[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 | |
124: 'Further Personal Payment Method Info' DFF.
125: priupadh 115.149 15-May-2009 Bug 8512762 Added commit in process_pay_dyndbi_changes.
126: ckesanap 115.148 25-Apr-2008 Bug 6955080. In delete_keyflex_dict, chaged the
127: where condition clause for deleting the
128: existing user_entity from ff_user_entities.
129: ckesanap 115.147 07-Aug-2007 Bug 5930272. Added fnd_message.retrieve() in
130: insert_user_entity_main() to clear the message
131: in the FND message queue as the error raised
132: in checkformat() is being handled.
158: divicker 115.140 01-JUN-2006 11511 branch merge
159: arashid 115.139 11-MAY-2006 Add code for running legislation-specific
160: database item translation from
161: process_pay_dyndbi_changes.
162: arashid 115.138 09-MAY-2006 Don't disable ff_user_entities delete
163: triggers as thay are required to delete
164: ff_database_items_tl rows (other child
165: rows are cascade deleted).
166: arashid 115.137 05-MAY-2006 Use substr to restrict names to 80
509: flex creation.
510: amills 09-DEC-1997 changed l_title to select from fnd_descriptive_
511: flexs_vl rather than take a hardcoded value
512: in create_org_pay_flex_dict
513: alogue 28-OCT-1997 legislation_code used in delete from ff_user_entities
514: in delete_keyflex_dict. Fix to bug 513364.
515: mreid 24-SEP-1997 Changed table_names for release 11 security.
516: dsaxby 15-SEP-1997 Changed substr to substrb to avoid problems with
517: generating NLS database items.
531: to the payroll model for User Defined Data,
532: thereby being consistent for Payroll and Formula.
533: This means that for User Defined Data only the
534: business group id should be populated on
535: ff_user_entities, not legislation code as well.
536: (previously both columns were populated). Refer
537: to the Notes above for more information. The
538: change dated 15-JUL-1994 is undone.
539: mwcallag 15-JUL-1994 procedure 'new_defined_balance' altered to
865: rgeflg varchar2(1);
866: begin
867: IF (g_triggers_altered) THEN
868: -- validate the name is OK. This was originally called as part of
869: -- the FF_USER_ENTITIES_BRI trigger so we add it here.
870: begin
871: -- Check if name legal format eg no spaces, or special characters
872: hr_chkfmt.checkformat (p_db_item_name, 'DB_ITEM_NAME', p_db_item_name,
873: null,null,'Y',rgeflg,null);
882: hr_utility.raise_error;
883: end;
884: --
885: -- create user entity
886: select ff_user_entities_s.nextval
887: into p_user_entity_id
888: from sys.dual;
889:
890: BEGIN
887: into p_user_entity_id
888: from sys.dual;
889:
890: BEGIN
891: insert into ff_user_entities
892: (user_entity_id,
893: business_group_id,
894: legislation_code,
895: route_id,
910: p_description
911: from dual
912: where not exists (
913: select null
914: from ff_user_entities a
915: where a.user_entity_name = p_db_item_name
916: and
917: ( p_startup_mode = 'MASTER'
918: or
952: raise;
953: END;
954: ELSE -- g_triggers_altered is FALSE so use existing trigger validation
955: --
956: select ff_user_entities_s.nextval
957: into p_user_entity_id
958: from sys.dual;
959: --
960: insert into ff_user_entities
956: select ff_user_entities_s.nextval
957: into p_user_entity_id
958: from sys.dual;
959: --
960: insert into ff_user_entities
961: (user_entity_id,
962: business_group_id,
963: legislation_code,
964: route_id,
1057: pdr.priority
1058: from pay_dimension_routes pdr
1059: where pdr.balance_dimension_id = p_bal_dim
1060: and not exists (select null
1061: from ff_user_entities u
1062: where u.creator_id = p_defined_balance_id
1063: and u.creator_type = 'RB'
1064: and u.route_id = pdr.route_id)
1065: order by pdr.balance_dimension_id, pdr.priority;
1069: p_business_group_id in number,
1070: p_startup_mode in varchar2)
1071: is
1072: select /*+ ORDERED
1073: INDEX(a FF_USER_ENTITIES_N50)
1074: INDEX(fdbi FF_DATABASE_ITEMS_FK1)
1075: INDEX(fdi FF_USER_ENTITIES_N50)
1076: USE_NL(a fdbi fdi) */
1077: formula_id
1071: is
1072: select /*+ ORDERED
1073: INDEX(a FF_USER_ENTITIES_N50)
1074: INDEX(fdbi FF_DATABASE_ITEMS_FK1)
1075: INDEX(fdi FF_USER_ENTITIES_N50)
1076: USE_NL(a fdbi fdi) */
1077: formula_id
1078: from
1079: ff_user_entities a,
1075: INDEX(fdi FF_USER_ENTITIES_N50)
1076: USE_NL(a fdbi fdi) */
1077: formula_id
1078: from
1079: ff_user_entities a,
1080: ff_database_items fdbi,
1081: ff_fdi_usages_f fdi
1082: where fdi.usage = 'D'
1083: and fdi.item_name = fdbi.user_name
1132: delete ff_compiled_info_f where formula_id = r_sel_ffci.formula_id;
1133:
1134: end loop;
1135:
1136: delete ff_user_entities a
1137: where a.user_entity_name = l_db_item_name
1138: and
1139: ( p_startup_mode = 'MASTER'
1140: or
1524: legislation_code,
1525: save_run_balance
1526: from pay_defined_balances b
1527: where not exists (
1528: select null from ff_user_entities u
1529: where b.defined_balance_id = u.creator_id
1530: and u.creator_type = 'B')
1531: and mod(defined_balance_id, p_maxworkers) = p_worker_id
1532: order by b.defined_balance_id;
1540: from pay_defined_balances b
1541: where /* def bal ue simply doesn't exist but has an associated pdr */
1542: ((
1543: not exists (
1544: select null from ff_user_entities u
1545: where b.defined_balance_id = u.creator_id
1546: and u.creator_type = 'RB')
1547: and exists
1548: (select null
1555: select pdr.balance_dimension_id
1556: from pay_dimension_routes pdr
1557: where pdr.balance_dimension_id = b.balance_dimension_id
1558: and not exists (select null
1559: from ff_user_entities ue
1560: where ue.creator_id = b.defined_balance_id
1561: and ue.route_id = pdr.route_id
1562: and ue.creator_type = 'RB'))
1563: ))
1648:
1649: -- delete_compiled_formula_priv(c2rec.defined_balance_id , 'RB', '%',
1650: -- c2rec.legislation_code);
1651:
1652: delete ff_user_entities
1653: where creator_id = c2rec.defined_balance_id
1654: and creator_type = 'RB';
1655:
1656: create_alternative_dbis(c2rec.defined_balance_id,
1695: legislation_code,
1696: save_run_balance
1697: from pay_defined_balances a
1698: where not exists (
1699: select null from ff_user_entities u
1700: where a.defined_balance_id = u.creator_id
1701: and u.creator_type = 'B')
1702: and mod(defined_balance_id, p_maxworkers) = p_worker_id
1703: and (a.legislation_code = p_leg_code
1716: from pay_defined_balances a
1717: where /* def bal ue simply doesn't exist but has an associated pdr */
1718: ((
1719: not exists (
1720: select null from ff_user_entities u
1721: where a.defined_balance_id = u.creator_id
1722: and u.creator_type = 'RB')
1723: and exists
1724: (select null
1731: select pdr.balance_dimension_id
1732: from pay_dimension_routes pdr
1733: where pdr.balance_dimension_id = a.balance_dimension_id
1734: and not exists (select null
1735: from ff_user_entities ue
1736: where ue.creator_id = a.defined_balance_id
1737: and ue.route_id = pdr.route_id
1738: and ue.creator_type = 'RB'))
1739: ))
1830:
1831: -- delete_compiled_formula_priv(c2rec.defined_balance_id , 'RB', '%',
1832: -- c2rec.legislation_code);
1833:
1834: delete ff_user_entities
1835: where creator_id = c2rec.defined_balance_id
1836: and creator_type = 'RB';
1837:
1838: create_alternative_dbis(c2rec.defined_balance_id,
1875: --
1876: -- delete_compiled_formula_priv(p_defined_balance_id, 'B', '%', p_legislation_code);
1877: -- delete_compiled_formula_priv(p_defined_balance_id, 'RB', '%', p_legislation_code);
1878:
1879: delete from ff_user_entities
1880: where creator_id = p_defined_balance_id
1881: and creator_type = 'B';
1882: --
1883: delete from ff_user_entities
1879: delete from ff_user_entities
1880: where creator_id = p_defined_balance_id
1881: and creator_type = 'B';
1882: --
1883: delete from ff_user_entities
1884: where creator_id = p_defined_balance_id
1885: and creator_type = 'RB';
1886: --
1887: hrdyndbi.new_defined_balance
1919: ) is
1920: l_route_parameter_id number;
1921: l_route_id number;
1922: l_user_entity_id number;
1923: l_created_by ff_user_entities.created_by%type;
1924: l_last_login ff_user_entities.last_update_login%type;
1925: l_user_entity_name ff_user_entities.user_entity_name%type;
1926: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1927: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1920: l_route_parameter_id number;
1921: l_route_id number;
1922: l_user_entity_id number;
1923: l_created_by ff_user_entities.created_by%type;
1924: l_last_login ff_user_entities.last_update_login%type;
1925: l_user_entity_name ff_user_entities.user_entity_name%type;
1926: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1927: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1928: l_route_name ff_routes.route_name%type;
1921: l_route_id number;
1922: l_user_entity_id number;
1923: l_created_by ff_user_entities.created_by%type;
1924: l_last_login ff_user_entities.last_update_login%type;
1925: l_user_entity_name ff_user_entities.user_entity_name%type;
1926: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1927: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1928: l_route_name ff_routes.route_name%type;
1929: l_route_cd varchar2(60);
1922: l_user_entity_id number;
1923: l_created_by ff_user_entities.created_by%type;
1924: l_last_login ff_user_entities.last_update_login%type;
1925: l_user_entity_name ff_user_entities.user_entity_name%type;
1926: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1927: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1928: l_route_name ff_routes.route_name%type;
1929: l_route_cd varchar2(60);
1930: l_route_lud varchar2(60);
1923: l_created_by ff_user_entities.created_by%type;
1924: l_last_login ff_user_entities.last_update_login%type;
1925: l_user_entity_name ff_user_entities.user_entity_name%type;
1926: l_ent_bg_id ff_user_entities.BUSINESS_GROUP_ID%type;
1927: l_ent_lc ff_user_entities.LEGISLATION_CODE%type;
1928: l_route_name ff_routes.route_name%type;
1929: l_route_cd varchar2(60);
1930: l_route_lud varchar2(60);
1931: l_route_lub number;
1932: BEGIN
1933: --
1934: -- get the user entity id to be used:
1935: --
1936: select ff_user_entities_s.currval
1937: into l_user_entity_id
1938: from dual;
1939: --
1940: -- get the relevant information for the user entity we are using
1947: into l_route_parameter_id,
1948: l_created_by,
1949: l_last_login,
1950: l_user_entity_name
1951: from ff_user_entities ENTITY
1952: , ff_route_parameters RPARAM
1953: where ENTITY.user_entity_id = l_user_entity_id
1954: and RPARAM.route_id = ENTITY.route_id
1955: and RPARAM.sequence_no = p_sequence_no;
1968: into l_user_entity_name,
1969: l_route_id,
1970: l_ent_bg_id,
1971: l_ent_lc
1972: from ff_user_entities ENTITY
1973: where ENTITY.user_entity_id = l_user_entity_id;
1974:
1975: hr_utility.trace('entity_name: ' || l_user_entity_name);
1976: hr_utility.trace('entity route id: ' || to_char(l_route_id));
2062: ) is
2063: l_item_name ff_database_items.user_name%type;
2064: l_user_name ff_database_items.user_name%type;
2065: l_user_entity_id number;
2066: l_created_by ff_user_entities.created_by%type;
2067: l_last_login ff_user_entities.last_update_login%type;
2068: l_db number;
2069: l_exists VARCHAR2(2);
2070: l_clash boolean;
2063: l_item_name ff_database_items.user_name%type;
2064: l_user_name ff_database_items.user_name%type;
2065: l_user_entity_id number;
2066: l_created_by ff_user_entities.created_by%type;
2067: l_last_login ff_user_entities.last_update_login%type;
2068: l_db number;
2069: l_exists VARCHAR2(2);
2070: l_clash boolean;
2071: --
2093: --
2094: if (p_user_entity_id is not NULL) then
2095: l_user_entity_id:=p_user_entity_id;
2096: else
2097: select ff_user_entities_s.currval
2098: into l_user_entity_id
2099: from dual;
2100: end if;
2101:
2109: into l_created_by,
2110: l_last_login,
2111: bg_id,
2112: leg_code
2113: from ff_user_entities
2114: where user_entity_id = l_user_entity_id;
2115: --
2116: IF (g_triggers_altered) THEN
2117: -- Get the startup mode
2453: p_created_by in number,
2454: p_last_login in number,
2455: p_record_inserted out nocopy boolean
2456: ) is
2457: l_user_entity_name ff_user_entities.user_entity_name%type;
2458: BEGIN
2459: --
2460: -- simply call insert_user_entity_main
2461: --
2536: --
2537: -- get the user entity id from its sequence
2538: --
2539: begin
2540: SELECT ff_user_entities_s.nextval
2541: INTO l_user_entities_seq
2542: FROM dual;
2543: end;
2544: --
2568: --
2569: -- Check the name is OK
2570: --
2571: -- validate the name is OK. This was originally called as part of
2572: -- the FF_USER_ENTITIES_BRI trigger so we add it here.
2573:
2574: begin
2575: -- Check if name legal format eg no spaces, or special characters
2576: hr_chkfmt.checkformat (l_user_entity_name, 'DB_ITEM_NAME', l_user_entity_name,
2594: -- Check if entity already exists before inserting it
2595:
2596: select count(*)
2597: into l_dummy_fetch_var
2598: from ff_user_entities
2599: where user_entity_name = l_user_entity_name
2600: and nvl (legislation_code, ' ') = nvl (p_legislation_code, ' ')
2601: and nvl (business_group_id, -1) = nvl (p_business_group_id, -1);
2602:
2619: hrrbdeib_trace_off;
2620: raise;
2621: END;
2622: --
2623: -- populate the ff_user_entities table :
2624: --
2625: BEGIN
2626:
2627: IF (g_triggers_altered) THEN
2624: --
2625: BEGIN
2626:
2627: IF (g_triggers_altered) THEN
2628: insert into ff_user_entities (
2629: user_entity_id,
2630: business_group_id,
2631: legislation_code,
2632: route_id,
2656: sysdate
2657: from dual
2658: where not exists (
2659: select null
2660: from ff_user_entities a
2661: where a.user_entity_name = l_user_entity_name
2662: and
2663: ( startup_mode = 'MASTER'
2664: or
2687: )
2688: )
2689: ));
2690: ELSE
2691: insert into ff_user_entities (
2692: user_entity_id,
2693: business_group_id,
2694: legislation_code,
2695: route_id,
2720: END IF;
2721: p_record_inserted := TRUE;
2722: EXCEPTION WHEN OTHERS THEN
2723: hrrbdeib_trace_on;
2724: hr_utility.trace('insert ff_user_entities: ue: ' || l_user_entity_name);
2725: hr_utility.trace('insert ff_user_entities: route_name: ' || p_route_name);
2726: hrrbdeib_trace_off;
2727: raise;
2728: END;
2721: p_record_inserted := TRUE;
2722: EXCEPTION WHEN OTHERS THEN
2723: hrrbdeib_trace_on;
2724: hr_utility.trace('insert ff_user_entities: ue: ' || l_user_entity_name);
2725: hr_utility.trace('insert ff_user_entities: route_name: ' || p_route_name);
2726: hrrbdeib_trace_off;
2727: raise;
2728: END;
2729:
2769: select /* INDEX(fdi FF_FDI_USAGES_F_N50)*/
2770: distinct fdi.formula_id,
2771: fdi.effective_start_date
2772: from
2773: ff_user_entities ent,
2774: ff_database_items dbi,
2775: ff_fdi_usages_f fdi
2776: where fdi.item_name = dbi.user_name
2777: and fdi.usage = 'D'
2857: and nvl(b.legislation_code, p_leg_code) = p_leg_code)))
2858: and exists (select null from
2859: ff_database_items dbi
2860: where fdi.item_name = dbi.user_name
2861: and exists (select /*+ INDEX(ent FF_USER_ENTITIES_N51)*/ null from
2862: ff_user_entities ent
2863: where ent.user_entity_id = dbi.user_entity_id
2864: and ent.creator_id = p_creator_id
2865: and ent.creator_type = p_creator_type
2858: and exists (select null from
2859: ff_database_items dbi
2860: where fdi.item_name = dbi.user_name
2861: and exists (select /*+ INDEX(ent FF_USER_ENTITIES_N51)*/ null from
2862: ff_user_entities ent
2863: where ent.user_entity_id = dbi.user_entity_id
2864: and ent.creator_id = p_creator_id
2865: and ent.creator_type = p_creator_type
2866: and ent.user_entity_name like p_user_entity_name
2952: BEGIN
2953:
2954: -- delete_compiled_formula_priv(p_element_type_id, 'E', '%', null);
2955:
2956: DELETE FROM ff_user_entities
2957: WHERE creator_id = p_element_type_id
2958: AND creator_type = 'E';
2959: END delete_element_type_dict;
2960: --
3127: IF l_record_inserted THEN
3128: --
3129: -- Fetch the user_entity_id.
3130: --
3131: select ff_user_entities_s.currval
3132: into l_user_entity_id
3133: from dual;
3134: --
3135: -- insert the element type id for the where clause filler
3361: IF l_record_inserted THEN
3362: --
3363: -- Fetch the user_entity_id.
3364: --
3365: select ff_user_entities_s.currval
3366: into l_user_entity_id
3367: from dual;
3368: --
3369: -- insert the element type id for the where clause filler
3570: IF l_record_inserted THEN
3571: --
3572: -- Fetch the user_entity_id.
3573: --
3574: select ff_user_entities_s.currval
3575: into l_user_entity_id
3576: from dual;
3577: --
3578: -- insert the element type id for the where clause filler
3808: BEGIN
3809:
3810: -- delete_compiled_formula_priv(p_input_value_id, 'I', '%', null);
3811:
3812: DELETE FROM ff_user_entities
3813: WHERE creator_id = p_input_value_id
3814: AND creator_type = 'I';
3815: END delete_input_value_dict;
3816: --
3869: l_route2_name varchar2(50) := 'INPUT_VALUE_USING_PARTIAL_MATCHING' ||p_date_p;
3870: l_entry_route varchar2(50) := 'INPUT_VALUE_ENTRY_LEVEL' || p_date_p;
3871: l_override_entry_route varchar2(50) := 'INPUT_VALUE_ENTRY_LEVEL_OVERRIDE' || p_date_p;
3872: l_input_name pay_input_values_f.name%type;
3873: l_entity_name ff_user_entities.user_entity_name%type;
3874: l_element_type_id pay_element_types_f.element_type_id%type;
3875: l_element_name pay_element_types_f.element_name%type;
3876: l_multiple_entries pay_element_types_f.multiple_entries_allowed_flag%type;
3877: l_legislation_code pay_element_types_f.legislation_code%type;
4113: IF l_record_inserted THEN
4114: --
4115: -- Fetch the user_entity_id.
4116: --
4117: select ff_user_entities_s.currval
4118: into l_user_entity_id
4119: from dual;
4120: --
4121: -- insert the input value id for the where clause filler
4220: IF l_record_inserted THEN
4221: --
4222: -- Fetch the user_entity_id.
4223: --
4224: select ff_user_entities_s.currval
4225: into l_user_entity_id
4226: from dual;
4227: --
4228: -- insert the input value id for the where clause filler
4348: IF l_record_inserted THEN
4349: --
4350: -- Fetch the user_entity_id.
4351: --
4352: select ff_user_entities_s.currval
4353: into l_user_entity_id
4354: from dual;
4355: --
4356: -- insert the input value id for the where clause filler
4481: IF l_record_inserted THEN
4482: --
4483: -- Fetch the user_entity_id.
4484: --
4485: select ff_user_entities_s.currval
4486: into l_user_entity_id
4487: from dual;
4488: --
4489: -- insert the input value id for the where clause filler
4622: IF l_record_inserted THEN
4623: --
4624: -- Fetch the user_entity_id.
4625: --
4626: select ff_user_entities_s.currval
4627: into l_user_entity_id
4628: from dual;
4629: --
4630: -- insert the input value id for the where clause filler
4686: IF l_record_inserted THEN
4687: --
4688: -- Fetch the user_entity_id.
4689: --
4690: select ff_user_entities_s.currval
4691: into l_user_entity_id
4692: from dual;
4693: --
4694: -- insert the input value id for the where clause filler
4804: max(effective_end_date)
4805: from pay_element_types_f e
4806: where not exists (
4807: select null
4808: from ff_user_entities u
4809: where u.creator_id = e.element_type_id
4810: and u.creator_type = 'E')
4811: and mod(element_type_id, p_maxworkers) = p_worker_id
4812: group by element_type_id
4817: from pay_input_values_f i
4818: where generate_db_items_flag = 'Y'
4819: and not exists (
4820: select null
4821: from ff_user_entities u
4822: where u.creator_id = i.input_value_id
4823: and u.creator_type = 'I')
4824: and mod(input_value_id, p_maxworkers) = p_worker_id
4825: group by input_value_id
4928: max(effective_end_date)
4929: from pay_element_types_f a
4930: where not exists (
4931: select null
4932: from ff_user_entities u
4933: where u.creator_id = a.element_type_id
4934: and u.creator_type = 'E')
4935: and mod(element_type_id, p_maxworkers) = p_worker_id
4936: and (a.legislation_code = p_leg_code
4946: from pay_input_values_f a
4947: where generate_db_items_flag = 'Y'
4948: and not exists (
4949: select null
4950: from ff_user_entities u
4951: where u.creator_id = a.input_value_id
4952: and u.creator_type = 'I')
4953: and mod(input_value_id, p_maxworkers) = p_worker_id
4954: and (a.legislation_code = p_leg_code
5058: procedure delete_element_types(p_worker_id in number default 0,
5059: p_maxworkers in number default 1) is
5060: begin
5061: --
5062: delete ff_user_entities u
5063: where u.creator_type = 'I'
5064: and exists (
5065: select null
5066: from pay_input_values_f a
5065: select null
5066: from pay_input_values_f a
5067: where u.creator_id = a.input_value_id);
5068:
5069: delete ff_user_entities u
5070: where u.creator_type = 'E'
5071: and exists (
5072: select null
5073: from pay_element_types_f x
5095: begin
5096: --
5097: -- delete the input values
5098: --
5099: delete ff_user_entities u
5100: where u.creator_type = 'I'
5101: and exists (
5102: select null
5103: from pay_input_values_f a
5108: from per_business_groups_perf b
5109: where a.business_group_id = b.business_group_id
5110: and nvl(b.legislation_code, p_leg_code) = p_leg_code)));
5111:
5112: delete ff_user_entities u
5113: where u.creator_type = 'E'
5114: and exists (
5115: select null
5116: from pay_element_types_f x
5499: from ff_database_items dbi
5500: where fdi.item_name = dbi.user_name
5501: and exists
5502: (select null
5503: from ff_user_entities ent
5504: where ent.user_entity_id = dbi.user_entity_id
5505: and ent.creator_type in ('B','RB','E','I')
5506: -- MERGE B RB E I main cursors
5507: --
5531: from pay_dimension_routes pdr,
5532: pay_defined_balances b
5533: where pdr.balance_dimension_id = b.balance_dimension_id
5534: and not exists (select null
5535: from ff_user_entities ue
5536: where ue.creator_id = b.defined_balance_id
5537: and ue.route_id = pdr.route_id
5538: and ue.creator_type = 'RB'))
5539: )
5898: p_rate_id in number
5899: ) is
5900: --
5901: BEGIN
5902: DELETE FROM ff_user_entities
5903: WHERE creator_id = p_rate_id
5904: AND creator_type = 'G';
5905: end delete_grade_spine_dict;
5906: --
6068: */
6069: procedure refresh_grade_spine_rates is
6070: cursor c1 is select rate_id
6071: from pay_rates r
6072: where not exists (select null from ff_user_entities u
6073: where u.creator_id = r.rate_id
6074: and u.creator_type = 'G');
6075: begin
6076: for c1rec in c1 loop
6137: p_title in varchar2,
6138: p_context in varchar2,
6139: p_leg_code in varchar2
6140: ) is
6141: l_entity_name ff_user_entities.user_entity_name%type;
6142: BEGIN
6143: if (p_title = '%') and
6144: (p_context = '%') then -- delete all descriptive flexfield DB items
6145: --
6151: p_leg_code);
6152: --
6153: -- now delete the actual database items
6154: --
6155: delete from ff_user_entities
6156: where creator_type = 'DF'
6157: and nvl (legislation_code, ' ') = nvl (p_leg_code, ' ');
6158: --
6159: else -- delete selected DB items
6172: p_leg_code);
6173: --
6174: -- now delete the actual database items
6175: --
6176: delete from ff_user_entities
6177: where creator_type = 'DF'
6178: and user_entity_name like l_entity_name
6179: and nvl (legislation_code, ' ') = nvl (p_leg_code, ' ');
6180: end if;
6268: p_business_group_id in varchar2
6269: ) is
6270: l_param_value ff_route_parameter_values.value%type;
6271: l_dbitem_found boolean;
6272: l_entity_name ff_user_entities.user_entity_name%type;
6273: l_created_by number;
6274: l_last_login number;
6275: l_record_inserted boolean;
6276: l_user_entity_id number;
6385: end if;
6386: ELSE
6387: select user_entity_id
6388: into l_user_entity_id
6389: from ff_user_entities
6390: where user_entity_name=l_entity_name || '_DF'
6391: and nvl(legislation_code,'X')=nvl(p_leg_code,'X')
6392: and nvl(business_group_id,-1)=nvl(p_business_group_id,-1);
6393: END IF;
6562: could be several database items.
6563: --
6564: NOTES
6565: Since there is no desciptive flex id to identify an individual row in
6566: the ff_user_entities table, the title of the descriptive flex is used
6567: instead, holding it in the user_entity_name column.
6568: --
6569: It is intended that this flexfield creation procedure be run from the
6570: Standard Report Submission (SRS) form.
6684: context passed for these 3 flexfields is the appropriate
6685: information type.
6686: NOTES
6687: Since there is no desciptive flex id to identify an individual row in
6688: the ff_user_entities table, the title of the descriptive flex is used
6689: instead, holding it in the user_entity_name column.
6690: --
6691: It is intended that this flexfield creation procedure be run from the
6692: Standard Report Submission (SRS) form.
7026: 'fnd_descr_flex_column_usages'. For a given descriptive flexfield there
7027: could be several database items.
7028: NOTES
7029: Since there is no desciptive flex id to identify an individual row in
7030: the ff_user_entities table, the title of the descriptive flex is used
7031: instead, holding it in the user_entity_name column.
7032:
7033: Added rtrim to title select below, due to Reserved word problem, ie
7034: the title, in creating descriptive flex cannot contain full stops,
7094: could be several database items.
7095: DB Item Format :
7096: NOTES
7097: Since there is no desciptive flex id to identify an individual row in
7098: the ff_user_entities table, the title of the descriptive flex is used
7099: instead, holding it in the user_entity_name column.
7100:
7101: Added rtrim to title select below, due to Reserved word problem, ie
7102: the title, in creating descriptive flex cannot contain full stops,
7236: p_absence_type_id in number
7237: ) is
7238: --
7239: BEGIN
7240: DELETE FROM ff_user_entities
7241: WHERE creator_id = p_absence_type_id
7242: AND creator_type = 'A';
7243: end delete_absence_dict;
7244: --
7321: p_entity_name in varchar2,
7322: p_leg_code in varchar2,
7323: p_business_group_id in number
7324: ) is
7325: l_entity_name ff_user_entities.user_entity_name%type;
7326: BEGIN
7327: l_entity_name := p_entity_name || '%';
7328: --
7329: -- delete any complied formula references
7334: p_leg_code);
7335: --
7336: -- now delete the actual database items
7337: --
7338: delete from ff_user_entities
7339: where creator_type = 'KF'
7340: and creator_id = p_creator_id
7341: and user_entity_name like l_entity_name
7342: and ( nvl (legislation_code, ' ') = nvl (p_leg_code, ' ') -- 6955080
7841: --
7842: -- delete user entities (and dbitems) owned by a user entity
7843: -- for an old value of the S leg rule
7844: --
7845: delete from ff_user_entities
7846: where creator_type = 'KF'
7847: and creator_id <> p_id_flex_num
7848: and user_entity_name like 'SCL%'
7849: and nvl (legislation_code, ' ') = nvl (l_legislation_code, ' ')
7950: statem varchar2(256);
7951: sql_cur number;
7952: ignore number;
7953: begin
7954: statem := 'alter trigger ff_user_entities_bri disable';
7955: sql_cur := dbms_sql.open_cursor;
7956: dbms_sql.parse(sql_cur,
7957: statem,
7958: dbms_sql.v7);
7978: statem varchar2(256);
7979: sql_cur number;
7980: ignore number;
7981: begin
7982: statem := 'alter trigger ff_user_entities_bri enable';
7983: sql_cur := dbms_sql.open_cursor;
7984: dbms_sql.parse(sql_cur,
7985: statem,
7986: dbms_sql.v7);
8018: commit;
8019: --
8020: disable_ffue_cascade_trig;
8021: --
8022: delete from ff_user_entities
8023: where creator_type in ('B', 'RB');
8024: --
8025: enable_ffue_cascade_trig;
8026: disable_refbal_trig;
8086: from dual
8087: where exists
8088: (
8089: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8090: INDEX(b FF_USER_ENTITIES_PK) */ null
8091: from ff_database_items a,
8092: ff_user_entities b
8093: where a.user_name = p_user_name
8094: and a.user_entity_id = b.user_entity_id
8088: (
8089: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8090: INDEX(b FF_USER_ENTITIES_PK) */ null
8091: from ff_database_items a,
8092: ff_user_entities b
8093: where a.user_name = p_user_name
8094: and a.user_entity_id = b.user_entity_id
8095: and
8096: (
8134: )
8135: or exists
8136: (
8137: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8138: INDEX(b FF_USER_ENTITIES_PK) */ null
8139: from ff_database_items_tl a,
8140: ff_user_entities b
8141: where a.translated_user_name = p_user_name
8142: and (a.user_name <> p_user_name or a.user_entity_id <> p_ue_id)
8136: (
8137: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8138: INDEX(b FF_USER_ENTITIES_PK) */ null
8139: from ff_database_items_tl a,
8140: ff_user_entities b
8141: where a.translated_user_name = p_user_name
8142: and (a.user_name <> p_user_name or a.user_entity_id <> p_ue_id)
8143: and a.user_entity_id = b.user_entity_id
8144: and
8728: ,p_user_name in varchar2
8729: ) is
8730: select u.user_entity_id
8731: from ff_database_items d
8732: , ff_user_entities u
8733: where d.user_name = p_user_name
8734: and u.user_entity_id = d.user_entity_id
8735: and u.creator_id = p_creator_id
8736: and u.creator_type = p_creator_type
8792: from dual
8793: where exists
8794: (
8795: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8796: INDEX(b FF_USER_ENTITIES_PK) */ null
8797: from ff_database_items a,
8798: ff_user_entities b
8799: where a.user_name = p_tl_user_name
8800: and (p_user_name <> p_tl_user_name or a.user_entity_id <> p_user_entity_id)
8794: (
8795: select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
8796: INDEX(b FF_USER_ENTITIES_PK) */ null
8797: from ff_database_items a,
8798: ff_user_entities b
8799: where a.user_name = p_tl_user_name
8800: and (p_user_name <> p_tl_user_name or a.user_entity_id <> p_user_entity_id)
8801: and a.user_entity_id = b.user_entity_id
8802: and
8841: )
8842: or exists
8843: (
8844: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8845: INDEX(b FF_USER_ENTITIES_PK) */ null
8846: from ff_database_items_tl a,
8847: ff_user_entities b
8848: where a.translated_user_name = p_tl_user_name
8849: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
8843: (
8844: select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
8845: INDEX(b FF_USER_ENTITIES_PK) */ null
8846: from ff_database_items_tl a,
8847: ff_user_entities b
8848: where a.translated_user_name = p_tl_user_name
8849: and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
8850: and a.user_entity_id = b.user_entity_id
8851: and
8928: l_count number;
8929: begin
8930: select count(*)
8931: into l_count
8932: from ff_user_entities ue
8933: where ue.creator_id = p_creator_id
8934: and ue.creator_type = p_creator_type
8935: and ue.user_entity_name like '%_DP'
8936: ;
8946: l_count number;
8947: begin
8948: select count(*)
8949: into l_count
8950: from ff_user_entities ue
8951: where ue.creator_id = p_creator_id
8952: and ue.creator_type = p_creator_type
8953: and ue.user_entity_name not like '%_DP'
8954: ;