[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ac.check_id,gjh.currency_code,gjh.currency_conversion_type,
gjh.currency_conversion_rate,ac.exchange_date,
gjl.accounted_dr,gjl.accounted_cr,
gjl.entered_dr, gjl.entered_cr, gjl.code_combination_id,
gjl.ROWID,
gjl.reference_4
FROM ap_checks ac, gl_je_lines gjl, gl_je_headers gjh
WHERE ac.void_date IS NULL
AND gjl.reference_1 = TO_CHAR(v_treasury_confirmation_id)
AND ac.check_id = gjl.reference_3
AND gjh.je_header_id = gjl.je_header_id
AND gjh.je_category = 'Treasury Confirmation'
AND gjh.je_source = 'Payables';
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'N'
WHERE treasury_confirmation_id = g_treasury_confirmation_id;
L_SELECT_STR VARCHAR2(1000) ;
-- AKA, declare cursor to select
-- corrected treasury pay number and check id from offsets table
-- need to join to ap_checks on check id to get the correct batch name
CURSOR cur_corr_treas_pay_num IS
SELECT fto.corrected_treasury_pay_number, fto.check_id
FROM fv_tc_offsets fto,
ap_checks ac,
iby_payments_all ipa
WHERE ac.check_id = fto.check_id
AND ac.payment_id = ipa.payment_id
AND ipa.payment_instruction_id = v_payment_instruction_id
AND ipa.org_id = v_org_id;
SELECT ftcr.range_from, ftcr.range_to, ftc.payment_instruction_id,ftc.treasury_doc_date
FROM fv_treasury_confirmations ftc,
fv_treasury_check_ranges ftcr
WHERE ftc.treasury_confirmation_id = g_treasury_confirmation_id
AND ftc.treasury_confirmation_id = ftcr.treasury_confirmation_id;
SELECT ftc.payment_instruction_id
INTO v_payment_instruction_id
FROM fv_treasury_confirmations ftc
WHERE ftc.treasury_confirmation_id = g_treasury_confirmation_id;
OPEN vl_check_id_cur FOR l_select_str USING v_payment_instruction_id;
UPDATE ap_checks c
SET treasury_pay_number = v_begin_doc,
treasury_pay_date = v_confirm_date,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE c.check_id = l_check_tbl(l_row_num).check_id;
-- AKA, need to update ap_checks if a corrected treasury pay number
-- for a payment within the batch being processed has been entered
OPEN cur_corr_treas_pay_num;
UPDATE ap_checks
SET treasury_pay_number = v_corr_treas_pay_num
WHERE check_id = v_offset_check_id;
SELECT period_name
INTO v_period
FROM gl_period_statuses g
WHERE g.application_id = c_gl_appl_id
AND g.set_of_books_id = v_set_of_books_id
AND g.closing_status IN ('O', 'F')
AND v_confirm_date BETWEEN
g.start_date AND g.end_date
AND g.adjustment_period_flag = 'N';
SELECT period_name
INTO v_period
FROM gl_period_statuses g
WHERE g.application_id = c_gl_appl_id
AND g.set_of_books_id = v_set_of_books_id
AND g.adjustment_period_flag = 'N'
AND g.start_date = (SELECT MIN(start_date)
FROM gl_period_statuses g2
WHERE g2.application_id = c_gl_appl_id
AND g2.set_of_books_id = v_set_of_books_id
AND g2.end_date > v_confirm_date
AND g2.closing_status IN ('O', 'F')
AND g2.adjustment_period_flag = 'N');
SELECT chart_of_accounts_id, currency_code
INTO v_chart_of_accounts_id, v_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = v_set_of_books_id;
SELECT apd.code_combination_id,
apd.entered_cr, -- transaction currency amt
apd.entered_dr,
apd.accounted_cr, -- functional currency amt
apd.accounted_dr,
apd.currency_code, -- transction currency
apd.currency_conversion_type,
apd.currency_conversion_date,
apd.currency_conversion_rate,
apd.ae_line_type_code,
aip.invoice_id,
ac.check_id
FROM fv_treasury_confirmations ftc,
ap_checks ac,
ap_invoice_payments aip,
ap_ae_lines apd,
iby_payments_all ipa
WHERE ftc.treasury_confirmation_id = x_treasury_confirmation_id
AND ftc.payment_instruction_id = ipa.payment_instruction_id
AND ipa.payment_id = ac.payment_id
AND ac.check_id = aip.check_id
AND (( aip.invoice_payment_id = apd.source_id
AND apd.source_table ='AP_INVOICE_PAYMENTS')
OR
( aip.check_id = apd.source_id
AND apd.source_table = 'AP_CHECKS'))
AND apd.ae_line_type_code IN ('CASH','CASH CLEARING','FUTURE PAYMENT')
AND ac.void_date IS NULL;
SELECT t.treasury_doc_date
INTO v_treasury_doc_date
FROM fv_treasury_confirmations t
WHERE treasury_confirmation_id = x_treasury_confirmation_id;
SELECT p.start_date, p.end_date
INTO v_period_start_date, v_period_end_date
FROM gl_period_statuses p
WHERE p.period_name = v_period
AND p.application_id = c_gl_appl_id
AND p.set_of_books_id = v_set_of_books_id
AND p.adjustment_period_flag = 'N';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'inserting into gl_interface');
INSERT INTO gl_interface(status, set_of_books_id,
accounting_date, currency_code,
functional_currency_code,
user_currency_conversion_type,
currency_conversion_date,
currency_conversion_rate,
date_created, created_by,
actual_flag, user_je_category_name,
user_je_source_name, segment1,
segment2, segment3,
segment4, segment5,
segment6, segment7,
segment8, segment9,
segment10, segment11,
segment12, segment13,
segment14, segment15,
segment16, segment17,
segment18, segment19,
segment20, segment21,
segment22, segment23,
segment24, segment25,
segment26, segment27,
segment28, segment29,
segment30, entered_dr,
accounted_dr,
reference1, reference21,
reference23, reference24,
reference25, reference26,
group_id)
VALUES('NEW', v_set_of_books_id,
v_accounting_date, v_currency_code,
v_functional_curr_code,
v_curr_conv_type,
v_curr_conv_date,
v_curr_conv_rate,
SYSDATE, fnd_global.user_id,
'A', 'Treasury Confirmation',
'Payables', a_segments(1),
a_segments(2), a_segments(3),
a_segments(4), a_segments(5),
a_segments(6), a_segments(7),
a_segments(8), a_segments(9),
a_segments(10), a_segments(11),
a_segments(12), a_segments(13),
a_segments(14), a_segments(15),
a_segments(16), a_segments(17),
a_segments(18), a_segments(19),
a_segments(20), a_segments(21),
a_segments(22), a_segments(23),
a_segments(24), a_segments(25),
a_segments(26), a_segments(27),
a_segments(28), a_segments(29),
a_segments(30), v_amount, -- transaction amt
v_amount_acct, -- functional amt
c_reference1, v_reference21,
v_reference3, v_invoice_id,
v_org_id, v_treasury_doc_date,
x_group_id);
INSERT INTO gl_interface(status, set_of_books_id,
accounting_date, currency_code,
functional_currency_code,
user_currency_conversion_type,
currency_conversion_date,
currency_conversion_rate,
date_created, created_by,
actual_flag, user_je_category_name,
user_je_source_name, segment1,
segment2, segment3,
segment4, segment5,
segment6, segment7,
segment8, segment9,
segment10, segment11,
segment12, segment13,
segment14, segment15,
segment16, segment17,
segment18, segment19,
segment20, segment21,
segment22, segment23,
segment24, segment25,
segment26, segment27,
segment28, segment29,
segment30, entered_cr, -- transaction amt
accounted_cr, -- functional amt
reference1, reference21,
reference23, reference24,
reference25, reference26,
group_id)
VALUES('NEW', v_set_of_books_id,
v_accounting_date, v_currency_code,
v_functional_curr_code,
v_curr_conv_type,
v_curr_conv_date,
v_curr_conv_rate,
SYSDATE, fnd_global.user_id,
'A', 'Treasury Confirmation',
'Payables', a_segments(1),
a_segments(2), a_segments(3),
a_segments(4), a_segments(5),
a_segments(6), a_segments(7),
a_segments(8), a_segments(9),
a_segments(10), a_segments(11),
a_segments(12), a_segments(13),
a_segments(14), a_segments(15),
a_segments(16), a_segments(17),
a_segments(18), a_segments(19),
a_segments(20), a_segments(21),
a_segments(22), a_segments(23),
a_segments(24), a_segments(25),
a_segments(26), a_segments(27),
a_segments(28), a_segments(29),
a_segments(30), v_amount,-- transaction amt
v_amount_acct, -- functional amt
c_reference1, v_reference21,
v_reference3, v_invoice_id,
v_org_id, v_treasury_doc_date,
x_group_id);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'insert into gl_interface');
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'After correcting error please reselect Confirm or Back OUT NOCOPY from Treasury Confirmation or resubmit Disbursements in Transit Voided Checks.');
DELETE FROM GL_INTERFACE
WHERE user_je_source_name = 'Payables'
AND set_of_books_id = v_set_of_books_id
AND group_id = x_group_id;
UPDATE fv_treasury_confirmations
-- if the process is confirm, status is Not Confirmed,
-- if the process is backout, status is Confirmed
SET confirmation_status_flag = DECODE(v_process_job, 'C', 'N', 'B', 'Y'),
gl_period = NULL,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE treasury_confirmation_id = x_treasury_confirmation_id;
-- Call first program to update treasury payments FV_DISB_IN_TRANSIT.
fv_disb_in_transit.confirm_treas_payment (
v_treasury_confirmation_id,
x_err_code,
x_err_stage,
v_period);
SELECT gl_interface_control_s.NEXTVAL
INTO x_group_id
FROM dual;
SELECT dit_flag
INTO v_dit_flag
FROM fv_operating_units;
SELECT treasury_doc_date
INTO v_treasury_doc_date
FROM fv_treasury_confirmations
WHERE treasury_confirmation_id = v_treasury_confirmation_id;
SELECT period_name,g.start_date,g.end_date
INTO v_period,v_period_start_date,v_period_end_date
FROM gl_period_statuses g
WHERE g.application_id = c_gl_appl_id
AND g.set_of_books_id = v_set_of_books_id
AND g.closing_status IN ('O', 'F')
AND v_treasury_doc_date BETWEEN
g.start_date AND g.end_date
AND g.adjustment_period_flag = 'N';
SELECT period_name,g.end_date,g.start_date
INTO v_period,v_period_end_date,v_period_start_date
FROM gl_period_statuses g
WHERE g.application_id = c_gl_appl_id
AND g.set_of_books_id = v_set_of_books_id
AND g.adjustment_period_flag = 'N'
AND g.start_date = (SELECT MIN(start_date)
FROM gl_period_statuses g2
WHERE g2.application_id = c_gl_appl_id
AND g2.set_of_books_id = v_set_of_books_id
AND g2.end_date > v_treasury_doc_date
AND g2.closing_status IN ('O', 'F')
AND g2.adjustment_period_flag = 'N');
SELECT gl_interface_control_s.NEXTVAL
INTO x_group_id
FROM dual;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'starting insert');
INSERT INTO gl_interface(status, set_of_books_id,
accounting_date, currency_code,
user_currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
functional_currency_code,
date_created, created_by,
actual_flag, user_je_category_name,
user_je_source_name, segment1,
segment2, segment3,
segment4, segment5,
segment6, segment7,
segment8, segment9,
segment10, segment11,
segment12, segment13,
segment14, segment15,
segment16, segment17,
segment18, segment19,
segment20, segment21,
segment22, segment23,
segment24, segment25,
segment26, segment27,
segment28, segment29,
segment30,
entered_dr, entered_cr,
accounted_dr,accounted_cr,
reference1,
reference21,
reference23,
reference24,
reference25,
reference26,
group_id)
VALUES('NEW', v_set_of_books_id,
v_accounting_date, v_currency_code,
v_curr_con_type,v_curr_con_rate,
v_curr_con_date,
v_func_currency_code,
SYSDATE, fnd_global.user_id,
'A', 'Treasury Confirmation',
'Payables', a_segments(1),
a_segments(2), a_segments(3),
a_segments(4), a_segments(5),
a_segments(6), a_segments(7),
a_segments(8), a_segments(9),
a_segments(10), a_segments(11),
a_segments(12), a_segments(13),
a_segments(14), a_segments(15),
a_segments(16), a_segments(17),
a_segments(18), a_segments(19),
a_segments(20), a_segments(21),
a_segments(22), a_segments(23),
a_segments(24), a_segments(25),
a_segments(26), a_segments(27),
a_segments(28), a_segments(29),
a_segments(30),
v_debit_amount, v_credit_amount,
v_dr_acct_amt,v_cr_acct_amt,
c_reference1,
v_tc_id,
v_check_id,
v_invoice_id,
v_org_id,
v_treasury_doc_date,
x_group_id);
l_insert_required BOOLEAN := FALSE;
l_insert_je_line BOOLEAN := FALSE;
SELECT /*+ ordered */ DISTINCT
fvt.check_id,
fvt.rowid,
ftc.treasury_confirmation_id,
fvt.processed_flag,
ac.exchange_date,
ac.void_date
FROM fv_treasury_confirmations_all ftc,
fv_voided_checks fvt,
ap_checks_all ac ,
ap_invoice_payments_all apip,
iby_payments_all ipa
WHERE ac.check_id = fvt.check_id
AND ac.payment_id = ipa.payment_id
AND ipa.payment_instruction_id = ftc.payment_instruction_id
AND ftc.checkrun_name IS NULL
AND NVL(fvt.processed_flag,'U') IN ('U', 'S')
AND ftc.set_of_books_id = p_set_of_books_id
AND ftc.org_id = p_org_id
AND ac.org_id = p_org_id
AND fvt.org_id = p_org_id
AND apip.check_id = ac.check_id
AND apip.reversal_flag = 'Y'
AND apip.reversal_inv_pmt_id is not null
AND ac.last_update_date < fvt.creation_date
AND ac.status_lookup_code = 'VOIDED'
AND (
(ftc.confirmation_status_flag = 'Y'
AND apip.creation_date > ftc.creation_date)
OR
(ftc.confirmation_status_flag in ('B','N')
AND apip.creation_date BETWEEN ftc.creation_date and ftc.last_update_date)
)
AND EXISTS (SELECT null
FROM gl_ledgers_public_v glpv,
gl_je_headers gjh,
gl_je_lines gjl
WHERE glpv.ledger_id = p_set_of_books_id
--AND gjh.set_of_books_id = gsob.set_of_books_id
AND gjh.ledger_id = glpv.ledger_id
AND gjh.je_category = 'Treasury Confirmation'
AND gjh.je_source = 'Payables'
AND gjh.je_header_id = gjl.je_header_id
--AND gjl.set_of_books_id = gsob.set_of_books_id
AND gjl.ledger_id = glpv.ledger_id
AND gjl.reference_3 = to_char(ac.check_id)
AND gjl.reference_1 = to_char(ftc.treasury_confirmation_id)
AND gjl.reference_4 = to_char(apip.invoice_id)
);
SELECT /*+ choose*/
gjl.entered_dr,
gjl.entered_cr,
gjh.currency_conversion_type,
gjh.currency_conversion_rate,
gjl.code_combination_id,
gjl.accounted_cr,
gjl.accounted_dr,
gjh.currency_code,
gjl.reference_4 invoice_id,
gjl.rowid gl_rowid
FROM gl_ledgers_public_v glpv,
gl_je_lines gjl,
gl_je_headers gjh
WHERE glpv.ledger_id = p_set_of_books_id
AND gjh.ledger_id = glpv.ledger_id
AND gjl.ledger_id = glpv.ledger_id
AND gjh.je_category = 'Treasury Confirmation'
AND gjh.je_source = 'Payables'
AND gjl.reference_3 = p_check_id
AND gjl.reference_1 = p_treas_conf_id
AND gjh.je_header_id = gjl.je_header_id;
fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'INSERT INTO fv_voided_checks');
INSERT INTO fv_voided_checks
(
void_id,
checkrun_name,
check_id,
processed_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id
)
SELECT fv_voided_checks_s.nextval,
checkrun_name,
check_id,
'U',
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
org_id
FROM ap_checks_all ac
WHERE org_id = l_org_id
AND void_date IS NOT NULL
AND (ac.checkrun_name IS NOT NULL OR ac.payment_id IS NOT NULL)
AND NOT EXISTS (SELECT 1
FROM fv_voided_checks fvc
WHERE fvc.check_id = ac.check_id
AND fvc.org_id = ac.org_id);
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'insert fv_voided_checks1',l_err_stage);
SELECT gl_interface_control_s.NEXTVAL
INTO l_group_id
FROM dual;
select accounting_date into v_accounting_date
from ap_invoice_payments_all
where check_id =voided_checks_list_rec.check_id
and REVERSAL_INV_PMT_ID is not null
and REVERSAL_FLAG = 'Y'
and rownum =1;
SELECT end_date
INTO v_accounting_date
FROM gl_period_statuses g
WHERE g.application_id = c_gl_appl_id
AND g.set_of_books_id = v_set_of_books_id
AND g.closing_status IN ('O', 'F')
AND v_accounting_date BETWEEN
g.start_date AND g.end_date
AND g.adjustment_period_flag = 'N';
SELECT end_date
INTO v_accounting_date
FROM gl_period_statuses g
WHERE g.application_id = c_gl_appl_id
AND g.set_of_books_id = v_set_of_books_id
AND g.adjustment_period_flag = 'N'
AND g.start_date = (SELECT MIN(start_date)
FROM gl_period_statuses g2
WHERE g2.application_id = c_gl_appl_id
AND g2.set_of_books_id = v_set_of_books_id
AND g2.end_date > v_accounting_date
AND g2.closing_status IN ('O', 'F')
AND g2.adjustment_period_flag = 'N');
INSERT INTO gl_interface
(
status,
set_of_books_id,
accounting_date,
currency_code,
user_currency_conversion_type,
currency_conversion_date,
currency_conversion_rate,
functional_currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference24,
reference21,
reference23,
reference25,
reference26,
group_id,
code_combination_id
)
VALUES
(
'NEW',
v_set_of_books_id,
v_accounting_date,
cur_process_void_check_rec.currency_code,
cur_process_void_check_rec.currency_conversion_type,
voided_checks_list_rec.exchange_date,
cur_process_void_check_rec.currency_conversion_rate,
l_func_currency_code,
SYSDATE,
fnd_global.user_id,
'A',
'Treasury Confirmation',
'Payables',
cur_process_void_check_rec.entered_cr,
cur_process_void_check_rec.entered_dr,
cur_process_void_check_rec.accounted_cr,
cur_process_void_check_rec.accounted_dr,
l_reference1,
cur_process_void_check_rec.invoice_id,
voided_checks_list_rec.treasury_confirmation_id,
voided_checks_list_rec.check_id,
l_org_id,
voided_checks_list_rec.void_date,
l_group_id,
cur_process_void_check_rec.code_combination_id
);
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'insert gl_interface',l_err_stage);
UPDATE fv_voided_checks
SET processed_flag = 'S'
WHERE check_id = voided_checks_list_rec.check_id
AND org_id = l_org_id;
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks1',l_err_stage);
INSERT INTO gl_interface_control
(
je_source_name,
status,
interface_run_id,
group_id,
set_of_books_id
)
VALUES
(
'Payables',
'S',
l_interface_run_id,
l_group_id,
v_set_of_books_id
);
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'insert gl_interface_control',l_err_stage);
SELECT COUNT(*)
INTO l_dummy
FROM gl_interface
WHERE group_id = l_group_id
AND set_of_books_id = v_set_of_books_id
AND user_je_source_name = 'Payables';
UPDATE fv_voided_checks
SET processed_flag = 'P'
WHERE processed_flag = 'S'
AND org_id = l_org_id;
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks2',l_err_stage);
UPDATE fv_voided_checks
SET processed_flag = 'X'
WHERE processed_flag = 'U'
AND org_id = l_org_id;
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks2',l_err_stage);
SELECT segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
INTO a_segments(1),
a_segments(2),
a_segments(3),
a_segments(4),
a_segments(5),
a_segments(6),
a_segments(7),
a_segments(8),
a_segments(9),
a_segments(10),
a_segments(11),
a_segments(12),
a_segments(13),
a_segments(14),
a_segments(15),
a_segments(16),
a_segments(17),
a_segments(18),
a_segments(19),
a_segments(20),
a_segments(21),
a_segments(22),
a_segments(23),
a_segments(24),
a_segments(25),
a_segments(26),
a_segments(27),
a_segments(28),
a_segments(29),
a_segments(30)
FROM gl_code_combinations
WHERE code_combination_id = v_gs_ccid;
SELECT 1
INTO v_dummy
FROM gl_je_categories
WHERE je_category_name = 'Treasury Confirmation';
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = DECODE(v_button_name, 'TREASURY_CONFIRMATION.CONFIRM', 'N', 'TREASURY_CONFIRMATION.BACK_OUT', 'Y')
WHERE treasury_confirmation_id = v_treasury_confirmation_id;
-- Insert a control record in Gl_INTERFACE record for the Gl
-- Import to work
INSERT INTO gl_interface_control(je_source_name,status,
interface_run_id,group_id,set_of_books_id)
VALUES ('Payables', 'S', x_interface_run_id, x_group_id,
v_set_of_books_id);
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y'
WHERE treasury_confirmation_id = v_treasury_confirmation_id;
SELECT COUNT(*)
INTO v_dummy
FROM gl_interface
WHERE group_id = x_group_id
AND set_of_books_id = v_set_of_books_id
AND user_je_source_name = 'Payables';
UPDATE AP_CHECKS
SET treasury_pay_number = NULL,
treasury_pay_date = NULL,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_id IN
(SELECT payment_id
FROM fv_treasury_confirmations ftca,
iby_payments_all ipa
WHERE ftca.treasury_confirmation_id = v_treasury_confirmation_id
AND ftca.payment_instruction_id = ipa.payment_instruction_id);
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'B',
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE treasury_confirmation_id = v_treasury_confirmation_id;
DELETE FROM fv_tc_offsets
WHERE check_id IN (SELECT check_id
FROM ap_checks ac,
fv_treasury_confirmations ftc,
iby_payments_all ipa
WHERE ftc.treasury_confirmation_id = v_treasury_confirmation_id
AND ftc.payment_instruction_id = ipa.payment_instruction_id
AND ipa.payment_id = ac.payment_id);
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y',
gl_period = v_period,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE treasury_confirmation_id = v_treasury_confirmation_id;