1 package body pay_magtape_generic
2 /* $Header: pymaggen.pkb 120.7.12010000.1 2008/07/27 23:08:56 appldev ship $ */
3 as
4 g_debug boolean; /* NOTE: CANNOT be initialised here !! */
5 --
6 type cursname_array is table of pay_magnetic_blocks.cursor_name%TYPE
7 index by binary_integer;
8 type num_array is table of number
9 index by binary_integer;
10 type bool_array is table of boolean
11 index by binary_integer;
12 -- Cursor Level arrays
13 curs cursname_array;
14 column_num num_array;
15 block_id num_array;
16 formulas num_array;
17 first_run_flag num_array;
18 row_counts num_array;
19 intermediate_run bool_array;
20 running_intermediate bool_array;
21 level_no number;
22 report_id pay_magnetic_blocks.report_format%TYPE;
23 int_prm_names pay_mag_tape.host_array;
24 int_prm_values pay_mag_tape.host_array;
25 --
26 -- Formula level details
27 formula_next_block pay_magnetic_records.next_block_id%TYPE;
28 formula_id pay_magnetic_records.formula_id%TYPE;
29 formula_frequency pay_magnetic_records.frequency%TYPE;
30 formula_overflow pay_magnetic_records.overflow_mode%TYPE;
31 formula_inter_repeat pay_magnetic_records.last_run_executed_mode%TYPE;
32 formula_action_level pay_magnetic_records.action_level%TYPE;
33 formula_block_label pay_magnetic_records.block_label%TYPE;
34 formula_block_row_label pay_magnetic_records.block_row_label%TYPE;
35 xml_proc_name pay_magnetic_records.xml_proc_name%TYPE;
36 rec_sequence pay_magnetic_records.sequence%TYPE;
37 return_arr_offset number;
38 --
39 --
40
41 procedure clear_cache
42 is
43 begin
44 level_no := 0;
45 formula_next_block := NULL;
46 use_action_block := 'N';
47 process_action_rec := 'N';
48 end clear_cache;
49 --
50
51 -----------------------------------------------------------------------------
52 -- Name
53 -- date_earned
54 -- Purpose
55 -- Returns the least of the maximum date of an assignment and a date.
56 -- Arguments
57 -- Notes
58 -- Used within cursor definitions ie. can only be used from 7.1 of RDBMS
59 -- onwards.
60 -----------------------------------------------------------------------------
61 --
62 function date_earned
63 (
64 p_report_date date,
65 p_assignment_id number
66 ) return date is
67 v_max_assignment_date date;
68 v_report_date date := p_report_date;
69 begin
70 select max(SS.effective_end_date)
71 into v_max_assignment_date
72 from per_all_assignments_f SS
73 where SS.assignment_id = p_assignment_id;
74 if v_max_assignment_date < v_report_date then
75 return (v_max_assignment_date);
76 else
77 return (v_report_date);
78 end if;
79 end date_earned;
80 --
81
82 procedure set_paramter_value(asg_act_id number,
83 prm_name varchar,
84 prm_value varchar)
85 is
86
87 begin
88 INSERT INTO FF_ARCHIVE_ITEMS
89 (ARCHIVE_ITEM_ID,
90 USER_ENTITY_ID,
91 CONTEXT1,
92 VALUE ,
93 ARCHIVE_TYPE,
94 NAME
95 )
96 VALUES
97 (ff_archive_items_s.nextval,
98 -1,
99 asg_act_id,
100 prm_value,
101 'AAP',
102 prm_name
103 );
104
105 end;
106
107
108 /* Function - get_parameter_value
109 Action - This returns the value of a named parameter from the
110 pay_mag_tape paramater tables.
111 */
112 function get_parameter_value(prm_name varchar2)
113 return varchar2 is
114 cnt number;
115 cnt2 number;
116 val varchar2(256);
117 begin
118 cnt := 1;
119 while cnt <= int_prm_values(1) loop
120 if int_prm_names(cnt) = prm_name then
121 val := int_prm_values(cnt);
122 end if;
123 cnt := cnt + 1;
124 end loop;
125 return val;
126 exception
127 when NO_DATA_FOUND then
128 return NULL;
129 end;
130 --
131 /* Function - get_cursor_return
132 Action - This returns a value retrieved by a named cursor and position
133 in that the column was selected.
134 */
135 function get_cursor_return(curs_name pay_magnetic_blocks.cursor_name%TYPE,
136 pos number) return varchar is
137 column_no number;
138 level_cnt number;
139 cnt number;
140 begin
141 column_no := 0;
142 level_cnt := 1;
143 -- Find the cursor required
144 while curs(level_cnt) <> curs_name and level_cnt <> level_no loop
145 level_cnt := level_cnt + 1;
146 end loop;
147 if curs(level_cnt) = curs_name then
148 cnt := 1;
149 while cnt < level_cnt loop -- Find the position of column in
150 -- the returns table.
151 column_no := column_no + column_num(cnt);
152 cnt := cnt + 1;
153 end loop;
154 column_no := column_no + pos;
155 return ret_vals(column_no);
156 else
157 return NULL;
158 end if;
159 end;
160 --
161 /* Function curs_is_open
162 Action:- This function is passed a cursor name and test to see if the
163 cursor is open.
164 */
165 function curs_is_open(cur_name pay_magnetic_blocks.cursor_name%TYPE)
166 return boolean is
167 sql_curs number;
168 rows_processed number;
169 statem varchar2(256);
170 begin
171 if g_debug then
172 hr_utility.trace('Entering pay_magtape_generic.curs_is_open');
173 end if;
174 statem := 'BEGIN IF '||cur_name||'%ISOPEN THEN '||
175 'pay_magtape_generic.boolean_flag := TRUE; '||
176 'ELSE pay_magtape_generic.boolean_flag := FALSE;'||
177 ' END IF; END;';
178 sql_curs := dbms_sql.open_cursor;
179 dbms_sql.parse(sql_curs,
180 statem,
181 dbms_sql.v7);
182 rows_processed := dbms_sql.execute(sql_curs);
183 dbms_sql.close_cursor(sql_curs);
184 if g_debug then
185 hr_utility.trace('Exiting pay_magtape_generic.curs_is_open');
186 end if;
187 return boolean_flag;
188 end curs_is_open;
189
190 /* Procedure - curs_close
191 Actions - This procedure close an already open cursor
192 */
193 procedure curs_close(cur_name pay_magnetic_blocks.cursor_name%TYPE) is
194 sql_curs number;
195 rows_processed number;
196 statem varchar2(256);
197 begin
198 if g_debug then
199 hr_utility.trace('Entering pay_magtape_generic.curs_close');
200 end if;
201 statem := 'BEGIN CLOSE '||cur_name||'; '||'end;';
202 sql_curs := dbms_sql.open_cursor;
203 dbms_sql.parse(sql_curs,
204 statem,
205 dbms_sql.v7);
206 rows_processed := dbms_sql.execute(sql_curs);
207 dbms_sql.close_cursor(sql_curs);
208 if g_debug then
209 hr_utility.trace('Exiting pay_magtape_generic.curs_close');
210 end if;
211 end curs_close;
212
213 /* Procedure - new_formula
214 Action - This procedure sets up the context and the parameters table
215 for the core C program from values setup in the controlling
216 tables and cursors.
217 */
218 PROCEDURE new_formula
219 IS
220 found boolean;
221 cnt number;
222 no_rows number;
223 temp_formula_block_label pay_magnetic_records.block_label%TYPE;
224 temp_formula_block_row_label pay_magnetic_records.block_row_label%TYPE;
225 i number;
226 prm_cnt number;
227
228 /* Function tab_is_open
229 Action:- Test a number in a table to see if it relates to an open
230 cursor and returns TRUE is the cursor is open False
231 otherwise.
232 */
233 function tab_is_open(sql_cur num_array, sql_pos number) return boolean is
234 res boolean;
235 begin
236 if g_debug then
237 hr_utility.trace('Entering pay_magtape_generic.tab_is_open' || sql_cur(sql_pos));
238 end if;
239 res := dbms_sql.is_open(sql_cur(sql_pos));
240 if g_debug then
241 hr_utility.trace('Exiting pay_magtape_generic.tab_is_open');
242 end if;
243 return res;
244 exception
245 when NO_DATA_FOUND then
246 return FALSE;
247 end;
248 --
249 /* Function first_run
250 Action:- THis function is passed a numeric value relating to a position
251 in the first_run_flag table and returns False if the value is
252 0 (ie. it is not the first run of this block) otherwise TRUE.
253 */
254 function first_run(pos number) return boolean is
255 res boolean;
256 begin
257 if g_debug then
258 hr_utility.trace('Entering pay_magtape_generic.first_run');
259 end if;
260 if first_run_flag(pos) = 1 then
261 first_run_flag(pos) := 0;
262 if g_debug then
263 hr_utility.trace('Exiting pay_magtape_generic.first_run');
264 end if;
265 return FALSE;
266 else
267 first_run_flag(pos) := 1;
268 if g_debug then
269 hr_utility.trace('Exiting pay_magtape_generic.first_run');
270 end if;
271 return TRUE;
272 end if;
273 exception
274 when NO_DATA_FOUND then
275 first_run_flag(pos) := 1;
276 if g_debug then
277 hr_utility.trace('Exiting pay_magtape_generic.first_run');
278 end if;
279 return TRUE;
280 end;
281 --
282 --
283 /* Function curs_no_data
284 Action:- This function is passed a cursor name and test to see if data
285 was retrieved on the last fetch.
286 */
287 function curs_no_data(cur_name pay_magnetic_blocks.cursor_name%TYPE)
288 return boolean is
289 sql_curs number;
290 rows_processed number;
291 statem varchar2(256);
292 begin
293 if g_debug then
294 hr_utility.trace('Entering pay_magtape_generic.curs_no_data');
295 end if;
296 statem := 'BEGIN IF '||cur_name||'%FOUND THEN '||
297 'pay_magtape_generic.boolean_flag := FALSE; '||
298 'ELSE pay_magtape_generic.boolean_flag := TRUE;'||
299 ' END IF; END;';
300 sql_curs := dbms_sql.open_cursor;
301 dbms_sql.parse(sql_curs,
302 statem,
303 dbms_sql.v7);
304 rows_processed := dbms_sql.execute(sql_curs);
305 dbms_sql.close_cursor(sql_curs);
306 if g_debug then
307 hr_utility.trace('Exiting pay_magtape_generic.curs_no_data');
308 end if;
309 return boolean_flag;
310 end curs_no_data;
311 --
312 /* Procedure - curs_open
313 Action - This opens a specified cursor.
314 */
315 procedure curs_open(cur_name pay_magnetic_blocks.cursor_name%TYPE) is
316 sql_curs number;
317 rows_processed number;
318 statem varchar2(256);
319 begin
320 if g_debug then
321 hr_utility.trace('Entering pay_magtape_generic.curs_open');
322 end if;
323 statem := 'BEGIN OPEN '||cur_name||'; '||'end;';
324 sql_curs := dbms_sql.open_cursor;
325 dbms_sql.parse(sql_curs,
326 statem,
327 dbms_sql.v7);
328 rows_processed := dbms_sql.execute(sql_curs);
329 dbms_sql.close_cursor(sql_curs);
330 if g_debug then
331 hr_utility.trace('Exiting pay_magtape_generic.curs_open');
332 end if;
333 end curs_open;
334 --
335 /* Procedure - curs_fetch
336 Action - This procedure executes a fetch into the retrieval table,
337 given the cursor name and the number of vales being selected.
338 */
339 procedure curs_fetch(cur_name pay_magnetic_blocks.cursor_name%TYPE,
340 return_no pay_magnetic_blocks.no_column_returned%TYPE)
341 is
342 sql_curs number;
343 rows_processed number;
344 statem varchar2(6000);
345 cnt number;
346 first boolean;
347 arr_num number;
348 pkg_name varchar2(50);
349 begin
350 if g_debug then
351 hr_utility.trace('Entering pay_magtape_generic.curs_fetch');
352 end if;
353 cnt := 1;
354 --
355 -- Workaround for bug #297130
356 --
357 pkg_name := substr(cur_name, 1, instr(cur_name, '.'));
358 statem := 'BEGIN '||pkg_name||'level_cnt := '||pkg_name||
359 'level_cnt; FETCH '||cur_name||' INTO ';
360 /*
361 statem := 'BEGIN FETCH '||cur_name||' INTO ';
362 */
363 first := TRUE;
364 while cnt <= return_no loop -- loop for the number of columns in
365 if first then -- the select statement
366 first := FALSE;
367 else
368 statem := statem||',';
369 end if;
370 arr_num := return_arr_offset + cnt; -- Add the off set so that
371 -- this cursor retrieves into
372 -- its own allocated area.
373 --
374 statem := statem||' pay_magtape_generic.ret_vals('||arr_num||')';
375 cnt := cnt + 1;
376 end loop;
377 statem := statem||'; END;';
378 hr_utility.trace(statem);
379 sql_curs := dbms_sql.open_cursor;
380 dbms_sql.parse(sql_curs,
381 statem,
382 dbms_sql.v7);
383 rows_processed := dbms_sql.execute(sql_curs);
384 dbms_sql.close_cursor(sql_curs);
385 if g_debug then
386 hr_utility.trace('Exiting pay_magtape_generic.curs_fetch');
387 end if;
388 end curs_fetch;
389 --
390 --
391 /* Function - is_intermediate_required
392 Action - This returns true if any rows of the formula table for a
393 particular block, may require to be run after the last row
394 of the cursor for that block is retrieved.
395 */
396 function is_intermediate_required(level_no number) return boolean is
397 dummy char(1);
398 begin
399 if g_debug then
400 hr_utility.trace('Entering pay_magtape_generic.is_intermediate_required');
401 end if;
402 select 'M'
403 into dummy
404 from pay_magnetic_records pmr
405 where pmr.magnetic_block_id = block_id(level_no)
406 and pmr.last_run_executed_mode in ('A', 'R', 'F');
407 --
408 return TRUE;
409 if g_debug then
410 hr_utility.trace('Exiting pay_magtape_generic.is_intermediate_required');
414 if g_debug then
411 end if;
412 exception
413 when NO_DATA_FOUND then
415 hr_utility.trace('Exiting pay_magtape_generic.is_intermediate_required');
416 end if;
417 return FALSE;
418 when TOO_MANY_ROWS then
419 if g_debug then
420 hr_utility.trace('Exiting pay_magtape_generic.is_intermediate_required');
421 end if;
422 return TRUE;
423 end;
424 --
425 /* Function - open_formula
426 Action - This opens a cursor for the formula for a particular cursor
427 and returns the new cursor id
428 */
429 function open_formula(block pay_magnetic_blocks.magnetic_block_id%TYPE)
430 return number is
431 sql_cur number;
432 ignore number;
433 statem varchar2(256);
434 begin
435 if g_debug then
436 hr_utility.trace('Entering pay_magtape_generic.open_formula');
437 end if;
438 sql_cur := dbms_sql.open_cursor;
439 statem := 'select formula_id, next_block_id,'||
440 ' frequency, overflow_mode, '||
441 'last_run_executed_mode, action_level ,'||
442 'block_label,block_row_label,xml_proc_name,sequence '||
443 ' from pay_magnetic_records '||
444 'where magnetic_block_id = '||block||
445 ' order by sequence';
446 dbms_sql.parse(sql_cur,
447 statem,
448 dbms_sql.v7);
449 dbms_sql.define_column(sql_cur, 1, formula_id);
450 dbms_sql.define_column(sql_cur, 2, formula_next_block);
451 dbms_sql.define_column(sql_cur, 3, formula_frequency);
452 dbms_sql.define_column(sql_cur, 4, formula_overflow, 1);
453 dbms_sql.define_column(sql_cur, 5, formula_inter_repeat, 1);
454 dbms_sql.define_column(sql_cur, 6, formula_action_level, 1);
455 dbms_sql.define_column(sql_cur, 7, formula_block_label,30);
456 dbms_sql.define_column(sql_cur, 8, formula_block_row_label,30);
457 dbms_sql.define_column(sql_cur, 9, xml_proc_name,256);
458 dbms_sql.define_column(sql_cur, 10, rec_sequence);
459 ignore := dbms_sql.execute(sql_cur);
460 if g_debug then
461 hr_utility.trace('Exiting pay_magtape_generic.open_formula');
462 end if;
463 return sql_cur;
464 end open_formula;
465 --
466
467 /* Function - intermediate_needed
468 Action - This function determines if an extra run is required after the
469 last row of the cursor is retrieved.
470 */
471 function intermediate_needed return boolean is
472 found boolean;
473 begin
474 if g_debug then
475 hr_utility.trace('Entering pay_magtape_generic.intermediate_needed');
476 end if;
477 found := FALSE;
478 if formula_inter_repeat = 'A' then
479 found := TRUE;
480 else
481 if formula_inter_repeat = 'R' then
482 if intermediate_run(level_no) then
483 found := TRUE;
484 end if;
485 else
486 if not intermediate_run(level_no) then
487 found := TRUE;
488 end if;
489 end if;
490 end if;
491 if g_debug then
492 hr_utility.trace('Exiting pay_magtape_generic.intermediate_needed');
493 end if;
494 return found;
495 end;
496 --
497 /* Function - open_inter_formula
498 Action - This function does the same as open_formula except it select
499 statement only selects rows that have their last_run_execute
500 flag set to R, A or F (ie if a run may be required).
501 */
502 function open_inter_formula(block
503 pay_magnetic_blocks.magnetic_block_id%TYPE) return number is
504 sql_cur number;
505 ignore number;
506 statem varchar2(512);
507 begin
508 if g_debug then
509 hr_utility.trace('Entering pay_magtape_generic.intermediate_needed');
510 end if;
511 sql_cur := dbms_sql.open_cursor;
512 statem := 'select formula_id,next_block_id,'||
513 'frequency,overflow_mode,'||
514 'last_run_executed_mode,action_level,'||
515 'block_label,block_row_label,xml_proc_name,sequence '||
516 'from pay_magnetic_records '||
517 'where magnetic_block_id='||block||
518 ' and last_run_executed_mode in (''R'',''A'',''F'')'||
519 ' order by sequence';
520 dbms_sql.parse(sql_cur,
521 statem,
522 dbms_sql.v7);
523 dbms_sql.define_column(sql_cur, 1, formula_id);
524 dbms_sql.define_column(sql_cur, 2, formula_next_block);
525 dbms_sql.define_column(sql_cur, 3, formula_frequency);
526 dbms_sql.define_column(sql_cur, 4, formula_overflow, 1);
527 dbms_sql.define_column(sql_cur, 5, formula_inter_repeat, 1);
528 dbms_sql.define_column(sql_cur, 6, formula_action_level, 1);
529 dbms_sql.define_column(sql_cur, 7, formula_block_label, 30);
530 dbms_sql.define_column(sql_cur, 8, formula_block_row_label, 30);
531 dbms_sql.define_column(sql_cur, 9, xml_proc_name, 256);
532 dbms_sql.define_column(sql_cur, 10, rec_sequence);
533 ignore := dbms_sql.execute(sql_cur);
537 return sql_cur;
534 if g_debug then
535 hr_utility.trace('Exiting pay_magtape_generic.intermediate_needed');
536 end if;
538 end open_inter_formula;
539 --
540 /* Function - run_overflow
541 Action - This function searches the parameter table for the parameter
542 TRANSFER_RUN_OVERFLOW, if it is found and its value is set to
543 Y then the boolean value true is returned otherwise false is
544 returned.
545 */
546 function run_overflow return boolean is
547 cnt number;
548 begin
549 if g_debug then
550 hr_utility.trace('Entering pay_magtape_generic.run_overflow');
551 end if;
552 cnt := 2;
553 while cnt <= pay_mag_tape.internal_prm_values(1) loop
554 if ((pay_mag_tape.internal_prm_names(cnt) = 'TRANSFER_RUN_OVERFLOW')
555 and (pay_mag_tape.internal_prm_values(cnt) = 'Y')) then
556 pay_mag_tape.internal_prm_values(cnt) := 'N';
557 return TRUE;
558 end if;
559 cnt := cnt + 1;
560 end loop;
561 if g_debug then
562 hr_utility.trace('Exiting pay_magtape_generic.run_overflow');
563 end if;
564 return FALSE;
565 exception
566 when NO_DATA_FOUND then
567 if g_debug then
568 hr_utility.trace('Exiting pay_magtape_generic.run_overflow');
569 end if;
570 return FALSE;
571 end run_overflow;
572 --
573 /* Procedure - set_report_id
574 Action - This procedure sets the report id on the first run of the
575 procedure.
576 */
577 procedure set_report_id is
578 cnt number;
579 report_name varchar2(30);
580 begin
581 if g_debug then
582 hr_utility.trace('Entering pay_magtape_generic.set_report_id');
583 end if;
584 report_id := '0';
585 cnt := 1;
586 --
587 if (use_action_block = 'Y') then
588 report_name := 'MAGTAPE_ASG_REPORT_ID';
589 else
590 report_name := 'MAGTAPE_REPORT_ID';
591 end if;
592 --
593 while cnt <= pay_mag_tape.internal_prm_values(1) loop
594 if pay_mag_tape.internal_prm_names(cnt) = report_name then
595 hr_utility.trace('Match');
596 report_id := pay_mag_tape.internal_prm_values(cnt);
597 hr_utility.trace('Set Value');
598 end if;
599 cnt := cnt + 1;
600 end loop;
601 if g_debug then
602 hr_utility.trace('Exiting pay_magtape_generic.set_report_id');
603 end if;
604 end;
605 --
606 /* Procedure - setup return_values
607 Action - This procedure sets up the PL/SQL tables for the Fast Formula
608 The context rules and the parameters are transfered from the
609 retrieval table.
610 */
611 procedure setup_return_values (formula_id number,xml_proc_name varchar,
612 return_num number) is
613 cnt number;
614 cxt_cnt number;
615 prm_cnt number;
616 xml_cnt number;
617 chk_cxt number;
618 pos number;
619 str varchar(256);
620 con_str varchar(256);
621 begin
622 -- Set up the Contexts
623 if g_debug then
624 hr_utility.trace('Entering pay_magtape_generic.setup_return_values');
625 end if;
626 cnt := return_num + return_arr_offset; -- Set up the outer loop to
627 cxt_cnt := 1; -- run from the last entry
628 while cnt > 0 loop -- in the retrieval list to
629 pos := instr(ret_vals(cnt), '='); -- the first.
630 if pos <> 0 then
631 str := substr(ret_vals(cnt),
632 pos + 1, pos + 2);
633 con_str := substr(ret_vals(cnt),0, pos - 1);
634 -- If entry is a context rule
635 if str = 'C' then -- then search context table
636 found := FALSE; -- for an existing entry for
637 chk_cxt := 1; -- this context.
638 while chk_cxt <= cxt_cnt loop
639 if pay_mag_tape.internal_cxt_names(chk_cxt) =
640 con_str then
641 found := TRUE;
642 end if;
643 chk_cxt := chk_cxt + 1;
644 end loop;
645 if not found then -- If there was no entry for
646 cxt_cnt := cxt_cnt + 1; -- the context then enter one.
647 pay_mag_tape.internal_cxt_names(cxt_cnt) :=
648 substr(ret_vals(cnt),
649 0, pos - 1);
650 pay_mag_tape.internal_cxt_values(cxt_cnt) := ret_vals(cnt +
651 1);
652 -- Bug 259276. Fix between the two following lines
653 -- Problem with using the to_char fuction within the dynamically called
654 -- cursors.
655 -- This code is now redundant as the value of the date context is
656 -- already in canonical form.
657 -- ---------------------------------------------------------------------------
658 -- if pay_mag_tape.internal_cxt_names(cxt_cnt) like 'DATE%'
662 -- 'YYYY/MM/DD');
659 -- then
660 -- pay_mag_tape.internal_cxt_values(cxt_cnt) :=
661 -- to_char(to_date(pay_mag_tape.internal_cxt_values(cxt_cnt), 'YYYY/MM/DD'),
663 -- end if;
664 -- --------------------------------------------------------------------------
665 end if;
666 end if;
667 end if;
668 cnt := cnt - 1;
669 end loop;
670 -- Set up the parameters
671 cnt := return_arr_offset + 1; -- Search the returns
672 while cnt <= return_num + return_arr_offset loop -- table for the new
673 pos := instr(ret_vals(cnt), '='); -- parameter values
674 if pos <> 0 then
675 str := substr(ret_vals(cnt),
676 pos + 1, pos + 2);
677 con_str := substr(ret_vals(cnt),0, pos - 1);
678 if str = 'P' then
679 found := FALSE;
680 prm_cnt := 1;
681 while prm_cnt <= pay_mag_tape.internal_prm_values(1) loop
682 if pay_mag_tape.internal_prm_names(prm_cnt) = con_str then
683 found := TRUE;
684 cnt := cnt + 1;
685 pay_mag_tape.internal_prm_values(prm_cnt) :=
686 ret_vals(cnt);
687 end if;
688 prm_cnt := prm_cnt + 1;
689 end loop;
690 if not found then -- Add new parameter to table
691 pay_mag_tape.internal_prm_names(prm_cnt) := con_str;
692 cnt := cnt + 1;
693 pay_mag_tape.internal_prm_values(prm_cnt) :=
694 ret_vals(cnt);
695 pay_mag_tape.internal_prm_values(1) := prm_cnt;
696 end if;
697 end if;
698 end if;
699 cnt := cnt + 1;
700 end loop;
701 cnt := return_arr_offset + 1; -- Search the returns
702 xml_cnt := pay_mag_tape.internal_xml_values(1);
703 while cnt <= return_num + return_arr_offset loop -- table for the new
704 pos := instr(ret_vals(cnt), '='); -- parameter values
705 if pos <> 0 then
706 str := substr(ret_vals(cnt),
707 pos + 1, pos + 2);
708 con_str := substr(ret_vals(cnt),0, pos - 1);
709 if str = 'X' then
710 xml_cnt := pay_mag_tape.internal_xml_values(1)+1;
711 pay_mag_tape.internal_xml_names(xml_cnt) := con_str;
712 cnt := cnt + 1;
713 pay_mag_tape.internal_xml_values(xml_cnt) :=
714 ret_vals(cnt);
715 xml_cnt := pay_mag_tape.internal_xml_values(1)+2;
716 pay_mag_tape.internal_xml_names(xml_cnt) := '/'||con_str;
717 pay_mag_tape.internal_xml_values(1) := xml_cnt;
718 end if;
719 end if;
720 cnt := cnt + 1;
721 end loop;
722 --
723 -- Set up the formula id and context count
724 pay_mag_tape.internal_cxt_values(1) := cxt_cnt;
725 pay_mag_tape.internal_xml_values(1) := xml_cnt;
726 pay_mag_tape.internal_prm_values(2) := formula_id;
727 pay_mag_tape.internal_xml_values(2) := xml_proc_name;
728 int_prm_names := pay_mag_tape.internal_prm_names;
729 int_prm_values := pay_mag_tape.internal_prm_values;
730 if (nvl(formula_action_level, 'N') = 'A') then
731 process_action_rec := 'Y';
732 else
733 process_action_rec := 'N';
734 end if;
735 if g_debug then
736 hr_utility.trace('Exiting pay_magtape_generic.setup_return_values');
737 end if;
738 end setup_return_values;
739 --
740 BEGIN
741 g_debug := hr_utility.debug_enabled;
742 if g_debug then
743 hr_utility.trace('Entering pay_magtape_generic.new_formula');
744 end if;
745 -- First run through setup report details
746 int_prm_names := pay_mag_tape.internal_prm_names;
747 int_prm_values := pay_mag_tape.internal_prm_values;
748 if level_no = 0 then
749 g_debug := hr_utility.debug_enabled;
750 set_report_id;
751 level_no := 1;
752 return_arr_offset := 0;
753 select cursor_name, nvl(no_column_returned,0), magnetic_block_id
754 into curs(1), column_num(1), block_id(1)
755 from pay_magnetic_blocks
756 where main_block_flag = 'Y'
757 and report_format = report_id;
758
759 hr_utility.trace(curs(1)||'..'||to_char(column_num(1))||'..'||to_char(block_id(1)));
760
761 prm_cnt :=pay_mag_tape.internal_prm_values(1)+1;
762 pay_mag_tape.internal_prm_names(prm_cnt) :='magnetic_block_id';
763 pay_mag_tape.internal_prm_values(prm_cnt):=block_id(1);
764 prm_cnt :=pay_mag_tape.internal_prm_values(1)+2;
765 pay_mag_tape.internal_prm_names(prm_cnt) :='rec_sequence';
766 pay_mag_tape.internal_prm_values(1):=prm_cnt;
767 int_prm_names := pay_mag_tape.internal_prm_names;
768 int_prm_values := pay_mag_tape.internal_prm_values;
769
773 pay_mag_tape.internal_xml_names(2) := formula_block_label;
770 end if;
771 if formula_block_label is not null
772 then
774 pay_mag_tape.internal_xml_values(1) := 2;
775 end if;
776 -- The previous formula requests a new
777 -- block to be set up
778 if formula_next_block is not null then
779 return_arr_offset := return_arr_offset + column_num(level_no);
780 level_no := level_no + 1;
781 select cursor_name, nvl(no_column_returned,0), magnetic_block_id
782 into curs(level_no), column_num(level_no), block_id(level_no)
783 from pay_magnetic_blocks
784 where magnetic_block_id = formula_next_block
785 and report_format = report_id;
786 hr_utility.trace(to_char(level_no)||'..'||curs(level_no)||'..'||to_char(column_num(level_no))||'..'||to_char(block_id(level_no)));
787 i:=1;
788 while (pay_mag_tape.internal_prm_names(i) <>'magnetic_block_id')
789 loop
790 i:=i+1;
791 end loop;
792 pay_mag_tape.internal_prm_values(i):=block_id(level_no);
793 int_prm_values := pay_mag_tape.internal_prm_values;
794 end if;
795 found := FALSE;
796 --
797 -- Is formula an overflow repeat and is the overflow formula requested to run
798 --
799 if (formula_overflow = 'R') then
800 if run_overflow then
801 if not running_intermediate(level_no) then
802 found := TRUE;
803 else
804 if intermediate_needed then
805 found := TRUE;
806 end if;
807 end if;
808 end if;
809 end if;
810 --
811 -- Loop until the next formula is found
812 --
813
814 cur_fetch:=FALSE;
815 while not found loop
816 -- If formulas cursor is not open then open
817 -- cursor
818 if not tab_is_open(formulas, level_no) then
819 -- If a driving cursor exists and its not open
820 -- then set the cursor up.
821 if not(curs(level_no) is null) then
822 if not curs_is_open(curs(level_no)) then
823 curs_open(curs(level_no));
824 row_counts(level_no) := 0;
825 running_intermediate(level_no) := FALSE;
826 intermediate_run(level_no) := FALSE;
827 end if;
828 curs_fetch(curs(level_no), column_num(level_no));
829 row_counts(level_no) := row_counts(level_no) + 1;
830 -- If no data is retrieved from the driving
831 -- cursor return to the previous level after
832 -- an intermediate run if required.
833 if curs_no_data(curs(level_no)) then
834 cur_fetch:=FALSE;
835 if (is_intermediate_required(level_no)
836 and not running_intermediate(level_no)) then
837 formulas(level_no) := open_inter_formula(
838 block_id(level_no));
839 running_intermediate(level_no) := TRUE;
840 else
841 if (formula_id=-9999 and xml_proc_name is null) then
842
843 no_rows := dbms_sql.last_row_count;
844 if (level_no >1) then
845 dbms_sql.column_value(formulas(level_no-1), 7,
846 temp_formula_block_label);
847 /* if (no_rows=0 and temp_formula_block_label is not NULL) then
848 cnt :=pay_mag_tape.internal_xml_values(1)-1;
849 pay_mag_tape.internal_xml_values(1) := cnt;
850 els*/if (temp_formula_block_label is not NULL) then
851 cnt :=pay_mag_tape.internal_xml_values(1)+1;
852 pay_mag_tape.internal_xml_names(cnt) := '/'||temp_formula_block_label;
853 pay_mag_tape.internal_xml_values(1) := cnt;
854 end if;
855 end if;
856 end if;
857 curs_close(curs(level_no));
858 level_no := level_no - 1;
859 return_arr_offset := return_arr_offset -
860 column_num(level_no);
861 end if;
862 else
863 cur_fetch:=TRUE;
864 formulas(level_no) := open_formula(block_id(level_no));
865 end if;
866 else
867 if first_run(level_no) then
868 formulas(level_no) := open_formula(block_id(level_no));
869 running_intermediate(level_no) := FALSE;
870 intermediate_run(level_no) := FALSE;
871 else
872 level_no := level_no -1;
873 end if;
874 end if;
875 else
876 -- Get formula details
877 if dbms_sql.fetch_rows(formulas(level_no)) > 0 then
881 dbms_sql.column_value(formulas(level_no), 4, formula_overflow);
878 dbms_sql.column_value(formulas(level_no), 1, formula_id);
879 dbms_sql.column_value(formulas(level_no), 2, formula_next_block);
880 dbms_sql.column_value(formulas(level_no), 3, formula_frequency);
882 dbms_sql.column_value(formulas(level_no), 5,
883 formula_inter_repeat);
884 dbms_sql.column_value(formulas(level_no), 6,
885 formula_action_level);
886 dbms_sql.column_value(formulas(level_no), 7,
887 formula_block_label);
888 dbms_sql.column_value(formulas(level_no), 8,
889 formula_block_row_label);
890 dbms_sql.column_value(formulas(level_no), 9, xml_proc_name);
891 dbms_sql.column_value(formulas(level_no), 10, rec_sequence);
892
893 -- set up mgnetic record id a s a paramter
894 i:=1;
895 while (pay_mag_tape.internal_prm_names(i) <>'rec_sequence')
896 loop
897 i:=i+1;
898 end loop;
899 pay_mag_tape.internal_prm_values(i):=rec_sequence;
900 int_prm_values := pay_mag_tape.internal_prm_values;
901
902 --
903 -- Is formula an overflow and is the overflow formula requested to run
904 --
905 if ((formula_overflow = 'Y') or (formula_overflow = 'R')) then
906 if run_overflow then
907 if not running_intermediate(level_no) then
908 found := TRUE;
909 else
910 if intermediate_needed then
911 found := TRUE;
912 end if;
913 end if;
914 end if;
915 else
916 --
917 -- Is formula a skip count and has the count reached the run point
918 --
919 if formula_frequency is not null then
920 if not running_intermediate(level_no) then
921 if (row_counts(level_no)
922 mod formula_frequency) = 0 then
923 intermediate_run(level_no) := TRUE;
924 found := TRUE;
925 end if;
926 else
927 if ((row_counts(level_no) -1)
928 mod formula_frequency <> 0) and
929 intermediate_needed then
930 found := TRUE;
931 end if;
932 end if;
933 else
934 --
935 -- Ordinary formula that runs everytime
936 --
937 if not running_intermediate(level_no) then
938 found := TRUE;
939 else
940 if intermediate_needed then
941 found := TRUE;
942 end if;
943 end if;
944 end if;
945 end if;
946 else
947 dbms_sql.close_cursor(formulas(level_no));
948 end if;
949 end if;
950 end loop;
951
952 if (formula_id=-9999 and xml_proc_name is null) then
953 if (level_no>1) then
954 dbms_sql.column_value(formulas(level_no-1), 8, temp_formula_block_row_label);
955 if (cur_fetch=TRUE and temp_formula_block_row_label is not null)
956 then
957 hr_utility.trace(to_char(cnt)||temp_formula_block_row_label);
958 cnt :=pay_mag_tape.internal_xml_values(1)+1;
959 pay_mag_tape.internal_xml_names(cnt) := temp_formula_block_row_label;
960 pay_mag_tape.internal_xml_values(1) := cnt;
961 setup_return_values(formula_id,xml_proc_name, column_num(level_no));
962 hr_utility.trace(to_char(cnt)||'..'||pay_mag_tape.internal_xml_values(1));
963 cnt :=pay_mag_tape.internal_xml_values(1)+1;
964 hr_utility.trace(to_char(cnt));
965 pay_mag_tape.internal_xml_names(cnt) := '/'||temp_formula_block_row_label;
966 pay_mag_tape.internal_xml_values(1) := cnt;
967 elsif (cur_fetch=TRUE) then
968 setup_return_values(formula_id,xml_proc_name, column_num(level_no));
969 end if;
970 elsif (cur_fetch=TRUE) then
971 setup_return_values(formula_id,xml_proc_name, column_num(level_no));
972 end if;
973 else
974 setup_return_values(formula_id,xml_proc_name, column_num(level_no));
975 end if;
976
977 if g_debug then
978 hr_utility.trace('Exiting pay_magtape_generic.new_formula');
979 end if;
980 END new_formula;
981 --
982 procedure clear_cursors is
983 begin
984 while level_no >0
985 loop
986 if (dbms_sql.is_open(formulas(level_no))) then
987 dbms_sql.close_cursor(formulas(level_no));
988 end if;
989 if curs_is_open(curs(level_no)) then
990 curs_close(curs(level_no));
991 end if;
992 level_no := level_no-1;
993 end loop;
994 return_arr_offset := 0;
995 formula_next_block := NULL;
996 process_action_rec := 'N';
997
998 end;
999
1000 BEGIN
1001 level_no := 0;
1002 formula_next_block := NULL;
1003 use_action_block := 'N';
1004 process_action_rec := 'N';
1005 END pay_magtape_generic;
[Home] [Help]