Invoice Block when Exceeded date limit

[Former Member]

Hi

               I want to block pick the item in pick and manger when a customer have any payment due above 60 dates as per posting date,

we are following first sale order ---->pick  ----->Delivery ---->Invoice

Please give me a solution

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT distinct 'true' FROM OINV T0 INNER JOIN OCRD T1 ON

T0.CardCode = T1.CardCode WHERE T0.CardCode = $[$4.0.0] AND

DateDiff(dd,T0.[DocDueDate],Getdate())>60 and  T0.DocStatus='O'

Thanks & Regards,

Nagarajan

[Former Member]

But Mr. Nagarajan, it effected all customers, not only the customer who has an open invoice of above 60 days

I mean ,the Query not take the customer code automatically from the invoice criteria , which invoice want to add

[Former Member]

HI,

TRY THIS NOTIFICATION :It will check only the current invoice ,which is going to add

if @object_type = '13' and @transaction_type = 'A'

BEGIN

if exists (SELECT distinct cardcode FROM OINV T0 INNER JOIN OCRD T1 ON

T0.CardCode = T1.CardCode WHERE  T0.DocEntry = @list_of_cols_val_tab_del  AND

DateDiff(dd,T0.[DocDueDate],Getdate())>60)

begin

select @error = 1, @error_message = 'more than 60 days .... '

end

end

[kothandaraman_nagarajan]

Hi,

Instead of blocking, you can send above document for approval. try to create approval procedure for AR invoice with below query:

SELECT distinct 'true' FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE T0.CardCode = $[$4.0.0] AND DateDiff(dd,T0.[DocDate],Getdate())>60 and T0.DocStatus='O'

If you want stored procedure, check this thread

Thanks & Regards,

Nagarajan

[Former Member]

Thank you for your reply

But it effected all Invoice, not only the customer who has an open invoice above 60 days.

[Former Member]

can you please explain $[$4.0.0] use

[former_member212181]

Hi Akhil
Try Below query thru store procedure.

if (@object_type = '156')  and (@transaction_type = 'A' or @transaction_type = 'U')

BEGIN

  SELECT top 1 @error = 1, @error_message = 'Open Invoice Exists'

    from OPKL A inner Join PKL1 B on A.AbsEntry = B.AbsEntry

  Inner Join ORDR C on B.OrderEntry= C.DocEntry and BaseObject='17'

  Inner Join OINV D on C.CardCode = D.CardCode

  Where A.AbsEntry=@list_of_cols_val_tab_del and D.DocEntry in (Select X.DocEntry from OINV X where X.DocStatus ='O' and datediff (Day,X.DocDate, GetDate())>60)

END

Regards

Unnikrishnan