Function Submit DO

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

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

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

    vProcessId              bigint;
    vProcessIdForSI         bigint;
    vDoId                   bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vFlagInvoice            character varying(1);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vStatusFinal            character varying(1);
    vEmptyValue             character varying(1);
    vProductStatus          character varying(5);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vProductCOA             character varying(10);
    vSystemCOA              character varying(10);
    vSoId                   bigint;
    vUnfinishedItem         bigint;
    vNol                    bigint;
    vParentOuId             bigint;
    vOuId                   bigint;
    vOuWarehouseId          bigint;
    vJournalTrxId           bigint;
    vCoaIdGIT               bigint;
    vRgId                   bigint;
    vPartnerShipId          bigint;
    vPartnerBillId          bigint;
    vJournalType            character varying(20);

    vRoleId                 bigint;
    vFlgUserRole            character varying;

    vAutonumIdSI            bigint;
    vDocNoNewSI             character varying;
    vProcessNoRG            character varying;

    vFlagDropship           character varying(1);
    vFlgPkp                 character varying(1);

    vYes                    character varying(1);
    vNo                     character varying(1);

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

    vSchemeDO               character varying(20);
    vSchemeRG               character varying(20);

    vDeliveryOrderDocTypeId bigint;
    vRoundingModeNonTax     character varying(5);

BEGIN

    vFlagInvoice := 'N';
    vEmptyId := -99;
    vStatusRelease := 'R';
    vStatusDraft := 'D';
    vStatusFinal := 'F';
    vEmptyValue := ' ';
    vProductStatus := 'GOOD';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vUnfinishedItem := 0;
    vNol := 0;
    vCoaIdGIT:=f_get_system_coa_by_group_coa(pTenantId, 'PersediaanInTransit');
    vYes  := 'Y';
    vNo   := 'N';

    vRoleId                 := -99;
    vFlgUserRole            := '';

    vSchemeDO := 'FB01';
    vSchemeRG := 'CB01';

    vDeliveryOrderDocTypeId = 311;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;


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

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

    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;
/*
 * 1. update status doc sl_do
 * 2. add sl_log_so_balance_item
 * 3. add sl_so_balance_invoice
 * 4. add sl_so_balance_invoice_tax
 * 5. add in_log_product_balance_stock
 * 6. add in_balance_do_item
 * 7. update status sl_so_balance_item
 * 8. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
 * 9. add gl_journal_trx
 * 10. add gl_journal_trx_item
 * 11. add gl_journal_trx_mapping
 *
 */

    SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
    FROM sl_do A
    WHERE A.do_id = vDoId INTO result;

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

    UPDATE sl_do SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE do_id = vDoId;

    INSERT INTO sl_log_so_balance_item
    (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
     qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, C.so_id, C.so_item_id, A.doc_type_id, A.do_id, B.do_item_id,
        B.qty_dlv_so * -1, B.so_uom_id, B.qty_dlv_int  *-1, B.base_uom_id, B.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_do A, sl_do_item B, sl_so_item C
    WHERE A.do_id = vDoId AND
          A.do_id = B.do_id AND
          B.ref_id = C.so_item_id;

    INSERT INTO sl_balance_do_custom_for_dkb(
            do_id, tenant_id, doc_no, doc_date, ou_id, warehouse_id, partner_id,
            partner_ship_to_id, so_id, so_no, so_date, flg_dkb, dkb_id, flg_packing_list,
            packing_list_id, create_datetime, create_user_id, update_datetime,
            update_user_id, version)
    SELECT A.do_id,A.tenant_id,A.doc_no,A.doc_date,A.ou_id,A.warehouse_id,B.partner_id,
        A.partner_ship_to_id,B.so_id AS so_id,B.doc_no AS so_no,B.doc_date AS so_date,'N',-99,'N',
        -99,A.create_datetime, A.create_user_id, A.update_datetime,
        A.update_user_id, A.version
    FROM sl_do A
    INNER JOIN sl_so B ON A.ref_id = B.so_id
    WHERE  A.do_id = vDoId;

    INSERT INTO sl_so_balance_invoice
    (tenant_id, ou_id, partner_id, so_id,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
    curr_code, price_so, item_amount, flg_invoice, invoice_id,
    regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    gross_sell_price_so, flg_tax_amount, tax_id, tax_percentage, discount_percentage, discount_amount)

    SELECT A.tenant_id, A.ou_id, D.partner_bill_to_id, A.ref_id,
        A.doc_type_id, A.do_id, A.doc_no, A.doc_date, B.do_item_id, B.qty_dlv_so, B.so_uom_id,
        C.curr_code, C.nett_sell_price,
        f_get_amount_before_tax_and_disc(B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax),
        vFlagInvoice, vEmptyId,
        C.discount_amount * B.qty_dlv_so, 0, 0, 0,
        0, vDatetime, vUserId, vDatetime, vUserId,

        C.gross_sell_price, C.flg_tax_amount, C.tax_id, C.tax_percentage, C.discount_percentage, C.discount_amount
    FROM sl_do A, sl_do_item B, sl_so_item C, sl_so D
    WHERE A.do_id = vDoId AND
          A.do_id = B.do_id AND
          B.ref_id = C.so_item_id AND
          C.so_id = D.so_id;

    INSERT INTO sl_so_balance_invoice_tax
    (tenant_id, ou_id, partner_id, so_id,
    ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
    tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, A.ref_id,
        A.doc_type_id, A.do_id, B.do_item_id, C.tax_id, D.flg_amount,
        C.tax_percentage, C.curr_code,
        f_get_amount_before_tax_and_disc(B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax),
        f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * (C.gross_sell_price - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage), vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_do A, sl_do_item B, sl_so_item C, m_tax D, sl_so E
    WHERE A.do_id = vDoId AND
          A.do_id = B.do_id AND
          B.ref_id = C.so_item_id AND
          C.tax_id = D.tax_id AND
          C.so_id = E.so_id;

    /*
     * buat data log product balance stock
     * ref item id = do_product_id
     */
    INSERT INTO in_log_product_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
        C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_do A, sl_do_item B, sl_do_product C, m_warehouse_ou D
    WHERE A.do_id = vDoId AND
        A.do_id = B.do_id AND
        B.do_item_id = C.do_item_id AND
        A.warehouse_id = D.warehouse_id
    GROUP BY A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
            C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;

    /*
     * add data balance do item yang akan digunakan di inventory untuk pembuatan return note,
     * saat akan membuat return note
     */
    INSERT INTO in_balance_do_item
    (do_item_id, tenant_id, ou_id, do_id, doc_no, doc_date, partner_id,
      so_id, so_no, so_date, so_item_id,
      qty_dlv, qty_return, so_uom_id, qty_dlv_int,
      qty_return_int, base_uom_id, status_item,
      "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
            A.ref_id, C.doc_no, C.doc_date, B.ref_id,
            SUM(B.qty_dlv_so), 0, B.so_uom_id, SUM(B.qty_dlv_int),
            0, B.base_uom_id, vStatusRelease,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_do A, sl_do_item B, sl_so C
    WHERE A.do_id = vDoId AND
        A.do_id = B.do_id AND
        A.ref_id = C.so_id
    GROUP BY B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
        A.ref_id, C.doc_no, C.doc_date, B.ref_id, B.so_uom_id, B.base_uom_id;

    UPDATE sl_so_balance_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    FROM sl_do_item A
    WHERE sl_so_balance_item.so_item_id = A.ref_id AND
        sl_so_balance_item.tenant_id = A.tenant_id AND
        A.do_id = vDoId AND
        sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv > 0;

    UPDATE sl_so_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
    FROM sl_do_item A
    WHERE sl_so_balance_item.so_item_id = A.ref_id AND
        sl_so_balance_item.tenant_id = A.tenant_id AND
        A.do_id = vDoId AND
        sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv <= 0;


    SELECT COUNT(1) INTO vUnfinishedItem
    FROM sl_so_balance_item A, sl_so_item B
    WHERE A.so_item_id = B.so_item_id AND
        B.so_id = vSoId AND
        A.status_item = vStatusRelease;

    IF vUnfinishedItem = 0 THEN
        UPDATE sl_so SET status_doc = vStatusFinal
        WHERE so_id = vSoId;
    END IF;

    /*
     * @author TKP, 9 Jun 2016
     * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
     * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
     */

    SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseId
    FROM sl_do A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.do_id = vDoId;

    IF (vOuId <> vOuWarehouseId) THEN
        SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
        vOuStructureJournalItem := result.ou_structure;
    ELSE
        vOuStructureJournalItem := ROW(-99, -99, -99);
    END IF;


    /*
     * Create otomatis dokumen Sales Invoice
     * Jika SO flag dropship Yes, SO dibuatkan Goods Receive, dan customer nya flg PKP No
     * vSoId
     */

    SELECT flag_dropship INTO vFlagDropship
    FROM sl_so_ext
    WHERE so_id = vSoId
    AND tenant_id = pTenantId;


    SELECT A.receive_goods_id INTO vRgId
    FROM pu_receive_goods A
    INNER JOIN pu_po B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.po_id
    INNER JOIN sl_so C ON B.ref_doc_type_id = C.doc_type_id AND B.ref_id = C.so_id
    WHERE C.so_id = vSoId;

    IF NOT FOUND THEN
          vRgId = vEmptyId;
    END IF;


    SELECT partner_ship_to_id, partner_bill_to_id INTO vPartnerShipId, vPartnerBillId
    FROM sl_so
    WHERE so_id = vSoId;

    /*
    --Generate otomatis dokumen Sales Invoice
    SELECT flg_pkp INTO vFlgPkp
    FROM m_partner_npwp
    WHERE partner_id = vPartnerBillId
    AND tenant_id = pTenantId;

    IF NOT FOUND THEN
          vFlgPkp = vNo;
    END IF;

    --Create Sales Invoice dengan kondisi flag dropship Yes, sudah dibuat Receive Goods, dan flg pkp partner bill to nya No
    IF (vFlagDropship = vYes AND vRgId <> vEmptyId AND vFlgPkp = vNo) THEN

            --Get receive goods id and doc no
            SELECT CAST(D.receive_goods_id AS character varying)|| '_' ||D.doc_no INTO vProcessNoRG
            FROM sl_do A
            INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id AND A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.so_id
            INNER JOIN pu_po C ON C.tenant_id = B.tenant_id AND C.ou_id = B.ou_id AND C.ref_doc_type_id = B.doc_type_id AND C.ref_id = B.so_id
            INNER JOIN pu_receive_goods D ON D.tenant_id = C.tenant_id AND D.ou_id = C.ou_id AND D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.po_id
            WHERE A.do_id = vDoId
            AND A.tenant_id = pTenantId;



            --Get Rreceive Goods process No
            SELECT A.process_message_id INTO vProcessIdForSI
            FROM t_process_message A
            WHERE A.tenant_id = pTenantId AND
                A.process_name = 'pu_submit_receive_goods' AND
                A.process_no = vProcessNoRG;

            SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdSI
            FROM t_process_parameter A
            WHERE A.process_message_id = vProcessIdForSI
            AND A.process_parameter_key = 'autonumIdSI';

            -- Ambil doc no SI baru
            SELECT A.process_parameter_value INTO vDocNoNewSI
            FROM t_process_parameter A
            WHERE A.process_message_id = vProcessIdForSI
            AND A.process_parameter_key = 'autonumSI';


            -- Get role id, flg user role awe submit RG
            SELECT role_id, flg_user_role INTO vRoleId, vFlgUserRole
            FROM awe_historydoc
            WHERE scheme = vSchemeRG
            AND doc_id = vRgId
            AND activity = 'SUBMIT';


        PERFORM sl_automatic_create_sales_invoice(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vDoId, vAutonumIdSI, vDocNoNewSI, vRoleId, vFlgUserRole);

    END IF;
    */




    /*
     * 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_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
    FROM sl_do A
    WHERE A.do_id = vDoId;

    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.do_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_ship_to_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_do A, sl_so B
    WHERE A.do_id = vDoId AND
        A.ref_doc_type_id = B.doc_type_id AND
        A.ref_id = B.so_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)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, B.do_item_id,
        A.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_dlv_int, B.base_uom_id,
        0, A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM sl_do A, sl_do_item B, sl_so_item C
    WHERE A.do_id = vDoId AND
        A.do_id = B.do_id AND
        B.ref_id = C.so_item_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,
    ou_branch_id, ou_sub_bu_id)
    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,
        (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId;

    /* untuk perubahan lama
    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)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
        0, A.journal_date, A.type_rate,
        1, 1, 'COGS', vEmptyValue,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
    */

    --Deo 26 Februari 2021
    --Untuk disamakan dengan SASA
    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,
    ou_branch_id, ou_sub_bu_id)
    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, vSignDebit, A.flg_source_coa, A.activity_gl_id,
        vCoaIdGIT,A.curr_code , A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, 'PRODUCT_STOCK_IN_TRANSIT', A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/