[Home] [Help]
PACKAGE BODY: APPS.PAY_DBI_STARTUP_PKG
Source
1 package body pay_dbi_startup_pkg as
2 /* $Header: pystrdbi.pkb 115.4 99/07/17 06:35:33 porting ship $ */
3 /*
4 --
5 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
6 --
7 /*
8 NAME
9 pystrdbi.pkb
10 --
11 DESCRIPTION
12 Procedures used for creating the start up data for database items,
13 namely the routes and the appropriate contexts. The procedure
14 create_dbi_startup is called from the main start up file.
15 --
16 MODIFIED (DD-MON-YYYY)
17 alogue 16-MAR-1999 - Removed GET_HOURS_WORKED.
18 alogue 11-JAN-1999 - Added GET_HOURS_WORKED to ff_functions.
19 R11.5 change whereby date contexts are passed
20 into routes as dates (and thus don't require
21 a to_date() on them).
22 alogue 20-MAY-1998 - Fix to ABSENCE_SUM_OF_ELEMENT_ENTRY_VALUES route
23 text.
24 alogue 31-OCT-1997 - change to GRADE_RATE_ROUTE route text.
25 alogue 24-OCT-1997 - change to RETROADJ_RUN_BAL_ROUTE route text.
26 alogue 05-AUG-1997 - Tidy Up: EVENTS_DESC_FLEX_ROUTE and
27 EMPLOYEE_ADDRESSES_DESC_FLEX_ROUTE
28 to use end of time as DD/MM/YY.
29 alogue 07-APR-1997 - Tidy Up: inclusion of bug 418051 route
30 ABSENCE_SUM_OF_ELEMENT_ENTRY_VALUES fix.
31 mwcallag 06-JAN-1995 - Performance changes resulting from the DEC
32 Benchmark. These include:
33 --
34 Route : ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES
35 modified by disabling the index on
36 EE.entry_type.
37 --
38 The following routes used to use the synonym
39 fnd_lookups. Originally this was a simple
40 table, but now is a complex view, and hence
41 these routes now refer to the view hr_lookups,
42 and also use the application id column (= 800)
43 for improved performance:
44 ELEMENT_TYPE_AT_TYPE_LEVEL
45 ELEMENT_TYPE_AT_TYPE_LEVEL_DP
46 ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL
47 ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL_DP
48 --
49 The following routes use the view hr_lookups.
50 They have been modified to also use the
51 application id column (= 800) for improved
52 performance:
53 INPUT_VALUE_FROM_INPUT_VALUE_TABLE
54 INPUT_VALUE_FROM_INPUT_VALUE_TABLE_DP
55 --
56 mwcallag 13-OCT-1994 - Route PAYROLL_ACTION_FLEXFIELD_ROUTE deleted.
57 rfine 05-OCT-1994 - Renamed package to pay_dbi_startup_pkg.
58 mwcallag 29-APR-1994 Route INPUT_VALUE_ENTRY_LEVEL tuned.
59 mwcallag 07-DEC-1993 - G291 Change to Legal Company route, now uses the
60 passed in context parameter.
61 mwcallag 01-DEC-1993 - inserts for User Defined Tables added.
62 mwcallag 03-NOV-1993 - Assignment Developer Descriptive flex addded.
63 mwcallag 02-NOV-1993 - Join to benefit_classifications added to
64 element_type_at_type_level route.
65 mwcallag 01-SEP-1993 - Date paid routes added for element types and
66 input values.
67 mwcallag 12-AUG-1993 - Minor change to Legal Company route.
68 mwcallag 11-AUG-1993 - Organization Payment Methods DF, External Account
69 Keyflex, and Legal company DF routes added.
70 mwcallag 09-AUG-1993 - More routes for Descriptive flexfields added.
71 mwcallag 03-AUG-1993 - Organization Developer DF and SCL routes added.
72 mwcallag 21-JUN-1993 - date earned removed from absence type route.
73 mwcallag 24-MAY-1993 - grade rate route shortened following removal of
74 rate_type database item.
75 mwcallag 07-MAY-1993 - spine and key flex routes added.
76 mwcallag 30-APR-1993 - absence and descriptive flex routes added.
77 mwcallag 27-APR-1993 - created.
78 */
79 --
80 PROCEDURE create_dbi_startup is
81 l_text long;
82 l_date_earned_context_id number;
83 l_assign_id_context_id number;
84 l_payroll_context_id number;
85 l_payroll_action_context_id number;
86 l_org_pay_method_id number;
87 l_per_pay_method_id number;
88 l_tax_unit_id number;
89 l_assignment_action_id number;
90 l_business_group_id number;
91 l_function_id number;
92 l_temp number;
93 --
94 -- ******** local procedure : insert_route_parameters ********
95 --
96 procedure insert_route_parameters
97 (
98 p_parameter_name in varchar2,
99 p_data_type in varchar2,
100 p_sequence_no in number
101 ) is
102 begin
103 hr_utility.set_location('pay_dbi_startup_pkg.insert_route_parameters', 1);
104 insert into ff_route_parameters
105 (route_id,
106 sequence_no,
107 parameter_name,
108 data_type,
109 route_parameter_id)
110 select ff_routes_s.currval,
111 p_sequence_no,
112 p_parameter_name,
113 p_data_type,
114 ff_route_parameters_s.nextval
115 from dual;
116 end insert_route_parameters;
117 --
118 -- ******** local procedure : insert_route_context_usages ********
119 --
120 procedure insert_route_context_usages
121 (
122 p_context_id in number,
123 p_sequence_no in number
124 ) is
125 begin
126 hr_utility.set_location('pay_dbi_startup_pkg.insert_route_context_usages', 1);
127 insert into ff_route_context_usages
128 (route_id,
129 context_id,
130 sequence_no)
131 select ff_routes_s.currval,
132 p_context_id,
133 p_sequence_no
134 from dual;
135 end insert_route_context_usages;
136 --
137 BEGIN
138 --
139 -- get the context ids from the context table
140 --
141 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 1);
142 select context_id
143 into l_date_earned_context_id
144 from ff_contexts
145 where context_name = 'DATE_EARNED';
146 --
147 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 2);
148 select context_id
149 into l_assign_id_context_id
150 from ff_contexts
151 where context_name = 'ASSIGNMENT_ID';
152 --
153 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 3);
154 select context_id
155 into l_payroll_context_id
156 from ff_contexts
157 where context_name = 'PAYROLL_ID';
158 --
159 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 4);
160 select context_id
161 into l_payroll_action_context_id
162 from ff_contexts
163 where context_name = 'PAYROLL_ACTION_ID';
164 --
165 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 5);
166 select context_id
167 into l_org_pay_method_id
168 from ff_contexts
169 where context_name = 'ORG_PAY_METHOD_ID';
170 --
171 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 6);
172 select context_id
173 into l_per_pay_method_id
174 from ff_contexts
175 where context_name = 'PER_PAY_METHOD_ID';
176 --
177 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 7);
178 select context_id
179 into l_tax_unit_id
180 from ff_contexts
181 where context_name = 'TAX_UNIT_ID';
182 --
183 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 8);
184 select context_id
185 into l_assignment_action_id
186 from ff_contexts
187 where context_name = 'ASSIGNMENT_ACTION_ID';
188 --
189 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
190 -- +
191 -- element type route, name : ELEMENT_TYPE_AT_TYPE_LEVEL +
192 -- +
193 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
194 --
195 l_text := 'pay_element_types_f ETYPE,
196 pay_element_classifications ECLASS,
197 ben_benefit_classifications BCLASS,
198 hr_lookups CELOOK
199 WHERE ETYPE.element_type_id = &U1
200 AND &B1 BETWEEN ETYPE.effective_start_date
201 AND ETYPE.effective_end_date
202 AND ETYPE.classification_id = ECLASS.classification_id
203 AND BCLASS.benefit_classification_id (+)= ETYPE.benefit_classification_id
204 AND ETYPE.closed_for_entry_flag = CELOOK.lookup_code
205 AND CELOOK.application_id = 800
206 AND CELOOK.lookup_type = ''YES_NO''';
207 --
208 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 8);
209 insert into ff_routes
210 (route_id,
211 route_name,
212 user_defined_flag,
213 description,
214 text,
215 last_update_date,
216 last_updated_by,
217 last_update_login,
218 created_by,
219 creation_date)
220 select ff_routes_s.nextval,
221 'ELEMENT_TYPE_AT_TYPE_LEVEL',
222 'N',
223 'simple element type route',
224 l_text,
225 sysdate,
226 0,
227 0,
228 0,
229 sysdate
230 from dual;
231 --
232 insert_route_parameters ('Element Type ID', 'N', 1);
233 insert_route_context_usages (l_date_earned_context_id, 1);
234 --
235 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
236 -- +
237 -- Date paid element type route : ELEMENT_TYPE_AT_TYPE_LEVEL_DP +
238 -- +
239 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
240 --
241 l_text := '/* route for date paid element type at type level */
242 pay_element_types_f ETYPE,
243 pay_element_classifications ECLASS,
244 ben_benefit_classifications BCLASS,
245 hr_lookups CELOOK,
246 fnd_sessions SES
247 WHERE ETYPE.element_type_id = &U1
248 AND SES.session_id = USERENV(''SESSIONID'')
249 AND SES.effective_date BETWEEN ETYPE.effective_start_date
250 AND ETYPE.effective_end_date
251 AND ETYPE.classification_id = ECLASS.classification_id
252 AND BCLASS.benefit_classification_id(+)= ETYPE.benefit_classification_id
253 AND ETYPE.closed_for_entry_flag = CELOOK.lookup_code
254 AND CELOOK.application_id = 800
255 AND CELOOK.lookup_type = ''YES_NO''';
256 --
257 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 9);
258 insert into ff_routes
259 (route_id,
260 route_name,
261 user_defined_flag,
262 description,
263 text,
264 last_update_date,
265 last_updated_by,
266 last_update_login,
267 created_by,
268 creation_date)
269 select ff_routes_s.nextval,
270 'ELEMENT_TYPE_AT_TYPE_LEVEL_DP',
271 'N',
272 'route for date paid element type at type level',
273 l_text,
274 sysdate,
275 0,
276 0,
277 0,
278 sysdate
279 from dual;
280 --
281 insert_route_parameters ('Element Type ID', 'N', 1);
282 --
283 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
284 -- +
285 -- element type route, name : ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL +
286 -- +
287 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
288 --
289 l_text := 'pay_element_links_f ELINK,
290 pay_element_types_f ETYPE,
291 per_assignments_f PERA,
292 hr_lookups QULOOK_LINK,
293 hr_lookups QULOOK_TYPE,
294 hr_lookups CTLOOK,
295 hr_lookups SLLOOK_LINK,
296 hr_lookups SLLOOK_TYPE
297 WHERE ETYPE.element_type_id = &U1
298 AND &B1 BETWEEN ETYPE.effective_start_date
299 AND ETYPE.effective_end_date
300 AND ETYPE.element_type_id (+)= ELINK.element_type_id
301 AND &B1 BETWEEN ELINK.effective_start_date
302 AND ELINK.effective_end_date
303 AND PERA.assignment_id = &B2
304 AND &B1 BETWEEN PERA.effective_start_date
305 AND PERA.effective_end_date
306 AND ((ELINK.payroll_id IS NOT NULL
307 AND ELINK.payroll_id = PERA.payroll_id)
308 OR (ELINK.link_to_all_payrolls_flag = ''Y''
309 AND PERA.payroll_id IS NOT NULL)
310 OR ELINK.payroll_id IS NULL)
311 AND (ELINK.organization_id = PERA.organization_id
312 OR ELINK.organization_id IS NULL)
313 AND (ELINK.position_id = PERA.position_id
314 OR ELINK.position_id IS NULL)
315 AND (ELINK.job_id = PERA.job_id
316 OR ELINK.job_id IS NULL)
317 AND (ELINK.grade_id = PERA.grade_id
318 OR ELINK.grade_id IS NULL)
319 AND (ELINK.location_id = PERA.location_id
320 OR ELINK.location_id IS NULL)
321 AND (ELINK.people_group_id IS NULL
322 OR EXISTS
323 (SELECT 1
324 FROM pay_assignment_link_usages_f PAL
325 WHERE PAL.assignment_id = &B2
326 AND PAL.element_link_id = ELINK.element_link_id
327 AND &B1 BETWEEN PAL.effective_start_date
328 AND PAL.effective_end_date))
329 AND QULOOK_LINK.lookup_code (+)= ELINK.qualifying_units
330 AND QULOOK_LINK.lookup_type (+)= ''QUALIFYING_UNITS''
331 AND QULOOK_LINK.application_id (+)= 800
332 AND QULOOK_TYPE.lookup_code (+)= ETYPE.qualifying_units
333 AND QULOOK_TYPE.lookup_type (+)= ''QUALIFYING_UNITS''
334 AND QULOOK_TYPE.application_id (+)= 800
335 AND SLLOOK_LINK.lookup_code (+)= ELINK.standard_link_flag
336 AND SLLOOK_LINK.lookup_type (+)= ''YES_NO''
337 AND SLLOOK_LINK.application_id (+)= 800
338 AND SLLOOK_TYPE.lookup_code (+)= ETYPE.standard_link_flag
339 AND SLLOOK_TYPE.lookup_type (+)= ''YES_NO''
340 AND SLLOOK_TYPE.application_id (+)= 800
341 AND ELINK.costable_type = CTLOOK.lookup_code
342 AND CTLOOK.application_id = 800
343 AND CTLOOK.lookup_type = ''COSTABLE_TYPE''';
344 --
348 --
345 -- the above route text is so long that we hit a current PL/SQL bug of
346 -- inserting with a select when using a long data type. So select the next
347 -- value for the route_id separately, until this bug is fixed:
349 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 10);
350 select ff_routes_s.nextval
351 into l_temp
352 from dual;
353 --
354 -- now do the normal insert
355 --
356 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 11);
357 insert into ff_routes
358 (route_id,
359 route_name,
360 user_defined_flag,
361 description,
362 text,
363 last_update_date,
364 last_updated_by,
365 last_update_login,
366 created_by,
367 creation_date)
368 values (ff_routes_s.currval,
369 'ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL',
370 'N',
371 'element type information with partial matching to the element link table',
372 l_text,
373 sysdate,
374 0,
375 0,
376 0,
377 sysdate);
378 --
379 insert_route_parameters ('Element Type ID', 'N', 1);
380 insert_route_context_usages (l_date_earned_context_id, 1);
381 insert_route_context_usages (l_assign_id_context_id, 2);
382 --
383 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
384 -- +
385 -- Date paid element type : ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL_DP +
386 -- +
387 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
388 --
389 l_text := '/* route for date paid element type at assignment level */
390 pay_element_links_f ELINK,
391 pay_element_types_f ETYPE,
392 per_assignments_f PERA,
393 hr_lookups QULOOK_LINK,
394 hr_lookups QULOOK_TYPE,
395 hr_lookups CTLOOK,
396 hr_lookups SLLOOK_LINK,
397 hr_lookups SLLOOK_TYPE,
398 fnd_sessions SES
399 WHERE ETYPE.element_type_id = &U1
400 AND SES.session_id = USERENV(''SESSIONID'')
401 AND SES.effective_date BETWEEN ETYPE.effective_start_date
402 AND ETYPE.effective_end_date
403 AND ETYPE.element_type_id (+)= ELINK.element_type_id
404 AND SES.effective_date BETWEEN ELINK.effective_start_date
405 AND ELINK.effective_end_date
406 AND PERA.assignment_id = &B1
407 AND SES.effective_date BETWEEN PERA.effective_start_date
408 AND PERA.effective_end_date
409 AND ((ELINK.payroll_id IS NOT NULL
410 AND ELINK.payroll_id = PERA.payroll_id)
411 OR (ELINK.link_to_all_payrolls_flag = ''Y''
412 AND PERA.payroll_id IS NOT NULL)
413 OR ELINK.payroll_id IS NULL)
414 AND (ELINK.organization_id = PERA.organization_id
415 OR ELINK.organization_id IS NULL)
416 AND (ELINK.position_id = PERA.position_id
417 OR ELINK.position_id IS NULL)
418 AND (ELINK.job_id = PERA.job_id
419 OR ELINK.job_id IS NULL)
420 AND (ELINK.grade_id = PERA.grade_id
421 OR ELINK.grade_id IS NULL)
422 AND (ELINK.location_id = PERA.location_id
423 OR ELINK.location_id IS NULL)
424 AND (ELINK.people_group_id IS NULL
425 OR EXISTS
426 (SELECT 1
427 FROM pay_assignment_link_usages_f PAL
428 WHERE PAL.assignment_id = &B1
429 AND PAL.element_link_id = ELINK.element_link_id
430 AND SES.effective_date BETWEEN PAL.effective_start_date
431 AND PAL.effective_end_date))
432 AND QULOOK_LINK.lookup_code (+)= ELINK.qualifying_units
433 AND QULOOK_LINK.lookup_type (+)= ''QUALIFYING_UNITS''
434 AND QULOOK_LINK.application_id (+)= 800
435 AND QULOOK_TYPE.lookup_code (+)= ETYPE.qualifying_units
436 AND QULOOK_TYPE.lookup_type (+)= ''QUALIFYING_UNITS''
437 AND QULOOK_TYPE.application_id (+)= 800
438 AND SLLOOK_LINK.lookup_code (+)= ELINK.standard_link_flag
439 AND SLLOOK_LINK.lookup_type (+)= ''YES_NO''
440 AND SLLOOK_LINK.application_id (+)= 800
441 AND SLLOOK_TYPE.lookup_code (+)= ETYPE.standard_link_flag
442 AND SLLOOK_TYPE.lookup_type (+)= ''YES_NO''
443 AND SLLOOK_TYPE.application_id (+)= 800
444 AND ELINK.costable_type = CTLOOK.lookup_code
445 AND CTLOOK.application_id = 800
446 AND CTLOOK.lookup_type = ''COSTABLE_TYPE''';
447 --
448 -- the above route text is so long that we hit a current PL/SQL bug of
449 -- inserting with a select when using a long data type. So select the next
450 -- value for the route_id separately, until this bug is fixed:
451 --
452 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 12);
453 select ff_routes_s.nextval
454 into l_temp
455 from dual;
456 --
457 -- now do the normal insert
458 --
459 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 13);
460 insert into ff_routes
461 (route_id,
462 route_name,
466 last_update_date,
463 user_defined_flag,
464 description,
465 text,
467 last_updated_by,
468 last_update_login,
469 created_by,
470 creation_date)
471 values (ff_routes_s.currval,
472 'ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL_DP',
473 'N',
474 'Date paid element type info with partial matching to the element link table',
475 l_text,
476 sysdate,
477 0,
478 0,
479 0,
480 sysdate);
481 --
482 insert_route_parameters ('Element Type ID', 'N', 1);
483 insert_route_context_usages (l_assign_id_context_id, 1);
484 --
485 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
486 -- +
487 -- element type route, name : ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES +
488 -- +
489 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
490 --
491 -- note : The upper on the EE.entry_type is to force the route to use
492 -- the more selective n4 index (1:element_link_id, 2:assignment_id)
493 -- rather than the n50 index (1:assignment_id, 2:entry_type, 3:start_date,
494 -- 4:end_date).
495 --
496 l_text := 'pay_element_entries_f EE,
497 pay_element_links_f ELINK,
498 pay_element_types_f ETYPE
499 WHERE &B1 BETWEEN EE.effective_start_date
500 AND EE.effective_end_date
501 AND upper (EE.entry_type) = ''E''
502 AND EE.element_link_id = ELINK.element_link_id
503 AND &B1 BETWEEN ELINK.effective_start_date
504 AND ELINK.effective_end_date
505 AND ELINK.element_type_id = ETYPE.element_type_id
506 AND &B1 BETWEEN ETYPE.effective_start_date
507 AND ETYPE.effective_end_date
508 AND ETYPE.element_type_id = &U1
509 AND EE.assignment_id = &B2';
510 --
511 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 14);
512 insert into ff_routes
513 (route_id,
514 route_name,
515 user_defined_flag,
516 description,
517 text,
518 last_update_date,
519 last_updated_by,
520 last_update_login,
521 created_by,
522 creation_date)
523 select ff_routes_s.nextval,
524 'ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES',
525 'N',
526 'route to element entry table for given assignment and element type',
527 l_text,
528 sysdate,
529 0,
530 0,
531 0,
532 sysdate
533 from dual;
534 --
535 insert_route_parameters ('Element Type ID', 'N', 1);
536 insert_route_context_usages (l_date_earned_context_id, 1);
537 insert_route_context_usages (l_assign_id_context_id, 2);
538 --
539 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
540 -- +
541 -- Date paid, element type : ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES_DP +
542 -- +
543 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
544 --
545 l_text := '/* route for date paid element type count of entries */
546 pay_element_entries_f EE,
547 pay_element_links_f ELINK,
548 pay_element_types_f ETYPE,
549 fnd_sessions SES
550 WHERE EE.assignment_id = &B1
551 and SES.session_id = USERENV(''SESSIONID'')
552 AND SES.effective_date BETWEEN EE.effective_start_date
553 AND EE.effective_end_date
554 AND EE.entry_type = ''E''
555 AND EE.element_link_id = ELINK.element_link_id
556 AND SES.effective_date BETWEEN ELINK.effective_start_date
557 AND ELINK.effective_end_date
558 AND ELINK.element_type_id = ETYPE.element_type_id
559 AND SES.effective_date BETWEEN ETYPE.effective_start_date
560 AND ETYPE.effective_end_date
561 AND ETYPE.element_type_id = &U1';
562 --
563 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 15);
564 insert into ff_routes
565 (route_id,
566 route_name,
567 user_defined_flag,
568 description,
569 text,
570 last_update_date,
571 last_updated_by,
572 last_update_login,
573 created_by,
574 creation_date)
575 select ff_routes_s.nextval,
576 'ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES_DP',
577 'N',
578 'route for DP, element entry table for given assignment and element type',
579 l_text,
580 sysdate,
584 sysdate
581 0,
582 0,
583 0,
585 from dual;
586 --
587 insert_route_parameters ('Element Type ID', 'N', 1);
588 insert_route_context_usages (l_assign_id_context_id, 1);
589 --
590 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
591 -- +
592 -- input value route, name: INPUT_VALUE_FROM_INPUT_VALUE_TABLE +
593 -- +
594 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
595 --
596 l_text := 'pay_input_values_f INPUTV,
597 hr_lookups UMLOOK
598 WHERE INPUTV.input_value_id = &U1
599 AND &B1 BETWEEN INPUTV.effective_start_date
600 AND INPUTV.effective_end_date
601 AND INPUTV.uom = UMLOOK.lookup_code
602 AND UMLOOK.application_id = 800
603 AND UMLOOK.lookup_type = ''UNITS''';
604 --
605 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 16);
606 insert into ff_routes
607 (route_id,
608 route_name,
609 user_defined_flag,
610 description,
611 text,
612 last_update_date,
613 last_updated_by,
614 last_update_login,
615 created_by,
616 creation_date)
617 select ff_routes_s.nextval,
618 'INPUT_VALUE_FROM_INPUT_VALUE_TABLE',
619 'N',
620 'route to input value table',
621 l_text,
622 sysdate,
623 0,
624 0,
625 0,
626 sysdate
627 from dual;
628 --
629 insert_route_parameters ('Input value ID', 'N', 1);
630 insert_route_context_usages (l_date_earned_context_id, 1);
631 --
632 --+++++++++++++++++++********+++++++++++++++++++++++++++++++++++++++++++
633 -- +
634 -- Date paid input value route : INPUT_VALUE_FROM_INPUT_VALUE_TABLE_DP +
635 -- +
636 --+++++++++++++++++++********+++++++++++++++++++++++++++++++++++++++++++
637 --
638 l_text := '/* route for date paid input value */
642 WHERE INPUTV.input_value_id = &U1
639 pay_input_values_f INPUTV,
640 hr_lookups UMLOOK,
641 fnd_sessions SES
643 AND SES.session_id = USERENV(''SESSIONID'')
644 AND SES.effective_date BETWEEN INPUTV.effective_start_date
645 AND INPUTV.effective_end_date
646 AND INPUTV.uom = UMLOOK.lookup_code
647 AND UMLOOK.application_id = 800
648 AND UMLOOK.lookup_type = ''UNITS''';
649 --
650 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 17);
651 insert into ff_routes
652 (route_id,
653 route_name,
654 user_defined_flag,
655 description,
656 text,
657 last_update_date,
658 last_updated_by,
659 last_update_login,
660 created_by,
661 creation_date)
662 select ff_routes_s.nextval,
663 'INPUT_VALUE_FROM_INPUT_VALUE_TABLE_DP',
664 'N',
665 'Date paid route to input value table',
666 l_text,
667 sysdate,
668 0,
669 0,
670 0,
671 sysdate
672 from dual;
673 --
674 insert_route_parameters ('Input value ID', 'N', 1);
675 --
676 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
677 -- +
678 -- input value route, name: INPUT_VALUE_USING_PARTIAL_MATCHING +
679 -- +
680 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
681 --
682 l_text := 'pay_input_values_f INPUTV,
683 pay_link_input_values_f LIV,
684 pay_element_types_f ETYPE,
685 pay_element_links_f ELINK,
686 per_assignments_f PERA
687 WHERE INPUTV.input_value_id = &U1
688 AND &B1 BETWEEN INPUTV.effective_start_date
689 AND INPUTV.effective_end_date
690 AND INPUTV.input_value_id = LIV.input_value_id
691 AND INPUTV.element_type_id = ETYPE.element_type_id
692 AND &B1 BETWEEN ETYPE.effective_start_date
693 AND ETYPE.effective_end_date
694 AND ETYPE.element_type_id (+)= ELINK.element_type_id
695 AND &B1 BETWEEN ELINK.effective_start_date
696 AND ELINK.effective_end_date
697 AND PERA.assignment_id = &B2
698 AND &B1 BETWEEN PERA.effective_start_date
699 AND PERA.effective_end_date
700 AND ((ELINK.payroll_id IS NOT NULL
701 AND ELINK.payroll_id = PERA.payroll_id)
702 OR (ELINK.link_to_all_payrolls_flag = ''Y''
703 AND PERA.payroll_id IS NOT NULL)
707 AND (ELINK.position_id = PERA.position_id
704 OR ELINK.payroll_id IS NULL)
705 AND (ELINK.organization_id = PERA.organization_id
706 OR ELINK.organization_id IS NULL)
708 OR ELINK.position_id IS NULL)
709 AND (ELINK.job_id = PERA.job_id
710 OR ELINK.job_id IS NULL)
711 AND (ELINK.grade_id = PERA.grade_id
712 OR ELINK.grade_id IS NULL)
713 AND (ELINK.location_id = PERA.location_id
714 OR ELINK.location_id IS NULL)
715 AND (ELINK.people_group_id IS NULL
716 OR EXISTS
717 (SELECT 1
718 FROM pay_assignment_link_usages_f PAL
719 WHERE PAL.assignment_id = &B2
720 AND PAL.element_link_id = ELINK.element_link_id
721 AND &B1 BETWEEN PAL.effective_start_date
722 AND PAL.effective_end_date))
723 AND ELINK.element_link_id = LIV.element_link_id
724 AND &B1 BETWEEN LIV.effective_start_date
725 AND LIV.effective_end_date';
726 --
727 -- the above route text is so long that we hit a current PL/SQL bug of
728 -- inserting with a select when using a long data type. So select the next
729 -- value for the route_id separately, until this bug is fixed:
730 --
731 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 18);
732 select ff_routes_s.nextval
733 into l_temp
734 from dual;
735 --
736 -- now do the normal insert
737 --
738 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 19);
739 insert into ff_routes
740 (route_id,
741 route_name,
742 user_defined_flag,
743 description,
744 text,
745 last_update_date,
746 last_updated_by,
747 last_update_login,
748 created_by,
749 creation_date)
750 values (ff_routes_s.currval,
751 'INPUT_VALUE_USING_PARTIAL_MATCHING',
752 'N',
753 'route for input value for given assignment id',
754 l_text,
755 sysdate,
756 0,
757 0,
758 0,
759 sysdate);
760 --
761 insert_route_parameters ('Input value ID', 'N', 1);
762 insert_route_context_usages (l_date_earned_context_id, 1);
763 insert_route_context_usages (l_assign_id_context_id, 2);
764 --
765 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
766 -- +
767 -- Date paid input value route: INPUT_VALUE_USING_PARTIAL_MATCHING_DP +
768 -- +
769 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
770 --
771 l_text := ' /* route for date paid input value partial matching */
772 pay_input_values_f INPUTV,
773 pay_link_input_values_f LIV,
777 fnd_sessions SES
774 pay_element_types_f ETYPE,
775 pay_element_links_f ELINK,
776 per_assignments_f PERA,
778 WHERE INPUTV.input_value_id = &U1
779 AND SES.session_id = USERENV(''SESSIONID'')
780 AND SES.effective_date BETWEEN INPUTV.effective_start_date
781 AND INPUTV.effective_end_date
782 AND INPUTV.input_value_id = LIV.input_value_id
783 AND INPUTV.element_type_id = ETYPE.element_type_id
784 AND SES.effective_date BETWEEN ETYPE.effective_start_date
785 AND ETYPE.effective_end_date
786 AND ETYPE.element_type_id (+)= ELINK.element_type_id
787 AND SES.effective_date BETWEEN ELINK.effective_start_date
788 AND ELINK.effective_end_date
789 AND PERA.assignment_id = &B1
790 AND SES.effective_date BETWEEN PERA.effective_start_date
791 AND PERA.effective_end_date
792 AND ((ELINK.payroll_id IS NOT NULL
793 AND ELINK.payroll_id = PERA.payroll_id)
794 OR (ELINK.link_to_all_payrolls_flag = ''Y''
795 AND PERA.payroll_id IS NOT NULL)
796 OR ELINK.payroll_id IS NULL)
797 AND (ELINK.organization_id = PERA.organization_id
798 OR ELINK.organization_id IS NULL)
799 AND (ELINK.position_id = PERA.position_id
800 OR ELINK.position_id IS NULL)
801 AND (ELINK.job_id = PERA.job_id
802 OR ELINK.job_id IS NULL)
803 AND (ELINK.grade_id = PERA.grade_id
804 OR ELINK.grade_id IS NULL)
805 AND (ELINK.location_id = PERA.location_id
806 OR ELINK.location_id IS NULL)
807 AND (ELINK.people_group_id IS NULL
808 OR EXISTS
809 (SELECT 1
810 FROM pay_assignment_link_usages_f PAL
811 WHERE PAL.assignment_id = &B1
812 AND PAL.element_link_id = ELINK.element_link_id
813 AND SES.effective_date BETWEEN PAL.effective_start_date
814 AND PAL.effective_end_date))
815 AND ELINK.element_link_id = LIV.element_link_id
816 AND SES.effective_date BETWEEN LIV.effective_start_date
817 AND LIV.effective_end_date';
818 --
819 -- the above route text is so long that we hit a current PL/SQL bug of
820 -- inserting with a select when using a long data type. So select the next
821 -- value for the route_id separately, until this bug is fixed:
822 --
823 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 20);
824 select ff_routes_s.nextval
825 into l_temp
826 from dual;
827 --
828 -- now do the normal insert
829 --
830 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 21);
831 insert into ff_routes
832 (route_id,
833 route_name,
834 user_defined_flag,
835 description,
836 text,
840 created_by,
837 last_update_date,
838 last_updated_by,
839 last_update_login,
841 creation_date)
842 values (ff_routes_s.currval,
843 'INPUT_VALUE_USING_PARTIAL_MATCHING_DP',
844 'N',
845 'route for input value for given assignment id',
846 l_text,
847 sysdate,
848 0,
849 0,
850 0,
851 sysdate);
852 --
853 insert_route_parameters ('Input value ID', 'N', 1);
854 insert_route_context_usages (l_assign_id_context_id, 1);
855 --
856 --++++++++++++++++++++++++++++++++++++++++++++++++++++
857 -- +
858 -- input value route, name : INPUT_VALUE_ENTRY_LEVEL +
859 -- +
860 --++++++++++++++++++++++++++++++++++++++++++++++++++++
861 --
862 l_text := '/* Route : INPUT_VALUE_ENTRY_LEVEL */
863 pay_element_entry_values_f EEV,
864 pay_element_entries_f EE,
865 pay_link_input_values_f LIV,
866 pay_input_values_f INPUTV
867 WHERE INPUTV.input_value_id = &U1
868 AND &B1 BETWEEN INPUTV.effective_start_date
869 AND INPUTV.effective_end_date
870 AND INPUTV.element_type_id + 0 = &U2
871 AND LIV.input_value_id = INPUTV.input_value_id
872 AND &B1 BETWEEN LIV.effective_start_date
873 AND LIV.effective_end_date
874 AND EEV.input_value_id + 0 = INPUTV.input_value_id
875 AND EEV.element_entry_id = EE.element_entry_id
876 AND EEV.effective_start_date = EE.effective_start_date
877 AND EEV.effective_end_date = EE.effective_end_date
878 AND EE.element_link_id = LIV.element_link_id
879 AND EE.assignment_id = &B2
880 AND &B1 BETWEEN EE.effective_start_date
881 AND EE.effective_end_date
882 AND nvl(EE.ENTRY_TYPE, ''E'') = ''E''';
883 --
884 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 22);
885 insert into ff_routes
886 (route_id,
887 route_name,
888 user_defined_flag,
889 description,
890 text,
891 last_update_date,
892 last_updated_by,
893 last_update_login,
894 created_by,
895 creation_date)
896 select ff_routes_s.nextval,
897 'INPUT_VALUE_ENTRY_LEVEL',
898 'N',
899 'route for input value to element entry level',
900 l_text,
901 sysdate,
905 sysdate
902 0,
903 0,
904 0,
906 from dual;
907 --
908 insert_route_parameters ('Input value ID', 'N', 1);
909 insert_route_parameters ('Element Type ID', 'N', 2);
910 insert_route_context_usages (l_date_earned_context_id, 1);
911 insert_route_context_usages (l_assign_id_context_id, 2);
912 --
913 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
914 -- +
915 -- Date paid input value route : INPUT_VALUE_ENTRY_LEVEL_DP +
916 -- +
917 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
918 --
919 l_text := '/* route for date paid input value entry level */
920 pay_input_values_f INPUTV,
921 pay_element_entry_values_f EEV,
922 pay_link_input_values_f LIV,
923 pay_element_types_f ETYPE,
924 pay_element_links_f ELINK,
925 pay_element_entries_f EE,
926 fnd_sessions SES
927 WHERE INPUTV.input_value_id = &U1
928 AND SES.session_id = USERENV(''SESSIONID'')
929 AND SES.effective_date BETWEEN INPUTV.effective_start_date
930 AND INPUTV.effective_end_date
931 AND INPUTV.input_value_id = EEV.input_value_id
932 AND INPUTV.input_value_id = LIV.input_value_id
933 AND SES.effective_date BETWEEN LIV.effective_start_date
934 AND LIV.effective_end_date
935 AND INPUTV.element_type_id = ETYPE.element_type_id
936 AND SES.effective_date BETWEEN ETYPE.effective_start_date
937 AND ETYPE.effective_end_date
938 AND ETYPE.element_type_id = ELINK.element_type_id
939 AND SES.effective_date BETWEEN ELINK.effective_start_date
940 AND ELINK.effective_end_date
941 AND EE.assignment_id = &B1
942 AND SES.effective_date BETWEEN EE.effective_start_date
943 AND EE.effective_end_date
944 AND ELINK.element_link_id = EE.element_link_id
945 AND ELINK.element_link_id = LIV.element_link_id
946 AND EE.entry_type = ''E''
947 AND EE.element_entry_id = EEV.element_entry_id
948 AND SES.effective_date BETWEEN EEV.effective_start_date
949 AND EEV.effective_end_date';
950 --
951 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 23);
952 insert into ff_routes
953 (route_id,
954 route_name,
955 user_defined_flag,
956 description,
960 last_update_login,
957 text,
958 last_update_date,
959 last_updated_by,
961 created_by,
962 creation_date)
963 select ff_routes_s.nextval,
964 'INPUT_VALUE_ENTRY_LEVEL_DP',
965 'N',
966 'route for input value to element entry level',
967 l_text,
968 sysdate,
969 0,
970 0,
971 0,
972 sysdate
973 from dual;
974 --
975 insert_route_parameters ('Input value ID', 'N', 1);
976 insert_route_context_usages (l_assign_id_context_id, 1);
977 --
978 --+++++++++++++++++++++++++++++++++++++++
979 -- +
980 -- grade route, name : GRADE_RATE_ROUTE +
981 -- +
982 --+++++++++++++++++++++++++++++++++++++++
983 --
984 l_text := '/* route for grade rates */
985 pay_grade_rules_f GRULE,
986 per_assignments_f ASSIGN
987 WHERE &B1 BETWEEN GRULE.effective_start_date
988 AND GRULE.effective_end_date
989 AND GRULE.grade_or_spinal_point_id = ASSIGN.grade_id +0
990 AND GRULE.rate_type = ''G''
991 AND ASSIGN.assignment_id = &B2
992 AND &B1 BETWEEN ASSIGN.effective_start_date
993 AND ASSIGN.effective_end_date
994 AND GRULE.rate_id = &U1';
995 --
996 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 24);
997 insert into ff_routes
998 (route_id,
999 route_name,
1000 user_defined_flag,
1001 description,
1002 text,
1003 last_update_date,
1004 last_updated_by,
1005 last_update_login,
1006 created_by,
1007 creation_date)
1008 select ff_routes_s.nextval,
1009 'GRADE_RATE_ROUTE',
1010 'N',
1011 'route for grade rates',
1012 l_text,
1013 sysdate,
1014 0,
1015 0,
1016 0,
1017 sysdate
1018 from dual;
1019 --
1023 --
1020 insert_route_parameters ('Grade Rate ID', 'N', 1);
1021 insert_route_context_usages (l_date_earned_context_id, 1);
1022 insert_route_context_usages (l_assign_id_context_id, 2);
1024 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1025 -- +
1026 -- people flex route, name : PEOPLE_FLEXFIELD_ROUTE +
1027 -- +
1028 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1029 --
1030 l_text := '/* route for people flex */
1031 per_assignments_f ASSIGN,
1032 per_all_people_f target
1033 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1034 AND ASSIGN.effective_end_date
1035 AND ASSIGN.assignment_id = &B2
1036 AND ASSIGN.person_id = target.person_id
1037 AND &B1 BETWEEN target.effective_start_date
1038 AND target.effective_end_date';
1039 --
1040 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 25);
1041 insert into ff_routes
1042 (route_id,
1043 route_name,
1044 user_defined_flag,
1045 description,
1046 text,
1047 last_update_date,
1048 last_updated_by,
1049 last_update_login,
1050 created_by,
1051 creation_date)
1052 select ff_routes_s.nextval,
1053 'PEOPLE_FLEXFIELD_ROUTE',
1054 'N',
1055 'people group flexfield route',
1056 l_text,
1057 sysdate,
1058 0,
1059 0,
1060 0,
1061 sysdate
1062 from dual;
1063 --
1064 insert_route_context_usages (l_date_earned_context_id, 1);
1065 insert_route_context_usages (l_assign_id_context_id, 2);
1066 --
1067 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1068 -- +
1069 -- payroll flex route, name : PAYROLL_FLEXFIELD_ROUTE +
1070 -- +
1071 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1072 --
1073 l_text := '/* route for payrolls flex */
1074 pay_all_payrolls_f target
1075 WHERE target.payroll_id = &B1
1076 AND &B2 BETWEEN target.effective_start_date
1077 AND target.effective_end_date';
1078 --
1079 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 26);
1080 insert into ff_routes
1081 (route_id,
1082 route_name,
1083 user_defined_flag,
1084 description,
1085 text,
1086 last_update_date,
1087 last_updated_by,
1088 last_update_login,
1092 'PAYROLL_FLEXFIELD_ROUTE',
1089 created_by,
1090 creation_date)
1091 select ff_routes_s.nextval,
1093 'N',
1094 'payroll flexfield route',
1095 l_text,
1096 sysdate,
1097 0,
1098 0,
1099 0,
1100 sysdate
1101 from dual;
1102 --
1103 insert_route_context_usages (l_payroll_context_id, 1);
1104 insert_route_context_usages (l_date_earned_context_id, 2);
1105 --
1106 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1107 -- +
1108 -- assignment flex route, name : ASSIGNMENT_FLEXFIELD_ROUTE +
1109 -- +
1110 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1111 --
1112 l_text := '/* route for assignment flex */
1113 per_all_assignments_f target
1114 WHERE &B1 BETWEEN target.effective_start_date
1115 AND target.effective_end_date
1116 AND target.assignment_id = &B2';
1117 --
1118 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 28);
1119 insert into ff_routes
1120 (route_id,
1121 route_name,
1122 user_defined_flag,
1123 description,
1124 text,
1125 last_update_date,
1126 last_updated_by,
1127 last_update_login,
1128 created_by,
1129 creation_date)
1130 select ff_routes_s.nextval,
1131 'ASSIGNMENT_FLEXFIELD_ROUTE',
1132 'N',
1133 'assignment flexfield route',
1134 l_text,
1135 sysdate,
1136 0,
1137 0,
1138 0,
1139 sysdate
1140 from dual;
1141 --
1142 insert_route_context_usages (l_date_earned_context_id, 1);
1143 insert_route_context_usages (l_assign_id_context_id, 2);
1144 --
1145 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1146 -- +
1147 -- grade flex route, name : GRADE_FLEXFIELD_ROUTE +
1148 -- +
1149 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1150 --
1154 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1151 l_text := '/* route for grade flex */
1152 per_grades target,
1153 per_assignments_f ASSIGN
1155 AND ASSIGN.effective_end_date
1156 AND ASSIGN.assignment_id = &B2
1157 AND ASSIGN.grade_id = target.grade_id';
1158 --
1159 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 29);
1160 insert into ff_routes
1161 (route_id,
1162 route_name,
1163 user_defined_flag,
1164 description,
1165 text,
1166 last_update_date,
1167 last_updated_by,
1168 last_update_login,
1169 created_by,
1170 creation_date)
1171 select ff_routes_s.nextval,
1172 'GRADE_FLEXFIELD_ROUTE',
1173 'N',
1174 'grade flexfield route',
1175 l_text,
1176 sysdate,
1177 0,
1178 0,
1179 0,
1180 sysdate
1181 from dual;
1182 --
1183 insert_route_context_usages (l_date_earned_context_id, 1);
1184 insert_route_context_usages (l_assign_id_context_id, 2);
1185 --
1186 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1187 -- +
1188 -- Absence descriptive flex : ABSENCE_DESC_FLEX_ROUTE +
1189 -- +
1190 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1191 --
1192 l_text := '/* route for absence descriptive flex */
1193 per_absence_attendances target,
1194 per_assignments_f ASSIGN
1195 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1199 AND target.person_id = ASSIGN.person_id
1196 AND ASSIGN.effective_end_date
1197 AND ASSIGN.assignment_id = &B2
1198 AND ASSIGN.assignment_type = ''E''
1200 AND target.absence_attendance_id =
1201 (select max (absence_attendance_id)
1202 from per_absence_attendances
1203 where person_id = ASSIGN.person_id
1204 and date_start <= &B1
1205 )';
1206 --
1207 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 30);
1208 insert into ff_routes
1209 (route_id,
1210 route_name,
1211 user_defined_flag,
1212 description,
1213 text,
1214 last_update_date,
1215 last_updated_by,
1216 last_update_login,
1217 created_by,
1218 creation_date)
1219 select ff_routes_s.nextval,
1220 'ABSENCE_DESC_FLEX_ROUTE',
1221 'N',
1222 'Absence flexfield route',
1223 l_text,
1224 sysdate,
1225 0,
1226 0,
1227 0,
1228 sysdate
1229 from dual;
1230 --
1231 insert_route_context_usages (l_date_earned_context_id, 1);
1232 insert_route_context_usages (l_assign_id_context_id, 2);
1233 --
1234 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1235 -- +
1236 -- Absence type descriptive flex : ABSENCE_TYPE_DESC_FLEX_ROUTE +
1237 -- +
1238 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1239 --
1240 l_text := '/* route for Absence type descriptive flex */
1241 per_absence_attendance_types target,
1242 per_absence_attendances ABSENCE,
1243 per_assignments_f ASSIGN
1244 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1248 AND ABSENCE.person_id = ASSIGN.person_id
1245 AND ASSIGN.effective_end_date
1246 AND ASSIGN.assignment_id = &B2
1247 AND ASSIGN.assignment_type = ''E''
1249 AND target.absence_attendance_type_id = ABSENCE.absence_attendance_type_id
1250 AND ABSENCE.absence_attendance_id =
1251 (select max (absence_attendance_id)
1252 from per_absence_attendances
1253 where person_id = ASSIGN.person_id
1254 and date_start <= &B1
1255 )';
1256 --
1257 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 31);
1258 insert into ff_routes
1259 (route_id,
1260 route_name,
1261 user_defined_flag,
1262 description,
1263 text,
1264 last_update_date,
1265 last_updated_by,
1266 last_update_login,
1267 created_by,
1268 creation_date)
1269 select ff_routes_s.nextval,
1270 'ABSENCE_TYPE_DESC_FLEX_ROUTE',
1271 'N',
1272 'Absence type flexfield route',
1273 l_text,
1274 sysdate,
1275 0,
1276 0,
1277 0,
1278 sysdate
1279 from dual;
1280 --
1281 insert_route_context_usages (l_date_earned_context_id, 1);
1282 insert_route_context_usages (l_assign_id_context_id, 2);
1283 --
1284 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1285 -- +
1286 -- Employee Addresses desc. flex : EMPLOYEE_ADDRESSES_DESC_FLEX_ROUTE +
1287 -- +
1288 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1289 --
1290 l_text := '/* route for Employee Addresses descriptive flex */
1291 per_addresses target,
1292 per_assignments_f ASSIGN
1293 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1294 AND ASSIGN.effective_end_date
1298 AND &B1 BETWEEN target.date_from
1295 AND ASSIGN.assignment_id = &B2
1296 AND target.person_id = ASSIGN.person_id
1297 AND target.primary_flag = ''Y''
1299 AND nvl (target.date_to, to_date (''4712/12/31'',''YYYY/MM/DD''))';
1300 --
1301 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 32);
1302 insert into ff_routes
1303 (route_id,
1304 route_name,
1305 user_defined_flag,
1306 description,
1307 text,
1308 last_update_date,
1309 last_updated_by,
1310 last_update_login,
1311 created_by,
1312 creation_date)
1313 select ff_routes_s.nextval,
1314 'EMPLOYEE_ADDRESSES_DESC_FLEX_ROUTE',
1315 'N',
1316 'Employee Addresses flexfield route',
1317 l_text,
1318 sysdate,
1319 0,
1320 0,
1321 0,
1322 sysdate
1323 from dual;
1324 --
1325 insert_route_context_usages (l_date_earned_context_id, 1);
1326 insert_route_context_usages (l_assign_id_context_id, 2);
1327 --
1328 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1329 -- +
1330 -- Events descriptive flex : EVENTS_DESC_FLEX_ROUTE +
1331 -- +
1332 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1333 --
1334 l_text := '/* route for events descriptive flex */
1335 per_events target
1336 WHERE target.assignment_id = &B2
1337 AND event_id =
1338 (select max (event_id)
1339 from per_events
1340 where assignment_id = &B2
1341 and &B1 between date_start
1342 and nvl (date_end, to_date (''4712/12/31'',''YYYY/MM/DD''))
1343 )';
1344 --
1345 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 33);
1346 insert into ff_routes
1347 (route_id,
1348 route_name,
1349 user_defined_flag,
1350 description,
1351 text,
1352 last_update_date,
1353 last_updated_by,
1354 last_update_login,
1355 created_by,
1356 creation_date)
1357 select ff_routes_s.nextval,
1358 'EVENTS_DESC_FLEX_ROUTE',
1359 'N',
1360 'Events flexfield route',
1361 l_text,
1362 sysdate,
1363 0,
1364 0,
1365 0,
1366 sysdate
1367 from dual;
1368 --
1372 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1369 insert_route_context_usages (l_date_earned_context_id, 1);
1370 insert_route_context_usages (l_assign_id_context_id, 2);
1371 --
1373 -- +
1374 -- Job descriptive flex : JOBS_DESC_FLEX_ROUTE +
1375 -- +
1376 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1377 --
1378 l_text := '/* route for Job descriptive flex */
1379 per_jobs target,
1380 per_assignments_f ASSIGN
1381 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1382 AND ASSIGN.effective_end_date
1383 AND ASSIGN.assignment_id = &B2
1384 AND ASSIGN.job_id = target.job_id';
1385 --
1386 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 34);
1387 insert into ff_routes
1388 (route_id,
1389 route_name,
1390 user_defined_flag,
1391 description,
1392 text,
1393 last_update_date,
1394 last_updated_by,
1395 last_update_login,
1396 created_by,
1397 creation_date)
1398 select ff_routes_s.nextval,
1399 'JOBS_DESC_FLEX_ROUTE',
1400 'N',
1401 'Job flexfield route',
1402 l_text,
1403 sysdate,
1404 0,
1405 0,
1406 0,
1407 sysdate
1408 from dual;
1409 --
1410 insert_route_context_usages (l_date_earned_context_id, 1);
1411 insert_route_context_usages (l_assign_id_context_id, 2);
1412 --
1413 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1414 -- +
1415 -- Contacts descriptive flex : CONTACTS_DESC_FLEX_ROUTE +
1416 -- +
1417 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1418 --
1419 l_text := '/* route for Contacts descriptive flex */
1420 per_contact_relationships target,
1421 per_assignments_f ASSIGN
1422 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1423 AND ASSIGN.effective_end_date
1424 AND ASSIGN.assignment_id = &B2
1425 AND ASSIGN.assignment_type = ''E''
1426 AND ASSIGN.person_id = target.contact_person_id
1427 AND target.primary_contact_flag = ''Y''';
1428 --
1429 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 35);
1430 insert into ff_routes
1431 (route_id,
1432 route_name,
1433 user_defined_flag,
1434 description,
1435 text,
1436 last_update_date,
1437 last_updated_by,
1438 last_update_login,
1439 created_by,
1440 creation_date)
1441 select ff_routes_s.nextval,
1445 l_text,
1442 'CONTACTS_DESC_FLEX_ROUTE',
1443 'N',
1444 'Contacts flexfield route',
1446 sysdate,
1447 0,
1448 0,
1449 0,
1450 sysdate
1451 from dual;
1452 --
1453 insert_route_context_usages (l_date_earned_context_id, 1);
1454 insert_route_context_usages (l_assign_id_context_id, 2);
1455 --
1456 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1457 -- +
1458 -- period of service desc flex : PERIODS_OF_SERVICE_DESC_FLEX_ROUTE +
1459 -- +
1460 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1461 --
1462 l_text := '/* route for periods of service descriptive flex */
1463 per_periods_of_service target,
1464 per_assignments_f ASSIGN
1465 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1466 AND ASSIGN.effective_end_date
1467 AND ASSIGN.assignment_id = &B2
1468 AND ASSIGN.assignment_type = ''E''
1469 AND target.period_of_service_id = ASSIGN.period_of_service_id';
1470 --
1471 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 36);
1472 insert into ff_routes
1473 (route_id,
1474 route_name,
1475 user_defined_flag,
1476 description,
1477 text,
1478 last_update_date,
1479 last_updated_by,
1480 last_update_login,
1481 created_by,
1482 creation_date)
1483 select ff_routes_s.nextval,
1484 'PERIODS_OF_SERVICE_DESC_FLEX_ROUTE',
1485 'N',
1486 'periods of service flexfield route',
1487 l_text,
1488 sysdate,
1489 0,
1490 0,
1491 0,
1492 sysdate
1493 from dual;
1494 --
1495 insert_route_context_usages (l_date_earned_context_id, 1);
1496 insert_route_context_usages (l_assign_id_context_id, 2);
1497 --
1498 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1499 -- +
1500 -- recruitment desc flex : RECRUITMENT_ACTIVITIES_DESC_FLEX_ROUTE +
1501 -- +
1502 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1503 --
1504 l_text := '/* route for recruitment activities descriptive flex */
1505 per_recruitment_activities target,
1506 per_assignments_f ASSIGN
1507 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1508 AND ASSIGN.effective_end_date
1509 AND ASSIGN.assignment_id = &B2
1510 AND ASSIGN.assignment_type = ''A''
1511 AND target.recruitment_activity_id = ASSIGN.recruitment_activity_id';
1512 --
1513 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 37);
1514 insert into ff_routes
1518 description,
1515 (route_id,
1516 route_name,
1517 user_defined_flag,
1519 text,
1520 last_update_date,
1521 last_updated_by,
1522 last_update_login,
1523 created_by,
1524 creation_date)
1525 select ff_routes_s.nextval,
1526 'RECRUITMENT_ACTIVITIES_DESC_FLEX_ROUTE',
1527 'N',
1528 'recruitment activities flexfield route',
1529 l_text,
1530 sysdate,
1531 0,
1532 0,
1533 0,
1534 sysdate
1535 from dual;
1536 --
1537 insert_route_context_usages (l_date_earned_context_id, 1);
1538 insert_route_context_usages (l_assign_id_context_id, 2);
1539 --
1540 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1541 -- +
1542 -- Positions descriptive flex : POSITION_DESC_FLEX_ROUTE +
1543 -- +
1544 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1545 --
1546 l_text := '/* route for Positions descriptive flex */
1547 per_positions target,
1548 per_assignments_f ASSIGN
1549 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1550 AND ASSIGN.effective_end_date
1551 AND ASSIGN.assignment_id = &B2
1552 AND target.position_id = ASSIGN.position_id';
1553 --
1554 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 38);
1555 insert into ff_routes
1556 (route_id,
1557 route_name,
1558 user_defined_flag,
1559 description,
1560 text,
1561 last_update_date,
1562 last_updated_by,
1563 last_update_login,
1564 created_by,
1565 creation_date)
1566 select ff_routes_s.nextval,
1567 'POSITION_DESC_FLEX_ROUTE',
1568 'N',
1569 'Positions flexfield route',
1570 l_text,
1571 sysdate,
1572 0,
1573 0,
1574 0,
1575 sysdate
1576 from dual;
1577 --
1578 insert_route_context_usages (l_date_earned_context_id, 1);
1579 insert_route_context_usages (l_assign_id_context_id, 2);
1580 --
1581 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1582 -- +
1583 -- Application descriptive flex : APPLICATIONS_DESC_FLEX_ROUTE +
1584 -- +
1585 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1586 --
1587 l_text := '/* route for Application descriptive flex */
1588 per_applications target,
1592 AND ASSIGN.assignment_id = &B2
1589 per_assignments_f ASSIGN
1590 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1591 AND ASSIGN.effective_end_date
1593 AND ASSIGN.assignment_type = ''A''
1594 AND target.application_id = ASSIGN.application_id';
1595 --
1596 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 39);
1597 insert into ff_routes
1598 (route_id,
1599 route_name,
1600 user_defined_flag,
1601 description,
1602 text,
1603 last_update_date,
1604 last_updated_by,
1605 last_update_login,
1606 created_by,
1607 creation_date)
1608 select ff_routes_s.nextval,
1609 'APPLICATIONS_DESC_FLEX_ROUTE',
1610 'N',
1611 'Applications flexfield route',
1612 l_text,
1613 sysdate,
1614 0,
1615 0,
1616 0,
1617 sysdate
1618 from dual;
1619 --
1620 insert_route_context_usages (l_date_earned_context_id, 1);
1621 insert_route_context_usages (l_assign_id_context_id, 2);
1622 --
1623 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1624 -- +
1625 -- Organization descriptive flex : ORGANIZATION_DESC_FLEX_ROUTE +
1626 -- +
1627 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1628 --
1629 l_text := '/* route for Organization descriptive flex */
1630 hr_organization_units target,
1631 per_assignments_f ASSIGN
1632 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1633 AND ASSIGN.effective_end_date
1634 AND ASSIGN.assignment_id = &B2
1635 AND target.organization_id = ASSIGN.organization_id';
1636 --
1637 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 40);
1638 insert into ff_routes
1639 (route_id,
1640 route_name,
1641 user_defined_flag,
1642 description,
1643 text,
1644 last_update_date,
1645 last_updated_by,
1646 last_update_login,
1647 created_by,
1648 creation_date)
1649 select ff_routes_s.nextval,
1650 'ORGANIZATION_DESC_FLEX_ROUTE',
1651 'N',
1652 'Organization flexfield route',
1653 l_text,
1654 sysdate,
1655 0,
1656 0,
1657 0,
1658 sysdate
1659 from dual;
1660 --
1661 insert_route_context_usages (l_date_earned_context_id, 1);
1662 insert_route_context_usages (l_assign_id_context_id, 2);
1663 --
1664 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1665 -- +
1669 --
1666 -- Organization Developer DF route : DEVELOPER_ORG_DESC_FLEX_ROUTE +
1667 -- +
1668 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1670 l_text := '/* route for Organization Developer DF */
1671 hr_organization_information target,
1672 per_assignments_f ASSIGN
1673 WHERE &B1 BETWEEN ASSIGN.effective_start_date
1674 AND ASSIGN.effective_end_date
1675 AND ASSIGN.assignment_id = &B2
1676 AND ASSIGN.organization_id = target.organization_id
1677 AND replace (ltrim(rtrim(target.org_information_context)),'' '',''_'')
1678 = &U1';
1679 --
1680 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 41);
1681 insert into ff_routes
1682 (route_id,
1683 route_name,
1684 user_defined_flag,
1685 description,
1686 text,
1687 last_update_date,
1688 last_updated_by,
1689 last_update_login,
1690 created_by,
1691 creation_date)
1692 select ff_routes_s.nextval,
1693 'DEVELOPER_ORG_DESC_FLEX_ROUTE',
1694 'N',
1695 'route for Organization Developer Descriptive Flexfield',
1696 l_text,
1697 sysdate,
1698 0,
1699 0,
1700 0,
1701 sysdate
1702 from dual;
1703 --
1704 insert_route_parameters ('Organization Information Context', 'T', 1);
1705 insert_route_context_usages (l_date_earned_context_id, 1);
1706 insert_route_context_usages (l_assign_id_context_id, 2);
1707 --
1708 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1709 -- +
1710 -- Assignment Developer DF route : DEVELOPER_ASS_DESC_FLEX_ROUTE +
1711 -- +
1712 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1713 --
1714 l_text := '/* route for Assignment Developer DF */
1715 per_assignment_extra_info target
1716 where target.assignment_id = &B1
1717 and replace (ltrim(rtrim(target.information_type)),'' '',''_'') = &U1';
1718 --
1719 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 42);
1720 insert into ff_routes
1721 (route_id,
1722 route_name,
1723 user_defined_flag,
1724 description,
1725 text,
1726 last_update_date,
1727 last_updated_by,
1728 last_update_login,
1729 created_by,
1730 creation_date)
1731 select ff_routes_s.nextval,
1732 'DEVELOPER_ASS_DESC_FLEX_ROUTE',
1733 'N',
1734 'route for Assignment Developer Descriptive Flexfield',
1735 l_text,
1736 sysdate,
1737 0,
1738 0,
1739 0,
1743 insert_route_parameters ('Assignment Information Context', 'T', 1);
1740 sysdate
1741 from dual;
1742 --
1744 insert_route_context_usages (l_assign_id_context_id, 1);
1745 --
1746 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1747 -- +
1748 -- Organization Payment DF : ORG_PAY_METHOD_DESC_FLEX_ROUTE +
1749 -- +
1750 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1751 --
1752 l_text := '/* route for Organization Payment Method DF */
1753 pay_org_payment_methods_f target
1754 where &B1 BETWEEN target.effective_start_date
1755 AND target.effective_end_date
1756 and target.org_payment_method_id = &B2
1757 and target.payment_type_id = &U1';
1758 --
1759 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 43);
1760 insert into ff_routes
1761 (route_id,
1762 route_name,
1763 user_defined_flag,
1764 description,
1765 text,
1766 last_update_date,
1767 last_updated_by,
1768 last_update_login,
1769 created_by,
1770 creation_date)
1771 select ff_routes_s.nextval,
1772 'ORG_PAY_METHOD_DESC_FLEX_ROUTE',
1773 'N',
1774 'route for Organization Payment Method Descriptive Flex',
1775 l_text,
1776 sysdate,
1777 0,
1778 0,
1779 0,
1780 sysdate
1781 from dual;
1782 --
1783 insert_route_parameters ('Payment Type id', 'N', 1);
1784 insert_route_context_usages (l_date_earned_context_id, 1);
1785 insert_route_context_usages (l_org_pay_method_id, 2);
1786 --
1787 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1788 -- +
1789 -- External Account Keyflex route : EXT_ACCOUNT_PER_KEYFLEX_ROUTE +
1790 -- +
1791 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1792 --
1793 l_text := '/* route for Personal External Account Keyflex */
1794 pay_external_accounts target
1795 , pay_personal_payment_methods_f PERPAY
1796 where &B1 BETWEEN PERPAY.effective_start_date
1797 AND PERPAY.effective_end_date
1798 and PERPAY.personal_payment_method_id = &B2
1799 and target.external_account_id (+)= PERPAY.external_account_id';
1800 --
1801 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 44);
1802 insert into ff_routes
1803 (route_id,
1804 route_name,
1805 user_defined_flag,
1806 description,
1807 text,
1808 last_update_date,
1812 creation_date)
1809 last_updated_by,
1810 last_update_login,
1811 created_by,
1813 select ff_routes_s.nextval,
1814 'EXT_ACCOUNT_PER_KEYFLEX_ROUTE',
1815 'N',
1816 'route for Personal External Account Keyflex',
1817 l_text,
1818 sysdate,
1819 0,
1820 0,
1821 0,
1822 sysdate
1823 from dual;
1824 --
1825 insert_route_context_usages (l_date_earned_context_id, 1);
1826 insert_route_context_usages (l_per_pay_method_id, 2);
1827 --
1828 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1829 -- +
1830 -- External Account Keyflex route : EXT_ACCOUNT_ORG_KEYFLEX_ROUTE +
1831 -- +
1832 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1833 --
1834 l_text := '/* route for Organization External Account Keyflex */
1835 pay_external_accounts target
1836 , pay_org_payment_methods_f ORGPAY
1837 where ORGPAY.org_payment_method_id = &B2
1838 and &B1 BETWEEN ORGPAY.effective_start_date
1839 AND ORGPAY.effective_end_date
1840 and target.external_account_id = ORGPAY.external_account_id';
1841 --
1842 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 45);
1843 insert into ff_routes
1844 (route_id,
1845 route_name,
1846 user_defined_flag,
1847 description,
1848 text,
1849 last_update_date,
1850 last_updated_by,
1851 last_update_login,
1852 created_by,
1853 creation_date)
1854 select ff_routes_s.nextval,
1855 'EXT_ACCOUNT_ORG_KEYFLEX_ROUTE',
1856 'N',
1857 'route for Organization External Account Keyflex',
1858 l_text,
1859 sysdate,
1860 0,
1861 0,
1862 0,
1863 sysdate
1864 from dual;
1865 --
1866 insert_route_context_usages (l_date_earned_context_id, 1);
1867 insert_route_context_usages (l_org_pay_method_id, 2);
1868 --
1869 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1870 -- +
1871 -- Legal Company DF route : LEGAL_CO_DESC_FLEX_ROUTE +
1872 -- +
1873 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1874 --
1875 l_text := '/* route for Legal Company Descriptive Flex */
1876 hr_organization_information target
1877 where target.organization_id = &B1
1878 AND replace(ltrim(rtrim(target.org_information_context)),'' '',''_'')
1879 = &U1';
1883 (route_id,
1880 --
1881 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 46);
1882 insert into ff_routes
1884 route_name,
1885 user_defined_flag,
1886 description,
1887 text,
1888 last_update_date,
1889 last_updated_by,
1890 last_update_login,
1891 created_by,
1892 creation_date)
1893 select ff_routes_s.nextval,
1894 'LEGAL_CO_DESC_FLEX_ROUTE',
1895 'N',
1896 'route for Legal Company Descriptive Flex',
1897 l_text,
1898 sysdate,
1899 0,
1900 0,
1901 0,
1902 sysdate
1903 from dual;
1904 --
1905 insert_route_parameters ('Organization Information Context', 'T', 1);
1906 insert_route_context_usages (l_tax_unit_id, 1);
1907 --
1908 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1909 -- +
1910 -- absence route, name : ABSENCE_SUM_OF_ELEMENT_ENTRY_VALUES +
1911 -- +
1912 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1913 --
1914 -- note : there is no context of date earned since the route needs to
1915 -- sum all element entry values for a given absence type regardless of
1916 -- the current session date.
1917 --
1918 l_text := '/* absence route */
1919 pay_element_entry_values_f target,
1920 pay_element_entries_f EE,
1921 pay_link_input_values_f LNKV,
1922 per_absence_attendance_types ABTYPE
1923 WHERE ABTYPE.absence_attendance_type_id = &U1
1924 and LNKV.input_value_id = ABTYPE.input_value_id
1925 AND TARGET.input_value_id+0 = LNKV.input_value_id
1926 and EE.element_link_ID = LNKV.element_link_id
1927 AND EE.element_entry_id = TARGET.element_entry_id
1928 and TARGET.effective_start_date between
1929 EE.effective_start_date and EE.effective_end_date
1930 and TARGET.effective_start_date between
1931 LNKV.effective_start_date and LNKV.effective_end_date
1932 AND EE.assignment_id = &B1';
1933 --
1934 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 47);
1935 insert into ff_routes
1936 (route_id,
1937 route_name,
1938 user_defined_flag,
1939 description,
1940 text,
1941 last_update_date,
1942 last_updated_by,
1943 last_update_login,
1944 created_by,
1945 creation_date)
1946 select ff_routes_s.nextval,
1947 'ABSENCE_SUM_OF_ELEMENT_ENTRY_VALUES',
1948 'N',
1949 'absence route to element entry values',
1950 l_text,
1951 sysdate,
1952 0,
1953 0,
1954 0,
1958 insert_route_parameters ('Absence Type ID', 'N', 1);
1955 sysdate
1956 from dual;
1957 --
1959 insert_route_context_usages (l_assign_id_context_id, 1);
1960 --
1961 --+++++++++++++++++++++++++++++++++++++++
1962 -- +
1963 -- spine route, name : SPINE_RATE_ROUTE +
1964 -- +
1965 --+++++++++++++++++++++++++++++++++++++++
1966 --
1967 l_text := '/* route for spine rates */
1968 pay_grade_rules_f target,
1969 per_spinal_point_steps_f sps,
1970 per_spinal_point_placements_f spp
1971 WHERE &B1 BETWEEN target.effective_start_date
1972 AND target.effective_end_date
1973 AND target.rate_type = ''SP''
1974 AND target.rate_id = &U1
1975 AND target.grade_or_spinal_point_id = sps.spinal_point_id
1976 AND &B1 BETWEEN sps.effective_start_date
1977 AND sps.effective_end_date
1978 AND sps.step_id = spp.step_id
1979 AND spp.assignment_id = &B2
1980 AND &B1 BETWEEN spp.effective_start_date
1981 AND spp.effective_end_date';
1982 --
1983 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 48);
1984 insert into ff_routes
1985 (route_id,
1986 route_name,
1987 user_defined_flag,
1988 description,
1989 text,
1990 last_update_date,
1991 last_updated_by,
1992 last_update_login,
1993 created_by,
1994 creation_date)
1995 select ff_routes_s.nextval,
1996 'SPINE_RATE_ROUTE',
1997 'N',
1998 'route for grade rates',
1999 l_text,
2000 sysdate,
2001 0,
2002 0,
2003 0,
2004 sysdate
2005 from dual;
2006 --
2007 insert_route_parameters ('Spine Rate ID', 'N', 1);
2008 insert_route_context_usages (l_date_earned_context_id, 1);
2009 insert_route_context_usages (l_assign_id_context_id, 2);
2010 --
2011 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2012 -- +
2013 -- key flexfield route, name : KEY_FLEXFIELD_ROUTE +
2014 -- +
2015 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2016 --
2017 l_text := '/* key flexfield route */
2018 per_all_assignments_f ASSIGN,
2019 per_all_positions POS,
2020 per_position_definitions POSDEF, /* target for position */
2021 per_grades GRA,
2022 per_grade_definitions GRADEF, /* target for grade */
2023 per_jobs JOB,
2024 per_job_definitions JOBDEF, /* target for job */
2025 pay_people_groups PGROUP /* target for group */
2026 WHERE &B1 BETWEEN ASSIGN.effective_start_date
2030 AND POSDEF.position_definition_id (+)= POS.position_definition_id
2027 AND ASSIGN.effective_end_date
2028 AND ASSIGN.assignment_id = &B2
2029 AND POS.position_id (+)= ASSIGN.position_id
2031 AND GRA.grade_id (+)= ASSIGN.grade_id
2032 AND GRADEF.grade_definition_id (+)= GRA.grade_definition_id
2033 AND JOB.job_id (+)= ASSIGN.job_id
2034 AND JOBDEF.job_definition_id (+)= JOB.job_definition_id
2035 AND PGROUP.people_group_id (+)= ASSIGN.people_group_id';
2036 --
2037 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 49);
2038 insert into ff_routes
2039 (route_id,
2040 route_name,
2041 user_defined_flag,
2042 description,
2043 text,
2044 last_update_date,
2045 last_updated_by,
2046 last_update_login,
2047 created_by,
2048 creation_date)
2049 select ff_routes_s.nextval,
2050 'KEY_FLEXFIELD_ROUTE',
2051 'N',
2052 'key flexfield route',
2053 l_text,
2054 sysdate,
2055 0,
2056 0,
2057 0,
2058 sysdate
2059 from dual;
2060 --
2061 insert_route_context_usages (l_date_earned_context_id, 1);
2062 insert_route_context_usages (l_assign_id_context_id, 2);
2063 --
2064 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2065 -- +
2066 -- SCL assignment level route : SCL_ASS_FLEX_ROUTE +
2067 -- +
2068 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2069 --
2070 l_text := '/* route for SCL keyflex - assignment level */
2071 hr_soft_coding_keyflex target,
2072 per_assignments_f ASSIGN
2073 WHERE &B1 BETWEEN ASSIGN.effective_start_date
2074 AND ASSIGN.effective_end_date
2075 AND ASSIGN.assignment_id = &B2
2076 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
2077 AND target.enabled_flag = ''Y''
2078 AND target.id_flex_num = &U1';
2079 --
2080 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 50);
2081 insert into ff_routes
2082 (route_id,
2083 route_name,
2084 user_defined_flag,
2085 description,
2086 text,
2087 last_update_date,
2088 last_updated_by,
2089 last_update_login,
2090 created_by,
2091 creation_date)
2092 select ff_routes_s.nextval,
2093 'SCL_ASS_FLEX_ROUTE',
2094 'N',
2095 'route for SCL assignment level Flexfield',
2096 l_text,
2100 0,
2097 sysdate,
2098 0,
2099 0,
2101 sysdate
2102 from dual;
2103 --
2104 insert_route_parameters ('ID flex number', 'N', 1);
2105 insert_route_context_usages (l_date_earned_context_id, 1);
2106 insert_route_context_usages (l_assign_id_context_id, 2);
2107 --
2108 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2109 -- +
2110 -- SCL payroll level route : SCL_PAY_FLEX_ROUTE +
2111 -- +
2112 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2113 --
2114 l_text := '/* route for SCL keyflex - payroll level */
2115 hr_soft_coding_keyflex target
2116 , per_assignments_f ASSIGN
2117 , pay_payrolls_f PAYROLL
2118 WHERE &B1 BETWEEN ASSIGN.effective_start_date
2119 AND ASSIGN.effective_end_date
2120 AND ASSIGN.assignment_id = &B2
2121 AND target.id_flex_num = &U1
2122 AND target.enabled_flag = ''Y''
2123 AND PAYROLL.payroll_id = ASSIGN.payroll_id
2124 AND &B1 BETWEEN PAYROLL.effective_start_date
2125 AND PAYROLL.effective_end_date
2126 AND target.soft_coding_keyflex_id = PAYROLL.soft_coding_keyflex_id';
2127 --
2128 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 51);
2129 insert into ff_routes
2130 (route_id,
2131 route_name,
2132 user_defined_flag,
2133 description,
2134 text,
2135 last_update_date,
2136 last_updated_by,
2137 last_update_login,
2138 created_by,
2139 creation_date)
2140 select ff_routes_s.nextval,
2141 'SCL_PAY_FLEX_ROUTE',
2142 'N',
2143 'route for SCL payroll level Flexfield',
2144 l_text,
2145 sysdate,
2146 0,
2147 0,
2148 0,
2149 sysdate
2150 from dual;
2151 --
2152 insert_route_parameters ('ID flex number', 'N', 1);
2153 insert_route_context_usages (l_date_earned_context_id, 1);
2154 insert_route_context_usages (l_assign_id_context_id, 2);
2155 --
2156 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2157 -- +
2158 -- SCL organization level route : SCL_ORG_FLEX_ROUTE +
2159 -- +
2160 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2161 --
2162 l_text := '/* route for SCL keyflex - organization level */
2163 hr_soft_coding_keyflex target
2164 , per_assignments_f ASSIGN
2165 , hr_organization_units ORG
2169 AND target.enabled_flag = ''Y''
2166 WHERE &B1 BETWEEN ASSIGN.effective_start_date
2167 AND ASSIGN.effective_end_date
2168 AND ASSIGN.assignment_id = &B2
2170 AND target.id_flex_num = &U1
2171 AND ORG.organization_id = ASSIGN.organization_id
2172 AND target.soft_coding_keyflex_id = ORG.soft_coding_keyflex_id';
2173 --
2174 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 52);
2175 insert into ff_routes
2176 (route_id,
2177 route_name,
2178 user_defined_flag,
2179 description,
2180 text,
2181 last_update_date,
2182 last_updated_by,
2183 last_update_login,
2184 created_by,
2185 creation_date)
2186 select ff_routes_s.nextval,
2187 'SCL_ORG_FLEX_ROUTE',
2188 'N',
2189 'route for SCL organization level Flexfield',
2190 l_text,
2191 sysdate,
2192 0,
2193 0,
2194 0,
2195 sysdate
2196 from dual;
2197 --
2198 insert_route_parameters ('ID flex number', 'N', 1);
2199 insert_route_context_usages (l_date_earned_context_id, 1);
2200 insert_route_context_usages (l_assign_id_context_id, 2);
2201 --
2202 --
2203 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2204 -- +
2205 -- Retroadjusted Run route : RETROADJ_RUN_BAL_ROUTE +
2206 -- +
2207 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2208 --
2209 l_text := '
2210 pay_balance_feeds_f FEED,
2211 pay_run_result_values TARGET,
2212 pay_run_results RR,
2213 pay_assignment_actions ASSACT,
2214 pay_assignment_actions BAL_ASSACT,
2215 pay_payroll_actions PACT,
2216 pay_element_entries_f PEE
2217 where FEED.balance_type_id = &U1
2218 + decode(PACT.payroll_action_id, null, 0,0)
2219 and BAL_ASSACT.assignment_action_id = &B1
2220 and FEED.input_value_id = TARGET.input_value_id
2221 and TARGET.run_result_id = RR.run_result_id
2222 and RR.status in (''P'', ''PA'')
2223 and (RR.source_id = PEE.element_entry_id
2224 and RR.source_type not in (''R'', ''V''))
2225 and PEE.assignment_id = BAL_ASSACT.assignment_id
2226 and PACT.payroll_action_id = ASSACT.payroll_action_id
2227 and PACT.effective_date between
2228 FEED.effective_start_date and FEED.effective_end_date
2229 and PACT.date_earned between
2230 PEE.effective_start_date and PEE.effective_end_date
2231 and RR.assignment_action_id = ASSACT.assignment_action_id
2235 --
2232 and ((ASSACT.assignment_action_id = &B1 and PEE.creator_type <> ''R'')
2233 or (PEE.creator_type = ''R'' and PEE.source_id = &B1
2234 and PEE.entry_type = ''E''))';
2236 hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 53);
2237 insert into ff_routes
2238 (route_id,
2239 route_name,
2240 user_defined_flag,
2241 description,
2242 text,
2243 last_update_date,
2244 last_updated_by,
2245 last_update_login,
2246 created_by,
2247 creation_date)
2248 select ff_routes_s.nextval,
2249 'RETROADJ_RUN_BAL_ROUTE',
2250 'N',
2251 'route for Retroadjusted Run To Date Balance',
2252 l_text,
2253 sysdate,
2254 0,
2255 0,
2256 0,
2257 sysdate
2258 from dual;
2259 --
2260 insert_route_parameters ('ID balance type', 'N', 1);
2261 insert_route_context_usages (l_assignment_action_id, 1);
2262 --
2263 --
2264 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2265 -- +
2266 -- Functions for the Entity Horizon +
2267 -- +
2268 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2269 --
2270 -- The following function is used to call the PL/SQL function from
2271 -- formula to retrieve data from the User Defined Tables.
2272 --
2273 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 100);
2274 select ff_functions_s.nextval
2275 into l_function_id
2276 from dual;
2277 --
2278 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 101);
2279 --
2280 -- insert the main formula:
2281 -- note: perform a normal insert (rather than using a select) to avoid
2282 -- oracle error ora-4091.
2283 --
2284 insert into ff_functions
2285 (function_id,
2286 business_group_id,
2287 legislation_code,
2288 class,
2289 name,
2290 alias_name,
2291 data_type,
2292 definition,
2293 last_update_date,
2294 last_updated_by,
2295 last_update_login,
2296 created_by,
2297 creation_date)
2298 values(l_function_id,
2299 null,
2300 null,
2301 'U',
2302 'GET_TABLE_VALUE',
2303 null,
2304 'T',
2305 'hruserdt.get_table_value',
2306 sysdate,
2307 0,
2308 0,
2309 0,
2310 sysdate);
2314 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 102);
2311 --
2312 -- insert the context usages (first listed parameters to the formula)
2313 --
2315 select context_id
2316 into l_business_group_id
2317 from ff_contexts
2318 where context_name = 'BUSINESS_GROUP_ID';
2319 --
2320 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 103);
2321 insert into ff_function_context_usages
2322 (function_id,
2323 sequence_number,
2324 context_id)
2325 values (l_function_id,
2326 1,
2327 l_business_group_id);
2328 --
2329 -- insert the formula parameters
2330 --
2331 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 104);
2332 insert into ff_function_parameters
2333 (function_id,
2334 sequence_number,
2335 class,
2336 continuing_parameter,
2337 data_type,
2338 name,
2339 optional)
2340 values (l_function_id,
2341 1,
2342 'I',
2343 'N',
2344 'T',
2345 'table_name',
2346 'N');
2347 --
2348 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 105);
2349 insert into ff_function_parameters
2350 (function_id,
2351 sequence_number,
2352 class,
2353 continuing_parameter,
2354 data_type,
2355 name,
2356 optional)
2357 values (l_function_id,
2358 2,
2359 'I',
2360 'N',
2361 'T',
2362 'column_name',
2363 'N');
2364 --
2365 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 106);
2366 insert into ff_function_parameters
2367 (function_id,
2368 sequence_number,
2369 class,
2370 continuing_parameter,
2371 data_type,
2372 name,
2373 optional)
2374 values (l_function_id,
2375 3,
2376 'I',
2377 'N',
2378 'T',
2379 'row_value',
2380 'N');
2381 --
2382 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 107);
2383 insert into ff_function_parameters
2387 continuing_parameter,
2384 (function_id,
2385 sequence_number,
2386 class,
2388 data_type,
2389 name,
2390 optional)
2391 values (l_function_id,
2392 4,
2393 'I',
2394 'N',
2395 'D',
2396 'effective_date',
2397 'Y');
2398 --
2399 hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 108);
2400 --
2401 END create_dbi_startup;
2402 end pay_dbi_startup_pkg;