[Home] [Help]
PACKAGE BODY: APPS.OKC_K_ENTITY_LOCKS_GRP
Source
1 PACKAGE BODY okc_k_entity_locks_grp
2 /* $Header: OKCGELKB.pls 120.2.12020000.6 2013/03/26 08:51:55 skavutha ship $ */
3 AS
4 l_debug VARCHAR2 (1)
5 := NVL (fnd_profile.VALUE ('AFLOG_ENABLED'), 'N');
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 g_fnd_app CONSTANT VARCHAR2 (200) := okc_api.g_fnd_app;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 g_pkg_name CONSTANT VARCHAR2 (200)
14 := 'OKC_K_ENTITY_LOCKS_GRP';
15 g_app_name CONSTANT VARCHAR2 (3) := okc_api.g_app_name;
16
17 g_module CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
18 ------------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ------------------------------------------------------------------------------
21 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
22 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
23 g_ret_sts_success CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_success;
24 g_ret_sts_error CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_error;
25 g_ret_sts_unexp_error CONSTANT VARCHAR2 (1)
26 := fnd_api.g_ret_sts_unexp_error;
27 g_unexpected_error CONSTANT VARCHAR2 (200) := 'OKC_UNEXPECTED_ERROR';
28 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
29 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
30 g_amend_code_deleted CONSTANT VARCHAR2 (30) := 'DELETED';
31 g_amend_code_added CONSTANT VARCHAR2 (30) := 'ADDED';
32 g_amend_code_updated CONSTANT VARCHAR2 (30) := 'UPDATED';
33
34
35 ----------------------------------------------------
36 -- Declare the private Procedures
37 PROCEDURE refresh_clause (
38 p_source_doc_clause_id IN NUMBER,
39 p_target_doc_clause_id IN OUT NOCOPY NUMBER,
40 p_target_document_type IN VARCHAR2,
41 p_target_document_id IN NUMBER,
42 p_keep_orig_ref IN VARCHAR2 DEFAULT 'N',
43 p_scn_id IN NUMBER DEFAULT NULL,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2
47 );
48
49 PROCEDURE refresh_xprt (
50 p_target_document_type IN VARCHAR2,
51 p_target_document_id IN NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_msg_count OUT NOCOPY NUMBER,
54 x_msg_data OUT NOCOPY VARCHAR2
55 );
56
57 PROCEDURE copy_art_variables (
58 p_source_doc_clause_id IN NUMBER,
59 p_target_doc_clause_id IN NUMBER,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2
63 );
64
65 -----------------------------------------------------
66 PROCEDURE checkbaserecexists (
67 x_rec_exists OUT NOCOPY VARCHAR2,
68 p_entity_name IN VARCHAR2,
69 p_entity_pk1 IN VARCHAR2,
70 p_entity_pk2 IN VARCHAR2 DEFAULT NULL,
71 p_entity_pk3 IN VARCHAR2 DEFAULT NULL,
72 p_entity_pk4 IN VARCHAR2 DEFAULT NULL,
73 p_entity_pk5 IN VARCHAR2 DEFAULT NULL,
74 x_return_status OUT NOCOPY VARCHAR2
75 )
76 IS
77 l_sql VARCHAR2 (2000);
78 l_exists VARCHAR2 (1) := 'N';
79 l_from_table VARCHAR2 (256);
80 l_entity_pk1_column VARCHAR2 (240);
81 l_entity_pk2_column VARCHAR2 (240) := NULL;
82 l_entity_pk3_column VARCHAR2 (240) := NULL;
83 l_entity_pk4_column VARCHAR2 (240);
84 l_entity_pk5_column VARCHAR2 (240);
85 l_entity_pk1_n NUMBER;
86 l_entity_pk2_c VARCHAR2 (240);
87 BEGIN
88 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
89 THEN
90 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
91 g_pkg_name,
92 '1000: Entered CheckBaseRecExists Function'
93 );
94 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
95 g_pkg_name,
96 '1005: Parameters : P_ENTITY_NAME => '
97 || p_entity_name
98 || ' P_ENTITY_PK1 => '
99 || p_entity_pk1
100 || ' P_ENTITY_PK2 => '
101 || p_entity_pk2
102 );
103 END IF;
104
105 IF p_entity_name = G_CLAUSE_ENTITY
106 THEN
107 l_from_table := 'OKC_K_ARTICLES_B';
108 l_entity_pk1_column := 'ID';
109 l_entity_pk1_n := TO_NUMBER (p_entity_pk1);
110 ELSIF p_entity_name = G_SECTION_ENTITY
111 THEN
112 l_from_table := 'OKC_SECTIONS_B';
113 l_entity_pk1_column := 'ID';
114 l_entity_pk1_n := TO_NUMBER (p_entity_pk1);
115 ELSIF p_entity_name = G_XPRT_ENTITY
116 THEN
117 l_from_table := 'OKC_TEMPLATE_USAGES';
118 l_entity_pk1_column := 'DOCUMENT_ID';
119 l_entity_pk1_n := TO_NUMBER (p_entity_pk1);
120 l_entity_pk2_column := 'DOCUMENT_TYPE';
121 l_entity_pk2_c := p_entity_pk2;
122 ELSIF p_entity_name = G_DLVBL_ENTITY
123 THEN
124 l_from_table := 'OKC_DELIVERABLES';
125 l_entity_pk1_column := 'DELIVERABLE_ID';
126 l_entity_pk1_n := TO_NUMBER (p_entity_pk1);
127 END IF;
128
129 l_sql :=
130 'SELECT ''Y'' FROM '
131 || l_from_table
132 || ' WHERE 1 =1 AND '
133 || l_entity_pk1_column
134 || ' = '
135 || l_entity_pk1_n;
136
137 IF p_entity_pk2 IS NOT NULL
138 THEN
139 l_sql :=
140 l_sql
141 || ' AND '
142 || l_entity_pk2_column
143 || ' = '
144 || ''''
145 || l_entity_pk2_c
146 || '''';
147 END IF;
148
149 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
150 THEN
151 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name, '1010: l_sql ' || l_sql);
152 END IF;
153
154 EXECUTE IMMEDIATE l_sql
155 INTO l_exists;
156
157 x_rec_exists := l_exists;
158 x_return_status := g_ret_sts_success;
159 EXCEPTION
160 WHEN NO_DATA_FOUND
161 THEN
162 -- If it does not exists that means that the some other document has merged it's changes.
163 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
164 THEN
165 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
166 g_pkg_name,
167 '9990: exception - record does not exist'
168 );
169 END IF;
170
171 okc_api.set_message
172 (p_app_name => g_app_name,
173 p_msg_name => 'OKC_BASE_ENT_NOT_FOUND'
174 );
175 x_rec_exists := 'N';
176 x_return_status := g_ret_sts_error;
177 WHEN OTHERS
178 THEN
179 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
180 THEN
181 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
182 g_pkg_name,
183 '9992: exception - ' || SQLERRM
184 );
185 END IF;
186
187 okc_api.set_message (p_app_name => g_app_name,
188 p_msg_name => g_unexpected_error,
189 p_token1 => g_sqlcode_token,
190 p_token1_value => SQLCODE,
191 p_token2 => g_sqlerrm_token,
192 p_token2_value => SQLERRM
193 );
194 x_rec_exists := 'N';
195 x_return_status := g_ret_sts_unexp_error;
196 END checkbaserecexists;
197
198 PROCEDURE checklockexists (
199 x_lock_sts OUT NOCOPY NUMBER,
200 p_entity_name IN VARCHAR2,
201 p_entity_pk1 IN VARCHAR2,
202 p_entity_pk2 IN VARCHAR2,
203 p_entity_pk3 IN VARCHAR2,
204 p_entity_pk4 IN VARCHAR2,
205 p_entity_pk5 IN VARCHAR2,
206 p_document_type IN VARCHAR2,
207 p_document_id IN NUMBER,
208 x_document_type OUT NOCOPY VARCHAR2,
209 x_document_id OUT NOCOPY NUMBER,
210 x_return_status OUT NOCOPY VARCHAR2
211 )
212 IS
213 /* 0 --> No locks => Can proceed with lock
214 1 --> Lock already exists => Return. No need to lock
215 -1 --> Lock already exists => But locked by some other document
216 2 --> Two records => Record already locked so throw error.
217 */
218 l_chk_sql VARCHAR2 (2000);
219 l_lock_count NUMBER;
220 l_document_type VARCHAR2 (240);
221 l_document_id NUMBER;
222 BEGIN
223 l_chk_sql :=
224 'SELECT LOCK_by_document_type, LOCK_by_document_id FROM okc_k_entity_locks WHERE entity_name = '
225 || ''''
226 || p_entity_name
227 || ''''
228 || ' and entity_pk1 = '
229 || ''''
230 || p_entity_pk1
231 || '''';
232
233 IF p_entity_pk2 IS NOT NULL
234 THEN
235 l_chk_sql :=
236 l_chk_sql || ' AND entity_pk2 = ' || '''' || p_entity_pk2
237 || '''';
238 END IF;
239
240 IF p_entity_pk3 IS NOT NULL
241 THEN
242 l_chk_sql :=
243 l_chk_sql || ' AND entity_pk3 = ' || '''' || p_entity_pk3
244 || '''';
245 END IF;
246
247 IF p_entity_pk4 IS NOT NULL
248 THEN
249 l_chk_sql :=
250 l_chk_sql || ' AND entity_pk4 = ' || '''' || p_entity_pk4
251 || '''';
252 END IF;
253
254 IF p_entity_pk5 IS NOT NULL
255 THEN
256 l_chk_sql :=
257 l_chk_sql || ' AND entity_pk5 = ' || '''' || p_entity_pk5
258 || '''';
259 END IF;
260
261 EXECUTE IMMEDIATE l_chk_sql
262 INTO l_document_type, l_document_id;
263
264 IF l_document_type = p_document_type AND l_document_id = p_document_id
265 THEN
266 x_lock_sts := 1;
267 x_return_status := g_ret_sts_success;
268 ELSE
269 okc_api.set_message
270 (p_app_name => g_app_name,
271 p_msg_name => 'OKC_BASE_ENT_LOCKED'
272 );
273 x_lock_sts := -1;
274 x_return_status := g_ret_sts_error;
275 END IF;
276 EXCEPTION
277 WHEN NO_DATA_FOUND
278 THEN
279 x_return_status := g_ret_sts_success;
280 x_lock_sts := 0;
281 WHEN TOO_MANY_ROWS
282 THEN
283 x_return_status := g_ret_sts_error;
284 x_lock_sts := 2;
285 okc_api.set_message
286 (p_app_name => g_app_name,
287 p_msg_name => 'OKC_BASE_ENT_LOCKED'
288 );
289 WHEN OTHERS
290 THEN
291 x_return_status := g_ret_sts_unexp_error;
292 x_lock_sts := NULL;
293 okc_api.set_message (p_app_name => g_app_name,
294 p_msg_name => g_unexpected_error,
295 p_token1 => g_sqlcode_token,
296 p_token1_value => SQLCODE,
297 p_token2 => g_sqlerrm_token,
298 p_token2_value => SQLERRM
299 );
300 END checklockexists;
301
302 PROCEDURE lock_entity (
303 p_api_version IN NUMBER,
304 p_init_msg_list IN VARCHAR2
305 DEFAULT fnd_api.g_false,
306 p_commit IN VARCHAR2
307 DEFAULT fnd_api.g_false,
308 p_entity_name IN VARCHAR2,
309 p_entity_pk1 IN VARCHAR2,
310 p_entity_pk2 IN VARCHAR2 DEFAULT NULL,
311 p_entity_pk3 IN VARCHAR2 DEFAULT NULL,
312 p_entity_pk4 IN VARCHAR2 DEFAULT NULL,
313 p_entity_pk5 IN VARCHAR2 DEFAULT NULL,
314 p_lock_by_entity_id IN NUMBER DEFAULT NULL,
315 p_lock_by_document_type IN VARCHAR2,
316 p_lock_by_document_id IN NUMBER,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2
320 )
321 IS
322 l_api_version CONSTANT NUMBER := 1;
323 l_api_name CONSTANT VARCHAR2 (30) := 'G_LOCK_ENTITY';
324
325 CURSOR cur_lock_exists
326 IS
327 SELECT lock_by_entity_id, lock_by_document_type,
328 lock_by_document_id
329 FROM okc_k_entity_locks
330 WHERE entity_name = p_entity_name
331 AND entity_pk1 = p_entity_pk1
332 ;
333
334 l_lock_by_entity_id NUMBER;
335 l_lock_by_document_type VARCHAR2 (240);
336 l_lock_by_document_id NUMBER;
337 x_k_entity_lock_id NUMBER;
338 l_baserecexists VARCHAR2 (1);
339 l_lock_sts NUMBER;
340 x_document_type VARCHAR2 (240);
341 x_document_id NUMBER;
342 BEGIN
343 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
344 THEN
345 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
346 g_pkg_name,
347 '1000: Entered lock_entity'
348 );
349 END IF;
350
351 -- Standard Start of API savepoint
352 SAVEPOINT g_lock_entity_grp;
353
354 -- Standard call to check for call compatibility.
355 IF NOT fnd_api.compatible_api_call (l_api_version,
356 p_api_version,
357 l_api_name,
358 g_pkg_name
359 )
360 THEN
361 RAISE fnd_api.g_exc_unexpected_error;
362 END IF;
363
364 -- Initialize message list if p_init_msg_list is set to TRUE.
365 IF fnd_api.to_boolean (p_init_msg_list)
366 THEN
367 fnd_msg_pub.initialize;
368 END IF;
369
370 IF p_entity_name = 'DUMMYSEC' THEN
371 x_return_status := g_ret_sts_success;
372 INSERT INTO okc_k_entity_locks
373 (k_entity_lock_id, entity_name, entity_pk1, entity_pk2,
374 entity_pk3, entity_pk4, entity_pk5, lock_by_entity_id,
375 lock_by_document_type, lock_by_document_id,
376 object_version_number, created_by, creation_date,
377 last_updated_by, last_update_date, last_update_login)
378 SELECT okc_k_entity_locks_s.NEXTVAL, p_entity_name, p_entity_pk1,
379 p_entity_pk2, p_entity_pk3, p_entity_pk4, p_entity_pk5,
380 p_lock_by_entity_id, p_lock_by_document_type, p_lock_by_document_id,
381 1, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
382 fnd_global.login_id
383 FROM DUAL
384 WHERE NOT EXISTS (
385 SELECT 'Y'
386 FROM okc_k_entity_locks
387 WHERE entity_name = p_entity_name
388 AND lock_by_document_type = p_lock_by_document_type
389 AND lock_by_document_id = p_lock_by_document_id
390 AND lock_by_entity_id = p_lock_by_entity_id);
391 RETURN;
392 END IF;
393
394 -- Initialize API return status to success
395 x_return_status := g_ret_sts_success;
396 -- Check whether the record to be locked exists or not.
397 -- This procedure is not required as we always retain the same id for the entities on Award through out its life.
398 -- One problem we could get this new model is, while working on Mod, Award might got new version but we allow operations on mode with out syncing changes from the latest Award.
399 checkbaserecexists (x_rec_exists => l_baserecexists,
400 p_entity_name => p_entity_name,
401 p_entity_pk1 => p_entity_pk1,
402 p_entity_pk2 => p_entity_pk2,
403 p_entity_pk3 => p_entity_pk3,
404 p_entity_pk4 => p_entity_pk4,
405 p_entity_pk5 => p_entity_pk5,
406 x_return_status => x_return_status
407 );
408
409 ---------------------------------------------
410 IF (x_return_status = g_ret_sts_unexp_error)
411 THEN
412 RAISE fnd_api.g_exc_unexpected_error;
413 ELSIF (x_return_status = g_ret_sts_error)
414 THEN
415 RAISE fnd_api.g_exc_error;
416 END IF;
417
418 --------------------------------------------
419
420 -- Check whether the base record has been locked by other document.
421 checklockexists (x_lock_sts => l_lock_sts,
422 p_entity_name => p_entity_name,
423 p_entity_pk1 => p_entity_pk1,
424 p_entity_pk2 => p_entity_pk2,
425 p_entity_pk3 => p_entity_pk3,
426 p_entity_pk4 => p_entity_pk4,
427 p_entity_pk5 => p_entity_pk5,
428 p_document_type => p_lock_by_document_type,
429 p_document_id => p_lock_by_document_id,
430 x_return_status => x_return_status,
431 x_document_type => x_document_type,
432 x_document_id => x_document_id
433 );
434
435 ---------------------------------------------
436 IF (x_return_status = g_ret_sts_unexp_error)
437 THEN
438 RAISE fnd_api.g_exc_unexpected_error;
439 ELSIF (x_return_status = g_ret_sts_error)
440 THEN
441 RAISE fnd_api.g_exc_error;
442 END IF;
443
444 --------------------------------------------
445 IF NVL (l_lock_sts, -99) = 1 AND x_return_status = g_ret_sts_success
446 THEN
447 -- Entity already locked by the same document
448 x_return_status := g_ret_sts_success;
449 RETURN;
450 END IF;
451
452 -- Call the Package Handler to insert the Row
453 okc_k_entity_locks_pkg.insert_row
454 (p_entity_name => p_entity_name,
455 p_entity_pk1 => p_entity_pk1,
456 p_entity_pk2 => p_entity_pk2,
457 p_entity_pk3 => p_entity_pk3,
458 p_entity_pk4 => p_entity_pk4,
459 p_entity_pk5 => p_entity_pk5,
460 p_lock_by_entity_id => p_lock_by_entity_id,
461 p_lock_by_document_type => p_lock_by_document_type,
462 p_lock_by_document_id => p_lock_by_document_id,
463 p_object_version_number => 1,
464 p_created_by => fnd_global.user_id,
465 p_creation_date => SYSDATE,
466 p_last_updated_by => fnd_global.user_id,
467 p_last_update_date => SYSDATE,
468 p_last_update_login => fnd_global.login_id,
469 x_return_status => x_return_status,
470 x_msg_count => x_msg_count,
471 x_msg_data => x_msg_data,
472 x_k_entity_lock_id => x_k_entity_lock_id
473 );
474
475 --------------------------------------------
476 IF (x_return_status = g_ret_sts_unexp_error)
477 THEN
478 RAISE fnd_api.g_exc_unexpected_error;
479 ELSIF (x_return_status = g_ret_sts_error)
480 THEN
481 RAISE fnd_api.g_exc_error;
482 END IF;
483
484 --------------------------------------------
485
486 -- Check whether two records exists
487 checklockexists (x_lock_sts => l_lock_sts,
488 p_entity_name => p_entity_name,
489 p_entity_pk1 => p_entity_pk1,
490 p_entity_pk2 => p_entity_pk2,
491 p_entity_pk3 => p_entity_pk3,
492 p_entity_pk4 => p_entity_pk4,
493 p_entity_pk5 => p_entity_pk5,
494 p_document_type => p_lock_by_document_type,
495 p_document_id => p_lock_by_document_id,
496 x_return_status => x_return_status,
497 x_document_type => x_document_type,
498 x_document_id => x_document_id
499 );
500
501 ---------------------------------------------
502 IF (x_return_status = g_ret_sts_unexp_error)
503 THEN
504 RAISE fnd_api.g_exc_unexpected_error;
505 ELSIF (x_return_status = g_ret_sts_error)
506 THEN
507 RAISE fnd_api.g_exc_error;
508 END IF;
509
510 --------------------------------------------
511 IF x_return_status = g_ret_sts_success
512 THEN
513 IF NVL (l_lock_sts, -99) = 1
514 THEN
515 x_return_status := g_ret_sts_success;
516 ELSE
517 x_return_status := g_ret_sts_error;
518 END IF;
519 END IF;
520
521 -- Standard check of p_commit
522 IF fnd_api.to_boolean (p_commit)
523 THEN
524 COMMIT WORK;
525 END IF;
526 EXCEPTION
527 WHEN fnd_api.g_exc_error
528 THEN
529 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
530 THEN
531 fnd_log.STRING
532 (FND_LOG.LEVEL_STATEMENT,
533 g_pkg_name,
534 '9999: Leaving lock_entity: OKC_API.G_EXCEPTION_ERROR Exception'
535 );
536 END IF;
537
538 ROLLBACK TO g_lock_entity_grp;
539 x_return_status := g_ret_sts_error;
540 fnd_msg_pub.count_and_get (p_encoded => 'F',
541 p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544 WHEN OTHERS
545 THEN
546 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
547 THEN
548 fnd_log.STRING
549 (FND_LOG.LEVEL_STATEMENT,
550 g_pkg_name,
551 '9999: Leaving lock_entity: OKC_API.G_RET_STS_UNEXP_ERROR Exception'
552 );
553 END IF;
554 okc_api.set_message (p_app_name => g_app_name,
555 p_msg_name => g_unexpected_error,
556 p_token1 => g_sqlcode_token,
557 p_token1_value => SQLCODE,
558 p_token2 => g_sqlerrm_token,
559 p_token2_value => SQLERRM
560 );
561
562
563 ROLLBACK TO g_lock_entity_grp;
564 x_return_status := g_ret_sts_unexp_error;
565 fnd_msg_pub.count_and_get (p_encoded => 'F',
566 p_count => x_msg_count,
567 p_data => x_msg_data
568 );
569 END lock_entity;
570
571 PROCEDURE rebuild_locks (
572 p_api_version IN NUMBER,
573 p_init_msg_list IN VARCHAR2
574 DEFAULT fnd_api.g_false,
575 p_commit IN VARCHAR2
576 DEFAULT fnd_api.g_false,
577 p_update_from_doc_type IN VARCHAR2,
578 p_update_from_doc_id IN NUMBER,
579 p_update_to_doc_type IN VARCHAR2,
580 p_update_to_doc_id IN NUMBER,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2
584 )
585 IS
586 l_api_version CONSTANT NUMBER := 1;
587
588 TYPE l_k_entity_id IS TABLE OF NUMBER
589 INDEX BY PLS_INTEGER;
590
591 l_old_clause_id_tbl l_k_entity_id;
592 l_new_clause_id_tbl l_k_entity_id;
593 l_old_scn_id_tbl l_k_entity_id;
594 l_new_scn_id_tbl l_k_entity_id;
595 l_upd_clause_id_tbl l_k_entity_id;
596 l_upd_sec_id_tbl l_k_entity_id;
597
598 l_old_dlvbl_id_tbl l_k_entity_id;
599 l_new_dlvbl_id_tbl l_k_entity_id;
600 l_upd_dlvbl_id_tbl l_k_entity_id;
601
602 -- Get the list of source clauses from the Modification document
603 CURSOR c_clauses
604 IS
605 SELECT SOURCE.orig_system_reference_id1 old_id, target.ID new_id , klock.LOCK_by_entity_id
606 FROM okc_k_articles_b SOURCE -- Modification Doc
607 ,
608 okc_k_articles_b target -- Base Doc
609 ,
610 okc_k_entity_locks klock -- Locks Table
611 WHERE SOURCE.document_type = p_update_from_doc_type
612 AND SOURCE.document_id = p_update_from_doc_id
613 AND target.document_type = p_update_to_doc_type
614 AND target.document_id = p_update_to_doc_id
615 AND target.orig_system_reference_id1 = SOURCE.ID
616 AND klock.entity_name = G_CLAUSE_ENTITY
617 AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
618
619 CURSOR c_sections
620 IS
621 SELECT SOURCE.orig_system_reference_id1 old_id, target.ID new_id, klock.lock_by_entity_id
622 FROM okc_sections_b SOURCE -- Modification Doc
623 ,
624 okc_sections_b target -- Base Doc
625 ,
626 okc_k_entity_locks klock -- Locks Table
627 WHERE SOURCE.document_type = p_update_from_doc_type
628 AND SOURCE.document_id = p_update_from_doc_id
629 AND target.document_type = p_update_to_doc_type
630 AND target.document_id = p_update_to_doc_id
631 AND target.orig_system_reference_id1 = SOURCE.ID
632 AND klock.entity_name = G_SECTION_ENTITY
633 AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
634
635 CURSOR c_Dummysections -- This is to Achieve Sync Functionality
636 IS
637 SELECT SOURCE.orig_system_reference_id1 old_id, target.ID new_id
638 FROM okc_sections_b SOURCE -- Modification Doc
639 ,
640 okc_sections_b target -- Base Doc
641 ,
642 okc_k_entity_locks klock -- Locks Table
643 WHERE SOURCE.document_type = p_update_from_doc_type
644 AND SOURCE.document_id = p_update_from_doc_id
645 AND target.document_type = p_update_to_doc_type
646 AND target.document_id = p_update_to_doc_id
647 AND target.orig_system_reference_id1 = SOURCE.ID
648 AND klock.entity_name = 'DUMMYSEC'
649 AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
650
651 -- Deliverables changes:
652 CURSOR c_deliverables
653 IS
654 SELECT SOURCE.orig_system_reference_id1 old_id, target.deliverable_id new_id , klock.LOCK_by_entity_id
655 FROM okc_deliverables SOURCE -- Modification Doc
656 ,
657 okc_deliverables target -- Base Doc
658 ,
659 okc_k_entity_locks klock -- Locks Table
660 WHERE SOURCE.business_document_type = p_update_from_doc_type
661 AND SOURCE.business_document_id = p_update_from_doc_id
662 AND target.business_document_type = p_update_to_doc_type
663 AND target.business_document_id = p_update_to_doc_id
664 AND target.orig_system_reference_id1 = SOURCE.DELIVERABLE_ID
665 AND klock.entity_name = 'DELIVERABLE'
666 AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
667 BEGIN
668 -- Standard Start of API savepoint
669 SAVEPOINT g_rebuild_locks_grp;
670 -- Release the locks from Source Document Type and Doc ID
671 -- Delete the locks from Build_from_doc_type and build_to_doc_id
672 release_locks (p_api_version => 1,
673 p_init_msg_list => fnd_api.g_false,
674 p_commit => fnd_api.g_false,
675 p_doc_type => p_update_from_doc_type,
676 p_doc_id => p_update_from_doc_id,
677 x_return_status => x_return_status,
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data
680 );
681
682 -- Rebuild locks
683 -- a) Rebuild locks for clauses
684 OPEN c_clauses;
685
686 FETCH c_clauses
687 BULK COLLECT INTO l_old_clause_id_tbl, l_new_clause_id_tbl,l_upd_clause_id_tbl;
688
689 CLOSE c_clauses;
690
691 FORALL i IN l_old_clause_id_tbl.FIRST .. l_old_clause_id_tbl.LAST
692 UPDATE okc_k_entity_locks
693 SET entity_pk1 = TO_CHAR (l_new_clause_id_tbl (i)),
694 last_updated_by = fnd_global.user_id,
695 last_update_date = SYSDATE,
696 last_update_login = fnd_global.login_id
697 WHERE entity_pk1 = TO_CHAR (l_old_clause_id_tbl (i))
698 AND entity_name = G_CLAUSE_ENTITY;
699
700 -- Update the Lock By articles with the correct orig_sys_ref.
701 FORALL i IN l_new_clause_id_tbl.first..l_new_clause_id_tbl.LAST
702 UPDATE OKC_K_ARTICLES_B
703 SET orig_system_reference_id1 = l_new_clause_id_tbl(i)
704 WHERE id = l_upd_clause_id_tbl(i);
705
706 -- b) Rebuild locks for sections
707 OPEN c_sections;
708
709 FETCH c_sections
710 BULK COLLECT INTO l_old_scn_id_tbl, l_new_scn_id_tbl,l_upd_sec_id_tbl;
711
712 CLOSE c_sections;
713
714 FORALL i IN l_old_scn_id_tbl.FIRST .. l_old_scn_id_tbl.LAST
715 UPDATE okc_k_entity_locks
716 SET entity_pk1 = l_new_scn_id_tbl (i)
717 WHERE entity_pk1 = l_old_scn_id_tbl (i) AND entity_name = G_SECTION_ENTITY;
718
719 FORALL i IN l_new_scn_id_tbl.first..l_new_scn_id_tbl.LAST
720 UPDATE okc_sections_b
721 SET orig_system_reference_id1 = l_new_scn_id_tbl(i)
722 WHERE id = l_upd_sec_id_tbl(i);
723
724 --c) No need to re-build locks for xprt as the document type and document id will not be changed.
725
726 OPEN c_Dummysections;
727 FETCH c_Dummysections
728 BULK COLLECT INTO l_old_scn_id_tbl, l_new_scn_id_tbl;
729 CLOSE c_Dummysections;
730
731
732 FORALL i IN l_old_scn_id_tbl.FIRST .. l_old_scn_id_tbl.LAST
733 UPDATE okc_k_entity_locks
734 SET entity_pk1 = l_new_scn_id_tbl (i)
735 WHERE entity_pk1 = l_old_scn_id_tbl (i)
736 AND entity_name = 'DUMMYSEC';
737
738
739 -- Re-build locks for deliverables.
740 OPEN c_deliverables;
741
742 FETCH c_deliverables
743 BULK COLLECT INTO l_old_dlvbl_id_tbl, l_new_dlvbl_id_tbl,l_upd_dlvbl_id_tbl;
744
745 CLOSE c_deliverables;
746
747 FORALL i IN l_old_dlvbl_id_tbl.FIRST .. l_old_dlvbl_id_tbl.LAST
748 UPDATE okc_k_entity_locks
749 SET entity_pk1 = TO_CHAR (l_new_dlvbl_id_tbl (i)),
750 last_updated_by = fnd_global.user_id,
751 last_update_date = SYSDATE,
752 last_update_login = fnd_global.login_id
753 WHERE entity_pk1 = TO_CHAR (l_old_dlvbl_id_tbl (i))
754 AND entity_name = G_DLVBL_ENTITY;
755
756 -- Update the Lock By dleiverables with the correct orig_sys_ref.
757 FORALL i IN l_new_dlvbl_id_tbl.first..l_new_dlvbl_id_tbl.LAST
758 UPDATE OKC_DELIVERABLES
759 SET orig_system_reference_id1 = l_new_dlvbl_id_tbl(i)
760 WHERE deliverable_id = l_upd_dlvbl_id_tbl(i);
761 -- Standard check of p_commit
762 IF fnd_api.to_boolean (p_commit)
763 THEN
764 COMMIT WORK;
765 END IF;
766 EXCEPTION
767 WHEN OTHERS
768 THEN
769 ROLLBACK TO g_rebuild_locks_grp;
770 END rebuild_locks;
771
772 PROCEDURE release_locks (
773 p_api_version IN NUMBER,
774 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
775 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
776 p_doc_type IN VARCHAR2,
777 p_doc_id IN NUMBER,
778 x_return_status OUT NOCOPY VARCHAR2,
779 x_msg_count OUT NOCOPY NUMBER,
780 x_msg_data OUT NOCOPY VARCHAR2
781 )
782 IS
783 l_api_version CONSTANT NUMBER := 1;
784 BEGIN
785 DELETE FROM okc_k_entity_locks
786 WHERE lock_by_document_type = p_doc_type
787 AND lock_by_document_id = p_doc_id;
788
789 -- Standard check of p_commit
790 IF fnd_api.to_boolean (p_commit)
791 THEN
792 COMMIT WORK;
793 END IF;
794 EXCEPTION
795 WHEN OTHERS
796 THEN
797 NULL;
798 END release_locks;
799
800 FUNCTION islockexists (
801 p_entity_name IN VARCHAR2,
802 p_lock_by_document_type IN VARCHAR2,
803 p_lock_by_document_id IN NUMBER
804 )
805 RETURN VARCHAR2
806 IS
807 CURSOR c_lock_exists
808 IS
809 SELECT 'Y'
810 FROM okc_k_entity_locks
811 WHERE entity_name = p_entity_name
812 AND lock_by_document_type = p_lock_by_document_type
813 AND lock_by_document_id = p_lock_by_document_id;
814
815 l_lock_exists VARCHAR2 (1) := NULL;
816 BEGIN
817 OPEN c_lock_exists;
818
819 FETCH c_lock_exists
820 INTO l_lock_exists;
821
822 CLOSE c_lock_exists;
823
824 RETURN NVL (l_lock_exists, 'N');
825 EXCEPTION
826 WHEN OTHERS
827 THEN
828 RAISE;
829 END islockexists;
830
831 PROCEDURE unlock_entity (
832 p_api_version IN NUMBER,
833 p_init_msg_list IN VARCHAR2
834 DEFAULT fnd_api.g_false,
835 p_commit IN VARCHAR2
836 DEFAULT fnd_api.g_false,
837 p_entity_name IN VARCHAR2,
838 p_entity_pk1 IN VARCHAR2,
839 p_entity_pk2 IN VARCHAR2 DEFAULT NULL,
840 p_entity_pk3 IN VARCHAR2 DEFAULT NULL,
841 p_entity_pk4 IN VARCHAR2 DEFAULT NULL,
842 p_entity_pk5 IN VARCHAR2 DEFAULT NULL,
843 p_lock_by_entity_id IN NUMBER DEFAULT NULL,
844 p_lock_by_document_type IN VARCHAR2,
845 p_lock_by_document_id IN NUMBER,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2
849 )
850 IS
851 l_api_version CONSTANT NUMBER := 1;
852 l_api_name CONSTANT VARCHAR2 (30) := 'unlock_entity';
853 BEGIN
854 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
855 THEN
856 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
857 g_pkg_name,
858 '1000: Entered unlock_entity'
859 );
860 END IF;
861
862 -- Standard Start of API savepoint
863 SAVEPOINT g_unlock_entity_grp;
864
865 -- Standard call to check for call compatibility.
866 IF NOT fnd_api.compatible_api_call (l_api_version,
867 p_api_version,
868 l_api_name,
869 g_pkg_name
870 )
871 THEN
872 RAISE fnd_api.g_exc_unexpected_error;
873 END IF;
874
875 -- Initialize message list if p_init_msg_list is set to TRUE.
876 IF fnd_api.to_boolean (p_init_msg_list)
877 THEN
878 fnd_msg_pub.initialize;
879 END IF;
880
881 -- Initialize API return status to success
882 x_return_status := fnd_api.g_ret_sts_success;
883 -- Calling simple API to delete the row
884 okc_k_entity_locks_pkg.delete_row
885 (p_entity_name => p_entity_name,
886 p_entity_pk1 => p_entity_pk1,
887 p_entity_pk2 => p_entity_pk2,
888 p_entity_pk3 => p_entity_pk3,
889 p_entity_pk4 => p_entity_pk4,
890 p_entity_pk5 => p_entity_pk5,
891 p_lock_by_entity_id => p_lock_by_entity_id,
892 p_lock_by_document_type => p_lock_by_document_type,
893 p_lock_by_document_id => p_lock_by_document_id,
894 x_return_status => x_return_status,
895 x_msg_count => x_msg_count,
896 x_msg_data => x_msg_data
897 );
898
899 --------------------------------------------
900 IF (x_return_status = g_ret_sts_unexp_error)
901 THEN
902 RAISE fnd_api.g_exc_unexpected_error;
903 ELSIF (x_return_status = g_ret_sts_error)
904 THEN
905 RAISE fnd_api.g_exc_error;
906 END IF;
907
908 --------------------------------------------
909 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
910 THEN
911 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
912 g_pkg_name,
913 '9999: completed unlock_entity'
914 );
915 END IF;
916 EXCEPTION
917 WHEN fnd_api.g_exc_error
918 THEN
919 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
920 THEN
921 fnd_log.STRING
922 (FND_LOG.LEVEL_STATEMENT,
923 g_pkg_name,
924 '0000: Leaving unlock_entity: OKC_API.G_EXCEPTION_ERROR Exception'
925 );
926 END IF;
927
928 ROLLBACK TO g_unlock_entity_grp;
929 x_return_status := g_ret_sts_error;
930 fnd_msg_pub.count_and_get (p_encoded => 'F',
931 p_count => x_msg_count,
932 p_data => x_msg_data
933 );
934 WHEN fnd_api.g_exc_unexpected_error
935 THEN
936 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
937 THEN
938 fnd_log.STRING
939 (FND_LOG.LEVEL_STATEMENT,
940 g_pkg_name,
941 '0000: Leaving unlock_entity: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception'
942 );
943 END IF;
944
945 ROLLBACK TO g_unlock_entity_grp;
946 x_return_status := g_ret_sts_unexp_error;
947 fnd_msg_pub.count_and_get (p_encoded => 'F',
948 p_count => x_msg_count,
949 p_data => x_msg_data
950 );
951 WHEN OTHERS
952 THEN
953 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
954 THEN
955 fnd_log.STRING
956 (FND_LOG.LEVEL_STATEMENT,
957 g_pkg_name,
958 '0000: Leaving unlock_entity because of EXCEPTION: '
959 || SQLERRM
960 );
961 END IF;
962
963 okc_api.set_message (p_app_name => g_app_name,
964 p_msg_name => g_unexpected_error,
965 p_token1 => g_sqlcode_token,
966 p_token1_value => SQLCODE,
967 p_token2 => g_sqlerrm_token,
968 p_token2_value => SQLERRM
969 );
970 ROLLBACK TO g_unlock_entity_grp;
971 x_return_status := g_ret_sts_unexp_error;
972
973 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
974 THEN
975 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
976 END IF;
977
978 fnd_msg_pub.count_and_get (p_encoded => 'F',
979 p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982 END unlock_entity;
983
984 PROCEDURE delete_clause (
985 p_doc_clause_id IN NUMBER,
986 x_return_status OUT NOCOPY VARCHAR2
987 )
988 IS
989 BEGIN
990 x_return_status := g_ret_sts_success;
991
992 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
993 THEN
994 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
995 g_pkg_name,
996 '1000: Entered delete_clause : ' || p_doc_clause_id
997 );
998 END IF;
999
1000 --------------------------------------------------
1001 -- Call the API to take care of MRV values etc..
1002 ------------------------------------------------------
1003 okc_k_art_variables_pvt.delete_set (x_return_status => x_return_status,
1004 p_cat_id => p_doc_clause_id
1005 );
1006
1007 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1008 THEN
1009 fnd_log.STRING
1010 (FND_LOG.LEVEL_STATEMENT,
1011 g_pkg_name,
1012 '1010: After Call to okc_k_art_variables_pvt.delete_set : '
1013 || x_return_status
1014 );
1015 END IF;
1016
1017 IF x_return_status <> g_ret_sts_success
1018 THEN
1019 RETURN;
1020 END IF;
1021
1022 --------------------------------------------
1023 -- Calling Simple API for Deleting A Row
1024 --------------------------------------------
1025 okc_k_articles_pvt.delete_row (x_return_status => x_return_status,
1026 p_id => p_doc_clause_id,
1027 p_object_version_number => NULL
1028 );
1029
1030 --------------------------------------------
1031 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1032 THEN
1033 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1034 g_pkg_name,
1035 '1000: Completed delete_clause with status '
1036 || x_return_status
1037 );
1038 END IF;
1039 EXCEPTION
1040 WHEN OTHERS
1041 THEN
1042 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1043 THEN
1044 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1045 g_pkg_name,
1046 '0000: Exception in delete_clause ' || SQLERRM
1047 );
1048 END IF;
1049
1050 x_return_status := g_ret_sts_unexp_error;
1051 END delete_clause;
1052
1053 PROCEDURE copy_clause (
1054 p_source_doc_clause_id IN NUMBER,
1055 p_target_document_type IN VARCHAR2,
1056 p_target_document_id IN NUMBER,
1057 p_target_doc_clause_id IN OUT NOCOPY NUMBER,
1058 p_keep_orig_ref IN VARCHAR2 DEFAULT 'N',
1059 p_scn_id IN NUMBER DEFAULT NULL,
1060 x_return_status OUT NOCOPY VARCHAR2,
1061 x_msg_count OUT NOCOPY NUMBER,
1062 x_msg_data OUT NOCOPY VARCHAR2
1063 )
1064 IS
1065 CURSOR l_get_art_csr
1066 IS
1067 SELECT sav_sae_id, (SELECT standard_yn
1068 FROM okc_articles_all
1069 WHERE article_id = sav_sae_id) standard_yn,
1070 kart.attribute_category, kart.attribute1, kart.attribute2,
1071 kart.attribute3, kart.attribute4, kart.attribute5,
1072 kart.attribute6, kart.attribute7, kart.attribute8,
1073 kart.attribute9, kart.attribute10, kart.attribute11,
1074 kart.attribute12, kart.attribute13, kart.attribute14,
1075 kart.attribute15, kart.source_flag, kart.article_version_id,
1076 kart.change_nonstd_yn, scn_id,
1077 'COPY' orig_system_reference_code,
1078 Decode (p_keep_orig_ref,'Y', kart.orig_system_reference_id1,kart.ID )orig_system_reference_id1, kart.mandatory_yn,
1079 kart.mandatory_rwa, kart.label, kart.display_sequence,
1080 kart.ref_article_id, kart.ref_article_version_id,
1081 kart.orig_article_id, NULL amend_operation_code,
1082 kart.orig_system_reference_id1 src_orig_system_reference_id1
1083 FROM okc_k_articles_b kart
1084 WHERE kart.ID = p_source_doc_clause_id;
1085
1086 l_doc_clause_rec l_get_art_csr%ROWTYPE;
1087 x_article_version_id NUMBER;
1088 x_article_id NUMBER;
1089 x_article_number okc_articles_all.article_number%TYPE;
1090 x_new_doc_clause_id NUMBER;
1091
1092 CURSOR cur_get_clause_scn (cp_clause_id NUMBER)
1093 IS
1094 SELECT scn_id, orig_system_reference_id1
1095 FROM okc_k_articles_b
1096 WHERE ID = cp_clause_id;
1097
1098 l_target_scn_id NUMBER;
1099 l_target_art_orig_ref_id1 NUMBER;
1100 l_src_scn_id NUMBER;
1101 l_src_art_orig_ref_id1 NUMBER;
1102
1103 /*
1104 CURSOR cur_target_doc_scn (p_scn_id NUMBER)
1105 IS
1106 SELECT orig_system_reference_code, orig_system_reference_id1,
1107 amendment_operation_code
1108 FROM okc_sections_b
1109 WHERE ID = p_scn_id
1110 AND document_type = p_target_document_type
1111 AND document_id = p_target_document_id; */
1112
1113
1114 CURSOR cur_target_doc_scn1(cp_orig_sys_ref_id1 NUMBER )
1115 IS
1116 SELECT id,amendment_operation_code
1117 FROM okc_sections_b
1118 WHERE 1=1
1119 AND orig_system_reference_id1 = cp_orig_sys_ref_id1
1120 AND document_type = p_target_document_type
1121 AND document_id = p_target_document_id;
1122 l_target_doc_scn1_rec cur_target_doc_scn1%ROWTYPE;
1123
1124
1125 l_scn_tgt_orig_ref_code VARCHAR2 (240);
1126 l_scn_tgt_orig_ref_id1 NUMBER;
1127 l_scn_tgt_amen_operation_code VARCHAR2 (240);
1128 l_scn_id NUMBER := NULL;
1129 BEGIN
1130 x_return_status := g_ret_sts_success;
1131
1132 --------------------------------------------
1133 -- Insert the clause
1134 --------------------------------------------
1135 OPEN l_get_art_csr;
1136
1137 FETCH l_get_art_csr
1138 INTO l_doc_clause_rec;
1139
1140 IF l_get_art_csr%NOTFOUND
1141 THEN
1142 CLOSE l_get_art_csr;
1143
1144 okc_api.set_message
1145 (p_app_name => g_app_name,
1146 p_msg_name => 'OKC_SRC_ART_NOT_FOUND_RVRT'
1147 );
1148 x_return_status := g_ret_sts_error;
1149 RAISE fnd_api.g_exc_error;
1150 END IF;
1151
1152 CLOSE l_get_art_csr;
1153
1154 IF p_scn_id IS NULL THEN
1155 -- Find the section in the target document corresponding to the section in the source document.
1156 OPEN cur_target_doc_scn1(l_doc_clause_rec.scn_id);
1157 FETCH cur_target_doc_scn1 INTO l_target_doc_scn1_rec;
1158 CLOSE cur_target_doc_scn1;
1159
1160 l_doc_clause_rec.scn_id := l_target_doc_scn1_rec.id;
1161
1162 ELSE
1163
1164 l_doc_clause_rec.scn_id := p_scn_id;
1165
1166 END IF;
1167
1168 IF NVL (l_target_doc_scn1_rec.amendment_operation_code, '?') = 'DELETED'
1169 THEN
1170 okc_api.set_message
1171 (p_app_name => g_app_name,
1172 p_msg_name => 'OKC_RVRT_SEC_BEFORE_ART'
1173 );
1174 x_return_status := g_ret_sts_error;
1175 RAISE fnd_api.g_exc_error;
1176 END IF;
1177
1178 IF l_doc_clause_rec.standard_yn = 'N'
1179 THEN
1180 -- Copying Non-Standard Article and get the new article_id and article_Version_id
1181 okc_articles_grp.copy_article
1182 (p_api_version => 1,
1183 p_init_msg_list => fnd_api.g_false,
1184 p_validation_level => fnd_api.g_valid_level_full,
1185 p_commit => fnd_api.g_false,
1186 p_article_version_id => l_doc_clause_rec.article_version_id,
1187 p_new_article_title => NULL,
1188 p_create_standard_yn => 'N',
1189 x_article_version_id => x_article_version_id,
1190 x_article_id => x_article_id,
1191 x_article_number => x_article_number,
1192 x_return_status => x_return_status,
1193 x_msg_count => x_msg_count,
1194 x_msg_data => x_msg_data
1195 );
1196
1197 IF (x_return_status = g_ret_sts_unexp_error)
1198 THEN
1199 RAISE fnd_api.g_exc_unexpected_error;
1200 ELSIF (x_return_status = g_ret_sts_error)
1201 THEN
1202 RAISE fnd_api.g_exc_error;
1203 END IF;
1204
1205 l_doc_clause_rec.article_version_id := x_article_version_id;
1206 l_doc_clause_rec.sav_sae_id := x_article_id;
1207 END IF;
1208
1209 -- Insert the record into okc_k_articles_b
1210 INSERT INTO okc_k_articles_b
1211 (ID, sav_sae_id,
1212 document_type, document_id,
1213 chr_id,
1214 dnz_chr_id,
1215 source_flag,
1216 mandatory_yn,
1217 mandatory_rwa, scn_id,
1218 label, amendment_description, amendment_operation_code,
1219 article_version_id,
1220 change_nonstd_yn,
1221 orig_system_reference_code,
1222 orig_system_reference_id1, orig_system_reference_id2,
1223 display_sequence,
1224 attribute_category,
1225 attribute1, attribute2,
1226 attribute3, attribute4,
1227 attribute5, attribute6,
1228 attribute7, attribute8,
1229 attribute9, attribute10,
1230 attribute11,
1231 attribute12,
1232 attribute13,
1233 attribute14,
1234 attribute15, print_text_yn,
1235 ref_article_id,
1236 ref_article_version_id, object_version_number,
1237 created_by, creation_date, last_updated_by,
1238 last_update_login, last_update_date,
1239 orig_article_id
1240 )
1241 VALUES (okc_k_articles_b_s.NEXTVAL, l_doc_clause_rec.sav_sae_id,
1242 p_target_document_type, p_target_document_id,
1243 DECODE (p_target_document_type,
1244 'OKC_BUY', p_target_document_id,
1245 'OKC_SELL', p_target_document_id,
1246 'OKO', p_target_document_id,
1247 'OKS', p_target_document_id,
1248 'OKE_BUY', p_target_document_id,
1249 'OKE_SELL', p_target_document_id,
1250 'OKL', p_target_document_id,
1251 NULL
1252 ),
1253 DECODE (p_target_document_type,
1254 'OKC_BUY', p_target_document_id,
1255 'OKC_SELL', p_target_document_id,
1256 'OKO', p_target_document_id,
1257 'OKS', p_target_document_id,
1258 'OKE_BUY', p_target_document_id,
1259 'OKE_SELL', p_target_document_id,
1260 'OKL', p_target_document_id,
1261 NULL
1262 ),
1263 l_doc_clause_rec.source_flag,
1264 l_doc_clause_rec.mandatory_yn,
1265 l_doc_clause_rec.mandatory_rwa, l_doc_clause_rec.scn_id,
1266 l_doc_clause_rec.label, NULL, NULL,
1267 DECODE (p_target_document_type,
1268 okc_terms_util_grp.g_tmpl_doc_type, NULL,
1269 l_doc_clause_rec.article_version_id
1270 ),
1271 l_doc_clause_rec.change_nonstd_yn,
1272 l_doc_clause_rec.orig_system_reference_code,
1273 l_doc_clause_rec.orig_system_reference_id1, NULL,
1274 l_doc_clause_rec.display_sequence,
1275 l_doc_clause_rec.attribute_category,
1276 l_doc_clause_rec.attribute1, l_doc_clause_rec.attribute2,
1277 l_doc_clause_rec.attribute3, l_doc_clause_rec.attribute4,
1278 l_doc_clause_rec.attribute5, l_doc_clause_rec.attribute6,
1279 l_doc_clause_rec.attribute7, l_doc_clause_rec.attribute8,
1280 l_doc_clause_rec.attribute9, l_doc_clause_rec.attribute10,
1281 l_doc_clause_rec.attribute11,
1282 l_doc_clause_rec.attribute12,
1283 l_doc_clause_rec.attribute13,
1284 l_doc_clause_rec.attribute14,
1285 l_doc_clause_rec.attribute15, NULL,
1286 l_doc_clause_rec.ref_article_id,
1287 l_doc_clause_rec.ref_article_version_id, 1,
1288 fnd_global.user_id, SYSDATE, fnd_global.user_id,
1289 fnd_global.login_id, SYSDATE,
1290 l_doc_clause_rec.orig_article_id
1291 )
1292 RETURNING ID
1293 INTO x_new_doc_clause_id;
1294
1295 -- Copy the variables.
1296 copy_art_variables (p_source_doc_clause_id => p_source_doc_clause_id,
1297 p_target_doc_clause_id => x_new_doc_clause_id,
1298 x_return_status => x_return_status,
1299 x_msg_data => x_msg_data,
1300 x_msg_count => x_msg_count
1301 );
1302
1303 p_target_doc_clause_id := x_new_doc_clause_id;
1304
1305 EXCEPTION
1306 WHEN fnd_api.g_exc_error
1307 THEN
1308 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1309 THEN
1310 fnd_log.STRING
1311 (fnd_log.LEVEL_STATEMENT,
1312 g_pkg_name,
1313 '0000: Leaving copy_clause:FND_API.G_EXC_ERROR Exception'
1314 );
1315 END IF;
1316
1317 x_return_status := g_ret_sts_error;
1318 fnd_msg_pub.count_and_get (p_encoded => 'F',
1319 p_count => x_msg_count,
1320 p_data => x_msg_data
1321 );
1322 WHEN fnd_api.g_exc_unexpected_error
1323 THEN
1324 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1325 THEN
1326 fnd_log.STRING
1327 (fnd_log.LEVEL_STATEMENT,
1328 g_pkg_name,
1329 '0000: Leaving copy_clause:FND_API.G_EXC_UNEXPECTED_ERROR Exception'
1330 );
1331 END IF;
1332
1333 x_return_status := g_ret_sts_unexp_error;
1334 fnd_msg_pub.count_and_get (p_encoded => 'F',
1335 p_count => x_msg_count,
1336 p_data => x_msg_data
1337 );
1338 WHEN OTHERS
1339 THEN
1340 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1341 THEN
1342 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1343 g_pkg_name,
1344 '0000: exception in copy_clause ' || SQLERRM
1345 );
1346 END IF;
1347
1348 x_return_status := g_ret_sts_unexp_error;
1349 okc_api.set_message (p_app_name => g_app_name,
1350 p_msg_name => g_unexpected_error,
1351 p_token1 => g_sqlcode_token,
1352 p_token1_value => SQLCODE,
1353 p_token2 => g_sqlerrm_token,
1354 p_token2_value => SQLERRM
1355 );
1356 fnd_msg_pub.count_and_get (p_encoded => 'F',
1357 p_count => x_msg_count,
1358 p_data => x_msg_data
1359 );
1360 END copy_clause;
1361
1362 PROCEDURE refresh_clause (
1363 p_source_doc_clause_id IN NUMBER,
1364 p_target_doc_clause_id IN OUT NOCOPY NUMBER,
1365 p_target_document_type IN VARCHAR2,
1366 p_target_document_id IN NUMBER,
1367 p_keep_orig_ref IN VARCHAR2 DEFAULT 'N',
1368 p_scn_id IN NUMBER DEFAULT NULL,
1369 x_return_status OUT NOCOPY VARCHAR2,
1370 x_msg_count OUT NOCOPY NUMBER,
1371 x_msg_data OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 /*
1375 CURSOR cur_val_scn (cp_scn_id NUMBER)
1376 IS
1377 SELECT 'Y'
1378 FROM okc_sections_b
1379 WHERE ID = cp_scn_id
1380 AND NVL (amendment_operation_code, '?') <> 'DELETED'; */
1381
1382 l_found VARCHAR2 (1);
1383
1384 l_target_doc_clause_id NUMBER := p_target_doc_clause_id;
1385 BEGIN
1386 x_return_status := g_ret_sts_success;
1387
1388 -- Release locks if any
1389 copy_clause (p_source_doc_clause_id => p_source_doc_clause_id,
1390 p_target_doc_clause_id => l_target_doc_clause_id,
1391 p_target_document_type => p_target_document_type,
1392 p_target_document_id => p_target_document_id,
1393 p_keep_orig_ref => p_keep_orig_ref,
1394 p_scn_id => p_scn_id,
1395 x_return_status => x_return_status,
1396 x_msg_data => x_msg_data,
1397 x_msg_count => x_msg_count
1398 );
1399
1400 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1401 THEN
1402 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1403 g_pkg_name,
1404 '9999: Completed refresh_clause '
1405 );
1406 END IF;
1407
1408 --------------------------------------
1409 -- Delete the clause
1410 --------------------------------------
1411 IF p_target_doc_clause_id IS NOT NULL THEN
1412 /*Added for PAR merge, If a clause was added on PAR, it would not be available in Mod. */
1413 delete_clause (p_doc_clause_id => p_target_doc_clause_id,
1414 x_return_status => x_return_status
1415 );
1416 END IF;
1417
1418 p_target_doc_clause_id := l_target_doc_clause_id;
1419
1420 IF x_return_status <> g_ret_sts_success
1421 THEN
1422 RETURN;
1423 END IF;
1424 EXCEPTION
1425 WHEN OTHERS
1426 THEN
1427 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1428 THEN
1429 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1430 g_pkg_name,
1431 '0000: exception in refresh_clause ' || SQLERRM
1432 );
1433 END IF;
1434
1435 x_return_status := g_ret_sts_unexp_error;
1436 okc_api.set_message (p_app_name => g_app_name,
1437 p_msg_name => g_unexpected_error,
1438 p_token1 => g_sqlcode_token,
1439 p_token1_value => SQLCODE,
1440 p_token2 => g_sqlerrm_token,
1441 p_token2_value => SQLERRM
1442 );
1443 fnd_msg_pub.count_and_get (p_encoded => 'F',
1444 p_count => x_msg_count,
1445 p_data => x_msg_data
1446 );
1447 END refresh_clause;
1448
1449
1450 /* Tune the logic */
1451 PROCEDURE refresh_xprt (
1452 p_target_document_type IN VARCHAR2,
1453 p_target_document_id IN NUMBER,
1454 x_return_status OUT NOCOPY VARCHAR2,
1455 x_msg_count OUT NOCOPY NUMBER,
1456 x_msg_data OUT NOCOPY VARCHAR2
1457 )
1458 IS
1459 CURSOR cur_config_data (
1460 cp_document_type IN VARCHAR2,
1461 cp_document_id IN NUMBER
1462 )
1463 IS
1464 SELECT config_header_id, config_revision_number, valid_config_yn,
1465 orig_system_reference_code, orig_system_reference_id1
1466 FROM okc_template_usages
1467 WHERE document_type = cp_document_type
1468 AND document_id = cp_document_id;
1469
1470 TYPE l_clause_id_list IS TABLE OF NUMBER
1471 INDEX BY PLS_INTEGER;
1472
1473 l_clause_id_tbl l_clause_id_list;
1474 l_old_config_header_id okc_template_usages.config_header_id%TYPE;
1475 l_old_config_revision_number okc_template_usages.config_revision_number%TYPE;
1476 l_old_valid_config_yn okc_template_usages.valid_config_yn%TYPE;
1477 l_src_document_type VARCHAR2 (30);
1478 l_src_document_id NUMBER;
1479 l_src_config_header_id okc_template_usages.config_header_id%TYPE;
1480 l_src_config_revision_number okc_template_usages.config_revision_number%TYPE;
1481 l_src_valid_config_yn okc_template_usages.valid_config_yn%TYPE;
1482 l_src_src_document_type VARCHAR2 (30);
1483 l_src_src_document_id NUMBER;
1484 l_new_config_header_id okc_template_usages.config_header_id%TYPE;
1485 l_new_config_rev_nbr okc_template_usages.config_revision_number%TYPE;
1486
1487 CURSOR cur_src_xprt_clauses (
1488 cp_document_type IN VARCHAR2,
1489 cp_document_id IN NUMBER
1490 )
1491 IS
1492 SELECT ID
1493 FROM okc_k_articles_b
1494 WHERE document_type = cp_document_type
1495 AND document_id = cp_document_id
1496 AND source_flag = 'R';
1497
1498 l_config_exists VARCHAR2 (1) := 'N';
1499
1500 CURSOR check_config_exists (
1501 c_config_header_id NUMBER,
1502 c_config_rev_nbr NUMBER
1503 )
1504 IS
1505 SELECT 'Y'
1506 FROM cz_config_hdrs
1507 WHERE config_hdr_id = c_config_header_id
1508 AND config_rev_nbr = c_config_rev_nbr;
1509
1510 CURSOR cur_lock_on_clauses (
1511 cp_document_type IN VARCHAR2,
1512 cp_document_id IN NUMBER
1513 )
1514 IS
1515 SELECT TO_CHAR (orig_system_reference_id1)
1516 FROM okc_k_articles_b
1517 WHERE document_type = cp_document_type
1518 AND document_id = cp_document_id
1519 AND source_flag = 'R'
1520 AND orig_system_reference_id1 IS NOT NULL;
1521
1522 TYPE l_clause_tbl_type IS TABLE OF VARCHAR2 (240)
1523 INDEX BY PLS_INTEGER;
1524
1525 l_clause_tbl l_clause_tbl_type;
1526 l_target_doc_clause_id NUMBER := NULL;
1527 BEGIN
1528 x_return_status := g_ret_sts_success;
1529
1530 OPEN cur_config_data (p_target_document_type, p_target_document_id);
1531
1532 FETCH cur_config_data
1533 INTO l_old_config_header_id, l_old_config_revision_number,
1534 l_old_valid_config_yn, l_src_document_type, l_src_document_id;
1535
1536 CLOSE cur_config_data;
1537
1538 --------------------------------------------
1539 -- Delete xprt suggested clause locks
1540 --------------------------------------------
1541 OPEN cur_lock_on_clauses (p_target_document_type, p_target_document_id);
1542
1543 FETCH cur_lock_on_clauses
1544 BULK COLLECT INTO l_clause_tbl;
1545
1546 CLOSE cur_lock_on_clauses;
1547
1548 if l_clause_tbl.count > 0 then
1549 FORALL i IN l_clause_tbl.FIRST .. l_clause_tbl.LAST
1550 DELETE FROM okc_k_entity_locks
1551 WHERE lock_by_document_type = p_target_document_type
1552 AND lock_by_document_id = p_target_document_id
1553 AND entity_name = okc_k_entity_locks_grp.g_clause_entity
1554 AND entity_pk1 = l_clause_tbl (i);
1555 end if;
1556 --------------------------------------------
1557 -- Delete contract expert suggested clauses
1558 --------------------------------------------
1559 DELETE FROM okc_k_articles_b
1560 WHERE document_type = p_target_document_type
1561 AND document_id = p_target_document_id
1562 AND source_flag = 'R'
1563 ;
1564
1565 --------------------------------------------
1566 -- Delete contract expert suggested clauses
1567 --------------------------------------------
1568 -- Delete sections that are newly added by contract xprt.
1569
1570
1571
1572 --------------------------------------------
1573 -- Delete configuration data
1574 --------------------------------------------
1575 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN --okc rules engine
1576
1577 --deleting the responses
1578 DELETE FROM okc_xprt_doc_ques_response
1579 WHERE doc_id = p_target_document_id
1580 AND doc_type = p_target_document_type;
1581
1582 ELSE --configurator rule engine
1583 IF ( l_old_config_header_id IS NOT NULL
1584 AND l_old_config_revision_number IS NOT NULL
1585 )
1586 THEN
1587 okc_xprt_cz_int_pvt.delete_configuration
1588 (p_api_version => 1.0,
1589 p_init_msg_list => fnd_api.g_false,
1590 p_config_header_id => l_old_config_header_id,
1591 p_config_rev_nbr => l_old_config_revision_number,
1592 x_return_status => x_return_status,
1593 x_msg_data => x_msg_data,
1594 x_msg_count => x_msg_count
1595 );
1596 END IF; -- delete the old configuration
1597
1598
1599 IF (x_return_status <> fnd_api.g_ret_sts_success)
1600 THEN
1601 RAISE fnd_api.g_exc_unexpected_error;
1602 END IF;
1603 END IF; --configurator
1604
1605
1606 --------------------------------------------
1607 -- Add Expert clauses to the target document
1608 --------------------------------------------
1609
1610 -- Get the source clauses
1611 OPEN cur_src_xprt_clauses (l_src_document_type, l_src_document_id);
1612
1613 FETCH cur_src_xprt_clauses
1614 BULK COLLECT INTO l_clause_id_tbl;
1615
1616 CLOSE cur_src_xprt_clauses;
1617
1618 -- Call Copy clause article
1619 if l_clause_id_tbl.count > 0 then
1620 FOR i IN l_clause_id_tbl.FIRST .. l_clause_id_tbl.LAST
1621 LOOP
1622 copy_clause (p_source_doc_clause_id => l_clause_id_tbl (i),
1623 p_target_document_type => p_target_document_type,
1624 p_target_document_id => p_target_document_id,
1625 p_target_doc_clause_id => l_target_doc_clause_id,
1626 x_return_status => x_return_status,
1627 x_msg_data => x_msg_data,
1628 x_msg_count => x_msg_count
1629 );
1630 END LOOP;
1631 end if;
1632 -----------------------------------------------------
1633 -- Copy the configuration data from the Src to target
1634 -----------------------------------------------------
1635 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN
1636 --okc rules engine
1637 --copying responses from the base document
1638 INSERT INTO okc_xprt_doc_ques_response(doc_question_response_id, doc_id, doc_type, question_id, response)
1639 (SELECT okc_xprt_doc_ques_response_s.NEXTVAL, p_target_document_id, p_target_document_type, question_id, response
1640 FROM okc_xprt_doc_ques_response WHERE doc_id = l_src_document_id AND doc_type = l_src_document_type );
1641
1642 --updating the finish flag to the previous state of the base document
1643 UPDATE okc_template_usages
1644 SET contract_expert_finish_flag = (SELECT contract_expert_finish_flag FROM okc_template_usages WHERE document_type = l_src_document_type AND document_id = l_src_document_id)
1645 WHERE document_type = p_target_document_type
1646 AND document_id = p_target_document_id;
1647
1648 ELSE ---configurator
1649 OPEN cur_config_data (l_src_document_type, l_src_document_id);
1650
1651 FETCH cur_config_data
1652 INTO l_src_config_header_id, l_src_config_revision_number,
1653 l_src_valid_config_yn, l_src_src_document_type,
1654 l_src_src_document_id;
1655
1656 CLOSE cur_config_data;
1657
1658 IF l_src_config_header_id IS NOT NULL
1659 THEN
1660 OPEN check_config_exists (l_src_config_header_id,
1661 l_src_config_revision_number
1662 );
1663
1664 FETCH check_config_exists
1665 INTO l_config_exists;
1666
1667 CLOSE check_config_exists;
1668
1669 IF l_config_exists = 'Y'
1670 THEN
1671 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1672 THEN
1673 NULL;
1674 END IF;
1675
1676 /* Call Copy Config API provided by Contract Expert Team */
1677 okc_xprt_cz_int_pvt.copy_configuration
1678 (p_api_version => 1,
1679 p_init_msg_list => okc_api.g_false,
1680 p_config_header_id => l_src_config_header_id,
1681 p_config_rev_nbr => l_src_config_revision_number,
1682 p_new_config_flag => fnd_api.g_true,
1683 x_new_config_header_id => l_new_config_header_id,
1684 x_new_config_rev_nbr => l_new_config_rev_nbr,
1685 x_return_status => x_return_status,
1686 x_msg_data => x_msg_data,
1687 x_msg_count => x_msg_count
1688 );
1689
1690 IF (x_return_status = g_ret_sts_unexp_error)
1691 THEN
1692 RAISE fnd_api.g_exc_unexpected_error;
1693 ELSIF (x_return_status = g_ret_sts_error)
1694 THEN
1695 RAISE fnd_api.g_exc_error;
1696 END IF;
1697
1698 UPDATE okc_template_usages
1699 SET config_header_id = l_new_config_header_id,
1700 config_revision_number = l_new_config_rev_nbr,
1701 valid_config_yn = l_src_valid_config_yn
1702 WHERE document_type = p_target_document_type
1703 AND document_id = p_target_document_id;
1704 END IF;
1705 END IF;
1706 END IF;
1707 EXCEPTION
1708 WHEN OTHERS
1709 THEN
1710 RAISE;
1711 END refresh_xprt;
1712
1713 PROCEDURE copy_art_variables (
1714 p_source_doc_clause_id IN NUMBER,
1715 p_target_doc_clause_id IN NUMBER,
1716 x_return_status OUT NOCOPY VARCHAR2,
1717 x_msg_count OUT NOCOPY NUMBER,
1718 x_msg_data OUT NOCOPY VARCHAR2
1719 )
1720 IS
1721 CURSOR l_get_variables_csr
1722 IS
1723 SELECT p_target_doc_clause_id cat_id, var.variable_code,
1724 busvar.variable_type, busvar.external_yn,
1725 busvar.value_set_id, var.variable_value,
1726 var.variable_value_id, var.override_global_yn,
1727 var.mr_variable_html, var.mr_variable_xml, busvar.mrv_flag
1728 FROM okc_k_art_variables var,
1729 okc_k_articles_b kart,
1730 okc_k_articles_b kart_tar,
1731 okc_bus_variables_b busvar
1732 WHERE kart.ID = p_source_doc_clause_id
1733 AND var.cat_id = kart.ID
1734 AND busvar.variable_code = var.variable_code;
1735
1736 TYPE catlist IS TABLE OF okc_k_art_variables.cat_id%TYPE
1737 INDEX BY BINARY_INTEGER;
1738
1739 TYPE varlist IS TABLE OF okc_k_art_variables.variable_code%TYPE
1740 INDEX BY BINARY_INTEGER;
1741
1742 TYPE vartypelist IS TABLE OF okc_k_art_variables.variable_type%TYPE
1743 INDEX BY BINARY_INTEGER;
1744
1745 TYPE externallist IS TABLE OF okc_k_art_variables.external_yn%TYPE
1746 INDEX BY BINARY_INTEGER;
1747
1748 TYPE valsetlist IS TABLE OF okc_k_art_variables.attribute_value_set_id%TYPE
1749 INDEX BY BINARY_INTEGER;
1750
1751 TYPE varvallist IS TABLE OF okc_k_art_variables.variable_value%TYPE
1752 INDEX BY BINARY_INTEGER;
1753
1754 TYPE varidlist IS TABLE OF okc_k_art_variables.variable_value_id%TYPE
1755 INDEX BY BINARY_INTEGER;
1756
1757 TYPE overrideglobalynlist IS TABLE OF okc_k_art_variables.override_global_yn%TYPE
1758 INDEX BY BINARY_INTEGER;
1759
1760 TYPE mrvariablehtmllist IS TABLE OF okc_k_art_variables.mr_variable_html%TYPE
1761 INDEX BY BINARY_INTEGER;
1762
1763 TYPE mrvariablexmllist IS TABLE OF okc_k_art_variables.mr_variable_xml%TYPE
1764 INDEX BY BINARY_INTEGER;
1765
1766 TYPE mrvflaglist IS TABLE OF okc_bus_variables_b.mrv_flag%TYPE
1767 INDEX BY BINARY_INTEGER;
1768
1769 cat_tbl catlist;
1770 var_tbl varlist;
1771 var_type_tbl vartypelist;
1772 external_yn_tbl externallist;
1773 value_set_id_tbl valsetlist;
1774 var_value_tbl varvallist;
1775 var_value_id_tbl varidlist;
1776 override_global_yn_tbl overrideglobalynlist;
1777 mrvariablehtml_tbl mrvariablehtmllist;
1778 mrvariablexml_tbl mrvariablexmllist;
1779 mrvflag_tbl mrvflaglist;
1780 dochasmrv VARCHAR2 (1);
1781 BEGIN
1782 x_return_status := g_ret_sts_success;
1783
1784 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1785 THEN
1786 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1787 g_pkg_name,
1788 '1000: Entered copy_art_variables'
1789 );
1790 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1791 g_pkg_name,
1792 '1000: Entered p_source_doc_clause_id '
1793 || p_source_doc_clause_id
1794 );
1795 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1796 g_pkg_name,
1797 '1000: Entered p_target_doc_clause_id '
1798 || p_target_doc_clause_id
1799 );
1800 END IF;
1801
1802 OPEN l_get_variables_csr;
1803
1804 FETCH l_get_variables_csr
1805 BULK COLLECT INTO cat_tbl, var_tbl, var_type_tbl, external_yn_tbl,
1806 value_set_id_tbl, var_value_tbl, var_value_id_tbl,
1807 override_global_yn_tbl, mrvariablehtml_tbl, mrvariablexml_tbl,
1808 mrvflag_tbl;
1809
1810 CLOSE l_get_variables_csr;
1811
1812 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1813 THEN
1814 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1815 g_pkg_name,
1816 '1010: Got variable information'
1817 );
1818 END IF;
1819
1820 IF cat_tbl.COUNT > 0
1821 THEN
1822 FORALL i IN cat_tbl.FIRST .. cat_tbl.LAST
1823 INSERT INTO okc_k_art_variables
1824 (cat_id, variable_code, variable_type,
1825 external_yn, attribute_value_set_id,
1826 variable_value, variable_value_id,
1827 override_global_yn, mr_variable_html,
1828 mr_variable_xml, object_version_number,
1829 creation_date, created_by, last_update_date,
1830 last_updated_by, last_update_login
1831 )
1832 VALUES (cat_tbl (i), var_tbl (i), var_type_tbl (i),
1833 external_yn_tbl (i), value_set_id_tbl (i),
1834 var_value_tbl (i), var_value_id_tbl (i),
1835 override_global_yn_tbl (i), mrvariablehtml_tbl (i),
1836 mrvariablexml_tbl (i), 1,
1837 SYSDATE, fnd_global.user_id, SYSDATE,
1838 fnd_global.user_id, fnd_global.login_id
1839 );
1840 END IF;
1841
1842 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1843 THEN
1844 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1845 g_pkg_name,
1846 '1020: Inserted records into okc_k_art_variables ||'
1847 );
1848 END IF;
1849
1850 FOR i IN mrvflag_tbl.FIRST .. mrvflag_tbl.LAST
1851 LOOP
1852 IF NVL (mrvflag_tbl (i), 'N') = 'Y'
1853 THEN
1854 -- Call to UDA API.
1855 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1856 THEN
1857 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1858 g_pkg_name,
1859 '1040: Calling Copy UDA API'
1860 );
1861 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name, '1050: var_tbl (i)');
1862 END IF;
1863
1864 okc_mrv_util.copy_variable_uda_data
1865 (p_from_cat_id => p_source_doc_clause_id,
1866 p_from_variable_code => var_tbl (i),
1867 p_to_cat_id => p_target_doc_clause_id,
1868 p_to_variable_code => var_tbl (i),
1869 x_return_status => x_return_status,
1870 x_msg_count => x_msg_count,
1871 x_msg_data => x_msg_data
1872 );
1873 END IF;
1874 END LOOP;
1875
1876 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1877 THEN
1878 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1879 g_pkg_name,
1880 '9999: Completed copy_art_variables'
1881 );
1882 END IF;
1883 EXCEPTION
1884 WHEN OTHERS
1885 THEN
1886 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1887 THEN
1888 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
1889 g_pkg_name,
1890 '0000: exception in copy_art_variables '
1891 || SQLERRM
1892 );
1893 END IF;
1894
1895 x_return_status := g_ret_sts_unexp_error;
1896 okc_api.set_message (p_app_name => g_app_name,
1897 p_msg_name => g_unexpected_error,
1898 p_token1 => g_sqlcode_token,
1899 p_token1_value => SQLCODE,
1900 p_token2 => g_sqlerrm_token,
1901 p_token2_value => SQLERRM
1902 );
1903 fnd_msg_pub.count_and_get (p_encoded => 'F',
1904 p_count => x_msg_count,
1905 p_data => x_msg_data
1906 );
1907 END copy_art_variables;
1908
1909 PROCEDURE copy_section (
1910 p_source_section_id IN NUMBER,
1911 p_target_document_type IN VARCHAR2,
1912 p_target_document_id IN NUMBER,
1913 p_target_doc_section_id IN OUT NOCOPY NUMBER,
1914 p_keep_orig_ref IN VARCHAR2 DEFAULT 'N',
1915 p_scn_id IN NUMBER DEFAULT NULL,
1916 x_return_status OUT NOCOPY VARCHAR2,
1917 x_msg_count OUT NOCOPY NUMBER,
1918 x_msg_data OUT NOCOPY VARCHAR2
1919 )
1920 IS
1921 CURSOR cur_scn_details (cp_scn_id NUMBER)
1922 IS
1923 SELECT ID, scn_id parent_scn_id, scn_code, amendment_operation_code,
1924 orig_system_reference_id1, heading, label, section_sequence ,
1925 amendment_description,
1926 summary_amend_operation_code
1927 FROM okc_sections_b
1928 WHERE ID = cp_scn_id;
1929
1930
1931 CURSOR cur_scn_details1 (cp_scn_id number)
1932 IS SELECT ID, scn_id parent_scn_id, scn_code, amendment_operation_code,
1933 orig_system_reference_id1, heading, label, section_sequence
1934 FROM okc_sections_b
1935 WHERE orig_system_reference_id1 = cp_scn_id
1936 AND document_type = p_target_document_type
1937 AND document_id=p_target_document_id;
1938
1939 l_tgt_scn_details_rec cur_scn_details%ROWTYPE;
1940 l_par_scn_details_rec cur_scn_details%ROWTYPE;
1941 l_src_scn_details_rec cur_scn_details%ROWTYPE;
1942 l_parent_scn_details_rec cur_scn_details1%ROWTYPE;
1943 l_parent_scn_id NUMBER := NULL;
1944 x_new_section_id NUMBER;
1945 l_src_parent_section_id NUMBER;
1946 BEGIN
1947
1948 x_return_status := g_ret_sts_success;
1949
1950 IF Nvl(p_keep_orig_ref,'N') <> 'Y' THEN
1951 -- Get the current section details
1952 OPEN cur_scn_details (p_target_doc_section_id);
1953 FETCH cur_scn_details
1954 INTO l_tgt_scn_details_rec;
1955 CLOSE cur_scn_details;
1956
1957 -- Find the source record.
1958 OPEN cur_scn_details(l_tgt_scn_details_rec.orig_system_reference_id1);
1959 FETCH cur_scn_details INTO l_src_scn_details_rec;
1960 IF cur_scn_details%NOTFOUND THEN
1961 CLOSE cur_scn_details;
1962 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1963 p_msg_name => 'OKC_SRC_SEC_NOT_FOUND_RVRT'
1964 );
1965 x_return_status := G_RET_STS_ERROR;
1966 RAISE FND_API.G_EXC_ERROR;
1967 END IF;
1968 CLOSE cur_scn_details;
1969
1970 IF l_src_scn_details_rec.parent_scn_id IS NOT NULL THEN
1971 -- The section is a sub-section in the base document, so find the corresponding parent record in the target document.
1972 OPEN cur_scn_details1(l_src_scn_details_rec.parent_scn_id);
1973 FETCH cur_scn_details1 INTO l_parent_scn_details_rec;
1974 CLOSE cur_scn_details1;
1975
1976 IF l_parent_scn_details_rec.amendment_operation_code = 'DELETED'
1977 THEN
1978 okc_api.set_message
1979 (p_app_name => g_app_name,
1980 p_msg_name => 'OKC_RVRT_P_SEC_BEFORE_SEC',
1981 p_token1 => 'SEC_NAME',
1982 p_token1_value => l_parent_scn_details_rec.heading
1983 );
1984 x_return_status := g_ret_sts_error;
1985 RAISE fnd_api.g_exc_error;
1986 ELSE
1987 l_parent_scn_id := l_parent_scn_details_rec.ID;
1988 END IF;
1989 ELSE
1990 l_parent_scn_id := NULL;
1991 END IF;
1992 ELSE
1993
1994 OPEN cur_scn_details (p_source_section_id);
1995 FETCH cur_scn_details
1996 INTO l_par_scn_details_rec;
1997 CLOSE cur_scn_details;
1998 l_parent_scn_id := p_scn_id;
1999
2000 END IF;
2001
2002 IF ((Nvl(p_keep_orig_ref,'N') <> 'Y' AND l_tgt_scn_details_rec.scn_code IS NOT NULL)
2003 OR ( Nvl(p_keep_orig_ref,'N') = 'Y' AND l_par_scn_details_rec.scn_code IS NOT NULL))
2004 THEN
2005 -- section is coming from library no need to copy from base document.
2006 -- For PAR merge to MOd we need to stamp the amendment_operation code etc from PAR
2007 IF (Nvl(p_keep_orig_ref,'N') = 'N' ) THEN
2008 UPDATE okc_sections_b
2009 SET amendment_description = NULL,
2010 amendment_operation_code = NULL,
2011 summary_amend_operation_code = NULL,
2012 scn_id = l_parent_scn_id,
2013 last_amended_by = NULL,
2014 last_amendment_date = NULL
2015 WHERE ID = p_target_doc_section_id;
2016 ELSE
2017 UPDATE okc_sections_b
2018 SET amendment_description = l_par_scn_details_rec.amendment_description,
2019 amendment_operation_code = l_par_scn_details_rec.amendment_operation_code,
2020 summary_amend_operation_code = l_par_scn_details_rec.summary_amend_operation_code,
2021 scn_id = p_scn_id,
2022 last_amended_by = fnd_global.user_id,
2023 last_amendment_date = SYSDATE
2024 WHERE ID = p_target_doc_section_id;
2025 END IF;
2026 ELSE
2027 SELECT okc_sections_b_s.NEXTVAL
2028 INTO x_new_section_id
2029 FROM DUAL;
2030
2031 INSERT INTO okc_sections_b tar_sec
2032 (ID, chr_id, heading, description, document_type,
2033 document_id, scn_id, orig_system_reference_code,
2034 orig_system_reference_id1, orig_system_reference_id2,
2035 section_sequence, label, print_yn, attribute_category,
2036 attribute1, attribute2, attribute3, attribute4,
2037 attribute5, attribute6, attribute7, attribute8,
2038 attribute9, attribute10, attribute11, attribute12,
2039 attribute13, attribute14, attribute15,
2040 object_version_number,created_by, creation_date, last_updated_by, last_update_date, last_update_login,
2041 amendment_description,
2042 amendment_operation_code,
2043 summary_amend_operation_code, last_amended_by,last_amendment_date
2044 )
2045 SELECT x_new_section_id,
2046 DECODE (p_target_document_type,
2047 'OKC_BUY', p_target_document_id,
2048 'OKC_SELL', p_target_document_id,
2049 'OKO', p_target_document_id,
2050 'OKS', p_target_document_id,
2051 'OKE_BUY', p_target_document_id,
2052 'OKE_SELL', p_target_document_id,
2053 'OKL', p_target_document_id,
2054 NULL
2055 ),
2056 heading, description, p_target_document_type,
2057 p_target_document_id, l_parent_scn_id, 'COPY', Decode(p_keep_orig_ref,'Y',orig_system_reference_id1,ID), Decode(p_keep_orig_ref,'Y',orig_system_reference_id2,null),
2058 section_sequence, label, print_yn, attribute_category,
2059 attribute1, attribute2, attribute3, attribute4, attribute5,
2060 attribute6, attribute7, attribute8, attribute9,
2061 attribute10, attribute11, attribute12, attribute13,
2062 attribute14, attribute15,
2063 1,fnd_global.user_id,SYSDATE,fnd_global.user_id,SYSDATE,fnd_global.login_id
2064 ,Decode(p_keep_orig_ref,'Y',amendment_description,null)
2065 ,Decode(p_keep_orig_ref,'Y',amendment_operation_code,null)
2066 ,Decode(p_keep_orig_ref,'Y',summary_amend_operation_code,null)
2067 ,Decode(p_keep_orig_ref,'Y',fnd_global.user_id,null)
2068 ,Decode(p_keep_orig_ref,'Y',sysdate,To_Date(NULL))
2069 FROM okc_sections_b
2070 WHERE ID = p_source_section_id;
2071
2072 IF SQL%ROWCOUNT = 0 THEN
2073 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2074 p_msg_name => 'OKC_SRC_ART_NOT_FOUND_RVRT'
2075 );
2076 x_return_status := G_RET_STS_ERROR;
2077 RAISE FND_API.G_EXC_ERROR;
2078 END IF;
2079
2080 -- Update the existsing sub-sections /clauses with the new section id.
2081 UPDATE okc_sections_b
2082 SET scn_id = x_new_section_id
2083 WHERE document_type = p_target_document_type
2084 AND document_id = p_target_document_id
2085 AND scn_id = p_target_doc_section_id;
2086
2087 UPDATE okc_k_articles_b
2088 SET scn_id = x_new_section_id
2089 WHERE document_type = p_target_document_type
2090 AND document_id = p_target_document_id
2091 AND scn_id = p_target_doc_section_id;
2092 p_target_doc_section_id := x_new_section_id;
2093 END IF;
2094
2095
2096 END copy_section;
2097
2098 PROCEDURE refresh_section (
2099 p_source_section_id IN NUMBER,
2100 p_target_document_type IN VARCHAR2,
2101 p_target_document_id IN NUMBER,
2102 p_target_doc_section_id IN OUT NOCOPY NUMBER,
2103 p_keep_orig_ref IN VARCHAR2 DEFAULT 'N',
2104 p_scn_id IN NUMBER DEFAULT NULL,
2105 x_return_status OUT NOCOPY VARCHAR2,
2106 x_msg_count OUT NOCOPY NUMBER,
2107 x_msg_data OUT NOCOPY VARCHAR2
2108 )
2109 IS
2110 CURSOR cur_tgt_sec_details
2111 IS
2112 SELECT scn_code, ROWID
2113 FROM okc_sections_b
2114 WHERE ID = p_target_doc_section_id;
2115
2116 l_cur_tgt_rec cur_tgt_sec_details%ROWTYPE;
2117
2118
2119 l_api_name VARCHAR2(240) := g_pkg_name||'.' ||'refresh_section';
2120
2121 l_tgt_scn_id NUMBER := p_target_doc_section_id;
2122
2123 BEGIN
2124 x_return_status := g_ret_sts_success;
2125
2126 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2127 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_api_name,'1000: Entered refresh Section');
2128 END IF;
2129
2130
2131 --------------------------------------
2132 -- Copy the Section
2133 --------------------------------------
2134 copy_section (p_source_section_id => p_source_section_id,
2135 p_target_document_type => p_target_document_type,
2136 p_target_document_id => p_target_document_id,
2137 p_target_doc_section_id => l_tgt_scn_id,
2138 p_keep_orig_ref => p_keep_orig_ref,
2139 p_scn_id => p_scn_id,
2140 x_return_status => x_return_status,
2141 x_msg_data => x_msg_data,
2142 x_msg_count => x_msg_count
2143 );
2144
2145 --------------------------------------
2146 -- Delete the Section
2147 --------------------------------------
2148 OPEN cur_tgt_sec_details;
2149
2150 FETCH cur_tgt_sec_details
2151 INTO l_cur_tgt_rec;
2152
2153 CLOSE cur_tgt_sec_details;
2154
2155 IF l_cur_tgt_rec.scn_code IS NULL
2156 THEN
2157 DELETE FROM okc_sections_tl
2158 WHERE ID = p_target_doc_section_id;
2159
2160 DELETE FROM okc_sections_b
2161 WHERE ROWID = l_cur_tgt_rec.ROWID;
2162 END IF;
2163
2164 p_target_doc_section_id := l_tgt_scn_id;
2165
2166 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2167 THEN
2168 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
2169 g_pkg_name,
2170 '9999: Completed refresh_section '
2171 );
2172 END IF;
2173 END refresh_section;
2174
2175 PROCEDURE refresh_deliverable ( p_source_deliverable_id IN NUMBER,
2176 p_target_document_type IN VARCHAR2,
2177 p_target_document_id IN NUMBER,
2178 p_target_deliverable_id IN OUT NOCOPY NUMBER,
2179 p_keep_orig_ref IN VARCHAR2 DEFAULT 'N',
2180 x_return_status OUT NOCOPY VARCHAR2,
2181 x_msg_count OUT NOCOPY NUMBER,
2182 x_msg_data OUT NOCOPY VARCHAR2
2183 )
2184 IS
2185
2186 del_rec okc_deliverables%ROWTYPE;
2187
2188 CURSOR cur_del
2189 IS
2190 SELECT * FROM okc_deliverables WHERE deliverable_id= p_source_deliverable_id;
2191
2192 l_new_target_del_id NUMBER;
2193 x_errorcode NUMBER;
2194 BEGIN
2195
2196 x_return_status := g_ret_sts_success;
2197
2198 -- Copy the p_source_deliverable_id to p_target_document_type, p_target_document_id for -99 version.
2199 OPEN cur_del;
2200 FETCH cur_del INTO del_rec;
2201 CLOSE cur_del;
2202
2203 IF p_keep_orig_ref = 'N' THEN
2204 del_rec.orig_system_reference_code := 'COPY';
2205 del_rec.orig_system_reference_id1 := p_source_deliverable_id;
2206 END IF;
2207
2208 INSERT INTO okc_deliverables
2209 (DELIVERABLE_ID,
2210 BUSINESS_DOCUMENT_TYPE ,
2211 BUSINESS_DOCUMENT_ID ,
2212 BUSINESS_DOCUMENT_NUMBER ,
2213 DELIVERABLE_TYPE ,
2214 RESPONSIBLE_PARTY ,
2215 INTERNAL_PARTY_CONTACT_ID ,
2216 EXTERNAL_PARTY_CONTACT_ID ,
2217 DELIVERABLE_NAME ,
2218 DESCRIPTION ,
2219 COMMENTS ,
2220 DISPLAY_SEQUENCE ,
2221 FIXED_DUE_DATE_YN ,
2222 ACTUAL_DUE_DATE ,
2223 PRINT_DUE_DATE_MSG_NAME ,
2224 RECURRING_YN ,
2225 NOTIFY_PRIOR_DUE_DATE_VALUE ,
2226 NOTIFY_PRIOR_DUE_DATE_UOM ,
2227 NOTIFY_PRIOR_DUE_DATE_YN ,
2228 NOTIFY_COMPLETED_YN ,
2229 NOTIFY_OVERDUE_YN ,
2230 NOTIFY_ESCALATION_YN ,
2231 NOTIFY_ESCALATION_VALUE ,
2232 NOTIFY_ESCALATION_UOM ,
2233 ESCALATION_ASSIGNEE ,
2234 AMENDMENT_OPERATION ,
2235 PRIOR_NOTIFICATION_ID ,
2236 AMENDMENT_NOTES ,
2237 COMPLETED_NOTIFICATION_ID ,
2238 OVERDUE_NOTIFICATION_ID ,
2239 ESCALATION_NOTIFICATION_ID ,
2240 LANGUAGE ,
2241 ORIGINAL_DELIVERABLE_ID ,
2242 REQUESTER_ID ,
2243 EXTERNAL_PARTY_ID ,
2244 EXTERNAL_PARTY_ROLE ,
2245 RECURRING_DEL_PARENT_ID ,
2246 BUSINESS_DOCUMENT_VERSION ,
2247 RELATIVE_ST_DATE_DURATION ,
2248 RELATIVE_ST_DATE_UOM ,
2249 RELATIVE_ST_DATE_EVENT_ID ,
2250 RELATIVE_END_DATE_DURATION ,
2251 RELATIVE_END_DATE_UOM ,
2252 RELATIVE_END_DATE_EVENT_ID ,
2253 REPEATING_DAY_OF_MONTH ,
2254 REPEATING_DAY_OF_WEEK ,
2255 REPEATING_FREQUENCY_UOM ,
2256 REPEATING_DURATION ,
2257 FIXED_START_DATE ,
2258 FIXED_END_DATE ,
2259 MANAGE_YN ,
2260 INTERNAL_PARTY_ID ,
2261 DELIVERABLE_STATUS ,
2262 STATUS_CHANGE_NOTES ,
2263 CREATED_BY ,
2264 CREATION_DATE ,
2265 LAST_UPDATED_BY ,
2266 LAST_UPDATE_DATE ,
2267 LAST_UPDATE_LOGIN ,
2268 OBJECT_VERSION_NUMBER ,
2269 ATTRIBUTE_CATEGORY ,
2270 ATTRIBUTE1 ,
2271 ATTRIBUTE2 ,
2272 ATTRIBUTE3 ,
2273 ATTRIBUTE4 ,
2274 ATTRIBUTE5 ,
2275 ATTRIBUTE6 ,
2276 ATTRIBUTE7 ,
2277 ATTRIBUTE8 ,
2278 ATTRIBUTE9 ,
2279 ATTRIBUTE10 ,
2280 ATTRIBUTE11 ,
2281 ATTRIBUTE12 ,
2282 ATTRIBUTE13 ,
2283 ATTRIBUTE14 ,
2284 ATTRIBUTE15 ,
2285 DISABLE_NOTIFICATIONS_YN ,
2286 LAST_AMENDMENT_DATE ,
2287 BUSINESS_DOCUMENT_LINE_ID ,
2288 EXTERNAL_PARTY_SITE_ID ,
2289 START_EVENT_DATE ,
2290 END_EVENT_DATE ,
2291 SUMMARY_AMEND_OPERATION_CODE,
2292 PAY_HOLD_PRIOR_DUE_DATE_VALUE,
2293 PAY_HOLD_PRIOR_DUE_DATE_UOM,
2294 PAY_HOLD_PRIOR_DUE_DATE_YN,
2295 PAY_HOLD_OVERDUE_YN,
2296 orig_system_reference_code,
2297 orig_system_reference_id1,
2298 orig_system_reference_id2,
2299 RAISE_COMPLETION_EVENT_YN,
2300 del_category_code,
2301 exhibit_code,
2302 data_item_number,
2303 price_group,
2304 estimated_price
2305 , uda_template_id
2306 , schedule_type
2307 )
2308 VALUES (
2309 OKC_DELIVERABLE_ID_S.nextval,
2310 del_rec.BUSINESS_DOCUMENT_TYPE ,
2311 del_rec.BUSINESS_DOCUMENT_ID ,
2312 del_rec.BUSINESS_DOCUMENT_NUMBER ,
2313 del_rec.DELIVERABLE_TYPE ,
2314 del_rec.RESPONSIBLE_PARTY ,
2315 del_rec.INTERNAL_PARTY_CONTACT_ID ,
2316 del_rec.EXTERNAL_PARTY_CONTACT_ID ,
2317 del_rec.DELIVERABLE_NAME ,
2318 del_rec.DESCRIPTION ,
2319 del_rec.COMMENTS ,
2320 del_rec.DISPLAY_SEQUENCE ,
2321 del_rec.FIXED_DUE_DATE_YN ,
2322 del_rec.ACTUAL_DUE_DATE ,
2323 del_rec.PRINT_DUE_DATE_MSG_NAME ,
2324 del_rec.RECURRING_YN ,
2325 del_rec.NOTIFY_PRIOR_DUE_DATE_VALUE ,
2326 del_rec.NOTIFY_PRIOR_DUE_DATE_UOM ,
2327 del_rec.NOTIFY_PRIOR_DUE_DATE_YN ,
2328 del_rec.NOTIFY_COMPLETED_YN ,
2329 del_rec.NOTIFY_OVERDUE_YN ,
2330 del_rec.NOTIFY_ESCALATION_YN ,
2331 del_rec.NOTIFY_ESCALATION_VALUE ,
2332 del_rec.NOTIFY_ESCALATION_UOM ,
2333 del_rec.ESCALATION_ASSIGNEE ,
2334 del_rec.AMENDMENT_OPERATION ,
2335 del_rec.PRIOR_NOTIFICATION_ID ,
2336 del_rec.AMENDMENT_NOTES ,
2337 del_rec.COMPLETED_NOTIFICATION_ID ,
2338 del_rec.OVERDUE_NOTIFICATION_ID ,
2339 del_rec.ESCALATION_NOTIFICATION_ID ,
2340 del_rec.LANGUAGE ,
2341 del_rec.ORIGINAL_DELIVERABLE_ID ,
2342 del_rec.REQUESTER_ID ,
2343 del_rec.EXTERNAL_PARTY_ID ,
2344 del_rec.EXTERNAL_PARTY_ROLE ,
2345 del_rec.RECURRING_DEL_PARENT_ID ,
2346 del_rec.BUSINESS_DOCUMENT_VERSION ,
2347 del_rec.RELATIVE_ST_DATE_DURATION ,
2348 del_rec.RELATIVE_ST_DATE_UOM ,
2349 del_rec.RELATIVE_ST_DATE_EVENT_ID ,
2350 del_rec.RELATIVE_END_DATE_DURATION ,
2351 del_rec.RELATIVE_END_DATE_UOM ,
2352 del_rec.RELATIVE_END_DATE_EVENT_ID ,
2353 del_rec.REPEATING_DAY_OF_MONTH ,
2354 del_rec.REPEATING_DAY_OF_WEEK ,
2355 del_rec.REPEATING_FREQUENCY_UOM ,
2356 del_rec.REPEATING_DURATION ,
2357 del_rec.FIXED_START_DATE ,
2358 del_rec.FIXED_END_DATE ,
2359 del_rec.MANAGE_YN ,
2360 del_rec.INTERNAL_PARTY_ID ,
2361 del_rec.DELIVERABLE_STATUS ,
2362 del_rec.STATUS_CHANGE_NOTES ,
2363 del_rec.CREATED_BY ,
2364 del_rec.CREATION_DATE ,
2365 del_rec.LAST_UPDATED_BY ,
2366 del_rec.LAST_UPDATE_DATE ,
2367 del_rec.LAST_UPDATE_LOGIN ,
2368 del_rec.OBJECT_VERSION_NUMBER ,
2369 del_rec.ATTRIBUTE_CATEGORY ,
2370 del_rec.ATTRIBUTE1 ,
2371 del_rec.ATTRIBUTE2 ,
2372 del_rec.ATTRIBUTE3 ,
2373 del_rec.ATTRIBUTE4 ,
2374 del_rec.ATTRIBUTE5 ,
2375 del_rec.ATTRIBUTE6 ,
2376 del_rec.ATTRIBUTE7 ,
2377 del_rec.ATTRIBUTE8 ,
2378 del_rec.ATTRIBUTE9 ,
2379 del_rec.ATTRIBUTE10 ,
2380 del_rec.ATTRIBUTE11 ,
2381 del_rec.ATTRIBUTE12 ,
2382 del_rec.ATTRIBUTE13 ,
2383 del_rec.ATTRIBUTE14 ,
2384 del_rec.ATTRIBUTE15 ,
2385 del_rec.DISABLE_NOTIFICATIONS_YN ,
2386 del_rec.LAST_AMENDMENT_DATE ,
2387 del_rec.BUSINESS_DOCUMENT_LINE_ID ,
2388 del_rec.EXTERNAL_PARTY_SITE_ID ,
2389 del_rec.START_EVENT_DATE ,
2390 del_rec.END_EVENT_DATE ,
2391 del_rec.SUMMARY_AMEND_OPERATION_CODE,
2392 del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE,
2393 del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM,
2394 del_rec.PAY_HOLD_PRIOR_DUE_DATE_YN,
2395 del_rec.PAY_HOLD_OVERDUE_YN,
2396 del_rec.orig_system_reference_code,
2397 del_rec.orig_system_reference_id1,
2398 del_rec.orig_system_reference_id2,
2399 del_rec.RAISE_COMPLETION_EVENT_YN,
2400 del_rec.del_category_code,
2401 del_rec.exhibit_code,
2402 del_rec.data_item_number,
2403 del_rec.price_group,
2404 del_rec.estimated_price,
2405 del_rec.uda_template_id,
2406 del_rec.schedule_type
2407 )returning deliverable_id INTO l_new_target_del_id ;
2408 okc_deliverable_process_pvt.copy_deliverable_udas(
2409 p_source_deliverable_id,
2410 l_new_target_del_id,
2411 x_return_status,
2412 x_msg_count,
2413 x_msg_data,
2414 x_errorcode
2415 );
2416 if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2418 END IF;
2419
2420 -- Delete the p_target_deliverable_id
2421 IF p_target_deliverable_id IS NOT NULL THEN
2422 DELETE FROM okc_deliverables
2423 WHERE deliverable_id= p_target_deliverable_id;
2424 END IF;
2425
2426 p_target_deliverable_id := l_new_target_del_id;
2427
2428 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2429 THEN
2430 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
2431 g_pkg_name,
2432 '9999: Completed refresh_deliverable '
2433 );
2434 END IF;
2435
2436
2437 exception
2438 WHEN OTHERS THEN
2439 RAISE;
2440 END refresh_deliverable;
2441
2442 PROCEDURE revert_changes (
2443 p_api_version IN NUMBER,
2444 p_init_msg_list IN VARCHAR2
2445 DEFAULT fnd_api.g_false,
2446 p_commit IN VARCHAR2
2447 DEFAULT fnd_api.g_false,
2448 p_K_ENTITY_LOCK_ID IN NUMBER,
2449 p_entity_name IN VARCHAR2,
2450 p_entity_pk1 IN VARCHAR2,
2451 p_entity_pk2 IN VARCHAR2 DEFAULT NULL,
2452 p_entity_pk3 IN VARCHAR2 DEFAULT NULL,
2453 p_entity_pk4 IN VARCHAR2 DEFAULT NULL,
2454 p_entity_pk5 IN VARCHAR2 DEFAULT NULL,
2455 p_lock_by_entity_id IN NUMBER DEFAULT NULL,
2456 p_lock_by_document_type IN VARCHAR2,
2457 p_lock_by_document_id IN NUMBER,
2458 x_return_status OUT NOCOPY VARCHAR2,
2459 x_msg_count OUT NOCOPY NUMBER,
2460 x_msg_data OUT NOCOPY VARCHAR2
2461 )
2462 IS
2463 l_api_version CONSTANT NUMBER := 1;
2464 l_api_name CONSTANT VARCHAR2 (240) := 'revert_changes';
2465 l_lock_by_entity_id NUMBER := p_lock_by_entity_id;
2466 BEGIN
2467 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2468 THEN
2469 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
2470 g_pkg_name,
2471 '1000: Entered revert_changes'
2472 );
2473 END IF;
2474
2475 -- Standard Start of API savepoint
2476 SAVEPOINT g_revert_changes_grp;
2477
2478 -- Standard call to check for call compatibility.
2479 IF NOT fnd_api.compatible_api_call (l_api_version,
2480 p_api_version,
2481 l_api_name,
2482 g_pkg_name
2483 )
2484 THEN
2485 RAISE fnd_api.g_exc_unexpected_error;
2486 END IF;
2487
2488 -- Initialize message list if p_init_msg_list is set to TRUE.
2489 IF fnd_api.to_boolean (p_init_msg_list)
2490 THEN
2491 fnd_msg_pub.initialize;
2492 END IF;
2493
2494 -- Initialize API return status to success
2495 x_return_status := fnd_api.g_ret_sts_success;
2496
2497 IF p_entity_name = okc_k_entity_locks_grp.g_clause_entity
2498 THEN
2499 refresh_clause (p_source_doc_clause_id => TO_NUMBER
2500 (p_entity_pk1),
2501 p_target_document_type => p_lock_by_document_type,
2502 p_target_document_id => p_lock_by_document_id,
2503 p_target_doc_clause_id => l_lock_by_entity_id,
2504 x_return_status => x_return_status,
2505 x_msg_count => x_msg_count,
2506 x_msg_data => x_msg_data
2507 );
2508 ELSIF p_entity_name = okc_k_entity_locks_grp.g_section_entity
2509 THEN
2510 refresh_section (p_source_section_id => TO_NUMBER
2511 (p_entity_pk1),
2512 p_target_document_type => p_lock_by_document_type,
2513 p_target_document_id => p_lock_by_document_id,
2514 p_target_doc_section_id => l_lock_by_entity_id,
2515 x_return_status => x_return_status,
2516 x_msg_count => x_msg_count,
2517 x_msg_data => x_msg_data
2518 );
2519 ELSIF p_entity_name = okc_k_entity_locks_grp.g_xprt_entity
2520 THEN
2521 refresh_xprt (p_target_document_type => p_lock_by_document_type,
2522 p_target_document_id => p_lock_by_document_id,
2523 x_return_status => x_return_status,
2524 x_msg_count => x_msg_count,
2525 x_msg_data => x_msg_data
2526 );
2527 ELSIF p_entity_name = okc_k_entity_locks_grp.G_DLVBL_ENTITY
2528 THEN
2529 refresh_deliverable(
2530 p_source_deliverable_id => TO_NUMBER
2531 (p_entity_pk1),
2532 p_target_document_type => p_lock_by_document_type,
2533 p_target_document_id => p_lock_by_document_id,
2534 p_target_deliverable_id => l_lock_by_entity_id,
2535 x_return_status => x_return_status,
2536 x_msg_count => x_msg_count,
2537 x_msg_data => x_msg_data
2538 );
2539 END IF;
2540
2541 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2542 THEN
2543 fnd_log.STRING (FND_LOG.LEVEL_STATEMENT,
2544 g_pkg_name,
2545 '9999: completed revert_changes'
2546 );
2547 END IF;
2548
2549 -- Delete the lock from the table
2550 DELETE FROM okc_k_entity_locks
2551 WHERE k_entity_lock_id = p_K_ENTITY_LOCK_ID;
2552
2553 -- Standard check of p_commit
2554 IF fnd_api.to_boolean (p_commit)
2555 THEN
2556 COMMIT WORK;
2557 END IF;
2558
2559 EXCEPTION
2560 WHEN fnd_api.g_exc_error
2561 THEN
2562 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2563 THEN
2564 fnd_log.STRING
2565 (FND_LOG.LEVEL_STATEMENT,
2566 g_pkg_name,
2567 '0000: Leaving revert_changes: OKC_API.G_EXCEPTION_ERROR Exception'
2568 );
2569 END IF;
2570
2571 ROLLBACK TO g_revert_changes_grp;
2572 x_return_status := g_ret_sts_error;
2573 fnd_msg_pub.count_and_get (p_encoded => 'F',
2574 p_count => x_msg_count,
2575 p_data => x_msg_data
2576 );
2577 WHEN fnd_api.g_exc_unexpected_error
2578 THEN
2579 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2580 THEN
2581 fnd_log.STRING
2582 (FND_LOG.LEVEL_STATEMENT,
2583 g_pkg_name,
2584 '0000: Leaving revert_changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception'
2585 );
2586 END IF;
2587
2588 ROLLBACK TO g_revert_changes_grp;
2589 x_return_status := g_ret_sts_unexp_error;
2590 fnd_msg_pub.count_and_get (p_encoded => 'F',
2591 p_count => x_msg_count,
2592 p_data => x_msg_data
2593 );
2594 WHEN OTHERS
2595 THEN
2596 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2597 THEN
2598 fnd_log.STRING
2599 (FND_LOG.LEVEL_STATEMENT,
2600 g_pkg_name,
2601 '0000: Leaving revert_changes because of EXCEPTION: '
2602 || SQLERRM
2603 );
2604 END IF;
2605
2606 okc_api.set_message (p_app_name => g_app_name,
2607 p_msg_name => g_unexpected_error,
2608 p_token1 => g_sqlcode_token,
2609 p_token1_value => SQLCODE,
2610 p_token2 => g_sqlerrm_token,
2611 p_token2_value => SQLERRM
2612 );
2613 ROLLBACK TO g_revert_changes_grp;
2614 x_return_status := g_ret_sts_unexp_error;
2615
2616 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2617 THEN
2618 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2619 END IF;
2620
2621 fnd_msg_pub.count_and_get (p_encoded => 'F',
2622 p_count => x_msg_count,
2623 p_data => x_msg_data
2624 );
2625 END revert_changes;
2626
2627
2628 FUNCTION get_document_number(p_document_type IN VARCHAR2, p_document_id IN number)
2629 RETURN VARCHAR2
2630 IS
2631
2632 l_sql VARCHAR2(2000);
2633
2634 l_from_table VARCHAR2(240);
2635 l_where_clause VARCHAR2(1000);
2636 l_pk1_col_name VARCHAR2(240);
2637 l_pk1_id NUMBER;
2638
2639 l_doc_num_col_name VARCHAR2(240);
2640 l_document_number VARCHAR2(240);
2641
2642 BEGIN
2643
2644 IF p_document_type IN ( 'PO_STANDARD_MOD' , 'PA_BLANKET_MOD' , 'PA_CONTRACT_MOD' ) THEN
2645 l_from_table := 'PO_DRAFTS' ;
2646 l_pk1_col_name := 'DRAFT_ID';
2647 l_doc_num_col_name := 'MODIFICATION_NUMBER';
2648 END IF;
2649
2650 l_sql := 'SELECT ' ||l_doc_num_col_name || ' FROM ' || l_from_table || ' WHERE 1=1 ';
2651 l_where_clause := ' AND '|| l_pk1_col_name ||' = '|| p_document_id;
2652
2653 l_sql := l_sql || l_where_clause;
2654
2655 EXECUTE IMMEDIATE l_sql INTO l_document_number;
2656
2657 RETURN l_document_number;
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 RETURN NULL;
2661 END get_document_number;
2662
2663
2664 FUNCTION get_entity_title (p_entity_name IN VARCHAR2, p_entity_pk1 IN VARCHAR2 , p_entity_pk2 IN VARCHAR2)
2665 RETURN VARCHAR2
2666 IS
2667 l_title VARCHAR2(2000) := null;
2668 BEGIN
2669 IF p_entity_name = G_CLAUSE_ENTITY THEN
2670 SELECT okc_terms_util_pvt.get_article_name(kart.sav_sae_id, kart.article_version_id)
2671 INTO l_title
2672 FROM okc_k_articles_b kart
2673 WHERE id=To_Number(p_entity_pk1);
2674 ELSIF p_entity_name = G_SECTION_ENTITY THEN
2675 SELECT heading
2676 INTO l_title
2677 FROM okc_sections_b
2678 WHERE id=To_Number(p_entity_pk1);
2679 ELSIF p_entity_name = G_DLVBL_ENTITY THEN
2680 SELECT deliverable_name
2681 INTO l_title
2682 FROM okc_deliverables
2683 WHERE deliverable_id=To_Number(p_entity_pk1);
2684 ELSE
2685 RETURN l_title;
2686 END IF;
2687
2688 RETURN l_title;
2689 EXCEPTION
2690 WHEN OTHERS THEN
2691 RETURN l_title;
2692 END get_entity_title;
2693
2694
2695 PROCEDURE get_src_doc_details ( p_doc_type IN VARCHAR2,
2696 p_doc_id IN NUMBER,
2697 x_src_doc_type OUT NOCOPY VARCHAR2,
2698 x_src_doc_id OUT NOCOPY VARCHAR2,
2699 x_return_status OUT NOCOPY VARCHAR2,
2700 x_msg_data OUT NOCOPY VARCHAR2,
2701 x_msg_count OUT NOCOPY NUMBER
2702 )
2703 IS
2704
2705 CURSOR cur_get_details_from_usage
2706 IS
2707 SELECT orig_system_reference_code, To_Char(orig_system_reference_id1)
2708 FROM okc_template_usages
2709 WHERE document_type = p_doc_type
2710 AND document_id= p_doc_id;
2711
2712 CURSOR cur_get_details_from_sections
2713 IS
2714 SELECT src.document_type, To_Char(src.document_id)
2715 FROM okc_sections_b tgt,
2716 okc_sections_b src
2717 WHERE tgt.document_type= p_doc_type
2718 AND tgt.document_id= p_doc_id
2719 AND tgt.orig_system_reference_code = 'COPY'
2720 AND src.id=tgt.orig_system_reference_id1;
2721
2722 BEGIN
2723
2724 x_return_status := G_RET_STS_SUCCESS;
2725 x_msg_count := 0;
2726 x_msg_data := NULL;
2727
2728
2729 OPEN cur_get_details_from_usage;
2730 FETCH cur_get_details_from_usage INTO x_src_doc_type,x_src_doc_id;
2731 IF cur_get_details_from_usage%NOTFOUND THEN
2732 OPEN cur_get_details_from_sections;
2733 FETCH cur_get_details_from_sections INTO x_src_doc_type,x_src_doc_id;
2734 IF cur_get_details_from_sections%NOTFOUND THEN
2735 x_return_status := G_RET_STS_ERROR;
2736 OKC_API.SET_MESSAGE( p_app_name => g_app_name
2737 , p_msg_name => 'OKC_SRC_DOC_NOT_FOUND');
2738 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2739 fnd_log.STRING
2740 (FND_LOG.LEVEL_STATEMENT,
2741 g_pkg_name,
2742 '0100: Can not find the source document'
2743 );
2744 end IF;
2745 ELSE
2746 CLOSE cur_get_details_from_sections;
2747 RETURN ;
2748 END IF;
2749 ELSE
2750 CLOSE cur_get_details_from_usage;
2751 RETURN;
2752 END IF;
2753
2754 EXCEPTION
2755 WHEN OTHERS THEN
2756 IF cur_get_details_from_usage%ISOPEN THEN
2757 CLOSE cur_get_details_from_usage;
2758 END IF;
2759
2760 IF cur_get_details_from_sections%ISOPEN THEN
2761 CLOSE cur_get_details_from_sections;
2762 END IF;
2763 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2764 THEN
2765 fnd_log.STRING
2766 (FND_LOG.LEVEL_STATEMENT,
2767 g_pkg_name,
2768 '0000: Leaving get_src_doc_details because of EXCEPTION: '
2769 || SQLERRM
2770 );
2771 END IF;
2772 --RAISE;
2773 END get_src_doc_details;
2774
2775
2776 PROCEDURE revert_entity ( p_api_version IN NUMBER,
2777 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2778 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2779 p_k_entity_lock_id IN NUMBER,
2780 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2781 X_MSG_COUNT OUT NOCOPY NUMBER,
2782 X_MSG_DATA OUT NOCOPY VARCHAR2
2783 )
2784 IS
2785
2786 CURSOR cur_lock_details
2787 IS
2788 SELECT entity_name, entity_pk1, entity_pk2, entity_pk3, entity_pk4, entity_pk5, lock_by_entity_id, lock_by_document_type, lock_by_document_id
2789 FROM OKC_K_ENTITY_LOCKS
2790 WHERE k_entity_lock_id = p_k_entity_lock_id;
2791
2792 lock_details_rec cur_lock_details%rowtype;
2793 BEGIN
2794
2795 OPEN cur_lock_details;
2796 FETCH cur_lock_details INTO lock_details_rec;
2797 CLOSE cur_lock_details;
2798
2799 revert_changes( p_api_version => p_api_version
2800 , p_init_msg_list => p_init_msg_list
2801 , p_commit => p_commit
2802 , p_k_entity_lock_id => p_k_entity_lock_id
2803 , P_ENTITY_NAME => lock_details_rec.entity_name
2804 , P_ENTITY_PK1 => lock_details_rec.entity_pk1
2805 , P_ENTITY_PK2 => lock_details_rec.entity_pk2
2806 , P_ENTITY_PK3 => lock_details_rec.entity_pk3
2807 , P_ENTITY_PK4 => lock_details_rec.entity_pk4
2808 , P_ENTITY_PK5 => lock_details_rec.entity_pk5
2809 , p_lock_by_entity_id => lock_details_rec.lock_by_entity_id
2810 , p_LOCK_BY_DOCUMENT_TYPE => lock_details_rec.lock_by_document_type
2811 , p_LOCK_BY_DOCUMENT_ID => lock_details_rec.lock_by_document_id
2812 , X_RETURN_STATUS => X_RETURN_STATUS
2813 , X_MSG_COUNT => X_MSG_COUNT
2814 , X_MSG_DATA => X_MSG_DATA
2815 );
2816
2817
2818 END revert_entity;
2819
2820
2821 FUNCTION isclauseLockedbyOtherDoc (p_src_kart_id IN NUMBER,p_tgt_document_type IN VARCHAR2,p_tgt_document_id IN NUMBER)
2822 RETURN VARCHAR2
2823 IS
2824 l_lock_exists VARCHAR2(1);
2825 BEGIN
2826 SELECT 'Y'
2827 INTO l_lock_exists
2828 FROM okc_k_entity_locks
2829 WHERE entity_name=G_CLAUSE_ENTITY
2830 AND entity_pk1=To_Char(p_src_kart_id)
2831 AND lock_by_document_id <> p_tgt_document_id;
2832
2833 RETURN Nvl(l_lock_exists,'N');
2834 EXCEPTION
2835 WHEN No_Data_Found THEN
2836 RETURN 'N';
2837 WHEN OTHERS THEN
2838 RAISE;
2839 END isclauseLockedbyOtherDoc;
2840
2841
2842 FUNCTION isEntityLockedbyOtherDoc (p_entity_name IN VARCHAR2,p_src_entity_id IN NUMBER,p_tgt_document_type IN VARCHAR2,p_tgt_document_id IN NUMBER)
2843 RETURN VARCHAR2
2844 IS
2845 l_lock_exists VARCHAR2(1);
2846
2847 CURSOR cur_src_art_details(cp_article_id NUMBER)
2848 IS
2849 SELECT document_type,document_id
2850 FROM okc_k_Articles_b
2851 WHERE id= cp_article_id;
2852
2853
2854 CURSOR cur_src_sec_details(cp_sec_id NUMBER)
2855 IS
2856 SELECT document_type,document_id
2857 FROM okc_sections_b
2858 WHERE id= cp_sec_id;
2859
2860 l_src_doc_type VARCHAR2(30);
2861 l_src_doc_id NUMBER;
2862
2863 CURSOR cur_src_doc_details
2864 IS
2865 SELECT orig_system_reference_code, orig_system_reference_id1
2866 FROM okc_template_usages
2867 WHERE document_type = p_tgt_document_type
2868 AND document_id = p_tgt_document_id;
2869
2870 CURSOR cur_src_del_details (cp_del_id NUMBER)
2871 IS
2872 SELECT business_document_type, business_document_id
2873 FROM okc_deliverables
2874 WHERE deliverable_id = cp_del_id;
2875
2876
2877 l_document_type VARCHAR2(30);
2878 l_document_id NUMBER;
2879
2880 l_lock_by_doc_type VARCHAR2(30);
2881 l_lock_by_document_id NUMBER;
2882
2883 BEGIN
2884
2885 IF p_entity_name = G_CLAUSE_ENTITY THEN
2886 OPEN cur_src_art_details(p_src_entity_id);
2887 FETCH cur_src_art_details INTO l_document_type,l_document_id;
2888 CLOSE cur_src_art_details;
2889 ELSIF p_entity_name = G_SECTION_ENTITY THEN
2890 OPEN cur_src_sec_details(p_src_entity_id);
2891 FETCH cur_src_sec_details INTO l_document_type,l_document_id;
2892 CLOSE cur_src_sec_details;
2893 ELSIF p_entity_name = G_DLVBL_ENTITY THEN
2894 OPEN cur_src_del_details(p_src_entity_id);
2895 FETCH cur_src_del_details INTO l_document_type,l_document_id;
2896 CLOSE cur_src_del_details;
2897
2898 END IF;
2899
2900 IF l_document_type = p_tgt_document_type THEN
2901 RETURN 'N';
2902 END IF;
2903
2904 SELECT lock_by_document_type,lock_by_document_id
2905 INTO l_lock_by_doc_type,l_lock_by_document_id
2906 FROM okc_k_entity_locks
2907 WHERE entity_name=p_entity_name
2908 AND entity_pk1=To_Char(p_src_entity_id);
2909
2910 IF l_lock_by_doc_type = p_tgt_document_type AND l_lock_by_document_id = p_tgt_document_id THEN
2911 RETURN 'N';
2912 ELSE
2913 RETURN 'Y';
2914 END IF;
2915
2916 EXCEPTION
2917 WHEN No_Data_Found THEN
2918 RETURN 'N';
2919 WHEN OTHERS THEN
2920 RAISE;
2921 END isEntityLockedbyOtherDoc;
2922
2923
2924
2925 FUNCTION isSectionLockedbyOtherDoc (p_src_ksec_id IN NUMBER,p_tgt_document_type IN VARCHAR2,p_tgt_document_id IN NUMBER)
2926 RETURN VARCHAR2
2927 IS
2928 l_lock_exists VARCHAR2(1);
2929 BEGIN
2930 SELECT 'Y'
2931 INTO l_lock_exists
2932 FROM okc_k_entity_locks
2933 WHERE entity_name=G_SECTION_ENTITY
2934 AND entity_pk1=To_Char(p_src_ksec_id)
2935 AND lock_by_document_id <> p_tgt_document_id;
2936
2937 RETURN Nvl(l_lock_exists,'N');
2938 EXCEPTION
2939 WHEN No_Data_Found THEN
2940 RETURN 'N';
2941 WHEN OTHERS THEN
2942 RAISE;
2943 END isSectionLockedbyOtherDoc;
2944
2945
2946 FUNCTION isDeliverableLockedbyOtherDoc (p_src_dlvbl_id IN NUMBER,p_tgt_document_type IN VARCHAR2,p_tgt_document_id IN NUMBER)
2947 RETURN VARCHAR2
2948 IS
2949 l_lock_exists VARCHAR2(1);
2950 BEGIN
2951 SELECT 'Y'
2952 INTO l_lock_exists
2953 FROM okc_k_entity_locks
2954 WHERE entity_name='DELIVERABLE'
2955 AND entity_pk1=To_Char(p_src_dlvbl_id)
2956 AND lock_by_document_id <> p_tgt_document_id;
2957
2958 RETURN Nvl(l_lock_exists,'N');
2959 EXCEPTION
2960 WHEN No_Data_Found THEN
2961 RETURN 'N';
2962 WHEN OTHERS THEN
2963 RAISE;
2964 END isDeliverableLockedbyOtherDoc;
2965
2966
2967
2968 FUNCTION isXprtLockedbyOtherDoc (p_tgt_document_type IN VARCHAR2,p_tgt_document_id IN NUMBER)
2969 RETURN VARCHAR2
2970 IS
2971 l_lock_exists VARCHAR2(1);
2972
2973 l_src_doc_type VARCHAR2(30);
2974 l_src_doc_id NUMBER;
2975
2976 CURSOR cur_src_doc_details
2977 IS
2978 SELECT orig_system_reference_code, orig_system_reference_id1
2979 FROM okc_template_usages
2980 WHERE document_type = p_tgt_document_type
2981 AND document_id = p_tgt_document_id;
2982
2983 BEGIN
2984
2985 OPEN cur_src_doc_details;
2986 FETCH cur_src_doc_details INTO l_src_doc_type,l_src_doc_id;
2987 CLOSE cur_src_doc_details;
2988
2989 IF l_src_doc_type IS NULL OR l_src_doc_id IS NULL THEN
2990 RETURN 'N';
2991 END IF;
2992
2993 SELECT 'Y'
2994 INTO l_lock_exists
2995 FROM okc_k_entity_locks
2996 WHERE entity_name='XPRT'
2997 AND entity_pk1= To_Char(l_src_doc_id)
2998 AND entity_pk2 = l_src_doc_type
2999 AND lock_by_document_id <> p_tgt_document_id;
3000
3001 RETURN Nvl(l_lock_exists,'N');
3002 EXCEPTION
3003 WHEN No_Data_Found THEN
3004 RETURN 'N';
3005 WHEN OTHERS THEN
3006 RETURN 'N';
3007 END isXprtLockedbyOtherDoc;
3008
3009 FUNCTION isAnyClauseLockedByOtherDoc(p_variable_code IN VARCHAR2, p_tgt_document_type IN VARCHAR2, p_tgt_document_id IN NUMBER)
3010 RETURN VARCHAR2
3011 IS
3012
3013 CURSOR cur_lock_exists
3014 IS
3015 SELECT 'Y'
3016 FROM okc_k_art_variables v,
3017 okc_k_articles_b k
3018 WHERE k.id = v.cat_id
3019 AND v.variable_code = p_variable_code
3020 AND k.document_type = p_tgt_document_type
3021 AND k.document_id = p_tgt_document_id
3022 AND k.orig_system_reference_code = 'COPY'
3023 AND EXISTS (SELECT 'Lock Exists'
3024 FROM okc_k_entity_locks lck
3025 WHERE lck.entity_name=G_CLAUSE_ENTITY
3026 AND lck.entity_pk1 = To_Char(k.orig_system_reference_id1)
3027 AND lock_by_document_id <> p_tgt_document_id)
3028 ;
3029
3030 l_exists varchar2(1);
3031
3032 BEGIN
3033 OPEN cur_lock_exists;
3034 FETCH cur_lock_exists INTO l_exists;
3035 IF cur_lock_exists%FOUND THEN
3036 CLOSE cur_lock_exists;
3037 RETURN 'Y';
3038 END IF;
3039 CLOSE cur_lock_exists;
3040 RETURN 'N';
3041 END isAnyClauseLockedByOtherDoc;
3042
3043
3044 PROCEDURE isAnyEntityLockedbyOtherDoc( p_doc_type IN VARCHAR2,
3045 p_doc_id IN NUMBER,
3046 x_entity_locked OUT NOCOPY VARCHAR2,
3047 x_return_status OUT NOCOPY VARCHAR2,
3048 x_msg_data OUT NOCOPY VARCHAR2,
3049 x_msg_count OUT NOCOPY NUMBER
3050 )
3051 IS
3052 l_src_doc_type VARCHAR2 (30);
3053 l_src_doc_id NUMBER;
3054
3055 l_lock_exists VARCHAR2(1);
3056
3057
3058 BEGIN
3059
3060 x_return_status := G_RET_STS_SUCCESS;
3061
3062 -- Get the Source document details:
3063 get_src_doc_details ( p_doc_type => p_doc_type,
3064 p_doc_id => p_doc_id,
3065 x_src_doc_type => l_src_doc_type ,
3066 x_src_doc_id => l_src_doc_id,
3067 x_return_status => x_return_status,
3068 x_msg_data => x_msg_data,
3069 x_msg_count => x_msg_count
3070 );
3071
3072 -- Check for xprt locked.
3073 BEGIN
3074
3075 SELECT 'Y'
3076 INTO l_lock_exists
3077 FROM okc_k_entity_locks
3078 WHERE entity_name='XPRT'
3079 AND entity_pk1=To_Char(l_src_doc_id)
3080 AND entity_pk2=l_src_doc_type
3081 AND lock_by_document_id <> p_doc_id;
3082
3083 IF Nvl(l_lock_exists,'N')='Y' THEN
3084 x_entity_locked := 'Y';
3085 RETURN;
3086 END IF;
3087 EXCEPTION
3088 WHEN No_Data_Found THEN
3089 NULL;
3090 WHEN too_many_rows THEN
3091 x_entity_locked := 'Y';
3092 RETURN;
3093 END;
3094
3095 BEGIN
3096 x_return_status := G_RET_STS_SUCCESS;
3097 -- Check for sections
3098 SELECT 'Y'
3099 INTO l_lock_exists
3100 FROM okc_k_entity_locks lck, okc_sections_b sec
3101 WHERE lck.entity_name=G_SECTION_ENTITY
3102 AND lck.entity_pk1= To_Char(sec.id)
3103 AND sec.document_type= l_src_doc_type
3104 AND sec.document_id= l_src_doc_id
3105 AND lck.lock_by_document_id <> p_doc_id;
3106
3107 IF Nvl(l_lock_exists,'N')='Y' THEN
3108 x_entity_locked := 'Y' ;
3109 RETURN;
3110 END IF;
3111 EXCEPTION
3112 WHEN No_Data_Found THEN
3113 NULL;
3114 WHEN too_many_rows THEN
3115 x_entity_locked := 'Y';
3116 RETURN;
3117 END;
3118
3119
3120 BEGIN
3121 -- check for clauses
3122 SELECT 'Y'
3123 INTO l_lock_exists
3124 FROM okc_k_entity_locks lck, okc_k_articles_b kart
3125 WHERE lck.entity_name=G_CLAUSE_ENTITY
3126 AND lck.entity_pk1= To_Char(kart.id)
3127 AND kart.document_type= l_src_doc_type
3128 AND kart.document_id= l_src_doc_id
3129 AND lck.lock_by_document_id <> p_doc_id;
3130
3131 IF Nvl(l_lock_exists,'N')='Y' THEN
3132 x_entity_locked := 'Y' ;
3133 RETURN;
3134 END IF;
3135 EXCEPTION
3136 WHEN No_Data_Found THEN
3137 x_entity_locked := 'N';
3138 RETURN;
3139 WHEN too_many_rows THEN
3140 x_entity_locked := 'Y';
3141 RETURN;
3142 END;
3143
3144
3145 EXCEPTION
3146 WHEN OTHERS THEN
3147 fnd_msg_pub.initialize;
3148 x_return_status := G_RET_STS_ERROR;
3149 okc_api.set_message (p_app_name => g_app_name,
3150 p_msg_name => g_unexpected_error,
3151 p_token1 => g_sqlcode_token,
3152 p_token1_value => SQLCODE,
3153 p_token2 => g_sqlerrm_token,
3154 p_token2_value => SQLERRM
3155 );
3156 fnd_msg_pub.count_and_get (p_encoded => 'F',
3157 p_count => x_msg_count,
3158 p_data => x_msg_data
3159 );
3160
3161 END isAnyEntityLockedbyOtherDoc;
3162
3163
3164 PROCEDURE start_notify_workflow (
3165 p_document_type IN VARCHAR2,
3166 p_document_id IN NUMBER,
3167 p_requestor_id IN NUMBER,
3168 p_actioner_id IN NUMBER,
3169 p_action_requested IN VARCHAR2,
3170 p_action_req_details IN VARCHAR2,
3171 x_return_status OUT NOCOPY VARCHAR2,
3172 x_msg_data OUT NOCOPY VARCHAR2,
3173 x_msg_count OUT NOCOPY NUMBER,
3174 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true
3175
3176 )
3177 IS
3178 l_wf_item_key VARCHAR2 (240);
3179 l_wf_user_key VARCHAR2 (240);
3180 l_wf_item_type VARCHAR2 (100);
3181 l_reqestor_name VARCHAR2 (240);
3182 l_actioner_name VARCHAR2 (240);
3183 l_document_number VARCHAR2 (240);
3184 l_doc_type VARCHAR2 (240);
3185
3186 CURSOR cur_user (cp_user_id NUMBER)
3187 IS
3188 SELECT user_name
3189 FROM fnd_user
3190 WHERE user_id = cp_user_id;
3191
3192 CURSOR cur_doc_type (cp_doc_type VARCHAR2)
3193 IS
3194 SELECT NAME
3195 FROM okc_bus_doc_types_tl
3196 WHERE document_type = cp_doc_type AND LANGUAGE = USERENV ('Lang');
3197 BEGIN
3198 x_return_status := g_ret_sts_success;
3199
3200 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3201 THEN
3202 fnd_log.STRING (fnd_log.level_statement,
3203 g_pkg_name || '.start_notify_workflow',
3204 '0100: start'
3205 );
3206 END IF;
3207
3208 -- Get the Requstor user details:
3209 OPEN cur_user (p_requestor_id);
3210
3211 FETCH cur_user
3212 INTO l_reqestor_name;
3213
3214 CLOSE cur_user;
3215
3216 l_reqestor_name := NVL (l_reqestor_name, fnd_global.user_name);
3217
3218 -- Get the Actioner user details;
3219 OPEN cur_user (p_actioner_id);
3220
3221 FETCH cur_user
3222 INTO l_actioner_name;
3223
3224 CLOSE cur_user;
3225
3226 IF l_actioner_name IS NULL
3227 THEN
3228 -- Actioner name cannot be found
3229 NULL;
3230 -- raise error;
3231 END IF;
3232
3233 l_wf_item_key :=
3234 SUBSTR ( p_document_type
3235 || ':'
3236 || TO_CHAR (SYSDATE, 'DDMONRRHH24MISS')
3237 || ':'
3238 || TO_CHAR (p_document_id),
3239 1,
3240 240
3241 );
3242 l_wf_user_key :=
3243 SUBSTR ( p_document_type
3244 || ':'
3245 || TO_CHAR (p_document_id)
3246 || ':'
3247 || TO_CHAR (SYSDATE, 'DDMONRRHH24MISS'),
3248 1,
3249 240
3250 );
3251 -- Read the profile and use the item type
3252 l_wf_item_type := 'OKCDCACC';
3253
3254 wf_engine.createprocess (itemtype => l_wf_item_type,
3255 itemkey => l_wf_item_key,
3256 process => 'OKCDOCNOTIFY'
3257 );
3258
3259 wf_engine.setitemowner (itemtype => l_wf_item_type,
3260 itemkey => l_wf_item_key,
3261 owner => l_reqestor_name
3262 );
3263 wf_engine.setitemuserkey (itemtype => l_wf_item_type,
3264 itemkey => l_wf_item_key,
3265 userkey => l_wf_user_key
3266 );
3267 --
3268 -- Setting various Workflow Item Attributes
3269 --
3270 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3271 itemkey => l_wf_item_key,
3272 aname => 'REQUESTOR',
3273 avalue => l_reqestor_name
3274 );
3275 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3276 itemkey => l_wf_item_key,
3277 aname => 'ACTIONER',
3278 avalue => l_actioner_name
3279 );
3280 --action requested
3281 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3282 itemkey => l_wf_item_key,
3283 aname => 'ACNREQUESTED',
3284 avalue => p_action_requested
3285 );
3286
3287 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3288 itemkey => l_wf_item_key,
3289 aname => 'REQDETAILS',
3290 avalue => p_action_req_details
3291 );
3292
3293 OPEN cur_doc_type (p_document_type);
3294
3295 FETCH cur_doc_type
3296 INTO l_doc_type;
3297
3298 CLOSE cur_doc_type;
3299
3300 l_doc_type := NVL (l_doc_type, p_document_type);
3301 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3302 itemkey => l_wf_item_key,
3303 aname => 'DOCTYPE',
3304 avalue => l_doc_type
3305 );
3306 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3307 itemkey => l_wf_item_key,
3308 aname => 'DOCID',
3309 avalue => p_document_id
3310 );
3311
3312 l_document_number := get_document_number (p_document_type, p_document_id);
3313 wf_engine.setitemattrtext (itemtype => l_wf_item_type,
3314 itemkey => l_wf_item_key,
3315 aname => 'DOCNUMBER',
3316 avalue => l_document_number
3317 );
3318 wf_engine.startprocess (itemtype => l_wf_item_type, itemkey => l_wf_item_key);
3319 COMMIT;
3320
3321 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3322 THEN
3323 fnd_log.STRING (fnd_log.level_statement,
3324 g_pkg_name || '.start_notify_workflow',
3325 '1000: end'
3326 );
3327 END IF;
3328
3329 IF p_init_msg_list = fnd_api.g_true
3330 THEN
3331 fnd_msg_pub.initialize;
3332 END IF;
3333 EXCEPTION
3334 WHEN OTHERS
3335 THEN
3336 x_return_status := g_ret_sts_error;
3337 okc_api.set_message (p_app_name => g_app_name,
3338 p_msg_name => g_unexpected_error,
3339 p_token1 => g_sqlcode_token,
3340 p_token1_value => SQLCODE,
3341 p_token2 => g_sqlerrm_token,
3342 p_token2_value => SQLERRM
3343 );
3344 fnd_msg_pub.count_and_get (p_encoded => 'F',
3345 p_count => x_msg_count,
3346 p_data => x_msg_data
3347 );
3348
3349 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3350 THEN
3351 fnd_log.STRING (fnd_log.level_statement,
3352 g_pkg_name || '.start_notify_workflow',
3353 '1000:' || SQLERRM
3354 );
3355 END IF;
3356 --wf_core.context(d_module,'NOTIFY_MOD_REQUEST_KO',l_itemtype,l_itemkey);
3357 -- raise_application_error(-20041, 'Failure in start_concmods_notif_workflow ', true);
3358 END start_notify_workflow;
3359
3360
3361 PROCEDURE rebuild_ids( p_api_version IN NUMBER,
3362 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3363 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3364 p_update_from_doc_type IN VARCHAR2, -- Mod document
3365 p_update_from_doc_id IN NUMBER, -- Mod document
3366 p_update_to_doc_type IN VARCHAR2, -- Award document
3367 p_update_to_doc_id IN NUMBER, -- Award document
3368 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3369 X_MSG_COUNT OUT NOCOPY NUMBER,
3370 X_MSG_DATA OUT NOCOPY VARCHAR2
3371
3372 )
3373 IS
3374
3375 -- Sections
3376 CURSOR cur_sec_ids
3377 IS
3378 SELECT tgtAward.id,srcMod.orig_system_reference_id1
3379 FROM
3380 okc_sections_b tgtAward
3381 ,okc_sections_b srcMod
3382 WHERE srcMod.document_type= p_update_from_doc_type
3383 AND srcMod.document_id= p_update_from_doc_id
3384 AND tgtAward.document_type= p_update_to_doc_type
3385 AND tgtAward.document_id= p_update_to_doc_id
3386 AND tgtAward.orig_system_reference_id1= SrcMod.id
3387 AND srcMod.orig_system_reference_id1 IS NOT NULL; -- for newly added sections on a mod, this column would be having null value.
3388
3389
3390 -- Articles
3391 CURSOR cur_kart_ids
3392 IS
3393 SELECT tgtAward.id,srcMod.orig_system_reference_id1
3394 FROM
3395 okc_k_articles_b tgtAward
3396 ,okc_k_articles_b srcMod
3397 WHERE srcMod.document_type= p_update_from_doc_type
3398 AND srcMod.document_id= p_update_from_doc_id
3399 AND tgtAward.document_type= p_update_to_doc_type
3400 AND tgtAward.document_id= p_update_to_doc_id
3401 AND tgtAward.orig_system_reference_id1= SrcMod.id
3402 AND srcMod.orig_system_reference_id1 IS NOT NULL;
3403
3404 -- deliverables.
3405 CURSOR cur_del_ids
3406 IS
3407 SELECT tgtAward.deliverable_id,srcMod.orig_system_reference_id1
3408 FROM
3409 okc_deliverables tgtAward
3410 ,okc_deliverables srcMod
3411 WHERE srcMod.business_document_type= p_update_from_doc_type
3412 AND srcMod.business_document_id= p_update_from_doc_id
3413 AND tgtAward.business_document_type= p_update_to_doc_type
3414 AND tgtAward.business_document_id= p_update_to_doc_id
3415 AND tgtAward.orig_system_reference_id1= SrcMod.deliverable_id
3416 AND srcMod.orig_system_reference_id1 IS NOT NULL;
3417
3418 TYPE l_id_tbl_type IS TABLE OF NUMBER
3419 INDEX BY PLS_INTEGER;
3420
3421 l_tgt_id_tbl l_id_tbl_type;
3422 l_src_ref_id_tbl l_id_tbl_type;
3423
3424 l_api_name VARCHAR2(240) := 'REBUILD_IDS';
3425
3426 BEGIN
3427
3428 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3429 THEN
3430 fnd_log.STRING (fnd_log.level_statement, G_MODULE||l_api_name,
3431 '100: Inside OKC_K_ENTITY_LOCKS_GRP.REBUILD_IDS'
3432 );
3433
3434 fnd_log.STRING (fnd_log.level_statement, G_MODULE||l_api_name,
3435 '100: p_update_from_doc_type : ' || p_update_from_doc_type
3436 );
3437 fnd_log.STRING (fnd_log.level_statement, G_MODULE||l_api_name,
3438 '100: p_update_from_doc_id : ' || p_update_from_doc_id
3439 );
3440 fnd_log.STRING (fnd_log.level_statement, G_MODULE||l_api_name,
3441 '100: p_update_to_doc_type : ' || p_update_to_doc_type
3442 );
3443 fnd_log.STRING (fnd_log.level_statement, G_MODULE||l_api_name,
3444 '100: p_update_to_doc_id : ' || p_update_to_doc_id
3445 );
3446 END IF;
3447
3448 -- Standard Start of API savepoint
3449 SAVEPOINT g_rebuild_ids_grp;
3450
3451 X_RETURN_STATUS := G_RET_STS_SUCCESS;
3452
3453 -- sections
3454 OPEN cur_sec_ids;
3455 FETCH cur_sec_ids BULK COLLECT INTO l_tgt_id_tbl, l_src_ref_id_tbl;
3456 CLOSE cur_sec_ids;
3457
3458 IF l_tgt_id_tbl.Count > 0 THEN
3459
3460 FORALL i IN l_tgt_id_tbl.first..l_tgt_id_tbl.last
3461 update okc_sections_b
3462 set id= l_src_ref_id_tbl(i)
3463 where id=l_tgt_id_tbl(i);
3464
3465 FORALL i IN l_tgt_id_tbl.first..l_tgt_id_tbl.last
3466 UPDATE okc_sections_b
3467 SET scn_id= l_src_ref_id_tbl(i)
3468 WHERE scn_id= l_tgt_id_tbl(i)
3469 AND document_type= p_update_to_doc_type
3470 AND document_id=p_update_from_doc_id;
3471
3472 FORALL i IN l_tgt_id_tbl.first..l_tgt_id_tbl.last
3473 UPDATE okc_k_articles_b
3474 SET scn_id= l_src_ref_id_tbl(i)
3475 WHERE scn_id= l_tgt_id_tbl(i)
3476 AND document_type= p_update_to_doc_type
3477 AND document_id=p_update_from_doc_id;
3478
3479 END IF;
3480
3481 -- Clauses
3482 OPEN cur_kart_ids;
3483 FETCH cur_kart_ids BULK COLLECT INTO l_tgt_id_tbl, l_src_ref_id_tbl;
3484 CLOSE cur_kart_ids;
3485
3486
3487 -- update variable values and clauses.
3488 IF l_tgt_id_tbl.Count>0 then
3489
3490 forall i in l_tgt_id_tbl.first..l_tgt_id_tbl.last
3491 update okc_k_articles_b
3492 set cat_id= l_src_ref_id_tbl(i)
3493 where id=l_tgt_id_tbl(i);
3494
3495
3496 forall i in l_tgt_id_tbl.first..l_tgt_id_tbl.last
3497 update okc_k_art_variables
3498 set cat_id= l_src_ref_id_tbl(i)
3499 where cat_id=l_tgt_id_tbl(i);
3500
3501 -- Add code for MRV
3502 forall i in l_tgt_id_tbl.first..l_tgt_id_tbl.last
3503 update OKC_K_ART_VAR_EXT_B
3504 set cat_id= l_src_ref_id_tbl(i)
3505 where cat_id=l_tgt_id_tbl(i);
3506
3507 forall i in l_tgt_id_tbl.first..l_tgt_id_tbl.last
3508 update OKC_K_ART_VAR_EXT_tl
3509 set cat_id= l_src_ref_id_tbl(i)
3510 where cat_id=l_tgt_id_tbl(i);
3511
3512 END IF;
3513
3514 OPEN cur_del_ids;
3515 FETCH cur_del_ids BULK COLLECT INTO l_tgt_id_tbl, l_src_ref_id_tbl;
3516 CLOSE cur_del_ids;
3517
3518 IF l_tgt_id_tbl.Count > 0 THEN
3519 FORALL i IN l_tgt_id_tbl.first..l_tgt_id_tbl.last
3520 UPDATE okc_deliverables
3521 SET deliverable_id = l_src_ref_id_tbl(i)
3522 WHERE deliverable_id = l_tgt_id_tbl(i);
3523 END IF;
3524
3525 -- Release the locks acquired by Mod.
3526 -- Release the locks from Source Document Type and Doc ID
3527 -- Delete the locks from Build_from_doc_type and build_to_doc_id
3528 release_locks (p_api_version => 1,
3529 p_init_msg_list => fnd_api.g_false,
3530 p_commit => fnd_api.g_false,
3531 p_doc_type => p_update_from_doc_type,
3532 p_doc_id => p_update_from_doc_id,
3533 x_return_status => x_return_status,
3534 x_msg_count => x_msg_count,
3535 x_msg_data => x_msg_data
3536 );
3537 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3539 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3540 RAISE FND_API.G_EXC_ERROR ;
3541 END IF;
3542
3543
3544 -- Standard check of p_commit
3545 IF fnd_api.to_boolean (p_commit)
3546 THEN
3547 COMMIT WORK;
3548 END IF;
3549
3550 EXCEPTION
3551 WHEN OTHERS THEN
3552 ROLLBACK TO g_rebuild_locks_grp;
3553 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3554 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving rebuild_ids because of EXCEPTION: '||sqlerrm);
3555 END IF;
3556
3557 okc_api.set_message (p_app_name => g_app_name,
3558 p_msg_name => g_unexpected_error,
3559 p_token1 => g_sqlcode_token,
3560 p_token1_value => SQLCODE,
3561 p_token2 => g_sqlerrm_token,
3562 p_token2_value => SQLERRM
3563 );
3564 x_return_status := g_ret_sts_unexp_error;
3565
3566 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3567 THEN
3568 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3569 END IF;
3570
3571 fnd_msg_pub.count_and_get (p_encoded => 'F',
3572 p_count => x_msg_count,
3573 p_data => x_msg_data
3574 );
3575
3576 END rebuild_ids;
3577
3578 FUNCTION has_amended_terms ( p_doc_type IN VARCHAR2, p_doc_id IN NUMBER
3579 )
3580 RETURN VARCHAR2
3581 IS
3582
3583 CURSOR cur_section
3584 IS
3585 SELECT 'Y'
3586 FROM okc_sections_b
3587 WHERE document_type= p_doc_type
3588 AND document_id= p_doc_id
3589 AND amendment_operation_code IS NOT NULL;
3590
3591 CURSOR cur_clause
3592 IS
3593 SELECT 'Y'
3594 FROM okc_k_articles_b
3595 WHERE document_type= p_doc_type
3596 AND document_id= p_doc_id
3597 AND amendment_operation_code IS NOT NULL;
3598
3599
3600 CURSOR cur_deliverable
3601 IS
3602 SELECT 'Y'
3603 FROM okc_k_articles_b
3604 WHERE document_type= p_doc_type
3605 AND document_id= p_doc_id
3606 AND amendment_operation_code IS NOT NULL;
3607
3608 l_temp VARCHAR2 (1);
3609
3610
3611
3612 BEGIN
3613
3614 OPEN cur_section;
3615 FETCH cur_section INTO l_temp;
3616 IF cur_section%FOUND THEN
3617 CLOSE cur_section;
3618 RETURN l_temp;
3619 END IF;
3620 CLOSE cur_section;
3621
3622 OPEN cur_clause;
3623 FETCH cur_clause INTO l_temp;
3624 IF cur_clause%FOUND THEN
3625 CLOSE cur_clause;
3626 RETURN l_temp;
3627 END IF;
3628 CLOSE cur_clause;
3629
3630 OPEN cur_deliverable;
3631 FETCH cur_deliverable INTO l_temp;
3632 IF cur_deliverable%FOUND THEN
3633 CLOSE cur_deliverable;
3634 RETURN l_temp;
3635 END IF;
3636 CLOSE cur_deliverable;
3637
3638 RETURN 'N';
3639
3640 END has_amended_terms;
3641
3642 FUNCTION has_conflicting_locks ( p_doc_type IN VARCHAR2,
3643 p_doc_id IN NUMBER
3644 )
3645 RETURN VARCHAR2
3646 IS
3647 l_entity VARCHAR2(120);
3648 BEGIN
3649
3650 SELECT entity
3651 INTO l_entity
3652 FROM
3653 (SELECT 'SECTION' entity, orig_system_reference_id1 , amendment_operation_code, summary_amend_operation_code
3654 FROM okc_sections_b
3655 WHERE document_type= p_doc_type
3656 AND document_id= p_doc_id
3657 AND summary_amend_operation_code IN ('UPDATED','DELETED')
3658 UNION ALL
3659 SELECT 'CLAUSE' entity, orig_system_reference_id1 , amendment_operation_code, summary_amend_operation_code
3660 FROM okc_k_articles_b
3661 WHERE document_type= p_doc_type
3662 AND document_id = p_doc_id
3663 AND summary_amend_operation_code IN ('UPDATED','DELETED')
3664 UNION ALL
3665 SELECT 'DELIVERABLE' entity, orig_system_reference_id1 , amendment_operation, summary_amend_operation_code
3666 FROM okc_deliverables
3667 WHERE business_document_type= p_doc_type
3668 AND business_document_id = p_doc_id
3669 AND business_document_version = -99
3670 AND summary_amend_operation_code IN ('UPDATED','DELETED')
3671 ) src, okc_k_entity_locks LcK
3672 WHERE lck.entity_name = src. entity
3673 AND lck.entity_pk1 = To_Char(orig_system_reference_id1)
3674 AND ROWNUM=1;
3675
3676 RETURN 'Y';
3677
3678 EXCEPTION
3679 WHEN No_Data_Found THEN
3680 RETURN 'N';
3681 WHEN OTHERS THEN
3682 RAISE;
3683
3684 END has_conflicting_locks;
3685
3686 PROCEDURE merge_changes (
3687 p_api_version IN NUMBER,
3688 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3689 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3690 p_src_doc_type IN VARCHAR2, -- PAR document
3691 p_src_doc_id IN NUMBER, -- PAR document
3692 p_target_doc_type IN VARCHAR2, -- MOD document
3693 p_target_doc_id IN NUMBER, -- MOD document
3694 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3695 X_MSG_COUNT OUT NOCOPY NUMBER,
3696 X_MSG_DATA OUT NOCOPY VARCHAR2
3697 )
3698 IS
3699
3700 CURSOR cur_deliverables
3701 IS
3702 SELECT deliverable_id,
3703 amendment_operation,
3704 summary_amend_operation_code,
3705 orig_system_reference_code,
3706 orig_system_reference_id1,
3707 orig_system_reference_id2
3708 FROM okc_deliverables
3709 WHERE business_document_type = p_src_doc_type
3710 AND business_document_id = p_src_doc_id
3711 AND business_document_version = -99
3712 AND amendment_operation IS NOT NULL
3713 ;
3714
3715 CURSOR cur_get_tar_del (cp_deliverable_id NUMBER)
3716 IS
3717 SELECT deliverable_id
3718 FROM okc_deliverables
3719 WHERE business_document_type = p_target_doc_type
3720 AND business_document_id = p_target_doc_id
3721 AND business_document_version = -99
3722 AND orig_system_reference_id1 = cp_deliverable_id;
3723
3724 CURSOR cur_clauses
3725 IS
3726 SELECT id,
3727 amendment_operation_code,
3728 summary_amend_operation_code,
3729 orig_system_reference_code,
3730 orig_system_reference_id1,
3731 orig_system_reference_id2,
3732 scn_id
3733 FROM okc_k_articles_b
3734 WHERE document_type = p_src_doc_type
3735 AND document_id = p_src_doc_id
3736 AND amendment_operation_code IS NOT NULL;
3737
3738
3739
3740 CURSOR cur_get_tar_kartid (cp_kart_id NUMBER)
3741 IS
3742 SELECT id
3743 FROM okc_k_articles_b
3744 WHERE document_type = p_target_doc_type
3745 AND document_id = p_target_doc_id
3746 AND orig_system_reference_id1 = cp_kart_id;
3747
3748
3749 CURSOR cur_sections
3750 IS
3751 SELECT id,
3752 amendment_operation_code,
3753 summary_amend_operation_code,
3754 orig_system_reference_code,
3755 orig_system_reference_id1,
3756 orig_system_reference_id2,
3757 scn_id
3758 FROM okc_sections_b
3759 WHERE document_type = p_src_doc_type
3760 AND document_id = p_src_doc_id
3761 AND amendment_operation_code IS NOT NULL
3762 ORDER BY Nvl(scn_id,-99),id;
3763
3764
3765
3766 CURSOR cur_get_tar_scnid (cp_scn_id NUMBER)
3767 IS
3768 SELECT id
3769 FROM okc_sections_b
3770 WHERE document_type = p_target_doc_type
3771 AND document_id = p_target_doc_id
3772 AND orig_system_reference_id1 = cp_scn_id;
3773
3774
3775
3776 l_target_del_id NUMBER;
3777 l_target_kart_id NUMBER;
3778 l_target_scn_id NUMBER;
3779
3780 BEGIN
3781
3782 X_RETURN_STATUS := G_RET_STS_SUCCESS;
3783
3784 -- Deliverables
3785 FOR del_rec IN cur_deliverables
3786 LOOP
3787 -- Get the target deliverable id
3788 l_target_del_id := NULL;
3789 IF del_rec.orig_system_reference_code = 'COPY' THEN
3790 OPEN cur_get_tar_del(del_rec.orig_system_reference_id1);
3791 FETCH cur_get_tar_del INTO l_target_del_id;
3792 CLOSE cur_get_tar_del;
3793 END IF;
3794 -- copy src del to target deliverable
3795 -- delete the target deliverable
3796 refresh_deliverable(
3797 p_source_deliverable_id => del_rec.deliverable_id,
3798 p_target_document_type => p_target_doc_type,
3799 p_target_document_id => p_target_doc_id,
3800 p_target_deliverable_id => l_target_del_id,
3801 p_keep_orig_ref => 'Y',
3802 x_return_status => x_return_status,
3803 x_msg_count => x_msg_count,
3804 x_msg_data => x_msg_data
3805 );
3806 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3808 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3809 RAISE FND_API.G_EXC_ERROR ;
3810 END IF;
3811
3812 -- lock the deliverable
3813 IF del_rec.summary_amend_operation_code in ('UPDATED','DELETED')
3814 THEN
3815 lock_entity( P_API_VERSION => 1.0,
3816 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
3817 P_COMMIT => P_COMMIT,
3818 -----------------
3819 P_ENTITY_NAME => G_DLVBL_ENTITY,
3820 P_ENTITY_PK1 => del_rec.orig_system_reference_id1,
3821 P_LOCK_BY_ENTITY_ID => l_target_del_id,
3822 P_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
3823 P_LOCK_BY_DOCUMENT_ID => p_target_doc_id,
3824 ---------------------
3825 X_RETURN_STATUS => X_RETURN_STATUS,
3826 X_MSG_COUNT => X_MSG_COUNT,
3827 X_MSG_DATA => X_MSG_DATA
3828 );
3829 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3831 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3832 RAISE FND_API.G_EXC_ERROR ;
3833 END IF;
3834 END IF;
3835 END LOOP;
3836
3837 -- Articles
3838 FOR clause_rec IN cur_clauses
3839 LOOP
3840 -- Get the target deliverable id
3841 l_target_kart_id := NULL;
3842 IF clause_rec.orig_system_reference_code = 'COPY' THEN
3843 OPEN cur_get_tar_kartid(clause_rec.orig_system_reference_id1);
3844 FETCH cur_get_tar_kartid INTO l_target_kart_id;
3845 CLOSE cur_get_tar_kartid;
3846 END IF;
3847 -- copy src doc art to target deliverable
3848 -- delete the old target art
3849 refresh_clause(
3850 p_source_doc_clause_id => clause_rec.id,
3851 p_target_document_type => p_target_doc_type,
3852 p_target_document_id => p_target_doc_id,
3853 p_target_doc_clause_id => l_target_kart_id,
3854 p_keep_orig_ref => 'Y',
3855 p_scn_id => clause_rec.scn_id,
3856 x_return_status => x_return_status,
3857 x_msg_count => x_msg_count,
3858 x_msg_data => x_msg_data
3859 );
3860 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3862 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3863 RAISE FND_API.G_EXC_ERROR ;
3864 END IF;
3865
3866 -- lock the deliverable
3867 IF clause_rec.summary_amend_operation_code in ('UPDATED','DELETED')
3868 THEN
3869 lock_entity( P_API_VERSION => 1.0,
3870 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
3871 P_COMMIT => P_COMMIT,
3872 -----------------
3873 P_ENTITY_NAME => G_CLAUSE_ENTITY,
3874 P_ENTITY_PK1 => clause_rec.orig_system_reference_id1,
3875 P_LOCK_BY_ENTITY_ID => l_target_kart_id,
3876 P_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
3877 P_LOCK_BY_DOCUMENT_ID => p_target_doc_id,
3878 ---------------------
3879 X_RETURN_STATUS => X_RETURN_STATUS,
3880 X_MSG_COUNT => X_MSG_COUNT,
3881 X_MSG_DATA => X_MSG_DATA
3882
3883 );
3884 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3886 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3887 RAISE FND_API.G_EXC_ERROR ;
3888 END IF;
3889 END IF;
3890 END LOOP;
3891 -- Sections.
3892
3893 FOR section_rec IN cur_sections
3894 LOOP
3895 -- Get the target deliverable id
3896 l_target_scn_id := NULL;
3897 IF section_rec.orig_system_reference_code = 'COPY' THEN
3898 OPEN cur_get_tar_scnid(section_rec.orig_system_reference_id1);
3899 FETCH cur_get_tar_scnid INTO l_target_scn_id;
3900 CLOSE cur_get_tar_scnid;
3901 END IF;
3902 -- copy src del to target deliverable
3903 -- delete the target deliverable
3904 refresh_section(
3905 p_source_section_id => section_rec.id,
3906 p_target_document_type => p_target_doc_type,
3907 p_target_document_id => p_target_doc_id,
3908 p_target_doc_section_id => l_target_scn_id,
3909 p_keep_orig_ref => 'Y',
3910 p_scn_id => section_rec.scn_id,
3911 x_return_status => x_return_status,
3912 x_msg_count => x_msg_count,
3913 x_msg_data => x_msg_data
3914 );
3915 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3917 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3918 RAISE FND_API.G_EXC_ERROR ;
3919 END IF;
3920
3921 -- lock the deliverable
3922 IF section_rec.summary_amend_operation_code IN ('UPDATED','DELETED')
3923 THEN
3924 lock_entity( P_API_VERSION => 1.0,
3925 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
3926 P_COMMIT => P_COMMIT,
3927 -----------------
3928 P_ENTITY_NAME => G_SECTION_ENTITY,
3929 P_ENTITY_PK1 => section_rec.orig_system_reference_id1,
3930 P_LOCK_BY_ENTITY_ID => l_target_scn_id,
3931 P_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
3932 P_LOCK_BY_DOCUMENT_ID => p_target_doc_id,
3933 ---------------------
3934 X_RETURN_STATUS => X_RETURN_STATUS,
3935 X_MSG_COUNT => X_MSG_COUNT,
3936 X_MSG_DATA => X_MSG_DATA
3937
3938 );
3939 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3941 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3942 RAISE FND_API.G_EXC_ERROR ;
3943 END IF;
3944 END IF;
3945 END LOOP;
3946
3947 EXCEPTION
3948 WHEN OTHERS THEN
3949 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3950 THEN
3951 fnd_log.STRING
3952 (FND_LOG.LEVEL_STATEMENT,
3953 g_pkg_name,
3954 '9999: Leaving merge_changes because of exception'
3955 );
3956 END IF;
3957 RAISE;
3958 END merge_changes;
3959 END okc_k_entity_locks_grp;