ECC Contract Lifecycle Management, Requisitions, SQL12

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Assignment Id, Assignment Number, Assignment Start Date, Asgnmt Est Compl Days, Asgnmt Est Compl Date, Asgnmt Act Compl Date, Buyer Id, Buyer, Assigned By ...
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,
       pca.assignment_number ,
       pca.assignment_start_date,
       pca.estimated_completion_days asgnmt_est_compl_days,
       pca.estimated_completion_date asgnmt_est_compl_date,
       pca.actual_completion_date asgnmt_act_compl_date,
       pca.buyer_id,
       pbv.full_name buyer_name,
       per.full_name assigned_by,
       pol.description assignment_status,
       poll.description assignment_type,
       pol.language,
      case
        when pca.actual_completion_date is not null then
            (select Round(trunc(pca.actual_completion_date) - trunc(pca.assignment_start_date),0) from dual)
        when (trunc(pca.assignment_start_date) > trunc(sysdate)) then
             (select trunc(sysdate) - trunc(sysdate) from dual)
        else
             (SELECT Round(trunc(sysdate) - trunc(pca.assignment_start_date),0) from dual)
        end elapsed_days
FROM po_clm_assignments pca,
     po_buyers_val_v pbv,
     per_all_people_f per,
     fnd_lookup_values pol,
     fnd_lookup_values poll,
     PO_CLM_ASSGNMT_ACTION_HISTORY paah,
     po_session_gt gt
WHERE
  pca.buyer_id = pbv.employee_id
AND  pca.assignment_status = pol.lookup_code
AND  pol.lookup_type = 'CLM_ASSIGNMENT_STATUSES'
and  poll.lookup_type = 'CLM_ASSIGNMENT_TYPES'
and  nvl(pol.language,poll.language) = poll.language
and  poll.lookup_code = pca.assignment_type
and  pol.language in ('US')
and  pca.assignment_id  = paah.assignment_id (+)
AND paah.action_taken_by = per.person_id(+)
AND per.effective_start_date(+) <= trunc(sysdate)
AND per.effective_end_date(+) >= trunc(sysdate)
and pca.assignment_id = gt.num4
AND gt.KEY = 3197938 ) PIVOT ( max(assignment_status) as assignment_status,
                                              max(assignment_type) as assignment_type
                                       FOR language in ( 'US' "US"))
) x
where
2=2