[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_row( recinfo IN OUT NOCOPY gl_je_sources%ROWTYPE) IS
BEGIN
SELECT *
INTO recinfo
FROM gl_je_sources
WHERE je_source_name = recinfo.je_source_name;
END SELECT_ROW;
PROCEDURE select_columns(x_je_source_name VARCHAR2,
x_user_je_source_name IN OUT NOCOPY VARCHAR2,
x_effective_date_rule_code IN OUT NOCOPY VARCHAR2,
x_frozen_source_flag IN OUT NOCOPY VARCHAR2,
x_journal_approval_flag IN OUT NOCOPY VARCHAR2) IS
recinfo gl_je_sources%ROWTYPE;
select_row(recinfo);
END select_columns;
SELECT 'Duplicate'
FROM GL_JE_SOURCES jes
WHERE jes.je_source_name = x_je_source_name
AND ( x_row_id is null
OR jes.rowid <> chartorowid(x_row_id)) ;
SELECT 'Duplicate'
FROM GL_JE_SOURCES jes
WHERE jes.user_je_source_name = x_user_je_source_name
AND ( x_row_id is null
OR jes.rowid <> x_row_id);
SELECT 'Duplicate'
FROM GL_JE_SOURCES jes
WHERE jes.je_source_key = x_je_source_key
AND ( x_row_id is null
OR jes.rowid <> x_row_id);
SELECT gl_je_sources_s.NEXTVAL
FROM sys.dual;
SELECT 'Is SLA Source'
FROM
XLA_SUBLEDGERS sla
WHERE sla.je_source_name = X_je_source;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Je_Source_Name IN OUT NOCOPY VARCHAR2,
X_Language IN OUT NOCOPY VARCHAR2,
X_Source_Lang IN OUT NOCOPY VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Override_Edits_Flag VARCHAR2,
X_User_Je_Source_Name VARCHAR2,
X_Je_Source_Key VARCHAR2,
X_Journal_Reference_Flag VARCHAR2,
X_Journal_Approval_Flag VARCHAR2,
X_Effective_Date_Rule_Code VARCHAR2,
X_Import_Using_Key_Flag VARCHAR2,
X_Creation_Date DATE,
X_Last_Update_Login NUMBER,
X_Description VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2) IS
CURSOR C IS SELECT rowid, language, source_lang
FROM GL_JE_SOURCES_TL
WHERE je_source_name = X_Je_Source_Name
and Language = userenv('LANG');
CURSOR C2 IS SELECT gl_je_headers_s.nextval FROM dual;
INSERT INTO GL_JE_SOURCES_TL(
je_source_name,
je_source_key,
language,
source_lang,
last_update_date,
last_updated_by,
override_edits_flag,
user_je_source_name,
journal_reference_flag,
journal_approval_flag,
effective_date_rule_code,
import_using_key_flag,
creation_date,
created_by,
last_update_login,
description,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
context)
SELECT
X_Je_Source_Name,
X_Je_Source_Key,
L.Language_Code,
userenv('LANG'),
X_Last_Update_Date,
X_Last_Updated_By,
X_Override_Edits_Flag,
X_User_Je_Source_Name,
X_Journal_Reference_Flag,
X_Journal_Approval_Flag,
X_Effective_Date_Rule_Code,
X_Import_Using_Key_Flag,
X_Creation_Date,
-- workaround for passing in created_by information w/o changing the spec
X_Last_Updated_By,
X_Last_Update_Login,
X_Description,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Context
FROM FND_LANGUAGES L
WHERE L.Installed_Flag in ('I', 'B')
AND not exists
( select NULL
from GL_JE_SOURCES_TL B
where B.Je_Source_Name = X_Je_Source_Name
and B.Language = L.Language_Code);
END Insert_Row;
SELECT * FROM GL_JE_SOURCES_TL
WHERE Je_Source_Name = X_Je_Source_Name
and Language = userenv('LANG')
FOR UPDATE OF JE_SOURCE_NAME NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Je_Source_Name VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Override_Edits_Flag VARCHAR2,
X_User_Je_Source_Name VARCHAR2,
X_Je_Source_Key VARCHAR2,
X_Journal_Reference_Flag VARCHAR2,
X_Journal_Approval_Flag VARCHAR2,
X_Effective_Date_Rule_Code VARCHAR2,
X_Import_Using_Key_Flag VARCHAR2,
X_Creation_Date DATE,
X_Last_Update_Login NUMBER,
X_Description VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2) IS
BEGIN
-- update non-translatable columns
UPDATE GL_JE_SOURCES_TL
SET
je_source_name = x_je_source_name,
je_source_key = x_je_source_key,
override_edits_flag = x_override_edits_flag,
journal_reference_flag = x_journal_reference_flag,
journal_approval_flag = x_journal_approval_flag,
effective_date_rule_code = x_effective_date_rule_code,
import_using_key_flag = x_import_using_key_flag,
creation_date = x_creation_date,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
context = x_context
WHERE
Je_Source_Name = X_Je_Source_Name;
UPDATE GL_JE_SOURCES_TL
SET
user_je_source_name = x_user_je_source_name,
description = x_description,
source_lang = userenv('LANG')
WHERE je_source_name = X_je_source_name
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
END Update_Row;
PROCEDURE Delete_Row(X_Je_Source_Name VARCHAR2) IS
BEGIN
DELETE FROM GL_JE_SOURCES_TL
WHERE Je_Source_Name = X_Je_Source_Name;
END Delete_Row;
the creation date for update_row. */
select creation_date
into v_creation_date
from gl_je_sources
where je_source_name = X_je_source_name;
/* Update only if force_edits is 'Y' or if user_id = 1 */
if ( user_id = 1 or X_Force_Edits = 'Y' ) then
-- update row if present
GL_JE_SOURCES_PKG.Update_Row(
X_Rowid => v_rowid,
X_je_source_name => X_Je_Source_Name,
X_last_update_date => sysdate,
X_last_updated_by => user_id,
X_override_edits_flag => X_Override_Edits_Flag,
X_user_je_source_name => X_user_je_source_name,
X_je_source_key => nvl(X_Je_Source_Key, X_Je_Source_Name),
X_journal_reference_flag => X_journal_reference_flag,
X_journal_approval_flag => X_journal_approval_flag,
X_effective_date_rule_code => X_effective_date_rule_code,
X_import_using_key_flag => nvl(X_import_using_key_flag, 'N'),
X_creation_date => v_creation_date,
X_last_update_login => 0,
X_Description => X_Description,
X_Attribute1 => X_Attribute1,
X_Attribute2 => X_Attribute2,
X_Attribute3 => X_Attribute3,
X_Attribute4 => X_Attribute4,
X_Attribute5 => X_Attribute5,
X_context => X_Context);
GL_JE_SOURCES_PKG.Insert_Row(
X_Rowid => v_rowid,
X_je_source_name => X_Je_Source_Name,
X_language => v_language,
X_source_lang => v_source_lang,
X_last_update_date => sysdate,
X_last_updated_by => user_id,
X_override_edits_flag => X_Override_Edits_Flag,
X_user_je_source_name => X_user_je_source_name,
X_je_source_key => nvl(X_Je_Source_Key, X_Je_Source_Name),
X_journal_reference_flag => X_journal_reference_flag,
X_journal_approval_flag => X_journal_approval_flag,
X_effective_date_rule_code => X_effective_date_rule_code,
X_import_using_key_flag => nvl(X_import_using_key_flag, 'N'),
X_creation_date => sysdate,
X_last_update_login => 0,
X_Description => X_Description,
X_Attribute1 => X_Attribute1,
X_Attribute2 => X_Attribute2,
X_Attribute3 => X_Attribute3,
X_Attribute4 => X_Attribute4,
X_Attribute5 => X_Attribute5,
X_context => X_Context);
/* Update only if force_edits is 'Y' or user_id = 1 */
if ( user_id = 1 or X_Force_Edits = 'Y' ) then
UPDATE GL_JE_SOURCES_TL
SET
user_je_source_name = x_user_je_source_name,
description = x_description,
last_update_date = sysdate,
last_updated_by = user_id,
last_update_login = 0,
source_lang = userenv('LANG')
WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and je_source_name = X_je_source_name;
update GL_JE_SOURCES_TL T
set ( user_je_source_name,
DESCRIPTION )
= ( select
B.user_je_source_name,
B.DESCRIPTION
from gl_je_sources_tl B
where B.je_source_name = T.je_source_name
and B.LANGUAGE = T.SOURCE_LANG )
where ( T.je_source_name,
T.LANGUAGE ) in
( select
SUBT.je_source_name,
SUBT.LANGUAGE
from gl_je_sources_tl SUBB,
gl_je_sources_tl SUBT
where SUBB.je_source_name = SUBT.je_source_name
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_JE_SOURCE_NAME <> SUBT.USER_JE_SOURCE_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 gl_je_sources_tl (
je_source_name,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
override_edits_flag,
user_je_source_name,
je_source_key,
journal_reference_flag,
journal_approval_flag,
effective_date_rule_code,
import_using_key_flag,
LANGUAGE,
SOURCE_LANG,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
context
)
select
B.je_source_name,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.DESCRIPTION,
B.override_edits_flag,
B.user_je_source_name,
B.je_source_key,
B.journal_reference_flag,
B.journal_approval_flag,
B.effective_date_rule_code,
B.import_using_key_flag,
L.LANGUAGE_CODE,
B.source_lang,
B.attribute1,
B.attribute2,
B.attribute3,
B.attribute4,
B.attribute5,
B.context
from gl_je_sources_tl B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from gl_je_sources_tl T
where T.je_source_name = B.je_source_name
and T.LANGUAGE = L.LANGUAGE_CODE);