[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
skharida 26-Jun-2006 Bug# 5208136 - Modified update_refunds proc, removed the obsoleted columns from IGS_FI_REFUNDS_PKG
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified update_refunds and insert_interface.
sapanigr 14-Feb-2006 Bug#5018036 - R12 Repository tuning. Cursors changed in procedures log_person and transfer_to_int.
pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
Modified transfer_to_int() - added call to chk_manage_account()
Added flag g_b_data_found in transfer_int_rec()
pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build - Modified transfer_int_rec and transfer_to_int
Added global variable g_v_offset
vvutukur 03-Jan-2003 Bug#2727216.Modifications done in function lookup_desc and procedure transfer_to_int.
vvutukur 19-Nov-2002 Enh#2584986.Modifications done in insert_interface,update_refunds.
vchappid 03-Jul-2002 Bug#2442030, Refunds Inbound payment information is obsoleted,
reference to payment_date, payment_number, payment_mode is removed from the
table IGS_FI_REFUND_INT_ALL
Refund Record Status 'COMPLETED' has been removed.
vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
agairola 16-May-2002 For bug fix 2374103, modified log person, transfer_int_rec and update_pay_info
vchappid 06-Mar-2002 Enh#2144600, new concurrent manager program update_pay_info
********************************************************************************************** */
g_five_space CONSTANT VARCHAR2(10) :=' ';
g_update_last_person igs_fi_parties_v.person_id%TYPE :=NULL;
SELECT rfnd.*,rfnd.rowid
FROM igs_fi_refunds rfnd
WHERE (rfnd.person_id=cp_person_id OR (cp_person_id IS NULL))
AND rfnd.transfer_status='TODO'
AND rfnd.source_refund_id IS NOT NULL
AND (TRUNC(rfnd.voucher_date) >= TRUNC(cp_start_date) OR (cp_start_date IS NULL))
AND (TRUNC(rfnd.voucher_date) <= TRUNC(cp_end_date) OR (cp_end_date IS NULL))
ORDER BY rfnd.person_id,rfnd.refund_id;
SELECT rfnd.*,rfnd.rowid
FROM igs_fi_refunds rfnd
WHERE (rfnd.person_id=cp_person_id OR (cp_person_id IS NULL))
AND rfnd.transfer_status='TODO'
AND rfnd.source_refund_id IS NULL
AND NVL(rfnd.reversal_ind,'N') <> 'Y'
AND (TRUNC(rfnd.voucher_date) >= TRUNC(cp_start_date) OR (cp_start_date IS NULL))
AND (TRUNC(rfnd.voucher_date) <= TRUNC(cp_end_date) OR (cp_end_date IS NULL))
ORDER BY rfnd.person_id,rfnd.refund_id;
SELECT party_number
FROM hz_parties
WHERE party_id= cp_person_id;
SELECT invoice_number
FROM igs_fi_inv_int
WHERE invoice_id=p_invoice_id;
SELECT ci.start_dt start_dt,ci.end_dt end_dt
FROM igs_fi_f_typ_ca_inst_all ftci, igs_ca_inst ci
WHERE ftci.fee_type=p_fee_type
AND ftci.fee_cal_type=p_fee_cal_type
AND ftci.fee_ci_sequence_number=p_sequence_number
AND ci.cal_type = ftci.fee_cal_type
AND ci.sequence_number = ftci.fee_ci_sequence_number;
PROCEDURE update_refunds(p_status IN igs_fi_refunds.transfer_status%TYPE,
p_cur_ref_upd IN cur_refund%ROWTYPE ,
p_dml_status OUT NOCOPY BOOLEAN,
p_err_msg OUT NOCOPY fnd_new_messages.message_text%TYPE) AS
/***********************************************************************************************
Created By : Sarakshi
Date Created By: 1-Mar-2001
Purpose : To update records in refunds table
Known limitations,enhancements,remarks:
Change History
Who When What
skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_REFUNDS
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refunds
is now rounded off to currency precision
vvutukur 19-Nov-2002 Modified the call to igs_fi_refunds_pkg.update_row to add new parameters gl_date,
reversal_gl_date.
********************************************************************************************** */
BEGIN
p_dml_status:=TRUE;
igs_fi_refunds_pkg.update_row(X_ROWID => p_cur_ref_upd.rowid,
X_REFUND_ID => p_cur_ref_upd.refund_id,
X_VOUCHER_DATE => p_cur_ref_upd.voucher_date,
X_PERSON_ID => p_cur_ref_upd.person_id,
X_PAY_PERSON_ID => p_cur_ref_upd.pay_person_id,
X_DR_GL_CCID => p_cur_ref_upd.dr_gl_ccid,
X_CR_GL_CCID => p_cur_ref_upd.cr_gl_ccid,
X_DR_ACCOUNT_CD => p_cur_ref_upd.dr_account_cd,
X_CR_ACCOUNT_CD => p_cur_ref_upd.cr_account_cd,
X_REFUND_AMOUNT => igs_fi_gen_gl.get_formatted_amount(p_cur_ref_upd.refund_amount),
X_FEE_TYPE => p_cur_ref_upd.fee_type,
X_FEE_CAL_TYPE => p_cur_ref_upd.fee_cal_type,
X_FEE_CI_SEQUENCE_NUMBER => p_cur_ref_upd.fee_ci_sequence_number,
X_SOURCE_REFUND_ID => p_cur_ref_upd.source_refund_id,
X_INVOICE_ID => p_cur_ref_upd.invoice_id,
X_TRANSFER_STATUS => p_status,
X_REVERSAL_IND => p_cur_ref_upd.reversal_ind,
X_REASON => p_cur_ref_upd.reason,
X_ATTRIBUTE_CATEGORY => p_cur_ref_upd.attribute_category,
X_ATTRIBUTE1 => p_cur_ref_upd.attribute1,
X_ATTRIBUTE2 => p_cur_ref_upd.attribute2,
X_ATTRIBUTE3 => p_cur_ref_upd.attribute3,
X_ATTRIBUTE4 => p_cur_ref_upd.attribute4,
X_ATTRIBUTE5 => p_cur_ref_upd.attribute5,
X_ATTRIBUTE6 => p_cur_ref_upd.attribute6,
X_ATTRIBUTE7 => p_cur_ref_upd.attribute7,
X_ATTRIBUTE8 => p_cur_ref_upd.attribute8,
X_ATTRIBUTE9 => p_cur_ref_upd.attribute9,
X_ATTRIBUTE10 => p_cur_ref_upd.attribute10,
X_ATTRIBUTE11 => p_cur_ref_upd.attribute11,
X_ATTRIBUTE12 => p_cur_ref_upd.attribute12,
X_ATTRIBUTE13 => p_cur_ref_upd.attribute13,
X_ATTRIBUTE14 => p_cur_ref_upd.attribute14,
X_ATTRIBUTE15 => p_cur_ref_upd.attribute15,
X_ATTRIBUTE16 => p_cur_ref_upd.attribute16,
X_ATTRIBUTE17 => p_cur_ref_upd.attribute17,
X_ATTRIBUTE18 => p_cur_ref_upd.attribute18,
X_ATTRIBUTE19 => p_cur_ref_upd.attribute19,
X_ATTRIBUTE20 => p_cur_ref_upd.attribute20,
X_MODE => 'R',
X_GL_DATE => p_cur_ref_upd.gl_date,
X_REVERSAL_GL_DATE => p_cur_ref_upd.reversal_gl_date
);
END update_refunds;
PROCEDURE insert_interface(p_cur_ref_ins IN cur_ref%ROWTYPE ,
p_dml_status OUT NOCOPY BOOLEAN,
p_err_msg OUT NOCOPY fnd_new_messages.message_text%TYPE) AS
/***********************************************************************************************
Created By : Sarakshi
Date Created By: 1-Mar-2001
Purpose : To insert records in the refunds interface table
Known limitations,enhancements,remarks:
Change History
Who When What
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refund_int
is now rounded off to currency precision
vvutukur 19-Nov-2002 Enh#2584986.Modified the call to igs_fi_refund_int_pkg.insert_row to include the new parameter
x_gl_date.
********************************************************************************************** */
l_rowid VARCHAR2(25) ;
igs_fi_refund_int_pkg.insert_row( X_ROWID => l_rowid,
X_REFUND_ID => p_cur_ref_ins.refund_id,
X_VOUCHER_DATE => p_cur_ref_ins.voucher_date,
X_PERSON_ID => p_cur_ref_ins.person_id,
X_PAY_PERSON_ID => p_cur_ref_ins.pay_person_id,
X_DR_GL_CCID => p_cur_ref_ins.dr_gl_ccid,
X_CR_GL_CCID => p_cur_ref_ins.cr_gl_ccid,
X_DR_ACCOUNT_CD => p_cur_ref_ins.dr_account_cd,
X_CR_ACCOUNT_CD => p_cur_ref_ins.cr_account_cd,
X_REFUND_AMOUNT => igs_fi_gen_gl.get_formatted_amount(p_cur_ref_ins.refund_amount),
X_FEE_TYPE => p_cur_ref_ins.fee_type,
X_FEE_CAL_TYPE => p_cur_ref_ins.fee_cal_type,
X_FEE_CI_SEQUENCE_NUMBER => p_cur_ref_ins.fee_ci_sequence_number,
X_SOURCE_REFUND_ID => p_cur_ref_ins.source_refund_id,
X_INVOICE_ID => p_cur_ref_ins.invoice_id,
X_REASON => p_cur_ref_ins.reason,
X_ATTRIBUTE_CATEGORY => p_cur_ref_ins.attribute_category,
X_ATTRIBUTE1 => p_cur_ref_ins.attribute1,
X_ATTRIBUTE2 => p_cur_ref_ins.attribute2,
X_ATTRIBUTE3 => p_cur_ref_ins.attribute3,
X_ATTRIBUTE4 => p_cur_ref_ins.attribute4,
X_ATTRIBUTE5 => p_cur_ref_ins.attribute5,
X_ATTRIBUTE6 => p_cur_ref_ins.attribute6,
X_ATTRIBUTE7 => p_cur_ref_ins.attribute7,
X_ATTRIBUTE8 => p_cur_ref_ins.attribute8,
X_ATTRIBUTE9 => p_cur_ref_ins.attribute9,
X_ATTRIBUTE10 => p_cur_ref_ins.attribute10,
X_ATTRIBUTE11 => p_cur_ref_ins.attribute11,
X_ATTRIBUTE12 => p_cur_ref_ins.attribute12,
X_ATTRIBUTE13 => p_cur_ref_ins.attribute13,
X_ATTRIBUTE14 => p_cur_ref_ins.attribute14,
X_ATTRIBUTE15 => p_cur_ref_ins.attribute15,
X_ATTRIBUTE16 => p_cur_ref_ins.attribute16,
X_ATTRIBUTE17 => p_cur_ref_ins.attribute17,
X_ATTRIBUTE18 => p_cur_ref_ins.attribute18,
X_ATTRIBUTE19 => p_cur_ref_ins.attribute19,
X_ATTRIBUTE20 => p_cur_ref_ins.attribute20,
X_MODE => 'R',
X_GL_DATE => p_cur_ref_ins.gl_date
);
END insert_interface;
Purpose : To Transfer refunds records to interface table and to update the status
of the refund record accordingly.
Known limitations,enhancements,remarks:
Change History
Who When What
pathipat 12-May-2003 Enh 2831569 - Commercial Receivables build
Added flag (g_b_data_found) to keep track of no-data-found conditions
pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build
The status for Reversed Refund record and its Source is set to Offset
instead of Transferred
agairola 16-May-2002 Modified the cursor cur_check and cur_ref removing FOR UPDATE clause
Also made individual calls to Log_Person in case the Refunds table
is updated for bug 2374103
********************************************************************************************** */
CURSOR cur_check(cp_refund_id igs_fi_refunds.refund_id%TYPE) IS
SELECT r.*,r.rowid
FROM igs_fi_refunds r
WHERE refund_id = cp_refund_id
AND NVL(reversal_ind,'N') = 'Y';
update_refunds(g_v_offset, l_cur_ref,l_status,l_err_msg);
update_refunds(g_v_offset, l_cur_check,l_status,l_err_msg);
insert_interface(l_cur_ref,l_status,l_err_msg);
update_refunds(g_transferred, l_cur_ref,l_status,l_err_msg);
insert_interface(l_cur_refund,l_status,l_err_msg);
update_refunds(g_transferred, l_cur_refund,l_status,l_err_msg);
Purpose : To Transfer refunds records to interface table and to update the status
of the refund record accordingly.
Known limitations,enhancements,remarks:
Change History
Who When What
sapanigr 14-Feb-2006 Bug #5018036. Cursor cur_pers modified to query hz_parties instead of igs_fi_parties_v
ridas 13-Feb-2006 Bug #5021084. Added new parameter lv_group_type in call to igf_ap_ss_pkg.get_pid
pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
Added validation for manage_account - call to chk_manage_account()
Added code to log messages for test run = Y and for no-data-found cases
Increased width of l_v_sql_query from 1000 to 32767
pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build
1. Modified cursor cur_pers_grp - used igs_pe_all_persid_group_v instead of igs_pe_persid_group
Changed declaration of l_group_code accordingly.
2. Added validation to check for Refunds Destination before allowing the process to be run
3. Modified approach to identify persons in a person id group
vvutukur 03-Jan-2003 Bug#2727216.Modified the logic for logging the parameters, by removing the
redundant code.
vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
********************************************************************************************** */
CURSOR cur_pers(cp_person_id hz_parties.party_id%TYPE) IS
SELECT party_number
FROM hz_parties
WHERE party_id=cp_person_id;
SELECT group_cd
FROM igs_pe_all_persid_group_v
WHERE group_id = p_person_id_grp
AND closed_ind = 'N';
PROCEDURE update_pay_info ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_person_id IN igs_fi_parties_v.person_id%TYPE,
p_person_id_group IN igs_pe_persid_group.group_id%TYPE,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_test_run IN igs_lookups_view.lookup_code%TYPE)
AS
/***********************************************************************************************
Created By : vchappid
Date Created By: 04-MAR-2001
Purpose : Concurrent Manager Procedure for updating the payment information for the
Refunds transactions
Known limitations,enhancements,remarks:
Change History
Who When What
vchappid 03-Jul-2002 Bug# 2442030, Concurrent Process is obsoleted, and hence nullified the code
vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
agairola 16-May-2002 Moidified the cursor cur_refunds to remove FOR UPDATE clause
********************************************************************************************** */
BEGIN
--Bug# 2442030, Concurrent Process is obsoleted, and hence nullified the code
FND_MESSAGE.Set_Name('IGS',
'IGS_GE_OBSOLETE_JOB');
END update_pay_info;