[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_into_enr_cart
(
p_return_status out NOCOPY varchar2,
p_msg_count out NOCOPY number,
p_msg_data out NOCOPY varchar2,
p_person_id in varchar2,
p_cal_type in varchar2,
p_ci_sequence_number in varchar2,
p_call_number in varchar2,
p_org_id in number
)
is
lv_count number := 0 ;
select count(*) into lv_count
from igs_ss_su_selection
where person_id = p_person_id
and call_number = p_call_number
and cal_type = p_cal_type
and ci_sequence_number = p_ci_sequence_number ;
insert into igs_ss_su_selection
(
usec_su_selection_id ,
person_id ,
unit_cd ,
version_number ,
cal_type ,
ci_sequence_number ,
location_cd ,
unit_class ,
uoo_id ,
call_number ,
enrolled_dt ,
enrolled_cp ,
grading_schema_cd ,
unit_attempt_status ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
org_id
)
select
igs_ss_su_selection_s.nextval,
p_person_id,
unit_cd,
version_number ,
cal_type ,
ci_sequence_number ,
location_cd ,
unit_class ,
uoo_id ,
call_number ,
sysdate ,
null ,
null ,
'CART' ,
p_person_id ,
sysdate ,
p_person_id ,
sysdate ,
p_person_id ,
p_org_id
from igs_ps_unit_ofr_opt
where cal_type = p_cal_type
and ci_sequence_number = p_ci_sequence_number
and call_number = p_call_number ;
fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
end insert_into_enr_cart;
PROCEDURE insert_into_enr_cart
(
p_return_status out NOCOPY varchar2,
p_msg_count out NOCOPY number,
p_msg_data out NOCOPY varchar2,
p_insert_flag out NOCOPY varchar2,
p_person_id in varchar2,
p_cal_type in varchar2,
p_ci_sequence_number in varchar2,
p_unit_cd in varchar2,
p_unit_class in varchar2,
p_org_id in number
)
is
lv_count number(2) := 0 ;
select
count(unit_cd)
into lv_count
from igs_ps_unit_ofr_opt
where cal_type = p_cal_type
and ci_sequence_number = p_ci_sequence_number
and lower(unit_cd) = lower(p_unit_cd)
and lower(unit_class) = lower(p_unit_class);
p_insert_flag := 'NA' ;
insert into igs_ss_su_selection
(
usec_su_selection_id ,
person_id ,
unit_cd ,
version_number ,
cal_type ,
ci_sequence_number ,
location_cd ,
unit_class ,
uoo_id ,
call_number ,
enrolled_dt ,
enrolled_cp ,
grading_schema_cd ,
unit_attempt_status ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login,
org_id
)
select
igs_ss_su_selection_s.nextval,
p_person_id,
unit_cd,
version_number ,
cal_type ,
ci_sequence_number ,
location_cd ,
unit_class ,
uoo_id ,
call_number ,
sysdate ,
null ,
null ,
'CART' ,
p_person_id ,
sysdate ,
p_person_id ,
sysdate ,
p_person_id,
p_org_id
from igs_ps_unit_ofr_opt
where cal_type = p_cal_type
and ci_sequence_number = p_ci_sequence_number
and upper(unit_cd) = upper(p_unit_cd)
and upper(unit_class) = upper(p_unit_class) ;
p_insert_flag := 'Y' ;
fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
p_insert_flag := 'N' ;
end insert_into_enr_cart;
PROCEDURE insert_into_enr_cart
(
p_return_status out NOCOPY varchar2,
p_msg_count out NOCOPY number,
p_msg_data out NOCOPY varchar2,
p_person_id in varchar2,
p_uoo_id in varchar2,
p_org_id in number
)
as
lv_count number := 0 ;
select count(*) into lv_count
from igs_ss_su_selection
where person_id = p_person_id
and uoo_id = p_uoo_id ;
insert into igs_ss_su_selection
(
usec_su_selection_id ,
person_id ,
unit_cd ,
version_number ,
cal_type ,
ci_sequence_number ,
location_cd ,
unit_class ,
uoo_id ,
call_number ,
enrolled_dt ,
enrolled_cp ,
grading_schema_cd ,
unit_attempt_status ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
org_id
)
select
igs_ss_su_selection_s.nextval,
p_person_id ,
a.unit_cd ,
a.version_number ,
a.cal_type ,
a.ci_sequence_number ,
a.location_cd ,
a.unit_class ,
a.uoo_id ,
a.call_number,
sysdate,
null,
null,
'CART',
p_person_id,
sysdate,
p_person_id,
sysdate,
p_person_id,
p_org_id
from igs_ps_unit_ofr_opt a
where uoo_id = p_uoo_id ;
fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
end insert_into_enr_cart;
delete from igs_ss_su_selection
where person_id = p_person_id
and uoo_id = p_uoo_id ;
fnd_message.set_name('IGS','IGS_SS_ENR_CART_NO_DELETE');
PROCEDURE insert_into_su_attempt
(
p_return_status out NOCOPY varchar2,
p_msg_count out NOCOPY number,
p_msg_data out NOCOPY varchar2,
p_org_id in number ,
p_person_id in varchar2,
p_course_cd in varchar2,
p_uoo_id in varchar2,
p_grading_schema in varchar2,
p_enrolled_cp in varchar2
)
is
l_course_cd varchar2(6) ;
SELECT
a.course_cd
into l_course_cd
from igs_en_stdnt_ps_att a
where nvl(a.course_attempt_status,' ') not in ('INACTIVE')
and person_id = p_person_id ;
insert into igs_en_su_attempt
(
org_id,
person_id,
course_cd,
unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
ci_start_dt,
ci_end_dt,
uoo_id,
unit_attempt_status,
no_assessment_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
override_achievable_cp
)
select
p_org_id,
p_person_id,
l_course_cd,
a.unit_cd,
a.version_number,
a.cal_type,
a.ci_sequence_number,
a.location_cd,
a.unit_class,
b.start_dt,
b.end_dt,
a.uoo_id,
'ENROLLED',
'N',
p_person_id,
sysdate,
p_person_id,
sysdate,
p_enrolled_cp
from igs_ps_unit_ofr_opt a,igs_ca_inst b
where a.uoo_id = p_uoo_id
and b.sequence_number = a.ci_sequence_number
and b.cal_type = a.cal_type ;
delete from igs_ss_su_selection
where person_id = p_person_id
and uoo_id = p_uoo_id ;
fnd_message.set_name('IGS','IGS_SS_ENR_CART_NO_DELETE');
end insert_into_su_attempt;
PROCEDURE delete_from_su_attempt
(
p_return_status out NOCOPY varchar2,
p_msg_count out NOCOPY number,
p_msg_data out NOCOPY varchar2,
p_org_id in number ,
p_person_id in varchar2,
p_course_cd in varchar2,
p_uoo_id in varchar2
)
is
begin
begin
delete from igs_en_su_attempt
where person_id = p_person_id
and course_cd = p_course_cd
and uoo_id = p_uoo_id
and org_id = p_org_id ;
fnd_message.set_name('IGS','IGS_SS_ENR_CART_NO_DELETE');
end delete_from_su_attempt;
SELECT academic_year_flag
FROM igs_en_ss_disp_stps;
SELECT ci.sequence_number||ci.cal_type
FROM igs_ca_inst ci,
igs_ca_inst_rel cir,
igs_ca_type ct,
igs_ca_stat cs
WHERE ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
ci.cal_type = ct.cal_type AND
ct.s_cal_cat = 'LOAD' AND
ci.cal_status = cs.cal_status AND
cs.s_cal_status = 'ACTIVE' AND
cir.sup_cal_type = cp_acad_cal_type AND
cir.sup_ci_sequence_number = cp_acad_ci_sequence_number
ORDER BY ci.start_dt;
SELECT cal_type
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id AND
course_cd = cp_program_cd;
SELECT academic_year_flag
FROM igs_en_ss_disp_stps;
SELECT ci.start_dt
FROM igs_ca_inst ci,
igs_ca_inst_rel cir,
igs_ca_type ct,
igs_ca_stat cs
WHERE ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
ci.cal_type = ct.cal_type AND
ct.s_cal_cat = 'LOAD' AND
ci.cal_status = cs.cal_status AND
cs.s_cal_status = 'ACTIVE' AND
cir.sup_cal_type = cp_acad_cal_type AND
cir.sup_ci_sequence_number = cp_acad_ci_sequence_number
ORDER BY ci.start_dt;
SELECT cal_type
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id AND
course_cd = cp_program_cd;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_code = p_lookup_code
AND lookup_type = p_lookup_type;
SELECT 'X'
FROM igs_ps_stdnt_trn
WHERE course_cd= p_dest_program_cd
AND transfer_course_cd = p_source_program_cd
AND person_id = p_person_id;
SELECT 'X'
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND uoo_id = p_uoo_id
AND unit_Attempt_status <> 'DROPPED';
SELECT 'X'
FROM igs_as_su_setatmpt
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND unit_set_cd = p_unit_set_cd
AND us_version_number = p_us_version_number;
SELECT sut.transfer_course_cd
FROM igs_ps_stdnt_unt_trn sut,
igs_en_su_attempt sua
WHERE sut.person_id = p_person_id
AND sua.person_id = sut.person_id
AND sut.uoo_id = p_uoo_id
AND sua.uoo_id = sut.uoo_id
AND sua.course_cd = sut.transfer_course_cd
AND sut.course_cd = p_course_cd
ORDER BY sut.transfer_dt desc;
SELECT description
FROM igs_ca_inst
WHERE cal_type = p_cal_type
AND sequence_number = p_sequence_number;
SELECT transfer_course_cd
FROM igs_ps_stdnt_unt_trn
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND uoo_id = p_uoo_id;
SELECT course_type
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver cv
WHERE sca.person_id = p_person_id
AND sca.course_cd = cp_program_cd
AND cv.course_cd = sca.course_cd
AND cv.version_number = sca.version_number;