[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_CBS_ELEMENT_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp, --bug#16791711
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 nvl(act_tmp.cbs_element_id, -1) = nvl(tot_tmp.cbs_element_id, -1) --bug#16791711
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 nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
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 nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
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 nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
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 nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
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
* create the same in destination and then update the intermediate tmp2 with
* the etc values.
* The processing in this api happens in phases in first phase we direclty
* copy the budgetlines from the source plan if the time phase match and
* in second phase we distribute or club the amounts based on the time phases
* of source and destination.
*/
PROCEDURE GET_ETC_FROM_SRC_BDGT
(P_FP_COLS_SRC_FP_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_FP_COLS_SRC_WP_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
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.GET_ETC_FROM_SRC_BDGT';
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
NULL,
NULL
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra
WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
and ra.budget_version_id = c_target_bv_id
and sbl.end_date > P_ACTUALS_THRU_DATE
and sbl.cost_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null
and ra.project_id = c_project_id
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL;
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
NULL,
NULL
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra
WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
and ra.budget_version_id = c_target_bv_id
and sbl.end_date > P_ACTUALS_THRU_DATE
and sbl.cost_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null
and ra.project_id = c_project_id
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL;
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
NULL,
NULL
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
PA_GL_PA_PERIODS_TMP pa_gl
WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
and ra.budget_version_id = c_target_bv_id
and sbl.end_date > P_ACTUALS_THRU_DATE
and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
and sbl.cost_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null
and ra.project_id = c_project_id
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL;
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
NULL,
NULL
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
PA_GL_PA_PERIODS_TMP pa_gl
WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
and ra.budget_version_id = c_target_bv_id
and sbl.end_date > P_ACTUALS_THRU_DATE
and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
and sbl.cost_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null
and ra.project_id = c_project_id
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL;
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum((sbl.quantity)/pa_gl.multiplier),
sum((decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost))/pa_gl.multiplier),
sum((decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost))/pa_gl.multiplier),
sum((decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate)))/pa_gl.multiplier), --sbl.raw_cost
sum((decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate)))/pa_gl.multiplier), --sbl.burdened_cost
NULL,
NULL
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
PA_GL_PA_PERIODS_TMP pa_gl
WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
and ra.budget_version_id = c_target_bv_id
and sbl.end_date > P_ACTUALS_THRU_DATE
and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
and sbl.cost_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null
and ra.project_id = c_project_id
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL;
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum((sbl.quantity)/pa_gl.multiplier),
sum((decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost))/pa_gl.multiplier),
sum((decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost))/pa_gl.multiplier),
sum((decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate)))/pa_gl.multiplier), --sbl.raw_cost
sum((decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate)))/pa_gl.multiplier), --sbl.burdened_cost
NULL,
NULL
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
PA_GL_PA_PERIODS_TMP pa_gl
WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
and ra.budget_version_id = c_target_bv_id
and sbl.end_date > P_ACTUALS_THRU_DATE
and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
and sbl.cost_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null
and ra.project_id = c_project_id
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL;
l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE
:= FND_GLOBAL.user_id;
l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE
:= FND_GLOBAL.login_id;
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
END_DATE,
PERIOD_NAME,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE
)
VALUES (
pa_budget_lines_s.nextval,
P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
l_tgt_res_asg_id_tab(i),
l_start_date_tab(i),
l_txn_currency_code_tab(i),
l_txn_raw_cost_tab(i),
l_txn_brdn_cost_tab(i),
l_end_date_tab(i),
l_period_name_tab(i),
l_src_quantity_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
l_cost_rate_override_tab(i),
l_b_cost_rate_override_tab(i)
);
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
END_DATE,
PERIOD_NAME,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
RAW_COST_SOURCE,
BURDENED_COST_SOURCE,
QUANTITY_SOURCE)
VALUES (
pa_budget_lines_s.nextval,
P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
l_tgt_res_asg_id_tab(i),
l_start_date_tab(i),
l_txn_currency_code_tab(i),
l_txn_raw_cost_tab(i),
l_txn_brdn_cost_tab(i),
l_end_date_tab(i),
l_period_name_tab(i),
l_src_quantity_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
l_cost_rate_override_tab(i),
l_b_cost_rate_override_tab(i),
'SP',
'SP',
'SP');
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
END_DATE,
PERIOD_NAME,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
RAW_COST_SOURCE,
BURDENED_COST_SOURCE,
QUANTITY_SOURCE)
VALUES (
pa_budget_lines_s.nextval,
P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
l_tgt_res_asg_id_tab(i),
l_start_date_tab(i),
l_txn_currency_code_tab(i),
l_txn_raw_cost_tab(i),
l_txn_brdn_cost_tab(i),
l_end_date_tab(i),
l_period_name_tab(i),
l_src_quantity_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
l_cost_rate_override_tab(i),
l_b_cost_rate_override_tab(i),
'SP',
'SP',
'SP');
SELECT MAX(PLANNING_END_DATE)
INTO l_end_date
FROM PA_FP_CALC_AMT_TMP1;
SELECT MAX(pbl.end_date)
INTO l_end_date
FROM PA_BUDGET_LINES pbl,
PA_FP_CALC_AMT_TMP2 tmp
WHERE tmp.resource_assignment_id = pbl.resource_assignment_id ;
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
END_DATE,
PERIOD_NAME,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
RAW_COST_SOURCE,
BURDENED_COST_SOURCE,
QUANTITY_SOURCE)
VALUES (
pa_budget_lines_s.nextval,
P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
l_pr_tgt_res_asg_id_tab(i),
l_pr_start_date_tab(i),
l_pr_txn_currency_code_tab(i),
l_pr_txn_raw_cost_tab(i),
l_pr_txn_brdn_cost_tab(i),
l_pr_end_date_tab(i),
l_pr_period_name_tab(i),
l_pr_src_quantity_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
l_pr_cost_rate_override_tab(i),
l_pr_b_cost_rate_override_tab(i),
'SP',
'SP',
'SP');
USING ( SELECT NULL as BUDGET_LINE_ID,
P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID as BUDGET_VERSION_ID,
l_pr_tgt_res_asg_id_tab(i) as RESOURCE_ASSIGNMENT_ID,
l_pr_start_date_tab(i) as START_DATE,
l_pr_txn_currency_code_tab(i) as TXN_CURRENCY_CODE,
l_pr_txn_raw_cost_tab(i) as TXN_RAW_COST,
l_pr_txn_brdn_cost_tab(i) as TXN_BURDENED_COST,
l_pr_end_date_tab(i) as END_DATE,
l_pr_period_name_tab(i) as PERIOD_NAME,
l_pr_src_quantity_tab(i) as QUANTITY,
sysdate as LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID as LAST_UPDATED_BY,
sysdate as CREATION_DATE,
FND_GLOBAL.USER_ID as CREATED_BY,
FND_GLOBAL.LOGIN_ID as LAST_UPDATE_LOGIN,
P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE as PROJECT_CURRENCY_CODE,
P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE as PROJFUNC_CURRENCY_CODE,
l_pr_cost_rate_override_tab(i) as TXN_COST_RATE_OVERRIDE,
l_pr_b_cost_rate_override_tab(i) as BURDEN_COST_RATE_OVERRIDE ,
'SP' as RAW_COST_SOURCE,
'SP' as BURDENED_COST_SOURCE,
'SP' as QUANTITY_SOURCE
FROM dual) tmp
ON ( tmp.RESOURCE_ASSIGNMENT_ID = pbl.RESOURCE_ASSIGNMENT_ID AND
tmp.START_DATE = pbl.START_DATE AND
tmp.TXN_CURRENCY_CODE = pbl.TXN_CURRENCY_CODE)
WHEN MATCHED THEN
UPDATE
SET pbl.TXN_RAW_COST = nvl(pbl.TXN_RAW_COST,0) + nvl(tmp.TXN_RAW_COST,0)
,pbl.TXN_BURDENED_COST = nvl(pbl.TXN_BURDENED_COST,0) + nvl(tmp.TXN_BURDENED_COST,0)
,pbl.QUANTITY = nvl(pbl.QUANTITY,0) + nvl(tmp.QUANTITY,0)
,pbl.LAST_UPDATE_DATE = sysdate
,pbl.LAST_UPDATED_BY = FND_GLOBAL.USER_ID
,pbl.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHEN NOT MATCHED THEN
INSERT (
pbl.BUDGET_LINE_ID,
pbl.BUDGET_VERSION_ID,
pbl.RESOURCE_ASSIGNMENT_ID,
pbl.START_DATE,
pbl.TXN_CURRENCY_CODE,
pbl.TXN_RAW_COST,
pbl.TXN_BURDENED_COST,
pbl.END_DATE,
pbl.PERIOD_NAME,
pbl.QUANTITY,
pbl.LAST_UPDATE_DATE,
pbl.LAST_UPDATED_BY,
pbl.CREATION_DATE,
pbl.CREATED_BY,
pbl.LAST_UPDATE_LOGIN,
pbl.PROJECT_CURRENCY_CODE,
pbl.PROJFUNC_CURRENCY_CODE,
pbl.TXN_COST_RATE_OVERRIDE,
pbl.BURDEN_COST_RATE_OVERRIDE,
pbl.RAW_COST_SOURCE,
pbl.BURDENED_COST_SOURCE,
pbl.QUANTITY_SOURCE)
VALUES (
pa_budget_lines_s.nextval,
tmp.BUDGET_VERSION_ID,
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.START_DATE,
tmp.TXN_CURRENCY_CODE,
tmp.TXN_RAW_COST,
tmp.TXN_BURDENED_COST,
tmp.END_DATE,
tmp.PERIOD_NAME,
tmp.QUANTITY,
tmp.LAST_UPDATE_DATE,
tmp.LAST_UPDATED_BY,
tmp.CREATION_DATE,
tmp.CREATED_BY,
tmp.LAST_UPDATE_LOGIN,
tmp.PROJECT_CURRENCY_CODE,
tmp.PROJFUNC_CURRENCY_CODE,
tmp.TXN_COST_RATE_OVERRIDE,
tmp.BURDEN_COST_RATE_OVERRIDE,
tmp.RAW_COST_SOURCE,
tmp.BURDENED_COST_SOURCE,
tmp.QUANTITY_SOURCE);
USING ( SELECT NULL as BUDGET_LINE_ID,
P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID as BUDGET_VERSION_ID,
l_pr_tgt_res_asg_id_tab(i) as RESOURCE_ASSIGNMENT_ID,
l_pr_start_date_tab(i) as START_DATE,
l_pr_txn_currency_code_tab(i) as TXN_CURRENCY_CODE,
l_pr_txn_raw_cost_tab(i) as TXN_RAW_COST,
l_pr_txn_brdn_cost_tab(i) as TXN_BURDENED_COST,
l_pr_end_date_tab(i) as END_DATE,
l_pr_period_name_tab(i) as PERIOD_NAME,
l_pr_src_quantity_tab(i) as QUANTITY,
sysdate as LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID as LAST_UPDATED_BY,
sysdate as CREATION_DATE,
FND_GLOBAL.USER_ID as CREATED_BY,
FND_GLOBAL.LOGIN_ID as LAST_UPDATE_LOGIN,
P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE as PROJECT_CURRENCY_CODE,
P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE as PROJFUNC_CURRENCY_CODE,
l_pr_cost_rate_override_tab(i) as TXN_COST_RATE_OVERRIDE,
l_pr_b_cost_rate_override_tab(i) as BURDEN_COST_RATE_OVERRIDE,
'SP' as RAW_COST_SOURCE,
'SP' as BURDENED_COST_SOURCE,
'SP' as QUANTITY_SOURCE
FROM dual) tmp
ON ( tmp.RESOURCE_ASSIGNMENT_ID = pbl.RESOURCE_ASSIGNMENT_ID AND
tmp.START_DATE = pbl.START_DATE AND
tmp.TXN_CURRENCY_CODE = pbl.TXN_CURRENCY_CODE)
WHEN MATCHED THEN
UPDATE
SET pbl.TXN_RAW_COST = nvl(pbl.TXN_RAW_COST,0) + nvl(tmp.TXN_RAW_COST,0)
,pbl.TXN_BURDENED_COST = nvl(pbl.TXN_BURDENED_COST,0) + nvl(tmp.TXN_BURDENED_COST,0)
,pbl.QUANTITY = nvl(pbl.QUANTITY,0) + nvl(tmp.QUANTITY,0)
,pbl.LAST_UPDATE_DATE = sysdate
,pbl.LAST_UPDATED_BY = FND_GLOBAL.USER_ID
,pbl.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHEN NOT MATCHED THEN
INSERT (
pbl.BUDGET_LINE_ID,
pbl.BUDGET_VERSION_ID,
pbl.RESOURCE_ASSIGNMENT_ID,
pbl.START_DATE,
pbl.TXN_CURRENCY_CODE,
pbl.TXN_RAW_COST,
pbl.TXN_BURDENED_COST,
pbl.END_DATE,
pbl.PERIOD_NAME,
pbl.QUANTITY,
pbl.LAST_UPDATE_DATE,
pbl.LAST_UPDATED_BY,
pbl.CREATION_DATE,
pbl.CREATED_BY,
pbl.LAST_UPDATE_LOGIN,
pbl.PROJECT_CURRENCY_CODE,
pbl.PROJFUNC_CURRENCY_CODE,
pbl.TXN_COST_RATE_OVERRIDE,
pbl.BURDEN_COST_RATE_OVERRIDE,
pbl.RAW_COST_SOURCE,
pbl.BURDENED_COST_SOURCE,
pbl.QUANTITY_SOURCE)
VALUES (
pa_budget_lines_s.nextval,
tmp.BUDGET_VERSION_ID,
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.START_DATE,
tmp.TXN_CURRENCY_CODE,
tmp.TXN_RAW_COST,
tmp.TXN_BURDENED_COST,
tmp.END_DATE,
tmp.PERIOD_NAME,
tmp.QUANTITY,
tmp.LAST_UPDATE_DATE,
tmp.LAST_UPDATED_BY,
tmp.CREATION_DATE,
tmp.CREATED_BY,
tmp.LAST_UPDATE_LOGIN,
tmp.PROJECT_CURRENCY_CODE,
tmp.PROJFUNC_CURRENCY_CODE,
tmp.TXN_COST_RATE_OVERRIDE,
tmp.BURDEN_COST_RATE_OVERRIDE,
tmp.RAW_COST_SOURCE,
tmp.BURDENED_COST_SOURCE,
tmp.QUANTITY_SOURCE);
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
( resource_assignment_id,
txn_currency_code,
txn_raw_cost_rate_override,
txn_burden_cost_rate_override,
txn_bill_rate_override )
SELECT DISTINCT
bl.resource_assignment_id,
bl.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override,
rbc.txn_bill_rate_override
FROM pa_resource_assignments ra,
pa_budget_lines bl,
pa_resource_asgn_curr rbc,
PA_FP_CALC_AMT_TMP2 tmp4
WHERE ra.budget_version_id = P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID
AND ra.project_id = P_FP_COLS_TGT_REC.x_project_id
AND ra.resource_assignment_id = tmp4.target_res_asg_id
AND bl.resource_assignment_id = ra.resource_assignment_id
AND bl.resource_assignment_id = rbc.resource_assignment_id (+)
AND bl.txn_currency_code = rbc.txn_currency_code (+);
INSERT INTO PA_FP_CALC_AMT_TMP2
( --RESOURCE_ASSIGNMENT_ID, -- Bug 8346446
TARGET_RES_ASG_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
TRANSACTION_SOURCE_CODE
)
SELECT --tmp4.resource_assignment_id, -- bug 8346446
distinct
ra.resource_assignment_id,
sbl.txn_currency_code,
sum(sbl.quantity),
sum(sbl.txn_raw_cost),
sum(sbl.txn_burdened_cost),
'ETC'
FROM PA_FP_CALC_AMT_TMP2 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra
WHERE tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
AND sbl.resource_assignment_id=ra.resource_assignment_id
AND ra.budget_version_id = P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID
AND ra.project_id = P_FP_COLS_TGT_REC.x_project_id
AND ra.budget_version_id = sbl.budget_version_id
AND sbl.init_quantity IS NULL
GROUP BY
tmp4.resource_assignment_id,
ra.resource_assignment_id,
sbl.txn_currency_code,
'ETC';
INSERT
INTO PA_GL_PA_PERIODS_TMP
(
PA_PERIOD_NAME ,
GL_PERIOD_NAME ,
PA_START_DATE ,
PA_END_DATE ,
GL_START_DATE ,
GL_END_DATE
)
(SELECT PAP.PERIOD_NAME ,
PAP.GL_PERIOD_NAME,
PAP.START_DATE ,
PAP.END_DATE ,
GLP.START_DATE ,
GLP.END_DATE
FROM PA_PERIODS_ALL PAP ,
GL_PERIODS GLP ,
GL_SETS_OF_BOOKS GSOB ,
PA_IMPLEMENTATIONS_ALL PAIMP
WHERE PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
AND GLP.PERIOD_SET_NAME = GSOB.PERIOD_SET_NAME
AND GSOB.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
AND p_start_date <= LEAST(PAP.END_DATE,GLP.END_DATE)
AND p_end_date >= GREATEST(PAP.START_DATE,GLP.START_DATE)
AND PAIMP.org_id = PAP.org_id
AND PAP.org_id = p_org_id
);
SELECT 'Y'
INTO l_is_gl_greater
FROM
(SELECT COUNT(*)
FROM PA_GL_PA_PERIODS_TMP
GROUP BY GL_PERIOD_NAME
HAVING COUNT(*) > 1
)
WHERE rownum = 1;
UPDATE PA_GL_PA_PERIODS_TMP tmp1
SET multiplier =
(SELECT COUNT(*)
FROM PA_GL_PA_PERIODS_TMP tmp2
WHERE tmp1.GL_PERIOD_NAME = tmp2.GL_PERIOD_NAME
GROUP BY GL_PERIOD_NAME
);
UPDATE PA_GL_PA_PERIODS_TMP tmp1
SET multiplier =
(SELECT COUNT(*)
FROM PA_GL_PA_PERIODS_TMP tmp2
WHERE tmp1.PA_PERIOD_NAME = tmp2.PA_PERIOD_NAME
GROUP BY PA_PERIOD_NAME
);