[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM igs_av_unt_ref_cds
WHERE av_stnd_unit_id = p_av_stnd_unit_id
AND reference_code_id = p_reference_code_id;
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 trunc(td.start_date) = cp_start_date
AND trunc(td.end_date) = cp_end_date
AND ahv.unit = cp_unit
AND ahv.person_id = cp_person_id
AND ahv.institution_code = cp_institution_code ;
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 c.admission_test_type = cp_admission_test_type
AND b.test_segment_id = c.test_segment_id
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;
SELECT nvl(achievable_credit_points ,enrolled_credit_points) credit_points
FROM igs_ps_unit_ver
WHERE unit_cd=cp_unit_cd and version_number = cp_version_number ;
SELECT reference_code_id
FROM igs_ge_ref_cd
WHERE reference_cd_type = p_reference_cd_type
AND reference_cd = p_reference_cd;
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_s.nextval from dual ;
| inserting a record into the table IGS_AV_STND_UNIT_ALL and|
| keeps adding error message to stack as an when it encounters.|
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| p_lgcy_adstunt_rec |
| p_person_id |
| p_s_adv_stnd_type |
| p_cal_type |
| p_seq_number |
| p_auth_pers_number |
| p_unit_details_id |
| p_tst_rslt_dtls_id |
| p_as_version_number |
| |
| RETURNS : x_return_value |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| jhanda 11-11-2002 Created |
+===========================================================================*/
x_return_status BOOLEAN := TRUE;
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');
update IGS_AV_ADV_STANDING_ALL with above obtained values for
total_exmptn_approved, total_exmptn_granted and total_exmptn_perc_grntd
*/
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_adstunt_rec.program_cd
AND version_number = p_course_version
AND exemption_institution_cd = p_lgcy_adstunt_rec.exemption_institution_cd;
SELECT
av_stnd_unit_id
FROM
igs_av_stnd_unit_all
WHERE
person_id = cp_person_id AND
NVL(institution_cd,0) = NVL(cp_institution_cd,0) AND
NVL(tst_rslt_dtls_id,0) = NVL(cp_tst_rslt_dtls_id,0) AND
NVL(unit_details_id,0) = NVL(cp_unit_details_id,0) AND
unit_cd = cp_unit_cd AND
as_course_cd = cp_as_course_cd AND
as_version_number = cp_as_version_number ;
L_PROGRAM_UPDATE_DATE igs_av_stnd_unit_all.program_update_date%TYPE;
select IGS_AV_UNT_REF_CDS_S.nextval from dual;
mydebug('***** INSERT INTO IGS_AV_ADV_STANDING_ALL *****');
INSERT INTO igs_av_adv_standing_all(person_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
course_cd,
version_number,
total_exmptn_approved,
total_exmptn_granted,
total_exmptn_perc_grntd,
exemption_institution_cd ,
org_id
) VALUES (
l_person_id,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE ,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE ,
NVL(FND_GLOBAL.LOGIN_ID,-1),
upper(p_lgcy_adstunt_rec.program_cd) ,
l_as_version_number,
p_lgcy_adstunt_rec.total_exmptn_approved ,
p_lgcy_adstunt_rec.total_exmptn_granted,
p_lgcy_adstunt_rec.total_exmptn_perc_grntd,
p_lgcy_adstunt_rec.exemption_institution_cd ,
igs_ge_gen_003.get_org_id()
);
mydebug(' INSERT INTO IGS_AV_STND_UNIT_ALL N');
mydebug(' INSERT INTO IGS_AV_STND_UNIT_ALL lgcy_adstunt_rec.prog_group_ind ');
L_PROGRAM_UPDATE_DATE := NULL ;
L_PROGRAM_UPDATE_DATE := SYSDATE ;
INSERT INTO igs_av_stnd_unit_all(
person_id,
as_course_cd,
as_version_number,
s_adv_stnd_type,
unit_cd,
version_number,
s_adv_stnd_granting_status,
approved_dt,
authorising_person_id,
crs_group_ind,
exemption_institution_cd,
granted_dt,
expiry_dt,
cancelled_dt,
revoked_dt,
comments,
/* credit_percentage, */
s_adv_stnd_recognition_type,
org_id,
request_id,
program_application_id,
program_id,
program_update_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
av_stnd_unit_id,
cal_type,
ci_sequence_number,
institution_cd,
grading_schema_cd,
grd_sch_version_number,
grade,
achievable_credit_points,
deg_aud_detail_id,
unit_details_id,
tst_rslt_dtls_id
) VALUES (
l_person_id,
upper(p_lgcy_adstunt_rec.program_cd),
l_as_version_number,
upper(l_s_adv_stnd_type),
upper(p_lgcy_adstunt_rec.unit_cd),
p_lgcy_adstunt_rec.version_number,
upper(p_lgcy_adstunt_rec.s_adv_stnd_granting_status),
p_lgcy_adstunt_rec.approved_dt,
l_auth_pers_id,
upper(p_lgcy_adstunt_rec.prog_group_ind),
upper(p_lgcy_adstunt_rec.exemption_institution_cd),
p_lgcy_adstunt_rec.granted_dt,
p_lgcy_adstunt_rec.expiry_dt,
p_lgcy_adstunt_rec.cancelled_dt,
p_lgcy_adstunt_rec.revoked_dt,
p_lgcy_adstunt_rec.comments,
/* p_lgcy_adstunt_rec.credit_percentage, */
upper(p_lgcy_adstunt_rec.s_adv_stnd_recognition_type),
igs_ge_gen_003.get_org_id(),
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.LOGIN_ID,-1),
l_av_stnd_unit_lvl_id,
l_cal_type,
l_seq_number,
upper(p_lgcy_adstunt_rec.institution_cd),
p_lgcy_adstunt_rec.grading_schema_cd,
p_lgcy_adstunt_rec.grd_sch_version_number,
p_lgcy_adstunt_rec.grade,
p_lgcy_adstunt_rec.achievable_credit_points,
NULL ,
l_unit_details_id,
l_tst_rslt_dtls_id
);
mydebug(' Inserted into IGS_AV_STND_UNIT_ALL val AV_STND_UNIT_ID =' ||l_av_stnd_unit_lvl_id);
mydebug('INSERT INTO IGS_AV_STD_UNT_BASIS_ALL AV_STND_UNIT_ID= '|| l_av_stnd_unit_lvl_id);
INSERT INTO IGS_AV_UNT_REF_CDS(
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
AVU_REFERENCE_CD_ID,
PERSON_ID,
AV_STND_UNIT_ID,
REFERENCE_CODE_ID,
APPLIED_COURSE_CD,
DELETED_DATE
)
VALUES (
NVL(FND_GLOBAL.LOGIN_ID,-1),
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
l_AVU_REFERENCE_CD_ID ,
l_person_id,
l_av_stnd_unit_lvl_id,
l_REFERENCE_CODE_ID,
p_lgcy_adstunt_rec.APPLIED_PROGRAM_CD,
null
);
mydebug('INSERT INTO IGS_AV_STD_UNT_BASIS_ALL AV_STND_UNIT_ID= '|| l_av_stnd_unit_lvl_id);
INSERT INTO igs_av_std_unt_basis_all(
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
basis_course_type,
basis_year,
basis_completion_ind,
org_id,
av_stnd_unit_id
) VALUES (
NVL(FND_GLOBAL.LOGIN_ID,-1),
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
p_lgcy_adstunt_rec.basis_program_type ,
p_lgcy_adstunt_rec.basis_year,
p_lgcy_adstunt_rec.basis_completion_ind,
igs_ge_gen_003.get_org_id(),
l_av_stnd_unit_lvl_id
);
mydebug('**** INSERT INTO IGS_AV_STND_ALT_UNIT ');
INSERT INTO igs_av_stnd_alt_unit(
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
alt_unit_cd,
alt_version_number,
optional_ind,
av_stnd_unit_id
)
VALUES
(
NVL(FND_GLOBAL.LOGIN_ID,-1),
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
p_lgcy_adstunt_rec.alt_unit_cd,
p_lgcy_adstunt_rec.alt_version_number,
upper(p_lgcy_adstunt_rec.optional_ind),
l_av_stnd_unit_lvl_id
);