[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* This procedure updates planning txn level override rates
* for NON-RATE-BASED txns in the following ways:
* 1. In Cost and Revenue together target versions,
* for non-rate-based txns with only revenue amounts:
* a. Set bill rate override to 1
* b. Set cost rate overrides to 0
* 2. Null out any existing rate overrides for non-rate-based
* txns that do not have any budget lines.
*
* IMPORTANT NOTE:
* This procedure should only be called before the final
* rollup of amounts in the pa_resource_asgn_curr table.
* The impact of calling this API out of order is that rolled
* up amounts and average rates will be nulled out for
* updated planning txns!
*
* Also worth noting is that this procedure is package-private.
*/
PROCEDURE UPD_NRB_TXN_OVR_RATES
(P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_ETC_START_DATE IN DATE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
l_package_name VARCHAR2(30) := 'PA_FP_GEN_FCST_AMT_PUB1';
SELECT bl.resource_assignment_id,
bl.txn_currency_code
FROM pa_resource_assignments ra,
pa_budget_lines bl
WHERE ra.budget_version_id = p_budget_version_id
AND ra.project_id = p_project_id
AND ra.rate_based_flag = 'N'
AND bl.resource_assignment_id = ra.resource_assignment_id
AND bl.cost_rejection_code is null
AND bl.revenue_rejection_code is null
AND bl.burden_rejection_code is null
AND bl.other_rejection_code is null
AND bl.pc_cur_conv_rejection_code is null
AND bl.pfc_cur_conv_rejection_code is null
GROUP BY bl.resource_assignment_id,
bl.txn_currency_code
HAVING nvl(sum(bl.txn_raw_cost),0)-nvl(sum(bl.txn_init_raw_cost),0) = 0
and nvl(sum(bl.quantity),0)-nvl(sum(bl.init_quantity),0) <> 0
and nvl(sum(bl.quantity),0)-nvl(sum(bl.init_quantity),0) =
nvl(sum(bl.txn_revenue),0)-nvl(sum(bl.txn_init_revenue),0);
SELECT rbc.resource_assignment_id,
rbc.txn_currency_code
FROM pa_resource_assignments ra,
pa_resource_asgn_curr rbc
WHERE ra.budget_version_id = p_budget_version_id
AND ra.project_id = p_project_id
AND ra.rate_based_flag = 'N'
AND rbc.resource_assignment_id = ra.resource_assignment_id
AND NOT EXISTS
( SELECT null
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = rbc.resource_assignment_id
AND bl.txn_currency_code = rbc.txn_currency_code
AND nvl(bl.quantity,0) <> nvl(bl.init_quantity,0) )
AND ( rbc.txn_raw_cost_rate_override is not null OR
rbc.txn_burden_cost_rate_override is not null OR
rbc.txn_bill_rate_override is not null );
l_rbc_tmp_tbl_deleted_flag VARCHAR2(1);
l_rbc_tmp_tbl_deleted_flag := 'N';
( p_msg => 'Beginning Update Case 1',
--p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
* Update Case 1:
* In Cost and Revenue together target versions
* for non-rate-based txns with only revenue amounts:
* a. Set bill rate override to 1
* b. Set cost rate overrides to 0
*
* Background:
* By default, quantity = txn_raw_cost for non-rate-based
* txns at the periodic line level. In IPM, it is possible
* to have non-rate-based txns with just revenue amounts
* (without cost amounts); quantity = txn_revenue in such
* cases. The problem is that when users update or refresh
* the revenue for such txns, the Calculate API defaults
* quantity to raw cost in the absence of txn-level rate
* overrides, which is functionally incorrect. If txn-level
* rate overrides are set according to (a) and (b) above,
* then the Calculate API behaves correctly.
**/
IF p_fp_cols_rec.x_version_type = 'ALL' THEN
-- Get distinct (resource_assignment_id,txn_currency_code)
-- values for non-rate-based txns having only revenue amounts.
OPEN rev_only_nrb_txns_csr;
IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
DELETE pa_resource_asgn_curr_tmp;
l_rbc_tmp_tbl_deleted_flag := 'Y';
( p_msg => 'Records Deleted from pa_resource_asgn_curr_tmp',
--p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
END IF; -- IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
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 )
VALUES
( l_res_asg_id_tab(i),
l_txn_currency_code_tab(i),
0, -- txn_raw_cost_rate_override
0, -- txn_burden_cost_rate_override
1 ); -- txn_bill_rate_override
( p_msg => 'Number of records Inserted into ' ||
'PA_RESOURCE_ASGN_CURR_TMP:['||sql%Rowcount||']',
--p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
/* End Update Case 1 */
IF p_pa_debug_mode = 'Y' THEN
PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
( p_msg => 'Beginning Update Case 2',
--p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
* Update Case 2:
* Null out any existing rate overrides for non-rate-based
* txns that do not have any budget lines.
*
* Background:
* In IPM, non-rate-based txns are considered 'amount-based'
* and rates are not functionally meaningful. Thus, internal
* txn-level rates are not displayed to users. When users
* enter amounts on a blank line (i.e. for a txn that does
* not have any budget lines), calculation of amounts should
* not be affected by old internal txn-level rates.
*
* Open/Closed Issues:
* 1. Does this apply only to ETC budget lines?
* Answer: Yes. Since actuals are read-only, the absence of
* ETC budget lines would fall under the blank line scenario.
* 2. Should this consider only lines w/o rejections?
* Answer: No. Here's an example where rates should be retained:
* Src/Tgt options match. Src has cost rate overrides
* that get copied to tgt. However, bill rate is missing
* for the txn, so revenue_rejection_code stamped. We
* should not null out the cost rates in this case.
**/
-- Get distinct (resource_assignment_id,txn_currency_code)
-- values for non-rate-based txns that have existing txn-level
-- rate overrides but no (ETC) budget lines.
OPEN nrb_txns_without_bl_csr;
IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
DELETE pa_resource_asgn_curr_tmp;
l_rbc_tmp_tbl_deleted_flag := 'Y';
( p_msg => 'Records Deleted from pa_resource_asgn_curr_tmp',
--p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
END IF; -- IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
INSERT INTO PA_RESOURCE_ASGN_CURR_TMP
( resource_assignment_id,
txn_currency_code )
VALUES
( l_res_asg_id_tab(i),
l_txn_currency_code_tab(i) );
( p_msg => 'Number of records Inserted into ' ||
'PA_RESOURCE_ASGN_CURR_TMP:['||sql%Rowcount||']',
--p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
/* End Update Case 2 */
-- Call MAINTAIN_DATA to Insert rate overrides into the
-- pa_resource_asgn_curr table if required.
-- Note: temp table data should be completed populated
-- by this point by the preceding Update Cases.
IF l_maint_data_ins_req_flag = 'Y' THEN
-- Call the maintenance api in INSERT mode
IF p_pa_debug_mode = 'Y' THEN
PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
'MAINTAIN_DATA',
--P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name);
P_ROLLUP_FLAG => 'N', -- 'N' indicates Insert
--P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
SELECT bl.period_name,
NVL(bl.txn_cost_rate_override,bl.txn_standard_cost_rate),
NVL(bl.burden_cost_rate_override,bl.burden_cost_rate),
NVL(bl.txn_bill_rate_override,bl.txn_standard_bill_rate)
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = c_res_asg_id
AND bl.txn_currency_code = p_txn_currency_code;
/* Bug 4117267 added TARGET_RES_ASG_ID column in the INSERT stmt. */
FORALL i IN 1..l_period_name_tab.count
INSERT INTO PA_FP_GEN_RATE_TMP
( SOURCE_RES_ASG_ID,
TXN_CURRENCY_CODE,
PERIOD_NAME,
RAW_COST_RATE,
BURDENED_COST_RATE,
REVENUE_BILL_RATE,
TARGET_RES_ASG_ID)
VALUES
( p_source_res_asg_id,
p_txn_currency_code,
l_period_name_tab(i),
l_raw_cost_rate_tab(i),
l_burdened_cost_rate_tab(i),
l_revenue_bill_rate_tab(i),
p_target_res_asg_id );
* different units of measurement mapped to them. Such target txns are updated
* in the pa_resource_assignments table to be non rate-based with UOM equal to
* currency.
*
* Currently the P_FP_COLS_REC parameter is unused. This, however, will likely
* change with future modifications.
*/
PROCEDURE CHK_UPD_RATE_BASED_FLAG
(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
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_pub1.chk_upd_rate_based_flag';
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT(tmp.target_res_asg_id)
FROM pa_fp_calc_amt_tmp1 tmp,
pa_resource_assignments ra
WHERE ra.resource_assignment_id = tmp.target_res_asg_id
and ra.budget_version_id = p_budget_version_id
and ra.rate_based_flag = 'Y'
GROUP BY tmp.target_res_asg_id
HAVING COUNT(DISTINCT(tmp.unit_of_measure)) > 1;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
UPDATE pa_resource_assignments
SET rate_based_flag = 'N',
unit_of_measure = l_currency_code,
last_update_date = SYSDATE,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE resource_assignment_id = l_res_asg_id_tab(i);
select count(*) into l_count from PJI_FM_XBS_ACCUM_TMP1;
/* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
* (p_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
* This logic is not handled by the PJI generic resource mapping API. */
SELECT NVL(uncategorized_flag,'N')
INTO l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
UPDATE pji_fm_xbs_accum_tmp1
SET res_list_member_id = l_rlm_id;
INSERT INTO PA_FP_FCST_GEN_TMP1 (
PROJECT_ID,
STRUCT_VERSION_ID,
PROJECT_ELEMENT_ID,
CALENDAR_TYPE,
PERIOD_NAME,
PLAN_VERSION_ID,
RES_LIST_MEMBER_ID,
QUANTITY,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BRDN_COST,
TXN_REVENUE,
TXN_LABOR_RAW_COST,
TXN_LABOR_BRDN_COST,
TXN_EQUIP_RAW_COST,
TXN_EQUIP_BRDN_COST,
TXN_BASE_RAW_COST,
TXN_BASE_BRDN_COST,
TXN_BASE_LABOR_RAW_COST,
TXN_BASE_LABOR_BRDN_COST,
TXN_BASE_EQUIP_RAW_COST,
TXN_BASE_EQUIP_BRDN_COST,
PRJ_RAW_COST,
PRJ_BRDN_COST,
PRJ_REVENUE,
PRJ_LABOR_RAW_COST,
PRJ_LABOR_BRDN_COST,
PRJ_EQUIP_RAW_COST,
PRJ_EQUIP_BRDN_COST,
PRJ_BASE_RAW_COST,
PRJ_BASE_BRDN_COST,
PRJ_BASE_LABOR_RAW_COST,
PRJ_BASE_LABOR_BRDN_COST,
PRJ_BASE_EQUIP_RAW_COST,
PRJ_BASE_EQUIP_BRDN_COST,
POU_RAW_COST,
POU_BRDN_COST,
POU_REVENUE,
POU_LABOR_RAW_COST,
POU_LABOR_BRDN_COST,
POU_EQUIP_RAW_COST,
POU_EQUIP_BRDN_COST,
POU_BASE_RAW_COST,
POU_BASE_BRDN_COST,
POU_BASE_LABOR_RAW_COST,
POU_BASE_LABOR_BRDN_COST,
POU_BASE_EQUIP_RAW_COST,
POU_BASE_EQUIP_BRDN_COST,
LABOR_HOURS,
EQUIPMENT_HOURS,
SOURCE_ID,
DATA_TYPE_CODE )
(SELECT PROJECT_ID,
STRUCT_VERSION_ID,
PROJECT_ELEMENT_ID,
CALENDAR_TYPE,
PERIOD_NAME,
PLAN_VERSION_ID,
RES_LIST_MEMBER_ID,
QUANTITY,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BRDN_COST,
TXN_REVENUE,
TXN_LABOR_RAW_COST,
TXN_LABOR_BRDN_COST,
TXN_EQUIP_RAW_COST,
TXN_EQUIP_BRDN_COST,
TXN_BASE_RAW_COST,
TXN_BASE_BRDN_COST,
TXN_BASE_LABOR_RAW_COST,
TXN_BASE_LABOR_BRDN_COST,
TXN_BASE_EQUIP_RAW_COST,
TXN_BASE_EQUIP_BRDN_COST,
PRJ_RAW_COST,
PRJ_BRDN_COST,
PRJ_REVENUE,
PRJ_LABOR_RAW_COST,
PRJ_LABOR_BRDN_COST,
PRJ_EQUIP_RAW_COST,
PRJ_EQUIP_BRDN_COST,
PRJ_BASE_RAW_COST,
PRJ_BASE_BRDN_COST,
PRJ_BASE_LABOR_RAW_COST,
PRJ_BASE_LABOR_BRDN_COST,
PRJ_BASE_EQUIP_RAW_COST,
PRJ_BASE_EQUIP_BRDN_COST,
POU_RAW_COST,
POU_BRDN_COST,
POU_REVENUE,
POU_LABOR_RAW_COST,
POU_LABOR_BRDN_COST,
POU_EQUIP_RAW_COST,
POU_EQUIP_BRDN_COST,
POU_BASE_RAW_COST,
POU_BASE_BRDN_COST,
POU_BASE_LABOR_RAW_COST,
POU_BASE_LABOR_BRDN_COST,
POU_BASE_EQUIP_RAW_COST,
POU_BASE_EQUIP_BRDN_COST,
LABOR_HOURS,
EQUIPMENT_HOURS,
SOURCE_ID,
P_DATA_TYPE_CODE
FROM PJI_FM_XBS_ACCUM_TMP1 );
SELECT ra.resource_assignment_id,
ra.resource_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
p_task_id
FROM pa_resource_assignments ra
WHERE c_task_id_flag = 'Y' AND
ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.task_id = P_TASK_ID
UNION ALL
SELECT ra.resource_assignment_id,
ra.resource_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
ra.task_id
FROM pa_resource_assignments ra
WHERE c_task_id_flag = 'N' AND
ra.budget_version_id = P_BUDGET_VERSION_ID;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT ra.resource_assignment_id,
tmp.res_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
tmp.project_element_id
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_resource_assignments ra
WHERE tmp.project_element_id = p_task_id AND
ra.budget_version_id = P_BUDGET_VERSION_ID AND
NVL(ra.task_id,0) = 0 AND
ra.resource_list_member_id = tmp.res_list_member_id;
SELECT ra.resource_assignment_id,
ra.resource_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
p_task_id
FROM pa_resource_assignments ra
WHERE c_task_id_flag = 'Y' AND
ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.task_id = P_TASK_ID
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date > p_actuals_thru_date
AND rownum < 2 )))
UNION ALL
SELECT ra.resource_assignment_id,
ra.resource_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
ra.task_id
FROM pa_resource_assignments ra
WHERE c_task_id_flag = 'N' AND
ra.budget_version_id = P_BUDGET_VERSION_ID
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date > p_actuals_thru_date
AND rownum < 2 )));
SELECT ra.resource_assignment_id,
ra.resource_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
p_task_id
FROM pa_resource_assignments ra
WHERE c_task_id_flag = 'Y' AND
ra.budget_version_id = P_BUDGET_VERSION_ID
AND ra.task_id = P_TASK_ID
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND NVL(quantity,0) <> NVL(init_quantity,0)
AND rownum < 2 )))
UNION ALL
SELECT ra.resource_assignment_id,
ra.resource_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
ra.task_id
FROM pa_resource_assignments ra
WHERE c_task_id_flag = 'N' AND
ra.budget_version_id = P_BUDGET_VERSION_ID
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND NVL(quantity,0) <> NVL(init_quantity,0)
AND rownum < 2 )));
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT ra.resource_assignment_id,
tmp.res_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
tmp.project_element_id
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_resource_assignments ra
WHERE tmp.project_element_id = p_task_id AND
ra.budget_version_id = P_BUDGET_VERSION_ID AND
NVL(ra.task_id,0) = 0 AND
ra.resource_list_member_id = tmp.res_list_member_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date > p_actuals_thru_date
AND rownum < 2 )));
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT ra.resource_assignment_id,
tmp.res_list_member_id,
ra.planning_start_date,
ra.planning_end_date,
tmp.project_element_id
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_resource_assignments ra
WHERE tmp.project_element_id = p_task_id AND
ra.budget_version_id = P_BUDGET_VERSION_ID AND
NVL(ra.task_id,0) = 0 AND
ra.resource_list_member_id = tmp.res_list_member_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND NVL(quantity,0) <> NVL(init_quantity,0)
AND rownum < 2 )));
SELECT start_date
FROM pa_periods_all
WHERE period_name = c_period
AND org_id = p_fp_cols_rec.x_org_id;
SELECT start_date
FROM gl_period_statuses
WHERE period_name = c_period
AND application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N';
SELECT COUNT(DISTINCT txn_currency_code)
INTO l_currency_count
FROM PA_BUDGET_LINES
WHERE resource_assignment_id = l_res_asg_id_tab(i)
AND start_date BETWEEN l_actual_from_date AND l_actual_to_date;
SELECT DISTINCT txn_currency_code
INTO l_currency_code
FROM PA_BUDGET_LINES
WHERE resource_assignment_id = l_res_asg_id_tab(i)
AND start_date BETWEEN l_actual_from_date AND l_actual_to_date;
UPDATE pa_resource_assignments
SET spread_curve_id = NULL,
sp_fixed_date = NULL,
transaction_source_code = 'AVERAGE_ACTUALS' -- bug 4232619
WHERE resource_assignment_id = l_res_asg_id_tab(i);
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
COUNT(DISTINCT TXN_CURRENCY_CODE)
INTO l_currency_count_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT TXN_CURRENCY_CODE)
INTO l_currency_count_act
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_currency_code_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_currency_code_act
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_etc_currency_code
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_etc_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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
SUM(NVL(TOTAL_PLAN_QUANTITY, 0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', TOTAL_TXN_RAW_COST,
'PC', TOTAL_PC_RAW_COST), 0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', TOTAL_TXN_BURDENED_COST,
'PC', TOTAL_PC_BURDENED_COST),0))
INTO l_tot_qty,
l_tot_txn_raw_cost,
l_tot_txn_brdn_cost
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT SUM(NVL(quantity,0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', txn_raw_cost,
'PC', prj_raw_cost),0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', txn_brdn_cost,
'PC', prj_brdn_cost),0))
INTO
l_act_qty,
l_act_txn_raw_cost,
l_act_txn_brdn_cost
FROM /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
COUNT(DISTINCT TXN_CURRENCY_CODE)
INTO l_currency_count_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_currency_code_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
SUM(NVL(TOTAL_PLAN_QUANTITY, 0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', TOTAL_TXN_RAW_COST,
'PC', TOTAL_PC_RAW_COST), 0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', TOTAL_TXN_BURDENED_COST,
'PC', TOTAL_PC_BURDENED_COST),0))
INTO l_tot_qty,
l_tot_txn_raw_cost,
l_tot_txn_brdn_cost
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
INSERT INTO PA_FP_CALC_AMT_TMP2
(RESOURCE_ASSIGNMENT_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST)
VALUES
(P_RESOURCE_ASSIGNMENT_ID,
l_etc_currency_code,
l_etc_qty,
l_etc_txn_raw_cost,
l_etc_txn_brdn_cost);
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
COUNT(DISTINCT TXN_CURRENCY_CODE)
INTO l_currency_count_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
/* SELECT COUNT(DISTINCT TXN_CURRENCY_CODE)
INTO l_currency_count_bsl
FROM PA_FP_CALC_AMT_TMP3
WHERE plan_version_id = P_ETC_SRC_BUDGET_VER_ID
AND task_id = P_TASK_ID
AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
AND res_asg_id = P_RESOURCE_ASSIGNMENT_ID;*/
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_currency_code_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
SUM(NVL(TOTAL_PLAN_QUANTITY,0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', TOTAL_TXN_RAW_COST,
'PC', TOTAL_PC_RAW_COST),0)),
SUM(NVL(DECODE(l_currency_flag, 'TC', TOTAL_TXN_BURDENED_COST,
'PC', TOTAL_PC_BURDENED_COST),0))
INTO l_tot_qty,
l_tot_txn_raw_cost,
l_tot_txn_brdn_cost
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
INSERT INTO PA_FP_CALC_AMT_TMP2
(RESOURCE_ASSIGNMENT_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST)
VALUES
(P_RESOURCE_ASSIGNMENT_ID,
l_etc_currency_code,
l_etc_qty,
l_etc_txn_raw_cost,
l_etc_txn_brdn_cost);
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT TXN_CURRENCY_CODE),SUM(NVL(quantity,0))
INTO l_txn_currency_count_act,l_act_qty
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_txn_currency_code_act
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
SUM(NVL(quantity,0)),
SUM(NVL(txn_raw_cost,0)),
SUM(NVL(txn_brdn_cost,0)),
SUM(NVL(prj_raw_cost,0)),
SUM(NVL(prj_brdn_cost,0))
INTO
l_act_qty,
l_act_txn_raw_cost,
l_act_txn_brdn_cost,
l_act_pc_raw_cost,
l_act_pc_brdn_cost
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
SUM(NVL(quantity,0)),
SUM(NVL(prj_raw_cost,0)),
SUM(NVL(prj_brdn_cost,0))
INTO
l_act_qty,
l_act_pc_raw_cost,
l_act_pc_brdn_cost
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 = P_ETC_SOURCE_CODE;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
COUNT(DISTINCT TXN_CURRENCY_CODE)
INTO l_txn_currency_count_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
DISTINCT TXN_CURRENCY_CODE
INTO l_txn_currency_code_tot
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
SUM(NVL(TOTAL_PLAN_QUANTITY, 0)),
SUM(NVL(TOTAL_TXN_RAW_COST,0)),
SUM(NVL(TOTAL_TXN_BURDENED_COST,0)),
SUM(NVL(TOTAL_PC_RAW_COST, 0)),
SUM(NVL(TOTAL_PC_BURDENED_COST,0))
INTO l_tot_qty,
l_tot_txn_raw_cost,
l_tot_txn_brdn_cost,
l_tot_pc_raw_cost,
l_tot_pc_brdn_cost
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
SUM(NVL(TOTAL_PLAN_QUANTITY, 0)),
SUM(NVL(TOTAL_PC_RAW_COST,0)),
SUM(NVL(TOTAL_PC_BURDENED_COST,0))
INTO l_tot_qty,
l_tot_pc_raw_cost,
l_tot_pc_brdn_cost
FROM PA_FP_CALC_AMT_TMP2
WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
AND ETC_CURRENCY_CODE IS NULL;
INSERT INTO PA_FP_CALC_AMT_TMP2
(RESOURCE_ASSIGNMENT_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST)
VALUES
(P_RESOURCE_ASSIGNMENT_ID,
l_etc_currency_code,
l_etc_qty,
l_etc_raw_cost,
l_etc_brdn_cost);
SELECT PROJ_ELEMENT_ID
BULK COLLECT INTO l_wp_task_tab
FROM PA_MAP_WP_TO_FIN_TASKS_V
WHERE PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VERSION_ID
AND MAPPED_FIN_TASK_ID = P_TASK_ID;
SELECT proj_element_id
BULK COLLECT INTO l_wp_task_tab
FROM pa_proj_element_versions
WHERE PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VERSION_ID
AND OBJECT_TYPE = 'PA_TASKS';
INSERT INTO PA_FP_CALC_AMT_TMP2 (
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TOTAL_PLAN_QUANTITY,
TOTAL_TXN_RAW_COST,
TOTAL_TXN_BURDENED_COST)
VALUES ( (-1) * P_TASK_ID,
P_PROJ_CURRENCY_CODE,
l_tot_work_qty,
l_tot_raw_cost_pc,
l_tot_brdn_cost_pc);*/
INSERT INTO PA_FP_CALC_AMT_TMP2 (
RESOURCE_ASSIGNMENT_ID,
ETC_CURRENCY_CODE,
ETC_PLAN_QUANTITY,
ETC_TXN_RAW_COST,
ETC_TXN_BURDENED_COST,
TRANSACTION_SOURCE_CODE,
ACTUAL_WORK_QTY)
VALUES ((-1) * P_TASK_ID,
P_PROJ_CURRENCY_CODE,
l_etc_raw_cost_pc,
l_etc_raw_cost_pc,
l_etc_brdn_cost_pc,
l_transaction_source_code,
l_act_work_qty_ind);
SELECT nvl(start_date,l_sysdate),
nvl(completion_date,l_sysdate)
INTO l_start_date, l_completion_date
FROM pa_tasks
WHERE task_id = P_TASK_ID;
SELECT nvl(start_date,l_sysdate),
nvl(completion_date,l_sysdate)
INTO l_start_date, l_completion_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT resource_list_member_id INTO l_ppl_class_rlm_id
FROM pa_resource_list_members
WHERE resource_list_id = P_TARGET_RES_LIST_ID
AND resource_class_flag = 'Y'
AND resource_class_code = 'PEOPLE';
INSERT INTO PA_FP_CALC_AMT_TMP1 (
RESOURCE_ASSIGNMENT_ID,
BUDGET_VERSION_ID,
PROJECT_ID,
TASK_ID,
TARGET_RLM_ID,
PLANNING_START_DATE,
PLANNING_END_DATE,
TRANSACTION_SOURCE_CODE,
MAPPED_FIN_TASK_ID )
VALUES (
(-1) * P_TASK_ID,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
P_TASK_ID,
l_ppl_class_rlm_id,
l_start_date,
l_completion_date,
'WORK_QUANTITY',
P_TASK_ID );
SELECT nvl(start_date,l_sysdate),
nvl(completion_date,l_sysdate)
INTO l_task_start_date, l_task_completion_date
FROM pa_tasks
WHERE task_id = P_TASK_ID;
SELECT nvl(start_date,l_sysdate),
nvl(completion_date,l_sysdate)
INTO l_task_start_date, l_task_completion_date
FROM pa_projects_all WHERE
project_id = p_project_id;
resource_list_member_id col in the following insert for the value
l_target_class_rlm_id value. */
INSERT INTO PA_FP_CALC_AMT_TMP1 (
RESOURCE_ASSIGNMENT_ID,
BUDGET_VERSION_ID,
PROJECT_ID,
TASK_ID,
target_rlm_id,
PLANNING_START_DATE,
PLANNING_END_DATE,
MAPPED_FIN_TASK_ID )
VALUES (
(-1) * P_TASK_ID,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
P_TASK_ID,
l_target_class_rlm_id,
l_task_start_date,
l_task_completion_date,
P_TASK_ID );
DELETE FROM pa_budget_lines bl
WHERE nvl(bl.quantity,0) = 0
AND bl.budget_version_id = p_budget_version_id
AND bl.init_quantity is null
AND bl.txn_init_raw_cost is null
AND bl.txn_init_burdened_cost is null
AND bl.txn_init_revenue is null;
the same resource assignments should be updated to the same start and end
dates, which honor the max and min of the individual budget lines. This
should also be updated back to resource assignments. **/
IF l_fp_cols_rec.x_time_phased_code = 'N' THEN
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_msg => 'Before calling pa_fp_maintain_actual_pub.'||
'SYNC_UP_PLANNING_DATES_NONE_TP',
p_module_name => l_module_name,
p_log_level => 5);
SELECT wp_version_flag
INTO l_wp_version_flag
FROM pa_budget_versions
WHERE budget_version_id=p_budget_version_id;
/* Calling insert_txn_currency api */
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_msg => 'Before calling
pa_fp_gen_budget_amt_pub.insert_txn_currency',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_BUDGET_AMT_PUB.INSERT_TXN_CURRENCY
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_FP_COLS_REC => l_fp_cols_rec,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
pa_fp_gen_budget_amt_pub.insert_txn_currency'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
(p_msg => 'Before calling PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE',
p_module_name => l_module_name,
p_log_level => 5);
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_fp_version_ids_tab,
x_return_status => x_return_status,
x_msg_code => x_msg_data );
(p_msg => 'After calling PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE,
ret status: '||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
(p_msg => 'Before calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_ETC_START_DATE => P_ETC_START_DATE,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
(p_msg => 'After calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE,
ret status: '||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
( resource_assignment_id )
SELECT ra.resource_assignment_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.transaction_source_code IS NOT NULL;
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
WHERE ra.budget_version_id = p_budget_version_id
AND ra.project_id = p_project_id
AND ra.transaction_source_code IS NOT NULL
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 (+);
DELETE FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.transaction_source_code IS NOT NULL
AND NOT EXISTS (SELECT null
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = ra.resource_assignment_id)
AND NOT EXISTS (SELECT null
FROM pa_resource_asgn_curr rbc
WHERE rbc.resource_assignment_id = ra.resource_assignment_id);
SELECT DECODE(BV.VERSION_TYPE,
'COST', OPT.GEN_SRC_COST_WP_VERSION_ID,
'REVENUE',OPT.GEN_SRC_REV_WP_VERSION_ID,
'ALL',OPT.GEN_SRC_ALL_WP_VERSION_ID),
DECODE(BV.VERSION_TYPE,
'COST', OPT1.GEN_SRC_COST_WP_VER_CODE,
'REVENUE',OPT1.GEN_SRC_REV_WP_VER_CODE,
'ALL',OPT1.GEN_SRC_ALL_WP_VER_CODE),
BV.PROJECT_ID
INTO l_etc_wp_bdgt_ver_id,
l_etc_wp_ver_code,
l_project_id
FROM PA_PROJ_FP_OPTIONS OPT,PA_PROJ_FP_OPTIONS OPT1,
PA_BUDGET_VERSIONS BV
WHERE OPT.FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND OPT.FIN_PLAN_VERSION_ID = BV.BUDGET_VERSION_ID
--AND OPT.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
AND OPT1.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
AND OPT1.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE'
AND OPT1.PROJECT_ID = BV.PROJECT_ID;
/* Plan_ver_code is selected at PLAN_TYPE instead of PLAN_VERSION */
IF l_etc_wp_bdgt_ver_id is not null AND P_CONTEXT = 'VER_ID' THEN
SELECT PROJECT_STRUCTURE_VERSION_ID into l_etc_wp_ver_id
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = l_etc_wp_bdgt_ver_id;
SELECT el.name INTO l_etc_wp_ver_name
FROM pa_budget_versions bv, pa_proj_elem_ver_structure el
WHERE bv.budget_version_id = l_etc_wp_bdgt_ver_id
AND bv.project_structure_version_id = el.element_version_id
AND bv.project_id = el.project_id;
SELECT name INTO l_etc_wp_ver_name
FROM pa_proj_elem_ver_structure
WHERE element_version_id = l_etc_wp_ver_id
AND project_id = l_project_id;
SELECT DECODE(BV.VERSION_TYPE,
'COST', OPT.GEN_SRC_COST_PLAN_TYPE_ID,
'REVENUE',OPT.GEN_SRC_REV_PLAN_TYPE_ID,
'ALL',OPT.GEN_SRC_ALL_PLAN_TYPE_ID),
PT.NAME
INTO l_src_plan_type_id,
l_src_plan_type_name
FROM PA_PROJ_FP_OPTIONS OPT,
PA_BUDGET_VERSIONS BV,
pa_fin_plan_types_vl PT
WHERE
OPT.FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND P_BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID
AND DECODE(BV.VERSION_TYPE,
'COST', OPT.GEN_SRC_COST_PLAN_TYPE_ID,
'REVENUE',OPT.GEN_SRC_REV_PLAN_TYPE_ID,
'ALL',OPT.GEN_SRC_ALL_PLAN_TYPE_ID)
= PT.FIN_PLAN_TYPE_ID;
SELECT DECODE(BV.VERSION_TYPE,
'COST', OPT.GEN_SRC_COST_PLAN_VERSION_ID,
'REVENUE',OPT.GEN_SRC_REV_PLAN_VERSION_ID,
'ALL',OPT.GEN_SRC_ALL_PLAN_VERSION_ID),
DECODE(BV.VERSION_TYPE,
'COST', OPT1.GEN_SRC_COST_PLAN_VER_CODE,
'REVENUE',OPT1.GEN_SRC_REV_PLAN_VER_CODE,
'ALL',OPT1.GEN_SRC_ALL_PLAN_VER_CODE),
DECODE(BV.VERSION_TYPE,
'COST', OPT.GEN_SRC_COST_PLAN_TYPE_ID,
'REVENUE',OPT.GEN_SRC_REV_PLAN_TYPE_ID,
'ALL',OPT.GEN_SRC_ALL_PLAN_TYPE_ID),
BV.PROJECT_ID
INTO l_etc_fp_ver_id,
l_etc_fp_ver_code,
l_src_plan_type_id,
l_project_id
FROM PA_PROJ_FP_OPTIONS OPT, PA_PROJ_FP_OPTIONS OPT1,
PA_BUDGET_VERSIONS BV
WHERE BV.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND OPT.FIN_PLAN_VERSION_ID = BV.BUDGET_VERSION_ID
AND OPT1.PROJECT_ID = BV.PROJECT_ID
AND OPT1.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
AND OPT1.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
/* Plan_ver_code is selected at PLAN_TYPE instead of PLAN_VERSION */
IF l_etc_fp_ver_id is not null AND P_CONTEXT = 'VER_ID' THEN
RETURN l_etc_fp_ver_id;
SELECT version_name INTO l_etc_fp_ver_name
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = l_etc_fp_ver_id;
SELECT version_name INTO l_etc_fp_ver_name
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = l_etc_fp_ver_id;
SELECT txn_currency_code,
SUM(NVL(init_quantity,0)),
SUM(NVL(txn_init_raw_cost,0)),
SUM(NVL(txn_init_burdened_cost,0)),
SUM(NVL(project_init_raw_cost,0)),
SUM(NVL(project_init_burdened_cost,0)),
SUM(NVL(init_raw_cost,0)),
SUM(NVL(init_burdened_cost,0))
BULK COLLECT INTO
l_txn_currency_code_tab,
l_init_quantity_tab,
l_txn_init_raw_cost_tab,
l_txn_init_brdn_cost_tab,
l_prj_init_raw_cost_tab,
l_prj_init_brdn_cost_tab,
l_init_raw_cost_tab,
l_init_brdn_cost_tab
FROM pa_budget_lines
WHERE budget_version_id = l_wp_bdgt_ver_id
AND resource_assignment_id = p_src_res_asg_id
AND init_quantity is not null
GROUP BY txn_currency_code;
SELECT txn_currency_code,
SUM(NVL(init_quantity,0)),
SUM(NVL(txn_init_raw_cost,0)),
SUM(NVL(txn_init_burdened_cost,0)),
SUM(NVL(project_init_raw_cost,0)),
SUM(NVL(project_init_burdened_cost,0)),
SUM(NVL(init_raw_cost,0)),
SUM(NVL(init_burdened_cost,0))
BULK COLLECT INTO
l_txn_currency_code_tab,
l_init_quantity_tab,
l_txn_init_raw_cost_tab,
l_txn_init_brdn_cost_tab,
l_prj_init_raw_cost_tab,
l_prj_init_brdn_cost_tab,
l_init_raw_cost_tab,
l_init_brdn_cost_tab
FROM pa_budget_lines
WHERE budget_version_id = l_wp_bdgt_ver_id
AND resource_assignment_id = p_src_res_asg_id
AND end_date <= p_actuals_thru_date
AND init_quantity is not null
GROUP BY txn_currency_code;
PROCEDURE call_clnt_extn_and_update_bl(
p_project_id IN pa_projects_all.project_id%TYPE
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,x_call_maintain_data_api OUT NOCOPY VARCHAR2
,X_RETURN_STATUS OUT NOCOPY VARCHAR2
,X_MSG_COUNT OUT NOCOPY NUMBER
,X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_msg_count NUMBER;
l_module_name VARCHAR2(200) := 'call_clnt_extn_and_update_bl';
tot_quantity pa_budget_lines.quantity%TYPE, -- this attribute is used to update the total quantity field in pa_budget_lines
txn_raw_cost pa_budget_lines.txn_raw_cost%TYPE,
txn_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE,
txn_revenue pa_budget_lines.txn_revenue%TYPE,
RAW_COST_RATE pa_budget_lines.txn_standard_cost_rate%TYPE,
BURDENED_COST_RATE pa_budget_lines.burden_cost_rate%TYPE,
REVENUE_BILL_RATE pa_budget_lines.txn_standard_bill_rate%TYPE,
disp_quantity pa_budget_lines.display_quantity%TYPE);
TYPE update_rbf_rec IS RECORD( ra_id pa_resource_assignments.resource_assignment_id%TYPE,
rate_based_flag pa_resource_assignments.rate_based_flag%TYPE);
TYPE update_rbf_tbl IS TABLE OF update_rbf_rec;
l_upd_rbf_tbl update_rbf_tbl;
pa_debug.set_curr_function( p_function => 'call_clnt_extn_and_update_bl',
p_debug_mode => p_pa_debug_mode);
SELECT prac.resource_assignment_id
,prac.txn_currency_code
,prac.total_quantity - NVL(prac.total_init_quantity,0)
,prac.total_txn_raw_cost - NVL(prac.total_txn_init_raw_cost,0)
,prac.total_txn_burdened_cost -NVL(prac.total_txn_init_burdened_cost,0)
,prac.total_txn_revenue - NVL(prac.total_txn_init_revenue,0)
,DECODE (prac.total_display_quantity, NULL, NULL, (prac.total_display_quantity - NVL(prac.total_init_quantity,0)))
,prac.total_init_quantity
,prac.total_txn_init_raw_cost
,prac.total_txn_init_burdened_cost
,prac.total_txn_init_revenue
,pra.task_id
,pra.RESOURCE_LIST_MEMBER_ID
,pra.unit_of_measure
,pra.rate_based_flag
,pra.resource_rate_based_flag
,pra.etc_method_code
,prac.txn_raw_cost_rate_override
,prac.txn_burden_cost_rate_override
,prac.txn_bill_rate_override
,prac.txn_average_raw_cost_rate
,prac.txn_average_burden_cost_rate
,prac.txn_average_bill_rate
,pra.transaction_source_code
,pra.planning_end_date
,decode(pra.transaction_source_code,NULL,NULL,
(SELECT meaning
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL'
AND LOOKUP_CODE= pra.transaction_source_code)) etc_source
BULK COLLECT INTO
l_ra_id_tbl_1
,l_txn_currency_code_tbl_1
,l_etc_qty_tbl_1 /* ETC QTY */
,l_txn_raw_cost_tbl_1
,l_txn_burdened_cost_tbl_1
,l_txn_revenue_tbl_1
,l_disp_quant_tbl_1
,l_init_quantity_tbl_1
,l_init_raw_cost_tbl_1
,l_init_burd_cost_tbl_1
,l_init_revenue_tbl_1
,l_task_id_tab
,l_rlm_id_tbl
,l_unit_of_measure_tbl
,l_rate_based_flag_tbl
,l_res_rate_based_flag_tbl
,l_etc_method_code_tbl
,l_txn_raw_cost_rate_ovrrid_tbl
,l_txn_burd_cst_rate_ovrrid_tbl
,l_txn_bill_rate_ovrrid_tbl
,l_txn_avg_raw_cost_rate_tbl
,l_txn_avg_burd_cost_rate_tbl
,l_txn_avg_bill_rate_tbl
,l_txn_src_code_tbl
,l_planning_end_date_tbl_1
,l_etc_source_tbl
FROM pa_resource_asgn_curr prac,
pa_resource_assignments pra
WHERE prac.budget_version_id = p_budget_version_id
AND pra.budget_version_id = p_budget_version_id
AND prac.resource_assignment_id = pra.resource_assignment_id;
l_upd_rbf_tbl := update_rbf_tbl();
l_upd_rbf_tbl.DELETE;
FOR C1 IN (select lookup_code, meaning from pa_lookups where lookup_type = 'PA_FP_FCST_GEN_CLNT_EXTN_LU') loop
l_description_tbl(c1.lookup_code) := c1.meaning;
DELETE from PA_FP_GEN_RATE_TMP;
SELECT
pbl.period_name
,NVL(pbl.txn_cost_rate_override,pbl.txn_standard_cost_rate)
,NVL(pbl.burden_cost_rate_override,pbl.burden_cost_rate)
,NVL(pbl.txn_bill_rate_override,pbl.txn_standard_bill_rate)
,pbl.quantity
,pbl.start_date
,pbl.end_date
,pbl.txn_raw_cost
,pbl.txn_burdened_cost
,pbl.txn_revenue
,pbl.display_quantity
,pbl.init_quantity
,pbl.txn_init_raw_cost
,pbl.txn_init_burdened_cost
,pbl.txn_init_revenue
BULK COLLECT INTO
l_ext_period_name_tab
,l_ext_raw_cost_rate_tab
,l_ext_burdened_cost_rate_tab
,l_ext_revenue_bill_rate_tab
,l_total_qty_tbl
,l_line_start_date_tbl
,l_line_end_date_tbl
,l_txn_raw_cost_tbl
,l_txn_burdened_cost_tbl
,l_txn_revenue_tbl
,l_disp_quant_tbl
,l_init_quantity_tbl
,l_init_raw_cost_tbl
,l_init_burd_cost_tbl
,l_init_revenue_tbl
FROM pa_budget_lines pbl
WHERE resource_assignment_id = l_ra_id_tbl_1(kk)
AND txn_currency_code = l_txn_currency_code_tbl_1(kk);
l_input_period_rates_tbl.delete;
l_plan_txn_prd_amt_tbl_1.delete;
SELECT tmp1.etc_method_code
INTO l_etc_method_code
FROM PA_FP_CALC_AMT_TMP1 tmp1,
PA_FP_CALC_AMT_TMP2 tmp2
WHERE tmp1.TARGET_RES_ASG_ID = l_ra_id_tbl_1(kk)
AND tmp2.TARGET_RES_ASG_ID = l_ra_id_tbl_1(kk)
AND tmp1.resource_assignment_id = tmp2.resource_assignment_id
AND tmp1.target_res_asg_id = tmp2.target_res_asg_id
AND tmp2.txn_currency_code = l_txn_currency_code_tbl_1(kk)
AND tmp1.transaction_source_code <> 'OPEN_COMMITMENTS'
AND tmp2.transaction_source_code = 'ETC';
/* In work quantity flows, resource_assignment_id is inserted as (-1) * task_id
* so we have to split this select into two to get the below values separately.
* -- Below code commented since this doesnt work. The tmp2
* table is empty for work qty flows! ... Logic needs to be put
* for this...
SELECT tmp2.etc_plan_quantity
,tmp2.actual_work_qty
INTO l_etc_plan_qty
,l_act_work_qty
FROM PA_FP_CALC_AMT_TMP1 tmp1,
PA_FP_CALC_AMT_TMP2 tmp2
WHERE tmp1.resource_assignment_id = ((-1) * l_task_id_tab(kk))
AND tmp2.resource_assignment_id = ((-1) * l_task_id_tab(kk))
AND tmp1.resource_assignment_id = tmp2.resource_assignment_id
AND tmp1.mapped_fin_task_id = l_task_id_tab(kk)
AND tmp1.transaction_source_code = 'WORK_QUANTITY';
l_upd_rbf_tbl.DELETE(u);
/* Quantity null or nulled out means the bl has to be deletedc */
l_del_bud_line_tbl.extend(1);
INSERT INTO PA_FP_GEN_RATE_TMP
( TXN_CURRENCY_CODE,
PERIOD_NAME,
RAW_COST_RATE,
BURDENED_COST_RATE,
REVENUE_BILL_RATE
)
VALUES
( l_txn_currency_code_tbl_1(kk),
l_period_rates_tbl(j).period_name,
l_period_rates_tbl(j).raw_cost_rate,
l_period_rates_tbl(j).burdened_cost_rate,
l_period_rates_tbl(j).revenue_bill_rate
);
UPDATE pa_budget_lines
SET txn_raw_cost = l_upd_bgt_line_tbl(i).txn_raw_cost
,txn_revenue = l_upd_bgt_line_tbl(i).txn_revenue
,txn_burdened_cost = l_upd_bgt_line_tbl(i).txn_burdened_cost
,display_quantity = l_upd_bgt_line_tbl(i).disp_quantity
,quantity = l_upd_bgt_line_tbl(i).tot_quantity -- updating total quantity
,txn_cost_rate_override = l_upd_bgt_line_tbl(i). raw_cost_rate -- updating rates
,burden_cost_rate_override = l_upd_bgt_line_tbl(i).burdened_cost_rate -- updating rates
,txn_bill_rate_override = l_upd_bgt_line_tbl(i).revenue_bill_rate -- updating rates
,cost_rejection_code = NULL
,revenue_rejection_code =NULL
,burden_rejection_code = NULL
,other_rejection_code = NULL
WHERE
resource_assignment_id = l_upd_bgt_line_tbl(i).ra_id
AND txn_currency_code = l_upd_bgt_line_tbl(i).txn_curr_code
AND period_name = NVL(l_upd_bgt_line_tbl(i).period_name , period_name);
DELETE FROM pa_budget_lines
WHERE
resource_assignment_id = l_del_bud_line_tbl(i).ra_id
AND txn_currency_code = l_del_bud_line_tbl(i).txn_curr_code
AND period_name = NVL(l_del_bud_line_tbl(i).period_name , period_name);
UPDATE pa_resource_assignments
SET rate_based_flag = 'Y'
WHERE resource_assignment_id = l_upd_rbf_tbl(upd).ra_id;
UPDATE pa_resource_assignments
SET rate_based_flag = 'Y'
WHERE resource_assignment_id = l_upd_rbf_tbl_1(upd);
DELETE FROM pa_fp_spread_calc_tmp;
INSERT INTO pa_fp_spread_calc_tmp
(budget_version_id,
resource_assignment_id)
VALUES
(p_budget_version_id,
l_ra_id_tbl_1(kk));
p_procedure_name => 'call_clnt_extn_and_update_bl',
p_error_text => substr(sqlerrm,1,240));
END call_clnt_extn_and_update_bl;