[Home] [Help]
PACKAGE BODY: APPS.FEM_OBJECT_CATALOG_UTIL_PKG
Source
1 PACKAGE BODY FEM_OBJECT_CATALOG_UTIL_PKG AS
2 /* $Header: fem_objcat_utl.plb 120.8 2006/07/28 19:41:29 dyung ship $ */
3
4 /* ***********************
5 ** Package constants
6 ** ***********************/
7 pc_pkg_name CONSTANT VARCHAR2(30) := 'fem_object_catalog_util_pkg';
8
9 pc_ret_sts_success CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_success;
10 pc_ret_sts_error CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_error;
11 pc_ret_sts_unexp_error CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_unexp_error;
12
13 pc_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
14 pc_last_update_login CONSTANT NUMBER := FND_GLOBAL.Login_Id;
15 pc_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
16
17 pc_object_version_number CONSTANT NUMBER := 1;
18
19 pc_log_level_statement CONSTANT NUMBER := fnd_log.level_statement;
20 pc_log_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
21 pc_log_level_event CONSTANT NUMBER := fnd_log.level_event;
22 pc_log_level_exception CONSTANT NUMBER := fnd_log.level_exception;
23 pc_log_level_error CONSTANT NUMBER := fnd_log.level_error;
24 pc_log_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
25
26
27 /* ***********************
28 ** Package variables
29 ** ***********************/
30 --dbms_utility.format_call_stack VARCHAR2(2000);
31
32 /* ***********************
33 ** Package exceptions
34 ** ***********************/
35 e_cannot_create_definition EXCEPTION;
36 e_invalid_object_type EXCEPTION;
37 e_invalid_object_origin EXCEPTION;
38 e_invalid_object_access_code EXCEPTION;
39 e_invalid_folder EXCEPTION;
40 e_invalid_effective_date_range EXCEPTION;
41 e_cannot_delete_object EXCEPTION;
42 e_invalid_local_vs_combo_id EXCEPTION;
43 e_cannot_write_to_object EXCEPTION;
44 e_duplicate_obj_name EXCEPTION;
45
46 gv_prg_msg VARCHAR2(2000);
47 gv_callstack VARCHAR2(2000);
48
49
50 /* ******************************************************************************/
51 PROCEDURE create_object (x_object_id OUT NOCOPY NUMBER,
52 x_object_definition_id OUT NOCOPY NUMBER,
53 x_msg_count OUT NOCOPY NUMBER,
54 x_msg_data OUT NOCOPY VARCHAR2,
55 x_return_status OUT NOCOPY VARCHAR2,
56 p_api_version IN NUMBER,
57 p_commit IN VARCHAR2,
58 p_object_type_code IN VARCHAR2,
59 p_folder_id IN NUMBER,
60 p_local_vs_combo_id IN NUMBER,
61 p_object_access_code IN VARCHAR2,
62 p_object_origin_code IN VARCHAR2,
63 p_object_name IN VARCHAR2,
64 p_description IN VARCHAR2,
65 p_effective_start_date IN DATE DEFAULT sysdate,
66 p_effective_end_date IN DATE DEFAULT to_date('9999/01/01','YYYY/MM/DD'),
67 p_obj_def_name IN VARCHAR2)
68 IS
69
70 /* ==========================================================================
71 ** This procedure creates a new Object in the FEM Object Catalog.
72 ** It also creates a new Object Definition for the new Object
73 ** ==========================================================================
74 ** ==========================================================================*/
75 c_api_name CONSTANT VARCHAR2(30) := 'create_object';
76 c_api_version CONSTANT NUMBER := 1.0;
77 v_rowid VARCHAR2(100);
78 v_count NUMBER;
79 v_folder_name varchar2(150);
80
81
82 BEGIN
83
84 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
85 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
86 p_msg_text => 'Begin. P_OBJECT_TYPE_CODE: '||p_object_type_code||
87 ' P_FOLDER_ID:'||p_folder_id||
88 ' P_LOCAL_VS_COMBO_ID:'||p_local_vs_combo_id||
89 ' P_OBJECT_ACCESS_CODE:'||p_object_access_code||
90 ' P_OBJECT_ORIGIN_CODE:'||p_object_origin_code||
91 ' P_OBJECT_NAME:'||p_object_name||
92 ' P_DESCRIPTION:'||p_description||
93 ' P_EFFECTIVE_START_DATE:'||p_effective_start_date||
94 ' P_EFFECTIVE_END_DATE:'||p_effective_end_date||
95 ' P_OBJ_DEF_NAME:'||p_obj_def_name||
96 ' P_COMMIT: '||p_commit);
97
98 /* Standard Start of API savepoint */
99 SAVEPOINT create_object_pub;
100
101 /* Standard call to check for call compatibility. */
102 IF NOT FND_API.Compatible_API_Call (c_api_version,
103 p_api_version,
104 c_api_name,
105 pc_pkg_name)
106 THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 /* Initialize API return status to success */
111 x_return_status := pc_ret_sts_success;
112
113 /* Validate that the Object Type exists */
114 SELECT count(*)
115 INTO v_count
116 FROM fem_object_types_b
117 WHERE object_type_code = p_object_type_code;
118
119 IF v_count = 0 THEN
120 RAISE e_invalid_object_type;
121 END IF; /* object_type_code validation */
122
123 /* Validate that the Object Origin exists in FND_LOOKUP_VALUES */
124 SELECT count(*)
125 INTO v_count
126 FROM fnd_lookup_values
127 WHERE lookup_type = 'FEM_OBJECT_ORIGIN_DSC'
128 AND lookup_code = p_object_origin_code;
129
130 IF v_count = 0 THEN
131 RAISE e_invalid_object_origin;
132 END IF; /* object_origin_code validation */
133
134 /* Validate that the Object Access Code exists in FND_LOOKUP_VALUES */
135 SELECT count(*)
136 INTO v_count
137 FROM fnd_lookup_values
138 WHERE lookup_type = 'FEM_OBJECT_ACCESS_DSC'
139 AND lookup_code = p_object_access_code;
140
141 IF v_count = 0 THEN
142 RAISE e_invalid_object_access_code;
143 END IF; /* object_access_code validation */
144
145 -- validate that the folder exists and get the name if it does
146 BEGIN
147 SELECT folder_name
148 INTO v_folder_name
149 FROM fem_folders_vl
150 WHERE folder_id = p_folder_id;
151
152 EXCEPTION
153 WHEN no_data_found THEN raise e_invalid_folder;
154
155 END;
156
157 -- Bug 4309949: ignore folder security for Undo objects
158 IF p_object_type_code <> 'UNDO' THEN
159 -- validate that the Folder ID exists and that
160 -- the user can write to the folder
161 SELECT count(*)
162 INTO v_count
163 FROM fem_user_folders
164 WHERE user_id = pc_user_id
165 AND folder_id = p_folder_id
166 AND write_flag = 'Y';
167
168 IF v_count = 0 THEN
169 RAISE e_invalid_folder;
170 END IF; /* folder_id validation*/
171 END IF;
172
173 /* validate that the start date and end date are consistent */
174 IF p_effective_start_date >= p_effective_end_date THEN
175 RAISE e_invalid_effective_date_range;
176 END IF; /* effective date validation */
177
178 /* Validate local_vs_combo_id */
179 IF p_local_vs_combo_id IS NOT NULL THEN
180 SELECT count(*)
181 INTO v_count
182 FROM fem_global_vs_combos_vl
183 WHERE global_vs_combo_id = p_local_vs_combo_id;
184
185 IF v_count = 0 THEN
186 RAISE e_invalid_local_vs_combo_id;
187 END IF; /* local_vs_combo_id validation */
188 END IF;
189
190 SELECT fem_object_id_seq.nextval
191 INTO x_object_id
192 FROM dual;
193
194 BEGIN
195 FEM_OBJECT_CATALOG_PKG.INSERT_ROW (
196 X_ROWID => v_rowid,
197 X_OBJECT_ID => x_object_id,
198 X_OBJECT_TYPE_CODE => p_object_type_code,
199 X_FOLDER_ID => p_folder_id,
200 X_LOCAL_VS_COMBO_ID => p_local_vs_combo_id,
201 X_OBJECT_ACCESS_CODE => p_object_access_code,
202 X_OBJECT_ORIGIN_CODE => p_object_origin_code,
203 X_OBJECT_VERSION_NUMBER => pc_object_version_number,
204 X_OBJECT_NAME => p_object_name,
205 X_DESCRIPTION => p_description,
206 X_CREATION_DATE => sysdate,
207 X_CREATED_BY => pc_user_id,
208 X_LAST_UPDATE_DATE => sysdate,
209 X_LAST_UPDATED_BY => pc_user_id,
210 X_LAST_UPDATE_LOGIN => pc_last_update_login);
211 EXCEPTION
212 WHEN dup_val_on_index THEN raise e_duplicate_obj_name;
213 END;
214
215 create_object_definition (p_api_version => 1.0,
216 p_commit => FND_API.G_FALSE,
217 p_object_id => x_object_id,
218 p_effective_start_date => p_effective_start_date,
219 p_effective_end_date => p_effective_end_date,
220 p_obj_def_name => p_obj_def_name,
221 p_object_origin_code => p_object_origin_code,
222 x_object_definition_id => x_object_definition_id,
223 x_msg_count => x_msg_count,
224 x_msg_data => x_msg_data,
225 x_return_status => x_return_status);
226
227
228 IF x_return_status <> pc_ret_sts_success THEN
229 RAISE e_cannot_create_definition;
230 END IF;
231
232 IF FND_API.To_Boolean( p_commit ) THEN
233 COMMIT WORK;
234 END IF;
235
236 fem_engines_pkg.put_message(p_app_name =>'FEM',
237 p_msg_name =>'FEM_CREATED_OBJ_TXT',
238 p_token1 => 'OBJECT_NAME',
239 p_value1 => p_object_name,
240 p_trans1 => 'N');
241
242 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
243 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
244 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
245
246 FND_MSG_PUB.Count_And_Get
247 (p_count => x_msg_count,
248 p_data => x_msg_data);
249
250 EXCEPTION
251 WHEN e_duplicate_obj_name THEN
252 ROLLBACK TO create_object_pub;
253 x_return_status := pc_ret_sts_error;
254
255 fem_engines_pkg.put_message(p_app_name =>'FEM'
256 ,p_msg_name =>'FEM_BR_OBJ_NAME_ERR'
257 ,p_token1 => 'OBJECT_TYPE_MEANING'
258 ,p_value1 => p_object_type_code
259 ,p_token2 => 'FOLDER_NAME'
260 ,p_value2 => v_folder_name
261 ,p_token3 => 'OBJECT_NAME'
262 ,p_value3 => p_object_name);
263
264 FND_MSG_PUB.Count_And_Get
265 (p_count => x_msg_count,
266 p_data => x_msg_data);
267
268
269
270 WHEN e_invalid_local_vs_combo_id THEN
271 ROLLBACK TO create_object_pub;
272 x_return_status := pc_ret_sts_error;
273 fem_engines_pkg.put_message(p_app_name =>'FEM'
274 ,p_msg_name =>'FEM_INVALID_LOCAL_VS_COMBO_ID'
275 ,p_token1 => 'LOCAL_VS_COMBO_ID'
276 ,p_value1 => p_local_vs_combo_id);
277
278 FND_MSG_PUB.Count_And_Get
279 (p_count => x_msg_count,
280 p_data => x_msg_data);
281
282 WHEN e_invalid_object_type THEN
283 ROLLBACK TO create_object_pub;
284 x_return_status := pc_ret_sts_error;
285 fem_engines_pkg.put_message(p_app_name =>'FEM'
286 ,p_msg_name =>'FEM_INVALID_OBJECT_TYPE'
287 ,p_token1 => 'OBJTYPE'
288 ,p_value1 => p_object_type_code);
289
290 FND_MSG_PUB.Count_And_Get
291 (p_count => x_msg_count,
292 p_data => x_msg_data);
293
294 WHEN e_invalid_object_origin THEN
295 ROLLBACK TO create_object_pub;
296 x_return_status := pc_ret_sts_error;
297 fem_engines_pkg.put_message(p_app_name =>'FEM'
298 ,p_msg_name =>'FEM_INVALID_OBJECT_ORIGIN'
299 ,p_token1 => 'OBJORIG'
300 ,p_value1 => p_object_origin_code);
301
302 FND_MSG_PUB.Count_And_Get
303 (p_count => x_msg_count,
304 p_data => x_msg_data);
305
306
307 WHEN e_invalid_object_access_code THEN
308 ROLLBACK TO create_object_pub;
309 x_return_status := pc_ret_sts_error;
310 fem_engines_pkg.put_message(p_app_name =>'FEM'
311 ,p_msg_name =>'FEM_INVALID_OBJ_ACCESS_CODE'
312 ,p_token1 => 'OBJACC'
313 ,p_value1 => p_object_access_code);
314
315 FND_MSG_PUB.Count_And_Get
316 (p_count => x_msg_count,
317 p_data => x_msg_data);
318
319 WHEN e_invalid_folder THEN
320 ROLLBACK TO create_object_pub;
321 x_return_status := pc_ret_sts_error;
322 fem_engines_pkg.put_message(p_app_name =>'FEM',
323 p_msg_name =>'FEM_IMPEXP_INVALID_FOLDER_ERR',
324 p_token1 => 'FOLDER',
325 p_value1 => v_folder_name);
326
327 FND_MSG_PUB.Count_And_Get
328 (p_count => x_msg_count,
329 p_data => x_msg_data);
330
331 WHEN e_invalid_effective_date_range THEN
332 ROLLBACK TO create_object_pub;
333 x_return_status := pc_ret_sts_error;
334 fem_engines_pkg.put_message(p_app_name =>'FEM',
335 p_msg_name =>'FEM_BR_END_LT_START_DATE_ERR',
336 p_token1 => 'END_DATE',
337 p_value1 => fnd_date.date_to_displaydate(p_effective_end_date),
338 p_trans1 => 'N',
339 p_token2 => 'START_DATE',
340 p_value2 => fnd_date.date_to_displaydate(p_effective_start_date),
341 p_trans2 => 'N');
342
343 FND_MSG_PUB.Count_And_Get
344 (p_count => x_msg_count,
345 p_data => x_msg_data);
346
347 WHEN e_cannot_create_definition THEN
348 ROLLBACK TO create_object_pub;
349 x_return_status := pc_ret_sts_error;
350 x_object_id := NULL;
351 fem_engines_pkg.put_message(p_app_name =>'FEM'
352 ,p_msg_name =>'FEM_CANNOT_CREATE_DEF_ERR'
353 ,p_token1 => 'DEFNAME'
354 ,p_value1 => p_obj_def_name);
355
356 FND_MSG_PUB.Count_And_Get
357 (p_count => x_msg_count,
358 p_data => x_msg_data);
359
360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
361 ROLLBACK TO create_object_pub;
362 x_return_status := pc_ret_sts_unexp_error;
363
364 fem_engines_pkg.tech_message(p_severity => pc_log_level_unexpected,
365 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
366 p_msg_name => 'FEM_BAD_P_API_VER_ERR'
367 ,p_token1 => 'VALUE'
368 ,p_value1 => p_api_version
369 ,p_trans1 => 'N');
370
371 FND_MSG_PUB.Count_And_Get
372 (p_count => x_msg_count,
373 p_data => x_msg_data);
374
375 WHEN OTHERS THEN
376 /* Unexpected exceptions */
377 x_return_status := pc_ret_sts_unexp_error;
378 gv_prg_msg := SQLERRM;
379 gv_callstack := dbms_utility.format_call_stack;
380
381 /* Log the call stack and the Oracle error message to
382 ** FND_LOG with the "unexpected exception" severity level. */
383
384 FEM_ENGINES_PKG.Tech_Message
385 (p_severity => pc_log_level_unexpected,
386 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
387 p_msg_text => gv_prg_msg);
388
389 FEM_ENGINES_PKG.Tech_Message
390 (p_severity => pc_log_level_unexpected,
391 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
392 p_msg_text => gv_callstack);
393
394 /* Log the Oracle error message to the stack. */
395 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
396 p_msg_name => 'FEM_UNEXPECTED_ERROR',
397 P_TOKEN1 => 'ERR_MSG',
398 P_VALUE1 => gv_prg_msg);
399 ROLLBACK TO create_object_pub;
400
401 FND_MSG_PUB.Count_And_Get
402 (p_count => x_msg_count,
403 p_data => x_msg_data);
404
405 END create_object;
406 /* ******************************************************************************/
407 PROCEDURE create_object_definition (x_object_definition_id OUT NOCOPY NUMBER,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2,
410 x_return_status OUT NOCOPY VARCHAR2,
411 p_api_version IN NUMBER,
412 p_commit IN VARCHAR2,
413 p_object_id IN NUMBER,
414 p_effective_start_date IN DATE,
415 p_effective_end_date IN DATE,
416 p_obj_def_name IN VARCHAR2,
417 p_object_origin_code IN VARCHAR2)
418 IS
419
420 c_api_name CONSTANT VARCHAR2(30) := 'create_object_definition';
421 c_api_version CONSTANT NUMBER := 1.0;
422 v_date_range_is_valid VARCHAR2(1);
423 v_rowid VARCHAR2(100);
424 v_approval_status_code VARCHAR2(30);
425 v_count NUMBER;
426 v_object_type_code FEM_OBJECT_TYPES.object_type_code%TYPE;
427
428 /* ==========================================================================
429 ** This procedure creates a new Object Definition for the specified Object ID.
430 ** It calls validate_obj_def_effdate to verify that the Start Date and
431 ** End Date parameters for the new Object Definition do not conflict with
432 ** existing Object Definition of the specified Object ID.
433 ** This procedure will only create the definition if the user can write to
434 ** the object.
435 ** ==========================================================================*/
436 BEGIN
437
438 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
439 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
440 p_msg_text => 'Begin. P_OBJECT_ID: '||p_object_id||
441 ' P_DESCRIPTION:'||p_obj_def_name||
442 ' P_EFFECTIVE_START_DATE:'||p_effective_start_date||
443 ' P_EFFECTIVE_END_DATE:'||p_effective_end_date||
444 ' P_OBJ_DEF_NAME:'||p_obj_def_name||
445 ' P_COMMIT: '||p_commit);
446
447 /* Standard Start of API savepoint */
448 SAVEPOINT create_object_definition_pub;
449
450 /* Standard call to check for call compatibility. */
451 IF NOT FND_API.Compatible_API_Call (c_api_version,
452 p_api_version,
453 c_api_name,
454 pc_pkg_name)
455 THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458
459 -- Initialize API return status to success
460 x_return_status := pc_ret_sts_success;
461
462 -- Validate that the Object Origin exists in FND_LOOKUP_VALUES
463 SELECT count(*)
464 INTO v_count
465 FROM fnd_lookup_values
466 WHERE lookup_type = 'FEM_OBJECT_ORIGIN_DSC'
467 AND lookup_code = p_object_origin_code;
468
469 IF v_count = 0 THEN
470 RAISE e_invalid_object_origin;
471 END IF; /* object_origin_code validation */
472
473 -- Bug 4309949: ignore folder security for Undo objects
474 SELECT object_type_code
475 INTO v_object_type_code
476 FROM fem_object_catalog_b
477 WHERE object_id = p_object_id;
478
479 IF v_object_type_code <> 'UNDO' THEN
480 -- Validate that the user can write to the object
481 -- User can only write to an object if they can write to the folder,
482 -- and, either, the user object is not read only, or,
483 -- the user created the object
484 SELECT count(*)
485 INTO v_count
486 FROM fem_object_catalog_b o, fem_user_folders f
487 WHERE o.object_id = p_object_id
488 AND (o.object_access_code = 'W' OR o.created_by = pc_user_id)
489 AND o.folder_id = f.folder_id
490 AND f.user_id = pc_user_id
491 AND f.write_flag = 'Y';
492
493 IF v_count = 0 THEN
494 RAISE e_cannot_write_to_object;
495 END IF; /* user can write to object validation */
496 END IF;
497
498 -- Validate effective date range
499 validate_obj_def_effdate (p_object_id => p_object_id,
500 p_new_effective_start_date => p_effective_start_date,
501 p_new_effective_end_date => p_effective_end_date,
502 x_date_range_is_valid => v_date_range_is_valid,
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data);
505
506 IF v_date_range_is_valid = 'Y' THEN
507 SELECT fem_object_definition_id_seq.nextval
508 INTO x_object_definition_id
509 FROM dual;
510
511 SELECT DECODE(t.workflow_enabled_flag,'Y','NEW','NOT_APPLICABLE')
512 INTO v_approval_status_code
513 FROM fem_object_types t, fem_object_catalog_b o
514 WHERE o.object_id = p_object_id
515 AND o.object_type_code = t.object_type_code;
516
517
518 FEM_OBJECT_DEFINITION_PKG.INSERT_ROW (
519 X_ROWID => v_rowid,
520 X_OBJECT_DEFINITION_ID => x_object_definition_id,
521 X_OBJECT_VERSION_NUMBER => pc_object_version_number,
522 X_OBJECT_ID => p_object_id,
523 X_EFFECTIVE_START_DATE => p_effective_start_date,
524 X_EFFECTIVE_END_DATE => p_effective_end_date,
525 X_OBJECT_ORIGIN_CODE => p_object_origin_code,
526 X_APPROVAL_STATUS_CODE => v_approval_status_code,
527 X_OLD_APPROVED_COPY_FLAG => 'N',
528 X_OLD_APPROVED_COPY_OBJ_DEF_ID => null,
529 X_APPROVED_BY => null,
530 X_APPROVAL_DATE => null,
531 X_DISPLAY_NAME => p_obj_def_name,
532 X_DESCRIPTION => p_obj_def_name,
533 X_CREATION_DATE => sysdate,
534 X_CREATED_BY => pc_user_id,
535 X_LAST_UPDATE_DATE => sysdate,
536 X_LAST_UPDATED_BY => pc_user_id,
537 X_LAST_UPDATE_LOGIN => null);
538
539 fem_engines_pkg.put_message(p_app_name =>'FEM',
540 p_msg_name =>'FEM_CREATED_DEF_TXT',
541 p_token1 => 'OBJECT_DEFINITION_NAME',
542 p_value1 => p_obj_def_name,
543 p_trans1 => 'N');
544
545 ELSE
546
547 RAISE e_invalid_effective_date_range;
548
549 END IF;
550
551 IF FND_API.To_Boolean( p_commit ) THEN
552 COMMIT WORK;
553 END IF;
554
555 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
556 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
557 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
558
559 FND_MSG_PUB.Count_And_Get
560 (p_count => x_msg_count,
561 p_data => x_msg_data);
562
563 EXCEPTION
564 WHEN e_invalid_object_origin THEN
565 ROLLBACK TO create_object_definition_pub;
566 x_return_status := pc_ret_sts_error;
567 fem_engines_pkg.put_message(p_app_name =>'FEM'
568 ,p_msg_name =>'FEM_INVALID_OBJECT_ORIGIN'
569 ,p_token1 => 'OBJORIG'
570 ,p_value1 => p_object_origin_code);
571
572 FND_MSG_PUB.Count_And_Get
573 (p_count => x_msg_count,
574 p_data => x_msg_data);
575
576 WHEN e_cannot_write_to_object THEN
577 ROLLBACK TO create_object_definition_pub;
578 x_return_status := pc_ret_sts_error;
579 fem_engines_pkg.put_message(p_app_name =>'FEM'
580 ,p_msg_name =>'FEM_CANNOT_WRITE_TO_OBJECT_ERR');
581
582 FND_MSG_PUB.Count_And_Get
583 (p_count => x_msg_count,
584 p_data => x_msg_data);
585
586 WHEN e_invalid_effective_date_range THEN
587 ROLLBACK TO create_object_definition_pub;
588 x_return_status := pc_ret_sts_error;
589 fem_engines_pkg.put_message(p_app_name =>'FEM',
590 p_msg_name =>'FEM_INVALID_DATE_RANGE_ERR');
591
592 FND_MSG_PUB.Count_And_Get
593 (p_count => x_msg_count,
594 p_data => x_msg_data);
595
596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
597 ROLLBACK TO create_object_definition_pub;
598 x_return_status := pc_ret_sts_unexp_error;
599
600 fem_engines_pkg.tech_message(p_severity => pc_log_level_unexpected,
601 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
602 p_msg_name => 'FEM_BAD_P_API_VER_ERR',p_token1 => 'VALUE',
603 p_value1 => p_api_version, p_trans1 => 'N');
604
605 WHEN OTHERS THEN
606 /* Unexpected exceptions */
607 x_return_status := pc_ret_sts_unexp_error;
608 gv_prg_msg := SQLERRM;
609 gv_callstack := dbms_utility.format_call_stack;
610
611 /* Log the call stack and the Oracle error message to
612 ** FND_LOG with the "unexpected exception" severity level. */
613
614 FEM_ENGINES_PKG.Tech_Message
615 (p_severity => pc_log_level_unexpected,
616 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
617 p_msg_text => gv_prg_msg);
618
619 FEM_ENGINES_PKG.Tech_Message
620 (p_severity => pc_log_level_unexpected,
621 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
622 p_msg_text => gv_callstack);
623
624 /* Log the Oracle error message to the stack. */
625 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
626 p_msg_name => 'FEM_UNEXPECTED_ERROR',
627 P_TOKEN1 => 'ERR_MSG',
628 P_VALUE1 => gv_prg_msg);
629 ROLLBACK TO create_object_definition_pub;
630
631 FND_MSG_PUB.Count_And_Get
632 (p_count => x_msg_count,
633 p_data => x_msg_data);
634
635 END create_object_definition;
636 /* ******************************************************************************/
637 PROCEDURE validate_obj_def_effdate (x_date_range_is_valid OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639 x_msg_data OUT NOCOPY VARCHAR2,
640 p_object_id IN NUMBER,
641 p_new_effective_start_date IN DATE,
642 p_new_effective_end_date IN DATE)
643 IS
644
645 /* ==========================================================================
646 ** If new effective start date is between any of the start date/end date
647 ** of existing Object Definitions for the specified Object ID, then
648 ** x_date_range_is_valid = 'N'
649 ** If new effective end date is between any of the start date/end date
650 ** of existing Object Definitions for the specified Object ID, then
651 ** x_date_range_is_valid = 'N'
652 ** else
653 ** x_date_range_is_valid = 'Y'
654 ** end if.
655 ** ==========================================================================*/
656
657 CURSOR c1 IS
658 SELECT object_definition_id, display_name, effective_start_date, effective_end_date
659 FROM fem_object_definition_vl
660 WHERE object_id = p_object_id;
661
662 BEGIN
663 x_date_range_is_valid := 'Y';
664
665
666 IF p_new_effective_start_date >= p_new_effective_end_date THEN
667 x_date_range_is_valid := 'N';
668 fem_engines_pkg.put_message(p_app_name =>'FEM',
669 p_msg_name =>'FEM_BR_END_LT_START_DATE_ERR',
670 p_token1 => 'END_DATE',
671 p_value1 => fnd_date.date_to_displaydate(p_new_effective_end_date),
672 p_trans1 => 'N',
673 p_token2 => 'START_DATE',
674 p_value2 => fnd_date.date_to_displaydate(p_new_effective_start_date),
675 p_trans2 => 'N');
676
677 ELSE
678
679 FOR object_def IN c1 LOOP
680
681 --dbms_output.put_line('old start date = '||object_def.effective_start_date);
682 --dbms_output.put_line('old end date = '||object_def.effective_end_date);
683 --dbms_output.put_line('new start date = '||p_new_effective_start_date);
684 --dbms_output.put_line('new end date = '||p_new_effective_end_date);
685
686 IF ((p_new_effective_start_date >= object_def.effective_start_date AND
687 p_new_effective_start_date <= object_def.effective_end_date) OR
688 (p_new_effective_end_date <= object_def.effective_end_date AND
689 p_new_effective_end_date >= object_def.effective_start_date)) THEN
690 x_date_range_is_valid := 'N';
691
692 fem_engines_pkg.put_message(p_app_name =>'FEM',
693 p_msg_name =>'FEM_BR_OVRLP_OBJ_DEF_ERR',
694 p_token1 => 'VERSION_NAME',
695 p_value1 => object_def.display_name,
696 p_trans1 => 'Y',
697 p_token2 => 'START_DATE',
698 p_value2 => fnd_date.date_to_displaydate(object_def.effective_start_date),
699 p_trans2 => 'N',
700 p_token3 => 'END_DATE',
701 p_value3 => fnd_date.date_to_displaydate(object_def.effective_end_date),
702 p_trans3 => 'N');
703
704 EXIT;
705 END IF;
706 END LOOP;
707
708 END IF; /* effective date validation */
709
710 /* Standard call to get message count and if count is 1, get message info. */
711 FND_MSG_PUB.Count_And_Get
712 (p_count => x_msg_count,
713 p_data => x_msg_data);
714
715 EXCEPTION
716 WHEN NO_DATA_FOUND THEN
717 x_date_range_is_valid := 'Y';
718
719 FND_MSG_PUB.Count_And_Get
720 (p_count => x_msg_count,
721 p_data => x_msg_data);
722
723 END validate_obj_def_effdate;
724 /* ******************************************************************************/
725 PROCEDURE delete_object (x_msg_count OUT NOCOPY NUMBER,
726 x_msg_data OUT NOCOPY VARCHAR2,
727 x_return_status OUT NOCOPY VARCHAR2,
728 p_api_version IN NUMBER,
729 p_commit IN VARCHAR2,
730 p_object_id IN NUMBER)
731
732 IS
733
734 c_api_name CONSTANT VARCHAR2(30) := 'delete_object';
735 c_api_version CONSTANT NUMBER := 1.0;
736 v_can_delete_object VARCHAR2(1);
737 v_count NUMBER;
738 v_object_type_code FEM_OBJECT_TYPES.object_type_code%TYPE;
739 v_object_name VARCHAR2(150);
740
741 BEGIN
742
743 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
744 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
745 p_msg_text => 'Begin. P_OBJECT_ID: '||p_object_id||' P_COMMIT: '||p_commit);
746
747 /* Standard Start of API savepoint */
748 SAVEPOINT delete_object_pub;
749
750 /* Standard call to check for call compatibility. */
751 IF NOT FND_API.Compatible_API_Call (c_api_version,
752 p_api_version,
753 c_api_name,
754 pc_pkg_name)
755 THEN
756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
757 END IF;
758
759 /* Initialize API return status to success */
760 x_return_status := pc_ret_sts_success;
761
762 -- Bug 4309949: ignore folder security for Undo objects
763 SELECT object_type_code
764 INTO v_object_type_code
765 FROM fem_object_catalog_b
766 WHERE object_id = p_object_id;
767
768 IF v_object_type_code <> 'UNDO' THEN
769 -- Validate that the user can write to the Folder
770 -- User can only delete if object if user can write to the
771 -- folder, and either the object is not read only, or the
772 -- user is the creator of the object.
773 SELECT count(*)
774 INTO v_count
775 FROM fem_object_catalog_b o, fem_user_folders f
776 WHERE o.object_id = p_object_id
777 AND (o.object_access_code = 'W' OR o.created_by = pc_user_id)
778 AND o.folder_id = f.folder_id
779 AND f.user_id = pc_user_id
780 AND f.write_flag = 'Y';
781
782 IF v_count = 0 THEN
783 RAISE e_cannot_write_to_object;
784 END IF; /* user can write to object validation */
785 END IF;
786
787 /* Check if can delete object. */
788 fem_pl_pkg.can_delete_object (
789 p_object_id => p_object_id,
790 x_can_delete_obj => v_can_delete_object,
791 x_msg_count => x_msg_count,
792 x_msg_data => x_msg_data);
793
794 IF v_can_delete_object = 'T' THEN
795 /* get the object name to display in the message */
796 SELECT object_name
797 INTO v_object_name
798 FROM fem_object_catalog_vl
799 WHERE object_id = p_object_id;
800
801 DELETE fem_object_dependencies
802 WHERE object_definition_id IN (
803 SELECT object_definition_id
804 FROM fem_object_definition_b
805 WHERE object_id = p_object_id);
806
807 DELETE fem_object_definition_tl
808 WHERE object_id = p_object_id;
809
810 DELETE fem_object_definition_b
811 WHERE object_id = p_object_id;
812
813 DELETE fem_object_catalog_tl
814 WHERE object_id = p_object_id;
815
816 DELETE fem_object_catalog_b
817 WHERE object_id = p_object_id;
818
819 fem_engines_pkg.put_message(p_app_name =>'FEM',
820 p_msg_name => 'FEM_DELETED_OBJ_TXT',p_token1 => 'OBJECT_NAME',
821 p_value1 => v_object_name, p_trans1 => 'N');
822
823 ELSE
824 /* (v_can_delete_object = 'F') */
825 RAISE e_cannot_delete_object;
826
827 END IF;
828
829 IF FND_API.To_Boolean( p_commit ) THEN
830 COMMIT WORK;
831 END IF;
832
833 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
834 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
835 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
836
837 /* Standard call to get message count and if count is 1, get message info. */
838 FND_MSG_PUB.Count_And_Get
839 (p_count => x_msg_count,
840 p_data => x_msg_data);
841
842 EXCEPTION
843 WHEN e_cannot_delete_object THEN
844 x_return_status := pc_ret_sts_error;
845 fem_engines_pkg.put_message(p_app_name =>'FEM',
846 p_msg_name => 'FEM_CANNOT_DELETE_OBJ_ERR'
847 ,p_token1 => 'OBJECT'
848 ,p_value1 => p_object_id
849 ,p_trans1 => 'N');
850
851 WHEN e_cannot_write_to_object THEN
852 ROLLBACK TO delete_object_pub;
853 x_return_status := pc_ret_sts_error;
854 fem_engines_pkg.put_message(p_app_name =>'FEM'
855 ,p_msg_name =>'FEM_CANNOT_WRITE_TO_OBJECT_ERR');
856
857 FND_MSG_PUB.Count_And_Get
858 (p_count => x_msg_count,
859 p_data => x_msg_data);
860
861 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
862 ROLLBACK TO delete_object_pub;
863 x_return_status := pc_ret_sts_unexp_error;
864
865 fem_engines_pkg.tech_message(p_severity => pc_log_level_unexpected,
866 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
867 p_msg_name => 'FEM_BAD_P_API_VER_ERR'
868 ,p_token1 => 'VALUE'
869 ,p_value1 => p_api_version
870 ,p_trans1 => 'N');
871
872 WHEN OTHERS THEN
873 /* Unexpected exceptions */
874 x_return_status := pc_ret_sts_unexp_error;
875 gv_prg_msg := SQLERRM;
876 gv_callstack := dbms_utility.format_call_stack;
877
878 /* Log the call stack and the Oracle error message to
879 ** FND_LOG with the "unexpected exception" severity level. */
880
881 FEM_ENGINES_PKG.Tech_Message
882 (p_severity => pc_log_level_unexpected,
883 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
884 p_msg_text => gv_prg_msg);
885
886 FEM_ENGINES_PKG.Tech_Message
887 (p_severity => pc_log_level_unexpected,
888 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
889 p_msg_text => gv_callstack);
890
891 /* Log the Oracle error message to the stack. */
892 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
893 p_msg_name => 'FEM_UNEXPECTED_ERROR',
894 P_TOKEN1 => 'ERR_MSG',
895 P_VALUE1 => gv_prg_msg);
896 ROLLBACK TO delete_object_pub;
897
898 FND_MSG_PUB.Count_And_Get
899 (p_count => x_msg_count,
900 p_data => x_msg_data);
901
902 END delete_object;
903 /* ******************************************************************************/
904
905 END fem_object_catalog_util_pkg;