[Home] [Help]
PACKAGE BODY: APPS.PER_EVS_MAG_REPORT
Source
1 PACKAGE BODY per_evs_mag_report AS
2 /* $Header: peevsmag.pkb 120.9.12000000.3 2007/03/08 10:37:53 rpasumar ship $ */
3
4 ----
5 -- Package Variables
6 --
7 g_package VARCHAR2(33) := 'per_evs_mag_report.';
8 g_file_id UTL_FILE.FILE_TYPE;
9 g_file_name VARCHAR2(40);
10
11 --
12 --
13 -- Global variables representing parameters passed by PYUGEN
14 --
15 g_start_date VARCHAR2(11);
16 g_end_date VARCHAR2(11);
17 g_tax_unit_id VARCHAR2(30);
18 g_evs_category VARCHAR2(20);
19 g_business_group_id NUMBER;
20
21
22 -- ----------------------------------------------------------------------------
23 -- Sets up global list of parameters
24 -- ----------------------------------------------------------------------------
25 --
26 PROCEDURE get_parameters( p_payroll_action_id IN NUMBER ) IS
27 l_proc varchar2(40) := g_package || 'get_parameters';
28 --
29 BEGIN
30 --hr_utility.trace_on(NULL,'EVS');
31 hr_utility.set_location(l_proc,10);
32 --
33 -- If parameters haven't already been set, then set them
34 --
35 IF (g_business_group_id IS NULL) THEN
36 hr_utility.set_location(l_proc,20);
37 --
38 SELECT
39 ppa.business_group_id
40 ,nvl(pay_core_utils.get_parameter('END_DATE',ppa.legislative_parameters), to_char(sysdate,'YYYY') || '/12/31')
41 ,nvl(pay_core_utils.get_parameter('START_DATE',ppa.legislative_parameters),to_char(sysdate,'YYYY/MM/DD'))
42 /*,pay_core_utils.get_parameter('STATE_DATE',ppa.legislative_parameters) */
43 ,pay_core_utils.get_parameter('TAX_UNIT_ID',ppa.legislative_parameters)
44 ,pay_core_utils.get_parameter('EVS_CATEGORY',ppa.legislative_parameters)
45 INTO
46 g_business_group_id
47 ,g_end_date
48 ,g_start_date
49 ,g_tax_unit_id
50 ,g_evs_category
51 FROM pay_payroll_actions ppa
52 WHERE payroll_action_id = p_payroll_action_id;
53 --
54 --
55 hr_utility.trace('g_business_group_id : ' || g_business_group_id);
56 hr_utility.trace('g_start_date : ' || g_start_date);
57 hr_utility.trace('g_end_date : ' || g_end_date);
58 hr_utility.trace('g_tax_unit_id : ' || g_tax_unit_id);
59
60 END IF;
61 hr_utility.set_location(l_proc,30);
62 --
63 END get_parameters;
64
65 -------------------------------------------------------------------------------
66 -- range_cursor
67 ------------------------------------------------------------------------------
68 PROCEDURE range_cursor (pactid IN NUMBER, sqlstr OUT NOCOPY VARCHAR2) IS
69 --
70 l_proc VARCHAR2(40) := g_package || 'range_cursor';
71
72 CURSOR c_gre(p_business_group_id IN NUMBER) IS
73 SELECT hou.organization_id organization_id,
74 hou.name org_name
75 FROM hr_all_organization_units hou,
76 hr_organization_information hoi
77 WHERE hou.business_group_id = p_business_group_id
78 AND hou.organization_id = hoi.organization_id
79 AND hoi.org_information_context = 'CLASS'
80 AND hoi.org_information1 = 'HR_LEGAL';
81
82 CURSOR c_get_requester_code(p_organization_id IN NUMBER) IS
83 SELECT hoi.org_information1 requester_code,
84 hou.name org_name
85 FROM hr_all_organization_units hou,
86 hr_organization_information hoi
87 WHERE hoi.organization_id = hou.organization_id
88 AND hoi.organization_id = p_organization_id
89 AND hoi.org_information_context = 'EVS Filing';
90
91 CURSOR c_gre_name(p_organization_id IN NUMBER) IS
92 SELECT hou.name org_name
93 FROM hr_all_organization_units hou
94 WHERE hou.organization_id = p_organization_id;
95
96
97 l_text VARCHAR(2000);
98 l_requester_code VARCHAR2(200);
99 l_org_name VARCHAR2(2000);
100 l_gre_name VARCHAR2(2000);
101
102 BEGIN
103
104 hr_utility.set_location(l_proc,10);
105 get_parameters(p_payroll_action_id => pactid);
106
107
108 IF g_tax_unit_id IS NULL THEN
109
110 --Fetching all the GREs.
111 FOR i IN c_gre(g_business_group_id) LOOP
112
113 OPEN c_gre_name(i.organization_id);
114 FETCH c_gre_name INTO l_gre_name;
115 CLOSE c_gre_name;
116
117 OPEN c_get_requester_code(i.organization_id);
118 FETCH c_get_requester_code INTO l_requester_code,l_org_name;
119 IF c_get_requester_code%NOTFOUND THEN
120 l_text := 'ERROR:Requester Identification Code is a mandatory field for the report. '||
121 'Please enter Requester Identification Code in EVS Filing for '||l_gre_name;
122 fnd_file.put_line(fnd_file.LOG, l_text);
123 ELSE
124 IF l_requester_code IS NULL THEN
125 l_text := 'ERROR:Requester Identification Code is a mandatory field for the report. '||
126 'Please enter Requester Identification Code in EVS Filing for '||l_gre_name;
127 fnd_file.put_line(fnd_file.LOG, l_text);
128 END IF;
129 END IF;
130 CLOSE c_get_requester_code;
131
132 END LOOP;
133 ELSE
134 OPEN c_gre_name(g_tax_unit_id);
135 FETCH c_gre_name INTO l_gre_name;
136 CLOSE c_gre_name;
137
138 OPEN c_get_requester_code(g_tax_unit_id);
139 FETCH c_get_requester_code INTO l_requester_code,l_org_name;
140 IF c_get_requester_code%NOTFOUND THEN
141 l_text := 'ERROR:Requester Identification Code is a mandatory field for the report. '||
142 'Please enter Requester Identification Code in EVS Filing for '||l_gre_name;
143 fnd_file.put_line(fnd_file.LOG, l_text);
144 ELSE
145 IF l_requester_code IS NULL THEN
146 l_text := 'ERROR:Requester Identification Code is a mandatory field for the report. '||
147 'Please enter Requester Identification Code in EVS Filing for '||l_gre_name;
148 fnd_file.put_line(fnd_file.LOG, l_text);
149 END IF;
150 END IF;
151 CLOSE c_get_requester_code;
152
153 END IF;
154
155
156 sqlstr :=
157
158 -- Bug# 5687781
159
160 'SELECT /*+ INDEX(hsck,HR_SOFT_CODING_KEYFLEX_PK),
161 INDEX(HR_ORGANIZATION_UNITS_PK,hou)*/
162 DISTINCT ppf.person_id
163 FROM per_all_people_f ppf
164 ,per_all_assignments_f paf
165 ,hr_soft_coding_keyflex hsck
166 ,hr_organization_units hou
167 ,hr_organization_information hoi
168 WHERE paf.assignment_type = ''E''
169 AND paf.primary_flag = ''Y''
170 AND paf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
171 AND paf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
172 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
173 AND paf.person_id = ppf.person_id
174 AND ppf.effective_start_date <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
175 AND ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
176 And ppf.business_group_id +0 = ' ||g_business_group_id || '
177 AND hou.business_group_id + 0 = ' ||g_business_group_id || '
178 AND hou.date_from <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
179 AND nvl(hou.date_to,to_date(''4712-12-31'',''YYYY-MM-DD''))
180 >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
181 and hsck.segment1= nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
182 AND ppf.business_group_id = hou.business_group_id
183 AND hou.organization_id = hoi.organization_id
184 AND hoi.org_information_context = ''CLASS''
185 AND hoi.org_information1 = ''HR_LEGAL''
186 AND hoi.org_information2 = ''Y''
187 AND :payroll_action_id is not NULL
188 ORDER BY ppf.person_id';
189
190 /*
191 -- Bug: 5212175
192
193 'select distinct ppf.person_id
194 from per_all_people_f ppf
195 ,per_all_assignments_f paf
196 ,hr_soft_coding_keyflex hsck
197 ,hr_organization_units hou
198 ,hr_organization_information hoi
199 where paf.assignment_type = ''E''
200 and paf.primary_flag = ''Y''
201 and paf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
202 and paf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
203 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
204 and paf.person_id = ppf.person_id
205 And ppf.business_group_id +0 = ' ||g_business_group_id || '
206 and ppf.effective_start_date <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
207 and ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
208 and ppf.business_group_id = hou.business_group_id
209 and hou.date_from <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
210 and nvl(hou.date_to,to_date(''4712-12-31'',''YYYY-MM-DD''))
211 >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
212 and hsck.segment1= nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
213 and hou.organization_id = hoi.organization_id
214 and hoi.org_information_context = ''CLASS''
215 and hoi.org_information1 = ''HR_LEGAL''
216 and hoi.org_information2 = ''Y''
217 and :payroll_action_id is not NULL
218 order by ppf.person_id'; */
219
220 /*
221 'select distinct ppf.person_id
222 from
223 per_people_f ppf
224 ,hr_soft_coding_keyflex hsck
225 ,per_assignments_f paf
226 where ppf.person_id = paf.person_id
227 and ppf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
228 and ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
229 and paf.effective_start_date <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
230 and paf.effective_end_date >= to_date('''|| g_start_date||''',''YYYY/MM/DD/'')
231 and hsck.segment1 in
232 (
233 select distinct hsck2.segment1
234 from hr_organization_information hoi
235 ,hr_organization_units hou
236 ,hr_soft_coding_keyflex hsck2
237 where
238 hou.business_group_id +0 = ' || g_business_group_id || '
239 and hsck2.segment1 = nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
240 and hoi.organization_id = hou.organization_id
241 and hoi.org_information_context = ''CLASS''
242 and hoi.org_information1 = ''HR_LEGAL''
243 and hoi.org_information2 = ''Y''
244 and hou.date_from <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
245 and nvl(hou.date_to,to_date(''4712-12-31'',''YYYY-MM-DD''))
246 >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
247 )
248 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
249 and paf.assignment_type = ''E''
250 and paf.primary_flag = ''Y''
251 And ppf.business_group_id +0 = ' ||g_business_group_id || '
252 and :payroll_action_id is not NULL
253 order by ppf.person_id';
254 */
255
256
257 hr_utility.trace('RK Modified SQL: ' || sqlstr);
258
259 END range_cursor;
260
261 -- -----------------------------------------------------------------------------
262 -- Returns list of people to be processed
263 -- -----------------------------------------------------------------------------
264 --
265 PROCEDURE action_creation(
266 pactid IN NUMBER,
267 stperson IN NUMBER,
268 endperson IN NUMBER,
269 chunk IN NUMBER ) IS
270
271 --
272 -- New Hire Only
273 --
274 CURSOR c_actions_nh
275 (
276 pactid number,
277 stperson number,
278 endperson number
279 ) is
280 select distinct paf.assignment_id
281 ,hsck.segment1
282 from
283 per_people_f ppf
284 ,hr_soft_coding_keyflex hsck
285 ,per_assignments_f paf
286 ,per_periods_of_service pps
287 where ppf.person_id = pps.person_id
288 and pps.date_start
289 between to_date(g_start_date,'YYYY/MM/DD/')
290 and to_date(g_end_date,'YYYY/MM/DD/')
291 and ppf.effective_start_date =
292 (select max(ppf2.effective_start_date)
293 from per_people_f ppf2
294 where ppf2.person_id = ppf.person_id
295 and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
296 and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
297 )
298 and ppf.person_id = paf.person_id
299 /* and pps.date_start = paf.effective_start_date */
300 and hsck.segment1 in
301 (
302 select distinct hsck2.segment1
303 from
304 hr_organization_information hoi
305 ,hr_organization_units hou
306 ,hr_soft_coding_keyflex hsck2
307 where
308 hou.business_group_id +0 = g_business_group_id
309 and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
310 and hoi.organization_id = hou.organization_id
311 and hoi.org_information_context = 'CLASS'
312 and hoi.org_information1 = 'HR_LEGAL'
313 and hoi.org_information2 = 'Y'
314 and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
315 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
316 >= to_date(g_start_date,'YYYY/MM/DD/')
317 )
318 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
319 and paf.assignment_type = 'E'
320 and paf.primary_flag = 'Y'
321 And ppf.business_group_id +0 = g_business_group_id
322 and paf.person_id between stperson and endperson
323 order by paf.assignment_id;
324
325
326 --
327 -- Employee Only
328 --
329 CURSOR c_actions_ee
330 (
331 pactid number,
332 stperson number,
333 endperson number
334 ) is
335
336 select /*+ index(hou,HR_ORGANIZATION_UNITS_FK1)*/
337 distinct paf.assignment_id,
338 hsck.segment1
339 from per_all_people_f ppf ,
340 per_all_assignments_f paf,
341 hr_soft_coding_keyflex hsck,
342 hr_all_organization_units hou,
343 hr_organization_information hoi,
344 per_assignment_status_types past
345 where paf.assignment_type = 'E'
346 and paf.primary_flag = 'Y'
347 and paf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
348 and paf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
349 and paf.person_id between stperson and endperson
350 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
351 and paf.assignment_status_type_id = past.assignment_status_type_id
352 and past.per_system_status = 'ACTIVE_ASSIGN'
353 and paf.person_id = ppf.person_id
354 and ppf.current_employee_flag = 'Y'
355 and ppf.effective_start_date = (select max(ppf2.effective_start_date)
356 from per_all_people_f ppf2
357 where ppf.person_id = ppf2.person_id
358 and ppf2.current_employee_flag = 'Y'
359 and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
360 and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
361 )
362 and ppf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
363 and ppf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
364 and ppf.business_group_id +0 = g_business_group_id
365 and hou.business_group_id + 0 = g_business_group_id
366 and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
367 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
368 >= to_date(g_start_date,'YYYY/MM/DD/')
369 and hsck.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
370 and ppf.business_group_id = hou.business_group_id
371 and hou.organization_id = hoi.organization_id
372 and hoi.org_information_context = 'CLASS'
373 and hoi.org_information1 = 'HR_LEGAL'
374 and hoi.org_information2 = 'Y'
375 order by paf.assignment_id;
376
377 /* commented for the bug# 5344584(Base bug# 5212175) */
378 /* select distinct paf.assignment_id
379 ,hsck.segment1
380 from
381 per_people_f ppf
382 ,hr_soft_coding_keyflex hsck
383 ,per_assignments_f paf
384 ,per_periods_of_service pps
385 ,per_assignment_status_types past
386 where ppf.person_id = paf.person_id
387 and ppf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
388 and ppf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
389 and paf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
390 and paf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
391 and ppf.current_employee_flag = 'Y'
392 and ppf.effective_start_date =
393 (select max(ppf2.effective_start_date)
394 from per_people_f ppf2
395 where ppf2.person_id = ppf.person_id
396 and ppf2.current_employee_flag = 'Y'
397 and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
398 and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
399 )
400 and hsck.segment1 in
401 (
402 select distinct hsck2.segment1
403 from hr_organization_information hoi
404 ,hr_organization_units hou
405 ,hr_soft_coding_keyflex hsck2
406 where
407 hou.business_group_id +0 = g_business_group_id
408 and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
409 and hoi.organization_id = hou.organization_id
410 and hoi.org_information_context = 'CLASS'
411 and hoi.org_information1 = 'HR_LEGAL'
412 and hoi.org_information2 = 'Y'
413 and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
414 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
415 >= to_date(g_start_date,'YYYY/MM/DD/')
416 )
417 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
418 and paf.assignment_type = 'E'
419 and paf.primary_flag = 'Y'
420 and paf.assignment_status_type_id = past.assignment_status_type_id
421 And past.per_system_status = 'ACTIVE_ASSIGN'
422 And ppf.business_group_id +0 = g_business_group_id
423 and paf.person_id between stperson and endperson
424 order by paf.assignment_id;
425 */
426 /* commented for bug# 5687781
427 select distinct paf.assignment_id, hsck.segment1
428 from per_all_people_f ppf
429 ,per_all_assignments_f paf
430 ,hr_soft_coding_keyflex hsck
431 ,hr_organization_units hou
432 ,hr_organization_information hoi
433 ,per_assignment_status_types past
434 where paf.assignment_type = 'E'
435 and paf.primary_flag = 'Y'
436 and paf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
437 and paf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
438 and paf.person_id between stperson and endperson
439 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
440 and paf.assignment_status_type_id = past.assignment_status_type_id
441 and past.per_system_status = 'ACTIVE_ASSIGN'
442 and paf.person_id = ppf.person_id
443 and ppf.current_employee_flag = 'Y'
444 and ppf.effective_start_date = (select max(ppf2.effective_start_date)
445 from per_all_people_f ppf2
446 where ppf.person_id = ppf2.person_id
447 and ppf2.current_employee_flag = 'Y'
448 and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
449 and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
450 )
451 and ppf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
452 and ppf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
453 and ppf.business_group_id +0 = g_business_group_id
454 and ppf.business_group_id = hou.business_group_id
455 and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
456 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
457 >= to_date(g_start_date,'YYYY/MM/DD/')
458 and hsck.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
459 and hou.organization_id = hoi.organization_id
460 and hoi.org_information_context = 'CLASS'
461 and hoi.org_information1 = 'HR_LEGAL'
462 and hoi.org_information2 = 'Y'
463 order by paf.assignment_id;
464 */
465
466 --
467 -- Retiree Only
468 --
469 CURSOR c_actions_rt
470 (
471 pactid number,
472 stperson number,
473 endperson number
474 ) is
475 select distinct paf.assignment_id
476 ,hsck.segment1
477 from
478 per_people_f ppf
479 ,hr_soft_coding_keyflex hsck
480 ,per_assignments_f paf
481 ,per_periods_of_service pps
482 ,per_person_type_usages_f ptu
483 ,per_person_types ppt
484 where ppf.person_id = pps.person_id
485 and pps.actual_termination_date is not NULL
486 and pps.actual_termination_date
487 between to_date(g_start_date,'YYYY/MM/DD/')
488 and to_date(g_end_date,'YYYY/MM/DD/')
489 and pps.leaving_reason = 'R'
490 and ppf.person_id = ptu.person_id
491 and ptu.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
492 and ptu.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
493 and paf.effective_start_date <= to_date( g_end_date,'YYYY/MM/DD/')
494 and ppt.person_type_id = ptu.person_type_id
495 and ppt.system_person_type = 'RETIREE'
496 and ppf.effective_start_date =
497 (select max(ppf2.effective_start_date)
498 from per_people_f ppf2
499 where ppf2.person_id = ppf.person_id
500 and ppf2.current_employee_flag is null
501 )
502 and ppf.person_id = paf.person_id
503 and paf.effective_start_date =
504 (select max(paf2.effective_start_date)
505 from per_assignments_f paf2
506 where paf.assignment_id = paf2.assignment_id
507 )
508 and hsck.segment1 in
509 (
510 select distinct hsck2.segment1
511 from hr_organization_information hoi
512 ,hr_organization_units hou
513 ,hr_soft_coding_keyflex hsck2
514 where
515 hou.business_group_id +0 = g_business_group_id
516 and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
517 and hoi.organization_id = hou.organization_id
518 and hoi.org_information_context = 'CLASS'
519 and hoi.org_information1 = 'HR_LEGAL'
520 and hoi.org_information2 = 'Y'
521 and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
522 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
523 >= to_date(g_start_date,'YYYY/MM/DD/')
524 )
525 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
526 and paf.assignment_type = 'E'
527 and paf.primary_flag = 'Y'
528 And ppf.business_group_id +0 = g_business_group_id
529 and paf.person_id between stperson and endperson
530 order by paf.assignment_id;
531
532 --
533 lockingactid NUMBER;
534 l_proc varchar2(40) := g_package || 'action_creation';
535 --
536 BEGIN
537 --
538 --hr_utility.trace_on(NULL,'EVS');
539 hr_utility.set_location('Entering.. ' || l_proc,10);
540 get_parameters( p_payroll_action_id => pactid );
541
542 hr_utility.trace('g_business_group_id :'||to_char(g_business_group_id));
543 hr_utility.trace('g_start_date :'||to_char(g_start_date));
544 hr_utility.trace('g_end_date :'||to_char(g_end_date));
545 hr_utility.trace('Stperson :'||to_char(stperson));
546 hr_utility.trace('Endperson :'||to_char(endperson));
547 hr_utility.trace('tax_unit_id :'||to_char(g_tax_unit_id));
548
549 hr_utility.trace('g_evs_category : ' || g_evs_category);
550
551 if g_evs_category = 'EMPLOYEE' then
552 hr_utility.set_location(l_proc,20);
553 for asgrec in c_actions_ee(pactid,stperson, endperson) loop
554 hr_utility.trace('RK in c_actions_ee cursor');
555 SELECT pay_assignment_actions_s.nextval
556 INTO lockingactid
557 FROM dual;
558 -- insert the action record.
559 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk,
560 asgrec.segment1);
561 --
562 end loop;
563 elsif g_evs_category = 'NEWHIRE' then
564 hr_utility.set_location(l_proc,30);
565 hr_utility.trace('g_evs_category = NEWHIRE Satisfied ');
566 for asgrec in c_actions_nh(pactid,stperson, endperson) loop
567
568 SELECT pay_assignment_actions_s.nextval
569 INTO lockingactid
570 FROM dual;
571
572 -- insert the action record.
573 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk,
574 asgrec.segment1);
575 hr_utility.trace('Created New Asg_Action: '||to_char(lockingactid));
576 hr_utility.trace('Asg_id: '||to_char(asgrec.assignment_id));
577 hr_utility.trace('GRE: '||asgrec.segment1);
578 --
579 end loop;
580 elsif g_evs_category = 'RETIREE' then
581 hr_utility.set_location(l_proc,40);
582 for asgrec in c_actions_rt(pactid,stperson, endperson) loop
583
584 SELECT pay_assignment_actions_s.nextval
585 INTO lockingactid
586 FROM dual;
587
588 -- insert the action record.
589 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk,
590 asgrec.segment1);
591 --
592 end loop;
593 elsif g_evs_category = 'EMPRTR' then
594 hr_utility.set_location(l_proc,50);
595 for asgrec in c_actions_ee(pactid,stperson, endperson) loop
596
597 SELECT pay_assignment_actions_s.nextval
598 INTO lockingactid
599 FROM dual;
600
601 -- insert the action record.
602 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk,
603 asgrec.segment1);
604 --
605 end loop;
606 end if;
607
608 --
609 hr_utility.set_location('Leaving.. ' || l_proc,60);
610 /* hr_utility.trace_off; */
611 END action_creation;
612 --
613
614 -- ----------------------------------------------------------------------------
615 -- Initialization - sets up global parameters
616 -- ----------------------------------------------------------------------------
617 --
618 PROCEDURE init_code( p_payroll_action_id IN NUMBER) IS
619 --
620 --
621 l_test VARCHAR2(20);
622 --
623 BEGIN
624 --
625 --
626 get_parameters( p_payroll_action_id => p_payroll_action_id );
627 --
628 --
629 END init_code;
630
631
632
633 ---------------------------------------------------------------------------
634 -- The following is old version code using UTL_FILE
635 ---------------------------------------------------------------------------
636
637
638
639 -- ------------------------- GET_ROOT_DIR ---------------------------------
640 -- Description: Opens the specified file in the named location
641 --
642 -- Input Parameters
643 -- p_path - utl_file_dir directores
644 --
645 --
646 -- Output Parameters
647 -- l_directory - output directory
648 --
649 -- ------------------------------------------------------------------------
650 FUNCTION get_root_dir
651 (p_path IN VARCHAR2
652 )
653 RETURN VARCHAR2
654 IS
655
656 l_proc varchar2(72);
657 BEGIN
658 l_proc := g_package||'get_root_dir';
659
660 hr_utility.set_location('Entering:' || l_proc,10);
661
662 IF INSTR(p_path,',',1) = 0 THEN
663 IF INSTR(p_path,';',1) = 0 THEN
664 RETURN SUBSTR(p_path , 1 ,LENGTH(p_path));
665 ELSE
666 RETURN SUBSTR(p_path , 1 ,INSTR(p_path,';',1)-1);
667 END IF;
668 ELSE
669 RETURN SUBSTR(p_path , 1 ,INSTR(p_path,',',1)-1);
670 END IF;
671 hr_utility.set_location('Leaving:' || l_proc,20);
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 hr_utility.set_location(l_proc || substr(sqlerrm,1,50),999);
676 fnd_file.put_line(fnd_file.log,SQLERRM);
677 END get_root_dir;
678
679 ---------------------------------------------------------------------------
680 -- EVS_MAG_REPORT
681 -- Description: Call evs_put_record foreach report_category
682 --
683 ---------------------------------------------------------------------------
684 procedure evs_mag_report
685 (p_path in varchar2
686 ,p_report_category in varchar2
687 ,p_user_control_data in varchar2
688 ,p_requester_id_code in varchar2
689 ,p_business_group_id in number
690 ,p_tax_unit_id in number
691 ,p_start_date in date
692 ,p_end_date in date
693 ,p_count in number
694 ,p_media_type in varchar2
695 ,p_gre_count out nocopy number
696 )
697 is
698 --
699 -- Define cursor
700 --
701 --
702 -- All Employee
703 --
704 cursor csr_get_ee_info(p_tax_unit_id in number) is
705 select
706 distinct ppf.PERSON_ID -- BUG4084819
707 ,substr(ppf.LAST_NAME,1,13) last_name
708 ,substr(ppf.MIDDLE_NAMES,1,7) middle_name
709 ,substr(ppf.FIRST_NAME,1,10) first_name
710 ,ppf.NATIONAL_IDENTIFIER
711 ,ppf.DATE_OF_BIRTH
712 ,substr(ppf.SEX,1,1) GENDER
713 --,paf.ASSIGNMENT_ID
714
715 From
716 per_people_f ppf
717 ,hr_soft_coding_keyflex hsck
718 ,per_assignments_f paf
719 ,per_periods_of_service pps
720 ,per_assignment_status_types past
721 Where
722 pps.person_id = ppf.person_id
723 and ppf.person_id = paf.person_id
724 and ppf.effective_start_date <= p_end_date
725 and ppf.effective_end_date >= p_start_date
726 and paf.effective_start_date <= p_end_date
727 and paf.effective_end_date >= p_start_date
728 and ppf.current_employee_flag = 'Y'
729 and ppf.effective_start_date =
730 (select max(ppf2.effective_start_date)
731 from per_people_f ppf2
732 where ppf2.person_id = ppf.person_id
733 and ppf2.current_employee_flag = 'Y'
734 and ppf2.effective_start_date <= p_end_date
735 and ppf2.effective_end_date >= p_start_date
736 )
737 And hsck.segment1 = to_char(p_tax_unit_id)
738 And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
739 And paf.assignment_type = 'E'
740 And paf.primary_flag = 'Y'
741 And paf.assignment_status_type_id = past.assignment_status_type_id
742 And past.per_system_status = 'ACTIVE_ASSIGN'
743 And ppf.business_group_id +0 = p_business_group_id
744 Order by national_identifier ;
745
746 --
747 -- New Hires only
748 --
749 cursor csr_get_nh_info(p_tax_unit_id in number) is
750 select
751 distinct ppf.PERSON_ID
752 ,substr(ppf.LAST_NAME,1,13) last_name
753 ,substr(ppf.MIDDLE_NAMES,1,7) middle_name
754 ,substr(ppf.FIRST_NAME,1,10) first_name
755 ,ppf.NATIONAL_IDENTIFIER
756 ,ppf.DATE_OF_BIRTH
757 ,substr(ppf.SEX,1,1) GENDER
758 --,paf.ASSIGNMENT_ID
759
760 From
761 per_people_f ppf
762 ,hr_soft_coding_keyflex hsck
763 ,per_assignments_f paf
764 ,per_periods_of_service pps
765 Where
766 ppf.person_id = pps.person_id
767 and pps.date_start between
768 p_start_date and p_end_date
769 and ppf.effective_start_date =
770 (select max(ppf2.effective_start_date)
771 from per_people_f ppf2
772 where ppf2.person_id = ppf.person_id
773 and ppf2.effective_start_date <= p_end_date
774 and ppf2.effective_end_date >= p_start_date
775 )
776 and ppf.person_id = paf.person_id
777 and pps.date_start = paf.effective_start_date
778 And hsck.segment1 = to_char(p_tax_unit_id)
779 And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
780 And paf.assignment_type = 'E'
781 And paf.primary_flag = 'Y'
782 And ppf.business_group_id +0 = p_business_group_id
783 Order by national_identifier ;
784
785 --
786 -- Retirees only
787 --
788 cursor csr_get_retire_info(p_tax_unit_id in number) is
789 select
790 distinct ppf.PERSON_ID
791 ,substr(ppf.LAST_NAME,1,13) last_name
792 ,substr(ppf.MIDDLE_NAMES,1,7) middle_name
793 ,substr(ppf.FIRST_NAME,1,10) first_name
794 ,ppf.NATIONAL_IDENTIFIER
795 ,ppf.DATE_OF_BIRTH
796 ,substr(ppf.SEX,1,1) GENDER
797 --,paf.ASSIGNMENT_ID
798
799 From
800 per_people_f ppf
801 ,hr_soft_coding_keyflex hsck
802 ,per_assignments_f paf
803 ,per_periods_of_service pps
804 ,per_person_type_usages_f ptu
805 ,per_person_types ppt
806 Where
807 ppf.person_id = pps.person_id
808 and pps.actual_termination_date is not NULL
809 and pps.actual_termination_date
810 between p_start_date and p_end_date
811 and pps.leaving_reason = 'R'
812 and ppf.person_id = ptu.person_id
813 and ptu.effective_start_date <= p_end_date
814 and ptu.effective_end_date >= p_start_date
815 and ppt.person_type_id = ptu.person_type_id
816 and ppt.system_person_type = 'RETIREE'
817 and ppf.effective_start_date =
818 (select max(ppf2.effective_start_date)
819 from per_people_f ppf2
820 where
821 ppf2.person_id = ppf.person_id
822 and ppf2.current_employee_flag is null
823 )
824 and ppf.person_id = paf.person_id
825 and paf.effective_start_date =
826 (select max(paf2.effective_start_date)
827 from per_assignments_f paf2
828 where
829 paf.assignment_id = paf2.assignment_id
830 )
831 And hsck.segment1 = to_char(p_tax_unit_id)
832 And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
833 And paf.assignment_type = 'E'
834 And paf.primary_flag = 'Y'
835 And ppf.business_group_id +0 = p_business_group_id
836 Order by national_identifier ;
837
838
839 --
840 -- Employees and Retirees
841 --
842 cursor csr_get_ee_and_rtr_info(p_tax_unit_id in number) is
843 select
844 distinct ppf.PERSON_ID
845 ,substr(ppf.LAST_NAME,1,13) last_name
846 ,substr(ppf.MIDDLE_NAMES,1,7) middle_name
847 ,substr(ppf.FIRST_NAME,1,10) first_name
848 ,ppf.NATIONAL_IDENTIFIER
849 ,ppf.DATE_OF_BIRTH
850 ,substr(ppf.SEX,1,1) GENDER
851 --,paf.ASSIGNMENT_ID
852
853 From
854 per_people_f ppf
855 ,hr_soft_coding_keyflex hsck
856 ,per_assignments_f paf
857 ,per_periods_of_service pps
858 ,per_person_type_usages_f ptu
859 ,per_person_types ppt
860 Where
861 ppf.person_id = pps.person_id
862 and pps.actual_termination_date is not NULL
863 and pps.actual_termination_date
864 between p_start_date and p_end_date
865 and pps.leaving_reason = 'R'
866 and ppf.person_id = ptu.person_id
867 and ptu.effective_start_date <= p_end_date
868 and ptu.effective_end_date >= p_start_date
869 and ppt.person_type_id = ptu.person_type_id
870 and ppt.system_person_type = 'RETIREE'
871 and ppf.effective_start_date =
872 (select max(ppf2.effective_start_date)
873 from per_people_f ppf2
874 where
875 ppf2.person_id = ppf.person_id
876 and ppf2.current_employee_flag is null
877 )
878 and ppf.person_id = paf.person_id
879 and paf.effective_start_date =
880 (select max(paf2.effective_start_date)
881 from per_assignments_f paf2
882 where
883 paf.assignment_id = paf2.assignment_id
884 )
885 And hsck.segment1 = to_char(p_tax_unit_id)
886 And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
887 And paf.assignment_type = 'E'
888 And paf.primary_flag = 'Y'
889 And ppf.business_group_id +0 = p_business_group_id
890 and exists
891 (select null
892 from per_people_f ppf2
893 ,per_periods_of_service pps2
894 where
895 ppf2.person_id = ppf.person_id
896 and ppf2.current_employee_flag = 'Y'
897 and pps2.person_id = ppf2.person_id
898 and pps2.date_start
899 between p_start_date and p_end_date
900 and pps2.date_start = ppf2.effective_start_date
901 )
902 Order by national_identifier ;
903
904
905 --
906 -- local variable
907 --
908 l_proc varchar2(72);
909 l_count number;
910 l_gre_count number;
911 l_file_count number;
912 l_report_category varchar2(40);
913 l_multiple_req_indicator varchar2(3);
914 begin
915
916 l_proc := g_package||'evs_mag_report';
917 hr_utility.set_location('Enteriing : ' || l_proc,10);
918 hr_utility.trace('p_path = ' || p_path);
919 hr_utility.trace('p_start_date = ' || p_start_date);
920 hr_utility.trace('p_end_date = ' || p_end_date);
921 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
922 hr_utility.trace('p_report_category = ' || p_report_category);
923 hr_utility.trace('p_user_control_data = ' || p_user_control_data);
924 hr_utility.trace('p_requester_id_code = ' || p_requester_id_code);
925 hr_utility.trace('p_report_category = ' || p_report_category);
926
927
928 l_count := p_count;
929 l_file_count := 0;
930 l_gre_count := 0;
931 l_multiple_req_indicator := to_char(l_file_count + 1);
932
933 if p_report_category is NULL then
934 l_report_category := 'NEWHIRE';
935 else
936 l_report_category := p_report_category;
937 end if;
938
939 hr_utility.trace('l_report_category = ' || l_report_category);
940
941 if l_report_category = 'EMPLOYEE' then
942 hr_utility.set_location(l_proc,20);
943 FOR ee_record IN csr_get_ee_info(p_tax_unit_id) LOOP
944 evs_put_record
945 (p_file_id => g_file_id
946 ,p_ssn => ee_record.national_identifier
947 ,p_last_name => ee_record.last_name
948 ,p_first_name => ee_record.first_name
949 ,p_middle_name => ee_record.middle_name
950 ,p_date_of_birth => ee_record.date_of_birth
951 ,p_gender => ee_record.gender
952 ,p_user_control_data => p_user_control_data
953 ,p_requester_id_code => p_requester_id_code
954 ,p_multiple_req_indicator => l_file_count
955 );
956 l_count := l_count + 1;
957 l_gre_count := l_gre_count + 1;
958 hr_utility.trace('l_count = ' || l_count);
959 hr_utility.trace('l_gre_count = ' || l_gre_count);
960 if p_media_type = 'DISKETTE' then
961 if l_count >= 11000 then
962 utl_file.fclose(g_file_id);
963 l_file_count := l_file_count + 1 ;
964 hr_utility.set_location(l_proc,25);
965 g_file_id := utl_file.fopen(p_path, g_file_name || l_file_count, 'w');
966 l_count := 0;
967 end if;
968 end if;
969 END LOOP;
970 elsif l_report_category = 'NEWHIRE' then
971 hr_utility.set_location(l_proc,30);
972 FOR ee_record IN csr_get_nh_info(p_tax_unit_id) LOOP
973 evs_put_record
974 (p_file_id => g_file_id
975 ,p_ssn => ee_record.national_identifier
976 ,p_last_name => ee_record.last_name
977 ,p_first_name => ee_record.first_name
978 ,p_middle_name => ee_record.middle_name
979 ,p_date_of_birth => ee_record.date_of_birth
980 ,p_gender => ee_record.gender
981 ,p_user_control_data => p_user_control_data
982 ,p_requester_id_code => p_requester_id_code
983 ,p_multiple_req_indicator => l_file_count
984 );
985 l_count := l_count + 1;
986 l_gre_count := l_gre_count + 1;
987 hr_utility.trace('l_count = ' || l_count);
988 hr_utility.trace('l_gre_count = ' || l_gre_count);
989 if p_media_type = 'DISKETTE' then
990 if l_count >= 11000 then
991 hr_utility.set_location(l_proc,35);
992 utl_file.fclose(g_file_id);
993 l_file_count := l_file_count + 1 ;
994 g_file_id := utl_file.fopen(p_path, g_file_name || l_file_count, 'w');
995 l_count := 0;
996 end if;
997 end if;
998 END LOOP;
999 elsif l_report_category = 'RETIREE' then
1000 hr_utility.set_location(l_proc,40);
1001 FOR ee_record IN csr_get_retire_info(p_tax_unit_id) LOOP
1002 evs_put_record
1003 (p_file_id => g_file_id
1004 ,p_ssn => ee_record.national_identifier
1005 ,p_last_name => ee_record.last_name
1006 ,p_first_name => ee_record.first_name
1007 ,p_middle_name => ee_record.middle_name
1008 ,p_date_of_birth => ee_record.date_of_birth
1009 ,p_gender => ee_record.gender
1010 ,p_user_control_data => p_user_control_data
1011 ,p_requester_id_code => p_requester_id_code
1012 ,p_multiple_req_indicator => l_file_count
1013 );
1014 l_count := l_count + 1;
1015 l_gre_count := l_gre_count + 1;
1016 hr_utility.trace('l_count = ' || l_count);
1017 hr_utility.trace('l_gre_count = ' || l_gre_count);
1018 if p_media_type = 'DISKETTE' then
1019 if l_count >= 11000 then
1020 hr_utility.set_location(l_proc,45);
1021 utl_file.fclose(g_file_id);
1022 l_file_count := l_file_count + 1 ;
1023 g_file_id := utl_file.fopen(p_path, g_file_name || l_file_count, 'w');
1024 l_count := 0;
1025 end if;
1026 end if;
1027 END LOOP;
1028 elsif l_report_category = 'EMPRTR' then
1029 hr_utility.set_location(l_proc,50);
1030 FOR ee_record IN csr_get_ee_info(p_tax_unit_id) LOOP --BUG3930540
1031 evs_put_record
1032 (p_file_id => g_file_id
1033 ,p_ssn => ee_record.national_identifier
1034 ,p_last_name => ee_record.last_name
1035 ,p_first_name => ee_record.first_name
1036 ,p_middle_name => ee_record.middle_name
1037 ,p_date_of_birth => ee_record.date_of_birth
1038 ,p_gender => ee_record.gender
1039 ,p_user_control_data => p_user_control_data
1040 ,p_requester_id_code => p_requester_id_code
1041 ,p_multiple_req_indicator => l_file_count
1042 );
1043 l_count := l_count + 1;
1044 hr_utility.trace('l_count = ' || l_count);
1045 l_gre_count := l_gre_count + 1;
1046 hr_utility.trace('l_gre_count = ' || l_gre_count);
1047 if p_media_type = 'DISKETTE' then
1048 if l_count >= 11000 then
1049 hr_utility.set_location(l_proc,55);
1050 utl_file.fclose(g_file_id);
1051 l_file_count := l_file_count + 1 ;
1052 g_file_id := utl_file.fopen(p_path, g_file_name || l_file_count, 'w');
1053 l_count := 0;
1054 end if;
1055 end if;
1056 END LOOP;
1057 end if;
1058 p_gre_count := l_gre_count;
1059 hr_utility.set_location('Leaving : ' || l_proc,100);
1060 end evs_mag_report;
1061
1062 ---------------------------------------------------------------------------
1063 -- EVS_PUT_REPORT
1064 -- Description: Output mag file
1065 --
1066 ---------------------------------------------------------------------------
1067 procedure evs_put_record
1068 (p_file_id in utl_file.file_type
1069 ,p_ssn in varchar2
1070 ,p_last_name in varchar2
1071 ,p_first_name in varchar2
1072 ,p_middle_name in varchar2
1073 ,p_date_of_birth in date
1074 ,p_gender in varchar2
1075 ,p_user_control_data in varchar2
1076 ,p_requester_id_code in varchar2
1077 ,p_multiple_req_indicator in varchar2
1078 )
1079 is
1080 --
1081 l_buff varchar2(130); -- XXXXX
1082 l_delimiter varchar2(1);
1083 l_proc varchar2(72);
1084 l_multiple_req_indicator varchar2(3);
1085
1086 begin
1087 l_proc := g_package || 'evs_put_record';
1088 hr_utility.set_location('Entering : ' || l_proc,10);
1089
1090 if p_multiple_req_indicator = 0 then
1091 l_multiple_req_indicator := ' ';
1092 else
1093 l_multiple_req_indicator := p_multiple_req_indicator;
1094 end if;
1095
1096 l_delimiter := fnd_global.local_chr(10);
1097
1098 l_buff :=
1099 -- 1-9
1100 rpad(nvl((substr(p_ssn,1,3) || substr(p_ssn,5,2) || substr(p_ssn,8,4)),' '),9,' ')
1101 -- 10-12
1102 || 'TPV'
1103 -- 13-15
1104 || '214'
1105 -- 16-28
1106 || rpad(p_last_name,13,' ')
1107 -- 29-38
1108 || rpad(nvl(p_first_name,' ') ,10,' ')
1109 -- 39-45
1110 || rpad(nvl(p_middle_name,' '),7,' ')
1111 -- 46-53
1112 || nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ')
1113 -- 54
1114 || nvl(p_gender,' ')
1115 -- 55-89
1116 || rpad(' ',35,' ')
1117 -- 90-103
1118 || rpad(nvl(p_user_control_data,' '),14,' ')
1119 -- 104-123
1120 || rpad(' ',20,' ')
1121 -- 124-127
1122 || p_requester_id_code
1123 -- 128-130
1124 || rpad(nvl(l_multiple_req_indicator,' '),3,' ')
1125 --
1126 || l_delimiter -- BUG4447245
1127 ;
1128 hr_utility.trace('l_buff = ' || l_buff);
1129
1130 fnd_file.put_line
1131 (which => fnd_file.output
1132 ,buff => l_buff
1133 );
1134
1135 utl_file.put(g_file_id,l_buff);
1136 utl_file.fflush(g_file_id); -- XXXXX
1137
1138 hr_utility.set_location('Leavning : ' || l_proc,100);
1139 end evs_put_record;
1140 ------------------------------------------------------------------------------
1141 --
1142 -- EVS_MAG_REPORT_MAIN
1143 -- Description : Electronic EVS Report main routine
1144 --
1145 ------------------------------------------------------------------------------
1146 procedure evs_mag_report_main
1147 (errbuf out nocopy varchar2
1148 ,retcode out nocopy number
1149 --
1150 ,p_start_date in varchar2
1151 ,p_end_date in varchar2
1152 ,p_tax_unit_id in number
1153 ,p_business_group_id in number
1154 ,p_report_category in varchar2
1155 ,p_media_type in varchar2
1156 ) is
1157 --
1158 -- local variables
1159 --
1160 l_proc VARCHAR2(72);
1161 l_requester_id_code varchar2(4);
1162 l_user_control_data varchar2(20); -- BUG3917159
1163 l_multiple_req_indicator varchar2(3);
1164 l_buff varchar2(200);
1165 l_start_date date;
1166 l_end_date date;
1167 l_path varchar2(2000);
1168 l_valid_profile varchar2(2000);
1169 l_delimiter varchar2(1);
1170 l_count number;
1171 l_file_count number;
1172 l_gre_count number;
1173 l_header number;
1174 l_all_count number;
1175 l_gre_name varchar2(200);
1176 l_media_type varchar2(40);
1177
1178 --
1179 -- Define cursor
1180 --
1181 CURSOR csr_valid_profile IS
1182 SELECT value
1183 FROM v$parameter
1184 WHERE name='utl_file_dir';
1185
1186 --
1187 -- Retrieve GREs if gre parameter is blank
1188 --
1189 cursor csr_get_gre is
1190 select distinct hou.name -- BUG4192188
1191 ,hsck.segment1 tax_unit_id
1192 ,hoi2.org_information1 requester_id_code
1193 ,hoi2.org_information2 user_control_data
1194 from hr_organization_information hoi
1195 ,hr_organization_units hou
1196 ,hr_soft_coding_keyflex hsck
1197 ,hr_organization_information hoi2
1198 where hou.business_group_id = p_business_group_id
1199 and hsck.segment1 = to_char(hou.organization_id)
1200 and hoi.organization_id = hou.organization_id
1201 and hoi.org_information_context = 'CLASS'
1202 and hoi.org_information1 = 'HR_LEGAL'
1203 and hoi.org_information2 = 'Y'
1204 and hou.date_from <= l_end_date
1205 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD')) >= l_start_date
1206 and hoi2.organization_id(+) = hou.organization_id
1207 and hoi2.org_information_context(+) = 'EVS Filing'
1208 order by hou.name;
1209
1210 cursor csr_get_org_info is
1211 SELECT
1212 hoi.org_information1 requester_id_code
1213 ,hoi.org_information2 user_control_data
1214 ,hou.name name
1215 FROM
1216 hr_organization_information hoi
1217 ,hr_organization_units hou
1218 WHERE
1219 hoi.organization_id = p_tax_unit_id
1220 AND hoi.org_information_context = 'EVS Filing'
1221 and hoi.organization_id = hou.organization_id
1222 ;
1223
1224 begin
1225
1226 g_package := 'per_evs_mag_report.';
1227 l_proc := g_package||'evs_mag_report_main';
1228 g_file_name := 'EVSREQ2K';
1229 l_file_count := 0;
1230 l_count := 0;
1231 l_all_count := 0;
1232 l_header := 0;
1233 l_gre_count := 0;
1234
1235 hr_utility.set_location('Entering:' || l_proc,10);
1236 --
1237 -- GET UTL_FILE_DIR
1238 --
1239 OPEN csr_valid_profile;
1240 FETCH csr_valid_profile INTO l_valid_profile;
1241 if csr_valid_profile%FOUND then
1242 close csr_valid_profile;
1243 hr_utility.trace('l_valid_profile : ' || l_valid_profile);
1244 l_path := GET_ROOT_DIR(l_valid_profile);
1245 hr_utility.trace('UTL_FILE_DIR : ' || l_path );
1246 else
1247 null;
1248 close csr_valid_profile;
1249 end if;
1250 --
1251 hr_utility.set_location(l_proc,20);
1252
1253 if p_start_date is null then
1254 l_start_date := fnd_date.canonical_to_date(to_char(sysdate,'YYYY/MM/DD'));
1255 else
1256 l_start_date := fnd_date.canonical_to_date(p_start_date);
1257 end if;
1258
1259 if p_end_date is null then
1260 l_end_date := to_date(to_char(sysdate,'YYYY') || '-12-31', 'YYYY-MM-DD');
1261 else
1262 l_end_date := fnd_date.canonical_to_date(p_end_date);
1263 end if;
1264
1265 if p_media_type is NULL then
1266 l_media_type := 'DISKETTE';
1267 else
1268 l_media_type := p_media_type;
1269 end if;
1270
1271 hr_utility.trace('l_start_date = ' || l_start_date);
1272 hr_utility.trace('l_end_date = ' || l_end_date);
1273 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
1274 hr_utility.trace('p_business_group_id = ' || p_business_group_id);
1275 hr_utility.trace('p_report_category = ' || p_report_category);
1276 hr_utility.trace('p_media_type = ' || l_media_type);
1277
1278
1279 if p_tax_unit_id is not NULL then
1280
1281 hr_utility.set_location(l_proc,30);
1282
1283 open csr_get_org_info;
1284 fetch csr_get_org_info into l_requester_id_code
1285 ,l_user_control_data
1286 ,l_gre_name;
1287
1288 if csr_get_org_info%NOTFOUND then
1289 close csr_get_org_info;
1290 select name into l_gre_name
1291 from hr_organization_units
1292 where organization_id = p_tax_unit_id;
1293 fnd_message.set_name('PER','HR_449246_EVS_NO_REQ_ID');
1294 fnd_message.set_token('GRE',l_gre_name);
1295 fnd_message.raise_error;
1296 else
1297 close csr_get_org_info;
1298 end if;
1299
1300 hr_utility.set_location(l_proc,40);
1301
1302 hr_utility.trace('l_requestoer_id_code = ' || l_requester_id_code);
1303 hr_utility.trace('l_user_control_data = ' || l_user_control_data);
1304 hr_utility.trace('p_report_category = ' || p_report_category);
1305
1306 --
1307 -- File Open
1308 --
1309 g_file_id := utl_file.fopen(l_path,g_file_name,'w');
1310
1311 evs_mag_report
1312 (p_path => l_path
1313 ,p_report_category => p_report_category
1314 ,p_user_control_data => l_user_control_data
1315 ,p_requester_id_code => l_requester_id_code
1316 ,p_business_group_id => p_business_group_id
1317 ,p_tax_unit_id => p_tax_unit_id
1318 ,p_start_date => l_start_date
1319 ,p_end_date => l_end_date
1320 ,p_count => l_count
1321 ,p_media_type => l_media_type
1322 ,p_gre_count => l_gre_count
1323 );
1324
1325 hr_utility.set_location(l_proc,50);
1326 utl_file.fclose(g_file_id);
1327 if l_header = 0 then
1328 fnd_file.put_line
1329 (which => fnd_file.log
1330 ,buff => ' '
1331 );
1332 fnd_file.put_line
1333 (which => fnd_file.log
1334 ,buff => ' EVS Report Summary '
1335 );
1336 fnd_file.put_line
1337 (which => fnd_file.log
1338 ,buff => ' '
1339 );
1340 fnd_file.put_line
1341 (which => fnd_file.log
1342 ,buff => ' GRE Name Total number '
1343 );
1344 fnd_file.put_line
1345 (which => fnd_file.log
1346 ,buff => ' ------------------- ------------- '
1347 );
1348 l_header := 1;
1349 end if;
1350 fnd_file.put_line
1351 (which => fnd_file.log
1352 ,buff => ' ' || rpad(l_gre_name,20,' ') || ' ' || to_char(l_gre_count)
1353 );
1354 fnd_file.put_line
1355 (which => fnd_file.log
1356 ,buff => ' '
1357 );
1358 else
1359 hr_utility.set_location(l_proc,60);
1360 --
1361 -- GRE parameter is blank
1362 --
1363
1364 --
1365 -- File Open
1366 --
1367 g_file_id := utl_file.fopen(l_path,g_file_name,'w');
1368
1369 FOR gre_record IN csr_get_gre LOOP
1370 if gre_record.requester_id_code is NULL then
1371 fnd_message.set_name('PER','HR_449246_EVS_NO_REQ_ID');
1372 fnd_message.set_token('GRE',gre_record.name);
1373 fnd_message.raise_error;
1374 end if;
1375
1376 evs_mag_report
1377 (p_path => l_path
1378 ,p_report_category => p_report_category
1379 ,p_user_control_data => gre_record.user_control_data
1380 ,p_requester_id_code => gre_record.requester_id_code
1381 ,p_business_group_id => p_business_group_id
1382 ,p_tax_unit_id => gre_record.tax_unit_id
1383 ,p_start_date => l_start_date
1384 ,p_end_date => l_end_date
1385 ,p_count => l_count
1386 ,p_media_type => l_media_type
1387 ,p_gre_count => l_gre_count
1388 );
1389
1390 hr_utility.set_location(l_proc,70);
1391
1392 if l_header = 0 then
1393 fnd_file.put_line
1394 (which => fnd_file.log
1395 ,buff => ' '
1396 );
1397 fnd_file.put_line
1398 (which => fnd_file.log
1399 ,buff => ' EVS Report Summary '
1400 );
1401 fnd_file.put_line
1402 (which => fnd_file.log
1403 ,buff => ' '
1404 );
1405 fnd_file.put_line
1406 (which => fnd_file.log
1407 ,buff => ' GRE Name Total number '
1408 );
1409 fnd_file.put_line
1410 (which => fnd_file.log
1411 ,buff => ' ------------------- ------------- '
1412 );
1413 l_header := 1;
1414 end if;
1415 hr_utility.set_location(l_proc,71);
1416 fnd_file.put_line
1417 (which => fnd_file.log
1418 ,buff => ' ' || rpad(gre_record.name,20,' ') ||
1419 ' ' || to_char(l_gre_count)
1420 );
1421 l_all_count := l_all_count + l_gre_count;
1422 END LOOP;
1423 hr_utility.set_location(l_proc,72);
1424 fnd_file.put_line
1425 (which => fnd_file.log
1426 ,buff => ' ' || rpad('ALL GREs',20,' ') ||
1427 ' ' || to_char(l_all_count)
1428 );
1429 fnd_file.put_line
1430 (which => fnd_file.log
1431 ,buff => ' '
1432 );
1433 -- utl_file.fclose(g_file_id); -- 08-JUL-2005
1434 hr_utility.set_location(l_proc,74);
1435 end if;
1436 hr_utility.set_location('Leaving..: ' || l_proc,100);
1437
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440 hr_utility.set_location(l_proc || substr(sqlerrm,1,50),999);
1441 fnd_file.put_line(fnd_file.log,SQLERRM);
1442 RAISE;
1443
1444 end evs_mag_report_main;
1445
1446 end per_evs_mag_report;