[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jts_configurations_b_s.nextval
INTO l_new_config_id
FROM sys.dual;
SELECT configuration_id
INTO l_config_id
FROM jts_configurations_b
WHERE config_name = p_config_name;
SELECT config_name
INTO l_config_name
FROM jts_configurations_b
WHERE configuration_id = p_config_id;
PROCEDURE INSERT_ROW(p_config_rec IN Config_Rec_Type,
x_config_id OUT NUMBER
) IS
BEGIN
x_config_id := GET_NEXT_CONFIG_ID;
insert into JTS_CONFIGURATIONS_B (
CONFIGURATION_ID,
CONFIG_NAME,
FLOW_ID,
RECORD_MODE,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_CONFIG_ID,
p_config_rec.CONFIG_NAME,
p_config_rec.FLOW_ID,
p_config_rec.RECORD_MODE,
1,
p_config_rec.ATTRIBUTE_CATEGORY,
p_config_rec.ATTRIBUTE1,
p_config_rec.ATTRIBUTE2,
p_config_rec.ATTRIBUTE3,
p_config_rec.ATTRIBUTE4,
p_config_rec.ATTRIBUTE5,
p_config_rec.ATTRIBUTE6,
p_config_rec.ATTRIBUTE7,
p_config_rec.ATTRIBUTE8,
p_config_rec.ATTRIBUTE9,
p_config_rec.ATTRIBUTE10,
p_config_rec.ATTRIBUTE11,
p_config_rec.ATTRIBUTE12,
p_config_rec.ATTRIBUTE13,
p_config_rec.ATTRIBUTE14,
p_config_rec.ATTRIBUTE15,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id
);
insert into JTS_CONFIGURATIONS_TL (
CONFIGURATION_ID,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
X_CONFIG_ID,
p_config_rec.DESCRIPTION,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from JTS_CONFIGURATIONS_TL T
where T.CONFIGURATION_ID = X_CONFIG_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_ROW;
SELECT count(*)
INTO l_count
FROM jts_configurations_b
WHERE config_name = p_config_name;
SELECT count(*)
INTO l_count
FROM jts_setup_flows_b
WHERE flow_id = p_flow_id;
INSERT_ROW(p_configuration_rec,
x_config_id);
PROCEDURE UPDATE_NAME_DESC (
p_api_version IN NUMBER,
p_config_id IN NUMBER,
p_config_name IN VARCHAR2,
p_config_desc IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ NAME_DESC';
SAVEPOINT update_name_and_desc;
UPDATE jts_configurations_b
SET config_name = p_config_name,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.user_id
WHERE configuration_id = p_config_id;
UPDATE jts_configurations_tl
SET description = p_config_desc,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.user_id,
source_lang = USERENV('LANG')
WHERE configuration_id = p_config_id
AND USERENV('LANG') IN (language, source_lang);
ROLLBACK TO update_name_and_desc;
ROLLBACK TO update_name_and_desc;
END UPDATE_NAME_DESC;
PROCEDURE DELETE_CONFIGURATION(
p_api_version IN NUMBER,
p_config_id IN NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_CONFIGURATION';
SAVEPOINT delete_configuration;
JTS_CONFIG_VERSION_PVT.DELETE_VERSIONS(p_api_version,
p_config_id);
JTS_CONFIGURATIONS_PKG.DELETE_ROW(p_config_id);
ROLLBACK TO delete_configuration;
END DELETE_CONFIGURATION;
SELECT c.configuration_id, c.config_name, c.description, c.flow_id, fl.flow_name, fl.flow_type,
lf.meaning, c.record_mode, l.meaning, c.attribute_category, c.attribute1, c.attribute2,
c.attribute3, c.attribute4, c.attribute5, c.attribute6,
c.attribute7, c.attribute8, c.attribute9, c.attribute10,
c.attribute11, c.attribute12, c.attribute13, c.attribute14,
c.attribute15, c.creation_date, c.created_by, c.last_update_date,
c.last_updated_by, c.last_update_login, u1.user_name, u2.user_name
INTO x_configuration_rec
FROM jts_configurations_vl c,
fnd_lookup_values_vl l,
fnd_lookup_values_vl lf,
jts_setup_flows_vl fl,
fnd_user u1,
fnd_user u2
WHERE c.configuration_id = p_config_id
AND fl.flow_id = c.flow_id
AND l.lookup_type = C_RECORD_MODE_TYPE
AND l.lookup_code = c.record_mode
AND lf.lookup_type = C_FLOW_TYPE
AND lf.lookup_code = fl.flow_type
AND u1.user_id (+) = c.created_by
AND u2.user_id (+) = c.last_updated_by;
sqlStmt := 'SELECT c.configuration_id, c.config_name, c.description, ' ||
' c.flow_id, fl.flow_name, fl.flow_type, lf.meaning, c.record_mode, ' ||
' l.meaning, c.attribute_category, c.attribute1, c.attribute2, ' ||
' c.attribute3, c.attribute4, c.attribute5, c.attribute6, ' ||
' c.attribute7, c.attribute8, c.attribute9, c.attribute10, ' ||
' c.attribute11, c.attribute12, c.attribute13, c.attribute14, ' ||
' c.attribute15, c.creation_date, c.created_by, ' ||
' c.last_update_date, c.last_updated_by, c.last_update_login, ' ||
' u1.user_name, u2.user_name ' ||
' FROM jts_configurations_vl c, ' ||
' fnd_lookup_values_vl l, ' ||
' fnd_lookup_values_vl lf, ' ||
' jts_setup_flows_vl fl, ' ||
' fnd_user u1, ' ||
' fnd_user u2 ' ||
' WHERE fl.flow_id = c.flow_id ' ||
' AND l.lookup_type = ''' || C_RECORD_MODE_TYPE ||
''' AND l.lookup_code = c.record_mode ' ||
' AND lf.lookup_type = ''' || C_FLOW_TYPE ||
''' AND lf.lookup_code = fl.flow_type ' ||
' AND u1.user_id = c.created_by ' ||
' AND u2.user_id = c.last_updated_by ' ||
p_where_clause ||
' ORDER BY ' || l_order_by || ' ' || l_how_to_order;
x_configuration_tbl(i).last_update_date,
x_configuration_tbl(i).last_updated_by,
x_configuration_tbl(i).last_update_login,
x_configuration_tbl(i).created_by_name,
x_configuration_tbl(i).last_updated_by_name;
SELECT flow_id
INTO x_flow_id
FROM jts_configurations_b
WHERE configuration_id = p_config_id;