in_submit_cost_alloc_to_product

-- Function: in_submit_cost_alloc_to_product(bigint, character varying, character varying)

-- DROP FUNCTION in_submit_cost_alloc_to_product(bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION in_submit_cost_alloc_to_product(
    bigint,
    character varying,
    character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId          bigint;
    vCostAllocToProductId       bigint;
    vUserId             bigint;
    vDatetime           character varying(14);
    vFlagNo             character varying(1);
    vEmptyId            bigint;
    vEmptyValue         character varying(1);
    vActivityCOA        character varying(20);
    vStatusRelease      character varying(1);
    vStatusDraft        character varying(1);
    vJournalType        character varying(20);
    vSignDebit          character varying(1);
    vSignCredit         character varying(1);
    vTypeRate           character varying(3);
    vProductCOA         character varying(10);
    vParentOuId         bigint;
    vJournalTrxId       bigint;

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;

BEGIN

    vEmptyId := -99;
    vFlagNo := 'N';
    vActivityCOA := 'ACTIVITY';
    vStatusRelease := 'R';
    vStatusDraft := 'D';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vEmptyValue := ' ';

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'in_submit_cost_alloc_to_product' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vCostAllocToProductId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'costAllocationId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_in_product_price_balance WHERE session_id = pSessionId;

    /*
     * mengambil data dari in_inventory_item tanpa melakukan group by untuk ditampung ke tt_in_product_price_balance
     */
    INSERT INTO tt_in_product_price_balance
    (session_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date, partner_id, warehouse_id,
    inventory_id, inventory_item_id, product_balance_id, product_id, product_status,
     serial_number, product_expired_date, product_year_made, lot_number, qty_realization, base_uom_id,
     product_price_balance_id, curr_code, amount)
    SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id, A.doc_no, A.doc_date, A.partner_id, A.warehouse_from_id,
        A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
        B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id,
        vEmptyId, B.curr_code, B.amount
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = B.inventory_id AND
        A.inventory_id = vCostAllocToProductId;

    UPDATE tt_in_product_price_balance SET product_balance_id = A.product_balance_id
    FROM in_product_balance A
    WHERE tt_in_product_price_balance.session_id = pSessionId AND
        tt_in_product_price_balance.product_id = A.product_id AND
        tt_in_product_price_balance.tenant_id = A.tenant_id AND
        tt_in_product_price_balance.serial_number = A.serial_number AND
        tt_in_product_price_balance.lot_number = A.lot_number AND
        tt_in_product_price_balance.product_balance_id = vEmptyId;

    INSERT INTO in_product_price_balance
    (tenant_id, ou_id, product_id, product_balance_id,
    product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
    curr_code, amount, qty, uom_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT C.tenant_id, C.ou_id, C.product_id, C.product_balance_id,
        C.doc_date, C.partner_id, C.doc_type_id, C.inventory_item_id, C.doc_no, C.doc_date,
        C.curr_code, SUM(C.amount), 0, C.base_uom_id,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_product_price_balance C
    WHERE C.inventory_id = vCostAllocToProductId AND
        C.session_id = pSessionId
    GROUP BY C.tenant_id, C.ou_id, C.product_id, C.product_balance_id,
        C.doc_date, C.partner_id, C.doc_type_id, C.inventory_item_id, C.doc_no, C.doc_date,
        C.curr_code, C.base_uom_id;

    /*
     * 1. update status doc in_inventory
     * 2. buat data jurnal
     */

    SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
    FROM in_inventory A
    WHERE A.inventory_id = vCostAllocToProductId INTO result;

    vOuStructure := result.ou;
    vDocJournal := result.doc;

    UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE inventory_id = vCostAllocToProductId;

    /*
     * journal adjusment qty
     * Debit Inventory, jika qty > 0
     * Credit Inventory, jika qty < 0
     *
     * Mapping ke account activity gl
     */
    /*
     * membuat data transaksi jurnal :
     * 1. buat admin
     * 2. buat temlate jurnal
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
    FROM in_inventory A
    WHERE A.inventory_id = vCostAllocToProductId;

    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

    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)
    SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A
    WHERE A.inventory_id = vCostAllocToProductId;

    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,
    gl_curr_code, gl_amount)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, B.inventory_item_id,
        A.partner_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, B.product_id), B.curr_code, B.qty_realization, B.base_uom_id,
        B.amount , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark,
        B.curr_code, B.amount
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = vCostAllocToProductId AND
        A.inventory_id = B.inventory_id;

    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,
    gl_curr_code, gl_amount)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, B.ou_rc_id,
        B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
        D.coa_id, B.curr_code, 0, vEmptyId,
        B.amount, A.doc_date, vTypeRate,
        1, 1, 'COST_ALLOC_PRODUCT', vEmptyValue,
        B.gl_curr_code, B.gl_amount
    FROM in_inventory A, in_inventory_alloc_cost_detail B, m_activity_gl D
    WHERE A.inventory_id = vCostAllocToProductId AND
        A.inventory_id = B.inventory_id AND
        B.activity_gl_id = D.activity_gl_id;

    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, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    gl_curr_code, gl_amount)
    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.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        A.gl_curr_code, A.gl_amount
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'PRODUCT_STOCK';

    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, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    gl_curr_code, gl_amount)
    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.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        A.gl_curr_code, A.gl_amount
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'COST_ALLOC_PRODUCT';

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_in_product_price_balance WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION in_submit_cost_alloc_to_product(bigint, character varying, character varying)
  OWNER TO sts;