[Home] [Help]
PACKAGE: APPS.HR_PERSON_RECORD
Source
1 PACKAGE HR_PERSON_RECORD AS
2 /* $Header: peperqry.pkh 120.2.12010000.9 2009/01/22 09:35:07 srgnanas noship $ */
3 /*#
4 * This is the source file to HR_PERSON_RECORD
5 * @rep:scope public
6 * @rep:product per
7 * @rep:displayname HR Person Record
8 */
9
10 -- Record for search criteria
11 Type srch_criteria_record is RECORD
12 (p_person_id per_all_people_f.person_id%type default null,
13 p_start_person_id per_all_people_f.person_id%type default null,
14 p_end_person_id per_all_people_f.person_id%type default null,
15 p_assignment_id per_all_assignments_f.assignment_id%type default null,
16 p_first_name per_all_people_f.first_name%type default null,
17 p_last_name per_all_people_f.last_name%type default null,
18 p_bgrp_name hr_all_organization_units.name%type default null,
19 p_bgrp_id hr_all_organization_units.business_group_id%type default null,
20 p_effective_date date,
21 p_employee_no per_all_people_f.employee_number%type default null,
22 p_applicant_no per_all_people_f.applicant_number%type default null,
23 p_cwk_no per_all_people_f.npw_number%type default null,
24 p_person_type PER_PERSON_TYPES_TL.user_person_type%TYPE default null,
25 p_employee_category hr_lookups.meaning%type default null,
26 p_employment_category hr_lookups.meaning%type default null,
27 p_phone_id per_phones.phone_id%type default null,
28 p_qualification_id per_qualifications.qualification_id%type default null,
29 p_delivery_method_id per_person_dlvry_methods.delivery_method_id%type default null,
30 p_address_id per_addresses.address_id%type default null);
31
32 -- Search criteria as table type
33 Type srch_criteria is table of srch_criteria_record INDEX BY BINARY_INTEGER;
34
35 /*This Table holds the list of options*/
36
37 type options is table of varchar2(200) index by binary_integer;
38
39 /*Record type for person details*/
40 TYPE person_details IS RECORD
41 (
42 PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE,
43 EFFECTIVE_START_DATE varchar2(10),
44 EFFECTIVE_END_DATE varchar2(10),
45 BUSINESS_GROUP_ID PER_ALL_PEOPLE_F.BUSINESS_GROUP_ID%TYPE,
46 BUSINESS_GROUP_DESC HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE, -- (BASED ON LANG)
47 PERSON_TYPE_ID PER_ALL_PEOPLE_F.PERSON_TYPE_ID%TYPE,
48 USER_PERSN_TYPE PER_PERSON_TYPES_TL.user_person_type%TYPE,
49 SYSTEM_PERSON_TYPE PER_PERSON_TYPES.system_person_type%TYPE,
50 LAST_NAME PER_ALL_PEOPLE_F.LAST_NAME%TYPE,
51 START_DATE varchar2(10),
52 APPLICANT_NUMBER PER_ALL_PEOPLE_F.APPLICANT_NUMBER%TYPE,
53 BACKGROUND_CHECK_STATUS PER_ALL_PEOPLE_F.BACKGROUND_CHECK_STATUS%TYPE,
54 BACKGROUND_DATE_CHECK varchar2(10),
55 BLOOD_TYPE PER_ALL_PEOPLE_F.BLOOD_TYPE%TYPE,
56 BLOOD_TYPE_DESC FND_LOOKUP_VALUES.MEANING%TYPE, -- (BASED ON LANG /'BLOOD_TYPE')
57 COMMENT_ID PER_ALL_PEOPLE_F.COMMENT_ID%TYPE,
58 CORRESPONDENCE_LANGUAGE PER_ALL_PEOPLE_F.CORRESPONDENCE_LANGUAGE%TYPE,
59 CORRESPONDENCE_LANGUAGE_DESC FND_LANGUAGES_VL.DESCRIPTION%TYPE,
60 CURRENT_APPLICANT_FLAG PER_ALL_PEOPLE_F.CURRENT_APPLICANT_FLAG%TYPE,
61 CURRENT_EMP_OR_APL_FLAG PER_ALL_PEOPLE_F.CURRENT_EMP_OR_APL_FLAG%TYPE,
62 CURRENT_EMPLOYEE_FLAG PER_ALL_PEOPLE_F.CURRENT_EMPLOYEE_FLAG%TYPE,
63 DATE_EMPLOYEE_DATA_VERIFIED varchar2(10),
64 DATE_OF_BIRTH varchar2(10),
65 EMAIL_ADDRESS PER_ALL_PEOPLE_F.EMAIL_ADDRESS%TYPE,
66 EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE,
67 EXPENSE_CHECK_SEND_TO_ADDRESS PER_ALL_PEOPLE_F.EXPENSE_CHECK_SEND_TO_ADDRESS%TYPE,
68 FAST_PATH_EMPLOYEE PER_ALL_PEOPLE_F.FAST_PATH_EMPLOYEE%TYPE,
69 FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%TYPE,
70 FTE_CAPACITY PER_ALL_PEOPLE_F.FTE_CAPACITY%TYPE,
71 FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
72 HOLD_APPLICANT_DATE_UNTIL varchar2(10),
73 HONORS PER_ALL_PEOPLE_F.HONORS%TYPE,
74 INTERNAL_LOCATION PER_ALL_PEOPLE_F.INTERNAL_LOCATION%TYPE,
75 KNOWN_AS PER_ALL_PEOPLE_F.KNOWN_AS%TYPE,
76 LAST_MEDICAL_TEST_BY PER_ALL_PEOPLE_F.LAST_MEDICAL_TEST_BY%TYPE,
77 LAST_MEDICAL_TEST_DATE varchar2(10),
78 MAILSTOP PER_ALL_PEOPLE_F.MAILSTOP%TYPE,
79 MARITAL_STATUS PER_ALL_PEOPLE_F.MARITAL_STATUS%TYPE,
80 MARITAL_STATUS_DESC FND_LOOKUP_VALUES.MEANING%TYPE,-- (BASED ON LANG /'MARITAL_STATUS')
81 MIDDLE_NAMES PER_ALL_PEOPLE_F.MIDDLE_NAMES%TYPE,
82 NATIONALITY PER_ALL_PEOPLE_F.NATIONALITY%TYPE,
83 NATIONALITY_DESC FND_LOOKUP_VALUES.MEANING%TYPE,-- (BASED ON LANG /'NATIONALITY')
84 NATIONAL_IDENTIFIER PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%TYPE,
85 NATIONAL_ID_LABEL VARCHAR2(200),
86 OFFICE_NUMBER PER_ALL_PEOPLE_F.OFFICE_NUMBER%TYPE,
87 ON_MILITARY_SERVICE PER_ALL_PEOPLE_F.ON_MILITARY_SERVICE%TYPE,
88 ORDER_NAME PER_ALL_PEOPLE_F.ORDER_NAME%TYPE,
89 PRE_NAME_ADJUNCT PER_ALL_PEOPLE_F.PRE_NAME_ADJUNCT%TYPE,
90 PREVIOUS_LAST_NAME PER_ALL_PEOPLE_F.PREVIOUS_LAST_NAME%TYPE,
91 PROJECTED_START_DATE varchar2(10),
92 REHIRE_AUTHORIZOR PER_ALL_PEOPLE_F.REHIRE_AUTHORIZOR%TYPE,
93 REHIRE_REASON PER_ALL_PEOPLE_F.REHIRE_REASON%TYPE,
94 REHIRE_RECOMMENDATION PER_ALL_PEOPLE_F.REHIRE_RECOMMENDATION%TYPE, -- CHECK BOX
95 RESUME_EXISTS PER_ALL_PEOPLE_F.RESUME_EXISTS%TYPE, -- (CHECK BOX)
96 RESUME_LAST_UPDATED varchar2(10),
97 REGISTERED_DISABLED_FLAG PER_ALL_PEOPLE_F.REGISTERED_DISABLED_FLAG%TYPE,
98 SECOND_PASSPORT_EXISTS PER_ALL_PEOPLE_F.SECOND_PASSPORT_EXISTS%TYPE, -- CHECK BOX
99 SEX PER_ALL_PEOPLE_F.SEX%TYPE,
100 SEX_DESC FND_LOOKUP_VALUES.MEANING%TYPE,-- (BASED ON LANG /'SEX')
101 STUDENT_STATUS PER_ALL_PEOPLE_F.STUDENT_STATUS%TYPE,
102 STUDENT_STATUS_DESC FND_LOOKUP_VALUES.MEANING%TYPE,-- (BASED ON LANG /'STUDENT_STATUS')
103 SUFFIX PER_ALL_PEOPLE_F.SUFFIX%TYPE,
104 TITLE PER_ALL_PEOPLE_F.TITLE%TYPE,
105 TITLE_DESC FND_LOOKUP_VALUES.MEANING%TYPE,-- (BASED ON LANG /'TITLE')
106 VENDOR_ID PER_ALL_PEOPLE_F.VENDOR_ID%TYPE,
107 WORK_SCHEDULE PER_ALL_PEOPLE_F.WORK_SCHEDULE%TYPE,
108 WORK_SCHEDULE_DESC FND_LOOKUP_VALUES.MEANING%TYPE,-- (BASED ON LANG /'WORK_SCHEDULE')
109 WORK_TELEPHONE PER_ALL_PEOPLE_F.WORK_TELEPHONE%TYPE,
110 COORD_BEN_MED_PLN_NO PER_ALL_PEOPLE_F.COORD_BEN_MED_PLN_NO%TYPE,
111 COORD_BEN_NO_CVG_FLAG PER_ALL_PEOPLE_F.COORD_BEN_NO_CVG_FLAG%TYPE, -- CHECK BOX
112 DPDNT_ADOPTION_DATE varchar2(10),
113 DPDNT_VLNTRY_SVCE_FLAG PER_ALL_PEOPLE_F.DPDNT_VLNTRY_SVCE_FLAG%TYPE, -- CHECK BOX
114 RECEIPT_OF_DEATH_CERT_DATE varchar2(10),
115 USES_TOBACCO_FLAG PER_ALL_PEOPLE_F.USES_TOBACCO_FLAG%TYPE,
116 USES_TOBACCO_FLAG_DESC FND_LOOKUP_VALUES.MEANING%TYPE, -- (BASED ON LANG /'TOBACCO_USER')
117 BENEFIT_GROUP_ID PER_ALL_PEOPLE_F.BENEFIT_GROUP_ID%TYPE,
118 BENEFIT_GROUP_NAME BEN_BENFTS_GRP.NAME%TYPE,
119 ATTRIBUTE_CATEGORY PER_ALL_PEOPLE_F.ATTRIBUTE_CATEGORY%TYPE,
120 ATTRIBUTE1 PER_ALL_PEOPLE_F.ATTRIBUTE1%TYPE,
121 ATTRIBUTE2 PER_ALL_PEOPLE_F.ATTRIBUTE2%TYPE,
122 ATTRIBUTE3 PER_ALL_PEOPLE_F.ATTRIBUTE3%TYPE,
123 ATTRIBUTE4 PER_ALL_PEOPLE_F.ATTRIBUTE4%TYPE,
124 ATTRIBUTE5 PER_ALL_PEOPLE_F.ATTRIBUTE5%TYPE,
125 ATTRIBUTE6 PER_ALL_PEOPLE_F.ATTRIBUTE6%TYPE,
126 ATTRIBUTE7 PER_ALL_PEOPLE_F.ATTRIBUTE7%TYPE,
127 ATTRIBUTE8 PER_ALL_PEOPLE_F.ATTRIBUTE8%TYPE,
128 ATTRIBUTE9 PER_ALL_PEOPLE_F.ATTRIBUTE9%TYPE,
129 ATTRIBUTE10 PER_ALL_PEOPLE_F.ATTRIBUTE10%TYPE,
130 ATTRIBUTE11 PER_ALL_PEOPLE_F.ATTRIBUTE11%TYPE,
131 ATTRIBUTE12 PER_ALL_PEOPLE_F.ATTRIBUTE12%TYPE,
132 ATTRIBUTE13 PER_ALL_PEOPLE_F.ATTRIBUTE13%TYPE,
133 ATTRIBUTE14 PER_ALL_PEOPLE_F.ATTRIBUTE14%TYPE,
134 ATTRIBUTE15 PER_ALL_PEOPLE_F.ATTRIBUTE15%TYPE,
135 ATTRIBUTE16 PER_ALL_PEOPLE_F.ATTRIBUTE16%TYPE,
136 ATTRIBUTE17 PER_ALL_PEOPLE_F.ATTRIBUTE17%TYPE,
137 ATTRIBUTE18 PER_ALL_PEOPLE_F.ATTRIBUTE18%TYPE,
138 ATTRIBUTE19 PER_ALL_PEOPLE_F.ATTRIBUTE19%TYPE,
139 ATTRIBUTE20 PER_ALL_PEOPLE_F.ATTRIBUTE20%TYPE,
140 ATTRIBUTE21 PER_ALL_PEOPLE_F.ATTRIBUTE21%TYPE,
141 ATTRIBUTE22 PER_ALL_PEOPLE_F.ATTRIBUTE22%TYPE,
142 ATTRIBUTE23 PER_ALL_PEOPLE_F.ATTRIBUTE23%TYPE,
143 ATTRIBUTE24 PER_ALL_PEOPLE_F.ATTRIBUTE24%TYPE,
144 ATTRIBUTE25 PER_ALL_PEOPLE_F.ATTRIBUTE25%TYPE,
145 ATTRIBUTE26 PER_ALL_PEOPLE_F.ATTRIBUTE26%TYPE,
146 ATTRIBUTE27 PER_ALL_PEOPLE_F.ATTRIBUTE27%TYPE,
147 ATTRIBUTE28 PER_ALL_PEOPLE_F.ATTRIBUTE28%TYPE,
148 ATTRIBUTE29 PER_ALL_PEOPLE_F.ATTRIBUTE29%TYPE,
149 ATTRIBUTE30 PER_ALL_PEOPLE_F.ATTRIBUTE30%TYPE,
150 PER_INFORMATION_CATEGORY PER_ALL_PEOPLE_F.PER_INFORMATION_CATEGORY%TYPE,
151 PER_INFORMATION1 PER_ALL_PEOPLE_F.PER_INFORMATION1%TYPE,
152 PER_INFORMATION2 PER_ALL_PEOPLE_F.PER_INFORMATION2%TYPE,
153 PER_INFORMATION3 PER_ALL_PEOPLE_F.PER_INFORMATION3%TYPE,
154 PER_INFORMATION4 PER_ALL_PEOPLE_F.PER_INFORMATION4%TYPE,
155 PER_INFORMATION5 PER_ALL_PEOPLE_F.PER_INFORMATION5%TYPE,
156 PER_INFORMATION6 PER_ALL_PEOPLE_F.PER_INFORMATION6%TYPE,
157 PER_INFORMATION7 PER_ALL_PEOPLE_F.PER_INFORMATION7%TYPE,
158 PER_INFORMATION8 PER_ALL_PEOPLE_F.PER_INFORMATION8%TYPE,
159 PER_INFORMATION9 PER_ALL_PEOPLE_F.PER_INFORMATION9%TYPE,
160 PER_INFORMATION10 PER_ALL_PEOPLE_F.PER_INFORMATION10%TYPE,
161 PER_INFORMATION11 PER_ALL_PEOPLE_F.PER_INFORMATION11%TYPE,
162 PER_INFORMATION12 PER_ALL_PEOPLE_F.PER_INFORMATION12%TYPE,
163 PER_INFORMATION13 PER_ALL_PEOPLE_F.PER_INFORMATION13%TYPE,
164 PER_INFORMATION14 PER_ALL_PEOPLE_F.PER_INFORMATION14%TYPE,
165 PER_INFORMATION15 PER_ALL_PEOPLE_F.PER_INFORMATION15%TYPE,
166 PER_INFORMATION16 PER_ALL_PEOPLE_F.PER_INFORMATION16%TYPE,
167 PER_INFORMATION17 PER_ALL_PEOPLE_F.PER_INFORMATION17%TYPE,
168 PER_INFORMATION18 PER_ALL_PEOPLE_F.PER_INFORMATION18%TYPE,
169 PER_INFORMATION19 PER_ALL_PEOPLE_F.PER_INFORMATION19%TYPE,
170 PER_INFORMATION20 PER_ALL_PEOPLE_F.PER_INFORMATION20%TYPE,
171 PER_INFORMATION21 PER_ALL_PEOPLE_F.PER_INFORMATION21%TYPE,
172 PER_INFORMATION22 PER_ALL_PEOPLE_F.PER_INFORMATION22%TYPE,
173 PER_INFORMATION23 PER_ALL_PEOPLE_F.PER_INFORMATION23%TYPE,
174 PER_INFORMATION24 PER_ALL_PEOPLE_F.PER_INFORMATION24%TYPE,
175 PER_INFORMATION25 PER_ALL_PEOPLE_F.PER_INFORMATION25%TYPE,
176 PER_INFORMATION26 PER_ALL_PEOPLE_F.PER_INFORMATION26%TYPE,
177 PER_INFORMATION27 PER_ALL_PEOPLE_F.PER_INFORMATION27%TYPE,
178 PER_INFORMATION28 PER_ALL_PEOPLE_F.PER_INFORMATION28%TYPE,
179 PER_INFORMATION29 PER_ALL_PEOPLE_F.PER_INFORMATION29%TYPE,
180 PER_INFORMATION30 PER_ALL_PEOPLE_F.PER_INFORMATION30%TYPE,
181 DATE_OF_DEATH varchar2(10),
182 ORIGINAL_DATE_OF_HIRE varchar2(10),
183 TOWN_OF_BIRTH PER_ALL_PEOPLE_F.TOWN_OF_BIRTH%TYPE,
184 REGION_OF_BIRTH PER_ALL_PEOPLE_F.REGION_OF_BIRTH%TYPE,
185 COUNTRY_OF_BIRTH PER_ALL_PEOPLE_F.COUNTRY_OF_BIRTH%TYPE,
186 COUNTRY_OF_BIRTH_DESC FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE,
187 GLOBAL_PERSON_ID PER_ALL_PEOPLE_F.GLOBAL_PERSON_ID%TYPE,
188 COORD_BEN_MED_PL_NAME PER_ALL_PEOPLE_F.COORD_BEN_MED_PL_NAME%TYPE,
189 COORD_BEN_MED_INSR_CRR_NAME PER_ALL_PEOPLE_F.COORD_BEN_MED_INSR_CRR_NAME%TYPE,
190 COORD_BEN_MED_INSR_CRR_IDENT PER_ALL_PEOPLE_F.COORD_BEN_MED_INSR_CRR_IDENT%TYPE,
191 COORD_BEN_MED_EXT_ER PER_ALL_PEOPLE_F.COORD_BEN_MED_EXT_ER%TYPE,
192 COORD_BEN_MED_CVG_STRT_DT PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_STRT_DT%TYPE,
193 COORD_BEN_MED_CVG_END_DT PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_END_DT%TYPE,
194 PARTY_ID PER_ALL_PEOPLE_F.PARTY_ID%TYPE,
195 NPW_NUMBER PER_ALL_PEOPLE_F.NPW_NUMBER%TYPE,
196 CURRENT_NPW_FLAG PER_ALL_PEOPLE_F.CURRENT_NPW_FLAG%TYPE,
197 GLOBAL_NAME PER_ALL_PEOPLE_F.GLOBAL_NAME%TYPE,
198 LOCAL_NAME PER_ALL_PEOPLE_F.LOCAL_NAME%TYPE
199 );
200 /*Record type for assignment details*/
201 TYPE ASSGN_DETAILS is record
202 (
203 ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE
204 , EFFECTIVE_START_DATE varchar2(10)
205 , EFFECTIVE_END_DATE varchar2(10)
206 , RECRUITER_ID PER_ALL_ASSIGNMENTS_F.RECRUITER_ID%TYPE
207 , GRADE_ID PER_ALL_ASSIGNMENTS_F.GRADE_ID%TYPE
208 , GRADE_DESC PER_GRADES_TL.NAME%TYPE --(BASED ON LANG)
209 , POSITION_ID PER_ALL_ASSIGNMENTS_F.POSITION_ID%TYPE
210 , POSITION_DESC HR_ALL_POSITIONS_F_TL.NAME%TYPE --(BASED ON LANG)
211 , JOB_ID PER_ALL_ASSIGNMENTS_F.JOB_ID%TYPE
212 , JOB_DESC PER_JOBS_TL.NAME%TYPE --(BASED ON LANG)
213 , ASSIGNMENT_STATUS_TYPE_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID%TYPE
214 , USER_STATUS PER_ASSIGNMENT_STATUS_TYPES_TL.USER_STATUS%TYPE
215 , PAY_SYSTEM_STATUS PER_ASSIGNMENT_STATUS_TYPES.PAY_SYSTEM_STATUS%TYPE
216 , PER_SYSTEM_STATUS PER_ASSIGNMENT_STATUS_TYPES.PER_SYSTEM_STATUS%TYPE
217 , PAYROLL_ID PER_ALL_ASSIGNMENTS_F.PAYROLL_ID%TYPE
218 , PAYROLL_DESC PAY_PAYROLLS_F.PAYROLL_NAME%TYPE
219 , LOCATION_ID PER_ALL_ASSIGNMENTS_F.LOCATION_ID%TYPE
220 , LOCATION_DESC HR_LOCATIONS_ALL_TL.DESCRIPTION%TYPE --(BASED ON LANG)
221 , PERSON_REFERRED_BY_ID PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID%TYPE
222 , SUPERVISOR_ID PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID%TYPE
223 , SUPERVISOR_EMP_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE
224 , SUPERVISOR_CWK_NUMBER PER_ALL_PEOPLE_F.NPW_NUMBER%TYPE
225 , SUPERVISOR_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE
226 , SPECIAL_CEILING_STEP_ID PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE
227 , RECRUITMENT_ACTIVITY_ID PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID%TYPE
228 , SOURCE_ORGANIZATION_ID PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID%TYPE
229 , ORGANIZATION_ID PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE
230 , ORGANIZATION_DESC HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE -- (BASED ON LANG)
231 , PEOPLE_GROUP_ID PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID%TYPE
232 , SOFT_CODING_KEYFLEX_ID PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID%TYPE
233 , VACANCY_ID PER_ALL_ASSIGNMENTS_F.VACANCY_ID%TYPE
234 , VACANCY_DESC PER_VACANCIES.NAME%TYPE
235 , PAY_BASIS_ID PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID%TYPE
236 , PAY_BASIS_DESC PER_PAY_BASES.NAME%TYPE
237 , ASSIGNMENT_SEQUENCE PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE
238 , ASSIGNMENT_TYPE PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_TYPE%TYPE -- LIKE E,C ETC
239 , PRIMARY_FLAG PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG%TYPE -- CHECK BOX
240 , APPLICATION_ID PER_ALL_ASSIGNMENTS_F.APPLICATION_ID%TYPE
241 , ASSIGNMENT_NUMBER PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE
242 , CHANGE_REASON PER_ALL_ASSIGNMENTS_F.CHANGE_REASON%TYPE
243 , CHANGE_REASON_DESC FND_LOOKUP_VALUES.MEANING%TYPE -- (BASED ON LANG /'E','EMP_ASSIGN_REASON', 'C', 'CWK_ASSIGN_REASON')
244 , COMMENT_ID PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE
245 , DATE_PROBATION_END varchar2(10)
246 , DEFAULT_CODE_COMB_ID PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID%TYPE
247 , EMPLOYMENT_CATEGORY PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY%TYPE
248 , EMPLOYMENT_CATEGORY_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'EMP_CAT')
249 , FREQUENCY PER_ALL_ASSIGNMENTS_F.FREQUENCY%TYPE
250 , FREQUENCY_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'FREQUENCY')
251 , INTERNAL_ADDRESS_LINE PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE%TYPE
252 , MANAGER_FLAG PER_ALL_ASSIGNMENTS_F.MANAGER_FLAG%TYPE -- CHECK BOX
253 , NORMAL_HOURS PER_ALL_ASSIGNMENTS_F.NORMAL_HOURS%TYPE
254 , PERF_REVIEW_PERIOD PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD%TYPE
255 , PERF_REVIEW_PERIOD_FREQUENCY PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY%TYPE
256 , PERF_RP_FREQUENCY_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'FREQUENCY')
257 , PERIOD_OF_SERVICE_ID PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID%TYPE
258 , PROBATION_PERIOD PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD%TYPE
259 , PROBATION_UNIT PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT%TYPE
260 , PROBATION_UNIT_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'QUALIFYING_UNITS')
261 , SAL_REVIEW_PERIOD PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD%TYPE
262 , SAL_REVIEW_PERIOD_FREQUENCY PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY%TYPE
263 , SAL_RP_FREQUENCY_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'FREQUENCY')
264 , SET_OF_BOOKS_ID PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID%TYPE
265 , SOURCE_TYPE PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE%TYPE
266 , TIME_NORMAL_FINISH PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH%TYPE
267 , TIME_NORMAL_START PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START%TYPE
268 , BARGAINING_UNIT_CODE PER_ALL_ASSIGNMENTS_F.BARGAINING_UNIT_CODE%TYPE
269 , BARGAINING_UNIT_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'BARGAINING_UNIT_CODE')
270 , LABOUR_UNION_MEMBER_FLAG PER_ALL_ASSIGNMENTS_F.LABOUR_UNION_MEMBER_FLAG%TYPE -- CHECK BOX
271 , HOURLY_SALARIED_CODE PER_ALL_ASSIGNMENTS_F.HOURLY_SALARIED_CODE%TYPE
272 , HOURLY_SALARIED_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'HOURLY_SALARIED_CODE')
273 , CONTRACT_ID PER_ALL_ASSIGNMENTS_F.CONTRACT_ID%TYPE
274 , COLLECTIVE_AGREEMENT_ID PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID%TYPE
275 , COLLECTIVE_AGREEMENT_DESC PER_COLLECTIVE_AGREEMENTS.NAME%TYPE
276 , CAGR_ID_FLEX_NUM PER_ALL_ASSIGNMENTS_F.CAGR_ID_FLEX_NUM%TYPE
277 , CAGR_GRADE_DEF_ID PER_ALL_ASSIGNMENTS_F.CAGR_GRADE_DEF_ID%TYPE
278 , ESTABLISHMENT_ID PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID%TYPE
279 , ESTABLISHMENT_DESC HR_ESTABLISHMENTS_V.NAME%TYPE
280 , ASS_ATTRIBUTE_CATEGORY PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE_CATEGORY%TYPE
281 , ASS_ATTRIBUTE1 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE1%TYPE
282 , ASS_ATTRIBUTE2 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE2%TYPE
283 , ASS_ATTRIBUTE3 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE3%TYPE
284 , ASS_ATTRIBUTE4 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE4%TYPE
285 , ASS_ATTRIBUTE5 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE5%TYPE
286 , ASS_ATTRIBUTE6 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE6%TYPE
287 , ASS_ATTRIBUTE7 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE7%TYPE
288 , ASS_ATTRIBUTE8 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE8%TYPE
289 , ASS_ATTRIBUTE9 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE9%TYPE
290 , ASS_ATTRIBUTE10 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE10%TYPE
291 , ASS_ATTRIBUTE11 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE11%TYPE
292 , ASS_ATTRIBUTE12 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE12%TYPE
293 , ASS_ATTRIBUTE13 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE13%TYPE
294 , ASS_ATTRIBUTE14 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE14%TYPE
295 , ASS_ATTRIBUTE15 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE15%TYPE
296 , ASS_ATTRIBUTE16 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE16%TYPE
297 , ASS_ATTRIBUTE17 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE17%TYPE
298 , ASS_ATTRIBUTE18 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE18%TYPE
299 , ASS_ATTRIBUTE19 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE19%TYPE
300 , ASS_ATTRIBUTE20 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE20%TYPE
301 , ASS_ATTRIBUTE21 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE21%TYPE
302 , ASS_ATTRIBUTE22 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE22%TYPE
303 , ASS_ATTRIBUTE23 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE23%TYPE
304 , ASS_ATTRIBUTE24 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE24%TYPE
305 , ASS_ATTRIBUTE25 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE25%TYPE
306 , ASS_ATTRIBUTE26 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE26%TYPE
307 , ASS_ATTRIBUTE27 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE27%TYPE
308 , ASS_ATTRIBUTE28 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE28%TYPE
309 , ASS_ATTRIBUTE29 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE29%TYPE
310 , ASS_ATTRIBUTE30 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE30%TYPE
311 , TITLE PER_ALL_ASSIGNMENTS_F.TITLE%TYPE
312 , NOTICE_PERIOD PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD%TYPE
313 , NOTICE_PERIOD_UOM PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD_UOM%TYPE
314 , EMPLOYEE_CATEGORY PER_ALL_ASSIGNMENTS_F.EMPLOYEE_CATEGORY%TYPE
315 , EMPLOYEE_CATEGORY_DESC FND_LOOKUP_VALUES.MEANING%TYPE --(BASED ON LANG /'EMPLOYEE_CATG')
316 , WORK_AT_HOME PER_ALL_ASSIGNMENTS_F.WORK_AT_HOME%TYPE -- CHECK BOX
317 , JOB_POST_SOURCE_NAME PER_ALL_ASSIGNMENTS_F.JOB_POST_SOURCE_NAME%TYPE
318 , POSTING_CONTENT_ID PER_ALL_ASSIGNMENTS_F.POSTING_CONTENT_ID%TYPE
319 , PERIOD_OF_PLACEMENT_DATE_START varchar2(10)
320 , VENDOR_ID PER_ALL_ASSIGNMENTS_F.VENDOR_ID%TYPE
321 , VENDOR_EMPLOYEE_NUMBER PER_ALL_ASSIGNMENTS_F.VENDOR_EMPLOYEE_NUMBER%TYPE
322 , VENDOR_ASSIGNMENT_NUMBER PER_ALL_ASSIGNMENTS_F.VENDOR_ASSIGNMENT_NUMBER%TYPE
323 , ASSIGNMENT_CATEGORY PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_CATEGORY%TYPE
324 , PROJECT_TITLE PER_ALL_ASSIGNMENTS_F.PROJECT_TITLE%TYPE
325 , APPLICANT_RANK PER_ALL_ASSIGNMENTS_F.APPLICANT_RANK%TYPE
326 , VENDOR_SITE_ID PER_ALL_ASSIGNMENTS_F.VENDOR_SITE_ID%TYPE
327 , PO_HEADER_ID PER_ALL_ASSIGNMENTS_F.PO_HEADER_ID%TYPE
328 , PO_LINE_ID PER_ALL_ASSIGNMENTS_F.PO_LINE_ID%TYPE
329 , PROJECTED_ASSIGNMENT_END PER_ALL_ASSIGNMENTS_F.PROJECTED_ASSIGNMENT_END%TYPE
330 , GRADE_LADDER_PGM_ID PER_ALL_ASSIGNMENTS_F.GRADE_LADDER_PGM_ID%TYPE
331 , GRADE_LADDER_PGM_NAME BEN_PGM_F.NAME%TYPE -- FOR PGM TYPE CODE GSP
332 , SUPERVISOR_ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID%TYPE
333 );
334
335 /*Record Type definition for Location Address*/
336
337 TYPE ASSGN_LOCN_ADDRESS IS RECORD
338 ( LOCATION_ID HR_LOCATIONS_ALL.LOCATION_ID%TYPE
339 , STYLE HR_LOCATIONS_ALL.STYLE%TYPE
340 , STYLE_DESC VARCHAR2(100)
341 , COUNTRY HR_LOCATIONS_ALL.COUNTRY%TYPE
342 , COUNTRY_DESC FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE
343 , ADDRESS_LINE_1 HR_LOCATIONS_ALL.ADDRESS_LINE_1%TYPE
344 , ADDRESS_LINE_2 HR_LOCATIONS_ALL.ADDRESS_LINE_2%TYPE
345 , ADDRESS_LINE_3 HR_LOCATIONS_ALL.ADDRESS_LINE_3%TYPE
346 , TOWN_OR_CITY HR_LOCATIONS_ALL.TOWN_OR_CITY%TYPE
347 , REGION_1 HR_LOCATIONS_ALL.REGION_1%TYPE
348 , REGION_2 HR_LOCATIONS_ALL.REGION_2%TYPE
349 , REGION_3 HR_LOCATIONS_ALL.REGION_3%TYPE
350 , POSTAL_CODE HR_LOCATIONS_ALL.POSTAL_CODE%TYPE
351 , TELEPHONE_NUMBER_1 HR_LOCATIONS_ALL.TELEPHONE_NUMBER_1%TYPE
352 , TELEPHONE_NUMBER_2 HR_LOCATIONS_ALL.TELEPHONE_NUMBER_2%TYPE
353 , TELEPHONE_NUMBER_3 HR_LOCATIONS_ALL.TELEPHONE_NUMBER_3%TYPE
354 , LOC_INFORMATION13 HR_LOCATIONS_ALL.LOC_INFORMATION13%TYPE
355 , LOC_INFORMATION14 HR_LOCATIONS_ALL.LOC_INFORMATION14%TYPE
356 , LOC_INFORMATION15 HR_LOCATIONS_ALL.LOC_INFORMATION15%TYPE
357 , LOC_INFORMATION16 HR_LOCATIONS_ALL.LOC_INFORMATION16%TYPE
358 , LOC_INFORMATION17 HR_LOCATIONS_ALL.LOC_INFORMATION17%TYPE
359 , LOC_INFORMATION18 HR_LOCATIONS_ALL.LOC_INFORMATION18%TYPE
360 , LOC_INFORMATION19 HR_LOCATIONS_ALL.LOC_INFORMATION19%TYPE
361 , LOC_INFORMATION20 HR_LOCATIONS_ALL.LOC_INFORMATION20%TYPE
362 );
363
364 /*Record type for assignment details with location address*/
365
366 TYPE ASSIGNMENT_DETAILS is RECORD
367 (ASG_DETAILS ASSGN_DETAILS,
368 ASG_LOC_ADDR ASSGN_LOCN_ADDRESS);
369
370 /*Record type for contact details*/
371 TYPE contact_details IS RECORD
372 (
373 CONTACT_RELATIONSHIP_ID PER_CONTACT_RELATIONSHIPS.CONTACT_RELATIONSHIP_ID%TYPE,
374 CONTACT_PERSON_ID PER_CONTACT_RELATIONSHIPS.CONTACT_PERSON_ID%TYPE,
375 CONTACT_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
376 CONTACT_TYPE PER_CONTACT_RELATIONSHIPS.CONTACT_TYPE%TYPE,
377 CONTACT_TYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE, -- 'CONTACT'
378 PRIMARY_CONTACT_FLAG PER_CONTACT_RELATIONSHIPS.PRIMARY_CONTACT_FLAG%TYPE,
379 DATE_START varchar2(10),
380 START_LIFE_REASON_ID PER_CONTACT_RELATIONSHIPS.START_LIFE_REASON_ID%TYPE,
381 DATE_END varchar2(10),
382 END_LIFE_REASON_ID PER_CONTACT_RELATIONSHIPS.END_LIFE_REASON_ID%TYPE,
383 RLTD_PER_RSDS_W_DSGNTR_FLAG PER_CONTACT_RELATIONSHIPS.RLTD_PER_RSDS_W_DSGNTR_FLAG%TYPE,
384 PERSONAL_FLAG PER_CONTACT_RELATIONSHIPS.PERSONAL_FLAG%TYPE,
385 SEQUENCE_NUMBER PER_CONTACT_RELATIONSHIPS.SEQUENCE_NUMBER%TYPE,
386 CONT_ATTRIBUTE_CATEGORY PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE_CATEGORY%TYPE,
387 CONT_ATTRIBUTE1 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE1%TYPE,
388 CONT_ATTRIBUTE2 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE2%TYPE,
389 CONT_ATTRIBUTE3 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE3%TYPE,
390 CONT_ATTRIBUTE4 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE4%TYPE,
391 CONT_ATTRIBUTE5 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE5%TYPE,
392 CONT_ATTRIBUTE6 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE6%TYPE,
393 CONT_ATTRIBUTE7 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE7%TYPE,
394 CONT_ATTRIBUTE8 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE8%TYPE,
395 CONT_ATTRIBUTE9 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE9%TYPE,
396 CONT_ATTRIBUTE10 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE10%TYPE,
397 CONT_ATTRIBUTE11 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE11%TYPE,
398 CONT_ATTRIBUTE12 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE12%TYPE,
399 CONT_ATTRIBUTE13 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE13%TYPE,
400 CONT_ATTRIBUTE14 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE14%TYPE,
401 CONT_ATTRIBUTE15 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE15%TYPE,
402 CONT_ATTRIBUTE16 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE16%TYPE,
403 CONT_ATTRIBUTE17 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE17%TYPE,
404 CONT_ATTRIBUTE18 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE18%TYPE,
405 CONT_ATTRIBUTE19 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE19%TYPE,
406 CONT_ATTRIBUTE20 PER_CONTACT_RELATIONSHIPS.CONT_ATTRIBUTE20%TYPE,
407 THIRD_PARTY_PAY_FLAG PER_CONTACT_RELATIONSHIPS.THIRD_PARTY_PAY_FLAG%TYPE,
408 BONDHOLDER_FLAG PER_CONTACT_RELATIONSHIPS.BONDHOLDER_FLAG%TYPE,
409 DEPENDENT_FLAG PER_CONTACT_RELATIONSHIPS.DEPENDENT_FLAG%TYPE,
410 BENEFICIARY_FLAG PER_CONTACT_RELATIONSHIPS.BENEFICIARY_FLAG%TYPE,
411 PARTY_ID PER_CONTACT_RELATIONSHIPS.PARTY_ID%TYPE,
412 CONT_INFORMATION_CATEGORY PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION_CATEGORY%TYPE,
413 CONT_INFORMATION1 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION1%TYPE,
414 CONT_INFORMATION2 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION2%TYPE,
415 CONT_INFORMATION3 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION3%TYPE,
416 CONT_INFORMATION4 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION4%TYPE,
417 CONT_INFORMATION5 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION5%TYPE,
418 CONT_INFORMATION6 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION6%TYPE,
419 CONT_INFORMATION7 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION7%TYPE,
420 CONT_INFORMATION8 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION8%TYPE,
421 CONT_INFORMATION9 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION9%TYPE,
422 CONT_INFORMATION10 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION10%TYPE,
423 CONT_INFORMATION11 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION11%TYPE,
424 CONT_INFORMATION12 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION12%TYPE,
425 CONT_INFORMATION13 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION13%TYPE,
426 CONT_INFORMATION14 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION14%TYPE,
427 CONT_INFORMATION15 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION15%TYPE,
428 CONT_INFORMATION16 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION16%TYPE,
429 CONT_INFORMATION17 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION17%TYPE,
430 CONT_INFORMATION18 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION18%TYPE,
431 CONT_INFORMATION19 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION19%TYPE,
432 CONT_INFORMATION20 PER_CONTACT_RELATIONSHIPS.CONT_INFORMATION20%TYPE
433 );
434
435 /*Record type for contact address details*/
436 TYPE CONTACT_ADDRESS_DETAILS is RECORD
437 (
438 CONTACT_RELATIONSHIP_ID PER_CONTACT_RELATIONSHIPS.CONTACT_RELATIONSHIP_ID%TYPE,
439 CONTACT_PERSON_ID PER_CONTACT_RELATIONSHIPS.CONTACT_PERSON_ID%TYPE,
440 ADDRESS_ID PER_ADDRESSES.ADDRESS_ID%TYPE,
441 DATE_FROM varchar2(10),
442 PRIMARY_FLAG PER_ADDRESSES.PRIMARY_FLAG%TYPE,
443 STYLE PER_ADDRESSES.STYLE%TYPE,
444 STYLE_DESC VARCHAR2(100), -- FROM FLEXFIELDS
445 ADDRESS_LINE1 PER_ADDRESSES.ADDRESS_LINE1%TYPE,
446 ADDRESS_LINE2 PER_ADDRESSES.ADDRESS_LINE2%TYPE,
447 ADDRESS_LINE3 PER_ADDRESSES.ADDRESS_LINE3%TYPE,
448 ADDRESS_TYPE PER_ADDRESSES.ADDRESS_TYPE%TYPE,
449 ADDRESS_TYPE_DESC FND_LOOKUP_VALUES.MEANING%TYPE, --(BASED ON LANG /'ADDRESS_TYPE')
450 COUNTRY PER_ADDRESSES.COUNTRY%TYPE,
451 COUNTRY_DESC FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE,
452 DATE_TO varchar2(10),
453 POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%TYPE,
454 REGION_1 PER_ADDRESSES.REGION_1%TYPE,
455 REGION_1_DESC VARCHAR2(240),
456 REGION_2 PER_ADDRESSES.REGION_2%TYPE,
457 REGION_2_DESC VARCHAR2(240),
458 REGION_3 PER_ADDRESSES.REGION_3%TYPE,
459 REGION_3_DESC VARCHAR2(240),
460 TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%TYPE,
461 TELEPHONE_NUMBER_2 PER_ADDRESSES.TELEPHONE_NUMBER_2%TYPE,
462 TELEPHONE_NUMBER_3 PER_ADDRESSES.TELEPHONE_NUMBER_3%TYPE,
463 TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%TYPE,
464 TOWN_OR_CITY_DESC VARCHAR2(240),
465 ADDR_ATTRIBUTE_CATEGORY PER_ADDRESSES.ADDR_ATTRIBUTE_CATEGORY%TYPE,
466 ADDR_ATTRIBUTE1 PER_ADDRESSES.ADDR_ATTRIBUTE1%TYPE,
467 ADDR_ATTRIBUTE2 PER_ADDRESSES.ADDR_ATTRIBUTE2%TYPE,
468 ADDR_ATTRIBUTE3 PER_ADDRESSES.ADDR_ATTRIBUTE3%TYPE,
469 ADDR_ATTRIBUTE4 PER_ADDRESSES.ADDR_ATTRIBUTE4%TYPE,
470 ADDR_ATTRIBUTE5 PER_ADDRESSES.ADDR_ATTRIBUTE5%TYPE,
471 ADDR_ATTRIBUTE6 PER_ADDRESSES.ADDR_ATTRIBUTE6%TYPE,
472 ADDR_ATTRIBUTE7 PER_ADDRESSES.ADDR_ATTRIBUTE7%TYPE,
473 ADDR_ATTRIBUTE8 PER_ADDRESSES.ADDR_ATTRIBUTE8%TYPE,
474 ADDR_ATTRIBUTE9 PER_ADDRESSES.ADDR_ATTRIBUTE9%TYPE,
475 ADDR_ATTRIBUTE10 PER_ADDRESSES.ADDR_ATTRIBUTE10%TYPE,
476 ADDR_ATTRIBUTE11 PER_ADDRESSES.ADDR_ATTRIBUTE11%TYPE,
477 ADDR_ATTRIBUTE12 PER_ADDRESSES.ADDR_ATTRIBUTE12%TYPE,
478 ADDR_ATTRIBUTE13 PER_ADDRESSES.ADDR_ATTRIBUTE13%TYPE,
479 ADDR_ATTRIBUTE14 PER_ADDRESSES.ADDR_ATTRIBUTE14%TYPE,
480 ADDR_ATTRIBUTE15 PER_ADDRESSES.ADDR_ATTRIBUTE15%TYPE,
481 ADDR_ATTRIBUTE16 PER_ADDRESSES.ADDR_ATTRIBUTE16%TYPE,
482 ADDR_ATTRIBUTE17 PER_ADDRESSES.ADDR_ATTRIBUTE17%TYPE,
483 ADDR_ATTRIBUTE18 PER_ADDRESSES.ADDR_ATTRIBUTE18%TYPE,
484 ADDR_ATTRIBUTE19 PER_ADDRESSES.ADDR_ATTRIBUTE19%TYPE,
485 ADDR_ATTRIBUTE20 PER_ADDRESSES.ADDR_ATTRIBUTE20%TYPE,
486 ADD_INFORMATION13 PER_ADDRESSES.ADD_INFORMATION13%TYPE,
487 ADD_INFORMATION14 PER_ADDRESSES.ADD_INFORMATION14%TYPE,
488 ADD_INFORMATION15 PER_ADDRESSES.ADD_INFORMATION15%TYPE,
489 ADD_INFORMATION16 PER_ADDRESSES.ADD_INFORMATION16%TYPE,
490 ADD_INFORMATION17 PER_ADDRESSES.ADD_INFORMATION17%TYPE,
491 ADD_INFORMATION18 PER_ADDRESSES.ADD_INFORMATION18%TYPE,
492 ADD_INFORMATION19 PER_ADDRESSES.ADD_INFORMATION19%TYPE,
493 ADD_INFORMATION20 PER_ADDRESSES.ADD_INFORMATION20%TYPE,
494 PARTY_ID PER_ADDRESSES.PARTY_ID%TYPE,
495 DERIVED_LOCALE PER_ADDRESSES.DERIVED_LOCALE%TYPE,
496 GEOMETRY PER_ADDRESSES.GEOMETRY%TYPE,
497 COMMENTS PER_ADDRESSES.COMMENTS%TYPE
498 );
499
500
501
502 /*Record type for address details*/
503 TYPE ADDRESS_DETAILS is RECORD
504 (
505 ADDRESS_ID PER_ADDRESSES.ADDRESS_ID%TYPE,
506 DATE_FROM varchar2(10),
507 PRIMARY_FLAG PER_ADDRESSES.PRIMARY_FLAG%TYPE,
508 STYLE PER_ADDRESSES.STYLE%TYPE,
509 STYLE_DESC VARCHAR2(100), -- FROM FLEXFIELDS
510 ADDRESS_LINE1 PER_ADDRESSES.ADDRESS_LINE1%TYPE,
511 ADDRESS_LINE2 PER_ADDRESSES.ADDRESS_LINE2%TYPE,
512 ADDRESS_LINE3 PER_ADDRESSES.ADDRESS_LINE3%TYPE,
513 ADDRESS_TYPE PER_ADDRESSES.ADDRESS_TYPE%TYPE,
514 ADDRESS_TYPE_DESC FND_LOOKUP_VALUES.MEANING%TYPE, --(BASED ON LANG /'ADDRESS_TYPE')
515 COUNTRY PER_ADDRESSES.COUNTRY%TYPE,
516 COUNTRY_DESC FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE,
517 DATE_TO varchar2(10),
518 POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%TYPE,
519 REGION_1 PER_ADDRESSES.REGION_1%TYPE,
520 REGION_1_DESC VARCHAR2(240),
521 REGION_2 PER_ADDRESSES.REGION_2%TYPE,
522 REGION_2_DESC VARCHAR2(240),
523 REGION_3 PER_ADDRESSES.REGION_3%TYPE,
524 REGION_3_DESC VARCHAR2(240),
525 TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%TYPE,
526 TELEPHONE_NUMBER_2 PER_ADDRESSES.TELEPHONE_NUMBER_2%TYPE,
527 TELEPHONE_NUMBER_3 PER_ADDRESSES.TELEPHONE_NUMBER_3%TYPE,
528 TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%TYPE,
529 TOWN_OR_CITY_DESC VARCHAR2(240),
530 ADDR_ATTRIBUTE_CATEGORY PER_ADDRESSES.ADDR_ATTRIBUTE_CATEGORY%TYPE,
531 ADDR_ATTRIBUTE1 PER_ADDRESSES.ADDR_ATTRIBUTE1%TYPE,
532 ADDR_ATTRIBUTE2 PER_ADDRESSES.ADDR_ATTRIBUTE2%TYPE,
533 ADDR_ATTRIBUTE3 PER_ADDRESSES.ADDR_ATTRIBUTE3%TYPE,
534 ADDR_ATTRIBUTE4 PER_ADDRESSES.ADDR_ATTRIBUTE4%TYPE,
535 ADDR_ATTRIBUTE5 PER_ADDRESSES.ADDR_ATTRIBUTE5%TYPE,
536 ADDR_ATTRIBUTE6 PER_ADDRESSES.ADDR_ATTRIBUTE6%TYPE,
537 ADDR_ATTRIBUTE7 PER_ADDRESSES.ADDR_ATTRIBUTE7%TYPE,
538 ADDR_ATTRIBUTE8 PER_ADDRESSES.ADDR_ATTRIBUTE8%TYPE,
539 ADDR_ATTRIBUTE9 PER_ADDRESSES.ADDR_ATTRIBUTE9%TYPE,
540 ADDR_ATTRIBUTE10 PER_ADDRESSES.ADDR_ATTRIBUTE10%TYPE,
541 ADDR_ATTRIBUTE11 PER_ADDRESSES.ADDR_ATTRIBUTE11%TYPE,
542 ADDR_ATTRIBUTE12 PER_ADDRESSES.ADDR_ATTRIBUTE12%TYPE,
543 ADDR_ATTRIBUTE13 PER_ADDRESSES.ADDR_ATTRIBUTE13%TYPE,
544 ADDR_ATTRIBUTE14 PER_ADDRESSES.ADDR_ATTRIBUTE14%TYPE,
545 ADDR_ATTRIBUTE15 PER_ADDRESSES.ADDR_ATTRIBUTE15%TYPE,
546 ADDR_ATTRIBUTE16 PER_ADDRESSES.ADDR_ATTRIBUTE16%TYPE,
547 ADDR_ATTRIBUTE17 PER_ADDRESSES.ADDR_ATTRIBUTE17%TYPE,
548 ADDR_ATTRIBUTE18 PER_ADDRESSES.ADDR_ATTRIBUTE18%TYPE,
549 ADDR_ATTRIBUTE19 PER_ADDRESSES.ADDR_ATTRIBUTE19%TYPE,
550 ADDR_ATTRIBUTE20 PER_ADDRESSES.ADDR_ATTRIBUTE20%TYPE,
551 ADD_INFORMATION13 PER_ADDRESSES.ADD_INFORMATION13%TYPE,
552 ADD_INFORMATION14 PER_ADDRESSES.ADD_INFORMATION14%TYPE,
553 ADD_INFORMATION15 PER_ADDRESSES.ADD_INFORMATION15%TYPE,
554 ADD_INFORMATION16 PER_ADDRESSES.ADD_INFORMATION16%TYPE,
555 ADD_INFORMATION17 PER_ADDRESSES.ADD_INFORMATION17%TYPE,
556 ADD_INFORMATION18 PER_ADDRESSES.ADD_INFORMATION18%TYPE,
557 ADD_INFORMATION19 PER_ADDRESSES.ADD_INFORMATION19%TYPE,
558 ADD_INFORMATION20 PER_ADDRESSES.ADD_INFORMATION20%TYPE,
559 PARTY_ID PER_ADDRESSES.PARTY_ID%TYPE,
560 DERIVED_LOCALE PER_ADDRESSES.DERIVED_LOCALE%TYPE,
561 GEOMETRY PER_ADDRESSES.GEOMETRY%TYPE,
562 COMMENTS PER_ADDRESSES.COMMENTS%TYPE
563
564 );
565 /*Record type for person extra information details*/
566 TYPE PERSON_EXTRA_INFO_DETAILS IS RECORD
567 (
568 PERSON_EXTRA_INFO_ID PER_PEOPLE_EXTRA_INFO.PERSON_EXTRA_INFO_ID%TYPE,
569 INFORMATION_TYPE PER_PEOPLE_EXTRA_INFO.INFORMATION_TYPE%TYPE,
570 PEI_INFORMATION_CATEGORY PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION_CATEGORY%TYPE,
571 PEI_INFORMATION1 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION1%TYPE,
572 PEI_INFORMATION2 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION2%TYPE,
573 PEI_INFORMATION3 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION3%TYPE,
574 PEI_INFORMATION4 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION4%TYPE,
575 PEI_INFORMATION5 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION5%TYPE,
576 PEI_INFORMATION6 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION6%TYPE,
577 PEI_INFORMATION7 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION7%TYPE,
578 PEI_INFORMATION8 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION8%TYPE,
579 PEI_INFORMATION9 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION9%TYPE,
580 PEI_INFORMATION10 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION10%TYPE,
581 PEI_INFORMATION11 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION11%TYPE,
582 PEI_INFORMATION12 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION12%TYPE,
583 PEI_INFORMATION13 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION13%TYPE,
584 PEI_INFORMATION14 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION14%TYPE,
585 PEI_INFORMATION15 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION15%TYPE,
586 PEI_INFORMATION16 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION16%TYPE,
587 PEI_INFORMATION17 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION17%TYPE,
588 PEI_INFORMATION18 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION18%TYPE,
589 PEI_INFORMATION19 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION19%TYPE,
590 PEI_INFORMATION20 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION20%TYPE,
591 PEI_INFORMATION21 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION21%TYPE,
592 PEI_INFORMATION22 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION22%TYPE,
593 PEI_INFORMATION23 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION23%TYPE,
594 PEI_INFORMATION24 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION24%TYPE,
595 PEI_INFORMATION25 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION25%TYPE,
596 PEI_INFORMATION26 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION26%TYPE,
597 PEI_INFORMATION27 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION27%TYPE,
598 PEI_INFORMATION28 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION28%TYPE,
599 PEI_INFORMATION29 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION29%TYPE,
600 PEI_INFORMATION30 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION30%TYPE,
601 PEI_ATTRIBUTE_CATEGORY PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE_CATEGORY%TYPE,
602 PEI_ATTRIBUTE1 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE1%TYPE,
603 PEI_ATTRIBUTE2 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE2%TYPE,
604 PEI_ATTRIBUTE3 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE3%TYPE,
605 PEI_ATTRIBUTE4 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE4%TYPE,
606 PEI_ATTRIBUTE5 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE5%TYPE,
607 PEI_ATTRIBUTE6 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE6%TYPE,
608 PEI_ATTRIBUTE7 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE7%TYPE,
609 PEI_ATTRIBUTE8 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE8%TYPE,
610 PEI_ATTRIBUTE9 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE9%TYPE,
611 PEI_ATTRIBUTE10 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE10%TYPE,
612 PEI_ATTRIBUTE11 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE11%TYPE,
613 PEI_ATTRIBUTE12 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE12%TYPE,
614 PEI_ATTRIBUTE13 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE13%TYPE,
615 PEI_ATTRIBUTE14 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE14%TYPE,
616 PEI_ATTRIBUTE15 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE15%TYPE,
617 PEI_ATTRIBUTE16 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE16%TYPE,
618 PEI_ATTRIBUTE17 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE17%TYPE,
619 PEI_ATTRIBUTE18 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE18%TYPE,
620 PEI_ATTRIBUTE19 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE19%TYPE,
621 PEI_ATTRIBUTE20 PER_PEOPLE_EXTRA_INFO.PEI_ATTRIBUTE20%TYPE,
622 PARTY_ID PER_PEOPLE_EXTRA_INFO.PARTY_ID%TYPE
623 );
624
625 /*Record type for special information details*/
626 TYPE SPECIAL_INFORMATION_DETAILS is record
627 (
628 PERSON_ANALYSIS_ID PER_PERSON_ANALYSES.PERSON_ANALYSIS_ID%TYPE,
629 ANALYSIS_CRITERIA_ID PER_PERSON_ANALYSES.ANALYSIS_CRITERIA_ID%TYPE,
630 DATE_FROM varchar2(10),
631 DATE_TO varchar2(10),
632 ID_FLEX_NUM PER_PERSON_ANALYSES.ID_FLEX_NUM%TYPE,
633 SEGMENT1 PER_ANALYSIS_CRITERIA.SEGMENT1%TYPE,
634 SEGMENT2 PER_ANALYSIS_CRITERIA.SEGMENT2%TYPE,
635 SEGMENT3 PER_ANALYSIS_CRITERIA.SEGMENT3%TYPE,
636 SEGMENT4 PER_ANALYSIS_CRITERIA.SEGMENT4%TYPE,
637 SEGMENT5 PER_ANALYSIS_CRITERIA.SEGMENT5%TYPE,
638 SEGMENT6 PER_ANALYSIS_CRITERIA.SEGMENT6%TYPE,
639 SEGMENT7 PER_ANALYSIS_CRITERIA.SEGMENT7%TYPE,
640 SEGMENT8 PER_ANALYSIS_CRITERIA.SEGMENT8%TYPE,
641 SEGMENT9 PER_ANALYSIS_CRITERIA.SEGMENT9%TYPE,
642 SEGMENT10 PER_ANALYSIS_CRITERIA.SEGMENT10%TYPE,
643 SEGMENT11 PER_ANALYSIS_CRITERIA.SEGMENT11%TYPE,
644 SEGMENT12 PER_ANALYSIS_CRITERIA.SEGMENT12%TYPE,
645 SEGMENT13 PER_ANALYSIS_CRITERIA.SEGMENT13%TYPE,
646 SEGMENT14 PER_ANALYSIS_CRITERIA.SEGMENT14%TYPE,
647 SEGMENT15 PER_ANALYSIS_CRITERIA.SEGMENT15%TYPE,
648 SEGMENT16 PER_ANALYSIS_CRITERIA.SEGMENT16%TYPE,
649 SEGMENT17 PER_ANALYSIS_CRITERIA.SEGMENT17%TYPE,
650 SEGMENT18 PER_ANALYSIS_CRITERIA.SEGMENT18%TYPE,
651 SEGMENT19 PER_ANALYSIS_CRITERIA.SEGMENT19%TYPE,
652 SEGMENT20 PER_ANALYSIS_CRITERIA.SEGMENT20%TYPE,
653 SEGMENT21 PER_ANALYSIS_CRITERIA.SEGMENT21%TYPE,
654 SEGMENT22 PER_ANALYSIS_CRITERIA.SEGMENT22%TYPE,
655 SEGMENT23 PER_ANALYSIS_CRITERIA.SEGMENT23%TYPE,
656 SEGMENT24 PER_ANALYSIS_CRITERIA.SEGMENT24%TYPE,
657 SEGMENT25 PER_ANALYSIS_CRITERIA.SEGMENT25%TYPE,
658 SEGMENT26 PER_ANALYSIS_CRITERIA.SEGMENT26%TYPE,
659 SEGMENT27 PER_ANALYSIS_CRITERIA.SEGMENT27%TYPE,
660 SEGMENT28 PER_ANALYSIS_CRITERIA.SEGMENT28%TYPE,
661 SEGMENT29 PER_ANALYSIS_CRITERIA.SEGMENT29%TYPE,
662 SEGMENT30 PER_ANALYSIS_CRITERIA.SEGMENT30%TYPE,
663 ATTRIBUTE_CATEGORY PER_PERSON_ANALYSES.ATTRIBUTE_CATEGORY%TYPE,
664 ATTRIBUTE1 PER_PERSON_ANALYSES.ATTRIBUTE1%TYPE,
665 ATTRIBUTE2 PER_PERSON_ANALYSES.ATTRIBUTE2%TYPE,
666 ATTRIBUTE3 PER_PERSON_ANALYSES.ATTRIBUTE3%TYPE,
667 ATTRIBUTE4 PER_PERSON_ANALYSES.ATTRIBUTE4%TYPE,
668 ATTRIBUTE5 PER_PERSON_ANALYSES.ATTRIBUTE5%TYPE,
669 ATTRIBUTE6 PER_PERSON_ANALYSES.ATTRIBUTE6%TYPE,
670 ATTRIBUTE7 PER_PERSON_ANALYSES.ATTRIBUTE7%TYPE,
671 ATTRIBUTE8 PER_PERSON_ANALYSES.ATTRIBUTE8%TYPE,
672 ATTRIBUTE9 PER_PERSON_ANALYSES.ATTRIBUTE9%TYPE,
673 ATTRIBUTE10 PER_PERSON_ANALYSES.ATTRIBUTE10%TYPE,
674 ATTRIBUTE11 PER_PERSON_ANALYSES.ATTRIBUTE11%TYPE,
675 ATTRIBUTE12 PER_PERSON_ANALYSES.ATTRIBUTE12%TYPE,
676 ATTRIBUTE13 PER_PERSON_ANALYSES.ATTRIBUTE13%TYPE,
677 ATTRIBUTE14 PER_PERSON_ANALYSES.ATTRIBUTE14%TYPE,
678 ATTRIBUTE15 PER_PERSON_ANALYSES.ATTRIBUTE15%TYPE,
679 ATTRIBUTE16 PER_PERSON_ANALYSES.ATTRIBUTE16%TYPE,
680 ATTRIBUTE17 PER_PERSON_ANALYSES.ATTRIBUTE17%TYPE,
681 ATTRIBUTE18 PER_PERSON_ANALYSES.ATTRIBUTE18%TYPE,
682 ATTRIBUTE19 PER_PERSON_ANALYSES.ATTRIBUTE19%TYPE,
683 ATTRIBUTE20 PER_PERSON_ANALYSES.ATTRIBUTE20%TYPE,
684 PARTY_ID PER_PERSON_ANALYSES.PARTY_ID%TYPE,
685 COMMENTS PER_PERSON_ANALYSES.COMMENTS%TYPE
686 );
687
688 /*Record type for work incidents*/
689 TYPE WORK_INC_INFO is Record
690 (INCIDENT_ID PER_WORK_INCIDENTS.INCIDENT_ID%TYPE,
691 INCIDENT_REFERENCE PER_WORK_INCIDENTS.INCIDENT_REFERENCE%TYPE,
692 INCIDENT_TYPE PER_WORK_INCIDENTS.INCIDENT_TYPE%TYPE,
693 INCIDENT_TYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
694 INCIDENT_DATE varchar2(10),
695 INCIDENT_TIME PER_WORK_INCIDENTS.INCIDENT_TIME%TYPE,
696 ASSIGNMENT_ID PER_WORK_INCIDENTS.ASSIGNMENT_ID%TYPE,
697 LOCATION PER_WORK_INCIDENTS.LOCATION%TYPE,
698 AT_WORK_FLAG PER_WORK_INCIDENTS.AT_WORK_FLAG%TYPE,
699 AT_WORK_FLAG_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
700 LAST_WORK_DATE varchar2(10),
701 LAST_WORK_TIME PER_WORK_INCIDENTS.LAST_WORK_TIME%TYPE,
702 REPORT_DATE varchar2(10),
703 REPORT_TIME PER_WORK_INCIDENTS.REPORT_TIME%TYPE,
704 REPORT_METHOD PER_WORK_INCIDENTS.REPORT_METHOD%TYPE,
705 PERSON_REPORTED_BY PER_WORK_INCIDENTS.PERSON_REPORTED_BY%TYPE,
706 PERSON_REPORTED_BY_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
707 PERSON_REPORTED_TO PER_WORK_INCIDENTS.PERSON_REPORTED_TO%TYPE,
708 WITNESS_DETAILS PER_WORK_INCIDENTS.WITNESS_DETAILS%TYPE,
709 DESCRIPTION PER_WORK_INCIDENTS.DESCRIPTION%TYPE,
710 INJURY_TYPE PER_WORK_INCIDENTS.INJURY_TYPE%TYPE,
711 INJURY_TYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
712 DISEASE_TYPE PER_WORK_INCIDENTS.DISEASE_TYPE%TYPE,
713 DISEASE_TYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
714 HAZARD_TYPE PER_WORK_INCIDENTS.HAZARD_TYPE%TYPE,
715 HAZARD_TYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
716 BODY_PART PER_WORK_INCIDENTS.BODY_PART%TYPE,
717 TREATMENT_RECEIVED_FLAG PER_WORK_INCIDENTS.TREATMENT_RECEIVED_FLAG%TYPE,
718 HOSPITAL_DETAILS PER_WORK_INCIDENTS.HOSPITAL_DETAILS%TYPE,
719 DOCTOR_ID PER_WORK_INCIDENTS.DOCTOR_ID%TYPE,
720 NEXT_OF_KIN_ID PER_WORK_INCIDENTS.NEXT_OF_KIN_ID%TYPE,
721 ABSENCE_ID PER_WORK_INCIDENTS.ABSENCE_ID%TYPE,
722 COMPENSATION_DATE varchar2(10),
723 COMPENSATION_CURRENCY PER_WORK_INCIDENTS.COMPENSATION_CURRENCY%TYPE,
724 COMPENSATION_AMOUNT PER_WORK_INCIDENTS.COMPENSATION_AMOUNT%TYPE,
725 REMEDIAL_HS_ACTION PER_WORK_INCIDENTS.REMEDIAL_HS_ACTION%TYPE,
726 NOTIFIED_HSREP_ID PER_WORK_INCIDENTS.NOTIFIED_HSREP_ID%TYPE,
727 NOTIFIED_HSREP_DATE varchar2(10),
728 NOTIFIED_UREP_ID PER_WORK_INCIDENTS.NOTIFIED_UREP_ID%TYPE,
729 NOTIFIED_UREP_DATE varchar2(10),
730 PREVIOUS_INCIDENT_ID PER_WORK_INCIDENTS.PREVIOUS_INCIDENT_ID%TYPE,
731 ATTRIBUTE_CATEGORY PER_WORK_INCIDENTS.ATTRIBUTE_CATEGORY%TYPE,
732 ATTRIBUTE1 PER_WORK_INCIDENTS.ATTRIBUTE1%TYPE,
733 ATTRIBUTE2 PER_WORK_INCIDENTS.ATTRIBUTE2%TYPE,
734 ATTRIBUTE3 PER_WORK_INCIDENTS.ATTRIBUTE3%TYPE,
735 ATTRIBUTE4 PER_WORK_INCIDENTS.ATTRIBUTE4%TYPE,
736 ATTRIBUTE5 PER_WORK_INCIDENTS.ATTRIBUTE5%TYPE,
737 ATTRIBUTE6 PER_WORK_INCIDENTS.ATTRIBUTE6%TYPE,
738 ATTRIBUTE7 PER_WORK_INCIDENTS.ATTRIBUTE7%TYPE,
739 ATTRIBUTE8 PER_WORK_INCIDENTS.ATTRIBUTE8%TYPE,
740 ATTRIBUTE9 PER_WORK_INCIDENTS.ATTRIBUTE9%TYPE,
741 ATTRIBUTE10 PER_WORK_INCIDENTS.ATTRIBUTE10%TYPE,
742 ATTRIBUTE11 PER_WORK_INCIDENTS.ATTRIBUTE11%TYPE,
743 ATTRIBUTE12 PER_WORK_INCIDENTS.ATTRIBUTE12%TYPE,
744 ATTRIBUTE13 PER_WORK_INCIDENTS.ATTRIBUTE13%TYPE,
745 ATTRIBUTE14 PER_WORK_INCIDENTS.ATTRIBUTE14%TYPE,
746 ATTRIBUTE15 PER_WORK_INCIDENTS.ATTRIBUTE15%TYPE,
747 ATTRIBUTE16 PER_WORK_INCIDENTS.ATTRIBUTE16%TYPE,
748 ATTRIBUTE17 PER_WORK_INCIDENTS.ATTRIBUTE17%TYPE,
749 ATTRIBUTE18 PER_WORK_INCIDENTS.ATTRIBUTE18%TYPE,
750 ATTRIBUTE19 PER_WORK_INCIDENTS.ATTRIBUTE19%TYPE,
751 ATTRIBUTE20 PER_WORK_INCIDENTS.ATTRIBUTE20%TYPE,
752 ATTRIBUTE21 PER_WORK_INCIDENTS.ATTRIBUTE21%TYPE,
753 ATTRIBUTE22 PER_WORK_INCIDENTS.ATTRIBUTE22%TYPE,
754 ATTRIBUTE23 PER_WORK_INCIDENTS.ATTRIBUTE23%TYPE,
755 ATTRIBUTE24 PER_WORK_INCIDENTS.ATTRIBUTE24%TYPE,
756 ATTRIBUTE25 PER_WORK_INCIDENTS.ATTRIBUTE25%TYPE,
757 ATTRIBUTE26 PER_WORK_INCIDENTS.ATTRIBUTE26%TYPE,
758 ATTRIBUTE27 PER_WORK_INCIDENTS.ATTRIBUTE27%TYPE,
759 ATTRIBUTE28 PER_WORK_INCIDENTS.ATTRIBUTE28%TYPE,
760 ATTRIBUTE29 PER_WORK_INCIDENTS.ATTRIBUTE29%TYPE,
761 ATTRIBUTE30 PER_WORK_INCIDENTS.ATTRIBUTE30%TYPE,
762 INC_INFORMATION_CATEGORY PER_WORK_INCIDENTS.INC_INFORMATION_CATEGORY%TYPE,
763 INC_INFORMATION1 PER_WORK_INCIDENTS.INC_INFORMATION1%TYPE,
764 INC_INFORMATION2 PER_WORK_INCIDENTS.INC_INFORMATION2%TYPE,
765 INC_INFORMATION3 PER_WORK_INCIDENTS.INC_INFORMATION3%TYPE,
766 INC_INFORMATION4 PER_WORK_INCIDENTS.INC_INFORMATION4%TYPE,
767 INC_INFORMATION5 PER_WORK_INCIDENTS.INC_INFORMATION5%TYPE,
768 INC_INFORMATION6 PER_WORK_INCIDENTS.INC_INFORMATION6%TYPE,
769 INC_INFORMATION7 PER_WORK_INCIDENTS.INC_INFORMATION7%TYPE,
770 INC_INFORMATION8 PER_WORK_INCIDENTS.INC_INFORMATION8%TYPE,
771 INC_INFORMATION9 PER_WORK_INCIDENTS.INC_INFORMATION9%TYPE,
772 INC_INFORMATION10 PER_WORK_INCIDENTS.INC_INFORMATION10%TYPE,
773 INC_INFORMATION11 PER_WORK_INCIDENTS.INC_INFORMATION11%TYPE,
774 INC_INFORMATION12 PER_WORK_INCIDENTS.INC_INFORMATION12%TYPE,
775 INC_INFORMATION13 PER_WORK_INCIDENTS.INC_INFORMATION13%TYPE,
776 INC_INFORMATION14 PER_WORK_INCIDENTS.INC_INFORMATION14%TYPE,
777 INC_INFORMATION15 PER_WORK_INCIDENTS.INC_INFORMATION15%TYPE,
778 INC_INFORMATION16 PER_WORK_INCIDENTS.INC_INFORMATION16%TYPE,
779 INC_INFORMATION17 PER_WORK_INCIDENTS.INC_INFORMATION17%TYPE,
780 INC_INFORMATION18 PER_WORK_INCIDENTS.INC_INFORMATION18%TYPE,
781 INC_INFORMATION19 PER_WORK_INCIDENTS.INC_INFORMATION19%TYPE,
782 INC_INFORMATION20 PER_WORK_INCIDENTS.INC_INFORMATION20%TYPE,
783 INC_INFORMATION21 PER_WORK_INCIDENTS.INC_INFORMATION21%TYPE,
784 INC_INFORMATION22 PER_WORK_INCIDENTS.INC_INFORMATION22%TYPE,
785 INC_INFORMATION23 PER_WORK_INCIDENTS.INC_INFORMATION23%TYPE,
786 INC_INFORMATION24 PER_WORK_INCIDENTS.INC_INFORMATION24%TYPE,
787 INC_INFORMATION25 PER_WORK_INCIDENTS.INC_INFORMATION25%TYPE,
788 INC_INFORMATION26 PER_WORK_INCIDENTS.INC_INFORMATION26%TYPE,
789 INC_INFORMATION27 PER_WORK_INCIDENTS.INC_INFORMATION27%TYPE,
790 INC_INFORMATION28 PER_WORK_INCIDENTS.INC_INFORMATION28%TYPE,
791 INC_INFORMATION29 PER_WORK_INCIDENTS.INC_INFORMATION29%TYPE,
792 INC_INFORMATION30 PER_WORK_INCIDENTS.INC_INFORMATION30%TYPE,
793 ORG_NOTIFIED_DATE varchar2(10),
794 DOCTOR_NAME PER_WORK_INCIDENTS.DOCTOR_NAME%TYPE,
795 NOTIFIED_REP_ID PER_WORK_INCIDENTS.NOTIFIED_REP_ID%TYPE,
796 NOTIFIED_REP_DATE varchar2(10),
797 NOTIFIED_REP_ORG_ID PER_WORK_INCIDENTS.NOTIFIED_REP_ORG_ID%TYPE,
798 RELATED_INCIDENT_ID PER_WORK_INCIDENTS.RELATED_INCIDENT_ID%TYPE,
799 OVER_TIME_FLAG PER_WORK_INCIDENTS.OVER_TIME_FLAG%TYPE,
800 ABSENCE_EXISTS_FLAG PER_WORK_INCIDENTS.ABSENCE_EXISTS_FLAG%TYPE,
801 EMERGENCY_CODE PER_WORK_INCIDENTS.EMERGENCY_CODE%TYPE,
802 PRIVACY_ISSUE PER_WORK_INCIDENTS.PRIVACY_ISSUE%TYPE,
803 OBJECTS_INVOLVED PER_WORK_INCIDENTS.OBJECTS_INVOLVED%TYPE,
804 ACTIVITY_AT_TIME_OF_WORK PER_WORK_INCIDENTS.ACTIVITY_AT_TIME_OF_WORK%TYPE,
805 HOSPITAL_ADDRESS PER_WORK_INCIDENTS.HOSPITAL_ADDRESS%TYPE,
806 DAYS_RESTRICTED_WORK PER_WORK_INCIDENTS.DAYS_RESTRICTED_WORK%TYPE,
807 HOSPITALIZED_FLAG PER_WORK_INCIDENTS.HOSPITALIZED_FLAG%TYPE,
808 DATE_OF_DEATH varchar2(10),
809 DAYS_AWAY_FROM_WORK PER_WORK_INCIDENTS.DAYS_AWAY_FROM_WORK%TYPE,
810 WORK_START_TIME PER_WORK_INCIDENTS.WORK_START_TIME%TYPE,
811 REPORTING_PERSON_PHONE PER_WORK_INCIDENTS.REPORTING_PERSON_PHONE%TYPE,
812 REPORTING_PERSON_TITLE PER_WORK_INCIDENTS.REPORTING_PERSON_TITLE%TYPE,
813 REPORT_COMPLETED_BY PER_WORK_INCIDENTS.REPORT_COMPLETED_BY%TYPE);
814
815
816 /*Record type for Previosu employment details*/
817 TYPE PREV_EMP_INFO IS RECORD
818 (
819 PREVIOUS_EMPLOYER_ID PER_PREVIOUS_EMPLOYERS.PREVIOUS_EMPLOYER_ID%TYPE,
820 PARTY_ID PER_PREVIOUS_EMPLOYERS.PARTY_ID%TYPE,
821 START_DATE varchar2(10),
822 END_DATE varchar2(10),
823 PERIOD_YEARS PER_PREVIOUS_EMPLOYERS.PERIOD_YEARS%TYPE,
824 PERIOD_DAYS PER_PREVIOUS_EMPLOYERS.PERIOD_DAYS%TYPE,
825 EMPLOYER_NAME PER_PREVIOUS_EMPLOYERS.EMPLOYER_NAME%TYPE,
826 EMPLOYER_COUNTRY PER_PREVIOUS_EMPLOYERS.EMPLOYER_COUNTRY%TYPE,
827 EMPLOYER_COUNTRY_DESC FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE,
828 EMPLOYER_ADDRESS PER_PREVIOUS_EMPLOYERS.EMPLOYER_ADDRESS%TYPE,
829 EMPLOYER_TYPE PER_PREVIOUS_EMPLOYERS.EMPLOYER_TYPE%TYPE,
830 EMPLOYER_TYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
831 EMPLOYER_SUBTYPE PER_PREVIOUS_EMPLOYERS.EMPLOYER_SUBTYPE%TYPE,
832 EMPLOYER_SUBTYPE_DESC HR_LEG_LOOKUPS.MEANING%TYPE,
833 DESCRIPTION PER_PREVIOUS_EMPLOYERS.DESCRIPTION%TYPE,
834 PEM_ATTRIBUTE_CATEGORY PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE_CATEGORY%TYPE,
835 PEM_ATTRIBUTE1 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE1%TYPE,
836 PEM_ATTRIBUTE2 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE2%TYPE,
837 PEM_ATTRIBUTE3 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE3%TYPE,
838 PEM_ATTRIBUTE4 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE4%TYPE,
839 PEM_ATTRIBUTE5 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE5%TYPE,
840 PEM_ATTRIBUTE6 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE6%TYPE,
841 PEM_ATTRIBUTE7 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE7%TYPE,
842 PEM_ATTRIBUTE8 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE8%TYPE,
843 PEM_ATTRIBUTE9 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE9%TYPE,
844 PEM_ATTRIBUTE10 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE10%TYPE,
845 PEM_ATTRIBUTE11 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE11%TYPE,
846 PEM_ATTRIBUTE12 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE12%TYPE,
847 PEM_ATTRIBUTE13 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE13%TYPE,
848 PEM_ATTRIBUTE14 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE14%TYPE,
849 PEM_ATTRIBUTE15 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE15%TYPE,
850 PEM_ATTRIBUTE16 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE16%TYPE,
851 PEM_ATTRIBUTE17 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE17%TYPE,
852 PEM_ATTRIBUTE18 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE18%TYPE,
853 PEM_ATTRIBUTE19 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE19%TYPE,
854 PEM_ATTRIBUTE20 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE20%TYPE,
855 PEM_ATTRIBUTE21 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE21%TYPE,
856 PEM_ATTRIBUTE22 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE22%TYPE,
857 PEM_ATTRIBUTE23 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE23%TYPE,
858 PEM_ATTRIBUTE24 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE24%TYPE,
859 PEM_ATTRIBUTE25 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE25%TYPE,
860 PEM_ATTRIBUTE26 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE26%TYPE,
861 PEM_ATTRIBUTE27 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE27%TYPE,
862 PEM_ATTRIBUTE28 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE28%TYPE,
863 PEM_ATTRIBUTE29 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE29%TYPE,
864 PEM_ATTRIBUTE30 PER_PREVIOUS_EMPLOYERS.PEM_ATTRIBUTE30%TYPE,
865 PEM_INFORMATION_CATEGORY PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION_CATEGORY%TYPE,
866 PEM_INFORMATION1 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION1%TYPE,
867 PEM_INFORMATION2 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION2%TYPE,
868 PEM_INFORMATION3 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION3%TYPE,
869 PEM_INFORMATION4 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION4%TYPE,
870 PEM_INFORMATION5 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION5%TYPE,
871 PEM_INFORMATION6 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION6%TYPE,
872 PEM_INFORMATION7 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION7%TYPE,
873 PEM_INFORMATION8 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION8%TYPE,
874 PEM_INFORMATION9 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION9%TYPE,
875 PEM_INFORMATION10 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION10%TYPE,
876 PEM_INFORMATION11 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION11%TYPE,
877 PEM_INFORMATION12 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION12%TYPE,
878 PEM_INFORMATION13 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION13%TYPE,
879 PEM_INFORMATION14 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION14%TYPE,
880 PEM_INFORMATION15 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION15%TYPE,
881 PEM_INFORMATION16 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION16%TYPE,
882 PEM_INFORMATION17 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION17%TYPE,
883 PEM_INFORMATION18 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION18%TYPE,
884 PEM_INFORMATION19 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION19%TYPE,
885 PEM_INFORMATION20 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION20%TYPE,
886 PEM_INFORMATION21 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION21%TYPE,
887 PEM_INFORMATION22 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION22%TYPE,
888 PEM_INFORMATION23 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION23%TYPE,
889 PEM_INFORMATION24 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION24%TYPE,
890 PEM_INFORMATION25 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION25%TYPE,
891 PEM_INFORMATION26 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION26%TYPE,
892 PEM_INFORMATION27 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION27%TYPE,
893 PEM_INFORMATION28 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION28%TYPE,
894 PEM_INFORMATION29 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION29%TYPE,
895 PEM_INFORMATION30 PER_PREVIOUS_EMPLOYERS.PEM_INFORMATION30%TYPE,
896 ALL_ASSIGNMENTS PER_PREVIOUS_EMPLOYERS.ALL_ASSIGNMENTS%TYPE,
897 PERIOD_MONTHS PER_PREVIOUS_EMPLOYERS.PERIOD_MONTHS%TYPE
898 );
899
900 TYPE DELIV_METHOD_INFO is RECORD
901 (DELIVERY_METHOD_ID PER_PERSON_DLVRY_METHODS.DELIVERY_METHOD_ID%TYPE,
902 DATE_START varchar2(10),
903 DATE_END varchar2(10),
904 COMM_DLVRY_METHOD PER_PERSON_DLVRY_METHODS.COMM_DLVRY_METHOD%TYPE,
905 COMM_DLVRY_METHOD_DESC HR_LOOKUPS.MEANING%TYPE,
906 PREFERRED_FLAG PER_PERSON_DLVRY_METHODS.PREFERRED_FLAG%TYPE,
907 ATTRIBUTE_CATEGORY PER_PERSON_DLVRY_METHODS.ATTRIBUTE_CATEGORY%TYPE,
908 ATTRIBUTE1 PER_PERSON_DLVRY_METHODS.ATTRIBUTE1%TYPE,
909 ATTRIBUTE2 PER_PERSON_DLVRY_METHODS.ATTRIBUTE2%TYPE,
910 ATTRIBUTE3 PER_PERSON_DLVRY_METHODS.ATTRIBUTE3%TYPE,
911 ATTRIBUTE4 PER_PERSON_DLVRY_METHODS.ATTRIBUTE4%TYPE,
912 ATTRIBUTE5 PER_PERSON_DLVRY_METHODS.ATTRIBUTE5%TYPE,
913 ATTRIBUTE6 PER_PERSON_DLVRY_METHODS.ATTRIBUTE6%TYPE,
914 ATTRIBUTE7 PER_PERSON_DLVRY_METHODS.ATTRIBUTE7%TYPE,
915 ATTRIBUTE8 PER_PERSON_DLVRY_METHODS.ATTRIBUTE8%TYPE,
916 ATTRIBUTE9 PER_PERSON_DLVRY_METHODS.ATTRIBUTE9%TYPE,
917 ATTRIBUTE10 PER_PERSON_DLVRY_METHODS.ATTRIBUTE10%TYPE,
918 ATTRIBUTE11 PER_PERSON_DLVRY_METHODS.ATTRIBUTE11%TYPE,
919 ATTRIBUTE12 PER_PERSON_DLVRY_METHODS.ATTRIBUTE12%TYPE,
920 ATTRIBUTE13 PER_PERSON_DLVRY_METHODS.ATTRIBUTE13%TYPE,
921 ATTRIBUTE14 PER_PERSON_DLVRY_METHODS.ATTRIBUTE14%TYPE,
922 ATTRIBUTE15 PER_PERSON_DLVRY_METHODS.ATTRIBUTE15%TYPE,
923 ATTRIBUTE16 PER_PERSON_DLVRY_METHODS.ATTRIBUTE16%TYPE,
924 ATTRIBUTE17 PER_PERSON_DLVRY_METHODS.ATTRIBUTE17%TYPE,
925 ATTRIBUTE18 PER_PERSON_DLVRY_METHODS.ATTRIBUTE18%TYPE,
926 ATTRIBUTE19 PER_PERSON_DLVRY_METHODS.ATTRIBUTE19%TYPE,
927 ATTRIBUTE20 PER_PERSON_DLVRY_METHODS.ATTRIBUTE20%TYPE
928 );
929
930 TYPE ASSIGNMENT_EXTRA_INFO is RECORD
931 (
932 ASSIGNMENT_EXTRA_INFO_ID PER_ASSIGNMENT_EXTRA_INFO.ASSIGNMENT_EXTRA_INFO_ID%TYPE,
933 ASSIGNMENT_ID PER_ASSIGNMENT_EXTRA_INFO.ASSIGNMENT_ID%TYPE,
934 INFORMATION_TYPE PER_ASSIGNMENT_EXTRA_INFO.INFORMATION_TYPE%TYPE,
935 AEI_ATTRIBUTE_CATEGORY PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE_CATEGORY%TYPE,
936 AEI_ATTRIBUTE1 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE1%TYPE,
937 AEI_ATTRIBUTE2 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE2%TYPE,
938 AEI_ATTRIBUTE3 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE3%TYPE,
939 AEI_ATTRIBUTE4 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE4%TYPE,
940 AEI_ATTRIBUTE5 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE5%TYPE,
941 AEI_ATTRIBUTE6 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE6%TYPE,
942 AEI_ATTRIBUTE7 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE7%TYPE,
943 AEI_ATTRIBUTE8 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE8%TYPE,
944 AEI_ATTRIBUTE9 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE9%TYPE,
945 AEI_ATTRIBUTE10 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE10%TYPE,
946 AEI_ATTRIBUTE11 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE11%TYPE,
947 AEI_ATTRIBUTE12 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE12%TYPE,
948 AEI_ATTRIBUTE13 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE13%TYPE,
949 AEI_ATTRIBUTE14 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE14%TYPE,
950 AEI_ATTRIBUTE15 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE15%TYPE,
951 AEI_ATTRIBUTE16 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE16%TYPE,
952 AEI_ATTRIBUTE17 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE17%TYPE,
953 AEI_ATTRIBUTE18 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE18%TYPE,
954 AEI_ATTRIBUTE19 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE19%TYPE,
955 AEI_ATTRIBUTE20 PER_ASSIGNMENT_EXTRA_INFO.AEI_ATTRIBUTE20%TYPE,
956 AEI_INFORMATION_CATEGORY PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION_CATEGORY%TYPE,
957 AEI_INFORMATION1 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION1%TYPE,
958 AEI_INFORMATION2 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION2%TYPE,
959 AEI_INFORMATION3 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION3%TYPE,
960 AEI_INFORMATION4 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION4%TYPE,
961 AEI_INFORMATION5 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION5%TYPE,
962 AEI_INFORMATION6 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION6%TYPE,
963 AEI_INFORMATION7 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION7%TYPE,
964 AEI_INFORMATION8 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION8%TYPE,
965 AEI_INFORMATION9 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION9%TYPE,
966 AEI_INFORMATION10 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION10%TYPE,
967 AEI_INFORMATION11 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION11%TYPE,
968 AEI_INFORMATION12 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION12%TYPE,
969 AEI_INFORMATION13 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION13%TYPE,
970 AEI_INFORMATION14 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION14%TYPE,
971 AEI_INFORMATION15 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION15%TYPE,
972 AEI_INFORMATION16 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION16%TYPE,
973 AEI_INFORMATION17 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION17%TYPE,
974 AEI_INFORMATION18 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION18%TYPE,
975 AEI_INFORMATION19 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION19%TYPE,
976 AEI_INFORMATION20 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION20%TYPE,
977 AEI_INFORMATION21 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION21%TYPE,
978 AEI_INFORMATION22 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION22%TYPE,
979 AEI_INFORMATION23 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION23%TYPE,
980 AEI_INFORMATION24 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION24%TYPE,
981 AEI_INFORMATION25 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION25%TYPE,
982 AEI_INFORMATION26 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION26%TYPE,
983 AEI_INFORMATION27 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION27%TYPE,
984 AEI_INFORMATION28 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION28%TYPE,
985 AEI_INFORMATION29 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION29%TYPE,
986 AEI_INFORMATION30 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION30%TYPE
987 );
988
989 TYPE CONTACT_EXTRA_INFO is RECORD
990 (
991 CONTACT_EXTRA_INFO_ID PER_CONTACT_EXTRA_INFO_F.CONTACT_EXTRA_INFO_ID%TYPE,
992 EFFECTIVE_START_DATE varchar2(10),
993 EFFECTIVE_END_DATE varchar2(10),
994 CONTACT_RELATIONSHIP_ID PER_CONTACT_EXTRA_INFO_F.CONTACT_RELATIONSHIP_ID%TYPE,
995 INFORMATION_TYPE PER_CONTACT_EXTRA_INFO_F.INFORMATION_TYPE%TYPE,
996 CEI_INFORMATION_CATEGORY PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION_CATEGORY%TYPE,
997 CEI_INFORMATION1 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION1%TYPE,
998 CEI_INFORMATION2 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE,
999 CEI_INFORMATION3 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE,
1000 CEI_INFORMATION4 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION4%TYPE,
1001 CEI_INFORMATION5 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION5%TYPE,
1002 CEI_INFORMATION6 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION6%TYPE,
1003 CEI_INFORMATION7 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION7%TYPE,
1004 CEI_INFORMATION8 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION8%TYPE,
1005 CEI_INFORMATION9 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION9%TYPE,
1006 CEI_INFORMATION10 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION10%TYPE,
1007 CEI_INFORMATION11 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION11%TYPE,
1008 CEI_INFORMATION12 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION12%TYPE,
1009 CEI_INFORMATION13 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION13%TYPE,
1010 CEI_INFORMATION14 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION14%TYPE,
1011 CEI_INFORMATION15 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION15%TYPE,
1012 CEI_INFORMATION16 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION16%TYPE,
1013 CEI_INFORMATION17 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION17%TYPE,
1014 CEI_INFORMATION18 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION18%TYPE,
1015 CEI_INFORMATION19 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION19%TYPE,
1016 CEI_INFORMATION20 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION20%TYPE,
1017 CEI_INFORMATION21 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION21%TYPE,
1018 CEI_INFORMATION22 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION22%TYPE,
1019 CEI_INFORMATION23 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION23%TYPE,
1020 CEI_INFORMATION24 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION24%TYPE,
1021 CEI_INFORMATION25 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION25%TYPE,
1022 CEI_INFORMATION26 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION26%TYPE,
1023 CEI_INFORMATION27 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION27%TYPE,
1024 CEI_INFORMATION28 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION28%TYPE,
1025 CEI_INFORMATION29 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION29%TYPE,
1026 CEI_INFORMATION30 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION30%TYPE,
1027 CEI_ATTRIBUTE_CATEGORY PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE_CATEGORY%TYPE,
1028 CEI_ATTRIBUTE1 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE1%TYPE,
1029 CEI_ATTRIBUTE2 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE2%TYPE,
1030 CEI_ATTRIBUTE3 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE3%TYPE,
1031 CEI_ATTRIBUTE4 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE4%TYPE,
1032 CEI_ATTRIBUTE5 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE5%TYPE,
1033 CEI_ATTRIBUTE6 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE6%TYPE,
1034 CEI_ATTRIBUTE7 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE7%TYPE,
1035 CEI_ATTRIBUTE8 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE8%TYPE,
1036 CEI_ATTRIBUTE9 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE9%TYPE,
1037 CEI_ATTRIBUTE10 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE10%TYPE,
1038 CEI_ATTRIBUTE11 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE11%TYPE,
1039 CEI_ATTRIBUTE12 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE12%TYPE,
1040 CEI_ATTRIBUTE13 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE13%TYPE,
1041 CEI_ATTRIBUTE14 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE14%TYPE,
1042 CEI_ATTRIBUTE15 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE15%TYPE,
1043 CEI_ATTRIBUTE16 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE16%TYPE,
1044 CEI_ATTRIBUTE17 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE17%TYPE,
1045 CEI_ATTRIBUTE18 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE18%TYPE,
1046 CEI_ATTRIBUTE19 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE19%TYPE,
1047 CEI_ATTRIBUTE20 PER_CONTACT_EXTRA_INFO_F.CEI_ATTRIBUTE20%TYPE
1048 );
1049
1050 TYPE phone_details is RECORD
1051 (
1052 PHONE_ID PER_PHONES.PHONE_ID%TYPE,
1053 DATE_FROM varchar2(10),
1054 DATE_TO varchar2(10),
1055 PHONE_TYPE PER_PHONES.PHONE_TYPE%TYPE,
1056 PHONE_TYPE_DESC HR_LOOKUPS.MEANING%TYPE,
1057 PHONE_NUMBER PER_PHONES.PHONE_NUMBER%TYPE,
1058 ATTRIBUTE_CATEGORY PER_PHONES.ATTRIBUTE_CATEGORY%TYPE,
1059 ATTRIBUTE1 PER_PHONES.ATTRIBUTE1%TYPE,
1060 ATTRIBUTE2 PER_PHONES.ATTRIBUTE2%TYPE,
1061 ATTRIBUTE3 PER_PHONES.ATTRIBUTE3%TYPE,
1062 ATTRIBUTE4 PER_PHONES.ATTRIBUTE4%TYPE,
1063 ATTRIBUTE5 PER_PHONES.ATTRIBUTE5%TYPE,
1064 ATTRIBUTE6 PER_PHONES.ATTRIBUTE6%TYPE,
1065 ATTRIBUTE7 PER_PHONES.ATTRIBUTE7%TYPE,
1066 ATTRIBUTE8 PER_PHONES.ATTRIBUTE8%TYPE,
1067 ATTRIBUTE9 PER_PHONES.ATTRIBUTE9%TYPE,
1068 ATTRIBUTE10 PER_PHONES.ATTRIBUTE10%TYPE,
1069 ATTRIBUTE11 PER_PHONES.ATTRIBUTE11%TYPE,
1070 ATTRIBUTE12 PER_PHONES.ATTRIBUTE12%TYPE,
1071 ATTRIBUTE13 PER_PHONES.ATTRIBUTE13%TYPE,
1072 ATTRIBUTE14 PER_PHONES.ATTRIBUTE14%TYPE,
1073 ATTRIBUTE15 PER_PHONES.ATTRIBUTE15%TYPE,
1074 ATTRIBUTE16 PER_PHONES.ATTRIBUTE16%TYPE,
1075 ATTRIBUTE17 PER_PHONES.ATTRIBUTE17%TYPE,
1076 ATTRIBUTE18 PER_PHONES.ATTRIBUTE18%TYPE,
1077 ATTRIBUTE19 PER_PHONES.ATTRIBUTE19%TYPE,
1078 ATTRIBUTE20 PER_PHONES.ATTRIBUTE20%TYPE,
1079 ATTRIBUTE21 PER_PHONES.ATTRIBUTE21%TYPE,
1080 ATTRIBUTE22 PER_PHONES.ATTRIBUTE22%TYPE,
1081 ATTRIBUTE23 PER_PHONES.ATTRIBUTE23%TYPE,
1082 ATTRIBUTE24 PER_PHONES.ATTRIBUTE24%TYPE,
1083 ATTRIBUTE25 PER_PHONES.ATTRIBUTE25%TYPE,
1084 ATTRIBUTE26 PER_PHONES.ATTRIBUTE26%TYPE,
1085 ATTRIBUTE27 PER_PHONES.ATTRIBUTE27%TYPE,
1086 ATTRIBUTE28 PER_PHONES.ATTRIBUTE28%TYPE,
1087 ATTRIBUTE29 PER_PHONES.ATTRIBUTE29%TYPE,
1088 ATTRIBUTE30 PER_PHONES.ATTRIBUTE30%TYPE,
1089 PARTY_ID PER_PHONES.PARTY_ID%TYPE,
1090 VALIDITY PER_PHONES.VALIDITY%TYPE
1091 );
1092
1093 TYPE SUBJECT_DETAILS IS RECORD
1094 (
1095 SUBJECTS_TAKEN_ID PER_SUBJECTS_TAKEN.SUBJECTS_TAKEN_ID%TYPE,
1096 START_DATE VARCHAR2(10),
1097 MAJOR PER_SUBJECTS_TAKEN.MAJOR%TYPE,
1098 SUBJECT_STATUS PER_SUBJECTS_TAKEN.SUBJECT_STATUS%TYPE,
1099 SUBJECT_STATUS_DESC HR_LOOKUPS.MEANING%TYPE,
1100 SUBJECT PER_SUBJECTS_TAKEN.SUBJECT%TYPE,
1101 SUBJECT_DESC HR_LOOKUPS.MEANING%TYPE,
1102 GRADE_ATTAINED PER_SUBJECTS_TAKEN.GRADE_ATTAINED%TYPE,
1103 END_DATE VARCHAR2(10),
1104 ATTRIBUTE_CATEGORY PER_SUBJECTS_TAKEN.ATTRIBUTE_CATEGORY%TYPE,
1105 ATTRIBUTE1 PER_SUBJECTS_TAKEN.ATTRIBUTE1%TYPE,
1106 ATTRIBUTE2 PER_SUBJECTS_TAKEN.ATTRIBUTE2%TYPE,
1107 ATTRIBUTE3 PER_SUBJECTS_TAKEN.ATTRIBUTE3%TYPE,
1108 ATTRIBUTE4 PER_SUBJECTS_TAKEN.ATTRIBUTE4%TYPE,
1109 ATTRIBUTE5 PER_SUBJECTS_TAKEN.ATTRIBUTE5%TYPE,
1110 ATTRIBUTE6 PER_SUBJECTS_TAKEN.ATTRIBUTE6%TYPE,
1111 ATTRIBUTE7 PER_SUBJECTS_TAKEN.ATTRIBUTE7%TYPE,
1112 ATTRIBUTE8 PER_SUBJECTS_TAKEN.ATTRIBUTE8%TYPE,
1113 ATTRIBUTE9 PER_SUBJECTS_TAKEN.ATTRIBUTE9%TYPE,
1114 ATTRIBUTE10 PER_SUBJECTS_TAKEN.ATTRIBUTE10%TYPE,
1115 ATTRIBUTE11 PER_SUBJECTS_TAKEN.ATTRIBUTE11%TYPE,
1116 ATTRIBUTE12 PER_SUBJECTS_TAKEN.ATTRIBUTE12%TYPE,
1117 ATTRIBUTE13 PER_SUBJECTS_TAKEN.ATTRIBUTE13%TYPE,
1118 ATTRIBUTE14 PER_SUBJECTS_TAKEN.ATTRIBUTE14%TYPE,
1119 ATTRIBUTE15 PER_SUBJECTS_TAKEN.ATTRIBUTE15%TYPE,
1120 ATTRIBUTE16 PER_SUBJECTS_TAKEN.ATTRIBUTE16%TYPE,
1121 ATTRIBUTE17 PER_SUBJECTS_TAKEN.ATTRIBUTE17%TYPE,
1122 ATTRIBUTE18 PER_SUBJECTS_TAKEN.ATTRIBUTE18%TYPE,
1123 ATTRIBUTE19 PER_SUBJECTS_TAKEN.ATTRIBUTE19%TYPE,
1124 ATTRIBUTE20 PER_SUBJECTS_TAKEN.ATTRIBUTE20%TYPE,
1125 SUB_INFORMATION_CATEGORY PER_SUBJECTS_TAKEN.SUB_INFORMATION_CATEGORY%TYPE,
1126 SUB_INFORMATION1 PER_SUBJECTS_TAKEN.SUB_INFORMATION1%TYPE,
1127 SUB_INFORMATION2 PER_SUBJECTS_TAKEN.SUB_INFORMATION2%TYPE,
1128 SUB_INFORMATION3 PER_SUBJECTS_TAKEN.SUB_INFORMATION3%TYPE,
1129 SUB_INFORMATION4 PER_SUBJECTS_TAKEN.SUB_INFORMATION4%TYPE,
1130 SUB_INFORMATION5 PER_SUBJECTS_TAKEN.SUB_INFORMATION5%TYPE,
1131 SUB_INFORMATION6 PER_SUBJECTS_TAKEN.SUB_INFORMATION6%TYPE,
1132 SUB_INFORMATION7 PER_SUBJECTS_TAKEN.SUB_INFORMATION7%TYPE,
1133 SUB_INFORMATION8 PER_SUBJECTS_TAKEN.SUB_INFORMATION8%TYPE,
1134 SUB_INFORMATION9 PER_SUBJECTS_TAKEN.SUB_INFORMATION9%TYPE,
1135 SUB_INFORMATION10 PER_SUBJECTS_TAKEN.SUB_INFORMATION10%TYPE,
1136 SUB_INFORMATION11 PER_SUBJECTS_TAKEN.SUB_INFORMATION11%TYPE,
1137 SUB_INFORMATION12 PER_SUBJECTS_TAKEN.SUB_INFORMATION12%TYPE,
1138 SUB_INFORMATION13 PER_SUBJECTS_TAKEN.SUB_INFORMATION13%TYPE,
1139 SUB_INFORMATION14 PER_SUBJECTS_TAKEN.SUB_INFORMATION14%TYPE,
1140 SUB_INFORMATION15 PER_SUBJECTS_TAKEN.SUB_INFORMATION15%TYPE,
1141 SUB_INFORMATION16 PER_SUBJECTS_TAKEN.SUB_INFORMATION16%TYPE,
1142 SUB_INFORMATION17 PER_SUBJECTS_TAKEN.SUB_INFORMATION17%TYPE,
1143 SUB_INFORMATION18 PER_SUBJECTS_TAKEN.SUB_INFORMATION18%TYPE,
1144 SUB_INFORMATION19 PER_SUBJECTS_TAKEN.SUB_INFORMATION19%TYPE,
1145 SUB_INFORMATION20 PER_SUBJECTS_TAKEN.SUB_INFORMATION20%TYPE
1146 );
1147
1148 TYPE SUBJECT_RECORD IS TABLE OF SUBJECT_DETAILS INDEX BY BINARY_INTEGER;
1149
1150 TYPE QUALIFICATION_DET IS RECORD
1151 (
1152 QUALIFICATION_ID PER_QUALIFICATIONS.QUALIFICATION_ID%type,
1153 TITLE PER_QUALIFICATIONS.TITLE%TYPE,
1154 GRADE_ATTAINED PER_QUALIFICATIONS.GRADE_ATTAINED%TYPE,
1155 STATUS PER_QUALIFICATIONS.STATUS%TYPE,
1156 STATUS_DESC HR_LOOKUPS.MEANING%TYPE,
1157 AWARDED_DATE VARCHAR2(10),
1158 FEE PER_QUALIFICATIONS.FEE%TYPE,
1159 FEE_CURRENCY PER_QUALIFICATIONS.FEE_CURRENCY%TYPE,
1160 FEE_CURRENCY_DESC FND_CURRENCIES_VL.NAME%TYPE,
1161 TRAINING_COMPLETED_AMOUNT PER_QUALIFICATIONS.TRAINING_COMPLETED_AMOUNT%TYPE,
1162 REIMBURSEMENT_ARRANGEMENTS PER_QUALIFICATIONS.REIMBURSEMENT_ARRANGEMENTS%TYPE,
1163 TRAINING_COMPLETED_UNITS PER_QUALIFICATIONS.TRAINING_COMPLETED_UNITS%TYPE,
1164 TOTAL_TRAINING_AMOUNT PER_QUALIFICATIONS.TOTAL_TRAINING_AMOUNT%TYPE,
1165 START_DATE VARCHAR2(10),
1166 END_DATE VARCHAR2(10),
1167 LICENSE_NUMBER PER_QUALIFICATIONS.LICENSE_NUMBER%TYPE,
1168 EXPIRY_DATE VARCHAR2(10),
1169 LICENSE_RESTRICTIONS PER_QUALIFICATIONS.LICENSE_RESTRICTIONS%TYPE,
1170 PROJECTED_COMPLETION_DATE VARCHAR2(10),
1171 AWARDING_BODY PER_QUALIFICATIONS.AWARDING_BODY%TYPE,
1172 TUITION_METHOD PER_QUALIFICATIONS.TUITION_METHOD%TYPE,
1173 TUITION_METHOD_DESC HR_LOOKUPS.MEANING%TYPE,
1174 GROUP_RANKING PER_QUALIFICATIONS.GROUP_RANKING%TYPE,
1175 COMMENTS PER_QUALIFICATIONS.COMMENTS%TYPE,
1176 QUALIFICATION_TYPE_ID PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID%TYPE,
1177 QUALIFICATION_TYPE_DESC PER_QUALIFICATION_TYPES_TL.NAME%TYPE,
1178 ATTENDANCE_ID PER_QUALIFICATIONS.ATTENDANCE_ID%TYPE,
1179 ATTRIBUTE_CATEGORY PER_QUALIFICATIONS.ATTRIBUTE_CATEGORY%TYPE,
1180 ATTRIBUTE1 PER_QUALIFICATIONS.ATTRIBUTE1%TYPE,
1181 ATTRIBUTE2 PER_QUALIFICATIONS.ATTRIBUTE2%TYPE,
1182 ATTRIBUTE3 PER_QUALIFICATIONS.ATTRIBUTE3%TYPE,
1183 ATTRIBUTE4 PER_QUALIFICATIONS.ATTRIBUTE4%TYPE,
1184 ATTRIBUTE5 PER_QUALIFICATIONS.ATTRIBUTE5%TYPE,
1185 ATTRIBUTE6 PER_QUALIFICATIONS.ATTRIBUTE6%TYPE,
1186 ATTRIBUTE7 PER_QUALIFICATIONS.ATTRIBUTE7%TYPE,
1187 ATTRIBUTE8 PER_QUALIFICATIONS.ATTRIBUTE8%TYPE,
1188 ATTRIBUTE9 PER_QUALIFICATIONS.ATTRIBUTE9%TYPE,
1189 ATTRIBUTE10 PER_QUALIFICATIONS.ATTRIBUTE10%TYPE,
1190 ATTRIBUTE11 PER_QUALIFICATIONS.ATTRIBUTE11%TYPE,
1191 ATTRIBUTE12 PER_QUALIFICATIONS.ATTRIBUTE12%TYPE,
1192 ATTRIBUTE13 PER_QUALIFICATIONS.ATTRIBUTE13%TYPE,
1193 ATTRIBUTE14 PER_QUALIFICATIONS.ATTRIBUTE14%TYPE,
1194 ATTRIBUTE15 PER_QUALIFICATIONS.ATTRIBUTE15%TYPE,
1195 ATTRIBUTE16 PER_QUALIFICATIONS.ATTRIBUTE16%TYPE,
1196 ATTRIBUTE17 PER_QUALIFICATIONS.ATTRIBUTE17%TYPE,
1197 ATTRIBUTE18 PER_QUALIFICATIONS.ATTRIBUTE18%TYPE,
1198 ATTRIBUTE19 PER_QUALIFICATIONS.ATTRIBUTE19%TYPE,
1199 ATTRIBUTE20 PER_QUALIFICATIONS.ATTRIBUTE20%TYPE,
1200 PARTY_ID PER_QUALIFICATIONS.PARTY_ID%TYPE,
1201 PROFESSIONAL_BODY_NAME PER_QUALIFICATIONS.PROFESSIONAL_BODY_NAME%TYPE,
1202 MEMBERSHIP_NUMBER PER_QUALIFICATIONS.MEMBERSHIP_NUMBER%TYPE,
1203 MEMBERSHIP_CATEGORY PER_QUALIFICATIONS.MEMBERSHIP_CATEGORY%TYPE,
1204 SUBSCRIPTION_PAYMENT_METHOD PER_QUALIFICATIONS.SUBSCRIPTION_PAYMENT_METHOD%TYPE,
1205 SUBSCRIPTION_PAYMENT_DESC HR_LOOKUPS.MEANING%TYPE,
1206 QUA_INFORMATION_CATEGORY PER_QUALIFICATIONS.QUA_INFORMATION_CATEGORY%TYPE,
1207 QUA_INFORMATION1 PER_QUALIFICATIONS.QUA_INFORMATION1%TYPE,
1208 QUA_INFORMATION2 PER_QUALIFICATIONS.QUA_INFORMATION2%TYPE,
1209 QUA_INFORMATION3 PER_QUALIFICATIONS.QUA_INFORMATION3%TYPE,
1210 QUA_INFORMATION4 PER_QUALIFICATIONS.QUA_INFORMATION4%TYPE,
1211 QUA_INFORMATION5 PER_QUALIFICATIONS.QUA_INFORMATION5%TYPE,
1212 QUA_INFORMATION6 PER_QUALIFICATIONS.QUA_INFORMATION6%TYPE,
1213 QUA_INFORMATION7 PER_QUALIFICATIONS.QUA_INFORMATION7%TYPE,
1214 QUA_INFORMATION8 PER_QUALIFICATIONS.QUA_INFORMATION8%TYPE,
1215 QUA_INFORMATION9 PER_QUALIFICATIONS.QUA_INFORMATION9%TYPE,
1216 QUA_INFORMATION10 PER_QUALIFICATIONS.QUA_INFORMATION10%TYPE,
1217 QUA_INFORMATION11 PER_QUALIFICATIONS.QUA_INFORMATION11%TYPE,
1218 QUA_INFORMATION12 PER_QUALIFICATIONS.QUA_INFORMATION12%TYPE,
1219 QUA_INFORMATION13 PER_QUALIFICATIONS.QUA_INFORMATION13%TYPE,
1220 QUA_INFORMATION14 PER_QUALIFICATIONS.QUA_INFORMATION14%TYPE,
1221 QUA_INFORMATION15 PER_QUALIFICATIONS.QUA_INFORMATION15%TYPE,
1222 QUA_INFORMATION16 PER_QUALIFICATIONS.QUA_INFORMATION16%TYPE,
1223 QUA_INFORMATION17 PER_QUALIFICATIONS.QUA_INFORMATION17%TYPE,
1224 QUA_INFORMATION18 PER_QUALIFICATIONS.QUA_INFORMATION18%TYPE,
1225 QUA_INFORMATION19 PER_QUALIFICATIONS.QUA_INFORMATION19%TYPE,
1226 QUA_INFORMATION20 PER_QUALIFICATIONS.QUA_INFORMATION20%TYPE
1227 );
1228
1229 TYPE QUALIFICATION_DETAILS IS RECORD
1230 (QUALIFICATIONS QUALIFICATION_DET,
1231 SUBJECT SUBJECT_RECORD);
1232
1233 TYPE REPORTEE_DETAILS IS RECORD
1234 (
1235 REPORTEE_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
1236 REPORTEE_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE,
1237 REPORTEE_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE,
1238 REPORTEE_CWK_NUMBER PER_ALL_PEOPLe_F.NPW_NUMBER%TYPE,
1239 REPORTEE_APPLICANT_NUMBER PER_ALL_PEOPLE_F.APPLICANT_NUMBER%TYPE,
1240 REPORTEE_LOCATION_ID PER_ALL_ASSIGNMENTS_F.LOCATION_ID%TYPE,
1241 REPORTEE_LOCATION_DESC HR_ALL_POSITIONS_F_TL.NAME%TYPE, --(BASED ON LANG)E,
1242 REPORTEE_ASG_STATUS_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID%TYPE,
1243 REPORTEE_ASG_STATUS_DESC PER_ASSIGNMENT_STATUS_TYPES_TL.USER_STATUS%TYPE
1244 );
1245
1246 TYPE ABSENCE_DETAILS IS RECORD
1247 (
1248 ACCRUAL_PLAN_NAME PAY_VIEW_ACCRUAL_PLANS_V.ACCRUAL_PLAN_NAME%TYPE,
1249 ACCRUAL_NET_ENTITLEMENT NUMBER,
1250 ACCRUAL_UOM PAY_VIEW_ACCRUAL_PLANS_V.ACCRUAL_UNITS_OF_MEASURE_NAME%TYPE,
1251 AS_OF_DATE VARCHAR2(10)
1252 );
1253
1254 /* Benefits reocrd types and tables */
1255
1256 TYPE g_beneficiary_rec_type IS RECORD
1257 (plan_type_name ben_pl_typ_f.name%type
1258 ,plan_name ben_pl_f.name%type
1259 ,option_name ben_opt_f.name%type
1260 ,beneficiary VARCHAR2(400)
1261 ,ben_ssn per_all_people_f.national_identifier%type
1262 ,ben_relation VARCHAR2(400)
1263 ,primary_bnf NUMBER
1264 ,contingent_bnf NUMBER
1265 ,le_name ben_ler_f.name%type
1266 ,ben_full_name per_all_people_f.full_name%type
1267 );
1268
1269 TYPE g_dependent_rec_type IS RECORD
1270 (
1271 name per_all_people_f.full_name%type,
1272 relationship VARCHAR2(400),
1273 type_of_benefit ben_pl_typ_f.name%type,
1274 coverage VARCHAR2(100)
1275 );
1276
1277 TYPE g_benefits_rec_type IS RECORD
1278 (
1279 type_of_benefit ben_pl_typ_f.name%type
1280 ,plan ben_pl_f.name%type
1281 ,coverage_or_participation ben_opt_f.name%type
1282 );
1283
1284 TYPE g_beneficiary_tab_type IS TABLE OF g_beneficiary_rec_type INDEX BY BINARY_INTEGER;
1285
1286 TYPE g_benefits_tab_type IS TABLE OF g_benefits_rec_type INDEX BY BINARY_INTEGER;
1287 TYPE g_dependent_tab_type IS TABLE OF g_dependent_rec_type INDEX BY BINARY_INTEGER;
1288
1289 /*Complete Ben record structure*/
1290 TYPE ben_record_details is record
1291 (
1292 benefit g_benefits_tab_type,
1293 dependent g_dependent_tab_type
1294 );
1295
1296 /*Record structure for Payroll details*/
1297 TYPE payroll_details IS RECORD
1298 (
1299 LEGISLATION_CODE VARCHAR2(30),
1300 COMPANY PAY_EMPLOYEE_ACTION_INFO_V.ORGANIZATION_NAME%TYPE,
1301 JOB_TITLE PAY_EMPLOYEE_ACTION_INFO_V.JOB%TYPE,
1302 PAYMENT_DATE VARCHAR2(10),
1303 ADDRESS VARCHAR2(2000),
1304 TAX_LOCATION PAY_EMPLOYEE_ACTION_INFO_V.LOCATION_NAME%TYPE,
1305 PAY_GROUP PAY_EMPLOYEE_ACTION_INFO_V.PAYROLL_NAME%TYPE,
1306 CURRENCY_CODE PER_BUSINESS_GROUPS.CURRENCY_CODE%TYPE,
1307 PERIOD_BEGIN VARCHAR2(10),
1308 PERIOD_END VARCHAR2(10),
1309 TAX_JURISDICTION PAY_US_EMP_W4DTL_ACTION_INFO_V.TAX_JURISDICTION%TYPE,
1310 MARITAL_STATUS PAY_US_EMP_W4DTL_ACTION_INFO_V.STATUS%TYPE,
1311 PAY_FREQUENCY PAY_EMPLOYEE_ACTION_INFO_V.PERIOD_TYPE%TYPE,
1312 RUN_TYPE PAY_RUN_TYPES_F.RUN_TYPE_NAME%TYPE,
1313 FED_EXEMPTIONS PAY_US_EMP_W4DTL_ACTION_INFO_V.EXEMPTIONS%TYPE,
1314 FED_ADDNL_TAX_AMOUNT PAY_US_EMP_W4DTL_ACTION_INFO_V.ADDITIONAL_TAX_AMOUNT%TYPE,
1315 FED_OVERRIDE_TAX_AMOUNT PAY_US_EMP_W4DTL_ACTION_INFO_V.OVERRIDE_TAX_AMOUNT%TYPE,
1316 FED_OVERRIDE_TAX_PERCENTAGE PAY_US_EMP_W4DTL_ACTION_INFO_V.OVERRIDE_TAX_PERCENTAGE%TYPE,
1317 STATE_CODE VARCHAR2(30),
1318 ST_EXEMPTIONS PAY_US_EMP_W4DTL_ACTION_INFO_V.EXEMPTIONS%TYPE,
1319 ST_ADDNL_TAX_AMOUNT PAY_US_EMP_W4DTL_ACTION_INFO_V.ADDITIONAL_TAX_AMOUNT%TYPE,
1320 ST_OVERRIDE_TAX_AMOUNT PAY_US_EMP_W4DTL_ACTION_INFO_V.OVERRIDE_TAX_AMOUNT%TYPE,
1321 ST_OVERRIDE_TAX_PERCENTAGE PAY_US_EMP_W4DTL_ACTION_INFO_V.OVERRIDE_TAX_PERCENTAGE%TYPE,
1322 TOTAL_EARNINGS_CV NUMBER,
1323 TOTAL_EARNINGS_YTD NUMBER,
1324 TOTAL_TAXES_CV NUMBER,
1325 TOTAL_TAXES_YTD NUMBER,
1326 TAX_GROSS_CV NUMBER,
1327 TAX_GROSS_YTD NUMBER,
1328 TOTAL_DED_CV NUMBER,
1329 TOTAL_DED_YTD NUMBER,
1330 TOTAL_NETPAY_CV NUMBER,
1331 TOTAL_NETPAY_YTD NUMBER,
1332 NI_CV NUMBER,
1333 NI_YTD NUMBER);
1334
1335 TYPE assignment_record is table of assignment_details INDEX BY BINARY_INTEGER;
1336 TYPE address_record is table of address_details INDEX BY BINARY_INTEGER;
1337 TYPE contact_record is table of contact_details INDEX BY BINARY_INTEGER;
1338 TYPE person_extra_info_record is table of person_extra_info_details INDEX BY BINARY_INTEGER;
1339 TYPE special_info_record is table of special_information_details INDEX BY BINARY_INTEGER;
1340 TYPE WORK_INC_INFO_RECORD is table of WORK_INC_INFO INDEX BY BINARY_INTEGER;
1341 TYPE PREV_EMP_INFO_RECORD is table of PREV_EMP_INFO INDEX BY BINARY_INTEGER;
1342 TYPE DELIV_METHOD_INFO_RECORD is table of DELIV_METHOD_INFO INDEX BY BINARY_INTEGER;
1343 TYPE ASSIGNMENT_EXTRA_INFO_RECORD is table of ASSIGNMENT_EXTRA_INFO INDEX BY BINARY_INTEGER;
1344 TYPE CONTACT_EXTRA_INFO_RECORD is table of CONTACT_EXTRA_INFO INDEX BY BINARY_INTEGER;
1345 TYPE PHONE_RECORD is table of PHONE_DETAILS INDEX BY BINARY_INTEGER;
1346 TYPE QUALIFICATION_RECORD is table of QUALIFICATION_DETAILS INDEX BY BINARY_INTEGER;
1347 TYPE REPORTEE_RECORD is table of REPORTEE_DETAILS INDEX BY BINARY_INTEGER;
1348 TYPE CONTACT_ADDRESS_RECORD IS TABLE OF CONTACT_ADDRESS_DETAILS INDEX BY BINARY_INTEGER;
1349 TYPE ABSENCE_RECORD IS TABLE OF ABSENCE_DETAILS INDEX BY BINARY_INTEGER;
1350 TYPE PAYROLL_RECORD IS TABLE OF PAYROLL_DETAILS INDEX BY BINARY_INTEGER;
1351
1352 /*Complete person record structure*/
1353 TYPE person_record_details is record
1354 (
1355 person person_details,
1356 assignment assignment_record,
1357 address address_record,
1358 contact contact_record,
1359 person_extra_information person_extra_info_record,
1360 special_information special_info_record,
1361 work_incidents work_inc_info_record,
1362 previous_employment prev_emp_info_record,
1363 delivery_methods deliv_method_info_record,
1364 assignment_extra_information assignment_extra_info_record,
1365 contact_extra_information contact_extra_info_record,
1366 phone phone_record,
1367 qualification qualification_record,
1368 reportee reportee_record,
1369 contact_address contact_address_record,
1370 absence absence_record,
1371 benefit_details ben_record_details,
1372 payroll payroll_record
1373 );
1374
1375 -- Person Record as table type
1376 TYPE person_record is table of person_record_details index by binary_integer;
1377
1378 /*#
1379 * This is source for GET_PERSON_DETAILS
1380 * @rep:displayname Get Person Details
1381 * @rep:category BUSINESS_ENTITY PER_EMPLOYEE_ASG
1382 * @rep:scope public
1383 * @rep:lifecycle active
1384 */
1385 procedure GET_PERSON_DETAILS (p_srch_criteria in srch_criteria,
1386 p_entity in options,
1387 p_person out nocopy person_record,
1388 p_error out nocopy varchar2);
1389 END HR_PERSON_RECORD;