SP_TRANSACTION NOTIFICATION GIVING ERROR

[former_member218051]

Hi Rahul,

I've tried solution provided by you. Actually it was a FMS earlier which worked well. But now we want to roll back SO instead of just notifying it.

here is the code

USE [Audio_Test]

GO

/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 07/16/2011 16:25:26 ******/

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 @Qty as float

Declare @minline22 int

Declare @maxline22 int

Declare @lastday as datetime

Declare @onhand as float

Declare @openqty_s_order as float

Declare @openqty_p_order as float

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 = ItemCode from RDR1 where docentry=@list_of_cols_val_tab_del And LineNum=@minline22

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

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.whscode = N'ANDHERI' or rdr1.whscode = N'01') and rdr1.LineStatus = 'O')

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.whscode = N'ANDHERI' or por1.whscode = N'01') and por1.LineStatus = 'O')

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

BEGIN

set @error = '-1'

set @error_message = @Itemcode22 + ' Is OverBooked By '

END

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

BEGIN

set @error = -1

set @error_message = @Itemcode22 + ' Is OverBooked By '

END

if (@onhand + @openqty_p_order) > @openqty_s_order

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

BEGIN

set @error = -1

set @error = @Itemcode22 + ' Is OverBooked By '

END

else

set @error =0

set @error_message = 'OK'

set @minline22 = @minline22 +1

END

END

-

---

-- Select the return values

select @error, @error_message

end

If my SO contains all the items with overbooking the SP gives the error for last row.

Suppose user corrects the quantity then the transaction gets saved irrespective of the fact that there are items with overbooking.

Please help.

Thanks

Malhaar

[former_member218051]

Hi Rahul,

Sorry to have bothered you.SP worked well by removing else part.

The SP worked well by removing else part.

Thanks

Malhaar