[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select RESOURCE_LIST_ID from pa_resource_lists where migration_code is null
and resource_list_id <> nvl(FND_PROFILE.VALUE('PA_FORECAST_RESOURCE_LIST'), -99);
SELECT f.res_format_id,f.resource_class_id,
decode(f.group_res_type_code,'None',0,'REVENUE_CATEGORY',300,'EXPENDITURE_CATEGORY',
100, 'ORGANIZATION',200, -1 ) + decode(f.res_type_code , 'EMPLOYEE',1,
'EXPENDITURE_CATEGORY', 2, 'EXPENDITURE_TYPE', 3, 'JOB',4,'ORGANIZATION',
5, 'REVENUE_CATEGORY',6,'EVENT_TYPE',7,'VENDOR', 8, 'UNCATEGORIZED',9,-1 ) +
decode(nvl(f.labor_flag,'N'),'Y',0,15) map_seq
FROM pa_restype_map_to_resformat f
ORDER BY map_seq;
SELECT fmt.resource_class_id,cls.resource_class_code,fmt.res_format_id,
def.spread_curve_id, def.etc_method_code, def.mfc_cost_type_id,
def.object_id,def.object_type,b.res_type_code
FROM pa_res_formats_b fmt, pa_resource_classes_b cls,pa_plan_res_defaults def,
pa_res_types_b b
WHERE fmt.resource_class_flag = 'Y'
AND fmt.resource_class_id = cls.resource_class_id
AND def.resource_class_id = cls.resource_class_id
AND fmt.res_type_id = b.res_type_id
AND fmt.res_type_enabled_flag = 'Y'
AND def.object_type = 'CLASS';
SELECT b.resource_list_id,b.group_resource_type_id,b.uncategorized_flag,
b.last_update_login, b.creation_date, b.created_by, b.last_update_date,
b.last_updated_by, b.name,t.resource_type_code,
decode(nvl(t.resource_type_code,'None'),'None',0, 'REVENUE_CATEGORY',300,
'EXPENDITURE_CATEGORY',100, 'ORGANIZATION',200, 0 ) grp_seq
FROM pa_resource_lists_all_bg b, pa_resource_types t
WHERE t.resource_type_id(+) = b.group_resource_type_id and
b.resource_list_id = c_res_list_id and
EXISTS ( select 'X' from pa_resource_list_members m
WHERE m.res_format_id is null and m.resource_list_id = b.resource_list_id
OR b.uncategorized_flag = 'Y');
SELECT m.resource_list_member_id,m.resource_type_id,m.parent_member_id,
m.organization_id,m.revenue_category, m.expenditure_category,m.expenditure_type,
decode(rl.uncategorized_flag,'Y',9,decode(nvl(t.resource_type_code,'None'),'None',0 , 'EMPLOYEE',1, 'EXPENDITURE_CATEGORY',
2, 'EXPENDITURE_TYPE', 3, 'JOB',4,'ORGANIZATION',5, 'REVENUE_CATEGORY',6,'EVENT_TYPE',
7,'VENDOR', 8, 'UNCATEGORIZED',9,-1 )) + decode(nvl(track_as_labor_flag,'N'),'Y',0,15) res_seq,t.resource_type_code
FROM pa_resource_list_members m,pa_resource_types t,
pa_resource_lists_all_bg rl
WHERE m.resource_list_id = l_res_list_id
AND m.resource_type_id = t.resource_type_id(+)
AND m.res_format_id is null
AND NVL(t.resource_type_code,-99) not in ('UNCLASSIFIED', 'PROJECT_ROLE', 'HZ_PARTY')
AND rl.resource_list_id = m.resource_list_id
ORDER BY m.parent_member_id desc;
SELECT t.name,t.resource_class_id
FROM pa_resource_classes_tl t,pa_resource_classes_b c
WHERE t.resource_class_id = c.resource_class_id
AND language = userenv('LANG');
SELECT 'Y'
FROM pa_plan_rl_formats
WHERE resource_list_id = c_res_list_id
AND res_format_id = c_res_for_id;
SELECT 'Y'
FROM pa_resource_lists_all_bg
WHERE resource_list_id = c_res_list_id
AND migration_code = 'M';
SELECT 'Y'
FROM pa_resource_lists_all_bg b
WHERE resource_list_id = c_res_list_id
AND exists ( select 'Y' from pa_resource_list_members m
WHERE m.resource_list_id = b.resource_list_id and
m.res_format_id is not null);
g_last_updated_by pa_resource_list_members.last_updated_by%TYPE:= FND_GLOBAL.USER_ID;
g_last_update_date pa_resource_list_members.last_update_date%TYPE := SYSDATE;
g_created_by pa_resource_list_members.last_update_login%TYPE := FND_GLOBAL.USER_ID;
g_last_update_login pa_resource_list_members.last_update_login%TYPE := FND_GLOBAL.USER_ID;
l_updated_alias pa_resource_list_members.alias%TYPE := NULL;
SELECT unit_of_measure
INTO l_uom
FROM pa_expenditure_types et
WHERE et.expenditure_type = l_get_res_list_mem_id_csr.expenditure_type
AND ROWNUM = 1;
SELECT nvl(incurred_by_enabled_flag, 'N')
INTO l_incur_by_res_flag
FROM pa_res_formats_b
WHERE res_format_id = l_res_format_id;
update pa_resource_list_members
set res_format_id = l_res_format_id,
revenue_category = nvl(l_rev_cat,l_get_res_list_mem_id_csr.revenue_category),
expenditure_category = nvl(l_exp_cat,l_get_res_list_mem_id_csr.expenditure_category),
organization_id = nvl(l_org_id,l_get_res_list_mem_id_csr.organization_id),
resource_class_id = l_res_class_id,
resource_class_code = l_res_class_code,
spread_curve_id = l_spread_curve_id,
mfc_cost_type_id = NULL,
etc_method_code = l_etc_method_code,
resource_class_flag = decode(l_get_res_list_id_csr.uncategorized_flag,'Y','Y','N'),
object_id = l_get_res_list_id_csr.resource_list_id,
object_type = 'RESOURCE_LIST',
inventory_item_id = null,
item_category_id = null,
migration_code = 'M',
fc_res_type_code = l_fc_res_type_code,
wp_eligible_flag = l_wp_eligible_flag,
unit_of_measure = l_uom,
incurred_by_res_flag = l_incur_by_res_flag,
record_version_number = 1,
enabled_flag = nvl(l_enabled_flag, enabled_flag)
where resource_list_member_id = l_get_res_list_mem_id_csr.resource_list_member_id;
Select
alias,
parent_member_id
Into
l_res_alias,
l_parent_member_id
from
pa_resource_list_members
where
resource_list_member_id = l_get_res_list_mem_id_csr.resource_list_member_id;
select
alias
Into
l_res_parent_alias
from
pa_resource_list_members
where
resource_list_member_id = l_parent_member_id;
pa_debug.g_err_stage := 'Update the pa_resource_list_members record with the combined alias';
update pa_resource_list_members
set alias = l_res_alias
where resource_list_member_id = l_get_res_list_mem_id_csr.resource_list_member_id;
pa_debug.g_err_stage := 'Inserting into pa_resource_list_members table';
l_updated_alias := l_alias;
SELECT resource_list_member_id
INTO l_exists_alias_id
FROM pa_resource_list_members
WHERE alias = l_updated_alias||l_alias_concat
AND resource_list_id =
l_get_res_list_id_csr.resource_list_id
AND object_type = 'RESOURCE_LIST'
AND object_id =
l_get_res_list_id_csr.resource_list_id;
l_updated_alias := l_updated_alias||l_alias_concat;
UPDATE pa_resource_list_members
SET alias = l_updated_alias
WHERE resource_list_member_id =
l_first_alias_id;
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,
OBJECT_TYPE,
OBJECT_ID,
RESOURCE_CLASS_ID,
RESOURCE_CLASS_CODE,
RES_FORMAT_ID,
SPREAD_CURVE_ID,
ETC_METHOD_CODE,
MFC_COST_TYPE_ID,
RESOURCE_CLASS_FLAG,
INVENTORY_ITEM_ID,
ITEM_CATEGORY_ID,
MIGRATION_CODE ,
INCURRED_BY_RES_FLAG,
INCUR_BY_RES_CLASS_CODE,
INCUR_BY_ROLE_ID,
RES_TYPE_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 ,
WP_ELIGIBLE_FLAG,
UNIT_OF_MEASURE,
PROJECT_ROLE_ID,
RECORD_VERSION_NUMBER)
(select pa_resource_list_members_s.NEXTVAL,
l_get_res_list_id_csr.resource_list_id,
l_resource_id,
l_alias,
null,
1,
1,
'Y',
'Y',
l_track_as_labor_flag,
g_last_updated_by,
g_last_update_date,
g_creation_date,
g_created_by,
g_last_update_login,
'RESOURCE_LIST',
l_get_res_list_id_csr.resource_list_id,
l_indx,
l_res_class_csr_tbl(l_indx).resource_class_code,
l_res_class_csr_tbl(l_indx).res_format_id,
l_res_class_csr_tbl(l_indx).spread_curve_id ,
l_res_class_csr_tbl(l_indx).etc_method_code ,
NULL, --l_res_class_csr_tbl(l_indx).mfc_cost_type_id,
'Y',
l_inventory_item_id,
l_item_category_id ,
'N',
'N', --l_incur_by_res_flag,
l_incur_by_res_code,
l_incur_by_role_id,
l_res_class_csr_tbl(l_indx).res_type_code ,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
'Y',
decode(l_res_class_csr_tbl(l_indx).resource_class_code,
'PEOPLE','HOURS',
'FINANCIAL_ELEMENTS','DOLLARS',
'MATERIAL_ITEMS','DOLLARS',
'EQUIPMENT','HOURS'),
null,
1
from dual);
pa_debug.g_err_stage := 'Inserting into pa_plan_rl_formats table';
insert into pa_plan_rl_formats (
PLAN_RL_FORMAT_ID,
RESOURCE_LIST_ID,
RES_FORMAT_ID ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER
)
select
pa_plan_rl_formats_s.nextval,
l_get_res_list_id_csr.resource_list_id,
l_res_format_tbl(i) ,
g_last_update_date,
g_last_updated_by ,
g_creation_date ,
g_created_by ,
g_last_update_login,
1
from dual;
l_res_format_tbl.delete;
update pa_resource_lists_all_bg
set control_flag = 'Y',
use_for_wp_flag = 'Y',
migration_code = 'M',
record_version_number = 1
where resource_list_id = l_get_res_list_id_csr.resource_list_id;