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