forecast_api/offline/scale_price_percent.sql

192 lines
5.7 KiB
PL/PgSQL

--BEGIN;
WITH
----------------create a log entry--------------------
log AS (
INSERT INTO
rlarp.osm_log(doc)
SELECT
$${
"message":"application of last price and target increases to all forecast orders",
"tag":"last price r1",
"type":"build"
}$$::jsonb doc
RETURNING *
)
---------collapse iterations-----------------------
,collapse AS (
SELECT
o.fspr
,o.plnt ---master data
,o.promo --history date mix
,o.terms
,o.bill_cust_descr --history cust mix
,o.ship_cust_descr --history cust mix
,o.dsm
,o.quota_rep_descr --master data
,o.director
,o.billto_group --master data
,o.shipto_group
,o.chan --master data
,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
,SUM(o.units) units
,SUM(o.value_loc) value_loc
,SUM(o.value_usd) value_usd
-----exclude any prior pricing adjustments from the "current" price in the forecast------
,SUM(o.value_loc) FILTER (WHERE iter <> 'upload price')/SUM(o.units) FILTER (WHERE iter <> 'upload price') price_loc
,SUM(o.cost_loc) cost_loc
,SUM(o.cost_usd) cost_usd
,o.calc_status --0
,o.flag --0
,o.order_date --history date mix
,o.order_month
,o.order_season
,o.request_date --history date mix
,o.request_month
,o.request_season
,o.ship_date --history date mix
,o.ship_month
,o.ship_season
FROM
rlarp.osm_pool o
--need to join to itemm to get the product from osm_pool
GROUP BY
o.fspr
,o.plnt ---master data
,o.promo --history date mix
,o.terms
,o.bill_cust_descr --history cust mix
,o.ship_cust_descr --history cust mix
,o.dsm
,o.quota_rep_descr --master data
,o.director
,o.billto_group --master data
,o.shipto_group
,o.chan --master data
,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
,o.calc_status --0
,o.flag --0
,o.order_date --history date mix
,o.order_month
,o.order_season
,o.request_date --history date mix
,o.request_month
,o.request_season
,o.ship_date --history date mix
,o.ship_month
,o.ship_season
HAVING
sum(o.units) <> 0
)
-------------build the iteration rows----------------
,ins AS (
SELECT
o.fspr
,o.plnt ---master data
,o.promo --history date mix
,o.terms
,o.bill_cust_descr --history cust mix
,o.ship_cust_descr --history cust mix
,o.dsm
,o.quota_rep_descr --master data
,o.director
,o.billto_group --master data
,o.shipto_group
,o.chan --master data
,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 --master data
,o.r_currency --history cust mix
,o.r_rate --master data
,o.c_currency --master data
,o.c_rate --master data
--,o.units
,0::numeric units
,value_loc*.45 value_loc
,value_usd*.45 value_usd
,0::numeric cost_loc
,0::numeric cost_usd
,o.calc_status --0
,o.flag --0
,o.order_date --history date mix
,o.order_month
,o.order_season
,o.request_date --history date mix
,o.request_month
,o.request_season
,o.ship_date --history date mix
,o.ship_month
,o.ship_season
---this iteration has to be listed in the master template file in order to be effectively included---
,'b22' AS version
,'upload price' iter
,log.id
,COALESCE(log.doc->>'tag','') "tag"
,log.doc->>'message' "comment"
,log.doc->>'type' module
FROM
collapse o
--need to join to itemm to get the product from osm_pool
,log
WHERE
-- a.productt = o.productt
--AND a.quota_rep_descr = o.quota_rep_descr
--AND a.majg = SUBSTRING(o.majg_descr,1,3)
--AND a.billto_group = o.billto_group
--AND a.shipto_group = o.shipto_group
--AND a.chgrp = substring(o.chan,1,1)
---only apply to 2022 orders----
---join p
o.order_date >= '2021-06-01'
AND o.segm <> 'Retail'
AND o.dsm = 'PW'
)
--SELECT *, SUM(VALUE_USD) OVER() FROM (SELECT order_season, order_month, sum(value_usd) value_usd from ins group by order_season, order_month) as x;
INSERT INTO rlarp.osm_pool SELECT * FROM ins;
--SELECT * FROM ins WHERE shipto_group = 'BATTLEFIELD FARMS' and part = 'XNT0TQT3X56B220PYDKP';
-----------aggregate the impact------------
--COMMIT;