ECC Project Procurement, Deliverables

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Record Identifier, Project Name, Order Number, Deldata, Business Document Type Code, Deliverable Type Code, Deliverable Status Code, Responsible Party Code, Deliverable Name ...
Imported from Enterprise Command Center
Dataset Key: po-proc-deliverables
Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL
Security Procedure:
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
Parameter NameSQL textValidation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV