[Home] [Help]
33:
34: IF c_untref_cd%FOUND
35: THEN
36: fnd_message.set_name ('IGS', 'IGS_AV_UNT_REF_PK_EXISTS ');
37: fnd_msg_pub.ADD;
38: x_return_status := FALSE;
39: mydebug ('validate_unt_bss_db_cons IGS_AV_UNT_REF_PK_EXISTS ');
40: END IF;
41: CLOSE c_untref_cd;
153: IF p_lgcy_adstunt_rec.person_number IS NULL THEN
154: l_s_message_name := 'IGS_EN_PER_NUM_NULL';
155: l_b_return_val :=FALSE;
156: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
157: FND_MSG_PUB.ADD;
158: END IF;
159: IF p_lgcy_adstunt_rec.program_cd IS NULL THEN
160: l_s_message_name := 'IGS_EN_PRGM_CD_NULL';
161: l_b_return_val :=FALSE;
159: IF p_lgcy_adstunt_rec.program_cd IS NULL THEN
160: l_s_message_name := 'IGS_EN_PRGM_CD_NULL';
161: l_b_return_val :=FALSE;
162: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
163: FND_MSG_PUB.ADD;
164: END IF;
165: IF p_lgcy_adstunt_rec.total_exmptn_approved IS NULL THEN
166: l_s_message_name := 'IGS_AV_TOT_EXMPT_APPR_NULL';
167: l_b_return_val :=FALSE;
165: IF p_lgcy_adstunt_rec.total_exmptn_approved IS NULL THEN
166: l_s_message_name := 'IGS_AV_TOT_EXMPT_APPR_NULL';
167: l_b_return_val :=FALSE;
168: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
169: FND_MSG_PUB.ADD;
170: END IF;
171: IF p_lgcy_adstunt_rec.total_exmptn_granted IS NULL THEN
172: l_s_message_name := 'IGS_AV_TOT_EXMPT_GRNT_NULL';
173: l_b_return_val :=FALSE;
171: IF p_lgcy_adstunt_rec.total_exmptn_granted IS NULL THEN
172: l_s_message_name := 'IGS_AV_TOT_EXMPT_GRNT_NULL';
173: l_b_return_val :=FALSE;
174: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
175: FND_MSG_PUB.ADD;
176: END IF;
177: IF p_lgcy_adstunt_rec.total_exmptn_perc_grntd IS NULL THEN
178: l_s_message_name := 'IGS_AV_TOT_EXT_PER_GRNT_NULL';
179: l_b_return_val :=FALSE;
177: IF p_lgcy_adstunt_rec.total_exmptn_perc_grntd IS NULL THEN
178: l_s_message_name := 'IGS_AV_TOT_EXT_PER_GRNT_NULL';
179: l_b_return_val :=FALSE;
180: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
181: FND_MSG_PUB.ADD;
182: END IF;
183: IF p_lgcy_adstunt_rec.exemption_institution_cd IS NULL THEN
184: l_s_message_name := 'IGS_AV_ADLVL_EX_INS_CD_NULL';
185: l_b_return_val :=FALSE;
183: IF p_lgcy_adstunt_rec.exemption_institution_cd IS NULL THEN
184: l_s_message_name := 'IGS_AV_ADLVL_EX_INS_CD_NULL';
185: l_b_return_val :=FALSE;
186: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
187: FND_MSG_PUB.ADD;
188: END IF;
189: IF p_lgcy_adstunt_rec.unit_cd IS NULL THEN
190: l_s_message_name := 'IGS_AV_UNIT_CD_NULL';
191: l_b_return_val :=FALSE;
189: IF p_lgcy_adstunt_rec.unit_cd IS NULL THEN
190: l_s_message_name := 'IGS_AV_UNIT_CD_NULL';
191: l_b_return_val :=FALSE;
192: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
193: FND_MSG_PUB.ADD;
194: END IF;
195: IF p_lgcy_adstunt_rec.version_number IS NULL THEN
196: l_s_message_name := 'IGS_AV_UNIT_VER_NULL';
197: l_b_return_val :=FALSE;
195: IF p_lgcy_adstunt_rec.version_number IS NULL THEN
196: l_s_message_name := 'IGS_AV_UNIT_VER_NULL';
197: l_b_return_val :=FALSE;
198: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
199: FND_MSG_PUB.ADD;
200: END IF;
201: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status IS NULL THEN
202: l_s_message_name := 'IGS_AV_ADLVL_GRNT_STAT_NULL';
203: l_b_return_val :=FALSE;
201: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status IS NULL THEN
202: l_s_message_name := 'IGS_AV_ADLVL_GRNT_STAT_NULL';
203: l_b_return_val :=FALSE;
204: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
205: FND_MSG_PUB.ADD;
206: END IF;
207: IF p_lgcy_adstunt_rec.approved_dt IS NULL THEN
208: l_s_message_name := 'IGS_AV_ADLVL_APPR_DT_NULL';
209: l_b_return_val :=FALSE;
207: IF p_lgcy_adstunt_rec.approved_dt IS NULL THEN
208: l_s_message_name := 'IGS_AV_ADLVL_APPR_DT_NULL';
209: l_b_return_val :=FALSE;
210: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
211: FND_MSG_PUB.ADD;
212: END IF;
213: IF p_lgcy_adstunt_rec.authorising_person_number IS NULL THEN
214: l_s_message_name := 'IGS_AV_ADLV_AUTH_PERNUM_NULL';
215: l_b_return_val :=FALSE;
213: IF p_lgcy_adstunt_rec.authorising_person_number IS NULL THEN
214: l_s_message_name := 'IGS_AV_ADLV_AUTH_PERNUM_NULL';
215: l_b_return_val :=FALSE;
216: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
217: FND_MSG_PUB.ADD;
218: END IF;
219: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type IS NULL THEN
220: l_s_message_name := 'IGS_AV_STUNT_RG_TYP_NOT_NULL';
221: l_b_return_val :=FALSE;
219: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type IS NULL THEN
220: l_s_message_name := 'IGS_AV_STUNT_RG_TYP_NOT_NULL';
221: l_b_return_val :=FALSE;
222: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
223: FND_MSG_PUB.ADD;
224: END IF;
225:
226: IF p_lgcy_adstunt_rec.prev_unit_cd IS NOT NULL AND
227: (p_lgcy_adstunt_rec.prev_term IS NULL OR
231: )THEN
232: l_s_message_name := 'IGS_AV_PREV_UNT_DET_NOT_NULL';
233: l_b_return_val :=FALSE;
234: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
235: FND_MSG_PUB.ADD;
236: END IF;
237:
238: IF p_lgcy_adstunt_rec.tst_admission_test_type IS NOT NULL AND
239: ( p_lgcy_adstunt_rec.tst_test_date IS NULL OR p_lgcy_adstunt_rec.test_segment_name IS NULL) THEN
239: ( p_lgcy_adstunt_rec.tst_test_date IS NULL OR p_lgcy_adstunt_rec.test_segment_name IS NULL) THEN
240: l_s_message_name := 'IGS_AV_TST_ADM_DET_NOT_NULL';
241: l_b_return_val :=FALSE;
242: FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
243: FND_MSG_PUB.ADD;
244: END IF;
245:
246: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type <> 'PRECLUSION' AND
247: (
249: p_lgcy_adstunt_rec.alt_version_number IS NOT NULL
250: )
251: THEN
252: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_CRD_ALT_BOTH_EXISTS');
253: FND_MSG_PUB.ADD;
254: l_b_return_val := FALSE;
255: END IF;
256:
257: /*
262: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' AND
263: (p_lgcy_adstunt_rec.revoked_dt IS NOT NULL OR
264: p_lgcy_adstunt_rec.cancelled_dt IS NOT NULL) THEN
265: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
266: FND_MSG_PUB.ADD;
267: l_b_return_val := FALSE;
268: END IF;
269:
270: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
270: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
271: (p_lgcy_adstunt_rec.revoked_dt IS NOT NULL OR
272: p_lgcy_adstunt_rec.granted_dt IS NOT NULL) THEN
273: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
274: FND_MSG_PUB.ADD;
275: l_b_return_val := FALSE;
276: END IF;
277:
278: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'REVOKED' AND
278: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'REVOKED' AND
279: (p_lgcy_adstunt_rec.granted_dt IS NOT NULL OR
280: p_lgcy_adstunt_rec.cancelled_dt IS NOT NULL) THEN
281: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
282: FND_MSG_PUB.ADD;
283: l_b_return_val := FALSE;
284: END IF;
285: mydebug('Comming Out Of validate_parameters' || l_s_message_name );
286: RETURN l_b_return_val;
393: p_person_id := IGS_GE_GEN_003.get_person_id(p_lgcy_adstunt_rec.person_number );
394: mydebug('Got person ID as ' || p_person_id);
395: IF p_person_id IS NULL THEN
396: FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
397: FND_MSG_PUB.ADD;
398: RETURN FALSE;
399: END IF;
400:
401: IF p_lgcy_adstunt_rec.load_cal_alt_code IS NULL THEN
399: END IF;
400:
401: IF p_lgcy_adstunt_rec.load_cal_alt_code IS NULL THEN
402: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
403: FND_MSG_PUB.ADD;
404: RETURN FALSE;
405: END IF;
406:
407: mydebug('Calling IGS_GE_GEN_003.get_calendar_instance ' || p_cal_type || p_lgcy_adstunt_rec.load_cal_alt_code);
415: mydebug('Got p_cal_type as ' || p_cal_type || ' and p_seq_number as' || p_seq_number);
416: -- IF 0 or more load calendars are found
417: IF p_seq_number IS NULL OR p_cal_type IS NULL THEN
418: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
419: FND_MSG_PUB.ADD;
420: RETURN FALSE;
421: END IF;
422:
423: p_auth_pers_id := igs_ge_gen_003.get_person_id(p_lgcy_adstunt_rec.authorising_person_number );
479: CLOSE c_ref_id;
480:
481: IF p_lgcy_adstunt_rec.reference_cd_type IS NOT NULL AND l_reference_code_id IS NULL THEN
482: FND_MESSAGE.SET_NAME('IGS', 'IGS_PS_NO_OPEN_REFCDTYPE_EXIS');
483: FND_MSG_PUB.ADD;
484: RETURN FALSE;
485: END IF;
486: p_reference_code_id :=l_reference_code_id;
487: mydebug('Got p_reference_code_id as ' || l_reference_code_id);
528: p_message_name => l_message_name,
529: p_return_type => l_return_type
530: ) THEN
531: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_LYENR_NOTGT_CURYR');
532: FND_MSG_PUB.ADD;
533: l_b_return_val:=FALSE;
534: END IF;
535:
536: RETURN l_b_return_val;
570: x_course_cd => p_lgcy_adstunt_rec.program_cd,
571: x_version_number => p_version_number
572: ) THEN
573: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PRG_CD_NOT_EXISTS');
574: FND_MSG_PUB.ADD;
575: x_return_status := FALSE;
576: END IF;
577: mydebug('Inside validate_adv_std_db_cons Got x_return_status as ' );
578: return x_return_status;
621: FETCH c_ind INTO l_ind;
622: CLOSE c_ind;
623: IF upper(l_ind) = 'Y' THEN
624: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PERSON_DECEASED');
625: FND_MSG_PUB.ADD;
626: x_return_status := FALSE;
627: END IF;
628: mydebug ('l_ind :'||l_ind);
629: END;
642: BEGIN
643: OPEN c_validate_inst(p_lgcy_adstunt_rec.exemption_institution_cd);
644: IF c_validate_inst%NOTFOUND THEN
645: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_STND_EXMPT_INVALID');
646: FND_MSG_PUB.ADD;
647: x_return_status := FALSE;
648: END IF;
649: CLOSE c_validate_inst;
650: mydebug (' exemption_inst_cd');
662: p_version_number => p_version_number,
663: p_message_name => l_message_name
664: ) THEN
665: FND_MESSAGE.SET_NAME('IGS', 'IGS_HE_EXT_SPA_DTL_NOT_FOUND');
666: FND_MSG_PUB.ADD;
667: x_return_status := FALSE;
668: END IF;
669: END;
670: /*
708: IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
709: IF p_lgcy_adstunt_rec.total_exmptn_approved < 0 OR
710: p_lgcy_adstunt_rec.total_exmptn_approved > rec_cur_program_exempt_totals.adv_stnd_limit THEN
711: FND_MESSAGE.SET_NAME('IGS',l_message_name);
712: FND_MSG_PUB.ADD;
713: x_return_status := FALSE;
714: END IF;
715: END IF;
716: IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
716: IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
717: IF p_lgcy_adstunt_rec.total_exmptn_granted < 0 OR
718: p_lgcy_adstunt_rec.total_exmptn_granted > rec_cur_program_exempt_totals.adv_stnd_limit THEN
719: FND_MESSAGE.SET_NAME('IGS',l_message_name);
720: FND_MSG_PUB.ADD;
721: x_return_status := FALSE;
722: END IF;
723: END IF;
724: END;
739: p_lgcy_adstunt_rec.program_cd);
740: FETCH c_exists INTO l_exists;
741: IF c_exists%NOTFOUND THEN
742: FND_MESSAGE.SET_NAME('IGS','IGS_AV_PRG_ATTMPT_INVALID');
743: FND_MSG_PUB.ADD;
744: x_return_status := FALSE;
745: END IF;
746: CLOSE c_exists;
747: END;
808: mydebug('***** Got l_av_stnd_unit_lvl_id=' ||l_av_stnd_unit_lvl_id);
809: -- Primary key validation
810: IF igs_av_stnd_unit_pkg.get_pk_for_validation(x_av_stnd_unit_id => l_av_stnd_unit_lvl_id) THEN
811: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STDUNT_ALREADY_EXISTS');
812: FND_MSG_PUB.ADD;
813: x_return_status := FALSE;
814: mydebug('validate_std_unt_db_cons IGS_AV_STND_UNIT_PKG.GET_PK_FOR_VALIDATION ');
815: RETURN x_return_status;
816: ELSE
822: IF p_unit_details_id IS NULL AND
823: p_lgcy_adstunt_rec.prev_unit_cd IS NOT NULL AND
824: p_lgcy_adstunt_rec.prev_term IS NOT NULL THEN
825: FND_MESSAGE.SET_NAME('IGS','IGS_AV_TERM_UNTDTLS_NOT_EXISTS');
826: FND_MSG_PUB.ADD;
827: x_return_status := FALSE;
828: mydebug('validate_std_unt_db_cons p_unit_details_id ');
829: END IF;
830: -- Foreign Key with Table IGS_AD_TST_RSLT_DTLS
829: END IF;
830: -- Foreign Key with Table IGS_AD_TST_RSLT_DTLS
831: IF p_tst_rslt_dtls_id IS NULL AND p_lgcy_adstunt_rec.tst_admission_test_type IS NOT NULL THEN
832: FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADM_TST_RSLT_NOT_EXISTS');
833: FND_MSG_PUB.ADD;
834: x_return_status := FALSE;
835: mydebug('validate_std_unt_db_cons p_tst_rslt_dtls_id ');
836: END IF;
837: -- Foreign Key with Table IGS_AV_ADV_STANDING_PKG
841: x_version_number => p_as_version_number ,
842: x_exemption_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd
843: ) THEN
844: FND_MESSAGE.SET_NAME('IGS','IGS_AV_NO_ADV_STND_DET_EXIST');
845: FND_MSG_PUB.ADD;
846: x_return_status := FALSE;
847: mydebug('validate_std_unt_db_cons IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION ');
848: END IF;
849: -- Foreign Key with AUTHORIZING_PERSON_ID exists in table IGS_PE_PERSON
848: END IF;
849: -- Foreign Key with AUTHORIZING_PERSON_ID exists in table IGS_PE_PERSON
850: IF p_auth_pers_id IS NULL THEN
851: FND_MESSAGE.SET_NAME('IGS','IGS_AV_INVALID_PERS_AUTH_NUM');
852: FND_MSG_PUB.ADD;
853: x_return_status := FALSE;
854: mydebug('validate_std_unt_db_cons p_auth_pers_id ');
855: END IF;
856: -- Valid s_adv_granting_status exists
857: IF NOT igs_lookups_view_pkg.get_pk_for_validation(
858: x_lookup_type => 'ADV_STND_GRANTING_STATUS',
859: x_lookup_code => p_lgcy_adstunt_rec.s_adv_stnd_granting_status) THEN
860: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CANNOT_DTR_GRNT_STAT');
861: FND_MSG_PUB.ADD;
862: x_return_status := FALSE;
863: mydebug('validate_std_unt_db_cons IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION ');
864: END IF;
865:
868: x_unit_cd => p_lgcy_adstunt_rec.unit_cd,
869: x_version_number => p_lgcy_adstunt_rec.version_number
870: ) THEN
871: FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADV_STUNT_UNIT_EXISTS');
872: FND_MSG_PUB.ADD;
873: x_return_status := FALSE;
874: mydebug('validate_std_unt_db_cons IGS_PS_UNIT_VER_PKG.GET_PK_FOR_VALIDATION ');
875: END IF;
876:
880: x_version_number => p_lgcy_adstunt_rec.grd_sch_version_number,
881: x_grade => p_lgcy_adstunt_rec.grade
882: ) THEN
883: FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADV_STUNT_GRD_EXISTS');
884: FND_MSG_PUB.ADD;
885: x_return_status := FALSE;
886: mydebug('validate_std_unt_db_cons IGS_AS_GRD_SCH_GRADE_PKG.GET_PK_FOR_VALIDATION ');
887: END IF;
888:
889: -- Validate that the record parameter S_Adv_Stnd_Recognition_Type cannot have any other values other than 'CREDIT','EXEMPTION' or 'PRECLUSION'
890:
891: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type NOT IN ('CREDIT' , 'EXEMPTION' , 'PRECLUSION') THEN
892: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_RECOG_VALUE');
893: FND_MSG_PUB.ADD;
894: x_return_status := FALSE;
895: mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.S_ADV_STND_RECOGNITION_TYPE ');
896: END IF;
897: -- Check constraint on PROG_GROUP_IND
897: -- Check constraint on PROG_GROUP_IND
898: IF p_lgcy_adstunt_rec.prog_group_ind <> upper(p_lgcy_adstunt_rec.prog_group_ind ) AND
899: p_lgcy_adstunt_rec.prog_group_ind NOT IN ('Y' , 'N') THEN
900: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRS_GRP_IN_Y_N');
901: FND_MSG_PUB.ADD;
902: x_return_status := FALSE;
903: mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.PROG_GROUP_IND ');
904: END IF;
905: -- Check that if institution_cd is NOT NULL and unit_details_id is NULL
905: -- Check that if institution_cd is NOT NULL and unit_details_id is NULL
906: IF p_lgcy_adstunt_rec.institution_cd IS NOT NULL AND
907: p_unit_details_id IS NULL THEN
908: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUT_INST_UID_NOT_NULL');
909: FND_MSG_PUB.ADD;
910: x_return_status := FALSE;
911: mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.EXEMPTION_INSTITUTION_CD ');
912: END IF;
913:
914: -- Validate that both institution_cd and tst_rslt_dtls_id are not nulls
915: IF p_lgcy_adstunt_rec.institution_cd IS NOT NULL AND
916: p_tst_rslt_dtls_id IS NOT NULL THEN
917: FND_MESSAGE.SET_NAME('IGS','IGS_AV_INST_RLID_BOTH_NOT_NULL');
918: FND_MSG_PUB.ADD;
919: x_return_status := FALSE;
920: mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.EXEMPTION_INSTITUTION_CD ');
921: END IF;
922: -- One and only one of unit details or test result details must be entered (both cannot be Not Nulls simultaneously
922: -- One and only one of unit details or test result details must be entered (both cannot be Not Nulls simultaneously
923: IF p_unit_details_id IS NULL AND
924: p_tst_rslt_dtls_id IS NULL THEN
925: FND_MESSAGE.SET_NAME('IGS','IGS_AV_UID_RSID_ATLEAST_NULL');
926: FND_MSG_PUB.ADD;
927: x_return_status := FALSE;
928: mydebug('validate_std_unt_db_cons IGS_AV_UID_RSID_ATLEAST_NULL ');
929: END IF;
930:
983: Validate that the approved date is greater than current date
984: */
985: IF p_lgcy_adstunt_rec.approved_dt >= SYSDATE THEN
986: FND_MESSAGE.SET_NAME('IGS','IGS_AV_APRVDT_LE_CURDT');
987: FND_MSG_PUB.ADD;
988: x_return_status := FALSE;
989: END IF;
990:
991: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
990:
991: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
992: p_lgcy_adstunt_rec.achievable_credit_points <> 0.00 THEN
993: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CREDIT_PRECL_IS_ZERO');
994: FND_MSG_PUB.ADD;
995: x_return_status := FALSE;
996: mydebug('validate_unit IGS_AV_CREDIT_PRECL_IS_ZERO ');
997: END IF;
998: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
997: END IF;
998: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
999: p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' THEN
1000: FND_MESSAGE.SET_NAME('IGS','IGS_AV_NOT_GRT_PRE');
1001: FND_MSG_PUB.ADD;
1002: x_return_status := FALSE;
1003: mydebug('validate_unit IGS_AV_NOT_GRT_PRE ');
1004: END IF;
1005:
1005:
1006: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'EXPIRED' AND
1007: p_lgcy_adstunt_rec.expiry_dt IS NULL THEN
1008: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_EXPDT_TOBE_SET');
1009: FND_MSG_PUB.ADD;
1010: x_return_status := FALSE;
1011: mydebug('validate_unit IGS_AV_STUNT_EXPDT_TOBE_SET');
1012: END IF;
1013:
1030: p_unit_details_id => p_unit_details_id ,
1031: p_tst_rslt_dtls_id => p_tst_rslt_dtls_id
1032: ) THEN
1033: FND_MESSAGE.SET_NAME('IGS',l_message_name);
1034: FND_MSG_PUB.ADD;
1035: x_return_status := FALSE;
1036: mydebug('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_AS_TOTALS ');
1037: END IF;
1038:
1043: p_effective_dt => p_lgcy_adstunt_rec.granted_dt,
1044: p_message_name => l_message_name
1045: ) THEN
1046: FND_MESSAGE.SET_NAME('IGS',l_message_name);
1047: FND_MSG_PUB.ADD;
1048: x_return_status := FALSE;
1049: mydebug('validate_unit IGS_EN_VAL_ENCMB.ENRP_VAL_EXCLD_PRSN ');
1050: END IF;
1051:
1052:
1053: IF p_as_version_number IS NULL AND
1054: p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' THEN
1055: FND_MESSAGE.SET_NAME('IGS','IGS_AV_GRANTED_STUDPRG_EXISTS');
1056: FND_MSG_PUB.ADD;
1057: x_return_status := FALSE;
1058: mydebug('validate_unit IGS_AV_GRANTED_STUDPRG_EXISTS ');
1059: END IF;
1060:
1060:
1061: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' AND
1062: p_lgcy_adstunt_rec.granted_dt IS NULL THEN
1063: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_GRANTDT_NOT_NULL');
1064: FND_MSG_PUB.ADD;
1065: x_return_status := FALSE;
1066: mydebug('validate_unit IGS_AV_STUNT_GRANTDT_NOT_NULL');
1067: END IF;
1068:
1068:
1069: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
1070: p_lgcy_adstunt_rec.cancelled_dt IS NULL THEN
1071: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_CANCDT_NOT_NULL');
1072: FND_MSG_PUB.ADD;
1073: x_return_status := FALSE;
1074: mydebug('validate_unit IGS_AV_STUNT_CANCDT_NOT_NULL');
1075: END IF;
1076:
1076:
1077: IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'REVOKED' AND
1078: p_lgcy_adstunt_rec.revoked_dt IS NULL THEN
1079: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_REVDT_NOT_NULL');
1080: FND_MSG_PUB.ADD;
1081: x_return_status := FALSE;
1082: mydebug('validate_unit IGS_AV_STUNT_REVDT_NOT_NULL');
1083: END IF;
1084:
1087: p_expiry_dt => p_lgcy_adstunt_rec.expiry_dt ,
1088: p_message_name => l_message_name
1089: ) THEN
1090: FND_MESSAGE.SET_NAME('IGS',l_message_name);
1091: FND_MSG_PUB.ADD;
1092: x_return_status := FALSE;
1093: mydebug('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_APPROVED_DT ');
1094: END IF;
1095: -- Validate whether the Granted Date, Cancelled Date Or Revoked Dates are greater than or equal to the Approved date
1111: )
1112: )THEN
1113:
1114: FND_MESSAGE.SET_NAME('IGS','IGS_AV_DTASSO_LE_APPRVDT' );
1115: FND_MSG_PUB.ADD;
1116: x_return_status := FALSE;
1117: mydebug('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_APRVD_DT ');
1118: END IF;
1119:
1121: p_person_id => p_auth_pers_id ,
1122: p_message_name => l_message_name
1123: ) THEN
1124: FND_MESSAGE.SET_NAME('IGS','IGS_GE_NOT_STAFF_MEMBER');
1125: FND_MSG_PUB.ADD;
1126: x_return_status := FALSE;
1127: mydebug('validate_unit IGS_GE_NOT_STAFF_MEMBER ');
1128: END IF;
1129:
1128: END IF;
1129:
1130: IF p_lgcy_adstunt_rec.achievable_credit_points IS NULL THEN
1131: FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRD_PER_CANNOT_BE_NULL');
1132: FND_MSG_PUB.ADD;
1133: x_return_status := FALSE;
1134: mydebug('validate_unit IGS_AV_CRD_PER_CANNOT_BE_NULL ');
1135: END IF;
1136:
1151: p_lgcy_adstunt_rec.program_cd);
1152: FETCH c_exists INTO l_exists;
1153: IF c_exists%NOTFOUND THEN
1154: FND_MESSAGE.SET_NAME('IGS','IGS_AV_PRG_ATTMPT_INVALID');
1155: FND_MSG_PUB.ADD;
1156: mydebug('validate_unit IGS_AV_PRG_ATTMPT_INVALID ');
1157: x_return_status := FALSE;
1158: END IF;
1159: CLOSE c_exists;
1221: p_tst_rslt_dtls_id => p_tst_rslt_dtls_id,
1222: p_asu_exmptn_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd
1223: ) THEN
1224: FND_MESSAGE.SET_NAME('IGS',l_message);
1225: FND_MSG_PUB.ADD;
1226: x_return_status := FALSE;
1227: ELSE -- function returns TRUE
1228: /*
1229: update IGS_AV_ADV_STANDING_ALL with above obtained values for
1322: FETCH cur_get_adv_stnd_id INTO l_av_stnd_unit_lvl_id;
1323: IF cur_get_adv_stnd_id%NOTFOUND THEN
1324: -- foreign key with table igs_av_stnd_unit_all does not exist.
1325: FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_ALT_ID_FK_EXISTS');
1326: FND_MSG_PUB.ADD;
1327: x_return_status := 'E';
1328: l_return_status := FALSE;
1329: ELSE
1330: -- av_stnd_unit_lvl_id found in table igs_av_stnd_unit_all
1335: x_alt_version_number => p_lgcy_adstunt_rec.alt_version_number
1336: )THEN
1337: CLOSE cur_get_adv_stnd_id;
1338: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STDUNT_ALREADY_EXISTS');
1339: FND_MSG_PUB.ADD;
1340: x_return_status := 'W';
1341: l_return_status := FALSE;
1342: RETURN (l_return_status) ;
1343: END IF;
1349: x_unit_cd =>p_lgcy_adstunt_rec.unit_cd ,
1350: x_version_number => p_lgcy_adstunt_rec.version_number
1351: )THEN
1352: FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_ALT_UID_FK_EXISTS');
1353: FND_MSG_PUB.ADD;
1354: x_return_status := 'E';
1355: l_return_status := FALSE;
1356: mydebug('validate_unit IGS_AV_UNT_ALT_UID_FK_EXISTS ');
1357: END IF;
1357: END IF;
1358:
1359: IF p_lgcy_adstunt_rec.OPTIONAL_IND NOT IN ('Y' , 'N') THEN
1360: FND_MESSAGE.SET_NAME('IGS','IGS_AV_ALT_OPT_IND_IN_Y_N');
1361: FND_MSG_PUB.ADD;
1362: x_return_status := 'E';
1363: l_return_status := FALSE;
1364: mydebug('validate_unit IGS_AV_ALT_OPT_IND_IN_Y_N ');
1365: END IF;
1406: p_alternate_unit_cd => p_lgcy_adstunt_rec.alt_unit_cd ,
1407: p_message_name => l_message_name
1408: ) THEN
1409: FND_MESSAGE.SET_NAME('IGS','IGS_AV_ALTUNIT_DIFF_UNITASSOC');
1410: FND_MSG_PUB.ADD;
1411: x_return_status := FALSE;
1412: mydebug('validate_unit IGS_AV_ALTUNIT_DIFF_UNITASSOC ');
1413: END IF;
1414: RETURN x_return_status;
1451: IF igs_av_std_unt_basis_pkg.get_pk_for_validation(
1452: x_av_stnd_unit_id =>p_av_stnd_unit_id
1453: ) THEN
1454: FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_BAS_PK_EXISTS');
1455: FND_MSG_PUB.ADD;
1456: x_return_status := FALSE;
1457: mydebug('validate_unt_bss_db_cons IGS_AV_UNT_BAS_PK_EXISTS ');
1458: END IF;
1459:
1459:
1460: -- Check Foreign key Validation with the table IGS_AV_STND_UNIT_ALL
1461: IF NOT IGS_AV_STND_UNIT_PKG.GET_PK_FOR_VALIDATION( x_av_stnd_unit_id => p_av_stnd_unit_id) THEN
1462: FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_ALT_ID_FK_EXISTS');
1463: FND_MSG_PUB.ADD;
1464: x_return_status := FALSE;
1465: mydebug('validate_unt_bss_db_cons IGS_AV_UNT_ALT_ID_FK_EXISTS ');
1466: END IF;
1467:
1469: IF ((p_lgcy_adstunt_rec.basis_program_type IS NOT NULL) AND NOT igs_ps_type_pkg.get_pk_for_validation(
1470: x_course_type =>p_lgcy_adstunt_rec.basis_program_type
1471: )) THEN
1472: FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_CRS_TYP_FK_EXISTS');
1473: FND_MSG_PUB.ADD;
1474: x_return_status := FALSE;
1475: mydebug('validate_unt_bss_db_cons IGS_AV_BAS_CRS_TYP_FK_EXISTS ');
1476: END IF;
1477: -- Validate that the record parameter basis_year has a value greater than 1900 and less than 2100
1477: -- Validate that the record parameter basis_year has a value greater than 1900 and less than 2100
1478: IF ((p_lgcy_adstunt_rec.basis_year IS NOT NULL) AND (p_lgcy_adstunt_rec.basis_year < 1900 OR
1479: p_lgcy_adstunt_rec.basis_year > 2100)) THEN
1480: FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_YEAR_1900_2100');
1481: FND_MSG_PUB.ADD;
1482: x_return_status := FALSE;
1483: mydebug('validate_unt_bss_db_cons IGS_AV_BAS_YEAR_1900_2100 ');
1484: END IF;
1485: -- Validate that the value for the record parameter Basis_completion_Ind field cannot be anything other than 'Y' or 'N'
1484: END IF;
1485: -- Validate that the value for the record parameter Basis_completion_Ind field cannot be anything other than 'Y' or 'N'
1486: IF ((p_lgcy_adstunt_rec.basis_completion_ind IS NOT NULL) AND (NOT p_lgcy_adstunt_rec.basis_completion_ind IN ('Y','N'))) THEN
1487: FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_COMP_IND_IN_Y_N');
1488: FND_MSG_PUB.ADD;
1489: x_return_status := FALSE;
1490: mydebug('validate_unt_bss_db_cons IGS_AV_BAS_COMP_IND_IN_Y_N ');
1491: END IF;
1492: RETURN x_return_status;
1533: p_version_number => p_lgcy_adstunt_rec.version_number ,
1534: p_message_name => l_message_name,
1535: p_return_type => l_return_type ) THEN
1536: FND_MESSAGE.SET_NAME('IGS','IGS_AV_LYENR_NOTGT_CURYR');
1537: FND_MSG_PUB.ADD;
1538: x_return_status := FALSE;
1539: mydebug('validate_unt_bss_db_cons IGS_AV_LYENR_NOTGT_CURYR');
1540: END IF;
1541: RETURN x_return_status;
1625: END IF;
1626:
1627: --Initialize message list if p_init_msg_list is set to TRUE.
1628: IF FND_API.to_Boolean(p_init_msg_list) THEN
1629: FND_MSG_PUB.initialize;
1630: END IF;
1631:
1632: --Initialize API return status to success.
1633: x_return_status := FND_API.G_RET_STS_SUCCESS;
1957: ELSE
1958: IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type <> 'PRECLUSION' THEN
1959: mydebug('****IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION and s_adv_stnd_recognition_type <> PRECLUSION ');
1960: FND_MESSAGE.SET_NAME('IGS','IGS_AV_STDUNT_ALREADY_EXISTS');
1961: FND_MSG_PUB.ADD;
1962: RAISE duplicate_record_exists;
1963: END IF;
1964: END IF; --IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION
1965: mydebug('***** Preclusion *****');
2050: mydebug('************************ Doing a COMMIT ********************');
2051: commit;
2052: END IF;
2053: --Standard call to get message count and if count is 1, get message info.
2054: FND_MSG_PUB.Count_And_Get(
2055: p_count => x_msg_count,
2056: p_data => x_msg_data);
2057: EXCEPTION
2058: WHEN DUPLICATE_RECORD_EXISTS THEN
2057: EXCEPTION
2058: WHEN DUPLICATE_RECORD_EXISTS THEN
2059: ROLLBACK TO create_adv_stnd_unit;
2060: x_return_status := 'W';
2061: FND_MSG_PUB.Count_And_Get(
2062: p_count => x_msg_count,
2063: p_data => x_msg_data
2064: );
2065: WHEN FND_API.G_EXC_ERROR THEN
2064: );
2065: WHEN FND_API.G_EXC_ERROR THEN
2066: ROLLBACK TO create_adv_stnd_unit;
2067: x_return_status := FND_API.G_RET_STS_ERROR;
2068: FND_MSG_PUB.Count_And_Get(
2069: p_count => x_msg_count,
2070: p_data => x_msg_data);
2071:
2072: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2071:
2072: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2073: ROLLBACK TO create_adv_stnd_unit;
2074: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2075: FND_MSG_PUB.Count_And_Get(
2076: p_count => x_msg_count,
2077: p_data => x_msg_data);
2078: WHEN OTHERS THEN
2079: ROLLBACK TO create_adv_stnd_unit;
2079: ROLLBACK TO create_adv_stnd_unit;
2080: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2081: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
2082: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2083: FND_MSG_PUB.ADD;
2084:
2085: FND_MSG_PUB.Count_And_Get(
2086: p_count => x_msg_count,
2087: p_data => x_msg_data);
2081: FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
2082: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2083: FND_MSG_PUB.ADD;
2084:
2085: FND_MSG_PUB.Count_And_Get(
2086: p_count => x_msg_count,
2087: p_data => x_msg_data);
2088:
2089: END create_adv_stnd_unit;