ECC Contract Lifecycle Management, Solicitations, SQL1

Description
Categories: Enterprise Command Center
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 Enterprise Command Center
Dataset Key: po-clm-solicitations
Query Procedure: po_pon_ecc_util_pvt.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesSol
select
x.*
from
(
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 = 3197942 )  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"   ))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV