192 lines
5.7 KiB
MySQL
192 lines
5.7 KiB
MySQL
|
--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;
|