The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER,
p_CREATION_DATE DATE,
p_CREATED_BY NUMBER,
p_LAST_UPDATE_LOGIN NUMBER,
p_SEEDED_FLAG VARCHAR2,
p_APPLICATION_ID NUMBER,
p_DASHBOARD_GROUP_ID NUMBER,
x_PROF_MODULE_GRP_Rec OUT NOCOPY PROF_MODULE_GRP_Rec_Type )
IS
BEGIN
x_PROF_MODULE_GRP_rec.MODULE_GROUP_ID := P_MODULE_GROUP_ID;
x_PROF_MODULE_GRP_rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
x_PROF_MODULE_GRP_rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
x_PROF_MODULE_GRP_rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER,
p_CREATION_DATE DATE,
p_CREATED_BY NUMBER,
p_LAST_UPDATE_LOGIN NUMBER,
p_SEEDED_FLAG VARCHAR2,
p_APPLICATION_ID NUMBER,
p_DASHBOARD_GROUP_ID NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_PROF_MODULE_GRP_REC PROF_MODULE_GRP_REC_TYPE;
p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE,
p_LAST_UPDATED_BY => p_LAST_UPDATED_BY,
p_CREATION_DATE => p_CREATION_DATE,
p_CREATED_BY => p_CREATED_BY,
p_LAST_UPDATE_LOGIN => p_LAST_UPDATE_LOGIN,
p_SEEDED_FLAG => p_SEEDED_FLAG,
p_APPLICATION_ID => p_APPLICATION_ID,
p_DASHBOARD_GROUP_ID => p_DASHBOARD_GROUP_ID,
x_PROF_MODULE_GRP_Rec => l_PROF_MODULE_GRP_Rec );
CSC_PROF_MODULE_GROUPS_PKG.Insert_Row(
px_MODULE_GROUP_ID => px_MODULE_GROUP_ID,
p_FORM_FUNCTION_ID => p_PROF_MODULE_GRP_rec.FORM_FUNCTION_ID,
p_FORM_FUNCTION_NAME => p_PROF_MODULE_GRP_rec.FORM_FUNCTION_NAME,
p_RESPONSIBILITY_ID => p_PROF_MODULE_GRP_rec.RESPONSIBILITY_ID,
p_RESP_APPL_ID => p_PROF_MODULE_GRP_rec.RESP_APPL_ID,
p_PARTY_TYPE => p_PROF_MODULE_GRP_rec.PARTY_TYPE,
p_GROUP_ID => p_PROF_MODULE_GRP_rec.GROUP_ID,
p_DASHBOARD_GROUP_FLAG => p_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_FLAG,
p_CURRENCY_CODE => p_PROF_MODULE_GRP_rec.CURRENCY_CODE,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => p_PROF_MODULE_GRP_rec.LAST_UPDATE_LOGIN,
p_SEEDED_FLAG => p_PROF_MODULE_GRP_rec.SEEDED_FLAG,
p_APPLICATION_ID => p_PROF_MODULE_GRP_rec.APPLICATION_ID,
p_DASHBOARD_GROUP_ID => p_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_ID);
PROCEDURE Update_prof_module_groups(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
P_MODULE_GROUP_ID NUMBER,
p_FORM_FUNCTION_ID NUMBER,
p_FORM_FUNCTION_NAME VARCHAR2,
p_RESPONSIBILITY_ID NUMBER,
p_RESP_APPL_ID NUMBER,
p_PARTY_TYPE VARCHAR2,
p_GROUP_ID NUMBER,
p_DASHBOARD_GROUP_FLAG VARCHAR2,
p_CURRENCY_CODE VARCHAR2,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER,
p_CREATION_DATE DATE DEFAULT NULL,
p_CREATED_BY NUMBER DEFAULT NULL,
p_LAST_UPDATE_LOGIN NUMBER,
p_SEEDED_FLAG VARCHAR2,
p_APPLICATION_ID NUMBER,
p_DASHBOARD_GROUP_ID NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_PROF_MODULE_GRP_REC PROF_MODULE_GRP_REC_TYPE;
p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE,
p_LAST_UPDATED_BY => p_LAST_UPDATED_BY,
p_CREATION_DATE => p_CREATION_DATE,
p_CREATED_BY => p_CREATED_BY,
p_LAST_UPDATE_LOGIN => p_LAST_UPDATE_LOGIN,
p_SEEDED_FLAG => p_SEEDED_FLAG,
p_APPLICATION_ID => p_APPLICATION_ID,
p_DASHBOARD_GROUP_ID => p_DASHBOARD_GROUP_ID,
x_PROF_MODULE_GRP_Rec => l_PROF_MODULE_GRP_Rec );
Update_prof_module_groups(
P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_PROF_MODULE_GRP_Rec => l_PROF_MODULE_GRP_Rec,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data
);
END Update_prof_module_groups;
PROCEDURE Update_prof_module_groups(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
P_PROF_MODULE_GRP_Rec IN PROF_MODULE_GRP_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor C_Get_prof_module_groups(c_MODULE_GROUP_ID Number) IS
Select rowid,
MODULE_GROUP_ID,
FORM_FUNCTION_ID,
FORM_FUNCTION_NAME,
RESPONSIBILITY_ID,
RESP_APPL_ID,
PARTY_TYPE,
GROUP_ID,
DASHBOARD_GROUP_FLAG,
CURRENCY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SEEDED_FLAG,
APPLICATION_ID,
DASHBOARD_GROUP_ID
From CSC_PROF_MODULE_GROUPS
Where module_group_id = c_module_group_id
For Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_prof_module_groups';
SAVEPOINT UPDATE_PROF_MODULE_GROUPS_PVT;
l_old_PROF_MODULE_GRP_rec.LAST_UPDATE_DATE,
l_old_PROF_MODULE_GRP_rec.LAST_UPDATED_BY,
l_old_PROF_MODULE_GRP_rec.CREATION_DATE,
l_old_PROF_MODULE_GRP_rec.CREATED_BY,
l_old_PROF_MODULE_GRP_rec.LAST_UPDATE_LOGIN,
l_old_PROF_MODULE_GRP_rec.SEEDED_FLAG,
l_old_PROF_MODULE_GRP_rec.APPLICATION_ID,
l_old_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_ID;
p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
P_PROF_MODULE_GRP_Rec => l_PROF_MODULE_GRP_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CSC_PROF_MODULE_GROUPS_PKG.Update_Row(
p_MODULE_GROUP_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.MODULE_GROUP_ID,l_old_PROF_MODULE_GRP_rec.MODULE_GROUP_ID),
p_FORM_FUNCTION_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.FORM_FUNCTION_ID,l_old_PROF_MODULE_GRP_rec.FORM_FUNCTION_ID),
p_FORM_FUNCTION_NAME =>csc_core_utils_pvt.Get_G_Miss_Char(p_PROF_MODULE_GRP_rec.FORM_FUNCTION_NAME,l_old_PROF_MODULE_GRP_rec.FORM_FUNCTION_NAME),
p_RESPONSIBILITY_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.RESPONSIBILITY_ID,l_old_PROF_MODULE_GRP_rec.RESPONSIBILITY_ID),
p_RESP_APPL_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.RESP_APPL_ID,l_old_PROF_MODULE_GRP_rec.RESP_APPL_ID),
p_PARTY_TYPE =>csc_core_utils_pvt.Get_G_Miss_Char(p_PROF_MODULE_GRP_rec.PARTY_TYPE,l_old_PROF_MODULE_GRP_rec.PARTY_TYPE),
p_GROUP_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.GROUP_ID,l_old_PROF_MODULE_GRP_rec.GROUP_ID),
p_DASHBOARD_GROUP_FLAG =>csc_core_utils_pvt.Get_G_Miss_Char(p_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_FLAG,l_old_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_FLAG),
p_CURRENCY_CODE => csc_core_utils_pvt.Get_G_Miss_Char(p_PROF_MODULE_GRP_rec.CURRENCY_CODE,l_old_PROF_MODULE_GRP_rec.CURRENCY_CODE),
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.LAST_UPDATE_LOGIN,l_old_PROF_MODULE_GRP_rec.LAST_UPDATE_LOGIN),
p_SEEDED_FLAG => csc_core_utils_pvt.Get_G_Miss_Char(p_PROF_MODULE_GRP_rec.SEEDED_FLAG,l_old_PROF_MODULE_GRP_rec.SEEDED_FLAG),
p_APPLICATION_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.APPLICATION_ID,l_old_PROF_MODULE_GRP_rec.APPLICATION_ID),
p_DASHBOARD_GROUP_ID =>csc_core_utils_pvt.Get_G_Miss_Num(p_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_ID,l_old_PROF_MODULE_GRP_rec.DASHBOARD_GROUP_ID));
ROLLBACK TO UPDATE_PROF_MODULE_GROUPS_PVT;
ROLLBACK TO UPDATE_PROF_MODULE_GROUPS_PVT;
ROLLBACK TO UPDATE_PROF_MODULE_GROUPS_PVT;
End Update_prof_module_groups;
PROCEDURE Delete_prof_module_groups(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
P_PROF_MODULE_GRP_Id IN NUMBER,
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_prof_module_groups';
SAVEPOINT DELETE_PROF_MODULE_GROUPS_PVT;
CSC_PROF_MODULE_GROUPS_PKG.Delete_Row(
p_MODULE_GROUP_ID => p_PROF_MODULE_GRP_Id);
ROLLBACK TO DELETE_PROF_MODULE_GROUPS_PVT;
ROLLBACK TO DELETE_PROF_MODULE_GROUPS_PVT;
ROLLBACK TO DELETE_PROF_MODULE_GROUPS_PVT;
End Delete_prof_module_groups;
ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
THEN
-- Hint: Validate data
-- IF p_MODULE_GROUP_ID <> G_MISS_CHAR
-- verify if data is valid
-- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
Select NULL
from fnd_responsibility_vl
where responsibility_id = p_responsibility_id
and application_id = p_resp_appl_id;
Select NULL
from fnd_form_functions
where function_name = p_form_function_name;
Select NULL
from fnd_form_functions
where function_id = p_form_function_id;
ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
THEN
-- Hint: Validate data
-- IF p_PARTY_TYPE <> G_MISS_CHAR
-- verify if data is valid
-- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
Select NULL
from csc_prof_groups_vl
where group_id = P_GROUP_ID
and party_type = p_party_type
and nvl(use_in_customer_dashboard,'Y') = 'N'
UNION
Select NULL
from csc_prof_groups_vl
where group_id = P_GROUP_ID
and party_type = 'ALL'
and nvl(use_in_customer_dashboard,'Y') = 'N';
ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
THEN
-- Hint: Validate data
-- IF p_GROUP_ID <> G_MISS_CHAR
-- verify if data is valid
-- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
THEN
IF p_DASHBOARD_GROUP_FLAG IS NOT NULL AND
p_DASHBOARD_GROUP_FLAG <> CSC_CORE_UTILS_PVT.G_MISS_CHAR
THEN
IF p_DASHBOARD_GROUP_FLAG NOT IN ('Y','N')
THEN
x_return_status := FND_API.G_RET_STS_ERROR;
Select function_name
from fnd_form_functions
where function_id = c_form_function_id;
Select use_in_customer_dashboard
from csc_prof_groups_vl
where group_id = c_group_id;
select count(*) from csc_prof_module_groups
where form_function_name=c_form_function_name
and party_type=c_party_type
and responsibility_id = c_resp_id
and resp_appl_id = c_resp_appl_id;
select count(*) from csc_prof_module_groups
where form_function_name=c_form_function_name
and party_type=c_party_type
and responsibility_id = c_resp_id
and resp_appl_id = c_resp_appl_id
and module_group_id <> c_mod_grp_id;
elsif p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE then
Open C4(p_prof_module_grp_rec.form_function_name,p_prof_module_grp_rec.party_type,
p_prof_module_grp_rec.responsibility_id, p_prof_module_grp_rec.resp_appl_id,
p_prof_module_grp_rec.module_group_id);
Select NULL
from csc_prof_groups_vl
where group_id = P_DASHBOARD_GROUP_ID
and party_type = p_party_type
and nvl(use_in_customer_dashboard,'N') = 'Y'
UNION
Select NULL
from csc_prof_groups_vl
where group_id = P_DASHBOARD_GROUP_ID
and party_type = 'ALL'
and nvl(use_in_customer_dashboard,'N') = 'Y';
ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
THEN
-- Hint: Validate data
-- IF p_DASHBOARD_GROUP_ID <> G_MISS_CHAR
-- verify if data is valid
-- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;