Untitled

 avatar
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...