[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rate_based_flag
INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = p_tgt_res_asg_id;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DECODE(l_currency_flag,
'PC',l_pc_currency_code,
'TC',txn_currency_code,
'PFC',l_pfc_currency_code),
SUM(NVL(quantity,0)),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0),
'PFC', NVL(pou_raw_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_brdn_cost,0),
'TC', NVL(txn_brdn_cost,0),
'PFC', NVL(pou_brdn_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_revenue,0),
'TC', NVL(txn_revenue,0),
'PFC', NVL(pou_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 DECODE(l_currency_flag,
'PC',l_pc_currency_code,
'TC',txn_currency_code,
'PFC', l_pfc_currency_code);
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
SUM(NVL(quantity,0)),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0),
'PFC', NVL(pou_raw_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_brdn_cost,0),
'TC', NVL(txn_brdn_cost,0),
'PFC', NVL(pou_brdn_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_revenue,0),
'TC', NVL(txn_revenue,0),
'PFC', NVL(pou_revenue,0))),
SUM(NVL(prj_raw_cost,0)),
SUM(NVL(prj_brdn_cost,0)),
SUM(NVL(prj_revenue,0)),
SUM(NVL(pou_raw_cost,0)),
SUM(NVL(pou_brdn_cost,0)),
SUM(NVL(pou_revenue,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,
l_pfc_rate_raw_cost,
l_pfc_rate_brdn_cost,
l_pfc_rate_revenue
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'
AND DECODE(l_currency_flag, 'TC',txn_currency_code,
'PC', l_act_currency_code_tab(i),
'PFC',l_act_currency_code_tab(i)) = l_act_currency_code_tab(i);
/* Insert total ETC amounts */
/* 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,
l_act_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_etc_quantity_tab(i) * l_pfc_raw_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_pfc_brdn_cost_rate_tab(i),
l_etc_quantity_tab(i) * l_pfc_revenue_rate_tab(i),
l_transaction_source_code);
/*Added for Bulk insert at version level*/
l_blk_src_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
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_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_pfc_raw_cost_rate_tab.delete;
l_pfc_brdn_cost_rate_tab.delete;
l_pfc_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 = p_tgt_res_asg_id_tab(main_loop);
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DECODE(l_currency_flag,
'PC',l_pc_currency_code,
'TC',txn_currency_code,
'PFC',l_pfc_currency_code),
SUM(NVL(quantity,0)),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0),
'PFC', NVL(pou_raw_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_brdn_cost,0),
'TC', NVL(txn_brdn_cost,0),
'PFC', NVL(pou_brdn_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_revenue,0),
'TC', NVL(txn_revenue,0),
'PFC', NVL(pou_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_tab(main_loop)
AND res_list_member_id = p_res_list_member_id_tab(main_loop)
AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID_TAB(main_loop),-1) --bug#16791711
AND data_type_code = 'ETC_FP'
GROUP BY DECODE(l_currency_flag,
'PC',l_pc_currency_code,
'TC',txn_currency_code,
'PFC', l_pfc_currency_code);
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
SUM(NVL(quantity,0)),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0),
'PFC', NVL(pou_raw_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_brdn_cost,0),
'TC', NVL(txn_brdn_cost,0),
'PFC', NVL(pou_brdn_cost,0))),
SUM(DECODE(l_currency_flag,
'PC', NVL(prj_revenue,0),
'TC', NVL(txn_revenue,0),
'PFC', NVL(pou_revenue,0))),
SUM(NVL(prj_raw_cost,0)),
SUM(NVL(prj_brdn_cost,0)),
SUM(NVL(prj_revenue,0)),
SUM(NVL(pou_raw_cost,0)),
SUM(NVL(pou_brdn_cost,0)),
SUM(NVL(pou_revenue,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,
l_pfc_rate_raw_cost,
l_pfc_rate_brdn_cost,
l_pfc_rate_revenue
FROM PA_FP_FCST_GEN_TMP1
WHERE project_element_id = p_task_id_tab(main_loop)
AND res_list_member_id = p_res_list_member_id_tab(main_loop)
AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID_TAB(main_loop),-1) --bug#16791711
AND data_type_code = 'ETC_FP'
AND DECODE(l_currency_flag, 'TC',txn_currency_code,
'PC', l_act_currency_code_tab(i),
'PFC',l_act_currency_code_tab(i)) = l_act_currency_code_tab(i);
/* Insert total ETC amounts */
/* 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 (
l_blk_src_res_asg_id_tab(i),
l_blk_tgt_res_asg_id_tab(i),
l_blk_act_currency_code_tab(i),
l_blk_etc_quantity_tab(i),
l_blk_etc_txn_rcost_tab(i),
l_blk_etc_txn_bcost_tab(i),
l_blk_etc_txn_revenue_tab(i),
l_blk_etc_pc_rcost_tab(i),
l_blk_etc_pc_bcost_tab(i),
l_blk_etc_pc_revenue_tab(i),
l_blk_etc_pfc_rcost_tab(i),
l_blk_etc_pfc_bcost_tab(i),
l_blk_etc_pfc_revenue_tab(i),
l_transaction_source_code);