ECC Contract Lifecycle Management, Acquisitions
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: CLM Acquisitions
Dataset Key: po-clm-acquisitions
Query Procedure: PO_ACQUISITION_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesAcq
Description: CLM Acquisitions
Dataset Key: po-clm-acquisitions
Query Procedure: PO_ACQUISITION_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesAcq
Run
ECC Contract Lifecycle Management, Acquisitions and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Blitz Report™