ECC Procurement, Procurement Cycle Time Graph

select
x.*
from
(
select * from (
SELECT
ecc_spec_id||'-'||duration_type as ecc_spec_id,
grh_po_header_id,
grh_po_num,
grh_buyer_name,
language,
DOC_AUTHORIZED_USER_IDS,
SECURITY_LEVEL_CODE,
ORG_ID,
duration_type,
to_number(duration) duration
 
FROM (
 
 
select poh.po_header_id                       AS ecc_spec_id,
poh.po_header_id                                                    AS grh_po_header_id,
poh.segment1    grh_po_num,
(SELECT emp.full_name
 FROM   per_all_people_f emp
 WHERE  emp.person_id = poh.agent_id
 AND emp.effective_start_date =(SELECT Max(c.effective_start_date)
                                     FROM   per_all_people_f c
                                     WHERE  emp.person_id = c.person_id)
 AND rownum < 2 )  AS grh_buyer_name,
houtl.language                                                      AS language,
CASE WHEN pdt.SECURITY_LEVEL_CODE='PUBLIC' OR pdt.SECURITY_LEVEL_CODE='PURCHASING' THEN
          NULL
     ELSE
          po_pcc_agreements_util_pvt.get_doc_authorized_userIds('POPA', poh.TYPE_LOOKUP_CODE, poh.AUTHORIZATION_STATUS,
          poh.org_id,poh.AGENT_ID, poh.PO_HEADER_ID) END as DOC_AUTHORIZED_USER_IDS,
pdt.SECURITY_LEVEL_CODE,
poh.org_id ORG_ID,
(SELECT
 to_number( Avg(to_date(pll.creation_date)-to_date(nvl(prh.approved_date,pll.creation_date))))
 FROM po_requisition_headers_all prh,
      po_requisition_lines_all prl,
      po_line_locations_all pll
 WHERE pll.po_header_id = poh.po_header_id
 AND   prl.line_location_id = pll.line_location_id
 AND   prl.requisition_header_id = prh.requisition_header_id)
           AS time_in_pool,
(SELECT to_number(Avg(to_date(poh.creation_date)- to_date(neg.creation_date)))
 FROM
 pon_auction_headers_all neg,
 po_lines_all pol
 WHERE
 pol.po_header_id = poh.po_header_id
 AND pol.auction_header_id = neg.auction_header_id)
       AS time_in_sourcing,
case when po_dates.po_submit_date is not null and  poh.creation_date is not null then
to_number(to_date(po_dates.po_submit_date)-to_date(poh.creation_date))
else to_number(null) end AS time_in_ord_drafting,
case when po_dates.po_app_date is not null and po_dates.po_submit_date is not null then
to_number(to_date(po_dates.po_app_date)-to_date(po_dates.po_submit_date))
else to_number(null) end AS time_in_ord_approval,
po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_ORD_TIME_IN_POOL','201',houtl.language) AS time_in_pool_msg,
po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_ORD_TIME_IN_NEG','201',houtl.language) AS time_in_sourcing_msg,
po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_ORD_TIME_IN_ORD_DRAFT','201',houtl.language) AS time_in_ord_drafting_msg,
po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_ORD_TIME_IN_ORD_APP','201',houtl.language) AS time_in_ord_approval_msg
FROM po_headers_all poh,po_doc_style_headers doc_style, PO_DOCUMENT_TYPES_ALL pdt,
hr_all_organization_units_tl houtl,
(
select
po_dates.po_header_id,
(SELECT max(action_date)
FROM po_action_history
WHERE OBJECT_TYPE_CODE ='PO'
AND OBJECT_SUB_TYPE_CODE = 'STANDARD'
AND OBJECT_REVISION_NUM = 0
AND ACTION_CODE = 'SUBMIT'
AND object_id = po_dates.po_header_id) po_submit_date,
(SELECT max(action_date)
FROM po_action_history
WHERE OBJECT_TYPE_CODE ='PO'
AND OBJECT_SUB_TYPE_CODE = 'STANDARD'
AND OBJECT_REVISION_NUM = 0
AND ACTION_CODE = 'APPROVE'
AND object_id = po_dates.po_header_id) po_app_date
from po_headers_all po_dates
)  po_dates
WHERE poh.type_lookup_code IN ('STANDARD')
AND doc_style.style_id = poh.style_id
AND nvl(doc_style.clm_flag,'N') = 'N'
AND pdt.org_id = poh.org_id
AND pdt.DOCUMENT_TYPE_CODE = 'PO'
AND pdt.DOCUMENT_SUBTYPE = poh.TYPE_LOOKUP_CODE
and houtl.LANGUAGE IN ('US')
AND houtl.organization_id (+) = poh.org_id
AND po_dates.po_header_id(+)=poh.po_header_id  AND poh.creation_date >= nvl(to_date(to_char(to_timestamp(''),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS'),poh.creation_date)  ) grp_poh unpivot ((duration_type,duration) FOR  req_duration IN ((time_in_pool_msg,time_in_pool)AS '1',
                                                           (time_in_sourcing_msg,time_in_sourcing) AS '2',
                                                           (time_in_ord_drafting_msg,time_in_ord_drafting) AS '3' ,
                                                            (time_in_ord_approval_msg,time_in_ord_approval ) AS '4'
                                                            ))) pivot(Max(duration_type) AS duration_type FOR LANGUAGE IN ('US' "US"))
) x
where
2=2