[Home] [Help]
PACKAGE BODY: APPS.FEM_BR_CONDITION_PVT
Source
1 PACKAGE BODY FEM_BR_CONDITION_PVT AS
2 /* $Header: FEMVCONDB.pls 120.0.12010000.2 2008/10/06 17:49:57 huli ship $ */
3
4 --------------------------------------------------------------------------------
5 -- PRIVATE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 G_PKG_NAME constant varchar2(30) := 'FEM_BR_CONDITION_PVT';
9 G_FEM constant varchar2(3) := 'FEM';
10 G_BLOCK constant varchar2(80) := G_FEM||'.PLSQL.'||G_PKG_NAME;
11
12 -- Log Level Constants
13 G_LOG_LEVEL_1 constant number := FND_LOG.Level_Statement;
14 G_LOG_LEVEL_2 constant number := FND_LOG.Level_Procedure;
15 G_LOG_LEVEL_3 constant number := FND_LOG.Level_Event;
16 G_LOG_LEVEL_4 constant number := FND_LOG.Level_Exception;
17 G_LOG_LEVEL_5 constant number := FND_LOG.Level_Error;
18 G_LOG_LEVEL_6 constant number := FND_LOG.Level_Unexpected;
19
20
21 --------------------------------------------------------------------------------
22 -- PRIVATE SPECIFICATIONS
23 --------------------------------------------------------------------------------
24
25 PROCEDURE DeleteConditions(
26 p_obj_def_id in number
27 );
28
29 PROCEDURE CopyConditions(
30 p_source_obj_def_id in number
31 ,p_target_obj_def_id in number
32 ,p_created_by in number
33 ,p_creation_date in date
34 );
35
36 PROCEDURE DeleteCondComponentDtls(
37 p_condition_obj_def_id in number
38 ,p_cond_component_obj_id in number
39 ,p_data_dim_flag in char
40 );
41
42 --------------------------------------------------------------------------------
43 -- PUBLIC BODIES
44 --------------------------------------------------------------------------------
45
46 --
47 -- PROCEDURE
48 -- DeleteObjectDefinition
49 --
50 -- DESCRIPTION
51 -- Deletes all the details records of a Condition Rule Definition.
52 --
53 -- IN
54 -- p_obj_def_id - Object Definition ID.
55 --
56 --------------------------------------------------------------------------------
57 PROCEDURE DeleteObjectDefinition(
58 p_obj_def_id in number
59 )
60 --------------------------------------------------------------------------------
61 IS
62
63 l_api_name constant varchar2(30) := 'DeleteObjectDefinition';
64
65 l_prg_msg VARCHAR2(2000);
66 l_callstack VARCHAR2(2000);
67
68 BEGIN
69
70 FEM_ENGINES_PKG.Tech_Message (
71 p_severity => G_LOG_LEVEL_3
72 ,p_module => G_BLOCK||'.'||l_api_name
73 ,p_msg_text => 'BEGIN'
74 );
75
76 DeleteConditions(
77 p_obj_def_id => p_obj_def_id
78 );
79
80 FEM_ENGINES_PKG.Tech_Message (
81 p_severity => G_LOG_LEVEL_3
82 ,p_module => G_BLOCK||'.'||l_api_name
83 ,p_msg_text => 'END'
84 );
85
86 EXCEPTION
87
88 when others then
89 l_callstack := DBMS_UTILITY.Format_Call_Stack;
90 l_prg_msg := SQLERRM;
91 FEM_ENGINES_PKG.Tech_Message (
92 p_severity => G_LOG_LEVEL_6
93 ,p_module => G_BLOCK||'.'||l_api_name
94 ,p_msg_text => 'others condition, l_callstack:' || l_callstack
95 );
96 FEM_ENGINES_PKG.Tech_Message (
97 p_severity => G_LOG_LEVEL_6
98 ,p_module => G_BLOCK||'.'||l_api_name
99 ,p_msg_text => 'others condition, l_prg_msg:' || l_prg_msg
100 );
101
102 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
103 raise FND_API.G_EXC_UNEXPECTED_ERROR;
104
105 END DeleteObjectDefinition;
106
107
108 --
109 -- PROCEDURE
110 -- CopyObjectDefinition
111 --
112 -- DESCRIPTION
113 -- Creates all the detail records of a new Condition Rule Definition (target)
114 -- by copying the detail records of another Condition Rule Definition (source).
115 --
116 -- IN
117 -- p_source_obj_def_id - Source Object Definition ID.
118 -- p_target_obj_def_id - Target Object Definition ID.
119 -- p_created_by - FND User ID (optional).
120 -- p_creation_date - System Date (optional).
121 --
122 --------------------------------------------------------------------------------
123 PROCEDURE CopyObjectDefinition(
124 p_source_obj_def_id in number
125 ,p_target_obj_def_id in number
126 ,p_created_by in number
127 ,p_creation_date in date
128 )
129 --------------------------------------------------------------------------------
130 IS
131
132 g_api_name constant varchar2(30) := 'CopyObjectDefinition';
133
134 BEGIN
135
136 CopyConditions(
137 p_source_obj_def_id => p_source_obj_def_id
138 ,p_target_obj_def_id => p_target_obj_def_id
139 ,p_created_by => p_created_by
140 ,p_creation_date => p_creation_date
141 );
142
143 EXCEPTION
144
145 when others then
146 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
147 raise FND_API.G_EXC_UNEXPECTED_ERROR;
148
149 END CopyObjectDefinition;
150
151
152 --
153 -- PROCEDURE
154 -- DeleteCondComponent
155 --
156 -- DESCRIPTION
157 -- Deletes all the details records of a Condition Component
158 --
159 -- IN
160 -- p_cond_component_obj_id - Component Object ID.
161 -- p_data_dim_flag - Component type
162 -- p_init_msg_list - Initialize Message List (boolean)
163 --
164 -- OUT
165 -- x_return_status - Return Status of API Call
166 -- x_msg_count - Total Count of Error Messages in API Call
167 -- x_msg_data - Error Message in API Call
168 --------------------------------------------------------------------------------
169 PROCEDURE DeleteCondComponent(
170 p_condition_obj_def_id in number
171 ,p_cond_component_obj_id in number
172 ,p_data_dim_flag in char
173 ,p_init_msg_list in varchar2
174 ,x_return_status out nocopy varchar2
175 ,x_msg_count out nocopy number
176 ,x_msg_data out nocopy varchar2
177 )
178 --------------------------------------------------------------------------------
179 IS
180
181 g_api_name constant varchar2(30) := 'DeleteCondComponent';
182
183 l_prg_msg VARCHAR2(2000);
184 l_callstack VARCHAR2(2000);
185
186 BEGIN
187
188 FEM_ENGINES_PKG.Tech_Message (
189 p_severity => G_LOG_LEVEL_3
190 ,p_module => G_BLOCK||'.'||g_api_name
191 ,p_msg_text => 'BEGIN'
192 );
193
194 -- Initialize API message list if necessary
195 if (FND_API.To_Boolean(p_init_msg_list)) then
196 FND_MSG_PUB.Initialize;
197 end if;
198
199 -- Initialize API return status to success
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201
202 FEM_ENGINES_PKG.Tech_Message (
203 p_severity => G_LOG_LEVEL_3
204 ,p_module => G_BLOCK||'.'||g_api_name
205 ,p_msg_text => 'Before DeleteCondComponentDtls'
206 );
207
208
209 DeleteCondComponentDtls(
210 p_condition_obj_def_id => p_condition_obj_def_id
211 ,p_cond_component_obj_id => p_cond_component_obj_id
212 ,p_data_dim_flag => p_data_dim_flag
213 );
214
215 FEM_ENGINES_PKG.Tech_Message (
216 p_severity => G_LOG_LEVEL_3
217 ,p_module => G_BLOCK||'.'||g_api_name
218 ,p_msg_text => 'After DeleteCondComponentDtls'
219 );
220
221 EXCEPTION
222
223 when FND_API.G_EXC_ERROR then
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 l_callstack := DBMS_UTILITY.Format_Call_Stack;
226 FEM_ENGINES_PKG.Tech_Message (
227 p_severity => G_LOG_LEVEL_6
228 ,p_module => G_BLOCK||'.'||g_api_name
229 ,p_msg_text => 'G_EXC_ERROR, l_callstack:' || l_callstack
230 );
231 FND_MSG_PUB.Count_And_Get(
232 p_count => x_msg_count
233 ,p_data => x_msg_data
234 );
235
236 when FND_API.G_EXC_UNEXPECTED_ERROR then
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 l_callstack := DBMS_UTILITY.Format_Call_Stack;
239 FEM_ENGINES_PKG.Tech_Message (
240 p_severity => G_LOG_LEVEL_6
241 ,p_module => G_BLOCK||'.'||g_api_name
242 ,p_msg_text => 'G_EXC_UNEXPECTED_ERROR, l_callstack:' || l_callstack
243 );
244 FND_MSG_PUB.Count_And_Get(
245 p_count => x_msg_count
246 ,p_data => x_msg_data
247 );
248
249 when others then
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
251 l_callstack := DBMS_UTILITY.Format_Call_Stack;
252 l_prg_msg := SQLERRM;
253 FEM_ENGINES_PKG.Tech_Message (
254 p_severity => G_LOG_LEVEL_6
255 ,p_module => G_BLOCK||'.'||g_api_name
256 ,p_msg_text => 'others, l_callstack:' || l_callstack
257 );
258 FEM_ENGINES_PKG.Tech_Message (
259 p_severity => G_LOG_LEVEL_6
260 ,p_module => G_BLOCK||'.'||g_api_name
261 ,p_msg_text => 'others 1, l_prg_msg:' || l_prg_msg
262 );
263 if (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) then
264 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
265 end if;
266 FND_MSG_PUB.Count_And_Get(
267 p_count => x_msg_count
268 ,p_data => x_msg_data
269 );
270 raise FND_API.G_EXC_UNEXPECTED_ERROR;
271
272 END DeleteCondComponent;
273
274
275
276
277 --------------------------------------------------------------------------------
278 -- PRIVATE BODIES
279 --------------------------------------------------------------------------------
280
281 --
282 -- PROCEDURE
283 -- DeleteConditions
284 --
285 -- DESCRIPTION
286 -- Deletes a Condition Rule Definition by performing deletes on records
287 -- in FEM Condition tables.
288 --
289 -- IN
290 -- p_obj_def_id - Object Definition ID.
291 --
292 --------------------------------------------------------------------------------
293 PROCEDURE DeleteConditions(
294 p_obj_def_id in number
295 )
296 --------------------------------------------------------------------------------
297 IS
298
299 x_return_status varchar2(1);
300 x_msg_count number;
301 x_msg_data varchar2(240);
302
303 v_cond_comp_obj_id number;
304 v_data_dim_flag varchar2(1);
305 v_component_rows_num number;
306
307
308 CURSOR c1 IS
309 SELECT cond_component_obj_id,
310 data_dim_flag
311 from fem_cond_components
312 where condition_obj_def_id = p_obj_def_id;
313 l_api_name constant varchar2(30) := 'DeleteConditions';
314
315
316 BEGIN
317
318 FEM_ENGINES_PKG.Tech_Message (
319 p_severity => G_LOG_LEVEL_3
320 ,p_module => G_BLOCK||'.'||l_api_name
321 ,p_msg_text => 'BEGIN, p_obj_def_id:' || p_obj_def_id
322 );
323
324 OPEN c1;
325
326 LOOP
327
328 FETCH c1 into v_cond_comp_obj_id, v_data_dim_flag;
329
330 SELECT count(*)
331 INTO v_component_rows_num
332 FROM fem_cond_components
333 WHERE cond_component_obj_id = v_cond_comp_obj_id;
334
335 FEM_ENGINES_PKG.Tech_Message (
336 p_severity => G_LOG_LEVEL_3
337 ,p_module => G_BLOCK||'.'||l_api_name
338 ,p_msg_text => 'v_component_rows_num:' || v_component_rows_num
339 );
340
341
342 EXIT WHEN c1%NOTFOUND;
343
344 IF (v_component_rows_num = 1) THEN
345 FEM_ENGINES_PKG.Tech_Message (
346 p_severity => G_LOG_LEVEL_3
347 ,p_module => G_BLOCK||'.'||l_api_name
348 ,p_msg_text => 'before calling DeleteCondComponent'
349 );
350 DeleteCondComponent(
351 p_obj_def_id
352 ,v_cond_comp_obj_id
353 ,v_data_dim_flag
354 ,FND_API.G_FALSE
358 );
355 ,x_return_status
356 ,x_msg_count
357 ,x_msg_data
359 FEM_ENGINES_PKG.Tech_Message (
360 p_severity => G_LOG_LEVEL_3
361 ,p_module => G_BLOCK||'.'||l_api_name
362 ,p_msg_text => 'after calling DeleteCondComponent'
363 );
364 END IF;
365
366 END LOOP;
367
368 CLOSE c1;
369 FEM_ENGINES_PKG.Tech_Message (
370 p_severity => G_LOG_LEVEL_3
371 ,p_module => G_BLOCK||'.'||l_api_name
372 ,p_msg_text => 'continue'
373 );
374
375 delete from fem_cond_components
376 where condition_obj_def_id = p_obj_def_id;
377
378 delete from fem_object_dependencies where object_definition_id = p_obj_def_id;
379 FEM_ENGINES_PKG.Tech_Message (
380 p_severity => G_LOG_LEVEL_3
381 ,p_module => G_BLOCK||'.'||l_api_name
382 ,p_msg_text => 'end'
383 );
384 END DeleteConditions;
385
386
387 --
388 -- PROCEDURE
389 -- CopyConditions
390 --
391 -- DESCRIPTION
392 -- Creates a new Condition Rule Definition by copying records in the
393 -- FEM_COND_COMPONENTS table.
394 --
395 -- IN
396 -- p_source_obj_def_id - Source Object Definition ID.
397 -- p_target_obj_def_id - Target Object Definition ID.
398 -- p_created_by - FND User ID (optional).
399 -- p_creation_date - System Date (optional).
400 --
401 --------------------------------------------------------------------------------
402 PROCEDURE CopyConditions(
403 p_source_obj_def_id in number
404 ,p_target_obj_def_id in number
405 ,p_created_by in number
406 ,p_creation_date in date
407 )
408 --------------------------------------------------------------------------------
409 IS
410 BEGIN
411
412 insert into fem_cond_components(
413 condition_obj_def_id
414 ,cond_component_obj_id
415 ,data_dim_flag
416 ,created_by
417 ,creation_date
418 ,last_updated_by
419 ,last_update_date
420 ,last_update_login
421 ,object_version_number
422 ) select
423 p_target_obj_def_id
424 ,cond_component_obj_id
425 ,data_dim_flag
426 ,nvl(p_created_by,FND_GLOBAL.user_id)
427 ,nvl(p_creation_date,sysdate)
428 ,FND_GLOBAL.user_id
429 ,sysdate
430 ,FND_GLOBAL.login_id
431 ,object_version_number
432 from fem_cond_components
433 where condition_obj_def_id = p_source_obj_def_id;
434
435 END CopyConditions;
436
437 --
438 -- PROCEDURE
439 -- DeleteCondComponentDtls
440 --
441 -- DESCRIPTION
442 -- Deletes Condition Rule Details by performing deletes on records
443 -- in FEM Condition tables.
444 --
445 -- IN
446 -- p_cond_component_obj_id - Component Object ID.
447 -- p_data_dim_flag - Data Dim Flag
448 --------------------------------------------------------------------------------
449 PROCEDURE DeleteCondComponentDtls(
450 p_condition_obj_def_id in number
451 ,p_cond_component_obj_id in number
452 ,p_data_dim_flag in char
453 )
454 --------------------------------------------------------------------------------
455 IS
456
457 l_cond_component_obj_def_id number;
458 v_component_rows_num number;
459 l_data_edit_lock_exists varchar2(1);
460 l_approval_edit_lock_exists varchar2(1);
461
462 l_api_name constant varchar2(30) := 'DeleteCondComponentDtls';
463 l_prg_msg VARCHAR2(2000);
464 l_callstack VARCHAR2(2000);
465
466
467 BEGIN
468 FEM_ENGINES_PKG.Tech_Message (
469 p_severity => G_LOG_LEVEL_3
470 ,p_module => G_BLOCK||'.'||l_api_name
471 ,p_msg_text => 'BEGIN, p_condition_obj_def_id:' || p_condition_obj_def_id
472 || ' p_cond_component_obj_id:' || p_cond_component_obj_id
473 || ' p_data_dim_flag:' || p_data_dim_flag
474 );
475
476 SELECT count(*)
477 INTO v_component_rows_num
478 FROM fem_cond_components
479 WHERE cond_component_obj_id = p_cond_component_obj_id
480 AND condition_obj_def_id <> p_condition_obj_def_id;
481
482 FEM_ENGINES_PKG.Tech_Message (
483 p_severity => G_LOG_LEVEL_3
484 ,p_module => G_BLOCK||'.'||l_api_name
485 ,p_msg_text => 'v_component_rows_num:' || v_component_rows_num
486 );
487
488 IF (v_component_rows_num = 0) THEN
489
490 FEM_ENGINES_PKG.Tech_Message (
491 p_severity => G_LOG_LEVEL_3
492 ,p_module => G_BLOCK||'.'||l_api_name
493 ,p_msg_text => 'v_component_rows_num = 0'
494 );
495
496 SELECT OBJECT_DEFINITION_ID
497 INTO l_cond_component_obj_def_id
498 FROM FEM_OBJECT_DEFINITION_VL
499 WHERE OBJECT_ID = p_cond_component_obj_id;
500
501 FEM_ENGINES_PKG.Tech_Message (
502 p_severity => G_LOG_LEVEL_3
503 ,p_module => G_BLOCK||'.'||l_api_name
504 ,p_msg_text => 'v_component_rows_num = 0, l_cond_component_obj_def_id:'
505 || l_cond_component_obj_def_id
506 );
507
511 ,x_approval_edit_lock_exists => l_approval_edit_lock_exists
508 -- Check to see if we can delete the Object Definition
509 FEM_PL_PKG.get_object_def_edit_locks(
510 p_object_definition_id => l_cond_component_obj_def_id
512 ,x_data_edit_lock_exists => l_data_edit_lock_exists
513 );
514
515 FEM_ENGINES_PKG.Tech_Message (
516 p_severity => G_LOG_LEVEL_3
517 ,p_module => G_BLOCK||'.'||l_api_name
518 ,p_msg_text => 'v_component_rows_num = 0, l_approval_edit_lock_exists:'
519 || l_approval_edit_lock_exists || ' l_data_edit_lock_exists:'
520 || l_data_edit_lock_exists
521 );
522
523
524 -- Do not throw error just do not delete
525 /* if (not FND_API.To_Boolean(l_data_edit_lock_exists)) then
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 raise FND_API.G_EXC_ERROR;
528 end if;
529 */
530
531 IF (not FND_API.To_Boolean(l_data_edit_lock_exists)) THEN
532 FEM_ENGINES_PKG.Tech_Message (
533 p_severity => G_LOG_LEVEL_3
534 ,p_module => G_BLOCK||'.'||l_api_name
535 ,p_msg_text => 'not l_data_edit_lock_exists'
536 );
537
538 IF (p_data_dim_flag = 'T') THEN
539
540 FEM_ENGINES_PKG.Tech_Message (
541 p_severity => G_LOG_LEVEL_3
542 ,p_module => G_BLOCK||'.'||l_api_name
543 ,p_msg_text => 'p_data_dim_flag = T'
544 );
545
546 delete from fem_cond_data_cmp_st_dtl
547 where cond_data_cmp_obj_def_id = l_cond_component_obj_def_id;
548
549 delete from fem_cond_data_cmp_steps
550 where cond_data_cmp_obj_def_id = l_cond_component_obj_def_id;
551
552 delete from fem_cond_data_cmp_tables
553 where cond_data_cmp_obj_def_id = l_cond_component_obj_def_id;
554
555 ELSE
556
557
558 FEM_ENGINES_PKG.Tech_Message (
559 p_severity => G_LOG_LEVEL_3
560 ,p_module => G_BLOCK||'.'||l_api_name
561 ,p_msg_text => 'p_data_dim_flag <> T'
562 );
563 delete from fem_cond_dim_cmp_dtl
564 where cond_dim_cmp_obj_def_id = l_cond_component_obj_def_id;
565
566 delete from fem_cond_dim_components
567 where cond_dim_cmp_obj_def_id = l_cond_component_obj_def_id;
568
569 END IF;
570
571
572 FEM_ENGINES_PKG.Tech_Message (
573 p_severity => G_LOG_LEVEL_3
574 ,p_module => G_BLOCK||'.'||l_api_name
575 ,p_msg_text => 'Before delete from fem_object_dependencies'
576 );
577 delete from fem_object_dependencies
578 where object_definition_id = l_cond_component_obj_def_id;
579
580 delete from fem_object_definition_vl
581 where object_definition_id = l_cond_component_obj_def_id;
582
583 delete from fem_object_catalog_vl
584 where object_id = p_cond_component_obj_id;
585
586 FEM_ENGINES_PKG.Tech_Message (
587 p_severity => G_LOG_LEVEL_3
588 ,p_module => G_BLOCK||'.'||l_api_name
589 ,p_msg_text => 'After delete from fem_object_catalog_vl'
590 );
591
592 END IF;
593
594 END IF;
595
596 FEM_ENGINES_PKG.Tech_Message (
597 p_severity => G_LOG_LEVEL_3
598 ,p_module => G_BLOCK||'.'||l_api_name
599 ,p_msg_text => 'END'
600 );
601
602 EXCEPTION
603 when others then
604 l_callstack := DBMS_UTILITY.Format_Call_Stack;
605 l_prg_msg := SQLERRM;
606 FEM_ENGINES_PKG.Tech_Message (
607 p_severity => G_LOG_LEVEL_6
608 ,p_module => G_BLOCK||'.'||l_api_name
609 ,p_msg_text => 'others, l_callstack:' || l_callstack
610 );
611 FEM_ENGINES_PKG.Tech_Message (
612 p_severity => G_LOG_LEVEL_6
613 ,p_module => G_BLOCK||'.'||l_api_name
614 ,p_msg_text => 'others 1, l_prg_msg:' || l_prg_msg
615 );
616
617 END DeleteCondComponentDtls;
618
619 END FEM_BR_CONDITION_PVT;