Skip to Content
author's profile photo Former Member
Former Member

Help with Stored Procedure after Upgrade to 8.82 from 2007

Hello ALL

please help. I did an upgrade from 2007 to 8.82 PL 05. Since the upgrade the server is EXTREMELY SLOW! it takes like 15 minutes to add 1 sales order.

this is their SBO_TRANSACTION_NOTIFICATION.

Can anyone help me check as I think this is whats causing the speed issues. When I de-activate the stored procedure the system is fast and normal.

USE [TEST]
GO
/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 11/14/2012 14:58:46 ******/
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

--Stop Salepeople cancelling orders that have already been allocated

IF @OBJECT_TYPE='17' AND @TRANSACTION_TYPE = 'C'

BEGIN
IF
(select count(*) from ORDR T0
where
T0.U_DEL = '2' AND T0.docnum = @list_of_cols_val_tab_del )> 0

SET @ERROR = 1
SET @ERROR_MESSAGE = 'BK - This Sales Order has already been allocated to a Delivery Note.'

END

----Doc Date and posting date must be the same
If @object_type = '17' and @transaction_type IN ('A','U')
Begin
declare @postDate datetime
declare @taxDate datetime
declare @createDate datetime

select @postDate = Docdate , @taxDate = TaxDate FROM ORDR T0 WHERE DocEntry = @list_of_cols_val_tab_del
print(@postDate)
print(@taxDate)

if @postDate <> @taxDate
begin
Select @error=1
SELECT @error_message='BK - The Posting Date and Document Date must be equal'
end

End

----The posting date can not be amended- deactivated by adv1 on 21082012 requested by kelly
--If @object_type = '17' and @transaction_type IN ('A','U')
--Begin
--
-- select @postDate = Docdate , @CreateDate = CreateDate FROM ORDR T0 WHERE DocEntry = @list_of_cols_val_tab_del
-- print(@postDate)
-- print(@CreateDate)
--
-- if @postDate <> @CreateDate
-- begin
-- Select @error=1
-- SELECT @error_message='BK - The Posting Date can not be amended'
-- end
--
--End
--Stop Salepeople Processing Order when Customer is on Sales Hold

IF @OBJECT_TYPE='17' AND @TRANSACTION_TYPE in ('U','A')

BEGIN
IF
(select count(*) from ORDR T0 inner join OCRD T1 on T0.CARDCODE = T1.CARDCODE
where
T1.[Priority] = '1'

AND T0.docnum = @list_of_cols_val_tab_del )> 0

select TOP 1 @ERROR = 1
,@ERROR_MESSAGE = 'BK - No more Sales Orders are allowed to be processed for this Customer'

END

--Stop Salepeople Processing Delivery when Customer is on Sales Hold

IF @OBJECT_TYPE='15' AND @TRANSACTION_TYPE in ('U','A')

BEGIN
IF
(select count(*) from ODLN T0 inner join OCRD T1 on T0.CARDCODE = T1.CARDCODE
where
T1.[Priority] = '1'

AND T0.docnum = @list_of_cols_val_tab_del )> 0

select TOP 1 @ERROR = 1
,@ERROR_MESSAGE = 'BK - No more Deliveries are allowed to be processed for this Customer'

END

--Stop Salepeople Processing Deliveries when Customer is on Sales Hold

IF @OBJECT_TYPE='13' AND @TRANSACTION_TYPE in ('U','A')

BEGIN
IF
(select count(*) from OINV T0 inner join OCRD T1 on T0.CARDCODE = T1.CARDCODE
where
T1.[Priority] = '1'

AND T0.docnum = @list_of_cols_val_tab_del )> 0

select TOP 1 @ERROR = 1
,@ERROR_MESSAGE = 'BK - No more Invoices are allowed to be processed for this Customer'

END

--Copy Batch Number onto Delivery Lines
--- uncommented not needed?janice

--IF @OBJECT_TYPE='15' AND @TRANSACTION_TYPE in ('U','A')

--BEGIN

--UPDATE DLN1

--SET DLN1.U_CN = (SELECT top 1 T0.BATCHNUM FROM IBT1 T0
-- WHERE
-- T0.ITEMCODE = DLN1.ITEMCODE
-- AND
-- T0.BaseLinNum = DLN1.LINENUM
-- AND
-- T0.BASEENTRY = DLN1.DOCENTRY

-- AND t0.basetype = '15')

--WHERE DLN1.DOCENTRY = @list_of_cols_val_tab_del

--END
--JANICE UNCOMMENTED ABOVE. NOT NEEDED?


--Block Delivery Sales Order

IF @OBJECT_TYPE='17' AND @TRANSACTION_TYPE in ('U','A')

BEGIN
IF
(select count(*) from ORDR T0
where T0.U_DEL IS NOT NULL
and t0.docentry > 7600

AND T0.docnum = @list_of_cols_val_tab_del )> 0

select TOP 1 @ERROR = 1
,@ERROR_MESSAGE = 'BK - You cannot create new Delivery Notes from Sales Orders'

END


--TN Oitm Item Name cannot be changed, therefore oitm.itemname must equal oitm.u_add_desc
If @object_type='4' and @transaction_type IN ('U')
Begin
Select Top 1 @error=1,@error_message='BK - Item description cannot be changed'
From OITM T0
where T0.ItemName <> T0.U_Add_Desc AND (T0.ItmsGrpCod = '157' or T0.ItmsGrpCod = '158' and T0.ItmsGrpCod = '159' or T0.ItmsGrpCod = '160') and @list_of_cols_val_tab_del = T0.ItemCode
End


--TN Adds Line Number of field to u_linenum On Sales Order
If @object_type='17' and @transaction_type IN ('A','U')
Begin
UPDATE rdr1
SET U_Linenum = linenum
from rdr1
WHERE rdr1.ItemCode Like '%%S00%%' AND
rdr1.DOCENTRY = @list_of_cols_val_tab_del
END

--Qty on Sales Order for Item may not exceed max bundle from OITM
If @object_type='17' and @transaction_type IN ('A','U')
Begin
Select Top 1 @error=1,@error_message='BK - Please re-check quantities as one of lines items exceeds max bundle size'
From RDR1 T0 Inner Join OITM T1 on T0.ItemCode = T1.ItemCode
where convert(nvarchar,T0.Text) > CONVERT(NVARCHAR,T1.U_Max_Amount) and T0.ItemCode = T1.ItemCode AND T1.ItemCode Like '%%S00%%'
and @list_of_cols_val_tab_del = T0.Docentry
End


--Issue for production cannot have 3 Decimal places (ADR)
--JANICE UNCOMMENTED 14/11/2012---
--If @object_type='6' and @transaction_type IN ('A')
--Begin
--Select Top 1 @error=1,@error_message='BK - You are tyring to issue a quantity with 3 decimals'
--From oibt T0 inner join ibt t1 on t0.docentry = t1.docentry
--where t1.baseref > 0 and t1.quantity - round(t1.quantity,2) <> 0 and @list_of_cols_val_tab_del = T0.DOCENTRY
--End
-- END JANICE UNCOMMENTED

-- ADD YOUR CODE HERE

----Update OITM when Delivery is done for FREESTOCK
if @object_type = '15' AND @transaction_type IN ('A')

BEGIN
update oitm
set OITM.U_DEL_QTY =
(
SELECT
T0.quantity
FROM dln1 t0 inner join odln t1 on t0.docentry = t1.docentry
WHERE
t0.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t0.WHScode = 'FS1' and left(t0.itemcode,4) = 'Free'
)
from oitm INNER JOIN DLN1 T10 ON T10.ITEMCODE = OITM.ITEMCODE
INNER JOIN ODLN T11 ON T10.DOCENTRY = T11.DOCENTRY
WHERE T11.DOCENTRY = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,4) = 'Free'
END

----Update OITM when Invoice is done for FREESTOCK
if @object_type = '13' AND @transaction_type IN ('A')

BEGIN
update oitm
set OITM.U_DEL_QTY =(
SELECT
T0.quantity-T0.quantity
FROM inv1 t0 inner join oinv t1 on t0.docentry = t1.docentry
WHERE
t0.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t0.WHScode = 'FS1' and left(t0.itemcode,4) = 'Free'
)
from oitm INNER JOIN inv1 T10 ON T10.ITEMCODE = OITM.ITEMCODE
INNER JOIN Oinv T11 ON T10.DOCENTRY = T11.DOCENTRY
WHERE T11.DOCENTRY = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,4) = 'Free'
END

--Update OITM when Return is done for FREESTOCK
if @object_type = '16' AND @transaction_type IN ('A')

BEGIN
update oitm
set OITM.U_DEL_QTY =
(
SELECT
T0.quantity-t0.quantity
FROM rdn1 t0 inner join ordn t1 on t0.docentry = t1.docentry
WHERE
t0.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t0.WHScode = 'FS1' and left(t0.itemcode,4) = 'Free'
)
from oitm INNER JOIN rdn1 T10 ON T10.ITEMCODE = OITM.ITEMCODE
INNER JOIN Ordn T11 ON T10.DOCENTRY = T11.DOCENTRY
WHERE T11.DOCENTRY = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,4) = 'Free'
END

---Update OITM when Delivery is done for GEO

if @object_type = '15' AND @transaction_type IN ('A')

BEGIN
update oitm
set OITM.U_DEL_QTY =
(
SELECT
T0.quantity
FROM dln1 t0 inner join odln t1 on t0.docentry = t1.docentry
WHERE
t0.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t0.WHScode = 'G01' and left(t0.itemcode,3) = 'GEO'
)
from oitm INNER JOIN DLN1 T10 ON T10.ITEMCODE = OITM.ITEMCODE
INNER JOIN ODLN T11 ON T10.DOCENTRY = T11.DOCENTRY
WHERE T11.DOCENTRY = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,3) = 'GEO'
END

----Update OITM when Invoice is done for GEO
if @object_type = '13' AND @transaction_type IN ('A')

BEGIN
update oitm
set OITM.U_DEL_QTY =(
SELECT
T0.quantity-T0.quantity
FROM inv1 t0 inner join oinv t1 on t0.docentry = t1.docentry
WHERE
t0.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t0.WHScode = 'G01' and left(t0.itemcode,3) = 'GEO'
)
from oitm INNER JOIN inv1 T10 ON T10.ITEMCODE = OITM.ITEMCODE
INNER JOIN Oinv T11 ON T10.DOCENTRY = T11.DOCENTRY
WHERE T11.DOCENTRY = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,3) = 'GEO'
END

--Update OITM when Return is done for GEO
if @object_type = '16' AND @transaction_type IN ('A')

BEGIN
update oitm
set OITM.U_DEL_QTY =
(
SELECT
T0.quantity-t0.quantity
FROM rdn1 t0 inner join ordn t1 on t0.docentry = t1.docentry
WHERE
t0.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t0.WHScode = 'G01' and left(t0.itemcode,3) = 'GEO'
)
from oitm INNER JOIN rdn1 T10 ON T10.ITEMCODE = OITM.ITEMCODE
INNER JOIN Ordn T11 ON T10.DOCENTRY = T11.DOCENTRY
WHERE T11.DOCENTRY = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,3) = 'GEO'

END

----Update OITM when Production Order captured for FREESTOCK
if @object_type = '202' AND @transaction_type IN ('A') or @object_type = '202' AND @transaction_type IN ('U')

BEGIN
update oitm
set OITM.U_WIP_QTY =
(
SELECT
T1.PlannedQty - T1.CmpltQty
FROM OWOR T1
WHERE
T1.itemcode = oitm.itemcode
and
t1.docentry = @list_of_cols_val_tab_del
and t1.Warehouse = 'FS1' and left(t1.itemcode,4) = 'Free'
)
from oitm INNER JOIN OWOR T10 ON T10.ITEMCODE = OITM.ITEMCODE
WHERE T10.DocEntry = @list_of_cols_val_tab_del and t10.itemcode = oitm.itemcode and left(t10.itemcode,4) = 'Free'
END


--AdvanceOne Sales Order Change request from **LIVE DB***-----------
--Update the Production Order Number on Sales Order When a Production Order is created
--IF (@transaction_type='A' OR @transaction_type='U') AND @Object_type = '202'
IF @Object_type = '202' AND (@transaction_type='A' OR @transaction_type='U')
UPDATE ORDR
SET ORDR.U_PO_Num = OWOR.DocNum
FROM ORDR
JOIN
OWOR ON OWOR.U_SO1 = ORDR.DocNum
WHERE OWOR.U_SO1 <> ''

UPDATE ORDR
SET ORDR.U_PO_Num = OWOR.DocNum
FROM ORDR
JOIN
OWOR ON OWOR.U_SO2 = ORDR.DocNum
WHERE OWOR.U_SO2 <> ''

UPDATE ORDR
SET ORDR.U_PO_Num = OWOR.DocNum
FROM ORDR
JOIN
OWOR ON OWOR.U_SO3 = ORDR.DocNum
WHERE OWOR.U_SO3 <> ''

UPDATE ORDR
SET ORDR.U_PO_Num = OWOR.DocNum
FROM ORDR
JOIN
OWOR ON OWOR.U_SO4 = ORDR.DocNum
WHERE OWOR.U_SO4 <> ''

UPDATE ORDR
SET ORDR.U_PO_Num = OWOR.DocNum
FROM ORDR
JOIN
OWOR ON OWOR.U_SO5 = ORDR.DocNum
WHERE OWOR.U_SO5 <> ''


--Update Status on Sales Order when production Order is created
--IF (@transaction_type='A' OR @transaction_type='U') AND @Object_type = '202'
IF @Object_type = '202' AND (@transaction_type='A' OR @transaction_type='U')

UPDATE ORDR
SET ORDR.U_Order_Stages = '04'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO1 = ORDR.DocNum
WHERE OWOR.U_SO1 <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '04'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO2 = ORDR.DocNum
WHERE OWOR.U_SO2 <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '04'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO3 = ORDR.DocNum
WHERE OWOR.U_SO3 <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '04'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO4 = ORDR.DocNum
WHERE OWOR.U_SO4 <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '04'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO5 = ORDR.DocNum
WHERE OWOR.U_SO5 <> ''


--Janice - update sales order status to "06 - Machine Allocation" when Machine Number is selected on Production Order ---
--Update Status on Sales Order when production Order is updated with Machine Number
--IF (@transaction_type='U') AND @Object_type = '202'
IF @Object_type = '202' AND (@transaction_type='U')
UPDATE ORDR
SET ORDR.U_Order_Stages = '06',
ORDR.U_MACHINE = OWOR.U_MAC
FROM ORDR
JOIN
OWOR ON OWOR.U_SO1 = ORDR.DocNum
WHERE OWOR.U_MAC <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '06',
ORDR.U_MACHINE = OWOR.U_MAC
FROM ORDR
JOIN
OWOR ON OWOR.U_SO2 = ORDR.DocNum
WHERE OWOR.U_MAC <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '06',
ORDR.U_MACHINE = OWOR.U_MAC
FROM ORDR
JOIN
OWOR ON OWOR.U_SO3 = ORDR.DocNum
WHERE OWOR.U_MAC <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '06',
ORDR.U_MACHINE = OWOR.U_MAC
FROM ORDR
JOIN
OWOR ON OWOR.U_SO4 = ORDR.DocNum
WHERE OWOR.U_MAC <> ''

UPDATE ORDR
SET ORDR.U_Order_Stages = '06',
ORDR.U_MACHINE = OWOR.U_MAC
FROM ORDR
JOIN
OWOR ON OWOR.U_SO5 = ORDR.DocNum
WHERE OWOR.U_MAC <> ''

--- end janice machine alloc


--Update the Sales Order Status to "Production order Complete" when PRoduction Order is completed
--IF (@transaction_type='A' OR @transaction_type='U') AND @Object_type = '202'
IF @Object_type = '202' AND (@transaction_type='A' OR @transaction_type='U')
UPDATE ORDR
SET ORDR.U_Order_Stages = '07'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO1 = ORDR.DocNum
WHERE (OWOR.U_SO1 <> '' and OWOR.status ='L')

UPDATE ORDR
SET ORDR.U_Order_Stages = '07'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO2 = ORDR.DocNum
WHERE OWOR.U_SO2 <> '' and OWOR.status ='L'

UPDATE ORDR
SET ORDR.U_Order_Stages = '07'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO3 = ORDR.DocNum
WHERE OWOR.U_SO3 <> '' and OWOR.status ='L'

UPDATE ORDR
SET ORDR.U_Order_Stages = '07'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO4 = ORDR.DocNum
WHERE OWOR.U_SO4 <> '' and OWOR.status ='L'

UPDATE ORDR
SET ORDR.U_Order_Stages = '07'
FROM ORDR
JOIN
OWOR ON OWOR.U_SO5 = ORDR.DocNum
WHERE OWOR.U_SO5 <> '' and OWOR.status ='L'

--Update Sales order when delivery exists
IF (@transaction_type='A' OR @transaction_type='U') AND @Object_type = '15'
UPDATE ORDR
SET ORDR.U_Order_Stages = '08'
FROM DLN1 T0 inner join ORDR T1 on T1.DocNum=T0.BaseRef
WHERE T1.DocNum = T0.BaseRef and T0.docentry = @list_of_cols_val_tab_del


--Update Sales order when invoice exists
--IF (@transaction_type='A' OR @transaction_type='U') AND @Object_type = '13'
IF @Object_type = '13' AND (@transaction_type='A' OR @transaction_type='U')

UPDATE ORDR
SET ORDR.U_Order_Stages = '10'
FROM DLN1 T0 inner join ORDR T1 on T1.DocNum=T0.BaseRef
inner join INV1 T2 on T2.BaseRef = T0.DocEntry
inner join OINV T3 on T2.DocEntry=T3.DocEntry
WHERE T1.DocNum = T0.BaseRef and T3.DocEntry = @list_of_cols_val_tab_del
and t0.TargetType= '13'


--Update Sales Order Status when Order has been Cancelled
--IF (@transaction_type='C') AND @Object_type = '17'
IF @Object_type = '17' AND (@transaction_type='C')
UPDATE ORDR
SET ORDR.U_Order_Stages = '11'
FROM ORDR T0
WHERE T0.DocEntry = @list_of_cols_val_tab_del

--------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message

end

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Nov 14, 2012 at 01:32 PM

    Hi Janice Middleton...

    You have too rearrange the the TN SP one by one and check which TN SP Make the performance Issue..

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 15, 2012 at 09:23 PM

    Hi,

    This SP-TN must be rewrite to avoid affecting db performance. Direct update is not allowed for system table.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.