SBO_SP_TRANSACTIONNOTIFICATION QUERY-

[millicentdark]

Hello Experts,

I want an validation query that is based on Credit Limit and Payment Method.

Assumption: A customers has a credit limit of say (50,000) and a payment method of 21 days. Customer should be able to pick goods not exceeding 50,000.00 within the 21 days period.

b. If he has any invoice more than 21 days he should pay that invoice before the customer will be allowed to pick any goods even though the customer has not exceeded the credit limit.

Validation is on both value and period.

Please treat this as urgent and assist.

Regards

Justice

[azizelmir]

Hello Justice,

Please try this code:

IF @object_type='17' AND @transaction_type IN ('A','U')
BEGIN
    IF EXISTS (
        SELECT T0.DocEntry
        FROM ORDR T0 
        INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
        WHERE DocStatus='O' 
        AND T1.DocEntry=@list_of_cols_val_tab_del
        AND (T0.DocTotal + T1.Balance) > T1.CreditLine
		AND DATEDIFF(day, Getdate(), T0.Taxdate) > (select Q1.ExtraDays from OCTG Q1 where Q1.GroupNum = T1.GroupNum)) 
    BEGIN   
        Set @error = -1
        SET @error_message = 'Business Partner is Over Credit Limit! Save this document as DRAFT, thank you'
    END
END 

Thank you,

Aziz

[millicentdark]

Hello Aziz,

It is okay.

You can send me the complete TN .

Standing by

[azizelmir]

Hi Justice,

Please check this code (validation on value), If okay, I will provide you with the complete TN.

IF @object_type='17' AND @transaction_type IN ('A','U')
BEGIN
    IF EXISTS (
        SELECT T0.DocEntry
        FROM ORDR T0 
        INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
        WHERE DocStatus='O' 
        AND T1.DocEntry=@list_of_cols_val_tab_del
        AND (T0.DocTotal + T1.Balance) > T1.CreditLine)
    BEGIN   
        Set @error = -1
        SET @error_message = 'Business Partner is Over Credit Limit! Save this document as DRAFT, thank you'
    END
END 

Thank you,

Aziz

[millicentdark]

Hello Experts,

Any idea or information on the above question?

Regards