[Home] [Help]
PACKAGE BODY: APPS.PAY_ARCH_MISSING_ASG_PKG
Source
1 PACKAGE BODY pay_arch_missing_asg_pkg AS
2 /* $Header: pymissarch.pkb 120.2.12000000.2 2007/03/21 11:20:21 amigarg noship $ */
3
4 /******************************************************************************
5
6 ******************************************************************
7 * *
8 * Copyright (C) 1996 Oracle Corporation. *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disCLOSEd to third parties without *
17 * the express written permission of Oracle Corporation, *
18 * 500 Oracle Parkway, Redwood City, CA, 94065. *
19 * *
20 ******************************************************************
21 --
22 Name : PAY_ARCH_MISSING_ASG_PKG
23 Description : This package contains the logic for Multi-threading of the
24 Year End Archive Missing Assignments Report
25 --
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------- ------- ------------------------------
30 25-OCT-2005 rdhingra 115.0 4674183 Code transferred from
31 payusyem.pkb. US specific
32 calls removed
33 15-NOV-2005 rdhingra 115.1 4737510 Correct g_effective_date sent
34 while opening cursors in
35 ARCHIVE_CODE.
36 Cursor c_non_zero_run_result
37 modified to check correct
38 effective date.
39 30-NOV-2005 rdhingra 115.2 YEPhaseII Call to
40 pay_ac_utility.range_person_on
41 in action_creation made generic
42 19-JAN-2007 ydevi 115.3 4886285 adding the condition for
43 RL1 and RL2 PRE in range_cursor
44 and action creation
45 Inside archive_code the cursor
46 c_non_zero_run_balance and
47 c_non_zero_run_result is handled
48 by ref cursor so that RL1 and
49 RL2 PRE can be handled
50
51 ******************************************************************************/
52
53 g_package varchar2(80) := 'pay_arch_missing_asg_pkg.';
54
55 ----------------------------------- range_cursor ------------------------------
56 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
57
58 -- Get legislation code
59 CURSOR c_get_leg_code(cp_business_group_id NUMBER
60 ) IS
61 SELECT legislation_code
62 FROM per_business_groups
63 WHERE business_group_id = cp_business_group_id;
64 --
65 lv_legislative_param VARCHAR2(240);
66 lv_legislation_code VARCHAR2(2);
67 lv_rc_where VARCHAR2(10000);
68 l_proc VARCHAR2(240);
69 ln_business_group_id NUMBER;
70
71 begin
72
73 l_proc := g_package||'range_cursor';
74 hr_utility.set_location(l_proc, 10);
75 hr_utility.trace('PACTID = '||to_char(pactid));
76
77 select ppa.legislative_parameters,
78 ppa.business_group_id
79 into lv_legislative_param, ln_business_group_id
80 from pay_payroll_actions ppa
81 where payroll_action_id = pactid;
82
83 hr_utility.set_location(l_proc, 20);
84
85 open c_get_leg_code(ln_business_group_id);
86 fetch c_get_leg_code into lv_legislation_code;
87 close c_get_leg_code;
88
89 hr_utility.set_location(l_proc, 30);
90
91 BEGIN
92 EXECUTE IMMEDIATE 'BEGIN PAY_'||lv_legislation_code||
93 '_ARCHIVE_RULES.RANGE_CURSOR(:a, :b); END;'
94 USING IN pactid, OUT lv_rc_where;
95
96 hr_utility.set_location(l_proc, 40);
97
98 EXCEPTION WHEN others THEN
99 hr_utility.set_location(l_proc, 50);
100 NULL;
101 END;
102
103 hr_utility.set_location(l_proc, 60);
104
105 sqlstr := 'SELECT distinct ASG.person_id
106 FROM per_all_assignments_f ASG,
107 pay_payroll_actions PPA
108 WHERE PPA.payroll_action_id = :payroll_action_id
109 AND ASG.business_group_id = PPA.business_group_id
110 AND ASG.payroll_id is not null
111 AND ASG.assignment_type = ''E''
112 AND ASG.effective_start_date <= PPA.effective_date
113 AND ASG.effective_end_date >= PPA.start_date
114 AND EXISTS ( --CHECKING THAT ATLEAST ONE ASSIGN ACT EXIST
115 SELECT 1
116 FROM pay_assignment_actions paa
117 WHERE paa.assignment_id = ASG.assignment_id
118 AND PAA.action_status = ''C'''
119 ||lv_rc_where||
120 ')
121 ORDER BY ASG.person_id';
122
123 hr_utility.trace(sqlstr);
124
125 exception when others then
126 hr_utility.trace('Error in range_cursor - '||to_char(sqlcode) || '-' || sqlerrm);
127
128 end range_cursor;
129
130 ---------------------------------- action_creation ----------------------------------
131 PROCEDURE action_creation(pactid IN NUMBER,
132 stperson IN NUMBER,
133 endperson IN NUMBER,
134 chunk IN NUMBER) IS
135
136 -- Get legislation code
137 CURSOR c_get_leg_code(cp_business_group_id NUMBER
138 ) IS
139 SELECT legislation_code
140 FROM per_business_groups
141 WHERE business_group_id = cp_business_group_id;
142
143 -- Get report_format
144 CURSOR c_get_report_format(cp_report_type VARCHAR2,
145 cp_report_qualifier VARCHAR2,
146 cp_report_category VARCHAR2,
147 cp_start_date DATE,
148 cp_end_date DATE
149 ) IS
150 SELECT report_format
151 FROM pay_report_format_mappings_f
152 WHERE report_type = cp_report_type
153 AND report_qualifier = cp_report_qualifier
154 AND report_category = cp_report_category
155 AND cp_start_date BETWEEN effective_start_date AND effective_end_date
156 AND cp_end_date BETWEEN effective_start_date AND effective_end_date;
157
158
159
160 --
161 l_effective_date DATE;
162 l_year_start DATE;
163 l_year_end DATE;
164 lockingactid NUMBER;
165 lockedactid NUMBER;
166 l_eoy_tax_unit_id NUMBER;
167 assignid NUMBER;
168 l_action NUMBER;
169 l_step NUMBER;
170 l_proc VARCHAR2(240);
171 lv_ac_where VARCHAR2(10000);
172 lv_legislation_code VARCHAR2(2);
173 lv_report_type VARCHAR2(30);
174 lv_report_format VARCHAR2(30);
175 lv_report_qualifier VARCHAR2(30);
176 lv_report_category VARCHAR2(30);
177 lv_range_person_on BOOLEAN;
178 ln_business_group_id NUMBER;
179
180 TYPE RefCurType IS REF CURSOR;
181 c_actions RefCurType;
182 c_actions_sql VARCHAR2(10000);
183
184 BEGIN
185
186 l_proc := g_package||'action_creation';
187 hr_utility.set_location(l_proc, 10);
188
189 l_step := 1;
190 SELECT effective_date
191 ,pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
192 ,business_group_id
193 ,report_type
194 ,report_qualifier
195 ,report_category
196 INTO g_effective_date,
197 g_tax_unit_id,
198 ln_business_group_id,
199 lv_report_type,
200 lv_report_qualifier,
201 lv_report_category
202 FROM pay_payroll_actions
203 WHERE payroll_action_id = pactid;
204
205
206 hr_utility.trace('g_effective_date:'|| to_char(g_effective_date,'dd-mm-yyyy'));
207 hr_utility.trace('g_tax_unit_id:'|| to_char(g_tax_unit_id));
208 hr_utility.trace('business_group_id:'|| to_char(ln_business_group_id));
209 hr_utility.trace('report_type:'|| lv_report_type);
210 hr_utility.trace('report_qualifier:'|| lv_report_qualifier);
211 hr_utility.trace('report_category:'|| lv_report_category);
212
213 open c_get_leg_code(ln_business_group_id);
214 fetch c_get_leg_code into lv_legislation_code;
215 close c_get_leg_code;
216
217 l_year_start := trunc(g_effective_date, 'Y');
218 l_year_end := add_months(trunc(g_effective_date, 'Y'),12) -1;
219 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
220 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
221
222 open c_get_report_format(lv_report_type, lv_report_qualifier,
223 lv_report_category, l_year_start, l_year_end);
224 fetch c_get_report_format into lv_report_format;
225 close c_get_report_format;
226 hr_utility.trace('report_format:'|| lv_report_format);
227
228
229 lv_range_person_on := pay_ac_utility.range_person_on(
230 p_report_type => lv_report_type
231 ,p_report_format => lv_report_format
232 ,p_report_qualifier => lv_report_qualifier
233 ,p_report_category => lv_report_category
234 );
235
236 l_step := 2;
237 hr_utility.set_location(l_proc, 20);
238
239 BEGIN
240 EXECUTE IMMEDIATE 'BEGIN PAY_'|| lv_legislation_code ||
241 '_ARCHIVE_RULES.ACTION_CREATION(:a, :b, :c, :d, :e); END;'
242 USING IN pactid, IN stperson, IN endperson, IN chunk, OUT lv_ac_where;
243
244 hr_utility.set_location(l_proc, 30);
245
246 EXCEPTION WHEN others THEN
247 hr_utility.set_location(l_proc, 40);
248 NULL;
249 END;
250 l_step := 3;
251
252 IF lv_range_person_on THEN
253 hr_utility.trace ('Person ranges are ON');
254 hr_utility.trace('chunk ='|| chunk);
255 hr_utility.trace('l_year_start ='|| l_year_start);
256 hr_utility.trace('l_year_end ='|| l_year_end);
257 hr_utility.trace('stperson ='|| stperson);
258 hr_utility.trace('endperson ='|| endperson);
259 c_actions_sql :=
260 'SELECT distinct paf.assignment_id asg_id,
261 paa.assignment_action_id assact
262 FROM per_all_assignments_f paf,
263 pay_assignment_actions paa,
264 pay_payroll_actions ppa,
265 pay_payrolls_f ppf,
266 pay_population_ranges ppr
267 WHERE paf.assignment_id = paa.assignment_id
268 AND paf.assignment_type = ''E''
269 AND paf.person_id = ppr.person_id
270 AND ppr.chunk_number = '|| chunk ||'
271 AND ppr.payroll_action_id = '|| pactid ||'
272 AND paf.effective_start_date <= add_months(ppa.effective_date, 12) - 1
273 AND paf.effective_end_date >= ppa.effective_date
274 AND ppa.payroll_action_id = paa.payroll_action_id
275 AND ppa.action_type in (''R'',''B'',''Q'',''V'',''I'')
276 AND ppa.business_group_id = paf.business_group_id
277 AND ppa.effective_date between '''|| l_year_start ||''' AND '''|| l_year_end ||'''
278 AND ppa.payroll_id = ppf.payroll_id
279 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
280 AND ppf.payroll_id >= 0
281 AND paf.person_id between '|| stperson ||' and '|| endperson ||'
282 '|| lv_ac_where ||'
283 ORDER BY paf.assignment_id';
284 ELSE
285 hr_utility.trace ('Person ranges are OFF');
286 c_actions_sql :=
287 'SELECT distinct paf.assignment_id asg_id,
288 paa.assignment_action_id assact
289 FROM per_all_assignments_f paf,
290 pay_assignment_actions paa,
291 pay_payroll_actions ppa,
292 pay_payrolls_f ppf
293 WHERE paf.assignment_id = paa.assignment_id
294 AND paf.assignment_type = ''E''
295 AND paf.effective_start_date <= add_months(ppa.effective_date, 12) - 1
296 AND paf.effective_end_date >= ppa.effective_date
297 AND ppa.payroll_action_id = paa.payroll_action_id
298 AND ppa.action_type in (''R'',''B'',''Q'',''V'',''I'')
299 AND ppa.business_group_id = paf.business_group_id
300 AND ppa.effective_date between '''|| l_year_start ||'''
301 AND '''|| l_year_end ||'''
302 AND ppa.payroll_id = ppf.payroll_id
303 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
304 AND ppf.payroll_id >= 0
305 AND paf.person_id between '|| stperson ||' and '|| endperson ||'
306 '|| lv_ac_where ||'
307 ORDER BY paf.assignment_id';
308 END IF;
309 --hr_utility.trace(c_actions_sql);
310 hr_utility.trace('after execution');
311 OPEN c_actions FOR c_actions_sql;
312 l_step := 4;
313 LOOP
314 FETCH c_actions INTO assignid,lockedactid;
315 EXIT WHEN c_actions%NOTFOUND;
316 hr_utility.trace('assignid = ' || assignid);
317 hr_utility.trace('lockedactid = ' || lockedactid);
318
319
320 SELECT pay_assignment_actions_s.nextval
321 INTO lockingactid
322 FROM dual;
323
324 l_step := 5;
325 -- insert the action record.
326 hr_nonrun_asact.insact --no change
327 (
328 lockingactid =>lockingactid,
329 object_id =>assignid,
330 pactid =>pactid,
331 chunk =>chunk,
332 greid =>g_tax_unit_id ---it should be passed as null for RL1 and RL2
333 );
334 hr_utility.trace('inserted into temp object actions - ' || lockingactid);
335 END LOOP;
336 CLOSE c_actions;
337
338 hr_utility.trace('leaving action_creation');
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 raise_application_error(-20001,'Error in action_creation in Step ' || l_step);
343 END action_creation;
344
345
346 ---------------------------------- archive_init ----------------------------------
347
348 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER) IS
349
350 CURSOR c_get_min_chunk is
351 SELECT min(paa.chunk_number)
352 FROM pay_assignment_actions paa
353 WHERE paa.payroll_action_id = p_payroll_action_id;
354
355 -- Get Balance Attributes
356 CURSOR c_get_bal_attributes(cp_leg_code VARCHAR2
357 ) IS
358 select distinct fcl.lookup_code
359 from fnd_common_lookups fcl,
360 fnd_lookup_values flv
361 where fcl.lookup_type = 'YE_ARCH_REPORTS_BAL_ATTRIBUTES'
362 and fcl.lookup_type = flv.lookup_type
363 and flv.tag = '+'||cp_leg_code
364 and fcl.lookup_code = flv.lookup_code;
365
366 -- Get legislation code
367 CURSOR c_get_leg_code(cp_business_group_id NUMBER
368 ) IS
369 SELECT legislation_code
370 FROM per_business_groups
371 WHERE business_group_id = cp_business_group_id;
372
373 -- Get Balance Attribute ID
374 CURSOR c_get_bal_attribute_id(cp_attribute_name varchar2) IS
375 SELECT attribute_id
376 FROM pay_bal_attribute_definitions
377 WHERE attribute_name = cp_attribute_name;
378
379 l_param VARCHAR2(240);
380 l_proc VARCHAR2(240);
381 l_bal_attribute_name VARCHAR2(100);
382 l_leg_code VARCHAR2(2);
383 l_start_date DATE;
384 l_end_date DATE;
385 l_business_group_id NUMBER;
386 l_count NUMBER;
387 l_step NUMBER;
388
389
390 BEGIN
391
392 l_proc := g_package||'archive_init';
393 hr_utility.set_location(l_proc, 10);
394 --
395 g_payroll_action_id := p_payroll_action_id;
396 --
397 l_count := 0;
398 l_step := 1;
399 --
400 SELECT ppa.legislative_parameters,
401 ppa.business_group_id,
402 ppa.start_date,
403 ppa.effective_date
404 INTO l_param,
405 l_business_group_id,
406 l_start_date,
407 l_end_date
408 FROM pay_payroll_actions ppa
409 WHERE ppa.payroll_action_id = p_payroll_action_id;
410
411 l_step := 2;
412 OPEN c_get_leg_code(l_business_group_id);
413 FETCH c_get_leg_code INTO l_leg_code;
414 CLOSE c_get_leg_code;
415
416 l_step := 3;
417 g_run_balance_status := 'N';
418 OPEN c_get_bal_attributes(l_leg_code);
419 LOOP
420 FETCH c_get_bal_attributes INTO l_bal_attribute_name;
421 EXIT WHEN c_get_bal_attributes%NOTFOUND;
422
423 ltr_def_bal_status(l_count).attribute := l_bal_attribute_name;
424 OPEN c_get_bal_attribute_id(ltr_def_bal_status(l_count).attribute);
425 FETCH c_get_bal_attribute_id INTO ltr_def_bal_status(l_count).attribute_id;
426 CLOSE c_get_bal_attribute_id;
427
428 l_step := 4;
429 g_run_balance_status := pay_us_payroll_utils.check_balance_status
430 (l_start_date,
431 l_business_group_id,
432 l_bal_attribute_name,
433 l_leg_code);
434
435 hr_utility.trace('Checking Attribute = '|| l_bal_attribute_name);
436 hr_utility.trace('g_run_balance_status = '|| g_run_balance_status);
437
438 l_count := l_count + 1;
439
440 IF (g_run_balance_status = 'N') THEN
441 EXIT;
442 END IF;
443
444 END LOOP;
445 CLOSE c_get_bal_attributes;
446 l_step := 5;
447
448 hr_utility.trace('Outside g_run_balance_status = '|| g_run_balance_status);
449 hr_utility.trace('leaving archive_init');
450
451 EXCEPTION WHEN NO_DATA_FOUND THEN
452 raise_application_error(-20001,'In Archive_Init No Data Found In Step '|| l_step);
453
454
455 END ARCHIVE_INIT;
456
457
458 ---------------------------------- archive_code ----------------------------------
459
460 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER,
461 p_effective_date in date
462 ) IS
463
464 CURSOR c_get_session_id IS
465 SELECT userenv('sessionid')
466 FROM dual;
467
468 -- Get legislation code
469 CURSOR c_get_leg_code(cp_business_group_id NUMBER
470 ) IS
471 SELECT legislation_code
472 FROM per_business_groups
473 WHERE business_group_id = cp_business_group_id;
474
475 lv_result_value number:=0;
476 lv_count number;
477 l_asgid pay_assignment_actions.assignment_id%TYPE;
478 l_chunk number;
479 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
480 lv_session_id number;
481 l_step number;
482 l_proc VARCHAR2(240);
483 lv_ac_where VARCHAR2(10000);
484 ln_business_group_id NUMBER;
485 lv_legislation_code VARCHAR2(2);
486
487 TYPE RefCurType IS REF CURSOR;
488 c_run_bal_or_result RefCurType;
489 c_non_zero_run_balance VARCHAR2(10000);
490 c_non_zero_run_result VARCHAR2(10000);
491
492 begin
493
494 l_proc := g_package||'archive_code';
495 hr_utility.set_location(l_proc, 10);
496
497 l_step := 1;
498
499 SELECT aa.object_id,
500 aa.chunk_number,
501 aa.payroll_action_id
502 into l_asgid,
503 l_chunk,
504 l_payroll_action_id
505 FROM pay_temp_object_actions aa
506 WHERE aa.object_action_id = p_assignment_action_id;
507
508 hr_utility.trace('l_asgid = ' || l_asgid);
509 hr_utility.trace('l_chunk = ' || l_chunk);
510 hr_utility.trace('l_payroll_action_id = ' || l_payroll_action_id);
511 hr_utility.trace('g_run_balance_status = ' || g_run_balance_status);
512
513 open c_get_session_id;
514 fetch c_get_session_id into g_session_id;
515 hr_utility.trace('g_session_id = '|| g_session_id);
516 close c_get_session_id;
517
518 select ppa.business_group_id
519 into ln_business_group_id
520 from pay_payroll_actions ppa
521 where payroll_action_id = l_payroll_action_id;
522
523 hr_utility.set_location(l_proc, 20);
524
525 open c_get_leg_code(ln_business_group_id);
526 fetch c_get_leg_code into lv_legislation_code;
527 close c_get_leg_code;
528
529 hr_utility.set_location(l_proc, 30);
530
531 BEGIN
532 EXECUTE IMMEDIATE 'BEGIN PAY_'|| lv_legislation_code ||
533 '_ARCHIVE_RULES.archive_code(:a, :b); END;'
534 USING IN l_payroll_action_id, OUT lv_ac_where;
535
536 hr_utility.set_location(l_proc, 30);
537
538 EXCEPTION WHEN others THEN
539 hr_utility.set_location(l_proc, 40);
540 NULL;
541 END;
542
543 hr_utility.trace('l_asgid = '|| l_asgid);
544 hr_utility.trace('g_effective_date = '||g_effective_date);
545 hr_utility.trace('g_tax_unit_id = '||g_tax_unit_id);
546 if g_run_balance_status = 'Y' then --As of this version all balances should be valid
547 for lv_count in ltr_def_bal_status.first..ltr_def_bal_status.last loop
548 hr_utility.trace('lv_result_value_1:'||to_char(lv_result_value));
549 hr_utility.trace('attribute_id = '||ltr_def_bal_status(lv_count).attribute_id);
550 if lv_result_value = 0 then
551 l_step := 2;
552 hr_utility.trace('opened c_non_zero_run_balance');
553 --hr_utility.trace(ltr_def_bal_status(lv_count).attribute_id);
554 c_non_zero_run_balance := 'SELECT 1
555 FROM DUAL
556 WHERE EXISTS(
557 select 1
558 from pay_run_balances prb,
559 pay_balance_attributes pba,
560 pay_assignment_actions paa
561 where paa.assignment_id = '||to_char(l_asgid)||'
562 AND paa.assignment_Action_id = prb.assignment_Action_id
563 AND prb.effective_date between '''||to_char(add_months(g_effective_date,-12)+1)||
564 ''' and '''||to_char(g_effective_date)||'''
565 and prb.defined_balance_id = pba.defined_balance_id
566 and pba.attribute_id = '||to_char(ltr_def_bal_status(lv_count).attribute_id) ||
567 lv_ac_where||')';
568 hr_utility.trace('c_non_zero_run_balance='||c_non_zero_run_balance);
569 hr_utility.trace('hmmm');
570 open c_run_bal_or_result for c_non_zero_run_balance;
571 hr_utility.trace('hmmm');
572 fetch c_run_bal_or_result into lv_result_value;
573 hr_utility.trace('lv_result_value_2:'||to_char(lv_result_value));
574 if c_run_bal_or_result%NOTFOUND then
575 l_step := 3;
576 lv_result_value := 0;
577 hr_utility.trace('lv_result_value_3:'||to_char(lv_result_value));
578 end if;
579 close c_run_bal_or_result;
580 end if;
581 end loop;
582 else -- Run Balance Status is 'N'
583 hr_utility.trace('opened c_non_zero_run_result');
584
585 l_step := 4;
586 c_non_zero_run_result := 'SELECT 1 FROM dual
587 WHERE EXISTS (SELECT 1
588 FROM pay_run_results prr,
589 pay_run_result_values prrv,
590 pay_input_values_f piv,
591 pay_assignment_actions paa,
592 pay_payroll_actions ppa,
593 pay_payrolls_f ppf
594 WHERE paa.assignment_id = '||to_char(l_asgid)||'
595 AND prr.assignment_Action_id = paa.assignment_Action_id
596 AND ppa.payroll_action_id = paa.payroll_action_id
597 AND ppa.action_type in (''R'',''B'',''Q'',''V'',''I'')
598 AND ppa.effective_date between '''||to_char(add_months(g_effective_date, -12) + 1)||
599 '''AND'''|| to_char(g_effective_date)||'''
600 AND ppa.payroll_id = ppf.payroll_id
601 AND ppa.effective_date between ppf.effective_start_date
602 AND ppf.effective_end_date
603 AND ppf.payroll_id > 0
604 AND prrv.run_result_id = prr.run_result_id
605 AND prrv.result_value <> ''0''
606 AND piv.input_value_id = prrv.input_value_id
607 AND ppa.effective_date between piv.effective_Start_date
608 AND piv.effective_end_date
609 AND piv.uom = ''M''
610 and exists (select 1
611 from pay_balance_feeds_f pbf
612 where piv.input_value_id = pbf.input_value_id
613 and ppa.effective_date between pbf.effective_Start_date
614 AND pbf.effective_end_date
615 )'|| lv_ac_where||'
616 )';
617
618 open c_run_bal_or_result for c_non_zero_run_result;
619 FETCH c_run_bal_or_result into lv_result_value;
620 hr_utility.trace('lv_result_value_4 = '||lv_result_value);
621 CLOSE c_run_bal_or_result;
622
623 end if;
624
625 hr_utility.trace('lv_result_value_5:'||to_char(lv_result_value));
626
627 l_step := 5;
628 if lv_result_value = 1 then
629 --
630 hr_utility.trace('lv_result_value_6:'||to_char(lv_result_value));
631 insert
632 into pay_us_rpt_totals(
633 session_id,
634 tax_unit_id,
635 value1,
636 attribute1,
637 location_id
638 )
639 values
640 (
641 g_session_id,
642 nvl(g_tax_unit_id,0),
643 l_asgid, --assignment action id passed by PYUGEN
644 'YEAR END MISSING ASSIGNMENTS',
645 l_payroll_action_id);
646 --
647
648
649
650
651 hr_utility.trace('assignment_id = ' || l_asgid);
652 end if;
653
654 hr_utility.trace('leaving archive_code');
655
656 exception
657 when others then
658 hr_utility.trace(sqlcode||':'||sqlerrm);
659 raise_application_error(-20001,'Error in archive_code in Step ' || l_step);
660
661 end ARCHIVE_CODE;
662
663
664 ---------------------------------- archive_deinit ----------------------------------
665
666 Procedure ARCHIVE_DEINIT(p_payroll_action_id IN NUMBER) IS
667
668 l_proc VARCHAR2(240);
669
670 begin
671
672 l_proc := g_package||'archive_deinit';
673 hr_utility.set_location(l_proc, 10);
674
675 --
676 select effective_date,
677 pay_core_utils.get_parameter('TRANSFER_GRE',
678 legislative_parameters)
679 into g_effective_date,
680 g_tax_unit_id
681 from pay_payroll_actions
682 where payroll_action_id = p_payroll_action_id;
683 --
684 hr_utility.trace('g_effective_date ='||g_effective_date);
685 hr_utility.trace('g_tax_unit_id ='||g_tax_unit_id);
686 pay_yepp_miss_assign_pkg.select_employee(p_payroll_action_id,
687 g_effective_date,
688 g_tax_unit_id,
689 g_session_id);
690 --
691 pay_archive.remove_report_actions(p_payroll_action_id);
692 --
693 DELETE FROM pay_us_rpt_totals
694 WHERE attribute1='YEAR END MISSING ASSIGNMENTS'
695 AND location_id = p_payroll_action_id;
696 --
697 hr_utility.trace('leaving archive_deinit');
698
699 end ARCHIVE_DEINIT;
700
701 --begin
702 --hr_utility.trace_on(null,'MIS');
703 END pay_arch_missing_asg_pkg;
704