Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
82

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

NEED TO GO FASTER

Microsoft SQL Server
Please wait...

NEED TO GO FASTER

Postby UncleRico on Wed Oct 17, 2007 3:00 pm

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

  1. CREATE PROCEDURE SP_ORDERSUBMISSION @SENDRECORD BIT, @TYPE CHAR(3) AS
  2. SET NOCOUNT ON
  3.  
  4.  
  5. DECLARE @STR_USER_ID CHAR(6)
  6. DECLARE @STR_ORDERREGION CHAR(1)
  7. DECLARE @STR_ORDEROFFICE CHAR(4)
  8. DECLARE @STR_MINIMUM_DATE CHAR(10)
  9. DECLARE @STR_MAXIMUM_DATE CHAR(10)
  10. DECLARE @STR_CDSM CHAR(50)
  11. DECLARE @STR_MISTAKE CHAR(100)
  12. DECLARE @INT_DSUM INT
  13. DECLARE @INT_TAXSUM INT
  14. DECLARE @INT_TAXCNT INT
  15. DECLARE @STR_ACTCH CHAR(6)
  16. DECLARE @STR_COSTCNTR CHAR(9)
  17. DECLARE @STR_LOCCODE CHAR(6)
  18. DECLARE @STR_ORDERNUM CHAR(7)
  19. DECLARE @STR_EC CHAR(1)
  20. DECLARE @STR_ORDERNOTMADE CHAR(27)
  21. DECLARE @STR_SECCODE CHAR(29)
  22. DECLARE @STR_TAX CHAR(4)
  23. DECLARE @INT_SEQUENCE INT
  24. DECLARE @STR_ORDERSCH CHAR(4)
  25. DECLARE @DEC_ORDERLEN DECIMAL(6,2)
  26. DECLARE @INT_ORDERID INT
  27. DECLARE @STR_ORDERDATE CHAR(10)
  28. DECLARE @STR_ORDERINFO CHAR(5)
  29. DECLARE @STR_ORDERREMOTE CHAR(4)
  30. DECLARE @STR_ORDERRSN CHAR(6)
  31. DECLARE @INT_COUNT INT
  32. DECLARE @SMALLINT_OVERNIGHT SMALLINT
  33. DECLARE @SMALLINT_FEES SMALLINT
  34. DECLARE @DECIMAL_HOLIDAY DECIMAL(6,2)
  35. DECLARE @STR_ORDERSUBDATE1 CHAR(14)
  36. DECLARE @STR_ORDERSUBDATE2 CHAR(14)
  37. DECLARE @STR_ORDERTYPE CHAR(6)
  38. DECLARE @STR_COSTCOD CHAR(4)
  39. DECLARE @STR_COST CHAR(1)
  40. DECLARE @INT_CNT INT
  41. DECLARE @DECIMAL_MAXORDER DECIMAL(6,2)
  42. DECLARE @STR_NUM CHAR(2)
  43. DECLARE @STR_FILENAME CHAR(21)
  44. DECLARE @SMALLINT_WEEK SMALLINT
  45. DECLARE @SMALLINT_YEAR SMALLINT
  46. DECLARE @STR_METHOD CHAR(1)
  47. DECLARE @INT_EXTRA INT
  48.  
  49.  
  50. SELECT @INT_CNT=COUNT(*) FROM db_ORDERS..tbl_LOCKEDORDERS WHERE
  51. LOCKED='ORDER_SUBMISSION'
  52. IF @INT_CNT=0
  53.     BEGIN
  54.         INSERT INTO db_ORDERS..tbl_LOCKEDORDERS VALUES
  55. ('ORDER_SUBMISSION', GETDATE())
  56.     END
  57. ELSE
  58.     BEGIN
  59.         RETURN
  60.     END
  61.  
  62.  
  63. SELECT @STR_ORDERSUBDATE1=CONVERT(CHAR(4),DATEPART(YY,GETDATE())) +
  64. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,GETDATE()))),2) +
  65. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,GETDATE()))),2) +
  66. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,GETDATE()))),2) +
  67. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,GETDATE()))),2) +
  68. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,GETDATE()))),2)
  69. SELECT @STR_ORDERSUBDATE2=CONVERT(CHAR(4),DATEPART(YY,DATEADD(SS,
  70. 2,GETDATE()))) +
  71. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,DATEADD(SS,2,GETDATE())))),
  72. 2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,DATEADD(SS,
  73. 2,GETDATE())))),2) +
  74. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,DATEADD(SS,2,GETDATE())))),
  75. 2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,DATEADD(SS,
  76. 2,GETDATE())))),2) +
  77. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,DATEADD(SS,2,GETDATE())))),
  78. 2)
  79. SELECT @STR_TAX = '    '
  80. SELECT @STR_ORDERNOTMADE = 'Order cannot be sent'
  81.  
  82.  
  83. CREATE TABLE #ORDEREXC (INT_ORDERID INT, ORDERDATE CHAR
  84. (10),SYSTEM_COD CHAR (5),CODE_TYPE CHAR (1),DVALUE FLOAT,RSN CHAR
  85. (12),STR_UPDATED CHAR (14),SUBMTD CHAR (14),STR_CSTCD CHAR (4) )
  86. CREATE TABLE #ORDERSCH (INT_ORDERID INT, ORDERDATE CHAR(10), SCHLEN
  87. DECIMAL(6,2), NITE SMALLINT, NPST SMALLINT, MHOL DECIMAL(6,2))
  88. CREATE TABLE #ORDERNAHI (INT_ORDERID INT, COL CHAR(10))
  89. CREATE TABLE #ORDERNONS (INT_ORDERID INT, COL CHAR(10))
  90. CREATE TABLE #ORDERSMAP (SECONDARY CHAR(6), RECTYPE CHAR(6), COST
  91. CHAR(1))
  92. CREATE TABLE #ORDERNONSSCH (INT_ORDERID INT, COL CHAR(10))
  93. CREATE TABLE #ORDERNONSSCH2 (INT_ORDERID INT, WK SMALLINT, YR
  94. SMALLINT)
  95. CREATE TABLE #ORDERNONSEXCSCH (INT_ORDERID INT)
  96. CREATE TABLE #ORDERNONSEXC (INT_ORDERID INT, COL CHAR(10))
  97. CREATE TABLE #NONRINT_ORDERID (INT_ORDERID INT)
  98. CREATE TABLE #KEY (REGION CHAR(1), INT_ORDERID INT)
  99.  
  100.  
  101. SELECT
  102. @STR_NUM=LEFT('0'+LTRIM(CONVERT(CHAR(2),CONVERT(INT,ISNULL(MAX(SUBSTRING(SE­NTNAME,
  103. 15,2)),'00')) + 1)),2) FROM db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME!
  104. ='' AND SUBSTRING(SENTNAME,15,2)>='01' AND SUBSTRING(SENTNAME,
  105. 15,2)<='99'
  106. IF @STR_NUM<'21' OR @STR_NUM>'50' SELECT
  107. @STR_FILENAME='ordr.ifa'+RTRIM(LTRIM(@STR_NUM))+'x.495'
  108. IF @STR_NUM>'20' AND @STR_NUM<'51' SELECT
  109. @STR_FILENAME='mistk.c5l'+RTRIM(LTRIM(@STR_NUM))+'x.495'
  110. UPDATE db_ORDERS..tbl_ORDERSTATUS SET SENTNAME=@STR_FILENAME WHERE
  111. SENTNAME='' AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  112. db_ORDERS..tbl_ORDERCUSTOMERS WHERE OFFICE='ORDR')
  113. IF @@ROWCOUNT=0
  114.   BEGIN
  115.     RETURN
  116.   END
  117.  
  118.  
  119. INSERT INTO #KEY SELECT DISTINCT REGION, INT_ORDERID FROM
  120. db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME=@STR_FILENAME
  121. SELECT @STR_MINIMUM_DATE=MIN(ORDERDATE),
  122. @STR_MAXIMUM_DATE=MAX(ORDERDATE) FROM db_ORDERS..tbl_ORDERSTATUS WHERE
  123. SENTNAME=@STR_FILENAME
  124.  
  125.  
  126. CREATE INDEX D ON #KEY (INT_ORDERID, REGION)
  127. SELECT @INT_SEQUENCE=SEQNUM FROM db_ORDERS..tbl_ORDERCUSTLOCK WHERE
  128. OFFICE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
  129. IF @@ROWCOUNT=0
  130.     BEGIN
  131.         SET @INT_SEQUENCE=1
  132.     END
  133. SET @ERRORMESSAGE='Check status'
  134. SELECT @INT_CNT=COUNT(*) FROM #KEY
  135.  
  136.  
  137. IF @INT_CNT=0 RETURN
  138. DECLARE MAXSCH CURSOR FOR SELECT REGION, INT_ORDERID FROM #KEY ORDER
  139. BY INT_ORDERID
  140. OPEN MAXSCH
  141. FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
  142. WHILE (@@FETCH_STATUS <> -1 )
  143.     BEGIN
  144.         SELECT @STR_ORDEROFFICE=OFFICE FROM
  145. db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
  146. REGION=@STR_ORDERREGION
  147.         SELECT @DECIMAL_MAXORDER = 240
  148.         SELECT @DECIMAL_MAXORDER = MAXINCR FROM
  149. db_ORDERS..tbl_ORDERMAXED WHERE CODE=@STR_ORDEROFFICE AND
  150. REGION=@STR_ORDERREGION
  151.         IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = MAXINCR,
  152. @STR_ORDEROFFICE=@STR_FILENAME FROM db_ORDERS..tbl_ORDERMAXED WHERE
  153. CODE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
  154.         IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = MAXINCR,
  155. @STR_ORDEROFFICE='ORDR' FROM db_ORDERS..tbl_ORDERMAXED WHERE
  156. CODE='ORDR' AND REGION=@STR_ORDERREGION
  157.         IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = 120,
  158. @STR_ORDEROFFICE='MISSING'
  159.         UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET STOP_TIME=START_TIME
  160. +@DECIMAL_MAXORDER+BREAK2_STOP, STR_UPDATED='2', SUBMTD='0' WHERE
  161. STOP_TIME-START_TIME-BREAK2_STOP>@DECIMAL_MAXORDER AND
  162. INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
  163.             ORDERDATE IN (SELECT ORDERDATE FROM
  164. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  165. REGION=@STR_ORDERREGION AND INT_ORDERID=@INT_ORDERID)
  166.         SELECT @INT_CNT=@@ROWCOUNT
  167.         IF @INT_CNT>0 PRINT CONVERT(CHAR(12),GETDATE(),114) + ' -
  168. LIMIT ' + RTRIM(CONVERT(CHAR(12),@INT_CNT)) + ' ORDERS TO ' +
  169. RTRIM(CONVERT(CHAR(10),@DECIMAL_MAXORDER/12)) + ' HR FOR ' +
  170. CONVERT(CHAR(10),@INT_ORDERID) + ' IN THE OFFICE ' + @STR_ORDEROFFICE
  171.         FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
  172.     END
  173. DEALLOCATE MAXSCH
  174.  
  175.  
  176. DECLARE EXCRND CURSOR FOR
  177.   SELECT REGION, INT_ORDERID, ORDERDATE, SYSTEM_COD AS CDE, DVALUE
  178. FROM db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ROUND(DVALUE,0)!=DVALUE AND
  179.       INT_ORDERID IN (SELECT INT_ORDERID FROM
  180. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  181. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
  182.       SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE
  183. WHERE RMETHOD!='')
  184.     ORDER BY INT_ORDERID, ORDERDATE
  185. OPEN EXCRND
  186. FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
  187. @STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
  188. WHILE (@@FETCH_STATUS <> -1 )
  189.     BEGIN
  190.         SELECT @STR_METHOD=RMETHOD, @INT_EXTRA=REXTRA FROM
  191. db_ORDERS..tbl_ORDERCODE WHERE SYSTEM_COD=@STR_ORDERREMOTE AND
  192. REGION=@STR_ORDERREGION
  193.         IF @STR_METHOD='O'
  194.             BEGIN
  195.                 UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  196. DVALUE=ROUND(DVALUE,@INT_EXTRA) WHERE REGION=@STR_ORDERREGION AND
  197. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  198. SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
  199.             END
  200.         IF @STR_METHOD='C'
  201.             BEGIN
  202.                 UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  203. DVALUE=CEILING(DVALUE) WHERE REGION=@STR_ORDERREGION AND
  204. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  205. SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
  206.             END
  207.         IF @STR_METHOD='M'
  208.             BEGIN
  209.                 UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  210. DVALUE=FLOOR(DVALUE) WHERE REGION=@STR_ORDERREGION AND
  211. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  212. SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
  213.             END
  214.         FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
  215. @STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
  216.     END
  217. DEALLOCATE EXCRND
  218.  
  219.  
  220. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='SUM OF ORDERS FOR THIS
  221. DAY GREATER THAN EXCEPTION LENGTH'
  222.   WHERE SYSTEM_COD='TTTT' AND
  223.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  224. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  225. C.REGION=CTEEXC.REGION) AND
  226.     INT_ORDERID IN (SELECT INT_ORDERID FROM
  227.              (SELECT REGION, INT_ORDERID, ORDERDATE, SUM(DVALUE) AS
  228. DAT FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE
  229. E.ORDERDATE=CTEEXC.ORDERDATE AND E.REGION=CTEEXC.REGION AND
  230.                   (SYSTEM_COD='TTTT' OR SYSTEM_COD IN (SELECT
  231. SYSTEM_COD FROM CTCODE AS C WHERE REGAAB='T' AND C.REGION=E.REGION))
  232.                 GROUP BY REGION, INT_ORDERID, ORDERDATE
  233.                 HAVING SUM(DVALUE)>(SELECT (STOP_TIME-START_TIME-
  234. BREAK2_STOP)*5/60.0 FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
  235. S.ORDERDATE=E.ORDERDATE AND S.INT_ORDERID=E.INT_ORDERID AND
  236. S.REGION=E.REGION)
  237.              ) AS BS
  238.            )
  239.  
  240.  
  241. INSERT INTO #ORDERNAHI SELECT INT_ORDERID, ORDERDATE FROM
  242. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='BILL' AND INT_ORDERID
  243. IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
  244. SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  245. C.REGION=CTEEXC.REGION)
  246.  
  247.  
  248. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE
  249.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  250. AS C WHERE SENTNAME=@STR_FILENAME AND
  251. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
  252. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
  253.   PATINDEX(UPPER('%ORDERS ARE NOT SENT%'),UPPER(ERR))>0
  254. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=SYSTEM_COD + ' ORDERS
  255. ARE NOT SENT' WHERE
  256.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  257. AS C WHERE SENTNAME=@STR_FILENAME AND
  258. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
  259. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
  260.   SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE WHERE
  261. RECTYPE='MISSING' AND CTCODE.REGION=CTEEXC.REGION)
  262.  
  263.  
  264. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
  265.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  266. AS C WHERE SENTNAME=@STR_FILENAME AND
  267. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  268. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  269.   PATINDEX(UPPER('%WILL NOT BE SENT CAUSE OF DUPLICATE ORDER
  270. %'),UPPER(ERR))>0
  271. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='WILL NOT BE SENT CAUSE OF
  272. DUPLICATE ORDER' WHERE
  273.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  274. AS C WHERE SENTNAME=@STR_FILENAME AND
  275. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  276. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  277.   INT_ORDERID IN (SELECT INT_ORDERID FROM
  278. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
  279.               CTEEXC.ORDERDATE=CTESCH.ORDERDATE AND
  280. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=db_ORDERS..tbl_ORDERSCHEDULE.REGION
  281. AND db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE>=@STR_MINIMUM_DATE AND
  282. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE<=@STR_MAXIMUM_DATE AND
  283. db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID>999999 AND
  284.               PATINDEX(UPPER('%ORDERS ARE NOT SENT
  285. %'),UPPER(db_ORDERS..tbl_ORDEREXCEPTIONS.ERR))>0)
  286.  
  287.  
  288. CREATE TABLE #ORDERSCHHRS (REGION CHAR(1), INT_ORDERID INT, FP BIT, MX
  289. DECIMAL(5, 2), HRS DECIMAL(5, 2), WK TINYINT, YR SMALLINT)
  290. CREATE TABLE #DATA2 (REGION CHAR(1), INT_ORDERID INT, ISFULLTIME BIT,
  291. MAXHOURS DECIMAL(5,2))
  292. INSERT INTO #DATA2 SELECT DISTINCT REGION, INT_ORDERID, ISFULLTIME,
  293. MAXHOURS FROM db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID IN
  294. (SELECT INT_ORDERID FROM #KEY)
  295.  
  296.  
  297. DECLARE SETUPSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
  298. DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)),
  299. DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
  300. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
  301. INT_ORDERID
  302. OPEN SETUPSCH
  303. FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  304. @SMALLINT_WEEK, @SMALLINT_YEAR
  305. WHILE (@@FETCH_STATUS <> -1 )
  306.     BEGIN
  307.         INSERT INTO #ORDERSCHHRS
  308.           SELECT DISTINCT S.REGION, S.INT_ORDERID, D.ISFULLTIME,
  309. D.MAXHOURS, SUM((S.STOP_TIME-S.START_TIME-
  310. S.BREAK2_STOP)*5.0/60),DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  311. +1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  312. +1,S.ORDERDATE))
  313.             FROM db_ORDERS..tbl_ORDERSCHEDULE AS S, #DATA2 AS D
  314.             WHERE DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  315. +1,S.ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
  316. DATEPART(DW,S.ORDERDATE)+1,S.ORDERDATE))=@SMALLINT_YEAR AND
  317. S.REGION=@STR_ORDERREGION AND D.REGION=@STR_ORDERREGION AND
  318. D.INT_ORDERID=@INT_ORDERID AND S.INT_ORDERID=@INT_ORDERID
  319.             GROUP BY S.REGION, S.INT_ORDERID, D.ISFULLTIME,
  320. D.MAXHOURS,DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  321. +1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  322. +1,S.ORDERDATE))
  323.         FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  324. @SMALLINT_WEEK, @SMALLINT_YEAR
  325.     END
  326. DEALLOCATE SETUPSCH
  327. CREATE INDEX D2 ON #ORDERSCHHRS (HRS, MX, INT_ORDERID, REGION, WK, YR)
  328.  
  329.  
  330. DECLARE CLRSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
  331. DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)),
  332. DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
  333. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
  334. INT_ORDERID
  335. OPEN CLRSCH
  336. FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  337. @SMALLINT_WEEK, @SMALLINT_YEAR
  338. WHILE (@@FETCH_STATUS <> -1 )
  339.     BEGIN
  340.         UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
  341. (PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED%'),UPPER(ERR))>0 OR
  342. PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0) AND
  343.             INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
  344. DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)
  345. +1,ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
  346. DATEPART(DW,ORDERDATE)+1,ORDERDATE))=@SMALLINT_YEAR
  347.         FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  348. @SMALLINT_WEEK, @SMALLINT_YEAR
  349.     END
  350. DEALLOCATE CLRSCH
  351.  
  352.  
  353. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
  354. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='13', ERR='MAX ORDERS EXCEEDED'
  355. WHERE
  356.   INT_ORDERID IN (SELECT C.INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  357. AS C WHERE SENTNAME=@STR_FILENAME AND
  358. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  359. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  360.   INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS AS S WHERE
  361. HRS>MX AND CTESCH.REGION=S.REGION AND
  362.                                              DATEPART(WW,DATEADD(DD,-
  363. DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.WK AND
  364.                                              DATEPART(YY,DATEADD(DD,-
  365. DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.YR)
  366.  
  367.  
  368. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
  369. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='12', ERR='NOT ENOUGH ORDERS
  370. SCHEDULED' WHERE
  371.   INT_ORDERID IN (SELECT C.INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  372. AS C WHERE SENTNAME=@STR_FILENAME AND
  373. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  374. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  375.   INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS WHERE FP=1 AND
  376. HRS<MX AND DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
  377. +1,CTESCH.ORDERDATE))=WK AND DATEPART(YY,DATEADD(DD,-
  378. DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=YR) AND
  379.   INT_ORDERID NOT IN
  380.     (SELECT INT_ORDERID FROM #ORDERNAHI AS E WHERE
  381. DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
  382. +1,CTESCH.ORDERDATE))=DATEPART(WW,DATEADD(DD,-DATEPART(DW,E.COL)
  383. +1,E.COL)) AND
  384.                                       DATEPART(YY,DATEADD(DD,-
  385. DATEPART(DW,CTESCH.ORDERDATE)
  386. +1,CTESCH.ORDERDATE))=DATEPART(YY,DATEADD(DD,-DATEPART(DW,E.COL)
  387. +1,E.COL)))
  388.  
  389.  
  390. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='MULTIPLE ORDERS' WHERE
  391.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  392. AS SS WHERE SENTNAME=@STR_FILENAME AND SS.ORDERDATE=CTESCH.ORDERDATE
  393. AND SS.REGION=CTESCH.REGION) AND
  394.   CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN
  395.     ( SELECT CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM
  396. db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
  397.              INT_ORDERID IN (SELECT INT_ORDERID FROM
  398. db_ORDERS..tbl_ORDERSTATUS AS SS WHERE SENTNAME=@STR_FILENAME AND
  399. SS.ORDERDATE=S.ORDERDATE AND SS.REGION=S.REGION)
  400.       GROUP BY CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION HAVING
  401. COUNT(*)>1)
  402.  
  403.  
  404. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='INVALID ORDER SCHEDULE'
  405. WHERE
  406.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  407. AS C WHERE SENTNAME=@STR_FILENAME AND
  408. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  409. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  410.   (STOP_TIME-START_TIME-BREAK2_STOP)/12.0 NOT IN (SELECT RTHRS FROM
  411. db_ORDERS..tbl_ORDERRECORD)
  412.  
  413.  
  414. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE ERR='MISSING
  415. ORDER' AND
  416.   CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN (SELECT
  417. CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM #KEY)
  418.  
  419.  
  420. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='MISSING ORDER'
  421.   WHERE (SELECT ISNULL(SUM(DVALUE),0) FROM
  422. db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
  423. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
  424. db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
  425. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND
  426. SYSTEM_COD='DDDL')>0 AND
  427.         (SELECT ISNULL(SUM(DVALUE),0) FROM
  428. db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
  429. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
  430. db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
  431. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND SYSTEM_COD
  432. IN ('MISA','KIPA'))=0
  433.         AND SYSTEM_COD='DDDL' AND ORDERDATE>=@STR_MINIMUM_DATE AND
  434. ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999 AND INT_ORDERID IN
  435. (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
  436. SENTNAME=@STR_FILENAME AND S.ORDERDATE=CTEEXC.ORDERDATE AND
  437. S.REGION=CTEEXC.REGION)
  438.  
  439.  
  440. SELECT ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
  441. AS E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
  442. E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS DSUM,
  443.        ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
  444. AS E1 WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE
  445. AND E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS EHSUM,
  446. ORDERDATE, INT_ORDERID
  447.     INTO #EHPROB
  448.     FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
  449.         SENTNAME=@STR_FILENAME
  450. DELETE FROM #EHPROB WHERE DSUM<=EHSUM
  451.  
  452.  
  453. INSERT INTO #EHPROB
  454.     SELECT 0, 0, ORDERDATE, INT_ORDERID
  455.         FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
  456.            (SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS
  457. E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
  458. E1.INT_ORDERID=S.INT_ORDERID)> 0 AND
  459.            (SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E1
  460. WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE AND
  461. E1.INT_ORDERID=S.INT_ORDERID)=0
  462.            AND SENTNAME=@STR_FILENAME
  463.  
  464.  
  465. INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #EHPROB
  466.  
  467.  
  468. INSERT INTO #ORDERSMAP SELECT DISTINCT SECONDARY, RECTYPE, COST FROM
  469. db_ORDERS..tbl_ORDERMAP WHERE REGION=@STR_ORDERREGION
  470. CREATE INDEX RecLoop ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
  471. INSERT INTO #ORDERNONSSCH SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
  472. db_ORDERS..tbl_ORDERSCHEDULE WHERE
  473.         ( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  474. ORDERS%'),UPPER(ERR))>0
  475.          OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
  476.          OR PATINDEX(UPPER('%SUM OF ORDERS FOR THIS DAY GREATER THAN
  477. SCHEDULE ALLOWS%' ),UPPER(ERR))>0
  478.          OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  479. %'),UPPER(ERR))>0 ) AND
  480.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  481. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  482. C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)
  483.  
  484.  
  485. INSERT INTO #ORDERNONSSCH2 SELECT DISTINCT INT_ORDERID,
  486. DATEPART(WW,ORDERDATE), DATEPART(YY,ORDERDATE) FROM
  487. db_ORDERS..tbl_ORDERSCHEDULE WHERE
  488.         ( PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED
  489. %'),UPPER(ERR))>0
  490.          OR PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0
  491.          OR PATINDEX(UPPER('%INVALID ORDER SCHEDULE%'),UPPER(ERR))>0
  492.          OR PATINDEX(UPPER('%MISSING ORDER%'),UPPER(ERR))>0 ) AND
  493. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
  494.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  495. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  496. C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)
  497.  
  498.  
  499. INSERT INTO #ORDERNONSEXC SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
  500. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
  501.         ( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  502. ORDERS%'),UPPER(ERR))>0
  503.          OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
  504.          OR PATINDEX(UPPER('%MISSING ORDER%' ),UPPER(ERR))>0
  505.          OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  506. %'),UPPER(ERR))>0  AND ORDERDATE>=@STR_MINIMUM_DATE AND
  507. ORDERDATE<=@STR_MAXIMUM_DATE) AND
  508.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  509. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  510. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
  511. INSERT INTO #ORDERNONS SELECT INT_ORDERID,ORDERDATE FROM
  512. db_ORDERS..tbl_ORDEREXCEPTIONS
  513.     WHERE SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
  514. db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
  515. REGION=@STR_ORDERREGION ) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  516. ORDERDATE<=@STR_MAXIMUM_DATE AND
  517.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  518. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  519. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
  520.  
  521.  
  522. INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #KEY
  523. WHERE
  524.         INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
  525. db_ORDERS..tbl_ORDERSCHEDULE WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
  526. ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
  527.         INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
  528. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
  529. ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
  530.         INT_ORDERID>999999
  531.  
  532.  
  533. INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM
  534. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='UMSL' AND
  535. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
  536. INT_ORDERID>999999 AND
  537.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  538. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  539. C.REGION=CTEEXC.REGION)
  540.  
  541.  
  542. CREATE CLUSTERED INDEX K1 ON #KEY (INT_ORDERID)
  543. CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH (INT_ORDERID,COL)
  544. CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH2 (INT_ORDERID)
  545. CREATE CLUSTERED INDEX S1 ON #ORDERNONSEXCSCH (INT_ORDERID)
  546. CREATE CLUSTERED INDEX E1 ON #ORDERNONSEXC (INT_ORDERID,COL)
  547. CREATE INDEX iNOSUB ON #ORDERNONS (INT_ORDERID,COL)
  548. CREATE INDEX iSECONDARYMAP ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
  549. CREATE INDEX iNONR ON #NONRINT_ORDERID (INT_ORDERID)
  550.  
  551.  
  552. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='', STR_UPDATED='15',
  553. SUBMTD='0' WHERE
  554.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  555. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
  556. C.REGION=CTESCH.REGION) AND
  557.     INT_ORDERID IN (SELECT INT_ORDERID FROM
  558. db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE E.ORDERDATE=CTESCH.ORDERDATE
  559. AND E.REGION=CTESCH.REGION AND STR_UPDATED>SUBMTD AND
  560. LEN(RTRIM(LTRIM(SYSTEM_COD)))>0) AND
  561.     INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
  562. #ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTESCH.ORDERDATE) AND
  563.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
  564.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
  565.     INT_ORDERID NOT IN (SELECT #ORDERNONSSCH.INT_ORDERID FROM
  566. #ORDERNONSSCH WHERE #ORDERNONSSCH.COL=CTESCH.ORDERDATE)
  567.  
  568.  
  569. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  570. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='17' WHERE INT_ORDERID>999999
  571. AND INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  572. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  573. C.REGION=CTEEXC.REGION) AND
  574.       ( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  575. ORDERS%'),UPPER(ERR))>0
  576.     OR PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))>0
  577.     OR PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))>0 )
  578. AND ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE
  579.  
  580.  
  581. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  582. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD=@STR_ORDERSUBDATE2 WHERE
  583. REGION=@STR_ORDERREGION AND INT_ORDERID>999999 AND INT_ORDERID IN
  584. (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
  585. SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  586. C.REGION=CTEEXC.REGION) AND
  587.     PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  588. %'),UPPER(ERR))>0
  589.  
  590.  
  591. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='', STR_UPDATED='15',
  592. SUBMTD='0' WHERE
  593.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  594. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  595. C.REGION=CTEEXC.REGION) AND
  596.     INT_ORDERID IN (SELECT INT_ORDERID FROM
  597. db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD AND
  598. INT_ORDERID>999999 AND ORDERDATE>=@STR_MINIMUM_DATE AND
  599. ORDERDATE<=@STR_MAXIMUM_DATE AND S.ORDERDATE=CTEEXC.ORDERDATE AND
  600. S.REGION=CTEEXC.REGION AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  601. #KEY)) AND
  602.     INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
  603. #ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
  604.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
  605.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
  606.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
  607. #ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND
  608.     LEN(RTRIM(LTRIM(SYSTEM_COD)))>0 AND LTRIM(RTRIM(SUBMTD)) NOT IN
  609. ('16','17')
  610.  
  611.  
  612. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18',
  613. STR_UPDATED='0', ERR=LTRIM(RTRIM(SYSTEM_COD)) + ' ' +
  614. @STR_ORDERNOTMADE WHERE (SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
  615. db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
  616. REGION=CTEEXC.REGION ) OR SYSTEM_COD='BILL') AND
  617. REGION=@STR_ORDERREGION AND SUBMTD<STR_UPDATED AND INT_ORDERID>999999
  618. AND INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  619. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  620. C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  621. ORDERDATE<=@STR_MAXIMUM_DATE
  622. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18', STR_UPDATED='0'
  623. WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
  624. #ORDERNONS.COL=CTEEXC.ORDERDATE) AND INT_ORDERID>999999 AND
  625. INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
  626. C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  627. C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  628. ORDERDATE<=@STR_MAXIMUM_DATE
  629. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET SUBMTD='18', STR_UPDATED='0'
  630. WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
  631. #ORDERNONS.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
  632. INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
  633. C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
  634. C.REGION=CTESCH.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  635. ORDERDATE<=@STR_MAXIMUM_DATE
  636.  
  637.  
  638. IF @@ROWCOUNT>0
  639.   BEGIN
  640.     CREATE INDEX iMHIR ON #ORDERNAHI (INT_ORDERID, COL)
  641.     UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='UMSL EXISTS FOR
  642. ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
  643.       WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
  644. #ORDERNAHI.COL=CTEEXC.ORDERDATE) AND SYSTEM_COD!='BILL' AND
  645. INT_ORDERID>999999 AND
  646.             INT_ORDERID IN (SELECT INT_ORDERID FROM
  647. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  648. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
  649.             ORDERDATE>=@STR_MINIMUM_DATE AND
  650. ORDERDATE<=@STR_MAXIMUM_DATE
  651.     UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='UMSL EXISTS FOR
  652. ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
  653.       WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
  654. #ORDERNAHI.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
  655.             INT_ORDERID IN (SELECT INT_ORDERID FROM
  656. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  657. C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION) AND
  658.             ORDERDATE>=@STR_MINIMUM_DATE AND
  659. ORDERDATE<=@STR_MAXIMUM_DATE
  660.   END
  661.  
  662.  
  663. INSERT INTO #ORDEREXC SELECT INT_ORDERID, LTRIM(RTRIM(ORDERDATE)) AS
  664. ORDERDATE, SYSTEM_COD, CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD,
  665. STR_CSTCD FROM db_ORDERS..tbl_ORDEREXCEPTIONS
  666.   WHERE INT_ORDERID>999999 AND STR_UPDATED>SUBMTD AND
  667. PATINDEX(UPPER('%Invalid Elink Code%'),UPPER(ERR))=0 AND
  668. PATINDEX(UPPER('%Unmapped RT Code%'),UPPER(ERR))=0 AND SYSTEM_COD!=''
  669. AND
  670.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
  671. #ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
  672.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
  673. WHERE NS.WK=DATEPART(WW,CTEEXC.ORDERDATE) AND
  674. NS.YR=DATEPART(WW,CTEEXC.ORDERDATE)) AND
  675.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
  676. #ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND INT_ORDERID IN (SELECT
  677. INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
  678. SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  679. C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  680. ORDERDATE<=@STR_MAXIMUM_DATE AND
  681.        PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  682. ORDERS%'),UPPER(ERR))=0
  683.     AND PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  684. %'),UPPER(ERR))=0
  685.     AND PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))=0
  686.     AND PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))=0
  687.   ORDER BY ORDERDATE
  688.  
  689.  
  690. INSERT INTO #ORDERSCH SELECT DISTINCT INT_ORDERID,
  691. LTRIM(RTRIM(ORDERDATE)) AS ORDERDATE, (STOP_TIME-START_TIME-
  692. BREAK2_STOP)*5.0/60 AS SCHLEN,
  693.         (SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
  694. WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
  695. E.REGION=S.REGION AND SYSTEM_COD='NDAY') AS NITE,
  696.         (SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
  697. WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
  698. E.REGION=S.REGION AND SYSTEM_COD='SPTN') AS NSPT,
  699.         (SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
  700. WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
  701. E.REGION=S.REGION AND SYSTEM_COD='CRSM') AS MHOL
  702.     FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD
  703. AND INT_ORDERID>999999 AND
  704.       INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
  705. #ORDERNONSEXC.COL=S.ORDERDATE) AND
  706.       INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
  707. WHERE NS.WK=DATEPART(WW,S.ORDERDATE) AND
  708. NS.YR=DATEPART(YY,S.ORDERDATE)) AND
  709.       INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
  710. #ORDERNONSSCH.COL=S.ORDERDATE) AND
  711.       INT_ORDERID IN (SELECT INT_ORDERID FROM
  712. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  713. C.ORDERDATE=S.ORDERDATE AND C.REGION=S.REGION) AND
  714. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
  715.        PATINDEX(UPPER('%MINIMUM ORDERS NOT MET%'),UPPER(ERR))=0 AND
  716.        PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))=0
  717.     ORDER BY INT_ORDERID, ORDERDATE
  718.  
  719.  
  720. CREATE INDEX iEXC1a ON #ORDEREXC (INT_ORDERID, ORDERDATE, SYSTEM_COD,
  721. CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD, STR_CSTCD)
  722. CREATE INDEX iEXC1b ON #ORDEREXC (SYSTEM_COD, INT_ORDERID, ORDERDATE,
  723. DVALUE)
  724. CREATE INDEX iSCHa ON #ORDERSCH (INT_ORDERID, ORDERDATE, SCHLEN)
  725.  
  726.  
  727. DECLARE EMPS CURSOR FOR SELECT DISTINCT (INT_ORDERID/2)+1134 FROM #KEY
  728. WHERE INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH)
  729. ORDER BY INT_ORDERID
  730. OPEN EMPS
  731. FETCH NEXT FROM EMPS INTO @INT_ORDERID
  732. TRUNCATE TABLE db_ORDERS..tbl_ORDERDUMPRAW
  733. INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'HEDR0000' +
  734. RIGHT('0000'+RTRIM(CONVERT(CHAR(4),@INT_SEQUENCE)),4) +
  735. CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(6),GETDATE(),
  736. 108),':','')
  737. SELECT @INT_COUNT=1
  738. WHILE (@@FETCH_STATUS <> -1 )
  739.     BEGIN
  740.         SELECT @STR_SECCODE = 'x '+CONVERT(CHAR(9),@INT_ORDERID)+' jk
  741. 9.216.12'
  742.         SELECT @STR_USER_ID='INVALID'
  743.         SELECT @STR_USER_ID=STR_USER_ID FROM
  744. db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
  745. LTRIM(RTRIM(STR_USER_ID))!=' '  AND STR_USER_ID IS NOT NULL
  746.         IF @STR_USER_ID='INVALID'
  747.           BEGIN
  748.  
  749.  
  750.           END
  751.         ELSE
  752.           BEGIN
  753.             INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
  754. 'JKUL0001        ' + RIGHT('00000000' +
  755. LTRIM(RTRIM(CONVERT(CHAR(9),@INT_ORDERID))),9) + '       ' +
  756. SPACE(80) + 'CAP' + @STR_SECCODE
  757.             SELECT @STR_ORDEROFFICE=MAX(OFFICE) FROM
  758. db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
  759. REGION=@STR_ORDERREGION
  760.             SELECT @INT_COUNT=@INT_COUNT+1
  761.             DECLARE SCHS CURSOR FOR SELECT ORDERDATE, SCHLEN, NITE,
  762. NPST, MHOL FROM #ORDERSCH
  763.                                         WHERE INT_ORDERID=@INT_ORDERID
  764. AND ORDERDATE NOT IN (SELECT ORDERDATE FROM #ORDEREXC WHERE
  765. INT_ORDERID=@INT_ORDERID AND SYSTEM_COD='BILL' AND
  766. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE) AND
  767.  
  768.  
  769. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE ORDER BY
  770. ORDERDATE
  771.             OPEN SCHS
  772.             FETCH NEXT FROM SCHS INTO @STR_ORDERDATE, @DEC_ORDERLEN,
  773. @SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
  774.             WHILE (@@FETCH_STATUS <> -1 )
  775.                 BEGIN
  776.                     SELECT @INT_CNT=COUNT(*) FROM #NONRINT_ORDERID
  777. WHERE INT_ORDERID=@INT_ORDERID
  778.                     IF @INT_CNT>0 OR @SENDRECORD=1
  779.                         BEGIN
  780.                             INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW
  781. SELECT 'RKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + @STR_SECCODE
  782.                             SELECT @INT_COUNT=@INT_COUNT+1
  783.                         END
  784.                     IF @SMALLINT_OVERNIGHT>0 SELECT
  785. @SMALLINT_OVERNIGHT=1
  786.                     IF @SMALLINT_FEES>0 SELECT @SMALLINT_FEES=1
  787.                     IF @DECIMAL_HOLIDAY>0 SELECT
  788. @STR_ORDERSCH=SECONDARYHRS FROM db_ORDERS..tbl_ORDERRECORD WHERE
  789. RTHRS=@DECIMAL_HOLIDAY AND NDIFF=@SMALLINT_OVERNIGHT AND
  790. CARFARE=@SMALLINT_FEES
  791.                     ELSE SELECT @STR_ORDERSCH=SECONDARYHRS FROM
  792. db_ORDERS..tbl_ORDERRECORD WHERE RTHRS=@DEC_ORDERLEN AND
  793. NDIFF=@SMALLINT_OVERNIGHT AND CARFARE=@SMALLINT_FEES
  794.                     INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
  795. 'ZKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + '01' + @STR_ORDERSCH +
  796. '                 ' + @STR_TAX + @STR_SECCODE
  797.                     SELECT @INT_COUNT=@INT_COUNT+1
  798.                     DECLARE EXCS CURSOR FOR
  799.                         SELECT E.ORDERDATE, M.SECONDARY,
  800. LTRIM(RTRIM(M.RECTYPE)),
  801. CONVERT(CHAR(6),CONVERT(DECIMAL(6,2),E.DVALUE)), LEFT(E.RSN,6),
  802. E.STR_CSTCD, M.COST
  803.                             FROM #ORDEREXC AS E, #ORDERSMAP AS M
  804.                             WHERE E.INT_ORDERID=@INT_ORDERID AND
  805. E.SYSTEM_COD=M.SECONDARY AND E.ORDERDATE=@STR_ORDERDATE AND
  806. E.SYSTEM_COD NOT LIKE '%?%'
  807.                             ORDER BY E.ORDERDATE, M.RECTYPE, M.COST
  808.                     OPEN EXCS
  809.                     FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
  810. @STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
  811. @STR_COSTCOD, @STR_COST
  812.                     WHILE (@@FETCH_STATUS <> -1 )
  813.                          BEGIN
  814.                             IF @STR_ORDERTYPE = 'JKUL'
  815.                                  BEGIN
  816.                                      INSERT INTO
  817. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'JKUL0001' +
  818. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  819. @STR_ORDERRSN + SPACE(15) + @STR_SECCODE
  820.                                      SELECT @INT_COUNT=@INT_COUNT+1
  821.                                  END
  822.                             IF @STR_ORDERTYPE = 'RKUL'
  823.                                  BEGIN
  824.                                      INSERT INTO
  825. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RKUL0001' +
  826. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  827. @STR_SECCODE
  828.                                      SELECT @INT_COUNT=@INT_COUNT+1
  829.                                  END
  830.                             IF @STR_ORDERTYPE = 'ZKUL'
  831.                                  BEGIN
  832.                                      SELECT @STR_ACTCH=ACT_CHG,
  833. @STR_COSTCNTR=COST_CNTR, @STR_LOCCODE=LOC_CODE,
  834. @STR_ORDERNUM=PROJ_NUM, @STR_EC=CONVERT(CHAR(1),ENV_CODE) FROM
  835. db_ORDERS..tbl_ORDERCOST WHERE DIST_COD=@STR_COSTCOD AND
  836. OFFICE=@STR_ORDEROFFICE
  837.                                      IF @STR_COST='*' AND
  838. @@ROWCOUNT>0
  839.                                          BEGIN
  840.                                              INSERT INTO
  841. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
  842. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  843. @STR_ACTCH + @STR_COSTCNTR + @STR_LOCCODE + SPACE(6) + @STR_ORDERNUM +
  844. @STR_EC + SPACE(111) + @STR_TAX + @STR_SECCODE
  845.                                              SELECT
  846. @INT_COUNT=@INT_COUNT+1
  847.                                          END
  848.                                      ELSE
  849.                                          BEGIN
  850.                                              IF @STR_ORDERREMOTE !=
  851. 'DDDL'
  852.                                                  BEGIN
  853.                                                      INSERT INTO
  854. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
  855. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  856. SPACE(146) + @STR_TAX + @STR_SECCODE
  857.                                                      SELECT
  858. @INT_COUNT=@INT_COUNT+1
  859.                                                  END
  860.                                              ELSE
  861.                                                  BEGIN
  862.                                                      SELECT
  863. @INT_TAXSUM=SUM(DVALUE) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
  864. SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
  865. ORDERDATE=@STR_ORDERDATE
  866.                                                      SELECT
  867. @INT_TAXCNT=COUNT(*) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
  868. SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
  869. ORDERDATE=@STR_ORDERDATE
  870.                                                      SELECT
  871. @INT_DSUM=SUM(DVALUE) FROM #ORDEREXC WHERE SYSTEM_COD='DDDL' AND
  872. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE
  873.                                                      IF
  874. @INT_DSUM<=@INT_TAXSUM AND @INT_TAXCNT>0
  875.                                                          BEGIN
  876.                                                              INSERT
  877. INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
  878. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  879. SPACE(146) + @STR_TAX + @STR_SECCODE
  880.                                                              SELECT
  881. @INT_COUNT=@INT_COUNT+1
  882.                                                          END
  883.                                                      ELSE
  884.                                                          BEGIN
  885.                                                              IF
  886. @INT_DSUM>@INT_TAXSUM SELECT @STR_MISTAKE = 'ORDER IS TOO SHORT'
  887.                                                              ELSE
  888. SELECT @STR_MISTAKE = 'ORDER MUST HAVE EXCEPTION ON SAME DAY'
  889.                                                              UPDATE
  890. db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=@STR_MISTAKE WHERE
  891. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  892. SYSTEM_COD='DDDL'
  893.                                                          END
  894.                                                  END
  895.                                          END
  896.                                  END
  897.                              IF @STR_ORDERTYPE = 'QKUL' OR
  898. @STR_ORDERTYPE = 'ZKUL'
  899.                                  BEGIN
  900.                                      IF @STR_ORDERTYPE = 'ERECM'
  901. INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
  902. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + SPACE(13) +
  903. RIGHT(SPACE(7)+RTRIM(ROUND(@STR_ORDERINFO,0)),7) + SPACE(35) +
  904. @STR_TAX + @STR_SECCODE
  905.                                      ELSE INSERT INTO
  906. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
  907. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
  908. RIGHT(SPACE(13)+RTRIM(ROUND(@STR_ORDERINFO,0))+'.00',13) + SPACE(7) +
  909. SPACE(35) + @STR_TAX + @STR_SECCODE
  910.                                      SELECT @INT_COUNT=@INT_COUNT+1
  911.                                  END
  912.                              IF @STR_ORDERTYPE = 'WKUL'
  913.                                  BEGIN
  914.                                      INSERT INTO
  915. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
  916. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
  917. RIGHT(SPACE(13)+@STR_ORDERINFO,13) + SPACE(35) + @STR_TAX +
  918. @STR_SECCODE
  919.                                      SELECT @INT_COUNT=@INT_COUNT+1
  920.                                  END
  921.                              IF @STR_ORDERTYPE = 'PLAXC'
  922.                                  BEGIN
  923.                                      INSERT INTO
  924. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
  925. REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
  926. @STR_ORDERREMOTE + SPACE(10) + REPLACE(@STR_ORDERDATE,'-','') +
  927. LEFT(LTRIM(RTRIM(ROUND(@STR_ORDERINFO,0)))+SPACE(5),5) + SPACE(58) +
  928. @STR_SECCODE
  929.                                      SELECT @INT_COUNT=@INT_COUNT+1
  930.                                  END
  931.                              IF @STR_ORDERTYPE = 'UKLQQ'
  932.                                  BEGIN
  933.                                      INSERT INTO
  934. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
  935. REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
  936. @STR_ORDERREMOTE + RIGHT(SPACE(10)+@STR_ORDERINFO,10) +
  937. REPLACE(@STR_ORDERDATE,'-','') + SPACE(63) + @STR_SECCODE
  938.                                      SELECT @INT_COUNT=@INT_COUNT+1
  939.                                  END
  940.                              FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
  941. @STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
  942. @STR_COSTCOD, @STR_COST
  943.                          END
  944.                     DEALLOCATE EXCS
  945.                     UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
  946. SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
  947. ORDERDATE=@STR_ORDERDATE
  948.                     UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  949. SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
  950. ORDERDATE=@STR_ORDERDATE AND SYSTEM_COD>'' AND SYSTEM_COD NOT LIKE '?
  951. %'
  952.                     FETCH NEXT FROM SCHS INTO @STR_ORDERDATE,
  953. @DEC_ORDERLEN, @SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
  954.                 END
  955.             DEALLOCATE SCHS
  956.         END
  957.     FETCH NEXT FROM EMPS INTO @INT_ORDERID
  958. END
  959. DEALLOCATE EMPS
  960. INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'TRLR' +
  961. RIGHT('00000000' + LTRIM(RTRIM(CONVERT(CHAR(8),@INT_COUNT+1))),8)
  962.  
  963.  
  964. DELETE db_ORDERS..tbl_LOCKEDORDERS WHERE LOCKED = 'ORDER_SUBMISSION'
  965. GO
UncleRico
Newbie
Newbie
 
Posts: 4
Joined: Wed Oct 17, 2007 2:58 pm
Unrated

Re: NEED TO GO FASTER

Postby AlexCuse on Wed Oct 17, 2007 3:18 pm

There you go, cursing our site from the beginning ;)
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: NEED TO GO FASTER

Postby kaht on Wed Oct 17, 2007 4:29 pm

Personally, I would use more cursors.

Oh, and make sure that you don't use any lowercase letters.

Now give me a purple star for my helpful tips!
#8
AlexCuse is n better than me.
User avatar
kaht
LTD Admin
LTD Admin
LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252
LTD Silver - Rating: 252
 
Posts: 1383
Joined: Wed Oct 10, 2007 6:49 pm
Location: STL
Unrated

Re: NEED TO GO FASTER

Postby ptheriault on Wed Oct 17, 2007 6:50 pm

I think you could speed this up a lot if you used truncate table statements in the first few lines of your code. :D
--Paul
--If at first you don't succeed destroy all evidence that you tried.
User avatar
ptheriault
LTD Admin
LTD Admin
LTD Bronze - Rating: 107LTD Bronze - Rating: 107LTD Bronze - Rating: 107
 
Posts: 640
Joined: Thu Oct 11, 2007 6:28 pm
Location: Warwick, RI

Re: NEED TO GO FASTER

Postby SQLDenis on Wed Oct 17, 2007 6:55 pm

Here is a trick to make it soooo much faster

Highlight all the code then press <CTRL + SHIFT +C> and then hit F5
See the difference?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: NEED TO GO FASTER

Postby AlexCuse on Wed Oct 17, 2007 8:22 pm

WOW THAT IS REALLY FAST.

U R MY HERO SQLDENIS. ONLY YOU AND CELKO KNOW WHAT YOUR TALKING ABOUT.
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: NEED TO GO FASTER

Postby UncleRico on Wed Oct 17, 2007 8:26 pm

AlexCuse wrote:WOW THAT IS REALLY FAST.

U R MY HERO SQLDENIS. ONLY YOU AND CELKO KNOW WHAT YOUR TALKING ABOUT.


Don't try to hijack my thread
SQLDenis your solution is rubbish it doesn't do anything, it makes the query pretty green but it doesn't really seem to do anything. What does the green color do? Does it mean that it is parsed okay when it is green?
However I don't care about the color I want it to run fast
UncleRico
Newbie
Newbie
 
Posts: 4
Joined: Wed Oct 17, 2007 2:58 pm
Unrated

Re: NEED TO GO FASTER

Postby AlexCuse on Wed Oct 17, 2007 8:54 pm

I DOES NOT HIJACK ANY THREADS.

I USE THIS SAME PROC IN MY DATABASE. IT VERY USEFUL BUT TAKEN LONG TIME. IF I USED SQLDENIS SUGGESTION IT RUNS IN VERY VERY FAST. QUERY TURNS PRETTY GREEN BECAUSE GREEN MEANS GO, EVEN RETIRED KNOWS THAT!

YOU MUST BE IDIOT TO NOT THINK THIS SOLUTION. AND ALSO BECAUSE YOU TYPE IN MIXED CASE. KEYBOARD R NOT FREE!!!
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US

Re: NEED TO GO FASTER

Postby SQLDenis on Wed Oct 17, 2007 8:58 pm

AlexCuse wrote:EVEN RETIRED KNOWS THAT!



Now, you sound like Borat, very nice, high five!
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: NEED TO GO FASTER

Postby kaht on Wed Oct 17, 2007 9:27 pm

I think someone hijacked U.R.'s account. He never speaks with grammar that well, and he also has no need for punctuation.
#8
AlexCuse is n better than me.
User avatar
kaht
LTD Admin
LTD Admin
LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252
LTD Silver - Rating: 252
 
Posts: 1383
Joined: Wed Oct 10, 2007 6:49 pm
Location: STL
Unrated

Re: NEED TO GO FASTER

Postby SQLDenis on Mon Dec 15, 2008 6:03 pm

Hey, did you ever resolve your problem?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: NEED TO GO FASTER

Postby Emtucifor on Mon Dec 15, 2008 6:37 pm

Denis, you shouldn't talk about special people like this as having problems, since they have no control over them, and deserve our compassion.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: NEED TO GO FASTER

Postby Thirster42 on Tue Dec 16, 2008 5:26 pm

I just barely looked at the code but... is he cursing through everything even though he could just use select statements?
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: NEED TO GO FASTER

Postby kaht on Tue Dec 16, 2008 5:44 pm

Thirster42, you need to realize that UncleRico isn't just a user on this site. He's a legend that writes legendary code.
#8
AlexCuse is n better than me.
User avatar
kaht
LTD Admin
LTD Admin
LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252LTD Silver - Rating: 252
LTD Silver - Rating: 252
 
Posts: 1383
Joined: Wed Oct 10, 2007 6:49 pm
Location: STL
Unrated

Re: NEED TO GO FASTER

Postby Chopstik on Tue Dec 16, 2008 7:50 pm

kaht wrote:Thirster42, you need to realize that UncleRico isn't just a user on this site. He's a legend that writes legendary code.

"Legendary" is certainly one word for it... :lol:
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: NEED TO GO FASTER

Postby SQLDenis on Wed Sep 25, 2013 1:57 pm

How come I don't see the code block? Oh yes I know this post is 5 years old but I wanted to look at the awesome SQL again
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: NEED TO GO FASTER

Postby AlexCuse on Wed Sep 25, 2013 7:00 pm

weird. maybe theres a length limit or something? Wonder if it will work in a quote

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

  1. CREATE PROCEDURE SP_ORDERSUBMISSION @SENDRECORD BIT, @TYPE CHAR(3) AS
  2. SET NOCOUNT ON
  3.  
  4.  
  5. DECLARE @STR_USER_ID CHAR(6)
  6. DECLARE @STR_ORDERREGION CHAR(1)
  7. DECLARE @STR_ORDEROFFICE CHAR(4)
  8. DECLARE @STR_MINIMUM_DATE CHAR(10)
  9. DECLARE @STR_MAXIMUM_DATE CHAR(10)
  10. DECLARE @STR_CDSM CHAR(50)
  11. DECLARE @STR_MISTAKE CHAR(100)
  12. DECLARE @INT_DSUM INT
  13. DECLARE @INT_TAXSUM INT
  14. DECLARE @INT_TAXCNT INT
  15. DECLARE @STR_ACTCH CHAR(6)
  16. DECLARE @STR_COSTCNTR CHAR(9)
  17. DECLARE @STR_LOCCODE CHAR(6)
  18. DECLARE @STR_ORDERNUM CHAR(7)
  19. DECLARE @STR_EC CHAR(1)
  20. DECLARE @STR_ORDERNOTMADE CHAR(27)
  21. DECLARE @STR_SECCODE CHAR(29)
  22. DECLARE @STR_TAX CHAR(4)
  23. DECLARE @INT_SEQUENCE INT
  24. DECLARE @STR_ORDERSCH CHAR(4)
  25. DECLARE @DEC_ORDERLEN DECIMAL(6,2)
  26. DECLARE @INT_ORDERID INT
  27. DECLARE @STR_ORDERDATE CHAR(10)
  28. DECLARE @STR_ORDERINFO CHAR(5)
  29. DECLARE @STR_ORDERREMOTE CHAR(4)
  30. DECLARE @STR_ORDERRSN CHAR(6)
  31. DECLARE @INT_COUNT INT
  32. DECLARE @SMALLINT_OVERNIGHT SMALLINT
  33. DECLARE @SMALLINT_FEES SMALLINT
  34. DECLARE @DECIMAL_HOLIDAY DECIMAL(6,2)
  35. DECLARE @STR_ORDERSUBDATE1 CHAR(14)
  36. DECLARE @STR_ORDERSUBDATE2 CHAR(14)
  37. DECLARE @STR_ORDERTYPE CHAR(6)
  38. DECLARE @STR_COSTCOD CHAR(4)
  39. DECLARE @STR_COST CHAR(1)
  40. DECLARE @INT_CNT INT
  41. DECLARE @DECIMAL_MAXORDER DECIMAL(6,2)
  42. DECLARE @STR_NUM CHAR(2)
  43. DECLARE @STR_FILENAME CHAR(21)
  44. DECLARE @SMALLINT_WEEK SMALLINT
  45. DECLARE @SMALLINT_YEAR SMALLINT
  46. DECLARE @STR_METHOD CHAR(1)
  47. DECLARE @INT_EXTRA INT
  48.  
  49.  
  50. SELECT @INT_CNT=COUNT(*) FROM db_ORDERS..tbl_LOCKEDORDERS WHERE
  51. LOCKED='ORDER_SUBMISSION'
  52. IF @INT_CNT=0
  53.     BEGIN
  54.         INSERT INTO db_ORDERS..tbl_LOCKEDORDERS VALUES
  55. ('ORDER_SUBMISSION', GETDATE())
  56.     END
  57. ELSE
  58.     BEGIN
  59.         RETURN
  60.     END
  61.  
  62.  
  63. SELECT @STR_ORDERSUBDATE1=CONVERT(CHAR(4),DATEPART(YY,GETDATE())) +
  64. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,GETDATE()))),2) +
  65. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,GETDATE()))),2) +
  66. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,GETDATE()))),2) +
  67. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,GETDATE()))),2) +
  68. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,GETDATE()))),2)
  69. SELECT @STR_ORDERSUBDATE2=CONVERT(CHAR(4),DATEPART(YY,DATEADD(SS,
  70. 2,GETDATE()))) +
  71. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,DATEADD(SS,2,GETDATE())))),
  72. 2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,DATEADD(SS,
  73. 2,GETDATE())))),2) +
  74. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,DATEADD(SS,2,GETDATE())))),
  75. 2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,DATEADD(SS,
  76. 2,GETDATE())))),2) +
  77. RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,DATEADD(SS,2,GETDATE())))),
  78. 2)
  79. SELECT @STR_TAX = '    '
  80. SELECT @STR_ORDERNOTMADE = 'Order cannot be sent'
  81.  
  82.  
  83. CREATE TABLE #ORDEREXC (INT_ORDERID INT, ORDERDATE CHAR
  84. (10),SYSTEM_COD CHAR (5),CODE_TYPE CHAR (1),DVALUE FLOAT,RSN CHAR
  85. (12),STR_UPDATED CHAR (14),SUBMTD CHAR (14),STR_CSTCD CHAR (4) )
  86. CREATE TABLE #ORDERSCH (INT_ORDERID INT, ORDERDATE CHAR(10), SCHLEN
  87. DECIMAL(6,2), NITE SMALLINT, NPST SMALLINT, MHOL DECIMAL(6,2))
  88. CREATE TABLE #ORDERNAHI (INT_ORDERID INT, COL CHAR(10))
  89. CREATE TABLE #ORDERNONS (INT_ORDERID INT, COL CHAR(10))
  90. CREATE TABLE #ORDERSMAP (SECONDARY CHAR(6), RECTYPE CHAR(6), COST
  91. CHAR(1))
  92. CREATE TABLE #ORDERNONSSCH (INT_ORDERID INT, COL CHAR(10))
  93. CREATE TABLE #ORDERNONSSCH2 (INT_ORDERID INT, WK SMALLINT, YR
  94. SMALLINT)
  95. CREATE TABLE #ORDERNONSEXCSCH (INT_ORDERID INT)
  96. CREATE TABLE #ORDERNONSEXC (INT_ORDERID INT, COL CHAR(10))
  97. CREATE TABLE #NONRINT_ORDERID (INT_ORDERID INT)
  98. CREATE TABLE #KEY (REGION CHAR(1), INT_ORDERID INT)
  99.  
  100.  
  101. SELECT
  102. @STR_NUM=LEFT('0'+LTRIM(CONVERT(CHAR(2),CONVERT(INT,ISNULL(MAX(SUBSTRING(SE­NTNAME,
  103. 15,2)),'00')) + 1)),2) FROM db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME!
  104. ='' AND SUBSTRING(SENTNAME,15,2)>='01' AND SUBSTRING(SENTNAME,
  105. 15,2)<='99'
  106. IF @STR_NUM<'21' OR @STR_NUM>'50' SELECT
  107. @STR_FILENAME='ordr.ifa'+RTRIM(LTRIM(@STR_NUM))+'x.495'
  108. IF @STR_NUM>'20' AND @STR_NUM<'51' SELECT
  109. @STR_FILENAME='mistk.c5l'+RTRIM(LTRIM(@STR_NUM))+'x.495'
  110. UPDATE db_ORDERS..tbl_ORDERSTATUS SET SENTNAME=@STR_FILENAME WHERE
  111. SENTNAME='' AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  112. db_ORDERS..tbl_ORDERCUSTOMERS WHERE OFFICE='ORDR')
  113. IF @@ROWCOUNT=0
  114.   BEGIN
  115.     RETURN
  116.   END
  117.  
  118.  
  119. INSERT INTO #KEY SELECT DISTINCT REGION, INT_ORDERID FROM
  120. db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME=@STR_FILENAME
  121. SELECT @STR_MINIMUM_DATE=MIN(ORDERDATE),
  122. @STR_MAXIMUM_DATE=MAX(ORDERDATE) FROM db_ORDERS..tbl_ORDERSTATUS WHERE
  123. SENTNAME=@STR_FILENAME
  124.  
  125.  
  126. CREATE INDEX D ON #KEY (INT_ORDERID, REGION)
  127. SELECT @INT_SEQUENCE=SEQNUM FROM db_ORDERS..tbl_ORDERCUSTLOCK WHERE
  128. OFFICE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
  129. IF @@ROWCOUNT=0
  130.     BEGIN
  131.         SET @INT_SEQUENCE=1
  132.     END
  133. SET @ERRORMESSAGE='Check status'
  134. SELECT @INT_CNT=COUNT(*) FROM #KEY
  135.  
  136.  
  137. IF @INT_CNT=0 RETURN
  138. DECLARE MAXSCH CURSOR FOR SELECT REGION, INT_ORDERID FROM #KEY ORDER
  139. BY INT_ORDERID
  140. OPEN MAXSCH
  141. FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
  142. WHILE (@@FETCH_STATUS <> -1 )
  143.     BEGIN
  144.         SELECT @STR_ORDEROFFICE=OFFICE FROM
  145. db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
  146. REGION=@STR_ORDERREGION
  147.         SELECT @DECIMAL_MAXORDER = 240
  148.         SELECT @DECIMAL_MAXORDER = MAXINCR FROM
  149. db_ORDERS..tbl_ORDERMAXED WHERE CODE=@STR_ORDEROFFICE AND
  150. REGION=@STR_ORDERREGION
  151.         IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = MAXINCR,
  152. @STR_ORDEROFFICE=@STR_FILENAME FROM db_ORDERS..tbl_ORDERMAXED WHERE
  153. CODE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
  154.         IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = MAXINCR,
  155. @STR_ORDEROFFICE='ORDR' FROM db_ORDERS..tbl_ORDERMAXED WHERE
  156. CODE='ORDR' AND REGION=@STR_ORDERREGION
  157.         IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = 120,
  158. @STR_ORDEROFFICE='MISSING'
  159.         UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET STOP_TIME=START_TIME
  160. +@DECIMAL_MAXORDER+BREAK2_STOP, STR_UPDATED='2', SUBMTD='0' WHERE
  161. STOP_TIME-START_TIME-BREAK2_STOP>@DECIMAL_MAXORDER AND
  162. INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
  163.             ORDERDATE IN (SELECT ORDERDATE FROM
  164. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  165. REGION=@STR_ORDERREGION AND INT_ORDERID=@INT_ORDERID)
  166.         SELECT @INT_CNT=@@ROWCOUNT
  167.         IF @INT_CNT>0 PRINT CONVERT(CHAR(12),GETDATE(),114) + ' -
  168. LIMIT ' + RTRIM(CONVERT(CHAR(12),@INT_CNT)) + ' ORDERS TO ' +
  169. RTRIM(CONVERT(CHAR(10),@DECIMAL_MAXORDER/12)) + ' HR FOR ' +
  170. CONVERT(CHAR(10),@INT_ORDERID) + ' IN THE OFFICE ' + @STR_ORDEROFFICE
  171.         FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
  172.     END
  173. DEALLOCATE MAXSCH
  174.  
  175.  
  176. DECLARE EXCRND CURSOR FOR
  177.   SELECT REGION, INT_ORDERID, ORDERDATE, SYSTEM_COD AS CDE, DVALUE
  178. FROM db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ROUND(DVALUE,0)!=DVALUE AND
  179.       INT_ORDERID IN (SELECT INT_ORDERID FROM
  180. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  181. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
  182.       SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE
  183. WHERE RMETHOD!='')
  184.     ORDER BY INT_ORDERID, ORDERDATE
  185. OPEN EXCRND
  186. FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
  187. @STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
  188. WHILE (@@FETCH_STATUS <> -1 )
  189.     BEGIN
  190.         SELECT @STR_METHOD=RMETHOD, @INT_EXTRA=REXTRA FROM
  191. db_ORDERS..tbl_ORDERCODE WHERE SYSTEM_COD=@STR_ORDERREMOTE AND
  192. REGION=@STR_ORDERREGION
  193.         IF @STR_METHOD='O'
  194.             BEGIN
  195.                 UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  196. DVALUE=ROUND(DVALUE,@INT_EXTRA) WHERE REGION=@STR_ORDERREGION AND
  197. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  198. SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
  199.             END
  200.         IF @STR_METHOD='C'
  201.             BEGIN
  202.                 UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  203. DVALUE=CEILING(DVALUE) WHERE REGION=@STR_ORDERREGION AND
  204. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  205. SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
  206.             END
  207.         IF @STR_METHOD='M'
  208.             BEGIN
  209.                 UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  210. DVALUE=FLOOR(DVALUE) WHERE REGION=@STR_ORDERREGION AND
  211. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  212. SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
  213.             END
  214.         FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
  215. @STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
  216.     END
  217. DEALLOCATE EXCRND
  218.  
  219.  
  220. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='SUM OF ORDERS FOR THIS
  221. DAY GREATER THAN EXCEPTION LENGTH'
  222.   WHERE SYSTEM_COD='TTTT' AND
  223.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  224. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  225. C.REGION=CTEEXC.REGION) AND
  226.     INT_ORDERID IN (SELECT INT_ORDERID FROM
  227.              (SELECT REGION, INT_ORDERID, ORDERDATE, SUM(DVALUE) AS
  228. DAT FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE
  229. E.ORDERDATE=CTEEXC.ORDERDATE AND E.REGION=CTEEXC.REGION AND
  230.                   (SYSTEM_COD='TTTT' OR SYSTEM_COD IN (SELECT
  231. SYSTEM_COD FROM CTCODE AS C WHERE REGAAB='T' AND C.REGION=E.REGION))
  232.                 GROUP BY REGION, INT_ORDERID, ORDERDATE
  233.                 HAVING SUM(DVALUE)>(SELECT (STOP_TIME-START_TIME-
  234. BREAK2_STOP)*5/60.0 FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
  235. S.ORDERDATE=E.ORDERDATE AND S.INT_ORDERID=E.INT_ORDERID AND
  236. S.REGION=E.REGION)
  237.              ) AS BS
  238.            )
  239.  
  240.  
  241. INSERT INTO #ORDERNAHI SELECT INT_ORDERID, ORDERDATE FROM
  242. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='BILL' AND INT_ORDERID
  243. IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
  244. SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  245. C.REGION=CTEEXC.REGION)
  246.  
  247.  
  248. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE
  249.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  250. AS C WHERE SENTNAME=@STR_FILENAME AND
  251. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
  252. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
  253.   PATINDEX(UPPER('%ORDERS ARE NOT SENT%'),UPPER(ERR))>0
  254. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=SYSTEM_COD + ' ORDERS
  255. ARE NOT SENT' WHERE
  256.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  257. AS C WHERE SENTNAME=@STR_FILENAME AND
  258. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
  259. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
  260.   SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE WHERE
  261. RECTYPE='MISSING' AND CTCODE.REGION=CTEEXC.REGION)
  262.  
  263.  
  264. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
  265.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  266. AS C WHERE SENTNAME=@STR_FILENAME AND
  267. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  268. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  269.   PATINDEX(UPPER('%WILL NOT BE SENT CAUSE OF DUPLICATE ORDER
  270. %'),UPPER(ERR))>0
  271. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='WILL NOT BE SENT CAUSE OF
  272. DUPLICATE ORDER' WHERE
  273.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  274. AS C WHERE SENTNAME=@STR_FILENAME AND
  275. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  276. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  277.   INT_ORDERID IN (SELECT INT_ORDERID FROM
  278. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
  279.               CTEEXC.ORDERDATE=CTESCH.ORDERDATE AND
  280. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=db_ORDERS..tbl_ORDERSCHEDULE.REGION
  281. AND db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE>=@STR_MINIMUM_DATE AND
  282. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE<=@STR_MAXIMUM_DATE AND
  283. db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID>999999 AND
  284.               PATINDEX(UPPER('%ORDERS ARE NOT SENT
  285. %'),UPPER(db_ORDERS..tbl_ORDEREXCEPTIONS.ERR))>0)
  286.  
  287.  
  288. CREATE TABLE #ORDERSCHHRS (REGION CHAR(1), INT_ORDERID INT, FP BIT, MX
  289. DECIMAL(5, 2), HRS DECIMAL(5, 2), WK TINYINT, YR SMALLINT)
  290. CREATE TABLE #DATA2 (REGION CHAR(1), INT_ORDERID INT, ISFULLTIME BIT,
  291. MAXHOURS DECIMAL(5,2))
  292. INSERT INTO #DATA2 SELECT DISTINCT REGION, INT_ORDERID, ISFULLTIME,
  293. MAXHOURS FROM db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID IN
  294. (SELECT INT_ORDERID FROM #KEY)
  295.  
  296.  
  297. DECLARE SETUPSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
  298. DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)),
  299. DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
  300. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
  301. INT_ORDERID
  302. OPEN SETUPSCH
  303. FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  304. @SMALLINT_WEEK, @SMALLINT_YEAR
  305. WHILE (@@FETCH_STATUS <> -1 )
  306.     BEGIN
  307.         INSERT INTO #ORDERSCHHRS
  308.           SELECT DISTINCT S.REGION, S.INT_ORDERID, D.ISFULLTIME,
  309. D.MAXHOURS, SUM((S.STOP_TIME-S.START_TIME-
  310. S.BREAK2_STOP)*5.0/60),DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  311. +1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  312. +1,S.ORDERDATE))
  313.             FROM db_ORDERS..tbl_ORDERSCHEDULE AS S, #DATA2 AS D
  314.             WHERE DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  315. +1,S.ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
  316. DATEPART(DW,S.ORDERDATE)+1,S.ORDERDATE))=@SMALLINT_YEAR AND
  317. S.REGION=@STR_ORDERREGION AND D.REGION=@STR_ORDERREGION AND
  318. D.INT_ORDERID=@INT_ORDERID AND S.INT_ORDERID=@INT_ORDERID
  319.             GROUP BY S.REGION, S.INT_ORDERID, D.ISFULLTIME,
  320. D.MAXHOURS,DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  321. +1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
  322. +1,S.ORDERDATE))
  323.         FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  324. @SMALLINT_WEEK, @SMALLINT_YEAR
  325.     END
  326. DEALLOCATE SETUPSCH
  327. CREATE INDEX D2 ON #ORDERSCHHRS (HRS, MX, INT_ORDERID, REGION, WK, YR)
  328.  
  329.  
  330. DECLARE CLRSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
  331. DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)),
  332. DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
  333. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
  334. INT_ORDERID
  335. OPEN CLRSCH
  336. FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  337. @SMALLINT_WEEK, @SMALLINT_YEAR
  338. WHILE (@@FETCH_STATUS <> -1 )
  339.     BEGIN
  340.         UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
  341. (PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED%'),UPPER(ERR))>0 OR
  342. PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0) AND
  343.             INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
  344. DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)
  345. +1,ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
  346. DATEPART(DW,ORDERDATE)+1,ORDERDATE))=@SMALLINT_YEAR
  347.         FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
  348. @SMALLINT_WEEK, @SMALLINT_YEAR
  349.     END
  350. DEALLOCATE CLRSCH
  351.  
  352.  
  353. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
  354. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='13', ERR='MAX ORDERS EXCEEDED'
  355. WHERE
  356.   INT_ORDERID IN (SELECT C.INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  357. AS C WHERE SENTNAME=@STR_FILENAME AND
  358. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  359. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  360.   INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS AS S WHERE
  361. HRS>MX AND CTESCH.REGION=S.REGION AND
  362.                                              DATEPART(WW,DATEADD(DD,-
  363. DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.WK AND
  364.                                              DATEPART(YY,DATEADD(DD,-
  365. DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.YR)
  366.  
  367.  
  368. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
  369. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='12', ERR='NOT ENOUGH ORDERS
  370. SCHEDULED' WHERE
  371.   INT_ORDERID IN (SELECT C.INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  372. AS C WHERE SENTNAME=@STR_FILENAME AND
  373. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  374. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  375.   INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS WHERE FP=1 AND
  376. HRS<MX AND DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
  377. +1,CTESCH.ORDERDATE))=WK AND DATEPART(YY,DATEADD(DD,-
  378. DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=YR) AND
  379.   INT_ORDERID NOT IN
  380.     (SELECT INT_ORDERID FROM #ORDERNAHI AS E WHERE
  381. DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
  382. +1,CTESCH.ORDERDATE))=DATEPART(WW,DATEADD(DD,-DATEPART(DW,E.COL)
  383. +1,E.COL)) AND
  384.                                       DATEPART(YY,DATEADD(DD,-
  385. DATEPART(DW,CTESCH.ORDERDATE)
  386. +1,CTESCH.ORDERDATE))=DATEPART(YY,DATEADD(DD,-DATEPART(DW,E.COL)
  387. +1,E.COL)))
  388.  
  389.  
  390. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='MULTIPLE ORDERS' WHERE
  391.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  392. AS SS WHERE SENTNAME=@STR_FILENAME AND SS.ORDERDATE=CTESCH.ORDERDATE
  393. AND SS.REGION=CTESCH.REGION) AND
  394.   CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN
  395.     ( SELECT CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM
  396. db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
  397.              INT_ORDERID IN (SELECT INT_ORDERID FROM
  398. db_ORDERS..tbl_ORDERSTATUS AS SS WHERE SENTNAME=@STR_FILENAME AND
  399. SS.ORDERDATE=S.ORDERDATE AND SS.REGION=S.REGION)
  400.       GROUP BY CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION HAVING
  401. COUNT(*)>1)
  402.  
  403.  
  404. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='INVALID ORDER SCHEDULE'
  405. WHERE
  406.   INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  407. AS C WHERE SENTNAME=@STR_FILENAME AND
  408. db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
  409. db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
  410.   (STOP_TIME-START_TIME-BREAK2_STOP)/12.0 NOT IN (SELECT RTHRS FROM
  411. db_ORDERS..tbl_ORDERRECORD)
  412.  
  413.  
  414. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE ERR='MISSING
  415. ORDER' AND
  416.   CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN (SELECT
  417. CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM #KEY)
  418.  
  419.  
  420. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='MISSING ORDER'
  421.   WHERE (SELECT ISNULL(SUM(DVALUE),0) FROM
  422. db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
  423. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
  424. db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
  425. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND
  426. SYSTEM_COD='DDDL')>0 AND
  427.         (SELECT ISNULL(SUM(DVALUE),0) FROM
  428. db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
  429. db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
  430. db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
  431. db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND SYSTEM_COD
  432. IN ('MISA','KIPA'))=0
  433.         AND SYSTEM_COD='DDDL' AND ORDERDATE>=@STR_MINIMUM_DATE AND
  434. ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999 AND INT_ORDERID IN
  435. (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
  436. SENTNAME=@STR_FILENAME AND S.ORDERDATE=CTEEXC.ORDERDATE AND
  437. S.REGION=CTEEXC.REGION)
  438.  
  439.  
  440. SELECT ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
  441. AS E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
  442. E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS DSUM,
  443.        ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
  444. AS E1 WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE
  445. AND E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS EHSUM,
  446. ORDERDATE, INT_ORDERID
  447.     INTO #EHPROB
  448.     FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
  449.         SENTNAME=@STR_FILENAME
  450. DELETE FROM #EHPROB WHERE DSUM<=EHSUM
  451.  
  452.  
  453. INSERT INTO #EHPROB
  454.     SELECT 0, 0, ORDERDATE, INT_ORDERID
  455.         FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
  456.            (SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS
  457. E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
  458. E1.INT_ORDERID=S.INT_ORDERID)> 0 AND
  459.            (SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E1
  460. WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE AND
  461. E1.INT_ORDERID=S.INT_ORDERID)=0
  462.            AND SENTNAME=@STR_FILENAME
  463.  
  464.  
  465. INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #EHPROB
  466.  
  467.  
  468. INSERT INTO #ORDERSMAP SELECT DISTINCT SECONDARY, RECTYPE, COST FROM
  469. db_ORDERS..tbl_ORDERMAP WHERE REGION=@STR_ORDERREGION
  470. CREATE INDEX RecLoop ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
  471. INSERT INTO #ORDERNONSSCH SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
  472. db_ORDERS..tbl_ORDERSCHEDULE WHERE
  473.         ( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  474. ORDERS%'),UPPER(ERR))>0
  475.          OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
  476.          OR PATINDEX(UPPER('%SUM OF ORDERS FOR THIS DAY GREATER THAN
  477. SCHEDULE ALLOWS%' ),UPPER(ERR))>0
  478.          OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  479. %'),UPPER(ERR))>0 ) AND
  480.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  481. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  482. C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)
  483.  
  484.  
  485. INSERT INTO #ORDERNONSSCH2 SELECT DISTINCT INT_ORDERID,
  486. DATEPART(WW,ORDERDATE), DATEPART(YY,ORDERDATE) FROM
  487. db_ORDERS..tbl_ORDERSCHEDULE WHERE
  488.         ( PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED
  489. %'),UPPER(ERR))>0
  490.          OR PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0
  491.          OR PATINDEX(UPPER('%INVALID ORDER SCHEDULE%'),UPPER(ERR))>0
  492.          OR PATINDEX(UPPER('%MISSING ORDER%'),UPPER(ERR))>0 ) AND
  493. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
  494.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  495. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  496. C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)
  497.  
  498.  
  499. INSERT INTO #ORDERNONSEXC SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
  500. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
  501.         ( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  502. ORDERS%'),UPPER(ERR))>0
  503.          OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
  504.          OR PATINDEX(UPPER('%MISSING ORDER%' ),UPPER(ERR))>0
  505.          OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  506. %'),UPPER(ERR))>0  AND ORDERDATE>=@STR_MINIMUM_DATE AND
  507. ORDERDATE<=@STR_MAXIMUM_DATE) AND
  508.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  509. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  510. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
  511. INSERT INTO #ORDERNONS SELECT INT_ORDERID,ORDERDATE FROM
  512. db_ORDERS..tbl_ORDEREXCEPTIONS
  513.     WHERE SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
  514. db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
  515. REGION=@STR_ORDERREGION ) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  516. ORDERDATE<=@STR_MAXIMUM_DATE AND
  517.         INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  518. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  519. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
  520.  
  521.  
  522. INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #KEY
  523. WHERE
  524.         INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
  525. db_ORDERS..tbl_ORDERSCHEDULE WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
  526. ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
  527.         INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
  528. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
  529. ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
  530.         INT_ORDERID>999999
  531.  
  532.  
  533. INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM
  534. db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='UMSL' AND
  535. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
  536. INT_ORDERID>999999 AND
  537.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  538. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  539. C.REGION=CTEEXC.REGION)
  540.  
  541.  
  542. CREATE CLUSTERED INDEX K1 ON #KEY (INT_ORDERID)
  543. CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH (INT_ORDERID,COL)
  544. CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH2 (INT_ORDERID)
  545. CREATE CLUSTERED INDEX S1 ON #ORDERNONSEXCSCH (INT_ORDERID)
  546. CREATE CLUSTERED INDEX E1 ON #ORDERNONSEXC (INT_ORDERID,COL)
  547. CREATE INDEX iNOSUB ON #ORDERNONS (INT_ORDERID,COL)
  548. CREATE INDEX iSECONDARYMAP ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
  549. CREATE INDEX iNONR ON #NONRINT_ORDERID (INT_ORDERID)
  550.  
  551.  
  552. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='', STR_UPDATED='15',
  553. SUBMTD='0' WHERE
  554.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  555. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
  556. C.REGION=CTESCH.REGION) AND
  557.     INT_ORDERID IN (SELECT INT_ORDERID FROM
  558. db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE E.ORDERDATE=CTESCH.ORDERDATE
  559. AND E.REGION=CTESCH.REGION AND STR_UPDATED>SUBMTD AND
  560. LEN(RTRIM(LTRIM(SYSTEM_COD)))>0) AND
  561.     INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
  562. #ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTESCH.ORDERDATE) AND
  563.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
  564.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
  565.     INT_ORDERID NOT IN (SELECT #ORDERNONSSCH.INT_ORDERID FROM
  566. #ORDERNONSSCH WHERE #ORDERNONSSCH.COL=CTESCH.ORDERDATE)
  567.  
  568.  
  569. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  570. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='17' WHERE INT_ORDERID>999999
  571. AND INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  572. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  573. C.REGION=CTEEXC.REGION) AND
  574.       ( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  575. ORDERS%'),UPPER(ERR))>0
  576.     OR PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))>0
  577.     OR PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))>0 )
  578. AND ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE
  579.  
  580.  
  581. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  582. STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD=@STR_ORDERSUBDATE2 WHERE
  583. REGION=@STR_ORDERREGION AND INT_ORDERID>999999 AND INT_ORDERID IN
  584. (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
  585. SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  586. C.REGION=CTEEXC.REGION) AND
  587.     PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  588. %'),UPPER(ERR))>0
  589.  
  590.  
  591. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='', STR_UPDATED='15',
  592. SUBMTD='0' WHERE
  593.     INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  594. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  595. C.REGION=CTEEXC.REGION) AND
  596.     INT_ORDERID IN (SELECT INT_ORDERID FROM
  597. db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD AND
  598. INT_ORDERID>999999 AND ORDERDATE>=@STR_MINIMUM_DATE AND
  599. ORDERDATE<=@STR_MAXIMUM_DATE AND S.ORDERDATE=CTEEXC.ORDERDATE AND
  600. S.REGION=CTEEXC.REGION AND INT_ORDERID IN (SELECT INT_ORDERID FROM
  601. #KEY)) AND
  602.     INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
  603. #ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
  604.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
  605.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
  606.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
  607. #ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND
  608.     LEN(RTRIM(LTRIM(SYSTEM_COD)))>0 AND LTRIM(RTRIM(SUBMTD)) NOT IN
  609. ('16','17')
  610.  
  611.  
  612. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18',
  613. STR_UPDATED='0', ERR=LTRIM(RTRIM(SYSTEM_COD)) + ' ' +
  614. @STR_ORDERNOTMADE WHERE (SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
  615. db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
  616. REGION=CTEEXC.REGION ) OR SYSTEM_COD='BILL') AND
  617. REGION=@STR_ORDERREGION AND SUBMTD<STR_UPDATED AND INT_ORDERID>999999
  618. AND INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
  619. AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  620. C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  621. ORDERDATE<=@STR_MAXIMUM_DATE
  622. UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18', STR_UPDATED='0'
  623. WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
  624. #ORDERNONS.COL=CTEEXC.ORDERDATE) AND INT_ORDERID>999999 AND
  625. INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
  626. C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  627. C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  628. ORDERDATE<=@STR_MAXIMUM_DATE
  629. UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET SUBMTD='18', STR_UPDATED='0'
  630. WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
  631. #ORDERNONS.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
  632. INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
  633. C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
  634. C.REGION=CTESCH.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  635. ORDERDATE<=@STR_MAXIMUM_DATE
  636.  
  637.  
  638. IF @@ROWCOUNT>0
  639.   BEGIN
  640.     CREATE INDEX iMHIR ON #ORDERNAHI (INT_ORDERID, COL)
  641.     UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='UMSL EXISTS FOR
  642. ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
  643.       WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
  644. #ORDERNAHI.COL=CTEEXC.ORDERDATE) AND SYSTEM_COD!='BILL' AND
  645. INT_ORDERID>999999 AND
  646.             INT_ORDERID IN (SELECT INT_ORDERID FROM
  647. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  648. C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
  649.             ORDERDATE>=@STR_MINIMUM_DATE AND
  650. ORDERDATE<=@STR_MAXIMUM_DATE
  651.     UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='UMSL EXISTS FOR
  652. ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
  653.       WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
  654. #ORDERNAHI.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
  655.             INT_ORDERID IN (SELECT INT_ORDERID FROM
  656. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  657. C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION) AND
  658.             ORDERDATE>=@STR_MINIMUM_DATE AND
  659. ORDERDATE<=@STR_MAXIMUM_DATE
  660.   END
  661.  
  662.  
  663. INSERT INTO #ORDEREXC SELECT INT_ORDERID, LTRIM(RTRIM(ORDERDATE)) AS
  664. ORDERDATE, SYSTEM_COD, CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD,
  665. STR_CSTCD FROM db_ORDERS..tbl_ORDEREXCEPTIONS
  666.   WHERE INT_ORDERID>999999 AND STR_UPDATED>SUBMTD AND
  667. PATINDEX(UPPER('%Invalid Elink Code%'),UPPER(ERR))=0 AND
  668. PATINDEX(UPPER('%Unmapped RT Code%'),UPPER(ERR))=0 AND SYSTEM_COD!=''
  669. AND
  670.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
  671. #ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
  672.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
  673. WHERE NS.WK=DATEPART(WW,CTEEXC.ORDERDATE) AND
  674. NS.YR=DATEPART(WW,CTEEXC.ORDERDATE)) AND
  675.     INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
  676. #ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND INT_ORDERID IN (SELECT
  677. INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
  678. SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
  679. C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
  680. ORDERDATE<=@STR_MAXIMUM_DATE AND
  681.        PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
  682. ORDERS%'),UPPER(ERR))=0
  683.     AND PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
  684. %'),UPPER(ERR))=0
  685.     AND PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))=0
  686.     AND PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))=0
  687.   ORDER BY ORDERDATE
  688.  
  689.  
  690. INSERT INTO #ORDERSCH SELECT DISTINCT INT_ORDERID,
  691. LTRIM(RTRIM(ORDERDATE)) AS ORDERDATE, (STOP_TIME-START_TIME-
  692. BREAK2_STOP)*5.0/60 AS SCHLEN,
  693.         (SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
  694. WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
  695. E.REGION=S.REGION AND SYSTEM_COD='NDAY') AS NITE,
  696.         (SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
  697. WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
  698. E.REGION=S.REGION AND SYSTEM_COD='SPTN') AS NSPT,
  699.         (SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
  700. WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
  701. E.REGION=S.REGION AND SYSTEM_COD='CRSM') AS MHOL
  702.     FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD
  703. AND INT_ORDERID>999999 AND
  704.       INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
  705. #ORDERNONSEXC.COL=S.ORDERDATE) AND
  706.       INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
  707. WHERE NS.WK=DATEPART(WW,S.ORDERDATE) AND
  708. NS.YR=DATEPART(YY,S.ORDERDATE)) AND
  709.       INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
  710. #ORDERNONSSCH.COL=S.ORDERDATE) AND
  711.       INT_ORDERID IN (SELECT INT_ORDERID FROM
  712. db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
  713. C.ORDERDATE=S.ORDERDATE AND C.REGION=S.REGION) AND
  714. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
  715.        PATINDEX(UPPER('%MINIMUM ORDERS NOT MET%'),UPPER(ERR))=0 AND
  716.        PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))=0
  717.     ORDER BY INT_ORDERID, ORDERDATE
  718.  
  719.  
  720. CREATE INDEX iEXC1a ON #ORDEREXC (INT_ORDERID, ORDERDATE, SYSTEM_COD,
  721. CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD, STR_CSTCD)
  722. CREATE INDEX iEXC1b ON #ORDEREXC (SYSTEM_COD, INT_ORDERID, ORDERDATE,
  723. DVALUE)
  724. CREATE INDEX iSCHa ON #ORDERSCH (INT_ORDERID, ORDERDATE, SCHLEN)
  725.  
  726.  
  727. DECLARE EMPS CURSOR FOR SELECT DISTINCT (INT_ORDERID/2)+1134 FROM #KEY
  728. WHERE INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH)
  729. ORDER BY INT_ORDERID
  730. OPEN EMPS
  731. FETCH NEXT FROM EMPS INTO @INT_ORDERID
  732. TRUNCATE TABLE db_ORDERS..tbl_ORDERDUMPRAW
  733. INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'HEDR0000' +
  734. RIGHT('0000'+RTRIM(CONVERT(CHAR(4),@INT_SEQUENCE)),4) +
  735. CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(6),GETDATE(),
  736. 108),':','')
  737. SELECT @INT_COUNT=1
  738. WHILE (@@FETCH_STATUS <> -1 )
  739.     BEGIN
  740.         SELECT @STR_SECCODE = 'x '+CONVERT(CHAR(9),@INT_ORDERID)+' jk
  741. 9.216.12'
  742.         SELECT @STR_USER_ID='INVALID'
  743.         SELECT @STR_USER_ID=STR_USER_ID FROM
  744. db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
  745. LTRIM(RTRIM(STR_USER_ID))!=' '  AND STR_USER_ID IS NOT NULL
  746.         IF @STR_USER_ID='INVALID'
  747.           BEGIN
  748.  
  749.  
  750.           END
  751.         ELSE
  752.           BEGIN
  753.             INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
  754. 'JKUL0001        ' + RIGHT('00000000' +
  755. LTRIM(RTRIM(CONVERT(CHAR(9),@INT_ORDERID))),9) + '       ' +
  756. SPACE(80) + 'CAP' + @STR_SECCODE
  757.             SELECT @STR_ORDEROFFICE=MAX(OFFICE) FROM
  758. db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
  759. REGION=@STR_ORDERREGION
  760.             SELECT @INT_COUNT=@INT_COUNT+1
  761.             DECLARE SCHS CURSOR FOR SELECT ORDERDATE, SCHLEN, NITE,
  762. NPST, MHOL FROM #ORDERSCH
  763.                                         WHERE INT_ORDERID=@INT_ORDERID
  764. AND ORDERDATE NOT IN (SELECT ORDERDATE FROM #ORDEREXC WHERE
  765. INT_ORDERID=@INT_ORDERID AND SYSTEM_COD='BILL' AND
  766. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE) AND
  767.  
  768.  
  769. ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE ORDER BY
  770. ORDERDATE
  771.             OPEN SCHS
  772.             FETCH NEXT FROM SCHS INTO @STR_ORDERDATE, @DEC_ORDERLEN,
  773. @SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
  774.             WHILE (@@FETCH_STATUS <> -1 )
  775.                 BEGIN
  776.                     SELECT @INT_CNT=COUNT(*) FROM #NONRINT_ORDERID
  777. WHERE INT_ORDERID=@INT_ORDERID
  778.                     IF @INT_CNT>0 OR @SENDRECORD=1
  779.                         BEGIN
  780.                             INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW
  781. SELECT 'RKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + @STR_SECCODE
  782.                             SELECT @INT_COUNT=@INT_COUNT+1
  783.                         END
  784.                     IF @SMALLINT_OVERNIGHT>0 SELECT
  785. @SMALLINT_OVERNIGHT=1
  786.                     IF @SMALLINT_FEES>0 SELECT @SMALLINT_FEES=1
  787.                     IF @DECIMAL_HOLIDAY>0 SELECT
  788. @STR_ORDERSCH=SECONDARYHRS FROM db_ORDERS..tbl_ORDERRECORD WHERE
  789. RTHRS=@DECIMAL_HOLIDAY AND NDIFF=@SMALLINT_OVERNIGHT AND
  790. CARFARE=@SMALLINT_FEES
  791.                     ELSE SELECT @STR_ORDERSCH=SECONDARYHRS FROM
  792. db_ORDERS..tbl_ORDERRECORD WHERE RTHRS=@DEC_ORDERLEN AND
  793. NDIFF=@SMALLINT_OVERNIGHT AND CARFARE=@SMALLINT_FEES
  794.                     INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
  795. 'ZKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + '01' + @STR_ORDERSCH +
  796. '                 ' + @STR_TAX + @STR_SECCODE
  797.                     SELECT @INT_COUNT=@INT_COUNT+1
  798.                     DECLARE EXCS CURSOR FOR
  799.                         SELECT E.ORDERDATE, M.SECONDARY,
  800. LTRIM(RTRIM(M.RECTYPE)),
  801. CONVERT(CHAR(6),CONVERT(DECIMAL(6,2),E.DVALUE)), LEFT(E.RSN,6),
  802. E.STR_CSTCD, M.COST
  803.                             FROM #ORDEREXC AS E, #ORDERSMAP AS M
  804.                             WHERE E.INT_ORDERID=@INT_ORDERID AND
  805. E.SYSTEM_COD=M.SECONDARY AND E.ORDERDATE=@STR_ORDERDATE AND
  806. E.SYSTEM_COD NOT LIKE '%?%'
  807.                             ORDER BY E.ORDERDATE, M.RECTYPE, M.COST
  808.                     OPEN EXCS
  809.                     FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
  810. @STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
  811. @STR_COSTCOD, @STR_COST
  812.                     WHILE (@@FETCH_STATUS <> -1 )
  813.                          BEGIN
  814.                             IF @STR_ORDERTYPE = 'JKUL'
  815.                                  BEGIN
  816.                                      INSERT INTO
  817. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'JKUL0001' +
  818. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  819. @STR_ORDERRSN + SPACE(15) + @STR_SECCODE
  820.                                      SELECT @INT_COUNT=@INT_COUNT+1
  821.                                  END
  822.                             IF @STR_ORDERTYPE = 'RKUL'
  823.                                  BEGIN
  824.                                      INSERT INTO
  825. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RKUL0001' +
  826. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  827. @STR_SECCODE
  828.                                      SELECT @INT_COUNT=@INT_COUNT+1
  829.                                  END
  830.                             IF @STR_ORDERTYPE = 'ZKUL'
  831.                                  BEGIN
  832.                                      SELECT @STR_ACTCH=ACT_CHG,
  833. @STR_COSTCNTR=COST_CNTR, @STR_LOCCODE=LOC_CODE,
  834. @STR_ORDERNUM=PROJ_NUM, @STR_EC=CONVERT(CHAR(1),ENV_CODE) FROM
  835. db_ORDERS..tbl_ORDERCOST WHERE DIST_COD=@STR_COSTCOD AND
  836. OFFICE=@STR_ORDEROFFICE
  837.                                      IF @STR_COST='*' AND
  838. @@ROWCOUNT>0
  839.                                          BEGIN
  840.                                              INSERT INTO
  841. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
  842. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  843. @STR_ACTCH + @STR_COSTCNTR + @STR_LOCCODE + SPACE(6) + @STR_ORDERNUM +
  844. @STR_EC + SPACE(111) + @STR_TAX + @STR_SECCODE
  845.                                              SELECT
  846. @INT_COUNT=@INT_COUNT+1
  847.                                          END
  848.                                      ELSE
  849.                                          BEGIN
  850.                                              IF @STR_ORDERREMOTE !=
  851. 'DDDL'
  852.                                                  BEGIN
  853.                                                      INSERT INTO
  854. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
  855. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  856. SPACE(146) + @STR_TAX + @STR_SECCODE
  857.                                                      SELECT
  858. @INT_COUNT=@INT_COUNT+1
  859.                                                  END
  860.                                              ELSE
  861.                                                  BEGIN
  862.                                                      SELECT
  863. @INT_TAXSUM=SUM(DVALUE) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
  864. SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
  865. ORDERDATE=@STR_ORDERDATE
  866.                                                      SELECT
  867. @INT_TAXCNT=COUNT(*) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
  868. SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
  869. ORDERDATE=@STR_ORDERDATE
  870.                                                      SELECT
  871. @INT_DSUM=SUM(DVALUE) FROM #ORDEREXC WHERE SYSTEM_COD='DDDL' AND
  872. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE
  873.                                                      IF
  874. @INT_DSUM<=@INT_TAXSUM AND @INT_TAXCNT>0
  875.                                                          BEGIN
  876.                                                              INSERT
  877. INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
  878. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
  879. SPACE(146) + @STR_TAX + @STR_SECCODE
  880.                                                              SELECT
  881. @INT_COUNT=@INT_COUNT+1
  882.                                                          END
  883.                                                      ELSE
  884.                                                          BEGIN
  885.                                                              IF
  886. @INT_DSUM>@INT_TAXSUM SELECT @STR_MISTAKE = 'ORDER IS TOO SHORT'
  887.                                                              ELSE
  888. SELECT @STR_MISTAKE = 'ORDER MUST HAVE EXCEPTION ON SAME DAY'
  889.                                                              UPDATE
  890. db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=@STR_MISTAKE WHERE
  891. INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
  892. SYSTEM_COD='DDDL'
  893.                                                          END
  894.                                                  END
  895.                                          END
  896.                                  END
  897.                              IF @STR_ORDERTYPE = 'QKUL' OR
  898. @STR_ORDERTYPE = 'ZKUL'
  899.                                  BEGIN
  900.                                      IF @STR_ORDERTYPE = 'ERECM'
  901. INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
  902. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + SPACE(13) +
  903. RIGHT(SPACE(7)+RTRIM(ROUND(@STR_ORDERINFO,0)),7) + SPACE(35) +
  904. @STR_TAX + @STR_SECCODE
  905.                                      ELSE INSERT INTO
  906. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
  907. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
  908. RIGHT(SPACE(13)+RTRIM(ROUND(@STR_ORDERINFO,0))+'.00',13) + SPACE(7) +
  909. SPACE(35) + @STR_TAX + @STR_SECCODE
  910.                                      SELECT @INT_COUNT=@INT_COUNT+1
  911.                                  END
  912.                              IF @STR_ORDERTYPE = 'WKUL'
  913.                                  BEGIN
  914.                                      INSERT INTO
  915. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
  916. REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
  917. RIGHT(SPACE(13)+@STR_ORDERINFO,13) + SPACE(35) + @STR_TAX +
  918. @STR_SECCODE
  919.                                      SELECT @INT_COUNT=@INT_COUNT+1
  920.                                  END
  921.                              IF @STR_ORDERTYPE = 'PLAXC'
  922.                                  BEGIN
  923.                                      INSERT INTO
  924. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
  925. REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
  926. @STR_ORDERREMOTE + SPACE(10) + REPLACE(@STR_ORDERDATE,'-','') +
  927. LEFT(LTRIM(RTRIM(ROUND(@STR_ORDERINFO,0)))+SPACE(5),5) + SPACE(58) +
  928. @STR_SECCODE
  929.                                      SELECT @INT_COUNT=@INT_COUNT+1
  930.                                  END
  931.                              IF @STR_ORDERTYPE = 'UKLQQ'
  932.                                  BEGIN
  933.                                      INSERT INTO
  934. db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
  935. REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
  936. @STR_ORDERREMOTE + RIGHT(SPACE(10)+@STR_ORDERINFO,10) +
  937. REPLACE(@STR_ORDERDATE,'-','') + SPACE(63) + @STR_SECCODE
  938.                                      SELECT @INT_COUNT=@INT_COUNT+1
  939.                                  END
  940.                              FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
  941. @STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
  942. @STR_COSTCOD, @STR_COST
  943.                          END
  944.                     DEALLOCATE EXCS
  945.                     UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
  946. SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
  947. ORDERDATE=@STR_ORDERDATE
  948.                     UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
  949. SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
  950. ORDERDATE=@STR_ORDERDATE AND SYSTEM_COD>'' AND SYSTEM_COD NOT LIKE '?
  951. %'
  952.                     FETCH NEXT FROM SCHS INTO @STR_ORDERDATE,
  953. @DEC_ORDERLEN, @SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
  954.                 END
  955.             DEALLOCATE SCHS
  956.         END
  957.     FETCH NEXT FROM EMPS INTO @INT_ORDERID
  958. END
  959. DEALLOCATE EMPS
  960. INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'TRLR' +
  961. RIGHT('00000000' + LTRIM(RTRIM(CONVERT(CHAR(8),@INT_COUNT+1))),8)
  962.  
  963.  
  964. DELETE db_ORDERS..tbl_LOCKEDORDERS WHERE LOCKED = 'ORDER_SUBMISSION'
  965. GO
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: NEED TO GO FASTER

Postby AlexCuse on Wed Sep 25, 2013 8:33 pm

hmmm

  1. --will a shorter codeblock work?
  2.  
  3. SELECT * FROM INFORMATION_SCHEMA.TABLES
  4. WHERE TABLE_NAME LIKE 'RICO%'
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: NEED TO GO FASTER

Postby SQLDenis on Wed Sep 25, 2013 8:48 pm

  1. -- I think a shorter version works
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: NEED TO GO FASTER

Postby AlexCuse on Wed Sep 25, 2013 9:41 pm

Its rendering an empty div, assuming something in the code sample is deemed unsafe. Or GeShi has become sentient and is trying to save the world from UR's code.
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: NEED TO GO FASTER

Postby Emtucifor on Thu Sep 26, 2013 3:00 am

I think it has to do with the length of the rendered HTML of the codebox (being very close to but just under 100,000 characters where it stops working).

At first it seemed like the colored keywords were doing it, until I realized that just adding characters breaks it, and the reason it looked like keywords (such as "FROM") were breaking it is because each change in color triggers a new <span> element to be created, increasing the length.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: NEED TO GO FASTER

Postby tarwn on Thu Sep 26, 2013 11:53 am

How do we have a post from 2007 on here...how many years old is this site again? :P

(and then he looks to the right and sees his join date, nevermind)

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: NEED TO GO FASTER

Postby SQLDenis on Thu Sep 26, 2013 1:49 pm

Seems like a GESHI problem, if it is just code it works
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: NEED TO GO FASTER

Postby Kermit on Thu Sep 26, 2013 7:54 pm

Erm I had to scroll down for five minutes...

So basically you have a wall of text, I often find that in this situation the following can work (sometimes in combination):

1. Switch off the Monitor (or move a larger window over the top to hide the obscenity)

2. Shout really loudly and then wait until a cleverer person walks over and then challenge them to workout this quiz (the quiz being your work that is hideous and way past it's deadline)

3. Run down the office flailing your arms about but do not make any sounds, just mime...for ages...not sure what the results will be but should divert attention long enough for your screensaver to come alive and save you from certain brain implosion

That lot should work, if that fails throw your PC out of the window (preferably with no one below) and then remark about how cloudy it is today and you should have brought your umbrella.

In an alternative universe you could try this:

1. Select into a temporary table

2. Select from that temp table into another one

3. Write a function that creates random temp tables

3. Write a procedure that randomly selects stuff from your DB tables and drops them into the temp tables, then recursively in a humongous cursor loops for ever and ever selecting into one table and out of another.

If you do this long enough a wormhole should open up allowing you escape.
MotherFbleeper
User avatar
Kermit
LTD Admin
LTD Admin
LTD Bronze - Rating: 125LTD Bronze - Rating: 125LTD Bronze - Rating: 125
 
Posts: 571
Joined: Thu Oct 11, 2007 11:14 am
Location: United Kingdom
Unrated