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