CREATE OR REPLACE FUNCTION r_sales_csv(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;

    vDocTypeIdDo                    BIGINT := 311;
    vDocTypeIdRn                    BIGINT := 502;
    vEmptyId                        BIGINT := -99;
    vNo                             CHARACTER VARYING := 'N';
    vYes                            CHARACTER VARYING := 'Y';
    vPromoEvent                     CHARACTER VARYING := 'PROMOEVENT';
    vEmptyString                    CHARACTER VARYING := '';

    vOuId                           BIGINT;
    vFlgIncludeAllBranch            CHARACTER VARYING;
    vYearMonth                      CHARACTER VARYING;
    vSalesmanCodeName               CHARACTER VARYING;
    vCustomerCodeName               CHARACTER VARYING;
    vCtgrProduct                    CHARACTER VARYING;
    vSubCtgrProduct                 CHARACTER VARYING;

    vFilterOU                       CHARACTER VARYING :='';
    vFilterSalesmanCodeName         CHARACTER VARYING :='';
    vFilterCustomerCodeName         CHARACTER VARYING :='';
    vFilterCtgrProduct              CHARACTER VARYING :='';
    vFilterSubCtgrProduct           CHARACTER VARYING :='';

BEGIN

    DELETE FROM tt_report_sales WHERE session_id = pSessionId;

    /**
     * Mendapatkan parameter yang dikirim dari UI
     */
    vOuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouId') AS BIGINT);
    vFlgIncludeAllBranch := f_get_report_parameter_value_by_id(pReportMessageId, 'flgIncludeAllBranch');
    vYearMonth := f_get_report_parameter_value_by_id(pReportMessageId, 'yearMonth');
    vSalesmanCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'salesmanCodeName');
    vCustomerCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'customerCodeName');
    vCtgrProduct := f_get_report_parameter_value_by_id(pReportMessageId, 'ctgrProduct');
    vSubCtgrProduct := f_get_report_parameter_value_by_id(pReportMessageId, 'subCtgrProduct');

    /**
     * Menbuat filter
     */
    IF (vFlgIncludeAllBranch!=vEmptyString AND vFlgIncludeAllBranch=vYes) THEN
        vFilterOU := ' AND AY.ou_bu_id = '||vOuId;
    ELSE
        vFilterOU := ' AND A.ou_id = '||vOuId;
    END IF;

    IF (vSalesmanCodeName!=vEmptyString) THEN
        vFilterSalesmanCodeName := ' AND (UPPER(f_get_partner_code(A.salesman_id)) LIKE UPPER(''%'||vSalesmanCodeName||'%'') OR UPPER(f_get_partner_name(A.salesman_id)) LIKE UPPER(''%'||vSalesmanCodeName||'%'')) ';
    END IF;
    IF (vCustomerCodeName!=vEmptyString) THEN
        vFilterCustomerCodeName := ' AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER(''%'||vCustomerCodeName||'%'') OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER(''%'||vCustomerCodeName||'%'')) ';
    END IF;
    IF (vCtgrProduct!=vEmptyString) THEN
        vFilterCtgrProduct := ' AND UPPER(f_get_ctgr_product_name(B.ctgr_product_id)) LIKE UPPER(''%'||vCtgrProduct||'%'') ';
    END IF;
    IF (vSubCtgrProduct!=vEmptyString) THEN
        vSubCtgrProduct := ' AND UPPER(f_get_sub_ctgr_product_name(B.sub_ctgr_product_id)) LIKE UPPER(''%'||vSubCtgrProduct||'%'') ';
    END IF;

    /**
     * HEADER CSV
     */
    Open pRefHeader FOR
        SELECT 15 AS _COUNT,'Year Month', 'OU', 'Customer code', 'Customer name', 'Salesman code', 'Salesman name', 'Promo code', 'Category product', 'Sub category product', 'Product code', 'Product name', 'Qty', 'Currency', 'Amount', 'Discount amount', 'Nett amount',1 AS ordinal
        ORDER BY ordinal;
    RETURN NEXT pRefHeader;

    /**
     * PREPARE DATA
     */

    -- DATA PENJUALAN YANG SUDAH DO/DOR/INVOICE
    EXECUTE '
        INSERT INTO tt_report_sales(
            session_id, ou_id, partner_id, salesman_id, promo_code, product_id,
            qty, curr_code, amount,
            discount_amount,
            nett_amount)

        SELECT $1, A.ou_id, A.partner_id, B.salesman_id, COALESCE(E.promo_code, $2) AS promo_code, D.product_id,
            A.qty_dlv_so, A.curr_code, A.item_amount,
            (A.regular_disc_amount + A.promo_disc_amount + A.adj_regular_disc_amount + A.adj_promo_disc_amount) AS discount_amount,
            A.item_amount - (A.regular_disc_amount + A.promo_disc_amount + A.adj_regular_disc_amount + A.adj_promo_disc_amount) AS nett_amount
        FROM sl_so_balance_invoice A
        INNER JOIN dt_date AX ON A.ref_doc_date = AX.string_date
        INNER JOIN m_ou_structure AY ON A.ou_id = AY.ou_id
        INNER JOIN sl_so B ON A.so_id = B.so_id
        INNER JOIN sl_so_additional_for_dlg C ON B.so_id = C.so_id
        INNER JOIN sl_do_item D ON A.ref_item_id = D.do_item_id
        LEFT JOIN m_promo_sales E ON C.promo_sales_id = E.promo_sales_id
        WHERE A.ref_doc_type_id = $3 '||
        vFilterOU || '
        AND AX.year_month_date = $4 '
    USING pSessionId, vEmptyString, vDocTypeIdDo, vYearMonth;

    -- DATA PENJUALAN DENGAN RETURN NOTE
    EXECUTE '
        INSERT INTO tt_report_sales(
            session_id, ou_id, partner_id, salesman_id, promo_code, product_id,
            qty, curr_code, amount,
            discount_amount, nett_amount)

        SELECT $1, A.ou_id, A.partner_id, COALESCE(E.salesman_id, $2) AS salesman_id, COALESCE(G.promo_code, $3) AS promo_code, B.product_id,
            (-1*A.qty_dlv_so) qty_dlv_so, A.curr_code, (-1*A.item_amount) item_amount,
            0 discount_amount, (-1*A.item_amount) AS nett_amount
        FROM sl_so_balance_invoice A
        INNER JOIN dt_date AX ON A.ref_doc_date = AX.string_date
        INNER JOIN m_ou_structure AY ON A.ou_id = AY.ou_id
        INNER JOIN sl_request_return_sales_item B ON A.ref_item_id = B.request_return_sales_item_id
        LEFT JOIN sl_do_item C ON B.ref_id = C.do_item_id
        LEFT JOIN sl_do D ON C.do_id = D.do_id
        LEFT JOIN sl_so E ON D.ref_id = E.so_id
        LEFT JOIN sl_so_additional_for_dlg F ON E.so_id = F.so_id
        LEFT JOIN m_promo_sales G ON F.promo_sales_id = G.promo_sales_id
        WHERE A.ref_doc_type_id = $4 '||
        vFilterOU || '
        AND AX.year_month_date = $5 '
    USING pSessionId, vEmptyId, vEmptyString, vDocTypeIdRn, vYearMonth;

    Open pRefDetail FOR

        EXECUTE '
        SELECT $1 AS year_month, f_get_ou_name(A.ou_id) AS ou, f_get_partner_code(A.partner_id) AS customer_code,
                f_get_partner_name(A.partner_id) AS customer_name, f_get_partner_code(A.salesman_id) AS salesman_code,
                f_get_partner_name(A.salesman_id) AS salesman_name, A.promo_code, f_get_ctgr_product_name(B.ctgr_product_id) AS category_product,
                f_get_sub_ctgr_product_name(B.sub_ctgr_product_id) AS sub_category_product, B.product_code, B.product_name,
                ROUND(SUM(A.qty), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS qty, A.curr_code,
                ROUND(SUM(A.amount), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS amount,
                ROUND(SUM(A.discount_amount), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS discount_amount,
                ROUND(SUM(A.nett_amount), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS nett_amount
        FROM tt_report_sales A
        INNER JOIN m_product B ON A.product_id = B.product_id
        WHERE A.session_id = $3 '||
        vFilterSalesmanCodeName ||
        vFilterCustomerCodeName ||
        vFilterCtgrProduct ||
        vFilterSubCtgrProduct ||'
        GROUP BY A.ou_id, A.partner_id, A.salesman_id, A.promo_code, A.curr_code, B.ctgr_product_id, B.sub_ctgr_product_id, B.product_code, B.product_name '
        USING vYearMonth, vDocTypeIdDo, pSessionId;

    RETURN NEXT pRefDetail;

    DELETE FROM tt_report_sales WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;