This post is answer of reader’s query which was pending from long time.

To elaborate in details , lets take sample case with two items.

Read the rest of this entry »

Before start of this topic you should be clear on these term;

• Encumbrance
  This is your any pre-expenditure, such as a purchase order, which will lead to a charge against an account.
• Commitment
  A journal entry you make to record an anticipated expenditure, as indicated by approval of a requisition.
• Obligation
  An encumbrance you record when you turn a requisition into a purchase order.

So…What Are Commitments?
Commitments are obligations for future expenditures made , and this should be for an employee or vendor PO’s. Commitments are tracked to help departments forecast their expenditures so that they do not exceed their budget available.

If you are using encumbrance you can get answer of these two question.

• How much do I have left to spend?
• Alert me if spending too much from allocated budget.

How much I left to spend , will be based out of these formula

Funds Available = Budget – Actual – Encumbrances.

budget is always unaltered, Encumbrances is reversed when matching to AP invoice.

Oracle Vanilla encumbrance accounting -Briefing

Oracle encumbrance accounting feature is supported by a process called funds reservation, which is implemented in the General Ledger as a user-exit or spawned program.

What is required if enabled Purchase Orders and Purchase Requisitions to first undergo a funds check when they are submitted. The system checks the line amount(s) of the requested purchase against the budget for the GL account(s) and alerts the user if that PO or Req. will exceed the funds on the account.This process of verification of available funds based on pre-defined summary templates.

Journals are then recorded in a special table called GL_BC_PACKETS, where they will sit temporarily before being converted by a concurrent process called “Program – Create journal” into permanent encumbrance journals.

The modules capable of initiating such transactions are the purchasing module for the creation of three documents, Standard Purchase Order, Requisition and Purchase Order Releases and the Accounts Payable module for the different invoices. It is possible to control the type of encumbrance created by these modules via the set-up of the applications.

The benefit of the encumbrance accounting feature is the ability to have the system control the expenditure budget from the General Ledger, reducing the risks of potential over expenditure.

This is often a legislated requirement for public organizations.

The transactional flow has funds reservation being performed through the GL_BC_PACKETS table when reserving funds for purchasing documents, and through the GL_INTERFACE table for receipt accruals which can be either On Receipt or at Period End. The funds checker process takes place whenever funds reservation and/or adjustment is made.

After transactions have taken place in Purchasing, it will become necessary to run the Create Journals program in GL. This program essentially sweeps through the GL_BC_PACKETS table and creates journals for the entries as entered.

Essentially this process allows the different manager to understand their “funds available” based on the following formula.

Funds Available = Budget – Actual – Encumbrance

The Oracle system has the ability to control this formula per fiscal period using the different balances tracked within GL_BALANCES (Essentially the General Ledger) by PTD (Period To Date), QTD (Quarter To Date), YTD (Year To Date),PJTD (Project To Date).

If you are coming from other Products like SAP or SUN , let clear these two terms in Oracle context in Project module specific.

• Actual Transactions – Actual transactions are recorded project costs. Which may includes labor, expense report, usage, burden, and miscellaneous costs.
• Commitment Transactions – Commitment transactions are anticipated project costs. Such examples include purchase requisitions and purchase orders or contract commitments.

Basic Oracle Process

The basic process flow for encumbrance accounting activity related to Oracle Purchasing typically is as follows:

1. Create a requisition.
2. Approve and reserve funds for the requisition.
3. AutoCreate requisition onto a purchase order.
4. Approve and reserve PO (which automatically reverses the requisition funds reservation)
5. Receive against the purchase order (either period-end or online accruals) and the funds are reversed for the amount received and entered against the charge account.

Take a note, funds reversal also occur when documents are cancelled or finally closed.

Requisitions, when autocreated onto a purchase order, have their funds reversed either when the PO is approved and reserved or when the requisition line(s) are rejected or returned.

Oracle Product associated with encumbrance

• General Ledger
• Purchasing
• Payables
• Projects
• Grants

Next will see Accounting Treatment in encumbrance.

Approved Supplier Lists in Oracle is the term used to describe a list of items and commodities that have approved sources from a list of suppliers.

In Oracle , while setting up Approved Supplier Lists you can specify the manufacturer of the product, the distributor of the product or a company that both sells and distributes. You must specify the name of the supplier, you can optionally specify the site that you purchase from. For each Approved Supplier Lists, you can specify whether it applies to the whole enterprise or a single facility.

Take a note, there is always one organization which is responsible for negotiating on behalf of subsidiaries and operating units, this is called the ‘Owning organization’.

Moreover ,you need to specify the unit of measure that will be used when creating purchase orders and the method of creating releases against blanket orders, which could be any of the following:

• Manual release through Autocreate
• Automatic release in complete status (i.e. generate and approve PO)
• Automatic release in incomplete status (i.e. generate PO and approve in separate step).

Read the rest of this entry »

In PO there are two methods to route documents for approval.

Approval Hierarchies (uses position hierarchies)

Employee/Supervisor Relationships (use employee/supervisor relationship)

Approval Hierarchies

Purchasing utilizes positions as a roadmap to determine how and where documents will be routed once the approval process has been initiated. It is first necessary to have created all positions that are going to be used in the system. Once all positions have been created, it is necessary to build the position hierarchy.

Each position has approval limits, so when a purchase order exceeds the limits of the position, the purchase order is forwarded onto the next position in the Hierarchy

Employee/Supervisor Relationships

This type of hierarchy does not use the Approval Hierarchy form,but is defined by the employee/supervisor relationship. The supervisor of an employee is defined on the Assignment region of the Employee form

If the purchase order entered by the employee exceeds the approval limits, the purchase order is forwarded onto the employees’ supervisor, as defined on the Employee form

To implement this form of approval routing, you need only to define JOBS. The JOB will then serve as the tie to the Approval group, and based on the approval limits from the Approval Group, the Document will either be Approved or Forwarded to the Employees’ Supervisor.

Similar Post

• Purchase Order > Approval hierarchy

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.

The consultant life while working at client site is not easy during ERP transformation projects, many times it’s required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

Internal Requisitions without Sales order

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

Requisition and PO

3. You need to list out all cancel Requisitions

Cancel Requisition

4. You need to list those PR which havn’t auto created to PO.(Purchase Requisition without a Purchase Order)

PR without PO

5. You need to list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

PR to PO

6.Identifying all PO’s which does not have any PR’s

PO without Requisition

7. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

8.You need to find table which hold PO Approval path…

These two table keeps the data:

• PO_APPROVAL_LIST_HEADERS
• PO_APPROVAL_LIST_LINES

9. List all the PO’s with there approval ,invoice and Payment Details

List PO’s with Approval , invoice and Payment info

Read the rest of this entry »

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.

Oracle Inventory automatically generates requisitions to maintain an item’s stock level. The requisitions may request stock from an internal predefined source organization or from an outside supplier.
Inventory generates replenishment requests automatically using the following methods:

• Min–Max Planning
• Reorder Point Planning
• Subinventory Replenishments for Replenishment Counts
• Kanban Replenishments

All inventory replenishment methods create requisitions via the Requisitions Open Interface.

A simple question …how can we drive creation of production requisitions from MRP, and automatically approve production requisitions for PO release.

Normally in oracle Master Scheduling/MRP creates requisitions for ”buy” items when you release them using the Planner Workbench. MRP generated requisitions are sourced using the same rules as on–line and inventory replenishment requisitions e.g., you can specify either Inventory or Supplier as the default source type, as well as the source organization and subinventory for internal requests. For ”buy” items, the Requisition Import process uses the item sourcing information to create either a purchase requisition or an inter–organization internal requisition for planned orders.

Last article I have mention , Integration aspect with oracle other modules. Here is the flow diagram of some of the shared data with other modules.