[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
abshriva 12-May-2006 Bug 5217319: Amount precision change in insert_gl_int
sapanigr 05-May-2006 Bug 5178077: Modified procedure transfer to disable process in R12.
bannamal 05-Jul-2005 Enh# 3392095, Tuition Waivers Build.
Modified functions get_crd_cat, get_inv_cat, get_app_cat, validate_parm
and procedures transfer_credit, transfer_app for this build.
svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
vvutukur 11-Dec-2003 Bug#3310756.Modified procedures transfer_credit,igs_ad_appl.
shtatiko 18-NOV-2003 Enh# 3256915, modified get_crd_cat and get_app_cat
vvutukur 09-Oct-2003 Bug#3160036. Modified procedure transfer_admapp.
pathipat 14-Jun-2003 Enh 2831587 - Credit Card Fund Transfer build
Modified transfer_admapp() - modified call to igs_ad_app_req_pkg.update_row()
shtatiko 22-APR-2003 Enh# 2831569, Modified validate_parm.
agairola 27-Jan-2003 Bug 2711195: Modified the generate_log and transfer procedures
agairola 02-Jan-2003 Bug 2714777,2727324: Modified the process for the following
1. In the validate_parm procedure, if the rec_installed is set to N
then after logging the parameters, the process exits.
2. In the validate_parm procedure, the message name changed if the
run journal import is set to Y and the start date is in a period that
is closed.
vchappid 20-Dec-2002 Bug 2720702: In the procedure transfer_charge, for cursor cur_chg,
NVL is missing while checking for Error Account. When the Error Account
is set to NULL, it has to be treated as a valid transaction
i.e. error_account is treated as 'N'
agairola 16-Dec-02 Bug 2584741: Added the code for the Deposits in get_crd_cat
********************************************************************************************** */
g_v_rec_inst igs_fi_control.rec_installed%TYPE;
SELECT user_je_source_name
FROM gl_je_sources
WHERE je_source_name = cp_je_src_name;
SELECT crtype.credit_class
FROM igs_fi_credits_all crd,
igs_fi_cr_types_all crtype
WHERE crd.credit_type_id = crtype.credit_type_id
AND crd.credit_id = cp_n_credit_id;
SELECT inv.transaction_type
FROM igs_fi_inv_int_all inv,
igs_fi_applications app
WHERE inv.invoice_id = app.invoice_id
AND app.credit_id = cp_n_credit_id;
SELECT crtype.credit_class
FROM igs_fi_credits_all crd,
igs_fi_cr_types_all crtype
WHERE crd.credit_type_id = crtype.credit_type_id
AND crd.credit_id = cp_n_credit_id;
SELECT inv.transaction_type
FROM igs_fi_inv_int_all inv
WHERE inv.invoice_id = cp_n_invoice_id;
SELECT IGS_FI_POSTING_CONTROL_S.NEXTVAL
FROM dual;
SELECT party_number
FROM hz_parties hzp
WHERE party_id = cp_party_id;
PROCEDURE insert_gl_int(p_d_gl_date DATE,
p_user_cat_name VARCHAR2,
p_dr_ccid NUMBER,
p_cr_ccid NUMBER,
p_amount NUMBER,
p_ref23 NUMBER,
p_ref30 VARCHAR2,
p_desc VARCHAR2) AS
/***********************************************************************************************
Created By : Amit Gairola
Date Created By: 1-Nov-2002
Purpose : This procedure will create the records in the GL Interface table
Known limitations,enhancements,remarks:
Change History
Who When What
abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
********************************************************************************************** */
l_n_dr_ccid gl_interface.code_combination_id%TYPE;
SELECT user_je_category_name
FROM gl_je_categories
WHERE je_category_name = cp_je_cat;
INSERT INTO gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference10,
reference23,
reference30,
group_id)
VALUES (g_v_new,
g_n_sob_id,
p_d_gl_date,
g_v_currency_cd,
sysdate,
l_n_user_id,
g_v_actual,
l_v_user_je_cat,
g_v_user_je_src_name,
l_n_dr_ccid,
l_n_amnt,
NULL,
l_n_amnt,
NULL,
to_char(g_n_batch_id),
p_desc,
p_ref23,
p_ref30,
g_n_batch_id);
INSERT INTO gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference10,
reference23,
reference30,
group_id)
VALUES (g_v_new,
g_n_sob_id,
p_d_gl_date,
g_v_currency_cd,
sysdate,
l_n_user_id,
g_v_actual,
l_v_user_je_cat,
g_v_user_je_src_name,
l_n_cr_ccid,
NULL,
l_n_amnt,
NULL,
l_n_amnt,
to_char(g_n_batch_id),
p_desc,
p_ref23,
p_ref30,
g_n_batch_id);
END insert_gl_int;
SELECT cra.rowid,cra.*,
crd.credit_number credit_number,
crd.party_id party_id
FROM igs_fi_cr_activities cra,
igs_fi_credits crd
WHERE cra.gl_date IS NOT NULL
AND ((cra.POSTING_ID IS NULL) AND (cra.POSTING_CONTROL_ID IS NULL))
AND TRUNC(cra.gl_date) >= TRUNC(cp_gl_date_start)
AND TRUNC(cra.gl_date) <= TRUNC(cp_gl_date_end)
AND cra.credit_id = crd.credit_id
AND cra.dr_gl_ccid IS NOT NULL
AND cra.cr_gl_ccid IS NOT NULL
AND cra.status <> g_v_transferred;
insert_gl_int(p_d_gl_date => crdrec.gl_date,
p_user_cat_name => l_v_crd_cat,
p_dr_ccid => crdrec.dr_gl_ccid,
p_cr_ccid => crdrec.cr_gl_ccid,
p_amount => crdrec.amount,
p_ref23 => crdrec.credit_activity_id,
p_ref30 => 'IGS_FI_CR_ACTIVITIES',
p_desc => l_v_crd_desc);
igs_fi_cr_activities_pkg.update_row(x_rowid => crdrec.rowid,
x_credit_activity_id => crdrec.credit_activity_id,
x_credit_id => crdrec.credit_id,
x_status => crdrec.status,
x_transaction_date => crdrec.transaction_date,
x_amount => crdrec.amount,
x_dr_account_cd => crdrec.dr_account_cd,
x_cr_account_cd => crdrec.cr_account_cd,
x_dr_gl_ccid => crdrec.dr_gl_ccid,
x_cr_gl_ccid => crdrec.cr_gl_ccid,
x_bill_id => crdrec.bill_id,
x_bill_number => crdrec.bill_number,
x_bill_date => crdrec.bill_date,
x_posting_id => crdrec.posting_id,
x_posting_control_id => g_n_batch_id,
x_gl_date => crdrec.gl_date,
x_gl_posted_date => p_d_gl_date_posted);
SELECT ln.*,
inv.invoice_number,
inv.transaction_type,
inv.person_id
FROM igs_fi_invln_int ln,
igs_fi_inv_int inv
WHERE ln.invoice_id = inv.invoice_id
AND ln.gl_date IS NOT NULL
AND TRUNC(ln.gl_date) >= TRUNC(cp_gl_date_start)
AND TRUNC(ln.gl_date) <= TRUNC(cp_gl_date_end)
AND NVL(ln.error_account,'N') = 'N'
AND ((ln.posting_id IS NULL) AND (ln.posting_control_id IS NULL));
insert_gl_int(p_d_gl_date => chgrec.gl_date,
p_user_cat_name => l_v_inv_cat,
p_dr_ccid => chgrec.rec_gl_ccid,
p_cr_ccid => chgrec.rev_gl_ccid,
p_amount => chgrec.amount,
p_ref23 => chgrec.invoice_lines_id,
p_ref30 => 'IGS_FI_INVLN_INT',
p_desc => l_v_inv_desc);
igs_fi_invln_int_pkg.update_row(x_rowid => chgrec.row_id,
x_invoice_id => chgrec.invoice_id,
x_line_number => chgrec.line_number,
x_invoice_lines_id => chgrec.invoice_lines_id,
x_attribute2 => chgrec.attribute2,
x_chg_elements => chgrec.chg_elements,
x_amount => chgrec.amount,
x_unit_attempt_status => chgrec.unit_attempt_status,
x_eftsu => chgrec.eftsu,
x_credit_points => chgrec.credit_points,
x_attribute_category => chgrec.attribute_category,
x_attribute1 => chgrec.attribute1,
x_s_chg_method_type => chgrec.s_chg_method_type,
x_description => chgrec.description,
x_attribute3 => chgrec.attribute3,
x_attribute4 => chgrec.attribute4,
x_attribute5 => chgrec.attribute5,
x_attribute6 => chgrec.attribute6,
x_attribute7 => chgrec.attribute7,
x_attribute8 => chgrec.attribute8,
x_attribute9 => chgrec.attribute9,
x_attribute10 => chgrec.attribute10,
x_rec_account_cd => chgrec.rec_account_cd,
x_rev_account_cd => chgrec.rev_account_cd,
x_rec_gl_ccid => chgrec.rec_gl_ccid,
x_rev_gl_ccid => chgrec.rev_gl_ccid,
x_org_unit_cd => chgrec.org_unit_cd,
x_posting_id => chgrec.posting_id,
x_attribute11 => chgrec.attribute11,
x_attribute12 => chgrec.attribute12,
x_attribute13 => chgrec.attribute13,
x_attribute14 => chgrec.attribute14,
x_attribute15 => chgrec.attribute15,
x_attribute16 => chgrec.attribute16,
x_attribute17 => chgrec.attribute17,
x_attribute18 => chgrec.attribute18,
x_attribute19 => chgrec.attribute19,
x_attribute20 => chgrec.attribute20,
x_error_string => chgrec.error_string,
x_error_account => chgrec.error_account,
x_location_cd => chgrec.location_cd,
x_uoo_id => chgrec.uoo_id,
x_gl_date => chgrec.gl_date,
x_posting_control_id => g_n_batch_id,
x_gl_posted_date => p_d_gl_date_posted,
x_unit_type_id => chgrec.unit_type_id,
x_unit_level => chgrec.unit_level);
SELECT app.*,app.rowid row_id,
inv.invoice_number,
crd.credit_number,
inv.person_id
FROM igs_fi_applications app,
igs_fi_credits crd,
igs_fi_inv_int inv
WHERE crd.credit_id = app.credit_id
AND inv.invoice_id = app.invoice_id
AND app.gl_date IS NOT NULL
AND TRUNC(app.gl_date) >= TRUNC(cp_gl_date_start)
AND TRUNC(app.gl_date) <= TRUNC(cp_gl_date_end)
AND ((app.posting_id IS NULL) AND (app.posting_control_id IS NULL));
insert_gl_int(p_d_gl_date => apprec.gl_date,
p_user_cat_name => l_v_app_cat,
p_dr_ccid => apprec.dr_gl_code_ccid,
p_cr_ccid => apprec.cr_gl_code_ccid,
p_amount => apprec.amount_applied,
p_ref23 => apprec.application_id,
p_ref30 => 'IGS_FI_APPLICATIONS',
p_desc => l_v_app_desc);
igs_fi_applications_pkg.update_row(x_rowid => apprec.row_id,
x_application_id => apprec.application_id,
x_application_type => apprec.application_type,
x_invoice_id => apprec.invoice_id,
x_credit_id => apprec.credit_id,
x_credit_activity_id => apprec.credit_activity_id,
x_amount_applied => apprec.amount_applied,
x_apply_date => apprec.apply_date,
x_link_application_id => apprec.link_application_id,
x_dr_account_cd => apprec.dr_account_cd,
x_cr_account_cd => apprec.cr_account_cd,
x_dr_gl_code_ccid => apprec.dr_gl_code_ccid,
x_cr_gl_code_ccid => apprec.cr_gl_code_ccid,
x_applied_invoice_lines_id => apprec.applied_invoice_lines_id,
x_appl_hierarchy_id => apprec.appl_hierarchy_id,
x_posting_id => apprec.posting_id,
x_gl_date => apprec.gl_date,
x_gl_posted_date => p_d_gl_date_posted,
x_posting_control_id => g_n_batch_id);
vvutukur 09-Oct-2003 Bug#3160036.Replaced the call to igs_ad_app_req.update_row with
the call to igs_ad_gen_015.update_igs_ad_app_req.
pathipat 14-Jun-2003 Enh 2831587 - Credit Card Fund Transfer build
Modified call to igs_ad_app_req_pkg.update_row - added 3 new parameters
********************************************************************************************** */
l_b_exception_flag BOOLEAN;
SELECT fee.*, fee.rowid row_id, appl.application_id
FROM igs_ad_app_req fee,
igs_ad_appl appl
WHERE appl.person_id = fee.person_id
AND appl.admission_appl_number = fee.admission_appl_number
AND fee.gl_date IS NOT NULL
AND fee.posting_control_id IS NULL
AND fee.gl_posted_date IS NULL
AND fee.rev_gl_ccid IS NOT NULL
AND fee.cash_gl_ccid IS NOT NULL
AND TRUNC(fee.gl_date) >= TRUNC(cp_gl_date_start)
AND TRUNC(fee.gl_date) <= TRUNC(cp_gl_date_end);
insert_gl_int(p_d_gl_date => admrec.gl_date,
p_user_cat_name => l_v_adm_cat,
p_dr_ccid => admrec.cash_gl_ccid,
p_cr_ccid => admrec.rev_gl_ccid,
p_amount => admrec.fee_amount,
p_ref23 => admrec.app_req_id,
p_ref30 => 'IGS_AD_APP_REQ',
p_desc => l_v_adm_desc);
igs_ad_gen_015.update_igs_ad_app_req(
p_rowid => admrec.row_id,
p_app_req_id => admrec.app_req_id,
p_person_id => admrec.person_id,
p_admission_appl_number => admrec.admission_appl_number,
p_applicant_fee_type => admrec.applicant_fee_type,
p_applicant_fee_status => admrec.applicant_fee_status,
p_fee_date => admrec.fee_date,
p_fee_payment_method => admrec.fee_payment_method,
p_fee_amount => admrec.fee_amount,
p_reference_num => admrec.reference_num,
p_credit_card_code => admrec.credit_card_code,
p_credit_card_holder_name => admrec.credit_card_holder_name,
p_credit_card_number => admrec.credit_card_number,
p_credit_card_expiration_date => admrec.credit_card_expiration_date,
p_rev_gl_ccid => admrec.rev_gl_ccid,
p_cash_gl_ccid => admrec.cash_gl_ccid,
p_rev_account_cd => admrec.rev_account_cd,
p_cash_account_cd => admrec.cash_account_cd,
p_posting_control_id => g_n_batch_id,
p_gl_date => admrec.gl_date,
p_gl_posted_date => p_d_gl_date_posted,
p_credit_card_tangible_cd => admrec.credit_card_tangible_cd,
p_credit_card_payee_cd => admrec.credit_card_payee_cd,
p_credit_card_status_code => admrec.credit_card_status_code,
p_mode => 'R'
);
Modified the cursor cur_ctrl.Added post_waiver_gl_flag in the select clause.
shtatiko 22-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option.
agairola 02-Jan-2003 Bug 2714777,2727324: Modified the process for the following
1. In the validate_parm procedure, if the rec_installed is set to N
then after logging the parameters, the process exits.
2. In the validate_parm procedure, the message name changed if the
run journal import is set to Y and the start date is in a period that
is closed.
********************************************************************************************** */
l_b_val_parm BOOLEAN;
SELECT rec_installed,
currency_cd,
set_of_books_id,
accounting_method,
post_waiver_gl_flag
FROM igs_fi_control;
SELECT name
FROM gl_sets_of_books
WHERE set_of_books_id = cp_sob_id;
SELECT period_name, end_date
FROM gl_period_statuses a
WHERE TRUNC(start_date) <= TRUNC(cp_gl_date_start)
AND TRUNC(end_date) >= TRUNC(cp_gl_date_start)
AND CLOSING_STATUS = 'O'
AND APPLICATION_ID = cp_app_id
AND adjustment_period_flag = 'N'
AND set_of_books_id = cp_sob_id;
/* gl_interface_control_pkg.insert_row(xset_of_books_id => g_n_sob_id,
xinterface_run_id => l_n_unique_id,
xje_source_name => g_v_je_source_name,
xgroup_id => g_n_batch_id,
xpacket_id => NULL); */
SELECT user_je_category_name,
SUM(decode(entered_dr,NULL,0,entered_dr)) dr_amnt,
SUM(decode(entered_cr,NULL,0,entered_cr)) cr_amnt
FROM gl_interface
WHERE group_id = cp_batch_id
AND set_of_books_id = cp_sob_id
AND user_je_source_name = cp_source_name
GROUP BY user_je_category_name
ORDER BY user_je_category_name;