Oracle Purchasing Queries

**Query to obtain PO/PR

Select Distinct
Pha.Segment1 Po#,
Pha.Po_Header_Id,
Pha.Creation_Date,
Pha.Approved_Date,
Pda.Po_Header_Id Pda_Po_Header_Id,
Prha.Segment1 Pr#
From
Apps.Po_Headers_All Pha,
Apps.Po_Distributions_All Pda,
Apps.Po_Req_Distributions_All Prda,
Apps.Po_Requisition_Lines_All Prla,
Apps.Po_Requisition_Headers_All Prha
Where
Pha.Org_Id=123 And
Pda.Po_Header_Id=Pha.Po_Header_Id And
Prda.Distribution_Id=Pda.Req_Distribution_Id And
Prla.Requisition_Line_Id=Prda.Requisition_Line_Id And
Prha.Requisition_Header_Id=Prla.Requisition_Header_Id And
Pha.Segment1 = '900000' And
Prha.Segment1 = '10000' And
--TO_CHAR(pha.approved_date,'DD-MON-YYYY') between('16-JAN-2007' AND '17-JAN_2007') AND
To_Date(Pha.Approved_Date) Between '15-JAN-2007' And '16-JAN-2007' And
Order By Pha.Creation_Date;

**Query to Obtain PO/Receipt

Select C.Segment1 Po_Number,
C.Closed_Code,
C.Authorization_Status,
D.Line_Num,
A.Receipt_Num,
A.Creation_Date "Receipt Creation",
C.Creation_Date "PO Creation Date"
From Apps.Rcv_Shipment_Headers A,
Apps.Rcv_Shipment_Lines B,
Apps.Po_Headers_All C,
Apps.Po_Lines_All D
Where A.Shipment_Header_Id = B.Shipment_Header_Id
And B.Po_Header_Id         = C.Po_Header_Id
And B.Po_Line_Id           = D.Po_Line_Id
And C.Po_Header_Id         = D.Po_Header_Id
And C.Org_Id=123
And A.Receipt_Num In ('1000')
And C.Segment1 In ('9100000');

**Query to get PO/Invoice/Vendor

Select
Pv.Vendor_Id "#Vendor Id",
Pv.Segment1 "#Vendor",
Pv.Vendor_Name "#Vendor Name",
Pha.Segment1 "#PO Number",
Pha.Vendor_Id "#Po Vendor id",
Pha.Approved_Date "#Po Approved Date",
Aia.Invoice_Num "#Invoice Num",
Aida.Distribution_Line_Number "#Invoice Line Num",
Aida.Amount "#Invoice Line Amount",
Aia.Invoice_Amount "#Invoice Amount",
Aia.Invoice_Date "Invoice Date"
From
Apps.Po_Vendors Pv,
Apps.Po_Headers_All Pha,
Apps.Po_Lines_All Pla,
Apps.Po_Distributions_All Pda,
Apps.Po_Line_Locations_All Plla,
Apps.Ap_Invoices_All Aia,
Apps.Ap_Invoice_Distributions_All Aida
Where
Aia.Invoice_Id=Aida.Invoice_Id And
--AIDA.INVOICE_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID AND
Aida.Po_Distribution_Id=Pda.Po_Distribution_Id And
Pda.Line_Location_Id=Plla.Line_Location_Id And
Plla.Po_Line_Id=Pla.Po_Line_Id And
Plla.Po_Header_Id=Pha.Po_Header_Id And
Pla.Po_Header_Id=Pha.Po_Header_Id And
Pha.Org_Id=Aia.Org_Id And
Pha.Authorization_Status Like 'APPROVED' And
Pv.Vendor_Id=Pha.Vendor_Id And
Pha.Segment1 = '9000000'And
--Pv.Segment1 ='ABC123'
Aia.Invoice_Num='123456' And
--to_date(AIA.INVOICE_DATE)>='01-JAN-2000' AND
--to_date(AIA.CREATION_DATE)>='01-JAN-2000' --AND
Order By Pv.Segment1,Pha.Segment1,Aia.Invoice_Date,Aia.Creation_Date;

**Query to obtain PO Approvers

Select Pha.Segment1 Po#,
(Select Sum (Poloc.Quantity * Pol.Base_Unit_Price)
From Apps.Po_Lines_All Pol,
Apps.Po_Line_Locations_All Poloc
Where Pol.Po_Header_Id = Pha.Po_Header_Id
And Poloc.Po_Header_Id = Pha.Po_Header_Id
And Pol.Po_Line_Id     = Poloc.Po_Line_Id
And Pol.Org_Id         = Pha.Org_Id
Group By Pol.Po_Header_Id) Total_Amount,
(Select Full_Name From Apps.Per_All_People_F A Where A.Person_Id=Pha.Agent_Id) Buyer_Name,
Pah.Sequence_Num,
Pah.Action_Code,
Ppf.Full_Name Approver,Ppf.Employee_Number Approver,
Pj.Name Job,
Paaf.Supervisor_Id,
(Select Full_Name From Apps.Per_All_People_F B Where B.Person_Id=Paaf.Supervisor_Id) Supervisor_Name
From Apps.Po_Headers_All Pha,
Apps.Po_Action_History Pah,
Apps.Per_All_People_F Ppf,
Apps.Per_All_Assignments_F Paaf,
Apps.Per_Jobs Pj
Where  Pha.Po_Header_Id=Pah.Object_Id
And    Ppf.Person_Id=Pah.Employee_Id
And    Ppf.Person_Id=Paaf.Person_Id
And    Pj.Job_Id=Paaf.Job_Id
And    Pah.Object_Type_Code='PO'
And    Pha.Segment1 In ('90000000')
Order By Pha.Segment1,Pah.Sequence_Num;

Select Distinct Papf.Last_Name ||' '||Papf.First_Name "Apporver Name",
Papf.Employee_Number "Approvar emp number",
Papfsup.Last_Name||' '||Papfsup.First_Name "Supervisor Name",
Papfsup.Employee_Number "Supervisor emp number",
Hou.Name "Operating Unit",
Decode (Ppca.Control_Function_Id, 1, 'APPROVE STANDARD PURCHASE ORDERS', 8, 'APPROVE PURCHASE REQUISITIONS') "FUNCTION",
Pcr.Amount_Limit "Approval Limit"
From Apps.Hr_Organization_Units Hou,
Apps.Po_Control_Rules Pcr,
Apps.Po_Control_Groups_All Pcg,
Apps.Po_Position_Controls_All Ppca,
Apps.Per_All_People_F Papf,
Apps.Per_People_V7 V7,
Apps.Per_All_Assignments_F Paaf,
Apps.Per_All_People_F Papfsup,
Apps.Per_Jobs Pb,
Apps.Po_Headers_All Poh,
Apps.Po_Distributions_All Pod,
Apps.Po_Lines_All Pla
Where Pcr.Control_Group_Id    = Pcg.Control_Group_Id
And Ppca.Control_Group_Id     = Pcg.Control_Group_Id
And Ppca.Org_Id               = Pcg.Org_Id
And Hou.Organization_Id       = Ppca.Org_Id
And Pcr.Object_Code           = 'DOCUMENT_TOTAL'
And Ppca.Control_Function_Id In (1,8)
And Papf.Person_Id            = Paaf.Person_Id
And Papf.Person_Id            =V7.Person_Id
And Paaf.Job_Id               = Pb.Job_Id
And Ppca.Job_Id              = Paaf.Job_Id
And Paaf.Supervisor_Id       = Papfsup.Person_Id--
And Pod.Po_Header_Id         = Poh.Po_Header_Id
And Pod.Org_Id               = Poh.Org_Id
And Poh.Po_Header_Id         = Pla.Po_Header_Id
And Pod.Deliver_To_Person_Id = Papf.Person_Id(+)
And Sysdate Between V7.Effective_Start_Date And V7.Effective_End_Date
And Sysdate Between Paaf.Effective_Start_Date And Paaf.Effective_End_Date
And Sysdate Between Papf.Effective_Start_Date And Papf.Effective_End_Date
And Sysdate Between Papfsup.Effective_Start_Date And Papfsup.Effective_End_Date
And Trunc (Sysdate) Between Papf.Effective_Start_Date And Papf.Effective_End_Date;

Query to obtain Payment terms on PO , Supplier and its supplier sites:

  SELECT   Poh.Segment1 Po,
           (SELECT   Name
              FROM   Apps.Ap_terms
             WHERE   Term_id = Poh.Terms_id)
              Po_terms,
           Aps.Vendor_name,
           (SELECT   Name
              FROM   Apps.Ap_terms
             WHERE   Term_id = Aps.Terms_id)
              Vendor_terms,
           Apss.Vendor_site_code,
           (SELECT   Name
              FROM   Apps.Ap_terms
             WHERE   Term_id = Apss.Terms_id)
              Vendor_site_terms
    FROM   Apps.Po_headers_all Poh,
           Apps.Ap_suppliers Aps,
           Apps.Ap_supplier_sites_all Apss
   WHERE   Poh.Vendor_id = Aps.Vendor_id
     AND   Aps.Vendor_id = Apss.Vendor_id
ORDER BY   1;

Query to Obtain PO-Receipt combination.

select
aps.segment1  "Supplier Number",
poh.segment1  "Purchase Order Number",
poh.AUTHORIZATION_STATUS  "Purchase Order Status",
Null  "Purchase Order Release Number",
poh.START_DATE  "Purchase Order Date",
papf.full_name  "Buyer",
msi.segment1   "Purchase Order Item Number",
pll.closed_code  "Purchase Order Item Status",
pol.PURCHASE_BASIS  "Purchase Order Item Type",
msi.segment1  "Location Part Number",
msi.description  "Location Part Description",
pol.VENDOR_PRODUCT_NUM  "Supplier Part Number",
POL.QUANTITY  "Item Order Quantity",
pol.unit_price  "Location Item Price",
poh.CURRENCY_CODE  "Location Currency Code",
pol.unit_price  "Supplier Item Price",
aps.PAYMENT_CURRENCY_CODE  "Supplier Currency Code",
msi.PRIMARY_UNIT_OF_MEASURE  "Inventory Unit of Measure",
pol.UNIT_MEAS_LOOKUP_CODE  "Purchase Unit of Measure",
Null  "Unit of Measure Conversion",
Null  "Item Schedule Number",
Null  "Schedule Quantity",
Null  "Schedule Open Quantity",
pll.Need_by_Date  "Need Date",
Null  "Expected Delivery Date",
pll.Promised_date  "Supplier Promise Date",
rct.transaction_date  "Receipt Date",
rct.receipt_num  "Receipt Number",
rct.quantity  "Receipt Quantity",
Null  "Rejected Quantity",
Null  "Rebate Amount",
Null  "Surcharge Amount",
Null  "PO Location Account Number",
POC.name  "PO Location Company Name",
POC.ADDRESS_LINE_1 "PO Location Address Line 1",
POC.ADDRESS_LINE_2 "PO Location Address Line 2",
POC.ADDRESS_LINE_3 "PO Location Address Line 3",
POC.TOWN_OR_CITY   "PO Location City",
POC.REGION_2       "PO Location State/Province",
POC.POSTAL_CODE    "PO Location Postal Code",
POC.COUNTRY        "PO Location Country Code"
from
apps.po_headers_all poh,
apps.po_lines_all pol,
apps.PO_LINE_LOCATIONS_ALL pll,
apps.PO_distributIONS_ALL pod,
apps.mtl_system_items_b msi,
apps.AP_SUPPLIERS aps,
apps.per_all_people_f papf,
(select
hou.name,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
hl.REGION_2,
hl.POSTAL_CODE,
hl.COUNTRY,
hou.ORGANIZATION_ID
from
hr_all_organization_units hou,
hr_locations hl
where hou.LOCATION_ID = hl.LOCATION_ID
) POC,
(select rt.transaction_date,
rsh.receipt_num,
rt.quantity,
rt.po_distribution_id,
rt.po_header_id,
rt.po_line_location_id,
rt.po_line_id
from
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where
rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.transaction_type = 'RECEIVE'
)RCT
where
poh.po_header_id = pol.po_header_id
and poh.po_header_id = pll.po_header_id
and poh.po_header_id = pod.po_header_id
and pol.po_line_id = pll.po_line_id
and pol.po_line_id = pod.po_line_id
and pol.item_id = msi.INVENTORY_ITEM_ID
and poh.vendor_id = APS.VENDOR_ID
and pod.destination_ORGANIZATION_ID = msi.ORGANIZATION_ID
and poh.agent_id=papf.person_id
AND pll.line_location_id = rct.po_line_location_id
AND pol.po_line_id = rct.po_line_id
AND pod.po_distribution_id = rct.po_distribution_id(+)
AND poh.po_header_id = rct.po_header_id(+);

Tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL

PO_HEADERS_ARCHIVE_ALL
PO_LINES_ARCHIVE_ALL
PO_LINE_LOCATIONS_ARCHIVE_ALL

PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
PO_VENDOR_LIST_ENTRIES

PO_AGENTS
PO_LINE_TYPES
PO_RELEASES_ALL
PO_ACTION_HISTORY
PO_USAGE_ATTRIBUTES
PO_INTERFACE_ERRORS

PO_CONTROL_RULES
PO_CONTROL_GROUPS_ALL
PO_POSITION_CONTROLS_ALL

RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS