[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ftci.fee_cal_type, ftci.fee_ci_sequence_number
FROM igs_fi_f_typ_ca_inst ftci,
igs_ca_da_inst_v daiv,
igs_ca_da_inst_v daiv1,
igs_fi_fee_type ft,
igs_fi_fee_str_stat stat
WHERE ftci.fee_type = ft.fee_type
AND ft.s_fee_type = 'DOCUMENT'
AND ftci.fee_type_ci_status = stat.fee_structure_status
AND stat.s_fee_structure_status = 'ACTIVE'
AND NVL (ft.closed_ind, 'N') = 'N'
AND ( daiv.dt_alias = ftci.start_dt_alias
AND daiv.sequence_number = ftci.start_dai_sequence_number
AND daiv.cal_type = ftci.fee_cal_type
AND daiv.ci_sequence_number = ftci.fee_ci_sequence_number
)
AND ( daiv1.dt_alias = ftci.end_dt_alias
AND daiv1.sequence_number = ftci.end_dai_sequence_number
AND daiv1.cal_type = ftci.fee_cal_type
AND daiv1.ci_sequence_number = ftci.fee_ci_sequence_number
)
AND SYSDATE BETWEEN daiv.alias_val
AND NVL (daiv1.alias_val, SYSDATE)
ORDER BY daiv.alias_val DESC;
SELECT 'X'
FROM igs_fi_wav_std_pgms
WHERE person_id = l_person_id
AND fee_cal_type = l_fee_cal_type
AND fee_ci_sequence_number = l_fee_ci_sequence_number
AND assignment_status_code = 'ACTIVE';
SELECT lifetime_trans_fee_ind,
provide_transcript_ind,
trans_request_if_hold_ind,
all_acad_hist_in_one_doc_ind,
hold_deliv_ind,
allow_enroll_cert_ind
FROM igs_as_docproc_stup;
SELECT encumbrance_type,
pee_start_dt
FROM igs_pe_persenc_effct
WHERE person_id = p_person_id
AND s_encmb_effect_type IN ('TRANS_BLK','RVK_SRVC','SUS_SRVC','RESULT_BLK')
AND NVL (expiry_dt, SYSDATE) >= SYSDATE
AND pee_start_dt < SYSDATE
ORDER BY pee_start_dt DESC;
SELECT lifetime_fee_paid
FROM igs_as_doc_fee_pmnt
WHERE person_id = p_person_id
AND document_type = 'TRANSCRIPT'
AND fee_paid_type = 'LIFETIME';
SELECT order_number,
lkup.meaning doc_type,
item_number,
item_status,
recip_inst_name,
recip_pers_name
FROM igs_as_doc_details dtls,
igs_lookups_view lkup
WHERE dtls.document_sub_type = lkup.lookup_code
AND lkup.lookup_type = 'IGS_AS_DOCUMENT_SUB_TYPE'
AND order_number = p_order_number
ORDER BY item_number; --msrinivi, for bug #2318474
SELECT order_number,
lkup.meaning doc_type,
item_number,
item_status,
recip_inst_name,
recip_pers_name,
recip_addr_line_1
|| ' '
|| recip_addr_line_2
|| ' '
|| recip_addr_line_3
|| ' '
|| recip_addr_line_4
|| ','
|| recip_city
|| ' '
|| recip_state
|| ', '
|| recip_country addr
FROM igs_as_doc_details dtls,
igs_lookups_view lkup
WHERE dtls.document_sub_type = lkup.lookup_code
AND lkup.lookup_type = 'IGS_AS_DOCUMENT_SUB_TYPE'
AND item_number = p_item_number
ORDER BY item_number; --msrinivi, for bug #2318474
SELECT dfs.lower_range lower_range,
dfs.upper_range upper_range,
dfs.payment_type payment_type,
dfs.amount amount
FROM igs_as_doc_fee_stup dfs
WHERE dfs.document_type = cp_document_type
AND l_start_at < upper_range
ORDER BY dfs.lower_range;
SELECT dlfs.amount amount
FROM igs_as_doc_dlvy_fee dlfs
WHERE dlfs.delivery_method_type = cp_delivery_method_type;
SELECT NVL (ltfp.lifetime_fee_paid, 'N') life_time_fee_paid
FROM igs_as_doc_fee_pmnt ltfp
WHERE ltfp.document_type = 'TRANSCRIPT'
AND fee_paid_type = 'LIFETIME'
AND ltfp.person_id = cp_person_id;
SELECT NVL (SUM (doc.num_of_copies), 0)
FROM igs_as_doc_details doc,
igs_as_order_hdr hdr
WHERE doc.person_id = p_person_id
AND doc.plan_id IS NULL
AND doc.item_number < cp_item_number
AND doc.document_type =
DECODE (
p_document_type,
'ENCERT', 'ENCERT',
'OFFICIAL', 'TRANSCRIPT',
'UNOFFICIAL', 'TRANSCRIPT',
'TRANSCRIPT')
AND hdr.order_number = doc.order_number
AND NVL (hdr.request_type, 'W') <> 'B';
SELECT NVL (SUM (num_of_copies), 0)
FROM igs_as_doc_details
WHERE person_id = p_person_id
AND item_number <> NVL (p_item_number, -1)
AND plan_id IN (SELECT plan_id
FROM igs_as_servic_plan
WHERE plan_type IN (SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
AND lookup_code = 'FREE_TRANSCRIPT'));
SELECT NVL (quantity_limit, 0)
FROM igs_as_servic_plan
WHERE plan_type IN (SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
AND lookup_code = 'FREE_TRANSCRIPT');
SELECT DECODE (
p_document_type,
'ENCERT', 'ENCERT',
'OFFICIAL', 'TRANSCRIPT',
'UNOFFICIAL', 'TRANSCRIPT',
'TRANSCRIPT')
INTO l_doc_type
FROM dual;
SELECT spa.commencement_dt
FROM igs_en_stdnt_ps_att_all spa,
igs_ps_ver_all pv
WHERE spa.person_id = cp_person_id
AND pv.course_type = cp_course_type
AND spa.course_cd = pv.course_cd
AND spa.version_number = pv.version_number
AND spa.course_attempt_status <> 'UNCONFIRM'
AND spa.commencement_dt IS NOT NULL
ORDER BY spa.commencement_dt ASC;
SELECT spa.course_rqrmnts_complete_dt
FROM igs_en_stdnt_ps_att_all spa,
igs_ps_ver_all pv
WHERE spa.person_id = cp_person_id
AND pv.course_type = cp_course_type
AND spa.course_cd = pv.course_cd
AND spa.version_number = pv.version_number
AND spa.course_attempt_status IN ('ENROLLED', 'COMPLETED')
ORDER BY spa.course_rqrmnts_complete_dt DESC;
SELECT (ord.delivery_fee + ord.order_fee) total_amount,
ord.*,
ord.ROWID row_id
FROM igs_as_order_hdr ord
WHERE order_number = cp_order_number;
SELECT invoice_amount_due
FROM igs_fi_inv_int_all
WHERE invoice_id = cp_inv;
SELECT ftci.fee_cal_type,
ftci.fee_ci_sequence_number,
ftci.fee_type,
ft.description description
FROM igs_fi_f_typ_ca_inst ftci,
igs_ca_da_inst_v daiv,
igs_ca_da_inst_v daiv1,
igs_fi_fee_type ft,
igs_fi_fee_str_stat stat
WHERE ftci.fee_type = ft.fee_type
AND ft.s_fee_type = 'DOCUMENT'
AND ftci.fee_type_ci_status = stat.fee_structure_status
AND stat.s_fee_structure_status = 'ACTIVE'
AND NVL (ft.closed_ind, 'N') = 'N'
AND (daiv.dt_alias = ftci.start_dt_alias
AND daiv.sequence_number = ftci.start_dai_sequence_number
AND daiv.cal_type = ftci.fee_cal_type
AND daiv.ci_sequence_number = ftci.fee_ci_sequence_number
)
AND (daiv1.dt_alias = ftci.end_dt_alias
AND daiv1.sequence_number = ftci.end_dai_sequence_number
AND daiv1.cal_type = ftci.fee_cal_type
AND daiv1.ci_sequence_number = ftci.fee_ci_sequence_number
)
AND SYSDATE BETWEEN daiv.alias_val AND NVL (daiv1.alias_val, SYSDATE)
ORDER BY daiv.alias_val DESC;
SELECT '1'
FROM igs_fi_invln_int_all
WHERE invoice_id = cp_invoice_id
AND NVL(error_account,'N') = 'Y'
AND ROWNUM < 2;
igs_as_order_hdr_pkg.update_row (
x_mode => 'R',
x_rowid => order_hdr_rec.row_id,
x_order_number => order_hdr_rec.order_number,
x_order_status => order_hdr_rec.order_status,
x_date_completed => order_hdr_rec.date_completed,
x_person_id => order_hdr_rec.person_id,
x_addr_line_1 => order_hdr_rec.addr_line_1,
x_addr_line_2 => order_hdr_rec.addr_line_2,
x_addr_line_3 => order_hdr_rec.addr_line_3,
x_addr_line_4 => order_hdr_rec.addr_line_4,
x_city => order_hdr_rec.city,
x_state => order_hdr_rec.state,
x_province => order_hdr_rec.province,
x_county => order_hdr_rec.county,
x_country => order_hdr_rec.country,
x_postal_code => order_hdr_rec.postal_code,
x_email_address => order_hdr_rec.email_address,
x_phone_country_code => order_hdr_rec.phone_country_code,
x_phone_area_code => order_hdr_rec.phone_area_code,
x_phone_number => order_hdr_rec.phone_number,
x_phone_extension => order_hdr_rec.phone_extension,
x_fax_country_code => order_hdr_rec.fax_country_code,
x_fax_area_code => order_hdr_rec.fax_area_code,
x_fax_number => order_hdr_rec.fax_number,
x_delivery_fee => order_hdr_rec.delivery_fee,
x_order_fee => order_hdr_rec.order_fee,
x_request_type => order_hdr_rec.request_type,
x_submit_method => order_hdr_rec.submit_method,
x_invoice_id => p_invoice_id, -- this is the value that is being updated
x_return_status => p_return_status,
x_msg_data => p_msg_data,
x_msg_count => p_msg_count,
x_order_placed_by => order_hdr_rec.order_placed_by,
x_order_description => order_hdr_rec.order_description,
p_init_msg_list => FND_API.G_FALSE
);
igs_as_documents_api.update_document_details (
p_order_number => p_order_number,
p_item_number => NULL,
p_init_msg_list => fnd_api.g_false,
p_return_status => p_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data
);
SELECT NVL (bill_me_later_ind, 'N') bill_me_later_ind
FROM igs_as_docproc_stup;
SELECT 'Y' current_student
FROM igs_pe_person_types typ,
igs_pe_typ_instances_all inst
WHERE inst.person_id = cp_person_id
AND inst.person_type_code = typ.person_type_code
AND typ.system_type = 'STUDENT'
AND inst.start_date <= SYSDATE
AND NVL (inst.end_date, SYSDATE) >= SYSDATE;
PROCEDURE update_order_fee (
p_order_number NUMBER,
p_item_number NUMBER,
p_old_sub_doc_type VARCHAR2,
p_old_deliv_type VARCHAR2,
p_old_num_copies VARCHAR2,
p_new_sub_doc_type VARCHAR2,
p_new_deliv_type VARCHAR2,
p_new_num_copies VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_data OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER
) AS
CURSOR c_order IS
SELECT ROWID row_id,
hdr.*
FROM igs_as_order_hdr hdr
WHERE order_number = p_order_number;
SELECT SUM (NVL (overridden_document_fee, doc_fee_per_copy)),
SUM (NVL (overridden_doc_delivery_fee, delivery_fee))
FROM igs_as_doc_details
WHERE order_number = p_order_number;
igs_as_order_hdr_pkg.update_row (
x_msg_count => p_msg_count,
x_msg_data => p_msg_data,
x_return_status => p_return_status,
x_rowid => cur_order.row_id,
x_order_number => cur_order.order_number,
x_order_status => cur_order.order_status,
x_date_completed => cur_order.date_completed,
x_person_id => cur_order.person_id,
x_addr_line_1 => cur_order.addr_line_1,
x_addr_line_2 => cur_order.addr_line_2,
x_addr_line_3 => cur_order.addr_line_3,
x_addr_line_4 => cur_order.addr_line_4,
x_city => cur_order.city,
x_state => cur_order.state,
x_province => cur_order.province,
x_county => cur_order.county,
x_country => cur_order.country,
x_postal_code => cur_order.postal_code,
x_email_address => cur_order.email_address,
x_phone_country_code => cur_order.phone_country_code,
x_phone_area_code => cur_order.phone_area_code,
x_phone_number => cur_order.phone_number,
x_phone_extension => cur_order.phone_extension,
x_fax_country_code => cur_order.fax_country_code,
x_fax_area_code => cur_order.fax_area_code,
x_fax_number => cur_order.fax_number,
--X_LIFE_TIME_FEE_PAID => cur_order.LIFE_TIME_FEE_PAID,
x_delivery_fee => NVL (lndeliveryfee, 0),
x_order_fee => NVL (lndocfeepercopy, 0),
x_request_type => cur_order.request_type,
x_submit_method => cur_order.submit_method,
x_invoice_id => cur_order.invoice_id,
x_order_placed_by => cur_order.order_placed_by,
x_order_description => cur_order.order_description
);
END update_order_fee;
SELECT edi_transaction_handling,
edi_id_number,
edi_payment_method,
edi_payment_format,
edi_remittance_method,
edi_remittance_instruction,
edi_tp_header_id,
edi_ece_tp_location_code
FROM hz_contact_points cont,
igs_pe_hz_parties ipz,
hz_parties org
WHERE cont.owner_table_name = 'HZ_PARTIES'
AND cont.owner_table_id = org.party_id
AND ipz.party_id = org.party_id
AND ipz.oss_org_unit_cd = p_inst_code;
SELECT all_acad_hist_in_one_doc_ind
FROM igs_as_docproc_stup;
SELECT load_description
FROM igs_en_su_attempt,
igs_ca_teach_to_load_v
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DUPLICATE', 'DISCONTIN')
AND teach_cal_type = cal_type
AND teach_ci_sequence_number = ci_sequence_number
ORDER BY load_start_dt ASC;
SELECT load_description
FROM igs_en_su_attempt,
igs_ca_teach_to_load_v
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DUPLICATE', 'DISCONTIN')
AND teach_cal_type = cal_type
AND teach_ci_sequence_number = ci_sequence_number
ORDER BY load_start_dt DESC;
SELECT spaa.conferral_date
FROM igs_en_spa_awd_aim spaa,
igs_gr_graduand_all gr
WHERE spaa.person_id = p_person_id
AND spaa.course_cd = p_course_cd
AND gr.person_id = spaa.person_id
AND gr.course_cd = spaa.course_cd
AND gr.award_cd = spaa.award_cd
AND EXISTS (
SELECT 'X'
FROM igs_gr_stat grst
WHERE grst.graduand_status = gr.graduand_status
AND grst.s_graduand_status = 'GRADUATED');
PROCEDURE delete_order_and_items (
p_order_number IN igs_as_order_hdr.order_number%TYPE,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2
) AS
--
CURSOR c_items IS
SELECT ROWID
FROM igs_as_doc_details
WHERE order_number = p_order_number;
SELECT ROWID
FROM igs_as_order_hdr
WHERE order_number = p_order_number;
igs_as_doc_details_pkg.delete_row (
x_rowid => c_items_rec.ROWID,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data,
x_return_status => p_return_status
);
igs_as_order_hdr_pkg.delete_row (
x_rowid => c_order_rec.ROWID,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data,
x_return_status => p_return_status
);
DELETE igs_as_ord_itm_int
WHERE order_number = p_order_number;
END delete_order_and_items;
SELECT subs.person_id,
subs.fee_paid_date,
subs.plan_id,
subs.plan_discon_from,
NVL (subs.num_of_copies, 0) num_of_copies,
subs.cal_type,
subs.ci_sequence_number,
subs.prev_paid_plan,
subs.program_on_file,
pln.plan_type,
pln.unlimited_ind,
pln.quantity_limit,
pln.period_of_plan,
pln.total_periods_covered
FROM igs_as_doc_fee_pmnt subs,
igs_as_servic_plan pln
WHERE subs.plan_id = pln.plan_id
AND subs.person_id = p_person_id
AND subs.plan_id = cp_plan_id
AND NVL (plan_discon_from, SYSDATE + 1) > SYSDATE;
SELECT plan_id,
plan_type,
unlimited_ind,
quantity_limit,
period_of_plan,
total_periods_covered
FROM igs_as_servic_plan pl,
igs_lookups_view lk
WHERE pl.plan_type = lk.meaning
AND lk.lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
AND lk.lookup_code = 'FREE_TRANSCRIPT'
AND NVL (pl.closed_ind, 'N') = 'N';
SELECT dlfs.amount amount
FROM igs_as_doc_dlvy_fee dlfs
WHERE dlfs.delivery_method_type = cp_delivery_method_type;
SELECT plan_id
FROM igs_as_servic_plan pl,
igs_lookups_view lk
WHERE pl.plan_type = lk.meaning
AND lk.lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
AND lk.lookup_code = 'FREE_TRANSCRIPT'
AND NVL (pl.closed_ind, 'N') = 'N';
SELECT SUM (NVL (num_of_copies, 0))
INTO lncopies_availded
FROM igs_as_doc_fee_pmnt
WHERE person_id = p_person_id
AND plan_id = free_plan_rec.plan_id
AND program_on_file IN ('ALL', p_program_on_file);
SELECT NVL ((MONTHS_BETWEEN (SYSDATE, plan_subs.fee_paid_date) / 12), 0)
INTO lnyearspassed
FROM DUAL;
SELECT NVL (MONTHS_BETWEEN (plan_subs.fee_paid_date, SYSDATE), 0)
INTO lnyearspassed
FROM DUAL;
SELECT 1
INTO lninperd
FROM igs_ca_inst ci
WHERE SYSDATE BETWEEN ci.start_dt AND ci.end_dt
AND cal_type = plan_subs.cal_type
AND sequence_number = plan_subs.ci_sequence_number;
SELECT delivery_method_type
FROM igs_as_doc_dlvy_typ
WHERE s_delivery_method_type = 'MANUAL'
AND closed_ind = 'Y'
AND delivery_method_type = 'NONE';
SELECT dfs.amount amount
FROM igs_as_doc_fee_stup dfs
WHERE dfs.document_type = 'LIFE_TIME_TRANS';
SELECT igs_as_doc_details_s.NEXTVAL
INTO l_item_number
FROM DUAL;
igs_as_doc_details_pkg.insert_row (
x_rowid => l_itm_row_id,
x_order_number => p_order_number,
x_document_type => 'TRANSCRIPT',
x_document_sub_type => 'LIFE_TIME_TRANS',
x_item_number => l_item_number,
x_item_status => 'INCOMPLETE',
x_date_produced => NULL,
x_incl_curr_course => NULL,
x_num_of_copies => 1,
x_comments => NULL,
x_recip_pers_name => NULL,
x_recip_inst_name => NULL,
x_recip_addr_line_1 => 'N/A',
x_recip_addr_line_2 => NULL,
x_recip_addr_line_3 => NULL,
x_recip_addr_line_4 => NULL,
x_recip_city => NULL,
x_recip_postal_code => NULL,
x_recip_state => NULL,
x_recip_province => NULL,
x_recip_county => NULL,
x_recip_country => l_default_country,
x_recip_fax_area_code => NULL,
x_recip_fax_country_code => NULL,
x_recip_fax_number => NULL,
x_delivery_method_type => ldeliv_type,
x_programs_on_file => NULL,
x_missing_acad_record_data_ind => NULL,
x_missing_academic_record_data => NULL,
x_send_transcript_immediately => NULL,
x_hold_release_of_final_grades => NULL,
x_fgrade_cal_type => NULL,
x_fgrade_seq_num => NULL,
x_hold_degree_expected => NULL,
x_deghold_cal_type => NULL,
x_deghold_seq_num => NULL,
x_hold_for_grade_chg => NULL,
x_special_instr => NULL,
x_express_mail_type => NULL,
x_express_mail_track_num => NULL,
x_ge_certification => NULL,
x_external_comments => NULL,
x_internal_comments => NULL,
x_dup_requested => NULL,
x_dup_req_date => NULL,
x_dup_sent_date => NULL,
x_enr_term_cal_type => NULL,
x_enr_ci_sequence_number => NULL,
x_incl_attempted_hours => NULL,
x_incl_class_rank => NULL,
x_incl_progresssion_status => NULL,
x_incl_class_standing => NULL,
x_incl_cum_hours_earned => NULL,
x_incl_gpa => NULL,
x_incl_date_of_graduation => NULL,
x_incl_degree_dates => NULL,
x_incl_degree_earned => NULL,
x_incl_date_of_entry => NULL,
x_incl_drop_withdrawal_dates => NULL,
x_incl_hrs_for_curr_term => NULL,
x_incl_majors => NULL,
x_incl_last_date_of_enrollment => NULL,
x_incl_professional_licensure => NULL,
x_incl_college_affiliation => NULL,
x_incl_instruction_dates => NULL,
x_incl_usec_dates => NULL,
x_incl_program_attempt => NULL,
x_incl_attendence_type => NULL,
x_incl_last_term_enrolled => NULL,
x_incl_ssn => NULL,
x_incl_date_of_birth => NULL,
x_incl_disciplin_standing => NULL,
x_incl_no_future_term => NULL,
x_incl_acurat_till_copmp_dt => NULL,
x_incl_cant_rel_without_sign => NULL,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_doc_fee_per_copy => l_lifetime_fee,
x_delivery_fee => 0,
x_recip_email => NULL,
x_overridden_doc_delivery_fee => NULL,
x_overridden_document_fee => NULL,
x_fee_overridden_by => NULL,
x_fee_overridden_date => NULL,
x_incl_department => NULL,
x_incl_field_of_stdy => NULL,
x_incl_attend_mode => NULL,
x_incl_yop_acad_prd => NULL,
x_incl_intrmsn_st_end => NULL,
x_incl_hnrs_lvl => NULL,
x_incl_awards => NULL,
x_incl_award_aim => NULL,
x_incl_acad_sessions => NULL,
x_incl_st_end_acad_ses => NULL,
x_incl_hesa_num => NULL,
x_incl_location => NULL,
x_incl_program_type => NULL,
x_incl_program_name => NULL,
x_incl_prog_atmpt_stat => NULL,
x_incl_prog_atmpt_end => NULL,
x_incl_prog_atmpt_strt => NULL,
x_incl_req_cmplete => NULL,
x_incl_expected_compl_dt => NULL,
x_incl_conferral_dt => NULL,
x_incl_thesis_title => NULL,
x_incl_program_code => NULL,
x_incl_program_ver => NULL,
x_incl_stud_no => NULL,
x_incl_surname => NULL,
x_incl_fore_name => NULL,
x_incl_prev_names => NULL,
x_incl_initials => NULL,
x_doc_purpose_code => NULL,
x_plan_id => NULL
);
/*IGS_AS_DOC_FEE_PMNT_PKG.INSERT_ROW(
X_ROWID => l_fmnt_row_Id,
X_PERSON_ID => p_person_id,
X_DOCUMENT_TYPE => 'TRANSCRIPT',
X_FEE_PAID_TYPE => 'LIFETIME',
X_FEE_PAID_DATE => NULL,
X_LIFETIME_FEE_PAID => 'N',
X_FEE_AMOUNT => l_lifetime_fee,
X_FEE_RECORDED_DATE => SYSDATE,
X_FEE_RECORDED_BY => p_person_id
);*/
SELECT ord.ROWID row_id,
ord.*
FROM igs_as_order_hdr ord
WHERE person_id = p_person_id
AND order_status = 'INCOMPLETE';
SELECT dtl.ROWID row_id,
dtl.*
FROM igs_as_doc_details dtl
WHERE order_number = cp_order_number
AND document_type = 'TRANSCRIPT'
AND document_sub_type <> 'LIFE_TIME_TRANS'
AND item_status = 'INCOMPLETE';
SELECT a.*,
a.ROWID
FROM igs_as_doc_fee_pmnt a
WHERE person_id = p_person_id;
/*IGS_AS_DOC_FEE_PMNT_PKG.UPDATE_ROW(
X_ROWID => c_doc_fee_pmnt_rec.rowid,
X_PERSON_ID => p_person_id,
X_DOCUMENT_TYPE => c_doc_fee_pmnt_rec.document_type,
X_FEE_PAID_TYPE => c_doc_fee_pmnt_rec.fee_paid_type,
X_FEE_PAID_DATE => SYSDATE,
X_LIFETIME_FEE_PAID => 'Y',
X_FEE_AMOUNT => c_doc_fee_pmnt_rec.fee_amount,
X_FEE_RECORDED_DATE => c_doc_fee_pmnt_rec.fee_recorded_date,
X_FEE_RECORDED_BY => c_doc_fee_pmnt_rec.fee_recorded_by
);*/
igs_as_doc_details_pkg.update_row (
x_rowid => c_order_item_rec.row_id,
x_order_number => c_order_item_rec.order_number,
x_document_type => c_order_item_rec.document_type,
x_document_sub_type => c_order_item_rec.document_sub_type,
x_item_number => c_order_item_rec.item_number,
x_item_status => c_order_item_rec.item_status,
x_date_produced => c_order_item_rec.date_produced,
x_incl_curr_course => c_order_item_rec.incl_curr_course,
x_num_of_copies => c_order_item_rec.num_of_copies,
x_comments => c_order_item_rec.comments,
x_recip_pers_name => c_order_item_rec.recip_pers_name,
x_recip_inst_name => c_order_item_rec.recip_inst_name,
x_recip_addr_line_1 => c_order_item_rec.recip_addr_line_1,
x_recip_addr_line_2 => c_order_item_rec.recip_addr_line_2,
x_recip_addr_line_3 => c_order_item_rec.recip_addr_line_3,
x_recip_addr_line_4 => c_order_item_rec.recip_addr_line_4,
x_recip_city => c_order_item_rec.recip_city,
x_recip_postal_code => c_order_item_rec.recip_postal_code,
x_recip_state => c_order_item_rec.recip_state,
x_recip_province => c_order_item_rec.recip_province,
x_recip_county => c_order_item_rec.recip_county,
x_recip_country => c_order_item_rec.recip_country,
x_recip_fax_area_code => c_order_item_rec.recip_fax_area_code,
x_recip_fax_country_code => c_order_item_rec.recip_fax_country_code,
x_recip_fax_number => c_order_item_rec.recip_fax_number,
x_delivery_method_type => c_order_item_rec.delivery_method_type,
x_programs_on_file => c_order_item_rec.programs_on_file,
x_missing_acad_record_data_ind => c_order_item_rec.missing_acad_record_data_ind,
x_missing_academic_record_data => c_order_item_rec.missing_academic_record_data,
x_send_transcript_immediately => c_order_item_rec.send_transcript_immediately,
x_hold_release_of_final_grades => c_order_item_rec.hold_release_of_final_grades,
x_fgrade_cal_type => c_order_item_rec.fgrade_cal_type,
x_fgrade_seq_num => c_order_item_rec.fgrade_seq_num,
x_hold_degree_expected => c_order_item_rec.hold_degree_expected,
x_deghold_cal_type => c_order_item_rec.deghold_cal_type,
x_deghold_seq_num => c_order_item_rec.deghold_seq_num,
x_hold_for_grade_chg => c_order_item_rec.hold_for_grade_chg,
x_special_instr => c_order_item_rec.special_instr,
x_express_mail_type => c_order_item_rec.express_mail_type,
x_express_mail_track_num => c_order_item_rec.express_mail_track_num,
x_ge_certification => c_order_item_rec.ge_certification,
x_external_comments => c_order_item_rec.external_comments,
x_internal_comments => c_order_item_rec.internal_comments,
x_dup_requested => c_order_item_rec.dup_requested,
x_dup_req_date => c_order_item_rec.dup_req_date,
x_dup_sent_date => c_order_item_rec.dup_sent_date,
x_enr_term_cal_type => c_order_item_rec.enr_term_cal_type,
x_enr_ci_sequence_number => c_order_item_rec.enr_ci_sequence_number,
x_incl_attempted_hours => c_order_item_rec.incl_attempted_hours,
x_incl_class_rank => c_order_item_rec.incl_class_rank,
x_incl_progresssion_status => c_order_item_rec.incl_progresssion_status,
x_incl_class_standing => c_order_item_rec.incl_class_standing,
x_incl_cum_hours_earned => c_order_item_rec.incl_cum_hours_earned,
x_incl_gpa => c_order_item_rec.incl_gpa,
x_incl_date_of_graduation => c_order_item_rec.incl_date_of_graduation,
x_incl_degree_dates => c_order_item_rec.incl_degree_dates,
x_incl_degree_earned => c_order_item_rec.incl_degree_earned,
x_incl_date_of_entry => c_order_item_rec.incl_date_of_entry,
x_incl_drop_withdrawal_dates => c_order_item_rec.incl_drop_withdrawal_dates,
x_incl_hrs_for_curr_term => c_order_item_rec.incl_hrs_earned_for_curr_term,
x_incl_majors => c_order_item_rec.incl_majors,
x_incl_last_date_of_enrollment => c_order_item_rec.incl_last_date_of_enrollment,
x_incl_professional_licensure => c_order_item_rec.incl_professional_licensure,
x_incl_college_affiliation => c_order_item_rec.incl_college_affiliation,
x_incl_instruction_dates => c_order_item_rec.incl_instruction_dates,
x_incl_usec_dates => c_order_item_rec.incl_usec_dates,
x_incl_program_attempt => c_order_item_rec.incl_program_attempt,
x_incl_attendence_type => c_order_item_rec.incl_attendence_type,
x_incl_last_term_enrolled => c_order_item_rec.incl_last_term_enrolled,
x_incl_ssn => c_order_item_rec.incl_ssn,
x_incl_date_of_birth => c_order_item_rec.incl_date_of_birth,
x_incl_disciplin_standing => c_order_item_rec.incl_disciplin_standing,
x_incl_no_future_term => c_order_item_rec.incl_no_future_term,
x_incl_acurat_till_copmp_dt => c_order_item_rec.incl_acurat_till_copmp_dt,
x_incl_cant_rel_without_sign => c_order_item_rec.incl_cant_rel_without_sign,
x_mode => 'R',
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_doc_fee_per_copy => 0,
x_delivery_fee => c_order_item_rec.delivery_fee,
x_recip_email => c_order_item_rec.recip_email,
x_overridden_doc_delivery_fee => c_order_item_rec.overridden_doc_delivery_fee,
x_overridden_document_fee => c_order_item_rec.overridden_document_fee,
x_fee_overridden_by => c_order_item_rec.fee_overridden_by,
x_fee_overridden_date => c_order_item_rec.fee_overridden_date,
x_incl_department => c_order_item_rec.incl_department,
x_incl_field_of_stdy => c_order_item_rec.incl_field_of_stdy,
x_incl_attend_mode => c_order_item_rec.incl_attend_mode,
x_incl_yop_acad_prd => c_order_item_rec.incl_yop_acad_prd,
x_incl_intrmsn_st_end => c_order_item_rec.incl_intrmsn_st_end,
x_incl_hnrs_lvl => c_order_item_rec.incl_hnrs_lvl,
x_incl_awards => c_order_item_rec.incl_awards,
x_incl_award_aim => c_order_item_rec.incl_award_aim,
x_incl_acad_sessions => c_order_item_rec.incl_acad_sessions,
x_incl_st_end_acad_ses => c_order_item_rec.incl_st_end_acad_ses,
x_incl_hesa_num => c_order_item_rec.incl_hesa_num,
x_incl_location => c_order_item_rec.incl_location,
x_incl_program_type => c_order_item_rec.incl_program_type,
x_incl_program_name => c_order_item_rec.incl_program_name,
x_incl_prog_atmpt_stat => c_order_item_rec.incl_prog_atmpt_stat,
x_incl_prog_atmpt_end => c_order_item_rec.incl_prog_atmpt_end,
x_incl_prog_atmpt_strt => c_order_item_rec.incl_prog_atmpt_strt,
x_incl_req_cmplete => c_order_item_rec.incl_req_cmplete,
x_incl_expected_compl_dt => c_order_item_rec.incl_expected_compl_dt,
x_incl_conferral_dt => c_order_item_rec.incl_conferral_dt,
x_incl_thesis_title => c_order_item_rec.incl_thesis_title,
x_incl_program_code => c_order_item_rec.incl_program_code,
x_incl_program_ver => c_order_item_rec.incl_program_ver,
x_incl_stud_no => c_order_item_rec.incl_stud_no,
x_incl_surname => c_order_item_rec.incl_surname,
x_incl_fore_name => c_order_item_rec.incl_fore_name,
x_incl_prev_names => c_order_item_rec.incl_prev_names,
x_incl_initials => c_order_item_rec.incl_initials,
x_doc_purpose_code => c_order_item_rec.doc_purpose_code,
x_plan_id => c_order_item_rec.plan_id,
x_produced_by => c_order_item_rec.produced_by,
x_person_id => c_order_item_rec.person_id
);
SELECT NVL (SUM (doc_fee_per_copy), 0),
NVL (SUM (delivery_fee), 0)
INTO l_doc_fee,
l_delivery_fee
FROM igs_as_doc_details
WHERE order_number = l_order_number;
igs_as_order_hdr_pkg.update_row (
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_rowid => c_order_rec.row_id,
x_order_number => c_order_rec.order_number,
x_order_status => c_order_rec.order_status,
x_date_completed => c_order_rec.date_completed,
x_person_id => c_order_rec.person_id,
x_addr_line_1 => c_order_rec.addr_line_1,
x_addr_line_2 => c_order_rec.addr_line_2,
x_addr_line_3 => c_order_rec.addr_line_3,
x_addr_line_4 => c_order_rec.addr_line_4,
x_city => c_order_rec.city,
x_state => c_order_rec.state,
x_province => c_order_rec.province,
x_county => c_order_rec.county,
x_country => c_order_rec.country,
x_postal_code => c_order_rec.postal_code,
x_email_address => c_order_rec.email_address,
x_phone_country_code => c_order_rec.phone_country_code,
x_phone_area_code => c_order_rec.phone_area_code,
x_phone_number => c_order_rec.phone_number,
x_phone_extension => c_order_rec.phone_extension,
x_fax_country_code => c_order_rec.fax_country_code,
x_fax_area_code => c_order_rec.fax_area_code,
x_fax_number => c_order_rec.fax_number,
x_delivery_fee => l_delivery_fee,
x_order_fee => l_doc_fee,
x_request_type => c_order_rec.request_type,
x_submit_method => c_order_rec.submit_method,
x_invoice_id => c_order_rec.invoice_id,
x_mode => 'R',
x_order_placed_by => c_order_rec.order_placed_by,
x_order_description => c_order_rec.order_description
);
fnd_message.set_token ('NAME', 'update_document_details: ' || SQLERRM);
EXECUTE IMMEDIATE 'SELECT cal_type, sequence_number, description
FROM
(SELECT
ci.cal_type,
ci.sequence_number,
ci.description,
NVL(
( SELECT MIN(daiv.alias_val)
FROM igs_ca_da_inst_v daiv,
igs_en_cal_conf secc
WHERE secc.s_control_num = 1 AND
daiv.cal_type = ci.cal_type AND
daiv.ci_sequence_number= ci.sequence_number AND
daiv.dt_alias = secc.load_effect_dt_alias),
ci.start_dt) load_effect_dt
FROM igs_ca_inst_all ci,
igs_ca_type cat,
igs_ca_stat cs
WHERE
ci.end_dt >= TRUNC(SYSDATE) AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = ''LOAD'' AND
cs.cal_status = ci.cal_status AND
cs.s_cal_status = ''ACTIVE'' AND
ci.ss_displayed = ''Y'' AND
cat.closed_ind = ''N''
ORDER BY load_effect_dt DESC ) dates
WHERE load_effect_dt <= SYSDATE
AND ROWNUM = 1 '
INTO p_cal_type,
p_sequence_number,
p_description;
EXECUTE IMMEDIATE 'select cal_type, sequence_number,description
from
(
SELECT
ci.cal_type,
ci.sequence_number,
ci.description,
ci.end_dt ,
NVL(
(
SELECT MIN(daiv.alias_val)
FROM igs_ca_da_inst_v daiv,
igs_en_cal_conf secc
WHERE secc.s_control_num = 1 AND
daiv.cal_type = ci.cal_type AND
daiv.ci_sequence_number= ci.sequence_number AND
daiv.dt_alias = secc.load_effect_dt_alias
),
ci.start_dt
) load_effect_dt
FROM igs_ca_inst_all ci,
igs_ca_type cat,
igs_ca_stat cs
WHERE
ci.end_dt >= TRUNC(SYSDATE) AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = ''LOAD'' AND
cs.cal_status = ci.cal_status AND
cs.s_cal_status = ''ACTIVE''
ORDER BY load_effect_dt ASC
) dates
where load_effect_dt > SYSDATE and rownum=1 '
INTO p_cal_type,
p_sequence_number,
p_description;
EXECUTE IMMEDIATE 'select cal_type, sequence_number,description
from
(
SELECT
ci.cal_type,
ci.sequence_number,
ci.description,
ci.end_dt ,
NVL(
(
SELECT MIN(daiv.alias_val)
FROM igs_ca_da_inst_v daiv,
igs_en_cal_conf secc
WHERE secc.s_control_num = 1 AND
daiv.cal_type = ci.cal_type AND
daiv.ci_sequence_number= ci.sequence_number AND
daiv.dt_alias = secc.load_effect_dt_alias
),
ci.start_dt
) load_effect_dt
FROM igs_ca_inst_all ci,
igs_ca_type cat,
igs_ca_stat cs
WHERE
ci.end_dt < TRUNC(SYSDATE) AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = ''LOAD'' AND
cs.cal_status = ci.cal_status AND
cs.s_cal_status = ''ACTIVE''
ORDER BY ci.end_dt DESC
) dates
where load_effect_dt <= SYSDATE and rownum=1'
INTO p_cal_type,
p_sequence_number,
p_description;
SELECT l.item_number,
l.order_number,
l.document_type,
l.num_of_copies,
l.delivery_method_type,
l.programs_on_file
FROM igs_as_doc_details l,
igs_as_order_hdr h
WHERE h.person_id = p_person_id
AND l.item_status = 'INCOMPLETE'
AND l.order_number = h.order_number
AND l.plan_id = p_plan_id;
SELECT l.item_number,
l.order_number,
l.document_type,
l.num_of_copies,
l.delivery_method_type,
l.programs_on_file
FROM igs_as_doc_details l,
igs_as_order_hdr h
WHERE h.person_id = p_person_id
AND l.item_status = 'INCOMPLETE'
AND l.order_number = h.order_number
AND l.plan_id IS NULL;
SELECT o.ROWID row_id,
o.*
FROM igs_as_order_hdr o
WHERE order_number = cp_order_number;
SELECT l.ROWID AS row_id,
l.*
FROM igs_as_doc_details l
WHERE l.item_number = cp_item_number;
SELECT plan_id,
plan_type,
unlimited_ind,
quantity_limit
FROM igs_as_servic_plan
WHERE plan_id = p_plan_id;
SELECT f.ROWID row_id,
f.*
FROM igs_as_doc_fee_pmnt f
WHERE person_id = p_person_id
AND plan_id = p_plan_id;
PROCEDURE update_order_for_recalc (p_order_number NUMBER, p_order_fee NUMBER) IS
BEGIN
OPEN cur_order (p_order_number);
igs_as_order_hdr_pkg.update_row (
x_msg_count => ln_msg_count,
x_msg_data => lv_msg_data,
x_return_status => lv_return_status,
x_rowid => order_rec.row_id,
x_order_number => order_rec.order_number,
x_order_status => order_rec.order_status,
x_date_completed => order_rec.date_completed,
x_person_id => order_rec.person_id,
x_addr_line_1 => order_rec.addr_line_1,
x_addr_line_2 => order_rec.addr_line_2,
x_addr_line_3 => order_rec.addr_line_3,
x_addr_line_4 => order_rec.addr_line_4,
x_city => order_rec.city,
x_state => order_rec.state,
x_province => order_rec.province,
x_county => order_rec.county,
x_country => order_rec.country,
x_postal_code => order_rec.postal_code,
x_email_address => order_rec.email_address,
x_phone_country_code => order_rec.phone_country_code,
x_phone_area_code => order_rec.phone_area_code,
x_phone_number => order_rec.phone_number,
x_phone_extension => order_rec.phone_extension,
x_fax_country_code => order_rec.fax_country_code,
x_fax_area_code => order_rec.fax_area_code,
x_fax_number => order_rec.fax_number,
x_delivery_fee => order_rec.delivery_fee,
x_order_fee => p_order_fee,
x_request_type => order_rec.request_type,
x_submit_method => order_rec.submit_method,
x_invoice_id => order_rec.invoice_id,
x_mode => 'R',
x_order_description => order_rec.order_description,
x_order_placed_by => order_rec.order_placed_by
);
END update_order_for_recalc;
PROCEDURE update_itm_for_recalc (p_item_number NUMBER, p_order_fee NUMBER, p_plan_id NUMBER) IS
BEGIN
OPEN cur_itm_dtls_upd (p_item_number);
igs_as_doc_details_pkg.update_row (
x_rowid => rec_dtls.row_id,
x_order_number => rec_dtls.order_number,
x_document_type => rec_dtls.document_type,
x_document_sub_type => rec_dtls.document_sub_type,
x_item_number => rec_dtls.item_number,
x_item_status => rec_dtls.item_status,
x_date_produced => rec_dtls.date_produced,
x_incl_curr_course => rec_dtls.incl_curr_course,
x_num_of_copies => rec_dtls.num_of_copies,
x_comments => rec_dtls.comments,
x_recip_pers_name => rec_dtls.recip_pers_name,
x_recip_inst_name => rec_dtls.recip_inst_name,
x_recip_addr_line_1 => rec_dtls.recip_addr_line_1,
x_recip_addr_line_2 => rec_dtls.recip_addr_line_2,
x_recip_addr_line_3 => rec_dtls.recip_addr_line_3,
x_recip_addr_line_4 => rec_dtls.recip_addr_line_4,
x_recip_city => rec_dtls.recip_city,
x_recip_postal_code => rec_dtls.recip_postal_code,
x_recip_state => rec_dtls.recip_state,
x_recip_province => rec_dtls.recip_province,
x_recip_county => rec_dtls.recip_county,
x_recip_country => rec_dtls.recip_country,
x_recip_fax_area_code => rec_dtls.recip_fax_area_code,
x_recip_fax_country_code => rec_dtls.recip_fax_country_code,
x_recip_fax_number => rec_dtls.recip_fax_number,
x_delivery_method_type => rec_dtls.delivery_method_type,
x_programs_on_file => rec_dtls.programs_on_file,
x_missing_acad_record_data_ind => rec_dtls.missing_acad_record_data_ind,
x_missing_academic_record_data => rec_dtls.missing_academic_record_data,
x_send_transcript_immediately => rec_dtls.send_transcript_immediately,
x_hold_release_of_final_grades => rec_dtls.hold_release_of_final_grades,
x_fgrade_cal_type => rec_dtls.fgrade_cal_type,
x_fgrade_seq_num => rec_dtls.fgrade_seq_num,
x_hold_degree_expected => rec_dtls.hold_degree_expected,
x_deghold_cal_type => rec_dtls.deghold_cal_type,
x_deghold_seq_num => rec_dtls.deghold_seq_num,
x_hold_for_grade_chg => rec_dtls.hold_for_grade_chg,
x_special_instr => rec_dtls.special_instr,
x_express_mail_type => rec_dtls.express_mail_type,
x_express_mail_track_num => rec_dtls.express_mail_track_num,
x_ge_certification => rec_dtls.ge_certification,
x_external_comments => rec_dtls.external_comments,
x_internal_comments => rec_dtls.internal_comments,
x_dup_requested => rec_dtls.dup_requested,
x_dup_req_date => rec_dtls.dup_req_date,
x_dup_sent_date => rec_dtls.dup_sent_date,
x_enr_term_cal_type => rec_dtls.enr_term_cal_type,
x_enr_ci_sequence_number => rec_dtls.enr_ci_sequence_number,
x_incl_attempted_hours => rec_dtls.incl_attempted_hours,
x_incl_class_rank => rec_dtls.incl_class_rank,
x_incl_progresssion_status => rec_dtls.incl_progresssion_status,
x_incl_class_standing => rec_dtls.incl_class_standing,
x_incl_cum_hours_earned => rec_dtls.incl_cum_hours_earned,
x_incl_gpa => rec_dtls.incl_gpa,
x_incl_date_of_graduation => rec_dtls.incl_date_of_graduation,
x_incl_degree_dates => rec_dtls.incl_degree_dates,
x_incl_degree_earned => rec_dtls.incl_degree_earned,
x_incl_date_of_entry => rec_dtls.incl_date_of_entry,
x_incl_drop_withdrawal_dates => rec_dtls.incl_drop_withdrawal_dates,
x_incl_hrs_for_curr_term => rec_dtls.incl_hrs_earned_for_curr_term,
x_incl_majors => rec_dtls.incl_majors,
x_incl_last_date_of_enrollment => rec_dtls.incl_last_date_of_enrollment,
x_incl_professional_licensure => rec_dtls.incl_professional_licensure,
x_incl_college_affiliation => rec_dtls.incl_college_affiliation,
x_incl_instruction_dates => rec_dtls.incl_instruction_dates,
x_incl_usec_dates => rec_dtls.incl_usec_dates,
x_incl_program_attempt => rec_dtls.incl_program_attempt,
x_incl_attendence_type => rec_dtls.incl_attendence_type,
x_incl_last_term_enrolled => rec_dtls.incl_last_term_enrolled,
x_incl_ssn => rec_dtls.incl_ssn,
x_incl_date_of_birth => rec_dtls.incl_date_of_birth,
x_incl_disciplin_standing => rec_dtls.incl_disciplin_standing,
x_incl_no_future_term => rec_dtls.incl_no_future_term,
x_incl_acurat_till_copmp_dt => rec_dtls.incl_acurat_till_copmp_dt,
x_incl_cant_rel_without_sign => rec_dtls.incl_cant_rel_without_sign,
x_mode => 'R',
x_return_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => ln_msg_count,
x_doc_fee_per_copy => p_order_fee,
x_delivery_fee => rec_dtls.delivery_fee,
x_recip_email => rec_dtls.recip_email,
x_overridden_doc_delivery_fee => rec_dtls.overridden_doc_delivery_fee,
x_overridden_document_fee => rec_dtls.overridden_document_fee,
x_fee_overridden_by => rec_dtls.fee_overridden_by,
x_fee_overridden_date => rec_dtls.fee_overridden_date,
x_incl_department => rec_dtls.incl_department,
x_incl_field_of_stdy => rec_dtls.incl_field_of_stdy,
x_incl_attend_mode => rec_dtls.incl_attend_mode,
x_incl_yop_acad_prd => rec_dtls.incl_yop_acad_prd,
x_incl_intrmsn_st_end => rec_dtls.incl_intrmsn_st_end,
x_incl_hnrs_lvl => rec_dtls.incl_hnrs_lvl,
x_incl_awards => rec_dtls.incl_awards,
x_incl_award_aim => rec_dtls.incl_award_aim,
x_incl_acad_sessions => rec_dtls.incl_acad_sessions,
x_incl_st_end_acad_ses => rec_dtls.incl_st_end_acad_ses,
x_incl_hesa_num => rec_dtls.incl_hesa_num,
x_incl_location => rec_dtls.incl_location,
x_incl_program_type => rec_dtls.incl_program_type,
x_incl_program_name => rec_dtls.incl_program_name,
x_incl_prog_atmpt_stat => rec_dtls.incl_prog_atmpt_stat,
x_incl_prog_atmpt_end => rec_dtls.incl_prog_atmpt_end,
x_incl_prog_atmpt_strt => rec_dtls.incl_prog_atmpt_strt,
x_incl_req_cmplete => rec_dtls.incl_req_cmplete,
x_incl_expected_compl_dt => rec_dtls.incl_expected_compl_dt,
x_incl_conferral_dt => rec_dtls.incl_conferral_dt,
x_incl_thesis_title => rec_dtls.incl_thesis_title,
x_incl_program_code => rec_dtls.incl_program_code,
x_incl_program_ver => rec_dtls.incl_program_ver,
x_incl_stud_no => rec_dtls.incl_stud_no,
x_incl_surname => rec_dtls.incl_surname,
x_incl_fore_name => rec_dtls.incl_fore_name,
x_incl_prev_names => rec_dtls.incl_prev_names,
x_incl_initials => rec_dtls.incl_initials,
x_doc_purpose_code => rec_dtls.doc_purpose_code,
x_plan_id => p_plan_id,
x_produced_by => rec_dtls.produced_by,
x_person_id => rec_dtls.person_id
);
END update_itm_for_recalc;
igs_as_doc_fee_pmnt_pkg.update_row (
x_rowid => rec_doc_fee_pmnt.row_id,
x_person_id => rec_doc_fee_pmnt.person_id,
x_fee_paid_date => rec_doc_fee_pmnt.fee_paid_date,
x_fee_amount => rec_doc_fee_pmnt.fee_amount,
x_fee_recorded_date => rec_doc_fee_pmnt.fee_recorded_date,
x_fee_recorded_by => rec_doc_fee_pmnt.fee_recorded_by,
x_mode => 'R',
x_plan_id => rec_doc_fee_pmnt.plan_id,
x_invoice_id => rec_doc_fee_pmnt.invoice_id,
x_plan_discon_from => SYSDATE,
x_plan_discon_by => p_admin_person_id,
x_num_of_copies => rec_doc_fee_pmnt.num_of_copies,
x_prev_paid_plan => rec_doc_fee_pmnt.prev_paid_plan,
x_cal_type => rec_doc_fee_pmnt.cal_type,
x_ci_sequence_number => rec_doc_fee_pmnt.ci_sequence_number,
x_program_on_file => rec_doc_fee_pmnt.program_on_file,
x_return_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => ln_msg_count
);
update_itm_for_recalc (uns_itm.item_number, uns_itm.order_number, NULL);
update_order_for_recalc (prev_order_number, ln_order_fee);
update_order_for_recalc (prev_order_number, ln_order_fee);
update_itm_for_recalc (subs_itm.item_number, subs_itm.order_number, ln_plan_id);
update_order_for_recalc (prev_order_number, ln_order_fee);
update_order_for_recalc (prev_order_number, ln_order_fee);
SELECT ftci.fee_cal_type,
ftci.fee_ci_sequence_number,
ftci.fee_type,
ft.description description
FROM igs_fi_f_typ_ca_inst ftci,
igs_ca_da_inst_v daiv,
igs_ca_da_inst_v daiv1,
igs_fi_fee_type ft,
igs_fi_fee_str_stat stat
WHERE ftci.fee_type = ft.fee_type
AND ft.s_fee_type = 'DOCUMENT'
AND ftci.fee_type_ci_status = stat.fee_structure_status
AND stat.s_fee_structure_status = 'ACTIVE'
AND NVL (ft.closed_ind, 'N') = 'N'
AND (daiv.dt_alias = ftci.start_dt_alias
AND daiv.sequence_number = ftci.start_dai_sequence_number
AND daiv.cal_type = ftci.fee_cal_type
AND daiv.ci_sequence_number = ftci.fee_ci_sequence_number
)
AND (daiv1.dt_alias = ftci.end_dt_alias
AND daiv1.sequence_number = ftci.end_dai_sequence_number
AND daiv1.cal_type = ftci.fee_cal_type
AND daiv1.ci_sequence_number = ftci.fee_ci_sequence_number
)
AND SYSDATE BETWEEN daiv.alias_val AND NVL (daiv1.alias_val, SYSDATE)
ORDER BY daiv.alias_val DESC;
PROCEDURE delete_bulk_item (
p_item_number IN NUMBER,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2
) AS
-- Delete allowed only if the item is
-- not in completed state
CURSOR c_chk_del_allwed IS
SELECT COUNT (*)
FROM igs_as_doc_details
WHERE item_number = p_item_number
AND item_status = 'PROCESSED';
SELECT ROWID
FROM igs_as_doc_details
WHERE item_number = p_item_number;
igs_as_doc_details_pkg.delete_row (
x_rowid => c_items_rec.ROWID,
x_return_status => p_return_status,
x_msg_data => p_msg_data,
x_msg_count => p_msg_count
);
DELETE igs_as_ord_itm_int
WHERE item_number = p_item_number;
fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
END delete_bulk_item;
SELECT document_type
FROM igs_as_doc_details
WHERE order_number = cp_order_number;
:= 'SELECT 1 FROM igs_as_ord_itm_int WHERE document_type = ''ENCERT'' AND item_number IN ('
|| p_item_numbers
|| ')';
:= 'SELECT 1 FROM igs_as_ord_itm_int WHERE document_type = ''TRANSCRIPT'' AND item_number IN ('
|| p_item_numbers
|| ')';
SELECT 1
FROM igs_as_ord_itm_int
WHERE document_type = cp_doc_type
AND item_number IN (cp_item_numbers);
SELECT party_name,
address1,
address2,
address3,
address4,
city,
state,
province,
postal_code,
county,
country,
email_address,
party_number
FROM hz_parties
WHERE party_id = p_person_id;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'PE_MIL_ASS_STATUS'
AND lookup_code = 'NA'
AND LANGUAGE = USERENV ('LANG')
AND view_application_id = 8405
AND security_group_id = 0;
igs_as_order_hdr_pkg.insert_row (
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_rowid => l_rowid,
x_order_number => l_order_number,
x_order_status => 'INPROCESS',
x_date_completed => NULL,
x_person_id => p_admin_person_id,
x_addr_line_1 => NVL (c_prsn_info_rec.address1, l_na_meaning),
x_addr_line_2 => c_prsn_info_rec.address2,
x_addr_line_3 => c_prsn_info_rec.address3,
x_addr_line_4 => c_prsn_info_rec.address4,
x_city => c_prsn_info_rec.city,
x_state => c_prsn_info_rec.state,
x_province => c_prsn_info_rec.province,
x_county => c_prsn_info_rec.county,
x_country => NVL (c_prsn_info_rec.country, l_na_meaning),
x_postal_code => c_prsn_info_rec.postal_code,
x_email_address => c_prsn_info_rec.email_address,
x_phone_country_code => NULL,
x_phone_area_code => NULL,
x_phone_number => NULL,
x_phone_extension => NULL,
x_fax_country_code => NULL,
x_fax_area_code => NULL,
x_fax_number => NULL,
x_delivery_fee => 0,
x_order_fee => 0,
x_request_type => 'B',
x_submit_method => NULL,
x_invoice_id => NULL,
x_mode => 'R',
x_order_description => p_order_desc,
x_order_placed_by => p_admin_person_id
);
igs_as_doc_details_pkg.insert_row (
x_rowid => l_rowid,
x_order_number => l_order_number,
x_document_type => 'TRANSCRIPT',
x_document_sub_type => p_trans_type,
x_item_number => l_item_number,
x_item_status => 'INPROCESS',
x_date_produced => NULL,
x_incl_curr_course => NULL,
x_num_of_copies => p_num_copies,
x_comments => NULL,
x_recip_pers_name => c_prsn_info_rec.party_name,
x_recip_inst_name => c_prsn_info_rec.party_number,
x_recip_addr_line_1 => NVL (c_prsn_info_rec.address1, l_na_meaning),
x_recip_addr_line_2 => c_prsn_info_rec.address2,
x_recip_addr_line_3 => c_prsn_info_rec.address3,
x_recip_addr_line_4 => c_prsn_info_rec.address4,
x_recip_city => c_prsn_info_rec.city,
x_recip_postal_code => c_prsn_info_rec.postal_code,
x_recip_state => c_prsn_info_rec.state,
x_recip_province => c_prsn_info_rec.province,
x_recip_county => c_prsn_info_rec.county,
x_recip_country => NVL (c_prsn_info_rec.country, l_na_meaning),
x_recip_fax_area_code => NULL,
x_recip_fax_country_code => NULL,
x_recip_fax_number => NULL,
x_delivery_method_type => p_deliv_meth,
x_programs_on_file => l_programs_on_file,
x_missing_acad_record_data_ind => NULL,
x_missing_academic_record_data => NULL,
x_send_transcript_immediately => NULL,
x_hold_release_of_final_grades => NULL,
x_fgrade_cal_type => NULL,
x_fgrade_seq_num => NULL,
x_hold_degree_expected => NULL,
x_deghold_cal_type => NULL,
x_deghold_seq_num => NULL,
x_hold_for_grade_chg => NULL,
x_special_instr => NULL,
x_express_mail_type => NULL,
x_express_mail_track_num => NULL,
x_ge_certification => NULL,
x_external_comments => NULL,
x_internal_comments => NULL,
x_dup_requested => NULL,
x_dup_req_date => NULL,
x_dup_sent_date => NULL,
x_enr_term_cal_type => NULL,
x_enr_ci_sequence_number => NULL,
x_incl_attempted_hours => NULL,
x_incl_class_rank => NULL,
x_incl_progresssion_status => NULL,
x_incl_class_standing => NULL,
x_incl_cum_hours_earned => NULL,
x_incl_gpa => NULL,
x_incl_date_of_graduation => NULL,
x_incl_degree_dates => NULL,
x_incl_degree_earned => NULL,
x_incl_date_of_entry => NULL,
x_incl_drop_withdrawal_dates => NULL,
x_incl_hrs_for_curr_term => NULL,
x_incl_majors => NULL,
x_incl_last_date_of_enrollment => NULL,
x_incl_professional_licensure => NULL,
x_incl_college_affiliation => NULL,
x_incl_instruction_dates => NULL,
x_incl_usec_dates => NULL,
x_incl_program_attempt => NULL,
x_incl_attendence_type => NULL,
x_incl_last_term_enrolled => NULL,
x_incl_ssn => NULL,
x_incl_date_of_birth => NULL,
x_incl_disciplin_standing => NULL,
x_incl_no_future_term => NULL,
x_incl_acurat_till_copmp_dt => NULL,
x_incl_cant_rel_without_sign => NULL,
x_mode => 'R',
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_doc_fee_per_copy => 0,
x_delivery_fee => 0,
x_recip_email => c_prsn_info_rec.email_address,
x_overridden_doc_delivery_fee => NULL,
x_overridden_document_fee => NULL,
x_fee_overridden_by => NULL,
x_fee_overridden_date => NULL,
x_incl_department => NULL,
x_incl_field_of_stdy => NULL,
x_incl_attend_mode => NULL,
x_incl_yop_acad_prd => NULL,
x_incl_intrmsn_st_end => NULL,
x_incl_hnrs_lvl => NULL,
x_incl_awards => NULL,
x_incl_award_aim => NULL,
x_incl_acad_sessions => NULL,
x_incl_st_end_acad_ses => NULL,
x_incl_hesa_num => NULL,
x_incl_location => NULL,
x_incl_program_type => NULL,
x_incl_program_name => NULL,
x_incl_prog_atmpt_stat => NULL,
x_incl_prog_atmpt_end => NULL,
x_incl_prog_atmpt_strt => NULL,
x_incl_req_cmplete => NULL,
x_incl_expected_compl_dt => NULL,
x_incl_conferral_dt => NULL,
x_incl_thesis_title => NULL,
x_incl_program_code => NULL,
x_incl_program_ver => NULL,
x_incl_stud_no => NULL,
x_incl_surname => NULL,
x_incl_fore_name => NULL,
x_incl_prev_names => NULL,
x_incl_initials => NULL,
x_doc_purpose_code => p_purpose,
x_plan_id => NULL,
x_produced_by => NULL,
x_person_id => l_person_id
);
INSERT INTO igs_as_ord_itm_int
(order_number, person_id, document_type, document_sub_type, item_number, item_status, date_produced,
num_of_copies, programs_on_file, comments, recip_pers_name, recip_inst_name, recip_addr_line_1,
recip_addr_line_2, recip_addr_line_3, recip_addr_line_4, recip_city,
recip_postal_code, recip_state, recip_province, recip_county,
recip_country, recip_fax_area_code, recip_fax_country_code, recip_fax_number, delivery_method_type,
dup_requested, dup_req_date, dup_sent_date, fgrade_cal_type, fgrade_seq_num, deghold_cal_type,
deghold_seq_num, hold_for_grade_chg, hold_degree_expected, hold_release_of_final_grades,
incl_curr_course, missing_acad_record_data_ind, missing_academic_record_data,
send_transcript_immediately, special_instr, express_mail_type, express_mail_track_num,
ge_certification, external_comments, internal_comments, enr_term_cal_type, enr_ci_sequence_number,
incl_attempted_hours, incl_class_rank, incl_progresssion_status, incl_class_standing,
incl_cum_hours_earned, incl_gpa, incl_date_of_graduation, incl_degree_dates, incl_degree_earned,
incl_date_of_entry, incl_drop_withdrawal_dates, incl_hrs_earned_for_curr_term, incl_majors,
incl_last_date_of_enrollment, incl_professional_licensure, incl_college_affiliation,
incl_instruction_dates, incl_usec_dates, incl_program_attempt, incl_attendence_type,
incl_last_term_enrolled, incl_ssn, incl_date_of_birth, incl_disciplin_standing, incl_no_future_term,
incl_acurat_till_copmp_dt, incl_cant_rel_without_sign, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, request_id, program_id, program_application_id,
program_update_date, recip_email)
VALUES (l_order_number, l_person_id, 'TRANSCRIPT', p_trans_type, l_item_number, 'INPROCESS', NULL,
p_num_copies, l_prog_cd, NULL, c_prsn_info_rec.party_name, NULL, c_prsn_info_rec.address1,
c_prsn_info_rec.address2, c_prsn_info_rec.address3, c_prsn_info_rec.address4, c_prsn_info_rec.city,
c_prsn_info_rec.postal_code, c_prsn_info_rec.state, c_prsn_info_rec.province, c_prsn_info_rec.county,
c_prsn_info_rec.country, NULL, NULL, NULL, p_deliv_meth,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.user_id, fnd_global.user_id, NULL, NULL, NULL,
NULL, c_prsn_info_rec.email_address);
fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
SELECT susa.unit_set_cd
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = p_person_id
AND susa.course_cd = p_course_cd
ORDER BY susa.selection_dt DESC;
SELECT COUNT (*)
FROM igs_as_doc_details
WHERE order_number = p_order_number
AND item_status = 'PROCESSED';