195 lines
5.5 KiB
MySQL
195 lines
5.5 KiB
MySQL
|
--BEGIN;
|
||
|
|
|
||
|
|
WITH
|
||
|
|
req AS (
|
||
|
|
SELECT
|
||
|
|
36600000 value_d
|
||
|
|
)
|
||
|
|
---------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.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
|
||
|
|
WHERE
|
||
|
|
o.segm = 'Retail'
|
||
|
|
AND o.ship_season = '2022'
|
||
|
|
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
|
||
|
|
)
|
||
|
|
,adj AS (
|
||
|
|
SELECT
|
||
|
|
(SELECT value_d FROM req) - SUM(o.value_usd) adj_d
|
||
|
|
,((SELECT value_d FROM req) - SUM(o.value_usd))/SUM(o.value_usd) adj_p
|
||
|
|
FROM
|
||
|
|
collapse o
|
||
|
|
)
|
||
|
|
--SELECT * from adj;
|
||
|
|
----------------create a log entry--------------------
|
||
|
|
,log AS (
|
||
|
|
INSERT INTO
|
||
|
|
rlarp.osm_log(doc)
|
||
|
|
SELECT
|
||
|
|
$${
|
||
|
|
"message":"plug retail sales",
|
||
|
|
"tag":"retail plug",
|
||
|
|
"type":"build"
|
||
|
|
}$$::jsonb doc
|
||
|
|
RETURNING *
|
||
|
|
)
|
||
|
|
-------------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*adj.adj_p value_loc
|
||
|
|
,value_usd*adj.adj_p 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 volume' 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
|
||
|
|
,adj
|
||
|
|
)
|
||
|
|
--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;
|