ECC Contract Lifecycle Management, CLM Closeout Tasks, SQL1

Columns: Ecc Spec Id, Deliverable Id, Business Document Id, Document Number, Name, Description, Comments, Actual Due Date, Completion Date, Pending ...
Imported from ECC
SELECT  gt.char1 ecc_spec_id
       ,gt.index_num2 business_document_id
       ,gt.char3 document_number
       , AS "TYPE"
       ,del.deliverable_name AS "NAME"
       ,flv.meaning AS "STATUS"
       , responsible_party
       , document_type
       ,DECODE(del.deliverable_status, 'OPEN', 'Y', 'SUBMITTED', 'Y', 'N') pending
          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
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 = 3127026 ) 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"))