PON FedBizOpps Compliance

Description

Application: Sourcing
Source: FedBizOpps Compliance
Short Name: PONXFEDOPPS_XML
DB package: PON_PONXFEDOPPS_XMLP_PKG

PON FedBizOpps Compliance and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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

Issue Office

LOV Oracle

Date From

Date

Date To

Date

Download

Related Blitz Reports

PON Solicitation data template ECC Sourcing, Negotiations, SQL2 ECC Sourcing, Negotiations, SQL1 ECC Sourcing, Negotiation Timeline PO IDV Modification PO IDV Data template ECC Project Procurement, Item Summary OKC CDRL Data Template ECC Procurement, Purchase Orders

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ