ECC Procurement, Procurement Cycle Time Graph

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Grh Po Header Id, Grh Po Num, Grh Buyer Name, Doc Authorized User Ids, Security Level Code, Org Id, Duration, Us Duration Type ...
Imported from Enterprise Command Center
Dataset Key: po-pcc-orders-prccycletimegraph
Query Procedure: po_pcc_orders_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
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_end_date IS NULL )
       OR ( emp.effective_end_date =(SELECT Max(c.effective_end_date)
                                     FROM   per_all_people_f c
                                     WHERE  emp.person_id = c.person_id)
			    )
		  )) 	                                                          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
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV