APPS.IGS_AD_IMP_024 dependencies on IGS_AD_TRANSCRIPT

Line 75: FROM IGS_AD_TRANSCRIPT trans_oss

71: FROM igs_ad_txcpt_int trans
72: WHERE interface_run_id = p_interface_run_id
73: AND trans.status = '2'
74: AND ( NOT EXISTS (SELECT 1
75: FROM IGS_AD_TRANSCRIPT trans_oss
76: WHERE education_id = trans.education_id
77: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue) )
78: OR ( p_rule = 'R' AND trans.match_ind IN ('16', '25') )
79: )

Line 89: AND ( EXISTS (SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss

85: WHERE interface_run_id = p_interface_run_id
86: AND status = '2'
87: AND ( p_rule = 'I' OR (p_rule = 'R' AND trans.match_ind = '21'))
88: AND interface_transcript_id BETWEEN cp_start_int_id AND cp_end_int_id --ARVSRINI--
89: AND ( EXISTS (SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
90: WHERE education_id = trans.education_id
91: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
92: )
93: OR UPDATE_TRANSCRIPT_ID IS NOT NULL

Line 100: IGS_AD_TRANSCRIPT trans_oss

96: CURSOR c_dup_cur(trans_cur_rec trans_cur%ROWTYPE) IS
97: SELECT
98: trans_oss.rowid, trans_oss.*
99: FROM
100: IGS_AD_TRANSCRIPT trans_oss
101: WHERE ( transcript_id = trans_cur_rec.update_transcript_id
102: AND trans_cur_rec.update_transcript_id IS NOT NULL)
103: OR ( trans_cur_rec.update_transcript_id IS NULL
104: AND education_id = trans_cur_rec.education_id

Line 158: igs_ad_transcript_pkg.insert_row(

154: END IF;
155: END IF;
156: l_msg_at_index := igs_ge_msg_stack.count_msg;
157: SAVEPOINT before_create_transcript;
158: igs_ad_transcript_pkg.insert_row(
159: x_rowid => l_rowid,
160: x_quintile_rank => p_trans_record.quintile_rank,
161: x_percentile_rank => p_trans_record.percentile_rank,
162: x_transcript_id => l_transcript_id,

Line 287: igs_ad_transcript_pkg.update_row(

283: END IF;
284: END IF;
285: l_msg_at_index := igs_ge_msg_stack.count_msg;
286: SAVEPOINT before_update_transcript;
287: igs_ad_transcript_pkg.update_row(
288: X_ROWID => dup_cur_rec.rowid,
289: X_QUINTILE_RANK =>NVL(p_trans_record.QUINTILE_RANK, dup_cur_rec.QUINTILE_RANK),
290: X_PERCENTILE_RANK => NVL(p_trans_record.PERCENTILE_RANK, dup_cur_rec.PERCENTILE_RANK),
291: X_TRANSCRIPT_ID =>dup_cur_rec.transcript_id ,

Line 386: AND NOT EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT

382: error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E707', 8405)
383: WHERE interface_run_id = p_interface_run_id
384: AND status = '2'
385: AND trans.update_transcript_id IS NOT NULL
386: AND NOT EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT
387: WHERE transcript_id = NVL(trans.update_transcript_id,transcript_id)
388: ) ;
389: COMMIT;
390:

Line 441: FROM IGS_AD_TRANSCRIPT trans_oss

437: WHERE interface_run_id = p_interface_run_id
438: AND status = '2'
439: AND UPDATE_TRANSCRIPT_ID IS NULL
440: AND 1 < ( SELECT COUNT(*)
441: FROM IGS_AD_TRANSCRIPT trans_oss
442: WHERE education_id = trans.education_id
443: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
444: );
445:

Line 460: FROM IGS_AD_TRANSCRIPT trans_oss

456: AND status = '2'
457: AND UPDATE_TRANSCRIPT_ID IS NULL
458: AND NVL(match_ind, '15') IN ('15', '21')
459: AND 1 < ( SELECT COUNT(*)
460: FROM IGS_AD_TRANSCRIPT trans_oss
461: WHERE education_id = trans.education_id
462: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
463: );
464:

Line 490: AND 1 < ( SELECT count(*) FROM igs_ad_transcript trans_oss

486: AND status = '2'
487: AND EXISTS( SELECT 1 FROM IGS_AD_TRMDT_INT term
488: WHERE term.interface_transcript_id = trans.interface_transcript_id
489: AND status = '2')
490: AND 1 < ( SELECT count(*) FROM igs_ad_transcript trans_oss
491: WHERE education_id = trans.education_id
492: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
493: );
494: COMMIT;

Line 499: , transcript_id = ( SELECT transcript_id FROM IGS_AD_TRANSCRIPT trans_oss

495: UPDATE igs_ad_txcpt_int trans
496: SET
497: status = '1'
498: , match_ind = '19'
499: , transcript_id = ( SELECT transcript_id FROM IGS_AD_TRANSCRIPT trans_oss
500: WHERE education_id = trans.education_id
501: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
502: AND rownum <= 1)
503: WHERE interface_run_id = p_interface_run_id

Line 505: AND EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss

501: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
502: AND rownum <= 1)
503: WHERE interface_run_id = p_interface_run_id
504: AND status = '2'
505: AND EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
506: WHERE education_id = trans.education_id
507: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
508: );
509: END IF;

Line 574: AND EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss

570: , match_ind = '23'
571: WHERE interface_run_id = p_interface_run_id
572: AND status = '2'
573: AND NVL (match_ind, '15') = '15'
574: AND EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
575: WHERE education_id = trans.education_id
576: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
577: AND transcript_type = trans.transcript_type
578: AND TRANSCRIPT_STATUS = trans.TRANSCRIPT_STATUS

Line 616: , dup_transcript_id= ( SELECT transcript_id FROM IGS_AD_TRANSCRIPT trans_oss

612: UPDATE igs_ad_txcpt_int trans
613: SET
614: status = '3'
615: , match_ind = '20'
616: , dup_transcript_id= ( SELECT transcript_id FROM IGS_AD_TRANSCRIPT trans_oss
617: WHERE education_id = trans.education_id
618: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue))
619: WHERE interface_run_id = p_interface_run_id
620: AND status = '2'

Line 622: AND EXISTS (SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss

618: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue))
619: WHERE interface_run_id = p_interface_run_id
620: AND status = '2'
621: AND NVL (match_ind, '15') = '15'
622: AND EXISTS (SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
623: WHERE education_id = trans.education_id
624: AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue));
625:
626: END IF;