[Home] [Help]
PACKAGE BODY: APPS.JTS_CONFIG_VERSION_PVT
Source
1 PACKAGE BODY JTS_CONFIG_VERSION_PVT as
2 /* $Header: jtsvcvrb.pls 115.11 2002/06/07 11:53:26 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_CONFIG_VERSION_PVT
7 -- Purpose : Configuration Versions.
8 -- History : 21-Feb-02 SHuh Created.
9 -- NOTE :
10 -- --------------------------------------------------------------------
11
12 C_STATUS_TYPE CONSTANT Varchar2(30) := 'JTS_STATUS';
13
14 C_NEW CONSTANT Varchar2(30) := 'NEW';
15 C_PROCESS CONSTANT Varchar2(30) := 'PROCESS';
16 C_COMPLETE CONSTANT Varchar2(30) := 'COMPLETE';
17 C_SUBMIT CONSTANT Varchar2(30) := 'SUBMIT';
18 C_NOSUBMIT CONSTANT Varchar2(30) := 'NOSUBMIT';
19 C_FAIL CONSTANT Varchar2(30) := 'FAIL';
20 C_CANCEL CONSTANT Varchar2(30) := 'CANCEL';
21 C_SUCCESS CONSTANT Varchar2(30) := 'SUCCESS';
22 C_ERRORS CONSTANT Varchar2(30) := 'ERRORS';
23 C_RUNNING CONSTANT Varchar2(30) := 'RUNNING';
24
25 -- Returns the next value for version_number in
26 -- jts_config_versions
27 FUNCTION GET_NEXT_VERSION_NUMBER(p_config_id IN NUMBER) return NUMBER IS
28 l_max_version NUMBER := 1;
29 l_exists NUMBER := 0;
30 BEGIN
31 SELECT count(*)
32 INTO l_exists
33 FROM jts_config_versions_b
34 WHERE configuration_id = p_config_id;
35
36 IF (l_exists > 0) THEN
37
38 SELECT max(version_number)
39 INTO l_max_version
40 FROM jts_config_versions_b
41 WHERE configuration_id = p_config_id;
42
43 return (l_max_version + 1);
44 ELSE
45 return 1;
46 END IF;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 return 1;
51 END GET_NEXT_VERSION_NUMBER;
52
53
54 --
55 -- Returns the next sequence in jts_config_versions for
56 -- version_id
57 FUNCTION GET_NEXT_VERSION_ID return NUMBER IS
58 l_version_id JTS_CONFIG_VERSIONS_B.version_ID%TYPE;
59 BEGIN
60 SELECT jts_config_versions_b_s.NEXTVAL
61 INTO l_version_id
62 FROM sys.dual;
63
64 return (l_version_id);
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 APP_EXCEPTION.RAISE_EXCEPTION;
69 END GET_NEXT_VERSION_ID;
70
71 --
72 -- Returns the versionin jts_config_versions for
73 -- version_name and configuration_id
74 FUNCTION GET_VERSION_ID(p_version_name IN VARCHAR2,
75 p_config_id IN NUMBER) return NUMBER IS
76 l_version_id JTS_CONFIG_VERSIONS_B.version_id%TYPE;
77 BEGIN
78
79 SELECT version_id
80 INTO l_version_id
81 FROM jts_config_versions_b
82 WHERE version_name = p_version_name
83 AND configuration_id = p_config_id;
84
85 return (l_version_id);
86
87 EXCEPTION
88 WHEN OTHERS THEN
89 return NULL;
90 END GET_VERSION_ID;
91
92
93 --
94 -- Checks for Unique Version Name per Configuration
95 --
96 FUNCTION CHECK_VERSION_NAME_UNIQUE(p_version_name IN VARCHAR2,
97 p_version_id IN NUMBER,
98 p_config_id IN NUMBER
99 ) RETURN BOOLEAN IS
100 l_count NUMBER := 0;
101 l_version_id JTS_CONFIG_VERSIONS_B.version_id%TYPE;
102 BEGIN
103 SELECT count(*)
104 INTO l_count
105 FROM jts_config_versions_vl
106 WHERE configuration_id = p_config_id
107 AND version_name = p_version_name;
108
109 IF (l_count = 0) THEN
110 return TRUE;
111 ELSIF l_count = 1 THEN
112 l_version_id := get_version_id(p_version_name, p_config_id);
113
114 IF (p_version_id IS NOT NULL AND p_version_id = l_version_id) THEN
115 return TRUE;
116 END IF;
117 return FALSE;
118 ELSE
119 return FALSE;
120 END IF;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 END CHECK_VERSION_NAME_UNIQUE;
126
127 --
128 -- Checks for Unique Version Name per Configuration
129 --
130 PROCEDURE VALIDATE_ROW(p_api_version IN NUMBER,
131 p_version_rec IN Config_Version_Rec_Type,
132 x_return_status OUT VARCHAR2
133 ) IS
134 l_api_version CONSTANT NUMBER := 1.0;
135 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_ROW';
136 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
137 BEGIN
138
139 x_return_status := FND_API.G_RET_STS_SUCCESS;
140 IF NOT fnd_api.compatible_api_call ( l_api_version,
141 p_api_version,
142 l_api_name,
143 G_PKG_NAME
144 )
145 THEN
146 RAISE fnd_api.g_exc_unexpected_error;
147 END IF;
148
149 IF (NOT CHECK_VERSION_NAME_UNIQUE(p_version_rec.version_name,
150 p_version_rec.version_id,
151 p_version_rec.configuration_id)) THEN
152 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
153 FND_MESSAGE.set_name('JTS', 'JTS_VERSION_NAME_NOT_UNIQUE');
154 FND_MSG_PUB.add;
155 END IF;
156 x_return_status := fnd_api.g_ret_sts_error;
157 END IF;
158
159 EXCEPTION
160 WHEN fnd_api.g_exc_unexpected_error THEN
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162 WHEN OTHERS THEN
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
165 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
166 END IF;
167 END VALIDATE_ROW;
168
169 -- Inserts a row into jts_config_versions
170 -- Note: Since there is no UI for Create Version, attribute1 - 15 are set to null in insert
171 -- at this time
172 PROCEDURE INSERT_ROW(p_configuration_id IN NUMBER,
173 p_init_version IN VARCHAR2,
174 x_version_id OUT NUMBER) IS
175 l_queue_name JTS_CONFIG_VERSIONS_B.queue_name%TYPE;
176 l_version_number NUMBER := 1;
177 l_version_name JTS_CONFIG_VERSIONS_B.version_name%TYPE;
178 l_description JTS_CONFIG_VERSIONS_TL.description%TYPE;
179 l_config_name JTS_CONFIGURATIONS_B.config_name%TYPE;
180 BEGIN
181 x_version_id := GET_NEXT_VERSION_ID;
182 l_queue_name := C_QUEUE_PREFIX || x_version_id;
183 --nodpfxml l_version_number := GET_NEXT_VERSION_NUMBER(p_configuration_id);
184
185 l_config_name := JTS_CONFIGURATION_PVT.get_config_name(p_configuration_id);
186
187 --nodpfxml
188 --IF (FND_API.to_boolean(p_init_version)) THEN --initial version
189 -- FND_MESSAGE.set_name('JTS', 'JTS_INITIAL_VERSION');
190 -- l_version_name := FND_MESSAGE.get;
191 --ELSE
192 FND_MESSAGE.set_name('JTS', 'JTS_VERSION_NAME');
193 --FND_MESSAGE.set_token('NUMBER', l_version_number || '.0');
194 FND_MESSAGE.set_token('NUMBER', l_config_name);
195 l_version_name := FND_MESSAGE.get;
196 --END IF;
197
198 FND_MESSAGE.set_name('JTS', 'JTS_VERSION_DESC');
199 --nodpfxml FND_MESSAGE.set_token('NAME', l_version_name );
200 --nodpfxml FND_MESSAGE.set_token('CONFIG_NAME', l_config_name);
201 l_description := substrb(FND_MESSAGE.get, 1, 240);
202
203
204 insert into JTS_CONFIG_VERSIONS_B (
205 VERSION_ID,
206 VERSION_NAME,
207 CONFIGURATION_ID,
208 VERSION_NUMBER,
209 QUEUE_NAME,
210 OBJECT_VERSION_NUMBER,
211 ATTRIBUTE_CATEGORY,
212 ATTRIBUTE1,
213 ATTRIBUTE2,
214 ATTRIBUTE3,
215 ATTRIBUTE4,
216 ATTRIBUTE5,
217 ATTRIBUTE6,
218 ATTRIBUTE7,
219 ATTRIBUTE8,
220 ATTRIBUTE9,
221 ATTRIBUTE10,
222 ATTRIBUTE11,
223 ATTRIBUTE12,
224 ATTRIBUTE13,
225 ATTRIBUTE14,
226 ATTRIBUTE15,
227 CREATION_DATE,
228 CREATED_BY,
229 LAST_UPDATE_DATE,
230 LAST_UPDATED_BY,
231 LAST_UPDATE_LOGIN
232 ) values (
233 X_VERSION_ID,
234 l_version_name,
235 P_CONFIGURATION_ID,
236 L_VERSION_NUMBER,
237 L_QUEUE_NAME,
238 1,
239 NULL,
240 NULL,
241 NULL,
242 NULL,
243 NULL,
244 NULL,
245 NULL,
246 NULL,
247 NULL,
248 NULL,
249 NULL,
250 NULL,
251 NULL,
252 NULL,
253 NULL,
254 NULL,
255 sysdate,
256 FND_GLOBAL.user_id,
257 sysdate,
258 FND_GLOBAL.user_id,
259 FND_GLOBAL.user_id
260 );
261
262 insert into JTS_CONFIG_VERSIONS_TL (
263 VERSION_ID,
264 CONFIGURATION_ID,
265 DESCRIPTION,
266 CREATION_DATE,
267 CREATED_BY,
268 LAST_UPDATE_DATE,
269 LAST_UPDATED_BY,
270 LAST_UPDATE_LOGIN,
271 LANGUAGE,
272 SOURCE_LANG
273 ) select
274 X_VERSION_ID,
275 P_CONFIGURATION_ID,
276 l_description,
277 sysdate,
278 FND_GLOBAL.user_id,
279 sysdate,
280 FND_GLOBAL.user_id,
281 FND_GLOBAL.user_id,
282 L.LANGUAGE_CODE,
283 userenv('LANG')
284 from FND_LANGUAGES L
285 where L.INSTALLED_FLAG in ('I', 'B')
286 and not exists
287 (select NULL
288 from JTS_CONFIG_VERSIONS_TL T
289 where T.VERSION_ID = X_VERSION_ID
290 and T.LANGUAGE = L.LANGUAGE_CODE);
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 APP_EXCEPTION.RAISE_EXCEPTION;
295 END INSERT_ROW;
296
297
298 -- Deletes rows from jts_config_versions based on configuration_id
299 PROCEDURE DELETE_ROWS(p_config_id IN NUMBER
300 ) IS
301 BEGIN
302 DELETE FROM jts_config_versions_b
303 WHERE configuration_id = p_config_id;
304
305 DELETE FROM jts_config_versions_tl
306 WHERE configuration_id = p_config_id;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 APP_EXCEPTION.RAISE_EXCEPTION;
311 END DELETE_ROWS;
312
313
314 -----------------------------------------------------------------
315 -- Creates a version, version flows for the setup summary data,
316 -- and version status with "NEW" as the value
317 -- Values passed in:
318 -- version_name
319 -- description
320 -- configuration_id
321 -----------------------------------------------------------------
322 PROCEDURE CREATE_VERSION(p_api_version IN Number,
323 P_commit IN Varchar2 DEFAULT FND_API.G_FALSE,
324 p_configuration_id IN NUMBER,
325 p_init_version IN VARCHAR2 DEFAULT FND_API.G_FALSE,
326 x_version_id OUT NUMBER,
327 x_return_status OUT VARCHAR2,
328 x_msg_count OUT NUMBER,
329 x_msg_data OUT VARCHAR2) IS
330
331 l_api_version CONSTANT NUMBER := 1.0;
332 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_VERSION';
333 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
334 l_flows JTS_SETUP_FLOW_PVT.Setup_Flow_Tbl_Type;
335 l_flow_id JTS_CONFIGURATIONS_B.flow_id%TYPE;
336 BEGIN
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338
339 -- Standard Start of API savepoint
340 SAVEPOINT create_version;
341
342 fnd_msg_pub.initialize;
343
344 IF NOT fnd_api.compatible_api_call ( l_api_version,
345 p_api_version,
346 l_api_name,
347 G_PKG_NAME
348 )
349 THEN
350 RAISE fnd_api.g_exc_unexpected_error;
351 END IF;
352
353
354 If (x_return_status = fnd_api.g_ret_sts_success) THEN
355 INSERT_ROW(p_configuration_id,
356 p_init_version,
357 x_version_id);
358
359 JTS_CONFIGURATION_PVT.get_flow_id(p_configuration_id,
360 l_flow_id);
361
362 JTS_SETUP_FLOW_PVT.GET_FLOW_HIEARCHY(
363 p_api_version => p_api_version,
364 p_flow_id => l_flow_id,
365 x_flow_tbl => l_flows
366 );
367
368 JTS_CONFIG_VERSION_FLOW_PVT.CREATE_VERSION_FLOWS(
369 p_api_version => p_api_version,
370 p_version_id => x_version_id,
371 p_flow_hiearchy => l_flows);
372
373 JTS_CONFIG_VER_STATUS_PVT.CREATE_VERSION_STATUS(
374 p_api_version => p_api_version,
375 p_commit => FND_API.G_FALSE,
376 p_version_id => x_version_id,
377 p_status => JTS_CONFIG_VER_STATUS_PVT.C_INIT_VERSION_STATUS);
378
379 IF (FND_API.to_boolean(p_commit)) THEN
380 COMMIT;
381 END IF;
382
383 END IF;
384
385 FND_MSG_PUB.Count_And_Get (
386 p_encoded => FND_API.G_FALSE,
387 p_count => x_msg_count,
388 p_data => x_msg_data);
389 EXCEPTION
390 WHEN OTHERS THEN
391 ROLLBACK TO create_version;
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
394 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
395 END IF;
396 -- Standard call to get message count and if count=1, get the message
397 FND_MSG_PUB.Count_And_Get (
398 p_encoded => FND_API.G_FALSE,
399 p_count => x_msg_count,
400 p_data => x_msg_data
401 );
402 END CREATE_VERSION;
403
404 -- Updates version_status_code, last_update_date, last_updated_by
405 PROCEDURE UPDATE_VERSION_STAT(p_api_version IN NUMBER,
406 p_version_id IN NUMBER,
407 p_status IN VARCHAR2
408 ) IS
409 l_api_version CONSTANT NUMBER := 1.0;
410 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_VERSION_STAT';
411 BEGIN
412 IF NOT fnd_api.compatible_api_call ( l_api_version,
413 p_api_version,
414 l_api_name,
415 G_PKG_NAME
416 )
417 THEN
418 RAISE fnd_api.g_exc_unexpected_error;
419 END IF;
420
421 UPDATE jts_config_versions_b
422 SET version_status_code = p_status,
423 last_update_date = sysdate,
424 last_updated_by = FND_GLOBAL.user_id
425 WHERE version_id = p_version_id;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 APP_EXCEPTION.RAISE_EXCEPTION;
430 END UPDATE_VERSION_STAT;
431
432 -- Updates version_status_code, last_update_date, last_updated_by
433 PROCEDURE UPDATE_REPLAY_DATA(p_api_version IN NUMBER,
434 p_version_id IN NUMBER,
435 p_status IN VARCHAR2
436 ) IS
437 l_api_version CONSTANT NUMBER := 1.0;
438 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_REPLAY_DATA';
439 BEGIN
440 IF NOT fnd_api.compatible_api_call ( l_api_version,
441 p_api_version,
442 l_api_name,
443 G_PKG_NAME
444 )
445 THEN
446 RAISE fnd_api.g_exc_unexpected_error;
447 END IF;
448
449 UPDATE jts_config_versions_b
450 SET replay_status_code = p_status,
451 replayed_on = sysdate,
452 replayed_by = FND_GLOBAL.user_id,
453 last_update_date = sysdate,
454 last_updated_by = FND_GLOBAL.user_id
455 WHERE version_id = p_version_id;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 APP_EXCEPTION.RAISE_EXCEPTION;
460 END UPDATE_REPLAY_DATA;
461
462 -- Updates last_update_date and last_updated_by
463 PROCEDURE UPDATE_LAST_MODIFIED(p_api_version IN NUMBER,
464 p_version_id IN NUMBER
465 ) IS
466 l_api_version CONSTANT NUMBER := 1.0;
467 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_LAST_MODIFIED';
468 BEGIN
469 IF NOT fnd_api.compatible_api_call ( l_api_version,
470 p_api_version,
471 l_api_name,
472 G_PKG_NAME
473 )
474 THEN
475 RAISE fnd_api.g_exc_unexpected_error;
476 END IF;
477
478 UPDATE jts_config_versions_b
479 SET last_update_date = sysdate,
480 last_updated_by = FND_GLOBAL.user_id
481 WHERE version_id = p_version_id;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END UPDATE_LAST_MODIFIED;
487
488 -- Updates version name and description.
489 -- May insert into version_statuses table
490 PROCEDURE UPDATE_NAME_DESC(p_api_version IN NUMBER,
491 p_version_id IN NUMBER,
492 p_config_id IN NUMBER,
493 p_version_name IN VARCHAR2,
494 p_version_desc IN VARCHAR2,
495 x_return_status OUT VARCHAR2,
496 x_msg_count OUT NUMBER,
497 x_msg_data OUT VARCHAR2
498 ) IS
499 l_api_version CONSTANT NUMBER := 1.0;
500 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_NAME_DESC';
501 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
502 l_version_rec Config_Version_Rec_Type;
503 BEGIN
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- Standard Start of API savepoint
507 SAVEPOINT UPDATE_NAME_DESC;
508
509 fnd_msg_pub.initialize;
510
511 IF NOT fnd_api.compatible_api_call ( l_api_version,
512 p_api_version,
513 l_api_name,
514 G_PKG_NAME
515 )
516 THEN
517 RAISE fnd_api.g_exc_unexpected_error;
518 END IF;
519
520 l_version_rec.version_id := p_version_id;
521 l_version_rec.configuration_id := p_config_id;
522 l_version_rec.version_name := p_version_name;
523 l_version_rec.description := p_version_desc;
524
525 VALIDATE_ROW(p_api_version => p_api_version,
526 p_version_rec => l_version_rec,
527 x_return_status => x_return_status);
528
529 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
530 UPDATE jts_config_versions_b
531 SET version_name = p_version_name,
532 last_update_date = sysdate,
533 last_updated_by = FND_GLOBAL.user_id,
534 last_update_login = FND_GLOBAL.user_id
535 WHERE version_id = p_version_id;
536
537 --take care of translation
538 UPDATE jts_config_versions_tl
539 SET description = p_version_desc,
540 last_update_date = sysdate,
541 last_updated_by = FND_GLOBAL.user_id,
542 last_update_login = FND_GLOBAL.user_id,
543 source_lang = USERENV('LANG')
544 WHERE version_id = p_version_id
545 AND USERENV('LANG') IN (language, source_lang);
546 COMMIT;
547 END IF;
548
549 FND_MSG_PUB.Count_And_Get (
550 p_encoded => FND_API.G_FALSE,
551 p_count => x_msg_count,
552 p_data => x_msg_data);
553 EXCEPTION
554 WHEN OTHERS THEN
555 ROLLBACK TO UPDATE_NAME_DESC;
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
558 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
559 END IF;
560 -- Standard call to get message count and if count=1, get the message
561 FND_MSG_PUB.Count_And_Get (
562 p_encoded => FND_API.G_FALSE,
563 p_count => x_msg_count,
564 p_data => x_msg_data
565 );
566 END UPDATE_NAME_DESC;
567
568 -- Deletes a version and its corresponding version_statuses and
569 -- version_flows
570 PROCEDURE DELETE_VERSION(p_api_version IN Number,
571 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
572 p_version_id IN NUMBER
573 ) IS
574 l_api_version CONSTANT NUMBER := 1.0;
575 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_VERSION';
576 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
577 BEGIN
578 -- Standard Start of API savepoint
579 SAVEPOINT DELETE_VERSION;
580
581 IF NOT fnd_api.compatible_api_call ( l_api_version,
582 p_api_version,
583 l_api_name,
584 G_PKG_NAME
585 )
586 THEN
587 RAISE fnd_api.g_exc_unexpected_error;
588 END IF;
589
590 JTS_CONFIG_VER_STATUS_PVT.DELETE_VERSION_STATUSES(p_api_version, p_version_id);
591 JTS_CONFIG_VERSION_FLOW_PVT.DELETE_VERSION_FLOWS(p_api_version, p_version_id);
592 JTS_CONFIG_VERSIONS_PKG.DELETE_ROW(p_version_id);
593 IF (FND_API.to_boolean(p_commit)) THEN
594 COMMIT;
595 END IF;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF (FND_API.to_boolean(p_commit)) THEN
600 ROLLBACK TO DELETE_VERSION;
601 ELSE
602 APP_EXCEPTION.RAISE_EXCEPTION;
603 END IF;
604 END DELETE_VERSION;
605
606 -- Deletes versions and their corresponding version_statuses and
607 -- version_flows given a table of version ids
608 PROCEDURE DELETE_SOME_VERSIONS(p_api_version IN Number,
609 p_version_tbl IN Version_Id_Tbl_Type
610 ) IS
611 l_api_version CONSTANT NUMBER := 1.0;
612 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SOME_VERSIONS';
613 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
614 i NUMBER := 1;
615 BEGIN
616
617 -- Standard Start of API savepoint
618 SAVEPOINT DELETE_SOME_VERSIONS;
619
620 IF NOT fnd_api.compatible_api_call ( l_api_version,
621 p_api_version,
622 l_api_name,
623 G_PKG_NAME
624 ) THEN
625 RAISE fnd_api.g_exc_unexpected_error;
626 END IF;
627
628 FOR I IN 1..p_version_tbl.count LOOP --loop through p_version_tbl
629 DELETE_VERSION(p_api_version => p_api_version,
630 p_commit => FND_API.G_FALSE,
631 p_version_id => p_version_tbl(i));
632 END LOOP;
633 COMMIT;
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 ROLLBACK TO DELETE_SOME_VERSIONS;
638 END DELETE_SOME_VERSIONS;
639
640 -- Deletes all versions of a configuration and their corresponding -- version_statuses and version_flows
641 -- Commit is done in Configurations Pkg
642 PROCEDURE DELETE_VERSIONS(p_api_version IN NUMBER,
643 p_config_id IN NUMBER
644 ) IS
645 l_api_version CONSTANT NUMBER := 1.0;
646 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_VERSIONS';
647 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
648 BEGIN
649
650 IF NOT fnd_api.compatible_api_call ( l_api_version,
651 p_api_version,
652 l_api_name,
653 G_PKG_NAME
654 )
655 THEN
656 RAISE fnd_api.g_exc_unexpected_error;
657 END IF;
658
659 JTS_CONFIG_VER_STATUS_PVT.DELETE_CONFIG_VER_STATUSES(p_api_version, p_config_id);
660 JTS_CONFIG_VERSION_FLOW_PVT.DELETE_CONFIG_VERSION_FLOWS(p_api_version, p_config_id);
661 DELETE_ROWS(p_config_id);
662
663 EXCEPTION
664 WHEN OTHERS THEN
665 APP_EXCEPTION.RAISE_EXCEPTION;
666 END DELETE_VERSIONS;
667
668
669 -- Gets version data based on version_id
670 PROCEDURE GET_VERSION(p_api_version IN NUMBER,
671 p_version_id IN NUMBER,
672 x_version_rec OUT NOCOPY Config_Version_Rec_Type,
673 x_return_status OUT VARCHAR2,
674 x_msg_count OUT NUMBER,
675 x_msg_data OUT VARCHAR2) IS
676 l_api_version CONSTANT NUMBER := 1.0;
677 l_api_name CONSTANT VARCHAR2 (30) := 'GET_VERSION';
678 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
679 l_config_rec JTS_CONFIGURATION_PVT.Config_Rec_Type;
680 l_debug_info VARCHAR2(2000);
681 BEGIN
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683
684 fnd_msg_pub.initialize;
685
686 IF NOT fnd_api.compatible_api_call ( l_api_version,
687 p_api_version,
688 l_api_name,
689 G_PKG_NAME
690 )
691 THEN
692 RAISE fnd_api.g_exc_unexpected_error;
693 END IF;
694
695 l_debug_info := 'Version select';
696 SELECT configuration_id, version_id, version_name, version_number, v.description, queue_name,
697 v.attribute_category, v.attribute1, v.attribute2, v.attribute3, v.attribute4, v.attribute5,
698 v.attribute6, v.attribute7, v.attribute8, v.attribute9, v.attribute10, v.attribute11,
699 v.attribute12, v.attribute13, v.attribute14, v.attribute15,
700 v.creation_date, v.created_by, v.last_update_date, v.last_updated_by, v.last_update_login,
701 u1.user_name, u2.user_name, replay_status_code, version_status_code,
702 rep.meaning, ver.meaning, replayed_on, u3.user_name
703 INTO x_version_rec.configuration_id,
704 x_version_rec.version_id,
705 x_version_rec.version_name,
706 x_version_rec.version_number,
707 x_version_rec.description,
708 x_version_rec.queue_name,
709 x_version_rec.attribute_category,
710 x_version_rec.attribute1,
711 x_version_rec.attribute2,
712 x_version_rec.attribute3,
713 x_version_rec.attribute4,
714 x_version_rec.attribute5,
715 x_version_rec.attribute6,
716 x_version_rec.attribute7,
717 x_version_rec.attribute8,
718 x_version_rec.attribute9,
719 x_version_rec.attribute10,
720 x_version_rec.attribute11,
721 x_version_rec.attribute12,
722 x_version_rec.attribute13,
723 x_version_rec.attribute14,
724 x_version_rec.attribute15,
725 x_version_rec.creation_date,
726 x_version_rec.created_by,
727 x_version_rec.last_update_date,
728 x_version_rec.last_updated_by,
729 x_version_rec.last_update_login,
730 x_version_rec.created_by_name,
731 x_version_rec.last_updated_by_name,
732 x_version_rec.replay_status_code,
733 x_version_rec.version_status_code,
734 x_version_rec.replay_status,
735 x_version_rec.version_status,
736 x_version_rec.replayed_date,
737 x_version_rec.replayed_by_name
738 FROM jts_config_versions_vl v,
739 fnd_lookup_values rep,
740 fnd_lookup_values ver,
741 fnd_user u1,
742 fnd_user u2,
743 fnd_user u3
744 WHERE version_id = p_version_id
745 AND rep.lookup_type (+) = C_STATUS_TYPE
746 AND rep.lookup_code (+) = v.replay_status_code
747 AND ver.lookup_type = C_STATUS_TYPE
748 AND ver.lookup_code = nvl(v.version_status_code, C_NEW)
749 AND u1.user_id = v.created_by
750 AND u2.user_id = v.last_updated_by
751 AND u3.user_id (+) = v.replayed_by;
752
753 l_debug_info := 'Configuration';
754 -- Get Configuration Data for Version
755 JTS_CONFIGURATION_PVT.GET_CONFIGURATION(
756 p_api_version => p_api_version,
757 p_init_msg_list => FND_API.G_FALSE,
758 p_config_id => x_version_rec.configuration_id,
759 x_configuration_rec => l_config_rec,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data
763 );
764
765 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
766 raise fnd_api.g_exc_unexpected_error;
767 END IF;
768
769 l_debug_info := 'Set Record';
770 -- Store Configuration Data for Version in the version record
771 x_version_rec.config_name := l_config_rec.config_name;
772 x_version_rec.config_desc := l_config_rec.description;
773 x_version_rec.config_flow_id := l_config_rec.flow_id;
774 x_version_rec.config_flow_name := l_config_rec.flow_name;
775 x_version_rec.config_flow_type := l_config_rec.flow_type;
776 x_version_rec.config_record_mode := l_config_rec.record_mode;
777 x_version_rec.config_disp_record_mode := l_config_rec.displayed_record_mode;
778
779 l_debug_info := 'Percent Completed';
780 x_version_rec.percent_completed := JTS_CONFIG_VERSION_FLOW_PVT.GET_PERCENT_COMPLETE(p_api_version,
781 x_version_rec.version_id);
782
783 FND_MSG_PUB.Count_And_Get (
784 p_encoded => FND_API.G_FALSE,
785 p_count => x_msg_count,
786 p_data => x_msg_data);
787 EXCEPTION
788 WHEN OTHERS THEN
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
791 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
792 END IF;
793 -- Standard call to get message count and if count=1, get the message
794 FND_MSG_PUB.Count_And_Get (
795 p_encoded => FND_API.G_FALSE,
796 p_count => x_msg_count,
797 p_data => x_msg_data
798 );
799 END GET_VERSION;
800
801
802 -- Retrieves all versions under a configuration with a certain order by clause
803 -- Uses Dynamic SQL
804 PROCEDURE GET_VERSIONS(
805 p_api_version IN NUMBER,
806 p_config_id IN NUMBER,
807 p_order_by IN VARCHAR2,
808 p_how_to_order IN VARCHAR2,
809 x_version_tbl OUT NOCOPY Config_Version_Tbl_Type,
810 x_return_status OUT VARCHAR2,
811 x_msg_count OUT NUMBER,
812 x_msg_data OUT VARCHAR2
813 ) IS
814 l_api_version CONSTANT NUMBER := 1.0;
815 l_api_name CONSTANT VARCHAR2 (30) := 'GET_VERSIONS';
816 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
817
818 TYPE Version_Cur_Typ IS REF CURSOR;
819 version_csr Version_Cur_Typ;
820 i NUMBER := 1;
821 sqlStmt VARCHAR2(4000);
822 l_config_rec JTS_CONFIGURATION_PVT.Config_Rec_Type;
823 l_how_to_order VARCHAR2(30);
824 l_order_by VARCHAR2(30) := upper(p_order_by);
825 BEGIN
826
827 x_return_status := fnd_api.G_RET_STS_SUCCESS;
828
829 fnd_msg_pub.initialize;
830
831 IF NOT fnd_api.compatible_api_call ( l_api_version,
832 p_api_version,
833 l_api_name,
834 G_PKG_NAME
835 )
836 THEN
837 RAISE fnd_api.g_exc_unexpected_error;
838 END IF;
839
840
841 -- Get Configuration Data for Version
842 JTS_CONFIGURATION_PVT.GET_CONFIGURATION(
843 p_api_version => p_api_version,
844 p_init_msg_list => FND_API.G_FALSE,
845 p_config_id => p_config_id,
846 x_configuration_rec => l_config_rec,
847 x_return_status => x_return_status,
848 x_msg_count => x_msg_count,
849 x_msg_data => x_msg_data
850 );
851
852 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
853 raise fnd_api.g_exc_unexpected_error;
854 END IF;
855
856 l_how_to_order := substrb(upper(p_how_to_order), 1, 30);
857 IF (l_how_to_order <> 'ASC' AND l_how_to_order <> 'DESC') THEN
858 l_how_to_order := 'ASC';
859 END IF;
860
861 IF (l_order_by = 'DESCRIPTION') THEN
862 l_order_by := 'v.description';
863 ELSIF (l_order_by = 'CREATION_DATE') THEN
864 l_order_by := 'v.creation_date';
865 ELSIF (l_order_by = 'LAST_UPDATE_DATE') THEN
866 l_order_by := 'v.last_update_date';
867 ELSIF (l_order_by = 'CREATED_BY') THEN
868 l_order_by := 'u1.user_name';
869 ELSIF (l_order_by = 'LAST_UPDATED_BY') THEN
870 l_order_by := 'u2.user_name';
871 ELSIF (l_order_by = 'REPLAY_STATUS') THEN
872 l_order_by := 'rs.replay_status_meaning';
873 ELSIF (l_order_by = 'VERSION_STATUS') THEN
874 l_order_by := 'vers.version_status_meaning';
875 ELSIF (l_order_by = 'REPLAYED_BY') THEN
876 l_order_by := 'rs.replayed_by';
877 ELSIF (l_order_by = 'REPLAYED_ON') THEN
878 l_order_by := 'rs.replayed_on';
879 END IF;
880
881 sqlStmt := 'SELECT configuration_id, version_id, version_name, version_number, v.description, queue_name, '
882 || ' v.attribute_category, v.attribute1, v.attribute2, v.attribute3, v.attribute4, v.attribute5, '
883 || ' v.attribute6, v.attribute7, v.attribute8, v.attribute9, v.attribute10, v.attribute11, '
884 || ' v.attribute12, v.attribute13, v.attribute14, v.attribute15, '
885 || ' v.creation_date, v.created_by, v.last_update_date, v.last_updated_by, v.last_update_login, '
886 || ' u1.user_name, u2.user_name, '
887 || ' replay_status_code, version_status_code, rep.meaning, ver.meaning, '
888 || ' replayed_on, u3.user_name '
889 || 'FROM jts_config_versions_vl v, '
890 || ' fnd_lookup_values rep, '
891 || ' fnd_lookup_values ver, '
892 || ' fnd_user u1, '
893 || ' fnd_user u2, '
894 || ' fnd_user u3 '
895 || 'WHERE configuration_id = ' || p_config_id ||
896 ' AND rep.lookup_type (+) = ''' || C_STATUS_TYPE ||
897 ''' AND rep.lookup_code (+)= v.replay_status_code ' ||
898 ' AND ver.lookup_type = ''' || C_STATUS_TYPE ||
899 ''' AND ver.lookup_code = nvl(v.version_status_code, ''' || C_NEW || ''') ' ||
900 ' AND u1.user_id = v.created_by ' ||
901 ' AND u2.user_id = v.last_updated_by ' ||
902 ' AND u3.user_id (+) = v.replayed_by ' ||
903 ' ORDER BY ' || l_order_by || ' ' || l_how_to_order;
904 i := 1;
905 OPEN version_csr FOR sqlStmt;
906 LOOP
907 FETCH version_csr INTO
908 x_version_tbl(i).configuration_id,
909 x_version_tbl(i).version_id,
910 x_version_tbl(i).version_name,
911 x_version_tbl(i).version_number,
912 x_version_tbl(i).description,
913 x_version_tbl(i).queue_name,
914 x_version_tbl(i).attribute_category,
915 x_version_tbl(i).attribute1,
916 x_version_tbl(i).attribute2,
917 x_version_tbl(i).attribute3,
918 x_version_tbl(i).attribute4,
919 x_version_tbl(i).attribute5,
920 x_version_tbl(i).attribute6,
921 x_version_tbl(i).attribute7,
922 x_version_tbl(i).attribute8,
923 x_version_tbl(i).attribute9,
924 x_version_tbl(i).attribute10,
925 x_version_tbl(i).attribute11,
926 x_version_tbl(i).attribute12,
927 x_version_tbl(i).attribute13,
928 x_version_tbl(i).attribute14,
929 x_version_tbl(i).attribute15,
930 x_version_tbl(i).creation_date,
931 x_version_tbl(i).created_by,
932 x_version_tbl(i).last_update_date,
933 x_version_tbl(i).last_updated_by,
934 x_version_tbl(i).last_update_login,
935 x_version_tbl(i).created_by_name,
936 x_version_tbl(i).last_updated_by_name,
937 x_version_tbl(i).replay_status_code,
938 x_version_tbl(i).version_status_code,
939 x_version_tbl(i).replay_status,
940 x_version_tbl(i).version_status,
941 x_version_tbl(i).replayed_date,
942 x_version_tbl(i).replayed_by_name;
943 EXIT WHEN version_csr%NOTFOUND;
944
945 -- Store Configuration Data for Version in the version record
946 x_version_tbl(i).config_name := l_config_rec.config_name;
947 x_version_tbl(i).config_desc := l_config_rec.description;
948 x_version_tbl(i).config_flow_id := l_config_rec.flow_id;
949 x_version_tbl(i).config_flow_name := l_config_rec.flow_name;
950 x_version_tbl(i).config_flow_type := l_config_rec.flow_type;
951 x_version_tbl(i).config_record_mode := l_config_rec.record_mode;
952 x_version_tbl(i).config_disp_record_mode := l_config_rec.displayed_record_mode;
953
954 x_version_tbl(i).percent_completed := JTS_CONFIG_VERSION_FLOW_PVT.GET_PERCENT_COMPLETE(
955 p_api_version,
956 x_version_tbl(i).version_id);
957
958 i := i + 1;
959 END LOOP;
960 CLOSE version_csr;
961
962 FND_MSG_PUB.Count_And_Get (
963 p_encoded => FND_API.G_FALSE,
964 p_count => x_msg_count,
965 p_data => x_msg_data
966 );
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
971 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
972 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
973 END IF;
974 -- Standard call to get message count and if count=1, get the message
975 FND_MSG_PUB.Count_And_Get (
976 p_encoded => FND_API.G_FALSE,
977 p_count => x_msg_count,
978 p_data => x_msg_data
979 );
980 END GET_VERSIONS;
981
982 END JTS_CONFIG_VERSION_PVT;