ECC Contract Lifecycle Management, CLM Deliverables, SQL1

select
x.*
from
(
SELECT * FROM ( 
SELECT  gt.char1 ecc_spec_id
       ,del.deliverable_id
       ,gt.index_num2 business_document_id
       ,gt.char3 document_number
       ,type_tl.name AS "TYPE"
       ,del.deliverable_name AS "NAME"
       ,del.description
       ,del.comments
       ,del.actual_due_date
       ,del.completion_date
       ,flv.meaning AS "STATUS"
       ,party_tl.name responsible_party
       ,flv.language
       ,busdoc_tl.name document_type
       ,DECODE(del.deliverable_status, 'OPEN', 'Y', 'SUBMITTED', 'Y', 'N') pending
       ,CASE
          WHEN  del.actual_due_date < sysdate and del.deliverable_status = 'OPEN'
	      THEN 'OA_MEDIA/warningind_status.gif'
          else  null
        END alert
       ,gt.char2 entity_type
       ,flv1.meaning ON_TIME
       ,del.EXHIBIT_CODE EXHIBIT
FROM    okc_deliverables del
       ,po_session_gt gt
       ,fnd_lookup_values flv
       ,fnd_lookup_values flv1
       ,okc_deliverable_types_tl type_tl
       ,okc_resp_parties_tl party_tl
       ,okc_bus_doc_types_b busdoc
       ,okc_bus_doc_types_tl busdoc_tl
WHERE   del.deliverable_id = gt.num2
AND     flv.view_application_id = 0
AND     flv.lookup_type  = 'OKC_DELIVERABLE_STATUS'
AND     flv.lookup_code  = del.deliverable_status
AND     type_tl.DELIVERABLE_TYPE_CODE = del.deliverable_type
AND     del.responsible_party = party_tl.resp_party_code
AND     del.business_document_type = busdoc.document_type
AND     busdoc.document_type_class = party_tl.document_type_class
AND     busdoc.intent = party_tl.intent
and     busdoc.document_type = busdoc_tl.document_type
AND     flv1.lookup_type = 'YES_NO'
AND     flv1.lookup_code = (CASE WHEN del.completion_date <= del.actual_due_date  THEN 'Y'
                                 ELSE 'N' END)
AND     flv1.view_application_id = 0
AND     type_tl.language = flv.language
and     type_tl.language = party_tl.language
AND     type_tl.language = busdoc_tl.language
AND     type_tl.language = flv1.language
AND     flv.language in ( 'US')
AND     gt.key = 3197934 ) PIVOT ( max(STATUS) as STATUS,
                                              max(type) as type,
                                              max(responsible_party) as responsible_party,
                                              max(document_type) as document_type,
                                              max(ON_TIME) as ON_TIME
                                             FOR language in ( 'US' "US"))
) x
where
2=2