[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y' found_person_id_group
FROM igs_pe_persid_group
WHERE group_id = cp_n_prsid_grp_id
AND closed_ind = 'N'
AND TRUNC(creation_date) <= TRUNC(SYSDATE);
SELECT 'Y' found_remit_addr
FROM igs_or_inst_org_base_v oi,
hz_party_sites ps,
igs_pe_hz_pty_sites igsps,
igs_pe_partysiteuse_v ppv
WHERE ps.party_site_id = cp_n_remit_prty_site_id
AND ps.party_site_id = ppv.party_site_id
AND oi.oi_local_institution_ind = 'Y'
AND oi.party_id = ps.party_id
AND ps.party_site_id = igsps.party_site_id (+)
AND oi.inst_org_ind = 'I'
AND ppv.site_use_type = cp_profile_value
AND ppv.active = 'A'
AND (ps.status = 'A'
AND ((TRUNC(igsps.start_date) <= TRUNC(SYSDATE) AND TRUNC(igsps.start_date) <= TRUNC(cp_d_due_dt)) OR igsps.start_date IS NULL)
AND ((TRUNC(igsps.end_date) >= TRUNC(SYSDATE) AND TRUNC(igsps.end_date) >= TRUNC(cp_d_due_dt)) OR igsps.end_date IS NULL));
SELECT 'Y' found_bill_to_addr_usage
FROM fnd_lookup_values
WHERE lookup_type = 'PARTY_SITE_USE_CODE'
AND lookup_code = cp_c_site_usg_type_cd
AND view_application_id = 222
AND security_group_id = 0
AND language = USERENV('LANG')
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,TRUNC(SYSDATE)) AND NVL(end_date_active,TRUNC(SYSDATE));
SELECT person_id
FROM igs_pe_prsid_grp_mem
WHERE group_id = cp_n_prsid_grp_id
AND NVL(start_date,SYSDATE) <= SYSDATE
AND NVL(end_date,SYSDATE) >= SYSDATE;
SELECT user_profile_option_name
FROM fnd_profile_options_vl
WHERE (profile_option_name LIKE 'IGS_REMIT_TO_ADD_USG');
SELECT (MAX (cut_off_date) + 1) start_date
FROM igs_fi_bill
WHERE person_id = cp_n_person_id;
SELECT inv.invoice_id invoice_id,
inv.invoice_number invoice_number,
inv.fee_type fee_type,
inv.invoice_creation_date invoice_creation_date,
inv.invoice_desc invoice_desc,
NVL (inv.invoice_amount, 0) invoice_amount
FROM igs_fi_inv_int inv
WHERE inv.person_id = cp_n_person_id
AND (cp_d_start_date IS NULL OR (TRUNC(inv.invoice_creation_date) >= TRUNC(cp_d_start_date)))
AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_cutoff_dt)
AND inv.bill_date IS NULL
FOR UPDATE NOWAIT;
SELECT inv.row_id row_id,
inv.invoice_id invoice_id,
inv.person_id person_id,
inv.fee_type fee_type,
inv.fee_cat fee_cat,
inv.fee_cal_type fee_cal_type,
inv.fee_ci_sequence_number fee_ci_sequence_number,
inv.course_cd course_cd,
inv.attendance_mode attendance_mode,
inv.attendance_type attendance_type,
inv.invoice_amount_due invoice_amount_due,
inv.invoice_creation_date invoice_creation_date,
inv.invoice_desc invoice_desc,
inv.transaction_type transaction_type,
inv.currency_cd currency_cd,
inv.exchange_rate exchange_rate,
inv.status status,
inv.attribute_category attribute_category,
inv.attribute1 attribute1,
inv.attribute2 attribute2,
inv.attribute3 attribute3,
inv.attribute4 attribute4,
inv.attribute5 attribute5,
inv.attribute6 attribute6,
inv.attribute7 attribute7,
inv.attribute8 attribute8,
inv.attribute9 attribute9,
inv.attribute10 attribute10,
inv.org_id org_id,
inv.invoice_amount invoice_amount,
inv.bill_id bill_id,
inv.bill_number bill_number,
inv.bill_date bill_date,
inv.waiver_flag waiver_flag,
inv.waiver_reason waiver_reason,
inv.effective_date effective_date,
inv.invoice_number invoice_number,
inv.bill_payment_due_date bill_payment_due_date,
inv.last_update_date last_update_date,
inv.last_updated_by last_updated_by,
inv.creation_date creation_date,
inv.created_by created_by,
inv.last_update_login last_update_login,
inv.request_id request_id,
inv.program_application_id program_application_id,
inv.program_id program_id,
inv.program_update_date program_update_date,
inv.optional_fee_flag optional_fee_flag,
inv.reversal_gl_date reversal_gl_date,
inv.tax_year_code tax_year_code,
inv.waiver_name
FROM igs_fi_inv_int inv
WHERE inv.person_id = cp_n_person_id
AND (cp_d_start_date IS NULL OR (TRUNC(inv.invoice_creation_date) >= TRUNC(cp_d_start_date)))
AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_cutoff_dt)
AND inv.bill_date IS NULL;
SELECT credit_activity_id,
credit_number,
effective_date,
credit_type,
description,
(-amount) amount -- Negate the amount since this is a Credit Transaction.
FROM igs_fi_crdt_trnsctns
WHERE person_id = cp_n_person_id
AND (cp_d_start_date IS NULL OR (TRUNC(effective_date) >= TRUNC(cp_d_start_date)))
AND TRUNC(effective_date) <= TRUNC(cp_d_cutoff_dt)
AND credit_activity_id IN (SELECT credit_activity_id
FROM igs_fi_cr_activities
WHERE bill_date IS NULL)
FOR UPDATE OF credit_activity_id NOWAIT;
SELECT pav.addr_line_1,
pav.addr_line_2,
pav.addr_line_3,
pav.addr_line_4,
pav.city,
pav.state,
pav.province,
pav.county,
pav.country,
pav.postal_code,
pav.delivery_point_code
FROM igs_pe_addr_v pav,
igs_pe_partysiteuse_v ppv
WHERE pav.person_id = p_n_person_id
AND pav.party_site_id = ppv.party_site_id
AND ppv.site_use_type IN (cp_c_site_usg_type_cd_1,cp_c_site_usg_type_cd_2,cp_c_site_usg_type_cd_3)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(pav.start_dt,SYSDATE))
AND TRUNC(NVL(pav.end_dt,SYSDATE))
AND pav.status = 'A'
AND ppv.active = 'A';
SELECT addr_line_1,
addr_line_2,
addr_line_3,
addr_line_4,
city,
state,
province,
county,
country,
postal_code,
delivery_point_code
FROM igs_pe_addr_v
WHERE party_site_id = cp_n_remit_prty_site_id;
SELECT NVL (closing_balance, 0) closing_balance
FROM igs_fi_bill
WHERE person_id = cp_n_person_id
AND TRUNC(cut_off_date) = TRUNC(cp_d_start_dt - 1);
SELECT NVL (SUM (inv.invoice_amount), 0) total_charge_amount
FROM igs_fi_inv_int inv
WHERE inv.person_id = cp_n_person_id
AND (cp_d_start_date IS NULL OR (TRUNC(inv.invoice_creation_date) >= TRUNC(cp_d_start_date)))
AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_cutoff_dt)
AND inv.bill_date IS NULL;
SELECT NVL (SUM (amount), 0) total_credit_amount
FROM igs_fi_crdt_trnsctns
WHERE person_id = cp_n_person_id
AND (cp_d_start_date IS NULL OR (TRUNC(effective_date) >= TRUNC(cp_d_start_date)))
AND TRUNC(effective_date) <= TRUNC(cp_d_cutoff_dt)
AND credit_activity_id IN (SELECT credit_activity_id
FROM igs_fi_cr_activities
WHERE bill_date IS NULL);
SELECT ca.rowid,
ca.*
FROM igs_fi_cr_activities ca
WHERE credit_activity_id = cp_n_credit_act_id;
SELECT
disb.award_id,
disb.disb_num,
disb.disb_date,
fmast.fund_id,
disb.ld_cal_type,
disb.ld_sequence_number,
disb.disb_net_amt,
fmast.bill_desc,
cr.description,
fcat.fed_fund_code
FROM
igf_aw_awd_disb disb,
igf_aw_award awd,
igf_aw_fund_mast fmast,
igf_aw_fund_cat fcat,
igf_ap_fa_base_rec base,
igs_fi_cr_types cr,
igf_ap_batch_aw_map bm
WHERE disb.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND awd.base_id = base.base_id
AND fmast.credit_type_id = cr.credit_type_id (+)
AND fmast.fund_code = fcat.fund_code
AND fmast.ci_cal_type = bm.ci_cal_type
AND fmast.ci_sequence_number = bm.ci_sequence_number
AND awd.award_status ='ACCEPTED'
AND disb.trans_type = 'P'
AND disb.show_on_bill = 'Y'
AND base.person_id = cp_person_id
AND TRUNC(disb.disb_date) <= TRUNC(cp_cutoff_dt)
AND bm.award_year_status_code = 'O';
SELECT
cra.credit_activity_id
FROM
igs_fi_cr_activities cra,
igs_fi_credits cr,
igs_fi_cr_types crt
WHERE
cra.status = 'CLEARED'
AND cr.party_id = cp_n_person_id
AND cra.credit_id = cr.credit_id
AND cr.credit_type_id = crt.credit_type_id
AND crt.credit_class IN ('ENRDEPOSIT', 'OTHDEPOSIT')
AND (cp_d_start_date IS NULL
OR (TRUNC(cr.effective_date) >= TRUNC(cp_d_start_date) ))
AND TRUNC(cr.effective_date) <= TRUNC(cp_d_cutoff_dt)
AND cra.bill_id IS NULL
AND (
(cp_c_rec_installed = 'Y'
AND cra.dr_gl_ccid IS NOT NULL
AND cra.cr_gl_ccid IS NOT NULL
)
OR
(cp_c_rec_installed = 'N'
AND cra.dr_account_cd IS NOT NULL
AND cra.cr_account_cd IS NOT NULL
)
)
FOR UPDATE OF credit_activity_id NOWAIT;
SELECT s_fee_type, fee_class
FROM igs_fi_fee_type ft
WHERE fee_type = cp_fee_type;
SELECT MIN(invoice_creation_date)
FROM igs_fi_inv_int_all
WHERE person_id = p_n_person_id;
SELECT MIN(effective_date)
FROM igs_fi_credits_all
WHERE party_id = p_n_person_id;
igs_fi_bill_pkg.insert_row (
x_rowid => l_r_bill_row_id,
x_bill_id => l_n_bill_id_seq,
x_bill_number => NULL,
x_bill_date => TRUNC(SYSDATE),
x_due_date => p_d_due_dt,
x_person_id => p_n_person_id,
x_bill_from_date => l_d_start_date,
x_opening_balance => igs_fi_gen_gl.get_formatted_amount(l_n_opening_balance),
x_cut_off_date => p_d_cutoff_dt,
x_closing_balance => igs_fi_gen_gl.get_formatted_amount(l_n_closing_balance),
x_to_pay_amount => igs_fi_gen_gl.get_formatted_amount(l_to_pay_amount), --added as a part of bug:2293676
x_printed_flag => 'N',
x_print_date => NULL,
x_mode => 'R'
);
igs_fi_bill_trnsctns_pkg.insert_row (
x_rowid => l_r_bill_trans_row_id,
x_transaction_id => l_n_transaction_id,
x_bill_id => l_n_bill_id_seq,
x_transaction_type => 'D',
x_invoice_creditact_id => rec_cur_charge_trans.invoice_id,
x_transaction_date => rec_cur_charge_trans.invoice_creation_date,
x_transaction_number => rec_cur_charge_trans.invoice_number,
x_fee_credit_type => rec_cur_charge_trans.fee_type,
x_transaction_description => rec_cur_charge_trans.invoice_desc,
x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(rec_cur_charge_trans.invoice_amount),
x_mode => 'R'
);
igs_fi_bill_trnsctns_pkg.insert_row (
x_rowid => l_r_bill_trans_row_id,
x_transaction_id => l_n_transaction_id,
x_bill_id => l_n_bill_id_seq,
x_transaction_type => 'C',
x_invoice_creditact_id => rec_cur_credit_trans.credit_activity_id,
x_transaction_date => rec_cur_credit_trans.effective_date,
x_transaction_number => rec_cur_credit_trans.credit_number,
x_fee_credit_type => rec_cur_credit_trans.credit_type,
x_transaction_description => rec_cur_credit_trans.description,
x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(rec_cur_credit_trans.amount),
x_mode => 'R'
);
igs_fi_bill_pln_crd_pkg.insert_row(
x_rowid => l_pln_crd_rowid,
x_bill_id => l_n_bill_id_seq,
x_award_id => l_cur_planned_crd.award_id,
x_disb_num => l_cur_planned_crd.disb_num,
x_pln_credit_date => l_cur_planned_crd.disb_date,
x_fund_id => l_cur_planned_crd.fund_id,
x_fee_cal_type => l_fee_cal_type,
x_fee_ci_sequence_number => l_fee_ci_seq_num,
x_pln_credit_amount => igs_fi_gen_gl.get_formatted_amount(l_cur_planned_crd.disb_net_amt),
x_mode => 'R',
x_bill_desc => l_bill_desc
);
igs_fi_bill_addr_pkg.insert_row (
x_rowid => l_r_bill_addr_row_id,
x_bill_addr_id => l_n_bill_addr_id,
x_bill_id => l_n_bill_id_seq,
x_addr_type => 'R',
x_addr_line_1 => rec_cur_remitt_addr.addr_line_1,
x_addr_line_2 => rec_cur_remitt_addr.addr_line_2,
x_addr_line_3 => rec_cur_remitt_addr.addr_line_3,
x_addr_line_4 => rec_cur_remitt_addr.addr_line_4,
x_city => rec_cur_remitt_addr.city,
x_state => rec_cur_remitt_addr.state,
x_province => rec_cur_remitt_addr.province,
x_county => rec_cur_remitt_addr.county,
x_country => rec_cur_remitt_addr.country,
x_postal_code => rec_cur_remitt_addr.postal_code,
x_delivery_point_code => rec_cur_remitt_addr.delivery_point_code,
x_mode => 'R'
);
igs_fi_bill_addr_pkg.insert_row (
x_rowid => l_r_bill_addr_row_id,
x_bill_addr_id => l_n_bill_addr_id,
x_bill_id => l_n_bill_id_seq,
x_addr_type => 'B',
x_addr_line_1 => rec_cur_bill_to_addr1.addr_line_1,
x_addr_line_2 => rec_cur_bill_to_addr1.addr_line_2,
x_addr_line_3 => rec_cur_bill_to_addr1.addr_line_3,
x_addr_line_4 => rec_cur_bill_to_addr1.addr_line_4,
x_city => rec_cur_bill_to_addr1.city,
x_state => rec_cur_bill_to_addr1.state,
x_province => rec_cur_bill_to_addr1.province,
x_county => rec_cur_bill_to_addr1.county,
x_country => rec_cur_bill_to_addr1.country,
x_postal_code => rec_cur_bill_to_addr1.postal_code,
x_delivery_point_code => rec_cur_bill_to_addr1.delivery_point_code,
x_mode => 'R'
);
igs_fi_bill_dpsts_pkg.insert_row (
x_rowid => l_c_bill_deposits_row_id,
x_bill_id => l_n_bill_id_seq,
x_credit_activity_id => rec_bill_deposits.credit_activity_id,
x_mode => 'R'
);
igs_fi_inv_int_pkg.update_row (
x_rowid => rec_cur_charge_trans.row_id,
x_invoice_id => rec_cur_charge_trans.invoice_id,
x_person_id => rec_cur_charge_trans.person_id,
x_fee_type => rec_cur_charge_trans.fee_type,
x_fee_cat => rec_cur_charge_trans.fee_cat,
x_fee_cal_type => rec_cur_charge_trans.fee_cal_type,
x_fee_ci_sequence_number => rec_cur_charge_trans.fee_ci_sequence_number,
x_course_cd => rec_cur_charge_trans.course_cd,
x_attendance_mode => rec_cur_charge_trans.attendance_mode,
x_attendance_type => rec_cur_charge_trans.attendance_type,
x_invoice_amount_due => rec_cur_charge_trans.invoice_amount_due,
x_invoice_creation_date => rec_cur_charge_trans.invoice_creation_date,
x_invoice_desc => rec_cur_charge_trans.invoice_desc,
x_transaction_type => rec_cur_charge_trans.transaction_type,
x_currency_cd => rec_cur_charge_trans.currency_cd,
x_status => rec_cur_charge_trans.status,
x_attribute_category => rec_cur_charge_trans.attribute_category,
x_attribute1 => rec_cur_charge_trans.attribute1,
x_attribute2 => rec_cur_charge_trans.attribute2,
x_attribute3 => rec_cur_charge_trans.attribute3,
x_attribute4 => rec_cur_charge_trans.attribute4,
x_attribute5 => rec_cur_charge_trans.attribute5,
x_attribute6 => rec_cur_charge_trans.attribute6,
x_attribute7 => rec_cur_charge_trans.attribute7,
x_attribute8 => rec_cur_charge_trans.attribute8,
x_attribute9 => rec_cur_charge_trans.attribute9,
x_attribute10 => rec_cur_charge_trans.attribute10,
x_invoice_amount => rec_cur_charge_trans.invoice_amount,
x_bill_id => l_n_bill_id_seq,
x_bill_number => TO_CHAR (l_n_bill_id_seq),
x_bill_date => TRUNC(SYSDATE),
x_waiver_flag => rec_cur_charge_trans.waiver_flag,
x_waiver_reason => rec_cur_charge_trans.waiver_reason,
x_effective_date => rec_cur_charge_trans.effective_date,
x_invoice_number => rec_cur_charge_trans.invoice_number,
x_exchange_rate => rec_cur_charge_trans.exchange_rate,
x_bill_payment_due_date => p_d_due_dt,
x_optional_fee_flag => rec_cur_charge_trans.optional_fee_flag,
x_mode => 'R',
x_reversal_gl_date => rec_cur_charge_trans.reversal_gl_date,
x_tax_year_code => rec_cur_charge_trans.tax_year_code,
x_waiver_name => rec_cur_charge_trans.waiver_name
);
igs_fi_cr_activities_pkg.update_row (
x_rowid => rec_cur_credit_hist.rowid,
x_credit_activity_id => rec_cur_credit_hist.credit_activity_id,
x_credit_id => rec_cur_credit_hist.credit_id,
x_status => rec_cur_credit_hist.status,
x_transaction_date => rec_cur_credit_hist.transaction_date,
x_amount => rec_cur_credit_hist.amount,
x_dr_account_cd => rec_cur_credit_hist.dr_account_cd,
x_cr_account_cd => rec_cur_credit_hist.cr_account_cd,
x_dr_gl_ccid => rec_cur_credit_hist.dr_gl_ccid,
x_cr_gl_ccid => rec_cur_credit_hist.cr_gl_ccid,
x_bill_id => l_n_bill_id_seq,
x_bill_number => TO_CHAR (l_n_bill_id_seq),
x_bill_date => TRUNC(SYSDATE),
x_posting_id => rec_cur_credit_hist.posting_id,
x_mode => 'R',
x_gl_date => rec_cur_credit_hist.gl_date,
x_gl_posted_date => rec_cur_credit_hist.gl_posted_date,
x_posting_control_id => rec_cur_credit_hist.posting_control_id
);
igs_fi_cr_activities_pkg.update_row (
x_rowid => rec_cur_credit_hist.rowid,
x_credit_activity_id => rec_cur_credit_hist.credit_activity_id,
x_credit_id => rec_cur_credit_hist.credit_id,
x_status => rec_cur_credit_hist.status,
x_transaction_date => rec_cur_credit_hist.transaction_date,
x_amount => rec_cur_credit_hist.amount,
x_dr_account_cd => rec_cur_credit_hist.dr_account_cd,
x_cr_account_cd => rec_cur_credit_hist.cr_account_cd,
x_dr_gl_ccid => rec_cur_credit_hist.dr_gl_ccid,
x_cr_gl_ccid => rec_cur_credit_hist.cr_gl_ccid,
x_bill_id => l_n_bill_id_seq,
x_bill_number => TO_CHAR (l_n_bill_id_seq),
x_bill_date => TRUNC(SYSDATE),
x_posting_id => rec_cur_credit_hist.posting_id,
x_mode => 'R',
x_gl_date => rec_cur_credit_hist.gl_date,
x_gl_posted_date => rec_cur_credit_hist.gl_posted_date,
x_posting_control_id => rec_cur_credit_hist.posting_control_id
);
smvk 10-Sep-2003 Bug 3045007, Modified the cursor c_clo_dis_pp_dtls to select
records of plan_end_date less than or equal to cutoff date.
***************************************************************/
-- cursor to select the payment plan contract signed by student and payment plan status is ACTIVE.
cursor c_act_pp_dtls (cp_n_person_id IN igs_fi_pp_std_attrs.person_id%TYPE) IS
SELECT *
FROM igs_fi_pp_std_attrs
WHERE person_id = cp_n_person_id
AND plan_status_code = 'ACTIVE';
SELECT *
FROM igs_fi_pp_std_attrs
WHERE person_id = cp_n_person_id
AND plan_status_code IN ('CLOSED','DISQUALIFIED')
AND TRUNC(plan_end_date) <= TRUNC(cp_d_cutoff_date);
SELECT *
FROM igs_fi_pp_instlmnts
WHERE student_plan_id = cp_n_student_plan_id;
l_c_rowid ROWID; -- to hold rowid while using insert_row tbh call.
igs_fi_bill_p_plans_pkg.insert_row (
X_ROWID => l_c_rowid,
X_STUDENT_PLAN_ID => rec_act_pp_dtls.student_plan_id,
X_BILL_ID => p_n_bill_id,
X_PLAN_START_DATE => rec_act_pp_dtls.plan_start_date,
X_PLAN_END_DATE => rec_act_pp_dtls.plan_end_date,
X_MODE => 'R'
);
igs_fi_bill_instls_pkg.insert_row(
X_ROWID => l_c_rowid,
X_STUDENT_PLAN_ID => rec_pp_instlmnts.student_plan_id,
X_BILL_ID => p_n_bill_id,
X_INSTALLMENT_ID => rec_pp_instlmnts.installment_id,
X_INSTALLMENT_LINE_NUM => rec_pp_instlmnts.installment_line_num,
X_INSTALLMENT_DUE_DATE => rec_pp_instlmnts.due_date,
X_INSTALLMENT_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.installment_amt),
X_DUE_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.due_amt),
X_MODE => 'R'
);
igs_fi_bill_p_plans_pkg.insert_row (
X_ROWID => l_c_rowid,
X_STUDENT_PLAN_ID => rec_clo_dis_pp_dtls.student_plan_id,
X_BILL_ID => p_n_bill_id,
X_PLAN_START_DATE => rec_clo_dis_pp_dtls.plan_start_date,
X_PLAN_END_DATE => rec_clo_dis_pp_dtls.plan_end_date,
X_MODE => 'R'
);
igs_fi_bill_instls_pkg.insert_row(
X_ROWID => l_c_rowid,
X_STUDENT_PLAN_ID => rec_pp_instlmnts.student_plan_id,
X_BILL_ID => p_n_bill_id,
X_INSTALLMENT_ID => rec_pp_instlmnts.installment_id,
X_INSTALLMENT_LINE_NUM => rec_pp_instlmnts.installment_line_num,
X_INSTALLMENT_DUE_DATE => rec_pp_instlmnts.due_date,
X_INSTALLMENT_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.installment_amt),
X_DUE_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.due_amt),
X_MODE => 'R'
);