ts

CREATE OR REPLACE FUNCTION r_report_inquiry_sales_outlet_csv(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT)
  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;
    pRoleId                 ALIAS FOR $9;

    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 := '';
    vFilterShowHpp          CHARACTER VARYING := '';
    vFilterShowHppSubTotal  CHARACTER VARYING := '';
    vFilterGroup            CHARACTER VARYING := '';
    vShowHpp                CHARACTER VARYING;
    vEmptyId                BIGINT := -99;
    vCount                  BIGINT:= 0;
    vOuGrazia               BIGINT;

BEGIN

    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'OU.GRAZIA.INDONESIA') AS BIGINT) INTO vOuGrazia;

    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;

    SELECT role_name INTO vShowHpp
    FROM t_role A
    INNER JOIN (
        SELECT unnest(string_to_array(f_get_value_system_config_by_param_code(pTenantId, 'authorized.role.id'), ';')) AS authorized_id
    ) B
    ON A.role_id = CAST(B.authorized_id AS bigint)
    WHERE role_id = pRoleId;

    IF vShowHpp IS NOT NULL THEN
        vFilterShowHpp := ', COALESCE(E.purch_price, 0) AS hpp';
        vFilterShowHppSubTotal := ', SUM(A.qty_verified* COALESCE(E.purch_price, 0)) AS sub_total_hpp';
        vFilterGroup           := 'GROUP BY C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
                               A.status_item, A.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, A.flg_denda, A.sub_total, a.denda, e.purch_price';
        vCount         := 20;

        OPEN pRefHeader FOR
            SELECT 20 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', 'HPP', 'Sub Total Hpp',
                    1 AS ordial;
        RETURN NEXT pRefHeader;
    ELSE
        vCount         := 18;
        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;
    END IF;

    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, B.product_id
                    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, B.product_id
                    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, B.product_id
                    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 '|| vCount ||' 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, CAST(A.qty AS BIGINT) AS qty, CAST(A.qty_verified AS BIGINT) AS 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 '|| vFilterShowHpp || vFilterShowHppSubTotal || '
                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
                LEFT JOIN m_purch_price_product E ON A.product_id = E.product_id
                    AND E.ou_id = $15
                    AND D.tenant_id = E.tenant_id
                    AND A.doc_date BETWEEN E.date_from AND E.date_to
                WHERE  C.group_outlet_id = $1
                       AND A.doc_date BETWEEN $3 AND $4'
                       || vFilterVerified  || vFilterStatus || vFilterOutlet || vFilterGroup || '
                ORDER BY A.doc_no, A.doc_date ASC'
        USING pGroupOutletId, pOutletId, pPeriodStart, pPeriodEnd, vVerified, vNotVerified, vInProgressVerified,
                vYes, vNo, vInProgress, vUse, vUnUsed, vFine, vNotFine, vOuGrazia;
    RETURN NEXT pRefDetail;


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