[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_dummy
FROM gl_je_categories
WHERE je_category_name = 'Treasury Confirmation';
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'N'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y'
WHERE treasury_confirmation_id = g_treasury_conf_id;
SELECT payment_instruction_id
,treasury_doc_date
,set_of_books_id
,org_id
,checkrun_name
INTO g_payment_instr_id
,g_accounting_date
,g_ledger_id
,g_org_id
,g_checkrun_name
FROM fv_treasury_confirmations
WHERE treasury_confirmation_id = g_treasury_conf_id;
SELECT dit_flag
INTO l_dit_flag
FROM fv_operating_units
where org_id = g_org_id ;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'N'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'N'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'B'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y'
WHERE treasury_confirmation_id = g_treasury_conf_id;
l_select_str VARCHAR2(1000);
SELECT ac.check_id
FROM ap_checks ac
,fv_treasury_confirmations ftc
WHERE ftc.treasury_confirmation_id = g_treasury_conf_id
AND ftc.payment_instruction_id = ac.payment_instruction_id
AND ac.org_id = g_org_id
ORDER BY ac.check_id;
SELECT fto.corrected_treasury_pay_number, fto.check_id
FROM fv_tc_offsets fto,
ap_checks ac,
iby_pay_instructions_all ipa
WHERE ac.check_id = fto.check_id
AND ipa.payment_instruction_id = ac.payment_instruction_id
AND ipa.payment_instruction_id = g_payment_instr_id;
SELECT ftcr.range_from, ftcr.range_to
FROM fv_treasury_check_ranges ftcr
WHERE ftcr.treasury_confirmation_id = g_treasury_conf_id;
SELECT checkrun_name
INTO l_checkrun_name
FROM FV_TREASURY_CONFIRMATIONS_ALL
WHERE payment_instruction_id = g_payment_instr_id
AND org_id = g_org_id;
SELECT appp.program_name
INTO l_pay_fmt_program_name
FROM ap_inv_selection_criteria_all apisc ,
ap_payment_programs appp
WHERE apisc.checkrun_name = g_checkrun_name
AND apisc.org_id = g_org_id
AND appp.program_id = apisc.program_id ;
l_select_str := 'SELECT check_id FROM fv_tc_check_v WHERE' ||
' checkrun_name = g_checkrun_name ORDER BY '||
' routing_transit_num , num_1099, check_number' ;
l_select_str := 'SELECT check_id FROM fv_tc_check_v WHERE' ||
' checkrun_name = g_checkrun_name ORDER BY '||
' num_1099, check_number' ;
l_select_str := 'SELECT check_id FROM fv_tc_check_v WHERE' ||
' checkrun_name = g_checkrun_name' ||
' ORDER BY check_number';
OPEN l_upg_check_id_cur FOR l_select_str;
UPDATE ap_checks c
SET treasury_pay_number = l_begin_doc,
treasury_pay_date = g_accounting_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;
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,
ac.checkrun_name,
ac.check_id,
'N',
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
ac.org_id
FROM ap_checks ac,
ap_payment_history aph
WHERE ac.check_id = l_check_tbl(l_row_num).check_id
AND ac.void_date IS NOT NULL
AND (ac.checkrun_name IS NOT NULL OR ac.payment_id IS NOT NULL)
AND aph.check_id = ac.check_id
AND aph.transaction_type = 'PAYMENT CANCELLED'
AND aph.posted_flag = 'Y'
AND NOT EXISTS (SELECT 1
FROM fv_voided_checks fvc
WHERE fvc.check_id = ac.check_id
AND fvc.org_id = ac.org_id);
UPDATE ap_checks
SET treasury_pay_number = l_corr_treas_pay_num
WHERE check_id = l_offset_check_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y'
WHERE treasury_confirmation_id = g_treasury_conf_id;
UPDATE fv_treasury_confirmations
SET confirmation_status_flag = 'Y',
dit_cash_tran_code = 'CASH_PAY'
WHERE treasury_confirmation_id = g_treasury_conf_id;
SELECT closing_status,period_year,effective_period_num
INTO v_status,v_pyear,v_pnum
FROM gl_period_statuses gps
WHERE gps.ledger_id = g_ledger_id
AND gps.application_id = 101
AND p_accounting_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT start_date
INTO p_accounting_date
FROM gl_period_statuses gps
WHERE gps.ledger_id = g_ledger_id
AND gps.application_id = 101
AND gps.period_year >= v_pyear
AND effective_period_num > v_pnum
AND gps.closing_status = 'O'
AND gps.adjustment_period_flag = 'N'
AND ROWNUM < 2
ORDER BY period_year,period_num ASC ;
SELECT distinct ac.legal_entity_id, ftc.event_id
FROM ap_checks ac
,fv_treasury_confirmations ftc
WHERE ftc.treasury_confirmation_id = p_treasury_conf_id
AND ftc.payment_instruction_id = ac.payment_instruction_id
AND ac.org_id = g_org_id;
SELECT
FVC.event_id,
FTC.payment_instruction_id,
FVC.check_id,
FTC.treasury_confirmation_id
FROM fv_voided_checks FVC,
fv_treasury_confirmations_all FTC,
ap_checks_all ac
WHERE
ftc.org_id = g_org_id
AND FVC.org_id = ftc.org_id
AND ac.org_id = FVC.org_id
AND FTC.treasury_confirmation_id = p_treasury_conf_id
AND FTC.payment_instruction_id = ac.payment_instruction_id
AND ac.check_id = fvc.check_id
AND fvc.processed_flag = 'U'
AND FTC.confirmation_status_flag = 'Y';
SELECT accounting_date
FROM ap_invoice_payments_all
WHERE check_id = l_check_id
AND amount < 0
GROUP BY check_id, accounting_date;
SELECT payment_instruction_id
INTO
l_pmt_id
FROM fv_treasury_confirmations ftc
WHERE
ftc.treasury_confirmation_id = p_treasury_conf_id;
/*XLA_EVENTS_PUB_PKG.DELETE_EVENT(
p_event_source_info => l_event_source_info,
p_event_id => l_tc_event_id,
p_valuation_method => NULL,
p_security_context => l_security_context);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'After delete '||l_tc_event_id);
Insert into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
values (l_tc_Event_id);
Update fv_treasury_confirmations_all
Set event_id = l_tc_event_id
Where treasury_confirmation_id = p_treasury_conf_id;
XLA_EVENTS_PUB_PKG.DELETE_EVENT(
p_event_source_info => l_event_source_info,
p_event_id => l_void_event_id,
p_valuation_method => NULL,
p_security_context => l_security_context);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'After delete '||l_void_event_id);
INSERT INTO XLA_ACCT_PROG_EVENTS_GT (Event_Id)
VALUES (l_void_Event_id);
UPDATE fv_voided_checks
SET event_id = l_void_event_id,
payment_instruction_id = l_payment_instr_id
WHERE check_id = l_check_id
AND org_id = g_org_id;
SELECT max(fvtreas.TREASURY_CONFIRMATION_ID) TREASURY_CONFIRMATION_ID
FROM fv_voided_checks fvc , ap_checks_all apchk,fv_treasury_confirmations_all fvtreas
WHERE
apchk.org_id = g_org_id
AND apchk.org_id = fvtreas.org_id
AND apchk.check_id = fvc.check_id
AND apchk.payment_instruction_id = fvtreas.payment_instruction_id
AND fvc.processed_flag = 'U'
GROUP BY fvc.check_id;
SELECT fc.event_id
FROM fv_voided_checks fc
WHERE fc.processed_flag = 'U'
AND fc.org_id = g_org_id
and fc.check_id in ( select check_id
from fv_treasury_confirmations_all fvtreas ,
ap_checks_all ac
where fvtreas.org_id = g_org_id
and ac.org_id = fvtreas.org_id
and fvtreas.treasury_confirmation_id = p_treas_conf_id
and fvtreas.payment_instruction_id = ac.payment_instruction_id
and ac.void_date is not null
);
SELECT fv.event_id
FROM fv_voided_checks fv
WHERE processed_flag = 'P'
AND org_id = g_org_id
AND EXISTS (SELECT event_id FROM xla_events xe
WHERE xe.application_id = 8901
AND xe.event_type_code = 'TREASURY_VOID'
AND xe.event_id = fv.event_id
AND xe.event_status_code = 'U'
)
AND NOT EXISTS
(SELECT event_id FROM xla_ae_headers xh
WHERE xh.application_id = 8901
AND xh.event_type_code = 'TREASURY_VOID'
AND xh.event_id = fv.event_id);
SELECT max(fvtreas.TREASURY_CONFIRMATION_ID) TREASURY_CONFIRMATION_ID into p_treasury_conf_id
FROM fv_voided_checks fvc , ap_checks_all apchk,
fv_treasury_confirmations_all fvtreas
WHERE apchk.org_id = g_org_id
AND apchk.org_id = fvtreas.org_id
AND apchk.check_id = fvc.check_id
AND apchk.payment_instruction_id = fvtreas.payment_instruction_id
AND fvc.event_id = cur_event_id;
SELECT payment_instruction_id
INTO l_pmt_id
FROM fv_treasury_confirmations ftc
WHERE ftc.treasury_confirmation_id = p_treasury_conf_id;
UPDATE fv_voided_checks
SET processed_flag = 'U', event_id = NULL
WHERE processed_flag = 'P'
AND event_id = cur_event_id
AND org_id = g_org_id;
XLA_EVENTS_PUB_PKG.DELETE_EVENT(
p_event_source_info => l_event_source_info,
p_event_id => cur_event_id,
p_valuation_method => NULL,
p_security_context => l_security_context);
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||' insert fv_voided_checks2',l_err_stage);
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,
ac.checkrun_name,
ac.check_id,
'U',
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
ac.org_id
FROM ap_checks_all ac,
fv_treasury_confirmations_all fvtc
, ap_payment_history_all aph
WHERE ac.org_id = g_org_id
AND fvtc.org_id = ac.org_id
AND fvtc.payment_instruction_id = ac.payment_instruction_id
AND ac.void_date IS NOT NULL
AND (ac.checkrun_name IS NOT NULL OR ac.payment_id IS NOT NULL)
AND aph.check_id = ac.check_id
AND aph.transaction_type = 'PAYMENT CANCELLED'
AND aph.posted_flag = 'Y'
--Added for ER: 11841305
and NVL(fvtc.dit_cash_tran_code, 'ZX') <> 'CASH_PAY'
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('Inserted: '||sql%rowcount||' row(s).');
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||' insert fv_voided_checks1',l_err_stage);
SELECT COUNT(ac.check_id) INTO l_void_count
FROM ap_checks_all ac
,fv_treasury_confirmations_all ftc
, fv_voided_checks fvc
WHERE ftc.treasury_confirmation_id = treas_conf_rec.TREASURY_CONFIRMATION_ID
AND ftc.payment_instruction_id = ac.payment_instruction_id
AND ac.org_id = g_org_id
AND ac.org_id = ftc.org_id
AND ac.void_date IS NOT NULL
AND fvc.check_id = ac.check_id
AND fvc.processed_flag = 'U';
SELECT event_status_code
INTO l_event_status
FROM xla_events
WHERE event_id = event_id_rec.event_id
AND application_id = 8901;
UPDATE fv_voided_checks
SET processed_flag = l_event_status
WHERE org_id = g_org_id
AND event_id = event_id_rec.event_id;
UPDATE fv_voided_checks
SET processed_flag = 'P'
WHERE processed_flag = 'U'
AND org_id = g_org_id
and check_id in ( select check_id
from fv_treasury_confirmations_all fvtreas ,
ap_checks_all ac
where
fvtreas.org_id = g_org_id
and ac.org_id = fvtreas.org_id
and fvtreas.treasury_confirmation_id = treas_conf_rec.TREASURY_CONFIRMATION_ID
and fvtreas.payment_instruction_id = ac.payment_instruction_id
and ac.void_date is not null
);
'update fv_voided_checks1',l_err_stage);
UPDATE fv_voided_checks
SET processed_flag = 'P'
WHERE processed_flag = 'U'
AND org_id = g_org_id;
UPDATE fv_voided_checks
SET processed_flag = 'X'
WHERE processed_flag = 'U'
AND org_id = g_org_id;
fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks1',l_err_stage);