ECC Contract Lifecycle Management, Requisitions, SQL11

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