ECC Contract Lifecycle Management, Requisitions, SQL10

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
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 = 3218981 ) PIVOT ( max(assignment_status) as assignment_status,
                                              max(assignment_type) as assignment_type
                                       FOR language in ( 'US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV