gl_posting_service_sales_pos_costing_journal

/**
 * Adrian, Feb 22, 2018
 * Untuk jurnal monthly dengan doc_type sales POS (431)
 * dan tidak memiliki item RM, FG, ataupun WIP,
 * maka update:
 * - gl_curr_code sesuai valuta buku
 * - status doc menjadi 'R'
 * - gl_amount = 0
 */
CREATE OR REPLACE FUNCTION gl_posting_service_sales_pos_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);
    vDocTypeIdSalesPos      bigint;

BEGIN

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

    DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
    DELETE FROM tt_gl_journal_trx_mapping 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 yang hanya memiliki produk
     *   selain FG, RM, dan WIP
     *      doc type : 431
     *      doc_desc : sales POS
     * 2.berikan nilai 0 untuk tiap journal trx item
     * 3.update nilai amount, untuk memastikan jumlah amount di in_summary_monthly_product = jumlah nilai product di tt_gl_journal_costing
     */
    WITH tt_journal_trx_non_service AS(
        SELECT A.journal_trx_id
        FROM gl_journal_trx A
        INNER JOIN vw_gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN dt_date C ON A.doc_date = C.string_date
        INNER JOIN m_product D ON B.product_id = D.product_id
        INNER JOIN m_ctgr_product E ON D.ctgr_product_id = E.ctgr_product_id
        INNER JOIN m_group_product F ON E.group_product_id = F.group_product_id
        WHERE A.tenant_id = pTenantId AND
            A.ou_bu_id = pOuId AND
            A.doc_type_id IN (vDocTypeIdSalesPos) AND
            C.year_month_date = pYearMonth AND
            B.product_id <> vEmptyId AND
            F.group_product_code IN ('FG', 'RM', 'SP', 'CONSUMABLE')
    )
    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,
        CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
        CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
        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
    WHERE A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.doc_type_id IN (vDocTypeIdSalesPos) 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
        NOT EXISTS (
            SELECT 1
            FROM tt_journal_trx_non_service D
            WHERE A.journal_trx_id = D.journal_trx_id
        );

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

    /*
     * buat data journal trx mapping untuk doc type sales POS
     */
    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 IN (vDocTypeIdSalesPos) 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 gl_journal_trx_mapping untuk nilai buku transaksi
     */
    UPDATE gl_journal_trx_mapping SET numerator_rate = 1, denominator_rate = 1,
                                gl_amount = 0,
                                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 = vDocTypeIdSalesPos AND
        gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;

    --Update status_doc di gl_journal_trx menjadi 'R'
    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;

    DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
    DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;

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