forecast_api/build/snap_cust_pool.sql

BEGIN;

--forego setting other customer attributes as they are not stored in osm_pool anyways

UPDATE
    RLARP.OSM_POOL S
SET
    DSM = CR.QUOTA_REP
FROM
(
    SELECT DISTINCT
        VERSION,
        COALESCE(SEGM,'') SEGM,
        MING_DESCR,
        BILL_CUST_DESCR,
        SHIP_CUST_DESCR,
        ------------quota rep column--------------
        CASE WHEN COALESCE(SUBSTRING(ming_descr,1,3),'') = '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(segm,'') = 'Retail' AND  COALESCE(cu.currep,'') <> '' THEN
                cu.currep
            --default logic
            ELSE
                CASE SUBSTR(bc.bvclas,2,3)
                    WHEN 'DIS' THEN
                        sc.bvsalm 
                    ELSE
                        bc.bvsalm 
                END
            END
        END QUOTA_REP
    FROM 
        RLARP.OSM_POOL S
        LEFT OUTER JOIN LGDAT.CUST BC ON
            BC.BVCUST = (regexp_match(S.bill_cust_descr,'(.*) - .*'))[1]
        LEFT OUTER JOIN LGDAT.CUST SC ON
            SC.BVCUST = (regexp_match(S.ship_cust_descr,'(.*) - .*'))[1]
        LEFT OUTER JOIN lgpgm.usrcust cu ON
            cu.cucust = (regexp_match(S.bill_cust_descr,'(.*) - .*'))[1]
    ) CR
WHERE
    CR.VERSION = S.VERSION
    AND CR.SEGM = COALESCE(S.SEGM,'')
    AND CR.MING_DESCR = S.MING_DESCR
    AND CR.BILL_CUST_DESCR = S.BILL_CUST_DESCR
    AND CR.SHIP_CUST_DESCR = S.SHIP_CUST_DESCR
    AND COALESCE(S.DSM,'') <> CR.QUOTA_REP;

UPDATE
    rlarp.osm_pool o
SET
    quota_rep_descr = (regexp_match(r.repp,'.* - (.*)$'))[1]
    ,director = r.director
FROM
    rlarp.repc r
WHERE
    r.rcode = o.dsm
    AND (
        quota_rep_descr <> (regexp_match(r.repp,'.* - (.*)$'))[1]
        OR o.director <> r.director
    );
COMMIT;