Oracle EBS Purchase Order

Oracle EBS Purchase Order

Tables:-
PO_LINES_ALL
PO_HEADERS_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL
ORG_ORGANIZATION_DEFINITIONS
HR_LOCATIONS
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
PER_ALL_PEOPLE_F
HR_OPERATING_UNITS
XLE_ENTITY_PROFILES
XLE_REGISTRATIONS

SQL Example:- PO_LINES

select distinct
hr.name "Operating Unit"
,ph.segment1 "Document Num"
,pol.line_num "Line Num"
,(SELECT line_type
 FROM  APPS.PO_LINE_TYPES
 WHERE line_type_id=pol.line_type_id) "Line Type"
,(SELECT segment1
 FROM APPS.MTL_SYSTEM_ITEMS_B
 WHERE inventory_item_id=pol.item_id and rownum=1) "Item"
,pol.item_description "Item Description"
,pol.unit_meas_lookup_code "Unit Of Measure"
,pol.quantity "Line Quantity"
,pol.unit_price "Unit Price"
,ood.organization_code "Ship To Organization Code"
,hl.location_code "Ship To Location"
,pll.need_by_date "Need By Date"
,pll.promised_date "Promise Date"
,pol.list_price_per_unit "List Price Per Unit"
,pol.note_to_vendor  "Note To Vendor"
,(SELECT routing_name FROM apps.rcv_routing_headers WHERE routing_header_id=pll.receiving_routing_id) "Receiving Routing"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.code_combination_id) "Charge Account"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.accrual_account_id) "PO Accural Account"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.budget_account_id) "PO Budget Account"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.variance_account_id) "PO Variance Account"
,pll.quantity "Ordered Quantity"
,pll.quantity_received  "Quantity Received"
,pll.quantity_billed  "Quantity Billed"
,pll.quantity_cancelled  "Quantity Cancelled"
,ph.rate  "RATE"
,DECODE(pol.closed_code,null,'OPEN',pol.closed_code) "Closure Status"
,ppf.full_name "Requestor"
,(select location_code from apps.hr_locations where location_id=pda.deliver_to_location_id) "Deliver To"
,pda.destination_subinventory  "Sub Inventory"
,hr.name "Requesition Org"
,pda.Accrued_flag "Accure At Receipt"
,(select mc.segment1 from MTL_CATEGORIES mc where category_id =pol.category_id ) "Category"
,pll.SHIPMENT_NUM SHIPMENT_NUM
,pda.DISTRIBUTION_NUM DISTRIBUTION_NUM
,pda.QUANTITY_ORDERED DISTRIBUTION_QUANTITY_ORDERED
,pda.quantity_delivered distri_quantity_delive
,pda.quantity_billed distri_quantity_billed
,pda.quantity_cancelled distri_quantity_cancelled
FROM
APPS.PO_LINES_ALL pol,
APPS.PO_HEADERS_ALL ph,
APPS.PO_LINE_LOCATIONS_ALL pll,
APPS.PO_DISTRIBUTIONS_ALL pda,
APPS.ORG_ORGANIZATION_DEFINITIONS ood,
APPS.HR_LOCATIONS hl,
APPS.AP_SUPPLIERS ap,
APPS.AP_SUPPLIER_SITES_ALL aps,
APPS.PER_ALL_PEOPLE_F ppf,
APPS.HR_OPERATING_UNITS hr,
APPS.XLE_ENTITY_PROFILES lep,
APPS.XLE_REGISTRATIONS reg
WHERE 1=1
AND pol.po_header_id=pll.po_header_id
 AND   ph.po_header_id=pol.po_header_id
 AND pol.po_line_id=pll.po_line_id
 AND pll.ship_to_organization_id=ood.organization_id
 AND pll.ship_to_location_id=hl.location_id
 AND pol.org_id=hr.organization_id
 AND ph.po_header_id=pda.po_header_id
 and pol.po_line_id=pda.po_line_id
 AND ph.org_id=pda.org_id
 AND ph.vendor_site_id=aps.vendor_site_id(+)
 AND ap.vendor_id =aps.vendor_id
  AND pda.deliver_to_person_id=ppf.person_id(+)
 AND (ph.authorization_status in('APPROVED','INCOMPLETE','REQUIRES REAPPROVAL','PRE-APPROVED','IN PROCESS','REJECTED') OR (ph.authorization_status is null))
 AND ((pol.closed_code  ='OPEN') OR (pol.closed_code is null))
 AND ((ph.closed_code  ='OPEN') OR (ph.closed_code is null))
  AND lep.legal_entity_id = reg.source_id
  AND hr.organization_id=ood.operating_unit
  AND reg.source_table = 'XLE_ENTITY_PROFILES'
  AND lep.legal_entity_id = hr.default_legal_context_id
  AND lep.legal_entity_id = ood.legal_entity;