forecast_api/build/build_pullforward.sql

/*
you have to be able to isolate the original plan in order for this to work
if you start with a clean 1+11 its not going to work
this script targets the 'plan' iteration and the 'diff' iteration
if plan orders have already been 'diffed' no need to build an additional diff
will have to be side-by-side roll-up of these two iterations
*/


--BEGIN;
--\timing
--CREATE TABLE IF NOT EXISTS rlarp.osmf_stage AS (SELECT * FROM rlarp.osmf_dev) WITH no data;
--will not match actuals exactly due to short-ships


WITH
---these dates will have to be syncronized with the forecast build-----------
tdr AS (
    SELECT
        -----------intented forecast range-----------------------------------
        DATERANGE('2021-06-01','2022-06-01','[)') prange
        -----------explicitly call out overlap period------------------------
        -----------anythign in this period has to net out to match actuals---
        ,DATERANGE('2021-06-01','2021-07-31','[]') overlap
        ,jsonb_build_array('plan','diff') iter
)
------actual orders according to whatever is in the forecast, has to be defined as 'plan' and 'diff'--------
,booked AS materialized(
    SELECT
        billto_group
        ,shipto_group
        ,part_group
        ,sum(units) units
    FROM
        rlarp.osm_pool
    WHERE
        --find actual orders in the overlap range (where orders fall in both target ranges)
        order_date <@ (SELECT overlap FROM tdr)
        --anything that falls in this range is supposed to already match actuals, so no need to filter for actuals
    GROUP BY
        billto_group
        ,shipto_group
        ,part_group
)
--SELECT * FROM booked
------------plan units according to the 'plan' iteration of the current forecast----------------------------
,planned AS (
    SELECT
         p.billto_group
        ,p.shipto_group
        ,p.part_group
        ,p.order_date
        ,p.ship_date
        ,sum(p.units) FILTER (WHERE iter = 'plan') plan_units
        ,sum(p.units) FILTER (WHERE iter = 'diff') diff_units
        ,b.units booked
    FROM
        rlarp.osm_pool p
        INNER JOIN booked b ON
            b.billto_group  = p.billto_group AND
            b.shipto_group  = p.shipto_group AND 
            b.part_group    = p.part_group
    WHERE
        --need to be able to isolate the original budget
        --budget orders have already been trued up to match actual, so should not be included in the this isolation
        --find actual orders in the overlap range (where orders fall in both target ranges)
        p.order_date <@ (SELECT prange FROM tdr)
        AND (SELECT iter FROM tdr) ? p.iter 
    GROUP BY
         p.billto_group
        ,p.shipto_group
        ,p.part_group
        ,p.order_date
        ,p.ship_date
        ,b.units
)
--SELECT * FROM planned LIMIT 100
-------flag rows by preparing a rolling total in this step------------------
,plan_ranked AS (
    SELECT
         p.billto_group
        ,p.shipto_group
        ,p.part_group
        ,p.order_date
        ,p.ship_date
        ,round(p.plan_units,2) plan_units
        ,round(SUM(p.plan_units) FILTER (WHERE order_date >= (SELECT upper(overlap) FROM tdr)) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group ORDER BY p.order_date, p.ship_date),2) plan_rolling
        ,round(SUM(p.plan_units) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group ),2) plan_units_tot
        --,round(SUM(COALESCE(p.plan_units,0) + COALESCE(p.diff_units,0)) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group ORDER BY p.order_date, p.ship_date),2) net_units_agg
        ,round(COALESCE(p.diff_units,0) + coalesce(p.plan_units,0),2) net_units
        ,round(SUM(COALESCE(p.plan_units,0) + COALESCE(p.diff_units,0)) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group),2) net_units_tot
        --,round(p.booked,2) booked
    FROM
        planned p
)
--SELECT * FROM plan_ranked
---flag future budget where up to the extent that actuals are larger than budget, but not more------
,eval AS (
    SELECT
         p.billto_group
        ,p.shipto_group
        ,p.part_group
        ,p.order_date
        ,p.ship_date
        ,p.plan_units
        ,p.plan_rolling
        ,p.plan_units_tot
        --,p.net_units_agg
        ,p.net_units_tot
        ,p.net_units
        --,p.booked
        -----total current units less plan total units is the excess
        -----if the current row contributes to the excess, flag it
        -----the last row will likely pull out too much, it will need split probably
        ,(plan_rolling - plan_units) <= (p.net_units_tot - p.plan_units_tot) flag
        ,-(p.net_units_tot - p.plan_units_tot) max_possible_reduction
        ,-CASE 
            WHEN plan_rolling <= (p.net_units_tot - p.plan_units_tot) THEN plan_units
            WHEN plan_rolling > (p.net_units_tot - p.plan_units_tot) AND (plan_rolling - plan_units) <= (p.net_units_tot - p.plan_units_tot) THEN (p.net_units_tot - p.plan_units_tot) - (plan_rolling - plan_units)
        END qty
        --,CASE WHEN (p.plan_units_agg - p.plan_units) > p.booked AND p.plan_units_agg <= p.booked * 2 THEN true ELSE false END remove
    FROM
        plan_ranked p
)
--SELECT * FROM eval LIMIT 10000
----------------create a log entry--------------------
,log AS (
    INSERT INTO
           rlarp.osm_log(doc)
    SELECT 
        $${
            "message":"remove future orders to the extent the new forecast totals more than the original budget",
            "tag":"1+11",
            "type":"build" 
        }$$::jsonb doc 
    RETURNING *
)
,remove AS (
    SELECT
         o.fspr
        ,o.plnt           
        ,o.promo          
        ,o.terms
        ,o.bill_cust_descr
        ,o.ship_cust_descr
        ,o.dsm
        ,o.quota_rep_descr
        ,o.director
        ,o.billto_group   
        ,o.shipto_group
        ,o.chan          
        ,o.chansub
        ,o.chan_retail
        ,o.part
        ,o.part_descr
        ,o.part_group
        ,o.branding
        ,o.majg_descr
        ,o.ming_descr
        ,o.majs_descr
        ,o.mins_descr
        ,o.segm
        ,o.substance
        ,o.fs_line         
        ,o.r_currency      
        ,o.r_rate          
        ,o.c_currency      
        ,o.c_rate          
        -----only keep the percentage of the current row determined by the last step--------
        ,round(-sum(o.units    ),2)*(-e.qty/e.plan_units) units
        ,round(-sum(o.value_loc),2)*(-e.qty/e.plan_units) value_loc
        ,round(-sum(o.value_usd),2)*(-e.qty/e.plan_units) value_usd
        ,round(-sum(o.cost_loc ),2)*(-e.qty/e.plan_units) cost_loc
        ,round(-sum(o.cost_usd ),2)*(-e.qty/e.plan_units) cost_usd
        ,o.calc_status   
        ,o.flag          
        ,o.order_date    
        ,o.order_month
        ,o.order_season
        ,o.request_date  
        ,o.request_month
        ,o.request_season
        ,o.ship_date     
        ,o.ship_month
        ,o.ship_season
        ,'1+11' AS version
        ,'pf' iter
        ,log.id
        ,coalescE(log.doc->>'tag','') AS "tag"
        ,log.doc->>'message' AS "comment"
        ,log.doc->>'type' module
    FROM
        rlarp.osm_pool o
        INNER JOIN eval e ON
            e.billto_group = o.billto_group AND
            e.shipto_group = o.shipto_group AND 
            e.part_group   = o.part_group   AND 
            e.order_date   = o.order_date   AND 
            e.ship_date    = o.ship_date    
        CROSS JOIN log
    WHERE
        e.flag
        AND e.plan_units <> 0
    GROUP BY
         o.fspr
        ,o.plnt           
        ,o.promo          
        ,o.terms
        ,o.bill_cust_descr
        ,o.ship_cust_descr
        ,o.dsm
        ,o.quota_rep_descr
        ,o.director
        ,o.billto_group   
        ,o.shipto_group
        ,o.chan          
        ,o.chansub
        ,o.chan_retail
        ,o.part
        ,o.part_descr
        ,o.part_group
        ,o.branding
        ,o.majg_descr
        ,o.ming_descr
        ,o.majs_descr
        ,o.mins_descr
        ,o.segm
        ,o.substance
        ,o.fs_line       
        ,o.r_currency    
        ,o.r_rate        
        ,o.c_currency    
        ,o.c_rate        
        ,(-e.qty/e.plan_units) --removal_rate
        ,o.calc_status   
        ,o.flag          
        ,o.order_date    
        ,o.order_month
        ,o.order_season
        ,o.request_date  
        ,o.request_month
        ,o.request_season
        ,o.ship_date     
        ,o.ship_month
        ,o.ship_season
        ,log.id
        ,coalescE(log.doc->>'tag','')
        ,log.doc->>'message'
        ,log.doc->>'type'
    -----some bad forecast swings, if one is removed it's wacky
    -----cap at -1,000,000
    HAVING
        round(-sum(o.value_loc),2) > -1000000
)
INSERT INTO rlarp.osm_pool SELECT * FROM remove;
-------sorted pull-forward scenarios------
--SELECT 
--    billto_group,
--    shipto_group,
--    part_group,
--    order_date,
--    ship_date,
--    SUM(value_usd)
--FROM 
--    remove 
--GROUP BY 
--    billto_group,
--    shipto_group,
--    part_group,
--    order_date,
--    ship_date
--ORDER BY
--    sum(value_usd) asc
--LIMIT 1000
-----pull-forward by month-----
--SELECT 
--    order_season, 
--    order_month, 
--    SUM(value_usd) 
--FROM 
--    remove 
--GROUP BY 
--    order_season
--    order_month
--ROLLBACK;