Importing Transactions Using the Transaction API (Oracle Receivables Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Importing Transactions Using the Transaction API

You can use the Oracle Receivables Transaction Application Programming Interface (API) to import invoices, credit memos, and debit memos into Oracle Receivables from an external system. Previously, Receivables required you to manually enter transactions in the Transactions windows or import them from an external system using the AutoInvoice program. However, AutoInvoice can often be time consuming and requires that you create transactions as part of a batch. The Transaction API lets you use your system to create transactions in Receivables more quickly and with fewer restrictions.

Attention: This feature currently has a BETA status and should be used only with a test or demonstration database. Before using this feature in a production environment, please contact the Receivables Product Director for approval.

The API process is very similar to creating transactions in the Receivables Transactions window. Using the Transaction API you can create a single transaction in your system, save your work, then immediately submit it for creation within Receivables. If all of the information is complete and valid, Receivables immediately creates the transaction; otherwise, you need to modify or provide any missing data, then resubmit the transaction to the API.

Other advantages to using the Transaction API include:

Save Time - The Transaction API allows faster throughput from your system to Oracle Receivables. You can create transactions on demand, rather than importing, validating, fixing errors, then re-importing as AutoInvoice requires.

Increased Flexibility - You can call the Transaction API from your system's transaction entry window or from a program that will transfer multiple transactions from a foreign system into Receivables.

Instant Validation - When called from an external system, the Transaction API instantly validates your information, then either creates the transaction in Oracle Receivables or displays an error message.

Update and Delete Transactions - You can modify a transaction from your system and the Transaction API will automatically update the information within Receivables. You can also delete a transaction in Receivables using this method (if the transaction meets certain requirements).

Import Data from Different Systems - The Transaction API lets you import transaction data from a variety of environments. You can import data from Oracle Order Entry, your existing system, or any third party application in which you store information.

The following diagram shows how transaction information is imported into Oracle Receivables using the Transaction API.

When you submit a transaction, the Transaction API validates the information. If the information meets all of the validation requirements, the API creates the transaction in Oracle Receivables. If the data fails validation, the API displays an error message to help you determine the missing or invalid data. You can then update the information in your system and resubmit the transaction to the API.

Transaction Validation

The Transaction API validates your data for compatibility with Oracle Receivables. It ensures that the columns in the Oracle Receivables Interface tables reference the appropriate values and columns in Receivables. Other validation that the Transaction API performs includes:

Existence: For some columns, the Transaction API ensures that the values are already defined in Receivables. However, the program does not validate against status. For example, if you import a transaction with an inactive payment term, the API will not reject the transaction.

Uniqueness: The Transaction API ensures that the invoice number you supply is unique and that the document number you supply is unique within the associated sequence type.

Precision: The Transaction API ensures that the amount and the accounted amount you supply have the correct precision for a given currency.

Cross Validation: The Transaction API ensures that certain column values agree with each other. These values can be within a single interface table or multiple interface tables.

For more information, see: Error Handling.

Calculating Tax on Transactions

The Transaction API provides the functionality you need to meet your sales tax and other taxing requirements, such as Value Added Tax (VAT). You can either pass tax code lines, tax exempt lines, or have the Transaction API automatically determine your tax rates using the hierarchy shown in the tax calculation flow charts. If the Transaction API determines your tax rates, it will take into account any customer or item tax exemptions or item tax exceptions. See: Overview of Calculating Tax.

The Transaction API lets you pass tax lines as individual transactions or as references to other transactions. If you are passing tax lines, you can only pass tax lines associated with tax codes of type VAT or Sales Tax. Unlike AutoInvoice, the Transaction API lets you pass non-ad hoc tax lines. In this case, the API calculates the rates and amounts for each line based on the tax code that you specify.

If you specify the tax code, rate, and precedence information in your feeder system, the Transaction API imports the data directly into the Receivables tables (i.e. no calculation is necessary). If you do not provide this information, Receivables calculates sales tax using the tax rates associated with your shipping address. Receivables calculates sales tax only for shipping addresses that are within the country defined in the Default Country field of the System Options window.

The Transaction API searches for a tax rate using the hierarchy that you define in the System Options window, stopping when one is found. You can assign a tax code at the following levels in Receivables:

Ship-To/Bill-To address

customer

item/standard memo line

System Options window (if your tax method is VAT)

If you do not want Receivables to calculate tax based on location, you can pass tax codes through lines with the line_type parameter set to 'Tax'. Tax codes can be of type 'VAT' or 'Sales Tax'. For ad hoc tax codes, you must also pass either a tax rate or an amount. If you are passing a non-ad hoc tax code, you cannot specify an extended amount or a tax rate. Any exemptions must be calculated into the rate or amount.

Determining GL Dates

The Transaction API derives the GL date based on the information you provide. The API searches for a GL date using the following hierarchy, stopping when one is found:

Batch GL Date

Transaction Date

Previous Transaction GL Date

Invoicing Rule ID

Attention: The Transaction API rejects transactions that have GL dates in closed periods.

Importing Invoices with Rules

If your accounting method is 'Accrual' you can use the Transaction API to import invoices with accounting and invoicing rules. If your accounting method is 'Cash Basis', the API will reject all invoices with rules. Accounting rules determine the number of periods and percentage of total revenue to record in each accounting period. Invoicing rules determine the accounting period in which the receivable amount is recorded.

Receivables provides two invoicing rules, 'Bill in Advance' and 'Bill in Arrears.' You supply the API with the model account which contains the accounting distributions and the percent allocated to each account. You must run the Revenue Recognition Program before Receivables can create your accounting entries for these invoices.

Besides validating dates, the Transaction API also validates and rejects lines if:

the accounting rule has overlapping periods

all of the accounting periods do not exist for the duration of your accounting rule

Importing Credit Memos

When you import credit memos against transactions, the Transaction API ensures that the Open Receivables flag of the credit memo being imported matches the Open Receivables flag of the transaction it is crediting.

When you import credit memos against invoices with rules, the API uses the accounting rule method you entered in the CREDIT_METHOD_FOR_RULES column to determine how to reverse the accounting entries created for the original invoice. You can either enter 'LIFO', 'PRORATE', or 'UNIT'. If you choose 'LIFO', Receivables reverses the accounting entries beginning with the last period. If you choose 'PRORATE', Receivables prorates the credit amount across all accounting periods. If you choose 'UNIT', Receivables lets you credit specific quantities (starting with the period you specified in the column LAST_PERIOD_TO_CREDIT) and working backwards.

When you import credit memos against invoices without rules, AutoInvoice uses the general ledger date in the interface table as the general ledger date of the credit memo. The credit memo lines must always have the same general ledger date as the credit memo.

The credit memo general ledger date must be equal to or greater than the general ledger date of the invoice you are crediting. Also, the credit memo general ledger date must be in an 'Open' or 'Future' period. However, if the accounting rule is 'In Arrears', the credit memo general ledger date can be in a 'never opened' period.

Credit memos against invoices without rules that are imported using the Transaction API behave the same as those entered manually through the Credit Memos window. For example, you pass the amount you want to credit and Receivables automatically creates all the accounting reversal entries. Receivables also automatically reverses the sales and non-revenue credit assigned to your salespeople.

Defaulting

The Transaction API provides default values for some parameters. For more information, see: Transaction API Matrix.

Deposits and Guarantees

The Transaction API does not import deposits or guarantees (Commitments) into Receivables.

Receivables Tables

If your transaction passes validation, the Transaction API writes the data into the following Receivables tables:

RA_BATCHES

RA_CUSTOMER_TRX

RA_CUSTOMER_TRX_LINES

RA_CUST_TRX_LINE_GL_DIST

RA_CUST_TRX_LINE_SALESREPS

AR_PAYMENT_SCHEDULES

AR_ADJUSTMENTS

AR_RECEIVABLE_APPLICATIONS

For more information, see: Transaction API Matrix.

Note: The AR_PAYMENT_SCHEDULES, AR_ADJUSTMENTS, and AR_RECEIVABLE_APPLICATIONS tables are not included in the Transaction API Matrix. This is because the API automatically writes data into these tables, whereas the other tables require that you pass a value for each column.

Library of Functions

The file $AR_TOP/admin/sql/ARTPTRXS.pls contains specifications for the following main API routines:

get_transaction

create_transaction

update_transaction

delete_transaction

The Transaction API also provides a library of supporting routines that help you prepare data to be imported into Receivables. For example, when creating a transaction, the API requires a transaction's payment term ID, not the payment term name, to populate the Receivables tables. But in some systems, key references are passed using the user values instead of the internal IDs. The Transaction API's library of routines convert user values from a foreign system into the IDs that Receivables requires to successfully create your transactions. See: Preparing Your System.

Refer to $AR_TOP/admin/sql/ARTPTRLS.pls for the following conversion routines:

get_ID

get_address_id

get_site_use_id

get_contact_id

get_bank_account_id

get_bank_branch_id

get_flex_ccid

get_transaction_ids

get_customer_trx_id

ARTPTRLS.pls also contains specifications for the following routines that you can use to get default values for transactions:

get_date_default_new

get_number_default_value

get_char_default_value

For more information, see: API Parameters and Transaction API Matrix.

API Parameters

The create, update, and delete routines have different sets of parameters. However, the following parameters are common to all three routines:

p_api_name The name of the program calling the API.

p_api_version Pass a value of 1.0. This verifies that you are calling a version of the API that is compatible with your feeder program.

p_init_msg_list Pass 'T' to have the API initialize the message stack for you; otherwise, pass 'F'.

p_commit Pass 'T' to have the API commit if it completes successfully; otherwise, pass 'F'.

p_validation_level (currently not used by the API)

p_return_status Returns one of the following values:
S - Successful completion
E - Validation/unexpected error
U - Unexpected error (e.g. Oracle error)

p_msg_count Returns a count of the messages put on the message stack.

p_msg_data If p_msg_count is 1, p_msg_data contains the message; otherwise, it is null.

p_errors List of errors encountered with additional information.

The following parameters are used in one or more API routines:

p_batch_rec Passes batch information to be stored in RA_BATCHES.

p_header_rec Passes header information to be stored in RA_CUSTOMER_TRX.

p_receivable_gl_date Passes the GL date for your Receivable account assignment.

p_lines_tbl Passes line information to be stored in RA_CUSTOMER_TRX_LINES.

p_tax_lines_tbl Passes tax information to be stored in RA_CUSTOMER_TRX_LINES.

p_freight_lines_tbl Passes freight information to be stored in RA_CUSTOMER_TRX_LINES.

p_salescredit_lines_ tbl Passes sales credit information to be stored in RA_CUST_TRX_LINE_SALESREPS.

p_dist_tbl Passes account assignments or account sets to be stored in RA_CUST_TRX_LINE_GL_DIST.

p_recac_tax_flag Pass 'T' to have the API recalculate your tax.

p_rerun_autoacc_flag Pass 'T' to have the API use AutoAccounting to recreate your account assignments or account sets.

p_backout_sc_flag Pass 'T' to have the API create adjusting account assignment entries instead of updating the existing sales credit lines.

p_backout_dist_flag Pass 'T' to have the API create adjusting account assignment entries instead of updating the existing account assignment lines.

Caching

The Transaction API uses several caches to store useful data such as accounting periods, customer information, and tax codes. Depending on the amount of memory in your system, you may want to reconfigure the cache size to improve system performance. Set the cache size to a higher number if you have a large amount of memory and want to increase caching; otherwise, set the cache size to a smaller number. The cache size is 1000 by default, but you can modify it by using the following library routine:

configure_caches

p_api_name	The name of the program calling the API.
p_api_version	Pass a value of 1.0. This verifies that you are calling a version of the API that is compatible with your feeder program.
p_init_msg_list	Pass 'T' to have the API initialize the message stack for you; otherwise, pass 'F'.
p_commit	Pass 'T' to have the API commit if it completes successfully; otherwise, pass 'F'.
p_validation_level	(currently not used by the API)
p_return_status	Returns one of the following values: S - Successful completion E - Validation/unexpected error U - Unexpected error (e.g. Oracle error)
p_msg_count	Returns a count of the messages put on the message stack.
p_msg_data	If p_msg_count is 1, p_msg_data contains the message; otherwise, it is null.
p_errors	List of errors encountered with additional information.

p_batch_rec	Passes batch information to be stored in RA_BATCHES.
p_header_rec	Passes header information to be stored in RA_CUSTOMER_TRX.
p_receivable_gl_date	Passes the GL date for your Receivable account assignment.
p_lines_tbl	Passes line information to be stored in RA_CUSTOMER_TRX_LINES.
p_tax_lines_tbl	Passes tax information to be stored in RA_CUSTOMER_TRX_LINES.
p_freight_lines_tbl	Passes freight information to be stored in RA_CUSTOMER_TRX_LINES.
p_salescredit_lines_ tbl	Passes sales credit information to be stored in RA_CUST_TRX_LINE_SALESREPS.
p_dist_tbl	Passes account assignments or account sets to be stored in RA_CUST_TRX_LINE_GL_DIST.
p_recac_tax_flag	Pass 'T' to have the API recalculate your tax.
p_rerun_autoacc_flag	Pass 'T' to have the API use AutoAccounting to recreate your account assignments or account sets.
p_backout_sc_flag	Pass 'T' to have the API create adjusting account assignment entries instead of updating the existing sales credit lines.
p_backout_dist_flag	Pass 'T' to have the API create adjusting account assignment entries instead of updating the existing account assignment lines.