[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_operation_type VARCHAR2, --INSERT, FROM, SELECT, WHERE1, WHERE2
p_mode VARCHAR2 DEFAULT 'TMP'
) RETURN VARCHAR2 IS
l_str VARCHAR2(50);
IF p_operation_type = 'INSERT' OR
p_operation_type = 'SELECT' OR
p_operation_type = 'FROM' THEN
IF p_operation_type = 'SELECT' THEN
l_str := 'tab1.';
SELECT prec.res_tokens, res_format_id
INTO l_token_string, g_res_format_id
FROM PA_ACT_PRL_PRECEDENCE prec
WHERE prec.EFF_RES_FORMAT_ID = p_eff_res_format_id
AND prec.res_prec_id = g_res_prec_id;
FUNCTION get_insert_clause (
p_resource_class_id NUMBER,
p_res_format_id NUMBER,
p_operation_type VARCHAR2
) RETURN VARCHAR2 IS
l_insert_clause VARCHAR2(4000);
l_insert_clause := 'INSERT INTO pa_res_list_map_tmp3 ( RES_FORMAT_ID, RESOURCE_LIST_MEMBER_ID, RES_LIST_MEM_SEQ, ';
l_insert_clause := l_insert_clause || get_SQL_tags (l_res_tokens(i), 'INSERT') || ', ';
l_insert_clause := l_insert_clause || get_SQL_tags (l_res_tokens(i), 'INSERT') || ' ';
l_insert_clause := l_insert_clause || ') ';
l_insert_clause := ' INSERT INTO pa_res_list_map_tmp4 (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_TYPE_ID,'
|| 'RESOURCE_TYPE_CODE,RESOURCE_CLASS_ID,RESOURCE_CLASS_CODE,RES_FORMAT_ID,'
|| 'MFC_COST_TYPE_ID,RESOURCE_CLASS_FLAG,FC_RES_TYPE_CODE,'
|| 'BOM_LABOR_RESOURCE_ID,BOM_EQUIP_RESOURCE_ID,INVENTORY_ITEM_ID,'
|| 'ITEM_CATEGORY_ID,PERSON_TYPE_CODE,BOM_RESOURCE_ID,NAMED_ROLE,'
|| 'INCURRED_BY_RES_FLAG,TXN_COPY_FROM_RL_FLAG,TXN_SPREAD_CURVE_ID,'
|| 'TXN_ETC_METHOD_CODE,TXN_OBJECT_TYPE,TXN_OBJECT_ID,TXN_PROJECT_ID,'
|| 'TXN_BUDGET_VERSION_ID,TXN_RESOURCE_LIST_MEMBER_ID,TXN_RESOURCE_ID,'
|| 'TXN_ALIAS,TXN_TRACK_AS_LABOR_FLAG,TXN_FUNDS_CONTROL_LEVEL_CODE,'
|| 'TXN_SOURCE_ID,TXN_SOURCE_TYPE_CODE,TXN_PROCESS_CODE,TXN_ERROR_MSG_CODE,'
|| 'TXN_TASK_ID,TXN_WBS_ELEMENT_VERSION_ID,TXN_RBS_ELEMENT_ID,'
|| 'TXN_RBS_ELEMENT_VERSION_ID,TXN_PLANNING_START_DATE,TXN_PLANNING_END_DATE,'
|| 'TXN_RECORD_VERSION_NUMBER,TXN_SP_FIXED_DATE,TXN_RATE_BASED_FLAG,'
|| 'TXN_RES_CLASS_BILL_RATE_SCH_ID,TXN_RES_CLASS_COST_SCH_ID,'
|| 'TXN_USE_PLANNING_RATES_FLAG,TXN_BILL_JOB_GROUP_ID,'
|| 'TXN_PROJECT_CURRENCY_CODE,TXN_PROJFUNC_CURRENCY_CODE,'
|| 'TXN_EMP_BILL_RATE_SCHEDULE_ID,TXN_JOB_BILL_RATE_SCHEDULE_ID,'
|| 'TXN_LABOR_BILL_RATE_ORG_ID,TXN_LABOR_SCH_TYPE,TXN_LABOR_SCHEDULE_DISCOUNT,'
|| 'TXN_LABOR_SCHEDULE_FIXED_DATE,TXN_LABOR_STD_BILL_RATE_SCHDL,'
|| 'TXN_CURRENCY_CODE,TXN_PLAN_QUANTITY,RESOURCE_LIST_MEMBER_ID, TMP_ROWID, CBS_ELEMENT_ID) '; --bug#16827157
RETURN l_insert_clause;
END;--end function get_insert_clause
FUNCTION get_select_clause (
p_resource_class_id NUMBER,
p_res_format_id NUMBER,
p_operation_type VARCHAR2
) RETURN VARCHAR2 IS
l_select_clause VARCHAR2(5000);
l_select_clause := ' SELECT ' || g_res_format_id || ', tab2.RESOURCE_LIST_MEMBER_ID,'
|| 'DECODE(tab2.RESOURCE_LIST_MEMBER_ID,NULL,pa_resource_list_members_s.NEXTVAL,NULL), ' ;
l_select_clause := l_select_clause || get_SQL_tags (l_res_tokens(i), 'SELECT');
l_select_clause := l_select_clause || ', ';
l_select_clause := l_select_clause || ' ';
RETURN l_select_clause;
l_select_clause := ' SELECT tab1.PERSON_ID,tab1.JOB_ID,tab1.ORGANIZATION_ID,tab1.VENDOR_ID,';
l_select_clause := l_select_clause || 'tab1.EXPENDITURE_TYPE,tab1.EVENT_TYPE,tab1.NON_LABOR_RESOURCE,';
l_select_clause := l_select_clause || 'tab1.EXPENDITURE_CATEGORY,tab1.REVENUE_CATEGORY,';
l_select_clause := l_select_clause || 'tab1.NON_LABOR_RESOURCE_ORG_ID,tab1.EVENT_TYPE_CLASSIFICATION,';
l_select_clause := l_select_clause || 'tab1.SYSTEM_LINKAGE_FUNCTION,tab1.PROJECT_ROLE_ID,tab1.RESOURCE_TYPE_ID,';
l_select_clause := l_select_clause || 'tab1.RESOURCE_TYPE_CODE,1,''PEOPLE'',';
l_select_clause := l_select_clause || 'tab1.RESOURCE_TYPE_CODE,tab2.RESOURCE_CLASS_ID,tab2.RESOURCE_CLASS_CODE,';
l_select_clause := l_select_clause || 'tab1.RESOURCE_TYPE_CODE,tab1.RESOURCE_CLASS_ID,tab1.RESOURCE_CLASS_CODE,';
l_select_clause := l_select_clause || 'tab2.RES_FORMAT_ID,tab1.MFC_COST_TYPE_ID,tab1.RESOURCE_CLASS_FLAG,';
l_select_clause := l_select_clause || 'tab1.FC_RES_TYPE_CODE,tab1.BOM_LABOR_RESOURCE_ID,';
l_select_clause := l_select_clause || 'tab1.BOM_EQUIP_RESOURCE_ID,tab1.INVENTORY_ITEM_ID,tab1.ITEM_CATEGORY_ID,';
l_select_clause := l_select_clause || 'tab1.PERSON_TYPE_CODE,tab1.BOM_RESOURCE_ID,tab1.NAMED_ROLE,';
l_select_clause := l_select_clause || 'tab1.INCURRED_BY_RES_FLAG,tab1.TXN_COPY_FROM_RL_FLAG,';
l_select_clause := l_select_clause || 'tab1.TXN_SPREAD_CURVE_ID,tab1.TXN_ETC_METHOD_CODE,tab1.TXN_OBJECT_TYPE,';
l_select_clause := l_select_clause || 'tab1.TXN_OBJECT_ID,tab1.TXN_PROJECT_ID,tab1.TXN_BUDGET_VERSION_ID,';
l_select_clause := l_select_clause || 'tab1.TXN_RESOURCE_LIST_MEMBER_ID,tab1.TXN_RESOURCE_ID,tab1.TXN_ALIAS,';
l_select_clause := l_select_clause || 'tab1.TXN_TRACK_AS_LABOR_FLAG,tab1.TXN_FUNDS_CONTROL_LEVEL_CODE,';
l_select_clause := l_select_clause || 'tab1.TXN_SOURCE_ID,tab1.TXN_SOURCE_TYPE_CODE,tab1.TXN_PROCESS_CODE,';
l_select_clause := l_select_clause || 'tab1.TXN_ERROR_MSG_CODE,tab1.TXN_TASK_ID,tab1.TXN_WBS_ELEMENT_VERSION_ID,';
l_select_clause := l_select_clause || 'tab1.TXN_RBS_ELEMENT_ID,tab1.TXN_RBS_ELEMENT_VERSION_ID,';
l_select_clause := l_select_clause || 'tab1.TXN_PLANNING_START_DATE,tab1.TXN_PLANNING_END_DATE,';
l_select_clause := l_select_clause || 'tab1.TXN_RECORD_VERSION_NUMBER,tab1.TXN_SP_FIXED_DATE,';
l_select_clause := l_select_clause || 'tab1.TXN_RATE_BASED_FLAG,tab1.TXN_RES_CLASS_BILL_RATE_SCH_ID,';
l_select_clause := l_select_clause || 'tab1.TXN_RES_CLASS_COST_SCH_ID,tab1.TXN_USE_PLANNING_RATES_FLAG,';
l_select_clause := l_select_clause || 'tab1.TXN_BILL_JOB_GROUP_ID,tab1.TXN_PROJECT_CURRENCY_CODE,';
l_select_clause := l_select_clause || 'tab1.TXN_PROJFUNC_CURRENCY_CODE,tab1.TXN_EMP_BILL_RATE_SCHEDULE_ID,';
l_select_clause := l_select_clause || 'tab1.TXN_JOB_BILL_RATE_SCHEDULE_ID,tab1.TXN_LABOR_BILL_RATE_ORG_ID,';
l_select_clause := l_select_clause || 'tab1.TXN_LABOR_SCH_TYPE,tab1.TXN_LABOR_SCHEDULE_DISCOUNT,';
l_select_clause := l_select_clause || 'tab1.TXN_LABOR_SCHEDULE_FIXED_DATE,tab1.TXN_LABOR_STD_BILL_RATE_SCHDL,';
l_select_clause := l_select_clause || 'tab1.TXN_CURRENCY_CODE,tab1.TXN_PLAN_QUANTITY,';
l_select_clause := l_select_clause || 'NVL(tab2.RESOURCE_LIST_MEMBER_ID,tab2.RES_LIST_MEM_SEQ),';
l_select_clause := l_select_clause || 'tab1.ROWID, tab1.CBS_ELEMENT_ID'; --bug#16827157
RETURN l_select_clause;
END;--end function get_select_clause
l_from_clause := ' FROM (SELECT DISTINCT tab1.RES_FORMAT_ID, ' ;
l_from_clause := ' FROM (SELECT DISTINCT ' ;
l_INSERT_clause VARCHAR2 (1000);
l_SELECT_clause VARCHAR2 (1000);
l_INSERT_clause := get_insert_clause (p_resource_class_id, p_format_id, 'TMP3');
l_SELECT_clause := get_select_clause (p_resource_class_id, p_format_id, 'TMP3');
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause || ';' ;
pa_debug.write('MAP_RESOURCE_LIST: ' || g_module_name,'identify_new_plan_res l_INSERT_clause '|| l_INSERT_clause ,1);
pa_debug.write('MAP_RESOURCE_LIST: ' || g_module_name,'identify_new_plan_res l_SELECT_clause '|| l_SELECT_clause ,1);
/* SELECT count(*) into l_count
FROM pa_res_list_map_tmp1
IF (l_count = 0) THEN
RETURN FALSE;
SELECT 1
INTO l_count FROM dual WHERE EXISTS (SELECT 1
FROM pa_res_list_map_tmp1);
SELECT
tmp3.RES_LIST_MEM_SEQ,
tmp3.RES_FORMAT_ID,
tmp3.BOM_LABOR_RESOURCE_ID,
tmp3.BOM_EQUIP_RESOURCE_ID,
tmp3.BOM_RESOURCE_ID,
tmp3.PERSON_ID,
tmp3.EVENT_TYPE,
tmp3.EXPENDITURE_CATEGORY,
tmp3.EXPENDITURE_TYPE,
tmp3.ITEM_CATEGORY_ID,
tmp3.INVENTORY_ITEM_ID,
tmp3.JOB_ID,
tmp3.ORGANIZATION_ID,
tmp3.PERSON_TYPE_CODE,
tmp3.NON_LABOR_RESOURCE,
tmp3.REVENUE_CATEGORY,
tmp3.VENDOR_ID,
tmp3.PROJECT_ROLE_ID,
tmp3.FC_RES_TYPE_CODE,
tmp3.INCURRED_BY_RES_FLAG,
tmp3.NAMED_ROLE,
cls.RESOURCE_CLASS_ID,
cls.RESOURCE_CLASS_CODE
FROM
pa_res_list_map_tmp3 tmp3,
pa_resource_classes_b cls
WHERE
RESOURCE_LIST_MEMBER_ID IS NULL AND
nvl(tmp3.resource_class_id, l_resource_class_id) = cls.resource_class_id;
l_INSERT_clause VARCHAR2 (4000);
l_SELECT_clause VARCHAR2 (5000);
l_INSERT_clause := get_insert_clause (p_resource_class_id, p_format_id, 'TMP4');
l_SELECT_clause := get_select_clause (p_resource_class_id, p_format_id, 'TMP4');
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause || ';' ;
pa_debug.write('MAP_RESOURCE_LIST: ' || g_module_name,'process_mapped_txns l_INSERT_clause '|| l_INSERT_clause ,1);
pa_debug.write('MAP_RESOURCE_LIST: ' || g_module_name,'process_mapped_txns l_SELECT_clause '|| l_SELECT_clause ,1);
-- Replaced truncate statement with delete for resolving the auto commit issue.
--EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP3');
EXECUTE IMMEDIATE ('DELETE FROM PA_RES_LIST_MAP_TMP3'); --bug#16865233
EXECUTE IMMEDIATE ('DELETE FROM PA_RES_LIST_MAP_TMP1 TMP1' || ' WHERE ROWID IN ( SELECT tmp_rowid FROM pa_res_list_map_tmp4 tmp4 ) '); --bug#16865233
select b.resource_class_id
into l_res_class_id
from PA_ACT_PRL_PRECEDENCE a, pa_Res_formats_b b
where a.res_prec_id = g_res_prec_id
and a.eff_res_format_id = p_format_id
and b.res_format_id = a.res_format_id;
UPDATE pa_res_list_map_tmp3 a
SET ( a.RESOURCE_CLASS_ID,a.RESOURCE_CLASS_CODE)
= (SELECT c.resource_class_id ,c.resource_class_code
FROM pa_Res_formats_b b,PA_RESOURCE_CLASSES_B c, PA_ACT_PRL_PRECEDENCE d
WHERE b.resource_class_id = c.resource_class_id
AND a.res_format_id = b.res_format_id
AND b.res_format_id = d.res_format_id
AND d.res_prec_id = g_res_prec_id
and d.eff_res_format_id = p_format_id
)
WHERE EXISTS (
SELECT 'x'
FROM pa_Res_formats_b b1,PA_RESOURCE_CLASSES_B c1, PA_ACT_PRL_PRECEDENCE d1
WHERE b1.resource_class_id = c1.resource_class_id
AND b1.res_format_id = d1.res_format_id
AND a.res_format_id = b1.res_format_id
AND d1.res_prec_id = g_res_prec_id
and d1.eff_res_format_id = p_format_id
);
SELECT
resource_class_id
FROM
pa_resource_classes_b
WHERE g_resource_class_flag = 'Y'
UNION
SELECT 1
FROM dual
WHERE g_resource_class_flag = 'N' ;
SELECT RES_FORMAT_ID
BULK COLLECT
INTO l_res_list_formats
FROM pa_plan_rl_formats
WHERE RESOURCE_LIST_ID = p_resource_list_id;
SELECT CONTROL_FLAG, nvl(resource_class_flag, 'Y')
INTO g_control_flag, g_resource_class_flag
FROM pa_resource_lists_all_bg
WHERE RESOURCE_LIST_ID = p_resource_list_id;
SELECT prec.EFF_RES_FORMAT_ID, prec.RES_PREC_ID
bulk collect INTO l_eff_formats, l_res_prec_id
FROM PA_ACT_PRL_PRECEDENCE prec, pa_plan_rl_formats prl
WHERE prl.RESOURCE_LIST_ID = p_resource_list_id
AND prl.RES_FORMAT_ID = prec.RES_FORMAT_ID
AND prec.res_class_type = 'ONE'
ORDER BY res_prec_id;
-- update resource attributes which can be derived but are not present
/* split the below update into two to remove 9i dependencies
UPDATE pa_res_list_map_tmp1 tmp1
SET tmp1.EXPENDITURE_CATEGORY = nvl(tmp1.EXPENDITURE_CATEGORY, (SELECT typ.EXPENDITURE_CATEGORY
FROM PA_EXPENDITURE_TYPES typ
WHERE typ.EXPENDITURE_TYPE = tmp1.EXPENDITURE_TYPE ) )
,tmp1.ITEM_CATEGORY_ID = nvl(tmp1.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.INVENTORY_ITEM_ID = tmp1.INVENTORY_ITEM_ID and
cat.organization_id = tmp1.organization_id )
) ;
UPDATE pa_res_list_map_tmp1 tmp1
SET tmp1.EXPENDITURE_CATEGORY = (SELECT typ.EXPENDITURE_CATEGORY
FROM PA_EXPENDITURE_TYPES typ
WHERE typ.EXPENDITURE_TYPE = tmp1.EXPENDITURE_TYPE )
WHERE tmp1.EXPENDITURE_CATEGORY IS NULL;
UPDATE pa_res_list_map_tmp1 tmp1
SET tmp1.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.INVENTORY_ITEM_ID = tmp1.INVENTORY_ITEM_ID and
cat.organization_id = tmp1.organization_id )
WHERE tmp1.ITEM_CATEGORY_ID IS NULL;
UPDATE pa_res_list_map_tmp1 tmp1
SET tmp1.revenue_category = (SELECT evt.revenue_category_code
FROM pa_event_types evt
WHERE evt.event_type=tmp1.event_type)
WHERE tmp1.revenue_category IS NULL
AND tmp1.event_type IS NOT NULL;
UPDATE pa_res_list_map_tmp1 tmp1
SET tmp1.revenue_category = (SELECT et.revenue_category_code
FROM pa_expenditure_types et
WHERE et.expenditure_type=tmp1.expenditure_type)
WHERE tmp1.revenue_category IS NULL
AND tmp1.expenditure_type IS NOT NULL;
SELECT job_group_id INTO l_job_group_id FROM pa_resource_lists_all_bg WHERE resource_list_id = p_resource_list_id ;
UPDATE pa_res_list_map_tmp1 tmp1
SET tmp1.job_id =
(SELECT PA_Cross_Business_Grp.IsMappedToJob(tmp1.job_id, l_job_group_id) FROM DUAL)
WHERE tmp1.job_id IS NOT NULL;
l_eff_formats.DELETE; --cleanup collection for next class