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

[former_member264311]

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'

[narayanis · Accepted solution]

Hi Romel,

This can be managed with the help of system wide configuration and user level authorization.

By selecting commitment limit and A/R invoice you can activate the limit check.

By giving No Authorization to the user you can restrict the punching of A/R invoice on violation of credit / commitment limit.

Regards

Narayani

[former_member264311 · Accepted solution]

Hi Sir Narayani,

Thank you to your reply. What about A/R price not below to last purchased price?

Best Regards,

Thanks

[KennedyT21]

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.UserSign1 = 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'

[former_member264311]

Sir Ken,

What's the difference my code to your replied code?

Thanks

[former_member264311]

Hi Sir Ken.

Thank you for your reply, by the way what's the difference of your code to my code?

thanks

[narayanis]

Hi Romel,

Ideal thing to practice would be to maintain the price list and bind it to the customer. So that the price will be automatically fetched.

Because this can not be handled through authorization. Also SAP updates Last Purchase Price automatically.

If at all you want to go by this then, SPTN is the only option.

Regards

Narayani

[narayanis]

Hi Romel,

I guess this is another question. If so, please explain the scenario because in this thread you mentioned about commitment limit.

Regards

Narayani

[former_member264311]

Thanks Narayani,

same scenario like authorization before adding A/R but in this case if the price of an item is below the last purchased price, some user will not be authorize to add the document.

Thanks.