130 lines
4.2 KiB
PL/PgSQL
130 lines
4.2 KiB
PL/PgSQL
DROP FUNCTION IF EXISTS rlarp.plcore_build_fullcode_cust(text,date);
|
|
|
|
CREATE FUNCTION rlarp.plcore_build_fullcode_cust(_price_lev text, _eff_date date)
|
|
RETURNS TABLE (
|
|
"Currency" text
|
|
,"Product" text
|
|
,"Description1" text
|
|
,"Description2" text
|
|
,"Included Accessories" text
|
|
,"PKG" text
|
|
,"Qty(M)" numeric
|
|
--,"Price" numeric
|
|
,"Cases Per Pallet" numeric
|
|
,"Single Case/Bulk Price Per M" numeric
|
|
,"Master Pallet Price Per M" numeric
|
|
,"Case/Bulk Price Per Package" numeric
|
|
,"Master Pallet Price Per Pallet" numeric
|
|
,"Bulk Pallet Price per Pallet" numeric
|
|
,"Master Pallet Price Per Case" numeric
|
|
,"Minimum Order(M)" numeric
|
|
--,"Inventory Class" text
|
|
)
|
|
LANGUAGE plpgsql AS
|
|
$function$
|
|
BEGIN
|
|
|
|
RETURN QUERY
|
|
WITH
|
|
----------sequence the volume breaks and assume that the second one is the master pallet volume-------------------
|
|
raw AS (
|
|
SELECT
|
|
CASE substring(pbf.listcode,4,1)
|
|
WHEN 'U' THEN 'USD'
|
|
WHEN 'C' THEN 'CAD'
|
|
ELSE ''
|
|
END currency
|
|
,pbf.listcode
|
|
,RTRIM(pbf.item) jcpart
|
|
,i.descr
|
|
,i.descr2
|
|
,pbf.vbm_uom jcunit
|
|
,pbf.price jcpric
|
|
,pbf.vb_m jcvoll
|
|
,i.uomp
|
|
,CASE WHEN i.mino = '0' THEN null::bigint ELSE i.mino::bigint END mino
|
|
,i.clss
|
|
,i.acc_list
|
|
,row_number() OVER (PARTITION BY pbf.listcode, pbf.item ORDER BY pbf.vb_m ASC) seq
|
|
FROM
|
|
rlarp.plcore_build_fullcode pbf
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.item = pbf.item
|
|
WHERE
|
|
pbf.price IS NOT NULL
|
|
AND pbf.errorm IS NULL
|
|
AND pbf.item IS NOT NULL
|
|
AND pbf.listcode IN (
|
|
SELECT
|
|
jbplcd
|
|
FROM
|
|
"CMS.CUSLG".iprcbhc p
|
|
WHERE
|
|
p.jbplvl = _price_lev
|
|
AND _eff_date BETWEEN p.jbfdat AND p.jbtdat
|
|
)
|
|
--AND pbf.listcode = 'GUAU'
|
|
)
|
|
--SELECT * FROM raw
|
|
----------start with only all sequence 1 items and then rejoin to get the 2nd break as a new column----------------
|
|
,pivot AS (
|
|
SELECT
|
|
w.currency
|
|
,w.listcode
|
|
,w.jcpart
|
|
,w.descr
|
|
,w.descr2
|
|
,w.acc_list
|
|
,w.jcunit
|
|
,w.jcpric
|
|
,w.seq
|
|
,w.uomp
|
|
,w.jcvoll
|
|
,ceiling(w.mino::numeric/(w.jcvoll::numeric*1000))*w.jcvoll mino
|
|
,w.clss
|
|
,CASE WHEN w.uomp = 'PLT' THEN null::numeric ELSE ROUND(COALESCE(c.jcvoll/w.jcvoll,1),2) END cse_per_plt
|
|
,CASE WHEN substring(w.listcode,1,1) = 'N' AND w.uomp <> 'PLT' THEN null::numeric ELSE w.jcpric END cse_price_m
|
|
,c.jcpric plt_price_m
|
|
,CASE WHEN substring(w.listcode,1,1) = 'N' AND w.uomp <> 'PLT' THEN null::numeric ELSE round(w.jcpric*w.jcvoll,2) END cse_price_pkg
|
|
,round(c.jcpric*c.jcvoll,2) plt_price_pkg
|
|
,round(CASE WHEN w.uomp = 'PLT' THEN w.jcpric*w.jcvoll ElSE CAST(null as numeric) END,2) plt_price_plt
|
|
,round(CASE WHEN w.uomp <> 'PLT' THEN cast(c.jcpric*c.jcvoll as numeric)/cast(c.jcvoll/w.jcvoll as numeric) ElSE CAST(null as numeric) END,2) plt_price_cse
|
|
FROM
|
|
raw w
|
|
LEFT OUTER JOIN raw c ON
|
|
c.jcpart = w.jcpart
|
|
AND c.seq = 2
|
|
AND c.listcode = w.listcode
|
|
WHERE
|
|
w.seq = 1
|
|
ORDER BY
|
|
w.jcpart
|
|
,w.seq
|
|
)
|
|
------------pretty column names-----------------------------------------
|
|
SELECT
|
|
currency "Currency"
|
|
,jcpart "Product"
|
|
,descr "Description1"
|
|
,descr2 "Description2"
|
|
,acc_list "Included Accessories"
|
|
,uomp "UOM"
|
|
,jcvoll "Case Qty"
|
|
--,jcpric "Price"
|
|
,cse_per_plt "Cases Per Pallet"
|
|
,cse_price_m "Single Case/Bulk Price Per M"
|
|
,plt_price_m "Master Pallet Price Per M"
|
|
,cse_price_pkg "Case/Bulk Price Per Package"
|
|
,plt_price_pkg "Master Pallet Price Per Pallet"
|
|
,plt_price_plt "Bulk Pallet Price per Pallet"
|
|
,plt_price_cse "Master Pallet Price Per Case"
|
|
,mino "Non-Stocking Minimum"
|
|
--,clss "Inventory Class"
|
|
--,japlcd "delete this column"
|
|
FROM
|
|
pivot
|
|
ORDER BY
|
|
jcpart;
|
|
END
|
|
$function$
|