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_AD_SS_GEN_001 dependencies on IGS_AD_APPL

Line 82: p_admission_appl_number IN IGS_AD_APPL_PERSTAT.admission_appl_number%TYPE,

78: p_nominated_course_cd IN igs_ad_edugoal.nominated_course_cd%TYPE,
79: p_sequence_number IN igs_ad_edugoal.sequence_number%TYPE); --this procedure has been modified, nshee Bug 2622488
80: PROCEDURE transfer_attachment(p_person_id IN IGS_SS_APPL_PERSTAT.person_id%TYPE,
81: p_ss_adm_appl_id IN IGS_SS_APPL_PERSTAT.ss_adm_appl_id%TYPE,
82: p_admission_appl_number IN IGS_AD_APPL_PERSTAT.admission_appl_number%TYPE,
83: x_return_status OUT NOCOPY VARCHAR2);
84: -- Bug # 2389273 [ APPLICATION FEE SAVED IN SS IS NOT SAVED TO FORMS ]
85: --** added by nshee
86: PROCEDURE insert_application_fee(p_person_id IN igs_ad_app_req.person_id%TYPE,

Line 129: l_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;

125: *****************************************************************************************/
126:
127: l_message_name VARCHAR2(2000);
128: l_return_status VARCHAR2(2);
129: l_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
130: l_msg_index NUMBER;
131: l_msg_count NUMBER;
132: l_msg_data VARCHAR2(2000);
133: p_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;

Line 248: l_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE;

244: *****************************************************************************************/
245:
246: --Local Var Declaration
247: l_message_name VARCHAR2(2000);
248: l_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE;
249: l_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE;
250: l_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
251:
252: BEGIN

Line 250: l_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;

246: --Local Var Declaration
247: l_message_name VARCHAR2(2000);
248: l_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE;
249: l_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE;
250: l_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
251:
252: BEGIN
253: --Main Begin
254:

Line 1459: p_admission_appl_number IN IGS_AD_APPL_PERSTAT.admission_appl_number%TYPE,

1455:
1456: --Procedure for transferring Personal Statements/Attachments
1457: PROCEDURE transfer_attachment(p_person_id IN IGS_SS_APPL_PERSTAT.person_id%TYPE,
1458: p_ss_adm_appl_id IN IGS_SS_APPL_PERSTAT.ss_adm_appl_id%TYPE,
1459: p_admission_appl_number IN IGS_AD_APPL_PERSTAT.admission_appl_number%TYPE,
1460: x_return_status OUT NOCOPY VARCHAR2) IS
1461: /*****************************************************************************************
1462: Created By: [email protected]
1463: Date Created : 11-DEC-2001

Line 1470: rboddu 17-FEB-2002 in IGS_AD_APPL_PERSTAT_PKG.Insert_Row, date_received is passed

1466:
1467: Known limitations,enhancements,remarks:
1468: Change History
1469: Who When What
1470: rboddu 17-FEB-2002 in IGS_AD_APPL_PERSTAT_PKG.Insert_Row, date_received is passed
1471: NVL(c_ss_appl_perstat_rec.date_received,SYSDATE). Earlier it was just SYSDATE
1472: Bug : 2224624
1473: rboddu 05-MAR-2003 Passing Received_Date as TRUNC(SYSDATE) if it's null in the SS Table. Bug: 2731445
1474: *****************************************************************************************/

Line 1476: l_appl_perstat_id IGS_AD_APPL_PERSTAT.appl_perstat_id%TYPE;

1472: Bug : 2224624
1473: rboddu 05-MAR-2003 Passing Received_Date as TRUNC(SYSDATE) if it's null in the SS Table. Bug: 2731445
1474: *****************************************************************************************/
1475: l_rowid VARCHAR2(25);
1476: l_appl_perstat_id IGS_AD_APPL_PERSTAT.appl_perstat_id%TYPE;
1477: l_from_pk1_value VARCHAR2(100);
1478: l_to_pk1_value VARCHAR2(100);
1479: lv_mode VARCHAR2(1) DEFAULT 'R';
1480:

Line 1489: FROM igs_ad_appl_perstat

1485: AND ps.person_id = p_person_id;
1486:
1487: CURSOR c_ad_appl_perstat IS
1488: SELECT APPL_PERSTAT_ID
1489: FROM igs_ad_appl_perstat
1490: WHERE person_id = p_person_id
1491: AND admission_appl_number = p_admission_appl_number;
1492:
1493: c_ss_appl_perstat_rec c_ss_appl_perstat%ROWTYPE;

Line 1510: IGS_AD_APPL_PERSTAT_PKG.Insert_Row(x_rowid => l_rowid,

1506: and error will be caused , as per back office functionality application can be created without personal statement. So procedure should return status 'S' when there are no personal statement staging records */
1507: EXIT WHEN c_ss_appl_perstat%NOTFOUND;
1508: IF c_ss_appl_perstat_rec.ATTACH_EXISTS = 'Y' THEN
1509: BEGIN
1510: IGS_AD_APPL_PERSTAT_PKG.Insert_Row(x_rowid => l_rowid,
1511: x_appl_perstat_id => l_appl_perstat_id,
1512: x_person_id => c_ss_appl_perstat_rec.person_id,
1513: x_admission_appl_number => p_admission_appl_number,
1514: x_persl_stat_type => c_ss_appl_perstat_rec.persl_stat_type,

Line 1519: /* If insert is successful copy and delete the attachments to the IGS_AD_APPL_PERSTAT table */

1515: x_date_received => NVL(c_ss_appl_perstat_rec.date_received,
1516: TRUNC(SYSDATE)), --If the date_received passed from Self Service is null then insert SYSDATE into OSS Table
1517: x_mode => lv_mode -- enable security for Admin
1518: );
1519: /* If insert is successful copy and delete the attachments to the IGS_AD_APPL_PERSTAT table */
1520: BEGIN
1521:
1522: -- bug 2407148 fix (transferring multiple personal statements)
1523:

Line 1538: X_to_entity_name => 'IGS_AD_APPL_PERSTAT',

1534: X_from_pk2_value => NULL,
1535: X_from_pk3_value => NULL,
1536: X_from_pk4_value => NULL,
1537: X_from_pk5_value => NULL,
1538: X_to_entity_name => 'IGS_AD_APPL_PERSTAT',
1539: X_to_pk1_value => l_appl_perstat_id, -- l_to_pk1_value is not used bug 2407148 fix
1540: X_to_pk2_value => NULL,
1541: X_to_pk3_value => NULL,
1542: X_to_pk4_value => NULL,

Line 1706: Procedure Check_FeeExists(p_person_id IN igs_ad_appl_all.person_id%TYPE,

1702: END IF;
1703: END;
1704:
1705: -- Check to see if there exists any fee for this Application
1706: Procedure Check_FeeExists(p_person_id IN igs_ad_appl_all.person_id%TYPE,
1707: p_adm_appl_num IN igs_ad_appl_all.admission_appl_number%TYPE,
1708: x_return_status OUT NOCOPY VARCHAR2,
1709: x_message_data OUT NOCOPY VARCHAR2) AS
1710:

Line 1707: p_adm_appl_num IN igs_ad_appl_all.admission_appl_number%TYPE,

1703: END;
1704:
1705: -- Check to see if there exists any fee for this Application
1706: Procedure Check_FeeExists(p_person_id IN igs_ad_appl_all.person_id%TYPE,
1707: p_adm_appl_num IN igs_ad_appl_all.admission_appl_number%TYPE,
1708: x_return_status OUT NOCOPY VARCHAR2,
1709: x_message_data OUT NOCOPY VARCHAR2) AS
1710:
1711: l_fee_amount NUMBER;

Line 1954: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';

1950: END IF;
1951:
1952: IF l_return_status = 'E' THEN ----check_update_aeps_acs return status
1953: IF p_role <> 'ADMIN' THEN
1954: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';
1955: END IF;
1956: FND_MESSAGE.SET_NAME('IGS', l_msg_data);
1957: IGS_GE_MSG_STACK.ADD;
1958: x_return_status := 'E';

Line 1986: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';

1982:
1983: IF l_return_status = 'E' THEN --- Update_Appl_Eqdo_Inst return status
1984: IF p_role <> 'ADMIN' THEN
1985: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
1986: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';
1987: FND_MESSAGE.SET_NAME('IGS', l_msg_data);
1988: IGS_GE_MSG_STACK.ADD;
1989: END IF;
1990: x_return_status := 'E';

Line 2019: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';

2015:
2016: IF l_return_status = 'E' THEN -- check_offer_update return status
2017: IF p_role <> 'ADMIN' THEN
2018: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
2019: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';
2020: END IF;
2021: FND_MESSAGE.SET_NAME('IGS', l_msg_data);
2022: IGS_GE_MSG_STACK.ADD;
2023: x_return_status := 'E';

Line 2048: l_message_data := 'IGS_AD_APPL_PRC_FAILED';

2044:
2045: IF l_return_status = 'E' THEN -- Update_Appl_Ofr_Inst return status
2046: IF p_role <> 'ADMIN' THEN
2047: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
2048: l_message_data := 'IGS_AD_APPL_PRC_FAILED';
2049: FND_MESSAGE.SET_NAME('IGS', l_message_data);
2050: IGS_GE_MSG_STACK.ADD;
2051: END IF;
2052: x_return_status := 'E';

Line 2085: FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_PRC_FAILED');

2081: logDetail('Exception from Process_OneStop, ' || SQLERRM, 'S');
2082: x_return_status := 'E';
2083: IF p_role <> 'ADMIN' THEN
2084: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
2085: FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_PRC_FAILED');
2086: IGS_GE_MSG_STACK.ADD;
2087: x_message_data := 'IGS_AD_APPL_PRC_FAILED';
2088: ELSE
2089: IF FND_MSG_PUB.Count_Msg < 1 THEN

Line 2087: x_message_data := 'IGS_AD_APPL_PRC_FAILED';

2083: IF p_role <> 'ADMIN' THEN
2084: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
2085: FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_PRC_FAILED');
2086: IGS_GE_MSG_STACK.ADD;
2087: x_message_data := 'IGS_AD_APPL_PRC_FAILED';
2088: ELSE
2089: IF FND_MSG_PUB.Count_Msg < 1 THEN
2090: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2091: Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.Process_OneStop -'||SQLERRM);

Line 2147: CURSOR get_appl_dtl(cp_person_id igs_ad_appl.person_id%TYPE, cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE) IS

2143: Where PERSON_ID = p_person_id
2144: And ADMISSION_APPL_NUMBER = p_admission_appl_number;
2145:
2146: --tray Bug 2405076
2147: CURSOR get_appl_dtl(cp_person_id igs_ad_appl.person_id%TYPE, cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
2148: SELECT acad_cal_type,
2149: acad_ci_sequence_number,
2150: admission_cat,
2151: s_admission_process_type

Line 2152: FROM IGS_AD_APPL

2148: SELECT acad_cal_type,
2149: acad_ci_sequence_number,
2150: admission_cat,
2151: s_admission_process_type
2152: FROM IGS_AD_APPL
2153: WHERE person_id = cp_person_id
2154: AND admission_appl_number = cp_adm_appl_number;
2155: get_appl_dtl_rec get_appl_dtl%ROWTYPE;
2156:

Line 2168: --l_adm_cat IGS_AD_APPL_ALL.ADMISSION_CAT%TYPE; changed to cursor now Bug 2405076

2164:
2165: l_one_stop VARCHAR2(1);
2166: c_course_rec c_course%ROWTYPE;
2167: l_return_status VARCHAR2(1);
2168: --l_adm_cat IGS_AD_APPL_ALL.ADMISSION_CAT%TYPE; changed to cursor now Bug 2405076
2169: --l_adm_proc_type IGS_AD_APPL_ALL. S_ADMISSION_PROCESS_TYPE%TYPE; changed to cursor now Bug 2405076
2170: l_msg_count NUMBER;
2171: l_msg_data VARCHAR2(1000);
2172: l_message_data VARCHAR2(1000);

Line 2169: --l_adm_proc_type IGS_AD_APPL_ALL. S_ADMISSION_PROCESS_TYPE%TYPE; changed to cursor now Bug 2405076

2165: l_one_stop VARCHAR2(1);
2166: c_course_rec c_course%ROWTYPE;
2167: l_return_status VARCHAR2(1);
2168: --l_adm_cat IGS_AD_APPL_ALL.ADMISSION_CAT%TYPE; changed to cursor now Bug 2405076
2169: --l_adm_proc_type IGS_AD_APPL_ALL. S_ADMISSION_PROCESS_TYPE%TYPE; changed to cursor now Bug 2405076
2170: l_msg_count NUMBER;
2171: l_msg_data VARCHAR2(1000);
2172: l_message_data VARCHAR2(1000);
2173: v_warn_level VARCHAR2(10);

Line 2223: From IGS_AD_APPL_ALL

2219: --tray: Select without cursor is not allowed, so changing this to cursor Bug 2405076
2220: /*
2221: Select ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE
2222: Into l_adm_cat, l_adm_proc_type
2223: From IGS_AD_APPL_ALL
2224: Where person_id = p_person_id
2225: And admission_appl_number = p_admission_appl_number;
2226: */
2227: Check_OneStop(p_person_id => p_person_id,

Line 2328: CURSOR get_appl_dtl(cp_person_id igs_ad_appl.person_id%TYPE, cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE) IS

2324: Where PERSON_ID = p_person_id
2325: And ADMISSION_APPL_NUMBER = p_admission_appl_number;
2326:
2327: --tray Bug 2405076
2328: CURSOR get_appl_dtl(cp_person_id igs_ad_appl.person_id%TYPE, cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
2329: SELECT acad_cal_type,
2330: acad_ci_sequence_number,
2331: admission_cat,
2332: s_admission_process_type

Line 2333: FROM IGS_AD_APPL

2329: SELECT acad_cal_type,
2330: acad_ci_sequence_number,
2331: admission_cat,
2332: s_admission_process_type
2333: FROM IGS_AD_APPL
2334: WHERE person_id = cp_person_id
2335: AND admission_appl_number = cp_adm_appl_number;
2336: get_appl_dtl_rec get_appl_dtl%ROWTYPE;
2337:

Line 2408: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';

2404: END IF;
2405:
2406: IF l_return_status = 'E' THEN ----check_offer_resp_update return status
2407: IF p_role <> 'ADMIN' THEN
2408: l_msg_data := 'IGS_AD_APPL_PRC_FAILED';
2409: END IF;
2410: FND_MESSAGE.SET_NAME('IGS', l_msg_data);
2411: IGS_GE_MSG_STACK.ADD;
2412: x_return_status := 'E';

Line 2491: FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_PRC_FAILED');

2487: logDetail('Exception from Process_OneStop2, ' || SQLERRM, 'S');
2488: x_return_status := 'E';
2489: IF p_role <> 'ADMIN' THEN
2490: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
2491: FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_PRC_FAILED');
2492: IGS_GE_MSG_STACK.ADD;
2493: x_msg_data := 'IGS_AD_APPL_PRC_FAILED';
2494: ELSE
2495: IF FND_MSG_PUB.Count_Msg < 1 THEN

Line 2493: x_msg_data := 'IGS_AD_APPL_PRC_FAILED';

2489: IF p_role <> 'ADMIN' THEN
2490: IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
2491: FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_PRC_FAILED');
2492: IGS_GE_MSG_STACK.ADD;
2493: x_msg_data := 'IGS_AD_APPL_PRC_FAILED';
2494: ELSE
2495: IF FND_MSG_PUB.Count_Msg < 1 THEN
2496: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2497: Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.Process_OneStop2 -'||SQLERRM);

Line 2525: CURSOR c_admission_process_type(cp_person_id igs_ad_appl_all.person_id%TYPE,

2521: AND acai.nominated_course_cd = cp_nominated_course_cd
2522: AND acai.sequence_number = cp_sequence_number
2523: FOR UPDATE OF acai.adm_doc_status, acai.adm_entry_qual_status NOWAIT;
2524:
2525: CURSOR c_admission_process_type(cp_person_id igs_ad_appl_all.person_id%TYPE,
2526: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
2527: SELECT s_admission_process_type
2528: FROM igs_ad_appl_all
2529: WHERE person_id = cp_person_id

Line 2526: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS

2522: AND acai.sequence_number = cp_sequence_number
2523: FOR UPDATE OF acai.adm_doc_status, acai.adm_entry_qual_status NOWAIT;
2524:
2525: CURSOR c_admission_process_type(cp_person_id igs_ad_appl_all.person_id%TYPE,
2526: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
2527: SELECT s_admission_process_type
2528: FROM igs_ad_appl_all
2529: WHERE person_id = cp_person_id
2530: AND admission_appl_number = cp_admission_appl_number;

Line 2528: FROM igs_ad_appl_all

2524:
2525: CURSOR c_admission_process_type(cp_person_id igs_ad_appl_all.person_id%TYPE,
2526: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
2527: SELECT s_admission_process_type
2528: FROM igs_ad_appl_all
2529: WHERE person_id = cp_person_id
2530: AND admission_appl_number = cp_admission_appl_number;
2531:
2532: Rec_IGS_AD_PS_APPL_Inst c_upd_acai_eqdo%ROWTYPE;

Line 2539: l_admission_process_type IGS_AD_APPL_ALL.s_admission_process_type%TYPE;

2535: l_application_status IGS_AD_PS_APPL_INST_ALL.adm_doc_status%TYPE;
2536:
2537: l_offer_resp_status IGS_AD_PS_APPL_INST_ALL.adm_offer_resp_status%TYPE;
2538: l_entry_qual_status IGS_AD_PS_APPL_INST_ALL.adm_entry_qual_status%TYPE;
2539: l_admission_process_type IGS_AD_APPL_ALL.s_admission_process_type%TYPE;
2540: lv_mode VARCHAR2(1) DEFAULT 'R';
2541:
2542: l_sc_encoded_text VARCHAR2(4000);
2543: l_sc_msg_count NUMBER;

Line 3509: FROM igs_ad_appl a

3505: l_applid VARCHAR2(1000);
3506:
3507: CURSOR c_applid(cp_person_id number) IS
3508: SELECT application_id
3509: FROM igs_ad_appl a
3510: WHERE a.person_id = cp_person_id;
3511:
3512: BEGIN
3513: l_applid := NULL;

Line 3590: CURSOR c_addr(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3586: -- Basic Information - BASIC_INFO
3587: -- No validation
3588:
3589: -- Address - ADDRESS
3590: CURSOR c_addr(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3591: SELECT 'x'
3592: FROM IGS_PE_ADDR_V addr, IGS_PE_PARTYSITEUSE_V use
3593: WHERE addr.PERSON_ID = cp_person_id
3594: AND addr.STATUS = 'A'

Line 3604: CURSOR c_phone(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3600: AND admission_application_type = cp_ApplType
3601: AND Closed_flag <> 'Y');
3602:
3603: -- Phone - PHONE
3604: CURSOR c_phone(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3605: SELECT 'x'
3606: FROM hz_contact_points
3607: WHERE CONTACT_POINT_TYPE = 'PHONE'
3608: AND OWNER_TABLE_NAME = 'HZ_PARTIES'

Line 3619: CURSOR c_email(cp_person_id igs_ad_appl.person_id%TYPE) IS

3615: AND Closed_flag <> 'Y')
3616: AND OWNER_TABLE_ID = cp_person_id;
3617:
3618: -- Email- EMAIL
3619: CURSOR c_email(cp_person_id igs_ad_appl.person_id%TYPE) IS
3620: SELECT 'x'
3621: FROM hz_contact_points
3622: WHERE CONTACT_POINT_TYPE = 'EMAIL'
3623: AND OWNER_TABLE_NAME = 'HZ_PARTIES'

Line 3628: CURSOR c_altId(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3624: AND STATUS = 'A'
3625: AND OWNER_TABLE_ID = cp_person_id;
3626:
3627: -- Identification- ALT_ID
3628: CURSOR c_altId(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3629: SELECT 'x'
3630: FROM IGS_PE_ALT_PERS_ID
3631: WHERE PE_PERSON_ID = cp_person_id
3632: AND PERSON_ID_TYPE IN

Line 3641: CURSOR c_other_Names(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3637: AND Closed_flag <> 'Y')
3638: AND (END_DT IS NULL OR START_DT <> END_DT);
3639:
3640: -- Other Names- OTHER_NAMES
3641: CURSOR c_other_Names(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3642: SELECT 'x'
3643: FROM IGS_PE_PERSON_ALIAS_V pon
3644: WHERE pon.PERSON_ID = cp_person_id
3645: AND ALIAS_TYPE IN

Line 3655: CURSOR c_race (cp_person_id igs_ad_appl.person_id%TYPE) IS

3651: -- Ask PM for Date Check
3652:
3653: -- Biographic- BIOGRAPHIC
3654: -- check only in IGS_PE_RACE
3655: CURSOR c_race (cp_person_id igs_ad_appl.person_id%TYPE) IS
3656: SELECT 'x'
3657: FROM IGS_PE_RACE race
3658: WHERE race.PERSON_ID = cp_person_id;
3659:

Line 3661: CURSOR c_spl_Needs(cp_person_id igs_ad_appl.person_id%TYPE) IS

3657: FROM IGS_PE_RACE race
3658: WHERE race.PERSON_ID = cp_person_id;
3659:
3660: -- Special Needs- SPECIAL_NEEDS
3661: CURSOR c_spl_Needs(cp_person_id igs_ad_appl.person_id%TYPE) IS
3662: SELECT 'x' FROM IGS_PE_PERS_DISABLTY WHERE person_id = cp_person_id
3663: AND SYSDATE <= NVL(END_DATE,SYSDATE);
3664:
3665: -- Privacy- PRIVACY

Line 3666: CURSOR c_privacy(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3662: SELECT 'x' FROM IGS_PE_PERS_DISABLTY WHERE person_id = cp_person_id
3663: AND SYSDATE <= NVL(END_DATE,SYSDATE);
3664:
3665: -- Privacy- PRIVACY
3666: CURSOR c_privacy(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3667: SELECT 'x'
3668: FROM IGS_PE_PRIV_LEVEL priv
3669: WHERE PERSON_ID = cp_person_id
3670: AND to_char(DATA_GROUP_ID) IN

Line 3678: CURSOR c_relations(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3674: AND admission_application_type = cp_ApplType
3675: AND Closed_flag <> 'Y');
3676:
3677: -- Relationships- RELATIONSHIPS
3678: CURSOR c_relations(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3679: SELECT 'x'
3680: FROM IGS_PE_HZ_REL_V rel
3681: WHERE rel.SUBJECT_ID = cp_person_id
3682: AND rel.SUBJECT_TYPE = 'PERSON'

Line 3694: CURSOR c_felony(cp_person_id igs_ad_appl.person_id%TYPE) IS

3690: AND admission_application_type = cp_ApplType
3691: AND Closed_flag <> 'Y');
3692:
3693: -- Felony- FELONY
3694: CURSOR c_felony(cp_person_id igs_ad_appl.person_id%TYPE) IS
3695: SELECT FELONY_CONVICTED_FLAG
3696: FROM IGS_PE_HZ_PARTIES
3697: WHERE PARTY_ID = cp_person_id;
3698:

Line 3700: CURSOR c_housing(cp_person_id igs_ad_appl.person_id%TYPE) IS

3696: FROM IGS_PE_HZ_PARTIES
3697: WHERE PARTY_ID = cp_person_id;
3698:
3699: -- Housing Status- HOUSING_STATUS
3700: CURSOR c_housing(cp_person_id igs_ad_appl.person_id%TYPE) IS
3701: SELECT 'x'
3702: FROM IGS_PE_TEACH_PERIODS_ALL
3703: WHERE person_id = cp_person_id;
3704:

Line 3707: CURSOR c_health_insu(cp_person_id igs_ad_appl.person_id%TYPE) IS

3703: WHERE person_id = cp_person_id;
3704:
3705: -- Health- HEALTH_INFO
3706: -- Health Insurance
3707: CURSOR c_health_insu(cp_person_id igs_ad_appl.person_id%TYPE) IS
3708: SELECT 'x' FROM IGS_PE_HLTH_INS_ALL
3709: WHERE person_id = cp_person_id
3710: AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
3711:

Line 3713: CURSOR c_health_immu(cp_person_id igs_ad_appl.person_id%TYPE) IS

3709: WHERE person_id = cp_person_id
3710: AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
3711:
3712: -- Immunization
3713: CURSOR c_health_immu(cp_person_id igs_ad_appl.person_id%TYPE) IS
3714: SELECT 'x' FROM IGS_PE_IMMU_DTLS WHERE person_id = cp_person_id
3715: AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
3716:
3717: -- Applicant Citizenship- CITIZENSHIP

Line 3721: CURSOR c_residence(cp_person_id igs_ad_appl.person_id%TYPE) IS

3717: -- Applicant Citizenship- CITIZENSHIP
3718: -- None Dynamic Behaviour
3719:
3720: -- Country of Residence- COUNTRY_RESIDENCE
3721: CURSOR c_residence(cp_person_id igs_ad_appl.person_id%TYPE) IS
3722: SELECT 'x'
3723: FROM IGS_PE_EIT T
3724: WHERE T.INFORMATION_TYPE = 'PE_STAT_RES_COUNTRY'
3725: AND Person_id = cp_person_id;

Line 3728: CURSOR c_lang(cp_person_id igs_ad_appl.person_id%TYPE) IS

3724: WHERE T.INFORMATION_TYPE = 'PE_STAT_RES_COUNTRY'
3725: AND Person_id = cp_person_id;
3726:
3727: -- Language- LANGUAGE
3728: CURSOR c_lang(cp_person_id igs_ad_appl.person_id%TYPE) IS
3729: SELECT 'x'
3730: FROM HZ_PERSON_LANGUAGE lang
3731: WHERE PARTY_ID = cp_person_id
3732: AND STATUS = 'A';

Line 3736: CURSOR c_sor(cp_person_id igs_ad_appl.person_id%TYPE) IS

3732: AND STATUS = 'A';
3733:
3734: -- Domicile- DOMICILE
3735: -- State of Residence
3736: CURSOR c_sor(cp_person_id igs_ad_appl.person_id%TYPE) IS
3737: SELECT 'x'
3738: FROM IGS_PE_EIT
3739: WHERE INFORMATION_TYPE = 'PE_STAT_RES_STATE'
3740: AND Person_id = cp_person_id;

Line 3743: CURSOR c_vinfo(cp_person_id igs_ad_appl.person_id%TYPE) IS

3739: WHERE INFORMATION_TYPE = 'PE_STAT_RES_STATE'
3740: AND Person_id = cp_person_id;
3741:
3742: -- Voter Info
3743: CURSOR c_vinfo(cp_person_id igs_ad_appl.person_id%TYPE) IS
3744: SELECT 'x' FROM IGS_PE_VOTE_INFO_ALL WHERE Person_id = cp_person_id;
3745:
3746: -- Income Tax
3747: CURSOR c_itax(cp_person_id igs_ad_appl.person_id%TYPE) IS

Line 3747: CURSOR c_itax(cp_person_id igs_ad_appl.person_id%TYPE) IS

3743: CURSOR c_vinfo(cp_person_id igs_ad_appl.person_id%TYPE) IS
3744: SELECT 'x' FROM IGS_PE_VOTE_INFO_ALL WHERE Person_id = cp_person_id;
3745:
3746: -- Income Tax
3747: CURSOR c_itax(cp_person_id igs_ad_appl.person_id%TYPE) IS
3748: SELECT 'x' FROM IGS_PE_INCOME_TAX_ALL WHERE Person_id = cp_person_id;
3749:
3750: -- Military Service
3751: CURSOR c_military(cp_person_id igs_ad_appl.person_id%TYPE) IS

Line 3751: CURSOR c_military(cp_person_id igs_ad_appl.person_id%TYPE) IS

3747: CURSOR c_itax(cp_person_id igs_ad_appl.person_id%TYPE) IS
3748: SELECT 'x' FROM IGS_PE_INCOME_TAX_ALL WHERE Person_id = cp_person_id;
3749:
3750: -- Military Service
3751: CURSOR c_military(cp_person_id igs_ad_appl.person_id%TYPE) IS
3752: SELECT 'x' FROM IGS_PE_MIL_SERVICES_ALL WHERE Person_id = cp_person_id;
3753:
3754: -- Country of Citizenship- COUNTRY_CITIZENSHIP
3755: CURSOR c_citizenship(cp_person_id igs_ad_appl.person_id%TYPE) IS

Line 3755: CURSOR c_citizenship(cp_person_id igs_ad_appl.person_id%TYPE) IS

3751: CURSOR c_military(cp_person_id igs_ad_appl.person_id%TYPE) IS
3752: SELECT 'x' FROM IGS_PE_MIL_SERVICES_ALL WHERE Person_id = cp_person_id;
3753:
3754: -- Country of Citizenship- COUNTRY_CITIZENSHIP
3755: CURSOR c_citizenship(cp_person_id igs_ad_appl.person_id%TYPE) IS
3756: SELECT 'x'
3757: FROM HZ_CITIZENSHIP cz
3758: WHERE cz.PARTY_ID = cp_person_id
3759: AND cz.STATUS = 'A';

Line 3762: CURSOR c_visa(cp_person_id igs_ad_appl.person_id%TYPE) IS

3758: WHERE cz.PARTY_ID = cp_person_id
3759: AND cz.STATUS = 'A';
3760:
3761: -- Visa- VISA
3762: CURSOR c_visa(cp_person_id igs_ad_appl.person_id%TYPE) IS
3763: SELECT 'x'
3764: FROM IGS_PE_VISIT_HISTRY_V visa
3765: WHERE visa.PERSON_ID = cp_person_id;
3766:

Line 3768: CURSOR c_passport(cp_person_id igs_ad_appl.person_id%TYPE) IS

3764: FROM IGS_PE_VISIT_HISTRY_V visa
3765: WHERE visa.PERSON_ID = cp_person_id;
3766:
3767: -- Passport- PASSPORT
3768: CURSOR c_passport(cp_person_id igs_ad_appl.person_id%TYPE) IS
3769: SELECT 'x'
3770: FROM IGS_PE_PASSPORT pport
3771: WHERE pport.PERSON_ID = cp_person_id;
3772:

Line 3814: CURSOR c_sec(cp_person_id igs_ad_appl.person_id%TYPE) IS

3810: FROM IGS_SS_APP_PGM_STG
3811: WHERE ss_adm_appl_id = cp_adm_appl_number;
3812:
3813: -- High School- SECONDARY
3814: CURSOR c_sec(cp_person_id igs_ad_appl.person_id%TYPE) IS
3815: SELECT 'x'
3816: FROM HZ_EDUCATION HE
3817: WHERE HE.PARTY_ID = cp_person_id
3818: AND HE.STATUS = 'A'

Line 3831: CURSOR c_post_sec(cp_person_id igs_ad_appl.person_id%TYPE) IS

3827: )
3828: AND SYSTEM_INST_TYPE = 'SECONDARY');
3829:
3830: -- College/University- POST_SECONDARY
3831: CURSOR c_post_sec(cp_person_id igs_ad_appl.person_id%TYPE) IS
3832: SELECT 'x'
3833: FROM HZ_EDUCATION HE
3834: WHERE HE.PARTY_ID = cp_person_id
3835: AND HE.STATUS = 'A'

Line 3846: CURSOR c_local_post_sec(cp_person_id igs_ad_appl.person_id%TYPE) IS

3842: AND SYSTEM_INST_TYPE IN ('POST-SECONDARY', 'OTHER')
3843: );
3844:
3845: -- Local College/University
3846: CURSOR c_local_post_sec(cp_person_id igs_ad_appl.person_id%TYPE) IS
3847: SELECT 'x'
3848: FROM igs_en_stdnt_ps_att_all psattmpt
3849: WHERE psattmpt.person_id = cp_person_id
3850: AND psattmpt.course_attempt_status <> 'UNCONFIRM'

Line 3856: CURSOR c_test_rslts(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3852: OR(fnd_profile.VALUE('CAREER_MODEL_ENABLED') = 'Y' AND psattmpt.primary_program_type = 'PRIMARY'));
3853:
3854:
3855: -- Admission Test Results- ADM_TEST_RESULTS
3856: CURSOR c_test_rslts(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3857: SELECT 'x'
3858: FROM IGS_AD_TEST_RESULTS
3859: WHERE PERSON_ID = cp_person_id
3860: AND ADMISSION_TEST_TYPE IN

Line 3868: CURSOR c_qual(cp_person_id igs_ad_appl.person_id%TYPE) IS

3864: AND admission_application_type = cp_ApplType
3865: AND Closed_flag <> 'Y');
3866:
3867: -- Qualification Details- QUALIFICATION
3868: CURSOR c_qual(cp_person_id igs_ad_appl.person_id%TYPE) IS
3869: SELECT 'x' FROM IGS_UC_QUAL_DETS WHERE person_id = cp_person_id;
3870:
3871: -- Academic Honors- ACAD_HONORS
3872: CURSOR c_acad_hon(cp_person_id igs_ad_appl.person_id%TYPE) IS

Line 3872: CURSOR c_acad_hon(cp_person_id igs_ad_appl.person_id%TYPE) IS

3868: CURSOR c_qual(cp_person_id igs_ad_appl.person_id%TYPE) IS
3869: SELECT 'x' FROM IGS_UC_QUAL_DETS WHERE person_id = cp_person_id;
3870:
3871: -- Academic Honors- ACAD_HONORS
3872: CURSOR c_acad_hon(cp_person_id igs_ad_appl.person_id%TYPE) IS
3873: SELECT 'x' FROM IGS_PE_ACAD_HONORS WHERE person_id = cp_person_id;
3874:
3875: -- Personal Statements- PERSONAL_STMTS
3876: -- Cursors c_perstat and c_perstat_group added by abhiskum

Line 3877: CURSOR c_perstat(cp_person_id igs_ad_appl.person_id%TYPE,

3873: SELECT 'x' FROM IGS_PE_ACAD_HONORS WHERE person_id = cp_person_id;
3874:
3875: -- Personal Statements- PERSONAL_STMTS
3876: -- Cursors c_perstat and c_perstat_group added by abhiskum
3877: CURSOR c_perstat(cp_person_id igs_ad_appl.person_id%TYPE,
3878: cp_adm_appl_number IGS_SS_APP_PGM_STG.ss_adm_appl_id%TYPE,
3879: cp_group_number IGS_AD_APTYP_PESTAT.group_number%TYPE) IS
3880: SELECT stg.persl_stat_type statement,
3881: DECODE(NVL(mandatory,'N'),'N','No','Yes') required,

Line 3893: CURSOR c_perstat_group(cp_person_id igs_ad_appl.person_id%TYPE,

3889: AND stg.ss_Adm_appl_id = cp_adm_appl_number
3890: AND stg.PERSON_ID = cp_person_id
3891: AND group_number = cp_group_number ;
3892:
3893: CURSOR c_perstat_group(cp_person_id igs_ad_appl.person_id%TYPE,
3894: cp_adm_appl_number IGS_SS_APP_PGM_STG.ss_adm_appl_id%TYPE,
3895: cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3896: SELECT
3897: group_name statement,

Line 3895: cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3891: AND group_number = cp_group_number ;
3892:
3893: CURSOR c_perstat_group(cp_person_id igs_ad_appl.person_id%TYPE,
3894: cp_adm_appl_number IGS_SS_APP_PGM_STG.ss_adm_appl_id%TYPE,
3895: cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3896: SELECT
3897: group_name statement,
3898: DECODE(NVL(group_required_flag,'N'),'N','No','Yes') required,
3899: group_number ,

Line 3928: CURSOR c_supp_info(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS

3924: AND stg.ss_Adm_appl_id = cp_adm_appl_number
3925: AND stg.PERSON_ID = cp_person_id ;
3926:
3927: -- Supporting Information- SUPPORTING_INFO
3928: CURSOR c_supp_info(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
3929: SELECT 'x'
3930: FROM IGS_PE_CREDENTIALS
3931: WHERE person_id = cp_person_id
3932: AND to_char(CREDENTIAL_TYPE_ID) IN

Line 3958: CURSOR c_ext_curr(cp_person_id igs_ad_appl.person_id%TYPE) IS

3954: FROM IGS_SS_AD_SPLTAL_STG
3955: WHERE SS_ADM_APPL_ID = cp_Appl_Num;
3956:
3957: -- Extracurricular Activities- EXTRACUR_ACTIVITIES
3958: CURSOR c_ext_curr(cp_person_id igs_ad_appl.person_id%TYPE) IS
3959: SELECT 'x'
3960: FROM HZ_PERSON_INTEREST PI, IGS_AD_HZ_EXTRACURR_ACT HEA
3961: WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID
3962: AND PI.PARTY_ID = cp_person_id;

Line 3965: CURSOR c_emp_hist(cp_person_id igs_ad_appl.person_id%TYPE) IS

3961: WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID
3962: AND PI.PARTY_ID = cp_person_id;
3963:
3964: -- Employment History- EMPLOYMENT_HIST
3965: CURSOR c_emp_hist(cp_person_id igs_ad_appl.person_id%TYPE) IS
3966: SELECT 'x' FROM HZ_EMPLOYMENT_HISTORY WHERE party_id = cp_person_id;
3967:
3968: -- Cursor for selecting components for a given page.
3969: CURSOR c_pgcomps(cp_Appl_Type IGS_AD_SS_APPL_COMPS.Admission_application_type%TYPE, cp_page_Name IGS_AD_SS_APPL_COMPS.page_name%TYPE) IS

Line 5161: cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,

5157: cp_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE,
5158: cp_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE,
5159: cp_unit_set_cd IGS_AD_PS_APPL_INST.unit_set_cd%TYPE,
5160: cp_us_version_number IGS_AD_PS_APPL_INST.us_version_number%TYPE,
5161: cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
5162: cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
5163: SELECT 'x'
5164: FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
5165: WHERE aav.person_id = acaiv.person_id AND

Line 5162: cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS

5158: cp_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE,
5159: cp_unit_set_cd IGS_AD_PS_APPL_INST.unit_set_cd%TYPE,
5160: cp_us_version_number IGS_AD_PS_APPL_INST.us_version_number%TYPE,
5161: cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
5162: cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
5163: SELECT 'x'
5164: FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
5165: WHERE aav.person_id = acaiv.person_id AND
5166: aav.admission_appl_number = acaiv.admission_appl_number AND

Line 5164: FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv

5160: cp_us_version_number IGS_AD_PS_APPL_INST.us_version_number%TYPE,
5161: cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
5162: cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
5163: SELECT 'x'
5164: FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
5165: WHERE aav.person_id = acaiv.person_id AND
5166: aav.admission_appl_number = acaiv.admission_appl_number AND
5167: acaiv.person_id = cp_person_id AND
5168: acaiv.course_cd = cp_course_cd AND

Line 5193: p_message_name := 'IGS_AD_APPL_NOT_FOUND';

5189: FETCH c_appl_Stg INTO appl_Stg_rec;
5190: IF c_appl_Stg%NOTFOUND THEN
5191: CLOSE c_appl_Stg;
5192: p_return_type := 'E';
5193: p_message_name := 'IGS_AD_APPL_NOT_FOUND';
5194:
5195: RETURN;
5196: END IF;
5197:

Line 5338: p_message_name := 'IGS_AD_APPL_NOT_FOUND';

5334: FETCH c_appl_Stg INTO appl_Stg_rec;
5335: IF c_appl_Stg%NOTFOUND THEN
5336: CLOSE c_appl_Stg;
5337: p_return_type := 'E';
5338: p_message_name := 'IGS_AD_APPL_NOT_FOUND';
5339: RETURN;
5340: END IF;
5341:
5342: lSuccess := igs_ad_val_acai.admp_val_acai_us(

Line 5557: entity_name => 'IGS_AD_APPL_PERSTAT',

5553: text => NULL,
5554: file_name => p_file_name,
5555: url => NULL,
5556: function_name => NULL,
5557: entity_name => 'IGS_AD_APPL_PERSTAT',
5558: pk1_value => P_APPL_PERSTAT_ID,
5559: pk2_value => NULL,
5560: pk3_value => NULL,
5561: pk4_value => NULL,