APPS.IGS_UC_PROC_APPLICATION_DATA dependencies on IGS_UC_ISTRPQR_INTS

Line 7353: CURSOR new_ivstarpqr_cur (cp_appno igs_uc_istrpqr_ints.appno%TYPE) IS

7349: 3. For *P marvin records making eblresult = grade1 + grade2
7350: ******************************************************************/
7351:
7352: -- get the records from interface tables where status is NEW.
7353: CURSOR new_ivstarpqr_cur (cp_appno igs_uc_istrpqr_ints.appno%TYPE) IS
7354: SELECT ivpqr.rowid,
7355: ivpqr.*
7356: FROM igs_uc_istrpqr_ints ivpqr
7357: WHERE ivpqr.record_status = 'N'

Line 7356: FROM igs_uc_istrpqr_ints ivpqr

7352: -- get the records from interface tables where status is NEW.
7353: CURSOR new_ivstarpqr_cur (cp_appno igs_uc_istrpqr_ints.appno%TYPE) IS
7354: SELECT ivpqr.rowid,
7355: ivpqr.*
7356: FROM igs_uc_istrpqr_ints ivpqr
7357: WHERE ivpqr.record_status = 'N'
7358: AND ivpqr.appno = cp_appno ;
7359:
7360:

Line 7382: FROM igs_uc_istrpqr_ints

7378:
7379: -- get the unique list of appno that would be processed
7380: CURSOR new_appl_cur IS
7381: SELECT DISTINCT appno
7382: FROM igs_uc_istrpqr_ints
7383: WHERE record_status = 'N';
7384:
7385: -- get all the records for an applicant
7386: CURSOR get_appno_cur(cp_appno igs_uc_app_results.app_no%TYPE) IS

Line 7441: l_gen_ebl_format igs_uc_istrpqr_ints.eblsubject%TYPE;

7437: appl_det_rec get_appl_dets%ROWTYPE;
7438: old_starpqr_rec old_starpqr_cur%ROWTYPE;
7439: subject_rec validate_subject%ROWTYPE;
7440: l_proc_reqd VARCHAR2(1);
7441: l_gen_ebl_format igs_uc_istrpqr_ints.eblsubject%TYPE;
7442: l_conv_ebl_code igs_uc_istrpqr_ints.eblsubject%TYPE;
7443: l_subjectid igs_uc_istrpqr_ints.subjectid%TYPE;
7444: l_appno_failed BOOLEAN ;
7445: BEGIN

Line 7442: l_conv_ebl_code igs_uc_istrpqr_ints.eblsubject%TYPE;

7438: old_starpqr_rec old_starpqr_cur%ROWTYPE;
7439: subject_rec validate_subject%ROWTYPE;
7440: l_proc_reqd VARCHAR2(1);
7441: l_gen_ebl_format igs_uc_istrpqr_ints.eblsubject%TYPE;
7442: l_conv_ebl_code igs_uc_istrpqr_ints.eblsubject%TYPE;
7443: l_subjectid igs_uc_istrpqr_ints.subjectid%TYPE;
7444: l_appno_failed BOOLEAN ;
7445: BEGIN
7446:

Line 7443: l_subjectid igs_uc_istrpqr_ints.subjectid%TYPE;

7439: subject_rec validate_subject%ROWTYPE;
7440: l_proc_reqd VARCHAR2(1);
7441: l_gen_ebl_format igs_uc_istrpqr_ints.eblsubject%TYPE;
7442: l_conv_ebl_code igs_uc_istrpqr_ints.eblsubject%TYPE;
7443: l_subjectid igs_uc_istrpqr_ints.subjectid%TYPE;
7444: l_appno_failed BOOLEAN ;
7445: BEGIN
7446:
7447: -- initialize variables

Line 7465: UPDATE igs_uc_istrpqr_ints

7461: -- initialise flag that this applicant has not failed any validation
7462: l_appno_failed := FALSE;
7463:
7464: -- initialise error_code = NULL for all interface records of this applicant in status NEW
7465: UPDATE igs_uc_istrpqr_ints
7466: SET error_code = NULL
7467: WHERE record_status = 'N' AND appno = new_appl_rec.appno ;
7468:
7469: -- Delete all the qualification records for the applicant before inserting.

Line 7837: UPDATE igs_uc_istrpqr_ints

7833: -- set flag that this applicant has failed a validation
7834: l_appno_failed := TRUE;
7835:
7836: -- update this record with derived error code
7837: UPDATE igs_uc_istrpqr_ints
7838: SET error_code = g_error_code
7839: WHERE rowid = new_ivstarpqr_rec.rowid ;
7840:
7841: -- log error message/meaning.

Line 7863: UPDATE igs_uc_istrpqr_ints SET error_code = '2001'

7859: igs_uc_app_results_pkg.delete_row(get_appno_rec.rowid);
7860: END LOOP;
7861:
7862: -- update INTS records for this appno which are successful to set error_code=2001
7863: UPDATE igs_uc_istrpqr_ints SET error_code = '2001'
7864: WHERE record_status = 'N' AND appno = new_appl_rec.appno AND error_code IS NULL ;
7865:
7866: ELSE
7867: -- update INTS records for this appno which are all successful to set record_status = L

Line 7868: UPDATE igs_uc_istrpqr_ints SET record_status = 'L' , error_code = NULL

7864: WHERE record_status = 'N' AND appno = new_appl_rec.appno AND error_code IS NULL ;
7865:
7866: ELSE
7867: -- update INTS records for this appno which are all successful to set record_status = L
7868: UPDATE igs_uc_istrpqr_ints SET record_status = 'L' , error_code = NULL
7869: WHERE record_status = 'N' AND appno = new_appl_rec.appno ;
7870:
7871: END IF ;
7872: