ECC Project Procurement, Deliverables

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