NEED TO GO FASTER

NEED TO GO FASTER

am 18.06.2007 23:33:58 von Uncle Rico

HI IS THERE A WAY TO GET MY PROC FASTER IT RUNS OK NOW BUT I JUST NEED
IT FASTER THANKS.


CREATE PROCEDURE SP_ORDERSUBMISSION @SENDRECORD BIT, @TYPE CHAR(3) AS
SET NOCOUNT ON

DECLARE @STR_USER_ID CHAR(6)
DECLARE @STR_ORDERREGION CHAR(1)
DECLARE @STR_ORDEROFFICE CHAR(4)
DECLARE @STR_MINIMUM_DATE CHAR(10)
DECLARE @STR_MAXIMUM_DATE CHAR(10)
DECLARE @STR_CDSM CHAR(50)
DECLARE @STR_MISTAKE CHAR(100)
DECLARE @INT_DSUM INT
DECLARE @INT_TAXSUM INT
DECLARE @INT_TAXCNT INT
DECLARE @STR_ACTCH CHAR(6)
DECLARE @STR_COSTCNTR CHAR(9)
DECLARE @STR_LOCCODE CHAR(6)
DECLARE @STR_ORDERNUM CHAR(7)
DECLARE @STR_EC CHAR(1)
DECLARE @STR_ORDERNOTMADE CHAR(27)
DECLARE @STR_SECCODE CHAR(29)
DECLARE @STR_TAX CHAR(4)
DECLARE @INT_SEQUENCE INT
DECLARE @STR_ORDERSCH CHAR(4)
DECLARE @DEC_ORDERLEN DECIMAL(6,2)
DECLARE @INT_ORDERID INT
DECLARE @STR_ORDERDATE CHAR(10)
DECLARE @STR_ORDERINFO CHAR(5)
DECLARE @STR_ORDERREMOTE CHAR(4)
DECLARE @STR_ORDERRSN CHAR(6)
DECLARE @INT_COUNT INT
DECLARE @SMALLINT_OVERNIGHT SMALLINT
DECLARE @SMALLINT_FEES SMALLINT
DECLARE @DECIMAL_HOLIDAY DECIMAL(6,2)
DECLARE @STR_ORDERSUBDATE1 CHAR(14)
DECLARE @STR_ORDERSUBDATE2 CHAR(14)
DECLARE @STR_ORDERTYPE CHAR(6)
DECLARE @STR_COSTCOD CHAR(4)
DECLARE @STR_COST CHAR(1)
DECLARE @INT_CNT INT
DECLARE @DECIMAL_MAXORDER DECIMAL(6,2)
DECLARE @STR_NUM CHAR(2)
DECLARE @STR_FILENAME CHAR(21)
DECLARE @SMALLINT_WEEK SMALLINT
DECLARE @SMALLINT_YEAR SMALLINT
DECLARE @STR_METHOD CHAR(1)
DECLARE @INT_EXTRA INT

SELECT @INT_CNT=COUNT(*) FROM db_ORDERS..tbl_LOCKEDORDERS WHERE
LOCKED='ORDER_SUBMISSION'
IF @INT_CNT=0
BEGIN
INSERT INTO db_ORDERS..tbl_LOCKEDORDERS VALUES
('ORDER_SUBMISSION', GETDATE())
END
ELSE
BEGIN
RETURN
END

SELECT @STR_ORDERSUBDATE1=CONVERT(CHAR(4),DATEPART(YY,GETDATE())) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,GETDATE()))),2)
SELECT @STR_ORDERSUBDATE2=CONVERT(CHAR(4),DATEPART(YY,DATEADD(SS,
2,GETDATE()))) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,DATEADD(SS,2,GET DATE())))),
2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,DATEADD(SS,
2,GETDATE())))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,DATEADD(SS,2,GET DATE())))),
2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,DATEADD(SS,
2,GETDATE())))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,DATEADD(SS,2,GET DATE())))),
2)
SELECT @STR_TAX = ' '
SELECT @STR_ORDERNOTMADE = 'Order cannot be sent'


CREATE TABLE #ORDEREXC (INT_ORDERID INT, ORDERDATE char
(10),SYSTEM_COD char (5),CODE_TYPE char (1),DVALUE float,RSN char
(12),STR_UPDATED char (14),SUBMTD char (14),STR_CSTCD char (4) )
CREATE TABLE #ORDERSCH (INT_ORDERID INT, ORDERDATE CHAR(10), SCHLEN
DECIMAL(6,2), NITE SMALLINT, NPST SMALLINT, MHOL DECIMAL(6,2))
CREATE TABLE #ORDERNAHI (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #ORDERNONS (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #ORDERSMAP (SECONDARY CHAR(6), RECTYPE CHAR(6), COST
CHAR(1))
CREATE TABLE #ORDERNONSSCH (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #ORDERNONSSCH2 (INT_ORDERID INT, WK SMALLINT, YR
SMALLINT)
CREATE TABLE #ORDERNONSEXCSCH (INT_ORDERID INT)
CREATE TABLE #ORDERNONSEXC (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #NONRINT_ORDERID (INT_ORDERID INT)
CREATE TABLE #KEY (REGION CHAR(1), INT_ORDERID INT)


SELECT
@STR_NUM=LEFT('0'+LTRIM(CONVERT(CHAR(2),CONVERT(INT,ISNULL(M AX(SUBSTRING(SENTNAME,
15,2)),'00')) + 1)),2) FROM db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME!
='' AND SUBSTRING(SENTNAME,15,2)>='01' AND SUBSTRING(SENTNAME,
15,2)<='99'
IF @STR_NUM<'21' OR @STR_NUM>'50' SELECT
@STR_FILENAME='ordr.ifa'+RTRIM(LTRIM(@STR_NUM))+'x.495'
IF @STR_NUM>'20' AND @STR_NUM<'51' SELECT
@STR_FILENAME='mistk.c5l'+RTRIM(LTRIM(@STR_NUM))+'x.495'
UPDATE db_ORDERS..tbl_ORDERSTATUS SET SENTNAME=@STR_FILENAME WHERE
SENTNAME='' AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE OFFICE='ORDR')
IF @@ROWCOUNT=0
BEGIN
RETURN
END



INSERT INTO #KEY SELECT DISTINCT REGION, INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME=@STR_FILENAME
SELECT @STR_MINIMUM_DATE=MIN(ORDERDATE),
@STR_MAXIMUM_DATE=MAX(ORDERDATE) FROM db_ORDERS..tbl_ORDERSTATUS WHERE
SENTNAME=@STR_FILENAME


CREATE INDEX D ON #KEY (INT_ORDERID, REGION)
SELECT @INT_SEQUENCE=SEQNUM FROM db_ORDERS..tbl_ORDERCUSTLOCK WHERE
OFFICE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
IF @@ROWCOUNT=0
BEGIN
SET @INT_SEQUENCE=1
END
SET @ERRORMESSAGE='Check status'
SELECT @INT_CNT=COUNT(*) FROM #KEY


IF @INT_CNT=0 RETURN
DECLARE MAXSCH CURSOR FOR SELECT REGION, INT_ORDERID FROM #KEY ORDER
BY INT_ORDERID
OPEN MAXSCH
FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @STR_ORDEROFFICE=OFFICE FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
REGION=@STR_ORDERREGION
SELECT @DECIMAL_MAXORDER = 240
SELECT @DECIMAL_MAXORDER = MAXINCR FROM
db_ORDERS..tbl_ORDERMAXED WHERE CODE=@STR_ORDEROFFICE AND
REGION=@STR_ORDERREGION
IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = MAXINCR,
@STR_ORDEROFFICE=@STR_FILENAME FROM db_ORDERS..tbl_ORDERMAXED WHERE
CODE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = MAXINCR,
@STR_ORDEROFFICE='ORDR' FROM db_ORDERS..tbl_ORDERMAXED WHERE
CODE='ORDR' AND REGION=@STR_ORDERREGION
IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = 120,
@STR_ORDEROFFICE='MISSING'
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET STOP_TIME=START_TIME
+@DECIMAL_MAXORDER+BREAK2_STOP, STR_UPDATED='2', SUBMTD='0' WHERE
STOP_TIME-START_TIME-BREAK2_STOP>@DECIMAL_MAXORDER AND
INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
ORDERDATE IN (SELECT ORDERDATE FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
REGION=@STR_ORDERREGION AND INT_ORDERID=@INT_ORDERID)
SELECT @INT_CNT=@@ROWCOUNT
IF @INT_CNT>0 PRINT CONVERT(CHAR(12),GETDATE(),114) + ' -
LIMIT ' + RTRIM(CONVERT(CHAR(12),@INT_CNT)) + ' ORDERS TO ' +
RTRIM(CONVERT(CHAR(10),@DECIMAL_MAXORDER/12)) + ' HR FOR ' +
CONVERT(CHAR(10),@INT_ORDERID) + ' IN THE OFFICE ' + @STR_ORDEROFFICE
FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
END
DEALLOCATE MAXSCH


DECLARE EXCRND CURSOR FOR
SELECT REGION, INT_ORDERID, ORDERDATE, SYSTEM_COD AS CDE, DVALUE
FROM db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ROUND(DVALUE,0)!=DVALUE AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE
WHERE RMETHOD!='')
ORDER BY INT_ORDERID, ORDERDATE
OPEN EXCRND
FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
@STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @STR_METHOD=RMETHOD, @INT_EXTRA=REXTRA FROM
db_ORDERS..tbl_ORDERCODE WHERE SYSTEM_COD=@STR_ORDERREMOTE AND
REGION=@STR_ORDERREGION
IF @STR_METHOD='O'
BEGIN
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
DVALUE=ROUND(DVALUE,@INT_EXTRA) WHERE REGION=@STR_ORDERREGION AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
END
IF @STR_METHOD='C'
BEGIN
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
DVALUE=CEILING(DVALUE) WHERE REGION=@STR_ORDERREGION AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
END
IF @STR_METHOD='M'
BEGIN
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
DVALUE=FLOOR(DVALUE) WHERE REGION=@STR_ORDERREGION AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
END
FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
@STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
END
DEALLOCATE EXCRND


UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='SUM OF ORDERS FOR THIS
DAY GREATER THAN EXCEPTION LENGTH'
WHERE SYSTEM_COD='TTTT' AND
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
(SELECT REGION, INT_ORDERID, ORDERDATE, SUM(DVALUE) AS
DAT FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE
E.ORDERDATE=CTEEXC.ORDERDATE AND E.REGION=CTEEXC.REGION AND
(SYSTEM_COD='TTTT' OR SYSTEM_COD IN (SELECT
SYSTEM_COD FROM CTCODE AS C WHERE REGAAB='T' AND C.REGION=E.REGION))
GROUP BY REGION, INT_ORDERID, ORDERDATE
HAVING SUM(DVALUE)>(SELECT (STOP_TIME-START_TIME-
BREAK2_STOP)*5/60.0 FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
S.ORDERDATE=E.ORDERDATE AND S.INT_ORDERID=E.INT_ORDERID AND
S.REGION=E.REGION)
) AS BS
)

INSERT INTO #ORDERNAHI SELECT INT_ORDERID, ORDERDATE FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='BILL' AND INT_ORDERID
IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION)

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
PATINDEX(UPPER('%ORDERS ARE NOT SENT%'),UPPER(ERR))>0
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=SYSTEM_COD + ' ORDERS
ARE NOT SENT' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE WHERE
RECTYPE='MISSING' AND CTCODE.REGION=CTEEXC.REGION)

UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
PATINDEX(UPPER('%WILL NOT BE SENT CAUSE OF DUPLICATE ORDER
%'),UPPER(ERR))>0
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='WILL NOT BE SENT CAUSE OF
DUPLICATE ORDER' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
CTEEXC.ORDERDATE=CTESCH.ORDERDATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=db_ORDERS..tbl_ORDERSC HEDULE.REGION
AND db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE>=@STR_MINIMUM_DATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE<=@STR_MAXIMUM_DATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID>999999 AND
PATINDEX(UPPER('%ORDERS ARE NOT SENT
%'),UPPER(db_ORDERS..tbl_ORDEREXCEPTIONS.ERR))>0)

CREATE TABLE #ORDERSCHHRS (REGION CHAR(1), INT_ORDERID INT, FP BIT, MX
DECIMAL(5, 2), HRS DECIMAL(5, 2), WK TINYINT, YR SMALLINT)
CREATE TABLE #DATA2 (REGION CHAR(1), INT_ORDERID INT, ISFULLTIME BIT,
MAXHOURS DECIMAL(5,2))
INSERT INTO #DATA2 SELECT DISTINCT REGION, INT_ORDERID, ISFULLTIME,
MAXHOURS FROM db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID IN
(SELECT INT_ORDERID FROM #KEY)

DECLARE SETUPSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) ,
DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
INT_ORDERID
OPEN SETUPSCH
FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
INSERT INTO #ORDERSCHHRS
SELECT DISTINCT S.REGION, S.INT_ORDERID, D.ISFULLTIME,
D.MAXHOURS, SUM((S.STOP_TIME-S.START_TIME-
S.BREAK2_STOP)*5.0/60),DATEPART(WW,DATEADD(DD,-DATEPART(DW,S .ORDERDATE)
+1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE))
FROM db_ORDERS..tbl_ORDERSCHEDULE AS S, #DATA2 AS D
WHERE DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
DATEPART(DW,S.ORDERDATE)+1,S.ORDERDATE))=@SMALLINT_YEAR AND
S.REGION=@STR_ORDERREGION AND D.REGION=@STR_ORDERREGION AND
D.INT_ORDERID=@INT_ORDERID AND S.INT_ORDERID=@INT_ORDERID
GROUP BY S.REGION, S.INT_ORDERID, D.ISFULLTIME,
D.MAXHOURS,DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE))
FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
END
DEALLOCATE SETUPSCH
CREATE INDEX D2 ON #ORDERSCHHRS (HRS, MX, INT_ORDERID, REGION, WK, YR)

DECLARE CLRSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) ,
DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
INT_ORDERID
OPEN CLRSCH
FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
(PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED%'),UPPER(ERR))>0 OR
PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0) AND
INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)
+1,ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
DATEPART(DW,ORDERDATE)+1,ORDERDATE))=@SMALLINT_YEAR
FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
END
DEALLOCATE CLRSCH

UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='13', ERR='MAX ORDERS EXCEEDED'
WHERE
INT_ORDERID IN (SELECT C.INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS AS S WHERE
HRS>MX AND CTESCH.REGION=S.REGION AND
DATEPART(WW,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.WK AND
DATEPART(YY,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.YR)

UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='12', ERR='NOT ENOUGH ORDERS
SCHEDULED' WHERE
INT_ORDERID IN (SELECT C.INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS WHERE FP=1 AND
HRS +1,CTESCH.ORDERDATE))=WK AND DATEPART(YY,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=YR) AND
INT_ORDERID NOT IN
(SELECT INT_ORDERID FROM #ORDERNAHI AS E WHERE
DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
+1,CTESCH.ORDERDATE))=DATEPART(WW,DATEADD(DD,-DATEPART(DW,E. COL)
+1,E.COL)) AND
DATEPART(YY,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)
+1,CTESCH.ORDERDATE))=DATEPART(YY,DATEADD(DD,-DATEPART(DW,E. COL)
+1,E.COL)))

UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='MULTIPLE ORDERS' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS SS WHERE SENTNAME=@STR_FILENAME AND SS.ORDERDATE=CTESCH.ORDERDATE
AND SS.REGION=CTESCH.REGION) AND
CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN
( SELECT CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM
db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS SS WHERE SENTNAME=@STR_FILENAME AND
SS.ORDERDATE=S.ORDERDATE AND SS.REGION=S.REGION)
GROUP BY CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION HAVING
COUNT(*)>1)

UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='INVALID ORDER SCHEDULE'
WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
(STOP_TIME-START_TIME-BREAK2_STOP)/12.0 NOT IN (SELECT RTHRS FROM
db_ORDERS..tbl_ORDERRECORD)

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE ERR='MISSING
ORDER' AND
CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN (SELECT
CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM #KEY)

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='MISSING ORDER'
WHERE (SELECT ISNULL(SUM(DVALUE),0) FROM
db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND
SYSTEM_COD='DDDL')>0 AND
(SELECT ISNULL(SUM(DVALUE),0) FROM
db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND SYSTEM_COD
IN ('MISA','KIPA'))=0
AND SYSTEM_COD='DDDL' AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999 AND INT_ORDERID IN
(SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
SENTNAME=@STR_FILENAME AND S.ORDERDATE=CTEEXC.ORDERDATE AND
S.REGION=CTEEXC.REGION)

SELECT ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
AS E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS DSUM,
ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
AS E1 WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE
AND E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS EHSUM,
ORDERDATE, INT_ORDERID
INTO #EHPROB
FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
SENTNAME=@STR_FILENAME
DELETE FROM #EHPROB WHERE DSUM<=EHSUM

INSERT INTO #EHPROB
SELECT 0, 0, ORDERDATE, INT_ORDERID
FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
(SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS
E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
E1.INT_ORDERID=S.INT_ORDERID)> 0 AND
(SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E1
WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE AND
E1.INT_ORDERID=S.INT_ORDERID)=0
AND SENTNAME=@STR_FILENAME

INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #EHPROB

INSERT INTO #ORDERSMAP SELECT DISTINCT SECONDARY, RECTYPE, COST FROM
db_ORDERS..tbl_ORDERMAP WHERE REGION=@STR_ORDERREGION
CREATE INDEX RecLoop ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
INSERT INTO #ORDERNONSSCH SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
db_ORDERS..tbl_ORDERSCHEDULE WHERE
( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%SUM OF ORDERS FOR THIS DAY GREATER THAN
SCHEDULE ALLOWS%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))>0 ) AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)

INSERT INTO #ORDERNONSSCH2 SELECT DISTINCT INT_ORDERID,
DATEPART(WW,ORDERDATE), DATEPART(YY,ORDERDATE) FROM
db_ORDERS..tbl_ORDERSCHEDULE WHERE
( PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED
%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%INVALID ORDER SCHEDULE%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%MISSING ORDER%'),UPPER(ERR))>0 ) AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)

INSERT INTO #ORDERNONSEXC SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%MISSING ORDER%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))>0 AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE) AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
INSERT INTO #ORDERNONS SELECT INT_ORDERID,ORDERDATE FROM
db_ORDERS..tbl_ORDEREXCEPTIONS
WHERE SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
REGION=@STR_ORDERREGION ) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)

INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #KEY
WHERE
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSCHEDULE WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
INT_ORDERID>999999

INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='UMSL' AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION)

CREATE CLUSTERED INDEX K1 ON #KEY (INT_ORDERID)
CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH (INT_ORDERID,COL)
CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH2 (INT_ORDERID)
CREATE CLUSTERED INDEX S1 ON #ORDERNONSEXCSCH (INT_ORDERID)
CREATE CLUSTERED INDEX E1 ON #ORDERNONSEXC (INT_ORDERID,COL)
CREATE INDEX iNOSUB ON #ORDERNONS (INT_ORDERID,COL)
CREATE INDEX iSECONDARYMAP ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
CREATE INDEX iNONR ON #NONRINT_ORDERID (INT_ORDERID)

UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='', STR_UPDATED='15',
SUBMTD='0' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
C.REGION=CTESCH.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE E.ORDERDATE=CTESCH.ORDERDATE
AND E.REGION=CTESCH.REGION AND STR_UPDATED>SUBMTD AND
LEN(RTRIM(LTRIM(SYSTEM_COD)))>0) AND
INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
#ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTESCH.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
INT_ORDERID NOT IN (SELECT #ORDERNONSSCH.INT_ORDERID FROM
#ORDERNONSSCH WHERE #ORDERNONSSCH.COL=CTESCH.ORDERDATE)

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='17' WHERE INT_ORDERID>999999
AND INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND
( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))>0 )
AND ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD=@STR_ORDERSUBDATE2 WHERE
REGION=@STR_ORDERREGION AND INT_ORDERID>999999 AND INT_ORDERID IN
(SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND
PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))>0

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='', STR_UPDATED='15',
SUBMTD='0' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD AND
INT_ORDERID>999999 AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND S.ORDERDATE=CTEEXC.ORDERDATE AND
S.REGION=CTEEXC.REGION AND INT_ORDERID IN (SELECT INT_ORDERID FROM
#KEY)) AND
INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
#ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
#ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND
LEN(RTRIM(LTRIM(SYSTEM_COD)))>0 AND LTRIM(RTRIM(SUBMTD)) NOT IN
('16','17')

UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18',
STR_UPDATED='0', ERR=LTRIM(RTRIM(SYSTEM_COD)) + ' ' +
@STR_ORDERNOTMADE WHERE (SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
REGION=CTEEXC.REGION ) OR SYSTEM_COD='BILL') AND
REGION=@STR_ORDERREGION AND SUBMTD999999
AND INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
#ORDERNONS.COL=CTEEXC.ORDERDATE) AND INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET SUBMTD='18', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
#ORDERNONS.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
C.REGION=CTESCH.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE

IF @@ROWCOUNT>0
BEGIN
CREATE INDEX iMHIR ON #ORDERNAHI (INT_ORDERID, COL)
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='UMSL EXISTS FOR
ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
#ORDERNAHI.COL=CTEEXC.ORDERDATE) AND SYSTEM_COD!='BILL' AND
INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='UMSL EXISTS FOR
ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
#ORDERNAHI.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION) AND
ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
END

INSERT INTO #ORDEREXC SELECT INT_ORDERID, LTRIM(RTRIM(ORDERDATE)) AS
ORDERDATE, SYSTEM_COD, CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD,
STR_CSTCD FROM db_ORDERS..tbl_ORDEREXCEPTIONS
WHERE INT_ORDERID>999999 AND STR_UPDATED>SUBMTD AND
PATINDEX(UPPER('%Invalid Elink Code%'),UPPER(ERR))=0 AND
PATINDEX(UPPER('%Unmapped RT Code%'),UPPER(ERR))=0 AND SYSTEM_COD!=''
AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
#ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
WHERE NS.WK=DATEPART(WW,CTEEXC.ORDERDATE) AND
NS.YR=DATEPART(WW,CTEEXC.ORDERDATE)) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
#ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND INT_ORDERID IN (SELECT
INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND
PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))=0
AND PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))=0
AND PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))=0
AND PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))=0
ORDER BY ORDERDATE

INSERT INTO #ORDERSCH SELECT DISTINCT INT_ORDERID,
LTRIM(RTRIM(ORDERDATE)) AS ORDERDATE, (STOP_TIME-START_TIME-
BREAK2_STOP)*5.0/60 AS SCHLEN,
(SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
E.REGION=S.REGION AND SYSTEM_COD='NDAY') AS NITE,
(SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
E.REGION=S.REGION AND SYSTEM_COD='SPTN') AS NSPT,
(SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
E.REGION=S.REGION AND SYSTEM_COD='CRSM') AS MHOL
FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD
AND INT_ORDERID>999999 AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
#ORDERNONSEXC.COL=S.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
WHERE NS.WK=DATEPART(WW,S.ORDERDATE) AND
NS.YR=DATEPART(YY,S.ORDERDATE)) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
#ORDERNONSSCH.COL=S.ORDERDATE) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=S.ORDERDATE AND C.REGION=S.REGION) AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
PATINDEX(UPPER('%MINIMUM ORDERS NOT MET%'),UPPER(ERR))=0 AND
PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))=0
ORDER BY INT_ORDERID, ORDERDATE

CREATE INDEX iEXC1a ON #ORDEREXC (INT_ORDERID, ORDERDATE, SYSTEM_COD,
CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD, STR_CSTCD)
CREATE INDEX iEXC1b ON #ORDEREXC (SYSTEM_COD, INT_ORDERID, ORDERDATE,
DVALUE)
CREATE INDEX iSCHa ON #ORDERSCH (INT_ORDERID, ORDERDATE, SCHLEN)

DECLARE EMPS CURSOR FOR SELECT DISTINCT (INT_ORDERID/2)+1134 FROM #KEY
WHERE INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH)
ORDER BY INT_ORDERID
OPEN EMPS
FETCH NEXT FROM EMPS INTO @INT_ORDERID
TRUNCATE TABLE db_ORDERS..tbl_ORDERDUMPRAW
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'HEDR0000' +
RIGHT('0000'+RTRIM(CONVERT(CHAR(4),@INT_SEQUENCE)),4) +
CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(6),GETDA TE(),
108),':','')
SELECT @INT_COUNT=1
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @STR_SECCODE = 'x '+CONVERT(CHAR(9),@INT_ORDERID)+' jk
9.216.12'
SELECT @STR_USER_ID='INVALID'
SELECT @STR_USER_ID=STR_USER_ID FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
LTRIM(RTRIM(STR_USER_ID))!=' ' AND STR_USER_ID IS NOT NULL
IF @STR_USER_ID='INVALID'
BEGIN

END
ELSE
BEGIN
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
'JKUL0001 ' + RIGHT('00000000' +
LTRIM(RTRIM(CONVERT(CHAR(9),@INT_ORDERID))),9) + ' ' +
SPACE(80) + 'CAP' + @STR_SECCODE
SELECT @STR_ORDEROFFICE=MAX(OFFICE) FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
REGION=@STR_ORDERREGION
SELECT @INT_COUNT=@INT_COUNT+1
DECLARE SCHS CURSOR FOR SELECT ORDERDATE, SCHLEN, NITE,
NPST, MHOL FROM #ORDERSCH
WHERE INT_ORDERID=@INT_ORDERID
AND ORDERDATE NOT IN (SELECT ORDERDATE FROM #ORDEREXC WHERE
INT_ORDERID=@INT_ORDERID AND SYSTEM_COD='BILL' AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE) AND

ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE ORDER BY
ORDERDATE
OPEN SCHS
FETCH NEXT FROM SCHS INTO @STR_ORDERDATE, @DEC_ORDERLEN,
@SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @INT_CNT=COUNT(*) FROM #NONRINT_ORDERID
WHERE INT_ORDERID=@INT_ORDERID
IF @INT_CNT>0 OR @SENDRECORD=1
BEGIN
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW
SELECT 'RKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @SMALLINT_OVERNIGHT>0 SELECT
@SMALLINT_OVERNIGHT=1
IF @SMALLINT_FEES>0 SELECT @SMALLINT_FEES=1
IF @DECIMAL_HOLIDAY>0 SELECT
@STR_ORDERSCH=SECONDARYHRS FROM db_ORDERS..tbl_ORDERRECORD WHERE
RTHRS=@DECIMAL_HOLIDAY AND NDIFF=@SMALLINT_OVERNIGHT AND
CARFARE=@SMALLINT_FEES
ELSE SELECT @STR_ORDERSCH=SECONDARYHRS FROM
db_ORDERS..tbl_ORDERRECORD WHERE RTHRS=@DEC_ORDERLEN AND
NDIFF=@SMALLINT_OVERNIGHT AND CARFARE=@SMALLINT_FEES
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
'ZKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + '01' + @STR_ORDERSCH +
' ' + @STR_TAX + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
DECLARE EXCS CURSOR FOR
SELECT E.ORDERDATE, M.SECONDARY,
LTRIM(RTRIM(M.RECTYPE)),
CONVERT(CHAR(6),CONVERT(DECIMAL(6,2),E.DVALUE)), LEFT(E.RSN,6),
E.STR_CSTCD, M.COST
FROM #ORDEREXC AS E, #ORDERSMAP AS M
WHERE E.INT_ORDERID=@INT_ORDERID AND
E.SYSTEM_COD=M.SECONDARY AND E.ORDERDATE=@STR_ORDERDATE AND
E.SYSTEM_COD NOT LIKE '%?%'
ORDER BY E.ORDERDATE, M.RECTYPE, M.COST
OPEN EXCS
FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
@STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
@STR_COSTCOD, @STR_COST
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
IF @STR_ORDERTYPE = 'JKUL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'JKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
@STR_ORDERRSN + SPACE(15) + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'RKUL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
@STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'ZKUL'
BEGIN
SELECT @STR_ACTCHT_CHG,
@STR_COSTCNTR=COST_CNTR, @STR_LOCCODE=LOC_CODE,
@STR_ORDERNUM=PROJ_NUM, @STR_EC=CONVERT(CHAR(1),ENV_CODE) FROM
db_ORDERS..tbl_ORDERCOST WHERE DIST_COD=@STR_COSTCOD AND
OFFICE=@STR_ORDEROFFICE
IF @STR_COST='*' AND
@@ROWCOUNT>0
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
@STR_ACTCH + @STR_COSTCNTR + @STR_LOCCODE + SPACE(6) + @STR_ORDERNUM +
@STR_EC + SPACE(111) + @STR_TAX + @STR_SECCODE
SELECT
@INT_COUNT=@INT_COUNT+1
END
ELSE
BEGIN
IF @STR_ORDERREMOTE !=
'DDDL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
SPACE(146) + @STR_TAX + @STR_SECCODE
SELECT
@INT_COUNT=@INT_COUNT+1
END
ELSE
BEGIN
SELECT
@INT_TAXSUM=SUM(DVALUE) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE
SELECT
@INT_TAXCNT=COUNT(*) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE
SELECT
@INT_DSUM=SUM(DVALUE) FROM #ORDEREXC WHERE SYSTEM_COD='DDDL' AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE
IF
@INT_DSUM<=@INT_TAXSUM AND @INT_TAXCNT>0
BEGIN
INSERT
INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
SPACE(146) + @STR_TAX + @STR_SECCODE
SELECT
@INT_COUNT=@INT_COUNT+1
END
ELSE
BEGIN
IF
@INT_DSUM>@INT_TAXSUM SELECT @STR_MISTAKE = 'ORDER IS TOO SHORT'
ELSE
SELECT @STR_MISTAKE = 'ORDER MUST HAVE EXCEPTION ON SAME DAY'
UPDATE
db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=@STR_MISTAKE WHERE
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD='DDDL'
END
END
END
END
IF @STR_ORDERTYPE = 'QKUL' OR
@STR_ORDERTYPE = 'ZKUL'
BEGIN
IF @STR_ORDERTYPE = 'ERECM'
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + SPACE(13) +
RIGHT(SPACE(7)+RTRIM(ROUND(@STR_ORDERINFO,0)),7) + SPACE(35) +
@STR_TAX + @STR_SECCODE
ELSE INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
RIGHT(SPACE(13)+RTRIM(ROUND(@STR_ORDERINFO,0))+'.00',13) + SPACE(7) +
SPACE(35) + @STR_TAX + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'WKUL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
RIGHT(SPACE(13)+@STR_ORDERINFO,13) + SPACE(35) + @STR_TAX +
@STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'PLAXC'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
@STR_ORDERREMOTE + SPACE(10) + REPLACE(@STR_ORDERDATE,'-','') +
LEFT(LTRIM(RTRIM(ROUND(@STR_ORDERINFO,0)))+SPACE(5),5) + SPACE(58) +
@STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'UKLQQ'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
@STR_ORDERREMOTE + RIGHT(SPACE(10)+@STR_ORDERINFO,10) +
REPLACE(@STR_ORDERDATE,'-','') + SPACE(63) + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
@STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
@STR_COSTCOD, @STR_COST
END
DEALLOCATE EXCS
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE AND SYSTEM_COD>'' AND SYSTEM_COD NOT LIKE '?
%'
FETCH NEXT FROM SCHS INTO @STR_ORDERDATE,
@DEC_ORDERLEN, @SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
END
DEALLOCATE SCHS
END
FETCH NEXT FROM EMPS INTO @INT_ORDERID
END
DEALLOCATE EMPS
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'TRLR' +
RIGHT('00000000' + LTRIM(RTRIM(CONVERT(CHAR(8),@INT_COUNT+1))),8)

DELETE db_ORDERS..tbl_LOCKEDORDERS WHERE LOCKED = 'ORDER_SUBMISSION'
GO

Re: NEED TO GO FASTER

am 19.06.2007 00:33:19 von Gert-Jan Strik

Hi Uncle Rico,

Writing your entire message in UPPER CASE is considered shouting, and
thus inappropriate.

I have had a quick look at your SP, but stopped reading after the first
cursor loop. What applies to normal text also applies to SQL: if it is
entirely written in upper case, then it is very had to read (especially
with the automatic wrapping because of the usenet post).

I have two questions for you:
1. Are you serious? Do you seriously expect someone to decode these 1K
lines of procedural code?
2. How fast is the SP running now (number of seconds), and how much
faster do you want it to be?


You did not post any DDL, nor an explanation what you are trying to do.
Although I don't know if it would have mattered...

So I will give give you some generic tips here that apply to your SP.

1. SQL is a set oriented language, not a procedural language. It works
fastest if you process set and avoid cursors, loops, unnecessary
variable assignments, etc. It is quite likely that you can completely
avoid some (or even all) of your cursors and a few of your temp tables,
by writing the statements set oriented, in combination with the use of
the CASE expression.

2. Make sure your database is properly normalized. Only then will the
RDBMS be able to use the proper indexes.
For example, there are a few substrings of columns that seem to have a
special meaning. From a database design point of view, that is
incorrect. These substrings should be columns. Some of your current
columns seems to be a composites. For example:
- SUBSTRING(SENTNAME,15,2)
- SUBSTRING(@STR_FILENAME,13,4)

3. Make sure the proper indexes are in place.

4. Use SQL Profiler to examine which parts of your stored procedure take
the longest. Often one statement takes a very large part of the total
run time.

HTH,
Gert-Jan


Uncle Rico wrote:
>
> HI IS THERE A WAY TO GET MY PROC FASTER IT RUNS OK NOW BUT I JUST NEED
> IT FASTER THANKS.
>
> CREATE PROCEDURE SP_ORDERSUBMISSION @SENDRECORD BIT, @TYPE CHAR(3) AS
> SET NOCOUNT ON
>
[snipped a massive 919 lines]

Re: NEED TO GO FASTER

am 19.06.2007 00:34:38 von Erland Sommarskog

Uncle Rico (Uncle.Rico291@gmail.com) writes:
> HI IS THERE A WAY TO GET MY PROC FASTER IT RUNS OK NOW BUT I JUST NEED
> IT FASTER THANKS.


It's a very long procedure, and without knowledge of the tables etc,
I don't think you should expect this audience to put the magic finger
on the sensitive spot.

In general: you are using cursors, and cursors are often a recipe for
slow-running code. I did not analyse what you cursors are doing, but you
could try replacing them with set-based operations. If you keep the cursors,
put INSENSITIVE before the CURSOR keyword. This can sometimes help.

Even more general: it's pointless trying to look at this beast as a whole.
Use the SQL Server Profiler, to track down what in the procedure that is
stealing time. Maybe it's just a single statement. Then you can fight
with that statement.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx