[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_MAG_ROE
Source
1 PACKAGE BODY pay_ca_mag_roe AS
2 /* $Header: pycaremg.pkb 120.0 2005/05/29 03:42:11 appldev noship $ */
3
4 -----------------------------------------------------------------------------
5 -- Name :: get_report_parameters
6 --
7 -- Purpose
8 -- The procedure gets the 'parameter' for which the report is being
9 -- run i.e., the period, person_id.
10 --
11 -- Arguments
12 -- p_date_start Start Date of the period for which the report
13 -- has been requested
14 -- p_date_end End date of the period
15 -- p_person_id If start_date and end_date is null then
16 -- we use person_id to get the record.
17 --
18 -- Notes
19 ----------------------------------------------------------------------------
20
21 function fun_user_entity_id(p_user_entity_name varchar2)
22 return number is
23 begin
24
25 declare
26
27 cursor cur_user_entity_id is
28 select user_entity_id
29 from ff_user_entities fue
30 where fue.user_entity_name = p_user_entity_name
31 and fue.legislation_code = 'CA';
32
33 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
34
35 begin
36
37 hr_utility.set_location('func_user_entity_id',1);
38
39 open cur_user_entity_id;
40 fetch cur_user_entity_id
41 into l_user_entity_id;
42 close cur_user_entity_id;
43
44 return l_user_entity_id;
45
46 end;
47
48 end fun_user_entity_id;
49
50 procedure get_report_parameters
51 ( p_pactid IN NUMBER,
52 p_start_date OUT NOCOPY DATE,
53 p_end_date OUT NOCOPY DATE,
54 p_person_id OUT NOCOPY VARCHAR2,
55 p_assignment_set OUT NOCOPY NUMBER
56
57 ) is
58 begin
59
60 declare
61
62 cursor cur_ppa is
63 select ppa.start_date,
64 ppa.effective_date,
65 pay_core_utils.get_parameter('PERSON_ID',ppa.legislative_parameters),
66 pay_core_utils.get_parameter('ASSIGNMENT_SET',ppa.legislative_parameters)
67 from pay_payroll_actions ppa
68 where payroll_action_id = p_pactid;
69
70 begin
71 hr_utility.set_location
72 ('pay_ca_mag_roe.get_report_parameters', 10);
73
74 open cur_ppa;
75
76 fetch cur_ppa
77 into p_start_date,
78 p_end_date,
79 p_person_id,
80 p_assignment_set;
81
82 close cur_ppa;
83
84 hr_utility.set_location
85 ('pay_ca_mag_roe.get_report_parameters', 20);
86 end;
87
88 hr_utility.set_location
89 ('pay_ca_mag_roe.get_report_parameters', 30);
90 end get_report_parameters;
91
92
93 --
94 ----------------------------------------------------------------------------
95 --Name
96 -- range_cursor
97 --Purpose
98 -- To prepare the the SQL statement to fetch the people.
99 --Arguments
100 -- p_pactid payroll action id for the report.
101 -- p_sqlstr the SQL statement to fetch the people.
102 ------------------------------------------------------------------------------
103 procedure range_cursor (
104 p_pactid IN NUMBER,
105 p_sqlstr OUT NOCOPY VARCHAR2) IS
106
107 begin
108
109 declare
110 p_start_date date;
111 p_end_date date;
112 p_person_id per_people_f.person_id%TYPE;
113 p_assignment_set number;
114 l_roe_date_uid ff_user_entities.user_entity_id%TYPE;
115 l_roe_asg_id_uid ff_user_entities.user_entity_id%TYPE;
116
117 begin
118
119 --hr_utility.trace_on(1,'ORACLE');
120 hr_utility.set_location( 'pay_ca_mag_roe.range_cursor', 10);
121
122 get_report_parameters(
123 p_pactid,
124 p_start_date,
125 p_end_date,
126 p_person_id,
127 p_assignment_set
128 );
129
130 hr_utility.set_location( 'pay_ca_mag_roe.range_cursor', 20);
131
132 l_roe_date_uid := FUN_USER_ENTITY_ID('ROE_DATE');
133 l_roe_asg_id_uid := FUN_USER_ENTITY_ID('ROE_ASSIGNMENT_ID');
134
135 --if p_start_date or p_end_date is not null then
136
137 /* Bug 2385763. Changes to handle the situation when ROE magnetic report is run
138 for one employee. */
139
140 if p_person_id is not null then
141 p_sqlstr := 'select distinct person_id from
142 per_assignments_f paf,
143 ff_archive_items fai,
144 pay_payroll_actions ppa,
145 pay_assignment_actions paa
146 where
147 fai.user_entity_id = ' || l_roe_date_uid || ' and
148 fnd_date.canonical_to_date(fai.value) between
149 ppa.start_date and
150 ppa.effective_date and
151 ppa.payroll_action_id = :p_pactid and
152 ppa.business_group_id = paf.business_group_id and
153 paa.assignment_action_id = fai.context1 and
154 paf.assignment_id = paa.assignment_id and
155 fnd_date.canonical_to_date(fai.value) between
156 paf.effective_start_date and
157 paf.effective_end_date and
158 person_id = '|| p_person_id;
159 elsif p_assignment_set is not null then
160 p_sqlstr := 'select distinct paf.person_id from
161 per_assignments_f paf,
162 ff_archive_items fai,
163 pay_payroll_actions ppa,
164 pay_assignment_actions paa,
165 HR_ASSIGNMENT_SET_AMENDMENTS haa
166 where
167 fai.user_entity_id = ' || l_roe_date_uid || ' and
168 fnd_date.canonical_to_date(fai.value) between
169 ppa.start_date and
170 ppa.effective_date and
171 ppa.payroll_action_id = :p_pactid and
172 ppa.business_group_id = paf.business_group_id and
173 paa.assignment_action_id = fai.context1 and
174 paf.assignment_id = paa.assignment_id and
175 fnd_date.canonical_to_date(fai.value) between
176 paf.effective_start_date and
177 paf.effective_end_date and
178 haa.assignment_id = paf.assignment_id and
179 haa.include_or_exclude = ''I'' and
180 haa.assignment_set_id = '|| p_assignment_set;
181 else
182 p_sqlstr := 'select distinct person_id from
183 per_assignments_f paf,
184 ff_archive_items fai,
185 pay_payroll_actions ppa,
186 pay_assignment_actions paa
187 where
188 fai.user_entity_id = ' || l_roe_date_uid || ' and
189 fnd_date.canonical_to_date(fai.value) between
190 ppa.start_date and
191 ppa.effective_date and
192 ppa.payroll_action_id = :p_pactid and
193 ppa.business_group_id = paf.business_group_id and
194 paa.assignment_action_id = fai.context1 and
195 paf.assignment_id = paa.assignment_id and
196 fnd_date.canonical_to_date(fai.value) between
197 paf.effective_start_date and
198 paf.effective_end_date';
199 end if;
200
201 --end if;
202 hr_utility.set_location( 'pay_ca_mag_roe.range_cursor', 30);
203 end;
204 end range_cursor;
205
206 --
207 -----------------------------------------------------------------------------
208 --Name
209 -- create_assignment_act
210 --Purpose
211 -- Creates assignment actions for the payroll action associated with the
212 -- report
213 --Arguments
214 -- p_pactid payroll action for the report
215 -- p_stperson starting person id for the chunk
216 -- p_endperson last person id for the chunk
217 -- p_chunk size of the chunk
218 --Note
219 -- The procedure processes assignments in 'chunks' to facilitate
220 -- multi-threaded operation. The chunk is defined by the size and the
221 -- starting and ending person id. An interlock is also created against the
222 -- pre-processor assignment action to prevent rolling back of the archiver.
223 ----------------------------------------------------------------------------
224 --
225 procedure create_assignment_act(
226 p_pactid IN NUMBER,
227 p_stperson IN NUMBER,
228 p_endperson IN NUMBER,
229 p_chunk IN NUMBER ) is
230
231
232 begin
233
234 declare
235
236 p_start_date date;
237 p_end_date date;
238 p_person_id varchar2(10);
239
240 l_roe_date_id ff_archive_items.user_entity_id%TYPE;
241 l_roe_assignment_id
242 ff_archive_items.user_entity_id%TYPE;
243 l_roe_gre_id
244 ff_archive_items.user_entity_id%TYPE;
245 l_roe_payroll_id
246 ff_archive_items.user_entity_id%TYPE;
247 l_assignment_id pay_assignment_actions.assignment_id%type;
248 l_gre_id pay_assignment_actions.tax_unit_id%type;
249 l_payroll_id ff_archive_items.value%type;
250
251 cursor cur_assignment_action_id is
252 select pay_assignment_actions_s.nextval
253 from dual;
254
255
256 lockingactid number;
257
258 --
259 -- per_assignments_f is not joined by date as there is
260 -- a distinct in the select clause.
261 --
262 cursor cur_assignment_action is
263 select
264 paa.assignment_id assignment_id,
265 paa.tax_unit_id gre_id,
266 fai2.value payroll_id
267 from
268 pay_payroll_actions ppa,
269 pay_assignment_actions paa,
270 ff_archive_items fai1,
271 ff_archive_items fai2,
272 per_assignments_f paf
273 where
274 ppa.report_type = 'ROE' and
275 ppa.report_category = 'ROEC' and
276 ppa.report_qualifier = 'ROEQ' and
277 ppa.payroll_action_id = paa.payroll_action_id and
278 paa.assignment_action_id = fai1.context1 and
279 fai1.user_entity_id=l_roe_date_id and
280 fnd_date.canonical_to_date(fai1.value) between
281 fnd_date.canonical_to_date(to_char(p_start_date,'yyyy/mm/dd hh24:mi:ss')) and
282 fnd_date.canonical_to_date(to_char(p_end_date,'yyyy/mm/dd hh24:mi:ss')) and
283 fai1.context1 = fai2.context1 and
284 fai2.user_entity_id = l_roe_payroll_id and
285 paa.assignment_id = paf.assignment_id and
286 fnd_date.canonical_to_date(fai1.value) between
287 paf.effective_start_date and
288 paf.effective_end_date and
289 paf.assignment_type = 'E' and
290 paf.person_id between
291 p_stperson and
292 p_endperson;
293
294 cursor cur_assignment_action_range is
295 select
296 paa.assignment_id assignment_id,
297 paa.tax_unit_id gre_id,
298 fai2.value payroll_id
299 from
300 pay_payroll_actions ppa,
301 pay_assignment_actions paa,
302 ff_archive_items fai1,
303 ff_archive_items fai2,
304 per_assignments_f paf,
305 pay_population_ranges ppr
306 where
307 ppa.report_type = 'ROE' and
308 ppa.report_category = 'ROEC' and
309 ppa.report_qualifier = 'ROEQ' and
310 ppa.payroll_action_id = paa.payroll_action_id and
311 paa.assignment_action_id = fai1.context1 and
312 fai1.user_entity_id=l_roe_date_id and
313 fnd_date.canonical_to_date(fai1.value) between
314 fnd_date.canonical_to_date(to_char(p_start_date,'yyyy/mm/dd hh24:mi:ss')) and
315 fnd_date.canonical_to_date(to_char(p_end_date,'yyyy/mm/dd hh24:mi:ss')) and
316 fai1.context1 = fai2.context1 and
317 fai2.user_entity_id = l_roe_payroll_id and
318 paa.assignment_id = paf.assignment_id and
319 fnd_date.canonical_to_date(fai1.value) between
320 paf.effective_start_date and
321 paf.effective_end_date and
322 paf.assignment_type = 'E' and
323 ppr.payroll_action_id = p_pactid
324 AND ppr.chunk_number = p_chunk
325 AND paf.person_id = ppr.person_id;
326
327 cursor cur_locked_action_id(l_assignment_id ff_archive_items.value%TYPE,
328 l_gre_id ff_archive_items.value%TYPE,
329 l_payroll_id ff_archive_items.value%TYPE) is
330 select
331 paa.assignment_action_id locked_action_id
332 from
333 pay_payroll_actions ppa,
334 pay_assignment_actions paa,
335 ff_archive_items fai1,
336 ff_archive_items fai2
337 where
338 ppa.report_type = 'ROE' and
339 ppa.report_category = 'ROEC' and
340 ppa.report_qualifier = 'ROEQ' and
341 ppa.payroll_action_id = paa.payroll_action_id and
342 paa.tax_unit_id = l_gre_id and
343 paa.assignment_id = l_assignment_id and
344 paa.assignment_action_id = fai1.context1 and
345 fai1.user_entity_id = l_roe_date_id and
346 fnd_date.canonical_to_date(fai1.value) between
347 fnd_date.canonical_to_date(to_char(p_start_date,'yyyy/mm/dd hh24:mi:ss')) and
348 fnd_date.canonical_to_date(to_char(p_end_date,'yyyy/mm/dd hh24:mi:ss')) and
349 fai1.context1 = fai2.context1 and
350 fai2.user_entity_id = l_roe_payroll_id and
351 fai2.value = l_payroll_id;
352
353 l_locked_action_id pay_assignment_actions.assignment_action_id%TYPE;
354
355 cursor cur_already_locked is
356 select 'x' from pay_action_interlocks
357 where locked_action_id = l_locked_action_id;
358
359 dummy varchar2(1);
360 lb_range_person BOOLEAN;
361 p_assignment_set number;
362 begin
363 -- Get the report parameters. These define the report being run.
364
365 hr_utility.set_location( 'pay_ca_mag_roe.create_assignment_act',10);
366
367 get_report_parameters(
368 p_pactid,
369 p_start_date,
370 p_end_date,
371 p_person_id,
372 p_assignment_set
373 );
374
375
376
377 hr_utility.set_location( 'pay_ca_mag_roe.create_assignment_act',20);
378
379 for cur_ass_id in 1..4 loop
380
381 if cur_ass_id = 1 then
382 l_roe_date_id := fun_user_entity_id('ROE_DATE');
383 hr_utility.set_location( 'pay_ca_mag_roe.create_assignment_act',30);
384 elsif cur_ass_id = 2 then
385 l_roe_assignment_id := fun_user_entity_id('ROE_ASSIGNMENT_ID');
386 hr_utility.set_location( 'pay_ca_mag_roe.create_assignment_act',40);
387 elsif cur_ass_id = 3 then
388 l_roe_gre_id := fun_user_entity_id('ROE_GRE_ID');
389 hr_utility.set_location( 'pay_ca_mag_roe.create_assignment_act',50);
390 elsif cur_ass_id = 4 then
391 l_roe_payroll_id := fun_user_entity_id('ROE_PAYROLL_ID');
392 hr_utility.set_location( 'pay_ca_mag_roe.create_assignment_act',70);
393 end if;
394
395 end loop;
396
397 lb_range_person := pay_ac_utility.range_person_on(
398 p_report_type => 'MAG_ROE'
399 ,p_report_format => 'MAG_ROEF'
400 ,p_report_qualifier => 'MAG_ROEQ'
401 ,p_report_category => 'MAG_ROEC');
402
403 if lb_range_person then
404 open cur_assignment_action_range;
405 else
406 open cur_assignment_action;
407 end if;
408
409 loop
410 if lb_range_person then
411 fetch cur_assignment_action_range into l_assignment_id, l_gre_id,l_payroll_id;
412 exit when cur_assignment_action_range%notfound;
413 else
414 fetch cur_assignment_action into l_assignment_id, l_gre_id,l_payroll_id;
415 exit when cur_assignment_action%notfound;
416 end if;
417
418 hr_utility.set_location('cur_assignment_action',10);
419
420 for j in cur_locked_action_id(l_assignment_id,
421 l_gre_id,
422 l_payroll_id) loop
423
424 l_locked_action_id := j.locked_action_id;
425
426 open cur_already_locked ;
427 fetch cur_already_locked
428 into dummy;
429
430 if cur_already_locked%NOTFOUND then
431
432 close cur_already_locked ;
433
434 open cur_assignment_action_id;
435 fetch cur_assignment_action_id
436 into lockingactid;
437 close cur_assignment_action_id;
438
439 -- insert into pay_assignment_actions.
440
441 hr_nonrun_asact.insact(lockingactid,l_assignment_id,
442 p_pactid,p_chunk,l_gre_id);
443
444 hr_utility.set_location('assignment action creation',30);
445
446
447 hr_nonrun_asact.insint(lockingactid, l_locked_action_id);
448
449 hr_utility.set_location('Assignment action interlock',40);
450
451 else
452
453 close cur_already_locked;
454
455 end if;
456
457 end loop;
458
459 end loop;
460 if lb_range_person then
461 close cur_assignment_action_range;
462 else
463 close cur_assignment_action;
464 end if;
465
466 end;
467
468 end create_assignment_act;
469
470
471 end pay_ca_mag_roe;