Under Progress……………

Purchasing Information in Oracle Apps(Including Tables)

Purchasing is the process of Purchase order documents from entry to the receipt of goods from the supplier.

Below is the high level information on different Types of documents at various stages in purchasing

Requisition

  • Internal
  • Purchase

RFQ

  • Standard
  • Bid
  • Catalog

Quotation

  • Standard
  • Bid and Catalog

Purchase Order

  • Standard
  • Planned
  • Contract
  • Blanket

Receipts

  • Direct Delivery (2 Way)
  • Standard Receipt (3 Way)
  • Inspection Required (4 Way)

1) Requisition

Requisition is one of the purchasing document created by employers when ever organization required goods or services.

These Requisitions contain three levels of information i.e.

  1. Header
  2. Lines
  3. Distribution

There are two types of Requisition process can be raised

  1. Internal Requisition : It is the requisition document created when we receiving the goods or services from other internal organizations
  2. Purchase Requisition : It is the requisition document created when we receiving the goods or services from outside the organization like supplier

We will be entering the information in requisition form at 3 levels header, lines, distributions

One Header=> one or Multiple Lines

One Line => one or Multiple distributions

Navigation : Purchase Order => Requisition => Requisitions

Enter the requisition details and click on approve. This will send for for further approval based on approval documentation setups

Requisition Tables :

select * from po_requisition_headers_all where segment1 = ‘1166’ — REQUISITION_HEADER_ID (Primary Key)

select * from po_requisition_lines_all where requisition_header_id = 254165 — REQUISITION_LIEN_ID (Primary Key)

select * from po_req_distributions_all where requisition_line_id = 52456 — DISTRIBUTION_ID (Primary Key)

If we want to find out requisition history. below is the table

select * from po_action_history where object_id = — requisition_header_id

2) Request for Quotation(RFQ)

It is also one of the purchase document after requisition is approved. 

RFQ will be created in two ways i.e.

  • Manual
  • Auto Create

We have 3 types of RFQs

  1. Bid : Suppose the items are expensive and company want to purchase. We will create Bid RFQ with header, lines and shipments information and not going to specify any price breaks
  2. Catalog : Assume company purchasing materials regularly with fixed quantity, location and date. We can include price breaks at different levels.
  3. Standard : This will be created for items we need only once. We can include price breaks at different levels

RFQ Terms and Conditions

  • Payment Terms – At the time of creating RFQ document, will be specify the payment terms like due date, interest rates etc.,
  • Freight Terms – These are transportation charges whether supplier will bear or buyer
  • Foot on Board(FOB) – Responsible of material damage or material missing during the transportation
  • Carrier – Will enter transportation company name. So that supplier will supply the materials to transportation company

3) Quotations

After RFQ document creation, we will be sending this document to the different suppliers who are going to supply the materials. We will receive quotations from the vendors through any of the channel via email/fax/phone

Quotation will be created in two ways i.e.

  • Manual
  • Auto Create

Quotations also 3 types Bid, Catalog and Standard

Quotations received from supplier will be entering into system for future purpose.

If we send Bid RFQ to the supplier, will receive Bid quotation. Similarly for other types as well

Navigation : Purchase Order => RFQ Form => RFQs and Quotation

Quotation Tables :

select * from po_headers_all where segment1 = ‘450’ AND TYPE_LOOKUP_CODE = ‘QUOTATION’ — PO_HEADER_ID (Primary Key)

select * from po_lines_all where po_header_id = 5551 — PO_LINE_ID (Primary Key)

select * from po_line_locations_all where po_line_id = 55251 — LINE_LOCATION_ID (Primary Key)

 

4) Purchase Orders

It’s a legal document which will be created by Organization and send to the Supplier to buy goods or services. Based on certain terms and conditions, we will create the PO document.

PO will be created in two ways i.e.

  • Manual
  • Auto Create

We can create RFQ or PO document manually or automatically. Auto create is the process of creating the RFQ/PO document in system based on approved requisition.

There are 4 types of purchase orders

  1. Standard purchase order
  2. Planned purchase order
  3. Blanket purchase Agreement
  4. Contract purchase Agreement

 Standard POPlanned POBlanket PAContract PA
Terms and ConditionsYesYesYesYes
Goods or ServicesYesYesYesNo
PricingYesYesMaybeNo
QuantityYesYesNoNo
Account DistributionsYesYesNoNo
Delivery ScheduleYesMaybeNoNo
Can be EncumberedYesYesNoNo
Can Encumber releasesNAYesYesNA

Standard purchase order :

It is a legal document to buy goods of service by supplier it will be created when we know the term and conditions goods or services, price, Quantity, Delivery, Schedule, and accounting distribution and also it is one time purchase order.

select * from po_headers_all where segment1 = ‘1448’ and TYPE_LOOKUP_CODE = ‘STANDARD’ — PO_HEADER_ID (Primary Key)

select * from po_lines_all where po_header_id = 5648– PO_LINE_ID (Primary Key)

select * from po_line_locations_all where po_line_id = 65451 — LINE_LOCATION_ID (Primary Key)

select * from po_distributions_all where line_location_id = 54215 — PO_DISTRIBUTION_ID (Primary Key)

Planned purchase order :

It is long time agreement so its agreement between the Org and Supplier to buy goods or services it will be created when we know the terms and conditions goods or services, price, quantity, and distribution, schedule, so later we will create schedule release with the reference of plan purchase order and send to the supplier to obtain goods or services.

 

Blanket purchase Agreement :

It is agreement between Org and Supplier to buy goods or services it will be created when we know the terms and condition goods or services and price later blanket retain will be created with reference of blanket purchase agreement and send to the supplier to obtain good or services

 

Contract purchase Agreement :

It’s long term agreement release a Org and Supplier it will be created when we know the terms and condition later standard purchase order will be created with reference of contract purchase agreement and send to the supplier to obtain goods or services

5) Receipts

Receipt document will be created while receiving the materials from the suppliers. We have 3 types of receipts

  1. Standard (2-Way)
  2. Direct Delivery (3-Way)
  3. Inspection Required (4-Way)

While creating purchase order in the shipments, we will specify match approval option

 

 POReceiptInspectionInvoice
Standard (2Way)Qty-1000NANAQty-1000
Direct Delivery (3Way)Qty-1000Qty-750NAQty-750
Inspection Required (4Way)Qty-1000Qty-750Qty
Good – 700
Bad – 50
Qty-700

2Way Matching – Comparing 2 documents PO quantity,price with Invoice Quantity,price

3Way Matching – Comparing 3 documents PO, Receipt and Invoice

4Way Matching – Comparing 4 documents PO, Receipt, Inspection and Invoice

Navigation => Purchase Order => Receiving => Receipts

Receipt Tables :

select * from rcv_shipment_headers where receipt_num = ‘34545’ — shipment_header_id (Primary Key)

select * from rcv_shipment_lines where shipment_header_id = 25432 — shipment_line_id (Primary Key)

select * from rcv_transactions

Note: always receiving will be done at inventory Organization

Purchasing and Receipts Important Queries in Oracle Apps R12

===================================================

—————————————-

Purchase Requisition details
—————————————-
SELECT prha.segment1 “Req No”, prha.creation_date, prha.created_by,
poha.segment1 “PO Num”, ppx.full_name “Requestor Name”,
prha.description “Req Description”, prha.authorization_status,
prha.note_to_authorizer, prha.type_lookup_code, prla.line_num,
prla.line_type_id, prla.item_description, prla.unit_meas_lookup_code,
prla.unit_price, prla.quantity, prla.quantity_delivered, prla.need_by_date,
prla.note_to_agent, prla.currency_code, prla.rate_type, prla.rate_date,
prla.quantity_cancelled, prla.cancel_date, prla.cancel_reason
FROM po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
per_people_x ppx,
po_headers_all poha,
po_distributions_all pda
WHERE prha.requisition_header_id = prla.requisition_header_id
AND ppx.person_id = prha.preparer_id
AND prha.type_lookup_code = ‘PURCHASE’
AND prda.requisition_line_id = prla.requisition_line_id
AND pda.req_distribution_id = prda.distribution_id
AND pda.po_header_id = poha.po_header_id


—————————————-

Internal Requisition details
—————————————-
SELECT prha.segment1 “Req Num”, prha.creation_date, prha.created_by,
poha.segment1 “PO Num”, ppx.full_name “Requestor Name”,
prha.description “Req Description”, prha.authorization_status,
prha.note_to_authorizer, prla.line_num, prla.line_type_id,
prla.source_type_code, prla.item_description, prla.unit_meas_lookup_code,
prla.unit_price, prla.quantity, prla.quantity_delivered, prla.need_by_date,
prla.note_to_agent, prla.currency_code, prla.rate_type, prl.rate_date,
prla.quantity_cancelled, prla.cancel_date, prla.cancel_reason
FROM po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
per_people_x ppx,
po_headers_all poha,
po_distributions_all pda
WHERE prha.requisition_header_id = prla.requisition_header_id
AND ppx.person_id = prha.preparer_id
AND prha.type_lookup_code = ‘INTERNAL’
AND prda.requisition_line_id = prla.requisition_line_id
AND pda.req_distribution_id(+) = prda.distribution_id
AND pda.po_header_id = poha.po_header_id(+)
— AND TO_CHAR (prha.creation_date, ‘YYYY’) IN (‘2017’, ‘2018’)

——————————–

Cancel Requisitions
——————————–

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 prha, apps.po_action_history pah
 WHERE action_code = ‘CANCEL’
   AND pah.object_type_code = ‘REQUISITION’
   AND pah.object_id = prh.requisition_header_id

————————————————————————————————————————

Internal Requisitions(IR) with associated Internal Sales Order (ISO) Query details

————————————————————————————————————————

SELECT  prha.segment1, rql.line_num, prla.requisition_header_id, prla.unit_price, prla.quantity, prla.quantity_cancelled, prla.requisition_line_id, prla.item_id, prla.unit_meas_lookup_code,
        prla.quantity_delivered, prla.cancel_flag, prla.source_type_code, prla.source_organization_id, prla.destination_organization_id, prla.transferred_to_oe_flag
    FROM po_requisition_lines_all prla, po_requisition_headers_all prha
   WHERE prla.requisition_header_id = prha.requisition_header_id
     AND prla.source_type_code = ‘INVENTORY’
     AND prla.source_organization_id IS NOT NULL
     AND NOT EXISTS (
            SELECT ‘existing internal order’
              FROM oe_order_lines_all oola
             WHERE oola.source_document_line_id = prla.requisition_line_id
               AND oola.source_document_type_id = 10)
ORDER BY prha.requisition_header_id, prla.line_num

 


Query to find Open POs in Oracle Apps

—————————————————

SELECT
    pha.segment1 ponumber,
    hou.name organization_code,
    pha.type_lookup_code potype,
    trunc(pha.creation_date) cdate,
    pv.vendor_name supplier,
    pv.segment1 supplier_number,
    pvs.vendor_site_code suppliersite,
    hl1.location_code shipto_loc,
    hl2.location_code billto_loc,
    (SELECT pla1.quantity * pla1.unit_price
     FROM po_lines_all pla1
     WHERE 1=1
     AND pla1.po_line_id = pla.po_line_id) PO_LINE_AMT ,
    pha.currency_code currency,
    papf.full_name buyer,
    pha.authorization_status,
    pha.comments comments,
    att.name terms,
    plla.need_by_date,
    plla.promised_date,
    pha.approved_date,
    pha.closed_code
FROM
    apps.po_headers_all pha,
    apps.ap_suppliers pv,
    apps.ap_supplier_sites_all pvs,
    apps.hr_locations hl1,
    apps.hr_locations hl2,
    apps.per_all_people_f papf,
    apps.po_lines_all pla,
    apps.hr_operating_units hou,
    apps.ap_terms_tl att,
    apps.po_line_locations_all plla
WHERE
        pha.vendor_id = pv.vendor_id
    AND pha.type_lookup_code   NOT IN (‘RFQ’,’QUOTATION’)
    AND pha.vendor_site_id = pvs.vendor_site_id
    AND pha.ship_to_location_id = hl1.location_id
    AND pha.bill_to_location_id = hl2.location_id
    AND pha.agent_id = papf.person_id
    AND pha.po_header_id = pla.po_header_id
    AND pha.org_id = hou.organization_id
    AND pha.terms_id = att.term_id
    AND plla.po_header_id = pha.po_header_id
    AND pla.po_line_id = plla.po_line_id
    AND pha.org_id = plla.org_id
    AND pha.closed_code = ‘OPEN’
    AND pha.org_id = 502
    AND pha.authorization_status = ‘APPROVED’
ORDER BY 1

——————————————————————

Query to find Purchase Order Approval History

——————————————————————

SELECT pah.action_code
, pah.object_id
, pah.action_date
, pah.sequence_num step
, pah.creation_date
, pha.segment1 po_num
, fu.description
, papf.full_name hr_full_name
, papf.employee_number emp_no
, papf.person_id
, fu.user_name
, pj.NAME job
FROM po.po_action_history pah
, po.po_headers_all pha
, applsys.fnd_user fu
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.per_jobs pj
WHERE object_id = pha.po_header_id
AND pah.employee_id = fu.employee_id
AND fu.employee_id = papf.person_id
AND papf.person_id = paaf.person_id
AND paaf.job_id = pj.job_id
AND paaf.primary_flag = ‘Y’
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND pah.object_type_code = ‘PO’
AND pah.action_code = ‘APPROVE’
AND papf.full_name = :pn
ORDER BY pah.sequence_num;

——————————————————————

Query to find Requisition Approval History

——————————————————————

SELECT pah.action_code
, pah.object_id
, pah.action_date
, pah.sequence_num step
, pah.creation_date
, prha.segment1 req_num
, prha.wf_item_key
, prha.authorization_status
, fu.description
, papf.full_name hr_full_name
, papf.employee_number emp_no
, pj.NAME job
FROM apps.po_action_history pah
, apps.po_requisition_headers_all prha
,apps.fnd_user fu
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.per_jobs pj
WHERE object_id = prha.requisition_header_id
AND pah.employee_id = fu.employee_id
AND fu.employee_id = papf.person_id
AND papf.person_id = paaf.person_id
AND paaf.job_id = pj.job_id
AND paaf.primary_flag = ‘Y’
AND SYSDATE BETWEEN papf.effective_start_date ANDpapf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date ANDpaaf.effective_end_date
AND pah.object_type_code = ‘REQUISITION’
AND pah.action_code = ‘APPROVE’
AND papf.full_name = :pn
ORDER BY pah.creation_date desc;