[Home] [Help]
32: OPEN c_untref_cd;
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;
152:
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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';
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;
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
230: p_lgcy_adstunt_rec.institution_cd IS NULL
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
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
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
248: p_lgcy_adstunt_rec.alt_unit_cd IS NOT NULL OR
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:
261: */
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:
269:
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:
277:
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 );
392: p_s_adv_stnd_type := 'UNIT'; -- initialise
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:
398: RETURN FALSE;
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:
414: p_return_status => l_return_status );
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:
478: END LOOP;
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;
527: p_version_number => p_version_number,
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:
569: (
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 ' );
620: OPEN c_ind (p_person_id);
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);
641: AND ihp.oss_org_unit_cd = cp_exemption_institution_cd;
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;
661: p_course_cd => p_lgcy_adstunt_rec.program_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;
707: CLOSE cur_program_exempt_totals;
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;
715: END IF;
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;
738: OPEN c_exists (p_person_id,
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;
807:
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;
821:
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;
828: mydebug('validate_std_unt_db_cons p_unit_details_id ');
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;
840: x_course_cd => p_lgcy_adstunt_rec.program_cd,
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;
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
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;
867: IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
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;
879: IF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation ( x_grading_schema_cd => p_lgcy_adstunt_rec.grading_schema_cd,
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;
896: END IF;
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;
904: END IF;
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;
921: END IF;
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;
982: /*
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:
989: END IF;
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;
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
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;
1004: END IF;
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;
1029: p_message_name => l_message_name,
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;
1042: p_course_cd => p_lgcy_adstunt_rec.program_cd,
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;
1059: END IF;
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;
1067: END IF;
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;
1075: END IF;
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;
1086: p_approved_dt => p_lgcy_adstunt_rec.approved_dt ,
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;
1110: p_message_name => l_message_name
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;
1120: IF NOT igs_ad_val_acai.genp_val_staff_prsn(
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;
1127: mydebug('validate_unit IGS_GE_NOT_STAFF_MEMBER ');
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;
1150: OPEN c_exists (p_person_id,
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;
1220: p_unit_details_id => p_unit_details_id,
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: /*
1321: );
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
1334: x_alt_unit_cd => p_lgcy_adstunt_rec.alt_unit_cd,
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) ;
1348: IF NOT IGS_PS_UNIT_VER_PKG.GET_PK_FOR_VALIDATION(
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 ');
1356: mydebug('validate_unit IGS_AV_UNT_ALT_UID_FK_EXISTS ');
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 ');
1405: p_precluded_unit_cd => p_lgcy_adstunt_rec.unit_cd ,
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;
1450: -- Primary key validation
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;
1458: END IF;
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;
1468: -- Check passed BASIS_PROGRAM_TYPE for x_course_type
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;
1476: END IF;
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;
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'
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;
1532: p_course_cd => p_lgcy_adstunt_rec.program_cd ,
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;
1956: END IF; --validate_std_unt_db_cons
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
2077: p_data => x_msg_data);
2078: WHEN OTHERS THEN
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(
2078: WHEN OTHERS THEN
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,