1 PACKAGE BODY PER_PL_PERSON_EXTRA_INFO AS
2 /* $Header: peplpeip.pkb 120.1 2006/09/13 12:43:30 mseshadr noship $ */
3
4
5
6 PROCEDURE CHK_PERSON_TYPE(P_PERSON_ID NUMBER,
7 P_FLEX_START_DATE VARCHAR2,
8 P_FLEX_END_DATE VARCHAR2,
9 P_INFORMATION_TYPE VARCHAR2) IS
10
11
12 cursor csr_chk_contact is
13 select 1 from
14 per_people_f pap,per_person_types ppf,per_person_type_usages_f ptu
15 where pap.person_id = P_PERSON_ID and
16 pap.person_id = ptu.person_id and
17 ppf.person_type_id = ptu.person_type_id and
18 ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
19 pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
20 pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
21 ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
22 ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
23
24 person_type number;
25
26 BEGIN
27
28 open csr_chk_contact;
29 fetch csr_chk_contact into person_type;
30 if csr_chk_contact%NOTFOUND then
31 hr_utility.set_message(800,'HR_CON_INVALID_DETAILS_PL');
32 hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
33 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
34 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
35 -- This message will be 'These details can be entered only for a Contact. Person type is not a Contact for the date range'
36 hr_utility.raise_error;
37 end if;
38 close csr_chk_contact;
39
40 END CHK_PERSON_TYPE;
41
42
43
44 PROCEDURE CREATE_CON_DATE_CHK(P_PERSON_ID NUMBER,
45 P_FLEX_START_DATE VARCHAR2,
46 P_FLEX_END_DATE VARCHAR2,
47 P_INFORMATION_TYPE VARCHAR2) IS
48
49 cursor csr_overlap_rec is
50 select 1 from per_people_extra_info where
51 person_id = P_PERSON_ID and
52 information_type = P_INFORMATION_TYPE and
53 (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
54 and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56 between fnd_date.canonical_to_date(PEI_INFORMATION1)
57 and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58 fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60 nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63
64 cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
65 and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
66 and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
67 and per_information2 ='Y';
68
69 overlap_found number;
70 vInherit per_all_people_f.per_information2%type;
71 BEGIN
72
73 if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >
74 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then
75 hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
76 -- This message will be 'Please enter a Start date >= End date
77 hr_utility.raise_error;
78 end if;
79
80 open cur_inherit;
81 fetch cur_inherit into vInherit;
82
83 if cur_inherit%notfound then
84 hr_utility.set_message(800,'HR_CONTACT_INSURED_FLEX_PL');
85 hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
86 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
87 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
88 hr_utility.raise_error;
89 end if;
90
91 close cur_inherit;
92
93 open csr_overlap_rec;
94 fetch csr_overlap_rec into overlap_found;
95 if csr_overlap_rec%found then
96 hr_utility.set_message(800,'HR_CONTACT_OVERLAP_REC_PL');
97 hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
98 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
99 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
100 hr_utility.raise_error;
101 end if;
102 close csr_overlap_rec;
103
104
105 END CREATE_CON_DATE_CHK;
106
107 PROCEDURE UPDATE_CON_DATE_CHK(P_PERSON_EXTRA_INFO_ID NUMBER
108 ,P_PEI_INFORMATION_CATEGORY VARCHAR2
109 ,P_FLEX_START_DATE VARCHAR2
110 ,P_FLEX_END_DATE VARCHAR2) IS
111
112
113 -- This cursor checks if the Start/End Dates are not overlapping with other records
114
115 cursor csr_overlap_upd_rec is
116 select 1 from per_people_extra_info where
117 pei_information_category = P_PEI_INFORMATION_CATEGORY and
118 (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
119 and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121 between fnd_date.canonical_to_date(PEI_INFORMATION1)
122 and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123 fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125 nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
127 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
128 person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
129 person_extra_info_id <> P_PERSON_EXTRA_INFO_ID;
130
131
132
133 -- This cursor checks if the Updated Dates are within the Start/End Dates of the 'Contact'
134
135 cursor csr_chk_contact_date is
136 select 1 from
137 per_people_f pap,per_person_types ppf,per_person_type_usages_f ptu
138 where pap.person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
139 pap.person_id = ptu.person_id and
140 ppf.person_type_id = ptu.person_type_id and
141 ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
142 pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
143 pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
144 ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
145 ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
146
147 cursor cur_inherit is select per_information2 from per_all_people_f where
148 person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)
149 and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
150 and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
151 and per_information2 ='Y';
152
153 vInherit per_all_people_f.per_information2%type;
154
155 overlap_upd_found number;
156 valid_contact_dates number;
157
158
159 BEGIN
160
161 if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >
162 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then
163 hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
164 -- This message will be 'Please enter a Start date >= End date
165 hr_utility.raise_error;
166 end if;
167
168 open cur_inherit;
169 fetch cur_inherit into vInherit;
170
171 if cur_inherit%notfound then
172 hr_utility.set_message(800,'HR_CONTACT_INSURED_FLEX_PL');
173 hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
174 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
175 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
176 hr_utility.raise_error;
177 end if;
178
179 close cur_inherit;
180
181 open csr_chk_contact_date;
182 fetch csr_chk_contact_date into valid_contact_dates;
183 if csr_chk_contact_date%NOTFOUND then
184 hr_utility.set_message(800,'HR_CON_INVALID_DETAILS_PL');
185 hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
186 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
187 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
188 -- This message will be 'These details can be entered only for a Contact. Person type is not a Contact for the date range'
189 hr_utility.raise_error;
190 end if;
191 close csr_chk_contact_date;
192
193
194 open csr_overlap_upd_rec;
195 fetch csr_overlap_upd_rec into overlap_upd_found;
196 if csr_overlap_upd_rec%found then
197 hr_utility.set_message(800,'HR_CONTACT_OVERLAP_REC_PL');
198 hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
199 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
200 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
201 hr_utility.raise_error;
202 end if;
203 close csr_overlap_upd_rec;
204
205
206
207 END UPDATE_CON_DATE_CHK;
208
209
210 PROCEDURE CREATE_PL_PERSON_EXTRA_INFO
211 (P_PERSON_ID in NUMBER
212 ,P_INFORMATION_TYPE in VARCHAR2
213 ,P_PEI_INFORMATION_CATEGORY in VARCHAR2
214 ,P_PEI_INFORMATION1 in VARCHAR2
215 ,P_PEI_INFORMATION2 in VARCHAR2
216 ,P_PEI_INFORMATION3 in VARCHAR2
217 ,P_PEI_INFORMATION4 in VARCHAR2
218 ,P_PEI_INFORMATION5 in VARCHAR2
219 ,P_PEI_INFORMATION6 in VARCHAR2
220 ,P_PEI_INFORMATION7 in VARCHAR2
221 ,P_PEI_INFORMATION8 in VARCHAR2
222 ,P_PEI_INFORMATION9 in VARCHAR2
223 ,P_PEI_INFORMATION10 in VARCHAR2
224 ,P_PEI_INFORMATION11 in VARCHAR2
225 ,P_PEI_INFORMATION12 in VARCHAR2
226 ,P_PEI_INFORMATION13 in VARCHAR2
227 ,P_PEI_INFORMATION14 in VARCHAR2
228 ,P_PEI_INFORMATION15 in VARCHAR2
229 ,P_PEI_INFORMATION16 in VARCHAR2
230 ,P_PEI_INFORMATION17 in VARCHAR2
231 ,P_PEI_INFORMATION18 in VARCHAR2
232 ,P_PEI_INFORMATION19 in VARCHAR2
233 ,P_PEI_INFORMATION20 in VARCHAR2
234 ,P_PEI_INFORMATION21 in VARCHAR2
235 ,P_PEI_INFORMATION22 in VARCHAR2
236 ,P_PEI_INFORMATION23 in VARCHAR2
237 ,P_PEI_INFORMATION24 in VARCHAR2
238 ,P_PEI_INFORMATION25 in VARCHAR2
239 ,P_PEI_INFORMATION26 in VARCHAR2
240 ,P_PEI_INFORMATION27 in VARCHAR2
241 ,P_PEI_INFORMATION28 in VARCHAR2
242 ,P_PEI_INFORMATION29 in VARCHAR2
243 ,P_PEI_INFORMATION30 in VARCHAR2) IS
244
245 BEGIN
246 /* Added for GSI Bug 5472781 */
247 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
248 hr_utility.trace('PL not installed.Leaving CREATE_PL_PERSON_EXTRA_INFO');
249 return;
250 END IF;
251
252 if P_INFORMATION_TYPE = 'PL_CON_HEALTH_INS' then
253
254 CHK_PERSON_TYPE(P_PERSON_ID,
255 P_PEI_INFORMATION1,
256 P_PEI_INFORMATION2,
257 P_INFORMATION_TYPE);
258
259 CREATE_CON_DATE_CHK(P_PERSON_ID,
260 P_PEI_INFORMATION1,
261 P_PEI_INFORMATION2,
262 P_INFORMATION_TYPE);
263
264 elsif P_INFORMATION_TYPE = 'PL_CON_HOUSEKEEPING' then
265
266 CHK_PERSON_TYPE(P_PERSON_ID,
267 P_PEI_INFORMATION1,
268 P_PEI_INFORMATION2,
269 P_INFORMATION_TYPE);
270
271 CREATE_CON_DATE_CHK(P_PERSON_ID,
272 P_PEI_INFORMATION1,
273 P_PEI_INFORMATION2,
274 P_INFORMATION_TYPE);
275
276
277
278 end if;
279
280
281 END CREATE_PL_PERSON_EXTRA_INFO;
282
283
284 PROCEDURE UPDATE_PL_PERSON_EXTRA_INFO
285 (P_PERSON_EXTRA_INFO_ID in NUMBER
286 ,P_PEI_INFORMATION_CATEGORY in VARCHAR2
287 ,P_PEI_INFORMATION1 in VARCHAR2
288 ,P_PEI_INFORMATION2 in VARCHAR2
289 ,P_PEI_INFORMATION3 in VARCHAR2
290 ,P_PEI_INFORMATION4 in VARCHAR2
291 ,P_PEI_INFORMATION5 in VARCHAR2
292 ,P_PEI_INFORMATION6 in VARCHAR2
293 ,P_PEI_INFORMATION7 in VARCHAR2
294 ,P_PEI_INFORMATION8 in VARCHAR2
295 ,P_PEI_INFORMATION9 in VARCHAR2
296 ,P_PEI_INFORMATION10 in VARCHAR2
297 ,P_PEI_INFORMATION11 in VARCHAR2
298 ,P_PEI_INFORMATION12 in VARCHAR2
299 ,P_PEI_INFORMATION13 in VARCHAR2
300 ,P_PEI_INFORMATION14 in VARCHAR2
301 ,P_PEI_INFORMATION15 in VARCHAR2
302 ,P_PEI_INFORMATION16 in VARCHAR2
303 ,P_PEI_INFORMATION17 in VARCHAR2
304 ,P_PEI_INFORMATION18 in VARCHAR2
305 ,P_PEI_INFORMATION19 in VARCHAR2
306 ,P_PEI_INFORMATION20 in VARCHAR2
307 ,P_PEI_INFORMATION21 in VARCHAR2
308 ,P_PEI_INFORMATION22 in VARCHAR2
309 ,P_PEI_INFORMATION23 in VARCHAR2
310 ,P_PEI_INFORMATION24 in VARCHAR2
311 ,P_PEI_INFORMATION25 in VARCHAR2
312 ,P_PEI_INFORMATION26 in VARCHAR2
313 ,P_PEI_INFORMATION27 in VARCHAR2
314 ,P_PEI_INFORMATION28 in VARCHAR2
315 ,P_PEI_INFORMATION29 in VARCHAR2
316 ,P_PEI_INFORMATION30 in VARCHAR2) IS
317
318 BEGIN
319 /* Added for GSI Bug 5472781 */
320 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
321 hr_utility.trace('PL not installed.Leaving UPDATE_PL_PERSON_EXTRA_INFO');
322 return;
323 END IF;
324
325 if P_PEI_INFORMATION_CATEGORY = 'PL_CON_HEALTH_INS' then
326
327 UPDATE_CON_DATE_CHK(P_PERSON_EXTRA_INFO_ID
328 ,P_PEI_INFORMATION_CATEGORY
329 ,P_PEI_INFORMATION1
330 ,P_PEI_INFORMATION2);
331
332
333 elsif P_PEI_INFORMATION_CATEGORY = 'PL_CON_HOUSEKEEPING' then
334
335 UPDATE_CON_DATE_CHK(P_PERSON_EXTRA_INFO_ID
336 ,P_PEI_INFORMATION_CATEGORY
337 ,P_PEI_INFORMATION1
338 ,P_PEI_INFORMATION2);
339
340 end if;
341
342 END UPDATE_PL_PERSON_EXTRA_INFO;
343
344 --
345 END PER_PL_PERSON_EXTRA_INFO;
[Home] [Help]