ECC Contract Lifecycle Management, Requisitions, SQL11
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Requisitions Data Set
Dataset Key: po-clm-requisitions
Query Procedure: PO_ICX_CLM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesReq
Description: Requisitions Data Set
Dataset Key: po-clm-requisitions
Query Procedure: PO_ICX_CLM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesReq
Run
ECC Contract Lifecycle Management, Requisitions, SQL11 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( SELECT gt.char1 ECC_SPEC_ID, pca.assignment_id || '-' || pmd.milestone_type || '-' || pmd.milestone_code milestone_id, pol.description milestone_type, pol.language, pmd.milestone_code, pmd.start_date milestone_start_date, pmd.description milestone_name, pmd.estimated_completion_days milestone_est_compl_days, pmd.estimated_completion_date milestone_est_compl_date, pmd.actual_completion_date milestone_actual_compl_date, case when pmd.actual_completion_date is not null then 'Completed' when pmd.estimated_completion_date < sysdate then fnd_message.get_string('PO','PO_ECC_ACQUISITION_OVERDUE') when trunc(pmd.estimated_completion_date) >= SYSDATE and trunc(pmd.estimated_completion_date) < Trunc(NEXT_DAY(sysdate, 'SUNDAY')) THEN fnd_message.get_string('PO','PO_ECC_ACQUISITION_DUE') else fnd_message.get_string('PO','PO_ECC_ACQUISITION_OPEN') end mile_stone_status , case when pmd.actual_completion_date is not null then (select Round(trunc(pmd.actual_completion_date) - trunc(pmd.start_date),0) from dual) when (trunc(pmd.start_date) > trunc(sysdate)) then (select trunc(sysdate) - trunc(sysdate) from dual) else (SELECT Round(trunc(sysdate) - trunc(pmd.start_date),0) from dual) end milestone_elapsed_days FROM po_clm_assignments pca, fnd_lookup_values pol, po_clm_assgnmt_milestone_dtls pmd, po_session_gt gt WHERE pmd.assignment_id = pca.assignment_id AND pol.lookup_type = 'CLM_MILESTONE_TYPE' AND pol.lookup_code = pmd.milestone_type and pol.language in ('US') and pca.assignment_id = gt.num4 and pmd.milestone_code = gt.char2 and pmd.milestone_type = gt.char3 AND gt.KEY = 3218981 ) PIVOT ( max(milestone_type) as milestone_type, max( mile_stone_status ) as mile_stone_status FOR language in ( 'US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |
Blitz Report™