1 PACKAGE BODY bis_impl_dev_pkg AS
2 /* $Header: BISCONCB.pls 120.10 2007/12/27 12:44:55 lbodired ship $ */
3 version CONSTANT CHAR (80)
4 := '$Header: BISCONCB.pls 120.10 2007/12/27 12:44:55 lbodired ship $';
5
6 -- ------------------------------------------------------------
7 -- -------------- Global variables and constants --------------
8 -- ------------------------------------------------------------
9 G_PKG_NAME VARCHAR2(30) := 'BIS_IMPL_DEV_PKG';
10 g_current_user_id NUMBER := FND_GLOBAL.User_id;
11 g_current_login_id NUMBER := FND_GLOBAL.Login_id;
12
13 function clob_to_varchar2 (
14 p_in clob,
15 p_size integer
16 ) return varchar2
17 is
18 l_result varchar2(32767) := '';
19 l_amount integer := 0;
20 l_offset integer := 1;
21 begin
22 if (p_in is null or
23 DBMS_LOB.getlength(p_in) = 0 ) then
24 return '';
25 else
26 l_amount := p_size;
27 DBMS_LOB.READ(p_in, l_amount, l_offset, l_result);
28 return l_result;
29 end if;
30 end;
31
32
33 procedure Create_Linkage_Inner (
34 P_OBJECT_TYPE in VARCHAR2,
35 P_OBJECT_OWNER in VARCHAR2,
36 P_OBJECT_NAME in VARCHAR2,
37 P_CONC_PROGRAM_NAME in VARCHAR2,
38 P_CONC_APP_ID in NUMBER,
39 P_CONC_APP_SHORT_NAME in VARCHAR2,
40 P_ENABLED_FLAG in VARCHAR2,
41 P_REFRESH_MODE in VARCHAR2,
42 P_CREATED_BY in NUMBER := null,
43 P_CREATION_DATE in DATE := null,
44 P_LAST_UPDATED_BY in NUMBER := null,
45 P_LAST_UPDATE_LOGIN in NUMBER := null,
46 P_LAST_UPDATE_DATE in DATE := null,
47 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
48 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_errorcode OUT NOCOPY NUMBER,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2
53 ) is
54 l_sysdate DATE := sysdate;
55 l_api_name CONSTANT VARCHAR2(30) := 'Create_Linkage';
56 l_created_by NUMBER := nvl(P_CREATED_BY,g_current_user_id);
57 l_creation_date DATE := nvl(P_CREATION_DATE, l_Sysdate);
58 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
59 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
60 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
61
62 cursor c_in_properties(p_object_name varchar2, p_object_type varchar2) is
63 select object_name
64 from bis_obj_properties
65 where object_name = p_object_name
66 and object_type = p_object_type;
67
68 l_object_name bis_obj_properties.object_name%type;
69
70 begin
71 -- Initialize API message list if necessary.
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF FND_API.to_Boolean( p_init_msg_list ) THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76
77 insert into bis_obj_prog_linkages (
78 OBJECT_TYPE,
79 OBJECT_OWNER,
80 OBJECT_NAME,
81 CONC_PROGRAM_NAME,
82 CONC_APP_ID,
83 CONC_APP_SHORT_NAME,
84 ENABLED_FLAG,
85 REFRESH_MODE,
86 CREATED_BY,
87 CREATION_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN,
90 LAST_UPDATE_DATE)
91 values(
92 P_OBJECT_TYPE,
93 P_OBJECT_OWNER,
94 P_OBJECT_NAME,
95 P_CONC_PROGRAM_NAME,
96 P_CONC_APP_ID,
97 P_CONC_APP_SHORT_NAME,
98 P_ENABLED_FLAG,
99 P_REFRESH_MODE,
100 L_CREATED_BY,
101 L_CREATION_DATE,
102 L_LAST_UPDATED_BY,
103 L_LAST_UPDATE_LOGIN,
104 L_LAST_UPDATE_DATE);
105
106 /* create new rows in bis_obj_properties if it is a new object */
107
108 /* temporarily remove this logic, leave this change to 4.0.9
109 open c_in_properties(p_object_name, p_object_type);
110 fetch c_in_properties into l_object_name;
111 if c_in_properties%NOTFOUND then
112 create_properties(
113 P_OBJECT_TYPE => p_object_type,
114 P_OBJECT_NAME => p_object_name,
115 P_OBJECT_OWNER => p_object_owner,
116 P_SNAPSHOT_LOG_SQL => null,
117 P_FAST_REFRESH_FLAG => null,
118 P_DIMENSION_FLAG => null,
119 x_return_status => x_return_status,
120 x_errorcode => x_errorcode,
121 x_msg_count => x_msg_count,
122 x_msg_data => x_msg_data
123 );
124 end if;
125 close c_in_properties;
126 */
127
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 -----------------------------------
131 -- Make a standard call to get message count
132 -- and if count is 1, get message info.
133 -- The client will directly display the x_msg_data (which is already
134 -- translated) if the x_msg_count = 1;
135 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
136 -- Server-side procedure to access the messages, and consolidate them
137 -- and display them all at once or display one message after another.
138
139 FND_MSG_PUB.Count_And_Get
140 ( p_count => x_msg_count,
141 p_data => x_msg_data
142 );
143
144 exception
145 when no_data_found then
146 return;
147 when others then
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149 IF FND_MSG_PUB.Check_Msg_Level
150 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
151 THEN
152 FND_MSG_PUB.Add_Exc_Msg
153 ( G_PKG_NAME,
154 l_api_name
155 );
156 END IF;
157 FND_MSG_PUB.Count_And_Get
158 ( p_count => x_msg_count,
159 p_data => x_msg_data
160 );
161 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
162 raise;
163 end Create_Linkage_Inner;
164
165
166 procedure Create_Linkage (
167 P_OBJECT_TYPE in VARCHAR2,
168 P_OBJECT_OWNER in VARCHAR2,
169 P_OBJECT_NAME in VARCHAR2,
170 P_CONC_PROGRAM_NAME in VARCHAR2,
171 P_CONC_APP_ID in NUMBER,
172 P_CONC_APP_SHORT_NAME in VARCHAR2,
173 P_ENABLED_FLAG in VARCHAR2,
174 P_REFRESH_MODE in VARCHAR2,
175 P_CREATED_BY in NUMBER := null,
176 P_CREATION_DATE in DATE := null,
177 P_LAST_UPDATED_BY in NUMBER := null,
178 P_LAST_UPDATE_LOGIN in NUMBER := null,
179 P_LAST_UPDATE_DATE in DATE := null,
180 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
181 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_errorcode OUT NOCOPY NUMBER,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2
186 ) is
187 begin
188 Create_Linkage_Inner (
189 P_OBJECT_TYPE,
190 P_OBJECT_OWNER,
191 P_OBJECT_NAME,
192 P_CONC_PROGRAM_NAME,
193 P_CONC_APP_ID,
194 P_CONC_APP_SHORT_NAME,
195 P_ENABLED_FLAG,
196 P_REFRESH_MODE,
197 P_CREATED_BY,
198 P_CREATION_DATE,
199 P_LAST_UPDATED_BY,
200 P_LAST_UPDATE_LOGIN,
201 P_LAST_UPDATE_DATE,
202 p_init_msg_list,
203 p_commit,
204 x_return_status,
205 x_errorcode,
206 x_msg_count,
207 x_msg_data
208 );
209 exception
210 when others then
211 null;
212 end Create_Linkage;
213
214
215 procedure Update_Linkage_Inner (
216 P_ROWID in ROWID,
217 P_OBJECT_TYPE in VARCHAR2,
218 P_OBJECT_OWNER in VARCHAR2,
219 P_OBJECT_NAME in VARCHAR2,
220 P_CONC_PROGRAM_NAME in VARCHAR2,
221 P_CONC_APP_ID in NUMBER,
222 P_CONC_APP_SHORT_NAME in VARCHAR2,
223 P_ENABLED_FLAG in VARCHAR2,
224 P_REFRESH_MODE in VARCHAR2,
225 P_LAST_UPDATED_BY in NUMBER := null,
226 P_LAST_UPDATE_LOGIN in NUMBER := null,
227 P_LAST_UPDATE_DATE in DATE := null,
228 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
229 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_errorcode OUT NOCOPY NUMBER,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2
234 ) is
235 l_sysdate DATE := sysdate;
236 l_api_name CONSTANT VARCHAR2(30) := 'Update_Linkage';
237 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
238 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
239 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
240 begin
241 -- Initialize API message list if necessary.
242 -- Initialize message list if p_init_msg_list is set to TRUE.
243 IF FND_API.to_Boolean( p_init_msg_list ) THEN
244 FND_MSG_PUB.initialize;
245 END IF;
246
247 if (P_ROWID is not null) then
248 update bis_obj_prog_linkages
249 set enabled_flag = P_ENABLED_FLAG,
250 conc_program_name = P_CONC_PROGRAM_NAME,
251 conc_app_id = P_CONC_APP_ID,
252 conc_app_short_name = P_CONC_APP_SHORT_NAME,
253 refresh_mode = P_REFRESH_MODE,
254 last_updated_by = l_last_updated_by,
255 last_update_login = l_last_update_login,
256 last_update_date = l_last_update_date
257 where rowid = P_ROWID;
258 else
259 UPDATE BIS_OBJ_PROG_LINKAGES
260 SET
261 ENABLED_FLAG = P_ENABLED_FLAG,
262 REFRESH_MODE = P_REFRESH_MODE,
263 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
264 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
265 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
266 WHERE OBJECT_TYPE = P_OBJECT_TYPE
267 AND OBJECT_NAME = P_OBJECT_NAME
268 AND OBJECT_OWNER = P_OBJECT_OWNER
269 AND CONC_PROGRAM_NAME = P_CONC_PROGRAM_NAME
270 AND CONC_APP_ID = P_CONC_APP_ID;
271 END IF;
272
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274
275 -----------------------------------
276 -- Make a standard call to get message count
277 -- and if count is 1, get message info.
278 -- The client will directly display the x_msg_data (which is already
279 -- translated) if the x_msg_count = 1;
280 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
281 -- Server-side procedure to access the messages, and consolidate them
282 -- and display them all at once or display one message after another.
283
284 FND_MSG_PUB.Count_And_Get
285 ( p_count => x_msg_count,
286 p_data => x_msg_data
287 );
288
289 exception
290 when no_data_found then
291 return;
292 when others then
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 IF FND_MSG_PUB.Check_Msg_Level
295 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
296 THEN
297 FND_MSG_PUB.Add_Exc_Msg
298 ( G_PKG_NAME,
299 l_api_name
300 );
301 END IF;
302 FND_MSG_PUB.Count_And_Get
303 ( p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
307 raise;
308 end Update_Linkage_Inner;
309
310 procedure Update_Linkage (
311 P_ROWID in ROWID,
312 P_OBJECT_TYPE in VARCHAR2,
313 P_OBJECT_OWNER in VARCHAR2,
314 P_OBJECT_NAME in VARCHAR2,
315 P_CONC_PROGRAM_NAME in VARCHAR2,
316 P_CONC_APP_ID in NUMBER,
317 P_CONC_APP_SHORT_NAME in VARCHAR2,
318 P_ENABLED_FLAG in VARCHAR2,
319 P_REFRESH_MODE in VARCHAR2,
320 P_LAST_UPDATED_BY in NUMBER := null,
321 P_LAST_UPDATE_LOGIN in NUMBER := null,
322 P_LAST_UPDATE_DATE in DATE := null,
323 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
324 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_errorcode OUT NOCOPY NUMBER,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2
329 ) is
330 begin
331 Update_Linkage_Inner (
332 P_ROWID,
333 P_OBJECT_TYPE,
334 P_OBJECT_OWNER,
335 P_OBJECT_NAME,
336 P_CONC_PROGRAM_NAME,
337 P_CONC_APP_ID,
338 P_CONC_APP_SHORT_NAME,
339 P_ENABLED_FLAG,
340 P_REFRESH_MODE,
341 P_LAST_UPDATED_BY,
342 P_LAST_UPDATE_LOGIN,
343 P_LAST_UPDATE_DATE,
344 p_init_msg_list,
345 p_commit,
346 x_return_status,
347 x_errorcode,
348 x_msg_count,
349 x_msg_data
350 );
351 exception
352 when others then
353 null;
354 end Update_Linkage;
355
356
357
358 procedure Delete_Linkage (
359 P_ROWID in ROWID
360 ) IS
361 BEGIN
362
363 delete from bis_obj_prog_linkages
364 where rowid = P_ROWID;
365 If (SQL%NOTFOUND) then
366 RAISE NO_DATA_FOUND;
367 End If;
368
369 end Delete_Linkage;
370
371
372 -- new implementation for removal of _OA
373 -- Note that x_is_oa_page is only meaningful as input parameter
374 -- when x_object_name as input parameter is not null
375 PROCEDURE page_name_validation (
376 P_OBJECT_TYPE IN VARCHAR2,
377 P_USER_OBJECT_NAME IN VARCHAR2,
378 X_OBJECT_NAME IN OUT NOCOPY VARCHAR2,
379 X_IS_OA_PAGE IN OUT NOCOPY VARCHAR2,
380 X_RETURN_STATUS OUT NOCOPY VARCHAR2
381 ) IS
382 l_object_name bis_obj_dependency.object_name%TYPE;
383 l_user_object_name VARCHAR2(480); --Enhancement 4106617
384 i NUMBER := 0;
385 j NUMBER := 0;
386 l_is_oa_page VARCHAR2(1);
387
388 -- new implementation to uptake the new page query to handle A and A_OA case
389 CURSOR c_page(p_user_object_name VARCHAR2, p_object_name VARCHAR2, p_is_oa_page VARCHAR2) IS
390 SELECT object_name, user_object_name, oa_page
391 FROM (-- portal pages existing in RSG
392 (select DISTINCT dep.object_name object_name, func.user_function_name user_object_name, 'N' oa_page
393 from fnd_form_functions_vl func, bis_obj_dependency dep
394 where upper(web_html_call)='ORACLESSWA.SWITCHPAGE'
395 and dep.object_type = 'PAGE'
396 and dep.object_name = substr(parameters,10))
397 UNION ALL
398 -- oa page existing in RSG repository
399 (select DISTINCT dep.object_name object_name, func.user_function_name user_object_name, 'Y' oa_page
400 from fnd_form_functions_vl func, bis_obj_dependency dep
401 where upper(func.web_html_call) LIKE '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
402 and dep.object_type = 'PAGE'
403 and bis_impl_dev_pkg.get_function_by_page(dep.object_name) = func.function_name)
404 UNION ALL
405 -- oa page not in RSG repository
406 select func.function_name object_name, func.user_function_name user_object_name, 'Y' oa_page
407 from fnd_form_functions_vl func
408 where upper(func.web_html_call) LIKE '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
409 and func.function_name not in (select f.function_name
410 from fnd_form_functions f, bis_obj_dependency dep
414 UNION ALL
411 where upper(f.web_html_call) LIKE '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
412 and dep.object_type = 'PAGE'
413 and bis_impl_dev_pkg.get_function_by_page(dep.object_name) = f.function_name)
415 -- page in RSG repository but w/o corresponding form function defined
416 (select DISTINCT objdep.object_name object_name, /*objdep.user_object_name user_object_name*/ objdep.object_name user_object_name, 'N' oa_page
417 --from bis_obj_dependency_v objdep
418 from bis_obj_dependency objdep
419 where objdep.object_type = 'PAGE'
420 and not exists (select 1 from fnd_form_functions func
421 where (upper(func.web_html_call) = 'ORACLESSWA.SWITCHPAGE'
422 and substr(func.parameters,10) = objdep.object_name)
423 /* or (upper(func.web_html_call) LIKE '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
424 and (func.function_name = objdep.object_name
425 or func.function_name||'_OA' = objdep.object_name))))
426 */
427 or (func.web_html_call like 'OA.jsp?akRegionCode=BIS_COMPONENT_PAGE'||'&'||'akRegionApplicationId=191%'
428 and (func.function_name = objdep.object_name
429 or (objdep.object_name like '%_OA'
430 and func.function_name = SUBSTR(object_name, 1, LENGTH(object_name) - 3))))))
431 )
432 WHERE object_name LIKE p_object_name
433 AND user_object_name LIKE p_user_object_name
434 AND oa_page LIKE p_is_oa_page;
435
436 begin
437 X_RETURN_STATUS := 'Y'; --default as Y.
438
439 -- none of p_object_type and p_user_object_name can be null
440 if (P_OBJECT_TYPE is null) or (p_object_type <> 'PAGE') or (P_USER_OBJECT_NAME is null) then
441 X_RETURN_STATUS := 'N';
442 return;
443 end if;
444
445 -- x_is_oa_page is only meaningful when x_object_name is not null
446 if (x_object_name is null) then
447 x_is_oa_page := null;
448 else -- when x_object_name is not null, x_is_oa_page cannot be null either
449 if (x_is_oa_page is null) then
450 x_return_status := 'N';
451 return;
452 end if;
453 end if;
454
455 -- new implementation
456 IF (x_object_name IS NULL) THEN
457 OPEN c_page(p_user_object_name, '%', '%');
458 ELSE
459 OPEN c_page(p_user_object_name, x_object_name, x_is_oa_page);
460 END IF;
461
462 loop
463 fetch c_page into l_object_name, l_user_object_name, l_is_oa_page;
464 exit when c_page%NOTFOUND;
465 end loop;
466
467 i := c_page%ROWCOUNT;
468 close c_page;
469
470 IF (i = 0) THEN -- no row is fetched
471 x_return_status := 'N';
472 ELSIF (i = 1) then -- exactly one row is fetched
473 x_return_status := 'Y';
474 ELSE -- multiple rows are fetched
475 x_return_status := 'Y';
476 l_object_name := NULL;
477 END IF;
478
479 x_object_name := l_object_name;
480 x_is_oa_page := l_is_oa_page;
481 EXCEPTION
482 WHEN OTHERS THEN
483 x_return_status := 'N';
484 end page_name_validation;
485
486
487 procedure object_name_validation (
488 P_OBJECT_TYPE IN VARCHAR2,
489 P_USER_OBJECT_NAME IN VARCHAR2,
490 X_OBJECT_NAME IN OUT NOCOPY VARCHAR2,
491 X_RETURN_STATUS OUT NOCOPY VARCHAR2
492 ) is
493 l_object_name bis_obj_dependency.object_name%TYPE;
494 i NUMBER;
495
496 ---fix bug 4067976. Added type WEBPORTLETX for portlet form
497 ---function
498 cursor c_portlet_function(p_user_object_name VARCHAR2) is
499 select function_name
500 from fnd_form_functions_vl
501 where type in ('WEBPORTLET','WEBPORTLETX')
502 and user_function_name = p_user_object_name;
503
504 cursor c_report_function(p_user_object_name VARCHAR2) is
505 select function_name
506 from fnd_form_functions_vl
507 where type in ('WWW','JSP') --modified for bug 4717956
508 and user_function_name = p_user_object_name;
509
510 cursor c_portlet_function_name(p_user_object_name VARCHAR2, p_function_name VARCHAR2) IS
511 select function_name
512 from fnd_form_functions_vl
513 where type in ('WEBPORTLET','WEBPORTLETX')
514 and user_function_name = p_user_object_name
515 and function_name = p_function_name;
516
517 cursor c_report_function_name(p_user_object_name VARCHAR2, p_function_name VARCHAR2) IS
518 select function_name
519 from fnd_form_functions_vl
520 where type in ('WWW','JSP') ---modified for bug 4717956
521 and user_function_name = p_user_object_name
522 and function_name = p_function_name;
523
524 cursor c_rsg_object(p_object_type varchar2, p_user_object_name varchar2) is
525 -- bis_obj_properties
526 (SELECT prop.object_name FROM bis_obj_properties prop
527 WHERE bis_impl_dev_pkg.get_user_object_name(prop.object_type,prop.object_name) = p_user_object_name
528 AND prop.object_type = p_object_type)
529 UNION ALL
530 -- bis_obj_dependency object
531 (select distinct d.object_name
532 from bis_obj_dependency_v d
533 where d.object_type = p_object_type
534 and d.user_object_name = p_user_object_name
535 AND NOT exists (SELECT 1 FROM bis_obj_properties prop
539 -- bis_obj_dependency depend_object
536 WHERE prop.object_type = d.object_type
537 AND prop.object_name = d.object_name))
538 UNION ALL
540 (SELECT DISTINCT d.depend_object_name
541 FROM bis_obj_dependency_v d
542 WHERE d.depend_object_type = p_object_type
543 AND d.user_depend_object_name = p_user_object_name
544 AND NOT exists (SELECT 1 FROM bis_obj_properties prop
545 WHERE prop.object_type = d.object_type
546 AND prop.object_name = d.object_name)
547 AND NOT exists (SELECT 1 FROM bis_obj_dependency dep
548 WHERE dep.object_type = d.depend_object_type
549 AND dep.object_name = d.depend_object_name))
550 UNION ALL
551 -- bis_obj_prog_linkages object
552 (SELECT DISTINCT l.object_name
553 FROM bis_obj_prog_linkages l
554 WHERE bis_impl_dev_pkg.get_user_object_name(l.object_type, l.object_name) = p_user_object_name
555 AND l.object_type = p_object_type
556 AND NOT exists (SELECT 1 FROM bis_obj_properties prop
557 WHERE prop.object_type = l.object_type
558 AND prop.object_name = l.object_name)
559 AND NOT exists (SELECT 1 FROM bis_obj_dependency d
560 WHERE (l.object_type = d.object_type AND l.object_name = d.object_name)
561 OR (l.object_type = d.depend_object_type AND l.object_name = d.depend_object_name)));
562
563 cursor c_rsg_object_name(p_object_type varchar2, p_user_object_name varchar2, p_object_name varchar2) is
564 -- bis_obj_properties
565 (SELECT prop.object_name FROM bis_obj_properties prop
566 WHERE bis_impl_dev_pkg.get_user_object_name(prop.object_type,prop.object_name) = p_user_object_name
567 AND prop.object_type = p_object_type
568 AND prop.object_name = p_object_name)
569 UNION ALL
570 -- bis_obj_dependency object
571 (select distinct d.object_name
572 from bis_obj_dependency_v d
573 where d.object_type = p_object_type
574 and d.user_object_name = p_user_object_name
575 AND d.object_name = p_object_name
576 AND NOT exists (SELECT 1 FROM bis_obj_properties prop
577 WHERE prop.object_type = d.object_type
578 AND prop.object_name = d.object_name))
579 UNION ALL
580 -- bis_obj_dependency depend_object
581 (SELECT DISTINCT d.depend_object_name
582 FROM bis_obj_dependency_v d
583 WHERE d.depend_object_type = p_object_type
584 AND d.depend_object_name = p_object_name
585 AND d.user_depend_object_name = p_user_object_name
586 AND NOT exists (SELECT 1 FROM bis_obj_properties prop
587 WHERE prop.object_type = d.object_type
588 AND prop.object_name = d.object_name)
589 AND NOT exists (SELECT 1 FROM bis_obj_dependency dep
590 WHERE dep.object_type = d.depend_object_type
591 AND dep.object_name = d.depend_object_name))
592 UNION ALL
593 -- bis_obj_prog_linkages object
594 (SELECT DISTINCT l.object_name
595 FROM bis_obj_prog_linkages l
596 WHERE bis_impl_dev_pkg.get_user_object_name(l.object_type, l.object_name) = p_user_object_name
597 AND l.object_type = p_object_type
598 AND l.object_name = p_object_name
599 AND NOT exists (SELECT 1 FROM bis_obj_properties prop
600 WHERE prop.object_type = l.object_type
601 AND prop.object_name = l.object_name)
602 AND NOT exists (SELECT 1 FROM bis_obj_dependency d
603 WHERE (l.object_type = d.object_type AND l.object_name = d.object_name)
604 OR (l.object_type = d.depend_object_type AND l.object_name = d.depend_object_name)));
605
606 begin
607 X_RETURN_STATUS := 'Y'; --default as Y.
608
609 if (P_OBJECT_TYPE is null) or (P_USER_OBJECT_NAME is null) then
610 X_RETURN_STATUS := 'N';
611 return;
612 end if;
613
614 --First check if the object exists in RSG
615 if (x_object_name is null) then
616 open c_rsg_object(P_OBJECT_TYPE, P_USER_OBJECT_NAME);
617 loop
618 FETCH C_RSG_OBJECT into x_object_name;
619 exit when C_RSG_OBJECT%NOTFOUND;
620 end loop;
621
622 i := C_RSG_OBJECT%ROWCOUNT;
623 if (i=1) then --exactly one row was fetched
624 X_RETURN_STATUS := 'Y';
625 CLOSE C_RSG_OBJECT;
626 return;
627 elsif (i>1) then --more than one row was fetched
628 X_RETURN_STATUS := 'Y';
629 X_OBJECT_NAME := null; -- indicate more than one row is fetched.
630 CLOSE C_RSG_OBJECT;
631 return;
632 end if;
633 close C_RSG_OBJECT;
634 else
635 open c_rsg_object_name(P_OBJECT_TYPE, P_USER_OBJECT_NAME, X_OBJECT_NAME);
636 fetch c_rsg_object_name into x_object_name;
637 if c_rsg_object_name%FOUND then
638 X_RETURN_STATUS := 'Y';
639 close c_rsg_object_name;
640 return;
641 end if;
642 close c_rsg_object_name;
643 end if;
644
645
646 -- If object doesn't exists in RSG, validate against data source
647 -- remove db object validation
648 if P_OBJECT_TYPE = 'TABLE' OR p_object_type = 'VIEW'
649 OR p_object_type = 'MV' OR p_object_type='AWCUBE' THEN
650 x_return_status := 'Y';
651 x_object_name := p_user_object_name;
652 elsif P_OBJECT_TYPE= 'PORTLET' then
653 if (X_OBJECT_NAME is null) then
654 open c_portlet_function(p_user_object_name);
655 loop
659
656 FETCH C_portlet_FUNCTION into x_object_name;
657 exit when C_portlet_FUNCTION%NOTFOUND;
658 end loop;
660 i := C_portlet_FUNCTION%ROWCOUNT;
661 if (i=0) then --no rows were fetched
662 X_RETURN_STATUS := 'N';
663 elsif (i=1) then --exactly one row was fetched
664 X_RETURN_STATUS := 'Y';
665 elsif (i>1) then --more than one row was fetched
666 X_RETURN_STATUS := 'Y';
667 X_OBJECT_NAME := null; -- indicate more than one row is fetched.
668 end if;
669
670 close c_portlet_function;
671
672 elsif (X_OBJECT_NAME is not null) then
673 open c_portlet_function_name(p_user_object_name, x_object_name);
674 FETCH C_portlet_FUNCTION_NAME into x_object_name;
675 if C_portlet_FUNCTION_NAME%NOTFOUND then
676 X_RETURN_STATUS := 'N';
677 end if;
678 close C_portlet_FUNCTION_NAME;
679 END IF;
680 elsif P_OBJECT_TYPE='REPORT' THEN
681 if (X_OBJECT_NAME is null) then
682 open c_report_function(p_user_object_name);
683 loop
684 FETCH C_report_FUNCTION into x_object_name;
685 exit when C_report_FUNCTION%NOTFOUND;
686 end loop;
687
688 i := C_report_FUNCTION%ROWCOUNT;
689 if (i=0) then --no rows were fetched
690 X_RETURN_STATUS := 'N';
691 elsif (i=1) then --exactly one row was fetched
692 X_RETURN_STATUS := 'Y';
693 elsif (i>1) then --more than one row was fetched
694 X_RETURN_STATUS := 'Y';
695 X_OBJECT_NAME := null; -- indicate more than one row is fetched.
696 end if;
697
698 close c_report_function;
699
700 elsif (X_OBJECT_NAME is not null) then
701 open c_report_function_name(p_user_object_name, x_object_name);
702 FETCH C_report_FUNCTION_NAME into x_object_name;
703 if C_report_FUNCTION_NAME%NOTFOUND then
704 X_RETURN_STATUS := 'N';
705 end if;
706 close C_report_FUNCTION_NAME;
707 end if;
708
709 else
710 x_return_status := 'N';
711 end if;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 x_return_status := 'N' ;
716 end object_name_validation;
717
718 procedure object_owner_validation (
719 P_OBJECT_OWNER IN VARCHAR2,
720 X_RETURN_STATUS OUT NOCOPY VARCHAR2
721 ) is
722 l_object_owner VARCHAR2(50);
723 begin
724 X_RETURN_STATUS := 'Y'; --default as Y.
725
726 if (P_OBJECT_OWNER is null) then
727 X_RETURN_STATUS := 'N';
728 return;
729 end if;
730
731 SELECT APPLICATION_SHORT_NAME INTO l_object_owner
732 FROM FND_APPLICATION
733 WHERE APPLICATION_SHORT_NAME = P_OBJECT_OWNER;
734
735 IF SQL%NOTFOUND THEN
736 x_return_status := 'N';
737 END IF;
738 end object_owner_validation;
739
740
741 -- added to detect loop for enabled dependency in RSG
742 PROCEDURE dep_loop_validation (
743 p_object_type IN VARCHAR2,
744 p_object_name IN VARCHAR2,
745 p_dep_object_type IN VARCHAR2,
746 p_dep_object_name IN VARCHAR2,
747 p_enabled_flag IN VARCHAR2,
748 X_RETURN_STATUS OUT NOCOPY VARCHAR2
749 ) IS
750 CURSOR c_dep_loop (p_object_type VARCHAR2, p_object_name VARCHAR2, p_dep_object_type VARCHAR2, p_dep_object_name VARCHAR2) IS
751 SELECT obj_parents.object_name
752 FROM (SELECT object_type, object_name
753 -- bug 3492509: loop detection regardless of enabled flag condition
754 -- FROM (SELECT object_type, object_name, depend_object_type, depend_object_name FROM bis_obj_dependency WHERE enabled_flag = 'Y') d
755 FROM bis_obj_dependency d
756 START WITH d.depend_object_type = p_object_type AND d.depend_object_name = p_object_name
757 CONNECT BY PRIOR d.object_name = d.depend_object_name
758 AND PRIOR d.object_type = d.depend_object_type) obj_parents
759 WHERE obj_parents.object_type = p_dep_object_type
760 AND obj_parents.object_name = p_dep_object_name;
761 v_dummy_obj_name VARCHAR2(480); --Enhancement 4106617
762 BEGIN
763 --bug 3492509: loop detection regardless of enabled flag condition
764 --IF ( (p_enabled_flag IS NULL) OR Upper(p_enabled_flag) <> 'Y') THEN
765 -- x_return_status := 'Y';
766 -- RETURN;
767 --END IF;
768
769 --bug 3494363: dependent object cannot be the same as parent object
770 IF (p_object_name = p_dep_object_name AND p_object_type = p_dep_object_type) THEN
771 x_return_status := 'N';
772 RETURN;
773 END IF;
774
775 OPEN c_dep_loop(p_object_type, p_object_name, p_dep_object_type, p_dep_object_name);
776 LOOP
777 FETCH c_dep_loop INTO v_dummy_obj_name;
778 EXIT WHEN c_dep_loop%notfound;
779 END LOOP;
780
781 IF (c_dep_loop%rowcount > 0) THEN
782 x_return_status := 'N';
783 ELSE
784 x_return_status := 'Y';
785 END IF;
786
787 CLOSE c_dep_loop;
788
789 RETURN;
790 EXCEPTION
791 WHEN OTHERS THEN
792 x_return_status := 'N';
793 RETURN;
794 END dep_loop_validation;
795
796 procedure conc_program_validation (
797 P_USER_CONC_PROGRAM_NAME IN VARCHAR2,
798 X_CONC_APP_ID IN OUT NOCOPY NUMBER,
799 X_CONC_APP_SHORT_NAME OUT NOCOPY VARCHAR2,
803 i NUMBER := 0;
800 X_CONC_PROGRAM_NAME OUT NOCOPY VARCHAR2,
801 X_RETURN_STATUS OUT NOCOPY VARCHAR2
802 ) is
804
805 CURSOR C_PROGRAM_APP_ID(P_USER_CONC_PROGRAM_NAME VARCHAR2,P_CONC_APP_ID NUMBER) IS
806 SELECT conc.CONCURRENT_PROGRAM_NAME, appl.APPLICATION_SHORT_NAME
807 FROM FND_CONCURRENT_PROGRAMS_VL conc, FND_APPLICATION appl
808 WHERE conc.USER_CONCURRENT_PROGRAM_NAME = P_USER_CONC_PROGRAM_NAME
809 AND conc.APPLICATION_ID = P_CONC_APP_ID
810 AND conc.APPLICATION_ID = appl.APPLICATION_ID
811 AND conc.ENABLED_FLAG = 'Y';
812
813 CURSOR C_PROGRAM(P_USER_CONC_PROGRAM_NAME VARCHAR2) IS
814 SELECT conc.CONCURRENT_PROGRAM_NAME, conc.APPLICATION_ID, appl.APPLICATION_SHORT_NAME
815 FROM FND_CONCURRENT_PROGRAMS_VL conc, FND_APPLICATION appl
816 WHERE conc.USER_CONCURRENT_PROGRAM_NAME = P_USER_CONC_PROGRAM_NAME
817 AND conc.APPLICATION_ID = appl.APPLICATION_ID
818 AND conc.ENABLED_FLAG = 'Y';
819
820 begin
821 X_RETURN_STATUS := 'Y'; --default as Y.
822
823 if (P_USER_CONC_PROGRAM_NAME is null) or (X_RETURN_STATUS is null) then
824 X_RETURN_STATUS := 'N';
825
826 elsif (X_CONC_APP_ID is not null) then --derive conc_program_name only
827 OPEN C_PROGRAM_APP_ID(P_USER_CONC_PROGRAM_NAME,X_CONC_APP_ID);
828 FETCH C_PROGRAM_APP_ID into X_CONC_PROGRAM_NAME, X_CONC_APP_SHORT_NAME;
829 if C_PROGRAM_APP_ID%NOTFOUND then
830 X_RETURN_STATUS := 'N';
831 end if;
832 close C_PROGRAM_APP_ID;
833
834 elsif (X_CONC_APP_ID is null) then --derive both conc_program_name and conc_app_id
835 OPEN C_PROGRAM(P_USER_CONC_PROGRAM_NAME);
836 loop
837 FETCH C_PROGRAM into X_CONC_PROGRAM_NAME, X_CONC_APP_ID, X_CONC_APP_SHORT_NAME;
838 exit when C_PROGRAM%NOTFOUND;
839 end loop;
840
841 i := C_PROGRAM%ROWCOUNT;
842 if (i=0) then --no rows were fetched
843 X_RETURN_STATUS := 'N';
844 elsif (i=1) then --exactly one row was fetched
845 X_RETURN_STATUS := 'Y';
846 elsif (i>1) then --more than one row was fetched
847 X_RETURN_STATUS := 'Y';
848 X_CONC_APP_ID := -1; -- indicate more than one row is fetched.
849 X_CONC_PROGRAM_NAME := null;
850 X_CONC_APP_SHORT_NAME := null;
851 end if;
852
853 close C_PROGRAM;
854
855 else
856 X_RETURN_STATUS := 'N';
857 end if;
858
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 x_return_status := 'N' ;
862 WHEN OTHERS THEN
863 x_return_status := 'N' ;
864 end conc_program_validation;
865
866
867 FUNCTION Refresh_Program_Exists(
868 P_OBJECT_TYPE in VARCHAR2,
869 P_OBJECT_NAME in VARCHAR2) RETURN VARCHAR2
870 IS
871 Cursor C_Program(P_OBJECT_TYPE VARCHAR2, P_OBJECT_NAME VARCHAR2) IS
872 select distinct conc_program_name
873 from bis_obj_prog_linkages
874 where object_type = P_OBJECT_TYPE
875 and object_name = P_OBJECT_NAME
876 and enabled_flag = 'Y';
877
878
879 l_exists VARCHAR2(1) := 'N';
880 l_conc_program_name VARCHAR2(30);
881
882 BEGIN
883 OPEN C_Program(P_OBJECT_TYPE, P_OBJECT_NAME);
884 FETCH C_PROGRAM into l_conc_program_name;
885 if C_PROGRAM%FOUND then
886 l_exists := 'Y';
887 end if;
888 close C_PROGRAM;
889
890 return l_exists;
891 END Refresh_Program_Exists;
892
893
894 procedure Create_Dependency_Inner (
895 P_OBJECT_TYPE in VARCHAR2,
896 P_OBJECT_OWNER in VARCHAR2,
897 P_OBJECT_NAME in VARCHAR2,
898 P_ENABLED_FLAG in VARCHAR2,
899 P_DEPEND_OBJECT_TYPE in VARCHAR2,
900 P_DEPEND_OBJECT_OWNER in VARCHAR2,
901 P_DEPEND_OBJECT_NAME in VARCHAR2,
902 P_FROM_UI in VARCHAR2,
903 P_CREATED_BY in NUMBER := null,
904 P_CREATION_DATE in DATE := null,
905 P_LAST_UPDATED_BY in NUMBER := null,
906 P_LAST_UPDATE_LOGIN in NUMBER := null,
907 P_LAST_UPDATE_DATE in DATE := null,
908 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
909 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
910 x_return_status OUT NOCOPY VARCHAR2,
911 x_errorcode OUT NOCOPY NUMBER,
912 x_msg_count OUT NOCOPY NUMBER,
913 x_msg_data OUT NOCOPY VARCHAR2
914 ) IS
915 l_sysdate DATE := sysdate;
916 l_api_name CONSTANT VARCHAR2(30) := 'Create_Dependency';
917 l_created_by NUMBER := nvl(P_CREATED_BY,g_current_user_id);
918 l_creation_date DATE := nvl(P_CREATION_DATE, l_Sysdate);
919 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
920 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
921 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
922
923 cursor c_in_properties(p_object_name varchar2, p_object_type varchar2) is
924 select object_name
925 from bis_obj_properties
926 where object_name = p_object_name
927 and object_type = p_object_type;
928
929 l_object_name bis_obj_properties.object_name%type;
930 l_return_status varchar2(10);
931 l_errorcode number;
932 l_msg_count number;
933 l_msg_data varchar2(4000);
934 begin
938 FND_MSG_PUB.initialize;
935 -- Initialize API message list if necessary.
936 -- Initialize message list if p_init_msg_list is set to TRUE.
937 IF FND_API.to_Boolean( p_init_msg_list ) THEN
939 END IF;
940
941 insert into bis_obj_dependency (
942 OBJECT_TYPE,
943 OBJECT_OWNER,
944 OBJECT_NAME,
945 ENABLED_FLAG,
946 DEPEND_OBJECT_TYPE,
947 DEPEND_OBJECT_OWNER,
948 DEPEND_OBJECT_NAME,
949 CREATED_BY,
950 CREATION_DATE,
951 LAST_UPDATED_BY,
952 LAST_UPDATE_LOGIN,
953 LAST_UPDATE_DATE)
954 values(
955 P_OBJECT_TYPE,
956 P_OBJECT_OWNER,
957 P_OBJECT_NAME,
958 P_ENABLED_FLAG,
959 P_DEPEND_OBJECT_TYPE,
960 P_DEPEND_OBJECT_OWNER,
961 P_DEPEND_OBJECT_NAME,
962 L_CREATED_BY,
963 L_CREATION_DATE,
964 L_LAST_UPDATED_BY,
965 L_LAST_UPDATE_LOGIN,
966 L_LAST_UPDATE_DATE);
967
968 /* create new rows in bis_obj_properties if it is a new object */
969 if (p_from_ui is not null) then
970 -- check parent object
971 open c_in_properties(p_object_name, p_object_type);
972 fetch c_in_properties into l_object_name;
973 if c_in_properties%NOTFOUND then
974 create_properties(
975 P_OBJECT_TYPE => p_object_type,
976 P_OBJECT_NAME => p_object_name,
977 P_OBJECT_OWNER => p_object_owner,
978 P_SNAPSHOT_LOG_SQL => null,
979 P_FAST_REFRESH_FLAG => null,
980 P_DIMENSION_FLAG => null,
981 x_return_status => l_return_status,
982 x_errorcode => l_errorcode,
983 x_msg_count => l_msg_count,
984 x_msg_data => l_msg_data
985 );
986 end if;
987 close c_in_properties;
988
989 --check child object
990 open c_in_properties(p_depend_object_name, p_depend_object_type);
991 fetch c_in_properties into l_object_name;
992 if c_in_properties%NOTFOUND then
993 create_properties(
994 P_OBJECT_TYPE => p_depend_object_type,
995 P_OBJECT_NAME => p_depend_object_name,
996 P_OBJECT_OWNER => p_depend_object_owner,
997 P_SNAPSHOT_LOG_SQL => null,
998 P_FAST_REFRESH_FLAG => null,
999 P_DIMENSION_FLAG => null,
1000 x_return_status => l_return_status,
1001 x_errorcode => l_errorcode,
1002 x_msg_count => l_msg_count,
1003 x_msg_data => l_msg_data
1004 );
1005 end if;
1006 close c_in_properties;
1007 end if;
1008
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010
1011 -----------------------------------
1012 -- Make a standard call to get message count
1013 -- and if count is 1, get message info.
1014 -- The client will directly display the x_msg_data (which is already
1015 -- translated) if the x_msg_count = 1;
1016 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1017 -- Server-side procedure to access the messages, and consolidate them
1018 -- and display them all at once or display one message after another.
1019
1020 FND_MSG_PUB.Count_And_Get
1021 ( p_count => x_msg_count,
1022 p_data => x_msg_data
1023 );
1024 exception
1025 when no_data_found then
1026 return;
1027 when others then
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1029 IF FND_MSG_PUB.Check_Msg_Level
1030 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1031 THEN
1032 FND_MSG_PUB.Add_Exc_Msg
1033 ( G_PKG_NAME,
1034 l_api_name
1035 );
1036 END IF;
1037 FND_MSG_PUB.Count_And_Get
1038 ( p_count => x_msg_count,
1039 p_data => x_msg_data
1040 );
1041 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
1042 raise;
1043 end Create_Dependency_Inner;
1044
1045 procedure Create_Dependency (
1046 P_OBJECT_TYPE in VARCHAR2,
1047 P_OBJECT_OWNER in VARCHAR2,
1048 P_OBJECT_NAME in VARCHAR2,
1049 P_ENABLED_FLAG in VARCHAR2,
1050 P_DEPEND_OBJECT_TYPE in VARCHAR2,
1051 P_DEPEND_OBJECT_OWNER in VARCHAR2,
1052 P_DEPEND_OBJECT_NAME in VARCHAR2,
1053 P_FROM_UI in VARCHAR2,
1054 P_CREATED_BY in NUMBER := null,
1055 P_CREATION_DATE in DATE := null,
1056 P_LAST_UPDATED_BY in NUMBER := null,
1057 P_LAST_UPDATE_LOGIN in NUMBER := null,
1058 P_LAST_UPDATE_DATE in DATE := null,
1059 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
1060 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 x_errorcode OUT NOCOPY NUMBER,
1063 x_msg_count OUT NOCOPY NUMBER,
1064 x_msg_data OUT NOCOPY VARCHAR2
1065 ) IS
1066 begin
1067 Create_Dependency_Inner (
1068 P_OBJECT_TYPE,
1069 P_OBJECT_OWNER,
1070 P_OBJECT_NAME,
1071 P_ENABLED_FLAG,
1072 P_DEPEND_OBJECT_TYPE,
1073 P_DEPEND_OBJECT_OWNER,
1074 P_DEPEND_OBJECT_NAME,
1075 P_FROM_UI,
1076 P_CREATED_BY,
1077 P_CREATION_DATE,
1078 P_LAST_UPDATED_BY,
1079 P_LAST_UPDATE_LOGIN,
1080 P_LAST_UPDATE_DATE,
1081 p_init_msg_list,
1082 p_commit,
1083 x_return_status,
1084 x_errorcode,
1085 x_msg_count,
1086 x_msg_data
1087 );
1088 exception
1089 when others then
1090 null;
1091 end Create_Dependency;
1092
1093
1094 procedure Update_Dependency_Inner (
1095 P_ROWID in ROWID := null,
1096 P_OBJECT_TYPE in VARCHAR2,
1097 P_OBJECT_OWNER in VARCHAR2,
1098 P_OBJECT_NAME in VARCHAR2,
1099 P_ENABLED_FLAG in VARCHAR2,
1100 P_DEPEND_OBJECT_TYPE in VARCHAR2,
1101 P_DEPEND_OBJECT_OWNER in VARCHAR2,
1102 P_DEPEND_OBJECT_NAME in VARCHAR2,
1103 P_FROM_UI in VARCHAR2,
1104 P_LAST_UPDATED_BY in NUMBER := null,
1105 P_LAST_UPDATE_LOGIN in NUMBER := null,
1106 P_LAST_UPDATE_DATE in DATE := null,
1107 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
1108 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
1109 x_return_status OUT NOCOPY VARCHAR2,
1110 x_errorcode OUT NOCOPY NUMBER,
1111 x_msg_count OUT NOCOPY NUMBER,
1112 x_msg_data OUT NOCOPY VARCHAR2
1113 ) is
1114 l_sysdate DATE := sysdate;
1115 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dependency';
1116 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
1117 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
1118 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
1119
1120 cursor c_in_properties(p_object_name varchar2, p_object_type varchar2) is
1121 select object_name
1122 from bis_obj_properties
1123 where object_name = p_object_name
1124 and object_type = p_object_type;
1125
1126 l_object_name bis_obj_properties.object_name%type;
1127 l_return_status varchar2(10);
1128 l_errorcode number;
1129 l_msg_count number;
1130 l_msg_data varchar2(4000);
1131 begin
1132 -- Initialize API message list if necessary.
1133 -- Initialize message list if p_init_msg_list is set to TRUE.
1134 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1135 FND_MSG_PUB.initialize;
1136 END IF;
1137
1138 if (P_ROWID IS NOT NULL) THEN
1139 update bis_obj_dependency
1140 SET object_owner = p_object_owner,
1141 enabled_flag = P_ENABLED_FLAG,
1142 depend_object_type = P_DEPEND_OBJECT_TYPE,
1143 depend_object_owner = P_DEPEND_OBJECT_OWNER,
1144 depend_object_name = P_DEPEND_OBJECT_NAME,
1145 last_updated_by = L_LAST_UPDATED_BY,
1146 last_update_login = L_LAST_UPDATE_LOGIN,
1147 last_update_date = L_LAST_UPDATE_DATE
1148 where rowid = P_ROWID;
1149 else
1150 UPDATE BIS_OBJ_DEPENDENCY
1151 SET object_owner = p_object_owner,
1152 ENABLED_FLAG = P_ENABLED_FLAG,
1153 DEPEND_OBJECT_OWNER = P_DEPEND_OBJECT_OWNER,
1154 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
1155 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
1156 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
1157 WHERE OBJECT_TYPE = P_OBJECT_TYPE
1158 AND OBJECT_NAME = P_OBJECT_NAME
1159 AND DEPEND_OBJECT_NAME = P_DEPEND_OBJECT_NAME
1160 AND DEPEND_OBJECT_TYPE = P_DEPEND_OBJECT_TYPE;
1161 end if;
1162
1163 /* create new rows in bis_obj_properties if it is a new object */
1164 if (p_from_ui is not null) then
1165 --check child object
1166 open c_in_properties(p_depend_object_name, p_depend_object_type);
1167 fetch c_in_properties into l_object_name;
1168 if c_in_properties%NOTFOUND then
1169 create_properties(
1170 P_OBJECT_TYPE => p_depend_object_type,
1171 P_OBJECT_NAME => p_depend_object_name,
1172 P_OBJECT_OWNER => p_depend_object_owner,
1173 P_SNAPSHOT_LOG_SQL => null,
1174 P_FAST_REFRESH_FLAG => null,
1175 P_DIMENSION_FLAG => null,
1176 x_return_status => l_return_status,
1177 x_errorcode => l_errorcode,
1178 x_msg_count => l_msg_count,
1179 x_msg_data => l_msg_data
1180 );
1181 end if;
1182 close c_in_properties;
1183 end if;
1184
1185 /* update the owner of the depend object in property and linkage for bug 3562027 */
1186 execute immediate 'update bis_obj_properties set object_owner = :1
1187 WHERE object_type = :2 AND object_name = :3'
1188 using P_DEPEND_OBJECT_OWNER, P_DEPEND_OBJECT_TYPE, P_DEPEND_OBJECT_NAME;
1189
1190 execute immediate 'update bis_obj_prog_linkages set object_owner = :1
1191 WHERE object_type = :2 AND object_name = :3'
1192 using P_DEPEND_OBJECT_OWNER, P_DEPEND_OBJECT_TYPE, P_DEPEND_OBJECT_NAME;
1193
1194 x_return_status := FND_API.G_RET_STS_SUCCESS;
1195
1196 -----------------------------------
1197 -- Make a standard call to get message count
1198 -- and if count is 1, get message info.
1199 -- The client will directly display the x_msg_data (which is already
1200 -- translated) if the x_msg_count = 1;
1201 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1205 FND_MSG_PUB.Count_And_Get
1202 -- Server-side procedure to access the messages, and consolidate them
1203 -- and display them all at once or display one message after another.
1204
1206 ( p_count => x_msg_count,
1207 p_data => x_msg_data
1208 );
1209
1210 exception
1211 when no_data_found then
1212 return;
1213 when others then
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 IF FND_MSG_PUB.Check_Msg_Level
1216 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1217 THEN
1218 FND_MSG_PUB.Add_Exc_Msg
1219 ( G_PKG_NAME,
1220 l_api_name
1221 );
1222 END IF;
1223 FND_MSG_PUB.Count_And_Get
1224 ( p_count => x_msg_count,
1225 p_data => x_msg_data
1226 );
1227 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
1228 raise;
1229 end Update_Dependency_Inner;
1230
1231 procedure Update_Dependency (
1232 P_ROWID in ROWID := null,
1233 P_OBJECT_TYPE in VARCHAR2,
1234 P_OBJECT_OWNER in VARCHAR2,
1235 P_OBJECT_NAME in VARCHAR2,
1236 P_ENABLED_FLAG in VARCHAR2,
1237 P_DEPEND_OBJECT_TYPE in VARCHAR2,
1238 P_DEPEND_OBJECT_OWNER in VARCHAR2,
1239 P_DEPEND_OBJECT_NAME in VARCHAR2,
1240 P_FROM_UI in VARCHAR2,
1241 P_LAST_UPDATED_BY in NUMBER := null,
1242 P_LAST_UPDATE_LOGIN in NUMBER := null,
1243 P_LAST_UPDATE_DATE in DATE := null,
1244 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
1245 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
1246 x_return_status OUT NOCOPY VARCHAR2,
1247 x_errorcode OUT NOCOPY NUMBER,
1248 x_msg_count OUT NOCOPY NUMBER,
1249 x_msg_data OUT NOCOPY VARCHAR2
1250 ) is
1251 begin
1252 Update_Dependency_Inner (
1253 P_ROWID,
1254 P_OBJECT_TYPE,
1255 P_OBJECT_OWNER,
1256 P_OBJECT_NAME,
1257 P_ENABLED_FLAG,
1258 P_DEPEND_OBJECT_TYPE,
1259 P_DEPEND_OBJECT_OWNER,
1260 P_DEPEND_OBJECT_NAME,
1261 P_FROM_UI,
1262 P_LAST_UPDATED_BY,
1263 P_LAST_UPDATE_LOGIN,
1264 P_LAST_UPDATE_DATE,
1265 p_init_msg_list,
1266 p_commit,
1267 x_return_status,
1268 x_errorcode,
1269 x_msg_count,
1270 x_msg_data
1271 );
1272
1273 exception
1274 when others then
1275 null;
1276 end Update_Dependency;
1277
1278
1279 procedure Delete_Dependency (
1280 P_ROWID in ROWID
1281 ) is
1282 BEGIN
1283
1284 delete from bis_obj_dependency
1285 where rowid = P_ROWID;
1286
1287 If (SQL%NOTFOUND) then
1288 RAISE NO_DATA_FOUND;
1289 End If;
1290
1291 end Delete_Dependency;
1292
1293
1294
1295 procedure Create_Properties_Inner(
1296 P_OBJECT_TYPE in VARCHAR2,
1297 P_OBJECT_NAME in VARCHAR2,
1298 P_OBJECT_OWNER in VARCHAR2,
1299 P_SNAPSHOT_LOG_SQL in VARCHAR2,
1300 P_FAST_REFRESH_FLAG in VARCHAR2,
1301 P_DIMENSION_FLAG in VARCHAR2,
1302 P_CUSTOM_API in VARCHAR2,
1303 P_CREATED_BY in NUMBER,
1304 P_CREATION_DATE in DATE,
1305 P_LAST_UPDATED_BY in NUMBER,
1306 P_LAST_UPDATE_LOGIN in NUMBER,
1307 P_LAST_UPDATE_DATE in DATE,
1308 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
1309 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
1310 x_return_status OUT NOCOPY VARCHAR2,
1311 x_errorcode OUT NOCOPY NUMBER,
1312 x_msg_count OUT NOCOPY NUMBER,
1313 x_msg_data OUT NOCOPY VARCHAR2
1314 ) IS
1315 l_sysdate DATE := sysdate;
1316 l_api_name CONSTANT VARCHAR2(30) := 'Create_Properties';
1317 l_created_by NUMBER := nvl(P_CREATED_BY,g_current_user_id);
1318 l_creation_date DATE := nvl(P_CREATION_DATE, l_Sysdate);
1319 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
1320 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
1321 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
1322
1323 begin
1324 -- Initialize API message list if necessary.
1325 -- Initialize message list if p_init_msg_list is set to TRUE.
1326 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1327 FND_MSG_PUB.initialize;
1328 END IF;
1329
1330 insert into bis_obj_properties (
1331 OBJECT_TYPE,
1332 OBJECT_NAME,
1333 OBJECT_OWNER,
1334 --SNAPSHOT_LOG_SQL,
1335 FAST_REFRESH_FLAG,
1336 DIMENSION_FLAG,
1337 CUSTOM_API,
1338 CREATED_BY,
1339 CREATION_DATE,
1340 LAST_UPDATED_BY,
1341 LAST_UPDATE_LOGIN,
1342 LAST_UPDATE_DATE)
1343 values(
1344 P_OBJECT_TYPE,
1345 P_OBJECT_NAME,
1346 P_OBJECT_OWNER,
1347 --P_SNAPSHOT_LOG_SQL,
1348 P_FAST_REFRESH_FLAG,
1349 P_DIMENSION_FLAG,
1350 P_CUSTOM_API,
1351 L_CREATED_BY,
1352 L_CREATION_DATE,
1353 L_LAST_UPDATED_BY,
1354 L_LAST_UPDATE_LOGIN,
1355 L_LAST_UPDATE_DATE);
1356
1357 x_return_status := FND_API.G_RET_STS_SUCCESS;
1358
1359 -----------------------------------
1360 -- Make a standard call to get message count
1361 -- and if count is 1, get message info.
1362 -- The client will directly display the x_msg_data (which is already
1363 -- translated) if the x_msg_count = 1;
1364 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1365 -- Server-side procedure to access the messages, and consolidate them
1366 -- and display them all at once or display one message after another.
1367
1368 FND_MSG_PUB.Count_And_Get
1369 ( p_count => x_msg_count,
1370 p_data => x_msg_data
1371 );
1372 exception
1373 when no_data_found then
1374 return;
1375 when others then
1376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1377 IF FND_MSG_PUB.Check_Msg_Level
1378 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1379 THEN
1380 FND_MSG_PUB.Add_Exc_Msg
1381 ( G_PKG_NAME,
1382 l_api_name
1383 );
1384 END IF;
1385 FND_MSG_PUB.Count_And_Get
1386 ( p_count => x_msg_count,
1387 p_data => x_msg_data
1388 );
1389 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
1390 raise;
1391 END Create_Properties_Inner;
1392
1393 procedure Create_Properties(
1394 P_OBJECT_TYPE in VARCHAR2,
1395 P_OBJECT_NAME in VARCHAR2,
1396 P_OBJECT_OWNER in VARCHAR2,
1397 P_SNAPSHOT_LOG_SQL in VARCHAR2,
1398 P_FAST_REFRESH_FLAG in VARCHAR2,
1399 P_DIMENSION_FLAG in VARCHAR2,
1400 P_CUSTOM_API in VARCHAR2,
1401 P_CREATED_BY in NUMBER,
1402 P_CREATION_DATE in DATE,
1403 P_LAST_UPDATED_BY in NUMBER,
1404 P_LAST_UPDATE_LOGIN in NUMBER,
1405 P_LAST_UPDATE_DATE in DATE,
1406 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
1407 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
1408 x_return_status OUT NOCOPY VARCHAR2,
1409 x_errorcode OUT NOCOPY NUMBER,
1410 x_msg_count OUT NOCOPY NUMBER,
1411 x_msg_data OUT NOCOPY VARCHAR2
1412 ) IS
1413 begin
1414
1415 Create_Properties_Inner(
1416 P_OBJECT_TYPE,
1417 P_OBJECT_NAME,
1418 P_OBJECT_OWNER,
1419 P_SNAPSHOT_LOG_SQL,
1420 P_FAST_REFRESH_FLAG,
1421 P_DIMENSION_FLAG,
1422 P_CUSTOM_API,
1423 P_CREATED_BY,
1424 P_CREATION_DATE,
1425 P_LAST_UPDATED_BY,
1426 P_LAST_UPDATE_LOGIN,
1427 P_LAST_UPDATE_DATE,
1428 p_init_msg_list,
1429 p_commit,
1430 x_return_status,
1431 x_errorcode,
1432 x_msg_count,
1433 x_msg_data
1434 );
1435 exception
1436 when others then
1437 null;
1438 END;
1439
1440
1441 -- added for bug3040249
1442 procedure Update_Obj_Last_Refresh_Date(
1443 P_OBJECT_TYPE in VARCHAR2,
1444 P_OBJECT_NAME in VARCHAR2,
1445 P_LAST_REFRESH_DATE in DATE
1446 ) IS
1447 Begin
1448 update bis_obj_properties
1449 set
1450 LAST_REFRESH_DATE = P_LAST_REFRESH_DATE
1451 WHERE OBJECT_TYPE = P_OBJECT_TYPE
1452 AND OBJECT_NAME = P_OBJECT_NAME ;
1453 END;
1454
1455 -- new API which consider the CUSTOM_API colomn.
1456 procedure Update_Properties_Inner(
1457 P_OBJECT_TYPE in VARCHAR2,
1458 P_OBJECT_NAME in VARCHAR2,
1459 P_OBJECT_OWNER in VARCHAR2,
1460 P_SNAPSHOT_LOG_SQL in VARCHAR2,
1461 P_FAST_REFRESH_FLAG in VARCHAR2,
1462 P_DIMENSION_FLAG in VARCHAR2,
1463 P_CUSTOM_API in VARCHAR2,
1464 P_LAST_UPDATED_BY in NUMBER,
1465 P_LAST_UPDATE_LOGIN in NUMBER,
1466 P_LAST_UPDATE_DATE in DATE,
1467 p_init_msg_list IN VARCHAR2,
1468 p_commit IN VARCHAR2,
1469 x_return_status OUT NOCOPY VARCHAR2,
1470 x_errorcode OUT NOCOPY NUMBER,
1471 x_msg_count OUT NOCOPY NUMBER,
1472 x_msg_data OUT NOCOPY VARCHAR2
1473 ) IS
1474 l_sysdate DATE := sysdate;
1475 l_api_name CONSTANT VARCHAR2(30) := 'Update_Properties';
1476 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
1477 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
1478 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
1479 begin
1480 -- Initialize API message list if necessary.
1481 -- Initialize message list if p_init_msg_list is set to TRUE.
1482 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1483 FND_MSG_PUB.initialize;
1484 END IF;
1485
1486 update bis_obj_properties
1487 set
1488 OBJECT_OWNER = P_OBJECT_OWNER,
1489 -- SNAPSHOT_LOG_SQL = P_SNAPSHOT_LOG_SQL,
1490 FAST_REFRESH_FLAG = P_FAST_REFRESH_FLAG,
1491 DIMENSION_FLAG = P_DIMENSION_FLAG,
1492 CUSTOM_API = P_CUSTOM_API,
1493 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
1494 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN,
1495 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE
1496 WHERE OBJECT_TYPE = P_OBJECT_TYPE
1497 AND OBJECT_NAME = P_OBJECT_NAME ;
1498
1499 x_return_status := FND_API.G_RET_STS_SUCCESS;
1500
1501 -----------------------------------
1502 -- Make a standard call to get message count
1506 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1503 -- and if count is 1, get message info.
1504 -- The client will directly display the x_msg_data (which is already
1505 -- translated) if the x_msg_count = 1;
1507 -- Server-side procedure to access the messages, and consolidate them
1508 -- and display them all at once or display one message after another.
1509
1510 FND_MSG_PUB.Count_And_Get
1511 ( p_count => x_msg_count,
1512 p_data => x_msg_data
1513 );
1514
1515 exception
1516 when no_data_found then
1517 return;
1518 when others then
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 IF FND_MSG_PUB.Check_Msg_Level
1521 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1522 THEN
1523 FND_MSG_PUB.Add_Exc_Msg
1524 ( G_PKG_NAME,
1525 l_api_name
1526 );
1527 END IF;
1528 FND_MSG_PUB.Count_And_Get
1529 ( p_count => x_msg_count,
1530 p_data => x_msg_data
1531 );
1532 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
1533 raise;
1534 END Update_Properties_Inner;
1535
1536
1537 procedure Update_Properties(
1538 P_OBJECT_TYPE in VARCHAR2,
1539 P_OBJECT_NAME in VARCHAR2,
1540 P_OBJECT_OWNER in VARCHAR2,
1541 P_SNAPSHOT_LOG_SQL in VARCHAR2,
1542 P_FAST_REFRESH_FLAG in VARCHAR2,
1543 P_DIMENSION_FLAG in VARCHAR2,
1544 P_CUSTOM_API in VARCHAR2,
1545 P_LAST_UPDATED_BY in NUMBER,
1546 P_LAST_UPDATE_LOGIN in NUMBER,
1547 P_LAST_UPDATE_DATE in DATE,
1548 p_init_msg_list IN VARCHAR2,
1549 p_commit IN VARCHAR2,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_errorcode OUT NOCOPY NUMBER,
1552 x_msg_count OUT NOCOPY NUMBER,
1553 x_msg_data OUT NOCOPY VARCHAR2
1554 ) IS
1555 begin
1556 Update_Properties_Inner(
1557 P_OBJECT_TYPE,
1558 P_OBJECT_NAME,
1559 P_OBJECT_OWNER,
1560 P_SNAPSHOT_LOG_SQL,
1561 P_FAST_REFRESH_FLAG,
1562 P_DIMENSION_FLAG,
1563 P_CUSTOM_API,
1564 P_LAST_UPDATED_BY,
1565 P_LAST_UPDATE_LOGIN,
1566 P_LAST_UPDATE_DATE,
1567 p_init_msg_list,
1568 p_commit,
1569 x_return_status,
1570 x_errorcode,
1571 x_msg_count,
1572 x_msg_data
1573 );
1574 exception
1575 when others then
1576 null;
1577 END;
1578
1579
1580 /*
1581 -- this is for backward compitability
1582 procedure Update_Properties(
1583 P_OBJECT_TYPE in VARCHAR2,
1584 P_OBJECT_NAME in VARCHAR2,
1585 P_OBJECT_OWNER in VARCHAR2,
1586 P_SNAPSHOT_LOG_SQL in VARCHAR2,
1587 P_FAST_REFRESH_FLAG in VARCHAR2,
1588 P_DIMENSION_FLAG in VARCHAR2,
1589 P_LAST_UPDATED_BY in NUMBER,
1590 P_LAST_UPDATE_LOGIN in NUMBER,
1591 P_LAST_UPDATE_DATE in DATE,
1592 p_init_msg_list IN VARCHAR2,
1593 p_commit IN VARCHAR2,
1594 x_return_status OUT NOCOPY VARCHAR2,
1595 x_errorcode OUT NOCOPY NUMBER,
1596 x_msg_count OUT NOCOPY NUMBER,
1597 x_msg_data OUT NOCOPY VARCHAR2
1598 ) IS
1599 l_sysdate DATE := sysdate;
1600 l_api_name CONSTANT VARCHAR2(30) := 'Update_Properties';
1601 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
1602 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
1603 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
1604 begin
1605 -- Initialize API message list if necessary.
1606 -- Initialize message list if p_init_msg_list is set to TRUE.
1607 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1608 FND_MSG_PUB.initialize;
1609 END IF;
1610
1611 update bis_obj_properties
1612 set
1613 OBJECT_OWNER = P_OBJECT_OWNER,
1614 SNAPSHOT_LOG_SQL = P_SNAPSHOT_LOG_SQL,
1615 FAST_REFRESH_FLAG = P_FAST_REFRESH_FLAG,
1616 DIMENSION_FLAG = P_DIMENSION_FLAG,
1617 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
1618 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN,
1619 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE
1620 WHERE OBJECT_TYPE = P_OBJECT_TYPE
1621 AND OBJECT_NAME = P_OBJECT_NAME ;
1622
1623 x_return_status := FND_API.G_RET_STS_SUCCESS;
1624
1625 -----------------------------------
1626 -- Make a standard call to get message count
1627 -- and if count is 1, get message info.
1628 -- The client will directly display the x_msg_data (which is already
1629 -- translated) if the x_msg_count = 1;
1630 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1631 -- Server-side procedure to access the messages, and consolidate them
1632 -- and display them all at once or display one message after another.
1633
1634 FND_MSG_PUB.Count_And_Get
1635 ( p_count => x_msg_count,
1636 p_data => x_msg_data
1637 );
1638
1639 exception
1640 when no_data_found then
1641 return;
1642 when others then
1643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1644 IF FND_MSG_PUB.Check_Msg_Level
1645 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1646 THEN
1647 FND_MSG_PUB.Add_Exc_Msg
1648 ( G_PKG_NAME,
1649 l_api_name
1650 );
1651 END IF;
1652 FND_MSG_PUB.Count_And_Get
1653 ( p_count => x_msg_count,
1654 p_data => x_msg_data
1655 );
1656 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
1657
1658 END;
1659 */
1660
1661 procedure Delete_Properties(
1662 P_OBJECT_TYPE in VARCHAR2,
1663 P_OBJECT_NAME in VARCHAR2
1664 ) IS
1665 BEGIN
1666 delete bis_obj_properties
1667 where object_type = p_object_type
1668 and object_name = p_object_name;
1669
1670 If (SQL%NOTFOUND) then
1671 RAISE NO_DATA_FOUND;
1672 End If;
1673 end Delete_Properties;
1674
1675
1676 -- new implementation for removal of _OA
1677 function Get_User_Object_Name (
1678 P_OBJECT_TYPE IN VARCHAR2,
1679 P_OBJECT_NAME IN VARCHAR2
1680 ) RETURN varchar2 IS
1681 x_user_object_name varchar2(480); --Enhancement 4106617
1682 i number;
1683
1684 cursor c_portlet_report(p_object_name varchar2) is
1685 select user_function_name
1686 from fnd_form_functions_vl
1687 where function_name = p_object_name;
1688
1689 -- new implementation to uptake get_function_by_page
1690 cursor c_oa_page(p_object_name varchar2) is
1691 select user_function_name
1692 from fnd_form_functions_vl
1693 where UPPER(web_html_call) like '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
1694 and get_function_by_page(p_object_name) = function_name;
1695
1696 begin
1697 if (P_OBJECT_TYPE = 'TABLE' OR P_OBJECT_TYPE = 'VIEW' OR P_OBJECT_TYPE = 'MV' or P_OBJECT_TYPE='AWCUBE') then
1698 X_USER_OBJECT_NAME := P_OBJECT_NAME;
1699 elsif (P_OBJECT_TYPE = 'PORTLET' OR P_OBJECT_TYPE = 'REPORT') then
1700 open c_portlet_report(p_object_name);
1701 fetch c_portlet_report into x_user_object_name;
1702 i := c_portlet_report%ROWCOUNT;
1703 close c_portlet_report;
1704 if (i <= 0) then -- nothing matches
1705 X_USER_OBJECT_NAME := P_OBJECT_NAME;
1706 end if;
1707 elsif (P_OBJECT_TYPE = 'PAGE') THEN
1708 open c_oa_page(p_object_name);
1709 fetch c_oa_page into x_user_object_name;
1710 i := c_oa_page%ROWCOUNT;
1711 close c_oa_page;
1712 IF (i > 0) THEN
1713 RETURN x_user_object_name;
1714 END IF;
1715
1716 -- bug 3975359: if the given page is a portal page return its object name as user object name
1717 X_USER_OBJECT_NAME := P_OBJECT_NAME;
1718 end if;
1719
1720 return X_USER_OBJECT_NAME;
1721 EXCEPTION
1722 when no_data_found then
1723 return P_OBJECT_NAME;
1724 when others then
1725 return P_OBJECT_NAME;
1726
1727 end Get_User_Object_Name;
1728
1729 function is_page_migrated (
1730 P_PAGE_NAME in VARCHAR2
1731 ) RETURN boolean
1732 IS
1733 cursor c_page_migrated(P_PAGE_NAME varchar2) is
1734 SELECT /*+ FIRST_ROWS*/ 'Y'
1735 FROM BIS_OBJ_DEPENDENCY
1736 WHERE OBJECT_TYPE = 'PAGE'
1737 AND P_PAGE_NAME like '%_OA'
1738 AND OBJECT_NAME = SUBSTR(P_PAGE_NAME, 1, LENGTH(P_PAGE_NAME) - 3)
1739 AND ROWNUM = 1;
1740 L_MIGRATED VARCHAR2(10) := NULL;
1741
1742 BEGIN
1743 open c_page_migrated(P_PAGE_NAME);
1744 fetch c_page_migrated into L_MIGRATED;
1745 close c_page_migrated;
1746 IF (L_MIGRATED = 'Y') THEN
1747 return true;
1748 else
1749 return false;
1750 end if;
1751 END is_page_migrated;
1752
1753 function get_function_by_page (
1754 P_PAGE_NAME in VARCHAR2
1755 ) RETURN varchar2
1756 IS
1757 cursor c_page1(P_PAGE_NAME varchar2) is
1758 select f.function_name
1759 from
1760 fnd_form_functions f
1761 where
1762 UPPER(f.web_html_call) like '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
1763 and function_name = P_PAGE_NAME;
1764
1765 cursor c_page2(P_PAGE_NAME varchar2) is
1766 select f.function_name
1767 from
1768 fnd_form_functions f
1769 where
1770 UPPER(f.web_html_call) like '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
1771 and P_PAGE_NAME like '%_OA'
1772 and function_name = SUBSTR(P_PAGE_NAME, 1, LENGTH(P_PAGE_NAME) - 3);
1773
1774 L_FUNCTION_NAME fnd_form_functions.function_name%TYPE := NULL;
1775 BEGIN
1776 open c_page1(P_PAGE_NAME);
1777 fetch c_page1 into L_FUNCTION_NAME;
1778 close c_page1;
1779 if (L_FUNCTION_NAME is not null) then
1780 RETURN L_FUNCTION_NAME;
1781 end if;
1782
1783 open c_page2(P_PAGE_NAME);
1784 fetch c_page2 into L_FUNCTION_NAME;
1785 close c_page2;
1786 RETURN L_FUNCTION_NAME;
1787
1788 END;
1789
1790
1791 procedure migrate_page(
1792 P_PAGE_NAME in VARCHAR2,
1793 P_NEW_PAGE_NAME in VARCHAR2
1794 ) IS
1795
1796 cursor skip_migrate(P_PAGE_NAME varchar2) is
1797 select /*+ FIRST_ROWS*/ 'Y'
1798 from
1799 fnd_form_functions f,
1800 bis_obj_dependency d
1801 where
1802 d.object_name = f.function_name
1803 and d.object_type = 'PAGE'
1804 and UPPER(f.web_html_call) like '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
1805 and function_name = P_PAGE_NAME;
1806 l_skip_migrate VARCHAR2(10);
1807
1808 BEGIN
1809
1810 open skip_migrate(P_PAGE_NAME);
1811 fetch skip_migrate into l_skip_migrate;
1812 close skip_migrate;
1813
1814 IF(l_skip_migrate = 'Y') THEN
1815 --FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'bis.maigrate_page', 'no nned to migrate');
1816 RETURN;
1817 END IF;
1818
1819 update bis_obj_properties
1820 set object_name = P_NEW_PAGE_NAME
1821 where
1822 object_type = 'PAGE'
1823 and object_name = P_PAGE_NAME;
1824 --FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'bis.maigrate_page', SQL%rowcount || ' rows updated :' || P_PAGE_NAME || '->' || P_NEW_PAGE_NAME || ' in bis_obj_properties.object_name');
1825
1826 update bis_obj_dependency
1827 set object_name = P_NEW_PAGE_NAME
1828 where
1829 object_type = 'PAGE'
1830 and object_name = P_PAGE_NAME;
1831 --FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'bis.maigrate_page', SQL%rowcount || ' rows updated :' || P_PAGE_NAME || '->' || P_NEW_PAGE_NAME || ' in bis_obj_dependency.object_name');
1832
1833 END migrate_page;
1834 /* starts: bug 3562027 -- change owner for parent object */
1835 /*Bug 4560963 : From now, we will get object owner from bis_obj_properties
1836 unlike the previous approach of getting it from bis_obj_dependency */
1837 FUNCTION is_owner_changed (
1838 p_obj_name IN VARCHAR2,
1839 p_obj_type IN VARCHAR2,
1840 p_new_obj_owner IN VARCHAR2,
1841 p_actual_owner OUT NOCOPY VARCHAR2
1842 ) RETURN VARCHAR2 IS
1843
1844 CURSOR c_owner(p_obj_name VARCHAR2, p_obj_type VARCHAR2) IS
1845 SELECT object_owner
1846 FROM bis_obj_properties
1847 WHERE object_type = p_obj_type
1848 AND object_name = p_obj_name;
1849
1850 v_obj_owner VARCHAR2(50);
1851 v_changed VARCHAR2(5);
1852 BEGIN
1853 OPEN c_owner(p_obj_name, p_obj_type);
1854 FETCH c_owner INTO v_obj_owner;
1855 IF (c_owner%notfound) THEN
1856 v_changed := 'N';
1857 CLOSE c_owner;
1858 RETURN v_changed;
1859 END IF;
1860 CLOSE c_owner;
1861
1862 IF (p_new_obj_owner = v_obj_owner) THEN
1863 v_changed := 'N';
1864 ELSE
1865 v_changed := 'Y';
1866 p_actual_owner := v_obj_owner;
1867 END IF;
1868 RETURN v_changed;
1869 EXCEPTION
1870 WHEN OTHERS THEN
1871 RETURN 'Y';
1872 END is_owner_changed;
1873
1874 PROCEDURE change_prop_linkage_owner (
1875 p_obj_name IN VARCHAR2,
1876 p_obj_type IN VARCHAR2,
1877 p_obj_owner IN VARCHAR2
1878 ) IS
1879
1880 BEGIN
1881 IF (p_obj_name IS NULL OR p_obj_type IS NULL OR p_obj_owner IS NULL) THEN
1882 RETURN;
1883 END IF;
1884
1885 execute immediate 'update bis_obj_properties set object_owner = :1
1886 WHERE object_type = :2 AND object_name = :3'
1887 using p_obj_owner, p_obj_type, p_obj_name;
1888
1889 execute immediate 'update bis_obj_prog_linkages set object_owner = :1
1890 WHERE object_type = :2 AND object_name = :3'
1891 using p_obj_owner, p_obj_type, p_obj_name;
1892 EXCEPTION
1893 WHEN OTHERS THEN
1894 RETURN;
1895 END change_prop_linkage_owner;
1896 /* sends: bug 3562027 -- change owner for parent object */
1897
1898 /*This procedure has been added for enhcnement 4391651. Given object name and type.
1899 Find out if the object is seeded or not */
1900
1901 FUNCTION is_object_seeded( p_obj_name IN VARCHAR2, p_obj_type IN VARCHAR2) RETURN VARCHAR2 IS
1902
1903 cursor c_obj_depend(l_obj_name VARCHAR2, l_obj_type VARCHAR2) is
1904 select CREATED_BY from bis_obj_dependency
1905 where OBJECT_NAME = l_obj_name and OBJECT_TYPE = l_obj_type;
1906
1907 cursor c_obj_depend_depend(l_obj_name VARCHAR2, l_obj_type VARCHAR2) is
1908 select CREATED_BY from bis_obj_dependency
1909 where DEPEND_OBJECT_NAME = l_obj_name and depend_OBJECT_TYPE = l_obj_type;
1910
1911
1912 cursor c_obj_prog(l_obj_name VARCHAR2, l_obj_type VARCHAR2) is
1913 select CREATED_BY from bis_obj_prog_linkages
1914 where OBJECT_NAME = l_obj_name and OBJECT_TYPE = l_obj_type;
1915
1916 cursor c_obj_prop(l_obj_name VARCHAR2, l_obj_type VARCHAR2) is
1917 select CREATED_BY from bis_obj_properties
1918 where OBJECT_NAME = l_obj_name and OBJECT_TYPE = l_obj_type;
1919
1920 created_user number;
1921
1922 BEGIN
1923
1924 for c_obj_depend_rec in c_obj_depend(p_obj_name,p_obj_type) loop
1925 created_user := c_obj_depend_rec.CREATED_BY;
1926 --Followed the logic as used in AFLDUTLB.pls for seeded user having 120 to 129 user_id range
1927 if (created_user =1 or created_user =2 or (created_user >=120 and created_user <=129)) then
1928 exit;
1929 end if;
1930 end loop;
1931 if (created_user is null) then
1932 --check if the object is dependent object and seeded object
1933 for c_obj_depend_depend_rec in c_obj_depend_depend(p_obj_name,p_obj_type) loop
1934 created_user := c_obj_depend_depend_rec.CREATED_BY;
1935 --Followed the logic as used in AFLDUTLB.pls for seeded user having 120 to 129 user_id range
1936 if (created_user =1 or created_user =2 or (created_user >=120 and created_user <=129)) then
1937 exit;
1938 end if;
1939 end loop;
1940 if (created_user is null) then
1941 for c_obj_prog_rec in c_obj_prog(p_obj_name,p_obj_type) loop
1942 created_user := c_obj_prog_rec.CREATED_BY;
1943 end loop;
1944 if(created_user is null) then
1945 for c_obj_prop_rec in c_obj_prop(p_obj_name,p_obj_type) loop
1946 created_user := c_obj_prop_rec.CREATED_BY;
1947 end loop;
1948 if (created_user is null) then
1949 return 'FALSE'; --new object
1950 else
1951 if(created_user <>1 and created_user <>2 and (created_user < 120 or created_user > 129))then
1952 return 'FALSE';
1953 else
1954 return 'TRUE';
1955 end if;
1956 end if;
1957 else
1958 if(created_user <>1 and created_user <>2 and (created_user < 120 or created_user > 129))then
1959 return 'FALSE';
1960 else
1961 return 'TRUE';
1962 end if;
1963 end if;
1964 else
1965 if(created_user <>1 and created_user <>2 and (created_user < 120 or created_user > 129))then
1966 return 'FALSE';
1967 else
1968 return 'TRUE';
1969 end if;
1970 end if;
1971 else
1972 if(created_user <>1 and created_user <>2 and (created_user < 120 or created_user > 129))then
1973 return 'FALSE';
1974 else
1975 return 'TRUE';
1976 end if;
1977 end if;
1978
1979 END is_object_seeded;
1980
1981 end BIS_IMPL_DEV_PKG;
[Home] [Help]