[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select concatenated_segments
into l_seg_rfe
from gl_code_combinations_kfv
where code_combination_id = 1002;
select concatenated_segments
into l_seg_ccid
from gl_code_combinations_kfv
where code_combination_id = p_ccid; --24350;
select code_combination_id
into rfe_ccid
from gl_code_combinations_kfv
where segment1 = l_segarray_rfe(1)
and segment2 = l_segarray_rfe(2)
and segment3 = l_segarray_rfe(3)
and segment4 = l_segarray_rfe(4)
and segment5 = l_segarray_rfe(5)
and segment6 = l_segarray_rfe(6)
and segment7 = l_segarray_rfe(7);
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_ID = '||p_entity_rec.source_id_int_1;
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID = '||p_entity_rec.source_id_int_1;
SELECT * FROM GMS_AWARD_DISTRIBUTIONS WHERE INVOICE_DISTRIBUTION_ID IN (
SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID = '||p_entity_rec.source_id_int_1||
')';
SELECT * FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id;
SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
')';
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID IN (
SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
'))';
SELECT * FROM XLA_DISTRIBUTION_LINKS WHERE AE_HEADER_ID IN (
SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
'))';
SELECT * FROM XLA_TRIAL_BALANCES WHERE AE_HEADER_ID IN (
SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
'))';
INSERT INTO psa_xla_undo_acct_audit
(
audit_id,
user_id,
resp_id,
login_id,
request_id,
start_date,
end_date,
application_id,
entity_code,
source_id_int_1,
source_id_int_2,
source_id_int_3,
source_id_int_4,
source_id_char_1,
source_id_char_2,
source_id_char_3,
source_id_char_4,
document_num,
gl_date,
program_status,
program_mesg,
tar_number,
bug_number,
program_mode
)
VALUES
(
psa_xla_undo_acct_audit_s.nextval,
g_user_id,
g_resp_id,
g_login_id,
g_request_id,
SYSDATE,
NULL,
p_entity_rec.application_id,
p_entity_rec.entity_code,
p_entity_rec.source_id_int_1,
p_entity_rec.source_id_int_2,
p_entity_rec.source_id_int_3,
p_entity_rec.source_id_int_4,
p_entity_rec.source_id_char_1,
p_entity_rec.source_id_char_2,
p_entity_rec.source_id_char_3,
p_entity_rec.source_id_char_4,
p_entity_rec.transaction_number,
p_gl_date,
NULL,
NULL,
p_tar_number,
p_bug_number,
p_mode
) RETURNING audit_id INTO p_audit_id;
UPDATE psa_xla_undo_acct_audit
SET end_date = SYSDATE,
program_status = p_program_status,
program_mesg = p_program_mesg
WHERE audit_id = p_audit_id;
SELECT *
INTO l_entity_rec
FROM xla_transaction_entities
WHERE entity_id = p_events_tab(1).entity_id;
error('System Error when selecting entity:'||SQLERRM);
DELETE from psa_bc_xla_events_gt;
debug_other_string(g_state_level,l_path_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
DELETE from xla_acct_prog_events_gt;
debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
DELETE from xla_ae_headers_gt;
debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
DELETE from xla_ae_lines_gt;
debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
DELETE from xla_validation_lines_gt;
debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
INSERT INTO psa_bc_xla_events_gt
(
event_id,
result_code
)
VALUES
(
p_events_tab(i).event_id,
'XLA_UNPROCESSED'
);
SELECT *
INTO l_entity_rec
FROM xla_transaction_entities t
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND NVL(source_id_int_1, -1) = NVL(p_source_id_int_1, -1)
AND NVL(source_id_int_2, -1) = NVL(p_source_id_int_2, -1)
AND NVL(source_id_int_3, -1) = NVL(p_source_id_int_3, -1)
AND NVL(source_id_int_4, -1) = NVL(p_source_id_int_4, -1)
AND NVL(source_id_char_1, ' ') = NVL(p_source_id_char_1, ' ')
AND NVL(source_id_char_2, ' ') = NVL(p_source_id_char_2, ' ')
AND NVL(source_id_char_3, ' ') = NVL(p_source_id_char_3, ' ')
AND NVL(source_id_char_4, ' ') = NVL(p_source_id_char_4, ' ')
AND EXISTS (SELECT 1
FROM xla_events e
WHERE t.entity_id = e.entity_id);
error('System Error when selecting entity:'||SQLERRM);
/* Gather all the events that are to be deleted */
IF (p_retcode = g_SUCCESS) THEN
log(l_path_name, 'Gathering Events that are to be processed');
FOR events_rec IN (SELECT e.event_id,
e.budgetary_control_flag,
e.event_status_code,
nvl(h.gl_transfer_status_code,'N') gl_xfer_flag,
h.accounting_date,
h.ae_header_id,
e.event_type_code,
ent.legal_entity_id,
ent.ledger_id,
ent.transaction_number
FROM xla_events e,
xla_ae_headers h,
xla_transaction_entities ent
WHERE ent.source_id_int_1 = p_source_id_int_1
and ent.application_id = p_application_id
AND ent.entity_code = p_entity_code
and e.entity_id = ent.entity_id
and e.budgetary_control_flag = decode(p_mode , 'B' , 'Y', e.budgetary_control_flag)
AND e.event_id = h.event_id(+)
ORDER BY e.event_id )
LOOP
log(l_path_name, 'Event id = '||events_rec.event_id);
SELECT p.closing_status
INTO l_gl_period_status
FROM gl_period_statuses p
WHERE p.application_id = 101
AND p.set_of_books_id = l_entity_rec.ledger_id
AND p.adjustment_period_flag = 'N'
AND events_rec.accounting_date BETWEEN p.start_date AND p.end_date;
error('Error While selecting GL Period Information For Accouting Date: '||SQLERRM);
log(l_path_name, 'Calling xla_events_pub_pkg.delete_event ');
xla_events_pub_pkg.delete_event
(
p_event_source_info => l_event_source_info,
p_event_id => p_events_tab(l_counter).event_id,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context
);
DELETE xla_events
WHERE event_id = p_events_tab(l_counter).event_id;
log(l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows from xla_events');
log(l_path_name, 'Calling xla_datafixes_pub.delete_journal_entries');
xla_datafixes_pub.delete_journal_entries
(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_application_id => p_application_id,
p_event_id => p_events_tab(l_counter).event_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DELETE gl_bc_packets
WHERE event_id = p_events_tab(l_counter).event_id;
log(l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows from GL BC Packets');
DELETE xla_events
WHERE event_id = p_events_tab(l_counter).event_id;
log(l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows from xla_events');
UPDATE xla_events
SET event_date = p_events_tab(l_counter).gl_date
WHERE event_id = p_events_tab(l_counter).event_id;
log(l_path_name, 'Updated '||SQL%ROWCOUNT||' rows in xla_events');
UPDATE xla_events
SET event_date = p_events_tab(l_counter).gl_date
WHERE event_id = p_events_tab(l_counter).event_id;
log(l_path_name, 'Updated '||SQL%ROWCOUNT||' rows in xla_events');
select count(*) into l_event_check
from xla_events e,
xla_transaction_entities ent
WHERE ent.source_id_int_1 = p_invoice_id
and ent.application_id = l_application_id
AND ent.entity_code = l_entity_code
and e.entity_id = ent.entity_id
and e.budgetary_control_flag = decode(p_mode , 'B', 'Y' , e.budgetary_control_flag);
select count(*) into l_event_check
from xla_events e,
xla_transaction_entities ent,
xla_ae_headers h
WHERE ent.source_id_int_1 = p_invoice_id
and ent.application_id = l_application_id
AND ent.entity_code = l_entity_code
and e.entity_id = ent.entity_id
and h.application_id = l_application_id
and h.event_id = e.event_id
and e.budgetary_control_flag = decode(p_mode , 'B', 'Y' , e.budgetary_control_flag)
and e.event_status_code in ('U', 'I')
and e.process_status_code <> 'P';
UPDATE ap_invoice_distributions_all aid
SET period_name = (SELECT DISTINCT gps.period_name
FROM gl_period_statuses gps
WHERE gps.application_id = 200
AND gps.set_of_books_id = l_events_tab(l_counter).ledger_id
AND nvl(gps.adjustment_period_flag, 'N') = 'N'
AND l_events_tab(l_counter).gl_date BETWEEN TRUNC(gps.start_date) AND TRUNC(gps.end_date))
WHERE invoice_id = p_invoice_id
AND accounting_event_id = l_events_tab(l_counter).event_id;
DELETE ap_holds_all
WHERE invoice_id = p_invoice_id
AND hold_lookup_code = 'CANT FUNDS CHECK'
AND release_lookup_code IS NULL;
debug_other_string(g_state_level,l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows.' );
UPDATE ap_invoice_distributions_all
SET encumbered_flag = 'N',
match_status_flag = 'N',
bc_event_id = NULL,
posted_flag = 'N'
WHERE invoice_id = p_invoice_id
AND NVL(encumbered_flag,'X') <> 'R';
debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
update gms_award_distributions
set fc_status = 'N'
where invoice_distribution_id in (select invoice_distribution_id
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and award_id is not null)
and fc_status ='A';
debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
UPDATE ap_self_assessed_tax_dist_all sad
SET bc_event_id = null,
accounting_event_id = decode(p_mode , 'A' , NULL,accounting_event_id)
WHERE sad.invoice_id = p_invoice_id
AND sad.invoice_distribution_id in (select invoice_distribution_id
from ap_invoice_distributions_all
where invoice_id = p_invoice_id);
debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
UPDATE ap_invoice_distributions_all
SET accounting_event_id = decode(p_mode , 'A' , NULL, accounting_event_id)
WHERE invoice_id = p_invoice_id;
debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
UPDATE ap_prepay_history_all h
SET h.bc_event_id = NULL,
h.accounting_event_id = decode(p_mode , 'A' , NULL,h.accounting_event_id)
WHERE h.invoice_id = p_invoice_id;
debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
UPDATE ap_prepay_app_dists d
SET d.bc_event_id = NULL,
d.accounting_event_id = decode(p_mode , 'A' , NULL,d.accounting_event_id)
WHERE d.invoice_distribution_id IN (SELECT invoice_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id);
debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
UPDATE ap_invoice_distributions_all aid
SET accrual_posted_flag = 'N',
cash_posted_flag = 'N',
posted_flag = 'N',
accounting_date = l_events_tab(l_counter).gl_date
WHERE invoice_id = p_invoice_id;
UPDATE ap_prepay_history_all
SET posted_flag = 'N'
WHERE invoice_id = p_invoice_id;
SELECT fnd_log_messages_s.nextval
INTO l_start_log_id
FROM DUAL;
SELECT fnd_log_messages_s.nextval
INTO l_end_log_id
FROM DUAL;
SELECT fnd_log_messages_s.nextval
INTO l_start_log_id
FROM DUAL;
SELECT fnd_log_messages_s.nextval
INTO l_end_log_id
FROM DUAL;
error ('select * from fnd_log_messages ');
SELECT fnd_log_messages_s.nextval
INTO l_start_log_id
FROM DUAL;
FOR xla_rec IN (SELECT e.event_id,
e.entity_id
FROM xla_transaction_entities t,
xla_events e
WHERE t.application_id = l_application_id
AND t.entity_code = 'PURCHASE_ORDER'
AND t.source_id_int_1 = p_po_header_id
AND t.entity_id = e.entity_id
AND e.event_status_code = 'U') LOOP
l_count := l_count + 1;
SELECT fnd_log_messages_s.nextval
INTO l_end_log_id
FROM DUAL;
error ('select * from fnd_log_messages ');
SELECT user_id,
start_date,
end_date
INTO p_user_id,
l_start_date,
l_end_date
FROM fnd_user
WHERE user_name = UPPER(p_user_name);
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM fnd_user
WHERE user_id = p_user_id;
SELECT application_name
INTO l_appl_name
FROM fnd_application_vl
WHERE application_id = p_appl_id;
SELECT b.start_date,
b.end_date
INTO l_start_date,
l_end_date
FROM fnd_responsibility b
WHERE b.responsibility_id = p_resp_id;
SELECT a.start_date,
a.end_date,
a.responsibility_application_id
INTO l_start_date,
l_end_date,
l_resp_appl_id
FROM fnd_user_resp_groups_direct a
WHERE a.responsibility_id = p_resp_id
AND a.user_id = p_user_id;
SELECT org_id,
invoice_num
INTO p_org_id,
p_invoice_num
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
undo_error ('Selecting ap_invoices: '||SQLERRM);
SELECT org_id,
segment1
INTO p_org_id,
p_po_num
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
undo_error ('Selecting ap_invoices: '||SQLERRM);
SELECT p.closing_status,
p.period_name
INTO l_gl_period_status,
l_gl_period_name
FROM gl_period_statuses p
WHERE p.application_id = 101
AND p.set_of_books_id = p_ledger_id
AND p_gl_date BETWEEN p.start_date AND p.end_date
AND p.adjustment_period_flag = 'N';
undo_error ('Selecting GL Period Information: '||SQLERRM);