Purchasing Table: PO_HEADERS_ALL

This query provides information about lookup codes and foreign keys used by PO_HEADERS_ALL table. I have added few additional columns that i use frequently.

SELECT po_header_id "PO_HEADER_ID(Primary Key)" , segment1 po_number /* Not unique, could repeat for ORG_ID AND TYPE_LOOKUP_CODE*/ , (SELECT agent_name FROM po_agents_v pa WHERE pa.agent_id = ph.agent_id -- agent_id is foreign key to per_all_people_f -- i used this view so that only current employee record is used to fetch agent name ) agent_id_full_name , (SELECT displayed_field FROM po_lookup_codes plc1 WHERE plc1.lookup_code = ph.authorization_status AND plc1.lookup_type = 'DOCUMENT STATE' ) authorization_status_mng , revision_num , revised_date , (SELECT displayed_field FROM po_lookup_codes plc2 WHERE plc2.lookup_code = ph.type_lookup_code AND plc2.lookup_type = 'POXMUB_DOCUMENT_TYPE' ) type_lookup_code_mng , (SELECT vendor_name FROM ap_suppliers sup WHERE sup.vendor_id = ph.vendor_id ) vendor , (SELECT vendor_site_code || ': ' || address_line1 || ' ' || city || ' ' || state || '-' || zip FROM ap_supplier_sites_all ss WHERE ss.vendor_site_id = ph.vendor_site_id ) vendor_site , (SELECT Location_code || ': ' || address_line_1 || ' ' || town_or_city || ' ' || region_2 FROM hr_locations loc_bill WHERE loc_bill.location_id = ph.bill_to_location_id ) bill_to_location , (SELECT Location_code || ': ' || address_line_1 || ' ' || town_or_city || ' ' || region_2 FROM hr_locations loc_ship WHERE loc_ship.location_id = ph.bill_to_location_id ) ship_to_location , (SELECT name FROM ap_terms_vl trm WHERE trm.term_id = ph.terms_id ) terms_id_name , ( SELECT displayed_field FROM po_lookup_codes fob WHERE fob.lookup_type = 'FOB' AND fob.lookup_code = ph.fob_lookup_code ) fob_lookup_code_mng , ( SELECT displayed_field FROM po_lookup_codes fob WHERE fob.lookup_type = 'FREIGHT TERMS' AND fob.lookup_code = ph.freight_terms_lookup_code ) frieght_terms_lookup_code_mng , approved_flag , approved_date , print_count , printed_date , cancel_flag , closed_date , ( SELECT displayed_field FROM po_lookup_codes fob WHERE fob.lookup_type = 'DOCUMENT STATE' AND fob.lookup_code = ph.closed_code ) closed_code_mng , start_date_active , end_date_active , wf_item_type , wf_item_key FROM po_headers_all ph WHERE segment1 = 'SKM20150515A' -- -- Add additional conditions -- ;