90 lines
2.4 KiB
SQL
90 lines
2.4 KiB
SQL
WITH
|
|
----------extract full code listing-------------------------------------------
|
|
pbf As (
|
|
SELECT
|
|
pbf.*
|
|
,vb_m * 1000 volume
|
|
FROM
|
|
rlarp.plcore_build_fullcode pbf
|
|
WHERE
|
|
errorm IS NULL
|
|
AND vb_m IS NOT NULL
|
|
AND price IS NOT NULL
|
|
)
|
|
----------create per part prices for each price level------------------------
|
|
,lpbf AS MATERIALIZED (
|
|
SELECT
|
|
i.jbplvl plevel
|
|
,pbf.item
|
|
,pbf.listcode
|
|
,pbf.volume
|
|
,pbf.price / CASE WHEN vbm_uom = 'M' then 1000 ELSE 1 END::numeric price
|
|
FROM
|
|
"CMS.CUSLG".iprcbhc i
|
|
INNER JOIN pbf ON
|
|
pbf.listcode = i.jbplcd
|
|
WHERE
|
|
current_date BETWEEN i.jbfdat AND i.jbtdat
|
|
)
|
|
--SELECT * FROM lpbf
|
|
----------join to price list-----------------------
|
|
,plj AS (
|
|
SELECT
|
|
o.*
|
|
,g.sspr || ' - ' || to_char(o.odate,'Mon') omonth
|
|
,c.plevel
|
|
,lpbf.listcode
|
|
,lpbf.volume
|
|
,lpbf.price
|
|
,row_number() OVER (PARTITION BY "ddord#",
|
|
"dditm#",
|
|
"fgbol#",
|
|
"fgent#",
|
|
"diinv#",
|
|
"dilin#",
|
|
"quoten",
|
|
"quotel"
|
|
ORDER BY lpbf.price ASC
|
|
) rn
|
|
FROM
|
|
rlarp.osm o
|
|
INNER JOIN rlarp.cust c ON
|
|
c.code = o.bill_cust
|
|
LEFT OUTER JOIN rlarp.gld g ON
|
|
o.odate <@ g.drange
|
|
LEFT OUTER JOIN lpbf ON
|
|
lpbf.plevel = c.plevel
|
|
AND lpbf.item = o.part
|
|
AND o.ddqtoi::numeric >= lpbf.volume
|
|
WHERE
|
|
calc_status <> 'CANCELED'
|
|
AND substring(glec,1,1) <= '2'
|
|
AND fs_line = '41010'
|
|
AND version = 'ACTUALS'
|
|
AND chan = 'WHS'
|
|
AND oseas = 2022
|
|
--AND o.odate >= '2022-02-01'
|
|
)
|
|
SELECT
|
|
p.account
|
|
,p.shipgrp
|
|
,p.oseas
|
|
,p.omonth
|
|
,p.promo
|
|
,p.part
|
|
,p.listcode
|
|
,ROUND(SUM(p.fb_qty) ,2) qty
|
|
,ROUND(SUM(p.fb_val_loc * p.r_rate) ,2) orig_usd
|
|
,ROUND(SUM(COALESCE(p.fb_qty * p.price * p.r_rate * CASE WHEN p.promo ~ 'EOP|EARLY' THEN 0.95 ELSE 1.00 END,p.fb_val_loc * p.r_rate)),2) new_usd
|
|
FROM
|
|
plj p
|
|
WHERE
|
|
rn = 1
|
|
GROUP BY
|
|
p.account
|
|
,p.shipgrp
|
|
,p.oseas
|
|
,p.omonth
|
|
,p.promo
|
|
,p.part
|
|
,p.listcode |