[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select d.MESSAGE_SUFFIX into l_message_suffix
from pon_auc_doctypes d , pon_auction_headers_all pah
where pah.auction_header_id = p_auction_header_id
and pah.DOCTYPE_ID = d.DOCTYPE_ID;
SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,
decode(p_outcome, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)) *
nvl(pbip.bid_currency_price,0)) bid_total
INTO v_bid_total
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip
WHERE pbip.auction_header_id = p_auction_header_id AND
pbip.bid_number = p_bid_number AND
nvl(pbip.has_bid_flag, 'N') = 'Y' AND
pbip.auction_header_id = paip.auction_header_id AND
pbip.line_number = paip.line_number AND
paip.group_type in ('LOT', 'LINE', 'GROUP_LINE');
select
orgf.description carrier
into
l_carrier
from
financials_system_params_all fsp,
org_freight orgf
where
nvl(fsp.org_id, -9999)= nvl(p_org_id, -9999)
and orgf.organization_id = fsp.inventory_organization_id
and orgf.freight_code = p_carrier_code
and orgf.language = userenv('lang');
select application_id
into x_app_id
from fnd_application
where application_short_name = 'XDO' ;
Select
papf.email_address into
x_usermail
from
per_all_people_f papf, fnd_user
where
fnd_user.person_party_id = p_user_party_id
and fnd_user.employee_id = papf.person_id
and papf.effective_end_date = (SELECT MAX(per1.effective_end_date)
FROM per_all_people_f per1
WHERE papf.person_id = per1.person_id)
and rownum = 1;
select message_text
into l_printing_text
from fnd_new_messages
where message_name = 'PON_AUC_CONTACT_BUYER'
and language_code = USERENV('LANG')
and application_id = 396;
select has_scoring_teams_flag
into l_has_scoring_teams_flag
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT 'Y'
into l_is_scorer
FROM pon_neg_team_members
WHERE menu_name = 'PON_SOURCING_SCORENEG'
AND auction_header_id = p_auction_header_id
AND user_id = p_user_id;
SELECT pst.price_visible_flag
INTO l_is_price_visible
FROM pon_scoring_team_members pstm,
pon_scoring_teams pst
WHERE pstm.auction_header_id = p_auction_header_id
AND pstm.user_id = p_user_id
AND pst.auction_header_id = pstm.auction_header_id
AND pstm.team_id = pst.team_id;
SELECT
paha.Technical_Evaluation_Status
, pbh.SURROG_BID_FLAG
FROM
pon_auction_headers_all paha, pon_bid_headers pbh
WHERE paha.auction_header_id=pbh.auction_header_id
AND paha.auction_header_id=p_auction_header_id
AND pbh.bid_number = p_bid_number;
select
bid_currency_code,
number_price_decimals,
vendor_site_id,
vendor_id,
trading_partner_id,
rate,
technical_shortlist_flag
into
l_bid_currency_code,
l_bid_price_precision,
l_vendor_site_id,
l_vendor_id,
l_trading_partner_id,
l_bid_rate,
l_tech_shortlist_flag
from pon_bid_headers
where bid_number = p_bid_number;
select
trading_partner_id,
doctype_id,
currency_code,
number_price_decimals,
enforce_prevrnd_bid_price_flag,
auction_header_id_prev_round,
contract_type,
supplier_view_type,
pf_type_allowed,
nvl(two_part_flag, 'N'),
nvl(sealed_auction_status,' ')
into
l_neg_tp_id,
l_doc_type_id,
l_currency_code,
l_price_precision,
l_enfrc_prevrnd_bid_price_flag,
l_auction_header_id_prev_round,
l_contract_type,
l_supplier_view_type,
l_pf_type_allowed,
l_two_part_flag,
l_commercial_lock_status
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT doctypes.internal_name
INTO l_prev_rnd_doctype
FROM pon_auction_headers_all pah, pon_auc_doctypes doctypes
WHERE pah.auction_header_id = l_auction_header_id_prev_round
and pah.doctype_id = doctypes.doctype_id;
SELECT 'Y'
INTO l_is_section_restricted
FROM pon_neg_team_members pntm, pon_auction_headers_all pah
WHERE pah.auction_header_id = p_auction_header_id
AND pntm.menu_name = 'PON_SOURCING_SCORENEG'
AND pntm.auction_header_id = pah.auction_header_id
AND pntm.user_id = p_user_id
AND pah.has_scoring_teams_flag = 'Y'
AND pah.scoring_lock_date is null;
select hz_parties.address1, hz_parties.address2, hz_parties.address3, hz_parties.city, hz_parties.state, hz_parties.postal_code, hz_parties.country, nvl(entity_terr.territory_short_name,hz_parties.country)
,PON_LOCALE_PKG.get_party_display_name(pon_bid_headers.trading_partner_contact_id)
into l_supplier_address_line1,l_supplier_address_line2,l_supplier_address_line3,l_supplier_address_city,l_supplier_address_state,l_supplier_postal_code,l_supplier_country_code,l_supplier_country
,l_contact_details_name
from hz_parties, pon_bid_headers, fnd_territories_tl entity_terr
where pon_bid_headers.trading_partner_id = hz_parties.party_id
and pon_bid_headers.bid_number = p_bid_number
and entity_terr.territory_code(+) = hz_parties.country
and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
and entity_terr.language(+) = l_printing_language
and rownum = 1;
SELECT pvsa.address_line1,pvsa.address_line2,pvsa.address_line3,pvsa.city,pvsa.state,pvsa.zip,pvsa.country, nvl(entity_terr.territory_short_name,pvsa.country),
decode(pbp.trading_partner_contact_id, null, pbp.requested_supp_contact_name, PON_LOCALE_PKG.get_party_display_name(pbp.trading_partner_contact_id)) contact_name
into l_supplier_address_line1,l_supplier_address_line2,l_supplier_address_line3,l_supplier_address_city,l_supplier_address_state,l_supplier_postal_code,l_supplier_country_code,l_supplier_country,l_contact_details_name
FROM PO_VENDOR_SITES_ALL pvsa, pon_auction_headers_all pah, pon_bidding_parties pbp, fnd_territories_tl entity_terr
WHERE
pah.auction_header_id = p_auction_header_id
AND pvsa.org_id = pah.org_id
AND PURCHASING_SITE_FLAG = 'Y'
AND SYSDATE< NVL(INACTIVE_DATE, SYSDATE + 1)
AND vendor_id=l_vendor_id
AND nvl(rfq_only_site_flag, 'N')='N'
AND pvsa.vendor_site_id = l_vendor_site_id
AND pbp.auction_header_id = pah.auction_header_id
AND pbp.vendor_site_id = pvsa.vendor_site_id
and entity_terr.territory_code(+) = pvsa.country
and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
and entity_terr.language(+) = l_printing_language
and rownum = 1;
SELECT 'Y'
INTO l_neg_has_price_breaks
FROM pon_auction_shipments_all
WHERE auction_header_id = p_auction_header_id
AND ROWNUM = 1;
SELECT sequence
INTO l_supplier_sequence_number
FROM pon_bidding_parties
WHERE
auction_header_id = p_auction_header_id
AND ((trading_partner_id = l_trading_partner_id AND
vendor_site_id = p_vendor_site_id) OR
requested_supplier_id = p_requested_supplier_id);
SELECT pbh.bid_number
INTO l_prev_rnd_bid_number
FROM pon_bid_headers pbh
WHERE pbh.auction_header_id = l_auction_header_id_prev_round
AND pbh.bid_status = 'ACTIVE'
AND pbh.trading_partner_id = p_trading_partner_id
AND pbh.trading_partner_contact_id = p_trading_partner_contact_id
AND pbh.vendor_site_id = p_vendor_site_id;
SELECT
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,
nvl(pah.two_part_flag,'N') two_part_flag,
l_hide_comm_part hide_comm_part,
l_is_super_large_neg is_super_large_neg,
pah.proxy_bidding_enabled_flag,
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pah.trading_partner_contact_id) auctioneer_display_name,
pah.bill_to_location_id,
bill_territories_tl.territory_short_name bill_country_name,
loc_bill.location_code bill_address_name,
loc_bill.address_line_1 bill_address1,
loc_bill.address_line_2 bill_address2,
loc_bill.address_line_3 bill_address3,
loc_bill.town_or_city bill_city,
loc_bill.region_2 bill_state,
loc_bill.region_3 bill_province_or_region,
loc_bill.postal_code bill_zip_code,
loc_bill.postal_code bill_postal_code,
loc_bill.country bill_country,
loc_bill.region_1 bill_county,
pah.ship_to_location_id,
ship_territories_tl.territory_short_name ship_country_name,
loc_ship.location_code ship_address_name,
loc_ship.address_line_1 ship_address1,
loc_ship.address_line_2 ship_address2,
loc_ship.address_line_3 ship_address3,
loc_ship.town_or_city ship_city,
loc_ship.region_2 ship_state,
loc_ship.region_3 ship_province_or_region,
loc_ship.postal_code ship_zip_code,
loc_ship.postal_code ship_postal_code,
loc_ship.country ship_country,
loc_ship.region_1 ship_county,
entitytl.name entity,
entity_loc.style entity_address_style,
entity_loc.address_line_1 entity_address_line_1,
entity_loc.address_line_2 entity_address_line_2,
entity_loc.address_line_3 entity_address_line_3,
entity_loc.town_or_city entity_city,
entity_loc.postal_code entity_postal_code,
nvl(entity_terr.territory_short_name, entity_loc.country) entity_country,
entity_loc.country entity_country_code,
entity_loc.region_1 entity_region_1,
entity_loc.region_2 entity_region_2,
entity_loc.region_3 entity_region_3,
pon_oa_util_pkg.display_date_time(pah.open_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') open_bidding_date,
pon_oa_util_pkg.display_date_time(pah.close_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') close_bidding_date,
pon_oa_util_pkg.display_date_time(pah.original_close_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') original_close_bidding_date,
pon_oa_util_pkg.display_date_time(pah.view_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') view_by_date,
pon_oa_util_pkg.display_date_time(pah.award_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') award_by_date,
pon_oa_util_pkg.display_date_time(pah.publish_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') publish_date,
pon_oa_util_pkg.display_date_time(pah.close_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') close_date,
pon_oa_util_pkg.display_date_time(pah.cancel_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') cancel_date,
pah.time_zone,
pon_auction_pkg.get_timezone_description(p_client_time_zone,l_printing_language) display_time_zone,
pah.open_auction_now_flag,
pah.publish_auction_now_flag,
fl.meaning pon_bid_visibility_display,
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,
decode(pah.min_bid_change_type, 'PERCENTAGE', pon_printing_pkg.format_number(pah.min_bid_decrement), pon_printing_pkg.format_price(pah.min_bid_decrement*l_rate, l_price_mask, l_price_precision)) min_bid_decrement_disp,
pah.price_driven_auction_flag,
pah.payment_terms_id,
ap.name payment_terms,
pah.freight_terms_code,
fl_freight_terms.meaning freight_terms,
pah.fob_code,
fl_fob.meaning fob,
pah.carrier_code,
pah.currency_code,
l_currency_code l_currency_code,
pon_printing_pkg.get_carrier_description(pah.org_id,pah.carrier_code) carrier,
currency_tl.name currency_name,
-- bidpdf: whether this is for a bid pdf
l_is_bidpdf is_bidpdf,
l_price_visibility price_visibility,
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,
pon_oa_util_pkg.display_date(pah.po_start_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') po_start_date,
pon_oa_util_pkg.display_date(pah.po_end_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') po_end_date,
to_char(pah.po_agreed_amount*l_rate, l_amount_mask) po_agreed_amount,
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,
flbr.meaning bid_ranking_display,
pah.rank_indicator,
pah.show_bidder_scores,
pah.org_id,
pah.buyer_id,
pah.has_pe_for_all_items,
pah.has_price_elements,
to_char(pah.po_min_rel_amount*l_rate, l_amount_mask) po_min_rel_amount,
pah.global_agreement_flag,
pah.document_number,
pah.amendment_number ,
pah.amendment_description ,
pah.auction_header_id_orig_amend ,
pah.auction_header_id_prev_amend ,
pah.document_number ,
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,
pon_oa_util_pkg.display_date_time(pah.first_line_close_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') first_line_close_date,
doctypes.internal_name,
l_prev_rnd_doctype prev_rnd_internal_name,
po_setup_s1.get_services_enabled_flag() is_services_enabled,
l_contracts_installed as is_contracts_installed,
p_printing_warning_flag print_warning_flag,
l_cont_attach_doc_flag contract_attached_doc,
l_cont_nonmerge_flag contract_non_mergeable,
p_neg_printed_with_contracts neg_printed_with_contracts,
pon_printing_pkg.get_messages('PON_AUCTS_START_CUR_PRICE','CURRENCY_CODE', l_currency_code) start_price_msg,
pon_printing_pkg.get_messages('PON_AUCTS_TARGET_PRICE_CURR','CURRENCY_CODE', l_currency_code) target_price_msg,
pon_printing_pkg.get_messages('PON_AUC_CURRENT_PRICE', 'AUCTION_CURRENCY', l_currency_code) current_price_msg,
pon_printing_pkg.get_messages('PON_AUCTS_MIN_RELEASE_CURR','AUCTION_CURRENCY', l_currency_code) min_release_amt_msg,
pon_printing_pkg.get_messages('PON_AUCTS_AGREEMENT_AMOUNT_CUR','CURRENCY', l_currency_code) agreement_amount_msg,
pon_printing_pkg.get_messages('PON_MAX_RTNGE_AMT_WITH_CURR','AUCTION_CURRENCY', l_currency_code) max_retainage_amt_curr_msg,
pon_printing_pkg.get_messages('PON_ADVANCE_AMT_WITH_CURR','AUCTION_CURRENCY', l_currency_code) advance_amount_curr_msg,
pon_printing_pkg.get_messages('PON_ESTIMATED_TOTAL_AMT_CURR','CURRENCY_CODE', l_currency_code) estimated_amt_msg,
pon_printing_pkg.get_messages('PON_AUC_PRN_LEGAL_CONSEQUENCES','LEGAL_ENTITY_NAME',entitytl.name) legal_consequences_msg,
pon_printing_pkg.get_messages('PON_AUC_INTERVAL_MIN','MINUTES',pah.staggered_closing_interval) stagger_auc_interval_min,
-- two-part project messages
l_two_part_general_msg two_part_general_info_msg,
l_two_part_tech_msg two_part_technical_msg,
l_two_part_comm_msg two_part_commercial_msg,
-- bidpdf: doc title and footer
decode(l_is_bidpdf, 'Y',
get_messages(pon_printing_pkg.get_document_message_name('PON_BID_PRN_PAGE_HEADING',doctypes.message_suffix),'DOCUMENT_NUMBER',pah.document_number,'BID_NUMBER',p_bid_number),
pon_printing_pkg.get_messages(pon_printing_pkg.get_document_message_name('PON_AUCTS_PRN_PAGE_HEADING',doctypes.message_suffix),'DOCUMENT_NUMBER',pah.document_number)
) page_heading_msg,
pbhs.bid_status,
-- bidpdf: document type
doctypes.doctype_group_name,
-- bidpdf: response status
fl_bid.meaning response_status,
-- bidpdf: Response Valid Until
decode(pbhs.bid_expiration_date, null, '', pon_oa_util_pkg.display_date(pbhs.bid_expiration_date, p_client_time_zone, p_server_time_zone, p_date_format,'N')) response_valid_until,
-- bidpdf: supplier address
l_supplier_address_line1 supplier_address_line1,
l_supplier_address_line2 supplier_address_line2,
l_supplier_address_line3 supplier_address_line3,
l_supplier_address_city supplier_address_city,
l_supplier_address_state supplier_address_state,
l_supplier_postal_code supplier_postal_code,
l_supplier_country_code supplier_country_code,
l_supplier_country supplier_address_country,
-- bidpdf: supplier site:
pbhs.vendor_site_code,
-- bidpdf: supplier contact name:
l_contact_details_name contact_details_name,
pbhs.bid_currency_code bid_currency_selected, --Response Currency
pbhs.bidders_bid_number reference_number, --Reference Number
pbhs.note_to_auction_owner note_to_buyer, --Note to Buyer
--bidpdf: Response Received Time value
pon_oa_util_pkg.display_date_time(pbhs.surrog_bid_receipt_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') bid_received_time,
pbhs.surrog_bid_flag surrog_bid_flag, --Surrogate Bid Flag
pon_printing_pkg.get_user_email(hp1.party_id) email,
pah.abstract_details,
fl_security.meaning security_level,
pah.approval_status,
ps.display_name outcome,
nvl(gdct.description, gdct.user_conversion_type) rate_type_display,
pon_oa_util_pkg.display_date(pah.rate_date,
p_client_time_zone,
p_server_time_zone,
p_date_format, 'N') rate_date_display,
pah.award_approval_flag,
fl_rank_ind.meaning rank_indicator_display,
pah.pf_type_allowed,
fl_pf_type_allowed.meaning pf_type_allowed_display,
pah.supplier_view_type,
nvl2(pah.source_doc_msg_app, nvl2(pah.source_doc_msg, fnd_message.get_string(pah.source_doc_msg_app, pah.source_doc_msg), null), null) source_doc_msg_text,
nvl2(pah.source_doc_msg_app, nvl2(pah.source_doc_line_msg, fnd_message.get_string(pah.source_doc_msg_app, pah.source_doc_line_msg), null), null) source_doc_msg_line_text,
fpg.multi_org_flag,
p_user_view_type as user_view_type,
-- for bidpdf, the Company Name comes from pon_bid_headers.trading_partner_name
decode(l_is_bidpdf, 'Y', pbhs.trading_partner_name,decode(p_trading_partner_id, null, p_requested_supplier_name, p_trading_partner_name)) as user_trading_partner_name,
l_award_approval_enabled as award_approval_enabled,
ns.style_name,
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,
proj.segment1 sourcing_project_number,
DECODE(pah.contract_type, 'STANDARD', DECODE(progress_payment_type,'NONE','N','Y'),'N') complex_services_enabled,
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_auction_pkg.GetPAOUInstalled(pah.org_id) projects_installed_flag,
pon_auction_pkg.GetGMSOUInstalled(pah.org_id) grants_installed_flag,
pah.large_neg_enabled_flag,
pah.team_scoring_enabled_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.price_element_enabled_flag,
buyer_phone.phone_number,
buyer_fax.phone_number fax_number,
-- bidpdf: Proxy response decrement
decode(pah.min_bid_change_type, 'PERCENTAGE', pon_printing_pkg.format_number(pbhs.min_bid_change)||'%', pon_printing_pkg.format_price(pbhs.min_bid_change*l_rate, l_price_mask, l_price_precision)) min_bid_currency_change,
pon_printing_pkg.get_messages('PON_AUCTS_CUR_PROXY_DEC','CURRENCY_CODE',l_currency_code) supplier_proxy_dec_msg,
-- bidpdf: response total
decode(p_user_view_type, 'BUYER', to_char(pbhs.buyer_bid_total, l_amount_mask),
to_char(get_supplier_bid_total(pah.auction_header_id, pbhs.bid_number, pbhs.buyer_bid_total, pah.contract_type, doctypes.doctype_group_name,pbhs.bid_status), l_amount_mask)
) supplier_bid_total,
pon_printing_pkg.get_messages('PON_BID_CUR_TOTAL','CURRENCY_CODE', l_currency_code) supplier_response_total_msg,
pah.price_tiers_indicator,
cursor (
Select
paip.item_number|| nvl2(paip.item_revision, ', ', '') || paip.item_revision || jobs.name item,
paip.line_number,
paip.item_description,
pon_oa_util_pkg.display_date_time(paip.close_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') line_close_bidding_date,
paip.category_id,
paip.category_name,
paip.ip_category_id,
icx.category_name ip_category_name,
paip.uom_code,
units.unit_of_measure_tl,
pon_printing_pkg.format_number(paip.quantity) quantity,
-- bidpdf: Note to Buyer
pbip.note_to_auction_owner,
-- bidpdf: add bid price info
decode(p_user_view_type, 'BUYER', pon_printing_pkg.format_price(pbip.price, l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbip.bid_currency_price, l_price_mask, l_price_precision)) bid_currency_price,
pon_printing_pkg.format_number(pbip.quantity) bid_quantity,
pon_oa_util_pkg.display_date_time(pbip.promised_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') bid_promised_date,
--in MAS case, pbip.quantity is null, use paip.quantity instead
to_char(decode(p_user_view_type, 'BUYER',pbip.price, pbip.bid_currency_price)*decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,decode(pah.contract_type, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)), l_amount_mask) bid_amount,
--response/inquiry/server/ViewBidItemsVORowImpl.java:getBidTotalDisplay(): exchange_rate * PON_TRANSFORM_BIDDING_PKG.calculate_quote_amount (paip.auction_header_id, pbip.line_number, pbip.bid_number, 'TRANSFORMED', 12637, 12438, -1) bid_amount,
--bidpdf: Bid Minimum Release Amount
decode(p_user_view_type, 'BUYER', to_char(pbip.po_min_rel_amount, l_amount_mask), to_char(pbip.po_bid_min_rel_amount, l_amount_mask)) bid_min_rel_amount,
--bidpdf: MAS Score
pbip.total_weighted_score,
--bidpdf: Proxy Minimum
decode(p_user_view_type, 'BUYER', pon_printing_pkg.format_price(pbip.proxy_bid_limit_price, l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbip.bid_currency_limit_price, l_price_mask, l_price_precision)) bid_currency_limit_price,
paip.ship_to_location_id,
pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(pah.auction_header_id,
paip.line_number, paip.target_price*l_rate, paip.quantity, p_trading_partner_id,
p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),l_price_mask, l_price_precision)
target_price,
-- Start price comes from the earlier bid for a supplier if he had bid
-- on the earlier round for the line and if the control for enforcing
-- previous round start price is set. If he did not bid on the line or
-- if it is buyer or other supplier, then we fall back upon the
-- auction start price
--untransform_one_price
DECODE(l_is_supplier_bidpdf, 'Y',
pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, paip.line_number,
nvl(pbip.bid_start_price, paip.bid_start_price)*l_rate, paip.quantity,
p_trading_partner_id,
p_trading_partner_contact_id,
p_vendor_site_id,
p_requested_supplier_id),l_price_mask, l_price_precision),
DECODE(l_start_price_from_prev_rnd, 'N',
pon_printing_pkg.format_price(
pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, paip.line_number, paip.bid_start_price, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),
l_price_mask, l_price_precision),
pon_printing_pkg.format_price(
NVL(pon_auction_headers_pkg.apply_price_factors(p_auction_header_id ,l_prev_rnd_bid_number,paip.line_number, l_contract_type, l_supplier_view_type, l_pf_type_allowed, 'Y'),paip.bid_start_price),
l_price_mask, l_price_precision)
)
) bid_start_price,
paip.note_to_bidders,
paip.display_target_price_flag,
paip.type,
to_char(paip.po_min_rel_amount*l_rate, l_amount_mask) po_min_rel_amount,
paip.unit_of_measure,
paip.has_attributes_flag,
paip.org_id,
paip.has_price_elements_flag,
paip.line_type_id,
paip.order_type_lookup_code,
paip.item_revision,
paip.item_id,
paip.item_number,
paip.price_break_type,
paip.price_break_neg_flag,
paip.has_shipments_flag,
paip.price_disabled_flag,
paip.quantity_disabled_flag,
paip.disp_line_number,
paip.is_quantity_scored,
paip.is_need_by_date_scored,
paip.job_id,
paip.additional_job_details,
to_char(paip.po_agreed_amount*l_rate, l_amount_mask) po_agreed_amount,
paip.has_price_differentials_flag,
paip.price_diff_shipment_number,
paip.differential_response_type,
paip.purchase_basis,
pon_auction_pkg.getNeedByDatesToPrint(paip.auction_header_id,paip.line_number,p_date_format) as need_by_dates_to_print,
paip.document_disp_line_number,
paip.group_type,
decode(paip.parent_line_number, null,to_char(null),(select paip2.item_description from pon_auction_item_prices_all paip2 where paip2.auction_header_id = paip.auction_header_id and paip2.line_number = paip.parent_line_number)) parent_line_description,
tl.territory_short_name country_name,
hl.location_code address_name,
hl.address_line_1 address1,
hl.address_line_2 address2,
hl.address_line_3 address3,
hl.town_or_city city,
hl.region_2 state,
hl.region_3 province_or_region,
hl.postal_code zip_code,
hl.postal_code postal_code,
hl.country country,
hl.region_1 county,
paip.requisition_number,
paip.line_origination_code,
nvl2(paip.source_doc_number, paip.source_doc_number || nvl2(paip.source_line_number, ' / ' || paip.source_line_number, null), null) source_doc_line_display,
lt.line_type,
pon_printing_pkg.format_price(paip.current_price, l_price_mask, l_price_precision) current_price,
pon_printing_pkg.format_price(paip.unit_target_price, l_price_mask, l_price_precision) unit_target_price,
paip.unit_display_target_flag
,paip.has_payments_flag
,to_char(paip.advance_amount*l_rate, l_amount_mask) advance_amount
,decode(p_user_view_type, 'BUYER', to_char(pbip.advance_amount, l_amount_mask), to_char(pbip.bid_curr_advance_amount, l_amount_mask)) bid_advance_amount
--bidpdf:remove "," after paip.recoupment_rate_percent and paip.progress_pymt_rate_percent
,paip.recoupment_rate_percent recoupment_rate_percent
,pbip.recoupment_rate_percent bid_recoupment_rate_percent
,paip.progress_pymt_rate_percent progress_pymt_rate_percent
,pbip.progress_pymt_rate_percent bid_progress_pymt_rate_percent
,paip.retainage_rate_percent retainage_rate_percent
,pbip.retainage_rate_percent bid_retainage_rate_percent
,to_char(paip.max_retainage_amount*l_rate, l_amount_mask) max_retainage_amount
,decode(p_user_view_type, 'BUYER', to_char(pbip.max_retainage_amount, l_amount_mask), to_char(pbip.bid_curr_max_retainage_amt, l_amount_mask)) bid_curr_max_retainage_amt
,paip.project_id project_id
,proj.segment1 project_number
,paip.project_task_id project_task_id
,task.task_number project_task_number
,paip.project_award_id project_award_id
,awrd.award_number project_award_number
,paip.project_expenditure_type project_expenditure_type
,paip.project_exp_organization_id project_exp_organization_id
,hrorg.name project_exp_organization_name
,pon_oa_util_pkg.display_date(paip.project_expenditure_item_date, p_client_time_zone,
p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
,NVL2(paip.work_approver_user_id, (SELECT per.full_name
FROM per_all_people_f per
WHERE per.person_id = fuser.employee_id
AND per.effective_end_date =
(SELECT MAX(per1.effective_end_date)
FROM per_all_people_f per1
WHERE per.person_id = per1.person_id)
), NULL) work_approver_name
,paip.has_quantity_tiers negline_has_quantity_tiers
,pbip.has_quantity_tiers bidline_has_quantity_tiers
from
pon_auction_item_prices_all paip ,
hr_locations_all hl,
fnd_territories_tl tl,
per_jobs_vl jobs,
icx_cat_categories_v icx,
mtl_units_of_measure_tl units,
po_line_types_tl lt
,pa_projects_all proj
,pa_tasks task
,gms_awards_all awrd
,hr_all_organization_units hrorg
,fnd_user fuser
,pon_bid_item_prices pbip
where
paip.auction_header_id = pah.auction_header_id
and pbip.auction_header_id(+) = paip.auction_header_id
and pbip.bid_number(+) = p_bid_number
and pbip.line_number(+)=paip.line_number
and hl.location_id(+) = paip.ship_to_location_id
and tl.territory_code(+) = hl.country
and tl.language(+) = l_printing_language
and hl.ship_to_site_flag(+)='Y'
and sysdate < nvl(hl.inactive_date(+), sysdate + 1)
and paip.uom_code = units.uom_code(+)
and units.language(+) = l_printing_language
and jobs.job_id(+) = paip.job_id
and paip.ip_category_id = icx.rt_category_id(+)
and icx.language(+) = l_printing_language
and lt.line_type_id(+) = paip.line_type_id
and lt.language(+) = l_printing_language
and nvl(hl.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
= nvl(hr_general.get_business_group_id, nvl(hl.business_group_id(+), -99))
and (l_is_buyer_negpdf = 'Y'
or
(not exists (select 'x'
from pon_bidding_parties bp
where bp.auction_header_id = paip.auction_header_id
and ((bp.trading_partner_id = l_trading_partner_id
and bp.vendor_site_id = p_vendor_site_id)
OR bp.requested_supplier_id = p_requested_supplier_id)
and bp.access_type = 'RESTRICTED')
or
nvl(paip.parent_line_number, paip.line_number) not in (
select line_number
from pon_party_line_exclusions pple
where pple.auction_header_id = paip.auction_header_id
and ((pple.trading_partner_id = l_trading_partner_id
and pple.vendor_site_id = p_vendor_site_id)
OR pple.requested_supplier_id = p_requested_supplier_id))))
AND paip.project_id = proj.project_id(+)
AND paip.project_task_id = task.task_id(+)
AND paip.project_award_id = awrd.award_id(+)
AND paip.project_exp_organization_id = hrorg.organization_id(+)
AND paip.work_approver_user_id = fuser.user_id(+)
order by paip.disp_line_number) as LINES,
cursor (
SELECT
TM.AUCTION_HEADER_ID,
P.full_name,
S.NAME position_name,
tm.approver_flag,
tm.menu_name,
flkp.meaning member_access_type,
tm.task_name,
pon_oa_util_pkg.display_date(tm.target_date,
p_client_time_zone,
p_server_time_zone,
p_date_format, 'N') target_date
FROM
PON_NEG_TEAM_MEMBERS TM,
FND_USER U,
PER_ALL_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PER_ALL_POSITIONS S,
FND_LOOKUPS flkp
WHERE
TM.AUCTION_HEADER_ID = pah.auction_header_id
AND l_is_buyer_negpdf = 'Y'
AND pah.neg_team_enabled_flag = 'Y'
AND TM.LAST_AMENDMENT_UPDATE <= pah.amendment_number
AND tm.menu_name = flkp.lookup_code
AND flkp.lookup_type = 'PON_NEG_TEAM_MEMBER_ACCESS'
AND U.USER_ID = TM.USER_ID
AND U.EMPLOYEE_ID = P.PERSON_ID
AND P.EFFECTIVE_END_DATE =
(SELECT MAX(PP.EFFECTIVE_END_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = U.EMPLOYEE_ID)
AND A.PERSON_ID = P.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND ((A.ASSIGNMENT_TYPE = 'E' AND P.CURRENT_EMPLOYEE_FLAG = 'Y')
OR
(A.ASSIGNMENT_TYPE = 'C' AND P.CURRENT_NPW_FLAG = 'Y'))
AND A.EFFECTIVE_END_DATE =
(SELECT MAX(AA.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F AA
WHERE AA.PRIMARY_FLAG = 'Y'
AND AA.ASSIGNMENT_TYPE in ('E', 'C')
AND AA.PERSON_ID = P.PERSON_ID)
AND A.POSITION_ID = S.POSITION_ID(+)
AND TM.AUCTION_HEADER_ID = pah.auction_header_id
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
ORDER BY P.FULL_NAME, U.USER_NAME) as COLLABORATION_TEAM,
CURSOR (
SELECT sctm.team_id
,sctm.team_name
,sctm.price_visible_flag
,sctm.instruction_text
FROM pon_scoring_teams sctm
WHERE sctm.auction_header_id = pah.auction_header_id
AND pah.has_scoring_teams_flag = 'Y' -- teams present only if flag present
ORDER BY sctm.team_name
) AS SCORING_TEAMS,
CURSOR (
SELECT DISTINCT -- Distinct added because sometimes an employee may have
-- multipler user ids resulting in more rows being returned
stmem.team_id
,stmem.user_id
,per.full_name member_name
FROM pon_scoring_team_members stmem
,fnd_user fuser
,per_all_people_f per
WHERE stmem.auction_header_id = pah.auction_header_id
AND stmem.user_id = fuser.user_id
AND fuser.employee_id = per.person_id
AND pah.has_scoring_teams_flag = 'Y' -- members present only if teams present
AND per.effective_end_date = (select max(pp.effective_end_date) from per_all_people_f pp where pp.person_id = per.person_id)
) AS SCORING_TEAM_MEMBERS,
CURSOR (
SELECT
team_sections.section_id
,sections.section_name
,team_sections.auction_header_id
,team_sections.team_id
FROM pon_scoring_team_sections team_sections
,pon_auction_sections sections
WHERE team_sections.auction_header_id = pah.auction_header_id
AND sections.section_id = team_sections.section_id
AND sections.auction_header_id = team_sections.auction_header_id
AND pah.has_scoring_teams_flag = 'Y' -- sections present only if teams present
) AS SCORING_TEAM_SECTIONS,
CURSOR (
SELECT
forms_tl.form_name,
forms.form_version,
forms.form_id,
forms.form_code
FROM
pon_forms_instances form_instances,
pon_forms_sections forms,
pon_forms_sections_tl forms_tl
WHERE
form_instances.entity_code = 'PON_AUCTION_HEADERS_ALL'
AND form_instances.entity_pk1 = TO_CHAR(pah.auction_header_id)
AND l_is_buyer_negpdf = 'Y'
AND forms_tl.language = l_printing_language
AND form_instances.form_id = forms.form_id
AND forms.form_id = forms_tl.form_id
ORDER BY form_name) AS ABSTRACT_AND_FORMS,
cursor (
select
pacr.bid_currency_code,
ftl.name bid_currency_name,
pacr.number_price_decimals,
pon_printing_pkg.get_display_rate(pacr.rate_dsp,pah.rate_type,pah.rate_date,pah.currency_code,bid_currency_code) display_rate
from
pon_auction_currency_rates pacr ,
fnd_currencies_tl ftl
where
pacr.auction_header_id = pah.auction_header_id
and ftl.currency_code = pacr.bid_currency_code
and ftl.language = l_printing_language)
as CURRENCY,
CURSOR(
SELECT
pbp.bid_currency_code,
ftl.name bid_currency_name,
pbp.number_price_decimals,
nvl2(pbp.rate_dsp, pon_printing_pkg.format_number(pbp.rate_dsp), null) as display_rate
FROM
pon_bidding_parties pbp,
fnd_currencies_tl ftl
WHERE
pbp.auction_header_id = pah.auction_header_id
AND (l_is_buyer_negpdf = 'N')
AND ftl.currency_code = pbp.bid_currency_code
AND ftl.language = l_printing_language
AND ((pbp.trading_partner_id = l_trading_partner_id
AND pbp.vendor_site_id = p_vendor_site_id)
OR pbp.requested_supplier_id = p_requested_supplier_id)
ORDER BY sequence asc) AS INVITED_SUPPLIER_CURRENCY,
cursor (
select sum(nvl(paa.weight,0)) weight,
sum(nvl(paa.attr_max_score,0)) score,
pass.section_name,
nvl(pass.two_part_section_type,'') two_part_section_type,
cursor (select
pa.auction_header_id,
pa.line_number,
pa.attribute_name as header_attribute_name,
pa.description,
pa.datatype,
pa.mandatory_flag,
print_attribute_target_value(pa.display_target_flag, pa.value, pa.datatype,10, p_client_time_zone, p_server_time_zone, p_date_format, p_user_view_type) value,
pa.display_prompt,
pa.display_target_flag,
pa.display_only_flag,
pa.sequence_number,
nvl(pa.weight,0) weight,
pa.scoring_type,
pa.attr_level,
pa.attr_group,
pa.attr_max_score,
pa.internal_attr_flag,
pa.attr_group_seq_number,
pa.attr_disp_seq_number,
pa.knockout_score,
pa.scoring_method,
print_attribute_response_value(pbav.value, pbav.datatype, p_client_time_zone, p_server_time_zone, p_date_format, pbav.sequence_number) attribute_bid_value,
pbav.score attribute_bid_score,
cursor( select
pas.auction_header_id,
pas.line_number,
pas.attribute_sequence_number,
pas.value,
pas.from_range,
pas.to_range,
pas.score,
pas.sequence_number,
pon_printing_pkg.get_acceptable_value(pah.HDR_ATTR_DISPLAY_SCORE,pas.attribute_sequence_number,pa.datatype,pas.from_range,pas.to_range,pas.value,pas.score, p_client_time_zone, p_server_time_zone, p_date_format, l_is_buyer_negpdf) display_score
from
pon_attribute_scores pas
where
pas.auction_header_id = pa.auction_header_id
and pas.line_number = -1
and pas.attribute_sequence_number = pa.sequence_number
order by pas.attribute_sequence_number,pas.sequence_number
) as HEADER_ATTRIBUTE_SCORES
from
pon_auction_attributes pa, pon_bid_attribute_values pbav
where
pa.auction_header_id = paa.auction_header_id
--bidpdf: add bid values for attributes from table pon_bid_attribute_values
-- The table has index on bid_number, line_number, sequence_number
and pbav.auction_header_id(+) = pa.auction_header_id
and pbav.bid_number(+) = p_bid_number
and pbav.line_number(+) = pa.line_number
and pbav.sequence_number(+) = pa.sequence_number
and pa.section_name = paa.section_name
and pa.line_number = -1
and pa.attr_level='HEADER'
and (l_is_buyer_negpdf = 'Y' or pa.internal_attr_flag <> 'Y')
order by pa.attr_disp_seq_number) as HEADER_ATTRIBUTES_DETAILS
from
pon_auction_attributes paa,pon_auction_sections pass
where
pass.auction_header_id = pah.auction_header_id
and pass.auction_header_id = paa.auction_header_id(+)
and pass.section_name = paa.section_name(+)
and paa.attr_level(+)='HEADER'
and paa.line_number(+) = -1
and (l_is_buyer_negpdf = 'Y' or paa.internal_attr_flag <> 'Y')
and (l_is_section_restricted = 'N'
or l_is_section_restricted = 'Y'
and paa.attr_group_seq_number in (
select pas.attr_group_seq_number
from pon_scoring_team_members pstm, pon_scoring_team_sections psts, pon_auction_sections pas
where pstm.auction_header_id = pah.auction_header_id
and pstm.user_id = p_user_id
and psts.auction_header_id = pstm.auction_header_id
and psts.team_id = pstm.team_id
and psts.section_id = pas.section_id
and psts.auction_header_id = pas.auction_header_id
)
)
and ((l_hide_comm_part = 'Y' and pass.two_part_section_type = 'TECHNICAL') or l_hide_comm_part <> 'Y')
group by(pass.section_name,paa.auction_header_id,paa.section_name,pass.auction_header_id,pass.attr_group_seq_number, two_part_section_type)
order by pass.attr_group_seq_number) as GROUP_HEADER_ATTRIBUTES ,
CURSOR(
SELECT
decode(pbp.trading_partner_id, null, pbp.requested_supplier_name, pbp.trading_partner_name) trading_partner_name,
pbp.vendor_site_code,
decode(pbp.trading_partner_contact_id, null, pbp.requested_supp_contact_name, PON_LOCALE_PKG.get_party_display_name(pbp.trading_partner_contact_id)) contact_name,
pbp.additional_contact_email,
pbp.bid_currency_code,
pbp.rate_dsp,
nvl2(pbp.rate_dsp, pon_printing_pkg.format_number(pbp.rate_dsp), null) as rate_dsp_display,
pbp.number_price_decimals,
pbp.access_type,
pbp.auction_header_id,
pbp.trading_partner_id,
pbp.trading_partner_contact_id,
pbp.sequence
FROM pon_bidding_parties pbp
WHERE
pbp.auction_header_id = pah.auction_header_id
AND l_is_buyer_negpdf = 'Y'
ORDER BY sequence asc) AS INVITED_SUPPLIERS,
cursor (
select
attrGrpFlv.meaning,
pal.auction_header_id,
pal.line_number,
pal.attribute_name,
pal.description,
pal.datatype,
pal.mandatory_flag,
print_attribute_target_value(pal.display_target_flag, pal.value, pal.datatype,pal.sequence_number, p_client_time_zone, p_server_time_zone, p_date_format, p_user_view_type) value,
pal.display_prompt,
pal.display_target_flag,
pal.display_only_flag,
pal.sequence_number,
pal.weight,
pal.scoring_type,
NVL(pal.attr_level,'LINE') attr_level,
NVL(pal.attr_group,'GENERAL') attr_group,
pal.attr_max_score,
pal.internal_attr_flag,
NVL(pal.attr_group_seq_number,10) attr_group_seq_number,
pal.attr_disp_seq_number,
-- bidpdf: add attribute response value
print_attribute_response_value(pbav.value, pbav.datatype, p_client_time_zone, p_server_time_zone, p_date_format, pbav.sequence_number) attr_bid_value
from
pon_auction_attributes pal,
pon_bid_attribute_values pbav,
fnd_lookups attrGrpFlv
where
pal.auction_header_id = pah.auction_header_id
and pbav.auction_header_id(+) = pal.auction_header_id
and pbav.bid_number(+) = pbhs.bid_number
and pbav.line_number(+) = pal.line_number
and pbav.sequence_number(+) = pal.sequence_number
and NVL(pal.attr_group,'GENERAL') = attrGrpFlv.lookup_code
and NVL(pal.attr_level,'LINE')='LINE'
and NVL(pal.internal_attr_flag,'N') <> 'Y'
and attrGrpFlv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS'
and attrGrpFlv.enabled_flag = 'Y'
and nvl(attrGrpFlv.start_date_active,sysdate) <= sysdate
and nvl(attrGrpFlv.end_date_active,sysdate) > sysdate-1
order by pal.line_number,NVL(pal.attr_group_seq_number,10),pal.attr_disp_seq_number) as LINE_ATTRIBUTES,
cursor( select
pas.auction_header_id,
pas.line_number,
pas.attribute_sequence_number,
pas.value,
pas.from_range,
pas.to_range,
pas.score,
pas.sequence_number,
get_acceptable_value(pah.show_bidder_scores,pas.attribute_sequence_number,paa.datatype,pas.from_range,pas.to_range,pas.value,pas.score, p_client_time_zone, p_server_time_zone, p_date_format, l_is_buyer_negpdf) display_score
from
pon_attribute_scores pas,
pon_auction_attributes paa
where
pas.auction_header_id = pah.auction_header_id
and paa.auction_header_id = pah.auction_header_id
and paa.line_number = pas.line_number
and paa.sequence_number = pas.attribute_sequence_number
and NVL(paa.attr_level,'LINE')='LINE'
order by pas.line_number,pas.attribute_sequence_number,pas.sequence_number
) as LINE_ATTRIBUTE_SCORES,
CURSOR (
SELECT
pe.auction_header_id,
pe.line_number,
pet.name,
pe.pricing_basis,
flv.meaning pricing_basis_display,
pe.value value,
--only in supplier bid pdf, the target value is in supplier currency and number format
--in neg pdf and buyer side bid pdf, the target value is in buyer currency
nvl2(pe.value, decode(pe.pricing_basis, 'PER_UNIT', pon_printing_pkg.format_price(pe.value*l_rate, l_price_mask, l_price_precision) ||' ('||l_currency_code||')',
'FIXED_AMOUNT', to_char(pe.value*l_rate, l_amount_mask) ||' ('||l_currency_code||')',
pon_printing_pkg.format_number(pe.value)),
null) target_value_display,
-- bidpdf: response value
nvl2(pbpe.bid_currency_value,
decode(pe.pricing_basis,
'PER_UNIT', decode(p_user_view_type,
'BUYER', pon_printing_pkg.format_price(pbpe.auction_currency_value, l_price_mask, l_price_precision)||' ('||l_currency_code||')',
pon_printing_pkg.format_price(pbpe.bid_currency_value, l_price_mask, l_price_precision)||' ('||l_currency_code||')'),
'FIXED_AMOUNT', decode(p_user_view_type, 'BUYER',to_char(pbpe.auction_currency_value, l_amount_mask)||' ('||l_currency_code||')',to_char(pbpe.bid_currency_value, l_amount_mask)||' ('||l_currency_code||')'),
pon_printing_pkg.format_number(pbpe.bid_currency_value)),
null) bid_value_display,
pe.price_element_type_id,
pe.sequence_number,
pe.display_target_flag,
pet.description,
pe.pf_type,
pe.display_to_suppliers_flag,
flv2.meaning pf_type_display,
--only in supplier bid pdf, the buyer response value is in supplier currency and number format
--in neg pdf and buyer side bid pdf, the buyer response value is in buyer currency
nvl2(pf_values.value, decode(pe.pf_type, 'BUYER', decode(pe.pricing_basis, 'PER_UNIT', pon_printing_pkg.format_price(pf_values.value*l_rate, l_price_mask, l_price_precision)||' ('||l_currency_code||')',
'FIXED_AMOUNT', to_char(pf_values.value*l_rate, l_amount_mask)||' ('||l_currency_code||')',
pon_printing_pkg.format_number(pf_values.value)),
null),
null) buyer_pf_value_display,
decode(pah.trading_partner_id,
p_trading_partner_id, 'Y',
decode(pe.pf_type,
'SUPPLIER', 'Y',
decode(pe.display_to_suppliers_flag,
'N', 'N',
PON_TRANSFORM_BIDDING_PKG.has_pf_values_defined(pe.auction_header_id, pe.line_number, pe.sequence_number, p_trading_partner_id, p_vendor_site_id, p_requested_supplier_id)))) can_view_pf_flag
FROM
pon_price_elements pe,
pon_price_element_types_tl pet,
pon_auction_item_prices_all itm,
fnd_lookup_values flv,
fnd_lookup_values flv2,
pon_pf_supplier_values pf_values,
-- bidpdf: add bid value for cost factor
pon_bid_price_elements pbpe
WHERE
pe.auction_header_id = pah.auction_header_id
AND pbpe.auction_header_id(+) = pe.auction_header_id
AND pbpe.bid_number(+) = pbhs.bid_number
AND pbpe.line_number(+) = pe.line_number
AND pbpe.price_element_type_id(+) = pe.price_element_type_id
AND itm.auction_header_id = pe.auction_header_id
AND itm.line_number = pe.line_number
AND pe.price_element_type_id = pet.price_element_type_id
AND pet.language = l_printing_language
AND flv.lookup_type = 'PON_PRICING_BASIS'
AND flv.language = l_printing_language
AND flv.lookup_code = pe.pricing_basis
AND flv.view_application_id = 0
AND flv.security_group_id = 0
AND flv2.lookup_type = 'PON_PRICE_FACTOR_TYPE'
AND flv2.language = l_printing_language
AND flv2.lookup_code = pe.pf_type
AND flv2.view_application_id = 0
AND flv2.security_group_id = 0
AND decode(pe.price_element_type_id, -10, itm.has_price_elements_flag, 'Y') = 'Y'
AND pf_values.auction_header_id(+) = pe.auction_header_id
AND pf_values.line_number(+) = pe.line_number
AND pf_values.pf_seq_number(+) = pe.sequence_number
AND pf_values.supplier_seq_number(+) = l_supplier_sequence_number
order by pe.line_number, pe.sequence_number asc) AS PRICE_FACTORS,
CURSOR (
SELECT
pet.name,
flv.meaning pricing_basis_display,
pet.description,
flv2.meaning pf_type_display
FROM
pon_price_element_types_tl pet,
fnd_lookup_values flv,
fnd_lookup_values flv2
WHERE
pet.language = l_printing_language
AND pet.price_element_type_id = -10
AND flv.lookup_type = 'PON_PRICING_BASIS'
AND flv.language = l_printing_language
AND flv.lookup_code = 'PER_UNIT'
AND flv.view_application_id = 0
AND flv.security_group_id = 0
AND flv2.lookup_type = 'PON_PRICE_FACTOR_TYPE'
AND flv2.language = l_printing_language
AND flv2.lookup_code = 'SUPPLIER'
AND flv2.view_application_id = 0
AND flv2.security_group_id = 0
) AS LINE_PRICE_PF_DETAILS,
CURSOR (
SELECT
pf_values.auction_header_id,
pf_values.line_number,
pf_values.pf_seq_number,
pf_values.supplier_seq_number,
pf_values.value,
pfs.price_element_type_id,
pfs.pricing_basis
FROM
pon_price_elements pfs,
pon_pf_supplier_values pf_values
WHERE
pf_values.auction_header_id = pah.auction_header_id
AND pah.large_neg_enabled_flag = 'N'
AND l_is_buyer_negpdf = 'Y'
AND pf_values.auction_header_id = pfs.auction_header_id
AND pf_values.line_number = pfs.line_number
AND pf_values.pf_seq_number = pfs.sequence_number
ORDER BY pf_values.supplier_seq_number, pf_values.line_number, pf_values.pf_seq_number) AS BUYER_PF_VALUES,
CURSOR (
SELECT DISTINCT
ppe.price_element_type_id,
ppe.pricing_basis,
ppett.name,
fl.meaning as pricing_basis_meaning
FROM
pon_price_elements ppe,
pon_price_element_types_tl ppett,
fnd_lookups fl
WHERE
ppe.auction_header_id = pah.auction_header_id
AND pah.large_neg_enabled_flag = 'N'
AND l_is_buyer_negpdf = 'Y'
AND ppe.pf_type = 'BUYER'
AND ppe.price_element_type_id = ppett.price_element_type_id
AND ppett.language = l_printing_language
AND ppe.pricing_basis = fl.lookup_code
AND fl.lookup_type = 'PON_PRICING_BASIS'
ORDER BY name, pricing_basis_meaning) as DISTINCT_BUYER_PFS,
cursor (
select
pon_large_neg_pf_values.supplier_seq_number,
priceelementtypesvl.name||'('||lookuptable.meaning||')' pf_name,
pon_large_neg_pf_values.value
from
pon_large_neg_pf_values pon_large_neg_pf_values,
pon_price_element_types_vl priceelementtypesvl,
fnd_lookups lookuptable
WHERE pon_large_neg_pf_values.auction_header_id = pah.auction_header_id
and pah.large_neg_enabled_flag = 'Y'
AND l_is_buyer_negpdf = 'Y'
AND priceelementtypesvl.price_element_type_id = pon_large_neg_pf_values.price_element_type_id
AND lookuptable.lookup_code = pon_large_neg_pf_values.pricing_basis
AND lookuptable.lookup_type = 'PON_PRICING_BASIS'
AND pon_large_neg_pf_values.value is not null
order by pon_large_neg_pf_values.supplier_seq_number,pf_name) as LARGE_NEG_BUYER_PF_VALUES,
CURSOR (
(
SELECT pbsm.auction_header_id,
pbsm.line_number,
pbsm.auction_shipment_number shipment_number,
pbsm.shipment_number bid_shipment_number,
pbsm.ship_to_organization_id,
mp.organization_code ship_to_organization,
pbsm.ship_to_location_id,
loc.location_code ship_to_location,
pon_printing_pkg.format_number(pbsm.quantity) quantity,
-- in case when supplier add new shipments, there's no target price
decode(pbsm.auction_shipment_number, null, null,
pon_printing_pkg.format_price(
pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),
l_price_mask, l_price_precision)
) price,
PON_OA_UTIL_PKG.DISPLAY_DATE(pbsm.effective_start_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_start_date,
PON_OA_UTIL_PKG.DISPLAY_DATE(pbsm.effective_end_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_end_date,
nvl2(pbsm.ship_to_location_id, loc.location_code, mp.organization_code) ship_to,
pbsm.has_price_differentials_flag,
pas.differential_response_type,
decode(p_user_view_type, 'BUYER',pon_printing_pkg.format_price(pbsm.price,l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbsm.bid_currency_price,l_price_mask, l_price_precision)) bid_currency_price,
pbsm.price_type,
pbsm.price_discount
FROM pon_auction_shipments_all pas,
pon_auction_item_prices_all paip,
hr_locations_all loc,
mtl_parameters mp,
-- bidpdf: add response price for price breaks
pon_bid_shipments pbsm
WHERE pbsm.bid_number = pbhs.bid_number
and pbsm.auction_header_id = pas.auction_header_id(+)
and pbsm.line_number = pas.line_number(+)
and pbsm.auction_shipment_number = pas.shipment_number(+)
AND l_neg_has_price_breaks = 'Y'
AND paip.auction_header_id = pbsm.auction_header_id
AND paip.line_number = pbsm.line_number
AND pbsm.shipment_type = 'PRICE BREAK'
AND mp.organization_id(+) = pbsm.ship_to_organization_id
AND loc.location_id(+) = pbsm.ship_to_location_id
and exists (select 1 from pon_bid_item_prices where bid_number=pbsm.bid_number and line_number=pbsm.line_number)
UNION ALL
SELECT pas.auction_header_id,
pas.line_number,
pas.shipment_number,
pas.shipment_number bid_shipment_number,
pas.ship_to_organization_id,
mp.organization_code ship_to_organization,
pas.ship_to_location_id,
loc.location_code ship_to_location,
pon_printing_pkg.format_number(pas.quantity) quantity,
pon_printing_pkg.format_price(
pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id,p_trading_partner_contact_id,p_vendor_site_id, p_requested_supplier_id),
l_price_mask,
l_price_precision
) price,
PON_OA_UTIL_PKG.DISPLAY_DATE(pas.effective_start_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_start_date,
PON_OA_UTIL_PKG.DISPLAY_DATE(pas.effective_end_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_end_date,
nvl2(pas.ship_to_location_id, loc.location_code, mp.organization_code) ship_to,
pas.has_price_differentials_flag,
pas.differential_response_type,
null bid_currency_price,
null price_type,
null price_discount
FROM pon_auction_shipments_all pas,
pon_auction_item_prices_all paip,
hr_locations_all loc,
mtl_parameters mp
WHERE pas.auction_header_id = pah.auction_header_id
AND l_neg_has_price_breaks = 'Y'
AND paip.auction_header_id = pas.auction_header_id
AND paip.line_number = pas.line_number
AND pas.shipment_type = 'PRICE BREAK'
AND mp.organization_id(+) = pas.ship_to_organization_id
AND loc.location_id(+) = pas.ship_to_location_id
and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number)
) ORDER BY line_number,bid_shipment_number asc
) as ITEM_PRICE_BREAKS,
CURSOR (
(
SELECT pbsm.auction_header_id,
pbsm.line_number,
pbsm.auction_shipment_number shipment_number,
pbsm.shipment_number bid_shipment_number,
pon_printing_pkg.format_number(pbsm.quantity) quantity,
pon_printing_pkg.format_number(pbsm.max_quantity) max_quantity,
-- in case when supplier add new shipments, there's no target price
nvl2(pbsm.auction_shipment_number,
pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(pah.auction_header_id,
pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id,
p_requested_supplier_id),l_price_mask, l_price_precision)
, null
) price,
decode(p_user_view_type, 'BUYER',pon_printing_pkg.format_price(pbsm.unit_price,l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbsm.bid_currency_unit_price,l_price_mask, l_price_precision)) bid_currency_unit_price
FROM pon_auction_shipments_all pas,
pon_auction_item_prices_all paip,
pon_bid_shipments pbsm
WHERE pbsm.bid_number = pbhs.bid_number
and pbsm.auction_header_id = pas.auction_header_id(+)
and pbsm.line_number = pas.line_number(+)
and pbsm.auction_shipment_number = pas.shipment_number(+)
AND paip.auction_header_id = pbsm.auction_header_id
AND paip.line_number = pbsm.line_number
AND pbsm.shipment_type = 'QUANTITY BASED'
and exists (select 1 from pon_bid_item_prices where bid_number=pbsm.bid_number and line_number=pbsm.line_number)
UNION ALL
SELECT pas.auction_header_id,
pas.line_number,
pas.shipment_number,
pas.shipment_number bid_shipment_number,
pon_printing_pkg.format_number(pas.quantity) quantity,
pon_printing_pkg.format_number(pas.max_quantity) max_quantity,
pon_printing_pkg.format_price(
pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id,p_trading_partner_contact_id,p_vendor_site_id, p_requested_supplier_id),
l_price_mask,
l_price_precision
) price,
null bid_currency_unit_price
FROM pon_auction_shipments_all pas,
pon_auction_item_prices_all paip
WHERE pas.auction_header_id = pah.auction_header_id
AND paip.auction_header_id = pas.auction_header_id
AND paip.line_number = pas.line_number
AND pas.shipment_type = 'QUANTITY BASED'
and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number)
) ORDER BY line_number,bid_shipment_number asc
) as ITEM_QUANTITY_TIERS,
CURSOR (
(SELECT
pay.auction_header_id
,pay.payment_id
,pay.line_number
,pay.payment_display_number payment_display_number
,pay.ship_to_location_id
,terr.territory_short_name shipto_country_name
,hrl.location_code shipto_address_name
,hrl.address_line_1 shipto_address1
,hrl.address_line_2 shipto_address2
,hrl.address_line_3 shipto_address3
,hrl.town_or_city shipto_city
,hrl.region_2 shipto_state
,hrl.region_3 shipto_province_or_region
,hrl.postal_code shipto_zip_code
,hrl.postal_code shipto_postal_code
,hrl.country shipto_country
,hrl.region_1 shipto_county
,pay.payment_description
,pay.payment_type_code
,lkp1.displayed_field payment_type_disp
,pay.quantity
,pay.uom_code
,uom_tl.unit_of_measure_tl unit_of_measure_tl
,pon_printing_pkg.format_price(pay.target_price*l_rate, l_price_mask, l_price_precision) target_price
,pon_oa_util_pkg.display_date_time(pay.need_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') need_by_date
,pay.work_approver_user_id
,NVL2(pay.work_approver_user_id, (SELECT per.full_name
FROM per_all_people_f per
WHERE per.person_id = fuser.employee_id
AND per.effective_end_date =
(SELECT MAX(per1.effective_end_date)
FROM per_all_people_f per1
WHERE per.person_id = per1.person_id)
), NULL) work_approver_name
,pay.note_to_bidders
,pay.project_id project_id
,proj.segment1 project_number
,pay.project_task_id project_task_id
,task.task_number project_task_number
,pay.project_award_id project_award_id
,awrd.award_number project_award_number
,pay.project_expenditure_type project_expenditure_type
,pay.project_exp_organization_id project_exp_organization_id
,hrorg.name project_exp_organization_name
,pon_oa_util_pkg.display_date(pay.project_expenditure_item_date, p_client_time_zone,
p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
,null pay_item_price
,null amount_display
,null bid_promised_date
FROM
pon_auc_payments_shipments pay,
pa_projects_all proj,
pa_tasks task,
gms_awards_all awrd,
hr_locations_all hrl,
hr_all_organization_units hrorg,
fnd_user fuser,
po_lookup_codes lkp1,
fnd_territories_tl terr,
mtl_units_of_measure_tl uom_tl
WHERE pay.auction_header_id = p_auction_header_id
AND pay.project_id = proj.project_id(+)
AND pay.project_task_id = task.task_id(+)
AND pay.project_award_id = awrd.award_id(+)
AND pay.ship_to_location_id = hrl.location_id(+)
AND terr.territory_code(+) = hrl.country
AND terr.language(+) = l_printing_language
AND pay.project_exp_organization_id = hrorg.organization_id(+)
AND pay.payment_type_code = lkp1.lookup_code(+)
AND lkp1.lookup_type(+) = 'PAYMENT TYPE'
AND pay.uom_code = uom_tl.uom_code(+)
AND uom_tl.language(+) = l_printing_language
AND fuser.user_id(+) = pay.work_approver_user_id
AND not exists (select 1 from pon_bid_item_prices where bid_number = p_bid_number and line_number=pay.line_number)
UNION ALL
SELECT
pbp.auction_header_id
,pbp.BID_PAYMENT_ID payment_id
,pbp.BID_LINE_NUMBER line_number
,pbp.payment_display_number payment_display_number
,pay.ship_to_location_id
,terr.territory_short_name shipto_country_name
,hrl.location_code shipto_address_name
,hrl.address_line_1 shipto_address1
,hrl.address_line_2 shipto_address2
,hrl.address_line_3 shipto_address3
,hrl.town_or_city shipto_city
,hrl.region_2 shipto_state
,hrl.region_3 shipto_province_or_region
,hrl.postal_code shipto_zip_code
,hrl.postal_code shipto_postal_code
,hrl.country shipto_country
,hrl.region_1 shipto_county
,pbp.payment_description
,pbp.payment_type_code
,lkp1.displayed_field payment_type_disp
,pay.quantity
,pay.uom_code
,uom_tl.unit_of_measure_tl unit_of_measure_tl
,pon_printing_pkg.format_price(pay.target_price*l_rate, l_price_mask, l_price_precision) target_price
,pon_oa_util_pkg.display_date_time(pay.need_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') need_by_date
,pay.work_approver_user_id
,NVL2(pay.work_approver_user_id, (SELECT per.full_name
FROM per_all_people_f per
WHERE per.person_id = fuser.employee_id
AND per.effective_end_date =
(SELECT MAX(per1.effective_end_date)
FROM per_all_people_f per1
WHERE per.person_id = per1.person_id)
), NULL) work_approver_name
,pay.note_to_bidders
,pay.project_id project_id
,proj.segment1 project_number
,pay.project_task_id project_task_id
,task.task_number project_task_number
,pay.project_award_id project_award_id
,awrd.award_number project_award_number
,pay.project_expenditure_type project_expenditure_type
,pay.project_exp_organization_id project_exp_organization_id
,hrorg.name project_exp_organization_name
,pon_oa_util_pkg.display_date(pay.project_expenditure_item_date, p_client_time_zone,
p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
,decode(p_user_view_type, 'BUYER', pon_printing_pkg.format_price(pbp.price, l_price_mask,l_price_precision), pon_printing_pkg.format_price(pbp.bid_currency_price,l_price_mask,l_price_precision)) pay_item_price
,to_char(decode(pbp.quantity,null,decode(pbip.quantity, null, 1,pbip.quantity),pbp.quantity)*decode(p_user_view_type, 'BUYER',pbp.price,pbp.bid_currency_price), l_amount_mask) amount_display
,pon_oa_util_pkg.display_date_time(pbp.promised_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') bid_promised_date
FROM
pon_auc_payments_shipments pay,
pa_projects_all proj,
pa_tasks task,
gms_awards_all awrd,
hr_locations_all hrl,
hr_all_organization_units hrorg,
fnd_user fuser,
po_lookup_codes lkp1,
fnd_territories_tl terr,
mtl_units_of_measure_tl uom_tl,
pon_bid_payments_shipments pbp,
pon_bid_item_prices pbip
WHERE
pbp.bid_number = pbhs.bid_number
AND pbp.auction_header_id = pay.auction_header_id(+)
AND pbip.bid_number = pbp.bid_number
AND pbip.line_number = pbp.bid_line_number
AND pbp.bid_line_number = pay.line_number(+)
AND pbp.auction_payment_id = pay.payment_id(+)
AND pay.project_id = proj.project_id(+)
AND pay.project_task_id = task.task_id(+)
AND pay.project_award_id = awrd.award_id(+)
AND pay.ship_to_location_id = hrl.location_id(+)
AND terr.territory_code(+) = hrl.country
AND terr.language(+) = l_printing_language
AND pay.project_exp_organization_id = hrorg.organization_id(+)
AND pbp.payment_type_code = lkp1.lookup_code(+)
AND lkp1.lookup_type(+) = 'PAYMENT TYPE'
AND pay.uom_code = uom_tl.uom_code(+)
AND uom_tl.language(+) = l_printing_language
AND fuser.user_id(+) = pay.work_approver_user_id
)ORDER BY line_number, payment_display_number
) AS PAY_ITEMS,
cursor(
((select
distinct loc.location_id id,
loc.location_code name,
ship_territories_tl.territory_short_name country_name,
loc.location_code address_name,
loc.address_line_1 address1,
loc.address_line_2 address2,
loc.address_line_3 address3,
loc.town_or_city city,
loc.region_2 state,
loc.region_3 province_or_region,
loc.postal_code zip_code,
loc.postal_code postal_code,
loc.country country,
loc.region_1 county
from
hr_locations_all loc,
pon_auction_shipments_all pas,
fnd_territories_tl ship_territories_tl
WHERE
pas.auction_header_id = pah.auction_header_id
and l_is_buyer_negpdf = 'N'
and l_neg_has_price_breaks = 'Y'
and pas.shipment_type = 'PRICE BREAK'
and loc.ship_to_site_flag='Y'
and sysdate < nvl(loc.inactive_date, sysdate + 1)
and loc.location_id = pas.ship_to_location_id
and ship_territories_tl.territory_code(+) = loc.country
and ship_territories_tl.language(+) = l_printing_language
and nvl(loc.business_group_id, nvl(hr_general.get_business_group_id, -99))
= nvl(hr_general.get_business_group_id, nvl(loc.business_group_id, -99)))
union
(select
mp.organization_id id,
mp.organization_code name,
ship_territories_tl.territory_short_name country_name,
loc.location_code address_name,
loc.address_line_1 address1,
loc.address_line_2 address2,
loc.address_line_3 address3,
loc.town_or_city city,
loc.region_2 state,
loc.region_3 province_or_region,
loc.postal_code zip_code,
loc.postal_code postal_code,
loc.country country,
loc.region_1 county
from
hr_locations_all loc,
hr_all_organization_units haou,
fnd_territories_tl ship_territories_tl,
mtl_parameters mp ,
( SELECT
distinct pas.ship_to_organization_id
FROM pon_auction_shipments_all pas
WHERE
pas.auction_header_id = p_auction_header_id
AND l_is_buyer_negpdf = 'N'
AND l_neg_has_price_breaks = 'Y'
AND pas.shipment_type = 'PRICE BREAK'
and pas.ship_to_location_id is null) pb_organizations
where
l_is_buyer_negpdf = 'N'
and l_neg_has_price_breaks = 'Y'
and haou.organization_id = mp.organization_id
and haou.organization_id = pb_organizations.ship_to_organization_id
and loc.ship_to_site_flag = 'Y'
and (loc.inventory_organization_id is null or nvl(loc.inventory_organization_id, -1) = nvl(pb_organizations.ship_to_organization_id,-1))
and sysdate < nvl(loc.inactive_date, sysdate + 1)
and ship_territories_tl.territory_code(+) = loc.country
and ship_territories_tl.language(+) = l_printing_language
and nvl(loc.business_group_id, nvl(haou.business_group_id, -99))
= nvl(haou.business_group_id, nvl(loc.business_group_id, -99))
))
order
by name) as PRICE_BREAK_LOCATIONS,
CURSOR (
(
SELECT
ppd.auction_header_id,
ppd.line_number,
ppd.shipment_number,
ppd.price_differential_number,
ppd.price_type,
pon_printing_pkg.format_number(ppd.multiplier) as target_multiplier,
pon_printing_pkg.format_number(pbpd.multiplier) as multiplier
FROM pon_price_differentials ppd,
-- bidpdf: add response multiplier for price differentials
pon_bid_price_differentials pbpd
WHERE ppd.auction_header_id = pah.auction_header_id
and pbpd.auction_header_id(+) = ppd.auction_header_id
and pbpd.bid_number (+) = pbhs.bid_number
and pbpd.line_number (+) = ppd.line_number
and pbpd.shipment_number(+) = ppd.shipment_number
and ppd.shipment_number = -1
and pbpd.price_differential_number(+) = ppd.price_differential_number
UNION ALL
SELECT
ppd.auction_header_id,
ppd.line_number,
ppd.shipment_number,
ppd.price_differential_number,
ppd.price_type,
pon_printing_pkg.format_number(ppd.multiplier) as target_multiplier,
pon_printing_pkg.format_number(pbpd.multiplier) as multiplier
FROM pon_price_differentials ppd,
-- bidpdf: add response multiplier for price differentials
pon_bid_price_differentials pbpd
WHERE ppd.auction_header_id = pah.auction_header_id
and pbpd.auction_header_id(+) = ppd.auction_header_id
and pbpd.bid_number (+) = pbhs.bid_number
and pbpd.line_number (+) = ppd.line_number
and pbpd.shipment_number(+) = ppd.shipment_number + 1
and ppd.shipment_number <> -1
and pbpd.price_differential_number(+) = ppd.price_differential_number
)ORDER BY auction_header_id, line_number, shipment_number, price_differential_number
) as PRICE_DIFFERENTIALS,
CURSOR (
SELECT DISTINCT
pov.price_differential_dsp,
pov.price_differential_desc,
pov.price_differential_type
FROM po_price_diff_lookups_v pov) as PRICE_DIFFERENTIAL_TYPES,
cursor(
select ad.attached_document_id,
d.datatype_name,
d.file_name file_name,
d.description,
d.title as attachment_title,
d.url,
'PON_AUCTION_ITEM_PRICES_ALL' as entity_name,
to_char(paip.auction_header_id) pk1_value,
to_char(paip.line_number) pk2_value,
ad.pk3_value,
categories_tl.user_name category_name
from fnd_documents_vl d,
fnd_attached_documents ad,
fnd_document_categories categories,
fnd_document_categories_tl categories_tl,
pon_auction_item_prices_all paip,
financials_system_params_all fsp
where d.document_id = ad.document_id
and
ad.entity_name = 'MTL_SYSTEM_ITEMS'
AND paip.auction_header_id = pah.auction_header_id
and fsp.org_id = pah.org_id
and ad.pk1_value = to_char(fsp.inventory_organization_id)
AND ad.pk2_value = to_char(paip.item_id)
and categories.name='Vendor'
and categories.category_id = d.category_id
and categories.category_id = categories_tl.category_id
and categories_tl.language = l_printing_language
UNION ALL
select ad.attached_document_id,
d.datatype_name,
d.file_name file_name,
d.description,
d.title,
d.url,
ad.entity_name,
ad.pk1_value,
ad.pk2_value,
ad.pk3_value,
categories_tl.user_name category_name
from fnd_documents_vl d,
fnd_attached_documents ad,
fnd_document_categories categories,
fnd_document_categories_tl categories_tl
where d.document_id = ad.document_id
and
ad.entity_name IN ('PON_AUCTION_ITEM_PRICES_ALL',
'PON_AUCTION_HEADERS_ALL')
and ad.pk1_value = to_char(p_auction_header_id)
and (l_is_buyer_negpdf = 'Y' or categories.name='Vendor')
and categories.category_id = d.category_id
and categories.category_id = categories_tl.category_id
and categories_tl.language = l_printing_language
--bidpdf:attachments in bid
UNION ALL
select ad.attached_document_id,
d.datatype_name,
d.file_name file_name,
d.description,
d.title,
d.url,
ad.entity_name,
ad.pk1_value,
ad.pk2_value,
ad.pk3_value,
categories_tl.user_name category_name
from fnd_documents_vl d,
fnd_attached_documents ad,
fnd_document_categories categories,
fnd_document_categories_tl categories_tl
where d.document_id = ad.document_id
and
ad.entity_name IN ('PON_BID_HEADERS',
'PON_BID_ITEM_PRICES')
and ad.pk1_value = to_char(p_auction_header_id)
and ad.pk2_value = to_char(pbhs.bid_number)
--and categories.name=pon_auction_pkg.g_supplier_attachment
and ((l_attach_categ_option = 1 AND categories.name = pon_auction_pkg.g_supplier_attachment)
or (l_attach_categ_option = 2 and categories.name = pon_auction_pkg.g_technical_attachment)
or (l_attach_categ_option = 3 and categories.name in (pon_auction_pkg.g_technical_attachment,pon_auction_pkg.g_commercial_attachment)))
and categories.category_id = d.category_id
and categories.category_id = categories_tl.category_id
and categories_tl.language = l_printing_language
--bidpdf:pay item attachments in bid
UNION ALL
select ad.attached_document_id,
d.datatype_name,
d.file_name file_name,
d.description,
d.title,
d.url,
ad.entity_name,
ad.pk1_value,
ad.pk2_value,
ad.pk3_value,
categories_tl.user_name category_name
from fnd_documents_vl d,
fnd_attached_documents ad,
fnd_document_categories categories,
fnd_document_categories_tl categories_tl
where d.document_id = ad.document_id
and
ad.entity_name IN ('PON_BID_PAYMENTS_SHIPMENTS')
and ad.pk1_value = to_char(pbhs.bid_number)
and categories.name = pon_auction_pkg.g_supplier_attachment
and categories.category_id = d.category_id
and categories.category_id = categories_tl.category_id
and categories_tl.language = l_printing_language
UNION ALL
select ad.attached_document_id,
d.datatype_name,
d.file_name file_name,
d.description,
d.title,
d.url,
ad.entity_name,
ad.pk1_value,
ad.pk2_value,
--for bid pdf, it should be bid_payment_id instead of auction_payment_id,
decode(l_is_bidpdf, 'Y',
(select to_char(bid_payment_id) from PON_BID_PAYMENTS_SHIPMENTS pby where bid_number = p_bid_number and pby.auction_payment_id = to_number(ad.pk3_value)),
ad.pk3_value) pk3_value,
categories_tl.user_name category_name
from fnd_documents_vl d,
fnd_attached_documents ad,
fnd_document_categories categories,
fnd_document_categories_tl categories_tl
where d.document_id = ad.document_id
and
ad.entity_name IN ('PON_AUC_PAYMENTS_SHIPMENTS')
and ad.pk1_value = to_char(p_auction_header_id)
and (l_is_buyer_negpdf = 'Y' or categories.name = 'Vendor')
and categories.category_id = d.category_id
and categories.category_id = categories_tl.category_id
and categories_tl.language = l_printing_language
) as ATTACHMENTS,
cursor( select
bizrules.name
from
pon_auc_doctype_rules doctype_rules
, pon_auc_bizrules bizrules
where doctype_rules.bizrule_id = bizrules.bizrule_id
and doctype_rules.doctype_id = doctypes.doctype_id
and doctype_rules.display_flag = 'Y'
and doctype_rules.validity_flag = 'Y'
and bizrules.name in (
'BID_LIST_TYPE',
'SHOW_BIDDER_NOTES',
'ALLOW_MULTIPLE_ROUNDS',
'BID_SCOPE',
'BID_QUANTITY_SCOPE',
'BID_FREQUENCY',
'MIN_BID_DECREMENT',
'MANUAL_CLOSE',
'MANUAL_EXTEND',
'AUTO_EXTENSION',
'RANK_INDICATOR',
'BID_RANKING',
'ALLOW_PRICE_ELEMENT',
'AWARD_APPROVAL_REQUIRED',
'DISPLAY_REQ_LINE_INTEGRATION_SOURCE',
'DISPLAY_LINE_INTEGRATION_SOURCE',
'GLOBAL_AGREEMENT',
'ALLOW_COLLABORATION_TEAM',
'START_PRICE',
'TARGET_PRICE',
'CURRENT_PRICE',
'CONTRACT_TYPE',
'ALLOW_PROXYBID',
'MIN_RELEASE_AMOUNT',
'BEST_PRICE'
)) as DOCUMENT_TYPE_RULES,
cursor (
select message_name,
message_text
from
fnd_new_messages
where message_name in ('PON_AUC_TITLE', --title
'PON_AUCTS_OPEN', -- Open Date
'PON_AUCTS_CLOSE', -- Close Date
'PON_AUCTS_PREVIEW', -- Preview Date
'PON_AUCTS_AWARD', -- Award Date
'PON_AUC_IMMEDIATELY', -- Immediately
'PON_AUC_STYLE', -- Style
'PON_EFFECTIVE_START_DATE', -- Effective Start Date
'PON_EFFECTIVE_END_DATE', -- Effective End Date
'PON_ACCTS_BUYER', -- Buyer
'PON_AUCTS_SHIP_TO_ADDRESS', --Ship-To Address
'PON_AUCTS_BILL_TO_ADDRESS', --Bill-To Address
'PON_AUCTS_PAYMENT_TERMS', --Payment Terms
'PON_AUCTS_CARRIER', --Carrier
'PON_AUCTS_FOB', --FOB
'PON_AUCTS_FREIGHT_TERMS', --Freight Terms
'PON_INTEL_AMOUNT', --Amount
'PON_AUCTS_PRICE_PREC', -- Price Precision
'PON_AUCTS_LINE_NO', --Line No.
'PON_AUCTS_UNIT_PRICE', --Unit Price
'PON_AUCTS_NUMBER_OF_UNITS', --Number of Units
'PON_AUCTS_PRN_GENERAL_INFO', -- L.1. General Information
'PON_AUCTS_PRN_TERMS', --I.2 Terms
'PON_AUCTS_PRN_PRICE_SCHEDULE', --2 Price Schedule
'PON_AUCTS_PRN_LINE_INFO', --2.1 Line Information
'PON_AUCTS_EXCHANGE_RATE', --Exchange Rate
'PON_AUCTS_HEADER_INFORMATION', --1 Header Information
'PON_AUC_WEIGHT', --Weight
'PON_AUC_REQUIRES_NO_RESP', --This requires no response.
'PON_AUCTS_OPTIONAL_RESP', --The response is optional.
'PON_AUCTS_MUST_PROVIDE_RESP', --You must provide a response.
'PON_AUCTS_RESP_MUST_BE_NUMERIC', --The response must be a numeric value.
'PON_AUCTS_RESP_MUST_BE_DATE', --The response must be a date value.
'PON_AUC_RESPONSE_VALUE', --Response Value
'PON_AUC_PRN_LINE_ATTR_NOTE', --You must provide a response unless otherwise indicated.
'PON_AUC_ACCEPTABLE_VALUES', --Acceptable Values
'PON_AUC_ATTRIBUTES', --Attributes
'PON_AUC_PRN_REF_ONLY_NO_RESP', --This is for reference only and your response is not required.
'PON_AUC_ANY', --Any
'PON_AUC_NOT_SPECIFIED', --Not Specified
'PON_AUCTION_CURRENCY', --Currency
'PON_AUC_CURRENCY_DESCRIPTION', --Currency escription
'PON_AUC_SHIP_TO', --Ship To
'PON_AUCTS_NEED_BY_DATE', --Need-By Date
'PON_AUC_TARGET_VALUE', --Target value
'PON_AUC_ENSURE_CURR_SELECTED', --Please ensure that you have selected a currency in Section I.2
'PON_AUC_TIME_ZONE', --Time Zone
'PON_AUCTS_EMAIL', --Email
'PON_AUCTS_PHONE', --Phone
'PON_AUCTS_FAX', --Fax
'PON_AUC_CONTACT_DETAILS', --Contact Details
'PON_AUC_YOUR_COMPANY_NAME', --Your Company Name
'PON_AUC_NOTE_TO_SUPPLIER', --Note to Supplier
'PON_AMEND_DESCRIPTION', --Amendment Description
'PON_AMEND_DATE', --Amendment Date
'PON_AUC_RULES_FOR_REFERENCE', --These rules are for your reference. Please do not check any checkboxes.
'PON_AUC_OPTIONAL_PB', --It is optional for you to enter a price for each line in the table. You may propose price breaks in the space provided or on a separate sheet of paper.
'PON_AUC_OPTIONAL_PB_2', -- It is optional for suppliers to enter a price for each line in the table. Suppliers may propose price breaks.
'PON_AUC_OPTIONAL_PB_3', -- You may propose price breaks in the space provided or on a separate sheet of paper.
'PON_AUC_OPTIONAL_PB_4', -- Suppliers may propose price breaks.
'PON_AUC_REQUIRED_PB', --You must enter a price for each line in the table.
'PON_AUC_REQUIRED_PB_2', -- Suppliers must enter a price for each line in the table.
'PON_AUC_CUMULATIVE_PB', --The break quantity is cumulative.
'PON_AUC_NON_CUMULATVE_PB', --The break quantity is non-cumulative.
'PON_AUC_PB_VIEW_SHIP_TO', --To view Ship-To addresses, refer to section
'PON_AUC_LOCATION_PRICING', --Location Pricing
'PON_AUC_REFER_ATTACH_PE', --Please refer to the attachments for price elements included in this line.
'PON_AUC_REFER_ATTACH_PD', --Please refer to the attachments for price differentials included in this line.
'PON_AUC_RFR_ATTACH_PD_LOC_PRC', --Please refer to the attachments for price differentials included in the location pricing for this line.
'PON_AUC_PART_I_HEADER_INFO_C', --PART I: HEADER INFORMATION
'PON_AUC_HEADER_ATTRIBUTES', --Header Attributes
'PON_AUC_PRN_PRICE_SCHEDULE_C', --2 Price Schedule
'PON_ITEM_DETAILS', --Line Details
'PON_AUC_TABLE_OF_CONTENTS_C', --TABLE OF CONTENTS
'PON_AUC_RESPONSE_RULES', --Response Rules
'PON_AUC_NR_CONTROL_MSG_1', --Negotiation is restricted to invited suppliers
'PON_AUC_NR_CONTROL_MSG_2', --Suppliers are allowed to view other suppliers notes and attachments
'PON_AUC_NR_CONTROL_MSG_2A', --Suppliers are allowed to view other suppliers' contract terms, notes and attachments
'PON_AUC_NR_CONTROL_MSG_9', --Buyer may create multiple rounds of negotiations
'PON_AUC_NR_CONTROL_MSG_3', --Suppliers are allowed to respond to selected lines
'PON_AUC_NR_CONTROL_MSG_4', --Suppliers are required to respond with full quantity on each line
'PON_AUC_NR_CONTROL_MSG_5', --Allow multiple responses
'PON_AUC_NR_CONTROL_MSG_14', --Suppliers are required to lower the line price when submitting a revised response
'PON_AUC_NR_CONTROL_MSG_7', --Buyer may close the negotiation before the Close Date
'PON_AUC_PRN_ALLOW_MANUAL_EXT', --Buyer may manually extend the negotiation while it is open
'PON_AUCTION_PROMISE_DATE', --Promise Date
'PON_AUCTS_DESCRIPTION', --Description
'PON_AUCTS_NAME', --Name
'PON_AUC_DATA_TYPE', --Data Type
'PON_AUC_UNDEFINED', --Undefined
'PON_AUC_GROUP_WEIGHT', --Group Weight
'PON_AUCTS_ITEM_DESC', -- Description
'PON_ITEM_REV', -- Item, Rev
'PON_ITEM_REV_JOB', --Item, Rev / Job
'PON_AUCTS_CATEGORY', --Category
'PON_SHOPPING_CAT', -- Shopping Category
'PON_AUCTS_UOM', -- Unit
'PON_AUC_CICRLE_RESP_BELOW', --Circle one from the response values below:
'PON_AUC_CIRCLE_RESPONSE_BELOW', -- (Circle one from the response values below):
'PON_AUC_UP_TO', --Up to
'PON_AUC_OPTIONAL_RESP', --It is optional for you to provide a response.
'PON_AUC_MAX_SCORE', --Maximum Score
'PON_AUCTS_RESP_MUST_BE_URL', --The response must be an URL value.
'PON_AUC_PRICE_TYPE_CIRCLE_VAL', --Price Type (Circle one value)
'PON_AUC_EFFECTIVE_FROM_DATE', --Effective From Date
'PON_AUC_EFFECTIVE_TO_DATE', --Effective To Date
'PON_BIDS_PRICE', --Price
'PON_AUC_DISCOUNT_PERCENTAGE', --Discount %
'PON_AUC_ADDRESS', --Address
'PON_AUC_LEAVE_BLANK', --Leave blank
'PON_AUC_ENTER_IN_ATTR_TABLE', --Enter in the Attributes Table below
'PON_AUC_SCORE_FOR_RESPONSE', --(Score for the response)
'PON_AUCTION_QUANTITY', --Quantity
'PON_AUC_PRN_CONTRACT_WARNING', --Note: This document does not include Contract Terms because the buyer does not have permission to view them.
'PON_AUCTS_COMPANY', --Company
'PON_AUC_LOCATION', --Location
'PON_JOB_DETAILS', --Job Details
'PON_AUCTION_LOT', --Lot
'PON_AUCTION_GROUP', --Group
'PON_FO_PROPRIETARY_INFORMATION', --Proprietary and Confidential
'PON_PAGE', -- Page PAGE_NUM of END_PAGE
'PON_AUC_SUBMIT_UR_RESPOSE_TO', -- Please submit your response to:
'PON_AUC_INCLD_FOLLOWING_INFO', -- When submitting your response, please include the following information.
'PON_AUC_BID_VALID_UNTIL', -- Response Valid Until
'PON_AUC_SECURITY_LEVEL', -- Security Level
'PON_AUC_APPROVAL_STATUS', -- Approval Status
'PON_OPERATING_UNIT', -- Operating Unit
'PON_AUC_OUTCOME', -- Outcome
'PON_AUC_NEGOTIATION_STYLE', -- Negotiation Style
'PON_AUCTS_AUCTION_EVENT', -- Event
'PON_SOURCING_PROJECT', -- Sourcing Project
'PON_AUC_APPROVAL_REQUIRED', -- Requires Approval
'PON_AUC_APPROVAL_NOT_REQUIRED', -- Requires No Approval
'PON_AUC_APPROVAL_APPROVED', -- Approved
'PON_AUC_APPROVAL_REJECTED', -- Rejected
'PON_AUC_APPROVAL_INPROCESS', -- In Process
'PON_AUC_COLLABORATION_TEAM', -- Collaboration Team
'PON_AUC_MEMBER_ROLE', -- Member
'PON_AUC_POSITION', -- Position
'PON_AUC_APPROVER', -- Approver
'PON_AUC_ACCESS', -- Access
'PON_AUC_TASK', -- Task
'PON_AUC_TARGET_DATE', -- Target Date
'PON_CORE_YES', -- Yes
'PON_CORE_NO', -- No
'PON_AUCTS_GLOBAL_AGREEMENT', -- Global Agreement
'PON_AUC_ELIGIBLE_RESP_CURR', -- Eligible Response Currencies
'PON_AUC_CHECK_RESP_CURR', -- Check the one currency in which you will enter your response.
'PON_AUC_EX_RATE_TYPE', -- Exchange Rate Type
'PON_AUC_EX_RATE_DATE', -- Exchange Rate Date
'PON_DISP_TO_SUPPLIERS', -- Display To Suppliers
'PON_DO_NOT_DISP_TO_SUPPLIERS', -- Do Not Display to Suppliers
'PON_AUC_DISPLAY_SCORE_2', -- Display scoring criteria to Suppliers
'PON_ABSTRACT_FORMS', -- Abstract and Forms
'PON_AUC_VERSION', -- Version
'PON_AUC_NR_CONTROL_MSG_6', -- Buyer is required to obtain approval of award decisions
'PON_AUC_NR_CONTROL_MSG_13', -- Negotiation is allowed to AutoExtend
'PON_AUC_NR_CONTROL_MSG_15', -- Negotiation is allowed to AutoExtend based on the following settings
'PON_AUC_NR_CONTROL_MSG_16', -- Show best price to a supplier in a blind negotiation
'PON_AUC_NR_CONTROL_MSG_18', -- Enforce supplier's previous round price as start price for this round
'PON_AUTO_EXTEND_SETTINGS', -- AutoExtend Settings
'PON_START_TIME_EXTEND', -- Start Time of Extensions
'PON_NUMBER_OF_EXTENSIONS', -- Number of Extensions
'PON_AUTO_EXTEND_PERIOD', -- AutoExtend Period
'PON_LINES_TO_AUTO_EXTEND', -- Lines to AutoExtend
'PON_AUCTS_CLOSE_DATE', -- Close Date
'PON_AUTOEXT_TIME_2', -- Receipt time of the triggering winning response
'PON_AUTOEXT_ITEM_2', -- Lines that have received winning responses during the AutoExtend period
'PON_TRIGGERING_RESPONSE', -- Triggering Response
'PON_LOW_TRIGG_RESP_RANK', -- Lowest Triggering Response Rank
'PON_AUTOEXT_RESPONSE_1', -- Response with winning lines
'PON_AUTOEXT_RESPONSE_2', -- Any Response
'PON_AUC_UNLIMITED', -- Unlimited
'PON_AUC_MINUTES', -- Minutes
'PON_AUCTS_ALL_ITEMS', -- All Lines
'PON_AUTOEXT_ITEM_3', -- Lines that have received responses during the AutoExtend period
'PON_AUC_DISPLAY_RANK', -- Display Rank As
'PON_AUC_RANKING', -- Ranking
'PON_AUC_PRICE_ELEMENTS', -- Price Factors
'PON_AUC_SUPPLIER_VIEW', -- Suppliers see their response price transformed
'PON_AUC_ENTER_IN_PF_TABLE', -- Enter in the Cost Factors table below
'PON_AUC_REQUISITION', -- Requisition
'PON_AUC_MULTIPLE', -- Multiple
'PON_AUC_LINE_TYPE', -- Line Type
'PON_AUC_PRICE_ELEMENT', -- Price Factor
'PON_AUC_PRICE_ELEMENT_DESC', -- Description
'PON_AUCTS_TYPE', -- Type
'PON_AUCTS_DISP_TO_BIDDER', -- Display To Suppliers
'PON_AUC_PRICING_BASIS', -- Pricing Basis
'PON_AUCTS_ATTR_D_TARGET', -- Display Target
'PON_AUCTS_BID_VALUE', -- Response Value
'PON_AUCTION_ITEM_PRICE', -- Line Price
'PON_AUC_PRICE_FACTOR_NOTE_1', -- It is required for you to enter a response value for the Supplier Price Factors.
'PON_PRICE_DIFFERENTIAL_DESC', -- Description
'PON_TARGET_MULTIPLIER', -- Target Multiplier
'PON_AUC_RESP_MULTIPLIER', -- Response Multiplier
'PON_PRICE_DIFFERENTIALS', -- Price Differentials
'PON_AUC_PRICE_DIFF_NOTE_1', -- Suppliers must enter a response multiplier for each line in the table.
'PON_AUC_PRICE_DIFF_NOTE_2', -- It is optional for suppliers to enter a response multiplier for each line in the table.
'PON_AUC_PRICE_DIFF_NOTE_3', -- You must enter a response multiplier for each line in the table.
'PON_AUC_PRICE_DIFF_NOTE_4', -- It is optional for you to enter a response multiplier for each line in the table.
'PON_AUCTS_PRICE_BREAKS', -- Price Breaks
'PON_AUCTS_PRICE_BREAK', -- Price Break
'PON_AUCTS_TARGET_PRICE', -- Target Price
'PON_AUC_SHIP_TO_ADDRESSES', -- Ship-To Addresses
'PON_INVITED_SUPPLIERS', -- Invited Suppliers
'PON_ACCTS_SUPPLIER', -- Supplier
'PON_AUCTS_SUPPLIER_SITE', -- Supplier Site
'PON_AUCTS_CONTACT', -- Contact
'PON_AUC_ADDNL_EMAIL', -- Additional Contact Email
'PON_AUC_RESPONSE_CURR', -- Response Currency
'PON_ANY_RESPONSE_CURRENCY', -- Any Response Currencies
'PON_NEG_FULL', -- Full
'PON_NEG_RESTRICTED', -- Restricted
'PON_AUC_BUYER_PF_VALUES', -- Buyer Price Factor Values
'PON_HEADER_INFORMATION', -- Header Information
'PON_PRICE_SCHEDULE', -- Price Schedule
'PON_AUCTS_ATTACHMENTS', -- Attachments
'PON_LINE_BID_OPTIONAL', -- It is optional for you to respond to this line.
'PON_AUC_BIDDER_ADDRESS' --Address
,'PON_DECREMENT_METHOD_MSG' -- Suppliers are required to lower the line price from the best response
,'PON_ADVANCE_AMOUNT_PROMPT' -- Advance Amount
,'PON_FINANCING' -- Financing
,'PON_RETAINAGE' -- Retainage
,'PON_DEFAULT_PROJECT_INFO' -- Default Project Information
,'PON_DEFAULT_OWNER' -- Default Owner
,'PON_DESCRIPTION' -- Description
,'PON_EXPENDITURE_ITEM_DATE' -- Expenditure Item Date
,'PON_EXPENDITURE_ORGANIZATION' -- Expenditure Organization
,'PON_EXPENDITURE_TYPE' -- Expenditure Type
,'PON_FLAG_DISPLAY_NO' -- Yes
,'PON_FLAG_DISPLAY_YES' -- No
,'PON_GOODS_LINE_PAY_ITEM_MSG' -- Unit Price for each pay item is based on the Number of Units quoted for this line
,'PON_MAXIMUM_RETAINAGE_AMOUNT' -- Maximum Retainage Amount
,'PON_PAYMENT_INFORMATION' -- Pay Item Information
,'PON_PAYMENT_TIP_FINANCE' -- Total pay item amount may not add up to the line amt
,'PON_PAYMENT_TIP_ACTUAL' -- Total pay item amount must add up to the line amt
,'PON_PAY_ITEM' -- Pay Item
,'PON_NEGOTIABLE' -- Negotiable
,'PON_FINANCING_ATTRIBUTES' -- Financing Attributes
,'PON_RETAINAGE_ATTRIBUTES' -- Retainage Attributes
,'PON_PROGRESS_PAYMENT_RATE' -- Progress Payment Rate
,'PON_PROJECT' -- Project
,'PON_PROJECT_INFORMATION' -- Project Information
,'PON_RECOUPMENT_RATE' -- Recoupment Rate
,'PON_RETAINAGE_RATE' -- Retainage Rate
,'PON_SUPP_ENTERABLE_PYMT_FLAG' -- Supplier can modify Pay Items
,'PON_SUPP_UPD_PAY_ITEMS_1' -- Suppliers may propose pay items.
,'PON_SUPP_UPD_PAY_ITEMS_2' -- You may propose pay items in the space provided or on a separate sheet of paper.
,'PON_SUPP_UPD_PAY_ITEMS_3' -- Suppliers may propose different pay items.
,'PON_SUPP_UPD_PAY_ITEMS_4' -- You may propose different pay items in the space provided or on a separate sheet of paper.
,'PON_TASK' -- Task
,'PON_UNITS' -- Units
,'PON_OWNER' -- Owner
,'PON_TEAM_SCORING' -- Team Scoring
,'PON_TEAM_SCORING_ENABLED' -- Team Scoring enabled
,'PON_TEAM' -- Team
,'PON_MEMBERS' -- Members
,'PON_TEAM_INSTRUCTIONS' -- Team Instructions
,'PON_PRICE_VISIBILITY' -- Price Visibility
,'PON_SECTION_ASSIGNMENT' -- Section Assignment
,'PON_AUC_REQUIREMENTS' -- Requirements
,'PON_AUC_SECTION_WEIGHT' -- Section Weight
,'PON_AUC_KO_SCORE' -- Knockout Score
,'PON_AUC_INTERNAL' -- Internal
,'PON_AUC_AUTOMATIC' -- Automatic
,'PON_AUC_NONE' -- None
,'PON_AUC_MANUAL' -- Manual
,'PON_AUCTS_ATTR_DATATYPE' -- Value Type
,'PON_AUC_SCORING' -- Scoring
,'PON_AUC_SCORE_DISPLAYED' -- score displayed in brackets
,'PON_PROVIDE_ANSWER' -- Provide your answer below
,'PON_STAGGERED_CLOSING_MSG' -- Staggered Closing
,'PON_AUCTS_STAG_FIRST_CLOSE_DAT' -- First Line Close date
,'PON_STAGGERED_CLOSE_INTERVAL' -- Staggered Closing Interval
,'PON_BID_RESPONSE_STATUS' --Response Status
,'PON_BID_RESPONSE_SUBMITTED' --Your response has been submitted to:
,'PON_BID_RESPONSE_WILLSUBMITTED' --Your response will be submitted to:
,'PON_BID_YOUR_INFO' --Your information is:
,'PON_AUCTS_YOUR_BID_NUMBER' --Reference Number
,'PON_AUCTS_NOTE_TO_BUYER' --Note to Buyer
,'PON_BID_YOUR_REQ_RESPONSE' -- Your response value:
,'PON_AUC_SURROG_RECVD_TIME' -- Response Received Time
,'PON_BID_BUYER_ATTACHMENTS' -- Buyer Attachments
,'PON_BID_SUP_ATTACHMENTS' -- Supplier Attachments
,'PON_AUCTS_BID_MIN_REL_AMT' -- Bid Minimum Release Amount
,'PON_BIDS_RESPONSE_PRICE' -- Response Price
,'PON_AUC_PRICE_SCORE' -- Price/Total Score
,'PON_AUCTS_PROXY_MIN' --Proxy Minimum
,'PON_AUCTS_PROXY_DEC' --Proxy Response Decrement
,'PON_BID_TOTAL_WARNING' --Cannot be displayed because quantity is not available on all lines
,'PON_AUCTS_PRICE_TYPE' --Price Type
,'PON_BIDS_PRICE_OR_DISCOUNT' --Response Price or Discount%
,'PON_BID_BUYER_PI_ATTACHMENTS' -- Buyer Pay Item Attachments
,'PON_BID_SUP_PI_ATTACHMENTS' -- Supplier Pay Item Attachments
,'PON_BID_YOUR_RESPONSE_BRACKET' -- Your response value (score displayed in brackets):
,'PON_BID_YOUR_SITE' -- Your Company Site
,'PON_BIDS_NO_RESPONSE' -- No Response
,'PON_BUYER_PDF_TXT' -- Message for buyer view pdf
,'PON_SUPPLIER_PDF_TXT' -- Message for supplier view pdf
,'PON_AUCTS_PRICE_TIERS' -- Quantity based price tiers
,'PON_TIERS_MIN_QUANTITY' -- Minimum Quantity
,'PON_TIERS_MAX_QUANTITY' -- Maximum Quantity
,'PON_AUC_TARGET_QUANTITY' --Target Quantity
,'PON_AUCTS_RESP_QUANTITY' --Response Quantity
,'PON_AUCTION_PRICE' -- Price
) and application_id =396
and language_code = l_printing_language) as GENERIC_MESSAGES ,
cursor ( -- this will have all messages that are specific to sourcing type
select substr(message_name,1,length(message_name)-2) message_name,
message_text
from
fnd_new_messages
where message_name in (
pon_printing_pkg.get_document_message_name('PON_CONT_MERGE_WARNING',doctypes.message_suffix), -- Note: There are contract terms associated to the RFQ that are not included in this document. The contract terms are an inseparable part of this RFQ.
pon_printing_pkg.get_document_message_name('PON_RESPONSE_STYLE',doctypes.message_suffix) -- Response Style
) and application_id =396
and language_code = l_printing_language) as DOCUMENT_SPECIFIC_MESSAGES
from
pon_auction_headers_all pah ,
fnd_lookups fl,
fnd_lookups fl2,
fnd_lookups flbr ,
fnd_lookups fl_rank_ind,
fnd_lookups fl_pf_type_allowed,
fnd_lookup_values fl_freight_terms ,
ap_terms ap ,
fnd_lookup_values fl_fob ,
hr_locations_all loc_bill,
fnd_territories_tl bill_territories_tl,
hr_locations_all loc_ship,
fnd_territories_tl ship_territories_tl,
fnd_currencies_tl currency_tl ,
pon_auc_doctypes doctypes,
hz_parties hp1,
hr_operating_units ou,
hr_all_organization_units entity,
hr_all_organization_units_tl entitytl,
hr_locations_all entity_loc,
fnd_territories_tl entity_terr,
fnd_lookups fl_security,
gl_daily_conversion_types gdct,
fnd_product_groups fpg,
pon_negotiation_styles_vl ns,
PO_ALL_DOC_STYLE_LINES ps,
po_doc_style_headers postyl,
pa_projects_all proj,
fnd_user buyer_user,
per_phones buyer_phone,
per_phones buyer_fax,
pon_bid_headers pbhs,
fnd_lookup_values fl_bid
where pah.auction_header_id = p_auction_header_id
and pbhs.auction_header_id (+) = pah.auction_header_id
and pbhs.bid_number (+) = p_bid_number
and fl_bid.lookup_type(+) = 'PON_BID_STATUS'
and fl_bid.lookup_code(+) = pbhs.bid_status
and fl_bid.language(+) = l_printing_language
and currency_tl.currency_code = pah.currency_code
and currency_tl.language = l_printing_language
and fl.lookup_type = 'PON_BID_VISIBILITY_CODE'
and fl.lookup_code = pah.bid_visibility_code
and flbr.lookup_type = 'PON_BID_RANKING_CODE'
and flbr.lookup_code = pah.bid_ranking
and pah.sealed_auction_status = fl2.lookup_code (+)
and fl2.lookup_type(+) = 'PON_SEALED_AUCTION_STATUS'
and fl_freight_terms.lookup_type(+) = 'FREIGHT TERMS'
and fl_freight_terms.lookup_code(+) = pah.freight_terms_code
and fl_security.lookup_type = 'PON_SECURITY_LEVEL_CODE'
and fl_security.lookup_code = pah.security_level_code
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 fl_freight_terms.language(+) = l_printing_language
and fl_freight_terms.view_application_id(+) = 201
and fl_freight_terms.security_group_id(+) = 0
and ap.term_id(+) = pah.payment_terms_id
and fl_fob.lookup_type(+) = 'FOB'
and fl_fob.lookup_code(+) = pah.fob_code
and fl_fob.language(+) = l_printing_language
and fl_fob.view_application_id(+) = 201
and fl_fob.security_group_id (+) = 0
and loc_bill.location_id(+) = pah.bill_to_location_id
and bill_territories_tl.territory_code(+) = loc_bill.country
and bill_territories_tl.language(+) = l_printing_language
and loc_bill.bill_to_site_flag(+)='Y'
and sysdate < nvl(loc_bill.inactive_date(+), sysdate + 1)
and nvl(loc_bill.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
= nvl(hr_general.get_business_group_id, nvl(loc_bill.business_group_id(+), -99))
and loc_ship.location_id(+) = pah.ship_to_location_id
and ship_territories_tl.territory_code(+) = loc_ship.country
and ship_territories_tl.language(+) = l_printing_language
and loc_ship.ship_to_site_flag(+)='Y'
and sysdate < nvl(loc_ship.inactive_date(+), sysdate + 1)
and nvl(loc_ship.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
= nvl(hr_general.get_business_group_id, nvl(loc_ship.business_group_id(+), -99))
and pah.org_id = ou.organization_id(+)
and nvl(ou.date_from(+),sysdate-1) < sysdate
and nvl(ou.date_to(+),sysdate+1) > sysdate
and pon_conterms_utl_pvt.get_legal_entity_id(pah.org_id) = entity.organization_id(+)
and entity.organization_id = entitytl.organization_id(+)
and entitytl.language(+) = l_printing_language
and entity.location_id = entity_loc.location_id(+)
and nvl(entity_loc.inactive_date(+), sysdate+1) > sysdate
and entity_terr.territory_code(+) = entity_loc.country
and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
and entity_terr.language(+) = l_printing_language
and gdct.conversion_type(+) = pah.rate_type
and hp1.party_id = pah.trading_partner_contact_id
and pah.doctype_id = doctypes.doctype_id
and pah.style_id = ns.style_id
and pah.po_style_id = postyl.style_id(+)
and pah.project_id = proj.project_id(+)
and pah.po_style_id = ps.style_id(+)
and pah.contract_type = ps.document_subtype(+)
and ps.language(+) = l_printing_language
and pah.trading_partner_contact_name = buyer_user.user_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 buyer_fax.parent_table(+) = 'PER_ALL_PEOPLE_F'
and buyer_fax.parent_id(+) = buyer_user.employee_id
and buyer_fax.phone_type(+) = 'WF'
and nvl(buyer_fax.date_from(+), trunc(sysdate)) <= trunc(sysdate)
and nvl(buyer_fax.date_to(+), trunc(sysdate)) >= trunc(sysdate);
SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
SELECT CURRENT_DATE INTO l_end_time FROM DUAL;