[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select list to fetch meaning from igs_lookup_values
******************************************************************/
CURSOR cur_desc( x_type igs_lookups_view.lookup_type%TYPE, x_code igs_lookups_view.lookup_code%TYPE ) IS
SELECT meaning
FROM igs_lookup_values
WHERE lookup_type = x_type
AND lookup_code = x_code
AND (SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
AND NVL(end_date_active,SYSDATE))
AND enabled_flag = 'Y';
smadathi 24-Jun-2002 Bug 2404720. The cursor c_credittype_id select is modified to fetch description column along
with credit_type_id column. The call to IGS_FI_CREDITS_API_PUB.Create_Credit has been modified
to pass this description value to the formal parameter p_description.
brajendr 22-FEB-2002 modifed the cursor (c_cc_number)which is used for creating the credit number as per the DLD
jbegum 20-FEB-2002 Enh bug # 2228910
Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
sykrishn 04-FEB-2002 Modifications due to SFCR020- Changes to call to credits API and changes in igs_fi_credits_pkg.update_row
to add the new columns
Code related to derivation of credit_source removed since it is going to be passed as null to credits API. - SFCR020 - 2191470
sarakshi 01-Feb-2002 In the call to the credit's API adding a new parameter p_invoice_id as a part of
SFCR003 , bug:2195715.
masehgal 17-Jan-2002 ENH # 2170429
Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
***************************************************************/
CURSOR c_credittype_id(cp_v_credit_class igs_fi_cr_types.credit_class%TYPE) IS
SELECT credit_type_id , description
FROM igs_fi_cr_types
WHERE SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date,SYSDATE)
AND credit_class = cp_v_credit_class;
SELECT lookup_code
FROM igs_lookup_values
WHERE lookup_type = 'IGS_CREDIT_CARDS'
AND meaning = ''||cp_meaning||'';
SELECT LAST_DAY(TO_DATE('01/' || cp_month_year,'DD/MM/YYYY')) expiry_date
FROM dual;
PROCEDURE update_cc_credit(
p_credit_card_approval_code IN NUMBER,
p_credit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) AS
/*************************************************************
Created By :samaresh
Date Created By : 29-SEP-2001
Purpose : This Procedure calls the Create Credits API
Know limitations, enhancements or remarks
Change History
Who When What
pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
Added param x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new parameters(lockbox_interface_id,batch_name,deposit_date) to the TBH
update_row call of credits table.
schodava 16-Jun-2003 Enh 2381587 - Credit Card Fund Transfer Build.
Modified the Credits TBH update row
vvutukur 16-Dec-2002 Enh#2584741.Modified the tbh call to igs_fi_credits_pkg.update_row to add 3 new
parameters check_number,source_transaction_type,source_transaction_ref.
vchappid 02-Dec-2002 Enh#2584986, NOCOPY is manually added for x_return_status procedure parameter
vvutukur 25-Nov-2002 Enh#2584986.Modified the call to igs_fi_credits_pkg.update_row to pass gl_date.
vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,from the call to
IGS_FI_CREDITS_pkg.Update_Row,
***************************************************************/
CURSOR c_credit_dtls(cp_credit_id NUMBER) IS
SELECT *
FROM igs_fi_credits
WHERE credit_id = cp_credit_id;
igs_fi_credits_pkg.update_row(x_rowid => l_credit_dtls_rec.row_id,
x_credit_id => l_credit_dtls_rec.credit_id,
x_credit_number => l_credit_dtls_rec.credit_number,
x_status => l_credit_dtls_rec.status,
x_credit_source => l_credit_dtls_rec.credit_source,
x_party_id => l_credit_dtls_rec.party_id,
x_credit_type_id => l_credit_dtls_rec.credit_type_id,
x_credit_instrument => l_credit_dtls_rec.credit_instrument,
x_description => l_credit_dtls_rec.description,
x_amount => l_credit_dtls_rec.amount,
x_currency_cd => l_credit_dtls_rec.currency_cd,
x_exchange_rate => l_credit_dtls_rec.exchange_rate,
x_transaction_date => l_credit_dtls_rec.transaction_date,
x_effective_date => l_credit_dtls_rec.effective_date,
x_reversal_date => l_credit_dtls_rec.reversal_date,
x_reversal_reason_code => l_credit_dtls_rec.reversal_reason_code,
x_reversal_comments => l_credit_dtls_rec.reversal_comments,
x_unapplied_amount => l_credit_dtls_rec.unapplied_amount,
x_source_transaction_id => l_credit_dtls_rec.source_transaction_id,
x_receipt_lockbox_number => l_credit_dtls_rec.receipt_lockbox_number,
x_merchant_id => l_credit_dtls_rec.merchant_id,
x_credit_card_code => l_credit_dtls_rec.credit_card_code,
x_credit_card_holder_name => l_credit_dtls_rec.credit_card_holder_name,
x_credit_card_number => l_credit_dtls_rec.credit_card_number,
x_credit_card_expiration_date => l_credit_dtls_rec.credit_card_expiration_date,
x_credit_card_approval_code => TO_CHAR(p_credit_card_approval_code),
x_awd_yr_cal_type => l_credit_dtls_rec.awd_yr_cal_type,
x_awd_yr_ci_sequence_number => l_credit_dtls_rec.awd_yr_ci_sequence_number,
x_fee_cal_type => l_credit_dtls_rec.fee_cal_type,
x_fee_ci_sequence_number => l_credit_dtls_rec.fee_ci_sequence_number,
x_attribute_category => l_credit_dtls_rec.attribute_category,
x_attribute1 => l_credit_dtls_rec.attribute1,
x_attribute2 => l_credit_dtls_rec.attribute2,
x_attribute3 => l_credit_dtls_rec.attribute3,
x_attribute4 => l_credit_dtls_rec.attribute4,
x_attribute5 => l_credit_dtls_rec.attribute5,
x_attribute6 => l_credit_dtls_rec.attribute6,
x_attribute7 => l_credit_dtls_rec.attribute7,
x_attribute8 => l_credit_dtls_rec.attribute8,
x_attribute9 => l_credit_dtls_rec.attribute9,
x_attribute10 => l_credit_dtls_rec.attribute10,
x_attribute11 => l_credit_dtls_rec.attribute11,
x_attribute12 => l_credit_dtls_rec.attribute12,
x_attribute13 => l_credit_dtls_rec.attribute13,
x_attribute14 => l_credit_dtls_rec.attribute14,
x_attribute15 => l_credit_dtls_rec.attribute15,
x_attribute16 => l_credit_dtls_rec.attribute16,
x_attribute17 => l_credit_dtls_rec.attribute17,
x_attribute18 => l_credit_dtls_rec.attribute18,
x_attribute19 => l_credit_dtls_rec.attribute19,
x_attribute20 => l_credit_dtls_rec.attribute20,
x_gl_date => l_credit_dtls_rec.gl_date,
x_check_number => l_credit_dtls_rec.check_number,
x_source_transaction_type => l_credit_dtls_rec.source_transaction_type,
x_source_transaction_ref => l_credit_dtls_rec.source_transaction_ref,
x_credit_card_status_code => l_credit_dtls_rec.credit_card_status_code,
x_credit_card_payee_cd => l_credit_dtls_rec.credit_card_payee_cd,
x_credit_card_tangible_cd => l_credit_dtls_rec.credit_card_tangible_cd,
x_lockbox_interface_id => l_credit_dtls_rec.lockbox_interface_id,
x_batch_name => l_credit_dtls_rec.batch_name,
x_deposit_date => l_credit_dtls_rec.deposit_date,
x_source_invoice_id => l_credit_dtls_rec.source_invoice_id,
x_tax_year_code => l_credit_dtls_rec.tax_year_code,
x_waiver_name => l_credit_dtls_rec.waiver_name
);
END update_cc_credit;
And Update Invoice record with optional_fee_flag=>'D'(Declined)
Know limitations, enhancements or remarks
Change History
Who When What
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
pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
smadathi 03-jun-2002 Enh. Bug 2831584. Modified opening of cursor cur_invheader to pass TO_NUMBER(p_invoice_id))
instead of passing l_inv_id. This was changed because the amount due and amount made same
by the update row call even after the declining of fees by charges API.
vvutukur 15-Nov-2002 Enh#2584986.Passed SYSDATE to the call to charges API for the parameter
l_chg_line_tbl(1).p_gl_date.
vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,the assignment of
L_CHG_REC.P_SUBACCOUNT_ID to L_CUR_INVHEADER.SUBACCOUNT_ID,and from
calls to igs_fi_inv_int_pkg.update_row.
masehgal 17-Jan-2002 ENH # 2170429
Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
***************************************************************/
-- Cursor for fetching Invoice Header details
CURSOR cur_invheader(cp_invoice_id IGS_FI_INV_INT.Invoice_Id%TYPE) IS
SELECT *
FROM igs_fi_inv_int
WHERE invoice_id = cp_invoice_id;
SELECT *
FROM IGS_FI_INVLN_INT
WHERE invoice_id = p_invoice_id;
igs_fi_inv_int_pkg.update_row( x_rowid => l_cur_invheader.row_id ,
x_invoice_id => l_cur_invheader.invoice_id ,
x_person_id => l_cur_invheader.person_id ,
x_fee_type => l_cur_invheader.fee_type ,
x_fee_cat => l_cur_invheader.fee_cat ,
x_fee_cal_type => l_cur_invheader.fee_cal_type ,
x_fee_ci_sequence_number => l_cur_invheader.fee_ci_sequence_number ,
x_course_cd => l_cur_invheader.course_cd ,
x_attendance_mode => l_cur_invheader.attendance_mode ,
x_attendance_type => l_cur_invheader.attendance_type ,
x_invoice_amount_due => l_cur_invheader.invoice_amount_due ,
x_invoice_creation_date => l_cur_invheader.invoice_creation_date ,
x_invoice_desc => l_cur_invheader.invoice_desc ,
x_transaction_type => l_cur_invheader.transaction_type ,
x_currency_cd => l_cur_invheader.currency_cd ,
x_status => l_cur_invheader.status ,
x_attribute_category => l_cur_invheader.attribute_category ,
x_attribute1 => l_cur_invheader.attribute1 ,
x_attribute2 => l_cur_invheader.attribute2 ,
x_attribute3 => l_cur_invheader.attribute3 ,
x_attribute4 => l_cur_invheader.attribute4 ,
x_attribute5 => l_cur_invheader.attribute5 ,
x_attribute6 => l_cur_invheader.attribute6 ,
x_attribute7 => l_cur_invheader.attribute7 ,
x_attribute8 => l_cur_invheader.attribute8 ,
x_attribute9 => l_cur_invheader.attribute9 ,
x_attribute10 => l_cur_invheader.attribute10 ,
x_invoice_amount => l_cur_invheader.invoice_amount ,
x_bill_id => l_cur_invheader.bill_id ,
x_bill_number => l_cur_invheader.bill_number ,
x_bill_date => l_cur_invheader.bill_date ,
x_waiver_flag => l_cur_invheader.waiver_flag ,
x_waiver_reason => l_cur_invheader.waiver_reason ,
x_effective_date => l_cur_invheader.effective_date ,
x_invoice_number => l_cur_invheader.invoice_number ,
x_exchange_rate => l_cur_invheader.exchange_rate ,
x_bill_payment_due_date => l_cur_invheader.bill_payment_due_date ,
x_optional_fee_flag => 'D' ,
x_mode => 'R',
x_reversal_gl_date => TRUNC(SYSDATE),
x_tax_year_code => l_cur_invheader.tax_year_code,
x_waiver_name => l_cur_invheader.waiver_name
);
Purpose : This Procedure Updates All Invoice records which are not Declined
for the given Student and SubAccountID with optional_fee_flag => 'A'(Accepted)
Know limitations, enhancements or remarks
Change History
Who When What
pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
vvutukur 04-Dec-2003 Bug#3249288.Modified cursor cur_opt_fees to select invoice records having
optional fee flag as 'O' without need to look at the value of optional_payment_ind
at fee type set up level.
pathipat 04-Jun-2003 Enh 2831584 - SS Enhancements Build
Modified cursor cur_opt_fees - included join with igs_fi_invln_int_all
vvutukur 25-Nov-2002 Enh#2584986.Passed NULL to the newly added parameter x_reversal_gl_date in the call to
igs_fi_inv_int_pkg.update_row.
vvutukur 16-Sep-2002 Enh#2564643.Removed the references to subaccount_id.ie., from
parameters list p_subaccount_id,from cursor cur_opt_fees and from the
call to igs_fi_inv_int_pkg.update_row.9/16/02
removal build.
masehgal 17-Jan-2002 ENH # 2170429
Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
***************************************************************/
-- Cursor to fetch all the Optional fees which are not Declined
CURSOR cur_opt_fees IS
SELECT inv.invoice_id
FROM igs_fi_inv_int_all inv ,
igs_fi_invln_int_all invln
WHERE invln.invoice_id = inv.invoice_id
AND NVL(invln.error_account,'N') = 'N'
AND inv.person_id = p_person_id
AND inv.optional_fee_flag ='O';
SELECT *
FROM igs_fi_inv_int
WHERE invoice_id = p_invoice_id;
Igs_Fi_Inv_Int_Pkg.update_row( x_rowid => l_cur_inv_int.row_id ,
x_invoice_id => l_cur_inv_int.invoice_id ,
x_person_id => l_cur_inv_int.person_id ,
x_fee_type => l_cur_inv_int.fee_type ,
x_fee_cat => l_cur_inv_int.fee_cat ,
x_fee_cal_type => l_cur_inv_int.fee_cal_type ,
x_fee_ci_sequence_number => l_cur_inv_int.fee_ci_sequence_number ,
x_course_cd => l_cur_inv_int.course_cd ,
x_attendance_mode => l_cur_inv_int.attendance_mode ,
x_attendance_type => l_cur_inv_int.attendance_type ,
x_invoice_amount_due => l_cur_inv_int.invoice_amount_due ,
x_invoice_creation_date => l_cur_inv_int.invoice_creation_date ,
x_invoice_desc => l_cur_inv_int.invoice_desc ,
x_transaction_type => l_cur_inv_int.transaction_type ,
x_currency_cd => l_cur_inv_int.currency_cd ,
x_status => l_cur_inv_int.status ,
x_attribute_category => l_cur_inv_int.attribute_category ,
x_attribute1 => l_cur_inv_int.attribute1 ,
x_attribute2 => l_cur_inv_int.attribute2 ,
x_attribute3 => l_cur_inv_int.attribute3 ,
x_attribute4 => l_cur_inv_int.attribute4 ,
x_attribute5 => l_cur_inv_int.attribute5 ,
x_attribute6 => l_cur_inv_int.attribute6 ,
x_attribute7 => l_cur_inv_int.attribute7 ,
x_attribute8 => l_cur_inv_int.attribute8 ,
x_attribute9 => l_cur_inv_int.attribute9 ,
x_attribute10 => l_cur_inv_int.attribute10 ,
x_invoice_amount => l_cur_inv_int.invoice_amount ,
x_bill_id => l_cur_inv_int.bill_id ,
x_bill_number => l_cur_inv_int.bill_number ,
x_bill_date => l_cur_inv_int.bill_date ,
x_waiver_flag => l_cur_inv_int.waiver_flag ,
x_waiver_reason => l_cur_inv_int.waiver_reason ,
x_effective_date => l_cur_inv_int.effective_date ,
x_invoice_number => l_cur_inv_int.invoice_number ,
x_exchange_rate => l_cur_inv_int.exchange_rate ,
x_bill_payment_due_date => l_cur_inv_int.bill_payment_due_date ,
x_optional_fee_flag => 'A' ,
x_mode => 'R',
x_reversal_gl_date => l_cur_inv_int.reversal_gl_date,
x_tax_year_code => l_cur_inv_int.tax_year_code,
x_waiver_name => l_cur_inv_int.waiver_name
);
SELECT person_id , s_student_todo_type , sequence_number ,
reference_number , cal_type , ci_sequence_number ,
course_cd , unit_cd , other_reference ,
logical_delete_dt , Created_by , creation_date ,
last_updated_by , last_update_date , last_update_login ,
request_id , program_application_id , program_id ,
program_update_date , rowid,uoo_id
FROM igs_pe_std_todo_ref
WHERE person_id = cp_n_person_id
AND s_student_todo_type = cst_fee_recalc
AND logical_delete_dt IS NULL
ORDER BY cal_type , ci_sequence_number;
igs_pe_std_todo_ref_pkg.update_row ( x_rowid => rec_c_igs_pe_std_todo_ref.rowid,
x_person_id => rec_c_igs_pe_std_todo_ref.person_id,
x_s_student_todo_type => rec_c_igs_pe_std_todo_ref.s_student_todo_type,
x_sequence_number => rec_c_igs_pe_std_todo_ref.sequence_number,
x_reference_number => rec_c_igs_pe_std_todo_ref.reference_number,
x_cal_type => rec_c_igs_pe_std_todo_ref.cal_type,
x_ci_sequence_number => rec_c_igs_pe_std_todo_ref.ci_sequence_number,
x_course_cd => rec_c_igs_pe_std_todo_ref.course_cd,
x_unit_cd => rec_c_igs_pe_std_todo_ref.unit_cd,
x_other_reference => rec_c_igs_pe_std_todo_ref.other_reference,
x_logical_delete_dt => SYSDATE,
x_mode => 'R',
x_uoo_id => rec_c_igs_pe_std_todo_ref.uoo_id);
SELECT message_text
FROM fnd_new_messages
WHERE message_name = cp_v_message_name
AND application_id = 8405
AND language_code = USERENV('LANG');