PON Solicitation data template
Description
Categories: BI Publisher
Application: Sourcing
Source:
Short Name: PON_SOLICIT
DB package: pon_datatemplate_pkg
Source:
Short Name: PON_SOLICIT
DB package: pon_datatemplate_pkg
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') |