[Home] [Help]
PACKAGE BODY: APPS.PAY_MAGTAPE_EXTRACT
Source
1 PACKAGE BODY pay_magtape_extract AS
2 -- $Header: pymagext.pkb 120.1 2005/10/10 16:54:29 meshah noship $
3 --
4 -- Copyright (c) Oracle Corporation 1995 All rights reserved
5 /*
6 PRODUCT
7 Oracle*Payroll
8 --
9 NAME
10 pymagext.pkb - PL/SQL Balance User Exit
11 --
12 DESCRIPTION
13 This package contains the procedures to process a magtape payroll action
14 and create archive items for any archive database items contained in the
15 magtape report format. The rollback routine will rollback these items
16 before rolling back the payroll action itself.
17 --
18 MODIFIED (DD-MON-YYYY)
19 Name Vers Date Notes
20 --------- ----- ----------- --------------------------------------------------
21 cadams 40.0 12-Feb-1996 Created
22 cadams 40.1 13-Mar-1996 Changed report output from dbms_output to
23 PAY_MESSAGE_LINES and added code to commit
24 after every CHUNK_SIZE employers/employees
25 cadams 40.2 15-Mar-1996 Added explicit deletion of assignment actions
26 to rollback code
27 ramurthy 40.3 17-Apr-1996 Made some changes to the way the Archive
28 Differences Report is formatted.
29 allee 40.4 17-Apr-1996 Modified l_state(2) -> l_state(10) so that
30 it could handle the Pseudo-State = 'FED'
31 for Federal W2 Reporting;
32 bhoman 40.6 17-APR-1998 Changes made to support SQWL diskette reporting.
33 achauhan 40.7 08-aug-1998 Commented out the insert into ff_archive_item_contexts
34 The table definition of ff_archive_item_contexts has
35 changed and the old archive process does not need to
36 populate this table since there is only one contxt for the routes
37 djoshi 40.8 08-Apr-1999 Verfied for Canonical Complience of Date
38 mreid 115.5 30-Nov-1999 Added column names to ff_archive_items insert
39 alogue 115.6 15-Feb-2000 Utf8 support : varchar_240_tbl required for item_name.
40 mreid 115.7 13-Sep-2001 Added column name to all inserts
41 rsirigir 115.8 13-Aug-2002 Bug 2484696, included dbdrv commands to conform to
42 GSCC compliance
43 meshah 115.9 10-Oct-2005 Added nocopy to the out parameters
44
45 */
46 --==============================================================================
47 -- TYPES
48 --
49 -- The table types are just simple tables or various types. The records are
50 -- composite types of tables that contain a size (sz) to hold the number of
51 -- data items currently stored in the table. Data items are stored in the
52 -- tables within the records contiguously from 1 to sz.
53 --==============================================================================
54 TYPE number_tbl IS TABLE OF NUMBER INDEX BY binary_integer;
55 TYPE varchar_60_tbl IS TABLE OF VARCHAR(60) INDEX BY binary_integer;
56 TYPE varchar_240_tbl IS TABLE OF VARCHAR(2400) INDEX BY binary_integer;
57 TYPE varchar_1_tbl IS TABLE OF VARCHAR(1) INDEX BY binary_integer;
58 --
59 TYPE archive_items IS RECORD
60 (
61 item_name varchar_240_tbl,
62 user_entity_id number_tbl,
63 data_type varchar_1_tbl,
64 sz INTEGER
65 );
66 --
67 TYPE balances IS RECORD
68 (
69 item_name varchar_240_tbl,
70 user_entity_id number_tbl,
71 balance_id number_tbl,
72 sz INTEGER
73 );
74 --
75 --==============================================================================
76 -- VARIABLES
77 --==============================================================================
78 --------------------------------------------------------------------------------
79 -- Table variables
80 --------------------------------------------------------------------------------
81 l_balance_dbis balances;
82 l_employer_dbis archive_items;
83 l_assignment_dbis archive_items;
84 --------------------------------------------------------------------------------
85 -- Other variables
86 --
87 -- l_payroll_action_id Global payroll action id
88 -- l_business_group_id Global business group id
89 -- l_legislation_code Global legislation code
90 -- l_effective_date Global effective date
91 -- l_date_earned Global date earned
92 -- l_runmode Global run mode ('S'tore, 'R'eport)
93 -- l_transmitter_tax_unit_id Global transmitter tax unit id
94 -- l_report_format Global report format
95 -- l_report_type Report type
96 -- l_media_type SQWLD - 'PD', or 'RT'
97 -- l_state State code (eg 'CA')
98 -- l_last_context Last context to be printed in report
99 -- l_next_header Next header to be printed in report
100 --------------------------------------------------------------------------------
101 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
102 l_business_group_id pay_payroll_actions.business_group_id%TYPE;
103 l_legislation_code per_business_groups.legislation_code%TYPE;
104 l_effective_date pay_payroll_actions.effective_date%TYPE;
105 l_date_earned pay_payroll_actions.date_earned%TYPE;
106 l_runmode VARCHAR2(1);
107 l_transmitter_tax_unit_id NUMBER;
108 l_report_format VARCHAR2(32);
109 l_report_type VARCHAR2(32);
110 -- SQWLD - track media_type ('PC Diskette', 'Reel Tape'), media value ('D', 'M')
111 l_media_type VARCHAR2(32);
112 l_state VARCHAR2(10);
113 l_last_context NUMBER := NULL;
114 l_next_header VARCHAR2(1);
115 l_chunk_size NUMBER;
116 --
117 --==============================================================================
118 -- PROCEDURES
119 --==============================================================================
120 --
121 --==============================================================================
122 -- ARCH_INITIALISE
123 --
124 -- Initialse tables and referance variables.
125 --
126 -- jurisdiction_code Jurisdiction code
127 -- legislative_parameters Legislative parameters
128 --==============================================================================
129 PROCEDURE arch_initialise (p_payroll_action_id NUMBER) IS
130 jurisdiction_code pay_state_rules.jurisdiction_code%TYPE;
131 legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
132 BEGIN
133 hr_utility.set_location ('arch_initialise',1);
134 --
135 l_payroll_action_id := p_payroll_action_id;
136 --------------------------------------------------------------------------------
137 -- Initialise table sizes
138 --------------------------------------------------------------------------------
139 l_balance_dbis.sz := 0;
140 l_employer_dbis.sz := 0;
141 l_assignment_dbis.sz := 0;
142 --
143 hr_utility.set_location ('arch_initialise',2);
144 --------------------------------------------------------------------------------
145 -- Get business_group and legislation_code
146 --------------------------------------------------------------------------------
147 SELECT pa.business_group_id,
148 bg.legislation_code,
149 pa.effective_date,
150 pa.date_earned,
151 pa.legislative_parameters
152 INTO l_business_group_id,
153 l_legislation_code,
154 l_effective_date,
155 l_date_earned,
156 legislative_parameters
157 FROM pay_payroll_actions pa,
158 per_business_groups bg
159 WHERE pa.payroll_action_id = l_payroll_action_id AND
160 pa.business_group_id = bg.business_group_id;
161 --
162 hr_utility.set_location ('arch_initialise',3);
163 --------------------------------------------------------------------------------
164 -- Set session date from effective date
165 --------------------------------------------------------------------------------
166 DELETE FROM fnd_sessions WHERE session_id = userenv('SESSIONID');
167 INSERT INTO fnd_sessions (session_id, effective_date)
168 VALUES ( userenv('SESSIONID'),l_effective_date );
169 --
170 hr_utility.set_location ('arch_initialise',4);
171 --------------------------------------------------------------------------------
172 -- Derive state, transmitter tax unit and report format from legislative params
173 --------------------------------------------------------------------------------
174 l_report_type := LTRIM (SUBSTR(legislative_parameters,11,5),' ');
175 l_state := LTRIM (SUBSTR(legislative_parameters,17,5),' ');
176 l_transmitter_tax_unit_id := fnd_number.canonical_to_number (SUBSTR(legislative_parameters,
177 23,5));
178 --
179 /* SQWLD - replace this select from pay_report_format_mappings with lookup_format,
180 parse media type from leg params
181
182 ORIGINAL CODE (before SQWLD):
183
184 SELECT report_format
185 INTO l_report_format
186 FROM pay_report_format_mappings
187 WHERE report_type = l_report_type AND
188 report_qualifier = l_state AND
189 l_effective_date BETWEEN effective_start_date AND
190 effective_end_date;
191
192 NEW SQWLD CODE FOLLOWS:
193 */
194 -- parse media_value from leg params
195 l_media_type := ltrim(substr(legislative_parameters, 29,5));
196 -- now we can call lookup_format
197 l_report_format := pay_us_magtape_reporting.lookup_format(l_effective_date,
198 l_report_type,
199 l_state,
200 l_media_type);
201 -- end new SQWLD code
202 --
203 hr_utility.set_location ('arch_initialise',5);
204 --------------------------------------------------------------------------------
205 -- Get jurisdiction code and store as a context
206 --------------------------------------------------------------------------------
207 IF l_state <> 'FED' THEN
208 SELECT sr.jurisdiction_code
209 INTO jurisdiction_code
210 FROM pay_state_rules sr
211 WHERE sr.state_code = l_state;
212 --
213 pay_balance_pkg.set_context ('JURISDICTION_CODE',jurisdiction_code);
214 END IF;
215 --
216 hr_utility.set_location ('arch_initialise',6);
217 --------------------------------------------------------------------------------
218 -- Get CHUNK_SIZE or default to 20 if CHUNK_SIZE does not exist
219 --------------------------------------------------------------------------------
220 BEGIN
221 SELECT parameter_value
222 INTO l_chunk_size
223 FROM pay_action_parameters
224 WHERE parameter_name = 'CHUNK_SIZE';
225 EXCEPTION
226 WHEN no_data_found THEN
227 l_chunk_size := 20;
228 END;
229 --
230 hr_utility.set_location ('arch_initialise',7);
231 END arch_initialise;
232 --
233 --==============================================================================
234 -- ARCH_DB_ITEMS_LOOP
235 --
236 -- Loop through db items and store into the three tables
237 --
238 -- db_items_csr Database items cursor for a report format
239 -- contexts_csr Contexts cursor for a user entity id
240 --
241 -- db_items_row Row record for cursor
242 -- contexts_row Row record for cursor
243 -- user_entity_id Current User entity id
244 -- a_user_entity_id Archive User entity id
245 -- a_data_type Archive data type
246 -- creator_type Creator type
247 -- name Database item name without prefix
248 -- flag Flag to denote which table data to be stored in
249 --==============================================================================
250 PROCEDURE arch_db_items_loop IS
251 CURSOR db_items_csr (p_report_format VARCHAR2) IS
252 SELECT distinct us.item_name
253 FROM pay_magnetic_blocks mb,
254 pay_magnetic_records mr,
255 ff_fdi_usages_f us
256 WHERE mb.report_format = p_report_format AND
257 mb.magnetic_block_id = mr.magnetic_block_id AND
258 mr.formula_id = us.formula_id AND
259 us.usage = 'D';
260 --
261 CURSOR contexts_csr (p_user_entity_id VARCHAR2) IS
262 SELECT cx.context_name
263 FROM ff_user_entities ue,
264 ff_route_context_usages rcu,
265 ff_contexts cx
266 WHERE ue.user_entity_id = p_user_entity_id AND
267 ue.route_id = rcu.route_id AND
268 rcu.context_id = cx.context_id;
269 --
270 db_items_row db_items_csr%ROWTYPE;
271 contexts_row contexts_csr%ROWTYPE;
272 user_entity_id ff_database_items.user_entity_id%TYPE;
273 a_user_entity_id ff_database_items.user_entity_id%TYPE;
274 a_data_type ff_database_items.data_type%TYPE;
275 creator_type ff_user_entities.creator_type%TYPE;
276 name VARCHAR2(240);
277 flag VARCHAR2(1);
278 BEGIN
279 hr_utility.set_location ('arch_db_items_loop',1);
280 --
281 --------------------------------------------------------------------------------
282 -- Loop through database items
283 --------------------------------------------------------------------------------
284 FOR db_items_row IN db_items_csr (l_report_format) LOOP
285 hr_utility.set_location ('arch_db_items_loop',11);
286 --------------------------------------------------------------------------------
287 -- Ignore any database item found without a A_ prefix
288 --------------------------------------------------------------------------------
289 IF substr(db_items_row.item_name,1,2) <> 'A_' THEN
290 hr_utility.trace ('** Ignoring DB Item without A_ prefix: ' ||
291 db_items_row.item_name || ' **');
292 ELSE
293 hr_utility.set_location ('arch_db_items_loop',111);
294 --------------------------------------------------------------------------------
295 -- Default to an employer database item
296 --------------------------------------------------------------------------------
297 flag := 'R';
298 name := substr (db_items_row.item_name,3,
299 length(db_items_row.item_name)-2);
300 --
301 hr_utility.trace ('** Found ' || db_items_row.item_name || ' **');
302 hr_utility.set_location ('arch_db_items_loop',112);
303 --------------------------------------------------------------------------------
304 -- Get archive entity id and data type
305 --------------------------------------------------------------------------------
306 SELECT dbi.user_entity_id, dbi.data_type
307 INTO a_user_entity_id, a_data_type
308 FROM ff_database_items dbi
309 WHERE dbi.user_name = db_items_row.item_name;
310 --
311 --------------------------------------------------------------------------------
312 -- Get live entity id and creator type
313 --------------------------------------------------------------------------------
314 SELECT dbi.user_entity_id,ue.creator_type
315 INTO user_entity_id,creator_type
316 FROM ff_database_items dbi,
317 ff_user_entities ue
318 WHERE dbi.user_name = name AND
319 dbi.user_entity_id = ue.user_entity_id;
320 --
321 --------------------------------------------------------------------------------
322 -- Check to see if db_item is balance or assignment
323 --------------------------------------------------------------------------------
324 IF creator_type = 'B' THEN
325 flag := 'B';
326 ELSE
330 flag := 'A';
327 FOR contexts_row IN contexts_csr (user_entity_id) LOOP
328 IF (contexts_row.context_name = 'ASSIGNMENT_ID') OR
329 (contexts_row.context_name = 'ASSIGNMENT_ACTION_ID') THEN
331 EXIT;
332 END IF;
333 END LOOP;
334 END IF;
335 --
336 hr_utility.set_location ('arch_db_items_loop',113);
337 --------------------------------------------------------------------------------
338 -- Store data in appropriate table
339 --------------------------------------------------------------------------------
340 IF flag = 'B' THEN
341 hr_utility.set_location ('arch_db_items_loop',1131);
342 --
343 l_balance_dbis.sz := l_balance_dbis.sz + 1;
344 l_balance_dbis.item_name(l_balance_dbis.sz) := name;
345 l_balance_dbis.user_entity_id(l_balance_dbis.sz) := a_user_entity_id;
346 l_balance_dbis.balance_id(l_balance_dbis.sz) :=
347 pay_us_magtape_reporting.bal_db_item (l_balance_dbis.item_name
348 (l_balance_dbis.sz));
349 ELSIF flag = 'A' THEN
350 hr_utility.set_location ('arch_db_items_loop',1132);
351 --
352 l_assignment_dbis.sz := l_assignment_dbis.sz + 1;
353 l_assignment_dbis.item_name(l_assignment_dbis.sz) := name;
354 l_assignment_dbis.user_entity_id(l_assignment_dbis.sz) :=
355 a_user_entity_id;
356 l_assignment_dbis.data_type(l_assignment_dbis.sz) := a_data_type;
357 ELSE
358 hr_utility.set_location ('arch_db_items_loop',1133);
359 --
360 l_employer_dbis.sz := l_employer_dbis.sz + 1;
361 l_employer_dbis.item_name(l_employer_dbis.sz) := name;
362 l_employer_dbis.user_entity_id(l_employer_dbis.sz) :=
363 a_user_entity_id;
364 l_employer_dbis.data_type(l_employer_dbis.sz) := a_data_type;
365 END IF;
366 END IF;
367 --
368 hr_utility.set_location ('arch_db_items_loop',12);
369 END LOOP;
370 --
371 hr_utility.set_location ('arch_db_items_loop',2);
372 END arch_db_items_loop;
373 --
374 --==============================================================================
375 -- ARCH_OUTPUT
376 --
377 -- Output a report line to pay_message_lines
378 --==============================================================================
379 PROCEDURE arch_output (p_line VARCHAR2) IS
380 BEGIN
381 hr_utility.set_location ('arch_output',1);
382 --
383 INSERT INTO pay_message_lines
384 ( line_sequence, payroll_id, message_level,
385 source_id, source_type, line_text )
386 VALUES
387 ( pay_message_lines_s.nextval,
388 NULL,
389 'I',
390 l_payroll_action_id,
391 'P',
392 p_line );
393 --
394 hr_utility.set_location ('arch_output',2);
395 END arch_output;
396 --
397 --==============================================================================
398 -- ARCH_REPORT_HEADER
399 --
400 -- Print report headers/footer depending on report section
401 --
402 -- now Current date/time in text form
403 -- title Centred title string
404 --==============================================================================
405 PROCEDURE arch_report_header (p_report_section VARCHAR2 := NULL) IS
406 now VARCHAR2(30);
407 title VARCHAR2(100);
408 period pay_w2_magnetic_tape_reports.period%TYPE;
409 --
410 --------------------------------------------------------------------------------
411 -- CENTRE
412 --
413 -- Return a string centred on a page of l width
414 --------------------------------------------------------------------------------
415 FUNCTION centre (s VARCHAR2,l INTEGER) RETURN VARCHAR2 IS
416 BEGIN
417 RETURN LPAD(RPAD(s,LENGTH(s) + ((l - LENGTH(s))/2)), l);
418 END centre;
419 --
420 BEGIN
421 hr_utility.set_location ('arch_report_header',1);
422 --
423 IF l_runmode = 'R' THEN
424 hr_utility.set_location ('arch_report_header',11);
425 --------------------------------------------------------------------------------
426 -- Print employee report header
427 --------------------------------------------------------------------------------
428 IF p_report_section = 'E' THEN
429 hr_utility.set_location ('arch_report_header',111);
430 --
431 arch_output ('');
432 arch_output ('Last Name ' ||
433 'SS Number ' ||
434 'Item Name ' ||
435 'Current Val ' ||
436 'Archive Val ');
437 arch_output ('-------------------- ' ||
438 '----------- ' ||
439 '------------------------------------------ ' ||
440 '----------- ' ||
441 '------------');
442 --------------------------------------------------------------------------------
443 -- Print employer report header
444 --------------------------------------------------------------------------------
445 ELSIF p_report_section = 'R' THEN
446 hr_utility.set_location ('arch_report_header',112);
447 --
448 arch_output ('');
449 arch_output ('Organization Name ' ||
450 ' ' ||
451 'Item Name ' ||
452 'Calculated Value ' ||
453 'Archive Value ');
454 arch_output ('------------------------------ ' ||
455 ' ' ||
456 '----------------------------------- ' ||
457 '-------------------- ' ||
458 '--------------------');
459 --------------------------------------------------------------------------------
460 -- Print report footer
461 --------------------------------------------------------------------------------
462 ELSIF p_report_section = 'F' THEN
463 hr_utility.set_location ('arch_report_header',113);
464 --
465 arch_output ('');
466 arch_output (centre('** End of Listing **',80));
467 --------------------------------------------------------------------------------
468 -- Print report main header
469 --------------------------------------------------------------------------------
470 ELSE
471 hr_utility.set_location ('arch_report_header',114);
472 --
473 SELECT period
474 INTO period
475 FROM pay_w2_magnetic_tape_reports
476 WHERE payroll_action_id = l_payroll_action_id;
477 --
478 hr_utility.set_location ('arch_report_header',115);
479 --
480 now := fnd_date.date_to_canonical (SYSDATE);
481 title := centre ('ARCHIVE DIFFERENCES REPORT',100);
482 --
483 arch_output (title || LPAD(now,100-LENGTH(title)));
484 arch_output (centre('==========================',100));
485 arch_output ('');
486 arch_output ('Payroll Action ID: ' || l_payroll_action_id);
487 arch_output ('Effective Date: ' || fnd_date.date_to_canonical(l_effective_date
488 ));
489 arch_output ('Report Type: ' || l_report_type);
490 arch_output ('State: ' || l_state);
491 arch_output ('Period: ' || period);
492 --
493 hr_utility.set_location ('arch_report_header',116);
494 END IF;
495 --
496 hr_utility.set_location ('arch_report_header',12);
497 END IF;
498 --
499 hr_utility.set_location ('arch_report_header',2);
500 END arch_report_header;
501 --
502 --==============================================================================
503 -- ARCH_STORE
504 --
505 -- Store the data to the archive tables or print report lines depending on
506 -- l_runmode
507 --
508 -- chk Currently stored archive value
509 --==============================================================================
510 PROCEDURE arch_store (p_report_section VARCHAR2,
511 p_item_name VARCHAR2,
512 p_user_entity_id ff_archive_items.user_entity_id%TYPE,
513 p_context1 ff_archive_items.context1%TYPE,
514 p_value ff_archive_items.value%TYPE,
515 p_context2 ff_archive_item_contexts.context%TYPE
516 := NULL) IS
517 --
518 chk ff_archive_items.value%TYPE;
519 --
520 --------------------------------------------------------------------------------
521 -- ARCH_REPORT_INFO
522 --
523 -- Get currently stored archive value for p_item_name with p_context1
524 --------------------------------------------------------------------------------
525 FUNCTION arch_report_info (p_item_name VARCHAR2,
526 p_context1 NUMBER) RETURN VARCHAR2 IS
527 r VARCHAR2(80);
528 BEGIN
529 SELECT ai.value
530 INTO r
531 FROM ff_database_items dbi,
532 ff_archive_items ai
533 WHERE dbi.user_entity_id = ai.user_entity_id AND
534 dbi.user_name = p_item_name AND
535 ai.context1 = p_context1;
536 --
537 RETURN r;
538 EXCEPTION
539 WHEN no_data_found THEN
540 RETURN '** NOT FOUND **';
541 END arch_report_info;
542 --
543 --------------------------------------------------------------------------------
544 -- ARCH_REPORT_INFO
545 --
546 -- Get organisation name for p_context2
547 --------------------------------------------------------------------------------
548 FUNCTION arch_report_info (p_context2 NUMBER) RETURN VARCHAR2 IS
549 r VARCHAR2(80);
550 BEGIN
551 SELECT name
552 INTO r
553 FROM hr_organization_units
554 WHERE organization_id = p_context2;
555 --
556 RETURN r;
557 EXCEPTION
558 WHEN no_data_found THEN
559 RETURN '** NOT FOUND **';
560 END arch_report_info;
561 --
562 --------------------------------------------------------------------------------
563 -- ARCH_REPORT_LINE
564 --
565 -- Print a line on the report adding heading lines as necissary
566 --------------------------------------------------------------------------------
567 PROCEDURE arch_report_line (p_item_name VARCHAR2,
568 p_calc_val VARCHAR2,
569 p_arch_val VARCHAR2) IS
570 BEGIN
571 hr_utility.set_location ('arch_report_line',1);
572 --------------------------------------------------------------------------------
573 -- If it is the start of a new person/organisation...
574 --------------------------------------------------------------------------------
575 IF (l_last_context <> p_context1) OR (l_last_context IS NULL) THEN
576 hr_utility.set_location ('arch_report_line',11);
577 --------------------------------------------------------------------------------
578 -- Add a space between the last person/organisation and the new one
579 --------------------------------------------------------------------------------
580 IF l_last_context IS NOT NULL THEN
581 hr_utility.set_location ('arch_report_line',111);
582 arch_output ('');
583 END IF;
584 --
585 l_last_context := p_context1;
586 --
587 hr_utility.set_location ('arch_report_line',12);
588 --------------------------------------------------------------------------------
589 -- Print new report header if section has changed
590 --------------------------------------------------------------------------------
591 IF (l_next_header = 'R') OR (l_next_header = 'E') THEN
592 hr_utility.set_location ('arch_report_line',121);
593 arch_report_header (l_next_header);
594 l_next_header := NULL;
595 END IF;
596 --
597 hr_utility.set_location ('arch_report_line',13);
598 --------------------------------------------------------------------------------
599 -- Print person/organisation details
600 --------------------------------------------------------------------------------
601 IF p_report_section = 'E' THEN
602 hr_utility.set_location ('arch_report_line',131);
603 --
604 arch_output (RPAD(arch_report_info('A_PER_LAST_NAME',
605 p_context1),20) || ' ' ||
606 RPAD(arch_report_info(
607 'A_PER_NATIONAL_IDENTIFIER',p_context1),11));
608 ELSE
609 hr_utility.set_location ('arch_report_line',132);
610 --
611 arch_output (RPAD(arch_report_info(p_context2),11));
612 END IF;
613 --
614 hr_utility.set_location ('arch_report_line',14);
615 END IF;
616 --
617 hr_utility.set_location ('arch_report_line',2);
618 --------------------------------------------------------------------------------
619 -- Print actual report line
620 --------------------------------------------------------------------------------
621 arch_output (' ' ||
622 ' ' ||
623 RPAD(replace(substr(p_item_name,3,42),'_', ' '),42) || ' ' ||
624 RPAD(NVL(p_calc_val,'** NULL **'),11) || ' ' ||
625 RPAD(NVL(p_arch_val,'** NULL **'),12));
626 --
627 hr_utility.set_location ('arch_report_line',3);
628 END arch_report_line;
629 --------------------------------------------------------------------------------
630 BEGIN
631 hr_utility.set_location ('arch_store',1);
632 --------------------------------------------------------------------------------
633 -- If in Validate mode, don't do anything
634 --------------------------------------------------------------------------------
635 IF l_runmode = 'V' THEN
636 hr_utility.set_location ('arch_store',111);
637 --------------------------------------------------------------------------------
638 -- If in Store mode, store the data into the archive tables
639 --------------------------------------------------------------------------------
640 ELSIF l_runmode = 'S' THEN
641 hr_utility.set_location ('arch_store',121);
642 --
643 INSERT INTO ff_archive_items
644 ( ARCHIVE_ITEM_ID, USER_ENTITY_ID, CONTEXT1, VALUE)
645 VALUES
646 ( ff_archive_items_s.nextval,p_user_entity_id,p_context1,p_value );
647 --
648 hr_utility.set_location ('arch_store',122);
649 --
650 /* context2 is not supported in the old archive process. So, commenting this insert */
651 IF p_context2 IS NOT NULL THEN
652 -- INSERT INTO ff_archive_item_contexts VALUES
653 -- ( ff_archive_items_s.currval,2,p_context2 );
654 --
655 hr_utility.set_location ('arch_store',123);
656 END IF;
657 --------------------------------------------------------------------------------
658 -- Otherwise, you're in Report mode so compare the data with the archive and
659 -- report any differences
660 --------------------------------------------------------------------------------
661 ELSE
662 hr_utility.set_location ('arch_store',131);
663 --
664 BEGIN
665 hr_utility.set_location ('arch_store',132);
666 --
667 --------------------------------------------------------------------------------
668 -- Get archived value
669 --------------------------------------------------------------------------------
670 IF p_context2 IS NOT NULL THEN
671 hr_utility.set_location ('arch_store',1321);
672 --
673 SELECT value
674 INTO chk
675 FROM ff_archive_items ai,
676 ff_archive_item_contexts aic
677 WHERE ai.archive_item_id = aic.archive_item_id AND
678 ai.user_entity_id = p_user_entity_id AND
679 ai.context1 = p_context1 AND
680 aic.sequence_no = 2 AND
681 aic.context = p_context2;
682 ELSE
683 hr_utility.set_location ('arch_store',1322);
684 --
685 SELECT value
686 INTO chk
687 FROM ff_archive_items ai
688 WHERE ai.user_entity_id = p_user_entity_id AND
689 ai.context1 = p_context1;
690 END IF;
691 --
692 hr_utility.set_location ('arch_store',133);
693 --------------------------------------------------------------------------------
694 -- If there is a differance print a line on the report
695 --------------------------------------------------------------------------------
696 IF chk <> p_value THEN
697 hr_utility.set_location ('arch_store',1331);
698 --
699 arch_report_line (p_item_name,p_value,chk);
700 END IF;
701 --
702 hr_utility.set_location ('arch_store',134);
703 EXCEPTION
704 --------------------------------------------------------------------------------
705 -- Catch for when there is no corresponding data in the archive table for a
706 -- calculated value
707 --------------------------------------------------------------------------------
708 WHEN no_data_found THEN
709 hr_utility.set_location ('arch_store',14);
710 --
711 arch_report_line (p_item_name,p_value,'** NOT FOUND **');
712 END;
713 --
714 hr_utility.set_location ('arch_store',2);
715 END IF;
716 END arch_store;
717 --
718 --==============================================================================
719 -- ARCH_EMPLOYER_LOOP
720 --
721 -- Loop through the employers and for each employer, insert one archive item
722 -- for every employer db item.
723 --
724 -- employer_csr Employer cursor to select employer tax unit id by payroll
725 -- action id while UNIONing the transmitter tax unit id passed
726 -- in
727 -- employer_row Row for the cursor
728 -- result Result of database item user callback
729 -- i Loop variable for employer table
730 --==============================================================================
731 PROCEDURE arch_employer_loop IS
732 CURSOR employer_csr (p_payroll_action_id NUMBER,
733 p_transmitter_tax_unit_id NUMBER) IS
734 SELECT DISTINCT aa.tax_unit_id
735 FROM pay_assignment_actions aa
736 WHERE aa.payroll_action_id = p_payroll_action_id
737 UNION
738 SELECT p_transmitter_tax_unit_id
739 FROM DUAL;
740 --
741 employer_row employer_csr%ROWTYPE;
742 result ff_archive_items.value%TYPE;
743 i INTEGER;
744 BEGIN
745 hr_utility.set_location ('arch_employer_loop',1);
746 l_next_header := 'R';
747 --
748 --------------------------------------------------------------------------------
749 -- Loop through employers
750 --------------------------------------------------------------------------------
751 FOR employer_row IN employer_csr (l_payroll_action_id,
752 l_transmitter_tax_unit_id) LOOP
753 hr_utility.set_location ('arch_employer_loop',11);
754 --
755 --------------------------------------------------------------------------------
756 -- Set contexts
757 --------------------------------------------------------------------------------
758 pay_balance_pkg.set_context ('TAX_UNIT_ID',employer_row.tax_unit_id);
759 --
760 hr_utility.set_location ('arch_employer_loop',12);
761 --------------------------------------------------------------------------------
762 -- Loop through employer db items
763 --------------------------------------------------------------------------------
764 FOR i IN 1..l_employer_dbis.sz LOOP
765 hr_utility.set_location ('arch_employer_loop',121);
766 --
767 --------------------------------------------------------------------------------
768 -- Execute user exit
769 --------------------------------------------------------------------------------
770 result := pay_balance_pkg.run_db_item (l_employer_dbis.item_name(i),
771 l_business_group_id,
772 l_legislation_code);
773 --
774 --------------------------------------------------------------------------------
775 -- Ensure date is in correct format
776 --------------------------------------------------------------------------------
777 -- Commented it out because run_db_item already returns date in proper format.
778 -- IF l_employer_dbis.data_type(i) = 'D' THEN
779 -- result := TO_CHAR (fnd_date.canonical_to_date(result));
780 -- END IF;
781 --
782 --------------------------------------------------------------------------------
783 -- Store data
784 --------------------------------------------------------------------------------
785 arch_store ('R',
786 'A_' || l_employer_dbis.item_name(i),
787 l_employer_dbis.user_entity_id(i),
788 l_payroll_action_id,
789 result,
790 employer_row.tax_unit_id );
791 --
792 hr_utility.set_location ('arch_employer_loop',122);
793 END LOOP;
794 --
795 hr_utility.set_location ('arch_employer_loop',13);
796 --------------------------------------------------------------------------------
797 -- Commit every 20 employees
798 --------------------------------------------------------------------------------
799 IF (employer_csr%ROWCOUNT > 0) AND
800 ((employer_csr%ROWCOUNT MOD l_chunk_size) = 0) THEN
801 COMMIT;
802 END IF;
803 --
804 hr_utility.set_location ('arch_employer_loop',14);
805 END LOOP;
806 --
807 hr_utility.set_location ('arch_employer_loop',2);
808 END arch_employer_loop;
809 --
810 --==============================================================================
811 -- ARCH_EMPLOYEE_LOOP
812 --
813 -- Loop through the employees and for every employee insert an archive item for
814 -- every balance db item and every assignment db item
815 --
816 -- employee_csr Employer cursor to select employer tax unit id by payroll
817 -- action id. The effective date is used to calculate the date
818 -- earned
819 -- employee_row Row for the cursor
820 -- result Result of database item/balance user callbacks
821 -- i Loop variable for assignment/balance tables
822 --==============================================================================
823 PROCEDURE arch_employee_loop IS
824 CURSOR employee_csr (p_payroll_action_id NUMBER,
825 p_effective_date DATE) IS
826 SELECT aa.assignment_action_id,
827 aa.assignment_id,
828 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id)
832 WHERE aa.payroll_action_id = p_payroll_action_id;
829 date_earned,
830 aa.tax_unit_id
831 FROM pay_assignment_actions aa
833 --
834 employee_row employee_csr%ROWTYPE;
835 result ff_archive_items.value%TYPE;
836 aaid pay_assignment_actions.assignment_action_id%TYPE;
837 i INTEGER;
838 BEGIN
839 hr_utility.set_location ('arch_employee_loop',1);
840 l_next_header := 'E';
841 --
842 --------------------------------------------------------------------------------
843 -- Loop through employees
844 --------------------------------------------------------------------------------
845 FOR employee_row IN employee_csr (l_payroll_action_id,l_effective_date) LOOP
846 hr_utility.set_location ('arch_employee_loop',11);
847 --
848 --------------------------------------------------------------------------------
849 -- Setup contexts
850 --------------------------------------------------------------------------------
851 pay_balance_pkg.set_context ('ASSIGNMENT_ID',employee_row.assignment_id);
852 pay_balance_pkg.set_context ('DATE_EARNED',
853 fnd_date.date_to_canonical(employee_row.date_earned));
854 pay_balance_pkg.set_context ('TAX_UNIT_ID',employee_row.tax_unit_id);
855 --
856 IF l_effective_date > employee_row.date_earned THEN
857 SELECT MAX(assignment_action_id)
858 INTO aaid
859 FROM pay_assignment_actions
860 WHERE tax_unit_id = employee_row.tax_unit_id AND
861 assignment_id = employee_row.assignment_id;
862 --
863 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',aaid);
864 ELSE
865 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',
866 employee_row.assignment_action_id);
867 END IF;
868 --
869 hr_utility.set_location ('arch_employee_loop',12);
870 --------------------------------------------------------------------------------
871 -- Balance Loop
872 --------------------------------------------------------------------------------
873 FOR i IN 1..l_balance_dbis.sz LOOP
874 --
875 hr_utility.set_location ('arch_employee_loop',121);
876 --
877 result := pay_balance_pkg.get_value (l_balance_dbis.balance_id(i),
878 employee_row.assignment_action_id);
879 --
880 hr_utility.trace ('** Balance Loop ** ' ||
881 l_balance_dbis.item_name(i) || ' = ' || result);
882 --
883 arch_store ('E',
884 'A_' || l_balance_dbis.item_name(i),
885 l_balance_dbis.user_entity_id(i),
886 employee_row.assignment_action_id,
887 result );
888 --
889 hr_utility.set_location ('arch_employee_loop',122);
890 END LOOP;
891 --
892 hr_utility.set_location ('arch_employee_loop',13);
893 --------------------------------------------------------------------------------
894 -- Assignment Loop
895 --------------------------------------------------------------------------------
896 FOR i IN 1..l_assignment_dbis.sz LOOP
897 hr_utility.set_location ('arch_employee_loop',131);
898 --
899 --------------------------------------------------------------------------------
900 -- Execute user exit
901 --------------------------------------------------------------------------------
902 result := pay_balance_pkg.run_db_item (l_assignment_dbis.item_name(i),
903 l_business_group_id,
904 l_legislation_code);
905 --
906 --------------------------------------------------------------------------------
907 -- Ensure date is in correct format
908 --------------------------------------------------------------------------------
909 -- Commented it out because run_db_item already returns date in proper format.
910 -- IF l_assignment_dbis.data_type(i) = 'D' THEN
911 -- result := TO_CHAR (fnd_date.canonical_to_date(result));
912 -- END IF;
913 --
914 hr_utility.trace ('** Assignments loop ** ' ||
915 l_assignment_dbis.item_name(i) || ' = ' || result);
916 --
917 --------------------------------------------------------------------------------
918 -- Store data
919 --------------------------------------------------------------------------------
920 arch_store ('E',
921 'A_' || l_assignment_dbis.item_name(i),
922 l_assignment_dbis.user_entity_id(i),
923 employee_row.assignment_action_id,
924 result );
925 --
926 hr_utility.set_location ('arch_employee_loop',132);
927 END LOOP;
928 --
929 hr_utility.set_location ('arch_employee_loop',14);
930 --------------------------------------------------------------------------------
931 -- Commit every 20 employees
932 --------------------------------------------------------------------------------
933 IF (employee_csr%ROWCOUNT > 0) AND
934 ((employee_csr%ROWCOUNT MOD l_chunk_size) = 0) THEN
935 COMMIT;
936 END IF;
937 --
938 hr_utility.set_location ('arch_employee_loop',15);
939 END LOOP;
940 --
941 hr_utility.set_location ('arch_employee_loop',2);
942 END arch_employee_loop;
943 --
944 --==============================================================================
945 -- ARCH_MAIN
946 --
947 -- Extract main program
948 --==============================================================================
949 PROCEDURE arch_main (p_runmode VARCHAR2,
950 p_payroll_action_id NUMBER) IS
951 BEGIN
952 hr_utility.set_location ('arch_main',1);
953 --
954 l_runmode := p_runmode;
955 --
956 arch_initialise (p_payroll_action_id);
957 arch_report_header;
958 arch_db_items_loop;
959 arch_employer_loop;
960 arch_employee_loop;
961 arch_report_header ('F');
962 COMMIT;
963 --
964 hr_utility.set_location ('arch_main',2);
965 END arch_main;
966 --
967 --==============================================================================
968 -- ARCH_ROLBK
969 --
970 -- rollback an archive - delete rows from ff_archive_items and
971 -- ff_archive_item_contexts that relate to a specified payroll action
972 --==============================================================================
973 PROCEDURE arch_rolbk (p_errmsg OUT nocopy VARCHAR2,
974 p_errcode OUT nocopy NUMBER,
975 p_payroll_action_id NUMBER) IS
976 BEGIN
977 hr_utility.set_location ('arch_rolbk',1);
978 --
979 --------------------------------------------------------------------------------
980 -- Delete archive items
981 --------------------------------------------------------------------------------
982 DELETE FROM ff_archive_item_contexts ic
983 WHERE EXISTS ( SELECT '1'
984 FROM ff_contexts con,
985 ff_route_context_usages rcu,
986 ff_user_entities ue,
987 pay_assignment_actions assact,
988 ff_archive_items i
989 WHERE i.archive_item_id = ic.archive_item_id
990 AND assact.payroll_action_id = p_payroll_action_id
991 AND assact.assignment_action_id = i.context1
992 AND i.user_entity_id = ue.user_entity_id
993 AND rcu.route_id = ue.route_id
994 AND rcu.sequence_no = 1
995 AND rcu.context_id = con.context_id
996 AND con.context_name ||''= 'ASSIGNMENT_ACTION_ID' )
997 OR EXISTS ( SELECT '1'
998 FROM ff_contexts con,
999 ff_route_context_usages rcu,
1000 ff_user_entities ue,
1001 ff_archive_items i
1002 WHERE i.archive_item_id = ic.archive_item_id
1003 AND i.context1 = p_payroll_action_id
1004 AND i.user_entity_id = ue.user_entity_id
1005 AND rcu.route_id = ue.route_id
1006 AND rcu.sequence_no = 1
1007 AND rcu.context_id = con.context_id
1008 AND con.context_name ||''= 'PAYROLL_ACTION_ID' );
1009 --
1010 hr_utility.set_location ('arch_rolbk',2);
1011 --
1012 DELETE FROM ff_archive_items i
1013 WHERE EXISTS ( SELECT '1'
1014 FROM ff_contexts con,
1015 ff_route_context_usages rcu,
1016 ff_user_entities ue,
1017 pay_assignment_actions assact
1018 WHERE assact.payroll_action_id = p_payroll_action_id
1019 AND assact.assignment_action_id = i.context1
1020 AND i.user_entity_id = ue.user_entity_id
1021 AND rcu.route_id = ue.route_id
1022 AND rcu.sequence_no = 1
1023 AND rcu.context_id = con.context_id
1024 AND con.context_name ||''= 'ASSIGNMENT_ACTION_ID' )
1025 OR EXISTS ( SELECT '1'
1026 FROM ff_contexts con,
1027 ff_route_context_usages rcu,
1028 ff_user_entities ue
1029 WHERE i.context1 = p_payroll_action_id
1030 AND i.user_entity_id = ue.user_entity_id
1031 AND rcu.route_id = ue.route_id
1032 AND rcu.sequence_no = 1
1033 AND rcu.context_id = con.context_id
1034 AND con.context_name ||''= 'PAYROLL_ACTION_ID' );
1035 --
1036 --------------------------------------------------------------------------------
1037 -- Delete assignment actions
1038 --------------------------------------------------------------------------------
1039 DELETE FROM pay_assignment_actions
1040 WHERE payroll_action_id = p_payroll_action_id;
1041 --
1042 --------------------------------------------------------------------------------
1043 -- Delete payroll action
1044 --------------------------------------------------------------------------------
1045 py_rollback_pkg.rollback_payroll_action (p_payroll_action_id);
1046 COMMIT;
1047 --
1048 hr_utility.set_location ('arch_rolbk',3);
1049 END arch_rolbk;
1050 --
1051 END pay_magtape_extract;