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 |
