forecast_api/build/snap_cost_current.sql

BEGIN; 

WITH
plist AS (
    SELECT DISTINCT
        part
        ,plnt
    FROM
        rlarp.osmf
        -----prevent a list of fake parts
        INNER JOIN lgdat.stka ON
            v6part = part
            AND v6plnt = plnt
)
,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.osmf o
SET
    fb_cst_loc_cur = c.stdcost * o.fb_qty
FROM
    clist c
WHERE
    c.part = o.part
    AND  c.plnt = o.plnt;
    --AND version = 'b21';

commit;