[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
abshriva 30/11/05 Bug:4768071 - Modification made in procedure 'insert_1098t_data'
agairola 23/11/05 Bug:4747419 - Modified box45_credits and box45_charges
abshriva 9/11/05 Bug:4695680-Modification made in procedure'insert_1098t_data'
abshriva 26/10/05 Bug: 4697644-Modification made in procedure 'insert_1098t_data'
***************************************************************** */
g_v_label_tax_year igs_lookup_values.meaning%TYPE;
SELECT *
FROM igs_fi_1098t_setup
WHERE tax_year_name = cp_v_tax_year_name;
SELECT 'x'
FROM igs_fi_1098t_sfts
WHERE tax_year_name = cp_v_tax_year_name
AND sys_fund_type = cp_v_sys_fund_type;
PROCEDURE update_credits(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
p_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 06-May-2005
Purpose : Procedure for updating Credits
Known limitations,enhancements,remarks:
Change History
Who When What
agairola 05-Aug-2005 Changes as per Waiver build: 3392095
***************************************************************** */
-- Cursor for selecting all the records from the credits table for the person id
-- having the tax_year_name as the current tax year
CURSOR cur_crd(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
cp_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) IS
SELECT crd.rowid row_id,
crd.*
FROM igs_fi_credits_all crd
WHERE party_id = cp_n_person_id
AND tax_year_code = cp_v_tax_year
FOR UPDATE OF tax_year_code NOWAIT;
log_to_fnd(p_v_module => 'update_credits',
p_v_string => 'Updating Credit transactions');
igs_fi_credits_pkg.update_row(x_rowid => l_rec_crd.row_id,
x_credit_id => l_rec_crd.credit_id,
x_credit_number => l_rec_crd.credit_number,
x_status => l_rec_crd.status,
x_credit_source => l_rec_crd.credit_source,
x_party_id => l_rec_crd.party_id,
x_credit_type_id => l_rec_crd.credit_type_id,
x_credit_instrument => l_rec_crd.credit_instrument,
x_description => l_rec_crd.description,
x_amount => l_rec_crd.amount,
x_currency_cd => l_rec_crd.currency_cd,
x_exchange_rate => l_rec_crd.exchange_rate,
x_transaction_date => l_rec_crd.transaction_date,
x_effective_date => l_rec_crd.effective_date,
x_reversal_date => l_rec_crd.reversal_date,
x_reversal_reason_code => l_rec_crd.reversal_reason_code,
x_reversal_comments => l_rec_crd.reversal_comments,
x_unapplied_amount => l_rec_crd.unapplied_amount,
x_source_transaction_id => l_rec_crd.source_transaction_id,
x_receipt_lockbox_number => l_rec_crd.receipt_lockbox_number,
x_merchant_id => l_rec_crd.merchant_id,
x_credit_card_code => l_rec_crd.credit_card_code,
x_credit_card_holder_name => l_rec_crd.credit_card_holder_name,
x_credit_card_number => l_rec_crd.credit_card_number,
x_credit_card_expiration_date => l_rec_crd.credit_card_expiration_date,
x_credit_card_approval_code => l_rec_crd.credit_card_approval_code,
x_awd_yr_cal_type => l_rec_crd.awd_yr_cal_type,
x_awd_yr_ci_sequence_number => l_rec_crd.awd_yr_ci_sequence_number,
x_fee_cal_type => l_rec_crd.fee_cal_type,
x_fee_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
x_attribute_category => l_rec_crd.attribute_category,
x_attribute1 => l_rec_crd.attribute1,
x_attribute2 => l_rec_crd.attribute2,
x_attribute3 => l_rec_crd.attribute3,
x_attribute4 => l_rec_crd.attribute4,
x_attribute5 => l_rec_crd.attribute5,
x_attribute6 => l_rec_crd.attribute6,
x_attribute7 => l_rec_crd.attribute7,
x_attribute8 => l_rec_crd.attribute8,
x_attribute9 => l_rec_crd.attribute9,
x_attribute10 => l_rec_crd.attribute10,
x_attribute11 => l_rec_crd.attribute11,
x_attribute12 => l_rec_crd.attribute12,
x_attribute13 => l_rec_crd.attribute13,
x_attribute14 => l_rec_crd.attribute14,
x_attribute15 => l_rec_crd.attribute15,
x_attribute16 => l_rec_crd.attribute16,
x_attribute17 => l_rec_crd.attribute17,
x_attribute18 => l_rec_crd.attribute18,
x_attribute19 => l_rec_crd.attribute19,
x_attribute20 => l_rec_crd.attribute20,
x_gl_date => l_rec_crd.gl_date,
x_check_number => l_rec_crd.check_number,
x_source_transaction_type => l_rec_crd.source_transaction_type,
x_source_transaction_ref => l_rec_crd.source_transaction_ref,
x_credit_card_status_code => l_rec_crd.credit_card_status_code,
x_credit_card_payee_cd => l_rec_crd.credit_card_payee_cd,
x_credit_card_tangible_cd => l_rec_crd.credit_card_tangible_cd,
x_lockbox_interface_id => l_rec_crd.lockbox_interface_id,
x_batch_name => l_rec_crd.batch_name,
x_deposit_date => l_rec_crd.deposit_date,
x_source_invoice_id => l_rec_crd.source_invoice_id,
x_tax_year_code => null,
x_waiver_name => l_rec_crd.waiver_name);
END update_credits;
PROCEDURE update_charges(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
p_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 06-May-2005
Purpose : Procedure for updating Charges
Known limitations,enhancements,remarks:
Change History
Who When What
skharida 26-Jun-2006 Bug 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
agairola 05-Aug-2005 Changes as per Waiver build: 3392095
***************************************************************** */
-- Cursor for selecting all the Charge records for the person and tax year.
CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
cp_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) IS
SELECT inv.rowid row_id, inv.*
FROM igs_fi_inv_int_all inv
WHERE person_id = cp_n_person_id
AND tax_year_code = cp_v_tax_year
FOR UPDATE OF tax_year_code NOWAIT;
log_to_fnd(p_v_module => 'update_charges',
p_v_string => 'Updating Charge transactions');
igs_fi_inv_int_pkg.update_row(x_rowid => l_rec_chg.row_id,
x_invoice_id => l_rec_chg.invoice_id,
x_person_id => l_rec_chg.person_id,
x_fee_type => l_rec_chg.fee_type,
x_fee_cat => l_rec_chg.fee_cat,
x_fee_cal_type => l_rec_chg.fee_cal_type,
x_fee_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
x_course_cd => l_rec_chg.course_cd,
x_attendance_mode => l_rec_chg.attendance_mode,
x_attendance_type => l_rec_chg.attendance_type,
x_invoice_amount_due => l_rec_chg.invoice_amount_due,
x_invoice_creation_date => l_rec_chg.invoice_creation_date,
x_invoice_desc => l_rec_chg.invoice_desc,
x_transaction_type => l_rec_chg.transaction_type,
x_currency_cd => l_rec_chg.currency_cd,
x_status => l_rec_chg.status,
x_attribute_category => l_rec_chg.attribute_category,
x_attribute1 => l_rec_chg.attribute1,
x_attribute2 => l_rec_chg.attribute2,
x_attribute3 => l_rec_chg.attribute3,
x_attribute4 => l_rec_chg.attribute4,
x_attribute5 => l_rec_chg.attribute5,
x_attribute6 => l_rec_chg.attribute6,
x_attribute7 => l_rec_chg.attribute7,
x_attribute8 => l_rec_chg.attribute8,
x_attribute9 => l_rec_chg.attribute9,
x_attribute10 => l_rec_chg.attribute10,
x_invoice_amount => l_rec_chg.invoice_amount,
x_bill_id => l_rec_chg.bill_id,
x_bill_number => l_rec_chg.bill_number,
x_bill_date => l_rec_chg.bill_date,
x_waiver_flag => l_rec_chg.waiver_flag,
x_waiver_reason => l_rec_chg.waiver_reason,
x_effective_date => l_rec_chg.effective_date,
x_invoice_number => l_rec_chg.invoice_number,
x_exchange_rate => l_rec_chg.exchange_rate,
x_bill_payment_due_date => l_rec_chg.bill_payment_due_date,
x_optional_fee_flag => l_rec_chg.optional_fee_flag,
x_reversal_gl_date => l_rec_chg.reversal_gl_date,
x_tax_year_code => NULL,
x_waiver_name => l_rec_chg.waiver_name);
END update_charges;
SELECT stp.tax_year_name
FROM igs_fi_1098t_lps lps,
igs_fi_1098t_setup stp
WHERE lps.cal_type = cp_v_load_cal_type
AND lps.sequence_number = cp_n_load_ci_seq_num
AND lps.tax_year_name = stp.tax_year_name
AND TRUNC(stp.end_date) <= TRUNC(cp_d_end_date)
ORDER BY stp.end_date DESC;
SELECT group_cd
FROM igs_pe_persid_group_all
WHERE group_id = cp_n_person_grp_id;
SELECT 'x'
FROM igs_pe_person_base_v
WHERE person_id = cp_n_person_id;
SELECT 'x'
FROM igs_pe_persid_group_all
WHERE group_id = cp_n_pers_grp_id
AND closed_ind = cp_v_closed_ind;
SELECT 'x'
FROM igs_fi_1098t_lps
WHERE tax_year_name = cp_v_tax_year_name;
SELECT 'x'
FROM igs_fi_1098t_fts
WHERE tax_year_name = cp_v_tax_year_name;
SELECT awd.fund_id
FROM igf_db_awd_disb_dtl_all disb,
igf_aw_award_all awd
WHERE awd.award_id = disb.award_id
AND disb.sf_credit_id = cp_n_credit_id;
SELECT awd.fund_id
FROM igf_db_awd_disb_dtl_all disb,
igf_aw_award_all awd
WHERE awd.award_id = disb.award_id
AND disb.sf_invoice_num = cp_n_invoice_id;
SELECT fcat.sys_fund_type
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE fmast.fund_id = cp_n_fund_id
AND fmast.fund_code = fcat.fund_code;
SELECT altid.api_person_id,
altid.api_person_id_uf
FROM igs_pe_alt_pers_id altid,
igs_pe_person_id_typ pid
WHERE altid.pe_person_id = cp_n_person_id
AND altid.person_id_type = pid.person_id_type
AND pid.s_person_id_type = cp_v_s_person_id_type
AND sysdate BETWEEN altid.start_dt AND NVL(altid.end_dt, sysdate)
AND pid.closed_ind = 'N';
SELECT altid.api_person_id,
altid.api_person_id_uf
FROM igs_pe_alt_pers_id altid,
igs_pe_person_id_typ pid
WHERE altid.pe_person_id = cp_n_person_id
AND altid.person_id_type = pid.person_id_type
AND pid.s_person_id_type = cp_v_s_person_id_type
AND altid.person_id_type = cp_v_person_id_type
AND sysdate BETWEEN altid.start_dt AND NVL(altid.end_dt, sysdate)
AND pid.closed_ind = 'N';
SELECT crd.*,
crd.rowid row_id,
fts.fee_type,
inv.invoice_id
FROM igs_fi_credits_all crd,
igs_fi_cr_types_all crt,
igs_fi_1098t_fts fts,
igs_fi_inv_int_all inv,
igs_fi_applications app
WHERE crd.credit_type_id = crt.credit_type_id
AND crt.credit_class = 'CHGADJ'
AND crd.party_id = cp_n_person_id
AND inv.invoice_id = app.invoice_id
AND crd.credit_id = app.credit_id
AND inv.fee_type = fts.fee_type
AND fts.tax_year_name = cp_v_tax_year_name
AND TRUNC(crd.transaction_date) <= TRUNC(cp_d_end_date)
AND crd.tax_year_code IS NULL
ORDER BY crd.fee_ci_sequence_number;
SELECT 'x'
FROM igs_fi_inv_int_all inv,
igs_fi_1098t_setup stp
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 TRUNC(invoice_creation_date) < TRUNC(cp_d_start_date)
AND inv.tax_year_code IS NOT NULL
AND inv.tax_year_code = stp.tax_year_code
AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date);
igs_fi_credits_pkg.update_row(x_rowid => l_rec_crd.row_id,
x_credit_id => l_rec_crd.credit_id,
x_credit_number => l_rec_crd.credit_number,
x_status => l_rec_crd.status,
x_credit_source => l_rec_crd.credit_source,
x_party_id => l_rec_crd.party_id,
x_credit_type_id => l_rec_crd.credit_type_id,
x_credit_instrument => l_rec_crd.credit_instrument,
x_description => l_rec_crd.description,
x_amount => l_rec_crd.amount,
x_currency_cd => l_rec_crd.currency_cd,
x_exchange_rate => l_rec_crd.exchange_rate,
x_transaction_date => l_rec_crd.transaction_date,
x_effective_date => l_rec_crd.effective_date,
x_reversal_date => l_rec_crd.reversal_date,
x_reversal_reason_code => l_rec_crd.reversal_reason_code,
x_reversal_comments => l_rec_crd.reversal_comments,
x_unapplied_amount => l_rec_crd.unapplied_amount,
x_source_transaction_id => l_rec_crd.source_transaction_id,
x_receipt_lockbox_number => l_rec_crd.receipt_lockbox_number,
x_merchant_id => l_rec_crd.merchant_id,
x_credit_card_code => l_rec_crd.credit_card_code,
x_credit_card_holder_name => l_rec_crd.credit_card_holder_name,
x_credit_card_number => l_rec_crd.credit_card_number,
x_credit_card_expiration_date => l_rec_crd.credit_card_expiration_date,
x_credit_card_approval_code => l_rec_crd.credit_card_approval_code,
x_awd_yr_cal_type => l_rec_crd.awd_yr_cal_type,
x_awd_yr_ci_sequence_number => l_rec_crd.awd_yr_ci_sequence_number,
x_fee_cal_type => l_rec_crd.fee_cal_type,
x_fee_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
x_attribute_category => l_rec_crd.attribute_category,
x_attribute1 => l_rec_crd.attribute1,
x_attribute2 => l_rec_crd.attribute2,
x_attribute3 => l_rec_crd.attribute3,
x_attribute4 => l_rec_crd.attribute4,
x_attribute5 => l_rec_crd.attribute5,
x_attribute6 => l_rec_crd.attribute6,
x_attribute7 => l_rec_crd.attribute7,
x_attribute8 => l_rec_crd.attribute8,
x_attribute9 => l_rec_crd.attribute9,
x_attribute10 => l_rec_crd.attribute10,
x_attribute11 => l_rec_crd.attribute11,
x_attribute12 => l_rec_crd.attribute12,
x_attribute13 => l_rec_crd.attribute13,
x_attribute14 => l_rec_crd.attribute14,
x_attribute15 => l_rec_crd.attribute15,
x_attribute16 => l_rec_crd.attribute16,
x_attribute17 => l_rec_crd.attribute17,
x_attribute18 => l_rec_crd.attribute18,
x_attribute19 => l_rec_crd.attribute19,
x_attribute20 => l_rec_crd.attribute20,
x_gl_date => l_rec_crd.gl_date,
x_check_number => l_rec_crd.check_number,
x_source_transaction_type => l_rec_crd.source_transaction_type,
x_source_transaction_ref => l_rec_crd.source_transaction_ref,
x_credit_card_status_code => l_rec_crd.credit_card_status_code,
x_credit_card_payee_cd => l_rec_crd.credit_card_payee_cd,
x_credit_card_tangible_cd => l_rec_crd.credit_card_tangible_cd,
x_lockbox_interface_id => l_rec_crd.lockbox_interface_id,
x_batch_name => l_rec_crd.batch_name,
x_deposit_date => l_rec_crd.deposit_date,
x_source_invoice_id => l_rec_crd.source_invoice_id,
x_tax_year_code => g_rec_1098t_setup.tax_year_code,
x_waiver_name => l_rec_crd.waiver_name);
SELECT inv.*, inv.rowid row_id
FROM igs_fi_inv_int_all inv,
igs_fi_1098t_fts fts
WHERE inv.fee_type = fts.fee_type
AND inv.person_id = cp_n_person_id
AND fts.tax_year_name = cp_v_tax_year_name
AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_end_date)
AND inv.tax_year_code IS NULL
ORDER BY inv.fee_ci_sequence_number;
SELECT 'x'
FROM igs_fi_1098t_lps
WHERE tax_year_name = cp_v_tax_year_name
AND cal_type = cp_v_load_cal_type
AND sequence_number = cp_n_load_ci_seq
AND next_acad_flag = 'Y';
SELECT 'x'
FROM igs_fi_inv_int_all inv,
igs_fi_1098t_setup stp
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.tax_year_code IS NOT NULL
AND inv.tax_year_code = stp.tax_year_code
AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date)
AND TRUNC(invoice_creation_date) < TRUNC(cp_d_start_date);
igs_fi_inv_int_pkg.update_row(x_rowid => l_rec_chg.row_id,
x_invoice_id => l_rec_chg.invoice_id,
x_person_id => l_rec_chg.person_id,
x_fee_type => l_rec_chg.fee_type,
x_fee_cat => l_rec_chg.fee_cat,
x_fee_cal_type => l_rec_chg.fee_cal_type,
x_fee_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
x_course_cd => l_rec_chg.course_cd,
x_attendance_mode => l_rec_chg.attendance_mode,
x_attendance_type => l_rec_chg.attendance_type,
x_invoice_amount_due => l_rec_chg.invoice_amount_due,
x_invoice_creation_date => l_rec_chg.invoice_creation_date,
x_invoice_desc => l_rec_chg.invoice_desc,
x_transaction_type => l_rec_chg.transaction_type,
x_currency_cd => l_rec_chg.currency_cd,
x_status => l_rec_chg.status,
x_attribute_category => l_rec_chg.attribute_category,
x_attribute1 => l_rec_chg.attribute1,
x_attribute2 => l_rec_chg.attribute2,
x_attribute3 => l_rec_chg.attribute3,
x_attribute4 => l_rec_chg.attribute4,
x_attribute5 => l_rec_chg.attribute5,
x_attribute6 => l_rec_chg.attribute6,
x_attribute7 => l_rec_chg.attribute7,
x_attribute8 => l_rec_chg.attribute8,
x_attribute9 => l_rec_chg.attribute9,
x_attribute10 => l_rec_chg.attribute10,
x_invoice_amount => l_rec_chg.invoice_amount,
x_bill_id => l_rec_chg.bill_id,
x_bill_number => l_rec_chg.bill_number,
x_bill_date => l_rec_chg.bill_date,
x_waiver_flag => l_rec_chg.waiver_flag,
x_waiver_reason => l_rec_chg.waiver_reason,
x_effective_date => l_rec_chg.effective_date,
x_invoice_number => l_rec_chg.invoice_number,
x_exchange_rate => l_rec_chg.exchange_rate,
x_bill_payment_due_date => l_rec_chg.bill_payment_due_date,
x_optional_fee_flag => l_rec_chg.optional_fee_flag,
x_reversal_gl_date => l_rec_chg.reversal_gl_date,
x_tax_year_code => g_rec_1098t_setup.tax_year_code,
x_waiver_name => l_rec_chg.waiver_name);
SELECT 'x'
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE fmast.fund_code = fcat.fund_code
AND fcat.sys_fund_type = cp_v_sys_fund_type
AND fmast.party_id = cp_n_person_id;
SELECT crd.*,
crd.rowid row_id,
crt.credit_class
FROM igs_fi_credits_all crd,
igs_fi_cr_types_all crt
WHERE crd.credit_type_id = crt.credit_type_id
AND crd.status = 'CLEARED'
AND crd.party_id = cp_n_person_id
AND crt.credit_class IN ('SPNSP','EXTFA','INTFA','WAIVER')
AND TRUNC(crd.transaction_date) BETWEEN TRUNC(cp_d_start_date) AND TRUNC(cp_d_end_date)
AND crd.tax_year_code IS NULL;
SELECT 'x'
FROM igf_db_awd_disb_dtl_all
WHERE spnsr_credit_id = cp_n_credit_id;
SELECT crd.credit_id,
crd.waiver_name
FROM igs_fi_credits_all crd,
igs_fi_1098t_setup stp
WHERE party_id = cp_n_person_id
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_seq
AND TRUNC(transaction_date) < TRUNC(cp_d_start_date)
AND crd.tax_year_code IS NOT NULL
AND crd.tax_year_code = stp.tax_year_code
AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date);
igs_fi_credits_pkg.update_row(x_rowid => l_rec_crd.row_id,
x_credit_id => l_rec_crd.credit_id,
x_credit_number => l_rec_crd.credit_number,
x_status => l_rec_crd.status,
x_credit_source => l_rec_crd.credit_source,
x_party_id => l_rec_crd.party_id,
x_credit_type_id => l_rec_crd.credit_type_id,
x_credit_instrument => l_rec_crd.credit_instrument,
x_description => l_rec_crd.description,
x_amount => l_rec_crd.amount,
x_currency_cd => l_rec_crd.currency_cd,
x_exchange_rate => l_rec_crd.exchange_rate,
x_transaction_date => l_rec_crd.transaction_date,
x_effective_date => l_rec_crd.effective_date,
x_reversal_date => l_rec_crd.reversal_date,
x_reversal_reason_code => l_rec_crd.reversal_reason_code,
x_reversal_comments => l_rec_crd.reversal_comments,
x_unapplied_amount => l_rec_crd.unapplied_amount,
x_source_transaction_id => l_rec_crd.source_transaction_id,
x_receipt_lockbox_number => l_rec_crd.receipt_lockbox_number,
x_merchant_id => l_rec_crd.merchant_id,
x_credit_card_code => l_rec_crd.credit_card_code,
x_credit_card_holder_name => l_rec_crd.credit_card_holder_name,
x_credit_card_number => l_rec_crd.credit_card_number,
x_credit_card_expiration_date => l_rec_crd.credit_card_expiration_date,
x_credit_card_approval_code => l_rec_crd.credit_card_approval_code,
x_awd_yr_cal_type => l_rec_crd.awd_yr_cal_type,
x_awd_yr_ci_sequence_number => l_rec_crd.awd_yr_ci_sequence_number,
x_fee_cal_type => l_rec_crd.fee_cal_type,
x_fee_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
x_attribute_category => l_rec_crd.attribute_category,
x_attribute1 => l_rec_crd.attribute1,
x_attribute2 => l_rec_crd.attribute2,
x_attribute3 => l_rec_crd.attribute3,
x_attribute4 => l_rec_crd.attribute4,
x_attribute5 => l_rec_crd.attribute5,
x_attribute6 => l_rec_crd.attribute6,
x_attribute7 => l_rec_crd.attribute7,
x_attribute8 => l_rec_crd.attribute8,
x_attribute9 => l_rec_crd.attribute9,
x_attribute10 => l_rec_crd.attribute10,
x_attribute11 => l_rec_crd.attribute11,
x_attribute12 => l_rec_crd.attribute12,
x_attribute13 => l_rec_crd.attribute13,
x_attribute14 => l_rec_crd.attribute14,
x_attribute15 => l_rec_crd.attribute15,
x_attribute16 => l_rec_crd.attribute16,
x_attribute17 => l_rec_crd.attribute17,
x_attribute18 => l_rec_crd.attribute18,
x_attribute19 => l_rec_crd.attribute19,
x_attribute20 => l_rec_crd.attribute20,
x_gl_date => l_rec_crd.gl_date,
x_check_number => l_rec_crd.check_number,
x_source_transaction_type => l_rec_crd.source_transaction_type,
x_source_transaction_ref => l_rec_crd.source_transaction_ref,
x_credit_card_status_code => l_rec_crd.credit_card_status_code,
x_credit_card_payee_cd => l_rec_crd.credit_card_payee_cd,
x_credit_card_tangible_cd => l_rec_crd.credit_card_tangible_cd,
x_lockbox_interface_id => l_rec_crd.lockbox_interface_id,
x_batch_name => l_rec_crd.batch_name,
x_deposit_date => l_rec_crd.deposit_date,
x_source_invoice_id => l_rec_crd.source_invoice_id,
x_tax_year_code => g_rec_1098t_setup.tax_year_code,
x_waiver_name => l_rec_crd.waiver_name);
SELECT inv.rowid row_id,
inv.*,
ft.s_fee_type
FROM igs_fi_inv_int_all inv,
igs_fi_fee_type ft
WHERE inv.person_id = cp_n_person_id
AND ft.fee_type = inv.fee_type
AND inv.tax_year_code IS NULL
AND TRUNC(inv.invoice_creation_date) BETWEEN TRUNC(cp_d_start_date) AND TRUNC(cp_d_end_date)
AND ft.s_fee_type IN ('AID_ADJ','WAIVER_ADJ');
SELECT crd.credit_id,
crd.waiver_name
FROM igs_fi_credits_all crd,
igs_fi_1098t_setup stp
WHERE crd.party_id = cp_n_person_id
AND crd.fee_cal_type = cp_v_fee_cal_type
AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq
AND TRUNC(transaction_date) < TRUNC(cp_d_start_date)
AND crd.tax_year_code IS NOT NULL
AND crd.tax_year_code = stp.tax_year_code
AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date);
igs_fi_inv_int_pkg.update_row(x_rowid => l_rec_chg.row_id,
x_invoice_id => l_rec_chg.invoice_id,
x_person_id => l_rec_chg.person_id,
x_fee_type => l_rec_chg.fee_type,
x_fee_cat => l_rec_chg.fee_cat,
x_fee_cal_type => l_rec_chg.fee_cal_type,
x_fee_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
x_course_cd => l_rec_chg.course_cd,
x_attendance_mode => l_rec_chg.attendance_mode,
x_attendance_type => l_rec_chg.attendance_type,
x_invoice_amount_due => l_rec_chg.invoice_amount_due,
x_invoice_creation_date => l_rec_chg.invoice_creation_date,
x_invoice_desc => l_rec_chg.invoice_desc,
x_transaction_type => l_rec_chg.transaction_type,
x_currency_cd => l_rec_chg.currency_cd,
x_status => l_rec_chg.status,
x_attribute_category => l_rec_chg.attribute_category,
x_attribute1 => l_rec_chg.attribute1,
x_attribute2 => l_rec_chg.attribute2,
x_attribute3 => l_rec_chg.attribute3,
x_attribute4 => l_rec_chg.attribute4,
x_attribute5 => l_rec_chg.attribute5,
x_attribute6 => l_rec_chg.attribute6,
x_attribute7 => l_rec_chg.attribute7,
x_attribute8 => l_rec_chg.attribute8,
x_attribute9 => l_rec_chg.attribute9,
x_attribute10 => l_rec_chg.attribute10,
x_invoice_amount => l_rec_chg.invoice_amount,
x_bill_id => l_rec_chg.bill_id,
x_bill_number => l_rec_chg.bill_number,
x_bill_date => l_rec_chg.bill_date,
x_waiver_flag => l_rec_chg.waiver_flag,
x_waiver_reason => l_rec_chg.waiver_reason,
x_effective_date => l_rec_chg.effective_date,
x_invoice_number => l_rec_chg.invoice_number,
x_exchange_rate => l_rec_chg.exchange_rate,
x_bill_payment_due_date => l_rec_chg.bill_payment_due_date,
x_optional_fee_flag => l_rec_chg.optional_fee_flag,
x_reversal_gl_date => l_rec_chg.reversal_gl_date,
x_tax_year_code => g_rec_1098t_setup.tax_year_code,
x_waiver_name => l_rec_chg.waiver_name);
SELECT cal_type,
sequence_number
FROM igs_fi_1098t_lps
WHERE tax_year_name = cp_v_tax_year_name
AND half_time_flag = 'Y';
SELECT 'x'
FROM igs_fi_1098t_ats
WHERE tax_year_name = cp_v_tax_year_name
AND attendance_type = cp_v_att_type;
SELECT cal_type,
sequence_number
FROM igs_fi_1098t_lps
WHERE tax_year_name = cp_v_tax_year_name
AND grad_flag = 'Y';
SELECT teach_cal_type,
teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = cp_v_cal_type
AND load_ci_sequence_number = cp_n_ci_seq;
SELECT 'x'
FROM igs_en_su_attempt_all sua,
igs_ps_ver ps,
igs_fi_1098t_pts pts
WHERE sua.person_id = cp_n_person_id
AND sua.cal_type = cp_v_teach_cal_type
AND sua.ci_sequence_number = cp_n_teach_ci_seq
AND sua.unit_attempt_status NOT IN ('UNCONFIRM',
'WAITLISTED')
AND ps.course_cd = sua.course_cd
AND pts.course_type = ps.course_type
AND pts.tax_year_name = cp_v_tax_year_name;
PROCEDURE insert_1098t_data(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
p_n_person_id igs_pe_person_base_v.person_id%TYPE,
p_v_full_name igs_pe_person_base_v.full_name%TYPE,
p_n_box2 igs_fi_1098t_data.billed_amt%TYPE,
p_n_box3 igs_fi_1098t_data.adj_amt%TYPE,
p_n_box4 igs_fi_1098t_data.fin_aid_amt%TYPE,
p_n_box5 igs_fi_1098t_data.fin_aid_adj_amt%TYPE,
p_v_box6 igs_fi_1098t_data.next_acad_flag%TYPE,
p_v_box8 igs_fi_1098t_data.half_time_flag%TYPE,
p_v_box9 igs_fi_1098t_data.grad_flag%TYPE,
p_v_file_addr_correction VARCHAR2) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 06-May-2005
Purpose : Procedure for creating 1098t records
Known limitations,enhancements,remarks:
Change History
Who When What
abshriva 30/11/05 Bug:4768071-Modification in where clause of cursor cur_party_sites
abshriva 9/11/05 Bug:4695680-Modification in where clause and inclusion of new table
'igs_pe_hz_pty_sites' in cursor 'cur_party_sites'
abshriva 26/10/05 Bug:4697644-Modification in where clause for cursor 'cur_party_sites'
***************************************************************** */
CURSOR cur_data_exists(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
SELECT tdata.rowid row_id, tdata.*
FROM igs_fi_1098t_data tdata
WHERE tdata.party_id = cp_n_person_id
AND tdata.tax_year_name = cp_v_tax_year_name
AND tdata.status_code <> 'DNT_RPT'
ORDER BY tdata.stu_1098t_id desc
FOR UPDATE NOWAIT;
SELECT ps.location_id,
ps.identifying_address_flag,
ps.status
FROM hz_party_site_uses hps,
hz_party_sites ps,
igs_pe_hz_pty_sites igsps
WHERE ps.party_id = cp_n_person_id
AND ps.party_site_id = hps.party_site_id
AND hps.site_use_type = cp_v_party_site_use_code
AND hps.status = 'A'
AND igsps.party_site_id(+)=ps.party_site_id
AND TRUNC(sysdate)
BETWEEN TRUNC(NVL(igsps.start_date,sysdate))
AND TRUNC(NVL(igsps.end_date,sysdate));
SELECT loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.country,
loc.city,
loc.state,
loc.postal_code,
loc.province,
loc.county,
loc.delivery_point_code
FROM hz_locations loc
WHERE location_id = cp_location_id;
SELECT rowid
FROM igs_fi_1098t_dtls
WHERE stu_1098t_id = cp_n_stu_1098t_id;
SELECT dat.*, dat.rowid row_id
FROM igs_fi_1098t_data dat
WHERE party_id = cp_n_person_id
AND tax_year_name = cp_v_tax_year_name
AND irs_filed_flag = 'Y'
AND status_code <> 'DNT_RPT'
ORDER BY stu_1098t_id DESC;
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Inside creating 1098t transactions');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'API Person Id calculated as '||l_v_api_pers_id);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Call to igf procedure for validating SSN failed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Call to validate_tin failed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Permanent Address could not be identified. Hence Invalid Address');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Temporary Address could not be identified. Hence Invalid Address');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Neither temporary nor permanent address could be identified. Hence Invalid Address');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Name Control = '||l_v_name_control);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'There is no 1098T record existing. Hence creating a new record.');
igs_fi_1098t_data_pkg.insert_row( x_rowid => l_v_rowid,
x_stu_1098t_id => l_n_stu_1098t_id,
x_tax_year_name => p_v_tax_year_name,
x_party_id => p_n_person_id,
x_extract_date => trunc(sysdate),
x_party_name => p_v_full_name,
x_taxid => l_v_api_pers_id,
x_stu_name_control => l_v_name_control,
x_country => l_rec_locations.country,
x_address1 => l_rec_locations.address1,
x_address2 => l_rec_locations.address2,
x_refund_amt => 0,
x_half_time_flag => p_v_box8,
x_grad_flag => p_v_box9,
x_special_data_entry => null,
x_status_code => g_v_validation_status,
x_error_code => l_v_error_code,
x_file_name => null,
x_irs_filed_flag => 'N',
x_correction_flag => 'N',
x_correction_type_code => null,
x_stmnt_print_flag => 'N',
x_override_flag => 'N',
x_address3 => l_rec_locations.address3,
x_address4 => l_rec_locations.address4,
x_city => l_rec_locations.city,
x_postal_code => l_rec_locations.postal_code,
x_state => l_rec_locations.state,
x_province => l_rec_locations.province,
x_county => l_rec_locations.county,
x_delivery_point_code => l_rec_locations.delivery_point_code,
x_payment_amt => 0,
x_billed_amt => p_n_box2,
x_adj_amt => p_n_box3,
x_fin_aid_amt => p_n_box4,
x_fin_aid_adj_amt => p_n_box5,
x_next_acad_flag => p_v_box6,
x_batch_id => null,
x_mode => 'R');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
x_stu_1098t_id => l_n_stu_1098t_id,
x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => '1098T Data record exists for the person');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Original Record has been filed to IRS');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as the box values are different');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as the address has changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as the Alternate Person Id has changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as full name has changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction Record does not need to be created. Hence exiting from this procedure');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Creating a new correction record');
igs_fi_1098t_data_pkg.insert_row( x_rowid => l_v_rowid,
x_stu_1098t_id => l_n_stu_1098t_id,
x_tax_year_name => p_v_tax_year_name,
x_party_id => p_n_person_id,
x_extract_date => trunc(sysdate),
x_party_name => p_v_full_name,
x_taxid => l_v_api_pers_id,
x_stu_name_control => l_v_name_control,
x_country => l_rec_locations.country,
x_address1 => l_rec_locations.address1,
x_address2 => l_rec_locations.address2,
x_refund_amt => l_rec_1098t_data.refund_amt,
x_half_time_flag => p_v_box8,
x_grad_flag => p_v_box9,
x_special_data_entry => l_rec_1098t_data.special_data_entry,
x_status_code => g_v_validation_status,
x_error_code => l_v_error_code,
x_file_name => null,
x_irs_filed_flag => 'N',
x_correction_flag => l_v_correction_flag,
x_correction_type_code => l_v_correction_code,
x_stmnt_print_flag => 'N',
x_override_flag => 'N',
x_address3 => l_rec_locations.address3,
x_address4 => l_rec_locations.address4,
x_city => l_rec_locations.city,
x_postal_code => l_rec_locations.postal_code,
x_state => l_rec_locations.state,
x_province => l_rec_locations.province,
x_county => l_rec_locations.county,
x_delivery_point_code => l_rec_locations.delivery_point_code,
x_payment_amt => l_rec_1098t_data.payment_amt,
x_billed_amt => p_n_box2,
x_adj_amt => p_n_box3,
x_fin_aid_amt => p_n_box4,
x_fin_aid_adj_amt => p_n_box5,
x_next_acad_flag => p_v_box6,
x_batch_id => null,
x_mode => 'R');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
x_stu_1098t_id => l_n_stu_1098t_id,
x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Original Record has not been filed to IRS');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction flag has not been set. Hence delete all detail records.');
igs_fi_1098t_dtls_pkg.delete_row(x_rowid => l_rec_dtl.rowid);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Update the 1098T Transaction record');
igs_fi_1098t_data_pkg.update_row(x_rowid => l_rec_1098t_data.row_id,
x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
x_tax_year_name => p_v_tax_year_name,
x_party_id => l_rec_1098t_data.party_id,
x_extract_date => trunc(sysdate),
x_party_name => p_v_full_name,
x_taxid => l_v_api_pers_id,
x_stu_name_control => l_v_name_control,
x_country => l_rec_locations.country,
x_address1 => l_rec_locations.address1,
x_address2 => l_rec_locations.address2,
x_refund_amt => l_rec_1098t_data.refund_amt,
x_half_time_flag => p_v_box8,
x_grad_flag => p_v_box9,
x_special_data_entry => l_rec_1098t_data.special_data_entry,
x_status_code => g_v_validation_status,
x_error_code => l_v_error_code,
x_file_name => null,
x_irs_filed_flag => 'N',
x_correction_flag => l_rec_1098t_data.correction_flag,
x_correction_type_code => l_rec_1098t_data.correction_type_code,
x_stmnt_print_flag => 'N',
x_override_flag => 'N',
x_address3 => l_rec_locations.address3,
x_address4 => l_rec_locations.address4,
x_city => l_rec_locations.city,
x_postal_code => l_rec_locations.postal_code,
x_state => l_rec_locations.state,
x_province => l_rec_locations.province,
x_county => l_rec_locations.county,
x_delivery_point_code => l_rec_locations.delivery_point_code,
x_payment_amt => l_rec_1098t_data.payment_amt,
x_billed_amt => p_n_box2,
x_adj_amt => p_n_box3,
x_fin_aid_amt => p_n_box4,
x_fin_aid_adj_amt => p_n_box5,
x_next_acad_flag => p_v_box6,
x_batch_id => null,
x_mode => 'R');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction Flag has been set.');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as the box values have changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as the address has changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as Alternate Person Id has changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created as the address has changed');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs not be created. Hence updating the record with DNT_RPT status');
igs_fi_1098t_data_pkg.update_row(x_rowid => l_rec_1098t_data.row_id,
x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
x_tax_year_name => l_rec_1098t_data.tax_year_name,
x_party_id => l_rec_1098t_data.party_id,
x_extract_date => l_rec_1098t_data.extract_date,
x_party_name => l_rec_1098t_data.party_name,
x_taxid => l_rec_1098t_data.taxid,
x_stu_name_control => l_rec_1098t_data.stu_name_control,
x_country => l_rec_1098t_data.country,
x_address1 => l_rec_1098t_data.address1,
x_address2 => l_rec_1098t_data.address2,
x_refund_amt => l_rec_1098t_data.refund_amt,
x_half_time_flag => l_rec_1098t_data.half_time_flag,
x_grad_flag => l_rec_1098t_data.grad_flag,
x_special_data_entry => l_rec_1098t_data.special_data_entry,
x_status_code => 'DNT_RPT',
x_error_code => l_rec_1098t_data.error_code,
x_file_name => l_rec_1098t_data.file_name,
x_irs_filed_flag => l_rec_1098t_data.irs_filed_flag,
x_correction_flag => l_rec_1098t_data.correction_flag,
x_correction_type_code => l_rec_1098t_data.correction_type_code,
x_stmnt_print_flag => l_rec_1098t_data.stmnt_print_flag,
x_override_flag => l_rec_1098t_data.override_flag,
x_address3 => l_rec_1098t_data.address3,
x_address4 => l_rec_1098t_data.address4,
x_city => l_rec_1098t_data.city,
x_postal_code => l_rec_1098t_data.postal_code,
x_state => l_rec_1098t_data.state,
x_province => l_rec_1098t_data.province,
x_county => l_rec_1098t_data.county,
x_delivery_point_code => l_rec_1098t_data.delivery_point_code,
x_payment_amt => l_rec_1098t_data.payment_amt,
x_billed_amt => l_rec_1098t_data.billed_amt,
x_adj_amt => l_rec_1098t_data.adj_amt,
x_fin_aid_amt => l_rec_1098t_data.fin_aid_amt,
x_fin_aid_adj_amt => l_rec_1098t_data.fin_aid_adj_amt,
x_next_acad_flag => l_rec_1098t_data.next_acad_flag,
x_batch_id => l_rec_1098t_data.batch_id,
x_mode => 'R');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Correction record needs to be created. Hence delete all detail records');
igs_fi_1098t_dtls_pkg.delete_row(x_rowid => l_rec_dtl.rowid);
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Updating the 1098T record');
igs_fi_1098t_data_pkg.update_row(x_rowid => l_rec_1098t_data.row_id,
x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
x_tax_year_name => p_v_tax_year_name,
x_party_id => l_rec_1098t_data.party_id,
x_extract_date => trunc(sysdate),
x_party_name => p_v_full_name,
x_taxid => l_v_api_pers_id,
x_stu_name_control => l_v_name_control,
x_country => l_rec_locations.country,
x_address1 => l_rec_locations.address1,
x_address2 => l_rec_locations.address2,
x_refund_amt => l_rec_1098t_data.refund_amt,
x_half_time_flag => p_v_box8,
x_grad_flag => p_v_box9,
x_special_data_entry => l_rec_1098t_data.special_data_entry,
x_status_code => g_v_validation_status,
x_error_code => l_v_error_code,
x_file_name => null,
x_irs_filed_flag => 'N',
x_correction_flag => l_v_correction_flag,
x_correction_type_code => l_v_correction_code,
x_stmnt_print_flag => 'N',
x_override_flag => 'N',
x_address3 => l_rec_locations.address3,
x_address4 => l_rec_locations.address4,
x_city => l_rec_locations.city,
x_postal_code => l_rec_locations.postal_code,
x_state => l_rec_locations.state,
x_province => l_rec_locations.province,
x_county => l_rec_locations.county,
x_delivery_point_code => l_rec_locations.delivery_point_code,
x_payment_amt => l_rec_1098t_data.payment_amt,
x_billed_amt => p_n_box2,
x_adj_amt => p_n_box3,
x_fin_aid_amt => p_n_box4,
x_fin_aid_adj_amt => p_n_box5,
x_next_acad_flag => p_v_box6,
x_batch_id => null,
x_mode => 'R');
log_to_fnd(p_v_module => 'insert_1098t_data',
p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
END insert_1098t_data;
SELECT person_number,
first_name,
last_name
FROM igs_pe_person_base_v
WHERE person_id = cp_n_person_id;
SELECT dat.override_flag, dat.irs_filed_flag
FROM igs_fi_1098t_data dat
WHERE dat.party_id = cp_n_person_id
AND dat.tax_year_name = cp_v_tax_year_name
AND dat.status_code <> cp_v_status
ORDER BY stu_1098t_id DESC;
SELECT 'x'
FROM igs_pe_typ_instances_all ptyp,
igs_pe_person_types ptm
WHERE ptyp.person_id = cp_n_person_id
AND ptyp.person_type_code = ptm.person_type_code
AND ptm.system_type IN ('EXCHG_VISITOR','NONIMG_STUDENT')
AND TRUNC(ptyp.start_date) <= TRUNC(cp_d_start_date)
AND ((TRUNC(ptyp.end_date) >= TRUNC(cp_d_end_date)) OR (ptyp.end_date IS NULL));
l_t_1098t_drilldown.DELETE;
update_charges(p_n_person_id => p_n_person_id,
p_v_tax_year => g_rec_1098t_setup.tax_year_code);
update_credits(p_n_person_id => p_n_person_id,
p_v_tax_year => g_rec_1098t_setup.tax_year_code);
insert_1098t_data(p_v_tax_year_name => p_v_tax_year_name,
p_n_person_id => p_n_person_id,
p_v_full_name => l_v_full_name,
p_n_box2 => l_n_box2,
p_n_box3 => l_n_box3,
p_n_box4 => l_n_box4,
p_n_box5 => l_n_box5,
p_v_box6 => l_v_box6,
p_v_box8 => l_v_box8,
p_v_box9 => l_v_box9,
p_v_file_addr_correction => p_v_file_addr_correction);