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