SAP B1 Notification open dr 30 Days

[former_member813690]

HI,

please help, want to restrict OPEN DR (no Invoice) with same SALES ORDER NO.

with 30 DAYS

-- RESTRICT OPEN DR DAYS (30DAYS) --

IF @OBJECT_TYPE = '15' AND @TRANSACTION_TYPE='A'

BEGIN

IF EXISTS (SELECT a.DOCENTRY FROM ODLN a INNER JOIN DLN1 b ON a.DOCENTRY=b.DOCENTRY WHERE a.docentry=@list_of_cols_val_tab_del and (a.DOCDATE)<=(select min(A.DOCDATE)+30 from ODLN a inner join DLN1 b on a.docentry = b.docentry inner join ORDR c on b.baseentry = c.docentry and b.basetype = c.objtype where a.docentry=@list_of_cols_val_tab_del and a.docstatus='O')

Begin

Select @error = 1, @error_message = 'Please check Terms Days (30)! '

End

End

[former_member813690]

Thank you Aziz, but the notification appear on 1st open DR, the notification only appear if the system detect that oldest open DR and new created Dr have >=30 days gap with only SAME Sales Order Number.

thanks.

[azizelmir]

Hi Jonathan,

Please check this:

IF @OBJECT_TYPE = '15' AND @TRANSACTION_TYPE='A'

BEGIN

IF EXISTS (SELECT a.DOCENTRY FROM ODLN a INNER JOIN DLN1 b ON a.DOCENTRY=b.DOCENTRY WHERE a.docentry=@list_of_cols_val_tab_del and (a.DOCDATE) <= (SELECT MIN(CONVERT(DATETIME, a.DocDate, 102)) + 30 FROM dbo.ODLN AS a INNER JOIN dbo.DLN1 AS b ON a.DocEntry = b.DocEntry LEFT OUTER JOIN dbo.ORDR AS c ON b.BaseEntry = c.DocEntry AND b.BaseType = c.ObjType WHERE(a.DocEntry =@list_of_cols_val_tab_del) AND (a.DocStatus = 'O')) )

Begin

Select @error = 1, @error_message = 'Please check Terms Days (30)! '

End

End

Thank you,

Aziz El Mir