ECC Contract Lifecycle Management, Solicitations, SQL1

Description
Columns: Ecc Spec Id, Sol View Action, Auction Header Id, Solicitation Number, Title, Federal Publication Exception, Auction Status, Amendment Description, Two Stage Solicitation, Creation Date ...
Imported from ECC
select * from ( select
             gt.char1                    ecc_spec_id,
             'VIEW' SOL_VIEW_ACTION,
              ah.auction_header_id,
              (SELECT pah.document_number FROM pon_auction_headers_all pah
              WHERE pah.auction_header_id=ah.AUCTION_HEADER_ID_ORIG_AMEND) solicitation_number,
             ah.auction_title            title,
             ah.federal_publication_exception,
             ah.auction_status,
             CASE
                 WHEN ah.auction_status = 'DRAFT'
                      AND approval_status IS NOT NULL
                      AND approval_status NOT IN (
                     'REQUIRED',
                     'NOT_REQUIRED'
                 ) THEN fndmsg.message_text
                 ELSE po_pon_ecc_util_pvt.get_auction_status(ah.auction_header_id,hrou.language)
             END AS status,
             fndmsg.message_text         approval_status,
             ah.amendment_description amendment_description,
             hrou.name                   operating_unit,
             ps.display_name             outcome,
             pon_locale_pkg.get_party_display_name(ah.trading_partner_contact_id,12,hrou.language) buyer,
             ah.two_part_flag            two_stage_solicitation,
             ah.creation_date,
             ah.abstract_details         description,
             ah.close_bidding_date       close_date,
             CASE WHEN Nvl(ah.amendment_number,0)>= 1 and ah.AUCTION_STATUS='DRAFT' THEN
                ah.document_number
              ELSE
                NULL
             END  amendment_number,
             ah.auction_header_id_orig_amend,
             ah.contract_id,
             pon_clm_okc_integ_pkg.get_sol_total_amt(ah.auction_header_id) sol_total_amt,
             ah.org_id,
             DECODE(ah.auction_status,'DRAFT', (DECODE(ah.approval_status,'INPROCESS','Yes','TIMEOUT','Yes','No') ),'No') pending_solicitation_approvals
           ,
             DECODE(ah.auction_status,'DRAFT',DECODE(nvl(ah.amendment_number,0),0,'No','Yes'),'No') pending_sol_amendments,
             case
             when ah.auction_status<> 'DRAFT' or ah.approval_status='APPROVED' then
             'No'
 
             when po_pon_ecc_util_pvt.get_fbo_publish_exception(ah.auction_header_id) = 'Y' then
             'Yes'
             else 'No'
             end as fbo_publish_exceptions,
             ah.view_by_date             preview_date,
             ah.publish_date             published_date,
             ah.open_bidding_date        open_date,
             posh.style_name             negotiation_style,
             ah.currency_code            currency,
             PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ah.federal_publication_exception,'PON_FEDERAL_PUBLICATION',0,hrou.language)  exception_reason,
             pup.umbrella_program_name   umbrella_program,
             PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ah.solicitation_type,'PON_SOLICITATION_TYPE',0,hrou.language)          solicitation_type,
             PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ah.standard_form,DECODE(nvl(ah.amendment_number,0),0,'PO_SOL_STD_FORM','PO_SOL_AMEND_STD_FORM'),201,hrou.language)     standard_form,
             PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ah.document_format,'PO_CLM_DOC_FORMAT',201,hrou.language)   document_format,
             null as sol_std_form_doc_format,
             DECODE(nvl(ah.fair_opp_notice_flag,'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)) fair_opportunity_notice,
             PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ah.bid_visibility_code,'PON_BID_VISIBILITY_CODE',0,hrou.language)      offer_style,
             ah.event_id,
             ah.event_title              event,
             PO_PON_ECC_UTIL_PVT.get_lookup_meaning(ah.security_level_code,'PON_SECURITY_LEVEL_CODE',0,hrou.language)         security_level,
             ah.project_id,
             pa.segment1                 sourcing_project,
             hrou.language               language,
             ah.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=ah.auction_header_id
                  GROUP BY pntm.auction_header_id) NEG_TEAM_MEMBERS ,
                  (SELECT
                LISTAGG(replace(wfn.TO_USER,',',''),'|') WITHIN GROUP  (ORDER BY ah.auction_header_id) AS approval_pending_with
                  FROM
                  wf_notifications wfn,
                  wf_item_activity_statuses wfa
                WHERE
                  wfn.notification_id = wfa.notification_id
                         AND wfa.item_type         = 'PONAPPRV'
                         AND SUBSTR(wfa.item_key, 0, INSTR(wfa.item_key, '_')-1) =  ah.WF_APPROVAL_ITEM_KEY
                         AND wfn.status            = 'OPEN'
                  GROUP BY ah.auction_header_id) APPROVAL_PENDING_WITH,
                  ah.po_start_date EFFECTIVE_START_DATE,
                  ah.po_end_date EFFECTIVE_END_DATE,
                  ah.po_agreed_amount TOTAL_AGREEMENT_AMOUNT,
                  loc_bill.location_code BILL_TO_ADDRESS,
                  loc_ship.location_code SHIP_TO_ADDRESS,
                  ap.name PAYMENT_TERMS,
                  null as Carrier,
                    ah.AWARD_DATE,
                    ah.AWARD_COMPLETE_DATE  COMPLETION_DATE,
                    nvl(ah.MAX_LINE_NUMBER,0) as MAX_LINE_NUMBER
 
FROM
pon_auction_headers_all ah,
             hr_all_organization_units_tl hrou,
             po_all_doc_style_lines ps,
             po_doc_style_headers posh,
             pon_umbrella_programs pup,
             pa_projects_all pa,
             fnd_new_messages fndmsg,
             hr_locations_all_tl loc_bill,
             hr_locations_all_tl loc_ship,
             ap_terms ap,
             po_session_gt gt
WHERE
ah.auction_header_id = gt.num1
             AND hrou.organization_id(+)  = ah.org_id
             AND ah.po_style_id = ps.style_id (+)
             AND ah.contract_type = ps.document_subtype (+)
             AND hrou.language = ps.language
             AND posh.status (+) = 'ACTIVE'
             AND posh.style_id (+) = ah.style_id
             AND pup.umbrella_program_id (+) = ah.umbrella_program_id
             AND ah.project_id = pa.project_id (+)
             AND loc_bill.location_id(+) = ah.bill_to_location_id
             AND nvl(loc_bill.language,hrou.language) = hrou.language
             AND loc_ship.location_id(+) = ah.ship_to_location_id
             AND nvl(loc_ship.language,hrou.language) = hrou.language
             AND ap.term_id(+) = ah.payment_terms_id
AND fndMsg.application_id=396
AND fndMsg.language_code=hrou.language
AND fndMsg.message_name = decode(ah.approval_status,'APPROVED','PON_AUC_APPROVAL_APPROVED','REJECTED','PON_AUC_APPROVAL_REJECTED',
'NOT_REQUIRED','PON_AUC_APPROVAL_NOT_REQUIRED','REQUIRED','PON_AUC_APPROVAL_REQUIRED','INPROCESS','PON_AUC_APPROVAL_INPROCESS',
'TIMEOUT','PON_AUC_APPROVAL_INPROCESS',ah.approval_status)
AND hrou.LANGUAGE in ( 'US')
AND gt.key = 3127030 )  PIVOT (max(operating_unit) as operating_unit,
                              max(status) as status,
                              max(approval_status) as approval_status,
                              max(exception_reason) as exception_reason,
                              max(solicitation_type) as solicitation_type,
                              max(standard_form) as standard_form,
                              max(document_format) as document_format,
                              max(sol_std_form_doc_format) as sol_std_form_doc_format,
                              max(Offer_Style) as Offer_Style,
                              max(security_level) as security_level,
                              max(outcome) as outcome,
                              max(buyer) as buyer,
                              max(fair_opportunity_notice) as fair_opportunity_notice,
                              max(BILL_TO_ADDRESS) as BILL_TO_ADDRESS,
                              max(SHIP_TO_ADDRESS) as SHIP_TO_ADDRESS,
                              max(Carrier) as Carrier
                              for LANGUAGE in ('US' "US"   ))