1 package body pay_autogn as
2 /* $Header: pyautogn.pkb 115.2 1999/11/24 02:18:35 pkm ship $ */
3 procedure insert_bf_dimensions is
4 x number;
5 procedure my_dimension_insert ( p_route_id number,
6 p_payments_flag varchar2,
7 p_database_item_suffix varchar2,
8 p_dimension_name varchar2,
9 p_dimension_type varchar2,
10 p_description varchar2,
11 p_feed_checking_type varchar2,
12 p_feed_checking_code varchar2,
13 p_expiry_checking_level varchar2,
14 p_expiry_checking_code varchar2) is
15 begin
16 hr_utility.trace('p_dimension_name is ' || p_dimension_name);
17 x := pay_db_balances_pkg.create_balance_dimension
18 (p_legislation_code => 'BF',
19 p_route_id => p_route_id,
20 p_database_item_suffix => p_database_item_suffix,
21 p_dimension_name => p_dimension_name,
22 p_dimension_type => p_dimension_type,
23 p_description => p_description,
24 p_feed_checking_type => p_feed_checking_type,
25 p_feed_checking_code => p_feed_checking_code,
26 p_payments_flag => p_payments_flag,
27 p_expiry_checking_code => p_expiry_checking_code,
28 p_expiry_checking_level => p_expiry_checking_level);
29 end my_dimension_insert;
30 function do_child_inserts return number is
31 x number;
32 begin
33 --
34 -- insert row into ff_route_context_usages
35 --
36 hr_utility.set_location('pay_autogn.insert_bf_dimensions',20);
37 insert into ff_route_context_usages
38 (route_id,
39 context_id,
40 sequence_no)
41 select ff_routes_s.currval,
42 CON.context_id,
43 1
44 from ff_contexts CON
45 where CON.context_name = 'ASSIGNMENT_ACTION_ID';
46 --
47 -- insert row into ff_route_parameters
48 --
49 hr_utility.set_location('pay_autogn.insert_bf_dimensions',30);
50 insert into ff_route_parameters
51 (route_parameter_id,
52 route_id,
53 sequence_no,
54 parameter_name,
55 data_type)
56 values
57 (ff_route_parameters_s.nextval,
58 ff_routes_s.currval,
59 1,
60 'Balance Type ID',
61 'N');
62 hr_utility.set_location('pay_autogn.insert_bf_dimensions',40);
63 select ff_routes_s.currval into x from dual;
64 --
65 return x;
66 --
67 end do_child_inserts;
68 begin
69 -- Insert row in FF_ROUTES for this dimension
70 hr_utility.set_location('pay_autogn.insert_bf_dimensions',1);
71 insert into ff_routes
72 (route_id,
73 user_defined_flag,
74 route_name,
75 description,
76 text)
77 values
78 (ff_routes_s.nextval,
79 'N',
80 'BF Person-level Tax Year to Date Balance Dimension',
81 'Summed data for the PERSON-LEVEL BF TAX YEAR TO DATE balance dimension',
82 ' pay_balance_feeds_f FEED
83 ,pay_run_result_values TARGET
84 ,pay_run_results RR
85 ,pay_payroll_actions PACT
86 ,pay_assignment_actions ASSACT
87 ,pay_payroll_actions BACT
88 ,pay_assignment_actions BAL_ASSACT
89 ,per_assignments_f ASS
90 ,per_assignments_f START_ASS
91 where BAL_ASSACT.assignment_action_id = &B1
92 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
93 and FEED.balance_type_id = &U1
94 and FEED.input_value_id = TARGET.input_value_id
95 and TARGET.run_result_id = RR.run_result_id
96 and RR.assignment_action_id = ASSACT.assignment_action_id
97 and ASSACT.payroll_action_id = PACT.payroll_action_id
98 and PACT.effective_date between
99 FEED.effective_start_date and FEED.effective_end_date
100 and RR.status in (''P'',''PA'')
101 and PACT.effective_date >=
102 (select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
103 to_char( BACT.effective_date,''YYYY''))
104 + decode(sign( BACT.effective_date - to_date(''06-04-''
105 || to_char(BACT.effective_date,''YYYY''),''DD-MM-YYYY'')),
106 -1,-1,0)),''DD-MM-YYYY'')
107 from dual)
108 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
109 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
110 and ASS.period_of_service_id = START_ASS.period_of_service_id
111 and ASSACT.assignment_id = ASS.assignment_id
112 and BACT.effective_date between
113 ASS.effective_start_date and ASS.effective_end_date
114 and PACT.effective_date between
115 START_ASS.effective_start_date and START_ASS.effective_end_date');
116 --
117 -- now insert rows into ff_route_context_usages and ff_route_parameters
118 -- and load variable with the current route_id
119 --
120 x := do_child_inserts;
121 --
122 -- now insert row into pay_balance_dimensions
123 --
124 my_dimension_insert(x,
125 'N',
126 '_YTD',
127 'Person-level BF Tax Year to Date',
128 'P',
129 'Summed data for all a person''s assignments in the BF tax year',
130 null, -- feed checking type (always feed)
131 null, -- always feed
132 'P', -- expiry check at payroll action level
133 'pay_bf_expc.pytd_ec' -- expry checking procedure.
134 );
135 -- Insert row in FF_ROUTES for this dimension
136 hr_utility.set_location('pay_autogn.insert_bf_dimensions',2);
137 insert into ff_routes
138 (route_id,
139 user_defined_flag,
140 route_name,
141 description,
142 text)
143 values
144 (ff_routes_s.nextval,
145 'N',
146 'BF Assignment-level Tax Year to Date Balance Dimension',
147 'Summed data for the ASSIGNMENT-LEVEL BF TAX YEAR TO DATE balance dimension',
148 ' pay_balance_feeds_f FEED
149 ,pay_run_result_values TARGET
150 ,pay_run_results RR
151 ,pay_payroll_actions PACT
152 ,pay_assignment_actions ASSACT
153 ,pay_payroll_actions BACT
154 ,pay_assignment_actions BAL_ASSACT
155 where BAL_ASSACT.assignment_action_id = &B1
156 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
157 and FEED.balance_type_id = &U1
158 and FEED.input_value_id = TARGET.input_value_id
159 and TARGET.run_result_id = RR.run_result_id
160 and RR.assignment_action_id = ASSACT.assignment_action_id
161 and ASSACT.payroll_action_id = PACT.payroll_action_id
162 and PACT.effective_date between
163 FEED.effective_start_date and FEED.effective_end_date
164 and RR.status in (''P'',''PA'')
165 and PACT.effective_date >=
166 (select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
167 to_char( BACT.effective_date,''YYYY''))
168 + decode(sign( BACT.effective_date - to_date(''06-04-''
169 || to_char(BACT.effective_date,''YYYY''),''DD-MM-YYYY'')),
170 -1,-1,0)),''DD-MM-YYYY'')
171 from dual)
172 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
173 and ASSACT.assignment_id = BAL_ASSACT.assignment_id');
174 --
175 -- now insert rows into ff_route_context_usages and ff_route_parameters
176 -- and load variable with the current route_id
177 --
178 x := do_child_inserts;
179 --
180 -- now insert row into pay_balance_dimensions
181 --
182 my_dimension_insert(x,
183 'N',
184 '_AYTD',
185 'Assignment-level BF Tax Year to Date',
186 'A',
187 'Summed data for a single assignment in the BF tax year',
188 null, -- always feed (no checking type)
189 null, -- always feed (no code)
190 'P',
191 'pay_bf_expc.aytd_ec' -- checking code.
192 );
193 -- Insert row in FF_ROUTES for this dimension
194 hr_utility.set_location('pay_autogn.insert_bf_dimensions',3);
195 insert into ff_routes
196 (route_id,
197 user_defined_flag,
198 route_name,
199 description,
200 text)
201 values
202 (ff_routes_s.nextval,
203 'N',
204 'BF Person-level Period to Date Balance Dimension',
205 'Summed data for the PERSON-LEVEL PERIOD TO DATE balance dimension',
206 ' pay_balance_feeds_f FEED
207 ,pay_run_result_values TARGET
208 ,pay_run_results RR
209 ,pay_payroll_actions PACT
210 ,pay_assignment_actions ASSACT
211 ,pay_payroll_actions BACT
212 ,pay_assignment_actions BAL_ASSACT
213 ,per_assignments_f ASS
214 ,per_assignments_f START_ASS
215 where BAL_ASSACT.assignment_action_id = &B1
216 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
217 and FEED.balance_type_id = &U1
218 and FEED.input_value_id = TARGET.input_value_id
219 and TARGET.run_result_id = RR.run_result_id
220 and RR.assignment_action_id = ASSACT.assignment_action_id
221 and ASSACT.payroll_action_id = PACT.payroll_action_id
222 and PACT.effective_date between
223 FEED.effective_start_date and FEED.effective_end_date
224 and RR.status in (''P'',''PA'')
225 and PACT.effective_date >=
226 (select PTP.start_date from per_time_periods PTP
227 where BACT.effective_date
228 between PTP.start_date and PTP.end_date
229 and PTP.payroll_id = PACT.payroll_id
230 )
231 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
232 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
233 and ASS.period_of_service_id = START_ASS.period_of_service_id
234 and ASSACT.assignment_id = ASS.assignment_id
235 and BACT.effective_date between
236 ASS.effective_start_date and ASS.effective_end_date
237 and PACT.effective_date between
238 START_ASS.effective_start_date and START_ASS.effective_end_date');
239 --
240 -- now insert rows into ff_route_context_usages and ff_route_parameters
241 -- and load variable with the current route_id
242 --
243 x := do_child_inserts;
244 --
245 -- now insert row into pay_balance_dimensions
246 --
247 my_dimension_insert(x,
248 'N',
249 '_PTD',
250 'Person-level Period to Date',
251 'P',
252 'Summed data for all a person''s assignments in the current earnings period',
253 null, -- always feed
254 null, -- always feed
255 'P', -- expiry check at Payroll Action level
256 'pay_bf_expc.pptd_ec' -- expiry checking procedure.
257 );
258 -- Insert row in FF_ROUTES for this dimension
259 hr_utility.set_location('pay_autogn.insert_bf_dimensions',5);
260 insert into ff_routes
261 (route_id,
262 user_defined_flag,
263 route_name,
264 description,
265 text)
266 values
267 (ff_routes_s.nextval,
268 'N',
269 'BF Assignment-level Period to Date Balance Dimension',
270 'Summed data for the ASSIGNMENT-LEVEL PERIOD TO DATE balance dimension',
271 ' pay_balance_feeds_f FEED
272 ,pay_run_result_values TARGET
273 ,pay_run_results RR
274 ,pay_payroll_actions PACT
275 ,pay_assignment_actions ASSACT
276 ,pay_payroll_actions BACT
277 ,pay_assignment_actions BAL_ASSACT
278 where BAL_ASSACT.assignment_action_id = &B1
279 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
280 and FEED.balance_type_id = &U1
281 and FEED.input_value_id = TARGET.input_value_id
282 and TARGET.run_result_id = RR.run_result_id
283 and RR.assignment_action_id = ASSACT.assignment_action_id
284 and ASSACT.payroll_action_id = PACT.payroll_action_id
285 and PACT.effective_date between
286 FEED.effective_start_date and FEED.effective_end_date
287 and RR.status in (''P'',''PA'')
288 and PACT.effective_date >=
289 (select PTP.start_date from per_time_periods PTP
290 where BACT.effective_date
291 between PTP.start_date and PTP.end_date
292 and PTP.payroll_id = PACT.payroll_id
293 )
294 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
295 and ASSACT.assignment_id = BAL_ASSACT.assignment_id');
296 --
297 -- now insert rows into ff_route_context_usages and ff_route_parameters
298 -- and load variable with the current route_id
299 --
300 x := do_child_inserts;
301 --
302 -- now insert row into pay_balance_dimensions
303 --
304 my_dimension_insert(x,
305 'N',
306 '_APTD',
307 'Assignment-level Period to Date',
308 'A',
309 'Summed data for a single assignment in the current earnings period',
310 null, -- always feed
311 null, -- always feed
312 'P', -- expiry check at Payroll Action level
313 'pay_bf_expc.aptd_ec' -- expiry checking procedure.
314 );
315 -- Insert row in FF_ROUTES for this dimension
316 hr_utility.set_location('pay_autogn.insert_bf_dimensions',4);
317 insert into ff_routes
318 (route_id,
319 user_defined_flag,
320 route_name,
321 description,
322 text)
323 values
324 (ff_routes_s.nextval,
325 'N',
326 'BF Person-level Period to Date Balance Dimension (test)',
327 'Summed data for the PERSON-LEVEL PERIOD TO DATE balance dimension (test)',
328 ' pay_balance_feeds_f FEED
329 ,pay_run_result_values TARGET
330 ,pay_run_results RR
331 ,pay_payroll_actions PACT
332 ,pay_assignment_actions ASSACT
333 ,pay_payroll_actions BACT
334 ,pay_assignment_actions BAL_ASSACT
335 ,per_assignments_f ASS
336 ,per_assignments_f START_ASS
337 where BAL_ASSACT.assignment_action_id = &B1
338 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
339 and FEED.balance_type_id = &U1
340 and FEED.input_value_id = TARGET.input_value_id
341 and TARGET.run_result_id = RR.run_result_id
342 and RR.assignment_action_id = ASSACT.assignment_action_id
343 and ASSACT.payroll_action_id = PACT.payroll_action_id
344 and PACT.effective_date between
345 FEED.effective_start_date and FEED.effective_end_date
346 and RR.status in (''P'',''PA'')
347 and PACT.effective_date >=
348 (select PTP.start_date from per_time_periods PTP
349 where BACT.effective_date
350 between PTP.start_date and PTP.end_date
351 and PTP.payroll_id = PACT.payroll_id
352 )
353 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
354 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
355 and ASS.period_of_service_id = START_ASS.period_of_service_id
356 and ASSACT.assignment_id = ASS.assignment_id
357 and BACT.effective_date between
358 ASS.effective_start_date and ASS.effective_end_date
359 and PACT.effective_date between
360 START_ASS.effective_start_date and START_ASS.effective_end_date');
361 --
362 -- now insert rows into ff_route_context_usages and ff_route_parameters
363 -- and load variable with the current route_id
364 --
365 x := do_child_inserts;
366 --
367 -- now insert row into pay_balance_dimensions
368 --
369 my_dimension_insert(x,
370 'N',
371 '_TSTPTD',
372 'Person-level Period to Date (test)',
373 'P',
374 'Summed data for all a person''s assignments in the current earnings period',
375 null, -- always feed
376 null, -- always feed
377 'A', -- expiry check at Assignment Action level
378 'pay_bf_expc.pptd_alc_ec'
379 );
380 -- Insert row in FF_ROUTES for this dimension
381 hr_utility.set_location('pay_autogn.insert_bf_dimensions',6);
382 insert into ff_routes
383 (route_id,
384 user_defined_flag,
385 route_name,
386 description,
387 text)
388 values
389 (ff_routes_s.nextval,
390 'N',
391 'BF Person-level User Cleared Balance Dimension',
395 ,pay_run_results RR
392 'Summed data for the PERSON-LEVEL USER CLEARED balance dimension',
393 ' pay_balance_feeds_f FEED
394 ,pay_run_result_values TARGET
396 ,pay_payroll_actions PACT
397 ,pay_assignment_actions ASSACT
398 ,pay_payroll_actions BACT
399 ,pay_assignment_actions BAL_ASSACT
400 ,per_assignments_f ASS
401 ,per_assignments_f START_ASS
402 where BAL_ASSACT.assignment_action_id = &B1
403 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
404 and FEED.balance_type_id = &U1
405 and FEED.input_value_id = TARGET.input_value_id
406 and TARGET.run_result_id = RR.run_result_id
407 and RR.assignment_action_id = ASSACT.assignment_action_id
408 and ASSACT.payroll_action_id = PACT.payroll_action_id
409 and PACT.effective_date between
410 FEED.effective_start_date and FEED.effective_end_date
411 and RR.status in (''P'',''PA'')
412 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
413 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
414 and ASS.period_of_service_id = START_ASS.period_of_service_id
415 and ASSACT.assignment_id = ASS.assignment_id
416 and BACT.effective_date between
417 ASS.effective_start_date and ASS.effective_end_date
418 and PACT.effective_date between
419 START_ASS.effective_start_date and START_ASS.effective_end_date');
420 --
421 -- now insert rows into ff_route_context_usages and ff_route_parameters
422 -- and load variable with the current route_id
423 --
424 x := do_child_inserts;
425 --
426 -- now insert row into pay_balance_dimensions
427 --
428 my_dimension_insert(x,
429 'N',
430 '_USER',
431 'Person-level User Cleared',
432 'P',
433 'Summed data for all a person''s assignments since last cleared down',
434 null, -- always feed
435 null, -- always feed
436 'P', -- expiry check at payroll action level.
437 'pay_bf_expc.never_expires'
438 );
439 -- Insert row in FF_ROUTES for this dimension
440 hr_utility.set_location('pay_autogn.insert_bf_dimensions',7);
441 insert into ff_routes
442 (route_id,
443 user_defined_flag,
444 route_name,
445 description,
446 text)
447 values
448 (ff_routes_s.nextval,
449 'N',
450 'BF Assignment-level User Cleared Balance Dimension',
451 'Summed data for the ASSIGNMENT-LEVEL USER CLEARED balance dimension',
452 ' pay_balance_feeds_f FEED
453 ,pay_run_result_values TARGET
454 ,pay_run_results RR
455 ,pay_payroll_actions PACT
456 ,pay_assignment_actions ASSACT
457 ,pay_payroll_actions BACT
458 ,pay_assignment_actions BAL_ASSACT
459 where BAL_ASSACT.assignment_action_id = &B1
460 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
461 and FEED.balance_type_id = &U1
462 and FEED.input_value_id = TARGET.input_value_id
463 and TARGET.run_result_id = RR.run_result_id
464 and RR.assignment_action_id = ASSACT.assignment_action_id
465 and ASSACT.payroll_action_id = PACT.payroll_action_id
466 and PACT.effective_date between
467 FEED.effective_start_date and FEED.effective_end_date
468 and RR.status in (''P'',''PA'')
469 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
470 and ASSACT.assignment_id = BAL_ASSACT.assignment_id');
471 --
472 -- now insert rows into ff_route_context_usages and ff_route_parameters
473 -- and load variable with the current route_id
474 --
475 x := do_child_inserts;
476 --
477 -- now insert row into pay_balance_dimensions
478 --
479 my_dimension_insert(x,
480 'N',
481 '_AUSER',
482 'Assignment-level User Cleared',
483 'P',
484 'Summed data for a single assignment since last cleared down',
485 null, -- always feed
486 null, -- always feed
487 'P', -- expiry check at Payroll Action level
488 'pay_bf_expc.never_expires'
489 );
490 -- Insert row in FF_ROUTES for this dimension
491 hr_utility.set_location('pay_autogn.insert_bf_dimensions',8);
492 insert into ff_routes
493 (route_id,
494 user_defined_flag,
495 route_name,
496 description,
497 text)
498 values
499 (ff_routes_s.nextval,
500 'N',
501 'BF Assignment-level User Cleared Balance Dimension (not fed)',
502 'Summed data for the ASSIGNMENT-LEVEL USER balance dimension (not fed)',
503 ' pay_balance_feeds_f FEED
504 ,pay_run_result_values TARGET
505 ,pay_run_results RR
506 ,pay_payroll_actions PACT
507 ,pay_assignment_actions ASSACT
508 ,pay_payroll_actions BACT
509 ,pay_assignment_actions BAL_ASSACT
510 where BAL_ASSACT.assignment_action_id = &B1
511 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
512 and FEED.balance_type_id = &U1
513 and FEED.input_value_id = TARGET.input_value_id
514 and TARGET.run_result_id = RR.run_result_id
515 and RR.assignment_action_id = ASSACT.assignment_action_id
516 and ASSACT.payroll_action_id = PACT.payroll_action_id
517 and PACT.effective_date between
518 FEED.effective_start_date and FEED.effective_end_date
519 and RR.status in (''P'',''PA'')
520 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
521 and ASSACT.assignment_id = BAL_ASSACT.assignment_id');
522 --
523 -- now insert rows into ff_route_context_usages and ff_route_parameters
524 -- and load variable with the current route_id
525 --
526 x := do_child_inserts;
527 --
528 -- now insert row into pay_balance_dimensions
529 --
530 my_dimension_insert(x,
531 'N',
535 'Summed data for a single assignment since last cleared down (not fed)',
532 '_ANOTFEDUSER',
533 'Assignment-level User Cleared (not fed)',
534 'N',
536 null, -- never fed or stored
537 null, -- never fed or stored
538 null, -- never fed or stored
539 null -- never fed or stored
540 );
541 -- Insert row in FF_ROUTES for this dimension
542 hr_utility.set_location('pay_autogn.insert_bf_dimensions',9);
543 insert into ff_routes
544 (route_id,
545 user_defined_flag,
546 route_name,
547 description,
548 text)
549 values
550 (ff_routes_s.nextval,
551 'N',
552 'BF Assignment-level Current Run Balance Dimension',
553 'Summed data for the ASSIGNMENT-LEVEL CURRENT RUN balance dimension',
554 ' pay_balance_feeds_f FEED
555 ,pay_run_result_values TARGET
556 ,pay_run_results RR
557 ,pay_payroll_actions PACT
558 ,pay_assignment_actions ASSACT
559 where ASSACT.assignment_action_id = &B1
560 and FEED.balance_type_id = &U1
561 and FEED.input_value_id = TARGET.input_value_id
562 and TARGET.run_result_id = RR.run_result_id
563 and RR.assignment_action_id = ASSACT.assignment_action_id
564 and ASSACT.payroll_action_id = PACT.payroll_action_id
565 and PACT.effective_date between
566 FEED.effective_start_date and FEED.effective_end_date
567 and RR.status in (''P'',''PA'')');
568 --
569 -- now insert rows into ff_route_context_usages and ff_route_parameters
570 -- and load variable with the current route_id
571 --
572 x := do_child_inserts;
573 --
574 -- now insert row into pay_balance_dimensions
575 --
576 my_dimension_insert(x,
577 'N',
578 '_RUN',
579 'Assignment-level Current Run',
580 'F',
581 'Summed data for an assignment within a run',
582 null, -- always feed
583 null, -- always feed
584 null, -- never stored, no expiry details needed
585 null -- never stored, no expiry details needed
586 );
587 -- Insert row in FF_ROUTES for this dimension
588 hr_utility.set_location('pay_autogn.insert_bf_dimensions',10);
589 insert into ff_routes
590 (route_id,
591 user_defined_flag,
592 route_name,
593 description,
594 text)
595 values
596 (ff_routes_s.nextval,
597 'N',
598 'BF Person-level Contracted In YTD Balance Dimension',
599 'Summed data for the PERSON-LEVEL CONTRACTED IN YTD balance dimension',
600 ' pay_balance_feeds_f FEED
601 ,pay_run_result_values TARGET
602 ,pay_run_results RR
603 ,pay_payroll_actions PACT
604 ,pay_assignment_actions ASSACT
605 ,pay_payroll_actions BACT
606 ,pay_assignment_actions BAL_ASSACT
607 ,per_assignments_f ASS
608 ,per_assignments_f START_ASS
609 where BAL_ASSACT.assignment_action_id = &B1
610 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
611 and FEED.balance_type_id = &U1
612 and FEED.input_value_id = TARGET.input_value_id
613 and TARGET.run_result_id = RR.run_result_id
614 and RR.assignment_action_id = ASSACT.assignment_action_id
615 and ASSACT.payroll_action_id = PACT.payroll_action_id
616 and PACT.effective_date between
617 FEED.effective_start_date and FEED.effective_end_date
618 and RR.status in (''P'',''PA'')
619 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
620 and ASS.period_of_service_id = START_ASS.period_of_service_id
621 and ASSACT.assignment_id = ASS.assignment_id
622 and nvl(ASS.ass_attribute1, ''CO'') = ''CI''
623 and BACT.effective_date between
624 ASS.effective_start_date and ASS.effective_end_date
625 and PACT.effective_date between
626 START_ASS.effective_start_date and START_ASS.effective_end_date');
627 --
628 -- now insert rows into ff_route_context_usages and ff_route_parameters
629 -- and load variable with the current route_id
630 --
631 x := do_child_inserts;
632 --
633 -- now insert row into pay_balance_dimensions
634 --
635 my_dimension_insert(x,
636 'N',
637 '_CI_YTD',
638 'Person-level Contracted In YTD',
639 'P',
640 'Summed data for all a person''s contracted-in assignments (in BF tax year)',
641 'P', -- we must always feed check.
642 'pay_bf_expc.pcon_fc', -- feed checking procedure.
643 'P', -- expiry check at payroll action level.
644 'pay_bf_expc.pcon_ec' -- expiry checking procedure.
645 );
646 -- Insert row in FF_ROUTES for this dimension
647 hr_utility.set_location('pay_autogn.insert_bf_dimensions',11);
648 insert into ff_routes
649 (route_id,
650 user_defined_flag,
651 route_name,
652 description,
653 text)
654 values
655 (ff_routes_s.nextval,
656 'N',
657 'BF Payments Balance Dimension',
658 'Summed data for the PAYMENTS balance dimension',
659 ' pay_balance_feeds_f FEED
660 ,pay_run_result_values TARGET
661 ,pay_run_results RR
662 ,pay_payroll_actions PACT
663 ,pay_assignment_actions ASSACT
664 ,pay_action_interlocks INTLK
665 ,pay_payroll_actions BACT
666 ,pay_assignment_actions BAL_ASSACT
667 where BAL_ASSACT.assignment_action_id = &B1
668 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
669 and FEED.balance_type_id = &U1
670 and FEED.input_value_id = TARGET.input_value_id
671 and TARGET.run_result_id = RR.run_result_id
672 and RR.assignment_action_id = ASSACT.assignment_action_id
673 and ASSACT.payroll_action_id = PACT.payroll_action_id
674 and PACT.effective_date between
678 and INTLK.locking_action_id = BAL_ASSACT.assignment_action_id
675 FEED.effective_start_date and FEED.effective_end_date
676 and RR.status in (''P'',''PA'')
677 and ASSACT.assignment_action_id = INTLK.locked_action_id
679 and ASSACT.assignment_id = BAL_ASSACT.assignment_id');
680 --
681 -- now insert rows into ff_route_context_usages and ff_route_parameters
682 -- and load variable with the current route_id
683 --
684 x := do_child_inserts;
685 --
686 -- now insert row into pay_balance_dimensions
687 --
688 my_dimension_insert(x,
689 'Y',
690 '_PAYMENTS',
691 'Payments',
692 'N',
693 'Summed data for all an assignments runs being handled within a payment run',
694 null, -- never held or stored
695 null, -- never held or stored
696 null, -- never held or stored
697 null -- never held or stored
698 );
699 end insert_bf_dimensions;
700 end pay_autogn;
[Home] [Help]