[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_PRS_UTILS_PVT
Source
1 PACKAGE BODY FEM_DIM_PRS_UTILS_PVT AS
2 /* $Header: FEMVDPMB.pls 120.0 2005/06/06 21:21:08 appldev noship $ */
3
4 G_PKG_NAME constant varchar2(30) := 'FEM_DIM_PRS_UTILS_PVT';
5
6 /* ---------------------- Private Routine prototypes -----------------------*/
7
8 CURSOR g_xdim_csr (c_dimension_id in number)
9 IS
10 select dim.dimension_id
11 ,dim.dimension_varchar_label
12 ,xdim.member_b_table_name
13 ,xdim.member_tl_table_name
14 ,xdim.attribute_table_name
15 ,xdim.member_col
16 ,xdim.personal_hierarchy_table_name
17 from fem_dimensions_b dim
18 ,fem_xdim_dimensions xdim
19 where xdim.dimension_id = dim.dimension_id
20 and xdim.hier_editor_managed_flag = 'Y'
21 and xdim.read_only_flag = 'N'
22 and xdim.composite_dimension_flag ='N'
23 and xdim.dimension_active_flag = 'Y'
24 and (
25 ((c_dimension_id is not null) and (dim.dimension_id = c_dimension_id))
26 or ( c_dimension_id is null )
27 )
28 ORDER BY dim.dimension_id ;
29
30
31 PROCEDURE Purge_Personal_Members_Pvt
32 ( p_xdim_rec IN g_xdim_csr%ROWTYPE,
33 p_user_id IN number
34 );
35
36 /* ------------------ End Private Routines prototypes ----------------------*/
37
38
39 /*===========================================================================+
40 | PROCEDURE pd |
41 +===========================================================================*/
42 PROCEDURE pd( p_message IN VARCHAR2)
43 IS
44 BEGIN
45 NULL ;
46 --DBMS_OUTPUT.Put_Line(p_message) ;
47 END pd ;
48 /*---------------------------------------------------------------------------*/
49
50
51 /*===========================================================================+
52 | PROCEDURE Purge_Personal_Metadata |
53 +===========================================================================*/
54 PROCEDURE Purge_Personal_Metadata
55 ( p_api_version IN NUMBER
56 ,p_init_msg_list IN VARCHAR2 := NULL
57 ,p_commit IN VARCHAR2 := NULL
58 ,p_validation_level IN NUMBER := 0
59 ,p_user_id IN VARCHAR2 := NULL
60 ,p_dimension_id IN NUMBER := NULL
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 )
65 IS
66 --
67 l_api_name constant varchar2(30) := 'Purge_Personal_Metadata';
68 l_api_version constant number := 1.0;
69
70 l_return_status varchar2(1);
71 l_msg_count number;
72 l_msg_data varchar2(240);
73
74 l_user_id number;
75 l_dimension_id number;
76 l_pers_hier_table_name varchar2(30);
77 --
78 CURSOR l_hier_obj_csr
79 IS
80 SELECT hierarchy_obj_id
81 FROM fem_hierarchies
82 WHERE dimension_id = l_dimension_id
83 AND hierarchy_usage_code = 'PLANNING'
84 AND hierarchy_type_code <> 'DAG'
85 AND personal_flag = 'Y'
86 AND created_by = l_user_id ;
87 --
88 BEGIN
89
90 -- API Savepoint
91 savepoint Purge_Personal_Metadata_Pvt;
92
93 -- Call to check for call compatibility
94 if not FND_API.Compatible_Api_Call(
95 l_api_version
96 ,p_api_version
97 ,l_api_name
98 ,G_PKG_NAME
99 ) then
100 raise FND_API.G_EXC_UNEXPECTED_ERROR;
101 end if;
102
103 -- Initialize API message list if necessary
104 if p_init_msg_list = 'Y' then
105 FND_MSG_PUB.Initialize;
106 end if;
107
108 -- Initialize API return status to success
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 -- Default the user id if it is null
112 if (p_user_id is not null) then
113 l_user_id := p_user_id;
114 else
115 l_user_id := FND_GLOBAL.user_id;
116 end if;
117
118 -- Every record in the XDimension cursor must be processed.
119 -- If a null is passed for dimension id, all dimensions are processed.
120 -- If a valid dimension id is passed, only that dimension is processed.
121 FOR l_xdim_rec in g_xdim_csr (c_dimension_id => p_dimension_id)
122 LOOP
123
124 l_dimension_id := l_xdim_rec.dimension_id;
125
126 -- First Delete Personal Hierarchies
127 l_pers_hier_table_name := l_xdim_rec.personal_hierarchy_table_name;
128
129 if (l_pers_hier_table_name is not null) then
130
131 for l_hier_obj_rec in l_hier_obj_csr loop
132
133 --pd('l_pers_hier_table_name:' || l_pers_hier_table_name);
134 --pd('hierarchy_obj_id:' || l_hier_obj_rec.hierarchy_obj_id);
135 FEM_HIER_UTILS_PVT.Delete_Hierarchy (
136 p_api_version => 1.0
137 ,p_hier_table_name => l_pers_hier_table_name
138 ,p_hier_obj_id => l_hier_obj_rec.hierarchy_obj_id
139 ,p_return_status => l_return_status
140 ,p_msg_count => l_msg_count
141 ,p_msg_data => l_msg_data
142 );
143
144 if (l_return_status = FND_API.G_RET_STS_ERROR) then
145 raise FND_API.G_EXC_ERROR;
146 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
147 raise FND_API.G_EXC_UNEXPECTED_ERROR;
148 end if;
149
150 end loop;
151
152 end if;
153
154 -- Second, Delete Personal Members
155 Purge_Personal_Members_Pvt (
156 p_xdim_rec => l_xdim_rec
157 ,p_user_id => l_user_id
158 );
159
160 -- Third, Delete Personal Groups
161 delete from fem_dim_attr_grps
162 where dimension_group_id in (
163 select dimension_group_id
164 from fem_dimension_grps_b
165 where dimension_id = l_dimension_id
166 and personal_flag = 'Y'
167 and created_by = l_user_id
168 );
169
170 delete from fem_dimension_grps_tl
171 where dimension_group_id in (
172 select dimension_group_id
173 from fem_dimension_grps_b
174 where dimension_id = l_dimension_id
175 and personal_flag = 'Y'
176 and created_by = l_user_id
177 );
178
179 delete from fem_dimension_grps_b
180 where dimension_id = l_dimension_id
181 and personal_flag = 'Y'
182 and created_by = l_user_id;
183
184 -- Last, Delete Personal Attributes
185 delete from fem_dim_attr_versions_tl avt
186 where exists (
187 select null
188 from fem_dim_attr_versions_b avb
189 ,fem_dim_attributes_b ab
190 where avb.version_id = avt.version_id
191 --and avb.personal_flag = 'Y'
192 --and avb.created_by = l_user_id
193 and ab.attribute_id = avb.attribute_id
194 and ab.dimension_id = l_dimension_id
195 and ab.personal_flag = 'Y'
196 and ab.created_by = l_user_id
197 );
198
199 delete from fem_dim_attr_versions_b
200 --where personal_flag = 'Y'
201 --and created_by = l_user_id
202 where attribute_id in (
203 select attribute_id
204 from fem_dim_attributes_b
205 where dimension_id = l_dimension_id
206 and personal_flag = 'Y'
207 and created_by = l_user_id
208 );
209
210 delete from fem_dim_attributes_tl
211 where attribute_id in (
212 select attribute_id
213 from fem_dim_attributes_b
214 where dimension_id = l_dimension_id
215 and personal_flag = 'Y'
216 and created_by = l_user_id
217 );
218
219 delete from fem_dim_attributes_b
220 where dimension_id = l_dimension_id
221 and personal_flag = 'Y'
222 and created_by = l_user_id;
223
224 end loop;
225
226 -- Check for p_commit
227 if p_commit = 'Y' then
228 commit work;
229 end if;
230
231 -- Call to get message count and if count is 1, get message info.
232 FND_MSG_PUB.Count_And_Get(
233 p_count => x_msg_count
234 ,p_data => x_msg_data
235 );
236
237 EXCEPTION
238
239 when FND_API.G_EXC_ERROR then
240 rollback to Purge_Personal_Metadata_Pvt;
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 FND_MSG_PUB.Count_And_Get(
243 p_count => x_msg_count
244 ,p_data => x_msg_data
245 );
246
247 when FND_API.G_EXC_UNEXPECTED_ERROR then
248 rollback to Purge_Personal_Metadata_Pvt;
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 FND_MSG_PUB.Count_And_Get(
251 p_count => x_msg_count
252 ,p_data => x_msg_data
253 );
254
255 when others then
256 rollback to Purge_Personal_Metadata_Pvt;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 if (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) then
259 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
260 end if;
261 FND_MSG_PUB.Count_And_Get(
262 p_count => x_msg_count
263 ,p_data => x_msg_data
264 );
265
266 END Purge_Personal_Metadata;
267 /*---------------------------------------------------------------------------*/
268
269
270 /*===========================================================================+
271 | PROCEDURE Purge_Personal_Members_Pvt |
272 +===========================================================================*/
273 PROCEDURE Purge_Personal_Members_Pvt
274 ( p_xdim_rec IN g_xdim_csr%ROWTYPE,
275 p_user_id IN number
276 )
277 IS
278 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Personal_Members_Pvt';
279 l_dimension_varchar_label FEM_DIMENSIONS_B.dimension_varchar_label%TYPE;
280 l_sql_stmt VARCHAR2(2000) := NULL;
281 BEGIN
282
283 l_dimension_varchar_label := p_xdim_rec.dimension_varchar_label;
284
285 if (l_dimension_varchar_label = 'CAL_PERIOD') then
286
287 -- delete from fem_<xdim>_attr
288 delete from fem_cal_periods_attr
289 where cal_period_id in (
290 select cal_period_id
291 from fem_cal_periods_b
292 where personal_flag = 'Y'
293 and created_by = p_user_id
294 );
295
296 -- delete from fem_<xdim>_tl
297 delete from fem_cal_periods_tl
298 where cal_period_id in (
299 select cal_period_id
300 from fem_cal_periods_b
301 where personal_flag = 'Y'
302 and created_by = p_user_id
303 );
304
305 -- delete from fem_<xdim>_b
306 delete from fem_cal_periods_b
307 where personal_flag = 'Y'
308 and created_by = p_user_id;
309
310 elsif (l_dimension_varchar_label = 'NATURAL_ACCOUNT') then
311
312 -- delete from fem_<xdim>_attr
313 delete from fem_nat_accts_attr
314 where natural_account_id in (
315 select natural_account_id
316 from fem_nat_accts_b
317 where personal_flag = 'Y'
318 and created_by = p_user_id
319 );
320
321 -- delete from fem_<xdim>_tl
322 delete from fem_nat_accts_tl
323 where natural_account_id in (
324 select natural_account_id
325 from fem_nat_accts_b
326 where personal_flag = 'Y'
327 and created_by = p_user_id
328 );
329
330 -- delete from fem_<xdim>_b
331 delete from fem_nat_accts_b
332 where personal_flag = 'Y'
333 and created_by = p_user_id;
334
335 elsif (l_dimension_varchar_label = 'PRODUCT') then
336
337 -- delete from fem_<xdim>_attr
338 delete from fem_products_attr
339 where product_id in (
340 select product_id
341 from fem_products_b
342 where personal_flag = 'Y'
343 and created_by = p_user_id
344 );
345
346 -- delete from fem_<xdim>_tl
347 delete from fem_products_tl
348 where product_id in (
349 select product_id
350 from fem_products_b
351 where personal_flag = 'Y'
352 and created_by = p_user_id
353 );
354
355 -- delete from fem_<xdim>_b
356 delete from fem_products_b
357 where personal_flag = 'Y'
358 and created_by = p_user_id;
359
360 elsif (l_dimension_varchar_label = 'OBJECT') then
361
362 null;
363 -- OBJECT is not an XDimension with standard _ATTR, _TL, and _B tables
364
365 elsif (l_dimension_varchar_label = 'DATASET') then
366
367 -- delete from fem_<xdim>_attr
368 delete from fem_datasets_attr
369 where dataset_code in (
370 select dataset_code
371 from fem_datasets_b
372 where personal_flag = 'Y'
373 and created_by = p_user_id
374 );
375
376 -- delete from fem_<xdim>_tl
377 delete from fem_datasets_tl
378 where dataset_code in (
379 select dataset_code
380 from fem_datasets_b
381 where personal_flag = 'Y'
382 and created_by = p_user_id
383 );
384
385 -- delete from fem_<xdim>_b
386 delete from fem_datasets_b
387 where personal_flag = 'Y'
388 and created_by = p_user_id;
389
390 elsif (l_dimension_varchar_label = 'SOURCE_SYSTEM') then
391
392 -- No fem_<xdim>_attr table
393
394 -- delete from fem_<xdim>_tl
395 delete from fem_source_systems_tl
396 where source_system_code in (
397 select source_system_code
398 from fem_source_systems_b
399 where personal_flag = 'Y'
400 and created_by = p_user_id
401 );
402
403 -- delete from fem_<xdim>_b
404 delete from fem_source_systems_b
405 where personal_flag = 'Y'
406 and created_by = p_user_id;
407
408 elsif (l_dimension_varchar_label = 'LEDGER') then
412 where ledger_id in (
409
410 -- delete from fem_<xdim>_attr
411 delete from fem_ledgers_attr
413 select ledger_id
414 from fem_ledgers_b
415 where personal_flag = 'Y'
416 and created_by = p_user_id
417 );
418
419 -- delete from fem_<xdim>_tl
420 delete from fem_ledgers_tl
421 where ledger_id in (
422 select ledger_id
423 from fem_ledgers_b
424 where personal_flag = 'Y'
425 and created_by = p_user_id
426 );
427
428 -- delete from fem_<xdim>_b
429 delete from fem_ledgers_b
430 where personal_flag = 'Y'
431 and created_by = p_user_id;
432
433 elsif (l_dimension_varchar_label = 'COMPANY_COST_CENTER_ORG') then
434
435 -- delete from fem_<xdim>_attr
436 delete from fem_cctr_orgs_attr
437 where company_cost_center_org_id in (
438 select company_cost_center_org_id
439 from fem_cctr_orgs_b
440 where personal_flag = 'Y'
441 and created_by = p_user_id
442 );
443
444 -- delete from fem_<xdim>_tl
445 delete from fem_cctr_orgs_tl
446 where company_cost_center_org_id in (
447 select company_cost_center_org_id
448 from fem_cctr_orgs_b
449 where personal_flag = 'Y'
450 and created_by = p_user_id
451 );
452
453 -- delete from fem_<xdim>_b
454 delete from fem_cctr_orgs_b
455 where personal_flag = 'Y'
456 and created_by = p_user_id;
457
458 elsif (l_dimension_varchar_label = 'CURRENCY') then
459
460 -- not supported in DHM
461 null;
462
463 elsif (l_dimension_varchar_label = 'ACTIVITY') then
464
465 -- No personal activity hierarhies
466 null;
467
468 elsif (l_dimension_varchar_label = 'COST_OBJECT') then
469
470 -- No personal cost object hierarhies
471 null;
472
473 elsif (l_dimension_varchar_label = 'FINANCIAL_ELEMENT') then
474
475 -- delete from fem_<xdim>_attr
476 delete from fem_fin_elems_attr
477 where financial_elem_id in (
478 select financial_elem_id
479 from fem_fin_elems_b
480 where personal_flag = 'Y'
481 and created_by = p_user_id
482 );
483
484 -- delete from fem_<xdim>_tl
485 delete from fem_fin_elems_tl
486 where financial_elem_id in (
487 select financial_elem_id
488 from fem_fin_elems_b
489 where personal_flag = 'Y'
490 and created_by = p_user_id
491 );
492
493 -- delete from fem_<xdim>_b
494 delete from fem_fin_elems_b
495 where personal_flag = 'Y'
496 and created_by = p_user_id;
497
498 elsif (l_dimension_varchar_label = 'CHANNEL') then
499
500 -- delete from fem_<xdim>_attr
501 delete from fem_channels_attr
502 where channel_id in (
503 select channel_id
504 from fem_channels_b
505 where personal_flag = 'Y'
506 and created_by = p_user_id
507 );
508
509 -- delete from fem_<xdim>_tl
510 delete from fem_channels_tl
511 where channel_id in (
512 select channel_id
513 from fem_channels_b
514 where personal_flag = 'Y'
515 and created_by = p_user_id
516 );
517
518 -- delete from fem_<xdim>_b
519 delete from fem_channels_b
520 where personal_flag = 'Y'
521 and created_by = p_user_id;
522
523 elsif (l_dimension_varchar_label = 'LINE_ITEM') then
524
525 -- delete from fem_<xdim>_attr
526 delete from fem_ln_items_attr
527 where line_item_id in (
528 select line_item_id
529 from fem_ln_items_b
530 where personal_flag = 'Y'
531 and created_by = p_user_id
532 );
533
534 -- delete from fem_<xdim>_tl
535 delete from fem_ln_items_tl
536 where line_item_id in (
537 select line_item_id
538 from fem_ln_items_b
539 where personal_flag = 'Y'
540 and created_by = p_user_id
541 );
542
543 -- delete from fem_<xdim>_b
544 delete from fem_ln_items_b
545 where personal_flag = 'Y'
546 and created_by = p_user_id;
547
548 elsif (l_dimension_varchar_label = 'PROJECT') then
549
550 -- delete from fem_<xdim>_attr
551 delete from fem_projects_attr
552 where project_id in (
553 select project_id
554 from fem_projects_b
555 where personal_flag = 'Y'
556 and created_by = p_user_id
557 );
558
559 -- delete from fem_<xdim>_tl
560 delete from fem_projects_tl
561 where project_id in (
562 select project_id
563 from fem_projects_b
564 where personal_flag = 'Y'
565 and created_by = p_user_id
566 );
567
568 -- delete from fem_<xdim>_b
569 delete from fem_projects_b
570 where personal_flag = 'Y'
571 and created_by = p_user_id;
572
573 elsif (l_dimension_varchar_label = 'CUSTOMER') then
574
575 -- delete from fem_<xdim>_attr
579 from fem_customers_b
576 delete from fem_customers_attr
577 where customer_id in (
578 select customer_id
580 where personal_flag = 'Y'
581 and created_by = p_user_id
582 );
583
584 -- delete from fem_<xdim>_tl
585 delete from fem_customers_tl
586 where customer_id in (
587 select customer_id
588 from fem_customers_b
589 where personal_flag = 'Y'
590 and created_by = p_user_id
591 );
592
593 -- delete from fem_<xdim>_b
594 delete from fem_customers_b
595 where personal_flag = 'Y'
596 and created_by = p_user_id;
597
598 elsif (l_dimension_varchar_label = 'ENTITY') then
599
600 -- delete from fem_<xdim>_attr
601 delete from fem_entities_attr
602 where entity_id in (
603 select entity_id
604 from fem_entities_b
605 where personal_flag = 'Y'
606 and created_by = p_user_id
607 );
608
609 -- delete from fem_<xdim>_tl
610 delete from fem_entities_tl
611 where entity_id in (
612 select entity_id
613 from fem_entities_b
614 where personal_flag = 'Y'
615 and created_by = p_user_id
616 );
617
618 -- delete from fem_<xdim>_b
619 delete from fem_entities_b
620 where personal_flag = 'Y'
621 and created_by = p_user_id;
622
623 elsif (l_dimension_varchar_label = 'GEOGRAPHY') then
624
625 -- delete from fem_<xdim>_attr
626 delete from fem_geography_attr
627 where geography_id in (
628 select geography_id
629 from fem_geography_b
630 where personal_flag = 'Y'
631 and created_by = p_user_id
632 );
633
634 -- delete from fem_<xdim>_tl
635 delete from fem_geography_tl
636 where geography_id in (
637 select geography_id
638 from fem_geography_b
639 where personal_flag = 'Y'
640 and created_by = p_user_id
641 );
642
643 -- delete from fem_<xdim>_b
644 delete from fem_geography_b
645 where personal_flag = 'Y'
646 and created_by = p_user_id;
647
648 elsif (l_dimension_varchar_label = 'TASK') then
649
650 -- delete from fem_<xdim>_attr
651 delete from fem_tasks_attr
652 where task_id in (
653 select task_id
654 from fem_tasks_b
655 where personal_flag = 'Y'
656 and created_by = p_user_id
657 );
658
659 -- delete from fem_<xdim>_tl
660 delete from fem_tasks_tl
661 where task_id in (
662 select task_id
663 from fem_tasks_b
664 where personal_flag = 'Y'
665 and created_by = p_user_id
666 );
667
668 -- delete from fem_<xdim>_b
669 delete from fem_tasks_b
670 where personal_flag = 'Y'
671 and created_by = p_user_id;
672
673 elsif (l_dimension_varchar_label = 'BUDGET') then
674
675 -- delete from fem_<xdim>_attr
676 delete from fem_budgets_attr
677 where budget_id in (
678 select budget_id
679 from fem_budgets_b
680 where personal_flag = 'Y'
681 and created_by = p_user_id
682 );
683
684 -- delete from fem_<xdim>_tl
685 delete from fem_budgets_tl
686 where budget_id in (
687 select budget_id
688 from fem_budgets_b
689 where personal_flag = 'Y'
690 and created_by = p_user_id
691 );
692
693 -- delete from fem_<xdim>_b
694 delete from fem_budgets_b
695 where personal_flag = 'Y'
696 and created_by = p_user_id;
697
698 elsif (l_dimension_varchar_label = 'USER_DIM1') then
699
700 -- delete from fem_<xdim>_attr
701 delete from fem_user_dim1_attr
702 where user_dim1_id in (
703 select user_dim1_id
704 from fem_user_dim1_b
705 where personal_flag = 'Y'
706 and created_by = p_user_id
707 );
708
709 -- delete from fem_<xdim>_tl
710 delete from fem_user_dim1_tl
711 where user_dim1_id in (
712 select user_dim1_id
713 from fem_user_dim1_b
714 where personal_flag = 'Y'
715 and created_by = p_user_id
716 );
717
718 -- delete from fem_<xdim>_b
719 delete from fem_user_dim1_b
720 where personal_flag = 'Y'
721 and created_by = p_user_id;
722
723 elsif (l_dimension_varchar_label = 'USER_DIM2') then
724
725 -- delete from fem_<xdim>_attr
726 delete from fem_user_dim2_attr
727 where user_dim2_id in (
728 select user_dim2_id
729 from fem_user_dim2_b
730 where personal_flag = 'Y'
731 and created_by = p_user_id
732 );
733
734 -- delete from fem_<xdim>_tl
735 delete from fem_user_dim2_tl
736 where user_dim2_id in (
737 select user_dim2_id
738 from fem_user_dim2_b
739 where personal_flag = 'Y'
740 and created_by = p_user_id
741 );
742
743 -- delete from fem_<xdim>_b
744 delete from fem_user_dim2_b
745 where personal_flag = 'Y'
746 and created_by = p_user_id;
747
748 elsif (l_dimension_varchar_label = 'USER_DIM3') then
749
750 -- delete from fem_<xdim>_attr
751 delete from fem_user_dim3_attr
752 where user_dim3_id in (
753 select user_dim3_id
754 from fem_user_dim3_b
755 where personal_flag = 'Y'
756 and created_by = p_user_id
757 );
758
759 -- delete from fem_<xdim>_tl
763 from fem_user_dim3_b
760 delete from fem_user_dim3_tl
761 where user_dim3_id in (
762 select user_dim3_id
764 where personal_flag = 'Y'
765 and created_by = p_user_id
766 );
767
768 -- delete from fem_<xdim>_b
769 delete from fem_user_dim3_b
770 where personal_flag = 'Y'
771 and created_by = p_user_id;
772
773 elsif (l_dimension_varchar_label = 'USER_DIM4') then
774
775 -- delete from fem_<xdim>_attr
776 delete from fem_user_dim4_attr
777 where user_dim4_id in (
778 select user_dim4_id
779 from fem_user_dim4_b
780 where personal_flag = 'Y'
781 and created_by = p_user_id
782 );
783
784 -- delete from fem_<xdim>_tl
785 delete from fem_user_dim4_tl
786 where user_dim4_id in (
787 select user_dim4_id
788 from fem_user_dim4_b
789 where personal_flag = 'Y'
790 and created_by = p_user_id
791 );
792
793 -- delete from fem_<xdim>_b
794 delete from fem_user_dim4_b
795 where personal_flag = 'Y'
796 and created_by = p_user_id;
797
798 elsif (l_dimension_varchar_label = 'USER_DIM5') then
799
800 -- delete from fem_<xdim>_attr
801 delete from fem_user_dim5_attr
802 where user_dim5_id in (
803 select user_dim5_id
804 from fem_user_dim5_b
805 where personal_flag = 'Y'
806 and created_by = p_user_id
807 );
808
809 -- delete from fem_<xdim>_tl
810 delete from fem_user_dim5_tl
811 where user_dim5_id in (
812 select user_dim5_id
813 from fem_user_dim5_b
814 where personal_flag = 'Y'
815 and created_by = p_user_id
816 );
817
818 -- delete from fem_<xdim>_b
819 delete from fem_user_dim5_b
820 where personal_flag = 'Y'
821 and created_by = p_user_id;
822
823 elsif (l_dimension_varchar_label = 'USER_DIM6') then
824
825 -- delete from fem_<xdim>_attr
826 delete from fem_user_dim6_attr
827 where user_dim6_id in (
828 select user_dim6_id
829 from fem_user_dim6_b
830 where personal_flag = 'Y'
831 and created_by = p_user_id
832 );
833
834 -- delete from fem_<xdim>_tl
835 delete from fem_user_dim6_tl
836 where user_dim6_id in (
837 select user_dim6_id
838 from fem_user_dim6_b
839 where personal_flag = 'Y'
840 and created_by = p_user_id
841 );
842
843 -- delete from fem_<xdim>_b
844 delete from fem_user_dim6_b
845 where personal_flag = 'Y'
846 and created_by = p_user_id;
847
848 elsif (l_dimension_varchar_label = 'USER_DIM7') then
849
850 -- delete from fem_<xdim>_attr
851 delete from fem_user_dim7_attr
852 where user_dim7_id in (
853 select user_dim7_id
854 from fem_user_dim7_b
855 where personal_flag = 'Y'
856 and created_by = p_user_id
857 );
858
859 -- delete from fem_<xdim>_tl
860 delete from fem_user_dim7_tl
861 where user_dim7_id in (
862 select user_dim7_id
863 from fem_user_dim7_b
864 where personal_flag = 'Y'
865 and created_by = p_user_id
866 );
867
868 -- delete from fem_<xdim>_b
869 delete from fem_user_dim7_b
870 where personal_flag = 'Y'
871 and created_by = p_user_id;
872
873 elsif (l_dimension_varchar_label = 'USER_DIM8') then
874
875 -- delete from fem_<xdim>_attr
876 delete from fem_user_dim8_attr
877 where user_dim8_id in (
878 select user_dim8_id
879 from fem_user_dim8_b
880 where personal_flag = 'Y'
881 and created_by = p_user_id
882 );
883
884 -- delete from fem_<xdim>_tl
885 delete from fem_user_dim8_tl
886 where user_dim8_id in (
887 select user_dim8_id
888 from fem_user_dim8_b
889 where personal_flag = 'Y'
890 and created_by = p_user_id
891 );
892
893 -- delete from fem_<xdim>_b
894 delete from fem_user_dim8_b
895 where personal_flag = 'Y'
896 and created_by = p_user_id;
897
898 elsif (l_dimension_varchar_label = 'USER_DIM9') then
899
900 -- delete from fem_<xdim>_attr
901 delete from fem_user_dim9_attr
902 where user_dim9_id in (
903 select user_dim9_id
904 from fem_user_dim9_b
905 where personal_flag = 'Y'
906 and created_by = p_user_id
907 );
908
909 -- delete from fem_<xdim>_tl
910 delete from fem_user_dim9_tl
911 where user_dim9_id in (
912 select user_dim9_id
913 from fem_user_dim9_b
914 where personal_flag = 'Y'
915 and created_by = p_user_id
916 );
917
918 -- delete from fem_<xdim>_b
919 delete from fem_user_dim9_b
920 where personal_flag = 'Y'
921 and created_by = p_user_id;
922
923 elsif (l_dimension_varchar_label = 'USER_DIM10') then
924
925 -- delete from fem_<xdim>_attr
926 delete from fem_user_dim10_attr
927 where user_dim10_id in (
928 select user_dim10_id
929 from fem_user_dim10_b
930 where personal_flag = 'Y'
931 and created_by = p_user_id
932 );
933
934 -- delete from fem_<xdim>_tl
935 delete from fem_user_dim10_tl
936 where user_dim10_id in (
937 select user_dim10_id
938 from fem_user_dim10_b
939 where personal_flag = 'Y'
940 and created_by = p_user_id
941 );
942
943 -- delete from fem_<xdim>_b
944 delete from fem_user_dim10_b
945 where personal_flag = 'Y'
946 and created_by = p_user_id;
947
948 else
949
950 l_sql_stmt := NULL;
951
952 -- delete from fem_<xdim>_attr
953 if (p_xdim_rec.attribute_table_name is not null) then
954 l_sql_stmt :=
955 'delete from '|| p_xdim_rec.attribute_table_name ||
956 ' where ' || p_xdim_rec.member_col ||
957 ' in ( select ' || p_xdim_rec.member_col ||
958 ' from ' || p_xdim_rec.member_b_table_name ||
959 ' where personal_flag = ''Y''' ||
960 ' and created_by = ' || p_user_id ||
961 ' )';
962 execute immediate l_sql_stmt;
963 end if;
964
965 -- delete from fem_<xdim>_tl
966 if (p_xdim_rec.member_tl_table_name is not null) then
967 l_sql_stmt :=
968 'delete from '|| p_xdim_rec.member_tl_table_name ||
969 ' where ' || p_xdim_rec.member_col ||
970 ' in ( select ' || p_xdim_rec.member_col ||
971 ' from ' || p_xdim_rec.member_b_table_name ||
972 ' where personal_flag = ''Y''' ||
973 ' and created_by = ' || p_user_id ||
974 ' )';
975 execute immediate l_sql_stmt;
976 end if;
977
978 -- delete from fem_<xdim>_b
979 l_sql_stmt :=
980 'delete from '|| p_xdim_rec.member_b_table_name ||
981 ' where personal_flag = ''Y''' ||
982 ' and created_by = ' || p_user_id;
983 execute immediate l_sql_stmt;
984
985 l_sql_stmt := NULL;
986
987 end if;
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 IF l_sql_stmt IS NOT NULL THEN
992 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
993 FND_MESSAGE.SET_TOKEN('PKG_NAME', g_pkg_name);
994 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name) ;
995 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_sql_stmt);
996 FND_MSG_PUB.Add;
997 END IF;
998 RAISE;
999 END Purge_Personal_Members_Pvt ;
1000 /*---------------------------------------------------------------------------*/
1001
1002
1003 END FEM_DIM_PRS_UTILS_PVT;