[Home] [Help]
PACKAGE BODY: APPS.PON_CONTERMS_UTL_GRP
Source
1 PACKAGE BODY PON_CONTERMS_UTL_GRP as
2 /* $Header: PONCTDVB.pls 120.7.12020000.3 2013/02/20 05:29:51 hvutukur ship $ */
3
4 g_package_name VARCHAR2(30) := 'pon_conterms_utl_grp';
5
6 FUNCTION is_contracts_installed RETURN VARCHAR2 IS
7
8 BEGIN
9
10 return PON_CONTERMS_UTL_PVT.is_contracts_installed();
11
12 EXCEPTION
13 WHEN OTHERS THEN
14 RAISE;
15 END is_contracts_installed;
16
17
18 FUNCTION get_document_sub_type ( p_doctype_id IN NUMBER)
19 RETURN VARCHAR2 is
20
21 l_doc_sub_type VARCHAR2(40);--PON_AUC_DOCTYPES%DOCUMENT_SUBTYPE;
22 BEGIN
23 select document_subtype into l_doc_sub_type from pon_auc_doctypes where doctype_id = p_doctype_id;
24 return l_doc_sub_type;
25 END get_document_sub_type;
26
27
28
29 FUNCTION get_contracts_document_type(
30 p_doctype_id IN NUMBER,
31 p_is_response IN VARCHAR2)
32 RETURN VARCHAR2 IS
33
34 BEGIN
35 IF (p_is_response = 'Y') THEN
36 return pon_conterms_utl_pvt.get_response_doc_type(p_doctype_id);
37 ELSE
38 return pon_conterms_utl_pvt.get_negotiation_doc_type(p_doctype_id);
39 END IF;
40 END get_contracts_document_type;
41
42
43 PROCEDURE ok_to_commit(
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
46 p_doctype_id IN VARCHAR2,
47 p_doc_id IN NUMBER,
48 x_update_allowed OUT NOCOPY VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER
52 ) IS
53 l_api_version NUMBER := 1;
54 l_api_name VARCHAR2(30) := 'IS_OK_TO_COMMIT';
55 l_auction_header_id pon_auction_headers_all.auction_header_id%type;
56 l_auction_status pon_auction_headers_all.auction_status%type;
57 BEGIN
58 -- Initialize API return status to unexpected error
59 x_update_allowed := fnd_api.g_false;
60 x_return_status := fnd_api.g_ret_sts_unexp_error;
61 x_msg_data := 'pon_conterms_utl_grp.ok_to_commit() unexpected error';
62 x_msg_count := 1;
63
64 -- Standard call to check for call compatibility.
65 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
66 l_api_name, l_api_name) THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 IF FND_API.to_Boolean(p_init_msg_list) THEN
72 FND_MSG_PUB.initialize();
73 END IF;
74
75 -- get auction_header_id
76 pon_conterms_utl_pvt.get_auction_header_id(p_doctype_id,
77 p_doc_id,
78 l_auction_header_id,
79 x_return_status,
80 x_msg_data,
81 x_msg_count);
82 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
83 return ;
84 END IF;
85
86 /*ELINs & CDRLs project : When updating cdrl's in offers, no need
87 * to perform below validation.
88 */
89 IF p_doctype_id NOT IN (PON_CONTERMS_UTL_PVT.OFFER) THEN
90 -- check whether the current user holds the lock on the draft
91 BEGIN
92 -- this is just an existance test but we have to select x into y
93 -- to pass the pl/sql compiler so...
94 select
95 auction.auction_status
96 into
97 l_auction_status
98 from
99 fnd_user,
100 hz_parties user_parties,
101 hz_parties company_parties,
102 hz_relationships,
103 hz_code_assignments,
104 pon_auction_headers_all auction
105 where
106 fnd_user.user_id = fnd_global.user_id()
107 and fnd_user.person_party_id = user_parties.party_id
108 and hz_relationships.object_id = company_parties.party_id
109 and hz_relationships.subject_id = user_parties.party_id
110 and hz_relationships.relationship_type = 'POS_EMPLOYMENT'
111 and hz_relationships.relationship_code = 'EMPLOYEE_OF'
112 and hz_relationships.start_date <= SYSDATE
113 and hz_relationships.end_date >= SYSDATE
114 and hz_code_assignments.owner_table_id = company_parties.party_id
115 and hz_code_assignments.owner_table_name = 'HZ_PARTIES'
116 and hz_code_assignments.class_category = 'POS_PARTICIPANT_TYPE'
117 and hz_code_assignments.class_code = 'ENTERPRISE'
118 and auction.auction_header_id = l_auction_header_id
119 and auction.draft_locked = 'Y'
120 and auction.draft_locked_by_contact_id = user_parties.party_id
121 and auction.trading_partner_id = company_parties.party_id
122 and auction.auction_status = 'DRAFT';
123 EXCEPTION
124 WHEN no_data_found THEN
125 x_update_allowed := fnd_api.g_false;
126 x_return_status := fnd_api.g_ret_sts_success;
127 x_msg_data := 'pon_conterms_utl_grp.ok_to_commit() - user doesn''t have lock for draft: ' || l_auction_header_id;
128 x_msg_count := 1;
129 return;
130 END;
131 END IF;
132
133 x_update_allowed := fnd_api.g_true;
134 x_return_status := fnd_api.g_ret_sts_success;
135 x_msg_data := 'Success!';
136 x_msg_count := 1;
137 END ok_to_commit;
138
139 /*
140 * Procedure:
141 * get_article_variable_values
142 *
143 * Purpose:
144 * This API will be called by Contracts to get values of system variables
145 * used in Contract terms configurator rules.
146 *
147 * Parameters:
148 * IN:
149 * p_api_version
150 * API version number expected by the caller
151 * p_init_msg_list
152 * Initialize message list
153 * p_doctype_id
154 * Contracts Doc Type; one of 'RFQ', 'RFQ_RESPONSE', etc
155 * p_doc_id
156 * pon_auction_headers_all.auction_header_id
157 * IN OUT:
158 * p_sys_var_value_tbl
159 * A table of records to hold the system variable codes and values
160 * OUT:
161 * x_msg_count
162 * message count
163 * x_msg_data
164 * message data
165 * x_return_status
166 * Status Returned to calling API. Possible values are following
167 * FND_API.G_RET_STS_ERROR - for expected error
168 * FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
169 * FND_API.G_RET_STS_SUCCESS - for success
170 */
171 PROCEDURE get_article_variable_values(
172 p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
174 p_doctype_id IN VARCHAR2,
175 p_doc_id IN NUMBER,
176 p_sys_var_value_tbl IN OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_data OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER
180 ) IS
181 l_api_name VARCHAR2(60) := g_package_name || '.get_article_variable_values';
182 l_api_version NUMBER := 1.0;
183 l_pon_sys_vars OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
184 l_sys_var_index BINARY_INTEGER;
185 l_pon_var_index BINARY_INTEGER;
186 l_progress NUMBER := 0;
187 l_dummy_value VARCHAR2(10) := 'NOT_NULL';
188 BEGIN
189 -- Initialize API return status to unexpected error
190 x_return_status := fnd_api.g_ret_sts_unexp_error;
191 x_msg_data := 'pon_conterms_utl_grp.get_article_variable_values() unexpected error';
192 x_msg_count := 1;
193
194 -- Standard call to check for call compatibility.
195 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
196 l_api_name, l_api_name) THEN
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 END IF;
199
200 l_progress := 50;
201
202 -- Initialize message list if p_init_msg_list is set to TRUE.
203 IF FND_API.to_Boolean(p_init_msg_list) THEN
204 FND_MSG_PUB.initialize();
205 END IF;
206
207 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
208 fnd_log.string(fnd_log.level_statement,
209 l_api_name,
210 'called ' || l_api_name);
211 end if;
212
213 -- bug 3264980
214 -- if we're not passed and variables, then return immediately
215 if (p_sys_var_value_tbl is null OR
216 p_sys_var_value_tbl.count <= 0) then
217 x_return_status := fnd_api.g_ret_sts_success;
218 x_msg_data := null;
219 x_msg_count := 0;
220 return ;
221 end if;
222
223 -- determine which query to execute depending on the doctype
224 if (p_doctype_id = PON_CONTERMS_UTL_PVT.BID or
225 p_doctype_id = PON_CONTERMS_UTL_PVT.QUOTE or
226 p_doctype_id = PON_CONTERMS_UTL_PVT.OFFER or
227 p_doctype_id = PON_CONTERMS_UTL_PVT.RESPONSE) then
228
229 -- this is a response
230
231 l_progress := 101;
232
233 l_pon_sys_vars( 1).variable_code:= 'OKC$B_AGREEMENT_AMOUNT_FUNC';
234 l_pon_sys_vars( 2).variable_code:= 'OKC$B_AGREEMENT_AMOUNT_TXN';
235 l_pon_sys_vars( 3).variable_code:= 'OKC$B_AGREEMENT_END_DATE';
236 l_pon_sys_vars( 4).variable_code:= 'OKC$B_AGREEMENT_START_DATE';
237 l_pon_sys_vars( 5).variable_code:= 'OKC$B_AMENDMENT_DESCRIPTION';
238 l_pon_sys_vars( 6).variable_code:= 'OKC$B_AUTO_EXTEND_ALLOWED_FLAG';
239 l_pon_sys_vars( 7).variable_code:= 'OKC$B_BILL_TO_ADDRESS';
240 l_pon_sys_vars( 8).variable_code:= 'OKC$B_BUYER';
241 l_pon_sys_vars( 9).variable_code:= 'OKC$B_CARRIER';
242 l_pon_sys_vars(10).variable_code:= 'OKC$B_CLOSE_RESPONSE_DATE';
243 l_pon_sys_vars(11).variable_code:= 'OKC$B_CURRNCY_RESPONSE_FLAG';
244 l_pon_sys_vars(12).variable_code:= 'OKC$B_DISPLAY_SCORING_CRITERIA';
245 l_pon_sys_vars(13).variable_code:= 'OKC$B_DOCUMENT_TYPE';
246 l_pon_sys_vars(14).variable_code:= 'OKC$B_ENTERPRISE_NAME';
247 l_pon_sys_vars(15).variable_code:= 'OKC$B_FOB';
248 l_pon_sys_vars(16).variable_code:= 'OKC$B_FREIGHT_TERMS';
249 l_pon_sys_vars(17).variable_code:= 'OKC$B_FULL_QTY_RSPONS_FLAG';
250 l_pon_sys_vars(18).variable_code:= 'OKC$B_INVITATION_ONLY_FLAG';
251 l_pon_sys_vars(19).variable_code:= 'OKC$B_LEGAL_ENTITY';
252 l_pon_sys_vars(20).variable_code:= 'OKC$B_MANU_CLOSE_ALLOWED_FLAG';
253 l_pon_sys_vars(21).variable_code:= 'OKC$B_MANU_EXTEND_ALLOWED_FLAG';
254 l_pon_sys_vars(22).variable_code:= 'OKC$B_MINIMUM_RELEASE_AMT_FUNC';
255 l_pon_sys_vars(23).variable_code:= 'OKC$B_MINIMUM_RELEASE_AMT_TXN';
256 l_pon_sys_vars(24).variable_code:= 'OKC$B_MUTI_ROUNDS_ALLOWED_FLAG';
257 l_pon_sys_vars(25).variable_code:= 'OKC$B_MUTI_RSP_ALLOWED_FLAG';
258 l_pon_sys_vars(26).variable_code:= 'OKC$B_OPEN_RESPONSE_DATE';
259 l_pon_sys_vars(27).variable_code:= 'OKC$B_ORGANIZATION';
260 l_pon_sys_vars(28).variable_code:= 'OKC$B_OUTCOME';
261 l_pon_sys_vars(29).variable_code:= 'OKC$B_PAYMENT_TERMS';
262 l_pon_sys_vars(30).variable_code:= 'OKC$B_PREVIEW_DATE';
263 l_pon_sys_vars(31).variable_code:= 'OKC$B_RESPONSE_CURRENCY';
264 l_pon_sys_vars(32).variable_code:= 'OKC$B_RESPONSE_NUMBER';
265 l_pon_sys_vars(33).variable_code:= 'OKC$B_RESPONSE_RANKING';
266 l_pon_sys_vars(34).variable_code:= 'OKC$B_RSPONS_PRICE_MUST_DEC';
267 l_pon_sys_vars(35).variable_code:= 'OKC$B_SCHEDULED_AWARD_DATE';
268 l_pon_sys_vars(36).variable_code:= 'OKC$B_SEE_OTHER_RESPONSE_FLAG';
269 l_pon_sys_vars(37).variable_code:= 'OKC$B_SELECTIVE_RESPONSE_FLAG';
270 l_pon_sys_vars(38).variable_code:= 'OKC$B_SHIP_TO_ADDRESS';
271 l_pon_sys_vars(39).variable_code:= 'OKC$B_SOURCING_DOC_NUMBER';
272 l_pon_sys_vars(40).variable_code:= 'OKC$B_STYLE';
273 l_pon_sys_vars(41).variable_code:= 'OKC$B_SUPPLIER_CONTACT';
274 l_pon_sys_vars(42).variable_code:= 'OKC$B_SUPPLIER_NAME';
275 l_pon_sys_vars(43).variable_code:= 'OKC$B_TITLE';
276 l_pon_sys_vars(44).variable_code:= 'OKC$B_TXN_CURRENCY';
277
278 --------------------------------------------------------------------
279 -- BUG 3250745 (3240942)
280 -- Return dummy values for values not in PON tables.
281 -- Otherwise, errors are generated in qa_doc()
282 --------------------------------------------------------------------
283
284 l_pon_sys_vars(45).variable_code:= 'OKC$B_BILL_TO_ADDR_STYLE';
285 l_pon_sys_vars(45).variable_value_id:= l_dummy_value;
286 l_pon_sys_vars(46).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR';
287 l_pon_sys_vars(46).variable_value_id:= l_dummy_value;
288 l_pon_sys_vars(47).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_1';
289 l_pon_sys_vars(47).variable_value_id:= l_dummy_value;
290 l_pon_sys_vars(48).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_2';
291 l_pon_sys_vars(48).variable_value_id:= l_dummy_value;
292 l_pon_sys_vars(49).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_3';
293 l_pon_sys_vars(49).variable_value_id:= l_dummy_value;
294 l_pon_sys_vars(50).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_STYLE';
295 l_pon_sys_vars(50).variable_value_id:= l_dummy_value;
296 l_pon_sys_vars(51).variable_code:= 'OKC$B_LEGAL_ENTITY_CITY';
297 l_pon_sys_vars(51).variable_value_id:= l_dummy_value;
298 l_pon_sys_vars(52).variable_code:= 'OKC$B_LEGAL_ENTITY_COUNTRY';
299 l_pon_sys_vars(52).variable_value_id:= l_dummy_value;
300 l_pon_sys_vars(53).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION1';
301 l_pon_sys_vars(53).variable_value_id:= l_dummy_value;
302 l_pon_sys_vars(54).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION2';
303 l_pon_sys_vars(54).variable_value_id:= l_dummy_value;
304 l_pon_sys_vars(55).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION3';
305 l_pon_sys_vars(55).variable_value_id:= l_dummy_value;
306 l_pon_sys_vars(56).variable_code:= 'OKC$B_LEGAL_ENTITY_ZIP';
307 l_pon_sys_vars(56).variable_value_id:= l_dummy_value;
308 l_pon_sys_vars(57).variable_code:= 'OKC$B_ORGANIZATION_ADDR';
309 l_pon_sys_vars(57).variable_value_id:= l_dummy_value;
310 l_pon_sys_vars(58).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_1';
311 l_pon_sys_vars(58).variable_value_id:= l_dummy_value;
312 l_pon_sys_vars(59).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_2';
313 l_pon_sys_vars(59).variable_value_id:= l_dummy_value;
314 l_pon_sys_vars(60).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_3';
315 l_pon_sys_vars(60).variable_value_id:= l_dummy_value;
316 l_pon_sys_vars(61).variable_code:= 'OKC$B_ORGANIZATION_ADDR_STYLE';
317 l_pon_sys_vars(61).variable_value_id:= l_dummy_value;
318 l_pon_sys_vars(62).variable_code:= 'OKC$B_ORGANIZATION_CITY';
319 l_pon_sys_vars(62).variable_value_id:= l_dummy_value;
320 l_pon_sys_vars(63).variable_code:= 'OKC$B_ORGANIZATION_COUNTRY';
321 l_pon_sys_vars(63).variable_value_id:= l_dummy_value;
322 l_pon_sys_vars(64).variable_code:= 'OKC$B_ORGANIZATION_REGION1';
323 l_pon_sys_vars(64).variable_value_id:= l_dummy_value;
324 l_pon_sys_vars(65).variable_code:= 'OKC$B_ORGANIZATION_REGION2';
325 l_pon_sys_vars(65).variable_value_id:= l_dummy_value;
326 l_pon_sys_vars(66).variable_code:= 'OKC$B_ORGANIZATION_REGION3';
327 l_pon_sys_vars(66).variable_value_id:= l_dummy_value;
328 l_pon_sys_vars(67).variable_code:= 'OKC$B_ORGANIZATION_ZIP';
329 l_pon_sys_vars(67).variable_value_id:= l_dummy_value;
330 l_pon_sys_vars(68).variable_code:= 'OKC$B_SHIP_TO_ADDR_STYLE';
331 l_pon_sys_vars(68).variable_value_id:= l_dummy_value;
332 l_pon_sys_vars(69).variable_code:= 'OKC$B_SUPPLIER_CLASSIFICATION';
333 l_pon_sys_vars(69).variable_value_id:= l_dummy_value;
334 l_pon_sys_vars(70).variable_code:= 'OKC$B_SUPPLIER_MINORITY_TYPE';
335 l_pon_sys_vars(70).variable_value_id:= l_dummy_value;
336
337 -- Bug 4102993
338 l_pon_sys_vars(71).variable_code := 'OKC$B_FUNC_CURRENCY';
339
340 -- ECO 4241852
341 l_pon_sys_vars(72).variable_code := 'OKC$B_OUTCOME_PO_STYLE';
342
343 begin
344 select
345 pah.org_id organization,
346 pon_conterms_utl_pvt.get_legal_entity_id(pah.org_id) entity,
347 -- Bug 4099936
348 -- Decode the doctype_id to the internal name as in PO valueset for the variable POC_XPRT_DOC_TYPE
349 -- Note that this piece of code may not be used as of today since Contract expert is not used for
350 -- response documents
351 DECODE(pah.doctype_id, 21, 'SOURCING RFI', 5, 'SOURCING RFQ', 1, 'BUYER AUCTION','SOLICITATION') document_type,
352 --get_document_sub_type(pah.doctype_id) document_type,
353 pah.document_number document_number,
354 pah.ship_to_location_id ship_to_address,
355 pah.bill_to_location_id bill_to_address,
356 pah.currency_code currency,
357 pah.trading_partner_contact_id buyer,
358 pah.trading_partner_name enterprise_name,
359 pah.po_agreed_amount * nvl(pah.rate, 1)
360 agreement_amount1,
361 pah.po_agreed_amount * nvl(pah.rate, 1)
362 agreement_amount2,
363 pah.payment_terms_id payment_terms,
364 pah.freight_terms_code freight_terms,
365 pah.carrier_code carrier,
366 pah.fob_code fob,
367 pah.po_start_date agreement_start_date,
368 pah.po_end_date agreement_end_date,
369 pah.po_min_rel_amount * nvl(pah.rate, 1)
370 minimum_release_amount1,
371 pah.po_min_rel_amount * nvl(pah.rate, 1)
372 minimum_release_amount1,
373 pah.contract_type outcome,
374 pah.auction_title title,
375 pah.bid_visibility_code style,
376 pah.bid_ranking response_ranking,
377 pah.hdr_attr_display_score display_criteria,
378 pah.open_bidding_date open_response_date,
379 pah.close_bidding_date close_response_date,
380 pah.view_by_date preview_date,
381 pah.award_by_date award_date,
382 pah.allow_other_bid_currency_flag
383 currency_response_flag,
384 decode(pah.bid_list_type,'PRIVATE_BID_LIST','Y','N')
385 invitation_only_flag,
386 pah.show_bidder_notes supplier_response_flag,
387 -- Bug 4099936
388 -- decode control settings to Y/N to match the valueset
389 DECODE(pah.bid_scope_code, 'MUST_BID_ALL_ITEMS', 'N', 'Y')
390 selective_response_flag,
391 decode(pah.full_quantity_bid_code,'FULL_QTY_BIDS_REQD','Y','N')
392 full_quantity_response_flag,
393 decode(pah.bid_frequency_code,'MULTIPLE_BIDS_ALLOWED','Y','N')
394 multiple_responses_flag,
395 pah.multiple_rounds_flag multiple_rounds_flag,
396 pah.manual_close_flag manual_close_flag,
397 pah.manual_extend_flag manual_extend_flag,
398 pah.auto_extend_flag auto_extend_flag,
399 pah.price_driven_auction_flag prices_decrease_flag,
400 pah.amendment_description amendment_description,
401 pbh.trading_partner_name supplier_name,
402 pbh.trading_partner_contact_id supplier_contact,
403 pbh.bid_number response_number,
404 pbh.bid_currency_code response_currency,
405 pah.currency_code func_currency,
406 -- ECO 4241852 -- BUG 5087598
407 pah.po_style_id || '-' || pah.contract_type style_id
408 into
409 l_pon_sys_vars(27).variable_value_id,
410 l_pon_sys_vars(19).variable_value_id,
411 l_pon_sys_vars(13).variable_value_id,
412 l_pon_sys_vars(39).variable_value_id,
413 l_pon_sys_vars(38).variable_value_id,
414 l_pon_sys_vars( 7).variable_value_id,
415 l_pon_sys_vars(44).variable_value_id,
416 l_pon_sys_vars( 8).variable_value_id,
417 l_pon_sys_vars(14).variable_value_id,
418 l_pon_sys_vars( 1).variable_value_id,
419 l_pon_sys_vars( 2).variable_value_id,
420 l_pon_sys_vars(29).variable_value_id,
421 l_pon_sys_vars(16).variable_value_id,
422 l_pon_sys_vars( 9).variable_value_id,
423 l_pon_sys_vars(15).variable_value_id,
424 l_pon_sys_vars( 4).variable_value_id,
425 l_pon_sys_vars( 3).variable_value_id,
426 l_pon_sys_vars(22).variable_value_id,
427 l_pon_sys_vars(23).variable_value_id,
428 l_pon_sys_vars(28).variable_value_id,
429 l_pon_sys_vars(43).variable_value_id,
430 l_pon_sys_vars(40).variable_value_id,
431 l_pon_sys_vars(33).variable_value_id,
432 l_pon_sys_vars(12).variable_value_id,
433 l_pon_sys_vars(26).variable_value_id,
434 l_pon_sys_vars(10).variable_value_id,
435 l_pon_sys_vars(30).variable_value_id,
436 l_pon_sys_vars(35).variable_value_id,
437 l_pon_sys_vars(11).variable_value_id,
438 l_pon_sys_vars(18).variable_value_id,
439 l_pon_sys_vars(36).variable_value_id,
440 l_pon_sys_vars(37).variable_value_id,
441 l_pon_sys_vars(17).variable_value_id,
442 l_pon_sys_vars(25).variable_value_id,
443 l_pon_sys_vars(24).variable_value_id,
444 l_pon_sys_vars(20).variable_value_id,
445 l_pon_sys_vars(21).variable_value_id,
446 l_pon_sys_vars( 6).variable_value_id,
447 l_pon_sys_vars(34).variable_value_id,
448 l_pon_sys_vars( 5).variable_value_id,
449 l_pon_sys_vars(42).variable_value_id,
450 l_pon_sys_vars(41).variable_value_id,
451 l_pon_sys_vars(32).variable_value_id,
452 l_pon_sys_vars(31).variable_value_id,
453 l_pon_sys_vars(71).variable_value_id,
454 -- ECO 4241852
455 l_pon_sys_vars(72).variable_value_id
456 from
457 pon_auction_headers_all pah,
458 pon_bid_headers pbh,
459 hr_all_organization_units ou
460 where
461 pbh.bid_number = p_doc_id and
462 pbh.auction_header_id = pah.auction_header_id and
463 pah.org_id = ou.organization_id(+) and
464 nvl(ou.date_from(+),sysdate-1) < sysdate and
465 nvl(ou.date_to(+),sysdate+1) > sysdate ;
466 exception
467 when no_data_found then
468 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
469 fnd_log.string(fnd_log.level_exception,
470 l_api_name,
471 'no data found for ' || p_doc_id);
472 end if;
473
474 x_msg_data := 'no data found for ' || p_doc_id;
475 x_return_status := fnd_api.g_ret_sts_error;
476 return;
477 end;
478 elsif (p_doctype_id = PON_CONTERMS_UTL_PVT.AUCTION or
479 p_doctype_id = PON_CONTERMS_UTL_PVT.REQUEST_FOR_QUOTE or
480 p_doctype_id = PON_CONTERMS_UTL_PVT.SOLICITATION or
481 p_doctype_id = PON_CONTERMS_UTL_PVT.REQUEST_FOR_INFORMATION) then
482
483 -- this is an auction
484
485 l_progress := 102;
486
487 l_pon_sys_vars( 1).variable_code:= 'OKC$B_AGREEMENT_AMOUNT_FUNC';
488 l_pon_sys_vars( 2).variable_code:= 'OKC$B_AGREEMENT_AMOUNT_TXN';
489 l_pon_sys_vars( 3).variable_code:= 'OKC$B_AGREEMENT_END_DATE';
490 l_pon_sys_vars( 4).variable_code:= 'OKC$B_AGREEMENT_START_DATE';
491 l_pon_sys_vars( 5).variable_code:= 'OKC$B_AMENDMENT_DESCRIPTION';
492 l_pon_sys_vars( 6).variable_code:= 'OKC$B_AUTO_EXTEND_ALLOWED_FLAG';
493 l_pon_sys_vars( 7).variable_code:= 'OKC$B_BILL_TO_ADDRESS';
494 l_pon_sys_vars( 8).variable_code:= 'OKC$B_BUYER';
495 l_pon_sys_vars( 9).variable_code:= 'OKC$B_CARRIER';
496 l_pon_sys_vars(10).variable_code:= 'OKC$B_CLOSE_RESPONSE_DATE';
497 l_pon_sys_vars(11).variable_code:= 'OKC$B_CURRNCY_RESPONSE_FLAG';
498 l_pon_sys_vars(12).variable_code:= 'OKC$B_DISPLAY_SCORING_CRITERIA';
499 l_pon_sys_vars(13).variable_code:= 'OKC$B_DOCUMENT_TYPE';
500 l_pon_sys_vars(14).variable_code:= 'OKC$B_ENTERPRISE_NAME';
501 l_pon_sys_vars(15).variable_code:= 'OKC$B_FOB';
502 l_pon_sys_vars(16).variable_code:= 'OKC$B_FREIGHT_TERMS';
503 l_pon_sys_vars(17).variable_code:= 'OKC$B_FULL_QTY_RSPONS_FLAG';
504 l_pon_sys_vars(18).variable_code:= 'OKC$B_GLOBAL_FLAG';
505 l_pon_sys_vars(19).variable_code:= 'OKC$B_INVITATION_ONLY_FLAG';
506 l_pon_sys_vars(20).variable_code:= 'OKC$B_LEGAL_ENTITY';
507 l_pon_sys_vars(21).variable_code:= 'OKC$B_MANU_CLOSE_ALLOWED_FLAG';
508 l_pon_sys_vars(22).variable_code:= 'OKC$B_MANU_EXTEND_ALLOWED_FLAG';
509 l_pon_sys_vars(23).variable_code:= 'OKC$B_MINIMUM_RELEASE_AMT_FUNC';
510 l_pon_sys_vars(24).variable_code:= 'OKC$B_MINIMUM_RELEASE_AMT_TXN';
511 l_pon_sys_vars(25).variable_code:= 'OKC$B_MUTI_ROUNDS_ALLOWED_FLAG';
512 l_pon_sys_vars(26).variable_code:= 'OKC$B_MUTI_RSP_ALLOWED_FLAG';
513 l_pon_sys_vars(27).variable_code:= 'OKC$B_OPEN_RESPONSE_DATE';
514 l_pon_sys_vars(28).variable_code:= 'OKC$B_ORGANIZATION';
515 l_pon_sys_vars(29).variable_code:= 'OKC$B_OUTCOME';
516 l_pon_sys_vars(30).variable_code:= 'OKC$B_PAYMENT_TERMS';
517 l_pon_sys_vars(31).variable_code:= 'OKC$B_PREVIEW_DATE';
518 l_pon_sys_vars(32).variable_code:= 'OKC$B_RESPONSE_RANKING';
519 l_pon_sys_vars(33).variable_code:= 'OKC$B_RSPONS_PRICE_MUST_DEC';
520 l_pon_sys_vars(34).variable_code:= 'OKC$B_SCHEDULED_AWARD_DATE';
521 l_pon_sys_vars(35).variable_code:= 'OKC$B_SEE_OTHER_RESPONSE_FLAG';
522 l_pon_sys_vars(36).variable_code:= 'OKC$B_SELECTIVE_RESPONSE_FLAG';
523 l_pon_sys_vars(37).variable_code:= 'OKC$B_SHIP_TO_ADDRESS';
524 l_pon_sys_vars(38).variable_code:= 'OKC$B_SOURCING_DOC_NUMBER';
525 l_pon_sys_vars(39).variable_code:= 'OKC$B_STYLE';
526 l_pon_sys_vars(40).variable_code:= 'OKC$B_TITLE';
527 l_pon_sys_vars(41).variable_code:= 'OKC$B_TXN_CURRENCY';
528
529 --------------------------------------------------------------------
530 -- BUG 3250745 (3240942)
531 -- Return dummy values for values not in PON tables.
532 -- Otherwise, errors are generated in qa_doc()
533 --------------------------------------------------------------------
534
535 l_pon_sys_vars(42).variable_code:= 'OKC$B_RESPONSE_CURRENCY';
536 l_pon_sys_vars(42).variable_value_id:= l_dummy_value;
537 l_pon_sys_vars(43).variable_code:= 'OKC$B_RESPONSE_NUMBER';
538 l_pon_sys_vars(43).variable_value_id:= l_dummy_value;
539 l_pon_sys_vars(44).variable_code:= 'OKC$B_SUPPLIER_CONTACT';
540 l_pon_sys_vars(44).variable_value_id:= l_dummy_value;
541 l_pon_sys_vars(45).variable_code:= 'OKC$B_SUPPLIER_NAME';
542 l_pon_sys_vars(45).variable_value_id:= l_dummy_value;
543 l_pon_sys_vars(46).variable_code:= 'OKC$B_BILL_TO_ADDR_STYLE';
544 l_pon_sys_vars(46).variable_value_id:= l_dummy_value;
545 l_pon_sys_vars(47).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR';
546 l_pon_sys_vars(47).variable_value_id:= l_dummy_value;
547 l_pon_sys_vars(48).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_1';
548 l_pon_sys_vars(48).variable_value_id:= l_dummy_value;
549 l_pon_sys_vars(49).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_2';
550 l_pon_sys_vars(49).variable_value_id:= l_dummy_value;
551 l_pon_sys_vars(50).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_LINE_3';
552 l_pon_sys_vars(50).variable_value_id:= l_dummy_value;
553 l_pon_sys_vars(51).variable_code:= 'OKC$B_LEGAL_ENTITY_ADDR_STYLE';
554 l_pon_sys_vars(51).variable_value_id:= l_dummy_value;
555 l_pon_sys_vars(52).variable_code:= 'OKC$B_LEGAL_ENTITY_CITY';
556 l_pon_sys_vars(52).variable_value_id:= l_dummy_value;
557 l_pon_sys_vars(53).variable_code:= 'OKC$B_LEGAL_ENTITY_COUNTRY';
558 l_pon_sys_vars(53).variable_value_id:= l_dummy_value;
559 l_pon_sys_vars(54).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION1';
560 l_pon_sys_vars(54).variable_value_id:= l_dummy_value;
561 l_pon_sys_vars(55).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION2';
562 l_pon_sys_vars(55).variable_value_id:= l_dummy_value;
563 l_pon_sys_vars(56).variable_code:= 'OKC$B_LEGAL_ENTITY_REGION3';
564 l_pon_sys_vars(56).variable_value_id:= l_dummy_value;
565 l_pon_sys_vars(57).variable_code:= 'OKC$B_LEGAL_ENTITY_ZIP';
566 l_pon_sys_vars(57).variable_value_id:= l_dummy_value;
567 l_pon_sys_vars(58).variable_code:= 'OKC$B_ORGANIZATION_ADDR';
568 l_pon_sys_vars(58).variable_value_id:= l_dummy_value;
569 l_pon_sys_vars(59).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_1';
570 l_pon_sys_vars(59).variable_value_id:= l_dummy_value;
571 l_pon_sys_vars(60).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_2';
572 l_pon_sys_vars(60).variable_value_id:= l_dummy_value;
573 l_pon_sys_vars(61).variable_code:= 'OKC$B_ORGANIZATION_ADDR_LINE_3';
574 l_pon_sys_vars(61).variable_value_id:= l_dummy_value;
575 l_pon_sys_vars(62).variable_code:= 'OKC$B_ORGANIZATION_ADDR_STYLE';
576 l_pon_sys_vars(62).variable_value_id:= l_dummy_value;
577 l_pon_sys_vars(63).variable_code:= 'OKC$B_ORGANIZATION_CITY';
578 l_pon_sys_vars(63).variable_value_id:= l_dummy_value;
579 l_pon_sys_vars(64).variable_code:= 'OKC$B_ORGANIZATION_COUNTRY';
580 l_pon_sys_vars(64).variable_value_id:= l_dummy_value;
581 l_pon_sys_vars(65).variable_code:= 'OKC$B_ORGANIZATION_REGION1';
582 l_pon_sys_vars(65).variable_value_id:= l_dummy_value;
583 l_pon_sys_vars(66).variable_code:= 'OKC$B_ORGANIZATION_REGION2';
584 l_pon_sys_vars(66).variable_value_id:= l_dummy_value;
585 l_pon_sys_vars(67).variable_code:= 'OKC$B_ORGANIZATION_REGION3';
586 l_pon_sys_vars(67).variable_value_id:= l_dummy_value;
587 l_pon_sys_vars(68).variable_code:= 'OKC$B_ORGANIZATION_ZIP';
588 l_pon_sys_vars(68).variable_value_id:= l_dummy_value;
589 l_pon_sys_vars(69).variable_code:= 'OKC$B_SHIP_TO_ADDR_STYLE';
590 l_pon_sys_vars(69).variable_value_id:= l_dummy_value;
591 l_pon_sys_vars(70).variable_code:= 'OKC$B_SUPPLIER_CLASSIFICATION';
592 l_pon_sys_vars(70).variable_value_id:= l_dummy_value;
593 l_pon_sys_vars(71).variable_code:= 'OKC$B_SUPPLIER_MINORITY_TYPE';
594 l_pon_sys_vars(71).variable_value_id:= l_dummy_value;
595
596 -- Bug 4102993
597 -- add the missing functional currency variable
598 l_pon_sys_vars(72).variable_code := 'OKC$B_FUNC_CURRENCY';
599
600 -- ECO 4241852
601 l_pon_sys_vars(73).variable_code := 'OKC$B_OUTCOME_PO_STYLE';
602
603 begin
604 select
605 pah.org_id organization,
606 pon_conterms_utl_pvt.get_legal_entity_id(pah.org_id) entity,
607 -- Bug 4099936
608 -- decode doctype_id to the document type code as per the po value set POC_XPRT_DOC_TYPE associated
609 -- with the doc type system variable.
610 DECODE(pah.doctype_id, 21, 'SOURCING RFI', 5, 'SOURCING RFQ', 1, 'BUYER AUCTION','SOLICITATION') document_type,
611 --get_document_sub_type(pah.doctype_id) document_type,
612 pah.document_number document_number,
613 pah.ship_to_location_id ship_to_address,
614 pah.bill_to_location_id bill_to_address,
615 pah.currency_code currency,
616 pah.trading_partner_contact_id buyer,
617 pah.trading_partner_name enterprise_name,
618 pah.po_agreed_amount * nvl(pah.rate, 1)
619 agreement_amount1,
620 pah.po_agreed_amount * nvl(pah.rate, 1)
621 agreement_amount2,
622 pah.global_agreement_flag global_flag,
623 pah.payment_terms_id payment_terms,
624 pah.freight_terms_code freight_terms,
625 pah.carrier_code carrier,
626 pah.fob_code fob,
627 pah.po_start_date agreement_start_date,
628 pah.po_end_date agreement_end_date,
629 pah.po_min_rel_amount * nvl(pah.rate, 1)
630 minimum_release_amount1,
631 pah.po_min_rel_amount * nvl(pah.rate, 1)
632 minimum_release_amount1,
633 pah.contract_type outcome,
634 pah.auction_title title,
635 pah.bid_visibility_code style,
636 pah.bid_ranking response_ranking,
637 pah.hdr_attr_display_score display_criteria,
638 pah.open_bidding_date open_response_date,
639 pah.close_bidding_date close_response_date,
640 pah.view_by_date preview_date,
641 pah.award_by_date award_date,
642 pah.allow_other_bid_currency_flag
643 currency_response_flag,
644 decode(pah.bid_list_type,'PRIVATE_BID_LIST','Y','N')
645 invitation_only_flag,
646 pah.show_bidder_notes supplier_response_flag,
647 -- Bug 4099936
648 -- decode control setting to Y/N as expected by the Contract Expert value set for the sys variable
649 DECODE(pah.bid_scope_code, 'MUST_BID_ALL_ITEMS', 'N', 'Y')
650 selective_response_flag,
651 decode(pah.full_quantity_bid_code,'FULL_QTY_BIDS_REQD','Y','N')
652 full_quantity_response_flag,
653 decode(pah.bid_frequency_code,'MULTIPLE_BIDS_ALLOWED','Y','N')
654 multiple_responses_flag,
655 pah.multiple_rounds_flag multiple_rounds_flag,
656 pah.manual_close_flag manual_close_flag,
657 pah.manual_extend_flag manual_extend_flag,
658 pah.auto_extend_flag auto_extend_flag,
659 pah.price_driven_auction_flag prices_decrease_flag,
660 pah.amendment_description amendment_description,
661 -- Bug 4102993 --> add missing functional currency variable
662 pah.currency_code func_currency,
663 -- ECO 4241852 -- BUG 5087598
664 pah.po_style_id || '-' || pah.contract_type style_id
665 into
666 l_pon_sys_vars(28).variable_value_id,
667 l_pon_sys_vars(20).variable_value_id,
668 l_pon_sys_vars(13).variable_value_id,
669 l_pon_sys_vars(38).variable_value_id,
670 l_pon_sys_vars(37).variable_value_id,
671 l_pon_sys_vars( 7).variable_value_id,
672 l_pon_sys_vars(41).variable_value_id,
673 l_pon_sys_vars( 8).variable_value_id,
674 l_pon_sys_vars(14).variable_value_id,
675 l_pon_sys_vars( 1).variable_value_id,
676 l_pon_sys_vars( 2).variable_value_id,
677 l_pon_sys_vars(18).variable_value_id,
678 l_pon_sys_vars(30).variable_value_id,
679 l_pon_sys_vars(16).variable_value_id,
680 l_pon_sys_vars( 9).variable_value_id,
681 l_pon_sys_vars(15).variable_value_id,
682 l_pon_sys_vars( 4).variable_value_id,
683 l_pon_sys_vars( 3).variable_value_id,
684 l_pon_sys_vars(23).variable_value_id,
685 l_pon_sys_vars(24).variable_value_id,
686 l_pon_sys_vars(29).variable_value_id,
687 l_pon_sys_vars(40).variable_value_id,
688 l_pon_sys_vars(39).variable_value_id,
689 l_pon_sys_vars(32).variable_value_id,
690 l_pon_sys_vars(12).variable_value_id,
691 l_pon_sys_vars(27).variable_value_id,
692 l_pon_sys_vars(10).variable_value_id,
693 l_pon_sys_vars(31).variable_value_id,
694 l_pon_sys_vars(34).variable_value_id,
695 l_pon_sys_vars(11).variable_value_id,
696 l_pon_sys_vars(19).variable_value_id,
697 l_pon_sys_vars(35).variable_value_id,
698 l_pon_sys_vars(36).variable_value_id,
699 l_pon_sys_vars(17).variable_value_id,
700 l_pon_sys_vars(26).variable_value_id,
701 l_pon_sys_vars(25).variable_value_id,
702 l_pon_sys_vars(21).variable_value_id,
703 l_pon_sys_vars(22).variable_value_id,
704 l_pon_sys_vars( 6).variable_value_id,
705 l_pon_sys_vars(33).variable_value_id,
706 l_pon_sys_vars( 5).variable_value_id,
707 -- Bug 4102993
708 -- add the missing variable value.
709 l_pon_sys_vars(72).variable_value_id,
710 -- ECO 4241852
711 l_pon_sys_vars(73).variable_value_id
712 from
713 pon_auction_headers_all pah,
714 hr_all_organization_units ou
715 where
716 pah.auction_header_id = p_doc_id and
717 pah.org_id = ou.organization_id(+) and
718 nvl(ou.date_from(+),sysdate-1) < sysdate and
719 nvl(ou.date_to(+),sysdate+1) > sysdate ;
720 exception
721 when no_data_found then
722 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
723 fnd_log.string(fnd_log.level_exception,
724 l_api_name,
725 'no data found for ' || p_doc_id);
726 end if;
727
728 x_msg_data := 'no data found for ' || p_doc_id;
729 x_return_status := fnd_api.g_ret_sts_error;
730 return;
731 end ;
732 else
733 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
734 fnd_log.string(fnd_log.level_exception,
735 l_api_name,
736 'unknown doctype ' || p_doctype_id);
737 end if;
738
739 x_return_status := fnd_api.g_ret_sts_error;
740 x_msg_data := l_api_name || ' unknown doctype ' || p_doctype_id;
741 x_msg_count := 1;
742 return;
743 end if;
744
745 l_progress := 200;
746
747 -- copy values from l_pon_sys_vars to p_sys_var_value_tbl
748 -- l_sys_var_index BINARY_INTEGER;
749 -- l_pon_var_index BINARY_INTEGER;
750 for l_sys_var_index in p_sys_var_value_tbl.first..p_sys_var_value_tbl.last loop
751
752 -- assume that we do not find the variable, set it to l_dummy_value
753 p_sys_var_value_tbl(l_sys_var_index).variable_value_id := l_dummy_value;
754
755 l_progress := 220;
756
757 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
758 fnd_log.string(fnd_log.level_statement,
759 l_api_name,
760 'searching for ' || p_sys_var_value_tbl(l_sys_var_index).variable_code);
761 end if;
762
763 -- find sys_var(sys_var_index).variable_code in pon_var
764 for l_pon_var_index in l_pon_sys_vars.first..l_pon_sys_vars.last loop
765
766 l_progress := 240;
767
768 if (p_sys_var_value_tbl(l_sys_var_index).variable_code =
769 l_pon_sys_vars(l_pon_var_index).variable_code) then
770
771 l_progress := 260;
772
773 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
774 fnd_log.string(fnd_log.level_statement,
775 l_api_name,
776 'found ' || p_sys_var_value_tbl(l_sys_var_index).variable_code || ' at l_pon_var_index=' || l_pon_var_index || ' value=' || l_pon_sys_vars(l_pon_var_index).variable_value_id);
777 end if;
778
779 -- copy the value to p_sys_var_value_tbl
780 p_sys_var_value_tbl(l_sys_var_index).variable_value_id :=
781 l_pon_sys_vars(l_pon_var_index).variable_value_id;
782 -- break out of loop
783 exit;
784 end if;
785 end loop;
786 end loop;
787
788 x_return_status := fnd_api.g_ret_sts_success;
789 x_msg_data := null;
790 x_msg_count := 0;
791
792 EXCEPTION when others then
793 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
794 fnd_log.string(fnd_log.level_exception,
795 l_api_name,
796 'unknown exception for ' || p_doc_id || ' - progress=' || l_progress);
797 end if;
798
799 FND_MSG_PUB.add_exc_msg(
800 p_pkg_name => g_package_name,
801 p_procedure_name => l_api_name || '.' || l_progress
802 );
803 FND_MSG_PUB.count_and_get(
804 p_encoded => 'F',
808 END get_article_variable_values;
805 p_count => x_msg_count,
806 p_data => x_msg_data
807 );
809
810 /*
811 * Procedure:
812 * get_changed_variables
813 *
814 * Purpose:
815 * This API will be called by Contracts to determine whether values of
816 * system variables changed between the latest revision and the previous.
817 *
818 * Parameters:
819 * IN:
820 * p_api_version
821 * API version number expected by the caller
822 * p_init_msg_list
823 * Initialize message list
824 * p_doctype_id
825 * Contracts Doc Type; one of 'RFQ', 'RFQ_RESPONSE', etc
826 * p_doc_id
827 * pon_auction_headers_all.auction_header_id
828 * IN OUT:
829 * p_sys_var_tbl
830 * A table of records to hold the system variable codes which changed
831 * between the two revisions. Contracts will pass a list of all variables
832 * being used in Contract terms for this document. This procedure will
833 * filter that list and return only those which changed.
834 * OUT:
835 * x_msg_count
836 * message count
837 * x_msg_data
838 * message data
839 * x_return_status
840 * Status Returned to calling API. Possible values are following
841 * FND_API.G_RET_STS_ERROR - for expected error
842 * FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
843 * FND_API.G_RET_STS_SUCCESS - for success
844 */
845 PROCEDURE get_changed_variables(
846 p_api_version IN NUMBER,
847 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
848 p_doctype_id IN VARCHAR2,
849 p_doc_id IN NUMBER,
850 p_sys_var_tbl IN OUT NOCOPY OKC_TERMS_UTIL_GRP.variable_code_tbl_type,
851 x_return_status OUT NOCOPY VARCHAR2,
852 x_msg_data OUT NOCOPY VARCHAR2,
853 x_msg_count OUT NOCOPY NUMBER
854 ) IS
855 l_api_name VARCHAR2(60) := g_package_name || '.get_changed_variables';
856 l_api_version NUMBER := 1.0;
857 l_prev_header_id pon_auction_headers_all.auction_header_id%type;
858 l_prev_round_id pon_auction_headers_all.auction_header_id%type;
859 l_prev_amend_id pon_auction_headers_all.auction_header_id%type;
860 l_pon_sys_vars OKC_TERMS_UTIL_GRP.variable_code_tbl_type;
861 l_sys_var_index BINARY_INTEGER;
862 l_pon_var_index BINARY_INTEGER;
863 l_progress NUMBER := 0;
864 l_found BOOLEAN;
865 BEGIN
866 -- Initialize API return status to unexpected error
867 x_return_status := fnd_api.g_ret_sts_unexp_error;
868 x_msg_data := l_api_name || ' unexpected error';
869 x_msg_count := 1;
870
871 -- Standard call to check for call compatibility.
872 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
873 l_api_name, l_api_name) THEN
874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
875 END IF;
876
877 l_progress := 50;
878
879 -- Initialize message list if p_init_msg_list is set to TRUE.
880 IF FND_API.to_Boolean(p_init_msg_list) THEN
881 FND_MSG_PUB.initialize();
882 END IF;
883
884 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
885 fnd_log.string(fnd_log.level_statement,
886 l_api_name,
887 'called ' || l_api_name);
888 end if;
889
890 -- bug 3264980
891 -- if we're not passed and variables, then return immediately
892 if (p_sys_var_tbl is null OR
893 p_sys_var_tbl.count <= 0) then
894 x_return_status := fnd_api.g_ret_sts_success;
895 x_msg_data := null;
896 x_msg_count := 0;
897 return ;
898 end if;
899
900 -- determine which query to execute depending on the doctype
901 if (p_doctype_id = PON_CONTERMS_UTL_PVT.BID or
902 p_doctype_id = PON_CONTERMS_UTL_PVT.QUOTE or
903 p_doctype_id = PON_CONTERMS_UTL_PVT.OFFER or
904 p_doctype_id = PON_CONTERMS_UTL_PVT.RESPONSE) then
905
906 l_progress := 101;
907
908 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
909 fnd_log.string(fnd_log.level_exception,
910 l_api_name,
911 'unexpected call for changes to ' || p_doctype_id || ' ' || p_doc_id);
912 end if;
913
914 -- this is a response
915 -- since we do not call qa_doc() for responses, we should not have
916 -- to implement anything here ... use the nothing changed case and
917 -- return error
918
919 for l_sys_var_index in p_sys_var_tbl.first..p_sys_var_tbl.last loop
920 p_sys_var_tbl.delete(l_sys_var_index);
921 end loop;
922
923 x_return_status := fnd_api.g_ret_sts_error;
924 x_msg_data := l_api_name || ' did not expect call with doctype ' || p_doctype_id;
925 return;
926 elsif (p_doctype_id = PON_CONTERMS_UTL_PVT.AUCTION or
927 p_doctype_id = PON_CONTERMS_UTL_PVT.REQUEST_FOR_QUOTE or
928 p_doctype_id = PON_CONTERMS_UTL_PVT.SOLICITATION or --<Sol Project>
929 p_doctype_id = PON_CONTERMS_UTL_PVT.REQUEST_FOR_INFORMATION) then
930
931 l_progress := 102;
932
933 -- this is an auction
934 -- find the previous auction_header_id (amendment or round?)
935 begin
936 select
937 auction_header_id_prev_round,
938 auction_header_id_prev_amend
939 into
940 l_prev_round_id,
941 l_prev_amend_id
942 from
943 pon_auction_headers_all
944 where
945 auction_header_id = p_doc_id;
946 exception
947 when no_data_found then
948 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
949 fnd_log.string(fnd_log.level_exception,
950 l_api_name,
951 'no data found for ' || p_doc_id);
952 end if;
953
954 x_msg_data := 'no data found for ' || p_doc_id;
955 x_return_status := fnd_api.g_ret_sts_error;
956 return;
957 end ;
958
959 l_progress := 132;
960
961 -- determine previous auction_header_id
962 if (l_prev_amend_id is not NULL) then
963 l_prev_header_id := l_prev_amend_id;
964 elsif (l_prev_round_id is not NULL) then
965 l_prev_header_id := l_prev_round_id;
966 else
967 -- no previous revision... so there can be no changes
968 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
969 fnd_log.string(fnd_log.level_statement,
970 l_api_name,
971 'no changes possible for document without revision');
972 end if;
973
974 -- clear variable list and return
975 for l_sys_var_index in p_sys_var_tbl.first..p_sys_var_tbl.last loop
976 p_sys_var_tbl.delete(l_sys_var_index);
977 end loop;
978
979 x_return_status := fnd_api.g_ret_sts_success;
980 x_msg_data := null;
981 x_msg_count := 0;
982 return ;
983 end if;
984
985 l_progress := 162;
986
987 -- select either 'N' or the System Variable code if a value has changed
988 select
989 decode(pah1.org_id,pah2.org_id,'N','OKC$B_ORGANIZATION'),
990 decode(pon_conterms_utl_pvt.get_legal_entity_id(pah1.org_id),
991 pon_conterms_utl_pvt.get_legal_entity_id(pah2.org_id),
992 'N','OKC$B_LEGAL_ENTITY'),
993 decode(pah1.doctype_id,pah2.doctype_id,'N','OKC$B_DOCUMENT_TYPE'),
994 decode(pah1.document_number,pah2.document_number,'N','OKC$B_SOURCING_DOC_NUMBER'),
995 decode(pah1.ship_to_location_id,pah2.ship_to_location_id,'N','OKC$B_SHIP_TO_ADDRESS'),
996 decode(pah1.bill_to_location_id,pah2.bill_to_location_id,'N','OKC$B_BILL_TO_ADDRESS'),
997 decode(pah1.currency_code,pah2.currency_code,'N','OKC$B_TXN_CURRENCY'),
998 decode(pah1.trading_partner_contact_id,pah2.trading_partner_contact_id,'N','OKC$B_BUYER'),
999 decode(pah1.trading_partner_name,pah2.trading_partner_name,'N','OKC$B_ENTERPRISE_NAME'),
1000 decode(pah1.po_agreed_amount*nvl(pah1.rate,1),pah2.po_agreed_amount*nvl(pah2.rate,1),'N','OKC$B_AGREEMENT_AMOUNT_FUNC'),
1001 decode(pah1.po_agreed_amount*nvl(pah1.rate,1),pah2.po_agreed_amount*nvl(pah2.rate,1),'N','OKC$B_AGREEMENT_AMOUNT_TXN'),
1002 decode(pah1.global_agreement_flag,pah2.global_agreement_flag,'N','OKC$B_GLOBAL_FLAG'),
1003 decode(pah1.payment_terms_id,pah2.payment_terms_id,'N','OKC$B_PAYMENT_TERMS'),
1004 decode(pah1.freight_terms_code,pah2.freight_terms_code,'N','OKC$B_FREIGHT_TERMS'),
1005 decode(pah1.carrier_code,pah2.carrier_code,'N','OKC$B_CARRIER'),
1006 decode(pah1.fob_code,pah2.fob_code,'N','OKC$B_FOB'),
1007 decode(pah1.po_start_date,pah2.po_start_date,'N','OKC$B_AGREEMENT_START_DATE'),
1008 decode(pah1.po_end_date,pah2.po_end_date,'N','OKC$B_AGREEMENT_END_DATE'),
1009 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'),
1010 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'),
1011 decode(pah1.contract_type,pah2.contract_type,'N','OKC$B_OUTCOME'),
1012 decode(pah1.auction_title,pah2.auction_title,'N','OKC$B_TITLE'),
1013 decode(pah1.bid_visibility_code,pah2.bid_visibility_code,'N','OKC$B_STYLE'),
1014 decode(pah1.bid_ranking,pah2.bid_ranking,'N','OKC$B_RESPONSE_RANKING'),
1015 decode(pah1.hdr_attr_display_score,pah2.hdr_attr_display_score,'N','OKC$B_DISPLAY_SCORING_CRITERIA'),
1016 decode(pah1.open_bidding_date,pah2.open_bidding_date,'N','OKC$B_OPEN_RESPONSE_DATE'),
1017 decode(pah1.close_bidding_date,pah2.close_bidding_date,'N','OKC$B_CLOSE_RESPONSE_DATE'),
1018 decode(pah1.view_by_date,pah2.view_by_date,'N','OKC$B_PREVIEW_DATE'),
1019 decode(pah1.award_by_date,pah2.award_by_date,'N','OKC$B_SCHEDULED_AWARD_DATE'),
1020 decode(pah1.allow_other_bid_currency_flag,pah2.allow_other_bid_currency_flag,'N','OKC$B_CURRNCY_RESPONSE_FLAG'),
1021 decode(pah1.bid_list_type,pah2.bid_list_type,'N','OKC$B_INVITATION_ONLY_FLAG'),
1022 decode(pah1.show_bidder_notes, pah2.show_bidder_notes,'N','OKC$B_SEE_OTHER_RESPONSE_FLAG'),
1023 decode(pah1.bid_scope_code, pah2.bid_scope_code,'N','OKC$B_SELECTIVE_RESPONSE_FLAG'),
1024 decode(pah1.full_quantity_bid_code,pah2.full_quantity_bid_code,'N','OKC$B_FULL_QTY_RSPONS_FLAG'),
1025 decode(pah1.bid_frequency_code,pah2.bid_frequency_code,'N','OKC$B_MUTI_RSP_ALLOWED_FLAG'),
1026 decode(pah1.multiple_rounds_flag,pah2.multiple_rounds_flag,'N','OKC$B_MUTI_ROUNDS_ALLOWED_FLAG'),
1027 decode(pah1.manual_close_flag,pah2.manual_close_flag,'N','OKC$B_MANU_CLOSE_ALLOWED_FLAG'),
1028 decode(pah1.manual_extend_flag,pah2.manual_extend_flag,'N','OKC$B_MANU_EXTEND_ALLOWED_FLAG'),
1029 decode(pah1.auto_extend_flag,pah2.auto_extend_flag,'N','OKC$B_AUTO_EXTEND_ALLOWED_FLAG'),
1030 decode(pah1.price_driven_auction_flag,pah2.price_driven_auction_flag,'N','OKC$B_RSPONS_PRICE_MUST_DEC'),
1031 decode(pah1.amendment_description,pah2.amendment_description,'N','OKC$B_AMENDMENT_DESCRIPTION'),
1032 decode(pah1.currency_code, pah2.currency_code, 'N', 'OKC$B_FUNC_CURRENCY'),
1033 -- ECO 4241852 -- BUG 5087598 --> no changes here
1034 decode(pah1.po_style_id,pah2.po_style_id,'N','OKC$B_OUTCOME_PO_STYLE')
1035 into
1036 l_pon_sys_vars( 1),
1037 l_pon_sys_vars( 2),
1038 l_pon_sys_vars( 3),
1039 l_pon_sys_vars( 4),
1040 l_pon_sys_vars( 5),
1041 l_pon_sys_vars( 6),
1042 l_pon_sys_vars( 7),
1043 l_pon_sys_vars( 8),
1044 l_pon_sys_vars( 9),
1045 l_pon_sys_vars(10),
1046 l_pon_sys_vars(11),
1047 l_pon_sys_vars(12),
1048 l_pon_sys_vars(13),
1049 l_pon_sys_vars(14),
1050 l_pon_sys_vars(15),
1051 l_pon_sys_vars(16),
1052 l_pon_sys_vars(17),
1053 l_pon_sys_vars(18),
1054 l_pon_sys_vars(19),
1055 l_pon_sys_vars(20),
1056 l_pon_sys_vars(21),
1057 l_pon_sys_vars(22),
1058 l_pon_sys_vars(23),
1059 l_pon_sys_vars(24),
1060 l_pon_sys_vars(25),
1061 l_pon_sys_vars(26),
1062 l_pon_sys_vars(27),
1063 l_pon_sys_vars(28),
1064 l_pon_sys_vars(29),
1065 l_pon_sys_vars(30),
1066 l_pon_sys_vars(31),
1067 l_pon_sys_vars(32),
1068 l_pon_sys_vars(33),
1069 l_pon_sys_vars(34),
1070 l_pon_sys_vars(35),
1071 l_pon_sys_vars(36),
1072 l_pon_sys_vars(37),
1073 l_pon_sys_vars(38),
1074 l_pon_sys_vars(39),
1075 l_pon_sys_vars(40),
1076 l_pon_sys_vars(41),
1077 l_pon_sys_vars(42),
1078 -- ECO 4241852
1079 l_pon_sys_vars(43)
1080 from
1081 pon_auction_headers_all pah1,
1082 pon_auction_headers_all pah2,
1083 hr_all_organization_units ou1,
1084 hr_all_organization_units ou2
1085 where
1086 pah1.auction_header_id = p_doc_id and
1087 pah1.org_id = ou1.organization_id(+) and
1088 nvl(ou1.date_from(+),sysdate-1) < sysdate and
1089 nvl(ou1.date_to(+),sysdate+1) > sysdate and
1090 pah2.auction_header_id = l_prev_header_id and
1091 pah2.org_id = ou2.organization_id(+) and
1092 nvl(ou2.date_from(+),sysdate-1) < sysdate and
1093 nvl(ou2.date_to(+),sysdate+1) > sysdate ;
1094 else
1095 -- this is an unknown doctype
1096
1097 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
1098 fnd_log.string(fnd_log.level_exception,
1099 l_api_name,
1100 'unknown doctype ' || p_doctype_id);
1101 end if;
1102
1103 x_return_status := fnd_api.g_ret_sts_error;
1104 x_msg_data := l_api_name || ' unknown doctype ' || p_doctype_id;
1105 x_msg_count := 1;
1106 return;
1107 end if;
1108
1109 l_progress := 200;
1110
1111 -- filter p_sys_var_tbl variable
1112 -- while pruning the 'N' entries from l_pon_sys_vars for performance
1113 l_sys_var_index := p_sys_var_tbl.first;
1114 while (l_sys_var_index <= p_sys_var_tbl.last) loop
1115 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1116 fnd_log.string(fnd_log.level_statement,
1117 l_api_name,
1118 'searching for ' || p_sys_var_tbl(l_sys_var_index));
1119 end if;
1120
1121 l_progress := 220;
1122
1123 l_found := false;
1124 l_pon_var_index := l_pon_sys_vars.first;
1125 while (l_pon_var_index <= l_pon_sys_vars.last) loop
1126 if (l_pon_sys_vars(l_pon_var_index)= 'N') then
1127 -- a value did not change, remove it from the array for performance
1128 l_progress := 230;
1129
1130 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1131 fnd_log.string(fnd_log.level_statement,
1132 l_api_name,
1133 'removing ' || l_pon_var_index);
1134 end if;
1135
1136 l_pon_sys_vars.delete(l_pon_var_index);
1137
1138 elsif (l_pon_sys_vars(l_pon_var_index) =
1139 p_sys_var_tbl(l_sys_var_index)) then
1140 -- this value has changed, set found and break the loop
1141 l_progress := 240;
1142
1143 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1144 fnd_log.string(fnd_log.level_statement,
1145 l_api_name,
1146 p_sys_var_tbl(l_sys_var_index) || ' changed');
1147 end if;
1148
1149 l_found:=true;
1150 exit;
1151 end if;
1152
1153 l_pon_var_index := l_pon_sys_vars.next(l_pon_var_index);
1154 end loop;
1155
1156 l_progress := 250;
1157
1158 if (not l_found) then
1159 -- this variable did not change, remove it from p_sys_var_tbl
1160 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1161 fnd_log.string(fnd_log.level_statement,
1162 l_api_name,
1163 p_sys_var_tbl(l_sys_var_index) || ' did not change');
1164 end if;
1165
1166 l_progress := 260;
1167 p_sys_var_tbl.delete(l_sys_var_index);
1168
1169 end if;
1170
1171 l_sys_var_index := p_sys_var_tbl.next(l_sys_var_index);
1172 end loop;
1173
1174 x_return_status := fnd_api.g_ret_sts_success;
1175 x_msg_data := null;
1176 x_msg_count := 0;
1177
1178 EXCEPTION when others then
1179 if (fnd_log.level_exception >= fnd_log.g_current_runtime_level) then
1180 fnd_log.string(fnd_log.level_exception,
1181 l_api_name,
1182 'unknown exception for ' || p_doc_id || ' - progress=' || l_progress);
1183 end if;
1184
1185 FND_MSG_PUB.add_exc_msg(
1186 p_pkg_name => g_package_name,
1187 p_procedure_name => l_api_name || '.' || l_progress
1188 );
1189 FND_MSG_PUB.count_and_get(
1190 p_encoded => 'F',
1191 p_count => x_msg_count,
1192 p_data => x_msg_data
1193 );
1194 END get_changed_variables;
1195
1196
1197 PROCEDURE get_item_category(
1198 p_api_version IN NUMBER,
1199 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1200 p_doctype_id IN VARCHAR2,
1201 p_doc_id IN NUMBER,
1202 x_category_tbl OUT NOCOPY OKC_TERMS_UTIL_GRP.item_tbl_type,
1203 x_item_tbl OUT NOCOPY OKC_TERMS_UTIL_GRP.item_tbl_type,
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_msg_data OUT NOCOPY VARCHAR2,
1206 x_msg_count OUT NOCOPY NUMBER
1207 ) IS
1208 l_api_version NUMBER := 1;
1209 l_api_name VARCHAR2(30) := 'GET_ITEM_CATEGORY';
1210 l_auction_header_id pon_auction_headers_all.auction_header_id%type;
1211 l_index NUMBER;
1212 TYPE category_tbl IS TABLE OF pon_auction_item_prices_all.category_name%TYPE;
1213 l_category_tbl category_tbl;
1214 TYPE item_tbl IS TABLE OF pon_auction_item_prices_all.item_number%TYPE;
1215 l_item_tbl item_tbl;
1216 BEGIN
1217 -- Initialize API return status to unexpected error
1218 x_category_tbl.delete();
1219 x_item_tbl.delete();
1220 x_return_status := fnd_api.g_ret_sts_unexp_error;
1221 x_msg_data := 'pon_conterms_utl_grp.ok_to_commit() unexpected error';
1222 x_msg_count := 1;
1223
1224 -- Standard call to check for call compatibility.
1225 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1226 l_api_name, l_api_name) THEN
1227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1228 END IF;
1229
1230 -- Initialize message list if p_init_msg_list is set to TRUE.
1231 IF FND_API.to_Boolean(p_init_msg_list) THEN
1232 FND_MSG_PUB.initialize();
1233 END IF;
1234
1235 -- get auction_header_id
1236 pon_conterms_utl_pvt.get_auction_header_id(p_doctype_id,
1237 p_doc_id,
1238 l_auction_header_id,
1239 x_return_status,
1240 x_msg_data,
1241 x_msg_count);
1242 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1243 return ;
1244 END IF;
1245
1246 -- insert categories
1247 -- bug 3290394 - type must match exactly for bulk collect in 8i
1248 begin
1249 select
1250 paip.category_name
1251 bulk collect into
1252 l_category_tbl
1253 from
1254 pon_auction_item_prices_all paip
1255 where
1256 paip.auction_header_id = l_auction_header_id
1257 and paip.category_name is not null;
1258 exception
1259 when others then
1260 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1261 fnd_log.string(fnd_log.level_statement,
1262 'pon_conterms_utl_grp.get_item_category',
1263 'category query returned no rows');
1264 end if;
1265 end ;
1266
1267 -- now copy to output table
1268 if (l_category_tbl.count > 0) then
1269 for l_index in l_category_tbl.first..l_category_tbl.last loop
1270 x_category_tbl(l_index).name := l_category_tbl(l_index);
1271 end loop;
1272 end if;
1273
1274 -- insert items
1275 -- bug 3290394 - type must match exactly for bulk collect in 8i
1276 begin
1277 select
1278 paip.item_number
1279 bulk collect into
1280 l_item_tbl
1281 from
1282 pon_auction_item_prices_all paip
1283 where
1284 paip.auction_header_id = l_auction_header_id
1285 and paip.item_number is not null;
1286 exception
1287 when others then
1288 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1289 fnd_log.string(fnd_log.level_statement,
1290 'pon_conterms_utl_grp.get_item_category',
1291 'item query returned no rows');
1292 end if;
1293 end ;
1294
1295 -- now copy to output table
1296 if (l_item_tbl.count > 0) then
1297 for l_index in l_item_tbl.first..l_item_tbl.last loop
1298 x_item_tbl(l_index).name := l_item_tbl(l_index);
1299 end loop;
1300 end if;
1301
1302 -- return success
1303 x_return_status := fnd_api.g_ret_sts_success;
1304 x_msg_data := null;
1305 x_msg_count := 0;
1306
1307 END get_item_category;
1308
1309
1310 END PON_CONTERMS_UTL_GRP;