ECC Contract Lifecycle Management, Requisitions, SQL13
Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Milestone Id, Milestone Code, Milestone Start Date, Milestone Name, Milestone Est Compl Days, Milestone Est Compl Date, Milestone Actual Compl Date, Milestone Elapsed Days, Us Milestone Type ...
Columns: Ecc Spec Id, Milestone Id, Milestone Code, Milestone Start Date, Milestone Name, Milestone Est Compl Days, Milestone Est Compl Date, Milestone Actual Compl Date, Milestone Elapsed Days, Us Milestone Type ...
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
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 = 3197938 ) PIVOT ( max(milestone_type) as milestone_type, max( mile_stone_status ) as mile_stone_status FOR language in ( 'US' "US")) ) x where 2=2 |