Skip to Content
0

Stored procedure in TransactionNotification help

Apr 09 at 10:00 AM

58

avatar image

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

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

5 Answers

Lance Paton Apr 09 at 02:51 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Apr 09 at 02:23 PM
0

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

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

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.

I have tried another variation,

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

BEGIN

IFEXISTS(

SELECT T0.DocEntry

FROM ORDR T0

INNERJOIN OCRD T1 ON T0.CardCode=T1.CardCode

WHERE DocStatus='O'

AND T1.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

This doesn't work either. It is successful on execution but then doesn't work in SAP.

Any further ideas what I could be doing wrong?

Thanks

Lance

0

Are you able to provide any further help?

Kind Regards
Lance

0
Nagarajan K Apr 17 at 02:08 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0
Nagarajan K Apr 18 at 02:27 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0
Nagarajan K Apr 18 at 02:58 PM
0

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

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

Hi,

Set up new BP, limit at 0 and input order. standard warning received about credit limit but proceed and it allows the order to be input.

Kind Regards

Lance

0