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