[Home] [Help]
PACKAGE BODY: APPS.HR_US_REPORTS
Source
1 PACKAGE BODY hr_us_reports AS
2 /* $Header: pyuslrep.pkb 120.3.12010000.2 2008/08/06 08:33:19 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22 Name : hr_us_reports (BODY)
23 File : pyuslrep.pkb
24 Description : This package declares functions and procedures which are used
25 to return values for the srw2 US Payroll r10 reports.
26
27 Change List
28 -----------
29
30 Version Date Author ER/CR No. Description of Change
31 -------+---------+----------+---------+-----------------------------------------------------------------
32 40.0 13-SEP-93 AKelly Date Created
33 40.1 14-SEP-93 AKelly Added dashes between components
34 of run in get_payroll_action.
35 40.2 26-OCT-93 AKelly Added get_legislation_code
36 40.3 22-NOV-93 AKelly Added get_defined_balance_id
37 40.4 09-DEC-93 AKelly Added get_startup_defined_balance,
38 which is intended for retrieving
39 startup balances' defined_balance_id,
40 as these generally have their reporting
41 names' set similar to the elements'
42 reporting names.
43 40.5 11-DEC-93 MSwanson Added 'get_payment_type_name'.
44 Added select to 'get_payroll_action'
45 for action_type 'P'.
46 Added 'get_element_type_name'.
47 40.5 17-FEB-94 GPayton-McDowall added get_ben_class_name
48 40.6 01-MAR-94 GPayton-McDowall added get_cobra_qualifying_event
49 get_cobra_status
50 40.7 23-Mar-94 MSwanson Added get_org_name, get_est_tax_unit and
51 get_org_hierarchy_name for EEO reporting.
52 40.8 25-Mar-94 MSwanson Added get_county_address for eeo and tax reps.
53 Added get_activity for eeo reps.
54 *********************************************************************************************************
55 40.0 18-May-94 M Gilmore Moved from US
56 40.1 03-Jul-94 A D Roussel Tidied up for 10G install.
57 40.2 03-Jul-94 hrdev Added Header
58 40.3 04-Jul-94 A D Roussel Fix symbol name message on load in sql+
59 40.x 12-Oct-94 MSwanson Add get_defined_balance_by_type.
60 Add get_employee_address.
61 Bug G1725 - remove use of index on
62 business_group_id.
63 Add get_person_name.
64 40.7 20-apr-95 MSwanson Add get_career_path_name.
65 40.8 29-Aug-95 MSwanson Tidy up balances for W2. Remove
66 many calls as we use new Bal API.
67 40.9 19-Oct-95 MSwanson Add get_state_name.
68 40.10 20-Oct-95 MSwanson Add get_new_hire_contact.
69 40.11 25-Oct-95 MSwanson Add get_salary.
70 40.12 01-Nov-95 Jthuring Removed error checking from end of script
71 40.13 29-Nov-95 mswanson Get normal_hours and work_schedule
72 in get_salary, so non-salaried emps
73 get calculated.
74 40.14 30-Apr-96 nbristow Now Caching defined_balance_id in
75 get_defined_balance_id to improve
76 performance (ChequeWriter).
77 40.15 21-May-96 nlee Bug 366087 Add new procedure
78 get_address_31.
79 Change 'Section 125' to
80 'Dependent Care'.
81 Add function get_location_code.
82 40.16 19-Aug-96 nlee Add a primary_flag = 'Y' condition to
83 get_employee_address procedure.
84 Add a new balance id and name called
85 '12' and 'W2 Fringe Benefit' respectively in get_defined_balance_by_type
86 40.17 06-Sep-96 ssdesai upgrade script py364888.sql creates a balance
87 called W2 Fringe Benefits (plural).
88 40.18 04-NOV-1996 hekim In get_person_name
89 -- changed l_person_name to VARCHAR2(240)
90 from VARCHAR2(60)
91 In get_address_31
92 -- take substr of line1,line2, line3 to
93 handle fields which are greater than 31 chars
94 40.19 05-NOV-1996 hekim Add function get_address_3lines
95 40.20 18-NOV-1996 hekim Added effective_date to get_address_3lines
96 40.21 04-DEC-1996 hekim Move state code on same line as city in get_address_31
97 40.22 09-JAN-1997 nlee Add a condition in get_person_name so that when it will get
98 the latest name in the effective_start_date and this will solve
99 the problem of fetching more than one row.
100
101 40.23 26-FEB-1997 hekim In get_address_3lines, take substring of city name
102 40.24 28-Jul-1997 nlee Change pay_state_rules to pay_us_states in get_state_name.
103 Change the selection of all rows from hr_locations and
104 per_addresses in get_address and get_employee_address
105 functions to selection of the specific rows that are
106 needed to increase performance and avoid overflow problem.
107 Change the sql statement to cursor statement in get_new_hire_contact
108 and add the exception handlers to the function.
109 40.25 08-DEC-1997 tlacey Added effective_date to get_employee_address.
110 115.1 04-MAR-1999 jmoyano MLS changes. Added references to _TL tables.
111
112 115.2 09-MAR-1999 sdoshi BUG 844582 - Ensure that all functions return a value,
113 whether it completes successfully or it fails via the
114 exception handler - default return value is NULL.
115 115.3 25-MAY-1999 mmukherj Added legislation code in the
116 get_defined_balance_id procedure.
117 115.4 18-APR-2000 mcpham Added function fnc_get_payee for report PAYRPTPP and bug 1063477
118 115.6 30-APR-2002 gperry Fixed WWBUG 2331831.
119 115.7 25-FEB-2003 vbanner Added function get_hr_est_tax_unit to
120 fix bug 2722353. (the new function will
121 return a tax id for the top org in a
122 hierarchy).
123 115.8 17-OCT-2003 ynegoro 3141907 Updated get_hr_est_tax_unit
124 Fixed GSCC warning, Added nocopy for out
125 parameters
126 115.9 23-OCT-2003 ynegoro 3182433 Added get_top_org_id function
127 115.10 09-APR-04 ynegoro 3545006 Updated get_top_org_id function
128 Added csr_get_max_child_id cursor;
129 115.11 14-JUL-04 saurgupt 3669973 Modified function get_salary. Modified the query to get freq.
130 from per_assignments_f. Also, add this freq. in the call to fun.
131 hr_us_ff_udfs.Convert_Period_Type.
132 Also, make the file GSCC compliant.
133 115.12 29-JUL-04 saurgupt 3624095 Modified cursor csr_tax_unit_flag to change the inpur parameter
134 name from tax_unit_id to p_tax_unit_id.
135 115.13 05-MAY-05 ynegoro 4346783 Added verify_state function fro VETS-100
136 115.14 18-Aug-05 sackumar 4350592 Changed the get_est_tax_unit function,
137 check_if_top_org cursor and csr_tax_unit_flag cursor.
138 115.15 10-OCT-2006 rpasumar 5577840 Modified verify_state function.
139 115.16 11-OCT-2006 rpasumar 5577840 Selected hierarchy_node_id instead of entity_id in c_est_entity cursor of the function verify_state.
140 115.17 12-MAR-2008 psugumar 6774707 Added get_employee_address40
141 Consolidate Report
142 ========================================================================================================
143 */
144 -- Global declarations
145 type char_array is table of varchar(81) index by binary_integer;
146 type num_array is table of number(16) index by binary_integer;
147 --
148 g_defbal_tbl_id num_array;
149 g_defbal_tbl_name char_array;
150 g_nxt_free_defbal number;
151 --
152 --
153 FUNCTION fnc_get_payee
154 ( IN_payee_id IN NUMBER,
155 IN_payee_type IN VARCHAR2,
156 IN_payment_date IN DATE,
157 IN_business_group_id IN NUMBER)
158 RETURN VARCHAR2 IS
159
160 CURSOR c_get_p_payee IS
161 SELECT SUBSTR(INITCAP(RTRIM(ppf.title)||' '||RTRIM(ppf.first_name)||' '||RTRIM(ppf.last_name)),1,60)
162 FROM per_addresses addr,
163 per_people_f ppf
164 WHERE ppf.person_id = IN_payee_id
165 AND ppf.business_group_id+0 = IN_business_group_id
166 AND IN_payment_date BETWEEN ppf.effective_start_date
167 AND ppf.effective_end_date
168 AND addr.person_id(+) = ppf.person_id
169 AND addr.primary_flag(+) = 'Y'
170 AND IN_payment_date BETWEEN addr.date_from(+)
171 AND NVL(addr.date_to, IN_payment_date);
172
173 CURSOR c_get_o_payee IS
174 SELECT SUBSTR(hou.name,1,240)
175 FROM hr_locations loc,
176 hr_organization_units hou
177 WHERE hou.organization_id = IN_payee_id
178 AND hou.business_group_id = IN_business_group_id
179 AND IN_payment_date BETWEEN hou.date_from
180 AND NVL(hou.date_to, IN_payment_date)
181 AND loc.location_id(+) = hou.location_id;
182
183 L_return_val VARCHAR2(240) := NULL;
184 -- Declare program variables as shown above
185
186
187 BEGIN
188
189 IF IN_payee_type = 'P' THEN
190 OPEN c_get_p_payee;
191 FETCH c_get_p_payee INTO L_return_val;
192 CLOSE c_get_p_payee;
193 ELSIF IN_payee_type = 'O' THEN
194 OPEN c_get_o_payee;
195 FETCH c_get_o_payee INTO L_return_val;
196 CLOSE c_get_o_payee;
197 END IF;
198
199 RETURN L_return_val;
200
201 EXCEPTION
202 WHEN OTHERS THEN
203 RAISE;
204 END fnc_get_payee;
205
206
207
208 --
209 --
210 FUNCTION get_salary (p_business_group_id NUMBER,
211 p_assignment_id NUMBER,
212 p_report_date DATE
213 ) return NUMBER
214 --
215 AS
216 --
217 l_effective_start_date date;
218 l_pay_basis varchar2(60);
219 l_salary number;
220 l_normal_hours number;
221 l_work_schedule varchar2(150);
222 l_annual_salary number;
223 l_frequency per_all_assignments_f.frequency%type;
224 --
225 Begin
226 --
227 hr_utility.set_location('hr_us_reports.get_salary',5);
228 hr_utility.trace('p_business_group_id ->'||to_char(p_business_group_id));
229 hr_utility.trace('p_assignment_id ->'||to_char(p_assignment_id));
230 hr_utility.trace('p_report_date ->'||p_report_date );
231 --
232 Begin
233 --
234 Select
235 peev.effective_start_date,
236 hl.meaning,
237 asg.normal_hours,
238 hscf.segment4,
239 peev.screen_entry_value,
240 decode(asg.frequency,'W','WEEK', -- Bug 3669973
241 'M','MONTH',
242 'Y','YEAR',
243 null) frequency
244
245 Into
246 l_effective_start_date,
247 l_pay_basis,
248 l_normal_hours,
249 l_work_schedule,
250 l_salary,
251 l_frequency
252 From
253 pay_element_entry_values_f peev,
254 pay_element_entries_f pee,
255 per_pay_bases ppb,
256 hr_soft_coding_keyflex hscf,
257 per_assignments_f asg,
258 hr_lookups hl
259 Where
260 hl.application_id = 800
261 And hl.lookup_type = 'PAY_BASIS'
262 And hl.lookup_code = ppb.pay_basis
263 And peev.element_entry_id = pee.element_entry_id
264 And peev.effective_start_date = pee.effective_start_date
265 And peev.input_value_id+0 = ppb.input_value_id
266 And asg.pay_basis_id = ppb.pay_basis_id
267 And pee.assignment_id = asg.assignment_id
268 And hscf.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
269 And asg.assignment_id = p_assignment_id
270 And asg.business_group_id = p_business_group_id
271 And pee.effective_start_date between asg.effective_start_date
272 and asg.effective_end_date
273 And p_report_date between pee.effective_start_date
274 and pee.effective_end_date;
275 --
276 hr_utility.trace('l_effective_start_date ->'||l_effective_start_date);
277 hr_utility.trace('l_pay_basis ->'||l_pay_basis);
278 hr_utility.trace('l_normal_hours ->'||to_char(l_normal_hours));
279 hr_utility.trace('l_work_schedule ->'||l_work_schedule);
280 hr_utility.trace('l_salary ->'||to_char(l_salary));
281 --
282 exception
283 when NO_DATA_FOUND then RETURN NULL;
284 when others then
285 hr_utility.set_location('hr_us_reports.get_salary',10);
286 RETURN NULL;
287 --
288 end;
289 --
290 --
291 l_annual_salary := hr_us_ff_udfs.Convert_Period_Type
292 (p_business_group_id,null,l_work_schedule,l_normal_hours,l_salary,l_pay_basis,'Year',
293 null,null,l_frequency); -- Bug 3669973
294 --
295 hr_utility.set_location('hr_us_reports.get_salary',15);
296 hr_utility.trace('l_annual_salary ->'||to_char(l_annual_salary));
297 --
298 return (l_annual_salary);
299 --
300 end get_salary;
301 --
302 --
303 procedure get_new_hire_contact( p_person_id in number,
304 p_business_group_id in number,
305 p_report_date in date,
306 p_contact_name out nocopy varchar2,
307 p_contact_title out nocopy varchar2,
308 p_contact_phone out nocopy varchar2
309 ) IS
310 --
311 v_contact_name per_people_f.full_name%TYPE;
312 v_contact_title per_jobs.name%TYPE;
313 v_contact_phone per_people_f.work_telephone%TYPE;
314
315 CURSOR c_new_hire_record IS
316 Select ppf.full_name,
317 job.name,
318 ppf.work_telephone
319 From
320 per_people_f ppf,
321 per_assignments_f paf,
322 per_jobs job
323 Where
324 ppf.person_id = p_person_id
325 And ppf.business_group_id + 0 = p_business_group_id
326 And p_report_date between paf.effective_start_date
327 and paf.effective_end_date
328 And ppf.person_id = paf.person_id
329 And paf.assignment_type = 'E'
330 And paf.primary_flag = 'Y'
331 And p_report_date between paf.effective_start_date
332 and paf.effective_end_date
333 And paf.job_id = job.job_id(+);
334
335 --
336 begin
337 --
338 hr_utility.set_location('Entered hr_us_reports.get_new_hire_contact',5);
339 --
340 OPEN c_new_hire_record;
341
342 --LOOP
343 FETCH c_new_hire_record INTO v_contact_name, v_contact_title, v_contact_phone;
344
345 p_contact_name := v_contact_name;
346 p_contact_title := v_contact_title;
347 p_contact_phone := v_contact_phone;
348
349 -- EXIT WHEN c_new_hire_record%NOTFOUND;
350 --END LOOP;
351
352 CLOSE c_new_hire_record;
353 --
354 hr_utility.trace('Contact name : '||v_contact_name);
355 hr_utility.trace('Contact title : '||v_contact_title);
356 hr_utility.set_location('Leaving hr_us_reports.get_new_hire_contact',10);
357 --
358 exception
359 when no_data_found then
360 hr_utility.set_location('Error found in hr_us_reports.get_new_hire.contact',20);
361 NULL;
362 when others then
363 hr_utility.set_location('Error found in hr_us_reports.get_new_hire_contact',15);
364 --
365 end get_new_hire_contact;
366 --
367 --
368 procedure get_address(p_location_id in number, p_address out nocopy varchar2) IS
369 --
370 f_address varchar2(300) := NULL;
371 --
372 v_address_line_1 hr_locations.address_line_1%TYPE;
373 v_address_line_2 hr_locations.address_line_2%TYPE;
374 v_address_line_3 hr_locations.address_line_3%TYPE;
375 v_town_or_city hr_locations.town_or_city%TYPE;
376 v_region_2 hr_locations.region_2%TYPE;
377 v_postal_code hr_locations.postal_code%TYPE;
378 --
379 cursor get_location_record is
380 select address_line_1, address_line_2, address_line_3,
381 town_or_city, region_2, postal_code
382 from hr_locations
383 where location_id = p_location_id;
384 --
385 begin
386 --
387 hr_utility.set_location('Entered hr_us_reports.get_address', 5);
388 --
389 open get_location_record;
390 --
391 fetch get_location_record into v_address_line_1, v_address_line_2,
392 v_address_line_3, v_town_or_city, v_region_2, v_postal_code;
393 --
394 hr_utility.set_location('hr_us_reports.get_address', 10);
395 --
396 if get_location_record%found
397 then
398 --
399 if v_address_line_1 is not null
400 then
401 f_address := rpad(v_address_line_1,48,' ');
402 end if;
403 --
404 if v_address_line_2 is not null
405 then
406 f_address := f_address ||
407 rpad(v_address_line_2,48,' ');
408 end if;
409 --
410 if v_address_line_3 is not null
411 then
412 f_address := f_address ||
413 rpad(v_address_line_3,48,' ');
414 end if;
415 --
416 if v_town_or_city is not null
417 then
418 f_address:= f_address || rpad(v_town_or_city,48,' ');
419 end if;
420 --
421 if v_region_2 is not null
422 then
423 f_address := f_address ||v_region_2||' '||
424 v_postal_code;
425 end if;
426 --
427 close get_location_record;
428 --
429 hr_utility.set_location('hr_us_reports.get_address', 15);
430 --
431 hr_utility.trace('location is '|| f_address);
432 p_address := f_address;
433 --
434 end if;
435 --
436 exception
437 when others then
438 hr_utility.trace('Error in hr_us_reports.get_address');
439 hr_utility.set_location('hr_us_reports.get_address', 20);
440 --
441 end get_address;
442 --
443 --
444 procedure get_address_31(p_location_id in number, p_address out nocopy varchar2) IS
445 f_address varchar2(155) := NULL;
446 f_city_state varchar2(50) := NULL;
447 address_record hr_locations%rowtype;
448 cursor get_location_record is
449 select *
450 from hr_locations
451 where location_id = p_location_id;
452 begin
453 open get_location_record;
454 fetch get_location_record into address_record;
455 if get_location_record%found
456 then
457 if address_record.address_line_1 is not null
458 then
459 f_address := rpad(substr(address_record.address_line_1,1,30),31,' ');
460 end if;
461 if address_record.address_line_2 is not null
462 then
463 f_address := f_address ||
464 rpad(substr(address_record.address_line_2,1,30),31,' ');
465 end if;
466 --
467 if address_record.address_line_3 is not null
468 then
469 f_address := f_address ||
470 rpad(substr(address_record.address_line_3,1,30),31,' ');
471 end if;
472 --
473 if address_record.town_or_city is not null
474 then
475 f_city_state := substr(address_record.town_or_city,1,25);
476 end if;
477 --
478 if address_record.region_2 is not null
479 then
480 f_city_state := f_city_state || ', ' || address_record.region_2;
481 end if;
482 if f_city_state is not null
483 then
484 f_address := f_address || rpad(substr(f_city_state,1,30),31,' ');
485 end if;
486 --
487 if address_record.postal_code is not null
488 then
489 f_address := f_address ||
490 substr(address_record.postal_code,1,12);
491 end if;
492 --
493 close get_location_record;
494 hr_utility.trace('location is '|| f_address);
495 p_address := f_address;
496 end if;
497 end get_address_31;
498 --
499 --
500
501
502
503 procedure get_address_3lines(p_person_id in number,
504 p_effective_date in date,
505 p_addr_line1 out nocopy varchar2,
506 p_addr_line2 out nocopy varchar2,
507 p_city_state_zip out nocopy varchar2 ) IS
508 --
509 f_addr_line1 varchar2(240) := NULL;
510 f_addr_line2 varchar2(240) := NULL;
511 f_city_state_zip varchar2(250) := NULL;
512 --
513 address_record per_addresses%rowtype;
514 cursor get_address_record is
515 select * from per_addresses
516 where person_id = p_person_id
517 and primary_flag='Y'
518 and nvl(date_to, p_effective_date) >= p_effective_date;
519 --
520 begin
521 open get_address_record;
522 fetch get_address_record into address_record;
523 if get_address_record%found
524 then
525 f_addr_line1 := rpad(substr(address_record.address_line1,1,30),31,' ');
526
527 f_addr_line2 := rpad(substr(address_record.address_line2,1,30),31,' ');
528
529 f_city_state_zip := substr(address_record.town_or_city,1,17) || ', ' ||
530 address_record.region_2 || ' ' ||
531 address_record.postal_code;
532 close get_address_record;
533 --
534 p_addr_line1 := f_addr_line1;
535 p_addr_line2 := f_addr_line2;
536 p_city_state_zip := f_city_state_zip;
537 hr_utility.trace('address is '|| f_addr_line1 );
538 hr_utility.trace( f_addr_line2 );
539 hr_utility.trace( f_city_state_zip );
540 end if;
541 end get_address_3lines;
542 --
543
544 FUNCTION break_address_line
545 (p_addr_line VARCHAR2) return VARCHAR2
546 --
547 AS
548 --
549 begin
550 if length(p_addr_line)<=30 then
551 return rpad(substr(p_addr_line,1,30),31,' ');
552 else
553 return rpad(substr(p_addr_line,1,30),31,' ') || rpad(substr(p_addr_line,31,40),31,' ');
554 end if;
555
556 end break_address_line;
557
558 procedure get_employee_address(p_person_id in number,
559 p_address out nocopy varchar2) IS
560 --
561 f_address varchar2(340) := NULL;
562
563 --
564 -- address_record per_addresses%rowtype;
565 --
566 v_address_line1 per_addresses.address_line1%TYPE;
567 v_address_line2 per_addresses.address_line2%TYPE;
568 v_address_line3 per_addresses.address_line3%TYPE;
569 v_town_or_city per_addresses.town_or_city%TYPE;
570 v_region_2 per_addresses.region_2%TYPE;
571 v_postal_code per_addresses.postal_code%TYPE;
572 --
573 cursor get_address_record is
574 select address_line1, address_line2, address_line3,
575 town_or_city, region_2, postal_code
576 from per_addresses
577 where person_id = p_person_id
578 and primary_flag = 'Y'
579 and nvl(date_to, sysdate) >= sysdate;
580 --
581 begin
582 --
583 hr_utility.set_location('Entered hr_us_reports.get_employee_address', 0);
584 --
585 open get_address_record;
586 --
587 fetch get_address_record into v_address_line1, v_address_line2,
588 v_address_line3, v_town_or_city, v_region_2, v_postal_code;
589 --
590 hr_utility.set_location('Entered hr_us_reports.get_employee_address', 5);
591 --
592 if get_address_record%found
593 then
594 --
595 if v_address_line1 is not null
596 then
597 f_address := break_address_line (v_address_line1) ;
598 end if;
599 --
600 if v_address_line2 is not null
601 then
602 f_address := f_address || break_address_line(v_address_line2) ;
603
604 end if;
605 --
606 if v_address_line3 is not null
607 then
608 f_address := f_address || break_address_line(v_address_line3) ;
609
610 end if;
611 --
612 if v_town_or_city is not null
613 then
614 f_address:= f_address || rpad(v_town_or_city,31,' ');
615 end if;
616 --
617 if v_region_2 is not null
618 then
619 f_address := f_address ||v_region_2||' '||
620 v_postal_code;
621 end if;
622 --
623 insert into pay_us_rpt_totals(ATTRIBUTE30,attribute1) values(f_address,'test1');
624 commit;
625 hr_utility.set_location('hr_us_reports.get_employee_address', 10);
626 close get_address_record;
627 --
628 hr_utility.trace('Person Address is '|| f_address);
629 --
630 p_address := f_address;
631 --
632 end if;
633 --
634 hr_utility.set_location('Leaving hr_us_reports.get_employee_address', 15);
635 --
636 exception when NO_DATA_FOUND then NULL;
637 --
638 end get_employee_address;
639
640 --
641 --
642 procedure get_county_address(p_location_id in number, p_address out nocopy varchar2) IS
643 f_address varchar2(300) := NULL;
644 address_record hr_locations%rowtype;
645 cursor get_location_record is
646 select * from hr_locations
647 where location_id = p_location_id;
648 begin
649 open get_location_record;
650 fetch get_location_record into address_record;
651 if get_location_record%found
652 then
653 if address_record.address_line_1 is not null
654 then
655 f_address := rpad(address_record.address_line_1,40,' ');
656 end if;
657 if address_record.address_line_2 is not null
658 then
659 f_address := f_address ||
660 rpad(address_record.address_line_2,40,' ');
661 end if;
662 if address_record.address_line_3 is not null
663 then
664 f_address := f_address ||
665 rpad(address_record.address_line_3,40,' ');
666 end if;
667 if address_record.town_or_city is not null
668 then
669 f_address:= f_address || rpad(address_record.town_or_city,40,' ');
670 end if;
671 if address_record.region_1 is not null
672 then
673 f_address := f_address || rpad(address_record.region_1,40,' ');
674 end if;
675 if address_record.region_2 is not null
676 then
677 f_address := f_address ||address_record.region_2||' '||
678 address_record.postal_code;
679 end if;
680 close get_location_record;
681 hr_utility.trace('location is '|| f_address);
682 p_address := f_address;
683 end if;
684 end get_county_address;
685 --
686 --
687 --
688 --
689 procedure get_activity(p_establishment_id in number, p_activity out nocopy varchar2) IS
690 f_activity varchar2(300) := NULL;
691 activity_record hr_establishments_v%rowtype;
692 cursor get_establishment_record is
693 select * from hr_establishments_v
694 where establishment_id = p_establishment_id;
695 begin
696 open get_establishment_record;
697 fetch get_establishment_record into activity_record;
698 if get_establishment_record%found
699 then
700 if activity_record.activity_line1 is not null
701 then
702 f_activity := rpad(activity_record.activity_line1,40,' ');
703 end if;
704 if activity_record.activity_line2 is not null
705 then
706 f_activity := f_activity ||
707 rpad(activity_record.activity_line2,40,' ');
708 end if;
709 if activity_record.activity_line3 is not null
710 then
711 f_activity := f_activity ||
712 rpad(activity_record.activity_line3,40,' ');
713 end if;
714 if activity_record.activity_line4 is not null
715 then
716 f_activity := f_activity ||
717 rpad(activity_record.activity_line4,40,' ');
718 end if;
719 close get_establishment_record;
720 hr_utility.trace('establishment activity is '|| f_activity);
721 p_activity := f_activity;
722 end if;
723 end get_activity;
724 --
725 FUNCTION get_consolidation_set
726 (p_consolidation_set_id NUMBER) return VARCHAR2
727 --
728 AS
729 l_consolidation_set_name VARCHAR2(60);
730 --
731 begin
732 --
733 hr_utility.trace('Entered Get_consolidation_set');
734 --
735 begin
736 hr_utility.set_location('hr_us_reports.get_consolidation_set',5);
737 SELECT consolidation_set_name
738 INTO l_consolidation_set_name
739 FROM pay_consolidation_sets
740 WHERE consolidation_set_id = p_consolidation_set_id;
741 --
742 exception when NO_DATA_FOUND then RETURN NULL;
743 end;
744 --
745 return l_consolidation_set_name;
746 --
747 end get_consolidation_set;
748 --
749 --
750 FUNCTION get_payment_type_name
751 (p_payment_type_id NUMBER) return VARCHAR2
752 --
753 AS
754 l_payment_type_name VARCHAR2(60);
755 --
756 begin
757 --
758 hr_utility.trace('Entered Get_payment_type_name');
759 --
760 begin
761 hr_utility.set_location('hr_us_reports.get_payment_type_name',5);
762 SELECT ppt_tl.payment_type_name
763 INTO l_payment_type_name
764 FROM pay_payment_types_tl ppt_tl,
765 pay_payment_types ppt
766 WHERE ppt_tl.payment_type_id = ppt.payment_type_id
767 and userenv('LANG') = ppt_tl.language
768 and ppt.payment_type_id = p_payment_type_id;
769 --
770 exception when NO_DATA_FOUND then RETURN NULL;
771 end;
772 --
773 return l_payment_type_name;
774 --
775 end get_payment_type_name;
776 --
777 --
778 FUNCTION get_element_type_name
779 (p_element_type_id NUMBER) return VARCHAR2
780 --
781 AS
782 l_element_type_name VARCHAR2(60);
783 --
784 begin
785 --
786 hr_utility.trace('Entered Get_element_type_name');
787 --
788 begin
789 hr_utility.set_location('hr_us_reports.get_element_type_name',5);
790 SELECT pet_tl.element_name
791 INTO l_element_type_name
792 FROM pay_element_classifications pec,
793 pay_element_types_f_tl pet_tl,
794 pay_element_types_f pet
795 WHERE pet_tl.element_type_id = pet.element_type_id
796 and userenv('LANG') = pet_tl.language
797 and pec.classification_id = pet.classification_id
798 AND pet.element_type_id = p_element_type_id;
799 --
800 exception when NO_DATA_FOUND then RETURN NULL;
801 end;
802 --
803 return l_element_type_name;
804 --
805 end get_element_type_name;
806 --
807 --
808 FUNCTION get_tax_unit
809 (p_tax_unit_id NUMBER) return VARCHAR2
810 --
811 AS
812 l_tax_unit_name VARCHAR2(240);
813 --
814 begin
815 --
816 hr_utility.trace('Entered Get_tax_unit');
817 --
818 begin
819 hr_utility.set_location('hr_us_reports.get_tax_unit',5);
820 SELECT name
821 INTO l_tax_unit_name
822 FROM hr_organization_units
823 WHERE organization_id = p_tax_unit_id;
824 --
825 exception when NO_DATA_FOUND then RETURN NULL;
826 end;
827 --
828 return l_tax_unit_name;
829 --
830 end get_tax_unit;
831 --
832 --
833 FUNCTION get_person_name
834 (p_person_id NUMBER) return VARCHAR2
835 --
836 AS
837 l_person_name VARCHAR2(240);
838 --
839 begin
840 --
841 hr_utility.trace('Entered get_person_name');
842 --
843 begin
844 hr_utility.set_location('hr_us_reports.get_person_name',5);
845 SELECT distinct full_name
846 INTO l_person_name
847 FROM per_people_f ppf
848 WHERE person_id = p_person_id
849 AND ppf.effective_start_date =
850 (select max(effective_start_date)
851 from per_people_f ppf1
852 where ppf1.person_id = ppf.person_id);
853 --
854 exception when NO_DATA_FOUND then RETURN NULL;
855 end;
856 --
857 return l_person_name;
858 --
859 end get_person_name;
860 --
861 --
862 FUNCTION get_payroll_action
863 (p_payroll_action_id NUMBER) return VARCHAR2
864 --
865 AS
866 l_action_type CHAR(1);
867 l_payroll_action_name VARCHAR2(60);
868 --
869 begin
870 --
871 hr_utility.trace('Entered Get_payroll_action');
872 --
873 begin
874 hr_utility.set_location('hr_us_reports.get_payroll_action',5);
875 SELECT action_type
876 INTO l_action_type
877 FROM pay_payroll_actions
878 WHERE payroll_action_id = p_payroll_action_id;
879 --
880 exception when NO_DATA_FOUND then RETURN NULL;
881 end;
882 if l_action_type = 'P' then
883 begin
884 hr_utility.set_location('hr_us_reports.get_payroll_action',10);
885 SELECT ppa.display_run_number || '-' || pcs.consolidation_set_name
886 || '-' || ppa.effective_date || '-' || ppa.payroll_action_id
887 INTO l_payroll_action_name
888 FROM pay_consolidation_sets pcs,
889 pay_payroll_actions ppa
890 WHERE ppa.consolidation_set_id = pcs.consolidation_set_id
891 AND ppa.payroll_action_id = p_payroll_action_id;
892 exception
893 when no_data_found then RETURN NULL;
894 end;
895 elsif l_action_type = 'R' then
896 begin
897 hr_utility.set_location('hr_us_reports.get_payroll_action',15);
898 SELECT ppa.display_run_number || '-' || has.assignment_set_name
899 || '-' || pes.element_set_name
900 INTO l_payroll_action_name
901 FROM hr_assignment_sets has,
902 pay_element_sets pes,
903 pay_payroll_actions ppa
904 WHERE has.assignment_set_id(+) = ppa.assignment_set_id
905 AND pes.element_set_id(+) = ppa.element_set_id
906 AND ppa.payroll_action_id = p_payroll_action_id;
907 exception
908 when no_data_found then RETURN NULL;
909 end;
910 elsif l_action_type = 'Q' then
911 begin
912 SELECT ppa.display_run_number || '-' || ppe.full_name
913 INTO l_payroll_action_name
914 FROM per_people_f ppe,
915 per_all_assignments_f pas,
916 pay_assignment_actions paa,
917 pay_payroll_actions ppa
918 WHERE ppe.person_id = pas.person_id
919 AND pas.assignment_id = paa.assignment_id
920 AND paa.payroll_action_id = ppa.payroll_action_id
921 AND ppa.payroll_action_id = p_payroll_action_id
922 AND ppa.effective_date between ppe.effective_start_date
923 and ppe.effective_end_date
924 AND ppa.effective_date between pas.effective_start_date
925 and pas.effective_end_date;
926 exception
927 when no_data_found then RETURN NULL;
928 end;
929 end if;
930 --
931 return l_payroll_action_name;
932 --
933 end get_payroll_action;
934 --
935 --
936 FUNCTION get_legislation_code
937 (p_business_group_id NUMBER) return VARCHAR2
938 --
939 AS
940 l_legislation_code VARCHAR2(30);
941 --
942 begin
943 --
944 hr_utility.trace('Entered Get_legislation_code');
945 --
946 begin
947 hr_utility.set_location('hr_us_reports.get_legislation_code',5);
948 SELECT org_information9
949 INTO l_legislation_code
950 FROM hr_organization_information
951 WHERE organization_id = p_business_group_id
952 AND UPPER(org_information_context) = 'BUSINESS GROUP INFORMATION';
953 --
954 exception when NO_DATA_FOUND then RETURN NULL;
955 end;
956 --
957 return l_legislation_code;
958 --
959 end get_legislation_code;
960 --
961 --
962 FUNCTION get_defined_balance_id
963 (p_balance_name VARCHAR2, p_dimension_suffix VARCHAR2,
964 p_business_group_id NUMBER) return NUMBER
965 --
966 AS
967 l_defined_balance_id NUMBER;
968 l_defbal_name CHAR(81);
969 l_count NUMBER;
970 l_found BOOLEAN;
971 --
972 begin
973 --
974 hr_utility.trace('Entered Get_defined_balance_id');
975 --
976 -- Search for the defined balance in the Cache.
977 --
978 l_defbal_name := p_balance_name||p_dimension_suffix||p_business_group_id;
979 l_count := 1;
980 l_found := FALSE;
981 while (l_count < g_nxt_free_defbal and l_found = FALSE) loop
982 if (l_defbal_name = g_defbal_tbl_name(l_count)) then
983 l_defined_balance_id := g_defbal_tbl_id(l_count);
984 l_found := TRUE;
985 end if;
986 l_count := l_count + 1;
987 end loop;
988 --
989 -- If the balance is not in the Cache get it from the database.
990 --
991 if (l_found = FALSE) then
992 begin
993 hr_utility.set_location('hr_us_reports.get_defined_balance_id',5);
994 /* Legislation code is added in this query so that it does not
995 fetch multiple values after Canadian Payroll is installed - mmukherj*/
996 SELECT pdb.defined_balance_id
997 INTO l_defined_balance_id
998 FROM pay_defined_balances pdb
999 , pay_balance_dimensions pbd
1000 , pay_balance_types pbt
1001 WHERE pbt.balance_name = p_balance_name
1002 AND ((pbt.business_group_id IS NULL
1003 AND pbt.legislation_code = 'US')
1004 OR pbt.business_group_id + 0 = p_business_group_id)
1005 AND pbd.database_item_suffix = p_dimension_suffix
1006 AND pdb.balance_type_id = pbt.balance_type_id
1007 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1008 AND (pdb.business_group_id IS NULL
1009 OR pdb.business_group_id + 0 = p_business_group_id);
1010 --
1011 -- Place the defined balance in cache.
1012 --
1013 g_defbal_tbl_name(g_nxt_free_defbal) := l_defbal_name;
1014 g_defbal_tbl_id(g_nxt_free_defbal) := l_defined_balance_id;
1015 g_nxt_free_defbal := g_nxt_free_defbal + 1;
1016 --
1017 exception when NO_DATA_FOUND then RETURN NULL;
1018 end;
1019 end if;
1020 --
1021 return l_defined_balance_id;
1022 --
1023 end get_defined_balance_id;
1024 --
1025 --
1026 FUNCTION get_startup_defined_balance
1027 (p_reporting_name VARCHAR2, p_dimension_suffix VARCHAR2) return NUMBER
1028 --
1029 AS
1030 l_defined_balance_id NUMBER;
1031 --
1032 begin
1033 --
1034 hr_utility.trace('Entered Get_startup_defined_balance');
1035 --
1036 begin
1037 hr_utility.set_location('hr_us_reports.get_startup_defined_balance',5);
1038 SELECT pdb.defined_balance_id
1039 INTO l_defined_balance_id
1040 FROM pay_defined_balances pdb
1041 , pay_balance_dimensions pbd
1042 , pay_balance_types pbt
1043 WHERE pbt.reporting_name = p_reporting_name
1044 AND pbd.database_item_suffix = p_dimension_suffix
1045 AND pdb.balance_type_id = pbt.balance_type_id
1046 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
1047 --
1048 exception when NO_DATA_FOUND then RETURN NULL;
1049 end;
1050 --
1051 return l_defined_balance_id;
1052 --
1053 end get_startup_defined_balance;
1054 --
1055 --
1056 -- Gets defined balance id using balance type id for seeded balances
1057 --
1058 FUNCTION get_defined_balance_by_type
1059 (p_box_num VARCHAR2, p_dimension_suffix VARCHAR2) return NUMBER
1060 --
1061 AS
1062 l_defined_balance_id NUMBER;
1063 l_balance_type_id NUMBER;
1064 l_balance_type_name VARCHAR2(30);
1065 --
1066 begin
1067 --
1068 hr_utility.trace('Entered Get_defined_balance_by_type');
1069 --
1070 -- **NOTE** We do not yet have the 'US_TAX DEDUCTIONS' category seeded yet.
1071 --
1072 hr_utility.set_location('hr_us_reports.get_defined_balance_by_type',5);
1073 --
1074 IF
1075 p_box_num = '10' THEN
1076 l_balance_type_name := 'Dependent Care'; -- *OK*
1077 ELSIF
1078 p_box_num = '15c' THEN
1079 l_balance_type_name := 'W2 Pension Plan'; -- *OK*
1080 ELSIF
1081 p_box_num = '15g' THEN
1082 l_balance_type_name := 'Def Comp 401K'; -- *OK*
1083 ELSIF
1084 p_box_num = '12' THEN
1085 l_balance_type_name := 'W2 Fringe Benefits';
1086 END IF;
1087 --
1088 begin
1089 SELECT pbt.balance_type_id
1090 INTO l_balance_type_id
1091 FROM pay_balance_types pbt
1092 WHERE pbt.balance_name = l_balance_type_name
1093 AND pbt.business_group_id is null
1094 AND pbt.legislation_code = 'US';
1095 exception
1096 when NO_DATA_FOUND then RETURN NULL;
1097 end;
1098 --
1099 begin
1100 hr_utility.set_location('hr_us_reports.get_defined_balance_by_type',10);
1101 --
1102 SELECT pdb.defined_balance_id
1103 INTO l_defined_balance_id
1104 FROM pay_defined_balances pdb
1105 , pay_balance_dimensions pbd
1106 , pay_balance_types pbt
1107 WHERE pbt.balance_type_id = l_balance_type_id
1108 AND pbd.database_item_suffix = p_dimension_suffix
1109 AND pdb.balance_type_id = pbt.balance_type_id
1110 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
1111 --
1112 exception
1113 when NO_DATA_FOUND then RETURN NULL;
1114 end;
1115 --
1116 return l_defined_balance_id;
1117 --
1118 end get_defined_balance_by_type;
1119 --
1120 --
1121 FUNCTION get_ben_class_name
1122 (p_session_date DATE,
1123 p_benefit_classification_id NUMBER) return VARCHAR2 IS
1124 --
1125 v_benefit_class_name ben_benefit_classifications.benefit_classification_name%type;
1126 --
1127 begin
1128 hr_utility.trace('Entered hr_reports.get_ben_class_name');
1129 --
1130 hr_utility.set_location('hr_reports.get_ben_class_name',5);
1131 if p_benefit_classification_id is null then
1132 null;
1133 else
1134 begin
1135 hr_utility.set_location('hr_reports.get_ben_class_name',10);
1136 select benefit_classification_name
1137 into v_benefit_class_name
1138 from ben_benefit_classifications
1139 where benefit_classification_id = p_benefit_classification_id;
1140 exception
1141 when no_data_found then RETURN NULL;
1142 end;
1143 end if;
1144 --
1145 hr_utility.trace('Leaving hr_reports.get_ben_class_name');
1146 --
1147 return v_benefit_class_name;
1148 --
1149 end get_ben_class_name;
1150 --
1151 --
1152 FUNCTION get_cobra_qualifying_event
1153 ( p_qualifying_event VARCHAR2 ) return VARCHAR2 IS
1154 --
1155 v_qualifying_event_meaning hr_lookups.meaning%type;
1156 --
1157 BEGIN
1158 hr_utility.trace('Entered hr_reports.get_cobra_qualifying_event');
1159 --
1160 hr_utility.set_location('hr_reports.get_cobra_qualifying_event',5);
1161 IF p_qualifying_event IS NULL
1162 THEN
1163 NULL;
1164 ELSE
1165 BEGIN
1166 hr_utility.set_location('hr_reports.get_cobra_qualifying_event',10);
1167 SELECT meaning
1168 INTO v_qualifying_event_meaning
1169 FROM hr_lookups
1170 WHERE lookup_type = 'US_COBRA_EVENT'
1171 AND lookup_code = p_qualifying_event;
1172 EXCEPTION
1173 WHEN no_data_found THEN RETURN NULL;
1174 END;
1175 END IF;
1176 --
1177 hr_utility.trace('Leaving hr_reports.get_cobra_qualifying_event');
1178 --
1179 return v_qualifying_event_meaning;
1180 --
1181 END get_cobra_qualifying_event;
1182 --
1183 --
1184 FUNCTION get_cobra_status
1185 ( p_cobra_status VARCHAR2 ) return VARCHAR2 IS
1186 --
1187 v_cobra_status_meaning hr_lookups.meaning%type;
1188 --
1189 BEGIN
1190 hr_utility.trace('Entered hr_reports.get_cobra_status');
1191 --
1192 hr_utility.set_location('hr_reports.get_cobra_status',5);
1193 IF p_cobra_status IS NULL
1194 THEN
1195 NULL;
1196 ELSE
1197 BEGIN
1198 hr_utility.set_location('hr_reports.get_cobra_status',10);
1199 SELECT meaning
1200 INTO v_cobra_status_meaning
1201 FROM hr_lookups
1202 WHERE lookup_type = 'US_COBRA_STATUS'
1203 AND lookup_code = p_cobra_status;
1204 EXCEPTION
1205 WHEN no_data_found THEN RETURN NULL;
1206 END;
1207 END IF;
1208 --
1209 hr_utility.trace('Leaving hr_reports.get_cobra_status');
1210 --
1211 return v_cobra_status_meaning;
1212 --
1213 END get_cobra_status;
1214 --
1215 --
1216 --
1217 -- Finds Reporting Entity for an organization entered which is an establishment
1218 -- Works up the organization hierarchy and returns the first reporting entity
1219 -- encountered in the hierarchy.
1220 --
1221 FUNCTION get_est_tax_unit (p_starting_org_id number,
1222 p_org_structure_version_id number
1223 ) RETURN number
1224 IS
1225 --
1226 -- WWBUG 2331831
1227 -- Fixed connect by so connect by loop not raised.
1228 --
1229 CURSOR get_parent IS
1230 SELECT ose.organization_id_parent
1231 FROM per_org_structure_elements ose
1232 WHERE ose.org_structure_version_id = p_org_structure_version_id
1233 START WITH ose.organization_id_child = p_starting_org_id
1234 CONNECT BY PRIOR ose.organization_id_parent = ose.organization_id_child
1235 AND ose.org_structure_version_id = p_org_structure_version_id;
1236 --
1237 -- WWBUG 2331831
1238 --
1239 parent_tax_unit_id number(15);
1240 tax_unit_flag char(2);
1241 --
1242 BEGIN
1243 parent_tax_unit_id := null;
1244 tax_unit_flag := 'N';
1245 OPEN get_parent;
1246 WHILE tax_unit_flag = 'N' LOOP
1247 FETCH get_parent INTO parent_tax_unit_id;
1248 hr_utility.trace('Parent tax unit >'||parent_tax_unit_id);
1249 EXIT WHEN get_parent%NOTFOUND;
1250 hr_utility.trace('Parent tax unit >'||parent_tax_unit_id);
1251 /* sackumar */
1252 begin
1253 SELECT 'Y'
1254 INTO tax_unit_flag
1255 FROM hr_organization_information hoi
1256 WHERE hoi.organization_id = parent_tax_unit_id
1257 AND hoi.ORG_INFORMATION1 = 'HR_LEGAL'
1258 AND hoi.ORG_INFORMATION2 = 'Y' ;
1259 exception
1260 when no_data_Found then
1261 tax_unit_flag := 'N';
1262 end;
1263 /* previous */
1264 /* Bug No 4350592
1265 SELECT decode(tax_unit_id,'','N','Y')
1266 INTO tax_unit_flag
1267 FROM hr_tax_units_v htuv,
1268 hr_organization_units hou
1269 WHERE htuv.tax_unit_id(+) = hou.organization_id
1270 AND hou.organization_id = parent_tax_unit_id;
1271 */
1272 END LOOP;
1273 CLOSE get_parent;
1274 --
1275 hr_utility.trace('Est tax unit >'||parent_tax_unit_id);
1276 return (parent_tax_unit_id);
1277 --
1278 end get_est_tax_unit;
1279 --
1280 -- bug 2722353 - new function.
1281 --
1282 -- Finds Reporting Entity for an organization entered which is an establishment
1283 -- Works up the organization hierarchy and returns the first reporting entity
1284 -- encountered in the hierarchy. This function differs from get_est_tax_unit
1285 -- in that it will return the first reporting entity encountered in the
1286 -- hierarchy if this top organization id is entered as the starting org.
1287 -- In this situation the function above returns null.
1288 --
1289 FUNCTION get_hr_est_tax_unit (p_starting_org_id number,
1290 p_org_structure_version_id number
1291 ) RETURN number
1292 IS
1293 --
1294 -- WWBUG 2331831
1295 -- Fixed connect by so connect by loop not raised.
1296 --
1297 CURSOR get_hr_parent IS
1298 SELECT ose.organization_id_parent
1299 FROM per_org_structure_elements ose
1300 WHERE ose.org_structure_version_id = p_org_structure_version_id
1301 START WITH ose.organization_id_child = p_starting_org_id
1302 CONNECT BY PRIOR ose.organization_id_parent = ose.organization_id_child
1303 AND ose.org_structure_version_id = p_org_structure_version_id;
1304 --
1305 -- WWBUG 2331831
1306 --
1307 /* sackumar */
1308 CURSOR check_if_top_org IS
1309 SELECT hoi.organization_id
1310 FROM hr_organization_information hoi
1311 WHERE hoi.organization_id = p_starting_org_id
1312 AND hoi.ORG_INFORMATION1 = 'HR_LEGAL'
1313 AND hoi.ORG_INFORMATION2 = 'Y' ;
1314
1315 /* previous */
1316 /* CURSOR check_if_top_org IS
1317 SELECT htuv.tax_unit_id
1318 FROM hr_tax_units_v htuv
1319 WHERE htuv.tax_unit_id = p_starting_org_id;
1320 */
1321 --
1322 -- BUG3141907
1323 --
1324 /* sackumar */
1325 cursor csr_tax_unit_flag(p_tax_unit_id number) is -- Bug 3624095
1326 SELECT hoi.organization_id
1327 FROM hr_organization_information hoi
1328 WHERE hoi.organization_id = p_tax_unit_id
1329 AND hoi.ORG_INFORMATION1 = 'HR_LEGAL'
1330 AND hoi.ORG_INFORMATION2 = 'Y' ;
1331
1332 /* previous */
1333 /*cursor csr_tax_unit_flag(p_tax_unit_id number) is -- Bug 3624095
1334 SELECT htuv.tax_unit_id
1335 FROM hr_tax_units_v htuv,
1336 hr_organization_units hou
1337 WHERE htuv.tax_unit_id(+) = hou.organization_id
1338 AND hou.organization_id = p_tax_unit_id;
1339 */--
1340 --
1341 parent_hr_tax_unit_id number(15);
1342 hr_tax_unit_id number(15);
1343 hr_tax_unit_flag char(2);
1344 l_tax_unit_id number(15);
1345 l_proc varchar2(72);
1346 --
1347 BEGIN
1348 parent_hr_tax_unit_id := null;
1349 hr_tax_unit_id := null;
1350 hr_tax_unit_flag := 'N';
1351 l_proc := 'get_hr_est_tax_unit';
1352
1353 --
1354 hr_utility.set_location('Entering...' || l_proc,10);
1355 OPEN check_if_top_org;
1356 LOOP
1357 BEGIN
1358 FETCH check_if_top_org INTO hr_tax_unit_id;
1359 EXIT WHEN check_if_top_org%NOTFOUND;
1360 EXCEPTION
1361 WHEN NO_DATA_FOUND
1362 THEN hr_tax_unit_id := -1;
1363 END;
1364 END LOOP;
1365 CLOSE check_if_top_org;
1366 --
1367 IF p_starting_org_id = hr_tax_unit_id
1368 THEN
1369 hr_utility.set_location(l_proc,20);
1370 parent_hr_tax_unit_id := p_starting_org_id;
1371 --
1372 ELSE
1373 --
1374 --
1375 OPEN get_hr_parent;
1376 WHILE hr_tax_unit_flag = 'N'
1377 LOOP
1378 FETCH get_hr_parent INTO parent_hr_tax_unit_id;
1379 --hr_utility.trace('Parent hr_tax unit >'||parent_hr_tax_unit_id);
1380 EXIT WHEN get_hr_parent%NOTFOUND;
1381 hr_utility.trace('Parent hr_tax unit >'||parent_hr_tax_unit_id);
1382
1383 /* commented out for BUG3141907
1384 --
1385 SELECT decode(hr_tax_unit_id,'','N','Y')
1386 INTO hr_tax_unit_flag
1387 FROM hr_tax_units_v htuv,
1388 hr_organization_units hou
1389 WHERE htuv.tax_unit_id(+) = hou.organization_id
1390 AND hou.organization_id = parent_hr_tax_unit_id;
1391 */
1392 --
1393 -- BUG3141907
1394 --
1395 hr_utility.set_location(l_proc,30);
1396 open csr_tax_unit_flag(parent_hr_tax_unit_id);
1397 hr_utility.set_location(l_proc,31);
1398 fetch csr_tax_unit_flag into l_tax_unit_id;
1399 if csr_tax_unit_flag%found then
1400 hr_utility.set_location(l_proc,40);
1401 hr_tax_unit_flag := 'Y';
1402 else
1403 hr_utility.set_location(l_proc,50);
1404 hr_tax_unit_flag := 'N';
1405 end if;
1406 close csr_tax_unit_flag;
1407
1408 hr_utility.trace('hr_tax_unit_flag > '||hr_tax_unit_flag);
1409 hr_utility.set_location(l_proc,50);
1410 END LOOP;
1411 CLOSE get_hr_parent;
1412 --
1413 --
1414 END IF;
1415 --
1416 hr_utility.trace('Est hr_tax unit >'||parent_hr_tax_unit_id);
1417 hr_utility.set_location('Leaving...' || l_proc,100);
1418 return (parent_hr_tax_unit_id);
1419 --
1420 end get_hr_est_tax_unit;
1421 --
1422 -- end bug fix 2722353
1423 --
1424 FUNCTION get_org_hierarchy_name (p_org_structure_version_id number
1425 ) RETURN varchar2
1426 IS
1427 --
1428 l_org_hierarchy_name VARCHAR2(30);
1429 --
1430 begin
1431 SELECT pos.name
1432 INTO l_org_hierarchy_name
1433 FROM per_organization_structures pos,
1434 per_org_structure_versions posv
1435 WHERE pos.organization_structure_id = posv.organization_structure_id
1436 AND posv.org_structure_version_id = p_org_structure_version_id;
1437 --
1438 return l_org_hierarchy_name;
1439 --
1440 exception when NO_DATA_FOUND then RETURN NULL;
1441 --
1442 end get_org_hierarchy_name;
1443 --
1444 --
1445 --
1446 FUNCTION get_state_name(p_state_code varchar2
1447 ) RETURN varchar2
1448 IS
1449 --
1450 l_state_name VARCHAR2(60);
1451 --
1452 begin
1453 --
1454 hr_utility.set_location('Entered hr_us_reports.get_state_name',5);
1455 --
1456 SELECT state_name
1457 INTO l_state_name
1458 FROM pay_us_states
1459 WHERE state_abbrev = p_state_code;
1460 --
1461 hr_utility.set_location('Leaving hr_us_reports.get_state_name',10);
1462 --
1463 return l_state_name;
1464 --
1465 exception when NO_DATA_FOUND then RETURN NULL;
1466 --
1467 end get_state_name;
1468 --
1469 --
1470 FUNCTION get_org_name (p_organization_id number, p_business_group_id number
1471 ) RETURN varchar2
1472 IS
1473 --
1474 l_org_name VARCHAR2(240);
1475 --
1476 begin
1477 --
1478 hr_utility.set_location('Entered hr_us_reports.get_org_name',5);
1479 --
1480 SELECT name
1481 INTO l_org_name
1482 FROM hr_organization_units
1483 WHERE organization_id = p_organization_id
1484 AND business_group_id + 0 = p_business_group_id;
1485 --
1486 hr_utility.set_location('Leaving hr_us_reports.get_org_name',10);
1487 return l_org_name;
1488 --
1489 exception when NO_DATA_FOUND then RETURN NULL;
1490 when others then
1491 hr_utility.set_location('Error found in hr_us_reports.get_org_name',15);
1492 RETURN NULL;
1493 --
1494 end get_org_name;
1495 --
1496 --
1497 FUNCTION get_location_code (p_location_id number) RETURN varchar2
1498 IS
1499 --
1500 l_location_code VARCHAR2(60);
1501 --
1502 begin
1503 SELECT location_code
1504 INTO l_location_code
1505 FROM hr_locations
1506 WHERE location_id = p_location_id;
1507 --
1508 return l_location_code;
1509 --
1510 exception when NO_DATA_FOUND then RETURN NULL;
1511 --
1512 end get_location_code;
1513 --
1514 --
1515 FUNCTION get_career_path_name (p_career_path_id number, p_business_group_id number
1516 ) RETURN varchar2
1517 IS
1518 --
1519 l_career_path_name VARCHAR2(60);
1520 --
1521 begin
1522 SELECT name
1523 INTO l_career_path_name
1524 FROM per_career_paths
1525 WHERE career_path_id = p_career_path_id
1526 AND business_group_id + 0 = p_business_group_id;
1527 --
1528 return l_career_path_name;
1529 --
1530 exception when NO_DATA_FOUND then RETURN NULL;
1531 --
1532 end get_career_path_name;
1533 --
1534 --
1535 --
1536 FUNCTION get_aap_org_id (p_aap_name VARCHAR2, p_business_group_id NUMBER
1537 ) RETURN number
1538 IS
1539 --
1540 l_aap_organization_id NUMBER(15):=null;
1541 --
1542 begin
1543 SELECT aap_organization_id
1544 INTO l_aap_organization_id
1545 FROM hr_aap_organizations_v
1546 WHERE aap_name = p_aap_name
1547 AND business_group_id + 0 = p_business_group_id;
1548 --
1549 return (l_aap_organization_id);
1550 --
1551 exception when NO_DATA_FOUND then RETURN NULL;
1552 --
1553 end get_aap_org_id;
1554 --
1555
1556 --
1557 -- bug 3182433 - new function.
1558 --
1559 -- Search top organization id in the hierarchy
1560 --
1561 --
1562 FUNCTION get_top_org_id
1563 (p_business_group_id number
1564 ,p_org_structure_version_id number
1565 ) RETURN number
1566 IS
1567 --
1568 --
1569 --
1570 cursor csr_get_parent(l_organization_id_child number) is
1571 select organization_id_parent
1572 from per_org_structure_elements
1573 where business_group_id = p_business_group_id
1574 and org_structure_version_id = p_org_structure_version_id
1575 and organization_id_child = l_organization_id_child;
1576
1577 cursor csr_get_element is
1578 select '1'
1579 from per_org_structure_elements
1580 where business_group_id = p_business_group_id
1581 and org_structure_version_id = p_org_structure_version_id;
1582
1583 cursor csr_get_max_child_id is
1584 select max(organization_id_child)
1585 from per_org_structure_elements
1586 where business_group_id = p_business_group_id
1587 and org_structure_version_id = p_org_structure_version_id;
1588
1589 --
1590 -- declare local variables
1591 --
1592 l_proc varchar2(72);
1593 l_organization_id_child number(15);
1594 l_organization_id_parent number(15);
1595 l_exists varchar2(1);
1596 --
1597 BEGIN
1598
1599 l_proc := 'hr_us_reports.get_top_org_id';
1600
1601 hr_utility.set_location('Entering...' || l_proc,10);
1602
1603 open csr_get_element;
1604 fetch csr_get_element into l_exists;
1605 if csr_get_element%NOTFOUND then
1606 close csr_get_element;
1607 hr_utility.set_location(l_proc,20);
1608 l_organization_id_child := p_business_group_id;
1609 else
1610 close csr_get_element;
1611 open csr_get_max_child_id;
1612 fetch csr_get_max_child_id into l_organization_id_child;
1613 close csr_get_max_child_id;
1614 hr_utility.trace('l_organization_id_child : ' || l_organization_id_child);
1615 hr_utility.set_location(l_proc,30);
1616 loop
1617 open csr_get_parent(l_organization_id_child);
1618 fetch csr_get_parent into l_organization_id_parent;
1619 exit when csr_get_parent%NOTFOUND;
1620 close csr_get_parent;
1621 hr_utility.trace('l_organization_id_patent : ' || l_organization_id_parent);
1622 l_organization_id_child := l_organization_id_parent;
1623 end loop;
1624 close csr_get_parent;
1625 end if;
1626 hr_utility.trace('top_org_id is ' || l_organization_id_child);
1627 hr_utility.set_location('Leaving...' || l_proc,40);
1628 return l_organization_id_child;
1629 --
1630 exception when NO_DATA_FOUND then RETURN p_business_group_id;
1631 --
1632 end get_top_org_id;
1633 --
1634 -- end of get_top_org_id
1635 --
1636 --
1637
1638 --
1639 -- BUG4346783 for VETS-100 Consolidted Report
1640 -- This function is called from Q_2_STATE query
1641 --
1642 FUNCTION verify_state
1643 (p_date_start in date
1644 ,p_date_end in date
1645 ,p_business_group_id in number
1646 ,p_hierarchy_version_id in number
1647 ,p_state in varchar2
1648 ) return number is
1649 --
1650 --
1651 l_est_node_id number := 0;
1652 l_no_est_emps number := 0;
1653 l_report_yes number := 0;
1654 --
1655 --
1656 -- 1. Get Establishment Entity
1657 --
1658 cursor c_est_entity is
1659 select
1660 pghn1.hierarchy_node_id
1661 from
1662 per_gen_hierarchy_nodes pghn1
1663 ,hr_location_extra_info hlei1
1664 ,hr_location_extra_info hlei2
1665 ,hr_locations_all eloc
1666 where
1667 (pghn1.hierarchy_version_id = P_HIERARCHY_VERSION_ID
1668 and pghn1.node_type = 'EST'
1669 and eloc.location_id = pghn1.entity_id
1670 and hlei1.location_id = pghn1.entity_id
1671 and hlei1.location_id = hlei2.location_id
1672 and hlei1.information_type = 'VETS-100 Specific Information'
1673 and hlei1.lei_information_category= 'VETS-100 Specific Information'
1674 and hlei2.information_type = 'Establishment Information'
1675 and hlei2.lei_information_category= 'Establishment Information'
1676 and hlei2.lei_information10 = 'N'
1677 and eloc.region_2 = P_STATE);
1678 --
1679 -- 2. Count employees within the establishment
1680 --
1681 -- change to per_all_assignments_f (speedier)
1682 cursor c_tot_emps is
1683 select count('ass')
1684 from
1685 per_all_assignments_f ass,
1686 per_gen_hierarchy_nodes pgn
1687 where
1688 ass.business_group_id = P_BUSINESS_GROUP_ID
1689 and ass.assignment_type = 'E'
1690 and ass.primary_flag = 'Y'
1691 -- Bug# 5577840
1692 and P_DATE_END between ass.effective_start_date and ass.effective_end_date
1693 -- Replaced the following conditions with the above query.
1694 /*
1695 and ass.effective_start_date <= P_DATE_END
1696 and ass.effective_end_date >= P_DATE_START
1697 */
1698 and ass.effective_start_date = (select max(paf2.effective_start_date)
1699 from per_all_assignments_f paf2
1700 where paf2.person_id = ass.person_id
1701 and paf2.primary_flag = 'Y'
1702 and paf2.assignment_type = 'E'
1703 and paf2.effective_start_date
1704 <= P_DATE_END)
1705 AND EXISTS (
1706 SELECT 'X'
1707 FROM HR_ORGANIZATION_INFORMATION HOI1,
1708 HR_ORGANIZATION_INFORMATION HOI2
1709 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1710 AND hoi1.org_information_context = 'Reporting Statuses'
1711 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1712 AND ass.employment_category = hoi2.org_information1
1713 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1714 AND hoi2.org_information_context = 'Reporting Categories'
1715 AND hoi1.organization_id = hoi2.organization_id
1716 )
1717 ---- Bug# 5577840
1718 AND ass.location_id = pgn.entity_id
1719 AND (pgn.hierarchy_node_id = l_est_node_id
1720 or pgn.parent_hierarchy_node_id = l_est_node_id)
1721 AND pgn.node_type in ('EST','LOC');
1722 -- Replaced the following condition with the above conditions.
1723 -- and ass.location_id = l_est_entity;
1724 --
1725 begin
1726 --
1727 --
1728 --srw.message('50','STATE -> ' || :STATE);
1729 open c_est_entity;
1730 loop
1731 fetch c_est_entity into l_est_node_id;
1732 --srw.message('56','ENTITY ID '||to_char(l_est_entity));
1733 exit when c_est_entity%notfound;
1734 --
1735 open c_tot_emps;
1736 fetch c_tot_emps into l_no_est_emps;
1737 --srw.message('56','for existing vets query total at location is '
1738 --||to_char(l_no_est_emps));
1739 close c_tot_emps;
1740 --
1741
1742 if l_no_est_emps < 50 -- :P_MINIMUM_NO_OF_EMPLOYEES
1743 then
1744 l_report_yes := 1;
1745 end if;
1746 end loop;
1747 close c_est_entity;
1748 return l_report_yes;
1749 end verify_state;
1750 --
1751 --
1752 --
1753 -- This procedure Added to increase the address length form 30 to 40 for Oregon for Bug#6774707
1754 procedure get_employee_address40(p_person_id in number,
1755 p_address out nocopy varchar2) IS
1756 --
1757 f_address varchar2(300) := NULL;
1758 --
1759 -- address_record per_addresses%rowtype;
1760 --
1761 v_address_line1 per_addresses.address_line1%TYPE;
1762 v_address_line2 per_addresses.address_line2%TYPE;
1763 v_address_line3 per_addresses.address_line3%TYPE;
1764 v_town_or_city per_addresses.town_or_city%TYPE;
1765 v_region_2 per_addresses.region_2%TYPE;
1766 v_postal_code per_addresses.postal_code%TYPE;
1767 --
1768 cursor get_address_record is
1769 select address_line1, address_line2, address_line3,
1770 town_or_city, region_2, postal_code
1771 from per_addresses
1772 where person_id = p_person_id
1773 and primary_flag = 'Y'
1774 and nvl(date_to, sysdate) >= sysdate;
1775 --
1776 begin
1777 --
1778
1779 hr_utility.set_location('Entered hr_us_reports.get_employee_address40', 0);
1780 --
1781 open get_address_record;
1782 --
1783 fetch get_address_record into v_address_line1, v_address_line2,
1784 v_address_line3, v_town_or_city, v_region_2, v_postal_code;
1785 --
1786 hr_utility.set_location('Entered hr_us_reports.get_employee_address40', 5);
1787 --
1788 if get_address_record%found
1789 then
1790 --
1791 if v_address_line1 is not null
1792 then
1793 f_address := rpad(substr(v_address_line1,1,40),41,' ');
1794 end if;
1795 --
1796 if v_address_line2 is not null
1797 then
1798 f_address := f_address ||
1799 rpad(substr(v_address_line2,1,40),41,' ');
1800 end if;
1801 --
1802 if v_address_line3 is not null
1803 then
1804 f_address := f_address ||
1805 rpad(substr(v_address_line3,1,40),41,' ');
1806 end if;
1807 --
1808 if v_town_or_city is not null
1809 then
1810 f_address:= f_address || rpad(v_town_or_city,41,' ');
1811 end if;
1812 --
1813 if v_region_2 is not null
1814 then
1815 f_address := f_address ||v_region_2||' '||
1816 v_postal_code;
1817 end if;
1818 --
1819 hr_utility.set_location('hr_us_reports.get_employee_address40', 10);
1820 close get_address_record;
1821 --
1822 hr_utility.trace('Person Address is '|| f_address);
1823 --
1824 p_address := f_address;
1825 --
1826 end if;
1827 --
1828 hr_utility.set_location('Leaving hr_us_reports.get_employee_address40', 15);
1829 --
1830 exception when NO_DATA_FOUND then NULL;
1831 --
1832 end get_employee_address40;
1833
1834
1835
1836 begin
1837 g_nxt_free_defbal := 1;
1838 --
1839 -- end hr_us_reports
1840 --
1841 end hr_us_reports;
1842 --/
1843 --show errors package body hr_us_reports
1844 --
1845 --select to_date('SQLERROR')
1846 --from user_errors
1847 --where type = 'PACKAGE BODY'
1848 --and name = upper('hr_us_reports')