PON Solicitation data template

Description
Categories: BI Publisher, Procurement
Application: Sourcing
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')