|
|
Posted by Uncle Rico on 06/18/07 21:33
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,GETDATE())))),
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,GETDATE())))),
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,GETDATE())))),
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(MAX(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_ORDERSCHEDULE.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<MX AND DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
+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 SUBMTD<STR_UPDATED 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_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),GETDATE(),
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_ACTCH=ACT_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
[Back to original message]
|