[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_res_numRecInserted Number := Null;
g_rbs_numRecInserted Number := Null;
INSERT INTO PA_FP_CALCULATE_LOG
(SESSIONID
,SEQ_NUMBER
,LOG_MESSAGE)
VALUES
(userenv('sessionid')
,HR.PAY_US_GARN_FEE_RULES_S.nextval
,substr(P_MSG,1,240)
);
Select NVL(p_resource_list_id,bv.resource_list_id)
,NVL(p_rbs_version_id,fp.rbs_version_id)
,NVL(p_project_id,bv.project_id)
,bv.budget_version_id
Into g_resource_list_id
,g_rbs_version_id
,g_project_id
,g_budget_version_id
From pa_budget_versions bv
,pa_proj_fp_options fp
Where bv.budget_version_id = p_budget_version_id
And fp.fin_plan_version_id (+) = bv.budget_version_id
and rownum = 1;
Select NVL(p_project_id,project_id)
Into g_project_id
From pa_budget_versions
Where budget_version_id = p_budget_version_id;
/* This API inserts records into RBS mapping tmp tables
* the records will be inserted Based on calling mode
*/
PROCEDURE populate_rbsmap_tmp
(p_budget_version_id IN Number
,p_calling_mode IN varchar2
,x_return_status OUT NOCOPY varchar2 ) IS
l_stage varchar2(1000);
DELETE FROM pa_rbs_plans_in_tmp ;
DELETE FROM pa_rbs_plans_out_tmp;
l_stage := 'Inserting recrods into pa_rbs_plans_in_tmp for the budget version';
INSERT INTO pa_rbs_plans_in_tmp
(source_id
,person_id
,Job_id
,organization_id
,Supplier_id
--,Expenditure_type_id
--,Event_type_id
--,Expenditure_category_id
--,Non_labor_resource_id
--,Resource_class_id
,Revenue_category_code
,Inventory_item_id
,Item_category_id
,Bom_labor_id
,Bom_equipment_id
,Role_id
,Person_type_code
)
SELECT ra.resource_assignment_id
,ra.person_id
,ra.job_id
,ra.organization_id
,ra.supplier_id
--,et.expenditure_type_id
--,ev.event_type_id
--,ec.expenditure_category_id
--,nlr.non_labor_resource_id
--,rc.resource_class_id
,ra.revenue_category_code
,ra.inventory_item_id
,ra.item_category_id
,decode(ra.bom_resource_id,NULL,NULL,
decode(nvl(ra.incur_by_res_class_code,ra.resource_class_code),'PEOPLE',ra.bom_resource_id,NULL))
,decode(ra.bom_resource_id,NULL,NULL,
decode(nvl(ra.incur_by_res_class_code,ra.resource_class_code),'EQUIPMENT',ra.bom_resource_id,NULL))
,nvl(ra.incur_by_role_id,ra.project_role_id)
,ra.person_type_code
FROM pa_resource_assignments ra
--,pa_budget_versions bv
--,pa_proj_fp_options fp
--,pa_expenditure_types et
--,pa_non_labor_resources nlr
--,pa_expenditure_categories ec
--,pa_event_types ev
--,pa_resource_classes_b rc
WHERE ra.budget_version_id = p_budget_version_id
--AND ra.budget_version_id = bv.budget_version_id (+)
--ANd bv.budget_version_id = fp.fin_plan_version_id(+)
--and ra.expenditure_type = et.expenditure_type (+)
--and ra.non_labor_resource = nlr.non_labor_resource (+)
--and ra.expenditure_category = ec.expenditure_category (+)
--and ra.event_type = ev.event_type (+)
--and ra.resource_class_code = rc.resource_class_code (+)
;
g_rbs_numRecInserted := sql%Rowcount ;
l_stage := 'Num Of Records Inserted ['||g_rbs_numRecInserted||']';
/* bug fix: 3678165 added this update commented out the outer join from insert */
If g_rbs_numRecInserted > 0 Then
l_stage := 'Update the tmp table with exp,event,cate,Ids';
UPDATE pa_rbs_plans_in_tmp tmp
SET tmp.expenditure_type_id = (select et.expenditure_type_id
from pa_expenditure_types et
,pa_resource_assignments ra
where et.expenditure_type = ra.expenditure_type
and ra.resource_assignment_id = tmp.source_id
and rownum =1 )
,tmp.non_labor_resource_id = (select nlr.non_labor_resource_id
from pa_non_labor_resources nlr
,pa_resource_assignments ra
where nlr.non_labor_resource = ra.non_labor_resource
and ra.resource_assignment_id = tmp.source_id
and rownum = 1)
,tmp.expenditure_category_id = (select ec.expenditure_category_id
from pa_expenditure_categories ec
,pa_resource_assignments ra
where ec.expenditure_category = ra.expenditure_category
and ra.resource_assignment_id = tmp.source_id
and rownum = 1)
,tmp.event_type_id = (select ev.event_type_id
from pa_event_types ev
,pa_resource_assignments ra
where ra.event_type = ev.event_type
and ra.resource_assignment_id = tmp.source_id
and rownum = 1)
,tmp.resource_class_id = (select rc.resource_class_id
from pa_resource_classes_b rc
,pa_resource_assignments ra
where nvl(ra.incur_by_res_class_code,ra.resource_class_code) = rc.resource_class_code
and ra.resource_assignment_id = tmp.source_id
and rownum = 1) ;
print_msg(g_debug_flag,'Number of rows updated on pa_rbs_plans_in_tmp['||sql%Rowcount||']');
l_stage := 'Inserting recrods into pa_rbs_plans_in_tmp from PLSQL tables';
INSERT INTO pa_rbs_plans_in_tmp
(source_id
,person_id
,Job_id
,organization_id
,Supplier_id
,Expenditure_type_id
,Event_type_id
,Expenditure_category_id
,Revenue_category_code
,Inventory_item_id
,Item_category_id
,Bom_labor_id
,Bom_equipment_id
,Non_labor_resource_id
,Role_id
,Person_type_code
,Resource_class_id
)
SELECT g_txn_source_id_sqltab(i)
,g_person_id_sqltab(i)
,g_job_id_sqltab(i)
,g_organization_id_sqltab(i)
,g_vendor_id_sqltab(i)
,NULL --et.expenditure_type_id
,NULL --ev.event_type_id
,NULL --ec.expenditure_category_id
,g_revenue_category_code_sqltab(i)
,g_inventory_item_id_sqltab(i)
,g_item_category_id_sqltab(i)
,decode(g_bom_resource_id_sqltab(i),NULL,NULL,
decode(g_resource_class_code_sqltab(i),'PEOPLE',g_bom_resource_id_sqltab(i),NULL))
,decode(g_bom_resource_id_sqltab(i),NULL,NULL,
decode(g_resource_class_code_sqltab(i),'EQUIPMENT',g_bom_resource_id_sqltab(i),NULL))
,NULL --nlr.non_labor_resource_id
,g_project_role_id_sqltab(i)
,g_person_type_code_sqltab(i)
,NULL --rc.resource_class_id
FROM Dual ;
g_rbs_numRecInserted := g_txn_source_id_sqltab.Count ;
l_stage := 'Num Of Records Inserted ['||g_rbs_numRecInserted||']';
UPDATE pa_rbs_plans_in_tmp tmp
SET tmp.expenditure_type_id = (select et.expenditure_type_id
from pa_expenditure_types et
where et.expenditure_type = g_expenditure_type_sqltab(i))
, tmp.non_labor_resource_id = (select nlr.non_labor_resource_id
from pa_non_labor_resources nlr
where nlr.non_labor_resource = g_non_labor_resource_sqltab(i))
,tmp.resource_class_id = (select rc.resource_class_id
from pa_resource_classes_b rc
where rc.resource_class_code = g_resource_class_code_sqltab(i) )
,tmp.expenditure_category_id = (select ec.expenditure_category_id
from pa_expenditure_categories ec
where ec.expenditure_category = g_expenditure_category_sqltab(i))
/* Bug fix: 3999186 populating event type */
,tmp.event_type_id = (select evt.event_type_id
from pa_event_types evt
where evt.event_type = g_event_type_sqltab(i))
WHERE tmp.source_id = g_txn_source_id_sqltab(i);
l_stage := 'Inserting recrods into pa_rbs_plans_in_tmp from SYSTEM tables';
INSERT INTO pa_rbs_plans_in_tmp
(source_id
,person_id
,Job_id
,organization_id
,Supplier_id
,Expenditure_type_id
,Event_type_id
,Expenditure_category_id
,Revenue_category_code
,Inventory_item_id
,Item_category_id
,Bom_labor_id
,Bom_equipment_id
,Non_labor_resource_id
,Role_id
,Person_type_code
,Resource_class_id
)
SELECT g_txn_source_id_systab(i)
,g_person_id_systab(i)
,g_job_id_systab(i)
,g_organization_id_systab(i)
,g_vendor_id_systab(i)
,NULL --et.expenditure_type_id
,NULL --ev.event_type_id
,NULL --ec.expenditure_category_id
,g_revenue_category_code_systab(i)
,g_inventory_item_id_systab(i)
,g_item_category_id_systab(i)
,decode(g_bom_resource_id_systab(i),NULL,NULL,
decode(g_resource_class_code_systab(i),'PEOPLE',g_bom_resource_id_systab(i),NULL))
,decode(g_bom_resource_id_systab(i),NULL,NULL,
decode(g_resource_class_code_systab(i),'EQUIPMENT',g_bom_resource_id_systab(i),NULL))
,NULL --nlr.non_labor_resource_id
,g_project_role_id_systab(i)
,g_person_type_code_systab(i)
,NULL --rc.resource_class_id
FROM Dual ;
g_rbs_numRecInserted := g_txn_source_id_systab.count;
l_stage := 'Num Of Records Inserted ['||g_rbs_numRecInserted||']';
UPDATE pa_rbs_plans_in_tmp
SET expenditure_type_id = (select et.expenditure_type_id
from pa_expenditure_types et
where et.expenditure_type = g_expenditure_type_systab(i))
, non_labor_resource_id = (select nlr.non_labor_resource_id
from pa_non_labor_resources nlr
where nlr.non_labor_resource = g_non_labor_resource_systab(i))
,resource_class_id = (select rc.resource_class_id
from pa_resource_classes_b rc
where rc.resource_class_code = g_resource_class_code_systab(i) )
,expenditure_category_id = (select ec.expenditure_category_id
from pa_expenditure_categories ec
where ec.expenditure_category = g_expenditure_category_systab(i))
/* Bug fix: 3999186 populating event type */
,event_type_id = (select evt.event_type_id
from pa_event_types evt
where evt.event_type = g_event_type_systab(i))
WHERE source_id = g_txn_source_id_systab(i);
UPDATE pa_rbs_plans_in_tmp tmp
SET tmp.expenditure_category_id = (select etc.expenditure_category_id
from pa_expenditure_types et
,pa_expenditure_categories etc
where et.expenditure_type_id = tmp.expenditure_type_id
and et.expenditure_category = etc.expenditure_category
)
WHERE tmp.expenditure_category_id is NULL
AND tmp.expenditure_type_id is NOT NULL ;
UPDATE pa_rbs_plans_in_tmp tmp
SET tmp.Revenue_category_code = (select et.Revenue_category_code
from pa_event_types et
where et.event_type_id = tmp.event_type_id
)
WHERE tmp.Revenue_category_code is NULL
AND tmp.event_type_id is NOT NULL ;
UPDATE pa_rbs_plans_in_tmp tmp
SET tmp.Revenue_category_code = (select et.Revenue_category_code
from pa_expenditure_types et
where et.expenditure_type_id = tmp.expenditure_type_id
)
WHERE tmp.Revenue_category_code is NULL
AND tmp.expenditure_type_id is NOT NULL ;
UPDATE pa_rbs_plans_in_tmp tmp
SET tmp.item_category_id = ( SELECT cat.CATEGORY_ID
FROM PA_RESOURCE_CLASSES_B classes
,PA_PLAN_RES_DEFAULTS cls
,MTL_ITEM_CATEGORIES cat
WHERE classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'
AND cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID
AND cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID
AND cat.ORGANIZATION_ID = tmp.organization_id
AND cat.INVENTORY_ITEM_ID = tmp.inventory_item_id
AND rownum = 1
)
WHERE tmp.item_category_id is NULL
AND tmp.inventory_item_id is NOT NULL;
/* This API inserts records into Resource mapping tmp tables
* the records will be inserted Based on calling mode
*/
PROCEDURE populate_resmap_tmp
(p_budget_version_id IN Number
,p_calling_mode IN varchar2
,x_return_status OUT NOCOPY varchar2 ) IS
l_NumRecInserted Number := 0;
SELECT decode(nvl(wp_version_flag,'N'),
'N',PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID( project_id ),
project_structure_version_id)
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
l_NumRecInserted := 0;
DELETE FROM pa_res_list_map_tmp1;
DELETE FROM pa_res_list_map_tmp4;
SELECT nvl(uncategorized_flag,'N')
INTO l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id=g_resource_list_id;
l_stage := 'Inserting recrods into pa_res_list_map_tmp1 for the given budget version';
INSERT INTO pa_res_list_map_tmp4
(TXN_SOURCE_ID
,TXN_SOURCE_TYPE_CODE
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,VENDOR_ID
,EXPENDITURE_TYPE
--,EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY
,NON_LABOR_RESOURCE_ORG_ID
,EVENT_TYPE_CLASSIFICATION
,SYSTEM_LINKAGE_FUNCTION
,PROJECT_ROLE_ID
,RESOURCE_CLASS_CODE
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,BOM_LABOR_RESOURCE_ID
,BOM_EQUIP_RESOURCE_ID
,NAMED_ROLE
--,NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,TXN_RATE_BASED_FLAG
,TXN_TASK_ID
,TXN_WBS_ELEMENT_VERSION_ID
,TXN_RBS_ELEMENT_ID
,TXN_PLANNING_START_DATE
,TXN_PLANNING_END_DATE
,TXN_PROJECT_ID
,TXN_BUDGET_VERSION_ID
,resource_list_member_id)
SELECT ra.resource_assignment_id
,'RES_ASSIGNMENT'
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,SUPPLIER_ID VENDOR_ID
-- bug fix: 3698197 ,NVL(EXPENDITURE_TYPE,RATE_EXPENDITURE_TYPE)
,EXPENDITURE_TYPE
--,Null EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,Null EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,Null NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,Null EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY_CODE
,Null NLR_ORGANIZATION_ID
,Null EVENT_CLASSIFICATION
,Null SYS_LINK_FUNCTION
,NVL(incur_by_role_id,PROJECT_ROLE_ID)
,NVL(incur_by_res_class_code,RESOURCE_CLASS_CODE)
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,decode(bom_resource_id,NULL,NULL,
decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'PEOPLE',BOM_RESOURCE_ID,NULL))
,decode(bom_resource_id,NULL,NULL,
decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'EQUIPMENT',BOM_RESOURCE_ID,NULL))
,NAMED_ROLE
--,Null NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,RATE_BASED_FLAG
,TASK_ID
,NULL --pelm.element_version_id
,RBS_ELEMENT_ID
,PLANNING_START_DATE
,PLANNING_END_DATE
,ra.project_id
,ra.budget_version_id
,l_financial_res_class_rlm_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id;
INSERT INTO pa_res_list_map_tmp1
(TXN_SOURCE_ID
,TXN_SOURCE_TYPE_CODE
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,VENDOR_ID
,EXPENDITURE_TYPE
--,EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY
,NON_LABOR_RESOURCE_ORG_ID
,EVENT_TYPE_CLASSIFICATION
,SYSTEM_LINKAGE_FUNCTION
,PROJECT_ROLE_ID
,RESOURCE_CLASS_CODE
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,BOM_LABOR_RESOURCE_ID
,BOM_EQUIP_RESOURCE_ID
,NAMED_ROLE
--,NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,TXN_RATE_BASED_FLAG
,TXN_TASK_ID
,TXN_WBS_ELEMENT_VERSION_ID
,TXN_RBS_ELEMENT_ID
,TXN_PLANNING_START_DATE
,TXN_PLANNING_END_DATE
,TXN_PROJECT_ID
,TXN_BUDGET_VERSION_ID )
SELECT ra.resource_assignment_id
,'RES_ASSIGNMENT'
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,SUPPLIER_ID VENDOR_ID
-- bug fix: 3698197 ,NVL(EXPENDITURE_TYPE,RATE_EXPENDITURE_TYPE)
,EXPENDITURE_TYPE
--,Null EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,Null EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,Null NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,Null EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY_CODE
,Null NLR_ORGANIZATION_ID
,Null EVENT_CLASSIFICATION
,Null SYS_LINK_FUNCTION
,NVL(incur_by_role_id,PROJECT_ROLE_ID)
,NVL(incur_by_res_class_code,RESOURCE_CLASS_CODE)
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,decode(bom_resource_id,NULL,NULL,
decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'PEOPLE',BOM_RESOURCE_ID,NULL))
,decode(bom_resource_id,NULL,NULL,
decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'EQUIPMENT',BOM_RESOURCE_ID,NULL))
,NAMED_ROLE
--,Null NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,RATE_BASED_FLAG
,TASK_ID
,NULL --pelm.element_version_id
,RBS_ELEMENT_ID
,PLANNING_START_DATE
,PLANNING_END_DATE
,ra.project_id
,ra.budget_version_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id;
l_NumRecInserted := sql%Rowcount;
g_res_numRecInserted := l_NumRecInserted ;
l_stage := 'Num of Records inserted ['||g_res_numRecInserted||']';
/* Insert these plsql tables into ResMap Temp Tables*/
If g_TXN_SOURCE_ID_sqltab.COUNT > 0 Then
IF l_uncategorized_flag = 'Y' THEN
l_stage := 'Inserting records into pa_res_list_map_tmp4 from PLSQL tables';
INSERT INTO pa_res_list_map_tmp4
(TXN_SOURCE_ID
,TXN_SOURCE_TYPE_CODE
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,VENDOR_ID
,EXPENDITURE_TYPE
--,EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY
,NON_LABOR_RESOURCE_ORG_ID
,EVENT_TYPE_CLASSIFICATION
,SYSTEM_LINKAGE_FUNCTION
,PROJECT_ROLE_ID
,RESOURCE_CLASS_CODE
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,BOM_LABOR_RESOURCE_ID
,BOM_EQUIP_RESOURCE_ID
,NAMED_ROLE
--,NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,TXN_RATE_BASED_FLAG
,TXN_TASK_ID
,TXN_WBS_ELEMENT_VERSION_ID
,TXN_RBS_ELEMENT_ID
,TXN_PLANNING_START_DATE
,TXN_PLANNING_END_DATE
,TXN_PROJECT_ID
,TXN_BUDGET_VERSION_ID
,resource_list_member_id)
SELECT g_TXN_SOURCE_ID_sqltab(i)
,g_TXN_SOURCE_TYPE_CODE_sqltab(i)
,g_PERSON_ID_sqltab(i)
,g_JOB_ID_sqltab(i)
,g_ORGANIZATION_ID_sqltab(i)
,g_VENDOR_ID_sqltab(i)
,g_EXPENDITURE_TYPE_sqltab(i)
--,g_EXPENDITURE_TYPE_ID_sqltab(i)
,g_EVENT_TYPE_sqltab(i)
--,g_EVENT_TYPE_ID_sqltab(i)
,g_NON_LABOR_RESOURCE_sqltab(i)
--,g_NON_LABOR_RESOURCE_ID_sqltab(i)
,g_EXPENDITURE_CATEGORY_sqltab(i)
--,g_EXP_CATEGORY_ID_sqltab(i)
,g_REVENUE_CATEGORY_CODE_sqltab(i)
,g_NLR_ORGANIZATION_ID_sqltab(i)
,g_EVENT_CLASSIFICATION_sqltab(i)
,g_SYS_LINK_FUNCTION_sqltab(i)
,g_PROJECT_ROLE_ID_sqltab(i)
,g_RESOURCE_CLASS_CODE_sqltab(i)
,g_MFC_COST_TYPE_ID_sqltab(i)
,g_RESOURCE_CLASS_FLAG_sqltab(i)
,g_FC_RES_TYPE_CODE_sqltab(i)
,g_INVENTORY_ITEM_ID_sqltab(i)
,g_ITEM_CATEGORY_ID_sqltab(i)
,g_PERSON_TYPE_CODE_sqltab(i)
,g_BOM_RESOURCE_ID_sqltab(i)
,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_sqltab(i),'PEOPLE',g_BOM_RESOURCE_ID_sqltab(i),NULL))
,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_sqltab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_sqltab(i),NULL))
,g_NAMED_ROLE_sqltab(i)
--,g_NAMED_ROLE_ID_sqltab(i)
,g_INCURRED_BY_RES_FLAG_sqltab(i)
,g_RATE_BASED_FLAG_sqltab(i)
,g_TXN_TASK_ID_sqltab(i)
,g_TXN_WBS_ELE_VER_ID_sqltab(i)
,g_TXN_RBS_ELEMENT_ID_sqltab(i)
,g_TXN_PLAN_START_DATE_sqltab(i)
,g_TXN_PLAN_END_DATE_sqltab (i)
,g_PROJECT_ID
,g_BUDGET_VERSION_ID
,l_financial_res_class_rlm_id
FROM DUAL;
l_stage := 'Inserting records into pa_res_list_map_tmp1 from PLSQL tables';
INSERT INTO pa_res_list_map_tmp1
(TXN_SOURCE_ID
,TXN_SOURCE_TYPE_CODE
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,VENDOR_ID
,EXPENDITURE_TYPE
--,EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY
,NON_LABOR_RESOURCE_ORG_ID
,EVENT_TYPE_CLASSIFICATION
,SYSTEM_LINKAGE_FUNCTION
,PROJECT_ROLE_ID
,RESOURCE_CLASS_CODE
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,BOM_LABOR_RESOURCE_ID
,BOM_EQUIP_RESOURCE_ID
,NAMED_ROLE
--,NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,TXN_RATE_BASED_FLAG
,TXN_TASK_ID
,TXN_WBS_ELEMENT_VERSION_ID
,TXN_RBS_ELEMENT_ID
,TXN_PLANNING_START_DATE
,TXN_PLANNING_END_DATE
,TXN_PROJECT_ID
,TXN_BUDGET_VERSION_ID )
SELECT g_TXN_SOURCE_ID_sqltab(i)
,g_TXN_SOURCE_TYPE_CODE_sqltab(i)
,g_PERSON_ID_sqltab(i)
,g_JOB_ID_sqltab(i)
,g_ORGANIZATION_ID_sqltab(i)
,g_VENDOR_ID_sqltab(i)
,g_EXPENDITURE_TYPE_sqltab(i)
--,g_EXPENDITURE_TYPE_ID_sqltab(i)
,g_EVENT_TYPE_sqltab(i)
--,g_EVENT_TYPE_ID_sqltab(i)
,g_NON_LABOR_RESOURCE_sqltab(i)
--,g_NON_LABOR_RESOURCE_ID_sqltab(i)
,g_EXPENDITURE_CATEGORY_sqltab(i)
--,g_EXP_CATEGORY_ID_sqltab(i)
,g_REVENUE_CATEGORY_CODE_sqltab(i)
,g_NLR_ORGANIZATION_ID_sqltab(i)
,g_EVENT_CLASSIFICATION_sqltab(i)
,g_SYS_LINK_FUNCTION_sqltab(i)
,g_PROJECT_ROLE_ID_sqltab(i)
,g_RESOURCE_CLASS_CODE_sqltab(i)
,g_MFC_COST_TYPE_ID_sqltab(i)
,g_RESOURCE_CLASS_FLAG_sqltab(i)
,g_FC_RES_TYPE_CODE_sqltab(i)
,g_INVENTORY_ITEM_ID_sqltab(i)
,g_ITEM_CATEGORY_ID_sqltab(i)
,g_PERSON_TYPE_CODE_sqltab(i)
,g_BOM_RESOURCE_ID_sqltab(i)
,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_sqltab(i),'PEOPLE',g_BOM_RESOURCE_ID_sqltab(i),NULL))
,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_sqltab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_sqltab(i),NULL))
,g_NAMED_ROLE_sqltab(i)
--,g_NAMED_ROLE_ID_sqltab(i)
,g_INCURRED_BY_RES_FLAG_sqltab(i)
,g_RATE_BASED_FLAG_sqltab(i)
,g_TXN_TASK_ID_sqltab(i)
,g_TXN_WBS_ELE_VER_ID_sqltab(i)
,g_TXN_RBS_ELEMENT_ID_sqltab(i)
,g_TXN_PLAN_START_DATE_sqltab(i)
,g_TXN_PLAN_END_DATE_sqltab (i)
,g_PROJECT_ID
,g_BUDGET_VERSION_ID
FROM DUAL;
l_NumRecInserted := sql%Rowcount;
g_res_numRecInserted := l_NumRecInserted;
l_stage := 'Num of Records inserted ['||g_res_numRecInserted||']';
/* Insert these system.tab into ResMap Temp Tables*/
If g_TXN_SOURCE_ID_systab.COUNT > 0 Then
IF l_uncategorized_flag = 'Y' THEN
l_stage := 'Inserting records into pa_res_list_map_tmp4 from SYSTEM tables';
INSERT INTO pa_res_list_map_tmp4
(TXN_SOURCE_ID
,TXN_SOURCE_TYPE_CODE
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,VENDOR_ID
,EXPENDITURE_TYPE
--,EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY
,NON_LABOR_RESOURCE_ORG_ID
,EVENT_TYPE_CLASSIFICATION
,SYSTEM_LINKAGE_FUNCTION
,PROJECT_ROLE_ID
,RESOURCE_CLASS_CODE
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,BOM_LABOR_RESOURCE_ID
,BOM_EQUIP_RESOURCE_ID
,NAMED_ROLE
--,NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,TXN_RATE_BASED_FLAG
,TXN_TASK_ID
,TXN_WBS_ELEMENT_VERSION_ID
,TXN_RBS_ELEMENT_ID
,TXN_PLANNING_START_DATE
,TXN_PLANNING_END_DATE
,TXN_PROJECT_ID
,TXN_BUDGET_VERSION_ID
,resource_list_member_id)
SELECT g_TXN_SOURCE_ID_systab(i)
,g_TXN_SOURCE_TYPE_CODE_systab(i)
,g_PERSON_ID_systab(i)
,g_JOB_ID_systab(i)
,g_ORGANIZATION_ID_systab(i)
,g_VENDOR_ID_systab(i)
,g_EXPENDITURE_TYPE_systab(i)
--,g_EXPENDITURE_TYPE_ID_systab(i)
,g_EVENT_TYPE_systab(i)
--,g_EVENT_TYPE_ID_systab(i)
,g_NON_LABOR_RESOURCE_systab(i)
--,g_NON_LABOR_RESOURCE_ID_systab(i)
,g_EXPENDITURE_CATEGORY_systab(i)
--,g_EXP_CATEGORY_ID_systab(i)
,g_REVENUE_CATEGORY_CODE_systab(i)
,g_NLR_ORGANIZATION_ID_systab(i)
,g_EVENT_CLASSIFICATION_systab(i)
,g_SYS_LINK_FUNCTION_systab(i)
,g_PROJECT_ROLE_ID_systab(i)
,g_RESOURCE_CLASS_CODE_systab(i)
,g_MFC_COST_TYPE_ID_systab(i)
,g_RESOURCE_CLASS_FLAG_systab(i)
,g_FC_RES_TYPE_CODE_systab(i)
,g_INVENTORY_ITEM_ID_systab(i)
,g_ITEM_CATEGORY_ID_systab(i)
,g_PERSON_TYPE_CODE_systab(i)
,g_BOM_RESOURCE_ID_systab(i)
,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_systab(i),'PEOPLE',g_BOM_RESOURCE_ID_systab(i),NULL))
,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_systab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_systab(i),NULL))
,g_NAMED_ROLE_systab(i)
--,g_NAMED_ROLE_ID_systab(i)
,g_INCURRED_BY_RES_FLAG_systab(i)
,g_RATE_BASED_FLAG_systab(i)
,g_TXN_TASK_ID_systab(i)
,g_TXN_WBS_ELE_VER_ID_systab(i)
,g_TXN_RBS_ELEMENT_ID_systab(i)
,g_TXN_PLAN_START_DATE_systab(i)
,g_TXN_PLAN_END_DATE_systab (i)
,g_PROJECT_ID
,g_BUDGET_VERSION_ID
,l_financial_res_class_rlm_id
FROM DUAL;
l_stage := 'Inserting records into pa_res_list_map_tmp1 from SYSTEM tables';
INSERT INTO pa_res_list_map_tmp1
(TXN_SOURCE_ID
,TXN_SOURCE_TYPE_CODE
,PERSON_ID
,JOB_ID
,ORGANIZATION_ID
,VENDOR_ID
,EXPENDITURE_TYPE
--,EXPENDITURE_TYPE_ID
,EVENT_TYPE
--,EVENT_TYPE_ID
,NON_LABOR_RESOURCE
--,NON_LABOR_RESOURCE_ID
,EXPENDITURE_CATEGORY
--,EXPENDITURE_CATEGORY_ID
,REVENUE_CATEGORY
,NON_LABOR_RESOURCE_ORG_ID
,EVENT_TYPE_CLASSIFICATION
,SYSTEM_LINKAGE_FUNCTION
,PROJECT_ROLE_ID
,RESOURCE_CLASS_CODE
,MFC_COST_TYPE_ID
,RESOURCE_CLASS_FLAG
,FC_RES_TYPE_CODE
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,PERSON_TYPE_CODE
,BOM_RESOURCE_ID
,BOM_LABOR_RESOURCE_ID
,BOM_EQUIP_RESOURCE_ID
,NAMED_ROLE
--,NAMED_ROLE_ID
,INCURRED_BY_RES_FLAG
,TXN_RATE_BASED_FLAG
,TXN_TASK_ID
,TXN_WBS_ELEMENT_VERSION_ID
,TXN_RBS_ELEMENT_ID
,TXN_PLANNING_START_DATE
,TXN_PLANNING_END_DATE
,TXN_PROJECT_ID
,TXN_BUDGET_VERSION_ID )
SELECT g_TXN_SOURCE_ID_systab(i)
,g_TXN_SOURCE_TYPE_CODE_systab(i)
,g_PERSON_ID_systab(i)
,g_JOB_ID_systab(i)
,g_ORGANIZATION_ID_systab(i)
,g_VENDOR_ID_systab(i)
,g_EXPENDITURE_TYPE_systab(i)
--,g_EXPENDITURE_TYPE_ID_systab(i)
,g_EVENT_TYPE_systab(i)
--,g_EVENT_TYPE_ID_systab(i)
,g_NON_LABOR_RESOURCE_systab(i)
--,g_NON_LABOR_RESOURCE_ID_systab(i)
,g_EXPENDITURE_CATEGORY_systab(i)
--,g_EXP_CATEGORY_ID_systab(i)
,g_REVENUE_CATEGORY_CODE_systab(i)
,g_NLR_ORGANIZATION_ID_systab(i)
,g_EVENT_CLASSIFICATION_systab(i)
,g_SYS_LINK_FUNCTION_systab(i)
,g_PROJECT_ROLE_ID_systab(i)
,g_RESOURCE_CLASS_CODE_systab(i)
,g_MFC_COST_TYPE_ID_systab(i)
,g_RESOURCE_CLASS_FLAG_systab(i)
,g_FC_RES_TYPE_CODE_systab(i)
,g_INVENTORY_ITEM_ID_systab(i)
,g_ITEM_CATEGORY_ID_systab(i)
,g_PERSON_TYPE_CODE_systab(i)
,g_BOM_RESOURCE_ID_systab(i)
,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_systab(i),'PEOPLE',g_BOM_RESOURCE_ID_systab(i),NULL))
,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
decode(g_RESOURCE_CLASS_CODE_systab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_systab(i),NULL))
,g_NAMED_ROLE_systab(i)
--,g_NAMED_ROLE_ID_systab(i)
,g_INCURRED_BY_RES_FLAG_systab(i)
,g_RATE_BASED_FLAG_systab(i)
,g_TXN_TASK_ID_systab(i)
,g_TXN_WBS_ELE_VER_ID_systab(i)
,g_TXN_RBS_ELEMENT_ID_systab(i)
,g_TXN_PLAN_START_DATE_systab(i)
,g_TXN_PLAN_END_DATE_systab (i)
,g_PROJECT_ID
,g_BUDGET_VERSION_ID
FROM DUAL;
l_NumRecInserted := sql%Rowcount;
g_res_numRecInserted := l_NumRecInserted;
l_stage := 'Num of Records inserted ['||g_res_numRecInserted||']';
IF l_NumRecInserted > 0 AND
l_uncategorized_flag = 'N' THEN
/* update the resource class id for the inseted rows*/
--FORALL i IN g_txn_id_sqltab.FIRST .. g_txn_id_sqltab.LAST
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.resource_class_id = (select rc.resource_class_id
from pa_resource_classes_b rc
where rc.resource_class_code = tmp.resource_class_code)
WHERE tmp.resource_class_code is NOT NULL
;
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.expenditure_category = (select etc.expenditure_category
from pa_expenditure_types et
,pa_expenditure_categories etc
where et.expenditure_type = tmp.expenditure_type
and et.expenditure_category = etc.expenditure_category
and rownum = 1
)
WHERE tmp.expenditure_category is NULL
AND tmp.expenditure_type is NOT NULL ;
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.revenue_category = (SELECT evt.revenue_category_code
FROM pa_event_types evt
WHERE evt.event_type=tmp.event_type)
WHERE tmp.revenue_category IS NULL
AND tmp.event_type IS NOT NULL;
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.Revenue_category = (select et.Revenue_category_code
from pa_expenditure_types et
where et.expenditure_type = tmp.expenditure_type
and rownum = 1
)
WHERE tmp.Revenue_category is NULL
AND tmp.expenditure_type is NOT NULL ;
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.item_category_id = ( SELECT cat.CATEGORY_ID
FROM PA_RESOURCE_CLASSES_B classes
,PA_PLAN_RES_DEFAULTS cls
,MTL_ITEM_CATEGORIES cat
WHERE classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'
AND cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID
AND cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID
AND cat.ORGANIZATION_ID = tmp.organization_id
AND cat.INVENTORY_ITEM_ID = tmp.inventory_item_id
AND rownum = 1
)
WHERE tmp.item_category_id is NULL
AND tmp.inventory_item_id is NOT NULL;
* moved the select to cursor */
IF l_struct_ver_id is NOT NULL Then
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.TXN_WBS_ELEMENT_VERSION_ID = (Select pelm.element_version_id
From pa_proj_element_versions pelm
WHERE pelm.parent_structure_version_id = l_struct_ver_id
AND pelm.proj_element_id = tmp.txn_task_id
AND rownum = 1
)
WHERE tmp.TXN_WBS_ELEMENT_VERSION_ID is NULL
AND tmp.txn_task_id is NOT NULL;
/* added this update for bug fix:3854817 */
UPDATE pa_res_list_map_tmp1 tmp
SET tmp.fc_res_type_code = DECODE(tmp.EXPENDITURE_TYPE,null
,DECODE(tmp.EVENT_TYPE,null
,DECODE(tmp.EXPENDITURE_CATEGORY,null
,DECODE(tmp.REVENUE_CATEGORY,null,NULL,'REVENUE_CATEGORY')
,'EXPENDITURE_CATEGORY')
,'EVENT_TYPE')
,'EXPENDITURE_TYPE')
WHERE tmp.fc_res_type_code is NULL;
SELECT rsmap.txn_source_id
,rsmap.resource_list_member_id
,null -- rsmap.res_map_rejection_code
,rsmap.txn_source_id
,rsmap.resource_list_member_id
,null --rsmap.res_map_rejection_code
,null
,null
,null
,null
,null
,null
FROM pa_res_list_map_tmp4 rsmap;
SELECT rsmap.source_id
,rsmap.rbs_element_id
,rsmap.txn_accum_header_id
,null -- rsmap.rbs_map_rejection_code
,rsmap.source_id
,rsmap.rbs_element_id
,rsmap.txn_accum_header_id
,null -- rsmap.rbs_map_rejection_code
,null
,null
,null
,null
FROM pa_rbs_plans_out_tmp rsmap;
SELECT resmap.txn_source_id
,resmap.resource_list_member_id
,null -- resmap.res_map_rejection_code
,resmap.txn_source_id
,resmap.resource_list_member_id
,null --resmap.res_map_rejection_code
,rbsmap.rbs_element_id
,rbsmap.txn_accum_header_id
,null -- rbsmap.rbs_map_rejection_code
,rbsmap.rbs_element_id
,rbsmap.txn_accum_header_id
,null -- rbsmap.rbs_map_rejection_code
FROM pa_res_list_map_tmp4 resmap
,pa_rbs_plans_out_tmp rbsmap
WHERE resmap.txn_source_id = rbsmap.source_id ;
g_res_map_reject_code_sqltab.delete;
g_res_list_member_id_sqltab.delete;
g_txn_source_id_sqltab.delete;
g_rbs_map_reject_code_sqltab.delete;
g_rbs_element_id_sqltab.delete;
g_txn_accum_header_id_sqltab.delete;
select count(*)
into l_count
from pa_res_list_map_tmp4;
select count(*)
into l_count
from pa_rbs_plans_out_tmp;
FOR i IN ( select * from pa_rbs_plans_out_tmp ) LOOP
print_msg(g_debug_flag,'Value from rbs outtmp SourceId['||i.source_id||']RbsEleId['||i.rbs_element_id||']TxnAccum['||i.txn_accum_header_id||']');
select count(*)
into l_count
from pa_res_list_map_tmp4;
select count(*)
into l_count
from pa_rbs_plans_out_tmp;
l_stage := 'End of Populate populate_resmap_tmp api NumOfRecsInserted['||g_res_numRecInserted||']';
If g_res_numRecInserted > 0
AND g_call_res_list_mapping_api ='Y' Then
l_stage := 'Calling pa_resource_mapping.map_resource_list api';
l_stage := 'End of populate_rbsmap_tmp api for RBS NumOfRecsInserted['||g_rbs_numRecInserted||']';
If g_rbs_numRecInserted > 0 Then
/** bug fix 3658113 is reverted
l_stage := 'Calling pa_rbs_mapping.create_mapping_rules api';
If ( g_res_numRecInserted > 0 OR g_rbs_numRecInserted > 0 ) Then
/* After resource mapping read the values from out tmp tables and
* populate the plsqltables */
l_stage := 'Calling populate_resrbsmap_outTbls API';
l_stage := 'End of Populate populate_resmap_tmp api NumOfRecsInserted['||g_res_numRecInserted||']';
If g_res_numRecInserted > 0 AND
g_call_res_list_mapping_api = 'Y' Then
/* call resource mapping api */
l_stage := 'Calling pa_resource_mapping.map_resource_list api';
l_stage := 'End of populate_rbsmap_tmp api for RBS NumOfRecsInserted['||g_rbs_numRecInserted||']';
If g_rbs_numRecInserted > 0 Then
/** bug fix3658113 is reverted
l_stage := 'Calling pa_rbs_mapping.create_mapping_rules api';
If ( g_res_numRecInserted > 0 OR g_rbs_numRecInserted > 0 )Then
/* After resource mapping read the values from out tmp tables and
* populate the plsqltables */
l_stage := 'Calling populate_resrbsmap_outTbls API';
/* This API updates the new frozen RBS version on all affected projects.
* Befare Calling this API, user has to populate the following global temp Table
* with all the affected project Ids : pji_pjp_proj_batch_map
* The out param x_return_status will be 'S' in case of Success, 'E'- Error , 'U' - Unexpected Errors
*/
PROCEDURE Push_RBS_Version
(p_old_rbs_version_id IN NUMBER
,p_new_rbs_version_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY Number
,x_msg_data OUT NOCOPY Varchar2 ) IS
CURSOR cur_rbsVersions(p_worker_id Number,p_rbs_header_id NUMBER) IS -- Modified for Bug 6450168
SELECT fp.project_id project_id
,fp.fin_plan_option_level_code finplan_option_level
,fp.fin_plan_version_id budget_version_id
,fp.Rbs_version_id
,fp.proj_fp_options_id
,pp.segment1 project_name
,bv.version_name version_name
,fptyp.name plan_type_name
FROM pa_budget_versions bv
,pa_proj_fp_options fp
,pji_pjp_proj_batch_map rbs
,pa_projects_all pp
,pa_fin_plan_types_tl fptyp
,pa_rbs_versions_b rvb -- Added for Bug 6450168
WHERE bv.budget_version_id (+) = fp.fin_plan_version_id
AND nvl(bv.project_id,fp.project_id) = fp.project_id
ANd fp.project_id = rbs.project_id
AND fp.project_id = pp.project_id
AND fp.rbs_version_id = rvb.rbs_version_id -- Added for Bug 6450168
AND rvb.rbs_header_id = p_rbs_header_id -- Added for Bug 6450168
-- AND rbs.PROJECT_ACTIVE_FLAG = 'Y' --commented for bug 4579741
AND rbs.WORKER_ID = p_worker_id
AND fp.fin_plan_type_id = fptyp.fin_plan_type_id (+)
AND NVL(fptyp.language,userenv('LANG')) = userenv('LANG')
ORDER BY fp.project_id,bv.budget_version_id
FOR UPDATE OF fp.proj_fp_options_id,bv.budget_version_id ;
SELECT 'Y'
FROM dual
WHERE EXISTS (select null
from pa_resource_assignments ra
where ra.budget_version_id = p_budget_version_id);
SELECT rbs1.name old_rbs_name
,rbs2.name new_rbs_name
FROM pa_rbs_versions_v rbs1
,pa_rbs_versions_v rbs2
WHERE rbs1.rbs_version_id = p_old_rbs_version_id
AND rbs2.rbs_version_id = p_new_rbs_version_id;
/* For Each budget version Id call the RBS mapping api and update the
* the rbs_element_id from the tmp table on proj_fp_options, budget versions, and resource assignments
* table
*/
l_prev_project_id := NULL;
/* Bug fix: 3977666 As discussed with Vijay Ranganathan, The proj fp options must be updated first and then
* to call the RBS mapping api. So moving the code of updating pa_proj_fp_options at end to first */
/* once all the mapping is done for the project update the fp options at plan Type and project level and version level
*/
l_stage := 'Update pa proj fp options with RBS details: FpOptionId['||i.proj_fp_options_id||']BdgtVer['||i.budget_version_id||']';
UPDATE pa_proj_fp_options fp
SET fp.rbs_version_id = p_new_rbs_version_id
,fp.record_version_number = nvl(fp.record_version_number,0) +1
WHERE fp.project_id = i.Project_id
--AND fp.rbs_version_id = p_old_rbs_version_id -- Commented for Bug 6450168
AND fp.proj_fp_options_id = i.proj_fp_options_id;
print_msg(g_debug_flag,'NumberOfRowsUpdated['||sql%rowcount||']',l_proc_name);
l_txn_source_id_tab.delete;
l_res_list_member_id_tab.delete;
l_rbs_element_id_tab.delete;
l_txn_accum_header_id_tab.delete;
/* update the resource assignment table with the new rbs details */
If l_txn_source_id_tab.count > 0 AND l_return_status = 'S' Then
l_stage := 'Update Resource assignments with new RBS details';
UPDATE pa_resource_assignments ra
SET ra.rbs_element_id = NVL(l_rbs_element_id_tab(j),ra.rbs_element_id)
,ra.txn_accum_header_id = NVL(l_txn_accum_header_id_tab(j),ra.txn_accum_header_id)
WHERE ra.budget_version_id = i.budget_version_id
AND ra.project_id = i.project_id
AND ra.resource_assignment_id = l_txn_source_id_tab(j) ;
/* update the budget version recod version nubmer, so that any changes in the rbs version related to
* old budget version should be in synch with these changes
*/
l_stage := 'Update pa_budget_versions with recordVerNum:BdgtVer['||i.budget_version_id||']';
UPDATE pa_budget_versions bv
SET bv.record_version_number = nvl(bv.record_version_number,0) +1
WHERE bv.budget_version_id = i.budget_version_id;