[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM igs_fi_credits
WHERE credit_id = cp_credit_id
AND status = 'CLEARED'
AND unapplied_amount >0;
SELECT inv.*
FROM igs_fi_inv_int inv
WHERE inv.invoice_id = cp_invoice_id
AND inv.invoice_amount_due >0;
SELECT *
FROM igs_fi_credits
WHERE credit_id = cp_credit_id
AND status = 'CLEARED'
AND unapplied_amount >=0;
SELECT inv.*
FROM igs_fi_inv_int inv
WHERE inv.invoice_id = cp_invoice_id
AND inv.invoice_amount_due >=0;
CURSOR cur_sob IS SELECT set_of_books_id FROM igs_fi_control;
CURSOR cur_coa IS SELECT chart_of_accounts_id FROM gl_sets_of_books
WHERE set_of_books_id = get_sob_id;
CURSOR cur_sname IS SELECT short_name FROM gl_sets_of_books
WHERE set_of_books_id = get_sob_id;
CURSOR cur_vdesc IS SELECT description FROM fnd_flex_values_vl
WHERE flex_value_Set_id = p_value_set_id AND
flex_value=p_value;
SELECT 'X'
FROM igs_fi_a_hierarchies
WHERE appl_hierarchy_id = p_appl_hierarchy_id;
SELECT 'X'
FROM igs_fi_applications
WHERE application_id=p_application_id
AND credit_id=cp_credit_id
AND invoice_id=cp_invoice_id;
SELECT 'X'
FROM igs_fi_applications a1,
igs_fi_applications a2
WHERE a1.application_id=p_application_id
AND a1.application_id=a2.link_application_id
AND a1.amount_applied= - a2.amount_applied;
SELECT credit_id,invoice_id
FROM igs_fi_applications
WHERE application_id=p_application_id
AND application_type='APP';
SELECT credit_class
FROM igs_fi_cr_types crtyp
WHERE EXISTS (SELECT '1'
FROM igs_fi_credits crd
WHERE crd.credit_type_id = crtyp.credit_type_id
AND crd.credit_id = cp_n_credit_id
);
PROCEDURE call_update_charges(p_invoice_amount_due IN igs_fi_inv_int_all.invoice_amount_due%TYPE,
p_cur_invoice IN cur_invoice%ROWTYPE,
p_v_opt_fee_flag IN igs_fi_inv_int_all.optional_fee_flag%TYPE,
p_flag OUT NOCOPY BOOLEAN ) AS
/*|| Created By :Sarakshi
|| Created On :24-Jan-2002
|| Purpose : For updating charges record once a application/unapplication has happened.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
|| gurprsin 16-Aug-2005 Enh 3392095 - Tution Waiver build - Added new column waiver_name in the TBH call igs_fi_inv_int_pkg.update_row
|| pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
|| pathipat 07-Jun-2003 Enh 2831584 - SS Enhancements build
|| Added parameter p_v_opt_fee_flag
|| smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_inv_int_pkg.update_row
|| to add new parameter reversal GL Date
|| jbegum 20 feb 02 Enh bug # 2228910
|| Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
|| (reverse chronological order - newest change first) */
l_v_opt_fee_flag igs_fi_inv_int_all.optional_fee_flag%TYPE := NULL;
igs_fi_inv_int_pkg.update_row(x_rowid => p_cur_invoice.row_id,
x_invoice_id => p_cur_invoice.invoice_id,
x_person_id => p_cur_invoice.person_id,
x_fee_type => p_cur_invoice.fee_type,
x_fee_cat => p_cur_invoice.fee_cat,
x_fee_cal_type => p_cur_invoice.fee_cal_type,
x_fee_ci_sequence_number => p_cur_invoice.fee_ci_sequence_number,
x_course_cd => p_cur_invoice.course_cd,
x_attendance_mode => p_cur_invoice.attendance_mode,
x_attendance_type => p_cur_invoice.attendance_type,
x_invoice_amount_due => p_invoice_amount_due,
x_invoice_creation_date => p_cur_invoice.invoice_creation_date,
x_invoice_desc => p_cur_invoice.invoice_desc,
x_transaction_type => p_cur_invoice.transaction_type,
x_currency_cd => p_cur_invoice.currency_cd,
x_status => p_cur_invoice.status,
x_attribute_category => p_cur_invoice.attribute_category,
x_attribute1 => p_cur_invoice.attribute1,
x_attribute2 => p_cur_invoice.attribute2,
x_attribute3 => p_cur_invoice.attribute3,
x_attribute4 => p_cur_invoice.attribute4,
x_attribute5 => p_cur_invoice.attribute5,
x_attribute6 => p_cur_invoice.attribute6,
x_attribute7 => p_cur_invoice.attribute7,
x_attribute8 => p_cur_invoice.attribute8,
x_attribute9 => p_cur_invoice.attribute9,
x_attribute10 => p_cur_invoice.attribute10,
x_invoice_amount => p_cur_invoice.invoice_amount,
x_bill_id => p_cur_invoice.bill_id,
x_bill_number => p_cur_invoice.bill_number,
x_bill_date => p_cur_invoice.bill_date,
x_waiver_flag => p_cur_invoice.waiver_flag,
x_waiver_reason => p_cur_invoice.waiver_reason,
x_effective_date => p_cur_invoice.effective_date,
x_invoice_number => p_cur_invoice.invoice_number,
x_exchange_rate => p_cur_invoice.exchange_rate,
x_bill_payment_due_date => p_cur_invoice.bill_payment_due_date,
x_optional_fee_flag => l_v_opt_fee_flag,
x_mode => 'R',
x_reversal_gl_date => p_cur_invoice.reversal_gl_date,
x_tax_year_code => p_cur_invoice.tax_year_code,
x_waiver_name => p_cur_invoice.waiver_name
);
END call_update_charges;
PROCEDURE call_update_credits(p_unapplied_amount IN igs_fi_credits_all.unapplied_amount%TYPE,
p_cur_credit IN cur_credit%ROWTYPE,
p_flag OUT NOCOPY BOOLEAN)
AS
/*|| Created By :Sarakshi
|| Created On :24-Jan-2002
|| Purpose : For updating a credit record once a application/unappliaction has happened.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| gurprsin 16-Aug-2005 Enh 3392095 - Tution Waiver build - Added new column waiver_name in the TBH call igs_fi_credits_pkg.update_row
|| pathipat 21-Apr-2004 Enh 3558549 - Commercial Receivables Enhancements
|| Added parameter 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 update_row
|| call of credits table.
|| schodava 16-Jun-2003 Enh # 2831587 - Credit Card Fund Transfer Build - Modified call to update row.
|| pathipat 16-Dec-2002 Enh bug 2584741 - Deposits build - Modified call to update_row
|| Added columns check_number, source_transaction_type and source_transaction_ref
|| smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_credits_pkg.update_row
|| to add new parameter GL Date
|| sykrishn 14feb2002 SFCR020 build 2191470- Added 4 new params to update_row call for credits
|| (reverse chronological order - newest change first) */
BEGIN
p_flag:=TRUE;
igs_fi_credits_pkg.update_row( X_ROWID => p_cur_credit.row_id,
X_CREDIT_ID => p_cur_credit.credit_id,
X_CREDIT_NUMBER => p_cur_credit.credit_number,
X_STATUS => p_cur_credit.status,
X_CREDIT_SOURCE => p_cur_credit.credit_source,
X_PARTY_ID => p_cur_credit.party_id,
X_CREDIT_TYPE_ID => p_cur_credit.credit_type_id,
X_CREDIT_INSTRUMENT => p_cur_credit.credit_instrument,
X_DESCRIPTION => p_cur_credit.description,
X_AMOUNT => p_cur_credit.amount,
X_CURRENCY_CD => p_cur_credit.currency_cd,
X_EXCHANGE_RATE => p_cur_credit.exchange_rate,
X_TRANSACTION_DATE => p_cur_credit.transaction_date,
X_EFFECTIVE_DATE => p_cur_credit.effective_date,
X_REVERSAL_DATE => p_cur_credit.reversal_date,
X_REVERSAL_REASON_CODE => p_cur_credit.reversal_reason_code,
X_REVERSAL_COMMENTS => p_cur_credit.reversal_comments,
X_UNAPPLIED_AMOUNT => p_unapplied_amount,
X_SOURCE_TRANSACTION_ID => p_cur_credit.source_transaction_id,
X_RECEIPT_LOCKBOX_NUMBER => p_cur_credit.receipt_lockbox_number,
X_MERCHANT_ID => p_cur_credit.merchant_id,
X_CREDIT_CARD_CODE => p_cur_credit.credit_card_code,
X_CREDIT_CARD_HOLDER_NAME => p_cur_credit.credit_card_holder_name,
X_CREDIT_CARD_NUMBER => p_cur_credit.credit_card_number,
X_CREDIT_CARD_EXPIRATION_DATE => p_cur_credit.credit_card_expiration_date,
X_CREDIT_CARD_APPROVAL_CODE => p_cur_credit.credit_card_approval_code,
X_AWD_YR_CAL_TYPE => p_cur_credit.awd_yr_cal_type,
X_AWD_YR_CI_SEQUENCE_NUMBER => p_cur_credit.awd_yr_ci_sequence_number,
X_FEE_CAL_TYPE => p_cur_credit.fee_cal_type ,
X_FEE_CI_SEQUENCE_NUMBER => p_cur_credit.fee_ci_sequence_number,
X_ATTRIBUTE_CATEGORY => p_cur_credit.attribute_category,
X_ATTRIBUTE1 => p_cur_credit.attribute1,
X_ATTRIBUTE2 => p_cur_credit.attribute2,
X_ATTRIBUTE3 => p_cur_credit.attribute3,
X_ATTRIBUTE4 => p_cur_credit.attribute4,
X_ATTRIBUTE5 => p_cur_credit.attribute5,
X_ATTRIBUTE6 => p_cur_credit.attribute6,
X_ATTRIBUTE7 => p_cur_credit.attribute7,
X_ATTRIBUTE8 => p_cur_credit.attribute8,
X_ATTRIBUTE9 => p_cur_credit.attribute9,
X_ATTRIBUTE10 => p_cur_credit.attribute10,
X_ATTRIBUTE11 => p_cur_credit.attribute11,
X_ATTRIBUTE12 => p_cur_credit.attribute12,
X_ATTRIBUTE13 => p_cur_credit.attribute13,
X_ATTRIBUTE14 => p_cur_credit.attribute14,
X_ATTRIBUTE15 => p_cur_credit.attribute15,
X_ATTRIBUTE16 => p_cur_credit.attribute16,
X_ATTRIBUTE17 => p_cur_credit.attribute17,
X_ATTRIBUTE18 => p_cur_credit.attribute18,
X_ATTRIBUTE19 => p_cur_credit.attribute19,
X_ATTRIBUTE20 => p_cur_credit.attribute20,
X_MODE => 'R',
X_GL_DATE => p_cur_credit.gl_date,
X_CHECK_NUMBER => p_cur_credit.check_number,
X_SOURCE_TRANSACTION_TYPE => p_cur_credit.source_transaction_type,
X_SOURCE_TRANSACTION_REF => p_cur_credit.source_transaction_ref,
x_credit_card_status_code => p_cur_credit.credit_card_status_code,
x_credit_card_payee_cd => p_cur_credit.credit_card_payee_cd,
x_credit_card_tangible_cd => p_cur_credit.credit_card_tangible_cd,
x_lockbox_interface_id => p_cur_credit.lockbox_interface_id,
x_batch_name => p_cur_credit.batch_name,
x_deposit_date => p_cur_credit.deposit_date,
x_source_invoice_id => p_cur_credit.source_invoice_id,
x_tax_year_code => p_cur_credit.tax_year_code,
x_waiver_name => p_cur_credit.waiver_name
);
END call_update_credits;
|| Modified call to call_update_charges() - Added check for optional_payment_ind
|| and waiver flag
|| smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_applications_pkg.insert_row
|| to add new parameters GL Date , GL_POSTED_DATE, POSTING_CONTROL_ID
|| (reverse chronological order - newest change first) */
CURSOR cur_cr_act IS
SELECT credit_activity_id
FROM igs_fi_cr_activities
WHERE credit_id = p_credit_id;
SELECT invoice_lines_id
FROM igs_fi_invln_int
WHERE invoice_id = p_invoice_id;
igs_fi_applications_pkg.insert_row( X_ROWID => l_rowid,
X_APPLICATION_ID => p_application_id,
X_APPLICATION_TYPE => g_app,
X_INVOICE_ID => p_invoice_id,
X_CREDIT_ID => p_credit_id,
X_CREDIT_ACTIVITY_ID => l_cur_cr_act.credit_activity_id,
X_AMOUNT_APPLIED => l_amount,
X_APPLY_DATE => TRUNC(SYSDATE),
X_LINK_APPLICATION_ID => NULL,
X_DR_ACCOUNT_CD => p_dr_account_cd,
X_CR_ACCOUNT_CD => p_cr_account_cd,
X_DR_GL_CODE_CCID => p_dr_gl_ccid,
X_CR_GL_CODE_CCID => p_cr_gl_ccid,
X_APPLIED_INVOICE_LINES_ID => l_cur_invln.invoice_lines_id,
X_APPL_HIERARCHY_ID => p_appl_hierarchy_id,
X_POSTING_ID => NULL,
X_MODE => 'R' ,
X_GL_DATE => TRUNC(p_d_gl_date),
X_GL_POSTED_DATE => NULL,
X_POSTING_CONTROL_ID => NULL
);
call_update_credits(p_unapp_amount,p_cur_credit,l_crd_upd_success);
call_update_charges( p_invoice_amount_due => p_inv_amt_due,
p_cur_invoice => p_cur_invoice,
p_v_opt_fee_flag => l_v_optional_fee_flag,
p_flag => l_chg_upd_success);
|| Modified call to call_update_charges()
|| smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_applications_pkg.insert_row
|| to add new parameters GL Date , GL_POSTED_DATE, POSTING_CONTROL_ID
|| (reverse chronological order - newest change first) */
CURSOR cur_unapp IS
SELECT *
FROM igs_fi_applications
WHERE application_id = p_application_id;
igs_fi_applications_pkg.insert_row( X_ROWID => l_rowid,
X_APPLICATION_ID => l_application_id,
X_APPLICATION_TYPE => g_unapp,
X_INVOICE_ID => p_invoice_id,
X_CREDIT_ID => p_credit_id,
X_CREDIT_ACTIVITY_ID => l_cur_unapp.credit_activity_id,
X_AMOUNT_APPLIED => l_amount,
X_APPLY_DATE => TRUNC(SYSDATE),
X_LINK_APPLICATION_ID => l_cur_unapp.application_id,
X_DR_ACCOUNT_CD => l_cur_unapp.dr_account_cd,
X_CR_ACCOUNT_CD => l_cur_unapp.cr_account_cd,
X_DR_GL_CODE_CCID => l_cur_unapp.dr_gl_code_ccid,
X_CR_GL_CODE_CCID => l_cur_unapp.cr_gl_code_ccid,
X_APPLIED_INVOICE_LINES_ID => l_cur_unapp.applied_invoice_lines_id,
X_APPL_HIERARCHY_ID => l_cur_unapp.appl_hierarchy_id,
X_POSTING_ID => NULL,
X_MODE => 'R' ,
X_GL_DATE => TRUNC(p_d_gl_date),
X_GL_POSTED_DATE => NULL,
X_POSTING_CONTROL_ID => NULL
);
call_update_credits(p_unapp_amount,p_cur_credit,l_crd_upd_success);
call_update_charges( p_invoice_amount_due => p_inv_amt_due,
p_cur_invoice => p_cur_invoice,
p_v_opt_fee_flag => NULL,
p_flag => l_chg_upd_success);
SELECT dr_gl_ccid,cr_gl_ccid,dr_account_cd,cr_account_cd
FROM igs_fi_cr_activities
WHERE credit_activity_id = p_cr_activity_id;
SELECT rec_gl_ccid,rev_gl_ccid,rec_account_cd,rev_account_cd
FROM igs_fi_invln_int
WHERE invoice_lines_id = p_invoice_lines_id
AND NVL(error_account,'N') <> 'Y';
SELECT ct.credit_class
FROM igs_fi_cr_types ct,
igs_fi_credits c,
igs_fi_cr_activities ca
WHERE ct.credit_type_id=c.credit_type_id
AND c.credit_id=ca.credit_id
AND ca.credit_activity_id=p_cr_activity_id;
SELECT amount_applied FROM igs_fi_applications
WHERE (application_id = p_application_id AND application_type='APP')
OR link_application_id=p_application_id;
SELECT credit_id,invoice_id
FROM igs_fi_applications
WHERE application_id=p_application_id;
SELECT 'X'
FROM hz_parties
WHERE party_id=p_person_id;
SELECT concatenated_segments
FROM gl_code_combinations_kfv
WHERE code_combination_id = cp_ccid;
|| smadathi 01-jan-2003 Bug 2713272. Modified the cursor cur_pref_person to select
|| from igs_pe_person_id_typ instead of the igs_pe_person_id_typ_v.
|| This is done due to Non-Meargabile view and higher value of shared memory
|| beyong the acceptable limit
|| (reverse chronological order - newest change first) */
CURSOR cur_pref_person IS
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE preferred_ind='Y';
SELECT conv_process_run_ind
FROM igs_fi_control_all ;
SELECT balance_rule_id,
version_number,
last_conversion_date
FROM IGS_FI_BALANCE_RULES
WHERE balance_name = cp_balance_type
AND last_conversion_date IS NOT NULL
ORDER BY version_number DESC;
SELECT balance_rule_id,
version_number,
last_conversion_date
FROM IGS_FI_BALANCE_RULES
WHERE balance_name = cp_balance_type
ORDER BY version_number DESC;
SELECT *
FROM igs_lookup_values
WHERE lookup_type = 'IGS_FI_BALANCE_TYPE'
AND lookup_code = cp_balance_type
AND lookup_code IN ('HOLDS','FEE')
AND enabled_flag = 'Y'
AND ( (start_date_active < TRUNC(SYSDATE))
AND
(end_date_active IS NULL OR end_date_active > TRUNC(SYSDATE))
);