Oracle Purchasing

Oracle Purchasing

Oracle Purchasing

Requisition:

Requisition is a document where the need for the material is specified and is raised by an employee.

Types of Requisitions.

1. Internal Requisition: Transferring the material from one dept to another within the same company  and not from the third party (if the reqt is fulfilled within the company) is known as Internal requisition.

2. Purchase Requisition: If we want to purchase the material from  outside vendor then we raise Purchase requisition.

RFQ (Request for Qutotation):

The purchasing manager asks the supplier to send the quotation for the required material with the help of RFQ.

Types of RFQ:

1. Standard RFQ: The normal quotation is the standard rfq.

2. Bid RFQ: It is raised when we are planning to purchase heavy material which lasts longer.

3. Catalog RFQ: When we request the supplier to send catalog of items along with the quotation then it is called as catalog rfq.

Quotation:

A statement of the price, terms and conditions of sale, a supplier offers us for an item or items. It is a detailed description of goods or services offered by the supplier.

Types of Quotations:

1. Standard Quotation

2. Bid Quotation

3. Catalog Quotations

Purchase Order:

It is a statement which contains the required items, quantity, price,date when we need and the location where we need.

Types of Purchase Orders:

Blanket PO

Contract PO

Planned PO

Standard PO

Standard PO:

It is used for only one time purchase of various items when we know the details of goods or services we require, estimated cost, qty, delivery schedules and accounting distributions.

Blanket PO:

It is raised when we know the details of goods or services that we plan to buy from a specific supplier in a period, but we don’t know the details of delivery schedules.

Contract PO:

Here we don’t know any information regarding items but just know the terms and conditions.

Planned PO:

A planned po is along term agreement committing to by items or services from a single source. Here we know all the details.

Main concepts in PO:

Ø Item details

o Defining item category and its segment values

o Registering the item category.

o Define category sets and item.

Ø Employee

o Defining job and position of the employee

o Defining employee and his approval limits

o Defining the employees as buyers

§ Define and assign the approval groups to the employees

Ø Supplier

o Define and approve the supplier

o Define the supplier list

: Flow of Purchase Order

Ø Raise the requisition.

Ø Check whether the requisition is approved or not.

Ø Raise RFQ.

Ø Raise the quotation.

Ø Raise the purchase order.

Ø Receipts

Ø Inspect the goods

Matching approval levels:

2-Way Matching

Invoice à P.O.

Invoice à Receipts

3-Way Matching

Invoice-à P.O.

Invoice à Receipts

Invoiceà Invoice

4-Way Matching

Invoice-à P.O.

Invoice à Receipts

Invoiceà Invoice

Invoice à Inspect

PO Tables And Details

Important Table in Purchasing Module of Oracle apps
=========================================

Purchase Order
Po_headers_all
Po_lines_all
Po_line_locations_all
Po_distributions_all

Buyers
Po_agents

Document and line types
Po_document_types_all_b
Po_line_types_b


Releases
Po_realeases_all


Requisitions
Po_requisition_headers_all
Po_requisition_lines_all
Po_req_distributions_all

Vendors
Po_vendors
Po_vendor_sites_all
Po_vendor_contacts

Receiving
Rcv_transactions

Others
Mtl_system_items_b
Mtl_categories_b


Links between Oracle Apps PO Tables
============================
Po_headers_all/Po_lines_all --> po_header_id/po_header_id
Po_headers_all/Po_vendor_sites_all --> vendor_site_id/vendor_site_id
Po_headers_all/Po_vendors --> vendor_id/vendor_id
Po_headers_all/Po_line_locations_all--> po_header_id/po_header_id

Po_lines_all/Po_line_locations_all --> po_line_id/po_line_id
Po_lines_all/mtl_system_items_b --> item_id /inventory_item_id ( get for master org)
Po_lines_all/Mtl_categories_b --> category_id/ category_id

Po_line_locations_all/Rcv_transactions -->line_location_id/po_line_location_id

Po_vendor_sites_all/Po_vendors --> vendor_id/ vendor_id
po_distributions_all/po_req_distributions_all --> req_distribution_id/distribution_id

PO Interface Tables Details

1)PO_HEADERS_INTERFACE
====================

1)DOCUMENT_TYPE_CODE   = This column will accept any of the following string.
STANDARD
BLANKET
PLANNED   CONTRACT

2)VENDOR_NAME          =It  will accept valid vendor name . by using PO_VENDORS Table
we can findout wether vendorname is valid or not.

4)VENDOR_SITE_NAME     = PO_VENDOR_SITES_ALL
5)VENDOR_CONTACT_NAME  = PO_VENDOR_CONTACTS
6)SHIPTO           = HR_LOCATIONS  table will be use to find the location is valid
or not.
7)BILLTO           = HR_LOCATIONS  table will be use to find the location is valid
or not.
8)CREATION_DATE        =It should be in the Date Format.And also <=SYSDATE

9)AGENT_ID           = it should be valid agentID(Buyer ID) .By using PO_AGENTS table
we can find out wether agent_id is valid or not.
10)ORG_ID         =It should be Valid OrgID. By using hr_operating_units table we
can find wether it is valid or not.
11)AUTHORIZATION_STATUS = Valid status either APPROVED,INCOMPLETE,CANCELLED
12)CURRENCY_CODE    = Valid Currency Code from FND_CURRENCIES table
we can find wether valid currency code or not.


PO_LINES_INTERFACE:
====================
LINE_NUM        = Will accept only unique values.
LINE_TYPE    = Should be a Valid Line type. By using PO_LINE_TYPES we can findout
wether it is valid or not.
ITEM        =It should be a valid Item by using MTL_SYSTEM_ITEMS_B table we can
find wether valid Item or not.
ItemDesc        = Item Desc also should be valid description
UOM_Code        =Should be valid UOM by using MTL_UNITS_OF_MEASURES table we can find
wether it is valid or not.
QUANTItY        =Will accept any Positive Number
unit_Price      = Will accept any Positive Number
NEED_BY_DATE    = date Format and >= PO creation Date(from PO Headers Interface table)
PROMISED_DATE   = date Format and >= PO creation Date(from PO Headers Interface table)
ORG_ID            =It should be Valid OrgID. By using hr_operating_units table we
can find wether it is valid or not.
SHIP_TO_ORG    =Valid ORg_ID
ShipTo_Loc     = Valid Shiping Location

Interface Tables:


PO_REQUISITION_INTERFACE_ALL

PO_REQ_DIST_INTERFACE_ALL

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

AP_SUPPLIERS_INT

AP_SUPPLIER_SITES_INT

AP_SUP_SITE_CONTACT_INT

RCV_HEADERS_INTERFACE

RCV_TRANSACTIONS_INTERFACE

Error Table:


PO_INTERFACE_ERRORS

Query to list canceled Requisition:

select prh.REQUISITION_HEADER_ID,
prh.PREPARER_ID ,
prh.SEGMENT1 "REQ NUM",
trunc(prh.CREATION_DATE),
prh.DESCRIPTION,
prh.NOTE_TO_AUTHORIZER
from apps.Po_Requisition_headers_all prh,
apps.po_action_history pah
where Action_code='CANCEL'
and pah.object_type_code='REQUISITION'
and pah.object_id=prh.REQUISITION_HEADER_ID

REQUISITION

select prh.segment1 Req_num,

pdt.document_type_code,prh.TYPE_LOOKUP_CODE Type, prh.AUTHORIZATION_STATUS status,ppf.FULL_NAME Preparer,

PRL.LINE_NUM ,msi.SEGMENT1 item_num,PLT.LINE_TYPE,msi.DESCRIPTION Item_des,ppf.FULL_NAME Requester

from po_requisition_headers prh,

per_people_f ppf,

po_requisition_lines prl,

PO_LINE_TYPES PLT,

po_document_types pdt,

Mtl_system_items msi

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

and prh.preparer_id = ppf.person_id

and plt.line_type_id = prl.line_type_id

AND msi.inventory_item_id= prl.item_id

-- and msi.ORGANIZATION_ID=prl.ORG_ID

AND pdt.document_subtype = prh.type_lookup_code

AND pdt.document_type_code = 'REQUISITION'

AND pdt.org_id=prh.org_id

AND prh.segment1='5633'

PURCHASE ORDER QUERY

select pv.VENDOR_NAME,pv.VENDOR_ID,pv.SEGMENT1 vend_num,pp.FULL_NAME buyer_name, pp.LAST_NAME,pdt.type_name po_type,ph.STATUS_LOOKUP_CODE STATUS

,msi.SEGMENT1 item_num,msi.DESCRIPTION Item_des,pl.QUANTITY,pl.UNIT_PRICE

FROM po_vendors pv,

po_headers ph,

po_lines pl,

po_document_types pdt,

per_people_f pp,

mtl_system_items msi

where ph.po_header_id=pl.PO_HEADER_ID

AND pv.VENDOR_ID=ph.VENDOR_ID

and msi.ORGANIZATION_ID=ph.ORG_ID

AND ((    pdt.document_type_code IN ('PO', 'PA')

AND pdt.document_subtype = ph.type_lookup_code

) )

AND msi.inventory_item_id = pl.item_id

AND ph.agent_id = pp.person_id

AND ph.SEGMENT1=4419

P2P Cycle

select prh.segment1 reqno,ph.SEGMENT1 po_num ,prh.TYPE_LOOKUP_CODE po_Type,prh.TYPE_LOOKUP_CODE req_type ,prl.LINE_NUM,ph.ORG_ID

,pd.PO_DISTRIBUTION_ID,ai.INVOICE_TYPE_LOOKUP_CODE ,ai.INVOICE_AMOUNT,ai.AMOUNT_PAID,ai.INVOICE_DATE

from po_requisition_headers_all prh,

po_requisition_lines_all prl,

po_req_distributions_all prd,

po_headers ph

,po_lines pl

,ap_invoices_all ai

,po_distributions_all pd

,RCV_SHIPMENT_HEADERS RSH

,RCV_SHIPMENT_LINES RSL

,ap_invoice_distributions_all aid

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

and prd.REQUISITION_LINE_ID=prl.REQUISITION_LINE_ID

and pd.REQ_DISTRIBUTION_ID =prd.DISTRIBUTION_ID

and RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID

and ph.PO_HEADER_ID=rsl.PO_HEADER_ID

and pl.PO_LINE_ID=pd.PO_LINE_ID

and pl.PO_HEADER_ID=ph.PO_HEADER_ID

and aid.INVOICE_ID=ai.INVOICE_ID

and pd.PO_DISTRIBUTION_ID=aid.PO_DISTRIBUTION_ID

and prh.segment1='5659'

REQUSITION & PO QUERY FOR ID’S

select prh.SEGMENT1 reqno,ph.SEGMENT1 ponum, prh.REQUISITION_HEADER_ID,ppf.FULL_NAME preparer,ppf.FULL_NAME requester,

podt.DOCUMENT_TYPE_CODE,plt.LINE_TYPE,ms.SEGMENT1 Item,mo.ORGANIZATION_NAME,

pv.VENDOR_NAME supplier,hl.LOCATION_CODE "ship to location",mo.CITY "bill to location",ppf.FULL_NAME buyer

from

po_requisition_headers_all prh

,po_requisition_lines_all prl

,po_document_types podt

,po_headers ph

,po_lines pl

,po_vendors pv

,po_line_types plt

,po_req_distributions_all prd

,po_distributions_all pd

,per_people_f ppf

,mtl_system_items ms

,mtl_organizations mo

,hr_locations_all_tl hl

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

and ph.PO_HEADER_ID=pl.PO_HEADER_ID

and ph.VENDOR_ID=pv.VENDOR_ID

and prl.REQUISITION_LINE_ID =prd.REQUISITION_LINE_ID

and prd.DISTRIBUTION_ID=pd.REQ_DISTRIBUTION_ID

and pl.PO_LINE_ID=pd.PO_LINE_ID

and prh.TYPE_LOOKUP_CODE=podt.DOCUMENT_SUBTYPE

and prl.LINE_TYPE_ID=plt.LINE_TYPE_ID

and prh.PREPARER_ID=ppf.PERSON_ID

and prl.ITEM_ID=ms.INVENTORY_ITEM_ID

and prh.ORG_ID=mo.ORGANIZATION_ID

and prh.ORG_ID=ms.ORGANIZATION_ID

and ph.SHIP_TO_LOCATION_ID=hl.LOCATION_ID

and prh.SEGMENT1='5710'

ABOVE QUERY TO GET THE DETAILS FOR REQUSITION WITHOUT PO

select prh.SEGMENT1 reqno,ph.SEGMENT1 ponum, prh.REQUISITION_HEADER_ID,ppf.FULL_NAME preparer,ppf.FULL_NAME requester,

podt.DOCUMENT_TYPE_CODE,plt.LINE_TYPE,ms.SEGMENT1 Item,mo.ORGANIZATION_NAME,

pv.VENDOR_NAME supplier,hl.LOCATION_CODE "ship to location",mo.CITY "bill to location",ppf.FULL_NAME buyer

from

po_requisition_headers_all prh

,po_requisition_lines_all prl

,po_document_types podt

,po_headers ph

,po_lines pl

,po_vendors pv

, po_line_types plt

,po_req_distributions_all prd

,po_distributions_all pd

,per_people_f ppf

,mtl_system_items ms

,mtl_organizations mo

,hr_locations_all_tl hl

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

and ph.PO_HEADER_ID(+)=pl.PO_HEADER_ID

and ph.VENDOR_ID=pv.VENDOR_ID(+)

and prl.REQUISITION_LINE_ID =prd.REQUISITION_LINE_ID

and prd.DISTRIBUTION_ID=pd.REQ_DISTRIBUTION_ID(+)

and pl.PO_LINE_ID(+)=pd.PO_LINE_ID

and prh.TYPE_LOOKUP_CODE=podt.DOCUMENT_SUBTYPE

and prl.LINE_TYPE_ID=plt.LINE_TYPE_ID

and prh.PREPARER_ID=ppf.PERSON_ID

and prl.ITEM_ID=ms.INVENTORY_ITEM_ID

and prh.ORG_ID=mo.ORGANIZATION_ID

and prh.ORG_ID=ms.ORGANIZATION_ID

and ph.SHIP_TO_LOCATION_ID=hl.LOCATION_ID(+)

and prh.SEGMENT1='5711'