r_report_inquiry_sales_outlet_csv

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

    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pGroupOutletId          ALIAS FOR $3;
    pOutletId               ALIAS FOR $4;
    pPeriodStart            ALIAS FOR $5;
    pPeriodEnd              ALIAS FOR $6;
    pFlgVerified            ALIAS FOR $7;
    pFlgUnused              ALIAS FOR $8;

    vVerified               CHARACTER VARYING := 'Verified';
    vNotVerified            CHARACTER VARYING := 'Not Verified';
    vInProgressVerified     CHARACTER VARYING := 'In Progress';
    vYes                    CHARACTER VARYING := 'Y';
    vNo                     CHARACTER VARYING := 'N';
    vInProgress             CHARACTER VARYING := 'I';
    vUse                    CHARACTER VARYING := 'Dipakai';
    vUnUsed                 CHARACTER VARYING := 'Tidak Dipakai';
    vFine                   CHARACTER VARYING := 'Denda';
    vNotFine                CHARACTER VARYING := 'Tidak Denda';
    vEmptyValue             CHARACTER VARYING := '';
    vFilterVerified         CHARACTER VARYING := '';
    vFilterStatus           CHARACTER VARYING := '';
    vFilterOutlet           CHARACTER VARYING := '';
    vEmptyId                BIGINT := -99;

BEGIN

    IF (pFlgVerified <> vEmptyValue) THEN
        vFilterVerified := ' AND A.status_item = '''|| pFlgVerified || ''' ';
    END IF;

    IF (pFlgUnused <> vEmptyValue) THEN
        vFilterStatus := ' AND A.flg_unused = '''|| pFlgUnused || ''' ';
    END IF;

    IF (pOutletId <> vEmptyId) THEN
        vFilterOutlet := ' AND D.outlet_id = '|| pOutletId || '';
    END IF;

    OPEN pRefHeader FOR
    SELECT 18 AS _COUNT,
            'Dept Store', 'Outlet', 'Doc No',
            'Doc Date', 'Promo', 'Status',
            'Verified', 'Artikel', 'Harga Gross',
            'Harga Nett System', 'Harga Nett Diinput', 'Harga Nett Koreksi',
            'Qty Diinput', 'Qty Koreksi', 'Denda SPG',
            'Nilai Denda', 'Status', 'Sub Total',
            1 AS ordial;
    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR
        EXECUTE '
            WITH TEMP AS (
                    SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
                        A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
                        A.nett_sell_price_verified * A.qty_verified AS sub_total, C.flg_denda, COALESCE(D.fine_amount,0) AS denda,
                        A.outlet_id, A.promo_code_verified, A.status_item
                    FROM i_trx_mobile_pos_item_balance A
                    INNER JOIN m_product B  ON A.product_id = B.product_id
                    INNER JOIN i_verification_trx_mobile_pos_item C  ON A.trx_mobile_pos_item_id = C.ref_item_id
                    LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id
                    UNION
                    SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
                        A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
                        A.nett_sell_price_verified * A.qty_verified AS sub_total, C.flg_denda, COALESCE(D.fine_amount,0) AS denda,
                        A.outlet_id, A.promo_code_verified, A.status_item
                    FROM i_trx_mobile_pos_item_balance A
                    INNER JOIN m_product B  ON A.product_id = B.product_id
                    INNER JOIN i_verification_trx_mobile_pos_item C  ON A.trx_mobile_pos_item_id = C.verification_trx_item_id
                        AND C.ref_item_id = -99  LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id
                    UNION
                    SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
                        A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
                        A.nett_sell_price_verified * A.qty_verified AS sub_total, $9 AS flg_denda, 0 AS denda,
                        A.outlet_id, A.promo_code_verified, A.status_item
                    FROM i_trx_mobile_pos_item_balance A
                    INNER JOIN m_product B  ON A.product_id = B.product_id
                    WHERE NOT EXISTS(
                        SELECT 1  FROM i_verification_trx_mobile_pos_item C
                        LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id
                        WHERE A.trx_mobile_pos_item_id = C.verification_trx_item_id
                        AND C.ref_item_id = -99 ))
                SELECT 18 AS _COUNT,C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
                    CASE WHEN A.status_item = $8 THEN $5
                        WHEN A.status_item = $9 THEN $6
                        WHEN A.status_item = $10 THEN $7 END status_item,
                    CASE WHEN A.flg_unused = $9 THEN $11 ELSE $12 END flg_unused,
                    A.product_code, A.gross_sell_price,
                    A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified,
                    CASE WHEN A.flg_denda = $8 THEN $13 ELSE $14 END flg_denda, A.denda, CASE WHEN A.flg_unused = $9 THEN $11 ELSE $12 END AS flg_unused2,
                    A.sub_total
                FROM TEMP A
                INNER JOIN m_outlet_group B ON A.outlet_id = B.outlet_id
                INNER JOIN m_group_outlet C ON B.group_outlet_id = C.group_outlet_id
                INNER JOIN i_outlet D ON A.outlet_id = D.outlet_id
                WHERE  C.group_outlet_id = $1
                       AND A.doc_date BETWEEN $3 AND $4'
                       || vFilterVerified  || vFilterStatus || vFilterOutlet || '
                ORDER BY A.doc_no, A.doc_date ASC'
        USING pGroupOutletId, pOutletId, pPeriodStart, pPeriodEnd, vVerified, vNotVerified, vInProgressVerified,
                vYes, vNo, vInProgress, vUse, vUnUsed, vFine, vNotFine;
    RETURN NEXT pRefDetail;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000
/