[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login CONSTANT NUMBER := 0;
l_debug_loc := l_debug_loc || '->' || 'insert into fun_seq_entities';
INSERT INTO fun_seq_entities (
application_id,
table_name,
entity_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
p_application_id,
p_table_name,
p_entity_name,
l_user_id,
sysdate,
l_user_id,
sysdate,
g_last_update_login);
l_debug_loc := l_debug_loc || '->' || 'insert into fun_seq_rules';
INSERT INTO fun_seq_rules (
application_id,
table_name,
context_type,
event_code,
date_type,
flex_context_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
p_application_id,
p_table_name,
p_context_type,
p_event_code,
p_date_type,
p_flex_context_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
g_last_update_login);
PROCEDURE delete_entity (
p_application_id IN NUMBER,
p_table_name IN VARCHAR2) IS
l_debug_loc Debug_Loc;
l_debug_loc := 'delete_entity';
DELETE
FROM fun_seq_rules sr
WHERE sr.application_id = p_application_id
AND sr.table_name = p_table_name;
DELETE
FROM fun_seq_entities se
WHERE se.application_id = p_application_id
AND se.table_name = p_table_name;
PROCEDURE delete_sequencing_rule (
p_application_id IN NUMBER,
p_table_name IN VARCHAR2,
p_context_type IN VARCHAR2,
p_event_code IN VARCHAR2,
p_date_type IN VARCHAR2) IS
l_debug_loc Debug_Loc;
l_debug_loc := 'delete_sequencing_rule';
DELETE
FROM fun_seq_rules sr
WHERE sr.application_id = p_application_id
AND sr.table_name = p_table_name
AND sr.context_type = p_context_type
AND sr.event_code = p_event_code
AND sr.date_type = p_date_type;
END delete_sequencing_rule;
PROCEDURE update_entity (
p_application_id IN NUMBER,
p_table_name IN VARCHAR2,
p_entity_name IN VARCHAR2) IS
l_user_id NUMBER DEFAULT 1;
l_debug_loc := 'update_entity';
UPDATE fun_seq_entities se
SET se.entity_name = p_entity_name,
se.last_updated_by = l_user_id,
se.last_update_date = sysdate,
se.last_update_login = l_user_id
WHERE se.application_id = p_application_id
AND se.table_name = p_table_name;
END update_entity;
SELECT 'x'
INTO l_dummy
FROM fnd_lookups fl
WHERE fl.lookup_type = p_lookup_type
AND fl.lookup_code = p_lookup_code;
SELECT
'x'
FROM
fnd_tables ft
WHERE
ft.application_id = p_table_name_rec.application_id
AND ft.table_name = p_table_name_rec.table_name;
SELECT 'x'
INTO l_dummy
FROM fun_seq_entities se
WHERE se.application_id = p_application_id
AND se.table_name = p_table_name;
p_last_update_date IN VARCHAR2,
p_custom_mode IN VARCHAR2) IS
l_seq_header_id fun_seq_headers.seq_header_id%TYPE;
p_last_update_date => p_last_update_date,
p_custom_mode => p_custom_mode,
x_seq_header_id => l_seq_header_id);
p_last_update_date => p_last_update_date,
p_custom_mode => p_custom_mode);
p_last_update_date => p_last_update_date,
p_custom_mode => p_custom_mode,
x_seq_context_id => l_seq_context_id);
p_last_update_date => p_last_update_date,
p_custom_mode => p_custom_mode);
p_last_update_date IN VARCHAR2,
p_custom_mode IN VARCHAR2,
x_seq_header_id OUT NOCOPY NUMBER) IS
l_seq_header_id NUMBER;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
sysdate);
SELECT seq_header_id,
last_updated_by,
last_update_date
INTO x_seq_header_id,
db_luby,
db_ludate
FROM fun_seq_headers
WHERE header_name = p_sequence_rec.header_name;
UPDATE fun_seq_headers
SET description = p_sequence_rec.description,
last_updated_by = f_luby,
last_update_date = f_ludate,
last_update_login = 0
WHERE seq_header_id = x_seq_header_id;
INSERT
INTO fun_seq_headers (
seq_header_id,
header_name,
gapless_flag,
description,
obsolete_flag,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES (
fun_seq_headers_s.NEXTVAL, -- Sequence Header Id
p_sequence_rec.header_name, -- Name
p_sequence_rec.gapless, -- Type (Gapless)
p_sequence_rec.description, -- Description
'N', -- Obsolete Flag
1, -- Object Version Number
f_ludate, -- Last Update Date
f_luby, -- Last Updated By
f_ludate, -- Creation Date
f_luby, -- Created By
0) -- Last Update Login
RETURNING seq_header_id INTO x_seq_header_id;
p_last_update_date IN VARCHAR2,
p_custom_mode IN VARCHAR2) IS
l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
sysdate);
SELECT last_updated_by,
last_update_date
INTO db_luby,
db_ludate
FROM fun_seq_versions
WHERE seq_header_id = p_seq_header_id
AND version_name = p_version_rec.version_name;
INSERT
INTO fun_seq_versions (
seq_version_id,
seq_header_id,
version_name,
header_name,
initial_value,
start_date,
end_date,
current_value,
use_status_code,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES(
fun_seq_versions_s.NEXTVAL, -- Sequence Version Id
p_seq_header_id,
p_version_rec.version_name,
p_header_name,
p_version_rec.initial_value,
TRUNC(p_version_rec.start_date),
p_version_rec.end_date,
p_version_rec.current_value,
'NEW',
1, -- Object Version Number
f_ludate, -- Last Update Date
f_luby, -- Last Updated By
f_ludate, -- Creation Date
f_luby, -- Created By
0)
RETURNING seq_version_id INTO l_seq_version_id;
p_last_update_date => fnd_date.date_to_canonical(SYSDATE),
p_custom_mode => 'FORCE');
SELECT MIN(ou.oracle_username) -- In case for multiple installation
INTO l_fnd_user
FROM fnd_product_installations pi,
fnd_oracle_userid ou
WHERE ou.oracle_id = pi.oracle_id
AND application_id = 0;
UPDATE fun_seq_versions
SET db_sequence_name = l_db_sequence_name
WHERE seq_version_id = p_seq_version_id;
p_last_update_date IN VARCHAR2,
p_custom_mode IN VARCHAR2,
x_seq_context_id OUT NOCOPY NUMBER) IS
l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
sysdate);
SELECT seq_context_id,
last_updated_by,
last_update_date
INTO
x_seq_context_id,
db_luby,
db_ludate
FROM fun_seq_contexts
WHERE application_id = p_context_rec.application_id
AND table_name = p_context_rec.table_name
AND context_type = p_context_rec.context_type
AND context_value = p_context_rec.context_value
AND event_code = p_context_rec.event_code
AND inactive_date IS NULL;
UPDATE fun_seq_contexts
SET name = p_context_rec.NAME
WHERE seq_context_id = x_seq_context_id
AND inactive_date IS NULL;
INSERT
INTO fun_seq_contexts (
seq_context_id,
application_id,
table_name,
context_type,
context_value,
event_code,
date_type,
NAME,
require_assign_flag,
obsolete_flag,
inactive_date,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES (
fun_seq_contexts_s.NEXTVAL,
p_context_rec.application_id,
p_context_rec.table_name,
p_context_rec.context_type,
p_context_rec.context_value,
p_context_rec.event_code,
p_context_rec.date_type,
p_context_rec.name,
p_context_rec.require_assign_flag,
'N', -- Obsolete Flag
NULL, -- Inactive Date
1,
f_ludate, -- Last Update Date
f_luby, -- Last Updated By
f_ludate, -- Creation Date
f_luby, -- Created By
0)
RETURNING seq_context_id INTO x_seq_context_id;
p_last_update_date IN VARCHAR2,
p_custom_mode IN VARCHAR2) IS
l_assignment_id fun_seq_assignments.assignment_id%TYPE;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
sysdate);
SELECT assignment_id,
last_updated_by,
last_update_date
INTO
l_assignment_id,
db_luby,
db_ludate
FROM fun_seq_assignments
WHERE seq_context_id = p_seq_context_id
AND use_status_code <> 'OBSOLETE';
INSERT
INTO fun_seq_assignments (
assignment_id,
seq_context_id,
seq_header_id,
link_to_assignment_id,
priority,
control_attribute_structure,
balance_type,
journal_source,
journal_category,
accounting_event_type,
accounting_entry_type,
document_category,
start_date,
end_date,
use_status_code,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES (
fun_seq_assignments_s.NEXTVAL,
p_seq_context_id,
p_seq_header_id,
NULL, -- Link to Assignment Id,
NULL, -- Priority
p_assignment_rec.control_attribute_structure,
NULL, -- Balance Type
NULL, -- Jounral Source
NULL, -- Journal Category
NULL, -- Accounting Event Type
NULL, -- Accounting Entry Type
NULL, -- Document Category
TRUNC(p_assignment_rec.start_date),
p_assignment_rec.end_date,
'NEW',
1,
f_ludate, -- Last Update Date
f_luby, -- Last Updated By
f_ludate, -- Creation Date
f_luby, -- Created By
0);
PROCEDURE delete_sequence (
p_header_name IN VARCHAR2) IS
TYPE seq_version_id_tbl_type IS TABLE OF
fun_seq_versions.seq_version_id%TYPE INDEX BY BINARY_INTEGER;
SELECT sv.seq_version_id
BULK COLLECT
INTO l_seq_ver_id_tbl
FROM fun_seq_versions sv
WHERE sv.header_name = p_header_name
AND sv.use_status_code = 'NEW'
FOR UPDATE NOWAIT;
DELETE
FROM fun_seq_versions sv
WHERE sv.seq_version_id = l_seq_ver_id_tbl(i);
SELECT sh.seq_header_id
INTO l_seq_header_id
FROM fun_seq_headers sh
WHERE sh.header_name = p_header_name
AND NOT EXISTS (SELECT 1
FROM fun_seq_versions sv
WHERE sv.seq_header_id = sh.seq_header_id
AND sv.use_status_code <> 'NEW')
FOR UPDATE NOWAIT;
DELETE
FROM fun_seq_headers sh
WHERE sh.seq_header_id = l_seq_header_id;
END delete_sequence;
PROCEDURE delete_context (
p_context_name IN VARCHAR2) IS
TYPE assign_id_tbl_type IS TABLE OF
fun_seq_assignments.assignment_id%TYPE INDEX BY BINARY_INTEGER;
SELECT sa.assignment_id
BULK COLLECT
INTO l_assign_id_tbl
FROM fun_seq_assignments sa,
fun_seq_contexts sc
WHERE sc.seq_context_id = sa.seq_context_id
AND sc.name = p_context_name
AND sa.use_status_code = 'NEW'
FOR UPDATE NOWAIT;
DELETE
FROM fun_seq_assignments sa
WHERE sa.assignment_id = l_assign_id_tbl(i);
SELECT sc.seq_context_id
INTO l_seq_context_id
FROM fun_seq_contexts sc
WHERE sc.name = p_context_name
AND NOT EXISTS (SELECT 1
FROM fun_seq_assignments sa
WHERE sa.seq_context_id = sc.seq_context_id
AND sa.use_status_code <> 'NEW')
FOR UPDATE NOWAIT;
DELETE
FROM fun_seq_contexts sc
WHERE sc.seq_context_id = l_seq_context_id;
END delete_context;
SELECT sv.seq_version_id
INTO l_seq_version_id
FROM fun_seq_versions sv
WHERE sv.seq_version_id = p_seq_version_id
FOR UPDATE NOWAIT;
UPDATE fun_seq_versions sv
SET use_status_code = 'OBSOLETE'
WHERE sv.seq_version_id = l_seq_version_id;
SELECT sv.seq_header_id,
sv.seq_version_id,
sv.version_name,
sv.initial_value,
sv.current_value,
sv.start_date,
sv.end_date
INTO x_seq_header_id,
x_seq_version_id,
x_version_rec.version_name,
x_version_rec.initial_value,
x_version_rec.current_value,
x_version_rec.start_date,
x_version_rec.end_date
FROM fun_seq_versions sv
WHERE sv.header_name = p_header_name
AND sv.use_status_code IN ('USED','NEW');
SELECT max(TO_NUMBER(tb.batch_number))
INTO l_max_number
FROM fun_trx_batches tb
WHERE TRANSLATE(tb.batch_number,'0123456789','0000000000')
= RPAD('0',LENGTH(tb.batch_number),'0');