[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row1
( p_profile_name IN VARCHAR,
p_description IN VARCHAR2,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE DEFAULT NULL,
p_profile_type_code IN VARCHAR2 DEFAULT NULL,
p_approval_status_code IN VARCHAR2 DEFAULT NULL,
p_business_group_id IN NUMBER DEFAULT NULL,
p_organization_id IN NUMBER DEFAULT NULL,
p_job_id IN NUMBER DEFAULT NULL,
p_position_id IN NUMBER DEFAULT NULL,
p_resource_id IN NUMBER DEFAULT NULL,
x_profile_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
INSERT INTO pa_role_profiles
(profile_id,
profile_name,
description,
effective_start_date,
effective_end_date,
profile_type_code,
approval_status_code,
business_group_id,
organization_id,
job_id,
position_id,
resource_id,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES
(PA_ROLE_PROFILES_S.nextval,
p_profile_name,
p_description,
p_effective_start_date,
p_effective_end_date,
p_profile_type_code,
p_approval_status_code,
p_business_group_id,
p_organization_id,
p_job_id,
p_position_id,
p_resource_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING
profile_id INTO x_profile_id;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ROLE_PROFILES_PKG.Insert_Row1'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Insert_Row1;
PROCEDURE Insert_Row2
( p_profile_id IN NUMBER,
p_project_role_id IN NUMBER,
p_role_weighting IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
INSERT INTO pa_role_profile_lines
(profile_id,
project_role_id,
role_weighting,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES
(p_profile_id,
p_project_role_id,
p_role_weighting,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ROLE_PROFILES_PKG.Insert_Row2'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Insert_Row2;
PROCEDURE Update_Row
( p_profile_id IN NUMBER,
p_profile_name IN VARCHAR,
p_description IN VARCHAR2,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE DEFAULT NULL,
-- p_profile_type_code IN VARCHAR2 DEFAULT NULL,
p_approval_status_code IN VARCHAR2 DEFAULT NULL,
p_business_group_id IN NUMBER DEFAULT NULL,
p_organization_id IN NUMBER DEFAULT NULL,
p_job_id IN NUMBER DEFAULT NULL,
p_position_id IN NUMBER DEFAULT NULL,
p_resource_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
UPDATE pa_role_profiles SET
profile_name = p_profile_name,
description = p_description,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
approval_status_code = p_approval_status_code,
business_group_id = p_business_group_id,
organization_id = p_organization_id,
job_id = p_job_id,
position_id = p_position_id,
resource_id = p_resource_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE profile_id = p_profile_id;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ROLE_PROFILES_PKG.Update_Row'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Row;
SELECT 'Y'
FROM pa_role_profiles
WHERE resource_id = p_resource_id
AND profile_type_code = p_profile_type_code
AND (TRUNC(effective_start_date) BETWEEN TRUNC(p_effective_start_date)
AND NVL(TRUNC(p_effective_end_date), TRUNC(effective_start_date))
OR
TRUNC(p_effective_start_date) BETWEEN TRUNC(effective_start_date)
AND NVL(TRUNC(effective_end_date), TRUNC(effective_start_date)) )
AND rownum = 1;
INSERT INTO pa_role_profiles
(profile_id,
profile_name,
profile_type_code,
resource_id,
description,
effective_start_date,
effective_end_date,
approval_status_code,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES
(PA_ROLE_PROFILES_S.nextval,
p_profile_name,
p_profile_type_code,
p_resource_id,
p_description,
p_effective_start_date,
p_effective_end_date,
'ASGMT_APPRVL_APPROVED', -- should be changed
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING
profile_id INTO l_profile_id;
dbms_output.put_line('after inserting pa_role_profiles, l_profile_id:'||l_profile_id);
INSERT INTO pa_role_profile_lines
(profile_id,
project_role_id,
role_weighting,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES
(l_profile_id,
l_role_id_tbl(i),
p_weighting_tbl(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
* This procedure will launch workflow to update the Resource Role Profile
* and Role Profile Lines in PA_ROLE_PROFILES and PA_ROLE_PROFILE_LINES
* after proper validation.
* This will be called from 'Update Resource Role Profile' page of PJR.
**********************************************************************/
/*
PROCEDURE Update_Res_Profiles
( p_profile_id IN NUMBER,
p_profile_name IN VARCHAR2,
p_description IN VARCHAR2 := NULL,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE := NULL,
p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2)
IS
CURSOR is_conflict_profile_csr IS
SELECT 'Y'
FROM pa_role_profiles pf1,
pa_role_profiles pf2
WHERE pf2.profile_id = p_profile_id
AND pf1.resource_id = pf2.resource_id
AND pf1.profile_type_code = pf2.profile_type_code
AND pf1.profile_id <> p_profile_id
AND (TRUNC(pf1.effective_start_date) BETWEEN TRUNC(p_effective_start_date)
AND NVL(TRUNC(p_effective_end_date), TRUNC(pf1.effective_start_date))
OR
TRUNC(p_effective_start_date) BETWEEN TRUNC(pf1.effective_start_date)
AND NVL(TRUNC(pf1.effective_end_date), TRUNC(pf1.effective_start_date)))
AND rownum = 1;
PA_DEBUG.init_err_stack('PA_ROLE_PROFILES_PUB.Update_Res_Profile');
UPDATE pa_role_profiles
SET profile_name = p_profile_name,
description = p_description,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
approval_status_code = 'ASGMT_APPRVL_APPROVED', -- should be changed
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE profile_id = p_profile_id;
DELETE FROM pa_role_profile_lines
WHERE profile_id = p_profile_id;
INSERT INTO pa_role_profile_lines
(profile_id,
project_role_id,
role_weighting,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES
(p_profile_id,
l_role_id_tbl(i),
p_weighting_tbl(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_ROLE_PROFILES_PUB.Update_Res_Profiles'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Res_Profiles;