261 lines
7.6 KiB
PL/PgSQL
261 lines
7.6 KiB
PL/PgSQL
DROP FUNCTION rlarp.get_guidance_dseg CASCADE;
|
|
CREATE OR REPLACE FUNCTION rlarp.get_guidance_dseg(_bill text, _ship text, _stlc text, _dseg text, _qty numeric, _seas int)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql AS
|
|
--DO
|
|
$func$
|
|
DECLARE
|
|
--_item text;
|
|
--_bill text;
|
|
--_ship text;
|
|
--_qty numeric;
|
|
--_seas int;
|
|
_prem jsonb;
|
|
_mold text;
|
|
_item text;
|
|
_unti text;
|
|
_pltq numeric;
|
|
_cstd numeric;
|
|
_cstdina numeric;
|
|
_fstd numeric;
|
|
_fstdina numeric;
|
|
_cust text;
|
|
_curr text;
|
|
_rate numeric;
|
|
_v1ds text;
|
|
_v0ds text;
|
|
_v1tp jsonb;
|
|
_v0tp jsonb;
|
|
_chan text;
|
|
_regn text;
|
|
_rslt jsonb;
|
|
_targ jsonb;
|
|
_list jsonb;
|
|
_iidx jsonb;
|
|
_itemr text;
|
|
_input jsonb;
|
|
_product jsonb;
|
|
_customer jsonb;
|
|
_hist jsonb;
|
|
_pricing jsonb;
|
|
|
|
BEGIN
|
|
--_item := 'AMK06000G18B054';
|
|
--_bill := 'DIAM0004';
|
|
--_ship := 'DIAM0004';
|
|
--_qty := 5000;
|
|
--_seas := 2024;
|
|
|
|
_v0ds := CASE split_part(substring(_dseg,4,100), '.',1) WHEN 'B' THEN 'BASE' ELSE 'COLOR' END || CASE split_part(substring(_dseg,4,100), '.',2) WHEN 'L' THEN ' LABELED' WHEN 'P' THEN ' PRINTED' ELSE '' END;
|
|
_v1ds := _dseg;
|
|
|
|
_input := jsonb_build_object(
|
|
'inputs'
|
|
,jsonb_build_object(
|
|
'dseg',_dseg,
|
|
'v0ds',_v0ds,
|
|
'v1ds',_v1ds,
|
|
'bill',_bill,
|
|
'ship',_ship,
|
|
'stlc',_stlc,
|
|
'qty',_qty,
|
|
'season',_seas
|
|
));
|
|
|
|
----------------base product--------------------------------
|
|
SELECT
|
|
part_group
|
|
,item
|
|
,idxk
|
|
,prefer
|
|
,pltq
|
|
,curstdus
|
|
,curstdus_ina
|
|
,futstdus
|
|
,futstdus_ina
|
|
INTO
|
|
_mold
|
|
,_item
|
|
,_iidx
|
|
,_itemr
|
|
,_pltq
|
|
,_cstd
|
|
,_cstdina
|
|
,_fstd
|
|
,_fstdina
|
|
FROM
|
|
(
|
|
SELECT
|
|
i.partgroup part_group
|
|
,min(i.item) item
|
|
,i.stlc
|
|
,i.v1ds
|
|
,_ds.dataseg v0ds
|
|
,i.mpck pltq
|
|
,avg(i.curstdus) FILTER (WHERE aplnt <> 'I') curstdus
|
|
,avg(i.curstdus) curstdus_ina
|
|
,avg(i.futstdus) FILTER (WHERE aplnt <> 'I') futstdus
|
|
,avg(i.futstdus) futstdus_ina
|
|
,jsonb_strip_nulls(jsonb_build_object('assc',CASE WHEN i.assc <> '' THEN i.assc ELSE null::text END,'majg',i.majg::int,'coltier',i.coltier)) idxk
|
|
,CASE WHEN i.v1ds = _v1ds THEN 2 ELSE CASE WHEN _ds.dataseg = _v0ds THEN 1 ELSE 0 END END prefer
|
|
FROM
|
|
"CMS.CUSLG".itemm i
|
|
LEFT OUTER JOIN _ds ON
|
|
_ds.colgrp = i.colgrp
|
|
AND _ds.brand = SUBSTRING(i.branding,1,1)
|
|
--INNER JOIN rlarp.molds m ON
|
|
-- m.stlc = i.stlc
|
|
WHERE
|
|
i.stlc = _stlc
|
|
GROUP BY
|
|
i.partgroup
|
|
,i.stlc
|
|
,i.v1ds
|
|
,_ds.dataseg
|
|
,i.mpck
|
|
,jsonb_strip_nulls(jsonb_build_object('assc',CASE WHEN i.assc <> '' THEN i.assc ELSE null::text END,'majg',i.majg::int,'coltier',i.coltier))
|
|
,CASE WHEN i.v1ds = _v1ds THEN 2 ELSE CASE WHEN _ds.dataseg = _v0ds THEN 1 ELSE 0 END END
|
|
) best
|
|
ORDER BY
|
|
prefer DESC
|
|
LIMIT 1;
|
|
_product :=
|
|
jsonb_build_object(
|
|
'product'
|
|
,jsonb_build_object(
|
|
'mold',_mold
|
|
,'item',_item
|
|
,'itemrel',_itemr
|
|
,'iidx',_iidx
|
|
,'pltq',_pltq
|
|
,'cstd_usd',_cstd
|
|
,'cstd_usd_ina',_cstdina
|
|
,'fstd_usd',_fstd
|
|
,'fstd_usd_ina',_fstdina
|
|
)
|
|
);
|
|
--RAISE NOTICE 'item data %', jsonb_pretty(_product||_input);
|
|
|
|
----------------channel-------------------------------------
|
|
|
|
SELECT rlarp.get_cust(_bill, _ship) INTO _customer;
|
|
--_customer := jsonb_build_object('chan',_chan);
|
|
|
|
----------------customer------------------------------------
|
|
--SELECT dba INTO _cust FROM rlarp.cust WHERE code = CASE WHEN _chan = 'DRP' THEN _ship ELSE _bill END ;
|
|
--SELECT
|
|
-- currency,
|
|
-- (SELECT
|
|
-- x.rate
|
|
-- FROM
|
|
-- rlarp.ffcret x
|
|
-- WHERE
|
|
-- x.perd = (select fspr from rlarp.gld where drange @> current_date)
|
|
-- AND x.rtyp = 'MA'
|
|
-- and x.fcur = currency
|
|
-- AND x.tcur = 'US'
|
|
-- )
|
|
--INTO
|
|
-- _curr
|
|
-- ,_rate
|
|
--FROM
|
|
-- rlarp.cust
|
|
--WHERE
|
|
-- code = _bill;
|
|
|
|
--_customer := jsonb_build_object(
|
|
-- 'customer',
|
|
-- _customer||jsonb_build_object(
|
|
-- 'cust',_cust
|
|
-- ,'curr',_curr
|
|
-- ,'fxrate',_rate
|
|
-- )
|
|
--);
|
|
--RAISE NOTICE 'cust %', jsonb_pretty(_customer);
|
|
|
|
----------------price history-------------------------------
|
|
--RAISE NOTICE 'varb %', _customer;
|
|
SELECT jsonb_build_object('hist',rlarp.get_hist(_mold, _v1ds, _customer->'customer'->>'cust', substring(_customer->'customer'->>'chan',1,1))) INTO _hist;
|
|
--RAISE NOTICE 'history %', jsonb_pretty(_hist);
|
|
|
|
----------------target pricing------------------------------
|
|
SELECT
|
|
jsonb_build_object(
|
|
'v0tp',
|
|
target_price,
|
|
'v0stdv',
|
|
stdev_price
|
|
)
|
|
INTO
|
|
_v0tp
|
|
FROM
|
|
pricequote.market_setavgprice
|
|
WHERE
|
|
mold = _stlc
|
|
AND season = _seas
|
|
AND data_segment = _v0ds
|
|
AND region = 'ALL'
|
|
AND chan = _customer->'customer'->>'chantp';
|
|
----------------target pricing------------------------------
|
|
SELECT
|
|
jsonb_build_object(
|
|
'v1tp',
|
|
target_price,
|
|
'v1stdv',
|
|
stdev_price
|
|
)
|
|
INTO
|
|
_v1tp
|
|
FROM
|
|
pricequote.market_setavgprice
|
|
WHERE
|
|
mold = _stlc
|
|
AND season = _seas
|
|
AND data_segment = _dseg
|
|
AND region = 'ALL'
|
|
AND chan = _customer->'customer'->>'chantp';
|
|
--RAISE NOTICE 'target: %', jsonb_pretty(_targ);
|
|
_pricing := (COALESCE(_v0tp,'{}'::jsonb)||COALESCE(_v1tp,'{}'::jsonb));
|
|
|
|
----------------inflation index-----------------------------
|
|
SELECT
|
|
jsonb_build_object(
|
|
'iidx'
|
|
,jsonb_build_object(
|
|
priority
|
|
,min(factor)
|
|
)
|
|
)
|
|
INTO
|
|
_iidx
|
|
FROM
|
|
rlarp.costindex
|
|
WHERE
|
|
timeframe @> current_date
|
|
AND (
|
|
attr @> _iidx
|
|
OR attr @> jsonb_build_object('stlc',_mold)
|
|
)
|
|
GROUP BY
|
|
priority;
|
|
_pricing := _pricing||COALESCE(_iidx,'{}'::jsonb);
|
|
--RAISE NOTICE 'add targets: %', jsonb_pretty(_pricing);
|
|
|
|
----------------list pricing---------------------------------
|
|
SELECT coalesce(rlarp.get_list(_bill, _ship, _item, _qty),'{}'::jsonb) INTO _list;
|
|
_pricing := _pricing||_list;
|
|
--RAISE NOTICE 'add list: %', jsonb_pretty(_pricing);
|
|
|
|
----------------get premium for quote hist gap--------------
|
|
SELECT coalesce(rlarp.get_premium(_stlc, _seas, _customer->'customer'->>'chantp',_hist->'hist'->'cust'->>'ds', _v1ds),'{}'::jsonb) INTO _prem;
|
|
_pricing := jsonb_build_object('pricing',_pricing||_prem);
|
|
--RAISE NOTICE 'add bridge: %', jsonb_pretty(_pricing);
|
|
|
|
_rslt := _input||_product||_customer||_pricing||_hist;
|
|
|
|
|
|
RETURN _rslt;
|
|
|
|
END;
|
|
$func$;
|