[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fpt.PLAN_CLASS_CODE
INTO l_plan_class_code
FROM PA_BUDGET_VERSIONS bv,
PA_FIN_PLAN_TYPES_B fpt
WHERE bv.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND bv.fin_plan_type_id = fpt.fin_plan_type_id;
SELECT rbc.resource_assignment_id,
rbc.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override
BULK COLLECT
INTO l_ra_id_tab,
l_ipm_currency_code_tab,
l_rc_rate_override_tab,
l_bc_rate_override_tab
FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_budget_version_id;
SELECT rbc.resource_assignment_id,
rbc.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override
BULK COLLECT
INTO l_ra_id_tab,
l_ipm_currency_code_tab,
l_rc_rate_override_tab,
l_bc_rate_override_tab
FROM pa_resource_asgn_curr rbc,
pa_resource_assignments ra
WHERE rbc.budget_version_id = p_budget_version_id
AND rbc.resource_assignment_id = ra.resource_assignment_id
AND (ra.transaction_source_code IS NOT NULL
OR (ra.transaction_source_code IS NULL
AND NOT EXISTS (SELECT null
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id )));
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 )
VALUES (
l_ra_id_tab(i),
l_ipm_currency_code_tab(i),
l_rc_rate_override_tab(i),
l_bc_rate_override_tab(i) );
SELECT NVL(APPROVED_COST_PLAN_TYPE_FLAG, 'N'),
NVL(APPROVED_REV_PLAN_TYPE_FLAG, 'N')
INTO
l_appr_cost_plan_type_flag,
l_appr_rev_plan_type_flag
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
SELECT PROJFUNC_OPP_VALUE,
PROJECT_OPP_VALUE
INTO l_pfc_project_value,
l_pc_project_value
FROM PA_PROJECT_OPP_ATTRS
WHERE PROJECT_ID = P_FP_COLS_REC.X_PROJECT_ID;
SELECT PROJFUNC_OPP_VALUE,
PROJECT_OPP_VALUE
INTO l_pfc_project_value,
l_pc_project_value
FROM PA_PROJECT_OPP_ATTRS
WHERE PROJECT_ID = P_FP_COLS_REC.X_PROJECT_ID;
SELECT DECODE( FIN_PLAN_PREFERENCE_CODE,
'REVENUE_ONLY', 'REVENUE' ,
'COST_AND_REV_SEP', 'REVENUE',
'COST_AND_REV_SAME', 'ALL')
INTO l_version_type
FROM pa_proj_fp_options
WHERE fin_plan_type_id = l_fin_plan_type_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND project_id = P_FP_COLS_REC.X_PROJECT_ID;
SELECT PROJFUNC_OPP_VALUE,
PROJECT_OPP_VALUE
INTO l_pfc_project_value,
l_pc_project_value
FROM PA_PROJECT_OPP_ATTRS
WHERE PROJECT_ID = P_FP_COLS_REC.X_PROJECT_ID;
SELECT NVL(REVENUE,0),
NVL(TOTAL_PROJECT_REVENUE,0)
INTO l_pfc_project_value,
l_pc_project_value
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = l_approved_fp_version_id;
SELECT nvl(sum(nvl(init_revenue,0)),0),
nvl(sum(nvl(project_init_revenue,0)),0)
INTO l_init_rev_sum,
l_pc_init_rev_sum
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND start_date <= p_etc_start_date - 1;
SELECT nvl(sum(nvl(init_revenue,0)),0),
nvl(sum(nvl(project_init_revenue,0)),0)
INTO l_init_rev_sum,
l_pc_init_rev_sum
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id;
END IF; -- insert/update temp table data to budget lines
/* selecting the total burdened cost for pfc amounts. */
BEGIN
SELECT nvl(sum(nvl(bl.projfunc_burdened_cost,0)),0),
nvl(sum(nvl(bl.project_burdened_cost,0)),0)
INTO l_pfc_burdened_cost,
l_pc_burdened_cost
FROM pa_fp_rollup_tmp bl,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND bl.cost_rejection_code is null
AND bl.burden_rejection_code is null
AND bl.pc_cur_conv_rejection_code is null
AND bl.pfc_cur_conv_rejection_code is null
AND bl.BILLABLE_FLAG = 'Y';
SELECT bl.budget_line_id,
nvl(bl.txn_burdened_cost,0),
nvl(bl.project_burdened_cost,0),
nvl(bl.projfunc_burdened_cost,0),
nvl(bl.quantity,0),
nvl(bl.txn_raw_cost,0),
nvl(bl.project_raw_cost,0),
nvl(bl.projfunc_raw_cost,0),
bl.resource_assignment_id,
bl.start_date,
bl.txn_currency_code
BULK COLLECT
INTO l_budget_line_id_tab,
l_txn_burdened_cost_tab,
l_pc_burdened_cost_tab,
l_burdened_cost_tab,
l_quantity_tab,
l_txn_raw_cost_tab,
l_pc_raw_cost_tab,
l_pfc_raw_cost_tab,
l_res_asg_id_tab,
l_start_date_tab,
l_txn_currency_code_tab
FROM pa_fp_rollup_tmp bl,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND bl.cost_rejection_code is null
AND bl.burden_rejection_code is null
AND bl.pc_cur_conv_rejection_code is null
AND bl.pfc_cur_conv_rejection_code is null
AND bl.BILLABLE_FLAG = 'Y'
ORDER BY bl.resource_assignment_id,
bl.start_date;
SELECT bl.budget_line_id,
nvl(bl.txn_burdened_cost,0),
nvl(bl.project_burdened_cost,0),
nvl(bl.projfunc_burdened_cost,0),
nvl(bl.quantity, 0),
nvl(bl.txn_raw_cost,0),
nvl(bl.project_raw_cost,0),
nvl(bl.projfunc_raw_cost,0),
bl.resource_assignment_id,
bl.start_date,
bl.txn_currency_code
BULK COLLECT
INTO l_budget_line_id_tab,
l_txn_burdened_cost_tab,
l_pc_burdened_cost_tab,
l_burdened_cost_tab,
l_quantity_tab,
l_txn_raw_cost_tab,
l_pc_raw_cost_tab,
l_pfc_raw_cost_tab,
l_res_asg_id_tab,
l_start_date_tab,
l_txn_currency_code_tab
FROM pa_fp_rollup_tmp bl,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND bl.start_date >= p_etc_start_date
AND bl.cost_rejection_code is null
AND bl.burden_rejection_code is null
AND bl.pc_cur_conv_rejection_code is null
AND bl.pfc_cur_conv_rejection_code is null
AND bl.BILLABLE_FLAG = 'Y'
ORDER BY bl.resource_assignment_id,
bl.start_date;
SELECT nvl(sum(nvl(bl.projfunc_burdened_cost,0)),0),
nvl(sum(nvl(bl.project_burdened_cost,0)),0)
INTO l_pfc_burdened_cost,
l_pc_burdened_cost
FROM pa_fp_rollup_tmp bl,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND bl.start_date >= p_etc_start_date
AND bl.cost_rejection_code is null
AND bl.burden_rejection_code is null
AND bl.pc_cur_conv_rejection_code is null
AND bl.pfc_cur_conv_rejection_code is null
AND bl.BILLABLE_FLAG = 'Y';
/* selecting the total burdened cost and revenue for pfc amounts. */
IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
BEGIN
SELECT nvl(sum(nvl(bl.burdened_cost,0)),0),
nvl(sum(nvl(bl.project_burdened_cost,0)),0)
INTO l_pfc_burdened_cost,
l_pc_burdened_cost
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
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
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
SELECT nvl(sum(nvl(bl.txn_revenue,0)),0),
nvl(sum(nvl(bl.project_revenue,0)),0)
INTO l_pfc_revenue,
l_pc_revenue
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
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
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
SELECT bl.budget_line_id,
nvl(bl.txn_burdened_cost,0),
nvl(bl.project_burdened_cost,0),
nvl(bl.burdened_cost,0),
nvl(bl.txn_revenue,0),
nvl(bl.project_revenue,0),
nvl(bl.revenue,0),
nvl(bl.quantity,0),
--nvl(bl.burdened_cost,0),
nvl(bl.txn_raw_cost,0),
nvl(bl.project_raw_cost,0),
nvl(bl.raw_cost,0),
bl.resource_assignment_id,
bl.start_date,
bl.txn_currency_code
BULK COLLECT
INTO l_budget_line_id_tab,
l_txn_burdened_cost_tab,
l_pc_burdened_cost_tab,
l_burdened_cost_tab,
l_txn_revenue_tab,
l_project_revenue_tab,
l_revenue_tab,
l_quantity_tab,
--l_pfc_burdened_cost_tab,
l_txn_raw_cost_tab,
l_pc_raw_cost_tab,
l_pfc_raw_cost_tab,
l_res_asg_id_tab,
l_start_date_tab,
l_txn_currency_code_tab
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
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
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID /* Added for Bug 4546405 */
ORDER BY bl.resource_assignment_id,
bl.start_date;
SELECT bl.budget_line_id,
nvl(bl.txn_burdened_cost,0),
nvl(bl.project_burdened_cost,0),
nvl(bl.burdened_cost,0),
nvl(bl.txn_revenue,0),
nvl(bl.project_revenue,0),
nvl(bl.revenue,0),
nvl(bl.quantity, 0),
--nvl(bl.burdened_cost,0),
nvl(bl.txn_raw_cost,0),
nvl(bl.project_raw_cost,0),
nvl(bl.raw_cost,0),
bl.resource_assignment_id,
bl.start_date,
bl.txn_currency_code
BULK COLLECT
INTO l_budget_line_id_tab,
l_txn_burdened_cost_tab,
l_pc_burdened_cost_tab,
l_burdened_cost_tab,
l_txn_revenue_tab,
l_project_revenue_tab,
l_revenue_tab,
l_quantity_tab,
--l_pfc_burdened_cost_tab,
l_txn_raw_cost_tab,
l_pc_raw_cost_tab,
l_pfc_raw_cost_tab,
l_res_asg_id_tab,
l_start_date_tab,
l_txn_currency_code_tab
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
AND bl.start_date >= p_etc_start_date
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
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID /* Added for Bug 4546405 */
ORDER BY bl.resource_assignment_id,
bl.start_date;
SELECT bl.budget_line_id,
nvl(bl.txn_burdened_cost,0) - nvl(bl.txn_init_burdened_cost,0),
nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0),
nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0),
nvl(bl.txn_revenue,0) - nvl(bl.txn_init_revenue,0),
nvl(bl.project_revenue,0) - nvl(bl.project_init_revenue,0),
nvl(bl.revenue,0) - nvl(bl.init_revenue,0),
nvl(bl.quantity,0) - nvl(bl.init_quantity,0),
--nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0),
nvl(bl.txn_raw_cost,0) - nvl(bl.txn_init_raw_cost,0),
nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0),
nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0),
bl.resource_assignment_id,
bl.start_date,
bl.txn_currency_code
BULK COLLECT
INTO l_budget_line_id_tab,
l_txn_burdened_cost_tab,
l_pc_burdened_cost_tab,
l_burdened_cost_tab,
l_txn_revenue_tab,
l_project_revenue_tab,
l_revenue_tab,
l_quantity_tab,
--l_pfc_burdened_cost_tab,
l_txn_raw_cost_tab,
l_pc_raw_cost_tab,
l_pfc_raw_cost_tab,
l_res_asg_id_tab,
l_start_date_tab,
l_txn_currency_code_tab
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
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
AND NVL(bl.quantity,0) <> NVL(bl.init_quantity,0)
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID /* Added for Bug 4546405 */
ORDER BY bl.resource_assignment_id,
bl.start_date;
SELECT nvl(sum(nvl(bl.burdened_cost,0)),0),
nvl(sum(nvl(bl.revenue,0)),0),
nvl(sum(nvl(bl.project_burdened_cost,0)),0),
nvl(sum(nvl(bl.project_revenue,0)),0)
INTO l_pfc_burdened_cost,
l_pfc_revenue,
l_pc_burdened_cost,
l_pc_revenue
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
AND bl.start_date >= p_etc_start_date
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
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
SELECT nvl(sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)),0),
nvl(sum(nvl(bl.revenue,0) - nvl(bl.init_revenue,0)),0),
nvl(sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)),0),
nvl(sum(nvl(bl.project_revenue,0) - nvl(bl.project_init_revenue,0)),0)
INTO l_pfc_burdened_cost,
l_pfc_revenue,
l_pc_burdened_cost,
l_pc_revenue
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks ta /* Bug 4546405, ER 4376722 */
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = bl.resource_assignment_id
AND ra.transaction_source_code is not null
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
AND NVL(bl.quantity,0) <> NVL(bl.init_quantity,0)
AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
END IF; -- insert/update temp table data to budget lines
l_tmp_budget_line_id_tab.delete;
l_tmp_txn_burdened_cost_tab.delete;
l_tmp_pc_burdened_cost_tab.delete;
l_tmp_burdened_cost_tab.delete;
l_tmp_quantity_tab.delete;
l_tmp_txn_raw_cost_tab.delete;
l_tmp_pc_raw_cost_tab.delete;
l_tmp_pfc_raw_cost_tab.delete;
l_tmp_res_asg_id_tab.delete;
l_tmp_start_date_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_txn_revenue_tab.delete;
l_tmp_project_revenue_tab.delete;
l_tmp_revenue_tab.delete;
UPDATE pa_fp_rollup_tmp
SET txn_revenue = l_txn_rev_tab(j),
projfunc_revenue = l_txn_rev_tab(j),
project_revenue = l_pc_rev_tab(j),
projfunc_rev_rate_type = 'User',
project_rev_rate_type = 'User'
WHERE budget_line_id = l_budget_line_id_tab(j);
UPDATE pa_budget_lines
SET quantity = l_quantity_tab(j),
txn_revenue = l_txn_rev_tab(j),
txn_bill_rate_override = l_txn_bill_rate_override_tab(j),
revenue = l_txn_rev_tab(j),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = 1,
project_revenue = l_pc_rev_tab(j),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(j)
WHERE budget_line_id = l_budget_line_id_tab(j);
UPDATE pa_budget_lines
SET quantity = NVL(init_quantity,0) + l_quantity_tab(j),
txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(j),
txn_bill_rate_override = l_txn_bill_rate_override_tab(j),
revenue = NVL(init_revenue,0) + l_txn_rev_tab(j),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = 1,
project_revenue = NVL(project_init_revenue,0) + l_pc_rev_tab(j),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(j)
WHERE budget_line_id = l_budget_line_id_tab(j);
UPDATE pa_budget_lines
SET quantity = l_txn_rev_tab(jj),
txn_revenue = l_txn_rev_tab(jj),
txn_bill_rate_override = l_txn_bill_rate_override_tab(jj),
revenue = l_txn_rev_tab(jj),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = 1,
project_revenue = l_pc_rev_tab(jj),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(jj)
WHERE budget_line_id = l_budget_line_id_tab(jj);
UPDATE pa_budget_lines
SET quantity = NVL(init_quantity,0) + l_txn_rev_tab(jj),
txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(jj),
txn_bill_rate_override = l_txn_bill_rate_override_tab(jj),
revenue = NVL(init_revenue,0) + l_txn_rev_tab(jj),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = 1,
project_revenue = NVL(project_init_revenue,0) + l_pc_rev_tab(jj),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(jj)
WHERE budget_line_id = l_budget_line_id_tab(jj);
( p_msg => 'No. of rows updated in bdgt_lines '
|| 'table when multi_curr_flag is N: '
|| sql%rowcount,
p_module_name => l_module_name,
p_log_level => 5 );
/* dbms_output.put_line('No. of rows updated in bdgt_lines table when multi_curr_flag is N: '
||sql%rowcount);*/
UPDATE pa_fp_rollup_tmp
SET txn_revenue = l_txn_rev_tab(k),
projfunc_revenue = l_pfc_rev_tab(k),
project_revenue = l_pc_rev_tab(k),
projfunc_rev_rate_type = 'User',
project_rev_rate_type = 'User'
WHERE budget_line_id = l_budget_line_id_tab(k);
UPDATE pa_budget_lines
SET quantity = l_quantity_tab(k),
txn_revenue = l_txn_rev_tab(k),
txn_bill_rate_override = l_txn_bill_rate_override_tab(k),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(k),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(k)
WHERE budget_line_id = l_budget_line_id_tab(k);
UPDATE pa_budget_lines
SET quantity = NVL(init_quantity,0) + l_quantity_tab(k),
txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(k),
txn_bill_rate_override = l_txn_bill_rate_override_tab(k),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(k),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(k)
WHERE budget_line_id = l_budget_line_id_tab(k);
UPDATE pa_budget_lines
SET quantity = l_txn_rev_tab(kk),
txn_revenue = l_txn_rev_tab(kk),
txn_bill_rate_override = l_txn_bill_rate_override_tab(kk),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(kk),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(kk)
WHERE budget_line_id = l_budget_line_id_tab(kk);
UPDATE pa_budget_lines
SET quantity = NVL(init_quantity,0) + l_txn_rev_tab(kk),
txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(kk),
txn_bill_rate_override = l_txn_bill_rate_override_tab(kk),
project_rev_rate_type = 'User',
project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(kk),
projfunc_rev_rate_type = 'User',
projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(kk)
WHERE budget_line_id = l_budget_line_id_tab(kk);
( p_msg => 'No. of rows updated in bdgt_lines table '
|| 'when multi_curr_flag is Y: '||sql%rowcount,
p_module_name => l_module_name,
p_log_level => 5 );
END IF; -- insert/update temp table data to budget lines
UPDATE pa_budget_lines
SET quantity = null
WHERE budget_version_id = p_budget_version_id;
* and Inserts/Updates it into PA_BUDGET_LINES. This includes txn/pc/pfc
* amounts, rate overrides, pc/pfc exchange rates, cost/revenue rate types,
* and rejection codes.
*
* This API should always be called by GEN_COST_BASED_REVENUE before
* returning with return status of Success.
**/
PROCEDURE PUSH_RES_SCH_DATA_TO_BL
(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 PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE,
P_PLAN_CLASS_CODE IN PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_REV_GEN_PUB.PUSH_RES_SCH_DATA_TO_BL';
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
START_DATE,
END_DATE,
PERIOD_NAME,
SUM(QUANTITY),
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
SUM(PROJECT_RAW_COST),
SUM(PROJECT_BURDENED_COST),
SUM(PROJECT_REVENUE),
SUM(PROJFUNC_RAW_COST),
SUM(PROJFUNC_BURDENED_COST),
SUM(PROJFUNC_REVENUE),
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE
FROM pa_fp_rollup_tmp
GROUP BY resource_assignment_id,
txn_currency_code,
start_date,
end_date,
period_name,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE;
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(START_DATE),
MAX(END_DATE),
PERIOD_NAME,
SUM(QUANTITY),
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
SUM(PROJECT_RAW_COST),
SUM(PROJECT_BURDENED_COST),
SUM(PROJECT_REVENUE),
SUM(PROJFUNC_RAW_COST),
SUM(PROJFUNC_BURDENED_COST),
SUM(PROJFUNC_REVENUE),
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE
FROM pa_fp_rollup_tmp
GROUP BY resource_assignment_id,
txn_currency_code,
period_name,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE;
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(START_DATE),
MAX(END_DATE),
PERIOD_NAME,
SUM(QUANTITY),
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
SUM(PROJECT_RAW_COST),
SUM(PROJECT_BURDENED_COST),
SUM(PROJECT_REVENUE),
SUM(PROJFUNC_RAW_COST),
SUM(PROJFUNC_BURDENED_COST),
SUM(PROJFUNC_REVENUE),
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE
FROM pa_fp_rollup_tmp tmp
GROUP BY resource_assignment_id,
txn_currency_code,
period_name,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE
HAVING ( SELECT count(*)
FROM pa_budget_lines bl
WHERE tmp.resource_assignment_id = bl.resource_assignment_id
AND tmp.txn_currency_code = bl.txn_currency_code ) = 0;
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(START_DATE),
MAX(END_DATE),
PERIOD_NAME,
SUM(QUANTITY),
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
SUM(PROJECT_RAW_COST),
SUM(PROJECT_BURDENED_COST),
SUM(PROJECT_REVENUE),
SUM(PROJFUNC_RAW_COST),
SUM(PROJFUNC_BURDENED_COST),
SUM(PROJFUNC_REVENUE),
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE
FROM pa_fp_rollup_tmp tmp
GROUP BY resource_assignment_id,
txn_currency_code,
period_name,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE
HAVING ( SELECT count(*)
FROM pa_budget_lines bl
WHERE tmp.resource_assignment_id = bl.resource_assignment_id
AND tmp.txn_currency_code = bl.txn_currency_code ) > 0;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
UPDATE pa_fp_rollup_tmp tmp
SET projfunc_cost_rate_type = 'User'
WHERE projfunc_cost_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.start_date = tmp.start_date
AND tmp2.projfunc_cost_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET project_cost_rate_type = 'User'
WHERE project_cost_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.start_date = tmp.start_date
AND tmp2.project_cost_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET projfunc_rev_rate_type = 'User'
WHERE projfunc_rev_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.start_date = tmp.start_date
AND tmp2.projfunc_rev_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET project_rev_rate_type = 'User'
WHERE project_rev_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.start_date = tmp.start_date
AND tmp2.project_rev_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET projfunc_cost_rate_type = 'User'
WHERE projfunc_cost_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.projfunc_cost_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET project_cost_rate_type = 'User'
WHERE project_cost_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.project_cost_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET projfunc_rev_rate_type = 'User'
WHERE projfunc_rev_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.projfunc_rev_rate_type = 'User' );
UPDATE pa_fp_rollup_tmp tmp
SET project_rev_rate_type = 'User'
WHERE project_rev_rate_type <> 'User'
AND EXISTS ( SELECT null
FROM pa_fp_rollup_tmp tmp2
WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
AND tmp2.txn_currency_code = tmp.txn_currency_code
AND tmp2.project_rev_rate_type = 'User' );
INSERT INTO PA_BUDGET_LINES(
RESOURCE_ASSIGNMENT_ID,
START_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_CURRENCY_CODE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
RAW_COST,
BURDENED_COST,
REVENUE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE,
PROJECT_COST_EXCHANGE_RATE,
PROJFUNC_COST_EXCHANGE_RATE,
PROJECT_REV_EXCHANGE_RATE,
PROJFUNC_REV_EXCHANGE_RATE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE,
PROJECT_REV_RATE_TYPE,
PROJFUNC_REV_RATE_TYPE )
VALUES(
l_bl_RES_ASSIGNMENT_ID_tab(bl_index),
l_bl_START_DATE_tab(bl_index),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_bl_END_DATE_tab(bl_index),
l_bl_PERIOD_NAME_tab(bl_index),
l_bl_QUANTITY_tab(bl_index),
l_bl_TXN_CURRENCY_CODE_tab(bl_index),
PA_BUDGET_LINES_S.nextval,
P_BUDGET_VERSION_ID,
P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
l_txn_rcost_rate_override_tab(bl_index),
l_txn_bill_rate_override_tab(bl_index),
l_txn_bcost_rate_override_tab(bl_index),
l_bl_TXN_RAW_COST_tab(bl_index),
l_bl_TXN_BURDENED_COST_tab(bl_index),
l_bl_TXN_REVENUE_tab(bl_index),
l_bl_PC_RAW_COST_tab(bl_index),
l_bl_PC_BURDENED_COST_tab(bl_index),
l_bl_PC_REVENUE_tab(bl_index),
l_bl_PFC_RAW_COST_tab(bl_index),
l_bl_PFC_BURDENED_COST_tab(bl_index),
l_bl_PFC_REVENUE_tab(bl_index),
l_bl_COST_REJ_CODE_tab(bl_index),
l_bl_BURDEN_REJ_CODE_tab(bl_index),
l_bl_PC_CUR_REJ_CODE_tab(bl_index),
l_bl_PFC_CUR_REJ_CODE_tab(bl_index),
l_cost_pc_exchg_rate_tab(bl_index),
l_cost_pfc_exchg_rate_tab(bl_index),
l_rev_pc_exchg_rate_tab(bl_index),
l_rev_pfc_exchg_rate_tab(bl_index),
l_bl_PC_COST_RT_TYPE_tab(bl_index),
l_bl_PFC_COST_RT_TYPE_tab(bl_index),
l_bl_PC_REV_RT_TYPE_tab(bl_index),
l_bl_PFC_REV_RT_TYPE_tab(bl_index) );
UPDATE PA_BUDGET_LINES
SET LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
START_DATE = LEAST(START_DATE, l_upd_bl_START_DATE_tab(bl_index)),
END_DATE = GREATEST(END_DATE, l_upd_bl_END_DATE_tab(bl_index)),
QUANTITY =
DECODE(INIT_QUANTITY, null, l_upd_bl_QUANTITY_tab(bl_index),
INIT_QUANTITY + NVL(l_upd_bl_QUANTITY_tab(bl_index),0)),
TXN_RAW_COST =
DECODE(TXN_INIT_RAW_COST, null, l_upd_bl_TXN_RAW_COST_tab(bl_index),
TXN_INIT_RAW_COST + NVL(l_upd_bl_TXN_RAW_COST_tab(bl_index),0)),
TXN_BURDENED_COST =
DECODE(TXN_INIT_BURDENED_COST, null, l_upd_bl_TXN_BURDENED_COST_tab(bl_index),
TXN_INIT_BURDENED_COST + NVL(l_upd_bl_TXN_BURDENED_COST_tab(bl_index),0)),
TXN_REVENUE =
DECODE(TXN_INIT_REVENUE, null, l_upd_bl_TXN_REVENUE_tab(bl_index),
TXN_INIT_REVENUE + NVL(l_upd_bl_TXN_REVENUE_tab(bl_index),0)),
PROJECT_RAW_COST =
DECODE(PROJECT_INIT_RAW_COST, null, l_upd_bl_PC_RAW_COST_tab(bl_index),
PROJECT_INIT_RAW_COST + NVL(l_upd_bl_TXN_RAW_COST_tab(bl_index),0)),
PROJECT_BURDENED_COST =
DECODE(PROJECT_INIT_BURDENED_COST, null, l_upd_bl_PC_BURDENED_COST_tab(bl_index),
PROJECT_INIT_BURDENED_COST + NVL(l_upd_bl_PC_BURDENED_COST_tab(bl_index),0)),
PROJECT_REVENUE =
DECODE(PROJECT_INIT_REVENUE, null, l_upd_bl_PC_REVENUE_tab(bl_index),
PROJECT_INIT_REVENUE + NVL(l_upd_bl_PC_REVENUE_tab(bl_index),0)),
RAW_COST =
DECODE(INIT_RAW_COST, null, l_upd_bl_PFC_RAW_COST_tab(bl_index),
INIT_RAW_COST + NVL(l_upd_bl_PFC_RAW_COST_tab(bl_index),0)),
BURDENED_COST =
DECODE(INIT_BURDENED_COST, null, l_upd_bl_PFC_BURDENED_COST_tab(bl_index),
INIT_BURDENED_COST + NVL(l_upd_bl_PFC_BURDENED_COST_tab(bl_index),0)),
REVENUE =
DECODE(INIT_REVENUE, null, l_upd_bl_PFC_REVENUE_tab(bl_index),
INIT_REVENUE + NVL(l_upd_bl_PFC_REVENUE_tab(bl_index),0)),
TXN_COST_RATE_OVERRIDE = l_upd_rcost_rate_override_tab(bl_index),
TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(bl_index),
BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(bl_index),
COST_REJECTION_CODE = l_upd_bl_COST_REJ_CODE_tab(bl_index),
BURDEN_REJECTION_CODE = l_upd_bl_BURDEN_REJ_CODE_tab(bl_index),
PC_CUR_CONV_REJECTION_CODE = l_upd_bl_PC_CUR_REJ_CODE_tab(bl_index),
PFC_CUR_CONV_REJECTION_CODE = l_upd_bl_PFC_CUR_REJ_CODE_tab(bl_index),
PROJECT_COST_EXCHANGE_RATE = l_upd_cost_pc_exchg_rate_tab(bl_index),
PROJFUNC_COST_EXCHANGE_RATE = l_upd_cost_pfc_exchg_rate_tab(bl_index),
PROJECT_REV_EXCHANGE_RATE = l_upd_rev_pc_exchg_rate_tab(bl_index),
PROJFUNC_REV_EXCHANGE_RATE = l_upd_rev_pfc_exchg_rate_tab(bl_index),
PROJECT_COST_RATE_TYPE = l_upd_bl_PC_COST_RT_TYPE_tab(bl_index),
PROJFUNC_COST_RATE_TYPE = l_upd_bl_PFC_COST_RT_TYPE_tab(bl_index),
PROJECT_REV_RATE_TYPE = l_upd_bl_PC_REV_RT_TYPE_tab(bl_index),
PROJFUNC_REV_RATE_TYPE = l_upd_bl_PFC_REV_RT_TYPE_tab(bl_index)
WHERE RESOURCE_ASSIGNMENT_ID = l_upd_bl_RES_ASSIGNMENT_ID_tab(bl_index)
AND TXN_CURRENCY_CODE = l_upd_bl_TXN_CURRENCY_CODE_tab(bl_index);