[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT disb_dlt.award_id,disb_dlt.disb_num,disb_dlt.disb_seq_num,disb_dlt.disb_date,
fab.person_id,fcat.fed_fund_code fed_fund_code,fnd.fee_type,fnd.party_id,fnd.spnsr_fee_type,
fcat.sys_fund_type sys_fund_type,fnd.ci_cal_type,fnd.ci_sequence_number,fnd.credit_type_id,
DECODE(disb_dlt.disb_seq_num,1,disb_dlt.disb_net_amt,disb_dlt.disb_adj_amt) amount,
NVL(disb_dlt.ld_cal_type,disb.ld_cal_type) ld_cal_type, NVL(disb_dlt.ld_sequence_number,disb.ld_sequence_number) ld_sequence_number,
fnd.fund_id, fnd.fund_code fund_code
FROM igf_db_awd_disb_dtl disb_dlt,
igf_aw_awd_disb disb,
igf_aw_award awd,
igf_aw_fund_mast fnd,
igf_aw_fund_cat fcat,
igf_ap_fa_base_rec fab
WHERE disb_dlt.award_id = disb.award_id
AND disb_dlt.disb_num = disb.disb_num
AND disb.award_id = awd.award_id
AND fnd.fund_id = awd.fund_id
AND fnd.fund_code = fcat.fund_code
AND awd.base_id = fab.base_id
AND disb_dlt.sf_status IN ('R','E')
AND fnd.ci_cal_type = cp_cal_type
AND fnd.ci_sequence_number = cp_sequence_number
AND (fnd.fund_id = cp_fund_id OR (cp_fund_id IS NULL))
AND (fab.person_id = cp_person_id OR (cp_person_id IS NULL))
AND (disb.ld_cal_type = cp_tp_cal_type OR (cp_tp_cal_type IS NULL))
AND (disb.ld_sequence_number = cp_tp_sequence_number OR (cp_tp_sequence_number IS NULL))
AND ((cp_manage_accounts = 'OTHER' and fcat.sys_fund_type = 'SPONSOR')
OR (cp_manage_accounts='STUDENT_FINANCE'));
SELECT 'X'
FROM igf_ap_award_year_v
WHERE cal_type = p_cal_type
AND sequence_number = p_sequence_number;
smadathi 01-JUL-2004 Bug 3735396. Modified the cursor cur_val select to validate
existance of input person id group from igs_pe_persid_group_all
smadathi 31-DEC-2002 Bug 2620343. Modified the cursor cur_val select to fetch
the records from view igs_pe_persid_group instead of
igs_pe_persid_group_v. This fix is done to remove
Non-mergablity due to igs_pe_persid_group_v view and to reduce shared memory
********************************************************************************************** */
CURSOR cur_val IS
SELECT 'X'
FROM igs_pe_persid_group_all
WHERE group_id = p_persid_grp_id
AND TRUNC(create_dt) <= TRUNC(SYSDATE)
AND NVL(closed_ind,'N') = 'N';
smadathi 31-dec-2002 Bug 2719776. Modified the cursor cur_val select to fetch
the records from view igf_ap_fa_base_rec instead of
igf_ap_fa_con_v. This fix is done to remove
Non-mergable view exists in the select and to reduce shared memory
within the acceptable limit
********************************************************************************************** */
CURSOR cur_val IS
SELECT 'X'
FROM igf_ap_fa_base_rec
WHERE base_id = p_base_id
AND ci_cal_type =p_cal_type
AND ci_sequence_number=p_sequence_number;
SELECT 'X'
FROM igf_aw_fund_mast
WHERE fund_id = p_fund_id
AND ci_cal_type =p_cal_type
AND ci_sequence_number=p_sequence_number;
SELECT bill_desc
FROM igf_aw_fund_mast
WHERE fund_id = cp_fund_id;
1. The cursor cur_api has been modified to select person_number
from igs_pe_person_base_v
2. For the parameter p_student_sponsor='SPONSOR', the value of the
parameter passed to cur_api is changed to p_cur_disb.person_id.
3. The credit description is modified for the sponsor transaction as
Negative Charge Adjustment : Person Number : Full Name
smadathi 31-dec-2002 Bug 2620349. Modified the cursor cur_api select to fetch
the records from view igs_fi_parties_v instead of
igs_pe_person_v. This fix is done to remove
Non-mergablity due to igs_pe_person_v view and to reduce shared memory
vvutukur 13-Dec-2002 Enh#2584741.Deposits Build.Modified the call to credits api to remove p_validation_level
parameter and add 3 new parameters p_v_check_number,p_v_source_tran_type,p_v_source_tran_ref_number.
vvutukur 20-Nov-2002 Enh#2584986.Added new IN parameter p_d_gl_date.Passed p_d_gl_date to the call to
igs_fi_credits_api_pub.create_credit.Removed references to igs_fi_cur.Instead defaulted the
currency that is set up in System Options Form and passed the same to the call to
credits api. Also exchange rate is passed as 1.
jbegum 21-Sep-2002 Bug #2564643 Modified the cursor cur_desc to select only description column
and not the subaccount_id column from igs_fi_cr_types table.
Also modified the call to igs_fi_credits_api_pub.create_credit.Removed the
parameter p_subaccount_id.
smadathi 10-JUL-2002 Bug 2450332. Call to igs_fi_credits_api_pub.create_credit modified to
pass current system date as effective date instead of disbursement date.
smadathi 03-Jun-2002 Bug 2349394. call to get_bill_desc function is made to get bill description
which is passed as parameter to credits API for all funds except sponsor.
vchappid 11-Feb-2002 Enh#2191470,Un-Commented reference to new parameters introduced in the Credits API
********************************************************************************************** */
-- Bug #2564643 Modified the cursor cur_desc to select only description column
CURSOR cur_desc(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) IS
SELECT credit_class
FROM igs_fi_cr_types_v
WHERE credit_type_id = cp_credit_type_id;
SELECT person_number,full_name
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
1. The cursor cur_api has been modified to select person_number
from igs_pe_person_base_v
2. For the parameter p_student_sponsor='SPONSOR', the value of the
parameter passed to cur_api is changed to p_cur_disb.person_id.
3. The invoice description is modified for the sponsor transaction as
Person Number : Full Name
smadathi 31-dec-2002 Bug 2620349. Modified the cursor cur_api select to fetch
the records from view igs_fi_parties_v instead of
igs_pe_person_v. This fix is done to remove
Non-mergablity due to igs_pe_person_v view and to reduce shared memory
vvutukur 20-Nov-2002 Enh#2584986.Added new IN parameter p_d_gl_date to this procedure.Passed p_d_gl_date to
the call to igs_fi_charges_api_pvt.create_charge.
jbegum 21-Sep-2002 Bug #2564643 Modified the cursor cur_desc to select only description column
and not the subaccount_id column from igs_fi_cr_types table.
Also modified the record structure being passed to igs_fi_charges_api_pvt.create_charge.
Removed the field l_chg_rec.p_subaccount_id .
smadathi 03-Jun-2002 Bug 2349394. call to get_bill_desc function is made to get bill description
which is passed as parameter to charges API for all funds except sponsor.
vchappid 11-Feb-2002 Enh#2191470, Un-Commented reference to Fee Cal parameters in the Charges API invoking
Fee Cal Parameters Derived for the Load Cal are passed for charges Creation
********************************************************************************************** */
-- Bug #2564643 Modified the cursor cur_desc to select only description column
CURSOR cur_desc(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) IS
SELECT description
FROM igs_fi_cr_types
WHERE credit_type_id = cp_credit_type_id;
SELECT person_number,full_name
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT description
FROM igs_fi_fee_type
WHERE fee_type = cp_v_fee_type;
in igf_aw_awd_disb_pkg.update_row , igf_aw_award_pkg.update_row calls
smadathi 01-JUL-2004 Bug 3735396. GSCC Warning File.Sql.35 was fixed as part of this bug. The initialization
of local variable l_status in the procedure declaration was removed and the same
was initialized at the starting of this procedure.
pathipat 22-Apr-2004 Enh 3558549 - Commercial Receivables Enhancements
Modified calls to call_credits_api() - added new parameter p_n_source_invoice_id
veramach 3-NOV-2003 FA 125 Multiple Distr Methods
Changed signature of igf_aw_award_pkg.update_row(Added adplans_id to the tbh call)
Changed signature of igf_aw_awd_disb_pkg.update_row(Added attendance_type_code to the tbh call)
pathipat 23-Aug-2003 Enh 3076768 - Automatic Release of Holds build
Added code to log message is holds release failed in call to Credits API
SMADATHI 26-jun-2003 Bug 2852816. Removed parameter p_student_sponsor from the calls to call_credits_api,call_charges_api
vvutukur 10-Apr-2003 Enh#2831554.Internal Credits API Build. Added validations for Fee,Load,Award Calendar Instances,credit type,
credit source,credit class.Added logic such that the disb. record's sf_status and error_desc fields gets updated with
appropriate values.
error description fields
shtatiko 26-MAR-2003 Bug# 2782124, Changed the logging of results from tabular format to
form layout.
vvutukur 26-Feb-2003 Enh#2758823.FA117 Build. Modified the call to igf_db_awd_disb_dtl_pkg.update_row to pass TRUNC(SYSDATE)
instead of SYSDATE for the parameter x_sf_status_date.
smadathi 31-dec-2002 Bug 2620359. Modified the cursor cur_person to fetch the person number
from igs_pe_person_base_v instead of the igs_pe_person. This is done
due to Non-Meargability and higher value of shared memory beyong the acceptable limit
vvutukur 20-Nov-2002 Enh#2584986.Added new parameter p_d_gl_date.Also modified the calls to call_charges_api
and call_credits_api to pass this p_d_gl_date parameter.Also added this p_d_gl_date
parameter to the call to igs_fi_prc_refunds.process_plus.
SYkrishn 08-MAY-2002 The column DISB_PAID_AMT in the table igf_aw_awd_disb is updated with the Cumulation of existing Disb Paid amount
with the newly disbursed amount (each iteration) instead of overriding with the new value - Bug 2356801.
sarakshi 18-Mar-2002 Bug:2144600, added logic for refunding the excess credit amount
vchappid 11-Feb-2002 Enh#2191470, Un-Commented reference to Fee Cal parameters in the Charges API invoking
Fee Cal Parameters Derived for the Load Cal are passed for charges Creation
********************************************************************************************** */
CURSOR cur_person(cp_person_id igs_pe_person_v.person_id%TYPE) IS
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id=cp_person_id;
SELECT credit_type_name
FROM igs_fi_cr_types_all
WHERE credit_type_id = cp_credit_type_id;
SELECT a.rowid,a.*
FROM igf_aw_award a
WHERE award_id=cp_award_id;
SELECT a.rowid,a.*
FROM igf_aw_awd_disb a
WHERE award_id=cp_award_id
AND disb_num=cp_disb_num;
SELECT lor.p_person_id
FROM igf_sl_loans lon ,igf_sl_lor lor
WHERE lon.award_id=cp_award_id
AND lon.loan_id=lor.loan_id;
SELECT a.rowid,a.*
FROM igf_db_awd_disb_dtl a
WHERE award_id=cp_award_id
AND disb_num=cp_disb_num
AND disb_seq_num=cp_disb_seq_num;
igf_aw_award_pkg.update_row( X_ROWID => l_rec_awd.rowid,
X_AWARD_ID => l_rec_awd.award_id,
X_FUND_ID => l_rec_awd.fund_id,
X_BASE_ID => l_rec_awd.base_id,
X_OFFERED_AMT => l_rec_awd.offered_amt,
X_ACCEPTED_AMT => l_rec_awd.accepted_amt,
X_PAID_AMT => igs_fi_gen_gl.get_formatted_amount(l_paid_amt),
X_PACKAGING_TYPE => l_rec_awd.packaging_type,
X_BATCH_ID => l_rec_awd.batch_id,
X_MANUAL_UPDATE => l_rec_awd.manual_update,
X_RULES_OVERRIDE => l_rec_awd.rules_override,
X_AWARD_DATE => l_rec_awd.award_date,
X_AWARD_STATUS => l_rec_awd.award_status,
X_ATTRIBUTE_CATEGORY => l_rec_awd.attribute_category,
X_ATTRIBUTE1 => l_rec_awd.attribute1,
X_ATTRIBUTE2 => l_rec_awd.attribute2,
X_ATTRIBUTE3 => l_rec_awd.attribute3,
X_ATTRIBUTE4 => l_rec_awd.attribute4,
X_ATTRIBUTE5 => l_rec_awd.attribute5,
X_ATTRIBUTE6 => l_rec_awd.attribute6,
X_ATTRIBUTE7 => l_rec_awd.attribute7,
X_ATTRIBUTE8 => l_rec_awd.attribute8,
X_ATTRIBUTE9 => l_rec_awd.attribute9,
X_ATTRIBUTE10 => l_rec_awd.attribute10,
X_ATTRIBUTE11 => l_rec_awd.attribute11,
X_ATTRIBUTE12 => l_rec_awd.attribute12,
X_ATTRIBUTE13 => l_rec_awd.attribute13,
X_ATTRIBUTE14 => l_rec_awd.attribute14,
X_ATTRIBUTE15 => l_rec_awd.attribute15,
X_ATTRIBUTE16 => l_rec_awd.attribute16,
X_ATTRIBUTE17 => l_rec_awd.attribute17,
X_ATTRIBUTE18 => l_rec_awd.attribute18,
X_ATTRIBUTE19 => l_rec_awd.attribute19,
X_ATTRIBUTE20 => l_rec_awd.attribute20,
X_RVSN_ID => l_rec_awd.rvsn_id,
x_ALT_PELL_SCHEDULE => l_rec_awd.alt_pell_schedule,
X_MODE => 'R',
X_AWARD_NUMBER_TXT => l_rec_awd.award_number_txt,
X_LEGACY_RECORD_FLAG => NULL,
x_adplans_id => l_rec_awd.adplans_id,
x_lock_award_flag => l_rec_awd.lock_award_flag,
x_app_trans_num_txt => l_rec_awd.app_trans_num_txt,
x_awd_proc_status_code => l_rec_awd.awd_proc_status_code,
x_notification_status_code => l_rec_awd.notification_status_code,
x_notification_status_date => l_rec_awd.notification_status_date,
x_publish_in_ss_flag => l_rec_awd.publish_in_ss_flag
);
The column DISB_PAID_AMT in the table igf_aw_awd_disb is updated with the Cumulation of existing Disb Paid amount
with the newly disbursed amount (each iteration) instead of overriding with the new value - Bug 2356801.
*/
l_disb_paid_amt := NVL(l_rec_awd_disb.disb_paid_amt,0) + p_rec_disb.amount;
Thus X_DISB_DATE should always be updated with SYSDATE.
*/
BEGIN
-- Bug 5217319 Added call to format amount by rounding off to currency precision for l_disb_paid_amt
igf_aw_awd_disb_pkg.update_row( X_ROWID => l_rec_awd_disb.rowid,
X_AWARD_ID => l_rec_awd_disb.award_id,
X_DISB_NUM => l_rec_awd_disb.disb_num,
X_TP_CAL_TYPE => l_rec_awd_disb.tp_cal_type,
X_TP_SEQUENCE_NUMBER => l_rec_awd_disb.tp_sequence_number,
X_DISB_GROSS_AMT => l_rec_awd_disb.disb_gross_amt,
X_FEE_1 => l_rec_awd_disb.fee_1,
X_FEE_2 => l_rec_awd_disb.fee_2,
X_DISB_NET_AMT => l_rec_awd_disb.disb_net_amt,
X_DISB_DATE => TRUNC(SYSDATE),
X_TRANS_TYPE => l_rec_awd_disb.trans_type,
X_ELIG_STATUS => l_rec_awd_disb.elig_status,
X_ELIG_STATUS_DATE => l_rec_awd_disb.elig_status_date,
X_AFFIRM_FLAG => l_rec_awd_disb.affirm_flag,
X_HOLD_REL_IND => l_rec_awd_disb.hold_rel_ind,
X_MANUAL_HOLD_IND => l_rec_awd_disb.manual_hold_ind,
X_DISB_STATUS => l_rec_awd_disb.disb_status,
X_DISB_STATUS_DATE => l_rec_awd_disb.disb_status_date,
X_LATE_DISB_IND => l_rec_awd_disb.late_disb_ind,
X_FUND_DIST_MTHD => l_rec_awd_disb.fund_dist_mthd,
X_PREV_REPORTED_IND => l_rec_awd_disb.prev_reported_ind,
X_FUND_RELEASE_DATE => l_rec_awd_disb.fund_release_date,
X_FUND_STATUS => l_rec_awd_disb.fund_status,
X_FUND_STATUS_DATE => l_rec_awd_disb.fund_status_date,
X_FEE_PAID_1 => l_rec_awd_disb.fee_paid_1,
X_FEE_PAID_2 => l_rec_awd_disb.fee_paid_2,
X_CHEQUE_NUMBER => l_rec_awd_disb.cheque_number,
X_LD_CAL_TYPE => l_rec_awd_disb.ld_cal_type,
X_LD_SEQUENCE_NUMBER => l_rec_awd_disb.ld_sequence_number,
X_DISB_ACCEPTED_AMT => l_rec_awd_disb.disb_accepted_amt,
X_DISB_PAID_AMT => igs_fi_gen_gl.get_formatted_amount(l_disb_paid_amt),
X_RVSN_ID => l_rec_awd_disb.rvsn_id,
X_INT_REBATE_AMT => l_rec_awd_disb.int_rebate_amt,
X_FORCE_DISB => l_rec_awd_disb.force_disb,
X_MIN_CREDIT_PTS => l_rec_awd_disb.min_credit_pts,
X_DISB_EXP_DT => l_rec_awd_disb.disb_exp_dt,
X_VERF_ENFR_DT => l_rec_awd_disb.verf_enfr_dt,
X_FEE_CLASS => l_rec_awd_disb.fee_class,
X_SHOW_ON_BILL => l_rec_awd_disb.show_on_bill,
X_MODE => 'R',
x_attendance_type_code => l_rec_awd_disb.attendance_type_code,
x_base_attendance_type_code => l_rec_awd_disb.base_attendance_type_code,
x_payment_prd_st_date => l_rec_awd_disb.payment_prd_st_date,
x_change_type_code => l_rec_awd_disb.change_type_code,
x_fund_return_mthd_code => l_rec_awd_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_rec_awd_disb.direct_to_borr_flag
);
l_error_desc:=fnd_message.get_string('IGF','IGF_DB_UPDATE_FAILED');
igf_db_awd_disb_dtl_pkg.update_row( X_ROWID => l_rec_disb_dtl.rowid,
X_AWARD_ID => l_rec_disb_dtl.award_id,
X_DISB_NUM => l_rec_disb_dtl.disb_num,
X_DISB_SEQ_NUM => l_rec_disb_dtl.disb_seq_num,
X_DISB_GROSS_AMT => l_rec_disb_dtl.disb_gross_amt,
X_FEE_1 => l_rec_disb_dtl.fee_1,
X_FEE_2 => l_rec_disb_dtl.fee_2,
X_DISB_NET_AMT => l_rec_disb_dtl.disb_net_amt,
X_DISB_ADJ_AMT => l_rec_disb_dtl.disb_adj_amt,
X_DISB_DATE => l_rec_disb_dtl.disb_date,
X_FEE_PAID_1 => l_rec_disb_dtl.fee_paid_1,
X_FEE_PAID_2 => l_rec_disb_dtl.fee_paid_2,
X_DISB_ACTIVITY => l_rec_disb_dtl.disb_activity,
X_DISB_BATCH_ID => NULL, -- obsolete
X_DISB_ACK_DATE => NULL, -- obsolete
X_BOOKING_BATCH_ID => NULL, -- obsolete
X_BOOKED_DATE => NULL, -- obsolete
X_DISB_STATUS => NULL, -- obsolete
X_DISB_STATUS_DATE => NULL, -- obsolete
X_SF_STATUS => l_status_code,
X_SF_STATUS_DATE => TRUNC(SYSDATE),
X_SF_INVOICE_NUM => l_sf_invoice_num,
X_SF_CREDIT_ID => l_sf_credit_id,
X_SPNSR_CREDIT_ID => l_spnsr_credit_id,
X_SPNSR_CHARGE_ID => l_spnsr_charge_id,
X_ERROR_DESC => l_error_desc,
X_MODE => 'R' ,
x_NOTIFICATION_DATE => l_rec_disb_dtl.notification_date,
X_INTEREST_REBATE_AMT => l_rec_disb_dtl.interest_rebate_amt,
x_ld_cal_type => l_rec_disb_dtl.ld_cal_type,
x_ld_sequence_number => l_rec_disb_dtl.ld_sequence_number
);
sapanigr 16-SEP-2005 Modified the cursor, cur_fund to select the fund_code rather than description
for the bug# 3810157
smadathi 01-JUL-2004 Bug 3735396. The logic to handle both static and dynamic person group id
incorporated.
vvutukur 18-Jul-2003 Enh#3038511.FICR106 Build. Added call to generic procedure
igs_fi_crdapi_util.get_award_year_status to validate Award Year Status.
rasahoo 30-june-2003 Removed the cursor cur_ld_cal as it is not used any where.
shtatiko 02-MAY-2003 Enh# 2831569, Added check for Manage Accounts System Option before running this process.
If its value is NULL then process will error out.
vvutukur 11-Apr-2003 Enh#2831554.Internal Credits API Build. Added validations for currency code and credit instrument 'AID'.
shtatiko 26-MAR-2003 Bug# 2782124, Removed logging of header in the log file as log format
is changed from tabular to form layout.
smadathi 06-Jan-2003 Bug 2684895. Removed the logging of person group id. Instead
used call to igs_fi_gen_005.finp_get_prsid_grp_code to
log person group code.
smadathi 31-dec-2002 Bug 2719776. Modified the cursor cur_person select to fetch
the records from view igf_ap_fa_base_rec and igs_pe_person_base_v
instead of igf_ap_fa_con_v. This fix is done to remove
Non-mergable view exists in the select and to reduce shared memory
within the acceptable limit
smadathi 31-DEC-2002 Bug 2719776. Logic has been modified to raise user defined exception
when invalid GL date is passed to the concurrent process. The similar
logic has been implemented for invalid values passed to the rest of
the concurrent parameters. Henceforth, the whenever invalid values for the concurrent
parameteres are provided, control will be transferred to the user defined exception part
and un handled exception will not appear in the log file
vvutukur 20-Nov-2002 Enh#2584986.Added p_d_gl_date parameter to transfer_disb_dtls_to_sf and validations
corresponding to this parameter.
vchappid 11-Feb-2002 Enh#2191470,When the Load Cal Parameter is passed, then check if there exists a
superior Fee Cal relation, if there is no relation set then log error
and abort the process
********************************************************************************************** */
CURSOR cur_person IS
SELECT pe.person_number person_number ,
fabase.person_id person_id
FROM igf_ap_fa_base_rec fabase,
igs_pe_person_base_v pe
WHERE fabase.person_id = pe.person_id
AND base_id= p_base_id;
SELECT fund_code
FROM igf_aw_fund_mast
WHERE fund_id = p_fund_id;
SELECT base_id
FROM igf_ap_fa_base_rec
WHERE person_id = cp_person_id
AND ci_cal_type = cp_ci_cal_type
AND ci_sequence_number = cp_ci_sequence_number;