[Home] [Help]
36: \ | | /
37: The effect on the entity horizon is that \------------/
38: either the legislation code OR the | |
39: business group id may be specified. The ------------ |
40: columns on the table ff_user_entities should | Business | |
41: not BOTH be populated. | group | |
42: ------------ |
43: The allowed combinations for these 2 columns Y |
44: are shown in the table below. This is now the | |
121: Name Ver DD-MON-YYYY BugNo. and text
122: -----------------------------------------
123: ckesanap 115.148 25-Apr-2008 Bug 6955080. In delete_keyflex_dict, chaged the
124: where condition clause for deleting the
125: existing user_entity from ff_user_entities.
126: ckesanap 115.147 07-Aug-2007 Bug 5930272. Added fnd_message.retrieve() in
127: insert_user_entity_main() to clear the message
128: in the FND message queue as the error raised
129: in checkformat() is being handled.
155: divicker 115.140 01-JUN-2006 11511 branch merge
156: arashid 115.139 11-MAY-2006 Add code for running legislation-specific
157: database item translation from
158: process_pay_dyndbi_changes.
159: arashid 115.138 09-MAY-2006 Don't disable ff_user_entities delete
160: triggers as thay are required to delete
161: ff_database_items_tl rows (other child
162: rows are cascade deleted).
163: arashid 115.137 05-MAY-2006 Use substr to restrict names to 80
506: flex creation.
507: amills 09-DEC-1997 changed l_title to select from fnd_descriptive_
508: flexs_vl rather than take a hardcoded value
509: in create_org_pay_flex_dict
510: alogue 28-OCT-1997 legislation_code used in delete from ff_user_entities
511: in delete_keyflex_dict. Fix to bug 513364.
512: mreid 24-SEP-1997 Changed table_names for release 11 security.
513: dsaxby 15-SEP-1997 Changed substr to substrb to avoid problems with
514: generating NLS database items.
528: to the payroll model for User Defined Data,
529: thereby being consistent for Payroll and Formula.
530: This means that for User Defined Data only the
531: business group id should be populated on
532: ff_user_entities, not legislation code as well.
533: (previously both columns were populated). Refer
534: to the Notes above for more information. The
535: change dated 15-JUL-1994 is undone.
536: mwcallag 15-JUL-1994 procedure 'new_defined_balance' altered to
862: rgeflg varchar2(1);
863: begin
864: IF (g_triggers_altered) THEN
865: -- validate the name is OK. This was originally called as part of
866: -- the FF_USER_ENTITIES_BRI trigger so we add it here.
867: begin
868: -- Check if name legal format eg no spaces, or special characters
869: hr_chkfmt.checkformat (p_db_item_name, 'DB_ITEM_NAME', p_db_item_name,
870: null,null,'Y',rgeflg,null);
879: hr_utility.raise_error;
880: end;
881: --
882: -- create user entity
883: select ff_user_entities_s.nextval
884: into p_user_entity_id
885: from sys.dual;
886:
887: BEGIN
884: into p_user_entity_id
885: from sys.dual;
886:
887: BEGIN
888: insert into ff_user_entities
889: (user_entity_id,
890: business_group_id,
891: legislation_code,
892: route_id,
907: p_description
908: from dual
909: where not exists (
910: select null
911: from ff_user_entities a
912: where a.user_entity_name = p_db_item_name
913: and
914: ( p_startup_mode = 'MASTER'
915: or
949: raise;
950: END;
951: ELSE -- g_triggers_altered is FALSE so use existing trigger validation
952: --
953: select ff_user_entities_s.nextval
954: into p_user_entity_id
955: from sys.dual;
956: --
957: insert into ff_user_entities
953: select ff_user_entities_s.nextval
954: into p_user_entity_id
955: from sys.dual;
956: --
957: insert into ff_user_entities
958: (user_entity_id,
959: business_group_id,
960: legislation_code,
961: route_id,
1054: pdr.priority
1055: from pay_dimension_routes pdr
1056: where pdr.balance_dimension_id = p_bal_dim
1057: and not exists (select null
1058: from ff_user_entities u
1059: where u.creator_id = p_defined_balance_id
1060: and u.creator_type = 'RB'
1061: and u.route_id = pdr.route_id)
1062: order by pdr.balance_dimension_id, pdr.priority;
1066: p_business_group_id in number,
1067: p_startup_mode in varchar2)
1068: is
1069: select /*+ ORDERED
1070: INDEX(a FF_USER_ENTITIES_N50)
1071: INDEX(fdbi FF_DATABASE_ITEMS_FK1)
1072: INDEX(fdi FF_USER_ENTITIES_N50)
1073: USE_NL(a fdbi fdi) */
1074: formula_id
1068: is
1069: select /*+ ORDERED
1070: INDEX(a FF_USER_ENTITIES_N50)
1071: INDEX(fdbi FF_DATABASE_ITEMS_FK1)
1072: INDEX(fdi FF_USER_ENTITIES_N50)
1073: USE_NL(a fdbi fdi) */
1074: formula_id
1075: from
1076: ff_user_entities a,
1072: INDEX(fdi FF_USER_ENTITIES_N50)
1073: USE_NL(a fdbi fdi) */
1074: formula_id
1075: from
1076: ff_user_entities a,
1077: ff_database_items fdbi,
1078: ff_fdi_usages_f fdi
1079: where fdi.usage = 'D'
1080: and fdi.item_name = fdbi.user_name
1129: delete ff_compiled_info_f where formula_id = r_sel_ffci.formula_id;
1130:
1131: end loop;
1132:
1133: delete ff_user_entities a
1134: where a.user_entity_name = l_db_item_name
1135: and
1136: ( p_startup_mode = 'MASTER'
1137: or
1521: legislation_code,
1522: save_run_balance
1523: from pay_defined_balances b
1524: where not exists (
1525: select null from ff_user_entities u
1526: where b.defined_balance_id = u.creator_id
1527: and u.creator_type = 'B')
1528: and mod(defined_balance_id, p_maxworkers) = p_worker_id
1529: order by b.defined_balance_id;
1537: from pay_defined_balances b
1538: where /* def bal ue simply doesn't exist but has an associated pdr */
1539: ((
1540: not exists (
1541: select null from ff_user_entities u
1542: where b.defined_balance_id = u.creator_id
1543: and u.creator_type = 'RB')
1544: and exists
1545: (select null
1552: select pdr.balance_dimension_id
1553: from pay_dimension_routes pdr
1554: where pdr.balance_dimension_id = b.balance_dimension_id
1555: and not exists (select null
1556: from ff_user_entities ue
1557: where ue.creator_id = b.defined_balance_id
1558: and ue.route_id = pdr.route_id
1559: and ue.creator_type = 'RB'))
1560: ))
1645:
1646: -- delete_compiled_formula_priv(c2rec.defined_balance_id , 'RB', '%',
1647: -- c2rec.legislation_code);
1648:
1649: delete ff_user_entities
1650: where creator_id = c2rec.defined_balance_id
1651: and creator_type = 'RB';
1652:
1653: create_alternative_dbis(c2rec.defined_balance_id,
1692: legislation_code,
1693: save_run_balance
1694: from pay_defined_balances a
1695: where not exists (
1696: select null from ff_user_entities u
1697: where a.defined_balance_id = u.creator_id
1698: and u.creator_type = 'B')
1699: and mod(defined_balance_id, p_maxworkers) = p_worker_id
1700: and (a.legislation_code = p_leg_code
1713: from pay_defined_balances a
1714: where /* def bal ue simply doesn't exist but has an associated pdr */
1715: ((
1716: not exists (
1717: select null from ff_user_entities u
1718: where a.defined_balance_id = u.creator_id
1719: and u.creator_type = 'RB')
1720: and exists
1721: (select null
1728: select pdr.balance_dimension_id
1729: from pay_dimension_routes pdr
1730: where pdr.balance_dimension_id = a.balance_dimension_id
1731: and not exists (select null
1732: from ff_user_entities ue
1733: where ue.creator_id = a.defined_balance_id
1734: and ue.route_id = pdr.route_id
1735: and ue.creator_type = 'RB'))
1736: ))
1827:
1828: -- delete_compiled_formula_priv(c2rec.defined_balance_id , 'RB', '%',
1829: -- c2rec.legislation_code);
1830:
1831: delete ff_user_entities
1832: where creator_id = c2rec.defined_balance_id
1833: and creator_type = 'RB';
1834:
1835: create_alternative_dbis(c2rec.defined_balance_id,
1872: --
1873: -- delete_compiled_formula_priv(p_defined_balance_id, 'B', '%', p_legislation_code);
1874: -- delete_compiled_formula_priv(p_defined_balance_id, 'RB', '%', p_legislation_code);
1875:
1876: delete from ff_user_entities
1877: where creator_id = p_defined_balance_id
1878: and creator_type = 'B';
1879: --
1880: delete from ff_user_entities
1876: delete from ff_user_entities
1877: where creator_id = p_defined_balance_id
1878: and creator_type = 'B';
1879: --
1880: delete from ff_user_entities
1881: where creator_id = p_defined_balance_id
1882: and creator_type = 'RB';
1883: --
1884: hrdyndbi.new_defined_balance
1916: ) is
1917: l_route_parameter_id number;
1918: l_route_id number;
1919: l_user_entity_id number;
1920: l_created_by ff_user_entities.created_by%type;
1921: l_last_login ff_user_entities.last_update_login%type;
1922: l_user_entity_name ff_user_entities.user_entity_name%type;
1923: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1924: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1917: l_route_parameter_id number;
1918: l_route_id number;
1919: l_user_entity_id number;
1920: l_created_by ff_user_entities.created_by%type;
1921: l_last_login ff_user_entities.last_update_login%type;
1922: l_user_entity_name ff_user_entities.user_entity_name%type;
1923: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1924: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1925: l_route_name ff_routes.route_name%type;
1918: l_route_id number;
1919: l_user_entity_id number;
1920: l_created_by ff_user_entities.created_by%type;
1921: l_last_login ff_user_entities.last_update_login%type;
1922: l_user_entity_name ff_user_entities.user_entity_name%type;
1923: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1924: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1925: l_route_name ff_routes.route_name%type;
1926: l_route_cd varchar2(60);
1919: l_user_entity_id number;
1920: l_created_by ff_user_entities.created_by%type;
1921: l_last_login ff_user_entities.last_update_login%type;
1922: l_user_entity_name ff_user_entities.user_entity_name%type;
1923: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1924: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1925: l_route_name ff_routes.route_name%type;
1926: l_route_cd varchar2(60);
1927: l_route_lud varchar2(60);
1920: l_created_by ff_user_entities.created_by%type;
1921: l_last_login ff_user_entities.last_update_login%type;
1922: l_user_entity_name ff_user_entities.user_entity_name%type;
1923: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1924: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1925: l_route_name ff_routes.route_name%type;
1926: l_route_cd varchar2(60);
1927: l_route_lud varchar2(60);
1928: l_route_lub number;
1929: BEGIN
1930: --
1931: -- get the user entity id to be used:
1932: --
1933: select ff_user_entities_s.currval
1934: into l_user_entity_id
1935: from dual;
1936: --
1937: -- get the relevant information for the user entity we are using
1944: into l_route_parameter_id,
1945: l_created_by,
1946: l_last_login,
1947: l_user_entity_name
1948: from ff_user_entities ENTITY
1949: , ff_route_parameters RPARAM
1950: where ENTITY.user_entity_id = l_user_entity_id
1951: and RPARAM.route_id = ENTITY.route_id
1952: and RPARAM.sequence_no = p_sequence_no;
1965: into l_user_entity_name,
1966: l_route_id,
1967: l_ent_bg_id,
1968: l_ent_lc
1969: from ff_user_entities ENTITY
1970: where ENTITY.user_entity_id = l_user_entity_id;
1971:
1972: hr_utility.trace('entity_name: ' || l_user_entity_name);
1973: hr_utility.trace('entity route id: ' || to_char(l_route_id));
2059: ) is
2060: l_item_name ff_database_items.user_name%type;
2061: l_user_name ff_database_items.user_name%type;
2062: l_user_entity_id number;
2063: l_created_by ff_user_entities.created_by%type;
2064: l_last_login ff_user_entities.last_update_login%type;
2065: l_db number;
2066: l_exists VARCHAR2(2);
2067: l_clash boolean;
2060: l_item_name ff_database_items.user_name%type;
2061: l_user_name ff_database_items.user_name%type;
2062: l_user_entity_id number;
2063: l_created_by ff_user_entities.created_by%type;
2064: l_last_login ff_user_entities.last_update_login%type;
2065: l_db number;
2066: l_exists VARCHAR2(2);
2067: l_clash boolean;
2068: --
2090: --
2091: if (p_user_entity_id is not NULL) then
2092: l_user_entity_id:=p_user_entity_id;
2093: else
2094: select ff_user_entities_s.currval
2095: into l_user_entity_id
2096: from dual;
2097: end if;
2098:
2106: into l_created_by,
2107: l_last_login,
2108: bg_id,
2109: leg_code
2110: from ff_user_entities
2111: where user_entity_id = l_user_entity_id;
2112: --
2113: IF (g_triggers_altered) THEN
2114: -- Get the startup mode
2450: p_created_by in number,
2451: p_last_login in number,
2452: p_record_inserted out nocopy boolean
2453: ) is
2454: l_user_entity_name ff_user_entities.user_entity_name%type;
2455: BEGIN
2456: --
2457: -- simply call insert_user_entity_main
2458: --
2533: --
2534: -- get the user entity id from its sequence
2535: --
2536: begin
2537: SELECT ff_user_entities_s.nextval
2538: INTO l_user_entities_seq
2539: FROM dual;
2540: end;
2541: --
2565: --
2566: -- Check the name is OK
2567: --
2568: -- validate the name is OK. This was originally called as part of
2569: -- the FF_USER_ENTITIES_BRI trigger so we add it here.
2570:
2571: begin
2572: -- Check if name legal format eg no spaces, or special characters
2573: hr_chkfmt.checkformat (l_user_entity_name, 'DB_ITEM_NAME', l_user_entity_name,
2591: -- Check if entity already exists before inserting it
2592:
2593: select count(*)
2594: into l_dummy_fetch_var
2595: from ff_user_entities
2596: where user_entity_name = l_user_entity_name
2597: and nvl (legislation_code, ' ') = nvl (p_legislation_code, ' ')
2598: and nvl (business_group_id, -1) = nvl (p_business_group_id, -1);
2599:
2616: hrrbdeib_trace_off;
2617: raise;
2618: END;
2619: --
2620: -- populate the ff_user_entities table :
2621: --
2622: BEGIN
2623:
2624: IF (g_triggers_altered) THEN
2621: --
2622: BEGIN
2623:
2624: IF (g_triggers_altered) THEN
2625: insert into ff_user_entities (
2626: user_entity_id,
2627: business_group_id,
2628: legislation_code,
2629: route_id,
2653: sysdate
2654: from dual
2655: where not exists (
2656: select null
2657: from ff_user_entities a
2658: where a.user_entity_name = l_user_entity_name
2659: and
2660: ( startup_mode = 'MASTER'
2661: or
2684: )
2685: )
2686: ));
2687: ELSE
2688: insert into ff_user_entities (
2689: user_entity_id,
2690: business_group_id,
2691: legislation_code,
2692: route_id,
2717: END IF;
2718: p_record_inserted := TRUE;
2719: EXCEPTION WHEN OTHERS THEN
2720: hrrbdeib_trace_on;
2721: hr_utility.trace('insert ff_user_entities: ue: ' || l_user_entity_name);
2722: hr_utility.trace('insert ff_user_entities: route_name: ' || p_route_name);
2723: hrrbdeib_trace_off;
2724: raise;
2725: END;
2718: p_record_inserted := TRUE;
2719: EXCEPTION WHEN OTHERS THEN
2720: hrrbdeib_trace_on;
2721: hr_utility.trace('insert ff_user_entities: ue: ' || l_user_entity_name);
2722: hr_utility.trace('insert ff_user_entities: route_name: ' || p_route_name);
2723: hrrbdeib_trace_off;
2724: raise;
2725: END;
2726:
2766: select /* INDEX(fdi FF_FDI_USAGES_F_N50)*/
2767: distinct fdi.formula_id,
2768: fdi.effective_start_date
2769: from
2770: ff_user_entities ent,
2771: ff_database_items dbi,
2772: ff_fdi_usages_f fdi
2773: where fdi.item_name = dbi.user_name
2774: and fdi.usage = 'D'
2854: and nvl(b.legislation_code, p_leg_code) = p_leg_code)))
2855: and exists (select null from
2856: ff_database_items dbi
2857: where fdi.item_name = dbi.user_name
2858: and exists (select /*+ INDEX(ent FF_USER_ENTITIES_N51)*/ null from
2859: ff_user_entities ent
2860: where ent.user_entity_id = dbi.user_entity_id
2861: and ent.creator_id = p_creator_id
2862: and ent.creator_type = p_creator_type
2855: and exists (select null from
2856: ff_database_items dbi
2857: where fdi.item_name = dbi.user_name
2858: and exists (select /*+ INDEX(ent FF_USER_ENTITIES_N51)*/ null from
2859: ff_user_entities ent
2860: where ent.user_entity_id = dbi.user_entity_id
2861: and ent.creator_id = p_creator_id
2862: and ent.creator_type = p_creator_type
2863: and ent.user_entity_name like p_user_entity_name
2949: BEGIN
2950:
2951: -- delete_compiled_formula_priv(p_element_type_id, 'E', '%', null);
2952:
2953: DELETE FROM ff_user_entities
2954: WHERE creator_id = p_element_type_id
2955: AND creator_type = 'E';
2956: END delete_element_type_dict;
2957: --
3124: IF l_record_inserted THEN
3125: --
3126: -- Fetch the user_entity_id.
3127: --
3128: select ff_user_entities_s.currval
3129: into l_user_entity_id
3130: from dual;
3131: --
3132: -- insert the element type id for the where clause filler
3358: IF l_record_inserted THEN
3359: --
3360: -- Fetch the user_entity_id.
3361: --
3362: select ff_user_entities_s.currval
3363: into l_user_entity_id
3364: from dual;
3365: --
3366: -- insert the element type id for the where clause filler
3567: IF l_record_inserted THEN
3568: --
3569: -- Fetch the user_entity_id.
3570: --
3571: select ff_user_entities_s.currval
3572: into l_user_entity_id
3573: from dual;
3574: --
3575: -- insert the element type id for the where clause filler
3805: BEGIN
3806:
3807: -- delete_compiled_formula_priv(p_input_value_id, 'I', '%', null);
3808:
3809: DELETE FROM ff_user_entities
3810: WHERE creator_id = p_input_value_id
3811: AND creator_type = 'I';
3812: END delete_input_value_dict;
3813: --
3866: l_route2_name varchar2(50) := 'INPUT_VALUE_USING_PARTIAL_MATCHING' ||p_date_p;
3867: l_entry_route varchar2(50) := 'INPUT_VALUE_ENTRY_LEVEL' || p_date_p;
3868: l_override_entry_route varchar2(50) := 'INPUT_VALUE_ENTRY_LEVEL_OVERRIDE' || p_date_p;
3869: l_input_name pay_input_values_f.name%type;
3870: l_entity_name ff_user_entities.user_entity_name%type;
3871: l_element_type_id pay_element_types_f.element_type_id%type;
3872: l_element_name pay_element_types_f.element_name%type;
3873: l_multiple_entries pay_element_types_f.multiple_entries_allowed_flag%type;
3874: l_legislation_code pay_element_types_f.legislation_code%type;
4110: IF l_record_inserted THEN
4111: --
4112: -- Fetch the user_entity_id.
4113: --
4114: select ff_user_entities_s.currval
4115: into l_user_entity_id
4116: from dual;
4117: --
4118: -- insert the input value id for the where clause filler
4217: IF l_record_inserted THEN
4218: --
4219: -- Fetch the user_entity_id.
4220: --
4221: select ff_user_entities_s.currval
4222: into l_user_entity_id
4223: from dual;
4224: --
4225: -- insert the input value id for the where clause filler
4345: IF l_record_inserted THEN
4346: --
4347: -- Fetch the user_entity_id.
4348: --
4349: select ff_user_entities_s.currval
4350: into l_user_entity_id
4351: from dual;
4352: --
4353: -- insert the input value id for the where clause filler
4478: IF l_record_inserted THEN
4479: --
4480: -- Fetch the user_entity_id.
4481: --
4482: select ff_user_entities_s.currval
4483: into l_user_entity_id
4484: from dual;
4485: --
4486: -- insert the input value id for the where clause filler
4619: IF l_record_inserted THEN
4620: --
4621: -- Fetch the user_entity_id.
4622: --
4623: select ff_user_entities_s.currval
4624: into l_user_entity_id
4625: from dual;
4626: --
4627: -- insert the input value id for the where clause filler
4683: IF l_record_inserted THEN
4684: --
4685: -- Fetch the user_entity_id.
4686: --
4687: select ff_user_entities_s.currval
4688: into l_user_entity_id
4689: from dual;
4690: --
4691: -- insert the input value id for the where clause filler
4801: max(effective_end_date)
4802: from pay_element_types_f e
4803: where not exists (
4804: select null
4805: from ff_user_entities u
4806: where u.creator_id = e.element_type_id
4807: and u.creator_type = 'E')
4808: and mod(element_type_id, p_maxworkers) = p_worker_id
4809: group by element_type_id
4814: from pay_input_values_f i
4815: where generate_db_items_flag = 'Y'
4816: and not exists (
4817: select null
4818: from ff_user_entities u
4819: where u.creator_id = i.input_value_id
4820: and u.creator_type = 'I')
4821: and mod(input_value_id, p_maxworkers) = p_worker_id
4822: group by input_value_id
4925: max(effective_end_date)
4926: from pay_element_types_f a
4927: where not exists (
4928: select null
4929: from ff_user_entities u
4930: where u.creator_id = a.element_type_id
4931: and u.creator_type = 'E')
4932: and mod(element_type_id, p_maxworkers) = p_worker_id
4933: and (a.legislation_code = p_leg_code
4943: from pay_input_values_f a
4944: where generate_db_items_flag = 'Y'
4945: and not exists (
4946: select null
4947: from ff_user_entities u
4948: where u.creator_id = a.input_value_id
4949: and u.creator_type = 'I')
4950: and mod(input_value_id, p_maxworkers) = p_worker_id
4951: and (a.legislation_code = p_leg_code
5055: procedure delete_element_types(p_worker_id in number default 0,
5056: p_maxworkers in number default 1) is
5057: begin
5058: --
5059: delete ff_user_entities u
5060: where u.creator_type = 'I'
5061: and exists (
5062: select null
5063: from pay_input_values_f a
5062: select null
5063: from pay_input_values_f a
5064: where u.creator_id = a.input_value_id);
5065:
5066: delete ff_user_entities u
5067: where u.creator_type = 'E'
5068: and exists (
5069: select null
5070: from pay_element_types_f x
5092: begin
5093: --
5094: -- delete the input values
5095: --
5096: delete ff_user_entities u
5097: where u.creator_type = 'I'
5098: and exists (
5099: select null
5100: from pay_input_values_f a
5105: from per_business_groups_perf b
5106: where a.business_group_id = b.business_group_id
5107: and nvl(b.legislation_code, p_leg_code) = p_leg_code)));
5108:
5109: delete ff_user_entities u
5110: where u.creator_type = 'E'
5111: and exists (
5112: select null
5113: from pay_element_types_f x
5496: from ff_database_items dbi
5497: where fdi.item_name = dbi.user_name
5498: and exists
5499: (select null
5500: from ff_user_entities ent
5501: where ent.user_entity_id = dbi.user_entity_id
5502: and ent.creator_type in ('B','RB','E','I')
5503: -- MERGE B RB E I main cursors
5504: --
5528: from pay_dimension_routes pdr,
5529: pay_defined_balances b
5530: where pdr.balance_dimension_id = b.balance_dimension_id
5531: and not exists (select null
5532: from ff_user_entities ue
5533: where ue.creator_id = b.defined_balance_id
5534: and ue.route_id = pdr.route_id
5535: and ue.creator_type = 'RB'))
5536: )
5895: p_rate_id in number
5896: ) is
5897: --
5898: BEGIN
5899: DELETE FROM ff_user_entities
5900: WHERE creator_id = p_rate_id
5901: AND creator_type = 'G';
5902: end delete_grade_spine_dict;
5903: --
6065: */
6066: procedure refresh_grade_spine_rates is
6067: cursor c1 is select rate_id
6068: from pay_rates r
6069: where not exists (select null from ff_user_entities u
6070: where u.creator_id = r.rate_id
6071: and u.creator_type = 'G');
6072: begin
6073: for c1rec in c1 loop
6134: p_title in varchar2,
6135: p_context in varchar2,
6136: p_leg_code in varchar2
6137: ) is
6138: l_entity_name ff_user_entities.user_entity_name%type;
6139: BEGIN
6140: if (p_title = '%') and
6141: (p_context = '%') then -- delete all descriptive flexfield DB items
6142: --
6148: p_leg_code);
6149: --
6150: -- now delete the actual database items
6151: --
6152: delete from ff_user_entities
6153: where creator_type = 'DF'
6154: and nvl (legislation_code, ' ') = nvl (p_leg_code, ' ');
6155: --
6156: else -- delete selected DB items
6169: p_leg_code);
6170: --
6171: -- now delete the actual database items
6172: --
6173: delete from ff_user_entities
6174: where creator_type = 'DF'
6175: and user_entity_name like l_entity_name
6176: and nvl (legislation_code, ' ') = nvl (p_leg_code, ' ');
6177: end if;
6265: p_business_group_id in varchar2
6266: ) is
6267: l_param_value ff_route_parameter_values.value%type;
6268: l_dbitem_found boolean;
6269: l_entity_name ff_user_entities.user_entity_name%type;
6270: l_created_by number;
6271: l_last_login number;
6272: l_record_inserted boolean;
6273: l_user_entity_id number;
6375: end if;
6376: ELSE
6377: select user_entity_id
6378: into l_user_entity_id
6379: from ff_user_entities
6380: where user_entity_name=l_entity_name || '_DF'
6381: and nvl(legislation_code,'X')=nvl(p_leg_code,'X')
6382: and nvl(business_group_id,-1)=nvl(p_business_group_id,-1);
6383: END IF;
6552: could be several database items.
6553: --
6554: NOTES
6555: Since there is no desciptive flex id to identify an individual row in
6556: the ff_user_entities table, the title of the descriptive flex is used
6557: instead, holding it in the user_entity_name column.
6558: --
6559: It is intended that this flexfield creation procedure be run from the
6560: Standard Report Submission (SRS) form.
6674: context passed for these 3 flexfields is the appropriate
6675: information type.
6676: NOTES
6677: Since there is no desciptive flex id to identify an individual row in
6678: the ff_user_entities table, the title of the descriptive flex is used
6679: instead, holding it in the user_entity_name column.
6680: --
6681: It is intended that this flexfield creation procedure be run from the
6682: Standard Report Submission (SRS) form.
7016: 'fnd_descr_flex_column_usages'. For a given descriptive flexfield there
7017: could be several database items.
7018: NOTES
7019: Since there is no desciptive flex id to identify an individual row in
7020: the ff_user_entities table, the title of the descriptive flex is used
7021: instead, holding it in the user_entity_name column.
7022:
7023: Added rtrim to title select below, due to Reserved word problem, ie
7024: the title, in creating descriptive flex cannot contain full stops,
7159: p_absence_type_id in number
7160: ) is
7161: --
7162: BEGIN
7163: DELETE FROM ff_user_entities
7164: WHERE creator_id = p_absence_type_id
7165: AND creator_type = 'A';
7166: end delete_absence_dict;
7167: --
7244: p_entity_name in varchar2,
7245: p_leg_code in varchar2,
7246: p_business_group_id in number
7247: ) is
7248: l_entity_name ff_user_entities.user_entity_name%type;
7249: BEGIN
7250: l_entity_name := p_entity_name || '%';
7251: --
7252: -- delete any complied formula references
7257: p_leg_code);
7258: --
7259: -- now delete the actual database items
7260: --
7261: delete from ff_user_entities
7262: where creator_type = 'KF'
7263: and creator_id = p_creator_id
7264: and user_entity_name like l_entity_name
7265: and ( nvl (legislation_code, ' ') = nvl (p_leg_code, ' ') -- 6955080
7764: --
7765: -- delete user entities (and dbitems) owned by a user entity
7766: -- for an old value of the S leg rule
7767: --
7768: delete from ff_user_entities
7769: where creator_type = 'KF'
7770: and creator_id <> p_id_flex_num
7771: and user_entity_name like 'SCL%'
7772: and nvl (legislation_code, ' ') = nvl (l_legislation_code, ' ')
7873: statem varchar2(256);
7874: sql_cur number;
7875: ignore number;
7876: begin
7877: statem := 'alter trigger ff_user_entities_bri disable';
7878: sql_cur := dbms_sql.open_cursor;
7879: dbms_sql.parse(sql_cur,
7880: statem,
7881: dbms_sql.v7);
7901: statem varchar2(256);
7902: sql_cur number;
7903: ignore number;
7904: begin
7905: statem := 'alter trigger ff_user_entities_bri enable';
7906: sql_cur := dbms_sql.open_cursor;
7907: dbms_sql.parse(sql_cur,
7908: statem,
7909: dbms_sql.v7);
7941: commit;
7942: --
7943: disable_ffue_cascade_trig;
7944: --
7945: delete from ff_user_entities
7946: where creator_type in ('B', 'RB');
7947: --
7948: enable_ffue_cascade_trig;
7949: disable_refbal_trig;
8009: from dual
8010: where exists
8011: (
8012: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8013: INDEX(b FF_USER_ENTITIES_PK) */ null
8014: from ff_database_items a,
8015: ff_user_entities b
8016: where a.user_name = p_user_name
8017: and a.user_entity_id = b.user_entity_id
8011: (
8012: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8013: INDEX(b FF_USER_ENTITIES_PK) */ null
8014: from ff_database_items a,
8015: ff_user_entities b
8016: where a.user_name = p_user_name
8017: and a.user_entity_id = b.user_entity_id
8018: and
8019: (
8057: )
8058: or exists
8059: (
8060: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8061: INDEX(b FF_USER_ENTITIES_PK) */ null
8062: from ff_database_items_tl a,
8063: ff_user_entities b
8064: where a.translated_user_name = p_user_name
8065: and (a.user_name <> p_user_name or a.user_entity_id <> p_ue_id)
8059: (
8060: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8061: INDEX(b FF_USER_ENTITIES_PK) */ null
8062: from ff_database_items_tl a,
8063: ff_user_entities b
8064: where a.translated_user_name = p_user_name
8065: and (a.user_name <> p_user_name or a.user_entity_id <> p_ue_id)
8066: and a.user_entity_id = b.user_entity_id
8067: and
8651: ,p_user_name in varchar2
8652: ) is
8653: select u.user_entity_id
8654: from ff_database_items d
8655: , ff_user_entities u
8656: where d.user_name = p_user_name
8657: and u.user_entity_id = d.user_entity_id
8658: and u.creator_id = p_creator_id
8659: and u.creator_type = p_creator_type
8715: from dual
8716: where exists
8717: (
8718: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8719: INDEX(b FF_USER_ENTITIES_PK) */ null
8720: from ff_database_items a,
8721: ff_user_entities b
8722: where a.user_name = p_tl_user_name
8723: and (p_user_name <> p_tl_user_name or a.user_entity_id <> p_user_entity_id)
8717: (
8718: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8719: INDEX(b FF_USER_ENTITIES_PK) */ null
8720: from ff_database_items a,
8721: ff_user_entities b
8722: where a.user_name = p_tl_user_name
8723: and (p_user_name <> p_tl_user_name or a.user_entity_id <> p_user_entity_id)
8724: and a.user_entity_id = b.user_entity_id
8725: and
8764: )
8765: or exists
8766: (
8767: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8768: INDEX(b FF_USER_ENTITIES_PK) */ null
8769: from ff_database_items_tl a,
8770: ff_user_entities b
8771: where a.translated_user_name = p_tl_user_name
8772: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
8766: (
8767: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8768: INDEX(b FF_USER_ENTITIES_PK) */ null
8769: from ff_database_items_tl a,
8770: ff_user_entities b
8771: where a.translated_user_name = p_tl_user_name
8772: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
8773: and a.user_entity_id = b.user_entity_id
8774: and
8851: l_count number;
8852: begin
8853: select count(*)
8854: into l_count
8855: from ff_user_entities ue
8856: where ue.creator_id = p_creator_id
8857: and ue.creator_type = p_creator_type
8858: and ue.user_entity_name like '%_DP'
8859: ;
8869: l_count number;
8870: begin
8871: select count(*)
8872: into l_count
8873: from ff_user_entities ue
8874: where ue.creator_id = p_creator_id
8875: and ue.creator_type = p_creator_type
8876: and ue.user_entity_name not like '%_DP'
8877: ;