Skip to Content

Stored procedure in TransactionNotification help

Hi,

I wonder if someone could help me,

I am trying to prevent users from being able to add a sales order if a business partners collective, orders/deliveries and account balance exceeds the set credit limit.

I have tried the below in the stored procedure dbo.SBO_SP_TransactionNotification on my test database and it hasn't done anything.

if @object_type = '17' and (@TRANSACTION_TYPE = 'A')

begin

If exists(

SELECT T0.[CardCode] FROM ORDR T0 WHERE T0.[CardCode] IN ( select Ta.cardcode from OCRD Ta Where (Ta.[Balance] + Ta.[DNotesBal] + Ta.[OrdersBal]) >Ta.[CreditLine] ) and t0.docentry = @list_of_cols_val_tab_del )

begin

select @error = 1,

@error_message = 'Credit Limit is over'

end

end


Can anyone please assist?

Kind Regards

Lance

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Apr 09 at 02:51 PM

    Hi,

    Customer has £64k of orders, £5k of deliveries and a £4k account balance. Credit limit was £10k and it processed new orders.

    Then set BP credit limit to 0 and is still processes the orders.


    Any ideas?


    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 09 at 02:23 PM

    Hi,

    Share your BP credit limit value of BP that you are testing.

    OR set 0 value for credit limit and check above TN.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 17 at 02:08 PM

    Hi,

    Check whether transaction notification enabled under General settings--> Service tab.

    Also below query and let me know the result with amount and customer code

    SELECT T0.DocEntry, T0.Cardcode, T0.Doctotal

    FROM ORDR T0

    INNERJOIN OCRD T1 ON T0.CardCode=T1.CardCode

    WHERE DocStatus='O'

    AND(T0.DocTotal + T1.Balance + T1.DNotesBal + T1.OrdersBal)> T1.CreditLine)

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Yes Transaction Notification is enabled in the settings.

      The result for the BP "AMAZON" is as below,

      Internal NumberCustomer/Supplier No.Document Total54AMAZON4,380.7011AMAZON186.2312AMAZON38,085.0020AMAZON1,236.0085AMAZON7,440.0088AMAZON2,700.0089AMAZON2,700.0090AMAZON1.891AMAZON1.892AMAZON9,996.0093AMAZON9,996.0096AMAZON2,700.0097AMAZON2,700.0098AMAZON2,700.0099AMAZON2,700.00100AMAZON2,700.00101AMAZON2,700.00102AMAZON2,700.00103AMAZON2,700.00104AMAZON2,700.00105AMAZON2,700.00As it stands, Credit and commitment limit is set at 0 and

      T1.Balance = £6,110.07
      T1.DNotesBal = £8,430.00
      T1.OrdersBal = £70,703.73

      Thanks

      Lance

  • Apr 18 at 02:27 PM

    Hi,

    Try this, it should work,

    IF @object_type='17'AND @transaction_type IN('A','U')

    BEGIN

    IF EXISTS

    ( SELECT T0.DocEntry

    FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode

    WHERE DocStatus='O' AND T0.DocEntry=@list_of_cols_val_tab_del AND (T0.DocTotal + T1.Balance + T1.DNotesBal + T1.OrdersBal)> T1.CreditLine)

    BEGIN

    Set @error =-1

    SET @error_message ='Business Partner is Over Credit Limit! Save this document as DRAFT, thank you'

    END

    END

    Note: Replaced T1. Docentry with T0.Docentry

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Ok so just to go through the process to make sure.

      SQL Server Management Studio - Databases - RUP (my database) - Programmability - Stored procedures

      Right click dbo.SBO_SP_TranactionNotification

      Select Modify

      Input code

      Execute (Query executed successfully)

      Exit

      Save Query when prompted (does this have to be in a specific location? It is saving as default within the C:\ on the server PC)

      I tried the revised query and followed the above step and it still allows the test user to process the order.

      Kind Regards

      Lance

  • Apr 18 at 02:58 PM

    The above steps are correct. Create new BP and set credit limit as 0 and then troubleshoot TN.

    Add comment
    10|10000 characters needed characters exceeded