- plsql
1 CREATE OR REPLACE PROCEDURE BMSSA.Usp_Neworderextract_All (sBBID IN VARCHAR2,ordType IN VARCHAR2,strFrDate IN VARCHAR2,strToDate IN VARCHAR2) AS 2 3 intRSQty INTEGER(10); 4 intManuSOH INTEGER(10); 5 intNettOrd INTEGER(10); 6 intCTDUS INTEGER(10); 7 intRemTrend INTEGER(10); 8 intHQOH INTEGER(10); 9 intKKOH INTEGER(10); 10 intKCHOH INTEGER(10); 11 intRecmdHQQty INTEGER(10); 12 intTransferHQQty INTEGER(10); 13 intPOQty INTEGER(10); 14 intRS INTEGER(10); 15 16 intLatestIT INTEGER(10); 17 strLineNum VARCHAR2(15); 18 19 strSql VARCHAR2(1000); 20 strApCode VARCHAR2(15); 21 strETADate DATE; 22 23 fRate NUMBER := 0.0050; 24 BizCount NUMBER := 0; 25 NBCount NUMBER := 0; 26 27 28 29 CURSOR Main_LOOP (BBID IN VARCHAR2 ) IS 30 31 SELECT 'AP'||SUBSTR(A.Requestno,1,2) ApCenter,' '||A.storeid storeid,A.requestdate,A.requestno,B.brochurecampaign Camp, 32 Y.ITEMGROUPID BUSSCAT, 33 B.FSC ,Y.ItemName description, 34 DECODE(NVL(D.FSC,0),0,0,1)ControlTag, 35 B.OnHandQty,B.InTransitQty,B.requestqty AddOrderQty, 36 B.OnHandQty+B.InTransitQty+B.requestqty TotalNeed, 37 DECODE(A.REASONCODE,'1','CO','2','AO','PCM') CONFORD, 38 NVL(F.testtestailtomonitor,0) testtestailtomonitor 39 FROM drmpos.storeinvrequest@drmprod A, 40 drmpos.storeinvrequestdetail@drmprod B,testMANUBLOCKFSC D,Inventtable Y, 41 (SELECT * FROM testCMHEADER WHERE dataareaid='835') F 42 WHERE 43 Y.Dataareaid='835' 44 AND A.requestNo = B.requestNo 45 AND A.status in (0,2) 46 AND B.FSC=Y.ITEMID 47 AND B.FSC =D.FSC(+) 48 AND B.FSC=F.ITEMID (+) 49 AND ' '||A.storeid = sBBID 50 AND A.reasoncode = ordtype 51 --AND requestdate between to_date(strFrDate,'YYYYMMDD') and to_date(strToDate,'YYYYMMDD') ; 52 AND requestdate between to_date(strFrDate,'YYYY/MM/DD') and to_date(strToDate,'YYYY/MM/DD') ; 53 rMain Main_Loop%ROWTYPE; 54 55 56 CURSOR Main2_LOOP (BBID IN VARCHAR2 ) IS 57 58 59 SELECT 'AP'||SUBSTR(A.REquestno,1,2) ApCenter,A.requestno,B.brochurecampaign Camp, 60 Y.ITEMGROUPID BUSSCAT, 61 C.testbrochurelinenum linenum,B.FSC ,C.testinvoicedesc description, 62 DECODE(NVL(D.FSC,0),0,1,0)ControlTag, 63 B.OnHandQty,B.InTransitQty,B.requestqty AddOrderQty, 64 B.OnHandQty+B.InTransitQty+B.requestqty TotalNeed, 65 NVL(E.AddOrdQty,0) LatestIT, 66 DECODE(A.REASONCODE,'1','CO','2','AO','PCM') CONFORD, 67 --A.REASONCODE CONFORD, 68 NVL(F.testtestailtomonitor,0)testtestailtomonitor 69 FROM drmpos.storeinvrequest@drmprod A, 70 drmpos.storeinvrequestdetail@drmprod B,PRICEDISCTABLE C ,testMANUBLOCKFSC D,Inventtable Y, 71 (SELECT A.CUSTACCOUNT,B.ITEMID,SUM(B.testQUANTITY)AddOrdQty 72 FROM 73 testORDERTRACKING A,testCARTONITEM B,testCARTONPICK C 74 WHERE A.dataareaid='835' 75 AND B.dataareaid='835' 76 AND C.dataareaid='835' 77 AND A.SALESID=C.SALESID 78 AND B.testPICKID=C.testPICKID 79 AND A.custaccount= BBID 80 AND A.testorderstatus='CARRIER' 81 GROUP BY A.CUSTACCOUNT,B.ITEMID 82 )E,(SELECT * FROM testCMHEADER WHERE dataareaid='835') F 83 WHERE 84 C.Dataareaid='835' 85 AND Y.Dataareaid='835' 86 AND ' '||A.storeid=BBID 87 AND A.requestNo = B.requestNo 88 AND B.FSC=C.itemrelation 89 AND TO_CHAR(B.BROCHURECAMPAIGN)=C.testCAMPAIGNID 90 AND C.ACCOUNTRELATION='WM' 91 AND C.testbrochuretype='B' 92 AND A.status in (0,2) 93 AND B.FSC=Y.ITEMID 94 AND B.FSC =D.FSC(+) 95 AND B.FSC =E.ITEMID(+) 96 AND B.FSC=F.ITEMID (+) ; 97 98 rMain2 Main2_Loop%ROWTYPE; 99 100 101 ---Get BrochLineNum 102 CURSOR RS_LineNum (camp IN VARCHAR2,cItemID IN VARCHAR2 ) IS 103 104 Select testbrochurelinenum linenum 105 from PRICEDISCTABLE where dataareaid='835' and testcampaignid=camp and accountrelation='WM' 106 and itemrelation=cItemID 107 and rownum < 2 108 order by rownum; 109 110 rLineNum RS_LineNum%ROWTYPE; 111 112 113 --- Latest Intransit 114 CURSOR RS_LatestIT (BBID IN VARCHAR2,cItemID IN VARCHAR2 ) IS 115 116 SELECT A.CUSTACCOUNT,B.ITEMID,SUM(B.testQUANTITY)LATESTIT 117 FROM 118 testORDERTRACKING A,testCARTONITEM B,testCARTONPICK C 119 WHERE A.dataareaid='835' 120 AND B.dataareaid='835' 121 AND C.dataareaid='835' 122 AND A.SALESID=C.SALESID 123 AND B.testPICKID=C.testPICKID 124 AND A.custaccount= BBID 125 AND ITEMID=cItemID 126 AND A.testorderstatus='CARRIER' 127 GROUP BY A.CUSTACCOUNT,B.ITEMID; 128 129 rLatestIT RS_LatestIT%ROWTYPE; 130 131 132 ---To get RS and ManuSOH-- 133 CURSOR RS_ManuSOH (cAPcenter IN VARCHAR2,camp IN VARCHAR2,cItemID IN VARCHAR2 ) IS 134 135 SELECT A.ITEMID,A.TRENDQTY BizShare,B.testSTOCKONHAND,NVL(C.testQTY,0) UNITSOLD,NVL(D.TRENDQTY,0)TRENDQTY 136 FROM testABBAUTOORDERSPLIT A, testMANUSOH B, 137 testMANUUNITSOLDTMP C, testmanutrend_new D 138 WHERE A.ITEMID=B.ITEMID 139 AND A.ITEMID=C.testFSC(+) 140 AND A.APCENTER=B.testAPSCENTER 141 AND A.APCENTER=C.testAPCENTER(+) 142 AND A.APCENTER=D.APCENTER(+) 143 AND A.ITEMID=D.ITEMID(+) 144 AND A.testSELLYEARCAMP=D.testSELLYEARCAMP(+) 145 AND A.ITEMID=cItemID 146 AND A.testsellyearcamp = camp 147 AND A.APCENTER= cAPcenter; 148 149 r_RSManuSOH RS_ManuSOH%ROWTYPE; 150 151 152 ---- Picking Qty --- OH withour carrier status 153 154 CURSOR RS_PickQty (cItemID IN VARCHAR2,BBID IN VARCHAR2 ) IS 155 156 SELECT A.CUSTACCOUNT,B.ITEMID,SUM(B.testQUANTITY)PickQty 157 FROM 158 testORDERTRACKING A,testCARTONITEM B,testCARTONPICK C 159 WHERE A.dataareaid='835' 160 AND B.dataareaid='835' 161 AND C.dataareaid='835' 162 AND A.SALESID=C.SALESID 163 AND B.testPICKID=C.testPICKID 164 AND A.custaccount= BBID 165 AND B.ITEMID = cItemID 166 AND A.testorderstatus in ('CARTONED','SHIPPING','TRANSPRT') 167 GROUP BY A.CUSTACCOUNT,B.ITEMID; 168 169 r_PickQty RS_PickQty%ROWTYPE; 170 171 -- Cursor HQ,RWKK,RWKCH OnHand 172 CURSOR RS_HQOH (cItemID IN VARCHAR2 ) IS 173 /* 174 select itemid,sum(testailphysical) HQOH from inventsum where dataareaid='835' 175 and inventdimid in (Select inventdimid from inventdim where inventlocationid in ('testN','SHP','BIN')) 176 and itemid <> chr(2) 177 and itemid=cItemID 178 group by itemid; 179 */ 180 181 select itemid, ( NVL(sum(decode(B.inventlocationid,'testN',A.testailphysical)),0) + NVL(sum(decode(B.inventlocationid,'SHP',A.testailphysical)),0) + NVL(sum(decode(B.inventlocationid,'BIN',A.testailphysical)),0) ) HQOH, 182 NVL(sum(decode(B.inventlocationid,'0001000',A.testailphysical)),0) KKOH, 183 NVL(sum(decode(B.inventlocationid,'0002000',A.testailphysical)),0) KCHOH 184 from inventsum A ,InventDim B where 185 A.dataareaid='835' and B.dataareaid='835' 186 and A.inventdimid =B.inventdimid 187 and B.inventlocationid in ('testN','SHP','BIN','0001000','0002000') 188 and A.itemid <> chr(2) 189 and A.itemid=cItemID 190 group by itemid; 191 192 r_HQOH RS_HQOH%ROWTYPE; 193 194 195 --&&&&&&&&&&&&&&&&&&&&&&&GEN(0) Cursor for BizShare and NonBeauty &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 196 197 CURSOR BizShare (parmAPCode IN VARCHAR2, parmFSC IN VARCHAR2) IS --- Loop ITEM,LOC, for Item exists in LL for Curr and BackCpg 198 199 Select count(1) bizCnt,APCODE,testFSC,RATE from testManuBizShare 200 where APCODE=parmAPCode and testFSC= parmFSC 201 group by APCODE,testFSC,RATE; 202 203 204 CURSOR NonBty (parmAPCode IN VARCHAR2, parmBuss IN VARCHAR2) IS --- Loop ITEM,LOC, for Item exists in LL for Curr and BackCpg 205 206 Select count(1) nbCnt,APCODE,Buss,RATE from testManuNonBeauty 207 where APCODE=parmAPCode and Buss= parmBuss 208 group by APCODE,Buss,RATE; 209 210 211 --&&&&&&&&&&&&&&&&&&&&&&&GEN(0) Cursor for BizShare and NonBeauty &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 212 213 214 215 216 --Get PODate,Qty 217 218 CURSOR RS_PO (cItemID IN VARCHAR2 ) IS 219 Select sum(qtyordered) POQty,trunc(min(deliverydate)) PODate from PurchLine where dataareaid='835' 220 and deliverydate > trunc(sysdate -14) 221 and purchstatus <> '2' 222 and itemid=cItemID 223 and deliverydate in ( 224 Select min(deliverydate) 225 from PurchLine where dataareaid='835' 226 and deliverydate > trunc(sysdate -14) 227 and purchstatus <> '2' 228 and itemid=cItemID); 229 230 r_PO RS_PO%ROWTYPE; 231 232 BEGIN 233 234 235 236 strSql := 'INSERT INTO testABBADDORDER_NEW (APCENTER, REQUESTNO, CAMP, LINENUM, FSC,' || 237 ' DESCRIPTION, CONTROLTAG, ONHANDQTY, INTRANSITQTY, ADDORDERQTY ,'|| --,' || 238 ' TOTALNEED, MANUSOH,LATESTIT, RSQTY, NETTORD ,' || 239 ' CAMPBZSHARE, US,' || 240 'RTREND, RECHQQTY,'|| 241 'TRANSHQ, TRANSKK, TRANSKH, ' || 242 'CONFORD,HQCMF_OH, SBKK_OH, SBKH_OH, REMARK, ' || 243 'FUTURE_PO , PO_ETA ,STOREID,REQUESTDATE) ' || 244 ' VALUES (:1, :2, :3, :4, :5, ' || 245 ' :6, :7, :8, :9, :10 ,' || 246 ' :11, :12, :13 , :14, :15 , ' || 247 ' :16, :17 ,'|| 248 ' :18, :19, '|| 249 ':20, :21, :22,'|| 250 ' :23, :24, :25, :26, :27, ' || 251 ':28 , :29, :30,:31) '; 252 253 254 255 DELETE FROM testABBADDORDER_NEW ; 256 COMMIT; 257 258 FOR rMain IN Main_Loop(sBBID) LOOP 259 260 intRecmdHQQty := 0 ; 261 intTransferHQQty := 0 ; 262 intHQOH := 0 ; 263 intRemTrend := 0 ; 264 intRS := 0; 265 intNettOrd := 0; 266 intPOQty := 0; 267 intLatestIT := 0; 268 strLineNum :=0000 ; 269 270 --BrochLineNum 271 272 FOR r_LineNum IN RS_LineNum(rmain.camp,rmain.FSC) LOOP 273 274 strLineNum := r_LineNum.LineNum; 275 276 END LOOP; 277 278 --Latest Intransit 279 280 FOR rLatestIT IN RS_LatestIT(rmain.StoreID,rmain.FSC) LOOP 281 282 intLatestIT := rLatestIT.LatestIT; 283 284 END LOOP; 285 286 287 FOR r_RSManuSOH IN RS_ManuSOH(rmain.apcenter, rmain.Camp,rmain.FSC) LOOP 288 289 intRSQty := r_RSManuSOH.BizShare; --CampBizShare 290 intManuSOH := r_RSManuSOH.testSTOCKONHAND; 291 intCTDUS := r_RSManuSOH.UNITSOLD; 292 293 --intNettOrd := (rMain.INTRANSITQTY - rMain.LATESTIT - intRSQty )- rMain.ADDORDERQTY; 294 295 intRemTrend := r_RSManuSOH.TRENDQTY - r_RSManuSOH.UNITSOLD ; 296 297 END LOOP; 298 299 FOR r_HQOH IN RS_HQOH(rmain.FSC) LOOP 300 301 intHQOH := r_HQOH.HQOH; 302 intKKOH := r_HQOH.KKOH; 303 intKCHOH := r_HQOH.KCHOH; 304 305 END LOOP; 306 307 308 309 --RS Qty ( Pick Qty) 310 311 FOR r_PickQty IN RS_PickQty(rmain.FSC,sBBID) LOOP 312 313 intRS := r_PickQty.PickQty; 314 315 END LOOP; 316 317 --Nett Order 318 -- intNettOrd := (rMain.INTRANSITQTY - rMain.LATESTIT - intRS )- rMain.ADDORDERQTY; 319 intNettOrd := rMain.ADDORDERQTY + rMain.INTRANSITQTY - intLatestIT - intRS ; -- latest revision by sob April 18 2013 320 321 -- Recommended HQ Qty 322 --intRecmdHQQty := intRSQty * intHQOH; 323 324 strApCode := rMain.APCENTER ; 325 326 327 BizCount := 0; 328 ---Lookup in LOCFSC (BizShare) 329 FOR rBizShare IN BizShare (strApCode, rmain.FSC) LOOP 330 331 BizCount := rBizShare.bizCnt ; 332 333 334 intRecmdHQQty := ROUND(intHQOH * rBizShare.Rate ) ; 335 BizCount := 1; 336 337 338 END LOOP; -- Biz Share 339 340 341 IF BizCount <> 1 THEN 342 343 NBCount := 0; 344 345 FOR rNonBty IN NonBty (strApCode,rmain.BUSSCAT) LOOP 346 347 NBCount := rNonBty.nbCnt; 348 349 intRecmdHQQty := ROUND(intHQOH * rNonBty.Rate) ; 350 NBCount := 1; 351 352 END LOOP; 353 354 END IF; 355 ---Fixed Rate 356 357 IF ( NBCount=0 AND BizCount=0 ) THEN 358 359 intRecmdHQQty := ROUND(intHQOH * fRate) ; 360 361 END IF; 362 363 364 365 366 367 if (intRecmdHQQty < intNettOrd) then 368 369 intTransferHQQty := intRecmdHQQty; 370 371 else 372 373 intTransferHQQty := intNettOrd; 374 end if; 375 376 377 378 379 --- PO date and Qty 380 FOR r_PO IN RS_PO(rmain.FSC) LOOP 381 382 intPOQty := r_PO.POQty; 383 strETADate := r_PO.PODate; 384 385 386 END LOOP; 387 388 BEGIN 389 390 EXECUTE IMMEDIATE strSql USING 391 rMain.APCENTER, rMain.REQUESTNO, rMain.CAMP, strLineNum, rMain.FSC, 392 rMain.DESCRIPTION, rMain.CONTROLTAG,rMain.ONHANDQTY, rMain.INTRANSITQTY, rMain.ADDORDERQTY , 393 rMain.TOTALNEED, intManuSOH,intLatestIT, intRS, intNettOrd, --RSQty = Picking (Ordertracking not equal to Carrier) 394 intRSQty,intCTDUS, --intRSQty actually CampBizbShare 395 intRemTrend,intRecmdHQQty, 396 '','','', -- 20,21,22 397 rMain.CONFORD,rMain.testtestailtomonitor,intKKOH,intKCHOH,'', -- 23,24,25,26,27 398 intPOQty, strETADate, rMain.storeid,rMain.requestdate; 399 400 EXCEPTION 401 WHEN OTHERS THEN 402 RAISE_APPLICATION_ERROR('-20050', 'SQL: ' || strSql || ' ' || 'OrderNo: ' || rMain.REQUESTNO || 403 ' Error: ' || SQLERRM ); 404 405 END; 406 407 END LOOP; 408 409 410 END; 411 /
- tsql
1 2 IF EXISTS(SELECT 1 FROM SYS.types AS t WHERE t.name='UDT_Usp_Neworderextract_All_ML') 3 DROP TYPE dbo.UDT_Usp_Neworderextract_All_ML 4 GO 5 CREATE TYPE dbo.UDT_Usp_Neworderextract_All_ML AS TABLE 6 ( ID BIGINT , 7 ApCenter NVARCHAR(5), 8 storeid NVARCHAR(20), 9 requestdate DATE, 10 requestno NVARCHAR(20), 11 Camp NVARCHAR(20), 12 BUSSCAT NVARCHAR(20), 13 FSC NVARCHAR(20), 14 [DESCRIPTION] NVARCHAR(20), 15 ControlTag NVARCHAR(5), 16 OnHandQty NUMERIC(30, 12), 17 InTransitQty NUMERIC(30, 12), 18 AddOrderQty NUMERIC(30, 12), 19 TotalNeed NUMERIC(30, 12), 20 CONFORD NVARCHAR(5), 21 testtestailtomonitor NUMERIC(30, 12) 22 PRIMARY KEY CLUSTERED(ID) 23 ) 24 GO 25 IF object_id('Usp_Neworderextract_All') IS NOT NULL 26 BEGIN 27 PRINT 'Dropping procedure Usp_Neworderextract_All' 28 DROP PROCEDURE [Usp_Neworderextract_All] 29 IF @@ERROR = 0 PRINT 'Procedure Usp_Neworderextract_All dropped' 30 END 31 go 32 33 CREATE PROCEDURE [Usp_Neworderextract_All] 34 (@SBBID NVARCHAR(20),@ordType NVARCHAR(20),@strFrDate DATE,@strToDate DATE) 35 AS 36 BEGIN 37 SET NOCOUNT ON; 38 DECLARE @intRSQty INT, 39 @intManuSOH INT, 40 @intNettOrd INT, 41 @intCTDUS INT, 42 @intRemTrend INT, 43 @intHQOH INT, 44 @intKKOH INT, 45 @intKCHOH INT, 46 @intRecmdHQQty INT, 47 @intTransferHQQty INT, 48 @intPOQty INT, 49 @intRS INT, 50 @intLatestIT INT, 51 @strLineNum NVARCHAR(15), 52 @strSql NVARCHAR(2000), 53 @PARAMS nvarchar(500), 54 @strApCode NVARCHAR(15), 55 @strETADate DATE, 56 @fRate NUMERIC = 0.0050, 57 @BizCount NUMERIC = 0, 58 @NBCount NUMERIC = 0 , 59 @ML_SQL NVARCHAR(1000), 60 @ML_PARAM NVARCHAR(100), 61 @ML_ROWID NUMERIC(10) = 1, 62 @ML_MAX NUMERIC(10) = 0, 63 @ML_RECORD UDT_Usp_Neworderextract_All_ML, 64 @ML_CURSOR UDT_Usp_Neworderextract_All_ML 65 /*BEGIN TRY*/ 66 BEGIN TRY 67 SET @strSql = N'INSERT INTO testABBADDORDER_NEW( APCENTER,REQUESTNO,CAMP,LINENUM,FSC,[DESCRIPTION], ' + char(10) 68 + N' CONTROLTAG,ONHANDQTY,INTRANSITQTY,ADDORDERQTY,TOTALNEED,MANUSOH,LATESTIT, ' + char(10) 69 + N' RSQTY,NETTORD,CAMPBZSHARE,US,RTREND,RECHQQTY,TRANSHQ,TRANSKK,TRANSKH, ' + char(10) 70 + N' CONFORD,HQCMF_OH,SBKK_OH,SBKH_OH,REMARK,FUTURE_PO,PO_ETA,STOREID, ' + char(10) 71 + N' REQUESTDATE) ' + char(10) 72 + N' SELECT rMain.APCENTER,rMain.REQUESTNO,rMain.CAMP,@strLineNum,rMain.FSC,rMain.[DESCRIPTION], ' + char(10) 73 + N' rMain.CONTROLTAG,rMain.ONHANDQTY,rMain.INTRANSITQTY,rMain.ADDORDERQTY, ' + char(10) 74 + N' rMain.TOTALNEED,@intManuSOH,@intLatestIT,@intRS,@intNettOrd,@intRSQty,@intCTDUS, ' + char(10) 75 + N' @intRemTrend,@intRecmdHQQty,'''','''','''',rMain.CONFORD,rMain.testtestailtomonitor, ' + char(10) 76 + N' @intKKOH,@intKCHOH,'''',@intPOQty,@strETADate,rMain.storeid,rMain.requestdate FROM @RMAin AS rMain' 77 78 SET @PARAMS = N'@RMAin UDT_Usp_Neworderextract_All_ML,@strLineNum NVARCHAR(15),@intManuSOH ' 79 + N' INT,@intLatestIT INT,@intRS INT,@intNettOrd INT,@intRSQty INT,@intCTDUS ' 80 + N' INT,@intRemTrend INT,@intRecmdHQQty INT,@intKKOH INT,@intKCHOH INT,@intPOQty ' 81 + N' INT,@strETADate DATE' 82 /* CURSOR Main_LOOP(BBID IN NVARCHAR) IS */ 83 SELECT @ML_SQL = N' SELECT ROW_NUMBER() OVER( ORDER BY ApCenter) AS ID,T1.* FROM (' 84 +' SELECT ''AP'' + SUBSTRING(A.Requestno, 1, 2) ApCenter,'' '' + A.storeid storeid,A.requestdate, ' + char(10) 85 + ' A.requestno,B.brochurecampaign Camp,Y.ITEMGROUPID BUSSCAT,B.FSC,Y.ItemName ' + char(10) 86 + ' [DESCRIPTION],CASE ISNULL(D.FSC, 0) WHEN 0 THEN 0 ELSE 1 END ControlTag, ' + char(10) 87 + 'B.OnHandQty,B.InTransitQty,B.requestqty AddOrderQty, ' + char(10) 88 + 'B.OnHandQty + B.InTransitQty + B.requestqty TotalNeed, ' + char(10) 89 + 'CASE A.REASONCODE WHEN ''1'' THEN ''CO'' WHEN ''2''THEN ''AO''ELSE ''PCM'' END CONFORD, ' + char(10) 90 + 'ISNULL(F.testtestailtomonitor, 0) testtestailtomonitor ' + char(10) 91 + 'FROM drmpos.storeinvrequest A ' + char(10) 92 + 'INNER JOIN drmpos.storeinvrequestdetail B ON A.requestNo = B.requestNo ' + char(10) 93 + 'LEFT JOIN testMANUBLOCKFSC D ON B.FSC = D.FSC ' + char(10) 94 + 'LEFT JOIN Inventtable Y ON B.FSC = Y.ITEMID ' + char(10) 95 + 'LEFT JOIN ( ' + char(10) 96 + ' SELECT * FROM testCMHEADER WHERE dataareaid = ''835'' ' + char(10) 97 + ' ) F ON B.FSC = F.ITEMID ' + char(10) 98 + ' WHERE Y.Dataareaid = ''835'' AND A.[status] IN (0, 2) ' + char(10) 99 + 'AND '' '' + A.storeid = @sBBID AND A.reasoncode = @ordType ' + char(10) 100 + 'AND requestdate BETWEEN CONVERT( DATE ,@strFrDate ) AND CONVERT( DATE , @strToDate ) ' 101 +') AS T1', 102 @ML_PARAM =N'@sBBID NVARCHAR(20), @ordType NVARCHAR(20), @strFrDate DATE,@strToDate DATE' 103 104 --//CLEAR 105 DELETE FROM testABBADDORDER_NEW 106 DELETE FROM @ML_CURSOR 107 INSERT INTO @ML_CURSOR EXEC SYS.sp_executesql @STMT=@ML_SQL,@PARAM=@ML_PARAM,@sBBID = @sBBID,@ordType = @ordType,@strFrDate = @strFrDate,@strToDate = @strToDate 108 SELECT @ML_MAX=@@ROWCOUNT , @ML_ROWID=1 FROM @ML_CURSOR 109 WHILE (@ML_ROWID<=@ML_MAX) 110 --//BEGIN @ML_RECORD 111 BEGIN 112 DELETE FROM @ML_RECORD/*clear*/ 113 INSERT INTO @ML_RECORD SELECT * FROM @ML_CURSOR WHERE ID=@ML_ROWID 114 SELECT @intRecmdHQQty = 0, 115 @intTransferHQQty = 0, 116 @intHQOH = 0, 117 @intRemTrend = 0, 118 @intRS = 0, 119 @intNettOrd = 0, 120 @intPOQty = 0, 121 @intLatestIT = 0, 122 @strLineNum = 0000 123 124 --BrochLineNum 125 ;WITH r_LineNum AS ( 126 SELECT testbrochurelinenum linenum 127 FROM PRICEDISCTABLE,@ML_RECORD rmain WHERE dataareaid = '835' AND testcampaignid = rmain.camp AND 128 accountrelation = 'WM' 129 AND itemrelation = rmain.FSC 130 AND rownum < 2 131 132 ) 133 SELECT @strLineNum=linenum FROM r_LineNum 134 135 --Latest Intransit 136 ;WITH rLatestIT AS( 137 SELECT A.CUSTACCOUNT,B.ITEMID,SUM(B.testQUANTITY)LATESTIT FROM 138 testORDERTRACKING A,testCARTONITEM B,testCARTONPICK C,@ML_RECORD rmain 139 WHERE A.dataareaid = '835' 140 AND B.dataareaid = '835' 141 AND C.dataareaid = '835' 142 AND A.SALESID = C.SALESID 143 AND B.testPICKID = C.testPICKID 144 AND A.custaccount = rmain.StoreID 145 AND ITEMID = rmain.FSC 146 AND A.testorderstatus = 'CARRIER' 147 GROUP BY A.CUSTACCOUNT,B.ITEMID 148 ) 149 SELECT @intLatestIT = rLatestIT.LatestIT FROM rLatestIT 150 151 --RS_ManuSOH 152 ;WITH r_RSManuSOH AS ( 153 SELECT A.ITEMID,A.TRENDQTY BizShare,B.testSTOCKONHAND,ISNULL(C.testQTY, 0) UNITSOLD, 154 ISNULL(D.TRENDQTY, 0) TRENDQTY 155 FROM testABBAUTOORDERSPLIT A 156 INNER JOIN testMANUSOH B ON A.ITEMID = B.ITEMID AND A.APCENTER = B.testAPSCENTER 157 INNER JOIN @ML_RECORD rmain ON 1=1 158 LEFT JOIN testMANUUNITSOLDTMP C ON A.ITEMID = C.testFSC AND A.APCENTER = C.testAPCENTER 159 LEFT JOIN testmanutrend_new D ON A.APCENTER = D.APCENTER AND A.ITEMID = D.ITEMID AND A.testSELLYEARCAMP = D.testSELLYEARCAMP 160 WHERE A.ITEMID = rmain.FSC AND A.testsellyearcamp = rmain.Camp AND A.APCENTER = rmain.apcenter 161 ) 162 SELECT @intRSQty = r_RSManuSOH.BizShare, @intManuSOH = r_RSManuSOH.testSTOCKONHAND,@intCTDUS = r_RSManuSOH.UNITSOLD , 163 @intRemTrend = r_RSManuSOH.TRENDQTY - r_RSManuSOH.UNITSOLD FROM r_RSManuSOH 164 165 --RS_HQOH 166 ;WITH r_HQOH AS ( 167 SELECT itemid,( 168 ISNULL(SUM(CASE B.inventlocationid WHEN 'testN' THEN A.testailphysical END ), 0 ) + 169 ISNULL(SUM(CASE B.inventlocationid WHEN 'SHP' THEN A.testailphysical END ),0) + 170 ISNULL(SUM(CASE B.inventlocationid WHEN'BIN' THEN A.testailphysical END),0) 171 ) HQOH, 172 ISNULL(SUM(CASE B.inventlocationid WHEN'0001000' THEN A.testailphysical END),0) KKOH, 173 ISNULL(SUM(CASE B.inventlocationid WHEN'0002000' THEN A.testailphysical END),0) KCHOH 174 FROM inventsum A ,InventDim B,@ML_RECORD rmain WHERE 175 A.dataareaid = '835' AND B.dataareaid = '835' 176 AND A.inventdimid = B.inventdimid 177 AND B.inventlocationid 178 IN ('testN', 'SHP', 'BIN', '0001000', '0002000') 179 AND A.itemid <> CHAR(2) 180 AND A.itemid = rmain.FSC 181 GROUP BY itemid 182 ) 183 SELECT @intHQOH = r_HQOH.HQOH, @intKKOH = r_HQOH.KKOH, @intKCHOH = r_HQOH.KCHOH FROM r_HQOH 184 185 --RS Qty ( Pick Qty) 186 ;WITH r_PickQty AS ( 187 SELECT A.CUSTACCOUNT,B.ITEMID,SUM(B.testQUANTITY)PickQty 188 FROM testORDERTRACKING A,testCARTONITEM B,testCARTONPICK C,@ML_RECORD rmain 189 WHERE A.dataareaid = '835' 190 AND B.dataareaid = '835' 191 AND C.dataareaid = '835' 192 AND A.SALESID = C.SALESID 193 AND B.testPICKID = C.testPICKID 194 AND A.custaccount = @SBBID 195 AND B.ITEMID = rmain.FSC 196 AND A.testorderstatus IN ('CARTONED', 'SHIPPING', 'TRANSPRT') 197 GROUP BY A.CUSTACCOUNT,B.ITEMID 198 ) 199 SELECT @intRS = r_PickQty.PickQty FROM r_PickQty 200 201 202 --Nett Order 203 SELECT @intNettOrd = rMain.ADDORDERQTY + rMain.INTRANSITQTY - @intLatestIT - @intRS FROM @ML_RECORD rmain 204 -- Recommended HQ Qty 205 SELECT @strApCode = rMain.APCENTER ,@BizCount = 0 FROM @ML_RECORD rmain 206 207 --Lookup in LOCFSC (BizShare) 208 ;WITH rBizShare AS ( 209 SELECT COUNT(1) bizCnt,APCODE,testFSC,RATE FROM testManuBizShare,@ML_RECORD rmain 210 WHERE APCODE = @strApCode AND testFSC = rmain.FSC 211 GROUP BY APCODE,testFSC,RATE 212 ) 213 SELECT @BizCount = rBizShare.bizCnt, @intRecmdHQQty = ROUND(@intHQOH * rBizShare.Rate, 0), 214 @BizCount= 1 FROM rBizShare 215 -- Biz Share 216 IF @BizCount <> 1 217 BEGIN 218 SET @NBCount = 0 219 ;WITH rNonBty AS ( 220 SELECT COUNT(1) nbCnt,APCODE,Buss,RATE FROM testManuNonBeauty,@ML_RECORD rmain 221 WHERE APCODE = @strApCode AND Buss = rmain.BUSSCAT 222 GROUP BY APCODE,Buss,RATE 223 ) 224 SELECT @NBCount = rNonBty.nbCnt, @intRecmdHQQty = ROUND(@intHQOH * rNonBty.Rate,0) , @NBCount = 1 FROM rNonBty 225 END 226 ---Fixed Rate 227 IF @NBCount = 0 AND @BizCount = 0 228 BEGIN 229 SET @intRecmdHQQty = ROUND(@intHQOH * @fRate, 0) 230 END 231 232 IF @intRecmdHQQty < @intNettOrd 233 BEGIN 234 SET @intTransferHQQty = @intRecmdHQQty 235 END 236 ELSE 237 BEGIN 238 SET @intTransferHQQty = @intNettOrd 239 END 240 241 --- PO date and Qty 242 ;WITH r_PO AS ( 243 SELECT SUM(qtyordered) POQty,CONVERT(DATE, MIN(deliverydate)) PODate FROM 244 PurchLine,@ML_RECORD rmain WHERE dataareaid = '835' 245 AND deliverydate > CONVERT(DATE, GETDATE() -14) 246 AND purchstatus <> '2' 247 AND itemid = rmain.FSC 248 AND deliverydate IN ( 249 SELECT MIN(deliverydate) 250 FROM PurchLine,@ML_RECORD rmain WHERE dataareaid = '835' 251 AND deliverydate > CONVERT(DATE, GETDATE() -14) 252 AND purchstatus <> '2' 253 AND itemid = rmain.FSC ) 254 ) 255 SELECT @intPOQty = r_PO.POQty , @strETADate = r_PO.PODate FROM r_PO 256 257 BEGIN 258 EXEC SP_EXECUTESQL @STMT=@strSql,@PARAM=@PARAMS 259 , @RMAin = @ML_RECORD,@strLineNum = @strLineNum,@intManuSOH = @intManuSOH,@intLatestIT 260 = @intLatestIT,@intRS = @intRS,@intNettOrd = @intNettOrd,@intRSQty = @intRSQty, 261 @intCTDUS = @intCTDUS,@intRemTrend = @intRemTrend,@intRecmdHQQty = @intRecmdHQQty, 262 @intKKOH = @intKKOH,@intKCHOH = @intKCHOH,@intPOQty = @intPOQty,@strETADate = @strETADate 263 IF @@ERROR>0 264 RAISERROR ('Error raised in EXEC SP_EXECUTESQL @STMT=@strSql,@PARAM=@PARAMS...', 16, 1 );/*[0-10]:CONTINUE;[11-19]:jump to catch*/ 265 END 266 SET @ML_ROWID+=1 267 END --//END @ML_RECORD 268 END TRY 269 /*END TRY*/ 270 BEGIN CATCH 271 /*DECLARE*/ 272 DECLARE @ErrorMessage NVARCHAR(4000), 273 @ErrorSeverity NVARCHAR(5), 274 @ErrorState NVARCHAR(5), 275 @ERROR_NUMBER NVARCHAR(5), 276 @ERROR_LINE NVARCHAR(5), 277 @ERROR_PROCEDURE NVARCHAR(100) 278 /*SET VALUES*/ 279 SELECT @ErrorMessage = ERROR_MESSAGE(), 280 @ErrorSeverity = ERROR_SEVERITY(), 281 @ErrorState = ERROR_STATE(), 282 @ERROR_NUMBER = ERROR_NUMBER(), 283 @ERROR_LINE = ERROR_LINE(), 284 @ERROR_PROCEDURE = CASE ISNULL(ERROR_PROCEDURE(), '') WHEN '' THEN '' ELSE 'Error occur when running procedure: [' + ERROR_PROCEDURE() + '];' END; 285 /*FORMATING MSG*/ 286 SET @ErrorMessage = @ERROR_PROCEDURE + CHAR(10) 287 + 'Msg:' + @ErrorMessage + ' Line:' + @ERROR_LINE + ' Number:' + @ERROR_NUMBER 288 + CHAR(10) 289 + 'Date:' + CONVERT(NVARCHAR(30), GETDATE(), 120) 290 /*RAISERROR*/ 291 RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); 292 END CATCH; 293 RETURN 0 294 END 295 go 296 297 IF @@ERROR = 0 PRINT 'Procedure Usp_Neworderextract_All created' 298 go
