78 lines
2.2 KiB
SQL
78 lines
2.2 KiB
SQL
WITH
|
|
--------list of list codes-----------
|
|
nrl AS (
|
|
SELECT DISTINCT
|
|
vers->>'NRList' nrlist
|
|
FROM
|
|
rlarp.ffterr f
|
|
WHERE
|
|
vers->>'NRList' IS NOT NULL
|
|
UNION ALL
|
|
SELECT DISTINCT
|
|
vers->>'GAList' nrlist
|
|
FROM
|
|
rlarp.ffterr f
|
|
WHERE
|
|
vers->>'GAList' IS NOT NULL
|
|
)
|
|
---------full code listing for all list codes------------------
|
|
,fc AS (
|
|
SELECT
|
|
item
|
|
,listcode
|
|
,vb_m*1000 vb
|
|
,price/1000 price
|
|
,row_number() OVER (PARTITION BY item, listcode ORDER BY price ASC) rn
|
|
FROM
|
|
rlarp.plcore_build_fullcode pbf
|
|
WHERE
|
|
pbf.listcode IN (SELECT nrlist FROM nrl)
|
|
AND item IS NOT NULL
|
|
AND errorm IS NULL
|
|
)
|
|
---------link to matrix---------------------------------------
|
|
SELECT
|
|
--o.bill_dba
|
|
o.bill_cust
|
|
--,o.ship_dba
|
|
,o.ship_cust
|
|
,o.part
|
|
,o.qtyord
|
|
,o.segm
|
|
,f.vers->>CASE o.segm
|
|
WHEN 'Nursery' THEN 'NRList'
|
|
WHEN 'Greenhouse' THEN 'GAList'
|
|
ELSE ''
|
|
END rlist
|
|
--,fc.vb
|
|
,min(fc.price) price
|
|
FROM
|
|
rlarp.osm_stack o
|
|
LEFT OUTER JOIN rlarp.ffterr f ON
|
|
f.ctry = o.dest_ctry
|
|
AND f.prov = o.dest_prov
|
|
LEFT OUTER JOIN fc ON
|
|
fc.item = o.part
|
|
AND fc.listcode = f.vers->>CASE o.segm
|
|
WHEN 'Nursery' THEN 'NRList'
|
|
WHEN 'Greenhouse' THEN 'GAList'
|
|
ELSE ''
|
|
END
|
|
AND o.qtyord >= fc.vb
|
|
WHERE
|
|
--item ~ 'AZA06000G18C'
|
|
ordnum = ' 899872 5'
|
|
GROUP BY
|
|
--o.bill_dba
|
|
o.bill_cust
|
|
--,o.ship_dba
|
|
,o.ship_cust
|
|
,o.part
|
|
,o.qtyord
|
|
,o.segm
|
|
,f.vers->>CASE o.segm
|
|
WHEN 'Nursery' THEN 'NRList'
|
|
WHEN 'Greenhouse' THEN 'GAList'
|
|
ELSE ''
|
|
END
|