137 lines
3.0 KiB
SQL
137 lines
3.0 KiB
SQL
CREATE OR REPLACE VIEW rlarp.plcore_build_fullcode AS
|
|
WITH
|
|
f20 AS (
|
|
SELECT
|
|
b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
,b.suff
|
|
,b.uomp
|
|
,b.vol_uom
|
|
,b.vol_qty
|
|
,b.vol_price price
|
|
,b.listcode
|
|
,b.orig_row
|
|
,b.orig_col
|
|
,i.item
|
|
,i.clss
|
|
,i.aplnt
|
|
FROM
|
|
rlarp.plcore b
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.stlc = b.stlc
|
|
AND i.coltier = b.coltier
|
|
AND COALESCE(b.branding,'') = COALESCE(i.branding,'')
|
|
AND COALESCE(b.accs,'') = COALESCE(i.accs_ps,'')
|
|
AND COALESCE(b.suff,'') = COALESCE(i.suffix,'')
|
|
AND b.uomp = i.uomp
|
|
AND i.item NOT LIKE '9PT%'
|
|
WHERE
|
|
true
|
|
AND COALESCE(b.vol_price,0) <> 0
|
|
--AND listcode = 'GUAU'
|
|
)
|
|
--select * from f20;
|
|
,uom AS (
|
|
SELECT
|
|
uom.*
|
|
FROM
|
|
--aggregate converion targets to array
|
|
(
|
|
SELECT
|
|
jsonb_agg(row_to_json(d)::jsonb) jdoc
|
|
FROM
|
|
--inline table made up of conversion targets
|
|
(
|
|
SELECT DISTINCT
|
|
f20.item partn, 'PC' tu, f20.vol_uom fu
|
|
FROM
|
|
f20
|
|
) d
|
|
) c
|
|
JOIN LATERAL rlarp.uom_array(c.jdoc) uom ON TRUE
|
|
)
|
|
--SELECT * FROM uom
|
|
-------------------sort pricing to isolate duplicates----------------------
|
|
,grp AS (
|
|
SELECT
|
|
f.stlc
|
|
,f.coltier
|
|
,f.branding
|
|
,f.accs
|
|
,f.suff
|
|
,f.item
|
|
,f.uomp
|
|
,f.listcode
|
|
,f.orig_row
|
|
,f.orig_col
|
|
,f.clss
|
|
,f.vol_uom
|
|
,f.vol_qty
|
|
,round(round(u.nm/u.dm,0)/1000,5) vb_m
|
|
,'M' vbm_uom
|
|
,f.price
|
|
--,f.orig_row
|
|
--,f.orig_col
|
|
,COALESCE(
|
|
CASE WHEN f.price IS NOT NULL AND f.item IS NULL THEN 'No SKU' ELSE NULL::text END
|
|
,CASE WHEN f.price IS NOT NULL AND f.aplnt = 'I' THEN 'Inactive' ELSE NULL::text END
|
|
,CASE WHEN f.price IS NOT NULL AND f.item IS NOT NULL AND u.p IS NULL THEN 'No UOM Convesion' ELSE NULL::text END
|
|
) errorm
|
|
,current_user::text
|
|
,row_number() OVER (PARTITION BY f.listcode, f.item, round(round(u.nm/u.dm,0)/1000,5) ORDER BY f.price DESC) srt
|
|
FROM
|
|
f20 f
|
|
LEFT OUTER JOIN uom u ON
|
|
u.p = f.item
|
|
AND u.f = f.vol_uom
|
|
WHERE
|
|
true
|
|
AND COALESCE(f.price,0) <> 0
|
|
--AND f.aplnt <> 'I'
|
|
ORDER BY
|
|
f.listcode
|
|
,f.item
|
|
,round(round(u.nm/u.dm,0)/1000,5)
|
|
)
|
|
SELECT
|
|
f.stlc
|
|
,f.coltier
|
|
,f.branding
|
|
,f.accs
|
|
,f.suff
|
|
,f.item
|
|
,f.uomp
|
|
,f.listcode
|
|
,f.orig_row
|
|
,f.orig_col
|
|
,f.clss
|
|
,f.vol_uom
|
|
,f.vol_qty
|
|
,f.vb_m
|
|
,f.vbm_uom
|
|
,f.price
|
|
,f.errorm
|
|
,f.current_user
|
|
--,f.srt
|
|
FROM
|
|
grp f
|
|
WHERE
|
|
true
|
|
------any row with a srt <> 1 is a duplicate price point for the exact same scenario----
|
|
AND f.srt = 1
|
|
--AND f.aplnt <> 'I'
|
|
--AND f.item <> ''
|
|
--AND f.srt <> 1
|
|
--AND f.item = 'FHS12000WAXD032GR'
|
|
ORDER BY
|
|
f.listcode
|
|
,f.item
|
|
,f.vb_m;
|
|
-- ,errorm DESC
|
|
-- ,item ASC;
|
|
--END
|
|
--$function$
|
|
GRANT SELECT ON rlarp.plcore_build_fullcode TO PUBLIC;
|