[Home] [Help]
1: package body ffdict as
2: /* $Header: ffdict.pkb 120.14 2006/10/13 14:13:52 arashid noship $ */
3: --
4: -- Temporary storage for UE details to be used in cascade delete of DB items
5: -- to get round mutating ff_user_entities
6: --
7: tmp_ue_id ff_user_entities.user_entity_id%TYPE;
8: tmp_bg_id ff_user_entities.business_group_id%TYPE;
9: tmp_leg_code ff_user_entities.legislation_code%TYPE;
3: --
4: -- Temporary storage for UE details to be used in cascade delete of DB items
5: -- to get round mutating ff_user_entities
6: --
7: tmp_ue_id ff_user_entities.user_entity_id%TYPE;
8: tmp_bg_id ff_user_entities.business_group_id%TYPE;
9: tmp_leg_code ff_user_entities.legislation_code%TYPE;
10: --
11: -- Temporary storage for global database item information.
4: -- Temporary storage for UE details to be used in cascade delete of DB items
5: -- to get round mutating ff_user_entities
6: --
7: tmp_ue_id ff_user_entities.user_entity_id%TYPE;
8: tmp_bg_id ff_user_entities.business_group_id%TYPE;
9: tmp_leg_code ff_user_entities.legislation_code%TYPE;
10: --
11: -- Temporary storage for global database item information.
12: --
5: -- to get round mutating ff_user_entities
6: --
7: tmp_ue_id ff_user_entities.user_entity_id%TYPE;
8: tmp_bg_id ff_user_entities.business_group_id%TYPE;
9: tmp_leg_code ff_user_entities.legislation_code%TYPE;
10: --
11: -- Temporary storage for global database item information.
12: --
13: g_glb_id ff_globals_f.global_id%TYPE;
10: --
11: -- Temporary storage for global database item information.
12: --
13: g_glb_id ff_globals_f.global_id%TYPE;
14: g_glb_ueid ff_user_entities.user_entity_id%TYPE;
15: g_glb_dbi ff_database_items.user_name%TYPE;
16: --
17: g_debug boolean := hr_utility.debug_enabled;
18: ------------------------------ fetch_ue_details -------------------------------
19: --
20: -- NAME
21: -- fetch_ue_details
22: -- DESCRIPTION
23: -- Fetches UE details (assuming a cascade delete from ff_user_entities is
24: -- not in progress).
25: --
26: -------------------------------------------------------------------------------
27: --
39: select ue.business_group_id
40: , nvl(ue.legislation_code, bg.legislation_code)
41: into l_business_group_id
42: , l_legislation_code
43: from ff_user_entities ue
44: , per_business_groups_perf bg
45: where ue.user_entity_id = p_user_entity_id
46: and bg.business_group_id (+)= ue.business_group_id
47: ;
77: -------------------------------------------------------------------------------
78: --
79: procedure set_global_dbi_details (global_id in number) is
80: cursor csr_ueid(p_global_id in number) is
81: select /*+ INDEX(ue FF_USER_ENTITIES_N51) */ dbi.user_entity_id
82: , dbi.user_name
83: from ff_user_entities ue
84: , ff_database_items dbi
85: where ue.creator_type = 'S'
79: procedure set_global_dbi_details (global_id in number) is
80: cursor csr_ueid(p_global_id in number) is
81: select /*+ INDEX(ue FF_USER_ENTITIES_N51) */ dbi.user_entity_id
82: , dbi.user_name
83: from ff_user_entities ue
84: , ff_database_items dbi
85: where ue.creator_type = 'S'
86: and ue.creator_id = p_global_id
87: and dbi.user_entity_id = ue.user_entity_id
746: begin
747: select null into dummy from dual where exists
748: (select /*+ ORDERED
749: INDEX(a FF_DATABASE_ITEMS_PK)
750: INDEX(b FF_USER_ENTITIES_PK) */ null
751: from ff_database_items a,
752: ff_user_entities b
753: where a.user_entity_id = b.user_entity_id
754: and a.user_name = p_item_name
748: (select /*+ ORDERED
749: INDEX(a FF_DATABASE_ITEMS_PK)
750: INDEX(b FF_USER_ENTITIES_PK) */ null
751: from ff_database_items a,
752: ff_user_entities b
753: where a.user_entity_id = b.user_entity_id
754: and a.user_name = p_item_name
755: and
756: ( startup_mode = 'MASTER'
783: when no_data_found then
784: select null into dummy from dual where exists
785: (select /*+ ORDERED
786: INDEX(a FF_DATABASE_ITEMS_TL_N1)
787: INDEX(b FF_USER_ENTITIES_PK) */ null
788: from ff_database_items_tl a,
789: ff_user_entities b
790: where a.user_entity_id = b.user_entity_id
791: and a.translated_user_name = p_item_name
785: (select /*+ ORDERED
786: INDEX(a FF_DATABASE_ITEMS_TL_N1)
787: INDEX(b FF_USER_ENTITIES_PK) */ null
788: from ff_database_items_tl a,
789: ff_user_entities b
790: where a.user_entity_id = b.user_entity_id
791: and a.translated_user_name = p_item_name
792: and
793: ( startup_mode = 'MASTER'
876: --
877: select null into dummy from dual where exists
878: (select /*+ ORDERED
879: INDEX(a FF_DATABASE_ITEMS_PK)
880: INDEX(b FF_USER_ENTITIES_PK) */ null
881: from ff_database_items a,
882: ff_user_entities b
883: where a.user_name = p_tl_user_name
884: and a.user_entity_id <> p_user_entity_id
878: (select /*+ ORDERED
879: INDEX(a FF_DATABASE_ITEMS_PK)
880: INDEX(b FF_USER_ENTITIES_PK) */ null
881: from ff_database_items a,
882: ff_user_entities b
883: where a.user_name = p_tl_user_name
884: and a.user_entity_id <> p_user_entity_id
885: and a.user_entity_id = b.user_entity_id
886: and
921: --
922: select null into dummy from dual where exists
923: (select /*+ ORDERED
924: INDEX(a FF_DATABASE_ITEMS_TL_N1)
925: INDEX(b FF_USER_ENTITIES_PK) */ null
926: from ff_database_items_tl a,
927: ff_user_entities b
928: where a.translated_user_name = p_tl_user_name
929: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
923: (select /*+ ORDERED
924: INDEX(a FF_DATABASE_ITEMS_TL_N1)
925: INDEX(b FF_USER_ENTITIES_PK) */ null
926: from ff_database_items_tl a,
927: ff_user_entities b
928: where a.translated_user_name = p_tl_user_name
929: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
930: and a.user_entity_id = b.user_entity_id
931: and
1221: -- formula being validated.
1222: --
1223: select null into dummy from dual where exists
1224: (select null
1225: from ff_user_entities a
1226: where a.user_entity_name = p_item_name
1227: and
1228: ( startup_mode = 'MASTER'
1229: or
1582: --
1583: -- Fetch business group and legislation details for current user entity
1584: select business_group_id, legislation_code
1585: into bg_id, leg_code
1586: from ff_user_entities
1587: where user_entity_id = p_user_entity_id;
1588: --
1589: -- set error tracking information
1590: hr_utility.set_location('ffdict.validate_dbitem',3);
2128: select 'X' into dummy from dual
2129: where exists
2130: (
2131: select /*+ ORDERED
2132: INDEX(C FF_USER_ENTITIES_FK1)
2133: INDEX(B FF_DATABASE_ITEMS_FK1)
2134: INDEX(A FF_FDI_USAGES_F_N50) */
2135: null
2136: from ff_user_entities c,
2132: INDEX(C FF_USER_ENTITIES_FK1)
2133: INDEX(B FF_DATABASE_ITEMS_FK1)
2134: INDEX(A FF_FDI_USAGES_F_N50) */
2135: null
2136: from ff_user_entities c,
2137: ff_database_items b,
2138: ff_fdi_usages_f a
2139: where a.item_name = b.user_name
2140: and a.usage = 'D'
2176: begin
2177: -- set error tracking information
2178: hr_utility.set_location('ffdict.validate_rpv',1);
2179: --
2180: -- attempt to get BG details from ff_user_entities, which might be mutating
2181: -- so trap error. If it is mutating, can skip validation because a cascade
2182: -- delete is in progress and the deletion will have been validated already
2183: --
2184: select business_group_id, legislation_code
2182: -- delete is in progress and the deletion will have been validated already
2183: --
2184: select business_group_id, legislation_code
2185: into bg_id, leg_code
2186: from ff_user_entities
2187: where user_entity_id = p_user_entity_id;
2188: --
2189: -- set error tracking information
2190: hr_utility.set_location('ffdict.validate_rpv',2);
2280: -- Check whether DB item has already been created for this global
2281: begin
2282: select creator_id
2283: into l_route_id
2284: from ff_user_entities
2285: where creator_id = p_global_id
2286: and creator_type = 'S';
2287: raise item_present;
2288: exception
2363: -- set error tracking information
2364: hr_utility.set_location('ffdict.create_global_dbitem',7);
2365: --
2366: -- Create user entity for this global
2367: insert into ff_user_entities
2368: (user_entity_id,
2369: business_group_id,
2370: legislation_code,
2371: route_id,
2377: created_by,
2378: creation_date
2379: )
2380: values
2381: (ff_user_entities_s.nextval,
2382: p_business_group_id,
2383: p_legislation_code,
2384: l_route_id,
2385: 'N',
2403: creation_date
2404: )
2405: values
2406: (l_route_parameter_id,
2407: ff_user_entities_s.currval,
2408: p_global_id,
2409: p_created_by,
2410: p_creation_date
2411: );
2423: creation_date
2424: )
2425: values
2426: (p_name,
2427: ff_user_entities_s.currval,
2428: p_data_type,
2429: decode(p_data_type,'D',
2430: 'FFFUNC.CD(DECODE(DATA_TYPE,''D'',GLOBAL_VALUE,NULL))',
2431: 'N',
2458: --
2459: -- Delete user entity created for this global with a cascade delete of
2460: -- child records in ff_database_items and ff_route_parameter_values
2461: --
2462: delete from ff_user_entities
2463: where creator_id = p_global_id
2464: and creator_type = 'S';
2465: end delete_global_dbitem;
2466: --