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