f_check_gl_balance

CREATE OR REPLACE FUNCTION f_check_gl_balance(character varying, bigint, bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pDocDateFrom        ALIAS FOR $4;
    pDocDateTo          ALIAS FOR $5;
BEGIN

    DELETE FROM tt_gl_check_balance_header WHERE session_id = pSessionId;
    DELETE FROM tt_gl_check_balance_details WHERE session_id = pSessionId;

    /*
     * gl_journal_trx_item
     *
     * */
    INSERT INTO tt_gl_check_balance_header(
            session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
            doc_no, doc_date, type_coa, main_acc,
            sub_acc, coa_desc, sign_coa, sign_journal, curr_code, amount,
            gl_amount, sign_gl_amount, asset_amount, liabilities_amount,
            revenue_amount, costs_amount, selisih_neraca, selisih_laba_rugi)
    SELECT pSessionId, a.tenant_id, a.ou_bu_id, a.journal_trx_id, a.doc_type_id,
            a.doc_no, a.doc_date, d.type_coa, c.main_acc,
            c.sub_acc, c.coa_desc, c.sign_coa, b.sign_journal, b.curr_code, b.amount,
            b.gl_amount, (CASE WHEN c.sign_coa <> b.sign_journal THEN b.gl_amount *-1 ELSE b.gl_amount END) AS sign_gl_amount,
            0, 0, 0, 0, 0, 0
    FROM gl_journal_trx a, gl_journal_trx_item b, m_coa c, m_group_coa d
    WHERE a.tenant_id = pTenantId
        AND a.ou_bu_id = pOuId
        AND a.journal_trx_id = b.journal_trx_id
        AND a.doc_date BETWEEN pDocDateFrom AND pDocDateTo
        AND b.coa_id = c.coa_id
        AND c.group_coa_id = d.group_coa_id;

    /*
     * gl_journal_trx_mapping
     *
     * */
    INSERT INTO tt_gl_check_balance_header(
            session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
            doc_no, doc_date, type_coa, main_acc,
            sub_acc, coa_desc, sign_coa, sign_journal, curr_code, amount,
            gl_amount, sign_gl_amount, asset_amount, liabilities_amount,
            revenue_amount, costs_amount, selisih_neraca, selisih_laba_rugi)
    SELECT pSessionId, a.tenant_id, a.ou_bu_id, a.journal_trx_id, a.doc_type_id,
            a.doc_no, a.doc_date, d.type_coa, c.main_acc,
            c.sub_acc, c.coa_desc, c.sign_coa, b.sign_journal, b.curr_code, b.amount,
            b.gl_amount, (CASE WHEN c.sign_coa <> b.sign_journal THEN b.gl_amount *-1 ELSE b.gl_amount END) AS sign_gl_amount,
            0, 0, 0, 0, 0, 0
    FROM gl_journal_trx a, gl_journal_trx_mapping b, m_coa c, m_group_coa d
    WHERE a.tenant_id = pTenantId
        AND a.ou_bu_id = pOuId
        AND a.journal_trx_id = b.journal_trx_id
        AND a.doc_date BETWEEN pDocDateFrom AND pDocDateTo
        AND b.coa_id = c.coa_id
        AND c.group_coa_id = d.group_coa_id;

    /*
     * gl_journal_trx_fx
     *
     * */
    INSERT INTO tt_gl_check_balance_header(
            session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
            doc_no, doc_date, type_coa, main_acc,
            sub_acc, coa_desc, sign_coa, sign_journal, curr_code, amount,
            gl_amount, sign_gl_amount, asset_amount, liabilities_amount,
            revenue_amount, costs_amount, selisih_neraca, selisih_laba_rugi)
    SELECT pSessionId, a.tenant_id, a.ou_bu_id, a.journal_trx_id, a.doc_type_id,
            a.doc_no, a.doc_date, d.type_coa, c.main_acc,
            c.sub_acc, c.coa_desc, c.sign_coa, b.sign_journal, b.curr_code, b.amount,
            b.gl_amount, (CASE WHEN c.sign_coa <> b.sign_journal THEN b.gl_amount *-1 ELSE b.gl_amount END) AS sign_gl_amount,
            0, 0, 0, 0, 0, 0
    FROM gl_journal_trx a, gl_journal_trx_fx b, m_coa c, m_group_coa d
    WHERE a.tenant_id = pTenantId
        AND a.ou_bu_id = pOuId
        AND a.journal_trx_id = b.journal_trx_id
        AND a.doc_date BETWEEN pDocDateFrom AND pDocDateTo
        AND b.coa_id = c.coa_id
        AND c.group_coa_id = d.group_coa_id;

    UPDATE tt_gl_check_balance_header
    SET asset_amount = sign_gl_amount * -1
    WHERE type_coa = 'A'
        AND sign_coa = 'C'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET asset_amount = sign_gl_amount
    WHERE type_coa = 'A'
        AND sign_coa = 'D'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET liabilities_amount = sign_gl_amount * -1
    WHERE type_coa = 'L'
        AND sign_coa = 'C'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET liabilities_amount = sign_gl_amount
    WHERE type_coa = 'L'
        AND sign_coa = 'D'
        AND session_id = pSessionId;

     UPDATE tt_gl_check_balance_header
    SET revenue_amount = sign_gl_amount
    WHERE type_coa = 'R'
        AND sign_coa = 'C'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET revenue_amount = sign_gl_amount * -1
    WHERE type_coa = 'R'
        AND sign_coa = 'D'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET costs_amount = sign_gl_amount
    WHERE type_coa = 'C'
        AND sign_coa = 'C'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET costs_amount = sign_gl_amount * -1
    WHERE type_coa = 'C'
        AND sign_coa = 'D'
        AND session_id = pSessionId;

    UPDATE tt_gl_check_balance_header
    SET selisih_neraca = asset_amount + liabilities_amount,
        selisih_laba_rugi = revenue_amount + costs_amount
    WHERE session_id = pSessionId;

    INSERT INTO tt_gl_check_balance_details(
            session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
            doc_no, doc_date, jumlah_selisih_neraca,
            jumlah_selisih_laba_rugi, balance_amount, flg_balance)
    SELECT pSessionId, tenant_id, ou_id, journal_trx_id, doc_type_id,
            doc_no, doc_date, SUM(selisih_neraca),
            SUM(selisih_laba_rugi), SUM(selisih_neraca) - SUM(selisih_laba_rugi), 'N'
    FROM tt_gl_check_balance_header
    WHERE session_id = pSessionId
        AND tenant_id = pTenantId
        AND ou_id = pOuId
    GROUP BY tenant_id, ou_id, journal_trx_id, doc_type_id, doc_no, doc_date;

    UPDATE tt_gl_check_balance_details
    SET flg_balance = 'Y'
    WHERE balance_amount = 0
        AND session_id = pSessionId;

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