Untitled
unknown
plain_text
2 years ago
7.1 kB
7
Indexable
SELECT BLDAT = ISNULL(SUBSTRING(RFX_cap_180.rec_date_con, 1, 8), -- Document Date in Document SUBSTRING(CONVERT(VARCHAR, getdate(), 112), 1, 8)) ,BUDAT = SUBSTRING(CONVERT(VARCHAR, getdate(), 112), 1, 8) -- Posting Date in the Document ,XBLNR = LTRIM(RTRIM(RFX_cap_130.supp_dn_num)) -- Reference Document Number ,TCODE = 'MB11' -- Current Transaction Code -- ,ZZEXREF = 'WMMBXY-' + LTRIM(RTRIM(@rec_ann))+LTRIM(RTRIM(SUBSTRING ( @rec_id, 3 , 9 ))) /*'PL' +LEFT(LTRIM(RTRIM(RFX_cap_340.detail_owner)), 2) +CONVERT(VARCHAR, @num1) -- IDOC Reference document number(PPPPnnnnnnnnnnnn) +RIGHT('000000000'+CONVERT(VARCHAR, @num2), 9)*/ ,MATNR = CASE -- Material Number WHEN ISNUMERIC(LTRIM(RTRIM(RFX_cap_310.ref))) = 1 THEN RIGHT('000000000000000000' + LTRIM(RTRIM(RFX_cap_310.ref)), 18) ELSE LTRIM(RTRIM(RFX_cap_310.ref)) END ,WERKS = CASE WHEN RFX_cap_340.detail_owner in ('01', '05','15') THEN 'PL'+LEFT(LTRIM(RTRIM(RFX_cap_340.detail_owner)), 2) WHEN RFX_cap_340.detail_owner in ('AI1','AR1','HP1','MB1','MH1','MP1','MS1','SI1','MM1','MK1','DG1','WP1','NP1') THEN 'PL01' WHEN RFX_cap_340.detail_owner in ('HP5' ,'AI5' ,'NP5') THEN 'PL05' ELSE 'PL'+LEFT(LTRIM(RTRIM(RFX_cap_340.detail_owner)), 2) END -- Plant ini ,LGORT = CASE -- Storage Location ini WHEN LEFT(LTRIM(RTRIM(RFX_cap_340.detail_grade)), 3) = '025' THEN '0025' WHEN LEFT(LTRIM(RTRIM(RFX_cap_340.detail_grade)), 3) = 'M27' THEN '0027' WHEN LEFT(LTRIM(RTRIM(RFX_cap_340.detail_grade)), 3) = '031' THEN '0031' ELSE 'H'+LEFT(LTRIM(RTRIM(RFX_cap_340.detail_grade)), 3) END ,CHARG = 'dummy_batch_z ' -- Batch ini ,BWART = '501' -- Movement Type (Inventory Management) ,INSMK = NULL ,SOBKZ = 'K' ,ERFMG = CASE WHEN SUM(CONVERT(INT,RFX_cap_340.batch2)) > 0 --THEN CONVERT(VARCHAR, SUM(CONVERT(INT, RFX_cap_340.lv20_qty))) + substring(LEFT(CONVERT(VARCHAR, SUM(CONVERT(decimal, LEFT(RFX_cap_340.batch2 + '000',3))/1000 )),5), 2, 4) THEN substring(CONVERT(VARCHAR, SUM(CONVERT(INT, RFX_cap_340.lv20_qty)) + SUM(CONVERT(decimal, LEFT(RFX_cap_340.batch2 + '000',3))/1000 )),1,4+LEN(CONVERT(VARCHAR, SUM(CONVERT(INT, RFX_cap_340.lv20_qty))))) ELSE CONVERT(VARCHAR, SUM(CONVERT(INT, RFX_cap_340.lv20_qty))) END -- Qty ,ERFME = 'EA' -- Unit of Entry ,EXIDV = NULL -- External Handling Unit Identification ,FRBNR = NULL -- CNN ,KOSTL = NULL -- Cost Center ,UMWRK = NULL -- Receiving/Issuing Plant ,UMLGO = NULL -- Receiving/Issuing Storage Location ,UMCHA = NULL -- Receiving/issuing batch (fin batch) ,GRUND = NULL -- Reason for manual valuation of net assets ,KZBEW = NULL -- Movement Indicator ,VFDAT = NULL -- Shelf Life Expiration or Best-Before Date ,ZMVTDATE = NULL -- GM Date ,ZMVTTIME = NULL -- GM Time ,ZVHILM = NULL ,LIFNR = NULL --,EBELP = RIGHT('0000'+CONVERT(VARCHAR, (CONVERT(INT, RFX_cap_310.rec_line)/10)), 4) ,EBELP = NULL FROM RFX_cap_110, RFX_cap_130, RFX_cap_180, RFX_cap_310, RFX_cap_340--, RFX_cap_341 WHERE RFX_cap_110.rec_ann = RFX_cap_310.rec_ann AND RFX_cap_110.rec_num = RFX_cap_310.rec_num AND RFX_cap_110.maj = RFX_cap_310.maj AND RFX_cap_110.rec_ann = RFX_cap_130.rec_ann AND RFX_cap_110.rec_num = RFX_cap_130.rec_num AND RFX_cap_110.maj = RFX_cap_130.maj AND RFX_cap_180.rec_ann =* RFX_cap_130.rec_ann AND RFX_cap_180.rec_num =* RFX_cap_130.rec_num AND RFX_cap_180.maj =* RFX_cap_130.maj AND RFX_cap_310.rec_ann = RFX_cap_340.rec_ann AND RFX_cap_310.rec_num = RFX_cap_340.rec_num AND RFX_cap_310.rec_line = RFX_cap_340.rec_line AND RFX_cap_310.maj = RFX_cap_340.maj AND RFX_cap_340.flag_shortages = '0' AND RFX_cap_310.rec_ann = '23' AND RFX_cap_310.rec_num = '000011150' AND RFX_cap_310.rec_line = '002700' -- AND RFX_cap_310.trait = 'N' AND RFX_cap_310.maj = '20230119100244' group by RFX_cap_180.rec_date_con, RFX_cap_130.supp_dn_num, RFX_cap_310.ref, RFX_cap_340.detail_owner, RFX_cap_340.detail_grade
Editor is loading...