[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_RECORD
Source
1 PACKAGE BODY HR_PERSON_RECORD as
2 /* $Header: peperqry.pkb 120.8 2011/10/03 10:11:44 srannama ship $ */
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 ppf.PERSON_ID,
44 to_char(ppf.EFFECTIVE_START_DATE,'YYYY-MM-DD'),
45 to_char(ppf.EFFECTIVE_END_DATE,'YYYY-MM-DD'),
46 ppf.BUSINESS_GROUP_ID,
47 hr_general.decode_organization(ppf.BUSINESS_GROUP_ID) BUSINESS_GROUP_DESC,
48 pptu.PERSON_TYPE_ID,
49 pptl.user_person_type 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 ppf.ATTRIBUTE_CATEGORY,
160 ppf.ATTRIBUTE1,
161 ppf.ATTRIBUTE2,
162 ppf.ATTRIBUTE3,
163 ppf.ATTRIBUTE4,
164 ppf.ATTRIBUTE5,
165 ppf.ATTRIBUTE6,
166 ppf.ATTRIBUTE7,
167 ppf.ATTRIBUTE8,
168 ppf.ATTRIBUTE9,
169 ppf.ATTRIBUTE10,
170 ppf.ATTRIBUTE11,
171 ppf.ATTRIBUTE12,
172 ppf.ATTRIBUTE13,
173 ppf.ATTRIBUTE14,
174 ppf.ATTRIBUTE15,
175 ppf.ATTRIBUTE16,
176 ppf.ATTRIBUTE17,
177 ppf.ATTRIBUTE18,
178 ppf.ATTRIBUTE19,
179 ppf.ATTRIBUTE20,
180 ppf.ATTRIBUTE21,
181 ppf.ATTRIBUTE22,
182 ppf.ATTRIBUTE23,
183 ppf.ATTRIBUTE24,
184 ppf.ATTRIBUTE25,
185 ppf.ATTRIBUTE26,
186 ppf.ATTRIBUTE27,
187 ppf.ATTRIBUTE28,
188 ppf.ATTRIBUTE29,
189 ppf.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 NULL AS ADJUSTED_SVC_DATE,
224 NULL AS DATE_START,
225 NULL AS ACCEPTED_TERMINATION_DATE,
226 NULL AS ACTUAL_TERMINATION_DATE,
227 NULL AS FINAL_PROCESS_DATE,
228 NULL AS LAST_STANDARD_PROCESS_DATE,
229 NULL AS LEAVING_REASON,
230 TOWN_OF_BIRTH,
231 REGION_OF_BIRTH,
232 COUNTRY_OF_BIRTH,
233 (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL
234 WHERE TERRITORY_CODE = COUNTRY_OF_BIRTH)
235 COUNTRY_OF_BIRTH_DESC,
236 GLOBAL_PERSON_ID,
237 COORD_BEN_MED_PL_NAME,
238 COORD_BEN_MED_INSR_CRR_NAME,
239 COORD_BEN_MED_INSR_CRR_IDENT,
240 COORD_BEN_MED_EXT_ER,
241 to_char(COORD_BEN_MED_CVG_STRT_DT,'YYYY-MM-DD'),
242 to_char(COORD_BEN_MED_CVG_END_DT,'YYYY-MM-DD'),
243 PARTY_ID,
244 NPW_NUMBER,
245 CURRENT_NPW_FLAG,
246 GLOBAL_NAME,
247 LOCAL_NAME
248 FROM PER_PEOPLE_F ppf,
249 per_person_types ppt,
250 per_person_types_v pptl,
251 per_person_type_usages_f pptu
252 where ppf.person_id = p_person_id
253 and ppf.business_group_id = p_business_group_id
254 and ppt.person_type_id = pptl.person_type_id
255 and pptu.person_id = ppf.person_id
256 and pptu.person_type_id = ppt.person_type_id
257 and p_eff_date between pptu.effective_start_date and nvl(pptu.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
258 and ppf.business_group_id = ppt.business_group_id (+)
259 and p_eff_date between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
260 ORDER BY DECODE(ppt.system_person_type
261 ,'EMP' ,1
262 ,'CWK' ,2
263 ,'APL' ,3
264 ,'EX_EMP',4
265 ,'EX_CWK',5
266 ,'EX_APL',6
267 ,7
268 );
269
270 -- cursor to fetch the assignment records
271 cursor csr_assignment_record(p_person_id number,p_business_group_id number,p_eff_date date) IS
272 SELECT ASSIGNMENT_ID
273 , to_char(PAAF.EFFECTIVE_START_DATE,'YYYY-MM-DD')
274 , to_char(PAAF.EFFECTIVE_END_DATE,'YYYY-MM-DD')
275 , RECRUITER_ID
276 , GRADE_ID
277 , (SELECT NAME FROM PER_GRADES_TL PGT WHERE PGT.GRADE_ID = paaf.GRADE_ID
278 AND LANGUAGE = USERENV('LANG')) GRADE_DESC
279 , POSITION_ID
280 , (SELECT NAME FROM HR_ALL_POSITIONS_F_TL HAPFT WHERE HAPFT.POSITION_ID = paaf.POSITION_ID
281 AND LANGUAGE = USERENV('LANG')) POSITION_DESC
282 , JOB_ID
283 , (SELECT NAME FROM PER_JOBS_TL PJT WHERE PJT.JOB_ID = paaf.JOB_ID AND LANGUAGE = USERENV('LANG')) JOB_DESC
284 , PAAF.ASSIGNMENT_STATUS_TYPE_ID
285 , STTL.USER_STATUS
286 , ST.PAY_SYSTEM_STATUS
287 , ST.PER_SYSTEM_STATUS
288 , PAYROLL_ID
289 , (SELECT PAYROLL_NAME FROM PAY_PAYROLLS_F PPF WHERE PPF.PAYROLL_ID = PAAF.PAYROLL_ID
290 and p_eff_date between ppf.effective_start_date and
291 nvl(ppf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) PAYROLL_DESC
292 , LOCATION_ID
293 , (SELECT DESCRIPTION FROM HR_LOCATIONS_ALL_TL HLAT WHERE HLAT.LOCATION_ID = PAAF.LOCATION_ID
294 AND LANGUAGE = USERENV('LANG')) LOCATION_DESC
295 , PERSON_REFERRED_BY_ID
296 , SUPERVISOR_ID
297 , (SELECT EMPLOYEE_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
298 and p_eff_date between papf.effective_start_date and
299 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_EMP_NUMBER
300 , (SELECT NPW_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
301 and p_eff_date between papf.effective_start_date and
302 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_CWK_NUMBER
303 , (SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
304 and p_eff_date between papf.effective_start_date and
305 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_NAME
306 , SPECIAL_CEILING_STEP_ID
307 , RECRUITMENT_ACTIVITY_ID
308 , SOURCE_ORGANIZATION_ID
309 , ORGANIZATION_ID
310 , (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS_TL HAOUT WHERE HAOUT.ORGANIZATION_ID =
311 PAAF.ORGANIZATION_ID AND LANGUAGE = USERENV('LANG')) ORGANIZATION_DESC
312 , PEOPLE_GROUP_ID
313 , SOFT_CODING_KEYFLEX_ID
314 , VACANCY_ID
315 , (SELECT NAME FROM PER_VACANCIES PV
316 WHERE PV.VACANCY_ID = PAAF.VACANCY_ID) VACANCY_DESC
317 , PAY_BASIS_ID
318 , (SELECT NAME FROM PER_PAY_BASES PPB
319 WHERE PPB.PAY_BASIS_ID = PAAF.PAY_BASIS_ID) PAY_BASIS_DESC
320 , ASSIGNMENT_SEQUENCE
321 , ASSIGNMENT_TYPE
322 , PAAF.PRIMARY_FLAG
323 , APPLICATION_ID
324 , ASSIGNMENT_NUMBER
325 , CHANGE_REASON
326 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE =
327 DECODE(PAAF.ASSIGNMENT_TYPE,'E','EMP_ASSIGN_REASON', 'C', 'CWK_ASSIGN_REASON','')
328 AND LOOKUP_CODE = PAAF.CHANGE_REASON AND p_eff_date between nvl(start_date_active,p_eff_date)
329 and nvl(end_date_active,p_eff_Date)
330 and enabled_flag = 'Y') CHANGE_REASON_DESC
331 , COMMENT_ID
332 , to_char(DATE_PROBATION_END,'YYYY-MM-DD')
333 , DEFAULT_CODE_COMB_ID
334 , EMPLOYMENT_CATEGORY
335 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'EMP_CAT'
336 AND LOOKUP_CODE = EMPLOYMENT_CATEGORY
337 AND p_eff_date between nvl(start_date_active,p_eff_date) and nvl(end_date_active,p_eff_Date)
338 and enabled_flag = 'Y')
339 EMPLOYMENT_CATEGORY_DESC -- Too many values
340 , FREQUENCY
341 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
342 AND LOOKUP_CODE = PAAF.FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
343 and nvl(end_date_active,p_eff_Date)
344 and enabled_flag = 'Y')
345 FREQUENCY_DESC -- Too many values
346 , INTERNAL_ADDRESS_LINE
347 , MANAGER_FLAG
348 , NORMAL_HOURS
349 , PERF_REVIEW_PERIOD
350 , PERF_REVIEW_PERIOD_FREQUENCY
351 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
352 AND LOOKUP_CODE = PAAF.PERF_REVIEW_PERIOD_FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
353 and nvl(end_date_active,p_eff_Date)
354 and enabled_flag = 'Y')
355 PERF_RP_FREQUENCY_DESC -- Too many values
356 , PERIOD_OF_SERVICE_ID
357 , PROBATION_PERIOD
358 , PROBATION_UNIT
359 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'QUALIFYING_UNITS'
360 AND LOOKUP_CODE = PAAF.PROBATION_UNIT AND p_eff_date between nvl(start_date_active,p_eff_date)
361 and nvl(end_date_active,p_eff_Date)
362 and enabled_flag = 'Y') PROBATION_UNIT_DESC
363 , SAL_REVIEW_PERIOD
364 , SAL_REVIEW_PERIOD_FREQUENCY
365 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
366 AND LOOKUP_CODE = PAAF.SAL_REVIEW_PERIOD_FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
367 and nvl(end_date_active,p_eff_Date)
368 and enabled_flag = 'Y')
369 SAL_RP_FREQUENCY_DESC -- Too many values
370 , SET_OF_BOOKS_ID
371 , SOURCE_TYPE
372 , TIME_NORMAL_FINISH
373 , TIME_NORMAL_START
374 , BARGAINING_UNIT_CODE
375 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'BARGAINING_UNIT_CODE'
376 AND LOOKUP_CODE = PAAF.BARGAINING_UNIT_CODE AND p_eff_date between nvl(start_date_active,p_eff_date)
377 and nvl(end_date_active,p_eff_Date)
378 and enabled_flag = 'Y')
379 BARGAINING_UNIT_DESC
380 , LABOUR_UNION_MEMBER_FLAG
381 , HOURLY_SALARIED_CODE
382 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'HOURLY_SALARIED_CODE'
383 AND LOOKUP_CODE = PAAF.HOURLY_SALARIED_CODE AND p_eff_date between nvl(start_date_active,p_eff_date)
384 and nvl(end_date_active,p_eff_Date)
385 and enabled_flag = 'Y')
386 HOURLY_SALARIED_DESC
387 , CONTRACT_ID
388 , COLLECTIVE_AGREEMENT_ID
389 , (SELECT NAME FROM PER_COLLECTIVE_AGREEMENTS PCA WHERE PCA.COLLECTIVE_AGREEMENT_ID = PAAF.COLLECTIVE_AGREEMENT_ID) COLLECTIVE_AGREEMENT_DESC
390 , CAGR_ID_FLEX_NUM
391 , CAGR_GRADE_DEF_ID
392 , ESTABLISHMENT_ID
393 , (SELECT NAME FROM HR_ESTABLISHMENTS_V HEV WHERE HEV.ESTABLISHMENT_ID = PAAF.ESTABLISHMENT_ID) ESTABLISHMENT_DESC
394 , ASS_ATTRIBUTE_CATEGORY
395 , ASS_ATTRIBUTE1
396 , ASS_ATTRIBUTE2
397 , ASS_ATTRIBUTE3
398 , ASS_ATTRIBUTE4
399 , ASS_ATTRIBUTE5
400 , ASS_ATTRIBUTE6
401 , ASS_ATTRIBUTE7
402 , ASS_ATTRIBUTE8
403 , ASS_ATTRIBUTE9
404 , ASS_ATTRIBUTE10
405 , ASS_ATTRIBUTE11
406 , ASS_ATTRIBUTE12
407 , ASS_ATTRIBUTE13
408 , ASS_ATTRIBUTE14
409 , ASS_ATTRIBUTE15
410 , ASS_ATTRIBUTE16
411 , ASS_ATTRIBUTE17
412 , ASS_ATTRIBUTE18
413 , ASS_ATTRIBUTE19
414 , ASS_ATTRIBUTE20
415 , ASS_ATTRIBUTE21
416 , ASS_ATTRIBUTE22
417 , ASS_ATTRIBUTE23
418 , ASS_ATTRIBUTE24
419 , ASS_ATTRIBUTE25
420 , ASS_ATTRIBUTE26
421 , ASS_ATTRIBUTE27
422 , ASS_ATTRIBUTE28
423 , ASS_ATTRIBUTE29
424 , ASS_ATTRIBUTE30
425 , TITLE
426 , NOTICE_PERIOD
427 , NOTICE_PERIOD_UOM
428 , EMPLOYEE_CATEGORY
429 , (select meaning from HR_LOOKUPS where lookup_type = 'EMPLOYEE_CATG'
430 AND LOOKUP_CODE = PAAF.EMPLOYEE_CATEGORY AND p_eff_date between nvl(start_date_active,p_eff_date)
431 and nvl(end_date_active,p_eff_Date)
432 and enabled_flag = 'Y') EMPLOYEE_CATEGORY_DESC
433 , WORK_AT_HOME
434 , JOB_POST_SOURCE_NAME
435 , '' POSTING_CONTENT_ID -- column not exist in PER_ASSIGNMENTS_F2
436 , to_char(PERIOD_OF_PLACEMENT_DATE_START,'YYYY-MM-DD')
437 , VENDOR_ID
438 , VENDOR_EMPLOYEE_NUMBER
439 , VENDOR_ASSIGNMENT_NUMBER
440 , ASSIGNMENT_CATEGORY
441 , PROJECT_TITLE
442 , '' APPLICANT_RANK -- column not exist in PER_ASSIGNMENTS_F2
443 , VENDOR_SITE_ID
444 , PO_HEADER_ID
445 , PO_LINE_ID
446 , PROJECTED_ASSIGNMENT_END
447 , GRADE_LADDER_PGM_ID
448 , '' GRADE_LADDER_PGM_NAME
449 , SUPERVISOR_ASSIGNMENT_ID
450 FROM PER_ASSIGNMENTS_F2 paaf,
451 PER_ASSIGNMENT_STATUS_TYPES ST ,
452 PER_ASSIGNMENT_STATUS_TYPES_TL STTL
453 WHERE paaf.person_id = p_person_id
454 AND paaf.business_group_id = p_business_group_id
455 AND paaf.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
456 AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
457 AND STTL.LANGUAGE = USERENV('LANG')
458 and p_eff_date between EFFECTIVE_START_DATE AND nvl(EFFECTIVE_END_DATE,to_date('31/12/4712','DD/MM/YYYY'));
459
460 --Cursor to fetch the assignment location address
461 CURSOR csr_asg_loc_addr(p_location_id number) is
462 SELECT LOCATION_ID,
463 STYLE,
464 ( SELECT descriptive_flex_context_name
465 from fnd_descr_flex_contexts_vl
466 where descriptive_flexfield_name ='Address Structure'
467 AND descriptive_flex_context_code = STYLE) STYLE_DESC,
468 COUNTRY,
469 (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC,
470 ADDRESS_LINE_1,
471 ADDRESS_LINE_2,
472 ADDRESS_LINE_3,
473 TOWN_OR_CITY,
474 REGION_1,
475 REGION_2,
476 REGION_3,
477 POSTAL_CODE,
478 TELEPHONE_NUMBER_1,
479 TELEPHONE_NUMBER_2,
480 TELEPHONE_NUMBER_3,
481 LOC_INFORMATION13,
482 LOC_INFORMATION14,
483 LOC_INFORMATION15,
484 LOC_INFORMATION16,
485 LOC_INFORMATION17,
486 LOC_INFORMATION18,
487 LOC_INFORMATION19,
488 LOC_INFORMATION20
489 FROM HR_LOCATIONS_ALL
490 WHERE LOCATION_ID = P_LOCATION_ID;
491
492
493 -- cursor to fetch the contact information
494 CURSOR csr_contact_record(p_person_id number,p_business_group_id number,p_eff_date date) is
495 SELECT CONTACT_RELATIONSHIP_ID
496 , CONTACT_PERSON_ID
497 , (SELECT FULL_NAME FROM PER_PEOPLE_F
498 WHERE PERSON_ID = CONTACT_PERSON_ID
499 AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
500 AND P_EFF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) CONTACT_FULL_NAME
501 , CONTACT_TYPE
502 , (SELECT MEANING FROM hr_leg_lookups WHERE LOOKUP_TYPE='CONTACT'
503 AND LOOKUP_CODE= CONTACT_TYPE ) CONTACT_TYPE_DESC
504 , PRIMARY_CONTACT_FLAG
505 , to_char(DATE_START,'YYYY-MM-DD')
506 , START_LIFE_REASON_ID
507 , to_char(DATE_END,'YYYY-MM-DD')
508 , END_LIFE_REASON_ID
509 , RLTD_PER_RSDS_W_DSGNTR_FLAG
510 , PERSONAL_FLAG
511 , SEQUENCE_NUMBER
512 , CONT_ATTRIBUTE_CATEGORY
513 , CONT_ATTRIBUTE1
514 , CONT_ATTRIBUTE2
515 , CONT_ATTRIBUTE3
516 , CONT_ATTRIBUTE4
517 , CONT_ATTRIBUTE5
518 , CONT_ATTRIBUTE6
519 , CONT_ATTRIBUTE7
520 , CONT_ATTRIBUTE8
521 , CONT_ATTRIBUTE9
522 , CONT_ATTRIBUTE10
523 , CONT_ATTRIBUTE11
524 , CONT_ATTRIBUTE12
525 , CONT_ATTRIBUTE13
526 , CONT_ATTRIBUTE14
527 , CONT_ATTRIBUTE15
528 , CONT_ATTRIBUTE16
529 , CONT_ATTRIBUTE17
530 , CONT_ATTRIBUTE18
531 , CONT_ATTRIBUTE19
532 , CONT_ATTRIBUTE20
533 , THIRD_PARTY_PAY_FLAG
534 , BONDHOLDER_FLAG
535 , DEPENDENT_FLAG
536 , BENEFICIARY_FLAG
537 , PARTY_ID
538 , CONT_INFORMATION_CATEGORY
539 , CONT_INFORMATION1
540 , CONT_INFORMATION2
541 , CONT_INFORMATION3
542 , CONT_INFORMATION4
543 , CONT_INFORMATION5
544 , CONT_INFORMATION6
545 , CONT_INFORMATION7
546 , CONT_INFORMATION8
547 , CONT_INFORMATION9
548 , CONT_INFORMATION10
549 , CONT_INFORMATION11
550 , CONT_INFORMATION12
551 , CONT_INFORMATION13
552 , CONT_INFORMATION14
553 , CONT_INFORMATION15
554 , CONT_INFORMATION16
555 , CONT_INFORMATION17
556 , CONT_INFORMATION18
557 , CONT_INFORMATION19
558 , CONT_INFORMATION20
559 FROM PER_CONTACT_RELATIONSHIPS PCR
560 where PCR.PERSON_ID = p_person_id
561 and PCR.business_group_id = p_business_group_id
562 and p_eff_date between DATE_START and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY') );
563
564
565 --Cursor to fetch the contact address
566
567 CURSOR csr_cont_address(p_person_id number,p_business_group_id number,p_eff_date date) is
568 SELECT CONTACT_RELATIONSHIP_ID
569 , CONTACT_PERSON_ID
570 , ADDRESS_ID
571 , to_char(DATE_FROM,'YYYY-MM-DD')
572 , PRIMARY_FLAG
573 , STYLE
574 , ( SELECT descriptive_flex_context_name
575 from fnd_descr_flex_contexts_vl
576 where descriptive_flexfield_name ='Address Structure'
577 AND descriptive_flex_context_code = STYLE) STYLE_DESC
578 , ADDRESS_LINE1
579 , ADDRESS_LINE2
580 , ADDRESS_LINE3
581 , ADDRESS_TYPE
582 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'ADDRESS_TYPE' AND LOOKUP_CODE = ADDRESS_TYPE
583 AND p_eff_date between nvl(start_date_active,p_eff_date)
584 and nvl(end_date_active,p_eff_Date)
585 and enabled_flag = 'Y') ADDRESS_TYPE_DESC
586 , COUNTRY
587 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC
588 , to_char(DATE_TO,'YYYY-MM-DD')
589 , POSTAL_CODE
590 , REGION_1
591 , 'REGION_1' REGION_1_DESC
592 , REGION_2
593 , 'REGION_2' REGION_2_DESC
594 , REGION_3
595 , 'REGION_3' REGION_3_DESC
596 , TELEPHONE_NUMBER_1
597 , TELEPHONE_NUMBER_2
598 , TELEPHONE_NUMBER_3
599 , TOWN_OR_CITY
600 , 'TOWN_OR_CITY' TOWN_OR_CITY_DESC
601 , ADDR_ATTRIBUTE_CATEGORY
602 , ADDR_ATTRIBUTE1
603 , ADDR_ATTRIBUTE2
604 , ADDR_ATTRIBUTE3
605 , ADDR_ATTRIBUTE4
606 , ADDR_ATTRIBUTE5
607 , ADDR_ATTRIBUTE6
608 , ADDR_ATTRIBUTE7
609 , ADDR_ATTRIBUTE8
610 , ADDR_ATTRIBUTE9
611 , ADDR_ATTRIBUTE10
612 , ADDR_ATTRIBUTE11
613 , ADDR_ATTRIBUTE12
614 , ADDR_ATTRIBUTE13
615 , ADDR_ATTRIBUTE14
616 , ADDR_ATTRIBUTE15
617 , ADDR_ATTRIBUTE16
618 , ADDR_ATTRIBUTE17
619 , ADDR_ATTRIBUTE18
620 , ADDR_ATTRIBUTE19
621 , ADDR_ATTRIBUTE20
622 , ADD_INFORMATION13
623 , ADD_INFORMATION14
624 , ADD_INFORMATION15
625 , ADD_INFORMATION16
626 , ADD_INFORMATION17
627 , ADD_INFORMATION18
628 , ADD_INFORMATION19
629 , ADD_INFORMATION20
630 , PA.PARTY_ID
631 , DERIVED_LOCALE
632 , GEOMETRY
633 , PA.COMMENTS
634 FROM PER_ADDRESSES PA, PER_CONTACT_RELATIONSHIPS PCR
635 WHERE PCR.PERSON_ID = p_person_id
636 AND PCR.CONTACT_PERSON_ID = PA.PERSON_ID
637 and p_eff_date between PCR.DATE_START and nvl(PCR.date_end,to_date('31/12/4712','DD/MM/YYYY'))
638 and p_eff_date between pa.date_from and nvl(pa.date_to,to_date('31/12/4712','DD/MM/YYYY'));
639
640
641 -- cursor to fetch the address record
642 CURSOR csr_address_record(p_person_id number,p_business_group_id number,p_eff_date date) is SELECT ADDRESS_ID
643 , to_char(DATE_FROM,'YYYY-MM-DD')
644 , PRIMARY_FLAG
645 , STYLE
646 , ( SELECT descriptive_flex_context_name
647 from fnd_descr_flex_contexts_vl
648 where descriptive_flexfield_name ='Address Structure'
649 AND descriptive_flex_context_code = STYLE) STYLE_DESC
650 , ADDRESS_LINE1
651 , ADDRESS_LINE2
652 , ADDRESS_LINE3
653 , ADDRESS_TYPE
654 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'ADDRESS_TYPE' AND LOOKUP_CODE = ADDRESS_TYPE
655 AND p_eff_date between nvl(start_date_active,p_eff_date)
656 and nvl(end_date_active,p_eff_Date)
657 and enabled_flag = 'Y') ADDRESS_TYPE_DESC
658 , COUNTRY
659 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC
660 , to_char(DATE_TO,'YYYY-MM-DD')
661 , POSTAL_CODE
662 , REGION_1
663 , 'REGION_1' REGION_1_DESC
664 , REGION_2
665 , 'REGION_2' REGION_2_DESC
666 , REGION_3
667 , 'REGION_3' REGION_3_DESC
668 , TELEPHONE_NUMBER_1
669 , TELEPHONE_NUMBER_2
670 , TELEPHONE_NUMBER_3
671 , TOWN_OR_CITY
672 , 'TOWN_OR_CITY' TOWN_OR_CITY_DESC
673 , ADDR_ATTRIBUTE_CATEGORY
674 , ADDR_ATTRIBUTE1
675 , ADDR_ATTRIBUTE2
676 , ADDR_ATTRIBUTE3
677 , ADDR_ATTRIBUTE4
678 , ADDR_ATTRIBUTE5
679 , ADDR_ATTRIBUTE6
680 , ADDR_ATTRIBUTE7
681 , ADDR_ATTRIBUTE8
682 , ADDR_ATTRIBUTE9
683 , ADDR_ATTRIBUTE10
684 , ADDR_ATTRIBUTE11
685 , ADDR_ATTRIBUTE12
686 , ADDR_ATTRIBUTE13
687 , ADDR_ATTRIBUTE14
688 , ADDR_ATTRIBUTE15
689 , ADDR_ATTRIBUTE16
690 , ADDR_ATTRIBUTE17
691 , ADDR_ATTRIBUTE18
692 , ADDR_ATTRIBUTE19
693 , ADDR_ATTRIBUTE20
694 , ADD_INFORMATION13
695 , ADD_INFORMATION14
696 , ADD_INFORMATION15
697 , ADD_INFORMATION16
698 , ADD_INFORMATION17
699 , ADD_INFORMATION18
700 , ADD_INFORMATION19
701 , ADD_INFORMATION20
702 , PARTY_ID
703 , DERIVED_LOCALE
704 , GEOMETRY
705 , COMMENTS
706 FROM PER_ADDRESSES PA
707 WHERE PA.PERSON_ID = p_person_id
708 and p_eff_date between pa.date_from and nvl(pa.date_to,to_date('31/12/4712','DD/MM/YYYY'));
709
710 -- cursor to fetch the person extra information record
711 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
712 , PEI.INFORMATION_TYPE
713 , PEI.PEI_ATTRIBUTE_CATEGORY
714 , PEI.PEI_ATTRIBUTE1
715 , PEI.PEI_ATTRIBUTE2
716 , PEI.PEI_ATTRIBUTE3
717 , PEI.PEI_ATTRIBUTE4
718 , PEI.PEI_ATTRIBUTE5
719 , PEI.PEI_ATTRIBUTE6
720 , PEI.PEI_ATTRIBUTE7
721 , PEI.PEI_ATTRIBUTE8
722 , PEI.PEI_ATTRIBUTE9
723 , PEI.PEI_ATTRIBUTE10
724 , PEI.PEI_ATTRIBUTE11
725 , PEI.PEI_ATTRIBUTE12
726 , PEI.PEI_ATTRIBUTE13
727 , PEI.PEI_ATTRIBUTE14
728 , PEI.PEI_ATTRIBUTE15
729 , PEI.PEI_ATTRIBUTE16
730 , PEI.PEI_ATTRIBUTE17
731 , PEI.PEI_ATTRIBUTE18
732 , PEI.PEI_ATTRIBUTE19
733 , PEI.PEI_ATTRIBUTE20
734 , PEI.PEI_INFORMATION_CATEGORY
735 , PEI.PEI_INFORMATION1
736 , PEI.PEI_INFORMATION2
737 , PEI.PEI_INFORMATION3
738 , PEI.PEI_INFORMATION4
739 , PEI.PEI_INFORMATION5
740 , PEI.PEI_INFORMATION6
741 , PEI.PEI_INFORMATION7
742 , PEI.PEI_INFORMATION8
743 , PEI.PEI_INFORMATION9
744 , PEI.PEI_INFORMATION10
745 , PEI.PEI_INFORMATION11
746 , PEI.PEI_INFORMATION12
747 , PEI.PEI_INFORMATION13
748 , PEI.PEI_INFORMATION14
749 , PEI.PEI_INFORMATION15
750 , PEI.PEI_INFORMATION16
751 , PEI.PEI_INFORMATION17
752 , PEI.PEI_INFORMATION18
753 , PEI.PEI_INFORMATION19
754 , PEI.PEI_INFORMATION20
755 , PEI.PEI_INFORMATION21
756 , PEI.PEI_INFORMATION22
757 , PEI.PEI_INFORMATION23
758 , PEI.PEI_INFORMATION24
759 , PEI.PEI_INFORMATION25
760 , PEI.PEI_INFORMATION26
761 , PEI.PEI_INFORMATION27
762 , PEI.PEI_INFORMATION28
763 , PEI.PEI_INFORMATION29
764 , PEI.PEI_INFORMATION30
765 , PEI.PARTY_ID
766 FROM PER_PEOPLE_EXTRA_INFO PEI
767 where PEI.person_id = p_person_id;
768
769 -- cursor to fetch the special information record
770 CURSOR csr_special_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
771 SELECT PERSON_ANALYSIS_ID
772 , ppa.ANALYSIS_CRITERIA_ID
773 , to_char(DATE_FROM,'YYYY-MM-DD')
774 , to_char(DATE_TO,'YYYY-MM-DD')
775 , ppa.ID_FLEX_NUM
776 , SEGMENT1
777 , SEGMENT2
778 , SEGMENT3
779 , SEGMENT4
780 , SEGMENT5
781 , SEGMENT6
782 , SEGMENT7
783 , SEGMENT8
784 , SEGMENT9
785 , SEGMENT10
786 , SEGMENT11
787 , SEGMENT12
788 , SEGMENT13
789 , SEGMENT14
790 , SEGMENT15
791 , SEGMENT16
792 , SEGMENT17
793 , SEGMENT18
794 , SEGMENT19
795 , SEGMENT20
796 , SEGMENT21
797 , SEGMENT22
798 , SEGMENT23
799 , SEGMENT24
800 , SEGMENT25
801 , SEGMENT26
802 , SEGMENT27
803 , SEGMENT28
804 , SEGMENT29
805 , SEGMENT30
806 , ATTRIBUTE_CATEGORY
807 , ATTRIBUTE1
808 , ATTRIBUTE2
809 , ATTRIBUTE3
810 , ATTRIBUTE4
811 , ATTRIBUTE5
812 , ATTRIBUTE6
813 , ATTRIBUTE7
814 , ATTRIBUTE8
815 , ATTRIBUTE9
816 , ATTRIBUTE10
817 , ATTRIBUTE11
818 , ATTRIBUTE12
819 , ATTRIBUTE13
820 , ATTRIBUTE14
821 , ATTRIBUTE15
822 , ATTRIBUTE16
823 , ATTRIBUTE17
824 , ATTRIBUTE18
825 , ATTRIBUTE19
826 , ATTRIBUTE20
827 , PARTY_ID
828 , COMMENTS
829 FROM PER_PERSON_ANALYSES ppa,PER_ANALYSIS_CRITERIA pac
830 where ppa.person_id = p_person_id
831 and ppa.business_group_id = p_business_group_id
832 and ppa.analysis_criteria_id = pac.analysis_criteria_id
833 and ppa.id_flex_num = pac.id_flex_num
834 and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
835
836 -- cursor to fetch the previous employment
837 CURSOR csr_prev_emp_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
838 SELECT PREVIOUS_EMPLOYER_ID
839 , PARTY_ID
840 , to_char(START_DATE,'YYYY-MM-DD')
841 , to_char(END_DATE,'YYYY-MM-DD')
842 , PERIOD_YEARS
843 , PERIOD_DAYS
844 , EMPLOYER_NAME
845 , EMPLOYER_COUNTRY
846 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = EMPLOYER_COUNTRY) EMPLOYER_COUNTRY_DESC
847 , EMPLOYER_ADDRESS
848 , EMPLOYER_TYPE
849 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'PREV_EMP_TYPE' AND LOOKUP_CODE = EMPLOYER_TYPE
850 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
851 AND ENABLED_FLAG = 'Y') EMPLOYER_TYPE_DESC
852 , EMPLOYER_SUBTYPE
853 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'PREV_EMP_SUBTYPE' AND LOOKUP_CODE = EMPLOYER_SUBTYPE
854 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
855 AND ENABLED_FLAG = 'Y') EMPLOYER_SUBTYPE_DESC
856 , DESCRIPTION
857 , PEM_ATTRIBUTE_CATEGORY
858 , PEM_ATTRIBUTE1
859 , PEM_ATTRIBUTE2
860 , PEM_ATTRIBUTE3
861 , PEM_ATTRIBUTE4
862 , PEM_ATTRIBUTE5
863 , PEM_ATTRIBUTE6
864 , PEM_ATTRIBUTE7
865 , PEM_ATTRIBUTE8
866 , PEM_ATTRIBUTE9
867 , PEM_ATTRIBUTE10
868 , PEM_ATTRIBUTE11
869 , PEM_ATTRIBUTE12
870 , PEM_ATTRIBUTE13
871 , PEM_ATTRIBUTE14
872 , PEM_ATTRIBUTE15
873 , PEM_ATTRIBUTE16
874 , PEM_ATTRIBUTE17
875 , PEM_ATTRIBUTE18
876 , PEM_ATTRIBUTE19
877 , PEM_ATTRIBUTE20
878 , PEM_ATTRIBUTE21
879 , PEM_ATTRIBUTE22
880 , PEM_ATTRIBUTE23
881 , PEM_ATTRIBUTE24
882 , PEM_ATTRIBUTE25
883 , PEM_ATTRIBUTE26
884 , PEM_ATTRIBUTE27
885 , PEM_ATTRIBUTE28
886 , PEM_ATTRIBUTE29
887 , PEM_ATTRIBUTE30
888 , PEM_INFORMATION_CATEGORY
889 , PEM_INFORMATION1
890 , PEM_INFORMATION2
891 , PEM_INFORMATION3
892 , PEM_INFORMATION4
893 , PEM_INFORMATION5
894 , PEM_INFORMATION6
895 , PEM_INFORMATION7
896 , PEM_INFORMATION8
897 , PEM_INFORMATION9
898 , PEM_INFORMATION10
899 , PEM_INFORMATION11
900 , PEM_INFORMATION12
901 , PEM_INFORMATION13
902 , PEM_INFORMATION14
903 , PEM_INFORMATION15
904 , PEM_INFORMATION16
905 , PEM_INFORMATION17
906 , PEM_INFORMATION18
907 , PEM_INFORMATION19
908 , PEM_INFORMATION20
909 , PEM_INFORMATION21
910 , PEM_INFORMATION22
911 , PEM_INFORMATION23
912 , PEM_INFORMATION24
913 , PEM_INFORMATION25
914 , PEM_INFORMATION26
915 , PEM_INFORMATION27
916 , PEM_INFORMATION28
917 , PEM_INFORMATION29
918 , PEM_INFORMATION30
919 , ALL_ASSIGNMENTS
920 , PERIOD_MONTHS
921 FROM PER_PREVIOUS_EMPLOYERS
922 WHERE PERSON_ID = p_person_id
923 and business_group_id = p_business_group_id
924 and p_eff_date between start_date and nvl(end_date,to_date('31/12/4712','DD/MM/YYYY'));
925
926 -- cursor to fetch the work incidents details
927 CURSOR CSR_WORK_INC_INFO_RECORD(p_person_id number,p_business_group_id number,p_eff_date date) IS
928 SELECT INCIDENT_ID
929 , INCIDENT_REFERENCE
930 , INCIDENT_TYPE
931 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'INCIDENT_TYPE' AND LOOKUP_CODE = INCIDENT_TYPE
932 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
933 AND ENABLED_FLAG = 'Y') INCIDENT_TYPE_DESC
934 , to_char(INCIDENT_DATE,'YYYY-MM-DD')
935 , INCIDENT_TIME
936 , ASSIGNMENT_ID
937 , LOCATION
938 , AT_WORK_FLAG
939 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'AT_WORK_FLAG' AND LOOKUP_CODE = AT_WORK_FLAG
940 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
941 AND ENABLED_FLAG = 'Y') AT_WORK_FLAG_DESC
942 --, to_char(LAST_WORK_DATE,'YYYY-MM-DD') -- comented for bug 8905622
943 --, LAST_WORK_TIME -- comented for bug 8905622
944 , to_char(REPORT_DATE,'YYYY-MM-DD')
945 , REPORT_TIME
946 , REPORT_METHOD
947 , PERSON_REPORTED_BY
948 , (SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PERSON_REPORTED_BY
949 and p_eff_date between papf.effective_start_date and
950 nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) PERSON_REPORTED_BY_NAME
951 , PERSON_REPORTED_TO
952 , WITNESS_DETAILS
953 , DESCRIPTION
954 , INJURY_TYPE
955 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'INJURY_TYPE' AND LOOKUP_CODE = INJURY_TYPE
956 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
957 AND ENABLED_FLAG = 'Y') INJURY_TYPE_DESC
958 , DISEASE_TYPE
959 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'DISEASE_TYPE' AND LOOKUP_CODE = DISEASE_TYPE
960 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
961 AND ENABLED_FLAG = 'Y') DISEASE_TYPE_DESC
962 , HAZARD_TYPE
963 , (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'HAZARD_TYPE' AND LOOKUP_CODE = HAZARD_TYPE
964 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
965 AND ENABLED_FLAG = 'Y') HAZARD_TYPE_DESC
966 , BODY_PART
967 , TREATMENT_RECEIVED_FLAG
968 , HOSPITAL_DETAILS
969 --, DOCTOR_ID -- comented for bug 8905622
970 --, NEXT_OF_KIN_ID -- comented for bug 8905622
971 --, ABSENCE_ID -- comented for bug 8905622
972 , to_char(COMPENSATION_DATE,'YYYY-MM-DD')
973 , COMPENSATION_CURRENCY
974 , COMPENSATION_AMOUNT
975 , REMEDIAL_HS_ACTION
976 , NOTIFIED_HSREP_ID
977 , to_char(NOTIFIED_HSREP_DATE,'YYYY-MM-DD')
978 /* Bug 11792686
979 , NOTIFIED_UREP_ID
980 , to_char(NOTIFIED_UREP_DATE,'YYYY-MM-DD')
981 , PREVIOUS_INCIDENT_ID */
982
983 , ATTRIBUTE_CATEGORY
984 , ATTRIBUTE1
985 , ATTRIBUTE2
986 , ATTRIBUTE3
987 , ATTRIBUTE4
988 , ATTRIBUTE5
989 , ATTRIBUTE6
990 , ATTRIBUTE7
991 , ATTRIBUTE8
992 , ATTRIBUTE9
993 , ATTRIBUTE10
994 , ATTRIBUTE11
995 , ATTRIBUTE12
996 , ATTRIBUTE13
997 , ATTRIBUTE14
998 , ATTRIBUTE15
999 , ATTRIBUTE16
1000 , ATTRIBUTE17
1001 , ATTRIBUTE18
1002 , ATTRIBUTE19
1003 , ATTRIBUTE20
1004 , ATTRIBUTE21
1005 , ATTRIBUTE22
1006 , ATTRIBUTE23
1007 , ATTRIBUTE24
1008 , ATTRIBUTE25
1009 , ATTRIBUTE26
1010 , ATTRIBUTE27
1011 , ATTRIBUTE28
1012 , ATTRIBUTE29
1013 , ATTRIBUTE30
1014 , INC_INFORMATION_CATEGORY
1015 , INC_INFORMATION1
1016 , INC_INFORMATION2
1017 , INC_INFORMATION3
1018 , INC_INFORMATION4
1019 , INC_INFORMATION5
1020 , INC_INFORMATION6
1021 , INC_INFORMATION7
1022 , INC_INFORMATION8
1023 , INC_INFORMATION9
1024 , INC_INFORMATION10
1025 , INC_INFORMATION11
1026 , INC_INFORMATION12
1027 , INC_INFORMATION13
1028 , INC_INFORMATION14
1029 , INC_INFORMATION15
1030 , INC_INFORMATION16
1031 , INC_INFORMATION17
1032 , INC_INFORMATION18
1033 , INC_INFORMATION19
1034 , INC_INFORMATION20
1035 , INC_INFORMATION21
1036 , INC_INFORMATION22
1037 , INC_INFORMATION23
1038 , INC_INFORMATION24
1039 , INC_INFORMATION25
1040 , INC_INFORMATION26
1041 , INC_INFORMATION27
1042 , INC_INFORMATION28
1043 , INC_INFORMATION29
1044 , INC_INFORMATION30
1045 , to_char(ORG_NOTIFIED_DATE,'YYYY-MM-DD')
1046 , DOCTOR_NAME
1047 , NOTIFIED_REP_ID
1048 , to_char(NOTIFIED_REP_DATE,'YYYY-MM-DD')
1049 , NOTIFIED_REP_ORG_ID
1050 , RELATED_INCIDENT_ID
1051 , OVER_TIME_FLAG
1052 , ABSENCE_EXISTS_FLAG
1053 , EMERGENCY_CODE
1054 , PRIVACY_ISSUE
1055 , OBJECTS_INVOLVED
1056 , ACTIVITY_AT_TIME_OF_WORK
1057 , HOSPITAL_ADDRESS
1058 , DAYS_RESTRICTED_WORK
1059 , HOSPITALIZED_FLAG
1060 , to_char(DATE_OF_DEATH,'YYYY-MM-DD')
1061 , DAYS_AWAY_FROM_WORK
1062 , WORK_START_TIME
1063 , REPORTING_PERSON_PHONE
1064 , REPORTING_PERSON_TITLE
1065 , REPORT_COMPLETED_BY
1066 FROM PER_WORK_INCIDENTS
1067 WHERE PERSON_ID = p_person_id;
1068
1069
1070 -- cursor to fetch the deilvery methode
1071 CURSOR csr_deliv_method_info(p_person_id number,p_business_group_id number,p_eff_date date) is
1072 SELECT DELIVERY_METHOD_ID
1073 , to_char(DATE_START,'YYYY-MM-DD')
1074 , to_char(DATE_END,'YYYY-MM-DD')
1075 , COMM_DLVRY_METHOD
1076 , (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_CM_MTHD' AND LOOKUP_CODE = COMM_DLVRY_METHOD
1077 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1078 AND ENABLED_FLAG = 'Y') COMM_DLVRY_METHOD_DESC
1079 , PREFERRED_FLAG
1080 , ATTRIBUTE_CATEGORY
1081 , ATTRIBUTE1
1082 , ATTRIBUTE2
1083 , ATTRIBUTE3
1084 , ATTRIBUTE4
1085 , ATTRIBUTE5
1086 , ATTRIBUTE6
1087 , ATTRIBUTE7
1088 , ATTRIBUTE8
1089 , ATTRIBUTE9
1090 , ATTRIBUTE10
1091 , ATTRIBUTE11
1092 , ATTRIBUTE12
1093 , ATTRIBUTE13
1094 , ATTRIBUTE14
1095 , ATTRIBUTE15
1096 , ATTRIBUTE16
1097 , ATTRIBUTE17
1098 , ATTRIBUTE18
1099 , ATTRIBUTE19
1100 , ATTRIBUTE20
1101 FROM PER_PERSON_DLVRY_METHODS
1102 where person_id = p_person_id;
1103
1104 -- cursor to fetch the assignment extra information
1105 CURSOR csr_ass_extra_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
1106 SELECT ASSIGNMENT_EXTRA_INFO_ID
1107 , ASSIGNMENT_ID
1108 , INFORMATION_TYPE
1109 , AEI_ATTRIBUTE_CATEGORY
1110 , AEI_ATTRIBUTE1
1111 , AEI_ATTRIBUTE2
1112 , AEI_ATTRIBUTE3
1113 , AEI_ATTRIBUTE4
1114 , AEI_ATTRIBUTE5
1115 , AEI_ATTRIBUTE6
1116 , AEI_ATTRIBUTE7
1117 , AEI_ATTRIBUTE8
1118 , AEI_ATTRIBUTE9
1119 , AEI_ATTRIBUTE10
1120 , AEI_ATTRIBUTE11
1121 , AEI_ATTRIBUTE12
1122 , AEI_ATTRIBUTE13
1123 , AEI_ATTRIBUTE14
1124 , AEI_ATTRIBUTE15
1125 , AEI_ATTRIBUTE16
1126 , AEI_ATTRIBUTE17
1127 , AEI_ATTRIBUTE18
1128 , AEI_ATTRIBUTE19
1129 , AEI_ATTRIBUTE20
1130 , AEI_INFORMATION_CATEGORY
1131 , AEI_INFORMATION1
1132 , AEI_INFORMATION2
1133 , AEI_INFORMATION3
1134 , AEI_INFORMATION4
1135 , AEI_INFORMATION5
1136 , AEI_INFORMATION6
1137 , AEI_INFORMATION7
1138 , AEI_INFORMATION8
1139 , AEI_INFORMATION9
1140 , AEI_INFORMATION10
1141 , AEI_INFORMATION11
1142 , AEI_INFORMATION12
1143 , AEI_INFORMATION13
1144 , AEI_INFORMATION14
1145 , AEI_INFORMATION15
1146 , AEI_INFORMATION16
1147 , AEI_INFORMATION17
1148 , AEI_INFORMATION18
1149 , AEI_INFORMATION19
1150 , AEI_INFORMATION20
1151 , AEI_INFORMATION21
1152 , AEI_INFORMATION22
1153 , AEI_INFORMATION23
1154 , AEI_INFORMATION24
1155 , AEI_INFORMATION25
1156 , AEI_INFORMATION26
1157 , AEI_INFORMATION27
1158 , AEI_INFORMATION28
1159 , AEI_INFORMATION29
1160 , AEI_INFORMATION30
1161 FROM PER_ASSIGNMENT_EXTRA_INFO
1162 WHERE assignment_id in (SELECT assignment_id from PER_ASSIGNMENTS_F
1163 WHERE person_id = p_person_id
1164 and business_group_id = p_business_group_id
1165 and p_eff_date between effective_start_date and nvl(effective_end_date,to_date('31/12/4712','Dd/MM/YYYY')));
1166
1167 -- cursor to fetch the contact extra information
1168 CURSOR csr_con_extra_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is
1169 SELECT CONTACT_EXTRA_INFO_ID
1170 , to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD')
1171 , to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD')
1172 , CONTACT_RELATIONSHIP_ID
1173 , INFORMATION_TYPE
1174 , CEI_INFORMATION_CATEGORY
1175 , CEI_INFORMATION1
1176 , CEI_INFORMATION2
1177 , CEI_INFORMATION3
1178 , CEI_INFORMATION4
1179 , CEI_INFORMATION5
1180 , CEI_INFORMATION6
1181 , CEI_INFORMATION7
1182 , CEI_INFORMATION8
1183 , CEI_INFORMATION9
1184 , CEI_INFORMATION10
1185 , CEI_INFORMATION11
1186 , CEI_INFORMATION12
1187 , CEI_INFORMATION13
1188 , CEI_INFORMATION14
1189 , CEI_INFORMATION15
1190 , CEI_INFORMATION16
1191 , CEI_INFORMATION17
1192 , CEI_INFORMATION18
1193 , CEI_INFORMATION19
1194 , CEI_INFORMATION20
1195 , CEI_INFORMATION21
1196 , CEI_INFORMATION22
1197 , CEI_INFORMATION23
1198 , CEI_INFORMATION24
1199 , CEI_INFORMATION25
1200 , CEI_INFORMATION26
1201 , CEI_INFORMATION27
1202 , CEI_INFORMATION28
1203 , CEI_INFORMATION29
1204 , CEI_INFORMATION30
1205 , CEI_ATTRIBUTE_CATEGORY
1206 , CEI_ATTRIBUTE1
1207 , CEI_ATTRIBUTE2
1208 , CEI_ATTRIBUTE3
1209 , CEI_ATTRIBUTE4
1210 , CEI_ATTRIBUTE5
1211 , CEI_ATTRIBUTE6
1212 , CEI_ATTRIBUTE7
1213 , CEI_ATTRIBUTE8
1214 , CEI_ATTRIBUTE9
1215 , CEI_ATTRIBUTE10
1216 , CEI_ATTRIBUTE11
1217 , CEI_ATTRIBUTE12
1218 , CEI_ATTRIBUTE13
1219 , CEI_ATTRIBUTE14
1220 , CEI_ATTRIBUTE15
1221 , CEI_ATTRIBUTE16
1222 , CEI_ATTRIBUTE17
1223 , CEI_ATTRIBUTE18
1224 , CEI_ATTRIBUTE19
1225 , CEI_ATTRIBUTE20
1226 FROM PER_CONTACT_EXTRA_INFO_F
1227 WHERE CONTACT_RELATIONSHIP_ID in (SELECT PCR.CONTACT_RELATIONSHIP_ID
1228 FROM PER_CONTACT_RELATIONSHIPS PCR
1229 where PCR.PERSON_ID = p_person_id
1230 and PCR.business_group_id = p_business_group_id
1231 and p_eff_date between DATE_START and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY') ));
1232
1233 --Cursor to fetch the phone details
1234 CURSOR csr_phone_record (p_person_id number,p_business_group_id number,p_eff_date date) is
1235 SELECT
1236 PHONE_ID,
1237 to_char(DATE_FROM,'YYYY-MM-DD'),
1238 to_char(DATE_TO,'YYYY-MM-DD'),
1239 PHONE_TYPE,
1240 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PHONE_TYPE' AND LOOKUP_CODE = PHONE_TYPE
1241 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1242 AND ENABLED_FLAG = 'Y') COMM_DLVRY_METHOD_DESC,
1243 PHONE_NUMBER,
1244 ATTRIBUTE_CATEGORY,
1245 ATTRIBUTE1,
1246 ATTRIBUTE2,
1247 ATTRIBUTE3,
1248 ATTRIBUTE4,
1249 ATTRIBUTE5,
1250 ATTRIBUTE6,
1251 ATTRIBUTE7,
1252 ATTRIBUTE8,
1253 ATTRIBUTE9,
1254 ATTRIBUTE10,
1255 ATTRIBUTE11,
1256 ATTRIBUTE12,
1257 ATTRIBUTE13,
1258 ATTRIBUTE14,
1259 ATTRIBUTE15,
1260 ATTRIBUTE16,
1261 ATTRIBUTE17,
1262 ATTRIBUTE18,
1263 ATTRIBUTE19,
1264 ATTRIBUTE20,
1265 ATTRIBUTE21,
1266 ATTRIBUTE22,
1267 ATTRIBUTE23,
1268 ATTRIBUTE24,
1269 ATTRIBUTE25,
1270 ATTRIBUTE26,
1271 ATTRIBUTE27,
1272 ATTRIBUTE28,
1273 ATTRIBUTE29,
1274 ATTRIBUTE30,
1275 PARTY_ID,
1276 VALIDITY
1277 FROM PER_PHONES
1278 WHERE PARENT_ID = p_person_id
1279 AND PARENT_TABLE = 'PER_ALL_PEOPLE_F'
1280 and p_eff_date between DATE_FROM and nvl(DATE_TO,to_date('31/12/4712','DD/MM/YYYY'));
1281
1282 --cursor to fetch the qualification details
1283 CURSOR csr_qualification_record(p_person_id number,p_business_group_id number,p_eff_date date)
1284 is
1285 select QUALIFICATION_ID,
1286 TITLE,
1287 GRADE_ATTAINED,
1288 STATUS,
1289 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES' AND LOOKUP_CODE = STATUS
1290 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1291 AND ENABLED_FLAG = 'Y') STATUS_DESC,
1292 TO_CHAR(AWARDED_DATE,'YYYY-MM-DD'),
1293 FEE,
1294 FEE_CURRENCY,
1295 (SELECT name from fnd_currencies_vl WHERE
1296 CURRENCY_CODE = FEE_CURRENCY
1297 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1298 AND ENABLED_FLAG = 'Y') FEE_CURRENCY_DESC,
1299 TRAINING_COMPLETED_AMOUNT,
1300 REIMBURSEMENT_ARRANGEMENTS,
1301 TRAINING_COMPLETED_UNITS,
1302 TOTAL_TRAINING_AMOUNT,
1303 TO_CHAR(START_DATE,'YYYY-MM-DD'),
1304 TO_CHAR(END_DATE,'YYYY-MM-DD'),
1305 LICENSE_NUMBER,
1306 TO_CHAR(EXPIRY_DATE,'YYYY-MM-DD'),
1307 LICENSE_RESTRICTIONS,
1308 TO_CHAR(PROJECTED_COMPLETION_DATE,'YYYY-MM-DD'),
1309 AWARDING_BODY,
1310 TUITION_METHOD,
1311 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_TUITION_METHODS' AND LOOKUP_CODE = TUITION_METHOD
1312 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1313 AND ENABLED_FLAG = 'Y') TUITION_METHOD_DESC,
1314 GROUP_RANKING,
1315 COMMENTS,
1316 pq.QUALIFICATION_TYPE_ID,
1317 TL.name,
1318 pq.ATTENDANCE_ID,
1319 pq.ATTRIBUTE_CATEGORY,
1320 pq.ATTRIBUTE1,
1321 pq.ATTRIBUTE2,
1322 pq.ATTRIBUTE3,
1323 pq.ATTRIBUTE4,
1324 pq.ATTRIBUTE5,
1325 pq.ATTRIBUTE6,
1326 pq.ATTRIBUTE7,
1327 pq.ATTRIBUTE8,
1328 pq.ATTRIBUTE9,
1329 pq.ATTRIBUTE10,
1330 pq.ATTRIBUTE11,
1331 pq.ATTRIBUTE12,
1332 pq.ATTRIBUTE13,
1333 pq.ATTRIBUTE14,
1334 pq.ATTRIBUTE15,
1335 pq.ATTRIBUTE16,
1336 pq.ATTRIBUTE17,
1337 pq.ATTRIBUTE18,
1338 pq.ATTRIBUTE19,
1339 pq.ATTRIBUTE20,
1340 pq.PARTY_ID,
1341 PROFESSIONAL_BODY_NAME,
1342 MEMBERSHIP_NUMBER,
1343 MEMBERSHIP_CATEGORY,
1344 SUBSCRIPTION_PAYMENT_METHOD,
1345 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PQP_SUBSCRIPTION_PAY_METHODS' AND LOOKUP_CODE = SUBSCRIPTION_PAYMENT_METHOD
1346 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1347 AND ENABLED_FLAG = 'Y')SUBSCRIPTION_PAYMENT_DESC,
1348 QUA_INFORMATION_CATEGORY,
1349 QUA_INFORMATION1,
1350 QUA_INFORMATION2,
1351 QUA_INFORMATION3,
1352 QUA_INFORMATION4,
1353 QUA_INFORMATION5,
1354 QUA_INFORMATION6,
1355 QUA_INFORMATION7,
1356 QUA_INFORMATION8,
1357 QUA_INFORMATION9,
1358 QUA_INFORMATION10,
1359 QUA_INFORMATION11,
1360 QUA_INFORMATION12,
1361 QUA_INFORMATION13,
1362 QUA_INFORMATION14,
1363 QUA_INFORMATION15,
1364 QUA_INFORMATION16,
1365 QUA_INFORMATION17,
1366 QUA_INFORMATION18,
1367 QUA_INFORMATION19,
1368 QUA_INFORMATION20
1369 FROM
1370 PER_QUALIFICATIONS pq,per_qualification_types_tl tl,PER_ESTABLISHMENT_ATTENDANCES pea
1371 WHERE (pq.person_id = p_person_id or pea.person_id = p_person_id)
1372 and pq.business_group_id = p_business_group_id
1373 and pq.attendance_id = pea.attendance_id(+)
1374 and tl.qualification_type_id = pq.qualification_type_id
1375 and tl.language = userenv('LANG')
1376 and nvl(start_date,p_eff_date) <= p_eff_date;
1377 -- and p_eff_date between nvl(start_date,p_eff_date) and nvl(end_date,p_eff_date); commented for 7460407
1378
1379 --cursor to fetch the subject details for the qualification
1380 cursor csr_subject_record (p_qualification_id number,p_eff_date date)is
1381 SELECT SUBJECTS_TAKEN_ID,
1382 to_char(START_DATE,'YYYY-MM-DD'),
1383 MAJOR,
1384 SUBJECT_STATUS,
1385 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES' AND LOOKUP_CODE = SUBJECT_STATUS
1386 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1387 AND ENABLED_FLAG = 'Y')SUBJECT_STATUS_DESC,
1388 SUBJECT,
1389 (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECTS' AND LOOKUP_CODE = SUBJECT
1390 AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
1391 AND ENABLED_FLAG = 'Y')
1392 SUBJECT_DESC,
1393 GRADE_ATTAINED,
1394 to_char(END_DATE,'YYYY-MM-DD'),
1395 ATTRIBUTE_CATEGORY,
1396 ATTRIBUTE1,
1397 ATTRIBUTE2,
1398 ATTRIBUTE3,
1399 ATTRIBUTE4,
1400 ATTRIBUTE5,
1401 ATTRIBUTE6,
1402 ATTRIBUTE7,
1403 ATTRIBUTE8,
1404 ATTRIBUTE9,
1405 ATTRIBUTE10,
1406 ATTRIBUTE11,
1407 ATTRIBUTE12,
1408 ATTRIBUTE13,
1409 ATTRIBUTE14,
1410 ATTRIBUTE15,
1411 ATTRIBUTE16,
1412 ATTRIBUTE17,
1413 ATTRIBUTE18,
1414 ATTRIBUTE19,
1415 ATTRIBUTE20,
1416 SUB_INFORMATION_CATEGORY,
1417 SUB_INFORMATION1,
1418 SUB_INFORMATION2,
1419 SUB_INFORMATION3,
1420 SUB_INFORMATION4,
1421 SUB_INFORMATION5,
1422 SUB_INFORMATION6,
1423 SUB_INFORMATION7,
1424 SUB_INFORMATION8,
1425 SUB_INFORMATION9,
1426 SUB_INFORMATION10,
1427 SUB_INFORMATION11,
1428 SUB_INFORMATION12,
1429 SUB_INFORMATION13,
1430 SUB_INFORMATION14,
1431 SUB_INFORMATION15,
1432 SUB_INFORMATION16,
1433 SUB_INFORMATION17,
1434 SUB_INFORMATION18,
1435 SUB_INFORMATION19,
1436 SUB_INFORMATION20
1437 FROM PER_SUBJECTS_TAKEN
1438 WHERE qualification_id = p_qualification_id
1439 and nvl(start_date,p_eff_date) <= p_eff_date;
1440 --and p_eff_date between nvl(start_date,p_eff_date) and nvl(end_date,p_eff_date); commented for 7460407
1441
1442 --cursor to fetch the reportees details for the person
1443 cursor csr_reportee_record (p_person_id number,p_business_group_id number,p_eff_date date)
1444 is
1445 SELECT distinct ppf.full_name,
1446 ppf.person_id,
1447 employee_number ,
1448 ppf.npw_number,
1449 ppf.applicant_number,
1450 papf.location_id,
1451 (SELECT DESCRIPTION FROM HR_LOCATIONS_ALL_TL HLAT WHERE HLAT.LOCATION_ID = PAPF.LOCATION_ID
1452 AND LANGUAGE = USERENV('LANG')) REPORTEE_LOCATION_DESC,
1453 papf.assignment_status_type_id,
1454 (SELECT USER_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES_TL
1455 WHERE ASSIGNMENT_STATUS_TYPE_ID = papf.assignment_status_type_id
1456 AND LANGUAGE = USERENV('LANG')) REPORTEE_ASG_STATUS_DESC
1457 FROM per_assignments_f papf , per_people_f ppf
1458 WHERE
1459 p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
1460 and p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1461 and papf.business_group_id = P_BUSINESS_GROUP_ID
1462 and papf.assignment_type in ('A','E','C') -- added for bug8248111
1463 and papf.supervisor_id = P_PERSON_ID
1464 and papf.person_id=ppf.person_id;
1465
1466
1467 --Cursor to fetch Absence Details
1468 CURSOR csr_abs_details (p_person_id number,p_business_group_id number,p_eff_date date) is
1469 SELECT ACCRUAL_PLAN_ID,
1470 ACCRUAL_PLAN_NAME,
1471 ACCRUAL_UNITS_OF_MEASURE_NAME,
1472 ASSIGNMENT_ID,
1473 PAYROLL_ID
1474 FROM PAY_VIEW_ACCRUAL_PLANS_V
1475 WHERE PERSON_ID = p_person_id
1476 AND BUSINESS_GROUP_ID = p_business_group_id
1477 AND P_EFF_dATE BETWEEN ASG_EFFECTIVE_START_DATE AND ASG_EFFECTIVE_END_DATE;
1478
1479 l_start_date date := null;
1480 l_end_date date := null;
1481 l_accrual_end_date date;
1482 l_entitlement number := 0;
1483 l_accrual number := 0;
1484
1485
1486 -- Benefit cursors are removed.
1487
1488
1489 --Declaration for Payroll - Starts here
1490 p_leg_code varchar2(20);
1491 p_assignment_id per_all_assignments_f.assignment_id%type;
1492 p_assg_action_id pay_assignment_actions.assignment_action_id%type;
1493 p_pyrl_action_id pay_payroll_actions.payroll_action_id%type;
1494
1495 cursor csr_leg_code(p_bus_grp_id number) is
1496 select to_char(org_information9) from
1497 hr_organization_information where organization_id = p_bus_grp_id
1498 and org_information_context = 'Business Group Information';
1499
1500 --Declaration for Payroll - ends here
1501
1502
1503 cursor csr_periods_of_service(p_person_id number, p_employee_number number, p_effective_end_date varchar2) is
1504 select to_char(pps.adjusted_svc_date,'YYYY-MM-DD') adjusted_svc_date
1505 ,to_char(pps.date_start,'YYYY-MM-DD') date_start
1506 ,to_char(pps.accepted_termination_date,'YYYY-MM-DD') accepted_termination_date
1507 ,to_char(pps.actual_termination_date,'YYYY-MM-DD') actual_termination_date
1508 ,to_char(pps.final_process_date,'YYYY-MM-DD') final_process_date
1509 ,to_char(pps.last_standard_process_date,'YYYY-MM-DD') last_standard_process_date
1510 ,leaving_reason
1511 from per_periods_of_service pps
1512 where pps.person_id = p_person_id
1513 and ( ( p_employee_number is null )
1514 or ( p_employee_number is not null
1515 and pps.date_start = (
1516 select max(pps1.date_start)
1517 from per_periods_of_service pps1
1518 where pps1.person_id = p_person_id
1519 and pps1.date_start <= to_date(p_effective_end_date,'YYYY-MM-DD') ) ) );
1520 cursor csr_periods_of_placement(p_person_id number, p_employee_number number, p_effective_end_date varchar2) is
1521 select null adjusted_svc_date
1522 ,to_char(ppp.date_start,'YYYY-MM-DD') date_start
1523 ,null accepted_termination_date
1524 ,to_char(ppp.actual_termination_date,'YYYY-MM-DD') actual_termination_date
1525 ,to_char(ppp.final_process_date,'YYYY-MM-DD') final_process_date
1526 ,to_char(ppp.last_standard_process_date,'YYYY-MM-DD') last_standard_process_date
1527 ,termination_reason leaving_reason
1528 from per_periods_of_placement ppp
1529 where ppp.person_id = p_person_id
1530 and (ppp.date_start = (
1531 select max(ppp1.date_start)
1532 from per_periods_of_placement ppp1
1533 where ppp1.person_id = p_person_id
1534 and ppp1.date_start <= to_date(p_effective_end_date,'YYYY-MM-DD') ) );
1535
1536
1537
1538 l_first_assignment_type varchar2(10);
1539 l_temp_assignment HR_PERSON_RECORD.assignment_details;
1540
1541
1542 BEGIN
1543 -- fetch the person id business group id and effective date for all the search criteria and store it in a table of record type
1544 --p_error := 'Before Search counts||';
1545
1546 if p_srch_criteria.count > 0 then
1547
1548 for i in p_srch_criteria.first .. p_srch_criteria.last
1549 loop
1550 if p_srch_criteria(i).p_effective_date is null
1551 then
1552 p_error := 'Effective date is mandatory.Please enter the effective date';
1553 exit;
1554 end if;
1555
1556 if p_srch_criteria(i).p_bgrp_id is not null
1557 then
1558 p_bus_group_id := p_srch_criteria(i).p_bgrp_id;
1559 end if;
1560
1561 if p_srch_criteria(i).p_bgrp_name is not null
1562 then
1563 select business_group_id into p_bus_group_id
1564 from hr_organization_units org
1565 where upper(name) = upper(p_srch_criteria(i).p_bgrp_name)
1566 and org.organization_id = org.business_group_id
1567 and p_srch_criteria(i).p_effective_date between date_from and
1568 nvl (date_to, to_date('31-12-4712', 'DD-MM-YYYY'));
1569 end if;
1570
1571 if p_srch_criteria(i).p_employee_category is not null
1572 then
1573 select LOOKUP_CODE into p_empl_category
1574 from HR_LOOKUPS where lookup_type = 'EMPLOYEE_CATG'
1575 AND meaning = p_srch_criteria(i).p_employee_category
1576 AND p_srch_criteria(i).p_effective_date between nvl(start_date_active,p_srch_criteria(i).p_effective_date)
1577 and nvl(end_date_active,p_srch_criteria(i).p_effective_date)
1578 and enabled_flag = 'Y';
1579 end if;
1580
1581 if p_srch_criteria(i).p_employment_category is not null
1582 then
1583 select LOOKUP_CODE into p_emplmt_category
1584 from HR_LOOKUPS where lookup_type = 'EMP_CAT'
1585 AND meaning = p_srch_criteria(i).p_employment_category
1586 AND p_srch_criteria(i).p_effective_date between nvl(start_date_active,p_srch_criteria(i).p_effective_date)
1587 and nvl(end_date_active,p_srch_criteria(i).p_effective_date)
1588 and enabled_flag = 'Y';
1589 end if;
1590
1591 -- Special cases when qualification_id, phone_id, deliver_mothod id
1592 -- and person_address_id is passed without person_id or assignment_id
1593 IF p_srch_criteria(i).p_phone_id IS NOT NULL
1594 AND p_srch_criteria(i).p_assignment_id IS NULL
1595 AND p_srch_criteria(i).p_person_id IS NULL THEN
1596 open srch_filtrd for
1597
1598 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
1599 from per_people_f ppf
1600 ,per_person_types ppt
1601 ,per_phones pp
1602 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
1603 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1604 and ppf.person_id = pp.parent_id(+)
1605 and pp.parent_table = 'PER_ALL_PEOPLE_F'
1606 and pp.phone_id = p_srch_criteria(i).p_phone_id;
1607
1608 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;
1609 close srch_filtrd;
1610
1611 ELSIF p_srch_criteria(i).p_qualification_id IS NOT NULL
1612 AND p_srch_criteria(i).p_assignment_id IS NULL
1613 AND p_srch_criteria(i).p_person_id IS NULL THEN
1614 open srch_filtrd for
1615
1616 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
1617 from per_people_f ppf
1618 ,per_person_types ppt
1619 ,per_qualifications pq
1620 ,per_establishment_attendances pea
1621 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
1622 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1623 and pq.attendance_id = pea.attendance_id(+)
1624 and (pq.person_id = ppf.person_id or pea.person_id = ppf.person_id)
1625 and pq.qualification_id = p_srch_criteria(i).p_qualification_id;
1626
1627 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;
1628 close srch_filtrd;
1629
1630 ELSIF p_srch_criteria(i).p_delivery_method_id IS NOT NULL
1631 AND p_srch_criteria(i).p_assignment_id IS NULL
1632 AND p_srch_criteria(i).p_person_id IS NULL THEN
1633 open srch_filtrd for
1634
1635 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
1636 from per_people_f ppf
1637 ,per_person_types ppt
1638 ,per_person_dlvry_methods ppdm
1639 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
1640 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1641 and ppdm.person_id = ppf.person_id
1642 and ppdm.delivery_method_id = p_srch_criteria(i).p_delivery_method_id;
1643
1644 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;
1645 close srch_filtrd;
1646
1647 ELSIF p_srch_criteria(i).p_address_id IS NOT NULL
1648 AND p_srch_criteria(i).p_assignment_id IS NULL
1649 AND p_srch_criteria(i).p_person_id IS NULL THEN
1650 open srch_filtrd for
1651
1652 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
1653 from per_people_f ppf
1654 ,per_person_types ppt
1655 ,per_addresses pa
1656 where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
1657 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1658 and pa.person_id = ppf.person_id
1659 and pa.address_id = p_srch_criteria(i).p_address_id;
1660
1661 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;
1662 close srch_filtrd;
1663
1664 ELSE
1665 open srch_filtrd for
1666
1667 select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
1668 from per_people_f ppf ,per_person_types ppt ,per_assignments_f paaf
1669 where nvl(p_srch_criteria(i).p_person_id, ppf.person_id) = ppf.person_id
1670 and ppf.person_id between nvl(p_srch_criteria(i).p_start_person_id, ppf.person_id)
1671 and nvl(p_srch_criteria(i).p_end_person_id, ppf.person_id)
1672 and ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
1673 and last_name = nvl(p_srch_criteria(i).p_last_name,last_name)
1674 and nvl(first_name,'*') = nvl(nvl(p_srch_criteria(i).p_first_name,first_name),'*')
1675 and nvl(employee_number,'*') = nvl(nvl(p_srch_criteria(i).p_employee_no,employee_number),'*')
1676 and nvl(npw_number,'*') = nvl(nvl(p_srch_criteria(i).p_cwk_no,npw_number),'*')
1677 and nvl(applicant_number,'*') = nvl(nvl(p_srch_criteria(i).p_applicant_no,applicant_number),'*')
1678 and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1679 and nvl(ppt.user_person_type ,'*') = nvl(nvl(p_srch_criteria(i).p_person_type,ppt.user_person_type),'*')
1680 and ppf.person_type_id = ppt.person_type_id
1681 and ppf.business_group_id = ppt.business_group_id
1682 and ppf.person_id = paaf.person_id (+)
1683 and nvl(paaf.assignment_id, -1 ) = nvl(nvl(p_srch_criteria(i).p_assignment_id,paaf.assignment_id),-1)
1684 and p_srch_criteria(i).p_effective_date between paaf.effective_start_date (+) and paaf.effective_end_date (+)
1685 and nvl(employment_category,'*') = nvl(nvl(p_emplmt_category,paaf.employment_category ),'*')
1686 and nvl(employee_category,'*') = nvl(nvl(p_empl_category,paaf.employee_category ),'*');
1687
1688
1689 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;
1690 close srch_filtrd;
1691 END IF;
1692
1693 end loop;
1694 end if;
1695 --p_error := 'after selecting the personids||';
1696 -- loop through each of the person record stored in the table of record p_srch_filter_tbl
1697 -- and fetch the person details and all other details
1698
1699 if p_srch_filter_tbl.person_id.count > 0
1700 then
1701 -- p_error := p_error||'Looping in the Person Id||';
1702 for j in p_srch_filter_tbl.person_id.first .. p_srch_filter_tbl.person_id.last
1703 loop
1704
1705 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));
1706 -- 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);
1707 fetch csr_person_record into p_person(j).person;
1708
1709
1710 if p_person(j).person.system_person_type = 'CWK' or p_person(j).person.system_person_type = 'EX_CWK' then
1711
1712 open csr_periods_of_placement(p_person(j).person.person_id,p_person(j).person.employee_number,p_person(j).person.effective_end_date);
1713 fetch csr_periods_of_placement into
1714 p_person(j).person.adjusted_svc_date
1715 ,p_person(j).person.date_start
1716 ,p_person(j).person.accepted_termination_date
1717 ,p_person(j).person.actual_termination_date
1718 ,p_person(j).person.final_process_date
1719 ,p_person(j).person.last_standard_process_date
1720 ,p_person(j).person.leaving_reason;
1721 close csr_periods_of_placement;
1722
1723 else
1724
1725 open csr_periods_of_service(p_person(j).person.person_id,p_person(j).person.employee_number,p_person(j).person.effective_end_date);
1726 fetch csr_periods_of_service into
1727 p_person(j).person.adjusted_svc_date
1728 ,p_person(j).person.date_start
1729 ,p_person(j).person.accepted_termination_date
1730 ,p_person(j).person.actual_termination_date
1731 ,p_person(j).person.final_process_date
1732 ,p_person(j).person.last_standard_process_date
1733 ,p_person(j).person.leaving_reason;
1734 close csr_periods_of_service;
1735 end if;
1736
1737
1738 -- p_error := p_error||'after fectch||';
1739 close csr_person_record;
1740 --p_error := p_error||'before Entity||';
1741
1742 if p_entity.count > 0 then
1743 for m in p_entity.first .. p_entity.last
1744 loop
1745
1746
1747 if p_entity(m) = 'ASSIGNMENT' then
1748 p_cnt := 1;
1749
1750 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));
1751 loop
1752 fetch csr_assignment_record into p_person(j).assignment(p_cnt).asg_details;
1753 exit when csr_assignment_record%notfound;
1754
1755 open csr_asg_loc_addr(p_person(j).assignment(p_cnt).asg_details.location_id);
1756 fetch csr_asg_loc_addr into p_person(j).assignment(p_cnt).asg_loc_addr;
1757 close csr_asg_loc_addr;
1758
1759 p_cnt := p_cnt + 1;
1760 end loop;
1761 close csr_assignment_record;
1762 if (p_person(j).person.system_person_type = 'EMP' or p_person(j).person.system_person_type = 'EX_EMP') then
1763 l_first_assignment_type := 'E';
1764 elsif (p_person(j).person.system_person_type = 'CWK' or p_person(j).person.system_person_type = 'EX_CWK') then
1765 l_first_assignment_type := 'C';
1766 end if;
1767 if l_first_assignment_type in ('E','C') then
1768 for ind in p_person(j).assignment.first..p_person(j).assignment.last loop
1769 if(p_person(j).assignment(ind).asg_details.assignment_type = l_first_assignment_type) then
1770 l_temp_assignment := p_person(j).assignment(ind);
1771 p_person(j).assignment(ind) := p_person(j).assignment(p_person(j).assignment.first);
1772 p_person(j).assignment(p_person(j).assignment.first) := l_temp_assignment;
1773 exit;
1774 end if;
1775 end loop;
1776 end if;
1777 --p_error := p_error||'after selecting the assignments||';
1778 end if;
1779
1780
1781 if p_entity(m) = 'CONTACT' then
1782 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));
1783 fetch csr_contact_record bulk collect into p_person(j).contact;
1784 close csr_contact_record;
1785 --p_error := p_error||'after selecting the contacts||';
1786 end if;
1787
1788 if p_entity(m) = 'CONTACT_ADDRESS' then
1789 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));
1790 fetch csr_cont_address bulk collect into p_person(j).contact_address;
1791 close csr_cont_address;
1792 --p_error := p_error||'after selecting the contact address||';
1793 end if;
1794
1795
1796 if p_entity(m) = 'ADDRESS' then
1797 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));
1798 fetch csr_address_record bulk collect into p_person(j).address;
1799 close csr_address_record;
1800 --p_error := p_error||'after selecting the addresses||';
1801 end if;
1802
1803 if p_entity(m) = 'PERSON_EXTRA_INFO' then
1804 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));
1805 fetch csr_person_extra_info_record bulk collect into p_person(j).person_extra_information;
1806 close csr_person_extra_info_record;
1807 --p_error := p_error||'after selecting the person extra information||';
1808 end if;
1809
1810 if p_entity(m) = 'SPECIAL_INFO' then
1811 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));
1812 fetch csr_special_info_record bulk collect into p_person(j).special_information;
1813 close csr_special_info_record;
1814 -- p_error := p_error||'after selecting the person special information||';
1815 end if;
1816
1817 if p_entity(m) = 'PREVIOUS_EMPLOYMENT' then
1818 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));
1819 fetch csr_prev_emp_info_record bulk collect into p_person(j).previous_employment;
1820 close csr_prev_emp_info_record;
1821 -- p_error := p_error||'after selecting the person previous employment||';
1822 end if;
1823
1824
1825 if p_entity(m) = 'DELIVERY_METHODS' then
1826 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));
1827 fetch csr_deliv_method_info bulk collect into p_person(j).delivery_methods;
1828
1829 close csr_deliv_method_info;
1830 --p_error := p_error||'after selecting the delivery methods||';
1831 end if;
1832
1833 if p_entity(m) = 'WORK_INCIDENTS' then
1834 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));
1835 fetch csr_work_inc_info_record bulk collect into p_person(j).work_incidents;
1836 close csr_work_inc_info_record;
1837 --p_error := p_error||'after selecting the work incidents||';
1838 end if;
1839
1840 if p_entity(m) = 'ASSIGNMENT_EXTRA_INFO' then
1841 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));
1842 fetch csr_ass_extra_info_record bulk collect into p_person(j).assignment_extra_information;
1843 close csr_ass_extra_info_record;
1844 -- p_error := p_error||'after selecting the ASSIGNMENT_EXTRA_INFO||';
1845 end if;
1846
1847 if p_entity(m) = 'CONTACT_EXTRA_INFO' then
1848 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));
1849 fetch csr_con_extra_info_record bulk collect into p_person(j).contact_extra_information;
1850 close csr_con_extra_info_record;
1851 --p_error := p_error||'after selecting the CONTACT_EXTRA_INFO||';
1852 end if;
1853
1854 if p_entity(m) = 'PHONE' then
1855 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));
1856 fetch csr_phone_record bulk collect into p_person(j).phone;
1857 close csr_phone_record;
1858 --p_error := p_error||'after selecting the PHONE||';
1859 end if;
1860
1861 if p_entity(m) = 'QUALIFICATION' then
1862 p_cnt := 1;
1863 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));
1864 LOOP
1865
1866 fetch csr_qualification_record INTO p_person(j).qualification(p_cnt).QUALIFICATIONS;
1867 exit when csr_qualification_record%NOTFOUND;
1868
1869
1870
1871 open csr_subject_record(p_person(j).qualification(p_cnt).qualifications.qualification_id,p_srch_filter_tbl.p_srch_dt(j));
1872 fetch csr_subject_record bulk collect into p_person(j).qualification(p_cnt).subject;
1873 close csr_subject_record;
1874 p_cnt := p_cnt + 1;
1875 end loop;
1876 close csr_qualification_record;
1877 --p_error := p_error||'after selecting the QUALIFICATION||';
1878 end if;
1879
1880 if p_entity(m) = 'REPORTEE' then
1881 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));
1882 fetch csr_reportee_record bulk collect into p_person(j).reportee;
1883 close csr_reportee_record;
1884 --p_error := p_error||'after selecting the REPORTEE||';
1885 end if;
1886
1887 if p_entity(m) = 'ABSENCE' then
1888 p_cnt := 1;
1889 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))
1890 loop
1891 per_accrual_calc_functions.get_net_accrual(
1892 P_assignment_id => ACCRUAL_DETAILS.assignment_id,
1893 P_plan_id => ACCRUAL_DETAILS.ACCRUAL_PLAN_ID,
1894 P_payroll_id => ACCRUAL_DETAILS.payroll_id,
1895 p_business_group_id => p_srch_filter_tbl.business_group_id(j),
1896 p_assignment_action_id => -1,
1897 P_calculation_date => p_srch_filter_tbl.p_srch_dt(j),
1898 P_Accrual_Start_Date => Null,
1899 P_Accrual_Latest_Balance => Null,
1900 P_Start_Date => L_Start_Date,
1901 P_End_Date => L_End_Date,
1902 P_Accrual_End_Date => L_Accrual_End_Date,
1903 P_Accrual => L_Accrual,
1904 P_Net_Entitlement => L_Entitlement
1905 );
1906 SELECT ACCRUAL_DETAILS.ACCRUAL_PLAN_NAME,L_ENTITLEMENT,ACCRUAL_DETAILS.ACCRUAL_UNITS_OF_MEASURE_NAME,
1907 TO_CHAR(p_srch_filter_tbl.p_srch_dt(j),'YYYY-MM-DD')
1908 INTO p_person(j).ABSENCE(P_CNT) FROM DUAL;
1909 p_cnt := p_cnt + 1;
1910 end loop;
1911
1912 --p_error := p_error||'after selecting the Absence||';
1913 end if;
1914
1915 if p_entity(m) = 'BENEFITS' then
1916
1917 ben_person_record.get_ben_details(p_person(j).benefit_details,p_srch_filter_tbl.person_id(j),p_srch_filter_tbl.p_srch_dt(j),p_srch_filter_tbl.business_group_id(j));
1918
1919 end if;
1920
1921 if p_entity(m) = 'PAYROLL' then
1922
1923 open csr_leg_code(p_srch_filter_tbl.business_group_id(j));
1924 fetch csr_leg_code into p_leg_code;
1925 close csr_leg_code;
1926
1927 -- Process US Payroll since the legislationcode is US
1928
1929 if p_leg_code = 'US' then
1930
1931 PAY_US_HR_HELPDESK.GET_USPAY_DETAILS(p_per_id =>p_srch_filter_tbl.person_id(j),
1932 p_bg_id => p_srch_filter_tbl.business_group_id(j),
1933 p_eff_date => p_srch_filter_tbl.p_srch_dt(j),
1934 p_leg_code => p_leg_code,
1935 p_pyrl_dtls => p_person(j).payroll,
1936 p_error => p_error);
1937 end if;
1938
1939 if p_leg_code = 'GB' then
1940
1941 PAY_GB_HR_HELPDESK.GET_UKPAY_DETAILS(p_per_id =>p_srch_filter_tbl.person_id(j),
1942 p_bg_id => p_srch_filter_tbl.business_group_id(j),
1943 p_eff_date => p_srch_filter_tbl.p_srch_dt(j),
1944 p_leg_code => p_leg_code,
1945 p_pyrl_dtls => p_person(j).payroll,
1946 p_error => p_error);
1947 end if;
1948
1949 end if;
1950
1951 end loop;
1952 -- p_error := 'p_error||'Ended the options loop||';
1953 end if;
1954
1955 end loop;
1956 --p_error := p_error||'Ended the person search loop||';
1957 end if;
1958
1959 exception when others
1960 then
1961 p_error := p_error||substr(SQLERRM,1,1500);
1962
1963 END GET_PERSON_DETAILS;
1964 end HR_PERSON_RECORD ;