PON FedBizOpps Compliance

Description
Categories: BI Publisher, Procurement
Application: Sourcing
Source: FedBizOpps Compliance
Short Name: PONXFEDOPPS_XML
DB package: PON_PONXFEDOPPS_XMLP_PKG
select distinct
pah.document_number,
decode(pah.amendment_flag, 'Y',
       PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
       'addresses', NULL, 'location', 'AMD_ISSUING_OFFICE', 'INTERNAL_VALUE'),
       PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
       'addresses', NULL, 'location', 'ISSUING_OFFICE', 'INTERNAL_VALUE')) ISSUE_OFFICE_ID,
decode(pah.amendment_flag, 'Y', null,pah.amendment_number) amendment_number,
pah.auction_header_id,       
PAH.PUBLISH_DATE, 
PAH.CREATION_DATE, 
PAH.OPEN_BIDDING_DATE,
PAH.CURRENCY_CODE,        
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pah.trading_partner_contact_id) BUYER_FULL_NAME,
pah.auction_status,
(PON_AUCTION_PKG.get_auction_status_display(pah.auction_header_id, pah.trading_partner_id)) DOC_STATUS_DESC,
(SELECT SUM (NVL(PAI.CLM_AMOUNT, 0)) FROM pon_auction_item_prices PAI 
WHERE PAI.AUCTION_HEADER_ID = PAH.AUCTION_HEADER_ID) ESTIMATED_AMOUNT,
 decode(pah.amendment_flag, 'Y',
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'contact','AMD_ISSUING_OFFICE', 'DISPLAY_VALUE'),
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'contact','ISSUING_OFFICE', 'DISPLAY_VALUE')) ISSUE_OFFICE_CONTACT,
 decode(pah.amendment_flag, 'Y',
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'addresscode', 'AMD_ISSUING_OFFICE', 'DISPLAY_VALUE'),
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'addresscode', 'ISSUING_OFFICE', 'DISPLAY_VALUE'))ISSUE_OFFICE_CODE,
 decode(pah.amendment_flag, 'Y',
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'location', 'AMD_ISSUING_OFFICE', 'DISPLAY_VALUE'),
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'location', 'ISSUING_OFFICE', 'DISPLAY_VALUE'))ISSUE_OFFICE_NAME
from pon_forms_instances pfi, pon_forms_sections psi ,
pon_auction_headers pah, pon_auc_doctypes padoc
where pfi.form_id = psi.form_id 
and pfi.entity_code = 'PON_AUCTION_HEADERS_ALL'
and pah.auction_header_id = pfi.entity_pk1
and pah.doctype_id = padoc.doctype_id
and padoc.internal_name = 'SOLICITATION'
and (pah.amendment_flag = 'Y' 
or pah.amendment_number = 0)
and (
(psi.form_code = 'FED_DOC_UPLOAD' 
and pah.auction_status not in ('DRAFT', 'CANCELLED')
and pah.open_bidding_date < sysdate
and pfi.fbo_date_sent is null)
or (psi.form_code in ('FED_PRESOL', 'FED_MOD_PRESOL')
and pah.auction_status not in ('CANCELLED')
and ((nvl(pah.open_bidding_date, sysdate) - nvl(pfi.fbo_date_sent, sysdate))) < 15)
or (pah.open_bidding_date < sysdate
and pah.auction_status not in ('DRAFT', 'CANCELLED')
and psi.form_code in ('FED_PRESOL', 'FED_MOD_PRESOL', 'FED_COMB_SOL', 'FED_AMD_COMB_SOL')
and pfi.fbo_date_sent is null
and exists((SELECT 1 
           FROM pon_auction_item_prices PAI 
           WHERE PAI.AUCTION_HEADER_ID = pah.AUCTION_HEADER_ID
           GROUP BY pai.auction_header_id
           HAVING SUM(NVL(PAI.CLM_AMOUNT, 0)) > 25000)))
)
and &doc_iss_off_where
and &doc_date_where
union all
select distinct
pah.document_number,
decode(pah.amendment_flag, 'Y',
       PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
       'addresses', NULL, 'location', 'AMD_ISSUING_OFFICE', 'INTERNAL_VALUE'),
       PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
       'addresses', NULL, 'location', 'ISSUING_OFFICE', 'INTERNAL_VALUE')) ISSUE_OFFICE_ID,
decode(pah.amendment_flag, 'Y', null,pah.amendment_number) amendment_number,
pah.auction_header_id,       
PAH.PUBLISH_DATE, 
PAH.CREATION_DATE, 
PAH.OPEN_BIDDING_DATE,
PAH.CURRENCY_CODE,        
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pah.trading_partner_contact_id) BUYER_FULL_NAME,
pah.auction_status,
(PON_AUCTION_PKG.get_auction_status_display(pah.auction_header_id, pah.trading_partner_id)) DOC_STATUS_DESC,
(SELECT SUM (NVL(PAI.CLM_AMOUNT, 0)) FROM pon_auction_item_prices PAI 
WHERE PAI.AUCTION_HEADER_ID = PAH.AUCTION_HEADER_ID) ESTIMATED_AMOUNT,
 decode(pah.amendment_flag, 'Y',
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'contact','AMD_ISSUING_OFFICE', 'DISPLAY_VALUE'),
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'contact','ISSUING_OFFICE', 'DISPLAY_VALUE')) ISSUE_OFFICE_CONTACT,
         decode(pah.amendment_flag, 'Y',
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'addresscode', 'AMD_ISSUING_OFFICE', 'DISPLAY_VALUE'),
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'addresscode', 'ISSUING_OFFICE', 'DISPLAY_VALUE'))ISSUE_OFFICE_CODE,
         decode(pah.amendment_flag, 'Y',
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'location', 'AMD_ISSUING_OFFICE', 'DISPLAY_VALUE'),
         PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PAH.UDA_TEMPLATE_ID, NULL, PAH.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
         'addresses', NULL, 'location', 'ISSUING_OFFICE', 'DISPLAY_VALUE'))ISSUE_OFFICE_NAME
from pon_auction_headers pah, pon_auc_doctypes padoc
where pah.doctype_id = padoc.doctype_id
and padoc.internal_name = 'SOLICITATION'
and (pah.amendment_flag = 'Y' 
or pah.amendment_number = 0)
and pah.auction_status not in ('DRAFT', 'CANCELLED')
and pah.open_bidding_date < sysdate
and (not exists(select 1 
               from pon_forms_instances pfi, pon_forms_sections psi
               where pfi.entity_code = 'PON_AUCTION_HEADERS_ALL'
               and psi.form_code in ('FED_DOC_UPLOAD')
               and  pfi.entity_pk1 = pah.auction_header_id )
or (exists((SELECT 1 
           FROM pon_auction_item_prices PAI 
           WHERE PAI.AUCTION_HEADER_ID = pah.AUCTION_HEADER_ID
           GROUP BY pai.auction_header_id
           HAVING SUM(NVL(PAI.CLM_AMOUNT, 0)) > 25000))
and not exists(select 1 
               from pon_forms_instances pfi, pon_forms_sections psi
               where pfi.entity_code = 'PON_AUCTION_HEADERS_ALL'
               and psi.form_code in ('FED_PRESOL', 'FED_MOD_PRESOL', 'FED_COMB_SOL', 'FED_AMD_COMB_SOL')
               and  pfi.entity_pk1 = pah.auction_header_id )            
    ))
and &doc_iss_off_where
and &doc_date_where
order by  2, 1
Parameter Name SQL text Validation
Date To
 
Date
Date From
 
Date
Issue Office
 
LOV Oracle