129 lines
2.9 KiB
PL/PgSQL
129 lines
2.9 KiB
PL/PgSQL
DROP FUNCTION IF EXISTS rlarp.plcore_fullcode_inq;
|
|
CREATE FUNCTION rlarp.plcore_fullcode_inq (_items jsonb)
|
|
RETURNS TABLE (
|
|
stlc text
|
|
,coltier text
|
|
,branding text
|
|
,accs text
|
|
,suffix text
|
|
,item text
|
|
,uomp text
|
|
,clss text
|
|
,vb numeric
|
|
,vb_uom text
|
|
,vb_m numeric
|
|
,vbm_uom text
|
|
,price numeric
|
|
,orig_row numeric
|
|
,orig_col numeric
|
|
,error text
|
|
,usern text
|
|
,listcode text
|
|
)
|
|
LANGUAGE plpgsql AS $function$
|
|
|
|
BEGIN
|
|
|
|
RETURN QUERY
|
|
WITH
|
|
f20 AS (
|
|
SELECT
|
|
b.stlc
|
|
,b.coltier
|
|
,b.branding
|
|
,b.accs
|
|
,b.suffix
|
|
,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
|
|
jsonb_to_recordset(_items) AS b (
|
|
stlc text
|
|
,coltier text
|
|
,branding text
|
|
,accs text
|
|
,suffix text
|
|
,uomp text
|
|
,vol_uom text
|
|
,vol_qty numeric
|
|
,vol_price numeric
|
|
,listcode text
|
|
,orig_row numeric
|
|
,orig_col numeric
|
|
)
|
|
--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.suffix,'') = COALESCE(i.suffix,'')
|
|
AND b.uomp = i.uomp
|
|
)
|
|
--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
|
|
SELECT
|
|
f.stlc
|
|
,f.coltier
|
|
,f.branding
|
|
,f.accs
|
|
,f.suffix
|
|
,f.item
|
|
,f.uomp
|
|
,f.clss
|
|
,f.vol_qty vb
|
|
,f.vol_uom vb_uom
|
|
,round(f.vol_qty*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
|
|
) error
|
|
,current_user::text usern
|
|
,f.listcode
|
|
FROM
|
|
f20 f
|
|
LEFT OUTER JOIN uom u ON
|
|
u.p = f.item
|
|
AND u.f = f.vol_uom
|
|
WHERE
|
|
true
|
|
--AND f.aplnt <> 'I'
|
|
ORDER BY
|
|
orig_row
|
|
,orig_col
|
|
,error DESC
|
|
,item ASC;
|
|
END
|
|
$function$
|