r_ledger_card_report

CREATE OR REPLACE FUNCTION r_ledger_card_report(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, character varying, 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;
    pOuBuId             ALIAS FOR $6;
    pOuBranchId         ALIAS FOR $7;
    pOuSubBuId          ALIAS FOR $8;
    pGroupCoaId         ALIAS FOR $9;
    pOuRc               ALIAS FOR $10;
    pSegment            ALIAS FOR $11;
    pPeriodFrom         ALIAS FOR $12;
    pPeriodTo           ALIAS FOR $13;
    pMainAcc            ALIAS FOR $14;
    pSubAcc             ALIAS FOR $15;

    vEmptyId            bigint := -99;
    vEmptyString        character varying := '';
    vEmptyAmount        numeric := 0;
    vYes                character varying := 'Y';
    vStatusDocTrx       character varying := '';
    vStatusDocDraft     character varying := 'D';
    vStatusDocRelease   character varying := 'R';
    vSignDebt           character varying := 'D';
    vSignCredit         character varying := 'C';
    vTypeRateCom        character varying := 'COM';
    vCurrGL             character varying(5);
    vJoinOuRc           text := '';
    vJoinSegment        text := '';
    vFilterBranch       text := '';
    vFilterSubBu        text := '';
    vFilterGroupCoa     text := '';
    vFilterOuRc         text := '';
    vFilterSegment      text := '';
    vFilterCoaMainAcc   text := '';
    vFilterCoaSubAcc    text := '';
    vRoundingDigit      integer;
    vTypeSaldoAwal      character varying := 'SALDO AWAL';

BEGIN
    vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');

    DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
    DELETE FROM tr_gl_detail_ledger_card WHERE session_id = pSessionId;

    /* ambil nilai pembulatan*/
    vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);

    /* vJoinOuRc, jika ouRc diisi */
    IF (pOuRc <> vEmptyString) THEN
        vJoinOuRc := '  INNER JOIN t_ou K ON B.ou_rc_id = K.ou_id
                        INNER JOIN t_ou_type L ON K.ou_type_id = L.ou_type_id AND L.flg_accounting = '''|| vYes || '''';
    END IF;

    /* vJoinSegment, jika segment diisi*/
    IF (pSegment <> vEmptyString) THEN
        vJoinSegment := ' INNER JOIN m_segment_coa M ON B.segmen_id = M.segment_coa_id ';
    END IF;

    /* branchId <> -99*/
    IF (pOuBranchId <> vEmptyId) THEN
        vFilterBranch := ' AND B.ou_branch_id = ' || pOuBranchId;
    END IF;

    /* subBuId <> -99*/
    IF (pOuSubBuId <> vEmptyId) THEN
        vFilterSubBu := ' AND B.ou_sub_bu_id =  ' || pOuSubBuId;
    END IF;

    /* gorupCoaId <> -99*/
    IF (pGroupCoaId <> vEmptyId) THEN
        vFilterGroupCoa := ' AND E.group_coa_id = ' || pGroupCoaId;
    END IF;

    /* jika ouRc diisi*/
    IF (pOuRc <> vEmptyString) THEN
        vFilterOuRc := ' AND UPPER(K.ou_name) LIKE UPPER(''%' ||  pOuRc || '%'')';
    END IF;

    /* jika segment diisi*/
    IF (pSegment <> vEmptyString) THEN
        vFilterSegment := ' AND UPPER(M.segment_coa_name) LIKE UPPER(''%' || pSegment || '%'')';
    END IF;

    /* jika main acc diisi*/
    IF (pMainAcc <> vEmptyString) THEN
        vFilterCoaMainAcc := ' AND E.main_acc LIKE ''' || pMainAcc || '%''';
    END IF;

    /* jika sub acc diisi*/
    IF (pSubAcc <> vEmptyString) THEN
        vFilterCoaSubAcc := ' AND E.sub_acc LIKE ''' || pSubAcc || '%''';
    END IF;

    /*
     * ambil saldo awal ledger card
     */
    PERFORM gl_get_ledger_card_beginning_balance(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pOuBuId, pOuBranchId, pOuSubBuId, pGroupCoaId, pOuRc, pSegment, pPeriodFrom, pMainAcc, pSubAcc);

    /* ambil data detail yang, status doc = R
     * 1) Ada transaksi tapi tidak ada saldo
     * 2) Ada transaksi dan ada saldo awal
     */
    EXECUTE '
    INSERT INTO tr_gl_detail_ledger_card(
            session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
            doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
            journal_date, numerator_rate, denominator_rate, gl_curr_code,
            gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
            ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
            sub_acc, beginning_balance, sign_coa, coa_desc, remark, order_num)
    WITH gl_journal_trx_details AS (
        SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
              a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
              a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark,
              CASE WHEN a.ou_branch_id = $7 AND a.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE a.ou_branch_id END AS ou_branch_id,
              CASE WHEN a.ou_branch_id = $7 AND a.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE a.ou_sub_bu_id END AS ou_sub_bu_id
        FROM gl_journal_trx_item a
        INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
        INNER JOIN dt_date p ON x.doc_date = p.string_date
        WHERE a.tenant_id = $2
        AND p.year_month_date BETWEEN $3 AND $4
          UNION ALL
        SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
              b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
              b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark,
              CASE WHEN b.ou_branch_id = $7 AND b.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE b.ou_branch_id END AS ou_branch_id,
              CASE WHEN b.ou_branch_id = $7 AND b.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE b.ou_sub_bu_id END AS ou_sub_bu_id
        FROM gl_journal_trx_mapping b
        INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id
        INNER JOIN dt_date p ON x.doc_date = p.string_date
        WHERE b.tenant_id = $2
        AND p.year_month_date BETWEEN $3 AND $4
          UNION ALL
        SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
              c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
              c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark,
              CASE WHEN c.ou_branch_id = $7 AND c.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE c.ou_branch_id END AS ou_branch_id,
              CASE WHEN c.ou_branch_id = $7 AND c.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE c.ou_sub_bu_id END AS ou_sub_bu_id
        FROM gl_journal_trx_fx c
        INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
        INNER JOIN dt_date p ON x.doc_date = p.string_date
        WHERE c.tenant_id = $2
        AND p.year_month_date BETWEEN $3 AND $4
    )
    SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
            A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
            B.journal_date, B.numerator_rate, B.denominator_rate, B.gl_curr_code,
            B.gl_amount, B.ou_branch_id, f_get_ou_name(B.ou_branch_id) AS branch_name, B.ou_sub_bu_id,f_get_ou_name(B.ou_sub_bu_id) AS sub_bu_name,
            B.ou_rc_id, f_get_ou_name(B.ou_rc_id) AS rc_name, B.segmen_id, f_get_segment_name(B.segmen_id) AS segment_name,
            B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
            CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark, 2
    FROM gl_journal_trx A
    INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
    INNER JOIN m_coa E ON B.coa_id = E.coa_id' ||
    vJoinOuRc ||
    vJoinSegment ||
    ' LEFT OUTER JOIN tr_gl_beginning_balance_ledger_card C ON A.ou_bu_id = C.ou_bu_id AND A.ou_branch_id = C.ou_branch_id
        AND A.ou_sub_bu_id = C.ou_sub_bu_id AND B.coa_id = C.coa_id AND B.ou_rc_id = C.ou_rc_id AND B.segmen_id = C.segment_id
        AND C.session_id = $1
    WHERE A.tenant_id = $2
        AND A.status_doc = $5
        AND A.ou_bu_id = $6' ||
        vFilterBranch ||
        vFilterSubBu ||
        vFilterGroupCoa ||
        vFilterOuRc ||
        vFilterSegment ||
        vFilterCoaMainAcc ||
        vFilterCoaSubAcc ||
    ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, pPeriodFrom, pPeriodTo, vStatusDocRelease, pOuBuId, vEmptyId;

    /* ambil data detail, status doc = D */
    EXECUTE '
    INSERT INTO tr_gl_detail_ledger_card(
            session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
            doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
            journal_date, numerator_rate, denominator_rate, gl_curr_code,
            gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
            ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
            sub_acc, beginning_balance, sign_coa, coa_desc, remark, order_num)
    WITH gl_journal_trx_details AS (
        SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
              a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
              a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark, a.type_rate,
              CASE WHEN a.ou_branch_id = $10 AND a.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE a.ou_branch_id END AS ou_branch_id,
              CASE WHEN a.ou_branch_id = $10 AND a.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE a.ou_sub_bu_id END AS ou_sub_bu_id
        FROM gl_journal_trx_item a
        INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
        INNER JOIN dt_date p ON x.doc_date = p.string_date
        WHERE a.tenant_id = $2
        AND p.year_month_date BETWEEN $3 AND $4
          UNION ALL
        SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
              b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
              b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark, b.type_rate,
              CASE WHEN b.ou_branch_id = $10 AND b.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE b.ou_branch_id END AS ou_branch_id,
              CASE WHEN b.ou_branch_id = $10 AND b.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE b.ou_sub_bu_id END AS ou_sub_bu_id
        FROM gl_journal_trx_mapping b
        INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id
        INNER JOIN dt_date p ON x.doc_date = p.string_date
        WHERE b.tenant_id = $2
        AND p.year_month_date BETWEEN $3 AND $4
          UNION ALL
        SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
              c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
              c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark, $8,
              CASE WHEN c.ou_branch_id = $10 AND c.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE c.ou_branch_id END AS ou_branch_id,
              CASE WHEN c.ou_branch_id = $10 AND c.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE c.ou_sub_bu_id END AS ou_sub_bu_id
        FROM gl_journal_trx_fx c
        INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
        INNER JOIN dt_date p ON x.doc_date = p.string_date
        WHERE c.tenant_id = $2
        AND p.year_month_date BETWEEN $3 AND $4
    ), tt_gl_admin_journal_rate AS (
        SELECT A.type_rate, A.journal_date, A.curr_code, $9, f_get_exchange_rate($2, A.type_rate, A.journal_date, A.curr_code, $9) AS data_exchange_rate
        FROM (
            SELECT A.journal_date, A.type_rate, A.curr_code
            FROM gl_journal_trx_details A
            GROUP BY journal_date, type_rate, curr_code
        ) A
    )
    SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
            A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
            B.journal_date, (D.data_exchange_rate).numerator_rate AS numerator_rate, (D.data_exchange_rate).denominator_rate AS denominator_rate, CASE WHEN (B.gl_curr_code is null OR TRIM(B.gl_curr_code) = '''') THEN $9 ELSE B.gl_curr_code END gl_curr_code,
            ROUND(B.amount * (D.data_exchange_rate).numerator_rate / (D.data_exchange_rate).denominator_rate, $7) AS gl_amount,
            B.ou_branch_id, f_get_ou_name(B.ou_branch_id) AS branch_name, B.ou_sub_bu_id, f_get_ou_name(B.ou_sub_bu_id) AS sub_bu_name,
            B.ou_rc_id, f_get_ou_name(B.ou_rc_id) AS rc_name, B.segmen_id, f_get_segment_name(B.segmen_id) AS segment_name,
            B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
            CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark, 2
    FROM gl_journal_trx A
    INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
    INNER JOIN m_coa E ON B.coa_id = E.coa_id
    INNER JOIN tt_gl_admin_journal_rate D ON D.type_rate=B.type_rate AND B.journal_date=D.journal_date AND D.curr_code=B.curr_code'||
    vJoinOuRc ||
    vJoinSegment ||
    ' LEFT OUTER JOIN tr_gl_beginning_balance_ledger_card C ON A.ou_bu_id = C.ou_bu_id AND A.ou_branch_id = C.ou_branch_id
        AND A.ou_sub_bu_id = C.ou_sub_bu_id AND B.coa_id = C.coa_id AND B.ou_rc_id = C.ou_rc_id AND B.segmen_id = C.segment_id
        AND C.session_id = $1
    WHERE A.tenant_id = $2
        AND A.status_doc = $5
        AND A.ou_bu_id = $6' ||
        vFilterBranch ||
        vFilterSubBu ||
        vFilterGroupCoa ||
        vFilterOuRc ||
        vFilterSegment ||
        vFilterCoaMainAcc ||
        vFilterCoaSubAcc ||
    ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, pPeriodFrom, pPeriodTo, vStatusDocDraft, pOuBuId, vRoundingDigit, vTypeRateCom, vCurrGL, vEmptyId;


    /* ambil data detail yang tidak ada transaksi tapi ada saldo */
    INSERT INTO tr_gl_detail_ledger_card(
            session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
            doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
            journal_date, gl_curr_code,
            gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
            ou_rc_id, rc_name, segment_id, segment_name,
            coa_id, main_acc, sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
    SELECT pSessionId, pTenantId, A.ou_bu_id, vEmptyId, vEmptyString,
            vEmptyId, vEmptyString, vEmptyString, L.sign_coa, vEmptyString, vEmptyAmount,
            vEmptyString, vEmptyString,
            vEmptyAmount, A.ou_branch_id, f_get_ou_name(A.ou_branch_id) AS branch_name, A.ou_sub_bu_id, f_get_ou_name(A.ou_sub_bu_id) AS sub_bu_name,
            A.ou_rc_id, f_get_ou_name(A.ou_rc_id) AS rc_name, A.segment_id, f_get_segment_name(A.segment_id) AS segment_name,
            A.coa_id, L.main_acc, L.sub_acc, COALESCE(A.beginning_balance, 0) AS beginning_balance, L.sign_coa, L.coa_desc, 1, vEmptyString
    FROM tr_gl_beginning_balance_ledger_card A
    INNER JOIN m_coa L ON A.coa_id = L.coa_id
    WHERE NOT EXISTS (SELECT 1
                       FROM tr_gl_detail_ledger_card B
                       WHERE A.ou_bu_id = B.ou_bu_id
                            AND A.ou_branch_id = B.ou_branch_id
                            AND A.ou_sub_bu_id = B.ou_sub_bu_id
                            AND A.coa_id = B.coa_id
                            AND A.ou_rc_id = B.ou_rc_id
                            AND A.segment_id = B.segment_id
                            AND A.session_id = B.session_id)
    AND A.tenant_id = pTenantId
    AND A.session_id = pSessionId;

    /*
    /* Insert data beginning balance yang ada transaksinya ambil dari table tr_gl_detail_ledger_card yang order_num= 2 */
    INSERT INTO tr_gl_detail_ledger_card(
        session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
        doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
        journal_date, gl_curr_code,
        gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
        ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
        sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
    WITH data_saldo_awal_with_transaksi AS (
        SELECT A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
            A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
            A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
        FROM tr_gl_detail_ledger_card A
        WHERE A.session_id = pSessionId
        AND A.tenant_id = pTenantId
        AND A.order_num = 2
        GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
            A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
            A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
    )
    SELECT pSessionId, A.tenant_id, A.ou_bu_id, vEmptyId, vTypeSaldoAwal,
        vEmptyId, vEmptyString, vEmptyString, A.sign_coa, vEmptyString, vEmptyAmount,
        vEmptyString, vEmptyString,
        A.beginning_balance, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
        A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
        A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc, 1, vEmptyString
    FROM data_saldo_awal_with_transaksi A
    ORDER BY A.coa_id;
    */

    /* header result*/
    Open pRefHeader FOR
    SELECT pOuBuId AS ou_id, f_get_ou_name(pOuBuId) AS ou_name, pPeriodFrom AS period_from, pPeriodTo AS period_to, f_get_username(pUserId) AS username, pDatetime AS datetime;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT A.doc_type_id, A.doc_type_desc, A.doc_no, A.doc_date, A.sign_journal, A.curr_code_trx, A.amount_trx AS amount_trx, A.journal_date,
            A.numerator_rate, A.denominator_rate, A.sign_coa, A.gl_curr_code, A.gl_amount AS gl_amount, A.ou_branch_id, A.branch_name,
            A.ou_sub_bu_id, A.sub_bu_name, A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id,
            A.main_acc, A.sub_acc, A.beginning_balance, A.coa_desc, A.remark,
            CASE WHEN A.sign_journal = vSignDebt THEN A.gl_amount ELSE 0 END AS gl_amount_debt,
            CASE WHEN A.sign_journal = vSignCredit THEN A.gl_amount ELSE 0 END AS gl_amount_credit,
            (A.curr_code_trx || to_char(A.denominator_rate, '999,999.90') || ' = ' || A.gl_curr_code || to_char(A.numerator_rate, '999,999.90')) AS rate, A.order_num
    FROM tr_gl_detail_ledger_card A
    WHERE A.session_id = pSessionId
        AND A.tenant_id = pTenantId
    --ORDER BY A.branch_name, A.sub_bu_name, A.main_acc, A.sub_acc, A.rc_name, A.segment_name, A.coa_desc, A.doc_date, A.doc_no, A.curr_code_trx, A.gl_curr_code;
    ORDER BY A.branch_name, A.sub_bu_name, A.coa_id, A.rc_name, A.segment_name, A.order_num, A.doc_date, A.doc_type_desc, A.doc_no ;

    RETURN NEXT pRefDetail ;

    DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
    DELETE FROM tr_gl_detail_ledger_card WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/