[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* FULL -- DEFAULT, indicates full update of existing period *
* INCREMENTAL -- indicates increment the passed value of existing period *
**/
PROCEDURE MAINTAIN_ACTUAL_AMT_WRP
(P_PROJECT_ID_TAB IN SYSTEM.PA_NUM_TBL_TYPE,
P_WP_STR_VERSION_ID_TAB IN SYSTEM.PA_NUM_TBL_TYPE,
P_ACTUALS_THRU_DATE IN SYSTEM.PA_DATE_TBL_TYPE,
P_CALLING_CONTEXT IN VARCHAR2,
P_COMMIT_FLAG IN VARCHAR2,
P_INIT_MSG_FLAG IN VARCHAR2,
P_CALLING_MODE IN VARCHAR2,
P_EXTRACTION_TYPE 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_maintain_actual_pub.maintain_actual_amt_wrp';
SELECT distinct vw.project_id,
bv.budget_version_id,
vw.STRUCTURE_VERSION_ID,
vw.RESOURCE_ASSIGNMENT_ID,
vw.TXN_CURRENCY_CODE
FROM PA_PROG_ACT_BY_PERIOD_TEMP vw,
PA_BUDGET_VERSIONS bv,
PA_RESOURCE_ASSIGNMENTS ra
WHERE bv.project_structure_version_id = vw.structure_version_id
AND nvl(bv.wp_version_flag,'N') = 'Y' AND
vw.RESOURCE_ASSIGNMENT_ID IS NOT NULL AND
ra.resource_assignment_id = vw.resource_assignment_id AND
ra.budget_version_id = bv.budget_version_id AND
vw.project_id = c_project_id AND
vw.structure_version_id = c_STRUCTURE_VERSION_ID
UNION
SELECT distinct vw.project_id,
bv.budget_version_id,
vw.STRUCTURE_VERSION_ID,
vw.HIDDEN_RES_ASSGN_ID,
vw.TXN_CURRENCY_CODE
FROM PA_PROG_ACT_BY_PERIOD_TEMP vw,
PA_BUDGET_VERSIONS bv,
PA_RESOURCE_ASSIGNMENTS ra
WHERE bv.project_structure_version_id = vw.structure_version_id
AND nvl(bv.wp_version_flag,'N') = 'Y' AND
vw.HIDDEN_RES_ASSGN_ID IS NOT NULL AND
ra.resource_assignment_id = vw.HIDDEN_RES_ASSGN_ID AND
ra.budget_version_id = bv.budget_version_id AND
vw.project_id = c_project_id AND
vw.structure_version_id = c_STRUCTURE_VERSION_ID;
/* Added start date and finish date in the SELECT stmt for bug 4408930 */
CURSOR budget_line_cursor(p_struct_ver_id NUMBER,
p_res_asg_id NUMBER,
p_txn_currency_code VARCHAR2) IS
SELECT period_name,
actual_effort,
actual_cost,
actual_cost_pc,
actual_cost_fc,
actual_rawcost,
actual_rawcost_pc,
actual_rawcost_fc,
start_date,
finish_date
FROM PA_PROG_ACT_BY_PERIOD_TEMP
WHERE structure_version_id = p_struct_ver_id
AND nvl(resource_assignment_id,HIDDEN_RES_ASSGN_ID) = p_res_asg_id
AND txn_currency_code = p_txn_currency_code;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT budget_version_id into l_bv_id
FROM PA_BUDGET_VERSIONS
WHERE project_id = P_PROJECT_ID_tab(ss1)
AND project_structure_version_id = P_WP_STR_VERSION_ID_TAB(ss1)
AND nvl(wp_version_flag,'N') = 'Y';
UPDATE pa_budget_lines
SET TXN_INIT_RAW_COST = decode(TXN_INIT_RAW_COST,null,null,0),
TXN_INIT_BURDENED_COST = decode(TXN_INIT_BURDENED_COST,null,null,0),
---TXN_INIT_REVENUE = decode(TXN_INIT_REVENUE,null,null,0),
PROJECT_INIT_RAW_COST = decode(PROJECT_INIT_RAW_COST,null,null,0),
PROJECT_INIT_BURDENED_COST = decode(PROJECT_INIT_BURDENED_COST,null,null,0),
---PROJECT_INIT_REVENUE = decode(PROJECT_INIT_REVENUE,null,null,0),
INIT_RAW_COST = decode(INIT_RAW_COST,null,null,0),
INIT_BURDENED_COST = decode(INIT_BURDENED_COST,null,null,0),
---INIT_REVENUE = decode(INIT_REVENUE,null,null,0),
INIT_QUANTITY = decode(INIT_QUANTITY,null,null,0),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login
WHERE budget_version_id = l_bv_id;
l_ra_id_upd_reprt_tab.DELETE;
SELECT DISTINCT NVL(resource_assignment_id, hidden_res_assgn_id)
BULK COLLECT
INTO l_ra_id_upd_reprt_tab
FROM PA_PROG_ACT_BY_PERIOD_TEMP
WHERE project_id = P_PROJECT_ID_TAB(ss1)
AND structure_version_id = P_WP_STR_VERSION_ID_TAB(ss1)
AND NVL(resource_assignment_id, hidden_res_assgn_id) IS NOT NULL;
P_ACTIVITY_CODE => 'DELETE',
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
SELECT pd.end_date INTO l_open_pd_end_date
FROM pa_periods_all pd
WHERE pd.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: NVL(pd.org_id,-99)
AND p_actuals_thru_date(ss1) BETWEEN pd.start_date AND pd.end_date;
SELECT gl.end_date INTO l_open_pd_end_date
FROM gl_period_statuses gl,
pa_implementations_all imp
WHERE gl.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND gl.set_of_books_id = imp.set_of_books_id
AND gl.adjustment_period_flag = 'N'
AND imp.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: NVL(imp.org_id,-99)
AND p_actuals_thru_date(ss1) BETWEEN gl.start_date AND gl.end_date;
l_period_name_tab.delete;
l_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_proj_raw_cost_tab.delete;
l_pou_raw_cost_tab.delete;
l_txn_bd_cost_tab.delete;
l_proj_bd_cost_tab.delete;
l_pou_bd_cost_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_amt_dtls_tbl.DELETE;
SELECT pd.start_date ,pd.end_date into l_start_date, l_end_date
FROM pa_periods_all pd
WHERE pd.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: nvl(pd.org_id,-99)
AND pd.period_name = l_period_name_tab(m);
SELECT gl.start_date, gl.end_date INTO l_start_date,l_end_date
FROM gl_period_statuses gl,
pa_implementations_all imp
WHERE gl.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND gl.SET_OF_BOOKS_ID = imp.SET_OF_BOOKS_ID
AND gl.ADJUSTMENT_PERIOD_FLAG = 'N'
AND imp.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: nvl(imp.org_id,-99)
AND gl.period_name = l_period_name_tab(n);
SELECT NVL(planning_start_date,trunc(sysdate)),
NVL(planning_end_date,trunc(sysdate) ) INTO
l_start_date,l_end_date
FROM pa_resource_assignments
WHERE resource_assignment_id = l_res_asg_id_tab(i);
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp (
resource_assignment_id,
txn_currency_code )
VALUES (
l_res_asg_id_tab(i),
l_txn_currency_code_tab(i) );
UPDATE pa_resource_asgn_curr_tmp tmp
SET ( TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE ) =
( SELECT rbc.TXN_RAW_COST_RATE_OVERRIDE,
rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
rbc.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_asgn_curr rbc
WHERE rbc.resource_assignment_id = tmp.resource_assignment_id
AND rbc.txn_currency_code = tmp.txn_currency_code );
SELECT DISTINCT
resource_assignment_id
BULK COLLECT
INTO l_display_qty_ra_id_tab
FROM pa_resource_asgn_curr_tmp;
P_ACTIVITY_CODE => 'UPDATE',
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
UPDATE pa_budget_versions
SET etc_start_date = p_actuals_thru_date(kk)+1
WHERE budget_version_id = l_bv_id_tab(kk);
UPDATE pa_resource_assignments
SET unplanned_flag = 'N'
WHERE resource_assignment_id = l_res_asg_id_tab(m)
AND nvl(unplanned_flag,'N') = 'Y';
delete from PA_PROG_ACT_BY_PERIOD_TEMP where project_id = P_PROJECT_ID_TAB(ss1) and structure_version_id = P_WP_STR_VERSION_ID_TAB(ss1);
pa_fp_pji_intg_pkg.update_reporting_lines_frombl',
p_module_name => l_module_name,
p_log_level => 5);
calling update_reporting_lines_frombl: '
||p_budget_line_id_tab(jj),
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_PJI_INTG_PKG.UPDATE_REPORTING_LINES_FROMBL
(p_calling_module => p_calling_module
,p_activity_code => p_activity_code
,p_budget_version_id => p_budget_version_id
,p_resource_assignment_id => p_resource_assignment_id
,p_budget_line_id => p_budget_line_id_tab(jj)
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_return_status => x_return_status);
pa_fp_pji_intg_pkg.update_reporting_lines_frombl'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
*P_EXTRACTION_TYPE: FULL -- DEFAULT, indicates full update of existing period
* INCREMENTAL -- indicates increment the passed value of existing period
*P_OPEN_PD_PLAN_AMT_FLAG: Y -- Leave existing plan qty/amounts as-is for period with end date of
* P_OPEN_PD_END_DATE when Context is WP_APPLY_PROGRESS_TO_WORKING.
* N (default value) -- Set plan = actual.
* NOTE: If Target time phasing is None, the API will override this
* parameter with N.
*
*We currently expect the following Scenarios from the Workplan side:
*1. P_CALLING_CONTEXT = WP_APPLY_PROGRESS_TO_WORKING:
* -- P_EXTRACTION_TYPE always equals FULL
* -- No restriction on structure sharing type.
* -- IMPORTANT NOTE: If we start supporting extraction type of INCREMENT in this
* -- case, we will need to extend fixes made for Bug 4142150.
*2. P_CALLING_CONTEXT = WP_PROGRESS:
* -- P_EXTRACTION_TYPE always equals INCREMENTAL
* -- The structure cannot be fully shared in this case.
*3. P_CALLING_CONTEXT = WP_SUMMARIZED_ACTUAL:
* -- P_EXTRACTION_TYPE = FULL the 1st time this API is called (or after a refresh)
* -- P_EXTRACTION_TYPE = INCREMENTAL for subsequent calls
* -- The structure must be fully shared in this case.
*
**/
PROCEDURE MAINTAIN_ACTUAL_AMT_RA
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_RESOURCE_ASSIGNMENT_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
P_TXN_CURRENCY_CODE IN PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE,
P_AMT_DTLS_REC_TAB IN PA_FP_MAINTAIN_ACTUAL_PUB.l_amt_dtls_tbl_typ,
P_CALLING_CONTEXT IN VARCHAR2,
P_TXN_AMT_TYPE_CODE IN VARCHAR2,
P_CALLING_MODE IN VARCHAR2,
P_EXTRACTION_TYPE IN VARCHAR2,
P_OPEN_PD_PLAN_AMT_FLAG IN VARCHAR2,
P_OPEN_PD_END_DATE IN DATE,
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_maintain_actual_pub.maintain_actual_amt_ra';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT NVL(UNPLANNED_FLAG,'N')
INTO l_unplanned_res_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT project_currency_code,
projfunc_currency_code INTO
l_pc_code, l_pfc_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT version_type INTO l_version_type
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = P_BUDGET_VERSION_ID;
SELECT decode(l_version_type,
'COST', opt.cost_time_phased_code,
'REVENUE',opt.revenue_time_phased_code,
'ALL',opt.all_time_phased_code),
decode(l_version_type,
'COST', opt.gen_cost_ret_manual_line_flag,
'REVENUE', opt.gen_rev_ret_manual_line_flag,
'ALL', opt.gen_all_ret_manual_line_flag)
INTO l_time_phased_code,
l_ret_manual_line_flag
FROM pa_proj_fp_options opt
WHERE opt.fin_plan_version_id = p_budget_version_id;
SELECT 'N'
INTO l_ins_flag
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code
AND rownum < 2;
/* Bulk Insert in PA_BUDGET_LINES table */
-- Bug 4071198: When p_txn_amt_type_code = ACTUAL_TXN (i.e. the
-- context is FP_GEN_FCST_COPY_ACTUAL ), we populate the appropriate
-- override rates in the budget lines. Code changes are tagged with bug# 4071198
IF l_ins_flag = 'Y' THEN
IF (p_txn_amt_type_code = 'ACTUAL_TXN' AND
(p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
OR p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL')) THEN
/* no matter unplanned res flag is Y or N,
the actual values (init cols ) should be copied to
the plan columns. */
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_called_mode => p_calling_mode,
p_msg => 'Before inserting into pa_bdgt_lines when l_ins_flag is Y,
p_txn_amt_type_code is ACTUAL_TXN and l_unplanned_res_flag is Y',
p_module_name => l_module_name,
p_log_level => 5);
l_bl_id_tab.delete;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
PROJECT_INIT_RAW_COST,
PROJECT_INIT_BURDENED_COST,
PROJECT_INIT_REVENUE,
INIT_RAW_COST,
INIT_BURDENED_COST,
INIT_REVENUE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
QUANTITY,
INIT_QUANTITY,
project_currency_code,
projfunc_currency_code,
TXN_COST_RATE_OVERRIDE, /* Bug 4071198 start */
BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE ) /* Bug 4071198 start */
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_period_name_tab(j),
l_start_date_tab(j),
l_end_date_tab(j),
p_txn_currency_code,
l_txn_raw_cost_tab(j),
l_txn_burdened_cost_tab(j),
l_txn_revenue_tab(j),
l_project_raw_cost_tab(j),
l_project_burdened_cost_tab(j),
l_project_revenue_tab(j),
l_pfc_raw_cost_tab(j),
l_pfc_burdened_cost_tab(j),
l_pfc_revenue_tab(j),
l_txn_raw_cost_tab(j),
l_txn_burdened_cost_tab(j),
l_txn_revenue_tab(j),
l_project_raw_cost_tab(j),
l_project_burdened_cost_tab(j),
l_project_revenue_tab(j),
l_pfc_raw_cost_tab(j),
l_pfc_burdened_cost_tab(j),
l_pfc_revenue_tab(j),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_qty_tab(j),
l_qty_tab(j),
l_pc_code,
l_pfc_code ,
l_cost_rate_override_tab(j), /* bug 4071198 */
l_bcost_rate_override_tab(j),
l_bill_rate_override_tab(j)) /* bug 4071198 */
RETURNING budget_line_id BULK COLLECT INTO l_bl_id_tab;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
PROJECT_INIT_RAW_COST,
PROJECT_INIT_BURDENED_COST,
PROJECT_INIT_REVENUE,
INIT_RAW_COST,
INIT_BURDENED_COST,
INIT_REVENUE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
QUANTITY,
INIT_QUANTITY,
project_currency_code,
projfunc_currency_code,
TXN_STANDARD_COST_RATE, /* Bug 4398799, 4071198 start */
BURDEN_COST_RATE, /* Bug 4398799, 4071198 start */
TXN_STANDARD_BILL_RATE ) /* Bug 4398799, 4071198 start */
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_period_name_tab(j),
l_start_date_tab(j),
l_end_date_tab(j),
p_txn_currency_code,
l_txn_raw_cost_tab(j),
l_txn_burdened_cost_tab(j),
l_txn_revenue_tab(j),
l_project_raw_cost_tab(j),
l_project_burdened_cost_tab(j),
l_project_revenue_tab(j),
l_pfc_raw_cost_tab(j),
l_pfc_burdened_cost_tab(j),
l_pfc_revenue_tab(j),
l_txn_raw_cost_tab(j),
l_txn_burdened_cost_tab(j),
l_txn_revenue_tab(j),
l_project_raw_cost_tab(j),
l_project_burdened_cost_tab(j),
l_project_revenue_tab(j),
l_pfc_raw_cost_tab(j),
l_pfc_burdened_cost_tab(j),
l_pfc_revenue_tab(j),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_qty_tab(j),
l_qty_tab(j),
l_pc_code,
l_pfc_code ,
l_cost_rate_override_tab(j), /* bug 4071198 */
l_bcost_rate_override_tab(j),
l_bill_rate_override_tab(j)) /* bug 4071198 */
RETURNING budget_line_id BULK COLLECT INTO l_bl_id_tab;
p_msg => 'After inserting into pa_bdgt_lines when l_ins_flag is Y,
p_txn_amt_type_code is ACTUAL_TXN and l_unplanned_res_flag is Y',
p_module_name => l_module_name,
p_log_level => 5);
p_msg => 'Before inserting into pa_bdgt_lines when l_ins_flag is Y,
p_txn_amt_type_code is ACTUAL_TXN and l_unplanned_res_flag is N',
p_module_name => l_module_name,
p_log_level => 5);
l_bl_id_tab.delete;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
PROJECT_INIT_RAW_COST,
PROJECT_INIT_BURDENED_COST,
PROJECT_INIT_REVENUE,
INIT_RAW_COST,
INIT_BURDENED_COST,
INIT_REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INIT_QUANTITY,
project_currency_code,
projfunc_currency_code)
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_period_name_tab(j),
l_start_date_tab(j),
l_end_date_tab(j),
p_txn_currency_code,
l_txn_raw_cost_tab(j),
l_txn_burdened_cost_tab(j),
l_txn_revenue_tab(j),
l_project_raw_cost_tab(j),
l_project_burdened_cost_tab(j),
l_project_revenue_tab(j),
l_pfc_raw_cost_tab(j),
l_pfc_burdened_cost_tab(j),
l_pfc_revenue_tab(j),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_qty_tab(j),
l_pc_code,
l_pfc_code );
p_msg => 'Before inserting into pa_bdgt_lines when l_ins_flag is Y,
p_txn_amt_type_code is PLANNING_TXN and
l_version_type is COST or REVENUE or ALL',
p_module_name => l_module_name,
p_log_level => 5);
l_bl_id_tab.delete;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
QUANTITY,
project_currency_code,
projfunc_currency_code,
PROJFUNC_COST_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE,
PROJECT_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE )
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_period_name_tab(j2),
l_start_date_tab(j2),
l_end_date_tab(j2),
p_txn_currency_code,
l_txn_raw_cost_tab(j2),
l_txn_burdened_cost_tab(j2),
l_txn_revenue_tab(j2),
l_project_raw_cost_tab(j2),
l_project_burdened_cost_tab(j2),
l_project_revenue_tab(j2),
l_pfc_raw_cost_tab(j2),
l_pfc_burdened_cost_tab(j2),
l_pfc_revenue_tab(j2),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_qty_tab(j2),
l_pc_code,
l_pfc_code,
l_projfunc_cost_rate_type_tab(j2),
l_projfunc_rev_rate_type_tab(j2),
l_project_cost_rate_type_tab(j2),
l_project_rev_rate_type_tab(j2),
l_cost_rate_override_tab(j2),
l_bcost_rate_override_tab(j2),
l_bill_rate_override_tab(j2))
RETURNING budget_line_id
BULK COLLECT INTO l_bl_id_tab;
/* dbms_output.put_line('No. of rows inserted in
bl table: '||sql%rowcount); */
SELECT budget_line_id,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
QUANTITY,
TXN_STANDARD_COST_RATE, /* Bug 4398799 */
BURDEN_COST_RATE, /* Bug 4398799 */
TXN_STANDARD_BILL_RATE /* Bug 4398799 */
INTO l_bdgt_line_id,
l_txn_raw_cost,
l_txn_burdened_cost,
l_txn_revenue,
l_project_raw_cost,
l_project_burdened_cost,
l_project_revenue,
l_raw_cost,
l_burdened_cost,
l_revenue,
l_quantity,
l_txn_cost_rate_override,
l_burden_cost_rate_override,
l_txn_bill_rate_override
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND start_date = l_start_date_tab(k)
AND txn_currency_code = p_txn_currency_code;
SELECT budget_line_id
INTO l_bdgt_line_id
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND start_date = l_start_date_tab(k)
AND txn_currency_code = p_txn_currency_code;
SELECT budget_line_id
INTO l_bdgt_line_id
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code;
/* dbms_output.put_line('Update count when the ins_flag is null:
'|| l_upd_period_name_tab.count);*/
/* Bulk Update in PA_BUDGET_LINES table */
IF l_upd_period_name_tab.count > 0 THEN
IF (p_txn_amt_type_code = 'ACTUAL_TXN' AND
(p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
OR p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL')) THEN
IF p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
AND P_EXTRACTION_TYPE = 'INCREMENTAL' THEN
FORALL m in 1..l_upd_period_name_tab.count
UPDATE pa_budget_lines
SET
TXN_INIT_RAW_COST = NVL(TXN_INIT_RAW_COST,0) + l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = NVL(TXN_INIT_BURDENED_COST,0) + l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = NVL(TXN_INIT_REVENUE,0) + l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = NVL(PROJECT_INIT_RAW_COST,0) + l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = NVL(PROJECT_INIT_BURDENED_COST,0) + l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = NVL(PROJECT_INIT_REVENUE,0) + l_upd_project_revenue_tab(m),
INIT_RAW_COST = NVL(INIT_RAW_COST,0) + l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = NVL(INIT_BURDENED_COST,0) + l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = NVL(INIT_REVENUE,0) + l_upd_pfc_revenue_tab(m),
TXN_RAW_COST = NVL(TXN_RAW_COST,0) + l_upd_txn_raw_cost_tab(m),
TXN_BURDENED_COST = NVL(TXN_BURDENED_COST,0) + l_upd_txn_burdened_cost_tab(m),
TXN_REVENUE = NVL(TXN_REVENUE,0) + l_upd_txn_revenue_tab(m),
PROJECT_RAW_COST = NVL(PROJECT_RAW_COST,0) + l_upd_project_raw_cost_tab(m),
PROJECT_BURDENED_COST = NVL(PROJECT_BURDENED_COST,0) + l_upd_proj_burdened_cost_tab(m),
PROJECT_REVENUE = NVL(PROJECT_REVENUE,0) + l_upd_project_revenue_tab(m),
RAW_COST = NVL(RAW_COST,0) + l_upd_pfc_raw_cost_tab(m),
BURDENED_COST = NVL(BURDENED_COST,0) + l_upd_pfc_burdened_cost_tab(m),
REVENUE = NVL(REVENUE,0) + l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
QUANTITY = NVL(QUANTITY,0) + l_upd_qty_tab(m),
INIT_QUANTITY = NVL(INIT_QUANTITY,0) + l_upd_qty_tab(m)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines SET
TXN_STANDARD_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_raw_cost / quantity),
BURDEN_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_burdened_cost/quantity)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines SET
TXN_STANDARD_BILL_RATE = decode (nvl(quantity,0), 0, NULL, txn_revenue/quantity)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines SET
TXN_STANDARD_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_raw_cost / quantity),
BURDEN_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_burdened_cost/quantity),
TXN_STANDARD_BILL_RATE = decode (nvl(quantity,0), 0, NULL, txn_revenue/quantity)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines
SET
TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = l_upd_pfc_revenue_tab(m),
TXN_RAW_COST = l_upd_plan_txn_raw_cost_tab(m),
TXN_BURDENED_COST = l_upd_plan_txn_brdn_cost_tab(m),
TXN_REVENUE = l_upd_plan_txn_revenue_tab(m),
PROJECT_RAW_COST = l_upd_plan_proj_raw_cost_tab(m),
PROJECT_BURDENED_COST = l_upd_plan_proj_brdn_cost_tab(m),
PROJECT_REVENUE = l_upd_plan_proj_revenue_tab(m),
RAW_COST = l_upd_plan_pfc_raw_cost_tab(m),
BURDENED_COST = l_upd_plan_pfc_brdn_cost_tab(m),
REVENUE = l_upd_plan_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
QUANTITY = l_upd_plan_qty_tab(m),
INIT_QUANTITY = l_upd_qty_tab(m),
TXN_STANDARD_COST_RATE = l_upd_cost_rate_override_tab(m), /* Bug 4398799, 4071198 start */
BURDEN_COST_RATE = l_upd_bcost_rate_override_tab(m),
TXN_STANDARD_BILL_RATE = l_upd_bill_rate_override_tab(m) /* Bug 4398799, 4071198 end */
WHERE budget_line_id = l_bdgt_line_id_tab(m);
-- Bug 4292083: When the Target timephase is None, update the plan
-- columns with total amounts (Actual + Planning_Txn). Since we no
-- longer call the UPDATE_TOTAL_PLAN_AMTS API in the Forecast Gen
-- wrapper, we need to modify the update logic here.
-- ER 4376722: Split original UPDATE statement into 2 separate UPDATE
-- statements based on p_calling_context. When p_calling_context is
-- 'FP_GEN_FCST_COPY_ACTUAL', changed the update logic as follows:
-- Before: Set amount = NVL(existing amount,0) + update amount.
-- After: If existing amount is null, then set amount = update amount.
-- If existing amount is not null, then
-- set amount = existing amount + NVL(update amount, 0)
-- The new logic preserves the non-null existing amounts.
-- This change is necessary in case update revenue is Null. Using the
-- old logic, we would set revenue to NVL(existing revenue,0) + Null,
-- which is just Null. In other words, the existing revenue would be lost.
-- Using the new logic, we would set revenue to existing revenue +
-- NVL(NULL,0) = existing revenue. In this case, the existing amounts
-- are manually added.
-- In the ELSE case, the context is 'WP_APPLY_PROGRESS_TO_WORKING'
-- so we use the same UPDATE statement as before to avoid changing
-- Workplan behavior.
IF p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
FORALL m in 1..l_upd_period_name_tab.count
UPDATE pa_budget_lines
SET -- Begin 4376722, 4292083 changes --
TXN_RAW_COST =
DECODE(TXN_RAW_COST, null, l_upd_txn_raw_cost_tab(m),
TXN_RAW_COST + NVL(l_upd_txn_raw_cost_tab(m),0)),
TXN_BURDENED_COST =
DECODE(TXN_BURDENED_COST, null, l_upd_txn_burdened_cost_tab(m),
TXN_BURDENED_COST + NVL(l_upd_txn_burdened_cost_tab(m),0)),
TXN_REVENUE =
DECODE(TXN_REVENUE, null, l_upd_txn_revenue_tab(m),
TXN_REVENUE + NVL(l_upd_txn_revenue_tab(m),0)),
PROJECT_RAW_COST =
DECODE(PROJECT_RAW_COST, null, l_upd_project_raw_cost_tab(m),
PROJECT_RAW_COST + NVL(l_upd_project_raw_cost_tab(m),0)),
PROJECT_BURDENED_COST =
DECODE(PROJECT_BURDENED_COST, null, l_upd_proj_burdened_cost_tab(m),
PROJECT_BURDENED_COST + NVL(l_upd_proj_burdened_cost_tab(m),0)),
PROJECT_REVENUE =
DECODE(PROJECT_REVENUE, null, l_upd_project_revenue_tab(m),
PROJECT_REVENUE + NVL(l_upd_project_revenue_tab(m),0)),
RAW_COST =
DECODE(RAW_COST, null, l_upd_pfc_raw_cost_tab(m),
RAW_COST + NVL(l_upd_pfc_raw_cost_tab(m),0)),
BURDENED_COST =
DECODE(BURDENED_COST, null, l_upd_pfc_burdened_cost_tab(m),
BURDENED_COST + NVL(l_upd_pfc_burdened_cost_tab(m),0)),
REVENUE =
DECODE(REVENUE, null, l_upd_pfc_revenue_tab(m),
REVENUE + NVL(l_upd_pfc_revenue_tab(m),0)),
QUANTITY =
DECODE(QUANTITY, null, l_upd_qty_tab(m),
QUANTITY + NVL(l_upd_qty_tab(m),0)),
-- End 4376722, 4292083 changes --
TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
INIT_QUANTITY = l_upd_qty_tab(m)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines
SET -- Begin 4292083 changes --
TXN_RAW_COST = NVL(TXN_RAW_COST,0)
+ l_upd_txn_raw_cost_tab(m),
TXN_BURDENED_COST = NVL(TXN_BURDENED_COST,0)
+ l_upd_txn_burdened_cost_tab(m),
TXN_REVENUE = NVL(TXN_REVENUE,0)
+ l_upd_txn_revenue_tab(m),
PROJECT_RAW_COST = NVL(PROJECT_RAW_COST,0)
+ l_upd_project_raw_cost_tab(m),
PROJECT_BURDENED_COST = NVL(PROJECT_BURDENED_COST,0)
+ l_upd_proj_burdened_cost_tab(m),
PROJECT_REVENUE = NVL(PROJECT_REVENUE,0)
+ l_upd_project_revenue_tab(m),
RAW_COST = NVL(RAW_COST,0)
+ l_upd_pfc_raw_cost_tab(m),
BURDENED_COST = NVL(BURDENED_COST,0)
+ l_upd_pfc_burdened_cost_tab(m),
REVENUE = NVL(REVENUE,0)
+ l_upd_pfc_revenue_tab(m),
QUANTITY = NVL(QUANTITY,0) + l_upd_qty_tab(m),
-- End 4292083 changes --
TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
INIT_QUANTITY = l_upd_qty_tab(m)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
-- Bug 4398799: Split original UPDATE statement into 2 separate UPDATE
-- statements based on p_calling_context. When p_calling_context is
-- 'FP_GEN_FCST_COPY_ACTUAL', we continue populating the rate override
-- columns. In the ELSE case, the context is 'WP_APPLY_PROGRESS_TO_WORKING'
-- so we populate the standard rate columns instead. Everything else about
-- the UPDATE statements is unchanged.
IF p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
FORALL m in 1..l_upd_period_name_tab.count
UPDATE pa_budget_lines
SET
TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = l_upd_pfc_revenue_tab(m),
TXN_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_REVENUE = l_upd_project_revenue_tab(m),
RAW_COST = l_upd_pfc_raw_cost_tab(m),
BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
REVENUE = l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
QUANTITY = l_upd_qty_tab(m),
INIT_QUANTITY = l_upd_qty_tab(m),
TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(m), /* Bug 4071198 start */
BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(m),
TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(m) /* Bug 4071198 end */
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines
SET
TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = l_upd_pfc_revenue_tab(m),
TXN_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_REVENUE = l_upd_project_revenue_tab(m),
RAW_COST = l_upd_pfc_raw_cost_tab(m),
BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
REVENUE = l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
QUANTITY = l_upd_qty_tab(m),
INIT_QUANTITY = l_upd_qty_tab(m),
TXN_STANDARD_COST_RATE = l_upd_cost_rate_override_tab(m), /* Bug 4398799, 4071198 start */
BURDEN_COST_RATE = l_upd_bcost_rate_override_tab(m),
TXN_STANDARD_BILL_RATE = l_upd_bill_rate_override_tab(m) /* Bug 4398799, 4071198 end */
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines
SET
TXN_INIT_RAW_COST = NVL(TXN_INIT_RAW_COST,0) + l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = NVL(TXN_INIT_BURDENED_COST,0) + l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = NVL(TXN_INIT_REVENUE,0) + l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = NVL(PROJECT_INIT_RAW_COST,0) + l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = NVL(PROJECT_INIT_BURDENED_COST,0) + l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = NVL(PROJECT_INIT_REVENUE,0) + l_upd_project_revenue_tab(m),
INIT_RAW_COST = NVL(INIT_RAW_COST,0) + l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = NVL(INIT_BURDENED_COST,0) + l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = NVL(INIT_REVENUE,0) + l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
INIT_QUANTITY = NVL(INIT_QUANTITY,0) + l_upd_qty_tab(m)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines
SET
TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
INIT_REVENUE = l_upd_pfc_revenue_tab(m),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
INIT_QUANTITY = l_upd_qty_tab(m)
WHERE budget_line_id = l_bdgt_line_id_tab(m);
UPDATE pa_budget_lines
SET
TXN_RAW_COST =
DECODE(TXN_INIT_RAW_COST, null, l_upd_txn_raw_cost_tab(m2),
TXN_INIT_RAW_COST + NVL(l_upd_txn_raw_cost_tab(m2),0)),
TXN_BURDENED_COST =
DECODE(TXN_INIT_BURDENED_COST, null, l_upd_txn_burdened_cost_tab(m2),
TXN_INIT_BURDENED_COST + NVL(l_upd_txn_burdened_cost_tab(m2),0)),
TXN_REVENUE =
DECODE(TXN_INIT_REVENUE, null, l_upd_txn_revenue_tab(m2),
TXN_INIT_REVENUE + NVL(l_upd_txn_revenue_tab(m2),0)),
PROJECT_RAW_COST =
DECODE(PROJECT_INIT_RAW_COST, null, l_upd_project_raw_cost_tab(m2),
PROJECT_INIT_RAW_COST + NVL(l_upd_project_raw_cost_tab(m2),0)),
PROJECT_BURDENED_COST =
DECODE(PROJECT_INIT_BURDENED_COST, null, l_upd_proj_burdened_cost_tab(m2),
PROJECT_INIT_BURDENED_COST + NVL(l_upd_proj_burdened_cost_tab(m2),0)),
PROJECT_REVENUE =
DECODE(PROJECT_INIT_REVENUE, null, l_upd_project_revenue_tab(m2),
PROJECT_INIT_REVENUE + NVL(l_upd_project_revenue_tab(m2),0)),
RAW_COST =
DECODE(INIT_RAW_COST, null, l_upd_pfc_raw_cost_tab(m2),
INIT_RAW_COST + NVL(l_upd_pfc_raw_cost_tab(m2),0)),
BURDENED_COST =
DECODE(INIT_BURDENED_COST, null, l_upd_pfc_burdened_cost_tab(m2),
INIT_BURDENED_COST + NVL(l_upd_pfc_burdened_cost_tab(m2),0)),
REVENUE =
DECODE(INIT_REVENUE, null, l_upd_pfc_revenue_tab(m2),
INIT_REVENUE + NVL(l_upd_pfc_revenue_tab(m2),0)),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
QUANTITY =
DECODE(INIT_QUANTITY, null, l_upd_qty_tab(m2),
INIT_QUANTITY + NVL(l_upd_qty_tab(m2),0)),
PROJFUNC_COST_RATE_TYPE = l_projfunc_cost_rate_type_tab(m2),
PROJFUNC_REV_RATE_TYPE = l_projfunc_rev_rate_type_tab(m2),
PROJECT_COST_RATE_TYPE = l_project_cost_rate_type_tab(m2),
PROJECT_REV_RATE_TYPE = l_project_rev_rate_type_tab(m2),
TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(m2),
BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(m2),
TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(m2)
WHERE budget_line_id = l_bdgt_line_id_tab(m2);
UPDATE pa_budget_lines
SET
TXN_RAW_COST = l_upd_txn_raw_cost_tab(m2),
TXN_BURDENED_COST = l_upd_txn_burdened_cost_tab(m2),
TXN_REVENUE = l_upd_txn_revenue_tab(m2),
PROJECT_RAW_COST = l_upd_project_raw_cost_tab(m2),
PROJECT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m2),
PROJECT_REVENUE = l_upd_project_revenue_tab(m2),
RAW_COST = l_upd_pfc_raw_cost_tab(m2),
BURDENED_COST = l_upd_pfc_burdened_cost_tab(m2),
REVENUE = l_upd_pfc_revenue_tab(m2),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
QUANTITY = l_upd_qty_tab(m2),
PROJFUNC_COST_RATE_TYPE = l_projfunc_cost_rate_type_tab(m2),
PROJFUNC_REV_RATE_TYPE = l_projfunc_rev_rate_type_tab(m2),
PROJECT_COST_RATE_TYPE = l_project_cost_rate_type_tab(m2),
PROJECT_REV_RATE_TYPE = l_project_rev_rate_type_tab(m2),
TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(m2),
BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(m2),
TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(m2)
WHERE budget_line_id = l_bdgt_line_id_tab(m2);
/* dbms_output.put_line('Insert count when the ins_flag is null:
'|| l_ins_period_name_tab.count);*/
l_bl_id_tab.delete;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
PROJECT_INIT_RAW_COST,
PROJECT_INIT_BURDENED_COST,
PROJECT_INIT_REVENUE,
INIT_RAW_COST,
INIT_BURDENED_COST,
INIT_REVENUE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INIT_QUANTITY,
project_currency_code,
projfunc_currency_code,
QUANTITY,
TXN_COST_RATE_OVERRIDE, /* Bug 4071198 */
BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE ) /* Bug 4071198 */
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_ins_period_name_tab(n),
l_ins_start_date_tab(n),
l_ins_end_date_tab(n),
p_txn_currency_code,
l_ins_txn_raw_cost_tab(n),
l_ins_txn_burdened_cost_tab(n),
l_ins_txn_revenue_tab(n),
l_ins_project_raw_cost_tab(n),
l_ins_proj_burdened_cost_tab(n),
l_ins_project_revenue_tab(n),
l_ins_pfc_raw_cost_tab(n),
l_ins_pfc_burdened_cost_tab(n),
l_ins_pfc_revenue_tab(n),
l_ins_txn_raw_cost_tab(n),
l_ins_txn_burdened_cost_tab(n),
l_ins_txn_revenue_tab(n),
l_ins_project_raw_cost_tab(n),
l_ins_proj_burdened_cost_tab(n),
l_ins_project_revenue_tab(n),
l_ins_pfc_raw_cost_tab(n),
l_ins_pfc_burdened_cost_tab(n),
l_ins_pfc_revenue_tab(n),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_ins_qty_tab(n),
l_pc_code,
l_pfc_code,
l_ins_qty_tab(n) ,
l_ins_cost_rate_override_tab(n), /* Bug 4071198 */
l_ins_bcost_rate_override_tab(n), /* Bug 4071198 */
l_ins_bill_rate_override_tab(n)) /* Bug 4071198 */
RETURNING budget_line_id
BULK COLLECT INTO l_bl_id_tab;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
PROJECT_INIT_RAW_COST,
PROJECT_INIT_BURDENED_COST,
PROJECT_INIT_REVENUE,
INIT_RAW_COST,
INIT_BURDENED_COST,
INIT_REVENUE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INIT_QUANTITY,
project_currency_code,
projfunc_currency_code,
QUANTITY,
TXN_STANDARD_COST_RATE, /* Bug 4398799, 4071198 */
BURDEN_COST_RATE, /* Bug 4398799, 4071198 */
TXN_STANDARD_BILL_RATE ) /* Bug 4398799, 4071198 */
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_ins_period_name_tab(n),
l_ins_start_date_tab(n),
l_ins_end_date_tab(n),
p_txn_currency_code,
l_ins_txn_raw_cost_tab(n),
l_ins_txn_burdened_cost_tab(n),
l_ins_txn_revenue_tab(n),
l_ins_project_raw_cost_tab(n),
l_ins_proj_burdened_cost_tab(n),
l_ins_project_revenue_tab(n),
l_ins_pfc_raw_cost_tab(n),
l_ins_pfc_burdened_cost_tab(n),
l_ins_pfc_revenue_tab(n),
l_ins_txn_raw_cost_tab(n),
l_ins_txn_burdened_cost_tab(n),
l_ins_txn_revenue_tab(n),
l_ins_project_raw_cost_tab(n),
l_ins_proj_burdened_cost_tab(n),
l_ins_project_revenue_tab(n),
l_ins_pfc_raw_cost_tab(n),
l_ins_pfc_burdened_cost_tab(n),
l_ins_pfc_revenue_tab(n),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_ins_qty_tab(n),
l_pc_code,
l_pfc_code,
l_ins_qty_tab(n) ,
l_ins_cost_rate_override_tab(n), /* Bug 4071198 */
l_ins_bcost_rate_override_tab(n), /* Bug 4071198 */
l_ins_bill_rate_override_tab(n)) /* Bug 4071198 */
RETURNING budget_line_id
BULK COLLECT INTO l_bl_id_tab;
l_bl_id_tab.delete;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
PROJECT_INIT_RAW_COST,
PROJECT_INIT_BURDENED_COST,
PROJECT_INIT_REVENUE,
INIT_RAW_COST,
INIT_BURDENED_COST,
INIT_REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INIT_QUANTITY,
project_currency_code,
projfunc_currency_code)
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_ins_period_name_tab(n),
l_ins_start_date_tab(n),
l_ins_end_date_tab(n),
p_txn_currency_code,
l_ins_txn_raw_cost_tab(n),
l_ins_txn_burdened_cost_tab(n),
l_ins_txn_revenue_tab(n),
l_ins_project_raw_cost_tab(n),
l_ins_proj_burdened_cost_tab(n),
l_ins_project_revenue_tab(n),
l_ins_pfc_raw_cost_tab(n),
l_ins_pfc_burdened_cost_tab(n),
l_ins_pfc_revenue_tab(n),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_ins_qty_tab(n),
l_pc_code,
l_pfc_code );
l_bl_id_tab.delete;
INSERT INTO
PA_BUDGET_LINES(BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
BUDGET_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
QUANTITY,
project_currency_code,
projfunc_currency_code,
PROJFUNC_COST_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE,
PROJECT_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE )
VALUES(p_budget_version_id,
p_resource_assignment_id,
l_ins_period_name_tab(n2),
l_ins_start_date_tab(n2),
l_ins_end_date_tab(n2),
p_txn_currency_code,
l_ins_txn_raw_cost_tab(n2),
l_ins_txn_burdened_cost_tab(n2),
l_ins_txn_revenue_tab(n2),
l_ins_project_raw_cost_tab(n2),
l_ins_proj_burdened_cost_tab(n2),
l_ins_project_revenue_tab(n2),
l_ins_pfc_raw_cost_tab(n2),
l_ins_pfc_burdened_cost_tab(n2),
l_ins_pfc_revenue_tab(n2),
PA_BUDGET_LINES_S.nextval,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_ins_qty_tab(n2),
l_pc_code,
l_pfc_code,
l_projfunc_cost_rate_type_tab(n2),
l_projfunc_rev_rate_type_tab(n2),
l_project_cost_rate_type_tab(n2),
l_project_rev_rate_type_tab(n2),
l_ins_cost_rate_override_tab(n2),
l_ins_bcost_rate_override_tab(n2),
l_ins_bill_rate_override_tab(n2))
RETURNING budget_line_id
BULK COLLECT INTO l_bl_id_tab;
SELECT ra.spread_curve_id,t.spread_curve_code
INTO l_spread_curve_id,l_spread_curve_code
FROM pa_resource_assignments ra,pa_spread_curves_b t
WHERE ra.resource_assignment_id = p_resource_assignment_id
AND ra.spread_curve_id = t.spread_curve_id;
SELECT count(*)
INTO l_multi_bdgt_lines
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code;
UPDATE pa_resource_assignments
SET spread_curve_id = NULL,
sp_fixed_date = NULL
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date,
min(bl.start_date),
max(bl.end_date)
FROM pa_resource_assignments ra,
pa_budget_versions bv, -- Added for Perf Bug 4469690
pa_budget_lines bl,
pa_periods_all start_pd,
pa_periods_all end_pd
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
AND start_pd.org_id = l_fp_cols_rec.x_org_id
AND end_pd.org_id = l_fp_cols_rec.x_org_id
AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
GROUP BY bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date;
SELECT bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date,
min(bl.start_date),
max(bl.end_date)
FROM pa_resource_assignments ra,
pa_budget_versions bv, -- Added for Perf Bug 4469690
pa_budget_lines bl,
gl_period_statuses start_pd,
gl_period_statuses end_pd
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
AND start_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND start_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND start_pd.adjustment_period_flag = 'N'
AND end_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND end_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND end_pd.adjustment_period_flag = 'N'
AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
GROUP BY bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date;
SELECT bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
ra.planning_start_date,
ra.planning_end_date,
min(bl.start_date),
max(bl.end_date)
FROM pa_resource_assignments ra,
pa_budget_versions bv, -- Added for Perf Bug 4469690
pa_budget_lines bl
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
GROUP BY bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date;
SELECT ra.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
ra.planning_start_date,
ra.planning_end_date,
min(t.start_date),
max(t.finish_date)
FROM pa_resource_assignments ra,
PA_PROG_ACT_BY_PERIOD_TEMP t
WHERE ra.resource_assignment_id =
nvl(t.resource_assignment_id,t.HIDDEN_RES_ASSGN_ID)
AND ra.budget_version_id = p_budget_version_id
GROUP BY ra.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date;
SELECT bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date,
min(bl.start_date),
max(bl.end_date)
FROM pa_resource_assignments ra,
pa_budget_versions bv, -- Added for Perf Bug 4469690
pa_budget_lines bl,
pji_fm_xbs_accum_tmp1 pji_tmp,
pa_periods_all start_pd,
pa_periods_all end_pd
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
AND ra.resource_assignment_id = pji_tmp.source_id
AND start_pd.org_id = l_fp_cols_rec.x_org_id
AND end_pd.org_id = l_fp_cols_rec.x_org_id
AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
GROUP BY bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date;
SELECT bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date,
min(bl.start_date),
max(bl.end_date)
FROM pa_resource_assignments ra,
pa_budget_versions bv, -- Added for Perf Bug 4469690
pa_budget_lines bl,
pji_fm_xbs_accum_tmp1 pji_tmp,
gl_period_statuses start_pd,
gl_period_statuses end_pd
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
AND ra.resource_assignment_id = pji_tmp.source_id
AND start_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND start_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND start_pd.adjustment_period_flag = 'N'
AND end_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND end_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND end_pd.adjustment_period_flag = 'N'
AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
GROUP BY bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
start_pd.start_date,
end_pd.end_date;
SELECT bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
ra.planning_start_date,
ra.planning_end_date,
min(bl.start_date),
max(bl.end_date)
FROM pa_resource_assignments ra,
pa_budget_versions bv, -- Added for Perf Bug 4469690
pa_budget_lines bl,
pji_fm_xbs_accum_tmp1 pji_tmp
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
AND ra.resource_assignment_id = pji_tmp.source_id
GROUP BY bl.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date;
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date,
MIN(tmp.txn_planning_start_date),
MAX(tmp.txn_planning_end_date)
FROM pa_res_list_map_tmp4 tmp,
pa_resource_assignments ra
WHERE ra.resource_assignment_id = tmp.txn_resource_assignment_id
AND ra.budget_version_id = p_budget_version_id
GROUP BY ra.resource_assignment_id,
ra.planning_start_date,
ra.planning_end_date;
SELECT NVL(wp_version_flag,'N') INTO l_wp_version_flag FROM
pa_budget_versions WHERE budget_version_id = p_budget_version_id;
SELECT pd.end_date
INTO l_etc_start_period_end_date
FROM pa_periods_all pd
WHERE pd.org_id = l_fp_cols_rec.x_org_id
AND l_etc_start_date between pd.start_date and pd.end_date;
SELECT pd.end_date
INTO l_etc_start_period_end_date
FROM gl_period_statuses pd
WHERE pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND l_etc_start_date between pd.start_date and pd.end_date;
* end date based on the update value pl/sql table. This is done
* to handle the Forecast case when both the planning end date
* and max commitment / billing event date fall before the ETC
* start date and we need to set the planning end date to the
* last day of the ETC start date period. */
IF l_max_end_date_tab(i) > l_plan_end_date_tab(i) THEN
l_upd_flag := 'Y';
END IF; -- end populating update pl/sql tables
/* Update synched planning dates in the db. */
FORALL m IN 1..l_upd_res_asg_id_tab.count
UPDATE pa_resource_assignments
SET planning_start_date = l_upd_start_date_tab(m),
planning_end_date = l_upd_end_date_tab(m)
WHERE resource_assignment_id = l_upd_res_asg_id_tab(m);
UPDATE pa_budget_lines
SET start_date = l_upd_start_date_tab(m),
end_date = l_upd_end_date_tab(m)
WHERE resource_assignment_id = l_upd_res_asg_id_tab(m);
* 'N': not for entire version, will update reporting lines only for the passes*
* resource assignments. *
* 'Y': for entire version, will update reporting lines for all resource *
* assignments for the passed budget version id without looking into *
* p_res_asg_id_tab. *
* Valid values for parameter P_ACTIVITY_CODE: *
* 'UPDATE': update reporting lines *
* 'DELETE': delete reporting lines *
**/
PROCEDURE BLK_UPD_REPORTING_LINES_WRP
(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_ENTIRE_VERSION_FLAG IN VARCHAR2,
P_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IDTABTYP,
P_ACTIVITY_CODE 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_maintain_actual_pub.blk_upd_reporting_lines_wrp';
l_rep_budget_line_id_tab.delete;
l_rep_res_assignment_id_tab.delete;
l_rep_start_date_tab.delete;
l_rep_end_date_tab.delete;
l_rep_period_name_tab.delete;
l_rep_txn_curr_code_tab.delete;
l_rep_quantity_tab.delete;
l_rep_txn_raw_cost_tab.delete;
l_rep_txn_burdened_cost_tab.delete;
l_rep_txn_revenue_tab.delete;
l_rep_project_curr_code_tab.delete;
l_rep_project_raw_cost_tab.delete;
l_rep_project_burden_cost_tab.delete;
l_rep_project_revenue_tab.delete;
l_rep_projfunc_curr_code_tab.delete;
l_rep_projfunc_raw_cost_tab.delete;
l_rep_projfunc_burden_cost_tab.delete;
l_rep_projfunc_revenue_tab.delete;
l_rep_act_quantity_tab.delete;
l_rep_txn_act_raw_cost_tab.delete;
l_rep_txn_act_burd_cost_tab.delete;
l_rep_txn_act_rev_tab.delete;
l_rep_prj_act_raw_cost_tab.delete;
l_rep_prj_act_burd_cost_tab.delete;
l_rep_prj_act_rev_tab.delete;
l_rep_pf_act_raw_cost_tab.delete;
l_rep_pf_act_burd_cost_tab.delete;
l_rep_pf_act_rev_tab.delete;
SELECT budget_line_id,
resource_assignment_id,
start_date,
end_date,
period_name,
txn_currency_code,
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * quantity,
'UPDATE', quantity),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * txn_raw_cost,
'UPDATE', txn_raw_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * txn_burdened_cost,
'UPDATE', txn_burdened_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * txn_revenue,
'UPDATE', txn_revenue),
project_currency_code,
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * project_raw_cost,
'UPDATE', project_raw_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * project_burdened_cost,
'UPDATE', project_burdened_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * project_revenue,
'UPDATE', project_revenue),
projfunc_currency_code,
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * raw_cost,
'UPDATE', raw_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * burdened_cost,
'UPDATE', burdened_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * revenue,
'UPDATE', revenue)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * init_quantity,
'UPDATE', init_quantity)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * txn_init_raw_cost,
'UPDATE', txn_init_raw_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * txn_init_burdened_cost,
'UPDATE', txn_init_burdened_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * txn_init_revenue,
'UPDATE', txn_init_revenue)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * project_init_raw_cost,
'UPDATE', project_init_raw_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * project_init_burdened_cost,
'UPDATE', project_init_burdened_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * project_init_revenue,
'UPDATE', project_init_revenue)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * init_raw_cost,
'UPDATE', init_raw_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * init_burdened_cost,
'UPDATE', init_burdened_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * init_revenue,
'UPDATE', init_revenue)
BULK COLLECT INTO
l_rep_budget_line_id_tab,
l_rep_res_assignment_id_tab,
l_rep_start_date_tab,
l_rep_end_date_tab,
l_rep_period_name_tab,
l_rep_txn_curr_code_tab,
l_rep_quantity_tab,
l_rep_txn_raw_cost_tab,
l_rep_txn_burdened_cost_tab,
l_rep_txn_revenue_tab,
l_rep_project_curr_code_tab,
l_rep_project_raw_cost_tab,
l_rep_project_burden_cost_tab,
l_rep_project_revenue_tab,
l_rep_projfunc_curr_code_tab,
l_rep_projfunc_raw_cost_tab,
l_rep_projfunc_burden_cost_tab,
l_rep_projfunc_revenue_tab
,l_rep_act_quantity_tab
,l_rep_txn_act_raw_cost_tab
,l_rep_txn_act_burd_cost_tab
,l_rep_txn_act_rev_tab
,l_rep_prj_act_raw_cost_tab
,l_rep_prj_act_burd_cost_tab
,l_rep_prj_act_rev_tab
,l_rep_pf_act_raw_cost_tab
,l_rep_pf_act_burd_cost_tab
,l_rep_pf_act_rev_tab
FROM pa_budget_lines
WHERE budget_version_id = P_BUDGET_VERSION_ID;
DELETE FROM pa_fp_calc_amt_tmp1;
INSERT INTO pa_fp_calc_amt_tmp1 (resource_assignment_id)
VALUES (p_res_asg_id_tab(i));
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N2)*/
bl.budget_line_id,
bl.resource_assignment_id,
bl.start_date,
bl.end_date,
bl.period_name,
bl.txn_currency_code,
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.quantity,
'UPDATE', bl.quantity),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.txn_raw_cost,
'UPDATE', bl.txn_raw_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.txn_burdened_cost,
'UPDATE', bl.txn_burdened_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.txn_revenue,
'UPDATE', bl.txn_revenue),
project_currency_code,
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.project_raw_cost,
'UPDATE', bl.project_raw_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.project_burdened_cost,
'UPDATE', bl.project_burdened_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.project_revenue,
'UPDATE', bl.project_revenue),
bl.projfunc_currency_code,
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.raw_cost,
'UPDATE', bl.raw_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.burdened_cost,
'UPDATE', bl.burdened_cost),
DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.revenue,
'UPDATE', bl.revenue)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.init_quantity,
'UPDATE', bl.init_quantity)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.txn_init_raw_cost,
'UPDATE', bl.txn_init_raw_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.txn_init_burdened_cost,
'UPDATE', bl.txn_init_burdened_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.txn_init_revenue,
'UPDATE', bl.txn_init_revenue)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.project_init_raw_cost,
'UPDATE', bl.project_init_raw_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.project_init_burdened_cost,
'UPDATE', bl.project_init_burdened_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.project_init_revenue,
'UPDATE', bl.project_init_revenue)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.init_raw_cost,
'UPDATE', bl.init_raw_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.init_burdened_cost,
'UPDATE', bl.init_burdened_cost)
,DECODE(P_ACTIVITY_CODE,
'DELETE', (-1) * bl.init_revenue,
'UPDATE', bl.init_revenue)
BULK COLLECT INTO
l_rep_budget_line_id_tab,
l_rep_res_assignment_id_tab,
l_rep_start_date_tab,
l_rep_end_date_tab,
l_rep_period_name_tab,
l_rep_txn_curr_code_tab,
l_rep_quantity_tab,
l_rep_txn_raw_cost_tab,
l_rep_txn_burdened_cost_tab,
l_rep_txn_revenue_tab,
l_rep_project_curr_code_tab,
l_rep_project_raw_cost_tab,
l_rep_project_burden_cost_tab,
l_rep_project_revenue_tab,
l_rep_projfunc_curr_code_tab,
l_rep_projfunc_raw_cost_tab,
l_rep_projfunc_burden_cost_tab,
l_rep_projfunc_revenue_tab
,l_rep_act_quantity_tab
,l_rep_txn_act_raw_cost_tab
,l_rep_txn_act_burd_cost_tab
,l_rep_txn_act_rev_tab
,l_rep_prj_act_raw_cost_tab
,l_rep_prj_act_burd_cost_tab
,l_rep_prj_act_rev_tab
,l_rep_pf_act_raw_cost_tab
,l_rep_pf_act_burd_cost_tab
,l_rep_pf_act_rev_tab
FROM pa_budget_lines bl, pa_fp_calc_amt_tmp1 tmp
WHERE bl.budget_version_id = P_BUDGET_VERSION_ID
AND bl.resource_assignment_id = tmp.resource_assignment_id;
(p_msg => 'Before calling pa_fp_pji_intg_pkg.ublk_update_reporting_lines',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_PJI_INTG_PKG.BLK_UPDATE_REPORTING_LINES
(p_calling_module => 'MAINTAIN_ACTUAL_AMT_RA_API'
,p_activity_code => p_activity_code
,p_budget_version_id => p_budget_version_id
,p_rep_budget_line_id_tab => l_rep_budget_line_id_tab
,p_rep_res_assignment_id_tab => l_rep_res_assignment_id_tab
,p_rep_start_date_tab => l_rep_start_date_tab
,p_rep_end_date_tab => l_rep_end_date_tab
,p_rep_period_name_tab => l_rep_period_name_tab
,p_rep_txn_curr_code_tab => l_rep_txn_curr_code_tab
,p_rep_quantity_tab => l_rep_quantity_tab
,p_rep_txn_raw_cost_tab => l_rep_txn_raw_cost_tab
,p_rep_txn_burdened_cost_tab => l_rep_txn_burdened_cost_tab
,p_rep_txn_revenue_tab => l_rep_txn_revenue_tab
,p_rep_project_curr_code_tab => l_rep_project_curr_code_tab
,p_rep_project_raw_cost_tab => l_rep_project_raw_cost_tab
,p_rep_project_burden_cost_tab => l_rep_project_burden_cost_tab
,p_rep_project_revenue_tab => l_rep_project_revenue_tab
,p_rep_projfunc_curr_code_tab => l_rep_projfunc_curr_code_tab
,p_rep_projfunc_raw_cost_tab => l_rep_projfunc_raw_cost_tab
,p_rep_projfunc_burden_cost_tab => l_rep_projfunc_burden_cost_tab
,p_rep_projfunc_revenue_tab => l_rep_projfunc_revenue_tab
,p_rep_act_quantity_tab => l_rep_act_quantity_tab
,p_rep_txn_act_raw_cost_tab => l_rep_txn_act_raw_cost_tab
,p_rep_txn_act_burd_cost_tab => l_rep_txn_act_burd_cost_tab
,p_rep_txn_act_rev_tab => l_rep_txn_act_rev_tab
,p_rep_prj_act_raw_cost_tab => l_rep_prj_act_raw_cost_tab
,p_rep_prj_act_burd_cost_tab => l_rep_prj_act_burd_cost_tab
,p_rep_prj_act_rev_tab => l_rep_prj_act_rev_tab
,p_rep_pf_act_raw_cost_tab => l_rep_pf_act_raw_cost_tab
,p_rep_pf_act_burd_cost_tab => l_rep_pf_act_burd_cost_tab
,p_rep_pf_act_rev_tab => l_rep_pf_act_rev_tab
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_return_status => x_return_status );
pa_fp_pji_intg_pkg.blk_update_reporting_lines:'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
SELECT resource_assignment_id,
planning_start_date,
planning_end_date
BULK COLLECT INTO
l_res_asg_id_tab,
l_planning_start_date_tab,
l_planning_end_date_tab
FROM pa_resource_assignments
WHERE budget_version_id = P_BUDGET_VERSION_ID;
SELECT budget_line_id
BULK COLLECT INTO
l_budget_line_id_tab
FROM pa_budget_lines
WHERE resource_assignment_id = l_res_asg_id_tab(i);
SELECT MIN(start_date),
MAX(end_date),
SUM(NVL(init_quantity,0)),
SUM(NVL(quantity,0))
INTO l_min_start_date,
l_max_end_date,
l_sum_init_quantity,
l_sum_plan_quantity
FROM pa_budget_lines
WHERE resource_assignment_id = l_res_asg_id_tab(i);
UPDATE pa_budget_lines
SET start_date = l_min_start_date,
end_date = l_max_end_date
WHERE budget_line_id = l_budget_line_id_tab(j);
UPDATE pa_resource_assignments
SET planning_start_date = l_upd_planning_start_date_tab(i),
planning_end_date = l_upd_planning_end_date_tab(i)
WHERE resource_assignment_id = l_upd_res_asg_id_tab(i);