Skip to Content
0
Jul 14, 2019 at 03:04 PM

Credit limit & Days SP Transaction

65 Views

Hello guys,

Here is our client requirements:

For one customer, Credit limit is set as Rs 1000 & i have provided a UDF in which credit days are provided.

For this customer, Credit days is 3 days.

If an invoice is raised to the Customer on 1st July for a amount of 450;This invoice due date is 4th July.

Another invoice is raised on 2nd july for a amount of 350; This invoice due date is 5th July.

CASE 1:

If an user is raising an invoice on 6th july for 150Rs, AR invoice should not be added since there are two invoice for which payment is yet to recieved. So my client needs a blocking for this condition

CASE 2;

If an user is raising an invoice on 6th july for 1250Rs, AR invoice should not be added since there are two invoice for which payment is yet to recieved & Credit limit for this user is exceeded . So my client needs a blocking for this condition

This is my query for the blocking, But this is not working:

IF @object_type='13' AND @transaction_type IN ('A','U')

BEGIN

IF EXISTS ( SELECT T0.DocEntry

FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode

WHERE T0.DocStatus = 'O' AND

(Convert(numeric, GetDate())-convert(numeric, T0.DocDueDate )) < T1.U_EA_CREDIT AND

(T0.DocTotal + T1.Balance) > T1.CreditLine

AND

T0.DocEntry=@list_of_cols_val_tab_del )

BEGIN

Set

@error = -1

SET

@error_message = 'Business Partner is Credit Limit is exceeded'

END

END