CREATE OR REPLACE FUNCTION in_submit_claim_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;
    vClaimNoteId        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);
    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;
    vOuId               bigint;
    vOuWarehouseId      bigint;

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

    vClaimNoteDocTypeId bigint;
    vRoundingModeNonTax character varying(5);
BEGIN

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

    vClaimNoteDocTypeId := 511;
    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_claim_note' AND
        A.process_no = pProcessNo;

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

    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_in_po_balance_item WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    /*
     * 1. update status doc in_inventory
     * 2.insert data ke in_log_product_balance_stock
     * 3.update data pu_po_balance_item
     * 4.insert data pu_log_po_balance_item
     * 5.insert data pu_po_balance_invoice
     * 6.insert data pu_po_balance_invoice_tax
     * 7.update data in_balance_receive_goods_item
     *
     * ini di tambahin untuk penambahan doc type id invoice
     * 8. add pu_po_balance_invoice_ext_invoice_doc_type
     * 9. add pu_po_balance_invoice_tax_ext
     */

    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;

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

    /*
     * 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, 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 data temporer tt_in_po_balance_item
     */
    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 qty return di data pu_po_balance_item
     */
    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;

    /*
     * buat data pu_log_po_balance_item
     */
    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;

    /*
     * buat data pu_po_balance_invoice
     */
    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;

    /*
     * buat data pu_po_balance_invoice
     */
    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;

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

    /*
     * journal claim note
     * Credit Inventory = dari nilai COGS
     * Debit BiayaReturPembelian = dari nilai po
     */

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

     /*
     * Modify Sarah, 27 Des 2016
     * table:pu_po_balance_invoice_ext_invoice_doc_type dan pu_po_balance_invoice_tax_ext =>  add data ke table baru
     *
     */

    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;

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