[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_RECORD
Source
1 PACKAGE BODY HR_PERSON_RECORD as
2 /* $Header: peperqry.pkb 120.1.12010000.17 2009/02/04 08:50:46 sathkris noship $ */
3
4 --Procedure to populate the person_record
5 procedure GET_PERSON_DETAILS( p_srch_criteria in srch_criteria,
6 p_entity in options,
7 p_person out nocopy person_record,
8 p_error out nocopy varchar2)
9 is
10
11 Type p_person_id is table of per_all_peoplE_f.person_id%type index by binary_integer;
12 Type p_business_group_id is table of per_all_peoplE_f.business_group_id%type index by binary_integer;
13 Type p_srch_date is table of date index by binary_integer;
14
15
16
17 -- Temporary record to fetch and store the person id and business group id for each search criteria
18 TYPE p_srch_filter_tbl1 is record
19 (person_id p_person_id,
20 business_group_id p_business_group_id,
21 p_srch_dt p_srch_date);
22
23 p_cnt number := 1;
24 /* Code Commented for bug 7689952 Start
25 l_per_in_ler_id number;
26 l_lf_evt_ocrd_dt date;
27 Code commented for bug 7689952 End */
28 p_srch_filter_tbl p_srch_filter_tbl1;
29
30 TYPE srch_filtered IS REF CURSOR;
31
32 srch_filtrd srch_filtered;
33
34 -- temporary record for ease of access
35 p_bus_group_id number default null;
36 p_emplmt_category per_assignments_f2.employee_category%type default null;
37 p_empl_category per_assignments_f2.EMPLOYMENT_CATEGORY%type default null;
38
39 -- temporary record for ease of access
40 -- cursor to fetch the person records
41 cursor csr_person_record(p_person_id number,p_business_group_id number,p_eff_date date) is
42 SELECT
43 PERSON_ID,
44 to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD'),
45 to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD'),
46 ppf.BUSINESS_GROUP_ID,
47 hr_general.decode_organization(ppf.BUSINESS_GROUP_ID) BUSINESS_GROUP_DESC,
48 ppf.PERSON_TYPE_ID,
49 pptl.user_person_type,
50 ppt.system_person_type,
51 LAST_NAME,
52 to_char(START_DATE,'YYYY-MM-DD'),
53 APPLICANT_NUMBER,
54 BACKGROUND_CHECK_STATUS,
55 to_char(BACKGROUND_DATE_CHECK,'YYYY-MM-DD'),
56 BLOOD_TYPE,
57 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='BLOOD_TYPE'
58 AND LOOKUP_CODE=BLOOD_TYPE AND p_eff_date between nvl(start_date_active,p_eff_date)
59 AND nvl(end_date_active,p_eff_Date)
60 AND enabled_flag = 'Y')
61 BLOOD_TYPE_DESC,
62 COMMENT_ID,
63 CORRESPONDENCE_LANGUAGE,
64 (SELECT DESCRIPTION FROM FND_LANGUAGES_VL
65 WHERE language_code= CORRESPONDENCE_LANGUAGE)
66 CORRESPONDENCE_LANGUAGE_DESC,
67 CURRENT_APPLICANT_FLAG,
68 CURRENT_EMP_OR_APL_FLAG,
69 CURRENT_EMPLOYEE_FLAG,
70 to_char(DATE_EMPLOYEE_DATA_VERIFIED,'YYYY-MM-DD'),
71 to_char(DATE_OF_BIRTH,'YYYY-MM-DD'),
72 EMAIL_ADDRESS,
73 EMPLOYEE_NUMBER,
74 EXPENSE_CHECK_SEND_TO_ADDRESS,
75 FAST_PATH_EMPLOYEE,
76 FIRST_NAME,
77 FTE_CAPACITY,
78 FULL_NAME,
79 to_char(HOLD_APPLICANT_DATE_UNTIL,'YYYY-MM-DD'),
80 HONORS,
81 INTERNAL_LOCATION,
82 KNOWN_AS,
83 LAST_MEDICAL_TEST_BY,
84 to_char(LAST_MEDICAL_TEST_DATE,'YYYY-MM-DD'),
85 MAILSTOP,
86 MARITAL_STATUS,
87 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='MAR_STATUS'
88 AND LOOKUP_CODE=MARITAL_STATUS AND p_eff_date between nvl(start_date_active,p_eff_date)
89 AND nvl(end_date_active,p_eff_Date)
90 AND enabled_flag = 'Y')
91 MARITAL_STATUS_DESC,
92 MIDDLE_NAMES,
93 NATIONALITY,
94 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='NATIONALITY'
95 AND LOOKUP_CODE=NATIONALITY AND p_eff_date between nvl(start_date_active,p_eff_date)
96 AND nvl(end_date_active,p_eff_Date)
97 AND enabled_flag = 'Y')
98 NATIONALITY_DESC,
99 NATIONAL_IDENTIFIER,
100 (select message_text from fnd_new_messages where message_name = 'HR_NATIONAL_ID_NUMBER_'|| (select to_char(org_information9) from
101 hr_organization_information where organization_id = p_business_group_id
102 and org_information_context = 'Business Group Information')
103 and language_code = USERENV('LANG') ) NATIONAL_ID_LABEL,
104 OFFICE_NUMBER,
105 ON_MILITARY_SERVICE,
106 ORDER_NAME,
107 PRE_NAME_ADJUNCT,
108 PREVIOUS_LAST_NAME,
109 to_char(PROJECTED_START_DATE,'YYYY-MM-DD'),
110 REHIRE_AUTHORIZOR,
111 REHIRE_REASON,
112 REHIRE_RECOMMENDATION,
113 RESUME_EXISTS,
114 to_char(RESUME_LAST_UPDATED,'YYYY-MM-DD'),
115 REGISTERED_DISABLED_FLAG,
116 SECOND_PASSPORT_EXISTS,
117 SEX,
118 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='SEX'
119 AND LOOKUP_CODE=SEX AND p_eff_date between nvl(start_date_active,p_eff_date)
120 AND nvl(end_date_active,p_eff_Date)
121 AND enabled_flag = 'Y')
122 SEX_DESC,
123 STUDENT_STATUS,
124 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='STUDENT_STATUS'
125 AND LOOKUP_CODE=STUDENT_STATUS AND p_eff_date between nvl(start_date_active,p_eff_date)
126 AND nvl(end_date_active,p_eff_Date)
127 AND enabled_flag = 'Y')
128 STUDENT_STATUS_DESC,
129 SUFFIX,
130 TITLE,
131 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='TITLE'
132 AND LOOKUP_CODE=TITLE AND p_eff_date between nvl(start_date_active,p_eff_date)
133 and nvl(end_date_active,p_eff_Date)
134 and enabled_flag = 'Y')
135 TITLE_DESC, -- too many rows - date effective ?
136 VENDOR_ID,
137 WORK_SCHEDULE,
138 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='WORK_SCHEDULE'
139 AND LOOKUP_CODE=WORK_SCHEDULE AND p_eff_date between nvl(start_date_active,p_eff_date)
140 and nvl(end_date_active,p_eff_Date)
141 and enabled_flag = 'Y')
142 WORK_SCHEDULE_DESC,
143 WORK_TELEPHONE,
144 COORD_BEN_MED_PLN_NO,
145 COORD_BEN_NO_CVG_FLAG,
146 to_char(DPDNT_ADOPTION_DATE,'YYYY-MM-DD'),
147 DPDNT_VLNTRY_SVCE_FLAG,
148 to_char(RECEIPT_OF_DEATH_CERT_DATE,'YYYY-MM-DD'),
149 USES_TOBACCO_FLAG,
150 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='TOBACCO_USER'
151 AND LOOKUP_CODE=USES_TOBACCO_FLAG AND p_eff_date between nvl(start_date_active,p_eff_date)
152 and nvl(end_date_active,p_eff_Date)
153 and enabled_flag = 'Y')
154 USES_TOBACCO_FLAG_DESC,
155 BENEFIT_GROUP_ID,
156 (SELECT NAME FROM BEN_BENFTS_GRP
157 WHERE BENFTS_GRP_ID = BENEFIT_GROUP_ID)
158 BENEFIT_GROUP_NAME,
159 ATTRIBUTE_CATEGORY,
160 ATTRIBUTE1,
161 ATTRIBUTE2,
162 ATTRIBUTE3,
163 ATTRIBUTE4,
164 ATTRIBUTE5,
165 ATTRIBUTE6,
166 ATTRIBUTE7,
167 ATTRIBUTE8,
168 ATTRIBUTE9,
169 ATTRIBUTE10,
170 ATTRIBUTE11,
171 ATTRIBUTE12,
172 ATTRIBUTE13,
173 ATTRIBUTE14,
174 ATTRIBUTE15,
175 ATTRIBUTE16,
176 ATTRIBUTE17,
177 ATTRIBUTE18,
178 ATTRIBUTE19,
179 ATTRIBUTE20,
180 ATTRIBUTE21,
181 ATTRIBUTE22,
182 ATTRIBUTE23,
183 ATTRIBUTE24,
184 ATTRIBUTE25,
185 ATTRIBUTE26,
186 ATTRIBUTE27,
187 ATTRIBUTE28,
188 ATTRIBUTE29,
189 ATTRIBUTE30,
190 PER_INFORMATION_CATEGORY,
191 PER_INFORMATION1,
192 PER_INFORMATION2,
193 PER_INFORMATION3,
194 PER_INFORMATION4,
195 PER_INFORMATION5,
196 PER_INFORMATION6,
197 PER_INFORMATION7,
198 PER_INFORMATION8,
199 PER_INFORMATION9,
200 PER_INFORMATION10,
201 PER_INFORMATION11,
202 PER_INFORMATION12,
203 PER_INFORMATION13,
204 PER_INFORMATION14,
205 PER_INFORMATION15,
206 PER_INFORMATION16,
207 PER_INFORMATION17,
208 PER_INFORMATION18,
209 PER_INFORMATION19,
210 PER_INFORMATION20,
211 PER_INFORMATION21,
212 PER_INFORMATION22,
213 PER_INFORMATION23,
214 PER_INFORMATION24,
215 PER_INFORMATION25,
216 PER_INFORMATION26,
217 PER_INFORMATION27,
218 PER_INFORMATION28,
219 PER_INFORMATION29,
220 PER_INFORMATION30,
221 to_char(DATE_OF_DEATH,'YYYY-MM-DD'),
222 to_char(ORIGINAL_DATE_OF_HIRE,'YYYY-MM-DD'),
223 TOWN_OF_BIRTH,
224 REGION_OF_BIRTH,
225 COUNTRY_OF_BIRTH,
226 (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL
227 WHERE TERRITORY_CODE = COUNTRY_OF_BIRTH)
228 COUNTRY_OF_BIRTH_DESC,
229 GLOBAL_PERSON_ID,
230 COORD_BEN_MED_PL_NAME,
231 COORD_BEN_MED_INSR_CRR_NAME,
232 COORD_BEN_MED_INSR_CRR_IDENT,
233 COORD_BEN_MED_EXT_ER,
234 to_char(COORD_BEN_MED_CVG_STRT_DT,'YYYY-MM-DD'),
235 to_char(COORD_BEN_MED_CVG_END_DT,'YYYY-MM-DD'),
236 PARTY_ID,
237 NPW_NUMBER,
238 CURRENT_NPW_FLAG,
239 GLOBAL_NAME,
240 LOCAL_NAME
241 FROM PER_PEOPLE_F ppf,
242 per_person_types ppt,
243 per_person_types_v pptl
244 where ppf.person_id = p_person_id
245 and ppf.business_group_id = p_business_group_id
246 and ppt.person_type_id = pptl.person_type_id
247 and ppf.person_type_id = ppt.person_type_id
248 and ppf.business_group_id = ppt.business_group_id (+)
249 and p_eff_date between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'));
250
251 -- cursor to fetch the assignment records
252 cursor csr_assignment_record(p_person_id number,p_business_group_id number,p_eff_date date) IS
253 SELECT ASSIGNMENT_ID
254 , to_char(PAAF.EFFECTIVE_START_DATE,'YYYY-MM-DD')
255 , to_char(PAAF.EFFECTIVE_END_DATE,'YYYY-MM-DD')
256 , RECRUITER_ID
257 , GRADE_ID
258 , (SELECT NAME FROM PER_GRADES_TL PGT WHERE PGT.GRADE_ID = paaf.GRADE_ID
259 AND LANGUAGE = USERENV('LANG')) GRADE_DESC
260 , POSITION_ID
261 , (SELECT NAME FROM HR_ALL_POSITIONS_F_TL HAPFT WHERE HAPFT.POSITION_ID = paaf.POSITION_ID
262 AND LANGUAGE = USERENV('LANG')) POSITION_DESC
263 , JOB_ID
264 , (SELECT NAME FROM PER_JOBS_TL PJT WHERE PJT.JOB_ID = paaf.JOB_ID AND LANGUAGE = USERENV('LANG')) JOB_DESC
265 , PAAF.ASSIGNMENT_STATUS_TYPE_ID
266 , STTL.USER_STATUS
267 , ST.PAY_SYSTEM_STATUS
268 , ST.PER_SYSTEM_STATUS
269 , PAYROLL_ID
270 , (SELECT PAYROLL_NAME FROM PAY_PAYROLLS_F PPF WHERE PPF.PAYROLL_ID = PAAF.PAYROLL_ID
271 and p_eff_date between ppf.effective_start_date and
272 nvl(ppf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) PAYROLL_DESC
273 , LOCATION_ID
274 , (SELECT DESCRIPTION FROM HR_LOCATIONS_ALL_TL HLAT WHERE HLAT.LOCATION_ID = PAAF.LOCATION_ID
275 AND LANGUAGE = USERENV('LANG')) LOCATION_DESC
276 , PERSON_REFERRED_BY_ID
277 , SUPERVISOR_ID
278 , (SELECT EMPLOYEE_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
279 and p_eff_date between papf.effective_start_date and
280 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_EMP_NUMBER
281 , (SELECT NPW_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
282 and p_eff_date between papf.effective_start_date and
283 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_CWK_NUMBER
284 , (SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
285 and p_eff_date between papf.effective_start_date and
286 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_NAME
287 , SPECIAL_CEILING_STEP_ID
288 , RECRUITMENT_ACTIVITY_ID
289 , SOURCE_ORGANIZATION_ID
290 , ORGANIZATION_ID
291 , (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS_TL HAOUT WHERE HAOUT.ORGANIZATION_ID =
292 PAAF.ORGANIZATION_ID AND LANGUAGE = USERENV('LANG')) ORGANIZATION_DESC
293 , PEOPLE_GROUP_ID
294 , SOFT_CODING_KEYFLEX_ID
295 , VACANCY_ID
296 , (SELECT NAME FROM PER_VACANCIES PV
297 WHERE PV.VACANCY_ID = PAAF.VACANCY_ID) VACANCY_DESC
298 , PAY_BASIS_ID
299 , (SELECT NAME FROM PER_PAY_BASES PPB
300 WHERE PPB.PAY_BASIS_ID = PAAF.PAY_BASIS_ID) PAY_BASIS_DESC
301 , ASSIGNMENT_SEQUENCE
302 , ASSIGNMENT_TYPE
303 , PAAF.PRIMARY_FLAG
304 , APPLICATION_ID
305 , ASSIGNMENT_NUMBER
306 , CHANGE_REASON
307 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE =
308 DECODE(PAAF.ASSIGNMENT_TYPE,'E','EMP_ASSIGN_REASON', 'C', 'CWK_ASSIGN_REASON','')
309 AND LOOKUP_CODE = PAAF.CHANGE_REASON AND p_eff_date between nvl(start_date_active,p_eff_date)
310 and nvl(end_date_active,p_eff_Date)
311 and enabled_flag = 'Y') CHANGE_REASON_DESC
312 , COMMENT_ID
313 , to_char(DATE_PROBATION_END,'YYYY-MM-DD')
314 , DEFAULT_CODE_COMB_ID
315 , EMPLOYMENT_CATEGORY
316 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'EMP_CAT'
317 AND LOOKUP_CODE = EMPLOYMENT_CATEGORY
318 AND p_eff_date between nvl(start_date_active,p_eff_date) and nvl(end_date_active,p_eff_Date)
319 and enabled_flag = 'Y')
320 EMPLOYMENT_CATEGORY_DESC -- Too many values
321 , FREQUENCY
322 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
323 AND LOOKUP_CODE = PAAF.FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
324 and nvl(end_date_active,p_eff_Date)
325 and enabled_flag = 'Y')
326 FREQUENCY_DESC -- Too many values
327 , INTERNAL_ADDRESS_LINE
328 , MANAGER_FLAG
329 , NORMAL_HOURS
330 , PERF_REVIEW_PERIOD
331 , PERF_REVIEW_PERIOD_FREQUENCY
332 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
333 AND LOOKUP_CODE = PAAF.PERF_REVIEW_PERIOD_FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
334 and nvl(end_date_active,p_eff_Date)
335 and enabled_flag = 'Y')
336 PERF_RP_FREQUENCY_DESC -- Too many values
337 , PERIOD_OF_SERVICE_ID
338 , PROBATION_PERIOD
339 , PROBATION_UNIT
340 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'QUALIFYING_UNITS'
344 , SAL_REVIEW_PERIOD
341 AND LOOKUP_CODE = PAAF.PROBATION_UNIT AND p_eff_date between nvl(start_date_active,p_eff_date)
342 and nvl(end_date_active,p_eff_Date)
343 and enabled_flag = 'Y') PROBATION_UNIT_DESC
345 , SAL_REVIEW_PERIOD_FREQUENCY
346 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
347 AND LOOKUP_CODE = PAAF.SAL_REVIEW_PERIOD_FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
348 and nvl(end_date_active,p_eff_Date)
349 and enabled_flag = 'Y')
350 SAL_RP_FREQUENCY_DESC -- Too many values
351 , SET_OF_BOOKS_ID
352 , SOURCE_TYPE
353 , TIME_NORMAL_FINISH
354 , TIME_NORMAL_START
355 , BARGAINING_UNIT_CODE
356 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'BARGAINING_UNIT_CODE'
357 AND LOOKUP_CODE = PAAF.BARGAINING_UNIT_CODE AND p_eff_date between nvl(start_date_active,p_eff_date)
358 and nvl(end_date_active,p_eff_Date)
359 and enabled_flag = 'Y')
360 BARGAINING_UNIT_DESC
361 , LABOUR_UNION_MEMBER_FLAG
362 , HOURLY_SALARIED_CODE
363 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'HOURLY_SALARIED_CODE'
364 AND LOOKUP_CODE = PAAF.HOURLY_SALARIED_CODE AND p_eff_date between nvl(start_date_active,p_eff_date)
365 and nvl(end_date_active,p_eff_Date)
366 and enabled_flag = 'Y')
367 HOURLY_SALARIED_DESC
368 , CONTRACT_ID
369 , COLLECTIVE_AGREEMENT_ID
370 , (SELECT NAME FROM PER_COLLECTIVE_AGREEMENTS PCA WHERE PCA.COLLECTIVE_AGREEMENT_ID = PAAF.COLLECTIVE_AGREEMENT_ID) COLLECTIVE_AGREEMENT_DESC
371 , CAGR_ID_FLEX_NUM
372 , CAGR_GRADE_DEF_ID
373 , ESTABLISHMENT_ID
374 , (SELECT NAME FROM HR_ESTABLISHMENTS_V HEV WHERE HEV.ESTABLISHMENT_ID = PAAF.ESTABLISHMENT_ID) ESTABLISHMENT_DESC
375 , ASS_ATTRIBUTE_CATEGORY
376 , ASS_ATTRIBUTE1
377 , ASS_ATTRIBUTE2
378 , ASS_ATTRIBUTE3
379 , ASS_ATTRIBUTE4
380 , ASS_ATTRIBUTE5
381 , ASS_ATTRIBUTE6
382 , ASS_ATTRIBUTE7
383 , ASS_ATTRIBUTE8
384 , ASS_ATTRIBUTE9
385 , ASS_ATTRIBUTE10
386 , ASS_ATTRIBUTE11
387 , ASS_ATTRIBUTE12
388 , ASS_ATTRIBUTE13
389 , ASS_ATTRIBUTE14
390 , ASS_ATTRIBUTE15
391 , ASS_ATTRIBUTE16
392 , ASS_ATTRIBUTE17
393 , ASS_ATTRIBUTE18
394 , ASS_ATTRIBUTE19
395 , ASS_ATTRIBUTE20
396 , ASS_ATTRIBUTE21
397 , ASS_ATTRIBUTE22
398 , ASS_ATTRIBUTE23
399 , ASS_ATTRIBUTE24
400 , ASS_ATTRIBUTE25
401 , ASS_ATTRIBUTE26
402 , ASS_ATTRIBUTE27
403 , ASS_ATTRIBUTE28
404 , ASS_ATTRIBUTE29
405 , ASS_ATTRIBUTE30
406 , TITLE
407 , NOTICE_PERIOD
408 , NOTICE_PERIOD_UOM
409 , EMPLOYEE_CATEGORY
410 , (select meaning from HR_LOOKUPS where lookup_type = 'EMPLOYEE_CATG'
411 AND LOOKUP_CODE = PAAF.EMPLOYEE_CATEGORY AND p_eff_date between nvl(start_date_active,p_eff_date)
412 and nvl(end_date_active,p_eff_Date)
413 and enabled_flag = 'Y') EMPLOYEE_CATEGORY_DESC
414 , WORK_AT_HOME
415 , JOB_POST_SOURCE_NAME
416 , '' POSTING_CONTENT_ID -- column not exist in PER_ASSIGNMENTS_F2
417 , to_char(PERIOD_OF_PLACEMENT_DATE_START,'YYYY-MM-DD')
418 , VENDOR_ID
419 , VENDOR_EMPLOYEE_NUMBER
420 , VENDOR_ASSIGNMENT_NUMBER
421 , ASSIGNMENT_CATEGORY
422 , PROJECT_TITLE
423 , '' APPLICANT_RANK -- column not exist in PER_ASSIGNMENTS_F2
424 , VENDOR_SITE_ID
425 , PO_HEADER_ID
426 , PO_LINE_ID
427 , PROJECTED_ASSIGNMENT_END
428 , GRADE_LADDER_PGM_ID
429 , '' GRADE_LADDER_PGM_NAME
430 , SUPERVISOR_ASSIGNMENT_ID
431 FROM PER_ASSIGNMENTS_F2 paaf,
432 PER_ASSIGNMENT_STATUS_TYPES ST ,
433 PER_ASSIGNMENT_STATUS_TYPES_TL STTL
434 WHERE paaf.person_id = p_person_id
435 AND paaf.business_group_id = p_business_group_id
436 AND paaf.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
437 AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
438 AND STTL.LANGUAGE = USERENV('LANG')
439 and p_eff_date between EFFECTIVE_START_DATE AND nvl(EFFECTIVE_END_DATE,to_date('31/12/4712','DD/MM/YYYY'));
440
441 --Cursor to fetch the assignment location address
442 CURSOR csr_asg_loc_addr(p_location_id number) is
443 SELECT LOCATION_ID,
444 STYLE,
445 ( SELECT descriptive_flex_context_name
446 from fnd_descr_flex_contexts_vl
447 where descriptive_flexfield_name ='Address Structure'
448 AND descriptive_flex_context_code = STYLE) STYLE_DESC,
449 COUNTRY,
450 (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC,
451 ADDRESS_LINE_1,
452 ADDRESS_LINE_2,
453 ADDRESS_LINE_3,
454 TOWN_OR_CITY,
455 REGION_1,
456 REGION_2,
457 REGION_3,
458 POSTAL_CODE,
459 TELEPHONE_NUMBER_1,
460 TELEPHONE_NUMBER_2,
461 TELEPHONE_NUMBER_3,
462 LOC_INFORMATION13,
463 LOC_INFORMATION14,
464 LOC_INFORMATION15,
465 LOC_INFORMATION16,
466 LOC_INFORMATION17,
467 LOC_INFORMATION18,
468 LOC_INFORMATION19,
469 LOC_INFORMATION20
470 FROM HR_LOCATIONS_ALL
471 WHERE LOCATION_ID = P_LOCATION_ID;
472
473
474 -- cursor to fetch the contact information
475 CURSOR csr_contact_record(p_person_id number,p_business_group_id number,p_eff_date date) is
476 SELECT CONTACT_RELATIONSHIP_ID
477 , CONTACT_PERSON_ID
478 , (SELECT FULL_NAME FROM PER_PEOPLE_F
479 WHERE PERSON_ID = CONTACT_PERSON_ID
483 , (SELECT MEANING FROM hr_leg_lookups WHERE LOOKUP_TYPE='CONTACT'
480 AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
481 AND P_EFF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) CONTACT_FULL_NAME
482 , CONTACT_TYPE
484 AND LOOKUP_CODE= CONTACT_TYPE ) CONTACT_TYPE_DESC
485 , PRIMARY_CONTACT_FLAG
486 , to_char(DATE_START,'YYYY-MM-DD')
487 , START_LIFE_REASON_ID
488 , to_char(DATE_END,'YYYY-MM-DD')
489 , END_LIFE_REASON_ID
490 , RLTD_PER_RSDS_W_DSGNTR_FLAG
491 , PERSONAL_FLAG
492 , SEQUENCE_NUMBER
493 , CONT_ATTRIBUTE_CATEGORY
494 , CONT_ATTRIBUTE1
495 , CONT_ATTRIBUTE2
496 , CONT_ATTRIBUTE3
497 , CONT_ATTRIBUTE4
498 , CONT_ATTRIBUTE5
499 , CONT_ATTRIBUTE6
500 , CONT_ATTRIBUTE7
501 , CONT_ATTRIBUTE8
502 , CONT_ATTRIBUTE9
503 , CONT_ATTRIBUTE10
504 , CONT_ATTRIBUTE11
505 , CONT_ATTRIBUTE12
506 , CONT_ATTRIBUTE13
507 , CONT_ATTRIBUTE14
508 , CONT_ATTRIBUTE15
509 , CONT_ATTRIBUTE16
510 , CONT_ATTRIBUTE17
511 , CONT_ATTRIBUTE18
512 , CONT_ATTRIBUTE19
513 , CONT_ATTRIBUTE20
514 , THIRD_PARTY_PAY_FLAG
515 , BONDHOLDER_FLAG
516 , DEPENDENT_FLAG
517 , BENEFICIARY_FLAG
518 , PARTY_ID
519 , CONT_INFORMATION_CATEGORY
520 , CONT_INFORMATION1
521 , CONT_INFORMATION2
522 , CONT_INFORMATION3
523 , CONT_INFORMATION4
524 , CONT_INFORMATION5
525 , CONT_INFORMATION6
526 , CONT_INFORMATION7
527 , CONT_INFORMATION8
528 , CONT_INFORMATION9
529 , CONT_INFORMATION10
530 , CONT_INFORMATION11
531 , CONT_INFORMATION12
532 , CONT_INFORMATION13
533 , CONT_INFORMATION14
534 , CONT_INFORMATION15
535 , CONT_INFORMATION16
536 , CONT_INFORMATION17
537 , CONT_INFORMATION18
538 , CONT_INFORMATION19
539 , CONT_INFORMATION20
540 FROM PER_CONTACT_RELATIONSHIPS PCR
541 where PCR.PERSON_ID = p_person_id
542 and PCR.business_group_id = p_business_group_id
543 and p_eff_date between DATE_START and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY') );
544
545
546 --Cursor to fetch the contact address
547
548 CURSOR csr_cont_address(p_person_id number,p_business_group_id number,p_eff_date date) is
549 SELECT CONTACT_RELATIONSHIP_ID
550 , CONTACT_PERSON_ID
551 , ADDRESS_ID
552 , to_char(DATE_FROM,'YYYY-MM-DD')
553 , PRIMARY_FLAG
554 , STYLE
555 , ( SELECT descriptive_flex_context_name
556 from fnd_descr_flex_contexts_vl
557 where descriptive_flexfield_name ='Address Structure'
558 AND descriptive_flex_context_code = STYLE) STYLE_DESC
559 , ADDRESS_LINE1
560 , ADDRESS_LINE2
561 , ADDRESS_LINE3
562 , ADDRESS_TYPE
563 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'ADDRESS_TYPE' AND LOOKUP_CODE = ADDRESS_TYPE
564 AND p_eff_date between nvl(start_date_active,p_eff_date)
565 and nvl(end_date_active,p_eff_Date)
566 and enabled_flag = 'Y') ADDRESS_TYPE_DESC
567 , COUNTRY
568 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC
569 , to_char(DATE_TO,'YYYY-MM-DD')
570 , POSTAL_CODE
571 , REGION_1
572 , 'REGION_1' REGION_1_DESC
573 , REGION_2
574 , 'REGION_2' REGION_2_DESC
575 , REGION_3
576 , 'REGION_3' REGION_3_DESC
577 , TELEPHONE_NUMBER_1
578 , TELEPHONE_NUMBER_2
579 , TELEPHONE_NUMBER_3
580 , TOWN_OR_CITY
581 , 'TOWN_OR_CITY' TOWN_OR_CITY_DESC
582 , ADDR_ATTRIBUTE_CATEGORY
583 , ADDR_ATTRIBUTE1
584 , ADDR_ATTRIBUTE2
585 , ADDR_ATTRIBUTE3
586 , ADDR_ATTRIBUTE4
587 , ADDR_ATTRIBUTE5
588 , ADDR_ATTRIBUTE6
589 , ADDR_ATTRIBUTE7
590 , ADDR_ATTRIBUTE8
591 , ADDR_ATTRIBUTE9
592 , ADDR_ATTRIBUTE10
593 , ADDR_ATTRIBUTE11
594 , ADDR_ATTRIBUTE12
595 , ADDR_ATTRIBUTE13
596 , ADDR_ATTRIBUTE14
597 , ADDR_ATTRIBUTE15
598 , ADDR_ATTRIBUTE16
599 , ADDR_ATTRIBUTE17
600 , ADDR_ATTRIBUTE18
601 , ADDR_ATTRIBUTE19
602 , ADDR_ATTRIBUTE20
603 , ADD_INFORMATION13
604 , ADD_INFORMATION14
605 , ADD_INFORMATION15
606 , ADD_INFORMATION16
607 , ADD_INFORMATION17
608 , ADD_INFORMATION18
609 , ADD_INFORMATION19
610 , ADD_INFORMATION20
611 , PA.PARTY_ID
612 , DERIVED_LOCALE
613 , GEOMETRY
614 , PA.COMMENTS
615 FROM PER_ADDRESSES PA, PER_CONTACT_RELATIONSHIPS PCR
616 WHERE PCR.PERSON_ID = p_person_id
617 AND PCR.CONTACT_PERSON_ID = PA.PERSON_ID
618 and p_eff_date between PCR.DATE_START and nvl(PCR.date_end,to_date('31/12/4712','DD/MM/YYYY'))
619 and p_eff_date between pa.date_from and nvl(pa.date_to,to_date('31/12/4712','DD/MM/YYYY'));
620
621
622 -- cursor to fetch the address record
623 CURSOR csr_address_record(p_person_id number,p_business_group_id number,p_eff_date date) is SELECT ADDRESS_ID
624 , to_char(DATE_FROM,'YYYY-MM-DD')
625 , PRIMARY_FLAG
626 , STYLE
627 , ( SELECT descriptive_flex_context_name
628 from fnd_descr_flex_contexts_vl
629 where descriptive_flexfield_name ='Address Structure'
630 AND descriptive_flex_context_code = STYLE) STYLE_DESC
631 , ADDRESS_LINE1
632 , ADDRESS_LINE2
633 , ADDRESS_LINE3
634 , ADDRESS_TYPE
635 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'ADDRESS_TYPE' AND LOOKUP_CODE = ADDRESS_TYPE
639 , COUNTRY
636 AND p_eff_date between nvl(start_date_active,p_eff_date)
637 and nvl(end_date_active,p_eff_Date)
638 and enabled_flag = 'Y') ADDRESS_TYPE_DESC
640 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC
641 , to_char(DATE_TO,'YYYY-MM-DD')
642 , POSTAL_CODE
643 , REGION_1
644 , 'REGION_1' REGION_1_DESC
645 , REGION_2
646 , 'REGION_2' REGION_2_DESC
647 , REGION_3
648 , 'REGION_3' REGION_3_DESC
649 , TELEPHONE_NUMBER_1
650 , TELEPHONE_NUMBER_2
651 , TELEPHONE_NUMBER_3
652 , TOWN_OR_CITY
653 , 'TOWN_OR_CITY' TOWN_OR_CITY_DESC
654 , ADDR_ATTRIBUTE_CATEGORY
655 , ADDR_ATTRIBUTE1
656 , ADDR_ATTRIBUTE2
657 , ADDR_ATTRIBUTE3
658 , ADDR_ATTRIBUTE4
659 , ADDR_ATTRIBUTE5
660 , ADDR_ATTRIBUTE6
661 , ADDR_ATTRIBUTE7
662 , ADDR_ATTRIBUTE8
663 , ADDR_ATTRIBUTE9
664 , ADDR_ATTRIBUTE10
665 , ADDR_ATTRIBUTE11
666 , ADDR_ATTRIBUTE12
667 , ADDR_ATTRIBUTE13
668 , ADDR_ATTRIBUTE14
669 , ADDR_ATTRIBUTE15
670 , ADDR_ATTRIBUTE16
671 , ADDR_ATTRIBUTE17
672 , ADDR_ATTRIBUTE18
673 , ADDR_ATTRIBUTE19
674 , ADDR_ATTRIBUTE20
675 , ADD_INFORMATION13
676 , ADD_INFORMATION14
677 , ADD_INFORMATION15
678 , ADD_INFORMATION16
679 , ADD_INFORMATION17
680 , ADD_INFORMATION18
681 , ADD_INFORMATION19
682 , ADD_INFORMATION20
683 , PARTY_ID
684 , DERIVED_LOCALE
685 , GEOMETRY
686 , COMMENTS
687 FROM PER_ADDRESSES PA
688 WHERE PA.PERSON_ID = p_person_id
689 and p_eff_date between pa.date_from and nvl(pa.date_to,to_date('31/12/4712','DD/MM/YYYY'));
690
691 -- cursor to fetch the person extra information record
692 CURSOR csr_person_extra_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is SELECT PEI.PERSON_EXTRA_INFO_ID
693 , PEI.INFORMATION_TYPE
694 , PEI.PEI_ATTRIBUTE_CATEGORY
695 , PEI.PEI_ATTRIBUTE1
696 , PEI.PEI_ATTRIBUTE2
697 , PEI.PEI_ATTRIBUTE3
698 , PEI.PEI_ATTRIBUTE4
699 , PEI.PEI_ATTRIBUTE5
700 , PEI.PEI_ATTRIBUTE6
701 , PEI.PEI_ATTRIBUTE7
702 , PEI.PEI_ATTRIBUTE8
703 , PEI.PEI_ATTRIBUTE9
704 , PEI.PEI_ATTRIBUTE10
705 , PEI.PEI_ATTRIBUTE11
706 , PEI.PEI_ATTRIBUTE12
707 , PEI.PEI_ATTRIBUTE13
708 , PEI.PEI_ATTRIBUTE14
709 , PEI.PEI_ATTRIBUTE15
710 , PEI.PEI_ATTRIBUTE16
711 , PEI.PEI_ATTRIBUTE17
712 , PEI.PEI_ATTRIBUTE18
713 , PEI.PEI_ATTRIBUTE19
714 , PEI.PEI_ATTRIBUTE20
715 , PEI.PEI_INFORMATION_CATEGORY
716 , PEI.PEI_INFORMATION1
717 , PEI.PEI_INFORMATION2
718 , PEI.PEI_INFORMATION3
719 , PEI.PEI_INFORMATION4
720 , PEI.PEI_INFORMATION5
721 , PEI.PEI_INFORMATION6
722 , PEI.PEI_INFORMATION7
723 , PEI.PEI_INFORMATION8
724 , PEI.PEI_INFORMATION9
725 , PEI.PEI_INFORMATION10
726 , PEI.PEI_INFORMATION11
727 , PEI.PEI_INFORMATION12
728 , PEI.PEI_INFORMATION13
729 , PEI.PEI_INFORMATION14
730 , PEI.PEI_INFORMATION15
731 , PEI.PEI_INFORMATION16
732 , PEI.PEI_INFORMATION17
733 , PEI.PEI_INFORMATION18
734 , PEI.PEI_INFORMATION19
735 , PEI.PEI_INFORMATION20
736 , PEI.PEI_INFORMATION21
737 , PEI.PEI_INFORMATION22
738 , PEI.PEI_INFORMATION23
739 , PEI.PEI_INFORMATION24
740 , PEI.PEI_INFORMATION25
741 , PEI.PEI_INFORMATION26
742 , PEI.PEI_INFORMATION27
743 , PEI.PEI_INFORMATION28
744 , PEI.PEI_INFORMATION29
745 , PEI.PEI_INFORMATION30
746 , PEI.PARTY_ID
747 FROM PER_PEOPLE_EXTRA_INFO PEI
748 where PEI.person_id = p_person_id;
749
750 -- cursor to fetch the special information record
751 CURSOR csr_special_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
752 SELECT PERSON_ANALYSIS_ID
753 , ppa.ANALYSIS_CRITERIA_ID
754 , to_char(DATE_FROM,'YYYY-MM-DD')
755 , to_char(DATE_TO,'YYYY-MM-DD')
756 , ppa.ID_FLEX_NUM
757 , SEGMENT1
758 , SEGMENT2
759 , SEGMENT3
760 , SEGMENT4
761 , SEGMENT5
762 , SEGMENT6
763 , SEGMENT7
764 , SEGMENT8
765 , SEGMENT9
766 , SEGMENT10
767 , SEGMENT11
768 , SEGMENT12
769 , SEGMENT13
770 , SEGMENT14
771 , SEGMENT15
772 , SEGMENT16
773 , SEGMENT17
774 , SEGMENT18
775 , SEGMENT19
776 , SEGMENT20
777 , SEGMENT21
778 , SEGMENT22
779 , SEGMENT23
780 , SEGMENT24
781 , SEGMENT25
782 , SEGMENT26
783 , SEGMENT27
784 , SEGMENT28
785 , SEGMENT29
786 , SEGMENT30
787 , ATTRIBUTE_CATEGORY
788 , ATTRIBUTE1
789 , ATTRIBUTE2
790 , ATTRIBUTE3
791 , ATTRIBUTE4
792 , ATTRIBUTE5
793 , ATTRIBUTE6
794 , ATTRIBUTE7
795 , ATTRIBUTE8
796 , ATTRIBUTE9
797 , ATTRIBUTE10
798 , ATTRIBUTE11
799 , ATTRIBUTE12
800 , ATTRIBUTE13
801 , ATTRIBUTE14
802 , ATTRIBUTE15
803 , ATTRIBUTE16
804 , ATTRIBUTE17
805 , ATTRIBUTE18
806 , ATTRIBUTE19
807 , ATTRIBUTE20
808 , PARTY_ID
809 , COMMENTS
810 FROM PER_PERSON_ANALYSES ppa,PER_ANALYSIS_CRITERIA pac
811 where ppa.person_id = p_person_id
815 and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
812 and ppa.business_group_id = p_business_group_id
813 and ppa.analysis_criteria_id = pac.analysis_criteria_id
814 and ppa.id_flex_num = pac.id_flex_num
816
817 -- cursor to fetch the previous employment
818 CURSOR csr_prev_emp_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
819 SELECT PREVIOUS_EMPLOYER_ID
820 , PARTY_ID
821 , to_char(START_DATE,'YYYY-MM-DD')
822 , to_char(END_DATE,'YYYY-MM-DD')
823 , PERIOD_YEARS
824 , PERIOD_DAYS
825 , EMPLOYER_NAME
826 , EMPLOYER_COUNTRY
827 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = EMPLOYER_COUNTRY) EMPLOYER_COUNTRY_DESC
828 , EMPLOYER_ADDRESS
829 , EMPLOYER_TYPE
830 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'PREV_EMP_TYPE' AND LOOKUP_CODE = EMPLOYER_TYPE
831 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
832 AND ENABLED_FLAG = 'Y') EMPLOYER_TYPE_DESC
833 , EMPLOYER_SUBTYPE
834 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'PREV_EMP_SUBTYPE' AND LOOKUP_CODE = EMPLOYER_SUBTYPE
835 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
836 AND ENABLED_FLAG = 'Y') EMPLOYER_SUBTYPE_DESC
837 , DESCRIPTION
838 , PEM_ATTRIBUTE_CATEGORY
839 , PEM_ATTRIBUTE1
840 , PEM_ATTRIBUTE2
841 , PEM_ATTRIBUTE3
842 , PEM_ATTRIBUTE4
843 , PEM_ATTRIBUTE5
844 , PEM_ATTRIBUTE6
845 , PEM_ATTRIBUTE7
846 , PEM_ATTRIBUTE8
847 , PEM_ATTRIBUTE9
848 , PEM_ATTRIBUTE10
849 , PEM_ATTRIBUTE11
850 , PEM_ATTRIBUTE12
851 , PEM_ATTRIBUTE13
852 , PEM_ATTRIBUTE14
853 , PEM_ATTRIBUTE15
854 , PEM_ATTRIBUTE16
855 , PEM_ATTRIBUTE17
856 , PEM_ATTRIBUTE18
857 , PEM_ATTRIBUTE19
858 , PEM_ATTRIBUTE20
859 , PEM_ATTRIBUTE21
860 , PEM_ATTRIBUTE22
861 , PEM_ATTRIBUTE23
862 , PEM_ATTRIBUTE24
863 , PEM_ATTRIBUTE25
864 , PEM_ATTRIBUTE26
865 , PEM_ATTRIBUTE27
866 , PEM_ATTRIBUTE28
867 , PEM_ATTRIBUTE29
868 , PEM_ATTRIBUTE30
869 , PEM_INFORMATION_CATEGORY
870 , PEM_INFORMATION1
871 , PEM_INFORMATION2
872 , PEM_INFORMATION3
873 , PEM_INFORMATION4
874 , PEM_INFORMATION5
875 , PEM_INFORMATION6
876 , PEM_INFORMATION7
877 , PEM_INFORMATION8
878 , PEM_INFORMATION9
879 , PEM_INFORMATION10
880 , PEM_INFORMATION11
881 , PEM_INFORMATION12
882 , PEM_INFORMATION13
883 , PEM_INFORMATION14
884 , PEM_INFORMATION15
885 , PEM_INFORMATION16
886 , PEM_INFORMATION17
887 , PEM_INFORMATION18
888 , PEM_INFORMATION19
889 , PEM_INFORMATION20
890 , PEM_INFORMATION21
891 , PEM_INFORMATION22
892 , PEM_INFORMATION23
893 , PEM_INFORMATION24
894 , PEM_INFORMATION25
895 , PEM_INFORMATION26
896 , PEM_INFORMATION27
897 , PEM_INFORMATION28
898 , PEM_INFORMATION29
899 , PEM_INFORMATION30
900 , ALL_ASSIGNMENTS
901 , PERIOD_MONTHS
902 FROM PER_PREVIOUS_EMPLOYERS
903 WHERE PERSON_ID = p_person_id
904 and business_group_id = p_business_group_id
905 and p_eff_date between start_date and nvl(end_date,to_date('31/12/4712','DD/MM/YYYY'));
906
907 -- cursor to fetch the work incidents details
908 CURSOR CSR_WORK_INC_INFO_RECORD(p_person_id number,p_business_group_id number,p_eff_date date) IS
909 SELECT INCIDENT_ID
910 , INCIDENT_REFERENCE
911 , INCIDENT_TYPE
912 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'INCIDENT_TYPE' AND LOOKUP_CODE = INCIDENT_TYPE
913 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
914 AND ENABLED_FLAG = 'Y') INCIDENT_TYPE_DESC
915 , to_char(INCIDENT_DATE,'YYYY-MM-DD')
916 , INCIDENT_TIME
917 , ASSIGNMENT_ID
918 , LOCATION
919 , AT_WORK_FLAG
920 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'AT_WORK_FLAG' AND LOOKUP_CODE = AT_WORK_FLAG
921 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
922 AND ENABLED_FLAG = 'Y') AT_WORK_FLAG_DESC
923 , to_char(LAST_WORK_DATE,'YYYY-MM-DD')
924 , LAST_WORK_TIME
925 , to_char(REPORT_DATE,'YYYY-MM-DD')
926 , REPORT_TIME
927 , REPORT_METHOD
928 , PERSON_REPORTED_BY
929 , (SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PERSON_REPORTED_BY
930 and p_eff_date between papf.effective_start_date and
931 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) PERSON_REPORTED_BY_NAME
932 , PERSON_REPORTED_TO
933 , WITNESS_DETAILS
934 , DESCRIPTION
935 , INJURY_TYPE
936 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'INJURY_TYPE' AND LOOKUP_CODE = INJURY_TYPE
937 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
938 AND ENABLED_FLAG = 'Y') INJURY_TYPE_DESC
939 , DISEASE_TYPE
940 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'DISEASE_TYPE' AND LOOKUP_CODE = DISEASE_TYPE
941 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
942 AND ENABLED_FLAG = 'Y') DISEASE_TYPE_DESC
943 , HAZARD_TYPE
944 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'HAZARD_TYPE' AND LOOKUP_CODE = HAZARD_TYPE
945 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
946 AND ENABLED_FLAG = 'Y') HAZARD_TYPE_DESC
950 , DOCTOR_ID
947 , BODY_PART
948 , TREATMENT_RECEIVED_FLAG
949 , HOSPITAL_DETAILS
951 , NEXT_OF_KIN_ID
952 , ABSENCE_ID
953 , to_char(COMPENSATION_DATE,'YYYY-MM-DD')
954 , COMPENSATION_CURRENCY
955 , COMPENSATION_AMOUNT
956 , REMEDIAL_HS_ACTION
957 , NOTIFIED_HSREP_ID
958 , to_char(NOTIFIED_HSREP_DATE,'YYYY-MM-DD')
959 , NOTIFIED_UREP_ID
960 , to_char(NOTIFIED_UREP_DATE,'YYYY-MM-DD')
961 , PREVIOUS_INCIDENT_ID
962 , ATTRIBUTE_CATEGORY
963 , ATTRIBUTE1
964 , ATTRIBUTE2
965 , ATTRIBUTE3
966 , ATTRIBUTE4
967 , ATTRIBUTE5
968 , ATTRIBUTE6
969 , ATTRIBUTE7
970 , ATTRIBUTE8
971 , ATTRIBUTE9
972 , ATTRIBUTE10
973 , ATTRIBUTE11
974 , ATTRIBUTE12
975 , ATTRIBUTE13
976 , ATTRIBUTE14
977 , ATTRIBUTE15
978 , ATTRIBUTE16
979 , ATTRIBUTE17
980 , ATTRIBUTE18
981 , ATTRIBUTE19
982 , ATTRIBUTE20
983 , ATTRIBUTE21
984 , ATTRIBUTE22
985 , ATTRIBUTE23
986 , ATTRIBUTE24
987 , ATTRIBUTE25
988 , ATTRIBUTE26
989 , ATTRIBUTE27
990 , ATTRIBUTE28
991 , ATTRIBUTE29
992 , ATTRIBUTE30
993 , INC_INFORMATION_CATEGORY
994 , INC_INFORMATION1
995 , INC_INFORMATION2
996 , INC_INFORMATION3
997 , INC_INFORMATION4
998 , INC_INFORMATION5
999 , INC_INFORMATION6
1000 , INC_INFORMATION7
1001 , INC_INFORMATION8
1002 , INC_INFORMATION9
1003 , INC_INFORMATION10
1004 , INC_INFORMATION11
1005 , INC_INFORMATION12
1006 , INC_INFORMATION13
1007 , INC_INFORMATION14
1008 , INC_INFORMATION15
1009 , INC_INFORMATION16
1010 , INC_INFORMATION17
1011 , INC_INFORMATION18
1012 , INC_INFORMATION19
1013 , INC_INFORMATION20
1014 , INC_INFORMATION21
1015 , INC_INFORMATION22
1016 , INC_INFORMATION23
1017 , INC_INFORMATION24
1018 , INC_INFORMATION25
1019 , INC_INFORMATION26
1020 , INC_INFORMATION27
1021 , INC_INFORMATION28
1022 , INC_INFORMATION29
1023 , INC_INFORMATION30
1024 , to_char(ORG_NOTIFIED_DATE,'YYYY-MM-DD')
1025 , DOCTOR_NAME
1026 , NOTIFIED_REP_ID
1027 , to_char(NOTIFIED_REP_DATE,'YYYY-MM-DD')
1028 , NOTIFIED_REP_ORG_ID
1029 , RELATED_INCIDENT_ID
1030 , OVER_TIME_FLAG
1031 , ABSENCE_EXISTS_FLAG
1032 , EMERGENCY_CODE
1033 , PRIVACY_ISSUE
1034 , OBJECTS_INVOLVED
1035 , ACTIVITY_AT_TIME_OF_WORK
1036 , HOSPITAL_ADDRESS
1037 , DAYS_RESTRICTED_WORK
1038 , HOSPITALIZED_FLAG
1039 , to_char(DATE_OF_DEATH,'YYYY-MM-DD')
1040 , DAYS_AWAY_FROM_WORK
1041 , WORK_START_TIME
1042 , REPORTING_PERSON_PHONE
1043 , REPORTING_PERSON_TITLE
1044 , REPORT_COMPLETED_BY
1045 FROM PER_WORK_INCIDENTS
1046 WHERE PERSON_ID = p_person_id;
1047
1048
1049 -- cursor to fetch the deilvery methode
1050 CURSOR csr_deliv_method_info(p_person_id number,p_business_group_id number,p_eff_date date) is
1051 SELECT DELIVERY_METHOD_ID
1052 , to_char(DATE_START,'YYYY-MM-DD')
1053 , to_char(DATE_END,'YYYY-MM-DD')
1054 , COMM_DLVRY_METHOD
1055 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_CM_MTHD' AND LOOKUP_CODE = COMM_DLVRY_METHOD
1056 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1057 AND ENABLED_FLAG = 'Y') COMM_DLVRY_METHOD_DESC
1058 , PREFERRED_FLAG
1059 , ATTRIBUTE_CATEGORY
1060 , ATTRIBUTE1
1061 , ATTRIBUTE2
1062 , ATTRIBUTE3
1063 , ATTRIBUTE4
1064 , ATTRIBUTE5
1065 , ATTRIBUTE6
1066 , ATTRIBUTE7
1067 , ATTRIBUTE8
1068 , ATTRIBUTE9
1069 , ATTRIBUTE10
1070 , ATTRIBUTE11
1071 , ATTRIBUTE12
1072 , ATTRIBUTE13
1073 , ATTRIBUTE14
1074 , ATTRIBUTE15
1075 , ATTRIBUTE16
1076 , ATTRIBUTE17
1077 , ATTRIBUTE18
1078 , ATTRIBUTE19
1079 , ATTRIBUTE20
1080 FROM PER_PERSON_DLVRY_METHODS
1081 where person_id = p_person_id;
1082
1083 -- cursor to fetch the assignment extra information
1084 CURSOR csr_ass_extra_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
1085 SELECT ASSIGNMENT_EXTRA_INFO_ID
1086 , ASSIGNMENT_ID
1087 , INFORMATION_TYPE
1088 , AEI_ATTRIBUTE_CATEGORY
1089 , AEI_ATTRIBUTE1
1090 , AEI_ATTRIBUTE2
1091 , AEI_ATTRIBUTE3
1092 , AEI_ATTRIBUTE4
1093 , AEI_ATTRIBUTE5
1094 , AEI_ATTRIBUTE6
1095 , AEI_ATTRIBUTE7
1096 , AEI_ATTRIBUTE8
1097 , AEI_ATTRIBUTE9
1098 , AEI_ATTRIBUTE10
1099 , AEI_ATTRIBUTE11
1100 , AEI_ATTRIBUTE12
1101 , AEI_ATTRIBUTE13
1102 , AEI_ATTRIBUTE14
1103 , AEI_ATTRIBUTE15
1104 , AEI_ATTRIBUTE16
1105 , AEI_ATTRIBUTE17
1106 , AEI_ATTRIBUTE18
1107 , AEI_ATTRIBUTE19
1108 , AEI_ATTRIBUTE20
1109 , AEI_INFORMATION_CATEGORY
1110 , AEI_INFORMATION1
1111 , AEI_INFORMATION2
1112 , AEI_INFORMATION3
1113 , AEI_INFORMATION4
1114 , AEI_INFORMATION5
1115 , AEI_INFORMATION6
1116 , AEI_INFORMATION7
1117 , AEI_INFORMATION8
1118 , AEI_INFORMATION9
1119 , AEI_INFORMATION10
1120 , AEI_INFORMATION11
1121 , AEI_INFORMATION12
1122 , AEI_INFORMATION13
1123 , AEI_INFORMATION14
1124 , AEI_INFORMATION15
1125 , AEI_INFORMATION16
1129 , AEI_INFORMATION20
1126 , AEI_INFORMATION17
1127 , AEI_INFORMATION18
1128 , AEI_INFORMATION19
1130 , AEI_INFORMATION21
1131 , AEI_INFORMATION22
1132 , AEI_INFORMATION23
1133 , AEI_INFORMATION24
1134 , AEI_INFORMATION25
1135 , AEI_INFORMATION26
1136 , AEI_INFORMATION27
1137 , AEI_INFORMATION28
1138 , AEI_INFORMATION29
1139 , AEI_INFORMATION30
1140 FROM PER_ASSIGNMENT_EXTRA_INFO
1141 WHERE assignment_id in (SELECT assignment_id from PER_ASSIGNMENTS_F
1142 WHERE person_id = p_person_id
1143 and business_group_id = p_business_group_id
1144 and p_eff_date between effective_start_date and nvl(effective_end_date,to_date('31/12/4712','Dd/MM/YYYY')));
1145
1146 -- cursor to fetch the contact extra information
1147 CURSOR csr_con_extra_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
1148 SELECT CONTACT_EXTRA_INFO_ID
1149 , to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD')
1150 , to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD')
1151 , CONTACT_RELATIONSHIP_ID
1152 , INFORMATION_TYPE
1153 , CEI_INFORMATION_CATEGORY
1154 , CEI_INFORMATION1
1155 , CEI_INFORMATION2
1156 , CEI_INFORMATION3
1157 , CEI_INFORMATION4
1158 , CEI_INFORMATION5
1159 , CEI_INFORMATION6
1160 , CEI_INFORMATION7
1161 , CEI_INFORMATION8
1162 , CEI_INFORMATION9
1163 , CEI_INFORMATION10
1164 , CEI_INFORMATION11
1165 , CEI_INFORMATION12
1166 , CEI_INFORMATION13
1167 , CEI_INFORMATION14
1168 , CEI_INFORMATION15
1169 , CEI_INFORMATION16
1170 , CEI_INFORMATION17
1171 , CEI_INFORMATION18
1172 , CEI_INFORMATION19
1173 , CEI_INFORMATION20
1174 , CEI_INFORMATION21
1175 , CEI_INFORMATION22
1176 , CEI_INFORMATION23
1177 , CEI_INFORMATION24
1178 , CEI_INFORMATION25
1179 , CEI_INFORMATION26
1180 , CEI_INFORMATION27
1181 , CEI_INFORMATION28
1182 , CEI_INFORMATION29
1183 , CEI_INFORMATION30
1184 , CEI_ATTRIBUTE_CATEGORY
1185 , CEI_ATTRIBUTE1
1186 , CEI_ATTRIBUTE2
1187 , CEI_ATTRIBUTE3
1188 , CEI_ATTRIBUTE4
1189 , CEI_ATTRIBUTE5
1190 , CEI_ATTRIBUTE6
1191 , CEI_ATTRIBUTE7
1192 , CEI_ATTRIBUTE8
1193 , CEI_ATTRIBUTE9
1194 , CEI_ATTRIBUTE10
1195 , CEI_ATTRIBUTE11
1196 , CEI_ATTRIBUTE12
1197 , CEI_ATTRIBUTE13
1198 , CEI_ATTRIBUTE14
1199 , CEI_ATTRIBUTE15
1200 , CEI_ATTRIBUTE16
1201 , CEI_ATTRIBUTE17
1202 , CEI_ATTRIBUTE18
1203 , CEI_ATTRIBUTE19
1204 , CEI_ATTRIBUTE20
1205 FROM PER_CONTACT_EXTRA_INFO_F
1206 WHERE CONTACT_RELATIONSHIP_ID in (SELECT PCR.CONTACT_RELATIONSHIP_ID
1207 FROM PER_CONTACT_RELATIONSHIPS PCR
1208 where PCR.PERSON_ID = p_person_id
1209 and PCR.business_group_id = p_business_group_id
1210 and p_eff_date between DATE_START and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY') ));
1211
1212 --Cursor to fetch the phone details
1213 CURSOR csr_phone_record (p_person_id number,p_business_group_id number,p_eff_date date) is
1214 SELECT
1215 PHONE_ID,
1216 to_char(DATE_FROM,'YYYY-MM-DD'),
1217 to_char(DATE_TO,'YYYY-MM-DD'),
1218 PHONE_TYPE,
1219 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PHONE_TYPE' AND LOOKUP_CODE = PHONE_TYPE
1220 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1221 AND ENABLED_FLAG = 'Y') COMM_DLVRY_METHOD_DESC,
1222 PHONE_NUMBER,
1223 ATTRIBUTE_CATEGORY,
1224 ATTRIBUTE1,
1225 ATTRIBUTE2,
1226 ATTRIBUTE3,
1227 ATTRIBUTE4,
1228 ATTRIBUTE5,
1229 ATTRIBUTE6,
1230 ATTRIBUTE7,
1231 ATTRIBUTE8,
1232 ATTRIBUTE9,
1233 ATTRIBUTE10,
1234 ATTRIBUTE11,
1235 ATTRIBUTE12,
1236 ATTRIBUTE13,
1237 ATTRIBUTE14,
1238 ATTRIBUTE15,
1239 ATTRIBUTE16,
1240 ATTRIBUTE17,
1241 ATTRIBUTE18,
1242 ATTRIBUTE19,
1243 ATTRIBUTE20,
1244 ATTRIBUTE21,
1245 ATTRIBUTE22,
1246 ATTRIBUTE23,
1247 ATTRIBUTE24,
1248 ATTRIBUTE25,
1249 ATTRIBUTE26,
1250 ATTRIBUTE27,
1251 ATTRIBUTE28,
1252 ATTRIBUTE29,
1253 ATTRIBUTE30,
1254 PARTY_ID,
1255 VALIDITY
1256 FROM PER_PHONES
1257 WHERE PARENT_ID = p_person_id
1258 AND PARENT_TABLE = 'PER_ALL_PEOPLE_F'
1259 and p_eff_date between DATE_FROM and nvl(DATE_TO,to_date('31/12/4712','DD/MM/YYYY'));
1260
1261 --cursor to fetch the qualification details
1262 CURSOR csr_qualification_record(p_person_id number,p_business_group_id number,p_eff_date date)
1263 is
1264 select QUALIFICATION_ID,
1265 TITLE,
1266 GRADE_ATTAINED,
1267 STATUS,
1268 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES' AND LOOKUP_CODE = STATUS
1269 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1270 AND ENABLED_FLAG = 'Y') STATUS_DESC,
1271 TO_CHAR(AWARDED_DATE,'YYYY-MM-DD'),
1272 FEE,
1273 FEE_CURRENCY,
1274 (SELECT name from fnd_currencies_vl WHERE
1275 CURRENCY_CODE = FEE_CURRENCY
1276 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1277 AND ENABLED_FLAG = 'Y') FEE_CURRENCY_DESC,
1278 TRAINING_COMPLETED_AMOUNT,
1279 REIMBURSEMENT_ARRANGEMENTS,
1280 TRAINING_COMPLETED_UNITS,
1281 TOTAL_TRAINING_AMOUNT,
1282 TO_CHAR(START_DATE,'YYYY-MM-DD'),
1283 TO_CHAR(END_DATE,'YYYY-MM-DD'),
1284 LICENSE_NUMBER,
1285 TO_CHAR(EXPIRY_DATE,'YYYY-MM-DD'),
1286 LICENSE_RESTRICTIONS,
1287 TO_CHAR(PROJECTED_COMPLETION_DATE,'YYYY-MM-DD'),
1288 AWARDING_BODY,
1289 TUITION_METHOD,
1290 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_TUITION_METHODS' AND LOOKUP_CODE = TUITION_METHOD
1294 COMMENTS,
1291 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1292 AND ENABLED_FLAG = 'Y') TUITION_METHOD_DESC,
1293 GROUP_RANKING,
1295 pq.QUALIFICATION_TYPE_ID,
1296 TL.name,
1297 pq.ATTENDANCE_ID,
1298 pq.ATTRIBUTE_CATEGORY,
1299 pq.ATTRIBUTE1,
1300 pq.ATTRIBUTE2,
1301 pq.ATTRIBUTE3,
1302 pq.ATTRIBUTE4,
1303 pq.ATTRIBUTE5,
1304 pq.ATTRIBUTE6,
1305 pq.ATTRIBUTE7,
1306 pq.ATTRIBUTE8,
1307 pq.ATTRIBUTE9,
1308 pq.ATTRIBUTE10,
1309 pq.ATTRIBUTE11,
1310 pq.ATTRIBUTE12,
1311 pq.ATTRIBUTE13,
1312 pq.ATTRIBUTE14,
1313 pq.ATTRIBUTE15,
1314 pq.ATTRIBUTE16,
1315 pq.ATTRIBUTE17,
1316 pq.ATTRIBUTE18,
1317 pq.ATTRIBUTE19,
1318 pq.ATTRIBUTE20,
1319 pq.PARTY_ID,
1320 PROFESSIONAL_BODY_NAME,
1321 MEMBERSHIP_NUMBER,
1322 MEMBERSHIP_CATEGORY,
1323 SUBSCRIPTION_PAYMENT_METHOD,
1324 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PQP_SUBSCRIPTION_PAY_METHODS' AND LOOKUP_CODE = SUBSCRIPTION_PAYMENT_METHOD
1325 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1326 AND ENABLED_FLAG = 'Y')SUBSCRIPTION_PAYMENT_DESC,
1327 QUA_INFORMATION_CATEGORY,
1328 QUA_INFORMATION1,
1329 QUA_INFORMATION2,
1330 QUA_INFORMATION3,
1331 QUA_INFORMATION4,
1332 QUA_INFORMATION5,
1333 QUA_INFORMATION6,
1334 QUA_INFORMATION7,
1335 QUA_INFORMATION8,
1336 QUA_INFORMATION9,
1337 QUA_INFORMATION10,
1338 QUA_INFORMATION11,
1339 QUA_INFORMATION12,
1340 QUA_INFORMATION13,
1341 QUA_INFORMATION14,
1342 QUA_INFORMATION15,
1343 QUA_INFORMATION16,
1344 QUA_INFORMATION17,
1345 QUA_INFORMATION18,
1346 QUA_INFORMATION19,
1347 QUA_INFORMATION20
1348 FROM
1349 PER_QUALIFICATIONS pq,per_qualification_types_tl tl,PER_ESTABLISHMENT_ATTENDANCES pea
1350 WHERE (pq.person_id = p_person_id or pea.person_id = p_person_id)
1351 and pq.business_group_id = p_business_group_id
1352 and pq.attendance_id = pea.attendance_id(+)
1353 and tl.qualification_type_id = pq.qualification_type_id
1354 and tl.language = userenv('LANG')
1355 and nvl(start_date,p_eff_date) <= p_eff_date;
1356 -- and p_eff_date between nvl(start_date,p_eff_date) and nvl(end_date,p_eff_date); commented for 7460407
1357
1358 --cursor to fetch the subject details for the qualification
1359 cursor csr_subject_record (p_qualification_id number,p_eff_date date)is
1360 SELECT SUBJECTS_TAKEN_ID,
1361 to_char(START_DATE,'YYYY-MM-DD'),
1362 MAJOR,
1363 SUBJECT_STATUS,
1364 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES' AND LOOKUP_CODE = SUBJECT_STATUS
1365 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1366 AND ENABLED_FLAG = 'Y')SUBJECT_STATUS_DESC,
1367 SUBJECT,
1368 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECTS' AND LOOKUP_CODE = SUBJECT
1369 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1370 AND ENABLED_FLAG = 'Y')
1371 SUBJECT_DESC,
1372 GRADE_ATTAINED,
1373 to_char(END_DATE,'YYYY-MM-DD'),
1374 ATTRIBUTE_CATEGORY,
1375 ATTRIBUTE1,
1376 ATTRIBUTE2,
1377 ATTRIBUTE3,
1378 ATTRIBUTE4,
1379 ATTRIBUTE5,
1380 ATTRIBUTE6,
1381 ATTRIBUTE7,
1382 ATTRIBUTE8,
1383 ATTRIBUTE9,
1384 ATTRIBUTE10,
1385 ATTRIBUTE11,
1386 ATTRIBUTE12,
1387 ATTRIBUTE13,
1388 ATTRIBUTE14,
1389 ATTRIBUTE15,
1390 ATTRIBUTE16,
1391 ATTRIBUTE17,
1392 ATTRIBUTE18,
1393 ATTRIBUTE19,
1394 ATTRIBUTE20,
1395 SUB_INFORMATION_CATEGORY,
1396 SUB_INFORMATION1,
1397 SUB_INFORMATION2,
1398 SUB_INFORMATION3,
1399 SUB_INFORMATION4,
1400 SUB_INFORMATION5,
1401 SUB_INFORMATION6,
1402 SUB_INFORMATION7,
1403 SUB_INFORMATION8,
1404 SUB_INFORMATION9,
1405 SUB_INFORMATION10,
1406 SUB_INFORMATION11,
1407 SUB_INFORMATION12,
1408 SUB_INFORMATION13,
1409 SUB_INFORMATION14,
1410 SUB_INFORMATION15,
1411 SUB_INFORMATION16,
1412 SUB_INFORMATION17,
1413 SUB_INFORMATION18,
1414 SUB_INFORMATION19,
1415 SUB_INFORMATION20
1416 FROM PER_SUBJECTS_TAKEN
1417 WHERE qualification_id = p_qualification_id
1418 and nvl(start_date,p_eff_date) <= p_eff_date;
1419 --and p_eff_date between nvl(start_date,p_eff_date) and nvl(end_date,p_eff_date); commented for 7460407
1420
1421 --cursor to fetch the reportees details for the person
1422 cursor csr_reportee_record (p_person_id number,p_business_group_id number,p_eff_date date)
1423 is
1424 SELECT distinct ppf.full_name,
1425 ppf.person_id,
1426 employee_number ,
1427 ppf.npw_number,
1428 ppf.applicant_number,
1429 papf.location_id,
1430 (SELECT DESCRIPTION FROM HR_LOCATIONS_ALL_TL HLAT WHERE HLAT.LOCATION_ID = PAPF.LOCATION_ID
1431 AND LANGUAGE = USERENV('LANG')) REPORTEE_LOCATION_DESC,
1432 papf.assignment_status_type_id,
1433 (SELECT USER_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES_TL
1434 WHERE ASSIGNMENT_STATUS_TYPE_ID = papf.assignment_status_type_id
1435 AND LANGUAGE = USERENV('LANG')) REPORTEE_ASG_STATUS_DESC
1436 FROM per_assignments_f papf , per_people_f ppf
1437 WHERE
1438 p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
1439 and p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1440 and papf.business_group_id = P_BUSINESS_GROUP_ID
1441 and papf.supervisor_id = P_PERSON_ID
1442 and papf.person_id=ppf.person_id;
1443
1444
1445 --Cursor to fetch Absence Details
1446 CURSOR csr_abs_details (p_person_id number,p_business_group_id number,p_eff_date date) is
1447 SELECT ACCRUAL_PLAN_ID,
1448 ACCRUAL_PLAN_NAME,
1449 ACCRUAL_UNITS_OF_MEASURE_NAME,
1450 ASSIGNMENT_ID,
1451 PAYROLL_ID
1452 FROM PAY_VIEW_ACCRUAL_PLANS_V
1453 WHERE PERSON_ID = p_person_id
1457 l_start_date date := null;
1454 AND BUSINESS_GROUP_ID = p_business_group_id
1455 AND P_EFF_dATE BETWEEN ASG_EFFECTIVE_START_DATE AND ASG_EFFECTIVE_END_DATE;
1456
1458 l_end_date date := null;
1459 l_accrual_end_date date;
1460 l_entitlement number := 0;
1461 l_accrual number := 0;
1462
1463 -- Benefit Cursors
1464 -- Code Commented for bug 7689952 - Start
1465 /*cursor c_get_id(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
1466 select distinct pen.per_in_ler_id,pil.lf_evt_ocrd_dt
1467 from ben_prtt_enrt_rslt_f pen,
1468 ben_per_in_ler pil,
1469 ben_ler_f le
1470 where pen.business_group_id=p_business_group_id
1471 and p_effective_date BETWEEN pen.effective_start_date AND pen.effective_end_date
1472 and p_effective_date BETWEEN le.effective_start_date AND le.effective_end_date
1473 and pen.prtt_enrt_rslt_stat_cd IS NULL
1474 and pil.ler_id=le.ler_id
1475 and pen.ENRT_CVG_THRU_DT > pen.EFFECTIVE_START_DATE
1476 and le.typ_cd not in ('IREC','GSP','COMP','ABS','SCHEDDU','SCHEDDA')
1477 and pen.person_id=p_person_id
1478 order by pil.lf_evt_ocrd_dt desc; */
1479 -- Code Commented for bug 7689952 - End
1480
1481 cursor c_get_dep_details(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
1482
1483 select dependent_full_name, relation, plan_type_name, 'COVERED'
1484 from
1485 (
1486 select pln.name Plan_Name,
1487 opt.name Option_Name,
1488 plt.name plan_type_name,
1489 (select name from ben_pgm_f pgm
1490 where p_effective_date between pgm.effective_start_date
1491 and pgm.effective_end_date
1492 and pgm.pgm_id=epe.pgm_id
1493 and pgm.business_group_id = p_business_group_id) Program_Name,
1494 ppf.first_name||' '||ppf.last_name || ' ' || ppf.suffix Dependent,
1495 ppf.national_identifier Ssn,
1496 (select HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type)
1497 from per_contact_relationships pcr
1498 where pcr.personal_flag = 'Y'
1499 and pcr.person_id = pen.person_id
1500 and pcr.contact_person_id = pdp.dpnt_person_id
1501 and p_effective_date between nvl(pcr.date_start, p_effective_date )
1502 and nvl(pcr.date_end, p_effective_date )
1503 and decode(pcr.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8) <=
1504 (select decode(pcr2.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8)
1505 from per_contact_relationships pcr2
1506 where pcr2.person_id = pcr.person_id
1507 and pcr2.contact_person_id = pcr.contact_person_id
1508 and p_effective_date between nvl(pcr2.date_start, p_effective_date )
1509 and nvl(pcr2.date_end, p_effective_date )
1510 and pcr2.personal_flag = 'Y'
1511 )
1512 and rownum = 1
1513 ) Relation,
1514 epe.per_in_ler_id,
1515 epe.pgm_id,
1516 ler.name le_name,
1517 ppf.full_name dependent_full_name,
1518 pdp.cvg_strt_dt,
1519 to_date(null) cvg_thru_dt
1520 from ben_elig_per_elctbl_chc epe,
1521 ben_prtt_enrt_rslt_f pen,
1522 ben_elig_cvrd_dpnt_f pdp,
1523 per_contact_relationships pcr,
1524 per_people_f ppf,
1525 ben_pl_typ_f plt,
1526 ben_pl_f pln,
1527 ben_opt_f opt,
1528 ben_oipl_f oipl,
1529 ben_per_in_ler pil,
1530 ben_ler_f ler
1531 where epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1532 and epe.pl_id = pln.pl_id
1533 and epe.pl_typ_id = plt.pl_typ_id
1534 and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
1535 and pen.prtt_enrt_rslt_stat_cd is null
1536 and pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
1537 and pdp.dpnt_person_id = pcr.contact_person_id
1538 and pcr.contact_person_id = ppf.person_id
1539 and pcr.personal_flag = 'Y'
1540 and epe.prtt_enrt_rslt_id is not null
1541 and epe.per_in_ler_id = pil.per_in_ler_id
1542 and pil.ler_id = ler.ler_id
1543 and pcr.person_id = pil.person_id
1544 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
1545 -- Code Changes for bug 7689952 - Start
1546 --and pil.per_in_ler_id = l_per_in_ler_id
1547 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
1548 and pil.per_in_ler_id = pen.per_in_ler_id
1549 and pen.enrt_cvg_thru_dt >= p_effective_date
1550 -- Code Changes for bug 7689952 - End
1551 --and epe.pgm_id = :8
1552 and pil.person_id = p_person_id
1553 and p_effective_date between ler.effective_start_date
1554 and ler.effective_end_date
1555 and p_effective_date between pdp.effective_start_date
1556 and pdp.effective_end_date
1557 and p_effective_date between pln.effective_start_date
1558 and pln.effective_end_date
1559 and p_effective_date between plt.effective_start_date
1560 and plt.effective_end_date
1561 and p_effective_date between nvl(pcr.date_start, p_effective_date )
1562 and nvl(pcr.date_end, p_effective_date )
1563 and p_effective_date between ppf.effective_start_date
1564 and ppf.effective_end_date
1565 and p_effective_date between pen.effective_start_date
1566 and pen.effective_end_date
1567 and pdp.cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
1568 and pdp.per_in_ler_id = pil.per_in_ler_id
1569 and pen.oipl_id = oipl.oipl_id(+)
1570 and oipl.opt_id = opt.opt_id(+)
1571 and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
1572 and p_effective_date between
1573 oipl.effective_start_date (+) and
1574 oipl.effective_end_date (+)
1578 and pcr.business_group_id = p_business_group_id
1575 and p_effective_date between
1576 opt.effective_start_date (+) and
1577 opt.effective_end_date (+)
1579 and ppf.business_group_id = p_business_group_id
1580 and plt.business_group_id = p_business_group_id
1581 and pln.business_group_id = p_business_group_id
1582 and opt.business_group_id = p_business_group_id
1583 and oipl.business_group_id = p_business_group_id
1584 and pil.business_group_id = p_business_group_id
1585 and epe.business_group_id = p_business_group_id
1586 and pen.business_group_id = p_business_group_id
1587 and pdp.business_group_id = p_business_group_id
1588 and ler.business_group_id = p_business_group_id
1589 );
1590
1591 cursor c_get_ben_details(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
1592 select Plan_Type_Name,Plan_Name, Option_Name, Beneficiary, Ssn, Relation, Primary_Bnf, Contingent_Bnf,
1593 le_name,beneficiary_full_name,Primary_Bnf_Amt,Contingent_Bnf_Amt
1594 from (select plt.name Plan_Type_Name,pln.name Plan_Name, opt.name Option_Name,
1595 ppf.last_name last_name,
1596 ppf.first_name first_name,
1597 decode(pbn.organization_id, null, ppf.first_name||
1598 ' '||ppf.last_name || ' ' || ppf.suffix,
1599 org.name) Beneficiary,
1600 nvl(ppf.full_name, org.name) beneficiary_full_name,
1601 ppf.national_identifier Ssn,
1602 nvl(HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type),
1603 decode(pbn.organization_id, null,
1604 HR_GENERAL.DECODE_LOOKUP('BEN_EXT_RLSHP','SLF'),
1605 HR_GENERAL.DECODE_LOOKUP('BEN_EXT_RLSHP','TP'))) Relation, sum(decode(pbn.prmry_cntngnt_cd,'PRIMY',pbn.pct_dsgd_num,0)) Primary_Bnf, sum(decode(pbn.prmry_cntngnt_cd,'CNTNGNT',pbn.pct_dsgd_num,0)) Contingent_Bnf,
1606 sum(decode(pbn.prmry_cntngnt_cd,'PRIMY',pbn.amt_dsgd_val,0)) Primary_Bnf_Amt, sum(decode(pbn.prmry_cntngnt_cd,'CNTNGNT',pbn.amt_dsgd_val,0)) Contingent_Bnf_Amt,
1607 pcr.contact_type contact_type,
1608 ler.name le_name,
1609 ppf.date_of_birth date_of_birth,
1610 pen.ptip_ordr_num ptip_ordr_num,
1611 pen.plip_ordr_num plip_ordr_num,
1612 pen.pl_ordr_num pl_ordr_num,
1613 pen.oipl_ordr_num oipl_ordr_num,
1614 pen.bnft_ordr_num bnft_ordr_num
1615 from per_people_f ppf,
1616 per_contact_relationships pcr,
1617 ben_prtt_enrt_rslt_f pen,
1618 ben_pl_bnf_f pbn,
1619 ben_pl_typ_f plt,
1620 ben_pl_f pln,
1621 hr_all_organization_units org,
1622 ben_opt_f opt,
1623 ben_oipl_f oipl,
1624 ben_per_in_ler pil,
1625 ben_ler_f ler
1626 where pen.pl_id = pln.pl_id
1627 -- Code Changes for bug 7689952 - Start
1628 --and (pen.per_in_ler_id = l_per_in_ler_id)
1629 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
1630 and pen.enrt_cvg_thru_dt >= p_effective_date
1631 -- Code Changes for bug 7689952 - End
1632 and pen.oipl_id = oipl.oipl_id(+)
1633 and oipl.opt_id = opt.opt_id(+)
1634 and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
1635 and pln.invk_dcln_prtn_pl_flag = 'N'
1636 and pen.person_id = p_person_id
1637 and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
1638 and pbn.per_in_ler_id = pil.per_in_ler_id
1639 and pil.ler_id = ler.ler_id
1640 and exists (select null from ben_per_in_ler pil
1641 where pil.per_in_ler_id = pbn.per_in_ler_id
1642 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
1643 and pen.prtt_enrt_rslt_stat_cd IS NULL
1644 and pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
1645 and pen.pl_typ_id = plt.pl_typ_id
1646 and pcr.personal_flag(+) = 'Y'
1647 and pcr.person_id(+) = p_person_id
1648 and pbn.bnf_person_id = pcr.contact_person_id(+)
1649 and pbn.bnf_person_id = ppf.person_id(+)
1650 and pbn.organization_id = org.organization_id(+)
1651 and p_effective_date between ler.effective_start_date
1652 and ler.effective_end_date
1653 and p_effective_date between plt.effective_start_date
1654 and plt.effective_end_date
1655 and p_effective_date between pbn.effective_start_date
1656 and pbn.effective_end_date
1657 and p_effective_date between
1658 nvl(ppf.effective_start_date, p_effective_date ) and
1659 nvl(ppf.effective_end_date, p_effective_date )
1660 and p_effective_date between pen.effective_start_date
1661 and pen.effective_end_date
1662 and p_effective_date between pln.effective_start_date
1663 and pln.effective_end_date
1664 and p_effective_date between
1665 nvl(org.date_from, p_effective_date ) and
1666 nvl(org.date_to, p_effective_date )
1667 and p_effective_date between
1668 oipl.effective_start_date (+) and
1669 oipl.effective_end_date (+)
1670 and p_effective_date between
1671 opt.effective_start_date (+) and
1672 opt.effective_end_date (+)
1673 and ppf.business_group_id = p_business_group_id
1674 and pcr.business_group_id = p_business_group_id
1675 and pen.business_group_id = p_business_group_id
1676 and pbn.business_group_id = p_business_group_id
1677 and plt.business_group_id = p_business_group_id
1678 and pln.business_group_id = p_business_group_id
1679 and opt.business_group_id = p_business_group_id
1680 and oipl.business_group_id = p_business_group_id
1681 and org.business_group_id(+) = p_business_group_id
1682 and ler.business_group_id = p_business_group_id
1683 and (pcr.contact_relationship_id is null or
1684 (pcr.contact_relationship_id is not null and
1685 (p_effective_date between
1686 nvl(pcr.date_start, p_effective_date ) and
1687 nvl(pcr.date_end, p_effective_date )) or
1688 ((pcr.date_start = (select max(pcr2.date_start)
1692 and pcr2.personal_flag = 'Y')) and
1689 from per_contact_relationships pcr2
1690 where pcr2.contact_person_id = pcr.contact_person_id
1691 and pcr2.person_id = pcr.person_id
1693 not exists (select null
1694 from PER_CONTACT_RELATIONSHIPS pcr3
1695 where pcr3.contact_person_id = pcr.contact_person_id
1696 and pcr3.person_id = pcr.person_id
1697 and pcr3.personal_flag = 'Y'
1698 and p_effective_date between
1699 nvl(pcr3.date_start, p_effective_date )
1700 and nvl(pcr3.date_end, p_effective_date )))
1701 ))
1702 group by plt.name,
1703 pln.name,
1704 opt.name,
1705 ppf.first_name,
1706 ppf.last_name,
1707 ppf.suffix,
1708 ppf.full_name,
1709 pbn.organization_id,
1710 org.name,
1711 ppf.national_identifier,
1712 pcr.contact_type,
1713 ler.name,
1714 ppf.date_of_birth,
1715 pln.bnf_cntngt_bnfs_alwd_flag,
1716 pen.ptip_ordr_num,
1717 pen.plip_ordr_num,
1718 pen.pl_ordr_num,
1719 pen.oipl_ordr_num,
1720 pen.bnft_ordr_num
1721 )order by ptip_ordr_num,
1722 plip_ordr_num,
1723 pl_ordr_num,
1724 oipl_ordr_num,
1725 bnft_ordr_num,
1726 Plan_Name,
1727 decode(contact_type,'S',1,'D',1,'A',2,
1728 'C',2,'O',2,'T',2,'P',3,4),
1729 date_of_birth,
1730 last_name,
1731 first_name,
1732 Beneficiary;
1733
1734 cursor c_get_pgm_enrt_details(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
1735 SELECT plan_type_name,
1736 plan_name,
1737 option_name
1738 FROM
1739 (
1740 SELECT elc.plt_name plan_type_name,
1741 pln.name plan_name,
1742 opt.name option_name,
1743 elc.Program_Name,
1744 elc.Coverage,
1745 elc.le_name,
1746 elc.per_in_ler_id,
1747 elc.pgm_id,
1748 elc.sspndd_flag,
1749 elc.crntly_enrd_flag,
1750 elc.elctbl_flag,
1751 elc.enrt_cvg_strt_dt,
1752 elc.enrt_cvg_thru_dt,
1753 decode(elc.sspndd_flag,'Y','Suspended') suspended,
1754 decode(elc.interim, 'Y', 'Interim') interim
1755 FROM
1756 (
1757 SELECT epe.ptip_ordr_num ptip_ordr_num,
1758 epe.plip_ordr_num plip_ordr_num,
1759 pen.pl_ordr_num pl_ordr_num,
1760 epe.oipl_ordr_num oipl_ordr_num,
1761 pen.bnft_ordr_num bnft_ordr_num,
1762 plt.name plt_name,
1763 pen.person_id person_id,
1764 pen.bnft_amt Coverage,
1765 epe.per_in_ler_id per_in_ler_id,
1766 pen.sspndd_flag,
1767 epe.crntly_enrd_flag,
1768 epe.elctbl_flag,
1769 pen.enrt_cvg_strt_dt ,
1770 decode(pen.enrt_cvg_thru_dt,to_date('31-12-4712','DD-MM-YYYY'),to_date(null),pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt,
1771 decode(pen1.prtt_enrt_rslt_id , null, 'N', 'Y') interim,
1772 ler.name le_name,
1773 (select name from ben_pgm_f pgm
1774 where p_effective_date between pgm.effective_start_date
1775 and pgm.effective_end_date
1776 and pgm.pgm_id=epe.pgm_id
1777 and pgm.business_group_id = p_business_group_id) Program_Name,
1778 epe.pgm_id pgm_id,
1779 epe.ptip_id ptip_id,
1780 epe.plip_id plip_id,
1781 epe.oiplip_id oiplip_id,
1782 epe.pl_id,
1783 epe.oipl_id
1784 FROM ben_elig_per_elctbl_chc epe,
1785 ben_enrt_bnft beb,
1786 ben_prtt_enrt_rslt_f pen,
1787 ben_pl_typ_f plt,
1788 ben_pil_elctbl_chc_popl pel,
1789 ben_prtt_enrt_rslt_f pen1,
1790 ben_ler_f ler
1791 WHERE pen.prtt_enrt_rslt_id = pen1.rplcs_sspndd_rslt_id (+)
1792 and ( p_effective_date) between pen1.effective_start_date (+)
1793 and pen1.effective_end_date (+)
1794 and pen1.prtt_enrt_rslt_stat_cd (+) is NULL
1795 and pen1.enrt_cvg_thru_dt (+) =to_date('31-12-4712','DD-MM-YYYY')
1796 -- Code Changes for bug 7689952 - Start
1797 --and epe.per_in_ler_id = l_per_in_ler_id
1798 and pen.per_in_ler_id = epe.per_in_ler_id
1799 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
1800 and pen.enrt_cvg_thru_dt >= p_effective_date
1801 --Code Changes for bug 7689952 - End
1802 and pen.person_id = p_person_id
1803 and epe.business_group_id = p_business_group_id
1804 and plt.business_group_id = p_business_group_id
1805 and ler.business_group_id = p_business_group_id
1806 AND ((epe.elctbl_flag = 'N' and
1807 (nvl(beb.crntly_enrld_flag, epe.crntly_enrd_flag) = 'Y'
1808 or epe.auto_enrt_flag = 'Y'))
1809 or epe.elctbl_flag = 'Y')
1810 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1811 AND epe.pl_typ_id = plt.pl_typ_id
1812 AND decode(beb.enrt_bnft_id, null, epe.prtt_enrt_rslt_id, beb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
1813 AND epe.business_group_id = beb.business_group_id(+)
1814 AND epe.elig_per_elctbl_chc_id = beb.elig_per_elctbl_chc_id (+)
1815 AND ( p_effective_date) BETWEEN pen.effective_start_date
1816 AND pen.effective_end_date
1817 AND ( p_effective_date) BETWEEN plt.effective_start_date
1818 AND plt.effective_end_date
1819 AND ( p_effective_date) BETWEEN ler.effective_start_date
1820 AND ler.effective_end_date
1821 AND pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
1822 and pen.ler_id=ler.ler_id
1826 SELECT epe.ptip_ordr_num ptip_ordr_num,
1823 AND epe.comp_lvl_cd NOT IN ('PLANFC' ,'PLANIMP')
1824 AND pen.prtt_enrt_rslt_stat_cd is null
1825 UNION
1827 epe.plip_ordr_num plip_ordr_num,
1828 pen.pl_ordr_num pl_ordr_num,
1829 epe.oipl_ordr_num oipl_ordr_num,
1830 pen.bnft_ordr_num bnft_ordr_num,
1831 plt.name plt_name,
1832 pen.person_id person_id,
1833 pen.bnft_amt Coverage,
1834 epe.per_in_ler_id per_in_ler_id,
1835 pen.sspndd_flag,
1836 epe.crntly_enrd_flag,
1837 epe.elctbl_flag,
1838 pen.enrt_cvg_strt_dt ,
1839 decode(pen.enrt_cvg_thru_dt,
1840 to_date('31-12-4712','DD-MM-YYYY'),to_date(null),pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt,
1841 decode(pen1.prtt_enrt_rslt_id , null, 'N', 'Y') interim,
1842 ler.name le_name,
1843 (select name from ben_pgm_f pgm
1844 where p_effective_date between pgm.effective_start_date
1845 and pgm.effective_end_date
1846 and pgm.pgm_id=epe.pgm_id
1847 and pgm.business_group_id = p_business_group_id) Program_Name,
1848 epe.pgm_id pgm_id,
1849 epe.ptip_id ptip_id,
1850 epe.plip_id plip_id,
1851 epe.oiplip_id oiplip_id,
1852 epe.pl_id,
1853 epe.oipl_id
1854 FROM ben_elig_per_elctbl_chc epe,
1855 ben_enrt_bnft beb,
1856 ben_prtt_enrt_rslt_f pen,
1857 ben_pl_typ_f plt,
1858 ben_pil_elctbl_chc_popl pel,
1859 ben_prtt_enrt_rslt_f pen1,
1860 ben_ler_f ler
1861 WHERE pen.prtt_enrt_rslt_id = pen1.rplcs_sspndd_rslt_id (+)
1862 and (p_effective_date) between pen1.effective_start_date (+)
1863 and pen1.effective_end_date (+)
1864 and pen1.prtt_enrt_rslt_stat_cd (+) is NULL
1865 and pen1.enrt_cvg_thru_dt (+) =to_date('31-12-4712','DD-MM-YYYY')
1866 -- Code Changes for bug 7689952 - Start
1867 -- and epe.per_in_ler_id = l_per_in_ler_id
1868 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
1869 and pen.per_in_ler_id=epe.per_in_ler_id
1870 and pen.enrt_cvg_thru_dt >= p_effective_date
1871 -- Code Changes for bug 7689952 - End
1872 and pen.person_id = p_person_id
1873 and epe.business_group_id = p_business_group_id
1874 and plt.business_group_id = p_business_group_id
1875 and ler.business_group_id = p_business_group_id
1876 AND ((epe.elctbl_flag = 'N' and
1877 (nvl(beb.crntly_enrld_flag, epe.crntly_enrd_flag) = 'Y'
1878 or epe.auto_enrt_flag = 'Y'))
1879 or epe.elctbl_flag = 'Y')
1880 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1881 AND epe.pl_typ_id = plt.pl_typ_id
1882 AND decode(beb.enrt_bnft_id, null, epe.prtt_enrt_rslt_id, beb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
1883 AND epe.business_group_id = beb.business_group_id(+)
1884 AND epe.elig_per_elctbl_chc_id = beb.elig_per_elctbl_chc_id (+)
1885 AND (p_effective_date) BETWEEN pen.effective_start_date
1886 AND pen.effective_end_date
1887 AND (p_effective_date) BETWEEN plt.effective_start_date
1888 AND plt.effective_end_date
1889 AND ( p_effective_date) BETWEEN ler.effective_start_date
1890 AND ler.effective_end_date
1891 AND pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
1892 and pen.ler_id=ler.ler_id
1893 AND epe.comp_lvl_cd NOT IN ('PLANFC' ,'PLANIMP')
1894 AND pen.prtt_enrt_rslt_stat_cd is null
1895 ) elc,
1896 ben_pl_f pln,
1897 ben_oipl_f oipl,
1898 ben_opt_f opt,
1899 ben_ptip_f ptip,
1900 ben_plip_f plip
1901 where elc.pl_id = pln.pl_id
1902 AND elc.ptip_id = ptip.ptip_id
1903 AND elc.plip_id = plip.plip_id
1904 AND elc.oipl_id = oipl.oipl_id(+)
1905 AND oipl.opt_id = opt.opt_id(+)
1906 and pln.business_group_id = p_business_group_id
1907 and oipl.business_group_id = p_business_group_id
1908 and opt.business_group_id = p_business_group_id
1909 and ptip.business_group_id = p_business_group_id
1910 and plip.business_group_id = p_business_group_id
1911 AND ( p_effective_date) BETWEEN pln.effective_start_date
1912 AND pln.effective_end_date
1913 AND ( p_effective_date) BETWEEN oipl.effective_start_date(+)
1914 AND oipl.effective_end_date(+)
1915 AND ( p_effective_date) BETWEEN opt.effective_start_date(+)
1916 AND opt.effective_end_date(+)
1917 AND ( p_effective_date) BETWEEN ptip.effective_start_date
1918 AND ptip.effective_end_date
1919 AND ( p_effective_date) BETWEEN plip.effective_start_date
1920 AND plip.effective_end_date
1921 ORDER BY elc.ptip_ordr_num,
1922 elc.plip_ordr_num,
1923 elc.pl_ordr_num,
1924 elc.oipl_ordr_num,
1925 elc.bnft_ordr_num
1926 );
1927
1928
1929 --Declaration for Payroll - Starts here
1930 p_leg_code varchar2(20);
1931 p_assignment_id per_all_assignments_f.assignment_id%type;
1935 cursor csr_leg_code(p_bus_grp_id number) is
1932 p_assg_action_id pay_assignment_actions.assignment_action_id%type;
1933 p_pyrl_action_id pay_payroll_actions.payroll_action_id%type;
1934
1936 select to_char(org_information9) from
1937 hr_organization_information where organization_id = p_bus_grp_id
1938 and org_information_context = 'Business Group Information';
1939
1940
1941 --Declaration for US Payroll - starts
1942
1943
1944 p_location_id number;
1945
1946 p_state_code varchar2(100);
1947 p_state_desc varchar2(100);
1948
1949 cursor csr_uspay_req (p_person_id number,p_eff_date date) is
1950 select distinct
1951 ppa.payroll_action_id
1952 ,paa.assignment_id
1953 ,paa.assignment_action_id
1954 , paf.location_id
1955 from pay_payroll_actions ppa
1956 ,pay_assignment_actions paa
1957 ,per_assignments_f paf
1958 ,per_people_f ppf
1959 ,hr_lookups hl
1960 where ppa.action_type = 'X'
1961 and ppa.action_status = 'C'
1962 and ppa.report_type = hl.meaning
1963 and hl.lookup_type = 'PAYSLIP_REPORT_TYPES'
1964 and hl.lookup_code = 'US'
1965 and ppa.payroll_action_id = paa.payroll_action_id
1966 and paa.assignment_id = paf.assignment_id
1967 and paf.person_id = ppf.person_id
1968 and ppf.person_id = p_person_id
1969 and ppa.effective_date = (select max(ppa1.effective_date)
1970 from pay_payroll_actions ppa1
1971 ,pay_assignment_actions paa1
1972 ,hr_lookups hl1
1973 where ppa1.effective_date <= p_eff_date
1974 and ppa1.action_type = 'X'
1975 and ppa1.action_status = 'C'
1976 and ppa1.report_type = hl1.meaning
1977 and hl1.lookup_type = 'PAYSLIP_REPORT_TYPES'
1978 and hl1.lookup_code = 'US'
1979 and ppa1.payroll_action_id = paa1.payroll_action_id
1980 and paa1.assignment_id = paa.assignment_id
1981 and ppa1.business_group_id = ppa.business_group_id);
1982
1983
1984 cursor csr_state_det(p_location_id number) is
1985 select region_2,location_code,
1986 (select state_name from pay_us_states where state_abbrev = region_2)
1987 from hr_locations_all
1988 where location_id = p_location_id;
1989
1990 p_location_name varchar2(100);
1991
1992
1993
1994 cursor csr_uspay_det(p_asg_action_id number,p_asg_id number,p_eff_date date,p_state_code varchar2,p_state_desc varchar2,p_loc_name varchar2)
1995 is
1996 SELECT
1997 organization_name
1998 ,job
1999 ,to_char(payment_date,'YYYY-MM-DD')
2000 ,Period_type
2001 ,location_name
2002 ,employee_address1 || employee_address2 || employee_address3 || ' ' || employee_city || ' ' || employee_state || ' ' || employee_zip_code
2003 ,payroll_name
2004 ,'USD'
2005 ,to_char(ending_date,'YYYY-MM-DD')
2006 ,'Federal'
2007 ,(select status from pay_us_emp_w4dtl_action_info_v
2008 where action_context_id = p_asg_action_id
2009 and tax_jurisdiction = 'Federal'
2010 and trunc(effective_date) <= p_eff_date) STATUS
2011
2012 ,(select exemptions from pay_us_emp_w4dtl_action_info_v
2013 where action_context_id = p_asg_action_id
2014 and assignment_id = p_asg_id
2015 and trunc(effective_date) <= p_eff_date
2016 and tax_jurisdiction = 'Federal') EXEMPTIONS
2017
2018 ,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
2019 where action_context_id = p_asg_action_id
2020 and assignment_id = p_asg_id
2021 and trunc(effective_date) <= p_eff_date
2022 and tax_jurisdiction = 'Federal') ADDNL_TAX_AMOUNT
2023
2024 ,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
2025 where action_context_id = p_asg_action_id
2026 and assignment_id = p_asg_id
2027 and trunc(effective_date) <= p_eff_date
2028 and tax_jurisdiction = 'Federal') OVERRIDE_TAX_AMOUNT
2029
2030 ,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
2031 where action_context_id = p_asg_action_id
2032 and assignment_id = p_asg_id
2033 and trunc(effective_date) <= p_eff_date
2034 and tax_jurisdiction = 'Federal') OVERRIDE_TAX_PERCENTAGE
2035 ,p_state_code
2036
2037 ,(select exemptions from pay_us_emp_w4dtl_action_info_v
2038 where action_context_id = p_asg_action_id
2039 and assignment_id = p_asg_id
2040 and trunc(effective_date) <= p_eff_date
2041 and tax_jurisdiction = p_state_desc) STEXEMPTIONS
2042
2043 ,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
2044 where action_context_id = p_asg_action_id
2045 and assignment_id = p_asg_id
2046 and trunc(effective_date) <= p_eff_date
2047 and tax_jurisdiction = p_state_desc) STADDNL_TAX_AMOUNT
2048
2049 ,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
2050 where action_context_id = p_asg_action_id
2051 and assignment_id = p_asg_id
2052 and trunc(effective_date) <= p_eff_date
2053 and tax_jurisdiction = p_state_desc) STOVERRIDE_TAX_AMOUNT
2054
2055 ,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
2056 where action_context_id = p_asg_action_id
2057 and assignment_id = p_asg_id
2058 and trunc(effective_date) <= p_eff_date
2059 and tax_jurisdiction = p_state_desc) STOVERRIDE_TAX_PERCENTAGE
2060
2061 ,(select gross_earnings
2062 from pay_ac_emp_sum_action_info_v
2063 where action_context_id = p_asg_action_id
2064 and action_information_category = 'AC SUMMARY CURRENT') TOTAL_EARNINGS_CV
2065
2066 ,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
2067 from pay_ac_emp_sum_action_info_v
2068 where action_context_id = p_asg_action_id
2069 and action_information_category = 'AC SUMMARY CURRENT') TAXABLE_GROSS_CV
2070
2071 ,(select taxes
2072 from pay_ac_emp_sum_action_info_v
2073 where action_context_id = p_asg_action_id
2074 and action_information_category = 'AC SUMMARY CURRENT') TOTAL_TAXES_CV
2075
2076 ,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
2077 from pay_ac_emp_sum_action_info_v
2081 ,(select net_pay
2078 where action_context_id = p_asg_action_id
2079 and action_information_category = 'AC SUMMARY CURRENT') TOTAL_DEDUCTIONS_CV
2080
2082 from pay_ac_emp_sum_action_info_v
2083 where action_context_id = p_asg_action_id
2084 and action_information_category = 'AC SUMMARY CURRENT') NET_PAY_CV
2085
2086 ,(select gross_earnings
2087 from pay_ac_emp_sum_action_info_v
2088 where action_context_id = p_asg_action_id
2089 and action_information_category = 'AC SUMMARY YTD') TOTAL_EARNINGS_YTD
2090
2091 ,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
2092 from pay_ac_emp_sum_action_info_v
2093 where action_context_id = p_asg_action_id
2094 and action_information_category = 'AC SUMMARY YTD') TAXABLE_GROSS_YTD
2095
2096 ,(select taxes
2097 from pay_ac_emp_sum_action_info_v
2098 where action_context_id = p_asg_action_id
2099 and action_information_category = 'AC SUMMARY YTD') TOTAL_TAXES_YTD
2100
2101 ,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
2102 from pay_ac_emp_sum_action_info_v
2103 where action_context_id = p_asg_action_id
2104 and action_information_category = 'AC SUMMARY YTD') TOTAL_DEDUCTIONS_YTD
2105
2106 ,(select net_pay
2107 from pay_ac_emp_sum_action_info_v
2108 where action_context_id = p_asg_action_id
2109 and action_information_category = 'AC SUMMARY YTD') NET_PAY_YTD
2110
2111 from pay_employee_action_info_v peai
2112 where action_context_id = p_asg_action_id
2113 and assignment_id = p_asg_id
2114 and trunc(effective_date) <= p_eff_date
2115 and location_name = p_loc_name;
2116
2117 --Declaration for US Payroll -Ends Here
2118
2119 --Decalaration for UK Payroll- Starts here
2120
2121 cursor csr_ukpay_req (p_person_id number,p_eff_date date) is
2122 select to_char(action_context_id) assignment_action_id
2123 from pay_emp_payslip_action_info_v
2124 where person_id = p_person_id
2125 and effective_date = (select max(effective_date)
2126 from pay_emp_payslip_action_info_v
2127 where person_id = p_person_id
2128 and effective_date <= p_eff_date);
2129
2130 cursor csr_ukpay_ps_det(p_asg_action_id number) is
2131 select organization_name ,
2132 location_name ,
2133 job ,
2134 payroll_name ,
2135 to_char(payment_date,'YYYY-MM-DD'),
2136 pbg.currency_code ,
2137 to_char(beginning_date,'YYYY-MM-DD') ,
2138 to_char(ending_date,'YYYY-MM-DD'),
2139 paa.assignment_id
2140 from pay_employee_action_info_v empv,
2141 pay_assignment_actions paa,
2142 per_business_groups pbg
2143 where empv.action_context_id = p_asg_action_id
2144 and empv.action_context_id = paa.assignment_action_id
2145 and paa.assignment_id = nvl(empv.assignment_id,paa.assignment_id)
2146 and pbg.name = organization_name ;
2147
2148 -- cursor to fetch the run type
2149 cursor csr_run_type(p_assignment_id number,p_eff_date date) is
2150 select prtf.run_type_name
2151 from pay_payroll_actions ppa,
2152 pay_assignment_actions paa,
2153 pay_run_types_f prtf
2154 where paa.assignment_action_id in (SELECT /*+ USE_NL(paa, pact, ptp) */
2155 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2156 paa.assignment_action_id),16)) assignment_action_id
2157 FROM pay_assignment_actions paa,
2158 pay_payroll_actions pact
2159 WHERE paa.assignment_id = p_assignment_id
2160 AND paa.payroll_action_id = pact.payroll_action_id
2161 AND pact.action_type IN ('Q','R','B','I','V')
2162 AND paa.action_status = 'C'
2163 AND pact.effective_date <= p_eff_date)
2164 and ppa.payroll_action_id = paa.payroll_action_id
2165 and prtf.run_type_id = ppa.run_type_id
2166 and prtf.legislation_code = 'GB';
2167
2168 --cursor to fetch the UK earnings current value
2169
2170 cursor csr_uk_earnings_cv(p_assignment_action_id number) is
2171 SELECT /*+ leading(lck,paa2) */
2172 --pai.action_information4 NARRATIVE,
2173 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
2174 FROM pay_action_interlocks lck, -- archive action locking prepayment
2175 pay_assignment_actions paa1, -- prepayment action
2176 pay_assignment_actions paa2, -- archive action
2177 pay_payroll_actions ppa, -- prepayment
2178 pay_action_information pai, -- archived element/input value definition
2179 pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
2180 pay_assignment_actions paa, -- payroll run/quickpay action
2181 pay_payroll_actions ppa1, -- payroll run/quickpay action
2182 pay_element_types_f pet, -- element types processed by the payroll run/quickpay
2183 pay_input_values_f piv, -- "Pay values" of type Money
2184 pay_run_results prr, -- run result created by the payroll run/quick pay
2185 pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
2186 WHERE lck.locking_action_id = paa2.assignment_action_id
2187 AND paa2.payroll_action_id = pai.action_context_id
2188 AND pai.action_context_type = 'PA'
2189 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
2190 AND lck.locked_action_id = paa1.assignment_action_id
2191 AND paa1.source_action_id IS NULL
2192 AND paa1.payroll_action_id = ppa.payroll_action_id
2193 AND ppa.action_type IN ('P','U')
2194 AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
2195 AND paa1.assignment_action_id = pac.locking_action_id
2196 AND pet.element_type_id = pai.action_information2
2197 AND pet.element_type_id = piv.element_type_id
2198 AND piv.input_value_id = pai.action_information3
2199 AND prr.element_type_id = pet.element_type_id
2200 AND prr.status IN ('P','PA')
2204 AND piv.uom = 'M'
2201 AND prv.input_value_id = piv.input_value_id
2202 AND prv.run_result_id = prr.run_result_id
2203 AND piv.name = 'Pay Value'
2205 AND pac.locked_action_id = prr.assignment_action_id
2206 AND pac.locked_action_id = paa.assignment_action_id
2207 AND paa.payroll_action_id = ppa1.payroll_action_id
2208 AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2209 AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2210 AND lck.locking_action_id = p_assignment_action_id
2211 AND pai.action_information5 in ( 'E','P')
2212 GROUP BY lck.locking_action_id;
2213
2214 -- cursor to fetch the tax and national insurance current value
2215 cursor csr_uk_tx_cv(p_assignment_action_id number) is
2216 SELECT /*+ leading(lck,paa2) */
2217 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
2218 FROM pay_action_interlocks lck, -- archive action locking prepayment
2219 pay_assignment_actions paa1, -- prepayment action
2220 pay_assignment_actions paa2, -- archive action
2221 pay_payroll_actions ppa, -- prepayment
2222 pay_action_information pai, -- archived element/input value definition
2223 pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
2224 pay_assignment_actions paa, -- payroll run/quickpay action
2225 pay_payroll_actions ppa1, -- payroll run/quickpay action
2226 pay_element_types_f pet, -- element types processed by the payroll run/quickpay
2227 pay_input_values_f piv, -- "Pay values" of type Money
2228 pay_run_results prr, -- run result created by the payroll run/quick pay
2229 pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
2230 WHERE lck.locking_action_id = paa2.assignment_action_id
2231 AND paa2.payroll_action_id = pai.action_context_id
2232 AND pai.action_context_type = 'PA'
2233 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
2234 AND lck.locked_action_id = paa1.assignment_action_id
2235 AND paa1.source_action_id IS NULL
2236 AND paa1.payroll_action_id = ppa.payroll_action_id
2237 AND ppa.action_type IN ('P','U')
2238 AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
2239 AND paa1.assignment_action_id = pac.locking_action_id
2240 AND pet.element_type_id = pai.action_information2
2241 AND pet.element_type_id = piv.element_type_id
2242 AND piv.input_value_id = pai.action_information3
2243 AND prr.element_type_id = pet.element_type_id
2244 AND prr.status IN ('P','PA')
2245 AND prv.input_value_id = piv.input_value_id
2246 AND prv.run_result_id = prr.run_result_id
2247 AND piv.name = 'Pay Value'
2248 AND piv.uom = 'M'
2249 AND pac.locked_action_id = prr.assignment_action_id
2250 AND pac.locked_action_id = paa.assignment_action_id
2251 AND paa.payroll_action_id = ppa1.payroll_action_id
2252 AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2253 AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2254 AND lck.locking_action_id = p_assignment_action_id
2255 AND pai.action_information5 in ('D', NULL)
2256 AND pai.action_information4 = ('PAYE')
2257 GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
2258
2259 -- cursor to fetch the tax and national insurance current value
2260 cursor csr_uk_ni_cv(p_assignment_action_id number) is
2261 SELECT /*+ leading(lck,paa2) */
2262 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
2263 FROM pay_action_interlocks lck, -- archive action locking prepayment
2264 pay_assignment_actions paa1, -- prepayment action
2265 pay_assignment_actions paa2, -- archive action
2266 pay_payroll_actions ppa, -- prepayment
2267 pay_action_information pai, -- archived element/input value definition
2268 pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
2269 pay_assignment_actions paa, -- payroll run/quickpay action
2270 pay_payroll_actions ppa1, -- payroll run/quickpay action
2271 pay_element_types_f pet, -- element types processed by the payroll run/quickpay
2272 pay_input_values_f piv, -- "Pay values" of type Money
2273 pay_run_results prr, -- run result created by the payroll run/quick pay
2274 pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
2275 WHERE lck.locking_action_id = paa2.assignment_action_id
2276 AND paa2.payroll_action_id = pai.action_context_id
2277 AND pai.action_context_type = 'PA'
2278 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
2279 AND lck.locked_action_id = paa1.assignment_action_id
2280 AND paa1.source_action_id IS NULL
2281 AND paa1.payroll_action_id = ppa.payroll_action_id
2282 AND ppa.action_type IN ('P','U')
2283 AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
2284 AND paa1.assignment_action_id = pac.locking_action_id
2285 AND pet.element_type_id = pai.action_information2
2286 AND pet.element_type_id = piv.element_type_id
2287 AND piv.input_value_id = pai.action_information3
2288 AND prr.element_type_id = pet.element_type_id
2289 AND prr.status IN ('P','PA')
2290 AND prv.input_value_id = piv.input_value_id
2291 AND prv.run_result_id = prr.run_result_id
2292 AND piv.name = 'Pay Value'
2293 AND piv.uom = 'M'
2294 AND pac.locked_action_id = prr.assignment_action_id
2295 AND pac.locked_action_id = paa.assignment_action_id
2296 AND paa.payroll_action_id = ppa1.payroll_action_id
2297 AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2298 AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2299 AND lck.locking_action_id = p_assignment_action_id
2300 AND pai.action_information5 in ('D', NULL)
2301 AND pai.action_information4 like 'NI%'
2302 GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
2303
2304
2305 -- cursor to fetch the current net pay value for UK
2306 cursor csr_net_pay_cv (p_asg_action_id number) is
2307 select ACTION_INFORMATION16
2311 and pai.action_context_type = 'AAP';
2308 from pay_action_information pai
2309 where pai.action_context_id = p_asg_action_id
2310 and pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
2312
2313 -- cursor to fetch the tax and earnings YTD
2314 cursor csr_tx_er_ytd(p_assignment_action_id number,p_bal_name varchar2) is
2315 select pai.ACTION_INFORMATION4
2316 from pay_action_information pai,
2317 pay_defined_balances pdb,
2318 pay_balance_types pbt
2319 where pai.action_context_id = p_assignment_action_id -- 182069
2320 and to_char(pdb.DEFINED_BALANCE_ID) = (pai.ACTION_INFORMATION1)
2321 and pdb.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID
2322 and pai.action_information_category = 'EMEA BALANCES'
2323 and pai.action_context_type = 'AAP'
2324 and balance_name = p_bal_name
2325 and pbt.legislation_code = 'GB'
2326 and pdb.legislation_code = 'GB';
2327
2328 -- cursor to get the defined balance id
2329 cursor csr_def_bal_id(p_assignment_action_id number) is
2330 select defined_balance_id
2331 from pay_defined_balances pdb,
2332 pay_balance_types pbt,
2333 pay_balance_dimensions pbd
2334 where pbt.balance_name = 'NI '||(select ACTION_INFORMATION23
2335 from pay_action_information pai
2336 where pai.action_context_id = p_assignment_action_id --182069
2337 and pai.action_information_category = 'GB EMPLOYEE DETAILS'
2338 and pai.action_context_type = 'AAP')
2339 ||' Employee'
2340 and pbd.dimension_name = '_ASG_TD_YTD'
2341 and pdb.balance_type_id = pbt.balance_type_id
2342 and pdb.balance_dimension_id = pbd.balance_dimension_id
2343 and pbd.legislation_code='GB'
2344 and pbt.legislation_code='GB';
2345
2346 -- cursor to get the latest_action_id
2347 cursor csr_lat_action_id(p_assignment_id number,p_payment_date date)is
2348
2349 SELECT /*+ USE_NL(paa, pact, ptp) */
2350 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2351 paa.assignment_action_id),16)) assignment_action_id
2352 FROM pay_assignment_actions paa,
2353 pay_payroll_actions pact
2354 WHERE paa.assignment_id = p_assignment_id --16986
2355 AND paa.payroll_action_id = pact.payroll_action_id
2356 AND pact.action_type IN ('Q','R','B','I','V')
2357 AND paa.action_status = 'C'
2358 AND pact.effective_date <= p_payment_date;
2359
2360 -- cursor to fetch the netpay balance id
2361 cursor csr_netpay_bal_id is
2362 select defined_balance_id
2363 from pay_defined_balances pdb,
2364 pay_balance_types pbt,
2365 pay_balance_dimensions pbd
2366 where pbt.balance_name = 'Net Pay'
2367 and pbd.dimension_name = '_ASG_TD_YTD'
2368 and pdb.balance_type_id = pbt.balance_type_id
2369 and pdb.balance_dimension_id = pbd.balance_dimension_id
2370 and pbd.legislation_code='GB'
2371 and pbt.legislation_code='GB';
2372
2373
2374 p_latest_action_id number;
2375 p_netpay_bal_id number;
2376 p_def_bal_id number;
2377
2378
2379 --Declaration for UK Payroll -Ends Here
2380
2381 --Declaration for payroll - Ends here
2382
2383 BEGIN
2384 -- fetch the person id business group id and effective date for all the search criteria and store it in a table of record type
2385 --p_error := 'Before Search counts||';
2386
2387 if p_srch_criteria.count > 0 then
2388
2389 for i in p_srch_criteria.first .. p_srch_criteria.last
2390 loop
2391 if p_srch_criteria(i).p_effective_date is null
2392 then
2393 p_error := 'Effective date is mandatory.Please enter the effective date';
2394 exit;
2395 end if;
2396
2397 if p_srch_criteria(i).p_bgrp_id is not null
2398 then
2399 p_bus_group_id := p_srch_criteria(i).p_bgrp_id;
2400 end if;
2401
2402 if p_srch_criteria(i).p_bgrp_name is not null
2403 then
2404 select business_group_id into p_bus_group_id
2405 from hr_organization_units org
2406 where upper(name) = upper(p_srch_criteria(i).p_bgrp_name)
2407 and org.organization_id = org.business_group_id
2408 and p_srch_criteria(i).p_effective_date between date_from and
2409 nvl (date_to, to_date('31-12-4712', 'DD-MM-YYYY'));
2410 end if;
2411
2412 if p_srch_criteria(i).p_employee_category is not null
2413 then
2414 select LOOKUP_CODE into p_empl_category
2415 from HR_LOOKUPS where lookup_type = 'EMPLOYEE_CATG'
2416 AND meaning = p_srch_criteria(i).p_employee_category
2417 AND p_srch_criteria(i).p_effective_date between nvl(start_date_active,p_srch_criteria(i).p_effective_date)
2418 and nvl(end_date_active,p_srch_criteria(i).p_effective_date)
2419 and enabled_flag = 'Y';
2420 end if;
2421
2422 if p_srch_criteria(i).p_employment_category is not null
2423 then
2424 select LOOKUP_CODE into p_emplmt_category
2425 from HR_LOOKUPS where lookup_type = 'EMP_CAT'
2426 AND meaning = p_srch_criteria(i).p_employment_category
2427 AND p_srch_criteria(i).p_effective_date between nvl(start_date_active,p_srch_criteria(i).p_effective_date)
2428 and nvl(end_date_active,p_srch_criteria(i).p_effective_date)
2429 and enabled_flag = 'Y';
2430 end if;
2431
2432 -- Special cases when qualification_id, phone_id, deliver_mothod id
2433 -- and person_address_id is passed without person_id or assignment_id
2434 IF p_srch_criteria(i).p_phone_id IS NOT NULL
2435 AND p_srch_criteria(i).p_assignment_id IS NULL
2436 AND p_srch_criteria(i).p_person_id IS NULL THEN
2437 open srch_filtrd for
2438
2442 ,per_phones pp
2439 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
2440 from per_people_f ppf
2441 ,per_person_types ppt
2443 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
2444 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
2445 and ppf.person_id = pp.parent_id(+)
2446 and pp.parent_table = 'PER_ALL_PEOPLE_F'
2447 and pp.phone_id = p_srch_criteria(i).p_phone_id;
2448
2449 fetch srch_filtrd bulk collect into p_srch_filter_tbl.person_id,p_srch_filter_tbl.business_group_id,p_srch_filter_tbl.p_srch_dt;
2450 close srch_filtrd;
2451
2452 ELSIF p_srch_criteria(i).p_qualification_id IS NOT NULL
2453 AND p_srch_criteria(i).p_assignment_id IS NULL
2454 AND p_srch_criteria(i).p_person_id IS NULL THEN
2455 open srch_filtrd for
2456
2457 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
2458 from per_people_f ppf
2459 ,per_person_types ppt
2460 ,per_qualifications pq
2461 ,per_establishment_attendances pea
2462 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
2463 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
2464 and pq.attendance_id = pea.attendance_id(+)
2465 and (pq.person_id = ppf.person_id or pea.person_id = ppf.person_id)
2466 and pq.qualification_id = p_srch_criteria(i).p_qualification_id;
2467
2468 fetch srch_filtrd bulk collect into p_srch_filter_tbl.person_id,p_srch_filter_tbl.business_group_id,p_srch_filter_tbl.p_srch_dt;
2469 close srch_filtrd;
2470
2471 ELSIF p_srch_criteria(i).p_delivery_method_id IS NOT NULL
2472 AND p_srch_criteria(i).p_assignment_id IS NULL
2473 AND p_srch_criteria(i).p_person_id IS NULL THEN
2474 open srch_filtrd for
2475
2476 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
2477 from per_people_f ppf
2478 ,per_person_types ppt
2479 ,per_person_dlvry_methods ppdm
2480 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
2481 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
2482 and ppdm.person_id = ppf.person_id
2483 and ppdm.delivery_method_id = p_srch_criteria(i).p_delivery_method_id;
2484
2485 fetch srch_filtrd bulk collect into p_srch_filter_tbl.person_id,p_srch_filter_tbl.business_group_id,p_srch_filter_tbl.p_srch_dt;
2486 close srch_filtrd;
2487
2488 ELSIF p_srch_criteria(i).p_address_id IS NOT NULL
2489 AND p_srch_criteria(i).p_assignment_id IS NULL
2490 AND p_srch_criteria(i).p_person_id IS NULL THEN
2491 open srch_filtrd for
2492
2493 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
2494 from per_people_f ppf
2495 ,per_person_types ppt
2496 ,per_addresses pa
2497 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
2498 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
2499 and pa.person_id = ppf.person_id
2500 and pa.address_id = p_srch_criteria(i).p_address_id;
2501
2502 fetch srch_filtrd bulk collect into p_srch_filter_tbl.person_id,p_srch_filter_tbl.business_group_id,p_srch_filter_tbl.p_srch_dt;
2503 close srch_filtrd;
2504
2505 ELSE
2506 open srch_filtrd for
2507
2508 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
2509 from per_people_f ppf ,per_person_types ppt ,per_assignments_f paaf
2510 where nvl(p_srch_criteria(i).p_person_id, ppf.person_id) = ppf.person_id
2511 and ppf.person_id between nvl(p_srch_criteria(i).p_start_person_id, ppf.person_id)
2512 and nvl(p_srch_criteria(i).p_end_person_id, ppf.person_id)
2513 and ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
2514 and last_name = nvl(p_srch_criteria(i).p_last_name,last_name)
2515 and nvl(first_name,'*') = nvl(nvl(p_srch_criteria(i).p_first_name,first_name),'*')
2516 and nvl(employee_number,'*') = nvl(nvl(p_srch_criteria(i).p_employee_no,employee_number),'*')
2517 and nvl(npw_number,'*') = nvl(nvl(p_srch_criteria(i).p_cwk_no,npw_number),'*')
2518 and nvl(applicant_number,'*') = nvl(nvl(p_srch_criteria(i).p_applicant_no,applicant_number),'*')
2519 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
2520 and nvl(ppt.user_person_type ,'*') = nvl(nvl(p_srch_criteria(i).p_person_type,ppt.user_person_type),'*')
2521 and ppf.person_type_id = ppt.person_type_id
2522 and ppf.business_group_id = ppt.business_group_id
2523 and ppf.person_id = paaf.person_id (+)
2524 and nvl(paaf.assignment_id, -1 ) = nvl(nvl(p_srch_criteria(i).p_assignment_id,paaf.assignment_id),-1)
2525 and p_srch_criteria(i).p_effective_date between paaf.effective_start_date (+) and paaf.effective_end_date (+)
2526 and nvl(employment_category,'*') = nvl(nvl(p_emplmt_category,paaf.employment_category ),'*')
2527 and nvl(employee_category,'*') = nvl(nvl(p_empl_category,paaf.employee_category ),'*');
2528
2529
2530 fetch srch_filtrd bulk collect into p_srch_filter_tbl.person_id,p_srch_filter_tbl.business_group_id,p_srch_filter_tbl.p_srch_dt;
2531 close srch_filtrd;
2532 END IF;
2533
2537 -- loop through each of the person record stored in the table of record p_srch_filter_tbl
2534 end loop;
2535 end if;
2536 --p_error := 'after selecting the personids||';
2538 -- and fetch the person details and all other details
2539
2540 if p_srch_filter_tbl.person_id.count > 0
2541 then
2542 -- p_error := p_error||'Looping in the Person Id||';
2543 for j in p_srch_filter_tbl.person_id.first .. p_srch_filter_tbl.person_id.last
2544 loop
2545
2546 open csr_person_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2547 -- p_error := p_error||'before fetch||'||p_srch_filter_tbl.person_id(j)||'||'||p_srch_filter_tbl.business_group_id(j)||'||'||p_srch_filter_tbl.p_srch_dt(j);
2548 fetch csr_person_record into p_person(j).person;
2549 -- p_error := p_error||'after fectch||';
2550 close csr_person_record;
2551 --p_error := p_error||'before Entity||';
2552
2553 if p_entity.count > 0 then
2554 for m in p_entity.first .. p_entity.last
2555 loop
2556
2557
2558 if p_entity(m) = 'ASSIGNMENT' then
2559 p_cnt := 1;
2560
2561 open csr_assignment_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2562 loop
2563 fetch csr_assignment_record into p_person(j).assignment(p_cnt).asg_details;
2564 exit when csr_assignment_record%notfound;
2565
2566 open csr_asg_loc_addr(p_person(j).assignment(p_cnt).asg_details.location_id);
2567 fetch csr_asg_loc_addr into p_person(j).assignment(p_cnt).asg_loc_addr;
2568 close csr_asg_loc_addr;
2569
2570 p_cnt := p_cnt + 1;
2571 end loop;
2572 close csr_assignment_record;
2573 --p_error := p_error||'after selecting the assignments||';
2574 end if;
2575
2576
2577 if p_entity(m) = 'CONTACT' then
2578 open csr_contact_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2579 fetch csr_contact_record bulk collect into p_person(j).contact;
2580 close csr_contact_record;
2581 --p_error := p_error||'after selecting the contacts||';
2582 end if;
2583
2584 if p_entity(m) = 'CONTACT_ADDRESS' then
2585 open csr_cont_address(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2586 fetch csr_cont_address bulk collect into p_person(j).contact_address;
2587 close csr_cont_address;
2588 --p_error := p_error||'after selecting the contact address||';
2589 end if;
2590
2591
2592 if p_entity(m) = 'ADDRESS' then
2593 open csr_address_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2594 fetch csr_address_record bulk collect into p_person(j).address;
2595 close csr_address_record;
2596 --p_error := p_error||'after selecting the addresses||';
2597 end if;
2598
2599 if p_entity(m) = 'PERSON_EXTRA_INFO' then
2600 open csr_person_extra_info_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2601 fetch csr_person_extra_info_record bulk collect into p_person(j).person_extra_information;
2602 close csr_person_extra_info_record;
2603 --p_error := p_error||'after selecting the person extra information||';
2604 end if;
2605
2606 if p_entity(m) = 'SPECIAL_INFO' then
2607 open csr_special_info_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2608 fetch csr_special_info_record bulk collect into p_person(j).special_information;
2609 close csr_special_info_record;
2610 -- p_error := p_error||'after selecting the person special information||';
2611 end if;
2612
2613 if p_entity(m) = 'PREVIOUS_EMPLOYMENT' then
2614 open csr_prev_emp_info_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2615 fetch csr_prev_emp_info_record bulk collect into p_person(j).previous_employment;
2616 close csr_prev_emp_info_record;
2617 -- p_error := p_error||'after selecting the person previous employment||';
2618 end if;
2619
2620
2621 if p_entity(m) = 'DELIVERY_METHODS' then
2622 open csr_deliv_method_info(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2623 fetch csr_deliv_method_info bulk collect into p_person(j).delivery_methods;
2624
2625 close csr_deliv_method_info;
2626 --p_error := p_error||'after selecting the delivery methods||';
2627 end if;
2628
2629 if p_entity(m) = 'WORK_INCIDENTS' then
2630 open csr_work_inc_info_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2631 fetch csr_work_inc_info_record bulk collect into p_person(j).work_incidents;
2632 close csr_work_inc_info_record;
2633 --p_error := p_error||'after selecting the work incidents||';
2634 end if;
2635
2639 close csr_ass_extra_info_record;
2636 if p_entity(m) = 'ASSIGNMENT_EXTRA_INFO' then
2637 open csr_ass_extra_info_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2638 fetch csr_ass_extra_info_record bulk collect into p_person(j).assignment_extra_information;
2640 -- p_error := p_error||'after selecting the ASSIGNMENT_EXTRA_INFO||';
2641 end if;
2642
2643 if p_entity(m) = 'CONTACT_EXTRA_INFO' then
2644 open csr_con_extra_info_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2645 fetch csr_con_extra_info_record bulk collect into p_person(j).contact_extra_information;
2646 close csr_con_extra_info_record;
2647 --p_error := p_error||'after selecting the CONTACT_EXTRA_INFO||';
2648 end if;
2649
2650 if p_entity(m) = 'PHONE' then
2651 open csr_phone_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2652 fetch csr_phone_record bulk collect into p_person(j).phone;
2653 close csr_phone_record;
2654 --p_error := p_error||'after selecting the PHONE||';
2655 end if;
2656
2657 if p_entity(m) = 'QUALIFICATION' then
2658 p_cnt := 1;
2659 open csr_qualification_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2660 LOOP
2661
2662 fetch csr_qualification_record INTO p_person(j).qualification(p_cnt).QUALIFICATIONS;
2663 exit when csr_qualification_record%NOTFOUND;
2664
2665
2666
2667 open csr_subject_record(p_person(j).qualification(p_cnt).qualifications.qualification_id,p_srch_filter_tbl.p_srch_dt(j));
2668 fetch csr_subject_record bulk collect into p_person(j).qualification(p_cnt).subject;
2669 close csr_subject_record;
2670 p_cnt := p_cnt + 1;
2671 end loop;
2672 close csr_qualification_record;
2673 --p_error := p_error||'after selecting the QUALIFICATION||';
2674 end if;
2675
2676 if p_entity(m) = 'REPORTEE' then
2677 open csr_reportee_record(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j));
2678 fetch csr_reportee_record bulk collect into p_person(j).reportee;
2679 close csr_reportee_record;
2680 --p_error := p_error||'after selecting the REPORTEE||';
2681 end if;
2682
2683 if p_entity(m) = 'ABSENCE' then
2684 p_cnt := 1;
2685 FOR accrual_details in csr_abs_details(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.business_group_id(j),p_srch_filter_tbl.p_srch_dt(j))
2686 loop
2687 per_accrual_calc_functions.get_net_accrual(
2688 P_assignment_id => ACCRUAL_DETAILS.assignment_id,
2689 P_plan_id => ACCRUAL_DETAILS.ACCRUAL_PLAN_ID,
2690 P_payroll_id => ACCRUAL_DETAILS.payroll_id,
2691 p_business_group_id => p_srch_filter_tbl.business_group_id(j),
2692 p_assignment_action_id => -1,
2693 P_calculation_date => p_srch_filter_tbl.p_srch_dt(j),
2694 P_Accrual_Start_Date => Null,
2695 P_Accrual_Latest_Balance => Null,
2696 P_Start_Date => L_Start_Date,
2697 P_End_Date => L_End_Date,
2698 P_Accrual_End_Date => L_Accrual_End_Date,
2699 P_Accrual => L_Accrual,
2700 P_Net_Entitlement => L_Entitlement
2701 );
2702 SELECT ACCRUAL_DETAILS.ACCRUAL_PLAN_NAME,L_ENTITLEMENT,ACCRUAL_DETAILS.ACCRUAL_UNITS_OF_MEASURE_NAME,
2703 TO_CHAR(p_srch_filter_tbl.p_srch_dt(j),'YYYY-MM-DD')
2704 INTO p_person(j).ABSENCE(P_CNT) FROM DUAL;
2705 p_cnt := p_cnt + 1;
2706 end loop;
2707
2708 --p_error := p_error||'after selecting the Absence||';
2709 end if;
2710
2711 if p_entity(m) = 'BENEFITS' then
2712
2713 /* Code comment for bug 7689952 Start
2714 open c_get_id(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j),p_srch_filter_tbl.business_group_id(j));
2715 fetch c_get_id into l_per_in_ler_id,l_lf_evt_ocrd_dt;
2716 close c_get_id;
2717 Code comment for bug 7689952 End */
2718
2719 open c_get_dep_details(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j),p_srch_filter_tbl.business_group_id(j));
2720 FETCH c_get_dep_details BULK COLLECT INTO p_person(j).benefit_details.dependent;
2721 close c_get_dep_details;
2722
2723 p_cnt := p_person(j).benefit_details.dependent.count + 1;
2724
2725 for h in c_get_ben_details(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j),p_srch_filter_tbl.business_group_id(j))
2726 loop
2727 p_person(j).benefit_details.dependent(p_cnt).name := h.beneficiary_full_name;
2731 p_person(j).benefit_details.dependent(p_cnt).coverage := to_char(h.Primary_Bnf);
2728 p_person(j).benefit_details.dependent(p_cnt).relationship := h.Relation;
2729 p_person(j).benefit_details.dependent(p_cnt).type_of_benefit := h.plan_name;
2730 if h.Primary_Bnf is not null then
2732 elsif h.Contingent_Bnf is not null then
2733 p_person(j).benefit_details.dependent(p_cnt).coverage := to_char(h.Contingent_Bnf);
2734 elsif h.Primary_Bnf_amt is not null then
2735 p_person(j).benefit_details.dependent(p_cnt).coverage := to_char(h.Primary_Bnf_amt);
2736 elsif h.Contingent_Bnf_amt is not null then
2737 p_person(j).benefit_details.dependent(p_cnt).coverage := to_char(h.Contingent_Bnf_amt);
2738 end if;
2739 p_cnt := p_cnt + 1;
2740 end loop;
2741
2742 open c_get_pgm_enrt_details(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j),p_srch_filter_tbl.business_group_id(j));
2743 FETCH c_get_pgm_enrt_details BULK COLLECT INTO p_person(j).benefit_details.benefit;
2744 close c_get_pgm_enrt_details;
2745
2746 end if;
2747
2748 if p_entity(m) = 'PAYROLL' then
2749
2750 open csr_leg_code(p_srch_filter_tbl.business_group_id(j));
2751 fetch csr_leg_code into p_leg_code;
2752 close csr_leg_code;
2753
2754 -- Process US Payroll since the legislationcode is US
2755
2756 if p_leg_code = 'US' then
2757 p_cnt := 1;
2758
2759 open csr_uspay_req(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j));
2760 loop
2761 fetch csr_uspay_req into p_pyrl_action_id,p_assignment_id,p_assg_action_id,p_location_id;
2762 exit when csr_uspay_req%notfound;
2763
2764 open csr_state_det(p_location_id);
2765 fetch csr_state_det into p_state_code,p_location_name,p_state_desc;
2766 close csr_state_det;
2767
2768
2769 p_person(j).payroll(p_cnt).LEGISLATION_CODE := p_leg_code;
2770
2771 open csr_uspay_det(p_assg_action_id,p_assignment_id,p_srch_filter_tbl.p_srch_dt(j), p_state_code,p_state_desc,p_location_name);
2772 fetch csr_uspay_det into p_person(j).payroll(p_cnt).COMPANY,
2773 p_person(j).payroll(p_cnt).JOB_TITLE,
2774 p_person(j).payroll(p_cnt).PAYMENT_DATE,
2775 p_person(j).payroll(p_cnt).PAY_FREQUENCY,
2776 p_person(j).payroll(p_cnt).TAX_LOCATION,
2777 p_person(j).payroll(p_cnt).ADDRESS,
2778 p_person(j).payroll(p_cnt).PAY_GROUP,
2779 p_person(j).payroll(p_cnt).CURRENCY_CODE,
2780 p_person(j).payroll(p_cnt).PERIOD_END,
2781 p_person(j).payroll(p_cnt).TAX_JURISDICTION,
2782 p_person(j).payroll(p_cnt).MARITAL_STATUS,
2783 p_person(j).payroll(p_cnt).FED_EXEMPTIONS,
2784 p_person(j).payroll(p_cnt).FED_ADDNL_TAX_AMOUNT,
2785 p_person(j).payroll(p_cnt).FED_OVERRIDE_TAX_AMOUNT,
2786 p_person(j).payroll(p_cnt).FED_OVERRIDE_TAX_PERCENTAGE,
2787 p_person(j).payroll(p_cnt).STATE_CODE,
2788 p_person(j).payroll(p_cnt).ST_EXEMPTIONS,
2789 p_person(j).payroll(p_cnt).ST_ADDNL_TAX_AMOUNT,
2790 p_person(j).payroll(p_cnt).ST_OVERRIDE_TAX_AMOUNT,
2791 p_person(j).payroll(p_cnt).ST_OVERRIDE_TAX_PERCENTAGE,
2792 p_person(j).payroll(p_cnt).TOTAL_EARNINGS_CV,
2793 p_person(j).payroll(p_cnt).TAX_GROSS_CV,
2794 p_person(j).payroll(p_cnt).TOTAL_TAXES_CV,
2795 p_person(j).payroll(p_cnt).TOTAL_DED_CV,
2796 p_person(j).payroll(p_cnt).TOTAL_NETPAY_CV,
2797 p_person(j).payroll(p_cnt).TOTAL_EARNINGS_YTD,
2798 p_person(j).payroll(p_cnt).TAX_GROSS_YTD,
2799 p_person(j).payroll(p_cnt).TOTAL_TAXES_YTD,
2800 p_person(j).payroll(p_cnt).TOTAL_DED_YTD,
2801 p_person(j).payroll(p_cnt).TOTAL_NETPAY_YTD;
2802 close csr_uspay_det;
2803
2804 p_cnt := p_cnt+1;
2805
2806 end loop;
2807 close csr_uspay_req;
2808
2809 end if;
2810
2811 if p_leg_code = 'GB' then
2812
2813 p_cnt := 1;
2814
2815 open csr_ukpay_req(p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j));
2816 loop
2817
2818 p_person(j).payroll(p_cnt).LEGISLATION_CODE := p_leg_code;
2819
2820 fetch csr_ukpay_req into p_assg_action_id;
2821 exit when csr_ukpay_req%notfound;
2822
2823 -- to fetch the details in the pay summary region
2824 open csr_ukpay_ps_det(p_assg_action_id);
2825 fetch csr_ukpay_ps_det into p_person(j).payroll(p_cnt).company,
2826 p_person(j).payroll(p_cnt).address,
2827 p_person(j).payroll(p_cnt).job_title,
2831 p_person(j).payroll(p_cnt).period_begin,
2828 p_person(j).payroll(p_cnt).pay_group,
2829 p_person(j).payroll(p_cnt).payment_date,
2830 p_person(j).payroll(p_cnt).currency_code,
2832 p_person(j).payroll(p_cnt).period_end,
2833 p_assignment_id;
2834 close csr_ukpay_ps_det;
2835
2836
2837 -- fetch the run type for the pay summary region
2838 open csr_run_type(p_assignment_id,p_srch_filter_tbl.p_srch_dt(j));
2839 fetch csr_run_type into p_person(j).payroll(p_cnt).RUN_TYPE;
2840 close csr_run_type;
2841
2842 --fetch the earnings current value
2843 open csr_uk_earnings_cv(p_assg_action_id);
2844 fetch csr_uk_earnings_cv into p_person(j).payroll(p_cnt).TOTAL_EARNINGS_CV;
2845 close csr_uk_earnings_cv;
2846
2847 -- fetch the tax and national insurance current value
2848
2849 open csr_uk_ni_cv(p_assg_action_id);
2850 fetch csr_uk_ni_cv into p_person(j).payroll(p_cnt).NI_CV;
2851 close csr_uk_ni_cv;
2852
2853 open csr_uk_tx_cv(p_assg_action_id);
2854 fetch csr_uk_tx_cv into p_person(j).payroll(p_cnt).TOTAL_TAXES_CV;
2855 close csr_uk_tx_cv;
2856
2857 -- fetch the net pay current value
2858
2859 open csr_net_pay_cv(p_assg_action_id);
2860 fetch csr_net_pay_cv into p_person(j).payroll(p_cnt).TOTAL_NETPAY_CV;
2861 close csr_net_pay_cv;
2862
2863 -- fetch the earnings YTD
2864 open csr_tx_er_ytd(p_assg_action_id, 'Gross Pay');
2865 fetch csr_tx_er_ytd into p_person(j).payroll(p_cnt).TOTAL_EARNINGS_YTD;
2866 close csr_tx_er_ytd;
2867
2868 -- fetch the tax YTD
2869 open csr_tx_er_ytd(p_assg_action_id, 'PAYE');
2870 fetch csr_tx_er_ytd into p_person(j).payroll(p_cnt).TOTAL_TAXES_YTD;
2871 close csr_tx_er_ytd;
2872
2873
2874 open csr_def_bal_id(p_assg_action_id);
2875 fetch csr_def_bal_id into p_def_bal_id;
2876 close csr_def_bal_id;
2877
2878 open csr_netpay_bal_id;
2879 fetch csr_netpay_bal_id into p_netpay_bal_id;
2880 close csr_netpay_bal_id;
2881
2882 open csr_lat_action_id(p_assignment_id,p_person(j).payroll(p_cnt).payment_date);
2883 fetch csr_lat_action_id into p_latest_action_id;
2884 close csr_lat_action_id;
2885
2886
2887 -- fetch the net pay YTD value
2888 if (p_netpay_bal_id is not null) and (p_latest_action_id is not null)
2889 then
2890 select pay_balance_pkg.get_value(p_netpay_bal_id,p_latest_action_id)
2891 into p_person(j).payroll(p_cnt).TOTAL_NETPAY_YTD
2892 from dual ;
2893 end if;
2894
2895 -- fetch the NI YTD
2896 if(p_def_bal_id is not null) and (p_latest_action_id is not null)
2897 then
2898 select pay_balance_pkg.get_value(p_def_bal_id,p_latest_action_id)
2899 into p_person(j).payroll(p_cnt).NI_YTD
2900 from dual;
2901 end if;
2902
2903
2904 p_cnt := p_cnt+1;
2905
2906 end loop;
2907
2908 close csr_ukpay_req;
2909
2910 end if;
2911
2912 end if;
2913
2914 end loop;
2915 -- p_error := 'p_error||'Ended the options loop||';
2916 end if;
2917
2918 end loop;
2919 --p_error := p_error||'Ended the person search loop||';
2920 end if;
2921
2922 exception when others
2923 then
2924 p_error := p_error||substr(SQLERRM,1,1500);
2925
2926 END GET_PERSON_DETAILS;
2927 end HR_PERSON_RECORD ;