46 lines
1.4 KiB
SQL
46 lines
1.4 KiB
SQL
DROP VIEW IF EXISTS rlarp.plm CASCADE;
|
|
|
|
CREATE OR REPLACE VIEW rlarp.plm AS
|
|
SELECT
|
|
p.japlcd AS plcode
|
|
,substr(p.japld1, 1, 1) AS func
|
|
,substr(p.japld1, 10, 1) AS basis
|
|
,substr(p.japld1, 3, 2) AS tier
|
|
,substr(p.japld2, 1, 3) AS currency
|
|
--substr(p.japld2, 5, 3) AS des_currency,
|
|
--round(substr(p.japld2, 9, 7)::numeric(16,5) / 100000::numeric,5) AS fx,
|
|
--substr(p.japld2, 9, 7) fx,
|
|
,substr(p.japld1, 6, 3) AS country
|
|
,substr(p.japld1, 12, 5) AS parent
|
|
,min(i.jbfdat) AS fdat
|
|
,max(i.jbtdat) AS tdat
|
|
--jsonb_agg(DISTINCT c.bvcurr) AS curr,
|
|
--jsonb_agg(DISTINCT c.bvctry) AS ctry,
|
|
--jsonb_agg(DISTINCT bvname) cust
|
|
,p.JAPLDS D1
|
|
,p.JAPLD1 D2
|
|
,p.JAPLD2 D3
|
|
FROM
|
|
lgdat.iprca p
|
|
LEFT OUTER JOIN "CMS.CUSLG".iprcbhc i ON
|
|
p.japlcd = i.jbplcd
|
|
WHERE
|
|
true
|
|
--include any price list that is flagged with R,C,P in position 10 of descr line 2
|
|
--or simply any price list that is considered currently active in IPRCBHC
|
|
--substr(p.japld1, 10, 1) = ANY (ARRAY['R'::text, 'C'::text, 'P'::text])
|
|
--OR daterange('2019-06-01'::date, '2021-06-01'::date) && daterange(i.jbfdat, i.jbtdat)
|
|
GROUP BY
|
|
p.japlcd
|
|
,(substr(p.japld1, 1, 1))
|
|
,(substr(p.japld1, 3, 2))
|
|
,(substr(p.japld2, 5, 3))
|
|
,(substr(p.japld1, 6, 3))
|
|
,(substr(p.japld1, 10, 1))
|
|
,(substr(p.japld1, 12, 5))
|
|
,p.JAPLDS
|
|
,p.JAPLD1
|
|
,p.JAPLD2;
|
|
|
|
GRANT SELECT ON rlarp.plm TO public;
|