ECC Contract Lifecycle Management, Solicitation Protests

select
x.*
from
(
SELECT * from (select pah.auction_header_id||'-'|| prt.protest_id ECC_SPEC_ID,
        pah.auction_header_id,
        pah.document_number SOLICITATION_NUMBER,
        po_pon_ecc_util_pvt.get_auction_status(pah.auction_header_id, hrou.language) document_status,
        pon_locale_pkg.get_party_display_name(pah.trading_partner_contact_id,12,hrou.language) buyer,
        prt.protest_id
      ,prt.protest_number
      ,prt.description protest_description
      ,prt.protest_category protest_category_code
      ,PO_PON_ECC_UTIL_PVT.get_lookup_meaning(prt.protest_category,'PO_PROTEST_CATEGORY',201,HROU.LANGUAGE) protest_category
      ,prt.protest_status protest_status_code
      ,PO_PON_ECC_UTIL_PVT.get_lookup_meaning(prt.protest_status,'PO_PROTEST_STATUS',201,HROU.LANGUAGE) protest_status
      ,DECODE(prt.protest_status, 'CLOSED', 'No', 'Yes') pending_solicitation_protests
      ,prt.document_id protest_document_id
      ,prt.document_type protest_document_type_code
      ,prt.created_by protest_created_by_id
      ,pon_locale_pkg.get_party_display_name(users.person_party_id) protest_created_by
      ,PO_PON_ECC_UTIL_PVT.get_lookup_meaning(prt.document_type,'DOCUMENT TYPE',201,HROU.LANGUAGE) protest_Document_Type
      ,prt.protest_case_number
      ,prt.filing_date protest_filing_date
      ,prt.resolution_date protest_resolution_date
      ,decode(nvl(prt.protestor_in_system,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',hrou.language),PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',hrou.language)) 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,
        prt.phone protest_telephone_number,
        prt.email protest_email,
        prt.fax protest_fax,
        prt.cage_code protest_CAGE_NCAGE_Code,
        prt.duns protest_duns_number,
        HROU.LANGUAGE LANGUAGE,
        pah.org_id,
        pah.SECURITY_LEVEL_CODE,
             (SELECT
                LISTAGG(pntm.user_id,'|') WITHIN GROUP  (ORDER BY pntm.auction_header_id) AS NEG_TEAM_MEMBERS
                  FROM
                  pon_neg_team_members pntm
                WHERE
                  pntm.auction_header_id=pah.auction_header_id
                  GROUP BY pntm.auction_header_id) NEG_TEAM_MEMBERS
FROM    po_protests prt,
        fnd_user users,
        pon_auction_headers_all pah,
        HR_ALL_ORGANIZATION_UNITS_TL HROU
WHERE   prt.document_type = 'SOLICITATION'
        AND pah.auction_status NOT IN ('APPLIED','DELETED')
        AND users.user_id=    prt.created_by
        AND pah.auction_header_id= prt.document_id
        AND HROU.ORGANIZATION_ID = pah.ORG_ID
        and HROU.language in ( 'US')
 
        AND prt.creation_date >=
        to_date(to_char(to_timestamp('01-OCT-19'),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS'))PIVOT (max(protest_category) as protest_category,
                         max(protest_status) as protest_status,
                         max(protest_Document_Type) as protest_Document_Type,
                         max(buyer) as buyer,
                         max(document_status) as document_status,
                         max(protestor_in_system) as  protestor_in_system
                         FOR LANGUAGE in ('US' "US"))
) x
where
2=2