Skip to Content
0
Oct 26, 2016 at 06:32 AM

SBO_SP_Transaction Notification for Adding A/R invoice depends on Commitment Limit.

240 Views

Good Day Experts,

Please help me to my sql query code. I want to block specific users adding A/R invoice if the customers exceeded to their commitment limit.

If @object_type = '13' And @transaction_type in ('A')
If Exists(Select T0.DocEntry
From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
Left Join OCRD T2 On T0.CardCode = T2.CardCode
left join OCHH T3 On T0.CardCode = T3.CardCode
Left join JDT1 T4 On T0.CardCode = T4.shortname
Inner Join OUSR t5 On T0.UserSign = T5.USERID
Where T0.DocEntry = @list_of_cols_val_tab_del AND (T5.USER_CODE='manager' or T5.USER_CODE ='user2' OR T5.USER_CODE ='user3' OR T5.USER_CODE = 'user4' OR t5.USER_CODE = 'user5')
Group By T0.DocEntry,T2.DebtLine
Having(sum(T4.[Debit]- T4.[Credit])+sum(T3.[CheckSum])+sum(T0.[DocTotal]))>T2.DebtLine) Begin
SET @error=@list_of_cols_val_tab_del--@object_type;
SET @error_message ='Exceed Commitment Limit'