[Home] [Help]
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
167: characters in routines that generate base
168: dbi names - this is to match the main
180: arashid 115.135 27-APR-2006 The following changes were made:
181: - process date-paid and non-date-paid
182: database items when updating _TL names.
183: - process_pay_dyndbi_changes code now calls
184: FF_DATABASE_ITEMS_PKG.UPDATE_SEEDED_TL_ROWS
185: and writes messages to the concurrent
186: processing log file.
187: arashid 115.134 31-MAR-2006 Changed process_pay_dyndbi_changes to not
188: raise assertions when entities are missing.
212: uom_requires_dbis
213: 4. Changed insert_database_item to handle
214: full MLS and pseudo-MLS as separate
215: cases. For full MLS, only a single
216: base DBI in ff_database_items is
217: generated.
218: 5. Changed code for generating element,
219: input, and defined balance DBIs to
220: handle full MLS where it is supported.
218: 5. Changed code for generating element,
219: input, and defined balance DBIs to
220: handle full MLS where it is supported.
221: 6. Added update_xxx calls for MLS updates
222: to FF_DATABASE_ITEMS_TL.
223: Updates to FP.K and earlier should be
224: branched on 115.28.
225: alogue 115.128 07-OCT-2005 Performance fix to sel_ffci in
226: create_alternative_dbis. Bug 4658377.
592: hr_lookups in insert_database_item procedure,
593: also passed parameter of legislation code removed
594: since it is no longer used.
595: abraae 09-SEP-1993 strip blanks from DB Item defn text to fit into
596: ff_database_items.definition_text (char(240))
597: mwcallag 08-SEP-1993 Input value definition text modified to include a
598: decode on the UOM to avoid problem in formula when
599: several DB items of different user definable
600: data types are retrieved in 1 formula cursor.
1008: ,p_startup_mode => p_startup_mode
1009: ,p_clash => l_clash
1010: );
1011: if not l_clash then
1012: insert into ff_database_items
1013: (user_name
1014: ,user_entity_id
1015: ,data_type
1016: ,definition_text
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
1078: from
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
1084: and fdbi.user_entity_id = a.user_entity_id
1111: )
1112: )
1113: );
1114:
1115: l_db_item_name ff_database_items.user_name%type;
1116: usr_ent_id number;
1117: l_dbitem_def_text ff_database_items.definition_text%type;
1118: --
1119: begin
1113: );
1114:
1115: l_db_item_name ff_database_items.user_name%type;
1116: usr_ent_id number;
1117: l_dbitem_def_text ff_database_items.definition_text%type;
1118: --
1119: begin
1120: --
1121: for rrrec in get_alt_routes(p_balance_dimension_id) loop
1271: from fnd_sessions
1272: where session_id = userenv('sessionid');
1273: --
1274: l_route_id ff_routes.route_id%type;
1275: l_db_item_name ff_database_items.user_name%type;
1276: l_dbitem_def_text ff_database_items.definition_text%type;
1277: l_dbi_function pay_balance_dimensions.database_item_function%type;
1278:
1279: startup_mode varchar2(10);
1272: where session_id = userenv('sessionid');
1273: --
1274: l_route_id ff_routes.route_id%type;
1275: l_db_item_name ff_database_items.user_name%type;
1276: l_dbitem_def_text ff_database_items.definition_text%type;
1277: l_dbi_function pay_balance_dimensions.database_item_function%type;
1278:
1279: startup_mode varchar2(10);
1280: rgeflg varchar2(1);
1288:
1289: l_legislation_code varchar2(30);
1290: l_full_mls boolean;
1291: l_found boolean;
1292: l_tl_name ff_database_items_tl.translated_user_name%type;
1293: --
1294: begin
1295: --
1296: IF (g_triggers_altered) THEN
1563: ))
1564: and mod(defined_balance_id, p_maxworkers) = p_worker_id
1565: order by b.defined_balance_id;
1566:
1567: l_db_item_name ff_database_items.user_name%type;
1568: startup_mode varchar2(10);
1569: l_loop_cnt number;
1570:
1571: begin
1744: where a.business_group_id = b.business_group_id
1745: and nvl(b.legislation_code, p_leg_code) = p_leg_code))
1746: order by a.defined_balance_id;
1747:
1748: l_db_item_name ff_database_items.user_name%type;
1749: startup_mode varchar2(10);
1750: l_loop_cnt number;
1751:
1752: begin
2044:
2045: If P_FULL_MLS is true then this call just creates a single base
2046: database item whose name is returned in P_BASE_DBI_NAME. If
2047: P_FULL_MLS is false then this call performs the old pseudo-MLS
2048: generation of multiple database items in FF_DATABASE_ITEMS, and
2049: P_BASE_DBI_NAME is ignored.
2050: */
2051: procedure insert_database_item
2052: (
2059: p_user_entity_id in number,
2060: p_full_mls in boolean,
2061: p_base_dbi_name out nocopy varchar2
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;
2060: p_full_mls in boolean,
2061: p_base_dbi_name out nocopy varchar2
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;
2081: --
2082: cursor c_dbi_exists(c_user_name IN VARCHAR2,
2083: c_user_entity_id IN VARCHAR2) IS
2084: SELECT 'Y'
2085: FROM ff_database_items
2086: WHERE user_name = c_user_name
2087: AND user_entity_id = c_user_entity_id;
2088: --
2089: BEGIN
2167: FETCH c_dbi_exists INTO l_exists;
2168: IF c_dbi_exists%NOTFOUND then
2169: BEGIN
2170: --
2171: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2172: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2173: --
2174: check_for_dbi_clash
2175: (p_user_name => l_user_name
2168: IF c_dbi_exists%NOTFOUND then
2169: BEGIN
2170: --
2171: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2172: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2173: --
2174: check_for_dbi_clash
2175: (p_user_name => l_user_name
2176: ,p_ue_id => l_user_entity_id
2179: ,p_startup_mode => startup_mode
2180: ,p_clash => l_clash
2181: );
2182: if not l_clash then
2183: insert into ff_database_items
2184: (user_name
2185: ,user_entity_id
2186: ,data_type
2187: ,definition_text
2260: FETCH c_dbi_exists INTO l_exists;
2261: IF c_dbi_exists%NOTFOUND then
2262: BEGIN
2263: --
2264: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2265: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2266: --
2267: check_for_dbi_clash
2268: (p_user_name => l_user_name
2261: IF c_dbi_exists%NOTFOUND then
2262: BEGIN
2263: --
2264: -- Make sure that the name does not clash with FF_DATABASE_ITEMS,
2265: -- FF_DATABASE_ITEMS_TL, or FF_CONTEXT rows.
2266: --
2267: check_for_dbi_clash
2268: (p_user_name => l_user_name
2269: ,p_ue_id => l_user_entity_id
2272: ,p_startup_mode => startup_mode
2273: ,p_clash => l_clash
2274: );
2275: if not l_clash then
2276: insert into ff_database_items
2277: (user_name
2278: ,user_entity_id
2279: ,data_type
2280: ,definition_text
2526: ) is
2527: l_route_id number;
2528: l_user_entities_seq number;
2529: l_dummy_fetch_var number;
2530: l_user_entity_name ff_database_items.user_name%type;
2531: startup_mode varchar2(10);
2532: rgeflg varchar2(1);
2533: l_message varchar2(255);
2534:
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'
2778: and ent.user_entity_id = dbi.user_entity_id
2855: or exists (select null from per_business_groups_perf b
2856: where f.business_group_id = b.business_group_id
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
5495: , fdi.effective_start_date
5496: from ff_fdi_usages_f fdi
5497: where exists
5498: (select null
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
7079: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
7080: /*
7081: NAME
7082: create_ppm_devdff_flex_dict - Create Further Personal Payment Method
7083: Info DFF database items
7084: --
7085: DESCRIPTION
7086: This procedure is the main entry point for creating Further Personal
7087: Payment Method Info descriptive flexfield database items. The parameters
7908: statem varchar2(256);
7909: sql_cur number;
7910: ignore number;
7911: begin
7912: statem := 'alter trigger ff_database_items_brd disable';
7913: sql_cur := dbms_sql.open_cursor;
7914: dbms_sql.parse(sql_cur,
7915: statem,
7916: dbms_sql.v7);
7929: statem varchar2(256);
7930: sql_cur number;
7931: ignore number;
7932: begin
7933: statem := 'alter trigger ff_database_items_brd enable';
7934: sql_cur := dbms_sql.open_cursor;
7935: dbms_sql.parse(sql_cur,
7936: statem,
7937: dbms_sql.v7);
7957: statem,
7958: dbms_sql.v7);
7959: ignore := dbms_sql.execute(sql_cur);
7960: dbms_sql.close_cursor(sql_cur);
7961: statem := 'alter trigger ff_database_items_bri disable';
7962: sql_cur := dbms_sql.open_cursor;
7963: dbms_sql.parse(sql_cur,
7964: statem,
7965: dbms_sql.v7);
7985: statem,
7986: dbms_sql.v7);
7987: ignore := dbms_sql.execute(sql_cur);
7988: dbms_sql.close_cursor(sql_cur);
7989: statem := 'alter trigger ff_database_items_bri enable';
7990: sql_cur := dbms_sql.open_cursor;
7991: dbms_sql.parse(sql_cur,
7992: statem,
7993: dbms_sql.v7);
8054: --
8055: -- check_for_dbi_clash
8056: --
8057: -- This keeps the database item clash checking code in one place.
8058: -- This is for additions to FF_DATABASE_ITEMS.
8059: -- It also checks G_TRIGGERS_ALTERED.
8060: -- The code does not bother with FF_GLOBALS_F as that's the
8061: -- same as checking against database items.
8062: --
8085: into l_exists
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
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
8095: and
8133: )
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
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)
8143: and a.user_entity_id = b.user_entity_id
8225: and view_application_id = 3
8226: and security_group_id = g_security_group_id
8227: order by length(lookup_code) desc;
8228: --
8229: l_item_name ff_database_items.user_name%type;
8230: begin
8231: --
8232: -- 523343. if the item name consists of more than one lookup_code,
8233: -- split into its constituent parts and translate separately, then
8270: and d.balance_dimension_id = db.balance_dimension_id
8271: ;
8272: --
8273: l_found boolean;
8274: l_base_name ff_database_items.user_name%type;
8275: l_leg_code pay_defined_balances.legislation_code%type;
8276: l_bg_id pay_defined_balances.business_group_id%type;
8277: l_bal_id pay_defined_balances.balance_type_id%type;
8278: l_baldim_id pay_defined_balances.balance_dimension_id%type;
8392: where iv.input_value_id = p_input_value_id
8393: and et.element_type_id = iv.element_type_id
8394: ;
8395: --
8396: l_prefix ff_database_items.user_name%type;
8397: l_leg_code pay_input_values_f.legislation_code%type;
8398: l_bg_id pay_input_values_f.business_group_id%type;
8399: l_found boolean;
8400: begin
8727: ,p_creator_type in varchar2
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
8759: --
8760: -- check_for_tl_dbi_clash
8761: --
8762: -- This keeps the database item clash checking code in one place.
8763: -- This is for additions to FF_DATABASE_ITEMS_TL.
8764: -- It also checks G_TRIGGERS_ALTERED.
8765: -- The code does not bother with FF_GLOBALS_F as that's the
8766: -- same as checking against database items.
8767: --
8791: into l_exists
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
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)
8801: and a.user_entity_id = b.user_entity_id
8840: )
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
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)
8850: and a.user_entity_id = b.user_entity_id
8968: ,p_language in varchar2
8969: ,p_tl_user_name in varchar2
8970: ) is
8971: l_clash boolean;
8972: l_tl_user_name ff_database_items_tl.translated_user_name%type;
8973: l_got_error boolean;
8974: begin
8975: --
8976: -- G_TRIGGERS_ALTERED is true for the bulk generation of balance,
8994: ,p_clash => l_clash
8995: );
8996:
8997: if not l_clash then
8998: update ff_database_items_tl dbitl
8999: set dbitl.translated_user_name = l_tl_user_name
9000: , dbitl.source_lang = p_language
9001: where dbitl.language = p_language
9002: and dbitl.user_name = p_user_name
9023: hr_utility.raise_error;
9024: end if;
9025: elsif g_dyndbi_changes then
9026: l_tl_user_name := p_tl_user_name;
9027: ff_database_items_pkg.update_seeded_tl_rows
9028: (x_user_name => p_user_name
9029: ,x_user_entity_id => p_user_entity_id
9030: ,x_language => p_language
9031: ,x_translated_user_name => l_tl_user_name
9038: end if;
9039:
9040: --
9041: -- One-off update for a single element type, input value, or balance.
9042: -- In the FF_DATABASE_ITEMS case, the FF_DATABASE_ITEMS trigger would
9043: -- handle this validation. Unfortunately, an update trigger cannot
9044: -- perform this because of ORA-04091 errors.
9045: --
9046: else
9047: --
9048: -- Note: UPDATE_TL_ROW calls STR2DBINAME so passing p_tl_user_name
9049: -- unmodified to UPDATE_TL_ROW.
9050: --
9051: ff_database_items_pkg.update_tl_row
9052: (x_user_name => p_user_name
9053: ,x_user_entity_id => p_user_entity_id
9054: ,x_language => p_language
9055: ,x_source_lang => p_language
9072: ,p_suffix in varchar2
9073: ,p_date_p in varchar2
9074: ) is
9075: --
9076: l_tl_name ff_database_items_tl.translated_user_name%type;
9077: begin
9078: for i in 1 .. p_prefixes.count loop
9079: if p_prefixes(i).found then
9080: --
9114: l_leg_code varchar2(30);
9115: l_bg_id number;
9116: l_bal_id pay_defined_balances.balance_type_id%type;
9117: l_baldim_id pay_defined_balances.balance_dimension_id%type;
9118: l_base_name ff_database_items.user_name%type;
9119: l_ueid number;
9120: l_tl_name ff_database_items_tl.translated_user_name%type;
9121: l_found boolean;
9122: l_startup_mode varchar2(30);
9116: l_bal_id pay_defined_balances.balance_type_id%type;
9117: l_baldim_id pay_defined_balances.balance_dimension_id%type;
9118: l_base_name ff_database_items.user_name%type;
9119: l_ueid number;
9120: l_tl_name ff_database_items_tl.translated_user_name%type;
9121: l_found boolean;
9122: l_startup_mode varchar2(30);
9123: begin
9124: --
9228: ,p_languages in dbms_sql.varchar2s
9229: ,p_user_entity_id in out nocopy number
9230: ,p_suffix in varchar2
9231: ) is
9232: l_base_name ff_database_items.user_name%type;
9233: begin
9234: gen_et_base_dbi_name
9235: (p_element_type_id => p_element_type_id
9236: ,p_leg_code => p_leg_code
9704: ,p_languages in dbms_sql.varchar2s
9705: ,p_user_entity_id in out nocopy number
9706: ,p_suffix in varchar2
9707: ) is
9708: l_base_name ff_database_items.user_name%type;
9709: begin
9710: gen_eiv_base_dbi_name
9711: (p_input_value_id => p_input_value_id
9712: ,p_leg_code => p_leg_code