Untitled
unknown
plain_text
3 years ago
7.1 kB
8
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_gradeEditor is loading...