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.

..why integration is important ..

Integration is an extremely important part to ERP’s. ERP’s main goal is to integrate data and processes from all areas of an organization and unify it for easy access and work flow. ERP’s usually accomplish integration by creating one single database that employs multiple modules providing different areas of an organization with various business functions.

Oracle Purchasing Integration

Oracle Purchasing works in conjunction with other Oracle Applications to share information.

• Oracle EDI Gateway: Electronic Data Interchange
• Oracle Inventory: Items, demand, receipts
• Oracle Master Scheduling/MRP: Demand
• Oracle Order Management: Internal requisition
• Oracle Work in Process: Outside processing
• Oracle Human Resources: Employees
• Oracle Project Manufacturing: Project Details
• Oracle Quality: Quality Details
• Oracle Payables: Invoice Matching
• Oracle General Ledger :Journal Entries, Set of Books
• Oracle Supplier Scheduling: Approved Supplier List (ASL)
• Oracle Assets: Asset Details
• Oracle Bill of Material: Resources
• Oracle Cost Management :Pricing

When Oracle Inventory, Oracle Master Scheduling/MRP, and Oracle Purchasing are used in conjunction, they share supply information. The following information is shared:

• Open purchase orders.
• Open releases.

By using Oracle Project Manufacturing and oracle Purchasing, you can now specify project sand tasks for your requisition and purchasing transactions. Specifically, you can specify projects and tasks for the Inventory and Shop Floor destination types.

By using Oracle Workflow and Oracle Purchasing, you can automate the approval and document creation processes by using predefined Workflow processes.

What is Oracle Purchasing?

Oracle Purchasing is a comprehensive procurement solution designed to:

• Help purchasing professionals reduce administration costs.
• Process requisitions, purchase orders, requests for quotation, and receipts quickly and efficiently.
• Enable you to focus on:
  o Value Analysis
  o Development of strategic supplier relationships
  o Management of the procurement process

Oracle Purchasing satisfies the following business needs:

• Replaces paper processing with online requisition generation, purchase-order creation, and document approval.
• Regulates document access, controls modification activity and approval, and is based on organizational signature and security policies.
• Minimizes data entry time with item-saving templates.
• Controls purchasing activity and enables accurate,automatic pricing using approved suppliers and supplier lists.
• Consolidates purchase requirements from multiple warehouses, plants or locations.
• Enables negotiating from a position of strength based on easy access to purchase volume statistics and supplier performance reports.
• Facilitates communication between requesters, buyers, receiving staff, and accounts payable staff using online enquiries, attachments and notes.
• Provides related functions, such as marketing, finance, master scheduling, inventory management, production, cost accounting, and customer order entry with access to
  purchasing information.
• Manages historical purchasing data.

What is in Oracle Procurement solution suite?

From the oracle there are 4 different products , that helps in fulfilling complete business requirement as discussed above, these are mainly:

1. Internet Procurement (formerly Self-Service Purchasing) :A web-shopping solution for all employees that automates finding and requisitioning goods and services using self-guiding on-line catalogs coupled with efficient content management. Industry standard XML interfaces allow integration to customers’ existing Oracle, non-Oracle or legacy financial systems.

2. Purchasing Intelligence :a web-enabled analysis tool which provides access to enterprise-wide procurement information, along with the ability to perform ongoing analysis of sourcing decisions, contract compliance, and supplier performance .

3. Internet Supplier Portal, a self-service solution giving trading partners internet access to purchasing, receiving and payment information, as well as providing suppliers the capability to easily process their own transactions·extending process efficiencies across the entire supply chain.

4. Purchasing, which streamlines order planning and management for purchasing professionals.

Apart from these four basic products , these are also used as advance version of purchasing solutions:

• Oracle Sourcing is the enterprise application that drives more and better sourcing through online collaboration and negotiation.
• Oracle Procurement Contracts is used for createing and enforces better purchasing contracts.
• Oracle Daily Business Intelligence for Procurement is used for the spend-analysis application that allows buyers to spot savings opportunities immediately.
• Quality

More information can be found at :

• http://www.oracle.com/applications/procurement/Purchasing-data-sheet.pdf
• Oracle Datasheet