[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
g_last_update_date DATE := SYSDATE;
* If yes then we shouldn't allow the user to create/Update
* the value for the flag to 'Y'.
******************************************************************/
FUNCTION Check_Primary_rep_flag
(p_project_id IN NUMBER,
p_rbs_header_id IN NUMBER)
RETURN VARCHAR2
IS
l_primary_rep_exists Varchar2(1) := 'N';
SELECT 'Y'
INTO l_primary_rep_exists
FROM dual
WHERE EXISTS
(SELECT rbs_prj_assignment_id
FROM pa_rbs_prj_assignments
WHERE project_id = p_project_id
AND assignment_status = 'ACTIVE'
AND primary_reporting_rbs_flag = 'Y'
AND rbs_header_id <> p_rbs_header_id);
* do the insertion.
*Called From : PA_RBS_ASGMT_PUB.Create_RBS_Assignment
****************************************************************/
PROCEDURE Create_RBS_Assignment(
p_rbs_header_id IN NUMBER,
p_rbs_version_id IN NUMBER DEFAULT NULL,
p_project_id IN NUMBER,
p_wp_usage_flag IN VARCHAR2 DEFAULT NULL,
p_fp_usage_flag IN VARCHAR2 DEFAULT NULL,
p_prog_rep_usage_flag IN VARCHAR2 DEFAULT NULL,
p_primary_rep_flag IN VARCHAR2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_error_msg_data OUT NOCOPY VARCHAR2)
IS
--Declaration of Local Variables
l_count Number;
SELECT rbs_header_id
INTO l_rbs_header_id
FROM pa_rbs_headers_b
WHERE rbs_header_id = p_rbs_header_id;
SELECT rbs_version_id
INTO l_rbs_version_id
FROM pa_rbs_versions_b
WHERE rbs_version_id = p_rbs_version_id
AND rbs_header_id = p_rbs_header_id
AND status_code = 'FROZEN';
SELECT sys_program_flag
INTO l_sys_program_flag
FROM pa_projects_all
WHERE project_id = p_project_id;
UPDATE pa_rbs_prj_assignments
SET wp_usage_flag = 'N'
WHERE rbs_header_id <> p_rbs_header_id
AND rbs_version_id <> l_rbs_version_id
AND project_id = p_project_id
AND wp_usage_flag = 'Y'
AND assignment_status = 'ACTIVE';
SELECT rpa.rbs_prj_assignment_id
INTO l_fp_assoc_id
FROM pa_rbs_prj_assignments rpa
WHERE rpa.project_id = p_project_id
AND rpa.fp_usage_flag = 'Y'
AND rpa.assignment_status = 'ACTIVE'
-- AND rpa.rbs_version_id <> l_rbs_version_id
AND rpa.rbs_version_id NOT IN (
SELECT pfo.rbs_version_id
FROM pa_proj_fp_options pfo
WHERE pfo.project_id = rpa.project_id
AND ((pfo.fin_plan_type_id <> (
SELECT pt.fin_plan_type_id
FROM pa_fin_plan_types_b pt
WHERE use_for_workplan_flag = 'Y'))
OR
(pfo.fin_plan_type_id IS NULL)));
UPDATE pa_rbs_prj_assignments
SET fp_usage_flag = 'N'
WHERE rbs_prj_assignment_id = l_fp_assoc_id;
* do an Update. Only if it does not exist do an
* Insert.
************************************************/
BEGIN
SELECT 'Y'
INTO l_exists_association
FROM dual
WHERE EXISTS
(SELECT rbs_prj_assignment_id
FROM pa_rbs_prj_assignments
WHERE rbs_header_id = p_rbs_header_id
AND rbs_version_id = l_rbs_version_id
AND project_id = p_project_id
AND assignment_status = 'ACTIVE');
* to the Update_Row Procedure.
********************************************/
BEGIN
SELECT rbs_prj_assignment_id, record_version_number,
nvl(p_wp_usage_flag, wp_usage_flag),
nvl(p_fp_usage_flag, fp_usage_flag),
nvl(p_prog_rep_usage_flag, prog_rep_usage_flag)
INTO l_rbs_prj_assignment_id, l_record_version_number,
l_wp_flag, l_fp_flag, l_prog_flag
FROM pa_rbs_prj_assignments
WHERE project_id = p_project_id
AND rbs_header_id = p_rbs_header_id
AND rbs_version_id = l_rbs_version_id; -- changed 7376494 to pass on value of max frozen RBS version id
* Call to PA_RBS_ASGMT_PKG.Update_Row procedure, which would
* take care of Updation of the pa_rbs_prj_assignments
* table.
*****************************************************/
PA_RBS_ASGMT_PKG.Update_Row(
p_rbs_prj_assignment_id => l_rbs_prj_assignment_id ,
p_wp_usage_flag => l_wp_flag,
p_fp_usage_flag => l_fp_flag,
p_prog_rep_usage_flag => l_prog_flag,
p_primary_rep_flag => p_primary_rep_flag,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
SELECT PA_RBS_PRJ_ASSIGNMENTS_S.NEXTVAL
INTO l_rbs_prj_assignment_id
FROM DUAL;
* Call to PA_RBS_ASGMT_PKG.Insert_Row procedure, which would
* take care of Insertion into the pa_rbs_prj_assignments
* table.
*****************************************************/
BEGIN
SELECT count(*)
INTO l_count
FROM pa_rbs_prj_assignments
WHERE project_id = p_project_id
AND assignment_status = 'ACTIVE' ;
PA_RBS_ASGMT_PKG.Insert_Row(
p_rbs_assignment_id => l_rbs_prj_assignment_id,
p_rbs_header_id => p_rbs_header_id,
p_rbs_version_id => l_rbs_version_id,
p_project_id => p_project_id,
p_wp_usage_flag => l_wp_flag,
p_fp_usage_flag => l_fp_flag,
p_prog_rep_usage_flag => l_prog_flag,
p_primary_rep_flag => l_primary_assignment,
x_return_status => l_return_status);
* x_record_version_number after insertion.??
********************************************************/
END IF;
SELECT count(*)
INTO l_count
FROM pa_rbs_prj_assignments
WHERE project_id = p_project_id
AND assignment_status = 'ACTIVE'
AND primary_reporting_rbs_flag = 'Y';
UPDATE pa_rbs_prj_assignments
SET primary_reporting_rbs_flag = 'Y'
WHERE project_id = p_project_id
AND assignment_status = 'ACTIVE'
AND rownum = 1;
* Procedure : Update_RBS_Assignment
* Description : The purpose of this procedure is to update an associate
* of an RBS to a project for any of the 4 uasges:-
* Reporting, Financial Plan, Workplan and
* Program Reporting.
* Reporting is the Default Usage type for all the
* associations.
* This Package would take care of all the validations
* necessary and then call the PA_RBS_ASGMT_Pkg.Update_Row to
* do the Updation.
*Called From : PA_RBS_ASGMT_PUB.Update_RBS_Assignment
****************************************************************/
PROCEDURE Update_RBS_Assignment(
p_rbs_prj_assignment_id IN NUMBER,
p_wp_usage_flag IN VARCHAR2 DEFAULT 'N',
p_fp_usage_flag IN VARCHAR2 DEFAULT 'N',
p_prog_rep_usage_flag IN VARCHAR2 DEFAULT 'N',
p_primary_rep_flag IN VARCHAR2 DEFAULT 'N',
p_record_version_number IN Number,
p_set_as_primary IN Varchar2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_error_msg_data OUT NOCOPY VARCHAR2 )
IS
l_return_status Varchar2(30);
SELECT project_id,rbs_header_id,rbs_version_id
INTO l_project_id,l_rbs_header_id,l_rbs_version_id
FROM pa_rbs_prj_assignments
WHERE rbs_prj_assignment_id = p_rbs_prj_assignment_id;
UPDATE pa_rbs_prj_assignments
SET primary_reporting_rbs_flag = 'N'
WHERE project_id = l_project_id
AND primary_reporting_rbs_flag = 'Y'
AND assignment_status = 'ACTIVE';
* Call to the Pa_rbs_Asgmt_pkg.Update_Row Procedure
* Which would update the values in the table
* pa_rbs_prj_asignemnts with the values passed.
*****************************************************/
Pa_Rbs_Asgmt_Pkg.Update_Row(
p_rbs_prj_assignment_id => p_rbs_prj_assignment_id ,
p_wp_usage_flag => p_wp_usage_flag,
p_fp_usage_flag => p_fp_usage_flag,
p_prog_rep_usage_flag => p_prog_rep_usage_flag,
p_primary_rep_flag => p_primary_rep_flag,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status );
END Update_RBS_Assignment;
* Procedure : Delete_RBS_Assignment
* Description : The purpose of this procedure is to Delete an associate
* of an RBS to a project for any of the 4 uasges:-
* This Package would take care of all the validations
* necessary and then call the PA_RBS_ASGMT_Pkg.Delete_Row to
* do the Remove operation.
* We cannot Remove any RBS that is being used for
* Workplan or Financial Plan.
* Called From : PA_RBS_ASGMT_PUB.Delete_RBS_Assignment
****************************************************************/
PROCEDURE Delete_RBS_Assignment(
p_rbs_prj_assignment_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_error_msg_data OUT NOCOPY VARCHAR2 )
IS
l_wp_usage_flag Varchar2(1);
* This select is used to retrieve the wp_usage_flag,
* fp_usage_flag and prog_rep_usage_flag
* for the rbs_rpj_assignment_id passed, from the
* pa_rbs_prj_assignments table.
* We will then use these values to determine if Removal
* of record is possible or not.
* **********************************************/
BEGIN
SELECT WP_USAGE_FLAG, FP_USAGE_FLAG,
PROG_REP_USAGE_FLAG,project_id,rbs_version_id
INTO l_wp_usage_flag, l_fp_usage_flag,
l_prog_rep_usage_flag,l_project_id,l_rbs_version_id
FROM pa_rbs_prj_assignments
WHERE RBS_PRJ_ASSIGNMENT_ID = p_rbs_prj_assignment_id;
PA_RBS_ASGMT_PKG.Delete_Row(
p_rbs_prj_assignment_id => p_rbs_prj_assignment_id,
x_return_status => x_return_status);
PJI_FM_XBS_ACCUM_MAINT.RBS_DELETE (
p_rbs_version_id => l_rbs_version_id
, p_project_id => l_project_id
, x_return_status => x_return_status
, x_msg_code => x_error_msg_data);
END Delete_RBS_Assignment;
SELECT rbs_version_id
INTO l_rbs_version_id
FROM pa_rbs_versions_b
WHERE rbs_version_id = p_rbs_version_id
AND rbs_header_id = p_rbs_header_id
AND status_code = 'FROZEN';
* Delete all the associations in the pa_rbs_prj_assignments
* table which corr to the rbs_header, version and project ID
* passed in, Which are Obsolete.
******************************************************/
/*FORALL i IN p_project_id_tbl.first .. p_project_id_tbl.last
DELETE FROM pa_rbs_prj_assignments
WHERE rbs_header_id = p_rbs_header_id
AND rbs_version_id = l_rbs_version_id
AND project_id = p_project_id_tbl(i)
AND assignment_status = 'OBSOLETE';
* do an Update. Only if it does not exist do an
* Insert.
************************************************/
BEGIN
SELECT 'Y'
INTO l_exists_association
FROM dual
WHERE EXISTS
(SELECT rbs_prj_assignment_id
FROM pa_rbs_prj_assignments
WHERE rbs_header_id = p_rbs_header_id
AND rbs_version_id = l_rbs_version_id
AND project_id = p_project_id_tbl(i)
AND assignment_status = 'ACTIVE');
* to the Update_Row Procedure.
********************************************/
BEGIN
SELECT rbs_prj_assignment_id, record_version_number
INTO l_rbs_prj_assignment_id, l_record_version_number
FROM pa_rbs_prj_assignments
WHERE project_id = p_project_id_tbl(i)
AND rbs_header_id = p_rbs_header_id
AND rbs_version_id = l_rbs_version_id;
* Call to PA_RBS_ASGMT_PKG.Update_Row procedure, which would
* take care of Updation of the pa_rbs_prj_assignments
* table.
* We only need to set the value for the
* reporting_usage flag = 'Y' and the prog_rep_usage_flag
* = 'Y'
*****************************************************/
BEGIN
UPDATE pa_rbs_prj_assignments
SET reporting_usage_flag = 'Y',
prog_rep_usage_flag = 'Y',
last_update_date = sysdate,
record_version_number = record_version_number + 1
WHERE Rbs_prj_assignment_id = l_rbs_prj_assignment_id
AND assignment_status = 'ACTIVE'
AND prog_rep_usage_flag = 'N'
AND NVL(record_version_number, 0) =
NVL(l_record_version_number, 0);
SELECT PA_RBS_PRJ_ASSIGNMENTS_S.NEXTVAL
INTO l_rbs_prj_assignment_id
FROM DUAL;
PA_RBS_ASGMT_PKG.Insert_Row(
p_rbs_assignment_id => l_rbs_prj_assignment_id,
p_rbs_header_id => p_rbs_header_id,
p_rbs_version_id => l_rbs_version_id,
p_project_id => p_project_id_tbl(i),
p_wp_usage_flag => 'N',
p_fp_usage_flag => 'N',
p_prog_rep_usage_flag => 'Y',
p_primary_rep_flag => 'N',
x_return_status => l_return_status );
* Update the pa_rbs_prj_assignments
* table.
****************************************************/
PROCEDURE Assign_New_Version(
p_rbs_new_version_id IN Number,
p_project_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
x_return_status OUT NOCOPY Varchar2)
IS
l_rbs_header_id Number;
SELECT rbs_header_id
INTO l_rbs_header_id
FROM pa_rbs_versions_b
WHERE rbs_version_id = p_rbs_new_version_id;
UPDATE pa_rbs_prj_assignments
SET rbs_version_id = p_rbs_new_version_id
WHERE project_id = p_project_id_tbl(i)
AND rbs_header_id = l_rbs_header_id
AND assignment_status = 'ACTIVE' ;
INSERT INTO pa_rbs_prj_assignments
(RBS_PRJ_ASSIGNMENT_ID,
PROJECT_ID,
RBS_VERSION_ID,
RBS_HEADER_ID,
REPORTING_USAGE_FLAG,
WP_USAGE_FLAG,
FP_USAGE_FLAG,
PROG_REP_USAGE_FLAG,
PRIMARY_REPORTING_RBS_FLAG,
ASSIGNMENT_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER)
SELECT
PA_RBS_PRJ_ASSIGNMENTS_S.NEXTVAL,
p_rbs_dest_project_id,
a.RBS_VERSION_ID,
a.RBS_HEADER_ID,
a.REPORTING_USAGE_FLAG,
a.WP_USAGE_FLAG,
a.FP_USAGE_FLAG,
a.PROG_REP_USAGE_FLAG,
a.PRIMARY_REPORTING_RBS_FLAG,
a.ASSIGNMENT_STATUS,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
1
FROM pa_rbs_prj_assignments a
WHERE a.project_id = p_rbs_src_project_id
and (a.RBS_VERSION_ID,a.RBS_HEADER_ID)
NOT IN (select rbs_version_id,rbs_header_id
from pa_rbs_prj_assignments
where project_id = p_rbs_dest_project_id);