cancel
Showing results for 
Search instead for 
Did you mean: 

How to prevent Sales Orders to be added if the BP...

Former Member
0 Kudos

does not have enough funds (considering other open orders).

I have a problem where operaters can add as many sales order for a BP as they would like as long as the total amount on the order is below his available credit (OCRD_CreditLine minus OCRD_Balance)

I would like to add a stored procedure that will also take OCRD_OrdersBal into consideration.

If a BP has a credit limit of 50,000 and account balance of 31,000 and no other open orders, any order with a document total of 19,000 or less should be allowed to be captured.

If however the BP has a credit limit of 50,000 and account balance of 31,000 with a open order of 10,000, no orders above 9,000 should be able to get added.

Hope how I explained make sense?

All help will be greatly appreciated.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

Try this in SP_transaction Notification it will work

-


Order Amount Value----


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

BEGIN

Declare @OpenOrderAmount as decimal(10,2)

Declare @CreditLimitAmount as decimal(10,2)

Declare @CurrentOrderAmount as decimal(10,2)

Declare @Balance as decimal(10,2)

Declare @AvailOrder as Decimal(10,2)

Set @CreditLimitAmount = (Select CreditLine from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

Set @Balance = (Select Balance from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

Set @OpenOrderAmount = (Select OrdersBal from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

Set @CurrentOrderAmount = (select Convert(decimal(10,2),DocTotal) from ORDR where DocEntry =@list_of_cols_val_tab_del)

Set @AvailOrder = (@CreditLimitAmount-(@Balance +@OpenOrderAmount))

If @AvailOrder < @CurrentOrderAmount

begin

SET @error = 10

SET @error_message = N'Total Order Amount Should be ' + cONVERT(NVARCHAR(20),@AvailOrder)

end

END

-


End----


Thanks

Manvendra Singh Niranjan

Former Member
0 Kudos

Hello,

> Try this in SP_transaction Notification it will work

> -


Order Amount Value----


> IF (@transaction_type = 'A' Or @transaction_type = 'U') AND @object_type = '17'

> BEGIN

> Declare @OpenOrderAmount as decimal(10,2)

> Declare @CreditLimitAmount as decimal(10,2)

> Declare @CurrentOrderAmount as decimal(10,2)

> Declare @Balance as decimal(10,2)

> Declare @AvailOrder as Decimal(10,2)

>

> Set @CreditLimitAmount = (Select CreditLine from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> Set @Balance = (Select Balance from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> Set @OpenOrderAmount = (Select OrdersBal from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> Set @CurrentOrderAmount = (select Convert(decimal(10,2),DocTotal) from ORDR where DocEntry =@list_of_cols_val_tab_del)

> Set @AvailOrder = (@CreditLimitAmount-(@Balance +@OpenOrderAmount))

>

> If @AvailOrder < @CurrentOrderAmount

> begin

> SET @error = 10

> SET @error_message = N'Total Order Amount Should be ' + cONVERT(NVARCHAR(20),@AvailOrder)

> end

> END

> -


End----


>

>

> Thanks

> Manvendra Singh Niranjan

Hi Mavendra

You query worked wonderfully! I had to make a few small changes to have work 100%:

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

BEGIN

Declare @OpenOrderAmount as decimal(10,2)

Declare @CreditLimitAmount as decimal(10,2)

Declare @CurrentOrderAmount as decimal(10,2)

Declare @Balance as decimal(10,2)

Declare @AvailOrder as decimal(10,2)

Declare @ExceedOrder as decimal(10,2)

Set @CreditLimitAmount = (Select CreditLine from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

Set @Balance = (Select Balance from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

Set @OpenOrderAmount = (Select OrdersBal from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

Set @CurrentOrderAmount = (select Convert(decimal(10,2),DocTotal) from ORDR where DocEntry =@list_of_cols_val_tab_del)

Set @AvailOrder = (@CreditLimitAmount-(@Balance @OpenOrderAmount)__ @CurrentOrderAmount_)_

set @ExceedOrder =(@CurrentOrderAmount-@AvailOrder)

If @AvailOrder < @CurrentOrderAmount

begin

SET @error = 13

SET @error_message = N'Total Possible Order Amount is Only M ' + cONVERT(NVARCHAR(20),@AvailOrder) + N' You Have Tried to Exceed this by M ' + cONVERT(NVARCHAR(20),@ExceedOrder) + N' Please Reduce the Order and Try Again - THANK YOU'

end

END

Only problem I have now is that the error message that gets displayed when the conditions are not met, is one of the other messages from another code that i have written to do weight checking. If I remove that other codes it works fine. Why could this be?

Former Member
0 Kudos

> Hello,

> > Try this in SP_transaction Notification it will work

> > -


Order Amount Value----


> > IF (@transaction_type = 'A' Or @transaction_type = 'U') AND @object_type = '17'

> > BEGIN

> > Declare @OpenOrderAmount as decimal(10,2)

> > Declare @CreditLimitAmount as decimal(10,2)

> > Declare @CurrentOrderAmount as decimal(10,2)

> > Declare @Balance as decimal(10,2)

> > Declare @AvailOrder as Decimal(10,2)

> >

> > Set @CreditLimitAmount = (Select CreditLine from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> > Set @Balance = (Select Balance from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> > Set @OpenOrderAmount = (Select OrdersBal from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> > Set @CurrentOrderAmount = (select Convert(decimal(10,2),DocTotal) from ORDR where DocEntry =@list_of_cols_val_tab_del)

> > Set @AvailOrder = (@CreditLimitAmount-(@Balance +@OpenOrderAmount))

> >

> > If @AvailOrder < @CurrentOrderAmount

> > begin

> > SET @error = 10

> > SET @error_message = N'Total Order Amount Should be ' + cONVERT(NVARCHAR(20),@AvailOrder)

> > end

> > END

> > -


End----


> >

> >

> > Thanks

> > Manvendra Singh Niranjan

>

> Hi Mavendra

>

> You query worked wonderfully! I had to make a few small changes to have work 100%:

>

>

> IF (@transaction_type = 'A' Or @transaction_type = 'U') AND @object_type = '17'

> BEGIN

> Declare @OpenOrderAmount as decimal(10,2)

> Declare @CreditLimitAmount as decimal(10,2)

> Declare @CurrentOrderAmount as decimal(10,2)

> Declare @Balance as decimal(10,2)

> Declare @AvailOrder as decimal(10,2)

> Declare @ExceedOrder as decimal(10,2)

>

> Set @CreditLimitAmount = (Select CreditLine from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> Set @Balance = (Select Balance from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> Set @OpenOrderAmount = (Select OrdersBal from OCRD where CardCode = (Select CardCode from ORDR where DocEntry =@list_of_cols_val_tab_del))

> Set @CurrentOrderAmount = (select Convert(decimal(10,2),DocTotal) from ORDR where DocEntry =@list_of_cols_val_tab_del)

> Set @AvailOrder = (@CreditLimitAmount-(@Balance @OpenOrderAmount)__ @CurrentOrderAmount_)_

> set @ExceedOrder =(@CurrentOrderAmount-@AvailOrder)

>

> If @AvailOrder < @CurrentOrderAmount

> begin

> SET @error = 13

> SET @error_message = N'Total Possible Order Amount is Only M ' + cONVERT(NVARCHAR(20),@AvailOrder) + N' You Have Tried to Exceed this by M ' + cONVERT(NVARCHAR(20),@ExceedOrder) + N' Please Reduce the Order and Try Again - THANK YOU'

> end

> END

>

>

> Only problem I have now is that the error message that gets displayed when the conditions are not met, is one of the other messages from another code that i have written to do weight checking. If I remove that other codes it works fine. Why could this be?

Wrong error code message fixed by adding my new code to the bottom of the list of codes. Thanks!

Former Member
0 Kudos

Hello Skillet,

Please explain your condition so i will help you same.

Thanks

Manvendra

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Welcome you post on the forum.

You may try:

IF @transaction_type IN (N'A', N'U') AND

@Object_type = N'17'

BEGIN

if exists (SELECT T0.DocEntry

FROM dbo.ORDR T0

INNER JOIN dbo.OCRD T1 ON T1.CardCode = T0.CardCode

WHERE T0.DOCENTRY = @list_of_cols_val_tab_del AND

T0.DocTotal > T1.CreditLine-T1.Balance-T1.OrdersBal

Begin

select @Error = 10, @error_message = 'Sales Order total exceed allowrance'

End

END

Thanks,

Gordon

Former Member
0 Kudos

Hi,

>

> Welcome you post on the forum.

>

> You may try:

>

> IF @transaction_type IN (N'A', N'U') AND

> @Object_type = N'17'

>

> BEGIN

> if exists (SELECT T0.DocEntry

> FROM dbo.ORDR T0

> INNER JOIN dbo.OCRD T1 ON T1.CardCode = T0.CardCode

>

> WHERE T0.DOCENTRY = @list_of_cols_val_tab_del AND

> T0.DocTotal > T1.CreditLine-T1.Balance-T1.OrdersBal

>

> Begin

> select @Error = 10, @error_message = 'Sales Order total exceed allowrance'

> End

> END

>

> Thanks,

> Gordon

Hi Gordon

I have managed to come right with the code that was posted above.

I did try yours briefly but got a SQL error. Not too clued up on SQL but will give it another shot to see exactly what it was complaining about and let you know.

Thanks!