[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igs_as_docproc_stup
WHERE trans_request_if_hold_ind = 'N';
SELECT 'X'
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 missing_acad_record_data_ind,
hold_release_of_final_grades,
fgrade_cal_type,
fgrade_seq_num,
person_id,
hold_for_grade_chg,
creation_date,
hold_degree_expected,
programs_on_file
FROM igs_as_ord_itm_int
WHERE item_number = p_item_number;
SELECT trans_request_if_hold_ind, hold_deliv_ind
FROM igs_as_docproc_stup;
SELECT 'X'
FROM igs_pe_persenc_effct
WHERE person_id = cp_person_id AND
s_encmb_effect_type IN (cst_trans_blk,cst_trans_blk1,cst_trans_blk2,cst_trans_blk3) AND
expiry_dt IS NULL AND
TRUNC(pen_start_dt) <= TRUNC(cp_creation_date);
SELECT course_cd, unit_cd, uoo_id
FROM igs_en_su_attempt
WHERE person_id = cp_person_id AND
unit_attempt_status IN (cst_completed,cst_enrolled) AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number ;
SELECT finalised_outcome_ind
FROM igs_as_suaoa_v
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
-- anilk, 22-Apr-2003, Bug# 2829262
uoo_id = cp_uoo_id AND
grading_period_cd = cst_final AND
finalised_outcome_ind = 'Y';
SELECT 'X'
FROM igs_as_chn_grd_req
WHERE person_id = cp_person_id AND
current_status = cst_in_progress AND
request_date < cp_creation_date;
SELECT 'X'
FROM igs_en_stdnt_ps_att_all
WHERE person_id = cp_person_id AND
course_cd = NVL(cp_course_cd, course_cd) AND
TRUNC(course_rqrmnts_complete_dt) BETWEEN TRUNC(igs_ge_date.igsdate(cp_creation_date)) AND TRUNC(SYSDATE);
SELECT 'X'
FROM igs_en_sca_v
WHERE person_id = cp_person_id AND
course_type = NVL(cp_course_type, course_type) AND
TRUNC(course_rqrmnts_complete_dt) BETWEEN TRUNC(igs_ge_date.igsdate(cp_creation_date)) AND TRUNC(SYSDATE);
SELECT user_name
FROM fnd_user
WHERE person_party_id = cp_person_id;
SELECT igs_as_wf_beas008_s.NEXTVAL
FROM dual;
l_parameter_list_t.delete;
SELECT
igs_as_wf_beas004_s.NEXTVAL
FROM
dual;
SELECT aoii.order_number order_number,
aoii.item_number item_number,
aoii.person_id person_id
FROM igs_as_ord_itm_int aoii
WHERE fnd_date.date_to_canonical(aoii.date_produced) = cp_date_produced
AND aoii.item_status = 'PROCESSED';
SELECT user_name
FROM fnd_user
WHERE person_party_id = cp_person_id;
SELECT count(1)
FROM WF_LOCAL_USER_ROLES
WHERE USER_NAME = cp_user_name
AND ROLE_NAME = cp_role_name
AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
AND ROLE_ORIG_SYSTEM_ID = 0;
SELECT request_type
FROM igs_as_order_hdr
WHERE order_number = cp_order_number;
PROCEDURE asp_update_order_doc (
p_item_number IN NUMBER,
p_test_mode IN VARCHAR2
) AS
/******************************************************************
Created By : Sameer Manglm
Date Created By : 22-AUG-2002
Purpose : Update Order Docs interface table with the item status
(IGS_AS_ORD_ITM_INT.ITEM_STATUS) to PROCESSED and
the line item status (IGS_AS_DOC_DETAILS.ITEM_STATUS)
to PROCESSED. Also if all the items are processed,
update the Order status (IGS_AS_ORDER_HDR.ORDER_STATUS)
to COMPLETED.
Remarks :
Change History
Who When What
******************************************************************/
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT 'x'
FROM igs_as_ord_itm_int aoii
WHERE aoii.item_number = cp_item_number;
SELECT dd.rowid, dd.*
FROM igs_as_doc_details dd
WHERE dd.item_number = cp_item_number
AND NVL (missing_acad_record_data_ind, 'N') = 'N';
SELECT oh.rowid, oh.*
FROM igs_as_order_hdr oh
WHERE oh.order_status = 'INPROCESS'
AND NOT EXISTS (SELECT 'x'
FROM igs_as_doc_details dd
WHERE dd.order_number = oh.order_number
AND dd.item_status <> 'PROCESSED');
UPDATE igs_as_ord_itm_int
SET item_status = 'PROCESSED',
date_produced = SYSDATE
WHERE item_number = p_item_number
AND NVL (missing_acad_record_data_ind, 'N') = 'N';
igs_as_doc_details_pkg.update_row (
x_rowid => rec_doc_details.rowid,
x_order_number => rec_doc_details.order_number,
x_document_type => rec_doc_details.document_type,
x_document_sub_type => rec_doc_details.document_sub_type,
x_item_number => rec_doc_details.item_number,
x_item_status => 'PROCESSED',
x_date_produced => SYSDATE,
x_incl_curr_course => rec_doc_details.incl_curr_course,
x_num_of_copies => rec_doc_details.num_of_copies,
x_comments => rec_doc_details.comments,
x_recip_pers_name => rec_doc_details.recip_pers_name,
x_recip_inst_name => rec_doc_details.recip_inst_name,
x_recip_addr_line_1 => rec_doc_details.recip_addr_line_1,
x_recip_addr_line_2 => rec_doc_details.recip_addr_line_2,
x_recip_addr_line_3 => rec_doc_details.recip_addr_line_3,
x_recip_addr_line_4 => rec_doc_details.recip_addr_line_4,
x_recip_city => rec_doc_details.recip_city,
x_recip_postal_code => rec_doc_details.recip_postal_code,
x_recip_state => rec_doc_details.recip_state,
x_recip_province => rec_doc_details.recip_province,
x_recip_county => rec_doc_details.recip_county,
x_recip_country => rec_doc_details.recip_country,
x_recip_fax_area_code => rec_doc_details.recip_fax_area_code,
x_recip_fax_country_code => rec_doc_details.recip_fax_country_code,
x_recip_fax_number => rec_doc_details.recip_fax_number,
x_delivery_method_type => rec_doc_details.delivery_method_type,
x_programs_on_file => rec_doc_details.programs_on_file,
x_missing_acad_record_data_ind => rec_doc_details.missing_acad_record_data_ind,
x_missing_academic_record_data => rec_doc_details.missing_academic_record_data,
x_send_transcript_immediately => rec_doc_details.send_transcript_immediately,
x_hold_release_of_final_grades => rec_doc_details.hold_release_of_final_grades,
x_fgrade_cal_type => rec_doc_details.fgrade_cal_type,
x_fgrade_seq_num => rec_doc_details.fgrade_seq_num,
x_hold_degree_expected => rec_doc_details.hold_degree_expected,
x_deghold_cal_type => rec_doc_details.deghold_cal_type,
x_deghold_seq_num => rec_doc_details.deghold_seq_num,
x_hold_for_grade_chg => rec_doc_details.hold_for_grade_chg,
x_special_instr => rec_doc_details.special_instr,
x_express_mail_type => rec_doc_details.express_mail_type,
x_express_mail_track_num => rec_doc_details.express_mail_track_num,
x_ge_certification => rec_doc_details.ge_certification,
x_external_comments => rec_doc_details.external_comments,
x_internal_comments => rec_doc_details.internal_comments,
x_dup_requested => rec_doc_details.dup_requested,
x_dup_req_date => rec_doc_details.dup_req_date,
x_dup_sent_date => rec_doc_details.dup_sent_date,
x_enr_term_cal_type => rec_doc_details.enr_term_cal_type,
x_enr_ci_sequence_number => rec_doc_details.enr_ci_sequence_number,
x_incl_attempted_hours => rec_doc_details.incl_attempted_hours,
x_incl_class_rank => rec_doc_details.incl_class_rank,
x_incl_progresssion_status => rec_doc_details.incl_progresssion_status,
x_incl_class_standing => rec_doc_details.incl_class_standing,
x_incl_cum_hours_earned => rec_doc_details.incl_cum_hours_earned,
x_incl_gpa => rec_doc_details.incl_gpa,
x_incl_date_of_graduation => rec_doc_details.incl_date_of_graduation,
x_incl_degree_dates => rec_doc_details.incl_degree_dates,
x_incl_degree_earned => rec_doc_details.incl_degree_earned,
x_incl_date_of_entry => rec_doc_details.incl_date_of_entry,
x_incl_drop_withdrawal_dates => rec_doc_details.incl_drop_withdrawal_dates,
x_incl_hrs_for_curr_term => rec_doc_details.incl_hrs_earned_for_curr_term,
x_incl_majors => rec_doc_details.incl_majors,
x_incl_last_date_of_enrollment => rec_doc_details.incl_last_date_of_enrollment,
x_incl_professional_licensure => rec_doc_details.incl_professional_licensure,
x_incl_college_affiliation => rec_doc_details.incl_college_affiliation,
x_incl_instruction_dates => rec_doc_details.incl_instruction_dates,
x_incl_usec_dates => rec_doc_details.incl_usec_dates,
x_incl_program_attempt => rec_doc_details.incl_program_attempt,
x_incl_attendence_type => rec_doc_details.incl_attendence_type,
x_incl_last_term_enrolled => rec_doc_details.incl_last_term_enrolled,
x_incl_ssn => rec_doc_details.incl_ssn,
x_incl_date_of_birth => rec_doc_details.incl_date_of_birth,
x_incl_disciplin_standing => rec_doc_details.incl_disciplin_standing,
x_incl_no_future_term => rec_doc_details.incl_no_future_term,
x_incl_acurat_till_copmp_dt => rec_doc_details.incl_acurat_till_copmp_dt,
x_incl_cant_rel_without_sign => rec_doc_details.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 => rec_doc_details.doc_fee_per_copy,
x_delivery_fee => rec_doc_details.delivery_fee,
x_recip_email => rec_doc_details.recip_email,
x_overridden_doc_delivery_fee => rec_doc_details.overridden_doc_delivery_fee,
x_overridden_document_fee => rec_doc_details.overridden_document_fee,
x_fee_overridden_by => rec_doc_details.fee_overridden_by,
x_fee_overridden_date => rec_doc_details.fee_overridden_date,
x_incl_department => rec_doc_details.incl_department,
x_incl_field_of_stdy => rec_doc_details.incl_field_of_stdy,
x_incl_attend_mode => rec_doc_details.incl_attend_mode,
x_incl_yop_acad_prd => rec_doc_details.incl_yop_acad_prd,
x_incl_intrmsn_st_end => rec_doc_details.incl_intrmsn_st_end,
x_incl_hnrs_lvl => rec_doc_details.incl_hnrs_lvl,
x_incl_awards => rec_doc_details.incl_awards,
x_incl_award_aim => rec_doc_details.incl_award_aim,
x_incl_acad_sessions => rec_doc_details.incl_acad_sessions,
x_incl_st_end_acad_ses => rec_doc_details.incl_st_end_acad_ses,
x_incl_hesa_num => rec_doc_details.incl_hesa_num,
x_incl_location => rec_doc_details.incl_location,
x_incl_program_type => rec_doc_details.incl_program_type,
x_incl_program_name => rec_doc_details.incl_program_name,
x_incl_prog_atmpt_stat => rec_doc_details.incl_prog_atmpt_stat,
x_incl_prog_atmpt_end => rec_doc_details.incl_prog_atmpt_end,
x_incl_prog_atmpt_strt => rec_doc_details.incl_prog_atmpt_strt,
x_incl_req_cmplete => rec_doc_details.incl_req_cmplete,
x_incl_expected_compl_dt => rec_doc_details.incl_expected_compl_dt,
x_incl_conferral_dt => rec_doc_details.incl_conferral_dt,
x_incl_thesis_title => rec_doc_details.incl_thesis_title,
x_incl_program_code => rec_doc_details.incl_program_code,
x_incl_program_ver => rec_doc_details.incl_program_ver,
x_incl_stud_no => rec_doc_details.incl_stud_no,
x_incl_surname => rec_doc_details.incl_surname,
x_incl_fore_name => rec_doc_details.incl_fore_name,
x_incl_prev_names => rec_doc_details.incl_prev_names,
x_incl_initials => rec_doc_details.incl_initials,
x_doc_purpose_code => rec_doc_details.doc_purpose_code,
x_plan_id => rec_doc_details.plan_id,
x_produced_by => rec_doc_details.produced_by,
x_person_id => rec_doc_details.person_id
);
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 => rec_order_hdr.rowid,
x_order_number => rec_order_hdr.order_number,
x_order_status => 'COMPLETED',
x_date_completed => SYSDATE,
x_person_id => rec_order_hdr.person_id,
x_addr_line_1 => rec_order_hdr.addr_line_1,
x_addr_line_2 => rec_order_hdr.addr_line_2,
x_addr_line_3 => rec_order_hdr.addr_line_3,
x_addr_line_4 => rec_order_hdr.addr_line_4,
x_city => rec_order_hdr.city,
x_state => rec_order_hdr.state,
x_province => rec_order_hdr.province,
x_county => rec_order_hdr.county,
x_country => rec_order_hdr.country,
x_postal_code => rec_order_hdr.postal_code,
x_email_address => rec_order_hdr.email_address,
x_phone_country_code => rec_order_hdr.phone_country_code,
x_phone_area_code => rec_order_hdr.phone_area_code,
x_phone_number => rec_order_hdr.phone_number,
x_phone_extension => rec_order_hdr.phone_extension,
x_fax_country_code => rec_order_hdr.fax_country_code,
x_fax_area_code => rec_order_hdr.fax_area_code,
x_fax_number => rec_order_hdr.fax_number,
x_delivery_fee => rec_order_hdr.delivery_fee,
x_order_fee => rec_order_hdr.order_fee,
x_request_type => rec_order_hdr.request_type,
x_submit_method => rec_order_hdr.submit_method,
x_invoice_id => rec_order_hdr.invoice_id,
x_mode => 'R',
x_order_description => rec_order_hdr.order_description,
x_order_placed_by => rec_order_hdr.order_placed_by
);
END asp_update_order_doc;
SELECT NVL (SUM (NVL (dtl.overridden_document_fee, dtl.doc_fee_per_copy)), 0)
INTO l_order_fee
FROM igs_as_doc_details dtl
WHERE order_number = p_order_number;
SELECT NVL (SUM (NVL (dtl.overridden_doc_delivery_fee, dtl.delivery_fee)), 0)
INTO l_order_del_fee
FROM igs_as_doc_details dtl
WHERE order_number = p_order_number;