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_IMP_004 dependencies on IGS_AD_PS_APPL

Line 45: l_cndnl_ofr_must_be_stsfd_ind IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind%TYPE ;

41: -- Initialize Application Status , Fee status
42: l_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE ;
43: l_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE ;
44: l_Late_Adm_Fee_Status IGS_AD_FEE_STAT.adm_fee_status%TYPE ;
45: l_cndnl_ofr_must_be_stsfd_ind IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind%TYPE ;
46: l_adm_pending_outcome_status VARCHAR2(127) ;
47: l_adm_cndtnl_offer_status VARCHAR2(127) ;
48: l_adm_offer_resp_status VARCHAR2(127) ;
49: l_adm_offer_dfrmnt_status VARCHAR2(127) ;

Line 53: l_adm_doc_status igs_ad_ps_appl_inst.adm_doc_status%TYPE;

49: l_adm_offer_dfrmnt_status VARCHAR2(127) ;
50: l_admission_application_type IGS_AD_APPL.application_type%TYPE ;
51: l_admission_cat IGS_AD_APPL.admission_cat%TYPE ;
52: l_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE ;
53: l_adm_doc_status igs_ad_ps_appl_inst.adm_doc_status%TYPE;
54:
55: l_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE ;
56: l_enrolment_cat IGS_EN_ENROLMENT_CAT.enrolment_cat%TYPE ;
57: l_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE ;

Line 236: l_error_text igs_ad_ps_appl_inst_int.error_text%TYPE;

232: p_error_text VARCHAR2, p_sequence_number NUMBER,p_error_code VARCHAR2,
233: p_admission_appl_number NUMBER ,
234: p_match_ind VARCHAR2 ) AS
235: PRAGMA AUTONOMOUS_TRANSACTION;
236: l_error_text igs_ad_ps_appl_inst_int.error_text%TYPE;
237: BEGIN
238: UPDATE igs_ad_ps_appl_inst_int
239: SET error_text = p_error_text,
240: status = p_status,

Line 238: UPDATE igs_ad_ps_appl_inst_int

234: p_match_ind VARCHAR2 ) AS
235: PRAGMA AUTONOMOUS_TRANSACTION;
236: l_error_text igs_ad_ps_appl_inst_int.error_text%TYPE;
237: BEGIN
238: UPDATE igs_ad_ps_appl_inst_int
239: SET error_text = p_error_text,
240: status = p_status,
241: sequence_number = p_sequence_number,
242: error_code = p_error_code,

Line 291: igs_ad_ps_appl_inst_int aplinst

287: cp_interface_appl_id igs_ad_apl_int.interface_appl_id%TYPE ) IS
288: SELECT
289: cst_insert dmlmode, aplinst.rowid, aplinst.*
290: FROM
291: igs_ad_ps_appl_inst_int aplinst
292: WHERE aplinst.status = '2'
293: AND aplinst.interface_run_id = p_interface_run_id
294: AND ( p_rule = 'R' AND aplinst.match_ind IN ('16', '25')
295: OR update_adm_seq_number IS NULL )

Line 301: igs_ad_ps_appl_inst_int aplinst

297: UNION ALL
298: SELECT
299: cst_update dmlmode, aplinst.rowid, aplinst.*
300: FROM
301: igs_ad_ps_appl_inst_int aplinst
302: WHERE aplinst.status = '2'
303: AND aplinst.interface_run_id = p_interface_run_id
304: AND aplinst.interface_appl_id = NVL(cp_interface_appl_id , aplinst.interface_appl_id)
305: AND ( p_rule = 'I' OR (p_rule = 'R' AND aplinst.match_ind = '21'))

Line 324: igs_ad_ps_appl_inst acai

320: ) IS
321: SELECT
322: acai.rowid,acai.*
323: FROM
324: igs_ad_ps_appl_inst acai
325: WHERE
326: person_id = p_person_id
327: AND admission_appl_number = p_admission_appl_number
328: AND nominated_course_cd = applinst_rec.nominated_course_cd

Line 805: CURSOR c_prg_exists(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,

801: -- (reverse chronological order - newest change first)
802: --------------------------------------------------------------------------
803: l_error_text1 VARCHAR2(2000);
804: l_error_code1 NUMBER;
805: CURSOR c_prg_exists(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
806: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,
807: cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)
808: IS
809: SELECT tab.* --multiorg table , so rowid need not be selected explicitly

Line 806: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,

802: --------------------------------------------------------------------------
803: l_error_text1 VARCHAR2(2000);
804: l_error_code1 NUMBER;
805: CURSOR c_prg_exists(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
806: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,
807: cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)
808: IS
809: SELECT tab.* --multiorg table , so rowid need not be selected explicitly
810: FROM IGS_AD_PS_APPL tab

Line 807: cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)

803: l_error_text1 VARCHAR2(2000);
804: l_error_code1 NUMBER;
805: CURSOR c_prg_exists(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
806: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,
807: cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)
808: IS
809: SELECT tab.* --multiorg table , so rowid need not be selected explicitly
810: FROM IGS_AD_PS_APPL tab
811: WHERE person_id = cp_person_id AND

Line 810: FROM IGS_AD_PS_APPL tab

806: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,
807: cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)
808: IS
809: SELECT tab.* --multiorg table , so rowid need not be selected explicitly
810: FROM IGS_AD_PS_APPL tab
811: WHERE person_id = cp_person_id AND
812: admission_appl_number = cp_appl_no AND
813: nominated_course_cd = cp_nominated_course_cd;
814: c_prg_exists_rec c_prg_exists%ROWTYPE;

Line 816: CURSOR c_session_info(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,

812: admission_appl_number = cp_appl_no AND
813: nominated_course_cd = cp_nominated_course_cd;
814: c_prg_exists_rec c_prg_exists%ROWTYPE;
815:
816: CURSOR c_session_info(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
817: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE) IS
818: SELECT adm_cal_type, adm_ci_sequence_number, acad_cal_type , s_admission_process_type
819: FROM igs_Ad_appl_all
820: WHERE person_id = cp_person_id

Line 817: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE) IS

813: nominated_course_cd = cp_nominated_course_cd;
814: c_prg_exists_rec c_prg_exists%ROWTYPE;
815:
816: CURSOR c_session_info(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
817: cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE) IS
818: SELECT adm_cal_type, adm_ci_sequence_number, acad_cal_type , s_admission_process_type
819: FROM igs_Ad_appl_all
820: WHERE person_id = cp_person_id
821: AND admission_Appl_number = cp_appl_no;

Line 825: FROM IGS_AD_PS_APPL_INST

821: AND admission_Appl_number = cp_appl_no;
822:
823: CURSOR c_nxt_acai_seq_num IS
824: SELECT NVL(MAX(sequence_number), 0) + 1
825: FROM IGS_AD_PS_APPL_INST
826: WHERE
827: person_id = p_person_id AND
828: admission_appl_number = p_admission_appl_number AND
829: nominated_course_cd = p_applinst_rec.nominated_course_cd;

Line 837: l_app_source_id igs_ad_ps_appl_inst_all.app_source_id%TYPE;

833: lv_rowid VARCHAR2(20);
834: v_description VARCHAR2(2000);
835: l_error_code VARCHAR2(4);
836: l_error_text VARCHAR2(2000);
837: l_app_source_id igs_ad_ps_appl_inst_all.app_source_id%TYPE;
838: l_msg_at_index NUMBER ;
839: l_return_status VARCHAR2(1);
840: l_msg_count NUMBER ;
841: l_msg_data VARCHAR2(2000);

Line 845: l_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;

841: l_msg_data VARCHAR2(2000);
842: l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
843: l_completion_dt DATE;
844: l_course_start_dt DATE;
845: l_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;
846: l_expected_completion_perd IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;
847:
848: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
849: l_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;

Line 846: l_expected_completion_perd IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;

842: l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
843: l_completion_dt DATE;
844: l_course_start_dt DATE;
845: l_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;
846: l_expected_completion_perd IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;
847:
848: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
849: l_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;
850: PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE

Line 848: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;

844: l_course_start_dt DATE;
845: l_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;
846: l_expected_completion_perd IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;
847:
848: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
849: l_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;
850: PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE
851: ,p_nominated_course_cd IN igs_pe_typ_instances_all.nominated_course_cd%TYPE
852: ,p_person_id IN igs_pe_typ_instances_all.person_id%TYPE

Line 849: l_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;

845: l_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;
846: l_expected_completion_perd IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;
847:
848: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
849: l_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;
850: PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE
851: ,p_nominated_course_cd IN igs_pe_typ_instances_all.nominated_course_cd%TYPE
852: ,p_person_id IN igs_pe_typ_instances_all.person_id%TYPE
853: ,p_adm_appl_number IN igs_pe_typ_instances_all.admission_appl_number%TYPE ) AS

Line 909: IGS_AD_PS_APPL_PKG.Insert_Row (

905: --create/update program
906: l_msg_at_index := igs_ge_msg_stack.count_msg;
907: SAVEPOINT before_insert_ps_appl;
908: IF c_prg_exists%NOTFOUND THEN
909: IGS_AD_PS_APPL_PKG.Insert_Row (
910: X_Mode => 'R',
911: X_RowId => lv_rowid,
912: X_Person_Id => p_person_id,
913: X_Admission_Appl_Number => p_admission_Appl_number,

Line 926: IGS_AD_PS_APPL_PKG.update_Row (

922: X_Org_Id => l_org_id
923: );
924:
925: ELSE
926: IGS_AD_PS_APPL_PKG.update_Row (
927: X_RowId => c_prg_exists_rec.row_id,
928: X_Person_Id => NVL(p_person_id,c_prg_exists_rec.person_id),
929: X_Admission_Appl_Number => NVL(p_admission_Appl_number,c_prg_exists_rec.admission_appl_number),
930: X_Nominated_Course_Cd => NVL(p_applinst_rec.nominated_course_cd,c_prg_exists_rec.nominated_course_cd),

Line 1045: IGS_AD_PS_APPL_INST_PKG.Insert_Row (

1041: OPEN c_nxt_acai_seq_num;
1042: FETCH c_nxt_acai_seq_num INTO l_acai_sequence_number;
1043: CLOSE c_nxt_acai_seq_num;
1044: lv_rowid := NULL;
1045: IGS_AD_PS_APPL_INST_PKG.Insert_Row (
1046: X_ROWID => lv_rowid,
1047: X_PERSON_ID => p_Person_Id,
1048: X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
1049: X_NOMINATED_COURSE_CD => p_applinst_rec.nominated_course_cd,

Line 1278: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;

1274: l_msg_data VARCHAR2(2000);
1275: l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1276: l_error_code VARCHAR2(4);
1277: l_error_text VARCHAR2(2000);
1278: l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
1279:
1280: BEGIN
1281: --For all application instance interface record
1282: -- IF update_seq_num is NOT NULL

Line 1286: UPDATE igs_ad_ps_appl_inst_int aplinst

1282: -- IF update_seq_num is NOT NULL
1283: -- update the instance
1284: -- ELSE create_application_instance
1285: --If given invalid update transcript ID then error out.
1286: UPDATE igs_ad_ps_appl_inst_int aplinst
1287: SET
1288: status = '3'
1289: , error_code = 'E706'
1290: ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E706', 8405)

Line 1294: AND NOT EXISTS ( SELECT 1 FROM igs_ad_ps_appl_inst

1290: ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E706', 8405)
1291: WHERE interface_run_id = p_interface_run_id
1292: AND status ='2'
1293: AND update_adm_seq_number IS NOT NULL
1294: AND NOT EXISTS ( SELECT 1 FROM igs_ad_ps_appl_inst
1295: WHERE person_id = aplinst.person_id
1296: AND admission_Appl_number = aplinst.admission_Appl_number
1297: AND nominated_course_cd = aplinst.nominated_course_cd
1298: AND sequence_number = aplinst.update_adm_seq_number

Line 1303: UPDATE igs_ad_ps_appl_inst_int

1299: ) ;
1300: COMMIT;
1301: -- 1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
1302: IF p_rule IN ('E', 'I') THEN
1303: UPDATE igs_ad_ps_appl_inst_int
1304: SET
1305: status = '3'
1306: , error_code = 'E700'
1307: ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)

Line 1316: UPDATE igs_ad_ps_appl_inst_int

1312: COMMIT;
1313:
1314: -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
1315: IF p_rule = 'R' THEN
1316: UPDATE igs_ad_ps_appl_inst_int
1317: SET
1318: status = '1', error_code = NULL
1319: WHERE interface_run_id = p_interface_run_id
1320: AND status = '2'

Line 1325: UPDATE igs_ad_ps_appl_inst_int

1321: AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
1322: END IF;
1323:
1324: IF p_rule = 'E' THEN
1325: UPDATE igs_ad_ps_appl_inst_int
1326: SET
1327: status = '1'
1328: , match_ind = '19'
1329: , SEQUENCE_NUMBER = update_adm_seq_number

Line 1365: igs_ad_ps_appl_inst_pkg.update_row(

1361: END IF;
1362:
1363: l_msg_at_index := igs_ge_msg_stack.count_msg;
1364: SAVEPOINT before_update_appl_inst;
1365: igs_ad_ps_appl_inst_pkg.update_row(
1366: x_rowid => dup_applinst_rec.rowid,
1367: x_person_id =>dup_applinst_rec.person_id,
1368: x_admission_appl_number => dup_applinst_rec.admission_appl_number,
1369: x_nominated_course_cd=> dup_applinst_rec.nominated_course_cd,

Line 1480: UPDATE igs_ad_ps_appl_inst_int

1476: x_appl_inst_status =>dup_applinst_rec.appl_inst_status,
1477: x_ais_reason =>dup_applinst_rec.ais_reason
1478: );
1479:
1480: UPDATE igs_ad_ps_appl_inst_int
1481: SET
1482: status = '1',
1483: sequence_number = dup_applinst_rec.sequence_number
1484: WHERE interface_ps_appl_inst_id = applinst_rec.interface_ps_appl_inst_id;

Line 1522: UPDATE igs_ad_ps_appl_inst_int

1518: l_debug_str, NULL,
1519: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1520: END IF;
1521: END IF;
1522: UPDATE igs_ad_ps_appl_inst_int
1523: SET
1524: status = '3',
1525: error_code = l_error_code,
1526: error_text = l_error_text

Line 1534: UPDATE igs_ad_ps_appl_inst_int applinst

1530: END IF;
1531: END LOOP;
1532:
1533: IF p_rule = 'R' THEN
1534: UPDATE igs_ad_ps_appl_inst_int applinst
1535: SET
1536: status = '1'
1537: , match_ind = '23'
1538: , sequence_number = applinst.update_adm_seq_number

Line 1540: FROM igs_ad_ps_appl_inst ai

1536: status = '1'
1537: , match_ind = '23'
1538: , sequence_number = applinst.update_adm_seq_number
1539: WHERE EXISTS ( SELECT 'X'
1540: FROM igs_ad_ps_appl_inst ai
1541: WHERE ai.person_id = applinst.person_id AND
1542: ai.nominated_course_cd = applinst.nominated_course_cd AND
1543: ai.admission_appl_number = applinst.admission_Appl_number AND
1544: ai.sequence_number = applinst.update_adm_seq_number AND

Line 1594: UPDATE igs_ad_ps_appl_inst_int

1590: END IF;
1591:
1592:
1593: IF p_rule = 'R' THEN
1594: UPDATE igs_ad_ps_appl_inst_int
1595: SET
1596: status = '3'
1597: , match_ind = '20'
1598: WHERE interface_run_id = p_interface_run_id

Line 1604: UPDATE igs_ad_ps_appl_inst_int

1600: AND update_adm_seq_number IS NOT NULL;
1601: END IF;
1602:
1603: IF p_rule = 'R' THEN
1604: UPDATE igs_ad_ps_appl_inst_int
1605: SET
1606: status = '3'
1607: , error_code = 'E700'
1608: , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)

Line 1844: UPDATE igs_ad_ps_appl_inst_int a

1840: );
1841: END IF;
1842: COMMIT;
1843:
1844: UPDATE igs_ad_ps_appl_inst_int a
1845: SET (person_id, admission_appl_number,interface_run_id ) =
1846: ( SELECT person_id, admission_appl_number ,interface_run_id
1847: FROM igs_ad_apl_int
1848: WHERE interface_appl_id = a.interface_appl_id