[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* While inserting when we call this function then if 'N'
* is returned then proceed else throw an error.
*************************************************************/
P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
SELECT 'N'
INTO l_check_unique_res
FROM pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND alias = p_resource_alias
AND object_type = p_object_type
AND object_id = p_object_id
AND resource_list_member_id <>
nvl(p_resource_list_member_id,-99);
SELECT def.item_master_id
INTO l_master_organization_id
FROM pa_resource_classes_b cls,
pa_plan_res_defaults def
WHERE cls.resource_class_code = 'MATERIAL_ITEMS'
AND cls.resource_class_id = def.resource_class_id
AND def.object_type = 'CLASS';
SELECT primary_uom_code
INTO l_uom
FROM mtl_system_items_b items
WHERE items.inventory_item_id = p_inventory_item_id
AND items.organization_id = l_organization_id
AND ROWNUM = 1;
SELECT primary_uom_code
INTO l_uom
FROM mtl_system_items_b items
WHERE items.inventory_item_id = p_inventory_item_id
AND items.organization_id = l_master_organization_id;
SELECT 'Y'
INTO l_currency
FROM mtl_units_of_measure meas
WHERE meas.uom_code = l_uom
AND meas.uom_class = 'Currency';
SELECT unit_of_measure
INTO l_uom
FROM pa_expenditure_types et
WHERE et.expenditure_type = p_expenditure_type
AND ROWNUM = 1;
SELECT resource_class_code
FROM pa_resource_classes_b
WHERE resource_class_id = p_resource_class_id;
SELECT res_type_id,res_type_enabled_flag,
resource_class_flag
FROM pa_res_formats_b
WHERE res_format_id = p_res_format_id;
SELECT res_type_code
FROM pa_res_types_b
WHERE res_type_id = p_res_type_id;
SELECT meaning || ' '
INTO g_token
FROM pa_lookups
WHERE lookup_type = 'PA_PLANNING_RESOURCE'
AND lookup_code = 'PLANNING_RESOURCE';
SELECT resource_class_id
INTO l_res_class_id
FROM pa_resource_classes_b
WHERE resource_class_code = l_res_class_code;
SELECT 'Y'
INTO l_res_class_valid
FROM pa_resource_classes_b
WHERE resource_class_code = p_resource_class_code
AND resource_class_id = p_resource_class_id;
SELECT g_token ||
decode(l_fin_cat_name, NULL, NULL, '-' || l_fin_cat_name) ||
decode(l_org_name, NULL, NULL, '-' || l_org_name) ||
decode(l_supplier_name, NULL, NULL, '-' || l_supplier_name) ||
decode(l_role_name, NULL, NULL, '-' || l_role_name) ||
decode(l_inc_by_name, NULL, NULL, '-' || l_inc_by_name)
INTO g_token
FROM dual;
* Then Insert into pa_resources
* followed by insert into pa_resources_txn_attributes
***************************************************/
IF l_res_type = 'NAMED_PERSON' THEN
BEGIN
SELECT full_name
INTO l_person_name
FROM per_people_x
WHERE person_id = l_person_id;
* Insert into PA_RESOURCES and PA_RESOURCE_TXN_ATTRIBUTES
* Table. This is done by a call to
* pa_create_resource.Create_Resource procedure.
************************************************/
pa_create_resource.Create_Resource
(p_resource_name => l_person_name,
p_resource_type_Code => 'EMPLOYEE',
p_description => l_person_name,
p_unit_of_measure => l_res_uom, --NULL, /*Changed for bug#14337097*/
p_rollup_quantity_flag => l_rollup_qty_flag, --NULL, /*Changed for bug#14337097*/
p_track_as_labor_flag => l_track_as_labor_flag, --NULL, /*Changed for bug#14337097*/
p_start_date => SYSDATE,
p_end_date => NULL,
p_person_id => l_person_id,
p_job_id => NULL,
p_proj_organization_id => NULL,
p_vendor_id => NULL,
p_expenditure_type => NULL,
p_event_type => NULL,
p_expenditure_category => NULL,
p_revenue_category_code => NULL,
p_non_labor_resource => NULL,
p_system_linkage => NULL,
p_project_role_id => NULL,
p_resource_id => l_resource_id,
p_err_code => l_err_code,
p_err_stage => l_err_stage,
p_err_stack => l_err_stack);
* to to the Insert Into PA_RESOURCE_LIST_MEMBERS.
* If it is not Unique then we should display an error
* saying 'Planning resource already exists in this
* Planning resource list'
* If its = 'Y' dont insert. If it is = 'N' then insert.
********************************************************/
/*******************************************************
* Bug : 3486256
* Description : This fix has been done to fix the Duplicates issue.
* Earlier the NVL for incurred_by_res_flag
* used to check for 'B' but while inserting if
* the value was Null we were inserting 'N'
* Therefore it used to never find the dup record.
* We have now added NVL 'N' clause to help solve
* the issue.
**********************************************************/
BEGIN
Select 'Y'
Into l_unique_res_list
From pa_resource_list_members
Where resource_list_id = p_resource_list_id
And res_format_id = p_res_format_id
--Added the below 2 lines to check for the uniqueness
-- on a list/proj combination.
-- Removed NVL for performance tuning
And object_type = nvl(l_object_type,'DUMMY')
And object_id = nvl(l_object_id,-99)
-- Added resource class ID for performance
and resource_class_Id = l_res_class_id
And nvl(person_id, -99) = nvl(l_person_id, -99)
And nvl(organization_id, -99) = nvl(l_organization_id, -99)
And nvl(job_id, -99) = nvl(l_job_id, -99)
And nvl(vendor_id, -99) = nvl(l_vendor_id, -99)
-- Team Role Changes.
--And nvl(PROJECT_ROLE_ID, -99) = nvl(l_role_id, -99)
And nvl(inventory_item_id, -99) = nvl(l_inventory_item_id, -99)
And nvl(item_category_id, -99) = nvl(l_item_category_id, -99)
And nvl(bom_resource_id, -99) = nvl(l_bom_resource_id, -99)
And nvl(person_type_code, 'DUMMY') = nvl(l_person_type_code, 'DUMMY')
-- Team Role changes
And nvl(team_role, 'DUMMY') = nvl(l_team_role, 'DUMMY')
And nvl(incurred_by_res_flag, 'N') = nvl(l_incur_by_res_flag, 'N')
And nvl(incur_by_res_class_code, 'DUMMY') =
nvl(l_incur_by_res_class_code,'DUMMY')
And nvl(incur_by_role_id, -99) = nvl(l_incur_by_role_id, -99)
And nvl(expenditure_type,'DUMMY') = nvl(l_expenditure_type, 'DUMMY') --Verify This
And nvl(event_type, 'DUMMY') = nvl(l_event_type, 'DUMMY')
And nvl(non_labor_resource, 'DUMMY') =
nvl(l_non_labor_resource, 'DUMMY')
And nvl(expenditure_category, 'DUMMY')
= nvl(l_expenditure_category,'DUMMY')
And nvl(revenue_category, 'DUMMY') = nvl(l_revenue_category, 'DUMMY');
* Insert_row procedure.
* This value is got by call to Validate_Fin_Cat_For_WP
* Function, which takes in the p_fc_res_type_code
* and returns a 'Y' or 'N'.
* ************************************************/
l_wp_eligible_flag :=
PA_TASK_ASSIGNMENT_UTILS.Validate_Fin_Cat_For_WP(p_fc_res_type_code);
* Insert Into Pa_resource_list_members
************************************************/
IF l_res_list_member_id IS NULL THEN
SELECT pa_resource_list_members_s.NEXTVAL
INTO l_res_list_member_id
FROM dual;
* Call to Pa_Planning_Resource_pkg.insert_row
* Procedure, which will insert into the
* pa_resource_list_members table.
********************************************/
--Added for CBS bug13535688 & 13546557
/* l_expenditure_type:=p_expenditure_type;
pa_debug.write('Create_Planning_Resource: ' || g_module_name,'Calling pa_res_list_members_pkg.insert_row',1);
pa_res_list_members_pkg.insert_row
(p_resource_list_member_id => l_res_list_member_id,
p_resource_list_id => p_resource_list_id,
p_resource_id => l_resource_id,
p_resource_alias => p_resource_alias,
p_person_id => l_person_id,
p_job_id => l_job_id ,
p_organization_id => l_organization_id ,
p_vendor_id => l_vendor_id ,
p_expenditure_type => l_expenditure_type ,
p_event_type => l_event_type ,
p_non_labor_resource => l_non_labor_resource ,
p_expenditure_category => l_expenditure_category ,
p_revenue_category => l_revenue_category ,
p_role_id => l_role_id ,
p_resource_class_id => l_res_class_id ,
p_res_class_code => l_res_class_code ,
p_res_format_id => p_res_format_id ,
p_spread_curve_id => p_spread_curve_id ,
p_etc_method_code => p_etc_method_code ,
p_mfc_cost_type_id => p_mfc_cost_type_id ,
p_res_class_flag => l_res_class_flag ,
p_fc_res_type_code => p_fc_res_type_code ,
p_inventory_item_id => l_inventory_item_id ,
p_item_category_id => l_item_category_id ,
p_attribute_category => p_attribute_category ,
p_attribute1 => p_attribute1 ,
p_attribute2 => p_attribute2 ,
p_attribute3 => p_attribute3 ,
p_attribute4 => p_attribute4 ,
p_attribute5 => p_attribute5 ,
p_attribute6 => p_attribute6 ,
p_attribute7 => p_attribute7 ,
p_attribute8 => p_attribute8 ,
p_attribute9 => p_attribute9 ,
p_attribute10 => p_attribute10 ,
p_attribute11 => p_attribute11 ,
p_attribute12 => p_attribute12 ,
p_attribute13 => p_attribute13 ,
p_attribute14 => p_attribute14 ,
p_attribute15 => p_attribute15 ,
p_attribute16 => p_attribute16 ,
p_attribute17 => p_attribute17 ,
p_attribute18 => p_attribute18 ,
p_attribute19 => p_attribute19 ,
p_attribute20 => p_attribute20 ,
p_attribute21 => p_attribute21 ,
p_attribute22 => p_attribute22 ,
p_attribute23 => p_attribute23 ,
p_attribute24 => p_attribute24 ,
p_attribute25 => p_attribute25 ,
p_attribute26 => p_attribute26 ,
p_attribute27 => p_attribute27 ,
p_attribute28 => p_attribute28 ,
p_attribute29 => p_attribute29 ,
p_attribute30 => p_attribute30 ,
p_person_type_code => l_person_type_code,
p_bom_resource_id => l_bom_resource_id,
p_team_role => l_team_role,
p_incur_by_res_class_code => l_incur_by_res_class_code,
p_incur_by_role_id => l_incur_by_role_id,
p_incur_by_res_flag => l_incur_by_res_flag,
p_object_type => l_object_type,
p_object_id => l_object_id,
p_wp_eligible_flag => l_wp_eligible_flag,
p_unit_of_measure => l_uom,
p_expenditure_type_2 => p_expenditure_type_2, --Added for CBS bug13535688 & 13546557
x_msg_count => x_msg_count,
x_return_status => x_return_status ,
x_error_msg_data => x_error_msg_data );
pa_debug.write('Create_Planning_Resource: ' || g_module_name,'After calling pa_res_list_members_pkg.insert_row x_return_status '|| x_return_status ,1);
pa_debug.write('Create_Planning_Resource: ' || g_module_name,'After calling pa_res_list_members_pkg.insert_row x_error_msg_data '|| x_error_msg_data ,1);
* The below select would get the default values for
* spread curve id, etc method code and mfc cost type id
**************************************************/
BEGIN
SELECT spread_curve_id,
etc_method_code,
mfc_cost_type_id
INTO l_spread_curve_id,
l_etc_method_code,
l_mfc_cost_type_id
FROM Pa_Plan_Res_Defaults
WHERE resource_class_id = l_res_class_id
AND object_type = 'CLASS';
UPDATE pa_resource_list_members
SET spread_curve_id = DECODE(spread_curve_id,NULL,
l_spread_curve_id, spread_curve_id),
etc_method_code = DECODE(etc_method_code,NULL,
l_etc_method_code, etc_method_code)
WHERE resource_list_member_id = l_res_list_member_id;
SELECT res.res_type_code
INTO l_res_type_code
from pa_res_formats_b fmt,pa_res_types_b res
where fmt.res_type_id = res.res_type_id
and fmt.res_format_id = p_res_format_id;
UPDATE pa_resource_list_members
SET mfc_cost_type_id = DECODE(mfc_cost_type_id,NULL,
l_mfc_cost_type_id, mfc_cost_type_id)
WHERE resource_list_member_id = l_res_list_member_id;
UPDATE pa_resource_list_members
SET mfc_cost_type_id = NULL
WHERE resource_list_member_id = l_res_list_member_id;
* and then we need to update the table pa_resource_list_members
* with the derived value.
***************************************************/
IF p_resource_alias IS NULL
THEN
/***************************************************
* Get_Plan_Res_Combination
*************************************************/
PA_PLANNING_RESOURCE_UTILS. Get_Plan_Res_Combination(
P_Resource_List_Member_Id => l_res_list_member_id,
X_resource_alias => l_resource_alias,
X_Plan_Res_Combination => l_res_combo,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_error_msg_data);
UPDATE pa_resource_list_members
SET alias = l_resource_alias
WHERE resource_list_member_id = l_res_list_member_id;
* Procedure : Update_Planning_Resource
* Description : The purpose of this procedure is to
* Validate and update attributes on an existing
* planning resource for a resource list.
* It first checks for the Uniqueness of the
* resource list. If it is Unique then it updates
* the table PA_RESOURCE_LIST_MEMBERS
* with the values passed.
************************************/
PROCEDURE Update_Planning_Resource
(p_resource_list_id IN NUMBER,
p_resource_list_member_id IN NUMBER,
p_enabled_flag IN VARCHAR2,
p_resource_alias IN VARCHAR2 ,
p_spread_curve_id IN NUMBER DEFAULT NULL,
p_etc_method_code IN VARCHAR2 DEFAULT NULL,
p_mfc_cost_type_id IN NUMBER DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
p_attribute16 IN VARCHAR2 DEFAULT NULL,
p_attribute17 IN VARCHAR2 DEFAULT NULL,
p_attribute18 IN VARCHAR2 DEFAULT NULL,
p_attribute19 IN VARCHAR2 DEFAULT NULL,
p_attribute20 IN VARCHAR2 DEFAULT NULL,
p_attribute21 IN VARCHAR2 DEFAULT NULL,
p_attribute22 IN VARCHAR2 DEFAULT NULL,
p_attribute23 IN VARCHAR2 DEFAULT NULL,
p_attribute24 IN VARCHAR2 DEFAULT NULL,
p_attribute25 IN VARCHAR2 DEFAULT NULL,
p_attribute26 IN VARCHAR2 DEFAULT NULL,
p_attribute27 IN VARCHAR2 DEFAULT NULL,
p_attribute28 IN VARCHAR2 DEFAULT NULL,
p_attribute29 IN VARCHAR2 DEFAULT NULL,
p_attribute30 IN VARCHAR2 DEFAULT NULL,
p_record_version_number IN NUMBER,
p_expenditure_type_2 IN VARCHAR2 DEFAULT NULL, -- Added for Bug13535688 & 13546557 for CBS project
x_record_version_number OUT NOCOPY NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_error_msg_data OUT NOCOPY VARCHAR2 )
IS
l_resource_alias VARCHAR2(80);
SELECT meaning || ' '
INTO g_token
FROM pa_lookups
WHERE lookup_type = 'PA_PLANNING_RESOURCE'
AND lookup_code = 'PLANNING_RESOURCE';
SELECT object_type,object_id
INTO l_object_type,l_object_id
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
* If it is Unique we go ahead with the Update to
* pa_resource_list_members table.
* Update using the values passed.
*****************************************/
pa_res_list_members_pkg.update_row
(p_alias => l_resource_alias,
p_enabled_flag => p_enabled_flag,
p_resource_list_member_id => p_resource_list_member_id,
p_spread_curve_id => p_spread_curve_id,
p_etc_method_code => p_etc_method_code,
p_mfc_cost_type_id => p_mfc_cost_type_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_attribute21 => p_attribute21,
p_attribute22 => p_attribute22,
p_attribute23 => p_attribute23,
p_attribute24 => p_attribute24,
p_attribute25 => p_attribute25,
p_attribute26 => p_attribute26,
p_attribute27 => p_attribute27,
p_attribute28 => p_attribute28,
p_attribute29 => p_attribute29,
p_attribute30 => p_attribute30,
p_record_version_number => p_record_version_number,
p_expenditure_type_2 => p_expenditure_type_2, --Added for Bug13535688 & 13546557 for CBS project
x_return_status => x_return_status,
x_error_msg_data => x_error_msg_data,
x_msg_count => x_msg_count);
END Update_Planning_Resource;
* Procedure : Delete_Planning_Resource
* Description : The purpose of this procedure is to
* delete a planning resource if it is not
* being used, else disable it.
***************************************************/
PROCEDURE Delete_Planning_Resource(
p_resource_list_member_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_error_msg_data OUT NOCOPY VARCHAR2)
IS
l_exist_res_list VARCHAR2(30) := 'N';
SELECT meaning || ' '
INTO g_token
FROM pa_lookups
WHERE lookup_type = 'PA_PLANNING_RESOURCE'
AND lookup_code = 'PLANNING_RESOURCE';
SELECT resource_list_id, migration_code
INTO l_resource_list_id, l_migration_code
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
* or pa_project_assignments we cannot delete
* it. We will only set the enabled_flag = 'Y'.
**********************************************************/
SELECT 'Y'
INTO l_exist_res_list
FROM DUAL
WHERE EXISTS
(SELECT 'Y' from pa_resource_assignments
WHERE resource_list_member_id = p_resource_list_member_id
UNION
SELECT 'Y' from pa_project_assignments
WHERE resource_list_member_id = p_resource_list_member_id );
'pa_planning_resource_pvt.delete_planning_resource'
,p_procedure_name => PA_DEBUG.G_Err_Stack);
PA_GET_RESOURCE.delete_resource_list_member_ok(
l_resource_list_id => l_resource_list_id,
l_resource_list_member_id => p_resource_list_member_id,
x_err_code => x_msg_count,
x_err_stage => x_error_msg_data);
pa_res_list_members_pkg.Delete_row
(p_resource_list_member_id => p_resource_list_member_id,
p_exist_res_list => l_exist_res_list,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
END Delete_Planning_Resource;
SELECT control_flag
INTO l_control_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_destination_resource_list_id;
* The below select would be used to determine the
* uniqueness of the resource within the resource list.
*********************************************/
-- Bug 3642940
IF l_control_flag = 'Y' THEN
l_object_id := p_destination_resource_list_id;
DELETE FROM pa_res_members_temp;
DELETE FROM pa_res_member_id_temp;
INSERT INTO pa_res_member_id_temp
(resource_list_member_id,
order_id)
VALUES(p_src_res_list_member_id_tbl(i),
i);
INSERT INTO pa_res_members_temp
(resource_list_member_id ,
order_id ,
person_id ,
project_role_id ,
organization_id ,
job_id ,
vendor_id ,
inventory_item_id ,
item_category_id ,
bom_resource_id ,
person_type_code ,
-- named_role is holding team role
named_role ,
incurred_by_res_flag ,
incur_by_res_class_code ,
incur_by_role_id ,
expenditure_type ,
Event_type ,
non_labor_resource ,
expenditure_category ,
revenue_category ,
org_id ,
resource_class_id ,
-- Spread curve id is holding format id.
spread_curve_id )
SELECT /*+ ORDERED */
a.resource_list_member_id ,
b.order_id ,
a.person_id ,
a.project_role_id ,
a.organization_id ,
a.job_id ,
a.vendor_id ,
a.inventory_item_id ,
a.item_category_id ,
a.bom_resource_id ,
a.person_type_code ,
a.team_role ,
a.incurred_by_res_flag ,
a.incur_by_res_class_code ,
a.incur_by_role_id ,
a.expenditure_type ,
a.event_type ,
a.non_labor_resource ,
a.expenditure_category ,
a.revenue_category ,
NULL ,
a.resource_class_id ,
a.res_format_id
FROM pa_res_member_id_temp b,
pa_resource_list_members a
WHERE a.resource_list_member_id = b.resource_list_member_id;
UPDATE pa_res_members_temp rlmtmp
SET org_id = -1
WHERE NOT EXISTS (
SELECT 'Y'
FROM Pa_Plan_rl_formats
WHERE res_format_id = rlmtmp.spread_curve_id
AND resource_list_id = p_destination_resource_list_id
AND rownum = 1);
SELECT a.resource_list_member_id, -- matching rlm on dest
a.enabled_flag, -- enabled flag of match
b.resource_list_member_id -- matching rlm on source list
BULK COLLECT INTO l_bulk_resource_list_member_id,
l_bulk_enabled_flag,
l_old_resource_list_member_id
FROM pa_resource_list_members a,
pa_res_members_temp b
WHERE a.resource_list_id = p_destination_resource_list_id
-- To process only those RLM which has corr formats as that of source RL.
AND b.org_id IS NULL
AND a.res_format_id = b.spread_curve_id
AND a.object_type = l_object_type
And a.object_id = l_object_id
And a.resource_class_id = b.resource_class_id
And nvl(a.person_id, -99) = nvl(b.person_id, -99)
And nvl(a.organization_id, -99) =
nvl(b.organization_id, -99)
And nvl(a.job_id, -99) = nvl(b.job_id, -99)
And nvl(a.vendor_id, -99) = nvl(b.vendor_id, -99)
And nvl(a.inventory_item_id, -99) =
nvl(b.inventory_item_id, -99)
And nvl(a.item_category_id, -99) =
nvl(b.item_category_id, -99)
And nvl(a.bom_resource_id, -99) =
nvl(b.bom_resource_id, -99)
And nvl(a.person_type_code, 'DUMMY') =
nvl(b.person_type_code, 'DUMMY')
And nvl(a.team_role, 'DUMMY') =
nvl(b.named_role, 'DUMMY')
And nvl(a.incurred_by_res_flag, 'B') =
nvl(b.incurred_by_res_flag, 'B')
And nvl(a.incur_by_res_class_code, 'DUMMY') =
nvl(b.incur_by_res_class_code,'DUMMY')
And nvl(a.incur_by_role_id, -99) =
nvl(b.incur_by_role_id, -99)
And nvl(a.expenditure_type,'DUMMY') =
nvl(b.expenditure_type, 'DUMMY')
And nvl(a.event_type, 'DUMMY') = nvl(b.event_type, 'DUMMY')
And nvl(a.non_labor_resource, 'DUMMY') =
nvl(b.non_labor_resource, 'DUMMY')
And nvl(a.expenditure_category, 'DUMMY') =
nvl(b.expenditure_category,'DUMMY')
And nvl(a.revenue_category, 'DUMMY') =
nvl(b.revenue_category, 'DUMMY');
UPDATE pa_res_members_temp
SET org_id = DECODE(l_bulk_enabled_flag(j), 'Y' ,
l_bulk_resource_list_member_id(j),-1)
WHERE resource_list_member_id = l_old_resource_list_member_id(j);
l_bulk_resource_list_member_id.delete; -- initializing the table
SELECT DISTINCT resource_list_member_id
BULK COLLECT INTO l_bulk_resource_list_member_id
FROM pa_res_members_temp
WHERE org_id IS NULL;
SELECT pa_resource_list_members_s.NEXTVAL
INTO l_new_resource_list_member_id(i)
FROM dual;
INSERT INTO PA_RESOURCE_LIST_MEMBERS
( RESOURCE_LIST_MEMBER_ID ,
RESOURCE_LIST_ID ,
RESOURCE_ID ,
ALIAS ,
DISPLAY_FLAG ,
ENABLED_FLAG ,
TRACK_AS_LABOR_FLAG ,
PERSON_ID ,
JOB_ID ,
ORGANIZATION_ID ,
VENDOR_ID ,
EXPENDITURE_TYPE ,
EVENT_TYPE ,
NON_LABOR_RESOURCE ,
EXPENDITURE_CATEGORY ,
REVENUE_CATEGORY ,
PROJECT_ROLE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
RESOURCE_CLASS_ID ,
RESOURCE_CLASS_CODE ,
RES_FORMAT_ID ,
SPREAD_CURVE_ID ,
ETC_METHOD_CODE ,
MFC_COST_TYPE_ID ,
COPY_FROM_RL_FLAG ,
RESOURCE_CLASS_FLAG ,
FC_RES_TYPE_CODE ,
INVENTORY_ITEM_ID ,
ITEM_CATEGORY_ID ,
MIGRATION_CODE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
ATTRIBUTE16 ,
ATTRIBUTE17 ,
ATTRIBUTE18 ,
ATTRIBUTE19 ,
ATTRIBUTE20 ,
ATTRIBUTE21 ,
ATTRIBUTE22 ,
ATTRIBUTE23 ,
ATTRIBUTE24 ,
ATTRIBUTE25 ,
ATTRIBUTE26 ,
ATTRIBUTE27 ,
ATTRIBUTE28 ,
ATTRIBUTE29 ,
ATTRIBUTE30 ,
RECORD_VERSION_NUMBER ,
PERSON_TYPE_CODE ,
BOM_RESOURCE_ID ,
TEAM_ROLE ,
INCURRED_BY_RES_FLAG ,
INCUR_BY_RES_CLASS_CODE ,
INCUR_BY_ROLE_ID ,
WP_ELIGIBLE_FLAG ,
UNIT_OF_MEASURE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN )
SELECT
l_new_resource_list_member_id(k) ,
p_destination_resource_list_id ,
a.resource_id ,
a.alias ,
a.display_flag ,
a.enabled_flag ,
a.track_as_labor_flag ,
a.person_id ,
a.job_id ,
a.organization_id ,
a.vendor_id ,
a.expenditure_type ,
a.event_type ,
a.non_labor_resource ,
a.expenditure_category ,
a.revenue_category ,
a.project_role_id ,
'PROJECT' ,
p_destination_project_id ,
a.resource_class_id ,
a.resource_class_code ,
a.res_format_id ,
a.spread_curve_id ,
a.etc_method_code ,
a.mfc_cost_type_id ,
a.copy_from_rl_flag ,
a.resource_class_flag ,
a.fc_res_type_code ,
a.inventory_item_id ,
a.item_category_id ,
a.migration_code ,
a.attribute_category ,
a.attribute1 ,
a.attribute2 ,
a.attribute3 ,
a.attribute4 ,
a.attribute5 ,
a.attribute6 ,
a.attribute7 ,
a.attribute8 ,
a.attribute9 ,
a.attribute10 ,
a.attribute11 ,
a.attribute12 ,
a.attribute13 ,
a.attribute14 ,
a.attribute15 ,
a.attribute16 ,
a.attribute17 ,
a.attribute18 ,
a.attribute19 ,
a.attribute20 ,
a.attribute21 ,
a.attribute22 ,
a.attribute23 ,
a.attribute24 ,
a.attribute25 ,
a.attribute26 ,
a.attribute27 ,
a.attribute28 ,
a.attribute29 ,
a.attribute30 ,
a.record_version_number ,
a.person_type_code ,
a.bom_resource_id ,
a.team_role ,
a.incurred_by_res_flag ,
a.incur_by_res_class_code ,
a.incur_by_role_id ,
a.wp_eligible_flag ,
a.unit_of_measure ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.LOGIN_ID
FROM pa_resource_list_members a
WHERE a.resource_list_id = p_source_resource_list_id
AND a.resource_list_member_id =
l_bulk_resource_list_member_id(k);
UPDATE pa_res_members_temp
SET org_id = l_new_resource_list_member_id(x)
WHERE resource_list_member_id = l_bulk_resource_list_member_id(x);
UPDATE pa_res_members_temp
SET org_id = NULL
WHERE org_id = -1;
SELECT a.org_id
BULK COLLECT INTO x_dest_res_list_member_id_tbl
FROM pa_res_members_temp a,
pa_res_member_id_temp b
WHERE a.resource_list_member_id = b.resource_list_member_id
AND a.order_id = b.order_id
ORDER BY b.order_id;
DELETE FROM pa_res_members_temp;
DELETE FROM pa_res_member_id_temp;