[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tmp1.RESOURCE_ASSIGNMENT_ID, --p_TXN_SOURCE_ID,
'RES_ASSIGNMENTS', --tmp1.TXN_SOURCE_TYPE_CODE,
tmp1.PERSON_ID,
tmp1.JOB_ID,
tmp1.ORGANIZATION_ID,
tmp1.SUPPLIER_ID,
tmp1.EXPENDITURE_TYPE,
tmp1.EVENT_TYPE,
tmp1.NON_LABOR_RESOURCE,
tmp1.EXPENDITURE_CATEGORY,
tmp1.REVENUE_CATEGORY_CODE,
NULL, --tmp1.NLR_ORGANIZATION_ID,
tmp1.event_type,--tmp1.EVENT_CLASSIFICATION,
NULL, --tmp1.SYS_LINK_FUNCTION,
NVL(tmp1.INCUR_BY_ROLE_ID,tmp1.PROJECT_ROLE_ID),
NVL(tmp1.INCUR_BY_RES_CLASS_CODE,tmp1.RESOURCE_CLASS_CODE),
tmp1.MFC_COST_TYPE_ID,
tmp1.RESOURCE_CLASS_FLAG,
tmp1.FC_RES_TYPE_CODE,
tmp1.INVENTORY_ITEM_ID,
tmp1.ITEM_CATEGORY_ID,
tmp1.PERSON_TYPE_CODE,
tmp1.BOM_RESOURCE_ID,
tmp1.NAMED_ROLE,
tmp1.INCURRED_BY_RES_FLAG,
tmp1.RATE_BASED_FLAG,
tmp1.mapped_fin_task_id,
NULL, --TXN_WBS_ELEMENT_VER_ID
NULL, --tmp1.TXN_RBS_ELEMENT_ID,
tmp1.planning_start_date, --TXN_PLAN_START_DATE,
tmp1.planning_end_date --TXN_PLAN_END_DATE
FROM PA_FP_CALC_AMT_TMP1 tmp1
WHERE RESOURCE_ASSIGNMENT_ID > 0
AND TRANSACTION_SOURCE_CODE <> 'OPEN_COMMITMENTS'
AND TARGET_RLM_ID IS NULL;
/*after calling create_res_asg and update_res_asg
*we will create the new res_asg_id for the mapped
*rlm_id and task id for target budget_version; and
*to PA_FP_PLANNING_RES_TMP1. We need to update this
*value to calc_amt_tmp1 and calc_amt_tmp2 to facilitae
*our future operation. */
CURSOR update_res_asg IS
SELECT task_id,
resource_list_member_id,
resource_assignment_id
FROM PA_FP_PLANNING_RES_TMP1;
l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE;
l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE;
/* Date update variables */
l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
SELECT NVL(uncategorized_flag,'N')
INTO l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_fp_cols_rec.X_RESOURCE_LIST_ID;
UPDATE PA_FP_CALC_AMT_TMP1
SET target_rlm_id = l_rlm_id;
* p_fp_cols_rec does not have the updated source version ids. */
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
( p_msg => 'Before calling
pa_fp_gen_amount_utils.get_plan_version_dtls',
p_module_name => l_module_name,
p_log_level => 5 );
SELECT resource_list_id
INTO l_resource_list_id
FROM pa_budget_versions
WHERE budget_version_id = l_fp_cols_rec.x_gen_src_wp_version_id;
UPDATE PA_FP_CALC_AMT_TMP1
SET target_rlm_id = resource_list_member_id
WHERE transaction_source_code = 'WORKPLAN_RESOURCES';
SELECT resource_list_id
INTO l_resource_list_id
FROM pa_budget_versions
WHERE budget_version_id = l_fp_cols_rec.x_gen_src_plan_version_id;
UPDATE PA_FP_CALC_AMT_TMP1
SET target_rlm_id = resource_list_member_id
WHERE transaction_source_code = 'FINANCIAL_PLAN';
version_id not needed in pa_budget_versions. So update deleted **/
--select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP1,PA_FP_CALC_AMT_TMP1_N2)*/
PA_FP_CALC_AMT_TMP1
SET target_rlm_id = l_map_rlm_id_tab(i)
WHERE resource_assignment_id = l_map_txn_source_id_tab(i);
SELECT resource_assignment_id,
MIN(planning_start_date),
MAX(planning_end_date)
BULK COLLECT
INTO l_res_asg_id_tab,
l_start_date_tab,
l_end_date_tab
FROM pa_fp_planning_res_tmp1
GROUP BY resource_assignment_id;
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
tmp1.resource_assignment_id,
MIN(tmp1.planning_start_date),
MAX(tmp1.planning_end_date)
BULK COLLECT
INTO l_res_asg_id_tab,
l_start_date_tab,
l_end_date_tab
FROM pa_fp_planning_res_tmp1 tmp1,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.task_id = tmp1.task_id
AND ra.resource_list_member_id = tmp1.resource_list_member_id
--AND ra.resource_assignment_id = tmp1.resource_assignment_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date >= l_etc_start_date
AND rownum = 1 )))
GROUP BY tmp1.resource_assignment_id;
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
tmp1.resource_assignment_id,
MIN(tmp1.planning_start_date),
MAX(tmp1.planning_end_date)
BULK COLLECT
INTO l_res_asg_id_tab,
l_start_date_tab,
l_end_date_tab
FROM pa_fp_planning_res_tmp1 tmp1,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.task_id = tmp1.task_id
AND ra.resource_list_member_id = tmp1.resource_list_member_id
--AND ra.resource_assignment_id = tmp1.resource_assignment_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND NVL(bl.quantity,0) <>
NVL(bl.init_quantity,0)
AND rownum = 1 )))
GROUP BY tmp1.resource_assignment_id;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_resource_assignments
SET planning_start_date = l_start_date_tab(i),
planning_end_date = l_end_date_tab(i),
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
record_version_number = record_version_number + 1
WHERE resource_assignment_id = l_res_asg_id_tab(i);
OPEN update_res_asg;
FETCH update_res_asg
BULK COLLECT
INTO l_upd_task_id_tab,
l_upd_rlm_id_tab,
l_upd_target_ra_id_tab;
CLOSE update_res_asg;
UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP1,PA_FP_CALC_AMT_TMP1_N3)*/
PA_FP_CALC_AMT_TMP1
SET target_res_asg_id = l_upd_target_ra_id_tab(i)
WHERE mapped_fin_task_id = l_upd_task_id_tab(i)
AND target_rlm_id = l_upd_rlm_id_tab(i);
UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP1,PA_FP_CALC_AMT_TMP1_N3)*/
PA_FP_CALC_AMT_TMP1
SET target_res_asg_id = l_upd_target_ra_id_tab(i)
WHERE mapped_fin_task_id = l_upd_task_id_tab(i)
AND target_rlm_id = l_upd_rlm_id_tab(i);
UPDATE /*+ LEADING(PA_FP_CALC_AMT_TMP1) */
PA_FP_CALC_AMT_TMP1
SET target_res_asg_id = l_upd_target_ra_id_tab(i)
WHERE target_rlm_id = l_upd_rlm_id_tab(i);
* we need to update the transaction_source_code for target resources. */
-- Bug 4301959: Modified the Retain Manually Added Lines logic to
-- handle the non-time phased case separately, using the (quantity <>
-- actual quantity) check instead of (start_date > etc_start_date).
IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
SELECT DISTINCT target_res_asg_id, transaction_source_code
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_txn_src_code_tab
FROM PA_FP_CALC_AMT_TMP1;
SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp1.target_res_asg_id, tmp1.transaction_source_code
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_txn_src_code_tab
FROM PA_FP_CALC_AMT_TMP1 tmp1,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp1.target_res_asg_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date >= l_etc_start_date
AND rownum = 1 )));
SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp1.target_res_asg_id, tmp1.transaction_source_code
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_txn_src_code_tab
FROM PA_FP_CALC_AMT_TMP1 tmp1,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp1.target_res_asg_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND NVL(bl.quantity,0) <>
NVL(bl.init_quantity,0)
AND rownum = 1 )));
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_resource_assignments
SET transaction_source_code = l_txn_src_code_tab(i),
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
record_version_number = record_version_number + 1
WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
SELECT resource_assignment_id, target_res_asg_id
BULK COLLECT
INTO l_upd_ra_id_tab1,
l_upd_target_ra_id_tab1
FROM PA_FP_CALC_AMT_TMP1;
UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
PA_FP_CALC_AMT_TMP2
SET target_res_asg_id = l_upd_target_ra_id_tab1(i)
WHERE resource_assignment_id = l_upd_ra_id_tab1(i);