[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_update_allowed OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
l_api_version NUMBER := 1;
x_update_allowed := fnd_api.g_false;
select
auction.auction_status
into
l_auction_status
from
fnd_user,
hz_parties user_parties,
hz_parties company_parties,
hz_relationships,
hz_code_assignments,
pon_auction_headers_all auction
where
fnd_user.user_id = fnd_global.user_id()
and fnd_user.person_party_id = user_parties.party_id
and hz_relationships.object_id = company_parties.party_id
and hz_relationships.subject_id = user_parties.party_id
and hz_relationships.relationship_type = 'POS_EMPLOYMENT'
and hz_relationships.relationship_code = 'EMPLOYEE_OF'
and hz_relationships.start_date <= SYSDATE
and hz_relationships.end_date >= SYSDATE
and hz_code_assignments.owner_table_id = company_parties.party_id
and hz_code_assignments.owner_table_name = 'HZ_PARTIES'
and hz_code_assignments.class_category = 'POS_PARTICIPANT_TYPE'
and hz_code_assignments.class_code = 'ENTERPRISE'
and auction.auction_header_id = l_auction_header_id
and auction.draft_locked = 'Y'
and auction.draft_locked_by_contact_id = user_parties.party_id
and auction.trading_partner_id = company_parties.party_id
and auction.auction_status = 'DRAFT';
x_update_allowed := fnd_api.g_false;
x_update_allowed := fnd_api.g_true;
l_pon_sys_vars(37).variable_code:= 'OKC$B_SELECTIVE_RESPONSE_FLAG';
select
pah.org_id organization,
pon_conterms_utl_pvt.get_legal_entity_id(pah.org_id) entity,
-- Bug 4099936
-- Decode the doctype_id to the internal name as in PO valueset for the variable POC_XPRT_DOC_TYPE
-- Note that this piece of code may not be used as of today since Contract expert is not used for
-- response documents
DECODE(pah.doctype_id, 21, 'SOURCING RFI', 5, 'SOURCING RFQ', 1, 'BUYER AUCTION') document_type,
pah.document_number document_number,
pah.ship_to_location_id ship_to_address,
pah.bill_to_location_id bill_to_address,
pah.currency_code currency,
pah.trading_partner_contact_id buyer,
pah.trading_partner_name enterprise_name,
pah.po_agreed_amount * nvl(pah.rate, 1)
agreement_amount1,
pah.po_agreed_amount * nvl(pah.rate, 1)
agreement_amount2,
pah.payment_terms_id payment_terms,
pah.freight_terms_code freight_terms,
pah.carrier_code carrier,
pah.fob_code fob,
pah.po_start_date agreement_start_date,
pah.po_end_date agreement_end_date,
pah.po_min_rel_amount * nvl(pah.rate, 1)
minimum_release_amount1,
pah.po_min_rel_amount * nvl(pah.rate, 1)
minimum_release_amount1,
pah.contract_type outcome,
pah.auction_title title,
pah.bid_visibility_code style,
pah.bid_ranking response_ranking,
pah.hdr_attr_display_score display_criteria,
pah.open_bidding_date open_response_date,
pah.close_bidding_date close_response_date,
pah.view_by_date preview_date,
pah.award_by_date award_date,
pah.allow_other_bid_currency_flag
currency_response_flag,
decode(pah.bid_list_type,'PRIVATE_BID_LIST','Y','N')
invitation_only_flag,
pah.show_bidder_notes supplier_response_flag,
-- Bug 4099936
-- decode control settings to Y/N to match the valueset
DECODE(pah.bid_scope_code, 'MUST_BID_ALL_ITEMS', 'N', 'Y')
selective_response_flag,
decode(pah.full_quantity_bid_code,'FULL_QTY_BIDS_REQD','Y','N')
full_quantity_response_flag,
decode(pah.bid_frequency_code,'MULTIPLE_BIDS_ALLOWED','Y','N')
multiple_responses_flag,
pah.multiple_rounds_flag multiple_rounds_flag,
pah.manual_close_flag manual_close_flag,
pah.manual_extend_flag manual_extend_flag,
pah.auto_extend_flag auto_extend_flag,
pah.price_driven_auction_flag prices_decrease_flag,
pah.amendment_description amendment_description,
pbh.trading_partner_name supplier_name,
pbh.trading_partner_contact_id supplier_contact,
pbh.bid_number response_number,
pbh.bid_currency_code response_currency,
pah.currency_code func_currency,
-- ECO 4241852 -- BUG 5087598
pah.po_style_id || '-' || pah.contract_type style_id
into
l_pon_sys_vars(27).variable_value_id,
l_pon_sys_vars(19).variable_value_id,
l_pon_sys_vars(13).variable_value_id,
l_pon_sys_vars(39).variable_value_id,
l_pon_sys_vars(38).variable_value_id,
l_pon_sys_vars( 7).variable_value_id,
l_pon_sys_vars(44).variable_value_id,
l_pon_sys_vars( 8).variable_value_id,
l_pon_sys_vars(14).variable_value_id,
l_pon_sys_vars( 1).variable_value_id,
l_pon_sys_vars( 2).variable_value_id,
l_pon_sys_vars(29).variable_value_id,
l_pon_sys_vars(16).variable_value_id,
l_pon_sys_vars( 9).variable_value_id,
l_pon_sys_vars(15).variable_value_id,
l_pon_sys_vars( 4).variable_value_id,
l_pon_sys_vars( 3).variable_value_id,
l_pon_sys_vars(22).variable_value_id,
l_pon_sys_vars(23).variable_value_id,
l_pon_sys_vars(28).variable_value_id,
l_pon_sys_vars(43).variable_value_id,
l_pon_sys_vars(40).variable_value_id,
l_pon_sys_vars(33).variable_value_id,
l_pon_sys_vars(12).variable_value_id,
l_pon_sys_vars(26).variable_value_id,
l_pon_sys_vars(10).variable_value_id,
l_pon_sys_vars(30).variable_value_id,
l_pon_sys_vars(35).variable_value_id,
l_pon_sys_vars(11).variable_value_id,
l_pon_sys_vars(18).variable_value_id,
l_pon_sys_vars(36).variable_value_id,
l_pon_sys_vars(37).variable_value_id,
l_pon_sys_vars(17).variable_value_id,
l_pon_sys_vars(25).variable_value_id,
l_pon_sys_vars(24).variable_value_id,
l_pon_sys_vars(20).variable_value_id,
l_pon_sys_vars(21).variable_value_id,
l_pon_sys_vars( 6).variable_value_id,
l_pon_sys_vars(34).variable_value_id,
l_pon_sys_vars( 5).variable_value_id,
l_pon_sys_vars(42).variable_value_id,
l_pon_sys_vars(41).variable_value_id,
l_pon_sys_vars(32).variable_value_id,
l_pon_sys_vars(31).variable_value_id,
l_pon_sys_vars(71).variable_value_id,
-- ECO 4241852
l_pon_sys_vars(72).variable_value_id
from
pon_auction_headers_all pah,
pon_bid_headers pbh,
hr_all_organization_units ou
where
pbh.bid_number = p_doc_id and
pbh.auction_header_id = pah.auction_header_id and
pah.org_id = ou.organization_id(+) and
nvl(ou.date_from(+),sysdate-1) < sysdate and
nvl(ou.date_to(+),sysdate+1) > sysdate ;
l_pon_sys_vars(36).variable_code:= 'OKC$B_SELECTIVE_RESPONSE_FLAG';
select
pah.org_id organization,
pon_conterms_utl_pvt.get_legal_entity_id(pah.org_id) entity,
-- Bug 4099936
-- decode doctype_id to the document type code as per the po value set POC_XPRT_DOC_TYPE associated
-- with the doc type system variable.
DECODE(pah.doctype_id, 21, 'SOURCING RFI', 5, 'SOURCING RFQ', 1, 'BUYER AUCTION') document_type,
pah.document_number document_number,
pah.ship_to_location_id ship_to_address,
pah.bill_to_location_id bill_to_address,
pah.currency_code currency,
pah.trading_partner_contact_id buyer,
pah.trading_partner_name enterprise_name,
pah.po_agreed_amount * nvl(pah.rate, 1)
agreement_amount1,
pah.po_agreed_amount * nvl(pah.rate, 1)
agreement_amount2,
pah.global_agreement_flag global_flag,
pah.payment_terms_id payment_terms,
pah.freight_terms_code freight_terms,
pah.carrier_code carrier,
pah.fob_code fob,
pah.po_start_date agreement_start_date,
pah.po_end_date agreement_end_date,
pah.po_min_rel_amount * nvl(pah.rate, 1)
minimum_release_amount1,
pah.po_min_rel_amount * nvl(pah.rate, 1)
minimum_release_amount1,
pah.contract_type outcome,
pah.auction_title title,
pah.bid_visibility_code style,
pah.bid_ranking response_ranking,
pah.hdr_attr_display_score display_criteria,
pah.open_bidding_date open_response_date,
pah.close_bidding_date close_response_date,
pah.view_by_date preview_date,
pah.award_by_date award_date,
pah.allow_other_bid_currency_flag
currency_response_flag,
decode(pah.bid_list_type,'PRIVATE_BID_LIST','Y','N')
invitation_only_flag,
pah.show_bidder_notes supplier_response_flag,
-- Bug 4099936
-- decode control setting to Y/N as expected by the Contract Expert value set for the sys variable
DECODE(pah.bid_scope_code, 'MUST_BID_ALL_ITEMS', 'N', 'Y')
selective_response_flag,
decode(pah.full_quantity_bid_code,'FULL_QTY_BIDS_REQD','Y','N')
full_quantity_response_flag,
decode(pah.bid_frequency_code,'MULTIPLE_BIDS_ALLOWED','Y','N')
multiple_responses_flag,
pah.multiple_rounds_flag multiple_rounds_flag,
pah.manual_close_flag manual_close_flag,
pah.manual_extend_flag manual_extend_flag,
pah.auto_extend_flag auto_extend_flag,
pah.price_driven_auction_flag prices_decrease_flag,
pah.amendment_description amendment_description,
-- Bug 4102993 --> add missing functional currency variable
pah.currency_code func_currency,
-- ECO 4241852 -- BUG 5087598
pah.po_style_id || '-' || pah.contract_type style_id
into
l_pon_sys_vars(28).variable_value_id,
l_pon_sys_vars(20).variable_value_id,
l_pon_sys_vars(13).variable_value_id,
l_pon_sys_vars(38).variable_value_id,
l_pon_sys_vars(37).variable_value_id,
l_pon_sys_vars( 7).variable_value_id,
l_pon_sys_vars(41).variable_value_id,
l_pon_sys_vars( 8).variable_value_id,
l_pon_sys_vars(14).variable_value_id,
l_pon_sys_vars( 1).variable_value_id,
l_pon_sys_vars( 2).variable_value_id,
l_pon_sys_vars(18).variable_value_id,
l_pon_sys_vars(30).variable_value_id,
l_pon_sys_vars(16).variable_value_id,
l_pon_sys_vars( 9).variable_value_id,
l_pon_sys_vars(15).variable_value_id,
l_pon_sys_vars( 4).variable_value_id,
l_pon_sys_vars( 3).variable_value_id,
l_pon_sys_vars(23).variable_value_id,
l_pon_sys_vars(24).variable_value_id,
l_pon_sys_vars(29).variable_value_id,
l_pon_sys_vars(40).variable_value_id,
l_pon_sys_vars(39).variable_value_id,
l_pon_sys_vars(32).variable_value_id,
l_pon_sys_vars(12).variable_value_id,
l_pon_sys_vars(27).variable_value_id,
l_pon_sys_vars(10).variable_value_id,
l_pon_sys_vars(31).variable_value_id,
l_pon_sys_vars(34).variable_value_id,
l_pon_sys_vars(11).variable_value_id,
l_pon_sys_vars(19).variable_value_id,
l_pon_sys_vars(35).variable_value_id,
l_pon_sys_vars(36).variable_value_id,
l_pon_sys_vars(17).variable_value_id,
l_pon_sys_vars(26).variable_value_id,
l_pon_sys_vars(25).variable_value_id,
l_pon_sys_vars(21).variable_value_id,
l_pon_sys_vars(22).variable_value_id,
l_pon_sys_vars( 6).variable_value_id,
l_pon_sys_vars(33).variable_value_id,
l_pon_sys_vars( 5).variable_value_id,
-- Bug 4102993
-- add the missing variable value.
l_pon_sys_vars(72).variable_value_id,
-- ECO 4241852
l_pon_sys_vars(73).variable_value_id
from
pon_auction_headers_all pah,
hr_all_organization_units ou
where
pah.auction_header_id = p_doc_id and
pah.org_id = ou.organization_id(+) and
nvl(ou.date_from(+),sysdate-1) < sysdate and
nvl(ou.date_to(+),sysdate+1) > sysdate ;
p_sys_var_tbl.delete(l_sys_var_index);
select
auction_header_id_prev_round,
auction_header_id_prev_amend
into
l_prev_round_id,
l_prev_amend_id
from
pon_auction_headers_all
where
auction_header_id = p_doc_id;
p_sys_var_tbl.delete(l_sys_var_index);
select
decode(pah1.org_id,pah2.org_id,'N','OKC$B_ORGANIZATION'),
decode(pon_conterms_utl_pvt.get_legal_entity_id(pah1.org_id),
pon_conterms_utl_pvt.get_legal_entity_id(pah2.org_id),
'N','OKC$B_LEGAL_ENTITY'),
decode(pah1.doctype_id,pah2.doctype_id,'N','OKC$B_DOCUMENT_TYPE'),
decode(pah1.document_number,pah2.document_number,'N','OKC$B_SOURCING_DOC_NUMBER'),
decode(pah1.ship_to_location_id,pah2.ship_to_location_id,'N','OKC$B_SHIP_TO_ADDRESS'),
decode(pah1.bill_to_location_id,pah2.bill_to_location_id,'N','OKC$B_BILL_TO_ADDRESS'),
decode(pah1.currency_code,pah2.currency_code,'N','OKC$B_TXN_CURRENCY'),
decode(pah1.trading_partner_contact_id,pah2.trading_partner_contact_id,'N','OKC$B_BUYER'),
decode(pah1.trading_partner_name,pah2.trading_partner_name,'N','OKC$B_ENTERPRISE_NAME'),
decode(pah1.po_agreed_amount*nvl(pah1.rate,1),pah2.po_agreed_amount*nvl(pah2.rate,1),'N','OKC$B_AGREEMENT_AMOUNT_FUNC'),
decode(pah1.po_agreed_amount*nvl(pah1.rate,1),pah2.po_agreed_amount*nvl(pah2.rate,1),'N','OKC$B_AGREEMENT_AMOUNT_TXN'),
decode(pah1.global_agreement_flag,pah2.global_agreement_flag,'N','OKC$B_GLOBAL_FLAG'),
decode(pah1.payment_terms_id,pah2.payment_terms_id,'N','OKC$B_PAYMENT_TERMS'),
decode(pah1.freight_terms_code,pah2.freight_terms_code,'N','OKC$B_FREIGHT_TERMS'),
decode(pah1.carrier_code,pah2.carrier_code,'N','OKC$B_CARRIER'),
decode(pah1.fob_code,pah2.fob_code,'N','OKC$B_FOB'),
decode(pah1.po_start_date,pah2.po_start_date,'N','OKC$B_AGREEMENT_START_DATE'),
decode(pah1.po_end_date,pah2.po_end_date,'N','OKC$B_AGREEMENT_END_DATE'),
decode(pah1.po_min_rel_amount*nvl(pah1.rate,1),pah2.po_min_rel_amount*nvl(pah2.rate,1),'N','OKC$B_MINIMUM_RELEASE_AMT_FUNC'),
decode(pah1.po_min_rel_amount*nvl(pah1.rate,1),pah2.po_min_rel_amount*nvl(pah2.rate,1),'N','OKC$B_MINIMUM_RELEASE_AMT_TXN'),
decode(pah1.contract_type,pah2.contract_type,'N','OKC$B_OUTCOME'),
decode(pah1.auction_title,pah2.auction_title,'N','OKC$B_TITLE'),
decode(pah1.bid_visibility_code,pah2.bid_visibility_code,'N','OKC$B_STYLE'),
decode(pah1.bid_ranking,pah2.bid_ranking,'N','OKC$B_RESPONSE_RANKING'),
decode(pah1.hdr_attr_display_score,pah2.hdr_attr_display_score,'N','OKC$B_DISPLAY_SCORING_CRITERIA'),
decode(pah1.open_bidding_date,pah2.open_bidding_date,'N','OKC$B_OPEN_RESPONSE_DATE'),
decode(pah1.close_bidding_date,pah2.close_bidding_date,'N','OKC$B_CLOSE_RESPONSE_DATE'),
decode(pah1.view_by_date,pah2.view_by_date,'N','OKC$B_PREVIEW_DATE'),
decode(pah1.award_by_date,pah2.award_by_date,'N','OKC$B_SCHEDULED_AWARD_DATE'),
decode(pah1.allow_other_bid_currency_flag,pah2.allow_other_bid_currency_flag,'N','OKC$B_CURRNCY_RESPONSE_FLAG'),
decode(pah1.bid_list_type,pah2.bid_list_type,'N','OKC$B_INVITATION_ONLY_FLAG'),
decode(pah1.show_bidder_notes, pah2.show_bidder_notes,'N','OKC$B_SEE_OTHER_RESPONSE_FLAG'),
decode(pah1.bid_scope_code, pah2.bid_scope_code,'N','OKC$B_SELECTIVE_RESPONSE_FLAG'),
decode(pah1.full_quantity_bid_code,pah2.full_quantity_bid_code,'N','OKC$B_FULL_QTY_RSPONS_FLAG'),
decode(pah1.bid_frequency_code,pah2.bid_frequency_code,'N','OKC$B_MUTI_RSP_ALLOWED_FLAG'),
decode(pah1.multiple_rounds_flag,pah2.multiple_rounds_flag,'N','OKC$B_MUTI_ROUNDS_ALLOWED_FLAG'),
decode(pah1.manual_close_flag,pah2.manual_close_flag,'N','OKC$B_MANU_CLOSE_ALLOWED_FLAG'),
decode(pah1.manual_extend_flag,pah2.manual_extend_flag,'N','OKC$B_MANU_EXTEND_ALLOWED_FLAG'),
decode(pah1.auto_extend_flag,pah2.auto_extend_flag,'N','OKC$B_AUTO_EXTEND_ALLOWED_FLAG'),
decode(pah1.price_driven_auction_flag,pah2.price_driven_auction_flag,'N','OKC$B_RSPONS_PRICE_MUST_DEC'),
decode(pah1.amendment_description,pah2.amendment_description,'N','OKC$B_AMENDMENT_DESCRIPTION'),
decode(pah1.currency_code, pah2.currency_code, 'N', 'OKC$B_FUNC_CURRENCY'),
-- ECO 4241852 -- BUG 5087598 --> no changes here
decode(pah1.po_style_id,pah2.po_style_id,'N','OKC$B_OUTCOME_PO_STYLE')
into
l_pon_sys_vars( 1),
l_pon_sys_vars( 2),
l_pon_sys_vars( 3),
l_pon_sys_vars( 4),
l_pon_sys_vars( 5),
l_pon_sys_vars( 6),
l_pon_sys_vars( 7),
l_pon_sys_vars( 8),
l_pon_sys_vars( 9),
l_pon_sys_vars(10),
l_pon_sys_vars(11),
l_pon_sys_vars(12),
l_pon_sys_vars(13),
l_pon_sys_vars(14),
l_pon_sys_vars(15),
l_pon_sys_vars(16),
l_pon_sys_vars(17),
l_pon_sys_vars(18),
l_pon_sys_vars(19),
l_pon_sys_vars(20),
l_pon_sys_vars(21),
l_pon_sys_vars(22),
l_pon_sys_vars(23),
l_pon_sys_vars(24),
l_pon_sys_vars(25),
l_pon_sys_vars(26),
l_pon_sys_vars(27),
l_pon_sys_vars(28),
l_pon_sys_vars(29),
l_pon_sys_vars(30),
l_pon_sys_vars(31),
l_pon_sys_vars(32),
l_pon_sys_vars(33),
l_pon_sys_vars(34),
l_pon_sys_vars(35),
l_pon_sys_vars(36),
l_pon_sys_vars(37),
l_pon_sys_vars(38),
l_pon_sys_vars(39),
l_pon_sys_vars(40),
l_pon_sys_vars(41),
l_pon_sys_vars(42),
-- ECO 4241852
l_pon_sys_vars(43)
from
pon_auction_headers_all pah1,
pon_auction_headers_all pah2,
hr_all_organization_units ou1,
hr_all_organization_units ou2
where
pah1.auction_header_id = p_doc_id and
pah1.org_id = ou1.organization_id(+) and
nvl(ou1.date_from(+),sysdate-1) < sysdate and
nvl(ou1.date_to(+),sysdate+1) > sysdate and
pah2.auction_header_id = l_prev_header_id and
pah2.org_id = ou2.organization_id(+) and
nvl(ou2.date_from(+),sysdate-1) < sysdate and
nvl(ou2.date_to(+),sysdate+1) > sysdate ;
l_pon_sys_vars.delete(l_pon_var_index);
p_sys_var_tbl.delete(l_sys_var_index);
x_category_tbl.delete();
x_item_tbl.delete();
select
paip.category_name
bulk collect into
l_category_tbl
from
pon_auction_item_prices_all paip
where
paip.auction_header_id = l_auction_header_id
and paip.category_name is not null;
select
paip.item_number
bulk collect into
l_item_tbl
from
pon_auction_item_prices_all paip
where
paip.auction_header_id = l_auction_header_id
and paip.item_number is not null;