Untitled

CREATE OR REPLACE FUNCTION "public"."gl_process_forex_ar" (in int8, in varchar, in int8, in varchar, in varchar, in int8) RETURNS void AS
$BODY$
DECLARE

    pTenantId               ALIAS FOR $1;
    pSessionId              ALIAS FOR $2;
    pOuId                   ALIAS FOR $3;
    pYearMonth              ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;
    pUserId                 ALIAS FOR $6;

    vNextYearMonth          character varying(6);
    vNextYearMonthBeg       character varying(8);
    vYearMonthBeg           character varying(8);
    vSignDebt               character varying(1);
    vSignCredit             character varying(1);
    vCurrGL                 character varying(5);
    vTypeRateCom            character varying(5);
    vTypeRateEom            character varying(5);
    vRoundingDigit          integer;
    vSystemCOA              character varying(10);
    vJournalTrxId           bigint;
    vEmptyValue             character varying(1);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusApproved         character varying(10);
    vForexArDocTypeId       bigint;
    vForexCoaId             bigint;
    vDateEndOfMonth         character varying(8);

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;

BEGIN

    vSignDebt = 'D';
    vSignCredit = 'C';
    vTypeRateCom := 'COM';
    vTypeRateEom := 'EOM';
    vStatusRelease := 'R';
    vStatusApproved := 'APPROVED';
    vSystemCOA := 'SYSTEM';
    vEmptyId    := -99;
    vEmptyValue := ' ';
    vForexArDocTypeId := 733;

    SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
    SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month - 1 day','YYYYMMDD') INTO vDateEndOfMonth;
    SELECT pYearMonth || '01' INTO vYearMonthBeg;
    SELECT vNextYearMonth || '01' INTO vNextYearMonthBeg;

    vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
    vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
    vDocJournal := f_get_document_journal(vForexArDocTypeId);
    vOuStructure := f_get_ou_bu_structure(pOuId);
    vForexCoaId := f_get_system_coa_by_group_coa(pTenantId, 'PerkiraanBiayaSelisihKurs');

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_gl_forex_ar WHERE session_id = pSessionId;
    DELETE FROM tt_gl_forex_ar_summary_coa WHERE session_id = pSessionId;

    /*
     * Ambil saldo akhir invoice AR yang masih mempunyai sisa saldo (balance_amount - invoice_amount > 0)
     */
    INSERT INTO tt_gl_forex_ar (
        session_id, date_year_month, tenant_id, ou_id, doc_type_id, invoice_id, doc_no, doc_date,
        partner_id, coa_id, curr_code,
        amount, gl_curr_code, initial_gl_amount, eom_gl_amount, add_forex_amount)
    SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id, B.doc_no, B.doc_date,
        B.partner_id, f_get_ar_coa_partner(A.tenant_id, B.partner_id), B.curr_code,
        A.balance_amount - A.payment_amount, vCurrGL, 0, 0, 0
    FROM fi_summary_monthly_ar A, fi_invoice_ar_balance B
    WHERE A.tenant_id = pTenantId AND
        A.date_year_month = pYearMonth AND
        A.ou_id = pOuId AND
        A.balance_amount - A.payment_amount <> 0 AND
        A.doc_type_id = B.doc_type_id AND
        A.invoice_id = B.invoice_ar_balance_id AND
        B.doc_type_id NOT IN (252,254);

    /*
     * Ambil saldo akhir invoice tax AR yang masih mempunyai sisa saldo (balance_amount - invoice_amount > 0)
     */
    INSERT INTO tt_gl_forex_ar (
        session_id, date_year_month, tenant_id, ou_id, doc_type_id, invoice_id, doc_no, doc_date,
        partner_id, coa_id, curr_code,
        amount, gl_curr_code, initial_gl_amount, eom_gl_amount, add_forex_amount)
    SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id, B.tax_no, B.tax_date,
        B.partner_id, f_get_ar_coa_partner(A.tenant_id, B.partner_id), B.tax_curr_code,
        A.balance_amount - A.payment_amount, vCurrGL, 0, 0, 0
    FROM fi_summary_monthly_ar A, fi_invoice_tax_ar_balance B
    WHERE A.tenant_id = pTenantId AND
        A.date_year_month = pYearMonth AND
        A.ou_id = pOuId AND
        A.balance_amount - A.payment_amount <> 0 AND
        A.doc_type_id = B.doc_type_id AND
        A.invoice_id = B.invoice_tax_ar_balance_id AND
        B.doc_type_id <> 283;

    /*
     * Update initial_gl_amount = eom_gl_amount = amount, untuk invoice yg curr_code = curr code GL
     */
    UPDATE tt_gl_forex_ar SET initial_gl_amount = amount, eom_gl_amount = amount
    WHERE session_id = pSessionId AND curr_code = gl_curr_code;

    /*
     * Update initial_gl_amount = amount * exc rate sesuai doc_date, untuk invoice yg curr_code <> curr code GL
     */
    UPDATE tt_gl_forex_ar
    SET initial_gl_amount = ROUND(tt_gl_forex_ar.amount * B.amount_to / B.amount_from, vRoundingDigit)
    FROM m_exchange_rate B
    WHERE tt_gl_forex_ar.session_id = pSessionId AND
        tt_gl_forex_ar.curr_code <> tt_gl_forex_ar.gl_curr_code AND
        tt_gl_forex_ar.tenant_id = B.tenant_id AND
        tt_gl_forex_ar.curr_code = B.curr_code_from AND
        tt_gl_forex_ar.gl_curr_code = B.curr_code_to AND
        tt_gl_forex_ar.doc_date = B.date_from AND
        B.type_exchange_rate = vTypeRateCom;

    /*
     * Update eom_gl_amount = amount * exc rate EoM, untuk invoice yg curr_code <> curr code GL
     */
    UPDATE tt_gl_forex_ar
    SET eom_gl_amount = ROUND(tt_gl_forex_ar.amount * B.amount_to / B.amount_from, vRoundingDigit)
    FROM m_exchange_rate B
    WHERE tt_gl_forex_ar.session_id = pSessionId AND
        tt_gl_forex_ar.curr_code <> tt_gl_forex_ar.gl_curr_code AND
        tt_gl_forex_ar.tenant_id = B.tenant_id AND
        tt_gl_forex_ar.curr_code = B.curr_code_from AND
        tt_gl_forex_ar.gl_curr_code = B.curr_code_to AND
        B.date_to = vDateEndOfMonth AND
        B.type_exchange_rate = vTypeRateEom;

    /*
     * Hitung jumlah eom_gl_amount berdasarkan coa
     */
    INSERT INTO tt_gl_forex_ar_summary_coa
        (session_id, coa_id, total_eom_gl_amount, total_forex_eom_gl_amount, trial_balance_amount, diff_amount)
    SELECT A.session_id, A.coa_id, SUM(A.eom_gl_amount),
        SUM(CASE WHEN A.curr_code = A.gl_curr_code THEN 0 ELSE A.eom_gl_amount END), 0, 0
    FROM tt_gl_forex_ar A
    WHERE session_id = pSessionId
    GROUP BY A.session_id, A.coa_id;

    /*
     * Update nilai trial_balance_amount, yaitu sum end_balance_3 dr tt_sum_gl_trial_balance, berdasarkan coa
     */
    UPDATE tt_gl_forex_ar_summary_coa
    SET trial_balance_amount = trial_balance_amount + (
        SELECT SUM(B.end_balance_3)
        FROM tt_sum_gl_trial_balance B
        WHERE tt_gl_forex_ar_summary_coa.session_id = B.session_id AND
            tt_gl_forex_ar_summary_coa.coa_id = B.coa_id)
    WHERE session_id = pSessionId;

    /*
     * Update nilai selisih antara nilai trial balance terhadap nilai EoM gl amount.
     */
    UPDATE tt_gl_forex_ar_summary_coa
    SET diff_amount = total_eom_gl_amount - trial_balance_amount
    WHERE session_id = pSessionId;

    /*
     * Add by WTC, 20151125:
     * Jika ada selisih, dan ada saldo dokumen valas, maka lakukan alokasi ke dokumen valas.
     * Jika ada selisih, namun tidak ada saldo dokumen valas, maka buat journal AR terhadap pembulatan nilai.
     *
     * Alokasikan nilai selisih antara nilai trial balance terhadap nilai EoM gl amount, ke semua dokumen AR secara prorate.
     * Pengalokasian nilai cukup untuk dokumen2 yg currency nya tidak sama dengan currency GL (valas), karena diasumsikan untuk
     * dokumen yg currency nya sama dengan currency GL, tidak ada selisih kurs.
     * Dengan demikian, saat perhitungan alokasi, total nilai EoM gl amount yg dijadikan acuan, hanya dari dokumen2 dgn
     * valas.
     */
    UPDATE tt_gl_forex_ar
    SET add_forex_amount = ROUND(tt_gl_forex_ar.eom_gl_amount * B.diff_amount / B.total_forex_eom_gl_amount, vRoundingDigit)
    FROM tt_gl_forex_ar_summary_coa B
    WHERE tt_gl_forex_ar.session_id = pSessionId AND
        tt_gl_forex_ar.session_id = B.session_id AND
        tt_gl_forex_ar.coa_id = B.coa_id AND
        tt_gl_forex_ar.curr_code <> tt_gl_forex_ar.gl_curr_code AND
        B.diff_amount <> 0 AND
        B.total_forex_eom_gl_amount <> 0;

    /*
     * Hitung nilai sisa diff_amount setelah dialokasikan ke semua dokumen AR.
     * Nilai sisa ini merupakan sisa hasil pembulatan saat alokasi.
     * Nilai sisa ini akan diupdate ke dokumen AR dengan nilai saldo terbesar per COA.
     */
    UPDATE tt_gl_forex_ar_summary_coa
    SET diff_amount = diff_amount - (SELECT SUM(B.add_forex_amount)
                                    FROM tt_gl_forex_ar B
                                    WHERE tt_gl_forex_ar_summary_coa.session_id = B.session_id AND
                                        tt_gl_forex_ar_summary_coa.coa_id = B.coa_id)
    WHERE tt_gl_forex_ar_summary_coa.session_id = pSessionId;

    UPDATE tt_gl_forex_ar
    SET add_forex_amount = add_forex_amount + B.diff_amount
    FROM tt_gl_forex_ar_summary_coa B
    WHERE tt_gl_forex_ar.session_id = pSessionId AND
        tt_gl_forex_ar.session_id = B.session_id AND
        tt_gl_forex_ar.coa_id = B.coa_id AND
        B.diff_amount <> 0 AND
        B.total_forex_eom_gl_amount <> 0 AND
        tt_gl_forex_ar.gl_forex_ar_id = (SELECT C.gl_forex_ar_id
                                         FROM tt_gl_forex_ar C
                                         WHERE B.session_id = C.session_id AND
                                            B.coa_id = C.coa_id AND
                                            C.curr_code <> C.gl_curr_code
                                         ORDER BY C.eom_gl_amount DESC, C.doc_date DESC, C.doc_no DESC
                                         LIMIT 1);

    /*
     * Cari coa_id yg terdaftar di type partner supplier dan memiliki nilai di temp trial balance,
     * namun tidak memiliki dokumen AR (tidak terdaftar di tt_gl_forex_ar_summary_coa).
     * Insert coa_id tersebut ke tt_gl_forex_ar.
     * Hal ini perlu dilakukan agar jika terjadi bug program atau kesalahan input oleh user,
     * dapat terlihat dari nilai forex AR yg tidak sesuai harapan.
     */
    INSERT INTO tt_gl_forex_ar (
        session_id, date_year_month, tenant_id, ou_id, doc_type_id, invoice_id, doc_no, doc_date,
        partner_id, coa_id, curr_code,
        amount, gl_curr_code, initial_gl_amount, eom_gl_amount, add_forex_amount)
    SELECT A.session_id, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId, 'NO DOC', vEmptyValue,
        vEmptyId, A.coa_id, vCurrGL, 0, vCurrGL, 0, 0, SUM(A.end_balance_3)
    FROM tt_sum_gl_trial_balance A, m_type_partner B, m_partner_type C, m_partner D
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.date_year_month = pYearMonth AND
        A.ou_bu_id = pOuId AND
        A.coa_id = B.coa_id AND
        B.type_partner_id = C.type_partner_id AND
        C.group_partner = 'C' AND
        C.partner_id = D.partner_id AND
        D.tenant_id = A.tenant_id AND
        NOT EXISTS (SELECT 1 FROM tt_gl_forex_ar_summary_coa E
                    WHERE A.session_id = E.session_id AND A.coa_id = E.coa_id)
    GROUP BY A.session_id, A.date_year_month, A.tenant_id, A.ou_bu_id, A.coa_id
    HAVING SUM(A.end_balance_3) <> 0;

    /*
     * Buat data journal apabila ada nilai forex
     */
    IF EXISTS (SELECT 1 FROM tt_gl_forex_ar_summary_coa WHERE session_id = pSessionId AND total_eom_gl_amount <> trial_balance_amount) THEN
        -- Buat data jurnal forex untuk tahun bulan ybs
        PERFORM gl_manage_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, pOuId, (vDocJournal).journal_type, (vDocJournal).ledger_code, pYearMonth, 'MONTHLY', pDatetime, pUserId);

        SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

        /*
         * Insert data gl_journal_trx. 1 data gl_journal_trx untuk semua
         */
        INSERT INTO gl_journal_trx
        (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
        ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
        ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        VALUES (vJournalTrxId, pTenantId, (vDocJournal).journal_type, vForexArDocTypeId, vForexArDocTypeId, vDateEndOfMonth, vDateEndOfMonth,
            (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, vEmptyId, vEmptyId, vEmptyId, vEmptyValue, vEmptyValue,
            vEmptyId, vEmptyId, vDateEndOfMonth, vCurrGL, 'FOREX AR', vStatusRelease, vStatusApproved,
            0, pDatetime, pUserId, pDatetime, pUserId);

        /*
         * Insert data temp item journal yang nilai forex > 0
         * Note: Debit AR
         */
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, pTenantId, vJournalTrxId, 1,
            A.doc_type_id, A.invoice_id,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
            A.coa_id, A.gl_curr_code, 0, vEmptyId,
            A.add_forex_amount, vDateEndOfMonth, vTypeRateEom,
            1, 1, 'FX_AR', f_get_partner_name(A.partner_id) || ' ; ' ||A.doc_no || ' ; ' || A.doc_date
        FROM tt_gl_forex_ar A
        WHERE A.session_id = pSessionId AND
            A.add_forex_amount > 0;

        /*
         * Insert data temp item journal yang nilai forex < 0
         * Note: Credit AR
         */
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, pTenantId, vJournalTrxId, 1,
            A.doc_type_id, A.invoice_id,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
            A.coa_id, A.gl_curr_code, 0, vEmptyId,
            A.add_forex_amount * -1, vDateEndOfMonth, vTypeRateEom,
            1, 1, 'FX_AR', f_get_partner_name(A.partner_id) || ' ; ' ||A.doc_no || ' ; ' || A.doc_date
        FROM tt_gl_forex_ar A
        WHERE A.session_id = pSessionId AND
            A.add_forex_amount < 0;

        /*
         * Insert data temp item journal untuk forex.
         * Diasumsikan hasil jumlahnya bernilai positif, maka perlu membuat Credit Forex.
         * 20170816, WTC, tambahkan filter yang memiliki O/S document AR saja. Yang tanpa O/S document AR, akan dijurnal terhadap rounding
         */
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, pTenantId, vJournalTrxId, 1,
            vEmptyId, vEmptyId,
            vEmptyId, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
            vForexCoaId, vCurrGL, 0, vEmptyId,
            SUM(A.add_forex_amount), vDateEndOfMonth, vTypeRateEom,
            1, 1, 'UNREAL_FX', 'Unrealize Forex'
        FROM tt_gl_forex_ar A
        WHERE A.session_id = pSessionId
            AND A.invoice_id <> vEmptyId
        GROUP BY A.session_id
        HAVING SUM(A.add_forex_amount) <> 0;

        /*
         * Insert data temp item journal AR, apabila total valas nya = 0 dan memiliki O/S document AR
         * Note: Debit AR, jika diff_amount > 0
         *       Credit AR, jika diff_amount < 0
         */
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, pTenantId, vJournalTrxId, 1,
            vEmptyId, vEmptyId,
            vEmptyId, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, CASE WHEN A.diff_amount > 0 THEN vSignDebt ELSE vSignCredit END, vSystemCOA, vEmptyId,
            A.coa_id, vCurrGL, 0, vEmptyId,
            ABS(A.diff_amount), vDateEndOfMonth, vTypeRateEom,
            1, 1, 'FX_AR', 'FOREX AR'
        FROM tt_gl_forex_ar_summary_coa A
        WHERE A.session_id = pSessionId AND
            A.total_forex_eom_gl_amount = 0;

        /*
         * Insert temp data journal item untuk rounding, apabila total valas nya = 0 dan memiliki O/S document AR
         * Note: credit rounding
         */
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, pTenantId, vJournalTrxId, 1,
            vEmptyId, vEmptyId,
            vEmptyId, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
            f_get_coa_id_for_rounding(pTenantId), vCurrGL, 0, vEmptyId,
            SUM(A.diff_amount), vDateEndOfMonth, vTypeRateEom,
            1, 1, 'ROUNDING', 'FOREX AR'
        FROM tt_gl_forex_ar_summary_coa A
        WHERE A.session_id = pSessionId AND
            A.total_forex_eom_gl_amount = 0
        GROUP BY A.session_id
        HAVING SUM(A.diff_amount) <> 0;

        /*
         * Insert temp data journal item untuk rounding, apabila total valas nya = 0 dan memiliki O/S document AR
         * Note: debit rounding
         */
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, pTenantId, vJournalTrxId, 1,
            vEmptyId, vEmptyId,
            vEmptyId, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
            f_get_coa_id_for_rounding(pTenantId), vCurrGL, 0, vEmptyId,
            SUM(A.add_forex_amount), vDateEndOfMonth, vTypeRateEom,
            1, 1, 'ROUNDING', 'FOREX AR'
        FROM tt_gl_forex_ar A
        WHERE A.session_id = pSessionId AND
            A.invoice_id = vEmptyId
        GROUP BY f_get_coa_id_for_rounding(pTenantId);

        /*
         * Jumlah amount forex hasil perhitungan di atas dapat bernilai negatif atau positif.
         * Jika bernilai negatif, maka update sign_journal = D dan amount = -amount (agar menjadi positif),
         * yang artinya Debit Forex.
         */
        UPDATE tt_journal_trx_item SET sign_journal = vSignDebt, amount = amount * -1
        WHERE session_id = pSessionId AND journal_desc IN ('UNREAL_FX', 'ROUNDING') AND amount < 0;

        /*
         * Insert temp data journal item
         */
        INSERT INTO gl_journal_trx_item
        (tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, gl_curr_code, gl_amount, journal_desc, remark,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
            A.ref_doc_type_id, A.ref_id,
            A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
            A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
            A.coa_id, A.curr_code, A.qty, A.uom_id,
            A.amount, A.journal_date, A.type_rate,
            A.numerator_rate, A.denominator_rate, A.curr_code, A.amount, A.journal_desc, A.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId
            AND A.journal_desc = 'FX_AR';

        INSERT INTO gl_journal_trx_mapping
        (tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, gl_curr_code, gl_amount, journal_desc, remark,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
            A.ref_doc_type_id, A.ref_id,
            A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
            A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
            A.coa_id, A.curr_code, A.qty, A.uom_id,
            A.amount, A.journal_date, A.type_rate,
            A.numerator_rate, A.denominator_rate, A.curr_code, A.amount, A.journal_desc, A.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId
            AND A.journal_desc IN ('UNREAL_FX', 'ROUNDING');

        /*
         * Insert data temp jurnal item ke temp trial balance, khusus untuk mengisi nilai mut_debit_3 dan mut_credit_3
         */
        INSERT INTO tt_gl_trial_balance (
            session_id, tenant_id, date_year_month, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id,
            segmen_id, coa_id, sign_coa, beg_balance_1, mut_debit_1, mut_credit_1, end_balance_1,
            mut_debit_2, mut_credit_2, end_balance_2,
            mut_debit_3,
            mut_credit_3, end_balance_3, amount)
        SELECT A.session_id, A.tenant_id, pYearMonth, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.ou_rc_id,
            A.segmen_id, A.coa_id, C.sign_coa, 0, 0, 0, 0,
            0, 0, 0,
            CASE WHEN A.sign_journal = vSignDebt THEN A.amount ELSE 0 END,
            CASE WHEN A.sign_journal = vSignCredit THEN A.amount ELSE 0 END, 0, 0
        FROM tt_journal_trx_item A, gl_journal_trx B, m_coa C
        WHERE A.session_id = pSessionId AND
            A.journal_trx_id = B.journal_trx_id AND
            A.coa_id = C.coa_id;

        /*
         * Insert juga data temp summary journal trx
         */
        INSERT INTO tt_gl_summary_journal_trx (
            session_id, tenant_id, date_year_month, ou_bu_id, ou_branch_id, ou_sub_bu_id,
            partner_id, product_id, cashbank_id, ou_rc_id, segment_id, sign_journal, coa_id, sign_coa,
            curr_code, trx_amount, gl_curr_code, gl_amount, journal_process_periode, journal_type)
        SELECT A.session_id, A.tenant_id, pYearMonth, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
            A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id, A.segmen_id, A.sign_journal, A.coa_id, C.sign_coa,
            A.curr_code, A.amount, A.curr_code, A.amount, 'MONTHLY', B.journal_type
        FROM tt_journal_trx_item A, gl_journal_trx B, m_coa C
        WHERE A.session_id = pSessionId AND
            A.journal_trx_id = B.journal_trx_id AND
            A.coa_id = C.coa_id;

        -- Add by WTC, 20151125, tambahkan jurnal balik forex untuk awal tahun bulan berikutnya
        -- Hal ini diperlukan agar tidak terjadi selisih forex
        IF EXISTS (SELECT 1 FROM tt_gl_forex_ar WHERE add_forex_amount <> 0 AND invoice_id <> vEmptyId AND session_id = pSessionId) THEN
            PERFORM gl_manage_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, pOuId, (vDocJournal).journal_type, (vDocJournal).ledger_code, vNextYearMonth, 'MONTHLY', pDatetime, pUserId);

            SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

            /*
             * Insert data gl_journal_trx. 1 data gl_journal_trx untuk semua
             */
            INSERT INTO gl_journal_trx
            (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
            ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
            ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
            VALUES (vJournalTrxId, pTenantId, (vDocJournal).journal_type, vForexArDocTypeId, vForexArDocTypeId, vNextYearMonthBeg, vNextYearMonthBeg,
                (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, vEmptyId, vEmptyId, vEmptyId, vEmptyValue, vEmptyValue,
                vEmptyId, vEmptyId, vNextYearMonthBeg, vCurrGL, 'REVERSED FOREX AR OF PREV YEAR MONTH', vStatusRelease, vStatusApproved,
                0, pDatetime, pUserId, pDatetime, pUserId);

            /*
             * Insert temp data journal item
             */
            INSERT INTO gl_journal_trx_item
            (tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, gl_curr_code, gl_amount, journal_desc, remark,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
            SELECT pTenantId, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId),
                vEmptyId, vEmptyId,
                vEmptyId, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, CASE WHEN (A.total_eom_gl_amount - A.trial_balance_amount) > 0 THEN vSignCredit ELSE vSignDebt END, vSystemCOA, vEmptyId,
                A.coa_id, vCurrGL, 0, vEmptyId,
                ABS(A.total_eom_gl_amount - A.trial_balance_amount), vNextYearMonthBeg, vTypeRateEom,
                1, 1, vCurrGL, ABS(A.total_eom_gl_amount - A.trial_balance_amount), 'FX_AR', 'FOREX AR',
                0, pDatetime, pUserId, pDatetime, pUserId
            FROM tt_gl_forex_ar_summary_coa A
            WHERE A.session_id = pSessionId;

            INSERT INTO gl_journal_trx_mapping
            (tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, gl_curr_code, gl_amount, journal_desc, remark,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
            SELECT A.tenant_id, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
                A.ref_doc_type_id, A.ref_id,
                A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
                A.segmen_id, CASE A.sign_journal WHEN vSignDebt THEN vSignCredit ELSE vSignDebt END, A.flg_source_coa, A.activity_gl_id,
                A.coa_id, A.curr_code, A.qty, A.uom_id,
                A.amount, vNextYearMonthBeg, A.type_rate,
                A.numerator_rate, A.denominator_rate, A.curr_code, A.amount, A.journal_desc, A.remark,
                0, pDatetime, pUserId, pDatetime, pUserId
            FROM tt_journal_trx_item A
            WHERE A.session_id = pSessionId
                AND A.journal_desc IN ('UNREAL_FX');
        END IF;

        DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    END IF;

    DELETE FROM tt_gl_forex_ar WHERE session_id = pSessionId;
    DELETE FROM tt_gl_forex_ar_summary_coa WHERE session_id = pSessionId;
END;
$BODY$
LANGUAGE 'plpgsql'