Oracle Applications R12 Hub

R12 - 6 Months

2010-12-07T20:51:00.002-05:00

All 6 months have just passed since I had started updating this blog. I am very thankful to all the readers who have visited the blog and gained oracle R12 knowledge. I also specially thank the visitors who had left me comments on my posts. I am planning to spend more time behind the blog than before, so its my request to all visitors to kindly give your comments and suggestions about how you find this blog and the posts. Also let me know how I can improve this blog and what topics actually you like to read in Oracle Release 12. I will definitely try to work on it ! You can also email me at onlyappsr12@gmail.com.

Have a Good Time !!!!!!

R12 Banks and Branches Model

2010-10-12T21:56:00.003-04:00

As discussed in earlier post about the new tables in R12 Banks and Branches, today lets go through the internal and external uses of bank accounts. When a bank account is created in Cash Management they are created as Internal bank Accounts, and the bank accounts created using the Supplier form or customer form through are created as External Bank Accounts. To be more specific when the user defines a new bank account using the supplier form the information is stored in the IBY_EXT_BANK_ACCOUNTS and the internal bank accounts are stored in the CE_BANK_ACCOUNT.

The ability to share bank and bank account information between internal bank account definitions from different modules like Payables, Receivables, Treasury, Payroll and Global Financials is provided by Cash Management.

As we know TCA party model is used to model banks and bank branches as parties with the associated Relationships, Address, Contacts and Locations. Cash Management uses the following TCA tables for modeling banks and bank Branches

1) HZ_PARTIES
2) HZ_RELATIONSHIPS
3) HZ_RELATIONSHIP_TYPES
4) HZ_CONTACT_POINTS
5) HZ_PARTY_SITES
6) HZ_LOCATIONS
7) HZ_ORGANIZATION_PROFILES
8) HZ_ORG_CONTACTS
9) HZ_ORG_CONTACT_ROLES

HZ_ORGANIZATION_PROFILES --Stores attributes of banks and bank branches with the history of changes made to banks and bank branches.
HZ_PARTIES -- The contacts of a person at the bank, bank branch and bank account are defined as a party
HZ_CONTACT_POINTS -- Stores the contact details
HZ_ORG_CONTACTS --Stores the contact’s title
HZ_ORG_CONTACT_ROLES --Stores the contact’s purpose or role
HZ_LOCATIONS -- Stores the address details of banks and bank branches
HZ_PARTY_SITES --Stores party sites

CE_GL_ACCOUNTS_CCID -- Stores the accounting data pertaining to the bank account use

The bank, branch and bank account related attributes in AP_BANK_BRANCHES and AP_BANK_ACCOUNTS_ALL tables will be upgraded to HZ_PARTIES and the new tables in Cash Management as mentioned in my earlier post. The new data model allows the bank and bank branch entities to be defined separately allowing users more flexibility and to establish a hierarchical relationship between them.

R12 Bank Data

2010-08-23T23:09:00.003-04:00

1.What are the new tables in R12?

The following tables were obsoleted in R12:

AP_BANK_BRANCHES
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNTS_USES_ALL

The new tables in R12 are now under Cash Management module: Here are the tables

CE_BANK_ACCOUNTS: This table contains Legal Entity Level bank account information. Each bank account must be affiliated with one bank branch.

CE_BANK_ACCT_USES_ALL: This table stores Operating Unit level bank account use information

CE_PAYMENT_DOCUMENTS: This table Payment Documents to be used for Printed type Payments

CE_BANK_BRANCHES_V: view: Bank/Branches Info

CE_BANK_ACCT_USES_OU_V: view: Internal Bank Account Uses Info

The bank setup has been moved from AP to CE module, one should use Cash Management responsibility with all grants and permissions in order to create and maintain bank accounts. All the grants and permissions are done by Sysadmin using User management responsibility.

BI/XML Publisher Notes

2010-08-23T22:00:00.002-04:00

Before going into more details of XML Publisher, lets have a look at some of the XML standards.

The XML language specification defines the rules for XML document structure and how XML processors should read the document.

The DTD (document type definition) validates an XML document and provides the relationships of elements contained in the document.

XML Namespaces helps us to distinguish elements with different definitions having same name in the XML document.

The XML Path Language (XPath) provides syntax for searching the XML documents.

The Extensible Style Sheet Language (XSL) uses XSLT (Transformations) to transform an XML document to another document.

An XML Schema describes the XML document structure using DTD and namespaces.

Oracle BI Publisher - OPP (Output Post Processor)

2010-08-05T22:37:00.002-04:00

The Output Post Processor (OPP) is mainly used to process post actions of concurrent requests. For instance once the concurrent manager finishes running the concurrent program, it will communicate with OPP to apply the XML Publisher template and create the final output. The services run by OPP are managed through OAM (Oracle Applications Manager). A minimum of one OPP process should always be available in the system to process the concurrent request, otherwise OPP will complete with status WARNING. Depending on the availability the concurrent manager may contact local or remote OPP while running a concurrent request. The OPP service is a multi threaded and initiates a new thread for every concurrent request it processes. We can also view more details of OPP service in OAM. So depending on your work, one can manage the workshifts and the number of threads per process to handle the workload.

Oracle BI Publisher Process Flow

2010-08-03T18:55:00.005-04:00

As you have seen a brief introduction to BI/XML Publisher in my last post, today let us see the steps involved in processing a report using BI/XML Publisher.

1.Run the desired concurrent program.
2.The concurrent program calls the data template using the XDO (Extensible
data object) engine.
3.The data template calls the SQL query.
4.The query runs in the database and output is converted to XML.
5.XML output is generated by XML engine.
6.The OPP (Output post processor) formats the output based on the XML Publisher
template you have chosen and creates the final output like pdf, word, excel
and web page.
7.Output delivered to the User.

BI/XML Publisher XDO data engine plays an important role in generating the reports.The XMLP Report business service is the interface to this logical component that initiates the Java services to load the JAR files to the JVM. The Oracle BI Publisher XDO Engine then processes the XML data, XSL template to generate the report. We can also configure the XDO engine to create log files that can capture detail information and errors while generating the reports. The log files are more helpful in determining the cause of problems with the JAR files or the Oracle BI/XML Publisher layout templates.

Oracle BI/XML Publisher

2010-07-26T20:16:00.003-04:00

Oracle BI Publisher is a powerful report generation tool. It is a template based publishing tool available with Oracle E-Business suite. The report designing generated is mainly driven by desktop tools and XML technology. Lot of people get confused by the terms BI publisher and XML publisher, but to let you know both are same.The older version was known as XML Publisher, which is now called as BI Publisher.BI Publisher can be used as stand alone product whereas XML Publisher is always included in EBS.

Compared to traditional oracle reports, XML publisher provides output of the
file in various formats like excel, word, pdf and HTML at runtime. The data structure and presentation layer can separated unlike the standard oracle reports where data logic, presentation are in one file.

BI/XML Publisher can be integrated with E-Business suite as:

BI Publisher + Data Templates
BI Publisher + Oracle Reports
BI Publisher + OA Framework

BI/XMl Publisher can be mainly used for types of reports like Invoices,
sales orders, purchase orders, statements, pay slips, financial and business reports.

R12 AP/AR Netting Technical Information and Notes

2010-07-20T22:09:00.002-04:00

Here are some of the important notes on the Netting Process.

The most important columns for joining the FUN tables are Agreement_id and Batch_id.

AP/AR netting process cannot be used cross the operating units. The process is restricted to the customers and suppliers of one operating unit.

R12 AP/AR Netting Slides II

2010-07-20T20:12:00.001-04:00

In this posting lets see the steps involved in Netting Process:

1. Create a Netting Agreement
2. Create a Netting Batch

The last couple of slides walks us thru the netting receipt and the payment created automatically
by the netting process.

R12 AP/AR Netting Slides I

2010-07-20T19:42:00.002-04:00

In this posting lets see the basic Netting Setups:

Steps involved in processing the Netting Setups are:

1. Create a customer in AR
2. Create a Supplier in AP
3. Define a Netting Bank Account
4. Enter Customer Invoice
The invoice must be in status "Complete"
5. Enter Suppliers Invoice
The invoice must be validated and have terms as Immediate.

The above slides show us until the invoices are created in both AP and AR. The Netting Agreement and Batch details slides will be posted i next session.

R12 AP/AR Netting Technical Info

2010-07-18T22:03:00.003-04:00

Lets see some of the netting tables involved in the process. All the tables related to netting are stored under "FUN" module.

Here are the tables:

FUN_NET_SUPPLIERS_ALL – Stores Supplier Details
FUN_NET_CUSTOMERS_ALL – Stores Customer Details
FUN_NET_AR_TXNS_ALL – Stores Details of AR transactions
FUN_NET_AP_INVS_AL – Stores Details of AP invoices
FUN_NET_BATCHES_ALL – Stores Batch Details
FUN_NET_AR_TRX_TYPES_ALL – Stores Transaction types of AR
FUN_NET_AP_INV_TYPES_ALL – Stores Transaction types of AP
FUN_NET_AGREEMENTS_ALL – Stores Agreement Details

R12 AP/AR Netting Part3

2010-07-17T07:49:00.004-04:00

Lets have a look at the AP/AR Netting process and features.

The create and settle netting batch is the first step of netting process between the your company and the trading partners.Information from the netting setup is used to select transactions along with the criteria entered at the time of the netting batch submission, which includes netting reconciliation date and transaction due dates. The selected transactions are then reviewed and can be individually removed by the user before submitting for netting.Once the user submits the transactions for netting
and are approved by the trading partner, the netting process kicks off and reconciles and clears the selected transactions in AP and AR.

We will now discuss the criteria used for selecting AP Invoices and AR Transactions, and how they are ordered for netting.

AP Invoices selection criteria are:
• Transaction due date must be less than or equal to AP scheduled payment due date.
• AP scheduled payment due date must be between the Netting Agreement Start Date
and End Date.
• Only invoices for the supplier and supplier sites in the agreement are selected.
• Only invoices with the selected invoice types in the agreement are included.
• Operating unit of the invoices must be the same as the operating unit of the batch.
• Invoices must be approved and not be on Hold
• Invoices that have already been selected in a batch which is not yet in
‘Complete’ status are not included.
• Selected AP invoices are locked by populating AP_PAYMENT_SCHEDULES.Checkrun_id
Column for the selected invoices.The same Checkrun_id value is populated in
FUN_NET_BATCHES.Checkrun_Id for the given batch.

AR Transactions selection criteria are:
• AR Payment Schedule due date must be less than or equal to transaction date
mentioned in Netting.
• AR payment schedule date should be between the Agreement Start Date and end date.
• Transactions must be status "complete".
• Only Transactions for customer and customer sites in the agreement
are selected.
• Only transactions with the selected transaction types in the agreement are
included.
• Operating unit of the transactions and the batch must be same.
• Transactions that have already been selected in a batch which is not in
status complete will not be included in the new batch.
• The Receipt method associated with the transaction should not have a
Payment_type_code = ‘CREDIT_CARD’
• Payment schedule status must be open
• Transaction must not be a prepayment (RA_CUSTOMER_TRX.PREPAYMENT_FLAG = ‘N’)

The netting priority plays important role because it determines which transactions and invoices will be netted first.The Invoices and transactions are ordered by Supplier and Customer priority and by netting order rule selected in the agreement.

Once the batch is submitted AP and AR balances for transactions are validated.If the validation fails then the batch status is set to ‘ERROR’ and the AP invoices are unlocked.If all the validations are successful, then it is sent for approval, if approval is required, else the settle Netting batch process gets initiated.The process initiates with the validation of AR transactions because these records are not locked and they might have been modified since it got selected for the batch.If the validations fail, the batch is set to status "Cancelled". The process also validates the GL, AR, and AP periods are open for the batch settlement date. If any of the periods are not open, then the batch is set to status ‘ERROR’.

All the netting bank details like bank name,owner and number are derived based on the
FUN_AGREEMENTS.bank_account_id for the given batch.AP invoices in the batch are grouped by vendor,vendor site and invoice currency code.AP payments are processed using APIs and update the FUN_NET_AP_INVS_ALL table with Check_ID.AR receipt APIs are called to create and apply the receipts. The receipt id gets updated in FUN_NET_AR_TXNS.RECEIPT_ID. If all the processing completes successfully then the
batch is set to status "COMPLETE" and unlocks the AP invoices and netting agreements.

R12 AP/AR Netting Part2

2010-07-16T21:53:00.002-04:00

Let us now go thru the setup steps for netting process.The steps involved in setup are

• Netting Bank Account
• Receivables System Options
• Netting batch approver
• Chargeable Subcontracting
• Netting Agreement

The first step in setting up a netting agreement is to create a bank account using cash management responsibility.Since the netting process works similar to the application of receipts and payments, the companies will have to setup a cash account specifically for netting, to be used as a clearing account. Note that the balance with in the cash account will always be zero.Also this account has to be associated with AP/AR netting receipt class using the Receivables responsibility.

In Receivables System Options we have to check the “Allow payment of unrelated transactions” check box to create netting agreements across multiple
unrelated customers.

Next step in the setup process is to select the trading partners. Users can select one or multiple supplier and customers to net.You can also select the site or leave the site blank, if you leave it blank all sites for selected trading partner will be included in the netting process.If netting process requires approval then the approver must be set up as a contact for the trading partner(Customer / Supplier). The contact must be setup with an email address. The two options available for approver are APPROVE and REJECT. If APPROVE is selected the administrator can submit the batch for netting process even if the trading partner does not approve the batch by the response date mentioned on the netting agreement page.If selection is REJECT then the administrator can resubmit the batch or cancel the batch and release all the transactions selected for netting.

If the user would like to use OSA(outsourced assembly processing), two profile options must be enabled.One "Chargeable Subcontracting Enabled" and second "JMF:Enable chargeable subcontracting". Both the profile options should be enabled at site level.The first one enables users to include OSA items in netting process.The second one allows items in purchasing to be marked as OSA.

Create a netting agreement in order to setup the rules that will decide which transactions can be selected for netting and how they are ordered and processed once selected.

R12 AP/AR Netting Part1

2010-07-15T20:33:00.004-04:00

AP/AR Netting solution is a standard feature delivered in R12 Payables and Receivables.The netting process is a new feature offering companies an increase level of visibility across Trading partners and control over cash flow. AP/AR Netting solution allows you to net your Payables invoice balances against your Receivables invoice balances for those Customers who are also your Suppliers.For example, If you have a customer who is also a supplier for you company, then AP/AR netting allows you to pay the net difference between how much you owe the supplier and how much he owes company rather than him paying you and then company paying him.

Prior to Release12 this process was mainly used to address the needs of US Federal Financials which has now become available to everybody. This solution provides the following benefits.

Netting allows companies to see their complete trade balance with trading partners both suppliers and customers. It also provides complete control over cash flow and collection process.Netting allows customers to define a netting agreement, incorporate the business rules and transaction criteria allowing them to run netting process with limited intervention. It improves efficiency and reduce costs by eliminating unnecessary payments, simplifies reconciliation, reduces bank charges and eases collection activities.

The new features for AP/AR Netting are the netting business rules that may be enforced on a global bases where regional administrators have the ability to tailor their standards to accommodate the local and country specific regulations.The netting agreement controls how a group of trading partners net payables and receivable transactions.The business rules include the netting agreement details such as name and effective dates and rules, based on which the transactions are selected, and how they are ordered and prioritized once selected.Trading partners have an option to net transaction for a single trading partner, one supplier and one customer or for multiple trading partners, multiple supplier or customers.

Based on the netting agreement and user criteria companies can execute an uninterrupted netting process which results in a netting batch. The netting batch compares AR and AP balances, allows users to optionally add or remove transactions and create a AP payment in payables and an offsetting receipt in AR. AP/AR netting provides easy to use enquiry screens to view netting batch details online including which transactions were netted and corresponding payment and receipt information. Two new reports available now are, One Proposed Netting report contains the transactions selected for netting which is created during the create netting batch process.This report is also sent to trading partner where approval is required. Second Final netting report which is created at the end of the netting process when the transaction have been created. This report shows the final netting details i.e the payment and receipt number and netted amount.

R12 Supplier Banks

2010-07-14T21:53:00.002-04:00

Supplier Bank Accounts can be imported during Supplier and Supplier Site Open Interface

Successfull creation of Supplier and Supplier Site a Payee is created in IBY (Payments application) for the Supplier and Supplier Site.If corresponding rows are created in IBY_TEMP_EXT_BANK_ACCTS table then we can use IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT to create bank account and associate it with the payee.In order to import supplier bank accounts during Supplier and Supplier Site Open Interface, we have to populate the IBY_TEMP_EXT_BANK_ACCTS table.
Each row in the IBY_TEMP_EXT_BANK_ACCTS table is associated with the Supplier or Supplier Site if the following condition satisfied

IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref1 = AP_SUPPLIERS_INT.vendor_interface_id (for Suppliers)
OR
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2 = AP_SUPPLIER_SITES_INT.vendor_site_interface_id (for Supplier Sites)

R12 Supplier Bank Accounts Slide Show

2010-07-13T22:27:00.004-04:00

Here is the slide show of all the screens used for creating Supplier bank.

R12 Supplier Bank Accounts

2010-07-11T11:11:00.007-04:00

So far we have seen creation of Bank, Bank Branch and Bank Account. Lets now focus on technical information.

In R12 Banks and their Branches are stored as Parties in HZ_PARTIES. Both are linked together through relationships in HZ_RELATIONSHIP table. The Bank accounts are stored in the Oracle Payments Application. All tables in this application are prefixed with IBY. The bank accounts 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.

The table IBY_ACCOUNT_OWNERS is used to identify the Supplier Party using the ext_bank_account_id to link the 2 IBY tables that the Bank Account belongs to. Finding the Bank account attached to supplier site can be done through linking the tables IBY_EXTERNAL_PAYEES_ALL and IBY_PMT_INSTR_USES_ALL. When a Bank account is assigned a record is created in the IBY_PMT_INSTR_USES_ALL table (Payment Instruments table) and is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Each instrument record is linked to an external payee record in IBY_EXTERNAL_PAYEES_ALL using the ext_pmt_party_id column. The external payee record links us to supplier party ID (payee_party_id), supplier party site ID (party_site_id) and supplier site ID (supplier_site_id). The IBY_EXTERNAL_PAYEES_ALL table stores every supplier site defined and the supplier itself. The IBY_PMT_INSTR_USES_ALL is the main table which lets us know the specific Site/Supplier that the Bank Account has been assigned to.

The query below gives you the links required to get a matching Bank account assigned to supplier site.

SELECT hp.party_name supplier_name
, as.segment1 supplier_number
, assa.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties hp
, ap_suppliers as
, hz_party_sites hps
, ap_supplier_sites_all assa
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE hp.party_id = as.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = assa.party_site_id
AND assa.vendor_id = as.vendor_id
AND iep.payee_party_id = hp.party_id
AND iep.party_site_id = hps.party_site_id
AND iep.supplier_site_id = assa.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY 1,3

R12 Supplier Bank Accounts

2010-07-11T10:59:00.002-04:00

R12 Supplier Bank Accounts

2010-07-11T10:53:00.002-04:00

R12 Supplier Bank Accounts

2010-07-11T10:49:00.003-04:00

R12 Supplier Bank Accounts

2010-07-11T10:42:00.002-04:00

R12 Supplier Bank Accounts

2010-07-11T10:37:00.000-04:00

R12 Supplier Bank Accounts

2010-07-11T10:14:00.002-04:00

R12 Supplier Bank Accounts

2010-07-10T12:25:00.004-04:00

In my earlier posting we have seen how to create a supplier and supplier site, now lets go thru the steps involved in creating bank, bank branch and bank accounts. Also will go thru the technical information underlying the bank model.

R12 Bank Account model provides a single access point for defining and managing internal bank accounts for Oracle Payables and Oracle Receivables. A single Legal Entity is granted ownership for each internal bank account. In order to grant access to the Legal Entities navigate to Roles &
Role Inheritance using User Management responsibility.

Fill in the required information like type (roles & Responsibilities), name (role name) and
application (Payables) and click on GO. Click on update icon for your selection of role and code.
In the next page click on Security wizards button which will navigate you to wizards where you have to click on Run wizard for CE UMX Security wizard. Select Add Legal Entities button to give access to Bank Accounts. Once you choose your Legal entity select all the options like Use,
Bank Accounts Grants Maintenance and Bank Accounts Transfer, click on Apply button.

Please follow the screens for creating Bank, Bank Branch and Bank Account.

Tips for Supplier Issues

2010-07-08T21:00:00.005-04:00

1. Navigate to Tax Registration number in Supplier form

The Tax Registration Number is in the Organization page of the Supplier form. Query for a any supplier and click on the Organization link located on the left side menu. Once you scroll down you can see the Tax and Financial Information region. Tax Registration Number is one of the fields in that region.

2. Automatically Populate the Site Number for a supplier

In order to generate the site number automatically the profile option HZ:Generate Party Site Number should be set to Yes.

3. Registry Id is required while entering supplier

Registry Id field is required while entering a Supplier Organization:
Supplier Entry > Enter Organization Name >
Some times you may receive an error message "Form Validation Failures" A Value must be entered for "Registry ID". The form should not display the Registry ID field if the "HZ: Generate Party Number" is set to Yes. In order to prevent the field from appearing set the profile to NO.

4. The four setup options that control supplier defaults are financial options, payables options,
purchasing options and Payables system setup.

5. In order to pay certain type of suppliers by batch, limit the payment batch under certain amount and restricting single payment for a supplier site with amount restrictions can be achieved by setting up the suppliers with pay groups for each type, and use them when running the payment batch with Maximum/Minimum payments.

6. Supplier site interface does not import sites

This might be due to the NULL value in the column AP_SUPPLIER_SITES_INT.VENDOR_SITE_INTERFACE_ID. To overcome this issue populate the AP_SUPPLIER_SITES_INT.VENDOR_SITE_INTERFACE_ID with AP_SUPPLIER_SITES_S.Next Val.