[Home] [Help]
PACKAGE BODY: APPS.PAY_ARCHIVE_MISSING_ASG_PKG
Source
1 package body pay_archive_missing_asg_pkg as
2 /* $Header: payusyem.pkb 120.0 2005/10/17 18:18:28 djoshi noship $ */
3 /*
4 Copyright (c) Oracle Corporation 2005. All rights reserved
5 --
6 Name : PAY_ARCHIVE_MISSING_ASG_PKG
7 Description : This package contains the logic for Multi-threading of the
8 Year End Archive Missing Assignments Report
9 --
10 Change List
11 -----------
12 Date Name Vers Bug Description
13 ----------- ---------- ----- -------- ----------------------------------
14 10-AUG-2005 rsethupa 115.0 Created
15 05-sep-2005 rsethupa 115.1 Delete records from PAY_US_RPT_TOTALS
16 in DEINIT code
17 13-Sep-2005 sdhole 115.3 4577187 Changed the report type from YEMA to
18 YREND_YEMA.
19 16-Sep-2005 sdhole 115.4 4613898 Modified ARCHIVE_INIT,ARCHIVE_DEINIT,
20 ARCHIVE_INIT,ARCHIVE_CODE procedures.
21 23-sep-2005 djoshi 115.5 462035 Modified the Package.
22 1. Archive Init commented
23 2. Archive_code modified
24 */
25 ----------------------------------- range_cursor ----------------------------------
26 --
27
28 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
29
30 --
31 lv_legislative_param varchar2(240);
32 begin
33
34
35 hr_utility.trace('Reached range_cursor');
36 hr_utility.trace('PACTID = '||to_char(pactid));
37
38 select legislative_parameters
39 into lv_legislative_param
40 from pay_payroll_actions
41 where payroll_action_id = pactid;
42
43 sqlstr := 'SELECT distinct ASG.person_id
44 FROM per_all_assignments_f ASG,
45 pay_us_asg_reporting PUAR,
46 pay_payroll_actions PPA
47 WHERE PPA.payroll_action_id = :payroll_action_id
48 AND PUAR.tax_unit_id = pay_us_payroll_utils.get_parameter(
49 ''TRANSFER_GRE'',
50 legislative_parameters)
51 AND PUAR.assignment_id = ASG.assignment_id
52 AND ASG.assignment_type = ''E''
53 AND ASG.effective_start_date <= PPA.effective_date
54 AND ASG.effective_end_date >= PPA.start_date
55 AND ASG.business_group_id + 0 = PPA.business_group_id
56 AND ASG.payroll_id is not null
57 ORDER BY ASG.person_id';
58
59 hr_utility.trace(sqlstr);
60
61 exception when others then
62 hr_utility.trace('Error in range_cursor - '||to_char(sqlcode) || '-' || sqlerrm);
63
64 end range_cursor;
65
66 ---------------------------------- action_creation ----------------------------------
67 --
68 procedure action_creation(pactid in number,
69 stperson in number,
70 endperson in number,
71 chunk in number) is
72
73 CURSOR c_actions
74 (
75 cp_year_start DATE
76 ,cp_year_end DATE
77 ,cp_tax_unit_id NUMBER
78 ,cp_start_person_id NUMBER
79 ,cp_end_person_id NUMBER
80 ) is
81
82 SELECT distinct paf.assignment_id asg_id,
83 paa.assignment_action_id assact
84 FROM per_all_assignments_f paf,
85 pay_assignment_actions paa,
86 pay_payroll_actions ppa,
87 pay_payrolls_f ppf,
88 pay_us_asg_reporting puar
89 WHERE paf.assignment_id = paa.assignment_id
90 AND paf.assignment_id = puar.assignment_id
91 AND puar.tax_unit_id = cp_tax_unit_id
92 AND paf.assignment_type = 'E'
93 AND paf.effective_start_date <= add_months(ppa.effective_date, 12) - 1
94 AND paf.effective_end_date >= ppa.effective_date
95 AND ppa.payroll_action_id = paa.payroll_action_id
96 AND ppa.action_type in ('R','B','Q','V','I')
97 AND ppa.business_group_id = paf.business_group_id
98 AND ppa.effective_date between cp_year_start
99 AND cp_year_end
100 AND ppa.payroll_id = ppf.payroll_id
101 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
102 AND ppf.payroll_id >= 0
103 AND paa.tax_unit_id = cp_tax_unit_id
104 AND paf.person_id between cp_start_person_id and cp_end_person_id
105 AND not exists (SELECT 1
106 FROM pay_payroll_actions ppa,
107 pay_assignment_actions paa
108 WHERE ppa.report_type = 'YREND'
109 AND ppa.action_status = 'C'
110 AND ppa.effective_date = cp_year_end
111 AND to_number(substr(legislative_parameters,
112 instr(legislative_parameters,'TRANSFER_GRE=')+
113 length('TRANSFER_GRE='))) = cp_tax_unit_id
114 AND ppa.payroll_action_id = paa.payroll_action_id
115 AND paa.action_status = 'C'
116 AND ppa.business_group_id = paf.business_group_id
117 AND paa.serial_number = to_char(paf.person_id))
118 ORDER BY paf.assignment_id;
119
120
121 CURSOR c_actions_range_person
122 (
123 cp_year_start DATE
124 ,cp_year_end DATE
125 ,cp_tax_unit_id NUMBER
126 ,cp_start_person_id NUMBER
127 ,cp_end_person_id NUMBER
128 ) is
129
130 SELECT distinct paf.assignment_id asg_id,
131 paa.assignment_action_id assact
132 FROM per_all_assignments_f paf,
133 pay_assignment_actions paa,
134 pay_payroll_actions ppa,
135 pay_payrolls_f ppf,
136 pay_us_asg_reporting puar,
137 pay_population_ranges ppr
138 WHERE paf.assignment_id = paa.assignment_id
139 AND paf.assignment_id = puar.assignment_id
140 AND puar.tax_unit_id = cp_tax_unit_id
141 AND paf.assignment_type = 'E'
142 AND paf.person_id = ppr.person_id
143 AND ppr.chunk_number = chunk
144 AND ppr.payroll_action_id = pactid
145 AND paf.effective_start_date <= add_months(ppa.effective_date, 12) - 1
146 AND paf.effective_end_date >= ppa.effective_date
147 AND ppa.payroll_action_id = paa.payroll_action_id
148 AND ppa.action_type in ('R','B','Q','V','I')
149 AND ppa.business_group_id = paf.business_group_id
150 AND ppa.effective_date between cp_year_start
151 AND cp_year_end
152 AND ppa.payroll_id = ppf.payroll_id
153 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
154 AND ppf.payroll_id >= 0
155 AND paa.tax_unit_id = cp_tax_unit_id
156 AND paf.person_id between cp_start_person_id and cp_end_person_id
157 AND not exists (SELECT 1
158 FROM pay_payroll_actions ppa,
159 pay_assignment_actions paa
160 WHERE ppa.report_type = 'YREND'
161 AND ppa.action_status = 'C'
162 AND ppa.effective_date = cp_year_end
163 AND to_number(substr(legislative_parameters,
164 instr(legislative_parameters,'TRANSFER_GRE=')+
165 length('TRANSFER_GRE='))) = cp_tax_unit_id
166 AND ppa.payroll_action_id = paa.payroll_action_id
167 AND paa.action_status = 'C'
168 AND ppa.business_group_id = paf.business_group_id
169 AND paa.serial_number = to_char(paf.person_id))
170 ORDER BY paf.assignment_id;
171
172 --
173 l_effective_date DATE;
174 lockingactid NUMBER;
175 lockedactid NUMBER;
176 l_year_start DATE;
177 l_year_end DATE;
178 lv_range_person_on BOOLEAN;
179 l_eoy_tax_unit_id NUMBER;
180 assignid NUMBER;
181 l_action NUMBER;
182 l_step number;
183
184 begin
185 hr_utility.trace('Entering action_creation');
186
187 l_step := 1;
188 select effective_date,
189 pay_us_payroll_utils.get_parameter(
190 'TRANSFER_GRE',
191 legislative_parameters)
192 into g_effective_date,
193 g_tax_unit_id
194 from pay_payroll_actions
195 where payroll_action_id = pactid;
196
197 l_year_start := trunc(g_effective_date, 'Y');
198 l_year_end := add_months(trunc(g_effective_date, 'Y'),12) -1;
199 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
200 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
201
202 lv_range_person_on := pay_ac_utility.range_person_on(
203 p_report_type => 'YREND_YEMA'
204 ,p_report_format => 'YEMA_ARCH'
205 ,p_report_qualifier => 'FED'
206 ,p_report_category => 'RT');
207
208 l_step := 2;
209 IF lv_range_person_on THEN
210 hr_utility.trace ('Person ranges are ON');
211 OPEN c_actions_range_person(l_year_start,
212 l_year_end,
213 g_tax_unit_id,
214 stperson,
215 endperson);
216 ELSE
217 hr_utility.trace ('Person ranges are OFF');
218 OPEN c_actions(
219 l_year_start,
220 l_year_end,
221 g_tax_unit_id,
222 stperson,
223 endperson
224 );
225 END IF;
226
227 l_step := 3;
228 loop
229 IF lv_range_person_on THEN
230 FETCH c_actions_range_person INTO assignid,lockedactid;
231 EXIT WHEN c_actions_range_person%NOTFOUND;
232 ELSE
233 FETCH c_actions INTO assignid,lockedactid;
234 EXIT WHEn c_actions%NOTFOUND;
235 hr_utility.trace('assignid = ' || assignid);
236 hr_utility.trace('lockedactid = ' || lockedactid);
237 END IF;
238
239 select pay_assignment_actions_s.nextval
240 into lockingactid
241 from dual;
242
243 l_step := 4;
244 -- insert the action record.
245 hr_nonrun_asact.insact(lockingactid =>lockingactid,
246 object_id =>assignid,
247 pactid =>pactid,
248 chunk =>chunk,
249 greid =>g_tax_unit_id);
250
251 hr_utility.trace('inserted into temp object actions - ' || lockingactid);
252
253 end loop;
254
255 if lv_range_person_on then
256 close c_actions_range_person;
257 else
258 close c_actions;
259 end if;
260
261 hr_utility.trace('leaving action_creation');
262 exception
263 when others then
264 raise_application_error(-20001,'Error in action_creation in Step ' || l_step);
265 end action_creation;
266
267
268 ---------------------------------- archive_init ----------------------------------
269
270 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER) IS
271
272 CURSOR c_get_min_chunk is
273 SELECT min(paa.chunk_number)
274 FROM pay_assignment_actions paa
275 WHERE paa.payroll_action_id = p_payroll_action_id;
276
277 -- Get Balance Attributes
278 CURSOR c_get_bal_attributes(cp_leg_code VARCHAR2
279 ) IS
280 select distinct fcl.lookup_code
281 from fnd_common_lookups fcl,
282 fnd_lookup_values flv
283 where fcl.lookup_type = 'YE_ARCH_REPORTS_BAL_ATTRIBUTES'
284 and fcl.lookup_type = flv.lookup_type
285 and flv.tag = '+'||cp_leg_code
286 and fcl.lookup_code = flv.lookup_code;
287
288 -- Get legislation code
289 CURSOR c_get_leg_code(cp_business_group_id NUMBER
290 ) IS
291 SELECT legislation_code
292 FROM per_business_groups
293 WHERE business_group_id = cp_business_group_id;
294
295 -- Get Balance Attribute ID
296 CURSOR c_get_bal_attribute_id(cp_attribute_name varchar2) IS
297 SELECT attribute_id
298 FROM pay_bal_attribute_definitions
299 WHERE attribute_name = cp_attribute_name;
300
301 l_param varchar2(240);
302 l_business_group_id number;
303 l_start_date date;
304 l_end_date date;
305 l_leg_code varchar2(2);
306 l_count number;
307 l_bal_attribute_name varchar2(100);
308 l_step number;
309
310 begin
311 --
312 hr_utility.trace('entering archive_init');
313 --
314 g_payroll_action_id := p_payroll_action_id;
315 --
316 /* l_count := 0;
317 l_step := 1;
318 --
319 select ppa.legislative_parameters,
320 ppa.business_group_id,
321 ppa.start_date,
322 ppa.effective_date
323 into l_param,
324 l_business_group_id,
325 l_start_date,
326 l_end_date
327 from pay_payroll_actions ppa
328 where ppa.payroll_action_id = p_payroll_action_id;
329
330 l_step := 2;
331 open c_get_leg_code(l_business_group_id);
332 fetch c_get_leg_code into l_leg_code;
333 close c_get_leg_code;
334
335 l_step := 3;
336 open c_get_bal_attributes(l_leg_code);
337 loop
338 fetch c_get_bal_attributes into l_bal_attribute_name;
339 exit when c_get_bal_attributes%NOTFOUND;
340
341 ltr_def_bal_status(l_count).attribute := l_bal_attribute_name;
342 open c_get_bal_attribute_id(ltr_def_bal_status(l_count).attribute);
343 fetch c_get_bal_attribute_id into ltr_def_bal_status(l_count).attribute_id;
344 close c_get_bal_attribute_id;
345
346 l_step := 4;
347 g_run_balance_status := pay_us_payroll_utils.check_balance_status
348 (l_start_date,
349 l_business_group_id,
350 l_bal_attribute_name,
351 l_leg_code);
352
353 hr_utility.trace('g_run_balance_status = '||g_run_balance_status);
354
355 l_count := l_count + 1;
356
357 end loop;
358 close c_get_bal_attributes;
359 l_step := 5;
360
361 hr_utility.trace('leaving archive_init');
362
363 exception when no_data_found then
364 raise_application_error(-20001,'In Archive_Init No Data Found In Step '|| l_step);
365 */
366
367 end ARCHIVE_INIT;
368
369
370 ---------------------------------- archive_code ----------------------------------
371
372 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER,
373 p_effective_date in date
374 ) IS
375
376 CURSOR c_non_zero_run_balance(cp_assignment_id number,
377 cp_effective_date DATE,
378 cp_tax_unit_id number,
379 cp_bal_attribute_id number
380 ) IS
381 SELECT 1
382 FROM DUAL
383 WHERE EXISTS(
384 select 1
385 from pay_run_balances prb,
386 pay_balance_attributes pba,
387 pay_assignment_actions paa
388 where paa.assignment_id = cp_assignment_id
389 AND paa.tax_unit_id = cp_tax_unit_id
390 AND paa.tax_unit_id = prb.tax_unit_id
391 AND paa.assignment_Action_id = prb.assignment_Action_id
392 AND prb.effective_date between add_months(cp_effective_date,-12)+1
393 and cp_effective_date
394 and prb.defined_balance_id = pba.defined_balance_id
395 and pba.attribute_id = cp_bal_attribute_id );
396
397 CURSOR c_non_zero_run_result(cp_assignment_id number,
398 cp_effective_date date,
399 cp_tax_unit_id number) is
400 SELECT 1 FROM dual
401 WHERE EXISTS (SELECT 1
402 FROM pay_run_results prr,
403 pay_run_result_values prrv,
404 pay_input_values_f piv,
405 pay_assignment_actions paa,
406 pay_payroll_actions ppa,
407 pay_payrolls_f ppf
408 WHERE paa.assignment_id = cp_assignment_id
409 AND paa.tax_unit_id = cp_tax_unit_id
410 AND prr.assignment_Action_id = paa.assignment_Action_id
411 AND ppa.payroll_action_id = paa.payroll_action_id
412 AND ppa.action_type in ('R','B','Q','V','I')
413 AND ppa.effective_date between cp_effective_date
414 AND add_months(cp_effective_date, 12) - 1
415 AND ppa.payroll_id = ppf.payroll_id
416 AND ppa.effective_date between ppf.effective_start_date
417 AND ppf.effective_end_date
418 AND ppf.payroll_id > 0
419 AND prrv.run_result_id = prr.run_result_id
420 AND prrv.result_value <> '0'
421 AND piv.input_value_id = prrv.input_value_id
422 AND ppa.effective_date between piv.effective_Start_date
423 AND piv.effective_end_date
424 AND piv.uom = 'M'
425 and exists (select '1'
426 from pay_balance_feeds_f pbf
427 where piv.input_value_id = pbf.input_value_id
428 and ppa.effective_date between pbf.effective_Start_date
429 AND pbf.effective_end_date
430 )
431 );
432
433
434 CURSOR c_get_session_id IS
435 SELECT userenv('sessionid')
436 FROM dual;
437
438 lv_result_value number:=0;
439 lv_count number;
440 l_asgid pay_assignment_actions.assignment_id%TYPE;
441 l_chunk number;
442 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
443 lv_session_id number;
444 l_step number;
445
446 begin
447
448 hr_utility.trace('entering archive_code');
449
450 l_step := 1;
451
452 SELECT aa.object_id,
453 aa.chunk_number,
454 aa.payroll_action_id
455 into l_asgid,
456 l_chunk,
457 l_payroll_action_id
458 FROM pay_temp_object_actions aa
459 WHERE aa.object_action_id = p_assignment_action_id;
460
461 hr_utility.trace('l_asgid = ' || l_asgid);
462 hr_utility.trace('l_chunk = ' || l_chunk);
463 hr_utility.trace('l_payroll_action_id = ' || l_payroll_action_id);
464 hr_utility.trace('g_run_balance_status = ' || g_run_balance_status);
465
466 open c_get_session_id;
467 fetch c_get_session_id into g_session_id;
468 hr_utility.trace('lv_session_id = '|| lv_session_id);
469 close c_get_session_id;
470 g_run_balance_status := 'N';
471
472 if g_run_balance_status = 'Y' then
473 for lv_count in ltr_def_bal_status.first..ltr_def_bal_status.last loop
474 if lv_result_value = 0 then
475
476 l_step := 2;
477 open c_non_zero_run_balance(l_asgid,
478 trunc(g_effective_date,'Y'),
479 g_tax_unit_id,
480 ltr_def_bal_status(lv_count).attribute_id);
481
482 fetch c_non_zero_run_balance into lv_result_value;
483 if c_non_zero_run_balance%NOTFOUND then
484 l_step := 3;
485 null;
486 end if;
487 close c_non_zero_run_balance;
488 end if;
489 end loop;
490 else -- Run Balance Status is 'N'
491 hr_utility.trace('opened c_non_zero_run_result');
492 hr_utility.trace('l_asgid = '|| l_asgid);
493 hr_utility.trace('g_effective_date = '||trunc(g_effective_date,'Y'));
494 hr_utility.trace('g_tax_unit_id = '||g_tax_unit_id);
495
496 l_step := 4;
497 OPEN c_non_zero_run_result(l_asgid,
498 trunc(g_effective_date,'Y'),
499 g_tax_unit_id);
500 FETCH c_non_zero_run_result into lv_result_value;
501 hr_utility.trace('lv_result_value = '||lv_result_value);
502 CLOSE c_non_zero_run_result;
503
504 end if;
505
506 l_step := 5;
507 if lv_result_value = 1 then
508 --
509 insert
510 into pay_us_rpt_totals(
511 session_id,
512 tax_unit_id,
513 value1,
514 attribute1,
515 location_id
516 )
517 values
518 (
519 g_session_id,
520 g_tax_unit_id,
521 l_asgid,
522 'YEAR END MISSING ASSIGNMENTS',
523 l_payroll_action_id);
524 --
525
526
527
528
529 hr_utility.trace('assignment_id = ' || l_asgid);
530 end if;
531
532 hr_utility.trace('leaving archive_code');
533
534 exception
535 when others then
536 raise_application_error(-20001,'Error in archive_code in Step ' || l_step);
537
538 end ARCHIVE_CODE;
539
540
541 ---------------------------------- archive_deinit ----------------------------------
542
543 Procedure ARCHIVE_DEINIT(p_payroll_action_id IN NUMBER) IS
544
545 begin
546 --
547 select effective_date,
548 pay_us_payroll_utils.get_parameter('TRANSFER_GRE',
549 legislative_parameters)
550 into g_effective_date,
551 g_tax_unit_id
552 from pay_payroll_actions
553 where payroll_action_id = p_payroll_action_id;
554 --
555 hr_utility.trace('entering archive_deinit');
556 --
557 pay_us_yepp_miss_assign_pkg.select_employee(p_payroll_action_id,
558 g_effective_date,
559 g_tax_unit_id,
560 g_session_id);
561 --
562 pay_archive.remove_report_actions(p_payroll_action_id);
563 --
564 DELETE FROM pay_us_rpt_totals
565 WHERE attribute1='YEAR END MISSING ASSIGNMENTS'
566 AND location_id = p_payroll_action_id;
567 --
568 hr_utility.trace('leaving archive_deinit');
569 end ARCHIVE_DEINIT;
570
571 --begin
572 --hr_utility.trace_on(null,'YREND_YEMA');
573 end pay_archive_missing_asg_pkg;
574