eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_PE_GEN_004 dependencies on IGS_PE_EIT

Line 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

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.

Line 85: FROM IGS_PE_EIT

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);

Line 94: FROM IGS_PE_EIT

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,

Line 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

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:

Line 169: FROM IGS_PE_EIT

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);

Line 279: FROM igs_pe_eit

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);

Line 289: FROM igs_pe_eit

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);

Line 299: FROM igs_pe_eit

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:

Line 313: l_pe_eit_id igs_pe_eit.pe_eit_id%TYPE;

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

Line 340: -- If there is any active record in IGS_PE_EIT for INFORMATION_TYPE = 'PE_INT_PERM_RES' and Country = as in profile

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

Line 423: igs_pe_eit_pkg.update_row (

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',

Line 530: -- Check whether in IGS_PE_EIT for INFORMATION_TYPE = 'PE_INT_PERM_RES' there is any record.

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.

Line 531: Since in IGS_PE_EIT the country is stored from the lookup PER_US_COUNTRY_CODE, call the Function CONVERT_COUNTRY_CODE

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.

Line 533: -- If there is no record then insert a new record in the table IGS_PE_EIT,

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.

Line 547: igs_pe_eit_pkg.insert_row (

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',

Line 562: igs_pe_eit_pkg.update_row (

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',

Line 580: igs_pe_eit_pkg.update_row (

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',

Line 598: Check if there is any active record in IGS_PE_EIT for INFORMATION_TYPE = 'PE_INT_PERM_RES' and Country = as in profile

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:

Line 605: igs_pe_eit_pkg.update_row (

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',