The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_last_update_date IN VARCHAR2
,p_owner IN VARCHAR2
,p_custom_mode IN VARCHAR2) IS
CURSOR c_def IS
SELECT definition_code
,object_version_number
,last_updated_by
,last_update_date
FROM xla_tb_definitions_b
WHERE definition_code = p_definition_code;
SELECT ledger_id
FROM gl_ledgers
WHERE short_name = p_ledger_short_name;
l_last_updated_by NUMBER; -- owner in file
l_last_update_date DATE; -- last update date in file
l_db_last_updated_by NUMBER; -- owner in db
l_db_last_update_date DATE; -- last update date in db
l_last_updated_by := fnd_load_util.owner_id(p_owner);
l_last_update_date := NVL(TO_DATE(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
,l_db_last_updated_by
,l_db_last_update_date;
Insert_Row (
p_rowid => l_rowid
,p_definition_code => p_definition_code
,p_object_version_number => l_db_object_version_number
,p_ledger_id => l_ledger_id
,p_enabled_flag => p_enabled_flag
,p_balance_side_code => p_balance_side_code
,p_defined_by_code => p_defined_by_code
,p_definition_status_code => p_definition_status_code
,p_name => p_name
,p_description => p_description
,p_defn_owner_code => p_defn_owner_code
,p_creation_Date => l_last_update_date
,p_Created_By => l_last_updated_by
,p_Last_Update_Date => l_last_update_date
,p_Last_Updated_By => l_last_updated_by
,p_Last_Update_Login => 0);
p_file_id => l_last_updated_by
,p_file_lud => l_last_update_date
,p_db_id => l_db_last_updated_by
,p_db_lud => l_db_last_update_date
,p_custom_mode => p_custom_mode))
THEN
Update_Row (
p_definition_code => p_definition_code
,p_object_version_number => l_db_object_version_number
,p_ledger_id => l_ledger_id
,p_enabled_flag => p_enabled_flag
,p_balance_side_code => p_balance_side_code
,p_defined_by_code => p_defined_by_code
,p_definition_status_code => p_definition_status_code
,p_name => p_name
,p_description => p_description
,p_defn_owner_code => p_defn_owner_code
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_last_update_Login => 0);
PROCEDURE Insert_Row
(p_rowid IN OUT NOCOPY VARCHAR2
,p_definition_code IN VARCHAR2
,p_object_version_number IN NUMBER
,p_ledger_id IN NUMBER
,p_enabled_flag IN VARCHAR2
,p_balance_side_code IN VARCHAR2
,p_defined_by_code IN VARCHAR2
,p_definition_status_code IN VARCHAR2
,p_name IN VARCHAR2
,p_description IN VARCHAR2
,p_defn_owner_code IN VARCHAR2
,p_creation_date IN DATE
,p_created_by IN NUMBER
,p_last_update_date IN DATE
,p_last_updated_by IN NUMBER
,p_last_update_login IN NUMBER) IS
CURSOR c_tb_b IS
SELECT rowid
FROM xla_tb_definitions_b
WHERE definition_code = p_definition_code;
INSERT INTO xla_tb_definitions_b
(
definition_code
,object_version_number
,ledger_id
,enabled_flag
,defined_by_code
,balance_side_code
,definition_status_code
,owner_code
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_definition_code
,1 -- Ignore p_object_version_number
,p_ledger_id
,p_enabled_flag
,p_defined_by_code
,p_balance_side_code
,p_definition_status_code
,p_defn_owner_code
,p_created_by
,p_creation_date
,p_last_updated_by
,p_last_update_date
,p_last_update_login
);
INSERT INTO xla_tb_definitions_tl
(
definition_code
,name
,description
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,language
,source_lang
)
SELECT
p_definition_code
,p_name
,p_description
,p_created_by
,p_creation_date
,p_last_updated_by
,p_last_update_date
,p_last_update_login
,l.language_code
,userenv('LANG')
FROM fnd_languages l
WHERE l.installed_flag in ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM xla_tb_definitions_tl t
WHERE t.definition_code = p_definition_code
AND t.language = l.language_code);
,'LOCATION' , C_PACKAGE_NAME || '.' || 'insert_row'
,'ERROR' , sqlerrm);
END Insert_Row;
PROCEDURE Update_Row
(p_definition_code IN VARCHAR2
,p_object_version_number IN OUT NOCOPY NUMBER
,p_ledger_id IN NUMBER
,p_enabled_flag IN VARCHAR2
,p_balance_side_code IN VARCHAR2
,p_defined_by_code IN VARCHAR2
,p_definition_status_code IN VARCHAR2
,p_name IN VARCHAR2
,p_description IN VARCHAR2
,p_defn_owner_code IN VARCHAR2
,p_last_update_date IN VARCHAR2
,p_last_updated_by IN VARCHAR2
,p_last_update_login IN VARCHAR2) IS
l_object_version_number NUMBER;
SELECT object_version_number
INTO l_object_version_number
FROM xla_tb_definitions_b
WHERE definition_code = p_definition_code;
SELECT object_version_number
INTO l_object_version_number
FROM xla_tb_definitions_b
WHERE definition_code = p_definition_code
FOR UPDATE;
fnd_message.set_name('XLA','XLA_COMMON_ROW_UPDATED');
UPDATE xla_tb_definitions_b
SET object_version_number = l_object_version_number
,ledger_id = p_ledger_id
,enabled_flag = p_enabled_flag
,balance_side_code = p_balance_side_code
,defined_by_code = p_defined_by_code
,definition_status_code = p_definition_status_code
,owner_code = p_defn_owner_code
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
WHERE definition_code = p_definition_code;
UPDATE xla_tb_definitions_tl
SET name = p_name
,description = p_description
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
,source_lang = userenv('LANG')
WHERE definition_code = p_definition_code
AND userenv('LANG') IN (language, source_lang);
,'LOCATION' , C_PACKAGE_NAME || '.' || 'update_row'
,'ERROR' , sqlerrm);
END Update_Row;
PROCEDURE Delete_Row
(p_definition_code IN VARCHAR2) IS
BEGIN
DELETE FROM xla_tb_defn_details
WHERE definition_code = p_definition_code;
DELETE FROM xla_tb_definitions_tl
WHERE definition_code = p_definition_code;
DELETE FROM xla_tb_definitions_b
WHERE definition_code = p_definition_code;
,'LOCATION' , C_PACKAGE_NAME || '.' || 'delete_row'
,'ERROR' , sqlerrm);
END Delete_Row;
DELETE FROM xla_tb_definitions_tl t
WHERE NOT EXISTS
(SELECT NULL
FROM xla_tb_definitions_b b
WHERE b.definition_code = t.definition_code
);
UPDATE xla_tb_definitions_tl t
SET (
NAME
,description
) =
(
SELECT b.NAME
,b.description
FROM xla_tb_definitions_tl b
WHERE b.definition_code = t.definition_code
AND b.language = t.source_lang)
WHERE (
t.definition_code
,t.language
) IN (SELECT subt.definition_code
,subt.language
FROM xla_tb_definitions_tl subb
,xla_tb_definitions_tl subt
WHERE subb.definition_code = subt.definition_code
AND subb.language = subt.source_lang
AND (subb.NAME <> subt.NAME
OR subb.description <> subt.description
OR (subb.description IS NULL AND subt.description IS NOT NULL)
OR (subb.description IS NOT NULL AND subt.description IS NULL)
)
);
INSERT INTO xla_tb_definitions_tl
(
definition_code
,name
,description
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,language
,source_lang
)
SELECT /*+ ORDERED */
b.definition_code
,b.name
,b.description
,b.creation_date
,b.created_by
,b.last_update_date
,b.last_updated_by
,b.last_update_login
,l.language_code
,b.source_lang
FROM xla_tb_definitions_tl b, fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND b.language = userenv('LANG')
AND NOT EXISTS
(SELECT NULL
FROM xla_tb_definitions_tl t
WHERE t.definition_code = b.definition_code
AND t.language = l.language_code);
,p_last_update_date IN NUMBER
,p_owner IN VARCHAR2
,p_custom_mode IN VARCHAR2) IS
CURSOR c_tl IS
SELECT last_updated_by
,last_update_date
FROM xla_tb_definitions_tl
WHERE definition_code = p_definition_code
AND LANGUAGE = userenv('LANG');
l_last_updated_by NUMBER; -- owner in file
l_last_update_date DATE; -- last update date in file
l_db_last_updated_by NUMBER; -- owner in db
l_db_last_update_date DATE; -- last update date in db
l_last_updated_by := fnd_load_util.owner_id(p_owner);
l_last_update_date := NVL(TO_DATE(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
INTO l_db_last_updated_by
,l_db_last_update_date;
p_file_id => l_last_updated_by
,p_file_lud => l_last_update_date
,p_db_id => l_db_last_updated_by
,p_db_lud => l_db_last_update_date
,p_custom_mode => p_custom_mode)
THEN
UPDATE xla_tb_definitions_tl
SET name = p_name
,description = p_description
,last_updated_by = l_last_updated_by
,last_update_date = l_last_update_date
,last_update_login = 0
,source_lang = userenv('LANG')
WHERE definition_code = p_definition_code
AND userenv('LANG') IN (language, source_lang);