forecast_api/build/convert_pool_all.sql

CREATE OR REPLACE PROCEDURE rlarp.convert_pool_all() 
LANGUAGE plpgsql AS
$func$
BEGIN

DELETE FROM rlarp.osmfs;

INSERT INTO
    rlarp.osmfs
SELECT
    ------------document ids---------------------
    null::int4,
    null::int4,
    null::int4,
    null::int4,
    null::int4,
    null::int4,
    null::int4,
    null::int4,
    ------------document dates-------------------
    order_date,
    request_date,
    null::date,
    null::date,
    ship_date,
    ------------document flags-------------------
    null::text,
    null::text,
    fspr,
    ------------document quantities--------------
    null::numeric,
    null::numeric,
    null::numeric,
    null::numeric,
    null::numeric,
    null::numeric,
    null::jsonb,
    ------------document attributes--------------
    null::text,
    plnt,
    promo,
    null::text,
    terms,
    null::text,
    null::text,
    ------------customer info---------------------
    null::text,
    rtrim(substring(bill_cust_descr,1,8)),
    null::text,
    null::text,
    null::text,
    rtrim(substring(ship_cust_descr,1,8)),
    null::text,
    null::text,
    dsm,
    billto_group,
    shipto_group,
    null::text,
    chan,
    chan,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    ------------product info----------------------
    part,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::text,
    null::numeric,
    null::numeric,
    ------------fiscal info-----------------------
    null::text,
    fs_line,
    r_currency,
    r_rate,
    c_currency,
    c_rate,
    units,
    value_loc,
    null::numeric,
    null::numeric,
    null::numeric,
    null::numeric,
    cost_loc,
    null::numeric,
    null::numeric,
    ------------status info-----------------------
    calc_status,
    flag,
    order_date,
    order_season,
    request_date,
    request_season,
    null::date promise_date,
    null::int  promise_season,
    ship_date,
    ship_season,
    version,
    iter,
    tag
FROM
    rlarp.osm_pool
WHERE
    true;
    --substring(iter,1,7) <> 'actuals';

-------need to set item master values before other things-----------
UPDATE
    RLARP.osmfs O
SET
     stlc = m.stlc
    ,colc = m.colc
    ,colgrp = m.colgrp
    ,coltier = m.coltier
    ,colstat = m.colstat
    ,sizc = m.sizc
    ,uomp = m.uomp
    ,suffix = m.suffix
    ,accs_ps = m.accs_ps
    ,brnd = m.branding
    ,majg = m.majg
    ,ming = m.ming
    ,majs = m.majs
    ,mins = m.mins
    ,gldc = m.glcd
    ,glec = m.glec
    ,harm = m.harm
    ,clss = m.clss
    ,brand = m.brand
    ,assc = m.assc
    ,lbs = CASE m.nwun WHEN 'kg' THEN 2.2046 ELSE 1 END*m.nwht
    ,unti = m.unti
FROM
    "CMS.CUSLG".itemm m
WHERE
    m.item = o.part;


WITH
plist AS (
    SELECT DISTINCT
        part
        ,plnt
    FROM
        rlarp.osmfs
)
,clist AS (
    SELECT
        p.part
        ,p.plnt
        ,COALESCE(im.cgstcs,ip.chstcs, ir.y0stcs) stdcost
    FROM
        plist p
        LEFT OUTER JOIN lgdat.icstm im ON
            im.cgpart = p.part
            AND im.cgplnt = p.plnt
        LEFT OUTER JOIN lgdat.icstp ip ON
            ip.chpart = p.part
            AND ip.chplnt = p.plnt
        LEFT OUTER JOIN lgdat.icstr ir ON
            ir.y0part = p.part
            AND ir.y0plnt = p.plnt
)
UPDATE
    rlarp.osmfs o
SET
    fb_cst_loc_cur = c.stdcost * o.fb_qty
FROM
    clist c
WHERE
    c.part = o.part
    AND  c.plnt = o.plnt;

----------------------------SET BILL-TO REP------------------------------------

UPDATE
    RLARP.osmfs S
SET
    BILL_REP = C.BVSALM
    ,BILL_CLASS = C.BVCLAS
    ,BILL_TERR = C.BVTERR
    ,BILL_CTRY = C.bvctry 
    ,bill_prov = C.bvprcd
    ,bill_post = C.bvpost
    ,remit_to = c.bvcomp
    ,ACCOUNT = CASE BVADR6 WHEN '' THEN BVNAME ELSE BVADR6 END
FROM
    LGDAT.CUST C
WHERE
    C.BVCUST = S.BILL_CUST
    AND (
        COALESCE(S.BILL_REP,'') <> C.BVSALM 
        OR COALESCE(S.BILL_CLASS,'') <> C.BVCLAS 
        OR COALESCE(S.BILL_TERR,'') <> C.BVTERR
        OR COALESCE(BILL_CTRY,'') <> C.bvctry 
        OR COALESCE(bill_prov,'') <> C.bvprcd
        OR COALESCE(bill_post,'') <> C.bvpost
        OR COALESCE(remit_to,'') <> c.bvcomp::text
    );
    
----------------------------SET SHIP-TO REP------------------------------------

UPDATE
    RLARP.osmfs S
SET
    SHIP_REP = C.BVSALM
    ,SHIP_CLASS = C.BVCLAS
    ,SHIP_TERR = C.BVTERR
    ,dest_CTRY = C.bvctry 
    ,dest_prov = C.bvprcd
    ,dest_post = C.bvpost 
FROM
    LGDAT.CUST C
WHERE
    C.BVCUST = S.SHIP_CUST
    AND (
           COALESCE(s.ship_rep,'') <> c.bvsalm 
        OR COALESCE(s.ship_class,'') <> c.bvclas 
        OR COALESCE(s.ship_terr,'') <> c.bvterr
        OR COALESCE(dest_ctry,'') <> c.bvctry 
        OR COALESCE(dest_prov,'') <> c.bvprcd
        OR COALESCE(dest_post,'') <> c.bvpost 
    );

----------------------------SET BILLTO GROUP------------------------------------

UPDATE
    RLARP.osmfs O
SET
    ACCOUNT = CASE BVADR6 WHEN '' THEN BVNAME ELSE BVADR6 END
FROM
    LGDAT.CUST C
WHERE
    C.BVCUST = O.BILL_CUST
    AND coalesce(account,'') <> CASE BVADR6 WHEN '' THEN BVNAME ELSE BVADR6 END;
    


----------------------------SET SHIPTO GROUP------------------------------------

UPDATE
    RLARP.osmfs O
SET
    SHIPGRP = CASE BVADR6 WHEN '' THEN BVNAME ELSE BVADR6 END
FROM
    LGDAT.CUST C
WHERE
    C.BVCUST = O.SHIP_CUST
    AND CASE BVADR6 WHEN '' THEN BVNAME ELSE BVADR6 END <> COALESCE(O.SHIPGRP,'');


---------------------------SET CHANNEL-----------------------------------------


UPDATE
    rlarp.osmfs
SET
    CHAN =  CASE SUBSTRING(BILL_CLASS,2,3) 
                --if the bill to class is ditsributor, then it's either warehouse or drop
                WHEN 'DIS' THEN
                    --if the ship-to is a different name than the bill-to then it's drop, otherwise it's warehouse
                    CASE SUBSTRING(SHIP_CLASS,2,3)
                        WHEN 'DIS' THEN 'WHS'
                        ELSE 'DRP'
                    END
                    --CASE WHEN RTRIM(SUBSTRING(LTRIM(SC.BVADR7)||SC.BVNAME,1,30)) = RTRIM(SUBSTRING(LTRIM(BC.BVADR7)||BC.BVNAME,1,30)) THEN 'DIS' ELSE 'DRP' END
                --everything else does not involve a distributor and is considered direct
                ELSE 'DIR'
            END,
    CHANSUB =   CASE SUBSTRING(BILL_CLASS,2,3) 
                    WHEN 'DIS' THEN
                        --if the ship-to is a different name than the bill-to then it's drop, otherwise it's warehouse
                        CASE SUBSTRING(SHIP_CLASS,2,3)
                            WHEN 'DIS' THEN 'WHS'
                            ELSE CASE SUBSTRING(SHIP_CLASS,1,1) WHEN 'R' THEN 'RDP' ELSE 'DRP' END
                        END
                    WHEN 'MAS' THEN 'RMN'
                    WHEN 'NAT' THEN 'RMN'
                    ELSE CASE SUBSTRING(SHIP_CLASS,1,1) WHEN 'R' THEN 'RDI' ELSE 'DIR' END
                END
WHERE
        COALESCE(CHAN,'') <>  CASE SUBSTRING(BILL_CLASS,2,3) 
                --if the bill to class is ditsributor, then it's either warehouse or drop
                WHEN 'DIS' THEN
                    --if the ship-to is a different name than the bill-to then it's drop, otherwise it's warehouse
                    CASE SUBSTRING(SHIP_CLASS,2,3)
                        WHEN 'DIS' THEN 'WHS'
                        ELSE 'DRP'
                    END
                    --CASE WHEN RTRIM(SUBSTRING(LTRIM(SC.BVADR7)||SC.BVNAME,1,30)) = RTRIM(SUBSTRING(LTRIM(BC.BVADR7)||BC.BVNAME,1,30)) THEN 'DIS' ELSE 'DRP' END
                --everything else does not involve a distributor and is considered direct
                ELSE 'DIR'
            END
        OR
        COALESCE(CHANSUB,'') <> CASE SUBSTRING(BILL_CLASS,2,3) 
                    WHEN 'DIS' THEN
                        --if the ship-to is a different name than the bill-to then it's drop, otherwise it's warehouse
                        CASE SUBSTRING(SHIP_CLASS,2,3)
                            WHEN 'DIS' THEN 'WHS'
                            ELSE CASE SUBSTRING(SHIP_CLASS,1,1) WHEN 'R' THEN 'RDP' ELSE 'DRP' END
                        END
                    WHEN 'MAS' THEN 'RMN'
                    WHEN 'NAT' THEN 'RMN'
                    ELSE CASE SUBSTRING(SHIP_CLASS,1,1) WHEN 'R' THEN 'RDI' ELSE 'DIR' END
                END;


---------------------------SET QUOTA REP---------------------------------------


UPDATE
    RLARP.osmfs S
SET
    DSM = CR.QUOTA_REP
FROM
(
    SELECT DISTINCT
        VERSION,
        COALESCE(GLEC,'') GLEC,
        COALESCE(MING,'') MING,
        BILL_CUST,
        SHIP_CUST,
        ------------quota rep column--------------
        CASE WHEN COALESCE(ming,'') = 'B52' THEN 'PW' ELSE
            --if the gl expense code is 1RE use the retail rep assigned to the bill-to customer if available
            CASE WHEN COALESCE(glec,'') = '1RE' AND  COALESCE(cu.currep,'') <> '' THEN
                cu.currep
            --default logic
            ELSE
                CASE SUBSTR(bill_class,2,3)
                    WHEN 'DIS' THEN
                        ship_rep
                    ELSE
                        bill_rep
                END
            END
        END QUOTA_REP

    FROM 
        RLARP.osmfs S
        LEFT OUTER JOIN lgdat.cust ON
            BVCUST = BILL_CUST
        LEFT OUTER JOIN lgpgm.usrcust cu ON 
            cu.cucust = s.bill_cust
    WHERE
        COALESCE(GLEC,'') IS NOT NULL
    ) CR
WHERE
        CR.VERSION = S.VERSION
    AND CR.GLEC = COALESCE(S.GLEC,'')
    AND CR.MING = COALESCE(S.MING,'')
    AND CR.BILL_CUST = S.BILL_CUST 
    AND CR.SHIP_CUST = S.SHIP_CUST
    AND COALESCE(S.DSM,'') <> CR.QUOTA_REP;


-------------------set fiscal period--------------------------------------

UPDATE
    rlarp.osmfs f
SET
    fspr = gld.fspr
FROM
    (
        SELECT 
            N1COMP COMP
            ,N1CCYY FSYRq
            ,KPMAXP PERDS
            ,N1FSPP PERD
            ,to_char(N1FSYP,'FM0000') FSPR
            ,N1SD01 SDAT
            ,N1ED01 EDAT
            ,to_char(N1ED01,'yymm') CAPR
            ,N1ED01 - N1SD01 +1 NDAYS 
            ,CASE WHEN EXTRACT(MONTH FROM N1ED01) >= 6 THEN EXTRACT(YEAR FROM N1ED01) + 1 ELSE EXTRACT(YEAR FROM N1ED01) END SSYR
            ,to_char(CASE WHEN EXTRACT(MONTH FROM N1ED01) >= 6 THEN EXTRACT(MONTH FROM N1ED01) -5  ELSE EXTRACT(MONTH FROM N1ED01) +7 END,'00') SSPR
        FROM 
            LGDAT.GLDATREF 
            INNER JOIN LGDAT.GLDATE ON 
                KPCOMP = N1COMP AND 
                KPCCYY = N1CCYY
        WHERE
            N1COMP = 93
            --AND DIGITS(N1FSYP) = '1901'
    ) gld
WHERE
    f.sdate BETWEEN gld.sdat AND gld.edat
    AND coalesce(f.fspr,'') <> gld.fspr;

--avoid setting fx as that should have been done up front to osmf
--UPDATE
--    rlarp.osmfS_dev
--SET
--    r_rate = .7900
--WHERE
--    r_currency = 'CA';
--
--UPDATE
--    rlarp.osmfS_dev
--SET
--    r_rate = 1
--WHERE
--    r_currency = 'US';
--
--UPDATE
--    rlarp.osmfS_dev
--SET
--    c_rate = .7900
--WHERE
--    c_currency = 'CA';
--
--UPDATE
--    rlarp.osmfS_dev
--SET
--    c_rate = 1
--WHERE
--    c_currency = 'US';

--DELETE FROM rlarp.osmf_dev WHERE iter IN ('adj price','adj volume');

--INSERT INTO rlarp.osmf_dev SELECT * FROM rlarp.osmf;

COMMIT;
END
$func$;

GRANT ALL ON PROCEDURE rlarp.convert_pool_all() TO PUBLIC;