[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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
(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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
(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.prj_revenue, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
(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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
(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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
(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.prj_revenue, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
(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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_revenue, 0) <> 0) OR
(NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
(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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
(NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
(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.prj_revenue, 0) <> 0) OR --Bug 9666017
(NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
(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,
CBS_ELEMENT_ID --bug#16791711
)
( SELECT DISTINCT PROJECT_ELEMENT_ID,
RES_LIST_MEMBER_ID,
NULL,
CBS_ELEMENT_ID --bug#16791711
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
);
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,
tmp1.CBS_ELEMENT_ID, --bug#16791711
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
);
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
distinct tmp1.task_id,
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_ID, --bug#16791711
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_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
UNION
SELECT distinct nvl(tmp1.task_id,0),
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_ID, --bug#16791711
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_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
);
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
task_t.task_id,
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_ID, --bug#16791711
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_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
GROUP BY task_t.task_id,
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_ID,--bug#16791711
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,
tmp1.CBS_ELEMENT_ID, --bug#16791711
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_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
GROUP BY nvl(tmp1.task_id,0),
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_ID,--bug#16791711
NVL(c_gen_etc_source_code, NULL);
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
distinct tmp1.task_id,
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_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 (SELECT tmp.MAPPED_FIN_TASK_ID AS TASK_ID,
tmp.TARGET_RLM_ID AS RESOURCE_LIST_MEMBER_ID,
PRA.CBS_ELEMENT_ID AS CBS_ELEMENT_ID,
min(tmp.planning_start_date) AS planning_start_date,
max(tmp.planning_end_date) AS planning_end_date
FROM PA_FP_CALC_AMT_TMP1 tmp,
PA_RESOURCE_ASSIGNMENTS PRA
WHERE tmp.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
GROUP BY mapped_fin_task_id,TARGET_RLM_ID,PRA.CBS_ELEMENT_ID) 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_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
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
AND ra.CBS_ELEMENT_ID = tmp1.CBS_ELEMENT_ID);
SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
task_t.task_id,
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_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 (SELECT tmp.MAPPED_FIN_TASK_ID AS TASK_ID,
tmp.TARGET_RLM_ID AS RESOURCE_LIST_MEMBER_ID,
PRA.CBS_ELEMENT_ID AS CBS_ELEMENT_ID,
min(tmp.planning_start_date) AS planning_start_date,
max(tmp.planning_end_date) AS planning_end_date
FROM PA_FP_CALC_AMT_TMP1 tmp,
PA_RESOURCE_ASSIGNMENTS PRA
WHERE tmp.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
GROUP BY mapped_fin_task_id,TARGET_RLM_ID,PRA.CBS_ELEMENT_ID) 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_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
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
AND ra.CBS_ELEMENT_ID = tmp1.CBS_ELEMENT_ID)
GROUP BY task_t.task_id,
tmp1.resource_list_member_id,
tmp1.CBS_ELEMENT_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
;
/* 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,
CBS_ELEMENT_ID, --bug#16791711
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_cbs_id_tab(i),--bug#16791711
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)
);
INSERT INTO PA_RESOURCE_ASSIGNMENTS (
RESOURCE_ASSIGNMENT_ID,
BUDGET_VERSION_ID,
PROJECT_ID,
RESOURCE_LIST_MEMBER_ID,
CBS_ELEMENT_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_cbs_ele_ids(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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
);
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
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
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
)
WHERE nvl(tmp1.task_id,0) = 0;
p_procedure_name => 'UPDATE_RES_ASG',
p_error_text => substr(sqlerrm,1,240));
END UPDATE_RES_ASG;
* This procedure is called to collect actuals for a selected resource assignments or
* for a whole budget version given.
**/
PROCEDURE COLLECT_ACTUALS
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_RESOURCE_ASSGN_IDS IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
P_INIT_MSG_FLAG IN VARCHAR2 default 'Y',
P_COMMIT_FLAG IN VARCHAR2 default 'N',
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.collect_actuals';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT COPY_ETC_FROM_PLAN_FLAG
FROM PA_PROJ_FP_OPTIONS
WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
SELECT RESOURCE_ASSIGNMENT_ID
FROM PA_RESOURCE_ASSIGNMENTS
WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
select tmp.source_id
,tmp.txn_currency_code
,tmp.period_name
FROM pji_fm_xbs_accum_tmp1 tmp
WHERE tmp.source_id = p_resource_assignment_id;
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
WHERE pji_tmp.source_id is NOT NULL --bug#8485646
order by 1,2;
SELECT bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
AND bl.period_name = NVL(p_period_name,bl.period_name)
AND bl.txn_currency_code = p_txn_currency_code;
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;
select * from
(SELECT 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) txn_currency_code,
pji_tmp.period_name,
gd.start_date,
gd.end_date,
sum(pji_tmp.quantity)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)) txn_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)) txn_brdn_cost,
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)) txn_revenue,
sum(pji_tmp.prj_raw_cost) prj_raw_cost,
sum(pji_tmp.prj_brdn_cost)prj_brdn_cost,
sum(pji_tmp.prj_revenue) 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)) 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)) pou_brdn_cost,
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)) pou_revenue
FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
WHERE c_version_type = 'ALL'
AND pji_tmp.source_id = c_source_id
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
GROUP BY 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),
pji_tmp.period_name,
gd.start_date,
gd.end_date
UNION ALL
SELECT 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) txn_currency_code,
pji_tmp.period_name,
gd.start_date,
gd.end_date,
sum(pji_tmp.quantity) 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)) txn_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)) txn_brdn_cost,
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.txn_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)) txn_revenue,
sum(pji_tmp.prj_raw_cost) prj_raw_cost,
sum(pji_tmp.prj_brdn_cost)prj_brdn_cost,
sum(pji_tmp.prj_revenue) 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)) 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)) pou_brdn_cost,
sum(DECODE(c_multi_currency_flag,
'Y', pji_tmp.pou_revenue,
'N', pji_tmp.prj_revenue,
'A', pji_tmp.pou_revenue)) pou_revenue
FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
WHERE c_version_type = 'COST'
AND pji_tmp.source_id = c_source_id
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
GROUP BY 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),
pji_tmp.period_name,
gd.start_date,
gd.end_date)
order by source_id, txn_currency_code;
select start_date, end_date
from gl_period_statuses
where period_name = p_period_name
and set_of_books_id = p_set_of_books_id;
select start_date, end_date
from pa_periods_all
where period_name = p_period_name
and org_id = p_ord_id;
select record_version_number
into l_record_version_number
from pa_budget_versions
where budget_version_id = p_budget_version_id;
delete from PJI_FM_EXTR_PLAN_LINES;
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
* (l_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 = l_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;
UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
SET source_id =
(SELECT resource_assignment_id
FROM pa_resource_assignments ra
WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
AND ra.resource_list_member_id = tmp1.res_list_member_id
AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
AND ra.budget_version_id = p_budget_version_id);
Delete resource assignments which are not selected if resouce
assignment id is passed to this api. Otherwise populate all the
resource assignment ids present for the budget version in
l_resource_assgn_id_tab pl/sql table. - Begin
-----------------------------------------------------------------*/
l_resource_assgn_id_tab := p_resource_assgn_ids;
delete from pji_fm_xbs_accum_tmp1
where source_id = l_del_resource_assgn_id_tab(i);
INSERT INTO PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PERIOD_NAME,
BUDGET_LINE_ID,
TXN_CURRENCY_CODE,
RAW_COST_SOURCE,
BURDENED_COST_SOURCE,
QUANTITY_SOURCE,
REQUEST_ID,
PROJFUNC_CURRENCY_CODE,
PROJECT_CURRENCY_CODE
)
VALUES(p_budget_version_id,
l_resource_assgn_id_tab(i),
l_start_date,
l_end_date,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
period_info_rec.period_name,
PA_BUDGET_LINES_S.nextval,
period_info_rec.txn_currency_code,
PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,
PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,
PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,
fnd_global.conc_request_id,
l_fp_cols_rec.x_projfunc_currency_code,
l_fp_cols_rec.x_project_currency_code);
API to update the budget lines with correct ETC, EAC values - Begin
---------------------------------------------------------------------*/
FOR i IN 1..l_resource_assgn_id_tab.count LOOP
IF l_calendar_type = 'P' THEN
OPEN budget_line_cursor_pa(
l_txn_currency_flag,
l_resource_assgn_id_tab(i),
l_txn_currency_code_tab(i),
l_org_id,
l_FP_COLS_REC.X_VERSION_TYPE);
l_period_name_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_brdn_cost_tab.delete;
l_txn_revenue_tab.delete;
l_proj_raw_cost_tab.delete;
l_proj_brdn_cost_tab.delete;
l_proj_revenue_tab.delete;
l_pou_raw_cost_tab.delete;
l_pou_brdn_cost_tab.delete;
l_pou_revenue_tab.delete;
SELECT rate_based_flag into l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = l_resource_assgn_id_tab(i);
l_amt_dtls_tbl.delete;