[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM igs_fi_f_typ_ca_inst ftci,
igs_fi_fee_str_stat fsst
WHERE ftci.fee_cal_type = cp_c_fee_cal_type
AND ftci.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
AND ftci.fee_type = cp_c_fee_type
AND ftci.fee_type_ci_status = fsst.fee_structure_status
AND (
(fsst.s_fee_structure_status = cp_c_status
AND
cp_c_transaction_type NOT IN (g_c_assessment,g_c_retention)
)
OR
(cp_c_transaction_type IN (g_c_assessment,g_c_retention))
);
SELECT 'x'
FROM igs_fi_f_cat_fee_lbl fcfl,
igs_fi_fee_str_stat fsst
WHERE fcfl.fee_cat = cp_c_fee_cat
AND fcfl.fee_type = cp_c_fee_type
AND fcfl.fee_liability_status = fsst.fee_structure_status
AND (
(fsst.s_fee_structure_status = cp_c_status
AND
cp_c_transaction_type NOT IN (g_c_assessment,g_c_retention)
)
OR
(cp_c_transaction_type IN (g_c_assessment,g_c_retention))
);
SELECT 'x'
FROM igs_ps_ver pv, igs_ps_stat ps
WHERE pv.course_cd = cp_course_cd
AND pv.course_status = ps.course_status
AND ps.s_course_status = cp_status;
pathipat 16-Nov-2002 Enh Bug: 2584986: Replaced cursor selecting from igs_fi_cur with generic
function to get the currency_cd set in igs_fi_control
********************************************************************************************** */
l_bool BOOLEAN;
SELECT 'x'
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT 'x'
FROM igs_or_unit
WHERE org_unit_cd = cp_org_unit_cd;
SELECT 'x'
FROM igs_fi_inv_int
WHERE invoice_id = cp_source_txn_id;
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE course_cd = l_course_cd
AND person_id = l_person_id;
SELECT start_dt
FROM igs_or_unit
WHERE org_unit_cd = l_org_unit_cd;
SELECT unit_cd ,version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = l_uoo_id;
SELECT ROWID,
inv.*
FROM igs_fi_inv_int inv
WHERE invoice_id = cp_invoice_id;
SELECT appl.application_id
FROM igs_fi_applications appl,
igs_fi_credits fc,
igs_fi_cr_types crt
WHERE appl.invoice_id = cp_invoice_id
AND appl.credit_id = fc.credit_id
AND fc.credit_type_id = crt.credit_type_id
AND crt.credit_class <> cp_credit_class
AND appl.application_type = cp_app_type
AND NOT EXISTS (SELECT 'x'
FROM igs_fi_applications appl2
WHERE appl2.application_type = cp_unapp_type
AND appl2.link_application_id = appl.application_id
AND appl2.amount_applied = - appl.amount_applied)
ORDER BY appl.application_id DESC;
SELECT credit_type_id
FROM igs_fi_cr_types
WHERE credit_class = cp_neg_credit_class
AND cp_effective_date BETWEEN effective_start_date AND NVL(effective_end_date,
cp_effective_date);
SELECT SUM(invoice_amount) inv_amt,
SUM(invoice_amount_due) inv_due,
iln.error_account
FROM igs_fi_inv_int inv,
igs_fi_invln_int iln
WHERE inv.invoice_id = iln.invoice_id
AND ((cp_invoice_id IS NULL
AND inv.person_id = cp_person_id
AND inv.fee_type = cp_fee_type
AND inv.fee_cal_type = cp_fee_cal_type
AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND inv.transaction_type = cp_transaction_type
AND ((inv.fee_cat = cp_fee_cat)
OR (inv.fee_cat IS NULL and cp_fee_cat IS NULL))
AND ((iln.uoo_id = cp_uoo_id)
OR (iln.uoo_id IS NULL and cp_uoo_id IS NULL))
AND ((iln.location_cd = cp_location_cd)
OR (iln.location_cd IS NULL and cp_location_cd IS NULL))
AND ((inv.course_cd = cp_course_cd)
OR (inv.course_cd IS NULL and cp_course_cd IS NULL)))
OR (inv.invoice_id = cp_invoice_id))
AND NOT EXISTS (SELECT 'x'
FROM igs_fi_credits fc,
igs_fi_cr_types crt,
igs_fi_applications app
WHERE app.invoice_id = inv.invoice_id
AND app.credit_id = fc.credit_id
AND fc.status = g_cleared
AND fc.credit_type_id = crt.credit_type_id
AND crt.credit_class = g_neg_cr_class
AND app.amount_applied = inv.invoice_amount)
GROUP BY iln.error_account;
cur_alt_cd_desc is modified to select another column 'description' from igs_ca_inst_all.
pathipat 12-Jun-2006 Bug 5306868 - Modified cursor cur_chg: Added filter on invoice_amount_due
and removed ORDER_BY on diff_amt
Added cursors cur_chgadj, cur_charges, associated code and local variables.
sapanigr 29-May-2006 Bug 5251760 Added cursor cur_chg_inv. Removed UNION in cur_chg. Modified related code appropriately
svuppala 05-May-2006 Bug 3924836; Added l_n_invoice_amount, l_n_amount variables to
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_inv_int
and igs_fi_invln_int are now rounded off to currency precision
sapanigr 24-Feb-2006 Bug 5018036 - Removed cursor 'cur_ret' and cursor variable 'l_ret_rec' as it was not being used anywhere in the code.
sapanigr 09-Feb-2006 Bug 5018036 - Modifed cursor 'cur_ret' to take values from base table 'igs_fi_f_typ_ca_inst_all'.
The view igs_fi_f_typ_ca_inst_lkp_v used earlier lead to high shared memory usage.
abshriva 24-Oct-2005 Bug 4680553 - The Message 'IGS_FI_WAV_TRANS_CREATED' was removed as it was being
called in error page when waiver transaction was successful
pathipat 05-Oct-2005 Bug 4383148 - Fees not assessed if attendance type cannot be derived
Removed invocation of validate_atd_mode and validate_atd_type
svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
Modified HEADER_REC_TYPE -- included waiver_name.
Modified l_api_version
gurprsin 02-Jun-2005 Enh# 3442712 Modified TBH call of table IGS_fi_invln_int_all
i.e. igs_fi_inv_int_pkg.insert_row to include unit_type_id and unti_level
pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
uudayapr 08-Mar-2004 Bug#3478599.Added the code to prevent charge creation with
error account as Y for Transaction Type as
Document when Revenue Account Derivation fails.
vvutukur 27-Jun-2003 Bug#2849185.Bypassed unncessary validations in case of negative charge.
jbegum 20-Jun-2003 Bug# 2998266, NEXT_INVOICE_NUMBER in the IGS_FI_CONTROL table will not be used for
generating unique charge numbers. Next Value from a DB sequence will be used for
for generating unique charges numbers.
Removed the call to IGS_FI_CONTROL_PKG.update_row
vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Removed 3 columns(lockbox_context,lockbox_number,attribute,ar_int_org_id)
from igs_fi_control_pkg.update_row.
vvutukur 26-May-2003 Enh#2831572.Financial Accounting Build. Changes as specified in TD.
shtatiko 05-MAY-2003 Enh# 2831569, Added check Manage Accounts System Option before creating a charge.
And updation of Holds or Standard balances is done only if its value is STUDENT_FINANCE.
pathipat 14-Apr-2003 Enh 2831569 - Commercial Receivables Interface build
Modified call to igs_fi_control_pkg.update_row
vvutukur 11-Apr-2003 Enh#2831554.Internal Credits API Build. Added checks to validate ADJ credit instrument,charge method and transaction type.
agairola 11-Mar-2003 Bug 2762740: 1. Modified the call to the validate_neg_amnt.
2. Added a new variable l_val_neg_amnt and this variable
gets the value from validate_neg_amnt
3. Added the code to check for l_val_neg_amnt = 1
4. Modified the code for the amount validation(l_val_neg_amnt=2)
smadathi 18-Feb-2002 Enh. Bug 2747329.Modified the TBH call to IGS_FI_CONTROL to Add new columns
rfnd_destination, ap_org_id, dflt_supplier_site_name
pathipat 14-Nov-2002 Enh Bug: 2584986 -
1. Added parameters gl_date, gl_posted_date and posting_control_id
in the call to igs_fi_invln_int_pkg.insert_row()
2. Added parameter p_gl_date in call to proc_neg_chg() for negative charges
3. Removed calls to get_local_amount, passed the invoice_amount and currency_cd
directly from p_header_rec, instead of conversion to local currency
vvutukur 20-sep-2002 Enh#2562745.1)Added conv_process_run_ind parameter to call to igs_fi_control_pkg.
update_row.2)Added call to igs_fi_prc_balance.update_balances to update the Holds
Balance real time whenever a charge gets created.3)Added two new validations to
error out of charges api in the following scenarios.a)if holds conversion process
is running b)if no active balance rule exist for HOLDS.
vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,Removed call to the private
function validate_subaccount and its related code.Removed declaration of local
variables l_party_subaccount_id,l_subacc_name.Removed cursor cur_sa.Removed
reference to subaccount_id from the calls to IGS_FI_INV_INT_PKG.Insert_Row and
igs_fi_prc_balances.update_balances.Removed call to igs_fi_gen_005.validate_psa
since the function igs_fi_gen_005.validate_psa is being removed.Removed call to
igs_fi_party_sa_pkg.insert_row as the table igs_fi_party_sa is being obsoleted.
Also added 7 new parameters to the call to igs_fi_control_pkg.update_row.
smadathi 03-Jul-2002 Bug 2443082. Modified update_balances procedure call. Modified to pass transaction date
instead of system date.
agairola 10-Jun-2002 Bug Number: 2407624 Modified the call to the Get_Local_Amount
agairola 17-May-2002 Following modifications were done for the bug 2323555 - Call Build Accounts
procedure is called in all cases even if the accounts are passed.
Also assigned the value of the accounts passed as input to the Charges API
to the local variables used in the call to Call Build Accounts procedure
SYkrishn 15-APR-2002 Added planned_credits_ind to the IGS_FI_CONTROL_PKG.update_row call as part of Enh 2293676
smvk 08-Mar-2002 Added four attributes refund_dr_gl_ccid,refund_cr_gl_ccid,refund_dr_account_cd,
refund_cr_account_cd and removed three attributes last_account_trans,last_payment_trans,
last_pay_term_trans to the call to IGS_FI_CONTROL_PKG.Update_row as per Bug #2144600
vvutukur 27-02-2002 added call to igs_fi_gen_007.validate_person instead of calling local function
validate_person for bug:2238362
vvutukur 18-feb-2002 added ar_int_org_id column to igs_fi_control_pkg.update_row call. bug:2222272
jbegum 14-Feb-2001 As part of Enh bug # 2201081
Added call to IGS_FI_GEN_005.validate_psa and IGS_FI_PARTY_SA_PKG.insert_row
Removed cursor cur_psa
kkillams 01-08-2001 Modification done w.r.t student finance dld bug id :1882122
Build accounting process is calling only if these parameters are
don't have values p_override_dr_rec_ccid, p_override_cr_rev_ccid,
p_override_dr_rec_account_cd and p_override_cr_rev_account_cd
before inserting the data into igs_fi_invln_int.
jbegum 26-Sep-2001 As part of bug #1962286 the following changes were done:
Changed the call to the local function validate_uoo.
Changed the call to IGS_FI_INVLN_INT_PKG.Insert_row.
vchappid 05-Oct-2001 As a part of Enh Bug#2030448, the call to the calculate balances process is
replaced with a call to the new procedure Update_Balances created as a part
of the SFCR010. Limitation of the Accounting Method to CASH is removed,
Balance_Flag has been removed from IGS_FI_INV_INT_ALL, IGS_FI_CREDITS_ALL tables,
New column optional_fee_flag column is added in IGS_FI_INV_INT_ALL Table.
smadathi 12-oct-2001 As part of enhancement bug#2042716 , the TBH calls to
IGS_FI_PARTY_SUBACTS modified . Payment_plan_flag added.
jbegum 19-Nov-2001 As part of Enhancement bug #2113459 the following changes were done:
Added a new cursor cur_ret.
Added an if condition that checks for transaction type RETENTION.
If transaction type is RETENTION then creates the credit and debit side of
retention charge.
nalkumar 19-Dec-2001 Changed the call to IGS_FI_PARTY_SUBACTS_PKG.insert_row.
This is as per the SF015 Holds DLD. Bug# 2126091.
agairola 12-Feb-2002 Changed the functionality for creation of the negative charges as per the DLD
specfied for the negative charges. SFCR003 Bug No: 2195715
********************************************************************************************** */
l_api_name CONSTANT VARCHAR2(30) := 'Create_Charge';
SELECT rowid,
igs_fi_inv_int.*
FROM igs_fi_inv_int
WHERE invoice_id = cp_invoice_id;
SELECT rowid,
igs_fi_applications.*
FROM igs_fi_applications
WHERE invoice_id = cp_invoice_id
AND application_type = cp_app;
SELECT rowid,
igs_fi_credits.*
FROM igs_fi_credits
WHERE credit_id = cp_credit_id;
SELECT rowid,
igs_fi_control.*
FROM igs_fi_control;
SELECT inv.rowid row_id,
inv.invoice_id invoice_id,
inv.invoice_amount_due invoice_amount_due,
inv.invoice_amount invoice_amount
FROM igs_fi_inv_int_all inv,
igs_fi_invln_int_all iln
WHERE inv.invoice_id = iln.invoice_id
AND inv.person_id = cp_person_id
AND inv.fee_type = cp_fee_type
AND inv.fee_cal_type = cp_fee_cal_type
AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND inv.transaction_type = cp_transaction_type
AND iln.error_account <> g_ind_yes
AND inv.invoice_amount_due > 0
AND ((iln.uoo_id = cp_uoo_id)
OR (iln.uoo_id IS NULL AND cp_uoo_id IS NULL))
AND ((iln.location_cd = cp_location_cd)
OR (iln.location_cd IS NULL AND cp_location_cd IS NULL))
AND ((fee_cat = cp_fee_cat)
OR (fee_cat IS NULL and cp_fee_cat IS NULL))
AND ((course_cd = cp_course_cd)
OR (course_cd IS NULL and cp_course_cd IS NULL))
ORDER BY invoice_amount_due DESC;
SELECT inv.rowid row_id,
inv.invoice_id,
inv.invoice_amount_due,
inv.invoice_amount
FROM igs_fi_inv_int_all inv
WHERE inv.invoice_id = cp_invoice_id
AND inv.invoice_amount_due > 0
ORDER BY invoice_amount_due DESC;
SELECT 'X'
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;
SELECT igs_fi_inv_int_all_s1.NEXTVAL
FROM DUAL;
SELECT alternate_code , description
FROM igs_ca_inst_all
WHERE cal_type = cp_fee_cal_type
AND sequence_number = cp_fee_ci_sequence_number;
SELECT inv.rowid row_id,
inv.invoice_id invoice_id,
inv.invoice_amount_due invoice_amount_due,
inv.invoice_amount invoice_amount
FROM igs_fi_inv_int inv,
igs_fi_invln_int iln
WHERE inv.invoice_id = iln.invoice_id
AND cp_invoice_id IS NULL
AND inv.person_id = cp_person_id
AND inv.fee_type = cp_fee_type
AND inv.fee_cal_type = cp_fee_cal_type
AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND inv.transaction_type = cp_transaction_type
AND iln.error_account <> g_ind_yes
AND ((iln.uoo_id = cp_uoo_id)
OR (iln.uoo_id IS NULL AND cp_uoo_id IS NULL))
AND ((iln.location_cd = cp_location_cd)
OR (iln.location_cd IS NULL AND cp_location_cd IS NULL))
AND ((fee_cat = cp_fee_cat)
OR (fee_cat IS NULL and cp_fee_cat IS NULL))
AND ((course_cd = cp_course_cd)
OR (course_cd IS NULL and cp_course_cd IS NULL))
ORDER BY invoice_id ASC;
SELECT inv.rowid row_id,
inv.invoice_id,
inv.invoice_amount_due,
inv.invoice_amount
FROM igs_fi_inv_int_all inv
WHERE inv.invoice_id = cp_invoice_id
ORDER BY invoice_id ASC;
SELECT SUM(amount_applied)
FROM igs_fi_credits fc,
igs_fi_cr_types crt,
igs_fi_applications app
WHERE app.invoice_id = cp_invoice_id
AND app.credit_id = fc.credit_id
AND fc.status = g_cleared
AND fc.credit_type_id = crt.credit_type_id
AND crt.credit_class = g_neg_cr_class;
x_line_id_tbl.DELETE;
igs_fi_inv_int_pkg.insert_row(x_rowid => l_rowid,
x_invoice_id => l_invoice_id,
x_person_id => p_header_rec.p_person_id,
x_fee_type => p_header_rec.p_fee_type,
x_fee_cat => p_header_rec.p_fee_cat,
x_fee_cal_type => p_header_rec.p_fee_cal_type,
x_fee_ci_sequence_number => p_header_rec.p_fee_ci_sequence_number,
x_course_cd => p_header_rec.p_course_cd,
x_attendance_mode => p_header_rec.p_attendance_mode,
x_attendance_type => p_header_rec.p_attendance_type,
x_invoice_amount_due => l_n_invoice_amount,
x_invoice_creation_date => p_header_rec.p_invoice_creation_date,
x_invoice_desc => p_header_rec.p_invoice_desc,
x_transaction_type => p_header_rec.p_transaction_type,
x_currency_cd => p_header_rec.p_currency_cd,
x_status => 'TODO',
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_invoice_amount => l_n_invoice_amount,
x_bill_id => NULL,
x_bill_number => NULL,
x_bill_date => NULL,
x_waiver_flag => p_header_rec.p_waiver_flag, -- Enh BUG 2030448, Removed Balance Flag Column
x_waiver_reason => p_header_rec.p_waiver_reason,
x_effective_date => p_header_rec.p_effective_date,
x_invoice_number => l_n_get_nextval,
x_exchange_rate => 1, -- Always passed as 1
x_org_id => l_org_id,
x_bill_payment_due_date => NULL,
x_optional_fee_flag => NULL,
x_reversal_gl_date => NULL,
x_tax_year_code => NULL,
x_waiver_name => p_header_rec.p_waiver_name --Enh 3392095 Added waiver_name
);
igs_fi_invln_int_pkg.insert_row(x_rowid => l_rowid,
x_invoice_lines_id => l_invoice_lines_id,
x_invoice_id => l_invoice_id,
x_line_number => l_var,
x_chg_elements => p_line_tbl(l_var).p_chg_elements,
x_s_chg_method_type => p_line_tbl(l_var).p_s_chg_method_type,
x_description => p_line_tbl(l_var).p_description,
x_amount => l_n_amount,
x_unit_attempt_status => p_line_tbl(l_var).p_unit_attempt_status,
x_credit_points => p_line_tbl(l_var).p_credit_points,
x_eftsu => p_line_tbl(l_var).p_eftsu,
x_org_unit_cd => p_line_tbl(l_var).p_org_unit_cd,
x_attribute_category => p_line_tbl(l_var).p_attribute_category,
x_attribute1 => p_line_tbl(l_var).p_attribute1,
x_attribute2 => p_line_tbl(l_var).p_attribute2,
x_attribute3 => p_line_tbl(l_var).p_attribute3,
x_attribute4 => p_line_tbl(l_var).p_attribute4,
x_attribute5 => p_line_tbl(l_var).p_attribute5,
x_attribute6 => p_line_tbl(l_var).p_attribute6,
x_attribute7 => p_line_tbl(l_var).p_attribute7,
x_attribute8 => p_line_tbl(l_var).p_attribute8,
x_attribute9 => p_line_tbl(l_var).p_attribute9,
x_attribute10 => p_line_tbl(l_var).p_attribute10,
x_attribute11 => p_line_tbl(l_var).p_attribute11,
x_attribute12 => p_line_tbl(l_var).p_attribute12,
x_attribute13 => p_line_tbl(l_var).p_attribute13,
x_attribute14 => p_line_tbl(l_var).p_attribute14,
x_attribute15 => p_line_tbl(l_var).p_attribute15,
x_attribute16 => p_line_tbl(l_var).p_attribute16,
x_attribute17 => p_line_tbl(l_var).p_attribute17,
x_attribute18 => p_line_tbl(l_var).p_attribute18,
x_attribute19 => p_line_tbl(l_var).p_attribute19,
x_attribute20 => p_line_tbl(l_var).p_attribute20,
x_org_id => l_org_id,
x_rec_account_cd => l_dr_account_cd,
x_rev_account_cd => l_cr_account_cd,
x_rec_gl_ccid => l_dr_gl_ccid,
x_rev_gl_ccid => l_cr_gl_ccid,
x_posting_id => NULL,
x_error_string => SUBSTR(l_error_string,1,1000),
x_error_account => l_error_account,
x_location_cd => p_line_tbl(l_var).p_location_cd,
x_uoo_id => p_line_tbl(l_var).p_uoo_id,
x_gl_date => p_line_tbl(1).p_d_gl_date,
x_gl_posted_date => NULL,
x_posting_control_id => NULL,
x_unit_type_id => p_line_tbl(l_var).p_unit_type_id,
x_unit_level => p_line_tbl(l_var).p_unit_level
);
igs_fi_prc_balances.update_balances ( p_party_id => p_header_rec.p_person_id,
p_balance_type => g_standard,
p_balance_date => TRUNC(p_header_rec.p_invoice_creation_date),
p_amount => l_n_invoice_amount,
p_source => g_charge,
p_source_id => l_invoice_id,
p_message_name => l_message_name);
igs_fi_prc_balances.update_balances ( p_party_id => p_header_rec.p_person_id,
p_balance_type => l_hold_bal_type,
p_balance_date => TRUNC(p_header_rec.p_invoice_creation_date),
p_amount => l_n_invoice_amount,
p_source => g_charge,
p_source_id => l_invoice_id,
p_message_name => l_message_name);
SELECT 'x'
FROM igs_fi_credits_all fc,
igs_fi_cr_types_all crt,
igs_fi_applications app
WHERE app.invoice_id = cp_invoice_id
AND app.credit_id = fc.credit_id
AND fc.status = g_cleared
AND fc.credit_type_id = crt.credit_type_id
AND crt.credit_class = g_neg_cr_class
AND app.amount_applied = cp_inv_amt
AND ROWNUM = 1;