Category Archives: Apps Functional

Script to get AP Invoice Aging for Prepayment(ADVANCES)

 

AP Invoice Aging for Prepayment(ADVANCES)

Below script is to get the Payables Aging fro Pre-Payment Invoices:

 

Thanks to Fakhra Imran for sending the Query.

Auto Invoice Basic Setups with an Example

Auto Invoice Setup with Sample Script

  1. Define Common Accounts Receivable Configuration: Setup the Accounting Flexfield which is a common Accounts. The Accounting Flexfield Setup is done using General Ledger Responsibility. Please see the link for more details: Define Common Accounts Receivable Configuration
  2. Define Common Accounts Receivable Configuration: Transaction Flexfields in Oracle Receivables uniquely identify each transaction line that is imported using Auto Invoice. Please see the link for more details: Receivables Descriptive Flexfields
  3. Receivables Profile Options: Profile options impact the behavior of Auto Invoice. Please see the link for more details: Receivables Profile Options
  4. AR Transaction Types: Oracle Receivables by seeded provides the following five transaction types: Invoice, Credit Memo, Projects, Credit Memo, Projects Invoice, Intercompany. Based on the Business requirements needs to setup a new one. Please see the link for setting up a new transaction type: AR Transaction Types
  5. AutoAccounting Rules: AutoAccounting enables to create default accounts for revenue, receivable, freight, tax, unearned revenue, unbilled receivable, late charges, bills receivables accounts, and AutoInvoice clearing (suspense) accounts when a transaction is entered or imported from API/AutoInvoice Import Program. Please see the link for more details: AutoAccounting Rules
  6. AutoInvoice Line Ordering Rules: By using this setup the Transaction lines will be ordered in a single transaction based on the setups. Please see the link for more details: AutoInvoice Line Ordering Rules
  7. AutoInvoice Grouping Rules: By using this setup the Transaction lines inserted in the Transactions interface tables will be grouped and be created in single Invoice based on the setups. Please see the link for more details: AutoInvoice Grouping Rules
  8. Receivables Payment Terms: Payment terms allows to specify the due date, installment and discount date for open items. There is a BFB Balance Bill Forwarding option which is newly added in R12 which allows to send the dunning letters based on the Day mentioned and also allows to send the letters on the customer level as well as at the site level. Will be adding BFB in detail. Please see the link for setting up Payment Terms: Receivables Payment Terms
  9. Transaction Sources: Based on the Transaction Source setup Transactions, Batch Source, AutoInvoice Options, Customer Information, Accounting Information, Sales Credit validation and Other Information will be dependent. Please see the link for more details for setting up Transaction Sources: Transaction Sources
  10. Receipt Classes and Methods: By using this setup the default payment method is derived for the Transactions. Please see the link for Setup: Receipt Classes and Methods Setup
  11. Receivables Customer Profile Classes: Based on the Profile class the Customers can be Categorized. Please see the link for setup:  Receivables Customer Profile Classes
  12. Create Customer for Auto Invoice: Create a new customer by using the Receipt Classes, Methods, Customer Profile classes. Please see the link for defining the Customer: Create Customer for Auto Invoice and API with all required Setups
  13. Receivables System Options: In Receivables System Options we can define the Accounting setup, Trans and Customers, Claims, Miscellaneous Setups. Please see the link for more details: Receivables System Options
  14. Receivables Accounting Periods: Periods should be open for doing the transaction and create the accounting. Please see the link for more details: Receivables Accounting Periods
  15. Remit-To Address: By using the Remit To Address, customers can send the payment details directly to Bank. Please see the link for more details: Remit-To Address
  16. Populate Interface Table and Run Auto Invoice Import: Populate ra_interface_lines_all tables using sample script and run the auto invoice import. If any issues check the Log file for error details. If any issue with AutoAccounting Rules check the segments account details. Please see the link for more details: Auto Invoice Import

Source: Metalink.oracle.com

Related posts:

Auto Invoice Import

 Auto Invoice Import

Sample scripts to populate the table for various scenarios:

Details for each field are explained below:

INTERFACE_LINE_CONTEXT – identifies the Transaction Flexfield used by the interface data this helps AutoInvoice understand what data is being passed in the interface table fields. The value here ties in with the setup created in Descriptive Flexfields section, where we created the Line Transaction Flexfield ‘XAOA’.
Use the value = ‘XAOA’

INTERFACE_LINE_ATTRIBUTE1 and INTERFACE_LINE_ATTRIBUTE2 – the combination of values in these two fields uniquely identify the transactions which needs to be created.
Use the values = ‘All Oracle Apps’ and ‘ALLORACLEAPPS.COM’

AMOUNT – this field contains the values of the transaction. This value should be the product of the numbers passed in for QUANTITY * UNIT_SELLING_PRICE.
Use the value = 1000.00

BATCH_SOURCE_NAME – the value specifies here identifies the transaction batch source to be used by AutoInvoice when interfacing this transaction. In Creating the Batch Source, a Batch Source ‘AOA Transaction Source’ is created.
Use the value = ‘AOA Transaction Source’

CONVERSION_RATE – For non-functional currency transactions, this field would contain the exchange rate value.
Use the value = 1

CONVERSION_TYPE – Specify the conversion rate type.
Use the value = ‘User’

CURRENCY_CODE – Indicate the currency code of the transaction.
Use the value = ‘USD’

CUST_TRX_TYPE_ID – identifies the ID associated to the transaction type that is created in  Transaction Types where defined as
‘All Oracle Apps INV’.

DESCRIPTION – enter a line item description.

GL_DATE – the value specifies will be used by AutoInvoice as the GL_DATE of the transaction. Typically the period in which this GL_DATE value falls should be an open period.

LINE_TYPE – indicates the type of line this interface data defines.

ORIG_SYSTEM_BILL_ADDRESS_ID and ORIG_SYSTEM_BILL_CUSTOMER_ID – identifies the Address ID and Customer ID associated to the invoice.

QUANTITY – specify the number of items.

UNIT_SELLING_PRICE – specify the price of each item.

TERM_ID – identifies the term ID associated with the setup created in Payment Terms.

TAXABLE_FLAG – indicates whether or not the transaction is taxable.

AMOUNT_INCLUDES_TAX_FLAG – indicates whether or not the amount is inclusive of taxes.

SET_OF_BOOKS_ID – identifies the Set of Books ID associated to the Operating Unit.

ORG_ID – indicates the ID of the Operating Unit against which this transaction is created.

After the Interface table is inserted then Run the Auto Invoice Import Master Program:

 

Result:

 

 

Review Invoices:

Will be extending this section with Distributions and sales credits.

Related posts:

Create Customer for Auto Invoice and API with all required Setups

Create Customer for Auto Invoice and API with all required Setups

Creating a Customer

Responsibility: Receivables Vision Operations
Navigation: Customers > Customers

Click on the Create Button

Enter the Customer information and the account details as below:

Check the Account Details:

Based on the Profile class all other information is defaulted.

Create a receipt Method for the Customer

and click on Apply.

When all the setups are done the Customer is eligible for Auto Invoice Import or importing transactions with API with minimal data populated in the Interface table or Minimum data is passed to the API so the rest of the values will be defaulted.

Related posts: