· 5 years ago · Mar 24, 2020, 11:56 PM
1drop function if exists report.fresh_pricelist_details_template_report(numeric, numeric, character varying, numeric);
2CREATE OR REPLACE FUNCTION report.fresh_pricelist_details_template_report(IN p_c_bpartner_id numeric, IN p_m_pricelist_version_id numeric, IN p_ad_language character varying,
3 IN p_c_bpartner_location_id numeric)
4 RETURNS TABLE
5 (
6 prodvalue text,
7 customerproductnumber text,
8 productcategory text,
9 productname text,
10 attributes text,
11 itemproductname text,
12 qty numeric,
13 uomsymbol text,
14 pricestd numeric,
15 m_productprice_id integer,
16 c_bpartner_id numeric,
17 m_hu_pi_item_product_id integer,
18 uom_x12de355 text,
19 c_bpartner_location_id numeric,
20 qtycuspertu numeric,
21 m_product_id integer,
22 bp_value text,
23 bp_name text,
24 reportfilename text
25 )
26
27AS
28$BODY$
29--
30
31SELECT plc.value AS prodvalue,
32 plc.customerproductnumber as customerproductnumber,
33 plc.productcategory as productcategory,
34 plc.productname as productname,
35 plc.attributes as attributes,
36 coalesce(hupiv.description, hupip.name, plc.uomsymbol) as itemproductname,
37 NULL::numeric as qty,
38 plc.uomsymbol as uomsymbol,
39 round(plc.pricestd, cur.stdprecision) as pricestd,
40 plc.M_ProductPrice_ID as m_productprice_id,
41 p_c_bpartner_id as c_bpartner_id,
42 plc.M_HU_PI_Item_Product_ID as m_hu_pi_item_product_id,
43 case when plc.m_hu_pi_item_product_id is not null then 'COLI' else plc.uom_x12de355 end as uom_x12de355,
44 p_c_bpartner_location_id as c_bpartner_location_id,
45 plc.qtycuspertu as qtycuspertu,
46 plc.m_product_id as m_product_id,
47 plc.BP_Value as bp_value,
48 plc.BP_Name as bp_name,
49 CONCAT(bp_value, '_', bp_name, '_', case when prlv.isactive = 'Y' then prlv.validfrom else null end, '.xls') as reportfilename
50
51FROM report.fresh_PriceList_Details_Report(p_c_bpartner_id, p_m_pricelist_version_id, NULL, p_ad_language) plc
52 LEFT OUTER JOIN M_HU_PI_Item_Product hupip on hupip.M_HU_PI_Item_Product_ID = plc.M_HU_PI_Item_Product_ID
53 LEFT OUTER JOIN M_HU_PI_Item hupii on hupii.M_HU_PI_Item_ID = hupip.M_HU_PI_Item_ID
54 LEFT OUTER JOIN M_HU_PI_Version hupiv on hupiv.M_HU_PI_Version_ID = hupii.M_HU_PI_Version_ID
55 LEFT OUTER JOIN M_Pricelist_Version prlv on prlv.m_pricelist_version_id = p_m_pricelist_version_id
56 LEFT OUTER JOIN M_Pricelist prl on prlv.m_pricelist_id = prl.m_pricelist_id
57 LEFT OUTER JOIN C_Currency cur on prl.c_currency_id = cur.c_currency_id
58--
59
60
61$BODY$
62 LANGUAGE sql STABLE
63 COST 100
64 ROWS 1000;