[Home] [Help]
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
164: characters in routines that generate base
165: dbi names - this is to match the main
177: arashid 115.135 27-APR-2006 The following changes were made:
178: - process date-paid and non-date-paid
179: database items when updating _TL names.
180: - process_pay_dyndbi_changes code now calls
181: FF_DATABASE_ITEMS_PKG.UPDATE_SEEDED_TL_ROWS
182: and writes messages to the concurrent
183: processing log file.
184: arashid 115.134 31-MAR-2006 Changed process_pay_dyndbi_changes to not
185: raise assertions when entities are missing.
209: uom_requires_dbis
210: 4. Changed insert_database_item to handle
211: full MLS and pseudo-MLS as separate
212: cases. For full MLS, only a single
213: base DBI in ff_database_items is
214: generated.
215: 5. Changed code for generating element,
216: input, and defined balance DBIs to
217: handle full MLS where it is supported.
215: 5. Changed code for generating element,
216: input, and defined balance DBIs to
217: handle full MLS where it is supported.
218: 6. Added update_xxx calls for MLS updates
219: to FF_DATABASE_ITEMS_TL.
220: Updates to FP.K and earlier should be
221: branched on 115.28.
222: alogue 115.128 07-OCT-2005 Performance fix to sel_ffci in
223: create_alternative_dbis. Bug 4658377.
589: hr_lookups in insert_database_item procedure,
590: also passed parameter of legislation code removed
591: since it is no longer used.
592: abraae 09-SEP-1993 strip blanks from DB Item defn text to fit into
593: ff_database_items.definition_text (char(240))
594: mwcallag 08-SEP-1993 Input value definition text modified to include a
595: decode on the UOM to avoid problem in formula when
596: several DB items of different user definable
597: data types are retrieved in 1 formula cursor.
1005: ,p_startup_mode => p_startup_mode
1006: ,p_clash => l_clash
1007: );
1008: if not l_clash then
1009: insert into ff_database_items
1010: (user_name
1011: ,user_entity_id
1012: ,data_type
1013: ,definition_text
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
1075: from
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
1081: and fdbi.user_entity_id = a.user_entity_id
1108: )
1109: )
1110: );
1111:
1112: l_db_item_name ff_database_items.user_name%type;
1113: usr_ent_id number;
1114: l_dbitem_def_text ff_database_items.definition_text%type;
1115: --
1116: begin
1110: );
1111:
1112: l_db_item_name ff_database_items.user_name%type;
1113: usr_ent_id number;
1114: l_dbitem_def_text ff_database_items.definition_text%type;
1115: --
1116: begin
1117: --
1118: for rrrec in get_alt_routes(p_balance_dimension_id) loop
1268: from fnd_sessions
1269: where session_id = userenv('sessionid');
1270: --
1271: l_route_id ff_routes.route_id%type;
1272: l_db_item_name ff_database_items.user_name%type;
1273: l_dbitem_def_text ff_database_items.definition_text%type;
1274: l_dbi_function pay_balance_dimensions.database_item_function%type;
1275:
1276: startup_mode varchar2(10);
1269: where session_id = userenv('sessionid');
1270: --
1271: l_route_id ff_routes.route_id%type;
1272: l_db_item_name ff_database_items.user_name%type;
1273: l_dbitem_def_text ff_database_items.definition_text%type;
1274: l_dbi_function pay_balance_dimensions.database_item_function%type;
1275:
1276: startup_mode varchar2(10);
1277: rgeflg varchar2(1);
1285:
1286: l_legislation_code varchar2(30);
1287: l_full_mls boolean;
1288: l_found boolean;
1289: l_tl_name ff_database_items_tl.translated_user_name%type;
1290: --
1291: begin
1292: --
1293: IF (g_triggers_altered) THEN
1560: ))
1561: and mod(defined_balance_id, p_maxworkers) = p_worker_id
1562: order by b.defined_balance_id;
1563:
1564: l_db_item_name ff_database_items.user_name%type;
1565: startup_mode varchar2(10);
1566: l_loop_cnt number;
1567:
1568: begin
1741: where a.business_group_id = b.business_group_id
1742: and nvl(b.legislation_code, p_leg_code) = p_leg_code))
1743: order by a.defined_balance_id;
1744:
1745: l_db_item_name ff_database_items.user_name%type;
1746: startup_mode varchar2(10);
1747: l_loop_cnt number;
1748:
1749: begin
2041:
2042: If P_FULL_MLS is true then this call just creates a single base
2043: database item whose name is returned in P_BASE_DBI_NAME. If
2044: P_FULL_MLS is false then this call performs the old pseudo-MLS
2045: generation of multiple database items in FF_DATABASE_ITEMS, and
2046: P_BASE_DBI_NAME is ignored.
2047: */
2048: procedure insert_database_item
2049: (
2056: p_user_entity_id in number,
2057: p_full_mls in boolean,
2058: p_base_dbi_name out nocopy varchar2
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;
2057: p_full_mls in boolean,
2058: p_base_dbi_name out nocopy varchar2
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;
2078: --
2079: cursor c_dbi_exists(c_user_name IN VARCHAR2,
2080: c_user_entity_id IN VARCHAR2) IS
2081: SELECT 'Y'
2082: FROM ff_database_items
2083: WHERE user_name = c_user_name
2084: AND user_entity_id = c_user_entity_id;
2085: --
2086: BEGIN
2164: FETCH c_dbi_exists INTO l_exists;
2165: IF c_dbi_exists%NOTFOUND then
2166: BEGIN
2167: --
2168: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2169: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2170: --
2171: check_for_dbi_clash
2172: (p_user_name => l_user_name
2165: IF c_dbi_exists%NOTFOUND then
2166: BEGIN
2167: --
2168: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2169: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2170: --
2171: check_for_dbi_clash
2172: (p_user_name => l_user_name
2173: ,p_ue_id => l_user_entity_id
2176: ,p_startup_mode => startup_mode
2177: ,p_clash => l_clash
2178: );
2179: if not l_clash then
2180: insert into ff_database_items
2181: (user_name
2182: ,user_entity_id
2183: ,data_type
2184: ,definition_text
2257: FETCH c_dbi_exists INTO l_exists;
2258: IF c_dbi_exists%NOTFOUND then
2259: BEGIN
2260: --
2261: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2262: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2263: --
2264: check_for_dbi_clash
2265: (p_user_name => l_user_name
2258: IF c_dbi_exists%NOTFOUND then
2259: BEGIN
2260: --
2261: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2262: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2263: --
2264: check_for_dbi_clash
2265: (p_user_name => l_user_name
2266: ,p_ue_id => l_user_entity_id
2269: ,p_startup_mode => startup_mode
2270: ,p_clash => l_clash
2271: );
2272: if not l_clash then
2273: insert into ff_database_items
2274: (user_name
2275: ,user_entity_id
2276: ,data_type
2277: ,definition_text
2523: ) is
2524: l_route_id number;
2525: l_user_entities_seq number;
2526: l_dummy_fetch_var number;
2527: l_user_entity_name ff_database_items.user_name%type;
2528: startup_mode varchar2(10);
2529: rgeflg varchar2(1);
2530: l_message varchar2(255);
2531:
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'
2775: and ent.user_entity_id = dbi.user_entity_id
2852: or exists (select null from per_business_groups_perf b
2853: where f.business_group_id = b.business_group_id
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
5492: , fdi.effective_start_date
5493: from ff_fdi_usages_f fdi
5494: where exists
5495: (select null
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
7831: statem varchar2(256);
7832: sql_cur number;
7833: ignore number;
7834: begin
7835: statem := 'alter trigger ff_database_items_brd disable';
7836: sql_cur := dbms_sql.open_cursor;
7837: dbms_sql.parse(sql_cur,
7838: statem,
7839: dbms_sql.v7);
7852: statem varchar2(256);
7853: sql_cur number;
7854: ignore number;
7855: begin
7856: statem := 'alter trigger ff_database_items_brd enable';
7857: sql_cur := dbms_sql.open_cursor;
7858: dbms_sql.parse(sql_cur,
7859: statem,
7860: dbms_sql.v7);
7880: statem,
7881: dbms_sql.v7);
7882: ignore := dbms_sql.execute(sql_cur);
7883: dbms_sql.close_cursor(sql_cur);
7884: statem := 'alter trigger ff_database_items_bri disable';
7885: sql_cur := dbms_sql.open_cursor;
7886: dbms_sql.parse(sql_cur,
7887: statem,
7888: dbms_sql.v7);
7908: statem,
7909: dbms_sql.v7);
7910: ignore := dbms_sql.execute(sql_cur);
7911: dbms_sql.close_cursor(sql_cur);
7912: statem := 'alter trigger ff_database_items_bri enable';
7913: sql_cur := dbms_sql.open_cursor;
7914: dbms_sql.parse(sql_cur,
7915: statem,
7916: dbms_sql.v7);
7977: --
7978: -- check_for_dbi_clash
7979: --
7980: -- This keeps the database item clash checking code in one place.
7981: -- This is for additions to FF_DATABASE_ITEMS.
7982: -- It also checks G_TRIGGERS_ALTERED.
7983: -- The code does not bother with FF_GLOBALS_F as that's the
7984: -- same as checking against database items.
7985: --
8008: into l_exists
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
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
8018: and
8056: )
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
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)
8066: and a.user_entity_id = b.user_entity_id
8148: and view_application_id = 3
8149: and security_group_id = g_security_group_id
8150: order by length(lookup_code) desc;
8151: --
8152: l_item_name ff_database_items.user_name%type;
8153: begin
8154: --
8155: -- 523343. if the item name consists of more than one lookup_code,
8156: -- split into its constituent parts and translate separately, then
8193: and d.balance_dimension_id = db.balance_dimension_id
8194: ;
8195: --
8196: l_found boolean;
8197: l_base_name ff_database_items.user_name%type;
8198: l_leg_code pay_defined_balances.legislation_code%type;
8199: l_bg_id pay_defined_balances.business_group_id%type;
8200: l_bal_id pay_defined_balances.balance_type_id%type;
8201: l_baldim_id pay_defined_balances.balance_dimension_id%type;
8315: where iv.input_value_id = p_input_value_id
8316: and et.element_type_id = iv.element_type_id
8317: ;
8318: --
8319: l_prefix ff_database_items.user_name%type;
8320: l_leg_code pay_input_values_f.legislation_code%type;
8321: l_bg_id pay_input_values_f.business_group_id%type;
8322: l_found boolean;
8323: begin
8650: ,p_creator_type in varchar2
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
8682: --
8683: -- check_for_tl_dbi_clash
8684: --
8685: -- This keeps the database item clash checking code in one place.
8686: -- This is for additions to FF_DATABASE_ITEMS_TL.
8687: -- It also checks G_TRIGGERS_ALTERED.
8688: -- The code does not bother with FF_GLOBALS_F as that's the
8689: -- same as checking against database items.
8690: --
8714: into l_exists
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
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)
8724: and a.user_entity_id = b.user_entity_id
8763: )
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
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)
8773: and a.user_entity_id = b.user_entity_id
8891: ,p_language in varchar2
8892: ,p_tl_user_name in varchar2
8893: ) is
8894: l_clash boolean;
8895: l_tl_user_name ff_database_items_tl.translated_user_name%type;
8896: l_got_error boolean;
8897: begin
8898: --
8899: -- G_TRIGGERS_ALTERED is true for the bulk generation of balance,
8917: ,p_clash => l_clash
8918: );
8919:
8920: if not l_clash then
8921: update ff_database_items_tl dbitl
8922: set dbitl.translated_user_name = l_tl_user_name
8923: , dbitl.source_lang = p_language
8924: where dbitl.language = p_language
8925: and dbitl.user_name = p_user_name
8946: hr_utility.raise_error;
8947: end if;
8948: elsif g_dyndbi_changes then
8949: l_tl_user_name := p_tl_user_name;
8950: ff_database_items_pkg.update_seeded_tl_rows
8951: (x_user_name => p_user_name
8952: ,x_user_entity_id => p_user_entity_id
8953: ,x_language => p_language
8954: ,x_translated_user_name => l_tl_user_name
8961: end if;
8962:
8963: --
8964: -- One-off update for a single element type, input value, or balance.
8965: -- In the FF_DATABASE_ITEMS case, the FF_DATABASE_ITEMS trigger would
8966: -- handle this validation. Unfortunately, an update trigger cannot
8967: -- perform this because of ORA-04091 errors.
8968: --
8969: else
8970: --
8971: -- Note: UPDATE_TL_ROW calls STR2DBINAME so passing p_tl_user_name
8972: -- unmodified to UPDATE_TL_ROW.
8973: --
8974: ff_database_items_pkg.update_tl_row
8975: (x_user_name => p_user_name
8976: ,x_user_entity_id => p_user_entity_id
8977: ,x_language => p_language
8978: ,x_source_lang => p_language
8995: ,p_suffix in varchar2
8996: ,p_date_p in varchar2
8997: ) is
8998: --
8999: l_tl_name ff_database_items_tl.translated_user_name%type;
9000: begin
9001: for i in 1 .. p_prefixes.count loop
9002: if p_prefixes(i).found then
9003: --
9037: l_leg_code varchar2(30);
9038: l_bg_id number;
9039: l_bal_id pay_defined_balances.balance_type_id%type;
9040: l_baldim_id pay_defined_balances.balance_dimension_id%type;
9041: l_base_name ff_database_items.user_name%type;
9042: l_ueid number;
9043: l_tl_name ff_database_items_tl.translated_user_name%type;
9044: l_found boolean;
9045: l_startup_mode varchar2(30);
9039: l_bal_id pay_defined_balances.balance_type_id%type;
9040: l_baldim_id pay_defined_balances.balance_dimension_id%type;
9041: l_base_name ff_database_items.user_name%type;
9042: l_ueid number;
9043: l_tl_name ff_database_items_tl.translated_user_name%type;
9044: l_found boolean;
9045: l_startup_mode varchar2(30);
9046: begin
9047: --
9151: ,p_languages in dbms_sql.varchar2s
9152: ,p_user_entity_id in out nocopy number
9153: ,p_suffix in varchar2
9154: ) is
9155: l_base_name ff_database_items.user_name%type;
9156: begin
9157: gen_et_base_dbi_name
9158: (p_element_type_id => p_element_type_id
9159: ,p_leg_code => p_leg_code
9627: ,p_languages in dbms_sql.varchar2s
9628: ,p_user_entity_id in out nocopy number
9629: ,p_suffix in varchar2
9630: ) is
9631: l_base_name ff_database_items.user_name%type;
9632: begin
9633: gen_eiv_base_dbi_name
9634: (p_input_value_id => p_input_value_id
9635: ,p_leg_code => p_leg_code