[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
in the igs_fi_lb_ovfl_errs_pkg.insert_row and igs_fi_lb_rect_errs_pkg.insert_row calls
pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number.
changed logic for credit_number in invoke_credits_api_pvt().
shtatiko 27-AUG-2003 Enh# 3045007, Modified valtype2_and_import_rects and initialize.
Added two new parameters p_n_credit_type_id, p_n_receipt_amt to invoke_credits_api_pvt
And added g_v_inst_payment, g_v_label_bal_amnt and g_v_label_dflt_cr_type.
pathipat 21-Aug-2003 Enh 3076768 - Auto Release of Holds
Modified invoke_credits_api_pvt() and
valtype2_and_import_rects()
agairola 07-Jul-03 Bug: 3032415 Modified validate_type1 procedure
***************************************************************** */
g_v_ind_no CONSTANT VARCHAR2(5) := 'N';
SELECT gl_date_source_code
FROM igs_fi_lockboxes
WHERE lockbox_name = cp_lockbox_name
AND closed_flag = cp_ind_yn;
SELECT credit_type_name
FROM igs_fi_cr_types
WHERE credit_type_id = cp_credit_type_id;
SELECT description
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_cal_seq;
SELECT record_type_code
FROM igs_fi_lb_rec_types
WHERE lockbox_name = cp_lockbox_name
AND record_identifier_cd = cp_rec_identifier_cd;
SELECT rowid row_id, lb.*
FROM igs_fi_lockbox_ints lb
WHERE lockbox_name = cp_lockbox_name
AND record_status = cp_status
FOR UPDATE NOWAIT
ORDER BY lockbox_interface_id;
SELECT rowid row_id,
lb.*
FROM igs_fi_lockbox_ints lb
WHERE batch_name = cp_batch_name
AND record_status = cp_status
AND lockbox_name IS NULL
FOR UPDATE NOWAIT
ORDER BY lockbox_interface_id;
l_t_th_tab.DELETE;
l_t_bh_tab.DELETE;
l_t_lh_tab.DELETE;
l_t_rc_tab.DELETE;
l_t_ro_tab.DELETE;
l_t_distinct_batch.DELETE;
l_t_distinct_batch.DELETE;
PROCEDURE update_lbint_status(p_v_status igs_fi_lockbox_ints.record_status%TYPE) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 12-Jun-2003
Purpose : Procedure for updating the interface table record status.
Known limitations,enhancements,remarks:
Change History
Who When What
***************************************************************** */
l_n_rec_cntr NUMBER(38);
igs_fi_lockbox_ints_pkg.update_row(x_rowid => g_lb_int_tab(l_n_rec_cntr).row_id,
x_lockbox_interface_id => g_lb_int_tab(l_n_rec_cntr).lockbox_interface_id,
x_record_identifier_cd => g_lb_int_tab(l_n_rec_cntr).record_identifier_cd,
x_record_status => p_v_status,
x_deposit_date => g_lb_int_tab(l_n_rec_cntr).deposit_date,
x_transmission_record_count => g_lb_int_tab(l_n_rec_cntr).transmission_record_count,
x_transmission_amt => g_lb_int_tab(l_n_rec_cntr).transmission_amt,
x_lockbox_name => g_lb_int_tab(l_n_rec_cntr).lockbox_name,
x_lockbox_batch_count => g_lb_int_tab(l_n_rec_cntr).lockbox_batch_count,
x_lockbox_record_count => g_lb_int_tab(l_n_rec_cntr).lockbox_record_count,
x_lockbox_amt => g_lb_int_tab(l_n_rec_cntr).lockbox_amt,
x_batch_name => g_lb_int_tab(l_n_rec_cntr).batch_name,
x_batch_amt => g_lb_int_tab(l_n_rec_cntr).batch_amt,
x_batch_record_count => g_lb_int_tab(l_n_rec_cntr).batch_record_count,
x_item_number => g_lb_int_tab(l_n_rec_cntr).item_number,
x_receipt_amt => g_lb_int_tab(l_n_rec_cntr).receipt_amt,
x_check_cd => g_lb_int_tab(l_n_rec_cntr).check_cd,
x_party_number => g_lb_int_tab(l_n_rec_cntr).party_number,
x_payer_name => g_lb_int_tab(l_n_rec_cntr).payer_name,
x_charge_cd1 => g_lb_int_tab(l_n_rec_cntr).charge_cd1,
x_charge_cd2 => g_lb_int_tab(l_n_rec_cntr).charge_cd2,
x_charge_cd3 => g_lb_int_tab(l_n_rec_cntr).charge_cd3,
x_charge_cd4 => g_lb_int_tab(l_n_rec_cntr).charge_cd4,
x_charge_cd5 => g_lb_int_tab(l_n_rec_cntr).charge_cd5,
x_charge_cd6 => g_lb_int_tab(l_n_rec_cntr).charge_cd6,
x_charge_cd7 => g_lb_int_tab(l_n_rec_cntr).charge_cd7,
x_charge_cd8 => g_lb_int_tab(l_n_rec_cntr).charge_cd8,
x_applied_amt1 => g_lb_int_tab(l_n_rec_cntr).applied_amt1,
x_applied_amt2 => g_lb_int_tab(l_n_rec_cntr).applied_amt2,
x_applied_amt3 => g_lb_int_tab(l_n_rec_cntr).applied_amt3,
x_applied_amt4 => g_lb_int_tab(l_n_rec_cntr).applied_amt4,
x_applied_amt5 => g_lb_int_tab(l_n_rec_cntr).applied_amt5,
x_applied_amt6 => g_lb_int_tab(l_n_rec_cntr).applied_amt6,
x_applied_amt7 => g_lb_int_tab(l_n_rec_cntr).applied_amt7,
x_applied_amt8 => g_lb_int_tab(l_n_rec_cntr).applied_amt8,
x_credit_type_cd => g_lb_int_tab(l_n_rec_cntr).credit_type_cd,
x_fee_cal_instance_cd => g_lb_int_tab(l_n_rec_cntr).fee_cal_instance_cd,
x_adm_application_id => g_lb_int_tab(l_n_rec_cntr).adm_application_id,
x_attribute_category => g_lb_int_tab(l_n_rec_cntr).attribute_category,
x_attribute1 => g_lb_int_tab(l_n_rec_cntr).attribute1,
x_attribute2 => g_lb_int_tab(l_n_rec_cntr).attribute2,
x_attribute3 => g_lb_int_tab(l_n_rec_cntr).attribute3,
x_attribute4 => g_lb_int_tab(l_n_rec_cntr).attribute4,
x_attribute5 => g_lb_int_tab(l_n_rec_cntr).attribute5,
x_attribute6 => g_lb_int_tab(l_n_rec_cntr).attribute6,
x_attribute7 => g_lb_int_tab(l_n_rec_cntr).attribute7,
x_attribute8 => g_lb_int_tab(l_n_rec_cntr).attribute8,
x_attribute9 => g_lb_int_tab(l_n_rec_cntr).attribute9,
x_attribute10 => g_lb_int_tab(l_n_rec_cntr).attribute10,
x_attribute11 => g_lb_int_tab(l_n_rec_cntr).attribute11,
x_attribute12 => g_lb_int_tab(l_n_rec_cntr).attribute12,
x_attribute13 => g_lb_int_tab(l_n_rec_cntr).attribute13,
x_attribute14 => g_lb_int_tab(l_n_rec_cntr).attribute14,
x_attribute15 => g_lb_int_tab(l_n_rec_cntr).attribute15,
x_attribute16 => g_lb_int_tab(l_n_rec_cntr).attribute16,
x_attribute17 => g_lb_int_tab(l_n_rec_cntr).attribute17,
x_attribute18 => g_lb_int_tab(l_n_rec_cntr).attribute18,
x_attribute19 => g_lb_int_tab(l_n_rec_cntr).attribute19,
x_attribute20 => g_lb_int_tab(l_n_rec_cntr).attribute20);
END update_lbint_status;
SELECT invoice_id,
fee_type
FROM igs_fi_inv_int
WHERE person_id = cp_party_id
AND invoice_number = cp_invoice_number;
SELECT designated_payment_flag
FROM igs_fi_fee_type
WHERE fee_type = cp_fee_type;
SELECT invoice_amount_due
FROM igs_fi_inv_int
WHERE invoice_id = cp_invoice_id;
PROCEDURE insert_lb_errors(p_r_receipt_rec lb_receipt_rec,
p_n_receipt_error_id OUT NOCOPY PLS_INTEGER) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 12-Jun-2003
Purpose : Procedure for inserting data in igs_fi_lb_rect_errs
table
Known limitations,enhancements,remarks:
Change History
Who When What
svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
in the igs_fi_lb_rect_errs_pkg.insert_row call
***************************************************************** */
l_v_rowid VARCHAR2(25);
igs_fi_lb_rect_errs_pkg.insert_row(x_rowid => l_v_rowid,
x_lockbox_receipt_error_id => l_n_lockbox_receipt_error_id,
x_lockbox_interface_id => p_r_receipt_rec.lockbox_interface_id,
x_item_number => p_r_receipt_rec.item_number,
x_lockbox_name => p_r_receipt_rec.lockbox_name,
x_receipt_amt => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.receipt_amt),
x_batch_name => p_r_receipt_rec.batch_name,
x_party_number => p_r_receipt_rec.party_number,
x_payer_name => p_r_receipt_rec.payer_name,
x_check_cd => p_r_receipt_rec.check_cd,
x_deposit_date => p_r_receipt_rec.deposit_date,
x_credit_type_cd => p_r_receipt_rec.credit_type_cd,
x_fee_cal_instance_cd => p_r_receipt_rec.fee_cal_instance_cd,
x_charge_cd1 => p_r_receipt_rec.charge_cd1,
x_charge_cd2 => p_r_receipt_rec.charge_cd2,
x_charge_cd3 => p_r_receipt_rec.charge_cd3,
x_charge_cd4 => p_r_receipt_rec.charge_cd4,
x_charge_cd5 => p_r_receipt_rec.charge_cd5,
x_charge_cd6 => p_r_receipt_rec.charge_cd6,
x_charge_cd7 => p_r_receipt_rec.charge_cd7,
x_charge_cd8 => p_r_receipt_rec.charge_cd8,
x_applied_amt1 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt1),
x_applied_amt2 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt2),
x_applied_amt3 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt3),
x_applied_amt4 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt4),
x_applied_amt5 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt5),
x_applied_amt6 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt6),
x_applied_amt7 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt7),
x_applied_amt8 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt8),
x_adm_application_id => p_r_receipt_rec.adm_application_id,
x_attribute_category => p_r_receipt_rec.attribute_category,
x_attribute1 => p_r_receipt_rec.attribute1,
x_attribute2 => p_r_receipt_rec.attribute2,
x_attribute3 => p_r_receipt_rec.attribute3,
x_attribute4 => p_r_receipt_rec.attribute4,
x_attribute5 => p_r_receipt_rec.attribute5,
x_attribute6 => p_r_receipt_rec.attribute6,
x_attribute7 => p_r_receipt_rec.attribute7,
x_attribute8 => p_r_receipt_rec.attribute8,
x_attribute9 => p_r_receipt_rec.attribute9,
x_attribute10 => p_r_receipt_rec.attribute10,
x_attribute11 => p_r_receipt_rec.attribute11,
x_attribute12 => p_r_receipt_rec.attribute12,
x_attribute13 => p_r_receipt_rec.attribute13,
x_attribute14 => p_r_receipt_rec.attribute14,
x_attribute15 => p_r_receipt_rec.attribute15,
x_attribute16 => p_r_receipt_rec.attribute16,
x_attribute17 => p_r_receipt_rec.attribute17,
x_attribute18 => p_r_receipt_rec.attribute18,
x_attribute19 => p_r_receipt_rec.attribute19,
x_attribute20 => p_r_receipt_rec.attribute20);
END insert_lb_errors;
PROCEDURE insert_lb_ovfl_errors(p_r_receipt_rec lb_receipt_rec,
p_n_receipt_error_id PLS_INTEGER) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 12-Jun-2003
Purpose : Procedure for inserting data in igs_fi_lb_ovfl_errs
table
Known limitations,enhancements,remarks:
Change History
Who When What
svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
in the igs_fi_lb_ovfl_errs_pkg.insert_row call
***************************************************************** */
l_v_rowid VARCHAR2(25);
igs_fi_lb_ovfl_errs_pkg.insert_row(x_rowid => l_v_rowid,
x_receipt_overflow_error_id => l_n_rec_oflow_err_id,
x_lockbox_receipt_error_id => p_n_receipt_error_id,
x_charge_cd1 => p_r_receipt_rec.charge_cd1,
x_charge_cd2 => p_r_receipt_rec.charge_cd2,
x_charge_cd3 => p_r_receipt_rec.charge_cd3,
x_charge_cd4 => p_r_receipt_rec.charge_cd4,
x_charge_cd5 => p_r_receipt_rec.charge_cd5,
x_charge_cd6 => p_r_receipt_rec.charge_cd6,
x_charge_cd7 => p_r_receipt_rec.charge_cd7,
x_charge_cd8 => p_r_receipt_rec.charge_cd8,
x_applied_amt1 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt1),
x_applied_amt2 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt2),
x_applied_amt3 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt3),
x_applied_amt4 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt4),
x_applied_amt5 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt5),
x_applied_amt6 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt6),
x_applied_amt7 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt7),
x_applied_amt8 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt8));
END insert_lb_ovfl_errors;
PROCEDURE delete_err_success(p_r_rowid rowid) AS
/******************************************************************
Created By : Amit Gairola
Date Created By : 12-Jun-2003
Purpose : Procedure for deleting the successful records
Known limitations,enhancements,remarks:
Change History
Who When What
***************************************************************** */
CURSOR cur_lb_err(cp_rowid rowid) IS
SELECT lockbox_receipt_error_id
FROM igs_fi_lb_rect_errs
WHERE rowid = cp_rowid;
SELECT rowid row_id
FROM igs_fi_lb_ovfl_errs
WHERE lockbox_receipt_error_id = cp_lb_rec_err_id;
igs_fi_lb_ovfl_errs_pkg.delete_row(l_oflow_rec.row_id);
igs_fi_lb_rect_errs_pkg.delete_row(p_r_rowid);
END delete_err_success;
agairola 01-Jul-03 Bug: 3030453 changed the call to the l_t_ro_tab.DELETE
to execute only if the receipt number matches the counter
agairola 01-Jul-03 Bug: 3030673 assigned g_n_retcode = 1 when credits API
invocation fails
***************************************************************** */
l_b_val_err BOOLEAN;
SELECT party_id person_id
FROM hz_parties
WHERE party_number = cp_party_number;
SELECT default_credit_type_id
FROM igs_fi_lockboxes
WHERE lockbox_name = cp_lockbox_name;
SELECT fee_cal_type,
fee_ci_sequence_number
FROM igs_fi_lb_fcis
WHERE lockbox_name = cp_lockbox_name
AND bank_cd = cp_bank_cd;
SELECT credit_type_id
FROM igs_fi_lb_cr_types
WHERE bank_cd = cp_bank_cd
AND lockbox_name = cp_lockbox_name;
insert_lb_errors(p_r_receipt_rec => l_t_rc_tab(l_n_cntr),
p_n_receipt_error_id => l_n_rec_err_id);
insert_lb_ovfl_errors(p_r_receipt_rec => l_t_ro_tab(l_n_cntr1),
p_n_receipt_error_id => l_n_rec_err_id);
l_t_ro_tab.DELETE(l_n_cntr1);
l_t_rc_tab.DELETE(l_n_cntr);
insert_lb_errors(p_r_receipt_rec => l_t_rc_tab(l_n_cntr),
p_n_receipt_error_id => l_n_rec_err_id);
insert_lb_ovfl_errors(p_r_receipt_rec => l_t_ro_tab(l_n_cntr1),
p_n_receipt_error_id => l_n_rec_err_id);
l_t_ro_tab.DELETE(l_n_cntr1);
l_t_rc_tab.DELETE(l_n_cntr);
delete_err_success(p_r_rowid => l_t_rc_tab(l_n_cntr).row_id);
SELECT ROWID row_id, lre.*
FROM igs_fi_lb_rect_errs lre
WHERE lockbox_name = cp_v_lockbox_name
FOR UPDATE NOWAIT;
SELECT ROWID row_id, loe.*
FROM igs_fi_lb_ovfl_errs loe
WHERE lockbox_receipt_error_id = cp_n_lre_id
FOR UPDATE NOWAIT;
update_lbint_status(g_v_error);
update_lbint_status(g_v_error);
update_lbint_status(g_v_success);
g_t_rec_tab.DELETE;
g_lb_int_tab.DELETE;
l_t_err_rec_tab.DELETE;