[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sykrishn 03-JUL-02 Bug 2442163 - Procedure finp_imp_calc_anc_charges modified to insert sysdate to
transaction_dt in the table igs_fi_impchgs_lines table
Reference to anc_int table.transaction_dt is removed as it will never be present.
Removed transaction_dt from igs_fi_anc_int_pkg.update_row
Call to charges api changed to pass sysdate for invoice creation date instead of transaction_dt
sykrishn 10-JUL-02 Bug 2454128 - Procedure finp_imp_calc_anc_charges
Call to charges api changed to pass Fee Type Description for invoice description
(l_header_rec.p_invoice_desc) instead of null.
*************************************************************************************************************/
g_ancillary CONSTANT VARCHAR2(30) := 'ANCILLARY';
SELECT ancillary_chg_rate
FROM igs_fi_anc_rates
WHERE fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_sequence_number
AND fee_type = cp_fee_type
AND NVL(enabled_flag,'N') = 'Y'
AND ((ancillary_attribute1 = cp_ancillary_attribute1)
OR ((cp_ancillary_attribute1 IS NULL) AND (ancillary_attribute1 IS NULL)))
AND ((ancillary_attribute2 = cp_ancillary_attribute2)
OR ((cp_ancillary_attribute2 IS NULL)AND (ancillary_attribute2 IS NULL)))
AND ((ancillary_attribute3 = cp_ancillary_attribute3)
OR ((cp_ancillary_attribute3 IS NULL) AND (ancillary_attribute3 IS NULL)))
AND ((ancillary_attribute4 = cp_ancillary_attribute4)
OR ((cp_ancillary_attribute4 IS NULL) AND (ancillary_attribute4 IS NULL)))
AND ((ancillary_attribute5 = cp_ancillary_attribute5)
OR ((cp_ancillary_attribute5 IS NULL) AND (ancillary_attribute5 IS NULL)))
AND ((ancillary_attribute6 = cp_ancillary_attribute6)
OR ((cp_ancillary_attribute6 IS NULL) AND (ancillary_attribute6 IS NULL)))
AND ((ancillary_attribute7 = cp_ancillary_attribute7)
OR ((cp_ancillary_attribute7 IS NULL) AND (ancillary_attribute7 IS NULL)))
AND ((ancillary_attribute8 = cp_ancillary_attribute8)
OR ((cp_ancillary_attribute8 IS NULL) AND (ancillary_attribute8 IS NULL)))
AND ((ancillary_attribute9 = cp_ancillary_attribute9)
OR ((cp_ancillary_attribute9 IS NULL) AND (ancillary_attribute9 IS NULL)))
AND ((ancillary_attribute10 = cp_ancillary_attribute10)
OR ((cp_ancillary_attribute10 IS NULL) AND (ancillary_attribute10 IS NULL)))
AND ((ancillary_attribute11 = cp_ancillary_attribute11)
OR ((cp_ancillary_attribute11 IS NULL) AND (ancillary_attribute11 IS NULL)))
AND ((ancillary_attribute12 = cp_ancillary_attribute12)
OR ((cp_ancillary_attribute12 IS NULL) AND (ancillary_attribute12 IS NULL)))
AND ((ancillary_attribute13 = cp_ancillary_attribute13)
OR ((cp_ancillary_attribute13 IS NULL) AND (ancillary_attribute13 IS NULL)))
AND ((ancillary_attribute14 = cp_ancillary_attribute14)
OR ((cp_ancillary_attribute14 IS NULL) AND (ancillary_attribute14 IS NULL)))
AND ((ancillary_attribute15 = cp_ancillary_attribute15)
OR ((cp_ancillary_attribute15 IS NULL) AND (ancillary_attribute15 IS NULL)));
SELECT 'x'
FROM igs_ca_type
WHERE cal_type = cp_cal_type;
SELECT 'x'
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_ci_sequence_number;
SELECT 'x'
FROM igs_fi_fee_type
WHERE fee_type = cp_fee_type
AND s_fee_type = g_ancillary
AND NVL(closed_ind,'N') = 'N';
SELECT 'x'
FROM igs_fi_f_typ_ca_inst ftci, igs_fi_fee_str_stat fss
WHERE ftci.fee_cal_type = cp_cal_type
AND ftci.fee_ci_sequence_number = cp_ci_sequence_number
AND ftci.fee_type = cp_fee_type
AND fss.fee_structure_status = ftci.fee_type_ci_status
AND fss.s_fee_structure_status = g_active;
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_impchgs_lines
are now rounded off to currency precision
svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
Impact of Charges API version Number change
Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
pathipat 30-Sep-2003 Bug 3166888 - Modified cur_inv_int to sum invoice_amount instead of invoice_amount_due
and added 'not exists' clause
vvutukur 20-Jun-2003 Enh#2777404.Modified the logic such that all the error messages pertaining to an ancillary record
being processed from interface table are logged in the log file and error_msg field in the interface table
is updated with the concatenated error message texts.
shtatiko 29-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option. If its value is NULL then
this process cannot be run.
vvutukur 23-Jan-20033 Bug#2750566.Modified the code to update the error_msg column in Interface record with appropriate message text.
vvutukur 06-Jan-2003 Bug#2737714.Used fnd_message.set_encoded to encode the message after charges api call,if charges api returns an error.
Also shown the no. of successfully imported records instead of showing no. of records processsed from interface table.
pathipat 20-NOV-2002 Enh#2584986 - GL Interface build
1. Removed override account columns from calls to insert_row of IGS_FI_IMP_CHGS and
IGS_FI_IMPCHGS_LINES and update_row of IGS_FI_ANC_INT.
Also removed the corresponding validations and local variables.
2. Removed ext_attribute columns from calls to insert_row of IGS_FI_IMPCHGS_LINES
3. Removed cursor cur_currency and its usage. Derived local currency using generic
function igs_fi_gen_gl.finp_get_cur()
4. Passed sysdate to p_d_gl_date in call to charges_api. Passed null to override account
codes before calling charges_api.
saraskhi 13-sep-2002 Enh#2564643, removed the reference of subaccount
vvutukur 24-Jul-2002 Bug#2425767.Removed references to obsoleted columns chg_rate,chg_elements from
call to igs_fi_impchgs_lines.insert_row procedure.
sykrishn 10-JUL-02 Bug 2454128 - Procedure finp_imp_calc_anc_charges
Call to charges api changed to pass Fee Type Description for invoice description
(l_header_rec.p_invoice_desc) instead of null.
jbegum 12-Jun-02 Bug#2400189 - Added a local valriable l_rec_cntr to count the number of
records processed and log that in the log file
agairola 04-Jun-2002 Bug 2395663 - Modified the TBH call for the IGS_FI_IMPCHGS_LINES_PKG
SYKRISHN 19-APR-2002 Bug 2324088 - Introduced Desc Flex Field Validations and CCID validations.
smadathi 27-Feb-2002 Bug 2238413. Reduced selection list for
rec installed flag to 'Y' and 'N'.Removed
reference of rec installed flag = 'E'.
sarakshi 16-jan-2002 Modified the logic of fetching subaccount_id as a part of bug:2175865
********************************************************************************************** */
-- Variables of type VARCHAR2
l_error_msg VARCHAR2(2000);
SELECT rowid,
IGS_FI_ANC_INT.*
FROM IGS_FI_ANC_INT
WHERE ((fee_cal_type = cp_fee_cal_type)
OR (cp_fee_cal_type IS NULL))
AND ((fee_ci_sequence_number = cp_fee_ci_sequence_number)
OR (cp_fee_ci_sequence_number IS NULL))
AND status = g_todo
AND ((person_id = cp_person_id)
OR (cp_person_id IS NULL))
AND ((person_id_type = cp_person_id_type)
OR (cp_person_id_type IS NULL))
AND ((api_person_id = cp_api_person_id)
OR (cp_api_person_id IS NULL))
AND ((fee_type = cp_fee_type)
OR (cp_fee_type IS NULL));
SELECT pe_person_id
FROM igs_pe_alt_pers_id
WHERE person_id_type = cp_person_id_type
AND api_person_id = cp_api_person_id;
SELECT import_charges_id
FROM igs_fi_imp_chgs
WHERE person_id = cp_person_id
AND fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_sequence_number
AND fee_type = cp_fee_type
AND transaction_type = cp_transaction_type;
SELECT SUM(invoice_amount)
FROM igs_fi_inv_int inv
WHERE inv.person_id = cp_person_id
AND inv.fee_cal_type = cp_fee_cal_type
AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND inv.fee_type = cp_fee_type
AND inv.transaction_type = cp_transaction_type
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_v_cleared
AND fc.credit_type_id = crt.credit_type_id
AND crt.credit_class = g_v_chgadj
AND app.amount_applied = inv.invoice_amount);
SELECT description
FROM igs_fi_fee_type
WHERE fee_type = cp_fee_type;
igs_fi_imp_chgs_pkg.Insert_Row( x_rowid => l_rowid,
x_import_charges_id => l_import_charges_id,
x_person_id => l_person_id,
x_fee_type => ancrec.fee_type,
x_fee_cal_type => ancrec.fee_cal_type,
x_fee_ci_sequence_number => ancrec.fee_ci_sequence_number,
x_transaction_type => g_ancillary);
igs_fi_impchgs_lines_pkg.Insert_Row( x_rowid => l_rowid,
x_impchg_lines_id => l_impchgs_lines_id,
x_import_charges_id => l_import_charges_id,
x_transaction_dt => SYSDATE,
x_effective_dt => l_effective_dt,
x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(l_ancillary_chg_rate),
x_currency_cd => l_v_local_currency,
x_exchange_rate => NULL,
x_comments => NULL,
x_ancillary_attribute1 => ancrec.ancillary_attribute1,
x_ancillary_attribute2 => ancrec.ancillary_attribute2,
x_ancillary_attribute3 => ancrec.ancillary_attribute3,
x_ancillary_attribute4 => ancrec.ancillary_attribute4,
x_ancillary_attribute5 => ancrec.ancillary_attribute5,
x_ancillary_attribute6 => ancrec.ancillary_attribute6,
x_ancillary_attribute7 => ancrec.ancillary_attribute7,
x_ancillary_attribute8 => ancrec.ancillary_attribute8,
x_ancillary_attribute9 => ancrec.ancillary_attribute9,
x_ancillary_attribute10 => ancrec.ancillary_attribute10,
x_ancillary_attribute11 => ancrec.ancillary_attribute11,
x_ancillary_attribute12 => ancrec.ancillary_attribute12,
x_ancillary_attribute13 => ancrec.ancillary_attribute13,
x_ancillary_attribute14 => ancrec.ancillary_attribute14,
x_ancillary_attribute15 => ancrec.ancillary_attribute15,
x_attribute_category => ancrec.attribute_category,
x_attribute1 => ancrec.attribute1,
x_attribute2 => ancrec.attribute2,
x_attribute3 => ancrec.attribute3,
x_attribute4 => ancrec.attribute4,
x_attribute5 => ancrec.attribute5,
x_attribute6 => ancrec.attribute6,
x_attribute7 => ancrec.attribute7,
x_attribute8 => ancrec.attribute8,
x_attribute9 => ancrec.attribute9,
x_attribute10 => ancrec.attribute10,
x_attribute11 => ancrec.attribute11,
x_attribute12 => ancrec.attribute12,
x_attribute13 => ancrec.attribute13,
x_attribute14 => ancrec.attribute14,
x_attribute15 => ancrec.attribute15,
x_attribute16 => ancrec.attribute16,
x_attribute17 => ancrec.attribute17,
x_attribute18 => ancrec.attribute18,
x_attribute19 => ancrec.attribute19,
x_attribute20 => ancrec.attribute20
);
igs_fi_impchgs_lines_pkg.Insert_Row( x_rowid => l_rowid,
x_impchg_lines_id => l_impchgs_lines_id,
x_import_charges_id => l_import_charges_id,
x_transaction_dt => SYSDATE,
x_effective_dt => l_effective_dt,
x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(l_ancillary_chg_rate),
x_currency_cd => l_v_local_currency,
x_exchange_rate => NULL,
x_comments => NULL,
x_ancillary_attribute1 => ancrec.ancillary_attribute1,
x_ancillary_attribute2 => ancrec.ancillary_attribute2,
x_ancillary_attribute3 => ancrec.ancillary_attribute3,
x_ancillary_attribute4 => ancrec.ancillary_attribute4,
x_ancillary_attribute5 => ancrec.ancillary_attribute5,
x_ancillary_attribute6 => ancrec.ancillary_attribute6,
x_ancillary_attribute7 => ancrec.ancillary_attribute7,
x_ancillary_attribute8 => ancrec.ancillary_attribute8,
x_ancillary_attribute9 => ancrec.ancillary_attribute9,
x_ancillary_attribute10 => ancrec.ancillary_attribute10,
x_ancillary_attribute11 => ancrec.ancillary_attribute11,
x_ancillary_attribute12 => ancrec.ancillary_attribute12,
x_ancillary_attribute13 => ancrec.ancillary_attribute13,
x_ancillary_attribute14 => ancrec.ancillary_attribute14,
x_ancillary_attribute15 => ancrec.ancillary_attribute15,
x_attribute_category => ancrec.attribute_category,
x_attribute1 => ancrec.attribute1,
x_attribute2 => ancrec.attribute2,
x_attribute3 => ancrec.attribute3,
x_attribute4 => ancrec.attribute4,
x_attribute5 => ancrec.attribute5,
x_attribute6 => ancrec.attribute6,
x_attribute7 => ancrec.attribute7,
x_attribute8 => ancrec.attribute8,
x_attribute9 => ancrec.attribute9,
x_attribute10 => ancrec.attribute10,
x_attribute11 => ancrec.attribute11,
x_attribute12 => ancrec.attribute12,
x_attribute13 => ancrec.attribute13,
x_attribute14 => ancrec.attribute14,
x_attribute15 => ancrec.attribute15,
x_attribute16 => ancrec.attribute16,
x_attribute17 => ancrec.attribute17,
x_attribute18 => ancrec.attribute18,
x_attribute19 => ancrec.attribute19,
x_attribute20 => ancrec.attribute20
);
l_line_tbl.DELETE;
igs_fi_anc_int_pkg.update_row( x_rowid => ancrec.rowid,
x_ancillary_int_id => ancrec.ancillary_int_id,
x_person_id => ancrec.person_id,
x_person_id_type => ancrec.person_id_type,
x_api_person_id => ancrec.api_person_id,
x_status => ancrec.status,
x_fee_type => ancrec.fee_type,
x_fee_cal_type => ancrec.fee_cal_type,
x_fee_ci_sequence_number => ancrec.fee_ci_sequence_number,
x_ancillary_attribute1 => ancrec.ancillary_attribute1,
x_ancillary_attribute2 => ancrec.ancillary_attribute2,
x_ancillary_attribute3 => ancrec.ancillary_attribute3,
x_ancillary_attribute4 => ancrec.ancillary_attribute4,
x_ancillary_attribute5 => ancrec.ancillary_attribute5,
x_ancillary_attribute6 => ancrec.ancillary_attribute6,
x_ancillary_attribute7 => ancrec.ancillary_attribute7,
x_ancillary_attribute8 => ancrec.ancillary_attribute8,
x_ancillary_attribute9 => ancrec.ancillary_attribute9,
x_ancillary_attribute10 => ancrec.ancillary_attribute10,
x_ancillary_attribute11 => ancrec.ancillary_attribute11,
x_ancillary_attribute12 => ancrec.ancillary_attribute12,
x_ancillary_attribute13 => ancrec.ancillary_attribute13,
x_ancillary_attribute14 => ancrec.ancillary_attribute14,
x_ancillary_attribute15 => ancrec.ancillary_attribute15,
x_attribute_category => ancrec.attribute_category,
x_attribute1 => ancrec.attribute1,
x_attribute2 => ancrec.attribute2,
x_attribute3 => ancrec.attribute3,
x_attribute4 => ancrec.attribute4,
x_attribute5 => ancrec.attribute5,
x_attribute6 => ancrec.attribute6,
x_attribute7 => ancrec.attribute7,
x_attribute8 => ancrec.attribute8,
x_attribute9 => ancrec.attribute9,
x_attribute10 => ancrec.attribute10,
x_attribute11 => ancrec.attribute11,
x_attribute12 => ancrec.attribute12,
x_attribute13 => ancrec.attribute13,
x_attribute14 => ancrec.attribute14,
x_attribute15 => ancrec.attribute15,
x_attribute16 => ancrec.attribute16,
x_attribute17 => ancrec.attribute17,
x_attribute18 => ancrec.attribute18,
x_attribute19 => ancrec.attribute19,
x_attribute20 => ancrec.attribute20,
x_effective_dt => ancrec.effective_dt,
x_error_msg => SUBSTR(LTRIM(ancrec.error_msg,'.'),1,2000),
x_validation_flag => ancrec.validation_flag,
x_mode => 'R');
SELECT 'x'
FROM igs_pe_person_base_v ppv,
igs_pe_alt_pers_id api
WHERE ppv.person_id = api.pe_person_id
AND ppv.person_id = cp_person_id
AND api.person_id_type = cp_person_id_type
AND api.api_person_id = cp_api_person_id;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;