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