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 ...
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
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