gl_posting_finish_goods_costing_journal

/*
 * 2016119, WTC, saat hitung total nilai alokasi costing ke jurnal, perlu ada special handling tuk adj stock qty dan exchange, yaitu harus perhatikan sign journal
 */
CREATE OR REPLACE FUNCTION gl_posting_finish_goods_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE

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

    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vEmptyValue             character varying(1);
    vEmptyId                bigint;
    vJournalProcessPeriode  character varying(10);
    vLedgerCode             character varying(10);
    vCurrGL                 character varying(5);
    vRoundingAmount         integer;
    vSignCredit             character varying(1);
    vSignDebit              character varying(1);
    vSystemCOA              character varying(10);

BEGIN

    vStatusRelease := 'R';
    vStatusDraft := 'D';
    vEmptyId := -99;
    vEmptyValue := ' ';
    vJournalProcessPeriode := 'MONTHLY';
    vLedgerCode := 'COSTING.FG';
    vSignCredit := 'C';
    vSignDebit := 'D';
    vSystemCOA := 'SYSTEM';

    DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
    DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
    DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
    DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
    DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;

    vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
    vRoundingAmount := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);

    /*
     * WTC, 160212, menambahkan update jurnal untuk dokumen DO MLM, Return/Buyback, dan Exchange.
     * 1.ambil data journal trx yang melakukan Debit / Credit Stok finish goods product
     *   doc type : 522, 502, 521, 413, 511, 311, 154, 354, 431, 526, 537, 313, 562, 563, 560
     *   doc_desc : adj stok amount, adj stok amount balance, return note, adj stok qty, adj stok outlet qty,
     *              Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop,
     *              DO Receipt, Goods Transfer In Receipt Lost, DO MLM, Return SO MLM, Buyback SO MLM, Exchange MLM
     * 2.berikan nilai tiap journal trx item sesuai dengan product, doc_type_id, ou_bu_id, ou_branch_id, ou_sub_bu_id di in_summary_monthly_product
     * 3.update nilai amount, untuk memastikan jumlah amount di in_summary_monthly_product = jumlah nilai product di tt_gl_journal_costing
     */
    INSERT INTO tt_gl_journal_costing
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
    qty, uom_id, amount, total_amount_product, total_qty_product)
    SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
        B.qty, B.uom_id, 0, 0, 0
    FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.doc_type_id IN (522, 502, 521, 413, 511, 311, 154, 354, 431, 526, 537, 313, 562, 563, 560) AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.journal_trx_id = B.journal_trx_id AND
        B.product_id <> vEmptyId AND
        B.product_id = D.product_id;

    INSERT INTO tt_gl_journal_costing
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
    qty, uom_id, amount, total_amount_product, total_qty_product)
    SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
        B.qty, B.uom_id, 0, 0, 0
    FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product_assembly D
    WHERE A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.doc_type_id IN (522, 502, 521, 413, 511, 311, 154, 354, 431, 526, 537, 313, 562, 563, 560) AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.journal_trx_id = B.journal_trx_id AND
        B.product_id <> vEmptyId AND
        B.product_id = D.product_id;

    /* WTC, 160212, Menambahkan tipe dokumen exchange, yg perilaku nya mirip dengan adj stock
     * Update amount dipisahkan menjadi 5 bagian, yaitu:
        1. Tuk doc selain adj qty dan exchange
        2. Tuk doc adj qty dan exchange yg qty > 0
        3. Tuk doc adj qty dan exchange yg qty < 0
       Hal ini perlu dilakukan, karena pada in_summary_monthly_amount bisa ada 2 nilai berbeda tuk doc adj qty dgn product_id yg sama,
       yaitu yg mengurangi stock dan yg menambah stock, dimana yg mengurangi stock nilainya berdasarkan nilai cogs,
       sedangkan yg menambah stock nilainya berdasarkan harga standar. Hal ini berlaku juga untuk exchange
    */
    UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
                                    total_amount_product = ABS(A.gl_amount), total_qty_product = ABS(A.qty)
    FROM in_summary_monthly_amount A
    WHERE  tt_gl_journal_costing.session_id = pSessionId AND
        tt_gl_journal_costing.tenant_id = A.tenant_id AND
        tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
        tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
        tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
        tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
        tt_gl_journal_costing.product_id = A.product_id AND
        A.date_year_month = pYearMonth AND
        A.doc_type_id NOT IN (521, 560) AND
        A.qty <> 0;

    UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
                                    total_amount_product = ABS(A.gl_amount), total_qty_product = ABS(A.qty)
    FROM in_summary_monthly_amount A
    WHERE  tt_gl_journal_costing.session_id = pSessionId AND
        tt_gl_journal_costing.tenant_id = A.tenant_id AND
        tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
        tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
        tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
        tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
        tt_gl_journal_costing.product_id = A.product_id AND
        A.date_year_month = pYearMonth AND
        A.doc_type_id IN (521, 560) AND
        A.qty > 0 AND
        tt_gl_journal_costing.sign_journal = vSignDebit;

    UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
                                    total_amount_product = ABS(A.gl_amount), total_qty_product = ABS(A.qty)
    FROM in_summary_monthly_amount A
    WHERE  tt_gl_journal_costing.session_id = pSessionId AND
        tt_gl_journal_costing.tenant_id = A.tenant_id AND
        tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
        tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
        tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
        tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
        tt_gl_journal_costing.product_id = A.product_id AND
        A.date_year_month = pYearMonth AND
        A.doc_type_id IN (521, 560) AND
        A.qty < 0 AND
        tt_gl_journal_costing.sign_journal = vSignCredit;

    /*
     * summary untuk menghitung total nilai alokasi hasil costing dari in_summary_monthly_amount
     * selisih nilai alokasi dan total nilai dari in_summary_monthly_amount akan diberikan
     * ke item journal trx yang paling maksimum id nya.
     */
    INSERT INTO tt_gl_summary_amount_product
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
     doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount)
    SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product
    FROM tt_gl_journal_costing A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id NOT IN (521, 560)
    GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
            A.doc_type_id, A.product_id, A.total_amount_product;

    INSERT INTO tt_gl_summary_amount_product
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
     doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount, sign_journal)
    SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product, A.sign_journal
    FROM tt_gl_journal_costing A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id IN (521, 560)
    GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
            A.doc_type_id, A.product_id, A.total_amount_product, A.sign_journal;

    UPDATE tt_gl_journal_costing SET amount = amount + A.gl_amount - A.alloc_amount
    FROM tt_gl_summary_amount_product A
    WHERE A.session_id = pSessionId AND
        tt_gl_journal_costing.session_id = A.session_id AND
        tt_gl_journal_costing.journal_trx_item_id = A.journal_trx_item_id AND
        A.alloc_amount <> A.gl_amount;

    /*
     * update nilai buku untuk semua transaksi di gl_journal_trx_item
     */
    UPDATE gl_journal_trx_item SET numerator_rate = 1, denominator_rate = 1,
                                gl_amount = A.amount,
                                gl_curr_code = vCurrGL
    FROM tt_gl_journal_costing A
    WHERE A.session_id = pSessionId AND
        gl_journal_trx_item.journal_trx_item_id = A.journal_trx_item_id;

    /*
     * hitung summary journal per trx, nilai summary akan digunakan
     *  1.untuk update nilai item journal mapping
     *  2.untuk hitung selisih forex : hny untuk trx Nota Klaim
     */
    INSERT INTO tt_gl_summary_journal
    (session_id, ctgr_journal, journal_trx_id, sign_journal,
    gl_amount, temp_amount)
    SELECT A.session_id, 'ITEM', A.journal_trx_id, A.sign_journal,
            SUM(A.amount), SUM(A.amount)
    FROM tt_gl_journal_costing A
    WHERE A.session_id = pSessionId
    GROUP BY session_id, journal_trx_id, sign_journal;

    /*
     * ambil data journal trx mapping untuk doc type id = Nota Klaim
     * hitung rate dan nilai buku nya
     * update langsung data gl_journal_trx_mapping nya
     */
    INSERT INTO tt_gl_journal_trx_mapping
    (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
    sign_journal, curr_code, amount,
    journal_date, type_rate)
    SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
        D.sign_journal, D.curr_code, D.amount,
        D.journal_date, D.type_rate
    FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
    WHERE A.session_id = pSessionId AND
        A.doc_type_id = 511 AND
        A.journal_trx_id = D.journal_trx_id
    GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
        D.sign_journal, D.curr_code, D.amount, D.journal_date, D.type_rate;

    INSERT INTO tt_gl_admin_journal_rate
    (session_id, type_rate, journal_date, curr_code, gl_curr_code, data_exchange_rate)
    SELECT pSessionId, A.type_rate, A.journal_date, A.curr_code, vCurrGL, f_get_exchange_rate(pTenantId, A.type_rate, A.journal_date, A.curr_code, vCurrGL)
    FROM (
        SELECT A.journal_date, A.type_rate, A.curr_code
        FROM tt_gl_journal_trx_mapping A
        WHERE A.session_id = pSessionId
        GROUP BY journal_date, type_rate, curr_code) A;

    UPDATE tt_gl_journal_trx_mapping SET numerator_rate = (data_exchange_rate).numerator_rate,
                                        denominator_rate = (data_exchange_rate).denominator_rate,
                                        gl_amount = ROUND(amount * (data_exchange_rate).numerator_rate / (data_exchange_rate).denominator_rate, vRoundingAmount)
    FROM tt_gl_admin_journal_rate A
    WHERE tt_gl_journal_trx_mapping.session_id = pSessionId AND
        tt_gl_journal_trx_mapping.session_id = A.session_id AND
        tt_gl_journal_trx_mapping.type_rate = A.type_rate AND
        tt_gl_journal_trx_mapping.journal_date = A.journal_date AND
        tt_gl_journal_trx_mapping.curr_code = A.curr_code;

    /*
     * update nilai buku untuk item journal mapping Nota Klaim
     */
    UPDATE gl_journal_trx_mapping SET numerator_rate = A.numerator_rate, denominator_rate = A.denominator_rate, gl_amount = A.gl_amount, gl_curr_code = vCurrGL
    FROM tt_gl_journal_trx_mapping A
    WHERE A.session_id = pSessionId AND
        gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;

    /*
     * buat data journal trx mapping untuk doc type yang bukan nota klaim
     * dengan menghitung total nilai dari tt_gl_journal_costing
     * hal ini dilakukan untuk memastikan jika terjadi kesalahan coding dalam membuat template journal
     * maka nanti akan bisa muncul Forex, padahal transaksi selain Nota Klaim seharusnya tidak ada forex.
     *
     * Khusus untuk adj qty, amountnya harus memperhatikan sign journal nya, karena dlm 1 trx adj qty,
     * bisa saja memiliki 2 jenis journal item dengan sign journal yg berbeda. Hal ini berlaku juga untuk exchange (WTC, 160212)
     */
    INSERT INTO tt_gl_journal_trx_mapping
    (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
    sign_journal, curr_code, amount, gl_amount,
    journal_date, type_rate)
    SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
        D.sign_journal, D.curr_code, D.amount, SUM(A.amount),
        D.journal_date, D.type_rate
    FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
    WHERE A.session_id = pSessionId AND
        A.doc_type_id NOT IN (511,521,560) AND
        A.journal_trx_id = D.journal_trx_id
    GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
        D.sign_journal, D.curr_code, D.journal_date, D.type_rate;

    INSERT INTO tt_gl_journal_trx_mapping
    (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
    sign_journal, curr_code, amount, gl_amount,
    journal_date, type_rate)
    SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
        D.sign_journal, D.curr_code, D.amount, SUM(CASE WHEN A.sign_journal = vSignDebit THEN A.amount ELSE -1 * A.amount END),
        D.journal_date, D.type_rate
    FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
    WHERE A.session_id = pSessionId AND
        A.doc_type_id IN (521,560) AND
        A.journal_trx_id = D.journal_trx_id
    GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
        D.sign_journal, D.curr_code, D.journal_date, D.type_rate;

    /*
     * Update sign_journal menjadi D untuk doc adj qty yg amountnya < 0
     */
    UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignDebit, gl_amount = ABS(gl_amount)
    WHERE doc_type_id IN (521,560) AND
        gl_amount < 0 AND
        session_id = pSessionId;

    /*
     * update gl_journal_trx_mapping untuk nilai buku transaksi yang bukan Nota Klaim
     */
    UPDATE gl_journal_trx_mapping SET numerator_rate = 1, denominator_rate = 1,
                                gl_amount = A.gl_amount,
                                gl_curr_code = vCurrGL,
                                sign_journal = A.sign_journal
    FROM tt_gl_journal_trx_mapping A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id <> 511 AND
        gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;

    INSERT INTO tt_gl_summary_journal
    (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
    SELECT A.session_id, 'MAPPING', A.journal_trx_id, A.sign_journal, SUM(A.gl_amount), SUM(A.gl_amount)
    FROM tt_gl_journal_trx_mapping A
    WHERE A.session_id = pSessionId
    GROUP BY session_id, journal_trx_id, sign_journal;

    UPDATE tt_gl_summary_journal SET temp_amount = temp_amount * -1
    WHERE sign_journal = vSignCredit AND
        session_id = pSessionId;

    INSERT INTO tt_gl_summary_journal
    (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
    SELECT A.session_id, 'ALL', A.journal_trx_id, vSignCredit, 0, SUM(A.temp_amount)
    FROM tt_gl_summary_journal A
    WHERE A.session_id = pSessionId
    GROUP BY A.session_id, A.journal_trx_id;

    UPDATE tt_gl_summary_journal SET sign_journal = vSignDebit, temp_amount = temp_amount * -1
    WHERE session_id = pSessionId AND
        ctgr_journal = 'ALL' AND
        temp_amount < 0;

    DELETE FROM gl_journal_trx_fx A
    WHERE EXISTS (
        SELECT 1
        FROM tt_gl_summary_journal B
        WHERE A.journal_trx_id = B.journal_trx_id AND
              B.ctgr_journal = 'ALL' AND
              B.session_id = pSessionId
    );

    /*
     * buat transaksi forex
     * seharusnya cuma ada untuk transaksi Nota Klaim
     * WTC, 160628, khusus untuk transaksi Nota Klaim, selisih nilai dimasukkan ke COA HPP.
     *     Selain nota klaim, dimasukkan sebagai biaya selisih kurs.
     */
    INSERT INTO gl_journal_trx_fx
    (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, 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 B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
        A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'BiayaSelisihKurs'),
        vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
        1, 1, vCurrGL, A.temp_amount,
        'FX', 'Forex ' || C.doc_desc || ' ' || B.doc_date,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_gl_summary_journal A,
        gl_journal_trx B, m_document C
    WHERE A.session_id = pSessionId AND
        A.journal_trx_id = B.journal_trx_id AND
        B.doc_type_id = C.doc_type_id AND
        A.ctgr_journal = 'ALL' AND
        A.temp_amount <> 0 AND
        B.doc_type_id NOT IN (511, 560);

    -- WTC, 160628, khusus untuk transaksi Nota Klaim, selisih nilai dimasukkan ke COA HPP
    INSERT INTO gl_journal_trx_fx
    (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, 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 B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
        A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'HargaPokokPenjualan'),
        vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
        1, 1, vCurrGL, A.temp_amount,
        'COGS', 'Selisih COGS atas Nota Klaim ' || C.doc_desc || ' ' || B.doc_date,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_gl_summary_journal A,
        gl_journal_trx B, m_document C
    WHERE A.session_id = pSessionId AND
        A.journal_trx_id = B.journal_trx_id AND
        B.doc_type_id = C.doc_type_id AND
        A.ctgr_journal = 'ALL' AND
        A.temp_amount <> 0 AND
        B.doc_type_id IN (511, 560);

    /*
     * data rate dari transaksi klaim
     */
    INSERT INTO gl_admin_journal_rate
    (tenant_id, journal_year_month, process_datetime, process_user_id,
    journal_date, type_rate, curr_code, gl_curr_code, numerator_rate, denominator_rate)
    SELECT pTenantId, pYearMonth, pDatetime, pUserId,
        journal_date, type_rate, curr_code, gl_curr_code, (data_exchange_rate).numerator_rate, (data_exchange_rate).denominator_rate
    FROM tt_gl_admin_journal_rate
    WHERE session_id = pSessionId AND
        curr_code <> gl_curr_code;

    UPDATE gl_admin_journal_trx SET process_datetime = pDatetime, process_user_id = pUserId
    WHERE tenant_id = pTenantId AND
        ou_id = pOuId AND
        journal_process_periode = vJournalProcessPeriode AND
        ledger_code = vLedgerCode AND
        journal_year_month = pYearMonth;

    UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
    FROM (SELECT journal_trx_id
            FROM tt_gl_journal_costing A
            WHERE A.session_id = pSessionId
            GROUP BY journal_trx_id) A
    WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;

    -- update status document khusus untuk document cost allocation to product (528)
    UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
    WHERE doc_type_id = 528
    AND SUBSTRING(doc_date, 1, 6) = pYearMonth;

    DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
    DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
    DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
    DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
    DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;

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