Oracle BI/XML Publisher

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

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

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

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

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

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

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

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

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

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

R12 Supplier Bank Accounts

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

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

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

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.