[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
create, update and delete Dynamic Groups from other modules.
Its main procedures are as following:
Create Dynamic Groups
Update Dynamic Groups
Delete Dynamic Groups
******************************************************************************************/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_DYNAMIC_GROUPS_PVT';
if (instr(ltrim(upper(l_sql_text)),'SELECT') = 1) then
--VALIDATE SQL STATEMENT
BEGIN
l_cursorid := DBMS_SQL.OPEN_CURSOR;
fnd_message.set_name ('JTF', 'JTF_RS_SQL_NOT_A_SELECT');
SELECT TO_CHAR(jtf_rs_groups_s.nextval), jtf_rs_dynamic_groups_s.nextval
INTO l_group_number, l_group_id
FROM dual;
jtf_rs_dynamic_groups_pkg.insert_row(
X_ROWID => l_rowid,
X_GROUP_ID => l_group_id,
X_GROUP_NUMBER => l_group_number,
X_USAGE => l_usage,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_SQL_TEXT => l_sql_text,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_GROUP_NAME => l_group_name,
X_GROUP_DESC => l_group_desc,
X_CREATION_DATE => l_date,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
/* Procedure to update the Dynamic Groups
based on input values passed by calling routines. */
PROCEDURE update_dynamic_groups
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
P_GROUP_NUMBER IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_NUMBER%TYPE,
P_GROUP_NAME IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_NAME%TYPE,
P_GROUP_DESC IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_DESC%TYPE,
P_USAGE IN JTF_RS_DYNAMIC_GROUPS_B.USAGE%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.END_DATE_ACTIVE%TYPE,
P_SQL_TEXT IN JTF_RS_DYNAMIC_GROUPS_B.SQL_TEXT%TYPE,
P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE,
P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DYNAMIC_GROUPS';
SELECT group_number,
usage ,
start_date_active,
end_date_active ,
sql_text,
object_version_number,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute_category,
group_name,
group_desc
FROM jtf_rs_dynamic_groups_vl
WHERE group_id = l_group_id;
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'C' ))
then
JTF_RS_DYNAMIC_GROUPS_CUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => p_group_number,
P_GROUP_NAME => p_group_name,
P_GROUP_DESC => p_group_desc,
P_USAGE => p_usage,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_SQL_TEXT => p_sql_text,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_resource_utl.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'V' ))
then
JTF_RS_DYNAMIC_GROUPS_VUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => p_group_number,
P_GROUP_NAME => p_group_name,
P_GROUP_DESC => p_group_desc,
P_USAGE => p_usage,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_SQL_TEXT => p_sql_text,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'I' ))
then
JTF_RS_DYNAMIC_GROUPS_IUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => p_group_number,
P_GROUP_NAME => p_group_name,
P_GROUP_DESC => p_group_desc,
P_USAGE => p_usage,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_SQL_TEXT => p_sql_text,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if (instr(ltrim(upper(l_sql_text)),'SELECT') = 1) then
--VALIDATE SQL STATEMENT
BEGIN
l_cursorid := DBMS_SQL.OPEN_CURSOR;
fnd_message.set_name ('JTF', 'JTF_RS_SQL_NOT_A_SELECT');
jtf_rs_dynamic_groups_pkg.update_row(
X_GROUP_ID => l_group_id,
X_GROUP_NUMBER => l_group_number,
X_USAGE => l_usage,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_SQL_TEXT => l_sql_text,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ATTRIBUTE1 => l_attribute1,
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_GROUP_NAME => l_group_name,
X_GROUP_DESC => l_group_desc,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'UPDATE_DYNAMIC_GROUPS', 'A', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'UPDATE_DYNAMIC_GROUPS', 'A', 'C' ))
then
JTF_RS_DYNAMIC_GROUPS_CUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => p_group_number,
P_GROUP_NAME => p_group_name,
P_GROUP_DESC => p_group_desc,
P_USAGE => p_usage,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_SQL_TEXT => p_sql_text,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'V' ))
then
JTF_RS_DYNAMIC_GROUPS_VUHK.UPDATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => p_group_number,
P_GROUP_NAME => p_group_name,
P_GROUP_DESC => p_group_desc,
P_USAGE => p_usage,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_SQL_TEXT => p_sql_text,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'I' ))
then
JTF_RS_DYNAMIC_GROUPS_IUHK.UPDATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => p_group_number,
P_GROUP_NAME => p_group_name,
P_GROUP_DESC => p_group_desc,
P_USAGE => p_usage,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_SQL_TEXT => p_sql_text,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
'UPDATE_DYNAMIC_GROUPS',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_DYNAMIC_GROUPS_PVT',
'UPDATE_DYNAMIC_GROUPS',
'M',
'M')
THEN
IF (jtf_rs_dynamic_groups_cuhk.ok_to_generate_msg(
p_group_id => l_group_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;
/* Procedure to delete the Dynamic Groups. */
PROCEDURE delete_dynamic_groups
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
P_OBJECT_VERSION_NUMBER IN JTF_RS_DYNAMIC_GROUPS_B.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_name CONSTANT VARCHAR2(30) := 'DELETE_DYNAMIC_GROUPS';
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'C' ))
then
JTF_RS_DYNAMIC_GROUPS_CUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'V' ))
then
JTF_RS_DYNAMIC_GROUPS_VUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'I' ))
then
JTF_RS_DYNAMIC_GROUPS_IUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
jtf_rs_dynamic_groups_pkg.delete_row(p_group_id);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'DELETE_DYNAMIC_GROUPS', 'A', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'DELETE_DYNAMIC_GROUPS', 'A', 'C' ))
then
JTF_RS_DYNAMIC_GROUPS_CUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'V' ))
then
JTF_RS_DYNAMIC_GROUPS_VUHK.DELETE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'I' ))
then
JTF_RS_DYNAMIC_GROUPS_IUHK.DELETE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
'DELETE_DYNAMIC_GROUPS',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_DYNAMIC_GROUPS_PVT',
'DELETE_DYNAMIC_GROUPS',
'M',
'M')
THEN
IF (jtf_rs_dynamic_groups_cuhk.ok_to_generate_msg(
p_group_id => p_group_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;