Skip to Content
0
Former Member
Nov 14, 2012 at 01:18 PM

Help with Stored Procedure after Upgrade to 8.82 from 2007

29 Views

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