ECC Contract Lifecycle Management, Acquisitions

select
x.*
from
(
select * from (select
          orc.contract_id||'-'||ord.deliverable_id as ecc_spec_id,
          orc.contract_id acquisition_plan_summary,
          orc.contract_number acquisition_plan_number,
          orc.contract_name name,
          lkup1.meaning status,
          lkup1.language doc_language,
          orc.contract_status_code,
          orc.latest_signed_ver_number,
          orc.contract_version_num version,
          orc.version_comments,
          orc.contract_desc as description,
          orc.org_id,
          ou.name   operating_unit,
          orc.contract_type contract_type_code,
          docs.name contract_type,
          orc.owner_id,
          (select nvl(pf.full_name, fu.user_name)
          from   per_all_people_f   pf,
                  fnd_user    fu
          where  fu.user_id = orc.owner_id
          and    pf.person_id (+) = fu.employee_id
          and   (fu.employee_id is null or pf.effective_start_date = (select max(effective_start_date)
                                                                      from   per_all_people_f
                                                                      where  person_id = fu.employee_id)))  administrator,
          orc.currency_code currency,
          orc.amount,
          orc.contract_effective_date as effective_date,
          orc.contract_expiration_date as expiration_date,
          orc.reference_document_type source_doc_type,
          orc.reference_document_number source_doc_reference,
          orc.reference_document_id source_doc_id,
          (select nvl(pf.full_name, fu.user_name)
                  from   per_all_people_f   pf,
                        fnd_user    fu
                  where  fu.user_id = orc.contract_last_updated_by
                  and    pf.person_id (+) = fu.employee_id
                  and   (fu.employee_id is null or pf.effective_start_date = (select max(effective_start_date)
                                                                              from   per_all_people_f
                                                                              where  person_id = fu.employee_id))) last_updated_by,
          trunc(orc.contract_last_update_date) as last_updated_date,
          PO_ACQUISITION_ECC_UTIL_PVT.get_next_approver(orc.contract_id) AS next_approver_id,
          orc.authoring_party_code,
          orc.overall_risk_code,
          orc.cancellation_comments,
          orc.cancellation_date,
          orc.termination_comments,
          orc.termination_date,
          orc.keywords,
          orc.object_version_number,
          Trunc(orc.contract_expiration_date) - Trunc(orc.contract_effective_date) planned_duration,
          CASE
            when orc.contract_status_code <> 'APPROVED'
              THEN null
            when trunc(orc.contract_effective_date) < trunc(sysdate)
              THEN Decode(
                  (SELECT 'INCOMPLETE' FROM okc_deliverables WHERE business_document_id=orc.contract_id AND deliverable_status<>'COMPLETED'
                    AND ROWNUM <2),
                  'INCOMPLETE', (Trunc(SYSDATE) - Trunc(contract_effective_date)),
                  --NULL )
                  (SELECT Max(Trunc(completion_date)) FROM okc_deliverables WHERE orc.contract_id = business_document_id AND deliverable_status='COMPLETED'
                    --AND (business_document_version(+) = Decode(t1.contract_status_code,'APPROVED',t1.contract_version_num,-99))
                    ) - Trunc(orc.contract_effective_date))
            ELSE null
          end elapsed_duration,
          (SELECT
                LISTAGG(replace(wfn.TO_USER,',',''),'|') WITHIN GROUP  (ORDER BY orc.contract_id) AS approval_pending_with
                  FROM
                  wf_notifications wfn,
                  wf_item_activity_statuses wfa
                WHERE
                          wfn.notification_id = wfa.notification_id
                         AND wfa.item_type         = 'OKCREPMA'
                         AND SUBSTR(wfa.item_key, 0, INSTR(wfa.item_key, '_')-1) =  orc.WF_ITEM_KEY
                         AND wfn.status            = 'OPEN'
                  GROUP BY orc.contract_id) current_approvers,
          ord.deliverable_id milestone,
          ord.deliverable_type,
          ord.deliverable_name milestone_name,
          ord.description milestone_description,
          ord.fixed_due_date_yn,
          ord.actual_due_date due_date,
          ord.internal_party_contact_id,
          (select buyer_contact.full_name
            from   per_all_people_f buyer_contact
            where  buyer_contact.person_id = ord.internal_party_contact_id
            and  buyer_contact.effective_start_date <= sysdate
            and (buyer_contact.effective_end_date is null or buyer_contact.effective_end_date> sysdate)
          ) milestone_owner,
          ord.schedule_type,
          ord.fixed_start_date,
          ord.fixed_end_date,
          ord.internal_party_id,
          ord.deliverable_status milestone_status_code,
          CASE
            WHEN orc.contract_status_code='REJECTED' THEN lkup1.meaning
            WHEN ord.DELIVERABLE_STATUS='INACTIVE' THEN fnd_message.get_string('PO','PO_ECC_ACQUISITION_DRAFT')
            WHEN ord.DELIVERABLE_STATUS='COMPLETED' THEN PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ord.deliverable_status,'OKC_DELIVERABLE_STATUS',0,lkup1.language)
            WHEN ord.DELIVERABLE_STATUS='FAILED_TO_PERFORM' THEN  PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ord.deliverable_status,'OKC_DELIVERABLE_STATUS',0,lkup1.language)
            WHEN trunc(ord.actual_due_date) >= SYSDATE and trunc(ord.actual_due_date) < Trunc(NEXT_DAY(sysdate, 'SUNDAY')) AND ord.deliverable_status='OPEN'
            THEN  fnd_message.get_string('PO','PO_ECC_ACQUISITION_DUE')
            WHEN trunc(ord.actual_due_date) < SYSDATE AND ord.deliverable_status='OPEN' THEN
            fnd_message.get_string('PO','PO_ECC_ACQUISITION_OVERDUE')
            ELSE fnd_message.get_string('PO','PO_ECC_ACQUISITION_OPEN')
          END milestone_status_derived,
          PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ord.deliverable_status,'OKC_DELIVERABLE_STATUS',0,lkup1.language) milestone_status,
          ord.business_document_version,
          ord.start_event_date,
          ord.end_event_date,
          ord.del_category_code,
          ord.external_party_id,
          ord.external_party_contact_id,
          ord.external_party_role,
          ord.comments milestone_comments,
          ord.completion_date,
          Trunc(ord.actual_due_date) - Trunc(orc.contract_effective_date) milestone_planned_duration,
          CASE
          WHEN ord.deliverable_status='COMPLETED' THEN Trunc(ord.completion_date) -Trunc(orc.contract_effective_date)
          WHEN trunc(orc.contract_effective_date) > trunc(sysdate) THEN null
          WHEN Trunc(orc.contract_effective_date) < Trunc(SYSDATE) AND ord.actual_due_date IS NOT null THEN
               Trunc(SYSDATE) - Trunc(orc.contract_effective_date)
          end milestone_elapsed_duration
          FROM OKC_REP_CONTRACTS_ALL orc,
          okc_deliverables ord,
          FND_LOOKUP_VALUES  lkup1,
          HR_ALL_ORGANIZATION_UNITS_tl ou,
          okc_bus_doc_types_tl docs
          WHERE
          CONTRACT_TYPE='REP_ACQ'
          AND orc.contract_id = ord.business_document_id(+)
          AND lkup1.lookup_type = 'OKC_REP_CONTRACT_STATUSES'
          AND   lkup1.lookup_code = orc.contract_status_code
          AND lkup1.language in ('US')
          and ou.organization_id = orc.org_id
          and ou.language = lkup1.language
          and docs.document_type = orc.contract_type
          and docs.language = lkup1.language
          AND ord.business_document_version(+) = Decode(orc.contract_status_code,'APPROVED',orc.contract_version_num,-99)
          AND orc.creatioN_date >=  to_date(to_char(to_timestamp('01-OCT-19'),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS')
               ) pivot(Max(status) AS status,
                                     Max(milestone_status) AS milestone_status,
                                     Max(operating_unit) AS operating_unit,
                                     Max(contract_type) AS contract_type,
                                     Max(milestone_status_derived) AS milestone_status_derived
                                     FOR doc_language IN ('US' "US")) 
) x
where
2=2