PON Solicitation data template

Description
Categories: BI Publisher
Columns: Pah Auction Header Id, Auction Title, Auction Status, Auction Status Name, Auction Type, Contract Type, Trading Partner Contact Name, Trading Partner Contact Id, Trading Partner Name, Trading Partner Name Upper ...
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')