ECC Project Procurement, Deliverables
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-proc-deliverables
Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL
Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues
Dataset Key: po-proc-deliverables
Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL
Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues
select x.* from ( SELECT * FROM (SELECT DELDATA AS ECC_SPEC_ID, DELDATA AS RECORD_IDENTIFIER, PROJECT_NAME , ORDER_NUMBER, DELDATA, BUSINESS_DOCUMENT_TYPE, BUSINESS_DOCUMENT_TYPE_CODE, DELIVERABLE_TYPE, DELIVERABLE_TYPE_CODE, DELIVERABLE_STATUS, DELIVERABLE_STATUS_CODE, RESPONSIBLE_PARTY_CODE, RESPONSIBLE_PARTY, DELIVERABLE_NAME, DELIVERABLE_DESCRIPTION, DISPLAY_SEQUENCE, FIXED_DUE_DATE_YN, ACTUAL_DUE_DATE, RECURRING_YN, AMENDMENT_NOTES, STATUS_CHANGE_NOTES, VENDOR_CONTACT_ID, PARTY_NAME, CONTACT, PO_HEADER_ID, DELIVERABLE_HOLD, HOLD_AMOUNT, DAYS_ON_HOLD, ORG_ID, PROJECT_ID, PROJECT_NUMBER, HOLD_DATE, MANAGE_DELIVERABLE, LANGUAGE FROM ( SELECT pap.name project_name ,poh.segment1 order_number ,del.deliverable_id deldata ,busdoctypes_tl.name business_document_type ,del.business_document_type business_document_type_code ,deliverabletypes_tl.name deliverable_type ,del.deliverable_type deliverable_type_code ,status_lookup.meaning deliverable_status ,del.deliverable_status deliverable_status_code ,del.responsible_party responsible_party_code ,reptl.name responsible_party ,del.deliverable_name ,del.description deliverable_description ,del.display_sequence ,del.fixed_due_date_yn ,del.actual_due_date ,del.recurring_yn ,del.amendment_notes ,del.status_change_notes ,poh.vendor_contact_id ,CASE WHEN del.deliverable_type = 'CONTRACTUAL' AND responsible_party = 'SUPPLIER_ORG' THEN ( SELECT vendor_name FROM ap_suppliers WHERE vendor_id = poh.vendor_id ) ELSE ( SELECT DISTINCT name FROM hr_all_organization_units_tl WHERE organization_id = poh.org_id AND language (+) = lang.language_code ) END party_name ,CASE WHEN del.deliverable_type = 'CONTRACTUAL' AND responsible_party = 'SUPPLIER_ORG' THEN ( SELECT DISTINCT party_name FROM hz_parties WHERE party_id = poh.vendor_contact_id ) ELSE ( SELECT DISTINCT full_name FROM per_all_people_f WHERE person_id = del.internal_party_contact_id ) END contact ,poh.po_header_id ,decode (nvl (holds.hold ,0) ,0 ,'N' ,'Y') deliverable_hold ,holds.hold_amount hold_amount ,trunc (sysdate - holds.hold_date) days_on_hold ,poh.org_id org_id ,psco.project_id ,pap.segment1 project_number ,holds.hold_date hold_date ,po_endeca_util_pub.po_get_action_text ('MANAGE_DELIVERABLE' ,20) manage_deliverable ,lang.language_code LANGUAGE FROM okc_deliverables del ,po_headers_all poh ,po_doc_style_headers ps ,pa_supply_chain_options psco ,pa_projects_all pap ,po_proc_plan_header pph ,fnd_lookup_values status_lookup ,okc_deliverable_types_tl deliverabletypes_tl ,okc_bus_doc_types_tl busdoctypes_tl ,okc_resp_parties_b repb ,okc_resp_parties_tl reptl , ( SELECT pod.po_header_id ,count (1) hold ,sum (apd.amount) hold_amount ,min (ah.hold_date) hold_date FROM ap_invoice_distributions_all apd ,ap_holds_all ah ,po_distributions_all pod WHERE apd.project_id > 0 AND ah.invoice_id = apd.invoice_id AND pod.po_distribution_id > 0 AND pod.po_distribution_id = apd.po_distribution_id AND ah.hold_lookup_code = 'PO Deliverable' AND ah.release_lookup_code IS NULL GROUP BY pod.po_header_id ) holds ,fnd_languages lang WHERE poh.po_header_id = del.business_document_id AND poh.revision_num = del.business_document_version AND poh.po_header_id = holds.po_header_id (+) AND business_document_type IN ('PO_STANDARD','RFQ') AND psco.project_id IN ( SELECT DISTINCT project_id FROM po_distributions_all WHERE po_header_id = poh.po_header_id ) AND pap.project_id = psco.project_id AND pph.project_id = psco.project_id AND psco.enable_scp_flag = 'Y' AND status_lookup.lookup_type = 'OKC_DELIVERABLE_STATUS' AND status_lookup.lookup_code = del.deliverable_status AND status_lookup.VIEW_APPLICATION_ID = 0 AND status_lookup.SECURITY_GROUP_ID = 0 AND status_lookup.language = lang.language_code AND del.deliverable_status not in ('INACTIVE' , 'CANCELLED') AND lang.installed_flag in ('I', 'B') AND nvl(deliverabletypes_tl.language, lang.language_code) = lang.language_code AND deliverabletypes_tl.deliverable_type_code = del.deliverable_type AND nvl(busdoctypes_tl.language, lang.language_code) = lang.language_code AND busdoctypes_tl.document_type = del.business_document_type AND poh.style_id = ps.style_id AND repb.document_type_class = reptl.document_type_class(+) AND repb.resp_party_code = reptl.resp_party_code(+) AND nvl (repb.intent , 'XXX') = nvl (reptl.intent ,'XXX') AND nvl(reptl.language, lang.language_code) = lang.language_code AND repb.resp_party_code(+) = del.responsible_party AND nvl(repb.document_type_class, 'PO') = 'PO' AND LANG.LANGUAGE_CODE in ('US')) ) PIVOT ( MAX(business_document_type) AS BUSINESS_DOCUMENT_TYPE, MAX(deliverable_type) AS DELIVERABLE_TYPE, MAX(party_name) AS PARTY_NAME, MAX(responsible_party) AS RESPONSIBLE_PARTY, MAX(deliverable_status) AS DELIVERABLE_STATUS for LANGUAGE in ('US' "US") ) ) x where 2=2 |