Fusion PO - Query to Purchase Order Details

SELECT hout.name operating_unit,

       pha.segment1 po_number,

       TO_CHAR (pha.creation_date, 'DD-MON-RRRR HH24:MI:SS') creation_date,

       asa.vendor_name supplier,

       hrla.location_code ship_to,

       hrlb.location_code bill_to,

       pha.revision_num rev,

       ppf.full_name buyer,

       pvs.vendor_site_code site,

       podt.type_name TYPE,

       po_headers_sv3.get_po_status (pha.po_header_id) status,

       pol.line_num num,

       plt.line_type TYPE,

       mcb.segment1 || mcb.segment2 || mcb.segment3 || mcb.segment4 "category",

       pol.item_description description,

       pol.unit_meas_lookup_code uom,

       TO_CHAR (pll.need_by_date, 'DD-MON-RRRR HH24:MI:SS') need_by,

       pol.quantity Quantity,

       pha.currency_code currency,

       pol.unit_price price,

       pol.quantity * pol.unit_price amount,

          gcc.segment1

       || '.'

       || gcc.segment2

       || '.'

       || gcc.segment3

       || '.'

       || gcc.segment4

       || '.'

       || gcc.segment5

       || '.'

       || gcc.segment6

       || '.'

       || gcc.segment7

       || '.'

       || gcc.segment8

       || '.'

       || gcc.segment9

          charge_account,

       pll.shipment_num,

       ood.organization_code,

       pda.distribution_num distribution_num,

       plc.displayed_field

  FROM po_headers_all pha,

       hr_operating_units hout,

       ap_suppliers asa,

       hr_locations_all hrla,

       hr_locations_all hrlb,

       per_all_people_f ppf,

       po_vendor_sites_all pvs,

       po_document_types_all podt,

       po_lines_all pol,

       po_line_types plt,

       mtl_categories_b mcb,

       po_distributions_all pda,

       gl_code_combinations gcc,

       po_line_locations_all pll,

       org_organization_definitions ood,

       po_lookup_codes plc

 WHERE     1 = 1

       AND podt.org_id = pha.org_id

       AND pvs.vendor_site_id = pha.vendor_site_id

       AND pha.agent_id = ppf.person_id

       AND hrlb.location_id = pha.bill_to_location_id

       AND hrla.location_id = pha.ship_to_location_id

       AND hout.organization_id = pha.org_id

       AND asa.vendor_id = pha.vendor_id

       AND TRUNC (SYSDATE) BETWEEN TRUNC (ppf.effective_start_date) AND TRUNC (ppf.effective_end_date)

       AND pha.segment1 = '568924'

       AND pha.type_lookup_code = 'STANDARD'

       AND pha.org_id = 204

       AND podt.document_type_code = 'PO'

       AND podt.document_subtype = 'STANDARD'

       AND plt.line_type_id = pol.line_type_id

       AND pha.po_header_id = pol.po_header_id

       AND mcb.category_id = pol.category_id

       AND pda.code_combination_id = gcc.code_combination_id

       AND pda.po_header_id = pha.po_header_id

       AND pll.po_header_id = pll.po_header_id

       AND pll.po_line_id = pol.po_line_id

       AND pll.ship_to_organization_id = ood.organization_id

       AND plc.lookup_type='DESTINATION TYPE'

       and plc.lookup_code=pda.destination_type_code