posting RM

-- Copied from ERP Trunk, 20171128
/*
 * WTC, 20161219:
 *   1. Update sign journal untuk trx mapping adj stock qty (521), baik yg bernilai minus maupun plus (sebelumnya hanya yg bernilai minus)
 *   2. Exclude adj stock amount dalam proses posting journal ini, melainkan set status doc nya saja menjadi R
 *
 * Modified By fitra 21 Mei 2018
 * mengambil nilai in_summary_monthly_amount berdasarkan ou bu saja
 *
 */
CREATE OR REPLACE FUNCTION gl_posting_raw_materials_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);
    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';
    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);

    /*
     * 1.ambil data journal trx yang melakukan Debit / Credit Stok finish goods product
     *   doc type : 502, 521, 413, 511, 311, 154, 354, 431, 526, 537
     *   doc_desc : 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
     * 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
     *
     * WTC, 20161219, adjustment stock amount tidak perlu diset lagi dari hasil proses costing, melainkan diset saja status nya menjadi R
     */
    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, vEmptyId, vEmptyId,
        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_raw_materials_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.doc_type_id IN (502, 521, 413, 511, 311, 154, 354, 431, 526, 537) 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;

    /* Update amount dipisahkan menjadi 3 bagian, yaitu:
        1. Tuk doc selain adj qty
        2. Tuk doc adj qty yg qty > 0
        3. Tuk doc adj qty 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/
    */
    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 <> 521;

    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 = 521 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 = 521 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.
     * Mod by WTC, 20161019, Khusus untuk dokumen adj stock qty, perlu memperhatikan sign journal nya juga
     */
    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)
    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)
    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.
     */
    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) 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 = 521 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;

    /*
     * WTC, 170130, Harus update yg gl_amount nya positif dl. Jika terbalik, maka akibatnya semua akan terupdate sign_journal menjadi C (karena semua gl_amount sudah bernilai positif)
     * Update sign_journal menjadi C untuk doc adj qty yg amountnya > 0
     */
    UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignCredit
    WHERE doc_type_id = 521 AND
        gl_amount > 0 AND
        session_id = pSessionId;

    /*
     * 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 = 521 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
     */
    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;

    /*
     * 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), dan adjustment stok amount (522)
    UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
    WHERE doc_type_id IN (528,522)
    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;
  /