CREATE OR REPLACE FUNCTION r_sell_price_product_for_so(BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';

    pTenantId           ALIAS FOR $1;
    pReportMessageId    ALIAS FOR $2;
    pSessionId          ALIAS FOR $3;
    pProcessNo          ALIAS FOR $4;

    vOuId                       bigint;
    vDateFrom                   CHARACTER varying;
    vDateTo                     CHARACTER varying;
    vCustomerCodeName           CHARACTER varying;
    vSalesmanCodeName           CHARACTER varying;
    vGolonganProductCodeName    CHARACTER varying;

    vEmptyString                CHARACTER varying;
    vOuBuId                     bigint;
    vYes                        CHARACTER varying := 'Y';
    vVoid                       CHARACTER varying := 'V';
    vIndustryTypeComboId        CHARACTER varying := 'INDUSTRYTYPE';
    vGRoupBrandComboId          CHARACTER varying := 'GROUPBRAND';
    vFilterOu                   CHARACTER varying;
    vFilterCustomer             CHARACTER varying;
    vFilterSalesman             CHARACTER varying;
    vFilterGolonganProduct      CHARACTER varying;

BEGIN
    vOuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouId') AS bigint);
    vDateFrom := f_get_report_parameter_value_by_id(pReportMessageId, 'dateFrom');
    vDateTo := f_get_report_parameter_value_by_id(pReportMessageId, 'dateTo');
    vCustomerCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'customerCodeName');
    vSalesmanCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'salesmanCodeName');
    vGolonganProductCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'golonganProductCodeName');

    vEmptyString :='';
    vFilterOu := '';
    vFilterCustomer := '';
    vFilterSalesman := '';
    vFilterGolonganProduct := '';

    SELECT (f_get_ou_bu_structure(pOuId)).ou_bu_id INTO vOuBuId;
    --filter ou
    IF(vOuId <> vOuBuId) THEN
        vFilterOu := ' AND C.ou_id = '|| vOuId;
    END IF;

    --filter customer
    IF(vCustomerCodeName <> vEmptyString) THEN
        vFilterCustomer := ' AND (UPPER(E.partner_code) LIKE UPPER('''||'%'||vCustomerCodeName||'%'||''') OR UPPER(E.partner_name) LIKE UPPER('''||'%'||vCustomerCodeName||'%'||''')) ';
    END IF;

    --filter salesman
    IF(vSalesmanCodeName <> vEmptyString) THEN
        vFilterSalesman := ' AND (UPPER(f_get_partner_code(C.salesman_id)) LIKE UPPER('''||'%'||vSalesmanCodeName||'%'||''') OR UPPER(f_get_partner_name(C.salesman_id)) LIKE UPPER('''||'%'||vSalesmanCodeName||'%'||''')) ';
    END IF;

    --filter golongan
    IF(vGolonganProductCodeName <> vEmptyString) THEN
        vFilterGolonganProduct :=' AND (UPPER(f_get_code_golongan_product(B.product_id)) LIKE UPPER('''||'%'||vGolonganProductCodeName||'%'||''') OR UPPER(f_get_name_golongan_product(B.product_id)) LIKE UPPER('''||'%'||vGolonganProductCodeName||'%'||''')) ';
    END IF;

    OPEN pRefHeader FOR
        SELECT 26 AS _COUNT,
        'KODEAREA', 'NAMAAREA', 'KODESALES', 'NAMASALES',
        'KOTA', 'NOSPB', 'TGLSPB', 'BULAN',
        'STATUSDO',
        'JENIS', 'NAMAJENIS','KODELANG','NAMALANG',
        'ALAMAT',
        'KODEGOL','GOL',
        'KODEPROD','KODEHARGA', 'NAMAPROD',
        'HARGA',
        'JUMSPB',
        'NILAISPB',
        'AREACOVER','SUPPLIER',
        'JENISBRG','TAHUN',1 AS ordinal
        order BY ordinal;
    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR
        EXECUTE
        'SELECT f_get_ou_code(C.ou_id) as ou_code, f_get_ou_name(C.ou_id) as ou_name, f_get_partner_code(C.salesman_id) as salesman_code, f_get_partner_name(C.salesman_id) as salesman_name,
               f_get_city_name_official(c.partner_id) as city, C.doc_no, c.doc_date, SUBSTRING(c.doc_date,5,2) as month,
               CASE WHEN EXISTS( SELECT 1
                                 FROM sl_so_balance_invoice K
                                 WHERE K.so_id = C.so_id    ) THEN 1 ELSE 0 END as status_do,
               E.industry_type, f_get_combo_prop_key($1,E.industry_type) as desc_industry, E.partner_code, E.partner_name,
               CONCAT((CASE WHEN F.address1 <> $2 THEN F.address1 ELSE $2 END),
                      (CASE WHEN F.address2 <> $2 THEN CONCAT((CASE WHEN F.address1 <> $2 THEN ', ' ELSE $2 END), F.address2) ELSE $2 END),
                      (CASE WHEN F.address3 <> $2 THEN CONCAT((CASE WHEN F.address1 <> $2 OR F.address2 <> $2 THEN ', ' ELSE $2 END), F.address3) ELSE $2 END)) AS address,
               f_get_code_golongan_product(B.product_id) as gol_code, f_get_name_golongan_product(B.product_id) as gol_name,
               f_get_product_code(B.product_id) as product_code, E.price_level, f_get_product_name(B.product_id) as product_name,
               CASE WHEN flg_tax_amount =$3 THEN B.gross_sell_price ELSE B.gross_sell_price + B.tax_price END as harga_spb,
               A.qty_so + A.qty_add - A.qty_cancel as qty,
               (A.qty_so + A.qty_add - A.qty_cancel) * (CASE WHEN flg_tax_amount =$3 THEN B.gross_sell_price ELSE B.gross_sell_price + B.tax_price END) as nilai_spb,
               I.region_name, f_get_partner_name(G.supplier_id) as supplier_name,
               H.group_brand_product as group_brand, SUBSTRING(c.doc_date,1,4) as year
        FROM sl_so_balance_item A
        INNER JOIN sl_so_item B ON B.so_item_id = A.so_item_id
        INNER JOIN sl_so C ON C.so_id = B.so_id
        INNER JOIN sl_so_additional_for_dlg D ON D.so_id = C.so_id
        INNER JOIN m_partner E ON E.partner_id = C.partner_id
        INNER JOIN m_partner_address F ON F.partner_id = C.partner_id AND F.flg_official = $3
        INNER JOIN m_product_custom_for_dlg G ON G.product_id = B.product_id
        INNER JOIN m_product_group_brand H ON H.product_id = B.product_id
        INNER JOIN m_region I ON I.region_id = D.region_id
        INNER JOIN m_ou_structure J ON J.ou_id = C.ou_id
        WHERE A.status_item <> $5 AND
              A.qty_so + A.qty_add - A.qty_cancel > 0 AND
              A.tenant_id = $6  AND
              C.doc_date BETWEEN $7 AND $8 AND
              J.ou_bu_id = $9 '||
              vFilterOu ||
              vFilterCustomer ||
              vFilterSalesman ||
              vFilterGolonganProduct ||
        ' ORDER BY ou_code, c.doc_date, C.doc_no '
        USING  vIndustryTypeComboId, vEmptyString, vYes, vGRoupBrandComboId, vVoid, pTenantId,
               vDateFrom, vDateTo, vOuBuId;
    RETURN NEXT pRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /