[Home] [Help]
PACKAGE BODY: APPS.PON_CONTERMS_UTL_PVT
Source
1 PACKAGE BODY PON_CONTERMS_UTL_PVT as
2 /* $Header: PONCTDPB.pls 120.11.12020000.2 2013/03/01 09:50:47 hvutukur ship $ */
3
4 -- POC_ENABLED : Procurement Contracts Enabled
5 -- store the profile value in a global constant variable
6
7 g_contracts_installed_flag CONSTANT varchar2(1) := NVL(FND_PROFILE.VALUE('POC_ENABLED'),'N');
8
9 -- Read the profile option that enables/disables the debug log
10 -- store the profile value for logging in a global constant variable
11
12 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
13
14 -- module prefix for logging
15 -- create a module name used for logging
16
17 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
18
19 -- a few constants -- deliverables events
20 -- add constants for all deliverables events
21
22 DOCUMENT_PUBLISHED CONSTANT varchar2(30) := 'SOURCING_DOCUMENT_PUBLISHED';
23 RESPONSE_RECEIVED CONSTANT varchar2(30) := 'RESPONSE_RECEIVED';
24 DOCUMENT_CLOSED CONSTANT varchar2(30) := 'SOURCING_DOCUMENT_CLOSED';
25
26 CONTRACT_SOURCE_ATTACHED CONSTANT varchar2(30) := 'ATTACHED';
27
28 PROCEDURE get_auction_header_id(
29 p_contracts_doctype IN VARCHAR2,
30 p_contracts_doc_id IN NUMBER,
31 x_auction_header_id OUT NOCOPY pon_auction_headers_all.auction_header_id%type,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_data OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER
35 ) IS
36
37 BEGIN
38 if (p_contracts_doctype = AUCTION or
39 p_contracts_doctype = REQUEST_FOR_QUOTE or
40 p_contracts_doctype = REQUEST_FOR_INFORMATION or
41 p_contracts_doctype = SOLICITATION ) then
42 x_auction_header_id := p_contracts_doc_id;
43 elsif (p_contracts_doctype = BID or
44 p_contracts_doctype = QUOTE or
45 p_contracts_doctype = RESPONSE or
46 p_contracts_doctype = OFFER ) then
50 from pon_bid_headers
47 begin
48 select auction_header_id
49 into x_auction_header_id
51 where bid_number = p_contracts_doc_id;
52
53 x_return_status := fnd_api.g_ret_sts_success;
54 exception
55 when others then
56 x_return_status := fnd_api.g_ret_sts_error;
57 x_msg_data := 'Bad Bid Number ' || p_contracts_doc_id;
58 x_msg_count := 1;
59 end;
60 else
61 x_return_status := fnd_api.g_ret_sts_error;
62 x_msg_data := 'Unknown doctype ' || p_contracts_doctype;
63 x_msg_count := 1;
64 end if;
65
66 -- success!
67 x_return_status := fnd_api.g_ret_sts_success;
68 END get_auction_header_id;
69
70
71 FUNCTION get_response_doc_type(p_doc_type_id IN NUMBER) RETURN VARCHAR2 IS
72 x_doctype_grp_name pon_auc_doctypes.DOCTYPE_GROUP_NAME%type;
73 x_response_doc_name Varchar2(30);
74 BEGIN
75 select DOCTYPE_GROUP_NAME
76 into x_doctype_grp_name
77 from pon_auc_doctypes
78 where DOCTYPE_ID = p_doc_type_id;
79 if(x_doctype_grp_name = SRC_AUCTION) then
80 x_response_doc_name:= BID;
81 elsif (x_doctype_grp_name = SRC_REQUEST_FOR_QUOTE) then
82 x_response_doc_name:= QUOTE;
83 elsif (x_doctype_grp_name = SRC_REQUEST_FOR_INFORMATION) then
84 x_response_doc_name:= RESPONSE;
85 elsif (x_doctype_grp_name = SRC_SOLICITATION) then
86 x_response_doc_name:= OFFER;
87 end if;
88 return(x_response_doc_name);
89
90 END get_response_doc_type;
91
92
93 FUNCTION get_negotiation_doc_type(p_doc_type_id IN NUMBER) RETURN VARCHAR2 IS
94 x_doctype_grp_name pon_auc_doctypes.DOCTYPE_GROUP_NAME%type;
95 x_contract_doc_name Varchar2(30);
96 BEGIN
97 select DOCTYPE_GROUP_NAME
98 into x_doctype_grp_name
99 from pon_auc_doctypes
100 where DOCTYPE_ID = p_doc_type_id;
101 if(x_doctype_grp_name = SRC_AUCTION) then
102 x_contract_doc_name:= AUCTION;
103 elsif (x_doctype_grp_name = SRC_REQUEST_FOR_QUOTE) then
104 x_contract_doc_name:= REQUEST_FOR_QUOTE;
105 elsif (x_doctype_grp_name = SRC_REQUEST_FOR_INFORMATION) then
106 x_contract_doc_name:= REQUEST_FOR_INFORMATION;
107 elsif (x_doctype_grp_name = SOLICITATION) then --<Sol Project>
108 x_contract_doc_name:= SOLICITATION;
109 end if;
110 return(x_contract_doc_name);
111
112 END get_negotiation_doc_type;
113
114 FUNCTION is_contracts_installed RETURN VARCHAR2 IS
115
116 BEGIN
117
118 -- read the global variable that stores the profile option.
119
120 IF (g_contracts_installed_flag = 'Y') THEN
121 RETURN FND_API.G_TRUE;
122 ELSE
123 RETURN FND_API.G_FALSE;
124 END IF;
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 RAISE;
129 END is_contracts_installed;
130
131 -- Contracts package will check the following.
132 -- 1. Whether Contracts 11.5.10+ is installed or not.
133 -- 2. Whether Contracts Terms is attached to the negotiation or not.
134 -- 3. Whether Contract Terms are attached/uploaded document or not.
135 FUNCTION is_deviations_enabled( p_document_type IN VARCHAR2, p_document_id IN NUMBER ) RETURN VARCHAR2 IS
136
137 l_api_name CONSTANT VARCHAR2(30) := 'is_deviations_enabled';
138 l_result VARCHAR2(10);
139 l_old_org_id NUMBER;
140 l_old_policy VARCHAR2(2);
141 l_org_id NUMBER;
142
143 BEGIN
144
145 select org_id
146 into l_org_id
147 from pon_auction_headers_all
148 where auction_header_id = p_document_id;
149
150 --
151 -- we only want to raise exception if we get zero or more
152 -- than one records from the above sql
153 --
154
155 IF (g_fnd_debug = 'Y') THEN
156 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
157 FND_LOG.string( log_level => FND_LOG.level_statement,
158 module => g_module_prefix || l_api_name,
159 message => 'Got ORG_ID for negotiation with the parameters : p_document_id = ' || p_document_id|| ' as:'||l_org_id );
160 END IF;
161 END IF;
162
163 l_old_policy := mo_global.get_access_mode();
164 l_old_org_id := mo_global.get_current_org_id();
165
166 IF (g_fnd_debug = 'Y') THEN
167 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
168 FND_LOG.string( log_level => FND_LOG.level_statement,
169 module => g_module_prefix || l_api_name,
170 message => 'BEGIN: Calling MO_GLOBAL.SET_POLICY_CONTEXT with the parameters : l_org_id = ' || l_org_id );
171 END IF;
172 END IF;
173
174 --
175 -- Set the connection policy context. Bug 5040821.
176 --
177 mo_global.set_policy_context('S', l_org_id);
178
179 IF (g_fnd_debug = 'Y') THEN
180 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
181 FND_LOG.string( log_level => FND_LOG.level_statement,
182 module => g_module_prefix || l_api_name,
183 message => 'BEGIN: Calling OKC Package with the parameters : p_document_type = ' || p_document_type ||
184 'p_document_id = ' || p_document_id);
185 END IF;
186 END IF;
187
188 l_result := OKC_TERMS_UTIL_GRP.is_deviations_enabled( p_document_type => p_document_type, p_document_id => p_document_id );
189
193 IF (g_fnd_debug = 'Y') THEN
190 --
191 -- Set the org context back
192 --
194 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
195 FND_LOG.string( log_level => FND_LOG.level_statement,
196 module => g_module_prefix || l_api_name,
197 message => 'BEGIN: Calling MO_GLOBAL.SET_POLICY_CONTEXT with the parameters : l_org_id = ' || l_old_org_id || ', l_old_policy:'|| l_old_policy );
198 END IF;
199 END IF;
200
201 mo_global.set_policy_context(l_old_policy, l_old_org_id);
202
203 return l_result;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF (g_fnd_debug = 'Y') THEN
208 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
209 FND_LOG.string( log_level => FND_LOG.level_procedure,
210 module => g_module_prefix || l_api_name,
211 message => 'Exception occured while calling OKC_TERMS_UTIL_GRP.is_deviations_enabled function :'
212 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
213 END IF;
214 END IF;
215 RAISE;
216 END is_deviations_enabled;
217
218
219 FUNCTION get_concatenated_address(
220 p_location_id IN NUMBER)
221 RETURN VARCHAR2 IS
222 v_address1 VARCHAR2(1000);
223 v_address2 VARCHAR2(1000);
224
225 BEGIN
226 BEGIN
227 select
228 hrl.address_line_1 || ' ' || hrl.address_line_2 || ' ' || hrl.address_line_3 || ' ' || hrl.town_or_city || ' ',
229 hrl.region_1 || ' ' || hrl.region_2 || ' ' || hrl.region_3 || ' ' || hrl.postal_code || ' ' || nvl(ftl.territory_short_name, hrl.country)
230 into
231 v_address1,
232 v_address2
233 from
234 hr_locations_all hrl,
235 fnd_territories_tl ftl
236 where
237 hrl.location_id = p_location_id and
238 ftl.territory_code(+) = hrl.country and
239 ftl.territory_code(+) NOT IN ('ZR','FX','LX') and
240 ftl.language(+) = userenv('LANG');
241 EXCEPTION
242 WHEN no_data_found THEN
243 v_address1 := null;
244 v_address2 := null;
245 END;
246
247 RETURN v_address1 || v_address2;
248 END get_concatenated_address;
249
250
251 /*==============================================================================================
252 PROCEDURE : activateDeliverables PUBLIC
253 PARAMETERS:
254 p_interface_id IN NUMBER auction header id for negotiation
255 p_new_bid_number IN NUMBER new bid number for which deliverables are activated
256 p_old_bid_number IN NUMBER old bid number for which deliverables are canceled
257 x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
258 x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
259 x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
260 size is 250.
261
262 COMMENT : activate deliverables for the newly placed active bid, cancel deliverables
263 for the archived bid, This procedure is invoked from the bidding engine
264 (pon_auction_headers_pkg)
265 ==============================================================================================*/
266
267 PROCEDURE activateDeliverables (p_auction_id IN NUMBER,
268 p_new_bid_number IN NUMBER,
269 p_old_bid_number IN NUMBER,
270 x_result OUT NOCOPY VARCHAR2,
271 x_error_code OUT NOCOPY VARCHAR2,
272 x_error_message OUT NOCOPY VARCHAR2)
273
274 IS
275
276 l_api_name CONSTANT VARCHAR2(30) := 'activateDeliverables';
277 l_api_version CONSTANT NUMBER := 1.0;
278
279 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
280 l_doc_type_id NUMBER;
281 l_bus_doc_type VARCHAR2(30) ;
282 l_open_date DATE;
283 l_close_date DATE;
284 -- out parameters for the contracts apis
285
286 l_msg_data VARCHAR2(250);
287 l_msg_count NUMBER;
288 l_return_status VARCHAR2(1);
289
290 indx PLS_INTEGER := 0;
291
292 -- create a new table and record
293 l_bus_doc_dates_tbl okc_manage_deliverables_grp.busdocdates_tbl_type;
294
295 l_new_bid_status VARCHAR2(30);
296 l_old_bid_status VARCHAR2(30);
297
298 -- multi-org related changes
299 l_old_org_id NUMBER;
300 l_old_policy VARCHAR2(2);
301 l_org_id NUMBER;
302
303
304 BEGIN
305
306 x_result := FND_API.g_ret_sts_success;
307
308 select doctype_id, open_bidding_date, close_bidding_date, org_id
309 into l_doc_type_id, l_open_date, l_close_date, l_org_id
310 from pon_auction_headers_all
311 where auction_header_id = p_auction_id;
312
313 l_bus_doc_type := get_response_doc_type(l_doc_type_id);
314
315 select bid_status
316 into l_new_bid_status
317 from pon_bid_headers
318 where bid_number = p_new_bid_number;
322 end if;
319
320 if (p_old_bid_number <>-1) then
321 select bid_status into l_old_bid_status from pon_bid_headers where bid_number = p_old_bid_number;
323
324 if (is_contracts_installed() = FND_API.G_TRUE) then
325
326 begin
327 --{
328 --
329 -- Get the current policy
330 --
331 l_old_policy := mo_global.get_access_mode();
332 l_old_org_id := mo_global.get_current_org_id();
333
334 --
335 -- Set the connection policy context. Bug 5040821.
336 --
337 mo_global.set_policy_context('S', l_org_id);
338
339
340 -- bug 3608706 - new api to update the status history
341
342 OKC_MANAGE_DELIVERABLES_GRP.postDelStatusChanges (
343 p_api_version => 1.0,
344 p_init_msg_list => FND_API.G_FALSE,
345 p_commit => FND_API.G_FALSE,
346 p_bus_doc_id => p_new_bid_number,
347 p_bus_doc_type => l_bus_doc_type,
348 p_bus_doc_version => -99,
349 x_msg_data => l_msg_data,
350 x_msg_count => l_msg_count,
351 x_return_status => l_return_status);
352
353 -- pass the remaining events and their dates
354 l_bus_doc_dates_tbl(indx).EVENT_CODE := PON_CONTERMS_UTL_PVT.DOCUMENT_PUBLISHED;
355 l_bus_doc_dates_tbl(indx).EVENT_DATE := l_open_date;
356
357 indx := indx + 1;
358
359 l_bus_doc_dates_tbl(indx).EVENT_CODE := PON_CONTERMS_UTL_PVT.DOCUMENT_CLOSED;
360 l_bus_doc_dates_tbl(indx).EVENT_DATE := l_close_date;
361
362 -- activate deliverables for the new bid
363
364 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
365 p_api_version => 1.0,
366 p_init_msg_list => FND_API.G_FALSE,
367 p_commit => FND_API.G_FALSE,
368 p_bus_doc_id => p_new_bid_number,
369 p_bus_doc_type => l_bus_doc_type,
370 p_bus_doc_version => -99,
371 p_event_code => PON_CONTERMS_UTL_PVT.RESPONSE_RECEIVED,
372 p_event_date => SYSDATE,
373 p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
374 x_msg_data => l_msg_data,
375 x_msg_count => l_msg_count,
376 x_return_status => l_return_status);
377
378 -- keep logging
379
380 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
381 x_result := FND_API.G_RET_STS_ERROR;
382 x_error_code := '20001';
383 x_error_message := 'ACTIVATE_FAILED';
384
385 IF (g_fnd_debug = 'Y') THEN
386 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
387
388 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
389 module => g_module_prefix || l_api_name,
390 message => l_msg_data);
391 END IF;
392 END IF;
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
394 END IF;
395
396 OKC_MANAGE_DELIVERABLES_GRP.enableNotifications(
397 p_api_version => 1.0,
398 p_init_msg_list => FND_API.G_FALSE,
399 p_commit => FND_API.G_FALSE,
400 p_bus_doc_id => p_new_bid_number,
401 p_bus_doc_type => l_bus_doc_type,
402 p_bus_doc_version => -99,
403 x_msg_data => l_msg_data,
404 x_msg_count => l_msg_count,
405 x_return_status => l_return_status);
406
407 IF (l_return_status < FND_API.G_RET_STS_SUCCESS) THEN
408 x_result := FND_API.G_RET_STS_ERROR;
409 x_error_code := '20002';
410 x_error_message := 'ENABLE_NOTIF_FAILED';
411
412 IF (g_fnd_debug = 'Y') THEN
413
414 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
415
416 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
417 module => g_module_prefix || l_api_name,
418 message => l_msg_data);
419 END IF;
420
421 END IF;
422
423 END IF;
424
425 -- cancel deliverables for the archived bid
426
427 IF (p_old_bid_number <>-1 AND l_new_bid_status = 'ACTIVE') THEN
428
429 OKC_MANAGE_DELIVERABLES_GRP.cancelDeliverables (
430 p_api_version => 1.0,
431 p_init_msg_list => FND_API.G_FALSE,
432 p_commit => FND_API.G_FALSE,
433 p_bus_doc_id => p_old_bid_number,
434 p_bus_doc_type => l_bus_doc_type,
435 p_bus_doc_version => -99,
436 x_msg_data => l_msg_data,
440 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
437 x_msg_count => l_msg_count,
438 x_return_status => l_return_status);
439
441
442 x_result := FND_API.G_RET_STS_ERROR;
443 x_error_code := '20003';
444 x_error_message := 'CANCEL_DELIV_FAILED';
445
446 IF (g_fnd_debug = 'Y') THEN
447
448 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
449
450 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
451 module => g_module_prefix || l_api_name,
452 message => l_msg_data);
453 END IF;
454
455 END IF;
456
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458
459 END IF; -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
460
461 END IF; -- if (p_old_bid_number <>-1 AND l_new_bid_status = 'ACTIVE')
462 --}
463 exception
464 when others then
465 --
466 -- Set the org context back
467 --
468 mo_global.set_policy_context(l_old_policy, l_old_org_id);
469 raise;
470 end;
471 end if;
472 END activateDeliverables;
473
474 /*==============================================================================================
475 PROCEDURE : updateDeliverables PUBLIC
476 PARAMETERS:
477 p_auction_header_id IN NUMBER auction header id for negotiation
478 p_doc_type_id IN NUMBER doc type id for negotiation
479 p_close_bidding_date IN NUMBER new close bidding date for negotiation
480 x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
481 x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
482 x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
483 size is 250.
484 COMMENT : This procedure is to be called whenever there is a changed in close
485 bidding date of any negotiation.
486
487 ==============================================================================================*/
488
489 PROCEDURE updateDeliverables (
490 p_auction_header_id IN NUMBER,
491 p_doc_type_id IN NUMBER,
492 p_close_bidding_date IN DATE,
493 x_msg_data OUT NOCOPY VARCHAR2,
494 x_msg_count OUT NOCOPY NUMBER,
495 x_return_status OUT NOCOPY VARCHAR2
496 )
497 IS
498
499 l_negotiation_doc_type VARCHAR2(30);
500 l_response_doc_type VARCHAR2(30);
501 l_bus_doc_dates_tbl okc_manage_deliverables_grp.busdocdates_tbl_type;
502 l_return_status VARCHAR2(1);
503 l_msg_data VARCHAR2(250);
504 l_api_name CONSTANT VARCHAR2(30) := 'updateDeliverables';
505 indx PLS_INTEGER := 0;
506
507 --multi-org related changes
508 l_old_org_id NUMBER;
509 l_old_policy VARCHAR2(2);
510 l_org_id NUMBER;
511
512
513 /* get all the active and resubmission required bids in
514 all the amendments (before/after) for the current auction
515 */
516
517 CURSOR active_bids IS
518 SELECT bid_number
519 FROM pon_bid_headers
520 WHERE auction_header_id in (select a.auction_header_id
521 from pon_auction_headers_all a,
522 pon_auction_headers_all b
523 where b.auction_header_id = p_auction_header_id
524 and b.auction_header_id_orig_amend = a.auction_header_id_orig_amend)
525 AND bid_status in ( 'ACTIVE', 'RESUBMISSION') ;
526
527 /* get all the amendments (before/after) for the current
528 auction.
529 */
530
531 CURSOR all_amendments is
532 SELECT auction_header_id
533 FROM pon_auction_headers_all
534 WHERE auction_header_id in (select a.auction_header_id
535 from pon_auction_headers_all a,
536 pon_auction_headers_all b
537 where b.auction_header_id = p_auction_header_id
538 and b.auction_header_id_orig_amend = a.auction_header_id_orig_amend);
539
540
541 BEGIN
542
543 if (is_contracts_installed() = FND_API.G_TRUE) then
544
545 BEGIN
546
547 -- get the contract doc type depending on p_doc_type_id
548 l_negotiation_doc_type := get_negotiation_doc_type(p_doc_type_id);
549 l_response_doc_type := get_response_doc_type(p_doc_type_id);
550
551 -- populate the doc_date_based_events table
552 l_bus_doc_dates_tbl(indx).EVENT_CODE := PON_CONTERMS_UTL_PVT.DOCUMENT_CLOSED;
553 l_bus_doc_dates_tbl(indx).EVENT_DATE := p_close_bidding_date;
554
555 indx := indx + 1;
556
557 --
558 -- Following sql will return the org_id of the negotiation chain as
559 -- we can not change the org_id for amendment or new round
560 --
561 select org_id
562 into l_org_id
563 from pon_auction_headers_all
564 where auction_header_id = p_auction_header_id;
565
566 --
567 -- Get the current policy
568 --
569 l_old_policy := mo_global.get_access_mode();
570 l_old_org_id := mo_global.get_current_org_id();
571
572 --
573 -- Set the connection policy context. Bug 5040821.
574 --
575 mo_global.set_policy_context('S', l_org_id);
576
577 -- Call Contracts API for updating negotiation first
578
579 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables(
580 p_api_version => 1.0,
581 p_init_msg_list => FND_API.G_FALSE,
582 p_commit => FND_API.G_FALSE,
583 p_bus_doc_id => p_auction_header_id,
584 p_bus_doc_type => l_negotiation_doc_type,
585 p_bus_doc_version => -99,
586 p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
587 x_msg_data => l_msg_data,
588 x_msg_count => x_msg_count,
589 x_return_status => l_return_status
590 );
591
592 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
593 IF (g_fnd_debug = 'Y') THEN
594 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
595 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
596 module => g_module_prefix || l_api_name,
597 message => l_msg_data);
598 END IF;
599 END IF;
600 END IF;
601
602 FOR current_amendment in all_amendments LOOP
603
604 -- special case for amendments
605 -- if this auction has any new amendments, we
606 -- need to update deliverables on them as well
607
608 IF (current_amendment.auction_header_id <> p_auction_header_id) THEN
609
610 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables(
611 p_api_version => 1.0,
612 p_init_msg_list => FND_API.G_FALSE,
613 p_commit => FND_API.G_FALSE,
614 p_bus_doc_id => current_amendment.auction_header_id,
615 p_bus_doc_type => l_negotiation_doc_type,
616 p_bus_doc_version => -99,
617 p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
618 x_msg_data => l_msg_data,
619 x_msg_count => x_msg_count,
620 x_return_status => l_return_status
621 );
622
623 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
624 IF (g_fnd_debug = 'Y') THEN
625 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
626 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
627 module => g_module_prefix || l_api_name,
628 message => l_msg_data);
629 END IF;
630 END IF;
631 END IF;
632
633 END IF;
634
635 END LOOP;
636
637
638 -- Call Contracts API for updating Active Bids
639 -- do we need to pass the close bidding date and response received date
640 -- for each bid that we need to update?
641
642 FOR active_bid IN active_bids LOOP
643
644 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables(
645 p_api_version => 1.0,
646 p_init_msg_list => FND_API.G_FALSE,
647 p_commit => FND_API.G_FALSE,
648 p_bus_doc_id => active_bid.bid_number,
649 p_bus_doc_type => l_response_doc_type,
650 p_bus_doc_version => -99,
651 p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
652 x_msg_data => l_msg_data,
653 x_msg_count => x_msg_count,
654 x_return_status => l_return_status
655 );
656 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
657 IF (g_fnd_debug = 'Y') THEN
658 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
659 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
660 module => g_module_prefix || l_api_name,
661 message => l_msg_data);
662 END IF;
663 END IF;
664 END IF;
665 END LOOP;
666
667 --
668 -- Set the org context back
669 --
670 mo_global.set_policy_context(l_old_policy, l_old_org_id);
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 --
675 -- Set the org context back
676 --
677 mo_global.set_policy_context(l_old_policy, l_old_org_id);
678 -- ignore exception
682 END updateDeliverables;
679 end;
680 END IF;
681
683
684 /*==============================================================================================
685 PROCEDURE : cancelDeliverables PUBLIC
686 PARAMETERS:
687 p_auction_header_id IN NUMBER auction header id fornegotiation
688 p_doc_type_id IN NUMBER doc type id for negotiation
689 x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
690 x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
691 x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
692 size is 250.
693
694 COMMENT : This procedure is to be called whenever negotiation gets cancelled.
695
696 ============================================================================================== */
697
698 PROCEDURE cancelDeliverables(
699 p_auction_header_id IN NUMBER,
700 p_doc_type_id IN NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2,
702 x_msg_count OUT NOCOPY NUMBER,
703 x_return_status OUT NOCOPY VARCHAR2
704 )
705 IS
706
707 l_negotiation_doc_type VARCHAR2(30);
708 l_response_doc_type VARCHAR2(30);
709 l_return_status VARCHAR2(1);
710 l_msg_data VARCHAR2(250);
711 l_api_name CONSTANT VARCHAR2(30) := 'cancelDeliverables';
712 x_doctype_id pon_auction_headers_all.doctype_id%type;
713
714
715 -- multi-org related changes
716 l_old_org_id NUMBER;
717 l_old_policy VARCHAR2(2);
718 l_org_id NUMBER;
719
720 CURSOR active_bids IS
721 SELECT bid_number
722 FROM pon_bid_headers
723 WHERE auction_header_id = p_auction_header_id
724 and bid_status = 'ACTIVE';
725 BEGIN
726 x_doctype_id :=p_doc_type_id;
727
728 if (x_doctype_id =-1) then
729 select doctype_id into x_doctype_id
730 from pon_auction_headers_all
731 where auction_header_id=p_auction_header_id;
732 end if;
733
734 if (is_contracts_installed() = FND_API.G_TRUE) then
735
736 BEGIN
737
738 select org_id
739 into l_org_id
740 from pon_auction_headers_all
741 where auction_header_id = p_auction_header_id;
742
743 -- get the contract doc type depending on p_doc_type_id
744 l_negotiation_doc_type := get_negotiation_doc_type(x_doctype_id);
745 l_response_doc_type := get_response_doc_type(x_doctype_id);
746
747 --
748 -- Get the current policy
749 --
750 l_old_policy := mo_global.get_access_mode();
751 l_old_org_id := mo_global.get_current_org_id();
752
753 --
754 -- Set the connection policy context. Bug 5040821.
755 --
756 mo_global.set_policy_context('S', l_org_id);
757
758 -- Call Contracts API for cancelling negotiation 's delvierablesfirst
759 OKC_MANAGE_DELIVERABLES_GRP.cancelDeliverables(
760 p_api_version => 1.0,
761 p_init_msg_list => FND_API.G_FALSE,
762 p_commit => FND_API.G_FALSE,
763 p_bus_doc_id => p_auction_header_id,
764 p_bus_doc_type => l_negotiation_doc_type,
765 p_bus_doc_version => -99,
766 x_msg_data => l_msg_data,
767 x_msg_count => x_msg_count,
768 x_return_status => l_return_status
769 );
770
771
772 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
773 IF (g_fnd_debug = 'Y') THEN
774 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
775 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
776 module => g_module_prefix || l_api_name,
777 message => l_msg_data);
778 END IF;
779 END IF;
780 END IF;
781 -- Call Contracts API for cancel Active Bids' deliverables
782 FOR active_bid IN active_bids LOOP
783 OKC_MANAGE_DELIVERABLES_GRP.cancelDeliverables(
784 p_api_version => 1.0,
785 p_init_msg_list => FND_API.G_FALSE,
786 p_commit => FND_API.G_FALSE,
787 p_bus_doc_id => active_bid.bid_number,
788 p_bus_doc_type => l_response_doc_type,
789 p_bus_doc_version => -99,
790 x_msg_data => l_msg_data,
791 x_msg_count => x_msg_count,
792 x_return_status => l_return_status
793 );
794
795 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
796 IF (g_fnd_debug = 'Y') THEN
797 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
798 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
799 module => g_module_prefix || l_api_name,
800 message => l_msg_data);
801 END IF;
802 END IF;
803 END IF;
804 END LOOP;
805
806 --
807 -- Set the org context back
808 --
809 mo_global.set_policy_context(l_old_policy, l_old_org_id);
810
811 EXCEPTION
815 --
812 WHEN OTHERS THEN
813 --
814 -- Set the org context back
816 mo_global.set_policy_context(l_old_policy, l_old_org_id);
817 -- ignore exception
818 end;
819 END IF;
820
821 END cancelDeliverables;
822
823 /*===============================================================================================
824 PROCEDURE : Delete_Doc PUBLIC
825 PARAMETERS:
826 p_auction_header_id IN NUMBER auction header id for negotiation
827 p_doc_type_id IN NUMBER doc type id for negotiation
828 x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
829 x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
830 x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
831 size is 250.
832
833 COMMENT : This procedure is to be called whenever negotiation gets deleted. As
834 of now only draft negotiation is allowed to be deleted. Therefore this API
835 should only be called for draft negotiation deletion only.
836
837 =============================================================================================== */
838
839 PROCEDURE Delete_Doc (
840 p_auction_header_id IN NUMBER,
841 p_doc_type_id IN NUMBER,
842 x_msg_data OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_return_status OUT NOCOPY VARCHAR2
845 )
846 IS
847
848 l_negotiation_doc_type VARCHAR2(30);
849 l_return_status VARCHAR2(1);
850 l_msg_data VARCHAR2(250);
851 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc';
852
853 -- multi-org related changes
854 l_old_org_id NUMBER;
855 l_old_policy VARCHAR2(2);
856 l_org_id NUMBER;
857
858 BEGIN
859
860 if (is_contracts_installed() = FND_API.G_TRUE) then
861
862 BEGIN
863
864 -- get the contract doc type depending on p_doc_type_id
865 l_negotiation_doc_type := get_negotiation_doc_type(p_doc_type_id);
866
867 select org_id
868 into l_org_id
869 from pon_auction_headers_all
870 where auction_header_id = p_auction_header_id;
871
872 --
873 -- Get the current policy
874 --
875 l_old_policy := mo_global.get_access_mode();
876 l_old_org_id := mo_global.get_current_org_id();
877
878 --
879 -- Set the connection policy context. Bug 5040821.
880 --
881 mo_global.set_policy_context('S', l_org_id);
882
883 OKC_TERMS_UTIL_GRP.Delete_Doc(
884 p_api_version => 1.0,
885 p_doc_id =>p_auction_header_id,
886 p_doc_type => l_negotiation_doc_type,
887 x_msg_data => l_msg_data,
888 x_msg_count => x_msg_count,
889 x_return_status => l_return_status
890 );
891 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
892 IF (g_fnd_debug = 'Y') THEN
893 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
894 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
895 module => g_module_prefix || l_api_name,
896 message => l_msg_data);
897 END IF;
898 END IF;
899 END IF;
900 --
901 -- Set the org context back
902 --
903 mo_global.set_policy_context(l_old_policy, l_old_org_id);
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 --
908 -- Set the org context back
909 --
910 mo_global.set_policy_context(l_old_policy, l_old_org_id);
911 -- ignore exceptions
912 End;
913 END IF;
914
915 END Delete_Doc;
916
917 /* ===============================================================================================
918 PROCEDURE : resolveDeliverables PUBLIC
919 PARAMETERS:
920 p_auction_header_id IN NUMBER auction header id for negotiation
921 x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
922 x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
923 x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
924 size is 250.
925
926 COMMENT : This procedure is to be called whenever negotiation is getting published.
927 In OA Implementation, this should be called in beforeCommit method which publishes the negotiation.
928 =============================================================================================== */
929
930 PROCEDURE resolveDeliverables (
931 p_auction_header_id IN NUMBER,
932 x_msg_data OUT NOCOPY VARCHAR2,
933 x_msg_count OUT NOCOPY NUMBER,
934 x_return_status OUT NOCOPY VARCHAR2
935 )
936
937 IS
938 l_negotiation_doc_type VARCHAR2(30);
939 l_doc_type_id NUMBER;
940 l_view_by_date DATE;
941 l_open_date DATE;
942 l_close_bidding_date DATE;
943
944 l_bus_doc_dates_tbl OKC_MANAGE_DELIVERABLES_GRP.BUSDOCDATES_TBL_TYPE;
945
946 l_event_name VARCHAR2(30);
947 l_event_date DATE;
951
948 l_return_status VARCHAR2(1);
949 l_msg_data VARCHAR2(250);
950 l_api_name CONSTANT VARCHAR2(30) := 'resolveDeliverables';
952 -- multi-org related changes
953 l_old_org_id NUMBER;
954 l_old_policy VARCHAR2(2);
955 l_org_id NUMBER;
956
957
958 indx PLS_INTEGER := 0;
959
960 BEGIN
961
962 if (is_contracts_installed() = FND_API.G_TRUE) then
963
964 BEGIN
965
966 SELECT doctype_id, view_by_Date, open_bidding_date, close_bidding_date, org_id
967 INTO l_doc_type_id, l_view_by_date, l_open_date, l_close_bidding_date, l_org_id
968 FROM pon_auction_headers_all
969 WHERE auction_header_id = p_auction_header_id;
970 -- get the contract doc type depending on p_doc_type_id
971
972 l_negotiation_doc_type := get_negotiation_doc_type(l_doc_type_id);
973
974 -- event name and date
975 l_event_name := PON_CONTERMS_UTL_PVT.DOCUMENT_PUBLISHED;
976 l_event_date := l_open_date;
977
978 -- populate the doc_date_based_events table
979
980 l_bus_doc_dates_tbl(indx).EVENT_CODE := PON_CONTERMS_UTL_PVT.DOCUMENT_CLOSED;
981 l_bus_doc_dates_tbl(indx).EVENT_DATE := l_close_bidding_date;
982
983 --
984 -- Get the current policy
985 --
986 l_old_policy := mo_global.get_access_mode();
987 l_old_org_id := mo_global.get_current_org_id();
988
989 --
990 -- Set the connection policy context. Bug 5040821.
991 --
992 mo_global.set_policy_context('S', l_org_id);
993
994 OKC_MANAGE_DELIVERABLES_GRP.resolveDeliverables(
995 p_api_version => 1.0,
996 p_init_msg_list => FND_API.G_FALSE,
997 p_commit => FND_API.G_FALSE,
998 p_bus_doc_id => p_auction_header_id,
999 p_bus_doc_type => l_negotiation_doc_type,
1000 p_bus_doc_version => -99,
1001 p_event_code => l_event_name,
1002 p_event_date => l_event_date,
1003 p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
1004 x_msg_data => x_msg_data,
1005 x_msg_count => x_msg_count,
1006 x_return_status => x_return_status
1007 );
1008
1009 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1010 IF (g_fnd_debug = 'Y') THEN
1011 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
1012 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
1013 module => g_module_prefix || l_api_name,
1014 message => l_msg_data);
1015 END IF;
1016 END IF;
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 END IF;
1019
1020 --
1021 -- Set the org context back
1022 --
1023 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 --
1027 -- Set the org context back
1028 --
1029 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1030 -- ignore exceptions
1031 End;
1032 END IF;
1033
1034 END resolveDeliverables;
1035
1036 /*===============================================================================================
1037 PROCEDURE : copyResponseDoc PUBLIC
1038 PARAMETERS:
1039 p_source_bid_number IN NUMBER bid number of the archived bid
1040 p_target_bid_number IN NUMBER bid number of the new active bid
1041
1042 COMMENT : This procedure is to be called whenever an active proxy bid is kicked in.
1043 =============================================================================================== */
1044
1045 PROCEDURE copyResponseDoc (
1046 p_source_bid_number IN NUMBER,
1047 p_target_bid_number IN NUMBER
1048 )
1049
1050 IS
1051
1052 l_api_version Number := 1.0;
1053 l_init_msg_list Varchar2(1) := FND_API.G_FALSE ;
1054 l_commit Varchar2(1) := FND_API.G_FALSE;
1055
1056 l_source_doc_type Varchar2(30) := 'AUCTION_RESPONSE'; -- need to get the correct doc type
1057 l_source_doc_id Number := p_source_bid_number;
1058
1059 l_target_doc_type Varchar2(30) := 'AUCTION_RESPONSE'; -- need to get the correct doc type
1060 l_target_doc_id Number := p_target_bid_number;
1061
1062 l_keep_version Varchar2(1) := 'N';
1063
1064 l_article_effective_date Date := sysdate;
1065
1066 l_initializeStatus_yn Varchar2(1) := 'N';
1067 l_reset_fixed_date_yn Varchar2(1) := 'N';
1068
1069 l_internal_party_id Number := null; -- pon_auction_headers_all.trading_partner_id
1070 l_internal_contact_id Number := null; --
1071 l_internal_contact_pid Number :=null;
1072 l_contractualonly Varchar2(1) := 'N';
1073
1074 l_external_party_id Number := null; -- pon_bid_headers.trading_partner_id
1075 l_external_contact_id Number := null; -- pon_bid_headers.trading_partner_contact_id
1076
1077 l_copy_deliverables Varchar2(1) := 'Y';
1078
1079 l_document_number Varchar2(250) := null;
1080
1081 l_copy_for_amendment Varchar2(1) := 'Y';
1082
1083 l_target_contractual_doctype Varchar2(25);
1084
1085 l_return_status Varchar2(1);
1086 l_msg_data Varchar2(250);
1087 l_msg_count NUMBER;
1088
1089 -- multi-org related changes
1090 l_old_org_id NUMBER;
1091 l_old_policy VARCHAR2(2);
1092 l_org_id NUMBER;
1093
1094 BEGIN
1095
1096 --
1097 -- org_id of the p_source_bid_number
1098 -- and p_target_bid_number will be very same. This is
1099 -- an assumption due to current functional
1100 -- design of sourcing
1101 --
1102 select h.org_id
1103 into l_org_id
1104 from pon_auction_headers_all h,
1105 pon_bid_headers b
1106 where b.bid_number = p_source_bid_number
1107 and h.auction_header_id = b.auction_header_id;
1108
1109 --
1110 -- Get the current policy
1111 --
1112 l_old_policy := mo_global.get_access_mode();
1113 l_old_org_id := mo_global.get_current_org_id();
1114
1115 --
1116 -- Set the connection policy context. Bug 5040821.
1117 --
1118 mo_global.set_policy_context('S', l_org_id);
1119
1120 OKC_TERMS_COPY_GRP.COPY_RESPONSE_DOC(
1121 p_api_version => 1.0 , --l_api_version ,
1122 p_init_msg_list => FND_API.G_FALSE , --l_init_msg_list ,
1123 p_commit => FND_API.G_FALSE , --l_commit ,
1124 p_source_doc_type => l_source_doc_type ,
1125 p_source_doc_id => l_source_doc_id ,
1126 p_target_doc_type => l_target_doc_type ,
1127 p_target_doc_id => l_target_doc_id ,
1128 p_target_doc_number => NULL , -- not sure what this is used for ??
1129 p_keep_version => 'N' , --l_keep_version ,
1130 p_article_effective_date => sysdate , --l_article_effective_date,
1131 p_copy_doc_attachments => 'N' , -- default 'N'
1132 p_allow_duplicate_terms => 'N' , -- default 'N'
1133 p_copy_attachments_by_ref => 'N' , -- default 'N'
1134 /*
1135 p_initialize_status_yn => 'N' , --l_initializeStatus_yn,
1136 p_reset_fixed_date_yn => 'N' , --l_reset_fixed_date_yn, -- new flag
1137 p_internal_party_id => l_internal_party_id ,
1138 p_internal_contact_id => l_internal_contact_id ,
1139 p_target_contractual_doctype => l_target_contractual_doctype ,
1140 p_copy_del_attachments_yn => 'Y' ,
1141 -- p_contractual_only => 'N' , --l_contractualonly ,
1142 p_external_party_id => l_external_party_id ,
1143 p_external_contact_id => l_external_contact_id ,
1144 p_copy_deliverables => 'Y' , --l_copy_deliverables ,
1145 p_document_number => NULL , --l_document_number ,
1146 p_copy_for_amendment => 'N' , --l_copy_for_amendment,
1147 */
1148 x_return_status => l_return_status ,
1149 x_msg_data => l_msg_data ,
1150 x_msg_count => l_msg_count );
1151
1152
1153 --
1157
1154 -- Set the org context back
1155 --
1156 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 --
1161 -- Set the org context back
1162 --
1163 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1164 -- raise the exception
1165 RAISE;
1166 END copyResponseDoc;
1167
1168
1169 /*===============================================================================================
1170 PROCEDURE : disqualifyDeliverables PUBLIC
1171 PARAMETERS:
1172 p_bid_number IN NUMBER bid number of the disqualified bid
1173
1174 COMMENT : This procedure is to be called whenever an active proxy bid is disqualified
1175 =============================================================================================== */
1176
1177 PROCEDURE disqualifyDeliverables (
1178 p_bid_number IN NUMBER
1179 )
1180
1181 IS
1182
1183 l_api_version NUMBER := 1.0;
1184 l_api_name CONSTANT VARCHAR2(30) := 'disqualifyDeliverables';
1185 l_bus_doc_id NUMBER;
1186 l_doc_type_id NUMBER;
1187 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1188 l_bus_doc_type VARCHAR2(30);
1189
1190 l_event_name VARCHAR2(30) := PON_CONTERMS_UTL_PVT.RESPONSE_RECEIVED;
1191 l_event_date DATE := sysdate;
1192
1193 -- multi-org related changes
1194 l_old_org_id NUMBER;
1195 l_old_policy VARCHAR2(2);
1196 l_org_id NUMBER;
1197
1198 -- out parameters for the contracts apis
1199
1200 l_msg_data VARCHAR2(250);
1201 l_msg_count NUMBER;
1202 l_return_status VARCHAR2(1);
1203 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1204
1205 BEGIN
1206
1207 select b.doctype_id ,
1208 a.org_id
1209 into l_doc_type_id,
1210 l_org_id
1211 from pon_bid_headers b,
1212 pon_auction_headers_all a
1213 where b.bid_number = p_bid_number
1214 and a.auction_header_id = b.auction_header_id;
1215
1216 l_bus_doc_type := get_response_doc_type(l_doc_type_id);
1217
1218 if (is_contracts_installed() = FND_API.G_TRUE) then
1219
1220 begin
1221 -- note that we do not need to cancel all the deliverabls
1222 -- for all the archived bids that will get 'disqualified'
1223 -- as we will have already canceled the deliverables on them
1224 -- while archiving the bid.
1225
1226 -- cancel deliverables for the archived bid
1227
1228 --
1229 -- Get the current policy
1230 --
1231 l_old_policy := mo_global.get_access_mode();
1232 l_old_org_id := mo_global.get_current_org_id();
1233
1234 --
1235 -- Set the connection policy context. Bug 5040821.
1236 --
1237 mo_global.set_policy_context('S', l_org_id);
1238
1239 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
1240 p_api_version => 1.0,
1241 p_init_msg_list => FND_API.G_FALSE,
1242 p_commit => FND_API.G_FALSE,
1243 p_bus_doc_id => p_bid_number,
1244 p_bus_doc_type => l_bus_doc_type,
1245 p_bus_doc_version => -99,
1246 x_msg_data => l_msg_data,
1247 x_msg_count => l_msg_count,
1248 x_return_status => l_return_status);
1249
1250 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1251 IF (g_fnd_debug = 'Y') THEN
1252 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
1253 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
1254 module => g_module_prefix || l_api_name,
1255 message => l_msg_data);
1256 END IF;
1257 END IF;
1258 END IF;
1259
1260 --
1261 -- Set the org context back
1262 --
1263 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1264
1265 exception
1266 when others then
1267 --
1268 -- Set the org context back
1269 --
1270 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1271 -- raise the exception
1272 RAISE;
1273 end;
1274
1275 end if;
1276
1277 END disqualifyDeliverables;
1278
1279
1280
1281
1282 PROCEDURE disableDeliverables(
1283 p_auction_number IN NUMBER,
1284 p_doc_type_id IN NUMBER,
1285 x_msg_data OUT NOCOPY VARCHAR2,
1286 x_msg_count OUT NOCOPY NUMBER,
1287 x_return_status OUT NOCOPY VARCHAR2
1288
1289 )
1290 IS
1291 l_api_version NUMBER := 1.0;
1292 l_bus_doc_id NUMBER;
1293
1294 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1295
1296 -- multi-org related changes
1297 l_old_org_id NUMBER;
1298 l_old_policy VARCHAR2(2);
1299 l_org_id NUMBER;
1300
1301 -- out parameters for the contracts apis
1302 l_msg_data VARCHAR2(250);
1303 l_msg_count NUMBER;
1304 l_return_status VARCHAR2(1);
1305 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1306 l_response_doc_type VARCHAR2(30);
1307 l_api_name CONSTANT VARCHAR2(30) :='disableDeliverables';
1308
1309 CURSOR active_bids IS
1310 SELECT bid_number
1311 FROM pon_bid_headers
1312 WHERE auction_header_id =p_auction_number
1313 and bid_status = 'ACTIVE';
1314
1315 BEGIN
1316
1317 l_response_doc_type := get_response_doc_type(p_doc_type_id);
1318 if (is_contracts_installed() = FND_API.G_TRUE) then
1319
1320 begin
1321
1322 select org_id
1323 into l_org_id
1324 from pon_auction_headers_all
1325 where auction_header_id = p_auction_number;
1326
1327 --
1328 -- Get the current policy
1329 --
1330 l_old_policy := mo_global.get_access_mode();
1331 l_old_org_id := mo_global.get_current_org_id();
1332
1333 --
1334 -- Set the connection policy context. Bug 5040821.
1335 --
1336 mo_global.set_policy_context('S', l_org_id);
1337
1338 FOR active_bid IN active_bids LOOP
1339
1340 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables(
1341 p_api_version => 1.0,
1342 p_init_msg_list => FND_API.G_FALSE,
1343 p_commit => FND_API.G_FALSE,
1344 p_bus_doc_id => active_bid.bid_number,
1345 p_bus_doc_type => l_response_doc_type,
1346 p_bus_doc_version => -99,
1347 x_msg_data => l_msg_data,
1348 x_msg_count => l_msg_count,
1349 x_return_status => l_return_status)
1350 ;
1351 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1352 IF (g_fnd_debug = 'Y') THEN
1353 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
1354 FND_LOG.string(log_level => FND_LOG.LEVEL_PROCEDURE ,
1355 module => g_module_prefix || l_api_name,
1356 message => l_msg_data);
1357 END IF;
1358 END IF;
1359 END IF;
1360 end loop;
1361
1362 --
1363 -- Set the org context back
1364 --
1365 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1366
1367 end;
1368 end if;
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 --
1372 -- Set the org context back
1373 --
1374 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1375 -- ignore exceptions
1376
1377 END disableDeliverables;
1378
1379
1380 /*======================================================================
1381 FUNCTION : contract_terms_exist PUBLIC
1382 PARAMETERS:
1383 p_doc_type IN document type for contract
1384 p_doc_id IN document id
1385
1386 COMMENT : check if negotiation has contract terms
1387 Earlier used to set pon_auction_headers_all.conterms_exist_flag
1388 After cdrls project, deliverables can be added without
1389 applying a contract template. Use other function contract_terms_or_del_exist
1390 to set pon_auction_headers_all.conterms_exist_flag
1391 ======================================================================*/
1392
1393 FUNCTION contract_terms_exist(p_doc_type IN VARCHAR2,
1394 p_doc_id IN NUMBER) RETURN VARCHAR2 IS
1395 -- Bug 7409774 : Multi-org Related changes are not required here.
1396 -- multi-org related changes
1397 -- l_old_org_id NUMBER;
1398 -- l_old_policy VARCHAR2(2);
1399 -- l_org_id NUMBER;
1400 l_msg_data VARCHAR2(250);
1401 l_msg_count NUMBER;
1402 l_return_status VARCHAR2(1);
1403 l_auction_header_id pon_auction_headers_all.auction_header_id%type;
1404
1405 BEGIN
1406
1407 get_auction_header_id(p_doc_type, p_doc_id, l_auction_header_id, l_return_status, l_msg_data, l_msg_count);
1408
1409 if(l_return_status <> fnd_api.g_ret_sts_success) then
1410 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1411 fnd_log.string( fnd_log.level_statement,
1412 'pon_conterms_utl_pvt',
1413 'contract_terms_exist() failed for doc_id=' || p_doc_id || ', msg_data=' || l_msg_data
1414 );
1415 end if;
1416 RETURN null;
1417 end if;
1418
1419 -- select org_id
1420 -- into l_org_id
1421 -- from pon_auction_headers_all
1422 -- where auction_header_id = l_auction_header_id;
1423
1424 --
1425 -- Get the current policy
1426 --
1427 -- Bug 7409774 -- Commenting the multi-org related code. Not required here.
1428 -- l_old_policy := mo_global.get_access_mode();
1429 -- l_old_org_id := mo_global.get_current_org_id();
1430
1431 --
1432 -- Set the connection policy context. Bug 5040821.
1433 --
1434 -- mo_global.set_policy_context('S', l_org_id);
1435
1436 -- Refer the bug 4129274 for more details.
1437 return OKC_TERMS_UTIL_GRP.HAS_TERMS( p_document_type => p_doc_type,
1438 p_document_id => p_doc_id
1439 );
1440 --
1441 -- Set the org context back
1442 --
1443 -- mo_global.set_policy_context(l_old_policy, l_old_org_id);
1444
1445
1446 END contract_terms_exist;
1447
1448
1449 /*======================================================================
1450 FUNCTION : contract_deliverables_exist PUBLIC
1451 PARAMETERS:
1452 p_doc_type IN document type for contract
1453 p_doc_id IN document id
1454
1455 COMMENT : check if negotiation has deliverables or not
1456 ======================================================================*/
1457 FUNCTION contract_deliverables_exist(p_doc_type IN VARCHAR2,
1458 p_doc_id IN NUMBER) RETURN VARCHAR2 IS
1459 -- Bug 7409774 : Multi-org Related changes are not required here.
1460 -- multi-org related changes
1461 -- l_old_org_id NUMBER;
1462 -- l_old_policy VARCHAR2(2);
1463 -- l_org_id NUMBER;
1464 l_msg_data VARCHAR2(250);
1465 l_msg_count NUMBER;
1466 l_return_status VARCHAR2(1);
1467 l_auction_header_id pon_auction_headers_all.auction_header_id%type;
1468
1469 BEGIN
1470
1474 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1471 get_auction_header_id(p_doc_type, p_doc_id, l_auction_header_id, l_return_status, l_msg_data, l_msg_count);
1472
1473 if(l_return_status <> fnd_api.g_ret_sts_success) then
1475 fnd_log.string( fnd_log.level_statement,
1476 'pon_conterms_utl_pvt',
1477 'contract_deliverables_exist() failed for doc_id=' || p_doc_id || ', msg_data=' || l_msg_data
1478 );
1479 end if;
1480 RETURN null;
1481 end if;
1482
1483 return OKC_TERMS_UTIL_GRP.HAS_DELIVERABLES( p_document_type => p_doc_type,
1484 p_document_id => p_doc_id
1485 );
1486
1487
1488 END contract_deliverables_exist;
1489
1490
1491 /*======================================================================
1492 FUNCTION : contract_terms_or_del_exist PUBLIC
1493 PARAMETERS:
1494 p_doc_type IN document type for contract
1495 p_doc_id IN document id
1496
1497 COMMENT : check if negotiation has contract terms or deliverables
1498 used to set pon_auction_headers_all.conterms_exist_flag
1499 ======================================================================*/
1500 FUNCTION contract_terms_or_del_exist(p_doc_type IN VARCHAR2,
1501 p_doc_id IN NUMBER) RETURN VARCHAR2 IS
1502
1503 l_msg_data VARCHAR2(250);
1504 l_msg_count NUMBER;
1505 l_return_status VARCHAR2(1);
1506 l_auction_header_id pon_auction_headers_all.auction_header_id%type;
1507 l_terms_exist VARCHAR2(1);
1508 l_del_exist VARCHAR2(1);
1509
1510 BEGIN
1511
1512 get_auction_header_id(p_doc_type, p_doc_id, l_auction_header_id, l_return_status, l_msg_data, l_msg_count);
1513
1514 if(l_return_status <> fnd_api.g_ret_sts_success) then
1515 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1516 fnd_log.string( fnd_log.level_statement,
1517 'pon_conterms_utl_pvt',
1518 'contract_terms_or_del_exist() failed for doc_id=' || p_doc_id || ', msg_data=' || l_msg_data
1519 );
1520 end if;
1521 RETURN null;
1522 end if;
1523 /*return OKC_TERMS_UTIL_GRP.HAS_TERMS( p_document_type => p_doc_type,
1524 p_document_id => p_doc_id
1525 ); */
1526
1527 l_terms_exist := contract_terms_exist(p_doc_type => p_doc_type, p_doc_id => p_doc_id);
1528 IF l_terms_exist = 'Y' THEN
1529 RETURN 'Y';
1530 END IF;
1531
1532 l_del_exist := contract_deliverables_exist(p_doc_type => p_doc_type, p_doc_id => p_doc_id);
1533 IF l_del_exist = 'Y' THEN
1534 RETURN 'Y';
1535 END IF;
1536
1537 RETURN 'N';
1538
1539 END contract_terms_or_del_exist;
1540
1541 /*
1542 is_article_attached()
1543
1544 returns:
1545 error = null
1546 G_ONLY_STANDARD_ART_EXIST = ONLY_STANDARD
1547 G_NON_STANDARD_ART_EXIST = NON_STANDARD
1548 G_NO_ARTICLE_EXIST = NONE
1549 */
1550 PROCEDURE is_article_attached(
1551 itemtype in varchar2,
1552 itemkey in varchar2,
1553 actid in number,
1554 uncmode in varchar2,
1555 resultout out NOCOPY varchar2
1556 ) IS
1557 v_return_status varchar2(1);
1558 v_msg_data varchar2(200);
1559 v_msg_count number;
1560 v_doc_id number;
1561
1562 -- multi-org related changes
1563 l_old_org_id NUMBER;
1564 l_old_policy VARCHAR2(2);
1565 l_org_id NUMBER;
1566
1567
1568 BEGIN
1569 -- get document id
1570 v_doc_id := wf_engine.getItemAttrNumber(itemtype => itemtype,
1571 itemkey => itemkey,
1572 aname => 'AUCTION_ID');
1573
1574 select org_id
1575 into l_org_id
1576 from pon_auction_headers_all
1577 where auction_header_id = v_doc_id;
1578
1579 --
1580 -- Get the current policy
1581 --
1582 l_old_policy := mo_global.get_access_mode();
1583 l_old_org_id := mo_global.get_current_org_id();
1584
1585 --
1586 -- Set the connection policy context. Bug 5040821.
1587 --
1588 mo_global.set_policy_context('S', l_org_id);
1589
1590 resultout := okc_terms_util_grp.is_article_exist(
1591 p_api_version => 1.0,
1592 p_init_msg_list => fnd_api.g_false,
1593 x_return_status => v_return_status,
1594 x_msg_data => v_msg_data,
1595 x_msg_count => v_msg_count,
1596 p_doc_type => pon_conterms_utl_grp.get_contracts_document_type(v_doc_id, 'N'),
1597 p_doc_id => v_doc_id);
1598
1599 if (v_return_status <> fnd_api.g_ret_sts_success) then
1600 resultout := null;
1601
1602 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1603 fnd_log.string(fnd_log.level_statement,
1604 'pon_conterms_utl_pvt',
1605 'is_article_exist() failed for doc_id=' || v_doc_id || ', msg_data=' || v_msg_data);
1606 end if;
1607 end if;
1608
1609 --
1610 -- Set the org context back
1611 --
1612 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1613
1614 END is_article_attached;
1615
1616 /*
1617 is_article_amended()
1618
1619 returns:
1623 G_NO_ARTICLE_AMENDED = NONE
1620 error = null
1621 G_ONLY_STANDARD_AMENDED = ONLY_STANDARD
1622 G_NON_STANDARD_AMENDED = NON_STANDARD
1624 */
1625 PROCEDURE is_article_amended(
1626 itemtype in varchar2,
1627 itemkey in varchar2,
1628 actid in number,
1629 uncmode in varchar2,
1630 resultout out NOCOPY varchar2
1631 ) IS
1632 v_return_status varchar2(1);
1633 v_msg_data varchar2(200);
1634 v_msg_count number;
1635 v_doc_id number;
1636
1637 -- multi-org related changes
1638 l_old_org_id NUMBER;
1639 l_old_policy VARCHAR2(2);
1640 l_org_id NUMBER;
1641
1642 BEGIN
1643 -- get document id
1644 v_doc_id := wf_engine.getItemAttrNumber(itemtype => itemtype,
1645 itemkey => itemkey,
1646 aname => 'AUCTION_ID');
1647
1648 select org_id
1649 into l_org_id
1650 from pon_auction_headers_all
1651 where auction_header_id = v_doc_id;
1652
1653 --
1654 -- Get the current policy
1655 --
1656 l_old_policy := mo_global.get_access_mode();
1657 l_old_org_id := mo_global.get_current_org_id();
1658
1659 --
1660 -- Set the connection policy context. Bug 5040821.
1661 --
1662 mo_global.set_policy_context('S', l_org_id);
1663
1664 resultout := okc_terms_util_grp.is_article_amended(
1665 p_api_version => 1.0,
1666 p_init_msg_list => fnd_api.g_false,
1667 x_return_status => v_return_status,
1668 x_msg_data => v_msg_data,
1669 x_msg_count => v_msg_count,
1670 p_doc_type => pon_conterms_utl_grp.get_contracts_document_type(v_doc_id, 'N'),
1671 p_doc_id => v_doc_id);
1672
1673 if (v_return_status <> fnd_api.g_ret_sts_success) then
1674 resultout := null;
1675
1676 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1677 fnd_log.string(fnd_log.level_statement,
1678 'pon_conterms_utl_pvt',
1679 'is_article_amended() failed for doc_id=' || v_doc_id || ', msg_data=' || v_msg_data);
1680 end if;
1681 end if;
1682
1683 --
1684 -- Set the org context back
1685 --
1686 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1687
1688 END;
1689
1690 /*
1691 is_deliverable_amended()
1692
1693 returns:
1694 error = null
1695 ALL
1696 NONE
1697 CONTRACTUAL
1698 INTERNAL
1699 SOURCING
1700 CONTRACTUAL_AND_INTERNAL
1701 CONTRACTUAL_AND_SOURCING
1702 SOURCING_AND_INTERNAL
1703 */
1704 PROCEDURE is_deliverable_amended(
1705 itemtype in varchar2,
1706 itemkey in varchar2,
1707 actid in number,
1708 uncmode in varchar2,
1709 resultout out NOCOPY varchar2
1710 ) IS
1711 v_return_status varchar2(1);
1712 v_msg_data varchar2(200);
1713 v_msg_count number;
1714 v_doc_id number;
1715
1716 -- multi-org related changes
1717 l_old_org_id NUMBER;
1718 l_old_policy VARCHAR2(2);
1719 l_org_id NUMBER;
1720
1721 BEGIN
1722 -- get document id
1723 v_doc_id := wf_engine.getItemAttrNumber(itemtype => itemtype,
1724 itemkey => itemkey,
1725 aname => 'AUCTION_ID');
1726
1727 select org_id
1728 into l_org_id
1729 from pon_auction_headers_all
1730 where auction_header_id = v_doc_id;
1731
1732 --
1733 -- Get the current policy
1734 --
1735 l_old_policy := mo_global.get_access_mode();
1736 l_old_org_id := mo_global.get_current_org_id();
1737
1738 --
1739 -- Set the connection policy context. Bug 5040821.
1740 --
1741 mo_global.set_policy_context('S', l_org_id);
1742
1743 resultout := okc_terms_util_grp.is_deliverable_amended(
1744 p_api_version => 1.0,
1745 p_init_msg_list => fnd_api.g_false,
1746 x_return_status => v_return_status,
1747 x_msg_data => v_msg_data,
1748 x_msg_count => v_msg_count,
1749 p_doc_type => pon_conterms_utl_grp.get_contracts_document_type(v_doc_id, 'N'),
1750 p_doc_id => v_doc_id);
1751
1752 if (v_return_status <> fnd_api.g_ret_sts_success) then
1753 resultout := null;
1754
1755 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1756 fnd_log.string(fnd_log.level_statement,
1757 'pon_conterms_utl_pvt',
1758 'is_deliverable_amended() failed for doc_id=' || v_doc_id || ', msg_data=' || v_msg_data);
1759 end if;
1760 end if;
1761
1762 --
1763 -- Set the org context back
1764 --
1765 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1766
1767 END;
1768
1769 /*
1770 is_template_expired()
1771
1772 returns:
1773 error = null
1774 template expired = T
1775 template not expired = F
1776 */
1777 PROCEDURE is_template_expired(
1778 itemtype in varchar2,
1779 itemkey in varchar2,
1780 actid in number,
1781 uncmode in varchar2,
1782 resultout out NOCOPY varchar2
1783 ) IS
1784 v_return_status varchar2(1);
1785 v_msg_data varchar2(200);
1786 v_msg_count number;
1787 v_doc_id number;
1788
1789 -- multi-org related changes
1790 l_old_org_id NUMBER;
1791 l_old_policy VARCHAR2(2);
1795 BEGIN
1792 l_org_id NUMBER;
1793
1794
1796 -- get document id
1797 v_doc_id := wf_engine.getItemAttrNumber(itemtype => itemtype,
1798 itemkey => itemkey,
1799 aname => 'AUCTION_ID');
1800
1801 select org_id
1802 into l_org_id
1803 from pon_auction_headers_all
1804 where auction_header_id = v_doc_id;
1805
1806 --
1807 -- Get the current policy
1808 --
1809 l_old_policy := mo_global.get_access_mode();
1810 l_old_org_id := mo_global.get_current_org_id();
1811
1812 --
1813 -- Set the connection policy context. Bug 5040821.
1814 --
1815 mo_global.set_policy_context('S', l_org_id);
1816
1817
1818 resultout := okc_terms_util_grp.is_template_expired(
1819 p_api_version => 1.0,
1820 p_init_msg_list => fnd_api.g_false,
1821 x_return_status => v_return_status,
1822 x_msg_data => v_msg_data,
1823 x_msg_count => v_msg_count,
1824 p_doc_type => pon_conterms_utl_grp.get_contracts_document_type(v_doc_id, 'N'),
1825 p_doc_id => v_doc_id);
1826
1827 if (v_return_status <> fnd_api.g_ret_sts_success) then
1828 resultout := null;
1829
1830 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1831 fnd_log.string(fnd_log.level_statement,
1832 'pon_conterms_utl_pvt',
1833 'is_template_expired() failed for doc_id=' || v_doc_id || ', msg_data=' || v_msg_data);
1834 end if;
1835 end if;
1836
1837 --
1838 -- Set the org context back
1839 --
1840 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1841
1842 END;
1843
1844 /*
1845 is_standard_contract()
1846
1847 returns:
1848 error = null
1849 G_NO_CHANGE = NO_CHANGE
1850 G_ARTICLES_CHANGED = ARTICLES_CHANGED
1851 G_DELIVERABLES_CHANGED = DELIVERABLES_CHANGED
1852 G_ART_AND_DELIV_CHANGED = ALL_CHANGED
1853 */
1854 PROCEDURE is_standard_contract(
1855 itemtype in varchar2,
1856 itemkey in varchar2,
1857 actid in number,
1858 uncmode in varchar2,
1859 resultout out NOCOPY varchar2
1860 ) IS
1861 v_return_status varchar2(1);
1862 v_msg_data varchar2(200);
1863 v_msg_count number;
1864 v_doc_id number;
1865
1866 -- multi-org related changes
1867 l_old_org_id NUMBER;
1868 l_old_policy VARCHAR2(2);
1869 l_org_id NUMBER;
1870
1871 BEGIN
1872 -- get document id
1873 v_doc_id := wf_engine.getItemAttrNumber(itemtype => itemtype,
1874 itemkey => itemkey,
1875 aname => 'AUCTION_ID');
1876
1877 select org_id
1878 into l_org_id
1879 from pon_auction_headers_all
1880 where auction_header_id = v_doc_id;
1881
1882 --
1883 -- Get the current policy
1884 --
1885 l_old_policy := mo_global.get_access_mode();
1886 l_old_org_id := mo_global.get_current_org_id();
1887
1888 --
1889 -- Set the connection policy context. Bug 5040821.
1890 --
1891 mo_global.set_policy_context('S', l_org_id);
1892
1893 resultout := okc_terms_util_grp.deviation_from_standard(
1894 p_api_version => 1.0,
1895 p_init_msg_list => fnd_api.g_false,
1896 x_return_status => v_return_status,
1897 x_msg_data => v_msg_data,
1898 x_msg_count => v_msg_count,
1899 p_doc_type => pon_conterms_utl_grp.get_contracts_document_type(v_doc_id, 'N'),
1900 p_doc_id => v_doc_id);
1901
1902 if (v_return_status <> fnd_api.g_ret_sts_success) then
1903 resultout := null;
1904
1905 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1906 fnd_log.string(fnd_log.level_statement,
1907 'pon_conterms_utl_pvt',
1908 'is_standard_contract() failed for doc_id=' || v_doc_id || ', msg_data=' || v_msg_data);
1909 end if;
1910 end if;
1911
1912 --
1913 -- Set the org context back
1914 --
1915 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1916
1917 END;
1918
1919 /*
1920 is_deliverable_attached()
1921
1922 returns:
1923 error = null
1924 No Deliverables = NONE
1925 Contractual Only = CONTRACTUAL
1926 Internal Only = INTERNAL
1927 Contractual and Internal = CONTRACTUAL_AND_INTERNAL
1928 */
1929 PROCEDURE is_deliverable_attached(
1930 itemtype in varchar2,
1931 itemkey in varchar2,
1932 actid in number,
1933 uncmode in varchar2,
1934 resultout out NOCOPY varchar2
1935 ) IS
1936 v_return_status varchar2(1);
1937 v_msg_data varchar2(200);
1938 v_msg_count number;
1939 v_doc_id number;
1940
1941 -- multi-org related changes
1942 l_old_org_id NUMBER;
1943 l_old_policy VARCHAR2(2);
1944 l_org_id NUMBER;
1945
1946 BEGIN
1947 -- get document id
1948 v_doc_id := wf_engine.getItemAttrNumber(itemtype => itemtype,
1949 itemkey => itemkey,
1950 aname => 'AUCTION_ID');
1951
1952 select org_id
1953 into l_org_id
1954 from pon_auction_headers_all
1955 where auction_header_id = v_doc_id;
1956
1957 --
1958 -- Get the current policy
1959 --
1960 l_old_policy := mo_global.get_access_mode();
1961 l_old_org_id := mo_global.get_current_org_id();
1962
1963 --
1964 -- Set the connection policy context. Bug 5040821.
1965 --
1966 mo_global.set_policy_context('S', l_org_id);
1967
1968 resultout := okc_terms_util_grp.is_deliverable_exist(
1969 p_api_version => 1.0,
1970 p_init_msg_list => fnd_api.g_false,
1971 x_return_status => v_return_status,
1972 x_msg_data => v_msg_data,
1973 x_msg_count => v_msg_count,
1974 p_doc_type => pon_conterms_utl_grp.get_contracts_document_type(v_doc_id, 'N'),
1975 p_doc_id => v_doc_id);
1976
1977 if (v_return_status <> fnd_api.g_ret_sts_success) then
1978 resultout := null;
1979
1980 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1981 fnd_log.string(fnd_log.level_statement,
1982 'pon_conterms_utl_pvt',
1983 'is_deliverable_attached() failed for doc_id=' || v_doc_id || ', msg_data=' || v_msg_data);
1984 end if;
1985 end if;
1986
1987 --
1988 -- Set the org context back
1989 --
1990 mo_global.set_policy_context(l_old_policy, l_old_org_id);
1991
1992 END;
1993
1994
1995
1996
1997 PROCEDURE updateDelivOnVendorMerge
1998 ( p_from_vendor_id IN NUMBER,
1999 p_from_site_id IN NUMBER,
2000 p_to_vendor_id IN NUMBER,
2001 p_to_site_id IN NUMBER,
2002 x_msg_data OUT NOCOPY VARCHAR2,
2003 x_msg_count OUT NOCOPY NUMBER,
2004 x_return_status OUT NOCOPY VARCHAR2
2005 ) IS
2006
2007 l_api_name CONSTANT VARCHAR2(60) := 'updateDeliverablesOnVendorMerge';
2008 -- multi-org related changes
2009 l_old_org_id NUMBER;
2010 l_old_policy VARCHAR2(2);
2011
2012 BEGIN
2013
2014 IF (is_contracts_installed() = FND_API.G_TRUE) THEN --{
2015
2016 BEGIN
2017 --{
2018
2019 --
2020 -- Get the current policy
2021 --
2022 l_old_policy := mo_global.get_access_mode();
2023 l_old_org_id := mo_global.get_current_org_id();
2024
2025 --
2026 -- Set the connection policy context. Bug 5040821.
2027 --
2028 mo_global.set_policy_context('M', null);
2029
2030 OKC_MANAGE_DELIVERABLES_GRP.updateExtPartyOnDeliverables
2031 ( p_api_version => 1.0,
2032 p_init_msg_list => FND_API.G_TRUE,
2033 p_commit => FND_API.G_FALSE,
2034 p_document_class => 'SOURCING',
2035 p_from_external_party_id => p_from_vendor_id,
2036 p_from_external_party_site_id => p_from_site_id,
2037 p_to_external_party_id => p_to_vendor_id,
2038 p_to_external_party_site_id => p_to_site_id,
2039 x_msg_data => x_msg_data,
2040 x_msg_count => x_msg_count,
2041 x_return_status => x_return_status
2042 );
2043
2044 --
2045 -- Set the org context back
2046 --
2047 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2048
2049 -- keep logging
2050
2051 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN --{
2052
2053 IF (g_fnd_debug = 'Y') THEN --{
2054
2055 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2056
2057 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE ,
2058 module => g_module_prefix || l_api_name,
2059 message => 'UPDATE_DELIV_ON_VENDOR_MERGE_FAILED: '
2060 || 'p_from_external_party_id = ' || p_from_vendor_id
2064 END IF;
2061 || ' p_from_external_party_site_id=' || p_from_site_id
2062 || ' p_to_external_party_id=' || p_to_vendor_id
2063 || ' p_to_external_party_site_id=' || p_to_site_id);
2065
2066 END IF; --}
2067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2068 END IF; --}
2069 END;--}
2070 END IF;--}
2071 EXCEPTION
2072 WHEN OTHERS THEN
2073 null;
2074 END updateDelivOnVendorMerge;
2075
2076
2077
2078
2079 /* ===============================================================================================
2080 PROCEDURE : updateDelivOnAmendment PUBLIC
2081 PARAMETERS:
2082 p_auction_header_id_orig IN NUMBER auction header id of the original amendment
2083 p_auction_header_id_prev IN NUMBER auction header id of the previous amendment
2084 p_doc_type_id IN NUMBER doc-type-id for the current negotiation
2085 p_close_bidding_date IN DATE new close date for the new amendment
2086 p_close_date_changed IN VARCHAR2 flag to indicate whether the close date was changed
2087 x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
2088 x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
2089 x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
2090 size is 250.
2091
2092 COMMENT : This procedure is to be called whenever amendment is getting published.
2093 In OA Implementation, this should be called in beforeCommit method which publishes the negotiation.
2094 =============================================================================================== */
2095
2096 PROCEDURE updateDelivOnAmendment (
2097 p_auction_header_id_orig IN NUMBER,
2098 p_auction_header_id_prev IN NUMBER,
2099 p_doc_type_id IN NUMBER,
2100 p_close_bidding_date IN DATE,
2101 x_result OUT NOCOPY VARCHAR2,
2102 x_error_code OUT NOCOPY VARCHAR2,
2103 x_error_message OUT NOCOPY VARCHAR2
2104 )
2105
2106 IS
2107
2108 l_old_close_date DATE;
2109 l_msg_code VARCHAR2(240);
2110 l_api_name CONSTANT VARCHAR2(30) := 'updateDelivOnAmendment';
2111 l_api_version CONSTANT NUMBER := 1.0;
2112
2113 -- out parameters for the contracts apis
2114
2115 l_msg_data VARCHAR2(250);
2116 l_msg_count NUMBER;
2117 l_return_status VARCHAR2(1);
2118
2119 BEGIN
2120
2121 select close_bidding_date into l_old_close_date
2122 from pon_auction_headers_all
2123 where auction_header_id = p_auction_header_id_prev;
2124
2125 -- if the close date was changed during the amendment process
2126 -- then we need to update all the deliverables in the new and old
2127 -- amendments that are based upon the close date event
2128
2129 IF( p_close_bidding_date <> l_old_close_date) THEN
2130
2131 PON_CONTERMS_UTL_PVT.updateDeliverables(p_auction_header_id => p_auction_header_id_orig,
2132 p_doc_type_id => p_doc_type_id,
2133 p_close_bidding_date => p_close_bidding_date,
2134 x_msg_data => l_msg_data,
2135 x_msg_count => l_msg_count,
2136 x_return_status => l_return_status);
2137
2138
2139 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2140
2141 x_result := FND_API.G_RET_STS_ERROR;
2142 x_error_code := 'UPDATE_DELIV_AMEND_FAILED';
2143 x_error_message := 'Unable to update deliverables for auction ' || p_auction_header_id_orig;
2144
2145 IF (g_fnd_debug = 'Y') THEN
2146 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2147 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE ,
2148 module => g_module_prefix || l_api_name,
2149 message => l_msg_data || ' ' || x_error_code || ' ' || x_error_message);
2150 END IF;
2151 END IF;
2152 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2153 END IF;
2154 END IF;
2155
2156 -- finally call disabledeliverables on the previous amendment
2157
2158 PON_CONTERMS_UTL_PVT.disableDeliverables(p_auction_number => p_auction_header_id_prev,
2159 p_doc_type_id => p_doc_type_id,
2160 x_msg_data => l_msg_data,
2161 x_msg_count => l_msg_count,
2162 x_return_status => l_return_status);
2163
2164 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2165
2166 x_result := FND_API.G_RET_STS_ERROR;
2167 x_error_code := 'DISABLE_DELIV_AMEND_FAILED';
2168 x_error_message := 'Unable to disable deliverables for auction ' || p_auction_header_id_prev;
2169
2170 IF (g_fnd_debug = 'Y') THEN
2171 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2172 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE ,
2173 module => g_module_prefix || l_api_name,
2174 message => l_msg_data || ' ' || x_error_code || ' ' || x_error_message);
2175 END IF;
2176 END IF;
2177 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2178 END IF;
2179
2180
2181 EXCEPTION
2182
2183 WHEN OTHERS THEN
2184
2188
2185 x_result := FND_API.G_RET_STS_ERROR;
2186 x_error_code := 'UPDATE_DELIV_AMEND_FAILED_COMPLETELY - ' || SQLCODE;
2187 x_error_message := 'Unable to do anything with deliverables for auction ' || p_auction_header_id_prev || ' ' || SUBSTR(SQLERRM, 1, 100);
2189 IF (g_fnd_debug = 'Y') THEN
2190 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2191 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE ,
2192 module => g_module_prefix || l_api_name,
2193 message => l_msg_data || ' ' || x_error_code || ' ' || x_error_message);
2194 END IF;
2195 END IF;
2196
2197 END updateDelivOnAmendment;
2198
2199 /* ============================================================================
2200 * FUNCTION : attachedDocumentExists PUBLIC
2201 * PARAMETERS:
2202 * p_document_type IN VARCHAR2 - The document type:AUCTION, RFI, RFQ
2203 * p_document_id IN NUMBER - The document id
2204 * RETURNS :
2205 * FND_DOCUMENTS_TL.media_id of the Primary contract file for the
2206 * current version of the document if it is non mergeable.
2207 * 0 if document is mergeable.
2208 * -1 if no primary document exists.
2209 ============================================================================*/
2210 FUNCTION attachedDocumentExists (
2211 p_document_type IN VARCHAR2,
2212 p_document_id IN NUMBER)
2213 RETURN NUMBER
2214
2215 IS
2216 l_api_name CONSTANT VARCHAR2(30) := 'attachedDocumentExists';
2217 l_primary_terms_doc_file_id NUMBER;
2218
2219 -- multi-org related changes
2220 l_old_org_id NUMBER;
2221 l_old_policy VARCHAR2(2);
2222 l_org_id NUMBER;
2223
2224 BEGIN
2225
2226 select org_id
2227 into l_org_id
2228 from pon_auction_headers_all
2229 where auction_header_id = p_document_id;
2230
2231 --
2232 -- Get the current policy
2233 --
2234 l_old_policy := mo_global.get_access_mode();
2235 l_old_org_id := mo_global.get_current_org_id();
2236
2237 --
2238 -- Set the connection policy context. Bug 5040821.
2239 --
2240 mo_global.set_policy_context('S', l_org_id);
2241
2242 IF (g_fnd_debug = 'Y') THEN
2243 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2244 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2245 module => g_module_prefix || l_api_name,
2246 message => 'BEGIN: p_document_type = ' || p_document_type ||
2247 'p_document_id = ' || p_document_id);
2248 END IF;
2249 END IF;
2250
2251 l_primary_terms_doc_file_id := OKC_TERMS_UTIL_GRP.GET_PRIMARY_TERMS_DOC_FILE_ID(
2252 p_document_type => p_document_type,
2253 p_document_id => p_document_id);
2254
2255 IF (g_fnd_debug = 'Y') THEN
2256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2257 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2258 module => g_module_prefix || l_api_name,
2259 message => 'END: l_primary_terms_doc_file_id= ' || l_primary_terms_doc_file_id);
2260 END IF;
2261 END IF;
2262
2263 --
2264 -- Set the org context back
2265 --
2266 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2267
2268
2269 RETURN l_primary_terms_doc_file_id;
2270
2271 EXCEPTION
2272 WHEN OTHERS THEN
2273 --
2274 -- Set the org context back
2275 --
2276 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2277 IF (g_fnd_debug = 'Y') THEN
2278 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
2279 FND_LOG.string( log_level => FND_LOG.level_exception,
2280 module => g_module_prefix || l_api_name,
2281 message => 'Exception occured while calling OKC_TERMS_UTIL_GRP.GET_PRIMARY_TERMS_DOC_FILE_ID function :'
2282 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
2283 END IF;
2284 END IF;
2285 RAISE;
2286 END attachedDocumentExists;
2287
2288 /* =============================================================================
2289 * FUNCTION : isDocumentMergeable PUBLIC
2290 * PARAMETERS:
2291 * p_document_type IN VARCHAR2 - The document type:AUCTION, RFI, RFQ
2292 * p_document_id IN NUMBER - The document id
2293 * RETURNS : 'Y' - Attached document is oracle generated and mergeable.
2294 * 'N' - Non recognised format, non mergeable.
2295 * 'E' - Error.
2296 *
2297 * ===========================================================================*/
2298 FUNCTION isDocumentMergeable(
2299 p_document_type IN VARCHAR2,
2300 p_document_id IN NUMBER)
2301 RETURN VARCHAR2
2302 IS
2303 l_api_name CONSTANT VARCHAR2(30) := 'isDocumentMergeable';
2304 l_is_prm_trm_doc_mergeable VARCHAR2(1);
2305
2306 -- multi-org related changes
2307 l_old_org_id NUMBER;
2308 l_old_policy VARCHAR2(2);
2309 l_org_id NUMBER;
2310
2311 BEGIN
2312
2313 select org_id
2314 into l_org_id
2315 from pon_auction_headers_all
2316 where auction_header_id = p_document_id;
2317
2318 --
2319 -- Get the current policy
2320 --
2321 l_old_policy := mo_global.get_access_mode();
2322 l_old_org_id := mo_global.get_current_org_id();
2323
2324 --
2325 -- Set the connection policy context. Bug 5040821.
2326 --
2327 mo_global.set_policy_context('S', l_org_id);
2328
2329 IF (g_fnd_debug = 'Y') THEN
2330 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2331 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2332 module => g_module_prefix || l_api_name,
2333 message => 'BEGIN: p_document_type = ' || p_document_type ||
2334 'p_document_id = ' || p_document_id);
2335 END IF;
2336 END IF;
2337
2338 l_is_prm_trm_doc_mergeable := OKC_TERMS_UTIL_GRP.IS_PRIMARY_TERMS_DOC_MERGEABLE(
2339 p_document_type => p_document_type,
2340 p_document_id => p_document_id);
2341
2342 IF (g_fnd_debug = 'Y') THEN
2343 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2344 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2345 module => g_module_prefix || l_api_name,
2346 message => 'END: l_is_prm_trm_doc_mergeable= ' || l_is_prm_trm_doc_mergeable);
2347 END IF;
2348 END IF;
2349
2350 --
2351 -- Set the org context back
2352 --
2353 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2354
2355
2356 RETURN l_is_prm_trm_doc_mergeable;
2357
2358 EXCEPTION
2359 WHEN OTHERS THEN
2360 --
2361 -- Set the org context back
2362 --
2363 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2364
2365 IF (g_fnd_debug = 'Y') THEN
2366 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
2367 FND_LOG.string( log_level => FND_LOG.level_exception,
2368 module => g_module_prefix || l_api_name,
2369 message => 'Exception occured while calling OKC_TERMS_UTIL_GRP.IS_PRIMARY_TERMS_DOC_MERGEABLE function :'
2370 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
2371 END IF;
2372 END IF;
2373 RAISE;
2374 END isDocumentMergeable;
2375
2376
2377 /* =============================================================================
2378 * FUNCTION : isAttachedDocument PUBLIC
2379 * PARAMETERS:
2380 * p_document_type IN VARCHAR2 - The document type:AUCTION, RFI, RFQ
2381 * p_document_id IN NUMBER - The document id
2382 * RETURNS : if the Contract terms are structured or not.
2383 *
2384 * ===========================================================================*/
2385
2386 FUNCTION isAttachedDocument(
2387 p_document_type IN VARCHAR2,
2388 p_document_id IN NUMBER)
2389 RETURN VARCHAR2
2390 IS
2391 l_api_name CONSTANT VARCHAR2(30) := 'isAttachedDocument';
2392 l_contract_source_code VARCHAR2(60);
2393
2394 -- multi-org related changes
2395 l_old_org_id NUMBER;
2396 l_old_policy VARCHAR2(2);
2397 l_org_id NUMBER;
2398
2399 BEGIN
2400
2401 select org_id
2402 into l_org_id
2403 from pon_auction_headers_all
2404 where auction_header_id = p_document_id;
2405
2406 --
2407 -- Get the current policy
2408 --
2409 l_old_policy := mo_global.get_access_mode();
2410 l_old_org_id := mo_global.get_current_org_id();
2411
2412 --
2413 -- Set the connection policy context. Bug 5040821.
2414 --
2415 mo_global.set_policy_context('S', l_org_id);
2416
2417 IF (g_fnd_debug = 'Y') THEN
2418 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2419 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2420 module => g_module_prefix || l_api_name,
2421 message => 'BEGIN: p_document_type = ' || p_document_type ||
2422 'p_document_id = ' || p_document_id);
2423 END IF;
2424 END IF;
2425
2426 l_contract_source_code := OKC_TERMS_UTIL_GRP.GET_CONTRACT_SOURCE_CODE (
2427 p_document_type => p_document_type,
2428 p_document_id => p_document_id);
2429
2430 IF (g_fnd_debug = 'Y') THEN
2431 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2432 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2433 module => g_module_prefix || l_api_name,
2434 message => 'END: l_contract_source_code = ' || l_contract_source_code);
2435 END IF;
2436 END IF;
2437
2438 --
2439 -- Set the org context back
2440 --
2441 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2442
2443 IF (l_contract_source_code = PON_CONTERMS_UTL_PVT.CONTRACT_SOURCE_ATTACHED) THEN
2444 RETURN 'Y';
2445
2446 ELSE
2447 RETURN 'N';
2448 END IF;
2449
2450 EXCEPTION
2451 WHEN OTHERS THEN
2452 --
2453 -- Set the org context back
2454 --
2455 mo_global.set_policy_context(l_old_policy, l_old_org_id);
2456 IF (g_fnd_debug = 'Y') THEN
2457 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
2458 FND_LOG.string( log_level => FND_LOG.level_exception,
2459 module => g_module_prefix || l_api_name,
2460 message => 'Exception occured while calling OKC_TERMS_UTIL_GRP.GET_CONTRACT_SOURCE_CODE function :'
2461 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
2462 END IF;
2463 END IF;
2464 RAISE;
2465 END isAttachedDocument;
2466
2467 /* =============================================================================
2468 * FUNCTION : GET_LEGAL_ENTITY_ID PUBLIC
2469 * PARAMETERS:
2470 * p_ORG_ID IN NUMBER - The OU OR ORG-ID FOR CURRENT AUCTION/BID
2471 * RETURNS : LEGAL_ENTITY_ID for the corresponding org
2472 * DESCRIPTION: Since the XLE schema has changed, we have introduced this new
2473 * wrapper function to retrieve the legal_entity_id for a org_id
2474 *
2475 * ===========================================================================*/
2476
2477 FUNCTION GET_LEGAL_ENTITY_ID(p_org_id IN NUMBER) RETURN NUMBER IS
2478
2479 l_api_name CONSTANT VARCHAR2(30) := 'GET_LEGAL_ENTITY_ID';
2480 l_legal_entity_id NUMBER;
2481
2482 BEGIN
2483
2484 IF (g_fnd_debug = 'Y') THEN
2485 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2486 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2487 module => g_module_prefix || l_api_name,
2488 message => 'BEGIN: p_org_id = ' || p_org_id);
2489 END IF;
2490 END IF;
2491
2492 l_legal_entity_id := xle_utilities_grp.get_defaultlegalcontext_ou(p_org_id);
2493
2494
2495 IF (g_fnd_debug = 'Y') THEN
2496 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2497 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2498 module => g_module_prefix || l_api_name,
2499 message => 'END: p_org_id = ' || p_org_id || ' l_legal_entity_id = ' || l_legal_entity_id);
2500 END IF;
2501 END IF;
2502
2503 return l_legal_entity_id;
2504
2505 EXCEPTION
2506 WHEN OTHERS THEN
2507 IF (g_fnd_debug = 'Y') THEN
2508 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
2509 FND_LOG.string( log_level => FND_LOG.level_exception,
2510 module => g_module_prefix || l_api_name,
2511 message => 'Exception occured while calling xle_utilities_grp get_defaultlegalcontext_ou function with input org-id as :'
2512 || p_org_id
2513 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
2514 END IF;
2515 END IF;
2516 RAISE;
2517 END GET_LEGAL_ENTITY_ID;
2518
2519 --bug 7592494, added fucntion to get the legal enity associated with an OU.
2520
2521 /* =============================================================================
2522 * FUNCTION : GET_LEGAL_ENTITY_NAME PUBLIC
2523 * PARAMETERS:
2524 * p_ORG_ID IN NUMBER - The OU OR ORG-ID FOR CURRENT AUCTION/BID
2525 * RETURNS : LEGAL_ENTITY_NAME for the corresponding org
2526 *
2527 * ===========================================================================*/
2528
2529 FUNCTION GET_LEGAL_ENTITY_NAME(p_org_id IN NUMBER) RETURN VARCHAR2 IS
2530
2531 l_api_name CONSTANT VARCHAR2(30) := 'GET_LEGAL_ENTITY_NAME';
2532 x_return_status VARCHAR2(2);
2533 x_msg_data VARCHAR2(100);
2534 x_msg_count NUMBER;
2535
2536 LegalEntity_Rec XLE_UTILITIES_GRP.LegalEntity_Rec;
2537 l_legal_entity_name VARCHAR2(200);
2538
2539 BEGIN
2540
2541 IF (g_fnd_debug = 'Y') THEN
2542 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2543 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2544 module => g_module_prefix || l_api_name,
2545 message => 'BEGIN: p_org_id = ' || p_org_id);
2546 END IF;
2547 END IF;
2548
2549 --call to XLE API to get the legal entity info.
2550 XLE_UTILITIES_GRP.Get_LegalEntity_Info (x_return_status,
2551 x_msg_count,
2552 x_msg_data,
2553 NULL,
2554 XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_org_id),
2555 LegalEntity_Rec);
2556
2557 l_legal_entity_name := LegalEntity_Rec.NAME;
2558
2559 IF (g_fnd_debug = 'Y') THEN
2560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.g_current_runtime_level) THEN
2561 FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
2562 module => g_module_prefix || l_api_name,
2563 message => 'END: p_org_id = ' || p_org_id || ' legal_entity_name = ' || l_legal_entity_name);
2564 END IF;
2565 END IF;
2566
2567 return l_legal_entity_name;
2568
2569 EXCEPTION
2570 WHEN OTHERS THEN
2571 IF (g_fnd_debug = 'Y') THEN
2572 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
2573 FND_LOG.string( log_level => FND_LOG.level_exception,
2574 module => g_module_prefix || l_api_name,
2575 message => 'Exception occured while calling xle_utilities_grp get_defaultlegalcontext_ou function with input org-id as :'
2576 || p_org_id
2577 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
2578 END IF;
2579 END IF;
2580 RAISE;
2581 END GET_LEGAL_ENTITY_NAME;
2582
2583 END PON_CONTERMS_UTL_PVT;