Hi,
I am working on a B1 system which has some modifications in the SBO_SP_TransactionNotification stored procedure. The modifications are preventing me from performing Adds or Updates on the oOrders objects. I am trying to debug it by inserting the values it is working with into a table called SBO_SP_TransactionNotificationErrors, but it doesn't seem to work when there is an error. I think it doesn't work because it might be part of a transaction, so when the transaction fails, the insert is rolled back as well.... So I can't see what's going on inside.
Can you please help me figure out how to debug this stored procedure?
Thank you,
Mike
set ANSI_NULLS ON
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'
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
If @object_type = '17' -- Sales Order
begin
Declare @Project as nvarchar(20)
Declare @PoPrss as nvarchar(1)
Declare @AIR as nvarchar(20)
Declare @DocType_SO nvarchar(1)
declare @LineNum as int
Declare @ItemCode as nvarchar(32)
Declare @PC as nvarchar(32)
Declare @discount as nvarchar(32)
Declare @DocDueDate as datetime
Declare @Good4SAPS1_Invdate as datetime
Declare @whse as varchar(32)
Declare @docstatus as varchar(10)
set @Linenum = 0
WHILE @Linenum < 60
BEGIN
Select @DocStatus = ORDR.DocStatus,@Whse=whscode,@Project= RDR1.Project,@DocType_SO=DocType, @DocDueDate=ORDR.DocDueDate,@PoPrss=PoPrss, @AIR=U_AirwayBill ,@ItemCode=RDR1.ItemCode,@Discount=ORDR.DiscPrcnt from RDR1 inner join ORDR
on RDR1.DocEntry=ORDR.DocEntry
where RDR1.DocEntry = @list_of_cols_val_tab_del and RDR1.Linenum = @Linenum
select @PC=SWW from OITM where ItemCode = @ItemCode
select @Good4SAPS1_Invdate= invdate FROM InventoryStatus where WarehouseCD = @whse
declare @SQL as varchar(3000)
select @SQL = ' Select @DocStatus = ORDR.DocStatus,@Whse=whscode,@Project= RDR1.Project,@DocType_SO=DocType, @DocDueDate=ORDR.DocDueDate,@PoPrss=PoPrss, @AIR=U_AirwayBill ,@ItemCode=RDR1.ItemCode,@Discount=ORDR.DiscPrcnt from RDR1 inner join ORDR
on RDR1.DocEntry=ORDR.DocEntry
where RDR1.DocEntry = @list_of_cols_val_tab_del and RDR1.Linenum = @Linenum
select @PC=SWW from OITM where ItemCode = @ItemCode
select @Good4SAPS1_Invdate= invdate FROM InventoryStatus where WarehouseCD = @whse'
INSERT INTO [SBOJFDC_US_080620A].[dbo].[SBO_SP_TransactionNotificationErrors]
([error]
,[error_message]
,[datetime]
,[Project]
,[PrPrss]
,[AIR]
,[DocType_SO]
,[LineNum]
,[ItemCode]
,[discount]
,[DocDueDate]
,[Good4SAPS1_Invdate]
,[whse]
,[docstatus]
,[SQL])
VALUES
(0
,'starting.. read values'
,getdate()
,@Project
,@PoPrss
,@AIR
,@DocType_SO
,@LineNum
,@ItemCode
,@discount
,@DocDueDate
,@Good4SAPS1_Invdate
,@whse
,@docstatus
,@SQL)
if datediff(d,@DocDueDate,@Good4SAPS1_Invdate)>0 and @Linenum=0 and @DocStatus = 'O'
begin
set @error = 1
set @error_message = 'Split for warehouse is closed or the order is still open'--+ @transaction_type
end
else
if @ItemCode ='PAL' and @transaction_type <> 'L'
begin
set @error = 1
set @error_message = 'Cannot use ItemCode PAL '--+ @transaction_type
end
else
If( ltrim(@Project ) = '' or @Project is null) and @DocType_SO = 'I'
begin
set @error = 1
set @error_message = 'Please enter project code'
end
else
If( left(@discount,1)='-')
begin
set @error = 1
set @error_message = 'Discounts cannot be negative '
end
else
if( ltrim(@PoPrss)='Y') and @DocType_SO = 'I'
begin
set @error = 1
set @error_message = 'Second Do Not Use button in logistics tab was accidentally checked'
end
else
/*if datediff(day, getdate(),@DocDueDate)<0
begin
set @error = 1
set @error_message = 'Delivery date cannot be a past date'
end
else*/
if (@PC <> @Project)
begin
set @error = 1
set @error_message = 'Item ' +@ItemCode + ' must have a project code of ' + @PC
end
set @LineNum=@LineNum+1
end
Update ORDR
set U_OriginalOrderId = DocNum
where DoCEntry = @list_of_cols_val_tab_del
end
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
insert into SBO_SP_TransactionNotificationErrors
(error, error_message)
values
(@error, @error_message)
-- Select the return values
select @error, @error_message
end