Three banks you can manage in EBS

• House Bank or internal bank
• External bank for supplier and Customer
  • Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.
• Intermediary bank for SEPA payment : An intermediary bank is a financial institution that as a relationship with the destination bank (in this case the supplier bank account you are setting up) which is not a direct correspondent of the source bank (the disbursement bank in AP/Payments), which facilities the funds transfer to the destination bank.

You can enter intermediary bank accounts on Suppliers->Entry->Banking Details->Bank Account Details

This is important when paying a foreign supplier from a domestic disbursement account, there may be an intermediary bank used, and it would be set up on the supplier bank account. Although the intermediary bank UI is owned by Payments, the implementation is as embeddable UI components in pages owned by i-supplier Portal (suppliers) and AR/Collections (customers).

Some information

1. The supplier bank account information is in the table: IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table HZ_PARTIES.
2. Creating a supplier in AP now creates a record in HZ_PARTIES. In the create Supplier screen, you will notice that that Registry_id is the party_number in HZ_Parties.
3. The table hz_party_usg_assignments table stores the party_usage_code SUPPLIER, and also contains the given party_id for that supplier. Running this query will return if customer was a SUPPLIER or CUSTOMER
4. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds
5. IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.
6. The master record that replaces PO_VENDORS is now AP_SUPPLIERS. PO_VENDORS is a view that joins AP_SUPPLIERS and HZ_PARTIES.
7. The table that hold mappings between AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID is PO_SUPPLIER_MAPPINGS. Query by party_id.
8. The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created.

ER Diagram(Bank Model)

Oracle Table Involved

• IBY_EXTERNAL_PAYEES_ALL : This stores supplier information and customer information
• IBY_EXT_BANK_ACCOUNTS : This storage for bank accounts
• IBY_EXT_PARTY_PMT_MTHDS : This storage for payment method usage rules.
• IBY_CREDITCARD : stores the credit card information for a customer
• IBY_EXT_BANK_ACCOUNTS :This Stores external bank accounts . These records have bank_account_type = Supplier
• IBY_ACCOUNT_OWNERS :stores the joint account owners of a bank account
• IBY_PMT_INSTR_USES_ALL : This stores data from AP_BANK_ACCOUNT_USES_ALL for payment instruments assignments .This information is stored in the following iPayment (IBY) tables:

Read the rest of this entry »

This is short note for subledger reconciliation reports which helps during period end closure.

Reconciliations – Subledger Accounting on-line account reconciliation to streamline the reconciliation process.

1. Subledger Journal Entry Report : This will display detailed Header & Line information for Subledger and General Ledger journal entries by Source & Category
2. Subledger Account Analysis : This report can be used to display Transactions transferred to & posted to a GL account for a period or range of periods
3. Payables Third Party Balances : If you are managing third party account, then you can see Balances for your Third Party Control accounts
4. Open Items Listing :
   1. For this you need define this report by Segment or Accounting Flexfield.
   2. This report display Detail/Summary by Accounts/Third Party.
   3. This report output available in multiple output formats .
   4. This will shows only Open Transactions.
   5. This is very similar to SAP Open Item Report seeded report. User coming from SAP to Oracle Financial now find similar report with less information. Still SAP's Compact Journal Report is missing from Oracle's SLA.
5. Subledger Period Close Exception Report :During the period end run the Subledger Period End Close Exception report, and you can find the data with these information.
   1. Incomplete: Not ready for accounting, Run Subledger’s validation programs!
   2. Unprocessed: Transactions exist but have as yet not been accounted
   3. This report help in resolving exceptions quickly so that exceptions are not left to slow down the period-end close

SLA(Sub Ledger Accounting) -Characteristics

• All SLA data is stored in SLA and is imported to GL from SLA.
• A protocol exists to ensure all subledgers are transferring data into SLA consistently
• Creates the final accounting for Subledger Journal Entries (JEs) and transfers the accounting to GL
• During reconciliation subledgers to GL, all reconciliation reports point to the SLA tables
• Concurrent program used to transfer data into SLA and import to GL.
• SLA Can Fill In Individual Segments or Entire AFF
• SLA Can Create Multiple Representations of Each Subledger Transactions
• Stores a complete and balanced Subledger JE in a common model for each “business event” that requires accounting

Read the rest of this entry »

I have posted a number of previous notes defining and discussing Oracle diagnostics. This tool is enhanced because of new security model.Let's take a quick look on revisiting this utility.

Six reason why 'Everyone like Oracle Diagnostic'

Oracle E-Business Suite Diagnostics is a framework and a repository of diagnostic tests. These are six top reason why every one liked this product.

1. This is shipped product within the box
2. This is FREE ...
3. This utility based out of common known problems
4. Test Result with corrective actions information
5. Available for most of the products
6. You will get enough documentation on metalink.

Changes in R12.1

• Diagnostics now have role-based security
• Customizable security layer implemented using Role- Based Access Control (RBAC)
• The name got changed to "Oracle E-Business Suite Diagnostics" from 'Support Diagnostic Tools"
• The access is now taken care by Grant via Diagnostics Roles.
• 'Application Diagnostics' responsibility is new Navigational menu.
• Within new security model Sensitivity Levels is now being introduced which have three value 1,2,3.
• In other word Sensitivity is basically an attribute of the test that indicates the kind of data it is dealing with.
• Oracle Diagnostic is now a high performing tool coupled with Multi-threaded execution engine.
• Oracle Diagnostic have capability to includes Data masking.
• You can design your Custom roles based out of your business need.
• Oracle Diagnostic output is tagged with your SR, by passing SR number.

Diagnostics Architecture

Read the rest of this entry »

UPK Pre-built Content

You can take advantage of UPK pre-built content for pre-"go-live” and “post go-live” support training need. Pre Built Content are created by the Applications Development Documentation teams and documenting best practices for system processes within the Oracle applications.

• You can use this tool to educate your project teams, core user, power user, and process owners before installing an instance of the application.
• You can utilize while rolling out a new application
• With this tool you can create system process documentation, user acceptance test scripts, training, and support materials without acquiring any extra skill.
• You can utilize these for your “pre-go-live” training and “post-go-live” support training need.
• You can fit this anywhere in you development/deployment methodology
• With Pre-content you can get some help in project scoping and fit/gap.

Read the rest of this entry »

If your business need to drill down the Reporting for your financial report like Balance sheet ,P & L or Cash Flow then you can explore these options in EBS :

1. Financial Analyzer
2. Report Manager
3. Summary Accounts online drill down
4. Third Party tools like GL WAND where you can run the report or multiple report through Excel and drill down, which will show both sides of the journal.

R12 "Drilldown" Vanilla Functionality

With this feature business user allowed to drill into report values using the Account Analysis and Drilldown feature once the FSG output is generated. This is exactly the same as available from the applications using the Account Inquiry form.

If drilldown from the report is required, the underline template should be drill-enabled while uploading to the server. What you need to do you can use template builder and enable this feature which comes within box.

Those who are still on R11i can achieve drilldown feature only if you having FRM.H plus Patch 5377946. You can find my details under report manager availability section in this pots.

Power of Report Manager

This is Framework in EBS for web based reporting of financial data generated by mostly Financial Statement Generator (FSG). You can submit and publish the following types of reports: FSG Reports, Ad Hoc FSG Reports, Standard Reports, and RX-Only Reports.

Therefore this tool coupled with end user for e-reporting need directly with the self service pages. Additionally report manager can provide you :

• Reporting need into multiple formats(Excel, PDF, html)
• You can manage from central secured report repository
• These are supported based out of MS-Word & MS-Excel based templates, which is one productive office tool used for finance department.
• You can customize the look and feel of report output
• Distribution based on expansion values and time frames
• Facilitates data comparison and trend detection
• Preview reports prior to mass distribution

Take a note :

1. Report Manager is the reporting processes and Web ADI is the data loading processes.
2. Report Manager is included in the ATG R12 Product Family Pack Patch.

Report Manager Architecture

Report Manager have other key components like:

• FSG Template Editor
• Repository Management
• Security Workbench : For both Flex field value as well as user to value security
  • Flex field Value Security
  • Users to Value Security

What can you see from above diagram is reports are published to a file system on the middle tier. Therefore you can choose to make your report available from the personal home page at the same time as you publish the report, or alternatively you can create a form function at a later stage to make the report accessible to users.

You can also apply some control if required When publishing reports by:

• Use Expansion Values and Time frames to group reports together
• Use Security to control which users can see a Published Report
• Select a date and time to make your published Report available for viewing
• Select Reviewers for the Report

Read the rest of this entry »

Nine months since the launch of SEPA, Oracle has finally released patch for SEPA Credit Transfer (SCT) for much demanded need for European EBS customer.

High Points of SEPA Project

Lets recap some of high points of SEPA project.

• Objective: Harmonize electronic payment schemes according ISO 20022 rules.
• Countries: Euro zone countries
• Transactions: Only Euro transactions.
• Period: From January 1, 2008.The goal of EPC is to make SEPA mandatory from 2011

SEPA support at Oracle

• Oracle plan is support in major product(EBS,Fusion, Enterprise) lines
• Oracle plan is to support only two types of SEPA messages in most of these products:
  • Customer Credit Transfer Initiation
  • Customer Direct Debit Initiation
    • For Availability of these functionality , check out documentation of respective products.

SEPA@ EBS

Oracle EBS functionality for SEPA messages customer credit transfer initiation (pain.001.001.02) is based on the ISO20022 format. ISO20022 format have these benefits;

• Batch booking
• Grouping : This reduce the number of transmitted files
• Remittance message
• End to end id . This will ease reconciliation and referencing

SEPA messages with Oracle EBS Release dates for 11.5.10 and 12

These are summary of different SWIFT message.

Fig 1: SEPA swift Message

Out of these list Oracle EBS supports for both Customer Credit Transfer(pain.001.001.02 ) as well as Customer Direct Debit (pain.008.001.01)

Here are the summary of information for SEPA out-of-box Functionality in EBS.

Fig 2: Oracle Support for SEPA

SEPA availability in EBS

The SEPA Credit Transfer Initiation Message is available for R11.5.10 via Patch 6741280.Customer using 11.5.10 can get the functionality enable by applying patch.

Wrapping up

• The SEPA out-of-box functionality will be released as patches
• Jan 2008 – Jan 2010 is a official transition period
• Extensive test is required with bank when you are utilizing this functionality.

Will keep you posted for any updates.:)

Similar Post

• THE SINGLE EURO PAYMENTS AREA (SEPA)
• A bit on SEPA - IBAN & BIC

Oracle white paper

• Note:738361.1 :Supporting SEPA Credit Transfer in Oracle Payables for Oracle Applications Release 11.5.10

This year in OOW 2008, Oracle CEO Larry Ellison announced a new storage server product, Exadata, a hardware/software solution for offloading I/O processing to proprietary storage nodes. This is first time when Oracle moved in hardware side.Exadata is joint effort by Oracle and HP .

Oracle has put up in 22 page paper. The newly Exadata performs joins between large tables and small lookup tables, a very common scenario for data warehouses with star schemas. Read details

Take a note , current release is limited for data warehouse.

• Oracle's Exadata white paper
• Exadata Website

More on Oracle Exadata Architecture

• Oracle’s New Exadata Architecture

SEPA in system context have things which is really important.

1. IBAN Code
2. BIC Validation
3. SEPA customer credit transfer, based on the ISO20022 format (XML)

So lets see why IBAN and BIC is so important.Take a note, SEPA schemes use BIC and IBAN codes to uniquely identify bank accounts

IBAN (International Bank Account Number)

The IBAN is provided by the bank servicing the account.

It should not contain any spaces when it is stored electronically. When printed on paper, however, it is usually expressed in groups of four characters, the last group being of variable length.

It consists of:

• The ISO code (two letters) of the country in which the bank account is held and a two-digit check number;
• The domestic Basic Bank Account Number (BBAN) (up to 30 alphanumeric characters).

Total IBAN length may not exceed 34 characters.

BIC (Bank Identifier Code)

The BIC is the unique identifier for banks within the European Union.The BIC is regulated by ISO 9362

It consists of 8 or 11 characters, which include:

• A bank code (four characters)
• A country code (two characters)
• A location code (two characters)
• if applicable, a branch code (three characters)

Similar Post

• THE SINGLE EURO PAYMENTS AREA (SEPA)
• A bit on SEPA - IBAN & BIC

Banks and their corporate customers are about to enter an era of profound global business change.

In January 2008, foundations were laid for a Single Euro Payments Area (SEPA) – a process that aims to deliver harmony in all financial transactions across Europe, and create the conditions for much greater standardization in the movement of money across the world. With this note, lets take a insight view of SEPA.

“The Single Euro Payments Area (SEPA) will be the area where citizens, companies and other economic actors will be able to make and receive payments in Euros, within Europe, whether between or within national boundaries under the same basic conditions, rights and obligations, regardless of their location.”

What is all the fuss about SEPA?

As its well known, Europe has had a common currency and monetary policy since the Euro was introduced in 1999 and Since 1 January 2002, consumers have been making cash Euro payments everywhere in the Eurozone.However, non-cash payments between countries - mainly credit transfers, and credit and debit cards have been expensive and complicated to transact, and a barrier to European monetary integration. There is currently no robust mechanism for a direct debit
between countries.

Therefore, The Single Euro Payments Area (SEPA) project is designed to eliminate national differences in payment instruments and processing infrastructures in Europe,and harmonies fees for cross-border and domestic Euro transactions.

SEPA Journey

It all started in the year 2002 as an initiative to transform European banking Infrastructure by creation of a European Payments Council (EPC). The purpose of such initiative was to define new rules and frameworks and simplify cross border complex payments process across Europe. This project aimed at achieving harmony between national and international payments in non-cash mode. European banking industry used to face severe losses in the process of cross border transactions. This led the banks to create the EPC.

SEPA aims at providing a common platform for all the associated members to make and receive payments in a single currency (i.e. euro) within Europe irrespective of their base location. SEPA project involves key members like European Commission (EC) and European Central Bank (ECB) supported by the European Payment Council (EPC).

About European Payments Council (EPC)

It is a European governing body, which consists of more than 50 banking institutions formed to standardize the payments mechanisms in Europe. It is the decision-making and coordinating body, which is responsible for issues related to payment systems in the European Banking industry.

Mission SEPA

• It aims to provide a single area for all payments across Europe.
• Single Account, single card mechanisms irrespective of country

SEPA Project

As per website note, SEPA implementation schedule was split into three (overlapping) phases from a banking perspective:

• Design Phase (January 2004 -June 2006)
  • Establishment of the rules, practices and standards for new payment instruments.
• Implementation Phase (June 2006 –November 2009)
  • The banks design and test new product offerings under SEPA
  • Participating countries have to set up national implementation and migration bodies to ensure smooth roll out of the SEPA infrastructure
  • Launched in January 2008:
  • SCT (SEPA Credit Transfer)
  • SCF (SEPA Cards Framework)
  • Scheduled for launch at end of 2009:
  • SDD (SEPA Direct Debit).
• Migration Phase (January 2008 – December 2010)
  • The cross-over period between the old and new instruments, ensuring a gradual market-driven migration to SEPA
  • This phase includes set-up of PEACHs (Pan-European Automated Clearing House).

Therefore this third phase is more meaningful to corporate who are running there accounting system in ERP and they must be make sure they should not impacted by product limitation and avoid penalties as there is Europe wide mandatory requirement for non-compliance. These penalties would be leveled for each individual payment, which would be substantial across 16 countries.

SEPA Consist of:

• The single currency
• A single set of euro payment instruments – credit transfers, direct debits and card payments
• Efficient processing infrastructures for euro payments
• Common technical standards & business practices
• A harmonized legal basis
• Ongoing development of new customer-oriented services

SEPA coverage area

As SEPA will be introduced on a phased basis. Thirty-one countries are currently committed to SEPA: the 27 EU member states, the three other European Economic Area countries (Norway, Iceland and Liechtenstein) and Switzerland.[source Website]

Currently only 16 counties under coverage area.

• Austria
• Belgium
• Germany
• Spain
• Finland
• France
• Greece
• Ireland
• Italy
• Luxembourg
• Netherlands
• Poland
• Portugal
• Sweden
• Slovenia
• United Kingdom

What are the different payment Instrument that cover in SEPA

There are four types of payment Instrument

1. SEPA Credit Transfer
2. SEPA Direct Debit
3. SEPA Card
4. SEPA Cash

Standardization -Addressing the SEPA schemes Requirements

The SEPA payment platform supports different schemes for both credit transfers and direct debits.

New schemes have been developed for credit transfers and direct debits that describe the technical and functional protocol for exchanging credit transfers and direct debits between two banks using formats and procedures. The formats are based on the ISO 20022 XML standard developed by SWIFT.

SEPA requires common information (‘data elements’) for SCTs and SDDs, outlined in the SEPA Credit Transfer Rulebook and SEPA Direct Debit Scheme Rulebook respectively.

SEPA within ERP

SEPA will make it possible for ERP systems to be better integrated and to communicate better with the bank.

SEPA@Oracle

SEPA support is required in Oracle in these products;

• EBS 11.5.10, 12, 12+
• Fusion

Out of these EBS support started a few days back, which will be discussed in other post.

Possible changes with SEPA

• Euro BankAccounts :
• Cash Management Structures
• Banking Interfaces

Further References

• SEPA Vision
• Making SEPA a reality
• Roadmap towards SEPA