1 PACKAGE BODY IGS_PE_GEN_004 AS
2 /* $Header: IGSPE20B.pls 120.2 2005/10/06 01:28:07 appldev noship $ */
3
4 /* +=======================================================================+
5 | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | NAME |
9 | IGSVTFPB.pls |
10 | |
11 | DESCRIPTION |
12 | This package provides service functions and procedures to |
13 | print hte text file from the EDS system definition . |
14 | |
15 | NOTES |
16 | |
17 | DEPENDENCIES |
18 | |
19 | USAGE |
20 | |
21 | HISTORY |
22 +=======================================================================+ */
23
24 FUNCTION GET_RACE_DTLS(P_PERSON_ID IN NUMBER)
25 RETURN VARCHAR2 IS
26 /*
27 Purpose: This function returns a concatendated string of all the Races the person
28 has. Each of the value is concatenated with a comma (,)
29 Used in PersBiographicsVO.xml
30 Change History:
31 Who When What
32
33 */
34 CURSOR race_dtls_cur (cp_person_id igs_pe_hz_parties.party_id%TYPE,
35 cp_lookup_type igs_lookup_values.lookup_type%TYPE) IS
36 SELECT lk.meaning
37 FROM igs_lookup_values lk, igs_pe_race race
38 WHERE race.person_id = cp_person_id AND
39 lk.lookup_type = cp_lookup_type AND
40 lk.lookup_code = race.race_cd;
41
42 race_dtls_rec race_dtls_cur%ROWTYPE;
43 l_concat_race VARCHAR2(2000);
44 BEGIN
45 l_concat_race := NULL;
46
47 OPEN race_dtls_cur(p_person_id, 'PE_RACE');
48 FETCH race_dtls_cur INTO race_dtls_rec;
49 IF (race_dtls_cur%FOUND) THEN
50 l_concat_race := race_dtls_rec.meaning;
51 LOOP
52 FETCH race_dtls_cur INTO race_dtls_rec;
53 EXIT WHEN race_dtls_cur%NOTFOUND;
54 l_concat_race := l_concat_race ||', ' ||race_dtls_rec.meaning;
55 END LOOP;
56 END IF;
57
58 CLOSE race_dtls_cur;
59
60 RETURN l_concat_race;
61
62 END GET_RACE_DTLS;
63
64 FUNCTION PERM_RES_COUNTRY_DTL (
65 P_PERSON_ID IN VARCHAR2,
66 P_DETAIL IN VARCHAR2)
67 RETURN VARCHAR2 IS
68 /*
69 Purpose: This function checks for the current active record from the IGS_PE_EIT table for the information type = 'PE_INT_PERM_RES' and
70 country as setup in the profile OSS_COUNTRY_CODE.
71 For P_DETAIL = 'COUNTRY', It returns Y if there is a record. Returns N if there is a record for any other country. Else
72 checks whether any active citizenship record exists with country other than the one in the profile OSS_COUNTRY_CODE if
73 there is a record then returns 'N' Else returns NULL.
74 For P_DETAIL = 'REGNO', returns the pei_information2 (registration number)
75 Used in the ApplCitizensVO.xml
76 Change History:
77 Who When What
78
79 */
80
81 CURSOR check_eit_cur (cp_person_id NUMBER,
82 cp_information_type VARCHAR2,
83 cp_country_code VARCHAR2) IS
84 SELECT PEI_INFORMATION2, START_DATE
85 FROM IGS_PE_EIT
86 WHERE PERSON_ID = cp_person_id AND
87 INFORMATION_TYPE = cp_information_type AND
88 PEI_INFORMATION1= cp_country_code AND
89 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
90
91 CURSOR check_eit_exists_cur (cp_person_id NUMBER,
92 cp_information_type VARCHAR2) IS
93 SELECT PEI_INFORMATION2, START_DATE
94 FROM IGS_PE_EIT
95 WHERE PERSON_ID = cp_person_id AND
96 INFORMATION_TYPE = cp_information_type;
97
98 CURSOR active_ctzn_cur (cp_person_id NUMBER,
99 cp_country_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE)
100 IS
101 SELECT 'Y'
102 FROM hz_citizenship
103 WHERE country_code <> cp_country_code AND
104 party_id = cp_person_id AND
105 SYSDATE BETWEEN NVL(DATE_RECOGNIZED,SYSDATE) AND NVL(END_DATE,SYSDATE);
106
107 check_eit_exists_rec check_eit_exists_cur%ROWTYPE;
108 check_eit_rec check_eit_cur%ROWTYPE;
109 l_fnd_country_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE;
110 l_per_country_code VARCHAR2(30);
111 l_oth_ctzn_exists VARCHAR2(1);
112 BEGIN
113 l_fnd_country_code := FND_PROFILE.VALUE('OSS_COUNTRY_CODE');
114 l_per_country_code := convert_country_code (l_fnd_country_code);
115
116 OPEN check_eit_cur(p_person_id, 'PE_INT_PERM_RES', l_per_country_code);
117 FETCH check_eit_cur INTO check_eit_rec;
118 CLOSE check_eit_cur;
119
120 IF P_DETAIL = 'COUNTRY' THEN
121
122 IF check_eit_rec.start_date IS NOT NULL THEN
123 RETURN 'Y';
124 ELSE
125 OPEN check_eit_exists_cur(p_person_id, 'PE_INT_PERM_RES');
126 FETCH check_eit_exists_cur INTO check_eit_exists_rec;
127 CLOSE check_eit_exists_cur;
128
129 IF check_eit_exists_rec.start_date IS NOT NULL THEN
130 RETURN 'N';
131 ELSE
132 OPEN active_ctzn_cur(p_person_id, l_fnd_country_code);
133 FETCH active_ctzn_cur INTO l_oth_ctzn_exists;
134 CLOSE active_ctzn_cur;
135
136 IF l_oth_ctzn_exists = 'Y' THEN
137 RETURN 'N';
138 ELSE
139 RETURN NULL;
140 END IF;
141 END IF;
142 END IF;
143
144 ELSIF P_DETAIL = 'REGNO' THEN
145
146 RETURN check_eit_rec.pei_information2;
147
148 END IF;
149
150 END PERM_RES_COUNTRY_DTL;
151
152 FUNCTION perm_res_reg_date (
153 P_PERSON_ID IN VARCHAR2
154 )
155 RETURN DATE IS
156 /*
157 Purpose: This function checks for the current active record from the IGS_PE_EIT table for the information type = 'PE_INT_PERM_RES' and
158 country as setup in the profile OSS_COUNTRY_CODE.
159 Returns the start_date (registration date)
160 Used in the ApplCitizensVO.xml
161 Change History:
162 Who When What
163
164 */
165 CURSOR check_eit_cur (cp_person_id NUMBER,
166 cp_information_type VARCHAR2,
167 cp_country_code VARCHAR2) IS
168 SELECT PEI_INFORMATION2, START_DATE
169 FROM IGS_PE_EIT
170 WHERE PERSON_ID = cp_person_id AND
171 INFORMATION_TYPE = cp_information_type AND -- 'PE_INT_PERM_RES' AND
172 PEI_INFORMATION1= cp_country_code AND
173 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
174
175 check_eit_rec check_eit_cur%ROWTYPE;
176 l_fnd_country_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE;
177 l_per_country_code VARCHAR2(30);
178 BEGIN
179
180 l_fnd_country_code := FND_PROFILE.VALUE('OSS_COUNTRY_CODE');
181 l_per_country_code := convert_country_code (l_fnd_country_code);
182
183 OPEN check_eit_cur(p_person_id, 'PE_INT_PERM_RES', l_per_country_code);
184 FETCH check_eit_cur INTO check_eit_rec;
185 CLOSE check_eit_cur;
186
187 RETURN check_eit_rec.start_date;
188
189 END perm_res_reg_date;
190
191 FUNCTION CONVERT_COUNTRY_CODE (
192 P_COUNTRY_CODE IN VARCHAR2)
193 RETURN VARCHAR2 IS
194 /*
195 Purpose: This function takes the Country code defined in FND_TERRITORIES_VL as an input and returns the
196 corresponding mapping value as defined the lookup type PER_US_COUNTRY_CODE
197
198 Change History:
199 Who When What
200
201 */
202 CURSOR country_cur (cp_country_code VARCHAR2,
203 cp_lookup_type VARCHAR2) IS
204 SELECT SUBSTR(meaning,5,2)
205 FROM hr_lookups
206 WHERE lookup_type = cp_lookup_type
207 AND lookup_code= cp_country_code;
208
209 l_country_code VARCHAR2(80);
210 BEGIN
211 IF p_country_code = 'US' THEN
212
213 l_country_code := 'US';
214
215 ELSE
216
217 OPEN country_cur(P_COUNTRY_CODE, 'PQP_US_COUNTRY_TRANSLATE');
218 FETCH country_cur INTO l_country_code;
219 CLOSE country_cur;
220
221 END IF;
222
223 RETURN l_country_code;
224
225 END CONVERT_COUNTRY_CODE;
226
227 PROCEDURE PROCESS_APPLCITIZEN (
228 P_PERSON_ID IN NUMBER,
229 P_HAS_CITIZEN IN VARCHAR2,
230 P_CITIZEN_COUNTRY IN VARCHAR2,
231 P_PERM_RES IN VARCHAR2,
232 P_REG_NUMBER IN VARCHAR2,
233 P_REG_DATE IN DATE,
234 P_MSG_DATA OUT NOCOPY VARCHAR2
235 ) IS
236 /*
237 Purpose: This procedure is used to process records in the Applicant Citizenship component.
238 PersApplCtznRN.xml
239 Change History:
240 Who When What
241
242 */
243 CURSOR student_check_cur (cp_person_id NUMBER,
244 cp_system_type VARCHAR2)
245 IS
246 SELECT 1
247 FROM igs_pe_typ_instances_all inst,
248 igs_pe_person_types typ
249 WHERE inst.person_type_code = typ.person_type_code AND
250 inst.person_id = cp_person_id AND
251 typ.system_type = cp_system_type AND
252 SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE);
253
254 CURSOR ctzn_country_cur (cp_person_id NUMBER,
255 cp_country_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE)
256 IS
257 SELECT citizenship_id, country_code, object_version_number, date_recognized, end_date, document_reference,
258 document_type, birth_or_selected
259 FROM hz_citizenship
260 WHERE country_code = cp_country_code AND
261 party_id = cp_person_id;
262
263 ctzn_country_rec ctzn_country_cur%ROWTYPE;
264
265 CURSOR active_ctzn_cur (cp_person_id NUMBER,
266 cp_country_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE)
267 IS
268 SELECT birth_or_selected, country_code, date_recognized, document_reference, document_type,
269 citizenship_id, object_version_number
270 FROM hz_citizenship
271 WHERE country_code <> cp_country_code AND
272 party_id = cp_person_id AND
273 SYSDATE BETWEEN NVL(DATE_RECOGNIZED,SYSDATE) AND NVL(END_DATE,SYSDATE);
274
275 CURSOR active_permres_cur(cp_person_id NUMBER,
276 cp_country_code VARCHAR2)
277 IS
278 SELECT rowid, pe_eit_id, pei_information1, pei_information2, start_date, end_date
279 FROM igs_pe_eit
280 WHERE information_type = 'PE_INT_PERM_RES' AND
281 pei_information1 = cp_country_code AND
282 person_id = cp_person_id AND
283 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
284
285 CURSOR oth_active_permres_cur(cp_person_id NUMBER,
286 cp_country_code VARCHAR2)
287 IS
288 SELECT rowid, pe_eit_id, pei_information1, pei_information2, start_date, end_date
289 FROM igs_pe_eit
290 WHERE information_type = 'PE_INT_PERM_RES' AND
291 pei_information1 <> cp_country_code AND
292 person_id = cp_person_id AND
293 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
294
295 CURSOR permres_cur(cp_person_id NUMBER,
296 cp_country_code VARCHAR2)
297 IS
298 SELECT rowid, pe_eit_id, pei_information1, pei_information2, start_date, end_date
299 FROM igs_pe_eit
300 WHERE information_type = 'PE_INT_PERM_RES' AND
301 pei_information1 = cp_country_code AND
302 person_id = cp_person_id;
303
304 permres_rec permres_cur%ROWTYPE;
305
306 l_count NUMBER(1);
307 l_fnd_country_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE;
308 l_per_country_code VARCHAR2(30);
309 l_object_version_number hz_citizenship.object_version_number%TYPE;
310 l_last_update_date hz_citizenship.last_update_date%TYPE;
311 l_citizenship_id hz_citizenship.citizenship_id%TYPE;
312 l_rowid ROWID;
313 l_pe_eit_id igs_pe_eit.pe_eit_id%TYPE;
314 l_message VARCHAR2(2000);
315 l_return_status VARCHAR2(10);
316 l_msg_count NUMBER(2);
317 BEGIN
318
319 -- If the person is a Student then don't allow him to update any data
320 OPEN student_check_cur (p_person_id, 'STUDENT');
321 FETCH student_check_cur INTO l_count;
322 IF student_check_cur%FOUND THEN
323 CLOSE student_check_cur;
324 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_SS_CTZN_UPD_INST');
325 IGS_GE_MSG_STACK.ADD;
326 APP_EXCEPTION.RAISE_EXCEPTION;
327 END IF;
328 CLOSE student_check_cur;
329
330 l_fnd_country_code := FND_PROFILE.VALUE('OSS_COUNTRY_CODE');
331 l_per_country_code := convert_country_code (l_fnd_country_code);
332
333 /* Logic if p_has_citizen = 'Y' THEN
334 -- Check whether in HZ_CITIZENSHIP the person has any record with the Country as in the profile OSS_COUNTRY_CODE.
335 If there is no record create a record in HZ_CITIZENSHIP passing the Country as in the profile OSS_COUNTRY_CODE
336 and DATE_RECOGNIZED as the Current Date.
337 -- If there is already a Record with the Country as in the profile OSS_COUNTRY_CODE then update the record with end date as NULL.
338 -- In both the above scnerios if there is any other Active record with country other than the Country as in the
339 profile OSS_COUNTRY_CODE, then make them inactive (set the end date of those records as current date).
340 -- If there is any active record in IGS_PE_EIT for INFORMATION_TYPE = 'PE_INT_PERM_RES' and Country = as in profile
341 OSS_COUNTRY_CODE then update the record with end date as Current Date.
342 */
343
344 IF p_has_citizen = 'Y' THEN
345 OPEN ctzn_country_cur(p_person_id, l_fnd_country_code);
346 FETCH ctzn_country_cur INTO ctzn_country_rec;
347 CLOSE ctzn_country_cur;
348
349 IF ctzn_country_rec.citizenship_id IS NULL THEN
350
351 IGS_PE_CITIZENSHIPS_PKG.CITIZENSHIP(
352 p_action => 'INSERT',
353 P_birth_or_selected => NULL,
354 P_country_code => l_fnd_country_code,
355 p_date_disowned => NULL,
356 p_date_recognized => TRUNC(SYSDATE),
357 p_DOCUMENT_REFERENCE => NULL,
358 p_DOCUMENT_TYPE => NULL,
359 p_PARTY_ID => P_PERSON_ID,
360 p_END_DATE => NULL,
361 p_TERRITORY_SHORT_NAME => NULL,
362 p_last_update_date => l_last_update_date,
363 P_citizenship_id => l_citizenship_id,
364 p_return_status => l_return_status,
365 p_msg_count => l_msg_count,
366 p_msg_data => p_msg_data,
367 p_object_version_number => l_object_version_number
368 );
369
370 ELSE -- citizenship_id is NULL
371
372 IF ctzn_country_rec.end_date IS NOT NULL THEN
373
374 IGS_PE_CITIZENSHIPS_PKG.CITIZENSHIP(
375 p_action => 'UPDATE',
376 P_birth_or_selected => NULL,
377 P_country_code => l_fnd_country_code,
378 p_date_disowned => NULL,
379 p_date_recognized => ctzn_country_rec.date_recognized,
380 p_document_reference => ctzn_country_rec.document_reference,
381 p_document_type => ctzn_country_rec.document_type,
382 p_party_id => p_person_id,
383 p_end_date => NULL,
384 p_territory_short_name => NULL,
385 p_last_update_date => l_last_update_date,
386 P_citizenship_id => ctzn_country_rec.citizenship_id,
387 p_return_status => l_return_status,
388 p_msg_count => l_msg_count,
389 p_msg_data => p_msg_data,
390 p_object_version_number => ctzn_country_rec.object_version_number
391 );
392
393 END IF;
394 END IF;
395
396 IF l_return_status IS NULL OR l_return_status = 'S' THEN
397 FOR active_ctzn_rec IN active_ctzn_cur(p_person_id, l_fnd_country_code) LOOP
398
399 IGS_PE_CITIZENSHIPS_PKG.CITIZENSHIP(
400 p_action => 'UPDATE',
401 P_birth_or_selected => active_ctzn_rec.birth_or_selected,
402 P_country_code => active_ctzn_rec.country_code,
403 p_date_disowned => NULL,
404 p_date_recognized => active_ctzn_rec.date_recognized,
405 p_document_reference => active_ctzn_rec.document_reference,
406 p_document_type => active_ctzn_rec.document_type,
407 p_party_id => p_person_id,
408 p_end_date => TRUNC(SYSDATE),
409 p_territory_short_name => NULL,
410 p_last_update_date => l_last_update_date,
411 P_citizenship_id => active_ctzn_rec.citizenship_id,
412 p_return_status => l_return_status,
413 p_msg_count => l_msg_count,
414 p_msg_data => p_msg_data,
415 p_object_version_number => active_ctzn_rec.object_version_number
416 );
417 END LOOP;
418 END IF;
419
420 IF l_return_status IS NULL OR l_return_status = 'S' THEN
421 FOR active_permres_rec IN active_permres_cur(p_person_id, l_per_country_code) LOOP
422
423 igs_pe_eit_pkg.update_row (
424 X_ROWID => active_permres_rec.rowid,
425 X_PE_EIT_ID => active_permres_rec.pe_eit_id,
426 X_PERSON_ID => p_person_id,
427 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
428 X_PEI_INFORMATION1 => active_permres_rec.pei_information1,
429 X_PEI_INFORMATION2 => active_permres_rec.pei_information2,
430 X_PEI_INFORMATION3 => NULL,
431 X_PEI_INFORMATION4 => NULL,
432 X_PEI_INFORMATION5 => NULL,
433 X_START_DATE => active_permres_rec.start_date,
434 X_END_DATE => TRUNC(SYSDATE)
435 );
436
437 END LOOP;
438 END IF;
439
440 /* Logic if p_has_citizen = 'N' THEN
441 -- Check whether in HZ_CITIZENSHIP the person has any record with the country as in P_CITIZEN_COUNTRY. If there is no record
442 create a record in HZ_CITIZENSHIP passing the Country as in P_CITIZEN_COUNTRY and DATE_RECOGNIZED as the Current Date.
443 -- If there is already a Record with the country as in P_CITIZEN_COUNTRY then update the record with the end date as NULL.
444 -- In both the above scnerios if there is any other Active record (Current Date between Start Date and End Date) with country
445 other than P_CITIZEN_COUNTRY, then make them inactive (set the end date of those records as current date).
446 */
447 ELSIF p_has_citizen = 'N' THEN
448 IF p_citizen_country IS NULL THEN
449 RETURN;
450 END IF;
451
452 OPEN ctzn_country_cur(p_person_id, p_citizen_country);
453 FETCH ctzn_country_cur INTO ctzn_country_rec;
454 CLOSE ctzn_country_cur;
455
456 IF ctzn_country_rec.citizenship_id IS NULL THEN
457
458 igs_pe_citizenships_pkg.citizenship(
459 p_action => 'INSERT',
460 P_birth_or_selected => NULL,
461 P_country_code => p_citizen_country,
462 p_date_disowned => NULL,
463 p_date_recognized => TRUNC(SYSDATE),
464 p_DOCUMENT_REFERENCE => NULL,
465 p_DOCUMENT_TYPE => NULL,
466 p_PARTY_ID => P_PERSON_ID,
467 p_END_DATE => NULL,
468 p_TERRITORY_SHORT_NAME => NULL,
469 p_last_update_date => l_last_update_date,
470 P_citizenship_id => l_citizenship_id,
471 p_return_status => l_return_status,
472 p_msg_count => l_msg_count,
473 p_msg_data => p_msg_data,
474 p_object_version_number => l_object_version_number
475 );
476
477 ELSE
478 IF ctzn_country_rec.end_date IS NOT NULL THEN
479
480 IGS_PE_CITIZENSHIPS_PKG.CITIZENSHIP(
481 p_action => 'UPDATE',
482 P_birth_or_selected => NULL,
483 P_country_code => p_citizen_country,
484 p_date_disowned => NULL,
485 p_date_recognized => ctzn_country_rec.date_recognized,
486 p_document_reference => ctzn_country_rec.document_reference,
487 p_document_type => ctzn_country_rec.document_type,
488 p_party_id => p_person_id,
489 p_end_date => NULL,
490 p_territory_short_name => NULL,
491 p_last_update_date => l_last_update_date,
492 P_citizenship_id => ctzn_country_rec.citizenship_id,
493 p_return_status => l_return_status,
494 p_msg_count => l_msg_count,
495 p_msg_data => p_msg_data,
496 p_object_version_number => ctzn_country_rec.object_version_number
497 );
498
499 END IF;
500
501 END IF; -- citizenship_id
502
503 IF l_return_status IS NULL OR l_return_status = 'S' THEN
504
505 FOR active_ctzn_rec IN active_ctzn_cur(p_person_id, p_citizen_country) LOOP
506
507 IGS_PE_CITIZENSHIPS_PKG.CITIZENSHIP(
508 p_action => 'UPDATE',
509 P_birth_or_selected => active_ctzn_rec.birth_or_selected,
510 P_country_code => active_ctzn_rec.country_code,
511 p_date_disowned => NULL,
512 p_date_recognized => active_ctzn_rec.date_recognized,
513 p_document_reference => active_ctzn_rec.document_reference,
514 p_document_type => active_ctzn_rec.document_type,
515 p_party_id => p_person_id,
516 p_end_date => TRUNC(SYSDATE),
517 p_territory_short_name => NULL,
518 p_last_update_date => l_last_update_date,
519 P_citizenship_id => active_ctzn_rec.citizenship_id,
520 p_return_status => l_return_status,
521 p_msg_count => l_msg_count,
522 p_msg_data => p_msg_data,
523 p_object_version_number => active_ctzn_rec.object_version_number
524 );
525
526 END LOOP;
527 END IF;
528 /*
529 If P_PERM_RES = YES, then
530 -- Check whether in IGS_PE_EIT for INFORMATION_TYPE = 'PE_INT_PERM_RES' there is any record.
531 Since in IGS_PE_EIT the country is stored from the lookup PER_US_COUNTRY_CODE, call the Function CONVERT_COUNTRY_CODE
532 to get the proper Country Code for the value set in the profile OSS_COUNTRY_CODE.
533 -- If there is no record then insert a new record in the table IGS_PE_EIT,
534 -- If there is already a record then check whether any record present with Country as in the profile OSS_COUNTRY_CODE.
535 If there is already a record present then Update the start date with P_REG_DATE and Registration Number as P_REG_NUMBER.
536 -- If the existing record's Country Code is different from the profile OSS_COUNTRY_CODE then end date any active record
537 with end date = P_REG_DATE - 1 and create a new record as above.
538 */
539
540 IF P_PERM_RES = 'Y' THEN
541 OPEN permres_cur(p_person_id, l_per_country_code);
542 FETCH permres_cur INTO permres_rec;
543 CLOSE permres_cur;
544
545 IF permres_rec.pe_eit_id IS NULL THEN
546
547 igs_pe_eit_pkg.insert_row (
548 X_ROWID => l_rowid,
549 X_PE_EIT_ID => l_pe_eit_id,
550 X_PERSON_ID => p_person_id,
551 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
552 X_PEI_INFORMATION1 => l_per_country_code,
553 X_PEI_INFORMATION2 => p_reg_number,
554 X_PEI_INFORMATION3 => NULL,
555 X_PEI_INFORMATION4 => NULL,
556 X_PEI_INFORMATION5 => NULL,
557 X_START_DATE => p_reg_date,
558 X_END_DATE => NULL
559 );
560
561 ELSE
562 igs_pe_eit_pkg.update_row (
563 X_ROWID => permres_rec.rowid,
564 X_PE_EIT_ID => permres_rec.pe_eit_id,
565 X_PERSON_ID => p_person_id,
566 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
567 X_PEI_INFORMATION1 => permres_rec.pei_information1,
568 X_PEI_INFORMATION2 => p_reg_number,
569 X_PEI_INFORMATION3 => NULL,
570 X_PEI_INFORMATION4 => NULL,
571 X_PEI_INFORMATION5 => NULL,
572 X_START_DATE => p_reg_date,
573 X_END_DATE => NULL
574 );
575
576 END IF;
577
578 FOR oth_active_permres_rec IN oth_active_permres_cur(p_person_id, l_per_country_code) LOOP
579
580 igs_pe_eit_pkg.update_row (
581 X_ROWID => oth_active_permres_rec.rowid,
582 X_PE_EIT_ID => oth_active_permres_rec.pe_eit_id,
583 X_PERSON_ID => p_person_id,
584 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
585 X_PEI_INFORMATION1 => oth_active_permres_rec.pei_information1,
586 X_PEI_INFORMATION2 => oth_active_permres_rec.pei_information2,
587 X_PEI_INFORMATION3 => NULL,
588 X_PEI_INFORMATION4 => NULL,
589 X_PEI_INFORMATION5 => NULL,
590 X_START_DATE => oth_active_permres_rec.start_date,
591 X_END_DATE => p_reg_date - 1
592 );
593
594 END LOOP;
595
596 /*
597 If P_PERM_RES = NO, then
598 Check if there is any active record in IGS_PE_EIT for INFORMATION_TYPE = 'PE_INT_PERM_RES' and Country = as in profile
599 OSS_COUNTRY_CODE then update the record with end date as Current Date.
600 */
601 ELSIF P_PERM_RES = 'N' THEN
602
603 FOR active_permres_rec IN active_permres_cur(p_person_id, l_per_country_code) LOOP
604
605 igs_pe_eit_pkg.update_row (
606 X_ROWID => active_permres_rec.rowid,
607 X_PE_EIT_ID => active_permres_rec.pe_eit_id,
608 X_PERSON_ID => p_person_id,
609 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
610 X_PEI_INFORMATION1 => active_permres_rec.pei_information1,
611 X_PEI_INFORMATION2 => active_permres_rec.pei_information2,
612 X_PEI_INFORMATION3 => NULL,
613 X_PEI_INFORMATION4 => NULL,
614 X_PEI_INFORMATION5 => NULL,
615 X_START_DATE => active_permres_rec.start_date,
616 X_END_DATE => TRUNC(SYSDATE)
617 );
618
619 END LOOP;
620
621 END IF;
622
623 END IF; -- P_HAS_CITIZEN
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 l_message := FND_MESSAGE.GET;
628
629 IF l_message IS NOT NULL THEN
630 p_msg_data := l_message;
631 ELSE
632 p_msg_data := SQLERRM;
633 END IF;
634 END PROCESS_APPLCITIZEN;
635
636
637 FUNCTION VALIDATE_FELONY (
638 P_PERSON_ID IN NUMBER,
639 P_EVER_FELONY_CONVICTED IN VARCHAR2)
640 RETURN VARCHAR2 IS
641 /*
642 Purpose: This procedure is used to validate the Felony details.
643 If P_EVER_FELONY_CONVICTED IS NULL then there should not be any records in the Child table.
644 If P_EVER_FELONY_CONVICTED = 'N' then there should not be any records in the child table with CONVICT_IND = 'Y'
645
646 Change History:
647 Who When What
648
649 */
650 CURSOR records_null_dtls_cur (cp_person_id igs_pe_felony_dtls.person_id%TYPE) IS
651 SELECT 'X'
652 FROM igs_pe_felony_dtls
653 WHERE person_id=cp_person_id;
654
655 CURSOR records_no_dtls_cur (cp_person_id igs_pe_felony_dtls.person_id%TYPE) IS
656 SELECT 'X'
657 FROM igs_pe_felony_dtls
658 WHERE person_id=cp_person_id
659 AND convict_ind='Y';
660
661 l_race_null_dtls_rec VARCHAR2(1);
662 l_race_no_dtls_rec VARCHAR2(1);
663 l_message varchar2(35):=NULL;
664 BEGIN
665
666 IF P_EVER_FELONY_CONVICTED IS NULL THEN
667 OPEN records_null_dtls_cur (P_PERSON_ID);
668 FETCH records_null_dtls_cur INTO l_race_null_dtls_rec;
669 IF records_null_dtls_cur%FOUND THEN
670 l_message:= 'IGS_PE_SS_FLNY_CONVICT_NOBLANK';
671 END IF;
672 CLOSE records_null_dtls_cur;
673
674 ELSIF P_EVER_FELONY_CONVICTED = 'N' THEN
675 OPEN records_no_dtls_cur (P_PERSON_ID);
676 FETCH records_no_dtls_cur INTO l_race_no_dtls_rec;
677 IF records_no_dtls_cur%FOUND THEN
678 l_message:= 'IGS_PE_SS_FLNY_CONVICT_NOTNO';
679 END IF;
680 CLOSE records_no_dtls_cur;
681 END IF;
682
683 RETURN l_message;
684 END VALIDATE_FELONY;
685
686 FUNCTION get_load_teach_concat(
687 p_teach_cal_type IN VARCHAR2,
688 p_teach_seq_number IN NUMBER
689 ) RETURN VARCHAR2 IS
690 /*
691 Purpose: This function returns the concatenation of the teach_description ||'/'||load_description for the latest load calendar
692
693 Change History:
694 Who When What
695
696 */
697 CURSOR cur_cal_desc (cp_teach_cal_type VARCHAR2, cp_teach_seq_no VARCHAR2)IS
698 SELECT teach_description ||'/'||load_description
699 FROM igs_ca_teach_to_load_v
700 WHERE teach_cal_type = cp_teach_cal_type
701 AND teach_ci_sequence_number = cp_teach_seq_no
702 ORDER BY load_start_dt DESC;
703
704 l_cal_desc VARCHAR2(60);
705 BEGIN
706
707 OPEN cur_cal_desc(p_teach_cal_type, p_teach_seq_number);
708 FETCH cur_cal_desc INTO l_cal_desc;
709 CLOSE cur_cal_desc;
710
711 RETURN l_cal_desc;
712
713 END get_load_teach_concat;
714
715 FUNCTION get_start_term (
716 p_acad_cal_type IN VARCHAR2 ,
717 p_prog_commencement_dt IN VARCHAR2
718 ) RETURN VARCHAR2 AS
719 /*
720 Purpose: This function returns the term in which the program was started by the student.
721 Used in PeProgramAttemptsVO.xml
722 Change History:
723 Who When What
724
725 */
726 l_load_cal_type IGS_CA_INST_ALL.CAL_TYPE%TYPE;
727 l_load_seq_number IGS_CA_INST_ALL.SEQUENCE_NUMBER%TYPE;
728 l_load_alt_code IGS_CA_INST_ALL.ALTERNATE_CODE%TYPE;
729 l_load_start_dt IGS_CA_INST_ALL.START_DT%TYPE;
730 l_load_end_dt IGS_CA_INST_ALL.END_DT%TYPE;
731 l_message_name VARCHAR2(30);
732 l_load_cal_desc IGS_CA_INST_ALL.DESCRIPTION%TYPE;
733
734 CURSOR cur_cal_desc(cp_load_cal_typ igs_ca_inst.cal_type%TYPE,
735 cp_cal_seq_no igs_ca_inst.sequence_number%TYPE) IS
736 SELECT description
737 FROM igs_ca_inst_all
738 WHERE cal_type = cp_load_cal_typ
739 AND sequence_number = cp_cal_seq_no;
740
741 BEGIN
742
743 IGS_EN_GEN_015.GET_CURR_ACAD_TERM_CAL(
744 P_ACAD_CAL_TYPE => p_acad_cal_type,
745 P_EFFECTIVE_DT => p_prog_commencement_dt,
746 P_LOAD_CAL_TYPE => l_load_cal_type,
747 P_LOAD_CI_SEQ_NUM => l_load_seq_number,
748 P_LOAD_CI_ALT_CODE => l_load_alt_code,
749 P_LOAD_CI_START_DT => l_load_start_dt,
750 P_LOAD_CI_END_DT => l_load_end_dt,
751 P_MESSAGE_NAME => l_message_name
752 );
753
754 IF l_message_name IS NOT NULL THEN
755 RETURN NULL;
756 ELSE
757 OPEN cur_cal_desc(l_load_cal_type, l_load_seq_number);
758 FETCH cur_cal_desc INTO l_load_cal_desc;
759 CLOSE cur_cal_desc;
760 RETURN l_load_cal_desc;
761 END IF;
762 END get_start_term;
763
764 FUNCTION GET_OTHER_NAMES (
765 P_PERSON_ID IN NUMBER
766 ) RETURN VARCHAR2 AS
767 /*
768 Purpose: This function returns a string with concatenated values of alias type, surname and given name.
769 Used in FindPersonSearchResultsVO.xml
770 Change History:
771 Who When What
772
773 */
774 CURSOR cur_alias_type(cp_person_id NUMBER) IS
775 SELECT MEANING||': '||ALIAS.SURNAME||' '||ALIAS.GIVEN_NAMES OTHER_NAMES
776 FROM IGS_PE_PERSON_ALIAS ALIAS, IGS_LOOKUP_VALUES LKUP
777 WHERE ALIAS.ALIAS_TYPE = LKUP.LOOKUP_CODE
778 AND LKUP.LOOKUP_TYPE = 'PE_ALIAS_TYPE'
779 AND ALIAS.PERSON_ID = cp_person_id;
780
781 L_OTHER_NAMES VARCHAR2(4000);
782 BEGIN
783
784 FOR REC_ALIAS_TYPE IN cur_alias_type(P_PERSON_ID)
785 LOOP
786 IF L_OTHER_NAMES IS NOT NULL THEN
787 L_OTHER_NAMES := L_OTHER_NAMES||';<BR>'||REC_ALIAS_TYPE.OTHER_NAMES;
788 ELSE
789 L_OTHER_NAMES := REC_ALIAS_TYPE.OTHER_NAMES;
790 END IF;
791 END LOOP;
792
793 RETURN L_OTHER_NAMES;
794
795 END GET_OTHER_NAMES;
796
797 PROCEDURE SKIP_MAND_DATA_VAL AS
798 /*
799 Purpose: This procedure is to set the Global variable that will be called before the call of the API that creates
800 Student/Applicant person type in Self-service. If the Global variable is set to Y then in the TBH the Mandatory
801 Data by person type validation will be skipped.
802
803 Change History:
804 Who When What
805
806 */
807 BEGIN
808
809 G_SKIP_MAND_DATA_VAL := 'Y';
810
811 END SKIP_MAND_DATA_VAL;
812
813 PROCEDURE add_attachment(
814 P_PERSON_ID IN NUMBER,
815 P_CRED_TYPE_ID IN NUMBER,
816 P_FILE_NAME IN VARCHAR2,
817 P_FILE_CONTENT_TYPE IN VARCHAR2,
818 P_FILE_FORMAT IN VARCHAR2,
819 P_FILE_ID OUT NOCOPY NUMBER,
820 P_MSG_DATA OUT NOCOPY VARCHAR2
821 ) IS
822 /*
823 Purpose: This procedure inserts record in the Credential and Fnd_Lobs table.
824 After the successful insertion of these records it calls the FND_WEBATTCH API to create the link in the
825 attachment tables for the Entity IGS_PE_CREDENTIALS
826
827 Change History:
828 Who When What
829
830 */
831 l_fileid NUMBER;
832 lv_rowid ROWID;
833 l_credential_id NUMBER;
834 l_category_id NUMBER;
835
836 CURSOR cat_cur(cp_name VARCHAR2) IS
837 SELECT category_id
838 FROM fnd_document_categories_tl
839 WHERE name = cp_name;
840
841 CURSOR fileid_cur IS
842 SELECT FND_LOBS_S.NEXTVAL FROM dual;
843
844 BEGIN
845 -- Insert data in the fnd_lobs table
846 OPEN fileid_cur;
847 FETCH fileid_cur INTO l_fileid;
848 CLOSE fileid_cur;
849
850 INSERT INTO FND_LOBS(
851 FILE_ID,
852 FILE_NAME,
853 FILE_CONTENT_TYPE,
854 UPLOAD_DATE,
855 EXPIRATION_DATE,
856 PROGRAM_NAME,
857 PROGRAM_TAG,
858 LANGUAGE,
859 ORACLE_CHARSET,
860 FILE_FORMAT
861 ) VALUES
862 (
863 l_fileid,
864 P_FILE_NAME,
865 P_FILE_CONTENT_TYPE,
866 SYSDATE,
867 NULL,
868 NULL,
869 NULL,
870 USERENV('LANG'),
871 NULL,
872 P_FILE_FORMAT
873 );
874
875 P_FILE_ID := l_fileid;
876
877 igs_pe_credentials_pkg.insert_row (
878 x_mode => 'R',
879 x_rowid => lv_rowid,
880 x_credential_id => l_credential_id,
881 x_person_id => P_PERSON_ID,
882 x_credential_type_id => P_CRED_TYPE_ID,
883 x_date_received => TO_DATE(NULL),
884 x_reviewer_id => TO_NUMBER(NULL),
885 x_reviewer_notes => NULL,
886 x_recommender_name => NULL,
887 x_recommender_title => NULL,
888 x_recommender_organization => NULL,
889 x_rating_code => NULL
890 );
891
892 OPEN cat_cur('CUSTOM1499');
893 FETCH cat_cur INTO l_category_id;
894 CLOSE cat_cur;
895
896 FND_WEBATTCH.Add_Attachment (
897 seq_num => '1',
898 category_id => l_category_id,
899 document_description => NULL,
900 datatype_id => 6,
901 text => NULL,
902 file_name => P_FILE_NAME,
903 url => NULL,
904 function_name => NULL,
905 entity_name => 'IGS_PE_CREDENTIALS',
906 pk1_value => l_credential_id,
907 pk2_value => NULL,
908 pk3_value => NULL,
909 pk4_value => NULL,
910 pk5_value => NULL,
911 media_id => l_fileid,
912 user_id => FND_GLOBAL.USER_ID
913 );
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 P_MSG_DATA := SQLERRM;
918 END add_attachment;
919
920 PROCEDURE delete_attachment(
921 P_CREDENTIAL_ID IN NUMBER,
922 P_DOCUMENT_ID IN NUMBER,
923 P_MSG_DATA OUT NOCOPY VARCHAR2
924 ) IS
925 /*
926 Purpose: This procedure first removes the link for the attachment created and then deletes the record
927 from the IGS_PE_CREDENTIALS table
928
929 Change History:
930 Who When What
931
932 */
933 l_rowid ROWID;
934 BEGIN
935
936 FND_DOCUMENTS_PKG.DELETE_ROW(
937 x_document_id => P_DOCUMENT_ID,
938 x_datatype_id => 6,
939 delete_ref_Flag => 'Y'
940 );
941
942 SELECT ROWID INTO l_rowid
943 FROM igs_pe_credentials
944 WHERE credential_id = P_CREDENTIAL_ID;
945
946 igs_pe_credentials_pkg.delete_row(
947 x_rowid => l_rowid
948 );
949
950 EXCEPTION
951 WHEN OTHERS THEN
952 P_MSG_DATA := SQLERRM;
953 END delete_attachment;
954
955 FUNCTION get_deceased_indicator (
956 p_person_id IN NUMBER
957 ) RETURN VARCHAR2 AS
958 /*
959 Purpose: This functions returns the deceased indicator.
960 First it checks whether whether date_od_death is present in HZ_PERSON_PROFILES
961 If not then check whether deceased_ind is set in the IGS_PE_HZ_PARTIES table.
962
963 Change History:
964 Who When What
965
966 */
967
968 CURSOR hz_dod_cur(cp_person_id NUMBER) IS
969 SELECT date_of_death
970 FROM igs_pe_person_base_v
971 WHERE person_id = cp_person_id;
972
973 CURSOR igs_dod_cur(cp_person_id NUMBER) IS
974 SELECT deceased_ind
975 FROM igs_pe_hz_parties
976 WHERE party_id = cp_person_id;
977
978 l_date_of_death DATE;
979 l_deceased_ind VARCHAR2(1);
980 BEGIN
981 OPEN hz_dod_cur(p_person_id);
982 FETCH hz_dod_cur INTO l_date_of_death;
983 CLOSE hz_dod_cur;
984
985 IF l_date_of_death IS NOT NULL THEN
986 RETURN 'Y';
987 ELSE
988
989 OPEN igs_dod_cur(p_person_id);
990 FETCH igs_dod_cur INTO l_deceased_ind;
991 CLOSE igs_dod_cur;
992
993 IF l_deceased_ind IS NOT NULL THEN
994 RETURN l_deceased_ind;
995 ELSE
996 RETURN 'N';
997 END IF;
998
999 END IF;
1000
1001 END get_deceased_indicator;
1002
1003 END igs_pe_gen_004;
[Home] [Help]