[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lv_batch_type||
substr(lv_cycle_year,-1,1)||
RPAD(p_school_code,6,' ') ||
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') batch_id,
SYSDATE file_datetime
FROM igf_sl_dl_setup_v
WHERE ci_cal_type = p_cal_type
AND ci_sequence_number = p_cal_seq_num;
SELECT RPAD('DL HEADER',10)
||LPAD(TO_CHAR(p_rec_length),4,0)
||LPAD(p_mesg_class,8)
||RPAD(p_batch_id,23)
||RPAD(TO_CHAR(lv_file_datetime,'YYYYMMDD'),8)
||LPAD(TO_CHAR(lv_file_datetime,'HH24MISS'),6)
||RPAD(' ', 2)
||RPAD(' ', 8)
||RPAD(' ',2)
||RPAD('IGS1157',9)
||RPAD(' ',(p_rec_length-80)) h_record FROM DUAL;
igf_sl_dl_batch_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
X_dbth_id => p_dbth_id,
X_batch_id => p_batch_id,
X_message_class => p_mesg_class,
X_bth_creation_date => lv_file_datetime,
X_batch_rej_code => NULL,
X_end_date => NULL,
X_batch_type => lv_batch_type,
X_send_resp => 'S', -- Send File
X_status => 'Y' -- Status = Processed
);
SELECT RPAD('DL TRAILER',10)||
LPAD(TO_CHAR(p_rec_length),4,0)||
LPAD(TO_CHAR(p_num_of_rec),7,0)||
RPAD(' ', 5)||
RPAD(' ', 5)||
RPAD(' ', 5)||
RPAD(' ',44)||
RPAD(' ',(p_rec_length-80)) t_record FROM DUAL;
SELECT adisb.disb_date, NVL(adisb.disb_accepted_amt,adisb.disb_gross_amt) disb_gross_amt, adisb.fee_1,
adisb.int_rebate_amt interest_rebate,
adisb.disb_net_amt
FROM igf_aw_award awd,
igf_aw_awd_disb adisb,
igf_aw_fund_mast fmast,
igf_aw_fund_cat fcat,
igf_ap_fa_base_rec fabase
WHERE awd.award_id = p_award_id
AND adisb.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND fmast.fund_code = fcat.fund_code
AND awd.base_id = fabase.base_id
ORDER BY adisb.disb_num;
SELECT fa.person_id,
fa.base_id
FROM igf_ap_fa_base_rec fa, igf_sl_loans loan , igf_aw_award awd
WHERE fa.base_id = awd.base_id
AND awd.award_id = loan.award_id
AND loan.loan_number = cp_loan_number ;
SELECT loan_per_begin_date , loan_per_end_date
FROM igf_sl_loans
WHERE loan_number = cp_loan_number ;
SELECT cal_type, ci_sequence_number, ci_start_dt, ci_end_dt
FROM igs_ps_ofr_inst
WHERE course_cd = cp_course_cd
AND version_number = cp_ver_num
AND ci_start_dt <= cp_start_dt
AND ci_end_dt >= cp_end_dt ;
SELECT
loans.row_id,
loans.loan_id,
loans.loan_number,
loans.award_id,
awd.accepted_amt loan_amt_accepted,
loans.loan_per_begin_date,
loans.loan_per_end_date,
lor.orig_fee_perct,
lor.pnote_print_ind,
lor.s_default_status,
lor.p_default_status,
lor.p_person_id,
lor.grade_level_code,
lor.unsub_elig_for_heal,
lor.disclosure_print_ind,
lor.unsub_elig_for_depnt,
lor.pnote_batch_id,
lor.pnote_ack_date,
lor.pnote_mpn_ind,
lor.sch_cert_date,
fabase.base_id,
fabase.person_id student_id,
awd.accepted_amt,
isr.alien_reg_number,
isr.citizenship_status,
isr.dependency_status
FROM
igf_sl_loans loans,
igf_sl_lor lor,
igf_aw_award awd,
igf_aw_fund_mast fmast,
igf_aw_fund_cat fcat,
igf_ap_fa_base_rec fabase,
igf_ap_isir_matched isr
WHERE
fabase.ci_cal_type = cp_cal_type AND
fabase.ci_sequence_number = cp_seq_number AND
fabase.base_id = awd.base_id AND
awd.fund_id = fmast.fund_id AND
fabase.base_id = isr.base_id AND
isr.payment_isir = 'Y' AND
isr.system_record_type = 'ORIGINAL' AND
fcat.fund_code = fmast.fund_code AND
(fcat.fed_fund_code = cp_fed_fund_1 OR fcat.fed_fund_code = cp_fed_fund_2) AND
loans.award_id = awd.award_id AND
loans.loan_number LIKE NVL(cp_dl_loan_number,loans.loan_number) AND
loans.loan_id = lor.loan_id AND
loans.loan_status = cp_loan_status AND
loans.active = cp_active;
SELECT l_lor_details.award_id award_id,
l_lor_details.loan_id loan_id,
l_lor_details.loan_number loan_number,
RPAD(l_lor_details.loan_number,21) -- #1
||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn),9)
||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
||RPAD(NVL(UPPER(student_dtl_rec.p_permt_addr1)||' '||UPPER(student_dtl_rec.p_permt_addr2),' ') ,35)
||RPAD(NVL(UPPER(student_dtl_rec.p_permt_city) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_permt_state) ,' '), 2)
||RPAD(NVL(student_dtl_rec.p_permt_zip ,' '), 9)
||DECODE(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),10,0))
||RPAD(' ',22)
||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
||LPAD(' ', 1)
||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9)
||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
||LPAD(NVL(l_lor_details.grade_level_code ,' '), 1)
||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0)
||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD'),8)
||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD'),8) -- #20
||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
||RPAD(p_dl_batch_id,23) -- #101
||RPAD(NVL(l_lor_details.pnote_print_ind ,' '),1)
||RPAD(NVL(decode(l_lor_details.unsub_elig_for_depnt,
'Y',l_lor_details.unsub_elig_for_depnt,
' '),' '),1)
||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
||LPAD(' ', 9)
||RPAD(' ',12)
||RPAD(' ',16)
||RPAD(' ', 1)
||RPAD(' ', 1)
||LPAD(' ', 9) -- #110
||RPAD(' ', 8)
||RPAD(' ', 1)
||RPAD(NVL(p_school_code,' '),6)
||RPAD(' ', 5)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '||UPPER(student_dtl_rec.p_local_addr2) ,' '),35)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
||RPAD(' ', 32)
||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
||RPAD(' ',41) -- #124 to #143
||RPAD(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),8)
||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
||RPAD(NVL(decode(l_lor_details.unsub_elig_for_heal,
'Y',l_lor_details.unsub_elig_for_heal,
' '),' '),1)
||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
'N',' ',
l_lor_details.disclosure_print_ind) ,' '),1)
||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
transaction_rec
FROM dual;
SELECT l_lor_details.award_id award_id,
l_lor_details.loan_id loan_id,
l_lor_details.loan_number loan_number,
RPAD(l_lor_details.loan_number,21) -- #1
||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(parent_dtl_rec.p_ssn),9)
||RPAD(NVL(UPPER(parent_dtl_rec.p_first_name),' ') ,12)
||RPAD(NVL(UPPER(parent_dtl_rec.p_last_name),' ') ,16)
||RPAD(NVL(UPPER(parent_dtl_rec.p_middle_name) ,' ') ,1)
||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_addr1)||' '||UPPER(parent_dtl_rec.p_permt_addr2),' ') ,35)
||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_city),' ') ,16)
||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_state),' ') ,2) -- Should be valid 2 digit code
||RPAD(NVL(parent_dtl_rec.p_permt_zip,' ') ,9)
||DECODE(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),10,0)) -- #10 ######## p_phone
||RPAD(' ',22)
||RPAD(NVL(TO_CHAR(parent_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
||LPAD(NVL(parent_dtl_rec.p_citizenship_status,' ') , 1)
||LPAD(NVL(parent_dtl_rec.p_alien_reg_num,' ') ,9)
||RPAD(NVL(l_lor_details.p_default_status,' ') ,1)
||LPAD(NVL(l_lor_details.grade_level_code,' ') ,1)
||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0) --Should >0 for anytype of LOAN
||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD') ,8)
||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD') ,8) -- #20
||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
||RPAD(p_dl_batch_id,23) -- #101
||RPAD(NVL(l_lor_details.pnote_print_ind ,' '), 1)
||RPAD(' ', 1) --unsub_elig_for_depnt is N/A for PLUS
||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn), 9)
||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
||RPAD(NVL(l_lor_details.citizenship_status ,' '), 1)
||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9) -- #110
||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
||RPAD(NVL(p_school_code ,' '), 6)
||LPAD(' ', 5)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '|| UPPER(student_dtl_rec.p_local_addr2),' '),35)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
||RPAD(' ', 32)
||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
||RPAD(' ',41) -- #124 to #143
||RPAD(NVL(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),' '),8)
||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
||RPAD(' ',1) -- for plus loans Additional Unsubsidized Health .. is N/A
||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
'N',' ',
l_lor_details.disclosure_print_ind) ,' '),1)
||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
transaction_rec
FROM dual;
SELECT l_lor_details.award_id award_id,
l_lor_details.loan_id loan_id,
l_lor_details.loan_number loan_number,
RPAD(l_lor_details.loan_number,21) -- #1
||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn),9)
||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
||RPAD(NVL(UPPER(student_dtl_rec.p_permt_addr1)||' '||UPPER(student_dtl_rec.p_permt_addr2),' ') ,35)
||RPAD(NVL(UPPER(student_dtl_rec.p_permt_city) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_permt_state) ,' '), 2)
||RPAD(NVL(student_dtl_rec.p_permt_zip ,' '), 9)
||DECODE(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),10,0))
||RPAD(' ', 22)-- Filler for ED use. Bug # 2758823
||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
||LPAD(' ', 1)
||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9)
||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
||LPAD(NVL(l_lor_details.grade_level_code ,' '), 1)
||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0)
||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD'),8)
||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD'),8) -- #20
||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
||RPAD(p_dl_batch_id,23) -- #101
||RPAD(NVL(l_lor_details.pnote_print_ind,' '),1)
||RPAD(NVL(decode(l_lor_details.unsub_elig_for_depnt,
'Y',l_lor_details.unsub_elig_for_depnt,
' '),' '),1)
||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
||LPAD(' ', 9)
||RPAD(' ',12)
||RPAD(' ',16)
||RPAD(' ', 1)
||RPAD(' ', 1)
||LPAD(' ', 9) -- #110
||RPAD(' ', 8)
||RPAD(' ', 1)
||RPAD(NVL(p_school_code,' '),6)
||RPAD(' ', 5) -- Filler for ED use.
||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '||UPPER(student_dtl_rec.p_local_addr2) ,' '),35)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
||RPAD(' ', 32)
||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
||RPAD(' ',41) -- #124 to #143
||RPAD(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),8)
||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
||RPAD(NVL(decode(l_lor_details.unsub_elig_for_heal,
'Y',l_lor_details.unsub_elig_for_heal,
' '),' '),1)
||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
'N',' ',
l_lor_details.disclosure_print_ind) ,' '),1)
||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
transaction_rec
FROM dual;
SELECT l_lor_details.award_id award_id,
l_lor_details.loan_id loan_id,
l_lor_details.loan_number loan_number,
RPAD(l_lor_details.loan_number,21) -- #1
||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(parent_dtl_rec.p_ssn),9)
||RPAD(NVL(UPPER(parent_dtl_rec.p_first_name),' ') ,12)
||RPAD(NVL(UPPER(parent_dtl_rec.p_last_name),' ') ,16)
||RPAD(NVL(UPPER(parent_dtl_rec.p_middle_name) ,' ') ,1)
||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_addr1)||' '||UPPER(parent_dtl_rec.p_permt_addr2),' ') ,35)
||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_city),' ') ,16)
||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_state),' ') ,2) -- Should be valid 2 digit code
||RPAD(NVL(parent_dtl_rec.p_permt_zip,' ') ,9)
||DECODE(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),10,0))
||RPAD(' ', 22)-- Filler for ED use. Bug # 2758823
||RPAD(NVL(TO_CHAR(parent_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
||LPAD(NVL(parent_dtl_rec.p_citizenship_status,' ') , 1)
||LPAD(NVL(parent_dtl_rec.p_alien_reg_num,' ') ,9)
||RPAD(NVL(l_lor_details.p_default_status,' ') ,1)
||LPAD(NVL(l_lor_details.grade_level_code,' ') ,1)
||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0) --Should >0 for anytype of LOAN
||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD') ,8)
||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD') ,8) -- #20
||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
||RPAD(p_dl_batch_id,23) -- #101
||RPAD(NVL(l_lor_details.pnote_print_ind ,' '), 1)
||RPAD(' ', 1) --unsub_elig_for_depnt is N/A for PLUS
||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn), 9)
||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
||RPAD(NVL(l_lor_details.citizenship_status ,' '), 1)
||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9) -- #110
||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
||RPAD(NVL(p_school_code ,' '), 6)
||RPAD(' ', 5) -- Filler for ED use
||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '|| UPPER(student_dtl_rec.p_local_addr2),' '),35)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
||RPAD(' ', 32)
||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
||RPAD(' ',41) -- #124 to #143
||RPAD(NVL(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),' '),8)
||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
||RPAD(' ',1) -- for plus loans Additional Unsubsidized Health .. is N/A
||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
'N',' ',
l_lor_details.disclosure_print_ind) ,' '),1)
||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
transaction_rec
FROM dual;