Function Submit DO Receipt Stagging

-- Function: stgjenindo.in_submit_do_receipt(bigint, character varying, character varying)

-- DROP FUNCTION stgjenindo.in_submit_do_receipt(bigint, character varying, character varying);

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

    vProcessId              bigint;
    vDoReceiptId                    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);
    vStatusVoid             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;
    vDoId                   bigint;
    vUnfinishedItem         bigint;
    vParentOuId             bigint;
    vJournalTrxId           bigint;
    vJournalType            character varying(20);
    vDocNoSI                character varying(50);
    vOuId                   bigint;
    vOuWarehouseId          bigint;
    vAutoNumId              bigint;

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

    vDoReceiptDocTypeId     bigint;
    vRoundingModeNonTax     character varying(5);
    vDoDocTypeId            bigint;
    vSoDocTypeId            bigint;
    vSlsInvTempId           bigint;
    vSlsInvTempDocTypeId    bigint;
    vNo                     character varying(5);
    vYes                    character varying(5);
BEGIN

    vFlagInvoice := 'N';
    vEmptyId := -99;
    vStatusRelease := 'R';
    vStatusVoid := 'V';
    vStatusDraft := 'D';
    vStatusFinal := 'F';
    vEmptyValue := ' ';
    vProductStatus := 'GOOD';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vUnfinishedItem := 0;
    vNo := 'N';
    vYes := 'Y';

    vDoReceiptDocTypeId = 525;
    vDoDocTypeId := 311;
    vSoDocTypeId := 301;
    vSlsInvTempDocTypeId := 361;

    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 = 'in_submit_do_receipt' AND
        A.process_no = pProcessNo;

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

    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 in_do_receipt
 * 2. add sl_log_so_balance_item
 * 3. add sl_so_balance_invoice
 * 4. add sl_so_balance_invoice_tax
 * 5. update sl_so_balance_invoice untuk Do Id
 * 6. update sl_so_balance_invoice_tax untuk Do Id
 * 7. void sales invoice temporer untuk So Id
 * 8. delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
 * 9. add in_log_product_balance_stock
 * 10. update in_product_balance_stock
 * 11. update in_balance_do_item
 * 12. update sl_so_balance_item - status dan qty
 * 13. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
 * 14. add gl_journal_trx
 * 15. add gl_journal_trx_item
 * 16. 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 in_do_receipt A
    WHERE A.do_receipt_id = vDoReceiptId INTO result;

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

    SELECT ref_id INTO vSoId
    FROM sl_do
    WHERE do_id = vDoId;

    /*
     * update status doc in_do_receipt menjadi Release
     */
    UPDATE in_do_receipt SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE do_receipt_id = vDoReceiptId;

    /*
     * buat data log sl_log_so_balance_item
     */
    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_receipt_id, B.do_receipt_item_id,
        B.qty_return * D.qty_so / D.qty_int, D.so_uom_id, B.qty_return, B.uom_id, B.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D
    WHERE A.do_receipt_id = vDoReceiptId AND
          A.do_receipt_id = B.do_receipt_id AND
          B.ref_id = C.do_item_id AND
          C.so_item_id = D.so_item_id AND
          B.qty_return > 0;

    /*
     * buat data sl_so_balance_Invoice
     * supaya dapat ditarik saat buat sales invoice
     */
    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,
        do_receipt_item_id, gross_sell_price_so, flg_tax_amount, tax_id, tax_percentage, discount_percentage, discount_amount)

    SELECT A.tenant_id, A.ou_id, G.partner_id, C.so_id,
            A.ref_doc_type_id, A.ref_id, G.ref_doc_no, G.ref_doc_date, B.ref_id, -1 * B.qty_return * D.qty_so / D.qty_int, D.so_uom_id,
            D.curr_code, G.price_so,
            -1 * f_get_amount_before_tax_and_disc((B.qty_return * D.qty_so / D.qty_int) * G.gross_sell_price_so, (B.qty_return * D.qty_so / D.qty_int) * G.discount_amount, G.flg_tax_amount, G.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax),
            vFlagInvoice, vEmptyId,
            -1 * (G.discount_amount * (B.qty_return * D.qty_so / D.qty_int)),
            0, 0, 0,
            0, vDatetime, vUserId, vDatetime, vUserId,
            B.do_receipt_item_id, G.gross_sell_price_so, G.flg_tax_amount, G.tax_id, G.tax_percentage, G.discount_percentage, G.discount_amount

    FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D, sl_so_balance_invoice G
    WHERE A.do_receipt_id = vDoReceiptId AND
          A.do_receipt_id = B.do_receipt_id AND
          B.ref_id = C.do_item_id AND
          C.so_item_id = D.so_item_id AND
          G.ref_doc_type_id = A.ref_doc_type_id AND
          G.ref_id = A.ref_id AND
          G.ref_item_id = B.ref_id AND
          B.qty_return > 0 AND G.do_receipt_item_id = -99;

    /*
     * buat data sl_so_balance_invoice_tax
     * supaya dapat ditarik saat buat sales invoice
     */
    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,
    do_receipt_item_id)
    SELECT A.tenant_id, A.ou_id, G.partner_id, C.so_id,
        A.ref_doc_type_id, A.ref_id, B.ref_id, G.tax_id, E.flg_amount,
        D.tax_percentage, D.curr_code,
        -1 * f_get_amount_before_tax_and_disc((B.qty_return * D.qty_so / D.qty_int) * G.gross_sell_price_so, (B.qty_return * D.qty_so / D.qty_int) * G.discount_amount, G.flg_tax_amount, G.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax),
        -1 * f_tax_rounding(A.tenant_id, f_get_amount_before_tax((B.qty_return * D.qty_so / D.qty_int) * (G.gross_sell_price_so - G.discount_amount), G.flg_tax_amount, G.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax), G.tax_percentage),
        vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId,
        B.do_receipt_item_id
    FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D, m_tax E, sl_so F, sl_so_balance_invoice G
    WHERE A.do_receipt_id = vDoReceiptId AND
          A.do_receipt_id = B.do_receipt_id AND
          B.ref_id = C.do_item_id AND
          C.so_item_id = D.so_item_id AND
          D.tax_id = E.tax_id AND
          D.so_id = F.so_id AND
          B.qty_return > 0 AND

          G.ref_doc_type_id = A.ref_doc_type_id AND
          G.ref_id = A.ref_id AND
          G.ref_item_id = B.ref_id AND
          G.do_receipt_item_id = -99;
          /*
          G.do_id = vDoId AND
          G.do_id = H.do_id AND
          H.ref_id = D.so_item_id AND
          D.tax_id = E.tax_id AND
          D.so_id = F.so_id;
          */




    /*
     * update sl_so_balance_invoice, dan sl_so_balance_invoice_tax
     * yang sudah dibuat oleh sales invoice temporer
     */
    UPDATE sl_so_balance_invoice SET flg_invoice = vFlagInvoice, flg_invoice_temp = vFlagInvoice, invoice_id = vEmptyId
    WHERE ref_id = vDoId AND
        ref_doc_type_id = vDoDocTypeId;

    UPDATE sl_so_balance_invoice_tax SET flg_invoice = vFlagInvoice, invoice_id = vEmptyId
    WHERE ref_id = vDoId AND
        ref_doc_type_id = vDoDocTypeId;

    /*
     * void data sales invoice temporer yang menggunakan DO Item id yang sama dng Do Receipt :
     * 1. cari invoice temp id yang memiliki data DO Item yang sama dengan DO Receipt
     * 2. update status doc invoice temp sesuai id yang diperoleh di langkah ke 1
     */
    SELECT A.invoice_temp_id INTO vSlsInvTempId
    FROM sl_invoice_temp_item A, in_do_receipt_item B, in_do_receipt C, sl_invoice_temp D
    WHERE A.ref_doc_type_id = B.ref_doc_type_id AND
        A.ref_id = vDoId AND
        A.ref_item_id = B.ref_id AND
        B.do_receipt_id = C.do_receipt_id AND
        C.ref_id = A.ref_id AND
        B.do_receipt_id = vDoReceiptId AND
        A.invoice_temp_id = D.invoice_temp_id AND
        D.status_doc <> vStatusVoid;

    UPDATE sl_invoice_temp SET status_doc = vStatusVoid, update_datetime = vDatetime, update_user_id = vUserId
    WHERE invoice_temp_id = vSlsInvTempId;

    /*
     * advance invoice  harus diupdate juga supaya bisa dipakai lagi
     */
    UPDATE sl_so_balance_advance_invoice B SET flg_invoice = vFlagInvoice, flg_invoice_temp = vFlagInvoice, invoice_id = vEmptyId
    WHERE B.so_id = vSoId
    AND EXISTS (
        SELECT 1 FROM sl_invoice_temp_advance A
        WHERE A.ref_id = B.ref_id
        AND A.ref_doc_type_id = B.ref_doc_type_id
        AND A.invoice_temp_id = vSlsInvTempId
    );

    /*
     * nomor faktur pajak boleh digunakan kembali untuk temp sales invoice yang di void gara2 DO Receipt
     *
     */
    UPDATE m_gen_tax_number A
    SET invoice_id = vEmptyId, invoice_doc_type_id = vEmptyId, invoice_doc_no = vEmptyValue, invoice_doc_date = vEmptyValue
    FROM sl_invoice_temp B
    WHERE A.invoice_doc_no = B.inv_doc_no
    AND A.invoice_doc_date = B.inv_doc_date
    AND A.invoice_doc_type_id = B.doc_type_id
    AND A.invoice_id = vSlsInvTempId
    AND B.invoice_temp_id = vSlsInvTempId;

    /*
     * Ambil nomor dokumen SI untuk dilakukan pengecekan SI masiih ada/tidak
     */
     SELECT A.doc_no INTO vDocNoSI
     FROM sl_invoice A, sl_invoice_temp B
     WHERE B.invoice_temp_id = vSlsInvTempId
        AND A.doc_no = B.inv_doc_no;

     IF vDocNoSI IS NULL THEN

        /*
         * nomor dokumen SI boleh digunakan kembali untuk temp sales invoice yang di void gara2 DO Receipt
         *
         */
        UPDATE autonum_generated A
        SET flg_unused = vYes
        FROM sl_invoice_temp B
        WHERE A.value_auto_num = B.inv_doc_no
        AND B.invoice_temp_id = vSlsInvTempId;

        /*
         * ambil id autonum
         */
         SELECT A.autonum_generated_id INTO vAutoNumId
         FROM autonum_generated A, sl_invoice_temp B
         WHERE A.value_auto_num = B.inv_doc_no
         AND B.invoice_temp_id = vSlsInvTempId;

        /*
         * delete autonum_ref_mapping berdasarkan variable autonumId yg sudah diambil sebelumnya
         */
         IF vAutoNumId IS NOT NULL THEN
         DELETE FROM autonum_ref_mapping WHERE autonum_generated_id = vAutoNumId;
         END IF;

     END IF;

    /*
     * delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
     */

    DELETE FROM fi_invoice_tax_ar_balance A
    WHERE A.tenant_id = pTenantId AND
    EXISTS (
        SELECT 1 FROM fi_invoice_ar_balance B
        WHERE A.tenant_id = B.tenant_id
        AND A.invoice_ar_balance_id = B.invoice_ar_balance_id
        AND B.doc_type_id = vSlsInvTempDocTypeId
        AND B.invoice_ar_id = vSlsInvTempId
    );

    DELETE FROM fi_invoice_ar_balance A
    WHERE A.tenant_id = pTenantId AND
          A.doc_type_id = vSlsInvTempDocTypeId AND
          A.invoice_ar_id = vSlsInvTempId;


    /*
     * buat data log product balance stock
     * ref item id = do_receipt_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, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
        C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id, SUM(C.qty_return),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C, sl_do D, m_warehouse_ou E
    WHERE A.do_receipt_id = vDoReceiptId AND
        A.do_receipt_id = B.do_receipt_id AND
        B.do_receipt_item_id = C.do_receipt_item_id AND
        A.ref_id = D.do_id AND
        A.warehouse_id = E.warehouse_id
    GROUP BY A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
            C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id;

    /*
     * update in_product_balance_stock
     * mod by Didit, 30 Des 2016
     * perbaiki jika ada 1 produk yang sama (product dan product balance) tapi ada lebih dari 1 record di tabel itemnya akan menjadi salah
     */
    WITH tt_in_product_balance_summary AS (
        SELECT  C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status, SUM(C.qty_return) AS qty_return
        FROM    in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C
        WHERE   A.do_receipt_id = vDoReceiptId
                AND A.do_receipt_id = B.do_receipt_id
                AND B.do_receipt_item_id = C.do_receipt_item_id
                AND C.qty_return > 0
        GROUP BY C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status
    )
    UPDATE in_product_balance_stock SET qty = qty + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId, version = version + 1
    FROM tt_in_product_balance_summary A
    WHERE in_product_balance_stock.product_id = A.product_id AND
        in_product_balance_stock.tenant_id = A.tenant_id AND
        in_product_balance_stock.warehouse_id = A.warehouse_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = A.product_status;

    /*
     * update data balance do item sehubungan dengan product yang dikembalian/tidak diterima oleh customer,
     * sehingga saat akan membuat return note, hanya barang yang memang diterima oleh customer
     */
    UPDATE in_balance_do_item SET qty_dlv_int = qty_dlv_int - A.qty_return,
                                qty_dlv = in_balance_do_item.qty_dlv - (A.qty_return * in_balance_do_item.qty_dlv / in_balance_do_item.qty_dlv_int),
                                status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    FROM in_do_receipt_item A
    WHERE A.do_receipt_id = vDoReceiptId AND
        in_balance_do_item.do_item_id = A.ref_id AND
        A.qty_return > 0;

    UPDATE in_balance_do_item
    SET status_item = vStatusRelease,
        update_datetime = vDatetime,
        update_user_id = vUserId
    FROM in_do_receipt A
    WHERE A.do_receipt_id = vDoReceiptId AND
        in_balance_do_item.do_id = A.ref_id AND
        A.ref_doc_type_id = vDoDocTypeId;

    /*
     * update status SO seperti semula
     */
    UPDATE sl_so
    SET status_doc = flg_old_status,
        flg_old_status = vEmptyValue,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = version + 1
    WHERE so_id = vSoId;

    /*
     * update flg invoice so balance inv
     */
    UPDATE sl_so_balance_invoice
    SET flg_invoice = vNo,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = version + 1
    WHERE so_id = vSoId
        AND ref_id = vDoId;

    /*
     * update status so balance item
     */
    UPDATE sl_so_balance_item B
    SET status_item = B.flg_old_status,
        flg_old_status = vEmptyValue,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = B.version + 1
    FROM sl_so A
    INNER JOIN sl_so_item C ON A.so_id = C.so_id
    INNER JOIN sl_do_item D ON D.ref_id = C.so_item_id
    WHERE B.so_item_id = C.so_item_id
        AND A.so_id = vSoId
        AND D.do_id = vDoId;

    /*
     * status item SO akan berubah menjadi Release, karena ada nya barang yang dikembalikan oleh customer
     */
    UPDATE sl_so_balance_item SET qty_dlv = sl_so_balance_item.qty_dlv - (A.qty_return * sl_so_balance_item.qty_so / sl_so_balance_item.qty_so_int),
                                qty_dlv_int = sl_so_balance_item.qty_dlv_int - A.qty_return,
                                status_item = vStatusRelease
    FROM in_do_receipt_item A, in_balance_do_item B
    WHERE A.do_receipt_id = vDoReceiptId AND
        A.ref_id = B.do_item_id AND
        sl_so_balance_item.so_item_id = B.so_item_id AND
        A.qty_return > 0;

    UPDATE sl_so SET status_doc = vStatusRelease
    WHERE so_id = vSoId;

    /*
     * @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 in_do_receipt A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.do_receipt_id = vDoReceiptId;

    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;

    /*
     * 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 in_do_receipt A
    WHERE A.do_receipt_id = vDoReceiptId;

    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_receipt_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, B.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, C.curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_do_receipt A, sl_do B, sl_so C
    WHERE A.do_receipt_id = vDoReceiptId AND
        A.ref_id = B.do_id AND
        B.ref_id = C.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_receipt_item_id,
        C.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, 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_return, B.uom_id,
        0, A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM in_do_receipt A, in_do_receipt_item B, sl_do C
    WHERE A.do_receipt_id = vDoReceiptId AND
        A.do_receipt_id = B.do_receipt_id AND
        A.ref_id = C.do_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;

    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, vSignCredit, 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;

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