1 PACKAGE BODY bis_rsg_pub_api_pkg AS
2 /* $Header: BISRSGPB.pls 120.3 2005/11/14 01:44:41 amitgupt noship $ */
3 version CONSTANT CHAR (80)
4 := '$Header: BISRSGPB.pls 120.3 2005/11/14 01:44:41 amitgupt noship $';
5
6 g_pkg_name VARCHAR2(50) := 'bis_rsg_pub_api_pkg';
7
8 g_curr_user_id NUMBER := FND_GLOBAL.User_id;
9 g_curr_login_id NUMBER := FND_GLOBAL.Login_id;
10
11 -- As per discussion with Tiwang, Ian, the logic of this function is the following:
12 -- for given function name A, we will first try to find if there is A_OA page object
13 -- in RSG repository, if there is, return A_OA, otherwise, return A
14 -- Potential problem: if in a migrated environment, user indeed has A_OA and A in
15 -- fnd_form_function, and user intend to add dependencies for A, we will end up with
16 -- add dependencies to A_OA with this logic, currently there is no way for lct to handle this case
17 -- although we can handle it correctly by investigating if there is really a A_OA in
18 -- fnd_form_functions if there is, return A; if there is not, return A_OA
19 FUNCTION get_page_name_by_func (
20 p_func_name IN VARCHAR2) RETURN VARCHAR2
21 IS
22 CURSOR c_page_object_name(p_func_name VARCHAR2) IS
23 SELECT object_name FROM bis_obj_dependency
24 WHERE object_type = 'PAGE' AND object_name = p_func_name || '_OA'
25 UNION ALL
26 SELECT object_name FROM bis_obj_properties
27 WHERE object_type = 'PAGE' AND object_name = p_func_name || '_OA';
28
29 v_object_name bis_obj_dependency.object_name%type; --Enhancement 4106617
30 BEGIN
31 IF (p_func_name IS NULL OR p_func_name = '') THEN
32 RETURN NULL;
33 END IF;
34 OPEN c_page_object_name(p_func_name);
35 FETCH c_page_object_name INTO v_object_name;
36 IF (c_page_object_name%notfound) THEN
37 v_object_name := p_func_name;
38 END IF;
39 CLOSE c_page_object_name;
40 RETURN v_object_name;
41 EXCEPTION
42 WHEN OTHERS THEN
43 RETURN NULL;
44 END get_page_name_by_func;
45
46 /* notice that this function has to work either for OA pages with or
47 * without _OA attached to the object name
48 */
49 PROCEDURE page_name_validation(
50 P_OBJECT_NAME IN VARCHAR2,
51 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
52 IS
53 v_dummy VARCHAR(5);
54 BEGIN
55 x_return_status := 'Y';
56
57 -- those with _OA attached must have already exist in RSG repository
58 BEGIN
59 execute immediate 'select 1 from bis_obj_properties
60 WHERE object_type = :1 AND object_name = :2'
61 INTO v_dummy
62 using 'PAGE', p_object_name;
63 RETURN;
64 EXCEPTION
65 WHEN no_data_found THEN
66 NULL;
67 END;
68
69 execute immediate 'select 1 from fnd_form_functions
70 WHERE ( Upper(web_html_call) = :1 AND substr(parameters,10) = :2)
71 OR ( Upper(web_html_call) LIKE :3 AND function_name = :4)'
72 INTO v_dummy
73 using 'ORACLESSWA.SWITCHPAGE', p_object_name, '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%', p_object_name;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 x_return_status := 'N';
78 END;
79
80 PROCEDURE object_name_validation (
81 P_OBJECT_TYPE IN VARCHAR2,
82 P_OBJECT_NAME IN VARCHAR2,
83 X_RETURN_STATUS OUT NOCOPY VARCHAR2
84 ) IS
85 v_dummy VARCHAR2(5);
86 BEGIN
87 IF (p_object_type IS NULL OR p_object_name IS NULL) THEN
88 x_return_status := 'N';
89 RETURN;
90 END IF;
91
92 x_return_status := 'Y';
93 IF (p_object_type = 'TABLE' OR p_object_type = 'VIEW'
94 OR p_object_type = 'MV' ) THEN
95 x_return_status := 'Y';
96 ELSIF (p_object_type = 'PORTLET') THEN
97 execute immediate 'select 1 from fnd_form_functions
98 WHERE TYPE in (''WEBPORTLET'',''WEBPORTLETX'') AND function_name = :1'
99 INTO v_dummy
100 using p_object_name;
101 ELSIF (p_object_type = 'REPORT') THEN
102 execute immediate 'select 1 from fnd_form_functions
103 WHERE TYPE in (:1,:2) AND function_name = :3'
104 INTO v_dummy
105 using 'WWW', 'JSP',p_object_name;
106 ELSIF(p_object_type = 'PAGE') THEN
107 page_name_validation(p_object_name, x_return_status);
108 ELSE
109 x_return_status := 'N';
110 END IF;
111 EXCEPTION
112 WHEN OTHERS THEN
113 x_return_status := 'N';
114 END object_name_validation;
115
116 PROCEDURE object_owner_validation(
117 p_object_owner VARCHAR2,
118 x_return_status OUT nocopy VARCHAR2)
119 IS
120 v_dummy VARCHAR2(5);
121 BEGIN
122 X_RETURN_STATUS := 'Y'; --default as Y.
123
124 IF (P_OBJECT_OWNER IS NULL) THEN
125 X_RETURN_STATUS := 'N';
126 RETURN;
127 END IF;
128
129 execute immediate 'SELECT 1
130 FROM FND_APPLICATION
131 WHERE APPLICATION_SHORT_NAME = :1'
132 INTO v_dummy
133 using p_object_owner;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 x_return_status := 'N';
138 END object_owner_validation;
139
140 -- this procedure can only be invoked in create_dependency
141 -- thus here we don't need to call get_page_name_by_func
142 PROCEDURE create_property(
143 P_OBJECT_TYPE in VARCHAR2,
144 P_OBJECT_OWNER in VARCHAR2,
145 P_OBJECT_NAME in VARCHAR2,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_data OUT NOCOPY VARCHAR2
148 ) IS
149 v_procedure_name VARCHAR2(50) := 'Create_Property';
150 BEGIN
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 execute immediate 'insert into bis_obj_properties (
154 object_type, OBJECT_NAME, OBJECT_OWNER,
155 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
156 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE)
157 values(:1, :2, :3, :4, :5, :6, :7, :8)'
158 using P_OBJECT_TYPE, p_object_name, P_OBJECT_OWNER,
159 g_curr_user_id, Sysdate, g_curr_user_id,
160 g_curr_login_id, Sysdate;
161 EXCEPTION
162 WHEN dup_val_on_index THEN
163 RETURN;
164 WHEN OTHERS THEN
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
166 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
167 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
168 END IF;
169 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
170 END create_property;
171
172 -- for page definer
173 -- try to find object owner in RSG repository first
174 FUNCTION get_appl_short_name_for_obj(p_object_name VARCHAR2,
175 p_object_type VARCHAR2) RETURN VARCHAR2 IS
176
177 CURSOR c_obj_appl_short_name IS
178 SELECT DISTINCT object_owner
179 FROM bis_obj_dependency
180 WHERE object_name = p_object_name AND object_type = p_object_type;
181
182 CURSOR c_depend_obj_appl_short_name IS
183 SELECT DISTINCT depend_object_owner
184 FROM bis_obj_dependency
185 WHERE depend_object_name = p_object_name AND depend_object_type = p_object_type;
186
187 l_obj_owner VARCHAR(50);
188 BEGIN
189 OPEN c_obj_appl_short_name;
190 FETCH c_obj_appl_short_name INTO l_obj_owner;
191 CLOSE c_obj_appl_short_name;
192
193 IF (l_obj_owner IS NOT NULL) THEN
194 RETURN l_obj_owner;
195 END IF;
196
197 OPEN c_depend_obj_appl_short_name;
198 FETCH c_depend_obj_appl_short_name INTO l_obj_owner;
199 CLOSE c_depend_obj_appl_short_name;
200
201 RETURN l_obj_owner;
202 EXCEPTION
203 WHEN OTHERS THEN
204 RETURN NULL;
205 END get_appl_short_name_for_obj;
206
207 PROCEDURE Create_Dependency (
208 P_OBJECT_TYPE in VARCHAR2,
209 P_OBJECT_OWNER in VARCHAR2,
210 P_OBJECT_NAME in VARCHAR2,
211 P_ENABLED_FLAG in VARCHAR2,
212 P_DEPEND_OBJECT_TYPE in VARCHAR2,
213 P_DEPEND_OBJECT_OWNER in VARCHAR2,
214 P_DEPEND_OBJECT_NAME in VARCHAR2,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_data OUT NOCOPY VARCHAR2
217 ) IS
218 v_procedure_name VARCHAR2(50) := 'Create_Dependency';
219 l_object_owner VARCHAR2(50);
220 l_depend_object_owner VARCHAR2(50);
221 l_object_name bis_obj_dependency.object_name%type; --Enhancement 4106617
222 BEGIN
223 FND_MSG_PUB.initialize;
224
225 -- since page designer passes in fnd_form_function name instead of object name
226 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
227 IF (p_object_type = 'PAGE') THEN
228 l_object_name := get_page_name_by_func(p_object_name);
229 ELSE
230 l_object_name := p_object_name;
231 END IF;
232
233 l_object_owner := get_appl_short_name_for_obj(l_object_name, p_object_type);
234 l_depend_object_owner := get_appl_short_name_for_obj(p_depend_object_name, p_depend_object_type);
235
236 IF (l_object_owner IS NULL) THEN
237 l_object_owner := p_object_owner;
238 END IF;
239
240 IF (l_depend_object_owner IS NULL) THEN
241 l_depend_object_owner := p_depend_object_owner;
242 END IF;
243
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246 -- validate (object_type, object_name)
247 object_name_validation(p_object_type, l_object_name, x_return_status);
248 IF (x_return_status = 'N') THEN
249 --CHANGE FOR BUG 4698254
250 IF (p_object_type <> 'PORTLET') THEN
251 x_msg_data := 'BIS_BIA_RSG_INVALID_OBJ';
252 ELSE
253 x_msg_data := 'BIS_REGION_NOT_EXISTING_MSG';
254 END IF;
255 x_return_status := fnd_api.g_ret_sts_error;
256 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
257 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, x_msg_data);
258 END IF;
259 RETURN;
260 END IF;
261
262 -- validate object_owner
263 object_owner_validation(l_object_owner, x_return_status);
264 IF (x_return_status = 'N') THEN
265 x_msg_data := 'BIS_BIA_RSG_INVALID_OBJ_OWNER';
266 x_return_status := fnd_api.g_ret_sts_error;
267 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
268 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INVALID_OBJ_OWNER');
269 END IF;
270 RETURN;
271 END IF;
272
273 -- validate (depend_object_type, depend_object_name)
274 object_name_validation(p_depend_object_type, p_depend_object_name, x_return_status);
275 IF (x_return_status = 'N') THEN
276 --added for bug 4698254
277 IF (p_depend_object_type <> 'PORTLET') THEN
278 x_msg_data := 'BIS_BIA_RSG_INVALID_DEP_OBJ';
279 ELSE
280 x_msg_data := 'BIS_REGION_NOT_EXISTING_MSG';
281 END IF;
282 x_return_status := fnd_api.g_ret_sts_error;
283 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
284 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, x_msg_data);
285 END IF;
286 RETURN;
287 END IF;
288
289 -- validate depend_object_owner
290 object_owner_validation(l_depend_object_owner, x_return_status);
291 IF (x_return_status = 'N') THEN
292 x_msg_data := 'BIS_BIA_RSG_INV_DEP_OBJ_OWNER';
293 x_return_status := fnd_api.g_ret_sts_error;
294 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
295 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_DEP_OBJ_OWNER');
296 END IF;
297 RETURN;
298 END IF;
299
300 -- validate enabled flag
301 IF (p_enabled_flag IS NOT NULL AND p_enabled_flag <> 'Y'
302 AND p_enabled_flag <> 'N') THEN
303 x_return_status := fnd_api.g_ret_sts_error;
304 x_msg_data := 'BIS_BIA_RSG_INV_ENABLED_FLAG';
305 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
306 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_ENABLED_FLAG');
307 END IF;
308 RETURN;
309 END IF;
310
311 execute immediate 'insert into bis_obj_dependency (
312 OBJECT_TYPE, OBJECT_OWNER, OBJECT_NAME,
313 ENABLED_FLAG,
314 DEPEND_OBJECT_TYPE, DEPEND_OBJECT_OWNER, DEPEND_OBJECT_NAME,
315 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
316 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE)
317 values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)'
318 using p_object_type, l_OBJECT_OWNER, l_object_name,
319 P_ENABLED_FLAG,
320 P_DEPEND_OBJECT_TYPE, l_DEPEND_OBJECT_OWNER, P_DEPEND_OBJECT_NAME,
321 g_curr_user_id, sysdate, g_curr_user_id,
322 g_curr_login_id, sysdate;
323
324 -- create corresponding object property if it doesn't exist in RSG previously
325 -- note that x_return_status and x_msg_data are set appropriately in create_property
326 create_property(p_object_type, l_object_owner, l_object_name,
327 x_return_status, x_msg_data);
328 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
329 RETURN;
330 END IF;
331
332 --create corresponding depend object property if it doesn't exist in RSG previously
333 -- note that x_return_status and x_msg_data are set appropriately in create_property
334 create_property(p_depend_object_type, l_depend_object_owner,
335 p_depend_object_name,
336 x_return_status, x_msg_data);
337 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
338 RETURN;
339 END IF;
340
341 EXCEPTION
342 WHEN dup_val_on_index THEN -- unique constraint violation
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
345 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
346 END IF;
347 x_msg_data := 'BIS_BIA_RSG_DUP_DEP_REC';
348 WHEN OTHERS THEN
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
351 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
352 END IF;
353 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
354 END create_dependency;
355
356
357 PROCEDURE Update_Dependency (
358 P_OBJECT_TYPE in VARCHAR2,
359 P_OBJECT_NAME in VARCHAR2,
360 P_ENABLED_FLAG in VARCHAR2,
361 P_DEPEND_OBJECT_TYPE in VARCHAR2,
362 P_DEPEND_OBJECT_NAME in VARCHAR2,
363 x_return_status OUT NOCOPY VARCHAR2,
364 x_msg_data OUT NOCOPY VARCHAR2
365 ) IS
366 v_procedure_name VARCHAR2(50) := 'Update_Dependency';
367 l_object_name bis_obj_dependency.object_name%type; --Enhancement 4106617
368 BEGIN
369 FND_MSG_PUB.initialize;
370
371 -- since page designer passes in fnd_form_function name instead of object name
372 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
373 IF (p_object_type = 'PAGE') THEN
374 l_object_name := get_page_name_by_func(p_object_name);
375 ELSE
376 l_object_name := p_object_name;
377 END IF;
378
379 -- validate enabled_flag
380 IF (p_enabled_flag IS NOT NULL AND p_enabled_flag <> 'Y'
381 AND p_enabled_flag <> 'N') THEN
382 x_return_status := fnd_api.g_ret_sts_error;
383 x_msg_data := 'BIS_BIA_RSG_INV_ENABLED_FLAG';
384 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
385 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_ENABLED_FLAG');
386 END IF;
387 RETURN;
388 END IF;
389
390 --dbms_output.put_line('enabled flag validated');
391
392 x_return_status := FND_API.G_RET_STS_SUCCESS;
393
394 execute immediate 'update bis_obj_dependency
395 set enabled_flag = :1,
396 last_updated_by = :2,
397 last_update_login = :3,
398 last_update_date = :4
399 WHERE object_name = :5 AND object_type = :6
400 AND depend_object_name = :7 AND depend_object_type = :8'
404 --IF (SQL%notfound) THEN
401 using p_enabled_flag, g_curr_user_id, g_curr_login_id, Sysdate,
402 l_object_name, p_object_type, p_depend_object_name, p_depend_object_type;
403
405 -- x_return_status := FND_API.G_RET_STS_ERROR;
406 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
407 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
408 -- END IF;
409 -- x_msg_data := 'BIS_BIA_RSG_DEP_NOT_EXISTS';
410 --END IF;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
416 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
417 END IF;
418 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
419 END Update_Dependency;
420
421 -- this procedure will only be called from delete_dependencies procedure
422 -- no need to call get_page_object_name_by_func
423 PROCEDURE delete_property (
424 P_OBJECT_TYPE in VARCHAR2,
425 P_OBJECT_NAME in VARCHAR2
426 ) IS
427 CURSOR c_dependencies (p_obj_name VARCHAR2, p_obj_type VARCHAR2) IS
428 SELECT depend_object_name
429 FROM bis_obj_dependency
430 WHERE (object_name = p_obj_name AND object_type = p_obj_type)
431 OR (depend_object_name = p_obj_name AND depend_object_type = p_obj_type);
432
433 v_dep_obj_name bis_obj_dependency.depend_object_name%TYPE; --Enhancement 4106617
434 BEGIN
435 OPEN c_dependencies(p_object_name, p_object_type);
436 FETCH c_dependencies INTO v_dep_obj_name;
437 IF (c_dependencies%notfound) THEN
438 execute immediate 'delete from bis_obj_properties
439 where object_type = :1 and object_name = :2'
440 using p_object_type, p_object_name;
441 END IF;
442 CLOSE c_dependencies;
443 EXCEPTION
444 WHEN OTHERS THEN
445 RETURN;
446 END delete_property;
447
448 PROCEDURE Delete_Dependency (
449 P_OBJECT_TYPE in VARCHAR2,
450 P_OBJECT_NAME in VARCHAR2,
451 P_DEPEND_OBJECT_TYPE in VARCHAR2,
452 P_DEPEND_OBJECT_NAME in VARCHAR2,
453 x_return_status OUT NOCOPY VARCHAR2,
454 x_msg_data OUT NOCOPY VARCHAR2
455 ) IS
456 v_procedure_name VARCHAR2(50) := 'Delete_Dependency';
457 l_object_name VARCHAR2(480); --Enhancement 4106617
458 BEGIN
459 FND_MSG_PUB.initialize;
460
461 -- since page designer passes in fnd_form_function name instead of object name
462 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
463 IF (p_object_type = 'PAGE') THEN
464 l_object_name := get_page_name_by_func(p_object_name);
465 ELSE
466 l_object_name := p_object_name;
467 END IF;
468
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470
471 execute immediate 'delete from bis_obj_dependency
472 WHERE object_name = :1 AND object_type = :2
473 AND depend_object_name = :3 AND depend_object_type = :4'
474 using l_object_name, p_object_type, p_depend_object_name, p_depend_object_type;
475
476 -- as per discussion with Tianyi, comment out the following code
477 -- IF (SQL%notfound) THEN
478 -- x_return_status := FND_API.G_RET_STS_ERROR;
479 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
480 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
481 -- END IF;
482 -- x_msg_data := 'BIS_BIA_RSG_DEP_NOT_EXISTS';
483 -- END IF;
484
485 -- delete the corresponding properties if neccessary
486 delete_property(p_object_type, l_object_name);
487 delete_property(p_depend_object_type, p_depend_object_name);
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
493 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
494 END IF;
495 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
496 END Delete_Dependency;
497
498 --Added for bug 4606455
499 -- is to delete depdendency based on rowid instead of object name
500 -- this will in turn call the above function
501 PROCEDURE Delete_Dependency (
502 P_ROWID in ROWID,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_data OUT NOCOPY VARCHAR2
505 ) IS
506 l_object_type VARCHAR2(30);
507 l_object_name VARCHAR2(480);
508 l_depend_object_type VARCHAR2(30);
509 l_depend_object_name VARCHAR2(480);
510 cursor get_names(rid varchar2) is
511 select object_name,object_type,depend_object_name,depend_object_type
512 from bis_obj_dependency where rowid = rid;
513 BEGIN
514 open get_names(P_ROWID);
515 fetch get_names into l_object_name,l_object_type,l_depend_object_name,l_depend_object_type;
516 if get_names%NOTFOUND then
517 close get_names;
518 return;
519 end if;
520 close get_names;
521
522 delete_dependency(l_object_type,l_object_name,l_depend_object_type,l_depend_object_name,
523 x_return_status,x_msg_data);
524 EXCEPTION
525 WHEN OTHERS THEN
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
528 END Delete_Dependency;
529
533 x_msg_data OUT NOCOPY VARCHAR2
530 PROCEDURE Delete_Page_Dependencies (
531 P_OBJECT_NAME in VARCHAR2,
532 x_return_status OUT NOCOPY VARCHAR2,
534 ) IS
535 v_procedure_name VARCHAR2(50) := 'Delete_Page_Dependencies';
536 l_object_name bis_obj_dependency.object_name%type; --Enhancement 4106617
537 CURSOR c_deps (p_page_name VARCHAR2) IS
538 SELECT object_name, object_type, depend_object_name, depend_object_type
539 FROM bis_obj_dependency
540 WHERE object_type = 'PAGE' AND object_name = p_page_name;
541 v_dep c_deps%ROWTYPE;
542 BEGIN
543 FND_MSG_PUB.initialize;
544
545 -- since page designer passes in fnd_form_function name instead of object name
546 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
547 l_object_name := get_page_name_by_func(p_object_name);
548
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 -- execute immediate 'delete from bis_obj_dependency
552 -- WHERE object_name = :1 AND object_type = :2'
553 -- using l_object_name, 'PAGE';
554
555 -- IF (SQL%notfound) THEN
556 -- x_return_status := FND_API.G_RET_STS_ERROR;
557 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
558 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
559 -- END IF;
560 -- x_msg_data := 'BIS_BIA_RSG_PAGE_NOT_EXISTS';
561 -- END IF;
562
563 -- execute immediate 'delete from bis_obj_properties
564 -- WHERE object_name = :1 AND object_type = :2'
565 -- using l_object_name, 'PAGE';
566
567 OPEN c_deps(l_object_name);
568 LOOP
569 FETCH c_deps INTO v_dep;
570 EXIT WHEN c_deps%notfound;
571 Delete_Dependency (v_dep.object_type,
572 v_dep.OBJECT_NAME,
573 v_dep.depend_object_type,
574 v_dep.depend_object_name,
575 x_return_status,
576 x_msg_data);
577 EXIT WHEN (x_return_status <> FND_API.g_ret_sts_success);
578 END LOOP;
579 CLOSE c_deps;
580 EXCEPTION
581 WHEN OTHERS THEN
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
583 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
584 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
585 END IF;
586 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
587 END Delete_Page_Dependencies;
588
589 PROCEDURE Update_Property(
590 P_OBJECT_TYPE in VARCHAR2,
591 P_OBJECT_NAME in VARCHAR2,
592 P_DIMENSION_FLAG in VARCHAR2,
593 P_CUSTOM_API in VARCHAR2,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_data OUT NOCOPY VARCHAR2
596 ) IS
597 v_procedure_name VARCHAR2(50) := 'Update_Property';
598 l_object_name VARCHAR2(480); --Enhancement 4106617
599 BEGIN
600 FND_MSG_PUB.initialize;
601
602 -- since page designer passes in fnd_form_function name instead of object name
603 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
604 IF (p_object_type = 'PAGE') THEN
605 l_object_name := get_page_name_by_func(p_object_name);
606 ELSE
607 l_object_name := p_object_name;
608 END IF;
609
610 -- validate dimension_flag
611 IF (p_dimension_flag IS NOT NULL AND p_dimension_flag <> 'Y'
612 AND p_dimension_flag <> 'N') THEN
613 x_return_status := fnd_api.g_ret_sts_error;
614 x_msg_data := 'BIS_BIA_RSG_INVALID_DIM_FLAG';
615 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
616 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INVALID_DIM_FLAG');
617 END IF;
618 RETURN;
619 END IF;
620
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 execute immediate 'update bis_obj_properties
624 set dimension_flag = :1,
625 custom_api = :2,
626 last_updated_by = :3,
627 last_update_login = :4,
628 last_update_date = :5
629 WHERE object_name = :6 AND object_type = :7'
630 using p_dimension_flag, p_custom_api, g_curr_user_id, g_curr_login_id, Sysdate,
631 l_object_name, p_object_type;
632
633 --IF (SQL%notfound) THEN
634 -- x_return_status := FND_API.G_RET_STS_ERROR;
635 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
636 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
637 -- END IF;
638 -- x_msg_data := 'BIS_BIA_RSG_PROP_NOT_EXISTS';
639 --END IF;
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
645 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
646 END IF;
647 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
648 END update_property;
649
650
651 PROCEDURE Update_Property_Dim_Flag(
652 P_OBJECT_TYPE in VARCHAR2,
653 P_OBJECT_NAME in VARCHAR2,
654 P_DIMENSION_FLAG in VARCHAR2,
658 v_procedure_name VARCHAR2(50) := 'Update_Property_Dim_Flag';
655 x_return_status OUT NOCOPY VARCHAR2,
656 x_msg_data OUT NOCOPY VARCHAR2
657 ) IS
659 l_object_name VARCHAR2(480); --Enhancement 4106617
660 BEGIN
661 FND_MSG_PUB.initialize;
662
663 -- since page designer passes in fnd_form_function name instead of object name
664 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
665 IF (p_object_type = 'PAGE') THEN
666 l_object_name := get_page_name_by_func(p_object_name);
667 ELSE
668 l_object_name := p_object_name;
669 END IF;
670
671 -- validate dimension_flag
672 IF (p_dimension_flag IS NOT NULL AND p_dimension_flag <> 'Y'
673 AND p_dimension_flag <> 'N') THEN
674 x_return_status := fnd_api.g_ret_sts_error;
675 x_msg_data := 'BIS_BIA_RSG_INVALID_DIM_FLAG';
676 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
677 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INVALID_DIM_FLAG');
678 END IF;
679 RETURN;
680 END IF;
681
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683
684 execute immediate 'update bis_obj_properties
685 set dimension_flag = :1,
686 last_updated_by = :2,
687 last_update_login = :3,
688 last_update_date = :4
689 WHERE object_name = :5 AND object_type = :6'
690 using p_dimension_flag, g_curr_user_id, g_curr_login_id, Sysdate,
691 l_object_name, p_object_type;
692
693 --IF (SQL%notfound) THEN
694 -- x_return_status := FND_API.G_RET_STS_ERROR;
695 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
696 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
697 -- END IF;
698 -- x_msg_data := 'BIS_BIA_RSG_PROP_NOT_EXISTS';
699 -- END IF;
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
705 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
706 END IF;
707 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
708 END update_property_dim_flag;
709
710 PROCEDURE Update_Property_Custom_API(
711 P_OBJECT_TYPE in VARCHAR2,
712 P_OBJECT_NAME in VARCHAR2,
713 P_CUSTOM_API in VARCHAR2,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_data OUT NOCOPY VARCHAR2
716 ) IS
717 v_procedure_name VARCHAR2(50) := 'Update_Property_Custom_API';
718 l_object_name VARCHAR2(480); --Enhancement 4106617
719 BEGIN
720 FND_MSG_PUB.initialize;
721
722 -- since page designer passes in fnd_form_function name instead of object name
723 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
724 IF (p_object_type = 'PAGE') THEN
725 l_object_name := get_page_name_by_func(p_object_name);
726 ELSE
727 l_object_name := p_object_name;
728 END IF;
729
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732 execute immediate 'update bis_obj_properties
733 set custom_api = :1,
734 last_updated_by = :2,
735 last_update_login = :3,
736 last_update_date = :4
737 WHERE object_name = :5 AND object_type = :6'
738 using p_custom_api, g_curr_user_id, g_curr_login_id, Sysdate,
739 l_object_name, p_object_type;
740
741 --IF (SQL%notfound) THEN
745 -- END IF;
742 -- x_return_status := FND_API.G_RET_STS_ERROR;
743 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
744 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
746 -- x_msg_data := 'BIS_BIA_RSG_PROP_NOT_EXISTS';
747 --END IF;
748
749 EXCEPTION
750 WHEN OTHERS THEN
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
752 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
753 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
754 END IF;
755 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
756 END update_property_custom_api;
757 /**
758 * remove for bug#3748713
759 * the snapshot_log_sql should not be updated through any API other
760 * than BIS_BIA_RSG_CUSTOM_API_MGMNT package.
761 PROCEDURE Update_Property_Snapshotlog(
762 P_OBJECT_TYPE in VARCHAR2,
763 P_OBJECT_NAME in VARCHAR2,
764 P_SNAPSHOT_LOG in VARCHAR2,
765 x_return_status OUT NOCOPY VARCHAR2,
766 x_msg_data OUT NOCOPY VARCHAR2
767 ) IS
768 v_procedure_name VARCHAR2(50) := 'Update_Property_Snapshotlog';
769 l_object_name VARCHAR2(80);
770 BEGIN
771 FND_MSG_PUB.initialize;
772
773 -- since page designer passes in fnd_form_function name instead of object name
774 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
775 IF (p_object_type = 'PAGE') THEN
776 l_object_name := get_page_name_by_func(p_object_name);
777 ELSE
778 l_object_name := p_object_name;
779 END IF;
780
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 execute immediate 'update bis_obj_properties
784 set SNAPSHOT_LOG_SQL = :1,
785 last_updated_by = :2,
786 last_update_login = :3,
787 last_update_date = :4
788 WHERE object_name = :5 AND object_type = :6'
789 using p_snapshot_log, g_curr_user_id, g_curr_login_id, Sysdate,
790 l_object_name, p_object_type;
791
792 --IF (SQL%notfound) THEN
793 -- x_return_status := FND_API.G_RET_STS_ERROR;
794 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
795 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
796 -- END IF;
797 -- x_msg_data := 'BIS_BIA_RSG_PROP_NOT_EXISTS';
798 --END IF;
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
804 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
805 END IF;
806 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
807 END update_property_snapshotlog;
808 */
809
810 /**
811 * remove for bug#3748713
812 * the snapshot_log_sql should not be exposed through any API,
813 * as it is currently centrally managed by
814 * BIS_BIA_RSG_CUSTOM_API_MGMNT package.
815 FUNCTION Get_Property_Snapshotlog(
816 P_OBJECT_TYPE in VARCHAR2,
817 P_OBJECT_NAME in VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_msg_data OUT NOCOPY VARCHAR2
820 ) RETURN VARCHAR2 IS
821 v_procedure_name VARCHAR2(50) := 'Get_Property_Snapshotlog';
822 v_snapshot_log VARCHAR2(4000);
823 l_object_name VARCHAR2(80);
824 BEGIN
825 FND_MSG_PUB.initialize;
826
827 -- since page designer passes in fnd_form_function name instead of object name
828 -- we need take care of _OA problem by calling get_page_name_by_func, if object type is PAGE
829 IF (p_object_type = 'PAGE') THEN
830 l_object_name := get_page_name_by_func(p_object_name);
831 ELSE
832 l_object_name := p_object_name;
833 END IF;
834
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836
837 execute immediate 'select snapshot_log_sql
838 from bis_obj_properties
839 WHERE object_name = :1 AND object_type = :2'
840 INTO v_snapshot_log
841 using l_object_name, p_object_type;
842
843 RETURN v_snapshot_log;
844
845 EXCEPTION
846 WHEN no_data_found THEN
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
849 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
850 END IF;
851 x_msg_data := 'BIS_BIA_RSG_PROP_NOT_EXISTS';
852 RETURN NULL;
853 WHEN OTHERS THEN
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
855 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
856 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
857 END IF;
858 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
859 RETURN NULL;
860 END get_property_snapshotlog;
861 */
862
863 PROCEDURE prog_validation(
864 P_CONC_PROG_NAME in VARCHAR2,
865 P_APPL_SHORT_NAME in VARCHAR2,
866 x_return_status OUT NOCOPY VARCHAR2
867 ) IS
868 v_dummy VARCHAR2(5);
872 IF (P_CONC_PROG_NAME IS NULL OR p_appl_short_name IS NULL) THEN
869 BEGIN
870 x_return_status := 'Y';
871
873 X_RETURN_STATUS := 'N';
874 RETURN;
875 END IF;
876
877 execute immediate ' SELECT 1
878 FROM fnd_concurrent_programs prog, fnd_application appl
879 WHERE prog.CONCURRENT_PROGRAM_NAME = :1
880 AND prog.application_id = appl.application_id
881 AND appl.application_short_name = :2'
882 INTO v_dummy
883 using p_conc_prog_name, p_appl_short_name;
884 EXCEPTION
885 WHEN OTHERS THEN
886 x_return_status := 'N';
887 END prog_validation;
888
889 PROCEDURE loading_mode_validation(
890 p_loading_mode VARCHAR2,
891 x_return_status OUT nocopy VARCHAR2
892 ) IS
893 v_dummy VARCHAR2(5);
894 BEGIN
895 x_return_status := 'Y';
896
897 IF (P_LOADING_MODE IS NULL) THEN
898 X_RETURN_STATUS := 'N';
899 RETURN;
900 END IF;
901
902 execute immediate 'select 1
903 from fnd_common_lookups
904 where lookup_type = :1 AND lookup_code = :2'
905 INTO v_dummy
906 using 'BIS_REFRESH_MODE', p_loading_mode;
907 EXCEPTION
908 WHEN OTHERS THEN
909 x_return_status := 'N';
910 END loading_mode_validation;
911
912 PROCEDURE Create_Linkage (
913 P_OBJECT_TYPE in VARCHAR2,
914 P_OBJECT_OWNER in VARCHAR2,
915 P_OBJECT_NAME in VARCHAR2,
916 P_ENABLED_FLAG in VARCHAR2,
917 P_CONC_PROG_NAME in VARCHAR2,
918 P_APPL_SHORT_NAME in VARCHAR2,
919 P_REFRESH_MODE in VARCHAR2,
920 x_return_status OUT NOCOPY VARCHAR2,
921 x_msg_data OUT NOCOPY VARCHAR2
922 ) IS
923 v_procedure_name VARCHAR2(50) := 'Create_Linkage';
924 v_appl_id NUMBER;
925 BEGIN
926 FND_MSG_PUB.initialize;
927
928 -- page is not allowed to have refresh program associated
929 IF (p_object_type = 'PAGE') THEN
930 x_msg_data := 'BIS_BIA_RSG_NO_PAGE_LINKAGE';
931 x_return_status := fnd_api.g_ret_sts_error;
932 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
933 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_NO_PAGE_LINKAGE');
934 END IF;
935 RETURN;
936 END IF;
937
938 --dbms_output.put_line('1');
939
940 -- validate (object_type, object_name)
941 object_name_validation(p_object_type, p_object_name, x_return_status);
942 IF (x_return_status = 'N') THEN
943 x_msg_data := 'BIS_BIA_RSG_INVALID_OBJ';
944 x_return_status := fnd_api.g_ret_sts_error;
945 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
946 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INVALID_OBJ');
947 END IF;
948 RETURN;
949 END IF;
950
951 --dbms_output.put_line('2');
952
953 -- validate object_owner
954 object_owner_validation(p_object_owner, x_return_status);
955 IF (x_return_status = 'N') THEN
956 x_msg_data := 'BIS_BIA_RSG_INVALID_OBJ_OWNER';
957 x_return_status := fnd_api.g_ret_sts_error;
958 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
959 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INVALID_OBJ_OWNER');
960 END IF;
961 RETURN;
962 END IF;
963
964 --dbms_output.put_line('3');
965
966 -- validate concurrent program
967 prog_validation(P_CONC_PROG_NAME, P_APPL_SHORT_NAME, x_return_status);
968 IF (x_return_status = 'N') THEN
969 x_msg_data := 'BIS_BIA_RSG_INVALID_CONC_PROG';
970 x_return_status := fnd_api.g_ret_sts_error;
971 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
972 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INVALID_CONC_PROG');
973 END IF;
974 RETURN;
975 END IF;
976
977 --dbms_output.put_line('4');
978
979 -- validate enabled flag
980 IF (p_enabled_flag IS NOT NULL AND p_enabled_flag <> 'Y' AND p_enabled_flag <> 'N') THEN
981 x_return_status := fnd_api.g_ret_sts_error;
982 x_msg_data := 'BIS_BIA_RSG_INV_ENABLED_FLAG';
983 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
984 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_ENABLED_FLAG');
985 END IF;
986 RETURN;
987 END IF;
988
989 --dbms_output.put_line('5');
990
991 -- validate loading mode
992 loading_mode_validation(p_refresh_mode, x_return_status);
993 IF (x_return_status = 'N') THEN
994 x_msg_data := 'BIS_BIA_RSG_INV_LOADING_MODE';
995 x_return_status := fnd_api.g_ret_sts_error;
996 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
997 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_LOADING_MODE');
998 END IF;
999 RETURN;
1000 END IF;
1001
1002 --dbms_output.put_line('6');
1003
1004 x_return_status := FND_API.G_RET_STS_SUCCESS;
1005
1006 execute immediate 'select application_id from fnd_application
1007 WHERE application_short_name = :1'
1008 INTO v_appl_id
1009 using p_appl_short_name;
1010
1011 --dbms_output.put_line('7');
1015 CONC_PROGRAM_NAME, CONC_APP_ID, CONC_APP_SHORT_NAME,
1012
1013 execute immediate 'insert into bis_obj_prog_linkages (
1014 OBJECT_TYPE, OBJECT_OWNER, OBJECT_NAME,
1016 ENABLED_FLAG, REFRESH_MODE,
1017 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1018 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE)
1019 values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)'
1020 using P_OBJECT_TYPE, P_OBJECT_OWNER, P_OBJECT_NAME,
1021 P_CONC_PROG_NAME, V_APPL_ID, P_APPL_SHORT_NAME,
1022 P_ENABLED_FLAG, P_REFRESH_MODE,
1023 g_curr_user_id, sysdate, g_curr_user_id,
1024 g_curr_login_id, sysdate;
1025
1026 --dbms_output.put_line('8');
1027
1028 --create corresponding depend object property if it doesn't exist in RSG previously
1029 -- note that x_return_status and x_msg_data are set appropriately in create_property
1030 create_property(p_object_type, p_object_owner,
1031 p_object_name, x_return_status, x_msg_data);
1032 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1033 RETURN;
1034 END IF;
1035
1036 EXCEPTION
1037 WHEN dup_val_on_index THEN -- unique constraint violation
1038 x_return_status := FND_API.G_RET_STS_ERROR;
1039 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1040 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1041 END IF;
1042 x_msg_data := 'BIS_BIA_RSG_DUP_LINKAGE_REC';
1043 WHEN OTHERS THEN
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1045 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1046 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1047 END IF;
1048 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1049 END create_linkage;
1050
1051 PROCEDURE Update_Linkage (
1052 P_OBJECT_TYPE in VARCHAR2,
1053 P_OBJECT_NAME in VARCHAR2,
1054 P_ENABLED_FLAG in VARCHAR2,
1055 P_CONC_PROG_NAME in VARCHAR2,
1056 P_APPL_SHORT_NAME in VARCHAR2,
1057 p_refresh_mode IN VARCHAR2,
1058 x_return_status OUT NOCOPY VARCHAR2,
1059 x_msg_data OUT NOCOPY VARCHAR2
1060 ) IS
1061 v_procedure_name VARCHAR2(50) := 'Update_Linkage';
1062 BEGIN
1063 FND_MSG_PUB.initialize;
1064
1065 -- validate enabled_flag
1066 IF (p_enabled_flag IS NOT NULL AND p_enabled_flag <> 'Y'
1067 AND p_enabled_flag <> 'N') THEN
1068 x_return_status := fnd_api.g_ret_sts_error;
1069 x_msg_data := 'BIS_BIA_RSG_INV_ENABLED_FLAG';
1070 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1071 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_ENABLED_FLAG');
1072 END IF;
1073 RETURN;
1074 END IF;
1075
1076 --dbms_output.put_line('1');
1077
1078 --validate refresh mode
1079 loading_mode_validation(p_refresh_mode, x_return_status);
1080 IF (x_return_status = 'N') THEN
1081 x_msg_data := 'BIS_BIA_RSG_INV_LOADING_MODE';
1082 x_return_status := fnd_api.g_ret_sts_error;
1083 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1084 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_LOADING_MODE');
1085 END IF;
1086 RETURN;
1087 END IF;
1088
1089 --dbms_output.put_line('2');
1090
1091 x_return_status := FND_API.G_RET_STS_SUCCESS;
1092
1093 execute immediate 'update bis_obj_prog_linkages
1094 set enabled_flag = :1,
1095 refresh_mode = :2,
1096 last_updated_by = :3,
1097 last_update_login = :4,
1098 last_update_date = :5
1099 WHERE object_name = :6 AND object_type = :7
1100 AND CONC_PROGRAM_NAME = :8 AND CONC_APP_SHORT_NAME = :9'
1101 using p_enabled_flag, p_refresh_mode, g_curr_user_id, g_curr_login_id, Sysdate,
1102 p_object_name, p_object_type, p_conc_prog_name, p_appl_short_name;
1103
1104 --dbms_output.put_line('3');
1105
1106 -- IF (SQL%notfound) THEN
1107 -- x_return_status := FND_API.G_RET_STS_ERROR;
1108 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1109 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1110 -- END IF;
1111 -- x_msg_data := 'BIS_BIA_RSG_LINKAGE_NOT_EXISTS';
1112 --END IF;
1113
1114 --dbms_output.put_line('4');
1115
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1119 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1120 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1121 END IF;
1122 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1123 END update_linkage;
1124
1125
1126 PROCEDURE Update_Linkage_Enabled_Flag (
1127 P_OBJECT_TYPE in VARCHAR2,
1128 P_OBJECT_NAME in VARCHAR2,
1129 P_ENABLED_FLAG in VARCHAR2,
1130 P_CONC_PROG_NAME in VARCHAR2,
1131 P_APPL_SHORT_NAME in VARCHAR2,
1132 x_return_status OUT NOCOPY VARCHAR2,
1133 x_msg_data OUT NOCOPY VARCHAR2
1134 ) IS
1135 v_procedure_name VARCHAR2(50) := 'Update_Linkage_Enabled_Flag';
1136 BEGIN
1137 FND_MSG_PUB.initialize;
1138
1139 -- validate enabled_flag
1140 IF (p_enabled_flag IS NOT NULL AND p_enabled_flag <> 'Y'
1141 AND p_enabled_flag <> 'N') THEN
1142 x_return_status := fnd_api.g_ret_sts_error;
1143 x_msg_data := 'BIS_BIA_RSG_INV_ENABLED_FLAG';
1144 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1148 END IF;
1145 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_ENABLED_FLAG');
1146 END IF;
1147 RETURN;
1149
1150 x_return_status := FND_API.G_RET_STS_SUCCESS;
1151
1152 execute immediate 'update bis_obj_prog_linkages
1153 set enabled_flag = :1,
1154 last_updated_by = :2,
1155 last_update_login = :3,
1156 last_update_date = :4
1157 WHERE object_name = :5 AND object_type = :6
1158 AND CONC_PROGRAM_NAME = :7 AND CONC_APP_SHORT_NAME = :8'
1159 using p_enabled_flag, g_curr_user_id, g_curr_login_id, Sysdate,
1160 p_object_name, p_object_type, p_conc_prog_name, p_appl_short_name;
1161
1162 --IF (SQL%notfound) THEN
1163 -- x_return_status := FND_API.G_RET_STS_ERROR;
1164 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1165 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1166 -- END IF;
1167 -- x_msg_data := 'BIS_BIA_RSG_LINKAGE_NOT_EXISTS';
1168 --END IF;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
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,v_procedure_name, NULL);
1175 END IF;
1176 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1177 END Update_Linkage_Enabled_Flag;
1178
1179
1180 PROCEDURE Update_Linkage_Refresh_Mode (
1181 P_OBJECT_TYPE in VARCHAR2,
1182 P_OBJECT_NAME in VARCHAR2,
1183 P_CONC_PROG_NAME in VARCHAR2,
1184 P_APPL_SHORT_NAME in VARCHAR2,
1185 p_refresh_mode IN VARCHAR2,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_data OUT NOCOPY VARCHAR2
1188 ) IS
1189 v_procedure_name VARCHAR2(50) := 'Update_Linkage_Refresh_Mode';
1190 BEGIN
1191 FND_MSG_PUB.initialize;
1192
1193 --validate refresh mode
1194 loading_mode_validation(p_refresh_mode, x_return_status);
1195 IF (x_return_status = 'N') THEN
1196 x_msg_data := 'BIS_BIA_RSG_INV_LOADING_MODE';
1197 x_return_status := fnd_api.g_ret_sts_error;
1198 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1199 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_LOADING_MODE');
1200 END IF;
1201 RETURN;
1202 END IF;
1203
1204 x_return_status := FND_API.G_RET_STS_SUCCESS;
1205
1206 execute immediate 'update bis_obj_prog_linkages
1207 set refresh_mode = :1,
1208 last_updated_by = :2,
1209 last_update_login = :3,
1210 last_update_date = :4
1211 WHERE object_name = :5 AND object_type = :6
1212 AND CONC_PROGRAM_NAME = :7 AND CONC_APP_SHORT_NAME = :8'
1213 using p_refresh_mode, g_curr_user_id, g_curr_login_id, Sysdate,
1214 p_object_name, p_object_type, p_conc_prog_name, p_appl_short_name;
1215
1216 --IF (SQL%notfound) THEN
1217 -- x_return_status := FND_API.G_RET_STS_ERROR;
1218 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1219 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1220 -- END IF;
1221 -- x_msg_data := 'BIS_BIA_RSG_LINKAGE_NOT_EXISTS';
1222 --END IF;
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1227 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1228 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1229 END IF;
1230 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1231 END update_linkage_refresh_mode;
1232
1233 PROCEDURE Delete_Linkage (
1234 P_OBJECT_TYPE in VARCHAR2,
1235 P_OBJECT_NAME in VARCHAR2,
1239 x_msg_data OUT NOCOPY VARCHAR2
1236 P_CONC_PROG_NAME in VARCHAR2,
1237 P_APPL_SHORT_NAME in VARCHAR2,
1238 x_return_status OUT NOCOPY VARCHAR2,
1240 ) IS
1241 v_procedure_name VARCHAR2(50) := 'Delete_Linkage';
1242 BEGIN
1243 FND_MSG_PUB.initialize;
1244 x_return_status := FND_API.G_RET_STS_SUCCESS;
1245
1246 execute immediate 'delete from bis_obj_prog_linkages
1247 WHERE object_name = :1 AND object_type = :2
1248 AND conc_program_name = :3 AND conc_app_short_name = :4'
1249 using p_object_name, p_object_type,
1250 p_conc_prog_name, p_appl_short_name;
1251
1252 --IF (SQL%notfound) THEN
1253 -- x_return_status := FND_API.G_RET_STS_ERROR;
1254 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1255 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1256 -- END IF;
1257 -- x_msg_data := 'BIS_BIA_RSG_LINKAGE_NOT_EXISTS';
1258 --END IF;
1259
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1263 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1264 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1265 END IF;
1266 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1267 END Delete_Linkage;
1268
1269 --Added for bug 4606455
1270 -- is to delete linkage based on rowid instead of object name
1271 -- this will in turn call the above function
1272 PROCEDURE Delete_Linkage (
1273 P_ROWID in ROWID,
1274 x_return_status OUT NOCOPY VARCHAR2,
1275 x_msg_data OUT NOCOPY VARCHAR2
1276 ) IS
1277 l_object_type VARCHAR2(30);
1278 l_object_name VARCHAR2(480);
1279 l_conc_prog_name VARCHAR2(30);
1280 l_appl_short_name VARCHAR2(50);
1281 cursor get_names(rid varchar2) is
1282 select object_name,object_type,conc_program_name,conc_app_short_name
1283 from bis_obj_prog_linkages where rowid = rid;
1284 BEGIN
1285 open get_names(P_ROWID);
1286 fetch get_names into l_object_name,l_object_type,l_conc_prog_name,l_appl_short_name;
1287 if get_names%NOTFOUND then
1288 close get_names;
1289 return;
1290 end if;
1291 close get_names;
1292
1293 delete_linkage(l_object_type,l_object_name,l_conc_prog_name,l_appl_short_name,
1294 x_return_status,x_msg_data);
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1299 END Delete_Linkage;
1300
1301 PROCEDURE Delete_Obj_Linkages (
1302 P_OBJECT_TYPE in VARCHAR2,
1303 P_OBJECT_NAME in VARCHAR2,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_data OUT NOCOPY VARCHAR2
1306 ) IS
1307 v_procedure_name VARCHAR2(50) := 'Delete_Obj_Linkages';
1308 BEGIN
1309 FND_MSG_PUB.initialize;
1310 x_return_status := FND_API.G_RET_STS_SUCCESS;
1311
1312 execute immediate 'delete from bis_obj_prog_linkages
1313 WHERE object_name = :1 AND object_type = :2'
1314 using p_object_name, p_object_type;
1315
1316 --IF (SQL%notfound) THEN
1317 -- x_return_status := FND_API.G_RET_STS_ERROR;
1318 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1319 -- FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1320 -- END IF;
1321 -- x_msg_data := 'BIS_BIA_RSG_NO_OBJ_LINKAGES';
1322 --END IF;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1327 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1328 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1329 END IF;
1330 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1331 END Delete_Obj_Linkages;
1332
1333
1334 --begin: added for enhancement bug 3686273
1335 -- In order to fix bug 3867557, this function will get all the ancestor objects for the given
1336 -- dependent object, considering both enabled and disabled dependencies
1337 FUNCTION GetParentObjects(P_DEP_OBJ_NAME IN VARCHAR2,
1338 P_DEP_OBJ_TYPE IN VARCHAR2,
1339 P_OBJ_TYPE IN VARCHAR2,
1340 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1341 X_MSG_DATA OUT NOCOPY VARCHAR2
1342 ) RETURN t_bia_rsg_obj_table IS
1343 v_procedure_name VARCHAR2(50) := 'GetParentObjects';
1344
1345 l_obj_rec t_bia_rsg_obj_rec;
1346 x_parent_obj_table t_bia_rsg_obj_table;
1347 l_count INTEGER;
1348 l_index INTEGER;
1349
1350 CURSOR c_parent_objs (p_dep_obj_name VARCHAR2, p_dep_obj_type VARCHAR2, p_obj_type VARCHAR2) IS
1351 SELECT object_name, user_object_name, object_owner
1352 FROM (SELECT object_name, user_object_name, object_type, object_owner, depend_object_name, depend_object_type
1353 FROM bis_obj_dependency_v
1354 -- get rid of the filter for bug 3867557, i.e., conside both enabled and disabled dependencies
1355 --WHERE enabled_flag = 'Y'
1356 )
1357 WHERE object_type = p_obj_type
1358 START WITH depend_object_name = p_dep_obj_name AND depend_object_type = p_dep_obj_type
1359 CONNECT BY PRIOR object_name = depend_object_name AND PRIOR object_type = depend_object_type;
1360 BEGIN
1361 FND_MSG_PUB.initialize;
1362 x_return_status := FND_API.G_RET_STS_SUCCESS;
1363
1364 IF (p_dep_obj_name IS NULL OR p_dep_obj_name = '') THEN
1365 x_msg_data := 'BIS_BIA_RSG_INV_DEP_OBJ_NAME';
1366 x_return_status := fnd_api.g_ret_sts_error;
1367 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1368 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_DEP_OBJ_NAME');
1369 END IF;
1370 RETURN x_parent_obj_table;
1371 END IF;
1372
1373 IF (p_dep_obj_type IS NULL OR
1374 p_dep_obj_type NOT IN ('TABLE','VIEW', 'MV', 'PORTLET', 'PAGE', 'REPORT')) THEN
1375 x_msg_data := 'BIS_BIA_RSG_INV_DEP_OBJ_TYPE';
1376 x_return_status := fnd_api.g_ret_sts_error;
1377 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1378 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_DEP_OBJ_TYPE');
1379 END IF;
1380 RETURN x_parent_obj_table;
1381 END IF;
1382
1383 IF (p_obj_type IS NULL OR
1384 p_obj_type NOT IN ('TABLE','VIEW', 'MV', 'PORTLET', 'PAGE', 'REPORT')) THEN
1385 x_msg_data := 'BIS_BIA_RSG_INV_OBJ_TYPE';
1386 x_return_status := fnd_api.g_ret_sts_error;
1387 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR)) THEN
1388 fnd_msg_pub.add_exc_msg(g_pkg_name, v_procedure_name, 'BIS_BIA_RSG_INV_OBJ_TYPE');
1389 END IF;
1390 RETURN x_parent_obj_table;
1391 END IF;
1392
1393 l_count := 0;
1394
1395 OPEN c_parent_objs (p_dep_obj_name, p_dep_obj_type, p_obj_type);
1396 LOOP
1397 FETCH c_parent_objs INTO l_obj_rec;
1398 EXIT WHEN c_parent_objs%notfound;
1399 x_parent_obj_table(l_count) := l_obj_rec;
1400 l_count := l_count + 1;
1401 END LOOP;
1402
1403 RETURN x_parent_obj_table;
1404
1405 EXCEPTION
1406 WHEN OTHERS THEN
1407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1408 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1409 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,v_procedure_name, NULL);
1410 END IF;
1411 x_msg_data := 'BIS_BIA_RSG_UNEXP_ERROR';
1412 RETURN x_parent_obj_table;
1413 END;
1414
1415
1416 -- end: enhancement bug 3686273
1417
1418 -- begin: enhancement bug 3999642
1419
1420 procedure enable_index_mgmt(p_mv_name in varchar2, p_mv_schema in varchar2)
1421 is
1422 begin
1423 BIS_BIA_RSG_INDEX_MGMT.enable_index_mgmt(p_mv_name, p_mv_schema);
1424 end;
1425
1426
1427 procedure disable_index_mgmt(p_mv_name in varchar2, p_mv_schema in varchar2)
1428 is
1429 begin
1430 BIS_BIA_RSG_INDEX_MGMT.disable_index_mgmt(p_mv_name, p_mv_schema);
1431 end;
1432
1433 -- end: enhancement bug 3999642
1434
1435 END bis_rsg_pub_api_pkg;
[Home] [Help]