ECC Contract Lifecycle Management, CLM Protest, SQL1

Columns: Ecc Spec Id, Protest Id, Protest Number, Protest Description, Protest Document Type Code, Protest Case Number, Protest Filing Date, Protest Resolution Date, Protestor In System, Protest Supplier Id ...
Imported from ECC
SELECT * FROM ( SELECT  gt.char1 ecc_spec_id
       ,prt.description protest_description
       ,prt.document_type protest_document_type_code
       ,prt.filing_date protest_filing_date
       ,prt.resolution_date protest_resolution_date
       ,decode(nvl(prt.protestor_in_system,'N'),'Y','Y',null) protestor_in_system
       ,prt.supplier_id protest_supplier_id
       ,prt.supplier_contact_name protest_Supplier_User
       ,prt.supplier_name protest_Supplier_Organization
       ,prt.supplier_contact_id protest_supp_contact_id
       ,prt.address protest_address
       , protest_telephone_number
       , protest_email
       ,prt.fax protest_fax
       ,prt.cage_code protest_CAGE_NCAGE_Code
       , protest_duns_number
       ,DECODE(prt.protest_status, 'CLOSED', 'N', 'Y') open_protest
       ,pdslt.display_name protest_document_type
       ,ppf.full_name document_owner
       ,EMP.full_name protest_created_by
FROM    po_protests prt,
        po_headers_all poh,
        po_doc_style_lines_tl pdslt,
        po_session_gt gt,
        PER_ALL_PEOPLE_F ppf,
        fnd_user FU,
     	per_workforce_current_x EMP
WHERE   prt.document_id = gt.index_num2
AND     poh.po_header_id = gt.index_num2
AND     pdslt.style_id = poh.style_id
AND     pdslt.document_subtype = poh.type_lookup_code
AND     ppf.person_id = poh.agent_id
AND     fu.user_id = prt.created_by
AND     fu.employee_id = EMP.person_id
AND     prt.protest_id = gt.num2
AND     pdslt.language in ( 'US')
AND     gt.key = 3127023 ) PIVOT ( max(protest_document_type) as protest_document_type
                                             FOR language in ( 'US' "US"))