1 package body pay_archive as
2 /* $Header: pyarchiv.pkb 120.2.12010000.1 2008/07/27 22:03:29 appldev ship $ */
3 /*
4 * ---------------------------------------------------------------------------
5 Copyright (c) Oracle Corporation (UK) Ltd 1992.
6 All Rights Reserved.
7 --
8 --
9 PRODUCT
10 Oracle*Payroll
11 NAME
12 pyarchiv.pkb
13 NOTES
14 generic archiving procedures.
15 PROCEDURES
16 --
17 MODIFIED
18 --
19 ssinha 20-APR-1998 Created
20 dzshanno 22-JUN-1998 Added handling for multiple jurisdictions
21 dzshanno 08-JUL-1998 Added backward compatibility for report
22 dzshanno 17-JUL-1998 non_bal dbi with jurisdiction_code context now
23 supported without multiple jurisdictions
24 nbristow 05-AUG-1998 Changes for running magtape only part.
25 dzshanno 06-AUG-1998 Fixed typo in arch_db_items_loop
26 nbristow 08-MAR-1999 Added remove_report_actions.
27 nbristow 17-SEP-1999 Added archive_aa.
28 mreid 01-OCT-1999 Added explicit column list to insert on
29 ff_archive_items
30 nbristow 09-NOV-1999 Changes to the ff_archive_item table made for
31 the new Archive Item API.
32 alogue 15-FEB-2000 Utf8 support : ff_database_items user_name and
33 ff_user_entities item_name lengthened to 240.
34 Use of varchar_240_tbl.
35 Remove insert of 'AAP' into
36 ff_archive_items.archive_type as column doesn't
37 exist in 11i.
38 nbristow 19-MAY-2000 Added the deinitialize section.
39 ssarma 03-AUG-2000 Added US specific code for EOY related issue.
40 It starts as -- if g_leg_code = US then ....
41 nbristow 18-FEB-2001 Changed the process_employee so that the dynamic
42 procedure call can be done with out the
43 initialisation procedure.
44 mreid 26-MAR-2002 Bug 2281868. Added legislation_code to
45 csr_defined_balance cursor.
46 nbristow 16-JUL-2002 Added standard_deinit.
47 alogue 23-JUN-2003 Handle removal of lines from pay_population_ranges
48 in remove_report_actions. Bug 3017447.
49 nbristow 16-DEC-2003 Now delteting from pay_temp_object_actions.
50 alogue 25-FEB-2004 Bulk operations within remove_report_actions
51 for performance purposes.
52 nbristow 09-JUL-2004 Added process_chunk.
53 mreid 11-NOV-2005 Bug 4729140: added date effective joins in
54 arch_db_items_loop
55 alogue 31-AUG-2007 Bug 6196572: performance fix to
56 remove_report_actions. Deletion of assignment
57 actions to inside this loop to avoid rollback
58 segment issue.
59 --
60 --
61 * ---------------------------------------------------------------------
62 */
63 -- TYPES
64 --
65 -- The table types are just simple tables or various types. The records
66 -- are composite types of tables that contain a size (sz) to hold the
67 -- number of data items currently stored in the table. Data items are
68 -- stored in the tables within the records contiguously from 1 to sz.
69 --==================================================================
70 TYPE varchar_1_tbl IS TABLE OF VARCHAR(1) INDEX BY binary_integer;
71 TYPE boolean_tbl IS TABLE OF BOOLEAN INDEX BY binary_integer;
72 --
73 TYPE archive_items IS RECORD
74 (
75 item_name varchar_240_tbl,
76 user_entity_id number_tbl,
77 data_type varchar_1_tbl,
78 jur_level number_tbl,
79 context_start number_tbl,
80 context_end number_tbl,
81 sz INTEGER
82 );
83 --
84 TYPE balances IS RECORD
85 (
86 item_name varchar_240_tbl,
87 user_entity_id number_tbl,
88 balance_id number_tbl,
89 jur_level number_tbl,
90 context_start number_tbl,
91 context_end number_tbl,
92 sz integer
93 );
94 --
95 TYPE contexts IS RECORD
96 (
97 name varchar_60_tbl,
98 sz integer
99 );
100 -- Table variables
101 ------------------------------------------------------------------------
102 l_balance_dbis balances;
103 l_contexts_dbi contexts;
104 l_assignment_dbis archive_items;
105 ------------------------------------------------------------------------
106 l_jur_set varchar_60_tbl;
107 l_jur1_set varchar_60_tbl;
108 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
109 l_business_group_id pay_payroll_actions.business_group_id%TYPE;
110 l_legislation_code per_business_groups.legislation_code%TYPE;
111 l_effective_date pay_payroll_actions.effective_date%TYPE;
112 l_date_earned pay_payroll_actions.date_earned%TYPE;
113 l_report_format pay_report_format_mappings_f.report_format%TYPE;
114 l_report_type pay_report_format_mappings_f.report_type%TYPE;
115 legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
116 non_unique_con boolean := FALSE;
117 process_archive boolean := FALSE;
118 -----------------------------------------------------------------------
119 -- PROCEDURES
120 -----------------------------------------------------------------------------
121 -- Name
122 -- bal_db_item
123 -- Purpose
124 -- Given the name of a balance DB item as would be seen in a fast formula
125 -- it returns the defined_balance_id of the balance it represents.
126 -- Arguments
127 -- Notes
128 -- A defined +balance_id is required by the PLSQL balance function.
129 -----------------------------------------------------------------------------
130 --
131 function bal_db_item
132 (
133 p_db_item_name varchar2
134 ) return number is
135 --
136 -- Get the defined_balance_id for the specified balance DB item.
137 --
138 cursor csr_defined_balance is
139 select to_number(UE.creator_id)
140 from ff_database_items DI,
141 ff_user_entities UE
142 where DI.user_name = p_db_item_name
143 and UE.user_entity_id = DI.user_entity_id
144 and UE.creator_type = 'B'
145 and (UE.legislation_code = l_legislation_code
146 or UE.business_group_id = l_business_group_id);
147 --
148 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
149 --
150 begin
151 --
152 hr_utility.set_location('pay_archive.bal_db_item - opening cursor', 1);
153 open csr_defined_balance;
154 fetch csr_defined_balance into l_defined_balance_id;
155 if csr_defined_balance%notfound then
156 close csr_defined_balance;
157 raise hr_utility.hr_error;
158 else
159 hr_utility.set_location('pay_archive.bal_db_item - fetched from cursor', 2);
160 close csr_defined_balance;
161 end if;
162 --
163 return (l_defined_balance_id);
164 --
165 end bal_db_item;
166 --
167
168 function get_jursd_level(p_route_id number,p_user_entity_id number) return number is
169 l_jursd_value number:= 0;
170
171 begin
172
173 select frpv.value
174 into l_jursd_value
175 from ff_route_parameter_values frpv,
176 ff_route_parameters frp
177 where frpv.route_parameter_id = frp.route_parameter_id
178 and frpv.user_entity_id = p_user_entity_id
179 and frp.route_id = p_route_id
180 and frp.parameter_name = 'Jursd. Level';
181
182 return(l_jursd_value);
183
184 exception
185 when no_data_found then
186 return(0);
187 when others then
188 hr_utility.trace('Error while getting the jursd. value ' ||
189 to_char(sqlcode));
190
191 end get_jursd_level;
192
193 --
194 -- PROCEDURE SET_DBI_LEVEL (GLOBAL)--------------------------------------------
195 --
196 -- used to set the jurisdiction level for non balance database items.
197 -- this allows these dbi to be archived to the correct level
198 -- p_dbi_name The name of the dbi you wish to set the jur_level for
199 -- p_jur_level The level to be set
200 ------------------------------------------------------------------------
201 procedure set_dbi_level (p_dbi_name in varchar2,
202 p_jur_level in varchar2) is
203
204 begin
205 hr_utility.set_location('set_dbi_level',1);
206 for i IN 1..l_assignment_dbis.sz LOOP
207 if l_assignment_dbis.item_name(i) = p_dbi_name then
208 l_assignment_dbis.jur_level(i) := p_jur_level;
209 hr_utility.set_location('set_dbi_level',21);
210 hr_utility.trace('Jurisdiction level for '||p_dbi_name||' set to '||p_jur_level);
211 exit;
212 end if;
213 end loop;
214 --
215 end set_dbi_level;
216 --
217 -- PROCDURE ARCH_DB_ITEMS_LOOP ----------------------------------------
218 --
219 -- Loop through db items and store them in plsql cache tables
220 -- db_items_csr Database items cursor for a report format
221 -- contexts_csr Contexts cursor for a live user entity id
222 -- user_entity_id Current user entity id
223 -- route_id Current Route id
224 -- a_user_entity_id archive user entity id
225 -- a_data_type Archive data type
226 -- creator_type Creator type == used to identify balance db item
227 -- name Database item without prefix
228 -- flag flag for which of the two plsql tables to store in
229 ----------------------------------------------------------------------
230 procedure arch_db_items_loop (p_effective_date DATE) IS
231 CURSOR db_items_csr (p_report_format VARCHAR2) IS
232 SELECT distinct us.item_name
233 FROM pay_magnetic_blocks mb,
234 pay_magnetic_records mr,
235 ff_fdi_usages_f us
236 WHERE mb.report_format = p_report_format AND
237 mb.magnetic_block_id = mr.magnetic_block_id AND
238 mr.formula_id = us.formula_id AND
239 us.usage = 'D' AND
240 p_effective_date BETWEEN us.effective_start_date AND
241 us.effective_end_date;
242 --
243 CURSOR contexts_csr (p_user_entity_id VARCHAR2) IS
244 SELECT con.context_name name
245 FROM ff_user_entities ue,
246 ff_route_context_usages rcu,
247 ff_contexts con
248 WHERE ue.user_entity_id = p_user_entity_id AND
249 ue.route_id = rcu.route_id AND
250 con.context_id = rcu.context_id ;
251 --
252 db_items_row db_items_csr%ROWTYPE;
253 contexts_row contexts_csr%ROWTYPE;
254 user_entity_id ff_database_items.user_entity_id%TYPE;
255 route_id ff_routes.route_id%TYPE;
256 a_user_entity_id ff_database_items.user_entity_id%TYPE;
257 a_data_type ff_database_items.data_type%TYPE;
258 creator_type ff_user_entities.creator_type%TYPE;
259 name VARCHAR2(240);
260 flag VARCHAR2(1);
261 --
262 BEGIN
263 hr_utility.set_location ('arch_db_items_loop',1);
264 --
265 -- Loop through database items
266 FOR db_items_row IN db_items_csr (l_report_format) LOOP
267 --
268 hr_utility.set_location ('arch_db_items_loop',11);
269 --
270 -- Ignore any database item found without a A_ prefix
271 ----------------------------------------------------------------------
272 IF substr(db_items_row.item_name,1,2) <> 'A_' THEN
273 hr_utility.trace ('** Ignoring DB Item without A_ prefix: ' ||
274 db_items_row.item_name || ' **');
275 ELSE
276 hr_utility.set_location ('arch_db_items_loop',111);
277 -- get original database item name to run.
278 name := substr (db_items_row.item_name,3,
279 length(db_items_row.item_name)-2);
280 hr_utility.trace ('** Found '|| db_items_row.item_name ||' **');
281 hr_utility.set_location ('arch_db_items_loop',112);
282 --
283 -- Get archive entity id and data type
284 SELECT dbi.user_entity_id, dbi.data_type
285 INTO a_user_entity_id, a_data_type
286 FROM ff_database_items dbi,
287 ff_user_entities ue
288 WHERE dbi.user_name = db_items_row.item_name AND
289 dbi.user_entity_id = ue.user_entity_id AND
290 ((ue.legislation_code is null
291 and ue.business_group_id is null
292 and not exists
293 (select ''
294 from ff_user_entities fue2,
295 ff_database_items fdi2
296 where fdi2.user_name = db_items_row.item_name
297 and fdi2.user_entity_id = fue2.user_entity_id
298 and (fue2.business_group_id = l_business_group_id
299 or fue2.legislation_code = l_legislation_code)
300 )
301 )
302 OR (ue.business_group_id is null
303 and l_legislation_code = ue.legislation_code
304 and not exists
305 (select ''
306 from ff_user_entities fue2,
307 ff_database_items fdi2
308 where fdi2.user_name = db_items_row.item_name
309 and fdi2.user_entity_id = fue2.user_entity_id
310 and fue2.business_group_id = l_business_group_id
311 )
312 )
313 OR ue.business_group_id + 0 = l_business_group_id
314 );
315 hr_utility.set_location ('arch_db_items_loop',1121);
316 --------------------------------------------------------------------
317 -- Get live entity id to get the contexts
318 --
319 SELECT dbi.user_entity_id,ue.creator_type,ue.route_id
320 INTO user_entity_id,creator_type,route_id
321 FROM ff_database_items dbi,
322 ff_user_entities ue
323 WHERE dbi.user_name = name AND
324 dbi.user_entity_id = ue.user_entity_id
325 and ((ue.legislation_code is null
326 and ue.business_group_id is null
327 and not exists
328 (select ''
329 from ff_user_entities fue2,
330 ff_database_items fdi2
331 where fdi2.user_name = name
332 and fdi2.user_entity_id = fue2.user_entity_id
333 and (fue2.business_group_id = l_business_group_id
334 or fue2.legislation_code = l_legislation_code)
335 )
336 )
337 or (ue.business_group_id is null
338 and l_legislation_code = ue.legislation_code
339 and not exists
340 (select ''
341 from ff_user_entities fue2,
342 ff_database_items fdi2
343 where fdi2.user_name = name
344 and fdi2.user_entity_id = fue2.user_entity_id
345 and fue2.business_group_id = l_business_group_id
346 )
347 )
348 or ue.business_group_id + 0 = l_business_group_id
349 );
350 --
351 -- Check to see if db_item is balance or assignment,
352 -- assume that it's an assignment.
353 -----------------------------------------------------------------
354 flag := 'A';
355 IF creator_type = 'B' THEN
356 flag := 'B';
357 ELSE
358 FOR contexts_row IN contexts_csr (user_entity_id) LOOP
359 IF (contexts_row.name IN('ASSIGNMENT_ACTION_ID','ASSIGNMENT_ID')) THEN
360 flag := 'A';
364 END IF;
361 EXIT;
362 END IF;
363 END LOOP;
365 --
366 hr_utility.set_location ('arch_db_items_loop',113);
367 -----------------------------------------------------------------
368 -- Store archive data in plsql tables
369 -----------------------------------------------------------------
370 IF flag = 'B' THEN
371 hr_utility.set_location ('arch_db_items_loop',1131);
372 --
373 l_balance_dbis.sz := l_balance_dbis.sz + 1;
374 l_balance_dbis.item_name(l_balance_dbis.sz) := name;
375 l_balance_dbis.user_entity_id(l_balance_dbis.sz)
376 := a_user_entity_id;
377 l_balance_dbis.balance_id(l_balance_dbis.sz)
378 := bal_db_item (l_balance_dbis.item_name(l_balance_dbis.sz));
379 --
380 -- New bit
381 -- Find the jurisdiction level of the balance
382 -----------------------------------------------------------
383 SELECT jurisdiction_level jur_lev
384 INTO l_balance_dbis.jur_level(l_balance_dbis.sz)
385 FROM pay_balance_types pbt,
386 pay_defined_balances pdb
387 WHERE pbt.balance_type_id= pdb.balance_type_id AND
388 pdb.defined_balance_id =
389 l_balance_dbis.balance_id(l_balance_dbis.sz);
390 --
391 -----------------------------------------------------------
392 -- store the name of contexts and how many in the PLSQL table
393 --
394 l_balance_dbis.context_start(l_balance_dbis.sz)
395 :=l_contexts_dbi.sz+1;
396 FOR contexts_row IN contexts_csr (user_entity_id) LOOP
397 l_contexts_dbi.sz := l_contexts_dbi.sz +1;
398 l_contexts_dbi.name(l_contexts_dbi.sz):=contexts_row.name;
399 l_balance_dbis.context_end(l_balance_dbis.sz)
400 := l_contexts_dbi.sz;
401 end loop;
402 --
403 ELSIF flag = 'A' THEN
404 hr_utility.set_location ('arch_db_items_loop',1132);
405 --
406 l_assignment_dbis.sz := l_assignment_dbis.sz + 1;
407 l_assignment_dbis.item_name(l_assignment_dbis.sz) := name;
408 l_assignment_dbis.user_entity_id(l_assignment_dbis.sz)
409 := a_user_entity_id;
410 l_assignment_dbis.data_type(l_assignment_dbis.sz)
411 := a_data_type;
412 -- New bit
413 -- Find the jurisdiction level of the balance
414 -----------------------------------------------------------
415 l_assignment_dbis.jur_level(l_assignment_dbis.sz)
416 := get_jursd_level(route_id,user_entity_id);
417 --
418 -- store the name of contexts and how many in the PLSQL table
419 --
420 l_assignment_dbis.context_start(l_assignment_dbis.sz)
421 :=l_contexts_dbi.sz+1;
422 FOR contexts_row IN contexts_csr (user_entity_id) LOOP
423 l_contexts_dbi.sz := l_contexts_dbi.sz +1;
424 l_contexts_dbi.name(l_contexts_dbi.sz):=contexts_row.name;
425 l_assignment_dbis.context_end(l_assignment_dbis.sz)
426 :=l_contexts_dbi.sz;
427 end loop;
428 --
429 END IF;
430 --
431 END IF;
432 --
433 hr_utility.set_location ('arch_db_items_loop',12);
434 --
435 END LOOP; -- go back for next dbi
436 --
437 hr_utility.set_location ('arch_db_items_loop',2);
438 END arch_db_items_loop;
439 --
440 -----------------------------------------------------------------------
441 -- ARCH_INITIALISE
442 --
443 -- Initialise tables and reference variables.
444 -- Also instantiate plsql tables with database items.
445 --======================================================================
446 PROCEDURE arch_initialise (p_payroll_action_id in NUMBER)
447 IS
448 BEGIN
449 hr_utility.set_location ('arch_initialise',1);
450 -----------------------------------------------------------------------
451 -- Initialise table sizes
452 -----------------------------------------------------------------------
453 l_balance_dbis.sz := 0;
454 l_assignment_dbis.sz := 0;
455 l_contexts_dbi.sz := 0;
456 g_context_values.sz := 0;
457 --
458 hr_utility.set_location ('arch_initialise',2);
459 l_payroll_action_id := p_payroll_action_id;
460 -----------------------------------------------------------------------
461 SELECT pa.business_group_id,
462 bg.legislation_code,
463 pa.effective_date,
464 pa.date_earned,
465 pa.legislative_parameters
466 INTO l_business_group_id,
467 l_legislation_code,
468 l_effective_date,
469 l_date_earned,
470 legislative_parameters
471 FROM pay_payroll_actions pa,
472 per_business_groups bg
473 WHERE pa.payroll_action_id = l_payroll_action_id AND
474 pa.business_group_id = bg.business_group_id;
475 --
476 g_leg_code := l_legislation_code;
477 hr_utility.set_location ('arch_initialise',3);
478 ---------------------------------------------------------------------
479 -- Get format for report type and state specified.
480 SELECT prfm.report_format, pac.report_type
481 INTO l_report_format, l_report_type
482 FROM pay_report_format_mappings_f prfm,
483 pay_payroll_actions pac
484 WHERE prfm.report_type = pac.report_type
488 AND pac.effective_date BETWEEN effective_start_date AND
485 AND prfm.report_qualifier = pac.report_qualifier
486 AND prfm.report_category = pac.report_category
487 AND pac.payroll_action_id = l_payroll_action_id
489 effective_end_date;
490 hr_utility.set_location ('arch_initialise',5);
491
492 -- Create dynsql to invoke legislative hook
493 declare
494 sql_cur number;
495 ignore number;
496 init_proc pay_report_format_mappings.initialization_code%TYPE;
497 statem varchar2(256);
498 pactid number;
499 begin
500 pactid := l_payroll_action_id;
501 select prfm.initialization_code
502 into init_proc
503 from pay_report_format_mappings_f prfm,
504 pay_payroll_actions ppa
505 where ppa.payroll_action_id = pactid
506 and ppa.report_type = prfm.report_type
507 and ppa.report_category = prfm.report_category
508 and ppa.effective_date between prfm.effective_start_date
509 and prfm.effective_end_date
510 and ppa.report_qualifier = prfm.report_qualifier;
511 --
512 /* if the initialisation code is not set
513 then we need to record that.
514 This means that no archiving will take
515 place
516 */
517 if(init_proc is null) then
518 process_archive := FALSE;
519 /*
520 hr_utility.set_message(801,
521 'PAY_34956_ARCINIT_MUST_EXIST');
522 hr_utility.raise_error;
523 */
524 else
525 process_archive := TRUE;
526 end if;
527 --
528 /* Only process the archiver if process_archive is set */
529 if (process_archive = TRUE) then
530 --
531 statem := 'BEGIN '||init_proc||'(:pactid); END;';
532 --
533 hr_utility.set_location ('arch_initialise',6);
534 hr_utility.trace(statem);
535
536 sql_cur := dbms_sql.open_cursor;
537 dbms_sql.parse(sql_cur,
538 statem,
539 dbms_sql.v7);
540 --
541 dbms_sql.bind_variable(sql_cur, ':pactid', pactid);
542 ignore := dbms_sql.execute(sql_cur);
543 dbms_sql.close_cursor(sql_cur);
544 --
545 hr_utility.set_location('arch_initialise', 7);
546 --
547 end if;
548 --
549 exception
550 when others then
551 if (dbms_sql.is_open(sql_cur)) then
552 dbms_sql.close_cursor(sql_cur);
553 end if;
554 raise;
555 end;
556 -- Call procedure to retrieve all database items
557 -- from the magtape formula for formatting magnetic
558 -- blocks and headers.
559 -- This will also instantiate all plsql tables with db items.
560 --
561 if (process_archive = TRUE) then
562 hr_utility.set_location('arch_initialise', 8);
563 arch_db_items_loop(l_effective_date);
564 end if;
565 --
566 end arch_initialise;
567 --
568 -----------------------------------------------------------------------
569 -- DEINITIALISE
570 --
571 -- This basically just calls the deinitialise code specified for this
572 -- report type.
573 --
574 --======================================================================
575 PROCEDURE deinitialise (p_payroll_action_id in NUMBER)
576 IS
577 BEGIN
578 hr_utility.set_location ('deinitialise',1);
579 --
580 l_payroll_action_id := p_payroll_action_id;
581 --
582 hr_utility.set_location ('deinitialise',3);
583 ---------------------------------------------------------------------
584 -- Create dynsql to invoke legislative hook
585 declare
586 sql_cur number;
587 ignore number;
588 deinit_proc varchar2(60);
589 statem varchar2(256);
590 pactid number;
591 begin
592 pactid := l_payroll_action_id;
593 select prfm.deinitialization_code
594 into deinit_proc
595 from pay_report_format_mappings_f prfm,
596 pay_payroll_actions ppa
597 where ppa.payroll_action_id = pactid
598 and ppa.report_type = prfm.report_type
599 and ppa.report_category = prfm.report_category
600 and ppa.effective_date between prfm.effective_start_date
601 and prfm.effective_end_date
602 and ppa.report_qualifier = prfm.report_qualifier;
603 --
604 if(deinit_proc is not null) then
605 --
606 statem := 'BEGIN '||deinit_proc||'(:pactid); END;';
607 --
608 hr_utility.set_location ('deinitialise',6);
609 hr_utility.trace(statem);
610
611 sql_cur := dbms_sql.open_cursor;
612 dbms_sql.parse(sql_cur,
613 statem,
614 dbms_sql.v7);
615 --
616 dbms_sql.bind_variable(sql_cur, ':pactid', pactid);
617 ignore := dbms_sql.execute(sql_cur);
618 dbms_sql.close_cursor(sql_cur);
619 --
620 hr_utility.set_location('deinitialise', 7);
621 --
625 when others then
622 end if;
623 --
624 exception
626 if (dbms_sql.is_open(sql_cur)) then
627 dbms_sql.close_cursor(sql_cur);
628 end if;
629 raise;
630 end;
631 --
632 end deinitialise;
633 --------------------------------------------------------------------
634 -- procedure ARCH_STORE
635 --
636 -- Store the data to the archive tables
637 --
638 PROCEDURE arch_store (p_item_name in varchar2,
639 p_user_entity_id in ff_archive_items.user_entity_id%TYPE,
640 p_context1 in ff_archive_items.context1%TYPE,
641 p_value in ff_archive_items.value%TYPE
642 ) IS
643 begin
644 hr_utility.set_location ('arch_store',121);
645 --
646 INSERT INTO ff_archive_items
647 ( ARCHIVE_ITEM_ID, USER_ENTITY_ID, CONTEXT1, VALUE)
648 VALUES
649 ( ff_archive_items_s.nextval,p_user_entity_id,p_context1,p_value);
650 --
651 hr_utility.set_location ('arch_store',122);
652 END arch_store;
653 ------------------------------------------------------------------------
654 --
655 -- PROCEDURE ARCHIVE_DBI
656 -- single contexts
657 --
658 ------------------------------------------------------------------------
659 procedure archive_dbi ( p_balance_ptr number,
660 p_context_ptr number,
661 p_assactid number) is
662 --
663 context_val_loop number;
664 balance_ptr number;
665 context_ptr number;
666 l_level number;
667 v_context_value ff_archive_item_contexts.context%TYPE;
668 v_context_id ff_contexts.context_id%TYPE;
669 result ff_archive_items.value%TYPE;
670 begin
671 --
672 hr_utility.set_location ('archive_dbi',1);
673 --
674 balance_ptr:=p_balance_ptr;
675 context_ptr:=p_context_ptr;
676 --
677 hr_utility.set_location ('archive_dbi',2);
678 --
679 -- for the context specified (Jur Code, Tax Unit etc) go get all the
680 -- different values.
681 FOR context_val_loop IN 1..NVL(g_context_values.sz,0) LOOP
682 hr_utility.set_location ('archive_dbi',3);
683 --
684 if g_context_values.name(context_val_loop) = l_contexts_dbi.name(context_ptr) then
685 --
686 hr_utility.set_location ('archive_dbi',41);
687 --
688 non_unique_con := FALSE;
689 if g_context_values.name(context_val_loop) = 'JURISDICTION_CODE'
690 and l_jur_set.last is not null then
691 --
692 hr_utility.set_location ('archive_dbi',5);
693 --
694 non_unique_con := FALSE;
695 l_level :=NVL(l_balance_dbis.jur_level(balance_ptr),0);
696 --
697 FOR i IN 1..l_jur_set.last LOOP
698 --
699 if substr(l_jur_set(i),1,l_level) =
700 substr(g_context_values.value(context_val_loop) ,1,l_level)
701 then
702 non_unique_con := TRUE;
703 end if;
704 end loop;
705 end if;
706 --
707 if non_unique_con = FALSE then
708 if g_context_values.name(context_val_loop) ='JURISDICTION_CODE' then
709 l_level :=NVL(l_balance_dbis.jur_level(balance_ptr),0);
710
711 /* Specific to US Legislation */
712 if g_leg_code = 'US' then
713 if l_level = 2 and length(rtrim(g_context_values.value(context_val_loop))) <> 11 then
714 non_unique_con :=TRUE;
715 end if;
716
717 if l_level = 6 and substr(g_context_values.value(context_val_loop),4,3) = '000' then
718 non_unique_con :=TRUE;
719 end if;
720
721 if l_level = 6 and length(rtrim(g_context_values.value(context_val_loop))) <> 11 then
722 non_unique_con :=TRUE;
723 end if;
724
725 if l_level =11 and substr(g_context_values.value(context_val_loop),8,4) = '0000' then
726 non_unique_con :=TRUE;
727 end if;
728
729 if l_level =11 and length(rtrim(g_context_values.value(context_val_loop))) <> 11 then
730 non_unique_con :=TRUE;
731 end if;
732
733 if l_level > 0 and substr(g_context_values.value(context_val_loop),1,2) = '99' then
734 non_unique_con :=TRUE;
735 end if;
736
737 if l_level = 8 and length(rtrim(g_context_values.value(context_val_loop))) <> 8 then
738 non_unique_con :=TRUE;
739 end if;
740 end if;
741
742 /*
743 non_unique_con := pay_archive_chk.jd_code(p_jurisdiction_code =>
744 g_context_values.value(context_val_loop),
745 p_jurisdiction_level => l_level);
746
747 */
748 end if; -- Context Jurisdiction
749 end if; -- Non-Unique False
750
751 if g_context_values.name(context_val_loop) ='JURISDICTION_CODE'
752 and non_unique_con = TRUE then
753 --
754 hr_utility.set_location ('archive_dbi',61);
755 null;
756 else
757 hr_utility.set_location ('archive_dbi',62);
758 --
759 -- OK, the context names match, Set the context.
760 --
761 Pay_balance_pkg.set_context (
762 g_context_values.name(context_val_loop),
766 hr_utility.trace(
763 g_context_values.value(context_val_loop)
764 );
765 hr_utility.set_location ('archive_dbi',63);
767 'Set '||l_contexts_dbi.name(context_ptr)|| ' to '
768 ||g_context_values.value(context_val_loop)
769 );
770 --
771 -- Are all the contexts set for this DBI set?
772 --
773 if l_balance_dbis.context_end(balance_ptr) = context_ptr then
774 --
775 -- Yes, All contexts are set go get it
776 hr_utility.set_location ('archive_dbi',71);
777 -- run user exit to get balance value for
778 -- assignment action
779 result := pay_balance_pkg.get_value(
780 l_balance_dbis.balance_id(balance_ptr),
781 balance_aa
782 );
783 --
784 hr_utility.trace ('** Balance Loop ** '||
785 l_balance_dbis.item_name(balance_ptr) ||
786 ' = ' || result);
787 --
788 -- Archive balance item
789 arch_store ('A_' || l_balance_dbis.item_name(balance_ptr),
790 l_balance_dbis.user_entity_id(balance_ptr),
791 p_assactid,
792 result
793 );
794 --
795 hr_utility.set_location ('archive_dbi',72);
796 --
797 --loop through the contexts for this dbi
798 --
799 for i in l_balance_dbis.context_start(balance_ptr)..
800 l_balance_dbis.context_end(balance_ptr) LOOP
801 --
802 hr_utility.set_location ('archive_dbi',81);
803 if l_contexts_dbi.name(i) = 'ASSIGNMENT_ACTION_ID' then
804 hr_utility.set_location ('archive_dbi',91);
805 null; -- dont store ass_action_id in context table
806 else
807 hr_utility.set_location ('archive_dbi',92);
808 v_context_value
809 := pay_balance_pkg.get_context(l_contexts_dbi.name(i));
810 --
811 select context_id into v_context_id
812 from ff_contexts
813 where context_name= l_contexts_dbi.name(i);
814 --
815 --
816 insert into ff_archive_item_contexts
817 (archive_item_id,sequence_no,context,context_id)
818 VALUES
819 (ff_archive_items_s.currval,
820 1,v_context_value,v_context_id);
821 -- if were setting the jur code add to l_jur_set table
822 if l_contexts_dbi.name(i) = 'JURISDICTION_CODE' then
823 hr_utility.set_location ('archive_dbi',811);
824 l_jur_set(NVL(l_jur_set.last+1,1)):= v_context_value;
825 end if;
826 end if;
827 hr_utility.set_location ('archive_dbi',82);
828 end loop;
829 hr_utility.set_location ('archive_dbi',73);
830 else
831 hr_utility.set_location ('archive_dbi',64);
832 --
833 -- No, settup the next context required.
834 --
835 archive_dbi(balance_ptr, context_ptr + 1,p_assactid);
836 end if;
837 hr_utility.set_location ('archive_dbi',65);
838 end if;
839 hr_utility.set_location ('archive_dbi',42);
840 end if;
841 --
842 hr_utility.set_location ('archive_dbi',32);
843 --
844 end loop;
845 --
846 hr_utility.set_location ('archive_dbi',10);
847 --
848 -- If context_name is JURISDICTION_CODE then clear down the l_jur_set table in prep for
849 -- the next iteration
850 if l_contexts_dbi.name(context_ptr) = 'JURISDICTION_CODE' then
851 l_jur_set.delete;
852 non_unique_con := FALSE;
853 else
854 null;
855 end if;
856 --
857 end archive_dbi;
858 --==================================================================
859 -- ARCHIVE_ASS
860 -- archive details and contexts for non balance dbi's
861 --
862 -- similar to archive_bal but uses run_dbi not get_value
863 -- if jurisdiction_code is a required context for a non-balance
864 -- database item then its level should be set in the local code using
865 -- pay_archive.set_dbi_level
866 --==================================================================
867 procedure archive_ass ( p_ass_ptr number,
868 p_context_ptr number,
869 p_assactid number) is
870 --
871 context_val_loop number;
872 ass_ptr number;
873 context_ptr number;
874 l_level number;
875 v_context_value ff_archive_item_contexts.context%TYPE;
876 v_context_id ff_contexts.context_id%TYPE;
877 result ff_archive_items.value%TYPE;
878 --
879 begin
880 --
881 hr_utility.set_location ('archive_ass',1);
882 --
883 ass_ptr:=p_ass_ptr;
884 context_ptr:=p_context_ptr;
885 --
886 hr_utility.set_location ('archive_ass',2);
887 --
888 -- for the context specified (Tax Unit etc) go get all the
889 -- different values.
890 FOR context_val_loop IN 1..NVL(g_context_values.sz,0) LOOP
891 hr_utility.set_location ('archive_ass',3);
892 if g_context_values.name(context_val_loop)
893 =l_contexts_dbi.name(context_ptr) then
894 non_unique_con :=FALSE;
895 hr_utility.set_location ('archive_ass',41);
896 if g_context_values.name(context_val_loop) = 'JURISDICTION_CODE' and l_jur_set.last is not null then
900 FOR i IN 1..l_jur_set.last LOOP
897 hr_utility.set_location('archive_dbi',5);
898 non_unique_con := FALSE;
899 l_level := NVL(l_assignment_dbis.jur_level(ass_ptr),0);
901 if substr(l_jur_set(i),1,l_level) = substr(g_context_values.value(context_val_loop),1,l_level) then
902 non_unique_con := TRUE;
903 end if;
904 end loop;
905 end if;
906 --
907 if non_unique_con = FALSE then
908 if g_context_values.name(context_val_loop) ='JURISDICTION_CODE' then
909 l_level := NVL(l_assignment_dbis.jur_level(ass_ptr),0);
910
911 /* Specific to US Legislation */
912 if g_leg_code = 'US' then
913 if l_level = 2 and length(rtrim(g_context_values.value(context_val_loop))) <> 11 then
914 non_unique_con :=TRUE;
915 end if;
916
917 if l_level = 6 and substr(g_context_values.value(context_val_loop),4,3) = '000' then
918 non_unique_con :=TRUE;
919 end if;
920
921 if l_level = 6 and length(rtrim(g_context_values.value(context_val_loop))) <> 11 then
922 non_unique_con :=TRUE;
923 end if;
924
925 if l_level =11 and substr(g_context_values.value(context_val_loop),8,4) = '0000' then
926 non_unique_con :=TRUE;
927 end if;
928
929 if l_level =11 and length(rtrim(g_context_values.value(context_val_loop))) <> 11 then
930 non_unique_con :=TRUE;
931 end if;
932
933 if l_level > 0 and substr(g_context_values.value(context_val_loop),1,2) = '99' then
934 non_unique_con :=TRUE;
935 end if;
936
937 if l_level = 8 and length(rtrim(g_context_values.value(context_val_loop))) <> 8 then
938 non_unique_con :=TRUE;
939 end if;
940 end if;
941
942
943 /*
944 non_unique_con := pay_archive_chk.jd_code(p_jurisdiction_code =>
945 g_context_values.value(context_val_loop),
946 p_jurisdiction_level => l_level);
947 */
948
949 end if; /* Context JD */
950 end if; /* non_unique_con = False */
951
952 if g_context_values.name(context_val_loop) ='JURISDICTION_CODE' and non_unique_con=TRUE
953 then
954 hr_utility.set_location('archive_ass',61);
955 null;
956 else
957 hr_utility.set_location ('archive_ass',62);
958 --
959 -- OK, the context names match, Set the context.
960 --
961 Pay_balance_pkg.set_context (
962 g_context_values.name(context_val_loop),
963 g_context_values.value(context_val_loop)
964 );
965 hr_utility.set_location ('archive_ass',63);
966 --
967 -- Are all the contexts set for this DBI set?
968 --
969 if l_assignment_dbis.context_end(ass_ptr) = context_ptr then
970 --
971 -- Yes, All contexts are set go get it
972 hr_utility.set_location ('archive_ass',71);
973 -- run user exit to get balance value for assignment action
974 result := pay_balance_pkg.run_db_item(
975 l_assignment_dbis.item_name(ass_ptr),
976 l_business_group_id,
977 l_legislation_code
978 );
979 hr_utility.trace (
980 '** Assignment Loop ** '
981 || l_assignment_dbis.item_name(ass_ptr) ||
982 ' = ' || result
983 );
984 --
985 -- store data
986 arch_store ('A_' || l_assignment_dbis.item_name(ass_ptr),
987 l_assignment_dbis.user_entity_id(ass_ptr),
988 p_assactid,
989 result );
990 hr_utility.set_location ('archive_ass',72);
991 --
992 --loop through the contexts for this dbi
993 --
994 for i in l_assignment_dbis.context_start(ass_ptr)..
995 l_assignment_dbis.context_end(ass_ptr) LOOP
996 hr_utility.set_location ('archive_ass',81);
997 if l_contexts_dbi.name(i)='ASSIGNMENT_ACTION_ID' then
998 hr_utility.set_location ('archive_ass',91);
999 null; -- dont store ass_action_id in context table
1000 else
1001 hr_utility.set_location ('archive_ass',92);
1002 --
1003 v_context_value:=
1004 pay_balance_pkg.get_context(l_contexts_dbi.name(i));
1005 --
1006 select context_id into v_context_id
1007 from ff_contexts
1008 where context_name=l_contexts_dbi.name(i);
1009 --
1010 --
1011 insert into ff_archive_item_contexts
1012 (archive_item_id,sequence_no,context,context_id)
1013 VALUES
1014 (ff_archive_items_s.currval,1,
1015 v_context_value,v_context_id);
1016
1017 -- if were setting the jur code add to l_jur1_set table
1018 if l_contexts_dbi.name(i) = 'JURISDICTION_CODE' then
1019 l_jur1_set(NVL(l_jur1_set.last+1,1)) := v_context_value;
1020 end if;
1021 --
1022 end if;
1023 hr_utility.set_location ('archive_ass',82);
1024 end loop;
1025 hr_utility.set_location ('archive_ass',73);
1029 --
1026 --
1027 else
1028 hr_utility.set_location ('archive_ass',64);
1030 -- No, settup the next context required.
1031 --
1032 archive_ass(ass_ptr, context_ptr + 1,p_assactid);
1033 end if;
1034 hr_utility.set_location ('archive_ass',65);
1035 end if;
1036 hr_utility.set_location ('archive_ass',42);
1037 end if;
1038 --
1039 hr_utility.set_location ('archive_ass',32);
1040 --
1041 end loop;
1042 --
1043 hr_utility.set_location ('archive_ass',10);
1044 --
1045 --
1046 -- If context_name is JURISDICTION_CODE then clear down the l_jur_set table in prep for
1047 -- the next iteration
1048 if l_contexts_dbi.name(context_ptr) = 'JURISDICTION_CODE' then
1049 l_jur_set.delete;
1050 non_unique_con := FALSE;
1051 else
1052 null;
1053 end if;
1054 --
1055 end archive_ass;
1056 --
1057 --=================================================================
1058 -- PROCESS_CHUNK
1059 --
1060 -- Process each chunk for archiving and archive
1061 -- This is called from the C calling program for each employee
1062 -- within a loop
1063 --==================================================================
1064 PROCEDURE process_chunk(p_payroll_action_id in number,
1065 p_chunk_number in number) IS
1066 BEGIN
1067 hr_utility.set_location ('process_chunk',1);
1068 --
1069 --
1070 -- Call legislative hook to setup up employee contexts
1071 -- Create dynsql to invoke legislative hook
1072 declare
1073 sql_cur number;
1074 ignore number;
1075 archiv_proc varchar2(60);
1076 statem varchar2(256);
1077 pactid number;
1078 begin
1079 pactid := p_payroll_action_id;
1080 select prfm.archive_code
1081 into archiv_proc
1082 from pay_report_format_mappings_f prfm,
1083 pay_payroll_actions ppa
1084 where ppa.payroll_action_id = pactid
1085 and ppa.report_type = prfm.report_type
1086 and ppa.report_category = prfm.report_category
1087 and ppa.effective_date between prfm.effective_start_date
1088 and prfm.effective_end_date
1089 and ppa.report_qualifier = prfm.report_qualifier;
1090 --
1091 --
1092 -- if the archive code does not exist don't do any archiving
1093 if(archiv_proc is null) then
1094 process_archive := FALSE;
1095 else
1096 begin
1097 statem := 'BEGIN '||archiv_proc||'(:pactid, :chunk_number); END;';
1098 --
1099 hr_utility.trace(statem);
1100 hr_utility.set_location ('process_chunk',2);
1101 sql_cur := dbms_sql.open_cursor;
1102 dbms_sql.parse(sql_cur,
1103 statem,
1104 dbms_sql.v7);
1105 dbms_sql.bind_variable(sql_cur, ':pactid', p_payroll_action_id);
1106 dbms_sql.bind_variable(sql_cur, ':chunk_number',
1107 p_chunk_number);
1108 ignore := dbms_sql.execute(sql_cur);
1109 dbms_sql.close_cursor(sql_cur);
1110 --
1111 hr_utility.set_location ('process_chunk',3);
1112 --
1113 exception
1114 when others then
1115 if (dbms_sql.is_open(sql_cur)) then
1116 dbms_sql.close_cursor(sql_cur);
1117 end if;
1118 raise;
1119 end;
1120 end if;
1121 end;
1122 --
1123 end process_chunk;
1124 --
1125 --=================================================================
1126 -- PROCESS_EMPLOYEE
1127 --
1128 -- Process each employee for archiving and archive
1129 -- every balance db item and every assignment db item
1130 -- This is called from the C calling program for each employee
1131 -- within a loop
1132 --==================================================================
1133 PROCEDURE process_employee(p_assact_id in number) IS
1134 result ff_archive_items.value%TYPE;
1135 aactid pay_assignment_actions.assignment_action_id%TYPE;
1136 i INTEGER;
1137 pactid NUMBER;
1138 l_flag BOOLEAN;
1139 BEGIN
1140 hr_utility.set_location ('process_employee',1);
1141 --
1142 --
1143 aactid := p_assact_id;
1144 --
1145 -- clear down the plsql_tale holding the contexts
1146 g_context_values.sz :=0;
1147 g_context_values.name.delete;
1148 g_context_values.value.delete;
1149 -- Call legislative hook to setup up employee contexts
1150 -- Create dynsql to invoke legislative hook
1151 declare
1152 sql_cur number;
1153 ignore number;
1154 archiv_proc varchar2(60);
1155 statem varchar2(256);
1156 pactid number;
1157 begin
1158 pactid := l_payroll_action_id;
1159 select prfm.archive_code
1160 into archiv_proc
1161 from pay_report_format_mappings_f prfm,
1162 pay_payroll_actions ppa
1163 where ppa.payroll_action_id = pactid
1164 and ppa.report_type = prfm.report_type
1168 and ppa.report_qualifier = prfm.report_qualifier;
1165 and ppa.report_category = prfm.report_category
1166 and ppa.effective_date between prfm.effective_start_date
1167 and prfm.effective_end_date
1169 --
1170 -- Set the assignment action id that the balances will be retrieved
1171 -- as of. This can be overriden by the legislative code
1172 --
1173 balance_aa := aactid;
1174 archive_aa := aactid;
1175 --
1176 -- if the archive code does not exist don't do any archiving
1177 if(archiv_proc is null) then
1178 process_archive := FALSE;
1179 /*
1180 hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1181 hr_utility.raise_error;
1182 */
1183 else
1184 --
1185 begin
1186 statem := 'BEGIN '||archiv_proc||'(:aactid, :l_effective_date); END;';
1187 --
1188 hr_utility.trace(statem);
1189 hr_utility.set_location ('process_employee',2);
1190 sql_cur := dbms_sql.open_cursor;
1191 dbms_sql.parse(sql_cur,
1192 statem,
1193 dbms_sql.v7);
1194 dbms_sql.bind_variable(sql_cur, ':aactid', aactid);
1195 dbms_sql.bind_variable(sql_cur, ':l_effective_date',
1196 l_effective_date);
1197 ignore := dbms_sql.execute(sql_cur);
1198 dbms_sql.close_cursor(sql_cur);
1199 --
1200 hr_utility.set_location ('process_employee',3);
1201 --
1202 exception
1203 when others then
1204 if (dbms_sql.is_open(sql_cur)) then
1205 dbms_sql.close_cursor(sql_cur);
1206 end if;
1207 raise;
1208 end;
1209 end if;
1210 end;
1211 --
1212 --------------------------------------------------------------------
1213 -- Create entries in g_context_values table if needed
1214 --------------------------------------------------------------------
1215 --
1216 /* Only process the archiver if process_archive is
1217 set
1218 */
1219 if (process_archive = TRUE) then
1220 For i in 1..l_contexts_dbi.sz LOOP
1221 l_flag := FALSE;
1222 For j in 1..g_context_values.sz LOOP
1223 If g_context_values.name(j) = l_contexts_dbi.name(i) then
1224 l_flag := TRUE;
1225 end if;
1226 --
1227 end loop;
1228 --
1229 if l_flag = FALSE then
1230 g_context_values.sz := g_context_values.sz + 1;
1231 g_context_values.name(g_context_values.sz) :=
1232 l_contexts_dbi.name(i);
1233 g_context_values.value(g_context_values.sz) :=
1234 pay_balance_pkg.get_context(l_contexts_dbi.name(i));
1235 end if;
1236 --
1237 end loop;
1238 --
1239 ---------------------------------------------------------------------
1240 -- Balance Loop
1241 ---------------------------------------------------------------------
1242 FOR i IN 1..l_balance_dbis.sz LOOP
1243 hr_utility.set_location ('process_employee',4);
1244 --
1245 archive_dbi(i,l_balance_dbis.context_start(i),p_assact_id);
1246 --
1247 hr_utility.set_location ('process_employee',5);
1248 --
1249 END LOOP;
1250 ---------------------------------------------------------------------
1251 -- Assignment Loop
1252 ---------------------------------------------------------------------
1253 FOR i IN 1..l_assignment_dbis.sz LOOP
1254 hr_utility.set_location ('process_employee',6);
1255 --
1256 archive_ass(i,l_assignment_dbis.context_start(i),p_assact_id);
1257 --
1258 hr_utility.set_location ('process_employee',7);
1259 END LOOP;
1260 ---------------------------------------------------------------------
1261 hr_utility.set_location ('process_employee',8);
1262 ---------------------------------------------------------------------
1263 --
1264 end if;
1265 --
1266 end process_employee;
1267 --
1268 --
1269 --=================================================================
1270 -- remove_report_actions
1271 --
1272 -- This procedure deletes actions from the database, this
1273 -- should only be used with report actions.
1274 --==================================================================
1275 procedure remove_report_actions(p_pact_id in number,
1276 p_chunk_no in number default null)
1277 is
1278 --
1279 type t_aa_list is table of pay_assignment_actions.assignment_action_id%type;
1280 aalist t_aa_list;
1281
1282 type t_obj_list is table of pay_temp_object_actions.object_action_id%type;
1283 objlist t_obj_list;
1284
1285 i number;
1286 --
1287 cursor asgcur (p_payroll_act in number)
1288 is
1289 select assignment_action_id
1290 from pay_assignment_actions
1291 where payroll_action_id = p_payroll_act;
1292 --
1293 cursor objcur (p_payroll_act in number)
1294 is
1295 select object_action_id
1296 from pay_temp_object_actions
1297 where payroll_action_id = p_payroll_act;
1298 --
1299 cursor asgrescur (p_payroll_act in number, p_chunk in number)
1300 is
1301 select assignment_action_id
1302 from pay_assignment_actions
1306 begin
1303 where payroll_action_id = p_payroll_act
1304 and chunk_number = p_chunk;
1305 --
1307 --
1308 if (p_chunk_no is null) then
1309 --
1310 open asgcur(p_pact_id);
1311 loop
1312 fetch asgcur bulk collect into aalist limit 1000;
1313 --
1314 forall i in 1..aalist.count
1315 delete from pay_action_interlocks
1316 where locking_action_id = aalist(i);
1317 --
1318 forall i in 1..aalist.count
1319 delete from PAY_MESSAGE_LINES
1320 where source_id = aalist(i)
1321 and source_type = 'A';
1322 --
1323 forall i in 1..aalist.count
1324 delete from pay_assignment_actions
1325 where assignment_action_id = aalist(i);
1326 --
1327 exit when asgcur%notfound;
1328 end loop;
1329 close asgcur;
1330 --
1331 open objcur(p_pact_id);
1332 loop
1333 fetch objcur bulk collect into objlist limit 1000;
1334 --
1335 forall i in 1..objlist.count
1336 delete from PAY_MESSAGE_LINES
1337 where source_id = objlist(i)
1338 and source_type = 'A';
1339 --
1340 forall i in 1..objlist.count
1341 delete from pay_temp_object_actions
1342 where object_action_id = objlist(i);
1343 --
1344 exit when objcur%notfound;
1345 end loop;
1346 close objcur;
1347 --
1348 delete from PAY_MESSAGE_LINES
1349 where source_id = p_pact_id
1350 and source_type = 'P';
1351 --
1352 delete from pay_population_ranges
1353 where payroll_action_id = p_pact_id;
1354 --
1355 delete from pay_payroll_actions
1356 where payroll_action_id = p_pact_id;
1357 else
1358 --
1359 open asgrescur(p_pact_id, p_chunk_no);
1360 loop
1361 fetch asgrescur bulk collect into aalist limit 1000;
1362 --
1363 forall i in 1..aalist.count
1364 delete from pay_action_interlocks
1365 where locking_action_id = aalist(i);
1366 --
1367 forall i in 1..aalist.count
1368 delete from PAY_MESSAGE_LINES
1369 where source_id = aalist(i)
1370 and source_type = 'A';
1371 --
1372 exit when asgrescur%notfound;
1373 end loop;
1374 close asgrescur;
1375 --
1376 delete from pay_assignment_actions
1377 where payroll_action_id = p_pact_id
1378 and chunk_number = p_chunk_no;
1379 --
1380 end if;
1381 --
1382 end remove_report_actions;
1383 --
1384 /* Name : standard_deinit
1385 Purpose : This procedure is the standard dinitialisation for some archiver
1386 processes. It simply removes all the actions processed in this run
1387 Arguments :
1388 Notes :
1389 */
1390 procedure standard_deinit (pactid in number)
1391 is
1392 remove_act varchar2(10);
1393 begin
1394 --
1395 select pay_core_utils.get_parameter('REMOVE_ACT',
1396 pa1.legislative_parameters)
1397 into remove_act
1398 from pay_payroll_actions pa1
1399 where pa1.payroll_action_id = pactid;
1400 --
1401 if (remove_act is null or remove_act = 'Y') then
1402 pay_archive.remove_report_actions(pactid);
1403 end if;
1404 --
1405 end standard_deinit;
1406 --
1407 end pay_archive;
[Home] [Help]