1 PACKAGE body hr_legislation AS
2 /* $Header: pelegins.pkb 120.13.12000000.3 2007/07/10 11:28:50 divicker ship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 ------------------------------------------------------------------------------
7 -- NAME : pelegins.pkb
8 --
9 -- DESCRIPTION
10 -- Procedures used for the delivery of legislative startup data. The
11 -- same procedures are also used for legislative refreshes.
12 -- This is the main driving package to call all other startup
13 -- packages and procedures.
14 -- MODIFIED
15 -- 80.0 Ian Carline 14-09-1993 - Created
16 -- 80.2 Ian Carline 03-11-1993 - Corrected order of balance routines
17 -- 80.3 Ian Carline 03-11-1993 - Altered table definition of ass info
18 -- information types to include new
19 -- column.
20 -- 80.4 Ian Carline 11-11-1993 - Debugged for the US delivery test.
21 -- Including lookups delivery.
22 -- 80.5 Ian Carline 15-11-1993 - Further debugging and new delivery
23 -- blocks for US Bechtel delivery.
24 -- 80.6 Ian Carline 10-12-1993 - Added monetary units to delivery
25 -- scripts.
26 -- 80.7 Ian Carline 13-12-1993 - Corrections to syntax
27 -- 80.8 Rod Fine 16-12-1993 - Put AS on same line as CREATE stmt
28 -- to workaround export WWBUG #178613.
29 -- 80.9 Ian Carline 17-03-1994 - Changed the delivery of balance
30 -- dimensions, to compare details
31 -- using a reduced list of columns.
32 -- 80.10 Ian Carline 18-04-1994 - Added the recreate DB items logic
33 -- as supplied by MWCALLAG.
34 -- 80.11 Ian Carline 21-04-1994 - Install of user rows must consider
35 -- parent table id when looking for
36 -- duplicates.
37 --
38 -- 70.9 Ian Carline 06-Jun-1994 - per 7.0 and 8.0 merged.
39 -- Rewrite.
40 -- 70.10 Ian Carline 07-Jun-1994 - Amended check_next_sequence logic.
41 -- 70.11 Ian Carline 08-Jun-1994 - Extended check_next_sequence logic.
42 -- 70.12 Ian Carline 09-Jun-1994 - Corrected check_next_sequence logic.
43 -- 70.13 Ian Carline 13-Jun-1994 - New install driving procedure.
44 -- Balance_feeds are only installed if
45 -- payroll is instaled with status of I.
46 -- 70.15 Ian Carline 02-Aug-1994 - Altered check on assignment status
47 -- types. When looking for valid leg
48 -- subgroups, the statement used to
49 -- use 'IS NULL'. This caused an error
50 -- in Oracle 7.1.3 . Now the statement
51 -- uses an NVL statement to catch a
52 -- null condition.
53 -- 70.16 Ian Carline 27-Jul-1994 - Checks on balance type name now view
54 -- '_' as ' ' .
55 -- 70.17 Rod Fine 19-Sep-1994 - Removed the unnecessary cartesian
56 -- product join on all selects to get
57 -- the starting point for the sequence
58 -- number - improves performance.
59 -- Also added two new columns to
60 -- installation procedures:
61 -- - FEED_CHECKING_TYPE in
62 -- PAY_BALANCE_DIMENSIONS;
63 -- - JURISDICTION_LEVEL in
64 -- PAY_BALANCE_TYPES.
65 -- 70.18 Rod Fine 23-Nov-1994 - Suppressed index on business_group_id
66 -- 70.19 Rod Fine 27-Mar-1995 - Added tax_type column to
67 -- pay_balance_types table.
68 -- 70.20 Rod Fine 07-Apr-1995 - Changed order in which hr_leg_loc is
69 -- called, so that the new classn_id is
70 -- updated BEFORE the table is
71 -- transferred.
72 -- 70.21 Rod Fine 14-Nov-1995 - #271139: install_urows wrongly omitted
73 -- to include the user table id and row
74 -- high range as part of the true key.
75 -- Also added maintenance of the
76 -- display_sequence column in the user
77 -- rows table.
78 -- 70.22 Rod Fine 03-Jan-96 - #331831 Add NVLs to the check for
79 -- identical pay_balance_types wherever
80 -- values may be null, to prevent flagging
81 -- as different rows which are identical
82 -- but have some null values.
83 -- 70.23 Rod Fine 09-Jan-96 - #331831 Chngd NVL on jurisdiction_level
84 -- from ' ' to 0, as it's a number.
85 -- 70.24 rfine 27-Mar-96 n/a Removed call to the code which delivers
86 -- lookups. These are no longer delivered
87 -- by the HR mechanism: they're all
88 -- delivered by Datamerge.
89 -- 310520 Change to update pay_payment_types rows
90 -- if they already exist. This differs
91 -- from the previous functionality, which
92 -- only ever did inserts, no updates.
93 -- 70.25 rfine 01-Apr-96 n/a Fixed side-effect of previous fix to
94 -- 271139. It did not allow for the
95 -- possibility that row_high_range could
96 -- be null. Need to add NVL.
97 -- 70.25 M. Stewart 23-Sep-1996 - Updated table names from STU_ to HR_S_
98 -- 70.26 mhoyes 15-OCT-96 n/a Added code to procedure install which
99 -- loops through all exceptions raised
100 -- during phase 1 and outputs details of
101 -- these exceptions to the terminal using
102 -- dbms_output.
103 -- 70.27 Tim Eyres 02-Jan-1997 - Moved arcs header to directly after
104 -- 'create or replace' line
105 -- Fix to bug 434902
106 -- 70.30 Tim Eyres 02-Jan-1997 Correction to version number
107 -- 110.1 mstewart 23-JUL-1997 Removed show error and select from
108 -- user errors statements
109 -- (R11 version # 70.31)
110 -- 110.2 mfender 30-DEC-1997 bug 603778 - workaround in p.a.s.t.
111 -- to bump up sequence.
112 -- 110.3 A. Mills 27-APR-1998 Bug 648835. Added a housekeeping
113 -- measure into install_bal_types
114 -- that removes any balance feeds
115 -- from the delivery table where
116 -- they are unnecessary because
117 -- they already exist on the live tables,
118 -- to ensure that latest balances are not
119 -- trashed by the live table's
120 -- update insert and delete triggers.
121 -- 110.4 A.Mills 11-Sep-1998 Bug 724540. ORA-01722 Invalid Number
122 -- error fixed by ensuring proper
123 -- type-compare in remove/transfer row
124 -- (local) procedures between hr_application
125 -- ownerships and surrogate keys.
126 -- 110.5 A.Mills 30-Sep-1998 Added extra validation on delete
127 -- from pay_balance_feeds_f to ensure
128 -- the legislative rows aren't deleted
129 -- incorrectly.
130 -- 110.6 I. Harding 16/11/98 Commented out calls that insert lookups.
131 -- 110.7 M.Reid 27/01/99 Tidied up balance checks for visible
132 -- legislations. Although Core do not
133 -- seed balances currently the script did
134 -- not cater for core and legislation
135 -- balances clashing.
136 -- 110.8 A.Mills 17/02/99 Added to_chars around surrogate id's
137 -- for comparison and entry into the
138 -- hr_application_ownerships table, in
139 -- response to ZA install problem.
140 -- 110.9 A.Mills 05-Mar-1999 Changed implicit cursors to explicit
141 -- cursors, due to implicit cursor's
142 -- 'select into null where exists'
143 -- type queries not working correctly.
144 -- 110.10 S.Sivasub 15-Mar-1999 Created additional procedures to seed
145 -- seven MLS's TL tables.
146 -- 110.11 S.Sivasub 12-Apr-1999 Modified procedures to seed seven MLS's
147 -- TL tables with a double check on the not exist.
148 -- 115.14 A.Alogue 12-May-1999 Fix to handling of pay_user_rows_f
149 -- to cope with null legislation_codes
150 -- in the seeding of core data.
151 -- 115.15 I.Harding 21-May-99 removed dbms_output.put_line
152 -- 115.16 IHARDING 26-may-99 replaced dbms_output.put_line by null
153 -- 115.18 IHARDING 20-Jul-99 made all 9 numbers 15.
154 -- 115.19 tbattoo 27-OCT-99 added procedure install_magnetic_block,
155 -- install_report_mappings
156 -- 115.20 tbattoo 01-NOV-99 bug 1058335, moved installation of mag_records
157 -- to a seperate procedure, and not as a child
158 -- of magnetic_records
159 -- 115.21 vmehta 10-nov-1999 added the call to
160 -- hr_legislation_local.translate_ele_dev_df
161 -- for transferring the balance type ids
162 -- element_type_ids stored in the
163 -- Element Developer DF
164 -- 115.22 tbattoo 01-NOV-99 bug 1058335, moved installation of mag_records
165 -- to a seperate procedure, and not as a child
166 -- of magnetic_records
167 -- 115.22 tbattoo 01-NOV-99 bug 1034683, changed instal_bal_types,
168 -- check_next_sequence, to update
169 -- pay_defined_balance_s
170 -- 115.24 vmehta 07-DEC-99 Added report_format in
171 -- pay_report_format_mappings_f update
172 -- clause in transfer_row procedure.
173 -- 115.25 ablinko 08-DEC-99 Only delete rows with no buisness group
174 -- from pay_user_column_instances_f
175 -- 115.26 tbattoo 08-Feb-00 changed crt_exc so calls
176 -- hr_legislation.insert_hr_stu_exceptions
177 -- This is an autonomous transaction procedure
178 -- , so we dont loose error messages on rollback
179 -- 115.27 mreid 24-Feb-00 Added territory code to payment types
180 -- unique key checking.
181 -- 115.29 tbattoo 07-Mar-00 changed to check for null territory code
182 -- to payment types
183 -- 115.30 tbattoo 03-Apr-00 bug 1234525 - insert translated value for
184 -- input value name 'Pay Vaue'
185 -- 115.31 RThirlby 11-APR-2000 Added call to translate_ca_ele_dev_df
186 -- for transferrind CA balance type ids
187 -- stored in the Element Developer DF.
188 -- 115.32 tbattoo 12-APr-2000 Added new columns to install
189 -- pay_legislative_field_info
190 -- 115.33 tbattoo 18-Apr-2000 Added updatable_flag column to
191 -- install_report_mappings
192 -- 115.34 tbattoo 25-Apr-2000 Added support for pay_report_format_items_f
193 -- 115.35 mreid 19-Jun-2000 Added to update_sequence in case
194 -- sequence has been rebuilt and is less
195 -- than the installed rows.
196 -- 115.36 divicker 11-Sep-2000 Performace changes
197 -- 115.37 alogue 03-Nov-2000 Temporary fix for bug 1485136 : random
198 -- loss of balance_feeds.
199 -- 115.39 alogue 06-Nov-2000 Permanent fix for bug 1485136.
200 -- Reimplemented code to avoid needless
201 -- deletion and recreation of balance
202 -- feeds (thereby avoiding needless loss
203 -- of latest balances). We don't now delete
204 -- from hr_s_balance_feeds_f ... we only
205 -- select back those feeds that don't match
206 -- rows in pay_balance_feeds_f instead.
207 -- Bug 1490386.
208 -- 115.40 amills 21-Dec-2000 1550308. Handle exception whereby
209 -- legislative user rows on live table
210 -- have same row_low_range_or_name or
211 -- rlron and high range combination but
212 -- more than one user_row_id in Phase 1.
213 -- Previously caused ora-1422.
214 -- 115.41 alogue 21-Feb-2001 Performance fix to install_bal_types
215 -- transfer_row.
216 -- 115.42 divicker 25-APR-2001 Added GROSSUP_ALLOWED_FLAG processing
217 -- to balance_type transfer
218 -- 115.43 divicker May 2001 Support for parallel hrglobal and
219 -- better debugging into HR_STU_EXCEPTIONS
220 -- 115.44 divicker 14-Jun-2001 Bug fix 1803867
221 -- 115.45 mreid 02-Jul-2001 Fixed monetary unit app ownership
222 -- 115.46 divicker 07-Jul-2001 Fix to maintain_history PK violation
223 -- 115.47 divicker 11-Jul-2001 Fix to add distinct to hr_s_defined_balances
224 -- 115.48 divicker 19-Jul-2001 Fix to hr_s_defined_balances again so
225 -- that we don't update the defined_bal_id
226 -- by the seq for all values of the orig
227 -- def_bal_id but only those matching
228 -- the original balance_type_id as well
229 -- 115.49 divicker 19-JUL-2001 Version sync up
230 -- 115.50 divicker 26-JUL-2001 speed up sequence setting
231 -- 115.51 divicker 26-JUL-2001 pay_magnetic_blocks fix for installing
232 -- data over a previous install for APAC
233 -- 115.52 tbattoo 15-Aug-2001 fixed issue with check_seq on empty hr_s
234 -- tables.
235 -- 115.53 vmehta 25-Aug-2001 Removed calls to translate_us_ele_dev_df
236 -- 115.54 tbattoo 29-Aug-2001 Fix to install_report_format_mappings
237 -- 115.55 divicker 03-SEP-2001 nvl added to munge sequence for fresh
238 -- installs (1967626). remove owner hr
242 -- on all legislations selected
239 -- 115.56 divicker 03-SEP-2001 munge_sequence owner selection added
240 -- 115.57 divicker 07-SEP-2001 2 fixes to install_leg_field
241 -- 115.58 divicker 13-SEP-2001 fix so that hr_stu_history contains info
243 -- 115.59 divicker 14-SEP-2001 nvl added to validation_name in select
244 -- stmt in install_leg_field proc.
245 -- 115.60 divicker 24-SEP-2001 fix for munge_sequence where same named
246 -- sequence has more than one owner
247 -- 115.61 divicker 29-SEP-2001 performance fixes
248 -- 115.62 divicker 01-OCT-2001 remove sho err and uncomment exit from end
249 -- 115.63 divicker 02-OCT-2001 more performance fixes
250 -- 115.64 divicker 15-OCT-2001 fix to install_magnetic_blocks
251 -- 115.65 divicker 16-OCT-2001 another fix for magnetic_records
252 -- 115.66 divicker 17-OCT-2001 big speed up
253 -- 115.67 divicker 25-OCT-2001 performance-use temp HR_S indexes
254 -- 115.68 divicker 21-NOV-2001 del hr_s_app_ownerships commented
255 -- 115.69 divicker 21-NOV-2001 performance
256 -- 115.70 divicker 14-FEB-2002 fix to pet tl
257 -- 115.71 divicker 19-MAR-2002 added support for PAY_REPORT_FORMAT_
258 -- MAPPINGS_F.DEINITIALIZATION_CODE
259 -- 115.72 divicker 19-MAR-2002 Added dbdrv checkfile commands
260 -- 115.73 divicker 07-MAY-2002 Added w/around for situation where
261 -- customer has Core balance feeds
262 -- 2323024
263 -- 115.74 mreid 21-JUN-2002 Added support for run balance
264 -- architecture
265 -- 115.75 divicker 25-JUN-2002 defined balances now don't get deleted
266 -- simply if the hr_s and pay IDs match
267 -- so that changes to any column will get
268 -- transferred to live
269 -- 115.77 RThirlby 30-JUL-2002 Bug 2430399 After insert of balance
270 -- classification, added call to
271 -- hr_balance_feeds.ins_bf_bal_class, to
272 -- create balance feeds for existing
273 -- elements with same classification as
274 -- the balance classification inserted.
275 -- 115.78 RThirlby 06-AUG-2002 Bug 2496207 - changed update of
276 -- pay_defined_balances to not included
277 -- RUN_BALANCE_STATUS, so this column will
278 -- retain its original value.
279 -- 115.79 DVickers 12-AUG-2002 Managed db sequence fix
280 -- 115.80 DVickers 27-SEP-2002 HR_S->HR_APP_OWN move for ZZ leg
281 -- Internation Payroll requirement
282 -- 115.81 DVickers 07-OCT-2002 Added distinct to c_main legislative
283 -- driver cursor
284 -- 115.81.1159.2 DVickers 24-OCT-2002 Fix to create_zz_leg_rule proc to nable
285 -- rerun
286 -- 115.84 DVickers 06-JAN-2003 Support for PAY_DIMENSION_ROUTES
287 -- 115.85 DVickers 06-JAN-2003 Add a call to install_dimension_routes
288 -- 115.86 DVickers 14-JAN-2003 Fixes to install_dimension_routes
289 -- 115.87 DVickers 07-FEB-2003 Add bal_type columns
290 -- 115.88 DVickers 13-FEB-2003 Add support for pay_balance_categories
291 -- 115.89 DVickers 04-MAR-2003 Corrected for null bal cat in bal type
292 -- 115.90 DVickers 14-MAR-2003 explicit hrsao.key_value conversion
293 -- 115.91 DVickers 17-MAR-2003 Fix for correcting sync up of base_
294 -- balance_type_id
295 -- 115.92 DVickers 15-APR-2003 Added bg null check for update_uid
296 -- pay_defined_balances. Bug 2906340
297 -- 115.93 DVickers 24-APR-2003 Fix for delete of dt user_rows, cols
298 -- 115.94 DVickers 06-MAY-2003 del dim routes based on bal dim id
299 -- and priority not route_id
300 -- 115.95 DVickers 04-JUN-2003 new_category_name rollback correction
301 -- 115.95.11510.2 DVickers 17-JUL-2003 user_category_name col support for
302 -- pay_balance_categories_f
303 -- 115.95.11510.3 DVickers 18-JUL-2003 insert category_name if
304 -- user_category_name is null
305 -- 115.96 Scchakra 16-JUL-2003 Bug 2982582. Added code in install_att
306 -- to install startup data in table
307 -- pay_monetary_units_tl.
308 -- 115.97 DVickers 30-SEP-2003 Added distinct to create_zz_leg_rule
309 -- 115.98 DVickers 02-OCT-2003 Added 4 new cols to
310 -- HR_S_LEGISLATIVE_FIELD_INFO
311 -- 115.99 DVickers 02-SEP-2003 Added CATEGORY as well
312 -- 115.100 DVickers 05-NOV-2003 check for existence of pay_dimension
313 -- route parents before insertion
314 -- 115.101 DVickers 17-MAR-2004 Bug 3510411. gscc fixes
315 -- 115.102 DVickers 18-MAR-2004 Refix to 115.101
316 -- 115.103 DVickers 29-APR-2004 HRRUNPRC named PIPE for all common
320 -- abort immediately
317 -- unhandled exceptions
318 -- 115.104 DVickers 06-MAY-2004 Ensure Phase exceptions will retain the -- original error data and not cause
319 -- adpatch to rerun the whole process but
321 -- 115.105 DVickers 12-MAY-2004 Fix for 8i
322 -- 115.106 DVickers 16-JUN-2004 Close pipe on ins pay_user_col_inst
323 -- and reraise
324 -- 115.107 tvankayl 10-AUG-2004 Added code in install_report_items
325 -- and install_report_mappings to
326 -- include the newly added columns.
327 -- Changes done for Bug 3730528.
328 -- 115.108 divicker 28-SEP-2004 Check install language when deleting
329 -- element type tl or bal type tl rows
330 -- based on a changed certain field
331 -- bug 3280179
332 -- 115.109 divicker 28-SEP-2004 Remove sho err. oops!
333 -- 115.110 divicker 10-NOV-2004 Add TEMP_ACTION_FLAG to HR_S_RFM
334 -- 115.111 divicker 02-FEB-2005 Full col list for org info type
335 -- 115.112 divicker 02-FEB-2005 Remove legislative loop so we
336 -- process everything at the same time
337 -- Trace to show progress
338 -- 115.113 divicker 24-FEB-2005 Minor trace tidy up
339 -- 115.114 divicker 12-APR-2005 Fix for changed base reporting name
340 -- and description columns to TL version
341 -- for balance types and element types
342 -- 115.115 divicker 12-APR-2005 Add element class description fix
343 -- similar to 114
344 -- 115.116 divicker 29-APR-2005 Delete user entities owned by defined
345 -- balances whose balance dimensions
346 -- route_id is going to change.
347 -- Bug 4328538.
348 -- 115.117 mmukherj 03-MAY-2005 Commented out calls to
349 -- translate_ca_ele_dev_df. The updates
350 -- are being done in pycaearn.sql.
351 -- Calling these
352 -- procedure was causing HRGLOBAL to
353 -- error out if hrrunprc restars for some
354 -- other reason.
355 -- 115.118 divicker 06-MAY-2005 Ensure bal dim exists on live if
356 -- checking route id
357 -- 115.119 divicker 12-MAY-2005 remove auto trace in exceptions
358 -- 115.120 divicker 21-JUN-2005 Need to ensure user entity and route
359 -- parameter value rebuild in reib if the
360 -- dimension route has changed
361 -- also if run_dimension_id changes
362 -- 115.121 divicker 24-JUN-2005 Frther fix to 4417200 and del form usa
363 -- 115.122 divicker 26-JUL-2005 Fix for picking up extra route changed
364 -- formula usages
365 -- 115.123 divicker 03-AUG-2005 Programattic debugging via pay patch s
366 -- 115.124 divicker 10-AUG-2005 Extension to debug
367 -- 115.125 divicker 28-SEP-2005 Trace off added to one stmt
368 -- 115.126 divicker 26-OCT-2005 Bug 4701028. Correct urow insert
369 -- behaviour when date track change adds
370 -- a new row with existing low high range
371 -- 115.127 divicker 26-OCT-2005 Fix gscc to_date
372 -- 115.128 divicker 09-NOV-2005 install_urow fix for bug 4725573
373 -- 115.129 divicker 21-NOV-2005 short term fix for 4728513 - make
374 -- update_uid use 50000000
375 -- 115.130 divicker 12-DEC-2005 identical to 115.129
376 -- 115.131 divicker 12-DEC-2005 trash latest balance conditional
377 -- 115.132 divicker 16-DEC-2005 ect fix
378 -- 115.133 divicker 09-JAN-2006 balance related performance fixes
379 -- 115.134 divicker 11-JAN-2006 check on new bal type
380 -- 115.135 divicker 26-JAN-2006 switch order of del comp info and usg
381 -- 115.136 divicker 23-MAR-2006 hrrunprc rerunnability
382 -- 115.137 divicker 30-AUG-2006 Extra col chk on leg field
383 -- 115.138 divicker 01-SEP-2006 fix for 115.137 - bug 5507103
384 -- 115.139 divicker 04-SEP-2006 Remove sho err
385 -- 115.140 divicker 21-SEP-2006 Add index_ff to balance_types fts
386 -- 115.142 divicker 30-OCT-2006 bal feed scale check skip
387 -- 115.143 divicker 13-DEC-2006 support for pay_bal_cat_f_tl
388 -- 120.13.1200000.2 divicker 08-FEB-2007 Sync up to 115.,143 with org_info_type diff
389 -- 1200000.2 divicker 10-JUL-2007 pbtt row fetch cursor was not picking
390 -- up rows changing from null reporting
391 -- name - bug 6146653
392 ------------------------------------------------------------------------------
393
394 PROCEDURE hrrunprc_trace_on is
395 begin
396 if hr_legislation.g_debug_cnt > 0 then
397 hr_utility.trace_on(null, 'HRRUNPRC');
398 end if;
399 end;
403 if hr_legislation.g_debug_cnt > 0 then
400
401 PROCEDURE hrrunprc_trace_off is
402 begin
404 hr_utility.trace_off;
405 end if;
406 end;
407
408 PROCEDURE insert_hr_stu_exceptions (p_table_name varchar2,
409 p_surrogate_id number,
410 p_text varchar2,
411 p_true_key varchar2)
412 IS
413 PRAGMA AUTONOMOUS_TRANSACTION;
414 BEGIN
415
416 insert into HR_STU_EXCEPTIONS
417 (table_name
418 ,surrogate_id
419 ,exception_text
420 ,true_key)
421 select
422 upper(p_table_name)
423 ,p_surrogate_id
424 ,p_text
425 ,p_true_key
426 from dual
427 where not exists
428 (select null
429 from hr_stu_exceptions
430 where p_surrogate_id = surrogate_id
431 and table_name = upper(p_table_name));
432
433 commit;
434 END insert_hr_stu_exceptions;
435
436
437 PROCEDURE munge_sequence (p_seq_name varchar2,
438 p_seq_val number,
439 p_req_val number)
440 IS
441 old_inc number;
442 new_inc number;
443 l_sql_stmt varchar2(2000);
444 l_cursor number;
445 l_ret number;
446 dummy number;
447 l_seq_managed number;
448
449 l_status varchar2(50);
450 l_industry varchar2(50);
451 l_per_owner varchar2(30);
452 l_ret_per boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
453 l_industry, l_per_owner);
454
455 cursor c_seq is
456 select increment_by
457 from all_sequences
458 where sequence_name = p_seq_name
459 and sequence_owner = l_per_owner;
460
461 BEGIN
462
463 -- If a database is sequence managed, we must not modify
464 -- the sequence values. A sequence managed database will
465 -- have entries in the table hr_dm_databases (this table
466 -- is not used for any other purpose), ie. a count of
467 -- zero indicates it is safe to modify sequences.
468
469 SELECT COUNT(*)
470 INTO l_seq_managed
471 FROM hr_dm_databases;
472
473 IF l_seq_managed = 0 THEN
474
475 FOR lp_c_seq in c_seq LOOP
476
477 -- Set new increment_by value to be the difference
478 -- between our current sequence value and the
479 -- required value plus a bit
480 -- Use nvl on p_req_val in case of fresh installs where no live
481 -- data currently present
482 old_inc := lp_c_seq.increment_by;
483 new_inc := nvl(p_req_val, 1) + 30 - p_seq_val;
484
485 -- Alter the sequence to use this inc value
486 l_sql_stmt := 'ALTER SEQUENCE ' || l_per_owner || '.' || p_seq_name ||
487 ' INCREMENT BY '|| new_inc;
488 l_cursor := dbms_sql.open_cursor;
489 dbms_sql.parse(l_cursor, l_sql_stmt, DBMS_SQL.V7);
490 l_ret := dbms_sql.execute(l_cursor);
491 dbms_sql.close_cursor(l_cursor);
492
493 -- Now select the sequence once to bump it past our target
494 l_sql_stmt := 'SELECT ' || l_per_owner || '.' || p_seq_name || '.NEXTVAL FROM DUAL';
495 EXECUTE IMMEDIATE l_sql_stmt INTO dummy;
496
497 -- Reset the sequence to use the old value
498 l_sql_stmt := 'ALTER SEQUENCE ' || l_per_owner || '.' || p_seq_name ||
499 ' INCREMENT BY '|| old_inc;
500 l_cursor := dbms_sql.open_cursor;
501 dbms_sql.parse(l_cursor, l_sql_stmt, DBMS_SQL.V7);
502 l_ret := dbms_sql.execute(l_cursor);
503 dbms_sql.close_cursor(l_cursor);
504
505 END LOOP;
506
507 END IF;
508
509 END munge_sequence;
510
511
512
513 PROCEDURE create_zz_leg_rule
514 IS
515 BEGIN
516
517 hrrunprc_trace_on;
518 hr_utility.trace('enter: zz_leg_rule');
519 hrrunprc_trace_off;
520
521 INSERT INTO hr_application_ownerships
522 (key_name
523 ,product_name
524 ,key_value)
525 SELECT distinct ao.key_name
526 ,ao.product_name
527 ,ao.key_value
528 FROM hr_s_application_ownerships ao
529 ,pay_element_classifications pec
530 WHERE pec.legislation_code = 'ZZ'
531 AND ao.key_name = 'CLASSIFICATION_ID'
532 AND TO_NUMBER(ao.key_value) = pec.classification_id
533 AND NOT EXISTS (SELECT null
534 FROM hr_application_ownerships ao2
535 WHERE ao2.key_name = ao.key_name
536 AND ao2.product_name = ao.product_name
537 AND ao2.key_value = ao.key_value)
538 UNION ALL
539 SELECT distinct ao.key_name
540 ,ao.product_name
541 ,ao.key_value
542 FROM hr_s_application_ownerships ao
543 ,pay_balance_types pbt
544 WHERE pbt.legislation_code = 'ZZ'
545 AND ao.key_name = 'BALANCE_TYPE_ID'
546 AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
547 AND NOT EXISTS (SELECT null
548 FROM hr_application_ownerships ao2
549 WHERE ao2.key_name = ao.key_name
553 SELECT distinct ao.key_name
550 AND ao2.product_name = ao.product_name
551 AND ao2.key_value = ao.key_value)
552 UNION ALL
554 ,ao.product_name
555 ,ao.key_value
556 FROM hr_s_application_ownerships ao
557 ,pay_balance_dimensions pbd
558 WHERE pbd.legislation_code ='ZZ'
559 AND ao.key_name = 'BALANCE_DIMENSION_ID'
560 AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
561 AND NOT EXISTS (SELECT null
562 FROM hr_application_ownerships ao2
563 WHERE ao2.key_name = ao.key_name
564 AND ao2.product_name = ao.product_name
565 AND ao2.key_value = ao.key_value)
566 UNION ALL
567 SELECT distinct ao.key_name
568 ,ao.product_name
569 ,ao.key_value
570 FROM hr_s_application_ownerships ao
571 ,pay_defined_balances pdb
572 WHERE pdb.legislation_code ='ZZ'
573 AND ao.key_name = 'DEFINED_BALANCE_ID'
574 AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
575 AND NOT EXISTS (SELECT null
576 FROM hr_application_ownerships ao2
577 WHERE ao2.key_name = ao.key_name
578 AND ao2.product_name = ao.product_name
579 AND ao2.key_value = ao.key_value)
580 UNION ALL
581 SELECT distinct ao.key_name
582 ,ao.product_name
583 ,ao.key_value
584 FROM hr_s_application_ownerships ao
585 ,ff_routes fr
586 ,pay_balance_dimensions pbd
587 WHERE pbd.legislation_code ='ZZ'
588 AND ao.key_name = 'ROUTE_ID'
589 AND TO_NUMBER(ao.key_value) = fr.route_id
590 AND fr.route_id = pbd.route_id
591 AND NOT EXISTS (SELECT null
592 FROM hr_application_ownerships ao2
593 WHERE ao2.key_name = ao.key_name
594 AND ao2.product_name = ao.product_name
595 AND ao2.key_value = ao.key_value);
596
597 hrrunprc_trace_on;
598 hr_utility.trace('exit: zz_leg_rule');
599 hrrunprc_trace_off;
600
601 END create_zz_leg_rule;
602
603
604 --****************************************************************************
605 -- INSTALLATION PROCEDURE FOR : PER_ASSIGNMENT_STATUS_TYPES
606 --****************************************************************************
607
608 PROCEDURE install_past (p_phase IN number)
609 ------------------------------------------
610 IS
611 -- This procedure does not support the changing of the default flag. It
612 -- can insert new assignment status types, and update child refereences
613 -- to status types, to ensure the integrity of other delivered objects.
614
615 l_inst_rowid rowid; -- rowid of the installed row to update
616 l_null_return varchar2(1); -- used for 'select null' statements
617 l_new_surrogate_key number(15); -- new uid.
618
619 CURSOR stu -- Selects all rows from startup entity
620 IS
621 select user_status
622 , per_system_status
623 , pay_system_status
624 , default_flag
625 , rowid
626 , assignment_status_type_id c_surrogate_key
627 , last_update_date
628 , legislation_code c_leg_code
629 , null c_leg_sgrp
630 from hr_s_assignment_status_types;
631
632 stu_rec stu%ROWTYPE; -- Record for above SELECT
633
634 PROCEDURE crt_exc (exception_type IN varchar2)
635 ----------------------------------------------
636 IS
637 -- Reports any exceptions during the delivery of startup data to
638 -- PER_ASSIGNMENT_STATUS_TYPES
639
640 BEGIN
641 -- When the installation procedures encounter an error that cannot
642 -- be handled, an exception is raised and all work is rolled back
643 -- to the last savepoint. The installation process then continues
644 -- with the next primary key to install. The same exception will
645 -- not be raised more than once.
646
647 rollback to new_primary_key;
648 insert_hr_stu_exceptions('per_assignment_status_types'
649 , stu_rec.c_surrogate_key
650 , exception_type
651 , 'User: ' || stu_rec.user_status ||
652 ' PER: ' || stu_rec.per_system_status ||
653 ' PAY: ' || stu_rec.pay_system_status);
654
655 END crt_exc;
656
657
658 PROCEDURE check_next_sequence
659 -----------------------------
660 IS
661
662 v_sequence_number number(15);
663 v_min_delivered number(15);
664 v_max_delivered number(15);
665 v_max_live number(15);
666 cnt number(15);
667
668 -- Surrogate id conflicts may arise from two scenario's:
669 -- 1. Where the newly select sequence value conflicts with values
670 -- in the STU tables.
671 -- 2. Where selected surrogate keys, from the installed tables,
672 -- conflict with other rows in the STU tables.
673 --
674 -- Both of the above scenario's are tested for.
675 -- The first is a simple match, where if a value is detected in the
676 -- STU tables and the installed tables then a conflict is detected. In
677 -- This instance all STU surrogate keys, for this table, are updated.
678 -- The second is tested for using the sequences.
682 -- delivered tables, and child rows may be totally invalidated.
679 -- If the next value from the live sequence is within the range of
680 -- delivered surrogate id's then the live sequence must be incremented.
681 -- If no action is taken, then duplicates may be introduced into the
683
684 BEGIN
685
686
687 BEGIN --check that the installed id's will not conflict
688 --with the delivered values
689
690 select count(*)
691 into cnt
692 from hr_s_assignment_status_types;
693
694 If cnt=0 then return; end if;
695
696 select distinct null
697 into l_null_return
698 from per_assignment_status_types a
699 where exists
700 (select null
701 from hr_s_assignment_status_types b
702 where a.assignment_status_type_id=b.assignment_status_type_id
703 );
704
705 --conflict may exist
706 --update all assignment_status_type_id's to remove conflict
707
708 update /*+NO_INDEX*/ hr_s_assignment_status_types
709 set assignment_status_type_id=assignment_status_type_id - 50000000;
710
711 update /*+NO_INDEX*/ hr_s_status_processing_rules_f
712 set assignment_status_type_id=assignment_status_type_id - 50000000;
713
714 update hr_s_application_ownerships
715 set key_value = key_value - 50000000
716 where key_name = 'ASSIGNMENT_STATUS_TYPE_ID';
717
718 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
719
720 END; --check of assignment_status_type_id
721
722 select min(assignment_status_type_id) - (count(*) *3)
723 , max(assignment_status_type_id) + (count(*) *3)
724 into v_min_delivered
725 , v_max_delivered
726 from hr_s_assignment_status_types;
727
728 select max(assignment_status_type_id)
729 into v_max_live
730 from per_assignment_status_types;
731
732 select per_assignment_status_types_s.nextval
733 into v_sequence_number
734 from dual;
735
736 -- bug 603778
737 -- There is a problem with delivery mechanism for
738 -- per_assignment_status_types. We are delivering one
739 -- legislative row for R11 with an assignment_status_type_id of
740 -- 50002. Existing code appears to assume that a row will be delivered
741 -- with an id of 1. This workaround will bump up the sequence
742 -- past 50002 so that the p.a.s.t. constraint will not be
743 -- violated.
744
745 if v_min_delivered > 1 then v_min_delivered := 1; end if;
746
747 --
748
749 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
750 OR (v_sequence_number < v_max_live) THEN
751
752 IF v_max_live > v_max_delivered THEN
753
754 hr_legislation.munge_sequence('PER_ASSIGNMENT_STATUS_TYPES_S',
755 v_sequence_number,
756 v_max_live);
757 ELSE
758
759 hr_legislation.munge_sequence('PER_ASSIGNMENT_STATUS_TYPES_S',
760 v_sequence_number,
761 v_max_delivered);
762 END IF;
763 END IF;
764
765 END check_next_sequence;
766
767
768 PROCEDURE update_uid
769 --------------------
770 IS
771
772 BEGIN
773
774 BEGIN
775 select distinct assignment_status_type_id
776 into l_new_surrogate_key
777 from per_assignment_status_types
778 where user_status = stu_rec.user_status
779 and per_system_status = stu_rec.per_system_status
780 and business_group_id is null
781 and ( (pay_system_status is null and stu_rec.pay_system_status is null)
782 or (pay_system_status = stu_rec.pay_system_status) )
783 and ( (legislation_code is null and stu_rec.c_leg_code is null)
784 or (legislation_code = stu_rec.c_leg_code) );
785
786 EXCEPTION WHEN NO_DATA_FOUND THEN
787
788 select per_assignment_status_types_s.nextval
789 into l_new_surrogate_key
790 from dual;
791 WHEN TOO_MANY_ROWS THEN
792 hrrunprc_trace_on;
793 hr_utility.trace('sel per_assignment_status_types TMR');
794
795 hr_utility.trace('user_status ' ||
796 stu_rec.user_status);
797 hr_utility.trace('per_system_status ' ||
798 stu_rec.per_system_status);
799 hr_utility.trace('pay_system_status ' ||
800 stu_rec.pay_system_status);
801 hr_utility.trace(':lc: ' || ':' ||
802 stu_rec.c_leg_code || ':');
803 hrrunprc_trace_off;
804 raise;
805 END;
806
807 update hr_s_assignment_status_types
808 set assignment_status_type_id = l_new_surrogate_key
809 where assignment_status_type_id = stu_rec.c_surrogate_key;
810
811 update hr_s_status_processing_rules_f
812 set assignment_status_type_id = l_new_surrogate_key
816 set key_value = to_char(l_new_surrogate_key)
813 where assignment_status_type_id = stu_rec.c_surrogate_key;
814
815 update hr_s_application_ownerships
817 where key_value = to_char(stu_rec.c_surrogate_key)
818 and key_name = 'ASSIGNMENT_STATUS_TYPE_ID';
819
820 END update_uid;
821
822 PROCEDURE remove (subject IN varchar2)
823 --------------------------------------
824 IS
825 -- Remove a row from either the startup/delivered tables (D) or the installed
826 -- tables (I) as specified by the parameter.
827
828 BEGIN
829
830 IF subject = 'D' THEN
831 delete from hr_s_assignment_status_types
832 where rowid = stu_rec.rowid;
833 ELSE
834 IF p_phase = 1 THEN return; END IF;
835 delete from per_assignment_status_types
836 where rowid = l_inst_rowid;
837 END IF;
838
839 END remove;
840
841 FUNCTION valid_ownership RETURN BOOLEAN
842 ---------------------------------------
843 IS
844 -- Test ownership of this current row
845
846 BEGIN
847 -- This routine only operates in phase 1. Rows are present in the
848 -- table hr_application_ownerships in the delivery account, which
849 -- dictate which products a piece of data is used for. If the query
850 -- returns a row, then this data is required, and the function will
851 -- return true. If no rows are returned and an exception is raised,
852 -- then this row is not required and may be deleted from the delivery
853 -- tables.
854
855 -- If legislation code and subgroup code are included on the delivery
856 -- tables, a check must be made to determine if the data is defined for
857 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
858 -- installation.
859
860 -- A return code of TRUE indicates that the row is required.
861
862 -- The exception is raised within this procedure if no rows are returned
863 -- in this select statement. If no rows are returned then one of the
864 -- following is true:
865 -- 1. No ownership parameters are defined.
866 -- 2. The products, for which owning parameters are defined, are not
867 -- installed with as status of 'I'.
868 -- 3. The data is defined for a legislation subgroup that is not active.
869
870 IF p_phase <> 1 THEN return TRUE; END IF;
871
872 -- Cause an exception to be raised if this row is not needed
873
874 select null
875 into l_null_return
876 from dual
877 where exists (
878 select null
879 from hr_s_application_ownerships a
880 , fnd_product_installations b
881 , fnd_application c
882 where a.key_name = 'ASSIGNMENT_STATUS_TYPE_ID'
883 and a.key_value = l_new_surrogate_key
884 and a.product_name = c.application_short_name
885 and c.application_id = b.application_id
886 and ((b.status = 'I' and c.application_short_name <> 'PQP')
887 or
888 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
889
890
891 return TRUE; -- Row is required
892
893 EXCEPTION WHEN NO_DATA_FOUND THEN
894
895 -- The row is not needed for any installed product. Remove it from the
896 -- delivery tables and return false to indicate it is not required.
897
898 remove('D');
899 return FALSE; -- Row not required
900
901 END valid_ownership;
902
903 PROCEDURE transfer_row
904 ----------------------
905 IS
906 -- Check if a delivered row is needed and insert into the
907 -- live tables if it is.
908
909 l_inst_row number(1);
910
911 BEGIN
912 l_inst_row := null;
913
914 BEGIN
915
916 -- Perform a check to see if the primary key has been created within
917 -- a visible business group. Ie: the business group is for the same
918 -- legislation as the delivered row, or the delivered row has a null
919 -- legislation. If no rows are returned then the primary key has not
920 -- already been created by a user.
921
922
923 select distinct 1
924 into l_inst_row
925 from per_assignment_status_types
926 where user_status = stu_rec.user_status
927 and per_system_status = stu_Rec.per_system_status
928 and business_group_id is null
929 and ( (pay_system_status is null and stu_rec.pay_system_status is null)
930 or (pay_system_status = stu_rec.pay_system_status) )
931 and ( (legislation_code is null and stu_rec.c_leg_code is null)
932 or (legislation_code = stu_rec.c_leg_code) );
933
934 -- If the flow drops to here, then this row is not needed.
935
936 remove('D');
937
938 EXCEPTION WHEN NO_DATA_FOUND THEN
939
940
941 IF stu_rec.default_flag = 'Y' THEN
942
943 BEGIN
944
945 select null
946 into l_null_return
947 from dual
948 where exists (
949
950 select null
951 from per_assignment_status_types a
952 , per_business_groups b
953 where a.default_flag = 'Y'
954 and a.per_system_status = stu_rec.per_system_status
958 nvl(stu_rec.c_leg_code,b.legislation_code) )
955 and ( (a.business_group_id is not null
956 and b.business_group_id = a.business_group_id
957 and b.legislation_code =
959 or (a.business_group_id is null
960 and nvl(a.legislation_code,'X') =
961 nvl(stu_rec.c_leg_code,'X') ) ));
962
963 crt_exc('This PER_SYSTEM_TYPE has a default defined');
964
965 EXCEPTION WHEN NO_DATA_FOUND THEN
966
967 null;
968
969 END;
970
971 END IF;
972
973 END;
974
975 -- When the procedure is called in phase 1, there is no need to
976 -- actually perform the transfer from the delivery tables into the
977 -- live. Hence if phase = 1 control is returned to the calling
978 -- procedure and the next row is returned.
979
980 IF p_phase = 1 THEN return; END IF;
981
982 -- If the procedure is called in phase 2, then the live row is updated
983 -- with the values on the delivered row.
984
985 -- The routine check_parents validates foreign key references and
986 -- ensures referential integrity. The routine checks to see if the
987 -- parents of a given row have been transfered to the live tables.
988
989 -- This may only be called in phase two since in phase one all
990 -- parent rows will remain in the delivery tables.
991
992 -- The local variable 'l_inst_rowid' is used to decide if there is
993 -- a live row present or not. If this variable is not null it will
994 -- contain the rowid of the installed row to be updated.
995
996 -- The last step of the transfer, in phase 2, is to delete the now
997 -- transfered row from the delivery tables.
998
999 IF l_inst_row is null THEN
1000
1001 insert into per_assignment_status_types
1002 (ASSIGNMENT_STATUS_TYPE_ID
1003 ,BUSINESS_GROUP_ID
1004 ,LEGISLATION_CODE
1005 ,ACTIVE_FLAG
1006 ,DEFAULT_FLAG
1007 ,PRIMARY_FLAG
1008 ,USER_STATUS
1009 ,PAY_SYSTEM_STATUS
1010 ,PER_SYSTEM_STATUS
1011 ,LAST_UPDATE_DATE
1012 ,LAST_UPDATED_BY
1013 ,LAST_UPDATE_LOGIN
1014 ,CREATED_BY
1015 ,CREATION_DATE)
1016 select ASSIGNMENT_STATUS_TYPE_ID
1017 ,BUSINESS_GROUP_ID
1018 ,LEGISLATION_CODE
1019 ,ACTIVE_FLAG
1020 ,DEFAULT_FLAG
1021 ,PRIMARY_FLAG
1022 ,USER_STATUS
1023 ,PAY_SYSTEM_STATUS
1024 ,PER_SYSTEM_STATUS
1025 ,LAST_UPDATE_DATE
1026 ,LAST_UPDATED_BY
1027 ,LAST_UPDATE_LOGIN
1028 ,CREATED_BY
1029 ,CREATION_DATE
1030 from hr_s_assignment_status_types
1031 where rowid = stu_rec.rowid;
1032
1033 END IF;
1034
1035 -- Delete delivered row now it has been installed
1036
1037 remove('D');
1038
1039 END transfer_row;
1040
1041 BEGIN
1042
1043 -- This is the main loop to perform the installation logic. A cursor
1044 -- is opened to control the loop, and each row returned is placed
1045 -- into a record defined within the main procedure so each sub
1046 -- procedure has full access to all returrned columns. For each
1047 -- new row returned, a new savepoint is declared. If at any time
1048 -- the row is in error a rollback is performed to the savepoint
1049 -- and the next row is returned. Ownership details are checked and
1050 -- if the row is required then the surrogate id is updated and the
1051 -- main transfer logic is called.
1052
1053 IF p_phase = 1 THEN check_next_sequence; END IF;
1054
1055 FOR delivered IN stu LOOP
1056
1057 savepoint new_primary_key;
1058
1059 -- Make all cursor columns available to all procedures
1060
1061 stu_rec := delivered;
1062
1063 IF p_phase = 1 THEN update_uid; END IF;
1064
1065 -- Test the row ownerships for the current row
1066
1067 IF valid_ownership THEN
1068
1069 transfer_row;
1070
1071 END IF;
1072
1073 END LOOP;
1074
1075 END install_past;
1076
1077 --****************************************************************************
1078 -- INSTALLATION PROCEDURE FOR : PAY_BALANCE_CATEGORIES
1079 --****************************************************************************
1080 PROCEDURE install_bal_categories (p_phase IN NUMBER)
1081 ----------------------------------------------------
1082 IS
1083 row_in_error exception;
1084 l_current_proc varchar2(80) := 'hr_legislation.install_bal_categories';
1085 l_new_balance_category_id number(15);
1086 l_null_return varchar2(1);
1087
1088 CURSOR c_distinct
1089 IS
1090 select max(effective_end_date) c_end
1091 , BALANCE_CATEGORY_ID c_surrogate_key
1092 , CATEGORY_NAME c_true_key
1093 , legislation_code
1094 from hr_s_balance_categories_f
1095 group by BALANCE_CATEGORY_ID
1096 , CATEGORY_NAME
1097 , legislation_code;
1098
1099 CURSOR c_each_row (pc_bal_cat_id varchar2)
1100 IS
1101 -- The primary key has already been selected using the above cursor.
1102 -- This cursor accepts the primary key as a parameter and selects all
1106 from hr_s_balance_categories_f
1103 -- date effective rows for it.
1104
1105 select *
1107 where BALANCE_CATEGORY_ID = pc_bal_cat_id;
1108
1109 r_distinct c_distinct%ROWTYPE;
1110 r_each_row c_each_row%ROWTYPE;
1111
1112 PROCEDURE check_next_sequence
1113 -----------------------------
1114 IS
1115
1116 v_sequence_number number(15);
1117 v_min_delivered number(15);
1118 v_max_delivered number(15);
1119 v_max_live number(15);
1120 cnt number(15);
1121
1122 BEGIN
1123
1124 BEGIN
1125
1126 select count(*)
1127 into cnt
1128 from hr_s_balance_categories_f;
1129
1130 If cnt=0 then return; end if;
1131
1132 select distinct null
1133 into l_null_return
1134 from pay_balance_categories_f a
1135 where exists
1136 (select null
1137 from hr_s_balance_categories_f b
1138 where a.BALANCE_CATEGORY_ID = b.BALANCE_CATEGORY_ID
1139 );
1140
1141 --conflict may exist
1142 --update all user_row_id's to remove conflict
1143
1144 update /*+NO_INDEX*/ hr_s_balance_categories_f
1145 set BALANCE_CATEGORY_ID = BALANCE_CATEGORY_ID - 50000000;
1146
1147 update /*+NO_INDEX*/ hr_s_balance_types
1148 set BALANCE_CATEGORY_ID = BALANCE_CATEGORY_ID - 50000000;
1149
1150 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1151
1152 END;
1153
1154 select min(BALANCE_CATEGORY_ID) - (count(*) *3)
1155 , max(BALANCE_CATEGORY_ID) + (count(*) *3)
1156 into v_min_delivered
1157 , v_max_delivered
1158 from hr_s_balance_categories_f;
1159
1160 select max(BALANCE_CATEGORY_ID)
1161 into v_max_live
1162 from pay_balance_categories_f;
1163
1164 select pay_balance_categories_s.nextval
1165 into v_sequence_number
1166 from dual;
1167
1168 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
1169 OR (v_sequence_number < v_max_live) THEN
1170
1171 IF v_max_live > v_max_delivered THEN
1172
1173 hr_legislation.munge_sequence('PAY_BALANCE_CATEGORIES_S',
1174 v_sequence_number,
1175 v_max_live);
1176 ELSE
1177
1178 hr_legislation.munge_sequence('PAY_BALANCE_CATEGORIES_S',
1179 v_sequence_number,
1180 v_max_delivered);
1181 END IF;
1182 END IF;
1183
1184 END check_next_sequence;
1185
1186 PROCEDURE crt_exc (exception_type IN varchar2)
1187 ----------------------------------------------
1188 IS
1189 BEGIN
1190
1191 -- When the installation procedures encounter an error that cannot
1192 -- be handled, an exception is raised and all work is rolled back
1193 -- to the last savepoint. The installation process then continues
1194 -- with the next primary key to install. The same exception will
1195 -- not be raised more than once.
1196
1197 rollback to new_category_name;
1198
1199 insert_hr_stu_exceptions('pay_balance_categories_f'
1200 , r_distinct.c_surrogate_key
1201 , exception_type
1202 , r_distinct.c_true_key);
1203
1204 END crt_exc;
1205
1206 PROCEDURE remove (v_id IN number)
1207 ---------------------------------
1208 IS
1209 -- subprogram to delete a row from the delivery tables, and all child
1210 -- application ownership rows
1211
1212 BEGIN
1213
1214 delete from hr_s_balance_categories_f
1215 where BALANCE_CATEGORY_ID = v_id;
1216
1217 END remove;
1218
1219 PROCEDURE update_uid
1220 --------------------
1221 IS
1222
1223 BEGIN
1224
1225 BEGIN
1226
1227 select distinct BALANCE_CATEGORY_ID
1228 into l_new_balance_category_id
1229 from pay_balance_categories_f
1230 where category_name = r_distinct.c_true_key
1231 and business_Group_id is null
1232 and nvl(legislation_code, 'x') = nvl(r_distinct.legislation_code,'x');
1233
1234 EXCEPTION WHEN NO_DATA_FOUND THEN
1235
1236 select pay_balance_categories_s.nextval
1237 into l_new_balance_category_id
1238 from dual;
1239 --
1240 WHEN TOO_MANY_ROWS THEN
1241
1242 crt_exc('Non unique balance category ID for category name ' ||
1243 r_distinct.c_true_key);
1244 --
1245 END;
1246
1247 update hr_s_balance_categories_f
1248 set balance_category_id = l_new_balance_category_id
1249 where balance_category_id = r_distinct.c_surrogate_key;
1250
1251 update hr_s_balance_types
1255 END update_uid;
1252 set balance_category_id = l_new_balance_category_id
1253 where balance_category_id = r_distinct.c_surrogate_key;
1254
1256
1257 FUNCTION check_parents RETURN BOOLEAN
1258 -------------------------------------
1259 IS
1260
1261 BEGIN
1262
1263 return TRUE;
1264
1265 END check_parents;
1266
1267 FUNCTION valid_ownership RETURN BOOLEAN
1268 ---------------------------------------
1269 IS
1270 -- Test ownership of this current row
1271 BEGIN
1272
1273 BEGIN
1274
1275 select distinct null
1276 into l_null_return
1277 from pay_balance_categories_f a
1278 where a.category_name = r_distinct.c_true_key
1279 and a.business_group_id is not null
1280 and exists (select null from per_business_groups b
1281 where b.business_group_id = a.business_group_id
1282 and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
1283
1284 crt_exc('Row already created in a business group');
1285
1286 return FALSE; -- Indicates this row is not to be transferred
1287
1288 EXCEPTION WHEN NO_DATA_FOUND THEN
1289 null;
1290
1291 END;
1292
1293 -- Now perform a check to see if this primary key has been installed
1294 -- with a legislation code that would make it visible at the same time
1295 -- as this row. Ie: if any legislation code is null within the set of
1296 -- returned rows, then the transfer may not go ahead. If no rows are
1297 -- returned then the delivered row is fine.
1298
1299 BEGIN
1300 --
1301 select distinct null
1302 into l_null_return
1303 from pay_balance_categories_f
1304 where category_name = r_distinct.c_true_key
1305 and nvl(legislation_code,'x') <>
1306 nvl(r_distinct.legislation_code,'x')
1307 and (legislation_code is null
1308 or r_distinct.legislation_code is null);
1309
1310 crt_exc('Row already created for a visible legislation');
1311 return FALSE;
1312
1313 EXCEPTION WHEN NO_DATA_FOUND THEN
1314 null;
1315
1316 END;
1317
1318 IF p_phase <> 1 THEN return TRUE; END IF;
1319
1320 return TRUE; -- Indicates row is required
1321
1322 EXCEPTION WHEN NO_DATA_FOUND THEN
1323
1324 remove(r_distinct.c_surrogate_key);
1325
1326 return FALSE; -- Indicates row not needed
1327
1328 END valid_ownership;
1329
1330 BEGIN
1331 -- Two loops are used here. The main loop which select distinct primary
1332 -- key rows and an inner loop which selects all date effective rows for the
1333 -- primary key. The inner loop is only required in phase two, since only
1334 IF p_phase = 1 THEN check_next_sequence; END IF;
1335
1336 FOR category_names IN c_distinct LOOP
1337
1338 savepoint new_category_name;
1339 r_distinct := category_names;
1340
1341 BEGIN
1342
1343 IF valid_ownership THEN
1344 -- This row is wanted
1345 IF p_phase = 1 THEN
1346 -- Get new surrogate id and update child references
1347 update_uid;
1348 ELSE
1349 -- Phase = 2
1350 delete from pay_balance_categories_f
1351 where balance_category_id = r_distinct.c_surrogate_key;
1352
1353 FOR each_row IN c_each_row(r_distinct.c_surrogate_key) LOOP
1354 r_each_row := each_row;
1355
1356 IF NOT check_parents THEN RAISE row_in_error; END IF;
1357
1358 BEGIN
1359 insert into pay_balance_categories_f
1360 ( BALANCE_CATEGORY_ID
1361 ,CATEGORY_NAME
1362 ,EFFECTIVE_START_DATE
1363 ,EFFECTIVE_END_DATE
1364 ,LEGISLATION_CODE
1365 ,BUSINESS_GROUP_ID
1366 ,SAVE_RUN_BALANCE_ENABLED
1367 ,PBC_INFORMATION_CATEGORY
1368 ,PBC_INFORMATION1
1369 ,PBC_INFORMATION2
1370 ,PBC_INFORMATION3
1371 ,PBC_INFORMATION4
1372 ,PBC_INFORMATION5
1373 ,PBC_INFORMATION6
1374 ,PBC_INFORMATION7
1375 ,PBC_INFORMATION8
1376 ,PBC_INFORMATION9
1377 ,PBC_INFORMATION10
1378 ,PBC_INFORMATION11
1379 ,PBC_INFORMATION12
1380 ,PBC_INFORMATION13
1381 ,PBC_INFORMATION14
1382 ,PBC_INFORMATION15
1383 ,PBC_INFORMATION16
1384 ,PBC_INFORMATION17
1385 ,PBC_INFORMATION18
1389 ,PBC_INFORMATION22
1386 ,PBC_INFORMATION19
1387 ,PBC_INFORMATION20
1388 ,PBC_INFORMATION21
1390 ,PBC_INFORMATION23
1391 ,PBC_INFORMATION24
1392 ,PBC_INFORMATION25
1393 ,PBC_INFORMATION26
1394 ,PBC_INFORMATION27
1395 ,PBC_INFORMATION28
1396 ,PBC_INFORMATION29
1397 ,PBC_INFORMATION30
1398 ,LAST_UPDATE_DATE
1399 ,LAST_UPDATED_BY
1400 ,LAST_UPDATE_LOGIN
1401 ,CREATED_BY
1402 ,CREATION_DATE
1403 ,OBJECT_VERSION_NUMBER
1404 ,USER_CATEGORY_NAME)
1405 values
1406 (each_row.BALANCE_CATEGORY_ID
1407 ,each_row.CATEGORY_NAME
1408 ,each_row.EFFECTIVE_START_DATE
1409 ,each_row.EFFECTIVE_END_DATE
1410 ,each_row.LEGISLATION_CODE
1411 ,each_row.BUSINESS_GROUP_ID
1412 ,each_row.SAVE_RUN_BALANCE_ENABLED
1413 ,each_row.PBC_INFORMATION_CATEGORY
1414 ,each_row.PBC_INFORMATION1
1415 ,each_row.PBC_INFORMATION2
1416 ,each_row.PBC_INFORMATION3
1417 ,each_row.PBC_INFORMATION4
1418 ,each_row.PBC_INFORMATION5
1419 ,each_row.PBC_INFORMATION6
1420 ,each_row.PBC_INFORMATION7
1421 ,each_row.PBC_INFORMATION8
1422 ,each_row.PBC_INFORMATION9
1423 ,each_row.PBC_INFORMATION10
1424 ,each_row.PBC_INFORMATION11
1425 ,each_row.PBC_INFORMATION12
1426 ,each_row.PBC_INFORMATION13
1427 ,each_row.PBC_INFORMATION14
1428 ,each_row.PBC_INFORMATION15
1429 ,each_row.PBC_INFORMATION16
1430 ,each_row.PBC_INFORMATION17
1431 ,each_row.PBC_INFORMATION18
1432 ,each_row.PBC_INFORMATION19
1433 ,each_row.PBC_INFORMATION20
1434 ,each_row.PBC_INFORMATION21
1435 ,each_row.PBC_INFORMATION22
1436 ,each_row.PBC_INFORMATION23
1437 ,each_row.PBC_INFORMATION24
1438 ,each_row.PBC_INFORMATION25
1439 ,each_row.PBC_INFORMATION26
1440 ,each_row.PBC_INFORMATION27
1441 ,each_row.PBC_INFORMATION28
1442 ,each_row.PBC_INFORMATION29
1443 ,each_row.PBC_INFORMATION30
1444 ,each_row.LAST_UPDATE_DATE
1445 ,each_row.LAST_UPDATED_BY
1446 ,each_row.LAST_UPDATE_LOGIN
1447 ,each_row.CREATED_BY
1448 ,each_row.CREATION_DATE
1449 ,each_row.OBJECT_VERSION_NUMBER
1450 ,nvl(each_row.USER_CATEGORY_NAME,
1451 each_row.CATEGORY_NAME));
1452 EXCEPTION WHEN OTHERS THEN
1453 hrrunprc_trace_on;
1454 hr_utility.trace('ins pay_balance_categories');
1455 hr_utility.trace('cat id: ' ||
1456 to_char(each_row.BALANCE_CATEGORY_ID));
1457 hr_utility.trace('cat name ' ||
1458 each_row.CATEGORY_NAME);
1459 hr_utility.trace(':lc:bg: ' || ':' ||
1460 each_row.LEGISLATION_CODE || ':' ||
1461 to_char(each_row.BUSINESS_GROUP_ID) || ':');
1462 hrrunprc_trace_off;
1463 raise;
1464 END;
1465
1466 END LOOP each_row;
1467
1468 remove(r_distinct.c_surrogate_key);
1469
1470 END IF; -- End phase checking
1471
1472 END IF; --(valid ownership test)
1473
1474 EXCEPTION WHEN row_in_error THEN
1475 rollback to new_category_name;
1476
1477 END;
1478
1479 END LOOP category_names;
1480
1481 END install_bal_categories;
1482
1483 --****************************************************************************
1484 -- INSTALLATION PROCEDURE FOR : PAY_BALANCE_TYPES
1485 --****************************************************************************
1486
1487 PROCEDURE install_bal_types (p_phase IN number)
1488 -----------------------------------------------
1489 IS
1490
1491 -- Install procedure to insert required balance types/defined balances
1492 -- and balance classifications.
1493
1494 -- The child rows of defined balances and classifications, are only
1495 -- installed if the balance type itself has changed.
1496
1500 -- this key is composed of two surrogate keys. If the balance type is to
1497 -- To protect integrity, the defined_balance_id must be derived from the
1498 -- live table. Therfore the values of balance_type_id and dimension_id
1499 -- must be relied upon as a true composite key. This is not desirable as
1501 -- be installed, then a test will be performed and only those defined
1502 -- balances not already installed will be inserted. This logic is used
1503 -- since the only not null columns on defined balances are the two
1504 -- foreign keys that comprise the true key.
1505
1506 l_null_return varchar2(1); -- For 'select null' statements
1507 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
1508 l_bal_class_id pay_balance_classifications.balance_classification_id%type;
1509
1510 CURSOR stu -- Selects all rows from startup entity
1511 IS
1512
1513 select balance_name c_true_key
1514 , balance_type_id c_surrogate_key
1515 , legislation_code c_leg_code
1516 , legislation_subgroup c_leg_sgrp
1517 , assignment_remuneration_flag
1518 , currency_code
1519 , balance_uom
1520 , reporting_name
1521 , jurisdiction_level
1522 , tax_type
1523 , last_update_date
1524 , last_updated_by
1525 , last_update_login
1526 , created_by
1527 , creation_date
1528 , input_value_id
1529 , base_balance_type_id
1530 , balance_category_id
1531 , rowid
1532 , new_balance_type_flag
1533 from hr_s_balance_types;
1534
1535 CURSOR class (bal_type_id number)
1536 IS
1537 -- Cursor for installation of classification rules
1538 -- ADDED DISTINCT so that ldts that contains same bal_class
1539 -- for same legislation wont trigger PK error
1540 select distinct *
1541 from hr_s_balance_classifications hsbc
1542 where balance_type_id = bal_type_id
1543 and not exists
1544 ( select 1
1545 from pay_balance_classifications pbc
1546 where nvl(hsbc.business_group_id, -1) = nvl(pbc.business_group_id, -1)
1547 and hsbc.legislation_code = pbc.legislation_code
1548 and hsbc.balance_type_id = pbc.balance_type_id
1549 and hsbc.classification_id = pbc.classification_id
1550 and hsbc.scale = pbc.scale
1551 and nvl(hsbc.legislation_subgroup, 'X') = nvl(pbc.legislation_subgroup, 'X'));
1552
1553 CURSOR defined (bal_type_id number)
1554 IS
1555 -- Cursor for installation of child 'defined balances'
1556 -- ADDED DISTINCT so that ldts that contains same defined
1557 -- balance for same legislation wont trigger PK error
1558 select distinct *
1559 from hr_s_defined_balances
1560 where balance_type_id = bal_type_id;
1561
1562 CURSOR feed (bal_type_id number)
1563 IS
1564 -- Cursor to install child balance feeds
1565 -- that don't already exist in pay_balance_feeds_f
1566
1567 select distinct *
1568 from hr_s_balance_feeds_f hrs
1569 where hrs.balance_type_id = bal_type_id
1570 and not exists (
1571 select null
1572 from pay_balance_feeds_f pbf
1573 where pbf.balance_type_id = hrs.balance_type_id
1574 and pbf.input_value_id = hrs.input_value_id
1575 and pbf.effective_start_date = hrs.effective_start_date
1576 and pbf.effective_end_date = hrs.effective_end_date);
1577
1578 stu_rec stu%ROWTYPE; -- Cursor for earlier select
1579
1580
1581 PROCEDURE check_next_sequence
1582 -----------------------------
1583 IS
1584
1585 v_sequence_number number(15);
1586 v_min_delivered number(15);
1587 v_max_delivered number(15);
1588 v_max_live number(15);
1589 cnt number(15);
1590
1591 -- Surrogate id conflicts may arise from two scenario's:
1592 -- 1. Where the newly select sequence value conflicts with values
1593 -- in the STU tables.
1594 -- 2. Where selected surrogate keys, from the installed tables,
1595 -- conflict with other rows in the STU tables.
1596 --
1597 -- Both of the above scenario's are tested for.
1598 -- The first is a simple match, where if a value is detected in the
1599 -- STU tables and the installed tables then a conflict is detected. In
1600 -- This instance all STU surrogate keys, for this table, are updated.
1601 -- The second is tested for using the sequences.
1602 -- If the next value from the live sequence is within the range of
1603 -- delivered surrogate id's then the live sequence must be incremented.
1604 -- If no action is taken, then duplicates may be introduced into the
1605 -- delivered tables, and child rows may be totally invalidated.
1606
1607 BEGIN
1608
1609 BEGIN --check that the installed id's will not conflict
1610 --with the delivered values
1611
1612 select count(*)
1613 into cnt
1614 from hr_s_balance_types;
1615
1616 If cnt=0 then return; end if;
1617
1618
1619 select distinct null
1620 into l_null_return
1621 from pay_balance_types a
1622 where exists
1623 (select null
1624 from hr_s_balance_types b
1625 where a.balance_type_id = b.balance_type_id
1629 --update all balance_type_id's to remove conflict
1626 );
1627
1628 --conflict may exist
1630
1631 update /*+NO_INDEX*/ hr_s_BALANCE_CLASSIFICATIONS
1632 set balance_type_id = balance_type_id - 50000000;
1633
1634 update /*+NO_INDEX*/ hr_s_BALANCE_FEEDS_F
1635 set balance_type_id = balance_type_id - 50000000;
1636
1637 update /*+NO_INDEX*/ hr_s_BALANCE_TYPES
1638 set balance_type_id = balance_type_id - 50000000;
1639
1640 update /*+NO_INDEX*/ hr_s_BALANCE_TYPES
1641 set base_balance_type_id = base_balance_type_id - 50000000;
1642
1643 update /*+NO_INDEX*/ hr_s_DEFINED_BALANCES
1644 set balance_type_id = balance_type_id - 50000000;
1645
1646 update hr_s_application_ownerships
1647 set key_value = key_value - 50000000
1648 where key_name = 'BALANCE_TYPE_ID';
1649
1650 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1651
1652 END; --check of balance_type_id
1653
1654
1655 BEGIN --check that the installed defined balance id's will
1656 --not conflict with the delivered values
1657
1658 select count(*)
1659 into cnt
1660 from hr_s_defined_balances;
1661
1662 If cnt=0 then return; end if;
1663
1664
1665 select distinct null
1666 into l_null_return
1667 from pay_defined_balances a
1668 where exists
1669 (select null
1670 from hr_s_defined_balances b
1671 where a.defined_balance_id = b.defined_balance_id
1672 );
1673
1674 --conflict may exist
1675 --update all balance_type_id's to remove conflict
1676
1677 update /*+NO_INDEX*/ hr_s_DEFINED_BALANCES
1678 set defined_balance_id = defined_balance_id - 50000000;
1679
1680 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1681
1682 END; --check of balance_type_id
1683
1684 select min(balance_type_id) - (count(*) *3)
1685 , max(balance_type_id) + (count(*) *3)
1686 into v_min_delivered
1687 , v_max_delivered
1688 from hr_s_balance_types;
1689
1690 select max(balance_type_id)
1691 into v_max_live
1692 from pay_balance_types;
1693
1694 select pay_balance_types_s.nextval
1695 into v_sequence_number
1696 from dual;
1697
1698 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
1699 OR (v_sequence_number < v_max_live) THEN
1700
1701 IF v_max_live > v_max_delivered THEN
1702
1703 hr_legislation.munge_sequence('PAY_BALANCE_TYPES_S',
1704 v_sequence_number,
1705 v_max_live);
1706 ELSE
1707
1708 hr_legislation.munge_sequence('PAY_BALANCE_TYPES_S',
1709 v_sequence_number,
1710 v_max_delivered);
1711 END IF;
1712 END IF;
1713
1714
1715 select min(defined_balance_id) - (count(*) *3)
1716 , max(defined_balance_id) +(count(*) *3)
1717 into v_min_delivered
1718 , v_max_delivered
1719 from hr_s_defined_balances;
1720
1721 select pay_defined_balances_s.nextval
1722 into v_sequence_number
1723 from dual;
1724
1725 IF v_sequence_number BETWEEN v_min_delivered AND v_max_delivered THEN
1726
1727 hr_legislation.munge_sequence('PAY_DEFINED_BALANCES_S',
1728 v_sequence_number,
1729 v_max_delivered);
1730
1731 END IF;
1732
1733 END check_next_sequence;
1734
1735
1736 PROCEDURE crt_exc (exception_type IN varchar2)
1737 ----------------------------------------------
1738 IS
1739 -- Reports any exceptions during the delivery of startup data to
1740 -- PER_ASSIGNMENT_STATUS_TYPES. See crt_exc in procedure install_past
1741 -- for further, generic operational details.
1742
1743 BEGIN
1744
1745 rollback to new_balance_name;
1746 insert_hr_stu_exceptions('pay_balance_types'
1747 , stu_rec.c_surrogate_key
1748 , exception_type
1749 , stu_rec.c_true_key);
1750
1751 END crt_exc;
1752
1753 PROCEDURE update_uid
1754 --------------------
1755 IS
1756 v_new_def_bal_id number(15);
1757 v_new_balance_type_flag varchar2 (1);
1758
1759 BEGIN
1760
1761 BEGIN
1762
1763 select distinct balance_type_id
1764 into l_new_surrogate_key
1765 from pay_balance_types
1766 where replace(ltrim(rtrim(upper(balance_name))), ' ', '_') =
1767 replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_')
1768 and business_group_id is null
1769 and ( (legislation_code is null and stu_rec.c_leg_code is null)
1770 or (legislation_code = stu_rec.c_leg_code) );
1771
1772 v_new_balance_type_flag := 'N';
1773
1774 EXCEPTION WHEN NO_DATA_FOUND THEN
1775
1776
1777 select pay_balance_types_s.nextval
1781 v_new_balance_type_flag := 'Y';
1778 into l_new_surrogate_key
1779 from dual;
1780
1782
1783 WHEN TOO_MANY_ROWS THEN
1784 hrrunprc_trace_on;
1785 hr_utility.trace('sel pay_balance_types TMR');
1786 hr_utility.trace('balance_name ' ||
1787 stu_rec.c_true_key);
1788 hr_utility.trace(':lc: ' || ':' ||
1789 stu_rec.c_leg_code || ':');
1790 hrrunprc_trace_off;
1791 raise;
1792 END;
1793
1794 -- Update all child entities
1795
1796 update hr_s_balance_types
1797 set balance_type_id = l_new_surrogate_key
1798 where balance_type_id = stu_rec.c_surrogate_key;
1799
1800 update hr_s_balance_types
1801 set base_balance_type_id = l_new_surrogate_key,
1802 new_balance_type_flag = v_new_balance_type_flag
1803 where base_balance_type_id = stu_rec.c_surrogate_key;
1804
1805 update hr_s_application_ownerships
1806 set key_value = to_char(l_new_surrogate_key)
1807 where key_value = to_char(stu_rec.c_surrogate_key)
1808 and key_name = 'BALANCE_TYPE_ID';
1809
1810 update hr_s_balance_classifications
1811 set balance_type_id = l_new_surrogate_key
1812 where balance_type_id = stu_rec.c_surrogate_key;
1813
1814 update hr_s_balance_feeds_f
1815 set balance_type_id = l_new_surrogate_key,
1816 new_balance_type_flag = v_new_balance_type_flag
1817 where balance_type_id = stu_rec.c_surrogate_key;
1818
1819 -- Select the currently installed defined balance id, using
1820 -- the by now updated balance_dimension_id and the new balance_type_id.
1821 -- The balance_type_id will find its way onto the defined_balance row
1822 -- when the row is updated with a new surrogate key.
1823
1824 FOR def_bals IN defined(stu_rec.c_surrogate_key) LOOP
1825
1826 BEGIN
1827
1828 select defined_balance_id
1829 into v_new_def_bal_id
1830 from pay_defined_balances
1831 where balance_type_id = l_new_surrogate_key
1832 and balance_dimension_id = def_bals.balance_dimension_id
1833 and business_group_id is null
1834 and ( (legislation_code is null and def_bals.legislation_code is null)
1835 or (legislation_code = def_bals.legislation_code) );
1836
1837 EXCEPTION WHEN NO_DATA_FOUND THEN
1838
1839 select pay_defined_balances_s.nextval
1840 into v_new_def_bal_id
1841 from dual;
1842
1843 WHEN TOO_MANY_ROWS THEN
1844 hrrunprc_trace_on;
1845 hr_utility.trace('sel pay_defined_balances TMR');
1846 hr_utility.trace('balance_type_id ' ||
1847 to_char(l_new_surrogate_key));
1848 hr_utility.trace('balance_name ' ||
1849 stu_rec.c_true_key);
1850 hr_utility.trace('balance_dimension_id ' ||
1851 to_char(def_bals.balance_dimension_id));
1852 hr_utility.trace(':lc: ' || ':' ||
1853 def_bals.legislation_code || ':');
1854 hrrunprc_trace_off;
1855 raise;
1856 END;
1857
1858 update hr_s_defined_balances
1859 set defined_balance_id = v_new_def_bal_id
1860 , balance_type_id = l_new_surrogate_key
1861 where defined_balance_id = def_bals.defined_balance_id
1862 and balance_type_id = def_bals.balance_type_id;
1863
1864 END LOOP def_bals;
1865
1866 END update_uid;
1867
1868 PROCEDURE remove
1869 ----------------
1870 IS
1871 -- Remove a row from either the startup tables or the installed tables
1872
1873 BEGIN
1874
1875
1876 delete from hr_s_balance_classifications
1877 where balance_type_id = stu_Rec.c_surrogate_key;
1878
1879
1880 delete from hr_s_defined_balances
1881 where balance_type_id = stu_Rec.c_surrogate_key;
1882
1883
1884 delete from hr_s_balance_feeds_f
1885 where balance_type_id = stu_Rec.c_surrogate_key;
1886
1887 delete from hr_s_balance_types
1888 where rowid = stu_rec.rowid;
1889
1890
1891 END remove;
1892
1893 FUNCTION valid_ownership RETURN BOOLEAN
1894 ---------------------------------------
1895 IS
1896 -- Test ownership of this current row
1897
1898 BEGIN
1899
1900
1901 IF p_phase <> 1 THEN return TRUE; END IF;
1902
1903
1904 -- Cause an exception to be raised if this row is not needed
1905
1906 if (stu_rec.c_leg_sgrp is null) then
1907 select null
1908 into l_null_return
1909 from dual
1910 where exists
1911 (select null
1912 from hr_s_application_ownerships a
1913 , fnd_product_installations b
1914 , fnd_application c
1915 where a.key_name = 'BALANCE_TYPE_ID'
1916 and a.key_value = stu_rec.c_surrogate_key
1917 and a.product_name = c.application_short_name
1918 and c.application_id = b.application_id
1922 else
1919 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1920 or
1921 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1923 select null
1924 into l_null_return
1925 from dual
1926 where exists
1927 (select null
1928 from hr_s_application_ownerships a
1929 , fnd_product_installations b
1930 , fnd_application c
1931 where a.key_name = 'BALANCE_TYPE_ID'
1932 and a.key_value = stu_rec.c_surrogate_key
1933 and a.product_name = c.application_short_name
1934 and c.application_id = b.application_id
1935 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1936 or
1937 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
1938 and exists (
1939 select null
1940 from hr_legislation_subgroups d
1941 where d.legislation_code = stu_rec.c_leg_code
1942 and d.legislation_subgroup = stu_rec.c_leg_sgrp
1943 and d.active_inactive_flag = 'A' );
1944 end if;
1945
1946 return TRUE; -- Row is required
1947
1948 EXCEPTION WHEN NO_DATA_FOUND THEN
1949
1950 -- Row not needed for any installed product
1951
1952 remove; --+++
1953
1954 return FALSE; -- Row not needed
1955
1956 END valid_ownership;
1957
1958
1959 PROCEDURE transfer_row
1960 ----------------------
1961 IS
1962
1963 v_payroll_install_status varchar2(10);
1964 l_initbfid number;
1965
1966 CURSOR balance_clash
1967 IS
1968 -- Cursor to fetch balances with same name
1969
1970 select /*+ INDEX_FFS(pb) */ business_group_id
1971 from pay_balance_types pb
1972 where business_group_id is not null
1973 and replace(ltrim(rtrim(upper(balance_name))), ' ', '_') =
1974 replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_');
1975
1976 -- See procedure transfer_row in procedure install_past above for
1977 --generic comments.
1978
1979 BEGIN
1980
1981
1982 -- See if payroll is installed or not. This is in order to
1983 -- determine whether child feeds and classifications need to be
1984 -- installed.
1985
1986 select status
1987 into v_payroll_install_status
1988 from fnd_product_installations
1989 where application_id = 801;
1990
1991 --
1992 -- Following checks only need to be made if the balance type
1993 -- is a new one and hence doesn't exist yet.
1994 -- If it does already exist theres no point looking for potential
1995 -- clashes with existing data!
1996 --
1997 if stu_rec.new_balance_type_flag = 'Y' then
1998
1999 if stu_rec.c_leg_code is null then
2000
2001 BEGIN
2002
2003 select distinct null
2004 into l_null_return
2005 from pay_balance_types a
2006 where a.business_group_id is not null
2007 and replace(ltrim(rtrim(upper(a.balance_name))), ' ', '_') =
2008 replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_');
2009
2010 crt_exc('Row already created in a business group');
2011
2012 return; --indicates this row is not to be transferred +++
2013
2014 EXCEPTION WHEN NO_DATA_FOUND THEN
2015
2016 null;
2017
2018 END;
2019
2020 else
2021
2022 for bals in balance_clash loop
2023
2024 BEGIN
2025
2026 select distinct null
2027 into l_null_return
2028 from per_business_groups pbg
2029 where pbg.business_group_id = bals.business_group_id
2030 and pbg.legislation_code = stu_rec.c_leg_code;
2031
2032 crt_exc('Row already created in a business group');
2033
2034 return; --indicates this row is not to be transferred +++
2035
2036 EXCEPTION WHEN NO_DATA_FOUND THEN
2037
2038 null;
2039
2040 END;
2041
2042 end loop;
2043
2044 end if;
2045
2046 -- Now perform a check to see if this primary key has been installed
2047 -- with a legislation code that would make it visible at the same time
2048 -- as this row. Ie: if any legislation code is null within the set of
2049 -- returned rows, then the transfer may not go ahead. If no rows are
2050 -- returned then the delivered row is fine.
2051
2052 BEGIN
2053 select distinct null
2054 into l_null_return
2055 from pay_balance_types
2056 where balance_name = stu_rec.c_true_key
2057 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
2058 and (legislation_code is null or stu_rec.c_leg_code is null)
2059 and business_group_id is null;
2060
2061 crt_exc('Row already created for a visible legislation');
2062
2063 return; --indicates this row is not to be transferred
2064
2065 EXCEPTION WHEN NO_DATA_FOUND THEN
2066 null;
2067
2068 END;
2069
2070 end if;
2074 -- identical the delivered row will be deleted.
2071
2072 -- After the above two checks, no contention exists with the same
2073 -- true primary key. Now compare the row details. If the rows are
2075
2076 -- The child rows will be deleted in the 'remove' function.
2077
2078 -- If the balance type is to be installed, check the child
2079 -- defined balances. These children should be removed from the delivery
2080 -- tables if they match the installed rows. This check is performed in
2081 -- the exception handler, since this is where the installation of the
2082 -- balance type is first identified.
2083 --
2084 -- #331831. Add NVLs wherever values may be null, to prevent flagging
2085 -- rows which are identical but have some null values as different.
2086 --
2087 -- See comments in transfer_row procedure within install_past
2088 -- procedure.
2089
2090
2091 IF p_phase = 1 THEN return; END IF;
2092
2093 IF stu_rec.balance_category_id IS NOT NULL THEN
2094 BEGIN
2095 select distinct null
2096 into l_null_return
2097 from pay_balance_categories_f
2098 where balance_category_id = stu_rec.balance_category_id;
2099
2100 EXCEPTION WHEN NO_DATA_FOUND THEN
2101 crt_exc('Parent balance category does not exist');
2102 return;
2103 END;
2104 END IF;
2105
2106 update pay_balance_types
2107 set business_group_id = null
2108 , legislation_code = stu_rec.c_leg_code
2109 , legislation_subgroup = stu_rec.c_leg_sgrp
2110 , assignment_remuneration_flag=stu_rec.assignment_remuneration_flag
2111 , currency_code = stu_rec.currency_code
2112 , balance_uom = stu_rec.balance_uom
2113 , reporting_name = stu_rec.reporting_name
2114 , jurisdiction_level = stu_rec.jurisdiction_level
2115 , tax_type = stu_rec.tax_type
2116 , last_update_date = stu_rec.last_update_date
2117 , last_updated_by = stu_rec.last_updated_by
2118 , last_update_login = stu_rec.last_update_login
2119 , created_by = stu_rec.created_by
2120 , creation_date = stu_rec.creation_date
2121 , input_value_id = stu_rec.input_value_id
2122 , base_balance_type_id = stu_rec.base_balance_type_id
2123 , balance_category_id = stu_rec.balance_category_id
2124 where balance_type_id = stu_rec.c_surrogate_key;
2125
2126 --+++ Will the code below work or won't there be an exception ??
2127
2128 IF SQL%NOTFOUND THEN
2129
2130 -- No row there to update, must insert
2131
2132 BEGIN
2133 insert into pay_balance_types
2134 (balance_name
2135 ,balance_type_id
2136 ,legislation_code
2137 ,legislation_subgroup
2138 ,assignment_remuneration_flag
2139 ,currency_code
2140 ,balance_uom
2141 ,reporting_name
2142 ,jurisdiction_level
2143 ,tax_type
2144 ,last_update_date
2145 ,last_updated_by
2146 ,last_update_login
2147 ,created_by
2148 ,creation_date
2149 ,input_value_id
2150 ,base_balance_type_id
2151 ,balance_category_id )
2152 values
2153 (stu_rec.c_true_key
2154 ,stu_rec.c_surrogate_key
2155 ,stu_rec.c_leg_code
2156 ,stu_rec.c_leg_sgrp
2157 ,stu_rec.assignment_remuneration_flag
2158 ,stu_rec.currency_code
2159 ,stu_rec.balance_uom
2160 ,stu_rec.reporting_name
2161 ,stu_rec.jurisdiction_level
2162 ,stu_rec.tax_type
2163 ,stu_rec.last_update_date
2164 ,stu_rec.last_updated_by
2165 ,stu_rec.last_update_login
2166 ,stu_rec.created_by
2167 ,stu_rec.creation_date
2168 ,stu_rec.input_value_id
2169 ,stu_rec.base_balance_type_id
2170 ,stu_rec.balance_category_id);
2171 EXCEPTION WHEN OTHERS THEN
2172 hrrunprc_trace_on;
2173 hr_utility.trace('ins pay_balance_types');
2174 hr_utility.trace('bal type id: ' ||
2175 to_char(stu_rec.c_surrogate_key));
2176 hr_utility.trace('bal type name ' ||
2177 stu_rec.c_true_key);
2178 hr_utility.trace(':lc: ' || ':' ||
2179 stu_rec.c_leg_code || ':');
2180 hrrunprc_trace_off;
2181 raise;
2182 END;
2183
2184 END IF;
2185
2186 -- START INSTALL OF CHILD BALANCE_CLASSIFICATIONS
2187
2188 -- At this stage the balance type is either installed new or updated.
2189 -- Therefore all balance classifications will be refreshed. The first
2190 -- stage is to delete those already there, then insert all
2191 -- classification rows in the delivery tables.
2192 --
2193
2194 -- store balance feed currval for latest use - so don't delete any of the
2195 -- classification feeds created (if don't exist in hr_s)
2196 select pay_balance_feeds_s.nextval
2197 into l_initbfid
2198 from dual;
2199
2200 -- THESE ROWS SHOULD ONLY BE DELIVERED IF PAYROLL IS FULLY INSTALLED.
2201
2202 IF v_payroll_install_status = 'I' THEN
2203
2204
2208 ( select 1
2205 delete from pay_balance_classifications pbc
2206 where balance_type_id = stu_rec.c_surrogate_key
2207 and not exists
2209 from hr_s_balance_classifications hsbc
2210 where nvl(hsbc.business_group_id, -1) = nvl(pbc.business_group_id, -1)
2211 and hsbc.legislation_code = pbc.legislation_code
2212 and hsbc.balance_type_id = pbc.balance_type_id
2213 and hsbc.classification_id = pbc.classification_id
2214 and hsbc.scale = pbc.scale
2215 and nvl(hsbc.legislation_subgroup, 'X') = nvl(pbc.legislation_subgroup, 'X'));
2216
2217
2218 -- Install all associated child classification rows.
2219 -- Test to see if the parent classification exists in the live tables.
2220 -- If the select raises an exception then the classification does not
2221 -- exist. Otherwise the row will be inserted.
2222
2223 FOR bal_classes IN class(stu_rec.c_surrogate_key) LOOP
2224
2225
2226 BEGIN
2227 select distinct null
2228 into l_null_return
2229 from pay_element_classifications
2230 where classification_id = bal_classes.classification_id;
2231
2232 EXCEPTION WHEN NO_DATA_FOUND THEN
2233 crt_exc('Parent element classification does not exist');
2234 return;
2235
2236 END;
2237
2238 BEGIN
2239 insert into pay_balance_classifications
2240 (BALANCE_CLASSIFICATION_ID
2241 ,BUSINESS_GROUP_ID
2242 ,LEGISLATION_CODE
2243 ,BALANCE_TYPE_ID
2244 ,CLASSIFICATION_ID
2245 ,SCALE
2246 ,LEGISLATION_SUBGROUP
2247 ,LAST_UPDATE_DATE
2248 ,LAST_UPDATED_BY
2249 ,LAST_UPDATE_LOGIN
2250 ,CREATED_BY
2251 ,CREATION_DATE)
2252 select pay_balance_classifications_s.nextval
2253 , bal_classes.business_group_id
2254 , bal_classes.legislation_code
2255 , bal_classes.balance_type_id
2256 , bal_classes.classification_id
2257 , bal_classes.scale
2258 , bal_classes.legislation_subgroup
2259 , bal_classes.last_update_date
2260 , bal_classes.last_updated_by
2261 , bal_classes.last_update_login
2262 , bal_classes.created_by
2263 , bal_classes.creation_date
2264 from dual;
2265 EXCEPTION WHEN OTHERS THEN
2266 hrrunprc_trace_on;
2267 hr_utility.trace('ins pay_balance_class');
2268 hr_utility.trace('bal type id ' ||
2269 to_char(bal_classes.balance_type_id));
2270 hr_utility.trace('bal type name ' ||
2271 stu_rec.c_true_key);
2272 hr_utility.trace('bal class id ' ||
2273 to_char(bal_classes.classification_id));
2274 hr_utility.trace('bal class scale ' ||
2275 to_char(bal_classes.scale));
2276 hr_utility.trace(':lc: ' || ':' ||
2277 bal_classes.legislation_code || ':');
2278 hrrunprc_trace_off;
2279 raise;
2280 END;
2281
2282 --
2283 -- need to insert balance feeds to those existing elements that have the
2284 -- same classification as the balance classification just inserted.
2285 --
2286 select pay_balance_classifications_s.currval
2287 into l_bal_class_id
2288 from dual;
2289 --
2290 hr_balance_feeds.ins_bf_bal_class
2291 (p_balance_type_id => stu_rec.c_surrogate_key
2292 ,p_balance_classification_id => l_bal_class_id
2293 ,p_mode => 'STARTUP'
2294 );
2295
2296 END LOOP;
2297
2298 END IF; --end check on payroll installation status
2299
2300
2301 -- START INSTALL OF CHILD 'DEFINED BALANCES'
2302
2303 -- Install all associated child 'defined balances' rows.
2304 -- Test to see if the parent dimension exists in the live tables. If
2305 -- the select raises an exception then the dimension does not exist.
2306 -- Otherwise the row will be inserted. At this stage the
2307 -- defined_balance_id will not exist in the live tables. Consequently
2308 -- only new defined_balances will remain in the delivery tables.
2309
2310 FOR def_bals IN defined(stu_rec.c_surrogate_key) LOOP
2311
2312
2313 BEGIN
2314 select distinct null
2315 into l_null_return
2316 from pay_balance_dimensions
2317 where balance_dimension_id = def_bals.balance_dimension_id;
2318
2319 EXCEPTION WHEN NO_DATA_FOUND THEN
2320 crt_exc('Parent balance dimension does not exist');
2321 return;
2322
2323 END;
2324
2325 update pay_defined_balances
2326 set BUSINESS_GROUP_ID = null,
2327 LEGISLATION_CODE = def_bals.LEGISLATION_CODE,
2328 BALANCE_TYPE_ID = def_bals.BALANCE_TYPE_ID,
2329 BALANCE_DIMENSION_ID = def_bals.BALANCE_DIMENSION_ID,
2330 FORCE_LATEST_BALANCE_FLAG = def_bals.FORCE_LATEST_BALANCE_FLAG,
2331 LEGISLATION_SUBGROUP = def_bals.LEGISLATION_SUBGROUP,
2332 LAST_UPDATE_DATE = def_bals.LAST_UPDATE_DATE,
2336 CREATION_DATE = def_bals.CREATION_DATE,
2333 LAST_UPDATED_BY = def_bals.LAST_UPDATED_BY,
2334 LAST_UPDATE_LOGIN = def_bals.LAST_UPDATE_LOGIN,
2335 CREATED_BY = def_bals.CREATED_BY,
2337 GROSSUP_ALLOWED_FLAG = def_bals.GROSSUP_ALLOWED_FLAG,
2338 SAVE_RUN_BALANCE = def_bals.SAVE_RUN_BALANCE
2339 -- RUN_BALANCE_STATUS = def_bals.RUN_BALANCE_STATUS
2340 where DEFINED_BALANCE_ID = def_bals.defined_balance_id;
2341
2342 IF SQL%NOTFOUND THEN
2343 -- doesn't exist so do insert
2344 BEGIN
2345 insert into pay_defined_balances
2346 (DEFINED_BALANCE_ID
2347 ,BUSINESS_GROUP_ID
2348 ,LEGISLATION_CODE
2349 ,BALANCE_TYPE_ID
2350 ,BALANCE_DIMENSION_ID
2351 ,FORCE_LATEST_BALANCE_FLAG
2352 ,LEGISLATION_SUBGROUP
2353 ,LAST_UPDATE_DATE
2354 ,LAST_UPDATED_BY
2355 ,LAST_UPDATE_LOGIN
2356 ,CREATED_BY
2357 ,CREATION_DATE
2358 ,GROSSUP_ALLOWED_FLAG
2359 ,SAVE_RUN_BALANCE
2360 ,RUN_BALANCE_STATUS)
2361 values
2362 (def_bals.defined_balance_id
2363 ,null
2364 ,def_bals.legislation_code
2365 ,def_bals.balance_type_id
2366 ,def_bals.balance_dimension_id
2367 ,def_bals.force_latest_balance_flag
2368 ,def_bals.legislation_subgroup
2369 ,def_bals.last_update_date
2370 ,def_bals.last_updated_by
2371 ,def_bals.last_update_login
2372 ,def_bals.created_by
2373 ,def_bals.creation_date
2374 ,def_bals.grossup_allowed_flag
2375 ,def_bals.save_run_balance
2376 ,def_bals.run_balance_status);
2377 EXCEPTION WHEN OTHERS THEN
2378 hrrunprc_trace_on;
2379 hr_utility.trace('ins pay_def_bal');
2380 hr_utility.trace('def bal id ' ||
2381 to_char(def_bals.defined_balance_id));
2382 hr_utility.trace('bal type id ' ||
2383 to_char(def_bals.balance_type_id));
2384 hr_utility.trace('bal type name ' ||
2385 stu_rec.c_true_key);
2386 hr_utility.trace('bal dim id ' ||
2387 to_char(def_bals.balance_dimension_id));
2388 hr_utility.trace('save_run_balance ' ||
2389 def_bals.save_run_balance);
2390 hr_utility.trace(':lc: ' || ':' ||
2391 def_bals.legislation_code || ':');
2392 hrrunprc_trace_off;
2393 raise;
2394 END;
2395
2396 END IF;
2397
2398 END LOOP;
2399
2400
2401 -- START INSTALL OF CHILD 'BALANCE FEEDS'
2402
2403 -- THESE FEEDS SHOULD ONLY BE INSTALLED IF THE PAYROLL INSTALL STATUS
2404 -- IS SET TO 'I'.
2405
2406 IF v_payroll_install_status = 'I' THEN
2407
2408 -- Start by deleting balance feeds currently installed
2409
2410 --
2411 -- This statement removes any balance feeds from
2412 -- pay_balance_feeds_f that belong to a balance_type_id
2413 -- in hr_s_balance_types but do not exist in
2414 -- hr_s_balance_feeds_f.
2415
2416 delete from pay_balance_feeds_f pbf
2417 where pbf.balance_type_id = stu_rec.c_surrogate_key
2418 and pbf.business_group_id is null
2419 and pbf.legislation_code is not null
2420 and pbf.balance_feed_id <= l_initbfid
2421 and not exists (
2422 select null
2423 from hr_s_balance_feeds_f hrs
2424 where pbf.balance_type_id = hrs.balance_type_id
2425 and pbf.input_value_id = hrs.input_value_id
2426 and pbf.effective_start_date = hrs.effective_start_date
2427 and pbf.effective_end_date = hrs.effective_end_date);
2428
2429 --
2430 -- Install all associated child 'balance feed' rows.
2431 -- Test to see if the parent input vlue exists in the live tables. If
2432 -- the select raises an exception then the input value does not exist.
2433 -- Otherwise the row will be inserted.
2434
2435 FOR bal_feeds IN feed(stu_rec.c_surrogate_key) LOOP
2436
2437
2438 BEGIN
2439 select distinct null
2440 into l_null_return
2441 from pay_input_values_f
2442 where input_value_id = bal_feeds.input_value_id;
2443
2444 EXCEPTION WHEN NO_DATA_FOUND THEN
2445 crt_exc('Parent input value does not exist');
2446 return;
2447
2448 END;
2449
2450 BEGIN
2451
2452 if (bal_feeds.new_input_value_flag = 'Y' OR
2453 bal_feeds.new_balance_type_flag = 'Y') then
2454 HRASSACT.CHECK_LATEST_BALANCES := FALSE;
2455 end if;
2456
2457
2458 insert into pay_balance_feeds_f
2459 (balance_feed_id
2460 ,effective_start_date
2461 ,effective_end_date
2462 ,business_group_id
2463 ,legislation_code
2464 ,balance_type_id
2465 ,input_value_id
2466 ,scale
2467 ,legislation_subgroup
2471 ,created_by
2468 ,last_update_date
2469 ,last_updated_by
2470 ,last_update_login
2472 ,creation_date)
2473 select pay_balance_feeds_s.nextval
2474 ,bal_feeds.effective_start_date
2475 ,bal_feeds.effective_end_date
2476 ,bal_feeds.business_group_id
2477 ,bal_feeds.legislation_code
2478 ,bal_feeds.balance_type_id
2479 ,bal_feeds.input_value_id
2480 ,bal_feeds.scale
2481 ,bal_feeds.legislation_subgroup
2482 ,bal_feeds.last_update_date
2483 ,bal_feeds.last_updated_by
2484 ,bal_feeds.last_update_login
2485 ,bal_feeds.created_by
2486 ,bal_feeds.creation_date
2487 from dual;
2488 EXCEPTION WHEN OTHERS THEN
2489 hrrunprc_trace_on;
2490 hr_utility.trace('ins pay_bal_feed');
2491 hr_utility.trace('bal type id ' ||
2492 to_char(bal_feeds.balance_type_id));
2493 hr_utility.trace('bal type name ' ||
2494 stu_rec.c_true_key);
2495 hr_utility.trace('input value id ' ||
2496 to_char(bal_feeds.input_value_id));
2497 hr_utility.trace(':lc: ' || ':' ||
2498 bal_feeds.legislation_code || ':');
2499 hrrunprc_trace_off;
2500 raise;
2501 END;
2502
2503 HRASSACT.CHECK_LATEST_BALANCES := TRUE;
2504
2505 END LOOP;
2506
2507 END IF; -- end payroll install status check
2508
2509 remove;
2510 --+++
2511
2512 END transfer_row;
2513
2514 BEGIN
2515
2516 -- This is the main loop to perform the installation logic. A cursor
2517 -- is opened to control the loop, and each row returned is placed
2518 -- into a record defined within the main procedure so each sub
2519 -- procedure has full access to all returrned columns. For each
2520 -- new row returned, a new savepoint is declared. If at any time
2521 -- the row is in error a rollback is performed to the savepoint
2522 -- and the next row is returned. Ownership details are checked and
2523 -- if the row is required then the surrogate id is updated and the
2524 -- main transfer logic is called.
2525
2526 IF p_phase = 1 THEN check_next_sequence; END IF;
2527
2528 FOR delivered IN stu LOOP
2529
2530 savepoint new_balance_name;
2531 stu_rec := delivered;
2532
2533 IF p_phase = 2 THEN l_new_surrogate_key := stu_rec.c_surrogate_key; END IF;
2534
2535 -- Test the row onerships for the current row
2536
2537 IF (p_phase = 2 OR valid_ownership) THEN
2538 IF p_phase = 1 THEN update_uid; END IF;
2539 transfer_row;
2540 END IF;
2541
2542 END LOOP;
2543 --
2544 --
2545 END install_bal_types;
2546
2547 --****************************************************************************
2548 -- INSTALLATION PROCEDURE FOR : PAY_BALANCE_DIMENSIONS
2549 --****************************************************************************
2550
2551 PROCEDURE install_dimensions (p_phase IN number)
2552 ------------------------------------------------
2553 IS
2554 l_null_return varchar2(1); -- used for 'select null' statements
2555 l_new_surrogate_key number(15); -- new surrogate key for the delivery row
2556 l_route_id ff_routes.route_id%type;
2557
2558 CURSOR stu -- selects all rows from startup entity
2559 IS
2560 select dimension_name c_true_key
2561 , balance_dimension_id c_surrogate_key
2562 , route_id
2563 , legislation_code c_leg_code
2564 , legislation_subgroup c_leg_sgrp
2565 , database_item_suffix
2566 , dimension_type
2567 , description
2568 , feed_checking_code
2569 , feed_checking_type
2570 , payments_flag
2571 , expiry_checking_code
2572 , expiry_checking_level
2573 , dimension_level
2574 , period_type
2575 , asg_action_balance_dim_id
2576 , database_item_function
2577 , save_run_balance_enabled
2578 , start_date_code
2579 , rowid
2580 from hr_s_balance_dimensions;
2581
2582 stu_rec stu%ROWTYPE; -- Record for the above select
2583
2584
2585 PROCEDURE check_next_sequence
2586 ------------------------------
2587 IS
2588
2589 v_sequence_number number(15);
2590 v_min_delivered number(15);
2591 v_max_delivered number(15);
2592 v_max_live number(15);
2593 cnt number(15);
2594
2595 -- Surrogate id conflicts may arise from two scenario's:
2596 -- 1. Where the newly select sequence value conflicts with values
2597 -- in the STU tables.
2598 -- 2. Where selected surrogate keys, from the installed tables,
2599 -- conflict with other rows in the STU tables.
2600 --
2601 -- Both of the above scenario's are tested for.
2602 -- The first is a simple match, where if a value is detected in the
2603 -- STU tables and the installed tables then a conflict is detected. In
2604 -- This instance all STU surrogate keys, for this table, are updated.
2605 -- The second is tested for using the sequences.
2606 -- If the next value from the live sequence is within the range of
2610
2607 -- delivered surrogate id's then the live sequence must be incremented.
2608 -- If no action is taken, then duplicates may be introduced into the
2609 -- delivered tables, and child rows may be totally invalidated.
2611 BEGIN
2612
2613
2614 BEGIN --check that the installed id's will not conflict
2615 --with the delivered values
2616
2617
2618 select count(*)
2619 into cnt
2620 from hr_s_balance_dimensions;
2621
2622 If cnt=0 then return; end if;
2623
2624
2625 select distinct null
2626 into l_null_return
2627 from pay_balance_dimensions a
2628 where exists
2629 (select null
2630 from hr_s_balance_dimensions b
2631 where a.balance_dimension_id = b.balance_dimension_id
2632 );
2633
2634 --conflict may exist
2635 --update all balance_dimension_id's to remove conflict
2636
2637 update /*+NO_INDEX*/ hr_s_balance_dimensions
2638 set balance_dimension_id = balance_dimension_id - 50000000;
2639
2640 update /*+NO_INDEX*/ hr_s_defined_balances
2641 set balance_dimension_id = balance_dimension_id - 50000000;
2642
2643 update /*+NO_INDEX*/ hr_s_balance_dimensions
2644 set asg_action_balance_dim_id = asg_action_balance_dim_id
2645 - 50000000;
2646
2647 update /*+NO_INDEX*/ hr_s_dimension_routes
2648 set balance_dimension_id = balance_dimension_id - 50000000;
2649
2650 update /*+NO_INDEX*/ hr_s_dimension_routes
2651 set run_dimension_id = run_dimension_id - 50000000;
2652
2653 update hr_s_application_ownerships
2654 set key_value = key_value - 50000000
2655 where key_name = 'BALANCE_DIMENSION_ID';
2656
2657 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2658
2659 END; --check of balance_dimension_id
2660
2661
2662
2663 select min(balance_dimension_id) - (count(*) *3)
2664 , max(balance_dimension_id) + (count(*) *3)
2665 into v_min_delivered
2666 , v_max_delivered
2667 from hr_s_balance_dimensions;
2668
2669 select max(balance_dimension_id)
2670 into v_max_live
2671 from pay_balance_dimensions;
2672
2673 select pay_balance_dimensions_s.nextval
2674 into v_sequence_number
2675 from dual;
2676
2677 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
2678 OR (v_sequence_number < v_max_live) THEN
2679
2680 IF v_max_live > v_max_delivered THEN
2681
2682 hr_legislation.munge_sequence('PAY_BALANCE_DIMENSIONS_S',
2683 v_sequence_number,
2684 v_max_live);
2685 ELSE
2686
2687 hr_legislation.munge_sequence('PAY_BALANCE_DIMENSIONS_S',
2688 v_sequence_number,
2689 v_max_delivered);
2690 END IF;
2691 END IF;
2692
2693 END check_next_sequence;
2694
2695 PROCEDURE crt_exc (exception_type IN varchar2)
2696 ----------------------------------------------
2697 IS
2698 -- Reports any exceptions during the delivery of startup data to
2699 -- PAY_BALANCE_DIMENSIONS
2700
2701 BEGIN
2702 -- See procedure crt_exc in procedure install_past above for generic
2703 -- details.
2704
2705 rollback to new_dimension_name;
2706 insert_hr_stu_exceptions('pay_balance_dimensions'
2707 , stu_rec.c_surrogate_key
2708 , exception_type
2709 , stu_rec.c_true_key);
2710
2711
2712 END crt_exc;
2713
2714 PROCEDURE update_uid
2715 --------------------
2716 IS
2717 -- Subprogram to update surrogate UID and all occurrences in child rows
2718
2719 BEGIN
2720
2721 BEGIN
2722 select distinct balance_dimension_id
2723 into l_new_surrogate_key
2724 from pay_balance_dimensions
2725 where dimension_name = stu_rec.c_true_key
2726 and business_group_id is null
2727 and ( (legislation_code is null and stu_rec.c_leg_code is null)
2728 or (legislation_code = stu_rec.c_leg_code) );
2729
2730 EXCEPTION WHEN NO_DATA_FOUND THEN
2731
2732 select pay_balance_dimensions_s.nextval
2733 into l_new_surrogate_key
2734 from dual;
2735
2736 WHEN TOO_MANY_ROWS THEN
2737 hrrunprc_trace_on;
2738 hr_utility.trace('sel pay_balance_dimensions TMR');
2739 hr_utility.trace('dimension_name ' ||
2740 stu_rec.c_true_key);
2741 hr_utility.trace(':lc: ' || ':' ||
2742 stu_rec.c_leg_code || ':');
2743 hrrunprc_trace_off;
2744 raise;
2745 END;
2746
2747 -- Update all child entities
2748
2749
2750 update hr_s_balance_dimensions
2751 set balance_dimension_id = l_new_surrogate_key
2752 where balance_dimension_id = stu_rec.c_surrogate_key;
2753
2754 update hr_s_application_ownerships
2758
2755 set key_value = to_char(l_new_surrogate_key)
2756 where key_value = to_char(stu_rec.c_surrogate_key)
2757 and key_name = 'BALANCE_DIMENSION_ID';
2759 update hr_s_defined_balances
2760 set balance_dimension_id = l_new_surrogate_key
2761 where balance_dimension_id = stu_rec.c_surrogate_key;
2762
2763 update hr_s_dimension_routes
2764 set balance_dimension_id = l_new_surrogate_key
2765 where balance_dimension_id = stu_rec.c_surrogate_key;
2766
2767 update hr_s_dimension_routes
2768 set run_dimension_id = l_new_surrogate_key
2769 where run_dimension_id = stu_rec.c_surrogate_key;
2770
2771 update hr_s_balance_dimensions
2772 set asg_action_balance_dim_id = l_new_surrogate_key
2773 where asg_action_balance_dim_id = stu_rec.c_surrogate_key;
2774
2775
2776 END update_uid;
2777
2778 PROCEDURE remove
2779 ----------------
2780 IS
2781 --+++ Comment used to say remove from either s/u or installed
2782
2783 BEGIN
2784
2785 delete from hr_s_balance_dimensions
2786 where rowid = stu_rec.rowid;
2787
2788
2789 END remove;
2790
2791 FUNCTION valid_ownership RETURN BOOLEAN
2792 ---------------------------------------
2793 IS
2794 -- Test ownership of this current row
2795 l_null_return varchar2(1); -- used for 'select null' statements
2796
2797 BEGIN
2798 -- See valid_ownership procedure within install_past above for generic
2799 -- details of this procedure.
2800
2801
2802 IF p_phase <> 1 THEN return TRUE; END IF;
2803
2804
2805 -- Cause an exception to be raised if this row is not needed
2806 if (stu_rec.c_leg_sgrp is null) then
2807 select distinct null
2808 into l_null_return
2809 from dual
2810 where exists
2811 (select null
2812 from hr_s_application_ownerships a
2813 , fnd_product_installations b
2814 , fnd_application c
2815 where a.key_name = 'BALANCE_DIMENSION_ID'
2816 and a.key_value = stu_rec.c_surrogate_key
2817 and a.product_name = c.application_short_name
2818 and c.application_id = b.application_id
2819 and ((b.status = 'I' and c.application_short_name <> 'PQP')
2820 or
2821 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2822 else
2823 select distinct null
2824 into l_null_return
2825 from dual
2826 where exists
2827 (select null
2828 from hr_s_application_ownerships a
2829 , fnd_product_installations b
2830 , fnd_application c
2831 where a.key_name = 'BALANCE_DIMENSION_ID'
2832 and a.key_value = stu_rec.c_surrogate_key
2833 and a.product_name = c.application_short_name
2834 and c.application_id = b.application_id
2835 and ((b.status = 'I' and c.application_short_name <> 'PQP')
2836 or
2837 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
2838 and exists (
2839 select null
2840 from hr_legislation_subgroups d
2841 where d.legislation_code = stu_rec.c_leg_code
2842 and d.legislation_subgroup = stu_rec.c_leg_sgrp
2843 and d.active_inactive_flag = 'A' );
2844 end if;
2845
2846 --
2847 return TRUE; -- Row is required
2848
2849 EXCEPTION WHEN NO_DATA_FOUND THEN
2850
2851 -- Row not needed for any installed product
2852
2853 remove;
2854 return FALSE; -- Row not needed
2855
2856 END valid_ownership;
2857
2858 FUNCTION check_parents RETURN BOOLEAN
2859 -------------------------------------
2860 IS
2861 -- Check if parent data is correct
2862
2863 BEGIN
2864
2865 -- This procedure is only called in phase 2. The logic to check if
2866 -- a given parental foriegn key exists is split into two parts for
2867 -- every foriegn key. The first select from the delivery tables.
2868
2869 -- If a row is founnd then the installation of the parent must have
2870 -- failed, and this installation must not go ahead. If no data is
2871 -- found, ie: an exception is raised, the installation is valid.
2872
2873 -- The second check looks for a row in the live tables. If no rows
2874 -- are returned then this installation is invalid, since this means
2875 -- that the parent referenced by this row is not present in the
2876 -- live tables.
2877
2878 -- The distinct is used in case the parent is date effective and many rows
2879 -- may be returned by the same parent id.
2880
2881
2882 BEGIN
2883
2884 -- Start the checking against the first parent table
2885
2886 select distinct null
2887 into l_null_return
2888 from hr_s_routes
2889 where route_id = stu_rec.route_id;
2890
2891 crt_exc('Parent route remains in delivery tables');
2892
2893 return FALSE; -- Parent row still in startup account
2894
2895 EXCEPTION WHEN NO_DATA_FOUND THEN
2896 null;
2897
2901 -- Now check the live account
2898 END;
2899
2900
2902
2903 BEGIN
2904 select null
2905 into l_null_return
2906 from ff_routes
2907 where route_id = stu_rec.route_id;
2908
2909 return TRUE;
2910
2911 EXCEPTION WHEN NO_DATA_FOUND THEN
2912
2913 crt_exc('Parent route not installed in live tables');
2914
2915 return FALSE;
2916
2917 END;
2918
2919 END check_parents;
2920
2921 PROCEDURE transfer_row
2922 ----------------------
2923 IS
2924
2925 -- See procedure transfer_row in procedure install_past above for generic
2926 -- comments.
2927
2928 v_inst_update date; -- Hold update details of installed row
2929 form_count number;
2930 --
2931 cursor c_get_baldim is
2932 select distinct null
2933 from pay_balance_dimensions a
2934 where a.dimension_name = stu_rec.c_true_key
2935 and a.business_group_id is not null
2936 and exists (select null from per_business_groups b
2937 where b.business_group_id = a.business_group_id
2938 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
2939
2940 cursor c_get_baldim_null is
2941 select distinct null
2942 from pay_balance_dimensions
2943 where dimension_name = stu_rec.c_true_key
2944 and legislation_code <> stu_rec.c_leg_code
2945 and (legislation_code is null or stu_rec.c_leg_code is null);
2946 --
2947 BEGIN
2948
2949 BEGIN
2950 open c_get_baldim;
2951 fetch c_get_baldim into l_null_return;
2952 IF c_get_baldim%NOTFOUND OR c_get_baldim%NOTFOUND IS NULL THEN
2953 RAISE NO_DATA_FOUND;
2954 END IF;
2955 close c_get_baldim;
2956 --
2957 crt_exc('Row already created in a business group');
2958
2959 return;
2960
2961 EXCEPTION WHEN NO_DATA_FOUND THEN
2962 null;
2963
2964 END;
2965
2966
2967 -- Now perform a check to see if this primary key has been installed
2968 -- with a legislation code that would make it visible at the same time
2969 -- as this row. Ie: if any legislation code is null within the set of
2970 -- returned rows, then the transfer may not go ahead. If no rows are
2971 -- returned then the delivered row is fine.
2972
2973 BEGIN
2974 --
2975 open c_get_baldim_null;
2976 fetch c_get_baldim_null into l_null_return;
2977 IF c_get_baldim_null%NOTFOUND OR c_get_baldim_null%NOTFOUND IS NULL THEN
2978 RAISE NO_DATA_FOUND;
2979 END IF;
2980 close c_get_baldim_null;
2981 --
2982 crt_exc('Row already created for a visible legislation');
2983 return; -- Indicates this row is not to be transferred
2984 --
2985 EXCEPTION WHEN NO_DATA_FOUND THEN
2986 null;
2987
2988 END;
2989
2990
2991 -- After the above two checks, no contention exists with the same
2992 -- true primary key. Now compare the row details. If the rows are
2993 -- identical the delivered row will be deleted.
2994
2995 delete from hr_s_balance_dimensions a
2996 where a.balance_dimension_id = stu_rec.c_surrogate_key
2997 and exists (
2998 select 1 from pay_balance_dimensions b
2999 where a.ROUTE_ID = b.route_id
3000 and a.DATABASE_ITEM_SUFFIX = b.DATABASE_ITEM_SUFFIX
3001 and a.DIMENSION_TYPE = b.DIMENSION_TYPE
3002 and length(a.FEED_CHECKING_CODE) = length(b.FEED_CHECKING_CODE)
3003 and a.FEED_CHECKING_TYPE = b.FEED_CHECKING_TYPE
3004 and a.PAYMENTS_FLAG = b.PAYMENTS_FLAG
3005 and length(a.EXPIRY_CHECKING_CODE) = length(b.EXPIRY_CHECKING_CODE)
3006 and a.EXPIRY_CHECKING_LEVEL = b.EXPIRY_CHECKING_LEVEL
3007 and a.DIMENSION_LEVEL = b.DIMENSION_LEVEL
3008 and a.PERIOD_TYPE = b.PERIOD_TYPE);
3009
3010 -- When the procedure is called in phase 1, there is no need to
3011 -- actually perform the transfer from the delivery tables into the
3012 -- live. Hence if phase = 1 control is returned to the calling
3013 -- procedure and the next row is returned.
3014
3015 IF p_phase = 1 THEN return; END IF;
3016
3017 -- If the procedure is called in phase 2, then the live row is updated
3018 -- with the values on the delivered row.
3019
3020 -- The routine check_parents validates foreign key references and
3021 -- ensures referential integrity. The routine checks to see if the
3022 -- parents of a given row have been transfered to the live tables.
3023
3024 IF NOT check_parents THEN return; END IF;
3025
3026 -- This may only be called in phase two since in phase one all
3027 -- parent rows will remain in the delivery tables.
3028
3029 -- After the above checks only data that has been chanegd or is new
3030 -- will be left in the delivery tables. At this stage if the row is
3031 -- already present then it must be updated to ensure referential
3032 -- integrity. Therefore an update will be performed and if SQL%FOUND
3033 -- is FALSE an insert will be performed.
3034
3035 -- The last step of the transfer, in phase 2, is to delete the now
3039 -- about to have its route_id changed. Bug 4328538.
3036 -- transferred row from the delivery tables.
3037
3038 -- Delete the user entity for a dimension if its
3040
3041 BEGIN
3042
3043 select route_id
3044 into l_route_id
3045 from pay_balance_dimensions
3046 where balance_dimension_id = stu_rec.c_surrogate_key;
3047
3048 EXCEPTION WHEN NO_DATA_FOUND THEN
3049 l_route_id := stu_rec.route_id;
3050 END;
3051
3052 if l_route_id <> stu_rec.route_id then
3053
3054 delete ff_compiled_info_f
3055 where formula_id in (
3056 select fdi.formula_id
3057 from ff_fdi_usages_f fdi,
3058 ff_user_entities ue,
3059 pay_defined_balances db,
3060 pay_balance_dimensions bd,
3061 ff_database_items di
3062 where fdi.item_name = di.user_name
3063 and ue.creator_type = 'B'
3064 and ue.creator_id = db.defined_balance_id
3065 and bd.balance_dimension_id = db.balance_dimension_id
3066 and bd.balance_dimension_id = stu_rec.c_surrogate_key
3067 and di.user_entity_id = ue.user_entity_id);
3068
3069 delete from ff_fdi_usages_f fdi2
3070 where fdi2.formula_id in
3071 (select fdi.formula_id
3072 from ff_fdi_usages_f fdi,
3073 ff_user_entities ue,
3074 pay_defined_balances db,
3075 pay_balance_dimensions bd,
3076 ff_database_items di
3077 where fdi.item_name = di.user_name
3078 and ue.creator_type = 'B'
3079 and ue.creator_id = db.defined_balance_id
3080 and bd.balance_dimension_id = db.balance_dimension_id
3081 and bd.balance_dimension_id = stu_rec.c_surrogate_key
3082 and di.user_entity_id = ue.user_entity_id);
3083
3084 delete from ff_user_entities
3085 where creator_type = 'B'
3086 and creator_id in
3087 (select defined_balance_id
3088 from pay_defined_balances pdb
3089 where pdb.balance_dimension_id = stu_rec.c_surrogate_key);
3090
3091 end if;
3092
3093 update pay_balance_dimensions
3094 set route_id = stu_rec.route_id
3095 , database_item_suffix = stu_rec.database_item_suffix
3096 , dimension_type = stu_rec.dimension_type
3097 , description = stu_rec.description
3098 , feed_checking_code = stu_rec.feed_checking_code
3099 , feed_checking_type = stu_rec.feed_checking_type
3100 , payments_flag = stu_rec.payments_flag
3101 , expiry_checking_code = stu_rec.expiry_checking_code
3102 , expiry_checking_level = stu_rec.expiry_checking_level
3103 , dimension_level = stu_rec.dimension_level
3104 , period_type = stu_rec.period_type
3105 , asg_action_balance_dim_id = stu_rec.asg_action_balance_dim_id
3106 , database_item_function = stu_rec.database_item_function
3107 , save_run_balance_enabled = stu_rec.save_run_balance_enabled
3108 , start_date_code = stu_rec.start_date_code
3109 where balance_dimension_id = stu_rec.c_surrogate_key;
3110
3111 IF NOT SQL%FOUND THEN
3112 BEGIN
3113 insert into pay_balance_dimensions
3114 (dimension_name
3115 ,balance_dimension_id
3116 ,route_id
3117 ,legislation_code
3118 ,legislation_subgroup
3119 ,database_item_suffix
3120 ,dimension_type
3121 ,description
3122 ,feed_checking_code
3123 ,feed_checking_type
3124 ,payments_flag
3125 ,expiry_checking_code
3126 ,expiry_checking_level
3127 ,dimension_level
3128 ,period_type
3129 ,asg_action_balance_dim_id
3130 ,database_item_function
3131 ,save_run_balance_enabled
3132 ,start_date_code
3133 )
3134 values
3135 (stu_rec.c_true_key
3136 ,stu_rec.c_surrogate_key
3137 ,stu_rec.route_id
3138 ,stu_rec.c_leg_code
3139 ,stu_rec.c_leg_sgrp
3140 ,stu_rec.database_item_suffix
3141 ,stu_rec.dimension_type
3142 ,stu_rec.description
3143 ,stu_rec.feed_checking_code
3144 ,stu_rec.feed_checking_type
3145 ,stu_rec.payments_flag
3146 ,stu_rec.expiry_checking_code
3147 ,stu_rec.expiry_checking_level
3148 ,stu_rec.dimension_level
3149 ,stu_rec.period_type
3150 ,stu_rec.asg_action_balance_dim_id
3151 ,stu_rec.database_item_function
3152 ,stu_rec.save_run_balance_enabled
3153 ,stu_rec.start_date_code);
3154 EXCEPTION WHEN OTHERS THEN
3155 hrrunprc_trace_on;
3156 hr_utility.trace('ins pay_bal_dim');
3157 hr_utility.trace('bal dim id ' ||
3158 to_char(stu_rec.c_surrogate_key));
3159 hr_utility.trace('dimension name ' ||
3160 stu_rec.c_true_key);
3161 hr_utility.trace('dbi suffix ' ||
3162 stu_rec.database_item_suffix);
3163 hr_utility.trace('save_run_balance_enabled ' ||
3167 hrrunprc_trace_off;
3164 stu_rec.save_run_balance_enabled);
3165 hr_utility.trace(':lc: ' || ':' ||
3166 stu_rec.c_leg_code || ':');
3168 raise;
3169 END;
3170
3171 END IF;
3172
3173 remove;
3174
3175 END transfer_row;
3176
3177 BEGIN
3178
3179 -- This is the main loop to perform the installation logic. A cursor
3180 -- is opened to control the loop, and each row returned is placed
3181 -- into a record defined within the main procedure so each sub
3182 -- procedure has full access to all returned columns. For each
3183 -- new row returned, a new savepoint is declared. If at any time
3184 -- the row is in error a rollback is performed to the savepoint
3185 -- and the next row is returned. Ownership details are checked and
3186 -- if the row is required then the surrogate id is updated and the
3187 -- main transfer logic is called.
3188
3189 IF p_phase = 1 THEN check_next_sequence; END IF;
3190
3191 FOR delivered IN stu LOOP
3192
3193 savepoint new_dimension_name;
3194
3195 stu_rec := delivered;
3196
3197 IF p_phase = 2 THEN l_new_surrogate_key := stu_rec.c_surrogate_key; END IF;
3198
3199 -- Test the row onerships for the current row
3200
3201 IF valid_ownership THEN
3202 IF p_phase = 1 THEN update_uid; END IF;
3203 transfer_row;
3204 END IF;
3205
3206 END LOOP;
3207
3208 END install_dimensions;
3209
3210 --****************************************************************************
3211 -- INSTALLATION PROCEDURE FOR : PAY_DIMENSION_ROUTES
3212 --****************************************************************************
3213 PROCEDURE install_dimension_routes(p_phase IN number) IS
3214
3215 l_null_return varchar2(1); -- used for select null stmts
3216 l_new_surrogate_key number(15); -- new surrogate key for the delivery row
3217 form_count number;
3218
3219 CURSOR stu -- selects all rows from startup entity
3220 IS
3221 select BALANCE_DIMENSION_ID
3222 , ROUTE_ID
3223 , ROUTE_TYPE
3224 , PRIORITY
3225 , RUN_DIMENSION_ID
3226 , BALANCE_TYPE_COLUMN
3227 , DECODE_REQUIRED
3228 , rowid
3229 from hr_s_dimension_routes;
3230
3231 stu_rec stu%ROWTYPE; -- Record for the above select
3232 l_route_id ff_routes.route_id%type;
3233 l_run_dimension_id pay_dimension_routes.run_dimension_id%type;
3234
3235 PROCEDURE remove IS
3236 -------------------
3237 -- Remove a row from either the startup tables or the installed tables
3238 BEGIN
3239 delete from hr_s_dimension_routes
3240 where rowid = stu_rec.rowid;
3241 END remove;
3242
3243 PROCEDURE crt_exc (exception_type IN varchar2)
3244 ----------------------------------------------
3245 IS
3246 -- Reports any exceptions during the delivery of startup data to
3247 -- PAY_DIMENSION_ROUTES
3248
3249 BEGIN
3250 -- See procedure crt_exc in procedure install_past above for generic
3251 -- details.
3252
3253 rollback to new_dimension_route;
3254 insert_hr_stu_exceptions('pay_dimension_routes'
3255 , stu_rec.BALANCE_DIMENSION_ID
3256 , exception_type
3257 , to_char(stu_rec.ROUTE_ID));
3258
3259 END crt_exc;
3260
3261 FUNCTION check_parents RETURN BOOLEAN IS
3262 ----------------------------------------
3263 -- Check if parent data is correct
3264 BEGIN
3265
3266 -- Start the checking bal dim against delivered
3267
3268 BEGIN
3269
3270 select distinct null
3271 into l_null_return
3272 from hr_s_balance_dimensions
3273 where balance_dimension_id = stu_rec.balance_dimension_id;
3274
3275 crt_exc('Parent balance dimension remains in delivery tables');
3276 return FALSE; -- Parent row still in startup account
3277
3278 EXCEPTION WHEN NO_DATA_FOUND THEN
3279 null;
3280 END;
3281
3282 -- Start the checking bal dim against live
3283
3284 BEGIN
3285
3286 select distinct null
3287 into l_null_return
3288 from pay_balance_dimensions
3289 where balance_dimension_id = stu_rec.balance_dimension_id;
3290
3291 EXCEPTION WHEN NO_DATA_FOUND THEN
3292 remove;
3293 return FALSE; -- Parent row still in startup account
3294 END;
3295
3296 -- Now check the same for the route id
3297
3298 BEGIN
3299
3300 select distinct null
3301 into l_null_return
3302 from hr_s_routes
3303 where route_id = stu_rec.route_id;
3304
3305 crt_exc('Parent route remains in delivery tables');
3306 return FALSE; -- Parent row still in startup account
3307
3308 EXCEPTION WHEN NO_DATA_FOUND THEN
3309 null;
3310 END;
3314 BEGIN
3311
3312 -- Start the checking bal dim against live
3313
3315
3316 select distinct null
3317 into l_null_return
3318 from ff_routes
3319 where route_id = stu_rec.route_id;
3320
3321 EXCEPTION WHEN NO_DATA_FOUND THEN
3322 remove;
3323 return FALSE; -- Parent row still in startup account
3324 END;
3325
3326 return TRUE;
3327
3328 END check_parents;
3329
3330 PROCEDURE transfer_row IS
3331 -------------------------
3332 BEGIN
3333
3334 -- First do a parental integrity check
3335 IF NOT check_parents THEN
3336 return;
3337 END IF;
3338
3339 -- Need to ensure user entity and route parameter value
3340 -- rebuild in rebuild_ele_input_bal if the dimension route
3341 -- has changed
3342 BEGIN
3343
3344 select route_id, run_dimension_id
3345 into l_route_id, l_run_dimension_id
3346 from pay_dimension_routes
3347 where BALANCE_DIMENSION_ID=stu_rec.BALANCE_DIMENSION_ID
3348 and PRIORITY = stu_rec.PRIORITY;
3349
3350 if (l_route_id <> stu_rec.ROUTE_ID or
3351 l_run_dimension_id <> stu_rec.RUN_DIMENSION_ID) then
3352
3353 delete ff_compiled_info_f fci
3354 where fci.formula_id in (
3355 select fdi.formula_id
3356 from ff_fdi_usages_f fdi
3357 where FDI.usage = 'D'
3358 and exists (select null from
3359 ff_database_items dbi
3360 where fdi.item_name = dbi.user_name
3361 and exists (select null from
3362 ff_user_entities ent
3363 where ent.user_entity_id = dbi.user_entity_id
3364 and ent.creator_type in ('B', 'RB')
3365 and ent.route_id = stu_rec.route_id)));
3366
3367 delete ff_fdi_usages_f fdi
3368 where FDI.usage = 'D'
3369 and exists (select null from
3370 ff_database_items dbi
3371 where fdi.item_name = dbi.user_name
3372 and exists (select null from
3373 ff_user_entities ent
3374 where ent.user_entity_id = dbi.user_entity_id
3375 and ent.creator_type in ('B', 'RB')
3376 and ent.route_id = stu_rec.route_id));
3377
3378 delete from ff_user_entities ue
3379 where creator_type in ('B', 'RB')
3380 and route_id = stu_rec.ROUTE_ID;
3381 end if;
3382
3383 EXCEPTION WHEN NO_DATA_FOUND THEN
3384 null;
3385 END;
3386
3387 -- Delete potentially existing live dimension route
3388 delete pay_dimension_routes
3389 where BALANCE_DIMENSION_ID=stu_rec.BALANCE_DIMENSION_ID
3390 and PRIORITY = stu_rec.PRIORITY;
3391
3392 BEGIN
3393 insert into pay_dimension_routes
3394 (BALANCE_DIMENSION_ID
3395 ,ROUTE_ID
3396 ,ROUTE_TYPE
3397 ,PRIORITY
3398 ,RUN_DIMENSION_ID
3399 ,BALANCE_TYPE_COLUMN
3400 ,DECODE_REQUIRED)
3401 values
3402 (stu_rec.BALANCE_DIMENSION_ID
3403 ,stu_rec.ROUTE_ID
3404 ,stu_rec.ROUTE_TYPE
3405 ,stu_rec.PRIORITY
3406 ,stu_rec.RUN_DIMENSION_ID
3407 ,stu_rec.BALANCE_TYPE_COLUMN
3408 ,stu_rec.DECODE_REQUIRED);
3409 EXCEPTION WHEN OTHERS THEN
3410 hrrunprc_trace_on;
3411 hr_utility.trace('ins pay dim routes');
3412 hr_utility.trace('bal dim id ' ||
3413 to_char(stu_rec.BALANCE_DIMENSION_ID));
3414 hr_utility.trace('route id ' ||
3415 to_char(stu_rec.ROUTE_ID));
3416 hr_utility.trace('priority ' ||
3417 to_char(stu_rec.PRIORITY));
3418 hr_utility.trace('route type ' ||
3419 stu_rec.ROUTE_TYPE);
3420 hrrunprc_trace_off;
3421 raise;
3422 END;
3423
3424 remove;
3425
3426 END transfer_row;
3427
3428 BEGIN
3429 FOR delivered IN stu LOOP
3430 savepoint new_dimension_route;
3431 stu_rec := delivered;
3432 IF p_phase = 2 THEN transfer_row; END IF;
3433 END LOOP;
3434
3435 END install_dimension_routes;
3436
3437 --****************************************************************************
3438 -- INSTALLATION PROCEDURE FOR : PER_ORG_INFORMATION_TYPES
3439 --****************************************************************************
3440
3441 PROCEDURE install_org_info (p_phase IN number)
3442 ----------------------------------------------
3443 IS
3444 -- Install procedure to transfer startup element classifications into
3445 -- a live account.
3446
3447 l_null_return varchar2(1); -- For 'select null' statements
3448
3452 , description
3449 CURSOR stu -- Selects all rows from startup entity
3450 IS
3451 select org_information_type
3453 , destination
3454 , displayed_org_information_type doit
3455 , legislation_code
3456 , navigation_method
3457 , fnd_application_id
3458 , rowid
3459 from hr_s_org_information_types;
3460
3461 stu_rec stu%ROWTYPE; -- Record for above SELECT
3462
3463 PROCEDURE remove
3464 ----------------
3465 IS
3466 -- Remove row from startup/delivery tables
3467
3468 BEGIN
3469
3470 delete from hr_s_org_information_types
3471 where rowid = stu_rec.rowid;
3472
3473 delete from hr_s_org_info_types_by_class
3474 where org_information_type = stu_rec.org_information_type;
3475
3476 END remove;
3477
3478 PROCEDURE transfer_row
3479 ----------------------
3480 IS
3481 -- Check if a delivered row is needed and insert into the
3482 -- live tables if it is
3483
3484 BEGIN
3485
3486 -- Now comapre the row details. If the rows are identical the delivered row
3487 -- will be deleted.
3488
3489 select null
3490 into l_null_return
3491 from hr_org_information_types
3492 where org_information_type = stu_rec.org_information_type
3493 and nvl(destination,'X') = nvl(stu_rec.destination,'X')
3494 and nvl(displayed_org_information_type,'X') = nvl(stu_rec.doit,'X')
3495 and nvl(legislation_code,'X') = nvl(stu_rec.legislation_code,'X')
3496 and nvl(navigation_method,'X') = nvl(stu_rec.navigation_method,'X');
3497
3498
3499 -- The org information row is exactly the same as installed, so now
3500 -- check the installed types by class. If the next sql statement returns
3501 -- a row, then the installed info types by class are identical to those
3502 -- in the delivery tables.
3503
3504 select null
3505 into l_null_return
3506 from dual
3507 where not exists
3508 ((select ORG_CLASSIFICATION,
3509 ORG_INFORMATION_TYPE,
3510 MANDATORY_FLAG,
3511 ENABLED_FLAG
3512 from hr_s_org_info_types_by_class
3513 where org_information_type = stu_rec.org_information_type
3514 MINUS
3515 select ORG_CLASSIFICATION,
3516 ORG_INFORMATION_TYPE,
3517 MANDATORY_FLAG,
3518 ENABLED_FLAG
3519 from hr_org_info_types_by_class
3520 where org_information_type = stu_rec.org_information_type
3521 )
3522 UNION
3523 (select ORG_CLASSIFICATION,
3524 ORG_INFORMATION_TYPE,
3525 MANDATORY_FLAG,
3526 ENABLED_FLAG
3527 from hr_org_info_types_by_class
3528 where org_information_type = stu_rec.org_information_type
3529 MINUS
3530 select ORG_CLASSIFICATION,
3531 ORG_INFORMATION_TYPE,
3532 MANDATORY_FLAG,
3533 ENABLED_FLAG
3534 from hr_s_org_info_types_by_class
3535 where org_information_type = stu_rec.org_information_type
3536 ));
3537
3538 remove;
3539
3540 return;
3541
3542 EXCEPTION WHEN NO_DATA_FOUND THEN
3543
3544 -- This exception is called when one of the statements above returns no
3545 -- rows. If this happens then all the org_information row and all child
3546 -- types_by_class rows will be inserted. No actual data transfer is to
3547 -- take place in phase 1. The first step is to perform an update
3548 -- on the info type row. If SQL%NOTFOUND is true then the row needs to be
3549 -- inserted. After the info type has been inserted/updated, refresh all
3550 -- child types_by_class rows.
3551
3552 IF p_phase = 1 THEN return; END IF;
3553
3554
3555 update hr_org_information_types
3556 set destination = stu_rec.destination
3557 , displayed_org_information_type = stu_rec.doit
3558 , legislation_code = stu_rec.legislation_code
3559 , navigation_method = stu_rec.navigation_method
3560 where org_information_type = stu_rec.org_information_type;
3561
3562 IF SQL%NOTFOUND THEN
3563
3564
3565 insert into hr_org_information_types
3566 (org_information_type
3567 ,description
3568 ,destination
3569 ,displayed_org_information_type
3570 ,fnd_application_id
3571 ,legislation_code
3572 ,navigation_method
3573 )
3574 values
3575 (stu_rec.org_information_type
3576 ,stu_rec.description
3577 ,stu_rec.destination
3578 ,stu_rec.doit
3579 ,stu_rec.fnd_application_id
3580 ,stu_rec.legislation_code
3584 END IF;
3581 ,stu_rec.navigation_method
3582 );
3583
3585
3586
3587 delete from hr_org_info_types_by_class
3588 where org_information_type = stu_rec.org_information_type;
3589
3590
3591 insert into hr_org_info_types_by_class
3592 (ORG_CLASSIFICATION
3593 ,ORG_INFORMATION_TYPE
3594 ,MANDATORY_FLAG
3595 ,ENABLED_FLAG
3596 )
3597 select org_classification
3598 , org_information_type
3599 , mandatory_flag
3600 , 'Y' -- default to Y at least for now
3601 from hr_s_org_info_types_by_class
3602 where org_information_type = stu_rec.org_information_type;
3603
3604 remove;
3605
3606 END transfer_row;
3607
3608 BEGIN
3609
3610 -- This is the main loop to perform the installation logic. A cursor
3611 -- is opened to control the loop, and each row returned is placed
3612 -- into a record defined within the main procedure so each sub
3613 -- procedure has full access to all returrned columns. For each
3614 -- new row returned, a new savepoint is declared. If at any time
3615 -- the row is in error a rollback iss performed to the savepoint
3616 -- and the next row is returned. Ownership details are checked and
3617 -- if the row is required then the surrogate id is updated and the
3618 -- main transfer logic is called.
3619
3620 FOR delivered IN stu LOOP
3621 savepoint new_org_information_type;
3622
3623 -- Make all cursor columns available to all procedures
3624
3625 stu_rec := delivered;
3626
3627 transfer_row;
3628
3629 END LOOP;
3630
3631 END install_org_info;
3632
3633 --****************************************************************************
3634 -- INSTALLATION PROCEDURE FOR : PER_ASSIGNMENT_INFO_TYPES
3635 --****************************************************************************
3636
3637 PROCEDURE install_ass_info (p_phase IN number)
3638 ----------------------------------------------
3639 IS
3640 -- Install procedure to transfer assignment information into a live account.
3641
3642 l_null_return varchar2(1); -- For 'select null' statements
3643 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3644
3645 CURSOR stu -- Selects all rows from startup entity
3646 IS
3647 select information_type c_true_key
3648 , active_inactive_flag
3649 , description
3650 , legislation_code c_leg_code
3651 , request_id
3652 , program_application_id
3653 , program_id
3654 , program_update_date
3655 , last_update_date
3656 , last_updated_by
3657 , last_update_login
3658 , created_by
3659 , creation_date
3660 , rowid
3661 , multiple_occurences_flag
3662 from hr_s_assignment_info_types;
3663
3664 stu_rec stu%ROWTYPE; -- Cursor for earlier select
3665
3666 PROCEDURE crt_exc (exception_type IN varchar2)
3667 ----------------------------------------------
3668 IS
3669 -- Reports any exceptions during the delivery of startup data to
3670 -- PER_ASSIGNMENT_INFO_TYPES.
3671
3672 BEGIN
3673
3674 -- When the installation procedures encounter an error that cannot
3675 -- be handled, an exception is raised and all work is rolled back
3676 -- to the last savepoint. The installation process then continues
3677 -- with the next primary key to install. The same exception will
3678 -- not be raised more than once.
3679
3680 rollback to new_information_type;
3681
3682 insert_hr_stu_exceptions('per_assignment_info_types'
3683 , 0
3684 , exception_type
3685 , stu_rec.c_true_key);
3686
3687
3688 END crt_exc;
3689
3690 PROCEDURE remove
3691 ----------------
3692 IS
3693 -- Remove a row from either the startup tables or the installed tables
3694
3695 BEGIN
3696
3697 delete from hr_s_assignment_info_types
3698 where rowid = stu_rec.rowid;
3699
3700 END remove;
3701
3702 PROCEDURE transfer_row
3703 ----------------------
3704 IS
3705 -- Check if a delivered row is needed and insert into the
3706 -- live tables if it is
3707
3708 BEGIN
3709
3710 -- Perform a check to see if this primary key has been installed
3711 -- with a legislation code that would make it visible at the same time
3712 -- as this row. Ie: if any legislation code is null within the set of
3713 -- returned rows, then the transfer may not go ahead. If no rows are
3714 -- returned then the delivered row is fine.
3715
3716 BEGIN
3717 select distinct null
3718 into l_null_return
3719 from per_assignment_info_types
3720 where information_type = stu_rec.c_true_key
3721 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
3722 and (legislation_code is null or stu_rec.c_leg_code is null);
3723
3724 crt_exc('Row already created for a visible legislation');
3725
3726 return; -- Row is not to be transferred
3727
3728 EXCEPTION WHEN NO_DATA_FOUND THEN
3729 null;
3730
3731 END;
3732
3736
3733 -- See if the assignment information type exists. If so then remove from the
3734 -- delivery tables, otherwise insert into the live table.
3735
3737 BEGIN
3738 select distinct null
3739 into l_null_return
3740 from per_assignment_info_types
3741 where legislation_code = stu_rec.c_leg_code
3742 and information_type = stu_rec.c_true_key;
3743
3744 remove; -- The row exists
3745
3746 return;
3747
3748 EXCEPTION WHEN NO_DATA_FOUND THEN
3749
3750 IF p_phase = 1 THEN return; END IF;
3751
3752
3753 --+++ Has occurences below meant to have 2 rs ?
3754 BEGIN
3755 insert into per_assignment_info_types
3756 (information_type
3757 ,active_inactive_flag
3758 ,description
3759 ,legislation_code
3760 ,request_id
3761 ,program_application_id
3762 ,program_id
3763 ,program_update_date
3764 ,last_update_date
3765 ,last_updated_by
3766 ,last_update_login
3767 ,created_by
3768 ,creation_date
3769 ,multiple_occurences_flag
3770 )
3771 values
3772 (stu_rec.c_true_key
3773 ,stu_rec.active_inactive_flag
3774 ,stu_rec.description
3775 ,stu_rec.c_leg_code
3776 ,stu_rec.request_id
3777 ,stu_rec.program_application_id
3778 ,stu_rec.program_id
3779 ,stu_rec.program_update_date
3780 ,stu_rec.last_update_date
3781 ,stu_rec.last_updated_by
3782 ,stu_rec.last_update_login
3783 ,stu_rec.created_by
3784 ,stu_rec.creation_date
3785 ,stu_rec.multiple_occurences_flag
3786 );
3787 EXCEPTION WHEN OTHERS THEN
3788 hrrunprc_trace_on;
3789 hr_utility.trace('ins per_ass_info_type');
3790 hr_utility.trace('information_type ' ||
3791 stu_rec.c_true_key);
3792 hr_utility.trace(':lc: ' || ':' ||
3793 stu_rec.c_leg_code || ':');
3794 hrrunprc_trace_off;
3795 raise;
3796 END;
3797
3798
3799 remove;
3800
3801 END;
3802
3803 END transfer_row;
3804
3805 BEGIN
3806
3807 -- This is the main loop to perform the installation logic. A cursor
3808 -- is opened to control the loop, and each row returned is placed
3809 -- into a record defined within the main procedure so each sub
3810 -- procedure has full access to all returned columns. For each
3811 -- new row returned, a new savepoint is declared. If at any time
3812 -- the row is in error a rollback is performed to the savepoint
3813 -- and the next row is returned. Ownership details are checked and
3814 -- if the row is required then the surrogate id is updated and the
3815 -- main transfer logic is called.
3816
3817 FOR delivered IN stu LOOP
3818 savepoint new_information_type;
3819
3820 -- Make all cursor columns available to all procedures
3821 stu_rec := delivered;
3822
3823 transfer_row;
3824
3825 END LOOP;
3826
3827 END install_ass_info;
3828
3829 --****************************************************************************
3830 -- INSTALLATION PROCEDURE FOR : PAY_LEGISLATIVE FIELD INFO
3831 --****************************************************************************
3832
3833 PROCEDURE install_leg_field (phase IN number)
3834 ---------------------------------------------
3835 IS
3836 l_null_return varchar2(1); -- used for 'select null' statements
3837
3838 CURSOR stu -- Selects all rows from startup entity
3839 IS
3840 select field_name
3841 , legislation_code
3842 , prompt
3843 , validation_name
3844 , validation_type
3845 , target_location
3846 , rule_mode
3847 , rule_type
3848 , PROMPT_MESSAGE
3849 , IN_LINE_MESSAGE
3850 , QUICK_TIP_MESSAGE
3851 , BUBBLE_TIP_MESSAGE
3852 , category
3853 , rowid
3854 from hr_s_legislative_field_info;
3855
3856 stu_rec stu%ROWTYPE; -- Record for the above select
3857
3858 PROCEDURE remove
3859 ----------------
3860 IS
3861 BEGIN
3862
3863 delete from hr_s_legislative_field_info
3864 where rowid = stu_rec.rowid;
3865
3866 END remove;
3867
3868 PROCEDURE transfer_row
3869 ----------------------
3870 IS
3871 -- Check if a delivered row is needed and insert into the
3872 -- live tables if it is.
3873
3874 l_prompt pay_legislative_field_info.prompt%type;
3875 l_val_type pay_legislative_field_info.validation_type%type;
3876 l_rule_mode pay_legislative_field_info.rule_mode%type;
3877 l_PROMPT_MESSAGE pay_legislative_field_info.PROMPT_MESSAGE%type;
3878 l_IN_LINE_MESSAGE pay_legislative_field_info.IN_LINE_MESSAGE%type;
3879 l_QUICK_TIP_MESSAGE pay_legislative_field_info.QUICK_TIP_MESSAGE%type;
3880 l_BUBBLE_TIP_MESSAGE pay_legislative_field_info.BUBBLE_TIP_MESSAGE%type;
3881 l_category pay_legislative_field_info.category%type;
3885
3882 l_target_location pay_legislative_field_info.target_location%type;
3883 l_rowid rowid;
3884 BEGIN
3886 BEGIN
3887 select prompt
3888 , validation_type
3889 , rule_mode
3890 , PROMPT_MESSAGE
3891 , IN_LINE_MESSAGE
3892 , QUICK_TIP_MESSAGE
3893 , BUBBLE_TIP_MESSAGE
3894 , category
3895 , target_location
3896 , rowid
3897 into l_prompt
3898 , l_val_type
3899 , l_rule_mode
3900 , l_PROMPT_MESSAGE
3901 , l_IN_LINE_MESSAGE
3902 , l_QUICK_TIP_MESSAGE
3903 , l_BUBBLE_TIP_MESSAGE
3904 , l_category
3905 , l_target_location
3906 , l_rowid
3907 from pay_legislative_field_info
3908 where field_name = stu_rec.field_name
3909 and legislation_code = stu_rec.legislation_code
3910 and rule_type = stu_rec.rule_type
3911 and nvl(target_location,'~') = nvl(stu_rec.target_location,'~')
3912 and nvl(validation_name,'~') = nvl(stu_rec.validation_name,'~');
3913 EXCEPTION WHEN TOO_MANY_ROWS THEN
3914 hrrunprc_trace_on;
3915 hr_utility.trace('sel pay_legislative_field_info TMR');
3916 hr_utility.trace('field_name ' ||
3917 stu_rec.field_name);
3918 hr_utility.trace('rule_type ' ||
3919 stu_rec.rule_type);
3920 hr_utility.trace('target_location ' ||
3921 stu_rec.target_location);
3922 hr_utility.trace('validation_name ' ||
3923 stu_rec.validation_name);
3924 hr_utility.trace(':lc: ' || ':' ||
3925 stu_rec.legislation_code || ':');
3926 hrrunprc_trace_off;
3927 raise;
3928 END;
3929
3930 IF l_prompt <> stu_rec.prompt
3931 OR l_val_type <> stu_rec.validation_type
3932 OR l_rule_mode <> stu_rec.rule_mode
3933 OR l_PROMPT_MESSAGE <> stu_rec.PROMPT_MESSAGE
3934 OR l_IN_LINE_MESSAGE <> stu_rec.IN_LINE_MESSAGE
3935 OR l_QUICK_TIP_MESSAGE <> stu_rec.QUICK_TIP_MESSAGE
3936 OR l_BUBBLE_TIP_MESSAGE <> stu_rec.BUBBLE_TIP_MESSAGE
3937 OR l_category <> stu_rec.category
3938 OR l_target_location <> stu_rec.target_location THEN
3939
3940
3941 IF phase = 1 THEN return; END IF;
3942
3943
3944 update pay_legislative_field_info
3945 set
3946 prompt = stu_rec.prompt
3947 , validation_name = stu_rec.validation_name
3948 , validation_type = stu_rec.validation_type
3949 , target_location = stu_rec.target_location
3950 , rule_mode = stu_rec.rule_mode
3951 , PROMPT_MESSAGE = stu_rec.PROMPT_MESSAGE
3952 , IN_LINE_MESSAGE = stu_rec.IN_LINE_MESSAGE
3953 , QUICK_TIP_MESSAGE = stu_rec.QUICK_TIP_MESSAGE
3954 , BUBBLE_TIP_MESSAGE = stu_rec.BUBBLE_TIP_MESSAGE
3955 , CATEGORY = stu_rec.CATEGORY
3956 where rowid = l_rowid;
3957
3958 END IF;
3959
3960 remove;
3961
3962 EXCEPTION WHEN NO_DATA_FOUND THEN
3963
3964 -- Row needs to be inserted
3965
3966 IF phase = 1 THEN return; END IF;
3967
3968
3969 BEGIN
3970 insert into pay_legislative_field_info
3971 (field_name
3972 ,legislation_code
3973 ,prompt
3974 ,validation_name
3975 ,validation_type
3976 ,target_location
3977 ,rule_mode
3978 ,rule_type
3979 ,PROMPT_MESSAGE
3980 ,IN_LINE_MESSAGE
3981 ,QUICK_TIP_MESSAGE
3982 ,BUBBLE_TIP_MESSAGE
3983 ,CATEGORY
3984 )
3985 values
3986 (stu_rec.field_name
3987 ,stu_rec.legislation_code
3988 ,stu_rec.prompt
3989 ,stu_rec.validation_name
3990 ,stu_rec.validation_type
3991 ,stu_rec.target_location
3992 ,stu_rec.rule_mode
3993 ,stu_rec.rule_type
3994 ,stu_rec.PROMPT_MESSAGE
3995 ,stu_rec.IN_LINE_MESSAGE
3996 ,stu_rec.QUICK_TIP_MESSAGE
3997 ,stu_rec.BUBBLE_TIP_MESSAGE
3998 ,stu_rec.CATEGORY
3999 );
4000 EXCEPTION WHEN OTHERS THEN
4001 hrrunprc_trace_on;
4002 hr_utility.trace('ins pay_leg_field_info');
4003 hr_utility.trace('field_name ' ||
4004 stu_rec.field_name);
4005 hr_utility.trace('rule_type ' ||
4006 stu_rec.rule_type);
4007 hr_utility.trace('rule_mode ' ||
4008 stu_rec.rule_mode);
4009 hr_utility.trace(':lc: ' || ':' ||
4010 stu_rec.legislation_code || ':');
4011 hrrunprc_trace_off;
4012 raise;
4013 END;
4014
4015
4016
4017 -- Delete the row now it has been inserted
4018
4022
4019 remove;
4020
4021 END transfer_row;
4023 BEGIN
4024
4025 FOR delivered IN stu LOOP
4026 stu_rec := delivered;
4027 transfer_row;
4028 END LOOP;
4029
4030 END install_leg_field;
4031
4032 --****************************************************************************
4033 -- INSTALLATION PROCEDURE FOR : PAY_PAYMENT_TYPES
4034 --****************************************************************************
4035
4036
4037 PROCEDURE install_payment_types (p_phase IN number)
4038 ---------------------------------------------------
4039 IS
4040 l_null_return varchar2(1); -- Used for 'select null' statements
4041 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
4042
4043 CURSOR stu
4044 IS
4045 select payment_type_id c_surrogate_key
4046 , territory_code
4047 , currency_code
4048 , category
4049 , payment_type_name c_true_key
4050 , allow_as_default
4051 , description
4052 , pre_validation_required
4053 , procedure_name
4054 , validation_days
4055 , validation_value
4056 , last_update_date
4057 , last_updated_by
4058 , last_update_login
4059 , created_by
4060 , creation_date
4061 , rowid
4062 from hr_s_payment_types;
4063
4064 stu_rec stu%ROWTYPE; -- Record for above SELECT
4065
4066
4067 PROCEDURE check_next_sequence
4068 -----------------------------
4069 IS
4070
4071 v_sequence_number number(15);
4072 v_min_delivered number(15);
4073 v_max_delivered number(15);
4074 v_max_live number(15);
4075 cnt number(15);
4076
4077 -- Surrogate id conflicts may arise from two scenario's:
4078 -- 1. Where the newly select sequence value conflicts with values
4079 -- in the STU tables.
4080 -- 2. Where selected surrogate keys, from the installed tables,
4081 -- conflict with other rows in the STU tables.
4082 --
4083 -- Both of the above scenario's are tested for.
4084 -- The first is a simple match, where if a value is detected in the
4085 -- STU tables and the installed tables then a conflict is detected. In
4086 -- This instance all STU surrogate keys, for this table, are updated.
4087 -- The second is tested for using the sequences.
4088 -- If the next value from the live sequence is within the range of
4089 -- delivered surrogate id's then the live sequence must be incremented.
4090 -- If no action is taken, then duplicates may be introduced into the
4091 -- delivered tables, and child rows may be totally invalidated.
4092
4093 BEGIN
4094
4095
4096 BEGIN --check that the installed id's will not conflict
4097 --with the delivered values
4098
4099
4100 select count(*)
4101 into cnt
4102 from hr_s_payment_types;
4103
4104 If cnt=0 then return; end if;
4105
4106
4107 select distinct null
4108 into l_null_return
4109 from pay_payment_types a
4110 where exists
4111 (select null
4112 from hr_s_payment_types b
4113 where a.payment_type_id = b.payment_type_id
4114 );
4115
4116 --conflict may exist
4117 --update all payment_type_id's to remove conflict
4118
4119 update /*+NO_INDEX*/ hr_s_payment_types
4120 set payment_type_id = payment_type_id - 50000000;
4121
4122 update hr_s_application_ownerships
4123 set key_value = key_value - 50000000
4124 where key_name = 'PAYMENT_TYPE_ID';
4125
4126 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4127
4128 END; --check of payment_type_id
4129
4130 select min(payment_type_id) - (count(*) *3)
4131 , max(payment_type_id) + (count(*) *3)
4132 into v_min_delivered
4133 , v_max_delivered
4134 from hr_s_payment_types;
4135
4136 select max(payment_type_id)
4137 into v_max_live
4138 from pay_payment_types;
4139
4140 select pay_payment_types_s.nextval
4141 into v_sequence_number
4142 from dual;
4143
4144 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
4145 OR (v_sequence_number < v_max_live) THEN
4146
4147 IF v_max_live > v_max_delivered THEN
4148
4149 hr_legislation.munge_sequence('PAY_PAYMENT_TYPES_S',
4150 v_sequence_number,
4151 v_max_live);
4152 ELSE
4153
4154 hr_legislation.munge_sequence('PAY_PAYMENT_TYPES_S',
4155 v_sequence_number,
4156 v_max_delivered);
4157 END IF;
4158 END IF;
4159
4160 END check_next_sequence;
4161
4162 PROCEDURE crt_exc (exception_type IN varchar2)
4163 ----------------------------------------------
4164 IS
4165
4166 BEGIN
4167
4168 -- When the installation procedures encounter an error that cannot
4169 -- be handled, an exception is raised and all work is rolled back
4170 -- to the last savepoint. The installation process then continues
4171 -- with the next primary key to install. The same exception will
4172 -- not be raised more than once.
4173
4177 , stu_rec.c_surrogate_key
4174 rollback to new_payment_type_name;
4175
4176 insert_hr_stu_exceptions ('pay_payment_types'
4178 , exception_type
4179 , stu_rec.c_true_key);
4180
4181
4182 END crt_exc;
4183
4184 PROCEDURE update_uid
4185 --------------------
4186 IS
4187 -- Subprogram to update surrogate UID and all occurrences in child rows
4188
4189 BEGIN
4190
4191 BEGIN
4192
4193 select distinct payment_type_id
4194 into l_new_surrogate_key
4195 from pay_payment_types
4196 where payment_type_name = stu_rec.c_true_key
4197 and ((territory_code is NULL and stu_rec.territory_code is NULL)
4198 or stu_rec.territory_code= territory_code);
4199
4200
4201 EXCEPTION WHEN NO_DATA_FOUND THEN
4202
4203 select pay_payment_types_s.nextval
4204 into l_new_surrogate_key
4205 from dual;
4206 END;
4207
4208 -- Update all child entities
4209
4210
4211 update hr_s_payment_types
4212 set payment_type_id = l_new_surrogate_key
4213 where payment_type_id = stu_rec.c_surrogate_key;
4214
4215 update hr_s_application_ownerships
4216 set key_value = to_char(l_new_surrogate_key)
4217 where key_value = to_char(stu_rec.c_surrogate_key)
4218 and key_name = 'PAYMENT_TYPE_ID';
4219 END update_uid;
4220
4221 PROCEDURE remove
4222 ----------------
4223 IS
4224 -- Remove a row from either the startup tables or the installed tables
4225
4226 BEGIN
4227
4228 delete from hr_s_payment_types
4229 where rowid = stu_rec.rowid;
4230
4231
4232 END remove;
4233
4234 PROCEDURE transfer_row
4235 ----------------------
4236 IS
4237 -- Check if a delivered row is needed and insert into the
4238 -- live tables if it is.
4239 --
4240 -- #310520. Change to update the row if it already exists. This
4241 -- differs from the previous functionality, which only ever did
4242 -- inserts, and wouldn't handle updates.
4243 --
4244 BEGIN
4245 IF p_phase = 1 THEN
4246 return;
4247 END IF;
4248
4249 update pay_payment_types
4250 set payment_type_id = stu_rec.c_surrogate_key
4251 , currency_code = stu_rec.currency_code
4252 , category = stu_rec.category
4253 , allow_as_default = stu_rec.allow_as_default
4254 , description = stu_rec.description
4255 , pre_validation_required = stu_rec.pre_validation_required
4256 , procedure_name = stu_rec.procedure_name
4257 , validation_days = stu_rec.validation_days
4258 , validation_value = stu_rec.validation_value
4259 , last_update_date = stu_rec.last_update_date
4260 , last_updated_by = stu_rec.last_updated_by
4261 , last_update_login = stu_rec.last_update_login
4262 , created_by = stu_rec.created_by
4263 , creation_date = stu_rec.creation_date
4264 where payment_type_name = stu_rec.c_true_key
4265 and ((territory_code is NULL and stu_rec.territory_code is NULL)
4266 or stu_rec.territory_code= territory_code);
4267 IF SQL%NOTFOUND THEN
4268
4269 -- Row does not exist so insert
4270
4271 BEGIN
4272 insert into pay_payment_types
4273 (payment_type_id
4274 ,territory_code
4275 ,currency_code
4276 ,category
4277 ,payment_type_name
4278 ,allow_as_default
4279 ,description
4280 ,pre_validation_required
4281 ,procedure_name
4282 ,validation_days
4283 ,validation_value
4284 ,last_update_date
4285 ,last_updated_by
4286 ,last_update_login
4287 ,created_by
4288 ,creation_date
4289 )
4290 values
4291 (stu_rec.c_surrogate_key
4292 ,stu_rec.territory_code
4293 ,stu_rec.currency_code
4294 ,stu_rec.category
4295 ,stu_rec.c_true_key
4296 ,stu_rec.allow_as_default
4297 ,stu_rec.description
4298 ,stu_rec.pre_validation_required
4299 ,stu_rec.procedure_name
4300 ,stu_rec.validation_days
4301 ,stu_rec.validation_value
4302 ,stu_rec.last_update_date
4303 ,stu_rec.last_updated_by
4304 ,stu_rec.last_update_login
4305 ,stu_rec.created_by
4306 ,stu_rec.creation_date
4307 );
4308 EXCEPTION WHEN OTHERS THEN
4309 hrrunprc_trace_on;
4310 hr_utility.trace('ins payment_types');
4311 hr_utility.trace('type_id ' ||
4312 to_char(stu_rec.c_surrogate_key));
4313 hr_utility.trace('type_name ' ||
4314 stu_rec.c_true_key);
4315 hr_utility.trace('currency_code ' ||
4316 stu_rec.currency_code);
4317 hrrunprc_trace_off;
4318 raise;
4319 END;
4320
4321 END IF;
4322 remove;
4323 END transfer_row;
4324
4325 BEGIN
4326
4327 -- This is the main loop to perform the installation logic. A cursor
4328 -- is opened to control the loop, and each row returned is placed
4332 -- the row is in error a rollback is performed to the savepoint
4329 -- into a record defined within the main procedure so each sub
4330 -- procedure has full access to all returned columns. For each
4331 -- new row returned, a new savepoint is declared. If at any time
4333 -- and the next row is returned. Ownership details are checked and
4334 -- if the row is required then the surrogate id is updated and the
4335 -- main transfer logic is called.
4336
4337 IF p_phase = 1 THEN check_next_sequence; END IF;
4338
4339 FOR delivered IN stu LOOP
4340 savepoint new_payment_type_name;
4341 stu_rec := delivered;
4342
4343 IF p_phase = 2 THEN
4344 l_new_surrogate_key := stu_rec.c_surrogate_key;
4345 END IF;
4346
4347 IF p_phase = 1 THEN update_uid; END IF;
4348 transfer_row;
4349 END LOOP;
4350
4351 END install_payment_types;
4352
4353 --****************************************************************************
4354 -- INSTALLATION PROCEDURE FOR : PER_USER_ROWS
4355 --****************************************************************************
4356
4357 PROCEDURE install_urows (p_phase IN NUMBER)
4358 -------------------------------------------
4359 IS
4360 row_in_error exception;
4361 l_current_proc varchar2(80) := 'hr_legislation.install_urows';
4362 l_new_user_row_id number(15);
4363 l_null_return varchar2(1);
4364 l_last_urow_id number(15);
4365
4366 -- This cursor returns the earliest record for each user_row_id,
4367 -- which is mandatory to examine matching between pay_user_rows_f
4368 -- and hr_s_user_rows_f.
4369
4370 CURSOR c_distinct
4371 IS
4372 --
4373 select r1.effective_start_date
4374 , r1.user_row_id c_surrogate_key
4375 , r1.row_low_range_or_name c_true_key
4376 , r1.row_high_range
4377 , r1.legislation_code
4378 , r1.legislation_subgroup
4379 , r1.user_table_id
4380 from hr_s_user_rows_f r1
4381 where not exists(
4382 select null
4383 from hr_s_user_rows_f r2
4384 where r2.user_row_id = r1.user_row_id
4385 and r2.effective_start_date < r1.effective_start_date);
4386
4387 CURSOR c_each_row (pc_user_row_id varchar2)
4388 IS
4389 -- The primary key has already been selected using the above cursor.
4390 -- This cursor accepts the primary key as a parameter and selects all
4391 -- date effective rows for it.
4392
4393 select *
4394 from hr_s_user_rows_f
4395 where user_row_id = pc_user_row_id;
4396
4397 CURSOR c_col_inst (p_user_row_id number)
4398 IS
4399 -- select all child user column instances for the current user row
4400
4401 select *
4402 from hr_s_user_column_instances_f
4403 where user_row_id = p_user_row_id;
4404
4405 r_distinct c_distinct%ROWTYPE;
4406 r_each_row c_each_row%ROWTYPE;
4407
4408
4409 PROCEDURE check_next_sequence
4410 -----------------------------
4411 IS
4412
4413 v_sequence_number number(15);
4414 v_min_delivered number(15);
4415 v_max_delivered number(15);
4416 v_max_live number(15);
4417 cnt number(15);
4418
4419 -- Surrogate id conflicts may arise from two scenario's:
4420 -- 1. Where the newly select sequence value conflicts with values
4421 -- in the STU tables.
4422 -- 2. Where selected surrogate keys, from the installed tables,
4423 -- conflict with other rows in the STU tables.
4424 --
4425 -- Both of the above scenario's are tested for.
4426 -- The first is a simple match, where if a value is detected in the
4427 -- STU tables and the installed tables then a conflict is detected. In
4428 -- This instance all STU surrogate keys, for this table, are updated.
4429 -- The second is tested for using the sequences.
4430 -- If the next value from the live sequence is within the range of
4431 -- delivered surrogate id's then the live sequence must be incremented.
4432 -- If no action is taken, then duplicates may be introduced into the
4433 -- delivered tables, and child rows may be totally invalidated.
4434
4435 BEGIN
4436
4437
4438 BEGIN --check that the installed id's will not conflict
4439 --with the delivered values
4440
4441
4442 select count(*)
4443 into cnt
4444 from hr_s_user_rows_f;
4445
4446 If cnt=0 then return; end if;
4447
4448
4449 select distinct null
4450 into l_null_return
4451 from pay_user_rows_f a
4452 where exists
4453 (select null
4454 from hr_s_user_rows_f b
4455 where a.user_row_id = b.user_row_id
4456 );
4457
4458 --conflict may exist
4459 --update all user_row_id's to remove conflict
4460
4461 update /*+NO_INDEX*/ hr_s_user_rows_f
4462 set user_row_id = user_row_id - 50000000;
4463
4464 update /*+NO_INDEX*/ hr_s_user_column_instances_f
4465 set user_row_id = user_row_id - 50000000;
4466
4467 update hr_s_application_ownerships
4468 set key_value = key_value - 50000000
4469 where key_name = 'USER_ROW_ID';
4470
4471 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4472
4473 END; --check of user_row_id
4477 select min(user_row_id) - (count(*) *3)
4474
4475
4476
4478 , max(user_row_id) + (count(*) *3)
4479 into v_min_delivered
4480 , v_max_delivered
4481 from hr_s_user_rows_f;
4482
4483 select max(user_row_id)
4484 into v_max_live
4485 from pay_user_rows_f;
4486
4487 select pay_user_rows_s.nextval
4488 into v_sequence_number
4489 from dual;
4490
4491 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
4492 OR (v_sequence_number < v_max_live) THEN
4493
4494 IF v_max_live > v_max_delivered THEN
4495
4496 hr_legislation.munge_sequence('PAY_USER_ROWS_S',
4497 v_sequence_number,
4498 v_max_live);
4499 ELSE
4500
4501 hr_legislation.munge_sequence('PAY_USER_ROWS_S',
4502 v_sequence_number,
4503 v_max_delivered);
4504 END IF;
4505 END IF;
4506
4507 END check_next_sequence;
4508
4509
4510 PROCEDURE crt_exc (exception_type IN varchar2)
4511 ----------------------------------------------
4512 IS
4513 BEGIN
4514
4515 -- When the installation procedures encounter an error that cannot
4516 -- be handled, an exception is raised and all work is rolled back
4517 -- to the last savepoint. The installation process then continues
4518 -- with the next primary key to install. The same exception will
4519 -- not be raised more than once.
4520
4521 rollback to new_row_low_range_or_name;
4522
4523 insert_hr_stu_exceptions('pay_user_rows_f'
4524 , r_distinct.c_surrogate_key
4525 , exception_type
4526 , r_distinct.c_true_key);
4527
4528
4529 END crt_exc;
4530
4531 PROCEDURE remove (v_id IN number)
4532 ---------------------------------
4533 IS
4534 -- subprogram to delete a row from the delivery tables, and all child
4535 -- application ownership rows
4536
4537 BEGIN
4538
4539 delete from hr_s_user_rows_f
4540 where user_row_id = v_id;
4541
4542 delete from hr_s_user_column_instances_f
4543 where user_row_id = v_id;
4544
4545 END remove;
4546
4547 PROCEDURE update_uid
4548 --------------------
4549 IS
4550 -- subprogram to update surrogate UID and all occurrences in child rows
4551
4552 BEGIN
4553
4554 -- See if this primary key is already installed. If so then the sorrogate
4555 -- key of the delivered row must be updated to the value in the installed
4556 -- tables. If the row is not already present then select the next value
4557 -- from the sequence. In either case all rows for this primary key must
4558 -- be updated, as must all child references to the old surrogate uid.
4559
4560 --
4561 --
4562 BEGIN
4563 --
4564 select distinct user_row_id
4565 into l_new_user_row_id
4566 from pay_user_rows_f
4567 where user_table_id = r_distinct.user_table_id
4568 and row_low_range_or_name = r_distinct.c_true_key
4569 and nvl(row_high_range, 'NULL') =
4570 nvl(r_distinct.row_high_range, 'NULL')
4571 and effective_start_date = r_distinct.effective_start_date
4572 and business_Group_id is null
4573 and nvl(legislation_code, 'x') = nvl(r_distinct.legislation_code, 'x');
4574
4575 EXCEPTION WHEN NO_DATA_FOUND THEN
4576
4577 select pay_user_rows_s.nextval
4578 into l_new_user_row_id
4579 from dual;
4580 --
4581 WHEN TOO_MANY_ROWS THEN
4582 --
4583 -- 1550308. Trap the error whereby there is more than one
4584 -- User Row ID for a row_low_range_or_name or rlron and
4585 -- row_high_range combination in the live table.
4586 --
4587 crt_exc('Non unique User Row ID for user row range or name');
4588 --
4589 END;
4590
4591
4592 update hr_s_user_rows_f
4593 set user_row_id = l_new_user_row_id
4594 where user_row_id = r_distinct.c_surrogate_key;
4595
4596
4597 update hr_s_application_ownerships
4598 set key_value = to_char(l_new_user_row_id)
4599 where key_value = to_char(r_distinct.c_surrogate_key)
4600 and key_name = 'USER_ROW_ID';
4601
4602
4603 update hr_s_user_column_instances_f
4604 set user_row_id = l_new_user_row_id
4605 where user_row_id = r_distinct.c_surrogate_key;
4606
4607 END update_uid;
4608
4609 FUNCTION check_parents RETURN BOOLEAN
4610 -------------------------------------
4611 IS
4612 -- Check the integrity of the references to parent data, before allowing
4613 -- data to be installed. No parents can exist in the startup tables, since
4614 -- this will violate constraints when the row is installed, also the
4615 -- parent uid's must exist in the installed tables already.
4616 -- This function will RETURN TRUE if a parent row still exists in the
4617 -- delivery account. All statements drop through to a RETURN FALSE.
4618
4619 BEGIN
4620
4624
4621 -- This procedure is only called in phase 2. The logic to check if
4622 -- a given parental foriegn key exists is split into two parts for
4623 -- every foreign key. The first select from the delivery tables.
4625 -- If a row is found then the installation of the parent must have
4626 -- failed, and this installation must not go ahead. If no data is
4627 -- found, ie: an exception is raised, the installation is valid.
4628 -- The second check looks for a row in the live tables. If no rows
4629 -- are returned then this installation is invalid, since this means
4630 -- that the parent referenced by this row is not present in the
4631 -- live tables.
4632
4633 -- Return code of true indicates that all parental data is correct.
4634
4635 BEGIN
4636 -- Check first parent does not exist in the delivery tables
4637
4638 select null
4639 into l_null_return
4640 from hr_s_user_tables
4641 where user_table_id = r_each_row.user_table_id;
4642
4643
4644 crt_exc('Parent user table still exists in delivery tables');
4645
4646 return FALSE; -- Parent still exists, ignore this row
4647
4648 EXCEPTION WHEN NO_DATA_FOUND THEN
4649 null;
4650
4651 END;
4652
4653 BEGIN
4654 -- Check that the parent exists in the live tables
4655
4656
4657 select null
4658 into l_null_return
4659 from pay_user_tables
4660 where user_table_id = r_each_row.user_table_id;
4661
4662 EXCEPTION WHEN NO_DATA_FOUND THEN
4663
4664 crt_exc('Parent user table does not exist in live tables');
4665 return FALSE;
4666
4667 END;
4668
4669 return TRUE; -- Logic drops through to this statement
4670
4671 END check_parents;
4672
4673 FUNCTION valid_ownership RETURN BOOLEAN
4674 ---------------------------------------
4675 IS
4676 -- Test ownership of this current row
4677
4678 BEGIN
4679 -- This function is split into three distinct parts. The first
4680 -- checks to see if a row exists with the same primary key, for a
4681 -- business group that would have access to the delivered row. The
4682 -- second checks details for data created in other legislations,
4683 -- in case data is either created with a null legislation or the
4684 -- delivered row has a null legislation. The last check examines
4685 -- if this data is actually required for a given install by examining
4686 -- the product installation table, and the ownership details for
4687 -- this row.
4688
4689 -- A return code of TRUE indicates that the row is required.
4690
4691
4692 BEGIN
4693 -- Perform a check to see if the primary key has been created within
4694 -- a visible business group. Ie: the business group is for the same
4695 -- legislation as the delivered row, or the delivered row has a null
4696 -- legislation. If no rows are returned then the primary key has not
4697 -- already been created by a user.
4698
4699 --
4700 -- #271139 - hitting a problem because the row_low_range_or_name is
4701 -- not the true key on its own; it's only unique for the user table.
4702 -- Add the user table id and row high range to the select criteria.
4703 --
4704 -- Further fix necessary to cater for the possibility that the
4705 -- high range may be null. Must put NVL on both sides, otherwise
4706 -- matching rows with null row_high_ranges are not detected.
4707 --
4708 select distinct null
4709 into l_null_return
4710 from pay_user_rows_f a
4711 where a.user_table_id = r_distinct.user_table_id
4712 and a.effective_start_date = r_distinct.effective_start_date
4713 and a.row_low_range_or_name = r_distinct.c_true_key
4714 and nvl(row_high_range, 'NULL') =
4715 nvl(r_distinct.row_high_range, 'NULL')
4716 and a.business_group_id is not null
4717 and exists (select null from per_business_groups b
4718 where b.business_group_id = a.business_group_id
4719 and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
4720
4721 crt_exc('Row already created in a business group');
4722
4723 return FALSE; -- Indicates this row is not to be transferred
4724
4725 EXCEPTION WHEN NO_DATA_FOUND THEN
4726 null;
4727
4728 END;
4729
4730 -- Now perform a check to see if this primary key has been installed
4731 -- with a legislation code that would make it visible at the same time
4732 -- as this row. Ie: if any legislation code is null within the set of
4733 -- returned rows, then the transfer may not go ahead. If no rows are
4734 -- returned then the delivered row is fine.
4735
4736 BEGIN
4737 --
4738 -- #271139 - hitting a problem because the row_low_range_or_name is
4739 -- not the true key on its own; it's only unique for the user table.
4740 -- Add the user table id and row high range to the select criteria.
4741 --
4742 --
4743 -- Further fix necessary to cater for the possibility that the
4744 -- high range may be null. Must put NVL on both sides, otherwise
4745 -- matching rows with null row_high_ranges are not detected.
4746 --
4747 select distinct null
4748 into l_null_return
4752 and nvl(row_high_range, 'NULL') =
4749 from pay_user_rows_f
4750 where row_low_range_or_name = r_distinct.c_true_key
4751 and effective_start_date = r_distinct.effective_start_date
4753 nvl(r_distinct.row_high_range, 'NULL')
4754 and user_table_id = r_distinct.user_table_id
4755 and nvl(legislation_code,'x') <>
4756 nvl(r_distinct.legislation_code,'x')
4757 and (
4758 legislation_code is null
4759 or r_distinct.legislation_code is null
4760 );
4761
4762
4763 crt_exc('Row already created for a visible legislation');
4764 return FALSE;
4765
4766 EXCEPTION WHEN NO_DATA_FOUND THEN
4767 null;
4768
4769 END;
4770
4771 -- The last check examines the product installation table, and the
4772 -- ownership details for the delivered row. By examining these
4773 -- tables the row is either deleted or not. If the delivered row
4774 -- is 'stamped' with a legislation subgroup, then a check must be
4775 -- made to see if that subgroup is active or not. This check only
4776 -- needs to be performed in phase 1, since once this decision is
4777 -- made, it is pointless to perform this logic again.
4778
4779 -- The exception is raised within this procedure if no rows are returned
4780 -- in this select statement. If no rows are returned then one of the
4781 -- following is true:
4782 -- 1. No ownership parameters are defined.
4783 -- 2. The products, for which owning parameters are defined, are not
4784 -- installed with as status of 'I'.
4785 -- 3. The data is defined for a legislation subgroup that is not active.
4786
4787 IF p_phase <> 1 THEN return TRUE; END IF;
4788
4789
4790 -- If exception raised then this row is not needed
4791
4792 if (r_distinct.legislation_subgroup is null) then
4793 select distinct null
4794 into l_null_return
4795 from dual
4796 where exists (
4797 select null
4798 from hr_s_application_ownerships a
4799 , fnd_product_installations b
4800 , fnd_application c
4801 where a.key_name = 'USER_ROW_ID'
4802 and a.key_value = r_distinct.c_surrogate_key
4803 and a.product_name = c.application_short_name
4804 and c.application_id = b.application_id
4805 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4806 or
4807 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
4808 else
4809 select distinct null
4810 into l_null_return
4811 from dual
4812 where exists (
4813 select null
4814 from hr_s_application_ownerships a
4815 , fnd_product_installations b
4816 , fnd_application c
4817 where a.key_name = 'USER_ROW_ID'
4818 and a.key_value = r_distinct.c_surrogate_key
4819 and a.product_name = c.application_short_name
4820 and c.application_id = b.application_id
4821 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4822 or
4823 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
4824 and exists (
4825 select null
4826 from hr_legislation_subgroups d
4827 where d.legislation_code = r_distinct.legislation_code
4828 and d.legislation_subgroup = r_distinct.legislation_subgroup
4829 and d.active_inactive_flag = 'A'
4830 );
4831 end if;
4832
4833
4834 return TRUE; -- Indicates row is required
4835
4836 EXCEPTION WHEN NO_DATA_FOUND THEN
4837
4838 -- Row not needed for any installed product
4839
4840
4841 remove(r_distinct.c_surrogate_key);
4842
4843 return FALSE; -- Indicates row not needed
4844
4845 END valid_ownership;
4846
4847 BEGIN
4848 -- Two loops are used here. The main loop which select distinct primary
4849 -- key rows and an inner loop which selects all date effective rows for the
4850 -- primary key. The inner loop is only required in phase two, since only
4851 -- in phase 2 are rows actually transferred. The logic reads as follows:
4852 -- Only deal with rows which have correct ownership details and will
4853 -- not cause integrity problems (valid_ownership).
4854 -- In Phase 1:
4855 -- - Delete delivery rows where the installed rows are identical.
4856 -- - The UNION satement compares delivery rows to installed rows.
4857 -- If the sub query returns any rows, then the delivered
4858 -- tables and the installed tables are different.
4859 -- In Phase 2:
4860 -- - Delete from the installed tables using the surrogate id.
4861 -- - If an installed row is to be replaced, the values of
4862 -- the surrogate keys will be identical at this stage.
4863 -- - Data will then be deleted from the delivery tables.
4864 -- - Call the installation procedure for any child tables, that
4865 -- must be installed within the same commit unit. If any
4866 -- errors occur then rollback to the last declared savepoint.
4867 -- - Check that all integrity rules are still obeyed at the end
4868 -- of the installation (validity_checks).
4872 -- is performed.
4869 -- An exception is used with this procedure 'row_in_error' in case an error
4870 -- is encountered from calling any function. If this is raised, then an
4871 -- exception is entered into the control tables (crt_exc();) and a rollback
4873
4874 IF p_phase = 1 THEN check_next_sequence; END IF;
4875
4876 FOR row_low_range_or_names IN c_distinct LOOP
4877
4878 savepoint new_row_low_range_or_name;
4879 r_distinct := row_low_range_or_names;
4880
4881 BEGIN
4882
4883 IF valid_ownership THEN
4884 -- This row is wanted
4885
4886 IF p_phase = 1 THEN
4887
4888 -- Get new surrogate id and update child references
4889
4890 update_uid;
4891
4892 ELSE
4893 -- Phase = 2
4894
4895 delete from pay_user_column_instances_f
4896 where user_row_id = r_distinct.c_surrogate_key
4897 and business_group_id is null;
4898
4899 delete from pay_user_rows_f
4900 where user_row_id = r_distinct.c_surrogate_key;
4901
4902 FOR each_row IN c_each_row(r_distinct.c_surrogate_key) LOOP
4903 r_each_row := each_row;
4904
4905 IF NOT check_parents THEN RAISE row_in_error; END IF;
4906
4907 BEGIN
4908 insert into pay_user_rows_f
4909 (user_row_id
4910 ,effective_start_date
4911 ,effective_end_date
4912 ,business_group_id
4913 ,legislation_code
4914 ,user_table_id
4915 ,row_low_range_or_name
4916 ,display_sequence
4917 ,legislation_subgroup
4918 ,row_high_range
4919 ,last_update_date
4920 ,last_updated_by
4921 ,last_update_login
4922 ,created_by
4923 ,creation_date
4924 )
4925 values
4926 (each_row.user_row_id
4927 ,each_row.effective_start_date
4928 ,each_row.effective_end_date
4929 ,each_row.business_group_id
4930 ,each_row.legislation_code
4931 ,each_row.user_table_id
4932 ,each_row.row_low_range_or_name
4933 ,each_row.display_sequence
4934 ,each_row.legislation_subgroup
4935 ,each_row.row_high_range
4936 ,each_row.last_update_date
4937 ,each_row.last_updated_by
4938 ,each_row.last_update_login
4939 ,each_row.created_by
4940 ,each_row.creation_date
4941 );
4942 EXCEPTION WHEN OTHERS THEN
4943 hrrunprc_trace_on;
4944 hr_utility.trace('ins pay_user_rows');
4945 hr_utility.trace('table_id ' ||
4946 to_char(each_row.user_table_id));
4947 hr_utility.trace('name ' ||
4948 each_row.row_low_range_or_name);
4949 hr_utility.trace('high_range ' ||
4950 each_row.row_high_range);
4951 hr_utility.trace(':lc: ' || ':' ||
4952 each_row.legislation_code || ':');
4953 hr_utility.trace('eff start date ' ||
4954 to_date(each_row.effective_start_date,'DD-MM-YYYY'));
4955 hrrunprc_trace_off;
4956 raise;
4957 END;
4958
4959 END LOOP each_row;
4960
4961 FOR each_child IN c_col_inst(r_distinct.c_surrogate_key) LOOP
4962
4963 BEGIN
4964
4965 select distinct null
4966 into l_null_return
4967 from pay_user_columns
4968 where user_column_id = each_child.user_column_id;
4969
4970 insert into pay_user_column_instances_f
4971 (user_column_instance_id
4972 ,effective_start_date
4973 ,effective_end_date
4974 ,user_row_id
4975 ,user_column_id
4976 ,business_group_id
4977 ,legislation_code
4978 ,legislation_subgroup
4979 ,value
4980 ,last_update_date
4981 ,last_updated_by
4982 ,last_update_login
4983 ,created_by
4984 ,creation_date)
4985 select pay_user_column_instances_s.nextval
4986 ,each_child.effective_start_date
4987 ,each_child.effective_end_date
4988 ,each_child.user_row_id
4989 ,each_child.user_column_id
4990 ,each_child.business_group_id
4991 ,each_child.legislation_code
4992 ,each_child.legislation_subgroup
4993 ,each_child.value
4994 ,each_child.last_update_date
4995 ,each_child.last_updated_by
4996 ,each_child.last_update_login
4997 ,each_child.created_by
4998 ,each_child.creation_date
4999 from dual;
5000
5001 EXCEPTION WHEN NO_DATA_FOUND THEN
5002 crt_exc('Parent Column not in live tables');
5003 RAISE row_in_error;
5004 WHEN OTHERS THEN
5005 hrrunprc_trace_on;
5006 hr_utility.trace('ins pay_user_col_inst');
5007 hr_utility.trace('value ' ||
5008 each_child.value);
5009 hr_utility.trace('column_id ' ||
5010 to_char(each_child.user_column_id));
5011 hr_utility.trace('row id ' ||
5012 to_char(r_distinct.c_surrogate_key));
5013 hr_utility.trace('row low range ' ||
5017 hr_utility.trace('col eff start date ' ||
5014 r_distinct.c_true_key);
5015 hr_utility.trace('row eff start date ' ||
5016 to_date(r_distinct.effective_start_date, 'DD-MM-YYYY'));
5018 to_date(each_child.effective_start_date, 'DD-MM-YYYY'));
5019 hrrunprc_trace_off;
5020 raise;
5021 END;
5022
5023 END LOOP;
5024
5025 remove(r_distinct.c_surrogate_key);
5026
5027 END IF; -- End phase checking
5028
5029
5030 END IF; --(valid ownership test)
5031
5032 EXCEPTION WHEN row_in_error THEN
5033 rollback to new_row_low_range_or_name;
5034
5035 END;
5036
5037 END LOOP row_low_range_or_names;
5038
5039 END install_urows;
5040
5041 --****************************************************************************
5042 -- INSTALLATION PROCEDURE FOR : HR_STU_HISTORY
5043 --****************************************************************************
5044
5045 FUNCTION maintain_history (p_phase IN number) RETURN BOOLEAN
5046 ------------------------------------------------------------
5047 IS
5048 -- Checks and validates the value of the passed parameter. If the value
5049 -- is within the correct range, rows will be inserted/updated to the
5050 -- history table. If the routine has been called in pahse 2, a row must
5051 -- exist in the history table.
5052
5053 cursor c_legs is
5054 select package_name
5055 from hr_s_history;
5056
5057 v_package_name varchar2(30);
5058
5059 BEGIN
5060
5061 -- First insert a row into the hr_stu_history table
5062
5063 FOR r_legs in c_legs loop
5064
5065 v_package_name := r_legs.package_name;
5066
5067 IF p_phase = 1 OR p_phase = 2 THEN
5068
5069 delete from hr_stu_exceptions;
5070
5071 -- Used to error oinly if in phase 1 but to stop
5072 -- situations where we lose legit Phase 1 exceptions
5073 -- because an adpatch rerun then fails in Phase 2
5074 -- (implit commit due to a resequence?) we will do the
5075 -- removal of stu exception data prior to the adpatch
5076 -- hrrunprc line in the hrglobal driver
5077 IF SQL%FOUND THEN
5078 rollback;
5079 return FALSE;
5080 END IF;
5081
5082 update hr_stu_history
5083 set status = 'Phase '||p_phase
5084 where package_name = v_package_name;
5085
5086 IF SQL%NOTFOUND THEN
5087 -- History row must exist for phase 2
5088 IF p_phase = 2 THEN return FALSE; END IF;
5089
5090 insert into hr_stu_history
5091 (package_name
5092 ,date_of_export
5093 ,date_of_import
5094 ,status
5095 ,legislation_code
5096 )
5097 select distinct package_name
5098 , date_of_export
5099 , sysdate
5100 , 'Phase 1'
5101 , legislation_code
5102 from hr_s_history
5103 where package_name = v_package_name;
5104
5105 END IF;
5106
5107 ELSE
5108 return FALSE; -- Phase value is incorrect.
5109
5110 END IF; --end phase check
5111
5112 END LOOP;
5113
5114 return TRUE;
5115
5116 END maintain_history;
5117
5118 --****************************************************************************
5119 -- INSTALLATION PROCEDURE FOR : PAY_MONETARY_UNITS
5120 --****************************************************************************
5121
5122 PROCEDURE install_monetary (p_phase IN number)
5123 ----------------------------------------------
5124 IS
5125 l_null_return varchar2(1); -- Used for 'select null' statements
5126 l_new_surrogate_key number(15); -- Used to hold the new uid.
5127
5128 CURSOR stu
5129 IS
5130 select monetary_unit_id c_surrogate_key
5131 , currency_code
5132 , business_group_id
5133 , legislation_code c_leg_code
5134 , monetary_unit_name c_true_key
5135 , relative_value
5136 , last_update_date
5137 , last_updated_by
5138 , last_update_login
5139 , created_by
5140 , creation_date
5141 , rowid
5142 from hr_s_monetary_units;
5143
5144 stu_rec stu%ROWTYPE; -- Record definition for above SELECT
5145
5146 PROCEDURE crt_exc (exception_type IN varchar2)
5147 ----------------------------------------------
5148 IS
5149 -- When the installation procedures encounter an error that cannot
5150 -- be handled, an exception is raised and all work is rolled back
5151 -- to the last savepoint. The installation process then continues
5152 -- with the next primary key to install. The same exception will
5153 -- not be raised more than once.
5154
5155 BEGIN
5156 rollback to new_primary_key;
5157
5158 insert_hr_stu_exceptions('PAY_MONETARY_UNITS'
5159 , stu_rec.c_surrogate_key
5160 , exception_type
5161 , stu_rec.c_true_key);
5162
5163
5164 END crt_exc;
5165
5166 PROCEDURE check_next_sequence
5167 -----------------------------
5171 v_min_delivered number(15);
5168 IS
5169
5170 v_sequence_number number(15);
5172 v_max_delivered number(15);
5173 v_max_live number(15);
5174 cnt number(15);
5175
5176 -- Surrogate id conflicts may arise from two scenario's:
5177 -- 1. Where the newly select sequence value conflicts with values
5178 -- in the STU tables.
5179 -- 2. Where selected surrogate keys, from the installed tables,
5180 -- conflict with other rows in the STU tables.
5181 --
5182 -- Both of the above scenario's are tested for.
5183 -- The first is a simple match, where if a value is detected in the
5184 -- STU tables and the installed tables then a conflict is detected. In
5185 -- This instance all STU surrogate keys, for this table, are updated.
5186 -- The second is tested for using the sequences.
5187 -- If the next value from the live sequence is within the range of
5188 -- delivered surrogate id's then the live sequence must be incremented.
5189 -- If no action is taken, then duplicates may be introduced into the
5190 -- delivered tables, and child rows may be totally invalidated.
5191
5192 BEGIN
5193
5194
5195 BEGIN --check that the installed id's will not conflict
5196 --with the delivered values
5197
5198
5199 select count(*)
5200 into cnt
5201 from hr_s_monetary_units;
5202
5203 If cnt=0 then return; end if;
5204
5205
5206 select distinct null
5207 into l_null_return
5208 from pay_monetary_units a
5209 where exists
5210 (select null
5211 from hr_s_monetary_units b
5212 where a.monetary_unit_id = b.monetary_unit_id
5213 );
5214
5215 --conflict may exist
5216 --update all monetary_unit_id's to remove conflict
5217
5218 update hr_s_monetary_units
5219 set monetary_unit_id = monetary_unit_id - 50000000;
5220
5221 update hr_s_application_ownerships
5222 set key_value = key_value - 50000000
5223 where key_name = 'MONETARY_UNIT_ID';
5224
5225 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
5226
5227 END; --check of monetary_unit_id
5228
5229
5230 select min(monetary_unit_id) - (count(*) *3)
5231 , max(monetary_unit_id) + (count(*) *3)
5232 into v_min_delivered
5233 , v_max_delivered
5234 from hr_s_monetary_units;
5235
5236 select max(monetary_unit_id)
5237 into v_max_live
5238 from pay_monetary_units;
5239
5240 select pay_monetary_units_s.nextval
5241 into v_sequence_number
5242 from dual;
5243
5244 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
5245 OR (v_sequence_number < v_max_live) THEN
5246
5247 IF v_max_live > v_max_delivered THEN
5248
5249 hr_legislation.munge_sequence('PAY_MONETARY_UNITS_S',
5250 v_sequence_number,
5251 v_max_live);
5252 ELSE
5253
5254 hr_legislation.munge_sequence('PAY_MONETARY_UNITS_S',
5255 v_sequence_number,
5256 v_max_delivered);
5257 END IF;
5258 END IF;
5259
5260 END check_next_sequence;
5261
5262 PROCEDURE update_uid
5263 --------------------
5264 IS
5265 -- Subprogram to update surrogate UID and all occurrences in child rows
5266
5267 BEGIN
5268
5269 BEGIN
5270 select distinct monetary_unit_id
5271 into l_new_surrogate_key
5272 from pay_monetary_units
5273 where currency_code = stu_rec.currency_code
5274 and monetary_unit_name = stu_rec.c_true_key
5275 and business_group_id is null
5276 and (
5277 (legislation_code is null
5278 and stu_rec.c_leg_code is null)
5279 or (legislation_code = stu_rec.c_leg_code)
5280 );
5281
5282 EXCEPTION WHEN NO_DATA_FOUND THEN
5283
5284 select pay_monetary_units_s.nextval
5285 into l_new_surrogate_key
5286 from dual;
5287
5288 END;
5289
5290 -- Update all child entities
5291
5292
5293 update hr_s_monetary_units
5294 set monetary_unit_id = l_new_surrogate_key
5295 where monetary_unit_id = stu_rec.c_surrogate_key;
5296
5297 update hr_s_application_ownerships
5298 set key_value = to_char(l_new_surrogate_key)
5299 where key_value = to_char(stu_rec.c_surrogate_key)
5300 and key_name = 'MONETARY_UNIT_ID';
5301
5302 END update_uid;
5303
5304 PROCEDURE remove
5305 ----------------
5306 IS
5307 -- Remove a row from either the startup tables or the installed tables
5308
5309 BEGIN
5310
5311 delete from hr_s_monetary_units
5312 where rowid = stu_rec.rowid;
5313
5314 END remove;
5315
5316 FUNCTION valid_ownership RETURN BOOLEAN
5317 ---------------------------------------
5318 IS
5322
5319 -- Test ownership of this current row
5320
5321 BEGIN
5323 -- This routine only operates in phase 1. Rows are present in the
5324 -- table hr_application_ownerships in the delivery account, which
5325 -- dictate which products a piece of data is used for. If the query
5326 -- returns a row then this data is required, and the function will
5327 -- return true. If no rows are returned and an exception is raised,
5328 -- then this row is not required and may be deleted from the delivery
5329 -- tables.
5330
5331 -- If legislation code and subgroup code are included on the delivery
5332 -- tables, a check must be made to determine if the data is defined for
5333 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
5334 -- installation.
5335
5336 -- A return code of TRUE indicates that the row is required.
5337
5338 -- The exception is raised within this procedure if no rows are returned
5339 -- in this select statement. If no rows are returned then one of the
5340 -- following is true:
5341 -- 1. No ownership parameters are defined.
5342 -- 2. The products, for which owning parameters are defined, are not
5343 -- installed with as status of 'I'.
5344 -- 3. The data is defined for a legislation subgroup that is not active.
5345
5346
5347 IF p_phase <> 1 THEN return TRUE; END IF;
5348
5349
5350 select null
5351 into l_null_return
5352 from dual
5353 where exists
5354 (select null
5355 from hr_s_application_ownerships a
5356 , fnd_product_installations b
5357 , fnd_application c
5358 where a.key_name = 'MONETARY_UNIT_ID'
5359 and a.key_value = stu_rec.c_surrogate_key
5360 and a.product_name = c.application_short_name
5361 and c.application_id = b.application_id
5362 and ((b.status = 'I' and c.application_short_name <> 'PQP')
5363 or
5364 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
5365
5366 return TRUE; -- Indicates row is required
5367
5368 EXCEPTION WHEN NO_DATA_FOUND THEN
5369
5370 -- Row not needed for any installed product
5371
5372 remove;
5373
5374 return FALSE; -- Indicates row not needed
5375
5376 END valid_ownership;
5377
5378 PROCEDURE transfer_row
5379 IS
5380 -- Check if a delivered row is needed and insert into the
5381 -- live tables if it is.
5382
5383 -- The procedure checks to see if the same monetary unit has been
5384 -- installed in a contentious business group or legislation.
5385
5386 BEGIN
5387
5388 BEGIN
5389
5390 -- Perform a check to see if the primary key has been creeated within
5391 -- a visible business group. Ie: the business group is for the same
5392 -- legislation as the delivered row, or the delivered row has a null
5393 -- legislation. If no rows are returned then the primary key has not
5394 -- already been created by a user.
5395
5396 select distinct null
5397 into l_null_return
5398 from pay_monetary_units a
5399 where a.monetary_unit_name = stu_rec.c_true_key
5400 and a.currency_code = stu_rec.currency_code
5401 and a.business_group_id is not null
5402 and exists (select null from per_business_groups b
5403 where b.business_group_id = a.business_group_id
5404 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
5405
5406 crt_exc('Row already created in a business group');
5407
5408 return; -- Indicates this row is not to be transferred
5409
5410 EXCEPTION WHEN NO_DATA_FOUND THEN
5411 null;
5412
5413 END;
5414
5415
5416 -- Now perform a check to see if this primary key has been installed
5417 -- with a legislation code that would make it visible at the same time
5418 -- as this row. Ie: if any legislation code is null within the set of
5419 -- returned rows, then the transfer may not go ahead. If no rows are
5420 -- returned then the delivered row is fine.
5421
5422 BEGIN
5423 select distinct null
5424 into l_null_return
5425 from pay_monetary_units
5426 where monetary_unit_name = stu_rec.c_true_key
5427 and currency_code = stu_rec.currency_code
5428 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
5429 and (
5430 legislation_code is null
5431 or stu_rec.c_leg_code is null
5432 )
5433 and business_group_id is null;
5434
5435 crt_exc('Row already created for a visible legislation');
5436
5437 return; -- Indicates this row is not to be transferred
5438
5439 EXCEPTION WHEN NO_DATA_FOUND THEN
5440 null;
5441
5442 END;
5443
5444
5445 -- When the procedure is called in phase 1, there is no need to
5446 -- actually perform the transfer from the delivery tables into the
5447 -- live. Hence if phase = 1 control is returned to the calling
5448 -- procedure and the next row is returned.
5449
5450 IF p_phase = 1 THEN return; END IF; --only insert on phase 2
5451
5455 -- The routine check_parents validates foreign key references and
5452 -- If the procedure is called in phase 2, then the live row is updated
5453 -- with the values on the delivered row.
5454
5456 -- ensures referential integrity. The routine checks to see if the
5457 -- parents of a given row have been transfered to the live tables.
5458
5459 -- This may only be called in phase two since in phase one all
5460 -- parent rows will remain in the delivery tables.
5461
5462 -- After the above checks only data that has been chanegd or is new
5463 -- will be left in the delivery tables. At this stage if the row is
5464 -- already present then it must be updated to ensure referential
5465 -- integrity. Therefore an update will be performed and if SQL%FOUND
5466 -- is FALSE an insert will be performed.
5467
5468 -- The last step of the transfer, in phase 2, is to delete the now
5469 -- transfered row from the delivery tables.
5470
5471
5472 update pay_monetary_units
5473 set currency_code = stu_rec.currency_code
5474 , business_group_id = null
5475 , legislation_code = stu_rec.c_leg_code
5476 , relative_value = stu_rec.relative_value
5477 , last_update_date = stu_rec.last_update_date
5478 , last_updated_by = stu_rec.last_updated_by
5479 , last_update_login = stu_rec.last_update_login
5480 , created_by = stu_rec.created_by
5481 , creation_date = stu_rec.creation_date
5482 where monetary_unit_id = stu_rec.c_surrogate_key;
5483
5484 IF SQL%NOTFOUND THEN
5485
5486 insert into pay_monetary_units
5487 (monetary_unit_id
5488 ,currency_code
5489 ,business_group_id
5490 ,legislation_code
5491 ,monetary_unit_name
5492 ,relative_value
5493 ,last_update_date
5494 ,last_updated_by
5495 ,last_update_login
5496 ,created_by
5497 ,creation_date
5498 )
5499 values
5500 (stu_rec.c_surrogate_key
5501 ,stu_rec.currency_code
5502 ,stu_rec.business_group_id
5503 ,stu_rec.c_leg_code
5504 ,stu_rec.c_true_key
5505 ,stu_rec.relative_value
5506 ,stu_rec.last_update_date
5507 ,stu_rec.last_updated_by
5508 ,stu_rec.last_update_login
5509 ,stu_rec.created_by
5510 ,stu_rec.creation_date
5511 );
5512
5513 END IF;
5514
5515
5516 remove;
5517
5518 END transfer_row;
5519
5520 BEGIN
5521
5522 -- This is the main loop to perform the installation logic. A cursor
5523 -- is opened to control the loop, and each row returned is placed
5524 -- into a record defined within the main procedure so each sub
5525 -- procedure has full access to all returned columns. For each
5526 -- new row returned, a new savepoint is declared. If at any time
5527 -- the row is in error a rollback is performed to the savepoint
5528 -- and the next row is returned. Ownership details are checked and
5529 -- if the row is required then the surrogate id is updated and the
5530 -- main transfer logic is called.
5531
5532 IF p_phase = 1 THEN check_next_sequence; END IF;
5533
5534 FOR delivered IN stu LOOP
5535
5536 savepoint new_primary_key;
5537
5538 stu_rec := delivered;
5539
5540 IF valid_ownership THEN
5541
5542 -- Test the row onerships for the current row
5543
5544
5545 IF p_phase = 1 THEN update_uid; END IF;
5546
5547 transfer_row;
5548
5549 END IF;
5550
5551 END LOOP;
5552
5553 END install_monetary;
5554
5555
5556 --****************************************************************************
5557 -- INSTALLATION PROCEDURE FOR : PAY_REPORT_FORMAT_MAPPINGS
5558 --****************************************************************************
5559 PROCEDURE install_report_mappings(p_phase IN number)
5560
5561 -- as this table does not have a primary key, but uses a cpomposite key,
5562 -- we do not need to check the id
5563
5564 IS
5565 l_null_return varchar2(1); -- used for 'select null' statements
5566 l_new_surrogate_key number(15); -- new uid.
5567
5568 CURSOR stu -- Selects all rows from startup entity
5569 IS
5570 select report_type ,
5571 report_qualifier ,
5572 report_format ,
5573 effective_start_date ,
5574 effective_end_date ,
5575 range_code ,
5576 assignment_action_code ,
5577 initialization_code ,
5578 archive_code ,
5579 magnetic_code ,
5580 report_category ,
5581 report_name ,
5582 sort_code ,
5583 updatable_flag ,
5584 deinitialization_code ,
5585 last_update_date ,
5586 last_updated_by ,
5587 last_update_login ,
5588 created_by ,
5589 creation_date ,
5590 temporary_action_flag ,
5591 rowid
5592 from hr_s_report_format_mappings_f;
5593
5594 stu_rec stu%ROWTYPE;
5595
5596 PROCEDURE remove
5597 ----------------
5598 IS
5602
5599 -- Remove a row from either the startup tables or the installed tables
5600
5601 BEGIN
5603 delete from hr_s_report_format_mappings_f
5604 where rowid = stu_rec.rowid;
5605
5606
5607 END remove;
5608
5609 PROCEDURE transfer_row
5610 ----------------------
5611 IS
5612
5613 BEGIN
5614
5615 -- this updates uses only report_type,qualifier,category as its primary key
5616 -- it may be that effective start and end dates will need to be added
5617 -- but as of know we can see no need for this
5618
5619 update pay_report_format_mappings_f
5620 set effective_start_date=stu_rec.effective_start_date
5621 , effective_end_date=stu_rec.effective_end_date
5622 , range_code=stu_rec.range_code
5623 , assignment_action_code=stu_rec.assignment_action_code
5624 , initialization_code=stu_rec.initialization_code
5625 , archive_code=stu_rec.archive_code
5626 , magnetic_code=stu_rec.magnetic_code
5627 , report_name=stu_rec.report_name
5628 , sort_code=stu_rec.sort_code
5629 , updatable_flag=stu_rec.updatable_flag
5630 , report_format=stu_rec.report_format
5631 , deinitialization_code=stu_rec.deinitialization_code
5632 , last_update_date = stu_rec.last_update_date
5633 , last_updated_by = stu_rec.last_updated_by
5634 , last_update_login = stu_rec.last_update_login
5635 , created_by = stu_rec.created_by
5636 , creation_date = stu_rec.creation_date
5637 , temporary_action_flag = stu_rec.temporary_action_flag
5638 where report_type= stu_rec.report_type
5639 and report_qualifier=stu_rec.report_qualifier
5640 and report_category=stu_rec.report_category
5641 and effective_start_date = stu_rec.effective_start_date
5642 and effective_end_date = stu_rec.effective_end_date;
5643
5644 IF SQL%NOTFOUND THEN
5645
5646 BEGIN
5647 insert into pay_report_format_mappings_f
5648 ( report_type ,
5649 report_qualifier ,
5650 report_format ,
5651 effective_start_date ,
5652 effective_end_date ,
5653 range_code ,
5654 assignment_action_code ,
5655 initialization_code ,
5656 archive_code ,
5657 magnetic_code ,
5658 report_category ,
5659 report_name ,
5660 sort_code ,
5661 updatable_flag ,
5662 deinitialization_code ,
5663 last_update_date ,
5664 last_updated_by ,
5665 last_update_login ,
5666 created_by ,
5667 creation_date ,
5668 temporary_action_flag
5669 )
5670 values
5671 ( stu_rec.report_type ,
5672 stu_rec.report_qualifier ,
5673 stu_rec.report_format ,
5674 stu_rec.effective_start_date ,
5675 stu_rec.effective_end_date ,
5676 stu_rec.range_code ,
5677 stu_rec.assignment_action_code ,
5678 stu_rec.initialization_code ,
5679 stu_rec.archive_code ,
5680 stu_rec.magnetic_code ,
5681 stu_rec.report_category ,
5682 stu_rec.report_name ,
5683 stu_rec.sort_code ,
5684 stu_rec.updatable_flag ,
5685 stu_rec.deinitialization_code ,
5686 stu_rec.last_update_date ,
5687 stu_rec.last_updated_by ,
5688 stu_rec.last_update_login ,
5689 stu_rec.created_by ,
5690 stu_rec.creation_date ,
5691 stu_rec.temporary_action_flag
5692 );
5693
5694 EXCEPTION WHEN OTHERS THEN
5695 hrrunprc_trace_on;
5696 hr_utility.trace('ins pay_rep_format_map');
5697 hr_utility.trace('report_type ' ||
5698 stu_rec.report_type);
5699 hr_utility.trace('report_qualifier ' ||
5700 stu_rec.report_qualifier);
5701 hr_utility.trace('report_format ' ||
5702 stu_rec.report_format);
5703 hrrunprc_trace_off;
5704 raise;
5705 END;
5706
5707 END IF;
5708
5709 remove;
5710
5711 END transfer_row;
5712
5713 BEGIN
5714
5715 FOR delivered IN stu LOOP
5716 savepoint new_primary_key;
5717 stu_rec := delivered;
5718 IF p_phase = 2 THEN transfer_row; END IF;
5719 END LOOP;
5720
5721
5722 END install_report_mappings;
5723
5724
5725 PROCEDURE install_magnetic_blocks(p_phase IN number)
5726 -----------------------------------------------
5727 IS
5728 l_null_return varchar2(1); -- used for 'select null' statements
5729 l_new_surrogate_key number(15); -- new uid.
5730
5731
5732
5736 block_name c_true_key,
5733 CURSOR stu -- Selects all rows from startup entity
5734 IS
5735 select distinct magnetic_block_id c_surrogate_key,
5737 main_block_flag,
5738 report_format,
5739 cursor_name,
5740 no_column_returned
5741 from hr_s_magnetic_blocks;
5742
5743 stu_rec stu%ROWTYPE;
5744
5745
5746 PROCEDURE check_next_sequence
5747 -----------------------------
5748 IS
5749
5750 v_sequence_number number(15);
5751 v_min_delivered number(15);
5752 v_max_delivered number(15);
5753 v_max_live number(15);
5754 cnt number(15);
5755
5756 BEGIN
5757
5758
5759 BEGIN --check that the installed id's will not conflict
5760 --with the delivered values
5761
5762 select count(*)
5763 into cnt
5764 from hr_s_magnetic_blocks;
5765
5766 If cnt=0 then return; end if;
5767
5768 select distinct null
5769 into l_null_return
5770 from pay_magnetic_blocks a
5771 where exists
5772 (select null
5773 from hr_s_magnetic_blocks b
5774 where a.magnetic_block_id = b.magnetic_block_id
5775 );
5776
5777 update hr_s_magnetic_blocks
5778 set magnetic_block_id=magnetic_block_id -50000000;
5779
5780 update hr_s_magnetic_records
5781 set magnetic_block_id=magnetic_block_id -50000000;
5782
5783 update hr_s_magnetic_records
5784 set next_block_id=next_block_id -50000000;
5785
5786 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
5787
5788 END; --check of magnetic_block_id
5789
5790
5791 select min(magnetic_block_id) - (count(*) *3)
5792 , max(magnetic_block_id) + (count(*) *3)
5793 into v_min_delivered
5794 , v_max_delivered
5795 from hr_s_magnetic_blocks;
5796
5797 select max(magnetic_block_id)
5798 into v_max_live
5799 from pay_magnetic_blocks;
5800
5801 select pay_magnetic_blocks_s.nextval
5802 into v_sequence_number
5803 from dual;
5804
5805 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
5806 OR (v_sequence_number < v_max_live) THEN
5807
5808 IF v_max_live > v_max_delivered THEN
5809
5810 hr_legislation.munge_sequence('PAY_MAGNETIC_BLOCKS_S',
5811 v_sequence_number,
5812 v_max_live);
5813 ELSE
5814
5815 hr_legislation.munge_sequence('PAY_MAGNETIC_BLOCKS_S',
5816 v_sequence_number,
5817 v_max_delivered);
5818 END IF;
5819 END IF;
5820
5821
5822 END check_next_sequence;
5823
5824
5825 PROCEDURE crt_exc (exception_type IN varchar2)
5826 ----------------------------------------------
5827 IS
5828 -- Reports any exceptions during the delivery of startup data to
5829 -- PER_ASSIGNMENT_STATUS_TYPES. See crt_exc in procedure install_past
5830 -- for further, generic operational details.
5831
5832 BEGIN
5833
5834 rollback to new_magnetic_blocks;
5835
5836 insert_hr_stu_exceptions('pay_magnetic_blocks'
5837 , stu_rec.c_surrogate_key
5838 , exception_type
5839 , stu_rec.c_true_key);
5840
5841
5842
5843
5844 END crt_exc;
5845
5846
5847 PROCEDURE update_uid
5848 --------------------
5849 IS
5850
5851 BEGIN
5852
5853
5854 BEGIN
5855
5856 select distinct magnetic_block_id
5857 into l_new_surrogate_key
5858 from pay_magnetic_blocks
5859 where replace(ltrim(rtrim(upper(block_name))), ' ', '_') =
5860 replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_')
5861 and replace(ltrim(rtrim(upper(report_format))), ' ', '_') =
5862 replace(ltrim(rtrim(upper(stu_rec.report_format))), ' ', '_')
5863 and replace(ltrim(rtrim(upper(nvl(cursor_name,'X')))), ' ', '_') =
5864 replace(ltrim(rtrim(upper(nvl(stu_rec.cursor_name,'X')))), ' ', '_');
5865
5866 EXCEPTION WHEN NO_DATA_FOUND THEN
5867
5868 select pay_magnetic_blocks_s.nextval
5869 into l_new_surrogate_key
5870 from dual;
5871
5872 WHEN TOO_MANY_ROWS THEN
5873 hrrunprc_trace_on;
5874 hr_utility.trace('sel pay_mag_blocks TMR');
5875 hr_utility.trace('block_name ' ||
5876 stu_rec.c_true_key);
5877 hr_utility.trace('report_format ' ||
5878 stu_rec.report_format);
5879 hr_utility.trace('cursor_name ' ||
5880 stu_rec.cursor_name);
5881 hrrunprc_trace_off;
5882 raise;
5883 END;
5884
5885 -- Update all child entities
5886
5887 update hr_s_magnetic_blocks
5888 set magnetic_block_id = l_new_surrogate_key
5889 where magnetic_block_id = stu_rec.c_surrogate_key;
5890
5894 and key_name = 'MAGNETIC_BLOCK_ID';
5891 update hr_s_application_ownerships
5892 set key_value = to_char(l_new_surrogate_key)
5893 where key_value = to_char(stu_rec.c_surrogate_key)
5895
5896 update hr_s_magnetic_records
5897 set magnetic_block_id = l_new_surrogate_key
5898 where magnetic_block_id = stu_rec.c_surrogate_key;
5899
5900 update hr_s_magnetic_records
5901 set next_block_id= l_new_surrogate_key
5902 where next_block_id=stu_rec.c_surrogate_key;
5903
5904 END update_uid;
5905
5906 PROCEDURE remove
5907 ----------------
5908 IS
5909 -- Remove a row from either the startup tables or the installed tables
5910
5911 BEGIN
5912
5913 delete from hr_s_magnetic_blocks
5914 where magnetic_block_id = stu_rec.c_surrogate_key;
5915
5916 END remove;
5917
5918
5919 PROCEDURE transfer_row
5920 ----------------------
5921 -- if a magnetic_block has changed then the row will be updated,
5922 -- if it is new , it gets inserted.
5923
5924 IS
5925
5926 BEGIN
5927
5928
5929 BEGIN
5930 select distinct null
5931 into l_null_return
5932 from pay_magnetic_blocks
5933 where magnetic_block_id =l_new_surrogate_key
5934 and block_name = stu_rec.c_true_key
5935 and main_block_flag =stu_rec.main_block_flag
5936 and report_format =stu_rec.report_format
5937 and nvl(cursor_name,'')=nvl(stu_rec.cursor_name,'')
5938 and nvl(no_column_returned,0)=nvl(stu_rec.no_column_returned,0);
5939
5940 EXCEPTION WHEN NO_DATA_FOUND THEN
5941
5942 IF p_phase = 1 THEN return; END IF;
5943
5944 update pay_magnetic_blocks
5945 set block_name=stu_rec.c_true_key
5946 , main_block_flag=stu_rec.main_block_flag
5947 , report_format=stu_rec.report_format
5948 , cursor_name=stu_rec.cursor_name
5949 , no_column_returned=stu_rec.no_column_returned
5950 where magnetic_block_id =stu_rec.c_surrogate_key;
5951
5952 IF SQL%NOTFOUND THEN
5953
5954 -- No row there to update, must insert
5955
5956 BEGIN
5957 insert into pay_magnetic_blocks
5958 ( magnetic_block_id ,
5959 block_name ,
5960 main_block_flag ,
5961 report_format ,
5962 cursor_name ,
5963 no_column_returned)
5964 values
5965 ( stu_rec.c_surrogate_key ,
5966 stu_rec.c_true_key ,
5967 stu_rec.main_block_flag ,
5968 stu_rec.report_format ,
5969 stu_rec.cursor_name ,
5970 stu_rec.no_column_returned);
5971 EXCEPTION WHEN OTHERS THEN
5972 hrrunprc_trace_on;
5973 hr_utility.trace('ins pay_mag_blocks');
5974 hr_utility.trace('block_name ' ||
5975 stu_rec.c_true_key);
5976 hr_utility.trace('report_format ' ||
5977 stu_rec.report_format);
5978 hr_utility.trace('cursor_name ' ||
5979 stu_rec.cursor_name);
5980 hrrunprc_trace_off;
5981 raise;
5982 END;
5983
5984 END IF;
5985 END;
5986 remove;
5987
5988 END transfer_row;
5989
5990 BEGIN
5991
5992 IF p_phase = 1 THEN check_next_sequence; END IF;
5993
5994 FOR delivered IN stu LOOP
5995
5996 savepoint new_magnetic_blocks;
5997 stu_rec := delivered;
5998 IF p_phase = 2 THEN l_new_surrogate_key := stu_rec.c_surrogate_key; END IF;
5999
6000 IF p_phase = 1 THEN update_uid; END IF;
6001 transfer_row;
6002
6003 END LOOP;
6004
6005 END install_magnetic_blocks;
6006
6007
6008 PROCEDURE install_magnetic_records(p_phase IN number)
6009 -----------------------------------------------
6010 IS
6011 l_null_return varchar2(1); -- used for 'select null' statements
6012 l_new_surrogate_key number(15); -- new uid.
6013
6014 CURSOR stu -- Selects all rows from startup entity
6015 IS
6016 select distinct
6017 formula_id ,
6018 magnetic_block_id ,
6019 next_block_id ,
6020 overflow_mode ,
6021 sequence ,
6022 frequency ,
6023 last_run_executed_mode
6024 from hr_s_magnetic_records;
6025
6026 stu_rec stu%ROWTYPE;
6027
6028
6029 PROCEDURE remove
6030 ----------------
6031 IS
6032 -- Remove a row from either the startup tables or the installed tables
6033 BEGIN
6034 delete from hr_s_magnetic_records
6035 where magnetic_block_id = stu_rec.magnetic_block_id
6036 and sequence = stu_rec.sequence;
6037 END remove;
6038
6039
6040 PROCEDURE transfer_row
6041 ----------------------
6042 IS
6043 BEGIN
6044
6045
6046 BEGIN
6047
6048 select distinct null
6049 into l_null_return
6050 from pay_magnetic_records
6054 and overflow_mode=stu_rec.overflow_mode
6051 where formula_id=stu_rec.formula_id
6052 and magnetic_block_id=stu_rec.magnetic_block_id
6053 and next_block_id=stu_rec.next_block_id
6055 and sequence=stu_rec.sequence
6056 and frequency=stu_rec.frequency
6057 and last_run_executed_mode=stu_rec.last_run_executed_mode;
6058
6059 EXCEPTION WHEN NO_DATA_FOUND THEN
6060
6061 IF p_phase = 1 THEN return; END IF;
6062
6063 update pay_magnetic_records
6064 set formula_id=stu_rec.formula_id
6065 , next_block_id=stu_rec.next_block_id
6066 , overflow_mode=stu_rec.overflow_mode
6067 , frequency=stu_rec.frequency
6068 , last_run_executed_mode=stu_rec.last_run_executed_mode
6069 where magnetic_block_id=stu_rec.magnetic_block_id
6070 and sequence=stu_rec.sequence;
6071
6072
6073 IF SQL%NOTFOUND THEN
6074
6075 -- No row there to update, must insert
6076
6077
6078 BEGIN
6079 insert into pay_magnetic_records
6080 ( formula_id ,
6081 magnetic_block_id ,
6082 next_block_id ,
6083 overflow_mode ,
6084 sequence ,
6085 frequency ,
6086 last_run_executed_mode)
6087 values
6088 ( stu_rec.formula_id ,
6089 stu_rec.magnetic_block_id ,
6090 stu_rec.next_block_id ,
6091 stu_rec.overflow_mode ,
6092 stu_rec.sequence ,
6093 stu_rec.frequency ,
6094 stu_rec.last_run_executed_mode);
6095 EXCEPTION WHEN OTHERS THEN
6096 hrrunprc_trace_on;
6097 hr_utility.trace('ins pay_magnetic_records');
6098 hr_utility.trace('mag block id: ' ||
6099 to_char(stu_rec.magnetic_block_id));
6100 hr_utility.trace('next blk id: ' ||
6101 to_char(stu_rec.next_block_id));
6102 hr_utility.trace('sequence: ' ||
6103 to_char(stu_rec.sequence));
6104 hrrunprc_trace_off;
6105 raise;
6106 END;
6107
6108 END IF;
6109 END;
6110 remove;
6111 END transfer_row;
6112
6113
6114
6115 BEGIN
6116
6117 FOR delivered IN stu LOOP
6118 savepoint new_magnetic_blocks;
6119 stu_rec := delivered;
6120 transfer_row;
6121 END LOOP;
6122
6123 END install_magnetic_records;
6124
6125
6126 --****************************************************************************
6127 -- INSTALLATION PROCEDURE FOR : PAY_REPORT_FORMAT_ITEMS_F
6128 --****************************************************************************
6129 PROCEDURE install_report_items(p_phase IN number)
6130
6131 -- as this table does not have a primary key, but uses a cpomposite key,
6132 -- we do not need to check the id
6133
6134 IS
6135 l_null_return varchar2(1); -- used for 'select null' statements
6136 l_new_surrogate_key number(15); -- new uid.
6137
6138
6139
6140 CURSOR stu -- Selects all rows from startup entity
6141 IS
6142 select report_type ,
6143 report_qualifier ,
6144 report_category ,
6145 user_entity_id ,
6146 effective_start_date ,
6147 effective_end_date ,
6148 archive_type ,
6149 updatable_flag ,
6150 display_sequence ,
6151 report_format_item_id c_surrogate_key,
6152 last_update_date ,
6153 last_updated_by ,
6154 last_update_login ,
6155 created_by ,
6156 creation_date ,
6157 rowid
6158 from hr_s_report_format_items_f;
6159
6160
6161 stu_rec stu%ROWTYPE;
6162
6163
6164 PROCEDURE check_next_sequence
6165 -----------------------------
6166 IS
6167
6168 v_sequence_number number(15);
6169 v_min_delivered number(15);
6170 v_max_delivered number(15);
6171 v_max_live number(15);
6172 cnt number(15);
6173
6174 BEGIN
6175
6176
6177 BEGIN --check that the installed id's will not conflict
6178 --with the delivered values
6179
6180 select count(*)
6181 into cnt
6182 from hr_s_report_format_items_f;
6183
6184 If cnt=0 then return; end if;
6185
6186 select distinct null
6187 into l_null_return
6188 from pay_report_format_items_f a
6189 where exists
6190 (select null
6191 from hr_s_report_format_items_f b
6192 where a.report_format_item_id = b.report_format_item_id
6193 );
6194
6195 update hr_s_report_format_items_f
6196 set report_format_item_id=report_format_item_id -50000000;
6197
6198 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
6199
6200 END;
6201
6202
6203 select min(report_format_item_id) - (count(*) *3)
6204 , max(report_format_item_id) + (count(*) *3)
6208
6205 into v_min_delivered
6206 , v_max_delivered
6207 from hr_s_report_format_items_f;
6209 select max(report_format_item_id)
6210 into v_max_live
6211 from pay_report_format_items_f;
6212
6213 select pay_report_format_items_s.nextval
6214 into v_sequence_number
6215 from dual;
6216
6217 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
6218 OR (v_sequence_number < v_max_live) THEN
6219
6220 IF v_max_live > v_max_delivered THEN
6221
6222 hr_legislation.munge_sequence('PAY_REPORT_FORMAT_ITEMS_S',
6223 v_sequence_number,
6224 v_max_live);
6225 ELSE
6226
6227 hr_legislation.munge_sequence('PAY_REPORT_FORMAT_ITEMS_S',
6228 v_sequence_number,
6229 v_max_delivered);
6230 END IF;
6231 END IF;
6232
6233
6234 END check_next_sequence;
6235
6236 PROCEDURE update_uid
6237 --------------------
6238 IS
6239
6240 BEGIN
6241
6242
6243 BEGIN
6244
6245 select distinct report_format_item_id
6246 into l_new_surrogate_key
6247 from pay_report_format_items_f
6248 where report_type = stu_rec.report_type
6249 and report_qualifier = stu_rec.report_qualifier
6250 and report_category = stu_rec.report_category
6251 and user_entity_id = stu_rec.user_entity_id;
6252
6253
6254 EXCEPTION WHEN NO_DATA_FOUND THEN
6255
6256 select pay_report_format_items_s.nextval
6257 into l_new_surrogate_key
6258 from dual;
6259
6260 WHEN TOO_MANY_ROWS THEN
6261 hrrunprc_trace_on;
6262 hr_utility.trace('sel pay_report_format_items_f TMR');
6263 hr_utility.trace('report_type ' ||
6264 stu_rec.report_type);
6265 hr_utility.trace('report_qualifier ' ||
6266 stu_rec.report_qualifier);
6267 hr_utility.trace('report_category ' ||
6268 stu_rec.report_category);
6269 hr_utility.trace('user_entity_id ' ||
6270 stu_rec.user_entity_id);
6271 hrrunprc_trace_off;
6272 raise;
6273 END;
6274
6275 if l_new_surrogate_key is null then
6276
6277 select pay_report_format_items_s.nextval
6278 into l_new_surrogate_key
6279 from dual;
6280
6281 end if;
6282
6283 update hr_s_report_format_items_f
6284 set report_format_item_id = l_new_surrogate_key
6285 where report_type = stu_rec.report_type
6286 and report_qualifier = stu_rec.report_qualifier
6287 and report_category = stu_rec.report_category
6288 and user_entity_id = stu_rec.user_entity_id;
6289
6290 END update_uid;
6291
6292 PROCEDURE remove
6293 ----------------
6294 IS
6295 -- Remove a row from either the startup tables or the installed tables
6296
6297 BEGIN
6298
6299 delete from hr_s_report_format_items_f
6300 where rowid = stu_rec.rowid;
6301
6302
6303 END remove;
6304
6305 PROCEDURE transfer_row
6306 ----------------------
6307 IS
6308
6309 BEGIN
6310
6311 -- this updates uses only report_type,qualifier,category as its primary key
6312 -- it may be that effective start and end dates will need to be added
6313 -- but as of know we can see no need for this
6314
6315 update pay_report_format_items_f
6316 set effective_start_date=stu_rec.effective_start_date
6317 , effective_end_date=stu_rec.effective_end_date
6318 , archive_type=stu_rec.archive_type
6319 , updatable_flag=stu_rec.updatable_flag
6320 , display_sequence=stu_rec.display_sequence
6321 , report_format_item_id = stu_rec.c_surrogate_key
6322 , last_update_date = stu_rec.last_update_date
6323 , last_updated_by = stu_rec.last_updated_by
6324 , last_update_login = stu_rec.last_update_login
6325 , created_by = stu_rec.created_by
6326 , creation_date = stu_rec.creation_date
6327 where report_type= stu_rec.report_type
6328 and report_qualifier=stu_rec.report_qualifier
6329 and report_category=stu_rec.report_category
6330 and user_entity_id=stu_rec.user_entity_id
6331 and effective_start_date = stu_rec.effective_start_date
6332 and effective_end_date = stu_rec.effective_end_date ;
6333
6334 IF SQL%NOTFOUND THEN
6335 BEGIN
6336 insert into pay_report_format_items_f
6337 ( report_type ,
6338 report_qualifier ,
6339 report_category ,
6340 user_entity_id ,
6341 effective_start_date ,
6345 display_sequence ,
6342 effective_end_date ,
6343 archive_type ,
6344 updatable_flag ,
6346 report_format_item_id ,
6347 last_update_date ,
6348 last_updated_by ,
6349 last_update_login ,
6350 created_by ,
6351 creation_date
6352 )
6353 values
6354 ( stu_rec.report_type ,
6355 stu_rec.report_qualifier ,
6356 stu_rec.report_category ,
6357 stu_rec.user_entity_id ,
6358 stu_rec.effective_start_date ,
6359 stu_rec.effective_end_date ,
6360 stu_rec.archive_type ,
6361 stu_rec.updatable_flag ,
6362 stu_rec.display_sequence ,
6363 stu_rec.c_surrogate_key ,
6364 stu_rec.last_update_date ,
6365 stu_rec.last_updated_by ,
6366 stu_rec.last_update_login ,
6367 stu_rec.created_by ,
6368 stu_rec.creation_date
6369 );
6370
6371 EXCEPTION WHEN OTHERS THEN
6372 hrrunprc_trace_on;
6373 hr_utility.trace('ins pay_report_format_items_f');
6374 hr_utility.trace('report_type ' ||
6375 stu_rec.report_type);
6376 hr_utility.trace('report_qualifier ' ||
6377 stu_rec.report_qualifier);
6378 hr_utility.trace('report_category ' ||
6379 stu_rec.report_category);
6380 hr_utility.trace('ue_id ' ||
6381 to_char(stu_rec.user_entity_id));
6382 hrrunprc_trace_off;
6383 raise;
6384 END;
6385
6386 END IF;
6387
6388
6389 remove;
6390
6391 END transfer_row;
6392
6393 BEGIN
6394
6395 IF p_phase = 1 THEN check_next_sequence; END IF;
6396
6397 FOR delivered IN stu LOOP
6398 savepoint new_primary_key;
6399 stu_rec := delivered;
6400
6401 IF p_phase = 2 THEN l_new_surrogate_key := stu_rec.c_surrogate_key; END IF;
6402
6403 IF p_phase = 1 THEN update_uid; END IF;
6404
6405 IF p_phase=2 THEN transfer_row; end if;
6406
6407 END LOOP;
6408
6409 END install_report_items;
6410
6411
6412 --****************************************************************************
6413 -- INITIAL ENTRY POINT FOR THE INSTALLATION/DELIVERY OF STARTUP DATA
6414 --****************************************************************************
6415
6416
6417 PROCEDURE install (p_phase number)
6418 ----------------------------------
6419 IS
6420
6421 -- Driver procedure to control the execution of all installation procedures.
6422 -- The function call to 'maintain_history' decides if the phase value is
6423 -- correct. It also inserts/updates the history table.
6424
6425 -- This install procedure accepts the phase number, and is for the caller
6426 -- to control the execution of the phases themselves.
6427 -- There is another procedure called install.
6428
6429 -- The final part of this procedure updates the history row, if the phase
6430 -- just completed had exceptions raised.
6431
6432 -- The order in which the following procedures and packages are called
6433 -- is very important. Please refer to the startup low level design.
6434
6435 v_exception_counter number(15);
6436
6437 BEGIN
6438
6439 IF maintain_history(p_phase) THEN
6440
6441 -- Install all formula ditionary
6442 hrrunprc_trace_on;
6443 hr_utility.trace('start hr_legislation.install: ' || to_char(p_phase));
6444 hr_utility.trace('start ff_data_dict.install: ' || to_char(p_phase));
6445 hrrunprc_trace_off;
6446 ff_data_dict.install(p_phase);
6447
6448 -- Install assignment status types
6449 hrrunprc_trace_on;
6450 hr_utility.trace('start install_past: ' || to_char(p_phase));
6451 hrrunprc_trace_off;
6452 install_past(p_phase);
6453
6454 -- Benefits
6455 hrrunprc_trace_on;
6456 hr_utility.trace('start hr_legislation_benefits.install: ' || to_char(p_phase));
6457 hrrunprc_trace_off;
6458 hr_legislation_benefits.install(p_phase);
6459
6460 -- Elements
6461 hrrunprc_trace_on;
6462 hr_utility.trace('start hr_legislation_elements.install: ' || to_char(p_phase));
6463 hrrunprc_trace_off;
6464 hr_legislation_elements.install(p_phase);
6465
6466 -- Install payment types
6467 hrrunprc_trace_on;
6468 hr_utility.trace('start install_payment_types: ' || to_char(p_phase));
6469 hrrunprc_trace_off;
6470 install_payment_types(p_phase);
6471
6472 -- Install user rows
6473 hrrunprc_trace_on;
6474 hr_utility.trace('start install_urows: ' || to_char(p_phase));
6475 hrrunprc_trace_off;
6476 install_urows(p_phase);
6477
6478 -- Balance dimensions
6479 hrrunprc_trace_on;
6480 hr_utility.trace('start install_dimensions: ' || to_char(p_phase));
6481 hrrunprc_trace_off;
6482 install_dimensions(p_phase);
6483
6484 -- Install dimension routes
6485 hrrunprc_trace_on;
6489
6486 hr_utility.trace('start install_dimension_routes: ' || to_char(p_phase));
6487 hrrunprc_trace_off;
6488 install_dimension_routes(p_phase);
6490 -- Install balance categories
6491 hrrunprc_trace_on;
6492 hr_utility.trace('start install_bal_categories: ' || to_char(p_phase));
6493 hrrunprc_trace_off;
6494 install_bal_categories(p_phase);
6495
6496 -- Install balances,classes,defined,feeds
6497 hrrunprc_trace_on;
6498 hr_utility.trace('start install_bal_types: ' || to_char(p_phase));
6499 hrrunprc_trace_off;
6500 install_bal_types(p_phase);
6501
6502 -- Localization hook
6503 hrrunprc_trace_on;
6504 hr_utility.trace('start hr_legislation_local: ' || to_char(p_phase));
6505 hrrunprc_trace_off;
6506 hr_legislation_local.install(p_phase);
6507
6508 -- Install hr org info details
6509 hrrunprc_trace_on;
6510 hr_utility.trace('start install_org_info: ' || to_char(p_phase));
6511 hrrunprc_trace_off;
6512 install_org_info(p_phase);
6513
6514 -- Install assignment extra info
6515 hrrunprc_trace_on;
6516 hr_utility.trace('start install_ass_info: ' || to_char(p_phase));
6517 hrrunprc_trace_off;
6518 install_ass_info(p_phase);
6519
6520 -- Install legislative field info
6521 hrrunprc_trace_on;
6522 hr_utility.trace('start install_leg_field: ' || to_char(p_phase));
6523 hrrunprc_trace_off;
6524 install_leg_field(p_phase);
6525
6526 -- Install monetary units
6527 hrrunprc_trace_on;
6528 hr_utility.trace('start install_monetary: ' || to_char(p_phase));
6529 hrrunprc_trace_off;
6530 install_monetary(p_phase);
6531
6532 -- install report_format_mappings
6533 hrrunprc_trace_on;
6534 hr_utility.trace('start install_report_mappings: ' || to_char(p_phase));
6535 hrrunprc_trace_off;
6536 install_report_mappings(p_phase);
6537
6538 -- install report_items
6539 hrrunprc_trace_on;
6540 hr_utility.trace('start install_report_items: ' || to_char(p_phase));
6541 hrrunprc_trace_off;
6542 install_report_items(p_phase);
6543
6544 -- install magnetic blocks and records
6545 hrrunprc_trace_on;
6546 hr_utility.trace('start install_magnetic_blocks: ' || to_char(p_phase));
6547 hrrunprc_trace_off;
6548 install_magnetic_blocks(p_phase);
6549
6550 -- install magnetic_records
6551 hrrunprc_trace_on;
6552 hr_utility.trace('start install_magnetic_records: ' || to_char(p_phase));
6553 hrrunprc_trace_off;
6554 install_magnetic_records(p_phase);
6555
6556 -- Install lookup types/codes/usages
6557 --
6558 -- Commented out. Lookups are no longer delivered by this code;
6559 -- they're now all delivered via Datamerge. RMF 27-Mar-96.
6560 --
6561 -- install_lookups(p_phase);
6562 --
6563
6564 -- Check for any exceptions during above installation
6565
6566 select count(*)
6567 into v_exception_counter
6568 from hr_stu_exceptions;
6569
6570 IF v_exception_counter > 0 THEN
6571 --
6572 -- Output information for all rows in hr_stu_exceptions
6573 --
6577 (select package_name
6574 update hr_stu_history
6575 set status = 'Phase '||p_phase||' has exceptions raised'
6576 where package_name in
6578 from hr_s_history);
6579
6580 ELSE -- number of exceptions = 0
6581
6582 -- No exceptions have been raised, phase must have been successful
6583
6584 IF p_phase = 2 THEN
6585
6586 update hr_stu_history a
6587 set a.status = 'Complete'
6588 where exists
6589 (select null
6590 from hr_s_history b
6591 where b.package_name = a.package_name
6592 and b.date_of_export = a.date_of_export
6593 );
6594
6595 END IF;
6596
6597 END IF; -- end exception check
6598
6599 hrrunprc_trace_on;
6600 hr_utility.trace('exit hr_legislation.install: ' || to_char(p_phase));
6601 hrrunprc_trace_off;
6602
6603 ELSE -- error occured in maintain history
6604 rollback;
6605
6606 END IF;
6607
6608 END install;
6609
6610
6611 --****************************************************************************
6612 -- INSTALLATION PROCEDURE FOR : ALL TL Tables
6613 --****************************************************************************
6614 PROCEDURE install_att
6615 ---------------------
6616 IS
6617 -- Install all startup data for multilingual tables.
6618 -- They are:
6619 -- PAY_BALANCE_TYPES_TL
6620 -- PAY_ELEMENT_CLASSIFICATIONS_TL
6621 -- PAY_ELEMENT_TYPES_F_TL
6622 -- PAY_INPUT_VALUES_F_TL
6623 -- PAY_PAYMENT_TYPES_TL
6624 -- PER_ASSIGNMENT_STATUS_TYPES_TL
6625 -- PER_ASSIGNMENT_INFO_TYPES_TL
6626 -- PAY_MONETARY_UNITS_TL
6627 -- PAY_BALANCE_CATEGORIES_F_TL
6628
6629
6630 PROCEDURE install_pbtt
6631 ----------------------
6632 IS
6633 -- Seeds the PAY_BALANCE_TYPES_TL table.
6634
6635 CURSOR c_input_values IS
6636 select
6637 BT.BALANCE_TYPE_ID,
6638 L.LANGUAGE_CODE TRANS_LANG,
6639 B.LANGUAGE_CODE BASE_LANG,
6640 BT.BALANCE_NAME,
6641 BT.REPORTING_NAME,
6642 BT.LAST_UPDATE_DATE,
6643 BT.LAST_UPDATED_BY,
6644 BT.LAST_UPDATE_LOGIN,
6645 BT.CREATED_BY,
6646 BT.CREATION_DATE
6647 from PAY_BALANCE_TYPES BT,
6648 FND_LANGUAGES L,
6649 FND_LANGUAGES B
6650 where L.INSTALLED_FLAG in ('I', 'B')
6651 and B.INSTALLED_FLAG = 'B'
6652 and NVL(TO_CHAR(BT.BUSINESS_GROUP_ID),'Null Value')='Null Value'
6653 and (
6654 not exists (
6655 select '1'
6656 from pay_balance_types_tl btt
6657 where btt.balance_type_id = bt.balance_type_id
6658 and btt.language = l.language_code)
6659 or exists (select '1' from pay_balance_types_tl btt2
6660 where btt2.balance_type_id = bt.balance_type_id
6661 and btt2.language = b.language_code
6662 and nvl(btt2.reporting_name,'XXX') <>
6663 nvl(bt.reporting_name,'XXX'))
6664 );
6665
6666 l_counter NUMBER(3) := 0;
6667
6668 BEGIN
6669
6670
6671 FOR l_rec IN c_input_values LOOP
6672
6673 -- If reporting name for installed lang has changed then remove it
6674 -- before inserting updated reporting name for that installed lang
6675 -- Bug 3280179
6676
6677 delete PAY_BALANCE_TYPES_TL
6681 where pbt.balance_type_id = pbttl.balance_type_id
6678 where rowid in (select pbttl.rowid
6679 from pay_balance_types pbt,
6680 pay_balance_types_tl pbttl
6682 and nvl(pbt.reporting_name, 'XXX') <>
6683 nvl(pbttl.reporting_name, 'XXX')
6684 and NVL(TO_CHAR(PBT.BUSINESS_GROUP_ID),'Null Value')='Null Value'
6685 and pbttl.BALANCE_TYPE_ID = l_rec.BALANCE_TYPE_ID
6686 and pbttl.language = l_rec.trans_lang);
6687
6688 -- Insert all selected rows into the TL table.
6689 -- If the row exist in the TL table then it will be ignored.
6690 insert into PAY_BALANCE_TYPES_TL
6691 (
6692 BALANCE_TYPE_ID,
6693 LANGUAGE,
6694 SOURCE_LANG,
6695 BALANCE_NAME,
6696 REPORTING_NAME,
6697 LAST_UPDATE_DATE,
6698 LAST_UPDATED_BY,
6699 LAST_UPDATE_LOGIN,
6700 CREATED_BY,
6701 CREATION_DATE
6702 )
6703 select
6704 l_rec.BALANCE_TYPE_ID,
6705 l_rec.TRANS_LANG,
6706 l_rec.BASE_LANG,
6707 l_rec.BALANCE_NAME,
6708 l_rec.REPORTING_NAME,
6709 l_rec.LAST_UPDATE_DATE,
6710 l_rec.LAST_UPDATED_BY,
6711 l_rec.LAST_UPDATE_LOGIN,
6712 l_rec.CREATED_BY,
6713 l_rec.CREATION_DATE
6714 from dual
6715 where not exists (
6716 select '1'
6717 from pay_balance_types_tl btt
6718 where btt.balance_type_id = l_rec.balance_type_id
6719 and btt.language = l_rec.trans_lang);
6720
6721 l_counter := l_counter + 1;
6722 IF l_counter = 100 THEN
6723 --COMMIT;
6724 l_counter := 0;
6725 END IF;
6726
6727 END LOOP;
6728
6729 --COMMIT;
6730
6731
6732 EXCEPTION
6733 -- Exception must handle the no_data_found error raised when no rows were
6734 -- found within select statement within the cursor declaration.
6735 WHEN no_data_found THEN
6736 null;
6737 WHEN others THEN
6738 RAISE;
6739 END install_pbtt;
6740
6741
6742
6743
6744
6745
6746 PROCEDURE install_pect
6747 ----------------------
6748 IS
6749 -- Seeds the PAY_ELEMENT_CLASSIFICATIONS_TL table.
6750
6751 CURSOR c_input_values IS
6752 select
6753 EC.CLASSIFICATION_ID,
6754 EC.CLASSIFICATION_NAME,
6755 EC.DESCRIPTION,
6756 L.LANGUAGE_CODE TRANS_LANG,
6757 B.LANGUAGE_CODE BASE_LANG,
6758 EC.LAST_UPDATE_DATE,
6759 EC.LAST_UPDATED_BY,
6760 EC.LAST_UPDATE_LOGIN,
6761 EC.CREATED_BY,
6762 EC.CREATION_DATE
6763 from PAY_ELEMENT_CLASSIFICATIONS EC,
6764 FND_LANGUAGES L,
6765 FND_LANGUAGES B
6766 where L.INSTALLED_FLAG in ('I', 'B')
6767 and B.INSTALLED_FLAG = 'B'
6768 and NVL(TO_CHAR(EC.BUSINESS_GROUP_ID),'Null Value')='Null Value'
6769 and
6770 ( not exists (
6771 select '1'
6772 from PAY_ELEMENT_CLASSIFICATIONS_TL ECT
6773 where ECT.CLASSIFICATION_ID = EC.CLASSIFICATION_ID
6774 and ECT.language = l.language_code)
6775 or exists (select '1' from PAY_ELEMENT_CLASSIFICATIONS_TL ect2
6776 where ect2.CLASSIFICATION_ID = ec.CLASSIFICATION_ID
6777 and ect2.language = b.language_code
6778 and nvl( ect2.description,'ec.description' ||'1') <> nvl(ec.description,ect2.description || '-1'))
6779 );
6780
6781 l_counter NUMBER(3) := 0;
6782
6783 BEGIN
6784
6785
6786 FOR l_rec IN c_input_values LOOP
6787
6788 delete PAY_ELEMENT_CLASSIFICATIONS_TL
6789 where rowid in (select ectl.rowid
6790 from PAY_ELEMENT_CLASSIFICATIONS ec,
6791 PAY_ELEMENT_CLASSIFICATIONS_TL ectl
6792 where ec.CLASSIFICATION_ID = ectl.CLASSIFICATION_ID
6793 and nvl(ec.DESCRIPTION, 'XXX') <>
6794 nvl(ectl.DESCRIPTION, 'XXX')
6795 and NVL(TO_CHAR(ec.BUSINESS_GROUP_ID),'Null Value')='Null Value'
6796 and ectl.CLASSIFICATION_ID = l_rec.CLASSIFICATION_ID
6797 and ectl.language = l_rec.trans_lang);
6798
6799 -- Insert all selected rows into the TL table.
6800 -- If the row exist in the TL table then it will be ignored.
6801 insert into PAY_ELEMENT_CLASSIFICATIONS_TL
6802 (
6803 CLASSIFICATION_ID,
6804 CLASSIFICATION_NAME,
6805 DESCRIPTION,
6806 LANGUAGE,
6807 SOURCE_LANG,
6808 LAST_UPDATE_DATE,
6809 LAST_UPDATED_BY,
6810 LAST_UPDATE_LOGIN,
6811 CREATED_BY,
6815 l_rec.CLASSIFICATION_ID,
6812 CREATION_DATE
6813 )
6814 select
6816 l_rec.CLASSIFICATION_NAME,
6817 l_rec.DESCRIPTION,
6818 l_rec.TRANS_LANG,
6819 l_rec.base_lang,
6820 l_rec.LAST_UPDATE_DATE,
6821 l_rec.LAST_UPDATED_BY,
6822 l_rec.LAST_UPDATE_LOGIN,
6823 l_rec.CREATED_BY,
6824 l_rec.CREATION_DATE
6825 from dual
6826 where not exists (
6827 select '1'
6828 from PAY_ELEMENT_CLASSIFICATIONS_TL ECT
6829 where ECT.CLASSIFICATION_ID = l_rec.CLASSIFICATION_ID
6830 and ECT.language = l_rec.trans_lang);
6831
6832 l_counter := l_counter + 1;
6833 IF l_counter = 100 THEN
6834 --COMMIT;
6835 l_counter := 0;
6836 END IF;
6837
6838 END LOOP;
6839
6840 --COMMIT;
6841
6842
6843 EXCEPTION
6844 -- Exception must handle the no_data_found error raised when no rows were
6845 -- found within select statement within the cursor declaration.
6846 WHEN no_data_found THEN
6847 null;
6848 WHEN others THEN
6849 RAISE;
6850
6851 END install_pect;
6852
6853
6854
6855
6856
6857 PROCEDURE install_petft
6858 ----------------------
6859 IS
6860 -- Seeds the PAY_ELEMENT_TYPES_F_TL table.
6861
6862 CURSOR c_input_values IS
6863 select
6864 ET.ELEMENT_TYPE_ID,
6865 ET.ELEMENT_NAME,
6866 ET.REPORTING_NAME,
6867 ET.DESCRIPTION,
6868 L.LANGUAGE_CODE TRANS_LANG,
6869 B.LANGUAGE_CODE BASE_LANG,
6870 ET.LAST_UPDATE_DATE,
6871 ET.LAST_UPDATED_BY,
6872 ET.LAST_UPDATE_LOGIN,
6873 ET.CREATED_BY,
6874 ET.CREATION_DATE
6875 from PAY_ELEMENT_TYPES_F ET,
6876 FND_LANGUAGES L,
6877 FND_LANGUAGES B
6878 where L.INSTALLED_FLAG in ('I', 'B')
6879 and B.INSTALLED_FLAG = 'B'
6880 and NVL(TO_CHAR(ET.BUSINESS_GROUP_ID),'Null Value')='Null Value'
6881 and
6882 ( not exists (
6883 select '1'
6884 from PAY_ELEMENT_TYPES_F_TL ETT
6885 where ETT.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
6886 and ETT.language = l.language_code)
6887 or exists (select '1' from PAY_ELEMENT_TYPES_F_TL ett2
6888 where ett2.ELEMENT_TYPE_ID = et.ELEMENT_TYPE_ID
6889 and ett2.language = b.language_code
6890 and (nvl(ett2.reporting_name, 'XXX') <> nvl(et.reporting_name, 'XXX')
6891 or
6892 nvl(ett2.description, 'XXX') <> nvl(et.description, 'XXX')))
6893 );
6894
6895 l_counter NUMBER(3) := 0;
6896
6897 BEGIN
6898
6899 FOR l_rec IN c_input_values LOOP
6900
6901 -- If reporting name or description field for installed lang
6902 -- has changed then remove it before inserting updated
6903 -- value for that installed lang
6904 -- Bug 3280179
6905
6906 delete PAY_ELEMENT_TYPES_F_TL
6907 where rowid in (select pettl.rowid
6908 from pay_element_types_f pet,
6909 pay_element_types_f_tl pettl
6910 where pet.element_type_id = pettl.element_type_id
6911 and (
6912 (nvl(pet.reporting_name, 'XXX') <>
6913 nvl(pettl.reporting_name, 'XXX'))
6914 or
6915 (nvl(pet.description, 'XXX') <>
6916 nvl(pettl.description, 'XXX'))
6917 )
6918 and NVL(TO_CHAR(PET.BUSINESS_GROUP_ID),'Null Value')='Null Value'
6919 and pettl.ELEMENT_TYPE_ID = l_rec.ELEMENT_TYPE_ID
6920 and pettl.language = l_rec.trans_lang);
6921
6922 -- Insert all selected rows into the TL table.
6923 -- If the row exist in the TL table then it will be ignored.
6924
6925 insert into PAY_ELEMENT_TYPES_F_TL
6926 (
6927 ELEMENT_TYPE_ID,
6928 ELEMENT_NAME,
6929 REPORTING_NAME,
6930 DESCRIPTION,
6931 LANGUAGE,
6932 SOURCE_LANG,
6933 LAST_UPDATE_DATE,
6934 LAST_UPDATED_BY,
6935 LAST_UPDATE_LOGIN,
6936 CREATED_BY,
6937 CREATION_DATE
6938 )
6939 select
6940 l_rec.ELEMENT_TYPE_ID,
6941 l_rec.ELEMENT_NAME,
6942 l_rec.REPORTING_NAME,
6943 l_rec.DESCRIPTION,
6944 l_rec.TRANS_LANG,
6945 l_rec.base_lang,
6946 l_rec.LAST_UPDATE_DATE,
6947 l_rec.LAST_UPDATED_BY,
6948 l_rec.LAST_UPDATE_LOGIN,
6949 l_rec.CREATED_BY,
6953 select '1'
6950 l_rec.CREATION_DATE
6951 from dual
6952 where not exists (
6954 from PAY_ELEMENT_TYPES_F_TL ETT
6955 where ETT.ELEMENT_TYPE_ID = l_rec.ELEMENT_TYPE_ID
6956 and ETT.language = l_rec.trans_lang);
6957
6958 l_counter := l_counter + 1;
6959 IF l_counter = 100 THEN
6960 --COMMIT;
6961 l_counter := 0;
6962 END IF;
6963
6964 END LOOP;
6965
6966 --COMMIT;
6967
6968
6969 EXCEPTION
6970 -- Exception must handle the no_data_found error raised when no rows were
6971 -- found within select statement within the cursor declaration.
6972 WHEN no_data_found THEN
6973 null;
6974 WHEN others THEN
6975 RAISE;
6976
6977 END install_petft;
6978
6979
6980
6981
6982 PROCEDURE install_pivft
6983 -----------------------
6984 IS
6985 -- Seeds the PAY_INPUT_VALUES_F_TL table.
6986
6987 CURSOR c_input_values IS
6988 select
6989 IV.INPUT_VALUE_ID,
6990 IV.NAME,
6991 L.LANGUAGE_CODE TRANS_LANG,
6992 B.LANGUAGE_CODE BASE_LANG,
6993 IV.LAST_UPDATE_DATE,
6994 IV.LAST_UPDATED_BY,
6995 IV.LAST_UPDATE_LOGIN,
6996 IV.CREATED_BY,
6997 IV.CREATION_DATE
6998 from PAY_INPUT_VALUES_F IV,
6999 FND_LANGUAGES L,
7000 FND_LANGUAGES B
7001 where L.INSTALLED_FLAG in ('I', 'B')
7002 and B.INSTALLED_FLAG = 'B'
7003 and NVL(TO_CHAR(IV.BUSINESS_GROUP_ID),'Null Value')='Null Value'
7004 and not exists (
7005 select '1'
7006 from PAY_INPUT_VALUES_F_TL IVT
7007 where IVT.INPUT_VALUE_ID = IV.INPUT_VALUE_ID
7008 and IVT.language = l.language_code);
7009
7010 l_counter NUMBER(3) := 0;
7011 l_translated_value VARCHAR2(80);
7012
7013 BEGIN
7014
7015
7016 FOR l_rec IN c_input_values LOOP
7017
7018
7019 -- bug 1234525 - insert translated value for pay_value input type
7020 if (replace(upper(l_rec.NAME),' ','_')='PAY_VALUE')
7021 then
7022 select flv.meaning
7023 into l_translated_value
7024 from fnd_lookup_values flv
7025 where flv.lookup_type = 'NAME_TRANSLATIONS'
7026 and flv.lookup_code = 'PAY VALUE'
7027 and flv.view_application_id = 3
7028 and flv.security_group_id = 0
7029 and flv.language = l_rec.trans_lang;
7030 else
7031 l_translated_value:=l_rec.NAME;
7032 end if;
7033 -- Insert all selected rows into the TL table.
7034 -- If the row exist in the TL table then it will be ignored.
7035 insert into PAY_INPUT_VALUES_F_TL
7036 (
7037 INPUT_VALUE_ID,
7038 NAME,
7039 LANGUAGE,
7040 SOURCE_LANG,
7041 LAST_UPDATE_DATE,
7042 LAST_UPDATED_BY,
7043 LAST_UPDATE_LOGIN,
7044 CREATED_BY,
7045 CREATION_DATE
7046 )
7047 select
7051 l_rec.base_lang,
7048 l_rec.INPUT_VALUE_ID,
7049 l_translated_value,
7050 l_rec.TRANS_LANG,
7052 l_rec.LAST_UPDATE_DATE,
7053 l_rec.LAST_UPDATED_BY,
7054 l_rec.LAST_UPDATE_LOGIN,
7055 l_rec.CREATED_BY,
7056 l_rec.CREATION_DATE
7057 from dual
7058 where not exists (
7059 select '1'
7060 from PAY_INPUT_VALUES_F_TL IVT
7061 where IVT.INPUT_VALUE_ID = l_rec.INPUT_VALUE_ID
7062 and IVT.language = l_rec.trans_lang);
7063
7064 l_counter := l_counter + 1;
7065 IF l_counter = 100 THEN
7066 --COMMIT;
7067 l_counter := 0;
7068 END IF;
7069
7070 END LOOP;
7071
7072 --COMMIT;
7073
7074
7075 EXCEPTION
7076 -- Exception must handle the no_data_found error raised when no rows
7077 -- found within select statement within the cursor declaration.
7078 WHEN no_data_found THEN
7079 null;
7080 WHEN others THEN
7081 RAISE;
7082
7083 END install_pivft;
7084
7085 PROCEDURE install_pptt
7086 ----------------------
7087 IS
7088 -- Seeds the PAY_PAYMENT_TYPES_TL table.
7089
7090 CURSOR c_input_values IS
7091 select
7092 PT.PAYMENT_TYPE_ID,
7093 PT.PAYMENT_TYPE_NAME,
7094 PT.DESCRIPTION,
7095 L.LANGUAGE_CODE TRANS_LANG,
7096 B.LANGUAGE_CODE BASE_LANG,
7097 PT.LAST_UPDATE_DATE,
7098 PT.LAST_UPDATED_BY,
7099 PT.LAST_UPDATE_LOGIN,
7100 PT.CREATED_BY,
7101 PT.CREATION_DATE
7102 from PAY_PAYMENT_TYPES PT,
7103 FND_LANGUAGES L,
7104 FND_LANGUAGES B
7105 where L.INSTALLED_FLAG in ('I', 'B')
7106 and B.INSTALLED_FLAG = 'B'
7107 and not exists (
7108 select '1'
7109 from PAY_PAYMENT_TYPES_TL PTT
7110 where PTT.PAYMENT_TYPE_ID = PT.PAYMENT_TYPE_ID
7111 and PTT.language = l.language_code);
7112
7113 l_counter NUMBER(3) := 0;
7114
7115 BEGIN
7116
7117
7118 FOR l_rec IN c_input_values LOOP
7119
7120 -- Insert all selected rows into the TL table.
7121 -- If the row exist in the TL table then it will be ignored.
7122 insert into PAY_PAYMENT_TYPES_TL
7123 (
7124 PAYMENT_TYPE_ID,
7125 PAYMENT_TYPE_NAME,
7126 DESCRIPTION,
7127 LANGUAGE,
7128 SOURCE_LANG,
7129 LAST_UPDATE_DATE,
7130 LAST_UPDATED_BY,
7131 LAST_UPDATE_LOGIN,
7132 CREATED_BY,
7133 CREATION_DATE
7134 )
7135 select
7136 l_rec.PAYMENT_TYPE_ID,
7137 l_rec.PAYMENT_TYPE_NAME,
7138 l_rec.DESCRIPTION,
7139 l_rec.TRANS_LANG,
7140 l_rec.base_lang,
7141 l_rec.LAST_UPDATE_DATE,
7142 l_rec.LAST_UPDATED_BY,
7143 l_rec.LAST_UPDATE_LOGIN,
7144 l_rec.CREATED_BY,
7145 l_rec.CREATION_DATE
7146 from dual
7147 where not exists (
7148 select '1'
7149 from PAY_PAYMENT_TYPES_TL PTT
7150 where PTT.PAYMENT_TYPE_ID = l_rec.PAYMENT_TYPE_ID
7151 and PTT.language = l_rec.trans_lang);
7152
7153 l_counter := l_counter + 1;
7154 IF l_counter = 100 THEN
7155 --COMMIT;
7156 l_counter := 0;
7157 END IF;
7158
7159 END LOOP;
7160
7161 --COMMIT;
7162
7163
7164 EXCEPTION
7165 -- Exception must handle the no_data_found error raised when no rows
7166 -- were found within select statement within the cursor declaration.
7167 WHEN no_data_found THEN
7168 null;
7169 WHEN others THEN
7170 RAISE;
7171
7172 END install_pptt;
7173
7174
7175
7176
7177
7178 PROCEDURE install_paitt
7179 ----------------------
7180 IS
7181 -- Seeds the PER_ASSIGNMENT_INFO_TYPES_TL table.
7182
7183 CURSOR c_input_values IS
7184 select
7185 M.INFORMATION_TYPE,
7186 L.LANGUAGE_CODE TRANS_LANG,
7187 B.LANGUAGE_CODE BASE_LANG,
7188 M.DESCRIPTION,
7189 M.LAST_UPDATE_DATE,
7190 M.LAST_UPDATED_BY,
7191 M.LAST_UPDATE_LOGIN,
7192 M.CREATED_BY,
7193 M.CREATION_DATE
7194 from PER_ASSIGNMENT_INFO_TYPES M,
7195 FND_LANGUAGES L,
7196 FND_LANGUAGES B
7197 where L.INSTALLED_FLAG in ('I', 'B')
7198 and B.INSTALLED_FLAG = 'B'
7199 and not exists ( select '1'
7200 from per_assignment_info_types_tl pait
7201 where pait.information_type = m.information_type
7202 and pait.language = l.language_code);
7203
7204 l_counter NUMBER(3) := 0;
7205
7206 BEGIN
7207
7208
7209 FOR l_rec IN c_input_values LOOP
7210
7211 -- Insert all selected rows into the TL table.
7212 -- If the row exist in the TL table then it will be ignored.
7213 insert into PER_ASSIGNMENT_INFO_TYPES_TL
7214 (
7215 INFORMATION_TYPE,
7216 LANGUAGE,
7217 SOURCE_LANG,
7218 DESCRIPTION,
7219 LAST_UPDATE_DATE,
7220 LAST_UPDATED_BY,
7221 LAST_UPDATE_LOGIN,
7222 CREATED_BY,
7223 CREATION_DATE
7224 )
7225 select
7226 l_rec.INFORMATION_TYPE,
7227 l_rec.TRANS_LANG,
7228 l_rec.base_lang,
7229 l_rec.DESCRIPTION,
7230 l_rec.LAST_UPDATE_DATE,
7231 l_rec.LAST_UPDATED_BY,
7232 l_rec.LAST_UPDATE_LOGIN,
7233 l_rec.CREATED_BY,
7234 l_rec.CREATION_DATE
7235 from dual
7236 where not exists ( select '1'
7237 from per_assignment_info_types_tl pait
7238 where pait.information_type =
7239 l_rec.information_type
7240 and pait.language = l_rec.trans_lang);
7241
7242 l_counter := l_counter + 1;
7243 IF l_counter = 100 THEN
7244 --COMMIT;
7245 l_counter := 0;
7246 END IF;
7247
7248 END LOOP;
7249
7250 --COMMIT;
7251
7252
7253 EXCEPTION
7254 -- Exception must handle the no_data_found error raised when no rows
7255 -- were found within select statement within the cursor declaration.
7256 WHEN no_data_found THEN
7257 null;
7258 WHEN others THEN
7259 RAISE;
7260
7261 END install_paitt;
7262
7263
7264
7265
7266
7267 PROCEDURE install_pastt
7268 ----------------------
7269 IS
7270 -- Seeds the PER_ASSIGNMENT_STATUS_TYPES_TL table.
7271
7272 CURSOR c_input_values IS
7273 select
7274 M.ASSIGNMENT_STATUS_TYPE_ID,
7275 L.LANGUAGE_CODE TRANS_LANG,
7276 B.LANGUAGE_CODE BASE_LANG,
7277 M.USER_STATUS,
7278 M.LAST_UPDATE_DATE,
7279 M.LAST_UPDATED_BY,
7280 M.LAST_UPDATE_LOGIN,
7281 M.CREATED_BY,
7282 M.CREATION_DATE
7283 from PER_ASSIGNMENT_STATUS_TYPES M,
7284 FND_LANGUAGES L,
7285 FND_LANGUAGES B
7286 where L.INSTALLED_FLAG in ('I', 'B')
7287 and B.INSTALLED_FLAG = 'B'
7288 and NVL(TO_CHAR(M.BUSINESS_GROUP_ID),'Null Value')='Null Value'
7289 and not exists (
7290 select '1'
7291 from per_assignment_status_types_tl past
7292 where past.assignment_status_type_id =
7293 m.assignment_status_type_id
7294 and past.language = l.language_code);
7295
7296 l_counter NUMBER(3) := 0;
7297
7298 BEGIN
7299
7300
7301 FOR l_rec IN c_input_values LOOP
7302
7303 -- Insert all selected rows into the TL table.
7304 -- If the row exist in the TL table then it will be ignored.
7305 insert into PER_ASSIGNMENT_STATUS_TYPES_TL
7306 (
7307 ASSIGNMENT_STATUS_TYPE_ID,
7308 LANGUAGE,
7309 SOURCE_LANG,
7310 USER_STATUS,
7311 LAST_UPDATE_DATE,
7312 LAST_UPDATED_BY,
7313 LAST_UPDATE_LOGIN,
7314 CREATED_BY,
7315 CREATION_DATE
7316 )
7317 select
7318 l_rec.ASSIGNMENT_STATUS_TYPE_ID,
7319 l_rec.TRANS_LANG,
7320 l_rec.base_lang,
7321 l_rec.USER_STATUS,
7322 l_rec.LAST_UPDATE_DATE,
7323 l_rec.LAST_UPDATED_BY,
7324 l_rec.LAST_UPDATE_LOGIN,
7325 l_rec.CREATED_BY,
7326 l_rec.CREATION_DATE
7327 from dual
7328 where not exists (
7329 select '1'
7330 from per_assignment_status_types_tl past
7331 where past.assignment_status_type_id =
7332 l_rec.assignment_status_type_id
7333 and past.language = l_rec.trans_lang);
7334
7335 l_counter := l_counter + 1;
7336 IF l_counter = 100 THEN
7337 --COMMIT;
7338 l_counter := 0;
7339 END IF;
7340
7341 END LOOP;
7342
7343 --COMMIT;
7344
7345
7346 EXCEPTION
7347 -- Exception must handle the no_data_found error raised when no rows
7348 -- were found within select statement within the cursor declaration.
7349 WHEN no_data_found THEN
7353
7350 null;
7351 WHEN others THEN
7352 RAISE;
7354 END install_pastt;
7355 --
7356 PROCEDURE install_pmut
7357 ----------------------
7358 IS
7359 -- Seeds the PAY_MONETARY_UNITS_TL table.
7360
7361 CURSOR c_monetary_units IS
7362 select
7363 M.MONETARY_UNIT_ID,
7364 L.LANGUAGE_CODE TRANS_LANG,
7365 B.LANGUAGE_CODE BASE_LANG,
7366 M.MONETARY_UNIT_NAME,
7367 M.LAST_UPDATE_DATE,
7368 M.LAST_UPDATED_BY,
7369 M.LAST_UPDATE_LOGIN,
7370 M.CREATED_BY,
7371 M.CREATION_DATE
7372 from PAY_MONETARY_UNITS M,
7373 FND_LANGUAGES L,
7374 FND_LANGUAGES B
7375 where L.INSTALLED_FLAG in ('I', 'B')
7376 and B.INSTALLED_FLAG = 'B'
7377 and NVL(TO_CHAR(M.BUSINESS_GROUP_ID),'Null Value')='Null Value'
7378 and not exists (
7379 select '1'
7380 from PAY_MONETARY_UNITS_TL pmut
7381 where pmut.monetary_unit_id =
7382 m.monetary_unit_id
7383 and pmut.language = l.language_code);
7384
7385 l_counter NUMBER(3) := 0;
7386
7387 BEGIN
7388
7389
7390 FOR l_rec IN c_monetary_units LOOP
7391
7392 -- Insert all selected rows into the TL table.
7393 -- If the row exist in the TL table then it will be ignored.
7394 insert into PAY_MONETARY_UNITS_TL
7395 (
7396 MONETARY_UNIT_ID,
7397 LANGUAGE,
7398 SOURCE_LANG,
7399 MONETARY_UNIT_NAME,
7400 LAST_UPDATE_DATE,
7401 LAST_UPDATED_BY,
7402 LAST_UPDATE_LOGIN,
7403 CREATED_BY,
7404 CREATION_DATE
7405 )
7406 select
7407 l_rec.MONETARY_UNIT_ID,
7408 l_rec.TRANS_LANG,
7409 l_rec.base_lang,
7410 l_rec.MONETARY_UNIT_NAME,
7411 l_rec.LAST_UPDATE_DATE,
7412 l_rec.LAST_UPDATED_BY,
7413 l_rec.LAST_UPDATE_LOGIN,
7414 l_rec.CREATED_BY,
7415 l_rec.CREATION_DATE
7416 from dual
7417 where not exists (
7418 select '1'
7419 from PAY_MONETARY_UNITS_TL pmut
7420 where pmut.monetary_unit_id =
7421 l_rec.monetary_unit_id
7422 and pmut.language = l_rec.trans_lang);
7423
7424 l_counter := l_counter + 1;
7425 IF l_counter = 100 THEN
7426 --COMMIT;
7427 l_counter := 0;
7428 END IF;
7429
7430 END LOOP;
7431
7432 --COMMIT;
7433
7434
7435 EXCEPTION
7436 -- Exception must handle the no_data_found error raised when no rows
7437 -- were found within select statement within the cursor declaration.
7438 WHEN no_data_found THEN
7439 null;
7440 WHEN others THEN
7441 RAISE;
7442 END install_pmut;
7443 --
7444 PROCEDURE install_pbct
7445 ----------------------
7446 IS
7447 -- Seeds the PAY_BALANCE_CATEGORIES_F_TL table.
7448
7449 CURSOR c_balance_categories IS
7450 select
7451 bc.BALANCE_CATEGORY_ID,
7452 L.LANGUAGE_CODE TRANS_LANG,
7453 B.LANGUAGE_CODE BASE_LANG,
7454 bc.CATEGORY_NAME,
7455 bc.LAST_UPDATE_DATE,
7456 bc.LAST_UPDATED_BY,
7457 bc.LAST_UPDATE_LOGIN,
7458 bc.CREATED_BY,
7459 bc.CREATION_DATE
7460 from PAY_BALANCE_CATEGORIES_F bc,
7461 FND_LANGUAGES L,
7462 FND_LANGUAGES B
7463 where L.INSTALLED_FLAG in ('I', 'B')
7464 and B.INSTALLED_FLAG = 'B'
7465 and not exists (
7466 select '1'
7467 from PAY_BALANCE_CATEGORIES_F_TL bct
7468 where bct.BALANCE_CATEGORY_ID = bc.BALANCE_CATEGORY_ID
7469 and bct.language = l.language_code);
7470
7471 l_counter NUMBER(3) := 0;
7472
7473 BEGIN
7474
7475
7476 FOR l_rec IN c_balance_categories LOOP
7477
7478 -- Insert all selected rows into the TL table.
7479 -- If the row exist in the TL table then it will be ignored.
7480 insert into PAY_BALANCE_CATEGORIES_F_TL
7481 (
7482 BALANCE_CATEGORY_ID,
7483 LANGUAGE,
7484 SOURCE_LANG,
7485 USER_CATEGORY_NAME,
7486 LAST_UPDATE_DATE,
7487 LAST_UPDATED_BY,
7488 LAST_UPDATE_LOGIN,
7489 CREATED_BY,
7490 CREATION_DATE
7491 )
7492 select
7493 l_rec.BALANCE_CATEGORY_ID,
7494 l_rec.TRANS_LANG,
7495 l_rec.base_lang,
7496 l_rec.CATEGORY_NAME,
7497 l_rec.LAST_UPDATE_DATE,
7501 l_rec.CREATION_DATE
7498 l_rec.LAST_UPDATED_BY,
7499 l_rec.LAST_UPDATE_LOGIN,
7500 l_rec.CREATED_BY,
7502 from dual
7503 where not exists (
7504 select '1'
7505 from PAY_BALANCE_CATEGORIES_F_TL bct
7506 where bct.BALANCE_CATEGORY_ID = l_rec.BALANCE_CATEGORY_ID
7507 and bct.language = l_rec.trans_lang);
7508
7509 l_counter := l_counter + 1;
7510 IF l_counter = 100 THEN
7511 --COMMIT;
7512 l_counter := 0;
7513 END IF;
7514
7515 END LOOP;
7516
7517 --COMMIT;
7518
7519
7520 EXCEPTION
7521 -- Exception must handle the no_data_found error raised when no rows
7522 -- were found within select statement within the cursor declaration.
7523 WHEN no_data_found THEN
7524 null;
7525 WHEN others THEN
7526 RAISE;
7527 END install_pbct;
7528 --
7529
7530 BEGIN
7531
7532 -- Install the startup data for all the required 'TL' tables.
7533
7534 install_pbtt;
7535 install_pect;
7536 install_petft;
7537 install_pivft;
7538 install_pptt;
7539 install_paitt;
7540 install_pastt;
7541 install_pmut;
7542 install_pbct;
7543
7544 END install_att;
7545
7546 --****************************************************************************
7547 -- END OF PROCEDURE
7548 --****************************************************************************
7549
7550
7551
7552 --****************************************************************************
7553 -- ENTRY POINT FOR THE AUTOMATIC INSTALLATION/DELIVERY OF STARTUP DATA
7554 --****************************************************************************
7555
7556 PROCEDURE install
7557 -----------------
7558 IS
7559
7560 cursor c_main is
7561 select distinct legislation_code
7562 from hr_s_history;
7563
7564 v_exception_counter number (15);
7565
7566 -- This install procedure calls the parameterised install procedure.
7567 -- The order of processing is as follows:
7568 -- 1. call install in phase 1
7569 -- 2. if exceptions exist, rollback, warn user and exit.
7570 -- 3. call install in phase 2
7571 -- 4. if exceptions exist, rollback, warn user and exit.
7572 -- 5. commit.
7573
7574 l_leg_code VARCHAR2(3);
7575
7576 BEGIN
7577
7578 ff_data_dict.disable_ffuebru_trig;
7579
7580 -- call installation in phase 1
7581
7582 g_debug_cnt := 0;
7583
7584 select count(*)
7585 into g_debug_cnt
7586 from pay_patch_status
7587 where patch_name = 'HRGLOBAL_DEBUG';
7588
7589 install(1);
7590
7591 -- check for the existance of any exceptions after phase 1
7592
7593 hr_utility.set_Location('hr_legislation.install2',20);
7594
7595 select count(*)
7596 into v_exception_counter
7597 from hr_stu_exceptions;
7598
7599 IF v_exception_counter > 0 THEN
7600
7601 hr_utility.set_message(801,'HR_7129_STARTUP_EXCEPTIONS');
7602 hr_utility.set_message_token('PHASE_NUMBER','1');
7603 hr_utility.raise_error;
7604
7605 END IF;
7606
7607 -- perform phase 2 of the installation
7608
7609 hr_utility.set_Location('hr_legislation.install2',40);
7610
7611 install(2);
7612
7616
7613 -- check for the existance of exceptions after phase 2
7614
7615 hr_utility.set_Location('hr_legislation.install2',50);
7617 select count(*)
7618 into v_exception_counter
7619 from hr_stu_exceptions;
7620
7621 IF v_exception_counter > 0 THEN
7622
7623 hr_utility.set_message(801,'HR_7129_STARTUP_EXCEPTIONS');
7624 hr_utility.set_message_token('PHASE_NUMBER','2');
7625 hr_utility.raise_error;
7626
7627 END IF;
7628
7629 -- Move the hr_s_application_ownership data to the HR_APPLICATION_OWNERSHIP
7630 -- tables for the International Payroll project (leg_code ZZ)
7631
7632 BEGIN
7633 select distinct legislation_code
7634 into l_leg_code
7635 from hr_s_history
7636 where legislation_code = 'ZZ';
7637
7638 create_zz_leg_rule;
7639
7640 EXCEPTION WHEN OTHERS THEN NULL;
7641 END;
7642
7643 -- Populate the TL tables with the installed data.
7644 install_att;
7645
7646 -- The whole installation was performed with no exceptions
7647 -- so it is now safe to commit.
7648
7649 ff_data_dict.enable_ffuebru_trig;
7650 commit;
7651
7652 END install;
7653
7654 END hr_legislation;
7655
[Home] [Help]