[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT description
FROM igs_fi_fee_type_all
WHERE fee_type = cp_v_fee_type
AND closed_ind = cp_closed_ind;
SELECT crtyp.description
FROM igs_fi_cr_types crtyp
WHERE credit_type_id = cp_n_credit_type_id;
SELECT crd.rowid,crd. *
FROM igs_fi_credits_all crd
WHERE crd.credit_id = cp_n_source_credit_id;
SELECT igs_fi_gen_007.get_sum_appl_amnt(appl.application_id) amount_applied,
appl.application_id,
appl.credit_id,
appl.invoice_id
FROM igs_fi_applications appl,
igs_fi_inv_int_all inv
WHERE appl.credit_id = cp_n_source_credit_id
AND appl.invoice_id = inv.invoice_id
AND appl.application_type = 'APP'
AND inv.transaction_type <> 'WAIVER_ADJ'
--Added for the Bug 4686711, to exclude those charges for which unapp record exists in application table.
AND NOT EXISTS (
SELECT 'X'
FROM IGS_FI_APPLICATIONS APPL2
WHERE APPL2.APPLICATION_TYPE = 'UNAPP'
AND APPL2.LINK_APPLICATION_ID = APPL.APPLICATION_ID
AND APPL2.AMOUNT_APPLIED = - APPL.AMOUNT_APPLIED)
ORDER BY appl.application_id;
SELECT fwp.fee_cal_type,
fwp.fee_ci_sequence_number,
fwp.waiver_name,
fwp.credit_type_id,
fwp.target_fee_type,
fwp.adjustment_fee_type
FROM igs_fi_waiver_pgms fwp
WHERE fwp.fee_cal_type = cp_fee_cal_type
AND fwp.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND fwp.waiver_name = cp_waiver_name;
p_v_string => 'Before the Callout to update the Credit api Table');
igs_fi_credits_pkg.update_row(x_rowid => l_cur_credit_rec_exists.rowid,
x_credit_id => l_cur_credit_rec_exists.credit_id,
x_credit_number => l_cur_credit_rec_exists.credit_number,
x_status => 'REVERSED',
x_credit_source => l_cur_credit_rec_exists.credit_source,
x_party_id => l_cur_credit_rec_exists.party_id,
x_credit_type_id => l_cur_credit_rec_exists.credit_type_id,
x_credit_instrument => l_cur_credit_rec_exists.credit_instrument,
x_description => l_cur_credit_rec_exists.description,
x_amount => l_cur_credit_rec_exists.amount,
x_currency_cd => l_cur_credit_rec_exists.currency_cd,
x_exchange_rate => l_cur_credit_rec_exists.exchange_rate,
x_transaction_date => l_cur_credit_rec_exists.transaction_date,
x_effective_date => l_cur_credit_rec_exists.effective_date,
x_reversal_date => TRUNC(SYSDATE),
x_reversal_reason_code => p_v_reversal_reason,
x_reversal_comments => p_v_reversal_comments,
x_unapplied_amount => l_cur_credit_rec_exists.unapplied_amount,
x_source_transaction_id => l_cur_credit_rec_exists.source_transaction_id,
x_receipt_lockbox_number => l_cur_credit_rec_exists.receipt_lockbox_number,
x_merchant_id => l_cur_credit_rec_exists.merchant_id,
x_credit_card_code => l_cur_credit_rec_exists.credit_card_code,
x_credit_card_holder_name => l_cur_credit_rec_exists.credit_card_holder_name,
x_credit_card_number => l_cur_credit_rec_exists.credit_card_number,
x_credit_card_expiration_date => l_cur_credit_rec_exists.credit_card_expiration_date,
x_credit_card_approval_code => l_cur_credit_rec_exists.credit_card_approval_code,
x_awd_yr_cal_type => l_cur_credit_rec_exists.awd_yr_cal_type,
x_awd_yr_ci_sequence_number => l_cur_credit_rec_exists.awd_yr_ci_sequence_number,
x_fee_cal_type => l_cur_credit_rec_exists.fee_cal_type,
x_fee_ci_sequence_number => l_cur_credit_rec_exists.fee_ci_sequence_number,
x_attribute_category => l_cur_credit_rec_exists.attribute_category,
x_attribute1 => l_cur_credit_rec_exists.attribute1,
x_attribute2 => l_cur_credit_rec_exists.attribute2,
x_attribute3 => l_cur_credit_rec_exists.attribute3,
x_attribute4 => l_cur_credit_rec_exists.attribute4,
x_attribute5 => l_cur_credit_rec_exists.attribute5,
x_attribute6 => l_cur_credit_rec_exists.attribute6,
x_attribute7 => l_cur_credit_rec_exists.attribute7,
x_attribute8 => l_cur_credit_rec_exists.attribute8,
x_attribute9 => l_cur_credit_rec_exists.attribute9,
x_attribute10 => l_cur_credit_rec_exists.attribute10,
x_attribute11 => l_cur_credit_rec_exists.attribute11,
x_attribute12 => l_cur_credit_rec_exists.attribute12,
x_attribute13 => l_cur_credit_rec_exists.attribute13,
x_attribute14 => l_cur_credit_rec_exists.attribute14,
x_attribute15 => l_cur_credit_rec_exists.attribute15,
x_attribute16 => l_cur_credit_rec_exists.attribute16,
x_attribute17 => l_cur_credit_rec_exists.attribute17,
x_attribute18 => l_cur_credit_rec_exists.attribute18,
x_attribute19 => l_cur_credit_rec_exists.attribute19,
x_attribute20 => l_cur_credit_rec_exists.attribute20,
x_gl_date => l_cur_credit_rec_exists.gl_date,
x_check_number => l_cur_credit_rec_exists.check_number,
x_source_transaction_type => l_cur_credit_rec_exists.source_transaction_type,
x_source_transaction_ref => l_cur_credit_rec_exists.source_transaction_ref,
x_credit_card_status_code => l_cur_credit_rec_exists.credit_card_status_code,
x_credit_card_payee_cd => l_cur_credit_rec_exists.credit_card_payee_cd,
x_credit_card_tangible_cd => l_cur_credit_rec_exists.credit_card_tangible_cd,
x_lockbox_interface_id => l_cur_credit_rec_exists.lockbox_interface_id,
x_batch_name => l_cur_credit_rec_exists.batch_name,
x_deposit_date => l_cur_credit_rec_exists.deposit_date,
x_source_invoice_id => l_cur_credit_rec_exists.source_invoice_id,
x_tax_year_code => l_cur_credit_rec_exists.tax_year_code,
x_waiver_name => l_cur_credit_rec_exists.waiver_name
);
SELECT crd. *
FROM igs_fi_credits_all crd
WHERE crd.credit_id = cp_n_source_credit_id;
SELECT igs_fi_gen_007.get_sum_appl_amnt(appl.application_id) amount_applied,
appl.application_id,
appl.credit_id,
appl.invoice_id
FROM igs_fi_applications appl,
igs_fi_inv_int_all inv
WHERE appl.credit_id = cp_n_source_credit_id
AND appl.invoice_id = inv.invoice_id
AND appl.application_type = 'APP'
AND inv.transaction_type <> 'WAIVER_ADJ'
--Added for the Bug 4686711, to exclude those charges for which unapp record exists in application table.
AND NOT EXISTS (
SELECT 'X'
FROM IGS_FI_APPLICATIONS APPL2
WHERE APPL2.APPLICATION_TYPE = 'UNAPP'
AND APPL2.LINK_APPLICATION_ID = APPL.APPLICATION_ID
AND APPL2.AMOUNT_APPLIED = - APPL.AMOUNT_APPLIED)
ORDER BY appl.application_id;
SELECT fwp.fee_cal_type,
fwp.fee_ci_sequence_number,
fwp.waiver_name,
fwp.waiver_method_code
FROM igs_fi_waiver_pgms fwp
WHERE fwp.target_fee_type = cp_v_fee_type
AND fwp.fee_cal_type = cp_v_fee_cal_type
AND fwp.fee_ci_sequence_number = cp_n_fee_ci_seq_number
AND fwp.waiver_method_code = 'COMP_RULE';
SELECT fwsp.waiver_name,
fwsp.person_id,
fwsp.assignment_status_code
FROM igs_fi_wav_std_pgms fwsp
WHERE fwsp.fee_cal_type = cp_v_fee_cal_type
AND fwsp.fee_ci_sequence_number = cp_n_fee_ci_seq_number
AND fwsp.waiver_name = cp_v_waiver_name
AND fwsp.person_id = cp_n_person_id
AND fwsp.assignment_status_code = 'ACTIVE';
SELECT 'X'
FROM igs_fi_f_typ_ca_inst_all
WHERE fee_type = cp_v_fee_type
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_dest_fee_ci_seq_number;
SELECT pgms.*
FROM igs_fi_waiver_pgms pgms
WHERE pgms.rowid = cp_rollover_rowid;
SELECT sup_waiver_name,
sub_waiver_name,
fee_cal_type,
fee_ci_sequence_number
FROM igs_fi_wav_pr_preqs
WHERE sub_waiver_name = cp_v_waiver_name
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_sequence_number;
SELECT 'x'
FROM igs_fi_waiver_pgms
WHERE waiver_name = cp_v_waiver_name
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_sequence_number;
SELECT stuwavpgm.*
FROM igs_fi_wav_std_pgms stuwavpgm
WHERE stuwavpgm.fee_cal_type = cp_v_fee_cal_type
AND stuwavpgm.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
AND stuwavpgm.waiver_name = cp_v_waiver_name
AND stuwavpgm.assignment_status_code ='ACTIVE';
p_v_string => 'After selecting the Waiver Program for Rollover');
igs_fi_waiver_pgms_pkg.insert_row(x_rowid => l_rowid,
x_fee_cal_type => l_cur_waiver_pgms.fee_cal_type,
x_fee_ci_sequence_number => p_n_dest_fee_ci_seq_number,
x_waiver_name => l_cur_waiver_pgms.waiver_name,
x_waiver_desc => l_cur_waiver_pgms.waiver_desc,
x_waiver_status_code => l_cur_waiver_pgms.waiver_status_code,
x_credit_type_id => l_cur_waiver_pgms.credit_type_id,
x_adjustment_fee_type => l_cur_waiver_pgms.adjustment_fee_type,
x_target_fee_type => l_cur_waiver_pgms.target_fee_type,
x_waiver_method_code => l_cur_waiver_pgms.waiver_method_code,
x_waiver_mode_code => l_cur_waiver_pgms.waiver_mode_code,
x_waiver_criteria_code => l_cur_waiver_pgms.waiver_criteria_code,
x_waiver_percent_alloc => l_cur_waiver_pgms.waiver_percent_alloc,
x_rule_fee_type => l_cur_waiver_pgms.rule_fee_type);
igs_fi_wav_pr_preqs_pkg.insert_row(x_rowid => l_rowid,
x_waiver_relation_id => l_waiver_relation_id,
x_fee_cal_type => l_cur_pre_req_wav_programs.fee_cal_type,
x_fee_ci_sequence_number => p_n_dest_fee_ci_seq_number,
x_sup_waiver_name => l_cur_pre_req_wav_programs.sup_waiver_name,
x_sub_waiver_name => l_cur_pre_req_wav_programs.sub_waiver_name,
x_mode => 'R');
igs_fi_wav_std_pgms_pkg.insert_row( x_rowid => l_rowid,
x_waiver_student_id => l_waiver_student_id,
x_fee_cal_type => l_cur_stud_waiver_assign.fee_cal_type,
x_fee_ci_sequence_number => p_n_dest_fee_ci_seq_number,
x_waiver_name => l_cur_stud_waiver_assign.waiver_name,
x_person_id => l_cur_stud_waiver_assign.person_id,
x_assignment_status_code => l_cur_stud_waiver_assign.assignment_status_code,
x_mode => 'R');
PROCEDURE update_wav_assign_status(
p_v_fee_cal_type IN VARCHAR2,
p_n_fee_ci_seq_number IN NUMBER,
p_v_waiver_name IN VARCHAR2,
p_v_new_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) AS
/******************************************************************
Created By : Anji Yedubati
Date Created By : 11-JUL-2005
Purpose : To update the Student Waiver Assignment Status
Created as part of Tuition Waivers Enhancment Bug # 3392095
Known limitations,enhancements,remarks:
Change History :
WHO WHEN WHAT
***************************************************************** */
CURSOR stdnt_wav_assgn_cur (
cp_fee_cal_type igs_fi_wav_std_pgms.fee_cal_type%TYPE,
cp_fee_ci_seq_number igs_fi_wav_std_pgms.fee_ci_sequence_number%TYPE,
cp_waiver_name igs_fi_wav_std_pgms.waiver_name%TYPE,
cp_new_status igs_fi_wav_std_pgms.assignment_status_code%TYPE) IS
SELECT fwsp.*, fwsp.ROWID
FROM igs_fi_wav_std_pgms fwsp
WHERE fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_seq_number
AND waiver_name = cp_waiver_name
AND assignment_status_code <> cp_new_status;
igs_fi_wav_std_pgms_pkg.update_row(
x_rowid => stdnt_wav_assgn_rec.ROWID,
x_waiver_student_id => stdnt_wav_assgn_rec.waiver_student_id,
x_fee_cal_type => stdnt_wav_assgn_rec.fee_cal_type,
x_fee_ci_sequence_number => stdnt_wav_assgn_rec.fee_ci_sequence_number,
x_waiver_name => stdnt_wav_assgn_rec.waiver_name,
x_person_id => stdnt_wav_assgn_rec.person_id,
x_assignment_status_code => p_v_new_status,
x_mode => 'R');
log_to_fnd(p_v_module => 'update_wav_assign_status.exception',
p_v_string => 'sqlerrm ' || SQLERRM );
fnd_log.string(fnd_log.level_exception,'igs.plsql.update_wav_assign_status.exception','sqlerrm ' || SQLERRM);
END update_wav_assign_status;
SELECT 'x'
FROM igs_fi_inv_int inv,
igs_fi_invln_int invln
WHERE inv.person_id = cp_n_person_id
AND inv.fee_type = cp_v_fee_type
AND inv.fee_cal_type = cp_v_fee_cal_type
AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
AND inv.invoice_id = invln.invoice_id
AND invln.error_account = 'Y';