One quick question from non Oracle user ..

Do we have any functionality in Oracle for handling “Alternative Payee” in payable ?

My answer is NO , at least I have never seen any seeded out-of-box, though Customer asked many times..

Does anyone have any answer from Oracle side?

In the real business environment, there are many cases where the vendor name used for procurement purposes may be different than the name that the vendor must be paid to. Instead of using the vendor name as the payee name, it required to have an alternate field where we can state the payee name.

The good is that SAP does has capability to handle such functionality as they maintain an alternative payee at the supplier master level and wherever you need to pick either of them just pass the information and print the name of Check. So simple…That’s why user coming from SAP product really hates Oracle, and complaining why Oracle does not have such small things, but they don’t have choice.

Minor Customization or work around is not always a choice for SME’s as cost be major driving for the implementation.No comment on this..

I tried to explore by asking to core user, how they are achieving this in SAP. They simply run through few steps and demonstrated how they achive this. Within SAP this consist of few steps as:

• Created a vendor master record for the alternative payee. Block this account against posting.
• Specify the account number of the alternative payee in the Alternative payee field within the payment transactions section of the vendor master record.
• While making payments for this vendor, the payment program access the name and address of the alternative payee.Cool..

Oracle Approach

Some things called “Factoring Arrangements”

What is Factoring Arrangements ?

Exploring in accounting books and google , A factoring arrangement is basically a purchasing agreement under which a person or entity such as a big Corporation acquires outstanding debts, invoice or account receivable at a discount from another entity, usually a company.This was very common in US specially in textile industry. [Adopted]

Let see how Oracle trying to handle this in payable:

When the check is going to a factor or for a garnishment you can create a pay site and indicate the payee’s name on the first address line starting with “OR Factor”.

This is what 11i/R12 payable/Payment documentation says :

If a vendor A sells its receivables to a factor (ie third part for your Oracle), you can enter invoices for the supplier A and make payments to the factor by creating a pay site defined for the factor. On the first address line enter the name of the factor preceded by the word “and” or “or.” On the remaining two address lines, enter the address of the factor. Payables then addresses the payment to the supplier and/or the factor, since Payables prints the vendor name and the three address lines on each payment.

How Oracle explain this case

Supplier ABC has sold their receivables to Factor B, and you want to send payments directly to Factor B.

Case (i) You want your payment document to be made out to “ABC or Factor B.”
Case (ii) You want your payment document to be made out to”ABC AND Factor B.”

Define a pay site for supplier ABC called Factor B.
On the first address line enter “OR Factor B.” for case (i)
On the first address line enter ” AND Factor B.” for case (ii)

On the remaining two address lines enter Factor B’s address.

If you select the Factor B supplier site when you enter and pay an invoice for ABC, the first two lines of the payment will read “Payable to: ABC OR Factor B” if setup is as case (i) or “Payable to: ABC AND Factor B.” if setup is case (ii).

And this way the factor can endorse and cash the payment.

I tried and tested , but this does not serve the purpose.Any one have some better idea , Please …

Don’t tell customization , I have 2-3 options ready to fit but customer want $0 solution.

Finally

Is this a totally different things not any work around with alternate payee.

Lets hope, Oracle payment is new family in R12 might,consider this an enhancement request to cater the need for those user who is moving from SAP to Oracle.

Any thoughts welcome :)

Disclaimer : SAP Functionality which is discussed is based out of Personal observation and discussion with SAP user, which they are using bit older version.Any Conflicts with SAP functionlity must be validated. Oracle Version which is used for reference is 11.5.10.2/R12.

Yes, that true, this is one of the functionality that Payable managers are expecting in EBS. The Oracle payable still requires enhancement in few area where this is one called is ‘Stale Cheque management. This is very common trends seen by APAC entities , forced to do customization to automate this process.

What is Stale cheque ..

When payment cheques remain unpresented after certain period of drawing, the cheque has become stale and thereby should not be honoured by the bank. All entries made to the general ledger in respect of the stale cheque will need to be reversed by completing the necessary vouchers.

Is there any stipulated time when is a cheque considered stale-dated, and is it no longer valid after that point?

Yes, Checked with Financial controller and found as per CPA Rule A4, Section 21, a cheque is considered stale-dated after six months, unless it has been certified . Although it may be returned through the clearing for that reason, there is no obligation to do so, and the payers financial institution may still accept it as a valid payment item. An institution accepting a cheque that is dated more than six months earlier may choose to contact the payor’s financial institution to confirm whether the cheque will still be accepted.

What is the difference between cancelling or Voiding a payment and reversing it?

Payments should only be cancelled or voided if the goods or services have not been supplied or where the cheque is to be re issued due to some reason.

Where the goods and services have been received but the payment has not been processed within 6 months of issue, this becomes a Stale cheque. Stale cheques are reversed by receipting the same amount back into the system.

Why this is Need in Oracle ?

In the new world most of entity payment is processed by there banking partner, therefore important aspect for entity in term of managing this is bit challenging for.

1. Reconciliation AP with CM bank statement?
2. Internal Control which is subject to query by auditors?

As mention there is no automated way to manage such process, still we might can club with these manual intervention which consist of :

1. To Void the payment and choose No Action on the invoice if its to be paid again.
2. Place an appropriate hold on invoice if investigation is required, or cancel invoice from void screen.

Does Oracle Recommend a process for handling stale checks?

No not yet , as of latest 12.0.4.

The only information that Oracle has for there EBS customers is based out of metalink note:147077.1,Oracle does not have a recommended process for handling stale checks. Each business should define how they would handle this process.

One suggestion is to print on the Check that they are only valid for so many days and then to void the check after that time period if they have not come in.

There was something called “Escheating Checks” in Earlier Releases..

Don’t get confused with this functionality with stale checks. This is what Oracle documentation mention in section 5.86;

In the U.S.A., each state has different rules regarding checks that have been issued by which have not cleared the bank. In most cases, the organization that issued the check is required to remit the funds (escheat) to a state tax authority. You can use the following method to create payments for the tax authority.

Prerequisite

Complete the due diligence required by the state. To pay escheated checks to the state tax authority:

1. For each supplier for which you will escheat payments, create a new supplier site. Use the tax authority’s name and address for the pay site.
2. Void each payment. You may want to add a note in the description of the invoice that it was escheated to the relevant tax authority. Change the invoice pay site to the new tax authority site. Optionally assign a unique Payment Priority or Pay Group. This will help allow you to create a Payment Batch for just the escheated checks.
3. Pay the invoices and provide the necessary documentation to the tax authority.

So now, it is time to comment, share your thoughts .should this functionality is real need or customer should still manage by customization…

Do you know :

• Closing the accounting period within a specific timeframe for any business is Important
• Sometime failing to close the accounting period due to accounting exceptions will delay the overall fiscal close process

Lets take a case, how many of us aware of these problem reported during month end or during normal daily activity by account department . Lets list some of the common problem them from payables:

• Unable to Close Period
• Accounting Fatal Error
• Accounting Invalid Account Error
• Unable to account invoice/payment
• Journal Import Errors
• Duplicate Accounting Entry Created For a Payment
• Paid Invoices Appearing on the Trial Balance
• Orphan Accounting Data
• Can’t Confirm a payment batch
• Invoice not selected for payment
• Invoice does not have account
• Balance AP to GL
• Final Closing
• Can’t Confirm a payment batch
• Preliminary closing
• Invalid account
• Deadlock in Payables Accounting Process ORA-00060: deadlock detected while waiting for resource occurred in …AP_ACCOUNTING_MAIN_PKG.Create_Accounting_Entry
• Accounting Exceptions Sweeping

What next …finding notes on metalink or raise a SR with Oracle,with the assumption they can fix this. Did you ever realized how many hours or days it takes in resolution. Therefore very similar to FirstAid in our daily life during emergency, why not utilize Oracle Diagnostics.

Payables currently has 15 diagnostics as part of Oracle Diagnostic based out of all common issues faced in earlier releases.

About Oracle Diagnostic

Lets recap , based out of one earlier post regarding Oracle Support Diagnostics which designed to improve:

•Resolving configuration and data issues that would cause processes to fail
•Resolving problems without the need to contact Oracle Support
•Reduction in Resolution time by minimizing the time spent to resolve an issue

Typically Oracle Diagnostic consist of

• Diagnostic tests : This provided as part of the Oracle Support Diagnostics patch gather and analyze information from your E-Business Suite
• Results : This display the information gathered, the findings of the analysis and appropriate actions to take if necessary
• Tests : The good is that these do not alter data or setup.
• Purpose : These have clear purpose for automation that ensure after successful installation

What are the Oracle Diagnostics Available in AP

Very similar to other products, Payable does have three major Diagnostics

1. Setup Diagnostics
2. Activity Diagnostics
3. Data Collection Diagnostics

With in Setup Diagnostics of AP , these areas are covered:

• 1099 -This test normally used to verify and display 1099 setup details in AP.
• General -This test will verify and display basic setup details as outlined in the ‘Setting Up’ chapter of the Payables User Guide. If you are doing setup configuration, you try to take this test.
• Internet Expense User Setup -If you are doing iexpense setup , then running this test will validate and display the Oracle Internet Expense setup for an employee and corresponding underline details.

With in Activity Diagnostics of AP , these areas are covered:

• 1099 Missing Invoice -This test will verify why a particular invoice was not picked up in a 1099 report.
• Accounting -This test will diagnose why an invoice, payment or payment clearing transaction will not account . This will also find some other conditions like dependent events.
• Checkrun Selection -This test will assist in identifying why an invoice is not being selected in a checkrun
• Data Integrity Analysis -This test provides a series of tests to check the data integrity of all supplier invoices and checks.
• Internet Expense Report Status -This test will display details and identify problems for a specific expense report
• Mass Additions -This test verifies if a given invoice or invoice distribution line meets the conditions to be transferred to Oracle Assets when running Mass Additions Create.
• Payment Batch Status -This test will validate a payment batch .
• Payment Document -This test will validate the setup for a specific payment document
• Period Closing -This test will help in determining if a period can be closed and if not, why it cannot be closed.
• Trial Balance -This test will identify and report common problems for invoice(s) appearing or not appearing on the trial balance

With in Data Collection Diagnostics of AP , these key areas are covered:

• For Invoice-This diagnostic test will display ALL of the data for ALL of the tables used to process an invoice in Payables.
• Open Interface -This test is designed to display all related interface data for a specified invoice interfaced from Oracle source applications or a 3rd-party (legacy) source.

Most important,the reports which appears after running the scripts also have the required patch set information. These diagnostics were created from manual/standalone SQL scripts that Oracle normally providing during data fixing and data collection process against a particular SR. Indeed really good collaborative approach.

Best Practices

• To prevent issues always check status of periods in sub ledgers
• Clear all the exceptions
• Get use of Period end Exception Report - If user want some additional column enhance the reports.

Period Close Exception Report

This report will shows ALL exceptions that would prevent a period from being closed:

• Unaccounted Transactions
• Untransferred Journal Entries
• Unconfirmed Payment Batches
• Unmatured Future Dated Payments

You will notice this report got enhanced ( in 11.5.10/Family Pack F). The new improved Payables Accounting Process report covers the error message section. It has also added section to the end of the Payables Accounting Process Report output to show what transactions could not be accounted by the process. And included in that section a comment to see the corresponding transaction number in the log file to see why the accounting could not be created

Therefore.. Lets the end user/core user know how and when to use these tools to address such of problem on daily basis or during closure time and make a better experience with EBS.

Similar Post

• Diagnostics Made Easy : Oracle Diagnostic Tool
• Diagnostics Made Easy : Oracle Diagnostic Tool -II
• Get use of “Oracle Diagnostics” Available with Products

Background

Once the entries have been made and before any report are extracted from the accounts, a trail balance is drawn up.This is standard accounting practice. This is just what it says it is - a try at balancing the figure before the profit is worked out and the balance sheet drawn up. By rule of thumb,If the double entry is correct, ie all debit entry for every credit entry and viceversa, then the total of debit balance and total of the credit balances equal .

In release 12:

o 11i Trial Balance is known as the Open Account Balances Listing report in R12
o It runs based on the Open Account Balances Listing Definition that you define.
o You can define the Listing Definition at a Ledger or Ledger Set level

It runs based on the Open Account Balances Listing Definition that you define then select as a parameter when you submit the Open Account Balances Listing report.

As a prerequisite, you must run the Open Account Balances Data Manager and select the Open Account Balances Listing Definition in the parameter labeled “Trial Balance Definition”.

Now , you can define the Listing Definition at a Ledger or Ledger Set level such that when you run the report, you can view open liability balances for operating units belonging to a given ledger or ledgers within a ledger set as shown below:

Similar Post

• Welcome to R12 Account Payable
• Month End Process -Useful Payable Reports
• Month End Process for Account Payable
• R12 : AP Trail Balance

Good news for those customer who are using or planning to use MasterCard Corporate cards , as they have upgraded online reporting service with the launch of MasterCard smartdata.gen2 some time back. This provides greater flexibility and the ability to exporting the expense data that can be used as to feed into company ERP system with i-Expense system. This is yet another options in place of Amex Corporate Card integration with ERP. Important to know, more and more company are now a days finding the way to integrate Corporate Card with iexpense.

High Points of benefits

• Transforms data into your BI : This provides powerful integrated reporting views and insights, leveraging transactional and enhanced data from hotel folios, airline, car rental, rail and other supplemental data procured from travel agencies.
• Provides a holistic view to global data — virtually anytime, anywhere through a secure web-based interface. Single sign-on capabilities enable easy access to the reporting solution through issuers’ websites.
• Multiple languages reporting :Available in 14 languages US and International English, Spanish, Castellano Spanish, French, Canadian French, German, Italian, Japanese, Dutch, Korean, Portuguese, Chinese, and Simplified Chinese.
• Multiple Currency :Supports transaction in virtually every currency in world circulation
• Provides spend data detail : transactions and line item details can be automatically viewed resulting in deeper spend insights and the ability to better analyze data for vendor negotiations and pre-population of employee expense reports.
• Streamlines the accounting process :accommodates multiple accounting systems and schemes resulting in powerful accounting capabilities that solve simple to complex requirements.
• Customizable expense monitoring : spend alerts, specific data analysis and filtering, and tailored reports based on specific parameters can be easily customized to monitor spend and help ensure compliance.
• Exporting Capability : This can be exported to software programs such as Microsoft Excel(R) which then after can be feed into ERP systems.

Suggested Reading

• Smart Data Solutions
• MasterCard Takes Reporting and Expense Management to the Next Level with MasterCard smartdata.gen2

Similar Post

• Credit Card functionality within i-expense

Here are some of the key accounts used in P2P(Procure to Pay) Cycle.

RECEIVING INVENTORY ACCOUNT

This is one of the clearing account.

The account is used for receipt accruals.

After receiving transactions are processed and the Transfer Transactions to GL process is run, the Receiving Inventory Account is cleared and the Material account is charged with the cost of the capitalized inventory .

You can specify this account when you define Receiving Information for your inventory organizations.

INVENTORY AP ACCRUAL

This is the account used by Purchasing to accrue your payable liabilities when you receive items you will capitalize as inventory.

This account represents your uninvoiced receipt liability and is usually part of your Accounts Payable Liabilities in the balance sheet.

Oracle payables relieves this account when the invoice is matched and validated.

So, you have to specify this account when you define Inventory Information for your inventory organizations in the Other Accounts tab.

AP LIABILITY ACCOUNT

This account defaults from the supplier site and is credited when a standard invoice is entered or debited when a credit memo or debit memo is entered. The account is relieved when the invoice is paid.

When you are creating an invoice, the liability account will get defaulted based on the hierarchy i.e., it can be from Financial options/Supplier /Supplier site.However you will still have an option to modify the Liability account by replacing the defaulted account.

(0r) do you see a different account getting defaulted during Invoice creation

EXPENSE AP ACCRUAL

This is the account used by Purchasing to accrue your payable liabilities when you receive items you will expense. This account represents your uninvoiced receipt liability when you run the Receipt Accruals - Period End process.

When you receive the goods,the accounting entry will be
Receiving Inv Dr
To Expense Accrual Cr

In Payables

Expense Accrual Dr
To Liability Cr.

So the Expense Accrual will knock-off

You can specify this account on the Accrual tab when you set up Purchasing Options.

MATERIAL ACCOUNT

An asset account is used for to tracks material cost .

In the average costing, this account holds your inventory and in transit values. Once you perform transactions, you cannot change this account.

You can specify this account when you define Inventory Information for your inventory organizations in the Valuation Accounts region for the Costing Information tab.

CHARGE ACCOUNT

This is the charge account is the account that will be charged for the purchase on either the balance sheet or income statement.

• If the destination type for the distribution is Inventory, this account will be the Material account associated with the subinventory and you cannot override it. This is the balance sheet account that will be charged after inventory is capitalized.
• If the destination type is expense, you can specify this account (provided it isn’t project related) and override any defaults. This account will be either an asset clearing account that will be included on the balance sheet or an expense account that will be included on the income statement. This account is either created or specified when you create a purchase order.

Look at the Material Account on the destination inventory organization, or (if specified), destination subinventory.
Under Inventory: Setup/Organizations/Parameters or Sub inventories

PURCHASE PRICE VARIANCE

This account is used to record differences between purchase order line price and standard cost.

The Purchase Price Variance is calculated when items delivered to inventory are costed.

You should note, this account is not used with the average cost method.

For example, assume the purchase order line price for an item was set at $100 per item but standard cost was set to $120 per item and you purchased 10 items. The Purchase Price Variance would be $200.

You can specify the Purchase Price Variance account when you define Inventory Information for your inventory organizations in the Other Accounts tab.

INVOICE PRICE VARIANCE

The variance account used to record differences between purchase order price and invoice price.

This account is used by Payables to record the invoice price variance for inventory items.

For expense items, the account generator uses the charge account to record any invoice price variance.

You can understand with this set of example, how its works;

1). Create a purchase order with expense type item having the above
Navigation: Purchasing->Purchase Orders->Purchase Orders

2). Receive the goods for this PO.
Navigation: Purchasing->Receiving->Receipts

3). Login as Payables manager, create an invoice and match it to the PO created in step 1.
Navigation:Payables->Invoices->Entry-Invoices

for example

PO Quantity=100
PO Price = 5

Now you match an invoice to the PO and Invoice(match) details are as follows:
Matched Quantity=100
Price on Invoice= 1

in this case, probably you have set the Invoice price variance account in define Organization Parameters form( alternate region: other accounts) same as the expense account on the PO

Invoice Price Variance= (PO Price - Invoice Price) x Qty. Invoiced

You can specify this account when you define Inventory Information for your inventory organizations in the Other Accounts tab.

Similar Post

• Quick notes : ‘Procure 2 Pay’ Cycle
• Procure to Pay (P2P) - Accounting Entries
• Accrual Accounting (Periodic Accrual)

In response of my last post ,yet another reader asked for “Asset ,Purchasing & Inventory Purchasing and there corresponding accounting entry within P2P cycle. Therefore this post highlights some of key accounting entry in each steps with respect to th

As you know “procure to pay” Business Flow start Purchasing requisition till paying to vendors and most important, in all the case the purchase is made for basic element called Items.

As you know there are three types of items:

• Inventory Expense Item
• Inventory Asset Item
• Expense item

Definition of above Items used in Purchasing can be best understood as:

Asset flag means means it is an asset and the items value will show in your inventory valuation.

Inventory Item

Expense Item

These are one which is used for consumable items purchase for your organization. More importantly , for creating an expense item you have to perform following setup doing in the Master Item form.Go to same path in oracle inventory

Oracle Inventory -> Items -> Master Items

When master items form open Go to Inventory Menu you need to tick followings

1. Inventory item
2. Stock able
3. Transactble
4. Resolvable

And you can also setup in Costing and purchasing menu account code as per your requirement.

Asset Item

As discussed above , the following attributes need to be enabled for such an item.

• Inventory item
• Stock able
• transact able
• Costing flag
• Inventory asset value

For entering on purchase orders
It should have purchased and purchasable flags enabled and you have to make sure you are assigning this item to the Purchasing org which you have defined at

Oracle Purchasing > Setup > Organizations >
Financial Options > ‘Supplier-Purchasing’ alternate region ‘Inventory Organization’ field.

The accounting can be best described for such kind of items is;

Is there any effect on Step 5 in all three cases, that mean do matching have different accounting entry?

The answer is no; as per my understanding purpose of setting the PO to a 2way, 3 way or 4 way match is to ensure that the corresponding hold is generated on the invoice.

The holds are basically designed for control purposes, they do not have any accounting effects.

Additional Reading

• Accrual Accounting (Periodic Accrual)

Some time back one of my reader asked to provide accounting details information that take place in accrual accounting, so here to go:

Lets try to understand the accounting details with these entry:

• PO Cost SGD 200
• Invoice Price SGD 200
• Payment Rate SGD 200

Enter purchase order

When you enter a purchase order, accounts are created and stored with the purchase order distribution. The accounts will eventually be used as a basis for creating accounting that is sent to the general ledger. Creating a purchase order in and of itself generates no accounting that is sent to the general ledger.

Receive

When you process a receipt, no accounting is created for period end accruals. Receipts that are accrued at period end will always be for a destination type of expense.

Deliver and cost

When you deliver a receipt to its final destination, no accounting is created. The expense will be recorded after matching to the purchase order, running the Payables Accounting process and subsequently running the Payables Transfer to General Ledger process.

Period end accrual

If an invoice is not entered by period end, the Receipt Accruals - Period End process will generate accruals and transfer the accounting for them to the GL Interface. Use the Journal Import program to create unposted journals. This journal is created with a reversal date in a subsequent period. The journal must be reversed so your receipt liability is not overstated.

Reverse accrual in the general ledger

In the subsequent period, reverse the prior period accrual.

Invoice and match

Entering an invoice and matching creates a debit to the Inventory AP Accrual account to clear the liability for the uninvoiced receipt (you now have an invoice).

The entire credit is to the AP Liability account that defaults from the supplier site if the invoice unit price is the same as the purchase order line unit price. Any difference is charged to the Invoice Price Variance account.

For items with destination type of Expense, the Invoice Price Variance account will be the same as the charge account. The AP Liability account is cleared when a payment is processed.

Similar to O2C in last post , these things you should Know about Procure 2 Pay Cycle.

Oracle Purchasing

• You enter purchasing documents by operating unit. These include standard and blanket agreement purchase orders, requisitions, RFQ’s and quotes. You assign a PO shipment to an inventory organization, any inventory organization in the same set of books as the PO’s operating unit.
• You enter receipts by inventory organization.

Account Payable

• You setup one default liability account by operating unit.
• You enter invoices in one operating unit at a time.
• You run payment runs in one operating unit at a time.
• You can consolidate supplier invoices on one payment only within an operating unit.
• You setup bank accounts and associated cash accounts within an operating unit.
• You select invoices for payment on one payment run for one bank account based on pay group, priority, amount,currency, or payment method (i.e. check, electronic) within an operating unit.

PO/AP:

• You setup suppliers for the entire database instance but addresses for each operating unit.
• You merge suppliers and addresses within an operating unit.
• You report supplier/customer netting within an operating unit.

In Finance, transaction management processing is one of labor intensive task in ERP, as it requires extensive data entry , chance are very very high for duplication/re-entry. As we know Procure to Pay life cycle start itself from contract management till making payment.

As we know the efficient Procure to pay process have these sub processes;

• Contract Management
• Purchase Requisitions
• Purchase Orders
• Accounts Payable - Managing invoice
• Supplier Payment

In real business world, many time when system is running external/internal auditor are more interested in scrutiny of:

• Goods received / invoices received
• Inaccurate or duplicate vendor & material master records
• Discrepancies in payment terms
• Delays / long processing times
• Detect duplicate vendor
• Unusually large or small payments
• Unauthorized changes made to invoices
• Detect Duplicate invoice
• Detect Duplicate payment
• Approval status

Therefore, it is Inhouse ISD/Finance IT or implementing company responsibility is to provide such kind of adhoc reporting for auditor so that they can satisfy the audit requirement.

A ‘P2P’ query that made Auditors happy

It was brought by ISD team , as part year end audit for a ERP system which went live 3 month back. It was a one of requirement to display data for a particular PO which covers data from there all 5 five phases, means a particular PO line consist of:

1. Requisition Detail
2. Purchase Order Details
3. Receiving Details
4. Invoicing Detail
5. Payment Details

Therefore thought to share this query, hope this would be great help who have such kind of adhoc requirement from daily life.

Here is query:

SELECT
A.ORG_ID “ORG ID”,
E.VENDOR_NAME “VENDOR NAME”,
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) “VENDOR TYPE”,
F.VENDOR_SITE_CODE “VENDOR SITE”,
F.ADDRESS_LINE1 “ADDRESS”,
F.CITY “CITY”,
F.COUNTRY “COUNTRY”,
TO_CHAR(TRUNC(D.CREATION_DATE)) “PO DATE”,
D.SEGMENT1 “PO NUMBER”,
D.TYPE_LOOKUP_CODE “PO TYPE”,
C.QUANTITY_ORDERED “QTY ORDERED”,
C.QUANTITY_CANCELLED “QTY CANCALLED”,
G.ITEM_DESCRIPTION “ITEM DESCRIPTION”,
G.UNIT_PRICE “UNIT PRICE”,
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) “PO Line Amount”,
(SELECT
DECODE(PH.APPROVED_FLAG, ‘Y’, ‘Approved’)
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) “PO STATUS”,
A.INVOICE_TYPE_LOOKUP_CODE “INVOICE TYPE”,
A.INVOICE_AMOUNT “INVOICE AMOUNT”,
TO_CHAR(TRUNC(A.INVOICE_DATE)) “INVOICE DATE”,
A.INVOICE_NUM “INVOICE NUMBER”,
(SELECT
DECODE(X.MATCH_STATUS_FLAG, ‘A’, ‘Approved’)
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)”Invoice Approved?”,
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER “CHEQUE NUMBER”,
TO_CHAR(TRUNC(I.CHECK_DATE)) “PAYMENT DATE”
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = ‘Y’
AND D.TYPE_LOOKUP_CODE != ‘BLANKET’;

The important section which cover in the query output is as:

1. Information for Supplier

2.Purchase Order details

3. Receiving Items Details

4.Invoice Details

5.Payment Details

You download the query and details here.