Two Way, Three Way and Four Way PO Match in Oracle

      Procure to Pay

Two Way, Three Way and Four Way PO Match in Oracle

One of the fundamental controls in Payables is the PO match. This simply means that the invoice you enter in the application is matched to the Purchase Order. If the match is not successful, the invoice is kept On-hold. On-Hold invoices cannot be paid. The hold has to be resolved before you can pay the Invoice. The PO Match functionality ensures you only record and pay invoices that are eligible for payment. It is a very simple & powerful feature that prevents your organization from paying incorrect & fraudulent invoices.

Now that we understand why we perform a PO match, let’s look at the different methods of PO Match. Depending on your business requirement, you would perform a two-way, three-way or four-way matching.

• Two-Way Match: In a two way match, you compare two attributes from your invoice with the Purchase Order. The invoices is considered to be successfully matched (matched within tolerances) when
  • The Quantity Billed (on the Invoice) is less than or equal to the Quantity Ordered (on the Purchase order)
  • The Invoice Price is less than or equal to the Purchase order price.
• Three-Way Match: In a three-way match, in addition to the two match conditions mentioned above, you also check the quantity received. So the three conditions that are checked are
  • The Quantity Billed (on the Invoice) is less than or equal to the Quantity Ordered (on the Purchase order)
  • The Invoice Price is less than or equal to the Purchase order price.
  • The Quantity Billed (on the Invoice) is less than or equal to the Quantity Received against the Purchase Order
• Four-Way Match: In a four-way match, you add one more match condition to the three-way match. Not only do you check the quantity received, but you also check the quantity accepted. All the four conditions that are checked in the four-way match are
  • The Quantity Billed (on the Invoice) is less than or equal to the Quantity Ordered (on the Purchase order)
  • The Invoice Price is less than or equal to the Purchase order price.
  • The Quantity Billed (on the Invoice) is less than or equal to the Quantity Received against the Purchase Order
  • The Quantity Billed (on the Invoice) is less than or equal to the Quantity Accepted against the Purchase Order

In a tabular format, the match approval level options can be represented as follows

	Invoice	Purchase Order
Two-Way	Invoice Price	PO Price
	Invoice Quantity	Quantity Ordered
Three-Way	Invoice Price	PO Price
	Invoice Quantity	Quantity Ordered
		Quantity Received
Four-Way	Invoice Price	PO Price
	Invoice Quantity	Quantity Ordered
		Quantity Received
		Quantity Accepted

Most organizations opt for a three way match. This also ensures that the quantity is received in the system before an Invoice is created and also helps ensures that the Open PO information is up-to date.

The quantity and price tolerances set up in your system also plays a major role in determining if it is a match or if the invoice should be put on hold. Note that the match has to be within the tolerances. For example if the ordered quantity is 100 units, tolerance is 5% and if you invoice 103 units, the system will accept it as a match because it is within the tolerance level.

Where to setup the PO-Matching option?
You setup the match approval levels in the

• Purchasing Options
• Supplier Level
• Supplier Site Level
• Purchase Order Shipment level

Purchase Order shipment level takes precedence over Supplier Site Level which takes precedence over Supplier level and so on.

Revenue Recognition

Revenue Recognition principle is one of the important principles of Accrual Accounting. According to this principle, revenue must be recognized when

(1) They are realized or realizable and

(2) They are earned

Revenue is realized when products are exchanged for cash or claims to cash (Receivable).

Revenue is realizable when related assets received are readily convertible to cash or claims to cash.

Revenue is earned when the products are delivered or services are performed.

Recognizing the revenue means recording the amount as revenue in the financial statements.

Realization is the process of converting non-cash resources into cash.

In the Revenue Recognition principle, it does not matter when cash is received. (In Cash Basis Accounting, revenue is recognized when cash is received no matter when goods or services are sold).

For revenue to be recognized, both the above conditions must be met. In other words for revenue to be recognized, final delivery must be completed (of goods or services) and there has to be a payment assurance.

Let us have a look at the timing of Revenue Recognition

1) For sale of finished goods (Inventory Items), revenue is recognized at the date of sale (some interpret this as the date of shipping or the date of delivery)

2) For sale of services (e.g. support services), revenue is recognized when the services are performed (delivered)

3) For sale of Asset Items (other than inventory items like finished goods), revenue is recognized at the point of sale (i.e. when the customer is invoiced)

4) For revenue from other activities like rent for using company’s Fixed Assets, revenue is recognized as time passes or as assets are used.

Examples:

1) If a company invoices its customer for 100 units of item ‘A’, and ships (delivers) only 25 units, the company cannot recognize revenue for entire 100 items. It can only recognize revenue equivalent to the number of units delivered (Revenue is earned only when the products are delivered). Similarly, let’s say you pay $120 in advance to company ‘ABC’ for magazine subscription for one full year. The fact that company ‘ABC’ received money for one full year does not mean that they can record the entire amount as Revenue. In-fact the amount received in advance is a Liability to the company because they have to deliver magazines to their customer every month and if they fail to do so, they are liable to refund the amount received in advance. In this scenario, the company will recognize 1/12th of the entire amount every month as earned revenue after they deliver the magazine.

2) Company ‘ZXC’ signs a 3 year support contract with its client for a total amount of 3 million. This amount cannot be recorded as revenue unless the Company provides the support services to the client. Assuming the company is following a monthly calendar accounting period, the company will recognize 1/36th of the entire support contract deal amount every month. (Revenue is recognized when services are performed)

There are few exceptions to the timing of revenue recognition for sale of inventory items. Under normal scenario, revenue is recognizes at the point of sale, however if there are return policies, and if the company cannot reasonably estimate the amount of future returns, the revenue should be recognized only after the expiration of the return policy period.

Revenue Recognition Accounting:

If revenue is not recognized immediately, what is the accounting entry for the Sales Invoice? Let’s have a look

Let’s say, you invoice the Customer in Advance for the annual support contract of $12000. Since, you are invoicing the customer in Advance, you debit your Receivables. But then if you are not crediting the revenue right away, where do you account for the credit side of the accounting entry? You credit, what is called as Deferred Revenue (or Unearned Revenue). Deferred Revenue is actually a liability for the company. (The company is liable to provide the goods or services for which cash is received or will be received in advance). As and when the goods or services are delivered, the Deferred Revenue is reduced (debited) and revenue is recognized.

Accounting when the Invoice is created in Jan

Date	Accounting Class	Debit	Credit	Comments
1-Jan	Receivables	12000		The entire receivables is recognized in advance. How this receivable is collected will depend on the payment terms of the Invoice
1-Jan	Deferred Revenue		12000

End of Jan, Revenue is recognized for 1/12th of the entire amount, because the company has provided one month’s service to its client. To that effect, Deferred Revenue will be reduced and revenue will be recognized

Date	Accounting Class	Debit	Credit	Comments
31-Jan	Deferred Revenue	1000		Deferred Revenue reduced
31-Jan	Earned Revenue		1000	Earned Revenue amount for one month

End of Feb, another months revenue is recognized

Date	Accounting Class	Debit	Credit	Comments
28-Feb	Deferred Revenue	1000		Deferred Revenue reduced
28-Feb	Earned Revenue		1000	Earned Revenue amount for one month

The company will have similar accounting entry each month till Dec. At the end of Dec, the Deferred Revenue will be Zero and the entire amount will be reported as Revenue earned.

Oracle Alerts

Query to find Alerts:

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------

-- Query to find Custom Oracle Alert

-------------------------------------------------------------------------------

SELECT alr.application_id,

       alr.alert_id,

       alr.alert_name,

       alr.start_date_active,

       alr.description,

       alr.sql_statement_text

  FROM alr.alr_alerts alr

 WHERE 1=1

   AND alr.created_by <> 1      -- show only custom alerts

   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

About Alerts:
Alerts are basically used automate the system maintenance, provide reports in the format chosen, for database activity/Business Requirement as it occurs..

few scenarios:
- A database event which was supposed to be monitored for instance insert into some table(event based alert).
- A SQL statement which provides specific information (event based alert).
- The frequency we want the report of a SQL statement(periodic alert)

Responsibility Name: Alert Manager
Pre-requisites:

Navigation: Alert Manager --> System --> Options

-- Electronic Account Mail setup
-- Oracle Alert Mailbox

Navigation: Alert Manager --> System --> Installations
-- Setup if there are any CUSTOM application where the Alert needs to be created.

Types of Alerts: Event based and Periodic
Both types of alerts are defined by SQL statement specified in the SQL section.
Periodic Alert: Navigate to alert form and select the periodic Alert option and then below tasks to be performed.

- Application Name : Application name that owns the alert
- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field.
- Frequency : Select the frequency of the alert,below are the available options.

On Demand
On Day of the month
On Day of the week
Every N calendar Days
Every Day
Every Other Day
Every N Business Days
Every Business Day

Every Other Business Day
Also Start time and end time, number of times in 24 hours and end date if not required after some date.

- SQL : Enter a SQL Statement that retrieves all the alert information, that can used for actions to be planned.Thestatement should have INTO clause and one

output field.
Example: Input field    :INPUT_NAME
     Output field   &OUTPUT_NAME

Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in theSQL statement.

SELECT PACKAGE.FUNCTION_NAME(:INPUT_VALUE) INTO &OUTPUT_VALUE FROM DUAL;

IMPORT Option: Rather than creating a SQL statement, we can also import from file and can use in the application.

Event Alert: Navigate to alert form and select the Event Alert option and then below tasks to be performed.
- Application Name : Application name that owns the alert
- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field
- Specify the event table name (application be different that the table resides) but privileges should be present.
- Check the insert/update, when the alert has to be fired.

- SQL : Enter a SQL Statement that retrieves all the alert information, that can used for actions to be planned.Thestatement should have INTO clause and one

Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.

Also make sure there is a condition based on the event table in the SQL, by using :ROWID condition.

Example:
SELECT user_name INTO &NEWUSER FROM fnd_user WHERE rowid = :ROWID;

The SQL statement should be verified, run then it should be saved in the alert.

- Alert Details: Provide the input,output and installation/user id details.

- Action: Create the action Name and click on the action details.
Action type can be set as Message,SQL Statement script and operating system script.
and provide the details for the message details like To list and message data.

- Action sets: Once the alert actions are created, those alert actions can be included in the action sets.

FAQs on Oracle Alerts:

What are Oracle Alerts?

Oracle Alerts are used to monitor unusual or critical activity within a designated database. The flexibility of ALERTS allows a database administrator the ability to monitor activities from table space sizing to activities associated with particular applications. Alerts can be created to monitor a process in the database and to notify a specific individual of the status of the process.

2.    What are the different types of alerts, Define it?

You can define one of two types of alerts: an event alert or a periodic alert.

Event alert: An event alert immediately notifies you of activity in your database as it occurs.

Periodic alert: A periodic alert, on the other hand, checks the database for information according to a Schedule  you define.

3.    What are the different business uses of Alerts?

a) Keep you informed of critical activity in your database

b) Deliver key information from your applications, in the format  you choose to provide you with regular reports on your database information

c) Automate system maintenance and routine online tasks Information about exception conditions.

4.    What can be done with Alerts?

·         You can send notifications

·         You can send log files as attachments to notifications

·         You can call PL/SQL stored procedures.

·         You can send approval emails and get the results.

·         Print some content dynamically

5.    What types of actions can be generated when an Alert is triggered?

When an alert is executed, the alert can send an email message, run a concurrent program, run an operating system script, or run a SQL statement script. Using response processing, Oracle Alerts can solicit a response from a specific individual and perform an action based on the response that it receives.

6.    What is On-Demand periodic alert?
It is a periodic alert with frequency as ‘On-Demand’. That means there is no specific period assigned to this alert and you can run this alert at any time you want using Request Periodic Alert Check form.

7.    What database events can cause what actions?

An insert and/or an update to a specific database table

8.    What actions can you perform in an alert?

An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

9.    How event alert works?
When you define an event alert to monitor a table for inserts and/or updates, any insert or update to the table will trigger the event alert. When an insert or update to an event table occurs, Oracle Alert submits to the concurrent manager, a request to run a concurrent program called Check Event Alert. The concurrent manager runs this request according to its priority in the concurrent queue. When the request is run, Check Event Alert executes the alert Select statement. If the Select statement finds exceptions, Check Event Alert performs the actions defined in the enabled action set for the alert. If the Select statement does not find any exceptions, Check Event Alert performs the No Exception actions in the enabled action set for the alert.

10.  What do you specify when creating a Periodic Alert?

a. A SQL Select statement that retrieves specific database information

b. The frequency that you want the periodic alert to run the SQL statement

c. Actions that you want Oracle Alert to perform once it runs the SQL statement.

11. Can you define Alert on Oracle Applications Tables?

Yes

12. What is Periodic Set?
You can create a set of periodic alerts that Oracle Alert checks simultaneously. Use the Request Periodic Alert Check window to check the periodic set. Note that each periodic alert you include in a periodic set continues to run according to its individually defined frequency.

  13. How alert is different from database triggers?

a) Code can be modified and viewed in a screen

b) Periodic alert is not possible through Database trigger

c) Oracle Alert will also transfer your entire alert definition across databases. You can instantly leverage the work done in one area to all your systems.

d) Customizable Alert Frequency with Oracle Alert, you can choose the frequencyof each periodic alert. You may want to check some alerts every day, some only once a month, still others only when you explicitly request them.

  14.  What is Action Set?

An action set can include an unlimited number of actions and any combination of actions and action groups for your alert. You can define as many action sets as you want for each alert. Oracle Alert executes the alert Select statement once for each action set you define. During each action set check, Oracle Alert executes each action set member in the sequence you specify.

  15. Can you define detailed or summary actions in alert?

Yes, Detail or Summary Actions you can choose to have Oracle Alert perform actions based on a single exception or a combination of exceptions found in your database.

  16. What is Distribution List in Oracle Alert?
Distribution lists let you predefine a set of message recipients for use on many actions. If a recipient changes, you need only adjust it in the distribution list, not in the individual message actions.

  17.  Can you specify History Maintenance?

      Alert History Oracle Alert can keep a record of the actions it takes and the exceptions it finds in your database, for as many days as you specify.

  18. Can you perform actions when NO exceptions are found?

No Exception Actions : Oracle Alert can perform actions if it finds no exceptions in your database, same as alert actions.

19. What are the Action Levels for your alert actions?

There are three types of level for your action: Detail, Summary and No Exception.
During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.