req_header

SELECT DISTINCT
        (SELECT SEGMENT1 FROM PO_VENDORS WHERE VENDOR_ID=prl.VENDOR_ID) Vendor_Code,
        prh.requisition_header_id,
        prl.requisition_line_id,
        prh.segment1 REFERENCE_NUM,
        prh.TYPE_LOOKUP_CODE TYPE,
        prh.AUTHORIZATION_STATUS STATUS,
        (SELECT full_name
           FROM per_all_people_f
          WHERE person_id = prh.preparer_id ) PREPARER,
        prl.CURRENCY_CODE CURRENCY,
        prl.DESTINATION_TYPE_CODE DESTINATION_TYPE,
        ppf1.full_name "REQUESTOR",
        (SELECT organization_name
           FROM org_organization_definitions
          WHERE organization_id = prl.destination_organization_id) DESTINATION_ORGANIZATION,
        (SELECT location_code
           FROM hr_locations_all
          WHERE location_id = prl.deliver_to_location_id) LOCATION,
        prl.SOURCE_TYPE_CODE SOURCE,
        prl.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION,
        prl.SUGGESTED_VENDOR_NAME SUPPLIER,
        prl.SUGGESTED_VENDOR_LOCATION SUPPLIER_SITE,
        prl.SUGGESTED_VENDOR_CONTACT SUPPLIER_CONTACT,
        (SELECT line_type
           FROM po_line_types
          WHERE line_type_id = prl.line_type_id) LINE_TYPE,
        msi.segment1 Item_Code, 
        msi.description Item_Desc,        
        prl.UNIT_MEAS_LOOKUP_CODE UOM,
        prl.QUANTITY,
        prl.UNIT_PRICE PRICE,
        prl.NEED_BY_DATE,
        (SELECT concatenated_segments
           FROM gl_code_combinations_kfv
          WHERE code_combination_id = prd.CODE_COMBINATION_ID) CHARGE_ACCOUNT, 
        '' MULTIPLE_DISTRIBUTIONS,
        prh.attribute1,
        prh.attribute2,
        prh.attribute3,
        prh.attribute4,
        prh.attribute5,
        prl.RATE,
        prl.RATE_TYPE,
        prl.RATE_DATE       
from
        po.po_requisition_headers_all prh,
        po.po_requisition_lines_all prl,
        apps.per_people_f ppf1,
        (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
        po.po_req_distributions_all prd,
        inv.mtl_system_items_b msi,
        org_organization_definitions ood
WHERE
        prh.requisition_header_id = prl.requisition_header_id
        and prl.requisition_line_id = prd.requisition_line_id
        and ppf1.person_id = prh.preparer_id
        and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
        and ppf2.agent_id(+) = msi.buyer_id
        and msi.inventory_item_id = prl.item_id
        and msi.organization_id = prl.destination_organization_id
        AND trunc(prh.creation_date) between '01-Jul-2011' and '31-jul-2011'
        AND prh.org_id = ood.operating_unit       
        AND (prh.CLOSED_CODE NOT IN 'FINALLY CLOSED'
        AND prh.CLOSED_CODE NOT IN 'CLOSED'
        AND prh.closed_code NOT in 'CLOSE'
        OR prh.closed_code is null )
        AND ood.set_of_books_id = 5
ORDER BY 1,2