CREATE OR REPLACE FUNCTION public.r_report_raw_data_sales_order (in int8, in int8, in varchar, in varchar) 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(vOuId)).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;

    --RAISE NOTICE ''vFilterOu : %'',vFilterOu;
    --RAISE NOTICE ''vFilterCustomer : %'',vFilterCustomer;
    --RAISE NOTICE ''vFilterSalesman : %'',vFilterSalesman;
    --RAISE NOTICE ''vGolonganProductCodeName : %'',vGolonganProductCodeName;
    --RAISE NOTICE ''vFilterGolonganProduct : %'',vFilterGolonganProduct;

    Open pRefHeader FOR
        SELECT 29 AS _COUNT,
        'KODEAREA', 'NAMAAREA', 'KODESALES', 'NAMASALES',
        'KOTA', 'NOSPB', 'TGLSPB', 'BULAN',
        'STATUSDO',
        'JENIS', 'NAMAJENIS','KODELANG','NAMALANG',
        'ALAMAT',
        'KODEGOL','GOL',
        'KODEPROD','KODEHARGA', 'NAMAPROD',
        'KATEGORI','SUBKATEGORI', 'PROMO',
        '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,
               f_concat_partner_address_official(C.partner_id) AS address,
               f_get_code_golongan_product(B.product_id) as gol_code, f_get_name_golongan_product(B.product_id) as gol_name,
               L.product_code as product_code, E.price_level, L.product_name as product_name,
               f_get_ctgr_product_name(L.ctgr_product_id) as category, f_get_sub_ctgr_product_name(L.sub_ctgr_product_id) as sub_category, coalesce(K.promo_desc,'''') as promo_desc,
               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_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
        INNER JOIN m_product L ON L.product_id = B.product_id
        LEFT JOIN m_promo_sales K ON K.promo_sales_id = D.promo_sales_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'
GO