-- Function: r_cb_inquiry_payment_order(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying, character varying)

-- DROP FUNCTION r_cb_inquiry_payment_order(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION r_cb_inquiry_payment_order(character varying, bigint, bigint, bigint, character varying, bigint, character varying, 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;
    pUserId                 ALIAS FOR $3;
    pRoleId                 ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;
    pOuId                   ALIAS FOR $6;
    pPartnerCodeName        ALIAS FOR $7;
    pCurrCode               ALIAS FOR $8;
    pDocDateFrom            ALIAS FOR $9;
    pDocDateTo              ALIAS FOR $10;
    pDocNo                  ALIAS FOR $11;

    vEmptyId                bigint := -99;
    vEmpty                  text := '';
    vDocTypeId              bigint := 601;
    vFilterCurrCode         text := '';
    vFilterPartnerCodeName  text := '';
    vNone                   character varying := '-';
    vFilterDocNo            text := '';
    vFilterOu               text := '';
BEGIN
    IF (pCurrCode <> vEmpty) THEN
        vFilterCurrCode := ' AND UPPER(A.curr_code) LIKE UPPER(''%' || pCurrCode || '%'') ';
    END IF;

    IF (pPartnerCodeName <> vEmpty) THEN
        vFilterPartnerCodeName := ' AND (UPPER(C.partner_code) LIKE UPPER(''%' || pPartnerCodeName || '%'') OR
            UPPER(C.partner_name) LIKE UPPER(''%' || pPartnerCodeName || '%'') )';
    END IF;

    IF (pDocNo <> vEmpty) THEN
        vFilterDocNo := ' AND UPPER(A.payment_doc_no) LIKE UPPER(''%' || pDocNo || '%'')';
    END IF;

    IF (pOuId <> vEmptyId) THEN
        vFilterOu := ' AND B.ou_bu_id = ' || pOuId;
    END IF;

    Open pRefHeader FOR
    SELECT CASE WHEN pPartnerCodeName = vEmpty THEN 'All' ELSE pPartnerCodeName END AS partner_code_name,
        CASE WHEN pCurrCode = vEmpty THEN 'All' ELSE pCurrCode END AS curr_code,
        f_get_username(pUserId) AS user_name, f_get_role_name(pRoleId) AS role_name,
        pDocDateFrom AS doc_date_from, pDocDateTo AS doc_date_to, pDatetime AS print_datetime,
        CASE WHEN pOuId = vEmptyId THEN 'All' ELSE f_get_ou_code(pOuId) || ' / ' || f_get_ou_name(pOuId) END AS ou_name,
        pOuId AS ou_id;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    EXECUTE '
        SELECT A.ou_id, A.payment_id, A.payment_doc_no, A.payment_doc_date, f_get_partner_code(A.partner_id) AS partner_code,
            f_get_partner_name(A.partner_id) AS partner_name, A.payment_remark, A.curr_code, A.amount,
            f_get_ou_name(A.ou_id) AS ou_name, A.due_date, A.flg_payment, A.ref_doc_type_id, A.ref_id,
            COALESCE(D.doc_no, '''|| vNone ||''') AS cb_out_doc_no, COALESCE(D.doc_date,'''|| vNone ||''') AS cb_out_doc_date,
            E.status_doc, f_get_ou_code(A.ou_id) AS ou_code, G.ou_type_id
        FROM cb_trx_cashbank_balance A
        INNER JOIN m_ou_structure B ON A.ou_id = B.ou_id
        INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
        LEFT OUTER JOIN cb_in_out_cashbank D ON A.tenant_id = D.tenant_id AND A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.in_out_cashbank_id
        INNER JOIN cb_payment_order E ON A.tenant_id = E.tenant_id AND A.ou_id = E.ou_id AND A.payment_id = E.payment_order_id
        INNER JOIN t_ou G ON A.ou_id = G.ou_id
        INNER JOIN t_ou_type F ON G.ou_type_id = F.ou_type_id
        WHERE A.tenant_id = $1
        AND A.doc_type_id = $2
        AND A.payment_doc_date BETWEEN $3 AND $4
        '|| vFilterOu
         || vFilterPartnerCodeName
         || vFilterCurrCode
         || vFilterDocNo
         ||' ORDER BY F.flg_bu DESC, F.flg_sub_bu ASC, F.flg_branch ASC, ou_code, A.payment_doc_date ' USING pTenantId, vDocTypeId, pDocDateFrom, pDocDateTo;
    RETURN NEXT pRefDetail ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_cb_inquiry_payment_order(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying, character varying)
  OWNER TO sts;