select e.exhibit_number exhibit_name, e.exhibit_description,
item.line_num_display, item.item_description, document_number clm_document_number,
null vendor_name,
PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(hdr.UDA_TEMPLATE_ID, NULL, hdr.AUCTION_HEADER_ID, NULL, NULL, NULL, NULL, NULL,
'addresses', NULL, 'location', 'COTR_OFFICE', 'INTERNAL_VALUE') COTR_OFFICE_CODE
from pon_auction_exhibit_details e, pon_auction_item_prices_all item, pon_auction_headers_all hdr
where e.auction_header_id = :bus_doc_id
and :bus_doc_type = 'SOLICITATION'
and e.exhibit_number = :exhibitCode
and e.is_CDRL='Y'
and hdr.auction_header_id = e.auction_header_id
and item.auction_header_id(+) = e.auction_header_id
and item.line_number(+) = e.associated_to_line
union all
select e.exhibit_name, e.exhibit_description,
item.line_num_display, item.item_description,hdr.clm_document_number,
(select vendor_name from po_vendors where vendor_id = hdr.vendor_id) vendor_name,
PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(hdr.UDA_TEMPLATE_ID, NULL, hdr.po_header_id, -1, NULL, NULL, NULL, NULL,
'addresses', NULL, 'location', 'COTR_OFFICE', 'INTERNAL_VALUE') COTR_OFFICE_CODE
from po_exhibit_details e, po_lines_all item, po_headers_all hdr
where e.po_header_id = :bus_doc_id
and :bus_doc_type in ('PA_BLANKET', 'PA_CONTRACT', 'PO_STANDARD')
and e.exhibit_name = :exhibitCode
and e.is_CDRL='Y'
and hdr.po_header_id = e.po_header_id
and item.po_header_id(+) = e.po_header_id
and item.po_line_id(+) = e.reference_line_id
union all
select e.exhibit_name, e.exhibit_description,
item.line_num_display, item.item_description, hdr.clm_document_number,
(select vendor_name from po_vendors where vendor_id = hdr.vendor_id) vendor_name,
PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(hdr.UDA_TEMPLATE_ID, NULL, draft.document_id, draft.draft_id, NULL, NULL, NULL, NULL,
'addresses', NULL, 'location', 'COTR_OFFICE', 'INTERNAL_VALUE') COTR_OFFICE_CODE
from po_exhibit_details_merge_v e, po_lines_merge_v item, po_headers_merge_v hdr, po_drafts draft
where draft.draft_id = :bus_doc_id
and draft.document_id = hdr.po_header_id
and draft.draft_id = hdr.draft_id
and :bus_doc_type in ('PA_BLANKET_MOD', 'PA_CONTRACT_MOD', 'PO_STANDARD_MOD')
and hdr.po_header_id = e.po_header_id
and draft.draft_id = e.draft_id
and e.exhibit_name = :exhibitCode
and e.is_CDRL='Y'
and item.po_header_id(+) = e.po_header_id
and item.po_line_id(+) = e.reference_line_id
and item.draft_id(+) = e.draft_id |
