External Transactions (EXTERNAL_TRANSACTIONS_API)
IFS Energy and Resources (Deactivated)
Bobby Lo (Unlicensed)
/wiki/spaces/QG/pages/24674321
Overview
Offers the ability to:
- INSERT and DELETE records in the TEMP_INTERFACE_DATA table.
- INSERT and DELETE records in the TEMP_INTF_INV_DESC table.
- Validate data being loaded by means of the VALIDATION_API.
- Process TEMP_INTERFACE_DATA and TEMP_INTF_INV_DESC using.
- ACTU213 and ACTU116 to create live vouchers.
Listing of functions and procedures:
Function Name | Purpose |
|---|---|
insert_temp_interface_data | Inserts data into the temp_interface_data table. There are additional 5 overloaded versions of this function with various number of parameters. Make sure to learn their differences in order to use them properly with named notation. |
insert_temp_intf_inv_descs | Inserts data into the temp_intf_inv_desc table. |
initialize | Initializes the internal PL/SQL table. Call this at the beginning of a new voucher if using put_line_item and create_voucher functions. |
put_line_item | Stores line items in an internal PL/SQL table. |
create_voucher | Inserts line items data (stored in internal PL/SQL table) and voucher data into the temp_interface_data table and optionally launches the Batch Load and Batch Edit processes to load and validate the data. |
delete_temp_data | Deletes data from temp_interface_data and individual temp tables for the given report_id. |
validate_voucher | Submits the Batch Load (ACTU213) process (to copy the data from temp_interface_data table to individual temp tables) and Batch Edit (ACTU116) process (to validate the data in the individual temp tables). |
Procedure Name | Purpose |
|---|---|
insert_temp_interface_data | This overloads function insert_temp_interface_data() and has all the same parameters as the function. There are additional 5 overloaded versions of this function with various number of parameters. Make sure to learn their differences in order to use them properly with named notation. |
insert_temp_intf_inv_descs | This overloads function insert_temp_intf_inv_descs() and has all the same parameters as the function. |
initialize | This overloads function initialize() |
put_line_item | This overloads function put_line_item() |
create_voucher | This overloads function create_voucher() |
delete_temp_data | This overloads function delete_temp_data() |
validate_voucher | This overloads function validate_voucher() |
The API provides two different ways to populate TEMP_INTERFACE_DATA which is a basis for the creation of live VOUCHERS, INVOICES, LINE_ITEMS, CASH_TRANSACTIONS. See Method 1 and Method 2. The API provides ability to load multiple invoice description in TEMP_INTF_INV_DESC table using INSERT_TEMP_INTF_INV_DESC() function or procedure. Invoice Descriptions are eventually created in live INVOICE_DESCRIPTIONS table.
Method 1
- INSERT_TEMP_INTERFACE_DATA() function or procedure with optional call to VALIDATE_VOUCHER()
Method 2
INITIALIZE(), PUT_LINE_ITEM(), CREATE_VOUCHER() functions and procedures:
Method 1) INSERT_TEMP_INTERFACE_DATA() (RECOMMENDED)
1. Is utilized by TDEU001 (Spreadsheet Upload) and/or file upload using ACTU213.
2. All rules relevant to those processes in terms of transaction creation apply here as well.
3. Allows creation of new invoice records.
4. Allows creation of invoice adjustments.
5. Allows creation of cash receipts records offset by a new or existing invoice.
6. Journal entries.
7. Validates key master data information on each insert.
8. Checks voucher balance during VALIDATE_VOUCHER().
9. To load Invoices, call the procedures in the following order:
10. Insert_temp_interface_data, with invoice parameters populated, INTERFACE_TRANSACTION_CODE set to INVOICE and SRC_INVC_ID populated; new batch flag set to Y.
11. Insert_temp_interface_data, as many times as there are Invoice offset lines; use the same SRC_INVC_ID populated to link those line to the invoice; invoice parameters should be NULL; new batch flag set to N.
12. To process the loaded data execute VALIDATE_VOUCHER().
13. Sample test scripts below:
14. To load cash receipts call the procedures in the following order:
a. Insert_temp_interface_data, with cash transaction parameters populated.
b. Insert_temp_interface_data, as many times as there are Cash Transaction offset lines.
c. To process the loaded data execute VALIDATE_VOUCHER().
d. Sample test script below.
15. To load journal entries call the procedures in the following order:
a. Insert_temp_interface_data, do not populate cash transaction or invoice parameters.
b. Insert_temp_interface_data, as many times as needed.
c. To process the loaded data execute VALIDATE_VOUCHER().
d. Sample test scripts below.
LOAD EXAMPLES (with key required parameters)
Case 1 – Invoice and 2 offset line items |
|---|
INVOICE indicates value of INTERFACE_TRANSACTION_CODE; 1 represents SRC_INVC_ID parameter
|
Case 2 – 2 Invoices |
|---|
INVOICE indicates value of INTERFACE_TRANSACTION_CODE; SRC_INVC_ID is 1 for the first invoice and its lines and 2 for the second invoice
|
Case 3 – Invoice adjustment (Invoice already exists and is posted) |
|---|
1)JEI client_id = 64 invc_num = 26MAY2006 invc_date = 20051025 invc_amt = 21.00 2)JEI offset line item no invoice parameters populated amt = -21.00 |
Case 4 – New invoice + invoice adjustment (Invoice already exists and must be posted) |
|---|
1)INVOICE client_id = 34 invc_num = 25MAY2006 invc_date = 20051025 invc_amt = -21.00 (NEW INVOICE) 2)JEI client_id = 64 invc_num = 26MAY2006 invc_date = 20051025 invc_amt = 21.00 (ADJUSTMENT) |
Case 5 |
|---|
1)INVOICE 1 client_id = 64 invc_num = 26MAY2006 invc_date = 20051005 invc_amt = -321.00 2)INVOICE 1 offset line item no invoice parameters populated amt = 21.00 3)INVOICE 2 client_id = 64 invc_num = ABC64 invc_date = 20051005 invc_amt = 200.00 4)JEI 2 client_id = 64 invc_num = ABC64 invc_date = 20051005 invc_amt = 100.00 |
Case 6 – Cash transaction + new invoice offset |
1)JEI client_id = 64 cash_tx_date = 20051005 chq_micr_num = 51005 cash_tx_amt = -321.00 payable_or_receivable_code = ‘P’ NEW INVOICE offset 2)INVOICE client_id = 64 invc_num = 123 invc_date = 20051005 invc_amt = -321.00 |
Case 7 – Cash transaction + existing Invoice Offset |
|---|
1)JEI client_id = 64 cash_tx_date = 20051005 chq_micr_num = 51005 cash_tx_amt = -321.00 payable_or_receivable_code = ‘P’ 2)JEI client_id = 64 invc_num = 123 invc_date = 20051005 invc_amt = -321.00 |
Case 8 – Cash receipt + new invoice offset |
|---|
1)JEI client_id = 64 ba_issued_date = 20051005 ba_issued_num = 51005 cash_tx_date = 20051005 cash_tx_amt = 321.00 payable_or_receivable_code = ‘R’ NEW INVOICE offset 2)INVOICE client_id = 64 invc_num = 123 invc_date = 20051005 invc_amt = -321.00 |
Case 9 – Cash receipt + existing invoice offset |
|---|
1)JEI client_id = 64 ba_issued_date = 20051005 ba_issued_num = 51005 cash_tx_date = 20051005 cash_tx_amt = 321.00 payable_or_receivable_code = ‘R’ 2)JEI client_id = 64 invc_num = 123 invc_date = 20051005 invc_amt = -321.00 |
Case 10 – Cash receipt + new invoice offset WITH bank deposit |
|---|
1)JEI client_id = 8001 reporting_curr_amt = +75 ba_issued_date = 20150901 ba_issued_num = 9 bank_acct_id = 8000 bank_deposit_num = 18938 NEW INVOICE offset 2)INVOICE client_id = 8001 reporting_curr_amt = +75 invc_num = INV-1130 invc_date = 20150902 invc_amt = +75.00 ba_issued_date = 20150901 ba_issued_num = 9 bank_acct_id = 8000 ADJUSTMENT LINE TO THE NEW INVOICE offset 3)JEI client_id = 8001 reporting_curr_amt = -75 invc_num = INV-1130 invc_date = 20150902 invc_amt = -75.00 ba_issued_date = 20150901 ba_issued_num = 9 bank_acct_id = 8000 ADJUSTMENT LINE TO A NON-CONTROL & NON BANK-CASH ACCOUNT 4)JEI client_id = 64 reporting_curr_amt = -75 invc_amt = -75.00 ba_issued_date = 20150901 ba_issued_num = 9 bank_acct_id = 8000 |
Case 11 – Cash receipt + existing invoice offset WITH bank deposit |
|---|
1)JEI client_id = 8001 reporting_curr_amt = +75 ba_issued_date = 20150901 ba_issued_num = 11 bank_acct_id = 8000 bank_deposit_num = 18938 2)JEI client_id = 8001 reporting_curr_amt = -75 invc_num = INV-1147 invc_date = 20150902 invc_amt = -75.00 ba_issued_date = 20150901 ba_issued_num = 11 bank_acct_id = 8000 bank_deposit_num = 18938 |
Method 2) INITIALIZE(), PUT_LINE_ITEM(), CREATE_VOUCHER() functions and procedures method.
1. Allows creation of Journal Entries.
2. Allows creation of Cash Receipts (no invoice offset).
3. Validates key master data information.
4. Checks voucher balance.
5. To load a journal entry call the procedures in the following order:
a. initialize.
b. put_line_item.
c. put_line_item – as many as required line item recordscreate_voucher– when parameter VALIDATE_VOUCHER_FLAG is set to Y voucher will be processed using ACTU213 and ACTU116.
- To load a cash receipt call the procedures in the following order:
- Initialize.
- put_line_item – With Cash Receipt information and offset information.
- create_voucher– When parameter VALIDATE_VOUCHER_FLAG is set to Y voucher will be processed using ACTU213 and ACTU116.
- Sample test script below.
If you issued commit statement(s) then you may have to use DELETE_TEMP_DATA().
It does the following for specified REPORT_ID:
DELETE FROM temp_interface_data WHERE report_id = i_report_id;
DELETE FROM temp_invoices WHERE report_id = i_report_id;
DELETE FROM temp_cash_transactions WHERE report_id = i_report_id;
DELETE FROM temp_line_items_supplement WHERE report_id = i_report_id;
DELETE FROM temp_line_items WHERE report_id = i_report_id;
DELETE FROM temp_vouchers WHERE report_id = i_report_id;
If you have errors nothing should be inserted
Sample:
INSERT_TEMP_INTERFACE_DATA
o_return_status FALSE
o_return_code -20001
o_return_message Currency Code xAD is not valid.
io_report_id
INSERT_TEMP_INTERFACE_DATA
o_return_status FALSE
o_return_code -20001
o_return_message Currency Code xAD is not valid.
io_report_id
0 lines inserted into TEMP_INTERFACE_DATA
Sample Scripts
Cash Receipt with Offset to a New Invoice using INSERT_TEMP_INTERFACE_DATA() when bank deposit number is available but bank_cash_tx_id is not known
Use this procedure when the Bank_Deposit_Number is known but the Bank_Cash_Tx_ID is not known. Uses 115 parameters.
- Inserts four records into TEMP_INTERFACE_DATA one contains Cash Receipt information, one creates a new invoice, one adjusts the invoice created and the other to a non-bank cash account to balance the voucher.
- The Cash Receipt line will have INTERFACE_TRANSACTION_CODE = JEI.
- To offset with a new invoice set INTERFACE_TRANSACTION_CODE to INVOICE.
- The other two lines should have INTERFACE_TRANSACTION_CODE to JEI.
- All lines to be linked to the created Cash Receipt have to have the same BANK_ACCT_ID, BA_ISSUED_NUM, and BA_ISSUED_DATE.
- Only one record should have both the bank deposit number and the bank cash account as this is interpreted as a Cash Receipt line.
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
v_report_id NUMBER;
i_new_batch_flag VARCHAR2(1) DEFAULT NULL;
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE DEFAULT NULL;
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE DEFAULT NULL;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE DEFAULT NULL;
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE DEFAULT NULL;
i_acct_per_date VARCHAR2(8) DEFAULT NULL;
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE DEFAULT NULL;
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE DEFAULT NULL;
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE DEFAULT NULL;
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE DEFAULT NULL;
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE DEFAULT NULL;
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE DEFAULT NULL;
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE DEFAULT NULL;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE DEFAULT NULL;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE DEFAULT NULL;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE DEFAULT NULL;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE DEFAULT NULL;
i_invc_date VARCHAR2(8) DEFAULT NULL;
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE DEFAULT NULL;
i_due_date VARCHAR2(8) DEFAULT NULL;
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_bank_deposit_num BANK_DEPOSIT_ITEMS.BANK_DEPOSIT_NUM%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_pay_stat_code TEMP_INTERFACE_DATA.PAY_STAT_CODE%TYPE DEFAULT NULL;
i_priority_code TEMP_INTERFACE_DATA.PRIORITY_CODE%TYPE DEFAULT NULL;
i_rcvd_date VARCHAR2(8) DEFAULT NULL;
i_sent_for_approval_date VARCHAR2(8) DEFAULT NULL;
i_approval_date VARCHAR2(8) DEFAULT NULL;
i_approval_rem TEMP_INTERFACE_DATA.APPROVAL_REM%TYPE DEFAULT NULL;
i_sent_to TEMP_INTERFACE_DATA.SENT_TO%TYPE DEFAULT NULL;
i_invc_gst_amt TEMP_INTERFACE_DATA.INVC_GST_AMT%TYPE DEFAULT NULL;
i_invc_gst_factor TEMP_INTERFACE_DATA.INVC_GST_FACTOR%TYPE DEFAULT NULL;
i_invc_type_code TEMP_INTERFACE_DATA.INVC_TYPE_CODE%TYPE DEFAULT NULL;
i_release_for_payment_amt TEMP_INTERFACE_DATA.RELEASE_FOR_PAYMENT_AMT%TYPE DEFAULT NULL;
i_invc_afe_num TEMP_INTERFACE_DATA.INVC_AFE_NUM%TYPE DEFAULT NULL;
i_invc_cc_num TEMP_INTERFACE_DATA.INVC_CC_NUM%TYPE DEFAULT NULL;
i_nrwt_amt TEMP_INTERFACE_DATA.NRWT_AMT%TYPE DEFAULT NULL;
i_pst_amt TEMP_INTERFACE_DATA.PST_AMT%TYPE DEFAULT NULL;
i_gst_distribution_amt TEMP_INTERFACE_DATA.GST_DISTRIBUTION_AMT%TYPE DEFAULT NULL;
i_discount_due_date VARCHAR2(8) DEFAULT NULL;
i_discount_amt TEMP_INTERFACE_DATA.DISCOUNT_AMT%TYPE DEFAULT NULL;
i_discount_achieved TEMP_INTERFACE_DATA.DISCOUNT_ACHIEVED%TYPE DEFAULT NULL;
i_misc_income_amt TEMP_INTERFACE_DATA.MISCELLANEOUS_INCOME_AMT%TYPE DEFAULT NULL;
i_withholding_tax_amt TEMP_INTERFACE_DATA.WITHHOLDING_TAX_AMT%TYPE DEFAULT NULL;
i_gst_factor TEMP_INTERFACE_DATA.GST_FACTOR%TYPE DEFAULT NULL;
i_src_agr_id TEMP_INTERFACE_DATA.SRC_AGR_ID%TYPE DEFAULT NULL;
i_src_agr_type_code TEMP_INTERFACE_DATA.SRC_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_et_id TEMP_INTERFACE_DATA.ET_ID%TYPE DEFAULT NULL;
i_li_as_entered_vol TEMP_INTERFACE_DATA.LI_AS_ENTERED_VOL%TYPE DEFAULT NULL;
i_pre_tax_amt TEMP_INTERFACE_DATA.PRE_TAX_AMT%TYPE DEFAULT NULL;
i_warehouse_handling_flag TEMP_INTERFACE_DATA.WAREHOUSE_HANDLING_FLAG%TYPE DEFAULT NULL;
i_li_discount_amt TEMP_INTERFACE_DATA.LI_DISCOUNT_AMT%TYPE DEFAULT NULL;
i_misc_income_category TEMP_INTERFACE_DATA.MISCELLANEOUS_INCOME_CATEGORY%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
i_new_batch_flag := 'Y';
i_interface_transaction_code := 'JEI';
i_voucher_num := 1;
i_voucher_type_code := 'GEN';
i_org_id := 8000;
i_acct_Per_Date := '20130131';
i_curr_code := 'CAD';
i_src_code := '9AM';
i_major_acct := '1000';
i_minor_acct := '100';
i_reporting_curr_amt := 75;
i_ba_id := 8001;
i_invc_num := Null;
i_invc_date := Null;
i_invc_amt := NULL;
i_bank_acct_id := 8000;
i_ba_issued_date := '20150901';
i_ba_issued_num := '11';
i_bank_deposit_num := 18938;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_bank_deposit_num,
i_external_reference,
i_pay_stat_code,
i_priority_code,
i_rcvd_date,
i_sent_for_approval_date,
i_approval_date,
i_approval_rem,
i_sent_to,
i_invc_gst_amt,
i_invc_gst_factor,
i_invc_type_code,
i_release_for_payment_amt,
i_invc_afe_num,
i_invc_cc_num,
i_nrwt_amt,
i_pst_amt,
i_gst_distribution_amt,
i_discount_due_date,
i_discount_amt,
i_discount_achieved,
i_misc_income_amt,
i_withholding_tax_amt,
i_gst_factor,
i_src_agr_id,
i_src_agr_type_code,
i_et_id,
i_li_as_entered_vol,
i_pre_tax_amt,
i_warehouse_handling_flag,
i_li_discount_amt,
i_misc_income_category,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := 'INVOICE';
i_voucher_num := 1;
i_voucher_type_code := 'GEN';
i_org_id := 8000;
i_acct_per_date := '20130131';
i_curr_code := 'CAD';
i_src_code := '9AM';
i_major_acct := '1321';
i_minor_acct := '104';
i_reporting_curr_amt := 75;
i_ba_id := 8001;
i_invc_num := 'INV-1705';
I_invc_date := '20150902';
I_invc_amt := 75;
I_bank_acct_id := 8000;
I_ba_issued_date := '20150901';
I_ba_issued_num := '11';
I_bank_deposit_num := NULL;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_bank_deposit_num,
i_external_reference,
i_pay_stat_code,
i_priority_code,
i_rcvd_date,
i_sent_for_approval_date,
i_approval_date,
i_approval_rem,
i_sent_to,
i_invc_gst_amt,
i_invc_gst_factor,
i_invc_type_code,
i_release_for_payment_amt,
i_invc_afe_num,
i_invc_cc_num,
i_nrwt_amt,
i_pst_amt,
i_gst_distribution_amt,
i_discount_due_date,
i_discount_amt,
i_discount_achieved,
i_misc_income_amt,
i_withholding_tax_amt,
i_gst_factor,
i_src_agr_id,
i_src_agr_type_code,
i_et_id,
i_li_as_entered_vol,
i_pre_tax_amt,
i_warehouse_handling_flag,
i_li_discount_amt,
i_misc_income_category,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := 'JEI';
i_voucher_num := 1;
i_voucher_type_code := 'GEN';
i_org_id := 8000;
i_acct_per_date := '20130131';
i_curr_code := 'CAD';
i_src_code := '9AM';
i_major_acct := '1321';
i_minor_acct := '104';
i_reporting_curr_amt := -75;
i_ba_id := 8001;
i_invc_num := 'INV-1705';
i_invc_date := '20150902';
i_invc_amt := -75;
i_bank_acct_id := 8000;
i_ba_issued_date := '20150901';
i_ba_issued_num := '11';
i_bank_deposit_num := NULL;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_bank_deposit_num,
i_external_reference,
i_pay_stat_code,
i_priority_code,
i_rcvd_date,
i_sent_for_approval_date,
i_approval_date,
i_approval_rem,
i_sent_to,
i_invc_gst_amt,
i_invc_gst_factor,
i_invc_type_code,
i_release_for_payment_amt,
i_invc_afe_num,
i_invc_cc_num,
i_nrwt_amt,
i_pst_amt,
i_gst_distribution_amt,
i_discount_due_date,
i_discount_amt,
i_discount_achieved,
i_misc_income_amt,
i_withholding_tax_amt,
i_gst_factor,
i_src_agr_id,
i_src_agr_type_code,
i_et_id,
i_li_as_entered_vol,
i_pre_tax_amt,
i_warehouse_handling_flag,
i_li_discount_amt,
i_misc_income_category,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := 'JEI';
i_voucher_num := 1;
i_voucher_type_code := 'GEN';
i_org_id := 8000;
i_acct_per_date := '20130131';
i_curr_code := 'CAD';
i_src_code := '9AM';
i_major_acct := '1000';
i_minor_acct := '200';
i_reporting_curr_amt := -75;
i_ba_id := 8001;
i_invc_num := NULL;
i_invc_date := NULL;
i_invc_amt := -75;
i_bank_acct_id := 8000;
i_ba_issued_date := '20150901';
i_ba_issued_num := '11';
i_bank_deposit_num := NULL;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_bank_deposit_num,
i_external_reference,
i_pay_stat_code,
i_priority_code,
i_rcvd_date,
i_sent_for_approval_date,
i_approval_date,
i_approval_rem,
i_sent_to,
i_invc_gst_amt,
i_invc_gst_factor,
i_invc_type_code,
i_release_for_payment_amt,
i_invc_afe_num,
i_invc_cc_num,
i_nrwt_amt,
i_pst_amt,
i_gst_distribution_amt,
i_discount_due_date,
i_discount_amt,
i_discount_achieved,
i_misc_income_amt,
i_withholding_tax_amt,
i_gst_factor,
i_src_agr_id,
i_src_agr_type_code,
i_et_id,
i_li_as_entered_vol,
i_pre_tax_amt,
i_warehouse_handling_flag,
i_li_discount_amt,
i_misc_income_category,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
Dbms_Output.Put_Line(' o_return_message '||O_Return_Message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
END;
/
COMMIT;
Cash Receipt with Offset to an Existing Invoice using INSERT_TEMP_INTERFACE_DATA() when bank deposit number is available but bank_cash_tx_id is not known
Use this procedure when the Bank_Deposit_Number is known but the Bank_Cash_Tx_ID is not known. Uses 115 parameters.
- Inserts two records into TEMP_INTERFACE_DATA one contains Cash Receipt information and the other adjusts the existing invoice
- The Cash Receipt line will have INTERFACE_TRANSACTION_CODE = JEI.
- To offset with a new invoice set INTERFACE_TRANSACTION_CODE to INVOICE.
- To offset with an existing invoice set INTERFACE_TRANSACTION_CODE to JEI.
- All lines to be linked to the created Cash Receipt have to have the same BANK_ACCT_ID, BA_ISSUED_NUM, and BA_ISSUED_DATE.
Only one record should have both the bank deposit number and the bank cash account as this is interpreted as a cash receipt line.
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
v_report_id NUMBER;
i_new_batch_flag VARCHAR2(1) DEFAULT NULL;
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE DEFAULT NULL;
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE DEFAULT NULL;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE DEFAULT NULL;
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE DEFAULT NULL;
i_acct_per_date VARCHAR2(8) DEFAULT NULL;
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE DEFAULT NULL;
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE DEFAULT NULL;
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE DEFAULT NULL;
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE DEFAULT NULL;
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE DEFAULT NULL;
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE DEFAULT NULL;
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE DEFAULT NULL;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE DEFAULT NULL;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE DEFAULT NULL;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE DEFAULT NULL;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE DEFAULT NULL;
i_invc_date VARCHAR2(8) DEFAULT NULL;
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE DEFAULT NULL;
i_due_date VARCHAR2(8) DEFAULT NULL;
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_bank_deposit_num BANK_DEPOSIT_ITEMS.BANK_DEPOSIT_NUM%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_pay_stat_code TEMP_INTERFACE_DATA.PAY_STAT_CODE%TYPE DEFAULT NULL;
i_priority_code TEMP_INTERFACE_DATA.PRIORITY_CODE%TYPE DEFAULT NULL;
i_rcvd_date VARCHAR2(8) DEFAULT NULL;
i_sent_for_approval_date VARCHAR2(8) DEFAULT NULL;
i_approval_date VARCHAR2(8) DEFAULT NULL;
i_approval_rem TEMP_INTERFACE_DATA.APPROVAL_REM%TYPE DEFAULT NULL;
i_sent_to TEMP_INTERFACE_DATA.SENT_TO%TYPE DEFAULT NULL;
i_invc_gst_amt TEMP_INTERFACE_DATA.INVC_GST_AMT%TYPE DEFAULT NULL;
i_invc_gst_factor TEMP_INTERFACE_DATA.INVC_GST_FACTOR%TYPE DEFAULT NULL;
i_invc_type_code TEMP_INTERFACE_DATA.INVC_TYPE_CODE%TYPE DEFAULT NULL;
i_release_for_payment_amt TEMP_INTERFACE_DATA.RELEASE_FOR_PAYMENT_AMT%TYPE DEFAULT NULL;
i_invc_afe_num TEMP_INTERFACE_DATA.INVC_AFE_NUM%TYPE DEFAULT NULL;
i_invc_cc_num TEMP_INTERFACE_DATA.INVC_CC_NUM%TYPE DEFAULT NULL;
i_nrwt_amt TEMP_INTERFACE_DATA.NRWT_AMT%TYPE DEFAULT NULL;
i_pst_amt TEMP_INTERFACE_DATA.PST_AMT%TYPE DEFAULT NULL;
i_gst_distribution_amt TEMP_INTERFACE_DATA.GST_DISTRIBUTION_AMT%TYPE DEFAULT NULL;
i_discount_due_date VARCHAR2(8) DEFAULT NULL;
i_discount_amt TEMP_INTERFACE_DATA.DISCOUNT_AMT%TYPE DEFAULT NULL;
i_discount_achieved TEMP_INTERFACE_DATA.DISCOUNT_ACHIEVED%TYPE DEFAULT NULL;
i_misc_income_amt TEMP_INTERFACE_DATA.MISCELLANEOUS_INCOME_AMT%TYPE DEFAULT NULL;
i_withholding_tax_amt TEMP_INTERFACE_DATA.WITHHOLDING_TAX_AMT%TYPE DEFAULT NULL;
i_gst_factor TEMP_INTERFACE_DATA.GST_FACTOR%TYPE DEFAULT NULL;
i_src_agr_id TEMP_INTERFACE_DATA.SRC_AGR_ID%TYPE DEFAULT NULL;
i_src_agr_type_code TEMP_INTERFACE_DATA.SRC_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_et_id TEMP_INTERFACE_DATA.ET_ID%TYPE DEFAULT NULL;
i_li_as_entered_vol TEMP_INTERFACE_DATA.LI_AS_ENTERED_VOL%TYPE DEFAULT NULL;
i_pre_tax_amt TEMP_INTERFACE_DATA.PRE_TAX_AMT%TYPE DEFAULT NULL;
i_warehouse_handling_flag TEMP_INTERFACE_DATA.WAREHOUSE_HANDLING_FLAG%TYPE DEFAULT NULL;
i_li_discount_amt TEMP_INTERFACE_DATA.LI_DISCOUNT_AMT%TYPE DEFAULT NULL;
i_misc_income_category TEMP_INTERFACE_DATA.MISCELLANEOUS_INCOME_CATEGORY%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
i_new_batch_flag := 'Y';
i_interface_transaction_code := 'JEI';
i_voucher_num := 1;
i_voucher_type_code := 'GEN';
i_org_id := 8000;
i_acct_per_date := '20130131';
i_curr_code := 'CAD';
i_src_code := '9AM';
i_major_acct := '1000';
i_minor_acct := '100';
i_reporting_curr_amt := 75;
i_ba_id := 8001;
i_bank_acct_id := 8000;
i_ba_issued_date := '20150901';
i_ba_issued_num := '9';
i_bank_deposit_num := 18938;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_bank_deposit_num,
i_external_reference,
i_pay_stat_code,
i_priority_code,
i_rcvd_date,
i_sent_for_approval_date,
i_approval_date,
i_approval_rem,
i_sent_to,
i_invc_gst_amt,
i_invc_gst_factor,
i_invc_type_code,
i_release_for_payment_amt,
i_invc_afe_num,
i_invc_cc_num,
i_nrwt_amt,
i_pst_amt,
i_gst_distribution_amt,
i_discount_due_date,
i_discount_amt,
i_discount_achieved,
i_misc_income_amt,
i_withholding_tax_amt,
i_gst_factor,
i_src_agr_id,
i_src_agr_type_code,
i_et_id,
i_li_as_entered_vol,
i_pre_tax_amt,
i_warehouse_handling_flag,
i_li_discount_amt,
i_misc_income_category,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_Code := 'JEI';
i_voucher_num := 1;
i_voucher_type_code := 'GEN';
i_org_id := 8000;
i_acct_per_date := '20130131';
i_curr_code := 'CAD';
i_src_code := '9AM';
i_major_acct := '1321';
i_minor_acct := '104';
i_reporting_curr_amt := -75;
i_ba_id := 8001;
i_invc_num := 'INV-1147';
i_invc_date := '20150902';
i_invc_amt := -75;
i_bank_acct_id := 8000;
i_ba_issued_date := '20150901';
i_ba_issued_num := '9';
i_bank_deposit_num := 18938;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_bank_deposit_num,
i_external_reference,
i_pay_stat_code,
i_priority_code,
i_rcvd_date,
i_sent_for_approval_date,
i_approval_date,
i_approval_rem,
i_sent_to,
i_invc_gst_amt,
i_invc_gst_factor,
i_invc_type_code,
i_release_for_payment_amt,
i_invc_afe_num,
i_invc_cc_num,
i_nrwt_amt,
i_pst_amt,
i_gst_distribution_amt,
i_discount_due_date,
i_discount_amt,
i_discount_achieved,
i_misc_income_amt,
i_withholding_tax_amt,
i_gst_factor,
i_src_agr_id,
i_src_agr_type_code,
i_et_id,
i_li_as_entered_vol,
i_pre_tax_amt,
i_warehouse_handling_flag,
i_li_discount_amt,
i_misc_income_category,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
END;
/
COMMIT;
Cash Receipt with Invoice Offset using INSERT_TEMP_INTERFACE_DATA()
- Inserts two records into TEMP_INTERFACE_DATA one contains Cash Receipt information and the other creates a new invoice.
- The Cash Receipt line will have INTERFACE_TRANSACTION_CODE = JEI.
- To offset with a new invoice set INTERFACE_TRANSACTION_CODE to INVOICE.
- To offset with an existing invoice set INTERFACE_TRANSACTION_CODE to JEI
- All lines to be linked to the created Cash Receipt have to have the same CASH_TX_BA_ID and BA_ISSUED_NUM.
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
v_report_id NUMBER;
i_new_batch_flag VARCHAR2(1) DEFAULT NULL;
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE DEFAULT NULL;
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE DEFAULT NULL;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE DEFAULT NULL;
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE DEFAULT NULL;
i_acct_per_date VARCHAR2(8) DEFAULT NULL;
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE DEFAULT NULL;
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE DEFAULT NULL;
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE DEFAULT NULL;
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE DEFAULT NULL;
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE DEFAULT NULL;
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE DEFAULT NULL;
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE DEFAULT NULL;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE DEFAULT NULL;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE DEFAULT NULL;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE DEFAULT NULL;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE DEFAULT NULL;
i_invc_date VARCHAR2(8) DEFAULT NULL;
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE DEFAULT NULL;
i_due_date VARCHAR2(8) DEFAULT NULL;
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
i_new_batch_flag := 'Y';
i_interface_transaction_code := 'JEI';
i_voucher_num := 45679;
i_voucher_type_code := 'MISC';
i_org_id := 1;
i_acct_per_date := '20051031';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '100';
i_minor_acct := '001';
i_cc_num := NULL;
i_reporting_curr_amt := 50;
i_medium_code := 'C';
i_cash_tx_amt := 50;
i_cash_tx_date := '20051023';
i_bank_acct_id := 1;
i_cash_tx_ba_id := 64;
i_cash_tx_stat_code := 'ISS';
i_ba_issued_date := '20051011';
i_ba_issued_num := 'A445';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := 'INVOICE';
i_voucher_num := 45679;
i_voucher_type_code := 'MISC';
i_org_id := 1;
i_acct_per_date := '20051031';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '500';
i_minor_acct := '100';
i_reporting_curr_amt := -50;
i_ba_id := 64;
i_alternate_address_id := 31068;
i_invc_num := '20FEB2006';
i_invc_date := '20051022';
i_invc_amt := -50;
i_medium_code := NULL;
i_cash_tx_amt := NULL;
i_cash_tx_date := NULL;
i_bank_acct_id := NULL;
i_cash_tx_ba_id := 64;
i_cash_tx_stat_code := NULL;
i_ba_issued_date := NULL;
i_ba_issued_num := 'A445';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
END;
/
COMMIT;
Cash Receipt with Invoice Offset using INSERT_TEMP_INTERFACE_DATA() and Voucher creation
- The Cash Receipt line needs INTERFACE_TRANSACTION_CODE set to JEI.
- To offset with a new invoice set INTERFACE_TRANSACTION_CODE to INVOICE.
- To offset with an existing invoice set INTERFACE_TRANSACTION_CODE to JEI.
All lines to be linked to the newly created Cash Receipt must have the same value in CASH_TX_BA_ID and BA_ISSUED_NUM.
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
v_report_id NUMBER;
i_new_batch_flag VARCHAR2(1) DEFAULT NULL;
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE DEFAULT NULL;
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE DEFAULT NULL;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE DEFAULT NULL;
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE DEFAULT NULL;
i_acct_per_date VARCHAR2(8) DEFAULT NULL;
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE DEFAULT NULL;
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE DEFAULT NULL;
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE DEFAULT NULL;
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE DEFAULT NULL;
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE DEFAULT NULL;
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE DEFAULT NULL;
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE DEFAULT NULL;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE DEFAULT NULL;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE DEFAULT NULL;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE DEFAULT NULL;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE DEFAULT NULL;
i_invc_date VARCHAR2(8) DEFAULT NULL;
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE DEFAULT NULL;
i_due_date VARCHAR2(8) DEFAULT NULL;
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
i_new_batch_flag := 'Y';
i_interface_transaction_code := 'JEI';
i_voucher_num := 45679;
i_voucher_type_code := 'MISC';
i_org_id := 1;
i_acct_per_date := '20051031';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '100';
i_minor_acct := '001';
i_cc_num := NULL;
i_reporting_curr_amt := 50;
i_medium_code := 'C';
i_cash_tx_amt := 50;
i_cash_tx_date := '20051023';
i_bank_acct_id := 1;
i_cash_tx_ba_id := 64;
i_cash_tx_stat_code := 'ISS';
i_ba_issued_date := '20051011';
i_ba_issued_num := 'A445';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := 'INVOICE';
i_voucher_num := 45679;
i_voucher_type_code := 'MISC';
i_org_id := 1;
i_acct_per_date := '20051031';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '500';
i_minor_acct := '100';
i_reporting_curr_amt := -50;
i_ba_id := 64;
i_alternate_address_id := 31068;
i_invc_num := '12APR2006_4';
i_invc_date := '20051022';
i_invc_amt := -50;
i_medium_code := NULL;
i_cash_tx_amt := NULL;
i_cash_tx_date := NULL;
i_bank_acct_id := NULL;
i_cash_tx_ba_id := 64;
i_cash_tx_stat_code := NULL;
i_ba_issued_date := NULL;
i_ba_issued_num := 'A445';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
o_return_status := EXTERNAL_TRANSACTIONS_API.VALIDATE_VOUCHER(io_report_id,'USER_ID',v_report_id,o_return_code,o_return_message);
DBMS_OUTPUT.PUT_LINE('VALIDATE_VOUCHER');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' v_report_id '||to_char(v_report_id));
END;
/
COMMIT;
Invoice using INSERT_TEMP_INTERFACE_DATA()
- INTERFACE_TRANSACTION_CODE set to INVOICE on the Invoice record.
The SRC_INVC_ID must be populated on the invoice record and all lines related to the invoice in order to link the line items to the invoice.
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
i_new_batch_flag VARCHAR2(1) := 'Y';
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE := 'INVOICE';
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE := 45678;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE := 'MISC';
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE := 99;
i_acct_per_date VARCHAR2(8) := '20051031';
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE := 'CAD';
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE := 'FIN';
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE := '500';
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE := '100';
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE DEFAULT NULL;
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE DEFAULT NULL;
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE := -100;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE := 1;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE := 64;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE := 11539;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE := '20051022_1';
i_invc_date VARCHAR2(8) := '20051022';
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE := -100;
i_due_date VARCHAR2(8) := '20051122';
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := 'INVOICE';
i_voucher_num := 45678;
i_voucher_type_code := 'MISC';
i_org_id := 99;
i_acct_per_date := '20051031';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '400';
i_minor_acct := '100';
i_cc_num := 'CCJIB01';
i_reporting_curr_amt := 100;
i_actvy_per_date := '20051031';
i_ba_id := NULL;
i_alternate_address_id := NULL;
i_invc_num := NULL;
i_invc_date := NULL;
i_invc_amt := NULL;
i_due_date := NULL;
i_src_invc_id := 1;
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
END;
/
COMMIT;
Invoice using INSERT_TEMP_INTERFACE_DATA() and Voucher creation
- To create a voucher add call to VALIDATE_VOUCHER() to your script. It launches ACTU213 and ACTU116 to create live vouchers.
o_return_status := EXTERNAL_TRANSACTIONS_API.VALIDATE_VOUCHER (io_report_id,'USER_ID',v_report_id,o_return_code,o_return_message);
Journal Entry using INSERT_TEMP_INTERFACE_DATA()
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
v_report_id NUMBER;
i_new_batch_flag VARCHAR2(1) := 'Y';
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE := 'JEI';
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE := 45678;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE := 'MISC';
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE := 99;
i_acct_per_date VARCHAR2(8) := '20051031';
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE := 'CAD';
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE := 'FIN';
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE := '300';
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE := '100';
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE := 'AFEJIB01';
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE := 'CCJIB01';
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE := -100;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE DEFAULT NULL;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE DEFAULT NULL;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE DEFAULT NULL;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE DEFAULT NULL;
i_invc_date VARCHAR2(8) DEFAULT NULL;
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE DEFAULT NULL;
i_due_date VARCHAR2(8) DEFAULT NULL;
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
i_new_batch_flag := 'N';
i_interface_transaction_code := NULL;
i_voucher_num := 45678;
i_voucher_type_code := 'MISC';
i_org_id := 99;
i_acct_per_date := '20051031';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '400';
i_minor_acct := '100';
i_cc_num := 'CCJIB01';
i_reporting_curr_amt := 100;
i_actvy_per_date := '20051031';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
END;
/
COMMIT;
Journal Entry using INSERT_TEMP_INTERFACE_DATA() and Voucher creation
- To create a voucher add call to VALIDATE_VOUCHER() to your script. It launches ACTU213 and ACTU116 to create live vouchers.
o_return_status := EXTERNAL_TRANSACTIONS_API.VALIDATE_VOUCHER (io_report_id,'USER_ID',v_report_id,o_return_code,o_return_message);
Cash Receipt using PUT_LINE_ITEM()
SET SERVEROUTPUT ON
DECLARE
i_org_id temp_vouchers.org_id%TYPE := 99;
i_acct_per_date temp_vouchers.acct_per_date%TYPE := TO_DATE('2005/10/31','YYYY/MM/DD');
i_src_code temp_vouchers.src_code %TYPE := 'FIN';
i_voucher_type_code temp_vouchers.voucher_type_code%TYPE := 'MISC';
i_voucher_num temp_vouchers.voucher_num%TYPE := 9191;
i_curr_code temp_vouchers.curr_code%TYPE :='CAD';
i_voucher_rem temp_vouchers.voucher_rem%TYPE DEFAULT NULL;
i_external_reference temp_interface_data.external_reference%TYPE DEFAULT NULL;
i_user temp_vouchers.create_user%TYPE :='USER_ID';
--
-- this flag controls whether ACTU213 and ACTU116 are launched after data is inserted into
-- TEMP_INTERFACE_DATA
--
i_validate_voucher_flag VARCHAR2(1) := 'N';
-----------------------------------------------------------------------------------------------
o_report_id NUMBER;
i_major_acct temp_line_items.major_acct%TYPE := '400';
i_minor_acct temp_line_items.minor_acct%TYPE := '100';
i_cc_num temp_line_items.cc_num%TYPE := 'CCJIB01';
i_afe_num temp_line_items.afe_num%TYPE DEFAULT NULL;
i_actvy_per_date temp_line_items.actvy_per_date%TYPE := TO_DATE('2005/10/31','YYYY/MM/DD');
i_amount temp_line_items.reporting_curr_amt%TYPE := -100;
i_li_vol temp_line_items.li_vol%TYPE DEFAULT NULL;
i_govern_agr_id temp_line_items.govern_agr_id%TYPE DEFAULT NULL;
i_govern_agr_type_code temp_line_items.govern_agr_type_code%TYPE DEFAULT NULL;
i_dest_org_id temp_line_items.dest_org_id%TYPE DEFAULT NULL;
i_li_rem temp_line_items.li_rem%TYPE DEFAULT NULL;
i_gl_sub_code temp_line_items.gl_sub_code%TYPE DEFAULT NULL;
i_ba_id temp_invoices.client_id%TYPE DEFAULT NULL;
i_invc_num temp_invoices.invc_num%TYPE DEFAULT NULL;
i_invc_date temp_invoices.invc_date%TYPE DEFAULT NULL;
i_due_date temp_invoices.due_date%TYPE DEFAULT NULL;
i_alternate_address_id temp_invoices.alternate_address_id%TYPE DEFAULT NULL;
i_separate_chq_flag temp_invoices.separate_chq_flag%TYPE DEFAULT NULL;
i_invc_rem temp_invoices.invc_rem%TYPE DEFAULT NULL;
i_purchase_order_num temp_invoices.purchase_order_num%TYPE DEFAULT NULL;
i_bank_acct_id bank_accounts.bank_acct_id%TYPE := 1;
i_cash_txn_ba_id temp_cash_transactions.client_id%TYPE := 64;
i_chq_no temp_cash_transactions.client_issued_num%TYPE := 444;
i_chq_date temp_cash_transactions.client_issued_date%TYPE := TO_DATE('2005/10/11','YYYY/MM/DD');
i_chq_amt temp_cash_transactions.cash_tx_amt%TYPE := 100;
i_medium_code temp_cash_transactions.medium_code%TYPE := 'C';
i_bank_deposit_item_cash_tx_id temp_cash_transactions.bank_deposit_item_cash_tx_id%TYPE DEFAULT NULL;
o_li_count NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
o_return_status := external_transactions_api.initialize (
o_return_code,
o_return_message);
DBMS_OUTPUT.PUT_LINE('INITIALIZE o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
o_return_status := external_transactions_api.put_line_item
(
i_major_acct,
i_minor_acct,
i_cc_num,
i_afe_num,
i_actvy_per_date,
i_amount,
i_li_vol,
i_govern_agr_id,
i_govern_agr_type_code,
i_dest_org_id,
i_li_rem,
i_gl_sub_code,
i_ba_id,
i_invc_num,
i_invc_date,
i_due_date,
i_alternate_address_id,
i_separate_chq_flag,
i_invc_rem,
i_purchase_order_num,
i_bank_acct_id,
i_cash_txn_ba_id,
i_chq_no,
i_chq_date,
i_chq_amt,
i_medium_code,
i_bank_deposit_item_cash_tx_id,
o_li_count,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('PUT_LINE_ITEM o_li_count '||TO_CHAR(o_li_count));
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||TO_CHAR(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
o_return_status := external_transactions_api.create_voucher
(
i_org_id,
i_acct_per_date,
i_src_code,
i_voucher_type_code,
i_voucher_num,
i_curr_code,
i_voucher_rem,
i_external_reference,
i_user,
i_validate_voucher_flag,
o_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('CREATE_VOUCHER o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' o_report_id '||to_char(o_report_id));
END;
/
COMMIT;
Cash Receipt using PUT_LINE_ITEM() and Voucher creation
- Follow the same procedure as the previous example but set i_validate_voucher_flag to Y.
Cash Disbursement using INSERT_TEMP_INTERFACE_DATA(), Voucher creation
-- This example creates 1 Cheque to pay 2 Invoices
-- calls validate_voucher to create the voucher.
SET SERVEROUTPUT ON
DECLARE
v_count PLS_INTEGER;
v_report_id NUMBER;
i_new_batch_flag VARCHAR2(1) DEFAULT NULL;
i_interface_transaction_code TEMP_INTERFACE_DATA.INTERFACE_TRANSACTION_CODE%TYPE DEFAULT NULL;
i_voucher_num TEMP_INTERFACE_DATA.VOUCHER_NUM%TYPE DEFAULT NULL;
i_voucher_type_code TEMP_INTERFACE_DATA.VOUCHER_TYPE_CODE%TYPE DEFAULT NULL;
i_org_id TEMP_INTERFACE_DATA.ORG_ID%TYPE DEFAULT NULL;
i_acct_per_date VARCHAR2(8) DEFAULT NULL;
i_curr_code TEMP_INTERFACE_DATA.CURR_CODE%TYPE DEFAULT NULL;
i_src_code TEMP_INTERFACE_DATA.SRC_CODE%TYPE DEFAULT NULL;
i_src_module_id TEMP_INTERFACE_DATA.SRC_MODULE_ID%TYPE DEFAULT NULL;
i_voucher_rem TEMP_INTERFACE_DATA.VOUCHER_REM%TYPE DEFAULT NULL;
i_li_origin_code TEMP_INTERFACE_DATA.LI_ORIGIN_CODE%TYPE DEFAULT NULL;
i_dest_org_id TEMP_INTERFACE_DATA.DEST_ORG_ID%TYPE DEFAULT NULL;
i_major_acct TEMP_INTERFACE_DATA.MAJOR_ACCT%TYPE DEFAULT NULL;
i_minor_acct TEMP_INTERFACE_DATA.MINOR_ACCT%TYPE DEFAULT NULL;
i_afe_num TEMP_INTERFACE_DATA.AFE_NUM%TYPE DEFAULT NULL;
i_afe_item_num TEMP_INTERFACE_DATA.AFE_ITEM_NUM%TYPE DEFAULT NULL;
i_continuity_code TEMP_INTERFACE_DATA.CONTINUITY_CODE%TYPE DEFAULT NULL;
i_cc_num TEMP_INTERFACE_DATA.CC_NUM%TYPE DEFAULT NULL;
i_gl_sub_code TEMP_INTERFACE_DATA.GL_SUB_CODE%TYPE DEFAULT NULL;
i_actvy_per_date VARCHAR2(8) DEFAULT NULL;
i_reporting_curr_amt TEMP_INTERFACE_DATA.REPORTING_CURR_AMT%TYPE DEFAULT NULL;
i_reporting_curr_gst_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GST_AMT%TYPE DEFAULT NULL;
i_translation_rate TEMP_INTERFACE_DATA.TRANSLATION_RATE%TYPE DEFAULT NULL;
i_li_amt TEMP_INTERFACE_DATA.LI_AMT%TYPE DEFAULT NULL;
i_gst_amt TEMP_INTERFACE_DATA.GST_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_trans_rate TEMP_INTERFACE_DATA.ORG_REP_CURR_TRANSLATION_RATE%TYPE DEFAULT NULL;
i_org_rep_curr_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_AMT%TYPE DEFAULT NULL;
i_li_vol TEMP_INTERFACE_DATA.LI_VOL%TYPE DEFAULT NULL;
i_li_energy_amount TEMP_INTERFACE_DATA.LI_ENERGY_AMOUNT%TYPE DEFAULT NULL;
i_li_rem TEMP_INTERFACE_DATA.LI_REM%TYPE DEFAULT NULL;
i_govern_agr_id TEMP_INTERFACE_DATA.GOVERN_AGR_ID%TYPE DEFAULT NULL;
i_govern_agr_type_code TEMP_INTERFACE_DATA.GOVERN_AGR_TYPE_CODE%TYPE DEFAULT NULL;
i_reporting_curr_gross_up_amt TEMP_INTERFACE_DATA.REPORTING_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_amt TEMP_INTERFACE_DATA.GROSS_UP_AMT%TYPE DEFAULT NULL;
i_org_rep_curr_gross_up_amt TEMP_INTERFACE_DATA.ORG_REP_CURR_GROSS_UP_AMT%TYPE DEFAULT NULL;
i_gross_up_vol TEMP_INTERFACE_DATA.GROSS_UP_VOL%TYPE DEFAULT NULL;
i_gross_up_energy_val TEMP_INTERFACE_DATA.GROSS_UP_ENERGY_VAL%TYPE DEFAULT NULL;
i_src_invc_id TEMP_INTERFACE_DATA.SRC_INVC_ID%TYPE DEFAULT NULL;
i_invc_origin_code TEMP_INTERFACE_DATA.INVC_ORIGIN_CODE%TYPE DEFAULT NULL;
i_ba_id TEMP_INTERFACE_DATA.CLIENT_ID%TYPE DEFAULT NULL;
i_alternate_address_id TEMP_INTERFACE_DATA.ALTERNATE_ADDRESS_ID%TYPE DEFAULT NULL;
i_invc_num TEMP_INTERFACE_DATA.INVC_NUM%TYPE DEFAULT NULL;
i_invc_date VARCHAR2(8) DEFAULT NULL;
i_invc_amt TEMP_INTERFACE_DATA.INVC_AMT%TYPE DEFAULT NULL;
i_due_date VARCHAR2(8) DEFAULT NULL;
i_hold_date VARCHAR2(8) DEFAULT NULL;
i_invc_rem TEMP_INTERFACE_DATA.INVC_REM%TYPE DEFAULT NULL;
i_invoices_alternate_ba_id TEMP_INTERFACE_DATA.INVOICES_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_purchase_order_num TEMP_INTERFACE_DATA.PURCHASE_ORDER_NUM%TYPE DEFAULT NULL;
i_contract_num TEMP_INTERFACE_DATA.CONTRACT_NUM%TYPE DEFAULT NULL;
i_payment_code TEMP_INTERFACE_DATA.PAYMENT_CODE%TYPE DEFAULT NULL;
i_payment_format_code TEMP_INTERFACE_DATA.PAYMENT_FORMAT_CODE%TYPE DEFAULT NULL;
i_payment_handling_code TEMP_INTERFACE_DATA.PAYMENT_HANDLING_CODE%TYPE DEFAULT NULL;
i_separate_chq_flag TEMP_INTERFACE_DATA.SEPARATE_CHQ_FLAG%TYPE DEFAULT NULL;
i_chq_mail_type_code TEMP_INTERFACE_DATA.CHQ_MAIL_TYPE_CODE%TYPE DEFAULT NULL;
i_attachment_reqd_flag TEMP_INTERFACE_DATA.ATTACHMENT_REQD_FLAG%TYPE DEFAULT NULL;
i_invc_approval_short_name TEMP_INTERFACE_DATA.INVC_APPROVAL_SHORT_NAME%TYPE DEFAULT NULL;
i_env_code TEMP_INTERFACE_DATA.ENV_CODE%TYPE DEFAULT NULL;
i_user_defined_tag TEMP_INTERFACE_DATA.USER_DEFINED_TAG%TYPE DEFAULT NULL;
i_alternate_gl_code TEMP_INTERFACE_DATA.ALTERNATE_GL_CODE%TYPE DEFAULT NULL;
i_li_alternate_ba_id TEMP_INTERFACE_DATA.LINE_ITEMS_ALTERNATE_CLIENT_ID%TYPE DEFAULT NULL;
i_footnote TEMP_INTERFACE_DATA.FOOTNOTE%TYPE DEFAULT NULL;
i_billed_date VARCHAR2(8) DEFAULT NULL;
i_spreadsheet_line_num TEMP_INTERFACE_DATA.SPREADSHEET_LINE_NUM%TYPE DEFAULT NULL;
i_payable_or_receivable_code TEMP_CASH_TRANSACTIONS.PAYABLE_OR_RECEIVABLE_CODE%TYPE DEFAULT NULL;
i_medium_code TEMP_CASH_TRANSACTIONS.MEDIUM_CODE%TYPE DEFAULT NULL;
i_cash_tx_amt TEMP_CASH_TRANSACTIONS.CASH_TX_AMT%TYPE DEFAULT NULL;
i_cash_tx_date VARCHAR2(8) DEFAULT NULL;
i_bank_acct_id TEMP_CASH_TRANSACTIONS.BANK_ACCT_ID%TYPE DEFAULT NULL;
i_cash_tx_ba_id TEMP_CASH_TRANSACTIONS.CLIENT_ID%TYPE DEFAULT NULL;
i_cash_tx_stat_code TEMP_CASH_TRANSACTIONS.CASH_TX_STAT_CODE%TYPE DEFAULT NULL;
i_chq_cleared_amt TEMP_CASH_TRANSACTIONS.CHQ_CLEARED_AMT%TYPE DEFAULT NULL;
i_chq_cleared_date VARCHAR2(8) DEFAULT NULL;
i_chq_micr_num TEMP_CASH_TRANSACTIONS.CHQ_MICR_NUM%TYPE DEFAULT NULL;
i_ba_issued_date VARCHAR2(8) DEFAULT NULL;
i_ba_issued_num TEMP_CASH_TRANSACTIONS.CLIENT_ISSUED_NUM%TYPE DEFAULT NULL;
i_eft_file_num TEMP_CASH_TRANSACTIONS.EFT_FILE_NUM%TYPE DEFAULT NULL;
i_bank_deposit_item_cash_tx_id TEMP_CASH_TRANSACTIONS.BANK_DEPOSIT_ITEM_CASH_TX_ID%TYPE DEFAULT NULL;
i_external_reference TEMP_INTERFACE_DATA.EXTERNAL_REFERENCE%TYPE DEFAULT NULL;
i_user TEMP_INTERFACE_DATA.CREATE_USER%TYPE := 'USER_ID';
io_report_id NUMBER;
o_return_code NUMBER;
o_return_message VARCHAR2(240);
o_return_status VARCHAR2(5);
BEGIN
i_new_batch_flag := 'Y';
i_interface_transaction_code := 'JEI';
i_voucher_num := 50;
i_voucher_type_code := 'CR';
i_org_id := 700;
i_acct_per_date := '20070131';
i_actvy_per_date := '';
i_curr_code := 'CAD';
i_src_code := 'FIN';
-- Cash Disbursement - Cheque
--
-- Account is a cash account associated with a Bank Account
--
i_major_acct := '005';
i_minor_acct := '39';
i_reporting_curr_amt := -900;
i_medium_code := 'C';
i_cash_tx_amt := -900;
i_cash_tx_date := '20070123';
i_bank_acct_id := 78;
i_cash_tx_ba_id := 6;
i_cash_tx_stat_code := 'ISS';
i_ba_issued_date := '20070123';
i_ba_issued_num := 'B511';
i_chq_micr_num := 1237;
--
--
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
-- Offset. This identifies the invoice and amount being paid
i_new_batch_flag := 'N';
i_interface_transaction_code := 'JEI';
i_voucher_num := 50;
i_voucher_type_code := 'CR';
i_org_id := 700;
i_acct_per_date := '20070131';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '3500';
i_minor_acct := '776';
i_reporting_curr_amt := 700;
i_ba_id := 5;
i_alternate_address_id := 42509;
i_invc_num := '123FF';
i_invc_date := '20070101';
i_invc_amt := 700;
i_actvy_per_date := '';
--- Set Cheque parameters to null with exception of BA_ISSUED_NUM and CASH_TX_BA_ID
-- in order to link Cheque offsets
--
i_medium_code := NULL;
i_cash_tx_amt := NULL;
i_cash_tx_date := NULL;
i_bank_acct_id := NULL;
i_cash_tx_ba_id := 5;
i_cash_tx_stat_code := NULL;
i_ba_issued_date := NULL;
i_ba_issued_num := 'B511';
i_actvy_per_date := '';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
-- Offset. This identifies the invoice and amount being paid
i_new_batch_flag := 'N';
i_interface_transaction_code := 'JEI';
i_voucher_num := 50;
i_voucher_type_code := 'CR';
i_org_id := 700;
i_acct_per_date := '20070131';
i_curr_code := 'CAD';
i_src_code := 'FIN';
i_major_acct := '3500';
i_minor_acct := '776';
i_reporting_curr_amt := 200;
i_ba_id := 5;
i_alternate_address_id := 42509;
i_invc_num := '123AB';
i_invc_date := '20070101';
i_invc_amt := 200;
i_actvy_per_date := '';
--- Set Cheque parameters to null with exception of BA_ISSUED_NUM and CASH_TX_BA_ID
-- in order to link Cheque offset
--
i_medium_code := NULL;
i_cash_tx_amt := NULL;
i_cash_tx_date := NULL;
i_bank_acct_id := NULL;
i_cash_tx_ba_id := 5;
i_cash_tx_stat_code := NULL;
i_ba_issued_date := NULL;
i_ba_issued_num := 'B511';
i_actvy_per_date := '';
o_return_status := external_transactions_api.insert_temp_interface_data
(
i_new_batch_flag,
i_interface_transaction_code,
i_voucher_num,
i_voucher_type_code,
i_org_id,
i_acct_per_date,
i_curr_code,
i_src_code,
i_src_module_id,
i_voucher_rem,
i_li_origin_code,
i_dest_org_id,
i_major_acct,
i_minor_acct,
i_afe_num,
i_afe_item_num,
i_continuity_code,
i_cc_num,
i_gl_sub_code,
i_actvy_per_date,
i_reporting_curr_amt,
i_reporting_curr_gst_amt,
i_translation_rate,
i_li_amt,
i_gst_amt,
i_org_rep_curr_trans_rate,
i_org_rep_curr_amt,
i_li_vol,
i_li_energy_amount,
i_li_rem,
i_govern_agr_id,
i_govern_agr_type_code,
i_reporting_curr_gross_up_amt,
i_gross_up_amt,
i_org_rep_curr_gross_up_amt,
i_gross_up_vol,
i_gross_up_energy_val,
i_src_invc_id,
i_invc_origin_code,
i_ba_id,
i_alternate_address_id,
i_invc_num,
i_invc_date,
i_invc_amt,
i_due_date,
i_hold_date,
i_invc_rem,
i_invoices_alternate_ba_id,
i_purchase_order_num,
i_contract_num,
i_payment_code,
i_payment_format_code,
i_payment_handling_code,
i_separate_chq_flag,
i_chq_mail_type_code,
i_attachment_reqd_flag,
i_invc_approval_short_name,
i_env_code,
i_user_defined_tag,
i_alternate_gl_code,
i_li_alternate_ba_id,
i_footnote,
i_billed_date,
i_spreadsheet_line_num,
i_payable_or_receivable_code,
i_medium_code,
i_cash_tx_amt,
i_cash_tx_date,
i_bank_acct_id,
i_cash_tx_ba_id,
i_cash_tx_stat_code,
i_chq_cleared_amt,
i_chq_cleared_date,
i_chq_micr_num,
i_ba_issued_date,
i_ba_issued_num,
i_eft_file_num,
i_bank_deposit_item_cash_tx_id,
i_external_reference,
i_user,
io_report_id,
o_return_code,
o_return_message
);
DBMS_OUTPUT.PUT_LINE('INSERT_TEMP_INTERFACE_DATA');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' io_report_id '||to_char(io_report_id));
SELECT COUNT(*) INTO v_count FROM TEMP_INTERFACE_DATA WHERE REPORT_ID = io_report_id;
DBMS_OUTPUT.PUT_LINE(NVL(v_count,0)||' lines inserted into TEMP_INTERFACE_DATA');
o_return_status :=
EXTERNAL_TRANSACTIONS_API.VALIDATE_VOUCHER(io_report_id,'USER_ID',v_report_id,o_return_code,o_return_message);
DBMS_OUTPUT.PUT_LINE('VALIDATE_VOUCHER');
DBMS_OUTPUT.PUT_LINE(' o_return_status '||o_return_status);
DBMS_OUTPUT.PUT_LINE(' o_return_code '||to_char(o_return_code));
DBMS_OUTPUT.PUT_LINE(' o_return_message '||o_return_message);
DBMS_OUTPUT.PUT_LINE(' v_report_id '||to_char(v_report_id));
END;
/
COMMIT;
external_transactions_api.INSERT_TEMP_INTERFACE_DATA()
- 114 parameters function (115 parameters procedure).
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
- There are six overloaded versions of insert_temp_interface_data() routine, learn their differences described in this manual in order to use them properly with named notation, or use positional notation when calling overloaded routines.
- You must supply i_bank_deposit_num parameter to use this routine.
Use this routine if the organization requires to make a cash transaction via the TDEU001 spreadsheet or a cash transaction when the bank deposit number is known but the bank_cash_tx_id is not known.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_new_batch_flag | IN | VARCHAR2(1) | |
i_interface_transaction_code | IN | VARCHAR2(10) | NULL |
i_voucher_num | IN | NUMBER(5) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | VARCHAR2 | |
i_curr_code | IN | VARCHAR2(6) | |
i_src_code | IN | VARCHAR2(6) | |
i_src_module_id | IN | VARCHAR2(20) | NULL |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_li_origin_code | IN | VARCHAR2(3) | NULL. ‘ING’ (Interface Generated) is the recommended value. |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_afe_item_num | IN | VARCHAR2(6) | NULL |
i_continuity_code | IN | VARCHAR2(6) | NULL |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_gl_sub_code | IN | VARCHAR2(6) | NULL |
i_actvy_per_date | IN | VARCHAR2 | NULL |
i_reporting_curr_amt | IN | NUMBER(14,2) | |
i_reporting_curr_gst_amt | IN | NUMBER(14,2) | NULL |
i_translation_rate | IN | NUMBER(14,10) | NULL |
i_li_amt | IN | NUMBER(14,2) | NULL |
i_gst_amt | IN | NUMBER(14,2) | NULL |
i_org_rep_curr_trans_rate | IN | NUMBER(14,10) | NULL |
i_org_rep_curr_amt | IN | NUMBER(14,2) | NULL |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_li_energy_amount | IN | NUMBER(12,2) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_reporting_curr_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_org_rep_curr_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_gross_up_vol | IN | NUMBER(12,2) | NULL |
i_gross_up_energy_val | IN | NUMBER(12,2) | NULL |
i_src_invc_id | IN | NUMBER(10) | NULL |
i_invc_origin_code | IN | VARCHAR2(3) | NULL. ‘INI’ (Interface Input) is the recommended value. |
i_ba_id | IN | NUMBER(6) | NULL |
i_alternate_address_id | IN | NUMBER(6) | NULL |
i_invc_num | IN | VARCHAR2(20) | NULL |
i_invc_date | IN | VARCHAR2 | NULL |
i_invc_amt | IN | NUMBER(14,2) | NULL |
i_due_date | IN | VARCHAR2 | NULL |
i_hold_date | IN | VARCHAR2 | NULL |
i_invc_rem | IN | VARCHAR2(120) | NULL |
i_invoices_alternate_ba_id | IN | NUMBER(6) | NULL |
i_purchase_order_num | IN | VARCHAR2(20) | NULL |
i_contract_num | IN | VARCHAR2(20) | NULL |
i_payment_code | IN | VARCHAR2(3) | NULL |
i_payment_format_code | IN | VARCHAR2(6) | NULL |
i_payment_handling_code | IN | VARCHAR2(2) | NULL |
i_separate_chq_flag | IN | VARCHAR2(1) | NULL |
i_chq_mail_type_code | IN | VARCHAR2(2) | NULL |
i_attachment_reqd_flag | IN | VARCHAR2(1) | NULL |
i_invc_approval_short_name | IN | VARCHAR2(15) | NULL |
i_env_code | IN | VARCHAR2(6) | NULL |
i_user_defined_tag | IN | VARCHAR2(10) | NULL |
i_alternate_gl_code | IN | VARCHAR2(10) | NULL |
i_li_alternate_ba_id | IN | NUMBER(6) | NULL |
i_footnote | IN | VARCHAR2(2000) | NULL |
i_billed_date | IN | VARCHAR2 | NULL |
i_spreadsheet_line_num | IN | NUMBER(10) | NULL |
i_payable_or_receivable_code | IN | VARCHAR2(1) | NULL |
i_medium_code | IN | VARCHAR2(1) | NULL |
i_cash_tx_amt | IN | NUMBER(14,2) | NULL |
i_cash_tx_date | IN | VARCHAR2 | NULL |
i_bank_acct_id | IN | NUMBER(4) | NULL |
i_cash_tx_ba_id | IN | NUMBER(6) | NULL |
i_cash_tx_stat_code | IN | VARCHAR2(3) | NULL |
i_chq_cleared_amt | IN | NUMBER(14,2) | NULL |
i_chq_cleared_date | IN | VARCHAR2 | NULL |
i_chq_micr_num | IN | NUMBER(8) | NULL |
i_ba_issued_date | IN | VARCHAR2 | NULL |
i_ba_issued_num | IN | VARCHAR2(10) | NULL |
i_eft_file_num | IN | NUMBER(4) | NULL |
i_bank_deposit_item_cash_tx_id | IN | NUMBER(10) | NULL |
i_bank_deposit_num | IN | NUMBER(10) | |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_pay_stat_code | IN | VARCHAR2(1) | |
i_priority_code | IN | VARCHAR2(1) | NULL |
i_rcvd_date | IN | VARCHAR2 | NULL |
i_sent_for_approval_date | IN | VARCHAR2 | NULL |
i_approval_date | IN | VARCHAR2 | NULL |
i_approval_rem | IN | VARCHAR2(60) | NULL |
i_sent_to | IN | VARCHAR2(30) | NULL |
i_invc_gst_amt | IN | NUMBER(14,2) | NULL |
i_invc_gst_factor | IN | NUMBER(14,10) | NULL |
i_invc_type_code | IN | VARCHAR2(3) | NULL |
i_release_for_payment_amt | IN | NUMBER(14,2) | NULL |
i_invc_afe_num | IN | VARCHAR2(10) | NULL |
i_invc_cc_num | IN | VARCHAR2(10) | NULL |
i_nrwt_amt | IN | NUMBER(14,2) | NULL |
i_pst_amt | IN | NUMBER(14 | NULL |
i_gst_distribution_amt | IN | NUMBER(14 | NULL |
i_discount_due_date | IN | VARCHAR2 | NULL |
i_discount_amt | IN | NUMBER(14,2) | NULL |
i_discount_achieved | IN | VARCHAR2(1) | NULL |
i_misc_income_amt | IN | NUMBER(14,2) | NULL |
i_withholding_tax_amt | IN | NUMBER(14,2) | NULL |
i_gst_factor | IN | NUMBER(14,10) | NULL |
i_src_agr_id | IN | NUMBER(10) | NULL |
i_src_agr_type_code | IN | VARCHAR2(2) | NULL |
i_et_id | IN | NUMBER(10) | NULL |
i_li_as_entered_vol | IN | NUMBER(12,2) | NULL |
i_pre_tax_amt | IN | NUMBER(14,2) | NULL |
i_warehouse_handling_flag | IN | VARCHAR2(1) | NULL |
i_li_discount_amt | IN | NUMBER(14,2) | NULL |
i_misc_income_category | IN | VARCHAR2(30) | NULL |
i_user | IN | VARCHAR2(32) | |
io_report_id | IN OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INSERT_TEMP_INTERFACE_DATA()
- 113 parameters function (114 parameters procedure).
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
- You must supply i_pay_stat_code parameter to use this routine.
- Use this routine if organization requires non standard volume entry and validation.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_new_batch_flag | IN | VARCHAR2(1) | |
i_interface_transaction_code | IN | VARCHAR2(10) | NULL |
i_voucher_num | IN | NUMBER(5) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | VARCHAR2 | |
i_curr_code | IN | VARCHAR2(6) | |
i_src_code | IN | VARCHAR2(6) | |
i_src_module_id | IN | VARCHAR2(20) | NULL |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_li_origin_code | IN | VARCHAR2(3) | NULL. ‘ING’ (Interface Generated) is the recommended value. |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_afe_item_num | IN | VARCHAR2(6) | NULL |
i_continuity_code | IN | VARCHAR2(6) | NULL |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_gl_sub_code | IN | VARCHAR2(6) | NULL |
i_actvy_per_date | IN | VARCHAR2 | NULL |
i_reporting_curr_amt | IN | NUMBER(14,2) | |
i_reporting_curr_gst_amt | IN | NUMBER(14,2) | NULL |
i_translation_rate | IN | NUMBER(14,10) | NULL |
i_li_amt | IN | NUMBER(14,2) | NULL |
i_gst_amt | IN | NUMBER(14,2) | NULL |
i_org_rep_curr_trans_rate | IN | NUMBER(14,10) | NULL |
i_org_rep_curr_amt | IN | NUMBER(14,2) | NULL |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_li_energy_amount | IN | NUMBER(12,2) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_reporting_curr_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_org_rep_curr_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_gross_up_vol | IN | NUMBER(12,2) | NULL |
i_gross_up_energy_val | IN | NUMBER(12,2) | NULL |
i_src_invc_id | IN | NUMBER(10) | NULL |
i_invc_origin_code | IN | VARCHAR2(3) | NULL. ‘INI’ (Interface Input) is the recommended value. |
i_ba_id | IN | NUMBER(6) | NULL |
i_alternate_address_id | IN | NUMBER(6) | NULL |
i_invc_num | IN | VARCHAR2(20) | NULL |
i_invc_date | IN | VARCHAR2 | NULL |
i_invc_amt | IN | NUMBER(14,2) | NULL |
i_due_date | IN | VARCHAR2 | NULL |
i_hold_date | IN | VARCHAR2 | NULL |
i_invc_rem | IN | VARCHAR2(120) | NULL |
i_invoices_alternate_ba_id | IN | NUMBER(6) | NULL |
i_purchase_order_num | IN | VARCHAR2(20) | NULL |
i_contract_num | IN | VARCHAR2(20) | NULL |
i_payment_code | IN | VARCHAR2(3) | NULL |
i_payment_format_code | IN | VARCHAR2(6) | NULL |
i_payment_handling_code | IN | VARCHAR2(2) | NULL |
i_separate_chq_flag | IN | VARCHAR2(1) | NULL |
i_chq_mail_type_code | IN | VARCHAR2(2) | NULL |
i_attachment_reqd_flag | IN | VARCHAR2(1) | NULL |
i_invc_approval_short_name | IN | VARCHAR2(15) | NULL |
i_env_code | IN | VARCHAR2(6) | NULL |
i_user_defined_tag | IN | VARCHAR2(10) | NULL |
i_alternate_gl_code | IN | VARCHAR2(10) | NULL |
i_li_alternate_ba_id | IN | NUMBER(6) | NULL |
i_footnote | IN | VARCHAR2(2000) | NULL |
i_billed_date | IN | VARCHAR2 | NULL |
i_spreadsheet_line_num | IN | NUMBER(10) | NULL |
i_payable_or_receivable_code | IN | VARCHAR2(1) | NULL |
i_medium_code | IN | VARCHAR2(1) | NULL |
i_cash_tx_amt | IN | NUMBER(14,2) | NULL |
i_cash_tx_date | IN | VARCHAR2 | NULL |
i_bank_acct_id | IN | NUMBER(4) | NULL |
i_cash_tx_ba_id | IN | NUMBER(6) | NULL |
i_cash_tx_stat_code | IN | VARCHAR2(3) | NULL |
i_chq_cleared_amt | IN | NUMBER(14,2) | NULL |
i_chq_cleared_date | IN | VARCHAR2 | NULL |
i_chq_micr_num | IN | NUMBER(8) | NULL |
i_ba_issued_date | IN | VARCHAR2 | NULL |
i_ba_issued_num | IN | VARCHAR2(10) | NULL |
i_eft_file_num | IN | NUMBER(4) | NULL |
i_bank_deposit_item_cash_tx_id | IN | NUMBER(10) | NULL |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_pay_stat_code | IN | VARCHAR2(1) | |
i_priority_code | IN | VARCHAR2(1) | NULL |
i_rcvd_date | IN | VARCHAR2 | NULL |
i_sent_for_approval_date | IN | VARCHAR2 | NULL |
i_approval_date | IN | VARCHAR2 | NULL |
i_approval_rem | IN | VARCHAR2(60) | NULL |
i_sent_to | IN | VARCHAR2(30) | NULL |
i_invc_gst_amt | IN | NUMBER(14,2) | NULL |
i_invc_gst_factor | IN | NUMBER(14,10) | NULL |
i_invc_type_code | IN | VARCHAR2(3) | NULL |
i_release_for_payment_amt | IN | NUMBER(14,2) | NULL |
i_invc_afe_num | IN | VARCHAR2(10) | NULL |
i_invc_cc_num | IN | VARCHAR2(10) | NULL |
i_nrwt_amt | IN | NUMBER(14,2) | NULL |
i_pst_amt | IN | NUMBER(14 | NULL |
i_gst_distribution_amt | IN | NUMBER(14 | NULL |
i_discount_due_date | IN | VARCHAR2 | NULL |
i_discount_amt | IN | NUMBER(14,2) | NULL |
i_discount_achieved | IN | VARCHAR2(1) | NULL |
i_misc_income_amt | IN | NUMBER(14,2) | NULL |
i_withholding_tax_amt | IN | NUMBER(14,2) | NULL |
i_gst_factor | IN | NUMBER(14,10) | NULL |
i_src_agr_id | IN | NUMBER(10) | NULL |
i_src_agr_type_code | IN | VARCHAR2(2) | NULL |
i_et_id | IN | NUMBER(10) | NULL |
i_li_as_entered_vol | IN | NUMBER(12,2) | NULL |
i_pre_tax_amt | IN | NUMBER(14,2) | NULL |
i_warehouse_handling_flag | IN | VARCHAR2(1) | NULL |
i_li_discount_amt | IN | NUMBER(14,2) | NULL |
i_misc_income_category | IN | VARCHAR2(30) | NULL |
i_user | IN | VARCHAR2(32) | |
io_report_id | IN OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INSERT_TEMP_INTERFACE_DATA()
- Original 83 parameter function (84 parameters procedure).
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Do not use this routine if organization requires non standard volume entry and validation.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_new_batch_flag | IN | VARCHAR2(1) | |
i_interface_transaction_code | IN | VARCHAR2(10) | NULL |
i_voucher_num | IN | NUMBER(5) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | VARCHAR2 | |
i_curr_code | IN | VARCHAR2(6) | |
i_src_code | IN | VARCHAR2(6) | |
i_src_module_id | IN | VARCHAR2(20) | NULL |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_li_origin_code | IN | VARCHAR2(3) | NULL. ‘ING’ (Interface Generated) is the recommended value. |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_afe_item_num | IN | VARCHAR2(6) | NULL |
i_continuity_code | IN | VARCHAR2(6) | NULL |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_gl_sub_code | IN | VARCHAR2(6) | NULL |
i_actvy_per_date | IN | VARCHAR2 | NULL |
i_reporting_curr_amt | IN | NUMBER(14,2) | |
i_reporting_curr_gst_amt | IN | NUMBER(14,2) | NULL |
i_translation_rate | IN | NUMBER(14,10) | NULL |
i_li_amt | IN | NUMBER(14,2) | NULL |
i_gst_amt | IN | NUMBER(14,2) | NULL |
i_org_rep_curr_trans_rate | IN | NUMBER(14,10) | NULL |
i_org_rep_curr_amt | IN | NUMBER(14,2) | NULL |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_li_energy_amount | IN | NUMBER(12,2) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_reporting_curr_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_org_rep_curr_gross_up_amt | IN | NUMBER(14,2) | NULL |
i_gross_up_vol | IN | NUMBER(12,2) | NULL |
i_gross_up_energy_val | IN | NUMBER(12,2) | NULL |
i_src_invc_id | IN | NUMBER(10) | NULL |
i_invc_origin_code | IN | VARCHAR2(3) | NULL. ‘INI’ (Interface Input) is the recommended value. |
i_ba_id | IN | NUMBER(6) | NULL |
i_alternate_address_id | IN | NUMBER(6) | NULL |
i_invc_num | IN | VARCHAR2(20) | NULL |
i_invc_date | IN | VARCHAR2 | NULL |
i_invc_amt | IN | NUMBER(14,2) | NULL |
i_due_date | IN | VARCHAR2 | NULL |
i_hold_date | IN | VARCHAR2 | NULL |
i_invc_rem | IN | VARCHAR2(120) | NULL |
i_invoices_alternate_ba_id | IN | NUMBER(6) | NULL |
i_purchase_order_num | IN | VARCHAR2(20) | NULL |
i_contract_num | IN | VARCHAR2(20) | NULL |
i_payment_code | IN | VARCHAR2(3) | NULL |
i_payment_format_code | IN | VARCHAR2(6) | NULL |
i_payment_handling_code | IN | VARCHAR2(2) | NULL |
i_separate_chq_flag | IN | VARCHAR2(1) | NULL |
i_chq_mail_type_code | IN | VARCHAR2(2) | NULL |
i_attachment_reqd_flag | IN | VARCHAR2(1) | NULL |
i_invc_approval_short_name | IN | VARCHAR2(15) | NULL |
i_env_code | IN | VARCHAR2(6) | NULL |
i_user_defined_tag | IN | VARCHAR2(10) | NULL |
i_alternate_gl_code | IN | VARCHAR2(10) | NULL |
i_li_alternate_ba_id | IN | NUMBER(6) | NULL |
i_footnote | IN | VARCHAR2(2000) | NULL |
i_billed_date | IN | VARCHAR2 | NULL |
i_spreadsheet_line_num | IN | NUMBER(10) | NULL |
i_payable_or_receivable_code | IN | VARCHAR2(1) | NULL |
i_medium_code | IN | VARCHAR2(1) | NULL |
i_cash_tx_amt | IN | NUMBER(14,2) | NULL |
i_cash_tx_date | IN | VARCHAR2 | NULL |
i_bank_acct_id | IN | NUMBER(4) | NULL |
i_cash_tx_ba_id | IN | NUMBER(6) | NULL |
i_cash_tx_stat_code | IN | VARCHAR2(3) | NULL |
i_chq_cleared_amt | IN | NUMBER(14,2) | NULL |
i_chq_cleared_date | IN | VARCHAR2 | NULL |
i_chq_micr_num | IN | NUMBER(8) | NULL |
i_ba_issued_date | IN | VARCHAR2 | NULL |
i_ba_issued_num | IN | VARCHAR2(10) | NULL |
i_eft_file_num | IN | NUMBER(4) | NULL |
i_bank_deposit_item_cash_tx_id | IN | NUMBER(10) | NULL |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_user | IN | VARCHAR2(32) | |
io_report_id | IN OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INSERT_TEMP_INTERFACE_DATA()
- 58 parameter function (59 parameters procedure).
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
- You must supply i_cash_tx_amt parameter to use this routine.
Do not use this routine if organization requires non standard volume entry and validation.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_new_batch_flag | IN | VARCHAR2(1) | |
i_interface_transaction_code | IN | VARCHAR2(10) | NULL |
i_voucher_num | IN | NUMBER(5) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | VARCHAR2 | |
i_curr_code | IN | VARCHAR2(6) | |
i_src_code | IN | VARCHAR2(6) | |
i_src_module_id | IN | VARCHAR2(20) | NULL |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_li_origin_code | IN | VARCHAR2(3) | NULL. ‘ING’ (Interface Generated) is the recommended value. |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_afe_item_num | IN | VARCHAR2(6) | NULL |
i_continuity_code | IN | VARCHAR2(6) | NULL |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_gl_sub_code | IN | VARCHAR2(6) | NULL |
i_actvy_per_date | IN | VARCHAR2 | NULL |
i_amt | IN | NUMBER(14,2) | |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_src_invc_id | IN | NUMBER(10) | NULL |
i_invc_origin_code | IN | VARCHAR2(3) | NULL. ‘INI’ (Interface Input) is the recommended value. |
i_ba_id | IN | NUMBER(6) | NULL |
i_alternate_address_id | IN | NUMBER(6) | NULL |
i_invc_num | IN | VARCHAR2(20) | NULL |
i_invc_date | IN | VARCHAR2 | NULL |
i_invc_amt | IN | NUMBER(14,2) | NULL |
i_due_date | IN | VARCHAR2 | NULL |
i_hold_date | IN | VARCHAR2 | NULL |
i_invc_rem | IN | VARCHAR2(120) | NULL |
i_invoices_alternate_ba_id | IN | NUMBER(6) | NULL |
i_invc_approval_short_name | IN | VARCHAR2(15) | NULL |
i_spreadsheet_line_num | IN | NUMBER(10) | NULL |
i_payable_or_receivable_code | IN | VARCHAR2(1) | NULL |
i_medium_code | IN | VARCHAR2(1) | NULL |
i_cash_tx_amt | IN | NUMBER(14,2) |
|
i_cash_tx_date | IN | VARCHAR2 | NULL |
i_bank_acct_id | IN | NUMBER(4) | NULL |
i_cash_tx_ba_id | IN | NUMBER(6) | NULL |
i_cash_tx_stat_code | IN | VARCHAR2(3) | NULL |
i_chq_cleared_amt | IN | NUMBER(14,2) | NULL |
i_chq_cleared_date | IN | VARCHAR2 | NULL |
i_chq_micr_num | IN | NUMBER(8) | NULL |
i_ba_issued_date | IN | VARCHAR2 | NULL |
i_ba_issued_num | IN | VARCHAR2(10) | NULL |
i_eft_file_num | IN | NUMBER(4) | NULL |
i_bank_deposit_item_cash_tx_id | IN | NUMBER(10) | NULL |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_pay_stat_code | IN | VARCHAR2(1) | NULL |
i_user | IN | VARCHAR2(32) | |
io_report_id | IN OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INSERT_TEMP_INTERFACE_DATA()
- 45 parameter function (46 parameters procedure).
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
- You must supply i_invc_amt parameter to use this routine.
- Do not use this routine if organization requires non standard volume entry and validation.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_new_batch_flag | IN | VARCHAR2(1) | |
i_interface_transaction_code | IN | VARCHAR2(10) | NULL |
i_voucher_num | IN | NUMBER(5) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | VARCHAR2 | |
i_curr_code | IN | VARCHAR2(6) | |
i_src_code | IN | VARCHAR2(6) | |
i_src_module_id | IN | VARCHAR2(20) | NULL |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_li_origin_code | IN | VARCHAR2(3) | NULL. ‘ING’ (Interface Generated) is the recommended value. |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_afe_item_num | IN | VARCHAR2(6) | NULL |
i_continuity_code | IN | VARCHAR2(6) | NULL |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_gl_sub_code | IN | VARCHAR2(6) | NULL |
i_actvy_per_date | IN | VARCHAR2 | NULL |
i_amt | IN | NUMBER(14,2) | |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_src_invc_id | IN | NUMBER(10) | NULL |
i_invc_origin_code | IN | VARCHAR2(3) | NULL. ‘INI’ (Interface Input) is the recommended value. |
i_ba_id | IN | NUMBER(6) | NULL |
i_alternate_address_id | IN | NUMBER(6) | NULL |
i_invc_num | IN | VARCHAR2(20) | NULL |
i_invc_date | IN | VARCHAR2 | NULL |
i_invc_amt | IN | NUMBER(14,2) |
|
i_due_date | IN | VARCHAR2 | NULL |
i_hold_date | IN | VARCHAR2 | NULL |
i_invc_rem | IN | VARCHAR2(120) | NULL |
i_invoices_alternate_ba_id | IN | NUMBER(6) | NULL |
i_invc_approval_short_name | IN | VARCHAR2(15) | NULL |
i_spreadsheet_line_num | IN | NUMBER(10) | NULL |
i_payable_or_receivable_code | IN | VARCHAR2(1) | NULL |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_pay_stat_code | IN | VARCHAR2(1) | NULL |
i_user | IN | VARCHAR2(32) | |
io_report_id | IN OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INSERT_TEMP_INTERFACE_DATA()
- 31 parameter function (32 parameters procedure).
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
- Do not use this routine if organization requires non standard volume entry and validation.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_new_batch_flag | IN | VARCHAR2(1) | |
i_interface_transaction_code | IN | VARCHAR2(10) | NULL |
i_voucher_num | IN | NUMBER(5) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | VARCHAR2 | |
i_curr_code | IN | VARCHAR2(6) | |
i_src_code | IN | VARCHAR2(6) | |
i_src_module_id | IN | VARCHAR2(20) | NULL |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_li_origin_code | IN | VARCHAR2(3) | NULL. ‘ING’ (Interface Generated) is the recommended value. |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_afe_item_num | IN | VARCHAR2(6) | NULL |
i_continuity_code | IN | VARCHAR2(6) | NULL |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_gl_sub_code | IN | VARCHAR2(6) | NULL |
i_actvy_per_date | IN | VARCHAR2 | NULL |
i_amt | IN | NUMBER(14,2) | |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_spreadsheet_line_num | IN | NUMBER(10) | NULL |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_user | IN | VARCHAR2(32) |
|
io_report_id | IN OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INSERT_TEMP_INTF_INV_DESCS()
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_report_id | IN | NUMBER(10) | |
i_invc_num | IN | VARCHAR2(20) | |
i_invc_date | IN | VARCHAR2 | |
i_ba_id | IN | NUMBER(6) | |
i_payable_or_receivable_code | IN | VARCHAR2(1) | |
i_invc_desc | IN | VARCHAR2(60) | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.INITIALIZE()
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api. PUT_LINE_ITEM()
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_major_acct | IN | VARCHAR2(4) | |
i_minor_acct | IN | VARCHAR2(4) | |
i_cc_num | IN | VARCHAR2(10) | NULL |
i_afe_num | IN | VARCHAR2(10) | NULL |
i_actvy_per_date | IN | DATE | NULL |
i_amount | IN | NUMBER(14,2) | |
i_li_vol | IN | NUMBER(12,2) | NULL |
i_govern_agr_id | IN | NUMBER(10) | NULL |
i_govern_agr_type_code | IN | VARCHAR2(2) | NULL |
i_dest_org_id | IN | NUMBER(4) | NULL |
i_li_rem | IN | VARCHAR2(72) | NULL |
i_gl_sub_code | IN | VARCAHR2(6) | NULL |
i_ba_id | IN | NUMBER(6) | NULL |
i_invc_num | IN | VARCHAR2(20) | NULL |
i_invc_date | IN | DATE | NULL |
i_due_date | IN | DATE | NULL |
i_alternate_address_id | IN | NUMBER(6) | NULL |
i_separate_chq_flag | IN | VARCHAR2(1) | NULL |
i_invc_rem | IN | VARCHAR2(120) | NULL |
i_purchase_order_num | IN | VARCHAR2(20) | NULL |
i_bank_acct_id | IN | NUMBER(4) | NULL |
i_cash_txn_ba_id | IN | NUMBER(10) | NULL |
i_chq_no | IN | VARCHAR2(10) | NULL |
i_chq_date | IN | DATE | NULL |
i_chq_amt | IN | NUMBER(14,2) | NULL |
i_medium_code | IN | VARCHAR2(1) | NULL |
i_bank_deposit_item_cash_tx_id | IN | NUMBER(10) | NULL |
o_li_count | OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external _transactions_api.CREATE_VOUCHER()
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_org_id | IN | NUMBER(4) | |
i_acct_per_date | IN | DATE | |
i_src_code | IN | VARCHAR2(6) | |
i_voucher_type_code | IN | VARCHAR2(6) | |
i_voucher_num | IN | NUMBER(5) | |
i_curr_code | IN | VARCHAR2(6) | |
i_voucher_rem | IN | VARCHAR2(40) | NULL |
i_external_reference | IN | VARCHAR2(20) | NULL |
i_user | IN | VARCHAR2(32) | |
i_validate_voucher_flag | IN | VARCHAR2(1) | |
o_report_id | OUT | NUMBER | |
o_return_status *procedure only | OUT | VARCHAR2 | Possible values:
|
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external_transactions_api.DELETE_TEMP_DATA()
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
o_report_id | OUT | NUMBER | |
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
external _transactions_api.VALIDATE_VOUCHER()
- Function returns VARCHAR2 (‘TRUE’/’FALSE’) to indicate a successful/unsuccessful action.
- Default values on parameters indicate that the parameter is optional and the default value will be inserted into the corresponding column.
- Available as both a function and a procedure.
Parameter | In/Out | Type | Default Value |
|---|---|---|---|
i_report_id | IN | NUMBER | |
i_user | IN | VARCHAR2(32) | |
o_report_id *function only | OUT | NUMBER | |
o_return_code | OUT | NUMBER | Possible values:
|
o_return_message | OUT | VARCHAR2 | Insert ‘Successful’ or ‘Failure’ or error message |
Error Messages for EXTERNAL_TRANSACTION_API
Message | Explanation |
|---|---|
New Batch Flag must be Y (Yes) or N (No) | The flag is invalid; it must be set to either Y or N. |
Invalid Interface Transaction Code | An invalid interface transaction code was found. Use either INVOICE or JEI. |
Voucher Number must be supplied | A value must be supplied for the voucher number. |
Voucher Type Code must be supplied | A value must be supplied for the voucher type code. |
Organization ID must be supplied | A value must be supplied for the organization ID. |
Accounting Period Date must be supplied | A value must be supplied for the accounting period date. |
Currency Code must be supplied | A value must be supplied for the currency code. |
Source Code must be supplied | A value must be supplied for the source code. |
Major Account must be supplied | A value must be supplied for the major account. |
Minor Account must be supplied | A value must be supplied for the minor account. |
Reporting Currency Amount must be supplied | A value must be supplied for the reporting currency amount. |
Invoice Date must be supplied | If invoice number is supplied, then a value must be supplied for the invoice date. |
Business Associate ID must be supplied | If invoice number is supplied, then a value must be supplied for the business associate ID. |
Invoice Amount must be supplied | If invoice number is supplied, then a value must be supplied for the invoice amount. |
Medium Code must be supplied | If bank account ID is supplied, then a value must be supplied for the medium code. |
Cash Transaction Amount must be supplied | If bank account ID is supplied, then a value must be supplied for the cash transaction amount. |
Cash Transaction Date must be supplied | If bank account ID is supplied, then a value must be supplied for the cash transaction date. |
User ID must be supplied | A value must be supplied for the user ID. |
Accounting Period Date has invalid date format | The accounting period date must be a valid date format of 'YYYYMMDD'. |
Activity Period Date has invalid date format | If activity period date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Invoice Date has invalid date format | If invoice date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Due Date has invalid date format | If due date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Hold Date has invalid date format | If hold date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Cash Transaction Date has invalid date format | If cash transaction date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Cheque Cleared Date has invalid date format | If cheque cleared date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Business Associate Issued Date has invalid date format | If business associate issued date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Billed Date has invalid date format | If billed date is supplied, it must be a valid date format of 'YYYYMMDD'. |
Error getting Bank Deposit Item Cash Transaction ID | The line item bank deposit number must be valid and should match the number of a valid locked bank deposit. In addition the business associate id, cheque number, cheque issued date and reporting currency amount should match the business associate id, cheque number, cheque issued date and amount on an unused bank deposit item that belongs to the same bank deposit number. |
Special Considerations
1. There are six overloaded versions of insert_temp_interface_data() routine. Learn their differences described in this wiki in order to use them properly with named notation, or, it might be easier to use positional notation when calling overloaded routines.
2. When creating an entry, make sure the curr_code for the voucher is consistent with the currency on all accounts contained within the voucher and the dollar value on the line items is in the currency of the voucher.
3. In insert_temp_interface_data, the input parameter i_new_batch_flag is provided to indicate the beginning of a new voucher. This parameter should be set to Y when the voucher number changes or at the beginning of a set of logical voucher records. When this parameter is set to Y, a new report_id will be assigned by the API and returned in io_report_id. This will allow multiple vouchers to be inserted into temp_interface_data with different report ids. Therefore, if there are problems with data in one voucher, the rest of the vouchers will process as they all have different report ids. If the new_batch_flag = N, the io_report_id must contain the report_id passed back from the previous call to insert_temp_interface_data.
4. If you populate org_rep_curr_amt and org_rep_curr_trans_rate you must populate it on all lines.
5. The Voucher Number specified does not matter if the chosen Voucher Type Code is autogenerated.
6. In insert_temp_interface_data and put_line_item, the input parameter i_bank_deposit_item_cash_tx_id is provided to enable users to match a bank deposit to an existing cash transaction and ultimately update the voucher_id on BANK_DEPOSIT_ITEMS with the voucher_id of the existing cash transaction voucher.
7. In insert_temp_interface_data and create_voucher, the input parameter i_external_reference is provided to enable cross-reference of vouchers to external systems. The external_reference will be stored on the INTERFACE_CONTROL_VOUCHERS table.
8. In create_voucher, if the input parameter i_validate_voucher is set to Y, function validate_voucher will be called to submit the Batch Load and Batch Edit processes if creation of the voucher was successful.
Related content
Copyright© 2024 IFS AB. Copying prohibited. All rights reserved.