172 lines
4.1 KiB
PL/PgSQL
172 lines
4.1 KiB
PL/PgSQL
--DROP TABLE IF EXISTS rlarp.plbuildr1;
|
|
--
|
|
--CREATE TABLE Rlarp.plbuildr1 (
|
|
-- stlc text
|
|
-- ,coltier text
|
|
-- ,branding text
|
|
-- ,kit text
|
|
-- ,suffix text
|
|
-- ,item text
|
|
-- ,clss text
|
|
-- ,vb_m numeric
|
|
-- ,price text
|
|
-- ,uomp text
|
|
-- ,orig_row text
|
|
-- ,orig_col text
|
|
-- ,error text
|
|
-- ,usern text
|
|
--);
|
|
--
|
|
--GRANT ALL ON rlarp.plbuildr1 TO public;
|
|
--
|
|
--
|
|
DROP FUNCTION IF EXISTS rlarp.build_f20;
|
|
CREATE FUNCTION rlarp.build_f20(_items jsonb)
|
|
RETURNS TABLE (
|
|
stlc text
|
|
,coltier text
|
|
,branding text
|
|
,accs text
|
|
--,suffix text
|
|
,item text
|
|
,uomp text
|
|
,clss text
|
|
,vb integer
|
|
,vb_uom text
|
|
,vb_m numeric
|
|
,vbm_uom text
|
|
,price numeric
|
|
,orig_row numeric
|
|
,orig_col numeric
|
|
,error text
|
|
,usern text
|
|
)
|
|
LANGUAGE plpgsql AS $function$
|
|
|
|
BEGIN
|
|
|
|
DROP TABLE IF EXISTS f20;
|
|
CREATE TEMPORARY TABLE f20 AS (
|
|
WITH
|
|
---------get to a target pack unit based on the column; bulk should always be PLT-----------------------------------
|
|
vu AS (
|
|
SELECT * FROM ( VALUES
|
|
('CSE','1','CSE'),
|
|
('CSE','2','CSE'),
|
|
('CSE','3','PLT'),
|
|
('BDL','1','BDL'),
|
|
('BDL','2','BDL'),
|
|
('BDL','3','PLT'),
|
|
('BDL','1','SLV'),
|
|
('BDL','2','SLV')
|
|
--('BDL','3','PLT')
|
|
) x(cont,vol,uom)
|
|
)
|
|
SELECT
|
|
b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
--,b.suffix
|
|
,b.container
|
|
,b.volume
|
|
,i.item
|
|
,i.clss
|
|
,i.uomp->>0 uomp
|
|
,1 vb
|
|
,CASE b.volume
|
|
WHEN '1' THEN i.uomp->>0
|
|
WHEN '2' THEN 'PLT'
|
|
WHEN '3' THEN 'PLT'
|
|
END vb_uom
|
|
,b.price
|
|
,b.orig_row
|
|
,b.orig_col
|
|
,i.aplnt
|
|
FROM
|
|
jsonb_to_recordset(_items) AS b(
|
|
stlc text
|
|
,coltier text
|
|
,branding text
|
|
,accs text
|
|
--,suffix text
|
|
,container text
|
|
,volume text
|
|
,price numeric
|
|
,orig_row numeric
|
|
,orig_col numeric )
|
|
INNER JOIN vu ON
|
|
vu.cont = b.container
|
|
AND vu.vol = b.volume --curently the volume level is an integer that reflect which column the price is in
|
|
LEFT OUTER JOIN rlarp.itemmv i ON
|
|
i.stlc = b.stlc
|
|
AND i.coltier = b.coltier
|
|
AND COALESCE(b.branding,'') = COALESCE(i.branding,'')
|
|
---------flipping i.accs_ps/i.accs will change accessory linkage method----------
|
|
AND COALESCE(b.accs,'') = COALESCE(i.accs_ps,i.accs,'')
|
|
--AND COALESCE(b.suffix,'') = COALESCE(i.suffix,'')
|
|
--AND i.uomp->>0 = vu.uom
|
|
--revise to hook if any of the default units matches the target
|
|
AND i.uomp ? vu.uom
|
|
) WITH DATA;
|
|
|
|
--select * from f20;
|
|
RETURN QUERY
|
|
WITH
|
|
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.vb_uom fu
|
|
FROM
|
|
f20
|
|
) d
|
|
) c
|
|
JOIN LATERAL rlarp.uom_array(c.jdoc) uom ON TRUE
|
|
)
|
|
SELECT
|
|
f.stlc
|
|
,f.coltier
|
|
,f.branding
|
|
,f.accs
|
|
--,f.suffix
|
|
,f.item
|
|
,f.uomp
|
|
,f.clss
|
|
,f.vb
|
|
,f.vb_uom
|
|
,round(u.nm/u.dm,0)/1000 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
|
|
FROM
|
|
f20 f
|
|
LEFT OUTER JOIN uom u ON
|
|
u.p = f.item
|
|
AND u.f = f.vb_uom
|
|
WHERE
|
|
true
|
|
--AND f.aplnt <> 'I'
|
|
ORDER BY
|
|
orig_row
|
|
,orig_col
|
|
,errorm DESC
|
|
,item ASC;
|
|
END
|
|
$function$
|