Oracle EBS Purchase Order

Oracle EBS Purchase Order

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

SQL Example:-

SELECT DISTINCT
hr.name OU_NAME
,DECODE(ph.type_lookup_code,'STANDARD','Standard Purchase Order','PLANNED','Planned Purchase Order','BLANKET','Blanket Purchase Agreement','CONTRACT','Contract Purchase Agreement') "Document Type Code"
,ph.segment1 "Document Num"
,ph.creation_date "Creation Date"
,ph.currency_code "Currency Code"
,papf.full_name "Buyer Name"
,ap.vendor_name "Vendor Name"
,ap.segment1 "Vendor Num"
,aps.vendor_site_code "Vendor Site Code"
,ph.comments "Description/Comments"
,(SELECT hl1.location_code
FROM apps.hr_locations hl1
WHERE hl1.location_id=ph.bill_to_location_id) "Bill To Location"
,hl.location_code "Ship To Location"
,atr.name "Payment Terms"
,ph.freight_terms_lookup_code "Freight Terms"
,DECODE(ph.authorization_status,null,'INCOMPLETE',ph.authorization_status) Status
,ph.approved_date "Approved Date"
,ph.fob_lookup_code Fob
,ph.note_to_vendor "Note To Vendor"
,(SELECT user_conversion_type FROM APPS.GL_DAILY_CONVERSION_TYPES WHERE conversion_type=ph.rate_type) "Rate Type"
,ph.rate_date "Rate Date"
,ph.ship_via_lookup_code "Freight Carrier"
,ph.note_to_receiver "Receiver Note"
,ap.vendor_type_lookup_code "Supplier Type"
,(SELECT name FROM APPS.PA_PROJECTS_ALL WHERE project_id=pd.project_id) "FA Project"
,(SELECT task_name
FROM APPS.PA_TASKS
WHERE task_id=pd.task_id) "FA Task"
from
APPS.PO_HEADERS_ALL ph,
APPS.AP_SUPPLIERS ap,
APPS.AP_SUPPLIER_SITES_ALL aps,
APPS.HR_LOCATIONS hl,
APPS.AP_TERMS atr,
APPS.PER_ALL_PEOPLE_F papf,
APPS.HR_OPERATING_UNITS hr,
APPS.PO_DISTRIBUTIONS_ALL pd,
APPS.ORG_ORGANIZATION_DEFINITIONS ood,
APPS.XLE_ENTITY_PROFILES lep,
APPS.XLE_REGISTRATIONS reg
WHERE 1=1
AND ph.vendor_site_id=aps.vendor_site_id(+)
AND ap.vendor_id =aps.vendor_id 
AND ph.org_id=hr.organization_id
AND hl.location_id(+)=ph.ship_to_location_id
AND papf.person_id=ph.agent_id
AND ph.terms_id=atr.term_id
AND ph.po_header_id=pd.po_header_id
AND ph.org_id=pd.org_id
AND (ph.authorization_status in('APPROVED','INCOMPLETE','REQUIRES REAPPROVAL','PRE-APPROVED','IN PROCESS','REJECTED') OR (ph.authorization_status is null))
AND ((pH.closed_code  ='OPEN') OR (ph.closed_code is null))
AND lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND lep.legal_entity_id = hr.default_legal_context_id
AND pd.destination_organization_id=ood.organization_id
AND lep.legal_entity_id = ood.legal_entity
AND hr.organization_id=ood.operating_unit;