store_comp.sql

Source: DMF/PuC/AIF/DR1 scripts/store_comp.sql

INSERT INTO ORI_STORE_COMP_CTRL (
    ORG_NUM,
    COMP_FLG,
    EFFECTIVE_FROM_DT
)
SELECT LTRIM(w.LOCNR, '0') AS ORG_NUM,
       CASE
         WHEN w.ZZHAUSAKTIVKZ IN ('1','2','3') THEN 'C'
         WHEN w.ZZHAUSAN = '1' THEN 'Y'
         WHEN w.ZZHAUSAN = '2' THEN 'N'
         ELSE 'N'
       END AS COMP_FLG,
       CASE
         WHEN w.ZZHAUSAKTIVKZ IN ('1','2','3')
              AND w.SCHLD IS NOT NULL
              AND w.SCHLD <> '00000000'
         THEN TO_DATE(w.SCHLD, 'YYYYMMDD')
         ELSE DATE '2000-01-01'
       END AS EFFECTIVE_FROM_DT
FROM STORE_ADD_CTRL s
JOIN sap_slt_prod_pv1.WRF1 w
  ON w.LOCNR = LPAD(TO_CHAR(s.STORE), LENGTH(w.LOCNR), '0')
WHERE s.ctrl_status = 'C';