[Home] [Help]
1: package body ffdict as
2: /* $Header: ffdict.pkb 120.14.12020000.4 2012/08/28 08:56:10 ranarra ship $ */
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
747: begin
748: select null into dummy from dual where exists
749: (select /*+ ORDERED
750: INDEX(a FF_DATABASE_ITEMS_PK)
751: INDEX(b FF_USER_ENTITIES_PK) */ null
752: from ff_database_items a,
753: ff_user_entities b
754: where a.user_entity_id = b.user_entity_id
755: and a.user_name = p_item_name
749: (select /*+ ORDERED
750: INDEX(a FF_DATABASE_ITEMS_PK)
751: INDEX(b FF_USER_ENTITIES_PK) */ null
752: from ff_database_items a,
753: ff_user_entities b
754: where a.user_entity_id = b.user_entity_id
755: and a.user_name = p_item_name
756: and
757: ( startup_mode = 'MASTER'
784: when no_data_found then
785: select null into dummy from dual where exists
786: (select /*+ ORDERED
787: INDEX(a FF_DATABASE_ITEMS_TL_N1)
788: INDEX(b FF_USER_ENTITIES_PK) */ null
789: from ff_database_items_tl a,
790: ff_user_entities b
791: where a.user_entity_id = b.user_entity_id
792: and a.translated_user_name = p_item_name
786: (select /*+ ORDERED
787: INDEX(a FF_DATABASE_ITEMS_TL_N1)
788: INDEX(b FF_USER_ENTITIES_PK) */ null
789: from ff_database_items_tl a,
790: ff_user_entities b
791: where a.user_entity_id = b.user_entity_id
792: and a.translated_user_name = p_item_name
793: and
794: ( startup_mode = 'MASTER'
877: --
878: select null into dummy from dual where exists
879: (select /*+ ORDERED
880: INDEX(a FF_DATABASE_ITEMS_PK)
881: INDEX(b FF_USER_ENTITIES_PK) */ null
882: from ff_database_items a,
883: ff_user_entities b
884: where a.user_name = p_tl_user_name
885: and a.user_entity_id <> p_user_entity_id
879: (select /*+ ORDERED
880: INDEX(a FF_DATABASE_ITEMS_PK)
881: INDEX(b FF_USER_ENTITIES_PK) */ null
882: from ff_database_items a,
883: ff_user_entities b
884: where a.user_name = p_tl_user_name
885: and a.user_entity_id <> p_user_entity_id
886: and a.user_entity_id = b.user_entity_id
887: and
922: --
923: select null into dummy from dual where exists
924: (select /*+ ORDERED
925: INDEX(a FF_DATABASE_ITEMS_TL_N1)
926: INDEX(b FF_USER_ENTITIES_PK) */ null
927: from ff_database_items_tl a,
928: ff_user_entities b
929: where a.translated_user_name = p_tl_user_name
930: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
924: (select /*+ ORDERED
925: INDEX(a FF_DATABASE_ITEMS_TL_N1)
926: INDEX(b FF_USER_ENTITIES_PK) */ null
927: from ff_database_items_tl a,
928: ff_user_entities b
929: where a.translated_user_name = p_tl_user_name
930: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
931: and a.user_entity_id = b.user_entity_id
932: and
1222: -- formula being validated.
1223: --
1224: select null into dummy from dual where exists
1225: (select null
1226: from ff_user_entities a
1227: where a.user_entity_name = p_item_name
1228: and
1229: ( startup_mode = 'MASTER'
1230: or
1585: --
1586: -- Fetch business group and legislation details for current user entity
1587: select business_group_id, legislation_code
1588: into bg_id, leg_code
1589: from ff_user_entities
1590: where user_entity_id = p_user_entity_id;
1591: --
1592: -- set error tracking information
1593: hr_utility.set_location('ffdict.validate_dbitem',3);
2131: select 'X' into dummy from dual
2132: where exists
2133: (
2134: select /*+ ORDERED
2135: INDEX(C FF_USER_ENTITIES_FK1)
2136: INDEX(B FF_DATABASE_ITEMS_FK1)
2137: INDEX(A FF_FDI_USAGES_F_N50) */
2138: null
2139: from ff_user_entities c,
2135: INDEX(C FF_USER_ENTITIES_FK1)
2136: INDEX(B FF_DATABASE_ITEMS_FK1)
2137: INDEX(A FF_FDI_USAGES_F_N50) */
2138: null
2139: from ff_user_entities c,
2140: ff_database_items b,
2141: ff_fdi_usages_f a
2142: where a.item_name = b.user_name
2143: and a.usage = 'D'
2179: begin
2180: -- set error tracking information
2181: hr_utility.set_location('ffdict.validate_rpv',1);
2182: --
2183: -- attempt to get BG details from ff_user_entities, which might be mutating
2184: -- so trap error. If it is mutating, can skip validation because a cascade
2185: -- delete is in progress and the deletion will have been validated already
2186: --
2187: select business_group_id, legislation_code
2185: -- delete is in progress and the deletion will have been validated already
2186: --
2187: select business_group_id, legislation_code
2188: into bg_id, leg_code
2189: from ff_user_entities
2190: where user_entity_id = p_user_entity_id;
2191: --
2192: -- set error tracking information
2193: hr_utility.set_location('ffdict.validate_rpv',2);
2283: -- Check whether DB item has already been created for this global
2284: begin
2285: select creator_id
2286: into l_route_id
2287: from ff_user_entities
2288: where creator_id = p_global_id
2289: and creator_type = 'S';
2290: raise item_present;
2291: exception
2366: -- set error tracking information
2367: hr_utility.set_location('ffdict.create_global_dbitem',7);
2368: --
2369: -- Create user entity for this global
2370: insert into ff_user_entities
2371: (user_entity_id,
2372: business_group_id,
2373: legislation_code,
2374: route_id,
2380: created_by,
2381: creation_date
2382: )
2383: values
2384: (ff_user_entities_s.nextval,
2385: p_business_group_id,
2386: p_legislation_code,
2387: l_route_id,
2388: 'N',
2406: creation_date
2407: )
2408: values
2409: (l_route_parameter_id,
2410: ff_user_entities_s.currval,
2411: p_global_id,
2412: p_created_by,
2413: p_creation_date
2414: );
2426: creation_date
2427: )
2428: values
2429: (p_name,
2430: ff_user_entities_s.currval,
2431: p_data_type,
2432: decode(p_data_type,'D',
2433: 'FFFUNC.CD(DECODE(DATA_TYPE,''D'',GLOBAL_VALUE,NULL))',
2434: 'N',
2461: --
2462: -- Delete user entity created for this global with a cascade delete of
2463: -- child records in ff_database_items and ff_route_parameter_values
2464: --
2465: delete from ff_user_entities
2466: where creator_id = p_global_id
2467: and creator_type = 'S';
2468: end delete_global_dbitem;
2469: --
2630: );
2631: end update_global_dbitem;
2632: --
2633:
2634: procedure pur_brd ( p_ff_ue_id in ff_user_entities.USER_ENTITY_ID%type,
2635: p_bg_id in ff_user_entities.business_group_id%type,
2636: p_leg_code in ff_user_entities.legislation_code%type ) IS
2637: l_proc varchar2(30) ;
2638:
2631: end update_global_dbitem;
2632: --
2633:
2634: procedure pur_brd ( p_ff_ue_id in ff_user_entities.USER_ENTITY_ID%type,
2635: p_bg_id in ff_user_entities.business_group_id%type,
2636: p_leg_code in ff_user_entities.legislation_code%type ) IS
2637: l_proc varchar2(30) ;
2638:
2639: l_count binary_integer;
2632: --
2633:
2634: procedure pur_brd ( p_ff_ue_id in ff_user_entities.USER_ENTITY_ID%type,
2635: p_bg_id in ff_user_entities.business_group_id%type,
2636: p_leg_code in ff_user_entities.legislation_code%type ) IS
2637: l_proc varchar2(30) ;
2638:
2639: l_count binary_integer;
2640:
2659: end pur_brd;
2660:
2661: procedure pur_asd IS
2662: l_proc varchar2(30) := 'pur_asd' ;
2663: cursor c_pur_del ( p_ff_ue_id ff_user_entities.USER_ENTITY_ID%type )
2664: is
2665: select 1
2666: from ff_database_items dbi
2667: where dbi.user_entity_id = p_ff_ue_id
2666: from ff_database_items dbi
2667: where dbi.user_entity_id = p_ff_ue_id
2668: and not exists
2669: ( select 1
2670: from ff_user_entities ue
2671: where ue.user_entity_id = p_ff_ue_id
2672:
2673: );
2674: rec_pur c_pur_del%rowtype;