SELECT pah.auction_header_id pah_auction_header_id,
pah.auction_title,
pah.auction_status,
pah.auction_status_name,
pah.auction_type,
pah.contract_type,
pah.trading_partner_contact_name,
pah.trading_partner_contact_id,
pah.trading_partner_name,
pah.trading_partner_name_upper,
pah.bill_to_location_id pah_bill_to_location_id,
pah.ship_to_location_id pah_ship_to_location_id,
pah.open_auction_now_flag,
pah.publish_auction_now_flag,
pah.bid_visibility_code,
pah.bid_list_type,
pah.bid_frequency_code,
pah.bid_scope_code,
pah.auto_extend_flag,
pah.auto_extend_min_trigger_rank,
pah.auto_extend_number,
pah.auto_extend_enabled_flag,
pah.number_of_extensions,
pah.min_bid_decrement,
pah.price_driven_auction_flag,
pah.payment_terms_id,
pah.freight_terms_code,
pah.fob_code,
pah.carrier_code,
pon_printing_pkg.get_carrier_description(pah.org_id,pah.carrier_code) carrier_desc,
pah.currency_code,
pah.rate_type,
pah.rate_date,
pah.rate,
pah.note_to_bidders,
pah.attachment_flag,
pah.language_code,
pah.auto_extend_all_lines_flag,
pah.min_bid_increment,
pah.allow_other_bid_currency_flag,
pah.shipping_terms_code,
pah.shipping_terms,
pah.auto_extend_duration,
pah.proxy_bid_allowed_flag,
pah.publish_rates_to_bidders_flag,
pah.attributes_exist,
pah.order_number,
pah.event_title,
pah.sealed_auction_status,
pah.sealed_actual_unlock_date,
pah.sealed_actual_unseal_date,
pah.mode_of_transport,
pah.mode_of_transport_code,
pah.min_bid_change_type,
pah.full_quantity_bid_code,
pah.number_price_decimals,
pah.auto_extend_type_flag,
pah.auction_origination_code,
pah.multiple_rounds_flag,
pah.auction_header_id_orig_round,
pah.auction_header_id_prev_round,
pah.auction_round_number,
pah.manual_close_flag,
pah.manual_extend_flag,
pah.autoextend_changed_flag,
pah.doctype_id,
pah.approval_required_flag,
pah.max_response_iterations,
pah.payment_terms_neg_flag,
pah.mode_of_transport_neg_flag,
pah.contract_id,
pah.contract_version_num,
pah.show_bidder_notes,
pah.derive_type,
pah.bid_ranking,
pah.rank_indicator,
pah.show_bidder_scores,
pah.org_id pah_org_id,
pah.buyer_id,
pah.has_pe_for_all_items,
pah.has_price_elements,
pah.global_agreement_flag,
decode(:is_doc_amend , 'Y' , (select document_number from pon_auction_headers_all where auction_header_id = pah.auction_header_id_prev_amend),
PON_CLM_UTIL_PKG.GET_CLM_COMMERCIAL_DOC_NUMBER(pah.auction_header_id)) document_number,
pah.amendment_number pah_amendment_number,
pah.amendment_description,
pah.auction_header_id_orig_amend,
pah.auction_header_id_prev_amend auction_header_id_prev_amend,
pah.hdr_attr_enable_weights,
pah.hdr_attr_display_score,
pah.hdr_attr_maximum_score,
pah.attribute_line_number,
pah.conterms_exist_flag,
pah.award_mode,
pah.has_hdr_attr_flag,
nvl(pah.has_items_flag,'Y') has_items_flag,
decode(pah.staggered_closing_interval, null, 'N', 'Y') staggered_closing_enabled,
pah.staggered_closing_interval,
pah.abstract_details,
pah.approval_status,
pah.award_approval_flag,
pah.pf_type_allowed,
pah.supplier_view_type,
pah.progress_payment_type,
pah.advance_negotiable_flag,
pah.recoupment_negotiable_flag,
pah.progress_pymt_negotiable_flag,
pah.retainage_negotiable_flag,
pah.max_retainage_negotiable_flag,
pah.supplier_enterable_pymt_flag,
pah.project_id sourcing_project_id,
pah.bid_decrement_method,
pah.large_neg_enabled_flag pah_large_neg_enabled_flag,
pah.team_scoring_enabled_flag,
pah.has_scoring_teams_flag pah_has_scoring_teams_flag ,
NVL(pah.enforce_prevrnd_bid_price_flag, 'N') enforce_prevrnd_bid_price_flag,
nvl(pah.display_best_price_blind_flag,'N') display_best_price_blind_flag,
pah.neg_team_enabled_flag pah_neg_team_enabled_flag,
pah.price_element_enabled_flag,
pah.time_zone,
pah.po_agreed_amount,
pah.po_min_rel_amount,
decode(pah.contract_type,'STANDARD',decode(progress_payment_type,'NONE','N','Y'),'N') complex_services_enabled,
fl.meaning pon_bid_visibility_display,
ap.name payment_terms,
fl_freight_terms.meaning freight_terms,
fl_fob.meaning fob,
currency_tl.name currency_name,
flbr.meaning bid_ranking_display,
doctypes.internal_name,
fl_security.meaning security_level,
ps.display_name outcome,
nvl(gdct.description, gdct.user_conversion_type) rate_type_display,
fl_rank_ind.meaning rank_indicator_display,
fl_pf_type_allowed.meaning pf_type_allowed_display,
ns.style_name,
proj.segment1 sourcing_project_number,
buyer_phone.phone_number,
postyl.advances_flag,
postyl.retainage_flag,
postyl.progress_payment_flag,
postyl.contract_financing_flag,
nvl((Select pdsv.enabled_flag From po_doc_style_values pdsv Where pdsv.style_id = pah.po_style_id and pdsv.style_attribute_name = 'PAY_ITEM_TYPES' and pdsv.style_allowed_value = 'RATE'), 'N') rate_payments_allowed_flag,
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pah.trading_partner_contact_id) auctioneer_display_name,
pah.open_bidding_date,
pah.close_bidding_date,
pah.original_close_bidding_date,
to_char(pah.close_bidding_date, 'HH:MI') close_bidding_date_h,
to_char(pah.close_bidding_date, 'MM/DD/YYYY HH:MI') close_bidding_date_char,
pah.view_by_date,
pah.award_by_date,
pah.publish_date,
pah.close_date,
pah.cancel_date,
pon_auction_pkg.get_timezone_description(:p_client_time_zone,userenv('lang')) display_time_zone,
pah.po_start_date,
pah.po_end_date,
pah.first_line_close_date,
pah.rate_date rate_date_display,
:p_user_view_type as user_view_type,
decode(:p_trading_partner_id, null, :p_requested_supplier_name, :p_trading_partner_name) as user_trading_partner_name,
doctypes.doctype_id doctypes_doctype_id,
(pon_datatemplate_pkg.GET_UDA_HEADER_XML).getClobVal() uda_attr,
pon_datatemplate_pkg.get_addressxml addresses,
pon_datatemplate_pkg.get_contactxml contact_details,
pah.qty_price_tiers_enabled_flag,
pah.two_part_flag,
pah.technical_lock_status,
pah.technical_evaluation_status,
pah.technical_actual_unlock_date,
pah.technical_actual_unseal_date,
pah.technical_unlock_tp_contact_id,
pah.technical_unseal_tp_contact_id,
pah.emd_enable_flag,
pah.emd_amount,
pah.emd_due_date,
pah.emd_type,
pah.emd_guarantee_expiry_date,
pah.emd_additional_information,
pah.post_emd_to_finance,
pah.revision,
pah.amendment_flag,
pah.cancel_amendment,
pah.effective_date,
pah.signed_date,
pah.uda_template_id,
pah.uda_template_date,
pah.solicitation_type,
pah.no_of_copies,
pah.cd_generated_date,
pah.cd_editbyuser_flag,
pah.cd_generated_flag,
pah.amendupdated_aftercdgenerated,
pah.concurrent_reqid,
nvl(fl_auction_status.meaning,pah.auction_status) auction_status_meaning,
decode((SELECT count( DISTINCT pbr.requisition_header_id) FROM pon_backing_requisitions pbr
WHERE pbr.auction_header_id = pah.auction_header_id ) , 0, NULL,
1 ,(SELECT DISTINCT prh.segment1 FROM pon_backing_requisitions pbr,po_requisition_headers_all prh
WHERE pbr.auction_header_id = pah.auction_header_id and
pbr.REQUISITION_HEADER_ID = prh.REQUISITION_HEADER_ID),
fnd_message.get_string('PO','PO_SEE_SCHEDULE') ) ref_pr_num_t,
0 as total_no_of_pages,
decode(pah.auction_status, 'DRAFT', decode(pah.approval_status, 'INPROCESS', NULL, 'APPROVED', NULL, decode(nvl(pah.revision, 0), 0, nvl(fl_auction_status.meaning, pah.auction_status), NULL) ), NULL) draft_text,
fnd_profile.value('OKC_AMENDMENT_SPECIFIC_SECTION') amend_clause_section,
decode(:is_doc_amend , 'Y', fnd_message.get_string('PO', 'PO_CONFORM_UNOFF_COPY'), null) conformed_copy_value,
pon_dt_custom_pkg.is_far_clause_present(:p_auction_header_id) sf1449_far_clause,
pon_dt_custom_pkg.is_addenda_attached(:p_auction_header_id) sf1449_addenda_attach,
pon_dt_custom_pkg.is_doc_award_or_notice(:p_auction_header_id) sf1442_awd_or_notice,
pon_dt_custom_pkg.is_guarantee_reqd(:p_auction_header_id) sf1442_gua_reqd,
pon_dt_custom_pkg.get_calendar_days(:p_auction_header_id) sf1442_cal_days,
decode( nvl(pah.document_format, 'NULL'), 'COM', fnd_message.get_string('PON', 'PON_SF33_COM_DEPO_LOC'),
'CSI', fnd_message.get_string('PON', 'PON_SF33_CSI_DEPO_LOC'),
'UCF', fnd_message.get_string('PON', 'PON_SF33_UCF_DEPO_LOC'), '') fp_dep_loc_section,
pon_datatemplate_pkg.get_fpfobcodedes() fp_fob_lookup_code_d,
nvl2(po_uda_pub.get_single_attr_value(pah.uda_template_id, NULL, :p_auction_header_id , NULL, NULL, NULL, NULL, NULL,
'BU_PRI_PRJ_INF', NULL, 'DPAS_RATING','DISPLAY_VALUE'), 'Y' ,'N') dpas_rated,
decode((po_uda_pub.get_single_attr_value(pah.uda_template_id, NULL, :p_auction_header_id, NULL, NULL, NULL, NULL, NULL,
'SF1447_Informa', NULL, 'TYPE_PURCHASE', 'INTERNAL_VALUE')), 'SERVICES', 'Y', 'BOTH', 'Y', 'N') FP_SF1447_purchase_services,
decode((po_uda_pub.get_single_attr_value(pah.uda_template_id, NULL, :p_auction_header_id, NULL, NULL, NULL, NULL, NULL,
'SF1447_Informa', NULL, 'TYPE_PURCHASE', 'INTERNAL_VALUE')), 'SUPPLIES', 'Y', 'BOTH', 'Y', 'N') FP_SF1447_purchase_supplies,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION' and sec.heading = 'Section C'
and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_c_clauses,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION' and sec.heading = 'Section H'
and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_h_clauses,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION' and sec.heading = 'Section I'
and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_i_clauses,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION' and sec.heading = 'Section K'
and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_k_clauses,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION' and sec.heading = 'Section L'
and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_l_clauses,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION' and sec.heading = 'Section M'
and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_m_clauses,
(select 'Y' from pon_auction_item_prices_ext_b ple, ego_attr_groups_v eag
where ple.auction_header_id = :p_auction_header_id and eag.attr_group_name = 'INSPECT_INFO' and eag.attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS'
and eag.attr_group_id = ple.attr_group_id and rownum = 1) has_inspection_info,
(select 'Y' from pon_auction_item_prices_ext_b ple, ego_attr_groups_v eag
where ple.auction_header_id = :p_auction_header_id and eag.attr_group_name = 'SHIP_INFO' and eag.attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS'
and eag.attr_group_id = ple.attr_group_id and rownum = 1) has_delivery_info,
(select 'Y' from pon_auction_headers_ext_b phe, ego_attr_groups_v eag
where phe.auction_header_id = :p_auction_header_id and eag.attr_group_name = 'addresses' and eag.attr_group_type = 'PON_AUC_HDRS_EXT_ATTRS'
and eag.attr_group_id = phe.attr_group_id and rownum = 1) has_contract_admin_data,
(PO_UDA_PUB.get_single_attr_value(pah.uda_template_id, NULL, pah.auction_header_id,
:p_auction_header_id, NULL, NULL, NULL, NULL, 'SET_ASIDE_INFO', NULL, 'NAICS', 'INTERNAL_VALUE' )) fp_naics,
(select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = :p_auction_header_id and sec.document_type = 'SOLICITATION'
and sec.heading = 'Packaging and Marking' and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_pack_and_marking_clauses
FROM pon_auction_headers_all pah,
fnd_lookups fl,
ap_terms ap,
fnd_lookup_values fl_freight_terms,
fnd_lookup_values fl_fob,
fnd_currencies_tl currency_tl,
fnd_lookups flbr,
pon_auc_doctypes doctypes,
fnd_lookups fl_security,
po_all_doc_style_lines ps,
gl_daily_conversion_types gdct,
fnd_lookups fl_rank_ind,
fnd_lookups fl_pf_type_allowed,
pon_negotiation_styles_vl ns,
hz_parties hp1,
fnd_product_groups fpg,
po_doc_style_headers postyl,
pa_projects_all proj,
fnd_user buyer_user,
per_phones buyer_phone,
hr_operating_units ou,
fnd_lookup_values fl_auction_status
WHERE pah.auction_header_id = :p_auction_header_id
and fl.lookup_type = 'PON_BID_VISIBILITY_CODE'
and fl.lookup_code = pah.bid_visibility_code
and ap.term_id(+) = pah.payment_terms_id
and fl_freight_terms.lookup_type(+) = 'FREIGHT TERMS'
and fl_freight_terms.lookup_code(+) = pah.freight_terms_code
and fl_freight_terms.view_application_id(+) = 201
and fl_freight_terms.security_group_id(+) = 0
and fl_freight_terms.language(+) = userenv('lang')
and fl_fob.lookup_type(+) = 'FOB'
and fl_fob.lookup_code(+) = pah.fob_code
and fl_fob.view_application_id(+) = 201
and fl_fob.security_group_id (+) = 0
and fl_fob.language(+) = userenv('lang')
and currency_tl.currency_code = pah.currency_code
and currency_tl.language = userenv('lang')
and flbr.lookup_type = 'PON_BID_RANKING_CODE'
and flbr.lookup_code = pah.bid_ranking
and doctypes.doctype_id = pah.doctype_id
and fl_security.lookup_type = 'PON_SECURITY_LEVEL_CODE'
and fl_security.lookup_code = pah.security_level_code
and ps.style_id(+) = pah.po_style_id
and ps.document_subtype(+) = pah.contract_type
and ps.language(+) = userenv('lang')
and gdct.conversion_type(+) = pah.rate_type
and fl_rank_ind.lookup_type = 'PON_RANK_INDICATOR_CODE'
and fl_rank_ind.lookup_code = pah.rank_indicator
and fl_pf_type_allowed.lookup_type = 'PON_PF_TYPE_ALLOWED'
and fl_pf_type_allowed.lookup_code = pah.pf_type_allowed
and ns.style_id = pah.style_id
and hp1.party_id = pah.trading_partner_contact_id
and ou.organization_id(+) = pah.org_id
and nvl(ou.date_from(+),sysdate-1) < sysdate
and nvl(ou.date_to(+),sysdate+1) > sysdate
and gdct.conversion_type(+) = pah.rate_type
and hp1.party_id = pah.trading_partner_contact_id
and postyl.style_id(+) = pah.po_style_id
and proj.project_id(+) = pah.project_id
and buyer_user.user_name = pah.trading_partner_contact_name
and buyer_phone.parent_table(+) = 'PER_ALL_PEOPLE_F'
and buyer_phone.parent_id(+) = buyer_user.employee_id
and buyer_phone.phone_type(+) = 'W1'
and nvl(buyer_phone.date_from(+), trunc(sysdate)) <= trunc(sysdate)
and nvl(buyer_phone.date_to(+), trunc(sysdate)) >= trunc(sysdate)
and fl_auction_status.lookup_type(+) = 'PON_AUCTION_STATUS'
and fl_auction_status.lookup_code(+) = pah.auction_status
and fl_auction_status.language(+) = userenv('lang') |