[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_CONTRACT_PROCESS_PVT
Source
1 PACKAGE BODY OKC_REP_CONTRACT_PROCESS_PVT AS
2 /* $Header: OKCVREPPROCSB.pls 120.15 2008/04/15 05:46:51 kkolukul ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ---------------------------------------------------------------------------
7 G_PARTY_TYPE_INTERNAL CONSTANT VARCHAR2(12) := 'INTERNAL_ORG';
8 G_REP_CONTRACT CONSTANT VARCHAR2(30) := 'OKC_REP_CONTRACT';
9
10 ---------------------------------------------------------------------------
11 -- START: Procedures and Functions
12 ---------------------------------------------------------------------------
13
14 -- Start of comments
15 --API name : submit_contract_for_approval
16 --Type : Private.
17 --Function : Submits contract for approval
18 --Pre-reqs : None.
19 --Parameters :
20 --IN : p_api_version IN NUMBER Required
21 -- : p_init_msg_list IN VARCHAR2 Optional
22 -- Default = FND_API.G_FALSE
23 -- : p_contract_id IN NUMBER Required
24 -- Contract ID of the contract to be submitted for approval
25 -- : p_contract_version IN NUMBER Required
26 -- Contract Version of the contract to be submitted for approval
27 --OUT : x_return_status OUT VARCHAR2(1)
28 -- : x_msg_count OUT NUMBER
29 -- : x_msg_data OUT VARCHAR2(2000)
30 --Note :
31 -- End of comments
32 PROCEDURE submit_contract_for_approval(
33 p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2,
35 p_contract_id IN NUMBER,
36 p_contract_version IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2
40
41 ) IS
42 l_api_name VARCHAR2(30);
43 l_api_version NUMBER;
44 l_user_key wf_items.user_key%TYPE;
45 l_wf_sequence wf_items.item_key%TYPE;
46 l_contract_number OKC_REP_CONTRACTS_ALL.contract_number%TYPE;
47
48 CURSOR contract_csr IS
49 SELECT contract_number
50 FROM okc_rep_contracts_all
51 WHERE contract_id = p_contract_id;
52
53 BEGIN
54
55 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
56 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
57 'Entered OKC_REP_WF_PVT.submit_contract');
58 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
59 'Contract Id is: ' || p_contract_id);
60 END IF;
61 l_api_name := 'submit_contract_for_approval';
62 l_api_version := 1.0;
63 -- Standard Start of API savepoint
64 SAVEPOINT submit_contract_PVT;
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69 -- Initialize message list if p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean( p_init_msg_list ) THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73 -- Initialize API return status to success
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76 SELECT OKC_REP_WF_S.nextval INTO l_wf_sequence FROM dual;
77 -- Get contract number
78 OPEN contract_csr;
79 FETCH contract_csr into l_contract_number;
80 IF(contract_csr%NOTFOUND) THEN
81 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
82 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
83 G_MODULE||l_api_name,
84 'Invalid Contract Id: '|| p_contract_id);
85 END IF;
86 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
87 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
88 p_token1 => G_CONTRACT_ID_TOKEN,
89 p_token1_value => to_char(p_contract_id));
90 RAISE FND_API.G_EXC_ERROR;
91 -- RAISE NO_DATA_FOUND;
92 END IF;
93 CLOSE contract_csr;
94
95 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
96 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
97 'Calling OKC_REP_UTIL_PVT.change_contract_status');
98 END IF;
99 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
100 OKC_REP_UTIL_PVT.change_contract_status(
101 p_api_version => 1.0,
102 p_init_msg_list => FND_API.G_FALSE,
103 p_contract_id => p_contract_id,
104 p_contract_version => p_contract_version,
105 p_status_code => G_STATUS_PENDING_APPROVAL,
106 p_user_id => fnd_global.user_id,
107 p_note => NULL,
108 x_msg_data => x_msg_data,
109 x_msg_count => x_msg_count,
110 x_return_status => x_return_status);
111 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
112 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
113 'OKC_REP_UTIL_PVT.change_contract_status return status is: '
114 || x_return_status);
115 END IF;
116 -----------------------------------------------------
117 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
118 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
119 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
120 RAISE OKC_API.G_EXCEPTION_ERROR;
121 END IF;
122 --------------------------------------------------------
123
124
125 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
126 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
127 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
128 END IF;
129 -- Add a record in ONC_REP_CON_APPROVALS table.
130 OKC_REP_UTIL_PVT.add_approval_hist_record(
131 p_api_version => 1.0,
132 p_init_msg_list => FND_API.G_FALSE,
133 p_contract_id => p_contract_id,
134 p_contract_version => p_contract_version,
135 p_action_code => G_ACTION_SUBMITTED,
136 p_user_id => fnd_global.user_id,
137 p_note => NULL,
138 x_msg_data => x_msg_data,
139 x_msg_count => x_msg_count,
140 x_return_status => x_return_status);
141 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
142 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
143 'OKC_REP_UTIL_PVT.add_approval_hist_record return status is: '
144 || x_return_status);
145 END IF;
146 -------------------------------------------------------
147 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
148 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
149 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
150 RAISE OKC_API.G_EXCEPTION_ERROR;
151 END IF;
152 --------------------------------------------------------
153 -- Get the user key
154 l_user_key := l_contract_number || ':' || l_wf_sequence;
155 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
156 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
157 'Calling WF_ENGINE.createprocess');
158 END IF;
159 WF_ENGINE.createprocess (
160 itemtype => G_APPROVAL_ITEM_TYPE,
161 itemkey => l_wf_sequence,
162 process => G_APPROVAL_PROCESS);
163
164
165 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
166 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
167 'Calling WF_ENGINE.SetItemUserKey');
168 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
169 'User key Value is: ' || l_user_key);
170 END IF;
171 WF_ENGINE.SetItemUserKey (
172 itemtype => G_APPROVAL_ITEM_TYPE,
173 itemkey => l_wf_sequence,
174 userkey => l_user_key);
175
176 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
177 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
178 'Calling WF_ENGINE.SetItemOwner');
179 END IF;
180 WF_ENGINE.SetItemOwner (
181 itemtype => G_APPROVAL_ITEM_TYPE,
182 itemkey => l_wf_sequence,
183 owner => fnd_global.user_name);
184
185
186 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
187 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
188 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_ID');
189 END IF;
190 WF_ENGINE.SetItemAttrText (
191 itemtype => G_APPROVAL_ITEM_TYPE,
192 itemkey => l_wf_sequence,
193 aname => 'CONTRACT_ID',
194 avalue => p_contract_id);
195
196 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
197 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
198 'Calling WF_ENGINE.startprocess for REQUESTOR');
199 END IF;
200 WF_ENGINE.SetItemAttrText (
201 itemtype => G_APPROVAL_ITEM_TYPE,
202 itemkey => l_wf_sequence,
203 aname => 'REQUESTER',
204 avalue => fnd_global.user_name);
205
206
207 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
208 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
209 'Calling WF_ENGINE.startprocess');
210 END IF;
211 WF_ENGINE.startprocess (
212 itemtype => G_APPROVAL_ITEM_TYPE,
213 itemkey => l_wf_sequence);
214
215
216
217 -- Update WF columns in OKC_REP_CONTRACTS_ALL
218 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
219 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
220 'Updating workflow columns in OKC_REP_CONTRACTS_ALL');
221 END IF;
222 UPDATE OKC_REP_CONTRACTS_ALL
223 SET wf_item_type = G_APPROVAL_ITEM_TYPE, wf_item_key = l_wf_sequence
224 WHERE contract_id=p_contract_id;
225
226 COMMIT WORK;
227
228 -- Standard call to get message count and if count is 1, get message info.
229 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
230
231 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
233 'Leaving OKC_REP_WF_PVT.submit_contract');
234 END IF;
235
236 EXCEPTION
237 WHEN FND_API.G_EXC_ERROR THEN
238 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
239 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
240 g_module || l_api_name,
241 'Leaving check_contract_access:FND_API.G_EXC_ERROR Exception');
242 END IF;
243 --close cursors
244 IF (contract_csr%ISOPEN) THEN
245 CLOSE contract_csr ;
246 END IF;
247 ROLLBACK TO submit_contract_PVT;
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 FND_MSG_PUB.Count_And_Get(
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
256 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
257 g_module || l_api_name,
258 'Leaving check_contract_access:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
259 END IF;
260 --close cursors
261 IF (contract_csr%ISOPEN) THEN
262 CLOSE contract_csr ;
263 END IF;
264 ROLLBACK TO submit_contract_PVT;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 FND_MSG_PUB.Count_And_Get(
267 p_count => x_msg_count,
268 p_data => x_msg_data
269 );
270
271 WHEN OTHERS THEN
272 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
273 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
274 g_module || l_api_name,
275 'Leaving check_contract_access because of EXCEPTION: ' || sqlerrm);
276 END IF;
277 --close cursors
278 IF (contract_csr%ISOPEN) THEN
279 CLOSE contract_csr ;
280 END IF;
281 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
282 p_msg_name => G_UNEXPECTED_ERROR,
283 p_token1 => G_SQLCODE_TOKEN,
284 p_token1_value => sqlcode,
285 p_token2 => G_SQLERRM_TOKEN,
286 p_token2_value => sqlerrm);
287 ROLLBACK TO submit_contract_PVT;
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 FND_MSG_PUB.Count_And_Get(
290 p_count => x_msg_count,
291 p_data => x_msg_data
292 );
293
294 END submit_contract_for_approval;
295
296
297 -- Start of comments
298 --API name : delete_contacts
299 --Type : Private.
300 --Function : Deletes party contacts of a particular Contract
301 --Pre-reqs : None.
302 --Parameters :
303 --IN : p_api_version IN NUMBER Required
304 -- : p_init_msg_list IN VARCHAR2 Optional
305 -- Default = FND_API.G_FALSE
306 -- : p_commit IN VARCHAR2 Optional
307 -- Default = FND_API.G_FALSE
308 -- : p_contract_id IN NUMBER Required
309 -- Contract ID of the contract whose contacts are to be deleted
310 --OUT : x_return_status OUT VARCHAR2(1)
311 -- : x_msg_count OUT NUMBER
312 -- : x_msg_data OUT VARCHAR2(2000)
313 --Note :
314 -- End of comments
315 PROCEDURE delete_contacts(
316 p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2,
318 p_commit IN VARCHAR2,
319 p_contract_id IN NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_return_status OUT NOCOPY VARCHAR2) IS
323
324 l_api_name VARCHAR2(30);
325 l_api_version NUMBER;
326
327 BEGIN
328
329 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
330 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
331 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts');
332 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
333 'Contract Id is: ' || to_char(p_contract_id));
334 END IF;
335 l_api_name := 'delete_contacts';
336 l_api_version := 1.0;
337 -- Standard Start of API savepoint
338 SAVEPOINT delete_contacts_PVT;
339 -- Standard call to check for call compatibility.
340 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END IF;
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344 IF FND_API.to_Boolean( p_init_msg_list ) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 -- Initialize API return status to success
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 -- Delete the records. The records are locked in the delete_contract API.
352 DELETE FROM OKC_REP_PARTY_CONTACTS
353 WHERE CONTRACT_ID = p_CONTRACT_ID;
354
355 -- Standard check of p_commit
356 IF FND_API.To_Boolean( p_commit ) THEN
357 COMMIT WORK;
358 END IF;
359
360 -- Standard call to get message count and if count is 1, get message info.
361 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
362
363 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
364 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
365 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts');
366 END IF;
367
368
369 EXCEPTION
370 WHEN FND_API.G_EXC_ERROR THEN
371 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
372 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
373 g_module || l_api_name,
374 'Leaving delete_contacts:FND_API.G_EXC_ERROR Exception');
375 END IF;
376 ROLLBACK TO delete_contacts_PVT;
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 FND_MSG_PUB.Count_And_Get(
379 p_count => x_msg_count,
380 p_data => x_msg_data
381 );
382
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
385 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
386 g_module || l_api_name,
387 'Leaving delete_contacts:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
388 END IF;
389 ROLLBACK TO delete_contacts_PVT;
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 FND_MSG_PUB.Count_And_Get(
392 p_count => x_msg_count,
393 p_data => x_msg_data
394 );
395
396 WHEN OTHERS THEN
397 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
398 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
399 g_module || l_api_name,
400 'Leaving delete_contacts because of EXCEPTION: ' || sqlerrm);
401 END IF;
402 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
403 p_msg_name => G_UNEXPECTED_ERROR,
404 p_token1 => G_SQLCODE_TOKEN,
405 p_token1_value => sqlcode,
406 p_token2 => G_SQLERRM_TOKEN,
407 p_token2_value => sqlerrm);
408 ROLLBACK TO delete_contacts_PVT;
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
410 FND_MSG_PUB.Count_And_Get(
411 p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414 END delete_contacts;
415
416
417 -- Start of comments
418 --API name : delete_parties
419 --Type : Private.
420 --Function : Deletes parties of a particular Contract
421 --Pre-reqs : None.
422 --Parameters :
423 --IN : p_api_version IN NUMBER Required
424 -- : p_init_msg_list IN VARCHAR2 Optional
425 -- Default = FND_API.G_FALSE
426 -- : p_commit IN VARCHAR2 Optional
427 -- Default = FND_API.G_FALSE
428 -- : p_contract_id IN NUMBER Required
429 -- Contract ID of the contract whose parties are to be deleted
430 --OUT : x_return_status OUT VARCHAR2(1)
431 -- : x_msg_count OUT NUMBER
432 -- : x_msg_data OUT VARCHAR2(2000)
433 --Note :
434 -- End of comments
435 PROCEDURE delete_parties(
436 p_api_version IN NUMBER,
437 p_init_msg_list IN VARCHAR2,
438 p_commit IN VARCHAR2,
439 p_contract_id IN NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2,
441 x_msg_count OUT NOCOPY NUMBER,
442 x_return_status OUT NOCOPY VARCHAR2) IS
443
444 l_api_name VARCHAR2(30);
445 l_api_version NUMBER;
446 BEGIN
447
448 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
449 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
450 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_parties');
451 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
452 'Contract Id is: ' || p_contract_id);
453 END IF;
454 l_api_name := 'delete_parties';
455 l_api_version := 1.0;
456 -- Standard Start of API savepoint
457 SAVEPOINT delete_parties_PVT;
458 -- Standard call to check for call compatibility.
459 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462 -- Initialize message list if p_init_msg_list is set to TRUE.
463 IF FND_API.to_Boolean( p_init_msg_list ) THEN
464 FND_MSG_PUB.initialize;
465 END IF;
466
467 -- Initialize API return status to success
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469
470 -- Delete the records. The records are locked in the delete_contract API.
471 DELETE FROM OKC_REP_CONTRACT_PARTIES
472 WHERE CONTRACT_ID = p_CONTRACT_ID;
473
474 -- Standard check of p_commit
475 IF FND_API.To_Boolean( p_commit ) THEN
476 COMMIT WORK;
477 END IF;
478
479 -- Standard call to get message count and if count is 1, get message info.
480 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
481
482 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
483 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
484 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_parties');
485 END IF;
486
487
488 EXCEPTION
489 WHEN FND_API.G_EXC_ERROR THEN
490 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
491 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
492 g_module || l_api_name,
493 'Leaving delete_parties:FND_API.G_EXC_ERROR Exception');
494 END IF;
495 ROLLBACK TO delete_parties_PVT;
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 FND_MSG_PUB.Count_And_Get(
498 p_count => x_msg_count,
499 p_data => x_msg_data
500 );
501
502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
505 g_module || l_api_name,
506 'Leaving delete_parties:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
507 END IF;
508 ROLLBACK TO delete_parties_PVT;
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510 FND_MSG_PUB.Count_And_Get(
511 p_count => x_msg_count,
512 p_data => x_msg_data
513 );
514
515 WHEN OTHERS THEN
516 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
517 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
518 g_module || l_api_name,
519 'Leaving delete_parties because of EXCEPTION: ' || sqlerrm);
520 END IF;
521 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
522 p_msg_name => G_UNEXPECTED_ERROR,
523 p_token1 => G_SQLCODE_TOKEN,
524 p_token1_value => sqlcode,
525 p_token2 => G_SQLERRM_TOKEN,
526 p_token2_value => sqlerrm);
527 ROLLBACK TO delete_parties_PVT;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 FND_MSG_PUB.Count_And_Get(
530 p_count => x_msg_count,
531 p_data => x_msg_data
532 );
533 END delete_parties;
534
535
536 -- Start of comments
537 --API name : delete_risks
538 --Type : Private.
539 --Function : Deletes risks of a particular Contract
540 --Pre-reqs : None.
541 --Parameters :
542 --IN : p_api_version IN NUMBER Required
543 -- : p_init_msg_list IN VARCHAR2 Optional
544 -- Default = FND_API.G_FALSE
545 -- : p_commit IN VARCHAR2 Optional
546 -- Default = FND_API.G_FALSE
547 -- : p_contract_id IN NUMBER Required
548 -- Contract ID of the contract whose risks are to be deleted
549 --OUT : x_return_status OUT VARCHAR2(1)
550 -- : x_msg_count OUT NUMBER
551 -- : x_msg_data OUT VARCHAR2(2000)
552 --Note :
553 -- End of comments
554 PROCEDURE delete_risks(
555 p_api_version IN NUMBER,
556 p_init_msg_list IN VARCHAR2,
557 p_commit IN VARCHAR2,
558 p_contract_id IN NUMBER,
559 x_msg_data OUT NOCOPY VARCHAR2,
560 x_msg_count OUT NOCOPY NUMBER,
561 x_return_status OUT NOCOPY VARCHAR2) IS
562
563 l_api_name VARCHAR2(30);
564 l_api_version NUMBER;
565
566 CURSOR contract_csr IS
567 SELECT contract_type, contract_version_num
568 FROM OKC_REP_CONTRACTS_ALL
569 WHERE contract_id = p_contract_id;
570
571 contract_rec contract_csr%ROWTYPE;
572
573 BEGIN
574
575 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
576 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
577 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_risks');
578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
579 'Contract Id is: ' || p_contract_id);
580 END IF;
581 l_api_name := 'delete_risks';
582 l_api_version := 1.0;
583 -- Standard Start of API savepoint
584 SAVEPOINT delete_risks_PVT;
585 -- Standard call to check for call compatibility.
586 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
588 END IF;
589 -- Initialize message list if p_init_msg_list is set to TRUE.
590 IF FND_API.to_Boolean( p_init_msg_list ) THEN
591 FND_MSG_PUB.initialize;
592 END IF;
593
594 -- Initialize API return status to success
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596 -- Get contract_type and version of the contract.
597 OPEN contract_csr;
598 FETCH contract_csr INTO contract_rec;
599 IF(contract_csr%NOTFOUND) THEN
600 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
601 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
602 G_MODULE||l_api_name,
603 'Invalid Contract Id: '|| p_contract_id);
604 END IF;
605 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
606 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
607 p_token1 => G_CONTRACT_ID_TOKEN,
608 p_token1_value => to_char(p_contract_id));
609 RAISE FND_API.G_EXC_ERROR;
610 -- RAISE NO_DATA_FOUND;
611 END IF;
612 -- Delete the records. The records are locked in the delete_contract API.
613 DELETE FROM OKC_CONTRACT_RISKS
614 WHERE BUSINESS_DOCUMENT_TYPE = contract_rec.contract_type
615 AND BUSINESS_DOCUMENT_ID = p_CONTRACT_ID
616 AND BUSINESS_DOCUMENT_VERSION = contract_rec.contract_version_num;
617 -- Close cursor
618 CLOSE contract_csr;
619 -- Standard check of p_commit
620 IF FND_API.To_Boolean( p_commit ) THEN
621 COMMIT WORK;
622 END IF;
623
624 -- Standard call to get message count and if count is 1, get message info.
625 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
626
627 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
629 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_risks');
630 END IF;
631
632
633 EXCEPTION
634 WHEN FND_API.G_EXC_ERROR THEN
635 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
636 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
637 g_module || l_api_name,
638 'Leaving delete_risks:FND_API.G_EXC_ERROR Exception');
639 END IF;
640 --close cursors
641 IF (contract_csr%ISOPEN) THEN
642 CLOSE contract_csr ;
643 END IF;
644 ROLLBACK TO delete_risks_PVT;
645 x_return_status := FND_API.G_RET_STS_ERROR;
646 FND_MSG_PUB.Count_And_Get(
647 p_count => x_msg_count,
648 p_data => x_msg_data
649 );
650
651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
652 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
654 g_module || l_api_name,
655 'Leaving delete_risks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
656 END IF;
657 --close cursors
658 IF (contract_csr%ISOPEN) THEN
659 CLOSE contract_csr ;
660 END IF;
661 ROLLBACK TO delete_risks_PVT;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 FND_MSG_PUB.Count_And_Get(
664 p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667
668 WHEN OTHERS THEN
669 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
670 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
671 g_module || l_api_name,
672 'Leaving delete_risks because of EXCEPTION: ' || sqlerrm);
673 END IF;
674 --close cursors
675 IF (contract_csr%ISOPEN) THEN
676 CLOSE contract_csr ;
677 END IF;
678 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
679 p_msg_name => G_UNEXPECTED_ERROR,
680 p_token1 => G_SQLCODE_TOKEN,
681 p_token1_value => sqlcode,
682 p_token2 => G_SQLERRM_TOKEN,
683 p_token2_value => sqlerrm);
684 ROLLBACK TO delete_risks_PVT;
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686 FND_MSG_PUB.Count_And_Get(
687 p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690 END delete_risks;
691
692
693 -- Start of comments
694 --API name : delete_related_contracts
695 --Type : Private.
696 --Function : Deletes related contracts of a particular Contract
697 --Pre-reqs : None.
698 --Parameters :
699 --IN : p_api_version IN NUMBER Required
700 -- : p_init_msg_list IN VARCHAR2 Optional
701 -- Default = FND_API.G_FALSE
702 -- : p_commit IN VARCHAR2 Optional
703 -- Default = FND_API.G_FALSE
704 -- : p_contract_id IN NUMBER Required
705 -- Contract ID of the contract whose related contracts are to be deleted
706 --OUT : x_return_status OUT VARCHAR2(1)
707 -- : x_msg_count OUT NUMBER
708 -- : x_msg_data OUT VARCHAR2(2000)
709 --Note :
710 -- End of comments
711 PROCEDURE delete_related_contracts(
712 p_api_version IN NUMBER,
713 p_init_msg_list IN VARCHAR2,
714 p_commit IN VARCHAR2,
715 p_contract_id IN NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_return_status OUT NOCOPY VARCHAR2) IS
719
720 l_api_name VARCHAR2(30);
721 l_api_version NUMBER;
722 BEGIN
723
724 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
726 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts');
727 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
728 'Contract Id is: ' || p_contract_id);
729 END IF;
730 l_api_name := 'delete_related_contracts';
731 l_api_version := 1.0;
732 -- Standard Start of API savepoint
733 SAVEPOINT delete_related_contracts_PVT;
734 -- Standard call to check for call compatibility.
735 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738 -- Initialize message list if p_init_msg_list is set to TRUE.
739 IF FND_API.to_Boolean( p_init_msg_list ) THEN
740 FND_MSG_PUB.initialize;
741 END IF;
742
743 -- Initialize API return status to success
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745
746 -- Delete the records. The records are locked in the delete_related_contracts API.
747 DELETE FROM OKC_REP_CONTRACT_RELS
748 WHERE CONTRACT_ID = p_CONTRACT_ID;
749
750 -- Standard check of p_commit
751 IF FND_API.To_Boolean( p_commit ) THEN
752 COMMIT WORK;
753 END IF;
754
755 -- Standard call to get message count and if count is 1, get message info.
756 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
757
758 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
759 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
760 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts');
761 END IF;
762
763
764 EXCEPTION
765 WHEN FND_API.G_EXC_ERROR THEN
766 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
767 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
768 g_module || l_api_name,
769 'Leaving delete_related_contracts:FND_API.G_EXC_ERROR Exception');
770 END IF;
771 ROLLBACK TO delete_related_contracts_PVT;
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 FND_MSG_PUB.Count_And_Get(
774 p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
780 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
781 g_module || l_api_name,
782 'Leaving delete_related_contracts:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
783 END IF;
784 ROLLBACK TO delete_related_contracts_PVT;
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786 FND_MSG_PUB.Count_And_Get(
787 p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790
791 WHEN OTHERS THEN
792 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
793 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
794 g_module || l_api_name,
795 'Leaving delete_related_contracts because of EXCEPTION: ' || sqlerrm);
796 END IF;
797 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
798 p_msg_name => G_UNEXPECTED_ERROR,
799 p_token1 => G_SQLCODE_TOKEN,
800 p_token1_value => sqlcode,
801 p_token2 => G_SQLERRM_TOKEN,
802 p_token2_value => sqlerrm);
803 ROLLBACK TO delete_related_contracts_PVT;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 FND_MSG_PUB.Count_And_Get(
806 p_count => x_msg_count,
807 p_data => x_msg_data
808 );
809 END delete_related_contracts;
810
811 -- Start of comments
812 --API name : delete_ACL
813 --Type : Private.
814 --Function : Deletes parties of a particular Contract
815 --Pre-reqs : None.
816 --Parameters :
817 --IN : p_api_version IN NUMBER Required
818 -- : p_init_msg_list IN VARCHAR2 Optional
819 -- Default = FND_API.G_FALSE
820 -- : p_commit IN VARCHAR2 Optional
821 -- Default = FND_API.G_FALSE
822 -- : p_contract_id IN NUMBER Required
823 -- Contract ID of the contract whose ACL is to be deleted
824 --OUT : x_return_status OUT VARCHAR2(1)
825 -- : x_msg_count OUT NUMBER
826 -- : x_msg_data OUT VARCHAR2(2000)
827 --Note :
828 -- End of comments
829 PROCEDURE delete_ACL(
830 p_api_version IN NUMBER,
831 p_init_msg_list IN VARCHAR2,
832 p_commit IN VARCHAR2,
833 p_contract_id IN NUMBER,
834 x_msg_data OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_return_status OUT NOCOPY VARCHAR2) IS
837
838 l_api_name VARCHAR2(30);
839 l_api_version NUMBER;
840 x_success VARCHAR2(1);
841 x_errcode NUMBER;
842
843 -- Query for the cursor
844 CURSOR acl_csr IS
845 SELECT
846 fgrant.grantee_type grantee_type,
847 fgrant.grantee_key grantee_key,
848 fgrant.instance_type instance_type,
849 fgrant.instance_set_id instance_set_id,
850 fmenu.menu_name menu_name,
851 fgrant.program_name program_name,
852 fgrant.program_tag program_tag
853 FROM FND_GRANTS fgrant, FND_OBJECTS fobj, FND_MENUS fmenu
854 WHERE fgrant.menu_id = fmenu.menu_id
855 AND fgrant.object_id = fobj.object_id
856 AND fobj.obj_name = 'OKC_REP_CONTRACT'
857 AND fgrant.instance_pk1_value = to_char(p_contract_id);
858
859 BEGIN
860
861 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
862 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
863 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL');
864 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
865 'Contract Id is: ' || p_contract_id);
866 END IF;
867 l_api_name := 'delete_ACL';
868 l_api_version := 1.0;
869 -- Standard Start of API savepoint
870 SAVEPOINT delete_ACL_PVT;
871 -- Standard call to check for call compatibility.
872 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
874 END IF;
875 -- Initialize message list if p_init_msg_list is set to TRUE.
876 IF FND_API.to_Boolean( p_init_msg_list ) THEN
877 FND_MSG_PUB.initialize;
878 END IF;
879
880 -- Initialize API return status to success
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882
883 FOR acl_rec IN acl_csr LOOP
884 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
885 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
886 'grantee_type is: ' || acl_rec.grantee_type);
887 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
888 'grantee_key is: ' || acl_rec.grantee_key);
889 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
890 'instance_type is: ' || acl_rec.instance_type);
891 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
892 'instance_set_id is: ' || acl_rec.instance_set_id);
893 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
894 'menu_name is: ' || acl_rec.menu_name);
895 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
896 'program_name is: ' || acl_rec.program_name);
897 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
898 'program_tag is: ' || acl_rec.program_tag);
899 END IF;
900 -- call FND_GRANT's delete api
901 FND_GRANTS_PKG.delete_grant(
902 p_grantee_type => acl_rec.grantee_type, -- USER or GROUP
903 p_grantee_key => acl_rec.grantee_key, -- user_id or group_id
904 p_object_name => G_REP_CONTRACT,
905 p_instance_type => acl_rec.instance_type, -- INSTANCE or SET
906 p_instance_set_id => acl_rec.instance_set_id, -- Instance set id.
907 p_instance_pk1_value => to_char(p_contract_id), -- Object PK Value
908 p_menu_name => acl_rec.menu_name, -- Menu to be deleted.
909 p_program_name => acl_rec.program_name, -- name of the program that handles grant.
910 p_program_tag => acl_rec.program_tag, -- tag used by the program that handles grant.
911 x_success => x_success, -- return param. 'T' or 'F'
912 x_errcode => x_errcode );
913 -----------------------------------------------------
914 IF (x_success = 'F' AND x_errcode < 0 ) THEN
915 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
916 ELSIF (x_success = 'F' AND x_errcode > 0) THEN
917 RAISE OKC_API.G_EXCEPTION_ERROR;
918 END IF;
919 --------------------------------------------------------
920 END LOOP;
921 -- Standard check of p_commit
922 IF FND_API.To_Boolean( p_commit ) THEN
923 COMMIT WORK;
924 END IF;
925
926 -- Standard call to get message count and if count is 1, get message info.
927 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
928
929 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
930 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
931 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL');
932 END IF;
933
934
935 EXCEPTION
936 WHEN FND_API.G_EXC_ERROR THEN
937 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
938 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
939 g_module || l_api_name,
940 'Leaving delete_ACL:FND_API.G_EXC_ERROR Exception');
941 END IF;
942 --close cursors
943 IF (acl_csr%ISOPEN) THEN
944 CLOSE acl_csr ;
945 END IF;
946 ROLLBACK TO delete_ACL_PVT;
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 FND_MSG_PUB.Count_And_Get(
949 p_count => x_msg_count,
950 p_data => x_msg_data
951 );
952
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
955 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
956 g_module || l_api_name,
957 'Leaving delete_ACL:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
958 END IF;
959 IF (acl_csr%ISOPEN) THEN
960 CLOSE acl_csr ;
961 END IF;
962 ROLLBACK TO delete_ACL_PVT;
963 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
964 FND_MSG_PUB.Count_And_Get(
965 p_count => x_msg_count,
966 p_data => x_msg_data
967 );
968
969 WHEN OTHERS THEN
970 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
972 g_module || l_api_name,
973 'Leaving delete_ACL because of EXCEPTION: ' || sqlerrm);
974 END IF;
975 IF (acl_csr%ISOPEN) THEN
976 CLOSE acl_csr ;
977 END IF;
978 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
979 p_msg_name => G_UNEXPECTED_ERROR,
980 p_token1 => G_SQLCODE_TOKEN,
981 p_token1_value => sqlcode,
982 p_token2 => G_SQLERRM_TOKEN,
983 p_token2_value => sqlerrm);
984 ROLLBACK TO delete_ACL_PVT;
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
986 FND_MSG_PUB.Count_And_Get(
987 p_count => x_msg_count,
988 p_data => x_msg_data
989 );
990 END delete_ACL;
991
992
993 -- Start of comments
994 --API name : delete_status_history
995 --Type : Private.
996 --Function : Deletes status history records of a contract version
997 --Pre-reqs : None.
998 --Parameters :
999 --IN : p_api_version IN NUMBER Required
1000 -- : p_init_msg_list IN VARCHAR2 Optional
1001 -- Default = FND_API.G_FALSE
1002 -- : p_commit IN VARCHAR2 Optional
1003 -- Default = FND_API.G_FALSE
1004 -- : p_contract_id IN NUMBER Required
1005 -- Contract ID of the contract whose status history is to be deleted
1006 -- : p_contract_version IN NUMBER Required
1007 -- Contract version of the contract whose status history is to be deleted
1008 --OUT : x_return_status OUT VARCHAR2(1)
1009 -- : x_msg_count OUT NUMBER
1010 -- : x_msg_data OUT VARCHAR2(2000)
1011 --Note :
1012 -- End of comments
1013 PROCEDURE delete_status_history(
1014 p_api_version IN NUMBER,
1015 p_init_msg_list IN VARCHAR2,
1016 p_commit IN VARCHAR2,
1017 p_contract_id IN NUMBER,
1018 p_contract_version IN NUMBER,
1019 x_msg_data OUT NOCOPY VARCHAR2,
1020 x_msg_count OUT NOCOPY NUMBER,
1021 x_return_status OUT NOCOPY VARCHAR2) IS
1022
1023 l_api_name VARCHAR2(30);
1024 l_api_version NUMBER;
1025
1026 BEGIN
1027
1028 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1029 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1030 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history');
1031 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1032 'Contract Id is: ' || to_char(p_contract_id));
1033 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1034 'Contract Version is: ' || to_char(p_contract_version));
1035 END IF;
1036 l_api_name := 'delete_status_history';
1037 l_api_version := 1.0;
1038 -- Standard Start of API savepoint
1039 SAVEPOINT delete_status_history_PVT;
1040 -- Standard call to check for call compatibility.
1041 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043 END IF;
1044 -- Initialize message list if p_init_msg_list is set to TRUE.
1045 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1046 FND_MSG_PUB.initialize;
1047 END IF;
1048
1049 -- Initialize API return status to success
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 -- Delete the records. The records are locked in the delete_contract API.
1053 DELETE FROM OKC_REP_CON_STATUS_HIST
1054 WHERE CONTRACT_ID = p_CONTRACT_ID
1055 AND CONTRACT_VERSION_NUM = p_contract_version;
1056
1057 -- Standard check of p_commit
1058 IF FND_API.To_Boolean( p_commit ) THEN
1059 COMMIT WORK;
1060 END IF;
1061
1062 -- Standard call to get message count and if count is 1, get message info.
1063 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1064
1065 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1067 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history');
1068 END IF;
1069
1070
1071 EXCEPTION
1072 WHEN FND_API.G_EXC_ERROR THEN
1073 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1074 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1075 g_module || l_api_name,
1076 'Leaving delete_status_history:FND_API.G_EXC_ERROR Exception');
1077 END IF;
1078 ROLLBACK TO delete_status_history_PVT;
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 FND_MSG_PUB.Count_And_Get(
1081 p_count => x_msg_count,
1082 p_data => x_msg_data
1083 );
1084
1085 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1086 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1087 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1088 g_module || l_api_name,
1089 'Leaving delete_status_history:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1090 END IF;
1091 ROLLBACK TO delete_status_history_PVT;
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093 FND_MSG_PUB.Count_And_Get(
1094 p_count => x_msg_count,
1095 p_data => x_msg_data
1096 );
1097
1098 WHEN OTHERS THEN
1099 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1100 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1101 g_module || l_api_name,
1102 'Leaving delete_status_history because of EXCEPTION: ' || sqlerrm);
1103 END IF;
1104 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1105 p_msg_name => G_UNEXPECTED_ERROR,
1106 p_token1 => G_SQLCODE_TOKEN,
1107 p_token1_value => sqlcode,
1108 p_token2 => G_SQLERRM_TOKEN,
1109 p_token2_value => sqlerrm);
1110 ROLLBACK TO delete_status_history_PVT;
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1112 FND_MSG_PUB.Count_And_Get(
1113 p_count => x_msg_count,
1114 p_data => x_msg_data
1115 );
1116 END delete_status_history;
1117
1118
1119
1120
1121
1122 -- Start of comments
1123 --API name : delete_approval_history
1124 --Type : Private.
1125 --Function : Deletes contract approval history records of a contract version
1126 --Pre-reqs : None.
1127 --Parameters :
1128 --IN : p_api_version IN NUMBER Required
1129 -- : p_init_msg_list IN VARCHAR2 Optional
1130 -- Default = FND_API.G_FALSE
1131 -- : p_commit IN VARCHAR2 Optional
1132 -- Default = FND_API.G_FALSE
1133 -- : p_contract_id IN NUMBER Required
1134 -- Contract ID of the contract whose approval history is to be deleted
1135 -- : p_contract_version IN NUMBER Required
1136 -- Contract version of the contract whose approval history is to be deleted
1137 --OUT : x_return_status OUT VARCHAR2(1)
1138 -- : x_msg_count OUT NUMBER
1139 -- : x_msg_data OUT VARCHAR2(2000)
1140 --Note :
1141 -- End of comments
1142 PROCEDURE delete_approval_history(
1143 p_api_version IN NUMBER,
1144 p_init_msg_list IN VARCHAR2,
1145 p_commit IN VARCHAR2,
1146 p_contract_id IN NUMBER,
1147 p_contract_version IN NUMBER,
1148 x_msg_data OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER,
1150 x_return_status OUT NOCOPY VARCHAR2)IS
1151
1152 l_api_name VARCHAR2(30);
1153 l_api_version NUMBER;
1154
1155 BEGIN
1156
1157 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1158 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1159 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history');
1160 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1161 'Contract Id is: ' || to_char(p_contract_id));
1162 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1163 'Contract Version is: ' || to_char(p_contract_version));
1164 END IF;
1165 l_api_name := 'delete_approval_history';
1166 l_api_version := 1.0;
1167 -- Standard Start of API savepoint
1168 SAVEPOINT delete_approval_history_PVT;
1169 -- Standard call to check for call compatibility.
1170 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 END IF;
1173 -- Initialize message list if p_init_msg_list is set to TRUE.
1174 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1175 FND_MSG_PUB.initialize;
1176 END IF;
1177
1178 -- Initialize API return status to success
1179 x_return_status := FND_API.G_RET_STS_SUCCESS;
1180
1181 -- Delete the records. The records are locked in the delete_contract API.
1182 DELETE FROM OKC_REP_CON_APPROVALS
1183 WHERE CONTRACT_ID = p_CONTRACT_ID
1184 AND CONTRACT_VERSION_NUM = p_contract_version;
1185
1186 -- Standard check of p_commit
1187 IF FND_API.To_Boolean( p_commit ) THEN
1188 COMMIT WORK;
1189 END IF;
1190
1191 -- Standard call to get message count and if count is 1, get message info.
1192 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1193
1194 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1195 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1196 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history');
1197 END IF;
1198
1199
1200 EXCEPTION
1201 WHEN FND_API.G_EXC_ERROR THEN
1202 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1204 g_module || l_api_name,
1205 'Leaving delete_approval_history:FND_API.G_EXC_ERROR Exception');
1206 END IF;
1207 ROLLBACK TO delete_approval_history_PVT;
1208 x_return_status :=FND_API.G_RET_STS_ERROR;
1209 FND_MSG_PUB.Count_And_Get(
1210 p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213
1214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1215 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1216 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1217 g_module || l_api_name,
1218 'Leaving delete_approval_history:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1219 END IF;
1220 ROLLBACK TO delete_approval_history_PVT;
1221 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
1222 FND_MSG_PUB.Count_And_Get(
1223 p_count => x_msg_count,
1224 p_data => x_msg_data
1225 );
1226
1227 WHEN OTHERS THEN
1228 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1229 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1230 g_module || l_api_name,
1231 'Leaving delete_approval_history because of EXCEPTION: ' || sqlerrm);
1232 END IF;
1233 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1234 p_msg_name => G_UNEXPECTED_ERROR,
1235 p_token1 => G_SQLCODE_TOKEN,
1236 p_token1_value => sqlcode,
1237 p_token2 => G_SQLERRM_TOKEN,
1238 p_token2_value => sqlerrm);
1239 ROLLBACK TO delete_approval_history_PVT;
1240 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
1241 FND_MSG_PUB.Count_And_Get(
1242 p_count => x_msg_count,
1243 p_data => x_msg_data
1244 );
1245 END delete_approval_history;
1246
1247 -- Start of comments
1248 --API name : delete_bookmarks
1249 --Type : Private.
1250 --Function : Deletes bookmarks for a given contract
1251 --Pre-reqs : None.
1252 --Parameters :
1253 --IN : p_api_version IN NUMBER Required
1254 -- : p_init_msg_list IN VARCHAR2 Optional
1255 -- Default = FND_API.G_FALSE
1256 -- : p_commit IN VARCHAR2 Optional
1257 -- Default = FND_API.G_FALSE
1258 -- : p_contract_type IN VARCHAR2 Required
1259 -- Contract Type of the contract whose status history is to be deleted
1260 -- : p_contract_id IN NUMBER Required
1261 -- Contract ID of the contract whose status history is to be deleted
1262 --OUT : x_return_status OUT VARCHAR2(1)
1263 -- : x_msg_count OUT NUMBER
1264 -- : x_msg_data OUT VARCHAR2(2000)
1265 --Note :
1266 -- End of comments
1267 PROCEDURE delete_bookmarks(
1268 p_api_version IN NUMBER,
1269 p_init_msg_list IN VARCHAR2,
1270 p_commit IN VARCHAR2,
1271 p_contract_type IN VARCHAR2,
1272 p_contract_id IN NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 x_msg_count OUT NOCOPY NUMBER,
1275 x_return_status OUT NOCOPY VARCHAR2) IS
1276
1277 l_api_name VARCHAR2(30);
1278 l_api_version NUMBER;
1279
1280 BEGIN
1281
1282 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1283 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1284 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks');
1285 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1286 'Contract Id is: ' || p_contract_id);
1287 END IF;
1288 l_api_name := 'delete_bookmarks';
1289 l_api_version := 1.0;
1290 -- Standard Start of API savepoint
1291 SAVEPOINT delete_bookmarks_PVT;
1292 -- Standard call to check for call compatibility.
1293 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1295 END IF;
1296 -- Initialize message list if p_init_msg_list is set to TRUE.
1297 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1298 FND_MSG_PUB.initialize;
1299 END IF;
1300
1301 -- Initialize API return status to success
1302 x_return_status := FND_API.G_RET_STS_SUCCESS;
1303
1304
1305
1306 -- Delete the records. The records are locked in the delete_contract API.
1307 DELETE FROM OKC_REP_BOOKMARKS
1308 WHERE OBJECT_TYPE = p_contract_type
1309 AND OBJECT_ID = p_CONTRACT_ID
1310 AND BOOKMARK_TYPE_CODE = G_CONTRACT_BOOKMARK_TYPE;
1311
1312 -- Standard check of p_commit
1313 IF FND_API.To_Boolean( p_commit ) THEN
1314 COMMIT WORK;
1315 END IF;
1316
1317 -- Standard call to get message count and if count is 1, get message info.
1318 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1319
1320 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1321 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1322 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks');
1323 END IF;
1324
1325
1326 EXCEPTION
1327 WHEN FND_API.G_EXC_ERROR THEN
1328 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1329 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1330 g_module || l_api_name,
1331 'Leaving delete_bookmarks:FND_API.G_EXC_ERROR Exception');
1332 END IF;
1333 ROLLBACK TO delete_bookmarks_PVT;
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 FND_MSG_PUB.Count_And_Get(
1336 p_count => x_msg_count,
1337 p_data => x_msg_data
1338 );
1339
1340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1341 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1342 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1343 g_module || l_api_name,
1344 'Leaving delete_bookmarks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1345 END IF;
1346 ROLLBACK TO delete_bookmarks_PVT;
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 FND_MSG_PUB.Count_And_Get(
1349 p_count => x_msg_count,
1350 p_data => x_msg_data
1351 );
1352
1353 WHEN OTHERS THEN
1354 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1355 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1356 g_module || l_api_name,
1357 'Leaving delete_bookmarks because of EXCEPTION: ' || sqlerrm);
1358 END IF;
1359 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1360 p_msg_name => G_UNEXPECTED_ERROR,
1361 p_token1 => G_SQLCODE_TOKEN,
1362 p_token1_value => sqlcode,
1363 p_token2 => G_SQLERRM_TOKEN,
1364 p_token2_value => sqlerrm);
1365 ROLLBACK TO delete_bookmarks_PVT;
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1367 FND_MSG_PUB.Count_And_Get(
1368 p_count => x_msg_count,
1369 p_data => x_msg_data
1370 );
1371 END delete_bookmarks;
1372
1373
1374 ---------------------------------------------------------------------------
1375 -- PROCEDURE Lock_Row
1376 ---------------------------------------------------------------------------
1377 -----------------------------------
1378 -- Lock_Row for:OKC_REP_CONTRACTS_ALL --
1379 -----------------------------------
1380 FUNCTION Lock_Contract_Header(
1381 p_contract_id IN NUMBER,
1382 p_object_version_number IN NUMBER
1383 ) RETURN VARCHAR2 IS
1384
1385 l_return_status VARCHAR2(1);
1386 l_object_version_number OKC_REP_CONTRACTS_ALL.OBJECT_VERSION_NUMBER%TYPE;
1387 l_row_notfound BOOLEAN := FALSE;
1388 l_api_name VARCHAR2(30);
1389
1390 CURSOR lock_csr (cp_contract_id NUMBER, cp_object_version_number NUMBER) IS
1391 SELECT object_version_number
1392 FROM OKC_REP_CONTRACTS_ALL
1393 WHERE CONTRACT_ID = cp_contract_id
1394 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1395 FOR UPDATE OF object_version_number NOWAIT;
1396
1397 CURSOR lchk_csr (cp_contract_id NUMBER) IS
1398 SELECT object_version_number
1399 FROM OKC_REP_CONTRACTS_ALL
1400 WHERE CONTRACT_ID = cp_contract_id;
1401 BEGIN
1402
1403 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1404 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1405 'Entered Function OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header');
1406 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1407 'Contract Id is: ' || p_contract_id);
1408 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1409 'Object Version is: ' || p_object_version_number);
1410 END IF;
1411
1412
1413 BEGIN
1414 l_api_name := 'lock_contract_header';
1415 OPEN lock_csr( p_contract_id, p_object_version_number );
1416 FETCH lock_csr INTO l_object_version_number;
1417 l_row_notfound := lock_csr%NOTFOUND;
1418 CLOSE lock_csr;
1419
1420 EXCEPTION
1421 WHEN E_Resource_Busy THEN
1422 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1423 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1424 g_module || l_api_name,
1425 'Leaving Function Lock_Contract_Header:E_Resource_Busy Exception');
1426 END IF;
1427
1428 IF (lock_csr%ISOPEN) THEN
1429 CLOSE lock_csr;
1430 END IF;
1431 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1432 RETURN(FND_API.G_RET_STS_ERROR );
1433 END;
1434
1435 IF ( l_row_notfound ) THEN
1436 l_return_status :=FND_API.G_RET_STS_ERROR;
1437
1438 OPEN lchk_csr(p_contract_id);
1439 FETCH lchk_csr INTO l_object_version_number;
1440 l_row_notfound := lchk_csr%NOTFOUND;
1441 CLOSE lchk_csr;
1442
1443 IF (l_row_notfound) THEN
1444 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_DELETED);
1445 ELSIF l_object_version_number > p_object_version_number THEN
1446 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1447 ELSIF l_object_version_number = -1 THEN
1448 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1449 ELSE -- it can be the only above condition. It can happen after restore version
1450 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1451 END IF;
1452 ELSE
1453 l_return_status :=FND_API.G_RET_STS_SUCCESS;
1454 END IF;
1455
1456 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1457 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1458 g_module || l_api_name,
1459 'Leaving Function Lock_Contract_Header');
1460 END IF;
1461
1462 RETURN( l_return_status );
1463
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466
1467 IF (lock_csr%ISOPEN) THEN
1468 CLOSE lock_csr;
1469 END IF;
1470 IF (lchk_csr%ISOPEN) THEN
1471 CLOSE lchk_csr;
1472 END IF;
1473
1474 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1475 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1476 g_module || l_api_name,
1477 'Leaving Function Lock_Contract_Header because of EXCEPTION: '||sqlerrm);
1478 END IF;
1479
1480 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1481 p_msg_name => G_UNEXPECTED_ERROR,
1482 p_token1 => G_SQLCODE_TOKEN,
1483 p_token1_value => sqlcode,
1484 p_token2 => G_SQLERRM_TOKEN,
1485 p_token2_value => sqlerrm);
1486
1487 RETURN(FND_API.G_RET_STS_UNEXP_ERROR );
1488 END Lock_Contract_Header;
1489
1490
1491 -- Start of comments
1492 --API name : lock_contract_header
1493 --Type : Private.
1494 --Function : Locks a row in OKC_REP_CONTRACTS_ALL table
1495 --Pre-reqs : None.
1496 --Parameters :
1497 --IN : p_contract_id IN NUMBER Required
1498 -- Contract ID of the contract to be locked.
1499 -- : p_object_version_number IN NUMBER Required
1500 -- Object version number of the contract to be locked
1501 --OUT : x_return_status OUT VARCHAR2(1)
1502 -- : x_msg_count OUT NUMBER
1503 -- : x_msg_data OUT VARCHAR2(2000)
1504 --Note :
1505 -- End of comments
1506 PROCEDURE Lock_Contract_Header(
1507 p_contract_id IN NUMBER,
1508 p_object_version_number IN NUMBER,
1509 x_return_status OUT NOCOPY VARCHAR2
1510 ) IS
1511
1512 l_api_name VARCHAR2(30);
1513
1514 BEGIN
1515 l_api_name := 'Lock_Contract_header';
1516 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1517 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1518 'Entered OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header');
1519 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1520 'Contract Id is: ' || p_contract_id);
1521 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1522 'Object Version is: ' || p_object_version_number);
1523 END IF;
1524
1525 --------------------------------------------
1526 -- Call the LOCK_ROW
1527 --------------------------------------------
1528 x_return_status := Lock_Contract_Header(
1529 p_contract_id => p_contract_id,
1530 p_object_version_number => p_object_version_number
1531 );
1532 ---------------------------------------------------------
1533 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1535 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1536 RAISE FND_API.G_EXC_ERROR;
1537 END IF;
1538 ----------------------------------------------------------
1539 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1540 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1541 g_module || l_api_name,
1542 'Leaving Lock_Contract_Header');
1543 END IF;
1544
1545 EXCEPTION
1546 WHEN FND_API.G_EXC_ERROR THEN
1547 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1548 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1549 g_module || l_api_name,
1550 'Leaving Lock_Contract_Header:FND_API.G_EXC_ERROR Exception');
1551 END IF;
1552 x_return_status := FND_API.G_RET_STS_ERROR;
1553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1554 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1555 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1556 g_module || l_api_name,
1557 'Leaving Lock_Contract_Header:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1558 END IF;
1559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1560
1561 WHEN OTHERS THEN
1562 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1563 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1564 g_module || l_api_name,
1565 'Leaving Lock_Contract_Header because of EXCEPTION: '||sqlerrm);
1566 END IF;
1567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1568 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1569 p_msg_name => G_UNEXPECTED_ERROR,
1570 p_token1 => G_SQLCODE_TOKEN,
1571 p_token1_value => sqlcode,
1572 p_token2 => G_SQLERRM_TOKEN,
1573 p_token2_value => sqlerrm);
1574
1575 END Lock_Contract_Header;
1576
1577
1578 -- Start of comments
1579 --API name : delete_contract
1580 --Type : Private.
1581 --Function : Deletes a Contract
1582 --Pre-reqs : None.
1583 --Parameters :
1584 --IN : p_api_version IN NUMBER Required
1585 -- : p_init_msg_list IN VARCHAR2 Optional
1586 -- Default = FND_API.G_FALSE
1587 -- : p_commit IN VARCHAR2 Optional
1588 -- Default = FND_API.G_FALSE
1589 -- : p_contract_id IN NUMBER Required
1590 -- Contract ID of the contract to be deleted
1591 --OUT : x_return_status OUT VARCHAR2(1)
1592 -- : x_msg_count OUT NUMBER
1593 -- : x_msg_data OUT VARCHAR2(2000)
1594 --Note :
1595 -- End of comments
1596 PROCEDURE delete_contract(
1597 p_api_version IN NUMBER,
1598 p_init_msg_list IN VARCHAR2,
1599 p_commit IN VARCHAR2,
1600 p_contract_id IN NUMBER,
1601 x_msg_data OUT NOCOPY VARCHAR2,
1602 x_msg_count OUT NOCOPY NUMBER,
1603 x_return_status OUT NOCOPY VARCHAR2) IS
1604
1605 l_api_name VARCHAR2(30);
1606 l_api_version NUMBER;
1607 l_contract_type OKC_REP_CONTRACTS_ALL.CONTRACT_TYPE%TYPE;
1608 l_prev_version OKC_REP_CONTRACTS_ALL.CONTRACT_VERSION_NUM%TYPE;
1609 l_prev_con_vers_status OKC_REP_CONTRACTS_ALL.contract_status_code%TYPE;
1610 l_is_activated VARCHAR2(1);
1611
1612
1613 CURSOR contract_csr IS
1614 SELECT contract_type, contract_version_num
1615 FROM OKC_REP_CONTRACTS_ALL
1616 WHERE contract_id = p_contract_id;
1617
1618 CURSOR prev_con_vers_status (p_contract_id IN NUMBER, p_con_version IN NUMBER) IS
1619 SELECT contract_status_code
1620 FROM okc_rep_contract_vers
1621 WHERE contract_id = p_contract_id
1622 AND contract_version_num = p_con_version - 1;
1623
1624 contract_rec contract_csr%ROWTYPE;
1625
1626 BEGIN
1627
1628 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1630 'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_contract');
1631 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1632 'Contract Id is: ' || p_contract_id);
1633 END IF;
1634 l_api_name := 'delete_contract';
1635 l_api_version := 1.0;
1636 -- Standard Start of API savepoint
1637 SAVEPOINT delete_contract_PVT;
1638 -- Standard call to check for call compatibility.
1639 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1641 END IF;
1642 -- Initialize message list if p_init_msg_list is set to TRUE.
1643 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1644 FND_MSG_PUB.initialize;
1645 END IF;
1646
1647 -- Initialize API return status to success
1648 x_return_status := FND_API.G_RET_STS_SUCCESS;
1649
1650 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1651 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1652 'Calling OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header()');
1653 END IF;
1654 -- Lock the contract header
1655 Lock_Contract_Header(
1656 p_contract_id => p_contract_id,
1657 p_object_version_number => NULL,
1658 x_return_status => x_return_status
1659 );
1660 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1661 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1662 'OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header return status is: '
1663 || x_return_status);
1664 END IF;
1665 -----------------------------------------------------
1666 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1667 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1668 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1669 RAISE OKC_API.G_EXCEPTION_ERROR;
1670 END IF;
1671 -----------------------------------------------------
1672
1673 -- The contract version
1674 -- Get contract_type and version required for deliverables and documents APIs
1675 OPEN contract_csr;
1676 FETCH contract_csr INTO contract_rec;
1677 IF(contract_csr%NOTFOUND) THEN
1678 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1679 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1680 G_MODULE||l_api_name,
1681 'Invalid Contract Id: '|| p_contract_id);
1682 END IF;
1683 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1684 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
1685 p_token1 => G_CONTRACT_ID_TOKEN,
1686 p_token1_value => to_char(p_contract_id));
1687 RAISE FND_API.G_EXC_ERROR;
1688 -- RAISE NO_DATA_FOUND;
1689 END IF;
1690
1691
1692 if (contract_rec.contract_version_num = 1) THEN
1693 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1694 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1695 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts()');
1696 END IF;
1697 delete_contacts(
1698 p_api_version => 1.0,
1699 p_commit => FND_API.G_FALSE,
1700 p_init_msg_list => FND_API.G_FALSE,
1701 p_contract_id => p_contract_id,
1702 x_msg_data => x_msg_data,
1703 x_msg_count => x_msg_count,
1704 x_return_status => x_return_status);
1705 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1706 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1707 'OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts return status is: '
1708 || x_return_status);
1709 END IF;
1710 -----------------------------------------------------
1711 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1712 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1713 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1714 RAISE OKC_API.G_EXCEPTION_ERROR;
1715 END IF;
1716 -----------------------------------------------------
1717
1718
1719 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1720 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1721 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_parties()');
1722 END IF;
1723 delete_parties(
1724 p_api_version => 1.0,
1725 p_init_msg_list => FND_API.G_FALSE,
1726 p_commit => FND_API.G_FALSE,
1727 p_contract_id => p_contract_id,
1728 x_msg_data => x_msg_data,
1729 x_msg_count => x_msg_count,
1730 x_return_status => x_return_status);
1731 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1732 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1733 'OKC_REP_CONTRACT_PROCESS_PVT.delete_parties return status is: '
1734 || x_return_status);
1735 END IF;
1736 -----------------------------------------------------
1737 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1738 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1739 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1740 RAISE OKC_API.G_EXCEPTION_ERROR;
1741 END IF;
1742 --------------------------------------------------------
1743
1744
1745 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1746 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1747 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_risks()');
1748 END IF;
1749 delete_risks(
1750 p_api_version => 1.0,
1751 p_init_msg_list => FND_API.G_FALSE,
1752 p_commit => FND_API.G_FALSE,
1753 p_contract_id => p_contract_id,
1754 x_msg_data => x_msg_data,
1755 x_msg_count => x_msg_count,
1756 x_return_status => x_return_status);
1757 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1758 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1759 'OKC_REP_CONTRACT_PROCESS_PVT.delete_risks return status is: '
1760 || x_return_status);
1761 END IF;
1762 -----------------------------------------------------
1763 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1764 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1765 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1766 RAISE OKC_API.G_EXCEPTION_ERROR;
1767 END IF;
1768 --------------------------------------------------------
1769
1770
1771 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1772 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1773 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL()');
1774 END IF;
1775 delete_ACL(
1776 p_api_version => 1.0,
1777 p_init_msg_list => FND_API.G_FALSE,
1778 p_commit => FND_API.G_FALSE,
1779 p_contract_id => p_contract_id,
1780 x_msg_data => x_msg_data,
1781 x_msg_count => x_msg_count,
1782 x_return_status => x_return_status);
1783 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1784 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1785 'OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL return status is: '
1786 || x_return_status);
1787 END IF;
1788 -----------------------------------------------------
1789 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1791 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1792 RAISE OKC_API.G_EXCEPTION_ERROR;
1793 END IF;
1794 --------------------------------------------------------
1795
1796
1797 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1798 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1799 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts()');
1800 END IF;
1801 delete_related_contracts(
1802 p_api_version => 1.0,
1803 p_init_msg_list => FND_API.G_FALSE,
1804 p_commit => FND_API.G_FALSE,
1805 p_contract_id => p_contract_id,
1806 x_msg_data => x_msg_data,
1807 x_msg_count => x_msg_count,
1808 x_return_status => x_return_status);
1809 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1810 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1811 'OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts return status is: '
1812 || x_return_status);
1813 END IF;
1814 -----------------------------------------------------
1815 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1816 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1817 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1818 RAISE OKC_API.G_EXCEPTION_ERROR;
1819 END IF;
1820 --------------------------------------------------------
1821
1822
1823 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1824 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1825 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history()');
1826 END IF;
1827 delete_status_history(
1828 p_api_version => 1.0,
1829 p_init_msg_list => FND_API.G_FALSE,
1830 p_commit => FND_API.G_FALSE,
1831 p_contract_id => p_contract_id,
1832 p_contract_version => contract_rec.contract_version_num,
1833 x_msg_data => x_msg_data,
1834 x_msg_count => x_msg_count,
1835 x_return_status => x_return_status);
1836 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1837 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1838 'OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history return status is: '
1839 || x_return_status);
1840 END IF;
1841 -----------------------------------------------------
1842 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1843 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1844 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1845 RAISE OKC_API.G_EXCEPTION_ERROR;
1846 END IF;
1847 --------------------------------------------------------
1848
1849
1850 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1851 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1852 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history()');
1853 END IF;
1854 delete_approval_history(
1855 p_api_version => 1.0,
1856 p_init_msg_list => FND_API.G_FALSE,
1857 p_commit => FND_API.G_FALSE,
1858 p_contract_id => p_contract_id,
1859 p_contract_version => contract_rec.contract_version_num,
1860 x_msg_data => x_msg_data,
1861 x_msg_count => x_msg_count,
1862 x_return_status => x_return_status);
1863 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1864 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1865 'OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history return status is: '
1866 || x_return_status);
1867 END IF;
1868 -----------------------------------------------------
1869 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1870 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1871 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1872 RAISE OKC_API.G_EXCEPTION_ERROR;
1873 END IF;
1874 --------------------------------------------------------
1875
1876
1877 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1879 'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks()');
1880 END IF;
1881 delete_bookmarks(
1882 p_api_version => 1.0,
1883 p_init_msg_list => FND_API.G_FALSE,
1884 p_commit => FND_API.G_FALSE,
1885 p_contract_type => contract_rec.contract_type,
1886 p_contract_id => p_contract_id,
1887 x_msg_data => x_msg_data,
1888 x_msg_count => x_msg_count,
1889 x_return_status => x_return_status);
1890 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1891 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1892 'OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks return status is: '
1893 || x_return_status);
1894 END IF;
1895 -----------------------------------------------------
1896 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1897 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1898 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1899 RAISE OKC_API.G_EXCEPTION_ERROR;
1900 END IF;
1901 --------------------------------------------------------
1902
1903 END IF; -- (contract_rec.contract_version_num = 1)
1904
1905 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1906 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1907 'Calling OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments');
1908 END IF;
1909 -- Delete Contract Documents
1910 -- The following package call should be uncommented once the package is compiling.
1911 OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments(
1912 p_api_version => 1.0,
1913 p_business_document_type => contract_rec.contract_type,
1914 p_business_document_id => p_contract_id,
1915 p_business_document_version => G_CURRENT_VERSION,
1916 x_return_status => x_return_status,
1917 x_msg_count => x_msg_count,
1918 x_msg_data => x_msg_data
1919 );
1920 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1922 'OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments return status is : '
1923 || x_return_status);
1924 END IF;
1925 -----------------------------------------------------
1926 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1927 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1928 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1929 RAISE OKC_API.G_EXCEPTION_ERROR;
1930 END IF;
1931 --------------------------------------------------------
1932
1933 IF (contract_rec.contract_version_num > 1) THEN
1934
1935 -- Call this API only if the contract has previous versions
1936 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1937 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1938 'Calling OKC_CONTRACT_DOCS_PVT.reset_bus_doc_ver_to_current');
1939 END IF;
1940
1941 -- Bug 5044121
1942 -- The following API will change the business document version number attribute
1943 -- of all the documents of the previous version to the deleted version to -99
1944 -- so that the UI will show documents correctly
1945 x_return_status := OKC_CONTRACT_DOCS_PVT.reset_bus_doc_ver_to_current(
1946 p_business_document_type => contract_rec.contract_type,
1947 p_business_document_id => p_contract_id,
1948 p_business_document_version => contract_rec.contract_version_num);
1949
1950 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1951 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1952 'OKC_CONTRACT_DOCS_PVT.reset_bus_doc_ver_to_current return status is : '
1953 || x_return_status);
1954 END IF;
1955
1956 -----------------------------------------------------
1957 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1958 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1959 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1960 RAISE OKC_API.G_EXCEPTION_ERROR;
1961 END IF;
1962 --------------------------------------------------------
1963
1964 END IF;
1965
1966 -- Repository Enhancement 12.1 (For Delete Action)
1967 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1968 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Calling Ptivate API to Delete the doc');
1969 END IF;
1970
1971 -- If the contract has only one version, delete the terms.
1972 -- Otherwise the only the terms versions have to be deleted.
1973 -- For Bug# 6902073
1974
1975 IF(contract_rec.contract_version_num = 1) THEN
1976
1977 OKC_TERMS_UTIL_PVT.Delete_Doc(
1978 x_return_status => x_return_status,
1979 p_doc_type => contract_rec.contract_type,
1980 p_doc_id => p_contract_id
1981 );
1982 --------------------------------------------
1983 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1984 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1985 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1986 RAISE OKC_API.G_EXCEPTION_ERROR;
1987 END IF;
1988
1989 ELSE
1990
1991 -- Restore the previous version's terms details from the history table to the base tables
1992 OKC_TERMS_VERSION_GRP.Restore_Doc_Version(
1993 p_api_version => 1.0,
1994 x_return_status => x_return_status,
1995 x_msg_data => x_msg_data,
1996 x_msg_count => x_msg_count,
1997 p_doc_type => contract_rec.contract_type,
1998 p_doc_id => p_contract_id,
1999 p_version_number => contract_rec.contract_version_num - 1
2000 );
2001 --------------------------------------------
2002 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2003 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2004 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2005 RAISE OKC_API.G_EXCEPTION_ERROR;
2006 END IF;
2007 -- Deletes the previous version's terms details in the history table
2008 OKC_TERMS_VERSION_PVT.Delete_Doc_Version(
2009 x_return_status => x_return_status,
2010 p_doc_type => contract_rec.contract_type,
2011 p_doc_id => p_contract_id,
2012 p_version_number => contract_rec.contract_version_num - 1
2013 );
2014 --------------------------------------------
2015 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2017 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2018 RAISE OKC_API.G_EXCEPTION_ERROR;
2019 END IF;
2020
2021 -- Changes for Bug# 6902073 Ends
2022 END IF;
2023 -- Repository Enhancement 12.1 Ends(For Delete Action)
2024
2025
2026 IF(contract_rec.contract_version_num = 1) THEN
2027
2028 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2029 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2030 'Calling OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables');
2031 END IF;
2032
2033 -- Delete Deliverables
2034 OKC_DELIVERABLE_PROCESS_PVT.deleteDeliverables (
2035 p_api_version => 1.0,
2036 p_init_msg_list => FND_API.G_FALSE,
2037 p_bus_doc_id => p_contract_id,
2038 p_bus_doc_type => contract_rec.contract_type,
2039 p_bus_doc_version => G_CURRENT_VERSION,
2040 x_return_status => x_return_status,
2041 x_msg_count => x_msg_count,
2042 x_msg_data => x_msg_data);
2043
2044 ELSE
2045
2046 -- Get previous contract version status
2047 OPEN prev_con_vers_status(p_contract_id, contract_rec.contract_version_num);
2048 FETCH prev_con_vers_status INTO l_prev_con_vers_status;
2049 CLOSE prev_con_vers_status;
2050
2051 IF (l_prev_con_vers_status = G_STATUS_SIGNED) THEN
2052 l_is_activated := 'Y';
2053 ELSE
2054 l_is_activated := 'N';
2055 END IF;
2056
2057 OKC_DELIVERABLE_PROCESS_PVT.deleteDeliverables(
2058 p_api_version => 1.0,
2059 p_init_msg_list => FND_API.G_FALSE,
2060 p_bus_doc_id => p_contract_id,
2061 p_bus_doc_type => contract_rec.contract_type,
2062 p_bus_doc_version => contract_rec.contract_version_num,
2063 p_prev_del_active => l_is_activated,
2064 p_revert_dels => 'Y',
2065 x_return_status => x_return_status,
2066 x_msg_count => x_msg_count,
2067 x_msg_data => x_msg_data);
2068
2069 END IF;
2070
2071 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2072 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2073 'OKC_DELIVERABLE_PROCESS_PVT.deleteDeliverables return status is : '
2074 || x_return_status);
2075 END IF;
2076
2077 -----------------------------------------------------
2078 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2079 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2080 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2081 RAISE OKC_API.G_EXCEPTION_ERROR;
2082 END IF;
2083 --------------------------------------------------------
2084
2085
2086 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2087 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2088 'Deleting Contract header record');
2089 END IF;
2090 -- Delete contract header from the main header table OKC_REP_CONTRACTS_ALL
2091 DELETE FROM OKC_REP_CONTRACTS_ALL
2092 WHERE contract_id = p_contract_id;
2093
2094 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2095 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2096 'Deleted Contract header record');
2097 END IF;
2098 -- If Version > 1, copy the latest version from the archive table.
2099 if (contract_rec.contract_version_num > 1) THEN
2100 l_prev_version := contract_rec.contract_version_num - 1;
2101 INSERT INTO OKC_REP_CONTRACTS_ALL(
2102 CONTRACT_ID,
2103 CONTRACT_VERSION_NUM,
2104 CONTRACT_NUMBER,
2105 CONTRACT_TYPE,
2106 CONTRACT_STATUS_CODE,
2107 ORG_ID,
2108 OWNER_ID,
2109 SOURCE_LANGUAGE,
2110 CONTRACT_NAME,
2111 CONTRACT_DESC,
2112 VERSION_COMMENTS,
2113 AUTHORING_PARTY_CODE,
2114 CONTRACT_EFFECTIVE_DATE,
2115 CONTRACT_EXPIRATION_DATE,
2116 CURRENCY_CODE,
2117 AMOUNT,
2118 OVERALL_RISK_CODE,
2119 CANCELLATION_COMMENTS,
2120 CANCELLATION_DATE,
2121 TERMINATION_COMMENTS,
2122 TERMINATION_DATE,
2123 KEYWORDS,
2124 PHYSICAL_LOCATION,
2125 EXPIRE_NTF_FLAG,
2126 EXPIRE_NTF_PERIOD,
2127 NOTIFY_CONTACT_ROLE_ID,
2128 USE_ACL_FLAG,
2129 WF_ITEM_TYPE,
2130 WF_ITEM_KEY,
2131 PROGRAM_ID,
2132 PROGRAM_LOGIN_ID,
2133 PROGRAM_APPLICATION_ID,
2134 REQUEST_ID,
2135 LATEST_SIGNED_VER_NUMBER,
2136 ATTRIBUTE_CATEGORY,
2137 ATTRIBUTE1,
2138 ATTRIBUTE2,
2139 ATTRIBUTE3,
2140 ATTRIBUTE4,
2141 ATTRIBUTE5,
2142 ATTRIBUTE6,
2143 ATTRIBUTE7,
2144 ATTRIBUTE8,
2145 ATTRIBUTE9,
2146 ATTRIBUTE10,
2147 ATTRIBUTE11,
2148 ATTRIBUTE12,
2149 ATTRIBUTE13,
2150 ATTRIBUTE14,
2151 ATTRIBUTE15,
2152 OBJECT_VERSION_NUMBER,
2153 CREATED_BY,
2154 CREATION_DATE,
2155 LAST_UPDATED_BY,
2156 LAST_UPDATE_LOGIN,
2157 LAST_UPDATE_DATE,
2158 CONTRACT_LAST_UPDATED_BY,
2159 CONTRACT_LAST_UPDATE_DATE,
2160 REFERENCE_DOCUMENT_TYPE,
2161 REFERENCE_DOCUMENT_NUMBER)
2162 SELECT
2163 CONTRACT_ID,
2164 CONTRACT_VERSION_NUM,
2165 CONTRACT_NUMBER,
2166 CONTRACT_TYPE,
2167 CONTRACT_STATUS_CODE,
2168 ORG_ID,
2169 OWNER_ID,
2170 SOURCE_LANGUAGE,
2171 CONTRACT_NAME,
2172 CONTRACT_DESC,
2173 VERSION_COMMENTS,
2174 AUTHORING_PARTY_CODE,
2175 CONTRACT_EFFECTIVE_DATE,
2176 CONTRACT_EXPIRATION_DATE,
2177 CURRENCY_CODE,
2178 AMOUNT,
2179 OVERALL_RISK_CODE,
2180 CANCELLATION_COMMENTS,
2181 CANCELLATION_DATE,
2182 TERMINATION_COMMENTS,
2183 TERMINATION_DATE,
2184 KEYWORDS,
2185 PHYSICAL_LOCATION,
2186 EXPIRE_NTF_FLAG,
2187 EXPIRE_NTF_PERIOD,
2188 NOTIFY_CONTACT_ROLE_ID,
2189 USE_ACL_FLAG,
2190 WF_ITEM_TYPE,
2191 WF_ITEM_KEY,
2192 PROGRAM_ID,
2193 PROGRAM_LOGIN_ID,
2194 PROGRAM_APPLICATION_ID,
2195 REQUEST_ID,
2196 LATEST_SIGNED_VER_NUMBER,
2197 ATTRIBUTE_CATEGORY,
2198 ATTRIBUTE1,
2199 ATTRIBUTE2,
2200 ATTRIBUTE3,
2201 ATTRIBUTE4,
2202 ATTRIBUTE5,
2203 ATTRIBUTE6,
2204 ATTRIBUTE7,
2205 ATTRIBUTE8,
2206 ATTRIBUTE9,
2207 ATTRIBUTE10,
2208 ATTRIBUTE11,
2209 ATTRIBUTE12,
2210 ATTRIBUTE13,
2211 ATTRIBUTE14,
2212 ATTRIBUTE15,
2213 OBJECT_VERSION_NUMBER,
2214 CREATED_BY,
2215 CREATION_DATE,
2216 LAST_UPDATED_BY,
2217 LAST_UPDATE_LOGIN,
2218 LAST_UPDATE_DATE,
2219 CONTRACT_LAST_UPDATED_BY,
2220 CONTRACT_LAST_UPDATE_DATE,
2221 REFERENCE_DOCUMENT_TYPE,
2222 REFERENCE_DOCUMENT_NUMBER
2223 FROM OKC_REP_CONTRACT_VERS
2224 WHERE contract_id = p_contract_id
2225 AND contract_version_num = l_prev_version;
2226
2227 -- Also, we need to delete this history table record that has been copied to
2228 -- the main table
2229 DELETE FROM OKC_REP_CONTRACT_VERS
2230 WHERE contract_id = p_contract_id
2231 AND contract_version_num = l_prev_version;
2232 END IF; -- (contract_rec.contract_version_num > 1)
2233
2234 -- Standard check of p_commit
2235 IF FND_API.To_Boolean( p_commit ) THEN
2236 COMMIT WORK;
2237 END IF;
2238
2239 CLOSE contract_csr;
2240 -- Standard call to get message count and if count is 1, get message info.
2241 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2242
2243 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2244 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2245 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_contract');
2246 END IF;
2247
2248
2249 EXCEPTION
2250 WHEN FND_API.G_EXC_ERROR THEN
2251 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2252 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2253 g_module || l_api_name,
2254 'Leaving delete_contract:FND_API.G_EXC_ERROR Exception');
2255 END IF;
2256 --close cursors
2257 IF (contract_csr%ISOPEN) THEN
2258 CLOSE contract_csr ;
2259 END IF;
2260 ROLLBACK TO delete_contract_PVT;
2261 x_return_status := FND_API.G_RET_STS_ERROR;
2262 FND_MSG_PUB.Count_And_Get(
2263 p_count => x_msg_count,
2264 p_data => x_msg_data
2265 );
2266
2267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2268 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2269 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2270 g_module || l_api_name,
2271 'Leaving delete_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2272 END IF;
2273 --close cursors
2274 IF (contract_csr%ISOPEN) THEN
2275 CLOSE contract_csr ;
2276 END IF;
2277 ROLLBACK TO delete_contract_PVT;
2278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2279 FND_MSG_PUB.Count_And_Get(
2280 p_count => x_msg_count,
2281 p_data => x_msg_data
2282 );
2283
2284 WHEN OTHERS THEN
2285 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2286 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2287 g_module || l_api_name,
2288 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
2289 END IF;
2290 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2291 p_msg_name => G_UNEXPECTED_ERROR,
2292 p_token1 => G_SQLCODE_TOKEN,
2293 p_token1_value => sqlcode,
2294 p_token2 => G_SQLERRM_TOKEN,
2295 p_token2_value => sqlerrm);
2296 ROLLBACK TO delete_contract_PVT;
2297 --close cursors
2298 IF (contract_csr%ISOPEN) THEN
2299 CLOSE contract_csr ;
2300 END IF;
2301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2302 FND_MSG_PUB.Count_And_Get(
2303 p_count => x_msg_count,
2304 p_data => x_msg_data
2305 );
2306 END delete_contract;
2307
2308
2309 -- Start of comments
2310 --API name : copy_contacts
2311 --Type : Private.
2312 --Function : Copies party contacts of source contract to target contract
2313 --Pre-reqs : None.
2314 --Parameters :
2315 --IN : p_api_version IN NUMBER Required
2316 -- : p_init_msg_list IN VARCHAR2 Optional
2317 -- Default = FND_API.G_FALSE
2318 -- : p_commit IN VARCHAR2 Optional
2319 -- Default = FND_API.G_FALSE
2320 -- : p_source_contract_id IN NUMBER Required
2321 -- Id of the contract whose contacts are to be copied
2322 -- : p_target_contract_id IN NUMBER Required
2323 -- Id of the contract to which source contacts are to be copied
2324 --OUT : x_return_status OUT VARCHAR2(1)
2325 -- : x_msg_count OUT NUMBER
2326 -- : x_msg_data OUT VARCHAR2(2000)
2327 --Note :
2328 -- End of comments
2329 PROCEDURE copy_contacts(
2330 p_api_version IN NUMBER,
2331 p_init_msg_list IN VARCHAR2,
2332 p_commit IN VARCHAR2,
2333 p_source_contract_id IN NUMBER,
2334 p_target_contract_id IN NUMBER,
2335 x_msg_data OUT NOCOPY VARCHAR2,
2336 x_msg_count OUT NOCOPY NUMBER,
2337 x_return_status OUT NOCOPY VARCHAR2) IS
2338
2339 l_api_name VARCHAR2(30);
2340 l_api_version NUMBER;
2341 l_created_by OKC_REP_PARTY_CONTACTS.CREATED_BY%TYPE;
2342 l_creation_date OKC_REP_PARTY_CONTACTS.CREATION_DATE%TYPE;
2343 l_last_updated_by OKC_REP_PARTY_CONTACTS.LAST_UPDATED_BY%TYPE;
2344 l_last_update_login OKC_REP_PARTY_CONTACTS.LAST_UPDATE_LOGIN%TYPE;
2345 l_last_update_date OKC_REP_PARTY_CONTACTS.LAST_UPDATE_DATE%TYPE;
2346
2347 -- Contact cursor.
2348 CURSOR contact_csr IS
2349 SELECT *
2350 FROM OKC_REP_PARTY_CONTACTS
2351 WHERE contract_id = p_source_contract_id;
2352 BEGIN
2353
2354 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2355 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2356 'Entered OKC_REP_CONTRACT_PROCESS_PVT.copy_contacts');
2357 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2358 'Source Contract Id is: ' || to_char(p_source_contract_id));
2359 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2360 'Target Contract Id is: ' || to_char(p_target_contract_id));
2361 END IF;
2362 l_api_name := 'copy_contacts';
2363 l_api_version := 1.0;
2364 -- Standard Start of API savepoint
2365 SAVEPOINT copy_contacts_PVT;
2366 -- Standard call to check for call compatibility.
2367 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2369 END IF;
2370 -- Initialize message list if p_init_msg_list is set to TRUE.
2371 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2372 FND_MSG_PUB.initialize;
2373 END IF;
2374
2375 -- Initialize API return status to success
2376 x_return_status := FND_API.G_RET_STS_SUCCESS;
2377 -- Populate who columns
2378 l_creation_date := Sysdate;
2379 l_created_by := Fnd_Global.User_Id;
2380 l_last_update_date := l_creation_date;
2381 l_last_updated_by := l_created_by;
2382 l_last_update_login := Fnd_Global.Login_Id;
2383
2384 FOR contact_rec IN contact_csr LOOP
2385 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2387 'Party Id is: ' || contact_rec.party_id);
2388 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2389 'Party Role Code is: ' || contact_rec.party_role_code);
2390 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2391 'Contact Id is: ' || contact_rec.contact_id);
2392 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2393 'Contact Role Code is: ' || contact_rec.contact_role_id);
2394 END IF;
2395 -- INSERT THE NEW RECORDS into OKC_REP_PARTY_CONTACTS table
2396
2397 INSERT INTO OKC_REP_PARTY_CONTACTS (
2398 CONTRACT_ID,
2399 PARTY_ID,
2400 PARTY_ROLE_CODE,
2401 CONTACT_ID,
2402 CONTACT_ROLE_ID,
2403 OBJECT_VERSION_NUMBER,
2404 CREATED_BY,
2405 CREATION_DATE,
2406 LAST_UPDATED_BY,
2407 LAST_UPDATE_DATE,
2408 LAST_UPDATE_LOGIN)
2409 VALUES(
2410 p_target_contract_id,
2411 contact_rec.PARTY_ID,
2412 contact_rec.PARTY_ROLE_CODE,
2413 contact_rec.CONTACT_ID,
2414 contact_rec.CONTACT_ROLE_ID,
2415 1,
2416 l_created_by,
2417 l_creation_date,
2418 l_last_updated_by,
2419 l_last_update_date,
2420 l_last_update_login);
2421 END LOOP;
2422 -- Standard check of p_commit
2423 IF FND_API.To_Boolean( p_commit ) THEN
2424 COMMIT WORK;
2425 END IF;
2426
2427 -- Standard call to get message count and if count is 1, get message info.
2428 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2429
2430 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2431 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2432 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_contacts');
2433 END IF;
2434
2435
2436 EXCEPTION
2437 WHEN FND_API.G_EXC_ERROR THEN
2438 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2439 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2440 g_module || l_api_name,
2441 'Leaving copy_contacts:FND_API.G_EXC_ERROR Exception');
2442 END IF;
2443 ROLLBACK TO copy_contacts_PVT;
2444 x_return_status := FND_API.G_RET_STS_ERROR;
2445 FND_MSG_PUB.Count_And_Get(
2446 p_count => x_msg_count,
2447 p_data => x_msg_data
2448 );
2449
2450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2451 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2452 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2453 g_module || l_api_name,
2454 'Leaving copy_contacts:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2455 END IF;
2456 ROLLBACK TO copy_contacts_PVT;
2457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2458 FND_MSG_PUB.Count_And_Get(
2459 p_count => x_msg_count,
2460 p_data => x_msg_data
2461 );
2462
2463 WHEN OTHERS THEN
2464 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2465 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2466 g_module || l_api_name,
2467 'Leaving copy_contacts because of EXCEPTION: ' || sqlerrm);
2468 END IF;
2469 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2470 p_msg_name => G_UNEXPECTED_ERROR,
2471 p_token1 => G_SQLCODE_TOKEN,
2472 p_token1_value => sqlcode,
2473 p_token2 => G_SQLERRM_TOKEN,
2474 p_token2_value => sqlerrm);
2475 ROLLBACK TO copy_contacts_PVT;
2476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2477 FND_MSG_PUB.Count_And_Get(
2478 p_count => x_msg_count,
2479 p_data => x_msg_data
2480 );
2481 END copy_contacts;
2482
2483
2484 -- Start of comments
2485 --API name : copy_parties
2486 --Type : Private.
2487 --Function : Copies parties of source contract to target contract
2488 --Pre-reqs : None.
2489 --Parameters :
2490 --IN : p_api_version IN NUMBER Required
2491 -- : p_init_msg_list IN VARCHAR2 Optional
2492 -- Default = FND_API.G_FALSE
2493 -- : p_commit IN VARCHAR2 Optional
2494 -- Default = FND_API.G_FALSE
2495 -- : p_source_contract_id IN NUMBER Required
2496 -- Id of the contract whose parties are to be copied
2497 -- : p_target_contract_id IN NUMBER Required
2498 -- Id of the contract to which source parties are to be copied
2499 --OUT : x_return_status OUT VARCHAR2(1)
2500 -- : x_msg_count OUT NUMBER
2501 -- : x_msg_data OUT VARCHAR2(2000)
2502 --Note :
2503 -- End of comments
2504 PROCEDURE copy_parties(
2505 p_api_version IN NUMBER,
2506 p_init_msg_list IN VARCHAR2,
2507 p_commit IN VARCHAR2,
2508 p_source_contract_id IN NUMBER,
2509 p_target_contract_id IN NUMBER,
2510 x_msg_data OUT NOCOPY VARCHAR2,
2511 x_msg_count OUT NOCOPY NUMBER,
2512 x_return_status OUT NOCOPY VARCHAR2) IS
2513
2514 l_api_name VARCHAR2(30);
2515 l_api_version NUMBER;
2516 l_created_by OKC_REP_CONTRACT_PARTIES.CREATED_BY%TYPE;
2517 l_creation_date OKC_REP_CONTRACT_PARTIES.CREATION_DATE%TYPE;
2518 l_last_updated_by OKC_REP_CONTRACT_PARTIES.LAST_UPDATED_BY%TYPE;
2519 l_last_update_login OKC_REP_CONTRACT_PARTIES.LAST_UPDATE_LOGIN%TYPE;
2520 l_last_update_date OKC_REP_CONTRACT_PARTIES.LAST_UPDATE_DATE%TYPE;
2521
2522 -- Contact cursor.
2523 CURSOR party_csr IS
2524 SELECT *
2525 FROM OKC_REP_CONTRACT_PARTIES
2526 WHERE contract_id = p_source_contract_id;
2527 BEGIN
2528
2529 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2530 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2531 'Entered OKC_REP_CONTRACT_PROCESS_PVT.copy_parties');
2532 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2533 'Source Contract Id is: ' || to_char(p_source_contract_id));
2534 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2535 'Target Contract Id is: ' || to_char(p_target_contract_id));
2536 END IF;
2537 l_api_name := 'copy_parties';
2538 l_api_version := 1.0;
2539 -- Standard Start of API savepoint
2540 SAVEPOINT copy_parties_PVT;
2541 -- Standard call to check for call compatibility.
2542 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2544 END IF;
2545 -- Initialize message list if p_init_msg_list is set to TRUE.
2546 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2547 FND_MSG_PUB.initialize;
2548 END IF;
2549
2550 -- Initialize API return status to success
2551 x_return_status := FND_API.G_RET_STS_SUCCESS;
2552 -- Populate who columns
2553 l_creation_date := Sysdate;
2554 l_created_by := Fnd_Global.User_Id;
2555 l_last_update_date := l_creation_date;
2556 l_last_updated_by := l_created_by;
2557 l_last_update_login := Fnd_Global.Login_Id;
2558
2559 FOR party_rec IN party_csr LOOP
2560 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2561 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2562 'Party Id is: ' || party_rec.party_id);
2563 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2564 'Party Role Code is: ' || party_rec.party_role_code);
2565 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2566 'Party Location ID is: ' || party_rec.party_location_id);
2567 END IF;
2568 -- INSERT THE NEW RECORDS into OKC_REP_CONTRACT_PARTIES table
2569 INSERT INTO OKC_REP_CONTRACT_PARTIES (
2570 CONTRACT_ID,
2571 PARTY_ID,
2572 PARTY_ROLE_CODE,
2573 PARTY_LOCATION_ID,
2574 OBJECT_VERSION_NUMBER,
2575 CREATED_BY,
2576 CREATION_DATE,
2577 LAST_UPDATED_BY,
2578 LAST_UPDATE_DATE,
2579 LAST_UPDATE_LOGIN)
2580 VALUES(
2581 p_target_contract_id,
2582 party_rec.PARTY_ID,
2583 party_rec.PARTY_ROLE_CODE,
2584 party_rec.PARTY_LOCATION_ID,
2585 1,
2586 l_created_by,
2587 l_creation_date,
2588 l_last_updated_by,
2589 l_last_update_date,
2590 l_last_update_login);
2591 END LOOP;
2592 -- Standard check of p_commit
2593 IF FND_API.To_Boolean( p_commit ) THEN
2594 COMMIT WORK;
2595 END IF;
2596
2597 -- Standard call to get message count and if count is 1, get message info.
2598 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2599
2600 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2601 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2602 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_parties');
2603 END IF;
2604
2605
2606 EXCEPTION
2607 WHEN FND_API.G_EXC_ERROR THEN
2608 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2609 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2610 g_module || l_api_name,
2611 'Leaving copy_parties:FND_API.G_EXC_ERROR Exception');
2612 END IF;
2613 ROLLBACK TO copy_parties_PVT;
2614 x_return_status := FND_API.G_RET_STS_ERROR;
2615 FND_MSG_PUB.Count_And_Get(
2616 p_count => x_msg_count,
2617 p_data => x_msg_data
2618 );
2619
2620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2621 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2622 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2623 g_module || l_api_name,
2624 'Leaving copy_parties:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2625 END IF;
2626 ROLLBACK TO copy_parties_PVT;
2627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2628 FND_MSG_PUB.Count_And_Get(
2629 p_count => x_msg_count,
2630 p_data => x_msg_data
2631 );
2632
2633 WHEN OTHERS THEN
2634 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2635 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2636 g_module || l_api_name,
2637 'Leaving copy_parties because of EXCEPTION: ' || sqlerrm);
2638 END IF;
2639 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2640 p_msg_name => G_UNEXPECTED_ERROR,
2641 p_token1 => G_SQLCODE_TOKEN,
2642 p_token1_value => sqlcode,
2643 p_token2 => G_SQLERRM_TOKEN,
2644 p_token2_value => sqlerrm);
2645 ROLLBACK TO copy_parties_PVT;
2646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2647 FND_MSG_PUB.Count_And_Get(
2648 p_count => x_msg_count,
2649 p_data => x_msg_data
2650 );
2651 END copy_parties;
2652
2653
2654 -- Start of comments
2655 --API name : copy_risks
2656 --Type : Private.
2657 --Function : Copies risks of source contract to target contract
2658 --Pre-reqs : None.
2659 --Parameters :
2660 --IN : p_api_version IN NUMBER Required
2661 -- : p_init_msg_list IN VARCHAR2 Optional
2662 -- Default = FND_API.G_FALSE
2663 -- : p_commit IN VARCHAR2 Optional
2664 -- Default = FND_API.G_FALSE
2665 -- : p_source_contract_id IN NUMBER Required
2666 -- Id of the contract whose risks are to be copied
2667 -- : p_target_contract_id IN NUMBER Required
2668 -- Id of the contract to which source contract risks are to be copied
2669 --OUT : x_return_status OUT VARCHAR2(1)
2670 -- : x_msg_count OUT NUMBER
2671 -- : x_msg_data OUT VARCHAR2(2000)
2672 --Note :
2673 -- End of comments
2674 PROCEDURE copy_risks(
2675 p_api_version IN NUMBER,
2676 p_init_msg_list IN VARCHAR2,
2677 p_commit IN VARCHAR2,
2678 p_source_contract_id IN NUMBER,
2679 p_target_contract_id IN NUMBER,
2680 x_msg_data OUT NOCOPY VARCHAR2,
2681 x_msg_count OUT NOCOPY NUMBER,
2682 x_return_status OUT NOCOPY VARCHAR2) IS
2683
2684 l_api_name VARCHAR2(30);
2685 l_api_version NUMBER;
2686 l_created_by OKC_CONTRACT_RISKS.CREATED_BY%TYPE;
2687 l_creation_date OKC_CONTRACT_RISKS.CREATION_DATE%TYPE;
2688 l_last_updated_by OKC_CONTRACT_RISKS.LAST_UPDATED_BY%TYPE;
2689 l_last_update_login OKC_CONTRACT_RISKS.LAST_UPDATE_LOGIN%TYPE;
2690 l_last_update_date OKC_CONTRACT_RISKS.LAST_UPDATE_DATE%TYPE;
2691 -- l_contract_risk_id OKC_CONTRACT_RISKS.CONTRACT_RISK_ID%TYPE;
2692
2693
2694 -- Contact cursor.
2695 CURSOR risk_csr(doc_type VARCHAR2, doc_id NUMBER) IS
2696 SELECT *
2697 FROM OKC_CONTRACT_RISKS
2698 WHERE business_document_type = doc_type
2699 AND business_document_id = doc_id;
2700
2701 -- Contract cursor to get contract_type and version
2702 CURSOR contract_csr IS
2703 SELECT contract_type, contract_version_num
2704 FROM OKC_REP_CONTRACTS_ALL
2705 WHERE contract_id = p_source_contract_id;
2706
2707 contract_rec contract_csr%ROWTYPE;
2708 BEGIN
2709
2710 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2711 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2712 'Entered OKC_REP_CONTRACT_PROCESS_PVT.copy_risks');
2713 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2714 'Source Contract Id is: ' || to_char(p_source_contract_id));
2715 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2716 'Target Contract Id is: ' || to_char(p_target_contract_id));
2717 END IF;
2718 l_api_name := 'copy_risks';
2719 l_api_version := 1.0;
2720 -- Standard Start of API savepoint
2721 SAVEPOINT copy_risks_PVT;
2722 -- Standard call to check for call compatibility.
2723 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2725 END IF;
2726 -- Initialize message list if p_init_msg_list is set to TRUE.
2727 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2728 FND_MSG_PUB.initialize;
2729 END IF;
2730
2731 -- Initialize API return status to success
2732 x_return_status := FND_API.G_RET_STS_SUCCESS;
2733
2734 -- Populate who columns
2735 l_creation_date := Sysdate;
2736 l_created_by := Fnd_Global.User_Id;
2737 l_last_update_date := l_creation_date;
2738 l_last_updated_by := l_created_by;
2739 l_last_update_login := Fnd_Global.Login_Id;
2740
2741 -- Get Contract type and version number columns
2742 -- Get effective dates and version of the contract.
2743 OPEN contract_csr;
2744 FETCH contract_csr INTO contract_rec;
2745 IF(contract_csr%NOTFOUND) THEN
2746 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2747 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
2748 G_MODULE||l_api_name,
2749 'Invalid Contract Id: '|| p_source_contract_id);
2750 END IF;
2751 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2752 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
2753 p_token1 => G_CONTRACT_ID_TOKEN,
2754 p_token1_value => to_char(p_source_contract_id));
2755 RAISE FND_API.G_EXC_ERROR;
2756 -- RAISE NO_DATA_FOUND;
2757 END IF;
2758
2759
2760 FOR risk_rec IN risk_csr(contract_rec.contract_type, p_source_contract_id) LOOP
2761 -- Get the id column.
2762 -- SELECT OKC_CONTRACT_RISKS_S.nextval INTO l_contract_risk_id FROM dual;
2763
2764 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2765 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2766 'Contract Type is: ' || contract_rec.CONTRACT_TYPE);
2767 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2768 'Contract Id is: ' || risk_rec.BUSINESS_DOCUMENT_ID);
2769 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2770 'Contract Version is: ' || contract_rec.CONTRACT_VERSION_NUM);
2771 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2772 'Risk Event ID is: ' || risk_rec.RISK_EVENT_ID);
2773 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2774 'Probability code is: ' || risk_rec.PROBABILITY_CODE);
2775 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2776 'Impact Code is: ' || risk_rec.IMPACT_CODE);
2777 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2778 'Risk Occurred Flag is: ' || risk_rec.RISK_OCCURRED_FLAG);
2779 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2780 'Occurence Date is: ' || to_char(risk_rec.OCCURRENCE_DATE));
2781 END IF;
2782 -- INSERT THE NEW RECORDS into OKC_CONTRACT_RISKS table
2783 INSERT INTO OKC_CONTRACT_RISKS (
2784 -- CONTRACT_RISK_ID,
2785 BUSINESS_DOCUMENT_TYPE,
2786 BUSINESS_DOCUMENT_ID,
2787 BUSINESS_DOCUMENT_VERSION,
2788 RISK_EVENT_ID,
2789 PROBABILITY_CODE,
2790 IMPACT_CODE,
2791 COMMENTS,
2792 RISK_OCCURRED_FLAG,
2793 OCCURRENCE_DATE,
2794 OBJECT_VERSION_NUMBER,
2795 CREATED_BY,
2796 CREATION_DATE,
2797 LAST_UPDATED_BY,
2798 LAST_UPDATE_DATE,
2799 LAST_UPDATE_LOGIN)
2800 VALUES(
2801 -- l_contract_risk_id,
2802 --risk_rec.CONTRACT_ID,
2803 contract_rec.contract_type,
2804 p_target_contract_id,
2805 risk_rec.BUSINESS_DOCUMENT_VERSION,
2806 risk_rec.RISK_EVENT_ID,
2807 risk_rec.PROBABILITY_CODE,
2808 risk_rec.IMPACT_CODE,
2809 risk_rec.COMMENTS,
2810 risk_rec.RISK_OCCURRED_FLAG,
2811 risk_rec.OCCURRENCE_DATE,
2812 1,
2813 l_created_by,
2814 l_creation_date,
2815 l_last_updated_by,
2816 l_last_update_date,
2817 l_last_update_login);
2818 END LOOP;
2819 -- Standard check of p_commit
2820 IF FND_API.To_Boolean( p_commit ) THEN
2821 COMMIT WORK;
2822 END IF;
2823
2824 -- Standard call to get message count and if count is 1, get message info.
2825 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2826
2827 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2828 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2829 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_risks');
2830 END IF;
2831
2832
2833 EXCEPTION
2834 WHEN FND_API.G_EXC_ERROR THEN
2835 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2836 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2837 g_module || l_api_name,
2838 'Leaving copy_risks:FND_API.G_EXC_ERROR Exception');
2839 END IF;
2840 --close cursors
2841 IF (contract_csr%ISOPEN) THEN
2842 CLOSE contract_csr ;
2843 END IF;
2844 ROLLBACK TO copy_risks_PVT;
2845 x_return_status := FND_API.G_RET_STS_ERROR;
2846 FND_MSG_PUB.Count_And_Get(
2847 p_count => x_msg_count,
2848 p_data => x_msg_data
2849 );
2850
2851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2852 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2853 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2854 g_module || l_api_name,
2855 'Leaving copy_risks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2856 END IF;
2857 --close cursors
2858 IF (contract_csr%ISOPEN) THEN
2859 CLOSE contract_csr ;
2860 END IF;
2861 ROLLBACK TO copy_risks_PVT;
2862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2863 FND_MSG_PUB.Count_And_Get(
2864 p_count => x_msg_count,
2865 p_data => x_msg_data
2866 );
2867
2868 WHEN OTHERS THEN
2869 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2870 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2871 g_module || l_api_name,
2872 'Leaving copy_risks because of EXCEPTION: ' || sqlerrm);
2873 END IF;
2874 --close cursors
2875 IF (contract_csr%ISOPEN) THEN
2876 CLOSE contract_csr ;
2877 END IF;
2878 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2879 p_msg_name => G_UNEXPECTED_ERROR,
2880 p_token1 => G_SQLCODE_TOKEN,
2881 p_token1_value => sqlcode,
2882 p_token2 => G_SQLERRM_TOKEN,
2883 p_token2_value => sqlerrm);
2884 ROLLBACK TO copy_risks_PVT;
2885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2886 FND_MSG_PUB.Count_And_Get(
2887 p_count => x_msg_count,
2888 p_data => x_msg_data
2889 );
2890 END copy_risks;
2891
2892
2893 -- Start of comments
2894 --API name : copy_ACL
2895 --Type : Private.
2896 --Function : Copies ACL records from source contract to target contract
2897 --Pre-reqs : None.
2898 --Parameters :
2899 --IN : p_api_version IN NUMBER Required
2900 -- : p_init_msg_list IN VARCHAR2 Optional
2901 -- Default = FND_API.G_FALSE
2902 -- : p_commit IN VARCHAR2 Optional
2903 -- Default = FND_API.G_FALSE
2904 -- : p_source_contract_id IN NUMBER Required
2905 -- Id of the contract whose ACLs are to be copied
2906 -- : p_target_contract_id IN NUMBER Required
2907 -- Id of the contract to which source contract ACL are to be copied
2908 --OUT : x_return_status OUT VARCHAR2(1)
2909 -- : x_msg_count OUT NUMBER
2910 -- : x_msg_data OUT VARCHAR2(2000)
2911 --Note :
2912 -- End of comments
2913 PROCEDURE copy_ACL(
2914 p_api_version IN NUMBER,
2915 p_init_msg_list IN VARCHAR2,
2916 p_commit IN VARCHAR2,
2917 p_source_contract_id IN NUMBER,
2918 p_target_contract_id IN NUMBER,
2919 x_msg_data OUT NOCOPY VARCHAR2,
2920 x_msg_count OUT NOCOPY NUMBER,
2921 x_return_status OUT NOCOPY VARCHAR2)IS
2922
2923 l_api_name VARCHAR2(30);
2924 l_api_version NUMBER;
2925 x_success VARCHAR2(1);
2926 x_errorcode NUMBER;
2927 x_grant_guid RAW(16);
2928
2929
2930 CURSOR acl_csr IS
2931 SELECT
2932 fgrant.grantee_type grantee_type,
2933 fgrant.grantee_key grantee_key,
2934 fgrant.instance_type instance_type,
2935 fgrant.instance_set_id instance_set_id,
2936 fmenu.menu_name menu_name,
2937 fgrant.program_name program_name,
2938 fgrant.program_tag program_tag,
2939 fgrant.parameter1 parameter1,
2940 fgrant.parameter2 parameter2,
2941 fgrant.parameter3 parameter3
2942 FROM FND_GRANTS fgrant, FND_OBJECTS fobj, FND_MENUS fmenu
2943 WHERE fgrant.menu_id = fmenu.menu_id
2944 AND fgrant.object_id = fobj.object_id
2945 AND fobj.obj_name = 'OKC_REP_CONTRACT'
2946 AND fgrant.instance_pk1_value = to_char(p_source_contract_id);
2947
2948 BEGIN
2949
2950 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2951 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2952 'Entered OKC_REP_CONTRACT_PROCESS_PVT.copy_ACL');
2953 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2954 'Source Contract Id is: ' || p_source_contract_id);
2955 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2956 'Target Contract Id is: ' || p_target_contract_id);
2957 END IF;
2958 l_api_name := 'copy_ACL';
2959 l_api_version := 1.0;
2960 -- Standard Start of API savepoint
2961 SAVEPOINT copy_ACL_PVT;
2962 -- Standard call to check for call compatibility.
2963 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2965 END IF;
2966 -- Initialize message list if p_init_msg_list is set to TRUE.
2967 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2968 FND_MSG_PUB.initialize;
2969 END IF;
2970
2971 -- Initialize API return status to success
2972 x_return_status := FND_API.G_RET_STS_SUCCESS;
2973
2974 FOR acl_rec IN acl_csr LOOP
2975 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2976 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2977 'grantee_type is: ' || acl_rec.grantee_type);
2978 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2979 'grantee_key is: ' || acl_rec.grantee_key);
2980 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2981 'instance_type is: ' || acl_rec.instance_type);
2982 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2983 'instance_set_id is: ' || acl_rec.instance_set_id);
2984 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2985 'menu_name is: ' || acl_rec.menu_name);
2986 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2987 'program_name is: ' || acl_rec.program_name);
2988 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2989 'program_tag is: ' || acl_rec.program_tag);
2990 END IF;
2991 -- call FND_GRANT's delete api
2992 FND_GRANTS_PKG.grant_function(
2993 p_api_version => 1.0,
2994 p_menu_name => acl_rec.menu_name, -- Menu to be deleted.
2995 p_object_name => G_REP_CONTRACT,
2996 p_instance_type => acl_rec.instance_type, -- INSTANCE or SET
2997 p_instance_set_id => acl_rec.instance_set_id, -- Instance set id.
2998 p_instance_pk1_value => to_char(p_target_contract_id), -- Object PK Value
2999 p_grantee_type => acl_rec.grantee_type, -- USER or GROUP
3000 p_grantee_key => acl_rec.grantee_key, -- user_id or group_id
3001 p_start_date => sysdate,
3002 p_end_date => null,
3003 p_program_name => acl_rec.program_name, -- name of the program that handles grant.
3004 p_program_tag => acl_rec.program_tag, -- tag used by the program that handles grant.
3005 p_parameter1 => acl_rec.parameter1, -- resource type
3006 p_parameter2 => acl_rec.parameter2, -- resource id
3007 p_parameter3 => acl_rec.parameter3, -- access type
3008 x_grant_guid => x_grant_guid,
3009 x_success => x_success, -- return param. 'T' or 'F'
3010 x_errorcode => x_errorcode );
3011 -----------------------------------------------------
3012 IF (x_success = 'F' AND x_errorcode < 0 ) THEN
3013 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3014 ELSIF (x_success = 'F' AND x_errorcode > 0) THEN
3015 RAISE OKC_API.G_EXCEPTION_ERROR;
3016 END IF;
3017 --------------------------------------------------------
3018 END LOOP;
3019 -- Standard check of p_commit
3020 IF FND_API.To_Boolean( p_commit ) THEN
3021 COMMIT WORK;
3022 END IF;
3023
3024 -- Standard call to get message count and if count is 1, get message info.
3025 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
3026
3027 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3028 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3029 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_ACL');
3030 END IF;
3031
3032
3033 EXCEPTION
3034 WHEN FND_API.G_EXC_ERROR THEN
3035 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3036 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3037 g_module || l_api_name,
3038 'Leaving copy_ACL:FND_API.G_EXC_ERROR Exception');
3039 END IF;
3040 ROLLBACK TO copy_ACL_PVT;
3041 x_return_status := FND_API.G_RET_STS_ERROR;
3042 FND_MSG_PUB.Count_And_Get(
3043 p_count => x_msg_count,
3044 p_data => x_msg_data
3045 );
3046
3047 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3048 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3049 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3050 g_module || l_api_name,
3051 'Leaving copy_ACL:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
3052 END IF;
3053 ROLLBACK TO copy_ACL_PVT;
3054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3055 FND_MSG_PUB.Count_And_Get(
3056 p_count => x_msg_count,
3057 p_data => x_msg_data
3058 );
3059
3060 WHEN OTHERS THEN
3061 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3062 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3063 g_module || l_api_name,
3064 'Leaving copy_ACL because of EXCEPTION: ' || sqlerrm);
3065 END IF;
3066 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3067 p_msg_name => G_UNEXPECTED_ERROR,
3068 p_token1 => G_SQLCODE_TOKEN,
3069 p_token1_value => sqlcode,
3070 p_token2 => G_SQLERRM_TOKEN,
3071 p_token2_value => sqlerrm);
3072 ROLLBACK TO copy_ACL_PVT;
3073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3074 FND_MSG_PUB.Count_And_Get(
3075 p_count => x_msg_count,
3076 p_data => x_msg_data
3077 );
3078 END copy_ACL;
3079
3080
3081
3082 -- Start of comments
3083 --API name : copy_contract_details
3084 --Type : Private.
3085 --Function : Copies contract details for duplication
3086 --Pre-reqs : None.
3087 --Parameters :
3088 --IN : p_api_version IN NUMBER Required
3089 -- : p_init_msg_list IN VARCHAR2 Optional
3090 -- Default = FND_API.G_FALSE
3091 -- : p_commit IN VARCHAR2 Optional
3092 -- Default = FND_API.G_FALSE
3093 -- : p_source_contract_id IN NUMBER Required
3094 -- Id of the contract whose details are to be copied
3095 -- : p_target_contract_id IN NUMBER Required
3096 -- Id of the contract to which source contract details are to be copied
3097 -- : p_target_contract_number IN VARCHAR2 Required
3098 -- Number of the contract to which source contract details are to be copied
3099 --OUT : x_return_status OUT VARCHAR2(1)
3100 -- : x_msg_count OUT NUMBER
3101 -- : x_msg_data OUT VARCHAR2(2000)
3102 --Note :
3103 -- End of comments
3104 PROCEDURE copy_contract_details(
3105 p_api_version IN NUMBER,
3106 p_init_msg_list IN VARCHAR2,
3107 p_commit IN VARCHAR2,
3108 p_source_contract_id IN NUMBER,
3109 p_target_contract_id IN NUMBER,
3110 p_target_contract_number IN VARCHAR2,
3111 x_msg_data OUT NOCOPY VARCHAR2,
3112 x_msg_count OUT NOCOPY NUMBER,
3113 x_return_status OUT NOCOPY VARCHAR2) IS
3114
3115 l_api_name VARCHAR2(30);
3116 l_api_version NUMBER;
3117 l_internal_party_id OKC_REP_CONTRACT_PARTIES.PARTY_ID%TYPE;
3118
3119 -- Repository Enhancement 12.1 (For Duplicate Action)
3120 x_target_contract_id OKC_REP_CONTRACTS_ALL.CONTRACT_ID%TYPE;
3121 G_TEMPLATE_MISS_REC OKC_TERMS_TEMPLATES_PVT.template_rec_type;
3122 -- Repository Enhancement 12.1 ends
3123
3124
3125 CURSOR source_contract_csr IS
3126 SELECT contract_type, owner_id
3127 FROM OKC_REP_CONTRACTS_ALL
3128 WHERE contract_id = p_source_contract_id;
3129
3130 source_contract_rec source_contract_csr%ROWTYPE;
3131
3132 -- Repository Enhancement 12.1(For Duplicate Action)
3133 x_target_contract_type source_contract_rec.contract_type%TYPE;
3134 -- Repository Enhancement 12.1 ends (For Duplicate Action)
3135
3136
3137 CURSOR party_csr IS
3138 SELECT party_id
3139 FROM OKC_REP_CONTRACT_PARTIES
3140 WHERE contract_id = p_source_contract_id
3141 AND party_role_code = 'INTERNAL_ORG';
3142
3143
3144 BEGIN
3145
3146 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3148 'Entered OKC_REP_CONTRACT_PROCESS_PVT.copy_contract_details');
3149 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3150 'Source Contract Id is: ' || p_source_contract_id);
3151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3152 'Target Contract Id is: ' || p_target_contract_id);
3153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3154 'Target Contract Number is: ' || p_target_contract_number);
3155 END IF;
3156 l_api_name := 'copy_contract_details';
3157 l_api_version := 1.0;
3158 -- Standard Start of API savepoint
3159 SAVEPOINT copy_contract_details_PVT;
3160 -- Standard call to check for call compatibility.
3161 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3163 END IF;
3164 -- Initialize message list if p_init_msg_list is set to TRUE.
3165 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3166 FND_MSG_PUB.initialize;
3167 END IF;
3168
3169 -- Initialize API return status to success
3170 x_return_status := FND_API.G_RET_STS_SUCCESS;
3171
3172 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3173 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3174 'Calling OKC_REP_CONTRACT_PROCESS_PVT.copy_parties()');
3175 END IF;
3176 copy_parties(
3177 p_api_version => 1.0,
3178 p_init_msg_list => FND_API.G_FALSE,
3179 p_commit => FND_API.G_FALSE,
3180 p_source_contract_id => p_source_contract_id,
3181 p_target_contract_id => p_target_contract_id,
3182 x_msg_data => x_msg_data,
3183 x_msg_count => x_msg_count,
3184 x_return_status => x_return_status);
3185 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3186 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3187 'OKC_REP_CONTRACT_PROCESS_PVT.copy_parties return status is: '
3188 || x_return_status);
3189 END IF;
3190 ----------------------------------------------------
3191 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3192 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3193 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3194 RAISE OKC_API.G_EXCEPTION_ERROR;
3195 END IF;
3196 --------------------------------------------------------
3197
3198
3199 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3200 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3201 'Calling OKC_REP_CONTRACT_PROCESS_PVT.copy_contacts()');
3202 END IF;
3203 copy_contacts(
3204 p_api_version => 1.0,
3205 p_init_msg_list => FND_API.G_FALSE,
3206 p_commit => FND_API.G_FALSE,
3207 p_source_contract_id => p_source_contract_id,
3208 p_target_contract_id => p_target_contract_id,
3209 x_msg_data => x_msg_data,
3210 x_msg_count => x_msg_count,
3211 x_return_status => x_return_status);
3212 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3213 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3214 'OKC_REP_CONTRACT_PROCESS_PVT.copy_contacts return status is: '
3215 || x_return_status);
3216 END IF;
3217 -----------------------------------------------------
3218 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3219 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3220 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3221 RAISE OKC_API.G_EXCEPTION_ERROR;
3222 END IF;
3223 -----------------------------------------------------
3224
3225 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3226 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3227 'Calling OKC_REP_CONTRACT_PROCESS_PVT.copy_risks()');
3228 END IF;
3229 copy_risks(
3230 p_api_version => 1.0,
3231 p_init_msg_list => FND_API.G_FALSE,
3232 p_commit => FND_API.G_FALSE,
3233 p_source_contract_id => p_source_contract_id,
3234 p_target_contract_id => p_target_contract_id,
3235 x_msg_data => x_msg_data,
3236 x_msg_count => x_msg_count,
3237 x_return_status => x_return_status);
3238 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3239 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3240 'OKC_REP_CONTRACT_PROCESS_PVT.copy_risks return status is: '
3241 || x_return_status);
3242 END IF;
3243 -----------------------------------------------------
3244 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3245 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3246 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3247 RAISE OKC_API.G_EXCEPTION_ERROR;
3248 END IF;
3249 --------------------------------------------------------
3250
3251 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3252 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3253 'Calling OKC_REP_CONTRACT_PROCESS_PVT.copy_ACL()');
3254 END IF;
3255 copy_ACL(
3256 p_api_version => 1.0,
3257 p_init_msg_list => FND_API.G_FALSE,
3258 p_commit => FND_API.G_FALSE,
3259 p_source_contract_id => p_source_contract_id,
3260 p_target_contract_id => p_target_contract_id,
3261 x_msg_data => x_msg_data,
3262 x_msg_count => x_msg_count,
3263 x_return_status => x_return_status);
3264 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3265 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3266 'OKC_REP_CONTRACT_PROCESS_PVT.copy_ACL return status is: '
3267 || x_return_status);
3268 END IF;
3269 -----------------------------------------------------
3270 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3272 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3273 RAISE OKC_API.G_EXCEPTION_ERROR;
3274 END IF;
3275 -----------------------------------------------------
3276 -- Get contract_type of source contract, required for deliverables and documents APIs
3277 OPEN source_contract_csr;
3278 FETCH source_contract_csr INTO source_contract_rec;
3279 IF(source_contract_csr%NOTFOUND) THEN
3280 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3281 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
3282 G_MODULE||l_api_name,
3283 'Invalid Contract Id: '|| p_source_contract_id);
3284 END IF;
3285 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3286 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
3287 p_token1 => G_CONTRACT_ID_TOKEN,
3288 p_token1_value => to_char(p_source_contract_id));
3289 RAISE FND_API.G_EXC_ERROR;
3290 END IF;
3291
3292 -- Get internal party_id. Needed for deliverables api
3293 OPEN party_csr;
3294 FETCH party_csr INTO l_internal_party_id;
3295 IF(party_csr%NOTFOUND) THEN
3296 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3297 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
3298 G_MODULE||l_api_name,
3299 'No internal party for the contract');
3300 END IF;
3301 RAISE FND_API.G_EXC_ERROR;
3302 END IF;
3303
3304 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3305 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3306 'Calling OKC_CONTRACT_DOCS_GRP.Copy_Attachments');
3307 END IF;
3308 OKC_CONTRACT_DOCS_GRP.Copy_Attachments(
3309 p_api_version => 1,
3310 p_from_bus_doc_type => source_contract_rec.contract_type,
3311 p_from_bus_doc_id => p_source_contract_id,
3312 p_to_bus_doc_type => source_contract_rec.contract_type,
3313 p_to_bus_doc_id => p_target_contract_id,
3314 p_copy_by_ref => 'N',
3315 x_return_status => x_return_status,
3316 x_msg_count => x_msg_count,
3317 x_msg_data => x_msg_data
3318 );
3319 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3320 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3321 'OKC_CONTRACT_DOCS_GRP.Copy_Attachments return status is : '
3322 || x_return_status);
3323 END IF;
3324 -----------------------------------------------------
3325 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3327 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3328 RAISE OKC_API.G_EXCEPTION_ERROR;
3329 END IF;
3330 --------------------------------------------------------
3331
3332 -- Repository Enhancement 12.1(For Duplicate Action)
3333
3334
3335 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3336 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3337 'Calling OKC_TERMS_COPY_PVT.copy_tc');
3338 END IF;
3339
3340 x_target_contract_type := source_contract_rec.contract_type;
3341 x_target_contract_id := p_target_contract_id;
3342 OKC_TERMS_COPY_PVT.copy_tc(
3343 p_api_version => 1,
3344 p_init_msg_list => FND_API.G_FALSE,
3345 p_commit => FND_API.G_FALSE,
3346 p_source_doc_type => source_contract_rec.contract_type,
3347 p_source_doc_id => p_source_contract_id,
3348 p_target_doc_type =>x_target_contract_type ,
3349 p_target_doc_id => x_target_contract_id,
3350 p_document_number => p_target_contract_number,
3351 p_keep_version => 'N',
3352 p_article_effective_date => SYSDATE,
3353 p_target_template_rec => G_TEMPLATE_MISS_REC ,
3354 x_return_status => x_return_status,
3355 x_msg_data => x_msg_data,
3356 x_msg_count => x_msg_count);
3357 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3358 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished OKC_TERMS_COPY_PVT.copy_tc, return status : '||x_return_status);
3359 END IF;
3360
3361 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3362 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3363 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3364 RAISE OKC_API.G_EXCEPTION_ERROR;
3365 END IF;
3366 -- Repository Enhancement 12.1 ends(For Duplicate Action)
3367
3368
3369 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3370 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3371 'Calling OKC_DELIVERABLE_PROCESS_PVT.copy_deliverables');
3372 END IF;
3373 OKC_DELIVERABLE_PROCESS_PVT.copy_deliverables (
3374 p_api_version => 1.0,
3375 p_init_msg_list => FND_API.G_FALSE,
3376 p_source_doc_id => p_source_contract_id,
3377 p_source_doc_type => source_contract_rec.contract_type,
3378 p_target_doc_id => p_target_contract_id,
3379 p_target_doc_type => source_contract_rec.contract_type,
3380 p_target_doc_number => p_target_contract_number,
3381 p_internal_party_id => l_internal_party_id,
3382 p_internal_contact_id => source_contract_rec.owner_id,
3383 p_carry_forward_ext_party_yn => 'Y',
3384 p_carry_forward_int_contact_yn => 'Y',
3385 p_reset_fixed_date_yn => 'Y',
3386 x_return_status => x_return_status,
3387 x_msg_count => x_msg_count,
3388 x_msg_data => x_msg_data
3389 );
3390 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3391 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3392 'OKC_DELIVERABLE_PROCESS_PVT.copy_deliverables return status is : '
3393 || x_return_status);
3394 END IF;
3395 -----------------------------------------------------
3396 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3397 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3398 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3399 RAISE OKC_API.G_EXCEPTION_ERROR;
3400 END IF;
3401 --------------------------------------------------------
3402
3403 -- close open cursors
3404 CLOSE source_contract_csr;
3405 CLOSE party_csr;
3406
3407 -- Standard check of p_commit
3408 IF FND_API.To_Boolean( p_commit ) THEN
3409 COMMIT WORK;
3410 END IF;
3411
3412 -- Standard call to get message count and if count is 1, get message info.
3413 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
3414
3415 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3416 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3417 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_contract_details');
3418 END IF;
3419
3420
3421 EXCEPTION
3422 WHEN FND_API.G_EXC_ERROR THEN
3423 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3424 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3425 g_module || l_api_name,
3426 'Leaving copy_contract_details:FND_API.G_EXC_ERROR Exception');
3427 END IF;
3428 --close cursors
3429 IF (source_contract_csr%ISOPEN) THEN
3430 CLOSE source_contract_csr ;
3431 END IF;
3432 IF (party_csr%ISOPEN) THEN
3433 CLOSE party_csr ;
3434 END IF;
3435 ROLLBACK TO copy_contract_details_PVT;
3436 x_return_status := FND_API.G_RET_STS_ERROR;
3437 FND_MSG_PUB.Count_And_Get(
3438 p_count => x_msg_count,
3439 p_data => x_msg_data
3440 );
3441
3442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3443 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3444 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3445 g_module || l_api_name,
3446 'Leaving copy_contract_details:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
3447 END IF;
3448 --close cursors
3449 IF (source_contract_csr%ISOPEN) THEN
3450 CLOSE source_contract_csr ;
3451 END IF;
3452 IF (party_csr%ISOPEN) THEN
3453 CLOSE party_csr ;
3454 END IF;
3455 ROLLBACK TO copy_contract_details_PVT;
3456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3457 FND_MSG_PUB.Count_And_Get(
3458 p_count => x_msg_count,
3459 p_data => x_msg_data
3460 );
3461
3462 WHEN OTHERS THEN
3463 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3464 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3465 g_module || l_api_name,
3466 'Leaving copy_contract_details because of EXCEPTION: ' || sqlerrm);
3467 END IF;
3468 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3469 p_msg_name => G_UNEXPECTED_ERROR,
3470 p_token1 => G_SQLCODE_TOKEN,
3471 p_token1_value => sqlcode,
3472 p_token2 => G_SQLERRM_TOKEN,
3473 p_token2_value => sqlerrm);
3474 ROLLBACK TO copy_contract_details_PVT;
3475 --close cursors
3476 IF (source_contract_csr%ISOPEN) THEN
3477 CLOSE source_contract_csr ;
3478 END IF;
3479 IF (party_csr%ISOPEN) THEN
3480 CLOSE party_csr ;
3481 END IF;
3482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3483 FND_MSG_PUB.Count_And_Get(
3484 p_count => x_msg_count,
3485 p_data => x_msg_data
3486 );
3487 END copy_contract_details;
3488
3489
3490 -- Start of comments
3491 --API name : version_contract_details
3492 --Type : Private.
3493 --Function : Copies deliverables and documents for versioning
3494 --Pre-reqs : None.
3495 --Parameters :
3496 --IN : p_api_version IN NUMBER Required
3497 -- : p_init_msg_list IN VARCHAR2 Optional
3498 -- Default = FND_API.G_FALSE
3499 -- : p_commit IN VARCHAR2 Optional
3500 -- Default = FND_API.G_FALSE
3501 -- : p_contract_id IN NUMBER Required
3502 -- Id of the contract whose details are to be versioned
3503 --OUT : x_return_status OUT VARCHAR2(1)
3504 -- : x_msg_count OUT NUMBER
3505 -- : x_msg_data OUT VARCHAR2(2000)
3506 --Note :
3507 -- End of comments
3508 PROCEDURE version_contract_details(
3509 p_api_version IN NUMBER,
3510 p_init_msg_list IN VARCHAR2,
3511 p_commit IN VARCHAR2,
3512 p_contract_id IN NUMBER,
3513 x_msg_data OUT NOCOPY VARCHAR2,
3514 x_msg_count OUT NOCOPY NUMBER,
3515 x_return_status OUT NOCOPY VARCHAR2) IS
3516 l_api_name VARCHAR2(30);
3517 l_api_version NUMBER;
3518 l_contract_type OKC_REP_CONTRACTS_ALL.CONTRACT_TYPE%TYPE;
3519 l_contract_version OKC_REP_CONTRACTS_ALL.CONTRACT_VERSION_NUM%TYPE;
3520 -- Repository Enhancement 12.1 (For Create New Version Action)
3521 l_conterms_exist_flag VARCHAR2(1);
3522
3523 CURSOR contract_csr IS
3524 SELECT contract_type, contract_version_num, contract_status_code
3525 FROM OKC_REP_CONTRACTS_ALL
3526 WHERE contract_id = p_contract_id;
3527
3528 contract_rec contract_csr%ROWTYPE;
3529 -- Repository Enhancement 12.1 (For Create New Version Action)
3530 CURSOR conterms_exist_csr is
3531 SELECT 'Y' FROM DUAL
3532 WHERE EXISTS (SELECT 'Y'
3533 FROM okc_template_usages
3534 WHERE DOCUMENT_ID = p_contract_id
3535 AND DOCUMENT_TYPE= contract_rec.contract_type);
3536 -- Repository Enhancement 12.1 Ends (For Create New Version Action)
3537 l_contract_source VARCHAR2(30); -- For Bug# 6902073
3538 BEGIN
3539
3540 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3541 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3542 'Entered OKC_REP_CONTRACT_PROCESS_PVT.version_contract_details');
3543 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3544 'Contract Id is: ' || p_contract_id);
3545 END IF;
3546 l_api_name := 'version_contract_details';
3547 l_api_version := 1.0;
3548 -- Standard Start of API savepoint
3549 SAVEPOINT version_contract_details_PVT;
3550 -- Standard call to check for call compatibility.
3551 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3553 END IF;
3554 -- Initialize message list if p_init_msg_list is set to TRUE.
3555 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3556 FND_MSG_PUB.initialize;
3557 END IF;
3558
3559 -- Initialize API return status to success
3560 x_return_status := FND_API.G_RET_STS_SUCCESS;
3561
3562 -- Lock the contract header
3563 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3564 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3565 'Calling OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header()');
3566 END IF;
3567 -- Lock the contract header
3568 Lock_Contract_Header(
3569 p_contract_id => p_contract_id,
3570 p_object_version_number => NULL,
3571 x_return_status => x_return_status
3572 );
3573 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3574 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3575 'OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header return status is: '
3576 || x_return_status);
3577 END IF;
3578 -----------------------------------------------------
3579 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3580 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3581 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3582 RAISE OKC_API.G_EXCEPTION_ERROR;
3583 END IF;
3584 -----------------------------------------------------
3585
3586 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3587 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3588 'Populating contract header record in OKC_REP_CONTRACT_VERS');
3589 END IF;
3590 -- Copy the header record to versions table
3591 INSERT INTO OKC_REP_CONTRACT_VERS(
3592 CONTRACT_ID,
3593 CONTRACT_VERSION_NUM,
3594 CONTRACT_NUMBER,
3595 CONTRACT_TYPE,
3596 CONTRACT_STATUS_CODE,
3597 ORG_ID,
3598 OWNER_ID,
3599 SOURCE_LANGUAGE,
3600 CONTRACT_NAME,
3601 CONTRACT_DESC,
3602 VERSION_COMMENTS,
3603 AUTHORING_PARTY_CODE,
3604 CONTRACT_EFFECTIVE_DATE,
3605 CONTRACT_EXPIRATION_DATE,
3606 CURRENCY_CODE,
3607 AMOUNT,
3608 OVERALL_RISK_CODE,
3609 CANCELLATION_COMMENTS,
3610 CANCELLATION_DATE,
3611 TERMINATION_COMMENTS,
3612 TERMINATION_DATE,
3613 KEYWORDS,
3614 PHYSICAL_LOCATION,
3615 EXPIRE_NTF_FLAG,
3616 EXPIRE_NTF_PERIOD,
3617 NOTIFY_CONTACT_ROLE_ID,
3618 USE_ACL_FLAG,
3619 WF_ITEM_TYPE,
3620 WF_ITEM_KEY,
3621 PROGRAM_ID,
3622 PROGRAM_LOGIN_ID,
3623 PROGRAM_APPLICATION_ID,
3624 REQUEST_ID,
3625 LATEST_SIGNED_VER_NUMBER,
3626 ATTRIBUTE_CATEGORY,
3627 ATTRIBUTE1,
3628 ATTRIBUTE2,
3629 ATTRIBUTE3,
3630 ATTRIBUTE4,
3631 ATTRIBUTE5,
3632 ATTRIBUTE6,
3633 ATTRIBUTE7,
3634 ATTRIBUTE8,
3635 ATTRIBUTE9,
3636 ATTRIBUTE10,
3637 ATTRIBUTE11,
3638 ATTRIBUTE12,
3639 ATTRIBUTE13,
3640 ATTRIBUTE14,
3641 ATTRIBUTE15,
3642 OBJECT_VERSION_NUMBER,
3643 CREATED_BY,
3644 CREATION_DATE,
3645 LAST_UPDATED_BY,
3646 LAST_UPDATE_LOGIN,
3647 LAST_UPDATE_DATE,
3648 CONTRACT_LAST_UPDATE_DATE,
3649 CONTRACT_LAST_UPDATED_BY,
3650 REFERENCE_DOCUMENT_TYPE,
3651 REFERENCE_DOCUMENT_NUMBER)
3652 SELECT
3653 CONTRACT_ID,
3654 CONTRACT_VERSION_NUM,
3655 CONTRACT_NUMBER,
3656 CONTRACT_TYPE,
3657 CONTRACT_STATUS_CODE,
3658 ORG_ID,
3659 OWNER_ID,
3660 SOURCE_LANGUAGE,
3661 CONTRACT_NAME,
3662 CONTRACT_DESC,
3663 VERSION_COMMENTS,
3664 AUTHORING_PARTY_CODE,
3665 CONTRACT_EFFECTIVE_DATE,
3666 CONTRACT_EXPIRATION_DATE,
3667 CURRENCY_CODE,
3668 AMOUNT,
3669 OVERALL_RISK_CODE,
3670 CANCELLATION_COMMENTS,
3671 CANCELLATION_DATE,
3672 TERMINATION_COMMENTS,
3673 TERMINATION_DATE,
3674 KEYWORDS,
3675 PHYSICAL_LOCATION,
3676 EXPIRE_NTF_FLAG,
3677 EXPIRE_NTF_PERIOD,
3678 NOTIFY_CONTACT_ROLE_ID,
3679 USE_ACL_FLAG,
3680 WF_ITEM_TYPE,
3681 WF_ITEM_KEY,
3682 PROGRAM_ID,
3683 PROGRAM_LOGIN_ID,
3684 PROGRAM_APPLICATION_ID,
3685 REQUEST_ID,
3686 LATEST_SIGNED_VER_NUMBER,
3687 ATTRIBUTE_CATEGORY,
3688 ATTRIBUTE1,
3689 ATTRIBUTE2,
3690 ATTRIBUTE3,
3691 ATTRIBUTE4,
3692 ATTRIBUTE5,
3693 ATTRIBUTE6,
3694 ATTRIBUTE7,
3695 ATTRIBUTE8,
3696 ATTRIBUTE9,
3697 ATTRIBUTE10,
3698 ATTRIBUTE11,
3699 ATTRIBUTE12,
3700 ATTRIBUTE13,
3701 ATTRIBUTE14,
3702 ATTRIBUTE15,
3703 OBJECT_VERSION_NUMBER,
3704 CREATED_BY,
3705 CREATION_DATE,
3706 LAST_UPDATED_BY,
3707 LAST_UPDATE_LOGIN,
3708 LAST_UPDATE_DATE,
3709 CONTRACT_LAST_UPDATE_DATE,
3710 CONTRACT_LAST_UPDATED_BY,
3711 REFERENCE_DOCUMENT_TYPE,
3712 REFERENCE_DOCUMENT_NUMBER
3713 FROM OKC_REP_CONTRACTS_ALL
3714 WHERE contract_id = p_contract_id;
3715
3716 -- Get contract_type of the contract, required for deliverables and documents APIs
3717 OPEN contract_csr;
3718 FETCH contract_csr INTO contract_rec;
3719 IF(contract_csr%NOTFOUND) THEN
3720 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3721 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
3722 G_MODULE||l_api_name,
3723 'Invalid Contract Id: '|| p_contract_id);
3724 END IF;
3725 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3726 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
3727 p_token1 => G_CONTRACT_ID_TOKEN,
3728 p_token1_value => to_char(p_contract_id));
3729 RAISE FND_API.G_EXC_ERROR;
3730 -- RAISE NO_DATA_FOUND;
3731 END IF;
3732
3733 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3734 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3735 'Calling OKC_CONTRACT_DOCS_GRP.Version_Attachments');
3736 END IF;
3737
3738 /*Bug 6957819: Added an additional parameter in the call for not copying the system generated attachment to the new version*/
3739
3740 OKC_CONTRACT_DOCS_GRP.Version_Attachments(
3741 p_api_version => 1,
3742 p_business_document_type => contract_rec.contract_type,
3743 p_business_document_id => p_contract_id,
3744 p_business_document_version => contract_rec.contract_version_num,
3745 x_return_status => x_return_status,
3746 x_msg_count => x_msg_count,
3747 x_msg_data => x_msg_data,
3748 p_include_gen_attach => 'N'
3749 );
3750 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3751 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3752 'OKC_CONTRACT_DOCS_GRP.Version_Attachments return status is : '
3753 || x_return_status);
3754 END IF;
3755 -----------------------------------------------------
3756 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3757 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3758 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3759 RAISE OKC_API.G_EXCEPTION_ERROR;
3760 END IF;
3761 --------------------------------------------------------
3762
3763 -- Repository Enhancement 12.1 (For Create New Version Action)
3764 -- SQL What:Find out if contract terms exist
3765 -- SQL Why: Archive Contract Terms if needed
3766 l_conterms_exist_flag := 'N';
3767 OPEN conterms_exist_csr;
3768 FETCH conterms_exist_csr into l_conterms_exist_flag;
3769 CLOSE conterms_exist_csr;
3770
3771
3772 IF (l_conterms_exist_flag = 'Y') THEN
3773 -- Code changes for Bug# 6902073 Begins
3774 l_contract_source := OKC_TERMS_UTIL_GRP.Get_Contract_Source_Code(
3775 p_document_type => contract_rec.contract_type,
3776 p_document_id => p_contract_id
3777 );
3778 IF l_contract_source = 'STRUCTURED' THEN
3779 --------------------------------------------
3780 -- Call internal Version_Doc
3781 --------------------------------------------
3782 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3783 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Call Private Version_Doc ');
3784 END IF;
3785
3786 OKC_TERMS_VERSION_PVT.Version_Doc(
3787 x_return_status => x_return_status,
3788
3789 p_doc_type => contract_rec.contract_type,
3790 p_doc_id => p_contract_id,
3791 p_version_number => contract_rec.contract_version_num
3792 );
3793 --------------------------------------------
3794 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3796 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3797 RAISE FND_API.G_EXC_ERROR ;
3798 END IF;
3799
3800 ELSIF l_contract_source = 'ATTACHED' THEN
3801
3802 --Only need to version usages record in case of offline authoring
3803 --------------------------------------------
3804 -- Call Create_Version for template usages
3805 --------------------------------------------
3806 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3807 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Call Create_Version for template usages');
3808 END IF;
3809
3810 x_return_status := OKC_TEMPLATE_USAGES_PVT.Create_Version(
3811 p_doc_type => contract_rec.contract_type,
3812 p_doc_id => p_contract_id,
3813 p_major_version => contract_rec.contract_version_num
3814 );
3815 --------------------------------------------
3816 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3818 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3819 RAISE FND_API.G_EXC_ERROR ;
3820 END IF;
3821
3822 END IF;
3823 -- Code changes for Bug# 6902073 Ends
3824
3825
3826 END IF; /* IF(l_conterms_exist_flag = 'Y') */
3827 -- Repository Enhancement 12.1 Ends(For Create New Version Action)
3828
3829
3830 -- If contract status not Signed, we need to version the deliverables as well
3831 IF (contract_rec.contract_status_code <> G_STATUS_SIGNED) THEN
3832 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3833 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3834 'Calling OKC_DELIVERABLE_PROCESS_PVT.version_deliverables');
3835 END IF;
3836 OKC_DELIVERABLE_PROCESS_PVT.version_deliverables (
3837 p_api_version => 1.0,
3838 p_init_msg_list => FND_API.G_FALSE,
3839 p_doc_id => p_contract_id,
3840 p_doc_version => contract_rec.contract_version_num,
3841 p_doc_type => contract_rec.contract_type,
3842 x_return_status => x_return_status,
3843 x_msg_count => x_msg_count,
3844 x_msg_data => x_msg_data
3845 );
3846 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3847 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3848 'OKC_DELIVERABLE_PROCESS_PVT.version_deliverables return status is : '
3849 || x_return_status);
3850 END IF;
3851 -----------------------------------------------------
3852 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3853 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3854 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3855 RAISE OKC_API.G_EXCEPTION_ERROR;
3856 END IF;
3857 --------------------------------------------------------
3858 END IF; -- contract_rec.contract_status_code <> G_STATUS_SIGNED
3859
3860 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3861 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3862 'Calling OKC_DELIVERABLE_PROCESS_PVT.clear_amendment_operation');
3863 END IF;
3864 OKC_DELIVERABLE_PROCESS_PVT.clear_amendment_operation (
3865 p_api_version => 1.0,
3866 p_init_msg_list => FND_API.G_FALSE,
3867 p_doc_id => p_contract_id,
3868 p_doc_type => contract_rec.contract_type,
3869 p_keep_summary => 'N',
3870 x_return_status => x_return_status,
3871 x_msg_count => x_msg_count,
3872 x_msg_data => x_msg_data
3873 );
3874 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3875 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3876 'OKC_DELIVERABLE_PROCESS_PVT.clear_amendment_operation return status is : '
3877 || x_return_status);
3878 END IF;
3879 -----------------------------------------------------
3880 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3881 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3882 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3883 RAISE OKC_API.G_EXCEPTION_ERROR;
3884 END IF;
3885 --------------------------------------------------------
3886
3887
3888
3889
3890
3891 CLOSE contract_csr;
3892
3893 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3894 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3895 'Delete contract header record.');
3896 END IF;
3897
3898
3899 -- Delete the header record
3900 DELETE FROM OKC_REP_CONTRACTS_ALL
3901 WHERE contract_id=p_contract_id;
3902
3903 -- Standard check of p_commit
3904 IF FND_API.To_Boolean( p_commit ) THEN
3905 COMMIT WORK;
3906 END IF;
3907
3908 -- Standard call to get message count and if count is 1, get message info.
3909 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
3910
3911 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3912 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3913 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.version_contract_details');
3914 END IF;
3915
3916
3917 EXCEPTION
3918 WHEN FND_API.G_EXC_ERROR THEN
3919 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3920 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3921 g_module || l_api_name,
3922 'Leaving version_contract_details:FND_API.G_EXC_ERROR Exception');
3923 END IF;
3924 --close cursors
3925 IF (contract_csr%ISOPEN) THEN
3926 CLOSE contract_csr ;
3927 END IF;
3928 ROLLBACK TO version_contract_details_PVT;
3929 x_return_status := FND_API.G_RET_STS_ERROR;
3930 FND_MSG_PUB.Count_And_Get(
3931 p_count => x_msg_count,
3932 p_data => x_msg_data
3933 );
3934
3935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3936 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3937 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3938 g_module || l_api_name,
3939 'Leaving version_contract_details:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
3940 END IF;
3941 --close cursors
3942 IF (contract_csr%ISOPEN) THEN
3943 CLOSE contract_csr ;
3944 END IF;
3945 ROLLBACK TO version_contract_details_PVT;
3946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3947 FND_MSG_PUB.Count_And_Get(
3948 p_count => x_msg_count,
3949 p_data => x_msg_data
3950 );
3951
3952 WHEN OTHERS THEN
3953 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3954 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3955 g_module || l_api_name,
3956 'Leaving version_contract_details because of EXCEPTION: ' || sqlerrm);
3957 END IF;
3958 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3959 p_msg_name => G_UNEXPECTED_ERROR,
3960 p_token1 => G_SQLCODE_TOKEN,
3961 p_token1_value => sqlcode,
3962 p_token2 => G_SQLERRM_TOKEN,
3963 p_token2_value => sqlerrm);
3964 ROLLBACK TO version_contract_details_PVT;
3965 --close cursors
3966 IF (contract_csr%ISOPEN) THEN
3967 CLOSE contract_csr ;
3968 END IF;
3969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3970 FND_MSG_PUB.Count_And_Get(
3971 p_count => x_msg_count,
3972 p_data => x_msg_data
3973 );
3974 END version_contract_details;
3975
3976
3977
3978 -- Start of comments
3979 --API name : sign_contract
3980 --Type : Private.
3981 --Function : Changes contract status to SIGNED and calls delivarables
3982 -- API to activate deliverables of that contract
3983 --Pre-reqs : None.
3984 --Parameters :
3985 --IN : p_api_version IN NUMBER Required
3986 -- : p_init_msg_list IN VARCHAR2 Optional
3987 -- Default = FND_API.G_FALSE
3988 -- : p_commit IN VARCHAR2 Optional
3989 -- Default = FND_API.G_FALSE
3990 -- : p_contract_id IN NUMBER Required
3991 -- Id of the contract to be signed
3992 --OUT : x_return_status OUT VARCHAR2(1)
3993 -- : x_msg_count OUT NUMBER
3994 -- : x_msg_data OUT VARCHAR2(2000)
3995 --Note :
3996 -- End of comments
3997 PROCEDURE sign_contract(
3998 p_api_version IN NUMBER,
3999 p_init_msg_list IN VARCHAR2,
4000 p_commit IN VARCHAR2,
4001 p_contract_id IN NUMBER,
4002 x_msg_data OUT NOCOPY VARCHAR2,
4003 x_msg_count OUT NOCOPY NUMBER,
4004 x_return_status OUT NOCOPY VARCHAR2)
4005 IS
4006 l_api_name VARCHAR2(30);
4007 l_api_version NUMBER;
4008 l_activate_event_tbl EVENT_TBL_TYPE;
4009 l_update_event_tbl EVENT_TBL_TYPE;
4010 l_sync_flag VARCHAR2(1);
4011 l_expiration_date_matches_flag VARCHAR2(1);
4012 l_effective_date_matches_flag VARCHAR2(1);
4013 l_prev_signed_expiration_date OKC_REP_CONTRACTS_ALL.CONTRACT_EXPIRATION_DATE%TYPE;
4014 l_prev_signed_effective_date OKC_REP_CONTRACTS_ALL.CONTRACT_EXPIRATION_DATE%TYPE;
4015
4016 CURSOR contract_csr IS
4017 SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date
4018 FROM OKC_REP_CONTRACTS_ALL
4019 WHERE contract_id = p_contract_id;
4020
4021 CURSOR arch_contract_csr (l_contract_version NUMBER) IS
4022 SELECT contract_effective_date, contract_expiration_date
4023 FROM OKC_REP_CONTRACT_VERS
4024 WHERE contract_id = p_contract_id
4025 AND contract_version_num = l_contract_version;
4026
4027 contract_rec contract_csr%ROWTYPE;
4028 arch_contract_rec arch_contract_csr%ROWTYPE;
4029
4030 BEGIN
4031
4032 l_expiration_date_matches_flag := FND_API.G_FALSE;
4033 l_effective_date_matches_flag := FND_API.G_FALSE;
4034
4035 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4036 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4037 'Entered OKC_REP_CONTRACT_PROCESS_PVT.sign_contract');
4038 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4039 'Contract Id is: ' || p_contract_id);
4040 END IF;
4041 l_api_name := 'sign_contacts';
4042 l_api_version := 1.0;
4043 -- Standard Start of API savepoint
4044 SAVEPOINT sign_contract_PVT;
4045 -- Standard call to check for call compatibility.
4046 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4048 END IF;
4049 -- Initialize message list if p_init_msg_list is set to TRUE.
4050 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4051 FND_MSG_PUB.initialize;
4052 END IF;
4053
4054 -- Initialize API return status to success
4055 x_return_status := FND_API.G_RET_STS_SUCCESS;
4056
4057 -- Get effective dates and version of the contract.
4058 OPEN contract_csr;
4059 FETCH contract_csr INTO contract_rec;
4060 IF(contract_csr%NOTFOUND) THEN
4061 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4062 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
4063 G_MODULE||l_api_name,
4064 'Invalid Contract Id: '|| p_contract_id);
4065 END IF;
4066 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4067 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
4068 p_token1 => G_CONTRACT_ID_TOKEN,
4069 p_token1_value => to_char(p_contract_id));
4070 RAISE FND_API.G_EXC_ERROR;
4071 -- RAISE NO_DATA_FOUND;
4072 END IF;
4073
4074 -- Lock the contract header
4075 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4076 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4077 'Calling OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header()');
4078 END IF;
4079 -- Lock the contract header
4080 Lock_Contract_Header(
4081 p_contract_id => p_contract_id,
4082 p_object_version_number => NULL,
4083 x_return_status => x_return_status
4084 );
4085 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4086 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4087 'OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header return status is: '
4088 || x_return_status);
4089 END IF;
4090 -----------------------------------------------------
4091 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4092 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4093 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4094 RAISE OKC_API.G_EXCEPTION_ERROR;
4095 END IF;
4096 -----------------------------------------------------
4097
4098 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4099 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4100 'Calling OKC_REP_UTIL_PVT.change_contract_status');
4101 END IF;
4102 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
4103 OKC_REP_UTIL_PVT.change_contract_status(
4104 p_api_version => 1.0,
4105 p_init_msg_list => FND_API.G_FALSE,
4106 p_contract_id => p_contract_id,
4107 p_contract_version => contract_rec.contract_version_num,
4108 p_status_code => G_STATUS_SIGNED,
4109 p_user_id => fnd_global.user_id,
4110 p_note => NULL,
4111 x_msg_data => x_msg_data,
4112 x_msg_count => x_msg_count,
4113 x_return_status => x_return_status);
4114 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4115 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4116 g_module || l_api_name,
4117 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || x_return_status);
4118 END IF;
4119 -----------------------------------------------------
4120 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4121 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4122 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4123 RAISE OKC_API.G_EXCEPTION_ERROR;
4124 END IF;
4125 ------------------------------------------------------
4126
4127 -- We need to first version the deliverables
4128 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4129 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4130 'Calling OKC_DELIVERABLE_PROCESS_PVT.version_deliverables');
4131 END IF;
4132 OKC_DELIVERABLE_PROCESS_PVT.version_deliverables (
4133 p_api_version => 1.0,
4134 p_init_msg_list => FND_API.G_FALSE,
4135 p_doc_id => p_contract_id,
4136 p_doc_version => contract_rec.contract_version_num,
4137 p_doc_type => contract_rec.contract_type,
4138 x_return_status => x_return_status,
4139 x_msg_count => x_msg_count,
4140 x_msg_data => x_msg_data
4141 );
4142 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4143 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4144 'OKC_DELIVERABLE_PROCESS_PVT.version_deliverables return status is : '
4145 || x_return_status);
4146 END IF;
4147 -----------------------------------------------------
4148 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4149 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4150 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4151 RAISE OKC_API.G_EXCEPTION_ERROR;
4152 END IF;
4153 --------------------------------------------------------
4154
4155 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4156 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4157 'Latest signed version number is : '
4158 || contract_rec.latest_signed_ver_number);
4159 END IF;
4160 -- Now we need to activate deliverables
4161 if (contract_rec.latest_signed_ver_number IS NULL) THEN
4162 l_sync_flag := FND_API.G_FALSE;
4163 ELSE
4164 l_sync_flag := FND_API.G_TRUE;
4165 END IF;
4166
4167 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4168 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4169 'l_sync_flag is : ' || l_sync_flag);
4170 END IF;
4171 l_activate_event_tbl(1).event_code := G_CONTRACT_EXPIRE_EVENT;
4172 l_activate_event_tbl(1).event_date := contract_rec.contract_expiration_date;
4173
4174 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4175 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4176 'Calling OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
4177 END IF;
4178
4179 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
4180 p_api_version => 1.0,
4181 p_init_msg_list => FND_API.G_FALSE,
4182 p_commit => FND_API.G_FALSE,
4183 p_bus_doc_id => p_contract_id,
4184 p_bus_doc_type => contract_rec.contract_type,
4185 p_bus_doc_version => contract_rec.contract_version_num,
4186 p_event_code => G_CONTRACT_EFFECTIVE_EVENT,
4187 p_event_date => contract_rec.contract_effective_date,
4188 p_sync_flag => l_sync_flag,
4189 p_bus_doc_date_events_tbl => l_activate_event_tbl,
4190 x_msg_data => x_msg_data,
4191 x_msg_count => x_msg_count,
4192 x_return_status => x_return_status);
4193
4194 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4195 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4196 'OKC_DELIVERABLE_PROCESS_PVT.activateDeliverables return status is : '
4197 || x_return_status);
4198 END IF;
4199 -----------------------------------------------------
4200 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4201 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4202 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4203 RAISE OKC_API.G_EXCEPTION_ERROR;
4204 END IF;
4205 --------------------------------------------------------
4206
4207 -- Checking if we need to call deliverable's APIs for synch-ing
4208 IF (l_sync_flag = FND_API.G_TRUE) THEN
4209 -- Get the previous signed contract's expiration date
4210 -- Get effective dates and version of the contract.
4211 OPEN arch_contract_csr(contract_rec.latest_signed_ver_number);
4212 FETCH arch_contract_csr INTO arch_contract_rec;
4213 IF(contract_csr%NOTFOUND) THEN
4214 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4215 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
4216 G_MODULE||l_api_name,
4217 'Invalid Contract Id: '|| p_contract_id);
4218 END IF;
4219 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4220 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
4221 p_token1 => G_CONTRACT_ID_TOKEN,
4222 p_token1_value => to_char(p_contract_id));
4223 RAISE FND_API.G_EXC_ERROR;
4224 -- RAISE NO_DATA_FOUND;
4225 END IF;
4226 l_prev_signed_effective_date := arch_contract_rec.contract_effective_date;
4227 l_prev_signed_expiration_date := arch_contract_rec.contract_expiration_date;
4228
4229 CLOSE arch_contract_csr;
4230 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4231 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4232 'Before checking if we need to call updateDeliverable and disableDeliverable()');
4233 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4234 'Prev signed expiration date: ' || trunc(l_prev_signed_expiration_date));
4235 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4236 'Current version expiration date: ' || trunc(contract_rec.contract_expiration_date));
4237 END IF;
4238 l_update_event_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
4239 l_update_event_tbl(1).event_date := contract_rec.contract_effective_date;
4240 l_update_event_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
4241 l_update_event_tbl(2).event_date := contract_rec.contract_expiration_date;
4242 -- If last signed version's expiration date is different from the current version's expiration date
4243 -- we need to call deliverables API for synching previous signed deliverables.
4244 -- This logic is executed to handle the null date scenarios
4245 IF (trunc(l_prev_signed_expiration_date)=trunc(contract_rec.contract_expiration_date)) THEN
4246 l_expiration_date_matches_flag := FND_API.G_TRUE;
4247 END IF;
4248
4249 IF (trunc(l_prev_signed_effective_date)=trunc(contract_rec.contract_effective_date)) THEN
4250 l_effective_date_matches_flag := FND_API.G_TRUE;
4251 END IF;
4252
4253 IF ((l_expiration_date_matches_flag = FND_API.G_FALSE ) OR (l_effective_date_matches_flag = FND_API.G_FALSE)) THEN
4254 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4255 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4256 'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
4257 END IF;
4258 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
4259 p_api_version => 1.0,
4260 p_init_msg_list => FND_API.G_FALSE,
4261 p_commit => FND_API.G_FALSE,
4262 p_bus_doc_id => p_contract_id,
4263 p_bus_doc_type => contract_rec.contract_type,
4264 p_bus_doc_version => contract_rec.contract_version_num,
4265 p_bus_doc_date_events_tbl => l_update_event_tbl,
4266 x_msg_data => x_msg_data,
4267 x_msg_count => x_msg_count,
4268 x_return_status => x_return_status);
4269
4270 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4271 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4272 'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
4273 || x_return_status);
4274 END IF;
4275 -----------------------------------------------------
4276 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4277 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4278 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4279 RAISE OKC_API.G_EXCEPTION_ERROR;
4280 END IF;
4281 --------------------------------------------------------
4282 END IF; -- expiration date comparision
4283 -- Disable prev. version deliverables
4284 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4285 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4286 'Calling OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables');
4287 END IF;
4288 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
4289 p_api_version => 1.0,
4290 p_init_msg_list => FND_API.G_FALSE,
4291 p_commit => FND_API.G_FALSE,
4292 p_bus_doc_id => p_contract_id,
4293 p_bus_doc_type => contract_rec.contract_type,
4294 p_bus_doc_version => contract_rec.latest_signed_ver_number,
4295 x_msg_data => x_msg_data,
4296 x_msg_count => x_msg_count,
4297 x_return_status => x_return_status);
4298
4299 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4300 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4301 'OKC_DELIVERABLE_PROCESS_PVT.disableDeliverables return status is : '
4302 || x_return_status);
4303 END IF;
4304 -----------------------------------------------------
4305 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4306 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4307 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4308 RAISE OKC_API.G_EXCEPTION_ERROR;
4309 END IF;
4310 --------------------------------------------------------
4311 END IF; -- (l_sync_flag = 'Y')
4312 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4313 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4314 'Updating latest_signed_ver_number column');
4315 END IF;
4316 UPDATE okc_rep_contracts_all
4317 SET latest_signed_ver_number = contract_rec.contract_version_num
4318 WHERE contract_id = p_contract_id;
4319 CLOSE contract_csr;
4320 -- Standard check of p_commit
4321 IF FND_API.To_Boolean( p_commit ) THEN
4322 COMMIT WORK;
4323 END IF;
4324
4325 -- Standard call to get message count and if count is 1, get message info.
4326 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4327
4328 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4329 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4330 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.sign_contract');
4331 END IF;
4332
4333
4334 EXCEPTION
4335 WHEN FND_API.G_EXC_ERROR THEN
4336 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4337 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4338 g_module || l_api_name,
4339 'Leaving sign_contract:FND_API.G_EXC_ERROR Exception');
4340 END IF;
4341 --close cursors
4342 IF (contract_csr%ISOPEN) THEN
4343 CLOSE contract_csr ;
4344 END IF;
4345 IF (arch_contract_csr%ISOPEN) THEN
4346 CLOSE arch_contract_csr ;
4347 END IF;
4348 ROLLBACK TO sign_contract_PVT;
4349 x_return_status := FND_API.G_RET_STS_ERROR;
4350 FND_MSG_PUB.Count_And_Get(
4351 p_count => x_msg_count,
4352 p_data => x_msg_data
4353 );
4354
4355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4356 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4357 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4358 g_module || l_api_name,
4359 'Leaving sign_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
4360 END IF;
4361 --close cursors
4362 IF (contract_csr%ISOPEN) THEN
4363 CLOSE contract_csr ;
4364 END IF;
4365 IF (arch_contract_csr%ISOPEN) THEN
4366 CLOSE arch_contract_csr ;
4367 END IF;
4368 ROLLBACK TO sign_contract_PVT;
4369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4370 FND_MSG_PUB.Count_And_Get(
4371 p_count => x_msg_count,
4372 p_data => x_msg_data
4373 );
4374
4375 WHEN OTHERS THEN
4376 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4377 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4378 g_module || l_api_name,
4379 'Leaving sign_contract because of EXCEPTION: ' || sqlerrm);
4380 END IF;
4381 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4382 p_msg_name => G_UNEXPECTED_ERROR,
4383 p_token1 => G_SQLCODE_TOKEN,
4384 p_token1_value => sqlcode,
4385 p_token2 => G_SQLERRM_TOKEN,
4386 p_token2_value => sqlerrm);
4387 ROLLBACK TO sign_contract_PVT;
4388 --close cursors
4389 IF (contract_csr%ISOPEN) THEN
4390 CLOSE contract_csr ;
4391 END IF;
4392 IF (arch_contract_csr%ISOPEN) THEN
4393 CLOSE arch_contract_csr ;
4394 END IF;
4395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4396 FND_MSG_PUB.Count_And_Get(
4397 p_count => x_msg_count,
4398 p_data => x_msg_data
4399 );
4400 END sign_contract;
4401
4402
4403 -- Start of comments
4404 --API name : terminate_contract
4405 --Type : Private.
4406 --Function : Changes contract status to TERMINATED and calls delivarables
4407 -- API to cancel deliverables of that contract
4408 --Pre-reqs : None.
4409 --Parameters :
4410 --IN : p_api_version IN NUMBER Required
4411 -- : p_init_msg_list IN VARCHAR2 Optional
4412 -- Default = FND_API.G_FALSE
4413 -- : p_commit IN VARCHAR2 Optional
4414 -- Default = FND_API.G_FALSE
4415 -- : p_contract_id IN NUMBER Required
4416 -- Id of the contract to be terminated
4417 -- : p_termination_date IN DATE Required
4418 -- Date the contract is terminated
4419 --OUT : x_return_status OUT VARCHAR2(1)
4420 -- : x_msg_count OUT NUMBER
4421 -- : x_msg_data OUT VARCHAR2(2000)
4422 --Note :
4423 -- End of comments
4424 PROCEDURE terminate_contract(
4425 p_api_version IN NUMBER,
4426 p_init_msg_list IN VARCHAR2,
4427 p_commit IN VARCHAR2,
4428 p_contract_id IN NUMBER,
4429 p_termination_date IN DATE,
4430 x_msg_data OUT NOCOPY VARCHAR2,
4431 x_msg_count OUT NOCOPY NUMBER,
4432 x_return_status OUT NOCOPY VARCHAR2)
4433 IS
4434
4435 l_api_name VARCHAR2(30);
4436 l_api_version NUMBER;
4437 l_cancel_event_tbl EVENT_TBL_TYPE;
4438 l_update_event_tbl EVENT_TBL_TYPE;
4439
4440
4441
4442 CURSOR contract_csr IS
4443 SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date,
4444 contract_expiration_date, termination_date
4445 FROM OKC_REP_CONTRACTS_ALL
4446 WHERE contract_id = p_contract_id;
4447
4448 contract_rec contract_csr%ROWTYPE;
4449 BEGIN
4450
4451 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4452 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4453 'Entered OKC_REP_CONTRACT_PROCESS_PVT.terminate_contract');
4454 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4455 'Contract Id is: ' || p_contract_id);
4456 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4457 'Termination date is: ' || to_char(p_termination_date));
4458 END IF;
4459 l_api_name := 'terminate_contacts';
4460 l_api_version := 1.0;
4461 -- Standard Start of API savepoint
4462 SAVEPOINT terminate_contract_PVT;
4463 -- Standard call to check for call compatibility.
4464 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4466 END IF;
4467 -- Initialize message list if p_init_msg_list is set to TRUE.
4468 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4469 FND_MSG_PUB.initialize;
4470 END IF;
4471
4472 -- Initialize API return status to success
4473 x_return_status := FND_API.G_RET_STS_SUCCESS;
4474
4475 -- Get effective dates and version of the contract.
4476 OPEN contract_csr;
4477 FETCH contract_csr INTO contract_rec;
4478 IF(contract_csr%NOTFOUND) THEN
4479 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4480 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
4481 G_MODULE||l_api_name,
4482 'Invalid Contract Id: '|| p_contract_id);
4483 END IF;
4484 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4485 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
4486 p_token1 => G_CONTRACT_ID_TOKEN,
4487 p_token1_value => to_char(p_contract_id));
4488 RAISE FND_API.G_EXC_ERROR;
4489 -- RAISE NO_DATA_FOUND;
4490 END IF;
4491
4492 -- Lock the contract header
4493 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4494 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4495 'Calling OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header()');
4496 END IF;
4497 -- Lock the contract header
4498 Lock_Contract_Header(
4499 p_contract_id => p_contract_id,
4500 p_object_version_number => NULL,
4501 x_return_status => x_return_status
4502 );
4503 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4504 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4505 'OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header return status is: '
4506 || x_return_status);
4507 END IF;
4508 -----------------------------------------------------
4509 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4510 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4511 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4512 RAISE OKC_API.G_EXCEPTION_ERROR;
4513 END IF;
4514 -----------------------------------------------------
4515 IF (trunc(p_termination_date) <= trunc(sysdate)) THEN
4516 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4517 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4518 'Calling OKC_REP_UTIL_PVT.change_contract_status');
4519 END IF;
4520
4521 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
4522 OKC_REP_UTIL_PVT.change_contract_status(
4523 p_api_version => 1.0,
4524 p_init_msg_list => FND_API.G_FALSE,
4525 p_contract_id => p_contract_id,
4526 p_contract_version => contract_rec.contract_version_num,
4527 p_status_code => G_STATUS_TERMINATED,
4528 p_user_id => fnd_global.user_id,
4529 p_note => NULL,
4530 x_msg_data => x_msg_data,
4531 x_msg_count => x_msg_count,
4532 x_return_status => x_return_status);
4533 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4534 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4535 g_module || l_api_name,
4536 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || x_return_status);
4537 END IF;
4538 -----------------------------------------------------
4539 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4540 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4541 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4542 RAISE OKC_API.G_EXCEPTION_ERROR;
4543 END IF;
4544 ------------------------------------------------------
4545
4546 END IF; -- (p_termination_date <= sysdate)
4547
4548 -- We should call cancel_deliverables only for the first time
4549 IF (contract_rec.termination_date IS NULL) THEN
4550 l_cancel_event_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
4551 l_cancel_event_tbl(1).event_date := contract_rec.contract_effective_date;
4552 l_cancel_event_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
4553 l_cancel_event_tbl(2).event_date := contract_rec.contract_expiration_date;
4554 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4555 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4556 'Calling OKC_MANAGE_DELIVERABLES_GRP.activateCloseOutDeliverables ');
4557 END IF;
4558
4559 OKC_MANAGE_DELIVERABLES_GRP.activateCloseOutDeliverables (
4560 p_api_version => 1.0,
4561 p_init_msg_list => FND_API.G_FALSE,
4562 p_commit => FND_API.G_FALSE,
4563 p_bus_doc_id => p_contract_id,
4564 p_bus_doc_type => contract_rec.contract_type,
4565 p_bus_doc_version => contract_rec.contract_version_num,
4566 p_event_code => G_CONTRACT_TERMINATED_EVENT,
4567 p_event_date => p_termination_date,
4568 p_bus_doc_date_events_tbl => l_cancel_event_tbl,
4569 x_msg_data => x_msg_data,
4570 x_msg_count => x_msg_count,
4571 x_return_status => x_return_status);
4572
4573 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4574 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4575 'OKC_DELIVERABLE_PROCESS_PVT.activateCloseOutDeliverables return status is : '
4576 || x_return_status);
4577 END IF;
4578 -----------------------------------------------------
4579 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4580 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4581 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4582 RAISE OKC_API.G_EXCEPTION_ERROR;
4583 END IF;
4584 --------------------------------------------------------
4585 ELSE
4586 IF (trunc(contract_rec.termination_date) <> trunc(p_termination_date)) THEN
4587 -- Update the deliverables
4588 l_update_event_tbl(1).event_code := G_CONTRACT_TERMINATED_EVENT;
4589 l_update_event_tbl(1).event_date := p_termination_date;
4590 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4591 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4592 'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables ');
4593 END IF;
4594 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
4595 p_api_version => 1.0,
4596 p_init_msg_list => FND_API.G_FALSE,
4597 p_commit => FND_API.G_FALSE,
4598 p_bus_doc_id => p_contract_id,
4599 p_bus_doc_type => contract_rec.contract_type,
4600 p_bus_doc_version => contract_rec.contract_version_num,
4601 p_bus_doc_date_events_tbl => l_update_event_tbl,
4602 x_msg_data => x_msg_data,
4603 x_msg_count => x_msg_count,
4604 x_return_status => x_return_status);
4605
4606 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4607 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4608 'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
4609 || x_return_status);
4610 END IF;
4611 -----------------------------------------------------
4612 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4613 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4614 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4615 RAISE OKC_API.G_EXCEPTION_ERROR;
4616 END IF;
4617 --------------------------------------------------------
4618 END IF; -- contract_rec.termination_date <> p_termination_date
4619 END IF; -- contract_rec.termination_date = null
4620
4621 CLOSE contract_csr;
4622
4623 -- Standard check of p_commit
4624 IF FND_API.To_Boolean( p_commit ) THEN
4625 COMMIT WORK;
4626 END IF;
4627
4628 -- Standard call to get message count and if count is 1, get message info.
4629 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4630
4631 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4632 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4633 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.terminate_contract');
4634 END IF;
4635
4636
4637 EXCEPTION
4638 WHEN FND_API.G_EXC_ERROR THEN
4639 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4640 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4641 g_module || l_api_name,
4642 'Leaving terminate_contract:FND_API.G_EXC_ERROR Exception');
4643 END IF;
4644 --close cursors
4645 IF (contract_csr%ISOPEN) THEN
4646 CLOSE contract_csr ;
4647 END IF;
4648 ROLLBACK TO terminate_contract_PVT;
4649 x_return_status := FND_API.G_RET_STS_ERROR;
4650 FND_MSG_PUB.Count_And_Get(
4651 p_count => x_msg_count,
4652 p_data => x_msg_data
4653 );
4654
4655 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4656 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4657 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4658 g_module || l_api_name,
4659 'Leaving terminate_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
4660 END IF;
4661 --close cursors
4662 IF (contract_csr%ISOPEN) THEN
4663 CLOSE contract_csr ;
4664 END IF;
4665 ROLLBACK TO terminate_contract_PVT;
4666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4667 FND_MSG_PUB.Count_And_Get(
4668 p_count => x_msg_count,
4669 p_data => x_msg_data
4670 );
4671
4672 WHEN OTHERS THEN
4673 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4674 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4675 g_module || l_api_name,
4676 'Leaving terminate_contract because of EXCEPTION: ' || sqlerrm);
4677 END IF;
4678 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4679 p_msg_name => G_UNEXPECTED_ERROR,
4680 p_token1 => G_SQLCODE_TOKEN,
4681 p_token1_value => sqlcode,
4682 p_token2 => G_SQLERRM_TOKEN,
4683 p_token2_value => sqlerrm);
4684 ROLLBACK TO terminate_contract_PVT;
4685 --close cursors
4686 IF (contract_csr%ISOPEN) THEN
4687 CLOSE contract_csr;
4688 END IF;
4689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4690 FND_MSG_PUB.Count_And_Get(
4691 p_count => x_msg_count,
4692 p_data => x_msg_data
4693 );
4694 END terminate_contract;
4695
4696 -- Start of comments
4697 --API name : repository_notifier
4698 --Type : Private.
4699 --Function : Sends notifications to contract's contacts if
4700 -- the contract is about to expire or expired.
4701 --Pre-reqs : None.
4702 --Parameters :
4703 --IN : p_api_version IN NUMBER Required
4704 -- : p_init_msg_list IN VARCHAR2 Optional
4705 -- Default = FND_API.G_FALSE
4706 -- : p_contract_id IN NUMBER Required
4707 -- Id of the contract to be processed
4708 -- : p_contract_number IN NUMBER Required
4709 -- Number of the contract to be processed
4710 -- : p_contract_version IN NUMBER Required
4711 -- Version of the contract to be processed
4712 --OUT : x_return_status OUT VARCHAR2(1)
4713 -- : x_msg_count OUT NUMBER
4714 -- : x_msg_data OUT VARCHAR2(2000)
4715 --Note :
4716 -- End of comments
4717
4718 PROCEDURE repository_notifier(
4719 p_api_version IN NUMBER,
4720 p_init_msg_list IN VARCHAR2,
4721 p_contract_id IN NUMBER,
4722 p_contract_number IN VARCHAR2,
4723 p_contract_version IN NUMBER,
4724 p_expired_flag IN VARCHAR2,
4725 p_notify_contact_role_id IN NUMBER,
4726 x_msg_data OUT NOCOPY VARCHAR2,
4727 x_msg_count OUT NOCOPY NUMBER,
4728 x_return_status OUT NOCOPY VARCHAR2)
4729 IS
4730 l_item_key NUMBER;
4731 l_item_type VARCHAR2(30);
4732 l_process_name VARCHAR2(30);
4733 l_contract_contacts_role_name VARCHAR2(320);
4734 l_user_name VARCHAR2(4000);
4735 l_display_name VARCHAR2(4000);
4736 l_contract_contacts_all VARCHAR2(4000);
4737 l_contract_contacts_role_desc VARCHAR2(500);
4738 l_subject_text VARCHAR2(200);
4739 l_error_msg VARCHAR2(4000);
4740 l_api_name VARCHAR2(30);
4741 l_api_version NUMBER;
4742 l_item_contract_id VARCHAR2(30);
4743 l_item_contract_number VARCHAR2(30);
4744 l_item_contract_version VARCHAR2(30);
4745 l_item_contract_contacts VARCHAR2(30);
4746 l_item_message_subject VARCHAR2(30);
4747 l_message_code_expired VARCHAR2(32);
4748 l_message_code_about_to_expire VARCHAR2(32);
4749 l_message_token_con_number VARCHAR2(30);
4750 l_message_token_con_version VARCHAR2(30);
4751 l_app_name VARCHAR2(30);
4752 l_msg_code VARCHAR2(30);
4753 l_contact_role_name okc_rep_contact_roles_tl.name%TYPE;
4754 l_email per_all_people_f.email_address%TYPE;
4755
4756 CURSOR CONTRACT_CONTACTS(c_contract_id in number) IS
4757 SELECT contact_id
4758 FROM okc_rep_party_contacts
4759 WHERE contract_id = c_contract_id
4760 AND party_role_code = G_PARTY_TYPE_INTERNAL
4761 AND contact_role_id = p_notify_contact_role_id;
4762
4763 CURSOR CONTACT_ATTRIBUTES(c_contact_id in number) IS
4764 SELECT email_address
4765 FROM per_all_people_f
4766 WHERE person_id = c_contact_id
4767 AND effective_start_date = (SELECT MAX(effective_start_date)
4768 FROM per_all_people_f
4769 WHERE person_id = c_contact_id);
4770
4771 contact_attributes_rec contact_attributes%ROWTYPE;
4772
4773
4774 BEGIN
4775 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4776 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4777 'Entered OKC_REP_CONTRACT_PROCESS_PVT.repository_notifier');
4778 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4779 'Contract Id is: ' || p_contract_id);
4780 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4781 'Contract Number is: ' || p_contract_number);
4782 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4783 'Expired flag is: ' || p_expired_flag);
4784 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4785 'Contact Role Id is: ' || p_notify_contact_role_id);
4786 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4787 'Contract Version is: ' || p_contract_version);
4788 END IF;
4789
4790 --initialize variables
4791 l_app_name := 'OKC';
4792 l_api_name := 'repository_notifier';
4793 l_api_version := 1.0;
4794 l_item_type := 'OKCREPXN';
4795 l_process_name := 'REP_CONTRACT_EXPIRATION_NTF';
4796
4797 l_item_contract_id := 'CONTRACT_ID';
4798 l_item_contract_number := 'CONTRACT_NUMBER';
4799 l_item_contract_version := 'CONTRACT_VERSION';
4800 l_item_contract_contacts := 'CONTRACT_CONTACTS';
4801 l_item_message_subject := 'SUBJECT';
4802
4803 l_message_code_expired := 'OKC_REP_CONTRACT_EXPIRED';
4804 l_message_code_about_to_expire := 'OKC_REP_CON_ABOUT_TO_EXPIRE';
4805
4806 l_message_token_con_number := 'CONTRACT_NUMBER';
4807 l_message_token_con_version := 'CONTRACT_VERSION';
4808
4809 -- Standard Start of API savepoint
4810 SAVEPOINT repository_notifier_pvt;
4811
4812 -- Standard call to check for call compatibility.
4813 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4815 END IF;
4816
4817 -- Initialize message list if p_init_msg_list is set to TRUE.
4818 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4819 FND_MSG_PUB.initialize;
4820 END IF;
4821
4822 FOR contract_contacts_rec in CONTRACT_CONTACTS(p_contract_id) LOOP
4823
4824 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4825 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4826 'contract_contacts_rec.contact_id ' || contract_contacts_rec.contact_id);
4827 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4828 'Calling WF_DIRECTORY.GetUserName()');
4829 END IF;
4830
4831 -- Get WF user name for the current contact
4832 WF_DIRECTORY.GetUserName(p_orig_system => 'PER',
4833 p_orig_system_id => contract_contacts_rec.contact_id,
4834 p_name => l_user_name,
4835 p_display_name => l_display_name);
4836
4837 IF (l_user_name IS NULL) THEN
4838
4839 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4840 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4841 'Current contact does not have FND User');
4842 END IF;
4843
4844 -- Get Email address of the current contact
4845 OPEN CONTACT_ATTRIBUTES(contract_contacts_rec.contact_id);
4846 FETCH CONTACT_ATTRIBUTES into l_email;
4847 CLOSE CONTACT_ATTRIBUTES;
4848
4849 -- Create adhoc user only if the current contact has a email address
4850 IF (l_email IS NOT NULL) THEN
4851
4852 l_display_name := null;
4853
4854 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4855 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4856 'Calling WF_DIRECTORY.CreateAdHocUser() with email ' || l_email);
4857 END IF;
4858
4859 --create ad hoc user if user does not already exists
4860 WF_DIRECTORY.CreateAdHocUser(
4861 name => l_user_name,
4862 display_name => l_display_name,
4863 email_address => l_email,
4864 description => 'Repository Ad Hoc User',
4865 notification_preference => 'MAILHTML',
4866 expiration_date => SYSDATE + 1);
4867
4868 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4869 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4870 'Adhoc User Name ' || l_user_name);
4871 END IF;
4872
4873 ELSE
4874
4875 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4876 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4877 'Email address not available for the current contact');
4878 END IF;
4879
4880 END IF;
4881
4882 ELSE
4883
4884 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4885 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4886 'WF User Name ' || l_user_name);
4887 END IF;
4888
4889 END IF;
4890
4891 --build concatinated user name list
4892 IF (l_user_name IS NOT NULL) THEN
4893
4894 IF (l_contract_contacts_all IS NULL) THEN
4895 l_contract_contacts_all := l_user_name;
4896 ELSE
4897 l_contract_contacts_all := l_contract_contacts_all || ',' || l_user_name;
4898 END IF;
4899
4900 END IF;
4901
4902 END LOOP;
4903
4904 IF (l_contract_contacts_all IS NOT NULL) THEN
4905
4906 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4907 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4908 'User names list ' || l_contract_contacts_all);
4909 END IF;
4910
4911 --Get item key from sequence
4912 SELECT TO_CHAR(okc_wf_notify_s1.NEXTVAL) INTO l_item_key FROM DUAL;
4913
4914 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4915 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4916 'Calling WF_DIRECTORY.createAdHocRole()');
4917 END IF;
4918
4919 --create ad hoc workflow role
4920 WF_DIRECTORY.createAdHocRole( role_name => l_contract_contacts_role_name,
4921 role_display_name => l_contract_contacts_role_desc,
4922 role_description => 'Repository Ad Hoc Role',
4923 notification_preference =>'MAILHTML',
4924 role_users => l_contract_contacts_all,
4925 expiration_date => SYSDATE + 1
4926 );
4927
4928 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4929 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4930 'Calling wf_engine.CreateProcess()');
4931 END IF;
4932
4933 --Create the process
4934 wf_engine.CreateProcess(
4935 itemtype => l_item_type,
4936 itemkey => l_item_key,
4937 process => l_process_name);
4938
4939 --set standard parameter
4940 wf_engine.SetItemUserKey (
4941 itemtype => l_item_type,
4942 itemkey => l_item_key,
4943 userkey => l_item_key);
4944
4945 --set process owner
4946 wf_engine.SetItemOwner (
4947 itemtype => l_item_type,
4948 itemkey => l_item_key,
4949 owner => fnd_global.user_name);
4950
4951 --set contracts role parameter
4952 wf_engine.SetItemAttrText (
4953 itemtype => l_item_type,
4954 itemkey => l_item_key,
4955 aname => l_item_contract_contacts,
4956 avalue => l_contract_contacts_role_name);
4957
4958 --set contract id parameter
4959 wf_engine.SetItemAttrText (
4960 itemtype => l_item_type,
4961 itemkey => l_item_key,
4962 aname => l_item_contract_id,
4963 avalue => p_contract_id);
4964
4965 --set contract number parameter
4966 wf_engine.SetItemAttrText (
4967 itemtype => l_item_type,
4968 itemkey => l_item_key,
4969 aname => l_item_contract_number,
4970 avalue => p_contract_number);
4971
4972 --set contract version parameter
4973 wf_engine.SetItemAttrText (
4974 itemtype => l_item_type,
4975 itemkey => l_item_key,
4976 aname => l_item_contract_version,
4977 avalue => p_contract_version);
4978
4979 --set message text, one message for already expired contract
4980 --and one for contract about to expire
4981 IF (p_expired_flag = 'Y') THEN
4982 l_msg_code := l_message_code_expired;
4983 ELSE
4984 l_msg_code := l_message_code_about_to_expire;
4985 END IF;
4986
4987 fnd_message.clear;
4988 --set message name
4989 fnd_message.set_name(
4990 application =>l_app_name,
4991 name =>l_msg_code);
4992 --set message tokens
4993 fnd_message.set_token(
4994 token => l_message_token_con_number,
4995 value => p_contract_number);
4996 fnd_message.set_token(
4997 token => l_message_token_con_version,
4998 value => p_contract_version);
4999
5000 --get fnd message
5001 l_subject_text := fnd_message.get;
5002
5003 --set message subject
5004 wf_engine.SetItemAttrText (
5005 itemtype => l_item_type,
5006 itemkey => l_item_key,
5007 aname => l_item_message_subject,
5008 avalue => l_subject_text);
5009
5010 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5011 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5012 'Calling wf_engine.StartProcess()');
5013 END IF;
5014
5015 --Start the process
5016 wf_engine.StartProcess(
5017 itemtype => l_item_type,
5018 itemkey => l_item_key);
5019
5020 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5021 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5022 'Updating okc_rep_contracts_all and okc_rep_contract_vers with wf_exp_ntf_item_key ' || l_item_key);
5023 END IF;
5024
5025 --update contracts with sent notifications
5026 UPDATE okc_rep_contracts_all c
5027 SET c.wf_exp_ntf_item_key = l_item_key
5028 WHERE c.contract_id = p_contract_id
5029 AND c.contract_version_num = p_contract_version;
5030
5031 --update contract versions with sent notifications
5032 UPDATE okc_rep_contract_vers c
5033 SET c.wf_exp_ntf_item_key = l_item_key
5034 WHERE c.contract_id = p_contract_id
5035 AND c.contract_version_num = p_contract_version;
5036
5037 COMMIT;
5038
5039 ELSE
5040
5041 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5042 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5043 'No WF users found for contacts of this contract');
5044 END IF;
5045
5046 END IF;
5047
5048 -- Standard call to get message count and if count is 1, get message info.
5049 FND_MSG_PUB.Count_And_Get(
5050 p_count => x_msg_count,
5051 p_data => x_msg_data );
5052
5053 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5054 FND_LOG.STRING(
5055 FND_LOG.LEVEL_PROCEDURE,
5056 G_MODULE||l_api_name,
5057 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.repository_notifier');
5058 END IF;
5059
5060
5061 EXCEPTION
5062 WHEN FND_API.G_EXC_ERROR THEN
5063 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5064 fnd_log.string(
5065 FND_LOG.LEVEL_EXCEPTION,
5066 g_module || l_api_name,
5067 'Leaving repository_notifier:FND_API.G_EXC_ERROR Exception');
5068 END IF;
5069 --close cursors
5070 IF (CONTRACT_CONTACTS%ISOPEN) THEN
5071 CLOSE CONTRACT_CONTACTS ;
5072 END IF;
5073 IF (CONTACT_ATTRIBUTES%ISOPEN) THEN
5074 CLOSE CONTACT_ATTRIBUTES ;
5075 END IF;
5076 ROLLBACK TO repository_notifier_pvt;
5077
5078 x_return_status := FND_API.G_RET_STS_ERROR;
5079 FND_MSG_PUB.Count_And_Get(
5080 p_count => x_msg_count,
5081 p_data => x_msg_data
5082 );
5083
5084 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5085 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5086 fnd_log.string(
5087 FND_LOG.LEVEL_EXCEPTION,
5088 g_module || l_api_name,
5089 'Leaving repository_notifier:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
5090 END IF;
5091 --close cursors
5092 IF (CONTRACT_CONTACTS%ISOPEN) THEN
5093 CLOSE CONTRACT_CONTACTS ;
5094 END IF;
5095 IF (CONTACT_ATTRIBUTES%ISOPEN) THEN
5096 CLOSE CONTACT_ATTRIBUTES ;
5097 END IF;
5098 ROLLBACK TO repository_notifier_pvt;
5099
5100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5101 FND_MSG_PUB.Count_And_Get(
5102 p_count => x_msg_count,
5103 p_data => x_msg_data
5104 );
5105
5106 WHEN OTHERS THEN
5107 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5108 fnd_log.string(
5109 FND_LOG.LEVEL_EXCEPTION,
5110 g_module || l_api_name,
5111 'Leaving repository_notifier because of EXCEPTION: ' || sqlerrm);
5112 END IF;
5113 Okc_Api.Set_Message(
5114 p_app_name => G_APP_NAME,
5115 p_msg_name => G_UNEXPECTED_ERROR,
5116 p_token1 => G_SQLCODE_TOKEN,
5117 p_token1_value => SQLCODE,
5118 p_token2 => G_SQLERRM_TOKEN,
5119 p_token2_value => SQLERRM);
5120
5121 --close cursors
5122 IF (CONTRACT_CONTACTS%ISOPEN) THEN
5123 CLOSE CONTRACT_CONTACTS ;
5124 END IF;
5125 IF (CONTACT_ATTRIBUTES%ISOPEN) THEN
5126 CLOSE CONTACT_ATTRIBUTES ;
5127 END IF;
5128 ROLLBACK TO repository_notifier_pvt;
5129
5130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5131 FND_MSG_PUB.Count_And_Get(
5132 p_count => x_msg_count,
5133 p_data => x_msg_data
5134 );
5135
5136 END repository_notifier;
5137
5138 --API name : cancel_approval
5139 --Type : Private.
5140 --Function : Aborts the contract approval workflow process.
5141 --Pre-reqs : None.
5142 --Parameters :
5143 --IN : p_api_version IN NUMBER Required
5144 -- : p_init_msg_list IN VARCHAR2 Optional
5145 -- Default = FND_API.G_FALSE
5146 -- : p_contract_id IN NUMBER Required
5147 -- Id of the contract to be processed
5148 -- : p_contract_version IN NUMBER Required
5149 -- Version of the contract to be processed
5150 --OUT : x_return_status OUT VARCHAR2(1)
5151 -- : x_msg_count OUT NUMBER
5152 -- : x_msg_data OUT VARCHAR2(2000)
5153 --Note :
5154 -- End of comments
5155 PROCEDURE cancel_approval(
5156 p_api_version IN NUMBER,
5157 p_init_msg_list IN VARCHAR2,
5158 p_contract_id IN NUMBER,
5159 p_contract_version IN NUMBER,
5160 x_return_status OUT NOCOPY VARCHAR2,
5161 x_msg_count OUT NOCOPY NUMBER,
5162 x_msg_data OUT NOCOPY VARCHAR2
5163
5164 ) IS
5165 l_api_name VARCHAR2(30);
5166 l_api_version NUMBER;
5167 l_wf_type wf_item_activity_statuses.item_type%TYPE;
5168 l_wf_key wf_item_activity_statuses.item_key%TYPE;
5169 l_contract_status okc_rep_contracts_all.contract_status_code%TYPE;
5170 l_contract_number okc_rep_contracts_all.contract_number%TYPE;
5171
5172 CURSOR contract_csr IS
5173 SELECT wf_item_type, wf_item_key, contract_status_code, contract_number
5174 FROM okc_rep_contracts_all
5175 WHERE contract_id = p_contract_id;
5176
5177 BEGIN
5178
5179 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5181 'Entered OKC_REP_CONTRACT_PROCESS_PVT.cancel_approval');
5182 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5183 'Contract Id is: ' || p_contract_id);
5184 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5185 'Contract Version is: ' || p_contract_version);
5186 END IF;
5187 l_api_name := 'cancel_approval';
5188 l_api_version := 1.0;
5189
5190 -- Standard Start of API savepoint
5191 SAVEPOINT submit_contract_PVT;
5192
5193 -- Standard call to check for call compatibility.
5194 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
5195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5196 END IF;
5197
5198 -- Initialize message list if p_init_msg_list is set to TRUE.
5199 IF FND_API.to_Boolean( p_init_msg_list ) THEN
5200 FND_MSG_PUB.initialize;
5201 END IF;
5202
5203 -- Initialize API return status to success
5204 x_return_status := FND_API.G_RET_STS_SUCCESS;
5205
5206
5207 -- Get workflow information of the contract's approval process
5208 OPEN contract_csr;
5209 FETCH contract_csr into l_wf_type, l_wf_key, l_contract_status, l_contract_number;
5210 IF(contract_csr%NOTFOUND) THEN
5211 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5212 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
5213 G_MODULE||l_api_name,
5214 'Invalid Contract Id: '|| p_contract_id);
5215 END IF;
5216 CLOSE contract_csr;
5217 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
5218 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
5219 p_token1 => G_CONTRACT_ID_TOKEN,
5220 p_token1_value => to_char(p_contract_id));
5221 RAISE FND_API.G_EXC_ERROR;
5222 -- RAISE NO_DATA_FOUND;
5223 END IF;
5224 CLOSE contract_csr;
5225
5226 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5227 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5228 'Calling WF_ENGINE.AbortProcess');
5229 END IF;
5230
5231 -- Check the status of the contract is Pending Approval before aborting the approval process
5232 IF (l_contract_status = G_STATUS_PENDING_APPROVAL) THEN
5233
5234 -- Call WF API to abort the approval process
5235 WF_ENGINE.AbortProcess(
5236 itemtype => l_wf_type,
5237 itemkey => l_wf_key,
5238 result => 'COMPLETE:',
5239 verify_lock => false,
5240 cascade => true);
5241
5242 ELSE
5243
5244 -- Show an error message
5245 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
5246 p_msg_name => G_CANEL_APPROVAL_ERROR_MSG,
5247 p_token1 => G_CONTRACT_NUM_TOKEN,
5248 p_token1_value => l_contract_number);
5249 RAISE FND_API.G_EXC_ERROR;
5250
5251 END IF;
5252
5253
5254 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5255 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5256 'Calling OKC_REP_UTIL_PVT.change_contract_status');
5257 END IF;
5258
5259 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
5260 OKC_REP_UTIL_PVT.change_contract_status(
5261 p_api_version => 1.0,
5262 p_init_msg_list => FND_API.G_FALSE,
5263 p_contract_id => p_contract_id,
5264 p_contract_version => p_contract_version,
5265 p_status_code => G_STATUS_DRAFT,
5266 p_user_id => fnd_global.user_id,
5267 p_note => NULL,
5268 x_msg_data => x_msg_data,
5269 x_msg_count => x_msg_count,
5270 x_return_status => x_return_status);
5271 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5272 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5273 'OKC_REP_UTIL_PVT.change_contract_status return status is: '
5274 || x_return_status);
5275 END IF;
5276 -----------------------------------------------------
5277 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5278 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5279 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5280 RAISE OKC_API.G_EXCEPTION_ERROR;
5281 END IF;
5282 --------------------------------------------------------
5283
5284
5285 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5286 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5287 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
5288 END IF;
5289
5290 -- Add a record in ONC_REP_CON_APPROVALS table.
5291 OKC_REP_UTIL_PVT.add_approval_hist_record(
5292 p_api_version => 1.0,
5293 p_init_msg_list => FND_API.G_FALSE,
5294 p_contract_id => p_contract_id,
5295 p_contract_version => p_contract_version,
5296 p_action_code => G_ACTION_ABORTED,
5297 p_user_id => fnd_global.user_id,
5298 p_note => NULL,
5299 x_msg_data => x_msg_data,
5300 x_msg_count => x_msg_count,
5301 x_return_status => x_return_status);
5302 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5303 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5304 'OKC_REP_UTIL_PVT.add_approval_hist_record return status is: '
5305 || x_return_status);
5306 END IF;
5307 -------------------------------------------------------
5308 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5309 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5310 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5311 RAISE OKC_API.G_EXCEPTION_ERROR;
5312 END IF;
5313 --------------------------------------------------------
5314
5315 COMMIT WORK;
5316
5317 -- Standard call to get message count and if count is 1, get message info.
5318 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
5319
5320 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5321 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5322 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.cancel_approval');
5323 END IF;
5324
5325 EXCEPTION
5326 WHEN FND_API.G_EXC_ERROR THEN
5327 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5328 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
5329 g_module || l_api_name,
5330 'Leaving cancel_approval:FND_API.G_EXC_ERROR Exception');
5331 END IF;
5332 --close cursors
5333 IF (contract_csr%ISOPEN) THEN
5334 CLOSE contract_csr ;
5335 END IF;
5336 ROLLBACK TO submit_contract_PVT;
5337 x_return_status := FND_API.G_RET_STS_ERROR;
5338 FND_MSG_PUB.Count_And_Get(
5339 p_count => x_msg_count,
5340 p_data => x_msg_data
5341 );
5342
5343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5344 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5345 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
5346 g_module || l_api_name,
5347 'Leaving cancel_approval:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
5348 END IF;
5349 --close cursors
5350 IF (contract_csr%ISOPEN) THEN
5351 CLOSE contract_csr ;
5352 END IF;
5353 ROLLBACK TO submit_contract_PVT;
5354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5355 FND_MSG_PUB.Count_And_Get(
5356 p_count => x_msg_count,
5357 p_data => x_msg_data
5358 );
5359
5360 WHEN OTHERS THEN
5361 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5362 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
5363 g_module || l_api_name,
5364 'Leaving cancel_approval because of EXCEPTION: ' || sqlerrm);
5365 END IF;
5366 --close cursors
5367 IF (contract_csr%ISOPEN) THEN
5368 CLOSE contract_csr ;
5369 END IF;
5370 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
5371 p_msg_name => G_UNEXPECTED_ERROR,
5372 p_token1 => G_SQLCODE_TOKEN,
5373 p_token1_value => sqlcode,
5374 p_token2 => G_SQLERRM_TOKEN,
5375 p_token2_value => sqlerrm);
5376 ROLLBACK TO submit_contract_PVT;
5377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5378 FND_MSG_PUB.Count_And_Get(
5379 p_count => x_msg_count,
5380 p_data => x_msg_data
5381 );
5382
5383 END cancel_approval;
5384
5385 -------------------------------------------------------------------------
5386 -------------------------------------------------------------------------
5387 -- This following API is not used anywhere now but this will be used once we take up
5388 -- the FND Document Sequence for auto generating contract number. Currently
5389 -- it is not used because the FND Document Sequence is not supporting multi-org.
5390 -------------------------------------------------------------------------
5391 -------------------------------------------------------------------------
5392 --API name : get_next_contract_number
5393 --Type : Private.
5394 --Function : Gets next available number to use for a contract number
5395 -- using FND Document Sequencing.
5396 --Pre-reqs : None.
5397 --Parameters :
5398 --IN : p_api_version IN NUMBER Required
5399 -- : p_init_msg_list IN VARCHAR2 Optional
5400 -- Default = FND_API.G_FALSE
5401 -- : p_contract_number IN VARCHAR2 Optional
5402 -- Number of the contract
5403 -- : p_org_id IN NUMBER Required
5404 -- Id of the contract organization
5405 -- : p_info_only IN VARCHAR2 Optional
5406 -- Default = 'N'
5407 --OUT : x_contract_number OUT NUMBER
5408 -- : x_auto_number_enabled OUT VARCHAR2(1)
5409 -- : x_return_status OUT VARCHAR2(1)
5410 -- : x_msg_count OUT NUMBER
5411 -- : x_msg_data OUT VARCHAR2(2000)
5412 --Note :
5413 -- End of comments
5414 PROCEDURE get_next_contract_number(
5415 p_api_version IN NUMBER,
5416 p_init_msg_list IN VARCHAR2,
5417 p_contract_number IN VARCHAR2 := NULL,
5418 p_org_id IN NUMBER,
5419 p_info_only IN VARCHAR2,
5420 x_contract_number OUT NOCOPY NUMBER,
5421 x_auto_number_enabled OUT NOCOPY VARCHAR2,
5422 x_return_status OUT NOCOPY VARCHAR2,
5423 x_msg_count OUT NOCOPY NUMBER,
5424 x_msg_data OUT NOCOPY VARCHAR2)
5425 IS
5426 l_api_name VARCHAR2(30);
5427 l_api_version NUMBER;
5428 l_doc_category_code FND_DOC_SEQUENCE_CATEGORIES.CODE%TYPE;
5429 l_set_Of_Books_id NUMBER;
5430 l_db_sequence_name FND_DOCUMENT_SEQUENCES.DB_SEQUENCE_NAME%TYPE;
5431 l_doc_sequence_type FND_DOCUMENT_SEQUENCES.TYPE%TYPE;
5432 l_doc_sequence_name FND_DOCUMENT_SEQUENCES.NAME%TYPE;
5433 l_seqassid FND_DOC_SEQUENCE_ASSIGNMENTS.DOC_SEQUENCE_ASSIGNMENT_ID%TYPE;
5434 l_Prd_Tbl_Name FND_DOCUMENT_SEQUENCES.TABLE_NAME%TYPE;
5435 l_Aud_Tbl_Name FND_DOCUMENT_SEQUENCES.AUDIT_TABLE_NAME%TYPE;
5436 l_Msg_Flag FND_DOCUMENT_SEQUENCES.MESSAGE_FLAG%TYPE;
5437 l_doc_sequence_value NUMBER;
5438 l_doc_sequence_id NUMBER;
5439 l_profile_doc_seq VARCHAR2(1);
5440 l_result NUMBER;
5441 l_row_notfound BOOLEAN := FALSE;
5442 l_contract_number OKC_REP_CONTRACTS_ALL.CONTRACT_NUMBER%TYPE;
5443
5444
5445 CURSOR l_get_sob_csr IS
5446 SELECT OI2.ORG_INFORMATION3 SET_OF_BOOKS_ID
5447 FROM HR_ORGANIZATION_INFORMATION OI1,
5448 HR_ORGANIZATION_INFORMATION OI2,
5449 HR_ALL_ORGANIZATION_UNITS OU
5450 WHERE OI1.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
5451 OI2.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
5452 OI1.ORG_INFORMATION_CONTEXT = 'CLASS' AND
5453 OI2.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND
5454 OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'AND
5455 OI1.ORGANIZATION_ID = p_org_id;
5456
5457 CURSOR l_ensure_unique_csr (p_contract_number IN VARCHAR2) IS
5458 SELECT CONTRACT_NUMBER
5459 FROM OKC_REP_CONTRACTS_ALL
5460 WHERE CONTRACT_NUMBER = p_contract_number
5461 AND ROWNUM < 2;
5462
5463 BEGIN
5464
5465 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5466 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5467 'Entered OKC_REP_CONTRACT_PROCESS_PVT.get_next_contract_number');
5468 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5469 'Org Id is: ' || p_org_id);
5470 END IF;
5471 l_api_name := 'get_next_contract_number';
5472 l_api_version := 1.0;
5473
5474 -- Standard Start of API savepoint
5475 SAVEPOINT submit_contract_PVT;
5476
5477 -- Standard call to check for call compatibility.
5478 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
5479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5480 END IF;
5481
5482 -- Initialize message list if p_init_msg_list is set to TRUE.
5483 IF FND_API.to_Boolean( p_init_msg_list ) THEN
5484 FND_MSG_PUB.initialize;
5485 END IF;
5486
5487 -- Initialize API return status to success
5488 x_return_status := FND_API.G_RET_STS_SUCCESS;
5489
5490 -- Get value of the profile option "Sequential Numbering"
5491 l_profile_doc_seq := fnd_profile.value('UNIQUE:SEQ_NUMBERS');
5492
5493 IF p_info_only = 'Y' AND
5494 l_profile_doc_seq = 'N' THEN
5495 x_auto_number_enabled := FND_API.G_FALSE;
5496 return;
5497 END IF;
5498
5499
5500 OPEN l_get_sob_csr;
5501 FETCH l_get_sob_csr into l_set_of_books_id;
5502 l_row_notfound := l_get_sob_csr%NOTFOUND;
5503 CLOSE l_get_sob_csr;
5504
5505 IF l_row_notfound THEN
5506 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5507 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
5508 'Set of book id not found');
5509 END IF;
5510
5511 CLOSE l_get_sob_csr;
5512 RAISE FND_API.G_EXC_ERROR ;
5513 END IF;
5514 l_row_notfound := FALSE;
5515 l_doc_category_code := substr(Fnd_Profile.Value('OKC_REP_CON_NUM_DOC_SEQ_CATEGORY'),1,30);
5516
5517 l_result := fnd_seqnum.get_seq_info(
5518 app_id => 510 ,
5519 cat_code => l_doc_category_code,
5520 sob_id => l_set_of_books_id,
5521 met_code => NULL,
5522 trx_date => sysdate,
5523 docseq_id => l_doc_sequence_id,
5524 docseq_type => l_doc_sequence_type,
5525 docseq_name => l_doc_sequence_name,
5526 db_seq_name => l_db_sequence_name,
5527 seq_ass_id => l_seqassid,
5528 prd_tab_name => l_Prd_Tbl_Name,
5529 aud_tab_name => l_Aud_Tbl_Name,
5530 msg_flag => l_msg_flag,
5531 suppress_error => 'N' ,
5532 suppress_warn => 'Y');
5533
5534 IF l_result <> FND_SEQNUM.SEQSUCC THEN
5535 RAISE FND_API.G_EXC_ERROR;
5536 END IF;
5537
5538 IF p_info_only = 'Y' THEN
5539 IF l_doc_sequence_type <> 'M' THEN
5540 x_auto_number_enabled := FND_API.G_TRUE;
5541 ELSE
5542 x_auto_number_enabled := FND_API.G_FALSE;
5543 END IF;
5544 return;
5545 END IF;
5546
5547
5548 IF ( l_doc_sequence_type <> 'M') THEN
5549 l_result := fnd_seqnum.get_seq_val(
5550 app_id => 510,
5551 cat_code => l_doc_category_code,
5552 sob_id => l_set_of_books_id,
5553 met_code => null,
5554 trx_date => sysdate,
5555 seq_val => l_doc_sequence_value,
5556 docseq_id => l_doc_sequence_id);
5557
5558 IF l_result <> 0 THEN
5559 RAISE FND_API.G_EXC_ERROR;
5560 ELSE
5561 x_contract_number := TO_CHAR(l_doc_sequence_value);
5562 END IF;
5563
5564 OPEN l_ensure_unique_csr (x_contract_number);
5565 FETCH l_ensure_unique_csr into l_contract_number;
5566 l_row_notfound := l_ensure_unique_csr%NOTFOUND;
5567 CLOSE l_ensure_unique_csr;
5568
5569 IF l_row_notfound THEN
5570 NULL; -- dups do not exist.
5571 ELSE
5572 -- Show duplicate Contract number error message
5573 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
5574 p_msg_name => G_INVALID_CONTRACT_NUMBER_MSG);
5575 RAISE FND_API.G_EXC_ERROR;
5576
5577 END IF;
5578
5579 ELSIF (l_doc_sequence_type = 'M') THEN
5580 x_contract_number := p_contract_number;
5581 END IF;
5582
5583
5584 -- Standard call to get message count and if count is 1, get message info.
5585 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
5586
5587 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5588 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
5589 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.get_next_contract_number');
5590 END IF;
5591
5592 EXCEPTION
5593 WHEN FND_API.G_EXC_ERROR THEN
5594 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5595 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
5596 g_module || l_api_name,
5597 'Leaving get_next_contract_number:FND_API.G_EXC_ERROR Exception');
5598 END IF;
5599 --close cursors
5600 IF (l_ensure_unique_csr%ISOPEN) THEN
5601 CLOSE l_ensure_unique_csr ;
5602 END IF;
5603
5604 IF (l_get_sob_csr%ISOPEN) THEN
5605 CLOSE l_get_sob_csr ;
5606 END IF;
5607
5608 ROLLBACK TO submit_contract_PVT;
5609 x_return_status := FND_API.G_RET_STS_ERROR;
5610 FND_MSG_PUB.Count_And_Get(
5611 p_count => x_msg_count,
5612 p_data => x_msg_data
5613 );
5614
5615 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5616 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5617 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
5618 g_module || l_api_name,
5619 'Leaving get_next_contract_number:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
5620 END IF;
5621 --close cursors
5622 IF (l_ensure_unique_csr%ISOPEN) THEN
5623 CLOSE l_ensure_unique_csr ;
5624 END IF;
5625
5626 IF (l_get_sob_csr%ISOPEN) THEN
5627 CLOSE l_get_sob_csr ;
5628 END IF;
5629
5630 ROLLBACK TO submit_contract_PVT;
5631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5632 FND_MSG_PUB.Count_And_Get(
5633 p_count => x_msg_count,
5634 p_data => x_msg_data
5635 );
5636
5637 WHEN OTHERS THEN
5638 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5639 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
5640 g_module || l_api_name,
5641 'Leaving get_next_contract_number because of EXCEPTION: ' || sqlerrm);
5642 END IF;
5643 --close cursors
5644 IF (l_ensure_unique_csr%ISOPEN) THEN
5645 CLOSE l_ensure_unique_csr ;
5646 END IF;
5647
5648 IF (l_get_sob_csr%ISOPEN) THEN
5649 CLOSE l_get_sob_csr ;
5650 END IF;
5651
5652 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
5653 p_msg_name => G_UNEXPECTED_ERROR,
5654 p_token1 => G_SQLCODE_TOKEN,
5655 p_token1_value => sqlcode,
5656 p_token2 => G_SQLERRM_TOKEN,
5657 p_token2_value => sqlerrm);
5658 ROLLBACK TO submit_contract_PVT;
5659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5660 FND_MSG_PUB.Count_And_Get(
5661 p_count => x_msg_count,
5662 p_data => x_msg_data
5663 );
5664
5665 END get_next_contract_number;
5666
5667
5668
5669 END OKC_REP_CONTRACT_PROCESS_PVT;