[Home] [Help]
PACKAGE BODY: APPS.PER_EVS_MAG_REPORT
Source
1 PACKAGE BODY per_evs_mag_report AS
2 /* $Header: peevsmag.pkb 120.11.12010000.2 2009/06/01 11:59:33 kagangul 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 -- Bug # 8528862
1063 -- F_EVS_REM_SPL_CHAR
1064 -- Description : Removes the special character like <'. -> from the input
1065 -- string and returns the remaining string.
1066 ------------------------------------------------------------------------------
1067 FUNCTION f_evs_rem_spl_char(p_input_string IN VARCHAR2)
1068 RETURN VARCHAR2 IS
1069
1070 ls_output_string VARCHAR2(100);
1071
1072 BEGIN
1073 hr_utility.trace('Original string passed : ' || p_input_string);
1074 ls_output_string := translate(p_input_string,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz''. -0123456789',
1075 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
1076 hr_utility.trace('String returned : ' || ls_output_string);
1077 RETURN ls_output_string;
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 RETURN p_input_string;
1081 END f_evs_rem_spl_char;
1082 ---------------------------------------------------------------------------
1083 -- EVS_PUT_REPORT
1084 -- Description: Output mag file
1085 --
1086 ---------------------------------------------------------------------------
1087 procedure evs_put_record
1088 (p_file_id in utl_file.file_type
1089 ,p_ssn in varchar2
1090 ,p_last_name in varchar2
1091 ,p_first_name in varchar2
1092 ,p_middle_name in varchar2
1093 ,p_date_of_birth in date
1094 ,p_gender in varchar2
1095 ,p_user_control_data in varchar2
1096 ,p_requester_id_code in varchar2
1097 ,p_multiple_req_indicator in varchar2
1098 )
1099 is
1100 --
1101 l_buff varchar2(130); -- XXXXX
1102 l_delimiter varchar2(1);
1103 l_proc varchar2(72);
1104 l_multiple_req_indicator varchar2(3);
1105
1106 begin
1107 l_proc := g_package || 'evs_put_record';
1108 hr_utility.set_location('Entering : ' || l_proc,10);
1109
1110 if p_multiple_req_indicator = 0 then
1111 l_multiple_req_indicator := ' ';
1112 else
1113 l_multiple_req_indicator := p_multiple_req_indicator;
1114 end if;
1115
1116 l_delimiter := fnd_global.local_chr(10);
1117
1118 l_buff :=
1119 -- 1-9
1120 rpad(nvl((substr(p_ssn,1,3) || substr(p_ssn,5,2) || substr(p_ssn,8,4)),' '),9,' ')
1121 -- 10-12
1122 || 'TPV'
1123 -- 13-15
1124 || '214'
1125 -- 16-28
1126 || rpad(p_last_name,13,' ')
1127 -- 29-38
1128 || rpad(nvl(p_first_name,' ') ,10,' ')
1129 -- 39-45
1130 || rpad(nvl(p_middle_name,' '),7,' ')
1131 -- 46-53
1132 || nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ')
1133 -- 54
1134 || nvl(p_gender,' ')
1135 -- 55-89
1136 || rpad(' ',35,' ')
1137 -- 90-103
1138 || rpad(nvl(p_user_control_data,' '),14,' ')
1139 -- 104-123
1140 || rpad(' ',20,' ')
1141 -- 124-127
1142 || p_requester_id_code
1143 -- 128-130
1144 || rpad(nvl(l_multiple_req_indicator,' '),3,' ')
1145 --
1146 || l_delimiter -- BUG4447245
1147 ;
1148 hr_utility.trace('l_buff = ' || l_buff);
1149
1150 fnd_file.put_line
1151 (which => fnd_file.output
1152 ,buff => l_buff
1153 );
1154
1155 utl_file.put(g_file_id,l_buff);
1156 utl_file.fflush(g_file_id); -- XXXXX
1157
1158 hr_utility.set_location('Leavning : ' || l_proc,100);
1159 end evs_put_record;
1160 ------------------------------------------------------------------------------
1161 --
1162 -- EVS_MAG_REPORT_MAIN
1163 -- Description : Electronic EVS Report main routine
1164 --
1165 ------------------------------------------------------------------------------
1166 procedure evs_mag_report_main
1167 (errbuf out nocopy varchar2
1168 ,retcode out nocopy number
1169 --
1170 ,p_start_date in varchar2
1171 ,p_end_date in varchar2
1172 ,p_tax_unit_id in number
1173 ,p_business_group_id in number
1174 ,p_report_category in varchar2
1175 ,p_media_type in varchar2
1176 ) is
1177 --
1178 -- local variables
1179 --
1180 l_proc VARCHAR2(72);
1181 l_requester_id_code varchar2(4);
1182 l_user_control_data varchar2(20); -- BUG3917159
1183 l_multiple_req_indicator varchar2(3);
1184 l_buff varchar2(200);
1185 l_start_date date;
1186 l_end_date date;
1187 l_path varchar2(2000);
1188 l_valid_profile varchar2(2000);
1189 l_delimiter varchar2(1);
1190 l_count number;
1191 l_file_count number;
1192 l_gre_count number;
1193 l_header number;
1194 l_all_count number;
1195 l_gre_name varchar2(200);
1196 l_media_type varchar2(40);
1197
1198 --
1199 -- Define cursor
1200 --
1201 CURSOR csr_valid_profile IS
1202 SELECT value
1203 FROM v$parameter
1204 WHERE name='utl_file_dir';
1205
1206 --
1207 -- Retrieve GREs if gre parameter is blank
1208 --
1209 cursor csr_get_gre is
1210 select distinct hou.name -- BUG4192188
1211 ,hsck.segment1 tax_unit_id
1212 ,hoi2.org_information1 requester_id_code
1213 ,hoi2.org_information2 user_control_data
1214 from hr_organization_information hoi
1215 ,hr_organization_units hou
1216 ,hr_soft_coding_keyflex hsck
1217 ,hr_organization_information hoi2
1218 where hou.business_group_id = p_business_group_id
1219 and hsck.segment1 = to_char(hou.organization_id)
1220 and hoi.organization_id = hou.organization_id
1221 and hoi.org_information_context = 'CLASS'
1222 and hoi.org_information1 = 'HR_LEGAL'
1223 and hoi.org_information2 = 'Y'
1224 and hou.date_from <= l_end_date
1225 and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD')) >= l_start_date
1226 and hoi2.organization_id(+) = hou.organization_id
1227 and hoi2.org_information_context(+) = 'EVS Filing'
1228 order by hou.name;
1229
1230 cursor csr_get_org_info is
1231 SELECT
1232 hoi.org_information1 requester_id_code
1233 ,hoi.org_information2 user_control_data
1234 ,hou.name name
1235 FROM
1236 hr_organization_information hoi
1237 ,hr_organization_units hou
1238 WHERE
1239 hoi.organization_id = p_tax_unit_id
1240 AND hoi.org_information_context = 'EVS Filing'
1241 and hoi.organization_id = hou.organization_id
1242 ;
1243
1244 begin
1245
1246 g_package := 'per_evs_mag_report.';
1247 l_proc := g_package||'evs_mag_report_main';
1248 g_file_name := 'EVSREQ2K';
1249 l_file_count := 0;
1250 l_count := 0;
1251 l_all_count := 0;
1252 l_header := 0;
1253 l_gre_count := 0;
1254
1255 hr_utility.set_location('Entering:' || l_proc,10);
1256 --
1257 -- GET UTL_FILE_DIR
1258 --
1259 OPEN csr_valid_profile;
1260 FETCH csr_valid_profile INTO l_valid_profile;
1261 if csr_valid_profile%FOUND then
1262 close csr_valid_profile;
1263 hr_utility.trace('l_valid_profile : ' || l_valid_profile);
1264 l_path := GET_ROOT_DIR(l_valid_profile);
1265 hr_utility.trace('UTL_FILE_DIR : ' || l_path );
1266 else
1267 null;
1268 close csr_valid_profile;
1269 end if;
1270 --
1271 hr_utility.set_location(l_proc,20);
1272
1273 if p_start_date is null then
1274 l_start_date := fnd_date.canonical_to_date(to_char(sysdate,'YYYY/MM/DD'));
1275 else
1276 l_start_date := fnd_date.canonical_to_date(p_start_date);
1277 end if;
1278
1279 if p_end_date is null then
1280 l_end_date := to_date(to_char(sysdate,'YYYY') || '-12-31', 'YYYY-MM-DD');
1281 else
1282 l_end_date := fnd_date.canonical_to_date(p_end_date);
1283 end if;
1284
1285 if p_media_type is NULL then
1286 l_media_type := 'DISKETTE';
1287 else
1288 l_media_type := p_media_type;
1289 end if;
1290
1291 hr_utility.trace('l_start_date = ' || l_start_date);
1292 hr_utility.trace('l_end_date = ' || l_end_date);
1293 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
1294 hr_utility.trace('p_business_group_id = ' || p_business_group_id);
1295 hr_utility.trace('p_report_category = ' || p_report_category);
1296 hr_utility.trace('p_media_type = ' || l_media_type);
1297
1298
1299 if p_tax_unit_id is not NULL then
1300
1301 hr_utility.set_location(l_proc,30);
1302
1303 open csr_get_org_info;
1304 fetch csr_get_org_info into l_requester_id_code
1305 ,l_user_control_data
1306 ,l_gre_name;
1307
1308 if csr_get_org_info%NOTFOUND then
1309 close csr_get_org_info;
1310 select name into l_gre_name
1311 from hr_organization_units
1312 where organization_id = p_tax_unit_id;
1313 fnd_message.set_name('PER','HR_449246_EVS_NO_REQ_ID');
1314 fnd_message.set_token('GRE',l_gre_name);
1315 fnd_message.raise_error;
1316 else
1317 close csr_get_org_info;
1318 end if;
1319
1320 hr_utility.set_location(l_proc,40);
1321
1322 hr_utility.trace('l_requestoer_id_code = ' || l_requester_id_code);
1323 hr_utility.trace('l_user_control_data = ' || l_user_control_data);
1324 hr_utility.trace('p_report_category = ' || p_report_category);
1325
1326 --
1327 -- File Open
1328 --
1329 g_file_id := utl_file.fopen(l_path,g_file_name,'w');
1330
1331 evs_mag_report
1332 (p_path => l_path
1333 ,p_report_category => p_report_category
1334 ,p_user_control_data => l_user_control_data
1335 ,p_requester_id_code => l_requester_id_code
1336 ,p_business_group_id => p_business_group_id
1337 ,p_tax_unit_id => p_tax_unit_id
1338 ,p_start_date => l_start_date
1339 ,p_end_date => l_end_date
1340 ,p_count => l_count
1341 ,p_media_type => l_media_type
1342 ,p_gre_count => l_gre_count
1343 );
1344
1345 hr_utility.set_location(l_proc,50);
1346 utl_file.fclose(g_file_id);
1347 if l_header = 0 then
1348 fnd_file.put_line
1349 (which => fnd_file.log
1350 ,buff => ' '
1351 );
1352 fnd_file.put_line
1353 (which => fnd_file.log
1354 ,buff => ' EVS Report Summary '
1355 );
1356 fnd_file.put_line
1357 (which => fnd_file.log
1358 ,buff => ' '
1359 );
1360 fnd_file.put_line
1361 (which => fnd_file.log
1362 ,buff => ' GRE Name Total number '
1363 );
1364 fnd_file.put_line
1365 (which => fnd_file.log
1366 ,buff => ' ------------------- ------------- '
1367 );
1368 l_header := 1;
1369 end if;
1370 fnd_file.put_line
1371 (which => fnd_file.log
1372 ,buff => ' ' || rpad(l_gre_name,20,' ') || ' ' || to_char(l_gre_count)
1373 );
1374 fnd_file.put_line
1375 (which => fnd_file.log
1376 ,buff => ' '
1377 );
1378 else
1379 hr_utility.set_location(l_proc,60);
1380 --
1381 -- GRE parameter is blank
1382 --
1383
1384 --
1385 -- File Open
1386 --
1387 g_file_id := utl_file.fopen(l_path,g_file_name,'w');
1388
1389 FOR gre_record IN csr_get_gre LOOP
1390 if gre_record.requester_id_code is NULL then
1391 fnd_message.set_name('PER','HR_449246_EVS_NO_REQ_ID');
1392 fnd_message.set_token('GRE',gre_record.name);
1393 fnd_message.raise_error;
1394 end if;
1395
1396 evs_mag_report
1397 (p_path => l_path
1398 ,p_report_category => p_report_category
1399 ,p_user_control_data => gre_record.user_control_data
1400 ,p_requester_id_code => gre_record.requester_id_code
1401 ,p_business_group_id => p_business_group_id
1402 ,p_tax_unit_id => gre_record.tax_unit_id
1403 ,p_start_date => l_start_date
1404 ,p_end_date => l_end_date
1405 ,p_count => l_count
1406 ,p_media_type => l_media_type
1407 ,p_gre_count => l_gre_count
1408 );
1409
1410 hr_utility.set_location(l_proc,70);
1411
1412 if l_header = 0 then
1413 fnd_file.put_line
1414 (which => fnd_file.log
1415 ,buff => ' '
1416 );
1417 fnd_file.put_line
1418 (which => fnd_file.log
1419 ,buff => ' EVS Report Summary '
1420 );
1421 fnd_file.put_line
1422 (which => fnd_file.log
1423 ,buff => ' '
1424 );
1425 fnd_file.put_line
1426 (which => fnd_file.log
1427 ,buff => ' GRE Name Total number '
1428 );
1429 fnd_file.put_line
1430 (which => fnd_file.log
1431 ,buff => ' ------------------- ------------- '
1432 );
1433 l_header := 1;
1434 end if;
1435 hr_utility.set_location(l_proc,71);
1436 fnd_file.put_line
1437 (which => fnd_file.log
1438 ,buff => ' ' || rpad(gre_record.name,20,' ') ||
1439 ' ' || to_char(l_gre_count)
1440 );
1441 l_all_count := l_all_count + l_gre_count;
1442 END LOOP;
1443 hr_utility.set_location(l_proc,72);
1444 fnd_file.put_line
1445 (which => fnd_file.log
1446 ,buff => ' ' || rpad('ALL GREs',20,' ') ||
1447 ' ' || to_char(l_all_count)
1448 );
1449 fnd_file.put_line
1450 (which => fnd_file.log
1451 ,buff => ' '
1452 );
1453 -- utl_file.fclose(g_file_id); -- 08-JUL-2005
1454 hr_utility.set_location(l_proc,74);
1455 end if;
1456 hr_utility.set_location('Leaving..: ' || l_proc,100);
1457
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 hr_utility.set_location(l_proc || substr(sqlerrm,1,50),999);
1461 fnd_file.put_line(fnd_file.log,SQLERRM);
1462 RAISE;
1463
1464 end evs_mag_report_main;
1465
1466 end per_evs_mag_report;