[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct pji_tmp.source_id,
DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code)
FROM pji_fm_xbs_accum_tmp1 pji_tmp;
SELECT pji_tmp.period_name,
pd.start_date,
pd.end_date,
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
WHERE c_version_type = 'ALL'
AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND pd.org_id = c_org_id
AND pd.period_name = pji_tmp.period_name
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
GROUP BY pji_tmp.period_name,
pd.start_date,
pd.end_date
UNION ALL
SELECT pji_tmp.period_name,
pd.start_date,
pd.end_date,
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
WHERE c_version_type = 'COST'
AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND pd.org_id = c_org_id
AND pd.period_name = pji_tmp.period_name
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
GROUP BY pji_tmp.period_name,
pd.start_date,
pd.end_date
UNION ALL
SELECT pji_tmp.period_name,
pd.start_date,
pd.end_date,
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
WHERE c_version_type = 'REVENUE'
AND (
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND pd.org_id = c_org_id
AND pd.period_name = pji_tmp.period_name
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
GROUP BY pji_tmp.period_name,
pd.start_date,
pd.end_date;
SELECT pji_tmp.period_name,
gd.start_date,
gd.end_date,
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
WHERE c_version_type = 'ALL'
AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
AND gd.period_name = pji_tmp.period_name
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
GROUP BY pji_tmp.period_name,
gd.start_date,
gd.end_date
UNION ALL
SELECT pji_tmp.period_name,
gd.start_date,
gd.end_date,
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
WHERE c_version_type = 'COST'
AND (
(NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
AND gd.period_name = pji_tmp.period_name
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
GROUP BY pji_tmp.period_name,
gd.start_date,
gd.end_date
UNION ALL
SELECT pji_tmp.period_name,
gd.start_date,
gd.end_date,
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
WHERE c_version_type = 'REVENUE'
AND (
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
AND gd.period_name = pji_tmp.period_name
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
GROUP BY pji_tmp.period_name,
gd.start_date,
gd.end_date;
SELECT pji_tmp.period_name,
nvl(ra.planning_start_date, TRUNC(Sysdate)),
nvl(ra.planning_end_date, TRUNC(Sysdate)),
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,
pa_resource_assignments ra
WHERE c_version_type = 'ALL'
AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
AND ra.resource_assignment_id = c_res_asg_id
GROUP BY pji_tmp.period_name,
nvl(ra.planning_start_date, TRUNC(Sysdate)),
nvl(ra.planning_end_date, TRUNC(Sysdate))
UNION ALL
SELECT pji_tmp.period_name,
nvl(ra.planning_start_date, TRUNC(Sysdate)),
nvl(ra.planning_end_date, TRUNC(Sysdate)),
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,
pa_resource_assignments ra
WHERE c_version_type = 'COST'
AND (
(NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
AND ra.resource_assignment_id = c_res_asg_id
GROUP BY pji_tmp.period_name,
nvl(ra.planning_start_date, TRUNC(Sysdate)),
nvl(ra.planning_end_date, TRUNC(Sysdate))
UNION ALL
SELECT pji_tmp.period_name,
nvl(ra.planning_start_date, TRUNC(Sysdate)),
nvl(ra.planning_end_date, TRUNC(Sysdate)),
sum(pji_tmp.quantity),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)),
sum(pji_tmp.prj_raw_cost),
sum(pji_tmp.prj_brdn_cost),
sum(pji_tmp.prj_revenue),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_raw_cost,
'N', pji_tmp.prj_raw_cost,
'A', pji_tmp.pou_raw_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_brdn_cost,
'N', pji_tmp.prj_brdn_cost,
'A', pji_tmp.pou_brdn_cost)),
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue))
FROM pji_fm_xbs_accum_tmp1 pji_tmp,
pa_resource_assignments ra
WHERE c_version_type = 'REVENUE'
AND (
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.quantity,0) <> 0)
)
AND pji_tmp.source_id = c_res_asg_id
AND DECODE(c_multi_currency_flag,'Y',
pji_tmp.txn_currency_code,c_txn_currency_code)
= c_txn_currency_code
AND ra.resource_assignment_id = c_res_asg_id
GROUP BY pji_tmp.period_name,
nvl(ra.planning_start_date, TRUNC(Sysdate)),
nvl(ra.planning_end_date, TRUNC(Sysdate));
SELECT plan_class_code
INTO l_plan_class_code
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID;
SELECT wp_version_flag
INTO l_wp_version_flag
FROM pa_budget_Versions
WHERE budget_version_id=P_BUDGET_VERSION_ID;
select count(*) into l_count from pji_fm_xbs_accum_tmp1;
select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
res_list_member_id IS NULL;
/* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
* (p_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
* This logic is not handled by the PJI generic resource mapping API. */
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 pji_fm_xbs_accum_tmp1
SET res_list_member_id = l_rlm_id;
update pji_fm_xbs_accum_tmp1 set project_element_id = null
where NVL(project_element_id,0) <= 0;
DELETE FROM PA_FP_PLANNING_RES_TMP1;
INSERT INTO PA_FP_PLANNING_RES_TMP1 (
TASK_ID,
RESOURCE_LIST_MEMBER_ID,
RESOURCE_ASSIGNMENT_ID )
( SELECT DISTINCT PROJECT_ELEMENT_ID,
RES_LIST_MEMBER_ID,
NULL
FROM PJI_FM_XBS_ACCUM_TMP1);
/**Calling update_res_asg to populate the newly created resource_assignment_id back to
*pa_fp_planning_res_tmp1. Then this value needs to populated back to pji_fm_xbs_accum_tmp1
**/
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_msg => 'Before calling update_res_asg',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_COPY_ACTUALS_PUB.UPDATE_RES_ASG (
P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_FP_COLS_REC => P_FP_COLS_REC,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
(p_msg => 'After calling update_res_asg,return status is: '||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
SET source_id =
(SELECT /*+ INDEX(ra,PA_FP_PLANNING_RES_TMP1_N2)*/ resource_assignment_id
FROM PA_FP_PLANNING_RES_TMP1 ra
WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
AND ra.resource_list_member_id = tmp1.res_list_member_id );
SELECT DISTINCT source_id
BULK COLLECT
INTO l_res_asg_id_tmp_tab
FROM pji_fm_xbs_accum_tmp1;
UPDATE pa_resource_assignments ra
SET ra.unit_of_measure = 'DOLLARS',
ra.rate_based_flag = 'N'
WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
AND ( ra.transaction_source_code is not null
OR
(ra.transaction_source_code is null and NOT exists
( select 1
from pa_budget_lines pbl
where pbl.resource_assignment_id = ra.resource_assignment_id
and pbl.start_date >= l_etc_start_date
)
)
);
UPDATE pa_resource_assignments ra
SET ra.unit_of_measure = 'DOLLARS',
ra.rate_based_flag = 'N'
WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
AND ( ra.transaction_source_code is not null
OR
(ra.transaction_source_code is null and NOT exists
( select 1
from pa_budget_lines pbl
where pbl.resource_assignment_id = ra.resource_assignment_id
)
)
);
SELECT rate_based_flag into l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = l_res_asg_id_tab(i);
l_amt_dtls_tbl.delete;
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp (
resource_assignment_id,
txn_currency_code )
VALUES (
l_res_asg_id_tab(i),
l_txn_currency_code_tab(i) );
UPDATE pa_resource_asgn_curr_tmp tmp
SET ( txn_raw_cost_rate_override,
txn_burden_cost_rate_override,
txn_bill_rate_override ) =
( SELECT rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override,
rbc.txn_bill_rate_override
FROM pa_resource_asgn_curr rbc
WHERE tmp.resource_assignment_id = rbc.resource_assignment_id
AND tmp.txn_currency_code = rbc.txn_currency_code );
SELECT distinct nvl(tmp1.task_id,0),
tmp1.resource_list_member_id,
DECODE(p_calling_process, 'COPY_ACTUALS',
p_proj_start_date,
tmp1.planning_start_date),
DECODE(p_calling_process, 'COPY_ACTUALS',
p_proj_completion_date,
tmp1.planning_end_date),
NVL(c_gen_etc_source_code, NULL)
FROM PA_FP_PLANNING_RES_TMP1 tmp1
WHERE nvl(tmp1.task_id,0) = 0
AND NOT EXISTS (
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND NVL(ra.task_id,0) = 0
AND ra.resource_list_member_id = tmp1.resource_list_member_id);
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
distinct tmp1.task_id,
tmp1.resource_list_member_id,
DECODE(p_calling_process, 'COPY_ACTUALS',
NVL(task.start_date, p_proj_start_date),
tmp1.planning_start_date),
DECODE(p_calling_process, 'COPY_ACTUALS',
NVL(task.completion_date, p_proj_completion_date),
tmp1.planning_end_date),
NVL(c_gen_etc_source_code,
DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
-- based on task's etc source
FROM PA_FP_PLANNING_RES_TMP1 tmp1,
pa_tasks task
WHERE nvl(tmp1.task_id,0) > 0
AND tmp1.task_id = task.task_id
AND NOT EXISTS (
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
UNION
SELECT distinct nvl(tmp1.task_id,0),
tmp1.resource_list_member_id,
DECODE(p_calling_process, 'COPY_ACTUALS',
p_proj_start_date,
tmp1.planning_start_date),
DECODE(p_calling_process, 'COPY_ACTUALS',
p_proj_completion_date,
tmp1.planning_end_date),
NVL(c_gen_etc_source_code, NULL)
FROM PA_FP_PLANNING_RES_TMP1 tmp1
WHERE nvl(tmp1.task_id,0) = 0
AND NOT EXISTS (
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND NVL(ra.task_id,0) = 0
AND ra.resource_list_member_id = tmp1.resource_list_member_id);
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
task_t.task_id,
tmp1.resource_list_member_id,
MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
NVL(task_t.start_date, p_proj_start_date),
tmp1.planning_start_date)),
MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
NVL(task_t.completion_date, p_proj_completion_date),
tmp1.planning_end_date)),
NVL(c_gen_etc_source_code,
DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
-- based on task's etc source
FROM PA_FP_PLANNING_RES_TMP1 tmp1,
pa_tasks task, pa_tasks task_t
WHERE nvl(tmp1.task_id,0) > 0
AND tmp1.task_id = task.task_id
AND task.top_task_id = task_t.task_id
AND NOT EXISTS (
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND nvl(ra.task_id,0) = task_t.task_id
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
GROUP BY task_t.task_id,
tmp1.resource_list_member_id,
NVL(c_gen_etc_source_code,
DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
-- based on task's etc source
UNION
SELECT nvl(tmp1.task_id,0),
tmp1.resource_list_member_id,
MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
p_proj_start_date,
tmp1.planning_start_date)),
MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
p_proj_completion_date,
tmp1.planning_end_date)),
NVL(c_gen_etc_source_code, NULL)
FROM PA_FP_PLANNING_RES_TMP1 tmp1
WHERE nvl(tmp1.task_id,0) = 0
AND NOT EXISTS (
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND NVL(ra.task_id,0) = 0
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
GROUP BY nvl(tmp1.task_id,0),
tmp1.resource_list_member_id,
NVL(c_gen_etc_source_code, NULL);
/* Variables added to replace literals in INSERT stmts. */
l_project_as_id_minus1 NUMBER:=-1;
SELECT NVL(start_date,trunc(sysdate)),
NVL(completion_date,trunc(sysdate))
INTO l_proj_start_date, l_proj_completion_date
FROM pa_projects_all
WHERE project_id = P_PROJECT_ID;
INSERT INTO PA_RESOURCE_ASSIGNMENTS (
RESOURCE_ASSIGNMENT_ID,
BUDGET_VERSION_ID,
PROJECT_ID,
RESOURCE_LIST_MEMBER_ID,
TASK_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_ASSIGNMENT_ID,
PLANNING_START_DATE,
PLANNING_END_DATE,
RESOURCE_ASSIGNMENT_TYPE,
RECORD_VERSION_NUMBER,
TRANSACTION_SOURCE_CODE )
VALUES (
pa_resource_assignments_s.nextval,
p_budget_version_id,
p_project_id,
l_rlm_id_tab(i),
l_task_id_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_project_as_id_minus1,
l_start_date_tab(i),
l_completion_date_tab(i),
l_res_as_type_USER_ENTERED,
l_rec_ver_number_1,
l_etc_src_code_tab(i)
);
(p_msg => 'Before calling update_res_defaults',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
(p_msg => 'Before calling update_res_defaults',
p_module_name => l_module_name,
p_log_level => 5);
SELECT spread_curve_id
INTO l_spread_curve_id
FROM pa_spread_curves_b
WHERE spread_curve_code = 'FIXED_DATE';
UPDATE PA_RESOURCE_ASSIGNMENTS
SET SP_FIXED_DATE = PLANNING_START_DATE
WHERE SP_FIXED_DATE IS NULL
AND SPREAD_CURVE_ID = l_spread_curve_id
AND BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
PROCEDURE UPDATE_RES_ASG (
P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_PROCESS IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.update_res_asg';
pa_debug.set_curr_function( p_function => 'UPDATE_RES_ASG',
p_debug_mode => p_pa_debug_mode );
UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
SET resource_assignment_id =
(SELECT resource_assignment_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.project_id = P_PROJECT_ID
AND nvl(ra.task_id,0) = 0
AND ra.resource_list_member_id = tmp1.resource_list_member_id);
UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
SET resource_assignment_id =
(SELECT resource_assignment_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.project_id = P_PROJECT_ID
AND ra.task_id = tmp1.task_id
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
WHERE tmp1.task_id is NOT NULL
AND tmp1.task_id > 0;
UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
SET resource_assignment_id =
(SELECT resource_assignment_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.project_id = P_PROJECT_ID
AND nvl(ra.task_id,0) = 0
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
WHERE nvl(tmp1.task_id,0) = 0;
UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
SET resource_assignment_id =
(SELECT resource_assignment_id
FROM pa_resource_assignments ra,
pa_tasks t
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.project_id = P_PROJECT_ID
AND tmp1.task_id = t.task_id
AND t.top_task_id = ra.task_id
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
WHERE tmp1.task_id is NOT NULL
AND tmp1.task_id > 0;
UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
SET resource_assignment_id =
(SELECT resource_assignment_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.project_id = P_PROJECT_ID
AND nvl(ra.task_id,0) = 0
AND ra.resource_list_member_id = tmp1.resource_list_member_id)
WHERE nvl(tmp1.task_id,0) = 0;
p_procedure_name => 'UPDATE_RES_ASG',
p_error_text => substr(sqlerrm,1,240));
END UPDATE_RES_ASG;