[Home] [Help]
PACKAGE BODY: APPS.JTF_PREFAB_CACHE_PUB
Source
1 Package Body JTF_PREFAB_CACHE_PUB AS
2 /* $Header: jtfprefabcab.pls 120.3 2006/09/15 12:23:31 amaddula ship $ */
3
4 -- global variables --
5 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PREFAB_CACHE_PUB';
6 G_FILE_NAME CONSTANT VARCHAR2(16):='jtfprefabcab.pls';
7
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10
11 PROCEDURE INSERT_HOST_APP(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15
16 p_host_app_id OUT NOCOPY jtf_prefab_host_apps.host_app_id%TYPE,
17 p_wsh_po_id IN jtf_prefab_host_apps.wsh_po_id%TYPE,
18 p_application_id IN jtf_prefab_host_apps.application_id%TYPE,
19 p_cache_policy IN jtf_prefab_host_apps.cache_policy%TYPE,
20 p_cache_clear_flag IN jtf_prefab_host_apps.cache_clear_flag%TYPE,
21 p_cache_reload_flag IN jtf_prefab_host_apps.cache_reload_flag%TYPE,
22 p_cache_filter_policy IN jtf_prefab_host_apps.cache_filter_policy%TYPE,
23
24 p_object_version_number OUT NOCOPY jtf_prefab_host_apps.object_version_number%TYPE,
25
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2
29 ) AS
30 -- local variables --
31 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_HOST_APP';
32 l_api_version NUMBER := p_api_version;
33
34 CURSOR sequence_cursor IS
35 SELECT jtf_prefab_host_apps_s.NEXTVAL from dual;
36 CURSOR cache_comps_cursor IS
37 SELECT ca_comp_id
38 FROM jtf_prefab_ca_comps_vl
39 WHERE application_id = p_application_id and cache_generic_flag='f';
40 CURSOR cache_comps_cursor_gen IS
41 SELECT ca_comp_id
42 FROM jtf_prefab_ca_comps_vl
43 WHERE cache_generic_flag='t';
44 CURSOR cache_filters_cursor IS
45 SELECT ca_filter_id
46 FROM jtf_prefab_ca_filters_vl
47 WHERE application_id = p_application_id;
48 l_ca_comp_id NUMBER;
49 l_ha_comp_id NUMBER;
50 l_ca_filter_id NUMBER;
51 l_ha_filter_id NUMBER;
52 BEGIN
53 -- Standard Start of API savepoint
54 SAVEPOINT INSERT_HOST_APP;
55
56 -- Standard call to check for call compatibility.
57 IF NOT FND_API.Compatible_API_Call (
58 l_api_version,
59 p_api_version,
60 l_api_name,
61 G_PKG_NAME)
62 THEN
63 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64 END IF;
65
66 -- Initialize message list if p_init_msg_list is set to TRUE.
67 IF FND_API.To_Boolean( p_init_msg_list ) THEN
68 FND_MSG_PUB.initialize;
69 END IF;
70
71 -- Initialize API return status to success
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 -- real logic --
75 ----------------
76
77 OPEN sequence_cursor;
78 FETCH sequence_cursor INTO p_host_app_id;
79 CLOSE sequence_cursor;
80 p_object_version_number := 1;
81
82 INSERT INTO jtf_prefab_host_apps (host_app_id,
83 object_version_number,
84 created_by,
85 creation_date,
86 last_updated_by,
87 last_update_date,
88 last_update_login,
89 -- security_group_id,
90 wsh_po_id,
91 application_id,
92 cache_policy,
93 cache_clear_flag,
94 cache_reload_flag,
95 cache_filter_policy)
96 VALUES (p_host_app_id,
97 p_object_version_number,
98 G_USER_ID,
99 SYSDATE,
100 G_USER_ID,
101 SYSDATE,
102 G_LOGIN_ID,
103 -- NULL,
104 p_wsh_po_id,
105 p_application_id,
106 p_cache_policy,
107 p_cache_clear_flag,
108 p_cache_reload_flag,
109 p_cache_filter_policy);
110
111 -- figure out all the cache components, and for each component,
112 -- add to the ha_comps table
113
114 IF p_application_id = -1 THEN
115 OPEN cache_comps_cursor_gen;
116 FETCH cache_comps_cursor_gen INTO l_ca_comp_id;
117
118 WHILE cache_comps_cursor_gen%FOUND LOOP
119 JTF_PREFAB_CACHE_PUB.INSERT_HA_COMP(p_api_version,
120 p_init_msg_list,
121 p_commit,
122 l_ha_comp_id,
123 p_host_app_id,
124 l_ca_comp_id,
125 'CO',
126 'f',
127 'f',
128 p_object_version_number,
129 x_return_status,
130 x_msg_count,
131 x_msg_data);
132 FETCH cache_comps_cursor_gen INTO l_ca_comp_id;
133 END LOOP;
134 CLOSE cache_comps_cursor_gen;
135 ELSE
136 OPEN cache_comps_cursor;
137 FETCH cache_comps_cursor INTO l_ca_comp_id;
138 WHILE cache_comps_cursor%FOUND LOOP
139 JTF_PREFAB_CACHE_PUB.INSERT_HA_COMP(p_api_version,
140 p_init_msg_list,
141 p_commit,
142 l_ha_comp_id,
143 p_host_app_id,
144 l_ca_comp_id,
145 'CO',
146 'f',
147 'f',
148 p_object_version_number,
149 x_return_status,
150 x_msg_count,
151 x_msg_data);
152 FETCH cache_comps_cursor INTO l_ca_comp_id;
153 END LOOP;
154 CLOSE cache_comps_cursor;
155 END IF;
156
157 -- for each application/host pair that the filter belongs to,
158 -- add a row to ha_filters
159
160 OPEN cache_filters_cursor;
161 FETCH cache_filters_cursor INTO l_ca_filter_id;
162
163 WHILE cache_filters_cursor%FOUND LOOP
164 JTF_PREFAB_CACHE_PUB.INSERT_HA_FILTER(p_api_version,
165 p_init_msg_list,
166 p_commit,
167 l_ha_filter_id,
168 p_host_app_id,
169 l_ca_filter_id,
170 't',
171 p_object_version_number,
172 x_return_status,
173 x_msg_count,
174 x_msg_data);
175 FETCH cache_filters_cursor INTO l_ca_filter_id;
176 END LOOP;
177
178 CLOSE cache_filters_cursor;
179
180 -----------------------
181 -- end of real logic --
182
183 -- Standard check of p_commit.
184 IF (FND_API.To_Boolean(p_commit)) THEN
185 COMMIT WORK;
186 END IF;
187
188 -- Standard call to get message count and if count is 1, get message info.
189 FND_MSG_PUB.Count_And_Get(
190 p_count => x_msg_count,
191 p_data => x_msg_data );
192
193 EXCEPTION
194 WHEN FND_API.G_EXC_ERROR THEN
195 ROLLBACK TO INSERT_HOST_APP;
196 x_return_status := FND_API.G_RET_STS_ERROR ;
197
198 FND_MSG_PUB.Count_And_Get(
199 p_count => x_msg_count,
200 p_data => x_msg_data );
201
202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
203 ROLLBACK TO INSERT_HOST_APP;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
205
206 FND_MSG_PUB.Count_And_Get(
207 p_count => x_msg_count,
208 p_data => x_msg_data );
209
210 WHEN OTHERS THEN
211 ROLLBACK TO INSERT_HOST_APP;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
213
214 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
215 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
216 END IF;
217
218 FND_MSG_PUB.Count_And_Get(
219 p_count => x_msg_count,
220 p_data => x_msg_data );
221
222 END INSERT_HOST_APP;
223
224 PROCEDURE UPDATE_HOST_APP(
225 p_api_version IN NUMBER,
226 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
227 p_commit IN VARCHAR2 := FND_API.G_FALSE,
228
229 p_host_app_id IN jtf_prefab_host_apps.host_app_id%TYPE,
230 p_cache_policy IN jtf_prefab_host_apps.cache_policy%TYPE,
231 p_cache_clear_flag IN jtf_prefab_host_apps.cache_clear_flag%TYPE,
232 p_cache_reload_flag IN jtf_prefab_host_apps.cache_reload_flag%TYPE,
233 p_cache_filter_policy IN jtf_prefab_host_apps.cache_filter_policy%TYPE,
234
235 p_object_version_number IN OUT NOCOPY jtf_prefab_host_apps.object_version_number%TYPE,
236
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2
240 ) AS
241 -- local variables --
242 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HOST_APP';
243 l_api_version NUMBER := p_api_version;
244
245 BEGIN
246 -- Standard Start of API savepoint
247 SAVEPOINT UPDATE_HOST_APP;
248
249 -- Standard call to check for call compatibility.
250 IF NOT FND_API.Compatible_API_Call (
251 l_api_version,
252 p_api_version,
253 l_api_name,
254 G_PKG_NAME)
255 THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258
259 -- Initialize message list if p_init_msg_list is set to TRUE.
260 IF FND_API.To_Boolean( p_init_msg_list ) THEN
261 FND_MSG_PUB.initialize;
262 END IF;
263
264 -- Initialize API return status to success
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266
267 -- real logic --
268 ----------------
269
270 UPDATE jtf_prefab_host_apps
271 SET object_version_number = p_object_version_number,
272 last_updated_by = G_USER_ID,
273 last_update_date = SYSDATE,
274 last_update_login = G_LOGIN_ID,
275 cache_policy = p_cache_policy,
276 cache_clear_flag = p_cache_clear_flag,
277 cache_reload_flag = p_cache_reload_flag,
278 cache_filter_policy = p_cache_filter_policy
279 WHERE host_app_id = p_host_app_id;
280
281 -----------------------
282 -- end of real logic --
283
284 -- Standard check of p_commit.
285 IF (FND_API.To_Boolean(p_commit)) THEN
286 COMMIT WORK;
287 END IF;
288
289 -- Standard call to get message count and if count is 1, get message info.
290 FND_MSG_PUB.Count_And_Get(
291 p_count => x_msg_count,
292 p_data => x_msg_data );
293
294 EXCEPTION
295 WHEN FND_API.G_EXC_ERROR THEN
296 ROLLBACK TO UPDATE_HOST_APP;
297 x_return_status := FND_API.G_RET_STS_ERROR ;
298
299 FND_MSG_PUB.Count_And_Get(
300 p_count => x_msg_count,
301 p_data => x_msg_data );
302
303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304 ROLLBACK TO UPDATE_HOST_APP;
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
306
307 FND_MSG_PUB.Count_And_Get(
308 p_count => x_msg_count,
309 p_data => x_msg_data );
310
311 WHEN OTHERS THEN
312 ROLLBACK TO UPDATE_HOST_APP;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
314
315 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
316 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
317 END IF;
318
319 FND_MSG_PUB.Count_And_Get(
320 p_count => x_msg_count,
321 p_data => x_msg_data );
322
323 END UPDATE_HOST_APP;
324
325 procedure SELECT_HOST_APP_FOR_HOST(
326 p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
328 p_host_app_id IN jtf_prefab_host_apps.host_app_id%TYPE,
329
330 p_object_version_number OUT NOCOPY jtf_prefab_host_apps.object_version_number%TYPE,
331 p_wsh_po_id OUT NOCOPY jtf_prefab_host_apps.wsh_po_id%TYPE,
332 p_application_id OUT NOCOPY jtf_prefab_host_apps.application_id%TYPE,
333 p_cache_policy OUT NOCOPY jtf_prefab_host_apps.cache_policy%TYPE,
334 p_cache_clear_flag OUT NOCOPY jtf_prefab_host_apps.cache_clear_flag%TYPE,
335 p_cache_reload_flag OUT NOCOPY jtf_prefab_host_apps.cache_reload_flag%TYPE,
336 p_cache_filter_policy OUT NOCOPY jtf_prefab_host_apps.cache_filter_policy%TYPE,
337 p_hostname OUT NOCOPY jtf_prefab_wsh_poes_b.hostname%TYPE,
338 p_appname OUT NOCOPY fnd_application_vl.application_name%TYPE,
339 p_app_short_name OUT NOCOPY fnd_application_vl.application_short_name%TYPE,
340
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2
344 ) AS
345 l_api_name CONSTANT VARCHAR2(30) := 'SELECT_HOST_APPS_FOR_HOST';
346 l_api_version CONSTANT NUMBER := p_api_version;
347
348 CURSOR host_app_cursor IS
349 SELECT application_id
350 FROM jtf_prefab_host_apps
351 WHERE host_app_id = p_host_app_id;
352
353 BEGIN
354
355 -- Standard call to check for call compatibility.
356 IF NOT FND_API.Compatible_API_Call (
357 l_api_version,
358 p_api_version,
359 l_api_name,
360 G_PKG_NAME)
361 THEN
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363 END IF;
364
365 -- Initialize message list if p_init_msg_list is set to TRUE.
366 IF FND_API.To_Boolean( p_init_msg_list ) THEN
367 FND_MSG_PUB.initialize;
368 END IF;
369
370 -- Initialize API return status to success
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372 x_msg_count := 0;
373 x_msg_data := 'S';
374
375
376 open host_app_cursor;
377 fetch host_app_cursor into p_application_id;
378
379 if host_app_cursor%FOUND THEN
380
381 if p_application_id = -1 then
382
383 SELECT app.object_version_number, app.wsh_po_id, app.application_id, app.cache_policy, app.cache_clear_flag, app.cache_reload_flag, app.cache_filter_policy, host.hostname, 'HTML-Platform', ''
384 INTO p_object_version_number, p_wsh_po_id, p_application_id, p_cache_policy, p_cache_clear_flag, p_cache_reload_flag, p_cache_filter_policy, p_hostname, p_appname, p_app_short_name
385 FROM jtf_prefab_host_apps app, jtf_prefab_wsh_poes_vl host
386 WHERE app.host_app_id = p_host_app_id
387 AND app.wsh_po_id = host.wsh_po_id;
388
389 else
390
391 SELECT app.object_version_number, app.wsh_po_id, app.application_id, app.cache_policy, app.cache_clear_flag, app.cache_reload_flag, app.cache_filter_policy, host.hostname, fndapp.application_name, fndapp.application_short_name
392 INTO p_object_version_number, p_wsh_po_id, p_application_id, p_cache_policy, p_cache_clear_flag, p_cache_reload_flag, p_cache_filter_policy, p_hostname, p_appname, p_app_short_name
393 FROM jtf_prefab_host_apps app, jtf_prefab_wsh_poes_vl host, fnd_application_vl fndapp
394 WHERE app.host_app_id = p_host_app_id
395 AND app.wsh_po_id = host.wsh_po_id
396 AND app.application_id = fndapp.application_id;
397
398 end if;
399
400 end if;
401 close host_app_cursor;
402
403 END SELECT_HOST_APP_FOR_HOST;
404
405
406 procedure DELETE_HOST_APP(
407 p_api_version IN NUMBER,
408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
409 p_commit IN VARCHAR2 := FND_API.G_FALSE,
410
411 p_host_app_id IN jtf_prefab_host_apps.host_app_id%TYPE,
412
413 p_object_version_number IN jtf_prefab_host_apps.object_version_number%TYPE,
414
415 x_return_status OUT NOCOPY VARCHAR2,
416 x_msg_count OUT NOCOPY NUMBER,
417 x_msg_data OUT NOCOPY VARCHAR2
418 ) AS
419 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOST_APP';
420 l_api_version CONSTANT NUMBER := p_api_version;
421
422 BEGIN
423 -- Standard Start of API savepoint
424 SAVEPOINT DELETE_HOST_APP;
425
426 -- Standard call to check for call compatibility.
427 IF NOT FND_API.Compatible_API_Call (
428 l_api_version,
429 p_api_version,
430 l_api_name,
431 G_PKG_NAME)
432 THEN
433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
434 END IF;
435
436 -- Initialize message list if p_init_msg_list is set to TRUE.
437 IF FND_API.To_Boolean( p_init_msg_list ) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440
441 -- Initialize API return status to success
442 x_return_status := FND_API.G_RET_STS_SUCCESS;
443
444 -- real logic --
445 ----------------
446
447 DELETE FROM jtf_prefab_host_apps
448 WHERE host_app_id = p_host_app_id;
449
450 JTF_PREFAB_CACHE_PUB.DELETE_HA_COMPS_FOR_HOST_APP(p_api_version,
451 p_init_msg_list,
452 p_commit,
453 p_host_app_id,
454 p_object_version_number,
455 x_return_status,
456 x_msg_count,
457 x_msg_data);
458
459 JTF_PREFAB_CACHE_PUB.DELETE_HA_FILTERS_F_HOST_APP(p_api_version,
460 p_init_msg_list,
461 p_commit,
462 p_host_app_id,
463 p_object_version_number,
464 x_return_status,
465 x_msg_count,
466 x_msg_data);
467
468 -----------------------
469 -- end of real logic --
470
471 -- Standard check of p_commit.
472 IF (FND_API.To_Boolean(p_commit)) THEN
473 COMMIT WORK;
474 END IF;
475
476 -- Standard call to get message count and if count is 1, get message info.
477 FND_MSG_PUB.Count_And_Get(
478 p_count => x_msg_count,
479 p_data => x_msg_data );
480
481 EXCEPTION
482 WHEN FND_API.G_EXC_ERROR THEN
483 ROLLBACK TO DELETE_HOST_APP;
484 x_return_status := FND_API.G_RET_STS_ERROR ;
485
486 FND_MSG_PUB.Count_And_Get(
487 p_count => x_msg_count,
488 p_data => x_msg_data );
489
490 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
491 ROLLBACK TO DELETE_HOST_APP;
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
493
494 FND_MSG_PUB.Count_And_Get(
495 p_count => x_msg_count,
496 p_data => x_msg_data );
497
498 WHEN OTHERS THEN
499 ROLLBACK TO DELETE_HOST_APP;
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501
502 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
503 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
504 END IF;
505
506 FND_MSG_PUB.Count_And_Get(
507 p_count => x_msg_count,
508 p_data => x_msg_data );
509
510 END DELETE_HOST_APP;
511
512 procedure DELETE_HOST_APPS_FOR_HOST(
513 p_api_version IN NUMBER,
514 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
515 p_commit IN VARCHAR2 := FND_API.G_FALSE,
516
517 p_wsh_po_id IN jtf_prefab_host_apps.wsh_po_id%TYPE,
518
519 p_object_version_number IN jtf_prefab_host_apps.object_version_number%TYPE,
520
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_msg_count OUT NOCOPY NUMBER,
523 x_msg_data OUT NOCOPY VARCHAR2
524 ) AS
525 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOST_APPS_FOR_HOST';
526 l_api_version CONSTANT NUMBER := p_api_version;
527
528 CURSOR host_apps_cursor IS
529 SELECT host_app_id
530 FROM jtf_prefab_host_apps
531 WHERE wsh_po_id = p_wsh_po_id;
532 l_host_app_id NUMBER;
533 BEGIN
534 -- Standard Start of API savepoint
535 SAVEPOINT DELETE_HOST_APPS_FOR_HOST;
536
537 -- Standard call to check for call compatibility.
538 IF NOT FND_API.Compatible_API_Call (
539 l_api_version,
540 p_api_version,
541 l_api_name,
542 G_PKG_NAME)
543 THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END IF;
546
547 -- Initialize message list if p_init_msg_list is set to TRUE.
548 IF FND_API.To_Boolean( p_init_msg_list ) THEN
549 FND_MSG_PUB.initialize;
550 END IF;
551
552 -- Initialize API return status to success
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554
555 -- real logic --
556 ----------------
557
558 OPEN host_apps_cursor;
559
560 FETCH host_apps_cursor INTO l_host_app_id;
561 WHILE host_apps_cursor%FOUND LOOP
562 JTF_PREFAB_CACHE_PUB.DELETE_HOST_APP(p_api_version,
563 p_init_msg_list,
564 p_commit,
565 l_host_app_id,
566 p_object_version_number,
567 x_return_status,
568 x_msg_count,
569 x_msg_data);
570 FETCH host_apps_cursor INTO l_host_app_id;
571 END LOOP;
572
573 CLOSE host_apps_cursor;
574
575 -----------------------
576 -- end of real logic --
577
578 -- Standard check of p_commit.
579 IF (FND_API.To_Boolean(p_commit)) THEN
580 COMMIT WORK;
581 END IF;
582
583 -- Standard call to get message count and if count is 1, get message info.
584 FND_MSG_PUB.Count_And_Get(
585 p_count => x_msg_count,
586 p_data => x_msg_data );
587
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 ROLLBACK TO DELETE_HOST_APPS_FOR_HOST;
591 x_return_status := FND_API.G_RET_STS_ERROR ;
592
593 FND_MSG_PUB.Count_And_Get(
594 p_count => x_msg_count,
595 p_data => x_msg_data );
596
597 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
598 ROLLBACK TO DELETE_HOST_APPS_FOR_HOST;
599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
600
601 FND_MSG_PUB.Count_And_Get(
602 p_count => x_msg_count,
603 p_data => x_msg_data );
604
605 WHEN OTHERS THEN
606 ROLLBACK TO DELETE_HOST_APPS_FOR_HOST;
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
608
609 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
610 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
611 END IF;
612
613 FND_MSG_PUB.Count_And_Get(
614 p_count => x_msg_count,
615 p_data => x_msg_data );
616
617 END DELETE_HOST_APPS_FOR_HOST;
618
619 PROCEDURE INSERT_CACHE_COMP(
620 p_api_version IN NUMBER,
621 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
622 p_commit IN VARCHAR2 := FND_API.G_FALSE,
623
624 p_ca_comp_id OUT NOCOPY jtf_prefab_ca_comps_b.ca_comp_id%TYPE,
625 p_application_id IN jtf_prefab_ca_comps_b.application_id%TYPE,
626 p_comp_name IN jtf_prefab_ca_comps_b.comp_name%TYPE,
627 p_description IN jtf_prefab_ca_comps_tl.description%TYPE,
628 p_component_key IN jtf_prefab_ca_comps_b.component_key%TYPE,
629 p_loader_class_name IN jtf_prefab_ca_comps_b.loader_class_name%TYPE,
630 p_timeout_type IN jtf_prefab_ca_comps_b.timeout_type%TYPE,
631 p_timeout IN jtf_prefab_ca_comps_b.timeout%TYPE,
632 p_timeout_unit IN jtf_prefab_ca_comps_b.timeout_unit%TYPE,
633 p_sgid_enabled_flag IN jtf_prefab_ca_comps_b.sgid_enabled_flag%TYPE,
634 p_stat_enabled_flag IN jtf_prefab_ca_comps_b.stat_enabled_flag%TYPE,
635 p_distributed_flag IN jtf_prefab_ca_comps_b.distributed_flag%TYPE,
636 p_cache_generic_flag IN jtf_prefab_ca_comps_b.cache_generic_flag%TYPE,
637 p_business_event_name IN jtf_prefab_ca_comps_b.business_event_name%TYPE,
638
639 p_object_version_number OUT NOCOPY jtf_prefab_ca_comps_b.object_version_number%TYPE,
640
641 x_return_status OUT NOCOPY VARCHAR2,
642 x_msg_count OUT NOCOPY NUMBER,
643 x_msg_data OUT NOCOPY VARCHAR2
644 ) AS
645 -- local variables --
646 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CACHE_COMP';
647 l_api_version NUMBER := p_api_version;
648 l_row_id VARCHAR2(255) := NULL;
649
650 CURSOR sequence_cursor IS
651 SELECT jtf_prefab_ca_comps_b_s.NEXTVAL from dual;
652 CURSOR host_apps_cursor IS
653 SELECT host_app_id
654 FROM jtf_prefab_host_apps
655 WHERE application_id = p_application_id;
656 CURSOR host_apps_cursor_gen IS
657 SELECT host_app_id
658 FROM jtf_prefab_host_apps
659 WHERE application_id = -1;
660 l_host_app_id NUMBER;
661 l_ha_comp_id NUMBER;
662 BEGIN
663 -- Standard Start of API savepoint
664 SAVEPOINT INSERT_CACHE_COMP;
665
666 -- Standard call to check for call compatibility.
667 IF NOT FND_API.Compatible_API_Call (
668 l_api_version,
669 p_api_version,
670 l_api_name,
671 G_PKG_NAME)
672 THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676 -- Initialize message list if p_init_msg_list is set to TRUE.
677 IF FND_API.To_Boolean( p_init_msg_list ) THEN
678 FND_MSG_PUB.initialize;
679 END IF;
680
681 -- Initialize API return status to success
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683
684 -- real logic --
685 ----------------
686
687 OPEN sequence_cursor;
688 FETCH sequence_cursor INTO p_ca_comp_id;
689 CLOSE sequence_cursor;
690 p_object_version_number := 1;
691
692 JTF_PREFAB_CA_COMPS_PKG.INSERT_ROW(l_row_id,
693 p_ca_comp_id,
694 NULL,
695 p_application_id,
696 p_comp_name,
697 p_component_key,
698 p_loader_class_name,
699 p_timeout_type,
700 p_timeout,
701 p_timeout_unit,
702 p_sgid_enabled_flag,
703 p_stat_enabled_flag,
704 p_distributed_flag,
705 p_cache_generic_flag,
706 p_business_event_name,
707 p_object_version_number,
708 p_description,
709 SYSDATE,
710 G_USER_ID,
711 SYSDATE,
712 G_USER_ID,
713 G_LOGIN_ID);
714
715 /*
716 INSERT INTO jtf_prefab_cache_comps (ca_comp_id,
717 object_version_number,
718 created_by,
719 creation_date,
720 last_updated_by,
721 last_update_date,
722 last_update_login,
723 -- security_group_id,
724 application_id,
725 comp_name,
726 description,
727 component_key,
728 loader_class_name,
729 timeout_type,
730 timeout,
731 timeout_unit,
732 sgid_enabled_flag,
733 stat_enabled_flag,
734 distributed_flag,
735 cache_generic_flag)
736 VALUES (p_ca_comp_id,
737 p_object_version_number,
738 G_USER_ID,
739 SYSDATE,
740 G_USER_ID,
741 SYSDATE,
742 G_LOGIN_ID,
743 -- NULL,
744 p_application_id,
745 p_comp_name,
746 p_description,
747 p_component_key,
748 p_loader_class_name,
749 p_timeout_type,
750 p_timeout,
751 p_timeout_unit,
752 p_sgid_enabled_flag,
753 p_stat_enabled_flag,
754 p_distributed_flag,
755 p_cache_generic_flag);
756 */
757
758 -- for each application/host pair that the component belongs to,
759 -- add a row to ha_comps
760
761 IF p_cache_generic_flag = 'f' THEN
762 OPEN host_apps_cursor;
763 FETCH host_apps_cursor INTO l_host_app_id;
764
765 WHILE host_apps_cursor%FOUND LOOP
766 JTF_PREFAB_CACHE_PUB.INSERT_HA_COMP(p_api_version,
767 p_init_msg_list,
768 p_commit,
769 l_ha_comp_id,
770 l_host_app_id,
771 p_ca_comp_id,
772 'CO',
773 'f',
774 'f',
775 p_object_version_number,
776 x_return_status,
777 x_msg_count,
778 x_msg_data);
779 FETCH host_apps_cursor INTO l_host_app_id;
780 END LOOP;
781 CLOSE host_apps_cursor;
782
783 END IF;
784
785 IF p_cache_generic_flag = 't' THEN
786 OPEN host_apps_cursor_gen;
787 FETCH host_apps_cursor_gen INTO l_host_app_id;
788
789 WHILE host_apps_cursor_gen%FOUND LOOP
790 JTF_PREFAB_CACHE_PUB.INSERT_HA_COMP(p_api_version,
791 p_init_msg_list,
792 p_commit,
793 l_ha_comp_id,
794 l_host_app_id,
795 p_ca_comp_id,
796 'CO',
797 'f',
798 'f',
799 p_object_version_number,
800 x_return_status,
801 x_msg_count,
802 x_msg_data);
803 FETCH host_apps_cursor_gen INTO l_host_app_id;
804 END LOOP;
805 CLOSE host_apps_cursor_gen;
806
807 END IF;
808
809 -----------------------
810 -- end of real logic --
811
812 -- Standard check of p_commit.
813 IF (FND_API.To_Boolean(p_commit)) THEN
814 COMMIT WORK;
815 END IF;
816
817 -- Standard call to get message count and if count is 1, get message info.
818 FND_MSG_PUB.Count_And_Get(
819 p_count => x_msg_count,
820 p_data => x_msg_data );
821
822 EXCEPTION
823 WHEN FND_API.G_EXC_ERROR THEN
824 ROLLBACK TO INSERT_CACHE_COMP;
825 x_return_status := FND_API.G_RET_STS_ERROR ;
826
827 FND_MSG_PUB.Count_And_Get(
828 p_count => x_msg_count,
829 p_data => x_msg_data );
830
831 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
832 ROLLBACK TO INSERT_CACHE_COMP;
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
834
835 FND_MSG_PUB.Count_And_Get(
836 p_count => x_msg_count,
837 p_data => x_msg_data );
838
839 WHEN OTHERS THEN
840 ROLLBACK TO INSERT_CACHE_COMP;
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
842
843 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
844 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
845 END IF;
846
847 FND_MSG_PUB.Count_And_Get(
848 p_count => x_msg_count,
849 p_data => x_msg_data );
850
851 END INSERT_CACHE_COMP;
852
853 PROCEDURE UPDATE_CACHE_COMP(
854 p_api_version IN NUMBER,
855 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
856 p_commit IN VARCHAR2 := FND_API.G_FALSE,
857
858 p_ca_comp_id IN jtf_prefab_ca_comps_b.ca_comp_id%TYPE,
859 p_application_id IN jtf_prefab_ca_comps_b.application_id%TYPE,
860 p_comp_name IN jtf_prefab_ca_comps_b.comp_name%TYPE,
861 p_description IN jtf_prefab_ca_comps_tl.description%TYPE,
862 p_component_key IN jtf_prefab_ca_comps_b.component_key%TYPE,
863 p_loader_class_name IN jtf_prefab_ca_comps_b.loader_class_name%TYPE,
864 p_timeout_type IN jtf_prefab_ca_comps_b.timeout_type%TYPE,
865 p_timeout IN jtf_prefab_ca_comps_b.timeout%TYPE,
866 p_timeout_unit IN jtf_prefab_ca_comps_b.timeout_unit%TYPE,
867 p_sgid_enabled_flag IN jtf_prefab_ca_comps_b.sgid_enabled_flag%TYPE,
868 p_stat_enabled_flag IN jtf_prefab_ca_comps_b.stat_enabled_flag%TYPE,
869 p_distributed_flag IN jtf_prefab_ca_comps_b.distributed_flag%TYPE,
870 p_cache_generic_flag IN jtf_prefab_ca_comps_b.cache_generic_flag%TYPE,
871 p_business_event_name IN jtf_prefab_ca_comps_b.business_event_name%TYPE,
872
873 p_object_version_number IN OUT NOCOPY jtf_prefab_ca_comps_b.object_version_number%TYPE,
874
875 x_return_status OUT NOCOPY VARCHAR2,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2
878 ) AS
879 -- local variables --
880 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CACHE_COMP';
881 l_api_version NUMBER := p_api_version;
882
883 BEGIN
884 -- Standard Start of API savepoint
885 SAVEPOINT UPDATE_CACHE_COMP;
886
887 -- Standard call to check for call compatibility.
888 IF NOT FND_API.Compatible_API_Call (
889 l_api_version,
890 p_api_version,
891 l_api_name,
892 G_PKG_NAME)
893 THEN
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895 END IF;
896
897 -- Initialize message list if p_init_msg_list is set to TRUE.
898 IF FND_API.To_Boolean( p_init_msg_list ) THEN
899 FND_MSG_PUB.initialize;
900 END IF;
901
902 -- Initialize API return status to success
903 x_return_status := FND_API.G_RET_STS_SUCCESS;
904
905 -- real logic --
906 ----------------
907
908 JTF_PREFAB_CA_COMPS_PKG.UPDATE_ROW(p_ca_comp_id,
909 NULL,
910 p_application_id,
911 p_comp_name,
912 p_component_key,
913 p_loader_class_name,
914 p_timeout_type,
915 p_timeout,
916 p_timeout_unit,
917 p_sgid_enabled_flag,
918 p_stat_enabled_flag,
919 p_distributed_flag,
920 p_cache_generic_flag,
921 p_business_event_name,
922 p_object_version_number,
923 p_description,
924 SYSDATE,
925 G_USER_ID,
926 G_LOGIN_ID);
927
928 /*
929 UPDATE jtf_prefab_cache_comps
930 SET object_version_number = p_object_version_number,
931 last_updated_by = G_USER_ID,
932 last_update_date = SYSDATE,
933 last_update_login = G_LOGIN_ID,
934 comp_name = p_comp_name,
935 description = p_description,
936 loader_class_name = p_loader_class_name,
937 timeout_type = p_timeout_type,
938 timeout = p_timeout,
939 timeout_unit = p_timeout_unit,
940 sgid_enabled_flag = p_sgid_enabled_flag,
941 stat_enabled_flag = p_stat_enabled_flag,
942 distributed_flag = p_distributed_flag,
943 cache_generic_flag = p_cache_generic_flag
944 WHERE ca_comp_id = p_ca_comp_id;
945 */
946
947 -----------------------
948 -- end of real logic --
949
950 -- Standard check of p_commit.
951 IF (FND_API.To_Boolean(p_commit)) THEN
952 COMMIT WORK;
953 END IF;
954
955 -- Standard call to get message count and if count is 1, get message info.
956 FND_MSG_PUB.Count_And_Get(
957 p_count => x_msg_count,
958 p_data => x_msg_data );
959
960 EXCEPTION
961 WHEN FND_API.G_EXC_ERROR THEN
962 ROLLBACK TO UPDATE_CACHE_COMP;
963 x_return_status := FND_API.G_RET_STS_ERROR ;
964
965 FND_MSG_PUB.Count_And_Get(
966 p_count => x_msg_count,
967 p_data => x_msg_data );
968
969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
970 ROLLBACK TO UPDATE_CACHE_COMP;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
972
973 FND_MSG_PUB.Count_And_Get(
974 p_count => x_msg_count,
975 p_data => x_msg_data );
976
977 WHEN OTHERS THEN
978 ROLLBACK TO UPDATE_CACHE_COMP;
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
980
981 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
982 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
983 END IF;
984
985 FND_MSG_PUB.Count_And_Get(
986 p_count => x_msg_count,
987 p_data => x_msg_data );
988
989 END UPDATE_CACHE_COMP;
990
991 PROCEDURE UPDATE_CACHE_COMP_1(
992 p_api_version IN NUMBER,
993 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
994 p_commit IN VARCHAR2 := FND_API.G_FALSE,
995
996 p_ca_comp_id IN jtf_prefab_ca_comps_b.ca_comp_id%TYPE,
997
998 -- p_object_version_number IN OUT NOCOPY jtf_prefab_ca_comps_b.object_version_number%TYPE,
999
1000 x_return_status OUT NOCOPY VARCHAR2,
1001 x_msg_count OUT NOCOPY NUMBER,
1002 x_msg_data OUT NOCOPY VARCHAR2
1003 ) AS
1004 -- local variables --
1005 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CACHE_COMP_1';
1006 l_api_version NUMBER := p_api_version;
1007
1008 BEGIN
1009 -- Standard Start of API savepoint
1010 SAVEPOINT UPDATE_CACHE_COMP_1;
1011
1012 -- Standard call to check for call compatibility.
1013 IF NOT FND_API.Compatible_API_Call (
1014 l_api_version,
1015 p_api_version,
1016 l_api_name,
1017 G_PKG_NAME)
1018 THEN
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020 END IF;
1021
1022 -- Initialize message list if p_init_msg_list is set to TRUE.
1023 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1024 FND_MSG_PUB.initialize;
1025 END IF;
1026
1027 -- Initialize API return status to success
1028 x_return_status := FND_API.G_RET_STS_SUCCESS;
1029
1030 -- real logic --
1031 ----------------
1032 -- Passing values for ca_comp_id and last_updated_by columns
1033 JTF_PREFAB_CA_COMPS_PKG.UPDATE_ROW_1(p_ca_comp_id,
1034 1);
1035
1036
1037 -----------------------
1038 -- end of real logic --
1039
1040 -- Standard check of p_commit.
1041 IF (FND_API.To_Boolean(p_commit)) THEN
1042 COMMIT WORK;
1043 END IF;
1044
1045 -- Standard call to get message count and if count is 1, get message info.
1046 FND_MSG_PUB.Count_And_Get(
1047 p_count => x_msg_count,
1048 p_data => x_msg_data );
1049
1050 EXCEPTION
1051 WHEN FND_API.G_EXC_ERROR THEN
1052 ROLLBACK TO UPDATE_CACHE_COMP_1;
1053 x_return_status := FND_API.G_RET_STS_ERROR ;
1054
1055 FND_MSG_PUB.Count_And_Get(
1056 p_count => x_msg_count,
1057 p_data => x_msg_data );
1058
1059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1060 ROLLBACK TO UPDATE_CACHE_COMP_1;
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1062
1063 FND_MSG_PUB.Count_And_Get(
1064 p_count => x_msg_count,
1065 p_data => x_msg_data );
1066
1067 WHEN OTHERS THEN
1068 ROLLBACK TO UPDATE_CACHE_COMP_1;
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1070
1071 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1072 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1073 END IF;
1074
1075 FND_MSG_PUB.Count_And_Get(
1076 p_count => x_msg_count,
1077 p_data => x_msg_data );
1078
1079 END UPDATE_CACHE_COMP_1;
1080
1081 procedure DELETE_CACHE_COMP(
1082 p_api_version IN NUMBER,
1083 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1084 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1085
1086 p_ca_comp_id IN jtf_prefab_ca_comps_b.ca_comp_id%TYPE,
1087
1088 p_object_version_number IN jtf_prefab_ca_comps_b.object_version_number%TYPE,
1089
1090 x_return_status OUT NOCOPY VARCHAR2,
1091 x_msg_count OUT NOCOPY NUMBER,
1092 x_msg_data OUT NOCOPY VARCHAR2
1093 ) AS
1094 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CACHE_COMP';
1095 l_api_version CONSTANT NUMBER := p_api_version;
1096
1097 BEGIN
1098 -- Standard Start of API savepoint
1099 SAVEPOINT DELETE_CACHE_COMP;
1100
1101 -- Standard call to check for call compatibility.
1102 IF NOT FND_API.Compatible_API_Call (
1103 l_api_version,
1104 p_api_version,
1105 l_api_name,
1106 G_PKG_NAME)
1107 THEN
1108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1109 END IF;
1110
1111 -- Initialize message list if p_init_msg_list is set to TRUE.
1112 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1113 FND_MSG_PUB.initialize;
1114 END IF;
1115
1116 -- Initialize API return status to success
1117 x_return_status := FND_API.G_RET_STS_SUCCESS;
1118
1119 -- real logic --
1120 ----------------
1121
1122 JTF_PREFAB_CA_COMPS_PKG.DELETE_ROW(p_ca_comp_id);
1123
1124 /*
1125 DELETE FROM jtf_prefab_cache_comps
1126 WHERE ca_comp_id = p_ca_comp_id;
1127 */
1128
1129 JTF_PREFAB_CACHE_PUB.DELETE_HA_COMPS_FOR_CACHE_COMP(p_api_version,
1130 p_init_msg_list,
1131 p_commit,
1132 p_ca_comp_id,
1133 p_object_version_number,
1134 x_return_status,
1135 x_msg_count,
1136 x_msg_data);
1137
1138
1139 -----------------------
1140 -- end of real logic --
1141
1142 -- Standard check of p_commit.
1143 IF (FND_API.To_Boolean(p_commit)) THEN
1144 COMMIT WORK;
1145 END IF;
1146
1147 -- Standard call to get message count and if count is 1, get message info.
1148 FND_MSG_PUB.Count_And_Get(
1149 p_count => x_msg_count,
1150 p_data => x_msg_data );
1151
1152 EXCEPTION
1153 WHEN FND_API.G_EXC_ERROR THEN
1154 ROLLBACK TO DELETE_CACHE_COMP;
1155 x_return_status := FND_API.G_RET_STS_ERROR ;
1156
1157 FND_MSG_PUB.Count_And_Get(
1158 p_count => x_msg_count,
1159 p_data => x_msg_data );
1160
1161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1162 ROLLBACK TO DELETE_CACHE_COMP;
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1164
1165 FND_MSG_PUB.Count_And_Get(
1166 p_count => x_msg_count,
1167 p_data => x_msg_data );
1168
1169 WHEN OTHERS THEN
1170 ROLLBACK TO DELETE_CACHE_COMP;
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1172
1173 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1174 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1175 END IF;
1176
1177 FND_MSG_PUB.Count_And_Get(
1178 p_count => x_msg_count,
1179 p_data => x_msg_data );
1180
1181 END DELETE_CACHE_COMP;
1182
1183 PROCEDURE INSERT_HA_COMP(
1184 p_api_version IN NUMBER,
1185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1186 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1187
1188 p_ha_comp_id OUT NOCOPY jtf_prefab_ha_comps.ha_comp_id%TYPE,
1189 p_host_app_id IN jtf_prefab_ha_comps.host_app_id%TYPE,
1190 p_ca_comp_id IN jtf_prefab_ha_comps.ca_comp_id%TYPE,
1191 p_cache_policy IN jtf_prefab_ha_comps.cache_policy%TYPE,
1192 p_cache_clear_flag IN jtf_prefab_ha_comps.cache_clear_flag%TYPE,
1193 p_cache_reload_flag IN jtf_prefab_ha_comps.cache_reload_flag%TYPE,
1194
1195 p_object_version_number OUT NOCOPY jtf_prefab_ha_comps.object_version_number%TYPE,
1196
1197 x_return_status OUT NOCOPY VARCHAR2,
1198 x_msg_count OUT NOCOPY NUMBER,
1199 x_msg_data OUT NOCOPY VARCHAR2
1200 ) AS
1201 -- local variables --
1202 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_HA_COMP';
1203 l_api_version NUMBER := p_api_version;
1204
1205 CURSOR sequence_cursor IS
1206 SELECT jtf_prefab_ha_comps_s.NEXTVAL from dual;
1207 BEGIN
1208 -- Standard Start of API savepoint
1209 SAVEPOINT INSERT_HA_COMP;
1210
1211 -- Standard call to check for call compatibility.
1212 IF NOT FND_API.Compatible_API_Call (
1213 l_api_version,
1214 p_api_version,
1215 l_api_name,
1216 G_PKG_NAME)
1217 THEN
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219 END IF;
1220
1221 -- Initialize message list if p_init_msg_list is set to TRUE.
1222 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1223 FND_MSG_PUB.initialize;
1224 END IF;
1225
1226 -- Initialize API return status to success
1227 x_return_status := FND_API.G_RET_STS_SUCCESS;
1228
1229 -- real logic --
1230 ----------------
1231
1232 OPEN sequence_cursor;
1233 FETCH sequence_cursor INTO p_ha_comp_id;
1234 CLOSE sequence_cursor;
1235 p_object_version_number := 1;
1236
1237 INSERT INTO jtf_prefab_ha_comps (ha_comp_id,
1238 object_version_number,
1239 created_by,
1240 creation_date,
1241 last_updated_by,
1242 last_update_date,
1243 last_update_login,
1244 -- security_group_id,
1245 host_app_id,
1246 ca_comp_id,
1247 cache_policy,
1248 cache_clear_flag,
1249 cache_reload_flag)
1250 VALUES (p_ha_comp_id,
1251 p_object_version_number,
1252 G_USER_ID,
1253 SYSDATE,
1254 G_USER_ID,
1255 SYSDATE,
1256 G_LOGIN_ID,
1257 -- NULL,
1258 p_host_app_id,
1259 p_ca_comp_id,
1260 p_cache_policy,
1261 p_cache_clear_flag,
1262 p_cache_reload_flag);
1263
1264 -----------------------
1265 -- end of real logic --
1266
1267 -- Standard check of p_commit.
1268 IF (FND_API.To_Boolean(p_commit)) THEN
1269 COMMIT WORK;
1270 END IF;
1271
1272 -- Standard call to get message count and if count is 1, get message info.
1273 FND_MSG_PUB.Count_And_Get(
1274 p_count => x_msg_count,
1275 p_data => x_msg_data );
1276
1277 EXCEPTION
1278 WHEN FND_API.G_EXC_ERROR THEN
1279 ROLLBACK TO INSERT_HA_COMP;
1280 x_return_status := FND_API.G_RET_STS_ERROR ;
1281
1282 FND_MSG_PUB.Count_And_Get(
1283 p_count => x_msg_count,
1284 p_data => x_msg_data );
1285
1286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1287 ROLLBACK TO INSERT_HA_COMP;
1288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1289
1290 FND_MSG_PUB.Count_And_Get(
1291 p_count => x_msg_count,
1292 p_data => x_msg_data );
1293
1294 WHEN OTHERS THEN
1295 ROLLBACK TO INSERT_HA_COMP;
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1297
1298 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1299 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1300 END IF;
1301
1302 FND_MSG_PUB.Count_And_Get(
1303 p_count => x_msg_count,
1304 p_data => x_msg_data );
1305
1306 END INSERT_HA_COMP;
1307
1308 PROCEDURE UPDATE_HA_COMP(
1309 p_api_version IN NUMBER,
1310 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1311 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1312
1313 p_ha_comp_id IN jtf_prefab_ha_comps.ha_comp_id%TYPE,
1314 p_cache_policy IN jtf_prefab_ha_comps.cache_policy%TYPE,
1315 p_cache_clear_flag IN jtf_prefab_ha_comps.cache_clear_flag%TYPE,
1316 p_cache_reload_flag IN jtf_prefab_ha_comps.cache_reload_flag%TYPE,
1317
1318 p_object_version_number IN OUT NOCOPY jtf_prefab_ha_comps.object_version_number%TYPE,
1319
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER,
1322 x_msg_data OUT NOCOPY VARCHAR2
1323 ) AS
1324 -- local variables --
1325 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HA_COMP';
1326 l_api_version NUMBER := p_api_version;
1327
1328 BEGIN
1329 -- Standard Start of API savepoint
1330 SAVEPOINT UPDATE_HA_COMP;
1331
1332 -- Standard call to check for call compatibility.
1333 IF NOT FND_API.Compatible_API_Call (
1334 l_api_version,
1335 p_api_version,
1336 l_api_name,
1337 G_PKG_NAME)
1338 THEN
1339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 END IF;
1341
1342 -- Initialize message list if p_init_msg_list is set to TRUE.
1343 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1344 FND_MSG_PUB.initialize;
1345 END IF;
1346
1347 -- Initialize API return status to success
1348 x_return_status := FND_API.G_RET_STS_SUCCESS;
1349
1350 -- real logic --
1351 ----------------
1352
1353 UPDATE jtf_prefab_ha_comps
1354 SET object_version_number = p_object_version_number,
1355 last_updated_by = G_USER_ID,
1356 last_update_date = SYSDATE,
1357 last_update_login = G_LOGIN_ID,
1358 cache_policy = p_cache_policy,
1359 cache_clear_flag = p_cache_clear_flag,
1360 cache_reload_flag = p_cache_reload_flag
1361 WHERE ha_comp_id = p_ha_comp_id;
1362
1363 -----------------------
1364 -- end of real logic --
1365
1366 -- Standard check of p_commit.
1367 IF (FND_API.To_Boolean(p_commit)) THEN
1368 COMMIT WORK;
1369 END IF;
1370
1371 -- Standard call to get message count and if count is 1, get message info.
1372 FND_MSG_PUB.Count_And_Get(
1373 p_count => x_msg_count,
1374 p_data => x_msg_data );
1375
1376 EXCEPTION
1377 WHEN FND_API.G_EXC_ERROR THEN
1378 ROLLBACK TO UPDATE_HA_COMP;
1379 x_return_status := FND_API.G_RET_STS_ERROR ;
1380
1381 FND_MSG_PUB.Count_And_Get(
1382 p_count => x_msg_count,
1383 p_data => x_msg_data );
1384
1385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1386 ROLLBACK TO UPDATE_HA_COMP;
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1388
1389 FND_MSG_PUB.Count_And_Get(
1390 p_count => x_msg_count,
1391 p_data => x_msg_data );
1392
1393 WHEN OTHERS THEN
1394 ROLLBACK TO UPDATE_HA_COMP;
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1396
1397 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1398 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1399 END IF;
1400
1401 FND_MSG_PUB.Count_And_Get(
1402 p_count => x_msg_count,
1403 p_data => x_msg_data );
1404
1405 END UPDATE_HA_COMP;
1406
1407 procedure DELETE_HA_COMPS_FOR_HOST_APP(
1408 p_api_version IN NUMBER,
1409 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1410 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1411
1412 p_host_app_id IN jtf_prefab_ha_comps.host_app_id%TYPE,
1413
1414 p_object_version_number IN jtf_prefab_ha_comps.object_version_number%TYPE,
1415
1416 x_return_status OUT NOCOPY VARCHAR2,
1417 x_msg_count OUT NOCOPY NUMBER,
1418 x_msg_data OUT NOCOPY VARCHAR2
1419 ) AS
1420 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HA_COMPS_FOR_HOST_APP';
1421 l_api_version CONSTANT NUMBER := p_api_version;
1422
1423 BEGIN
1424 -- Standard Start of API savepoint
1425 SAVEPOINT DELETE_HA_COMPS_FOR_HOST_APP;
1426
1427 -- Standard call to check for call compatibility.
1428 IF NOT FND_API.Compatible_API_Call (
1429 l_api_version,
1430 p_api_version,
1431 l_api_name,
1432 G_PKG_NAME)
1433 THEN
1434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1435 END IF;
1436
1437 -- Initialize message list if p_init_msg_list is set to TRUE.
1438 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1439 FND_MSG_PUB.initialize;
1440 END IF;
1441
1442 -- Initialize API return status to success
1443 x_return_status := FND_API.G_RET_STS_SUCCESS;
1444
1445 -- real logic --
1446 ----------------
1447
1448 DELETE FROM jtf_prefab_ha_comps
1449 WHERE host_app_id = p_host_app_id;
1450
1451 -----------------------
1452 -- end of real logic --
1453
1454 -- Standard check of p_commit.
1455 IF (FND_API.To_Boolean(p_commit)) THEN
1456 COMMIT WORK;
1457 END IF;
1458
1459 -- Standard call to get message count and if count is 1, get message info.
1460 FND_MSG_PUB.Count_And_Get(
1461 p_count => x_msg_count,
1462 p_data => x_msg_data );
1463
1464 EXCEPTION
1465 WHEN FND_API.G_EXC_ERROR THEN
1466 ROLLBACK TO DELETE_HA_COMPS_FOR_HOST_APP;
1467 x_return_status := FND_API.G_RET_STS_ERROR ;
1468
1469 FND_MSG_PUB.Count_And_Get(
1470 p_count => x_msg_count,
1471 p_data => x_msg_data );
1472
1473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1474 ROLLBACK TO DELETE_HA_COMPS_FOR_HOST_APP;
1475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1476
1477 FND_MSG_PUB.Count_And_Get(
1478 p_count => x_msg_count,
1479 p_data => x_msg_data );
1480
1481 WHEN OTHERS THEN
1482 ROLLBACK TO DELETE_HA_COMPS_FOR_HOST_APP;
1483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1484
1485 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1486 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1487 END IF;
1488
1489 FND_MSG_PUB.Count_And_Get(
1490 p_count => x_msg_count,
1491 p_data => x_msg_data );
1492
1493 END DELETE_HA_COMPS_FOR_HOST_APP;
1494
1495 procedure DELETE_HA_COMPS_FOR_CACHE_COMP(
1496 p_api_version IN NUMBER,
1497 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1498 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1499
1500 p_ca_comp_id IN jtf_prefab_ha_comps.ca_comp_id%TYPE,
1501
1502 p_object_version_number IN jtf_prefab_ha_comps.object_version_number%TYPE,
1503
1504 x_return_status OUT NOCOPY VARCHAR2,
1505 x_msg_count OUT NOCOPY NUMBER,
1506 x_msg_data OUT NOCOPY VARCHAR2
1507 ) AS
1508 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HA_COMPS_FOR_CACHE_COMP';
1509 l_api_version CONSTANT NUMBER := p_api_version;
1510
1511 BEGIN
1512 -- Standard Start of API savepoint
1513 SAVEPOINT DELETE_HA_COMPS_FOR_CACHE_COMP;
1514
1515 -- Standard call to check for call compatibility.
1516 IF NOT FND_API.Compatible_API_Call (
1517 l_api_version,
1518 p_api_version,
1519 l_api_name,
1520 G_PKG_NAME)
1521 THEN
1522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1523 END IF;
1524
1525 -- Initialize message list if p_init_msg_list is set to TRUE.
1526 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1527 FND_MSG_PUB.initialize;
1528 END IF;
1529
1530 -- Initialize API return status to success
1531 x_return_status := FND_API.G_RET_STS_SUCCESS;
1532
1533 -- real logic --
1534 ----------------
1535
1536 DELETE FROM jtf_prefab_ha_comps
1537 WHERE ca_comp_id = p_ca_comp_id;
1538
1539 -----------------------
1540 -- end of real logic --
1541
1542 -- Standard check of p_commit.
1543 IF (FND_API.To_Boolean(p_commit)) THEN
1544 COMMIT WORK;
1545 END IF;
1546
1547 -- Standard call to get message count and if count is 1, get message info.
1548 FND_MSG_PUB.Count_And_Get(
1549 p_count => x_msg_count,
1550 p_data => x_msg_data );
1551
1552 EXCEPTION
1553 WHEN FND_API.G_EXC_ERROR THEN
1554 ROLLBACK TO DELETE_HA_COMPS_FOR_CACHE_COMP;
1555 x_return_status := FND_API.G_RET_STS_ERROR ;
1556
1557 FND_MSG_PUB.Count_And_Get(
1558 p_count => x_msg_count,
1559 p_data => x_msg_data );
1560
1561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1562 ROLLBACK TO DELETE_HA_COMPS_FOR_CACHE_COMP;
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1564
1565 FND_MSG_PUB.Count_And_Get(
1566 p_count => x_msg_count,
1567 p_data => x_msg_data );
1568
1569 WHEN OTHERS THEN
1570 ROLLBACK TO DELETE_HA_COMPS_FOR_CACHE_COMP;
1571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1572
1573 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1574 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1575 END IF;
1576
1577 FND_MSG_PUB.Count_And_Get(
1578 p_count => x_msg_count,
1579 p_data => x_msg_data );
1580
1581 END DELETE_HA_COMPS_FOR_CACHE_COMP;
1582
1583 PROCEDURE INSERT_CACHE_STAT(
1584 p_api_version IN NUMBER,
1585 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1586 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1587
1588 p_cache_stat_id OUT NOCOPY jtf_prefab_cache_stats.cache_stat_id%TYPE,
1589 p_security_group_id IN jtf_prefab_cache_stats.security_group_id%TYPE,
1590 p_wsh_po_id IN jtf_prefab_cache_stats.wsh_po_id%TYPE,
1591 p_ca_comp_id IN jtf_prefab_cache_stats.ca_comp_id%TYPE,
1592 p_jvm_id IN jtf_prefab_cache_stats.jvm_id%TYPE,
1593 p_num_cache_miss IN jtf_prefab_cache_stats.num_cache_miss%TYPE,
1594 p_num_cache_hit IN jtf_prefab_cache_stats.num_cache_hit%TYPE,
1595 p_num_loader_miss IN jtf_prefab_cache_stats.num_loader_miss%TYPE,
1596 p_num_invalidate_call IN jtf_prefab_cache_stats.num_invalidate_call%TYPE,
1597 p_num_invalidations IN jtf_prefab_cache_stats.num_invalidations%TYPE,
1598 p_num_objects IN jtf_prefab_cache_stats.num_objects%TYPE,
1599 p_expiration_time IN jtf_prefab_cache_stats.expiration_time%TYPE,
1600 p_start_time IN jtf_prefab_cache_stats.start_time%TYPE,
1601 p_end_time IN jtf_prefab_cache_stats.end_time%TYPE,
1602
1603 p_object_version_number OUT NOCOPY jtf_prefab_cache_stats.object_version_number%TYPE,
1604
1605 x_return_status OUT NOCOPY VARCHAR2,
1606 x_msg_count OUT NOCOPY NUMBER,
1607 x_msg_data OUT NOCOPY VARCHAR2
1608 ) AS
1609 -- local variables --
1610 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CACHE_STAT';
1611 l_api_version NUMBER := p_api_version;
1612
1613 CURSOR sequence_cursor IS
1614 SELECT jtf_prefab_cache_stats_s.NEXTVAL from dual;
1615 BEGIN
1616 -- Standard Start of API savepoint
1617 SAVEPOINT INSERT_CACHE_STAT;
1618
1619 -- Standard call to check for call compatibility.
1620 IF NOT FND_API.Compatible_API_Call (
1621 l_api_version,
1622 p_api_version,
1623 l_api_name,
1624 G_PKG_NAME)
1625 THEN
1626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1627 END IF;
1628
1629 -- Initialize message list if p_init_msg_list is set to TRUE.
1630 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1631 FND_MSG_PUB.initialize;
1632 END IF;
1633
1634 -- Initialize API return status to success
1635 x_return_status := FND_API.G_RET_STS_SUCCESS;
1636
1637 -- real logic --
1638 ----------------
1639
1640 OPEN sequence_cursor;
1641 FETCH sequence_cursor INTO p_cache_stat_id;
1642 CLOSE sequence_cursor;
1643 p_object_version_number := 1;
1644
1645 INSERT INTO jtf_prefab_cache_stats(cache_stat_id,
1646 object_version_number,
1647 created_by,
1648 creation_date,
1649 last_updated_by,
1650 last_update_date,
1651 last_update_login,
1652 -- security_group_id,
1653 wsh_po_id,
1654 ca_comp_id,
1655 jvm_id,
1656 num_cache_miss,
1657 num_cache_hit,
1658 num_loader_miss,
1659 num_invalidate_call,
1660 num_invalidations,
1661 num_objects,
1662 expiration_time,
1663 start_time,
1664 end_time)
1665 VALUES (p_cache_stat_id,
1666 p_object_version_number,
1667 G_USER_ID,
1668 SYSDATE,
1669 G_USER_ID,
1670 SYSDATE,
1671 G_LOGIN_ID,
1672 -- p_security_group_id,
1673 p_wsh_po_id,
1674 p_ca_comp_id,
1675 -- p_jvm_id,
1676 0,
1677 p_num_cache_miss,
1678 p_num_cache_hit,
1679 p_num_loader_miss,
1680 p_num_invalidate_call,
1681 p_num_invalidations,
1682 p_num_objects,
1683 p_expiration_time,
1684 p_start_time,
1685 p_end_time);
1686
1687 -----------------------
1688 -- end of real logic --
1689
1690 -- Standard check of p_commit.
1691 IF (FND_API.To_Boolean(p_commit)) THEN
1692 COMMIT WORK;
1693 END IF;
1694
1695 -- Standard call to get message count and if count is 1, get message info.
1696 FND_MSG_PUB.Count_And_Get(
1697 p_count => x_msg_count,
1698 p_data => x_msg_data );
1699
1700 EXCEPTION
1701 WHEN FND_API.G_EXC_ERROR THEN
1702 ROLLBACK TO INSERT_CACHE_STAT;
1703 x_return_status := FND_API.G_RET_STS_ERROR ;
1704
1705 FND_MSG_PUB.Count_And_Get(
1706 p_count => x_msg_count,
1707 p_data => x_msg_data );
1708
1709 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1710 ROLLBACK TO INSERT_CACHE_STAT;
1711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1712
1713 FND_MSG_PUB.Count_And_Get(
1714 p_count => x_msg_count,
1715 p_data => x_msg_data );
1716
1717 WHEN OTHERS THEN
1718 ROLLBACK TO INSERT_CACHE_STAT;
1719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1720
1721 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1722 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1723 END IF;
1724
1725 FND_MSG_PUB.Count_And_Get(
1726 p_count => x_msg_count,
1727 p_data => x_msg_data );
1728 END INSERT_CACHE_STAT;
1729
1730 PROCEDURE UPDATE_CACHE_STAT(
1731 p_api_version IN NUMBER,
1732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1733 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1734
1735 p_cache_stat_id IN jtf_prefab_cache_stats.cache_stat_id%TYPE,
1736 p_security_group_id IN jtf_prefab_cache_stats.security_group_id%TYPE,
1737 p_wsh_po_id IN jtf_prefab_cache_stats.wsh_po_id%TYPE,
1738 p_ca_comp_id IN jtf_prefab_cache_stats.ca_comp_id%TYPE,
1739 p_jvm_id IN jtf_prefab_cache_stats.jvm_id%TYPE,
1740 p_num_cache_miss IN jtf_prefab_cache_stats.num_cache_miss%TYPE,
1741 p_num_cache_hit IN jtf_prefab_cache_stats.num_cache_hit%TYPE,
1742 p_num_loader_miss IN jtf_prefab_cache_stats.num_loader_miss%TYPE,
1743 p_num_invalidate_call IN jtf_prefab_cache_stats.num_invalidate_call%TYPE,
1744 p_num_invalidations IN jtf_prefab_cache_stats.num_invalidations%TYPE,
1745 p_num_objects IN jtf_prefab_cache_stats.num_objects%TYPE,
1746 p_expiration_time IN jtf_prefab_cache_stats.expiration_time%TYPE,
1747 p_start_time IN jtf_prefab_cache_stats.start_time%TYPE,
1748 p_end_time IN jtf_prefab_cache_stats.end_time%TYPE,
1749
1750 p_object_version_number IN OUT NOCOPY jtf_prefab_cache_stats.object_version_number%TYPE,
1751
1752 x_return_status OUT NOCOPY VARCHAR2,
1753 x_msg_count OUT NOCOPY NUMBER,
1754 x_msg_data OUT NOCOPY VARCHAR2
1755 ) AS
1756 -- local variables --
1757 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CACHE_STAT';
1758 l_api_version NUMBER := p_api_version;
1759
1760 BEGIN
1761 -- Standard Start of API savepoint
1762 SAVEPOINT UPDATE_CACHE_STAT;
1763
1764 -- Standard call to check for call compatibility.
1765 IF NOT FND_API.Compatible_API_Call (
1766 l_api_version,
1767 p_api_version,
1768 l_api_name,
1769 G_PKG_NAME)
1770 THEN
1771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1772 END IF;
1773
1774 -- Initialize message list if p_init_msg_list is set to TRUE.
1775 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1776 FND_MSG_PUB.initialize;
1777 END IF;
1778
1779 -- Initialize API return status to success
1780 x_return_status := FND_API.G_RET_STS_SUCCESS;
1781
1782 -- real logic --
1783 ----------------
1784
1785 UPDATE jtf_prefab_cache_stats
1786 SET object_version_number = p_object_version_number,
1787 last_updated_by = G_USER_ID,
1788 last_update_date = SYSDATE,
1789 last_update_login = G_LOGIN_ID,
1790 -- wsh_po_id = p_wsh_po_id,
1791 -- ca_comp_id = p_ca_comp_id,
1792 -- jvm_id = p_jvm_id,
1793 num_cache_miss = num_cache_miss + p_num_cache_miss,
1794 num_cache_hit = num_cache_hit + p_num_cache_hit,
1795 num_loader_miss = num_loader_miss + p_num_loader_miss,
1796 num_invalidate_call = num_invalidate_call + p_num_invalidate_call,
1797 num_invalidations = num_invalidations + p_num_invalidations,
1798 num_objects = GREATEST(num_objects, p_num_objects),
1799 expiration_time = p_expiration_time,
1800 -- security_group_id = p_security_group_id,
1801 end_time = p_end_time
1802 WHERE
1803 (cache_stat_id = p_cache_stat_id)
1804 or (jvm_id = 0 and wsh_po_id = p_wsh_po_id and ca_comp_id = p_ca_comp_id);
1805
1806
1807 -----------------------
1808 -- end of real logic --
1809
1810 -- Standard check of p_commit.
1811 IF (FND_API.To_Boolean(p_commit)) THEN
1812 COMMIT WORK;
1813 END IF;
1814
1815 -- Standard call to get message count and if count is 1, get message info.
1816 FND_MSG_PUB.Count_And_Get(
1817 p_count => x_msg_count,
1818 p_data => x_msg_data );
1819
1820 EXCEPTION
1821 WHEN FND_API.G_EXC_ERROR THEN
1822 ROLLBACK TO UPDATE_CACHE_STAT;
1823 x_return_status := FND_API.G_RET_STS_ERROR ;
1824
1825 FND_MSG_PUB.Count_And_Get(
1826 p_count => x_msg_count,
1827 p_data => x_msg_data );
1828
1829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1830 ROLLBACK TO UPDATE_CACHE_STAT;
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1832
1833 FND_MSG_PUB.Count_And_Get(
1834 p_count => x_msg_count,
1835 p_data => x_msg_data );
1836
1837 WHEN OTHERS THEN
1838 ROLLBACK TO UPDATE_CACHE_STAT;
1839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1840
1841 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1842 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1843 END IF;
1844
1845 FND_MSG_PUB.Count_And_Get(
1846 p_count => x_msg_count,
1847 p_data => x_msg_data );
1848
1849 END UPDATE_CACHE_STAT;
1850
1851 procedure DELETE_CACHE_STAT(
1852 p_api_version IN NUMBER,
1853 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1854 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1855
1856 x_return_status OUT NOCOPY VARCHAR2,
1857 x_msg_count OUT NOCOPY NUMBER,
1858 x_msg_data OUT NOCOPY VARCHAR2
1859 ) AS
1860 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CACHE_STAT';
1861 l_api_version CONSTANT NUMBER := p_api_version;
1862
1863 BEGIN
1864 -- Standard Start of API savepoint
1865 SAVEPOINT DELETE_CACHE_STAT;
1866
1867 -- Standard call to check for call compatibility.
1868 IF NOT FND_API.Compatible_API_Call (
1869 l_api_version,
1870 p_api_version,
1871 l_api_name,
1872 G_PKG_NAME)
1873 THEN
1874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1875 END IF;
1876
1877 -- Initialize message list if p_init_msg_list is set to TRUE.
1878 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1879 FND_MSG_PUB.initialize;
1880 END IF;
1881
1882 -- Initialize API return status to success
1883 x_return_status := FND_API.G_RET_STS_SUCCESS;
1884
1885 -- real logic --
1886 ----------------
1887
1888 UPDATE jtf_prefab_cache_stats
1889 SET end_time = GREATEST(expiration_time, sysdate), num_objects = 0
1890 WHERE expiration_time < sysdate and
1891 end_time < start_time;
1892
1893 -----------------------
1894 -- end of real logic --
1895
1896 -- Standard check of p_commit.
1897 IF (FND_API.To_Boolean(p_commit)) THEN
1898 COMMIT WORK;
1899 END IF;
1900
1901 -- Standard call to get message count and if count is 1, get message info.
1902 FND_MSG_PUB.Count_And_Get(
1903 p_count => x_msg_count,
1904 p_data => x_msg_data );
1905
1906 EXCEPTION
1907 WHEN FND_API.G_EXC_ERROR THEN
1908 ROLLBACK TO DELETE_CACHE_STAT;
1909 x_return_status := FND_API.G_RET_STS_ERROR ;
1910
1911 FND_MSG_PUB.Count_And_Get(
1912 p_count => x_msg_count,
1913 p_data => x_msg_data );
1914
1915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916 ROLLBACK TO DELETE_CACHE_STAT;
1917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1918
1919 FND_MSG_PUB.Count_And_Get(
1920 p_count => x_msg_count,
1921 p_data => x_msg_data );
1922
1923 WHEN OTHERS THEN
1924 ROLLBACK TO DELETE_CACHE_STAT;
1925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1926
1927 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1928 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1929 END IF;
1930
1931 FND_MSG_PUB.Count_And_Get(
1932 p_count => x_msg_count,
1933 p_data => x_msg_data );
1934
1935 END DELETE_CACHE_STAT;
1936
1937
1938 procedure RESET_CACHE_STAT(
1939 p_api_version IN NUMBER,
1940 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1941 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1942 p_wsh_po_id IN NUMBER,
1943 p_ca_comp_id IN NUMBER,
1944
1945 x_return_status OUT NOCOPY VARCHAR2,
1946 x_msg_count OUT NOCOPY NUMBER,
1947 x_msg_data OUT NOCOPY VARCHAR2
1948 ) AS
1949 l_api_name CONSTANT VARCHAR2(30) := 'RESET_CACHE_STAT';
1950 l_api_version CONSTANT NUMBER := p_api_version;
1951
1952 BEGIN
1953 -- Standard Start of API savepoint
1954 SAVEPOINT RESET_CACHE_STAT;
1955
1956 -- Standard call to check for call compatibility.
1957 IF NOT FND_API.Compatible_API_Call (
1958 l_api_version,
1959 p_api_version,
1960 l_api_name,
1961 G_PKG_NAME)
1962 THEN
1963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1964 END IF;
1965
1966 -- Initialize message list if p_init_msg_list is set to TRUE.
1967 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1968 FND_MSG_PUB.initialize;
1969 END IF;
1970
1971 -- Initialize API return status to success
1972 x_return_status := FND_API.G_RET_STS_SUCCESS;
1973
1974 -- real logic --
1975 ----------------
1976
1977 DELETE FROM jtf_prefab_cache_stats
1978 WHERE wsh_po_id = p_wsh_po_id and
1979 ca_comp_id = p_ca_comp_id and
1980 expiration_time < sysdate;
1981
1982 UPDATE jtf_prefab_cache_stats
1983 SET num_cache_miss = 0, num_cache_hit = 0, num_loader_miss = 0,
1984 num_invalidate_call = 0, num_invalidations = 0, num_objects = 0,
1985 start_time = sysdate
1986 WHERE wsh_po_id = p_wsh_po_id and
1987 ca_comp_id = p_ca_comp_id;
1988
1989 -----------------------
1990 -- end of real logic --
1991
1992 -- Standard check of p_commit.
1993 IF (FND_API.To_Boolean(p_commit)) THEN
1994 COMMIT WORK;
1995 END IF;
1996
1997 -- Standard call to get message count and if count is 1, get message info.
1998 FND_MSG_PUB.Count_And_Get(
1999 p_count => x_msg_count,
2000 p_data => x_msg_data );
2001
2002 EXCEPTION
2003 WHEN FND_API.G_EXC_ERROR THEN
2004 ROLLBACK TO RESET_CACHE_STAT;
2005 x_return_status := FND_API.G_RET_STS_ERROR ;
2006
2007 FND_MSG_PUB.Count_And_Get(
2008 p_count => x_msg_count,
2009 p_data => x_msg_data );
2010
2011 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2012 ROLLBACK TO RESET_CACHE_STAT;
2013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2014
2015 FND_MSG_PUB.Count_And_Get(
2016 p_count => x_msg_count,
2017 p_data => x_msg_data );
2018
2019 WHEN OTHERS THEN
2020 ROLLBACK TO RESET_CACHE_STAT;
2021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2022
2023 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2024 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2025 END IF;
2026
2027 FND_MSG_PUB.Count_And_Get(
2028 p_count => x_msg_count,
2029 p_data => x_msg_data );
2030
2031 END RESET_CACHE_STAT;
2032
2033
2034 PROCEDURE INSERT_CA_FILTER(
2035 p_api_version IN NUMBER,
2036 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2037 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2038
2039 p_ca_filter_id OUT NOCOPY jtf_prefab_ca_filters_b.ca_filter_id%TYPE,
2040 p_application_id IN jtf_prefab_ca_filters_b.application_id%TYPE,
2041 p_ca_filter_name IN jtf_prefab_ca_filters_b.ca_filter_name%TYPE,
2042 p_description IN jtf_prefab_ca_filters_tl.description%TYPE,
2043
2044 p_object_version_number OUT NOCOPY jtf_prefab_ca_filters_b.object_version_number%TYPE,
2045
2046 x_return_status OUT NOCOPY VARCHAR2,
2047 x_msg_count OUT NOCOPY NUMBER,
2048 x_msg_data OUT NOCOPY VARCHAR2
2049 ) AS
2050 -- local variables --
2051 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CA_FILTER';
2052 l_api_version NUMBER := p_api_version;
2053 l_row_id VARCHAR2(255) := NULL;
2054
2055 CURSOR sequence_cursor IS
2056 SELECT jtf_prefab_ca_filters_b_s.NEXTVAL from dual;
2057 CURSOR host_apps_cursor IS
2058 SELECT host_app_id
2059 FROM jtf_prefab_host_apps
2060 WHERE application_id = p_application_id;
2061 l_host_app_id NUMBER;
2062 l_ha_filter_id NUMBER;
2063 BEGIN
2064 -- Standard Start of API savepoint
2065 SAVEPOINT INSERT_CA_FILTER;
2066
2067 -- Standard call to check for call compatibility.
2068 IF NOT FND_API.Compatible_API_Call (
2069 l_api_version,
2070 p_api_version,
2071 l_api_name,
2072 G_PKG_NAME)
2073 THEN
2074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2075 END IF;
2076
2077 -- Initialize message list if p_init_msg_list is set to TRUE.
2078 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2079 FND_MSG_PUB.initialize;
2080 END IF;
2081
2082 -- Initialize API return status to success
2083 x_return_status := FND_API.G_RET_STS_SUCCESS;
2084
2085 -- real logic --
2086 ----------------
2087
2088 OPEN sequence_cursor;
2089 FETCH sequence_cursor INTO p_ca_filter_id;
2090 CLOSE sequence_cursor;
2091 p_object_version_number := 1;
2092
2093 JTF_PREFAB_CA_FILTERS_PKG.INSERT_ROW(p_ca_filter_id,
2094 p_ca_filter_name,
2095 p_application_id,
2096 NULL,
2097 p_object_version_number,
2098 p_description,
2099 SYSDATE,
2100 G_USER_ID,
2101 SYSDATE,
2102 G_USER_ID,
2103 G_LOGIN_ID);
2104
2105 -- for each application/host pair that the filter belongs to,
2106 -- add a row to ha_filters
2107
2108 OPEN host_apps_cursor;
2109 FETCH host_apps_cursor INTO l_host_app_id;
2110
2111 WHILE host_apps_cursor%FOUND LOOP
2112 JTF_PREFAB_CACHE_PUB.INSERT_HA_FILTER(p_api_version,
2113 p_init_msg_list,
2114 p_commit,
2115 l_ha_filter_id,
2116 l_host_app_id,
2117 p_ca_filter_id,
2118 't',
2119 p_object_version_number,
2120 x_return_status,
2121 x_msg_count,
2122 x_msg_data);
2123 FETCH host_apps_cursor INTO l_host_app_id;
2124 END LOOP;
2125
2126 CLOSE host_apps_cursor;
2127
2128 -----------------------
2129 -- end of real logic --
2130
2131 -- Standard check of p_commit.
2132 IF (FND_API.To_Boolean(p_commit)) THEN
2133 COMMIT WORK;
2134 END IF;
2135
2136 -- Standard call to get message count and if count is 1, get message info.
2137 FND_MSG_PUB.Count_And_Get(
2138 p_count => x_msg_count,
2139 p_data => x_msg_data );
2140
2141 EXCEPTION
2142 WHEN FND_API.G_EXC_ERROR THEN
2143 ROLLBACK TO INSERT_CA_FILTER;
2144 x_return_status := FND_API.G_RET_STS_ERROR ;
2145
2146 FND_MSG_PUB.Count_And_Get(
2147 p_count => x_msg_count,
2148 p_data => x_msg_data );
2149
2150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2151 ROLLBACK TO INSERT_CA_FILTER;
2152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2153
2154 FND_MSG_PUB.Count_And_Get(
2155 p_count => x_msg_count,
2156 p_data => x_msg_data );
2157
2158 WHEN OTHERS THEN
2159 ROLLBACK TO INSERT_CA_FILTER;
2160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2161
2162 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2163 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2164 END IF;
2165
2166 FND_MSG_PUB.Count_And_Get(
2167 p_count => x_msg_count,
2168 p_data => x_msg_data );
2169
2170 END INSERT_CA_FILTER;
2171
2172 PROCEDURE UPDATE_CA_FILTER(
2173 p_api_version IN NUMBER,
2174 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2175 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2176
2177 p_ca_filter_id IN jtf_prefab_ca_filters_b.ca_filter_id%TYPE,
2178 p_application_id IN jtf_prefab_ca_filters_b.application_id%TYPE,
2179 p_ca_filter_name IN jtf_prefab_ca_filters_b.ca_filter_name%TYPE,
2180 p_description IN jtf_prefab_ca_filters_tl.description%TYPE,
2181
2182 p_object_version_number IN OUT NOCOPY jtf_prefab_ca_filters_b.object_version_number%TYPE,
2183
2184 x_return_status OUT NOCOPY VARCHAR2,
2185 x_msg_count OUT NOCOPY NUMBER,
2186 x_msg_data OUT NOCOPY VARCHAR2
2187 ) AS
2188 -- local variables --
2189 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CA_FILTER';
2190 l_api_version NUMBER := p_api_version;
2191
2192 BEGIN
2193 -- Standard Start of API savepoint
2194 SAVEPOINT UPDATE_CA_FILTER;
2195
2196 -- Standard call to check for call compatibility.
2197 IF NOT FND_API.Compatible_API_Call (
2198 l_api_version,
2199 p_api_version,
2200 l_api_name,
2201 G_PKG_NAME)
2202 THEN
2203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204 END IF;
2205
2206 -- Initialize message list if p_init_msg_list is set to TRUE.
2207 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2208 FND_MSG_PUB.initialize;
2209 END IF;
2210
2211 -- Initialize API return status to success
2212 x_return_status := FND_API.G_RET_STS_SUCCESS;
2213
2214 -- real logic --
2215 ----------------
2216
2217 JTF_PREFAB_CA_FILTERS_PKG.UPDATE_ROW(p_ca_filter_id,
2218 p_ca_filter_name,
2219 p_application_id,
2220 NULL,
2221 p_object_version_number,
2222 p_description,
2223 SYSDATE,
2224 G_USER_ID,
2225 G_LOGIN_ID);
2226
2227 -----------------------
2228 -- end of real logic --
2229
2230 -- Standard check of p_commit.
2231 IF (FND_API.To_Boolean(p_commit)) THEN
2232 COMMIT WORK;
2233 END IF;
2234
2235 -- Standard call to get message count and if count is 1, get message info.
2236 FND_MSG_PUB.Count_And_Get(
2237 p_count => x_msg_count,
2238 p_data => x_msg_data );
2239
2240 EXCEPTION
2241 WHEN FND_API.G_EXC_ERROR THEN
2242 ROLLBACK TO UPDATE_CA_FILTER;
2243 x_return_status := FND_API.G_RET_STS_ERROR ;
2244
2245 FND_MSG_PUB.Count_And_Get(
2246 p_count => x_msg_count,
2247 p_data => x_msg_data );
2248
2249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2250 ROLLBACK TO UPDATE_CA_FILTER;
2251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2252
2253 FND_MSG_PUB.Count_And_Get(
2254 p_count => x_msg_count,
2255 p_data => x_msg_data );
2256
2257 WHEN OTHERS THEN
2258 ROLLBACK TO UPDATE_CA_FILTER;
2259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2260
2261 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2262 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2263 END IF;
2264
2265 FND_MSG_PUB.Count_And_Get(
2266 p_count => x_msg_count,
2267 p_data => x_msg_data );
2268
2269 END UPDATE_CA_FILTER;
2270
2271 procedure DELETE_CA_FILTER(
2272 p_api_version IN NUMBER,
2273 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2274 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2275
2276 p_ca_filter_id IN jtf_prefab_ca_filters_b.ca_filter_id%TYPE,
2277
2278 p_object_version_number IN jtf_prefab_ca_filters_b.object_version_number%TYPE,
2279
2280 x_return_status OUT NOCOPY VARCHAR2,
2281 x_msg_count OUT NOCOPY NUMBER,
2282 x_msg_data OUT NOCOPY VARCHAR2
2283 ) AS
2284 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CA_FILTER';
2285 l_api_version CONSTANT NUMBER := p_api_version;
2286
2287 BEGIN
2288 -- Standard Start of API savepoint
2289 SAVEPOINT DELETE_CA_FILTER;
2290
2291 -- Standard call to check for call compatibility.
2292 IF NOT FND_API.Compatible_API_Call (
2293 l_api_version,
2294 p_api_version,
2295 l_api_name,
2296 G_PKG_NAME)
2297 THEN
2298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2299 END IF;
2300
2301 -- Initialize message list if p_init_msg_list is set to TRUE.
2302 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2303 FND_MSG_PUB.initialize;
2304 END IF;
2305
2306 -- Initialize API return status to success
2307 x_return_status := FND_API.G_RET_STS_SUCCESS;
2308
2309 -- real logic --
2310 ----------------
2311
2312 JTF_PREFAB_CA_FILTERS_PKG.DELETE_ROW(p_ca_filter_id);
2313
2314 JTF_PREFAB_CACHE_PUB.DELETE_HA_FILTERS_F_CA_FILTER(p_api_version,
2315 p_init_msg_list,
2316 p_commit,
2317 p_ca_filter_id,
2318 p_object_version_number,
2319 x_return_status,
2320 x_msg_count,
2321 x_msg_data);
2322 JTF_PREFAB_CACHE_PUB.DELETE_CA_FL_RESP(p_api_version,
2323 p_init_msg_list,
2324 p_commit,
2325 p_ca_filter_id,
2326 p_object_version_number,
2327 x_return_status,
2328 x_msg_count,
2329 x_msg_data);
2330 JTF_PREFAB_CACHE_PUB.DELETE_CA_FL_LANG(p_api_version,
2331 p_init_msg_list,
2332 p_commit,
2333 p_ca_filter_id,
2334 p_object_version_number,
2335 x_return_status,
2336 x_msg_count,
2337 x_msg_data);
2338
2339 -----------------------
2340 -- end of real logic --
2341
2342 -- Standard check of p_commit.
2343 IF (FND_API.To_Boolean(p_commit)) THEN
2344 COMMIT WORK;
2345 END IF;
2346
2347 -- Standard call to get message count and if count is 1, get message info.
2348 FND_MSG_PUB.Count_And_Get(
2349 p_count => x_msg_count,
2350 p_data => x_msg_data );
2351
2352 EXCEPTION
2353 WHEN FND_API.G_EXC_ERROR THEN
2354 ROLLBACK TO DELETE_CA_FILTER;
2355 x_return_status := FND_API.G_RET_STS_ERROR ;
2356
2357 FND_MSG_PUB.Count_And_Get(
2358 p_count => x_msg_count,
2359 p_data => x_msg_data );
2360
2361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2362 ROLLBACK TO DELETE_CA_FILTER;
2363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2364
2365 FND_MSG_PUB.Count_And_Get(
2366 p_count => x_msg_count,
2367 p_data => x_msg_data );
2368
2369 WHEN OTHERS THEN
2370 ROLLBACK TO DELETE_CA_FILTER;
2371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2372
2373 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2374 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2375 END IF;
2376
2377 FND_MSG_PUB.Count_And_Get(
2378 p_count => x_msg_count,
2379 p_data => x_msg_data );
2380
2381 END DELETE_CA_FILTER;
2382
2383 PROCEDURE INSERT_HA_FILTER(
2384 p_api_version IN NUMBER,
2385 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2386 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2387
2388 p_ha_filter_id OUT NOCOPY jtf_prefab_ha_filters.ha_filter_id%TYPE,
2389 p_host_app_id IN jtf_prefab_ha_filters.host_app_id%TYPE,
2390 p_ca_filter_id IN jtf_prefab_ha_filters.ca_filter_id%TYPE,
2391 p_cache_filter_enabled_flag IN jtf_prefab_ha_filters.cache_filter_enabled_flag%TYPE,
2392
2393 p_object_version_number OUT NOCOPY jtf_prefab_ha_filters.object_version_number%TYPE,
2394
2395 x_return_status OUT NOCOPY VARCHAR2,
2396 x_msg_count OUT NOCOPY NUMBER,
2397 x_msg_data OUT NOCOPY VARCHAR2
2398 ) AS
2399 -- local variables --
2400 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_HA_FILTER';
2401 l_api_version NUMBER := p_api_version;
2402
2403 CURSOR sequence_cursor IS
2404 SELECT jtf_prefab_ha_filters_s.NEXTVAL from dual;
2405 BEGIN
2406 -- Standard Start of API savepoint
2407 SAVEPOINT INSERT_HA_FILTER;
2408
2409 -- Standard call to check for call compatibility.
2410 IF NOT FND_API.Compatible_API_Call (
2411 l_api_version,
2412 p_api_version,
2413 l_api_name,
2414 G_PKG_NAME)
2415 THEN
2416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2417 END IF;
2418
2419 -- Initialize message list if p_init_msg_list is set to TRUE.
2420 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2421 FND_MSG_PUB.initialize;
2422 END IF;
2423
2424 -- Initialize API return status to success
2425 x_return_status := FND_API.G_RET_STS_SUCCESS;
2426
2427 -- real logic --
2428 ----------------
2429
2430 OPEN sequence_cursor;
2431 FETCH sequence_cursor INTO p_ha_filter_id;
2432 CLOSE sequence_cursor;
2433 p_object_version_number := 1;
2434
2435 INSERT INTO jtf_prefab_ha_filters (ha_filter_id,
2436 object_version_number,
2437 created_by,
2438 creation_date,
2439 last_updated_by,
2440 last_update_date,
2441 last_update_login,
2442 -- security_group_id,
2443 host_app_id,
2444 ca_filter_id,
2445 cache_filter_enabled_flag)
2446 VALUES (p_ha_filter_id,
2447 p_object_version_number,
2448 G_USER_ID,
2449 SYSDATE,
2450 G_USER_ID,
2451 SYSDATE,
2452 G_LOGIN_ID,
2453 -- NULL,
2454 p_host_app_id,
2455 p_ca_filter_id,
2456 p_cache_filter_enabled_flag);
2457
2458 -----------------------
2459 -- end of real logic --
2460
2461 -- Standard check of p_commit.
2462 IF (FND_API.To_Boolean(p_commit)) THEN
2463 COMMIT WORK;
2464 END IF;
2465
2466 -- Standard call to get message count and if count is 1, get message info.
2467 FND_MSG_PUB.Count_And_Get(
2468 p_count => x_msg_count,
2469 p_data => x_msg_data );
2470
2471 EXCEPTION
2472 WHEN FND_API.G_EXC_ERROR THEN
2473 ROLLBACK TO INSERT_HA_FILTER;
2474 x_return_status := FND_API.G_RET_STS_ERROR ;
2475
2476 FND_MSG_PUB.Count_And_Get(
2477 p_count => x_msg_count,
2478 p_data => x_msg_data );
2479
2480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2481 ROLLBACK TO INSERT_HA_FILTER;
2482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2483
2484 FND_MSG_PUB.Count_And_Get(
2485 p_count => x_msg_count,
2486 p_data => x_msg_data );
2487
2488 WHEN OTHERS THEN
2489 ROLLBACK TO INSERT_HA_FILTER;
2490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2491
2492 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2493 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2494 END IF;
2495
2496 FND_MSG_PUB.Count_And_Get(
2497 p_count => x_msg_count,
2498 p_data => x_msg_data );
2499
2500 END INSERT_HA_FILTER;
2501
2502 PROCEDURE UPDATE_HA_FILTER(
2503 p_api_version IN NUMBER,
2504 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2505 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2506
2507 p_ha_filter_id IN jtf_prefab_ha_filters.ha_filter_id%TYPE,
2508 p_cache_filter_enabled_flag IN jtf_prefab_ha_filters.cache_filter_enabled_flag%TYPE,
2509
2510 p_object_version_number IN OUT NOCOPY jtf_prefab_ha_filters.object_version_number%TYPE,
2511
2512 x_return_status OUT NOCOPY VARCHAR2,
2513 x_msg_count OUT NOCOPY NUMBER,
2514 x_msg_data OUT NOCOPY VARCHAR2
2515 ) AS
2516 -- local variables --
2517 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HA_FILTER';
2518 l_api_version NUMBER := p_api_version;
2519
2520 BEGIN
2521 -- Standard Start of API savepoint
2522 SAVEPOINT UPDATE_HA_FILTER;
2523
2524 -- Standard call to check for call compatibility.
2525 IF NOT FND_API.Compatible_API_Call (
2526 l_api_version,
2527 p_api_version,
2528 l_api_name,
2529 G_PKG_NAME)
2530 THEN
2531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2532 END IF;
2533
2534 -- Initialize message list if p_init_msg_list is set to TRUE.
2535 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2536 FND_MSG_PUB.initialize;
2537 END IF;
2538
2539 -- Initialize API return status to success
2540 x_return_status := FND_API.G_RET_STS_SUCCESS;
2541
2542 -- real logic --
2543 ----------------
2544
2545 UPDATE jtf_prefab_ha_filters
2546 SET object_version_number = p_object_version_number,
2547 last_updated_by = G_USER_ID,
2548 last_update_date = SYSDATE,
2549 last_update_login = G_LOGIN_ID,
2550 cache_filter_enabled_flag = p_cache_filter_enabled_flag
2551 WHERE ha_filter_id = p_ha_filter_id;
2552
2553 -----------------------
2554 -- end of real logic --
2555
2556 -- Standard check of p_commit.
2557 IF (FND_API.To_Boolean(p_commit)) THEN
2558 COMMIT WORK;
2559 END IF;
2560
2561 -- Standard call to get message count and if count is 1, get message info.
2562 FND_MSG_PUB.Count_And_Get(
2563 p_count => x_msg_count,
2564 p_data => x_msg_data );
2565
2566 EXCEPTION
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 ROLLBACK TO UPDATE_HA_FILTER;
2569 x_return_status := FND_API.G_RET_STS_ERROR ;
2570
2571 FND_MSG_PUB.Count_And_Get(
2572 p_count => x_msg_count,
2573 p_data => x_msg_data );
2574
2575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2576 ROLLBACK TO UPDATE_HA_FILTER;
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2578
2579 FND_MSG_PUB.Count_And_Get(
2580 p_count => x_msg_count,
2581 p_data => x_msg_data );
2582
2583 WHEN OTHERS THEN
2584 ROLLBACK TO UPDATE_HA_FILTER;
2585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2586
2587 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2588 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2589 END IF;
2590
2591 FND_MSG_PUB.Count_And_Get(
2592 p_count => x_msg_count,
2593 p_data => x_msg_data );
2594
2595 END UPDATE_HA_FILTER;
2596
2597 procedure DELETE_HA_FILTERS_F_HOST_APP(
2598 p_api_version IN NUMBER,
2599 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2600 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2601
2602 p_host_app_id IN jtf_prefab_ha_filters.host_app_id%TYPE,
2603
2604 p_object_version_number IN jtf_prefab_ha_filters.object_version_number%TYPE,
2605
2606 x_return_status OUT NOCOPY VARCHAR2,
2607 x_msg_count OUT NOCOPY NUMBER,
2608 x_msg_data OUT NOCOPY VARCHAR2
2609 ) AS
2610 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HA_FILTERS_F_HOST_APP';
2611 l_api_version CONSTANT NUMBER := p_api_version;
2612
2613 BEGIN
2614 -- Standard Start of API savepoint
2615 SAVEPOINT DELETE_HA_FILTERS_F_HOST_APP;
2616
2617 -- Standard call to check for call compatibility.
2618 IF NOT FND_API.Compatible_API_Call (
2619 l_api_version,
2620 p_api_version,
2621 l_api_name,
2622 G_PKG_NAME)
2623 THEN
2624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2625 END IF;
2626
2627 -- Initialize message list if p_init_msg_list is set to TRUE.
2628 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2629 FND_MSG_PUB.initialize;
2630 END IF;
2631
2632 -- Initialize API return status to success
2633 x_return_status := FND_API.G_RET_STS_SUCCESS;
2634
2635 -- real logic --
2636 ----------------
2637
2638 DELETE FROM jtf_prefab_ha_filters
2639 WHERE host_app_id = p_host_app_id;
2640
2641 -----------------------
2642 -- end of real logic --
2643
2644 -- Standard check of p_commit.
2645 IF (FND_API.To_Boolean(p_commit)) THEN
2646 COMMIT WORK;
2647 END IF;
2648
2649 -- Standard call to get message count and if count is 1, get message info.
2650 FND_MSG_PUB.Count_And_Get(
2651 p_count => x_msg_count,
2652 p_data => x_msg_data );
2653
2654 EXCEPTION
2655 WHEN FND_API.G_EXC_ERROR THEN
2656 ROLLBACK TO DELETE_HA_FILTERS_F_HOST_APP;
2657 x_return_status := FND_API.G_RET_STS_ERROR ;
2658
2659 FND_MSG_PUB.Count_And_Get(
2660 p_count => x_msg_count,
2661 p_data => x_msg_data );
2662
2663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2664 ROLLBACK TO DELETE_HA_FILTERS_F_HOST_APP;
2665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2666
2667 FND_MSG_PUB.Count_And_Get(
2668 p_count => x_msg_count,
2669 p_data => x_msg_data );
2670
2671 WHEN OTHERS THEN
2672 ROLLBACK TO DELETE_HA_FILTERS_F_HOST_APP;
2673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2674
2675 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2676 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2677 END IF;
2678
2679 FND_MSG_PUB.Count_And_Get(
2680 p_count => x_msg_count,
2681 p_data => x_msg_data );
2682
2683 END DELETE_HA_FILTERS_F_HOST_APP;
2684
2685 procedure DELETE_HA_FILTERS_F_CA_FILTER(
2686 p_api_version IN NUMBER,
2687 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2688 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2689
2690 p_ca_filter_id IN jtf_prefab_ha_filters.ca_filter_id%TYPE,
2691
2692 p_object_version_number IN jtf_prefab_ha_filters.object_version_number%TYPE,
2693
2694 x_return_status OUT NOCOPY VARCHAR2,
2695 x_msg_count OUT NOCOPY NUMBER,
2696 x_msg_data OUT NOCOPY VARCHAR2
2697 ) AS
2698 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HA_FILTERS_F_CA_FILTER';
2699 l_api_version CONSTANT NUMBER := p_api_version;
2700
2701 BEGIN
2702 -- Standard Start of API savepoint
2703 SAVEPOINT DELETE_HA_FILTERS_F_CA_FILTER;
2704
2705 -- Standard call to check for call compatibility.
2706 IF NOT FND_API.Compatible_API_Call (
2707 l_api_version,
2708 p_api_version,
2709 l_api_name,
2710 G_PKG_NAME)
2711 THEN
2712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2713 END IF;
2714
2715 -- Initialize message list if p_init_msg_list is set to TRUE.
2716 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2717 FND_MSG_PUB.initialize;
2718 END IF;
2719
2720 -- Initialize API return status to success
2721 x_return_status := FND_API.G_RET_STS_SUCCESS;
2722
2723 -- real logic --
2724 ----------------
2725
2726 DELETE FROM jtf_prefab_ha_filters
2727 WHERE ca_filter_id = p_ca_filter_id;
2728
2729 -----------------------
2730 -- end of real logic --
2731
2732 -- Standard check of p_commit.
2733 IF (FND_API.To_Boolean(p_commit)) THEN
2734 COMMIT WORK;
2735 END IF;
2736
2737 -- Standard call to get message count and if count is 1, get message info.
2738 FND_MSG_PUB.Count_And_Get(
2739 p_count => x_msg_count,
2740 p_data => x_msg_data );
2741
2742 EXCEPTION
2743 WHEN FND_API.G_EXC_ERROR THEN
2744 ROLLBACK TO DELETE_HA_FILTERS_F_CA_FILTER;
2745 x_return_status := FND_API.G_RET_STS_ERROR ;
2746
2747 FND_MSG_PUB.Count_And_Get(
2748 p_count => x_msg_count,
2749 p_data => x_msg_data );
2750
2751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2752 ROLLBACK TO DELETE_HA_FILTERS_F_CA_FILTER;
2753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2754
2755 FND_MSG_PUB.Count_And_Get(
2756 p_count => x_msg_count,
2757 p_data => x_msg_data );
2758
2759 WHEN OTHERS THEN
2760 ROLLBACK TO DELETE_HA_FILTERS_F_CA_FILTER;
2761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2762
2763 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2764 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2765 END IF;
2766
2767 FND_MSG_PUB.Count_And_Get(
2768 p_count => x_msg_count,
2769 p_data => x_msg_data );
2770
2771 END DELETE_HA_FILTERS_F_CA_FILTER;
2772
2773 PROCEDURE INSERT_CA_FL_RESP(
2774 p_api_version IN NUMBER,
2775 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2776 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2777
2778 p_ca_fl_resp_id OUT NOCOPY jtf_prefab_ca_fl_resps.ca_fl_resp_id%TYPE,
2779 p_ca_filter_id IN jtf_prefab_ca_fl_resps.ca_filter_id%TYPE,
2780 p_responsibility_id IN jtf_prefab_ca_fl_resps.responsibility_id%TYPE,
2781
2782 p_object_version_number OUT NOCOPY jtf_prefab_ca_fl_resps.object_version_number%TYPE,
2783
2784 x_return_status OUT NOCOPY VARCHAR2,
2785 x_msg_count OUT NOCOPY NUMBER,
2786 x_msg_data OUT NOCOPY VARCHAR2
2787 ) AS
2788 -- local variables --
2789 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CA_FL_RESP';
2790 l_api_version NUMBER := p_api_version;
2791 l_row_id VARCHAR2(255) := NULL;
2792
2793 CURSOR sequence_cursor IS
2794 SELECT jtf_prefab_ca_fl_resps_s.NEXTVAL from dual;
2795 BEGIN
2796 -- Standard Start of API savepoint
2797 SAVEPOINT INSERT_CA_FL_RESP;
2798
2799 -- Standard call to check for call compatibility.
2800 IF NOT FND_API.Compatible_API_Call (
2801 l_api_version,
2802 p_api_version,
2803 l_api_name,
2804 G_PKG_NAME)
2805 THEN
2806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2807 END IF;
2808
2809 -- Initialize message list if p_init_msg_list is set to TRUE.
2810 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2811 FND_MSG_PUB.initialize;
2812 END IF;
2813
2814 -- Initialize API return status to success
2815 x_return_status := FND_API.G_RET_STS_SUCCESS;
2816
2817 -- real logic --
2818 ----------------
2819
2820 OPEN sequence_cursor;
2821 FETCH sequence_cursor INTO p_ca_fl_resp_id;
2822 CLOSE sequence_cursor;
2823 p_object_version_number := 1;
2824
2825 INSERT INTO jtf_prefab_ca_fl_resps(ca_fl_resp_id,
2826 object_version_number,
2827 created_by,
2828 creation_date,
2829 last_updated_by,
2830 last_update_date,
2831 last_update_login,
2832 -- security_group_id,
2833 ca_filter_id,
2834 responsibility_id)
2835 VALUES (p_ca_fl_resp_id,
2836 p_object_version_number,
2837 G_USER_ID,
2838 SYSDATE,
2839 G_USER_ID,
2840 SYSDATE,
2841 G_LOGIN_ID,
2842 -- NULL,
2843 p_ca_filter_id,
2844 p_responsibility_id);
2845
2846 -----------------------
2847 -- end of real logic --
2848
2849 -- Standard check of p_commit.
2850 IF (FND_API.To_Boolean(p_commit)) THEN
2851 COMMIT WORK;
2852 END IF;
2853
2854 -- Standard call to get message count and if count is 1, get message info.
2855 FND_MSG_PUB.Count_And_Get(
2856 p_count => x_msg_count,
2857 p_data => x_msg_data );
2858
2859 EXCEPTION
2860 WHEN FND_API.G_EXC_ERROR THEN
2861 ROLLBACK TO INSERT_CA_FL_RESP;
2862 x_return_status := FND_API.G_RET_STS_ERROR ;
2863
2864 FND_MSG_PUB.Count_And_Get(
2865 p_count => x_msg_count,
2866 p_data => x_msg_data );
2867
2868 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2869 ROLLBACK TO INSERT_CA_FL_RESP;
2870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2871
2872 FND_MSG_PUB.Count_And_Get(
2873 p_count => x_msg_count,
2874 p_data => x_msg_data );
2875
2876 WHEN OTHERS THEN
2877 ROLLBACK TO INSERT_CA_FL_RESP;
2878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2879
2880 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2881 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2882 END IF;
2883
2884 FND_MSG_PUB.Count_And_Get(
2885 p_count => x_msg_count,
2886 p_data => x_msg_data );
2887
2888 END INSERT_CA_FL_RESP;
2889
2890 procedure DELETE_CA_FL_RESP(
2891 p_api_version IN NUMBER,
2892 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2893 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2894
2895 p_ca_filter_id IN jtf_prefab_ca_fl_resps.ca_filter_id%TYPE,
2896
2897 p_object_version_number IN jtf_prefab_ca_fl_resps.object_version_number%TYPE,
2898
2899 x_return_status OUT NOCOPY VARCHAR2,
2900 x_msg_count OUT NOCOPY NUMBER,
2901 x_msg_data OUT NOCOPY VARCHAR2
2902 ) AS
2903 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CA_FL_RESP';
2904 l_api_version CONSTANT NUMBER := p_api_version;
2905
2906 BEGIN
2907 -- Standard Start of API savepoint
2908 SAVEPOINT DELETE_CA_FL_RESP;
2909
2910 -- Standard call to check for call compatibility.
2911 IF NOT FND_API.Compatible_API_Call (
2912 l_api_version,
2913 p_api_version,
2914 l_api_name,
2915 G_PKG_NAME)
2916 THEN
2917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2918 END IF;
2919
2920 -- Initialize message list if p_init_msg_list is set to TRUE.
2921 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2922 FND_MSG_PUB.initialize;
2923 END IF;
2924
2925 -- Initialize API return status to success
2926 x_return_status := FND_API.G_RET_STS_SUCCESS;
2927
2928 -- real logic --
2929 ----------------
2930
2931 DELETE FROM jtf_prefab_ca_fl_resps
2932 WHERE ca_filter_id = p_ca_filter_id;
2933
2934 -----------------------
2935 -- end of real logic --
2936
2937 -- Standard check of p_commit.
2938 IF (FND_API.To_Boolean(p_commit)) THEN
2939 COMMIT WORK;
2940 END IF;
2941
2942 -- Standard call to get message count and if count is 1, get message info.
2943 FND_MSG_PUB.Count_And_Get(
2944 p_count => x_msg_count,
2945 p_data => x_msg_data );
2946
2947 EXCEPTION
2948 WHEN FND_API.G_EXC_ERROR THEN
2949 ROLLBACK TO DELETE_CA_FL_RESP;
2950 x_return_status := FND_API.G_RET_STS_ERROR ;
2951
2952 FND_MSG_PUB.Count_And_Get(
2953 p_count => x_msg_count,
2954 p_data => x_msg_data );
2955
2956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2957 ROLLBACK TO DELETE_CA_FL_RESP;
2958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2959
2960 FND_MSG_PUB.Count_And_Get(
2961 p_count => x_msg_count,
2962 p_data => x_msg_data );
2963
2964 WHEN OTHERS THEN
2965 ROLLBACK TO DELETE_CA_FL_RESP;
2966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2967
2968 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2969 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2970 END IF;
2971
2972 FND_MSG_PUB.Count_And_Get(
2973 p_count => x_msg_count,
2974 p_data => x_msg_data );
2975
2976 END DELETE_CA_FL_RESP;
2977
2978 procedure DELETE_CA_FL_RESP(
2979 p_api_version IN NUMBER,
2980 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2981 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2982
2983 p_ca_filter_id IN jtf_prefab_ca_fl_resps.ca_filter_id%TYPE,
2984 p_responsibility_id IN jtf_prefab_ca_fl_resps.responsibility_id%TYPE,
2985
2986 p_object_version_number IN jtf_prefab_ca_fl_resps.object_version_number%TYPE,
2987
2988 x_return_status OUT NOCOPY VARCHAR2,
2989 x_msg_count OUT NOCOPY NUMBER,
2990 x_msg_data OUT NOCOPY VARCHAR2
2991 ) AS
2992 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CA_FL_RESP';
2993 l_api_version CONSTANT NUMBER := p_api_version;
2994
2995 BEGIN
2996 -- Standard Start of API savepoint
2997 SAVEPOINT DELETE_CA_FL_RESP;
2998
2999 -- Standard call to check for call compatibility.
3000 IF NOT FND_API.Compatible_API_Call (
3001 l_api_version,
3002 p_api_version,
3003 l_api_name,
3004 G_PKG_NAME)
3005 THEN
3006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3007 END IF;
3008
3009 -- Initialize message list if p_init_msg_list is set to TRUE.
3010 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3011 FND_MSG_PUB.initialize;
3012 END IF;
3013
3014 -- Initialize API return status to success
3015 x_return_status := FND_API.G_RET_STS_SUCCESS;
3016
3017 -- real logic --
3018 ----------------
3019
3020 DELETE FROM jtf_prefab_ca_fl_resps
3021 WHERE ca_filter_id = p_ca_filter_id
3022 AND responsibility_id = p_responsibility_id;
3023
3024 -----------------------
3025 -- end of real logic --
3026
3027 -- Standard check of p_commit.
3028 IF (FND_API.To_Boolean(p_commit)) THEN
3029 COMMIT WORK;
3030 END IF;
3031
3032 -- Standard call to get message count and if count is 1, get message info.
3033 FND_MSG_PUB.Count_And_Get(
3034 p_count => x_msg_count,
3035 p_data => x_msg_data );
3036
3037 EXCEPTION
3038 WHEN FND_API.G_EXC_ERROR THEN
3039 ROLLBACK TO DELETE_CA_FL_RESP;
3040 x_return_status := FND_API.G_RET_STS_ERROR ;
3041
3042 FND_MSG_PUB.Count_And_Get(
3043 p_count => x_msg_count,
3044 p_data => x_msg_data );
3045
3046 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3047 ROLLBACK TO DELETE_CA_FL_RESP;
3048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3049
3050 FND_MSG_PUB.Count_And_Get(
3051 p_count => x_msg_count,
3052 p_data => x_msg_data );
3053
3054 WHEN OTHERS THEN
3055 ROLLBACK TO DELETE_CA_FL_RESP;
3056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3057
3058 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3059 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3060 END IF;
3061
3062 FND_MSG_PUB.Count_And_Get(
3063 p_count => x_msg_count,
3064 p_data => x_msg_data );
3065
3066 END DELETE_CA_FL_RESP;
3067
3068 PROCEDURE INSERT_CA_FL_LANG(
3069 p_api_version IN NUMBER,
3070 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3071 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3072
3073 p_ca_fl_lang_id OUT NOCOPY jtf_prefab_ca_fl_langs.ca_fl_lang_id%TYPE,
3074 p_ca_filter_id IN jtf_prefab_ca_fl_langs.ca_filter_id%TYPE,
3075 p_language_code IN jtf_prefab_ca_fl_langs.language_code%TYPE,
3076
3077 p_object_version_number OUT NOCOPY jtf_prefab_ca_fl_langs.object_version_number%TYPE,
3078
3079 x_return_status OUT NOCOPY VARCHAR2,
3080 x_msg_count OUT NOCOPY NUMBER,
3081 x_msg_data OUT NOCOPY VARCHAR2
3082 ) AS
3083 -- local variables --
3084 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CA_FL_LANG';
3085 l_api_version NUMBER := p_api_version;
3086 l_row_id VARCHAR2(255) := NULL;
3087
3088 CURSOR sequence_cursor IS
3089 SELECT jtf_prefab_ca_fl_langs_s.NEXTVAL from dual;
3090 BEGIN
3091 -- Standard Start of API savepoint
3092 SAVEPOINT INSERT_CA_FL_LANG;
3093
3094 -- Standard call to check for call compatibility.
3095 IF NOT FND_API.Compatible_API_Call (
3096 l_api_version,
3097 p_api_version,
3098 l_api_name,
3099 G_PKG_NAME)
3100 THEN
3101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3102 END IF;
3103
3104 -- Initialize message list if p_init_msg_list is set to TRUE.
3105 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3106 FND_MSG_PUB.initialize;
3107 END IF;
3108
3109 -- Initialize API return status to success
3110 x_return_status := FND_API.G_RET_STS_SUCCESS;
3111
3112 -- real logic --
3113 ----------------
3114
3115 OPEN sequence_cursor;
3116 FETCH sequence_cursor INTO p_ca_fl_lang_id;
3117 CLOSE sequence_cursor;
3118 p_object_version_number := 1;
3119
3120 INSERT INTO jtf_prefab_ca_fl_langs(ca_fl_lang_id,
3121 object_version_number,
3122 created_by,
3123 creation_date,
3124 last_updated_by,
3125 last_update_date,
3126 last_update_login,
3127 -- security_group_id,
3128 ca_filter_id,
3129 language_code)
3130 VALUES (p_ca_fl_lang_id,
3131 p_object_version_number,
3132 G_USER_ID,
3133 SYSDATE,
3134 G_USER_ID,
3135 SYSDATE,
3136 G_LOGIN_ID,
3137 -- NULL,
3138 p_ca_filter_id,
3139 p_language_code);
3140
3141 -----------------------
3142 -- end of real logic --
3143
3144 -- Standard check of p_commit.
3145 IF (FND_API.To_Boolean(p_commit)) THEN
3146 COMMIT WORK;
3147 END IF;
3148
3149 -- Standard call to get message count and if count is 1, get message info.
3150 FND_MSG_PUB.Count_And_Get(
3151 p_count => x_msg_count,
3152 p_data => x_msg_data );
3153
3154 EXCEPTION
3155 WHEN FND_API.G_EXC_ERROR THEN
3156 ROLLBACK TO INSERT_CA_FL_LANG;
3157 x_return_status := FND_API.G_RET_STS_ERROR ;
3158
3159 FND_MSG_PUB.Count_And_Get(
3160 p_count => x_msg_count,
3161 p_data => x_msg_data );
3162
3163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3164 ROLLBACK TO INSERT_CA_FL_LANG;
3165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3166
3167 FND_MSG_PUB.Count_And_Get(
3168 p_count => x_msg_count,
3169 p_data => x_msg_data );
3170
3171 WHEN OTHERS THEN
3172 ROLLBACK TO INSERT_CA_FL_LANG;
3173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174
3175 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3176 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3177 END IF;
3178
3179 FND_MSG_PUB.Count_And_Get(
3180 p_count => x_msg_count,
3181 p_data => x_msg_data );
3182
3183 END INSERT_CA_FL_LANG;
3184
3185 procedure DELETE_CA_FL_LANG(
3186 p_api_version IN NUMBER,
3187 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3188 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3189
3190 p_ca_filter_id IN jtf_prefab_ca_fl_langs.ca_filter_id%TYPE,
3191
3192 p_object_version_number IN jtf_prefab_ca_fl_langs.object_version_number%TYPE,
3193
3194 x_return_status OUT NOCOPY VARCHAR2,
3195 x_msg_count OUT NOCOPY NUMBER,
3196 x_msg_data OUT NOCOPY VARCHAR2
3197 ) AS
3198 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CA_FL_LANG';
3199 l_api_version CONSTANT NUMBER := p_api_version;
3200
3201 BEGIN
3202 -- Standard Start of API savepoint
3203 SAVEPOINT DELETE_CA_FL_LANG;
3204
3205 -- Standard call to check for call compatibility.
3206 IF NOT FND_API.Compatible_API_Call (
3207 l_api_version,
3208 p_api_version,
3209 l_api_name,
3210 G_PKG_NAME)
3211 THEN
3212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3213 END IF;
3214
3215 -- Initialize message list if p_init_msg_list is set to TRUE.
3216 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3217 FND_MSG_PUB.initialize;
3218 END IF;
3219
3220 -- Initialize API return status to success
3221 x_return_status := FND_API.G_RET_STS_SUCCESS;
3222
3223 -- real logic --
3224 ----------------
3225
3226 DELETE FROM jtf_prefab_ca_fl_langs
3227 WHERE ca_filter_id = p_ca_filter_id;
3228
3229 -----------------------
3230 -- end of real logic --
3231
3232 -- Standard check of p_commit.
3233 IF (FND_API.To_Boolean(p_commit)) THEN
3234 COMMIT WORK;
3235 END IF;
3236
3237 -- Standard call to get message count and if count is 1, get message info.
3238 FND_MSG_PUB.Count_And_Get(
3239 p_count => x_msg_count,
3240 p_data => x_msg_data );
3241
3242 EXCEPTION
3243 WHEN FND_API.G_EXC_ERROR THEN
3244 ROLLBACK TO DELETE_CA_FL_LANG;
3245 x_return_status := FND_API.G_RET_STS_ERROR ;
3246
3247 FND_MSG_PUB.Count_And_Get(
3248 p_count => x_msg_count,
3249 p_data => x_msg_data );
3250
3251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3252 ROLLBACK TO DELETE_CA_FL_LANG;
3253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3254
3255 FND_MSG_PUB.Count_And_Get(
3256 p_count => x_msg_count,
3257 p_data => x_msg_data );
3258
3259 WHEN OTHERS THEN
3260 ROLLBACK TO DELETE_CA_FL_LANG;
3261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3262
3263 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3264 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3265 END IF;
3266
3267 FND_MSG_PUB.Count_And_Get(
3268 p_count => x_msg_count,
3269 p_data => x_msg_data );
3270
3271 END DELETE_CA_FL_LANG;
3272
3273 procedure DELETE_CA_FL_LANG(
3274 p_api_version IN NUMBER,
3275 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3276 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3277
3278 p_ca_filter_id IN jtf_prefab_ca_fl_langs.ca_filter_id%TYPE,
3279 p_language_code IN jtf_prefab_ca_fl_langs.language_code%TYPE,
3280
3281 p_object_version_number IN jtf_prefab_ca_fl_langs.object_version_number%TYPE,
3282
3283 x_return_status OUT NOCOPY VARCHAR2,
3284 x_msg_count OUT NOCOPY NUMBER,
3285 x_msg_data OUT NOCOPY VARCHAR2
3286 ) AS
3287 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CA_FL_LANG';
3288 l_api_version CONSTANT NUMBER := p_api_version;
3289
3290 BEGIN
3291 -- Standard Start of API savepoint
3292 SAVEPOINT DELETE_CA_FL_LANG;
3293
3294 -- Standard call to check for call compatibility.
3295 IF NOT FND_API.Compatible_API_Call (
3296 l_api_version,
3297 p_api_version,
3298 l_api_name,
3299 G_PKG_NAME)
3300 THEN
3301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3302 END IF;
3303
3304 -- Initialize message list if p_init_msg_list is set to TRUE.
3305 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3306 FND_MSG_PUB.initialize;
3307 END IF;
3308
3309 -- Initialize API return status to success
3310 x_return_status := FND_API.G_RET_STS_SUCCESS;
3311
3312 -- real logic --
3313 ----------------
3314
3315 DELETE FROM jtf_prefab_ca_fl_langs
3316 WHERE ca_filter_id = p_ca_filter_id
3317 AND language_code = p_language_code;
3318
3319 -----------------------
3320 -- end of real logic --
3321
3322 -- Standard check of p_commit.
3323 IF (FND_API.To_Boolean(p_commit)) THEN
3324 COMMIT WORK;
3325 END IF;
3326
3327 -- Standard call to get message count and if count is 1, get message info.
3328 FND_MSG_PUB.Count_And_Get(
3329 p_count => x_msg_count,
3330 p_data => x_msg_data );
3331
3332 EXCEPTION
3333 WHEN FND_API.G_EXC_ERROR THEN
3334 ROLLBACK TO DELETE_CA_FL_LANG;
3335 x_return_status := FND_API.G_RET_STS_ERROR ;
3336
3337 FND_MSG_PUB.Count_And_Get(
3338 p_count => x_msg_count,
3339 p_data => x_msg_data );
3340
3341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3342 ROLLBACK TO DELETE_CA_FL_LANG;
3343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3344
3345 FND_MSG_PUB.Count_And_Get(
3346 p_count => x_msg_count,
3347 p_data => x_msg_data );
3348
3349 WHEN OTHERS THEN
3350 ROLLBACK TO DELETE_CA_FL_LANG;
3351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3352
3353 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3354 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3355 END IF;
3356
3357 FND_MSG_PUB.Count_And_Get(
3358 p_count => x_msg_count,
3359 p_data => x_msg_data );
3360
3361 END DELETE_CA_FL_LANG;
3362
3363 END JTF_PREFAB_CACHE_PUB;