[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_CONTRACT_IMP_PVT
Source
1 PACKAGE BODY OKC_REP_CONTRACT_IMP_PVT AS
2 /* $Header: OKCVREPIMPB.pls 120.0.12020000.13 2013/06/04 14:25:02 nbingi noship $ */
3
4 /*
5 Updates the okc_contract_usages table with the relationships provided
6 */
7 PROCEDURE Update_contract_usages(p_api_version IN NUMBER,
8 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
9 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
10 p_commit IN VARCHAR2 := fnd_api.g_false,
11 x_msg_data OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2
14 ) IS
15
16 l_api_name VARCHAR2(50);
17 l_api_version CONSTANT NUMBER := 1.0;
18
19 l_user_id NUMBER;
20
21 BEGIN
22 l_api_name := 'Update_contract_usages';
23 l_user_id := FND_GLOBAL.user_id();
24
25 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
26 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
27 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
28 END IF;
29
30 -- Standard call to check for call compatibility.
31 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
32 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
33 END IF;
34
35 --Update the okc_rep_contract_usages table
36 INSERT INTO okc_rep_contract_usages ( sequence_id,
37 contract_type,
38 contract_id,
39 contract_version,
40 business_document_type,
41 business_document_id,
42 business_document_version,
43 document_number,
44 PK1_VALUE,
45 PK2_VALUE,
46 PK3_VALUE,
47 PK4_VALUE,
48 PK5_VALUE,
49 source_code,
50 relationship_id,
51 display_in_contract,
52 run_id,
53 object_version_number,
54 last_update_login,
55 creation_date,
56 created_by,
57 last_updated_by,
58 last_update_date
59 )
60 VALUES (okc_rep_contract_usages_s.NEXTVAL,
61 p_contract_rec.contract_type,
62 p_contract_rec.contract_id,
63 p_contract_rec.contract_version_num,
64 p_document_rec.business_document_type,
65 p_document_rec.business_document_id,
66 p_document_rec.business_document_version,
67 p_document_rec.document_number,
68 p_document_rec.pk1_value,
69 p_document_rec.pk2_value,
70 p_document_rec.pk3_value,
71 p_document_rec.pk4_value,
72 p_document_rec.pk5_value,
73 p_document_rec.source_code,
74 p_document_rec.relationship_id,
75 p_document_rec.display_in_contract,
76 p_contract_rec.run_id,
77 1.0,
78 l_user_id,
79 SYSDATE,
80 l_user_id,
81 l_user_id,
82 SYSDATE
83 );
84
85 x_return_status := 'S';
86
87 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
88 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
89 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
90 END IF;
91
92 END Update_contract_usages;
93
94 /*
95 Updates the okc_contract_usages table with the relationships provided
96 */
97 PROCEDURE delete_contract_usages(p_api_version IN NUMBER,
98 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
99 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
100 p_commit IN VARCHAR2 := fnd_api.g_false,
101 x_msg_data OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2
104 ) IS
105
106 l_api_name VARCHAR2(50);
107 l_api_version CONSTANT NUMBER := 1.0;
108
109 l_user_id NUMBER;
110
111 BEGIN
112 l_api_name := 'delete_contract_usages';
113 l_user_id := FND_GLOBAL.user_id();
114
115 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
117 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
118 END IF;
119
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 -- Standard call to check for call compatibility.
123 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 --Delete from the okc_rep_contract_usages table
128 DELETE FROM okc_rep_contract_usages
129 WHERE contract_type = p_contract_rec.contract_type
130 AND contract_id = p_contract_rec.contract_id
131 AND contract_version = p_contract_rec.contract_version_num;
132
133 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
135 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
136 END IF;
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
142 g_module || l_api_name,
143 'Leaving delete_contract_usages because of EXCEPTION: ' || sqlerrm);
144 END IF;
145 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
146 p_msg_name => G_UNEXPECTED_ERROR,
147 p_token1 => G_SQLCODE_TOKEN,
148 p_token1_value => sqlcode,
149 p_token2 => G_SQLERRM_TOKEN,
150 p_token2_value => sqlerrm);
151
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153 FND_MSG_PUB.Count_And_Get(
154 p_count => x_msg_count,
155 p_data => x_msg_data
156 );
157
158 END delete_contract_usages;
159
160
161 Procedure create_contract(p_api_version IN NUMBER,
162 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
163 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
164 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
165 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
166 p_create_contract IN VARCHAR2 := fnd_api.g_true,
167 p_commit IN VARCHAR2 := fnd_api.g_false,
168 x_msg_data OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_return_status OUT NOCOPY VARCHAR2
171 )
172 IS
173
174 l_api_name VARCHAR2(150);
175 l_run_id NUMBER;
176 l_imp_contract_id NUMBER;
177 l_number_inserted NUMBER;
178 l_api_version CONSTANT NUMBER := 1.0;
179
180 BEGIN
181
182 l_api_name := 'create_contract';
183
184 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
185 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
186 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
187 END IF;
188
189 -- Standard call to check for call compatibility.
190 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192 END IF;
193
194 -- Initialize API return status to success
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 IF p_create_contract = fnd_api.g_true THEN
198 SELECT okc_rep_import_run_id_s.NEXTVAL INTO l_run_id FROM dual;
199 END IF;
200
201 --Call API to populate Header and controls
202 populate_contract_hdr(p_api_version => p_api_version,
203 p_contract_rec => p_contract_rec,
204 p_run_id => l_run_id,
205 x_imp_contract_id => l_imp_contract_id,
206 x_msg_data => x_msg_data,
207 x_msg_count => x_msg_count,
208 x_return_status => x_return_status);
209
210 --Call APi to populate parties
211 IF(p_contract_parties_tbl IS NOT NULL) THEN
212
213 populate_contract_parties (p_api_version => p_api_version,
214 p_run_id => l_run_id,
215 p_contract_parties_tbl => p_contract_parties_tbl,
216 p_imp_contract_id => l_imp_contract_id,
217 x_msg_data => x_msg_data,
218 x_msg_count => x_msg_count,
219 x_return_status => x_return_status);
220 END IF;
221
222 --Call API to populate parties
223 IF(p_party_contacts_tbl IS NOT NULL) THEN
224
225 populate_party_contacts(p_api_version => p_api_version,
226 p_run_id => l_run_id,
227 p_party_contacts_tbl => p_party_contacts_tbl,
228 p_imp_contract_id => l_imp_contract_id,
229 x_msg_data => x_msg_data,
230 x_msg_count => x_msg_count,
231 x_return_status => x_return_status);
232 END IF;
233 --Call API to populate risks
234 IF(p_risks_tbl IS NOT NULL) THEN
235
236 populate_contract_risks(p_api_version => p_api_version,
237 p_run_id => l_run_id,
238 p_risks_tbl => p_risks_tbl,
239 p_imp_contract_id => l_imp_contract_id,
240 x_msg_data => x_msg_data,
241 x_msg_count => x_msg_count,
242 x_return_status => x_return_status);
243 END IF;
244
245 IF p_create_contract = fnd_api.g_true THEN
246 --Call validate and insert contract method to do the validation
247 OKC_REP_UTIL_PVT.validate_and_insert_contracts(p_api_version => p_api_version,
248 p_init_msg_list => fnd_api.g_FALSE,
249 p_run_id => l_run_id,
250 x_msg_data => x_msg_data,
251 x_msg_count => x_msg_count,
252 x_return_status => x_return_status,
253 x_number_inserted => l_number_inserted);
254 END IF;
255
256 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
257 SELECT contract_type, contract_id, contract_version_num, run_id, valid_flag
258 INTO p_contract_rec.contract_type, p_contract_rec.contract_id,
259 p_contract_rec.contract_version_num, p_contract_rec.run_id, p_contract_rec.valid_flag
260 FROM okc_rep_imp_contracts_t
261 WHERE run_id = l_run_id;
262 END IF;
263
264 IF (p_contract_rec.valid_flag <> 'Y') THEN
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 x_msg_count := 1;
267 SELECT error_message INTO x_msg_data
268 FROM okc_rep_imp_errors_t
269 WHERE run_id = p_contract_rec.run_id;
270 END IF;
271
272 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
273 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
274 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
275 END IF;
276
277
278 END create_contract;
279
280 Procedure populate_contract_hdr(p_api_version IN NUMBER,
281 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
282 p_run_id IN NUMBER ,
283 x_imp_contract_id OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2
287 )
288 IS
289
290 l_api_name VARCHAR2(150);
291 l_api_version CONSTANT NUMBER := 1.0;
292
293 BEGIN
294
295 l_api_name := 'populate_contract_hdr';
296
297 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
298 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
299 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
300 END IF;
301
302 -- Standard call to check for call compatibility.
303 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307 SELECT OKC_REP_IMP_CONTRACTS_T_S.NEXTVAL INTO x_imp_contract_id FROM dual;
308
309
310 INSERT INTO okc_rep_imp_contracts_t(run_id,
311 imp_contract_id,
312 contract_number ,
313 contract_name ,
314 contract_version_num ,
315 description ,
316 contract_type_txt ,
317 authoring_party_txt ,
318 contract_status_txt ,
319 version_comments ,
320 contract_effective_date ,
321 contract_expiration_date ,
322 currency_code ,
323 contract_amount ,
324 org_name ,
325 owner_user_name ,
326 keywords ,
327 physical_location ,
328 orig_system_reference_code ,
329 orig_system_reference_id1 ,
330 orig_system_reference_id2 ,
331 creation_date ,
332 latest_signed_ver_number ,
333 overall_risk_txt ,
334 cancellation_comments ,
335 cancellation_date ,
336 termination_comments ,
337 termination_date ,
338 expire_ntf_flag ,
339 expire_ntf_period ,
340 notify_contact_role_id ,
341 attribute_category ,
342 attribute1 ,
343 attribute2 ,
344 attribute3 ,
345 attribute4 ,
346 attribute5 ,
347 attribute6 ,
348 attribute7 ,
349 attribute8 ,
350 attribute9 ,
351 attribute10 ,
352 attribute11 ,
353 attribute12 ,
354 attribute13 ,
355 attribute14 ,
356 attribute15 ,
357 reference_document_type ,
358 reference_document_number,
359 reference_document_id)
360
361 VALUES(p_run_id,
362 x_imp_contract_id,
363 p_contract_rec.contract_number ,
364 p_contract_rec.contract_name ,
365 p_contract_rec.contract_version_num ,
366 p_contract_rec.description ,
367 p_contract_rec.contract_type_txt ,
368 p_contract_rec.authoring_party_txt ,
369 p_contract_rec.contract_status_txt ,
370 p_contract_rec.version_comments ,
371 p_contract_rec.contract_effective_date ,
372 p_contract_rec.contract_expiration_date ,
373 p_contract_rec.currency_code ,
374 p_contract_rec.contract_amount ,
375 p_contract_rec.org_name ,
376 p_contract_rec.owner_user_name ,
377 p_contract_rec.keywords ,
378 p_contract_rec.physical_location ,
379 p_contract_rec.orig_system_reference_code ,
380 p_contract_rec.orig_system_reference_id1 ,
381 p_contract_rec.orig_system_reference_id2 ,
382 p_contract_rec.creation_date ,
383 p_contract_rec.latest_signed_ver_number ,
384 p_contract_rec.overall_risk_txt ,
385 p_contract_rec.cancellation_comments ,
386 p_contract_rec.cancellation_date ,
387 p_contract_rec.termination_comments ,
388 p_contract_rec.termination_date ,
389 p_contract_rec.expire_ntf_flag ,
390 p_contract_rec.expire_ntf_period ,
391 p_contract_rec.notify_contact_role_id ,
392 p_contract_rec.attribute_category ,
393 p_contract_rec.attribute1 ,
394 p_contract_rec.attribute2 ,
395 p_contract_rec.attribute3 ,
396 p_contract_rec.attribute4 ,
397 p_contract_rec.attribute5 ,
398 p_contract_rec.attribute6 ,
399 p_contract_rec.attribute7 ,
400 p_contract_rec.attribute8 ,
401 p_contract_rec.attribute9 ,
402 p_contract_rec.attribute10 ,
403 p_contract_rec.attribute11 ,
404 p_contract_rec.attribute12 ,
405 p_contract_rec.attribute13 ,
406 p_contract_rec.attribute14 ,
407 p_contract_rec.attribute15 ,
408 p_contract_rec.reference_document_type ,
409 p_contract_rec.reference_document_number,
410 p_contract_rec.reference_document_id);
411
412
413 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
415 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
416 END IF;
417
418 END populate_contract_hdr;
419
420 PROCEDURE populate_contract_parties (p_api_version IN NUMBER,
421 p_run_id IN NUMBER ,
422 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
423 p_imp_contract_id IN NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_return_status OUT NOCOPY VARCHAR2)
427 IS
428 l_api_name VARCHAR2(150);
429 l_party_id NUMBER;
430 l_api_version CONSTANT NUMBER := 1.0;
431
432 BEGIN
433
434 l_api_name := 'populate_contract_parties';
435
436 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
437 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
438 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
439 END IF;
440
441 -- Standard call to check for call compatibility.
442 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
444 END IF;
445
446 IF p_contract_parties_tbl.COUNT > 0
447 THEN
448 FOR i IN p_contract_parties_tbl.FIRST .. p_contract_parties_tbl.LAST
449 LOOP
450
451 INSERT INTO okc_rep_imp_parties_t(imp_contract_id,
452 imp_party_id,
453 party_index,
454 signed_by_txt,
455 signed_date,
456 party_name_txt,
457 party_role_txt,
458 run_id)
459
460 VALUES(p_imp_contract_id,
461 OKC_REP_IMP_PARTIES_T_S.NEXTVAL,
462 p_contract_parties_tbl(i).party_index,
463 p_contract_parties_tbl(i).signed_by_txt,
464 p_contract_parties_tbl(i).signed_date,
465 p_contract_parties_tbl(i).party_name_txt,
466 p_contract_parties_tbl(i).party_role_txt,
467 p_run_id);
468 END LOOP;
469 END IF;
470
471 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
472 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
473 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
474 END IF;
475
476 END populate_contract_parties;
477
478 PROCEDURE populate_party_contacts(p_api_version IN NUMBER,
479 p_run_id IN NUMBER,
480 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
481 p_imp_contract_id IN NUMBER,
482 x_msg_data OUT NOCOPY VARCHAR2,
483 x_msg_count OUT NOCOPY NUMBER,
484 x_return_status OUT NOCOPY VARCHAR2)
485 IS
486 l_api_name VARCHAR2(150);
487 l_api_version CONSTANT NUMBER := 1.0;
488 l_error_msg VARCHAR2(2000);
489
490 l_party_id NUMBER;
491
492 BEGIN
493
494 l_api_name := 'populate_party_contacts';
495
496 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
498 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
499 END IF;
500
501 -- Standard call to check for call compatibility.
502 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505
506
507 IF p_party_contacts_tbl.COUNT > 0
508 THEN
509 FOR i IN p_party_contacts_tbl.FIRST .. p_party_contacts_tbl.LAST
510 LOOP
511 BEGIN
512 SELECT imp_party_id INTO l_party_id
513 FROM okc_rep_imp_parties_t
514 WHERE imp_contract_id = p_imp_contract_id
515 AND party_role_txt = p_party_contacts_tbl(i).party_role_txt
516 AND party_name_txt = p_party_contacts_tbl(i).party_name_txt;
517 EXCEPTION
518 WHEN No_Data_Found THEN
519 --PARTY_NOT_EXIST - Party doesn't exist
520 fnd_message.set_name(G_APP_NAME,'OKC_REP_IMP_INV_PARTY_NAME');
521 fnd_message.set_token(TOKEN => 'PARTY_INDEX',
522 VALUE => p_party_contacts_tbl(i).contact_index);
523 l_error_msg := fnd_message.get;
524
525 END;
526
527 INSERT INTO okc_rep_imp_contacts_t(imp_contract_id,
528 imp_party_id,
529 imp_contact_id,
530 contact_index,
531 contact_id,
532 party_role_txt,
533 party_name_txt,
534 contact_name,
535 contact_role_txt,
536 run_id)
537 VALUES (p_imp_contract_id,
538 l_party_id,
539 OKC_REP_IMP_CONTACTS_T_S.NEXTVAL,
540 p_party_contacts_tbl(i).contact_index,
541 p_party_contacts_tbl(i).contact_id,
542 p_party_contacts_tbl(i).party_role_txt,
543 p_party_contacts_tbl(i).party_name_txt,
544 p_party_contacts_tbl(i).contact_name_txt,
545 p_party_contacts_tbl(i).contact_role_txt,
546 p_run_id);
547 END LOOP;
548 END IF;
549
550 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
552 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
553 END IF;
554
555 END populate_party_contacts;
556
557 PROCEDURE populate_contract_risks(p_api_version IN NUMBER,
558 p_run_id IN NUMBER DEFAULT NULL,
559 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
560 p_imp_contract_id IN NUMBER,
561 x_msg_data OUT NOCOPY VARCHAR2,
562 x_msg_count OUT NOCOPY NUMBER,
563 x_return_status OUT NOCOPY VARCHAR2)
564 IS
565 l_api_name VARCHAR2(150);
566 l_api_version CONSTANT NUMBER := 1.0;
567
568 BEGIN
569
570 l_api_name := 'populate_contract_risks ';
571
572 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
574 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
575 END IF;
576
577 -- Standard call to check for call compatibility.
578 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581
582
583 IF p_risks_tbl.COUNT > 0
584 THEN
585 FOR i IN p_risks_tbl.FIRST .. p_risks_tbl.LAST
586 LOOP
587
588 INSERT INTO okc_rep_imp_risks_t(imp_contract_id,
589 imp_risk_id,
590 risk_event_txt,
591 probability_txt,
592 risk_impact_txt,
593 risk_comments,
594 risk_occured_YN,
595 risk_occurence_date,
596 run_id)
597 --created_by)
598
599 VALUES(p_imp_contract_id,
600 OKC_REP_IMP_RISKS_T_S.nextval,
601 p_risks_tbl(i).risk_event_txt,
602 p_risks_tbl(i).probability_txt,
603 p_risks_tbl(i).risk_impact_txt,
604 p_risks_tbl(i).risk_comments,
605 p_risks_tbl(i).risk_occured_YN,
606 p_risks_tbl(i).risk_occurence_date,
607 p_run_id);
608 --p_risks_tbl(i).created_by);
609 END LOOP;
610 END IF;
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 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
615 END IF;
616
617 END populate_contract_risks;
618
619 Procedure delete_contract(p_api_version IN NUMBER,
620 p_commit IN VARCHAR2 := fnd_api.g_false,
621 p_contract_id IN NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY NUMBER,
624 x_return_status OUT NOCOPY VARCHAR2
625 )
626 IS
627 l_api_name VARCHAR2(150);
628 l_api_version CONSTANT NUMBER := 1.0;
629
630 l_contract_status VARCHAR2(30);
631
632 CURSOR c_get_contract_status IS
633 SELECT contract_status_code FROM okc_rep_contracts_all
634 WHERE contract_id = p_contract_id;
635
636 BEGIN
637 l_api_name := 'delete_contract ';
638
639 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
640 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
641 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
642 END IF;
643
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645
646 -- Standard call to check for call compatibility.
647 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 END IF;
650
651 OPEN c_get_contract_status;
652 FETCH c_get_contract_status INTO l_contract_status;
653 CLOSE c_get_contract_status;
654
655 IF l_contract_status NOT IN ('SIGNED', 'TERMINATED') THEN
656
657 -- Delete contract
658 okc_rep_contract_process_pvt.delete_contract( p_api_version => 1.0,
659 p_init_msg_list => FND_API.G_FALSE,
660 p_commit => p_commit,
661 p_contract_id => p_contract_id,
662 x_msg_data => x_msg_data,
663 x_msg_count => x_msg_count,
664 x_return_status => x_return_status);
665 ELSE
666
667 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
668 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
669 'Delete cannot be performed on a contract in status signed/terminated');
670 END IF;
671
672 END IF;
673
674 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
675 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
676 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
677 END IF;
678
679 EXCEPTION
680 WHEN OTHERS THEN
681 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
683 g_module || l_api_name,
684 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
685 END IF;
686 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
687 p_msg_name => G_UNEXPECTED_ERROR,
688 p_token1 => G_SQLCODE_TOKEN,
689 p_token1_value => sqlcode,
690 p_token2 => G_SQLERRM_TOKEN,
691 p_token2_value => sqlerrm);
692 --close cursors
693 IF (c_get_contract_status%ISOPEN) THEN
694 CLOSE c_get_contract_status ;
695 END IF;
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 FND_MSG_PUB.Count_And_Get(
698 p_count => x_msg_count,
699 p_data => x_msg_data
700 );
701
702 END delete_contract;
703
704 PROCEDURE validate_and_insert_con_cp(errbuf OUT NOCOPY VARCHAR2,
705 retcode OUT NOCOPY VARCHAR2,
706 p_org_name IN NUMBER,
707 p_contract_type IN VARCHAR2,
708 p_from_date IN DATE,
709 p_to_date IN DATE,
710 p_validate_only IN VARCHAR2 )
711 IS
712
713 l_api_name VARCHAR2(150);
714 l_api_version CONSTANT NUMBER := 1.0;
715
716 l_run_id NUMBER;
717 l_where_clause VARCHAR2(2000);
718 l_update_query VARCHAR2(5000);
719 l_row_notfound BOOLEAN := FALSE;
720 l_organization_name HR_ORGANIZATION_UNITS.NAME%TYPE;
721 l_request_id okc_rep_imp_contracts_t.request_id%TYPE := fnd_global.conc_request_id;
722 l_contract_type VARCHAR2(30);
723
724 l_return_status VARCHAR2(1);
725 l_msg_count NUMBER;
726 l_msg_data VARCHAR2(2000);
727 l_number_inserted NUMBER;
728 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
729 l_count NUMBER;
730
731 CURSOR c_org_info_csr IS
732 SELECT ORGANIZATION_ID
733 FROM HR_ALL_ORGANIZATION_UNITS
734 WHERE UPPER(NAME) = UPPER(p_org_name)
735 AND mo_global.check_access(ORGANIZATION_ID) = 'Y';
736
737 CURSOR c_contract_type_valid_csr IS
738 SELECT document_type
739 FROM OKC_BUS_DOC_TYPES_V
740 WHERE Upper(name) = Upper(p_contract_type)
741 AND document_type_class = 'REPOSITORY'
742 AND TRUNC(SYSDATE) BETWEEN
743 NVL(START_DATE, TRUNC(SYSDATE -1)) AND
744 NVL(END_DATE, TRUNC(SYSDATE +1));
745
746 BEGIN
747 l_api_name := 'validate_and_insert_con_cp ';
748
749 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
751 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
752 END IF;
753
754 FND_MSG_PUB.initialize;
755 retcode := 0;
756
757 -- First determine whether the orgid passed is a valid org defined in HR Org definitions.
758 IF p_org_name IS NOT NULL THEN
759 OPEN c_org_info_csr;
760 FETCH c_org_info_csr INTO l_organization_name;
761 l_row_notfound := c_org_info_csr%NOTFOUND;
762 CLOSE c_org_info_csr ;
763
764 IF (l_row_notfound) THEN
765 IF (l_debug = 'Y') THEN
766 okc_debug.log('200: Org row not found', 2);
767 END IF;
768 FND_MESSAGE.SET_NAME(G_APP_NAME, 'OKC_REP_IMP_INV_ORG');
769 FND_MSG_PUB.add;
770 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(1, p_encoded =>FND_API.G_FALSE ));
771 retcode := 2;
772 return;
773 END IF;
774 END IF;
775
776 --verify if contract type is valid
777 IF p_contract_type IS NOT NULL THEN
778 OPEN c_contract_type_valid_csr;
779 FETCH c_contract_type_valid_csr INTO l_contract_type;
780 l_row_notfound := c_org_info_csr%NOTFOUND;
781 CLOSE c_contract_type_valid_csr ;
782
783 IF (l_row_notfound) THEN
784 IF (l_debug = 'Y') THEN
785 okc_debug.log('300: Contract Type not found', 2);
786 END IF;
787 FND_MESSAGE.SET_NAME(G_APP_NAME, 'OKC_REP_INV_CONTRACT_TYPE');
788 FND_MSG_PUB.add;
789 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(1, p_encoded =>FND_API.G_FALSE ));
790 retcode := 2;
791 return;
792 END IF;
793 END IF;
794
795 --identify the records that have to be imported in the current run.
796
797 IF p_org_name IS NOT NULL THEN
798 l_where_clause := ' AND org_name = ' || p_org_name ;
799 END IF;
800
801 IF p_contract_type IS NOT NULL THEN
802 l_where_clause := ' and contract_type_txt = ' || p_contract_type ;
803 END IF;
804 IF p_from_date IS NOT NULL THEN
805 l_where_clause := ' and creation_date >= ' ||p_from_date ;
806 END IF;
807 IF p_to_date IS NOT NULL THEN
808 l_where_clause := ' and creation_date <= ' || p_to_date;
809 END IF;
810
811 --create a run id sequence and populate the run_id column in all the interface tables
812
813 SELECT okc_rep_import_run_id_s.NEXTVAL INTO l_run_id FROM dual;
814
815 --stamp the request id column for the records that are being imported.
816 --update OKC_REP_IMP_CONTRACTS_T with the run_id and request_id generated
817
818 l_update_query := 'UPDATE OKC_REP_IMP_CONTRACTS_T
819 SET run_id = '|| l_run_id||' , request_id = ' || l_request_id ||
820 ' WHERE nvl(valid_flag, ''U'') IN (''U'', ''Y'')
821 AND run_id IS NULL ' || l_where_clause;
822
823 IF (l_debug = 'Y') THEN
824 okc_debug.log('400: Before Execute Immediate l_update_query: '|| l_update_query, 2);
825 END IF;
826
827 EXECUTE IMMEDIATE (l_update_query);
828
829 l_count := SQL%ROWCOUNT;
830
831 IF (l_debug = 'Y') THEN
832 okc_debug.log('500: After Execute Immediate l_count: '|| l_count, 2);
833 END IF;
834
835 IF l_count <= 0 THEN
836 IF (l_debug = 'Y') THEN
837 okc_debug.log('600: No Rows found to import');
838 END IF;
839 FND_MESSAGE.SET_NAME(G_APP_NAME, 'OKC_REP_IMP_NO_VALID_ROWS');
840 FND_MSG_PUB.add;
841 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(1, p_encoded =>FND_API.G_FALSE ));
842 retcode := 1;
843 return;
844 END IF;
845
846 --Update valid_flag = 'U' for records with valid flag null
847 UPDATE OKC_REP_IMP_CONTRACTS_T
848 SET valid_flag = 'U'
849 WHERE run_id = l_run_id
850 AND valid_flag IS NULL;
851
852 --update child tables run_id for all the records with imp_contract_id = imp_contract_id
853 --from OKC_REP_IMP_CONTRACTS_T with the run_id generated
854
855 --update okc_rep_imp_parties_t
856 UPDATE okc_rep_imp_parties_t
857 SET run_id = l_run_id,
858 request_id = l_request_id
859 WHERE imp_contract_id IN (SELECT imp_contract_id
860 FROM OKC_REP_IMP_CONTRACTS_T
861 WHERE run_id = l_run_id);
862
863 IF (l_debug = 'Y') THEN
864 okc_debug.log('700: After UPDATE okc_rep_imp_parties_t ', 1);
865 END IF;
866
867 --update okc_rep_imp_contacts_t
868 UPDATE okc_rep_imp_contacts_t
869 SET run_id = l_run_id,
870 request_id = l_request_id
871 WHERE imp_contract_id IN (SELECT imp_contract_id
872 FROM OKC_REP_IMP_CONTRACTS_T
873 WHERE run_id = l_run_id);
874
875 IF (l_debug = 'Y') THEN
876 okc_debug.log('800: After UPDATE okc_rep_imp_contacts_t ', 1);
877 END IF;
878
879 --update okc_rep_imp_risks_t
880 UPDATE okc_rep_imp_risks_t
881 SET run_id = l_run_id,
882 request_id = l_request_id
883 WHERE imp_contract_id IN (SELECT imp_contract_id
884 FROM OKC_REP_IMP_CONTRACTS_T
885 WHERE run_id = l_run_id);
886
887 IF (l_debug = 'Y') THEN
888 okc_debug.log('900: After UPDATE okc_rep_imp_risks_t ', 1);
889 END IF;
890
891
892 --update OKC_REP_IMP_DOCUMENTS_T
893 UPDATE OKC_REP_IMP_DOCUMENTS_T
894 SET run_id = l_run_id,
895 request_id = l_request_id
896 WHERE imp_contract_id IN (SELECT imp_contract_id
897 FROM OKC_REP_IMP_CONTRACTS_T
898 WHERE run_id = l_run_id);
899
900 IF (l_debug = 'Y') THEN
901 okc_debug.log('1000: After UPDATE OKC_REP_IMP_DOCUMENTS_T ', 1);
902 END IF;
903
904 --call the OKC_REP_UTIL_PVT.validate_and_insert_contracts
905
906 okc_rep_util_pvt.validate_and_insert_contracts( p_api_version => 1.0,
907 p_init_msg_list => FND_API.G_FALSE,
908 p_run_id => l_run_id,
909 p_call_source => 'CP_SQL',
910 p_validate_only => p_validate_only,
911 x_msg_data => l_msg_data,
912 x_msg_count => l_msg_count,
913 x_return_status => l_return_status,
914 x_number_inserted => l_number_inserted);
915 IF (l_debug = 'Y') THEN
916 okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_msg_data: '|| l_msg_data, 1);
917 okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_msg_count: '|| l_msg_count, 1);
918 okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_return_status: '||l_return_status, 1);
919 okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_number_inserted: ' ||l_number_inserted, 1);
920 END IF;
921
922 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
923 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
924 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
925 END IF;
926
927 EXCEPTION
928 WHEN OTHERS THEN
929 IF (l_debug = 'Y') THEN
930 okc_debug.log('1500: Leaving validate_and_insert_con_cp because of EXCEPTION: '||sqlerrm);
931 END IF;
932 l_return_status := G_RET_STS_UNEXP_ERROR ;
933 IF c_org_info_csr%ISOPEN Then
934 close c_org_info_csr;
935 END IF;
936 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
937 errbuf := substr(sqlerrm,1,200);
938 retcode := 2;
939
940 END validate_and_insert_con_cp;
941
942 /*
943
944 PROCEDURE delete_contract_risks(p_api_version IN NUMBER,
945 p_run_id IN NUMBER DEFAULT NULL,
946 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
947 p_imp_contract_id IN NUMBER,
948 x_msg_data OUT NOCOPY VARCHAR2,
949 x_msg_count OUT NOCOPY NUMBER,
950 x_return_status OUT NOCOPY VARCHAR2)
951 IS
952 l_api_name VARCHAR2(150);
953 l_api_version CONSTANT NUMBER := 1.0;
954
955 CURSOR c_risk_event_valid_csr(p_risk_event_txt VARCHAR2) IS
956 SELECT RISK_EVENT_ID
957 FROM okc_risk_events_tl
958 WHERE Upper(name) = Upper(p_risk_event_txt)
959 AND LANGUAGE = userenv('LANG');
960
961
962 CURSOR c_risk_present_on_cont(p_document_type varchar2, p_document_id NUMBER, p_document_version NUMBER, p_risk_event_id varchar2) IS
963 SELECT risk_event_id
964 FROM okc_contract_risks
965 WHERE business_document_type = p_document_type
966 AND business_document_id = p_document_id
967 AND business_document_version = p_document_version
968 AND risk_event_id = risk_event_id;
969
970 BEGIN
971
972 l_api_name := 'delete_contract_risks ';
973
974 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
975 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
976 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
977 END IF;
978
979 -- Standard call to check for call compatibility.
980 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982 END IF;
983
984
985 IF p_risks_tbl.COUNT > 0
986 THEN
987 FOR i IN p_risks_tbl.FIRST .. p_risks_tbl.LAST
988 LOOP
989 --Check whether the risk exists in the given contract
990 --Initialize l_valid_flag for every record
991 l_valid_flag := 'Y';
992 --Initialize l_error_msg for every record
993 l_error_msg := NULL;
994
995 --If l_valid_flag is already set to 'N', we do not perform any more checks
996 --Business_document_type should be entered
997 --Business_document_id should be entered
998 --Business_document_version should be entered
999
1000 --Risk Event should be entered
1001 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1002 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1003 'Checking if user enters risk_event_txt');
1004 END IF;
1005 IF p_risks_tbl(i).risk_event_txt IS NOT NULL THEN
1006 p_risks_tbl(i).risk_event_txt := LTRIM(p_risks_tbl(i).risk_event_txt);
1007 p_risks_tbl(i).risk_event_txt := RTRIM(p_risks_tbl(i).risk_event_txt);
1008 END IF;
1009 IF (l_valid_flag = 'Y' AND (p_risks_tbl(i).risk_event_txt IS NULL OR LENGTH(p_risks_tbl(i).risk_event_txt)=0)) THEN
1010 l_valid_flag := 'N';
1011
1012 fnd_message.set_name(G_APP_NAME,'OKC_REP_IMP_MISS_RISK_EVENT');
1013 fnd_message.set_token(TOKEN => 'RISK_INDEX',
1014 VALUE => p_risks_tbl(i).risk_index);
1015 l_error_msg := fnd_message.get;
1016 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1017 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1018 'Party Name is missing');
1019 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1020 'Contract ID: '||l_import_risks_rec.imp_contract_id);
1021 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1022 'l_error_msg: '||l_error_msg);
1023 END IF;
1024 END IF;
1025
1026 --If l_valid_flag is already set to 'N', we do not perform any more checks
1027 --Risk Event should be valid
1028 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1029 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1030 'Checking if risk_event_txt is valid');
1031 END IF;
1032
1033 OPEN c_risk_event_valid_csr(l_import_risks_rec.risk_event_txt);
1034 FETCH c_risk_event_valid_csr INTO l_risk_event_id;
1035
1036 IF c_risk_event_valid_csr%NOTFOUND THEN
1037
1038
1039 IF (l_valid_flag = 'Y') THEN
1040 l_valid_flag := 'N';
1041
1042 fnd_message.set_name(G_APP_NAME,'OKC_REP_IMP_INV_RISK_PROB');
1043 fnd_message.set_token(TOKEN => 'RISK_INDEX',
1044 VALUE => l_import_risks_rec.risk_index);
1045 l_error_msg := fnd_message.get;
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 'Risk Event is not valid ');
1049 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1050 'Contract ID: '||l_import_risks_rec.imp_contract_id);
1051 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1052 'Risk Event: '||l_import_risks_rec.risk_event_txt);
1053 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1054 'l_error_msg: '||l_error_msg);
1055 END IF;
1056
1057 END IF;
1058 END IF;
1059 CLOSE c_risk_event_valid_csr;
1060
1061
1062
1063
1064
1065 --If l_valid_flag is already set to 'N', we do not perform any more checks
1066 --Risk Event should be present on the contract
1067 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1068 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1069 'Checking if risk_event_txt is valid');
1070 END IF;
1071
1072 OPEN c_risk_event_valid_csr(p_risks_tbl(i).risk_event_txt);
1073 FETCH c_risk_event_valid_csr INTO l_risk_event_id;
1074
1075 IF c_risk_event_valid_csr%NOTFOUND THEN
1076
1077
1078 IF (l_valid_flag = 'Y') THEN
1079 l_valid_flag := 'N';
1080
1081 fnd_message.set_name(G_APP_NAME,'OKC_REP_IMP_RISK_NOT_AVAIL');
1082 fnd_message.set_token(TOKEN => 'RISK_INDEX',
1083 VALUE => l_import_risks_rec.risk_index);
1084 l_error_msg := fnd_message.get;
1085 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1087 'Risk Event is not valid ');
1088 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1089 'Contract ID: '||l_import_risks_rec.imp_contract_id);
1090 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1091 'Risk Event: '||l_import_risks_rec.risk_event_txt);
1092 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1093 'l_error_msg: '||l_error_msg);
1094 END IF;
1095
1096 END IF;
1097 END IF;
1098 CLOSE c_risk_event_valid_csr;
1099
1100
1101 END LOOP;
1102 END IF;
1103
1104 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1105 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1106 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1107 END IF;
1108
1109 END delete_contract_risks;
1110
1111
1112 PROCEDURE delete_party_contacts(p_api_version IN NUMBER,
1113 p_run_id IN NUMBER,
1114 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
1115 p_imp_contract_id IN NUMBER,
1116 x_msg_data OUT NOCOPY VARCHAR2,
1117 x_msg_count OUT NOCOPY NUMBER,
1118 x_return_status OUT NOCOPY VARCHAR2)
1119 IS
1120 l_api_name VARCHAR2(150);
1121 l_api_version CONSTANT NUMBER := 1.0;
1122
1123 BEGIN
1124
1125 l_api_name := 'delete_party_contacts ';
1126
1127 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1128 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1129 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1130 END IF;
1131
1132 -- Standard call to check for call compatibility.
1133 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137
1138 IF p_party_contacts_tbl.COUNT > 0
1139 THEN
1140 FOR i IN p_party_contacts_tbl.FIRST .. p_party_contacts_tbl.LAST
1141 LOOP
1142
1143 END LOOP;
1144 END IF;
1145
1146 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1148 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1149 END IF;
1150
1151 END delete_party_contacts;
1152 */
1153
1154 Procedure create_contract(p_api_version IN NUMBER,
1155 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1156 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
1157 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
1158 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
1159 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
1160 p_commit IN VARCHAR2 := fnd_api.g_false,
1161 x_msg_data OUT NOCOPY VARCHAR2,
1162 x_msg_count OUT NOCOPY NUMBER,
1163 x_return_status OUT NOCOPY VARCHAR2
1164 ) IS
1165
1166 l_api_name VARCHAR2(50);
1167 l_api_version CONSTANT NUMBER := 1.0;
1168
1169 l_user_id NUMBER;
1170 l_return_status VARCHAR2(1);
1171
1172 l_msg_count NUMBER;
1173
1174 l_msg_data VARCHAR2(2000);
1175 --l_return_status VARCHAR2(1);
1176
1177 BEGIN
1178 l_api_name := 'create_contract';
1179 l_user_id := FND_GLOBAL.user_id();
1180
1181 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1182 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1183 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1184 END IF;
1185
1186 -- Standard call to check for call compatibility.
1187 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1189 END IF;
1190
1191 x_return_status := FND_API.G_RET_STS_SUCCESS;
1192
1193 --Call first create Contract API to create the contract
1194 create_contract(p_api_version => p_api_version,
1195 p_contract_rec => p_contract_rec,
1196 p_contract_parties_tbl => p_contract_parties_tbl,
1197 p_party_contacts_tbl => p_party_contacts_tbl,
1198 p_risks_tbl => p_risks_tbl,
1199 p_create_contract => fnd_api.g_true,
1200 p_commit => p_commit,
1201 x_msg_data => l_msg_data,
1202 x_msg_count => l_msg_count,
1203 x_return_status => l_return_status);
1204
1205 IF (p_contract_rec.valid_flag = 'Y') THEN
1206
1207 Update_contract_usages(p_api_version => 1.0,
1208 p_contract_rec => p_contract_rec,
1209 p_document_rec => p_document_rec,
1210 p_commit => p_commit,
1211 x_msg_data => l_msg_data,
1212 x_msg_count => l_msg_count,
1213 x_return_status => l_return_status);
1214 /* ELSE
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216 x_msg_count := 1
1217 SELECT error_message FROM okc_rep_imp_errors_t
1218 WHERE run_id = p_contract_rec.run_id; */
1219 END IF;
1220
1221 x_msg_data := l_msg_data;
1222 x_msg_count := l_msg_count;
1223 x_return_status := l_return_status;
1224
1225 IF p_commit = fnd_api.g_true THEN
1226 COMMIT;
1227 END IF;
1228
1229 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1230 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1231 'Leaving OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1232 END IF;
1233
1234 END create_contract;
1235
1236 /*
1237 Deletes the Contract for the given document type.
1238 */
1239
1240 Procedure delete_contract(p_api_version IN NUMBER,
1241 p_commit IN VARCHAR2 := fnd_api.g_false,
1242 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1243 p_contract_type IN VARCHAR2 DEFAULT NULL,
1244 p_contract_id IN NUMBER DEFAULT NULL,
1245 x_msg_data OUT NOCOPY VARCHAR2,
1246 x_msg_count OUT NOCOPY NUMBER,
1247 x_return_status OUT NOCOPY VARCHAR2
1248 )
1249 IS
1250
1251 l_api_name VARCHAR2(50);
1252 l_api_version CONSTANT NUMBER := 1.0;
1253
1254 l_query VARCHAR2(4000);
1255 l_where_clause VARCHAR2(4000);
1256
1257 l_contract_rec OKC_IMP_RECORD_TYPES.contract_rec_type;
1258 l_contract_id NUMBER;
1259
1260 TYPE contract_id_cur IS REF CURSOR;
1261 c_contract_id_cur contract_id_cur;
1262
1263 BEGIN
1264 l_api_name := 'delete_contract';
1265
1266 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1268 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1269 END IF;
1270
1271 -- Standard call to check for call compatibility.
1272 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1274 END IF;
1275
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277
1278 --Find the contracts to be deleted w.r.t the document
1279
1280 l_query := 'SELECT contract_id FROM okc_rep_contract_usages
1281 WHERE business_document_type = ''' || p_document_rec.business_document_type ||
1282 ''' AND business_document_id = ' || p_document_rec.business_document_id;
1283
1284 IF p_document_rec.business_document_version IS NOT NULL THEN
1285 l_where_clause := l_where_clause || ' and business_document_version = ' ||
1286 p_document_rec.business_document_version;
1287 END IF;
1288
1289 IF p_document_rec.pk1_value IS NOT NULL THEN
1290 l_where_clause := l_where_clause || ' and pk1_value = ' || p_document_rec.pk1_value;
1291 END IF;
1292
1293 IF p_document_rec.pk2_value IS NOT NULL THEN
1294 l_where_clause := l_where_clause || ' and pk2_value = ' || p_document_rec.pk2_value;
1295 END IF;
1296
1297 IF p_document_rec.pk3_value IS NOT NULL THEN
1298 l_where_clause := l_where_clause || ' and pk3_value = ' || p_document_rec.pk3_value;
1299 END IF;
1300
1301 IF p_document_rec.pk3_value IS NOT NULL THEN
1302 l_where_clause := l_where_clause || ' and pk4_value = ' || p_document_rec.pk4_value;
1303 END IF;
1304
1305 IF p_document_rec.pk3_value IS NOT NULL THEN
1306 l_where_clause := l_where_clause || ' and pk5_value = ' || p_document_rec.pk5_value;
1307 END IF;
1308
1309 IF p_contract_type IS NOT NULL THEN
1310 l_where_clause := l_where_clause || ' and contract_type = ''' || p_contract_type || '''';
1311 END IF;
1312
1313 IF p_contract_id IS NOT NULL THEN
1314 l_where_clause := l_where_clause || ' and contract_id = ' || p_contract_id;
1315 END IF;
1316
1317 IF l_where_clause IS NOT NULL THEN
1318 l_query := l_query || l_where_clause;
1319 END IF;
1320
1321 OPEN c_contract_id_cur FOR l_query;
1322
1323 --Call in loop for every contract_id identified
1324 LOOP
1325 FETCH c_contract_id_cur INTO l_contract_id;
1326 exit when c_contract_id_cur%notfound;
1327
1328 --populate p_contract_rec
1329 SELECT contract_type, contract_id, contract_version_num, run_id
1330 INTO l_contract_rec.contract_type, l_contract_rec.contract_id,
1331 l_contract_rec.contract_version_num, l_contract_rec.run_id
1332 FROM okc_rep_contracts_all orca
1333 WHERE contract_id = l_contract_id;
1334
1335 --Call the original delete API to delete the conract
1336 delete_contract(p_api_version => p_api_version,
1337 p_contract_id => p_contract_id,
1338 p_commit => p_commit,
1339 x_msg_data => x_msg_data,
1340 x_msg_count => x_msg_count,
1341 x_return_status => x_return_status);
1342
1343 --------------------------------------------
1344 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1346 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1347 RAISE FND_API.G_EXC_ERROR ;
1348 END IF;
1349 --------------------------------------------
1350
1351 delete_contract_usages(p_api_version => p_api_version,
1352 p_contract_rec => l_contract_rec,
1353 p_document_rec => p_document_rec,
1354 p_commit => p_commit,
1355 x_msg_data => x_msg_data,
1356 x_msg_count => x_msg_count,
1357 x_return_status => x_return_status);
1358
1359 --------------------------------------------
1360 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1362 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1363 RAISE FND_API.G_EXC_ERROR ;
1364 END IF;
1365 --------------------------------------------
1366
1367 END LOOP;
1368 CLOSE c_contract_id_cur;
1369 IF FND_API.To_Boolean( p_commit ) THEN
1370 COMMIT;
1371 END IF;
1372
1373 -- Standard call to get message count and if count is 1, get message info.
1374 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1375
1376 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1377 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1378 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1379 END IF;
1380
1381 EXCEPTION
1382
1383 WHEN FND_API.G_EXC_ERROR THEN
1384
1385 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1386 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1387 g_module || l_api_name,
1388 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
1389 END IF;
1390 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1391 p_msg_name => G_UNEXPECTED_ERROR,
1392 p_token1 => G_SQLCODE_TOKEN,
1393 p_token1_value => sqlcode,
1394 p_token2 => G_SQLERRM_TOKEN,
1395 p_token2_value => sqlerrm);
1396 --close cursors
1397 IF (c_contract_id_cur%ISOPEN) THEN
1398 CLOSE c_contract_id_cur ;
1399 END IF;
1400 x_return_status := FND_API.G_RET_STS_ERROR;
1401 FND_MSG_PUB.Count_And_Get(
1402 p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405
1406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1407
1408 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1409 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1410 g_module || l_api_name,
1411 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
1412 END IF;
1413 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1414 p_msg_name => G_UNEXPECTED_ERROR,
1415 p_token1 => G_SQLCODE_TOKEN,
1416 p_token1_value => sqlcode,
1417 p_token2 => G_SQLERRM_TOKEN,
1418 p_token2_value => sqlerrm);
1419 --close cursors
1420 IF (c_contract_id_cur%ISOPEN) THEN
1421 CLOSE c_contract_id_cur ;
1422 END IF;
1423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1424 FND_MSG_PUB.Count_And_Get(
1425 p_count => x_msg_count,
1426 p_data => x_msg_data
1427 );
1428
1429 WHEN OTHERS THEN
1430 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1431 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1432 g_module || l_api_name,
1433 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
1434 END IF;
1435 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1436 p_msg_name => G_UNEXPECTED_ERROR,
1437 p_token1 => G_SQLCODE_TOKEN,
1438 p_token1_value => sqlcode,
1439 p_token2 => G_SQLERRM_TOKEN,
1440 p_token2_value => sqlerrm);
1441 --close cursors
1442 IF (c_contract_id_cur%ISOPEN) THEN
1443 CLOSE c_contract_id_cur ;
1444 END IF;
1445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1446 FND_MSG_PUB.Count_And_Get(
1447 p_count => x_msg_count,
1448 p_data => x_msg_data
1449 );
1450
1451 END delete_contract;
1452
1453 /*
1454 Creates a new version of the contract in draft status.
1455 Updates to the contract should be done from UI.
1456 */
1457
1458 Procedure create_new_contract_version(p_api_version IN NUMBER,
1459 p_contract_id IN NUMBER,
1460 p_run_id IN NUMBER,
1461 p_commit IN VARCHAR2 := fnd_api.g_false,
1462 x_msg_data OUT NOCOPY VARCHAR2,
1463 x_msg_count OUT NOCOPY NUMBER,
1464 x_return_status OUT NOCOPY VARCHAR2
1465 ) IS
1466
1467 l_api_name VARCHAR2(50);
1468 l_api_version CONSTANT NUMBER := 1.0;
1469
1470 l_user_id NUMBER;
1471 l_valid_flag VARCHAR2(1);
1472 l_contract_status VARCHAR2(100);
1473 l_error_code VARCHAR2(20);
1474 l_error_msg VARCHAR2(2000);
1475
1476 CURSOR c_get_contract_status IS
1477 SELECT contract_status_code FROM okc_rep_contracts_all
1478 WHERE contract_id = p_contract_id;
1479
1480
1481 BEGIN
1482 l_api_name := 'create_new_contract_version';
1483 l_user_id := FND_GLOBAL.user_id();
1484
1485 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1486 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1487 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1488 END IF;
1489
1490 -- Standard call to check for call compatibility.
1491 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1493 END IF;
1494
1495 x_return_status := FND_API.G_RET_STS_SUCCESS;
1496
1497 OPEN c_get_contract_status;
1498 FETCH c_get_contract_status INTO l_contract_status;
1499 CLOSE c_get_contract_status;
1500
1501 IF l_contract_status IN ('SIGNED', 'APPROVED', 'CANCELLED') THEN
1502
1503 -- OKC_REP_CONTRACT_PROCESS_PVT.version_contract_details API copies the record from okc_rep_contracts_all
1504 -- table to okc_rep_contract_vers table and versions Terms, Deliverables, Attachments and UDA's
1505 OKC_REP_CONTRACT_PROCESS_PVT.version_contract_details(p_api_version => p_api_version,
1506 p_init_msg_list => fnd_api.g_false,
1507 p_commit => p_commit,
1508 p_contract_id => p_contract_id,
1509 p_version_from_api => fnd_api.g_true,
1510 x_msg_data => x_msg_data,
1511 x_msg_count => x_msg_count,
1512 x_return_status => x_return_status);
1513
1514 --update the version and status of the record in okc_rep_contracts_all
1515 UPDATE okc_rep_contracts_all SET contract_version_num = contract_version_num +1,
1516 contract_status_code = 'DRAFT',
1517 last_updated_by = FND_GLOBAL.user_id(),
1518 last_update_login = FND_GLOBAL.user_id(),
1519 last_update_date = SYSDATE,
1520 run_id = okc_rep_import_run_id_s.NEXTVAL
1521 WHERE contract_id = p_contract_id;
1522
1523
1524 ELSE -- IF l_contract_status IN ('SIGNED', 'APPROVED', 'CANCELLED')
1525
1526 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1527 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1528 'Contract not in valid status for creating new version');
1529 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1530 'Contract ID: '||p_contract_id);
1531 END IF;
1532
1533 l_valid_flag := 'N';
1534 fnd_message.set_name(G_APP_NAME,'OKC_REP_INV_CON_STS');
1535 l_error_msg := fnd_message.get;
1536
1537 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1538 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1539 'Contract not in valid status for creating new version');
1540 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1541 'Contract ID: '||p_contract_id);
1542 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1543 'l_error_msg: '||l_error_msg);
1544
1545 END IF;
1546
1547 END IF; -- IF l_contract_status IN ('SIGNED', 'APPROVED', 'CANCELLED')
1548
1549 --Populate the error message table
1550 IF(l_valid_flag = 'N' AND l_error_msg IS NOT NULL) THEN
1551 OKC_REP_UTIL_PVT.populate_import_errors(p_init_msg_list => FND_API.G_FALSE,
1552 p_api_version => 1.0,
1553 p_contract_id => p_contract_id,
1554 p_error_obj_type => G_NEW_VERSION_ERROR,
1555 p_error_obj_id => p_contract_id,
1556 p_error_msg_txt => l_error_msg,
1557 p_program_id => 0,
1558 p_program_login_id => 0,
1559 p_program_app_id => 0,
1560 p_run_id => p_run_id,
1561 x_return_status => x_return_status,
1562 x_msg_count => x_msg_count,
1563 x_msg_data => x_msg_data);
1564 END IF;
1565
1566 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1567 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1568 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1569 END IF;
1570
1571 EXCEPTION
1572
1573 WHEN FND_API.G_EXC_ERROR THEN
1574
1575 IF c_get_contract_status%ISOPEN THEN
1576 CLOSE c_get_contract_status;
1577 END IF;
1578
1579 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1580 fnd_log.string(FND_LOG.LEVEL_EXCEPTION, g_module || l_api_name,
1581 'Leaving '||l_api_name||':FND_API.G_EXC_ERROR Exception');
1582 END IF;
1583
1584 x_return_status := FND_API.G_RET_STS_ERROR;
1585 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1586 p_data => x_msg_data);
1587
1588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1589
1590 IF c_get_contract_status%ISOPEN THEN
1591 CLOSE c_get_contract_status;
1592 END IF;
1593
1594 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1595 fnd_log.string(FND_LOG.LEVEL_EXCEPTION, g_module || l_api_name,
1596 'Leaving '||l_api_name||':FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1597 END IF;
1598
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1600 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1601 p_data => x_msg_data);
1602
1603 WHEN OTHERS THEN
1604
1605 IF c_get_contract_status%ISOPEN THEN
1606 CLOSE c_get_contract_status;
1607 END IF;
1608
1609 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1610 fnd_log.string(FND_LOG.LEVEL_EXCEPTION, g_module || l_api_name,
1611 'Leaving '||l_api_name||' because of EXCEPTION: ' || sqlerrm);
1612 END IF;
1613
1614 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1615 p_msg_name => G_UNEXPECTED_ERROR,
1616 p_token1 => G_SQLCODE_TOKEN,
1617 p_token1_value => sqlcode,
1618 p_token2 => G_SQLERRM_TOKEN,
1619 p_token2_value => sqlerrm);
1620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1621 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1622 p_data => x_msg_data);
1623
1624 END create_new_contract_version;
1625
1626
1627 /*
1628 Creates a new version of the contract contract in draft status.
1629 Updates to the contract should be done from UI.
1630
1631 The relation of parent document which is obtained in p_document_rec is stored in the okc_rep_contract_usages table
1632 */
1633
1634 Procedure create_new_contract_version(p_api_version IN NUMBER,
1635 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1636 p_contract_type IN VARCHAR2 DEFAULT NULL,
1637 p_contract_id IN NUMBER DEFAULT NULL,
1638 p_run_id IN NUMBER,
1639 p_commit IN VARCHAR2 := fnd_api.g_false,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 x_msg_count OUT NOCOPY NUMBER,
1642 x_return_status OUT NOCOPY VARCHAR2
1643 ) IS
1644
1645 l_api_name VARCHAR2(50);
1646 l_api_version CONSTANT NUMBER := 1.0;
1647
1648 l_user_id NUMBER;
1649
1650
1651 l_query VARCHAR2(4000);
1652 l_where_clause VARCHAR2(4000);
1653
1654 l_contract_id NUMBER;
1655 l_contract_rec OKC_IMP_RECORD_TYPES.contract_rec_type;
1656
1657 TYPE contract_id_cur IS REF CURSOR;
1658 c_contract_id_cur contract_id_cur;
1659
1660 BEGIN
1661 l_api_name := 'create_new_contract_version';
1662 l_user_id := FND_GLOBAL.user_id();
1663
1664 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1665 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1666 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1667 END IF;
1668
1669 -- Standard call to check for call compatibility.
1670 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1672 END IF;
1673
1674 x_return_status := FND_API.G_RET_STS_SUCCESS;
1675
1676 l_query := 'SELECT contract_id FROM okc_rep_contract_usages
1677 WHERE business_document_type = ''' || p_document_rec.business_document_type ||
1678 ''' AND business_document_id = ' || p_document_rec.business_document_id;
1679
1680 IF p_document_rec.business_document_version IS NOT NULL THEN
1681 l_where_clause := l_where_clause || ' and business_document_version = ' ||
1682 p_document_rec.business_document_version;
1683 END IF;
1684
1685 IF p_document_rec.pk1_value IS NOT NULL THEN
1686 l_where_clause := l_where_clause || ' and pk1_value = ' || p_document_rec.pk1_value;
1687 END IF;
1688
1689 IF p_document_rec.pk2_value IS NOT NULL THEN
1690 l_where_clause := l_where_clause || ' and pk2_value = ' || p_document_rec.pk2_value;
1691 END IF;
1692
1693 IF p_document_rec.pk3_value IS NOT NULL THEN
1694 l_where_clause := l_where_clause || ' and pk3_value = ' || p_document_rec.pk3_value;
1695 END IF;
1696
1697 IF p_document_rec.pk3_value IS NOT NULL THEN
1698 l_where_clause := l_where_clause || ' and pk4_value = ' || p_document_rec.pk4_value;
1699 END IF;
1700
1701 IF p_document_rec.pk3_value IS NOT NULL THEN
1702 l_where_clause := l_where_clause || ' and pk5_value = ' || p_document_rec.pk5_value;
1703 END IF;
1704
1705 IF p_contract_type IS NOT NULL THEN
1706 l_where_clause := l_where_clause || ' and contract_type = ''' || p_contract_type || '''';
1707 END IF;
1708
1709 IF p_contract_id IS NOT NULL THEN
1710 l_where_clause := l_where_clause || ' and contract_id = ' || p_contract_id;
1711 END IF;
1712
1713 IF l_where_clause IS NOT NULL THEN
1714 l_query := l_query || l_where_clause;
1715 END IF;
1716
1717 OPEN c_contract_id_cur FOR l_query;
1718
1719 --Call in loop for every contract_id identified
1720 LOOP
1721 FETCH c_contract_id_cur INTO l_contract_id;
1722 exit when c_contract_id_cur%notfound;
1723
1724 --Call first create Contract API to create the contract
1725 create_new_contract_version(p_api_version => p_api_version,
1726 p_contract_id => l_contract_id,
1727 p_run_id => p_run_id,
1728 p_commit => p_commit,
1729 x_msg_data => x_msg_data,
1730 x_msg_count => x_msg_count,
1731 x_return_status => x_return_status);
1732
1733 --populate p_contract_rec
1734 SELECT contract_type, contract_id, contract_version_num, run_id
1735 INTO l_contract_rec.contract_type, l_contract_rec.contract_id,
1736 l_contract_rec.contract_version_num, l_contract_rec.run_id
1737 FROM okc_rep_contracts_all orca
1738 WHERE contract_id = l_contract_id;
1739
1740 Update_contract_usages(p_api_version => p_api_version,
1741 p_contract_rec => l_contract_rec,
1742 p_document_rec => p_document_rec,
1743 p_commit => p_commit,
1744 x_msg_data => x_msg_data,
1745 x_msg_count => x_msg_count,
1746 x_return_status => x_return_status);
1747 END LOOP;
1748 CLOSE c_contract_id_cur;
1749
1750
1751 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1752 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1753 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
1754 END IF;
1755
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1759 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1760 g_module || l_api_name,
1761 'Leaving create_new_contract_version because of EXCEPTION: ' || sqlerrm);
1762 END IF;
1763 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1764 p_msg_name => G_UNEXPECTED_ERROR,
1765 p_token1 => G_SQLCODE_TOKEN,
1766 p_token1_value => sqlcode,
1767 p_token2 => G_SQLERRM_TOKEN,
1768 p_token2_value => sqlerrm);
1769 --close cursors
1770 IF (c_contract_id_cur%ISOPEN) THEN
1771 CLOSE c_contract_id_cur ;
1772 END IF;
1773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1774 FND_MSG_PUB.Count_And_Get(
1775 p_count => x_msg_count,
1776 p_data => x_msg_data
1777 );
1778 END create_new_contract_version;
1779
1780 /*Scenario where only one contract will be associated for this business Document*/
1781 PROCEDURE check_contract_exists(p_api_version IN NUMBER,
1782 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1783 x_contract_type IN OUT NOCOPY VARCHAR2,
1784 x_contract_id IN OUT NOCOPY NUMBER ,
1785 x_contract_version_num IN OUT NOCOPY NUMBER ,
1786 x_msg_data OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER,
1788 x_return_status OUT NOCOPY VARCHAR2
1789 )
1790 IS
1791
1792 l_api_name VARCHAR2(50);
1793 l_api_version CONSTANT NUMBER := 1.0;
1794
1795 CURSOR c_contract_exist_csr IS
1796 SELECT orcu.contract_type, orcu.contract_id, orcu.contract_version
1797 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
1798 WHERE orcu.business_document_type = p_document_rec.business_document_type
1799 AND orcu.business_document_id = p_document_rec.business_document_id
1800 --AND orcu.business_document_version = p_document_rec.business_document_version
1801 AND orcu.contract_type = Nvl(x_contract_type,orcu.contract_type)
1802 AND orca.contract_id = orcu.contract_id
1803 AND orca.contract_version_num = orcu.contract_version;
1804
1805 BEGIN
1806
1807 l_api_name := 'check_contract_exists';
1808 -- l_user_id := FND_GLOBAL.user_id();
1809
1810 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1812 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
1813 END IF;
1814
1815 -- Standard call to check for call compatibility.
1816 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818 END IF;
1819
1820 -- Initialize API return status to success
1821 x_return_status := FND_API.G_RET_STS_SUCCESS;
1822
1823 --Find the contracts associated to the document
1824
1825 OPEN c_contract_exist_csr;
1826 FETCH c_contract_exist_csr INTO x_contract_type, x_contract_id, x_contract_version_num;
1827 IF(c_contract_exist_csr%NOTFOUND) THEN
1828 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1829 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1830 G_MODULE||l_api_name,
1831 'No Contract Exists for this document: '|| p_document_rec.business_document_type );
1832 END IF;
1833
1834 x_contract_type := NULL;
1835 x_contract_id := NULL;
1836 x_contract_version_num := NULL;
1837 END IF;
1838 CLOSE c_contract_exist_csr;
1839
1840 END check_contract_exists;
1841
1842 PROCEDURE Activate_tasks_closeout(p_api_version IN NUMBER,
1843 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1844 p_physical_completion_date IN DATE ,
1845 p_init_msg_list IN VARCHAR2,
1846 p_commit IN VARCHAR2,
1847 x_msg_data OUT NOCOPY VARCHAR2,
1848 x_msg_count OUT NOCOPY NUMBER,
1849 x_return_status OUT NOCOPY VARCHAR2
1850 )
1851 IS
1852 l_api_name VARCHAR2(30);
1853 l_api_version NUMBER;
1854 l_activate_event_tbl okc_rep_contract_process_pvt.EVENT_TBL_TYPE;
1855 l_update_event_tbl okc_rep_contract_process_pvt.EVENT_TBL_TYPE;
1856 l_sync_flag VARCHAR2(1);
1857 l_phy_complete_date_match_flag VARCHAR2(1);
1858 l_effective_date_matches_flag VARCHAR2(1);
1859
1860 l_contract_type VARCHAR2(150);
1861 l_contract_id NUMBER;
1862
1863 CURSOR contract_csr(p_contract_type VARCHAR2, p_contract_id NUMBER) IS
1864 SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date
1865 FROM OKC_REP_CONTRACTS_ALL
1866 WHERE contract_id = p_contract_id
1867 AND contract_type = p_contract_type;
1868
1869 CURSOR c_get_contract_id_csr IS
1870 SELECT contract_type, contract_id FROM okc_rep_contract_usages
1871 WHERE business_document_id = p_document_rec.business_document_id
1872 AND business_document_type = p_document_rec.business_document_type;
1873
1874 contract_rec contract_csr%ROWTYPE;
1875
1876 BEGIN
1877
1878 l_phy_complete_date_match_flag := FND_API.G_FALSE;
1879
1880 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1881 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1882 'Entered OKC_REP_CONTRACT_PROCESS_PVT.sign_contract_closeout');
1883 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1884 'Business Document Id is: ' || p_document_rec.business_document_id);
1885 END IF;
1886 l_api_name := 'Activate_tasks_closeout';
1887 l_api_version := 1.0;
1888
1889 -- Standard Start of API savepoint
1890 SAVEPOINT sign_contract_PVT;
1891
1892 -- Standard call to check for call compatibility.
1893 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1895 END IF;
1896 -- Initialize message list if p_init_msg_list is set to TRUE.
1897 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1898 FND_MSG_PUB.initialize;
1899 END IF;
1900
1901 -- Initialize API return status to success
1902 x_return_status := FND_API.G_RET_STS_SUCCESS;
1903
1904 --find the contract associated to this business document
1905 OPEN c_get_contract_id_csr;
1906 FETCH c_get_contract_id_csr INTO l_contract_type, l_contract_id;
1907 IF(c_get_contract_id_csr%NOTFOUND) THEN
1908 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1909 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1910 G_MODULE||l_api_name,
1911 'Invalid Business Document Type: '|| p_document_rec.business_document_type);
1912 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1913 G_MODULE||l_api_name,
1914 'Invalid Business Document Id: '|| p_document_rec.business_document_id);
1915
1916 END IF;
1917 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1918 p_msg_name => G_INVALID_BUS_DOC_ID_MSG,
1919 p_token1 => 'BUS_DOC_ID',
1920 p_token1_value => to_char(p_document_rec.business_document_id));
1921 RAISE FND_API.G_EXC_ERROR;
1922 -- RAISE NO_DATA_FOUND;
1923 END IF;
1924
1925 -- Get effective dates and version of the contract.
1926 OPEN contract_csr(l_contract_type, l_contract_id);
1927 FETCH contract_csr INTO contract_rec;
1928 IF(contract_csr%NOTFOUND) THEN
1929 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1930 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1931 G_MODULE||l_api_name,
1932 'Invalid Contract Id: '|| l_contract_id);
1933 END IF;
1934 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1935 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
1936 p_token1 => 'CONTRACT_ID',
1937 p_token1_value => to_char(l_contract_id));
1938 RAISE FND_API.G_EXC_ERROR;
1939 -- RAISE NO_DATA_FOUND;
1940 END IF;
1941
1942 -- Lock the contract header
1943 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1944 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1945 'Calling OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header()');
1946 END IF;
1947 -- Lock the contract header
1948 okc_rep_contract_process_pvt.Lock_Contract_Header(
1949 p_contract_id => l_contract_id,
1950 p_object_version_number => NULL,
1951 x_return_status => x_return_status
1952 );
1953
1954 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1955 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1956 'OKC_REP_CONTRACT_PROCESS_PVT.Lock_Contract_Header return status is: '
1957 || x_return_status);
1958 END IF;
1959
1960 -----------------------------------------------------
1961 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1962 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1963 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1964 RAISE OKC_API.G_EXCEPTION_ERROR;
1965 END IF;
1966 -----------------------------------------------------
1967
1968 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1969 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1970 'Calling OKC_REP_UTIL_PVT.change_contract_status');
1971 END IF;
1972 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
1973 OKC_REP_UTIL_PVT.change_contract_status(
1974 p_api_version => 1.0,
1975 p_init_msg_list => FND_API.G_FALSE,
1976 p_contract_id => l_contract_id,
1977 p_contract_version => contract_rec.contract_version_num,
1978 p_status_code => G_STATUS_SIGNED,
1979 p_user_id => fnd_global.user_id,
1980 p_note => NULL,
1981 x_msg_data => x_msg_data,
1982 x_msg_count => x_msg_count,
1983 x_return_status => x_return_status);
1984 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1985 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1986 g_module || l_api_name,
1987 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || x_return_status);
1988 END IF;
1989 -----------------------------------------------------
1990 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1991 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1992 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1993 RAISE OKC_API.G_EXCEPTION_ERROR;
1994 END IF;
1995 ------------------------------------------------------
1996
1997 -- We need to first version the deliverables
1998 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1999 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2000 'Calling OKC_DELIVERABLE_PROCESS_PVT.version_deliverables');
2001 END IF;
2002
2003 OKC_DELIVERABLE_PROCESS_PVT.version_deliverables (
2004 p_api_version => 1.0,
2005 p_init_msg_list => FND_API.G_FALSE,
2006 p_doc_id => l_contract_id,
2007 p_doc_version => contract_rec.contract_version_num,
2008 p_doc_type => contract_rec.contract_type,
2009 x_return_status => x_return_status,
2010 x_msg_count => x_msg_count,
2011 x_msg_data => x_msg_data
2012 );
2013 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2014 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2015 'OKC_DELIVERABLE_PROCESS_PVT.version_deliverables return status is : '
2016 || x_return_status);
2017 END IF;
2018 -----------------------------------------------------
2019 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2020 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2021 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2022 RAISE OKC_API.G_EXCEPTION_ERROR;
2023 END IF;
2024 --------------------------------------------------------
2025
2026 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2027 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2028 'Latest signed version number is : '
2029 || contract_rec.latest_signed_ver_number);
2030 END IF;
2031 -- Now we need to activate deliverables
2032 if (contract_rec.latest_signed_ver_number IS NULL) THEN
2033 l_sync_flag := FND_API.G_FALSE;
2034 ELSE
2035 l_sync_flag := FND_API.G_TRUE;
2036 END IF;
2037
2038 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2039 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2040 'l_sync_flag is : ' || l_sync_flag);
2041 END IF;
2042
2043 l_activate_event_tbl(1).event_code := 'PO_PHYSICAL_COMPLETE';
2044 l_activate_event_tbl(1).event_date := p_physical_completion_date;
2045
2046 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2047 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2048 'Calling OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
2049 END IF;
2050
2051 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
2052 p_api_version => 1.0,
2053 p_init_msg_list => FND_API.G_FALSE,
2054 p_commit => FND_API.G_FALSE,
2055 p_bus_doc_id => l_contract_id,
2056 p_bus_doc_type => contract_rec.contract_type,
2057 p_bus_doc_version => contract_rec.contract_version_num,
2058 p_event_code => 'PO_PHYSICAL_COMPLETE',
2059 p_event_date => p_physical_completion_date,
2060 p_sync_flag => l_sync_flag,
2061 p_bus_doc_date_events_tbl => l_activate_event_tbl,
2062 x_msg_data => x_msg_data,
2063 x_msg_count => x_msg_count,
2064 x_return_status => x_return_status);
2065
2066 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2067 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2068 'OKC_DELIVERABLE_PROCESS_PVT.activateDeliverables return status is : '
2069 || x_return_status);
2070 END IF;
2071 -----------------------------------------------------
2072 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2073 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2074 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2075 RAISE OKC_API.G_EXCEPTION_ERROR;
2076 END IF;
2077 --------------------------------------------------------
2078
2079 -- Checking if we need to call deliverable's APIs for synch-ing
2080 IF (l_sync_flag = FND_API.G_TRUE) THEN
2081
2082 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2083 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2084 'Before checking if we need to call updateDeliverable and disableDeliverable()');
2085 END IF;
2086
2087 l_update_event_tbl(1).event_code := 'PO_PHYSICAL_COMPLETE';
2088 l_update_event_tbl(1).event_date := p_physical_completion_date;
2089
2090 -- we need to call deliverables API for synching previous signed deliverables.
2091
2092 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2093 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2094 'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
2095 END IF;
2096 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
2097 p_api_version => 1.0,
2098 p_init_msg_list => FND_API.G_FALSE,
2099 p_commit => FND_API.G_FALSE,
2100 p_bus_doc_id => l_contract_id,
2101 p_bus_doc_type => contract_rec.contract_type,
2102 p_bus_doc_version => contract_rec.contract_version_num,
2103 p_bus_doc_date_events_tbl => l_update_event_tbl,
2104 x_msg_data => x_msg_data,
2105 x_msg_count => x_msg_count,
2106 x_return_status => x_return_status);
2107
2108 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2109 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2110 'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
2111 || x_return_status);
2112 END IF;
2113 -----------------------------------------------------
2114 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2115 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2116 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2117 RAISE OKC_API.G_EXCEPTION_ERROR;
2118 END IF;
2119 --------------------------------------------------------
2120 END IF; -- expiration date comparision
2121
2122 -- Disable prev. version deliverables
2123 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2124 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2125 'Calling OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables');
2126 END IF;
2127 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
2128 p_api_version => 1.0,
2129 p_init_msg_list => FND_API.G_FALSE,
2130 p_commit => FND_API.G_FALSE,
2131 p_bus_doc_id => l_contract_id,
2132 p_bus_doc_type => contract_rec.contract_type,
2133 p_bus_doc_version => contract_rec.latest_signed_ver_number,
2134 x_msg_data => x_msg_data,
2135 x_msg_count => x_msg_count,
2136 x_return_status => x_return_status);
2137
2138 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2139 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2140 'OKC_DELIVERABLE_PROCESS_PVT.disableDeliverables return status is : '
2141 || x_return_status);
2142 END IF;
2143 -----------------------------------------------------
2144 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2145 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2146 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2147 RAISE OKC_API.G_EXCEPTION_ERROR;
2148 END IF;
2149 --------------------------------------------------------
2150 --END IF; -- (l_sync_flag = 'Y')
2151
2152 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2153 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2154 'Updating latest_signed_ver_number column');
2155 END IF;
2156
2157 UPDATE okc_rep_contracts_all
2158 SET latest_signed_ver_number = contract_rec.contract_version_num
2159 WHERE contract_id = l_contract_id;
2160 CLOSE contract_csr;
2161
2162 -- Standard check of p_commit
2163 IF FND_API.To_Boolean( p_commit ) THEN
2164 COMMIT WORK;
2165 END IF;
2166
2167 -- Standard call to get message count and if count is 1, get message info.
2168 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2169
2170 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2172 'Leaving OKC_REP_CONTRACT_PROCESS_PVT.sign_contract');
2173 END IF;
2174
2175
2176 EXCEPTION
2177 WHEN FND_API.G_EXC_ERROR THEN
2178 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2179 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2180 g_module || l_api_name,
2181 'Leaving sign_contract:FND_API.G_EXC_ERROR Exception');
2182 END IF;
2183 --close cursors
2184 IF (contract_csr%ISOPEN) THEN
2185 CLOSE contract_csr ;
2186 END IF;
2187 IF (c_get_contract_id_csr%ISOPEN) THEN
2188 CLOSE c_get_contract_id_csr ;
2189 END IF;
2190 ROLLBACK TO sign_contract_PVT;
2191 x_return_status := FND_API.G_RET_STS_ERROR;
2192 FND_MSG_PUB.Count_And_Get(
2193 p_count => x_msg_count,
2194 p_data => x_msg_data
2195 );
2196
2197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2198 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2199 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2200 g_module || l_api_name,
2201 'Leaving sign_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2202 END IF;
2203 --close cursors
2204 IF (contract_csr%ISOPEN) THEN
2205 CLOSE contract_csr ;
2206 END IF;
2207 IF (c_get_contract_id_csr%ISOPEN) THEN
2208 CLOSE c_get_contract_id_csr ;
2209 END IF;
2210 ROLLBACK TO sign_contract_PVT;
2211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2212 FND_MSG_PUB.Count_And_Get(
2213 p_count => x_msg_count,
2214 p_data => x_msg_data
2215 );
2216
2217 WHEN OTHERS THEN
2218 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2219 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2220 g_module || l_api_name,
2221 'Leaving sign_contract because of EXCEPTION: ' || sqlerrm);
2222 END IF;
2223 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2224 p_msg_name => G_UNEXPECTED_ERROR,
2225 p_token1 => G_SQLCODE_TOKEN,
2226 p_token1_value => sqlcode,
2227 p_token2 => G_SQLERRM_TOKEN,
2228 p_token2_value => sqlerrm);
2229 ROLLBACK TO sign_contract_PVT;
2230 --close cursors
2231 IF (contract_csr%ISOPEN) THEN
2232 CLOSE contract_csr ;
2233 END IF;
2234 IF (c_get_contract_id_csr%ISOPEN) THEN
2235 CLOSE c_get_contract_id_csr ;
2236 END IF;
2237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2238 FND_MSG_PUB.Count_And_Get(
2239 p_count => x_msg_count,
2240 p_data => x_msg_data
2241 );
2242 END Activate_tasks_closeout;
2243
2244 /*Scenario where only one contract will be associated for this business Document*/
2245 PROCEDURE check_contract_exists(p_api_version IN NUMBER,
2246 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
2247 x_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
2248 x_msg_data OUT NOCOPY VARCHAR2,
2249 x_msg_count OUT NOCOPY NUMBER,
2250 x_return_status OUT NOCOPY VARCHAR2
2251 )
2252 IS
2253
2254 l_api_name VARCHAR2(50);
2255 l_api_version CONSTANT NUMBER := 1.0;
2256
2257 CURSOR c_contract_exist_csr IS
2258 SELECT orcu.contract_type, orcu.contract_id, orcu.contract_version, orca.contract_status_code ,
2259 orca.sbcr_coordination_type
2260 FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
2261 WHERE orcu.business_document_type = p_document_rec.business_document_type
2262 AND orcu.business_document_id = p_document_rec.business_document_id
2263 --AND orcu.business_document_version = p_document_rec.business_document_version
2264 AND orcu.contract_type = Nvl(x_contract_rec.contract_type,orcu.contract_type)
2265 AND orca.contract_id = orcu.contract_id
2266 AND orca.contract_version_num = orcu.contract_version;
2267
2268 BEGIN
2269
2270 l_api_name := 'check_contract_exists';
2271 -- l_user_id := FND_GLOBAL.user_id();
2272
2273 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2274 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2275 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
2276 END IF;
2277
2278 -- Standard call to check for call compatibility.
2279 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2281 END IF;
2282
2283 -- Initialize API return status to success
2284 x_return_status := FND_API.G_RET_STS_SUCCESS;
2285
2286 --Find the contracts associated to the document
2287
2288 OPEN c_contract_exist_csr;
2289 FETCH c_contract_exist_csr INTO x_contract_rec.contract_type, x_contract_rec.contract_id,
2290 x_contract_rec.contract_version_num, x_contract_rec.contract_status_code,
2291 x_contract_rec.sbcr_coordination_type;
2292 IF(c_contract_exist_csr%NOTFOUND) THEN
2293 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2294 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
2295 G_MODULE||l_api_name,
2296 'No Contract Exists for this document: '|| p_document_rec.business_document_type );
2297 END IF;
2298
2299 x_contract_rec.contract_id := NULL;
2300 x_contract_rec.contract_version_num := NULL;
2301 x_contract_rec.contract_status_code := NULL;
2302
2303 END IF;
2304 CLOSE c_contract_exist_csr;
2305
2306 END check_contract_exists;
2307
2308 /*
2309 Deletes/ Cancels the Contract for the given document type based on the status
2310 */
2311
2312 Procedure delete_cancel_contract(p_api_version IN NUMBER,
2313 p_commit IN VARCHAR2 := fnd_api.g_false,
2314 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
2315 p_contract_type IN VARCHAR2 DEFAULT NULL,
2316 p_contract_id IN NUMBER DEFAULT NULL,
2317 x_msg_data OUT NOCOPY VARCHAR2,
2318 x_msg_count OUT NOCOPY NUMBER,
2319 x_return_status OUT NOCOPY VARCHAR2
2320 )
2321
2322 IS
2323
2324 l_api_name VARCHAR2(50);
2325 l_api_version CONSTANT NUMBER := 1.0;
2326
2327 l_query VARCHAR2(4000);
2328 l_where_clause VARCHAR2(4000);
2329
2330 l_contract_rec OKC_IMP_RECORD_TYPES.contract_rec_type;
2331 l_contract_id NUMBER;
2332 l_contract_status_code VARCHAR2(30);
2333 l_contract_version_num NUMBER;
2334
2335 TYPE contract_id_cur IS REF CURSOR;
2336 c_contract_id_cur contract_id_cur;
2337
2338 BEGIN
2339 l_api_name := 'delete_cancel_contract';
2340
2341 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2342 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2343 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
2344 END IF;
2345
2346 -- Standard call to check for call compatibility.
2347 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2349 END IF;
2350
2351 x_return_status := FND_API.G_RET_STS_SUCCESS;
2352
2353 --Find the contracts to be deleted w.r.t the document
2354
2355 l_query := 'SELECT contract_id, contract_version FROM okc_rep_contract_usages
2356 WHERE business_document_type = ''' || p_document_rec.business_document_type ||
2357 ''' AND business_document_id = ' || p_document_rec.business_document_id;
2358
2359 IF p_document_rec.business_document_version IS NOT NULL THEN
2360 l_where_clause := l_where_clause || ' and business_document_version = ' ||
2361 p_document_rec.business_document_version;
2362 END IF;
2363
2364 IF p_document_rec.pk1_value IS NOT NULL THEN
2365 l_where_clause := l_where_clause || ' and pk1_value = ' || p_document_rec.pk1_value;
2366 END IF;
2367
2368 IF p_document_rec.pk2_value IS NOT NULL THEN
2369 l_where_clause := l_where_clause || ' and pk2_value = ' || p_document_rec.pk2_value;
2370 END IF;
2371
2372 IF p_document_rec.pk3_value IS NOT NULL THEN
2373 l_where_clause := l_where_clause || ' and pk3_value = ' || p_document_rec.pk3_value;
2374 END IF;
2375
2376 IF p_document_rec.pk3_value IS NOT NULL THEN
2377 l_where_clause := l_where_clause || ' and pk4_value = ' || p_document_rec.pk4_value;
2378 END IF;
2379
2380 IF p_document_rec.pk3_value IS NOT NULL THEN
2381 l_where_clause := l_where_clause || ' and pk5_value = ' || p_document_rec.pk5_value;
2382 END IF;
2383
2384 IF p_contract_type IS NOT NULL THEN
2385 l_where_clause := l_where_clause || ' and contract_type = ''' || p_contract_type || '''';
2386 END IF;
2387
2388 IF p_contract_id IS NOT NULL THEN
2389 l_where_clause := l_where_clause || ' and contract_id = ' || p_contract_id;
2390 END IF;
2391
2392 IF l_where_clause IS NOT NULL THEN
2393 l_query := l_query || l_where_clause;
2394 END IF;
2395
2396 OPEN c_contract_id_cur FOR l_query;
2397
2398 --Call in loop for every contract_id identified
2399 LOOP
2400 FETCH c_contract_id_cur INTO l_contract_id, l_contract_version_num;
2401 exit when c_contract_id_cur%notfound;
2402
2403 SELECT contract_status_code INTO l_contract_status_code
2404 FROM okc_rep_contracts_all WHERE contract_id = l_contract_id;
2405
2406 IF (l_contract_status_code = 'DRAFT' AND l_contract_version_num = 1) THEN
2407 --populate p_contract_rec
2408 SELECT contract_type, contract_id, contract_version_num, run_id
2409 INTO l_contract_rec.contract_type, l_contract_rec.contract_id,
2410 l_contract_rec.contract_version_num, l_contract_rec.run_id
2411 FROM okc_rep_contracts_all orca
2412 WHERE contract_id = l_contract_id;
2413
2414 --Call the original delete API to delete the conract
2415 delete_contract(p_api_version => p_api_version,
2416 p_contract_id => l_contract_id,
2417 p_commit => p_commit,
2418 x_msg_data => x_msg_data,
2419 x_msg_count => x_msg_count,
2420 x_return_status => x_return_status);
2421
2422 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2423 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2424 'OKC_REP_CONTRACT_IMP_PVT.delete_contract return status is : '
2425 || x_return_status);
2426 END IF;
2427 -----------------------------------------------------
2428 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2429 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2430 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2431 RAISE OKC_API.G_EXCEPTION_ERROR;
2432 END IF;
2433 --------------------------------------------------------
2434
2435 delete_contract_usages(p_api_version => p_api_version,
2436 p_contract_rec => l_contract_rec,
2437 p_document_rec => p_document_rec,
2438 p_commit => p_commit,
2439 x_msg_data => x_msg_data,
2440 x_msg_count => x_msg_count,
2441 x_return_status => x_return_status);
2442
2443 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2444 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2445 'OKC_REP_CONTRACT_IMP_PVT.delete_contract return status is : '
2446 || x_return_status);
2447 END IF;
2448
2449 -----------------------------------------------------
2450 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2451 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2452 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2453 RAISE OKC_API.G_EXCEPTION_ERROR;
2454 END IF;
2455 --------------------------------------------------------
2456
2457 ELSE
2458 --cancel the contract if version is > 1 and status is not draft
2459
2460 cancel_contract(p_api_version => p_api_version,
2461 p_contract_id => l_contract_id,
2462 p_commit => p_commit,
2463 x_msg_data => x_msg_data,
2464 x_msg_count => x_msg_count,
2465 x_return_status => x_return_status);
2466
2467 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2468 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2469 'OKC_REP_CONTRACT_IMP_PVT.cancel_contract return status is : '
2470 || x_return_status);
2471 END IF;
2472
2473 -----------------------------------------------------
2474 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2475 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2476 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2477 RAISE OKC_API.G_EXCEPTION_ERROR;
2478 END IF;
2479 --------------------------------------------------------
2480
2481 END IF;
2482
2483 END LOOP;
2484 CLOSE c_contract_id_cur;
2485
2486 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2487 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2488 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
2489 END IF;
2490
2491 EXCEPTION
2492 WHEN OTHERS THEN
2493 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2494 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2495 g_module || l_api_name,
2496 'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
2497 END IF;
2498 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2499 p_msg_name => G_UNEXPECTED_ERROR,
2500 p_token1 => G_SQLCODE_TOKEN,
2501 p_token1_value => sqlcode,
2502 p_token2 => G_SQLERRM_TOKEN,
2503 p_token2_value => sqlerrm);
2504 --close cursors
2505 IF (c_contract_id_cur%ISOPEN) THEN
2506 CLOSE c_contract_id_cur ;
2507 END IF;
2508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2509 FND_MSG_PUB.Count_And_Get(
2510 p_count => x_msg_count,
2511 p_data => x_msg_data
2512 );
2513
2514 END delete_cancel_contract;
2515
2516 PROCEDURE cancel_contract(p_api_version IN NUMBER,
2517 p_commit IN VARCHAR2 := fnd_api.g_false,
2518 p_contract_id IN NUMBER,
2519 x_msg_data OUT NOCOPY VARCHAR2,
2520 x_msg_count OUT NOCOPY NUMBER,
2521 x_return_status OUT NOCOPY VARCHAR2
2522 )
2523 IS
2524 l_api_name VARCHAR2(150);
2525 l_api_version CONSTANT NUMBER := 1.0;
2526
2527 l_contract_status VARCHAR2(30);
2528 l_resolved_token VARCHAR2(100);
2529 l_contract_type VARCHAR2(30);
2530 l_resolved_msg_name VARCHAR2(30);
2531
2532 CURSOR c_get_contract_status IS
2533 SELECT contract_type, contract_status_code FROM okc_rep_contracts_all
2534 WHERE contract_id = p_contract_id;
2535
2536 BEGIN
2537 l_api_name := 'cancel_contract ';
2538
2539 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2540 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2541 'Entered OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
2542 END IF;
2543
2544 -- Standard Start of API savepoint
2545 SAVEPOINT cancel_contract_PVT;
2546
2547 x_return_status := FND_API.G_RET_STS_SUCCESS;
2548
2549 -- Standard call to check for call compatibility.
2550 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2552 END IF;
2553
2554 OPEN c_get_contract_status;
2555 FETCH c_get_contract_status INTO l_contract_type, l_contract_status;
2556 CLOSE c_get_contract_status;
2557
2558 IF l_contract_status NOT IN ('PENDING_APPROVAL', 'PENDING_SIGN') THEN
2559
2560 -- Delete contract
2561 OKC_REP_CONTRACT_PROCESS_PVT.terminate_contract(p_api_version => 1.0,
2562 p_init_msg_list => FND_API.G_FALSE,
2563 p_commit => p_commit,
2564 p_contract_id => p_contract_id,
2565 p_termination_date => SYSDATE,
2566 x_msg_data => x_msg_data,
2567 x_msg_count => x_msg_count,
2568 x_return_status => x_return_status);
2569 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2570 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2571 'OKC_REP_CONTRACT_PROCESS_PVT.terminate_contract return status is : '
2572 || x_return_status);
2573 END IF;
2574
2575 -----------------------------------------------------
2576 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2577 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2578 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2579 RAISE OKC_API.G_EXCEPTION_ERROR;
2580 END IF;
2581 --------------------------------------------------------
2582 --Change Contract status TO CANCELLED
2583 UPDATE okc_rep_contracts_all
2584 SET CONTRACT_STATUS_CODE = 'CANCELLED',
2585 CANCELLATION_DATE = SYSDATE
2586 WHERE contract_id = p_contract_id
2587 AND contract_type = l_contract_type;
2588
2589 ELSE
2590
2591 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2592 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2593 'Cancel cannot be performed on a contract in status pending signed/pending approval');
2594 END IF;
2595
2596 x_return_status := FND_API.G_RET_STS_ERROR;
2597
2598 l_resolved_msg_name := OKC_API.resolve_message('OKC_IMP_INVALID_STATUS',l_contract_type);
2599 l_resolved_token := OKC_API.resolve_hdr_token(l_contract_type);
2600
2601 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2602 p_msg_name => l_resolved_msg_name,
2603 p_token1 => 'HDR_TOKEN',
2604 p_token1_value => l_resolved_token);
2605
2606 RAISE OKC_API.G_EXCEPTION_ERROR;
2607
2608 END IF;
2609
2610 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2611 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2612 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
2613 END IF;
2614
2615 EXCEPTION
2616
2617 WHEN FND_API.G_EXC_ERROR THEN
2618 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2619 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2620 g_module || l_api_name,
2621 'Leaving cancel_contract:FND_API.G_EXC_ERROR Exception');
2622 END IF;
2623 --close cursors
2624 IF (c_get_contract_status%ISOPEN) THEN
2625 CLOSE c_get_contract_status ;
2626 END IF;
2627 ROLLBACK TO cancel_contract_PVT;
2628 x_return_status := FND_API.G_RET_STS_ERROR;
2629 FND_MSG_PUB.Count_And_Get(
2630 p_count => x_msg_count,
2631 p_data => x_msg_data
2632 );
2633
2634 WHEN OTHERS THEN
2635 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2636 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2637 g_module || l_api_name,
2638 'Leaving cancel_contract because of EXCEPTION: ' || sqlerrm);
2639 END IF;
2640 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2641 p_msg_name => G_UNEXPECTED_ERROR,
2642 p_token1 => G_SQLCODE_TOKEN,
2643 p_token1_value => sqlcode,
2644 p_token2 => G_SQLERRM_TOKEN,
2645 p_token2_value => sqlerrm);
2646 --close cursors
2647 IF (c_get_contract_status%ISOPEN) THEN
2648 CLOSE c_get_contract_status ;
2649 END IF;
2650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2651 FND_MSG_PUB.Count_And_Get(
2652 p_count => x_msg_count,
2653 p_data => x_msg_data
2654 );
2655
2656 END cancel_contract;
2657
2658
2659 /*
2660 Cancels the Contract for the given document type.
2661 */
2662
2663 Procedure cancel_contract(p_api_version IN NUMBER,
2664 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
2665 p_contract_type IN VARCHAR2 DEFAULT NULL,
2666 p_contract_id IN NUMBER DEFAULT NULL,
2667 p_commit IN VARCHAR2 := fnd_api.g_false,
2668 x_msg_data OUT NOCOPY VARCHAR2,
2669 x_msg_count OUT NOCOPY NUMBER,
2670 x_return_status OUT NOCOPY VARCHAR2
2671 )
2672
2673 IS
2674
2675 l_api_name VARCHAR2(50);
2676 l_api_version CONSTANT NUMBER := 1.0;
2677 l_run_id NUMBER;
2678 l_contract_id NUMBER;
2679
2680
2681 l_query VARCHAR2(4000);
2682 l_where_clause VARCHAR2(4000);
2683
2684 l_contract_rec OKC_IMP_RECORD_TYPES.contract_rec_type;
2685
2686
2687 TYPE contract_id_cur IS REF CURSOR;
2688 c_contract_id_cur contract_id_cur;
2689
2690 BEGIN
2691 l_api_name := 'cancel_contract';
2692
2693 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2694 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2695 'Entered OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
2696 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2697 'p_document_rec.business_document_type '||p_document_rec.business_document_type);
2698 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2699 'p_document_rec.business_document_id '||p_document_rec.business_document_id);
2700 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2701 'p_document_rec.business_document_version '||p_document_rec.business_document_version);
2702 END IF;
2703
2704 -- Standard call to check for call compatibility.
2705 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2707 END IF;
2708
2709 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2710 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2711 'l_run_id : '||l_run_id);
2712 END IF;
2713
2714 --Find the contracts to be deleted w.r.t the document
2715
2716 l_query := 'SELECT contract_id, contract_version FROM okc_rep_contract_usages
2717 WHERE business_document_type = ''' || p_document_rec.business_document_type ||
2718 ''' AND business_document_id = ' || p_document_rec.business_document_id;
2719
2720 IF p_document_rec.business_document_version IS NOT NULL THEN
2721 l_where_clause := l_where_clause || ' and business_document_version = ' ||
2722 p_document_rec.business_document_version;
2723 END IF;
2724
2725 IF p_document_rec.pk1_value IS NOT NULL THEN
2726 l_where_clause := l_where_clause || ' and pk1_value = ' || p_document_rec.pk1_value;
2727 END IF;
2728
2729 IF p_document_rec.pk2_value IS NOT NULL THEN
2730 l_where_clause := l_where_clause || ' and pk2_value = ' || p_document_rec.pk2_value;
2731 END IF;
2732
2733 IF p_document_rec.pk3_value IS NOT NULL THEN
2734 l_where_clause := l_where_clause || ' and pk3_value = ' || p_document_rec.pk3_value;
2735 END IF;
2736
2737 IF p_document_rec.pk3_value IS NOT NULL THEN
2738 l_where_clause := l_where_clause || ' and pk4_value = ' || p_document_rec.pk4_value;
2739 END IF;
2740
2741 IF p_document_rec.pk3_value IS NOT NULL THEN
2742 l_where_clause := l_where_clause || ' and pk5_value = ' || p_document_rec.pk5_value;
2743 END IF;
2744
2745 IF p_contract_type IS NOT NULL THEN
2746 l_where_clause := l_where_clause || ' and contract_type = ''' || p_contract_type || '''';
2747 END IF;
2748
2749 IF p_contract_id IS NOT NULL THEN
2750 l_where_clause := l_where_clause || ' and contract_id = ' || p_contract_id;
2751 END IF;
2752
2753 IF l_where_clause IS NOT NULL THEN
2754 l_query := l_query || l_where_clause;
2755 END IF;
2756
2757 OPEN c_contract_id_cur FOR l_query;
2758
2759 --Call in loop for every contract_id identified
2760 LOOP
2761 FETCH c_contract_id_cur INTO l_contract_id;
2762 exit when c_contract_id_cur%notfound;
2763
2764 cancel_contract(p_api_version => p_api_version,
2765 p_contract_id => l_contract_id,
2766 p_commit => p_commit,
2767 x_msg_data => x_msg_data,
2768 x_msg_count => x_msg_count,
2769 x_return_status => x_return_status);
2770
2771 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2772 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2773 'OKC_REP_CONTRACT_IMP_PVT.cancel_contract return status is : '
2774 || x_return_status);
2775 END IF;
2776
2777 -----------------------------------------------------
2778 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2779 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2780 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2781 RAISE OKC_API.G_EXCEPTION_ERROR;
2782 END IF;
2783 --------------------------------------------------------
2784
2785 END LOOP;
2786 CLOSE c_contract_id_cur;
2787
2788
2789 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2790 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2791 'Leaving OKC_REP_CONTRACT_IMP_PUB.'||l_api_name);
2792 END IF;
2793
2794 EXCEPTION
2795
2796 WHEN FND_API.G_EXC_ERROR THEN
2797 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2798 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2799 g_module || l_api_name,
2800 'Leaving cancel_contract:FND_API.G_EXC_ERROR Exception');
2801 END IF;
2802 --close cursors
2803 IF (c_contract_id_cur%ISOPEN) THEN
2804 CLOSE c_contract_id_cur ;
2805 END IF;
2806 ROLLBACK TO cancel_contract_PVT;
2807 x_return_status := FND_API.G_RET_STS_ERROR;
2808 FND_MSG_PUB.Count_And_Get(
2809 p_count => x_msg_count,
2810 p_data => x_msg_data
2811 );
2812
2813 WHEN OTHERS THEN
2814 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2815 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2816 g_module || l_api_name,
2817 'Leaving cancel_contract because of EXCEPTION: ' || sqlerrm);
2818 END IF;
2819 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2820 p_msg_name => G_UNEXPECTED_ERROR,
2821 p_token1 => G_SQLCODE_TOKEN,
2822 p_token1_value => sqlcode,
2823 p_token2 => G_SQLERRM_TOKEN,
2824 p_token2_value => sqlerrm);
2825 --close cursors
2826 IF (c_contract_id_cur%ISOPEN) THEN
2827 CLOSE c_contract_id_cur ;
2828 END IF;
2829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2830 FND_MSG_PUB.Count_And_Get(
2831 p_count => x_msg_count,
2832 p_data => x_msg_data
2833 );
2834
2835 END cancel_contract;
2836
2837
2838 /*
2839 * Procedure to migrate date from okc_rep_contract_rels table to okc_rep_contract_usages table.
2840 * This migration is applicable for 12.2.2.
2841 * 1. For one row in okc_rep_contract_rels table, all the target related contracts are brought and these are inserted
2842 * against each version of source contract.
2843 * 2. If the relationship(combination of contract_id, contract_type, contract_version_num, business_document_id, business_document_type,
2844 business_document_version, relationship_role_id) already exists in the new table (okc_rep_contract_usages), then this procedure doesn't insert it again in the new table
2845 * 3. Marks the rows in okc_rep_contract_usagaes with source_code as 'MIGRATION' and populates corresponding requires_id, program_id.
2846 *
2847 * Parameters :
2848 * p_purge_and_rerun if 'Yes', deletes all rows in okc_rep_contract_usages with source_code as 'MIGRATION and migrates the date
2849 * if 'No', starts migrating the data, if the row already exists doesn't insert again.
2850 * default value : 'No' (if no value is entered by the user, then it takes as 'No')
2851 *
2852 * p_simulate if 'Yes', starts migrating the data, logs all successful migrated rows and errored rows. But dont' commmit the data.
2853 * Can be used for testing the migration process before actual run of this program.
2854 * if 'No', migrates data and commits data
2855 * default value : 'Yes' ( if no value is entered by the user, then it takes as 'Yes')
2856 */
2857
2858 PROCEDURE migrate_contract_relations(errbuf OUT NOCOPY VARCHAR2,
2859 retcode OUT NOCOPY VARCHAR2,
2860 p_purge_and_rerun IN VARCHAR2,
2861 p_simulate IN VARCHAR2)
2862 IS
2863
2864 l_api_name VARCHAR2(50);
2865 l_api_version CONSTANT NUMBER := 1.0;
2866
2867 g_conc_request_id NUMBER := -1;
2868 g_conc_program_id NUMBER := -1;
2869 g_conc_login_id NUMBER := -1;
2870 g_prog_appl_id NUMBER := -1;
2871
2872 TYPE tbl_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2873 TYPE tbl_varchar2_30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2874 TYPE tbl_varchar2_150 IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
2875 TYPE tbl_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2876
2877 l_src_contract_ids tbl_number;
2878 l_src_contract_types tbl_varchar2_30;
2879 l_src_contract_ver_nums tbl_number;
2880 l_tgt_contract_ids tbl_number;
2881 l_tgt_contract_types tbl_varchar2_30;
2882 l_tgt_contract_ver_nums tbl_number;
2883 l_tgt_contract_nums tbl_varchar2_150;
2884 l_relationship_ids tbl_number;
2885 l_object_ver_nums tbl_number;
2886 l_created_bys tbl_number;
2887 l_creation_dates tbl_date;
2888 l_last_updated_bys tbl_number;
2889 l_last_update_dates tbl_date;
2890 l_last_update_logins tbl_number;
2891 l_seq_nums tbl_number;
2892
2893 l_source_rels NUMBER := 0;
2894 l_source_rows NUMBER := 0;
2895 l_already_migrated NUMBER := 0;
2896 l_total_rows NUMBER := 0;
2897
2898 CURSOR c_migrate_rels IS
2899 SELECT src.contract_id,
2900 src.contract_type,
2901 src.contract_version_num,
2902 tgt.contract_id,
2903 tgt.contract_type,
2904 tgt.contract_version_num,
2905 tgt.contract_number,
2906 rels.relationship_role1_id,
2907 rels.object_version_number,
2908 rels.created_by,
2909 rels.creation_date,
2910 rels.last_updated_by,
2911 rels.last_update_date,
2912 rels.last_update_login
2913 FROM okc_rep_contract_rels rels,
2914 okc_rep_contracts_all src,
2915 okc_rep_contracts_all tgt
2916 WHERE src.contract_id = rels.contract_id
2917 AND tgt.contract_id = rels.related_contract_id
2918 --to avoid reruns of this procedure or on abnormal run of this program
2919 AND NOT EXISTS (SELECT 1 FROM okc_rep_contract_usages new_rels
2920 WHERE new_rels.contract_id = src.contract_id
2921 AND new_rels.contract_type = src.contract_type
2922 AND new_rels.contract_version = src.contract_version_num
2923 AND new_rels.business_document_id = tgt.contract_id
2924 AND new_rels.business_document_type = tgt.contract_type
2925 AND new_rels.business_document_version = tgt.contract_version_num
2926 AND new_rels.relationship_id = rels.relationship_role1_id)
2927 UNION ALL
2928 SELECT src_vers.contract_id,
2929 src_vers.contract_type,
2930 src_vers.contract_version_num,
2931 tgt.contract_id,
2932 tgt.contract_type,
2933 tgt.contract_version_num,
2934 tgt.contract_number,
2935 rels.relationship_role1_id,
2936 rels.object_version_number,
2937 rels.created_by,
2938 rels.creation_date,
2939 rels.last_updated_by,
2940 rels.last_update_date,
2941 rels.last_update_login
2942 FROM okc_rep_contract_rels rels,
2943 okc_rep_contract_vers src_vers,
2944 okc_rep_contracts_all tgt
2945 WHERE src_vers.contract_id = rels.contract_id
2946 AND tgt.contract_id = rels.related_contract_id
2947 --to avoid reruns of this procedure or on abnormal run of this program
2948 AND NOT EXISTS (SELECT 1 FROM okc_rep_contract_usages new_rels
2949 WHERE new_rels.contract_id = src_vers.contract_id
2950 AND new_rels.contract_type = src_vers.contract_type
2951 AND new_rels.contract_version = src_vers.contract_version_num
2952 AND new_rels.business_document_id = tgt.contract_id
2953 AND new_rels.business_document_type = tgt.contract_type
2954 AND new_rels.business_document_version = tgt.contract_version_num
2955 AND new_rels.relationship_id = rels.relationship_role1_id);
2956
2957 bulk_errors exception;
2958 PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
2959
2960 BEGIN
2961 l_api_name := 'migrate_contract_relations';
2962
2963 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entered OKC_REP_CONTRACT_IMP_PVT.' || l_api_name);
2964 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Input parameter p_purge_and_rerun: ' || p_purge_and_rerun);
2965 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Input parameter p_simulate: ' || p_simulate);
2966
2967 g_conc_request_id := FND_GLOBAL.conc_request_id;
2968 g_conc_program_id := FND_GLOBAL.conc_program_id;
2969 g_conc_login_id := FND_GLOBAL.conc_login_id;
2970 g_prog_appl_id := FND_GLOBAL.prog_appl_id;
2971
2972 FND_FILE.PUT_LINE(FND_FILE.LOG, 'g_conc_request_id:' || g_conc_request_id);
2973 FND_FILE.PUT_LINE(FND_FILE.LOG, 'g_conc_program_id:' || g_conc_program_id);
2974 FND_FILE.PUT_LINE(FND_FILE.LOG, 'g_conc_login_id:' || g_conc_login_id);
2975 FND_FILE.PUT_LINE(FND_FILE.LOG, 'g_prog_appl_id:' || g_prog_appl_id);
2976
2977 BEGIN
2978 SELECT count(1) INTO l_source_rels
2979 FROM okc_rep_contract_rels;
2980
2981 SELECT sum(con.contract_version_num) INTO l_source_rows
2982 FROM okc_rep_contract_rels rels, okc_rep_contracts_all con
2983 WHERE rels.contract_id = con.contract_id;
2984
2985 SELECT count(1) INTO l_already_migrated
2986 FROM okc_rep_contract_usages usages
2987 WHERE source_code = 'MIGRATION';
2988 EXCEPTION
2989 WHEN OTHERS THEN
2990 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occured: ' || sqlerrm);
2991 END;
2992
2993 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of rows in okc_rep_contract_rels table: ' || l_source_rels);
2994 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Expected no. of rows to be migrated: ' || l_source_rows);
2995 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows which are already migrated (rows with source_code as MIGRATION): ' || l_already_migrated);
2996
2997 IF nvl(p_purge_and_rerun, 'No') = 'Yes' THEN
2998 DELETE FROM okc_rep_contract_usages
2999 WHERE source_code = 'MIGRATION';
3000
3001 IF SQL%FOUND THEN
3002 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of rows purged in okc_rep_contract_usages table: ' || sql%rowcount);
3003 END IF;
3004 END IF;
3005
3006 OPEN c_migrate_rels;
3007 LOOP
3008 FETCH c_migrate_rels BULK COLLECT INTO l_src_contract_ids, l_src_contract_types, l_src_contract_ver_nums, l_tgt_contract_ids, l_tgt_contract_types, l_tgt_contract_ver_nums,
3009 l_tgt_contract_nums, l_relationship_ids, l_object_ver_nums, l_created_bys, l_creation_dates, l_last_updated_bys, l_last_update_dates, l_last_update_logins LIMIT 10000;
3010 EXIT WHEN l_src_contract_ids.count = 0;
3011
3012 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Fetched ' || l_src_contract_ids.count || 'rows.');
3013
3014 BEGIN
3015 FORALL i IN 1 .. l_src_contract_ids.count SAVE EXCEPTIONS
3016 INSERT INTO okc_rep_contract_usages
3017 (sequence_id,
3018 contract_type,
3019 contract_id,
3020 contract_version,
3021 business_document_type,
3022 business_document_id,
3023 business_document_version,
3024 document_number,
3025 source_code,
3026 relationship_id,
3027 display_in_contract,
3028 program_id,
3029 program_login_id,
3030 program_application_id,
3031 request_id,
3032 object_version_number,
3033 created_by,
3034 creation_date,
3035 last_updated_by,
3036 last_update_date,
3037 last_update_login)
3038 VALUES
3039 (okc_rep_contract_usages_s.NEXTVAL,
3040 l_src_contract_types(i),
3041 l_src_contract_ids(i),
3042 l_src_contract_ver_nums(i),
3043 l_tgt_contract_types(i),
3044 l_tgt_contract_ids(i),
3045 l_tgt_contract_ver_nums(i),
3046 l_tgt_contract_nums(i),
3047 'MIGRATION',
3048 l_relationship_ids(i),
3049 'Y',
3050 g_conc_program_id,
3051 g_conc_login_id,
3052 g_prog_appl_id,
3053 g_conc_request_id,
3054 l_object_ver_nums(i),
3055 l_created_bys(i),
3056 l_creation_dates(i),
3057 l_last_updated_bys(i),
3058 l_last_update_dates(i),
3059 l_last_update_logins(i)
3060 )
3061 RETURNING sequence_id BULK COLLECT INTO l_seq_nums;
3062
3063 EXCEPTION
3064 WHEN bulk_errors THEN
3065 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of rows failed to migrate out of fetched rows: ' || sql%BULK_EXCEPTIONS.COUNT);
3066
3067 FOR indx IN 1 .. sql%BULK_EXCEPTIONS.COUNT
3068 LOOP
3069 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failed Row: Contract_id:' || l_src_contract_ids(sql%BULK_EXCEPTIONS(indx).ERROR_INDEX) ||
3070 ' Related_contract_id:' || l_tgt_contract_ids(sql%BULK_EXCEPTIONS(indx).ERROR_INDEX) ||
3071 ' Relationship_ID: ' || l_relationship_ids(sql%BULK_EXCEPTIONS(indx).ERROR_INDEX) ||
3072 ' Error:' || SQLERRM(-sql%BULK_EXCEPTIONS(indx).ERROR_CODE));
3073 END LOOP;
3074 END;
3075
3076 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of rows migrated successfully out of fetched rows: ' || l_seq_nums.count);
3077 l_total_rows := l_total_rows + l_seq_nums.count;
3078
3079 END LOOP;
3080 CLOSE c_migrate_rels;
3081
3082 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total No. of rows migrated successfully: ' || l_total_rows);
3083
3084 IF nvl(p_simulate, 'Yes') = 'No' THEN
3085 --After migration commiting the data, if simulate is 'No'
3086 COMMIT;
3087 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done with the migrating data from okc_rep_conract_rels to okc_rep_contract_usages. COMMITTED MIGRATED DATA');
3088 END IF;
3089
3090 IF nvl(p_simulate, 'Yes') = 'Yes' THEN -- if simulate = 'Yes', then there is not need to commit data.
3091 ROLLBACK;
3092 FND_FILE.PUT_LINE(FND_FILE.LOG, 'This program is run in simulate mode, so the MIGRATED DATA WILL NOT BE COMMITTED.');
3093 END IF;
3094
3095 retcode := G_RETURN_CODE_SUCCESS;
3096 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Leaving OKC_REP_CONTRACT_IMP_PVT.'||l_api_name);
3097
3098 EXCEPTION
3099 WHEN OTHERS THEN
3100 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Leaving migrate_contract_relations because of EXCEPTION: ' || sqlerrm);
3101 retcode := G_RETURN_CODE_ERROR;
3102 errbuf := substr(SQLERRM, 1, 200);
3103
3104 END migrate_contract_relations;
3105
3106 END OKC_REP_CONTRACT_IMP_PVT;
3107