SQL: Requisition to PO: Oracle Fusion

SELECT
POH.PO_HEADER_ID,
POH.SEGMENT1  ,
PRHA.REQUISITION_NUMBER
FROM
PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL PDA ,
POR_REQ_DISTRIBUTIONS_ALL PRDA ,
POR_REQUISITION_LINES_ALL PRLA ,
POR_REQUISITION_HEADERS_ALL PRHA
WHERE 1=1
AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND PRHA.REQUISITION_NUMBER = '57'


REQUISITION DETAILS SQL:

SELECT
PERSONNAMEDPEO_L.FULL_NAME AS LINE_REQUESTER,
PRH.REQUISITION_HEADER_ID ,
PRH.REQUISITION_NUMBER ,
PRH.REQ_PREPARER ,
PRH.REQ_BU_NAME ,
PRH.REQ_DESCRIPTION ,
PRH.REQ_JUSTIFICATION ,
PRH.DOCUMENT_STATUS ,
PRH.PRC_BU_NAME ,
PRH.SUBMISSION_DATE ,
PRH.REQ_BU_ID,
PORLA.LINE_NUMBER,
PORLA.ITEM_DESCRIPTION,
PORLA.UOM_CODE,
(SELECT UNIT_OF_MEASURE FROM INV_UNITS_OF_MEASURE_VL WHERE UOM_CODE = NVL(PORLA.UOM_CODE,'-1') )UNIT_OF_MEASURE,
PORLA.UNIT_PRICE,
PORLA.QUANTITY,
PORLA.ITEM_ID,
PORLA.CATEGORY_ID,
CASE
WHEN PORLA.PURCHASE_BASIS='GOODS' THEN PORLA.QUANTITY*PORLA.UNIT_PRICE
WHEN PORLA.PURCHASE_BASIS='SERVICES' THEN PORLA.AMOUNT
ELSE -1
END LINE_AMOUNT,
PORLA.destination_organization_id,
(SELECT ITEM_NUMBER from EGP_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = PORLA.ITEM_ID and ORGANIZATION_ID= PORLA.destination_organization_id and rownum =1) ITEM_NUMBER,
(SELECT CATEGORY_NAME FROM EGP_CATEGORIES_VL WHERE CATEGORY_ID= PORLA.category_id and rownum =1) CATEGORY_NAME,
PORLA.PURCHASE_BASIS,
PORLA.MATCHING_BASIS,
PORLA.ATTRIBUTE_CATEGORY,
PORLA.ATTRIBUTE1,
CASE WHEN TRIM(PORLA.ATTRIBUTE_CATEGORY) = 'XYZ' THEN PORLA.ATTRIBUTE1
ELSE NULL
END JOB_ORDER
FROM
(SELECT
  /*+ FIRST_ROWS(10) */
  PORHA.REQUISITION_HEADER_ID,
  MAX(PORHA.REQUISITION_NUMBER) AS REQUISITION_NUMBER,
  PERSONNAMEDPEO.FULL_NAME                    AS REQ_PREPARER,
  (select NAME from HR_ALL_ORGANIZATION_UNITS_X where ORGANIZATION_ID=PORHA.REQ_BU_ID) AS REQ_BU_NAME,
  PORHA.DESCRIPTION AS REQ_DESCRIPTION,
  PORHA.JUSTIFICATION AS REQ_JUSTIFICATION,
  GlLedgers.CURRENCY_CODE AS FUNC_CURRENCY_CODE,
  (select MEANING from FND_LOOKUPS where LOOKUP_TYPE='POR_DOCUMENT_STATUS' and LOOKUP_CODE=PORHA.DOCUMENT_STATUS) AS DOCUMENT_STATUS,
  (select NAME from HR_ALL_ORGANIZATION_UNITS_X where ORGANIZATION_ID=PORHA.PRC_BU_ID) AS PRC_BU_NAME,
  MAX(PORHA.SUBMISSION_DATE) AS SUBMISSION_DATE,
  PORHA.REQ_BU_ID
FROM POR_REQUISITION_HEADERS_ALL PORHA,
  POR_REQUISITION_LINES_ALL PORLA,
  POR_REQ_DISTRIBUTIONS_ALL PORDA,
  FUN_ALL_BUSINESS_UNITS_V BusinessUnitPEO,
  FINANCIALS_SYSTEM_PARAMS_ALL FINANCIALOPTIONSPEO,
  PER_PERSON_NAMES_F_V PERSONNAMEDPEO,
  GL_LEDGERS GlLedgers
WHERE PORHA.REQUISITION_HEADER_ID          = nvl(:P_REQ_HEADER_ID, PORHA.REQUISITION_HEADER_ID)
AND BusinessUnitPEO.BU_ID                                = PORHA.REQ_BU_ID
AND BusinessUnitPEO.PRIMARY_LEDGER_ID                    = TO_CHAR(GlLedgers.LEDGER_ID)
AND PORHA.REQUISITION_HEADER_ID            = PORLA.REQUISITION_HEADER_ID
AND PORLA.REQUISITION_LINE_ID                = PORDA.REQUISITION_LINE_ID
AND PORLA.LINE_STATUS NOT                   IN ('SPLIT','REPLACED','CANCELED')
AND NVL(PORLA.ITEM_SOURCE, 'NOT REINSTATE') != 'REINSTATE'
AND PORHA.REQ_BU_ID                        = FINANCIALOPTIONSPEO.ORG_ID
AND PORHA.REQ_BU_ID    = :P_BUSINESS_UNIT_ID
AND PERSONNAMEDPEO.PERSON_ID               = PORHA.PREPARER_ID
AND TRUNC(sysdate) BETWEEN PersonNameDPEO.EFFECTIVE_START_DATE AND PersonNameDPEO.EFFECTIVE_END_DATE
GROUP BY PERSONNAMEDPEO.PERSON_ID,
  PERSONNAMEDPEO.FULL_NAME,
  PORHA.BUDGET_CONTROL_ENABLED_FLAG,
  PORHA.FUNDS_CHK_FAIL_WARN_FLAG,
  PORHA.PREPARER_ID,
  PORHA.JUSTIFICATION,
  PORHA.DESCRIPTION,
  PORHA.REQUISITION_HEADER_ID,
  PORHA.REQ_BU_ID,
  GLLEDGERS.CURRENCY_CODE,
  FINANCIALOPTIONSPEO.DEFAULT_EXCHANGE_RATE_TYPE,
  PORHA.DOCUMENT_STATUS,
  PORHA.PRC_BU_ID,
  PORHA.PCARD_ID,
  PORHA.SOLDTO_LE_ID,
  PORHA.OVERRIDING_APPROVER_ID,
  PORHA.FUNDS_OVERRIDE_APPROVER_ID,
  PORHA.FUNDS_STATUS,
  PORHA.DEFAULT_TAXATION_COUNTRY,
  PORHA.DOCUMENT_SUB_TYPE
  )PRH,
  POR_REQUISITION_LINES_ALL PORLA,
  PER_PERSON_NAMES_F_V PersonNameDPEO_L
WHERE   1=1
AND PRH.REQUISITION_HEADER_ID            = PORLA.REQUISITION_HEADER_ID
AND PORLA.REQUESTER_ID                 = PersonNameDPEO_L.PERSON_ID
AND TRUNC(sysdate) BETWEEN PersonNameDPEO_L.EFFECTIVE_START_DATE AND PersonNameDPEO_L.EFFECTIVE_END_DATE
AND PRH.REQUISITION_HEADER_ID            = :P_REQ_HEADER_ID