[Home] [Help]
66: l_db_val_failed BOOLEAN := FALSE;
67: BEGIN
68:
69: -- Check whether Hesa program statistics details already exists for the given program attempt
70: IF igs_he_st_spa_all_pkg.get_uk_for_validation(x_person_id => p_person_id,
71: x_course_cd => p_hesa_spa_stats_rec.program_cd) THEN
72: FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPA_STATS_EXIST');
73: FND_MSG_PUB.ADD;
74: RETURN 'W';
412: --
413: -- Check whether the Associate UCAS Number has valid value
414: IF p_hesa_spa_stats_rec.associate_ucas_number IS NOT NULL THEN
415: BEGIN
416: igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_UCAS_NUMBER',
417: column_value => p_hesa_spa_stats_rec.associate_ucas_number);
418:
419: EXCEPTION
420: WHEN OTHERS THEN
428: END IF;
429: -- Check whether the Associate Scottish Candidate has valid value
430: IF p_hesa_spa_stats_rec.associate_scott_cand IS NOT NULL THEN
431: BEGIN
432: igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_SCOTT_CAND',
433: column_value => p_hesa_spa_stats_rec.associate_scott_cand);
434: EXCEPTION
435: WHEN OTHERS THEN
436: FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
443: END IF;
444: -- Check whether the Associate Teaching Reference Number has valid value
445: IF p_hesa_spa_stats_rec.associate_teach_ref_num IS NOT NULL THEN
446: BEGIN
447: igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_TEACH_REF_NUM',
448: column_value => p_hesa_spa_stats_rec.associate_teach_ref_num);
449: EXCEPTION
450: WHEN OTHERS THEN
451: FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
458: END IF;
459: -- Check whether the Associate NHS Registration Number has valid value
460: IF p_hesa_spa_stats_rec.associate_nhs_reg_num IS NOT NULL THEN
461: BEGIN
462: igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_NHS_REG_NUM',
463: column_value => p_hesa_spa_stats_rec.associate_nhs_reg_num);
464: EXCEPTION
465: WHEN OTHERS THEN
466: FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
542: -- 2. Validate the mandatory parameters
543: -- 3. Derive the required values based on input values
544: -- 4. Validate the database constraints
545: -- 5. Validate the business rules.
546: -- 6. Insert the record into OSS table(igs_he_st_spa_all)
547: --
548: -- If any of the above step validation/logic failed then the procudure returns
549: -- with appropriate message(s) and status.
550: --
564: x_msg_data OUT NOCOPY VARCHAR2) AS
565:
566: -- Derive HESA_ST_SPA_ID from sequence
567: CURSOR cur_hesa_st_spa_id IS
568: SELECT igs_he_st_spa_all_s.NEXTVAL
569: FROM dual;
570:
571: l_api_name CONSTANT VARCHAR2(30) := 'create_hesa_spa';
572: l_api_version CONSTANT NUMBER := 1.0;
574: l_validation_failed BOOLEAN := FALSE;
575: l_db_val_status VARCHAR2(1);
576: l_person_id hz_parties.party_id%TYPE;
577: l_version_number igs_en_stdnt_ps_att.version_number%TYPE;
578: l_hesa_st_spa_id igs_he_st_spa_all.hesa_st_spa_id%TYPE;
579:
580: BEGIN
581: -- Create save point
582: SAVEPOINT create_hesa_spa_pub;
661: l_validation_failed := TRUE;
662: END IF;
663: END IF;
664:
665: -- If no validation failed then insert the record into OSS table(Igs_He_St_Spa_all)
666: IF NOT l_validation_failed THEN
667:
668: -- get the hesa_st_spa_id from the sequence
669: OPEN cur_hesa_st_spa_id;
670: FETCH cur_hesa_st_spa_id INTO l_hesa_st_spa_id;
671: CLOSE cur_hesa_st_spa_id;
672:
673: --
674: -- Insert the HESA program statistics record into OSS table igs_he_st_spa_all
675: --
676: INSERT INTO igs_he_st_spa_all (
677: hesa_st_spa_id,
678: org_id,
672:
673: --
674: -- Insert the HESA program statistics record into OSS table igs_he_st_spa_all
675: --
676: INSERT INTO igs_he_st_spa_all (
677: hesa_st_spa_id,
678: org_id,
679: person_id,
680: course_cd,