[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_RES_DEFAULTS
(P_PROJECT_ID IN pa_projects_all.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_CALLED_MODE IN VARCHAR2,
P_COMMIT_FLAG IN VARCHAR2,
P_INIT_MSG_FLAG IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
PA_DEBUG.init_err_stack('PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS');
pa_debug.set_curr_function( p_function => 'UPDATE_RES_DEFAULTS'
,p_debug_mode => p_pa_debug_mode);
SELECT resource_assignment_id,
resource_list_member_id
BULK COLLECT
INTO l_da_ra_id_tab,
l_da_resource_list_members_tab
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id;
/* 5. Bug 4895793 : Update resource attributes by resource_assignment_id
instead of by (budget_version_id, resource_list_member_id).*/
-- IPM: At the time of resource creation, the resource_rate_based_flag
-- should be set based on the default rate_based_flag for the resource.
-- Modified the Update statements below to set resource_rate_based_flag.
-- Note that this API is used exclusively by the Forecast Generation
-- process and is called by CREATE_RES_ASG in PAFPCAPB.pls.
IF l_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
FORALL i IN 1 .. l_da_ra_id_tab.count --l_da_resource_list_members_tab.count Bug 4895793
UPDATE PA_RESOURCE_ASSIGNMENTS RA
SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
RES_TYPE_CODE = l_da_res_type_code_tab(i),
PERSON_ID = l_da_person_id_tab(i),
JOB_ID = l_da_job_id_tab(i),
PERSON_TYPE_CODE = l_da_person_type_code_tab(i),
NAMED_ROLE = l_da_named_role_tab(i),
BOM_RESOURCE_ID = l_da_bom_resource_id_tab(i),
NON_LABOR_RESOURCE = l_da_non_labor_resource_tab(i),
INVENTORY_ITEM_ID = l_da_inventory_item_id_tab(i),
ITEM_CATEGORY_ID = l_da_item_category_id_tab(i),
PROJECT_ROLE_ID = l_da_project_role_id_tab(i),
ORGANIZATION_ID = l_da_organization_id_tab(i),
FC_RES_TYPE_CODE = l_da_fc_res_type_code_tab(i),
EXPENDITURE_TYPE = l_da_expenditure_type_tab(i),
EXPENDITURE_CATEGORY = l_da_expenditure_category_tab(i),
EVENT_TYPE = l_da_event_type_tab(i),
REVENUE_CATEGORY_CODE = l_da_revenue_category_code_tab(i),
SUPPLIER_ID = l_da_supplier_id_tab(i),
SPREAD_CURVE_ID = l_da_spread_curve_id_tab(i),
ETC_METHOD_CODE = l_da_etc_method_code_tab(i),
MFC_COST_TYPE_ID = l_da_mfc_cost_type_id_tab(i),
INCURRED_BY_RES_FLAG = l_da_incurred_by_res_flag_tab(i),
INCUR_BY_RES_CLASS_CODE = l_da_incur_by_res_cls_code_tab(i),
INCUR_BY_ROLE_ID = l_da_incur_by_role_id_tab(i),
UNIT_OF_MEASURE = l_da_unit_of_measure_tab(i),
RATE_BASED_FLAG = l_da_rate_based_flag_tab(i),
RESOURCE_RATE_BASED_FLAG = l_da_rate_based_flag_tab(i), -- Added for IPM ER
RATE_EXPENDITURE_TYPE = l_da_rate_expenditure_type_tab(i),
RATE_EXP_FUNC_CURR_CODE = l_da_rate_func_curr_code_tab(i),
--RATE_INCURRED_BY_ORGANZ_ID = l_da_rat_incured_by_org_id_tab(i),
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,
PROJECT_ASSIGNMENT_ID = -1,
RATE_EXPENDITURE_ORG_ID = l_da_org_id_tab(i)
WHERE resource_assignment_id = l_da_ra_id_tab(i);
UPDATE PA_RESOURCE_ASSIGNMENTS RA
SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
RES_TYPE_CODE = l_da_res_type_code_tab(i),
PERSON_ID = l_da_person_id_tab(i),
JOB_ID = l_da_job_id_tab(i),
PERSON_TYPE_CODE = l_da_person_type_code_tab(i),
NAMED_ROLE = l_da_named_role_tab(i),
BOM_RESOURCE_ID = l_da_bom_resource_id_tab(i),
NON_LABOR_RESOURCE = l_da_non_labor_resource_tab(i),
INVENTORY_ITEM_ID = l_da_inventory_item_id_tab(i),
ITEM_CATEGORY_ID = l_da_item_category_id_tab(i),
PROJECT_ROLE_ID = l_da_project_role_id_tab(i),
ORGANIZATION_ID = l_da_organization_id_tab(i),
FC_RES_TYPE_CODE = l_da_fc_res_type_code_tab(i),
EXPENDITURE_TYPE = l_da_expenditure_type_tab(i),
EXPENDITURE_CATEGORY = l_da_expenditure_category_tab(i),
EVENT_TYPE = l_da_event_type_tab(i),
REVENUE_CATEGORY_CODE = l_da_revenue_category_code_tab(i),
SUPPLIER_ID = l_da_supplier_id_tab(i),
SPREAD_CURVE_ID = l_da_spread_curve_id_tab(i),
ETC_METHOD_CODE = l_da_etc_method_code_tab(i),
MFC_COST_TYPE_ID = l_da_mfc_cost_type_id_tab(i),
INCURRED_BY_RES_FLAG = l_da_incurred_by_res_flag_tab(i),
INCUR_BY_RES_CLASS_CODE = l_da_incur_by_res_cls_code_tab(i),
INCUR_BY_ROLE_ID = l_da_incur_by_role_id_tab(i),
UNIT_OF_MEASURE = l_da_unit_of_measure_tab(i),
RATE_BASED_FLAG = l_da_rate_based_flag_tab(i),
RESOURCE_RATE_BASED_FLAG = l_da_rate_based_flag_tab(i), -- Added for IPM ER
RATE_EXPENDITURE_TYPE = l_da_rate_expenditure_type_tab(i),
RATE_EXP_FUNC_CURR_CODE = l_da_rate_func_curr_code_tab(i),
--RATE_INCURRED_BY_ORGANZ_ID = l_da_rat_incured_by_org_id_tab(i),
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,
PROJECT_ASSIGNMENT_ID = -1,
RATE_EXPENDITURE_ORG_ID = l_da_org_id_tab(i)
WHERE resource_assignment_id = l_da_ra_id_tab(i)
--budget_version_id = p_budget_version_id
--AND RESOURCE_LIST_MEMBER_ID = l_da_resource_list_members_tab(i)
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date >=
DECODE(l_fp_cols_rec.x_plan_class_code,
'FORECAST', l_etc_start_date,
bl.start_date)
AND rownum = 1 )));
,p_procedure_name => 'UPDATE_RES_DEFAULTS');
END UPDATE_RES_DEFAULTS;
SELECT /* pfc.ci_type_name as cd_type
,pfc.cd_number as cd_number
,pfc.summary as summary
,pfc.task_no as task_no
,pfc.project_status_name as project_status_name
,pal.meaning as project_system_status
,pfc.people_effort as people_effort
,pfc.equipment_effort as equipment_effort
,PA_FP_CONTROL_ITEMS_UTILS.get_cost
(CI_VERSION_TYPE,p_fp_cols_rec.x_budget_version_id,
CI_VERSION_ID,RAW_COST,BURDENED_COST) as cost
,PA_FP_CONTROL_ITEMS_UTILS.get_revenue_partial
(CI_VERSION_TYPE,p_fp_cols_rec.x_budget_version_id,
CI_VERSION_ID,REVENUE) as revenue
,'0' as margin
,'0' as margin_percent */
distinct pfc.ci_id as ci_id
/* ,pci.ci_type_class_code as ci_type_class_code */
BULK COLLECT
INTO l_ci_id_tbl
FROM pa_fp_eligible_ci_v pfc,
pa_lookups pal
-- ,pa_ci_types_vl pci
WHERE pfc.project_id = p_fp_cols_rec.x_project_id
AND pfc.fin_plan_type_id = p_fp_cols_rec.x_fin_plan_type_id
AND CI_VERSION_TYPE <> decode(p_fp_cols_rec.x_version_type,
'COST','REVENUE',
'REVENUE','COST',
'ALL','-99')
AND decode (CI_VERSION_TYPE,
'ALL',PT_CT_VERSION_TYPE,
CI_VERSION_TYPE) = PT_CT_VERSION_TYPE
AND (pfc.REV_PARTIALLY_IMPL_FLAG='Y'
OR (pfc.ci_version_type='ALL' AND
decode(p_fp_cols_rec.x_version_type,'ALL',2,1) >
(SELECT COUNT(*)
FROM pa_fp_merged_ctrl_items merge
WHERE merge.ci_plan_version_id = pfc.ci_version_id
AND merge.plan_version_id = p_fp_cols_rec.x_budget_version_id))
OR (pfc.ci_version_type <> 'ALL' AND
NOT EXISTS (SELECT 'X'
FROM pa_fp_merged_ctrl_items merge
WHERE merge.ci_plan_version_id = pfc.ci_version_id
AND merge.plan_version_id = p_fp_cols_rec.x_budget_version_id
AND merge.version_type = pfc.ci_version_type)))
AND pfc.project_system_status_code = pal.lookup_code
AND pal.lookup_type = 'CONTROL_ITEM_SYSTEM_STATUS';
/* Variables for insert/update of Unspent Amount budget lines */
l_insert_flag VARCHAR2(1);
l_update_flag VARCHAR2(1);
/* Variables for amounts of budget lines to be updated */
l_quantity PA_BUDGET_LINES.QUANTITY%TYPE;
l_last_updated_by PA_BUDGET_LINES.LAST_UPDATED_BY%TYPE := FND_GLOBAL.user_id;
l_last_update_login PA_BUDGET_LINES.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.login_id;
/* Tables for budget line Insert */
l_ins_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
/* Tables for budget line Update */
l_upd_bl_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
/* Insert the distinct target task_id and rlm_id values from tmp3 into tmp4.
* These are the only resources that have planned amounts in the baselined
* approved cost budget and are therefore the only resources that can possibly
* have unspent amounts. */
DELETE PA_RES_LIST_MAP_TMP4;
INSERT INTO PA_RES_LIST_MAP_TMP4
( txn_task_id,
txn_resource_list_member_id )
SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
DISTINCT
task_id,
res_list_member_id
FROM PA_FP_CALC_AMT_TMP3
WHERE plan_version_id = p_app_cost_bdgt_ver_id;
select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
ra.resource_assignment_id,
ra.task_id,
ra.resource_list_member_id,
ra.rate_based_flag,
ra.planning_start_date,
ra.planning_end_date
BULK COLLECT
INTO l_res_asg_id_tab,
l_task_id_tab,
l_res_list_mem_id_tab,
l_rate_based_flag_tab,
l_planning_start_date_tab,
l_planning_end_date_tab
FROM pa_resource_assignments ra,
pa_res_list_map_tmp4 tmp4
WHERE ra.budget_version_id = p_budget_version_id
AND ra.task_id = tmp4.txn_task_id
AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
ORDER BY ra.resource_assignment_id ASC;
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
ra.resource_assignment_id,
ra.task_id,
ra.resource_list_member_id,
ra.rate_based_flag,
ra.planning_start_date,
ra.planning_end_date
BULK COLLECT
INTO l_res_asg_id_tab,
l_task_id_tab,
l_res_list_mem_id_tab,
l_rate_based_flag_tab,
l_planning_start_date_tab,
l_planning_end_date_tab
FROM pa_resource_assignments ra,
pa_res_list_map_tmp4 tmp4
WHERE ra.budget_version_id = p_budget_version_id
AND ra.task_id = tmp4.txn_task_id
AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date >= l_etc_start_date
AND rownum = 1 )))
ORDER BY ra.resource_assignment_id ASC;
* We delete tmp4 and insert new lines instead of updating the existing ones
* to simplify the manually added plan lines logic. */
DELETE PA_RES_LIST_MAP_TMP4;
INSERT INTO PA_RES_LIST_MAP_TMP4
( txn_task_id,
txn_resource_list_member_id,
txn_resource_assignment_id )
VALUES
( l_task_id_tab(i),
l_res_list_mem_id_tab(i),
l_res_asg_id_tab(i) );
select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1) INDEX(bl,PA_FP_CALC_AMT_TMP3_N1)*/
tmp4.txn_resource_assignment_id,
bl.txn_currency_code,
nvl(sum(nvl(bl.quantity,0)),0),
nvl(sum(nvl(bl.pc_raw_cost,0)),0),
nvl(sum(nvl(bl.txn_raw_cost,0)),0),
nvl(sum(nvl(bl.pc_burdened_cost,0)),0),
nvl(sum(nvl(bl.txn_burdened_cost,0)),0)
BULK COLLECT
INTO l_plan_ra_id_tab,
l_plan_txn_cur_code_tab,
l_plan_qty_tab,
l_plan_pc_raw_cost_tab,
l_plan_txn_raw_cost_tab,
l_plan_pc_burd_cost_tab,
l_plan_txn_burd_cost_tab
FROM pa_fp_calc_amt_tmp3 bl,
pa_res_list_map_tmp4 tmp4
WHERE bl.plan_version_id = p_app_cost_bdgt_ver_id
AND bl.task_id = tmp4.txn_task_id
AND bl.res_list_member_id = tmp4.txn_resource_list_member_id
GROUP BY tmp4.txn_resource_assignment_id,
bl.txn_currency_code
ORDER BY tmp4.txn_resource_assignment_id ASC;
SELECT /*+ LEADING(tmp4) */
tmp4.txn_resource_assignment_id,
bl.txn_currency_code,
nvl(sum(nvl(bl.init_quantity,0)),0),
nvl(sum(nvl(bl.project_init_raw_cost,0)),0),
nvl(sum(nvl(bl.txn_init_raw_cost,0)),0),
nvl(sum(nvl(bl.project_init_burdened_cost,0)),0),
nvl(sum(nvl(bl.txn_init_burdened_cost,0)),0)
BULK COLLECT
INTO l_init_ra_id_tab,
l_init_txn_cur_code_tab,
l_init_qty_tab,
l_init_pc_raw_cost_tab,
l_init_txn_raw_cost_tab,
l_init_pc_burd_cost_tab,
l_init_txn_burd_cost_tab
FROM pa_budget_lines bl,
pa_res_list_map_tmp4 tmp4
WHERE bl.resource_assignment_id = tmp4.txn_resource_assignment_id
AND bl.start_date < l_etc_start_date
GROUP BY tmp4.txn_resource_assignment_id,
bl.txn_currency_code
ORDER BY tmp4.txn_resource_assignment_id ASC;
SELECT pap.start_date,
pap.end_date
INTO l_start_date,
l_end_date
FROM pa_periods_all pap
WHERE pap.period_name = p_unspent_amt_period
AND pap.org_id = p_fp_cols_rec.x_org_id;
SELECT glp.start_date,
glp.end_date
INTO l_start_date,
l_end_date
FROM gl_period_statuses glp
WHERE glp.period_name = p_unspent_amt_period
AND glp.application_id = pa_period_process_pkg.application_id
AND glp.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND glp.adjustment_period_flag = 'N';
l_plan_currency_tab.delete;
l_init_currency_tab.delete;
/* Check if we should insert a new budget line or update an existing one
* with the unspent amounts. Store data in corresponding pl/sql tables. */
l_update_flag := 'Y';
l_insert_flag := 'N';
SELECT budget_line_id,
quantity,
txn_raw_cost,
txn_burdened_cost
INTO l_upd_bl_id,
l_quantity,
l_txn_raw_cost,
l_txn_burdened_cost
FROM pa_budget_lines
WHERE resource_assignment_id = l_curr_ra_id
AND txn_currency_code = l_unspent_amt_currency
AND start_date = DECODE(l_time_phase, 'N', start_date, l_start_date);
l_insert_flag := 'Y';
l_update_flag := 'N';
IF l_insert_flag = 'Y' THEN
l_index := l_ins_ra_id_tab.count+1;
IF l_update_flag = 'Y' THEN
l_index := l_upd_bl_id_tab.count+1;
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE)
VALUES (
pa_budget_lines_s.nextval,
p_budget_version_id,
l_ins_ra_id_tab(i),
NVL(l_start_date,l_ins_start_date_tab(i)),
l_ins_txn_curr_code_tab(i),
NVL(l_end_date,l_ins_end_date_tab(i)),
l_period_name,
l_ins_quantity_tab(i),
l_ins_raw_cost_tab(i),
l_ins_burd_cost_tab(i),
l_ins_raw_cost_rate_tab(i),
l_ins_burd_cost_rate_tab(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_pc_currency_code,
l_pfc_currency_code );
DELETE PA_RES_LIST_MAP_TMP4;
INSERT INTO PA_RES_LIST_MAP_TMP4
( txn_resource_assignment_id )
VALUES ( l_ins_ra_id_tab(i) );
SELECT spread_curve_id
INTO l_fixed_date_curve_id
FROM pa_spread_curves_b
WHERE spread_curve_code = lc_fixed_date_code;
SELECT /*+ LEADING(tmp4) */
ra.resource_assignment_id
BULK COLLECT
INTO l_fixed_date_ra_id_tab
FROM pa_resource_assignments ra,
pa_res_list_map_tmp4 tmp4
WHERE ra.resource_assignment_id = tmp4.txn_resource_assignment_id
AND ra.spread_curve_id = l_fixed_date_curve_id
AND NOT ( ra.sp_fixed_date BETWEEN l_start_date AND l_end_date );
UPDATE pa_resource_assignments
SET spread_curve_id = NULL,
sp_fixed_date = NULL,
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
record_version_number = NVL(record_version_number,0)+1
WHERE resource_assignment_id = l_fixed_date_ra_id_tab(i);
END IF; -- budget line insertion
UPDATE PA_BUDGET_LINES
SET LAST_UPDATE_DATE = l_sysdate
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
, QUANTITY = nvl(quantity,0) + nvl(l_upd_quantity_tab(i),0)
, TXN_RAW_COST = nvl(txn_raw_cost,0) + nvl(l_upd_raw_cost_tab(i),0)
, TXN_BURDENED_COST = nvl(txn_burdened_cost,0) + nvl(l_upd_burd_cost_tab(i),0)
, TXN_COST_RATE_OVERRIDE = (nvl(txn_raw_cost,0) + nvl(l_upd_raw_cost_tab(i),0)) /
(nvl(quantity,0) + nvl(l_upd_quantity_tab(i),0))
, BURDEN_COST_RATE_OVERRIDE = (nvl(txn_burdened_cost,0) + nvl(l_upd_burd_cost_tab(i),0)) /
(nvl(quantity,0) + nvl(l_upd_quantity_tab(i),0))
WHERE BUDGET_LINE_ID = l_upd_bl_id_tab(i);
SELECT NVL(P_STRUCTURE_VERSION_ID,project_structure_version_id),
NVL(wp_version_flag,'N')
INTO l_structure_version_id,
l_wp_version_flag
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT ra.task_id,
pa_proj_elements_utils.get_task_version_id(
l_structure_version_id,ra.task_id)
BULK COLLECT
INTO l_task_id_tab,
l_wbs_element_ver_id_tab
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND nvl(ra.task_id,0) > 0;
UPDATE pa_resource_assignments
SET wbs_element_version_id = l_wbs_element_ver_id_tab(i)
WHERE budget_version_id = p_budget_version_id
AND task_id = l_task_id_tab(i);
SELECT period_name, start_date, end_date
FROM pa_periods_all
WHERE org_id = c_org_id and -- R12 MOAC 4447573: nvl(org_id,-99) = nvl(c_org_id,-99)
c_amt_thru between start_date and end_date;
SELECT period_name, start_date , end_date
FROM gl_period_statuses
WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id and
set_of_books_id = c_set_of_books and
adjustment_period_flag = 'N' and
c_amt_thru between start_date and end_date;
select nvl(p.org_id,-99),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', po.cost_time_phased_code,
'REVENUE_ONLY', po.revenue_time_phased_code,
po.all_time_phased_code),
pia.set_of_books_id,
ra.budget_version_id
into l_org_id,
l_source_ver_period_type,
l_source_set_of_books_id,
l_source_bv_id
from pa_resource_assignments ra,
pa_projects_all p,
pa_proj_fp_options po,
pa_implementations_all pia
where ra.resource_assignment_id = p_src_res_asg_id_tab(1) and
ra.project_id = p.project_id and
ra.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
p.org_id = pia.org_id;
DELETE pa_fp_gen_rate_tmp;
INSERT INTO pa_fp_gen_rate_tmp
( target_res_asg_id )
VALUES ( p_src_res_asg_id_tab(i) );
SELECT /*+ LEADING(tmp) */
nvl(sum(sbl.quantity),0),
nvl(sum(decode(p_currency_code_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),0),
nvl(sum(decode(p_currency_code_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),0),
nvl(sum(decode(p_currency_code_flag,
'Y', sbl.txn_revenue,
'N', sbl.project_revenue,
'A', sbl.revenue)),0)
INTO l_quantity,
l_txn_raw_cost,
l_txn_burdened_cost,
l_txn_revenue
FROM pa_fp_gen_rate_tmp tmp,
pa_budget_lines sbl
WHERE tmp.target_res_asg_id = sbl.resource_assignment_id
and sbl.budget_version_id = l_source_bv_id
and sbl.period_name = l_source_period_name
and sbl.txn_currency_code = decode(p_currency_code_flag,
'Y', p_currency_code,
'N', sbl.txn_currency_code,
'A', sbl.txn_currency_code)
and sbl.cost_rejection_code is null
and sbl.revenue_rejection_code is null
and sbl.burden_rejection_code is null
and sbl.other_rejection_code is null
and sbl.pc_cur_conv_rejection_code is null
and sbl.pfc_cur_conv_rejection_code is null;
* This procedure updates the fixed date spread curve fields in the
* pa_resource_assignments table for all resource assignments belonging
* to the given budget version as necessary.
* More specifically, for each resource assignment of interest, we null
* out the spread_curve_id and sp_fixed_date pa_resource_assignments
* table values if there exists a budget line for which the resource
* assignment's sp_fixed_date is not in the budget line's start and end
* date range.
* Additionally, for resources not having Fixed Date spread curves, we
* ensure that sp_fixed_date is Nulled out to address Bug 4229963.
*
* Note: This API currently updates the PA_RESOURCE_ASSIGNMENTS table
* multiple times. In the future, we revisit this as a Performance
* issue and modify the logic so that we only update once.
*
* Note that the p_fp_col_rec parameter is currently not used.
*/
PROCEDURE MAINTAIN_FIXED_DATE_SP
(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_GEN_PUB.MAINTAIN_FIXED_DATE_SP';
SELECT DISTINCT(bl.resource_assignment_id)
FROM pa_resource_assignments ra,
pa_spread_curves_b sp,
pa_budget_lines bl
WHERE ra.budget_version_id = p_budget_version_id
AND sp.spread_curve_id = ra.spread_curve_id
AND sp.spread_curve_code = lc_FixedDate
AND bl.resource_assignment_id = ra.resource_assignment_id
GROUP BY bl.resource_assignment_id,
bl.txn_currency_code
HAVING count(*) > 1;
SELECT DISTINCT(bl.resource_assignment_id)
FROM pa_resource_assignments ra,
pa_spread_curves_b sp,
pa_budget_lines bl
WHERE ra.budget_version_id = p_budget_version_id
AND sp.spread_curve_id = ra.spread_curve_id
AND sp.spread_curve_code = lc_FixedDate
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.sp_fixed_date NOT BETWEEN bl.start_date AND bl.end_date;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT spread_curve_id INTO l_fixed_date_id
FROM pa_spread_curves_b
WHERE spread_curve_code = lc_FixedDate;
UPDATE pa_resource_assignments
SET sp_fixed_date = NULL,
spread_curve_id = NULL,
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
record_version_number = NVL(record_version_number,0) + 1
WHERE resource_assignment_id = l_res_asg_id_tab(i);
UPDATE pa_resource_assignments
SET sp_fixed_date = NULL,
spread_curve_id = NULL,
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
record_version_number = NVL(record_version_number,0) + 1
WHERE resource_assignment_id = l_res_asg_id_tab(i);
UPDATE pa_resource_assignments
SET sp_fixed_date = NULL,
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
record_version_number = NVL(record_version_number,0) + 1
WHERE budget_version_id = p_budget_version_id
AND spread_curve_id <> l_fixed_date_id
AND sp_fixed_date IS NOT NULL;
l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE
:= FND_GLOBAL.user_id;
l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE
:= FND_GLOBAL.login_id;
SELECT /*+ INDEX(map,PA_FP_CALC_AMT_TMP1_N1)*/
TMP1.TXN_RESOURCE_ASSIGNMENT_ID,
RA.RESOURCE_CLASS_FLAG,
RA.RESOURCE_CLASS_CODE,
RA.RES_TYPE_CODE,
RA.PERSON_ID,
RA.JOB_ID,
RA.PERSON_TYPE_CODE,
RA.NAMED_ROLE,
RA.BOM_RESOURCE_ID,
RA.NON_LABOR_RESOURCE,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.PROJECT_ROLE_ID,
RA.ORGANIZATION_ID,
RA.FC_RES_TYPE_CODE,
RA.EXPENDITURE_TYPE,
RA.EXPENDITURE_CATEGORY,
RA.EVENT_TYPE,
RA.REVENUE_CATEGORY_CODE,
RA.SUPPLIER_ID,
RA.SPREAD_CURVE_ID,
RA.SP_FIXED_DATE,
RA.MFC_COST_TYPE_ID,
RA.INCURRED_BY_RES_FLAG,
RA.INCUR_BY_RES_CLASS_CODE,
RA.INCUR_BY_ROLE_ID,
RA.RATE_EXPENDITURE_TYPE,
RA.RATE_EXP_FUNC_CURR_CODE,
RA.RATE_EXPENDITURE_ORG_ID,
RA.RESOURCE_RATE_BASED_FLAG -- Added for IPM ER
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_resource_class_flag_tab,
l_resource_class_code_tab,
l_res_type_code_tab,
l_person_id_tab,
l_job_id_tab,
l_person_type_code_tab,
l_named_role_tab,
l_bom_resource_id_tab,
l_non_labor_resource_tab,
l_inventory_item_id_tab,
l_item_category_id_tab,
l_project_role_id_tab,
l_organization_id_tab,
l_fc_res_type_code_tab,
l_expenditure_type_tab,
l_expenditure_category_tab,
l_event_type_tab,
l_revenue_category_code_tab,
l_supplier_id_tab,
l_spread_curve_id_tab,
l_sp_fixed_date_tab,
l_mfc_cost_type_id_tab,
l_incurred_by_res_flag_tab,
l_incur_by_res_cls_code_tab,
l_incur_by_role_id_tab,
l_rate_expenditure_type_tab,
l_rate_func_curr_code_tab,
l_org_id_tab,
l_res_rate_based_flag_tab -- Added for IPM ER
FROM PA_RESOURCE_ASSIGNMENTS RA,
PA_RES_LIST_MAP_TMP1 tmp1,
PA_FP_CALC_AMT_TMP1 map
WHERE RA.budget_version_id = l_src_version_id
AND RA.resource_assignment_id = map.resource_assignment_id
AND map.target_res_asg_id = tmp1.txn_resource_assignment_id
AND map.transaction_source_code = l_gen_etc_src_code;
UPDATE PA_RESOURCE_ASSIGNMENTS
SET RESOURCE_CLASS_FLAG = l_resource_class_flag_tab(j),
RESOURCE_CLASS_CODE = l_resource_class_code_tab(j),
RES_TYPE_CODE = l_res_type_code_tab(j),
PERSON_ID = l_person_id_tab(j),
JOB_ID = l_job_id_tab(j),
PERSON_TYPE_CODE = l_person_type_code_tab(j),
NAMED_ROLE = l_named_role_tab(j),
BOM_RESOURCE_ID = l_bom_resource_id_tab(j),
NON_LABOR_RESOURCE = l_non_labor_resource_tab(j),
INVENTORY_ITEM_ID = l_inventory_item_id_tab(j),
ITEM_CATEGORY_ID = l_item_category_id_tab(j),
PROJECT_ROLE_ID = l_project_role_id_tab(j),
ORGANIZATION_ID = l_organization_id_tab(j),
FC_RES_TYPE_CODE = l_fc_res_type_code_tab(j),
EXPENDITURE_TYPE = l_expenditure_type_tab(j),
EXPENDITURE_CATEGORY = l_expenditure_category_tab(j),
EVENT_TYPE = l_event_type_tab(j),
REVENUE_CATEGORY_CODE = l_revenue_category_code_tab(j),
SUPPLIER_ID = l_supplier_id_tab(j),
SPREAD_CURVE_ID = l_spread_curve_id_tab(j),
SP_FIXED_DATE = l_sp_fixed_date_tab(j),
MFC_COST_TYPE_ID = l_mfc_cost_type_id_tab(j),
INCURRED_BY_RES_FLAG = l_incurred_by_res_flag_tab(j),
INCUR_BY_RES_CLASS_CODE = l_incur_by_res_cls_code_tab(j),
INCUR_BY_ROLE_ID = l_incur_by_role_id_tab(j),
RATE_EXPENDITURE_TYPE = l_rate_expenditure_type_tab(j),
RATE_EXP_FUNC_CURR_CODE = l_rate_func_curr_code_tab(j),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
RATE_EXPENDITURE_ORG_ID = l_org_id_tab(j),
RESOURCE_RATE_BASED_FLAG = l_res_rate_based_flag_tab(j) -- Added for IPM ER
WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id
AND resource_assignment_id = l_tgt_res_asg_id_tab(j);