Skip to Content
0

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

Oct 26, 2016 at 06:32 AM

109

avatar image

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'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Pradnya S Oct 28, 2016 at 07:05 AM
0

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


image1.png (20.1 kB)
image2.png (73.1 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Sir Narayani,

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

Best Regards,

Thanks

0
Kennedy T Oct 27, 2016 at 10:05 AM
0



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'


Share
10 |10000 characters needed characters left characters exceeded
Romel Catalogo Nov 01, 2016 at 04:22 PM
0

Hi Sir Ken.

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

thanks

Share
10 |10000 characters needed characters left characters exceeded
Romel Catalogo Nov 01, 2016 at 04:22 PM
0

Sir Ken,

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

Thanks

Share
10 |10000 characters needed characters left characters exceeded
Pradnya S Oct 28, 2016 at 08:45 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Pradnya S Oct 29, 2016 at 10:18 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded