This document outlines the use of Invoice Creation API. This API allows users to create an invoice using simple calls to PL/SQL functions.
The Invoice Creation API is not intended to replace the existing Transaction workbench, AutoInvoice, or the Transaction API program.
You can access this API in two ways:
As standard PL/SQL servers-side routine calls
Through Forms, utilizing the capability of Forms6 to have a procedure as its underlying base table
To modularize the Invoice Creation API, the basic structure of the API is divided into four parts:
Get all the default values from profiles and AR_SYSTEM_PARAMETERS table.
Populate four global temporary tables for Header, Lines, Distributions and Sales Credits from PL/SQL tables and Default values (if user has not entered).
Validate all the parameters entered by the user.
Call the entity handlers to perform the relevant task (such as Create).
This results in easy to understand and easy to maintain code. Any new functionality can be added by a simple code plug-in at each of the four parts.
The Invoice Creation API uses the Oracle Applications Logging Framework to log all debug messages in a central repository. Please query using module name, ar.plsql.InvoiceAPI.
The debugging can be enabled by the setting the following profile options:
FND: Debug Log Enabled(AFLOG_ENABLED) to 'Y'.
FND: Debug Log Level (AFLOG_LEVEL) to 'Statement'.
Once the above parameters are set, the message will be logged in the FND repository. The API to log accepts log level, module name, and the actual text.
An example is given below:
FND_LOG.STRING(P_LOG_LEVEL,P_MODULE_NAME, P_MESSAGE);
All Invoice Creation API debug messages use a module name of 'ar.plsql.InvoiceAPI'.
See: Oracle Applications Supportability Guide.
To create an invoice, you can call the following PL/SQL APIs:
AR_INVOICE_API_PUB.CREATE_INVOICE: Creates multiple invoices in a batch.
AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE: Create a single invoice and return customer_trx_id.
See: AR_INVOICE_API_PUB.
The API contains 2 public procedures to create either a single invoice, or multiple invoices in batch mode. The input parameters are the same for both procedures and are explained in the following section.
Use the CREATE_INVOICE procedure to create multiple invoices in a batch. The procedure returns a global record type structure which contains the batch_id to retrieve the necessary data from the transaction tables. The structure is defined in the package specification of ar_invoice_api_pub. Please refer to Example for Creating Multiple Invoices in a Batch for usage.
TYPE api_outputs_type IS RECORD ( batch_id NUMBER DEFAULT NULL );
Use the CREATE_SINGLE_INVOICE procedure to create a single invoice. The procedure returns customer_trx_id as an out parameter, but the procedure does not create a batch for the single invoice. Please refer to Example for Creating a Single Invoice for usage.
The API accepts the following parameters:
p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_commit IN VARCHAR2 := FND_API.G_FALSE, p_batch_source_rec IN batch_source_rec_type, p_trx_header_tbl IN trx_header_tbl_type, p_trx_lines_tbl IN trx_line_tbl_type, p_trx_dist_tbl IN trx_dist_tbl_type, p_trx_salescredits_tbl IN trx_salescredits_tbl_type, p_trx_contingencies_tbl IN trx_contingencies_tbl_type x_customer_trx_id OUT NOCOPY NUMBER, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2,
The following table shows the list of standard API parameters.
Parameter | Type | Data Type | Required | Default Value | Description |
---|---|---|---|---|---|
p_api_version | IN | NUMBER | Yes | 1.0 | Compare version numbers of incoming calls to its current versions |
p_init_msg_list | IN | VARCHAR2 | FND_API.G_FALSE | Allow API callers to request that API does initialize the message list on their behalf. | |
p_commit | IN | VARCHAR2 | FND_API.G_FALSE | Used by API callers to ask the API to commit on their behalf. | |
x_customer_trx_id | OUT | NUMBER | Returns customer_trx_id in case it is called for creating a single invoice. This parameter works only with CREATE_SINGLE_INVOICE procedure. | ||
x_return_status | OUT | VARCHAR2 | Represent the API status. | ||
x_msg_count | OUT | NUMBER | Number of messages in the PI message list (not used by this API). | ||
x_message_data | OUT | VARCHAR2 | Message in case API encounters any unexpected error. |
The P_BATCH_SOURCE_REC parameter is of PL/SQL record type, and has the following attributes, as described in this table:
Attribute Name | Data Type | Required | Default Value | Description |
---|---|---|---|---|
batch_source_id | NUMBER | Null | If batch_source_id is null then value will be derived from AR_RA_BATCH_SOURCE profile option. In case the value is passed then it will be validated against ra_batch_sources. Only ‘Manual' batch sources are allowed. | |
default_date | DATE | Null | If the value is null then Sysdate will be taken. |
The P_TRX_CONTINGENCIES_TBL parameter is of PL/SQL table type TRX_CONTINGENCIES_REC_TYPE.
TRX_CONTINGENCIES_REC_TYPE has the following attributes, as described in this table:
Attribute Name | Data Type | Required | Default Value | Description |
---|---|---|---|---|
trx_contingency_id | NUMBER | Unique identifier for each contingency on a line. | ||
trx_line_id | NUMBER | Identifies rows in the user defined line table. | ||
contingency_code | VARCHAR2(30) | Obsolete. | ||
contingency_id | NUMBER | Identifies the actual revenue contingency being requested. Valid values are maintained in the AR_DEFERRAL_REASONS table. | ||
expiration_event_date | DATE | NULL | Indicates the expiration of the contingency removal event. Normally defaulted by the API, the expiration_event_date is set as one of the following based on settings in AR_DEFERRAL_REASONS: transaction date, fulfillment date, ship confirm date, or proof of delivery date. However, you can override the default date. | |
expiration_days | NUMBER | NULL | The offset days that are added to the expiration_event_date to calculate the actual expiration_date. | |
expiration_date | DATE | NULL | The date on which a contingency is scheduled to expire (unless another event causes it to expire earlier). The actual expiration_date is calculated by adding the expiration_days to the expiration_event_date. | |
attribute_category | VARCHAR2(30) | NULL | Descriptive flexfield structure definition column. | |
attribute1 - 15 | VARCHAR2 (150) | Null | Descriptive flexfield segment. | |
completed_flag | VARCHAR2(1) | 'N' | Determines if the contingency is active or expired at the time of creation. If set to 'Y', then the contingency does not bar or withhold revenue from being recognized. | |
completed_by | NUMBER | NULL | User who completed the contingency. |
The P_TRX_HEADER_TBL parameter is of PL/SQL table type TRX_HEADER_REC_TYPE.
TRX_HEADER_REC_TYPE has the following attributes, as described in this table:
Attribute Name | Data Type | Required | Default Value | Description |
---|---|---|---|---|
trx_header_id | NUMBER | Yes | Identifier for the Invoice header record. This must be unique for each record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. | |
trx_number | VARCHAR2(30) | Null | This is the transaction number for the invoice. This field should not be populated if the batch source has Copy Document Sequence Number to Transaction Number checked or if Automatic Transaction Numbering is enabled. | |
trx_date | DATE | Null | Invoice Date. If no value is passed then p_batch_source_rec.default_date is used. If that too is not passed then sysdate is used. | |
gl_date | DATE | Null | General ledger Date. If no date is passed then p_batch_source_rec.default_date is used. If that too is not passed then sysdate is used. | |
trx_currency | VARCHAR2(30) | Null | Transaction Currency. If not populated then ar_system_parameters is used to retrieve it. The currency if populated must be active as of the transaction date. | |
cust_trx_type_id | NUMBER | Null | Transaction Type Identifier. This can have any one of the following three values: 'INV', 'DM', or 'CM'. Validated against ra_cust_trx_types. If not populated, then it is retrieved from the batch source. | |
bill_to_customer_id | NUMBER | Yes | Bill To Customer ID. This must exist in hz_cust_accounts table. The customer must be an active (‘A') customer. Validated against hz_cust_accounts.cust_account_id. | |
bill_to_account_number | VARCHAR2(30) | Null | Bill To Customer Number. If both Bill To Customer ID and Bill To Customer Number are passed, then the former will take precedence.Validated against hz_cust_accounts.account_number. | |
bill_to_customer_name | VARCHAR2 (260) | Null | Bill To Customer Name. If all three are passed, the precedence is as follows: Customer ID, Customer Number, then Customer Name. | |
bill_to_contact_id | NUMBER | Null | Bill To Customer Contact ID. This must exist for the Bill To Customer and Bill To Address combination. | |
bill_to_address_id | NUMBER | Null | Bill To Address ID. This must exist in hz_cust_acct_sites for the populated Bill To Customer ID | |
bill_to_site_use_id | NUMBER | Null | Bill To Site use ID. The site use ID must exist in combination with Ship To Customer ID, Ship To Address ID. | |
ship_to_customer_id | NUMBER | Ship To Customer ID. This must exist in hz_cust_accounts table. | ||
ship_to_account_number | VARCHAR2(30) | Null | Ship To Customer Number. If both Bill To Customer ID and Ship To Customer Number are passed, then the former will take precedence. | |
ship_to_customer_name | VARCHAR2 (260) | Null | Ship To Customer Name. If all three are passed, the precedence is as follows: Customer ID, Customer Number, then Customer Name. | |
ship_to_contact_id | NUMBER | Null | Ship To Customer Contact ID. This must exist for the Ship To Customer and Ship To Address combination. | |
ship_to_address_id | NUMBER | Null | Ship To Address ID. This must exist in hz_cust_acct_sites for the populated Ship To Customer ID. | |
ship_to_site_use_id | NUMBER | Null | Ship To Site use ID. The site use ID must exist in combination with Ship To Customer ID, Ship To Address ID. | |
sold_to_customer_id | NUMBER | Null | Ship To Customer ID. This must exist in hz_cust_accounts table. | |
term_id | NUMBER | Null | Payment Terms Identifier. The Term ID must be valid for the transaction date. If not populated, then it is retrieved from ra_terms based on bill_to_customer_id and bill_to_site_use_id. | |
primary_salesrep_id | NUMBER | Null | Primary Salesrep ID. This is required if Salesperson check box is checked in the System Options form. If not populated, then it is derived based on bill-to_customer_id and bill_to_site_use_id. | |
primary_salesrep_name | VARCHAR2 (240) | Null | Primary Salesrep name. If both salesrep ID and name are passed, then Salesrep ID will take precedence. | |
exchange_rate_type | VARCHAR2(60) | Null | Exchange Rate Type. This must exist in gl_daily_conversion_types. Required if trx_currency is different from functional currency. If not populated, then it will derive from gl. | |
exchange_date | DATE | Null | Exchange Date. Required if trx_currency is different from functional currency. If not populated, then it will derive from gl. | |
exchange_rate | NUMBER | Null | Exchange Rate. This should be entered only if transaction currency is different from the functional currency and exchange rate type is ‘User'. | |
territory_id | NUMBER | Null | Territory ID. If not populated, then it is defaulted based on the following hierarchy:
|
|
remit_to_address_id | NUMBER | Null | Remit To Address ID. If not populated, then it is defaulted based on country, state, and postal code of bill_to_site_use_id. If populated, then validated against ar_active_remit_to_addresses_v. | |
invoicing_rule_id | NUMBER | Null | Invoicing Rule ID. Valid values are -2 and -3. If you enter a value here, then you must populate accounting rule for line type = ‘LINE'. | |
printing_option | VARCHAR2(20) | Null | Revenue Accounting lookup code for INVOICE_PRINT_OPTIONS. Valid codes are PRI - Print and NOT - Do not Print. | |
purchase_order | VARCHAR2(50) | Null | Purchase Order Number for this transaction. | |
purchase_order_revision | VARCHAR2(50) | Null | Purchase Order Revision. This must not be entered if purchase order is not populated. | |
purchase_order_date | DATE | Null | Purchase Order date. This must not be entered if purchase order is not populated. | |
comments | VARCHAR2 (240) | Null | Comments. Value can be printed on an invoice using the Print Invoice view. | |
internal_notes | VARCHAR2 (240) | Null | Stores the special instruction. Value can be printed on an invoice using the Print Invoice view. | |
finance_charges | VARCHAR2(1) | Null | Indicates if finance charges are included. Y for yes, N otherwise. | |
receipt_method_id | NUMBER | Null | This is the payment identifier for this transaction. If not populated, then it is defaulted based on the following hierarchy:
|
|
related_customer_trx_id | NUMBER | Null | Customer transaction ID of the document to which this transaction is related. Validated against ra_customer_trx_all.customer_trx_id. Not required for on-account credit memos. | |
agreement_id | NUMBER | Null | Customer Agreement identifier for this transaction. If not populated, then it will be defaulted from the commitment. Must exist in SO_AGREEMENTS. (For future use.) | |
ship_via | VARCHAR2(30) | Null | Ship Via Code. If populated, then validated against org_freight. | |
ship_date_actual | DATE | Null | Ship Date | |
waybill_number | VARCHAR2(50) | Null | Waybill Number | |
fob_point | VARCHAR2(30) | Null | Free on Board Point. Validated against AR_LOOKUPS.LOOKUP_TYPE='FOB'. | |
customer_bank_account_id | NUMBER | Null | Customer bank account ID. If the receipt method is Automatic, then it is required. If not populated, then it will be default using the following hierarchy.
|
|
default_ussgl_transaction_code | VARCHAR2(30) | Null | Default value for the USSGL Transaction Code Flexfield (for future use) | |
status_trx | VARCHAR2(30) | Null | The status of the transaction. If not populated, then defaulted from Transaction Type. Valid values are 'OP', 'CL', 'PEN', 'VD. | |
paying_customer_id | NUMBER | Null | This column is required when the RECEIPT_METHOD_ID column is an automatic receipt method. | |
paying_site_use_id | NUMBER | Null | This column is required when the RECEIPT_METHOD_ID column is an automatic receipt method. | |
doc_sequence_value | NUMBER(15) | Null | Document Number. Must not exist in Oracle Receivables. | |
attribute_category | VARCHAR2(30) | Null | Descriptive flexfield structure definition column. | |
attribute1 - 10 | VARCHAR2 (150) | Null | Descriptive flexfield segment. | |
global_attribute_category | VARCHAR2(30) | Null | Reserved for country-specific functionality. (For future use.) | |
global_attribute1-30 | VARCHAR2 (150) | Null | Reserved for country-specific functionality. (For future use.) | |
interface_header_context | VARCHAR2(30) | Null | Interface header context. | |
interface_header_attribute1 - 15 | VARCHAR2(30) | Null | Interface header attribute value. |
The P_TRX_LINES_TBL parameter is of PL/SQL table type TRX_LINE_REC_TYPE.
TRX_LINE_REC_TYPE has the following attributes, as described in this table:
Attribute Name | Data Type | Required | Default Value | Description |
---|---|---|---|---|
trx_header_id | NUMBER | Yes | Identifier for the Invoice header record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. This column ties back with P_TRX_HEADER_TBL. | |
trx_line_id | NUMBER | Yes | Identifier for the Invoice lines record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. | |
link_to_trx_line_id | NUMBER | This column is required only if line type is ‘TAX' and ‘FREIGHT' (if it is associated with any line). | ||
line_number | NUMBER | Yes | Line number of the invoice | |
reason_code | VARCHAR2(30) | Reason code. Validated against AR_LOOKUPS.LOOKUP_TYPE = ‘INVOICING_REASON'. | ||
inventory_item_id | NUMBER | Inventory item identifier. Mutually exclusive with the column MEMO_LINE_ID. Validated against mtl_system_items.inventory_item_id and invoice_enabled_flag = ‘Y'. | ||
description | VARCHAR2 (240) | Line description. Required if inventory_item_id or memo_line_id is not provided. | ||
quantity_ordered | NUMBER | Quantity of an order | ||
quantity_invoiced | NUMBER | Quantity of invoice line. Required for Invoices. | ||
unit_standard_price | NUMBER | List price per unit. | ||
unit_selling_price | NUMBER | Selling price per unit for a transaction line. Required for Invoices. | ||
sales_order | VARCHAR2(50) | Sales order number for this transaction. | ||
sales_order_line | VARCHAR2(30) | Sales order line number for this transaction. | ||
sales_order_date | DATE | Sales order date for this transaction. | ||
accounting_rule_id | NUMBER | Accounting rule identifier. Must provide a value for invoice with Rule ID. Validated against RA_RULES. | ||
line_type | VARCHAR2(20) | Yes | Receivables lookup code for STD_LINE_TYPE. | |
attribute_category | VARCHAR2(30) | Descriptive flexfield structure definition column. | ||
attribute1-15 | VARCHAR2 (150) | Descriptive flexfield segment. | ||
rule_start_date | DATE | First GL date of the invoice. Only used for invoice with rules. | ||
interface_line_context | VARCHAR2(30) | Interface line context. | ||
interface_line_attribute1-15 | VARCHAR2(30) | Interface line attribute value. | ||
sales_order_source | VARCHAR2(50) | The source of the sales order. | ||
amount | NUMBER | Transaction line revenue amount. If line type = ‘FREIGHT' or ‘TAX', then amount must be populated. | ||
tax_precedence | NUMBER | This column is obsolete and should not be populated. | ||
tax_rate | NUMBER | Tax rate for a line. Required for TAX line in case amount is not populated. | ||
memo_line_id | NUMBER | Memo line description identifier. Mutually exclusive with the column INVENTORY_ITEM_ID. Not required for ‘TAX' and ‘FREIGHT' lines. | ||
uom_code | VARCHAR2(3) | Unit of measure code. Required for line type of ‘LINE' and has a item on the line. Not required for ‘TAX' and ‘FREIGHT' lines. | ||
default_ussgl_transaction_code | VARCHAR2(30) | Default value for the USSGL Transaction Code Flexfield. (For future use.) | ||
default_ussgl_trx_code_context | VARCHAR2(30) | Default context value for the USSGL Transaction Code Flexfield. (For future use.) | ||
vat_tax_id | NUMBER | Unique identifier for AR_VAT_TAX. Required for ‘TAX' Lines. | ||
tax_exempt_flag | VARCHAR2(1) | Tax Lines are controlled by the lookup (TAX_CONTROL_FLAG), which allows for standard tax, exempt tax, and required tax. | ||
tax_exempt_number | VARCHAR2(80) | Exemption certificate number for item lines that have TAX_EXEMPT_FLAG set to E for exempt. | ||
tax_exempt_reason_code | VARCHAR2(30) | Tax Exempt Reason, for item lines that have tax_exempt_flag set to "E" (exempt). | ||
movement_id | NUMBER | Intrastate movement ID number that is tied to the shipment information. | ||
global_attribute1-20 -20 | VARCHAR2 (150) | Reserved for country-specific functionality. (For future use.) | ||
global_attribute_category | VARCHAR2(30) | Reserved for country-specific functionality. (For future use.) | ||
amount_includes_tax_flag | VARCHAR2(1) | Y indicates tax is inclusive. N indicates tax is exclusive. NULL for lines indicates tax cannot be overridden or tax is a tax group. Cannot be NULL for tax types. Must be NULL for other types. | ||
warehouse_id | NUMBER | Foreign key to the HR_ORGANIZATIONS table. The warehouse identifies the ship-from location and can be used to control taxation. Within the US, the Warehouse ID is important when calculating tax on the origin/modified origin state sales tax. Outside the US you can use tax groups and conditions to build a schedule of multiple conditional taxes based on both the ship-from and ship-to county/county/state or provinces. | ||
contract_line_id | NUMBER | Identifies the contract line from Oracle Contracts Core that is associated with this line. | ||
source_data_key1-5 | VARCHAR2 (150) | Identifies source data from original system. | ||
invoiced_line_acctg_level | VARCHAR2(15) | Identifies accounting level for invoiceable lines in original system. |
The P_TRX_DIST_TBL parameter is of PL/SQL table type TRX_DIST_REC_TYPE.
TRX_DIST_REC_TYPE has the following attributes, as described in this table:
Attribute Name | Data Type | Required | Default Value | Description |
---|---|---|---|---|
trx_line_id | NUMBER | Yes | Identifier for the Invoice lines record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. | |
trx_header_id | NUMBER | Identifier for the Invoice header record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. This column ties back with P_TRX_HEADER_TBL. Required in case of ‘REC' distribution type. | ||
trx_dist_id | NUMBER | Yes | Identifier for the Distribution record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. | |
account_class | VARCHAR2(20) | Yes | Account Class for this distribution. Freight, Receivable, Revenue, AutoInvoice Clearing, Tax, Unbilled Receivable, Unearned Revenue, or Charges account type. | |
amount | NUMBER | Amount of this record in the foreign currency. Required if percentage is not passed. | ||
acctd_amount | NUMBER | Amount of this record in the functional currency. If not populated, then it will be populated based on amount passed. | ||
percent | NUMBER | Percent of the line amount represented by this record. Required if amount is not passed. | ||
code_combination_id | NUMBER | Yes | Code combination ID for Accounting Flexfield. Validated against gl_code_combinations.code_combination_id. | |
attribute_category | VARCHAR2(30) | Descriptive flexfield structure definition column. | ||
attribute1-15 | VARCHAR2(150) | Descriptive flexfield segment. | ||
comments | VARCHAR2(240) | Comment about the revenue distribution. |
The P_TRX_SALESCREDITS_TBL parameter is of PL/SQL table type TRX_SALESCREDITS_REC_TYPE.
TRX_ SALESCREDITS _REC_TYPE has the following attributes, as described in this table:
Attribute Name | Data Type | Required | Default Value | Description |
---|---|---|---|---|
trx_salescredit_id | NUMBER | Yes | Identifier for the Salesperson on the lines record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. | |
trx_line_id | NUMBER | Yes | Identifier for the Invoice lines record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. | |
salesrep_id | NUMBER | Yes | Identifies the salesperson for this sales credit assignment. Validated against ra_salesreps.salesrep_id. | |
salesrep_number | VARCHAR2(30) | Salesrep Number assignment. Validated against ra_salesreps.salesrep_number. If both number and ID is passed, then ID will take precedence. | ||
sales_credit_type_name | VARCHAR2(30) | Sales Credit Type Name. Validated against so_sales_credit_types.name. | ||
sales_credit_type_id | NUMBER | Yes | Sales Credit Type Identifier. Validated against so_sales_credit_types.sales_credit_type_id. If both ID and name are passed, then ID will take precedence. | |
salescredit_amount_split | NUMBER | The amount of revenue/non-revenue credit for this salesperson/customer. Required if salescredit_percent_split is not passed. | ||
salescredit_percent_split | NUMBER | The percent of revenue/non-revenue credit for this salesperson/customer. Required if salescredit_amount_split is not passed. | ||
attribute_category | VARCHAR2(30) | Descriptive flexfield structure definition column. | ||
attribute1-15 | VARCHAR2(150) | Descriptive flexfield segment. |
Objective:
To create an Invoice using a call to ar_invoice_api_pub.Create_invoive and passing a minimum number of Input parameters.
DECLARE
l_return_status varchar2(1); l_msg_count number; l_msg_data varchar2(2000); l_batch_id number; l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type; l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type; l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type; l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type; l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type; CURSOR cBatch IS select customer_trx_id from ra_customer_trx_all where batch_id = l_batch_id; CURSOR cValidTxn IS SELECT trx_header_id From ar_trx_header_gt WHERE trx_header_id not in ( SELECT trx_header_id FROM ar_trx_errors_gt);
BEGIN
Set applications context if not already set.
fnd_global.apps_initialize(1318, 50559, 222,0);
Populate header information.
l_trx_header_tbl(1).trx_header_id := 101; l_trx_header_tbl(1).trx_number := 'Test Invoice API'; l_trx_header_tbl(1).bill_to_customer_id := 1006; l_trx_header_tbl(1).cust_trx_type_id := 2376;
Populate batch source information.
l_batch_source_rec.batch_source_id := 1188;
Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 101; l_trx_lines_tbl(1).trx_line_id := 101; l_trx_lines_tbl(1).line_number := 1; l_trx_lines_tbl(1).memo_line_id := 8; l_trx_lines_tbl(1).quantity_invoiced := 10; l_trx_lines_tbl(1).unit_selling_price := 12; l_trx_lines_tbl(1).line_type := 'LINE';
Populate line 2 information.
l_trx_lines_tbl(2).trx_header_id := 101; l_trx_lines_tbl(2).trx_line_id := 102; l_trx_lines_tbl(2).line_number := 2; l_trx_lines_tbl(2).description := 'Test'; l_trx_lines_tbl(2).quantity_invoiced := 12; l_trx_lines_tbl(2).unit_selling_price := 12; l_trx_lines_tbl(2).line_type := 'LINE';
Populate freight information and link it to line 1.
l_trx_lines_tbl(3).trx_header_id := 101; l_trx_lines_tbl(3).trx_line_id := 103; l_trx_lines_tbl(3).link_to_trx_line_id := 101; l_trx_lines_tbl(3).line_number := 1; l_trx_lines_tbl(3).line_type := 'FREIGHT'; l_trx_lines_tbl(3). amount := 25;
Call the invoice api to create multiple invoices in a batch.
AR_INVOICE_API_PUB.create_invoice( p_api_version => 1.0, p_batch_source_rec => l_batch_source_rec, p_trx_header_tbl => l_trx_header_tbl, p_trx_lines_tbl => l_trx_lines_tbl, p_trx_dist_tbl => l_trx_dist_tbl, p_trx_salescredits_tbl => l_trx_salescredits_tbl, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); IF l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN dbms_output.put_line('unexpected errors found!'); ELSE
Check if there are record exist in error table. If no records exist for a trx_header_id, then only Invoice will create in the system; otherwise not.
For cValidTxnRec IN cvalidTxn loop IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL) THEN dbms_output.put_line('Invoice(s) suceessfully created!') ; dbms_output.put_line('Batch ID: ' || ar_invoice_api_pub.g_api_outputs.batch_id); l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
To see all customer_trx_id for this batch:
for cBatchRec in cBatch loop dbms_output.put_line ( 'Cust Trx Id '|| cBatchRec.customer_trx_id ); end loop; ELSE dbms_output.put_line('Errors found!'); END IF; End loop; END IF; END; /
See all the validation errors.
SET LINESIZE 200 COLUMN trx_header_id HEADING 'Header ID' COLUMN trx_line_id HEADING 'Line ID' COLUMN error_message HEADING 'Message' COLUMN invalid_value HEADING 'Invalid Value' COLUMN trx_header_id FORMAT 9999999 COLUMN trx_line_id FORMAT 9999999 COLUMN error_message FORMAT a30 COLUMN invalid_value FORMAT a20 SELECT trx_header_id, trx_line_id, error_message, invalid_value FROM ar_trx_errors_gt;
Objective:
To create an Invoice using a call to ar_invoice_api_pub.Create_single_invoive and passing a minimum number of Input parameters.
DECLARE
l_return_status varchar2(1); l_msg_count number; l_msg_data varchar2(2000); l_batch_id number; l_cnt number := 0; l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type; l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type; l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type; l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type; l_trx_salescredits_tbl ar_invoice_api_pub. l_customer_trx_id number;
BEGIN
Set applications context if not already set.
fnd_global.apps_initialize(1318, 50559, 222,0);
Populate header information.
l_trx_header_tbl(1).trx_header_id := 101; l_trx_header_tbl(1).trx_number := 'Test Invoice API'; l_trx_header_tbl(1).bill_to_customer_id := 1006; l_trx_header_tbl(1).cust_trx_type_id := 2376;
Populate batch source information.
l_batch_source_rec.batch_source_id := 1188;
Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 101; l_trx_lines_tbl(1).trx_line_id := 101; l_trx_lines_tbl(1).line_number := 1; l_trx_lines_tbl(1).memo_line_id := 8; l_trx_lines_tbl(1).quantity_invoiced := 10; l_trx_lines_tbl(1).unit_selling_price := 12; l_trx_lines_tbl(1).line_type := 'LINE';
Populate line 2 information.
l_trx_lines_tbl(2).trx_header_id := 101; l_trx_lines_tbl(2).trx_line_id := 102; l_trx_lines_tbl(2).line_number := 2; l_trx_lines_tbl(2).description := 'Test'; l_trx_lines_tbl(2).quantity_invoiced := 12; l_trx_lines_tbl(2).unit_selling_price := 12; l_trx_lines_tbl(2).line_type := 'LINE';
Populate freight information and link it to line 1.
l_trx_lines_tbl(3).trx_header_id := 101; l_trx_lines_tbl(3).trx_line_id := 103; l_trx_lines_tbl(3).link_to_trx_line_id := 101; l_trx_lines_tbl(3).line_number := 1; l_trx_lines_tbl(3).line_type := 'FREIGHT'; l_trx_lines_tbl(3). amount := 25;
Call the invoice api to create multiple invoices in a batch.
AR_INVOICE_API_PUB.create_single_invoice( p_api_version => 1.0, p_batch_source_rec => l_batch_source_rec, p_trx_header_tbl => l_trx_header_tbl, p_trx_lines_tbl => l_trx_lines_tbl, p_trx_dist_tbl => l_trx_dist_tbl, p_trx_salescredits_tbl => l_trx_salescredits_tbl, x_customer_trx_id => l_customer_trx_id, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); IF l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN dbms_output.put_line('unexpected errors found!'); ELSE
Check whether any record exist in error table
SELECT count(*) Into cnt From ar_trx_errors_gt; IF cnt = 0 THEN dbms_output.put_line ( 'Customer Trx id '|| l_customer_trx_id); ELSE dbms_output.put_line ( 'Transaction not Created, Please check ar_trx_errors_gt table'); END IF; END; /
See all the validation errors.
SET LINESIZE 200 COLUMN trx_header_id HEADING 'Header ID' COLUMN trx_line_id HEADING 'Line ID' COLUMN error_message HEADING 'Message' COLUMN invalid_value HEADING 'Invalid Value' COLUMN trx_header_id FORMAT 9999999 COLUMN trx_line_id FORMAT 9999999 COLUMN error_message FORMAT a30 COLUMN invalid_value FORMAT a20 SELECT trx_header_id, trx_line_id, error_message, invalid_value FROM ar_trx_errors_gt;
Note: In the above examples, we did not pass distribution, sales credits, or contingencies . Note, however, that you can create an invoice passing distributions, sales credits, and contingencies.