[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_CLOSEOUT_PVT
Source
1 PACKAGE BODY okc_rep_closeout_pvt AS
2 /* $Header: OKCPREPCB.pls 120.0.12020000.18 2013/05/07 08:59:39 kkolukul noship $ */
3
4
5 /* Copy Deliverables from Checklist Template to Closeout Contract.
6 Here we need to copy only deliverables to the contract*/
7 PROCEDURE copy_deliverables( p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2,
9 p_commit IN VARCHAR2,
10 p_source_contract_type IN VARCHAR2 ,
11 p_source_contract_id IN NUMBER,
12 p_target_contract_type IN VARCHAR2,
13 p_target_contract_id IN NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2)
17
18 IS
19
20 l_api_name VARCHAR2(30);
21 l_api_version NUMBER;
22 l_internal_party_id OKC_REP_CONTRACT_PARTIES.PARTY_ID%TYPE;
23
24
25 CURSOR target_contract_csr IS
26 SELECT contract_type, owner_id, contract_number
27 FROM OKC_REP_CONTRACTS_ALL
28 WHERE contract_id = p_target_contract_id;
29
30 target_contract_rec target_contract_csr%ROWTYPE;
31
32 CURSOR party_csr IS
33 SELECT party_id
34 FROM OKC_REP_CONTRACT_PARTIES
35 WHERE contract_id = p_target_contract_id
36 AND party_role_code = 'INTERNAL_ORG';
37
38 BEGIN
39
40 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
41 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
42 'Entered OKC_REP_CONTRACT_PROCESS_PVT.copy_deliverables');
43 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
44 'Source Contract Id is: ' || p_source_contract_id);
45 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
46 'Target Contract Id is: ' || p_target_contract_id);
47 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
48 'Source Contract Type is: ' || p_source_contract_type);
49 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
50 'Target Contract Type is: ' || p_target_contract_type);
51 END IF;
52 l_api_name := 'copy_deliverables';
53 l_api_version := 1.0;
54 -- Standard Start of API savepoint
55 SAVEPOINT copy_deliverables_PVT;
56 -- Standard call to check for call compatibility.
57 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list ) THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64
65 -- Initialize API return status to success
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68
69 -- Get contract_type of target contract, required for deliverables and documents APIs
70 OPEN target_contract_csr;
71 FETCH target_contract_csr INTO target_contract_rec;
72 IF(target_contract_csr%NOTFOUND) THEN
73 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
74 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
75 G_MODULE||l_api_name,
76 'Invalid Contract Id: '|| p_source_contract_id);
77 END IF;
78 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
79 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
80 p_token1 => G_CONTRACT_ID_TOKEN,
81 p_token1_value => to_char(p_source_contract_id));
82 RAISE FND_API.G_EXC_ERROR;
83 END IF;
84
85 -- Get internal party_id. Needed for deliverables api
86 OPEN party_csr;
87 FETCH party_csr INTO l_internal_party_id;
88 IF(party_csr%NOTFOUND) THEN
89 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
90 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
91 G_MODULE||l_api_name,
92 'No internal party for the contract');
93 END IF;
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96
97
98 OKC_DELIVERABLE_PROCESS_PVT.copy_deliverables (
99 p_api_version => 1.0,
100 p_init_msg_list => FND_API.G_FALSE,
101 p_source_doc_id => p_source_contract_id,
102 p_source_doc_type => p_source_contract_type,
103 p_target_doc_id => p_target_contract_id,
104 p_target_doc_type => p_target_contract_type,
105 p_target_doc_number => target_contract_rec.contract_number,
106 p_internal_party_id => l_internal_party_id,
107 p_internal_contact_id => target_contract_rec.owner_id,
108 p_carry_forward_ext_party_yn => 'Y',
109 p_carry_forward_int_contact_yn => 'Y',
110 p_reset_fixed_date_yn => 'Y',
111 x_return_status => x_return_status,
112 x_msg_count => x_msg_count,
113 x_msg_data => x_msg_data
114 );
115
116 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
117 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
118 'OKC_DELIVERABLE_PROCESS_PVT.copy_deliverables return status is : '
119 || x_return_status);
120 END IF;
121 -----------------------------------------------------
122 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
123 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
124 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
125 RAISE OKC_API.G_EXCEPTION_ERROR;
126 END IF;
127 --------------------------------------------------------
128
129 -- close open cursors
130 CLOSE target_contract_csr;
131 CLOSE party_csr;
132 -- Standard check of p_commit
133 IF FND_API.To_Boolean( p_commit ) THEN
134 COMMIT WORK;
135 END IF;
136
137 -- Standard call to get message count and if count is 1, get message info.
138 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
139
140 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
142 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_deliverables');
143 END IF;
144
145
146 EXCEPTION
147 WHEN FND_API.G_EXC_ERROR THEN
148 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
149 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
150 g_module || l_api_name,
151 'Leaving copy_deliverables:FND_API.G_EXC_ERROR Exception');
152 END IF;
153 --close cursors
154 IF (target_contract_csr%ISOPEN) THEN
155 CLOSE target_contract_csr ;
156 END IF;
157 IF (party_csr%ISOPEN) THEN
158 CLOSE party_csr ;
159 END IF;
160 ROLLBACK TO copy_deliverables_PVT;
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 FND_MSG_PUB.Count_And_Get(
163 p_count => x_msg_count,
164 p_data => x_msg_data
165 );
166
167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
168 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
169 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
170 g_module || l_api_name,
171 'Leaving copy_deliverables:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
172 END IF;
173 --close cursors
174 IF (target_contract_csr%ISOPEN) THEN
175 CLOSE target_contract_csr ;
176 END IF;
177 IF (party_csr%ISOPEN) THEN
178 CLOSE party_csr ;
179 END IF;
180 ROLLBACK TO copy_deliverables_PVT;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MSG_PUB.Count_And_Get(
183 p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186
187 WHEN OTHERS THEN
188 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
189 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
190 g_module || l_api_name,
191 'Leaving copy_deliverables because of EXCEPTION: ' || sqlerrm);
192 END IF;
193 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
194 p_msg_name => G_UNEXPECTED_ERROR,
195 p_token1 => G_SQLCODE_TOKEN,
196 p_token1_value => sqlcode,
197 p_token2 => G_SQLERRM_TOKEN,
198 p_token2_value => sqlerrm);
199 ROLLBACK TO copy_deliverables_PVT;
200 --close cursors
201 IF (target_contract_csr%ISOPEN) THEN
202 CLOSE target_contract_csr ;
203 END IF;
204 IF (party_csr%ISOPEN) THEN
205 CLOSE party_csr ;
206 END IF;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 FND_MSG_PUB.Count_And_Get(
209 p_count => x_msg_count,
210 p_data => x_msg_data
211 );
212
213 END copy_deliverables;
214
215 /* Revise checklist create a new version of the existing Contract and
216 copies the deliverables to teh new version.
217 It will also make an entry into the okc_contract_usages table*/
218
219 PROCEDURE revise_checklist(p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2,
221 p_commit IN VARCHAR2,
222 p_contract_type IN VARCHAR2 ,
223 p_contract_id IN NUMBER,
224 p_contract_version_num IN NUMBER,
225 x_new_version_num OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_return_status OUT NOCOPY VARCHAR2)
229
230 IS
231
232 l_api_name VARCHAR2(30);
233 l_api_version NUMBER;
234 l_internal_party_id OKC_REP_CONTRACT_PARTIES.PARTY_ID%TYPE;
235
236 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
237
238 CURSOR c_get_doc_details IS
239 SELECT business_document_type, business_document_id
240 FROM okc_rep_contract_usages
241 WHERE contract_type = p_contract_type
242 AND contract_id = p_contract_id
243 AND contract_version = p_contract_version_num;
244
245 BEGIN
246
247 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
249 'Entered OKC_REP_CONTRACT_PROCESS_PVT.revise_checklist');
250 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
251 ' Contract Id is: ' || p_contract_id);
252 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
253 ' Contract Type is: ' || p_contract_type);
254 END IF;
255
256 l_api_name := 'revise_checklist';
257 l_api_version := 1.0;
258
259 -- Standard Start of API savepoint
260 SAVEPOINT revise_checklist_PVT;
261
262 -- Standard call to check for call compatibility.
263 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266
267 -- Initialize message list if p_init_msg_list is set to TRUE.
268 IF FND_API.to_Boolean( p_init_msg_list ) THEN
269 FND_MSG_PUB.initialize;
270 END IF;
271
272 -- Initialize API return status to success
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274
275 -- Get business document details to which this closeout Contract is associated
276 OPEN c_get_doc_details;
277 FETCH c_get_doc_details INTO l_document_rec.business_document_type, l_document_rec.business_document_id;
278 IF(c_get_doc_details%NOTFOUND) THEN
279 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
281 G_MODULE||l_api_name,
282 'Invalid Contract Id: '|| p_contract_id);
283 END IF;
284 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
285 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
286 p_token1 => G_CONTRACT_ID_TOKEN,
287 p_token1_value => to_char(p_contract_id));
288 RAISE FND_API.G_EXC_ERROR;
289 END IF;
290
291 create_new_contract_version( p_api_version => p_api_version,
292 p_document_rec => l_document_rec,
293 p_contract_type => p_contract_type,
294 p_contract_id => p_contract_id,
295 p_commit => p_commit,
296 x_msg_data => x_msg_data,
297 x_msg_count => x_msg_count,
298 x_return_status => x_return_status);
299
300 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
301 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
302 'OKC_REP_CONTRACT_IMP_PVT.create_new_contract_version return status is : '
303 || x_return_status);
304 END IF;
305 -----------------------------------------------------
306 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
307 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
308 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
309 RAISE OKC_API.G_EXCEPTION_ERROR;
310 END IF;
311 --------------------------------------------------------
312
313 -- close open cursors
314 CLOSE c_get_doc_details;
315
316 IF x_return_status = 'S' THEN
317 SELECT contract_version_num INTO x_new_version_num
318 FROM okc_rep_contracts_all
319 WHERE contract_id = p_contract_id
320 AND contract_type = p_contract_type;
321 END IF;
322
323 -- Standard check of p_commit
324 IF FND_API.To_Boolean( p_commit ) THEN
325 COMMIT WORK;
326 END IF;
327
328 -- Standard call to get message count and if count is 1, get message info.
329 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
330
331 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
332 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
333 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.revise_checklist');
334 END IF;
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
339 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
340 g_module || l_api_name,
341 'Leaving revise_checklist:FND_API.G_EXC_ERROR Exception');
342 END IF;
343 --close cursors
344 IF (c_get_doc_details%ISOPEN) THEN
345 CLOSE c_get_doc_details ;
346 END IF;
347 ROLLBACK TO revise_checklist_PVT;
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 FND_MSG_PUB.Count_And_Get(
350 p_count => x_msg_count,
351 p_data => x_msg_data
352 );
353
354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
355 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
356 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
357 g_module || l_api_name,
358 'Leaving revise_checklist:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
359 END IF;
360 --close cursors
361 IF (c_get_doc_details%ISOPEN) THEN
362 CLOSE c_get_doc_details ;
363 END IF;
364
365 ROLLBACK TO revise_checklist_PVT;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 FND_MSG_PUB.Count_And_Get(
368 p_count => x_msg_count,
369 p_data => x_msg_data
370 );
371
372 WHEN OTHERS THEN
373 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
374 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
375 g_module || l_api_name,
376 'Leaving revise_checklist because of EXCEPTION: ' || sqlerrm);
377 END IF;
378 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
379 p_msg_name => G_UNEXPECTED_ERROR,
380 p_token1 => G_SQLCODE_TOKEN,
381 p_token1_value => sqlcode,
382 p_token2 => G_SQLERRM_TOKEN,
383 p_token2_value => sqlerrm);
384 ROLLBACK TO revise_checklist_PVT;
385 --close cursors
386 IF (c_get_doc_details%ISOPEN) THEN
387 CLOSE c_get_doc_details ;
388 END IF;
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
390 FND_MSG_PUB.Count_And_Get(
391 p_count => x_msg_count,
392 p_data => x_msg_data
393 );
394
395 END revise_checklist;
396
397 /*This API marks the deliverable status to signed and activates the deliverables for
398 the given contract. Validate of deliverables shd be called before this step to ensure
399 the deliverables are all ready for activation. From UI if validation fails we shd show
400 errors on the Ui.For no errors it will proceed here.*/
401
402 PROCEDURE freeze_checklist(p_api_version IN NUMBER,
403 p_init_msg_list IN VARCHAR2,
404 p_commit IN VARCHAR2,
405 p_contract_type IN VARCHAR2 ,
406 p_contract_id IN NUMBER,
407 p_contract_version_num IN NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_return_status OUT NOCOPY VARCHAR2)
411
412 IS
413
414 l_api_name VARCHAR2(30);
415 l_api_version NUMBER;
416 l_internal_party_id OKC_REP_CONTRACT_PARTIES.PARTY_ID%TYPE;
417 l_physical_completion_date DATE;
418 l_task_status VARCHAR2(30);
419 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
420
421 CURSOR c_get_doc_details IS
422 SELECT orcu.business_document_type, orcu.business_document_id,
423 orca.contract_effective_date
424 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
425 WHERE orca.contract_id = p_contract_id
426 AND orca.contract_type = orcu.contract_type
427 AND orcu.contract_version = orca.contract_version_num
428 AND orca.contract_id = orcu.contract_id;
429
430 BEGIN
431
432 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
433 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
434 'Entered OKC_REP_CONTRACT_PROCESS_PVT.freeze_checklist');
435 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
436 ' Contract Id is: ' || p_contract_id);
437 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
438 ' Contract Type is: ' || p_contract_type);
439 END IF;
440
441 l_api_name := 'freeze_checklist';
442 l_api_version := 1.0;
443
444 -- Standard Start of API savepoint
445 SAVEPOINT freeze_checklist_PVT;
446
447 -- Standard call to check for call compatibility.
448 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 -- Initialize message list if p_init_msg_list is set to TRUE.
453 IF FND_API.to_Boolean( p_init_msg_list ) THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456
457 -- Initialize API return status to success
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459
460 -- Get business document details to which this closeout Contract is associated
461 OPEN c_get_doc_details;
462 FETCH c_get_doc_details INTO l_document_rec.business_document_type, l_document_rec.business_document_id, l_physical_completion_date;
463 IF(c_get_doc_details%NOTFOUND) THEN
464 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
465 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
466 G_MODULE||l_api_name,
467 'Invalid Contract Id: '|| p_contract_id);
468 END IF;
469 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
470 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
471 p_token1 => G_CONTRACT_ID_TOKEN,
472 p_token1_value => to_char(p_contract_id));
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475
476 Activate_tasks_closeout(p_api_version => 1.0,
477 p_document_rec => l_document_rec,
478 p_physical_completion_date => l_physical_completion_date,
479 p_init_msg_list => p_init_msg_list,
480 p_commit => p_commit,
481 x_task_status => l_task_status,
482 x_msg_data => x_msg_data,
483 x_msg_count => x_msg_count,
484 x_return_status => x_return_status);
485 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
486 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
487 'OKC_REP_CONTRACT_IMP_PVT.Activate_tasks_closeout return status is : '
488 || x_return_status);
489 END IF;
490 -----------------------------------------------------
491 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
492 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
493 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
494 RAISE OKC_API.G_EXCEPTION_ERROR;
495 END IF;
496 --------------------------------------------------------
497
498 -- close open cursors
499 CLOSE c_get_doc_details;
500
501 -- Standard check of p_commit
502 IF FND_API.To_Boolean( p_commit ) THEN
503 COMMIT WORK;
504 END IF;
505
506 -- Standard call to get message count and if count is 1, get message info.
507 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
508
509 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
511 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.freeze_checklist');
512 END IF;
513
514 EXCEPTION
515 WHEN FND_API.G_EXC_ERROR 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 freeze_checklist:FND_API.G_EXC_ERROR Exception');
520 END IF;
521 --close cursors
522 IF (c_get_doc_details%ISOPEN) THEN
523 CLOSE c_get_doc_details ;
524 END IF;
525 ROLLBACK TO freeze_checklist_PVT;
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 FND_MSG_PUB.Count_And_Get(
528 p_count => x_msg_count,
529 p_data => x_msg_data
530 );
531
532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
535 g_module || l_api_name,
536 'Leaving freeze_checklist:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
537 END IF;
538 --close cursors
539 IF (c_get_doc_details%ISOPEN) THEN
540 CLOSE c_get_doc_details ;
541 END IF;
542
543 ROLLBACK TO freeze_checklist_PVT;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 FND_MSG_PUB.Count_And_Get(
546 p_count => x_msg_count,
547 p_data => x_msg_data
548 );
549
550 WHEN OTHERS THEN
551 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
552 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
553 g_module || l_api_name,
554 'Leaving freeze_checklist because of EXCEPTION: ' || sqlerrm);
555 END IF;
556 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
557 p_msg_name => G_UNEXPECTED_ERROR,
558 p_token1 => G_SQLCODE_TOKEN,
559 p_token1_value => sqlcode,
560 p_token2 => G_SQLERRM_TOKEN,
561 p_token2_value => sqlerrm);
562 ROLLBACK TO freeze_checklist_PVT;
563 --close cursors
564 IF (c_get_doc_details%ISOPEN) THEN
565 CLOSE c_get_doc_details ;
566 END IF;
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 FND_MSG_PUB.Count_And_Get(
569 p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572
573 END freeze_checklist;
574
575 PROCEDURE validate_closeout_tasks(p_api_version IN NUMBER,
576 p_init_msg_list IN VARCHAR2,
577 p_commit IN VARCHAR2,
578 p_contract_type IN VARCHAR2 ,
579 p_contract_id IN NUMBER,
580 p_contract_version_num IN NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_return_status OUT NOCOPY VARCHAR2,
584 x_qa_return_status OUT NOCOPY VARCHAR2,
585 x_sequence_id OUT NOCOPY NUMBER)
586
587 IS
588
589 l_api_name VARCHAR2(30);
590 l_api_version NUMBER;
591 l_internal_party_id OKC_REP_CONTRACT_PARTIES.PARTY_ID%TYPE;
592 l_error_found Boolean := FALSE;
593 l_warning_found Boolean := FALSE;
594 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
595 l_bus_doc_date_events_tbl OKC_TERMS_QA_GRP.BUSDOCDATES_TBL_TYPE;
596 l_sequence_id NUMBER;
597 l_qa_result_tbl OKC_TERMS_QA_PVT.qa_result_tbl_type;
598
599 CURSOR c_get_doc_details IS
600 SELECT orcu.business_document_type, orcu.business_document_id,
601 orca.contract_effective_date
602 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
603 WHERE orca.contract_id = p_contract_id
604 AND orca.contract_type = orcu.contract_type
605 AND orcu.contract_version = orca.contract_version_num
606 AND orca.contract_id = orcu.contract_id;
607
608 contract_rec c_get_doc_details%ROWTYPE;
609
610 BEGIN
611
612 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
613 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
614 'Entered OKC_REP_CONTRACT_PROCESS_PVT.validate_closeout_tasks');
615 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
616 ' Contract Id is: ' || p_contract_id);
617 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
618 ' Contract Type is: ' || p_contract_type);
619 END IF;
620
621 l_api_name := 'validate_closeout_tasks';
622 l_api_version := 1.0;
623
624 -- Standard Start of API savepoint
625 SAVEPOINT val_closeout_tasks_PVT;
626
627 -- Standard call to check for call compatibility.
628 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 -- Initialize message list if p_init_msg_list is set to TRUE.
633 IF FND_API.to_Boolean( p_init_msg_list ) THEN
634 FND_MSG_PUB.initialize;
635 END IF;
636
637 -- Initialize API return status to success
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639
640 -- Get business document details to which this closeout Contract is associated
641 OPEN c_get_doc_details;
642 FETCH c_get_doc_details INTO contract_rec;
643 IF(c_get_doc_details%NOTFOUND) THEN
644 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
646 G_MODULE||l_api_name,
647 'Invalid Contract Id: '|| p_contract_id);
648 END IF;
649 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
650 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
651 p_token1 => G_CONTRACT_ID_TOKEN,
652 p_token1_value => to_char(p_contract_id));
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656 l_bus_doc_date_events_tbl(1).event_code := G_PO_PHYSICAL_COMPLETE_EVENT;
657 l_bus_doc_date_events_tbl(1).event_date := contract_rec.contract_effective_date;
658
659
660 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
661 p_api_version => 1,
662 p_init_msg_list => FND_API.G_FALSE,
663 p_doc_type => p_contract_type,
664 p_doc_id => p_contract_id,
665 p_mode => G_NORMAL_QA,
666 p_bus_doc_date_events_tbl => l_bus_doc_date_events_tbl,
667 p_qa_result_tbl => l_qa_result_tbl,
668 x_msg_data => x_msg_data,
669 x_msg_count => x_msg_count,
670 x_return_status => x_return_status,
671 x_qa_return_status => x_qa_return_status);
672
673 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
674 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
675 'Completed OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa with returned status: ' || x_return_status);
676 END IF;
677
678 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
679 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
680 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
681 RAISE OKC_API.G_EXCEPTION_ERROR;
682 END IF;
683
684 --------------------------------------------
685 -- VALIDATIONS are done for Deliverables.
686 -- Now insert into Temp table.
687 --------------------------------------------
688 -- Save result from PLSQL table into DB table
689 --------------------------------------------
690 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
692 G_MODULE||l_api_name,'1015: Save result from PLSQL table into DB table');
693 END IF;
694
695
696 -- After calling the validation APIs we need to find out about the x_qa_return_status. We should loop through
697 -- this only if we get
698 IF l_qa_result_tbl.COUNT > 0 THEN
699 FOR i IN l_qa_result_tbl.FIRST..l_qa_result_tbl.LAST LOOP
700 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
701 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
702 'Updating pl/sql table record: ' || i);
703 END IF;
704 l_qa_result_tbl(i).error_record_type_name := okc_util.decode_lookup('OKC_ERROR_RECORD_TYPE',l_qa_result_tbl(i).error_record_type);
705 l_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',l_qa_result_tbl(i).error_severity);
706 l_qa_result_tbl(i).document_type := p_contract_type;
707 l_qa_result_tbl(i).document_id := p_contract_id;
708 l_qa_result_tbl(i).creation_date := sysdate;
709 IF l_qa_result_tbl(i).error_severity = G_QA_STS_ERROR THEN
710 l_error_found := true;
711 END IF;
712 IF l_qa_result_tbl(i).error_severity = G_QA_STS_WARNING THEN
713 l_warning_found := true;
714 END IF;
715
716 END LOOP;
717 IF l_error_found THEN
718 x_qa_return_status := G_QA_STS_ERROR;
719 ELSIF l_warning_found THEN
720 x_qa_return_status := G_QA_STS_WARNING;
721 END IF;
722 END IF; -- l_qa_result_tbl.COUNT > 0 THEN
723
724 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
726 'Calling OKC_TERMS_QA_PVT.Log_QA_Messages');
727 END IF;
728 -- Load eror in the DB table
729 OKC_TERMS_QA_PVT.Log_QA_Messages(
730 x_return_status => x_return_status,
731
732 p_qa_result_tbl => l_qa_result_tbl,
733 x_sequence_id => x_sequence_id
734 );
735 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
736 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
737 'Completed OKC_TERMS_QA_PVT.Log_QA_Messages');
738 END IF;
739 --------------------------------------------
740 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
742 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
743 RAISE FND_API.G_EXC_ERROR ;
744 END IF;
745 --------------------------------------------
746
747 -- close open cursors
748 CLOSE c_get_doc_details;
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.validate_closeout_tasks');
761 END IF;
762
763 EXCEPTION
764 WHEN FND_API.G_EXC_ERROR THEN
765 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
766 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
767 g_module || l_api_name,
768 'Leaving validate_closeout_tasks:FND_API.G_EXC_ERROR Exception');
769 END IF;
770 --close cursors
771 IF (c_get_doc_details%ISOPEN) THEN
772 CLOSE c_get_doc_details ;
773 END IF;
774 ROLLBACK TO val_closeout_tasks_PVT;
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 FND_MSG_PUB.Count_And_Get(
777 p_count => x_msg_count,
778 p_data => x_msg_data
779 );
780
781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
782 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
783 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
784 g_module || l_api_name,
785 'Leaving validate_closeout_tasks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
786 END IF;
787 --close cursors
788 IF (c_get_doc_details%ISOPEN) THEN
789 CLOSE c_get_doc_details ;
790 END IF;
791
792 ROLLBACK TO val_closeout_tasks_PVT;
793
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 FND_MSG_PUB.Count_And_Get(
796 p_count => x_msg_count,
797 p_data => x_msg_data
798 );
799
800 WHEN OTHERS THEN
801 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
802 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
803 g_module || l_api_name,
804 'Leaving validate_closeout_tasks because of EXCEPTION: ' || sqlerrm);
805 END IF;
806 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
807 p_msg_name => G_UNEXPECTED_ERROR,
808 p_token1 => G_SQLCODE_TOKEN,
809 p_token1_value => sqlcode,
810 p_token2 => G_SQLERRM_TOKEN,
811 p_token2_value => sqlerrm);
812
813 ROLLBACK TO val_closeout_tasks_PVT;
814
815 --close cursors
816 IF (c_get_doc_details%ISOPEN) THEN
817 CLOSE c_get_doc_details ;
818 END IF;
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 FND_MSG_PUB.Count_And_Get(
821 p_count => x_msg_count,
822 p_data => x_msg_data
823 );
824
825 END validate_closeout_tasks;
826
827
828 PROCEDURE Activate_tasks_closeout(p_api_version IN NUMBER,
829 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
830 p_physical_completion_date IN DATE ,
831 p_init_msg_list IN VARCHAR2,
832 p_commit IN VARCHAR2,
833 x_task_status OUT NOCOPY VARCHAR2,
834 x_msg_data OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_return_status OUT NOCOPY VARCHAR2
837 )
838 IS
839 l_api_name VARCHAR2(30);
840 l_api_version NUMBER;
841 l_activate_event_tbl okc_rep_contract_process_pvt.EVENT_TBL_TYPE;
842 l_update_event_tbl okc_rep_contract_process_pvt.EVENT_TBL_TYPE;
843 l_sync_flag VARCHAR2(1);
844 l_phy_complete_date_match_flag VARCHAR2(1);
845 l_effective_date_matches_flag VARCHAR2(1);
846
847 l_contract_type VARCHAR2(150);
848 l_contract_id NUMBER;
849 l_del_status VARCHAR2(15);
850
851 CURSOR contract_csr(p_contract_type VARCHAR2, p_contract_id NUMBER) IS
852 SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date
853 FROM OKC_REP_CONTRACTS_ALL
854 WHERE contract_id = p_contract_id
855 AND contract_type = p_contract_type;
856
857 CURSOR c_get_contract_id_csr IS
858 SELECT contract_type, contract_id FROM okc_rep_contract_usages
859 WHERE business_document_id = p_document_rec.business_document_id
860 AND business_document_type = p_document_rec.business_document_type;
861
862 contract_rec contract_csr%ROWTYPE;
863
864 CURSOR c_del_status_csr IS
865 SELECT DELIVERABLE_STATUS FROM okc_deliverables del, okc_rep_contracts_all orca
866 WHERE del.BUSINESS_DOCUMENT_TYPE = orca.contract_type
867 AND del.BUSINESS_DOCUMENT_ID = orca.contract_id
868 AND del.BUSINESS_DOCUMENT_VERSION = orca.contract_version_num
869 AND contract_id = l_contract_id
870 AND del.DELIVERABLE_STATUS <> 'COMPLETED'
871 AND ROWNUM = 1;
872
873 BEGIN
874
875 l_phy_complete_date_match_flag := FND_API.G_FALSE;
876
877 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
878 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
879 'Entered OKC_REP_CLOSEOUT_PVT.Activate_tasks_closeout');
880 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
881 'Business Document Id is: ' || p_document_rec.business_document_id);
882 END IF;
883 l_api_name := 'Activate_tasks_closeout';
884 l_api_version := 1.0;
885
886 -- Standard Start of API savepoint
887 SAVEPOINT Act_tasks_closeout_PVT;
888
889 -- Standard call to check for call compatibility.
890 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
892 END IF;
893 -- Initialize message list if p_init_msg_list is set to TRUE.
894 IF FND_API.to_Boolean( p_init_msg_list ) THEN
895 FND_MSG_PUB.initialize;
896 END IF;
897
898 -- Initialize API return status to success
899 x_return_status := FND_API.G_RET_STS_SUCCESS;
900 x_task_status := G_TASK_STATUS_INIT;
901
902 --find the contract associated to this business document
903 OPEN c_get_contract_id_csr;
904 FETCH c_get_contract_id_csr INTO l_contract_type, l_contract_id;
905 IF(c_get_contract_id_csr%NOTFOUND) THEN
906 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
907 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
908 G_MODULE||l_api_name,
909 'Invalid Business Document Type: '|| p_document_rec.business_document_type);
910 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
911 G_MODULE||l_api_name,
912 'Invalid Business Document Id: '|| p_document_rec.business_document_id);
913
914 END IF;
915 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
916 p_msg_name => G_INVALID_BUS_DOC_ID_MSG,
917 p_token1 => 'BUS_DOC_ID',
918 p_token1_value => to_char(p_document_rec.business_document_id));
919 RAISE FND_API.G_EXC_ERROR;
920 -- RAISE NO_DATA_FOUND;
921 END IF;
922
923 -- Get effective dates and version of the contract.
924 OPEN contract_csr(l_contract_type, l_contract_id);
925 FETCH contract_csr INTO contract_rec;
926 IF(contract_csr%NOTFOUND) THEN
927 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
928 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
929 G_MODULE||l_api_name,
930 'Invalid Contract Id: '|| l_contract_id);
931 END IF;
932 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
933 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
934 p_token1 => 'CONTRACT_ID',
935 p_token1_value => to_char(l_contract_id));
936 RAISE FND_API.G_EXC_ERROR;
937 -- RAISE NO_DATA_FOUND;
938 END IF;
939
940 -- Lock the contract header
941 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
942 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
943 'Calling OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header()');
944 END IF;
945 -- Lock the contract header
946 okc_rep_contract_process_pvt.Lock_Contract_Header(
947 p_contract_id => l_contract_id,
948 p_object_version_number => NULL,
949 x_return_status => x_return_status
950 );
951
952 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
953 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
954 'OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header return status is: '
955 || x_return_status);
956 END IF;
957
958 -----------------------------------------------------
959 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
960 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
961 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
962 RAISE OKC_API.G_EXCEPTION_ERROR;
963 END IF;
964 -----------------------------------------------------
965
966 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
967 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
968 'Calling OKC_REP_UTIL_PVT.change_contract_status');
969 END IF;
970 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
971 OKC_REP_UTIL_PVT.change_contract_status(
972 p_api_version => 1.0,
973 p_init_msg_list => FND_API.G_FALSE,
974 p_contract_id => l_contract_id,
975 p_contract_version => contract_rec.contract_version_num,
976 p_status_code => G_STATUS_SIGNED,
977 p_user_id => fnd_global.user_id,
978 p_note => NULL,
979 x_msg_data => x_msg_data,
980 x_msg_count => x_msg_count,
981 x_return_status => x_return_status);
982
983 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
984 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
985 g_module || l_api_name,
986 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || x_return_status);
987 END IF;
988 -----------------------------------------------------
989 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
990 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
991 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
992 RAISE OKC_API.G_EXCEPTION_ERROR;
993 END IF;
994 ------------------------------------------------------
995
996 -- We need to first version the deliverables
997 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
998 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
999 'Calling OKC_DELIVERABLE_PROCESS_PVT.version_deliverables');
1000 END IF;
1001
1002 OKC_DELIVERABLE_PROCESS_PVT.version_deliverables (
1003 p_api_version => 1.0,
1004 p_init_msg_list => FND_API.G_FALSE,
1005 p_doc_id => l_contract_id,
1006 p_doc_version => contract_rec.contract_version_num,
1007 p_doc_type => contract_rec.contract_type,
1008 x_return_status => x_return_status,
1009 x_msg_count => x_msg_count,
1010 x_msg_data => x_msg_data
1011 );
1012
1013 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1014 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1015 'OKC_DELIVERABLE_PROCESS_PVT.version_deliverables return status is : '
1016 || x_return_status);
1017 END IF;
1018 -----------------------------------------------------
1019 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1020 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1021 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1022 RAISE OKC_API.G_EXCEPTION_ERROR;
1023 END IF;
1024 --------------------------------------------------------
1025
1026 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1027 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1028 'Latest signed version number is : '
1029 || contract_rec.latest_signed_ver_number);
1030 END IF;
1031 -- Now we need to activate deliverables
1032 if (contract_rec.latest_signed_ver_number IS NULL) THEN
1033 l_sync_flag := FND_API.G_FALSE;
1034 ELSE
1035 l_sync_flag := FND_API.G_TRUE;
1036 END IF;
1037
1038 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1039 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1040 'l_sync_flag is : ' || l_sync_flag);
1041 END IF;
1042
1043 l_activate_event_tbl(1).event_code := 'PO_PHYSICAL_COMPLETE';
1044 l_activate_event_tbl(1).event_date := p_physical_completion_date;
1045
1046 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1048 'Calling OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
1049 END IF;
1050
1051 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
1052 p_api_version => 1.0,
1053 p_init_msg_list => FND_API.G_FALSE,
1054 p_commit => FND_API.G_FALSE,
1055 p_bus_doc_id => l_contract_id,
1056 p_bus_doc_type => contract_rec.contract_type,
1057 p_bus_doc_version => contract_rec.contract_version_num,
1058 p_event_code => 'PO_PHYSICAL_COMPLETE',
1059 p_event_date => p_physical_completion_date,
1060 p_sync_flag => l_sync_flag,
1061 p_bus_doc_date_events_tbl => l_activate_event_tbl,
1062 x_msg_data => x_msg_data,
1063 x_msg_count => x_msg_count,
1064 x_return_status => x_return_status);
1065
1066 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1067 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1068 'OKC_DELIVERABLE_PROCESS_PVT.activateDeliverables return status is : '
1069 || x_return_status);
1070 END IF;
1071 -----------------------------------------------------
1072 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1073 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1074 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1075 RAISE OKC_API.G_EXCEPTION_ERROR;
1076 END IF;
1077 --------------------------------------------------------
1078
1079 -- Checking if we need to call deliverable's APIs for synch-ing
1080 IF (l_sync_flag = FND_API.G_TRUE) THEN
1081
1082 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1083 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1084 'Before checking if we need to call updateDeliverable and disableDeliverable()');
1085 END IF;
1086
1087 l_update_event_tbl(1).event_code := 'PO_PHYSICAL_COMPLETE';
1088 l_update_event_tbl(1).event_date := p_physical_completion_date;
1089
1090 -- we need to call deliverables API for synching previous signed deliverables.
1091
1092 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1093 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1094 'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
1095 END IF;
1096 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
1097 p_api_version => 1.0,
1098 p_init_msg_list => FND_API.G_FALSE,
1099 p_commit => FND_API.G_FALSE,
1100 p_bus_doc_id => l_contract_id,
1101 p_bus_doc_type => contract_rec.contract_type,
1102 p_bus_doc_version => contract_rec.contract_version_num,
1103 p_bus_doc_date_events_tbl => l_update_event_tbl,
1104 x_msg_data => x_msg_data,
1105 x_msg_count => x_msg_count,
1106 x_return_status => x_return_status);
1107
1108
1109 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1110 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1111 'OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables return status is : '
1112 || x_return_status);
1113 END IF;
1114 -----------------------------------------------------
1115 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1117 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1118 RAISE OKC_API.G_EXCEPTION_ERROR;
1119 END IF;
1120 --------------------------------------------------------
1121
1122 -- Disable prev. version deliverables
1123 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1124 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1125 'Calling OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables');
1126 END IF;
1127 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
1128 p_api_version => 1.0,
1129 p_init_msg_list => FND_API.G_FALSE,
1130 p_commit => FND_API.G_FALSE,
1131 p_bus_doc_id => l_contract_id,
1132 p_bus_doc_type => contract_rec.contract_type,
1133 p_bus_doc_version => contract_rec.latest_signed_ver_number,
1134 x_msg_data => x_msg_data,
1135 x_msg_count => x_msg_count,
1136 x_return_status => x_return_status);
1137
1138 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1139 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1140 'OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables return status is : '
1141 || x_return_status);
1142 END IF;
1143 -----------------------------------------------------
1144 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1145 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1146 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1147 RAISE OKC_API.G_EXCEPTION_ERROR;
1148 END IF;
1149 --------------------------------------------------------
1150 END IF; -- (l_sync_flag = 'Y')
1151
1152 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1153 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1154 'Updating latest_signed_ver_number column');
1155 END IF;
1156
1157 UPDATE okc_rep_contracts_all
1158 SET latest_signed_ver_number = contract_rec.contract_version_num ,
1159 contract_effective_date = p_physical_completion_date
1160 WHERE contract_id = l_contract_id;
1161 CLOSE contract_csr;
1162
1163 OPEN c_del_status_csr;
1164 FETCH c_del_status_csr INTO l_del_status;
1165 x_task_status := G_TASK_STATUS_INIT;
1166 IF (c_del_status_csr%NOTFOUND) THEN
1167 x_task_status := G_TASK_STATUS_COMP;
1168 END IF;
1169 CLOSE c_del_status_csr;
1170
1171 -- Standard check of p_commit
1172 IF FND_API.To_Boolean( p_commit ) THEN
1173 COMMIT WORK;
1174 END IF;
1175
1176 -- Standard call to get message count and if count is 1, get message info.
1177 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1178
1179 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1181 'Leaving OKC_REP_CLOSEOUT_PVT.Activate_tasks_closeout');
1182 END IF;
1183
1184 EXCEPTION
1185 WHEN FND_API.G_EXC_ERROR THEN
1186 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1187 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1188 g_module || l_api_name,
1189 'Leaving Activate_tasks_closeout:FND_API.G_EXC_ERROR Exception');
1190 END IF;
1191 --close cursors
1192 IF (contract_csr%ISOPEN) THEN
1193 CLOSE contract_csr ;
1194 END IF;
1195 IF (c_get_contract_id_csr%ISOPEN) THEN
1196 CLOSE c_get_contract_id_csr ;
1197 END IF;
1198 IF (c_del_status_csr%ISOPEN) THEN
1199 CLOSE c_del_status_csr ;
1200 END IF;
1201 ROLLBACK TO Act_tasks_closeout_PVT;
1202 x_return_status := FND_API.G_RET_STS_ERROR;
1203 FND_MSG_PUB.Count_And_Get(
1204 p_count => x_msg_count,
1205 p_data => x_msg_data
1206 );
1207
1208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1209 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1210 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1211 g_module || l_api_name,
1212 'Leaving Activate_tasks_closeout:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1213 END IF;
1214 --close cursors
1215 IF (contract_csr%ISOPEN) THEN
1216 CLOSE contract_csr ;
1217 END IF;
1218 IF (c_get_contract_id_csr%ISOPEN) THEN
1219 CLOSE c_get_contract_id_csr ;
1220 END IF;
1221 IF (c_del_status_csr%ISOPEN) THEN
1222 CLOSE c_del_status_csr ;
1223 END IF;
1224 ROLLBACK TO Act_tasks_closeout_PVT;
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 FND_MSG_PUB.Count_And_Get(
1227 p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1230
1231 WHEN OTHERS THEN
1232 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1233 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1234 g_module || l_api_name,
1235 'Leaving sign_contract because of EXCEPTION: ' || sqlerrm);
1236 END IF;
1237 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1238 p_msg_name => G_UNEXPECTED_ERROR,
1239 p_token1 => G_SQLCODE_TOKEN,
1240 p_token1_value => sqlcode,
1241 p_token2 => G_SQLERRM_TOKEN,
1242 p_token2_value => sqlerrm);
1243 ROLLBACK TO Act_tasks_closeout_PVT;
1244 --close cursors
1245 IF (contract_csr%ISOPEN) THEN
1246 CLOSE contract_csr ;
1247 END IF;
1248 IF (c_get_contract_id_csr%ISOPEN) THEN
1249 CLOSE c_get_contract_id_csr ;
1250 END IF;
1251 IF (c_del_status_csr%ISOPEN) THEN
1252 CLOSE c_del_status_csr ;
1253 END IF;
1254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1255 FND_MSG_PUB.Count_And_Get(
1256 p_count => x_msg_count,
1257 p_data => x_msg_data
1258 );
1259 END Activate_tasks_closeout;
1260
1261
1262 Procedure create_contract(p_api_version IN NUMBER,
1263 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1264 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
1265 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
1266 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
1267 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
1268 p_commit IN VARCHAR2 := fnd_api.g_false,
1269 x_msg_data OUT NOCOPY VARCHAR2,
1270 x_msg_count OUT NOCOPY NUMBER,
1271 x_return_status OUT NOCOPY VARCHAR2
1272 ) IS
1273
1274 l_api_name VARCHAR2(50);
1275 l_api_version CONSTANT NUMBER := 1.0;
1276 l_auto_num_doc VARCHAR2(1);
1277 l_auto_num_profile VARCHAR2(1);
1278 l_auto_number_yn VARCHAR2(1);
1279 l_auto_number_option CONSTANT VARCHAR2(30):= 'OKC_REP_AUTO_CON_NUMBER';
1280
1281 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
1282
1283 BEGIN
1284 l_api_name := 'create_contract';
1285
1286 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1287 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1288 'Entered OKC_REP_CLOSEOUT_PVT.'||l_api_name);
1289 END IF;
1290
1291 -- Standard Start of API savepoint
1292 SAVEPOINT create_contract_PVT;
1293
1294 -- Standard call to check for call compatibility.
1295 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298
1299 l_document_rec := p_document_rec;
1300
1301 l_document_rec.source_code := 'INTERNAL';
1302 l_document_rec.relationship_id := 10006;
1303 l_document_rec.display_in_contract := 'N';
1304
1305 --if Autonumbering is enabled then p_contract_rec.contract_number shd be null
1306 --PO will always pass a value. So here we will null it out based on the profile.
1307 --Checking profile option value
1308 BEGIN
1309 select auto_num_enabled_yn INTO l_auto_num_doc
1310 from okc_bus_doc_types_vl
1311 where name = p_contract_rec.contract_type_txt;
1312
1313 FND_PROFILE.GET(NAME => l_auto_number_option, VAL => l_auto_num_profile);
1314
1315 /* Check the autonumbering setup at document level */
1316
1317 IF Nvl(l_auto_num_doc,'N') = 'N' THEN
1318 IF Nvl(l_auto_num_profile , 'N') = 'N' THEN
1319 l_auto_number_yn := 'N';
1320 ELSE
1321 l_auto_number_yn := 'Y';
1322 END IF;
1323 ELSE
1324 l_auto_number_yn := 'Y';
1325 END IF;
1326
1327 IF (l_auto_number_yn = 'Y') THEN
1328 p_contract_rec.contract_number := NULL;
1329 END IF;
1330
1331 EXCEPTION
1332 WHEN No_Data_Found THEN
1333 NULL;
1334 END;
1335
1336 OKC_REP_CONTRACT_IMP_PVT.create_contract( p_api_version => p_api_version,
1337 p_document_rec => l_document_rec,
1338 p_contract_rec => p_contract_rec,
1339 p_contract_parties_tbl => p_contract_parties_tbl,
1340 p_party_contacts_tbl => p_party_contacts_tbl,
1341 p_risks_tbl => p_risks_tbl,
1342 p_commit => p_commit,
1343 x_msg_data => x_msg_data,
1344 x_msg_count => x_msg_count,
1345 x_return_status => x_return_status);
1346
1347 -- Standard check of p_commit
1348 IF FND_API.To_Boolean( p_commit ) THEN
1349 COMMIT WORK;
1350 END IF;
1351
1352 -- Standard call to get message count and if count is 1, get message info.
1353 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1354
1355 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1356 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1357 'Leaving OKC_REP_CLOSEOUT_PVT.'||l_api_name);
1358 END IF;
1359
1360 EXCEPTION
1361 WHEN FND_API.G_EXC_ERROR THEN
1362 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1363 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1364 g_module || l_api_name,
1365 'Leaving create_contract:FND_API.G_EXC_ERROR Exception');
1366 END IF;
1367
1368 ROLLBACK TO create_contract_PVT;
1369 x_return_status := FND_API.G_RET_STS_ERROR;
1370 FND_MSG_PUB.Count_And_Get(
1371 p_count => x_msg_count,
1372 p_data => x_msg_data
1373 );
1374
1375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1376 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1377 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1378 g_module || l_api_name,
1379 'Leaving create_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1380 END IF;
1381
1382 ROLLBACK TO create_contract_PVT;
1383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1384 FND_MSG_PUB.Count_And_Get(
1385 p_count => x_msg_count,
1386 p_data => x_msg_data
1387 );
1388
1389 WHEN OTHERS THEN
1390 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1391 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1392 g_module || l_api_name,
1393 'Leaving create_contract because of EXCEPTION: ' || sqlerrm);
1394 END IF;
1395 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1396 p_msg_name => G_UNEXPECTED_ERROR,
1397 p_token1 => G_SQLCODE_TOKEN,
1398 p_token1_value => sqlcode,
1399 p_token2 => G_SQLERRM_TOKEN,
1400 p_token2_value => sqlerrm);
1401 ROLLBACK TO create_contract_PVT;
1402
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404 FND_MSG_PUB.Count_And_Get(
1405 p_count => x_msg_count,
1406 p_data => x_msg_data
1407 );
1408
1409 END create_contract;
1410
1411 /*
1412 Deletes the Contract for the given document type.
1413 */
1414
1415 Procedure delete_contract(p_api_version IN NUMBER,
1416 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1417 p_contract_type IN VARCHAR2 DEFAULT NULL,
1418 p_contract_id IN NUMBER DEFAULT NULL,
1419 p_commit IN VARCHAR2 := fnd_api.g_false,
1420 x_msg_data OUT NOCOPY VARCHAR2,
1421 x_msg_count OUT NOCOPY NUMBER,
1422 x_return_status OUT NOCOPY VARCHAR2
1423 )
1424 IS
1425
1426 l_api_name VARCHAR2(50);
1427 l_api_version CONSTANT NUMBER := 1.0;
1428
1429 BEGIN
1430 l_api_name := 'delete_contract';
1431
1432 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1433 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1434 'Entered OKC_REP_CLOSEOUT_PVT.'||l_api_name);
1435 END IF;
1436
1437 -- Standard Start of API savepoint
1438 SAVEPOINT delete_contract_PVT;
1439
1440 -- Standard call to check for call compatibility.
1441 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1443 END IF;
1444
1445 OKC_REP_CONTRACT_IMP_PVT.delete_contract( p_api_version => p_api_version,
1446 p_document_rec => p_document_rec,
1447 p_contract_type => p_contract_type,
1448 p_contract_id => p_contract_id,
1449 p_commit => p_commit,
1450 x_msg_data => x_msg_data,
1451 x_msg_count => x_msg_count,
1452 x_return_status => x_return_status);
1453
1454 -- Standard check of p_commit
1455 IF FND_API.To_Boolean( p_commit ) THEN
1456 COMMIT WORK;
1457 END IF;
1458
1459 -- Standard call to get message count and if count is 1, get message info.
1460 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1461
1462 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1463 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1464 'Leaving OKC_REP_CLOSEOUT_PVT.'||l_api_name);
1465 END IF;
1466
1467 EXCEPTION
1468 WHEN FND_API.G_EXC_ERROR THEN
1469 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1471 g_module || l_api_name,
1472 'Leaving delete_contract:FND_API.G_EXC_ERROR Exception');
1473 END IF;
1474
1475 ROLLBACK TO delete_contract_PVT;
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 FND_MSG_PUB.Count_And_Get(
1478 p_count => x_msg_count,
1479 p_data => x_msg_data
1480 );
1481
1482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1483 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1484 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1485 g_module || l_api_name,
1486 'Leaving delete_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1487 END IF;
1488
1489 ROLLBACK TO delete_contract_PVT;
1490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1491 FND_MSG_PUB.Count_And_Get(
1492 p_count => x_msg_count,
1493 p_data => x_msg_data
1494 );
1495
1496 WHEN OTHERS THEN
1497 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1499 g_module || l_api_name,
1500 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
1501 END IF;
1502 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1503 p_msg_name => G_UNEXPECTED_ERROR,
1504 p_token1 => G_SQLCODE_TOKEN,
1505 p_token1_value => sqlcode,
1506 p_token2 => G_SQLERRM_TOKEN,
1507 p_token2_value => sqlerrm);
1508 ROLLBACK TO delete_contract_PVT;
1509
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1511 FND_MSG_PUB.Count_And_Get(
1512 p_count => x_msg_count,
1513 p_data => x_msg_data
1514 );
1515
1516 END delete_contract;
1517
1518 /*
1519 Creates a new version of the contract contract in draft status.
1520 Updates to the contract should be done from UI.
1521
1522 The relation of parent document which is obtained in p_document_rec is stored in the okc_rep_contract_usages table
1523 */
1524
1525 Procedure create_new_contract_version(p_api_version IN NUMBER,
1526 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1527 p_contract_type IN VARCHAR2 DEFAULT NULL,
1528 p_contract_id IN NUMBER DEFAULT NULL,
1529 p_commit IN VARCHAR2 := fnd_api.g_false,
1530 x_msg_data OUT NOCOPY VARCHAR2,
1531 x_msg_count OUT NOCOPY NUMBER,
1532 x_return_status OUT NOCOPY VARCHAR2
1533 )
1534 IS
1535
1536 l_api_name VARCHAR2(50);
1537 l_api_version CONSTANT NUMBER := 1.0;
1538 l_run_id NUMBER;
1539
1540 BEGIN
1541 l_api_name := 'create_new_contract_version';
1542
1543 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1544 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1545 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1546 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1547 'p_contract_id: '||p_contract_id);
1548 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1549 'p_contract_type: '||p_contract_type);
1550 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1551 'p_commit : '||p_commit);
1552 END IF;
1553
1554 -- Standard Start of API savepoint
1555 SAVEPOINT create_contract_vers_PVT;
1556
1557 -- Standard call to check for call compatibility.
1558 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1560 END IF;
1561
1562 SELECT okc_rep_import_run_id_s.NEXTVAL INTO l_run_id FROM dual;
1563
1564 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1565 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1566 'l_run_id : '||l_run_id);
1567 END IF;
1568
1569 OKC_REP_CONTRACT_IMP_PVT.create_new_contract_version( p_api_version => p_api_version,
1570 p_contract_id => p_contract_id,
1571 p_run_id => l_run_id,
1572 p_commit => p_commit,
1573 x_msg_data => x_msg_data,
1574 x_msg_count => x_msg_count,
1575 x_return_status => x_return_status);
1576
1577 -- Standard check of p_commit
1578 IF FND_API.To_Boolean( p_commit ) THEN
1579 COMMIT WORK;
1580 END IF;
1581
1582 -- Standard call to get message count and if count is 1, get message info.
1583 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1584
1585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1586 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1587 'Leaving OKC_REP_CLOSEOUT_PVT.'||l_api_name);
1588 END IF;
1589
1590 EXCEPTION
1591 WHEN FND_API.G_EXC_ERROR THEN
1592 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1593 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1594 g_module || l_api_name,
1595 'Leaving create_new_contract_version:FND_API.G_EXC_ERROR Exception');
1596 END IF;
1597
1598 ROLLBACK TO create_contract_vers_PVT;
1599 x_return_status := FND_API.G_RET_STS_ERROR;
1600 FND_MSG_PUB.Count_And_Get(
1601 p_count => x_msg_count,
1602 p_data => x_msg_data
1603 );
1604
1605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1606 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1607 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1608 g_module || l_api_name,
1609 'Leaving create_new_contract_version:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1610 END IF;
1611
1612 ROLLBACK TO create_contract_vers_PVT;
1613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1614 FND_MSG_PUB.Count_And_Get(
1615 p_count => x_msg_count,
1616 p_data => x_msg_data
1617 );
1618
1619 WHEN OTHERS THEN
1620 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1621 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1622 g_module || l_api_name,
1623 'Leaving create_new_contract_version because of EXCEPTION: ' || sqlerrm);
1624 END IF;
1625 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1626 p_msg_name => G_UNEXPECTED_ERROR,
1627 p_token1 => G_SQLCODE_TOKEN,
1628 p_token1_value => sqlcode,
1629 p_token2 => G_SQLERRM_TOKEN,
1630 p_token2_value => sqlerrm);
1631
1632 ROLLBACK TO create_contract_vers_PVT;
1633
1634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1635 FND_MSG_PUB.Count_And_Get(
1636 p_count => x_msg_count,
1637 p_data => x_msg_data
1638 );
1639
1640 END create_new_contract_version;
1641
1642 PROCEDURE check_contract_exists(p_api_version IN NUMBER,
1643 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1644 x_contract_type IN OUT NOCOPY VARCHAR2 ,
1645 x_contract_id IN OUT NOCOPY NUMBER ,
1646 x_contract_version_num IN OUT NOCOPY NUMBER,
1647 x_msg_data OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER,
1649 x_return_status OUT NOCOPY VARCHAR2
1650 )
1651 IS
1652
1653 l_api_name VARCHAR2(50);
1654 l_api_version CONSTANT NUMBER := 1.0;
1655 l_run_id NUMBER;
1656
1657 BEGIN
1658 l_api_name := 'check_contract_exists';
1659
1660 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1661 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1662 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1663 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1664 'p_document_rec.business_document_type '||p_document_rec.business_document_type);
1665 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1666 'p_document_rec.business_document_id '||p_document_rec.business_document_id);
1667 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1668 'p_document_rec.business_document_version '||p_document_rec.business_document_version);
1669 END IF;
1670
1671 -- Standard Start of API savepoint
1672 SAVEPOINT check_contract_exists_PVT;
1673
1674 -- Standard call to check for call compatibility.
1675 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1677 END IF;
1678
1679 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1680 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1681 'l_run_id : '||l_run_id);
1682 END IF;
1683
1684 OKC_REP_CONTRACT_IMP_PVT.check_contract_exists(p_api_version => 1.0,
1685 p_document_rec => p_document_rec,
1686 x_contract_type => x_contract_type,
1687 x_contract_id => x_contract_id,
1688 x_contract_version_num => x_contract_version_num,
1689 x_msg_data => x_msg_data,
1690 x_msg_count => x_msg_count,
1691 x_return_status => x_return_status);
1692
1693 -- Standard call to get message count and if count is 1, get message info.
1694 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1695
1696 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1697 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1698 'Leaving OKC_REP_CLOSEOUT_PVT.'||l_api_name);
1699 END IF;
1700
1701 EXCEPTION
1702 WHEN FND_API.G_EXC_ERROR THEN
1703 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1704 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1705 g_module || l_api_name,
1706 'Leaving check_contract_exists:FND_API.G_EXC_ERROR Exception');
1707 END IF;
1708
1709 ROLLBACK TO check_contract_exists_PVT;
1710 x_return_status := FND_API.G_RET_STS_ERROR;
1711 FND_MSG_PUB.Count_And_Get(
1712 p_count => x_msg_count,
1713 p_data => x_msg_data
1714 );
1715
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1718 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1719 g_module || l_api_name,
1720 'Leaving check_contract_exists:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1721 END IF;
1722
1723 ROLLBACK TO check_contract_exists_PVT;
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 FND_MSG_PUB.Count_And_Get(
1726 p_count => x_msg_count,
1727 p_data => x_msg_data
1728 );
1729
1730 WHEN OTHERS THEN
1731 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1732 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1733 g_module || l_api_name,
1734 'Leaving check_contract_exists because of EXCEPTION: ' || sqlerrm);
1735 END IF;
1736 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1737 p_msg_name => G_UNEXPECTED_ERROR,
1738 p_token1 => G_SQLCODE_TOKEN,
1739 p_token1_value => sqlcode,
1740 p_token2 => G_SQLERRM_TOKEN,
1741 p_token2_value => sqlerrm);
1742
1743 ROLLBACK TO check_contract_exists_PVT;
1744
1745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1746 FND_MSG_PUB.Count_And_Get(
1747 p_count => x_msg_count,
1748 p_data => x_msg_data
1749 );
1750
1751 END check_contract_exists;
1752
1753 -------------------------------------------------------------------------------
1754 --Start of Comments
1755 --Name: deliverable_completed_event
1756 --Function:
1757 -- This function is called from an business event subscription. The parameters are DELIVERABLE_ID.
1758 --End of Comments
1759 -------------------------------------------------------------------------------
1760
1761 FUNCTION deliverable_completed_event (p_subscription_guid in raw,
1762 p_event in out nocopy WF_EVENT_T) RETURN VARCHAR2 IS
1763
1764 l_deliverable_id NUMBER;
1765 l_po_header_id NUMBER;
1766 all_dels_completed VARCHAR2(1);
1767 l_return_status VARCHAR2(1);
1768
1769 CURSOR del_cur IS
1770 SELECT *
1771 FROM okc_deliverables
1772 WHERE deliverable_id = l_deliverable_id;
1773
1774 l_del_rec del_cur%ROWTYPE;
1775 l_api_name VARCHAR2(30);
1776 BEGIN
1777
1778 l_api_name := 'deliverable_completed_event';
1779
1780 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1781 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name, 'Entered OKC_REP_CLOSEOUT_PVT.deliverable_completed_event');
1782 END IF;
1783
1784 l_deliverable_id := p_event.GetValueForParameter('DELIVERABLE_ID');
1785
1786 OPEN del_cur;
1787 FETCH del_cur into l_del_rec;
1788 CLOSE del_cur;
1789
1790 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1791 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name, 'DeliverableId is: ' || l_deliverable_id);
1792 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name, 'business_document_type is: ' || l_del_rec.business_document_type);
1793 END IF;
1794
1795 --checking whether the contract_type is CLOSE OUT OR NOT
1796 IF (l_del_rec.business_document_type = 'REP_CCC') THEN
1797
1798 BEGIN
1799 SELECT 'N' INTO all_dels_completed
1800 FROM okc_deliverables del
1801 WHERE del.business_document_id = l_del_rec.business_document_id
1802 AND del.business_document_type = l_del_rec.business_document_type
1803 AND del.business_document_version = l_del_rec.business_document_version
1804 AND del.deliverable_status NOT IN ('COMPLETED', 'CANCELLED', 'FAILED_TO_PERFORM')
1805 AND ROWNUM = 1;
1806 EXCEPTION
1807 WHEN NO_DATA_FOUND THEN
1808 all_dels_completed := 'Y';
1809 WHEN OTHERS THEN
1810 RAISE;
1811 END;
1812
1813 IF (all_dels_completed = 'Y') THEN
1814
1815 SELECT orcu.business_document_id INTO l_po_header_id
1816 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orc
1817 WHERE orcu.contract_id = l_del_rec.business_document_id
1818 AND orcu.contract_type = l_del_rec.business_document_type
1819 AND orc.contract_id = orcu.contract_id
1820 AND orc.contract_type = orcu.contract_type
1821 AND orc.contract_version_num = orcu.contract_version;
1822
1823 -- calling package
1824 po_clm_okc_integ_pkg.start_closeout_wf_process(p_doc_id => l_po_header_id, x_return_status => l_return_status);
1825 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1826 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1827 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name, 'po_clm_okc_integ_pkg.start_closeout_wf_process return status is not success');
1828 END IF;
1829 rollback;
1830 RAISE FND_API.G_EXC_ERROR;
1831 END IF;
1832 END IF;
1833 END IF;
1834 commit;
1835
1836 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1837 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name, 'all_dels_completed is: ' || all_dels_completed);
1838 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name, 'Leaving OKC_REP_CLOSEOUT_PVT.deliverable_completed_event');
1839 END IF;
1840 RETURN 'SUCCESS';
1841
1842 EXCEPTION
1843 WHEN OTHERS THEN
1844 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1845 fnd_log.string(FND_LOG.LEVEL_EXCEPTION, g_module || l_api_name, 'Leaving deliverable_completed_event:FND_API.G_EXC_ERROR Exception');
1846 END IF;
1847 rollback;
1848 WF_CORE.CONTEXT('OKC_DELIVERABLE_WF_PVT', 'deliverable_completed_event', p_event.getEventName(), p_subscription_guid);
1849 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1850 RETURN 'ERROR';
1851
1852 END deliverable_completed_event;
1853
1854
1855 PROCEDURE validate_closeout_tasks(p_api_version IN NUMBER,
1856 p_init_msg_list IN VARCHAR2,
1857 p_commit IN VARCHAR2,
1858 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1859 x_msg_data OUT NOCOPY VARCHAR2,
1860 x_msg_count OUT NOCOPY NUMBER,
1861 x_return_status OUT NOCOPY VARCHAR2,
1862 x_qa_return_status OUT NOCOPY VARCHAR2,
1863 x_qa_result_tbl OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type)
1864 IS
1865
1866 l_api_name VARCHAR2(30);
1867 l_api_version NUMBER;
1868 l_internal_party_id OKC_REP_CONTRACT_PARTIES.PARTY_ID%TYPE;
1869 l_error_found Boolean := FALSE;
1870 l_warning_found Boolean := FALSE;
1871 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
1872 l_bus_doc_date_events_tbl OKC_TERMS_QA_GRP.BUSDOCDATES_TBL_TYPE;
1873 l_sequence_id NUMBER;
1874 l_qa_result_tbl OKC_TERMS_QA_PVT.qa_result_tbl_type;
1875
1876 CURSOR c_get_doc_details IS
1877 SELECT orcu.contract_type, orcu.contract_id,
1878 orca.contract_effective_date
1879 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
1880 WHERE orcu.business_document_id = p_document_rec.business_document_id
1881 and orcu.business_document_type = p_document_rec.business_document_type
1882 AND orca.contract_type = orcu.contract_type
1883 AND orcu.contract_version = orca.contract_version_num
1884 AND orca.contract_id = orcu.contract_id;
1885
1886 contract_rec c_get_doc_details%ROWTYPE;
1887
1888 BEGIN
1889
1890 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1891 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1892 'Entered OKC_REP_CONTRACT_PROCESS_PVT.validate_closeout_tasks');
1893 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1894 ' Business Document Id is: ' || p_document_rec.business_document_id);
1895 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1896 ' Business Document Type is: ' || p_document_rec.business_document_type);
1897 END IF;
1898
1899 l_api_name := 'validate_closeout_tasks';
1900 l_api_version := 1.0;
1901
1902 -- Standard Start of API savepoint
1903 SAVEPOINT val_closeout_tasks_PVT;
1904
1905 -- Standard call to check for call compatibility.
1906 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1908 END IF;
1909
1910 -- Initialize message list if p_init_msg_list is set to TRUE.
1911 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1912 FND_MSG_PUB.initialize;
1913 END IF;
1914
1915 -- Initialize API return status to success
1916 x_return_status := FND_API.G_RET_STS_SUCCESS;
1917
1918 -- Get contract document details which is associated to this PO Closeout
1919 OPEN c_get_doc_details;
1920 FETCH c_get_doc_details INTO contract_rec;
1921 IF(c_get_doc_details%NOTFOUND) THEN
1922 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1923 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1924 G_MODULE||l_api_name,
1925 'Invalid Business Document Id: '|| p_document_rec.business_document_id);
1926 END IF;
1927 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1928 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
1929 p_token1 => G_CONTRACT_ID_TOKEN,
1930 p_token1_value => to_char(p_document_rec.business_document_id));
1931 RAISE FND_API.G_EXC_ERROR;
1932 END IF;
1933
1934 l_bus_doc_date_events_tbl(1).event_code := G_PO_PHYSICAL_COMPLETE_EVENT;
1935 l_bus_doc_date_events_tbl(1).event_date := SYSDATE ;
1936
1937
1938 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
1939 p_api_version => 1,
1940 p_init_msg_list => FND_API.G_FALSE,
1941 p_doc_type => contract_rec.contract_type,
1942 p_doc_id => contract_rec.contract_id,
1943 p_mode => G_NORMAL_QA,
1944 p_bus_doc_date_events_tbl => l_bus_doc_date_events_tbl,
1945 p_qa_result_tbl => x_qa_result_tbl,
1946 x_msg_data => x_msg_data,
1947 x_msg_count => x_msg_count,
1948 x_return_status => x_return_status,
1949 x_qa_return_status => x_qa_return_status);
1950
1951 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1952 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1953 'Completed OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa with returned status: ' || x_return_status);
1954 END IF;
1955
1956 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1957 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1958 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1959 RAISE OKC_API.G_EXCEPTION_ERROR;
1960 END IF;
1961
1962 --------------------------------------------
1963 -- VALIDATIONS are done for Deliverables.
1964 -- Now insert into Temp table.
1965 --------------------------------------------
1966 -- Save result from PLSQL table into DB table
1967 --------------------------------------------
1968 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1969 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1970 G_MODULE||l_api_name,'1015: Save result from PLSQL table into DB table');
1971 END IF;
1972
1973
1974 -- After calling the validation APIs we need to find out about the x_qa_return_status. We should loop through
1975 -- this only if we get
1976 IF x_qa_result_tbl.COUNT > 0 THEN
1977 FOR i IN l_qa_result_tbl.FIRST..l_qa_result_tbl.LAST LOOP
1978 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1979 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1980 'Updating pl/sql table record: ' || i);
1981 END IF;
1982 x_qa_result_tbl(i).error_record_type_name := okc_util.decode_lookup('OKC_ERROR_RECORD_TYPE',x_qa_result_tbl(i).error_record_type);
1983 x_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',x_qa_result_tbl(i).error_severity);
1984 x_qa_result_tbl(i).document_type := p_document_rec.business_document_type;
1985 x_qa_result_tbl(i).document_id := p_document_rec.business_document_id;
1986 x_qa_result_tbl(i).creation_date := sysdate;
1987 IF x_qa_result_tbl(i).error_severity = G_QA_STS_ERROR THEN
1988 l_error_found := true;
1989 END IF;
1990 IF l_qa_result_tbl(i).error_severity = G_QA_STS_WARNING THEN
1991 l_warning_found := true;
1992 END IF;
1993
1994 END LOOP;
1995 IF l_error_found THEN
1996 x_qa_return_status := G_QA_STS_ERROR;
1997 ELSIF l_warning_found THEN
1998 x_qa_return_status := G_QA_STS_WARNING;
1999 END IF;
2000 END IF; -- l_qa_result_tbl.COUNT > 0 THEN
2001
2002 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2003 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2004 'Calling OKC_TERMS_QA_PVT.Log_QA_Messages');
2005 END IF;
2006
2007 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2008 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2009 'Completed OKC_TERMS_QA_PVT.Log_QA_Messages');
2010 END IF;
2011 --------------------------------------------
2012 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2014 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2015 RAISE FND_API.G_EXC_ERROR ;
2016 END IF;
2017 --------------------------------------------
2018
2019 -- close open cursors
2020 CLOSE c_get_doc_details;
2021
2022 -- Standard check of p_commit
2023 IF FND_API.To_Boolean( p_commit ) THEN
2024 COMMIT WORK;
2025 END IF;
2026
2027 -- Standard call to get message count and if count is 1, get message info.
2028 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2029
2030 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2032 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.validate_closeout_tasks');
2033 END IF;
2034
2035 EXCEPTION
2036 WHEN FND_API.G_EXC_ERROR THEN
2037 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2038 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2039 g_module || l_api_name,
2040 'Leaving validate_closeout_tasks:FND_API.G_EXC_ERROR Exception');
2041 END IF;
2042 --close cursors
2043 IF (c_get_doc_details%ISOPEN) THEN
2044 CLOSE c_get_doc_details ;
2045 END IF;
2046 ROLLBACK TO val_closeout_tasks_PVT;
2047 x_return_status := FND_API.G_RET_STS_ERROR;
2048 FND_MSG_PUB.Count_And_Get(
2049 p_count => x_msg_count,
2050 p_data => x_msg_data
2051 );
2052
2053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2054 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2055 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2056 g_module || l_api_name,
2057 'Leaving validate_closeout_tasks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2058 END IF;
2059 --close cursors
2060 IF (c_get_doc_details%ISOPEN) THEN
2061 CLOSE c_get_doc_details ;
2062 END IF;
2063
2064 ROLLBACK TO val_closeout_tasks_PVT;
2065
2066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067 FND_MSG_PUB.Count_And_Get(
2068 p_count => x_msg_count,
2069 p_data => x_msg_data
2070 );
2071
2072 WHEN OTHERS THEN
2073 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2074 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2075 g_module || l_api_name,
2076 'Leaving validate_closeout_tasks because of EXCEPTION: ' || sqlerrm);
2077 END IF;
2078 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2079 p_msg_name => G_UNEXPECTED_ERROR,
2080 p_token1 => G_SQLCODE_TOKEN,
2081 p_token1_value => sqlcode,
2082 p_token2 => G_SQLERRM_TOKEN,
2083 p_token2_value => sqlerrm);
2084
2085 ROLLBACK TO val_closeout_tasks_PVT;
2086
2087 --close cursors
2088 IF (c_get_doc_details%ISOPEN) THEN
2089 CLOSE c_get_doc_details ;
2090 END IF;
2091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2092 FND_MSG_PUB.Count_And_Get(
2093 p_count => x_msg_count,
2094 p_data => x_msg_data
2095 );
2096
2097 END validate_closeout_tasks;
2098
2099 PROCEDURE copy_set_tasks_to_each_po(p_api_version IN NUMBER,
2100 p_init_msg_list IN VARCHAR2,
2101 p_commit IN VARCHAR2,
2102 p_src_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
2103 p_tgt_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
2104 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
2105 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
2106 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
2107 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
2108 x_msg_data OUT NOCOPY VARCHAR2,
2109 x_msg_count OUT NOCOPY NUMBER,
2110 x_return_status OUT NOCOPY VARCHAR2)
2111 IS
2112
2113 l_api_name VARCHAR2(30);
2114 l_api_version NUMBER;
2115 l_source_contract_id NUMBER;
2116 l_target_contract_id NUMBER;
2117 l_auto_num_doc VARCHAR2(1);
2118 l_auto_num_profile VARCHAR2(1);
2119 l_auto_number_yn VARCHAR2(1);
2120 l_auto_number_option CONSTANT VARCHAR2(30):= 'OKC_REP_AUTO_CON_NUMBER';
2121
2122 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
2123
2124 CURSOR c_get_src_contract IS
2125 SELECT orcu.contract_id
2126 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
2127 WHERE orcu.business_document_id = p_src_document_rec.business_document_id
2128 and orcu.business_document_type = p_src_document_rec.business_document_type
2129 AND orca.contract_type = orcu.contract_type
2130 AND orcu.contract_version = orca.contract_version_num
2131 AND orca.contract_id = orcu.contract_id;
2132
2133 CURSOR c_get_tgt_contract IS
2134 SELECT orcu.contract_id
2135 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
2136 WHERE orcu.business_document_id = p_tgt_document_rec.business_document_id
2137 and orcu.business_document_type = p_tgt_document_rec.business_document_type
2138 AND orca.contract_type = orcu.contract_type
2139 AND orcu.contract_version = orca.contract_version_num
2140 AND orca.contract_id = orcu.contract_id;
2141
2142 BEGIN
2143
2144 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2145 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2146 'Entered OKC_REP_CONTRACT_PROCESS_PVT.validate_closeout_tasks');
2147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2148 ' Business Document Id is: ' || p_src_document_rec.business_document_id);
2149 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2150 ' Business Document Type is: ' || p_src_document_rec.business_document_type);
2151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2152 ' Business Document Id is: ' || p_tgt_document_rec.business_document_id);
2153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2154 ' Business Document Type is: ' || p_tgt_document_rec.business_document_type);
2155
2156 END IF;
2157
2158 l_api_name := 'copy_set_tasks_to_each_po';
2159 l_api_version := 1.0;
2160
2161 -- Standard Start of API savepoint
2162 SAVEPOINT copy_tasks_to_each_po_PVT;
2163
2164 -- Standard call to check for call compatibility.
2165 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2167 END IF;
2168
2169 -- Initialize message list if p_init_msg_list is set to TRUE.
2170 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2171 FND_MSG_PUB.initialize;
2172 END IF;
2173
2174 -- Initialize API return status to success
2175 x_return_status := FND_API.G_RET_STS_SUCCESS;
2176
2177 l_document_rec := p_tgt_document_rec;
2178
2179 l_document_rec.source_code := 'INTERNAL';
2180 l_document_rec.relationship_id := 10006;
2181 l_document_rec.display_in_contract := 'N';
2182
2183 --if Autonumbering is enabled then p_contract_rec.contract_number shd be null
2184 --PO will always pass a value. So here we will null it out based on the profile.
2185 --Checking profile option value
2186 BEGIN
2187 select auto_num_enabled_yn INTO l_auto_num_doc
2188 from okc_bus_doc_types_vl
2189 where name = p_contract_rec.contract_type_txt;
2190
2191 FND_PROFILE.GET(NAME => l_auto_number_option, VAL => l_auto_num_profile);
2192
2193 /* Check the autonumbering setup at document level */
2194
2195 IF Nvl(l_auto_num_doc,'N') = 'N' THEN
2196 IF Nvl(l_auto_num_profile , 'N') = 'N' THEN
2197 l_auto_number_yn := 'N';
2198 ELSE
2199 l_auto_number_yn := 'Y';
2200 END IF;
2201 ELSE
2202 l_auto_number_yn := 'Y';
2203 END IF;
2204
2205 IF (l_auto_number_yn = 'Y') THEN
2206 p_contract_rec.contract_number := NULL;
2207 END IF;
2208
2209 EXCEPTION
2210 WHEN No_Data_Found THEN
2211 NULL;
2212 END;
2213
2214 --create the contract for individual PO
2215
2216 OKC_REP_CONTRACT_IMP_PVT.create_contract( p_api_version => p_api_version,
2217 p_document_rec => l_document_rec,
2218 p_contract_rec => p_contract_rec,
2219 p_contract_parties_tbl => p_contract_parties_tbl,
2220 p_party_contacts_tbl => p_party_contacts_tbl,
2221 p_risks_tbl => p_risks_tbl,
2222 p_commit => p_commit,
2223 x_msg_data => x_msg_data,
2224 x_msg_count => x_msg_count,
2225 x_return_status => x_return_status);
2226
2227 -----------------------------------------------------
2228 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2229 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2230 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2231 RAISE OKC_API.G_EXCEPTION_ERROR;
2232 END IF;
2233 --------------------------------------------------------
2234
2235 OPEN c_get_src_contract;
2236 FETCH c_get_src_contract INTO l_source_contract_id;
2237 CLOSE c_get_src_contract;
2238
2239 OPEN c_get_tgt_contract;
2240 FETCH c_get_tgt_contract INTO l_target_contract_id;
2241 CLOSE c_get_tgt_contract;
2242
2243 --copy the deliverables from source to target contract(i.e from mass closeout contract to individual PO contract)
2244 copy_deliverables( p_api_version => 1.0,
2245 p_init_msg_list => FND_API.G_FALSE,
2246 p_commit => p_commit,
2247 p_source_contract_type => 'REP_CCC',
2248 p_source_contract_id => l_source_contract_id,
2249 p_target_contract_type => 'REP_CCC',
2250 p_target_contract_id => l_target_contract_id,
2251 x_msg_data => x_msg_data,
2252 x_msg_count => x_msg_count,
2253 x_return_status => x_return_status);
2254 -----------------------------------------------------
2255 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2256 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2257 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2258 RAISE OKC_API.G_EXCEPTION_ERROR;
2259 END IF;
2260 --------------------------------------------------------
2261
2262 --update supplier information on the individual Po Del
2263
2264 UPDATE okc_deliverables set EXTERNAL_PARTY_ID =
2265 (SELECT PARTY_ID
2266 FROM okc_rep_contract_parties
2267 WHERE contract_id = l_target_contract_id
2268 AND PARTY_ROLE_CODE = 'SUPPLIER_ORG')
2269 WHERE business_document_type = 'REP_CCC'
2270 AND business_document_id = l_target_contract_id
2271 AND DELIVERABLE_TYPE = 'CONTRACTUAL';
2272 -----------------------------------------------------
2273 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2274 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2275 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2276 RAISE OKC_API.G_EXCEPTION_ERROR;
2277 END IF;
2278 --------------------------------------------------------
2279
2280 -- Standard check of p_commit
2281 IF FND_API.To_Boolean( p_commit ) THEN
2282 COMMIT WORK;
2283 END IF;
2284
2285 -- Standard call to get message count and if count is 1, get message info.
2286 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2287
2288 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2289 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2290 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.copy_set_tasks_to_each_po');
2291 END IF;
2292
2293 EXCEPTION
2294 WHEN FND_API.G_EXC_ERROR THEN
2295 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2296 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2297 g_module || l_api_name,
2298 'Leaving copy_set_tasks_to_each_po:FND_API.G_EXC_ERROR Exception');
2299 END IF;
2300 --close cursors
2301 IF (c_get_src_contract%ISOPEN) THEN
2302 CLOSE c_get_src_contract ;
2303 END IF;
2304 IF (c_get_tgt_contract%ISOPEN) THEN
2305 CLOSE c_get_tgt_contract ;
2306 END IF;
2307
2308 ROLLBACK TO copy_tasks_to_each_po_PVT;
2309 x_return_status := FND_API.G_RET_STS_ERROR;
2310 FND_MSG_PUB.Count_And_Get(
2311 p_count => x_msg_count,
2312 p_data => x_msg_data
2313 );
2314
2315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2316 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2317 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2318 g_module || l_api_name,
2319 'Leaving copy_set_tasks_to_each_po:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2320 END IF;
2321 --close cursors
2322 IF (c_get_src_contract%ISOPEN) THEN
2323 CLOSE c_get_src_contract ;
2324 END IF;
2325 IF (c_get_tgt_contract%ISOPEN) THEN
2326 CLOSE c_get_tgt_contract ;
2327 END IF;
2328
2329 ROLLBACK TO copy_tasks_to_each_po_PVT;
2330
2331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2332 FND_MSG_PUB.Count_And_Get(
2333 p_count => x_msg_count,
2334 p_data => x_msg_data
2335 );
2336
2337 WHEN OTHERS THEN
2338 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2339 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2340 g_module || l_api_name,
2341 'Leaving copy_set_tasks_to_each_po because of EXCEPTION: ' || sqlerrm);
2342 END IF;
2343 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2344 p_msg_name => G_UNEXPECTED_ERROR,
2345 p_token1 => G_SQLCODE_TOKEN,
2346 p_token1_value => sqlcode,
2347 p_token2 => G_SQLERRM_TOKEN,
2348 p_token2_value => sqlerrm);
2349
2350 ROLLBACK TO copy_tasks_to_each_po_PVT;
2351
2352 --close cursors
2353 IF (c_get_src_contract%ISOPEN) THEN
2354 CLOSE c_get_src_contract ;
2355 END IF;
2356 IF (c_get_tgt_contract%ISOPEN) THEN
2357 CLOSE c_get_tgt_contract ;
2358 END IF;
2359
2360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2361 FND_MSG_PUB.Count_And_Get(
2362 p_count => x_msg_count,
2363 p_data => x_msg_data
2364 );
2365
2366 END copy_set_tasks_to_each_po;
2367
2368 PROCEDURE check_tasks_exists(p_api_version IN NUMBER,
2369 p_init_msg_list IN VARCHAR2,
2370 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
2371 x_del_count OUT NOCOPY NUMBER,
2372 x_msg_data OUT NOCOPY VARCHAR2,
2373 x_msg_count OUT NOCOPY NUMBER,
2374 x_return_status OUT NOCOPY VARCHAR2)
2375
2376 IS
2377
2378 CURSOR c_get_tasks_exists_csr IS
2379 SELECT Count(deliverable_id)
2380 FROM okc_rep_contract_usages orcu, okc_deliverables del
2381 WHERE orcu.business_document_type = p_document_rec.business_document_type
2382 AND orcu.business_document_id = p_document_rec.business_document_id
2383 AND del.business_document_type = orcu.contract_type
2384 AND del.business_document_id = orcu.contract_id
2385 AND del.business_document_version = -99 ;
2386
2387 l_api_name VARCHAR2(50);
2388 l_api_version CONSTANT NUMBER := 1.0;
2389
2390 BEGIN
2391
2392 l_api_name := 'check_tasks_exists';
2393
2394 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2395 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2396 'Entered okc_rep_closeout_pvt.'||l_api_name);
2397 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2398 'p_document_rec.business_document_type '||p_document_rec.business_document_type);
2399 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2400 'p_document_rec.business_document_id '||p_document_rec.business_document_id);
2401 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2402 'p_document_rec.business_document_version '||p_document_rec.business_document_version);
2403 END IF;
2404
2405 -- Standard Start of API savepoint
2406 SAVEPOINT check_tasks_exists_PVT;
2407
2408 -- Standard call to check for call compatibility.
2409 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411 END IF;
2412
2413 OPEN c_get_tasks_exists_csr;
2414 FETCH c_get_tasks_exists_csr INTO x_del_count;
2415
2416 IF c_get_tasks_exists_csr%NOTFOUND THEN
2417 x_return_status := FND_API.G_RET_STS_ERROR;
2418 END IF;
2419
2420 CLOSE c_get_tasks_exists_csr;
2421
2422 -- Standard call to get message count and if count is 1, get message info.
2423 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2424
2425 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2426 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2427 'Leaving OKC_REP_CLOSEOUT_PVT.'||l_api_name);
2428 END IF;
2429
2430 EXCEPTION
2431 WHEN FND_API.G_EXC_ERROR THEN
2432 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2433 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2434 g_module || l_api_name,
2435 'Leaving check_tasks_exists:FND_API.G_EXC_ERROR Exception');
2436 END IF;
2437
2438 ROLLBACK TO check_tasks_exists_PVT;
2439 x_return_status := FND_API.G_RET_STS_ERROR;
2440 FND_MSG_PUB.Count_And_Get(
2441 p_count => x_msg_count,
2442 p_data => x_msg_data
2443 );
2444
2445 WHEN OTHERS THEN
2446 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2447 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2448 g_module || l_api_name,
2449 'Leaving check_tasks_exists because of EXCEPTION: ' || sqlerrm);
2450 END IF;
2451 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2452 p_msg_name => G_UNEXPECTED_ERROR,
2453 p_token1 => G_SQLCODE_TOKEN,
2454 p_token1_value => sqlcode,
2455 p_token2 => G_SQLERRM_TOKEN,
2456 p_token2_value => sqlerrm);
2457
2458 ROLLBACK TO check_tasks_exists_PVT;
2459
2460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2461 FND_MSG_PUB.Count_And_Get(
2462 p_count => x_msg_count,
2463 p_data => x_msg_data
2464 );
2465 END check_tasks_exists;
2466
2467
2468
2469 END okc_rep_closeout_pvt;