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_UC_EXPORT_DECISION_REPLY dependencies on IGS_UC_APP_CHOICES

Line 4: PROCEDURE export_decision( p_app_no igs_uc_app_choices.app_no%TYPE,

1: PACKAGE BODY igs_uc_export_decision_reply AS
2: /* $Header: IGSUC65B.pls 120.6 2006/06/19 06:04:03 anwest ship $ */
3:
4: PROCEDURE export_decision( p_app_no igs_uc_app_choices.app_no%TYPE,
5: p_choice_number igs_uc_app_choices.choice_no%TYPE ) AS
6: /****************************************************************
7: Created By : ayedubat
8: Date Created By : 17-SEP-2002

Line 5: p_choice_number igs_uc_app_choices.choice_no%TYPE ) AS

1: PACKAGE BODY igs_uc_export_decision_reply AS
2: /* $Header: IGSUC65B.pls 120.6 2006/06/19 06:04:03 anwest ship $ */
3:
4: PROCEDURE export_decision( p_app_no igs_uc_app_choices.app_no%TYPE,
5: p_choice_number igs_uc_app_choices.choice_no%TYPE ) AS
6: /****************************************************************
7: Created By : ayedubat
8: Date Created By : 17-SEP-2002
9: Purpose : This process populates the admissions decision import

Line 16: the package IGS_UC_APP_CHOICES_PKG.

12: Known limitations,enhancements,remarks:
13: Change History
14: Who When What
15: Nishikant 01-OCT-2002 A new column extra_round_nbr added in the TBH calls of
16: the package IGS_UC_APP_CHOICES_PKG.
17: ayedubat 20-OCT-2002 Added a Logic to check whether the decision is required to export or not
18: for the bug fix: 2628041
19: smaddali 19-oct-2002 added igs_uc_old_oustat_pkg.delete_row call whenever
20: export_to_oss_status is being set to "DC" and captured record is found ,for bug 2630219

Line 46: FROM igs_uc_app_choices uac,

42: -- If both App. number and Choice are passed, it fetches only one Application
43: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
44: CURSOR cur_ucas_app_choice IS
45: SELECT uac.*,uac.ROWID
46: FROM igs_uc_app_choices uac,
47: igs_uc_defaults ud
48: WHERE uac.app_no = NVL(p_app_no,uac.app_no)
49: AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
50: AND uac.export_to_oss_status = 'AC'

Line 59: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,

55:
56: -- Cursor to find the OSS Application Instance for the current UACS Application Choice
57: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
58: -- jchin - bug 3691277 and 3691250
59: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
60: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
61: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
62: SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
63: APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,

Line 60: cp_choice_no igs_uc_app_choices.choice_no%TYPE,

56: -- Cursor to find the OSS Application Instance for the current UACS Application Choice
57: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
58: -- jchin - bug 3691277 and 3691250
59: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
60: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
61: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
62: SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
63: APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
64: APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,

Line 61: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS

57: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
58: -- jchin - bug 3691277 and 3691250
59: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
60: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
61: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
62: SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
63: APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
64: APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
65: APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,

Line 68: FROM IGS_UC_APP_CHOICES UAC,

64: APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
65: APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,
66: APLINST.ATTENDANCE_MODE, APLINST.ATTENDANCE_TYPE, APLINST.UNIT_SET_CD,
67: APLINST.US_VERSION_NUMBER, APL.ACAD_CAL_TYPE, UAC.POINT_OF_ENTRY
68: FROM IGS_UC_APP_CHOICES UAC,
69: IGS_UC_APPLICANTS UA,
70: IGS_UC_DEFAULTS UD,
71: IGS_AD_SS_APPL_TYP AAT,
72: IGS_AD_APPL_ALL APL,

Line 113: p_point_of_entry igs_uc_app_choices.point_of_entry%TYPE

109: p_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
110: p_location_cd igs_ad_ps_appl_inst_all.location_cd%TYPE,
111: p_attendance_mode igs_ad_ps_appl_inst_all.attendance_mode%TYPE,
112: p_attendance_type igs_ad_ps_appl_inst_all.attendance_type%TYPE,
113: p_point_of_entry igs_uc_app_choices.point_of_entry%TYPE
114: ) IS
115: SELECT US.UNIT_SET_CD,
116: US.VERSION_NUMBER US_VERSION_NUMBER
117: FROM IGS_PS_OFR_UNIT_SET COUS,

Line 165: CURSOR cur_prev_ou_captured( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,

161: cur_unit_set_cd_rec cur_unit_set_cd%ROWTYPE;
162:
163:
164: -- Cursor to fetch the previous Outcome Status details captured for the current Application Choice
165: CURSOR cur_prev_ou_captured( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,
166: p_choice_no IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) IS
167: SELECT uoc.*, uoc.ROWID
168: FROM igs_uc_old_oustat uoc
169: WHERE uoc.app_no = p_app_no

Line 166: p_choice_no IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) IS

162:
163:
164: -- Cursor to fetch the previous Outcome Status details captured for the current Application Choice
165: CURSOR cur_prev_ou_captured( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,
166: p_choice_no IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) IS
167: SELECT uoc.*, uoc.ROWID
168: FROM igs_uc_old_oustat uoc
169: WHERE uoc.app_no = p_app_no
170: AND uoc.choice_no = p_choice_no;

Line 174: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,

170: AND uoc.choice_no = p_choice_no;
171: cur_prev_ou_captured_rec cur_prev_ou_captured%ROWTYPE;
172:
173: -- Cursor to fetch the latest decision setting Transaction
174: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
175: p_choice_no igs_uc_app_choices.choice_no%TYPE,
176: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
177: SELECT *
178: FROM igs_uc_transactions tran

Line 175: p_choice_no igs_uc_app_choices.choice_no%TYPE,

171: cur_prev_ou_captured_rec cur_prev_ou_captured%ROWTYPE;
172:
173: -- Cursor to fetch the latest decision setting Transaction
174: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
175: p_choice_no igs_uc_app_choices.choice_no%TYPE,
176: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
177: SELECT *
178: FROM igs_uc_transactions tran
179: WHERE tran.app_no = p_app_no

Line 176: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS

172:
173: -- Cursor to fetch the latest decision setting Transaction
174: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
175: p_choice_no igs_uc_app_choices.choice_no%TYPE,
176: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
177: SELECT *
178: FROM igs_uc_transactions tran
179: WHERE tran.app_no = p_app_no
180: AND tran.choice_no = p_choice_no

Line 187: CURSOR cur_unprocess_trans_exist( p_app_no igs_uc_app_choices.app_no%TYPE,

183: ORDER BY tran.uc_tran_id DESC;
184: cur_latest_trans_rec cur_latest_trans%ROWTYPE;
185:
186: -- Cursor to Check whether there exists any un processed transaction
187: CURSOR cur_unprocess_trans_exist( p_app_no igs_uc_app_choices.app_no%TYPE,
188: p_choice_no igs_uc_app_choices.choice_no%TYPE,
189: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
190: SELECT 'X'
191: FROM igs_uc_transactions tran

Line 188: p_choice_no igs_uc_app_choices.choice_no%TYPE,

184: cur_latest_trans_rec cur_latest_trans%ROWTYPE;
185:
186: -- Cursor to Check whether there exists any un processed transaction
187: CURSOR cur_unprocess_trans_exist( p_app_no igs_uc_app_choices.app_no%TYPE,
188: p_choice_no igs_uc_app_choices.choice_no%TYPE,
189: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
190: SELECT 'X'
191: FROM igs_uc_transactions tran
192: WHERE tran.app_no = p_app_no

Line 189: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS

185:
186: -- Cursor to Check whether there exists any un processed transaction
187: CURSOR cur_unprocess_trans_exist( p_app_no igs_uc_app_choices.app_no%TYPE,
188: p_choice_no igs_uc_app_choices.choice_no%TYPE,
189: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
190: SELECT 'X'
191: FROM igs_uc_transactions tran
192: WHERE tran.app_no = p_app_no
193: AND tran.choice_no = p_choice_no

Line 237: FROM igs_uc_app_choices uac,

233: -- errored out in the previous decision import process
234: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
235: CURSOR cur_dp_app_choice IS
236: SELECT uac.*,uac.ROWID
237: FROM igs_uc_app_choices uac,
238: igs_uc_defaults ud
239: WHERE uac.app_no = NVL( p_app_no,uac.app_no)
240: AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
241: AND uac.batch_id IS NOT NULL

Line 295: FROM igs_uc_app_choices a,

291:
292: -- to get all the distinct system_codes belonging to the passed application choice parameter
293: CURSOR c_ch_system IS
294: SELECT DISTINCT a.system_code, a.entry_year, a.entry_month
295: FROM igs_uc_app_choices a,
296: igs_uc_defaults ud
297: WHERE a.app_no = NVL(p_app_no, a.app_no)
298: AND a.choice_no = NVL(p_choice_number,a.choice_no)
299: AND a.export_to_oss_status = 'AC'

Line 338: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;

334: l_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
335: l_deffered_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
336: l_current_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
337:
338: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
339: l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;
340:
341: l_rowid VARCHAR2(25);
342: l_interface_mkdes_id igs_ad_admde_int_all.interface_mkdes_id%TYPE ;

Line 339: l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;

335: l_deffered_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
336: l_current_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
337:
338: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
339: l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;
340:
341: l_rowid VARCHAR2(25);
342: l_interface_mkdes_id igs_ad_admde_int_all.interface_mkdes_id%TYPE ;
343: l_interface_run_id igs_ad_admde_int_all.interface_run_id%TYPE ;

Line 348: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;

344: l_return_status VARCHAR2(10) ;
345: l_error_message fnd_new_messages.message_text%TYPE ;
346: l_description VARCHAR2(2000);
347:
348: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
349: l_reconsideration_flag igs_ad_ps_appl.req_for_reconsideration_ind%TYPE;
350:
351: --Record Type to hold the batch_id created for a system cycle calendars.
352: TYPE batch_det_type IS RECORD

Line 353: ( system_code igs_uc_app_choices.system_code%TYPE,

349: l_reconsideration_flag igs_ad_ps_appl.req_for_reconsideration_ind%TYPE;
350:
351: --Record Type to hold the batch_id created for a system cycle calendars.
352: TYPE batch_det_type IS RECORD
353: ( system_code igs_uc_app_choices.system_code%TYPE,
354: entry_year igs_uc_app_choices.entry_year%TYPE,
355: entry_month igs_uc_app_choices.entry_month%TYPE,
356: batch_id igs_ad_batc_def_det_all.batch_id%TYPE
357: );

Line 354: entry_year igs_uc_app_choices.entry_year%TYPE,

350:
351: --Record Type to hold the batch_id created for a system cycle calendars.
352: TYPE batch_det_type IS RECORD
353: ( system_code igs_uc_app_choices.system_code%TYPE,
354: entry_year igs_uc_app_choices.entry_year%TYPE,
355: entry_month igs_uc_app_choices.entry_month%TYPE,
356: batch_id igs_ad_batc_def_det_all.batch_id%TYPE
357: );
358:

Line 355: entry_month igs_uc_app_choices.entry_month%TYPE,

351: --Record Type to hold the batch_id created for a system cycle calendars.
352: TYPE batch_det_type IS RECORD
353: ( system_code igs_uc_app_choices.system_code%TYPE,
354: entry_year igs_uc_app_choices.entry_year%TYPE,
355: entry_month igs_uc_app_choices.entry_month%TYPE,
356: batch_id igs_ad_batc_def_det_all.batch_id%TYPE
357: );
358:
359: --Table Type to hold the batch_id created for diferrent system cycle calendars.

Line 367: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,

363: l_batch_id_det batch_det_table_type;
364: l_batch_id_loc NUMBER;
365:
366:
367: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
368: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
369: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
370: p_batch_id_loc OUT NOCOPY NUMBER) IS
371: /******************************************************************

Line 368: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,

364: l_batch_id_loc NUMBER;
365:
366:
367: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
368: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
369: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
370: p_batch_id_loc OUT NOCOPY NUMBER) IS
371: /******************************************************************
372: Created By : rbezawad

Line 369: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,

365:
366:
367: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
368: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
369: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
370: p_batch_id_loc OUT NOCOPY NUMBER) IS
371: /******************************************************************
372: Created By : rbezawad
373: Date Created By : 16-Jun-03

Line 691: The offer_dt should be set to the decision date from the igs_uc_app_choices for the choice being

687: IGS_UC_OLD_OUSTAT table and Application Instance information from CUR_OSS_APPL_INST_REC
688: Else,Populate the Decision_maker, decision_reason,Decision_date details from UCAS Setup
689: and Application Instance information CUR_OSS_APPL_INST_REC.
690:
691: The offer_dt should be set to the decision date from the igs_uc_app_choices for the choice being
692: imported unless the outcome status is being synchronized with an unprocessed UCAS transaction
693: in which case it should be set to the transaction date created. */
694:
695: /* Get the Interface Run ID value from Sequence */

Line 1203: igs_uc_app_choices_pkg.update_row

1199: END IF ;
1200:
1201: /* Update the Application choice record with the error code, batch_id and status */
1202:
1203: igs_uc_app_choices_pkg.update_row
1204: ( x_rowid => cur_ucas_app_choice_rec.ROWID
1205: ,x_app_choice_id => cur_ucas_app_choice_rec.app_choice_id
1206: ,x_app_id => cur_ucas_app_choice_rec.app_id
1207: ,x_app_no => cur_ucas_app_choice_rec.app_no

Line 1330: igs_uc_app_choices_pkg.update_row (

1326: FETCH cur_dec_import_error INTO cur_dec_import_error_rec;
1327:
1328: IF cur_dec_import_error%NOTFOUND THEN
1329:
1330: igs_uc_app_choices_pkg.update_row (
1331: x_rowid => cur_dp_app_choice_rec.ROWID
1332: ,x_app_choice_id => cur_dp_app_choice_rec.app_choice_id
1333: ,x_app_id => cur_dp_app_choice_rec.app_id
1334: ,x_app_no => cur_dp_app_choice_rec.app_no

Line 1423: PROCEDURE export_reply( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,

1419: END export_decision;
1420:
1421:
1422:
1423: PROCEDURE export_reply( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,
1424: p_choice_number IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) AS
1425: /******************************************************************
1426: Created By : ayedubat
1427: Date Created By : 16-SEP-2002

Line 1424: p_choice_number IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) AS

1420:
1421:
1422:
1423: PROCEDURE export_reply( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,
1424: p_choice_number IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) AS
1425: /******************************************************************
1426: Created By : ayedubat
1427: Date Created By : 16-SEP-2002
1428: Purpose : This process exports the UCAS reply to OSS by populating the admissions

Line 1434: the package IGS_UC_APP_CHOICES_PKG.

1430: Known limitations,enhancements,remarks:
1431: Change History
1432: Who When What
1433: Nishikant 01-OCT-2002 A new column extra_round_nbr added in the TBH calls of
1434: the package IGS_UC_APP_CHOICES_PKG.
1435: Ayedubat 18-OCT-2002 Passed NULL to the column,ACTUAL_OFFER_RESPONSE_DT while populating into the interface table,IGS_AD_OFFRESP_INT for the bug fix:2632302
1436: jchin 20-jan-2006 Modified for R12 Perf improvements - bug 3691277 and 3691250
1437: jchakrab 22-May-2006 Modified for 5165624
1438: jbaber 07-Jun-2006 Added decline_ofr_reason for bug 528190/5222716

Line 1446: l_export_to_oss igs_uc_app_choices.export_to_oss_status%TYPE ;

1442: l_batch_id igs_ad_offresp_batch.batch_id%TYPE ;
1443: l_conc_request_id NUMBER(15) ;
1444: l_errbuf VARCHAR2(2000) ;
1445: l_retcode NUMBER(15) ;
1446: l_export_to_oss igs_uc_app_choices.export_to_oss_status%TYPE ;
1447: l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;
1448: l_last_update_login NUMBER(15) ;
1449: l_last_updated_by NUMBER(15) ;
1450: l_description VARCHAR2(2000);

Line 1447: l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;

1443: l_conc_request_id NUMBER(15) ;
1444: l_errbuf VARCHAR2(2000) ;
1445: l_retcode NUMBER(15) ;
1446: l_export_to_oss igs_uc_app_choices.export_to_oss_status%TYPE ;
1447: l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;
1448: l_last_update_login NUMBER(15) ;
1449: l_last_updated_by NUMBER(15) ;
1450: l_description VARCHAR2(2000);
1451: l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;

Line 1455: l_ch_error igs_uc_app_choices.error_code%TYPE ;

1451: l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
1452: l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
1453: l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;
1454: l_adm_seq_no igs_uc_sys_calndrs.adm_cal_seq_no%TYPE ;
1455: l_ch_error igs_uc_app_choices.error_code%TYPE ;
1456: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
1457: l_exp_reply_flag BOOLEAN ;
1458:
1459: -- Cursor to find the details of default UCAS setup defined in the SYSTEM.

Line 1456: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;

1452: l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
1453: l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;
1454: l_adm_seq_no igs_uc_sys_calndrs.adm_cal_seq_no%TYPE ;
1455: l_ch_error igs_uc_app_choices.error_code%TYPE ;
1456: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
1457: l_exp_reply_flag BOOLEAN ;
1458:
1459: -- Cursor to find the details of default UCAS setup defined in the SYSTEM.
1460: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build

Line 1474: FROM IGS_UC_APP_CHOICES uac,

1470: -- If both App. number and Choice are passed, it fetches only one Application
1471: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1472: CURSOR cur_ucas_app_choice IS
1473: SELECT uac.*,uac.ROWID
1474: FROM IGS_UC_APP_CHOICES uac,
1475: igs_uc_defaults ud
1476: WHERE uac.app_no = NVL(p_app_no,uac.app_no)
1477: AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
1478: AND uac.export_to_oss_status = 'DC'

Line 1488: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,

1484: -- Cursor to find the OSS Application Instance for the current UACS Application Choice
1485: -- smaddali modified this cursor to add the where clause of System_code check ,
1486: -- and modifying the where clause comparing calendars , bug 2643048 UCFD102 build
1487: -- jchin - bug 3691277 and 3691250
1488: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
1489: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1490: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1491: SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
1492: APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,

Line 1489: cp_choice_no igs_uc_app_choices.choice_no%TYPE,

1485: -- smaddali modified this cursor to add the where clause of System_code check ,
1486: -- and modifying the where clause comparing calendars , bug 2643048 UCFD102 build
1487: -- jchin - bug 3691277 and 3691250
1488: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
1489: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1490: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1491: SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
1492: APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
1493: APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,

Line 1490: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS

1486: -- and modifying the where clause comparing calendars , bug 2643048 UCFD102 build
1487: -- jchin - bug 3691277 and 3691250
1488: CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
1489: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1490: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1491: SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
1492: APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
1493: APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
1494: APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,

Line 1497: FROM IGS_UC_APP_CHOICES UAC,

1493: APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
1494: APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,
1495: APLINST.ATTENDANCE_MODE, APLINST.ATTENDANCE_TYPE, APLINST.UNIT_SET_CD,
1496: APLINST.US_VERSION_NUMBER, APL.ACAD_CAL_TYPE, UAC.POINT_OF_ENTRY
1497: FROM IGS_UC_APP_CHOICES UAC,
1498: IGS_UC_APPLICANTS UA,
1499: IGS_UC_DEFAULTS UD,
1500: IGS_AD_SS_APPL_TYP AAT,
1501: IGS_AD_APPL_ALL APL,

Line 1542: p_point_of_entry igs_uc_app_choices.point_of_entry%TYPE

1538: p_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
1539: p_location_cd igs_ad_ps_appl_inst_all.location_cd%TYPE,
1540: p_attendance_mode igs_ad_ps_appl_inst_all.attendance_mode%TYPE,
1541: p_attendance_type igs_ad_ps_appl_inst_all.attendance_type%TYPE,
1542: p_point_of_entry igs_uc_app_choices.point_of_entry%TYPE
1543: ) IS
1544: SELECT US.UNIT_SET_CD,
1545: US.VERSION_NUMBER US_VERSION_NUMBER
1546: FROM IGS_PS_OFR_UNIT_SET COUS,

Line 1595: CURSOR cur_map_offr_resp( p_decision igs_uc_app_choices.decision%TYPE,

1591:
1592:
1593: -- Cursor to get the OSS user Offer Response mapped to the UCAS system/decision and reply
1594: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1595: CURSOR cur_map_offr_resp( p_decision igs_uc_app_choices.decision%TYPE,
1596: p_reply igs_uc_app_choices.reply%TYPE,
1597: p_system_code igs_uc_app_choices.system_code%TYPE) IS
1598: SELECT adm_offer_resp_status
1599: FROM igs_uc_map_off_resp

Line 1596: p_reply igs_uc_app_choices.reply%TYPE,

1592:
1593: -- Cursor to get the OSS user Offer Response mapped to the UCAS system/decision and reply
1594: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1595: CURSOR cur_map_offr_resp( p_decision igs_uc_app_choices.decision%TYPE,
1596: p_reply igs_uc_app_choices.reply%TYPE,
1597: p_system_code igs_uc_app_choices.system_code%TYPE) IS
1598: SELECT adm_offer_resp_status
1599: FROM igs_uc_map_off_resp
1600: WHERE system_code = p_system_code

Line 1597: p_system_code igs_uc_app_choices.system_code%TYPE) IS

1593: -- Cursor to get the OSS user Offer Response mapped to the UCAS system/decision and reply
1594: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1595: CURSOR cur_map_offr_resp( p_decision igs_uc_app_choices.decision%TYPE,
1596: p_reply igs_uc_app_choices.reply%TYPE,
1597: p_system_code igs_uc_app_choices.system_code%TYPE) IS
1598: SELECT adm_offer_resp_status
1599: FROM igs_uc_map_off_resp
1600: WHERE system_code = p_system_code
1601: AND decision_code = p_decision

Line 1618: FROM igs_uc_app_choices uac,

1614: -- Cursor to Fetch the Application Choices of export to OSS status 'RP'
1615: -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1616: CURSOR cur_rp_app_choice IS
1617: SELECT uac.*,uac.ROWID
1618: FROM igs_uc_app_choices uac,
1619: igs_uc_defaults ud
1620: WHERE uac.app_no = NVL(p_app_no,uac.app_no)
1621: AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
1622: AND uac.export_to_oss_status = 'RP'

Line 1951: igs_uc_app_choices_pkg.update_row

1947: END IF ; /* End of OSS Application Instance processinng */
1948: CLOSE cur_oss_appl_inst;
1949:
1950: /* update the Application choice with the error code ,batch_id and export_to_oss_status*/
1951: igs_uc_app_choices_pkg.update_row
1952: ( x_rowid => cur_ucas_app_choice_rec.ROWID
1953: ,x_app_choice_id => cur_ucas_app_choice_rec.app_choice_id
1954: ,x_app_id => cur_ucas_app_choice_rec.app_id
1955: ,x_app_no => cur_ucas_app_choice_rec.app_no

Line 2118: igs_uc_app_choices_pkg.update_row

2114: END IF ; /* End If for cur_oss_appl_inst%FOUND */
2115: CLOSE cur_oss_appl_inst;
2116:
2117: -- Update the Application Choice with export to OSS status , Concurrent Request ID
2118: igs_uc_app_choices_pkg.update_row
2119: ( x_rowid => cur_rp_app_choice_rec.ROWID
2120: ,x_app_choice_id => cur_rp_app_choice_rec.app_choice_id
2121: ,x_app_id => cur_rp_app_choice_rec.app_id
2122: ,x_app_no => cur_rp_app_choice_rec.app_no