Debugging SBO_SP_TransactionNotification?

[Former Member]

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

[former_member201110 · Accepted solution]

Hi Michael,

Firstly, I notice that you are calling an Update statement on the ORDR table at the end of your code to update a UDF field. This is not allowed by SAP as it would update a system table and you risk losing all support for your site if you use this code on a production site. You should use the DI API to update UDFs on a system table.

To trap your error, you can execute the proc in SQL Studio Management and pass valid parameter values to see what error is being produced (I am assuming you are doing this on a test system ). If you are not sure what the parameter values should be then run SQL Profiler while doing an update or add on a sales order and then you'll get the full exec command in SQL Profiler.

Kind Regards,

Owen

[Former Member · Accepted solution]

Thank you Owen, your help solved it!

I used SQL profiler to explore what was happening in the database, and was able to find the calls to the stored procedure. I then replayed the calls in Query Analyzer, and saw where it was crashing.

I was not setting the Project property in the line items.

When I changed the code to set the Project property, everything was okay!

I will also connect with the people who wrote the stored procedure about the invalid update to the ORDR table. We'll get it sorted.

Thank you very much,

Mike