All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Thursday, February 19, 2015

OBIA Financial analytic AP Process 101

Today we are looking at the basic process of AP or Accounts Payable in Oracle EBS for financial analytic. This is to give you a basic understanding of how AP process works for most companies that implements Oracle EBS. In order to build AP reports out of EBS source, you need to understand the basics.

Although different companies will have different accounting process and different product dealings, the basic AP process is comparable.

Based on the below diagram, the AP process includes 4 main blocks:

Purchase Orders:

These are the purchase orders that the company has decided to execute through Oracle IProcurement. It basically means that the company has decided to buy, this can include items for the office, materials that the company products need, the contract for consultants, laptops, speakers, insurance services or electricity and gas.

PO usually consist of 3 hierarchies:
Header - highest level.
Line - under each header, there can be multiple lines.
Distribution - Under each line ordered, it can be used for different locations or cost centers. Manage A from this department uses it for a few weeks and then Manager B from another department uses it. From accounting perspective, it may belong to different cost centers

This is when the invoices of these PO have been entered. They have mirror the hierarchical structure as Purchase order. Each of the 3 AP Invoice tables can join to the corresponding PO tables.

Now that the company has been invoiced by vendors, they will receive goods and services that they purchase. The act of receiving is recorded in RCV_TRANSACTIONS table as transactions. Note that receives are at invoice line level. If your company makes a lot of order from one vendor and the goods will be delivered throughout a period of time continuously, you may want to check the Receipt_Date and take the Max of this date for each transaction, or it could potentially influence the grain of each invoices. As an example, most of the time you are interested in seeing how much you have totally spent in your trip to the local grocery store but not necessarily interested at each items you purchase, or at the end of a period, how many chairs does your company receive rather than how many chairs received on each day.

Hold indicates the status of your company's decision on handling the invoices. Let's say after receiving the invoices, the department realized that it doesn't have all of the goods that they thought they ordered. This could be categorized into 2 main reasons:

1. The company makes a mistake in dealing with the transactions. Maybe the goods are delivered to the wrong department or miscounted. The error lies within the company, therefore they are still responsible for making the payment on time according the to invoice.

2. The company thinks that the vendor has not completely delivered their services according to the PO. The order says 100 laptops but the company only receives 95, therefore the company puts this invoice on hold and waiting for the delivery of the remaining 5.

These information will be stored in AP_INVOICE_HOLD_DETAIL table and HOLD_TYPE will indicate what type of hold this invoice is. For each Invoice_ID, there can be multiple HOLD_LOOKUP_CODE to tell you the specific reasons for being on hold.

Now if the hold is deemed to be vendor not fulfilling its responsibility, the HOLD_RELEASE_DATE column will be used for the invoice payment due date, that is, until the error has been corrected, the hold will be released.

The payment_date and due_date are stored in various tables, but AP_PAYMENTS_ALL, which is the block at the bottom of the 4 blocks in the diagram, generally stores this information.

So for accounting purposes, the payment date and due date are based on HOLD conditions.

The process of determining what HOLD condition is, or whether there should be a hold or not, the called 3-way match, which means you have to compare the PO to invoice to the receipts to see if they matches or not.

If there is reporting requirements that needs OBIEE report that does these kind of comparisons, you should already know the logic and the type of report that will make sense.

Thank you

Until next time


keylabs Training said...

Amazing post thanks for sharing OBIEE

Anonymous said...

Thanks for all you posts.
Can you please write for SSO from EBSR12 to BI Publisher 11g.

Anonymous said...

Thanks for all your efforts..
can you please write on SSO from EBSR12 to BI Publisher 11g

Related Posts Plugin for WordPress, Blogger...