33 lines
1.4 KiB
SQL
33 lines
1.4 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_book_dimension AS
|
|
WITH bookvalue AS (
|
|
SELECT ad.id AS "dimensionId",
|
|
adv.id AS "valueId",
|
|
ad.code AS "dimensionCode",
|
|
adv.code AS "valueCode",
|
|
adv.name AS "valueName"
|
|
FROM (analytic_dimension ad
|
|
JOIN analytic_dimension_value adv ON ((ad.id = adv.dimension)))
|
|
WHERE ((1 = 1) AND ((ad.code)::text = 'book'::text))
|
|
)
|
|
SELECT DISTINCT aa.purchase AS "intTradeId",
|
|
pl.id AS "intTradeLineId",
|
|
'Physical'::text AS "strTradeCategory",
|
|
'Purchase'::text AS "strTradeType",
|
|
bv."valueCode" AS "strValueCode",
|
|
bv."valueName" AS "strValueName"
|
|
FROM ((analytic_dimension_assignment aa
|
|
JOIN bookvalue bv ON (((aa.dimension = bv."dimensionId") AND (aa.value = bv."valueId"))))
|
|
JOIN purchase_line pl ON ((aa.purchase = pl.purchase)))
|
|
WHERE (COALESCE(aa.purchase, 0) > 0)
|
|
UNION ALL
|
|
SELECT DISTINCT aa.sale AS "intTradeId",
|
|
sl.id AS "intTradeLineId",
|
|
'Physical'::text AS "strTradeCategory",
|
|
'Sale'::text AS "strTradeType",
|
|
bv."valueCode" AS "strValueCode",
|
|
bv."valueName" AS "strValueName"
|
|
FROM ((analytic_dimension_assignment aa
|
|
JOIN bookvalue bv ON (((aa.dimension = bv."dimensionId") AND (aa.value = bv."valueId"))))
|
|
JOIN sale_line sl ON ((sl.sale = aa.sale)))
|
|
WHERE (COALESCE(aa.sale, 0) > 0);;
|