If Clause In SP_Transaction Notification

[former_member218051]

Hi all,

Thanks to all experts on the forum especially Rahul and Grodon , with all your help extended i've framed a SP_Transaction which seems to be working properly at random.

USE [Audio_Test]

GO

/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 08/01/2011 15:45:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[SBO_SP_TransactionNotification]

@object_type nvarchar(20), -- SBO Object Type

@transaction_type nchar(1), -- [A]dd, <u>pdate, [D]elete, [C]ancel, C[L]ose

@num_of_cols_in_key int,

@list_of_key_cols_tab_del nvarchar(255),

@list_of_cols_val_tab_del nvarchar(255)

AS

begin

-- Return values

declare @error int -- Result (0 for no error)

declare @error_message nvarchar (200) -- Error string to be displayed

select @error = 0

select @error_message = N'Ok'

-

---

-- ADD YOUR CODE HERE

IF (@object_type = '2') and (@transaction_type IN ('A', 'U'))

BEGIN

IF exists (select T0.CardCode FROM OCRD T0 Where (T0.GroupNum != 4 Or T0.GroupCode != 116) and (T0.UserSign = 16 or T0.Usersign2 = 16) and T0.CardCode =@list_of_cols_val_tab_del)

Begin

Select @error = -1,

@error_message ='You are not authorized to change payment terms and customer group'

End

END

If (@object_type = '17') and (@transaction_type in ('A' , 'U'))

BEGIN

Declare @Itemcode22 as varchar(255)

Declare @ItemName22 as varchar(255)

Declare @Qty as int

Declare @minline22 int

Declare @maxline22 int

Declare @lastday as datetime

Declare @onhand as int

Declare @openqty_s_order as int

Declare @openqty_p_order as int

set @lastday = GETDATE()

set @lastday = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@lastday))),DATEADD(mm,1,@lastday)),101))

set @minline22 = (select min(T0.linenum) from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del)

set @maxline22 = (select max(T0.linenum) from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del)

while @minline22 <= @maxline22

BEGIN

select @Itemcode22 = T0.ItemCode from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del And T0.LineNum=@minline22

select @ItemName22 = T0.Dscription from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del And T0.LineNum=@minline22

select @Qty = T0.Quantity from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del And T0.LineNum=@minline22

IF EXISTS(SELECT RDR1.ITEMCODE FROM RDR1 INNER JOIN OITM ON RDR1.ItemCode = OITM.ItemCode WHERE RDR1.DocEntry = @list_of_cols_val_tab_del AND OITM.QryGroup64 = 'Y' and RDR1.ShipDate <= @LASTDAY AND RDR1.LineNum = @minline22 AND RDR1.LineStatus = 'O' AND RDR1.ItemCode = @Itemcode22 )

begin

set @onhand = (select isnull(sum(oitw.onhand),0) from oitw where (oitw.whscode = N'ANDHERI' or oitw.whscode = N'01') and oitw.itemcode = @Itemcode22 )

set @openqty_s_order = (select isnull(sum(openqty),0) from rdr1 where (rdr1.shipdate <= @lastday and rdr1.itemcode = @Itemcode22 and rdr1.LineStatus = 'O') and (rdr1.whscode = N'ANDHERI' or rdr1.whscode = N'01') )

set @openqty_p_order = (select isnull(sum(openqty),0) from por1 , opor where (opor.docentry = por1.docentry and opor.U_ETA + 5 <= @lastday and por1.itemcode = @Itemcode22 and por1.LineStatus = 'O' )and (por1.whscode = N'ANDHERI' or por1.whscode = N'01') )

set @QTY = @qTY - ((@onhand + @openqty_p_order) - (@openqty_s_order))

if @onhand = 0 and @openqty_p_order = 0 and @openqty_s_order = 0

BEGIN

set @error = -1

set @error_message = @ItemName22 + ' On Line ' + ltrim(str(@minline22)) + ' Is OverBooked_1 By ' + LTRIM(str(@qty))

END

if (@onhand + @openqty_p_order) <= @openqty_s_order

BEGIN

set @error = -1

set @error_message = @ItemName22 + ' On Line ' + ltrim(str(@minline22)) + ' Is OverBooked_2 By ' + LTRIM(str(@qty))

END

if (@onhand + @openqty_p_order) >= @openqty_s_order

if @Qty > (@onhand + @openqty_p_order) - @openqty_s_order

BEGIN

set @error = -1

set @error = @ItemName22 + ' On Line ' + ltrim(str(@minline22)) + ' Is OverBooked_3 By ' + LTRIM(str(@qty ))

END

-- else

-- set @error =0

-- set @error_message = 'OK'

end

set @minline22 = @minline22 +1

END

END

-

---

-- Select the return values

select @error ,@error_message

end

The If clause or Sale Order is not working properly. Wrong if is getting satisfied.

Pls. help

Thanks Malhaar

[former_member204969 · Accepted solution]

You surely should correct this line:

set @error = @ItemName22 + ' On Line ' + ltrim(str(@minline22)) + ' Is OverBooked_3 By ' + LTRIM(str(@qty ))

[former_member218051 · Accepted solution]

Hi ,

Thanks.

Line Corrected.

But it aleays go in second if that is (opn + purchase) < sales

irrespective of the condition.

Thanks

Malhaar

[former_member206488 · Accepted solution]

Check if below condition is correct in your query:

	if (@onhand + @openqty_p_order) <= @openqty_s_order
					BEGIN
						set @error = -1
						set @error_message = @ItemName22 + ' On Line ' + ltrim(str(@minline22)) + ' Is OverBooked_2 By ' + LTRIM(str(@qty))
				END
				if (@onhand + @openqty_p_order) >= @openqty_s_order 
					BEGIN
					if @Qty > (@onhand + @openqty_p_order) - @openqty_s_order 
					
						set @error = -1
						set @error = @ItemName22 + ' On Line ' + ltrim(str(@minline22)) + ' Is OverBooked_3 By ' + LTRIM(str(@qty )) 
					END

As in Both conditiions you are checing = (Greater than eqal to ) and (Less than equal to). if (@onhand + @openqty_p_order) =@openqty_s_order condition is true, then it will check for both conditions???

Thanks,

[former_member204969 · Accepted solution]

The @openqty_s_order contains the ordered quantity by the current order! So the Is OverBooked_3 message is generated when there is enough quantity, but not the double of the ordered. Think it over again.

[former_member218051 · Accepted solution]

Hi Neetu,

Thanks,

I just converted all the ifs into a single one by

Availability = (StockInHand + Pending POs) - Pending SOs

If EnteredQty > Availability then there is overbooking.

I'm checking the scenarios.

Thanking you

Malhaar

[former_member218051 · Accepted solution]

Hi Istavan,

when the SP fires it is also considering the quantity entered by the user.

I just excluded the qty entered by the user from my calculation and checking the scenarios.

Thanks for the insight.

Thanks

Malhaar