Project wise PO details Reports

Project wise PO details Reports

SELECT ipd.project_number PROJECT_NUMBER,
       ipda.asset_tag_id TAG_NUMBER,
       translate(ipd.description,'ä','a') Project_Desc,
       Translate(ipda.asset_desc,'ä','a') Asset_Desc,
      -- prh.segment1 req_number,
       ph.segment1 po_number ,
       ph.CREATION_DATE CREATION_DATE,
       (select Translate(full_name,'éÖ','eO') from per_all_people_f ppf1 where pd.deliver_to_person_id = ppf1.person_id(+) and sysdate between effective_start_Date and effective_end_Date)Requestor,
       ph.COMMENTS DESCRIPTION,
      to_char(sum(pl.quantity*pl.unit_price),'999,999,999.99') AMOUNT
       FROM
       apps.po_requisition_headers_all prh,
       apps.po_requisition_lines_all   prl,
       apps.po_req_distributions_all   prd,
       apps.po_distributions_all       pd,
       apps.po_line_locations_all      pll,
       apps.po_lines_all               pl,
       apps.po_headers_all             ph,
       apps.XXX_proj_reference_data_all ipda,
       apps.XXX_PROJ_DETAILS ipd
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)
   and prl.attribute6=ipda.asset_tag_id
   and prl.attribute5=ipda.project_id
   AND ph.AUTHORIZATION_STATUS='APPROVED'
   AND ipd.project_id=ipda.project_id
   --AND ipda.project_number='10004_000_OFF'
   --AND ipda.asset_tag_id='1115021_730'
   --and ph.segment1='394352'
  and ph.org_id=:P_ORG_ID
  and ph.org_id=ipd.org_id
--and ph.CREATION_DATE between '01-OCT-2018' AND '31-OCT-2018'
--and trunc(ph.CREATION_DATE) between trunc(:P_START_DATE) AND trunc(:P_END_DATE)
and trunc(ph.CREATION_DATE) between NVL(trunc(:P_START_DATE),trunc(ph.CREATION_DATE)) AND nvl(trunc(:P_END_DATE),trunc(SYSDATE))
   group by
   ipd.project_number,
   ipda.asset_tag_id,
   ph.segment1,
   ph.CREATION_DATE,
   pd.deliver_to_person_id,
   ph.COMMENTS,
   ipd.description,
   ipda.asset_desc
order by 1