Basic Tables in PO

Some Basic Tables in PO Module

Requisition:

 po_requisition_headers_all

 po_requisition_lines_all

 po_req_distributions_all

Rfq and Quotations:

po_document_types_all

PO:

Po_headers_all,

Po_lines_all,

Po_line_locations_all,

Po_distributions_all

Receipt:

Rcv_shipment_headers

Rcv_shipment_lines

Invoice:

Ap_invoices_all

Ap_invoice_distributions_all

Payment:

Ap_invoice_payments_all

Ap_payment_schedules_all

Ap_checks_all

Journal:

Gl_je_headers

Gl_je_lines

Some other basic tables that  you may require:

Po_vendors  

Po_vendor_sites_all

Po_lookup_codes
Ap_lookup_codes
Mtl_system_items_b 

Gl_code_combinations

Pulling PO details on PO number

Tables Used,
1)PO_HEADERS_ALL
2)PO_LINES_ALL
3)PO_LINE_LOCATIONS_ALL
4)PO_DISTRIBUTIONS_ALL
5)PO_LOOKUP_CODES
6)PO_VENDORS
7)PO_VENDOR_SITES_ALL
8)HR_LOCATIONS
9)PER_PEOPLE_F
10)MTL_SYSTEM_ITEMS_B

SELECT PHA.SEGMENT1 "PO_NUMBER",
       PLC.DESCRIPTION "TYPE",
       PV.VENDOR_NAME "SUPPLIER",
       PVSA.VENDOR_SITE_CODE "SITE",
       H1.LOCATION_CODE "SHIP_TO",
       H2.LOCATION_CODE "BILL_TO",
       PHA.CURRENCY_CODE "CURRENCY",
       PPF.FULL_NAME "BUYER",
       PHA.AUTHORIZATION_STATUS "STATUS",
       PLA.PO_LINE_ID,
       PLA.LINE_NUM,
       PLA.PURCHASE_BASIS "LINE_TYPE",
       MSIB.SEGMENT1"ITEM",
       PLA.ITEM_DESCRIPTION,
       PLA.UNIT_MEAS_LOOKUP_CODE "UOM",
       PLA.QUANTITY,
       PLA.UNIT_PRICE "PRICE",
       PLLA.LINE_LOCATION_ID,
       H3.LOCATION_CODE "LINE_SHIP_TO",
       PLLA.QUANTITY "lINE_LOCATION_QTY",
       PDA.PO_DISTRIBUTION_ID,
       PDA.REQ_HEADER_REFERENCE_NUM "REQ_NUM",
       GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 "CHARGE_ACCOUNT"
  FROM PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLLA,
       PO_DISTRIBUTIONS_ALL PDA,
       PO_LOOKUP_CODES PLC,
       PO_VENDORS PV,
       PO_VENDOR_SITES_ALL PVSA,
       HR_LOCATIONS H1,
       HR_LOCATIONS H2,
       HR_LOCATIONS H3,
       PER_PEOPLE_F PPF,
       GL_CODE_COMBINATIONS GCC,
       MTL_SYSTEM_ITEMS_B MSIB
 WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
   AND UPPER(PHA.TYPE_LOOKUP_CODE)=UPPER(PLC.LOOKUP_CODE)
   AND PLC.LOOKUP_TYPE='PO TYPE'
   AND PHA.VENDOR_ID= PV.VENDOR_ID
   AND PHA.VENDOR_SITE_ID=PVSA.VENDOR_SITE_ID
   --AND PV.VENDOR_ID=PVSA.VENDOR_ID
   AND PHA.SHIP_TO_LOCATION_ID=H1.LOCATION_ID
   AND PHA.BILL_TO_LOCATION_ID=H2.LOCATION_ID
   AND PLLA.SHIP_TO_LOCATION_ID=H3.LOCATION_ID
   AND PPF.PERSON_ID=PHA.AGENT_ID
   AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
   AND PLA.ORG_ID=MSIB.ORGANIZATION_ID
   AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
   AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
   AND GCC.CODE_COMBINATION_ID=PDA.CODE_COMBINATION_ID
   AND PHA.SEGMENT1='Enter your PO Number'  
  

Pulling Requisition Details on Requisition Number

Tables Used
1)PO_REQUISITION_HEADERS_ALL
2)PO_REQUISITION_LINES_ALL
3)PO_REQ_DISTRIBUTIONS_ALL
4)PO_LOOKUP_CODES
5)MTL_SYSTEM_ITEMS_B
6)GL_CODE_COMBINATION
7)PER_PEOPLE_F

SELECT PRHA.SEGMENT1 "REQUISITION_NUMBER",
       PLC.DESCRIPTION "REQ_TYPE",
       PRHA.AUTHORIZATION_STATUS "REQ_STATUS",
       PPF.FULL_NAME "PREPARER",
       PRLA.LINE_NUM,
       PRLA.PURCHASE_BASIS "TYPE",
       MSIB.SEGMENT1 "ITEM",
       PRLA.ITEM_DESCRIPTION,
       PRLA.UNIT_MEAS_LOOKUP_CODE "UOM",
       PRLA.QUANTITY"LINE_QTY",
       PRLA.UNIT_PRICE "PRICE",
       PRLA.NEED_BY_DATE "NEED-BY",
       PRDA.DISTRIBUTION_ID,
       PRDA.REQ_LINE_QUANTITY "DISTRIBUTION_LINE_QTY",
       GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 "CHARGE_ACCOUNT"
  FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLA,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       PO_LOOKUP_CODES PLC,
       MTL_SYSTEM_ITEMS_B MSIB,
       GL_CODE_COMBINATIONS GCC,
       PER_PEOPLE_F PPF
 WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
   AND PRDA.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
   AND MSIB.INVENTORY_ITEM_ID=PRLA.ITEM_ID
   AND GCC.CODE_COMBINATION_ID=PRDA.CODE_COMBINATION_ID
   AND PPF.PERSON_ID=PRHA.PREPARER_ID
   AND PRHA.ORG_ID=MSIB.ORGANIZATION_ID
   AND UPPER(PRHA.TYPE_LOOKUP_CODE)=UPPER(PLC.LOOKUP_CODE)
   AND PLC.LOOKUP_TYPE='REQUISITION TYPE'
   AND PRHA.SEGMENT1='Enter your Requisition Number'
  
  

concurrent_programs & executables that are created by a user

Tables used
1)FND_USER--Holds user Information.
2)FND_CONCURRENT_PROGRAMS_VL-Holds the concurrent programs defined in the system.

3)FND_EXECUTABLES_VL-Holds the concurrent executables information.

 

SELECT FU.USER_NAME,
       FCPL.USER_CONCURRENT_PROGRAM_NAME,                                                          FCPL.DESCRIPTION,
       FEV.USER_EXECUTABLE_NAME,
       FEV.EXECUTABLE_NAME,
       FEV.EXECUTION_FILE_NAME 
  FROM FND_USER FU,  
       FND_CONCURRENT_PROGRAMS_VL FCPL,
       FND_EXECUTABLES_VL FEV 
 WHERE FCPL.EXECUTABLE_ID=FEV.EXECUTABLE_ID 
   AND FEV.CREATED_BY=FU.USER_ID 
   AND FU.USER_NAME='Enter User_name'

User_name and the responsibilities to which he assigned

Tables used
1)FND_USER--holds the information of a user.
2)FND_RESPONSIBILITY--holds the information of a responsibility
3)FND_USER_RESP_GROUPS_ALL--holds the information regarding   
                            resposibility assignments to the users.

SELECT FU.USER_ID,
       FU.USER_NAME,
       FR.RESPONSIBILITY_KEY 
  FROM FND_USER FU, 
       FND_RESPONSIBILITY FR,
       FND_USER_RESP_GROUPS_ALL FUR 
WHERE  FR.RESPONSIBILITY_ID=FUR.RESPONSIBILITY_ID 
  AND  FU.USER_ID=FUR.USER_ID
  AND  FU.USER_NAME=UPPER('Enter USER_NAME');