General #

This is an Oracle Fusion SQL Query returning the data from Requisitions - Lines and distributions.
The entities joined are:

• Project
• Project Task
• Vendors
• Person Name
• Suppliers - Profile and Site

Query #

SELECT
  HOU.NAME AS "Operating Unit",
  PRH.REQUISITION_NUMBER AS "Requisition Number",
  PRH.DESCRIPTION AS "Description",
  PRH.DOCUMENT_SUB_TYPE AS "Requisition Type",
  PRH.DOCUMENT_STATUS AS "Requisition Status",
  PRL.currency_code AS "Requisition Currency",
  PRL.LINE_NUMBER AS "Requisition Line Num",
  PRL.ITEM_DESCRIPTION AS "Requisition Line Desc",
  PRL.UOM_CODE AS "UOM",
  PRL.QUANTITY AS "Quantity",
  PRL.UNIT_PRICE AS "Unit Cost",
  MSI.item_number AS "Item Number",
  PRL.LIFECYCLE_STATUS "Line Lifecycle Status",
  PRL.LINE_STATUS as "Line Status",
  PRL.FUNDS_STATUS AS "Line Funds Status",
  PRL.VENDOR_ID,
  HP.PARTY_NAME as "Vendor",
  PRL.VENDOR_SITE_ID,
  PSSV.PARTY_SITE_NAME as "Vendor Site",
  HOUT.NAME AS "Organization",
  HLA.LOCATION_CODE AS "Location",
  PRL.DESTINATION_SUBINVENTORY AS "Department",
  per_name.display_name AS "Preparer",
  per_name2.display_name AS "Requisitioner",
  PRD.distribution_number "Distribution Number",
  PRD.percent "Distribution Percent",
  PRD.distribution_currency_amount "Distribution Amount Currency",
  PRD.distribution_amount "Distribution Amount",
  PRD.funds_status as "Distribution Funds Status",
  PRJ.SEGMENT1 "Project Number",
  PRJT.NAME AS "Project Name",
  PRJ.PROJECT_CURRENCY_CODE "Project Currency",
  PRJ_TASK.ELEMENT_NUMBER AS "Project Task Number",
  PRJT_TASK.NAME AS "Project Task Name"
FROM
  POR_REQUISITION_HEADERS_ALL PRH,
  POR_REQUISITION_LINES_ALL PRL,
  POR_REQ_DISTRIBUTIONS_ALL PRD,
  PER_ALL_PEOPLE_F PPF,
  PER_ALL_PEOPLE_F PPF2,
  per_persons per,
  per_person_names_f per_name,
  per_persons per2,
  per_person_names_f per_name2,
  EGP_SYSTEM_MASTER_ITEMS_VL MSI,
  HR_LOCATIONS_ALL HLA,
  HR_ALL_ORGANIZATION_UNITS HOUT,
  HR_ALL_ORGANIZATION_UNITS HOU,
  PJF_PROJ_ELEMENTS_B PRJ_TASK,
  PJF_PROJ_ELEMENTS_TL PRJT_TASK,
  PJF_PROJECTS_ALL_B PRJ,
  PJF_PROJECTS_ALL_TL PRJT,
  HZ_PARTIES HP,
  POZ_SUPPLIER_SITES_V PSSV,
  POZ_SITE_ASSIGNMENTS_ALL_M PSAAM,
  POZ_SUPPLIER_SITES_ALL_M PSSAM,
  POZ_SUPPLIERS PS
WHERE
  1 = 1 
  AND NVL(PRL.CANCEL_FLAG, 'N') <> 'Y'
  AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
  AND PRL.requisition_line_id = PRD.requisition_line_id
  AND TRUNC(SYSDATE) BETWEEN nvl(PPF.EFFECTIVE_START_DATE, sysdate - 1)
  AND nvl(PPF.EFFECTIVE_END_DATE, sysdate + 1)
  AND TRUNC(SYSDATE) BETWEEN nvl(PPF2.EFFECTIVE_START_DATE, sysdate - 1)
  AND nvl(PPF2.EFFECTIVE_END_DATE, sysdate + 1) 
  AND PPF.PERSON_ID = PRH.PREPARER_ID
  AND PPF2.PERSON_ID = PRL.REQUESTER_ID
  AND per.person_id = PPF.person_id
  AND per_name.person_id = PPF2.person_id
  AND per2.person_id = PPF.person_id
  AND per_name2.person_id = PPF2.person_id
  and per_name2.name_type = 'GLOBAL'
  and per_name.name_type = 'GLOBAL'
  AND MSI.INVENTORY_ITEM_ID(+) = PRL.ITEM_ID
  AND HLA.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
  AND HOUT.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
  AND HOU.ORGANIZATION_ID = PRh.REQ_BU_ID
  AND PRJ.PROJECT_ID(+) = PRD.pjc_project_id
  AND PRJ_TASK.PROJ_ELEMENT_ID(+) = PRD.pjc_task_id
  AND PRJT_TASK.PROJ_ELEMENT_ID(+) = PRJ_TASK.PROJ_ELEMENT_ID
  AND USERENV('LANG') = PRJT_TASK.LANGUAGE(+)
  AND USERENV('LANG') = PRJT.LANGUAGE(+)
  AND PRJT.PROJECT_ID(+) = PRJ.PROJECT_ID
  AND PS.PARTY_ID = HP.PARTY_ID(+)
  AND PRL.VENDOR_ID = PSSV.VENDOR_ID(+)
  AND PRL.VENDOR_SITE_ID = PSSV.VENDOR_SITE_ID(+)
  AND PSSV.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID(+)
  AND PSSAM.VENDOR_SITE_ID = PSAAM.VENDOR_SITE_ID(+)
  AND PS.VENDOR_ID(+) = PRL.VENDOR_ID