The following lines contain the word 'select', 'insert', 'update' or 'delete':
like create, update delete and query resource values.
Its main procedures are as following:
Create Resource Values
Update Resource Values
Delete Resource Values
Delete All Resource Values
Get Resource Values
Get Resource Param List
Calls to these procedures will invoke table handlers
to do actual inserts, updates, deletes and queries from the tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RESOURCE_VALUES_PVT';
SELECT 'Y'
FROM jtf_rs_resource_values
WHERE ROWID = l_rowid;
SELECT 'Y'
FROM jtf_rs_resource_values
WHERE resource_param_id = l_resource_param_id
AND resource_id = l_resource_id
AND value_type = l_value_type;
select type
from jtf_rs_resource_params
where resource_param_id = c_resource_param_id;
SELECT jtf_rs_resource_params_s.nextval
INTO l_resource_param_value_id
FROM dual;
jtf_rs_resource_values_pkg.insert_row(
X_ROWID => l_rowid,
X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
X_RESOURCE_ID => l_resource_id,
X_RESOURCE_PARAM_ID => l_resource_param_id,
X_VALUE => l_value1,
X_VALUE_TYPE => l_value_type,
X_ATTRIBUTE2 => l_attribute2,
X_ATTRIBUTE3 => l_attribute3,
X_ATTRIBUTE4 => l_attribute4,
X_ATTRIBUTE5 => l_attribute5,
X_ATTRIBUTE6 => l_attribute6,
X_ATTRIBUTE7 => l_attribute7,
X_ATTRIBUTE8 => l_attribute8,
X_ATTRIBUTE9 => l_attribute9,
X_ATTRIBUTE10 => l_attribute10,
X_ATTRIBUTE11 => l_attribute11,
X_ATTRIBUTE12 => l_attribute12,
X_ATTRIBUTE13 => l_attribute13,
X_ATTRIBUTE14 => l_attribute14,
X_ATTRIBUTE15 => l_attribute15,
X_ATTRIBUTE_CATEGORY => l_attribute_category,
X_ATTRIBUTE1 => l_attribute1,
X_CREATION_DATE => sysdate,
X_CREATED_BY => jtf_resource_utl.created_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
);
PROCEDURE UPDATE_RS_RESOURCE_VALUES(
P_Api_Version IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_resource_param_value_id IN NUMBER,
p_value IN VARCHAR2,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
P_ATTRIBUTE10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
P_ATTRIBUTE_CATEGORY IN VARCHAR2,
p_object_version_number IN OUT NOCOPY JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RS_RESOURCE_VALUES';
SELECT resource_param_value_id
FROM jtf_rs_resource_values
WHERE resource_param_value_id = l_resource_param_value_id;
CURSOR c_resource_param_value_update( l_resource_param_value_id IN NUMBER ) IS
SELECT
DECODE(p_value, fnd_api.g_miss_char, value, p_value) l_value,
DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
FROM jtf_rs_resource_values
WHERE resource_param_value_id = l_resource_param_value_id;
resource_param_value_rec c_resource_param_value_update%ROWTYPE;
select type
from jtf_rs_resource_params
where resource_param_id = c_resource_param_id;
SAVEPOINT update_rs_resource_values_pvt;
'UPDATE_RS_RESOURCE_VALUES',
'B',
'C')
THEN
jtf_rs_resource_values_cuhk.update_rs_resource_values_pre(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
P_VALUE => l_value,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_VALUES',
'B',
'V')
THEN
jtf_rs_resource_values_vuhk.update_rs_resource_values_pre(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
P_VALUE => l_value,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_values_iuhk.update_rs_resource_values_pre(
X_RETURN_STATUS => x_return_status
);
OPEN c_resource_param_value_update(l_resource_param_value_id);
FETCH c_resource_param_value_update INTO resource_param_value_rec;
IF c_resource_param_value_update%NOTFOUND THEN
CLOSE c_resource_param_value_update;
SELECT resource_param_id, resource_id, value_type
INTO l_resource_param_id, l_resource_id, l_value_type
FROM jtf_rs_resource_values
WHERE resource_param_value_id = l_resource_param_value_id;
jtf_rs_resource_values_pkg.update_row(
X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
X_RESOURCE_ID => l_resource_id,
X_RESOURCE_PARAM_ID => l_resource_param_id,
X_VALUE => l_value1,
X_VALUE_TYPE => l_value_type,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ATTRIBUTE2 => resource_param_value_rec.l_attribute2,
X_ATTRIBUTE3 => resource_param_value_rec.l_attribute3,
X_ATTRIBUTE4 => resource_param_value_rec.l_attribute4,
X_ATTRIBUTE5 => resource_param_value_rec.l_attribute5,
X_ATTRIBUTE6 => resource_param_value_rec.l_attribute6,
X_ATTRIBUTE7 => resource_param_value_rec.l_attribute7,
X_ATTRIBUTE8 => resource_param_value_rec.l_attribute8,
X_ATTRIBUTE9 => resource_param_value_rec.l_attribute9,
X_ATTRIBUTE10 => resource_param_value_rec.l_attribute10,
X_ATTRIBUTE11 => resource_param_value_rec.l_attribute11,
X_ATTRIBUTE12 => resource_param_value_rec.l_attribute12,
X_ATTRIBUTE13 => resource_param_value_rec.l_attribute13,
X_ATTRIBUTE14 => resource_param_value_rec.l_attribute14,
X_ATTRIBUTE15 => resource_param_value_rec.l_attribute15,
X_ATTRIBUTE_CATEGORY => resource_param_value_rec.l_attribute_category,
X_ATTRIBUTE1 => resource_param_value_rec.l_attribute1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
);
'UPDATE_RS_RESOURCE_VALUES',
'A',
'C')
THEN
jtf_rs_resource_values_cuhk.update_rs_resource_values_post(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
P_VALUE => l_value,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_VALUES',
'A',
'V')
THEN
jtf_rs_resource_values_vuhk.update_rs_resource_values_post(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
P_VALUE => l_value,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_values_iuhk.update_rs_resource_values_post(
X_RETURN_STATUS => x_return_status
);
'UPDATE_RS_RESOURCE_VALUES',
'M',
'M')
THEN
IF (jtf_rs_resource_values_cuhk.ok_to_generate_msg(
p_resource_param_value_id => l_resource_param_value_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
ROLLBACK TO update_rs_resource_values_pvt;
ROLLBACK TO update_rs_resource_values_pvt;
END update_rs_resource_values;
PROCEDURE DELETE_RS_RESOURCE_VALUES(
P_Api_Version IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_resource_param_value_id IN NUMBER,
p_object_version_number IN JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RS_RESOURCE_VALUES';
SELECT resource_param_value_id
FROM jtf_rs_resource_values
WHERE resource_param_value_id = l_resource_param_value_id;
SAVEPOINT delete_rs_resource_values_pvt;
'DELETE_RS_RESOURCE_VALUES',
'B',
'C')
THEN
jtf_rs_resource_values_cuhk.delete_rs_resource_values_pre(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_VALUES',
'B',
'V')
THEN
jtf_rs_resource_values_vuhk.delete_rs_resource_values_pre(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_values_iuhk.delete_rs_resource_values_pre(
X_RETURN_STATUS => x_return_status
);
jtf_rs_resource_values_pkg.delete_row(
X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id
);
'DELETE_RS_RESOURCE_VALUES',
'A',
'C')
THEN
jtf_rs_resource_values_cuhk.delete_rs_resource_values_post(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_VALUES',
'A',
'V')
THEN
jtf_rs_resource_values_vuhk.delete_rs_resource_values_post(
P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_values_iuhk.delete_rs_resource_values_post(
X_RETURN_STATUS => x_return_status
);
'DELETE_RS_RESOURCE_VALUES',
'M',
'M')
THEN
IF (jtf_rs_resource_values_cuhk.ok_to_generate_msg(
p_resource_param_value_id => p_resource_param_value_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
ROLLBACK TO delete_rs_resource_values_pvt;
ROLLBACK TO delete_rs_resource_values_pvt;
END delete_rs_resource_values;
PROCEDURE DELETE_ALL_RS_RESOURCE_VALUES(
P_Api_Version IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_resource_id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ALL_RS_RESOURCE_VALUES';
SELECT jrv.resource_param_value_id, jrv.object_version_number
FROM jtf_rs_resource_values jrv, jtf_rs_resource_params jrp
WHERE jrv.resource_param_id = jrp.resource_param_id
AND jrp.application_id in ( 680, 172 )
AND jrv.resource_id = l_resource_id;
SAVEPOINT delete_all_rs_values_pvt;
'DELETE_ALL_RS_RESOURCE_VALUES',
'B',
'C')
THEN
jtf_rs_resource_values_cuhk.delete_all_rs_values_pre(
P_RESOURCE_ID => l_resource_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_ALL_RS_RESOURCE_VALUES',
'B',
'V')
THEN
jtf_rs_resource_values_vuhk.delete_all_rs_values_pre(
P_RESOURCE_ID => l_resource_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_values_iuhk.delete_all_rs_values_pre(
X_RETURN_STATUS => x_return_status
);
jtf_rs_resource_values_pkg.delete_row(
X_RESOURCE_PARAM_VALUE_ID => i.resource_param_value_id
);
'DELETE_ALL_RS_RESOURCE_VALUES',
'A',
'C')
THEN
jtf_rs_resource_values_cuhk.delete_all_rs_values_post(
P_RESOURCE_ID => l_resource_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_ALL_RS_RESOURCE_VALUES',
'A',
'V')
THEN
jtf_rs_resource_values_vuhk.delete_all_rs_values_post(
P_RESOURCE_ID => l_resource_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_values_iuhk.delete_all_rs_values_post(
X_RETURN_STATUS => x_return_status
);
ROLLBACK TO delete_all_rs_values_pvt;
ROLLBACK TO delete_all_rs_values_pvt;
END delete_all_rs_resource_values;
SELECT resource_param_value_id, value
FROM jtf_rs_resource_values
WHERE resource_param_id = l_resource_param_id
AND resource_id = l_resource_id
AND ( (value_type = l_value_type) OR (l_value_type is null) );
SELECT jrspm.resource_param_id, fnl.meaning, jrspm.type, jrspm.domain_lookup_type
FROM jtf_rs_resource_params jrspm, fnd_lookups fnl
WHERE jrspm.application_id = l_application_id
AND jrspm.name = fnl.lookup_code
AND fnl.lookup_type = 'IEM_AGENT_PARAMS';