Skip to Content

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

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'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 28, 2016 at 07:05 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2016 at 10:05 AM
    
    
    
    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'
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 01, 2016 at 04:22 PM

    Hi Sir Ken.

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

    thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 01, 2016 at 04:22 PM

    Sir Ken,

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

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 28, 2016 at 08:45 AM

    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

    Add comment
    10|10000 characters needed 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.

  • avatar image
    Former Member
    Oct 29, 2016 at 10:18 AM

    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

    Add comment
    10|10000 characters needed characters exceeded