[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
inserting a record in the table IGS_AV_ADV_STANDING_ALL
*/
FUNCTION validate_adv_stnd
(
p_person_id IN igs_pe_person.person_id%type,
p_version_number IN igs_ps_ver.version_number%type,
p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
)
RETURN BOOLEAN;
inserting a record into the table IGS_AV_STND_UNIT_LVL_ALL
*/
FUNCTION validate_level
(
p_person_id IN igs_pe_person.person_id%type,
p_unit_level IN igs_ps_unit_level.unit_level%type,
p_cal_type IN igs_ca_inst.cal_type%type,
p_seq_number IN igs_ca_inst.sequence_number%type,
p_auth_pers_id IN igs_pe_person.person_id%type,
p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
p_qual_dets_id IN igs_uc_qual_dets.qual_dets_id%type,
p_course_version IN igs_ps_ver.version_number%type,
p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
)
RETURN BOOLEAN;
create_post_lvl function performs all the Post Insert business
validations on the table IGS_AV_STND_UNIT_LVL_ALL
*/
FUNCTION create_post_lvl
(
p_person_id IN igs_pe_person.person_id%type,
p_course_version IN igs_ps_ver.version_number%type,
p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
)
RETURN BOOLEAN;
before inserting into the table IGS_AV_STD_ULVLBASIS_ALL
*/
FUNCTION validate_lvl_bas_db_cons
(
p_person_id IN igs_pe_person.person_id%type,
p_av_stnd_unit_lvl_id IN igs_av_std_ulvlbasis_all.av_stnd_unit_lvl_id%type,
p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
)
RETURN VARCHAR2;
inserting into the table IGS_AV_STD_ULVLBASIS_ALL
*/
FUNCTION validate_lvl_bas
(
p_course_version IN igs_ps_ver.version_number%type,
p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
)
RETURN BOOLEAN;
l_check VARCHAR2(1) := 'N'; -- check whether to insert into parent
insert into IGS_AV_STD_ULVLBASIS_ALL
*/
INSERT INTO IGS_AV_STD_ULVLBASIS_ALL (
AV_STND_UNIT_LVL_ID,
BASIS_COURSE_TYPE,
BASIS_YEAR,
BASIS_COMPLETION_IND,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID
)
VALUES (
l_av_stnd_unit_lvl_id,
UPPER(p_lgcy_adstlvl_rec.basis_program_type),
p_lgcy_adstlvl_rec.basis_year,
p_lgcy_adstlvl_rec.basis_completion_ind,
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
igs_ge_gen_003.get_org_id
);
INSERT INTO IGS_AV_ADV_STANDING_ALL (
PERSON_ID,
COURSE_CD,
VERSION_NUMBER,
TOTAL_EXMPTN_APPROVED,
TOTAL_EXMPTN_GRANTED,
TOTAL_EXMPTN_PERC_GRNTD,
EXEMPTION_INSTITUTION_CD,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID
) VALUES (
l_person_id,
UPPER(p_lgcy_adstlvl_rec.program_cd),
l_as_version_number,
p_lgcy_adstlvl_rec.total_exmptn_approved,
p_lgcy_adstlvl_rec.total_exmptn_granted,
p_lgcy_adstlvl_rec.total_exmptn_perc_grntd,
UPPER(p_lgcy_adstlvl_rec.exemption_institution_cd),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
igs_ge_gen_003.get_org_id
);
insert into IGS_AV_STND_UNIT_LVL_ALL
*/
INSERT INTO IGS_AV_STND_UNIT_LVL_ALL (
PERSON_ID,
AS_COURSE_CD,
AS_VERSION_NUMBER,
S_ADV_STND_TYPE,
UNIT_LEVEL,
CRS_GROUP_IND,
EXEMPTION_INSTITUTION_CD,
S_ADV_STND_GRANTING_STATUS,
CREDIT_POINTS,
APPROVED_DT,
AUTHORISING_PERSON_ID,
GRANTED_DT,
EXPIRY_DT,
CANCELLED_DT,
REVOKED_DT,
COMMENTS,
AV_STND_UNIT_LVL_ID,
CAL_TYPE,
CI_SEQUENCE_NUMBER,
INSTITUTION_CD,
UNIT_DETAILS_ID,
TST_RSLT_DTLS_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ORG_ID,
DEG_AUD_DETAIL_ID,
QUAL_DETS_ID,
UNIT_LEVEL_MARK
) VALUES (
l_person_id,
UPPER(p_lgcy_adstlvl_rec.program_cd),
l_as_version_number,
l_s_adv_stnd_unit_level,
UPPER(p_lgcy_adstlvl_rec.unit_level),
NVL(UPPER(p_lgcy_adstlvl_rec.prog_group_ind),'N'),
UPPER(p_lgcy_adstlvl_rec.exemption_institution_cd),
UPPER(p_lgcy_adstlvl_rec.s_adv_stnd_granting_status),
p_lgcy_adstlvl_rec.credit_points,
p_lgcy_adstlvl_rec.approved_dt,
l_auth_pers_id,
p_lgcy_adstlvl_rec.granted_dt,
p_lgcy_adstlvl_rec.expiry_dt,
p_lgcy_adstlvl_rec.cancelled_dt,
p_lgcy_adstlvl_rec.revoked_dt,
p_lgcy_adstlvl_rec.comments,
l_av_stnd_unit_lvl_id,
l_cal_type,
l_sequence_number,
p_lgcy_adstlvl_rec.institution_cd,
l_unit_details_id,
l_tst_rslt_dtls_id,
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
igs_ge_gen_003.get_org_id,
NULL, -- aiyer NEW_REFERENCES.DEG_AUD_DETAIL_ID,
l_qual_dets_id,
p_lgcy_adstlvl_rec.UNIT_LEVEL_MARK
);
post insert validation create_post_lvl
*/
IF create_post_lvl
(
p_person_id => l_person_id,
p_course_version => l_as_version_number,
p_unit_details_id => l_unit_details_id,
p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
) THEN
l_return := validate_lvl_bas_db_cons
(
p_person_id => l_person_id,
p_av_stnd_unit_lvl_id => l_av_stnd_unit_lvl_id,
p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
);
insert into IGS_AV_STD_ULVLBASIS_ALL
*/
INSERT INTO IGS_AV_STD_ULVLBASIS_ALL (
AV_STND_UNIT_LVL_ID,
BASIS_COURSE_TYPE,
BASIS_YEAR,
BASIS_COMPLETION_IND,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID
) VALUES (
l_av_stnd_unit_lvl_id,
UPPER(p_lgcy_adstlvl_rec.basis_program_type),
p_lgcy_adstlvl_rec.basis_year,
p_lgcy_adstlvl_rec.basis_completion_ind,
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
igs_ge_gen_003.get_org_id
);
SELECT qual_dets_id
FROM igs_uc_qual_dets
WHERE person_id = cp_person_id
AND exam_level = cp_exam_level
AND subject_code = cp_subject_code
AND year = cp_year
AND sitting = cp_sitting
AND awarding_body = cp_awarding_body
AND approved_result = cp_approved_result;
SELECT ahv.unit_details_id
FROM igs_av_acad_history_v ahv,
igs_ad_term_details td
WHERE ahv.term_details_id = td.term_details_id
AND ahv.term=td.term
AND td.term = cp_prev_term
AND td.start_date = cp_start_date
AND td.end_date = cp_end_date
AND ahv.unit = cp_unit
AND ahv.person_id = cp_person_id
AND ahv.institution_code = cp_inst_cd;
SELECT b.tst_rslt_dtls_id
FROM igs_ad_test_results a,
igs_ad_tst_rslt_dtls b,
igs_ad_test_segments c
WHERE a.test_results_id = b.test_results_id
AND b.test_segment_id = c.test_segment_id
AND c.admission_test_type = cp_admission_test_type
AND a.admission_test_type = cp_admission_test_type
AND a.test_date = cp_test_date
AND c.test_segment_name = cp_test_segment_name
AND a.person_id = cp_person_id;
rules before inserting a record in the table
IGS_AV_ADV_STANDING_ALL
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
IS
x_return_status BOOLEAN;
SELECT deceased_ind
FROM igs_pe_hz_parties
WHERE party_id = cp_party_id;
SELECT 'x'
FROM hz_parties hp, igs_pe_hz_parties ihp
WHERE hp.party_id = ihp.party_id
AND ihp.inst_org_ind = 'I'
AND ihp.oi_govt_institution_cd IS NOT NULL
AND ihp.oi_institution_status = 'ACTIVE'
AND ihp.oss_org_unit_cd = cp_exemption_institution_cd;
SELECT ins.local_institution_ind
FROM igs_or_institution ins
WHERE ins.institution_cd = cp_ins_cd;
SELECT DECODE (cp_local_ind, 'N', NVL (cv.external_adv_stnd_limit, -1),
NVL (cv.internal_adv_stnd_limit, -1)) adv_stnd_limit
FROM igs_ps_ver cv
WHERE cv.course_cd = cp_course_cd
AND cv.version_number = cp_version_number;
SELECT 'x'
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT','UNCONFIRM','DISCONTIN','COMPLETED');
SELECT IGS_AV_STND_UNIT_LVL_S.NEXTVAL INTO p_av_stnd_unit_lvl_id FROM dual;
validations before inserting a record into the table
IGS_AV_STND_UNIT_LVL_ALL
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
IS
x_return_status BOOLEAN;
SELECT 'x'
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT','UNCONFIRM','DISCONTIN','COMPLETED');
Insert business validations on the table
IGS_AV_STND_UNIT_LVL_ALL
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
IS
x_return_status BOOLEAN;
update IGS_AV_ADV_STANDING_ALL with above obtained values for
total_exmptn_approved, total_exmptn_granted and total_exmptn_perc_grntd
*/
IF x_return_status THEN
UPDATE IGS_AV_ADV_STANDING_ALL
SET TOTAL_EXMPTN_APPROVED = l_total_exmptn_approved,
TOTAL_EXMPTN_GRANTED = l_total_exmptn_granted,
TOTAL_EXMPTN_PERC_GRNTD = l_total_exmptn_perc_grntd
WHERE PERSON_ID = p_person_id
AND COURSE_CD = p_lgcy_adstlvl_rec.program_cd
AND VERSION_NUMBER = p_course_version
AND EXEMPTION_INSTITUTION_CD = p_lgcy_adstlvl_rec.exemption_institution_cd;
data integrity validation before inserting
into the table IGS_AV_STD_ULVLBASIS_ALL
Know limitations, enhancements or remarks
Change History
Who When What
swaghmar 19-Oct-2005 Changed for bug# 4676359
(reverse chronological order - newest change first)
***************************************************************/
IS
x_return_status VARCHAR2(1);
validation before inserting into the table
IGS_AV_STD_ULVLBASIS_ALL
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
IS
x_return_status BOOLEAN;