105 lines
2.3 KiB
PL/PgSQL
105 lines
2.3 KiB
PL/PgSQL
DROP FUNCTION IF EXISTS rlarp.get_option_costs_priceg;
|
|
CREATE FUNCTION rlarp.get_option_costs_priceg(_priceg text, _majg text)
|
|
RETURNS TABLE (
|
|
stlc text
|
|
,coltier text
|
|
,branding text
|
|
,accs_ps text
|
|
,acc_list text
|
|
,suffix text
|
|
,uomp text
|
|
,avgsc numeric
|
|
,avgfc numeric
|
|
,weight numeric
|
|
,target numeric
|
|
,futmargin numeric
|
|
)
|
|
LANGUAGE plpgsql AS
|
|
$function$
|
|
|
|
BEGIN
|
|
|
|
RETURN QUERY
|
|
WITH
|
|
sel AS (
|
|
SELECT
|
|
i.stlc
|
|
,i.coltier
|
|
,i.branding
|
|
--,accs
|
|
,i.accs_ps
|
|
,i.acc_list
|
|
,i.suffix
|
|
--,COALESCE(accs_ps,accs) accs_c
|
|
--,jsonb_agg(DISTINCT r.aomult) cavitation
|
|
--,jsonb_agg(DISTINCT r.aoctme) cyclet
|
|
--,jsonb_arr_aggcd(mino) min_ord
|
|
,i.uomp
|
|
--,jsonb_agg(DISTINCT aplnt) aplnt
|
|
--,jsonb_agg(DISTINCT colc) colc
|
|
--,jsonb_agg(DISTINCT substring(item,12,case when branding = '' THEN 7 ELSE 4 end)) items
|
|
--,jsonb_agg(DISTINCT majg) majgs
|
|
--,jsonb_agg(DISTINCT assc) accs
|
|
,round(avg(curstdus),5) avgsc
|
|
,round(avg(futstdus),5) avgfc
|
|
,round(avg(nwht),5) avgwt
|
|
,i.v1ds
|
|
FROM
|
|
"CMS.CUSLG".itemm i
|
|
WHERE
|
|
true
|
|
--stlc ~ 'TWA10200'
|
|
--AND branding = ''
|
|
AND aplnt <> 'I'
|
|
AND COALESCE(i.uomp,'') <> ''
|
|
--AND branding = ''
|
|
GROUP BY
|
|
i.stlc
|
|
,i.coltier
|
|
,i.uomp
|
|
,i.branding
|
|
,i.acc_list
|
|
,i.accs_ps
|
|
,i.suffix
|
|
,i.v1ds
|
|
ORDER BY
|
|
i.stlc ASC
|
|
)
|
|
SELECT
|
|
sel.stlc
|
|
,sel.coltier
|
|
,sel.branding
|
|
,sel.accs_ps
|
|
,sel.acc_list
|
|
,sel.suffix
|
|
,sel.uomp
|
|
,sel.avgsc
|
|
,sel.avgfc
|
|
,sel.avgwt
|
|
,t.price
|
|
,round(CASE WHEN coalesce(t.price,0) <> 0 THEN (t.price-COALESCE(sel.avgfc,0))/t.price ELSE 0 END,3) futmarg
|
|
FROM
|
|
sel
|
|
LEFT OUTER JOIN pricequote.target_prices t ON
|
|
sel.stlc = t.stlc
|
|
AND sel.v1ds = t.ds
|
|
AND t.chan = 'DIR'
|
|
AND COALESCE(t.tier,'') IN ('1')
|
|
AND 24 <@ t.vol
|
|
-- LEFT OUTER JOIN pricequote.market_setavgprice t ON
|
|
-- t.mold = sel.stlc
|
|
-- AND t.data_segment = sel.v1ds
|
|
-- AND t.season = 2025
|
|
-- AND t.chan = 'DIRECT'
|
|
-- AND t.country = 'ALL'
|
|
-- AND t.geo = 'ALL'
|
|
-- AND t.region = 'ALL'
|
|
WHERE
|
|
sel.stlc IN (SELECT DISTINCT m.stlc FROM rlarp.molds m WHERE m.priceg ~ _priceg AND m.majg ~ _majg);
|
|
|
|
END
|
|
|
|
$function$
|
|
|
|
|