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