Skip to Content
0
Former Member
Sep 28, 2008 at 02:49 AM

Debugging SBO_SP_TransactionNotification?

256 Views

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