62 lines
2.2 KiB
SQL
62 lines
2.2 KiB
SQL
-- View: public.vw_utility_product
|
|
|
|
-- DROP VIEW public.vw_utility_product;
|
|
|
|
CREATE OR REPLACE VIEW public.vw_utility_product AS
|
|
SELECT 0 AS "intProductId",
|
|
''::character varying AS "strProductCode",
|
|
''::text AS "strProductDescription",
|
|
true AS "ysnActive",
|
|
''::character varying AS "strTemplate",
|
|
''::character varying AS "strTemplateCode",
|
|
''::character varying AS "strDefaultUom",
|
|
0 AS "dblDefaultUomFactor",
|
|
0 AS "dblDefaultUomRate",
|
|
0 AS "dblDefaultUomRounding",
|
|
''::character varying(10) AS "strDefaultUomSymbol",
|
|
true AS "ysnAvailableOnPurchase",
|
|
''::character varying AS "strPurchaseUom",
|
|
0 AS "dblPurchaseUomFactor",
|
|
0 AS "dblPurchaseUomRate",
|
|
0 AS "dblPurchaseUomRounding",
|
|
''::character varying(10) AS "strPurchaseUomSymbol",
|
|
true AS "ysnAvailableOnSale",
|
|
''::character varying AS "strSaleUom",
|
|
0 AS "dblSaleUomFactor",
|
|
0 AS "dblSaleUomRate",
|
|
0 AS "dblSaleUomRounding",
|
|
''::character varying(10) AS "strSaleUomSymbol"
|
|
UNION ALL
|
|
SELECT p.id AS "intProductId",
|
|
p.code AS "strProductCode",
|
|
COALESCE(p.description, ''::text) AS "strProductDescription",
|
|
p.active AS "ysnActive",
|
|
pt.name AS "strTemplate",
|
|
pt.type AS "strTemplateCode",
|
|
pu1.name AS "strDefaultUom",
|
|
pu1.factor AS "dblDefaultUomFactor",
|
|
pu1.rate AS "dblDefaultUomRate",
|
|
pu1.rounding AS "dblDefaultUomRounding",
|
|
pu1.symbol AS "strDefaultUomSymbol",
|
|
pt.purchasable AS "ysnAvailableOnPurchase",
|
|
pu2.name AS "strPurchaseUom",
|
|
pu2.factor AS "dblPurchaseUomFactor",
|
|
pu2.rate AS "dblPurchaseUomRate",
|
|
pu2.rounding AS "dblPurchaseUomRounding",
|
|
pu2.symbol AS "strPurchaseUomSymbol",
|
|
pt.salable AS "ysnAvailableOnSale",
|
|
pu3.name AS "strSaleUom",
|
|
pu3.factor AS "dblSaleUomFactor",
|
|
pu3.rate AS "dblSaleUomRate",
|
|
pu3.rounding AS "dblSaleUomRounding",
|
|
pu3.symbol AS "strSaleUomSymbol"
|
|
FROM product_product p
|
|
JOIN product_template pt ON p.template = pt.id
|
|
JOIN product_uom pu1 ON pt.default_uom = pu1.id
|
|
JOIN product_uom pu2 ON pt.purchase_uom = pu2.id
|
|
JOIN product_uom pu3 ON pt.sale_uom = pu3.id
|
|
WHERE 1 = 1;
|
|
|
|
ALTER TABLE public.vw_utility_product
|
|
OWNER TO postgres;
|