Skip Headers

Oracle Receivables Reference Guide
Release 12.1
Part Number E13512-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Invoice Creation API User Notes

Overview

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:

Modular Approach

To modularize the Invoice Creation API, the basic structure of the API is divided into four parts:

  1. Get all the default values from profiles and AR_SYSTEM_PARAMETERS table.

  2. Populate four global temporary tables for Header, Lines, Distributions and Sales Credits from PL/SQL tables and Default values (if user has not entered).

  3. Validate all the parameters entered by the user.

  4. 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.

Debug Messages

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:

  1. FND: Debug Log Enabled(AFLOG_ENABLED) to 'Y'.

  2. 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.

API Usage

To create an invoice, you can call the following PL/SQL APIs:

See: AR_INVOICE_API_PUB.

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.

API Parameters

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.

P_BATCH_SOURCE_REC Parameter

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.

P_TRX_CONTINGENCIES_TBL Parameter

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.

P_TRX_HEADER_TBL Parameter

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:
  • The Bill To site use

  • The Ship To Site Use

  • The Primary Salesrep's territory depending on the value of the DEFAULT_TERRITORY system option

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:
  1. Primary receipt method of parent primary bill to site

  2. Primary receipt method of the parent customer

  3. Primary receipt method of the bill to site

  4. Primary receipt method of the bill-to customer

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.
  1. Primary bank account assigned to the primary site.

  2. Primary bank assigned to parent customer.

  3. Primary bank assigned to bill to site use.

  4. Primary bank assigned to bill to customer.

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.

P_TRX_LINES_TBL Parameter

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.

P_TRX_DIST_TBL Parameter

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.

P_TRX_SALESCREDITS_TBL Parameter

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.

Example for Creating Multiple Invoices in a Batch

Objective:

To create an Invoice using a call to ar_invoice_api_pub.Create_invoive and passing a minimum number of Input parameters.

  1. 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);
    
  2. BEGIN

    1. Set applications context if not already set.

        fnd_global.apps_initialize(1318, 50559, 222,0);
      
    2. 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; 
      
    3. Populate batch source information.

        l_batch_source_rec.batch_source_id := 1188; 
      
    4. 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';
      
    5. 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';
      
    6. 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; 
      
    7. 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
      
    8. 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;
      
    9. 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;
      /
      
    10. 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;
      

Example for Creating a Single Invoice

Objective:

To create an Invoice using a call to ar_invoice_api_pub.Create_single_invoive and passing a minimum number of Input parameters.

  1. 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;
    
  2. BEGIN

    1. Set applications context if not already set.

        fnd_global.apps_initialize(1318, 50559, 222,0);
      
    2. 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; 
      
    3. Populate batch source information.

        l_batch_source_rec.batch_source_id := 1188; 
      
    4. 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';
      
    5. 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';
      
    6. 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; 
      
    7. 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
      
    8. 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;
      /
      
    9. 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.