R12 Customer Bank Accounts

 The data model for storing Banks and Bank Account information has changed for this release of the Oracle Applications Suite.

Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

The Bank Accounts themselves are now stored in the new Oracle Payments Application. All tables are prefixed with the Application Short Name, IBY. The bank accounts themselves are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

Now, linking the Bank Account to the relevant Customer is a bit more involved. The table IBY_ACCOUNT_OWNERS can be used to identify the Customer Party (using the ext_bank_account_id to link the 2 IBY tables) that the Bank Account belongs to. Identifying the Customer Site that the Bank Account is actually attached to is a little bit trickier!

This is done through linking together the following tables IBY_EXTERNAL_PAYERS_ALL and IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument Record links to an External Payer Record held in IBY_EXTERNAL_PAYERS_ALL using the ext_pmt_party_id. It is the External Payer Record that links us to a customer and Customer Site Use.

There is a record stored in the IBY_EXTERNAL_PAYERS_ALL table for every customer site use defined. The IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site Use that the Bank Account has been assigned to.

The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name                   customer_name
,      cust_acct.account_number
,      cust_uses.site_use_code
,      cust_loc.address1
,      cust_loc.address2
,      cust_loc.address3
,      cust_loc.address4
,      cust_loc.city
,      cust_loc.postal_code
,      bank.party_name                   bank_name
,      bank_prof.home_country
,      branch.party_name                 branch_name
,      branch_prof.bank_or_branch_number branch_number
,      account.bank_account_num
,      account.bank_account_name
FROM   hz_parties bank
,      hz_relationships rel
,      hz_parties branch
,      hz_organization_profiles bank_prof
,      hz_organization_profiles branch_prof
,      iby_ext_bank_accounts account
,      iby_account_owners acc_owner
,      iby_external_payers_all ext_payer
,      iby_pmt_instr_uses_all acc_instr
,      hz_parties cust
,      hz_cust_accounts cust_acct
,      hz_cust_acct_sites_all cust_site
,      hz_cust_site_uses_all cust_uses
,      hz_locations cust_loc
WHERE  1=1
AND    bank.party_id = rel.object_id
and    bank.party_type = rel.object_type
AND    rel.object_table_name = 'HZ_PARTIES'
AND    rel.relationship_code = 'BRANCH_OF'
AND    rel.subject_id = branch.party_id
AND    rel.subject_type = branch.party_type
AND    rel.subject_table_name = 'HZ_PARTIES'
AND    bank.party_id = bank_prof.party_id
AND    branch.party_id = branch_prof.party_id
AND    bank.party_id = account.bank_id
AND    branch.party_id = account.branch_id
AND    account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND    acc_owner.account_owner_party_id = cust.party_id
AND    account.ext_bank_account_id = acc_instr.instrument_id
AND    acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND    ext_payer.cust_account_id = cust_acct.cust_account_id
AND    cust_acct.cust_account_id = cust_site.cust_account_id
AND    cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND    cust_uses.site_use_id = ext_payer.acct_site_use_id
AND    cust_uses.location = cust_loc.location_id
AND    cust.party_id = cust_acct.party_id;

AutoInvoice in Oracle Apps R12

AutoInvoice in Oracle Apps R12

AutoInvoice is a powerful, flexible tool you can use to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos, and on–account credits in Oracle Receivables. You use a custom feeder program to transfers transaction data from an external system into the AutoInvoice interface tables. AutoInvoice then selects data from the interface tables and creates transaction in Receivables. Receivables rejects transactions with invalid information to ensure the integrity of your data. You can run AutoInvoice together with Customer Interface or separately.

Oracle Receivables can create invoices from the following Oracle applications:  Oracle Order Management, Oracle Projects,  Oracle Service, Oracle Property Manager.
Oracle Receivables can create invoices from the following non-Oracle applications:  Legacy system (for transaction history),  Non-Oracle billing applications, Non-Oracle order entry applications.

Oracle Receivables uses three interface tables for AutoInvoice:

1. RA_INTERFACE_LINES
2. RA_INTERFACE_DISTRIBUTIONS
3. RA_INTERFACE_SALESCREDITS

AutoInvoice transfers transaction data from the above three interface tables into the following Receivables tables:

• RA_BATCHES_ALL
• RA_CUSTOMER_TRX _ALL
• RA_CUSTOMER_TRX_LINES _ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• RA_CUST_TRX_LINE_SALESREPS_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL

RA_INTERFACE_LINES:

• This table contains information relating to all transactions to be processed by AutoInvoice.
• Transactions include invoices, debit memos, credit memos, and on-account credits.
• Each record contains line, tax, freight, or finance charges information.
• The Line_Type field identifies the type of information contained in the record.
• A record can be a parent record: Line, Header Freight, or Charges; or a child record: Tax or linelevel Freight.
• A child record is linked to the parent record using the Link-To Transaction flexfield.

RA_INTERFACE_DISTRIBUTIONS:

• This table contains accounting distributions to be used by the transactions defined in RA_INTERFACE_LINES.
• Accounts defined in this table override any accounts created using AutoAccounting.
• You can choose to pass some or all account information to AutoInvoice. Any accounts that are not passed will be derived using AutoAccounting.
• Records in this table are linked to records in the RA_INTERFACE_LINES table using the Transaction flexfield.
• Not required if AutoAccounting determines GL distributions.

RA_INTERFACE_SALESCREDITS:

• This table contains all sales credit information for the transactions in the RA_INTERFACE_LINES table.
• The two tables are linked using the Transaction flexfield.
• Not required if not tracking sales credit.

What occurs during auto-invoice:

1. Populates the RA_Interface_Lines, RA_Interface_distribution and RA_Interface_salescredit tables.
2. Lines are grouped and ordered by the grouping rule and line ordering line. Grouping rules are mandatory and determine how transaction lines are grouped into transactions. Optionally, you can use line-ordering rules to determine the order in which lines are displayed on a transaction.
3. Tax, freight, commitments and credit memos are linked to transaction line by transaction line, transaction reference and transaction link-to descriptive flex fields. To uniquely identify imported transactions and link the tax, freight, commitments and credit memos, define the Transaction flexfields.
4. GL date is determined.
5. GL accounting code combinations are assigned using auto accounting rule.
6. TAX is determined.
7. All transactions are batched.
8. Validated lines are used to create the transaction.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.

Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

Transaction flexfields:

• Transaction flexfields are descriptive flexfields that AutoInvoice uses to uniquely identify transaction lines.
• Because they are unique for each transaction line, they can also be used to reference and link to other lines.
• Receivables lets you determine how you want to build your transaction flexfield structure and what information you want to capture.
• Define a flexfield for each import source. Specify which one to use during import.

Types of transaction flexfields:

• Invoice Header (optional): Specifies invoice header information
• Line (required): Uniquely identifies invoice lines
• Link-To (optional): Link tax and freight to invoice lines
• Reference (optional): Links credit memos to transactions

What is inside AutoInvoice?

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

• Master (RAXMTR)
• Import (RAXTRX)
• Purge (RAXDEL)

1] Auto Invoice Master program:

Selects and marks records in the interface tables to  be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

• Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables.
• Marks interface records for processing by marking request_id.
• Submits multiple workers for Parallel Processing by creating instances for request.

2] Auto Invoice Import Program:

Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

• Workhorse of Auto invoice
• Validates data
• Inserts records
• Deletes interface data
• Only when system option purge set to ‘Y’

3] Auto Invoice Purge Program:

Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run, and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

AutoInvoice Exception Handling:

Records that fail validation are called ‘Exceptions’

• Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL.
• Errors can be corrected in the Exception Handling window.
• Once corrections are made, Auto invoice must be resubmitted.
• Records that pass validation get transferred to Receivables tables.

AutoInvoice Exception Handling Windows:

• Interface Exception window displays exception messages associated with all invalid records.
• Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors.
• The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window.
• Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error.
• Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button.
• Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column.

List of Receipts API in Oracle Receivables

List of Receipts API in Oracle Receivables

Below is the list of some of the Receipt API’s in Oracle Receivables. Receipt APIs provide an extension to existing functionality for creating and manipulating receipts through standard AR Receipts forms and lockboxes.

AR_RECEIPT_API_PUB is the main package that has several procedures to perform different actions.

1] AR_RECEIPT_API_PUB.CREATE_CASH

Use this procedure to create a single cash receipt for payment received in the form of a check or cash.

2] AR_RECEIPT_API_PUB.APPLY & AR_RECEIPT_API_PUB.APPLY_IN_DETAIL

Use these procedures to apply the cash receipts from a customer to an invoice, debit memo, or other debit item.

3] AR_RECEIPT_API_PUB.UNAPPLY

Use this procedure to unapply a cash receipt application against a specified installment of a debit item or payment schedule ID.

4] AR_RECEIPT_API_PUB.CREATE_AND_APPLY 

Use this procedure  to create a cash receipt and apply it to a specified installment of a debit item.

5] AR_RECEIPT_API_PUB.REVERSE

Use this procedure to reverse cash and miscellaneous receipts.

6] AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT

Use this procedure to apply a cash receipt on account.

7] AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT

Use this procedure to unapply an on-account application of a specified cash receipt.

8] AR_RECEIPT_API_PUB.ACTIVITY_APPLICATION

Use this procedure to create an activity application on a cash receipt, including Short Term  Debit (STD) and Receipt Write-off applications.

9] AR_RECEIPT_API_PUB. ACTIVITY_UNAPPLICATION

Use this procedure to create a reversal of an activity application on a cash receipt including Short Term Debt and Receipt write-off.

10] AR_RECEIPT_API_PUB.CREATE_MISC

Use this procedure to create a miscellaneous receipt.

11] AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT

Use this procedure to apply a cash receipt to another open receipt. Open receipts include unapplied cash, on-account cash, and claim investigation applications.

12] AR_RECEIPT_API_PUB.UNAPPLY_OPEN_RECEIPT

Use this procedure to reverse a payment netting application on a  cash receipt.

HZ tables in Oracle Receivables

HZ(TCA) tables in Oracle Receivables

This article describes few important HZ tables in AR and their relationships with each other.

HZ_PARTIES:

The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.

Few Important Columns are

• PARTY_ID: Party identifier
• PARTY_NUMBER: Unique identification number for this party
• PARTY_NAME: Name of the party
• PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:

The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.

Few Important Columns are

• PARTY_SITE_ID: Party site identifier.
• PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
• LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
• PARTY_SITE_NUMBER: Party site number.
• PARTY_SITE_NAME: User-defined name for the site.
• ADDRESSEE: Addressee information.

HZ_LOCATIONS:

The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.

Few Important Columns are

• LOCATION_ID: Unique identifier for this location
• COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
• ADDRESS1: First line for address
• ADDRESS2: Second line for address
• ADDRESS3: Third line for address
• ADDRESS4: Fourth line for address
• CITY: City
• POSTAL_CODE: Postal Code
• STATE: State
• ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:

The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.

Few Important Columns are

• CUST_ACCOUNT_ID: Customer account identifier
• PARTY_ID: A foreign key to the HZ_PARTY table.
• ACCOUNT_NUMBER: Account Number
• CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
• CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

Few Important Columns are

• CUST_ACCT_SITE_ID: Customer site identifier
• CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
• PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
• BILL_TO_FLAG: Indicates if this is a Bill-To site.
• SHIP_TO_FLAG: Indicates if this is a Ship-To site.
• MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:

The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.

Few Important Columns are

• SITE_USE_ID: Site use identifier
• CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
• SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
• PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:

The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

Few Important Columns are

• CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
• CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
• STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:

The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.

HZ_PARTY_RELATIONSHIPS:

The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.

Relationship between the tables