in_submit_return_note_STAGING_20171229

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

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

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

    vProcessId      bigint;
    vReturnNoteId   bigint;
    vUserId         bigint;
    vDatetime       character varying(14);
    vFlagInvoice    character varying(1);
    vEmptyId        bigint;
    vStatusRelease  character varying(1);
    vEmptyValue     character varying(1);
    vStatusFinal    character varying(1);
    vStatusDraft    character varying(1);
    vFlagYes            character varying(1);
    vFlagNo             character varying(1);
    vEmptyString        character varying(1);
    vJournalType        character varying(20);
    vSignDebit          character varying(1);
    vSignCredit         character varying(1);
    vTypeRate           character varying(3);
    vProductCOA         character varying(10);
    vSystemCOA          character varying(10);
    vParentOuId         bigint;
    vJournalTrxId       bigint;
    vNol                numeric;
    vOuId               bigint;
    vOuWarehouseId      bigint;
    vDocTypeId          bigint;

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

    vReturnNoteDocTypeId bigint;
    vRoundingModeNonTax character varying(5);

    vTaxPercentage      numeric;
    vTaxId          bigint;
    vPartnerIdMagento   bigint;
    vReturnNotePartnerId    bigint;
    vReturnNoteDocDate  character varying(8);
    vPkpStartDate       character varying(8);

    vSalesOrderDocTypeId    bigint;
    vClaimNoteId            bigint;
    vClaimNoteDocTypeId     bigint;
    vClaimNoteDocNo         character varying(30);
    vClaimNoteNumId         bigint;
    vEmpty                  character varying(1);
    vEmptyIdString          character varying;
    vRemarkPrefix           text;
    vWorkflowApproved       character varying;
    vReceiveGoodsDocTypeId  bigint;

    vRoleIdForGenerateDoc       bigint := -99;
    vUserIdForGenerateDoc       bigint := -99;
    vClaimNoteFlowId            bigint := -99;
    vClaimNoteScheme            character varying := 'EA02';
    vFlgUserRole                character varying := 'R';
    vParamUserIdForGenerateDoc  character varying := 'user.id.for.return.note.for.finance';
    vParamRoleIdForGenerateDoc  character varying := 'role.id.for.return.note.for.finance';

BEGIN

    vFlagInvoice := 'N';
    vEmptyId := -99;
    vEmptyValue := ' ';
    vStatusRelease := 'R';
    vStatusFinal := 'F';
    vStatusDraft := 'D';
    vFlagYes := 'Y';
    vFlagNo := 'N';
    vEmptyString := ' ';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vNol := 0;

    vDocTypeId := 311;
    vReturnNoteDocTypeId := 502;
    vSalesOrderDocTypeId := 301;
    vClaimNoteDocTypeId := 511;
    vEmpty := '';
    vRemarkPrefix := '[AUTO GENERATED] ';
    vWorkflowApproved := 'APPROVED';
    vReceiveGoodsDocTypeId := 111;
    vEmptyIdString = '-99';
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    -- Menampung nilai tax_id dan tax_percentage dari table m_tax ke variable vTaxId dan vTaxPercentage
    SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId and tax_code = 'PPn';

    -- Menampung nilai partner_id magento dari sysconfig ke variable vPartnerIdMagento
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'partner.id.e.commerce')::bigint INTO vPartnerIdMagento;

    -- Menampung nilai pkp start date dari sysconfig ke variable vPkpStartDate
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'pkp.start.date') INTO vPkpStartDate;


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

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

    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';

    -- Menampung nilai doc_date return note dan partner_id return note ke dalam variable vReturnNoteDocDate dan vReturnNotePartnerId
    SELECT A.doc_date, B.partner_bill_to_id  INTO vReturnNoteDocDate, vReturnNotePartnerId
    FROM in_inventory A
    INNER JOIN sl_so B ON a.ref_doc_type_id = 301 AND A.ref_id = B.so_id
    WHERE A.inventory_id = vReturnNoteId;

    DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;

    /*
     * 1.update status doc in_inventory
     * 2.update data ke in_product_balance_stock / insert data ke in_product-balance_stock
     * 3.insert data ke in_log_product_balance_stock
     * 4.update data sl_so_balance_item
     * 5.insert data sl_log_so_balance_item
     * 6.insert data sl_so_balance_invoice
     * 7.insert data sl_so_balance_invoice_tax
     * 8.update data in_balance_do_item
     * 9.add trx 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 = vReturnNoteId 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 = vReturnNoteId;

    /*
     * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
     */
    INSERT INTO tt_in_product_balance_summary_stock
            (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
    SELECT  pSessionId, A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
            B.base_uom_id, SUM(B.qty_realization)
    FROM    in_inventory A, in_inventory_item B
    WHERE   A.inventory_id = B.inventory_id
    AND     A.inventory_id = vReturnNoteId
    GROUP BY A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;

    /*
     * update product_balance_stock
     */
    UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId,
        version = version + 1
    FROM tt_in_product_balance_summary_stock A
    WHERE A.session_id = pSessionId AND
        A.inventory_id = vReturnNoteId 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_id = A.product_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = A.product_status;

    /*
     * insert data in_product_balance_stock
     */
    INSERT INTO in_product_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
            0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = B.inventory_id AND
        A.inventory_id = vReturnNoteId AND
        NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
                    WHERE C.tenant_id = A.tenant_id AND
                        C.warehouse_id = A.warehouse_from_id AND
                        C.product_id = B.product_id AND
                        C.product_balance_id = B.product_balance_id AND
                        C.product_status = B.product_status)
    GROUP BY A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;

    /*
     * insert data in_log_product_balance_stock
     */
    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, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = vReturnNoteId AND
        A.inventory_id = B.inventory_id
    GROUP BY A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;


    /*
        Added Julius Tanuwijaya, 16 Nov 2016
    1. buat sysConfig -> pkp.start.date : diisi dengan 20161001
    2. Ambil nilai syconfig tampung ke variabel vPkpStartDate
    3. Ambil doc_date dari return note yang disubmit tampung ke variabel vReturnNoteDate
    4. Ambil dari m_tax untuk tenant id bersangkutan dgn tax code PPn, tampung vUsedTaxId, dan vUsedTaxPercentage
    5. Ambil partner_id dari dokumen return note tampung ke vReturnNotePartnerId
    6. Ambil partner_id eCommerce (MAGENTO) dari sysconfig tampung ke vMagentoPartnerId
    7. IF vReturnNotePartnerId = vMagentoPartnerId AND vReturnNoteDate >= vPkpStartDate THEN
         --custom code
         -- INSERT ke tt_in_so_balance_item
                flg_tax_amount selalu Yes (Y)
                tax_id pakai vUsedTaxId
                tax_percentage pakai vUsedTaxPercentage
       ELSE

                 -- default code insert ke tt_in_so_balance_item
       END IF
    */
    /*
     * insert data temporer tt_in_so_balance_item
     */
    IF vReturnNotePartnerId = vPartnerIdMagento AND vReturnNoteDocDate >= vPkpStartDate THEN
     -- INSERT ke tt_in_so_balance_item
     -- flg_tax_amount selalu Yes (Y)
     -- tax_id pakai vUsedTaxId
     -- tax_percentage pakai vUsedTaxPercentage
        INSERT INTO tt_in_so_balance_item
        (session_id, tenant_id, ou_id, doc_type_id,
        doc_no, doc_date, inventory_id, partner_id,
        inventory_item_id, so_id, do_id, do_item_id,
        qty_return, base_uom_id, remark,
        so_item_id, curr_code, price,
        flg_tax_amount, qty_so, qty_int_so,
        so_uom_id, tax_id, tax_percentage)
        SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                SUM(B.qty_realization), B.base_uom_id, A.remark,
                D.so_item_id, D.curr_code, D.gross_sell_price,
                vFlagYes, D.qty_so, D.qty_int,
                D.so_uom_id, vTaxId, vTaxPercentage
        FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
        WHERE A.inventory_id = vReturnNoteId AND
              A.inventory_id = B.inventory_id AND
              B.ref_item_id = C.do_item_id AND
              C.so_item_id = D.so_item_id AND
              D.so_id = E.so_id
        GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id ;

    ELSE
        -- default code insert ke tt_in_so_balance_item
        INSERT INTO tt_in_so_balance_item
        (session_id, tenant_id, ou_id, doc_type_id,
        doc_no, doc_date, inventory_id, partner_id,
        inventory_item_id, so_id, do_id, do_item_id,
        qty_return, base_uom_id, remark,
        so_item_id, curr_code, price,
        flg_tax_amount, qty_so, qty_int_so,
        so_uom_id, tax_id, tax_percentage)
        SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                SUM(B.qty_realization), B.base_uom_id, A.remark,
                D.so_item_id, D.curr_code, D.gross_sell_price,
                D.flg_tax_amount, D.qty_so, D.qty_int,
                D.so_uom_id, D.tax_id, D.tax_percentage
        FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
        WHERE A.inventory_id = vReturnNoteId AND
              A.inventory_id = B.inventory_id AND
              B.ref_item_id = C.do_item_id AND
              C.so_item_id = D.so_item_id AND
              D.so_id = E.so_id
        GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id ;
    END IF;


    /*
     * update qty return di data sl_so_balance_item
     */
    UPDATE sl_so_balance_item SET qty_return =  sl_so_balance_item.qty_return + ((A.qty_return * A.qty_so) / A.qty_int_so), qty_return_int = sl_so_balance_item.qty_return_int + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        sl_so_balance_item.so_item_id = A.so_item_id;

    /*
     * buat data 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, A.so_id, A.so_item_id, A.doc_type_id, A.inventory_id, A.inventory_item_id,
        ((A.qty_return * A.qty_so) / A.qty_int_so ), A.so_uom_id, A.qty_return, A.base_uom_id, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId;


    /*
     * buat data sl_so_balance_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)
    SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
        A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, SUM((A.qty_return * A.qty_so) / A.qty_int_so), A.so_uom_id,
        A.curr_code, f_get_price_before_tax_and_disc(A.price, ((B.regular_disc_amount / B.qty_dlv_so)  + (B.promo_disc_amount / B.qty_dlv_so)), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
        SUM(f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so * A.price) / A.qty_int_so, (B.regular_disc_amount + B.promo_disc_amount) * (A.qty_return * A.qty_so) / (A.qty_int_so / B.qty_dlv_so), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
        vFlagInvoice, vEmptyId,
        B.regular_disc_amount * SUM((A.qty_return * A.qty_so) / A.qty_int_so) / B.qty_dlv_so, 0, 0, 0,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_so_balance_item A
    INNER JOIN sl_so_balance_invoice B ON B.ref_id = A.do_id AND B.ref_item_id = A.do_item_id AND B.do_receipt_item_id = vEmptyId
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, A.so_uom_id, A.curr_code, A.price, B.regular_disc_amount, B.qty_dlv_so, B.promo_disc_amount, A.flg_tax_amount, A.tax_percentage;

    /*
     * buat data sl_so_balance_invoice_tax
     */
    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, A.partner_id, A.so_id,
        A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, D.flg_amount,
        A.tax_percentage, A.curr_code,
        SUM(f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so * A.price) / A.qty_int_so, (A.qty_return * A.qty_so * B.discount_amount) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
        f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_so * (A.price - B.discount_amount)) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage),
        vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_so_balance_item A, m_tax D, sl_so_item B
    WHERE A.session_id = pSessionId AND
          A.tax_id = D.tax_id AND
          A.so_item_id = B.so_item_id
    GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id,
        D.flg_amount, A.tax_percentage, A.curr_code;


    UPDATE in_balance_do_item SET status_item = vStatusFinal
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        in_balance_do_item.do_item_id = A.do_item_id AND
        in_balance_do_item.qty_dlv -in_balance_do_item.qty_return <= 0;

    UPDATE in_balance_do_item SET status_item = vStatusRelease
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        in_balance_do_item.do_item_id = A.do_item_id AND
        in_balance_do_item.qty_dlv -in_balance_do_item.qty_return > 0;

    /*
     * @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_from_id, B.ou_id INTO vOuId, vOuWarehouseId
    FROM in_inventory A
    INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
    WHERE A.inventory_id = vReturnNoteId;

    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;

    /*
     * journal return note
     * Debit Inventory = dari nilai COGS
     * Credit HPP
     */
    /*
     * 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 = vReturnNoteId;

    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 = vReturnNoteId;

    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.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), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = vReturnNoteId AND
        A.inventory_id = B.inventory_id;


/* NK, 1 Feb 2014, journal HPP tidak perlu break down sampai ke product
    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.inventory_item_id,
        A.partner_id, B.product_id, 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'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'HPP', B.remark
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = vReturnNoteId 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, ou_id, sub_ou_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, B.journal_trx_id, 1,
        A.doc_type_id, A.inventory_item_id, B.ou_id, B.sub_ou_id,
        vEmptyId, C.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, A.qty_return, A.base_uom_id,
        f_get_amount_before_tax((A.qty_return * A.qty_so * A.price) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage,0),
        A.doc_date, vTypeRate,
        1, 1, 'HPP', A.remark
    FROM tt_in_so_balance_item A, gl_journal_trx B, in_inventory_item C
    WHERE A.session_id = pSessionId AND
        B.journal_trx_id = vJournalTrxId AND
        A.inventory_item_id = C.inventory_item_id;

    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),
        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
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'HPP';

*/
    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 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)
    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;

    /*
     * 1.TODO: update ke pu_po_balance_balance_sold_manual ubah status item I setelah menjalankan function submit return note
     *         pastikan pu_po_balance_balance_sold_manual do item tersebut status item berubah menjadi R
     * 2.TODO update qty return di table pu_po_balance_balance_sold_manual menjadi qty_return + qty qty_realization di table in_inventory_item
     *  berdasarkan:
     *  1. in_inventory_item.ref id = pu_po_balance_balance_sold_manual.do id
     *  2. in_inventory_item.ref doc type id = pu_po_balance_balance_sold_manual.doc_type_id (DO) 311
     *  3. in_inventory_item.ref item id = pu_po_balance_balance_sold_manual.do_item_id
     *  4. in_inventory_item.doc_type_id = doc_type_id_return note (RN) 502
     *  5. in_inventory.in_inventory_id = inventory_id
     */

   UPDATE pu_po_balance_item_consignment_sold_manual Z
   SET status_item = vStatusRelease
   FROM in_inventory_item Y
   JOIN in_inventory X ON Y.inventory_id = X.inventory_id
   WHERE X.inventory_id = vReturnNoteId
          AND X.doc_type_id = vReturnNoteDocTypeId
          AND Y.ref_doc_type_id = vDocTypeId
          AND Y.ref_id = Z.do_id
          AND Y.ref_item_id = Z.do_item_id;

    UPDATE pu_po_balance_item_consignment_sold_manual Z
        SET qty_return = Z.qty_return + Y.qty_realization
    FROM in_inventory_item Y
    JOIN in_inventory X ON Y.inventory_id = X.inventory_id
    WHERE X.inventory_id = vReturnNoteId
        AND X.doc_type_id = vReturnNoteDocTypeId
        AND Y.ref_doc_type_id = vDocTypeId
        AND Y.ref_id = Z.do_id
        AND Y.ref_item_id = Z.do_item_id;

    --Jika Return Note adalah for Finance, buat data Claim Note
    IF EXISTS (
        SELECT (1)
        FROM in_return_note_for_finance A
        WHERE A.inventory_id = vReturnNoteId
            AND A.flg_for_finance = vFlagYes
    ) THEN

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

        SELECT NEXTVAL('in_inventory_seq') INTO vClaimNoteId;

        SELECT CAST(A.process_parameter_value AS character varying(30)) INTO vClaimNoteDocNo FROM t_process_parameter A
        WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'CLAIM_NOTE_NO';

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


        INSERT INTO in_inventory(
            inventory_id, tenant_id, doc_type_id, doc_no, doc_date, ou_from_id,
            ou_to_id, partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id,
            ref_id, remark, warehouse_from_id, warehouse_to_id, transfer_date,
            receive_date, activity_gl_id, ou_rc_id, no_vehicle, flg_delivery,
            delivery_code, status_doc, workflow_status, version, create_datetime,
            create_user_id, update_datetime, update_user_id, segment_id,
            group_product_id)
        SELECT vClaimNoteId, A.tenant_id, vClaimNoteDocTypeId, vClaimNoteDocNo, A.doc_date, C.ou_id,
            C.ou_id, C.partner_id, vEmpty, vEmpty, C.doc_type_id,
            C.po_id, vRemarkPrefix || A.remark, A.warehouse_from_id, A.warehouse_to_id, A.transfer_date,
            A.receive_date, vEmptyId, vEmptyId, A.no_vehicle, A.flg_delivery,
            A.delivery_code, vStatusRelease, vWorkflowApproved, 0, vDatetime,
            vUserId, vDatetime, vUserId, vEmptyId,
            vEmptyId
        FROM in_inventory A
        INNER JOIN in_return_note_for_finance B
            ON A.inventory_id = B.inventory_id
        INNER JOIN pu_po C
            ON B.po_id = C.po_id
        WHERE A.inventory_id = vReturnNoteId;

        INSERT INTO in_inventory_item(
            tenant_id, inventory_id, line_no, product_id,
            product_balance_id, product_status, serial_number, product_expired_date,
            product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
            curr_code, amount, base_uom_id, qty_request, qty_realization,
            remark, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
        SELECT A.tenant_id, vClaimNoteId, D.line_no, E.product_id,
            E.product_balance_id, F.product_status, G.serial_number, G.product_expired_date,
            G.product_year_made, G.lot_number, vReceiveGoodsDocTypeId, C.receive_goods_id, C.receive_goods_item_id,
            vEmptyIdString, 0, C.base_uom_id,
            CASE WHEN H.qty < E.qty THEN
                H.qty
            ELSE
                E.qty
            END,
            A.qty_return,
            D.remark, 0, vDatetime, vUserId, vDatetime,
            vUserId
        FROM tt_in_so_balance_item A
        INNER JOIN pu_po_item B
            ON A.so_item_id = B.ref_id
        INNER JOIN in_balance_receive_goods_item C
            ON B.po_item_id = C.po_item_id
        INNER JOIN in_inventory_item D
            ON A.inventory_item_id = D.inventory_item_id
        INNER JOIN in_log_product_balance_stock E
            ON C.tenant_id = E.tenant_id
                AND E.ref_id = C.receive_goods_id
                AND C.doc_date = E.doc_date
                AND B.product_id = E.product_id
                AND E.qty > 0
        INNER JOIN tt_in_product_balance_summary_stock F
            ON F.tenant_id = E.tenant_id
                --AND   F.warehouse_id = E.warehouse_id
                AND F.product_id = E.product_id
                AND F.product_balance_id = E.product_balance_id
        INNER JOIN in_product_balance G
            ON F.product_balance_id = G.product_balance_id
        INNER JOIN in_product_balance_stock H
            ON H.tenant_id = F.tenant_id
                AND H.warehouse_id = F.warehouse_id
                AND H.product_id = F.product_id
                AND H.product_balance_id = F.product_balance_id
                AND H.product_status = F.product_status
        WHERE A.session_id = pSessionId
            AND B.ref_doc_type_id = vSalesOrderDocTypeId
            AND E.doc_type_id = vReceiveGoodsDocTypeId
            AND F.session_id = pSessionId
            AND F.inventory_id = vReturnNoteId;

        /*
         * update product_balance_stock
         */
        UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = vDatetime, update_user_id = vUserId,
            version = version + 1
        FROM tt_in_product_balance_summary_stock A
        WHERE A.session_id = pSessionId AND
            A.inventory_id = vReturnNoteId 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_id = A.product_id AND
            in_product_balance_stock.product_balance_id = A.product_balance_id AND
            in_product_balance_stock.product_status = A.product_status;

        INSERT INTO in_inventory_logistic(
            tenant_id, inventory_id, partner_ship_address_id,
            partner_ship_cp_id, cp_name, cp_phone1, cp_phone2, remark, due_date,
            version, create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, vClaimNoteId, B.partner_address_id,
            C.partner_cp_id, C.cp_name, C.phone1, C.phone2, A.remark, A.transfer_date,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM in_inventory A
        INNER JOIN m_partner_address B
            ON A.tenant_id = B.tenant_id AND A.partner_id = B.partner_id
        INNER JOIN m_partner_cp C
            ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
        WHERE A.inventory_id = vClaimNoteId
            AND B.active = vFlagYes
            AND B.flg_default = vFlagYes
            AND C.active = vFlagYes
            AND C.flg_responsibility = vFlagYes;

        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 = vClaimNoteId INTO result;

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

        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, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
            B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
        WHERE A.inventory_id = vClaimNoteId AND
            A.inventory_id = B.inventory_id AND
            C.warehouse_id = A.warehouse_from_id
        GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
            B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;

        INSERT INTO tt_in_po_balance_item
        (session_id, tenant_id, ou_id, doc_type_id,
        doc_no, doc_date, inventory_id, partner_id,
        inventory_item_id, po_id, receive_goods_id, receive_goods_item_id,
        qty_return, base_uom_id, remark,
        po_item_id, curr_code, price,
        flg_tax_amount, qty_po, qty_int_po,
        po_uom_id, tax_id, tax_percentage)
        SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                SUM(B.qty_realization), B.base_uom_id, A.remark,
                D.po_item_id, D.curr_code, D.gross_price_po - D.discount_amount,
                D.flg_tax_amount, D.qty_po, D.qty_int,
                D.po_uom_id, D.tax_id, D.tax_percentage
        FROM in_inventory A, in_inventory_item B, in_balance_receive_goods_item C, pu_po_item D
        WHERE A.inventory_id = vClaimNoteId AND
              A.inventory_id = B.inventory_id AND
              B.ref_item_id = C.receive_goods_item_id AND
              C.po_item_id = D.po_item_id
        GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                B.base_uom_id, D.po_item_id, D.curr_code, D.nett_price_po, D.qty_po, D.qty_int, D.po_uom_id, D.tax_id ;

        UPDATE pu_po_balance_item SET qty_return = pu_po_balance_item.qty_return + ((A.qty_return * A.qty_po) / A.qty_int_po), qty_int_return = pu_po_balance_item.qty_int_return + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId
        FROM tt_in_po_balance_item A
        WHERE A.session_id = pSessionId AND
            pu_po_balance_item.po_item_id = A.po_item_id;

        INSERT INTO pu_log_po_balance_item
        (tenant_id, po_id, po_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, A.po_id, A.po_item_id, A.doc_type_id, A.inventory_id, A.inventory_item_id,
            ((A.qty_return * A.qty_po) / A.qty_int_po ), A.po_uom_id, A.qty_return, A.base_uom_id, A.remark,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM tt_in_po_balance_item A
        WHERE A.session_id = pSessionId;

        INSERT INTO pu_po_balance_invoice
        (tenant_id, ou_id, partner_id, po_id,
        ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
        curr_code, price_po, item_amount, flg_invoice, invoice_id,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, A.ou_id, A.partner_id, A.po_id,
            A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.receive_goods_item_id, SUM((A.qty_return * A.qty_po) / A.qty_int_po), A.po_uom_id,
            A.curr_code, A.price,
            SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
            vFlagInvoice, vEmptyId,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM tt_in_po_balance_item A
        WHERE A.session_id = pSessionId
        GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price;

        INSERT INTO pu_po_balance_invoice_tax
        (tenant_id, ou_id, partner_id, po_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, A.partner_id, A.po_id,
            A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id, D.flg_amount,
            A.tax_percentage, A.curr_code,
            SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
            f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage),
            vFlagInvoice, vEmptyId,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM tt_in_po_balance_item A, m_tax D
        WHERE A.session_id = pSessionId AND
              A.tax_id = D.tax_id
        GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id,
            D.flg_amount, A.tax_percentage, A.curr_code;

        UPDATE in_balance_receive_goods_item SET status_item = vStatusFinal
        FROM tt_in_po_balance_item A
        WHERE A.session_id = pSessionId AND
            in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id AND
            in_balance_receive_goods_item.qty_rcv -in_balance_receive_goods_item.qty_return <= 0;

        UPDATE in_balance_receive_goods_item SET status_item = vStatusRelease
        FROM tt_in_po_balance_item A
        WHERE A.session_id = pSessionId AND
            in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id AND
            in_balance_receive_goods_item.qty_rcv -in_balance_receive_goods_item.qty_return > 0;

        SELECT A.ou_from_id, B.ou_id INTO vOuId, vOuWarehouseId
        FROM in_inventory A
        INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
        WHERE A.inventory_id = vClaimNoteId;

        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;

        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), 'DAILY', vDatetime, vUserId)
        FROM in_inventory A
        WHERE A.inventory_id = vClaimNoteId;

        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 = vClaimNoteId;

        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.inventory_item_id,
            A.partner_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_realization, B.base_uom_id,
            f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
            C.doc_date, vTypeRate,
            1, 1, 'PRODUCT_STOCK', B.remark
        FROM tt_in_po_balance_item A, in_inventory_item B, pu_receive_goods C
        WHERE A.inventory_id = vClaimNoteId AND
            A.inventory_item_id = B.inventory_item_id AND
            A.receive_goods_id = C.receive_goods_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, A.inventory_item_id,
            A.partner_id, C.product_id, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, A.qty_return, A.base_uom_id,
            f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
            A.doc_date, vTypeRate,
            1, 1, 'ACCR_AP', A.remark
        FROM tt_in_po_balance_item A, in_inventory_item C
        WHERE A.session_id = pSessionId AND
            A.inventory_item_id = C.inventory_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 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)
        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
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc = 'ACCR_AP';

        INSERT INTO pu_po_balance_invoice_ext_invoice_doc_type
        (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
        ref_item_id, invoice_id, invoice_doc_type_id,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id, A.inventory_id,
            A.receive_goods_item_id, vEmptyId, vEmptyId,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM tt_in_po_balance_item A
        WHERE A.session_id = pSessionId
        GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
            A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price;

        INSERT INTO pu_po_balance_invoice_tax_ext
        (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
        ref_item_id, tax_id, invoice_id, invoice_doc_type_id,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id, A.inventory_id,
            A.receive_goods_item_id, A.tax_id, vEmptyId, vEmptyId,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM tt_in_po_balance_item A, m_tax D
        WHERE A.session_id = pSessionId AND
              A.tax_id = D.tax_id
        GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id,
            D.flg_amount, A.tax_percentage, A.curr_code;

        /**
         * GENERATE APPROVAL
         */

        -- Set user id from sysconfig
        SELECT f_get_value_system_config_by_param_code(pTenantId, vParamUserIdForGenerateDoc)::bigint INTO vUserIdForGenerateDoc;

        -- Set role id from sysconfig
        SELECT f_get_value_system_config_by_param_code(pTenantId, vParamRoleIdForGenerateDoc)::bigint INTO vRoleIdForGenerateDoc;


        -- Mendapatkan default approval flow ID
        SELECT awe_flow_id INTO vClaimNoteFlowId
        FROM awe_flow
        WHERE scheme = vClaimNoteScheme AND
            flg_validate = vFlagYes AND
            active = vFlagYes;

        -- Generate data awe_currdoc_status
        INSERT INTO awe_currdoc_status(
            req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
            remark, current_user_id, current_role_id, flg_user_role, label,
            data, flow_id, create_datetime, create_user_id, create_role_id,
            update_datetime, update_user_id, update_role_id, version)
        SELECT A.inventory_id||'_'||A.doc_no, A.tenant_id, vClaimNoteScheme, A.inventory_id, A.doc_no, A.doc_date, vWorkflowApproved,
            A.remark, vUserIdForGenerateDoc, vRoleIdForGenerateDoc, vFlgUserRole, 'CLAIM NOTE '||A.doc_no,
            '{}', vClaimNoteFlowId, vDatetime, vUserIdForGenerateDoc, vRoleIdForGenerateDoc,
            vDatetime, vUserIdForGenerateDoc, vRoleIdForGenerateDoc, 0
        FROM in_inventory A
        WHERE A.inventory_id = vClaimNoteId;

        -- Generate data awe_historydoc
        INSERT INTO awe_historydoc(
                tenant_id, req_id, doc_id, scheme, user_id, role_id,
                activity, previous_state, next_state, remark, next_user_id, next_role_id,
                flg_user_role, activity_datetime, version)
        SELECT pTenantId, B.req_id, A.inventory_id, vClaimNoteScheme, vUserIdForGenerateDoc, vRoleIdForGenerateDoc,
                'AUTO GENERATE', '', '', A.remark, vEmptyId, vEmptyId,
                vFlgUserRole, vDateTime, 0
        FROM in_inventory A
        INNER JOIN awe_currdoc_status B ON B.doc_id = A.inventory_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
        WHERE A.tenant_id = pTenantId
            AND A.inventory_id = vClaimNoteId;

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

    END IF;

    DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION in_submit_return_note(bigint, character varying, character varying)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION in_submit_return_note(bigint, character varying, character varying) TO sts;
GRANT EXECUTE ON FUNCTION in_submit_return_note(bigint, character varying, character varying) TO public;