plbuild/build_f20_suff.pg.sql

--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_suff;
CREATE FUNCTION rlarp.build_f20_suff(_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'),
    ('PC','1','PC'),
    ('PC','2','PC'),
    ('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
    ,1 vb
    ,CASE b.volume 
        WHEN '1' THEN i.uomp
        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 "CMS.CUSLG".itemm 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
        --i.uomp is an array of defalt units of measure from STKA, if we are trying to get from
        --a style code to a full20 part, we have to consider that column 3 pricing is only intended to hook up
        --with PLT denominated parts, and columns 1 and 2 are supposed to hook with with the listed
        --package unit. this achieved with the `vu` CTE above. however if a part has multiple default units
        --(STKA.V6CNTR) then it will join to the case and the pallet potentially. 
        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$