[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
2.Rate based flag for target resource assignment gets updated correctly before coming
into any of ETC methods.
3.All considered scenarios:
Rate_based
non multi currency enabled: use PC
multi currency enabled
actuals currency is subset of total currency: use TC, currency based substraction
actuals currency is not subset of total currency: use TC, prorate ETC quantity
Non_rate_based
non multi currency enabled: use PC
multi currency enabled
actuals currency not subset of total currency: use TC, currency based substraction
actuals currency not subset of total currency: Compute ETC quantity in PC, prorate
this ETC quantity to different planning currencies based on PC amounts,
convert back from PC to TC.
*/
PROCEDURE GET_ETC_REMAIN_BDGT_AMTS
(P_SRC_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
P_TGT_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
P_FP_COLS_SRC_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_TASK_ID IN PA_TASKS.TASK_ID%TYPE,
P_RESOURCE_LIST_MEMBER_ID IN PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE,
P_ETC_SOURCE_CODE IN PA_TASKS.GEN_ETC_SOURCE_CODE%TYPE,
P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
P_PLANNING_OPTIONS_FLAG 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(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_REMAIN_BDGT_AMTS';
SELECT rate_based_flag
INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = p_tgt_res_asg_id;
SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT act_tmp.txn_currency_code
FROM PA_FP_FCST_GEN_TMP1 act_tmp,
PA_FP_CALC_AMT_TMP1 tot_tmp
WHERE act_tmp.project_element_id = tot_tmp.task_id
AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
'WORKPLAN_RESOURCES', 'ETC_WP',
'FINANCIAL_PLAN', 'ETC_FP')
MINUS
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = p_tgt_res_asg_id
AND transaction_source_code = p_etc_source_code
) WHERE rownum = 1;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
NVL(SUM(NVL(
DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
'PFC', total_pfc_raw_cost),0)),0),
NVL(SUM(NVL(
DECODE(l_currency_flag, 'PC', total_pc_burdened_cost,
'PFC', total_pfc_burdened_cost),0)),0),
NVL(SUM(NVL(
DECODE(l_currency_flag, 'PC', total_pc_revenue,
'PFC', total_pfc_revenue),0)),0)
INTO l_tot_quantity_pc_pfc,
l_tot_raw_cost_pc_pfc,
l_tot_brdn_cost_pc_pfc,
l_tot_revenue_pc_pfc
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = p_src_res_asg_id
AND transaction_source_code = p_etc_source_code;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DECODE(l_currency_flag,
'PC', NVL(SUM(DECODE(l_rate_based_flag,
'Y', quantity,
'N', NVL(prj_raw_cost,0))),0),
'PFC', NVL(SUM(DECODE(l_rate_based_flag,
'Y', quantity,
'N', NVL(pou_raw_cost,0))),0))
INTO l_act_quantity_pc_pfc
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = p_task_id
AND res_list_member_id = p_resource_list_member_id
AND data_type_code = 'ETC_FP';
target resource assignmentInsert the single PC record for total ETC.*/
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0)),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_revenue,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_revenue,0)),0))
INTO l_pc_pfc_rate_quantity,
l_pc_pfc_rate_raw_cost,
l_pc_pfc_rate_brdn_cost,
l_pc_pfc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE resource_assignment_id = p_src_res_asg_id
AND transaction_source_code in ('FINANCIAL_PLAN',
'WORKPLAN_RESOURCES');
/*Insert single PC record
If commitment is not included, record is inserted directly as 'ETC'
record, if commitment is to be considered, record is inserted as
'TOTAL_ETC' for further processing. */
IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
l_transaction_source_code := 'TOTAL_ETC';
INSERT INTO PA_FP_CALC_AMT_TMP2 (
RESOURCE_ASSIGNMENT_ID,
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
ETC_PC_RAW_COST,
ETC_PC_BURDENED_COST,
ETC_PC_REVENUE,
ETC_PFC_RAW_COST,
ETC_PFC_BURDENED_COST,
ETC_PFC_REVENUE,
TRANSACTION_SOURCE_CODE)
VALUES (
P_SRC_RES_ASG_ID,
P_TGT_RES_ASG_ID,
DECODE(l_currency_flag, 'PC', l_pc_currency_code,
'PFC', l_pfc_currency_code),
l_etc_quantity_pc_pfc,
l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate,
DECODE(l_currency_flag,
'PC', l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
'PFC', NULL),
DECODE(l_currency_flag,
'PC', l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
'PFC', NULL),
DECODE(l_currency_flag,
'PC', l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate,
'PFC', NULL),
DECODE(l_currency_flag,
'PFC', l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
'PC', NULL),
DECODE(l_currency_flag,
'PFC', l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
'PC', NULL),
DECODE(l_currency_flag,
'PFC', l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate,
'PC', NULL),
l_transaction_source_code);
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
txn_currency_code,
SUM(NVL(total_plan_quantity,0)),
SUM(NVL(total_txn_raw_cost,0)),
SUM(NVL(total_txn_burdened_cost,0)),
SUM(NVL(total_txn_revenue,0))
BULK COLLECT INTO
l_tot_currency_code_tab,
l_tot_quantity_tab,
l_tot_raw_cost_tab,
l_tot_brdn_cost_tab,
l_tot_revenue_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = p_src_res_asg_id
AND transaction_source_code = p_etc_source_code
GROUP BY txn_currency_code;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
txn_currency_code,
SUM(NVL(quantity,0)),
SUM(NVL(txn_raw_cost,0)),
SUM(NVL(txn_brdn_cost,0)),
SUM(NVL(txn_revenue,0))
BULK COLLECT INTO
l_act_currency_code_tab,
l_act_quantity_tab,
l_act_raw_cost_tab,
l_act_brdn_cost_tab,
l_act_revenue_tab
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = p_task_id
AND res_list_member_id = p_resource_list_member_id
AND data_type_code = 'ETC_FP'
GROUP BY txn_currency_code;
SELECT COUNT(*)
INTO l_currency_count_act_min_tot
FROM (
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT txn_currency_code
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = p_task_id
AND res_list_member_id = p_resource_list_member_id
AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
'WORKPLAN_RESOURCES', 'ETC_WP',
'FINANCIAL_PLAN', 'ETC_FP')
MINUS
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = p_src_res_asg_id
AND transaction_source_code = p_etc_source_code
) WHERE rownum = 1;
l_etc_quantity_tab.delete;
SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
NVL(SUM(NVL(total_txn_raw_cost,0)),0),
NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
NVL(SUM(NVL(total_txn_revenue,0)),0),
NVL(SUM(NVL(total_pc_raw_cost,0)),0),
NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
NVL(SUM(NVL(total_pc_revenue,0)),0)
INTO l_txn_rate_quantity,
l_txn_rate_raw_cost,
l_txn_rate_brdn_cost,
l_txn_rate_revenue,
l_pc_rate_raw_cost,
l_pc_rate_brdn_cost,
l_pc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE resource_assignment_id = p_src_res_asg_id
AND txn_currency_code = l_tot_currency_code_tab(i)
AND transaction_source_code in ('FINANCIAL_PLAN',
'WORKPLAN_RESOURCES');
/*Bulk insert
If commitment is not included, record is inserted directly as 'ETC'
record, if commitment is to be considered, record is inserted as
'TOTAL_ETC' for further processing. */
IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
l_transaction_source_code := 'TOTAL_ETC';
INSERT INTO PA_FP_CALC_AMT_TMP2 (
RESOURCE_ASSIGNMENT_ID,
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
ETC_PC_RAW_COST,
ETC_PC_BURDENED_COST,
ETC_PC_REVENUE,
TRANSACTION_SOURCE_CODE )
VALUES (
P_SRC_RES_ASG_ID,
P_TGT_RES_ASG_ID,
l_tot_currency_code_tab(i),
l_etc_quantity_tab(i),
l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i),
l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i),
l_transaction_source_code);
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
txn_currency_code,
SUM(NVL(total_plan_quantity,0)),
SUM(NVL(total_pc_raw_cost,0)),
SUM(NVL(total_pc_burdened_cost,0)),
SUM(NVL(total_pc_revenue,0))
BULK COLLECT INTO
l_tot_currency_code_tab,
l_tot_quantity_pc_tab,
l_tot_raw_cost_pc_tab,
l_tot_brdn_cost_pc_tab,
l_tot_revenue_pc_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = p_src_res_asg_id
AND transaction_source_code = p_etc_source_code
GROUP BY txn_currency_code;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
NVL(SUM( DECODE(l_rate_based_flag,
'Y', NVL(quantity,0),
'N', NVL(prj_raw_cost,0))),0)
INTO l_act_quantity_pc_sum
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = p_task_id
AND res_list_member_id = p_resource_list_member_id
AND data_type_code = 'ETC_FP';
SELECT task_id,
planning_start_date
INTO l_task_id,
l_planning_start_date
FROM pa_resource_assignments
WHERE resource_assignment_id = p_src_res_asg_id;
SELECT name INTO g_project_name from
PA_PROJECTS_ALL WHERE
project_id = p_fp_cols_tgt_rec.x_project_id;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
NVL(SUM(NVL(total_txn_raw_cost,0)),0),
NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
NVL(SUM(NVL(total_txn_revenue,0)),0),
NVL(SUM(NVL(total_pc_raw_cost,0)),0),
NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
NVL(SUM(NVL(total_pc_revenue,0)),0)
INTO l_txn_rate_quantity,
l_txn_rate_raw_cost,
l_txn_rate_brdn_cost,
l_txn_rate_revenue,
l_pc_rate_raw_cost,
l_pc_rate_brdn_cost,
l_pc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE resource_assignment_id = p_src_res_asg_id
AND txn_currency_code = l_tot_currency_code_tab(i)
AND transaction_source_code in ('FINANCIAL_PLAN' ,
'WORKPLAN_RESOURCES');
/* Bulk insert
If commitment is not included, record is inserted directly as 'ETC'
record,if commitment is to be considered, record is inserted as
'TOTAL_ETC' for further processing.*/
IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
l_transaction_source_code := 'TOTAL_ETC';
INSERT INTO PA_FP_CALC_AMT_TMP2 (
RESOURCE_ASSIGNMENT_ID,
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
ETC_PC_RAW_COST,
ETC_PC_BURDENED_COST,
ETC_PC_REVENUE,
TRANSACTION_SOURCE_CODE )
VALUES (
P_SRC_RES_ASG_ID,
P_TGT_RES_ASG_ID,
l_tot_currency_code_tab(i),
l_etc_quantity_tab(i),
l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i),
l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i),
l_transaction_source_code);
SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = p_tgt_res_asg_id
AND (transaction_source_code = 'FINANCIAL_PLAN'
OR transaction_source_code = 'WORKPLAN_RESOURCES'
OR transaction_source_code = 'COMMITMENT')
UNION
SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT act_tmp.txn_currency_code
FROM PA_FP_FCST_GEN_TMP1 act_tmp,
PA_FP_CALC_AMT_TMP2 tot_tmp
WHERE act_tmp.source_id = tot_tmp.resource_assignment_id
AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
) WHERE rownum <= 2;
user can't select include commitment option from the UI.
3.No matter for cost, revenue or all forecast version, always pick up cost/revenue rate
from the source whenever applicable. */
/* Bug 4369741: Replaced single planning options flag parameter with
* 2 separate parameters - 1 for Workplan and 1 for Financial Plan. */
PROCEDURE GET_ETC_COMMITMENT_AMTS
(P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_WP_PLANNING_OPTIONS_FLAG IN VARCHAR2, /* Added for Bug 4369741 */
P_FP_PLANNING_OPTIONS_FLAG IN VARCHAR2, /* Added for Bug 4369741 */
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_COMMITMENT_AMTS';
SELECT DISTINCT target_res_asg_id,
resource_assignment_id
FROM PA_FP_CALC_AMT_TMP2
WHERE TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
SELECT DISTINCT target_res_asg_id,
NULL
FROM PA_FP_CALC_AMT_TMP2
WHERE TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
tmp.resource_assignment_id src_res_asg_id
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp
WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES'
UNION ALL
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
NULL src_res_asg_id
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp
WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN';
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
tmp.resource_assignment_id src_res_asg_id
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp
WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN'
UNION ALL
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
NULL src_res_asg_id
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp
WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES';
* is Task Level Selection, check both P_WP_PLANNING_OPTIONS_FLAG
* and P_FP_PLANNING_OPTIONS_FLAG. */
IF P_FP_COLS_TGT_REC.x_gen_etc_src_code = 'FINANCIAL_PLAN' THEN
IF P_FP_PLANNING_OPTIONS_FLAG = 'Y' THEN
OPEN src_tgt_cur_wp_fp_opt_same;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ COUNT(*)
INTO l_cmt_count
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'OPEN_COMMITMENTS'
AND rownum = 1;
simply update the temp table from total_etc records to net etc records. */
IF l_cmt_count = 0 THEN
UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ PA_FP_CALC_AMT_TMP2
SET transaction_source_code = 'ETC'
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'TOTAL_ETC';
l_etc_currency_code_tab.delete;
l_etc_quantity_tab.delete;
l_cmt_currency_code_tab.delete;
l_cmt_quantity_tab.delete;
l_cmt_raw_cost_tab.delete;
l_cmt_brdn_cost_tab.delete;
l_etc_noncmt_quantity_tab.delete;
l_etc_noncmt_raw_cost_tab.delete;
l_etc_noncmt_brdn_cost_tab.delete;
l_etc_quantity_pc_tab.delete;
l_etc_raw_cost_pc_tab.delete;
l_etc_brdn_cost_pc_tab.delete;
l_cmt_quantity_pc_tab.delete;
l_cmt_raw_cost_pc_tab.delete;
l_cmt_brdn_cost_pc_tab.delete;
l_etc_noncmt_quantity_pc_tab.delete;
l_etc_noncmt_raw_cost_pc_tab.delete;
l_etc_noncmt_brdn_cost_pc_tab.delete;
l_txn_raw_cost_rate_tab.delete;
l_txn_brdn_cost_rate_tab.delete;
SELECT rate_based_flag
INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
COUNT(*) INTO l_currency_count_for_flag FROM (
SELECT DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'OPEN_COMMITMENTS'
MINUS
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT etc_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'TOTAL_ETC'
) WHERE rownum = 1;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
NVL(SUM(ETC_PLAN_QUANTITY),0)
INTO l_etc_quantity_pc_pfc
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0))
INTO l_cmt_quantity_pc_pfc,
l_cmt_raw_cost_pc_pfc,
l_cmt_brdn_cost_pc_pfc
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'OPEN_COMMITMENTS';
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
NVL(SUM(NVL(etc_plan_quantity,0)),0),
NVL(SUM(DECODE(l_currency_flag,
'PC', NVL(etc_pc_raw_cost,0),
'PFC', NVL(etc_pfc_raw_cost,0))),0),
NVL(SUM(DECODE(l_currency_flag,
'PC', NVL(etc_pc_burdened_cost,0),
'PFC', NVL(etc_pfc_burdened_cost,0))),0),
NVL(SUM(DECODE(l_currency_flag,
'PC', NVL(etc_pc_revenue,0),
'PFC', NVL(etc_pfc_revenue,0))),0)
INTO l_pc_pfc_rate_quantity,
l_pc_pfc_rate_raw_cost,
l_pc_pfc_rate_brdn_cost,
l_pc_pfc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'TOTAL_ETC';
/* Insert the single PC record for total ETC with source rates */
INSERT INTO PA_FP_CALC_AMT_TMP2 (
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
TRANSACTION_SOURCE_CODE,
TXN_CURRENCY_CODE, -- Added for Bug 5203622
RESOURCE_ASSIGNMENT_ID) -- added for bug 5359863
VALUES (
l_tgt_res_asg_id_tab(i),
DECODE(l_currency_flag, 'PC',l_pc_currency_code,
'PFC', l_pfc_currency_code),
l_etc_noncmt_quantity_pc_pfc,
l_etc_noncmt_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
l_etc_noncmt_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
l_etc_noncmt_quantity_pc_pfc * l_pc_pfc_revenue_rate,
'ETC',
l_other_rej_code, -- Added for Bug 5203622
l_src_res_asg_id_tab(i)); -- added for bug 5359863
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
etc_currency_code,
SUM(NVL(ETC_PLAN_QUANTITY,0))
BULK COLLECT INTO
l_etc_currency_code_tab,
l_etc_quantity_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
GROUP BY etc_currency_code;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
txn_currency_code,
SUM(NVL(total_plan_quantity,0)),
SUM(NVL(total_txn_raw_cost,0)),
SUM(NVL(total_txn_burdened_cost,0))
BULK COLLECT INTO
l_cmt_currency_code_tab,
l_cmt_quantity_tab,
l_cmt_raw_cost_tab,
l_cmt_brdn_cost_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'OPEN_COMMITMENTS'
GROUP BY txn_currency_code;
SELECT COUNT (*) INTO l_currency_count_cmt_min_tot
FROM (
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'OPEN_COMMITMENTS'
MINUS
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT etc_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'TOTAL_ETC'
);
l_etc_noncmt_quantity_tab.delete;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
NVL(SUM(NVL(etc_plan_quantity,0)),0),
NVL(SUM(NVL(etc_txn_raw_cost,0)),0),
NVL(SUM(NVL(etc_txn_burdened_cost,0)),0),
NVL(SUM(NVL(etc_txn_revenue,0)),0)
INTO l_txn_rate_quantity,
l_txn_rate_raw_cost,
l_txn_rate_brdn_cost,
l_txn_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND etc_currency_code = l_etc_currency_code_tab(k)
AND transaction_source_code = 'TOTAL_ETC';
/* Bulk insert for the ETC amounts for current target resource
assignment with source rates */
FORALL k IN 1..l_etc_currency_code_tab.count
INSERT INTO PA_FP_CALC_AMT_TMP2 (
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
TRANSACTION_SOURCE_CODE,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE ) -- Added for Bug 5203622
VALUES (
l_tgt_res_asg_id_tab(i),
l_etc_currency_code_tab(k),
l_etc_noncmt_quantity_tab(k),
l_etc_noncmt_quantity_tab(k) * l_txn_raw_cost_rate_tab(k),
l_etc_noncmt_quantity_tab(k) * l_txn_brdn_cost_rate_tab(k),
l_etc_noncmt_quantity_tab(k) * l_txn_revenue_rate_tab(k),
'ETC',
l_src_res_asg_id_tab(i),
l_other_rej_code_tab(k) ); -- Added for Bug 5203622
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
etc_currency_code,
SUM(NVL(etc_plan_quantity,0)),
SUM(NVL(etc_pc_raw_cost,0)),
SUM(NVL(etc_pc_burdened_cost,0)),
SUM(NVL(etc_pc_revenue,0)) -- Added in IPM
BULK COLLECT INTO
l_etc_currency_code_tab,
l_etc_quantity_pc_tab,
l_etc_raw_cost_pc_tab,
l_etc_brdn_cost_pc_tab,
l_etc_revenue_pc_tab -- Added in IPM
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'TOTAL_ETC'
GROUP BY etc_currency_code;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
txn_currency_code,
SUM(NVL(total_plan_quantity,0)),
SUM(NVL(total_pc_raw_cost,0)),
SUM(NVL(total_pc_burdened_cost,0))
BULK COLLECT INTO
l_cmt_currency_code_tab,
l_cmt_quantity_pc_tab,
l_cmt_raw_cost_pc_tab,
l_cmt_brdn_cost_pc_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND transaction_source_code = 'OPEN_COMMITMENTS'
GROUP BY txn_currency_code;
SELECT task_id,
planning_start_date
INTO l_task_id,
l_planning_start_date
FROM pa_resource_assignments
WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N1)*/
NVL(SUM(NVL(etc_plan_quantity,0)),0),
NVL(SUM(NVL(etc_txn_raw_cost,0)),0),
NVL(SUM(NVL(etc_txn_burdened_cost,0)),0),
NVL(SUM(NVL(etc_txn_revenue,0)),0)
INTO l_txn_rate_quantity,
l_txn_rate_raw_cost,
l_txn_rate_brdn_cost,
l_txn_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
AND etc_currency_code = l_etc_currency_code_tab(k)
AND transaction_source_code = 'TOTAL_ETC';
/* Bulk insert */
FORALL k IN 1..l_etc_noncmt_quantity_tab.count
INSERT INTO PA_FP_CALC_AMT_TMP2 (
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
TRANSACTION_SOURCE_CODE,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE ) -- Added for Bug 5203622
VALUES (
l_tgt_res_asg_id_tab(i),
l_etc_currency_code_tab(k),
l_etc_noncmt_quantity_tab(k),
l_etc_noncmt_quantity_tab(k) * l_txn_raw_cost_rate_tab(k),
l_etc_noncmt_quantity_tab(k) * l_txn_brdn_cost_rate_tab(k),
l_etc_noncmt_quantity_tab(k) * l_txn_revenue_rate_tab(k),
'ETC',
l_src_res_asg_id_tab(i),
l_other_rej_code_tab(k) ); -- Added for Bug 5203622
2.Rate based flag for target resource assignment gets updated correctly before coming
into any of ETC methods.
3.All considered scenarios:
Rate_based
non multi currency enabled: use PC
multi currency enabled
actuals currency is subset of total currency: use TC, currency based substraction
actuals currency is not subset of total currency: use TC, prorate ETC quantity
Non_rate_based
non multi currency enabled: use PC
multi currency enabled
actuals currency not subset of total currency: use TC, currency based substraction
actuals currency not subset of total currency: Compute ETC quantity in PC, prorate
this ETC quantity to different planning currencies based on PC amounts,
convert back from PC to TC.
*/
PROCEDURE GET_ETC_REMAIN_BDGT_AMTS_BLK
(P_SRC_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
P_TGT_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
P_FP_COLS_SRC_REC_FP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_FP_COLS_SRC_REC_WP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_TASK_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
P_RES_LIST_MEMBER_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
P_ETC_SOURCE_CODE_TAB IN PA_PLSQL_DATATYPES.Char30TabTyp,
P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
P_PLANNING_OPTIONS_FLAG 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(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_REMAIN_BDGT_AMTS_BLK';
/* Delete pl/sql tables for the current task being processed. */
l_tot_currency_code_tab.delete;
l_tot_quantity_tab.delete;
l_tot_raw_cost_tab.delete;
l_tot_brdn_cost_tab.delete;
l_tot_revenue_tab.delete;
l_act_currency_code_tab.delete;
l_act_quantity_tab.delete;
l_act_raw_cost_tab.delete;
l_act_brdn_cost_tab.delete;
l_act_revenue_tab.delete;
l_tot_quantity_pc_tab.delete;
l_tot_raw_cost_pc_tab.delete;
l_tot_brdn_cost_pc_tab.delete;
l_tot_revenue_pc_tab.delete;
l_etc_quantity_pc_tab.delete;
l_txn_raw_cost_rate_tab.delete;
l_txn_brdn_cost_rate_tab.delete;
l_txn_revenue_rate_tab.delete;
l_pc_raw_cost_rate_tab.delete;
l_pc_brdn_cost_rate_tab.delete;
l_pc_revenue_rate_tab.delete;
l_etc_quantity_tab.delete;
SELECT rate_based_flag
INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = l_tgt_res_asg_id;
SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT act_tmp.txn_currency_code
FROM PA_FP_FCST_GEN_TMP1 act_tmp,
PA_FP_CALC_AMT_TMP1 tot_tmp
WHERE act_tmp.project_element_id = tot_tmp.task_id
AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
AND tot_tmp.target_res_asg_id = l_tgt_res_asg_id
AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
'WORKPLAN_RESOURCES', 'ETC_WP',
'FINANCIAL_PLAN', 'ETC_FP')
MINUS
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE target_res_asg_id = l_tgt_res_asg_id
AND transaction_source_code = l_etc_source_code
) WHERE rownum = 1;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
NVL(SUM(NVL(
DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
'PFC', total_pfc_raw_cost),0)),0),
NVL(SUM(NVL(
DECODE(l_currency_flag, 'PC', total_pc_burdened_cost,
'PFC', total_pfc_burdened_cost),0)),0),
NVL(SUM(NVL(
DECODE(l_currency_flag, 'PC', total_pc_revenue,
'PFC', total_pfc_revenue),0)),0)
INTO l_tot_quantity_pc_pfc,
l_tot_raw_cost_pc_pfc,
l_tot_brdn_cost_pc_pfc,
l_tot_revenue_pc_pfc
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = l_src_res_asg_id
AND transaction_source_code = l_etc_source_code;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DECODE(l_currency_flag,
'PC', NVL(SUM(DECODE(l_rate_based_flag,
'Y', quantity,
'N', NVL(prj_raw_cost,0))),0),
'PFC', NVL(SUM(DECODE(l_rate_based_flag,
'Y', quantity,
'N', NVL(pou_raw_cost,0))),0)),
DECODE(l_currency_flag, -- Added for Bug 5203622
'PC', NVL(SUM(NVL(prj_raw_cost,0)),0),
'PFC', NVL(SUM(NVL(pou_raw_cost,0)),0))
INTO l_act_quantity_pc_pfc,
l_act_raw_cost_pc_pfc -- Added for Bug 5203622
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = l_curr_task_id
AND res_list_member_id = l_resource_list_member_id
AND data_type_code = 'ETC_FP';
target resource assignmentInsert the single PC record for total ETC.*/
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0)),
DECODE(l_currency_flag,
'PC', NVL(SUM(NVL(total_pc_revenue,0)),0),
'PFC', NVL(SUM(NVL(total_pfc_revenue,0)),0))
INTO l_pc_pfc_rate_quantity,
l_pc_pfc_rate_raw_cost,
l_pc_pfc_rate_brdn_cost,
l_pc_pfc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE resource_assignment_id = l_src_res_asg_id
AND transaction_source_code in ('FINANCIAL_PLAN',
'WORKPLAN_RESOURCES');
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
txn_currency_code,
SUM(NVL(total_plan_quantity,0)),
SUM(NVL(total_txn_raw_cost,0)),
SUM(NVL(total_txn_burdened_cost,0)),
SUM(NVL(total_txn_revenue,0))
BULK COLLECT INTO
l_tot_currency_code_tab,
l_tot_quantity_tab,
l_tot_raw_cost_tab,
l_tot_brdn_cost_tab,
l_tot_revenue_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = l_src_res_asg_id
AND transaction_source_code = l_etc_source_code
GROUP BY txn_currency_code;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
txn_currency_code,
SUM(NVL(quantity,0)),
SUM(NVL(txn_raw_cost,0)),
SUM(NVL(txn_brdn_cost,0)),
SUM(NVL(txn_revenue,0))
BULK COLLECT INTO
l_act_currency_code_tab,
l_act_quantity_tab,
l_act_raw_cost_tab,
l_act_brdn_cost_tab,
l_act_revenue_tab
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = l_curr_task_id
AND res_list_member_id = l_resource_list_member_id
AND data_type_code = 'ETC_FP'
GROUP BY txn_currency_code;
SELECT COUNT(*)
INTO l_currency_count_act_min_tot
FROM (
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT txn_currency_code
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = l_curr_task_id
AND res_list_member_id = l_resource_list_member_id
AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
'WORKPLAN_RESOURCES', 'ETC_WP',
'FINANCIAL_PLAN', 'ETC_FP')
MINUS
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT txn_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = l_src_res_asg_id
AND transaction_source_code = l_etc_source_code
) WHERE rownum = 1;
l_etc_quantity_tab.delete;
l_other_rej_code_tab.delete; -- Added for Bug 5203622
SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
NVL(SUM(NVL(total_txn_raw_cost,0)),0),
NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
NVL(SUM(NVL(total_txn_revenue,0)),0),
NVL(SUM(NVL(total_pc_raw_cost,0)),0),
NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
NVL(SUM(NVL(total_pc_revenue,0)),0)
INTO l_txn_rate_quantity,
l_txn_rate_raw_cost,
l_txn_rate_brdn_cost,
l_txn_rate_revenue,
l_pc_rate_raw_cost,
l_pc_rate_brdn_cost,
l_pc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE resource_assignment_id = l_src_res_asg_id
AND txn_currency_code = l_tot_currency_code_tab(i)
AND transaction_source_code in ('FINANCIAL_PLAN',
'WORKPLAN_RESOURCES');
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
txn_currency_code,
SUM(NVL(total_plan_quantity,0)),
SUM(NVL(total_pc_raw_cost,0)),
SUM(NVL(total_pc_burdened_cost,0)),
SUM(NVL(total_pc_revenue,0))
BULK COLLECT INTO
l_tot_currency_code_tab,
l_tot_quantity_pc_tab,
l_tot_raw_cost_pc_tab,
l_tot_brdn_cost_pc_tab,
l_tot_revenue_pc_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE resource_assignment_id = l_src_res_asg_id
AND transaction_source_code = l_etc_source_code
GROUP BY txn_currency_code;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
NVL(SUM( DECODE(l_rate_based_flag,
'Y', NVL(quantity,0),
'N', NVL(prj_raw_cost,0))),0),
NVL(SUM(NVL(prj_raw_cost,0)),0)
INTO l_act_quantity_pc_sum,
l_act_raw_cost_pc_sum -- Added for Bug 5203622
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = l_curr_task_id
AND res_list_member_id = l_resource_list_member_id
AND data_type_code = 'ETC_FP';
SELECT task_id,
planning_start_date
INTO l_task_id,
l_planning_start_date
FROM pa_resource_assignments
WHERE resource_assignment_id = l_src_res_asg_id;
SELECT name INTO g_project_name from
PA_PROJECTS_ALL WHERE
project_id = p_fp_cols_tgt_rec.x_project_id;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
NVL(SUM(NVL(total_plan_quantity,0)),0),
NVL(SUM(NVL(total_txn_raw_cost,0)),0),
NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
NVL(SUM(NVL(total_txn_revenue,0)),0),
NVL(SUM(NVL(total_pc_raw_cost,0)),0),
NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
NVL(SUM(NVL(total_pc_revenue,0)),0)
INTO l_txn_rate_quantity,
l_txn_rate_raw_cost,
l_txn_rate_brdn_cost,
l_txn_rate_revenue,
l_pc_rate_raw_cost,
l_pc_rate_brdn_cost,
l_pc_rate_revenue
FROM pa_fp_calc_amt_tmp2
WHERE resource_assignment_id = l_src_res_asg_id
AND txn_currency_code = l_tot_currency_code_tab(i)
AND transaction_source_code in ('FINANCIAL_PLAN' ,
'WORKPLAN_RESOURCES');
/* If commitment is not included, record is inserted directly as 'ETC'
record,if commitment is to be considered, record is inserted as
'TOTAL_ETC' for further processing.*/
IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
l_transaction_source_code := 'TOTAL_ETC';
INSERT INTO PA_FP_CALC_AMT_TMP2
( RESOURCE_ASSIGNMENT_ID,
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
ETC_TXN_REVENUE,
ETC_PC_RAW_COST,
ETC_PC_BURDENED_COST,
ETC_PC_REVENUE,
ETC_PFC_RAW_COST,
ETC_PFC_BURDENED_COST,
ETC_PFC_REVENUE,
TRANSACTION_SOURCE_CODE,
TXN_CURRENCY_CODE ) -- Added for Bug 5203622
VALUES ( l_ins_src_res_asg_id_tab(i),
l_ins_tgt_res_asg_id_tab(i),
l_ins_currency_code_tab(i),
l_ins_etc_quantity_tab(i),
l_ins_txn_raw_cost_tab(i),
l_ins_txn_burdened_cost_tab(i),
l_ins_txn_revenue_tab(i),
l_ins_pc_raw_cost_tab(i),
l_ins_pc_burdened_cost_tab(i),
l_ins_pc_revenue_tab(i),
l_ins_pfc_raw_cost_tab(i),
l_ins_pfc_burdened_cost_tab(i),
l_ins_pfc_revenue_tab(i),
l_transaction_source_code,
l_ins_other_rej_code_tab(i) ); -- Added for Bug 5203622