on 05-18-2011 5:26 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
> 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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.