[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE fun_seq_versions
SET current_value = NVL(current_value + 1,initial_value)
WHERE seq_version_id= l_seq_version_Id
RETURNING current_value, sysdate
INTO x_sequence_number, x_sequenced_date;
update_gapless_status(
p_assignment_id => l_assignment_id,
p_seq_version_id => l_seq_version_id);
l_sql_stmt := 'SELECT '
|| 'FUN_SEQ_S' || l_seq_version_id || '.nextval '
|| 'FROM dual';
update_db_status(
p_assignment_id => l_assignment_id,
p_seq_version_id => l_seq_version_id);
UPDATE fun_seq_versions sv
SET sv.current_value = p_sequence_number
WHERE sv.seq_version_id = p_seq_version_id;
SELECT sac.seq_context_id,
sac.date_type,
sac.require_assign_flag,
sac.sort_option
INTO x_seq_context_id,
x_control_date_type,
x_req_assign_flag,
x_sort_option_code
FROM fun_seq_contexts sac
WHERE sac.application_id = p_application_id
AND sac.table_name = p_table_name
AND sac.context_type = p_context_type
AND sac.context_value = p_context_value
AND sac.event_code = p_event_code
AND sac.obsolete_flag = 'N'
FOR UPDATE;
l_debug_loc := 'update_assign_status';
update_assign_status(
p_assignment_id => l_assignment_id);
SELECT sa.assignment_id,
sa.seq_header_id,
sh.gapless_flag
INTO x_assignment_id,
x_seq_header_id,
x_sequence_type
FROM fun_seq_assignments sa, fun_seq_headers sh
WHERE sa.seq_context_id = p_seq_context_id
AND sa.seq_header_id = sh.seq_header_id
AND sh.obsolete_flag = 'N'
AND sa.link_to_assignment_id IS NULL
AND sa.start_date <= p_control_date_value
AND sa.use_status_code IN ('NEW','USED')
AND p_control_date_value <= NVL(sa.end_date, p_control_date_value + 1);
SELECT sa.assignment_id,
sa.seq_header_id,
sh.gapless_flag
INTO x_assignment_id,
x_seq_header_id,
x_sequence_type
FROM fun_seq_assignments sa, fun_seq_headers sh
WHERE sa.seq_context_id = p_seq_context_id
AND sa.seq_header_id = sh.seq_header_id (+) -- (+) Do Not Sequence
AND sh.obsolete_flag (+) = 'N'
AND sa.link_to_assignment_id IS NULL
AND sa.start_date <= p_control_date_value
AND sa.use_status_code IN ('NEW','USED')
AND p_control_date_value <= NVL(sa.end_date, p_control_date_value + 1)
AND NVL(sa.balance_type, '@NULL@') =
NVL2(sa.balance_type,
p_control_attribute_rec.balance_type, '@NULL@')
AND NVL(sa.journal_source, '@NULL@') =
NVL2(sa.journal_source,
p_control_attribute_rec.journal_source, '@NULL@')
AND NVL(sa.journal_category, '@NULL@') =
NVL2(sa.journal_category,
p_control_attribute_rec.journal_category, '@NULL@')
AND NVL(sa.document_category, '@NULL@') =
NVL2(sa.document_category,
p_control_attribute_rec.document_category, '@NULL@')
AND NVL(sa.accounting_event_type, '@NULL@') =
NVL2(sa.accounting_event_type,
p_control_attribute_rec.accounting_event_type, '@NULL@')
AND NVL(sa.accounting_entry_type, '@NULL@') =
NVL2(sa.accounting_entry_type,
p_control_attribute_rec.accounting_entry_type, '@NULL@')
FOR UPDATE;
SELECT xsa.assignment_id,
xsh.gapless_flag,
xsa.seq_header_id
FROM fun_seq_assignments xsa, fun_seq_headers xsh
WHERE xsa.link_to_assignment_id = p_assignment_id
AND xsa.start_date <= p_control_date_value
AND p_control_date_value <= NVL(xsa.end_date, p_control_date_value + 1)
AND xsa.use_status_code IN ('NEW','USED')
AND xsa.seq_header_id = xsh.seq_header_id (+) -- Do Not Sequence
AND xsh.obsolete_flag (+) = 'N'
AND (xsa.balance_type IS NULL OR
xsa.balance_type = p_control_attribute_rec.balance_type)
AND (xsa.journal_source IS NULL OR
xsa.journal_source = p_control_attribute_rec.journal_source)
AND (xsa.journal_category IS NULL OR
xsa.journal_category = p_control_attribute_rec.journal_category)
AND (xsa.document_category IS NULL OR
xsa.document_category
= p_control_attribute_rec.document_category)
AND (xsa.accounting_event_type IS NULL OR
xsa.accounting_event_type
= p_control_attribute_rec.accounting_event_type)
AND (xsa.accounting_entry_type IS NULL OR
xsa.accounting_entry_type
= p_control_attribute_rec.accounting_entry_type)
ORDER BY xsa.priority
FOR UPDATE;
SELECT name
INTO l_seq_context_name
FROM fun_seq_contexts
WHERE seq_context_id = p_seq_context_id;
SELECT header_name
INTO l_seq_header_name
FROM fun_seq_headers
WHERE seq_header_id = p_seq_header_id;
SELECT sv.seq_version_id
INTO x_seq_version_id
FROM fun_seq_versions sv
WHERE sv.seq_header_id = p_seq_header_id
AND sv.start_date <= p_control_date_value
AND p_control_date_value <= NVL(sv.end_date, p_control_date_value + 1)
AND sv.use_status_code IN ('NEW','USED')
FOR UPDATE;
SELECT sv.seq_version_id
INTO x_seq_version_id
FROM fun_seq_versions sv
WHERE sv.seq_header_id = p_seq_header_id
AND sv.start_date <= p_control_date_value
AND p_control_date_value <= NVL(sv.end_date, p_control_date_value + 1)
AND sv.use_status_code IN ('NEW','USED');
PROCEDURE update_gapless_status (
p_assignment_id IN NUMBER,
p_seq_version_id IN NUMBER) IS
BEGIN
--
-- Update the Status of Versions
--
update_seq_ver_status(p_seq_version_id => p_seq_version_id);
update_assign_status (p_assignment_id => p_assignment_id);
END update_gapless_status;
PROCEDURE update_db_status (
p_assignment_id IN NUMBER,
p_seq_version_id IN NUMBER) IS
--
-- This will cause deadlock if Version is Locked
--
PRAGMA AUTONOMOUS_TRANSACTION;
update_assign_status (p_assignment_id => p_assignment_id);
update_seq_ver_status(p_seq_version_id => p_seq_version_id);
END update_db_status;
PROCEDURE update_assign_status (
p_assignment_id IN NUMBER) IS
TYPE assign_id_tbl_type IS TABLE OF fun_seq_assignments.assignment_id%TYPE
INDEX BY BINARY_INTEGER;
SELECT assignment_id
BULK COLLECT
INTO l_assign_id_tbl
FROM fun_seq_assignments
WHERE use_status_code = 'NEW'
AND ((assignment_id = p_assignment_id) OR
(assignment_id = (SELECT ex.link_to_assignment_id
FROM fun_seq_assignments ex
WHERE ex.assignment_id = p_assignment_id)));
UPDATE fun_seq_assignments
SET use_status_code = 'USED'
WHERE assignment_id = l_assign_id_tbl(i);
END update_assign_status;
PROCEDURE update_seq_ver_status (
p_seq_version_id IN NUMBER) IS
l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
SELECT seq_version_id
INTO l_seq_version_id
FROM fun_seq_versions
WHERE seq_version_id = p_seq_version_id
AND use_status_code = 'NEW';
UPDATE fun_seq_versions
SET use_status_code = 'USED'
WHERE seq_version_id = l_seq_version_id;
END update_seq_ver_status;
SELECT sac.seq_context_id,
sac.date_type,
sac.require_assign_flag,
sac.sort_option
INTO l_context_ctrl_rec.seq_context_id,
l_context_ctrl_rec.date_type,
l_context_ctrl_rec.req_assign_flag,
l_context_ctrl_rec.sort_option_code
FROM fun_seq_contexts sac
WHERE sac.application_id = p_context_info_rec.application_id
AND sac.table_name = p_context_info_rec.table_name
AND sac.context_type = p_context_info_rec.context_type
AND sac.context_value = p_context_info_rec.context_value
AND sac.event_code = p_context_info_rec.event_code
AND sac.obsolete_flag = 'N';
SELECT sa.assignment_id,
sa.seq_header_id,
sh.gapless_flag
INTO l_assign_seq_head_rec.assignment_id,
l_assign_seq_head_rec.seq_header_id,
l_assign_seq_head_rec.seq_type
FROM fun_seq_assignments sa, fun_seq_headers sh
WHERE sa.seq_context_id = l_rec.seq_context_id
AND sa.seq_header_id = sh.seq_header_id (+) -- Do Not Sequence
AND sh.obsolete_flag (+) = 'N'
AND sa.link_to_assignment_id IS NULL
AND sa.start_date <= l_rec.control_date
AND sa.use_status_code IN ('NEW','USED')
AND l_rec.control_date <= NVL(sa.end_date, l_rec.control_date + 1)
AND NVL(sa.balance_type, '@NULL@') =
NVL2(sa.balance_type,
l_rec.ctrl_attr_rec.balance_type, '@NULL@')
AND NVL(sa.journal_source, '@NULL@') =
NVL2(sa.journal_source,
l_rec.ctrl_attr_rec.journal_source, '@NULL@')
AND NVL(sa.journal_category, '@NULL@') =
NVL2(sa.journal_category,
l_rec.ctrl_attr_rec.journal_category, '@NULL@')
AND NVL(sa.document_category, '@NULL@') =
NVL2(sa.document_category,
l_rec.ctrl_attr_rec.document_category, '@NULL@')
AND NVL(sa.accounting_event_type, '@NULL@') =
NVL2(sa.accounting_event_type,
l_rec.ctrl_attr_rec.accounting_event_type, '@NULL@')
AND NVL(sa.accounting_entry_type, '@NULL@') =
NVL2(sa.accounting_entry_type,
l_rec.ctrl_attr_rec.accounting_entry_type, '@NULL@');
SELECT xsa.assignment_id,
xsh.gapless_flag,
xsa.seq_header_id
FROM fun_seq_assignments xsa, fun_seq_headers xsh
WHERE xsa.link_to_assignment_id = l_rec_in.assignment_id
AND xsa.start_date <= l_rec_in.control_date
AND l_rec_in.control_date <= NVL(xsa.end_date, l_rec_in.control_date + 1)
AND xsa.use_status_code IN ('NEW','USED')
AND xsa.seq_header_id = xsh.seq_header_id (+) -- Do not Sequence
AND xsh.obsolete_flag (+) = 'N'
AND (xsa.balance_type IS NULL OR
xsa.balance_type = l_rec_in.ctrl_attr_rec.balance_type)
AND (xsa.journal_source IS NULL OR
xsa.journal_source = l_rec_in.ctrl_attr_rec.journal_source)
AND (xsa.journal_category IS NULL OR
xsa.journal_category = l_rec_in.ctrl_attr_rec.journal_category)
AND (xsa.document_category IS NULL OR
xsa.document_category
= l_rec_in.ctrl_attr_rec.document_category)
AND (xsa.accounting_event_type IS NULL OR
xsa.accounting_event_type
= l_rec_in.ctrl_attr_rec.accounting_event_type)
AND (xsa.accounting_entry_type IS NULL OR
xsa.accounting_entry_type
= l_rec_in.ctrl_attr_rec.accounting_entry_type)
ORDER BY xsa.priority;