1 PACKAGE BODY pay_us_get_item_data_pkg AS
2 /* $Header: pyusgitd.pkb 120.3 2007/12/07 10:44:29 vmkulkar noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_get_item_data_pkg
12
13 Purpose
14 The purpose of this package is to derive data and Validate data to support
15 the generation of magnetic tape W2 / SQWL reports for US legilsative
16 requirements.
17
18 This mainly derives following data for W2 / SQWL
19 Contact Person Information
20 Employer Address
21 Employee Address
22 Contact Person Address
23
24 Notes
25 Referenced By: Package pay_us_reporting_utils_pkg
26
27 Notes
28
29 History
30 14-Jul-03 ppanda 115.0 Created
31 11-Sep-03 ppanda 115.2 A new column added to record structure person_name_address
32 This change is the result of YE report requirements for
33 country short name
34 3137594 An error message was being printed on W2 Register which was
35 misleading for multiple primary address for a person
36 Refer bug description for details
37 29-Sep-03 ppanda 115.3 A change made in GET_PERSON_NAME_ADDRESS function
38 For MMREF while fetching a persons Address it was erroring
39 due to multiple address when person is terminated and
40 address changed after termination
41 10-Nov-03 ppanda 115.4 2587381 W2C addded to the report type
42 26-DEC-03 jgoswami 115.7 modified the cursors for person_address
43 which return more than one address found.
44 added assignment_type = 'E' condition in
45 where clause, i.e. assignment is of type Employee
46 29-DEC-03 jgoswami 115.8 3341291 modified the cursors for address which was
47 missing fnd_territories in sql and joining
48 directly to fnd_territories_tl. added where condition
49 for checking user language.
50 05-JAN-04 ppanda 115.10 3141296 When Country code is not for the address code assumes its a US
51 address to set the per_addresses.region_2 as the state for
52 the employee address
53
54 03-NOV-04 asasthan 115.11 2694998 Formatting of contact name.
55 03-NOV-04 asasthan 115.13 2694998 Removed more characters
56 18-aug-06 kvsankar 115.14 4939049 Modified the following cursors to
57 retrieve the country code from the
58 lookup 'PQP_US_COUNTRY_TRANSLATE'
59 * get_mmref_name_address
60 * get_mmref_name_address_eod
61 * get_report_name_address
62 * get_report_name_address_eod
63 23-AUG-06 sausingh 115.14 4939049 Modified the following cursors to
64 retrieve the country code from the
65 lookup 'PQP_US_COUNTRY_TRANSLATE'
66 or TABLE HR_LOCATIONS_ALL depending on condition
67 * get_organization_name_address
68 21-Sep-06 ppanda 115.15 Following cursors were opened but not closed.
69 * get_mmref_name_address_eod
70 * get_report_name_address_eod
71 These open cusrors were causing problem in Federal W-2 Magnetic
72 media to fail with Too many Cursors opened
73 07-DEC-07 vmkulkar 115.16 6644795 Updated get_contact_person_info to capture Title
74 6648007 using 'CS_PERSON'.
75 ******************************************************************************/
76 -- Global Variable
77 g_number NUMBER;
78 l_return varchar2(100);
79 end_date date := to_date('31/12/4712','DD/MM/YYYY');
80
81
82
83 /* --------------------------------------------------------------
84 Name : GET_CONTACT_PERSON_INFO
85 Purpose : Purpose of this function is to get contact person details
86 required for Submitter Record
87 Parameters :
88 p_effective_date -
89 This parameter indicates the year for the function.
90 p_item_name - 'CR_PERSON'
91 identifies Contact Persons details in the Submitter record.
92 p_report_type - This parameter will have the type of the report.
93 eg: 'W2' or 'SQWL'
94 p_format - This parameter will have the format to be printed
95 on W2. eg:'MMREF'
96 p_record_name - This parameter will have the particular
97 record name. eg: RA,RF,RE,RT etc.
98 p_validate - This parameter will check whether it wants to
99 validate the error condition or override the checking.
100 'N'- Override
101 'Y'- Check
102 p_exclude_from_output - This parameter gives the information on
103 whether the record has to be printed or not.
104 'Y'- Do not print.
105 'N'- Print.
106 p_person_id IN person_id of Contact Person
107 p_contact_prsn_name OUT Contact Person Name --out_1
108 p_contact_prsn_phone OUT Contact Person Phone --out_2
109 p_contact_prsn_extension OUT Contact Person Extension --out_3
110 p_contact_prsn_email OUT Contact Person Email --out_4
111 p_contact_prsn_fax OUT Contact Person Fax --out_5
112 p_contact_prsn_first_name OUT Contact Person First Name --out_6
113 p_contact_prsn_middle_name OUT Contact Person Middle Name --out_7
114 p_contact_prsn_last_name OUT Contact Person Last Name --out_8
115
116 Error checking
117
118 Special Note :
119 ---------------------------------------------------------------- */
120 FUNCTION GET_CONTACT_PERSON_INFO(
121 p_assignment_id IN number,
122 p_date_earned IN date,
123 p_tax_unit_id IN number,
124 p_effective_date IN varchar2,
125 p_item_name IN varchar2,
126 p_report_type IN varchar2,
127 p_format IN varchar2,
128 p_report_qualifier IN varchar2,
129 p_record_name IN varchar2,
130 p_person_id IN varchar2,
131 p_validate IN varchar2,
132 p_exclude_from_output OUT nocopy varchar2,
133 p_contact_prsn_name OUT nocopy varchar2,
134 p_contact_prsn_phone OUT nocopy varchar2,
135 p_contact_prsn_extension OUT nocopy varchar2,
136 p_contact_prsn_email OUT nocopy varchar2,
137 p_contact_prsn_fax OUT nocopy varchar2,
138 p_contact_prsn_first_name OUT nocopy varchar2,
139 p_contact_prsn_middle_name OUT nocopy varchar2,
140 p_contact_prsn_last_name OUT nocopy varchar2
141 ) RETURN VARCHAR2 IS
142
143 -- Cursor to fetch Contact person Name and Email Address
144 --
145 CURSOR get_person_infm(c_person_id per_all_people_f.person_id%TYPE,
146 c_date_earned date)
147 IS
148 SELECT ppf.first_name ||' '||ppf.middle_names||' '||ppf.last_name,
149 ppf.email_address,
150 ppf.first_name,
151 ppf.middle_names,
152 ppf.last_name,
153 ppf.title
154 FROM per_all_people_f ppf
155 WHERE ppf.person_id = c_person_id
156 AND c_date_earned BETWEEN ppf.effective_start_date
157 AND ppf.effective_end_date;
158
159 -- Cursor to fetch Contact person Phone and Fax #
160 --
161 CURSOR get_phone_fax(c_person_id per_all_people_f.person_id%TYPE,
162 c_date date)
163 IS
164 SELECT phone_number,
165 phone_type
166 FROM per_phones
167 WHERE parent_id = c_person_id
168 AND c_date BETWEEN date_from
169 AND nvl(date_to,end_Date)
170 AND parent_table = 'PER_ALL_PEOPLE_F';
171
172 c_item_name varchar2(100);
173 l_valid_address boolean;
174 l_person_id varchar2(50);
175 l_full_name per_all_people_f.full_name%TYPE := '';
176 l_first_name per_all_people_f.first_name%TYPE := '';
177 l_middle_name per_all_people_f.middle_names%TYPE := '';
178 l_last_name per_all_people_f.last_name%TYPE := '';
179 l_phone per_phones.phone_number%TYPE := '';
180 l_extension varchar2(5) := '';
181 l_phone_type per_phones.phone_type%TYPE := '';
182 l_fax per_phones.phone_number%TYPE := '';
183 l_email per_all_people_f.email_address%TYPE := '';
184 l_title per_all_people_f.title%TYPE := '';
185
186 BEGIN
187 c_item_name:='CR_PERSON';
188 l_valid_address:=FALSE;
189 hr_utility.trace('Fetching Contact Person Information CR_PERSON');
190 l_person_id:=replace(p_person_id,' ');
191 OPEN get_person_infm(to_number(l_person_id),
192 p_date_earned);
193
194 FETCH get_person_infm INTO l_full_name,
195 l_email,
196 l_first_name,
197 l_middle_name,
198 l_last_name,
199 l_title;
200
201 IF get_person_infm%NOTFOUND THEN
202 --{
203 p_contact_prsn_name :=l_full_name;
204 p_contact_prsn_phone :=l_phone;
205 p_contact_prsn_extension :=l_extension;
206 p_contact_prsn_email :=l_email;
207 hr_utility.trace('Contact person information not found');
208 --}
209 ELSIF get_person_infm%FOUND THEN
210 --{
211 p_contact_prsn_name := l_full_name;
212 p_contact_prsn_email := l_email;
213 p_contact_prsn_first_name := l_first_name;
214 p_contact_prsn_middle_name := l_middle_name;
215 p_contact_prsn_last_name := l_last_name;
216
217 l_title := trim(replace(l_title,'.'));
218
219 --
220 -- Fetching Contact Person Phone and Fax
221 --
222 OPEN get_phone_fax(to_number(l_person_id),
223 p_date_earned);
224 LOOP
225 FETCH get_phone_fax INTO l_phone,
226 l_phone_type;
227 EXIT WHEN get_phone_fax%NOTFOUND;
228
229 IF l_phone_type = 'W1' THEN -- Work Phone
230 p_contact_prsn_phone :=
231 replace(pay_us_reporting_utils_pkg.Character_check(l_phone),'-');
232 hr_utility.trace('Contact person PHONE Found.'||p_contact_prsn_phone);
233 ELSIF l_phone_type = 'WF' THEN -- Work Fax
234 p_contact_prsn_fax :=
235 replace(pay_us_reporting_utils_pkg.Character_check(l_phone),'-');
236 hr_utility.trace('Contact Person Fax found.'||p_contact_prsn_fax);
237 END IF;
238
239 END LOOP; /* get_phone_fax */
240 CLOSE get_phone_fax;
241 p_contact_prsn_phone :=
242 replace(replace(replace
243 (replace(replace(replace(replace(replace
244 (upper(p_contact_prsn_phone),
245 'E'),'X'),'T'),' '),'.'),'('),')'),'-');
246 p_contact_prsn_extension :=
247 rpad(nvl(substr(p_contact_prsn_phone,11,5),' '),5); --Extension
248 p_contact_prsn_phone :=
249 rpad(substr(nvl(p_contact_prsn_phone,' '),1,10),15); --Phone number
250 hr_utility.trace('Contact person PHONE Extension '||p_contact_prsn_extension);
251 --}
252 IF c_item_name <> 'CS_PERSON' THEN -- For title
253
254 IF p_report_type IN ( 'W2', 'W2C') THEN
255 --{
256 --
257 -- This procedure formats contact information fields specific to W2 reporting
258 --
259 hr_utility.trace('Formatting Contact Person Info for W2 reporting ');
260 pay_us_mmrf_w2_format_record.format_w2_contact_prsn_info(
261 p_report_qualifier,
262 p_record_name,
263 p_validate,
264 p_exclude_from_output,
265 p_contact_prsn_name,
266 p_contact_prsn_phone,
267 p_contact_prsn_extension,
268 p_contact_prsn_email,
269 p_contact_prsn_fax,
270 p_contact_prsn_first_name,
271 p_contact_prsn_middle_name,
272 p_contact_prsn_last_name
273 );
274 --}
275 hr_utility.trace('Contact Person info formatted for W2 reporting ');
276 ELSIF p_report_type = 'SQWL' THEN
277 --{
278 --
279 -- This procedure formats contact information fields specific to SQWL reporting
280 --
281 hr_utility.trace('Formatting Contact Person Info for SQWL reporting ');
282 pay_us_mmrf_sqwl_format_record.format_sqwl_contact_prsn_info(
283 p_report_qualifier,
284 p_record_name,
285 p_validate,
286 p_exclude_from_output,
287 p_contact_prsn_name,
288 p_contact_prsn_phone,
289 p_contact_prsn_extension,
290 p_contact_prsn_email,
291 p_contact_prsn_fax,
292 p_contact_prsn_first_name,
293 p_contact_prsn_middle_name,
294 p_contact_prsn_last_name
295 );
296 --}
297 END IF;
298
299 ELSE -- For title
300
301 p_contact_prsn_email := l_title; -- Only for Mary Land State W2 RV Record
302
303 /* vmkulkar - Contact person Title should we displayed in the MD RV Record.
304 So using p_contact_prsn_email(out4) for passing TITLE back to the formula.
305 ITEM NAME used is 'CS_PERSON' */
306
307 END IF; -- For title
308
309 hr_utility.trace('Contact Person Information found. Full Name = '
310 ||p_contact_prsn_name);
311 END IF; --get_person_infm%NOTFOUND
312 CLOSE get_person_infm;
313 return p_contact_prsn_name;
314 END GET_CONTACT_PERSON_INFO;
315 --
316 -- End of Function that derives Contact Person Information
317 --
318
319 --
320 -- Function to Get Employee Address
321 --
322 /*
323 Parameters :
324 p_effective_date -
325 This parameter indicates the year for the function.
326 p_item_name - 'EE_ADDRESS'
327 identifies Employee Address required for
328 Employee record.
332 on W2. eg:'MMREF'
329 p_report_type - This parameter will have the type of the report.
330 eg: 'W2' or 'SQWL'
331 p_format - This parameter will have the format to be printed
333 p_record_name - This parameter will have the particular
334 record name. eg: RE
335 p_validate - This parameter will check whether it wants to
336 validate the error condition or override the
337 checking.
338 'N'- Override
339 'Y'- Check
340 p_exclude_from_output -
341 This parameter gives the information on
342 whether the record has to be printed or not.
343 'Y'- Do not print.
344 'N'- Print.
345 p_input_2 - Application Session Date this would be used to
346 fetch the address
347 sp_out_1 - This out parameter returns Employee Location Address
348 sp_out_2 - This out parameter returns Employee Deliver Address
349 sp_out_3 - This out parameter returns Employee City
350 sp_out_4 - This out parameter returns State
351 sp_out_5 - This out parameter returns Zip Code
352 sp_out_6 - This out parameter returns Zip Code Extension
353 sp_out_7 - This out parameter returns Foreign State/Province
354 sp_out_8 - This out parameter returns Foreign Postal Code
355 sp_out_9 - This out parameter returns Foreign Country Code
356 sp_out_10 - This parameter is returns Employee Number
357 */
358 FUNCTION GET_MMREF_EMPLOYEE_ADDRESS(
359 p_assignment_id IN number,
360 p_date_earned IN date,
361 p_tax_unit_id IN number,
362 p_effective_date IN varchar2,
363 p_item_name IN varchar2,
364 p_report_type IN varchar2,
365 p_format IN varchar2,
366 p_report_qualifier IN varchar2,
367 p_record_name IN varchar2,
368 p_input_1 IN varchar2,
369 p_input_2 IN varchar2,
370 p_input_3 IN varchar2,
371 p_input_4 IN varchar2,
372 p_input_5 IN varchar2,
373 p_validate IN varchar2,
374 p_exclude_from_output OUT nocopy varchar2,
375 sp_out_1 OUT nocopy varchar2,
376 sp_out_2 OUT nocopy varchar2,
377 sp_out_3 OUT nocopy varchar2,
378 sp_out_4 OUT nocopy varchar2,
379 sp_out_5 OUT nocopy varchar2,
380 sp_out_6 OUT nocopy varchar2,
381 sp_out_7 OUT nocopy varchar2,
382 sp_out_8 OUT nocopy varchar2,
383 sp_out_9 OUT nocopy varchar2,
384 sp_out_10 OUT nocopy varchar2
385 ) RETURN VARCHAR2 IS
386 -- Local Variable Declaration
387 --
388
389 c_item_name varchar2(40);
390 c_tax_unit_id hr_all_organization_units.organization_id%TYPE;
391 l_organization_name hr_organization_units.name%TYPE;
392 l_person_id number(10);
393 l_locality_company_id varchar2(50);
394 lr_employee_addr pay_us_get_item_data_pkg.person_name_address;
395 l_effective_date date;
396 l_input_2 varchar2(200);
397 BEGIN
398 hr_utility.trace('In function GET_MMREF_EMPLOYEE_ADDRESS');
399 c_item_name:='EE_ADDRESS';
400 --
401 -- This change is made for Mag W-2 reporting purpose
402 -- For W2, SQWL and 1099R Mag was not reporting address change beyond
403 -- period end date. This change of effective date is intended to fix
404 -- the address issue
405 --
406 l_input_2 := ltrim(rtrim(p_input_2));
407 if l_input_2 is not null then
408 l_effective_date := fnd_date.canonical_TO_DATE(l_input_2);
409 else
410 l_effective_date := p_effective_date;
411 end if;
412 hr_utility.trace('In function GET_MMREF_EMPLOYEE_ADDRESS');
413 --
414 lr_employee_addr :=
415 pay_us_get_item_data_pkg.GET_PERSON_NAME_ADDRESS(
416 p_report_type,
417 l_person_id,
418 p_assignment_id,
419 p_date_earned,
420 l_effective_date,
421 p_validate,
422 p_record_name);
423 hr_utility.trace('Employee '||lr_employee_addr.full_name ||' Info found ');
424 hr_utility.trace('Formatting Employee Address for '||p_report_type
425 ||' Reporting ');
426 --
427 -- Format Employee Address for W2 / SQWL
428 --
429 pay_us_mmrf_w2_format_record.format_mmref_address (
430 lr_employee_addr.full_name,
431 l_locality_company_id,
432 lr_employee_addr.employee_number,
433 lr_employee_addr.addr_line_1,
437 lr_employee_addr.province_state,
434 lr_employee_addr.addr_line_2,
435 lr_employee_addr.addr_line_3,
436 lr_employee_addr.city,
438 lr_employee_addr.postal_code,
439 lr_employee_addr.country,
440 lr_employee_addr.region_1,
441 lr_employee_addr.region_2,
442 lr_employee_addr.valid_address,
443 p_item_name,
444 p_report_type,
445 p_record_name,
446 p_validate,
447 p_input_1,
448 p_exclude_from_output,
449 sp_out_1,
450 sp_out_2,
451 sp_out_3,
452 sp_out_4,
453 sp_out_5,
454 sp_out_6,
455 sp_out_7,
456 sp_out_8,
457 sp_out_9,
458 sp_out_10
459 );
460 RETURN sp_out_1;
461 END GET_MMREF_EMPLOYEE_ADDRESS;
462 -- End of function to fetch Employee Address used for W2/SQWL reporting
463 --
464
465 --
466 -- Function GET_ER_ADDRESS to Get Employer Address
467 --
468 /*
469 Parameters :
470 p_effective_date -
471 This parameter indicates the year for the function.
472 p_item_name - 'ER_ADDRESS'
473 identifies Employee Address required for
474 Employee record.
475 p_report_type - This parameter will have the type of the report.
476 eg: 'W2' or 'SQWL'
477 p_format - This parameter will have the format to be printed
478 on W2. eg:'MMREF'
479 p_record_name - This parameter will have the particular
480 record name. eg: RE
481 p_validate - This parameter will check whether it wants to
482 validate the error condition or override the
483 checking.
484 'N'- Override
485 'Y'- Check
486 p_exclude_from_output -
487 This parameter gives the information on
488 whether the record has to be printed or not.
489 'Y'- Do not print.
490 'N'- Print.
491 sp_out_1 - This out parameter returns Employer Location Address
492 sp_out_2 - This out parameter returns Employer Deliver Address
493 sp_out_3 - This out parameter returns Employer City
494 sp_out_4 - This out parameter returns State
495 sp_out_5 - This out parameter returns Zip Code
496 sp_out_6 - This out parameter returns Zip Code Extension
497 sp_out_7 - This out parameter returns Foreign State/Province
498 sp_out_8 - This out parameter returns Locality Company ID
499 (Currently only Philadelphia Local W2 uses this)
500 sp_out_9 - This out parameter returns Foreign Country Code
501 sp_out_10 - This parameter is returns Organization Name
502 */
503 FUNCTION GET_MMREF_EMPLOYER_ADDRESS (
504 p_assignment_id IN number,
505 p_date_earned IN date,
506 p_tax_unit_id IN number,
507 p_effective_date IN varchar2,
508 p_item_name IN varchar2,
509 p_report_type IN varchar2,
510 p_format IN varchar2,
511 p_report_qualifier IN varchar2,
512 p_record_name IN varchar2,
513 p_input_1 IN varchar2,
514 p_input_2 IN varchar2,
515 p_input_3 IN varchar2,
516 p_input_4 IN varchar2,
517 p_input_5 IN varchar2,
518 p_validate IN varchar2,
519 p_exclude_from_output OUT nocopy varchar2,
520 sp_out_1 OUT nocopy varchar2,
521 sp_out_2 OUT nocopy varchar2,
522 sp_out_3 OUT nocopy varchar2,
523 sp_out_4 OUT nocopy varchar2,
524 sp_out_5 OUT nocopy varchar2,
525 sp_out_6 OUT nocopy varchar2,
526 sp_out_7 OUT nocopy varchar2,
527 sp_out_8 OUT nocopy varchar2,
528 sp_out_9 OUT nocopy varchar2,
529 sp_out_10 OUT nocopy varchar2
530 ) RETURN VARCHAR2 IS
531
532 /* Philadelphi Locality Company Id */
533 CURSOR get_locality_company_id(c_tax_unit_id hr_organization_units.organization_id%type)
534 IS
535 select org_information2
536 from hr_organization_information hoi
537 WHERE hoi.organization_id = c_tax_unit_id
538 and hoi.org_information_context = 'Local Tax Rules'
539 AND hoi.org_information1 = '39-101-3000';
540
541 c_tax_unit_id hr_all_organization_units.organization_id%TYPE;
545 lr_employer_addr pay_us_get_item_data_pkg.organization_name_address;
542 c_item_name varchar2(40);
543 l_emp_number varchar2(40);
544 l_locality_company_id varchar2(50);
546 BEGIN
547 hr_utility.trace('FUNCTION GET_MMREF_EMPLOYER_ADDRESS for Employer Address');
548 c_tax_unit_id := p_tax_unit_id;
549 c_item_name := 'ER_ADDRESS';
550
551 hr_utility.trace('Tax Unit Id = '||to_char(c_tax_unit_id));
552 /* This would be used for Philadelphia locality only
553 subsequently this can be enhanced to generalise for any locality */
554
555 IF p_input_1 = 'PHILA' THEN
556 hr_utility.trace('Fetch Locality_Company_Id ...');
557 OPEN get_locality_company_id(c_tax_unit_id);
558 FETCH get_locality_company_id INTO l_locality_company_id;
559 CLOSE get_locality_company_id ;
560 hr_utility.trace('ER_ADDRESS Locality_Company_Id ...'||
561 l_locality_company_id );
562 END IF;
563 lr_employer_addr :=
564 pay_us_get_item_data_pkg.get_organization_name_address(
565 c_tax_unit_id,
566 p_effective_date,
567 p_validate,
568 p_record_name);
569 --
570 -- Format Employere Address for W2/SQWL reporting
571 --
572 pay_us_mmrf_w2_format_record.format_mmref_address (
573 lr_employer_addr.org_name,
574 l_locality_company_id,
575 l_emp_number,
576 lr_employer_addr.addr_line_1,
577 lr_employer_addr.addr_line_2,
578 lr_employer_addr.addr_line_3,
579 lr_employer_addr.city,
580 lr_employer_addr.province_state,
581 lr_employer_addr.postal_code,
582 lr_employer_addr.country,
583 lr_employer_addr.region_1,
584 lr_employer_addr.region_2,
585 lr_employer_addr.valid_address,
586 p_item_name,
587 p_report_type,
588 p_record_name,
589 p_validate,
590 p_input_1,
591 p_exclude_from_output,
592 sp_out_1,
593 sp_out_2,
594 sp_out_3,
595 sp_out_4,
596 sp_out_5,
597 sp_out_6,
598 sp_out_7,
599 sp_out_8,
600 sp_out_9,
601 sp_out_10
602 );
603 RETURN sp_out_1;
604 END GET_MMREF_EMPLOYER_ADDRESS;
605 --End of Function to fetch Employer Addres used by W2/SQWL
606 --
607
608 --
609 -- This function used to fetch Person Name and Address
610 -- For MMREF Assignment_id and Effective_Date/Date Earn are Input
611 -- For all other reporting need Person_id and Effective date are Input
612 -- Parameter Description
613 -- p_report_type (i.e. MMREF, REPORT)
614 -- p_person_id Person Id
615 -- p_assignment_id Assignment_id
616 -- p_period_end_date For W2 report/MMREF this would be the End of Year Date
617 -- For SQWL this would be end of Quater Date
618 -- For other reports this would be address effective date
619 -- p_effective_date Session effective Date for processing reports
620 -- p_validate Flag for Validation to be performed or skipped
621 -- p_source_type For MMREF purpose this would be the value of Mag Record
622 -- (i.e. RA, RE, RW, RS etc.)
623 -- For other Report this would be 'REPORT'
624
625
626 FUNCTION GET_PERSON_NAME_ADDRESS(
627 p_report_type IN varchar2,
628 p_person_id IN number,
629 p_assignment_id IN number,
630 p_period_end_date IN date,
631 p_effective_date IN date,
632 p_validate IN varchar2,
633 p_source_type IN varchar2)
634 RETURN person_name_address
635 AS
636
637 lr_person_name_address person_name_address;
638
639 --
640 -- This cursor would be used for W2, SQWL and 1099R mag
641 --
642 CURSOR get_mmref_person_name
643 (c_assignment_id per_all_assignments_f.assignment_id%type,
644 c_effective_date date)
645 IS
646 SELECT ppf.full_name ,
647 ppf.employee_number
648 from per_all_assignments_f assign
649 , per_all_people_f ppf
650 where ASSIGN.assignment_id = c_assignment_id
651 and c_effective_date BETWEEN ASSIGN.effective_start_date
652 AND ASSIGN.effective_end_date
653 and assign.person_id = ppf.person_id
654 and assign.assignment_type = 'E'
655 and c_effective_date BETWEEN ppf.effective_start_date
656 AND ppf.effective_end_date;
657
658 --
659 -- This cursor would be used for W2, SQWL, 1099R mag
660 --
661 CURSOR get_mmref_name_address
662 (c_assignment_id per_all_assignments_f.assignment_id%type,
663 c_effective_date date)
664 IS
665 SELECT ppf.full_name ,
666 ppf.employee_number ,
667 addr.address_line1 ,
668 addr.address_line2 ,
672 'US', addr.region_2, addr.region_1 )
669 addr.address_line3 ,
670 addr.town_or_city ,
671 decode(NVL(addr.country,'US'), 'CA', addr.region_1,
673 Province_or_state,
674 addr.postal_code ,
675 substr(hrt.meaning,-2),
676 fttl.territory_short_name,
677 addr.region_1 ,
678 addr.region_2 ,
679 'Y' valid_address
680 from per_all_assignments_f assign
681 , per_addresses addr
682 , per_all_people_f ppf
683 , fnd_territories_tl fttl
684 , fnd_territories ftt
685 , hr_lookups hrt
686 where ASSIGN.assignment_id = c_assignment_id
687 and c_effective_date BETWEEN ASSIGN.effective_start_date
688 AND ASSIGN.effective_end_date
689 and assign.person_id = ppf.person_id
690 and assign.assignment_type = 'E'
691 and c_effective_date BETWEEN ppf.effective_start_date
692 AND ppf.effective_end_date
693 and addr.person_id = ASSIGN.person_id
694 and addr.primary_flag = 'Y'
695 and NVL(addr.country,'US') = ftt.territory_code
696 and ftt.territory_code = fttl.territory_code
697 and fttl.language = USERENV('LANG')
698 and hrt.lookup_code = ftt.territory_code
699 and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
700 and c_effective_date BETWEEN ADDR.date_from
701 AND nvl(ADDR.date_to,end_date);
702 --
703 -- This cursor would be used for W2 and SQWL when person address not found
704 -- for the given effective_date. This cursor will fetch employee current
705 -- primary address
706 --
707 CURSOR get_mmref_name_address_eod
708 (c_assignment_id per_all_assignments_f.assignment_id%type)
709 IS
710 SELECT ppf.full_name ,
711 ppf.employee_number ,
712 addr.address_line1 ,
713 addr.address_line2 ,
714 addr.address_line3 ,
715 addr.town_or_city ,
716 decode(NVL(addr.country,'US'), 'CA', addr.region_1,
717 'US', addr.region_2, addr.region_1 )
718 Province_or_state,
719 addr.postal_code ,
720 substr(hrt.meaning,-2) ,
721 fttl.territory_short_name,
722 addr.region_1 ,
723 addr.region_2 ,
724 'Y' valid_address
725 FROM per_all_assignments_f ASSIGN
726 , per_addresses ADDR
727 , per_all_people_f PPF
728 , fnd_territories_tl fttl
729 , fnd_territories ftt
730 , hr_lookups hrt
731 where ASSIGN.assignment_id = c_assignment_id
732 and ASSIGN.person_id = ppf.person_id
733 and assign.assignment_type = 'E'
734 and ADDR.person_id = ASSIGN.person_id
735 and NVL(addr.country,'US') = ftt.territory_code
736 and ftt.territory_code = fttl.territory_code
737 and hrt.lookup_code = ftt.territory_code
738 and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
739 and fttl.language = USERENV('LANG')
740 and ADDR.primary_flag = 'Y'
741 and addr.date_to is NULL
742 order by addr.address_id desc;
743
744 --
745 -- For any report that needs to fetch person name and address inputing
746 -- person_id and effective date.
747 --
748 CURSOR get_report_name_address
749 (c_person_id per_all_people_f.person_id%type,
750 c_effective_date date)
751 IS
752 SELECT ppf.full_name ,
753 ppf.employee_number ,
754 addr.address_line1 ,
755 addr.address_line2 ,
756 addr.address_line3 ,
757 addr.town_or_city ,
758 decode(NVL(addr.country,'US'), 'CA', addr.region_1,
759 'US', addr.region_2, addr.region_1 )
760 Province_or_state,
761 addr.postal_code ,
762 substr(hrt.meaning,-2),
763 fttl.territory_short_name,
764 addr.region_1 ,
765 addr.region_2 ,
766 'Y' valid_address
767 FROM per_addresses ADDR
768 , per_all_people_f PPF
769 , fnd_territories_tl fttl
770 , fnd_territories ftt
771 , hr_lookups hrt
772 where ppf.person_id = c_person_id
773 and c_effective_date BETWEEN ppf.effective_start_date
774 AND ppf.effective_end_date
775 and ADDR.person_id = ppf.person_id
776 and ADDR.primary_flag = 'Y'
777 and NVL(addr.country,'US') = ftt.territory_code
778 and ftt.territory_code = fttl.territory_code
779 and hrt.lookup_code = ftt.territory_code
780 and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
781 and fttl.language = USERENV('LANG')
782 and c_effective_date BETWEEN ADDR.date_from
783 AND nvl(ADDR.date_to, end_date);
784 --
785 -- Get Person address for eod (i.e. end of date)
786 -- This would be used by most of Year end report to fetch person
787 -- address when effective date is greater then period_end_date
788 -- for instance for W2, if effective_date is greater then year end date
789 -- this will b
790 CURSOR get_report_name_address_eod
791 (c_person_id per_all_people_f.person_id%type)
792 IS
796 addr.address_line2 ,
793 SELECT ppf.full_name ,
794 ppf.employee_number ,
795 addr.address_line1 ,
797 addr.address_line3 ,
798 addr.town_or_city ,
799 decode(NVL(addr.country,'US'), 'CA', addr.region_1,
800 'US', addr.region_2, addr.region_1 )
801 Province_or_state,
802 addr.postal_code ,
803 substr(hrt.meaning,-2),
804 fttl.territory_short_name,
805 addr.region_1 ,
806 addr.region_2 ,
807 'Y' valid_address
808 FROM per_addresses addr
809 , per_all_people_f ppf
810 , fnd_territories_tl fttl
811 , fnd_territories ftt
812 , hr_lookups hrt
813 where ppf.person_id = c_person_id
814 and addr.person_id = ppf.person_id
815 and addr.primary_flag = 'Y'
816 and NVL(addr.country,'US') = ftt.territory_code
817 and ftt.territory_code = fttl.territory_code
818 and hrt.lookup_code = ftt.territory_code
819 and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
820 and fttl.language = USERENV('LANG')
821 and addr.date_to is NULL
822 order by addr.address_id desc;
823
824 --
825 -- This cursor would be used for W2, 1099R report to fetch Person Name
826 -- and Employee Number
827 CURSOR get_report_person_name
828 (c_person_id per_all_people_f.person_id%type,
829 c_effective_date date)
830 IS
831 SELECT ppf.full_name ,
832 ppf.employee_number
833 from per_all_people_f ppf
834 where ppf.person_id = c_person_id
835 and c_effective_date BETWEEN ppf.effective_start_date
836 AND ppf.effective_end_date;
837
838 l_addr_count number(10) := 0;
839 l_valid_address varchar2(3) := 'N';
840 l_emp_number per_all_people_f.employee_number%type;
841 l_full_name per_all_people_f.full_name%TYPE;
842 /*
843 l_address_line_1 per_addresses.address_line1%type;
844 l_address_line_2 per_addresses.address_line2%type;
845 l_address_line_3 per_addresses.address_line3%type;
846 l_city per_addresses.town_or_city%type;
847 l_region_1 per_addresses.region_1%TYPE;
848 l_region_2 per_addresses.region_2%TYPE;
849 l_postal_code per_addresses.postal_code%TYPE;
850 l_country per_addresses.country%type;
851 */
852 l_too_many_adr_token3 varchar2(50):='More than one address found';
853 l_token2 varchar2(50);
854 l_effective_date date;
855 l_record varchar2(50);
856 l_report_type varchar2(50);
857 l_name_count number := 0;
858 BEGIN
859 l_addr_count := 0;
860 l_valid_address := 'N';
861 if p_period_end_date > p_effective_date then
862 l_effective_date := p_period_end_date;
863 else
864 l_effective_date := p_effective_date;
865 end if;
866 --
867 hr_utility.trace('Date Earned or Period End Date '||to_char(p_period_end_date,'dd-mon-yyyy'));
868 hr_utility.trace('Date Effective or Session Date '||to_char(l_effective_date,'dd-mon-yyyy'));
869 --
870 if p_report_type IN ('W2','SQWL','1099R','W2C') then
871 l_report_type := 'MMREF';
872 else
873 l_report_type := p_report_type;
874 end if;
875
876 if l_report_type = 'MMREF' THEN
877 --{
878 open get_mmref_name_address(p_assignment_id,
879 l_effective_date);
880 LOOP
881 FETCH get_mmref_name_address INTO lr_person_name_address;
882 l_addr_count := get_mmref_name_address%ROWCOUNT;
883 EXIT WHEN l_addr_count > 1 or get_mmref_name_address%NOTFOUND;
884 END LOOP;
885 CLOSE get_mmref_name_address;
886 if l_addr_count = 0 then
887 open get_mmref_name_address_eod(p_assignment_id);
888 loop
889 fetch get_mmref_name_address_eod INTO lr_person_name_address;
890 l_addr_count := get_mmref_name_address_eod%ROWCOUNT;
891 exit when l_addr_count > 1 or get_mmref_name_address_eod%NOTFOUND;
892 end loop;
893 close get_mmref_name_address_eod;
894 if l_addr_count = 0 then
895 open get_mmref_person_name(p_assignment_id,
896 l_effective_date);
897 LOOP
898 FETCH get_mmref_person_name INTO l_full_name,
899 l_emp_number;
900 lr_person_name_address.full_name := l_full_name;
901 lr_person_name_address.employee_number := l_emp_number;
902 l_name_count := get_mmref_person_name%ROWCOUNT;
903 EXIT WHEN l_name_count > 1 or get_mmref_person_name%NOTFOUND;
904 END LOOP;
905 CLOSE get_mmref_person_name;
906 l_token2 := 'No Address found for Employee number '||
907 lr_person_name_address.employee_number;
908 elsif l_addr_count > 1 then
909 l_addr_count := 1;
910 end if;
911 end if;
912 l_record := p_source_type|| ' record';
913 --}
914 else
915 --{
916 open get_report_name_address(p_person_id,
917 l_effective_date);
918 LOOP
919 FETCH get_report_name_address INTO lr_person_name_address;
920 l_addr_count := get_report_name_address%ROWCOUNT;
924 l_addr_count := 1;
921 EXIT WHEN l_addr_count > 1 or get_report_name_address%NOTFOUND;
922 END LOOP;
923 if l_addr_count > 1 then
925 end if;
926 CLOSE get_report_name_address;
927 if l_addr_count = 0 then
928 --{
929 open get_report_name_address_eod(p_person_id);
930 loop
931 fetch get_report_name_address_eod INTO lr_person_name_address;
932 l_addr_count := get_report_name_address_eod%ROWCOUNT;
933 exit when l_addr_count > 1 or get_report_name_address_eod%NOTFOUND;
934 end loop;
935 close get_report_name_address_eod;
936 if l_addr_count = 0 then
937 open get_report_person_name(p_person_id,
938 l_effective_date);
939 LOOP
940 FETCH get_report_person_name INTO l_full_name,
941 l_emp_number;
942 lr_person_name_address.full_name := l_full_name;
943 lr_person_name_address.employee_number := l_emp_number;
944 l_name_count := get_report_person_name%ROWCOUNT;
945 EXIT WHEN l_name_count > 1 or get_report_person_name%NOTFOUND;
946 END LOOP;
947 CLOSE get_report_person_name;
948 l_token2 := 'No Address found for Employee number '||
949 lr_person_name_address.employee_number;
950 l_record := p_source_type;
951 elsif l_addr_count > 1 then
952 l_addr_count := 1;
953 end if;
954 --}
955 end if;
956 END IF;-- p_report_type
957 -- This is validate person Address
958 IF P_validate = 'Y' THEN
959 --{
960 IF l_addr_count = 0 THEN
961 hr_utility.trace('WARNING: Employee Address not found ');
962 l_valid_address := 'N';
963 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT_WARNING','A');
964 pay_core_utils.push_token('record_name',l_record);
965 pay_core_utils.push_token('name_or_number',
966 lr_person_name_address.employee_number);
967 pay_core_utils.push_token('description',l_token2);
968 ELSIF l_addr_count > 1 THEN
969 hr_utility.trace('Too many rows for the address');
970 l_valid_address := 'N';
971 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT_WARNING','A');
972 pay_core_utils.push_token('record_name',l_record);
973 pay_core_utils.push_token('name_or_number',
974 lr_person_name_address.employee_number);
975 pay_core_utils.push_token('description',l_too_many_adr_token3);
976 ELSIF l_addr_count = 1 THEN
977 l_valid_address:= 'Y';
978 hr_utility.trace('Single Primary Valid Address Found ..'||l_valid_address);
979 END IF;
980 --}
981 ELSE
982 l_valid_address:= 'Y';
983 END IF; --p_validate
984 lr_person_name_address.valid_address := l_valid_address;
985 return lr_person_name_address;
986 END GET_PERSON_NAME_ADDRESS;
987 --
988 -- This function may be used to fetch Organization Name and Address
989 -- by inputing organization_id and Effective Date
990 --
991 FUNCTION GET_ORGANIZATION_NAME_ADDRESS(
992 p_organization_id IN number,
993 p_effective_date IN date,
994 p_validate IN varchar2,
995 p_source_type IN varchar2)
996 RETURN organization_name_address
997 IS
998 CURSOR get_organization_name_address
999 (c_organization_id hr_organization_units.organization_id%type,
1000 c_effective_date date)
1001 IS
1002 SELECT hou.name org_name ,
1003 hl.address_line_1,
1004 hl.address_line_2,
1005 hl.address_line_3,
1006 hl.town_or_city,
1007 decode(hl.country, 'CA', hl.region_1,
1008 'US', hl.region_2, hl.region_1) Province_or_state,
1009 hl.postal_code,
1010
1011 nvl(substr(hrl.meaning, -2),hl.country) country_code,
1012 hl.region_1,
1013 hl.region_2,
1014 'Y' valid_address
1015 FROM hr_locations_all hl,
1016 hr_lookups hrl,
1017 hr_all_organization_units hou
1018 WHERE hou.organization_id = c_organization_id
1019 AND hl.location_id = hou.location_id
1020 AND nvl(hl.inactive_date,end_date)>= c_effective_date
1021 AND hrl.lookup_code (+)=hl.country
1022 AND lookup_type (+)='PQP_US_COUNTRY_TRANSLATE';
1023 lr_org_name_address pay_us_get_item_data_pkg.organization_name_address;
1024 l_addr_count number(10) := 0;
1025 l_valid_address varchar2(3) := 'N';
1026 l_too_many_adr_token3 varchar2(50):='More than one address found';
1027 l_token2 varchar2(50);
1028 l_effective_date date;
1029 l_record varchar2(50);
1030
1031 BEGIN
1032 --{
1033 open get_organization_name_address(p_organization_id,
1034 p_effective_date);
1035 LOOP
1036 FETCH get_organization_name_address INTO lr_org_name_address;
1037 l_addr_count := get_organization_name_address%ROWCOUNT;
1038 EXIT WHEN l_addr_count > 1 or get_organization_name_address%NOTFOUND;
1039 END LOOP;
1040 CLOSE get_organization_name_address;
1041 IF p_validate = 'Y' THEN
1042 --{
1043 IF l_addr_count = 0 THEN
1044 l_token2 := 'No data found for GRE ID '||to_char(p_organization_id);
1045 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1049 hr_utility.trace('ER_ADDRESS No data found for Tax_Unit_Id = '
1046 pay_core_utils.push_token('record_name',p_source_type||' record');
1047 pay_core_utils.push_token('description',l_token2);
1048 l_valid_address:='N';
1050 ||to_char(p_organization_id));
1051 ELSIF l_addr_count > 1 THEN
1052 hr_utility.trace('Too many rows for the address');
1053 l_valid_address := 'N';
1054 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','A');
1055 pay_core_utils.push_token('record_name',p_source_type||' record');
1056 pay_core_utils.push_token('description',l_too_many_adr_token3);
1057 ELSIF l_addr_count = 1 THEN
1058 hr_utility.trace('ER_ADDRESS Employer Address Found ');
1059 l_valid_address:='Y';
1060 END IF;
1061 --}
1062 END IF;
1063 --}
1064 lr_org_name_address.valid_address := l_valid_address;
1065 return lr_org_name_address;
1066 END GET_ORGANIZATION_NAME_ADDRESS;
1067
1068 -- End of Function used to fetch Name and address of person
1069 --{
1070 -- Function to get Concurrent Program Parameter Value for a given Payroll_Action_ID
1071 -- This function requires following
1072 FUNCTION GET_CPROG_PARAMETER_VALUE( p_mag_payroll_action_id varchar2,
1073 p_parameter_name varchar2)
1074 RETURN varchar2 IS
1075
1076 -- This cursor would be used to fetch first parameter value
1077 -- in legislative_prameters for a given Payroll_action_id
1078 cursor c_get_legislative_parameter( c_payroll_action_id number)
1079 IS
1080 select legislative_parameters
1081 from pay_payroll_Actions
1082 where payroll_action_id = c_payroll_action_id;
1083
1084 parameter_list varchar2(2000);
1085 start_ptr number;
1086 end_ptr number;
1087 token_val pay_payroll_actions.legislative_parameters%type;
1088 par_value pay_payroll_actions.legislative_parameters%type;
1089 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
1090 BEGIN
1091 --l_payroll_action_id := pay_magtape_generic.get_parameter_value
1092 -- ('TRANSFER_PAYROLL_ACTION_ID');
1093 --
1094 l_payroll_action_id := to_number(p_mag_payroll_action_id);
1095 -- Fetch Legislative_parameter for given Payroll_Action_Id
1096 --
1097 -- open c_get_legislative_parameter(p_mag_payroll_action_id);
1098 open c_get_legislative_parameter(l_payroll_action_id);
1099 loop
1100 fetch c_get_legislative_parameter INTO parameter_list;
1101 exit when c_get_legislative_parameter%NOTFOUND;
1102 end loop;
1103 close c_get_legislative_parameter;
1104 --
1105 token_val := p_parameter_name||'=';
1106 --
1107 start_ptr := instr(parameter_list, token_val) + length(token_val);
1108 end_ptr := instr(parameter_list, ' ',start_ptr);
1109
1110 hr_utility.trace('Magnetic Process Payroll_Action_Id '||to_char(l_payroll_action_id));
1111 --hr_utility.trace('Prameter List '||parameter_list);
1112 --hr_utility.trace('Token Value '||token_val);
1113 --hr_utility.trace('Start Ptr '||to_char(start_ptr));
1114 --hr_utility.trace('End Ptr '||to_char(end_ptr));
1115 --
1116 /* if there is no spaces use then length of the string */
1117 if end_ptr = 0 then
1118 end_ptr := length(parameter_list)+1;
1119 end if;
1120 --
1121 /* Did we find the token */
1122 if instr(parameter_list, token_val) = 0 then
1123 par_value := NULL;
1124 else
1125 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1126 end if;
1127 --
1128 if instr(par_value,'/') <> 0 then
1129 par_value := par_value || ' 00:00:00';
1130 end if;
1131 --
1132 if par_value IS NULL then
1133 par_value := 'NULL';
1134 end if;
1135 --
1136 hr_utility.trace('Parameter Value Returned '||par_value);
1137 return par_value;
1138 END GET_CPROG_PARAMETER_VALUE;
1139 --}
1140 --
1141 --BEGIN
1142 --hr_utility.trace_on(null,'GETITEMDATA');
1143 END pay_us_get_item_data_pkg; --End of Package Body
[Home] [Help]