[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT prta.person_id,
prta.job_id,
prta.organization_id,
prta.vendor_id,
prta.project_role_id,
prta.expenditure_type,
prta.event_type,
prta.expenditure_category,
prta.revenue_category,
prta.non_labor_resource,
prta.non_labor_resource_org_id,
prta.event_type_classification,
prta.system_linkage_function,
prta.resource_format_id,
prt.resource_type_id,
prt.resource_type_code
FROM PA_RESOURCE_TXN_ATTRIBUTES PRTA,
PA_RESOURCES PR,
PA_RESOURCE_TYPES PRT
WHERE prta.resource_id = pr.resource_id
AND pr.resource_id =P_RESOURCE_ID
AND pr.resource_type_id= prt.resource_type_id;
SELECT
group_resource_type_id
FROM
pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT 'x'
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id IS NULL
AND sort_order = p_sort_order;
SELECT
NVL(MAX(sort_order),0)+10
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id IS NULL
AND sort_order < 999999;
SELECT 'x'
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id IS NULL
AND alias = p_alias;
SELECT
resource_type_code
FROM
pa_resource_types_active_v
WHERE resource_type_id = l_resource_type_id;
/*SELECT
description
FROM
pa_revenue_categories_res_v
WHERE
revenue_category_code = p_resource_group;*/
SELECT
tmp.description
FROM (
SELECT
REVENUE_CATEGORY_CODE
,REVENUE_CATEGORY_M description
FROM PA_REVENUE_CATEGORIES_V RC
WHERE DECODE(PA_GET_RESOURCE.INCLUDE_INACTIVE_RESOURCES, 'Y', START_DATE_ACTIVE,TRUNC(SYSDATE)) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
) tmp
WHERE
tmp.revenue_category_code = p_resource_group;
SELECT
organization_name
FROM
pa_organizations_res_v
WHERE
organization_id = l_org_id ;
SELECT
pa_resource_list_members_s.NEXTVAL
FROM SYS.DUAL;
p_err_stage := ' Select group_resource_type_id from pa_resource_lists';
p_err_stage := ' Select resource_type_code from pa_resource_types';
p_err_stage := ' Select x from pa_resource_list_members';
p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
p_err_stage := 'Select description from pa_revenue_categories_res_v';
' Select organization_name from pa_organizations_res_v';
being inserted into resource_member_list table */
/* Comment starts ********************
PA_GET_RESOURCE.Get_Resource_Information
(p_resource_type_Code => l_resource_type_code,
p_resource_attr_value => p_resource_group,
p_unit_of_measure => l_uom,
p_Rollup_quantity_flag => l_rollup_qty_flag,
p_track_as_labor_flag => l_track_as_labor_flag,
p_err_code => l_err_code,
p_err_stage => p_err_stage,
p_err_stack => p_err_stack);
INSERT INTO pa_resource_list_members
(resource_list_id,
resource_list_member_id,
resource_id,
alias,
parent_member_id,
sort_order,
member_level,
display_flag,
enabled_flag,
track_as_labor_flag,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login,
PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
VENDOR_ID,
PROJECT_ROLE_ID,
EXPENDITURE_TYPE,
EVENT_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
NON_LABOR_RESOURCE,
NON_LABOR_RESOURCE_ORG_ID,
EVENT_TYPE_CLASSIFICATION,
SYSTEM_LINKAGE_FUNCTION,
RESOURCE_FORMAT_ID,
RESOURCE_TYPE_ID,
RESOURCE_TYPE_CODE
)
SELECT
p_resource_list_id,
l_resource_list_member_id,
l_resource_id,
l_alias,
NULL,
l_sort_order,
1,
NVL(p_display_flag,'Y'),
NVL(p_enabled_flag,'Y'),
l_track_as_labor_flag,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login,
l_person_id,
l_job_id,
l_organization_id,
l_vendor_id,
l_project_role_id,
l_expenditure_type,
l_event_type,
l_expenditure_category,
l_revenue_category,
l_nlr_resource,
l_nlr_res_org_id,
l_event_type_cls,
l_system_link_function,
l_resource_format_id,
l_resource_type_id,
l_res_type_code
FROM
sys.dual
WHERE NOT EXISTS
(SELECT 'x' FROM PA_RESOURCE_LIST_MEMBERS
WHERE resource_list_id = p_resource_list_id
AND resource_id = l_resource_id
AND parent_member_id IS NULL );
SELECT 'x' FROM
pa_resource_lists
WHERE NAME = p_resource_list_name;
SELECT
pa_resource_list_members_s.NEXTVAL
FROM SYS.DUAL;
SELECT fmt.res_format_id, fmt.resource_class_id, cls.resource_class_code
FROM pa_res_formats_b fmt, pa_resource_classes_b cls
WHERE fmt.resource_class_flag = 'Y'
AND fmt.resource_class_id = cls.resource_class_id;
SELECT def.spread_curve_id, def.etc_method_code --, def.mfc_cost_type_id
FROM pa_plan_res_defaults def
WHERE def.resource_class_id = p_resource_class_id
AND def.object_type = 'CLASS';
SELECT
group_resource_type_id
FROM
pa_resource_groups_valid_v
WHERE resource_group = p_group_resource_type;
SELECT pa_resource_lists_s.NEXTVAL
FROM
SYS.DUAL;
SELECT 1
FROM pa_jobs_v
WHERE job_group_id = p_job_group_id
AND ROWNUM = 1;
SELECT prt.resource_type_id,prt.resource_type_code
FROM pa_resources pr, pa_resource_types prt
WHERE prt.resource_type_code='UNCLASSIFIED'
AND pr.resource_type_id = prt.resource_type_id;
p_err_stage := 'Select x from pa_resource_lists ';
'Select group_resource_type_id from pa_resource_groups_valid_v ';
p_err_stage := 'Select pa_resource_lists_s.nextval from dual ';
p_err_stage := 'Select 1 from pa_jobs_v ';
p_err_stage := 'Insert into pa_resource_lists ';
* before inserting into the pa_resource_lists_all_bg
* table.
************************************************/
l_resource_list_name := substr(p_resource_list_name,0,58);
INSERT INTO pa_resource_lists_all_bg (
resource_list_id,
name,
business_group_id,
description,
public_flag,
group_resource_type_id,
start_date_active,
end_date_active,
uncategorized_flag,
job_group_id, --Added for Bug 2486405.
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login,
control_flag, -- Added by RM
use_for_wp_flag, -- Added by RM
migration_code, -- Added by RM
record_version_number -- Added by RM
)
VALUES
(l_resource_list_id ,
--p_resource_list_name,
l_resource_list_name, --Bug 3501039
NVL(p_business_group_id,fnd_profile.value('PER_BUSINESS_GROUP_ID')), -- MOAC Changes - get from HR profile
--NVL(p_description,p_resource_list_name),
NVL(p_description,l_resource_list_name), --Bug 3501039
NVL(p_public_flag,'Y'),
l_resource_type_id,
NVL(p_start_date,SYSDATE),
p_end_date,
'N',
l_job_group_id, --Added for Bug 2486405.
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login,
p_control_flag, -- Added by RM
p_use_for_wp_flag, -- Added by RM
p_migration_code, -- Added by RM
1 -- Added by RM
);
insert into pa_resource_lists_tl (
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RESOURCE_LIST_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
g_last_update_login,
g_creation_date,
g_created_by,
g_last_update_date,
g_last_updated_by,
L_RESOURCE_LIST_ID,
p_resource_list_name,
NVL(p_description,p_resource_list_name),
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from pa_resource_lists_tl T
where T.RESOURCE_LIST_ID = L_RESOURCE_LIST_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
p_err_stage := 'Insert into pa_resource_list_members ';
INSERT INTO pa_resource_list_members
(resource_list_id,
resource_list_member_id,
resource_id,
alias,
parent_member_id,
sort_order,
member_level,
display_flag,
enabled_flag,
track_as_labor_flag,
resource_type_id,
resource_type_code,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login )
VALUES (
l_resource_list_id,
pa_resource_list_members_s.NEXTVAL,
l_resource_id,
l_resource_name,
NULL,
9999999,
1,
'N',
'Y',
l_track_as_labor_flag,
l_resource_type_id,
l_resource_type_code,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login );
* and Migration_code = 'N' while doing this insert.
* *********************************************/
/*********************************************
* Bug : 3476765
* Desc : Defaulting the value of incurred_by_res_flag
* to 'N' while doing the insert.
*********************************************/
/**********************************************
* Bug - 3591751
* Desc - Defaulting the value of wp_eligible_flag
* to 'Y' while doing the insert.
***********************************************/
INSERT INTO pa_resource_list_members
(resource_list_id,
resource_list_member_id,
resource_id,
alias,
display_flag,
enabled_flag,
track_as_labor_flag,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login,
spread_curve_id,
etc_method_code,
mfc_cost_type_id,
object_type,
object_id,
res_format_id,
resource_class_flag,
resource_class_id,
resource_class_code,
Migration_code,
incurred_by_res_flag,
Record_version_number,
wp_eligible_flag,
--Bug 3636856
unit_of_measure
)
VALUES (
l_resource_list_id,
pa_resource_list_members_s.NEXTVAL,
-99,
initcap(replace(l_res_class_code, '_', ' ')),
'Y',
'Y',
decode(l_res_class_code, 'PEOPLE', 'Y', 'N'),
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login,
l_spread_curve_id,
l_etc_method_code,
NULL,
'RESOURCE_LIST',
l_resource_list_id,
l_format_id,
'Y',
l_res_class_id,
l_res_class_code,
'N',
'N',
1,
'Y',
--Bug 3636856
DECODE(l_res_class_code,'PEOPLE','HOURS','EQUIPMENT','HOURS',
'MATERIAL_ITEMS','DOLLARS','FINANCIAL_ELEMENTS','DOLLARS'));
PROCEDURE Update_Resource_List
(p_resource_list_name IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_start_date IN DATE DEFAULT NULL,
p_end_date IN DATE DEFAULT NULL,
p_job_group_id IN OUT NOCOPY NUMBER,
p_job_group_name IN VARCHAR2 DEFAULT NULL,
p_use_for_wp_flag IN VARCHAR2 DEFAULT NULL,
p_control_flag IN VARCHAR2 DEFAULT NULL,
p_migration_code IN VARCHAR2 DEFAULT NULL,
p_record_version_number IN OUT NOCOPY NUMBER,
p_resource_list_id IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS
/*************************************************************
* Bug : 3473679
* Description : Modified the below cursor to only pick up
* those records where the res_type_code
* is NAMED_PERSON, INVENTORY_ITEM, BOM_LABOR,
* BOM_EQUIPMENT, NON_LABOR_RESOURCE
* and the count is more than 1.
* Earlier we were not allowing the user to set the
* enabled flag to 'Y' if it was already being used
* irrespective of the format.
***********************************************************/
CURSOR chk_wp_change_allowed IS
select count(typ.res_type_code)-- , typ.res_type_code
from pa_plan_rl_formats prl,
pa_res_formats_b fmt,
pa_res_types_b typ
where prl.resource_list_id = p_resource_list_id
and prl.res_format_id = fmt.res_format_id
and fmt.res_type_id = typ.res_type_id
and typ.res_type_code in ('NAMED_PERSON', 'INVENTORY_ITEM', 'BOM_LABOR',
'BOM_EQUIPMENT', 'NON_LABOR_RESOURCE')
group by typ.res_type_code
having count(typ.res_type_code) > 1;
SELECT 'N'
FROM pa_resource_lists_all_bg rl
WHERE rl.resource_list_id = p_resource_list_id
AND rl.use_for_wp_flag <> p_use_for_wp_flag
--AND exists (select 'Y' from pa_resource_list_assignments rla
AND exists (select 'Y' from pa_resource_list_assignments_v rla
where rla.resource_list_id = rl.resource_list_id
and rla.use_for_wp_flag = 'Y');
SELECT 'N'
FROM pa_resource_lists_all_bg rl
WHERE rl.resource_list_id = p_resource_list_id
AND rl.control_flag <> p_control_flag
--AND exists (select 'Y' from pa_resource_list_assignments rla
AND exists (select 'Y' from pa_resource_list_assignments_v rla
where rla.resource_list_id = rl.resource_list_id);
SELECT 'N'
FROM pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND job_id IS NOT NULL;
SELECT job_group_id
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT use_for_wp_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT 'N'
FROM pa_resource_lists_all_bg rl
WHERE rl.resource_list_id = p_resource_list_id
AND rl.control_flag <> p_control_flag
AND rl.migration_code = 'M';
update pa_resource_lists_all_bg
set name = substr(nvl(p_resource_list_name, name),0,58),
description = p_description,
job_group_id = p_job_group_id,
start_date_active = nvl(p_start_date, start_date_active),
end_date_active = p_end_date, --Removed nvl for bug 3787913
last_updated_by = g_last_updated_by,
last_update_date = g_last_update_date,
last_update_login = g_last_update_login,
control_flag = p_control_flag,
use_for_wp_flag = p_use_for_wp_flag,
record_version_number = record_version_number + 1
where resource_list_id = p_resource_list_id
and nvl(record_version_number, 0) = nvl(p_record_version_number, 0);
update pa_resource_lists_tl set
NAME = nvl(p_resource_list_name, name),
DESCRIPTION = P_DESCRIPTION,
LAST_UPDATE_DATE = g_last_update_date,
LAST_UPDATED_BY = g_last_updated_by,
LAST_UPDATE_LOGIN = g_last_update_login,
SOURCE_LANG = userenv('LANG')
where resource_list_id = p_resource_list_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
END Update_Resource_List;
SELECT 'N' FROM
pa_resource_lists_tl
WHERE NAME = p_resource_list_name
AND LANGUAGE = userenv('LANG')
AND ((resource_list_id <> p_resource_list_id
AND p_resource_list_id IS NOT NULL)
OR p_resource_list_id IS NULL);
SELECT 'N' FROM
pa_resource_lists_all_bg
WHERE NAME = p_resource_list_name
AND ((resource_list_id <> p_resource_list_id
AND p_resource_list_id IS NOT NULL)
OR p_resource_list_id IS NULL);
SELECT
resource_class_code
FROM
pa_resource_types_active_v
WHERE
resource_type_code = p_resource_type_code;
SELECT
resource_format_id
FROM
pa_resource_formats
WHERE person_id_flag = l_person_id_flag
AND job_id_flag = l_job_id_flag
AND organization_id_flag = l_proj_organization_id_flag
AND vendor_id_flag = l_vendor_id_flag
AND expenditure_type_flag = l_expenditure_type_flag
AND event_type_flag = l_event_type_flag
AND expenditure_category_flag = l_expenditure_category_flag
AND revenue_category_flag = l_revenue_category_code_flag
AND non_labor_resource_flag = l_non_labor_resource_flag
AND non_labor_resource_org_id_flag = l_non_labor_res_org_id_flag
AND project_role_id_flag = l_project_role_id_flag;
SELECT pa_resource_txn_attributes_s.NEXTVAL
FROM SYS.DUAL;
p_err_stage := 'Select resource_class_code from pa_resource_types_active_v';
p_err_stage := 'Select resource_format_id from pa_resource_formats ';
p_err_stage := 'Select pa_resource_txn_attributes_s.nextval from sys.dual ';
p_err_stage := 'Insert into pa_resource_txn_attributes ';
INSERT INTO pa_resource_txn_attributes
(
resource_txn_attribute_id,
resource_id ,
person_id,
job_id ,
organization_id,
vendor_id,
expenditure_type,
event_type,
non_labor_resource ,
expenditure_category,
revenue_category ,
non_labor_resource_org_id ,
event_type_classification,
system_linkage_function ,
resource_format_id ,
last_updated_by ,
last_update_date,
creation_date,
created_by,
last_update_login,
project_role_id
)
VALUES
(l_resource_txn_attribute_id,
p_resource_id,
l_person_id,
l_job_id,
l_proj_organization_id,
l_vendor_id,
l_expenditure_type,
l_event_type,
p_non_labor_resource,
l_expenditure_category,
l_revenue_category_code,
NULL,
NULL,
p_system_linkage,
l_resource_format_id,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login,
l_project_role_id
);
SELECT
group_resource_type_id
FROM
pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT
resource_type_code
FROM
pa_resource_types_active_v
WHERE resource_type_id = l_grouped_resource_type_id;
/*SELECT
description
FROM
pa_revenue_categories_res_v
WHERE
revenue_category_code = l_revenue_category_code;*/
SELECT
tmp.description
FROM (
SELECT
REVENUE_CATEGORY_CODE
,REVENUE_CATEGORY_M description
FROM PA_REVENUE_CATEGORIES_V RC
WHERE DECODE(PA_GET_RESOURCE.INCLUDE_INACTIVE_RESOURCES, 'Y', START_DATE_ACTIVE,TRUNC(SYSDATE)) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
) tmp
WHERE
tmp.revenue_category_code = l_revenue_category_code;
SELECT
organization_name
FROM
pa_organizations_res_v
WHERE
organization_id = l_org_id ;
SELECT
'X'
FROM
pa_jobs_v
WHERE job_group_id = p_job_group_id
AND job_id = p_job_id;
p_err_stage := 'Select group_resource_type_id from pa_resource_lists ';
p_err_stage := 'Select resource_type_code from pa_resource_types ';
' Select organization_name from pa_organizations_res_v';
SELECT
resource_type_id
FROM
pa_resource_types_active_v
WHERE resource_type_code = p_resource_type_code;
SELECT
pa_resources_s.NEXTVAL
FROM
SYS.DUAL;
p_err_stage := 'Select resource_type_id from pa_resource_types_active_v ';
p_err_stage := 'Insert into pa_resources ';
INSERT INTO pa_resources
(resource_id,
name,
description,
resource_type_id,
unit_of_measure,
rollup_quantity_flag,
track_as_labor_flag,
start_date_active,
end_date_active,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login )
VALUES
(l_resource_id,
p_resource_name,
p_description,
l_resource_type_id,
p_unit_of_measure,
p_rollup_quantity_flag,
p_track_as_labor_flag,
NVL(p_start_date,SYSDATE),
p_end_date,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login );
SELECT
group_resource_type_id
FROM
pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT
resource_type_code
FROM
pa_resource_types
WHERE resource_type_id = l_grouped_resource_type_id;
SELECT
rta.expenditure_category
FROM
pa_resource_list_members rlm,
pa_resources re,
pa_resource_txn_attributes rta
WHERE rlm.resource_list_member_id = p_parent_member_id
AND rlm.resource_id = re.resource_id
AND re.resource_id = rta.resource_id;
SELECT
rta.revenue_category
FROM
pa_resource_list_members rlm,
pa_resources re,
pa_resource_txn_attributes rta
WHERE rlm.resource_list_member_id = p_parent_member_id
AND rlm.resource_id = re.resource_id
AND re.resource_id = rta.resource_id;
SELECT
expenditure_type
FROM
pa_expenditure_types_res_v
WHERE expenditure_type = p_expenditure_type
AND expenditure_category = l_exp_category;
SELECT
expenditure_type
FROM
pa_expenditure_types_res_v
WHERE expenditure_type = p_expenditure_type
AND revenue_category_code = l_revenue_category_code;
SELECT
event_type
FROM
pa_event_types_res_v
WHERE event_type = p_event_type
AND revenue_category_code = l_revenue_category_code;
SELECT 'x'
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id = p_parent_member_id
AND sort_order = p_sort_order;
SELECT 'x'
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id IS NULL
AND sort_order = p_sort_order;
SELECT
NVL(MAX(sort_order),0)+10
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id = p_parent_member_id
AND sort_order < 999999;
SELECT
NVL(MAX(sort_order),0)+10
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id IS NULL
AND sort_order < 999999;
SELECT 'x'
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id = p_parent_member_id
AND alias = p_alias;
SELECT 'x'
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND parent_member_id IS NULL
AND alias = p_alias;
SELECT track_as_labor_flag
FROM
pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND resource_list_member_id = p_parent_member_id;
SELECT
pa_resource_list_members_s.NEXTVAL
FROM SYS.DUAL;
SELECT prta.person_id,
prta.job_id,
prta.organization_id,
prta.vendor_id,
prta.project_role_id,
prta.expenditure_type,
prta.event_type,
prta.expenditure_category,
prta.revenue_category,
prta.non_labor_resource,
prta.non_labor_resource_org_id,
prta.event_type_classification,
prta.system_linkage_function,
prta.resource_format_id,
prt.resource_type_id,
prt.resource_type_code
FROM PA_RESOURCE_TXN_ATTRIBUTES PRTA,
PA_RESOURCES PR,
PA_RESOURCE_TYPES PRT
WHERE prta.resource_id = pr.resource_id
AND pr.resource_id =P_RESOURCE_ID
AND pr.resource_type_id= prt.resource_type_id;
SELECT prt.resource_type_id,prt.resource_type_code
FROM pa_resources pr, pa_resource_types prt
WHERE prt.resource_type_code='UNCLASSIFIED'
AND pr.resource_type_id = prt.resource_type_id;
p_err_stage := ' Select group_resource_type_id from pa_resource_lists';
p_err_stage := ' Select resource_type_code from pa_resource_types';
p_err_stage := 'Select expenditure_category from ....';
p_err_stage := 'Select revenue_category_code from ....';
'Select expenditure_type from pa_expenditure_types_res_v';
'Select event_type from pa_event_types_res_v';
p_err_stage := ' Select x from pa_resource_list_members';
p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
p_err_stage := ' Select x from pa_resource_list_members - alias';
'Select track_as_labor_flag from pa_resource_list_members';
being inserted into resource_member_list table */
/* Comment starts ********************
IF p_resource_type_code = 'EMPLOYEE' THEN
l_attr_value := to_char(p_person_id );
p_err_stage := 'Select pa_resource_list_members_s.nextval ';
p_err_stage := 'Insert into pa_resource_list_members ';
INSERT INTO pa_resource_list_members
(resource_list_id,
resource_list_member_id,
resource_id,
alias,
parent_member_id,
sort_order,
member_level,
display_flag,
enabled_flag,
track_as_labor_flag,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login,
PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
VENDOR_ID,
PROJECT_ROLE_ID,
EXPENDITURE_TYPE,
EVENT_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
NON_LABOR_RESOURCE,
NON_LABOR_RESOURCE_ORG_ID,
EVENT_TYPE_CLASSIFICATION,
SYSTEM_LINKAGE_FUNCTION,
RESOURCE_FORMAT_ID,
RESOURCE_TYPE_ID,
RESOURCE_TYPE_CODE
)
VALUES (
p_resource_list_id,
l_resource_list_member_id,
l_resource_id,
l_alias,
p_parent_member_id,
l_sort_order,
DECODE(p_parent_member_id,NULL,1,2),
NVL(p_display_flag,'Y'),
NVL(p_enabled_flag,'Y'),
l_new_track_as_labor_flag,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login,
l_person_id,
l_job_id,
l_organization_id,
l_vendor_id,
l_project_role_id,
l_expenditure_type,
l_event_type,
l_expenditure_category,
l_revenue_category,
l_nlr_resource,
l_nlr_res_org_id,
l_event_type_cls,
l_system_link_function,
l_resource_format_id,
l_resource_type_id,
l_res_type_code
);
p_err_stage := 'Insert into pa_resource_list_members ';
INSERT INTO pa_resource_list_members
(resource_list_id,
resource_list_member_id,
resource_id,
alias,
parent_member_id,
sort_order,
member_level,
display_flag,
enabled_flag,
track_as_labor_flag,
resource_type_id,
resource_type_code,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login )
VALUES (
p_resource_list_id,
pa_resource_list_members_s.NEXTVAL,
l_resource_id,
l_resource_name,
p_parent_member_id,
999999,
2,
'N',
'Y',
l_track_as_labor_flag,
l_resource_type_id,
l_resource_type_code,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login );
SELECT resource_list_id,
name, description, public_flag, group_resource_type_id,
start_date_active, end_date_active, uncategorized_flag,
control_flag,migration_code,use_for_wp_flag,
last_updated_by, last_update_date, creation_date,
created_by, last_update_login
FROM pa_resource_lists_all_bg
WHERE business_group_id = -3113;
X_err_stage := 'Select seeded_resource_list From PA_RESOURCE_LISTS';
X_err_stage := 'Select pa_resource_lists_s.NEXTVAL From Dual';
SELECT pa_resource_lists_s.NEXTVAL
INTO X_resource_list_id
FROM sys.dual;
X_err_stage := 'Insert Into PA_RESOURCE_LISTS';
INSERT INTO pa_resource_lists_all_bg (
resource_list_id
, name
, business_group_id
, description
, public_flag
, group_resource_type_id
, start_date_active
, end_date_active
, uncategorized_flag
, control_flag
, use_for_wp_flag
, migration_code
, last_updated_by
, last_update_date
, creation_date
, created_by
, last_update_login
, record_version_number )
SELECT
X_resource_list_id
, eachRL.name
, NVL(X_business_group_id, fnd_profile.value('PER_BUSINESS_GROUP_ID'))
, eachRL.description
, eachRL.public_flag
, eachRL.group_resource_type_id
, eachRL.start_date_active
, eachRL.end_date_active
, eachRL.uncategorized_flag
, eachRL.control_flag
, eachRL.use_for_wp_flag
, eachRL.migration_code
, X_user_id
, SYSDATE
, SYSDATE
, X_user_id
, X_login_id
, 1 -- record version number
FROM
sys.dual
WHERE NOT EXISTS (
SELECT NULL
FROM pa_resource_lists rl
WHERE business_group_id =
NVL(X_business_group_id,
fnd_profile.value('PER_BUSINESS_GROUP_ID'))
AND rl.name = eachRL.name );
INSERT into pa_resource_lists_tl (
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
resource_list_id,
name,
description,
language,
source_lang
) SELECT
x_login_id,
sysdate,
x_user_id,
sysdate,
X_user_id,
x_resource_list_id,
eachRL.name,
NVL(eachRL.description,eachRL.name),
L.LANGUAGE_CODE,
userenv('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from pa_resource_lists_tl T
where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
X_err_stage := 'Insert Into PA_RESOURCE_LIST_MEMBERS';
INSERT INTO pa_resource_list_members (
resource_list_member_id
, resource_list_id
, resource_id
, alias
, parent_member_id
, sort_order
, member_level
, display_flag
, enabled_flag
, track_as_labor_flag
, last_updated_by
, last_update_date
, creation_date
, created_by
, last_update_login
, RESOURCE_TYPE_ID
, RESOURCE_TYPE_CODE
, object_type
, object_id
, RESOURCE_CLASS_ID
, RES_FORMAT_ID
, SPREAD_CURVE_ID
, ETC_METHOD_CODE
, RES_TYPE_CODE
, RESOURCE_CLASS_CODE
, RESOURCE_CLASS_FLAG
, MIGRATION_CODE
, RECORD_VERSION_NUMBER
, INCURRED_BY_RES_FLAG
, WP_ELIGIBLE_FLAG
, UNIT_OF_MEASURE
)
SELECT pa_resource_list_members_s.NEXTVAL
, X_resource_list_id
, rlm.resource_id
, rlm.alias
, rlm.parent_member_id
, rlm.sort_order
, rlm.member_level
, rlm.display_flag
, rlm.enabled_flag
, rlm.track_as_labor_flag
, X_user_id
, SYSDATE
, SYSDATE
, X_user_id
, X_login_id
--For bug 4025330
, decode(rlm.resource_id, -99, null,rlm.resource_type_id)
, decode(rlm.resource_id, -99, null,rlm.resource_type_code)
, rlm.object_type
, X_resource_list_id -- object ID has to be the new resource list ID - that's why we can't copy over the object ID.
, rlm.RESOURCE_CLASS_ID
, rlm.RES_FORMAT_ID
, rlm.SPREAD_CURVE_ID
, rlm.ETC_METHOD_CODE
, rlm.RES_TYPE_CODE
, rlm.RESOURCE_CLASS_CODE
, rlm.RESOURCE_CLASS_FLAG
, rlm.MIGRATION_CODE
, 1 -- record version number
, rlm.INCURRED_BY_RES_FLAG
, rlm.WP_ELIGIBLE_FLAG
, rlm.UNIT_OF_MEASURE
FROM pa_resource_list_members rlm
WHERE rlm.resource_list_id = eachRL.resource_list_id
--begin:bug:5925973:Implementing the logic to restrict the creation of resource list member records when new OU ('Implementation Options' form of the OU)is created for an existing Business Group.
--The Check:check whether the 'X_RESOURCE_LIST_ID' exists in the 'pa_resource_lists_all_bg' table before inserting records into 'pa_resource_list_members' table
AND EXISTS
(SELECT NULL
FROM pa_resource_lists_all_bg T1
WHERE T1.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
PROCEDURE Delete_Plan_Res_List (p_resource_list_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR get_members (p_res_list_id in NUMBER) IS
SELECT resource_list_member_id
FROM pa_resource_list_members
WHERE resource_list_id = p_res_list_id;
PA_GET_RESOURCE.delete_resource_list_ok(
p_resource_list_id,
'Y',
l_err_code,
x_msg_data);
PA_GET_RESOURCE.delete_resource_list_member_ok(
p_resource_list_id,
l_res_list_member_id,
l_err_code,
x_msg_data);
delete from pa_resource_list_members
where resource_list_id = p_resource_list_id;
delete from pa_plan_rl_formats
where resource_list_id = p_resource_list_id;
delete from pa_resource_lists_tl
where resource_list_id = p_resource_list_id;
delete from pa_resource_lists_all_bg
where resource_list_id = p_resource_list_id;
END Delete_Plan_Res_List;
* While inserting when we call this function then if 'N'
* is returned then proceed else throw an error.
*************************************************************/
FUNCTION Check_pl_alias_unique(
p_resource_list_id IN VARCHAR2,
p_resource_alias IN VARCHAR2,
p_resource_list_member_id IN VARCHAR2)
RETURN VARCHAR2
IS
l_check_unique_res varchar2(30) := 'Y';
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 resource_list_member_id <>
nvl(p_resource_list_member_id,-99);
delete from pa_resource_lists_tl T
where not exists
(select NULL
from PA_RESOURCE_LISTS_ALL_BG B
where B.RESOURCE_LIST_ID = T.resource_list_id
);
update pa_resource_lists_tl T set (
NAME,
DESCRIPTION
) = (select
B.NAME,
B.DESCRIPTION
from pa_resource_lists_tl b
where B.RESOURCE_LIST_ID = T.RESOURCE_LIST_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.RESOURCE_LIST_ID,
T.LANGUAGE
) in (select
SUBT.RESOURCE_LIST_ID,
SUBT.LANGUAGE
from pa_resource_lists_tl SUBB, pa_resource_lists_tl SUBT
where SUBB.RESOURCE_LIST_ID = SUBT.RESOURCE_LIST_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME <> SUBT.NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into pa_resource_lists_tl (
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RESOURCE_LIST_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
B.LAST_UPDATE_LOGIN,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.RESOURCE_LIST_ID,
B.NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from pa_resource_lists_tl B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from pa_resource_lists_tl T
where T.RESOURCE_LIST_ID = B.RESOURCE_LIST_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
* The below select would check if the resource
* list is centrally controlled or not.
* If it is Centrally Controlled then we cannot associate
* it to a project.
************************************************/
/**************************************************
* If the Project ID is passed in as NULL then Raise an
* Unexpected error and Return.
***************************************************/
IF p_project_id IS NULL THEN
X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
SELECT Control_flag
INTO l_central_control
FROM pa_resource_lists_all_bg
where resource_list_id = p_resource_list_id;
* This select would check for the existance of recource
* members in the pa_resource_list_members table which have the same
* resource_list_id and project_id.
* *****************************************************/
BEGIN
SELECT 'Y'
INTO l_exist_record
FROM dual --For perf bug 4067435
WHERE EXISTS (SELECT resource_list_id,object_id
FROM pa_resource_list_members
WHERE object_id = p_project_id
AND object_type = 'PROJECT'
AND resource_list_id = p_resource_list_id)
AND ROWNUM = 1;
* Desc - While inserting into the Pa_resource_list_members
* insert value into the wp_eligible_flag as well.
*****************************************************/
/**********************************************************************
* Bug - 3597011
* Desc - While inserting we need to check for enabled_flag <> N
***********************************************************************/
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,
--3591751
WP_ELIGIBLE_FLAG,
--Bug 3637045
UNIT_OF_MEASURE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
pa_resource_list_members_s.NEXTVAL,
a.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_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,
--3591751
a.wp_eligible_flag,
--Bug 3637045
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_resource_list_id
AND a.object_id = p_resource_list_id
AND a.object_type = 'RESOURCE_LIST'
-- 3597011
and a.enabled_flag <> 'N';
SELECT
public_flag,
group_resource_type_id,
uncategorized_flag,
business_group_id,
adw_notify_flag,
resource_list_type,
--'N' --Bug 3695679
migration_code --Bug 3710189
INTO
p_public_flag,
p_group_resource_type_id,
p_uncategorized_flag,
p_business_group_id,
p_adw_notify_flag,
p_resource_list_type,
p_migration_code
FROM
pa_resource_lists_all_bg
WHERE
resource_list_id=p_parent_resource_list_id;
--Call Insert_row which inserts a row into PA_RESOURCE_LISTS_ALL_BG table
PA_Resource_List_tbl_Pkg.Insert_Row(
p_name,
p_description,
p_public_flag,
p_group_resource_type_id,
p_start_date_active,
p_end_date_active,
p_uncategorized_flag,
p_business_group_id,
p_adw_notify_flag,
p_job_group_id,
p_resource_list_type,
p_control_flag,
p_use_for_wp_flag,
p_migration_code,
x_resource_list_id,
x_return_status,
x_msg_data
);
INSERT into pa_resource_lists_tl (
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
resource_list_id,
name,
description,
language,
source_lang
) SELECT
fnd_global.login_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
x_resource_list_id,
p_name,
NVL(p_description,p_name),
L.LANGUAGE_CODE,
userenv('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from pa_resource_lists_tl T
where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
INSERT INTO pa_plan_rl_formats
(SELECT
Pa_Plan_RL_Formats_S.nextval,
X_Resource_List_Id,
res_format_id,
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM
pa_plan_rl_formats
WHERE
resource_list_id=p_parent_resource_list_id
AND p_migration_code IN ('N','M')); --Bug 3710189
Delete
From Pa_Rbs_Elements_Temp;
Insert Into Pa_Rbs_Elements_Temp(
New_Element_Id,
Old_Element_Id,
Old_Parent_Element_Id,
New_Parent_Element_Id )
(Select
Pa_resource_list_members_S.NextVal,
resource_list_member_id,
Parent_member_Id,
Null
From Pa_resource_list_members
Where resource_list_id = p_parent_resource_list_id
and (object_type = 'RESOURCE_LIST' OR object_type is NULL)
--don't want to copy proj specific resources
and enabled_flag <> 'N' );
--Update the parent member ID for the new child elements:
Update Pa_Rbs_Elements_Temp Tmp1
Set New_Parent_Element_Id =
(Select New_Element_Id
From Pa_Rbs_Elements_Temp Tmp2
Where Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
* Desc - While inserting we need to check for enabled_flag <> N
***********************************************************************/
-- hr_utility.trace('before copy members insert');
INSERT INTO pa_resource_list_members
( resource_list_member_id,
RESOURCE_LIST_ID,
RESOURCE_ID,
ALIAS ,
PARENT_MEMBER_ID,
SORT_ORDER ,
MEMBER_LEVEL,
DISPLAY_FLAG ,
ENABLED_FLAG ,
TRACK_AS_LABOR_FLAG,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login,
ADW_NOTIFY_FLAG,
FUNDS_CONTROL_LEVEL_CODE,
PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
VENDOR_ID,
EXPENDITURE_TYPE,
EVENT_TYPE,
NON_LABOR_RESOURCE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
NON_LABOR_RESOURCE_ORG_ID,
EVENT_TYPE_CLASSIFICATION,
SYSTEM_LINKAGE_FUNCTION,
PROJECT_ROLE_ID,
RESOURCE_FORMAT_ID,
RESOURCE_TYPE_ID,
RESOURCE_TYPE_CODE,
OBJECT_TYPE,
--3596702
object_id,
RES_FORMAT_ID,
SPREAD_CURVE_ID,
ETC_METHOD_CODE,
MFC_COST_TYPE_ID,
PERSON_TYPE_CODE,
RES_TYPE_CODE,
RESOURCE_CLASS_CODE,
RESOURCE_CLASS_ID,
RESOURCE_CLASS_FLAG,
FC_RES_TYPE_CODE,
BOM_RESOURCE_ID,
INVENTORY_ITEM_ID,
ITEM_CATEGORY_ID,
TEAM_ROLE,
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,
INCURRED_BY_RES_FLAG,
INCUR_BY_RES_CLASS_CODE,
INCUR_BY_ROLE_ID,
COPY_FROM_RL_FLAG,
WP_ELIGIBLE_FLAG,
--Bug 3636926
UNIT_OF_MEASURE)
--MIGRATED_RBS_ELEMENT_ID)
SELECT /*+ use_nl (tmp, a) */ --For perf bug 4067435
Tmp.New_Element_Id,
X_RESOURCE_LIST_ID,
a.RESOURCE_ID,
a.ALIAS ,
Tmp.New_Parent_Element_Id,
a.SORT_ORDER ,
a.MEMBER_LEVEL,
a.DISPLAY_FLAG ,
a.ENABLED_FLAG ,
a.TRACK_AS_LABOR_FLAG,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
a.ADW_NOTIFY_FLAG,
a.FUNDS_CONTROL_LEVEL_CODE,
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.NON_LABOR_RESOURCE_ORG_ID,
a.EVENT_TYPE_CLASSIFICATION,
a.SYSTEM_LINKAGE_FUNCTION,
a.PROJECT_ROLE_ID,
a.RESOURCE_FORMAT_ID,
a.RESOURCE_TYPE_ID,
a.RESOURCE_TYPE_CODE,
a.OBJECT_TYPE,
--3596702
--X_RESOURCE_LIST_ID,
decode(a.object_type, 'RESOURCE_LIST', X_RESOURCE_LIST_ID, NULL),
a.RES_FORMAT_ID,
a.SPREAD_CURVE_ID,
a.ETC_METHOD_CODE,
a.MFC_COST_TYPE_ID,
a.PERSON_TYPE_CODE,
a.RES_TYPE_CODE,
a.RESOURCE_CLASS_CODE,
a.RESOURCE_CLASS_ID,
a.RESOURCE_CLASS_FLAG,
a.FC_RES_TYPE_CODE,
a.BOM_RESOURCE_ID,
a.INVENTORY_ITEM_ID,
a.ITEM_CATEGORY_ID,
a.TEAM_ROLE,
--'N',--Bug 3695679
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,
1,
a.INCURRED_BY_RES_FLAG,
a.INCUR_BY_RES_CLASS_CODE,
a.INCUR_BY_ROLE_ID,
a.COPY_FROM_RL_FLAG,
a.WP_ELIGIBLE_FLAG,
-- Bug 3636926
a.UNIT_OF_MEASURE
--a.MIGRATED_RBS_ELEMENT_ID
From Pa_resource_list_members a, Pa_Rbs_Elements_Temp Tmp
Where Tmp.Old_Element_Id = a.resource_list_member_id;
SELECT resource_list_id
FROM pa_resource_list_assignments_v
WHERE project_id = p_source_project_id;
SELECT control_flag
INTO l_control_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = l_resource_list_id;
* Insert resource list members into the
* pa_resource_list_members table as those that
* exist for the p_source_project_id.
* The project_id should be the destination project id
* and the resource_list_member_id should be the one from
* the sequence.
* *****************************************************/
/******************************************************
* Bug - 3591751
* Desc - Inserting the wp_eligible_flag as in the source
* resource_list.
**********************************************************/
/**********************************************************************
* Bug - 3597011
* Desc - While inserting we need to check for enabled_flag <> N
***********************************************************************/
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,
--3591751
wp_eligible_flag,
--Bug 3636926
unit_of_measure,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
pa_resource_list_members_s.NEXTVAL,
l_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,
--3591751
a.wp_eligible_flag,
--Bug 3636926
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 = l_resource_list_id
AND a.object_id = p_source_project_id
AND a.object_type = 'PROJECT'
-- 3597011
and a.enabled_flag <> 'N'
AND
(a.resource_id,a.res_format_id,NVL(a.alias,'XXX'))
IN
(SELECT resource_id,res_format_id,NVL(alias,'XXX')
FROM pa_resource_list_members
WHERE resource_list_id = l_resource_list_id
AND object_id = p_source_project_id
AND object_type = 'PROJECT'
MINUS
SELECT resource_id,res_format_id,NVL(alias,'XXX')
FROM pa_resource_list_members
WHERE resource_list_id = l_resource_list_id
AND object_id = p_destination_project_id
AND object_type = 'PROJECT');
update pa_resource_lists_tl set
NAME = P_NAME,
DESCRIPTION = P_DESCRIPTION,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = decode(P_OWNER, 'SEED', 1, 0),
LAST_UPDATE_LOGIN = 0,
SOURCE_LANG = userenv('LANG')
where resource_list_id = P_RESOURCE_LIST_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
* Description : This API is used to update or insert rows
* into table pa_resource_lists_bg and
* pa_resource_lits_tl table. This procedure
* is called from the lct file.
* **************************************************/
procedure LOAD_ROW(
P_RESOURCE_LIST_ID in NUMBER,
P_NAME in VARCHAR2,
P_DESCRIPTION in VARCHAR2,
P_PUBLIC_FLAG in VARCHAR2,
P_GROUP_RESOURCE_TYPE_ID in NUMBER,
P_START_DATE_ACTIVE in DATE,
P_END_DATE_ACTIVE in DATE,
P_UNCATEGORIZED_FLAG in VARCHAR2,
P_BUSINESS_GROUP_ID in NUMBER,
P_JOB_GROUP_ID in NUMBER,
P_RESOURCE_LIST_TYPE in VARCHAR2,
P_OWNER in VARCHAR2)
IS
user_id NUMBER;
Select
Rowid
from
PA_RESOURCE_LISTS_ALL_BG
Where Resource_List_Id = P_Resource_List_Id;
/*SELECT ROWID
INTO l_row_id
FROM pa_resource_lists_all_bg
WHERE resource_list_id = P_RESOURCE_LIST_ID;
SELECT nvl(p_resource_list_id,pa_resource_lists_s.NEXTVAL)
INTO l_resource_list_id
FROM dual;
Update PA_RESOURCE_LISTS_ALL_BG
SET
NAME = P_NAME ,
DESCRIPTION = P_DESCRIPTION ,
PUBLIC_FLAG = P_PUBLIC_FLAG ,
GROUP_RESOURCE_TYPE_ID = P_GROUP_RESOURCE_TYPE_ID ,
START_DATE_ACTIVE = P_START_DATE_ACTIVE ,
END_DATE_ACTIVE = P_END_DATE_ACTIVE ,
UNCATEGORIZED_FLAG = P_UNCATEGORIZED_FLAG ,
BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID ,
JOB_GROUP_ID = P_JOB_GROUP_ID ,
RESOURCE_LIST_TYPE = P_RESOURCE_LIST_TYPE ,
LAST_UPDATED_BY = user_id ,
LAST_UPDATE_DATE = sysdate ,
LAST_UPDATE_LOGIN = 0
WHERE RESOURCE_LIST_ID = P_RESOURCE_LIST_ID;
Insert Into PA_RESOURCE_LISTS_ALL_BG
(
RESOURCE_LIST_ID,
NAME ,
DESCRIPTION ,
PUBLIC_FLAG ,
GROUP_RESOURCE_TYPE_ID ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
UNCATEGORIZED_FLAG ,
BUSINESS_GROUP_ID ,
JOB_GROUP_ID ,
RESOURCE_LIST_TYPE,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN,
CONTROL_FLAG,
USE_FOR_WP_FLAG,
MIGRATION_CODE
)
VALUES
(
P_RESOURCE_LIST_ID,
P_NAME ,
P_DESCRIPTION ,
P_PUBLIC_FLAG ,
P_GROUP_RESOURCE_TYPE_ID ,
P_START_DATE_ACTIVE ,
P_END_DATE_ACTIVE ,
P_UNCATEGORIZED_FLAG ,
P_BUSINESS_GROUP_ID ,
P_JOB_GROUP_ID ,
P_RESOURCE_LIST_TYPE,
user_id ,
sysdate,
sysdate ,
user_id ,
0,
'Y',
'N', -- open issue
NULL);
update pa_resource_lists_tl
set
NAME = P_NAME,
DESCRIPTION = P_DESCRIPTION,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id,
SOURCE_LANG = userenv('LANG')
where resource_list_id = P_RESOURCE_LIST_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert into pa_resource_lists_tl (
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RESOURCE_LIST_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG)
select
FND_GLOBAL.LOGIN_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
P_RESOURCE_LIST_ID,
P_NAME,
NVL(P_DESCRIPTION,P_NAME),
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from pa_resource_lists_tl T
where T.RESOURCE_LIST_ID = P_RESOURCE_LIST_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
/*PA_Resource_List_tbl_Pkg.Update_Row(
X_ROW_ID => l_row_id ,
X_RESOURCE_LIST_ID => P_RESOURCE_LIST_ID ,
X_NAME => P_NAME ,
X_DESCRIPTION => P_DESCRIPTION ,
X_PUBLIC_FLAG => P_PUBLIC_FLAG ,
X_GROUP_RESOURCE_TYPE_ID => P_GROUP_RESOURCE_TYPE_ID,
X_START_DATE_ACTIVE => P_START_DATE_ACTIVE ,
X_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
X_UNCATEGORIZED_FLAG => P_UNCATEGORIZED_FLAG ,
X_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID ,
X_JOB_GROUP_ID => P_JOB_GROUP_ID ,
X_RESOURCE_LIST_TYPE => P_RESOURCE_LIST_TYPE ,
X_LAST_UPDATED_BY => user_id ,
X_LAST_UPDATE_DATE => sysdate ,
X_LAST_UPDATE_LOGIN => 0 );
PA_Resource_List_tbl_Pkg.Insert_row(
X_ROW_ID => l_row_id ,
X_RESOURCE_LIST_ID => L_RESOURCE_LIST_ID ,
X_NAME => P_NAME ,
X_DESCRIPTION => P_DESCRIPTION ,
X_PUBLIC_FLAG => P_PUBLIC_FLAG ,
X_GROUP_RESOURCE_TYPE_ID => P_GROUP_RESOURCE_TYPE_ID ,
X_START_DATE_ACTIVE => P_START_DATE_ACTIVE ,
X_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
X_UNCATEGORIZED_FLAG => P_UNCATEGORIZED_FLAG ,
X_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID ,
X_JOB_GROUP_ID => P_JOB_GROUP_ID ,
X_RESOURCE_LIST_TYPE => P_RESOURCE_LIST_TYPE ,
X_LAST_UPDATED_BY => user_id ,
X_LAST_UPDATE_DATE => sysdate ,
X_CREATION_DATE => sysdate ,
X_CREATED_BY => user_id ,
X_LAST_UPDATE_LOGIN => 0 );*/