Function Report Buku Besar

CREATE OR REPLACE FUNCTION r_buku_besar(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefDetailBukuBesar     REFCURSOR := 'refDetailBukuBesar';
    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;
    pCoaId                  ALIAS FOR $9;
    pPeriodFrom             ALIAS FOR $10;
    pPeriodTo               ALIAS FOR $11;

    vEmptyId                bigint := -99;
    vEmptyString            character varying := '';
    vEmptyAmount            numeric := 0;
    vStatusDocDraft         character varying := 'D';
    vStatusDocRelease       character varying := 'R';
    vSignDebt               character varying := 'D';
    vSignCredit             character varying := 'C';
    vTypeRateCom            character varying := 'COM';
    vCurrGL                 character varying(5);
    vFlgDataDetail          character varying := 'BBBB';
    vFlgDataSaldo           character varying := 'AAAA';
    vCoaDescSaldoAwal       character varying := 'SALDO AWAL';

    vFilterBranch           text := '';
    vFilterSubBu            text := '';
    vFilterBeginingBranch           text := '';
    vFilterBeginingSubBu            text := '';
    vRoundingDigit          integer;

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

    DELETE FROM tr_gl_beginning_balance_buku_besar WHERE session_id = pSessionId;
    DELETE FROM tr_gl_detail_buku_besar WHERE session_id = pSessionId;
    DELETE FROM tr_gl_data_journal_trx_for_buku_besar WHERE session_id = pSessionId;

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

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

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

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

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

    /*
    RAISE NOTICE 'vFilterSubBu: %, vFilterBranch: %, pSessionId: %, pTenantId: %, pOuBuId: %, pCoaId: %, pPeriodFrom: %, pPeriodTo: %, vFlgDataDetail: %, vStatusDocDraft: %, vTypeRateCom: %, vCurrGL: %, vRoundingDigit: %',
                  vFilterSubBu,    vFilterBranch,      pSessionId,    pTenantId,    pOuBuId,   pCoaId,    pPeriodFrom,    pPeriodTo,    vFlgDataDetail,    vStatusDocDraft,     vTypeRateCom,   vCurrGL,   vRoundingDigit;
    */

    /*
     * ambil saldo awal untuk buku besar
     */
    PERFORM gl_get_buku_besar_beginning_balance(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pOuBuId, pOuBranchId, pOuSubBuId, pCoaId, pPeriodFrom);


    -- Ambil daftar journal_trx_id dan data COA sesuai filter (exclude filter ou branch dan ou sub bu)
    INSERT INTO tr_gl_data_journal_trx_for_buku_besar(
            session_id, tenant_id, journal_trx_id, ou_bu_id,
            coa_id, coa_name, coa_desc, sign_coa, ou_branch_id, ou_sub_bu_id)
    SELECT pSessionId, A.tenant_id, B.journal_trx_id, B.ou_bu_id,
        A.coa_id, C.main_acc||'-'||C.sub_acc AS coa_name, C.coa_desc, C.sign_coa,
        CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_branch_id ELSE A.ou_branch_id END,
        CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END
    FROM gl_journal_trx_item A
    INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
    INNER JOIN m_coa C ON A.coa_id = C.coa_id
    INNER JOIN dt_date D ON B.doc_date = D.string_date
    WHERE A.tenant_id = pTenantId
        AND B.ou_bu_id = pOuBuId
        AND A.coa_id = pCoaId
        AND D.year_month_date BETWEEN pPeriodFrom AND pPeriodTo
    UNION
    SELECT pSessionId, A.tenant_id, B.journal_trx_id, B.ou_bu_id,
        A.coa_id, C.main_acc||'-'||C.sub_acc AS coa_name, C.coa_desc, C.sign_coa,
        CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_branch_id ELSE A.ou_branch_id END,
        CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END
    FROM gl_journal_trx_mapping A
    INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
    INNER JOIN m_coa C ON A.coa_id = C.coa_id
    INNER JOIN dt_date D ON B.doc_date = D.string_date
    WHERE A.tenant_id = pTenantId
        AND B.ou_bu_id = pOuBuId
        AND A.coa_id = pCoaId
        AND D.year_month_date BETWEEN pPeriodFrom AND pPeriodTo
    UNION
    SELECT pSessionId, A.tenant_id, B.journal_trx_id, B.ou_bu_id,
        A.coa_id, C.main_acc||'-'||C.sub_acc AS coa_name, C.coa_desc, C.sign_coa,
        CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_branch_id ELSE A.ou_branch_id END,
        CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END
    FROM gl_journal_trx_fx A
    INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
    INNER JOIN m_coa C ON A.coa_id = C.coa_id
    INNER JOIN dt_date D ON B.doc_date = D.string_date
    WHERE A.tenant_id = pTenantId
        AND B.ou_bu_id = pOuBuId
        AND A.coa_id = pCoaId
        AND D.year_month_date BETWEEN pPeriodFrom AND pPeriodTo
    ORDER BY journal_trx_id;

    /*
     * Ambil data detail untuk buku besar yang status doc nya R
     */
    EXECUTE '
    INSERT INTO tr_gl_detail_buku_besar(
            session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
            ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id, doc_type_desc,
            doc_id, doc_no, doc_date, remark, coa_journal_id, coa_journal_desc, sign_journal,
            curr_code_trx, amount_trx, numerator_rate, denominator_rate, gl_curr_code,
            gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
            coa_journal_ou_branch_id,
            coa_journal_branch_name,
            coa_journal_ou_sub_bu_id,
            coa_journal_sub_bu_name )
    WITH gl_journal_trx_details AS (
        -- Ambil details journal dimana coa nya bukan coa filter

        SELECT B.tenant_id, D.ou_bu_id,
            D.ou_branch_id,D.ou_sub_bu_id,
            D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
            B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
            C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
            A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
        FROM gl_journal_trx_item A
        INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
        INNER JOIN m_coa C ON A.coa_id = C.coa_id
        WHERE A.tenant_id = $2
            AND A.coa_id <> D.coa_id
            AND B.status_doc = $4

        UNION ALL

        SELECT B.tenant_id, B.ou_bu_id,
            D.ou_branch_id, D.ou_sub_bu_id,
            D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
            B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
            C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
            A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
        FROM gl_journal_trx_mapping A
        INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
        INNER JOIN m_coa C ON A.coa_id = C.coa_id
        WHERE A.tenant_id = $2
            AND A.coa_id <> D.coa_id
            AND B.status_doc = $4

        UNION ALL

        SELECT B.tenant_id, B.ou_bu_id,
            D.ou_branch_id, D.ou_sub_bu_id,
            D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
            B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
            C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
            A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
        FROM gl_journal_trx_fx A
        INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
        INNER JOIN m_coa C ON A.coa_id = C.coa_id
        WHERE A.tenant_id = $2
            AND A.coa_id <> D.coa_id
            AND B.status_doc = $4

    )
    SELECT $1 AS session_id, A.tenant_id, A.ou_bu_id, f_get_ou_name(A.ou_bu_id) AS ou_bu_name,
            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.coa_id, A.coa_desc, A.sign_coa, A.doc_type_id, A.doc_type_desc,
            A.doc_id, A.doc_no, A.doc_date, A.remark, A.coa_journal_id, A.coa_journal_desc, A.sign_journal,
            A.curr_code_trx, A.amount_trx, A.numerator_rate, A.denominator_rate, A.gl_curr_code,
            A.gl_amount, $3 AS flg_data, A.status_doc, A.coa_name, A.coa_journal_name,
            A.coa_journal_ou_branch_id,
            f_get_ou_name(A.coa_journal_ou_branch_id) AS coa_journal_branch_name,
            A.coa_journal_ou_sub_bu_id,
            f_get_ou_name(A.coa_journal_ou_branch_id) AS coa_journal_sub_bu_name
    FROM gl_journal_trx_details A
    WHERE A.tenant_id = $2 '||
        vFilterBranch ||
        vFilterSubBu || '
    ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.coa_journal_id, A.doc_date, A.doc_no
    '
    USING pSessionId, pTenantId, vFlgDataDetail, vStatusDocRelease, vEmptyId;

    /*
     * Ambil data detail untuk buku besar yang status doc nya D
     */
    EXECUTE '
    INSERT INTO tr_gl_detail_buku_besar(
            session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
            ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id, doc_type_desc,
            doc_id, doc_no, doc_date, remark, coa_journal_id, coa_journal_desc, sign_journal,
            curr_code_trx, amount_trx, numerator_rate, denominator_rate, gl_curr_code,
            gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
            coa_journal_ou_branch_id,
            coa_journal_branch_name,
            coa_journal_ou_sub_bu_id,
            coa_journal_sub_bu_name )

    WITH gl_journal_trx_details AS (
        -- Ambil details journal dimana coa nya bukan coa filter

        SELECT B.tenant_id, B.ou_bu_id,
            D.ou_branch_id, D.ou_sub_bu_id,
            D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
            B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
            C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
            A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc, A.type_rate, A.journal_date,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id ,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
        FROM gl_journal_trx_item A
        INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
        INNER JOIN m_coa C ON A.coa_id = C.coa_id
        WHERE A.tenant_id = $2
            AND A.coa_id <> D.coa_id
            AND B.status_doc = $4

        UNION ALL

        SELECT B.tenant_id, B.ou_bu_id,
            D.ou_branch_id, D.ou_sub_bu_id,
            D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
            B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
            C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
            A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc, A.type_rate, A.journal_date,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id ,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
        FROM gl_journal_trx_mapping A
        INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
        INNER JOIN m_coa C ON A.coa_id = C.coa_id
        WHERE A.tenant_id = $2
            AND A.coa_id <> D.coa_id
            AND B.status_doc = $4

        UNION ALL

        SELECT B.tenant_id, B.ou_bu_id,
            D.ou_branch_id, D.ou_sub_bu_id,
            D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
            B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
            C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
            A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc, $6 AS type_rate, A.journal_date,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id ,
            ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
        FROM gl_journal_trx_fx A
        INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
        INNER JOIN m_coa C ON A.coa_id = C.coa_id
        WHERE A.tenant_id = $2
            AND A.coa_id <> D.coa_id
            AND B.status_doc = $4

        ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id, coa_journal_id, doc_date, doc_no

    ), tt_gl_admin_journal_rate AS (
        SELECT A.type_rate, A.journal_date, A.curr_code_trx, $7 AS gl_curr_code, f_get_exchange_rate($2, A.type_rate, A.journal_date, A.curr_code_trx, $7) AS data_exchange_rate
        FROM (
            SELECT A.journal_date, A.type_rate, A.curr_code_trx
            FROM gl_journal_trx_details A
            GROUP BY journal_date, type_rate, curr_code_trx
        ) A
    )
        SELECT $1 AS session_id, A.tenant_id, A.ou_bu_id, f_get_ou_name(A.ou_bu_id) AS ou_bu_name,
            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.coa_id, A.coa_desc, A.sign_coa, A.doc_type_id, A.doc_type_desc,
            A.doc_id, A.doc_no, A.doc_date, A.remark, A.coa_journal_id, A.coa_journal_desc, A.sign_journal,
            A.curr_code_trx, A.amount_trx, A.numerator_rate, A.denominator_rate, CASE WHEN (A.gl_curr_code is null OR TRIM(A.gl_curr_code) = '''') THEN $7 ELSE A.gl_curr_code END gl_curr_code,
            ROUND(A.amount_trx * (B.data_exchange_rate).numerator_rate / (B.data_exchange_rate).denominator_rate, $8) AS gl_amount,
            $3 AS flg_data, A.status_doc, A.coa_name, A.coa_journal_name,
            A.coa_journal_ou_branch_id,
            f_get_ou_name(A.coa_journal_ou_branch_id) AS coa_journal_branch_name,
            A.coa_journal_ou_sub_bu_id,
            f_get_ou_name(A.coa_journal_ou_sub_bu_id) AS coa_journal_sub_bu_name
        FROM gl_journal_trx_details A
        INNER JOIN tt_gl_admin_journal_rate B ON A.type_rate = B.type_rate AND A.journal_date = B.journal_date AND A.curr_code_trx = B.curr_code_trx
        WHERE A.tenant_id = $2 '||
            vFilterBranch ||
            vFilterSubBu || '
        ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.coa_journal_id, A.doc_date, A.doc_no
    '
    USING pSessionId, pTenantId, vFlgDataDetail, vStatusDocDraft, vEmptyId, vTypeRateCom, vCurrGL, vRoundingDigit;

    /*
     * Insert data ke table temporary, untuk transaksi yang nggak ada saldo nya (flg_data = AAAA <-- untuk data saldo)
     *
     * perubahan 2017-12-05, saldo awal hanya muncul 1 kali diatas, perhitungan saldo berdasarkan ou bu dan coa yang dipilih. Kondisi sekarang ou bu dan coa harus dipilih
     * Membuatkan data saldo jika saldo awal 0
     */
    INSERT INTO tr_gl_detail_buku_besar(
            session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
            ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id,
            doc_type_desc, doc_id, doc_no, doc_date, remark,
            coa_journal_id, coa_journal_desc, sign_journal,
            curr_code_trx, amount_trx, gl_curr_code,
            gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
            coa_journal_ou_branch_id,
            coa_journal_branch_name,
            coa_journal_ou_sub_bu_id,
            coa_journal_sub_bu_name )

    SELECT pSessionId AS session_id, pTenantId AS tenant_id, pOuBuId AS ou_bu_id, f_get_ou_name(pOuBuId) AS ou_bu_name,
            vEmptyId AS ou_branch_id, vEmptyString AS branch_name, vEmptyId AS ou_sub_bu_id, vEmptyString AS sub_bu_name,
            A.coa_id, vCoaDescSaldoAwal AS coa_desc_saldo_awal, A.sign_coa, vEmptyId AS doc_type_id,
            vEmptyString AS doc_type_desc, vEmptyId AS doc_id, vEmptyString AS doc_no, vEmptyString AS doc_date,
            vEmptyString AS remark, vEmptyId AS coa_journal_id, vEmptyString AS coa_journal_desc, A.sign_coa AS sign_journal,
            vEmptyString AS curr_code_trx, vEmptyAmount AS amount_trx, vEmptyString AS gl_curr_code,
            vEmptyAmount AS gl_amount, vFlgDataSaldo AS flg_data, vEmptyString AS status_doc, A.main_acc||'-'||A.sub_acc AS coa_name, vEmptyString AS coa_journal_name,
            vEmptyId,
            vEmptyString,
            vEmptyId,
            vEmptyString
    FROM m_coa A
    WHERE A.coa_id = pCoaId
        AND NOT EXISTS (SELECT 1 FROM tr_gl_beginning_balance_buku_besar B
                      WHERE B.session_id = pSessionId
                            AND B.tenant_id = pTenantId
                            AND B.ou_bu_id = pOuBuId
                            AND B.coa_id = A.coa_id);


    -- Ambil data beginning balance untuk di masukan ke table temporary
    EXECUTE '
        INSERT INTO tr_gl_detail_buku_besar(
                session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
                ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id, doc_type_desc,
                doc_id, doc_no, doc_date, remark, coa_journal_id, coa_journal_desc, sign_journal,
                curr_code_trx, amount_trx, gl_curr_code,
                gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
                coa_journal_ou_branch_id,
                coa_journal_branch_name,
                coa_journal_ou_sub_bu_id,
                coa_journal_sub_bu_name )
        SELECT $1 AS session_id, A.tenant_id, A.ou_bu_id, f_get_ou_name(A.ou_bu_id) AS ou_bu_name,
                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.coa_id, $6 AS coa_desc_saldo_awal, B.sign_coa, $7 AS doc_type_id, $8 AS doc_type_desc,
                $7 AS doc_id, $8 AS doc_no, $8 AS doc_date, $8 AS remark, $7 AS coa_journal_id, $8 AS coa_journal_desc, B.sign_coa AS sign_journal,
                $8 AS curr_code_trx, $9 AS amount_trx, $8 AS gl_curr_code,
                A.beginning_balance AS gl_amount, $5 AS flg_data, $8 AS status_doc,
                B.main_acc||''-''||B.sub_acc AS coa_name, $8 AS coa_journal_name,
                $7,
                $8,
                $7,
                $8
        FROM tr_gl_beginning_balance_buku_besar A
        INNER JOIN m_coa B ON A.coa_id = B.coa_id
        WHERE A.session_id = $1
                AND A.tenant_id = $2
                AND A.ou_bu_id = $3
                AND A.coa_id = $4 '||
                vFilterBeginingBranch ||
                vFilterBeginingSubBu || '
        ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id
    '
    USING pSessionId, pTenantId, pOuBuId, pCoaId, vFlgDataSaldo, vCoaDescSaldoAwal, vEmptyId, vEmptyString, vEmptyAmount;

    /* header result*/

    Open pRefDetailBukuBesar FOR
    WITH data_details_buku_besar AS (
        SELECT  A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
                A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
                A.curr_code_trx, CAST(SUM(A.amount_trx) AS CHARACTER VARYING(50)) AS amount_trx, A.gl_curr_code, SUM(A.gl_amount) AS gl_amount,
                (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,
                CASE WHEN A.sign_journal = vSignDebt THEN CAST(SUM(A.gl_amount) AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_credit,
                CASE WHEN A.sign_journal = vSignCredit THEN CAST(SUM(A.gl_amount) AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_debt,
                CASE WHEN A.sign_journal = A.sign_coa THEN (SUM(A.gl_amount) * -1) ELSE SUM(A.gl_amount) END AS balance_amount,
                A.flg_data, A.numerator_rate, A.denominator_rate, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
                A.coa_id, A.coa_journal_id,
                A.coa_journal_branch_name,
                A.coa_journal_sub_bu_name
        FROM tr_gl_detail_buku_besar A
        WHERE A.session_id = pSessionId
            AND A.tenant_id = pTenantId
            AND A.flg_data = vFlgDataDetail
        GROUP BY A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
                A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
                A.curr_code_trx, A.gl_curr_code, A.flg_data, A.numerator_rate, A.denominator_rate, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
                A.coa_id, A.coa_journal_id,A.doc_no,A.coa_journal_branch_name,A.coa_journal_sub_bu_name
        UNION ALL

        SELECT  A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
                A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
                A.curr_code_trx, CAST(A.amount_trx AS CHARACTER VARYING(50)) AS amount_trx, A.gl_curr_code, A.gl_amount,
                vEmptyString AS rate,
                CASE WHEN A.sign_coa = vSignCredit THEN CAST(A.gl_amount AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_credit,
                CASE WHEN A.sign_coa = vSignDebt THEN CAST(A.gl_amount AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_debt,
                A.gl_amount AS balance_amount,
                A.flg_data, A.numerator_rate, A.denominator_rate, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
                A.coa_id, A.coa_journal_id,
                A.coa_journal_branch_name,
                A.coa_journal_sub_bu_name
        FROM tr_gl_detail_buku_besar A
        WHERE A.session_id = pSessionId
            AND A.tenant_id = pTenantId
            AND A.flg_data = vFlgDataSaldo
        ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id, flg_data, doc_date, doc_no, coa_journal_id, sign_journal
        --ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id, flg_data, coa_journal_id, doc_date, doc_no
    )
    SELECT  A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
            A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
            A.curr_code_trx, A.amount_trx, A.gl_curr_code, A.gl_amount, A.rate, A.gl_amount_credit, A.gl_amount_debt,
            CAST(SUM(A.balance_amount) OVER (PARTITION BY A.ou_bu_id ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.flg_data,A.doc_date, A.doc_no, A.coa_journal_id) AS CHARACTER VARYING(50)) AS balance_amount,
            A.flg_data, A.numerator_rate, A.denominator_rate, A.coa_journal_branch_name,A.coa_journal_sub_bu_name
    FROM data_details_buku_besar A
    ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.flg_data, A.doc_date, A.doc_no, A.coa_journal_id ;

    RETURN NEXT pRefDetailBukuBesar ;


    DELETE FROM tr_gl_beginning_balance_buku_besar WHERE session_id = pSessionId;
    DELETE FROM tr_gl_detail_buku_besar WHERE session_id = pSessionId;
    DELETE FROM tr_gl_data_journal_trx_for_buku_besar WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /