[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
PX_GEN_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
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_WP_GEN_BUDGET_AMT_PUB.GENERATE_WP_BUDGET_AMT';
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(quantity),
/*
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.txn_revenue
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.project_revenue
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))), --sbl.revenue */
-- Bug 8937993. Need to pull use source rates for only the plannned quantity.
sum(decode(l_txn_currency_flag,
'Y', (((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))
+ NVL(sbl.txn_init_raw_cost,0)), --sbl.txn_raw_cost
'N', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))
+ NVL(sbl.txn_init_raw_cost,0)) * NVL(sbl.project_cost_exchange_rate,1)) , --sbl.project_raw_cost
'A', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))
+ NVL(sbl.txn_init_raw_cost,0)) * NVL(sbl.projfunc_cost_exchange_rate,1))
)), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', (((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))
+ NVL(sbl.txn_init_burdened_cost,0)), --sbl.txn_burdened_cost
'N', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))
+ NVL(sbl.txn_init_burdened_cost,0)) * NVL(sbl.project_cost_exchange_rate,1)), --sbl.project_burdened_cost
'A', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))
+ NVL(sbl.txn_init_burdened_cost,0)) * NVL(sbl.projfunc_cost_exchange_rate,1))
)), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', (((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))
+ NVL(sbl.txn_init_revenue,0)), --sbl.txn_revenue
'N', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))
+ NVL(sbl.txn_init_revenue,0)) * NVL(sbl.project_cost_exchange_rate,1)) , --sbl.project_revenue
'A', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))
+ NVL(sbl.txn_init_revenue,0)) * NVL(sbl.projfunc_cost_exchange_rate,1))
)),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = l_source_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_budget_version_id
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
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
and ra.project_id = P_PROJECT_ID /* Added for Bug 4543795 */
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'); /* Added for ER 4376722 */
SELECT sum(nvl(init_quantity,0))
FROM pa_budget_lines
WHERE resource_assignment_id = c_res_asgn_id
AND txn_currency_code = c_txn_currency_code;
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.txn_revenue
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.project_revenue
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))), --sbl.revenue
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = l_source_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_budget_version_id
and sbl.start_date > decode( c_src_time_phased_code,
'N', sbl.start_date-1, P_ACTUALS_THRU_DATE )
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
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
and ra.project_id = P_PROJECT_ID /* Added for Bug 4543795 */
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'); /* Added for ER 4376722 */
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity-NVL(sbl.init_quantity,0)),
sum(decode(l_txn_currency_flag,
'Y', (sbl.quantity-NVL(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', (sbl.quantity-NVL(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', (sbl.quantity-NVL(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.txn_revenue
'N', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.project_revenue
'A', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))), --sbl.revenue
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = l_source_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_budget_version_id
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
and NVL(sbl.quantity,0) <> NVL(sbl.init_quantity,0)
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
and ra.project_id = P_PROJECT_ID /* Added for Bug 4543795 */
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'); /* Added for ER 4376722 */
l_res_asg_uom_update_tab pa_plsql_datatypes.IdTabTyp;
select trunc(sysdate) into l_sysdate_trunc from dual;
corresponding to the work plan structure version id selected
as the source for the budget generation when the budget
generation source is Work plan. */
SELECT project_structure_version_id
INTO l_wp_id
FROM pa_budget_versions
WHERE budget_version_id = l_source_id;
* Now, we need to update back to pa_proj_fp_options*/
IF l_version_type = 'COST' THEN
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_COST_WP_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_ALL_WP_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_REV_WP_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_BUDGET_VERSION_ID;
UPDATE PA_BUDGET_VERSIONS
SET project_structure_version_id = l_wp_id
WHERE budget_version_id = P_BUDGET_VERSION_ID;
* Now, we need to update back to pa_proj_fp_options*/
IF l_version_type = 'COST' THEN
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_COST_PLAN_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_ALL_PLAN_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_REV_PLAN_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_BUDGET_VERSION_ID;
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;
resource mapping logic. Otherwise, the tmp table data will get deleted
and no amounts will be carried over to the target version from the
generation source. */
-- Bug 4114589: Moved from beginning of GENERATE_FCST_AMT_WRP to several
-- places - this being one of them. The Copy Actuals API call is placed
-- after calls to CREATE_RES_ASG and UPDATE_RES_ASG so that planning dates
-- from the source are honored when possible, since resources created by
-- the Copy Actuals API use task/project-level default dates.
IF l_calling_context = lc_ForecastGeneration THEN
IF p_pa_debug_mode = 'Y' THEN
PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
P_CALLED_MODE => P_CALLED_MODE,
P_MSG => 'Before calling pa_fp_copy_actuals_pub.copy_actuals',
P_MODULE_NAME => l_module_name);
DELETE FROM PA_RES_LIST_MAP_TMP1;
DELETE from pa_res_list_map_tmp4;
SELECT PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
EXPENDITURE_TYPE,
EVENT_TYPE,
NON_LABOR_RESOURCE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY_CODE,
NVL(INCUR_BY_ROLE_ID, PROJECT_ROLE_ID),
NVL(INCUR_BY_RES_CLASS_CODE,RESOURCE_CLASS_CODE),
MFC_COST_TYPE_ID,
RESOURCE_CLASS_FLAG,
FC_RES_TYPE_CODE,
INVENTORY_ITEM_ID,
ITEM_CATEGORY_ID,
PERSON_TYPE_CODE,
BOM_RESOURCE_ID,
NAMED_ROLE,
INCURRED_BY_RES_FLAG,
resource_assignment_id, --TXN_SOURCE_ID,
'RES_ASSIGNMENT', --TXN_SOURCE_TYPE_CODE,
TASK_ID,
NULL, --TXN_WBS_ELEMENT_VERSION_ID,
RBS_ELEMENT_ID,
nvl(PLANNING_START_DATE,l_sysdate_trunc),
nvl(PLANNING_END_DATE,l_sysdate_trunc),
RATE_BASED_FLAG,
SUPPLIER_ID,
CBS_ELEMENT_ID--bug#16827157
BULK COLLECT
INTO l_PERSON_ID_tab,
l_JOB_ID_tab,
l_ORGANIZATION_ID_tab,
l_EXPENDITURE_TYPE_tab,
l_EVENT_TYPE_tab,
l_NON_LABOR_RESOURCE_tab,
l_EXPENDITURE_CATEGORY_tab,
l_REVENUE_CATEGORY_CODE_tab,
l_PROJECT_ROLE_ID_tab,
l_RESOURCE_CLASS_CODE_tab,
l_MFC_COST_TYPE_ID_tab,
l_RESOURCE_CLASS_FLAG_tab,
l_FC_RES_TYPE_CODE_tab,
l_INVENTORY_ITEM_ID_tab,
l_ITEM_CATEGORY_ID_tab,
l_PERSON_TYPE_CODE_tab,
l_BOM_RESOURCE_ID_tab,
l_NAMED_ROLE_tab,
l_INCURRED_BY_RES_FLAG_tab,
l_TXN_SOURCE_ID_tab,
l_TXN_SOURCE_TYPE_CODE_tab,
l_TXN_TASK_ID_tab,
l_TXN_WBS_ELEMENT_VER_ID_tab,
l_TXN_RBS_ELEMENT_ID_tab,
l_TXN_PLAN_START_DATE_tab,
l_TXN_PLAN_END_DATE_tab,
l_RATE_BASED_FLAG_tab,
l_VENDOR_ID_tab,
l_CBS_ELEMENT_ID_tab --bug#16827157
FROM PA_RESOURCE_ASSIGNMENTS ra
WHERE ra.budget_version_id = l_source_id;
SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
count(*) INTO l_count1
FROM PA_RES_LIST_MAP_TMP4
WHERE RESOURCE_LIST_MEMBER_ID IS NULL AND rownum=1;
tmp_rlm_tab.delete;
select distinct resource_list_member_id,txn_task_id
bulk collect into tmp_rlm_tab, tmp_task_tab
from PA_RES_LIST_MAP_TMP4;
select 'Y' into tmp_flag from PA_RESource_list_members
where resource_list_member_id = tmp_rlm_tab(i);
DELETE from PA_RES_LIST_MAP_TMP4;
INSERT INTO PA_RES_LIST_MAP_TMP4( PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
VENDOR_ID,
EXPENDITURE_TYPE,
EVENT_TYPE,
NON_LABOR_RESOURCE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
-- NON_LABOR_RESOURCE_ORG_ID,
PROJECT_ROLE_ID,
RESOURCE_TYPE_CODE,
RESOURCE_CLASS_CODE,
MFC_COST_TYPE_ID,
RESOURCE_CLASS_FLAG,
FC_RES_TYPE_CODE,
--BOM_LABOR_RESOURCE_ID,
--BOM_EQUIP_RESOURCE_ID,
INVENTORY_ITEM_ID,
ITEM_CATEGORY_ID,
PERSON_TYPE_CODE,
BOM_RESOURCE_ID,
NAMED_ROLE,
INCURRED_BY_RES_FLAG,
TXN_TRACK_AS_LABOR_FLAG,
TXN_SOURCE_ID,
TXN_SOURCE_TYPE_CODE,
TXN_TASK_ID,
TXN_WBS_ELEMENT_VERSION_ID,
TXN_RBS_ELEMENT_ID,
TXN_PLANNING_START_DATE,
TXN_PLANNING_END_DATE,
TXN_SP_FIXED_DATE,
TXN_RESOURCE_LIST_MEMBER_ID,
TXN_RATE_BASED_FLAG,
RESOURCE_LIST_MEMBER_ID,
CBS_ELEMENT_ID--bug#16827157
)
SELECT PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
SUPPLIER_ID, --VENDOR_ID,
EXPENDITURE_TYPE,
EVENT_TYPE,
NON_LABOR_RESOURCE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY_CODE, --REVENUE_CATEGORY,
-- NON_LABOR_RESOURCE, --NON_LABOR_RESOURCE_ORG_ID,
NVL(INCUR_BY_ROLE_ID, PROJECT_ROLE_ID),
RES_TYPE_CODE, --RESOURCE_TYPE_CODE,
NVL(INCUR_BY_RES_CLASS_CODE,RESOURCE_CLASS_CODE),
MFC_COST_TYPE_ID,
RESOURCE_CLASS_FLAG,
FC_RES_TYPE_CODE,
--BOM_LABOR_RESOURCE_ID,
--BOM_EQUIP_RESOURCE_ID,
INVENTORY_ITEM_ID,
ITEM_CATEGORY_ID,
PERSON_TYPE_CODE,
BOM_RESOURCE_ID,
NAMED_ROLE,
INCURRED_BY_RES_FLAG,
TRACK_AS_LABOR_FLAG,
resource_assignment_id, --TXN_SOURCE_ID,
'RES_ASSIGNMENT', --TXN_SOURCE_TYPE_CODE,
TASK_ID,
NULL, --TXN_WBS_ELEMENT_VERSION_ID,
RBS_ELEMENT_ID,
nvl(PLANNING_START_DATE,l_sysdate_trunc),
nvl(PLANNING_END_DATE,l_sysdate_trunc),
SP_FIXED_DATE,
RESOURCE_LIST_MEMBER_ID,
RATE_BASED_FLAG,
RESOURCE_LIST_MEMBER_ID,
CBS_ELEMENT_ID--bug#16827157
FROM PA_RESOURCE_ASSIGNMENTS ra
WHERE ra.budget_version_id = l_source_id;
tmp_rlm_tab.delete;
tmp_task_tab.delete;
tmp_ra_tab.delete;
select resource_list_member_id, task_id, resource_assignment_id
bulk collect into tmp_rlm_tab, tmp_task_tab, tmp_ra_tab
from PA_RESOURCE_ASSIGNMENTS
where budget_version_id = P_BUDGET_VERSION_ID;
/*Calling UPDATE_RES_ASG API to update resource_assignment_id in tmp4 for target budget version*/
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
( p_called_mode => p_called_mode,
p_msg => 'Before calling
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG',
p_module_name => l_module_name,
p_log_level => 5 );
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
( P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_STRU_SHARING_CODE => l_stru_sharing_code,
P_GEN_SRC_CODE => l_gen_src_code,
P_FP_COLS_REC => l_fp_cols_rec_target,
P_WP_STRUCTURE_VER_ID=> l_wp_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG: '
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5 );
/* Bug 4057932 When structure is not fully shared source res/target resource mapping will not be one on one. In this case, rate based flag update is not happening correctly This code fixes the issue */
-- SQL Repository Bug 4884824; SQL ID 14903770
SELECT /*+ LEADING(tmp) */
DISTINCT txn_resource_assignment_id
BULK COLLECT
INTO l_tgt_res_asg_id_tab
FROM pa_res_list_map_tmp4 tmp, pa_resource_assignments ra
WHERE tmp.txn_resource_assignment_id = ra.resource_assignment_id
AND ra.rate_based_flag = 'Y'
AND tmp.txn_rate_based_flag = 'N';
UPDATE pa_resource_assignments
SET rate_based_flag = 'N',
unit_of_measure = 'DOLLARS'
WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
l_tgt_res_asg_id_tab.delete;
tmp_rlm_tab.delete;
tmp_task_tab.delete;
tmp_ra_tab.delete;
select distinct resource_list_member_id,txn_task_id,txn_resource_assignment_id
bulk collect into tmp_rlm_tab, tmp_task_tab, tmp_ra_tab
from PA_RES_LIST_MAP_TMP4;
p_msg => 'after update res asg, @@rlm in tmp4:'||tmp_rlm_tab(i)
||'; @@task in tmp4:'||tmp_task_tab(i)
* previously generated budget lines are deleted. If the Retain Manually
* Added Plan Lines flag is 'N', then the wrapper API will have already
* cleared all budget lines for us. However, if the flag is 'Y', then we
* must Delete (non-actuals) budget lines for resources that are not
* manually added/edited. */
IF p_retain_manual_flag = 'Y' THEN
IF l_calling_context = lc_BudgetGeneration THEN
DELETE FROM pa_budget_lines bl
WHERE budget_version_id = p_budget_version_id
AND EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_res_list_map_tmp4 tmp
WHERE tmp.txn_resource_assignment_id = bl.resource_assignment_id
AND rownum = 1 );
DELETE FROM pa_budget_lines bl
WHERE budget_version_id = p_budget_version_id
AND EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_res_list_map_tmp4 tmp
WHERE tmp.txn_resource_assignment_id = bl.resource_assignment_id
AND rownum = 1 )
AND bl.start_date >= l_etc_start_date;
SELECT NVL(UNCATEGORIZED_FLAG,'N') into l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = l_fp_cols_rec_target.X_resource_list_id;
l_res_asg_uom_update_tab.DELETE;
SELECT DISTINCT txn_resource_assignment_id
BULK COLLECT INTO l_res_asg_uom_update_tab
FROM pa_res_list_map_tmp4;
FORALL i IN 1..l_res_asg_uom_update_tab.count
UPDATE pa_resource_assignments
SET unit_of_measure = 'DOLLARS',
rate_based_flag = 'N'
WHERE resource_assignment_id = l_res_asg_uom_update_tab(i);
SELECT bl.resource_assignment_id,
ra.rate_based_flag,
bl.txn_currency_code,
sum(bl.quantity),
sum(bl.txn_raw_cost),
sum(bl.txn_burdened_cost),
sum(bl.txn_revenue),
null, --bl.txn_cost_rate_override
null, --bl.burden_cost_rate_override
null --bl.txn_bill_rate_override
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_tgt_rate_based_flag_tab,
l_txn_currency_code_tab,
l_src_quantity_tab,
l_src_raw_cost_tab,
l_src_brdn_cost_tab,
l_src_revenue_tab,
l_cost_rate_override_tab,
l_b_cost_rate_override_tab,
l_bill_rate_override_tab
FROM pa_Budget_lines bl,
pa_resource_assignments ra
WHERE bl.budget_version_id = p_budget_version_id
AND ra.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = ra.resource_assignment_id
AND EXISTS (SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_res_list_map_tmp4 tmp4
WHERE ra.resource_assignment_id = tmp4.txn_resource_assignment_id
AND rownum = 1)
and bl.cost_rejection_code is null
and bl.revenue_rejection_code is null
and bl.burden_rejection_code is null
and bl.other_rejection_code is null
and bl.pc_cur_conv_rejection_code is null
and bl.pfc_cur_conv_rejection_code is null
GROUP BY bl.resource_assignment_id,
ra.rate_based_flag,
bl.txn_currency_code,
null, --bl.txn_cost_rate_override
null, --bl.burden_cost_rate_override
null; --bl.txn_bill_rate_override
UPDATE PA_RESOURCE_ASSIGNMENTS
SET SPREAD_CURVE_ID = NULL,
SP_FIXED_DATE = NULL
WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
l_mapped_src_res_asg_id_tab.delete;
SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/
tmp.txn_source_id BULK COLLECT INTO l_mapped_src_res_asg_id_tab
FROM pa_res_list_map_tmp4 tmp
WHERE tmp.txn_resource_assignment_id = l_tgt_res_asg_id_tab(i);
l_tmp_tgt_res_asg_id_tab.delete;
l_tmp_tgt_rate_based_flag_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_src_quantity_tab.delete;
l_tmp_src_raw_cost_tab.delete;
l_tmp_src_brdn_cost_tab.delete;
l_tmp_src_revenue_tab.delete;
l_tmp_cost_rate_override_tab.delete;
l_tmp_b_cost_rate_override_tab.delete;
l_tmp_bill_rate_override_tab.delete;
l_tmp_billable_flag_tab.delete;
l_tmp_tgt_res_asg_id_tab.delete;
l_tmp_tgt_rate_based_flag_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_src_quantity_tab.delete;
l_tmp_src_raw_cost_tab.delete;
l_tmp_src_brdn_cost_tab.delete;
l_tmp_src_revenue_tab.delete;
l_tmp_cost_rate_override_tab.delete;
l_tmp_b_cost_rate_override_tab.delete;
l_tmp_bill_rate_override_tab.delete;
l_tmp_billable_flag_tab.delete;
l_tmp_tgt_res_asg_id_tab.delete;
l_tmp_tgt_rate_based_flag_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_src_quantity_tab.delete;
l_tmp_src_raw_cost_tab.delete;
l_tmp_src_brdn_cost_tab.delete;
l_tmp_src_revenue_tab.delete;
l_tmp_cost_rate_override_tab.delete;
l_tmp_b_cost_rate_override_tab.delete;
l_tmp_bill_rate_override_tab.delete;
l_tmp_billable_flag_tab.delete;
l_res_asg_uom_update_tab.DELETE;
SELECT DISTINCT txn_resource_assignment_id
BULK COLLECT INTO l_res_asg_uom_update_tab
FROM pa_res_list_map_tmp4;
FORALL i IN 1..l_res_asg_uom_update_tab.count
UPDATE pa_resource_assignments
SET unit_of_measure = 'DOLLARS',
rate_based_flag = 'N'
WHERE resource_assignment_id = l_res_asg_uom_update_tab(i);
DELETE pa_fp_gen_rate_tmp;
INSERT INTO pa_fp_gen_rate_tmp
( TARGET_RES_ASG_ID,
TXN_CURRENCY_CODE,
RAW_COST_RATE,
BURDENED_COST_RATE,
REVENUE_BILL_RATE ) /* Added for Bug 4568011 */
VALUES ( l_tgt_res_asg_id_tab(i),
l_txn_currency_code_tab(i),
l_cost_rate_override_tab(i),
l_b_cost_rate_override_tab(i),
l_bill_rate_override_tab(i) ); /* Added for Bug 4568011 */
l_delete_budget_lines_tab.extend;
l_delete_budget_lines_tab(i) := Null;
delete from pa_fp_calc_amt_tmp2;
INSERT INTO pa_fp_calc_amt_tmp2(
resource_assignment_id,
txn_currency_code,
TOTAL_PLAN_QUANTITY)
VALUES(
l_tgt_res_asg_id_tab(i),
l_txn_currency_code_tab(i),
l_src_quantity_tab(i));
SELECT sum(init_quantity),sum(init_revenue) into
l_calc_qty_tmp, l_calc_tmp_rev FROM
pa_budget_lines where
resource_assignment_id = l_tgt_res_asg_id_tab(k) AND
txn_currency_code = l_txn_currency_code_tab(k);
p_delete_budget_lines_tab => l_delete_budget_lines_tab,
p_spread_amts_flag_tab => l_spread_amts_flag_tab,
p_txn_currency_code_tab => l_txn_currency_code_tab,
p_txn_currency_override_tab => l_txn_currency_override_tab,
p_total_qty_tab => l_src_quantity_tab,
p_addl_qty_tab => l_addl_qty_tab,
p_total_raw_cost_tab => l_src_raw_cost_tab,
p_addl_raw_cost_tab => l_addl_raw_cost_tab,
p_total_burdened_cost_tab => l_src_brdn_cost_tab,
p_addl_burdened_cost_tab => l_addl_burdened_cost_tab,
p_total_revenue_tab => l_src_revenue_tab,
p_addl_revenue_tab => l_addl_revenue_tab,
p_raw_cost_rate_tab => l_raw_cost_rate_tab,
p_rw_cost_rate_override_tab => l_cost_rate_override_tab,
p_b_cost_rate_tab => l_b_cost_rate_tab,
p_b_cost_rate_override_tab => l_b_cost_rate_override_tab,
p_bill_rate_tab => l_bill_rate_tab,
p_bill_rate_override_tab => l_bill_rate_override_tab,
p_line_start_date_tab => l_line_start_date_tab,
p_line_end_date_tab => l_line_end_date_tab,
p_calling_module => l_calling_context,
p_raTxn_rollup_api_call_flag => l_raTxn_rollup_api_call_flag,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_revenue,
'N', sbl.project_revenue,
'A', sbl.revenue)),
/*
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.txn_revenue
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.project_revenue
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))), --sbl.revenue */
-- Bug 8937993. Need to pull use source rates for only the plannned quantity.
sum(decode(l_txn_currency_flag,
'Y', (((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))
+ NVL(sbl.txn_init_raw_cost,0)), --sbl.txn_raw_cost
'N', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))
+ NVL(sbl.txn_init_raw_cost,0)) * NVL(sbl.project_cost_exchange_rate,1)) , --sbl.project_raw_cost
'A', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))
+ NVL(sbl.txn_init_raw_cost,0)) * NVL(sbl.projfunc_cost_exchange_rate,1))
)), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', (((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))
+ NVL(sbl.txn_init_burdened_cost,0)), --sbl.txn_burdened_cost
'N', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))
+ NVL(sbl.txn_init_burdened_cost,0)) * NVL(sbl.project_cost_exchange_rate,1)), --sbl.project_burdened_cost
'A', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))
+ NVL(sbl.txn_init_burdened_cost,0)) * NVL(sbl.projfunc_cost_exchange_rate,1))
)), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', (((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))
+ NVL(sbl.txn_init_revenue,0)), --sbl.txn_revenue
'N', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))
+ NVL(sbl.txn_init_revenue,0)) * NVL(sbl.project_cost_exchange_rate,1)) , --sbl.project_revenue
'A', ((((sbl.quantity - nvl(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))
+ NVL(sbl.txn_init_revenue,0)) * NVL(sbl.projfunc_cost_exchange_rate,1))
)),
sum(sbl.project_raw_cost),
sum(sbl.project_burdened_cost),
sum(sbl.project_revenue),
sum(sbl.raw_cost),
sum(sbl.burdened_cost),
sum(sbl.revenue),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
avg(sbl.txn_markup_percent) /* Added for Bug 5166047 */
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = p_source_bv_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_target_bv_id
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
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
and ra.project_id = P_PROJECT_ID /* Added for Bug 4543795 */
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'); /* Added for ER 4376722 */
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
ra.planning_start_date,
ra.planning_end_date,
null,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_revenue,
'N', sbl.project_revenue,
'A', sbl.revenue)),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate),
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate),
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate),
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate),
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate),
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate),
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))),
sum(sbl.project_raw_cost),
sum(sbl.project_burdened_cost),
sum(sbl.project_revenue),
sum(sbl.raw_cost),
sum(sbl.burdened_cost),
sum(sbl.revenue),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'),
avg(sbl.txn_markup_percent) -- Bug #13552225
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = p_source_bv_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_target_bv_id
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
and NVL(ra.task_id,0) = ta.task_id (+)
and ra.project_id = P_PROJECT_ID
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
ra.planning_start_date,
ra.planning_end_date,
NULL,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y');
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost,
'N', sbl.project_raw_cost,
'A', sbl.raw_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost,
'N', sbl.project_burdened_cost,
'A', sbl.burdened_cost)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_revenue,
'N', sbl.project_revenue,
'A', sbl.revenue)),
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', sbl.quantity *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.txn_revenue
'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.project_revenue
'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))), --sbl.revenue
sum(sbl.project_raw_cost),
sum(sbl.project_burdened_cost),
sum(sbl.project_revenue),
sum(sbl.raw_cost),
sum(sbl.burdened_cost),
sum(sbl.revenue),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
avg(sbl.txn_markup_percent) /* Added for Bug 5166047 */
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = p_source_bv_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_target_bv_id
and sbl.start_date > decode( c_src_time_phased_code,
'N', sbl.start_date-1, P_ACTUALS_THRU_DATE )
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
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
and ra.project_id = P_PROJECT_ID /* Added for Bug 4543795 */
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'); /* Added for ER 4376722 */
SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
sum(sbl.quantity-NVL(sbl.init_quantity,0)),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_raw_cost - NVL(sbl.txn_init_raw_cost,0),
'N', sbl.project_raw_cost - NVL(sbl.project_init_raw_cost,0),
'A', sbl.raw_cost - NVL(sbl.init_raw_cost,0))),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_burdened_cost - NVL(sbl.txn_init_burdened_cost,0),
'N', sbl.project_burdened_cost - NVL(sbl.project_init_burdened_cost,0),
'A', sbl.burdened_cost - NVL(sbl.init_burdened_cost,0))),
sum(decode(l_txn_currency_flag,
'Y', sbl.txn_revenue - NVL(sbl.txn_init_revenue,0),
'N', sbl.project_revenue - NVL(sbl.project_init_revenue,0),
'A', sbl.revenue - NVL(sbl.init_revenue,0))),
sum(decode(l_txn_currency_flag,
'Y', (sbl.quantity-NVL(sbl.init_quantity,0)) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
'N', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
'A', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
sum(decode(l_txn_currency_flag,
'Y', (sbl.quantity-NVL(sbl.init_quantity,0)) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
'N', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
'A', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
sum(decode(l_txn_currency_flag,
'Y', (sbl.quantity-NVL(sbl.init_quantity,0)) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.txn_revenue
'N', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.project_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate), --sbl.project_revenue
'A', (sbl.quantity-NVL(sbl.init_quantity,0)) * NVL(sbl.projfunc_cost_exchange_rate,1) *
NVL(sbl.txn_bill_rate_override,sbl.txn_standard_bill_rate))), --sbl.revenue
sum(sbl.project_raw_cost - NVL(sbl.project_init_raw_cost,0)),
sum(sbl.project_burdened_cost - NVL(sbl.project_init_burdened_cost,0)),
sum(sbl.project_revenue - NVL(sbl.project_init_revenue,0)),
sum(sbl.raw_cost - NVL(sbl.init_raw_cost,0)),
sum(sbl.burdened_cost - NVL(sbl.init_burdened_cost,0)),
sum(sbl.revenue - NVL(sbl.init_revenue,0)),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
avg(sbl.txn_markup_percent) /* Added for Bug 5166047 */
FROM pa_res_list_map_tmp4 tmp4,
pa_budget_lines sbl,
pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp4.txn_source_id = sbl.resource_assignment_id
and sbl.budget_version_id = p_source_bv_id
and tmp4.txn_resource_assignment_id = ra.resource_assignment_id
and ra.budget_version_id = p_target_bv_id
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
and NVL(sbl.quantity,0) <> NVL(sbl.init_quantity,0)
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
and ra.project_id = P_PROJECT_ID /* Added for Bug 4543795 */
GROUP BY ra.resource_assignment_id,
ra.rate_based_flag,
sbl.start_date,
sbl.end_date,
sbl.period_name,
decode(l_txn_currency_flag,
'Y', sbl.txn_currency_code,
'N', c_proj_currency_code,
'A', c_projfunc_currency_code),
NULL,
NULL,
NULL,
NVL(ta.billable_flag,'Y'); /* Added for ER 4376722 */
SELECT sum(nvl(init_quantity,0))
FROM pa_budget_lines
WHERE resource_assignment_id = c_res_asgn_id
AND txn_currency_code = c_txn_currency_code;
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;
l_res_asg_uom_update_tab pa_plsql_datatypes.IdTabTyp;
l_next_update NUMBER;
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N3)*/
TMP.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.ETC_METHOD_CODE,
RA.MFC_COST_TYPE_ID,
RA.INCURRED_BY_RES_FLAG,
RA.INCUR_BY_RES_CLASS_CODE,
RA.INCUR_BY_ROLE_ID,
RA.UNIT_OF_MEASURE,
RA.RATE_BASED_FLAG,
RA.RESOURCE_RATE_BASED_FLAG, -- Added for IPM ER
RA.RATE_EXPENDITURE_TYPE,
RA.RATE_EXP_FUNC_CURR_CODE,
RA.RATE_EXPENDITURE_ORG_ID
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_etc_method_code_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_unit_of_measure_tab,
l_rate_based_flag_tab,
l_res_rate_based_flag_tab, -- Added for IPM ER
l_rate_expenditure_type_tab,
l_rate_func_curr_code_tab,
l_org_id_tab
FROM PA_RESOURCE_ASSIGNMENTS RA,
PA_RES_LIST_MAP_TMP4 TMP
WHERE RA.budget_version_id = p_source_bv_id
AND RA.resource_assignment_id = TMP.txn_source_id;
UPDATE PA_RESOURCE_ASSIGNMENTS
SET RESOURCE_CLASS_FLAG = l_resource_class_flag_tab(i),
RESOURCE_CLASS_CODE = l_resource_class_code_tab(i),
RES_TYPE_CODE = l_res_type_code_tab(i),
PERSON_ID = l_person_id_tab(i),
JOB_ID = l_job_id_tab(i),
PERSON_TYPE_CODE = l_person_type_code_tab(i),
NAMED_ROLE = l_named_role_tab(i),
BOM_RESOURCE_ID = l_bom_resource_id_tab(i),
NON_LABOR_RESOURCE = l_non_labor_resource_tab(i),
INVENTORY_ITEM_ID = l_inventory_item_id_tab(i),
ITEM_CATEGORY_ID = l_item_category_id_tab(i),
PROJECT_ROLE_ID = l_project_role_id_tab(i),
ORGANIZATION_ID = l_organization_id_tab(i),
FC_RES_TYPE_CODE = l_fc_res_type_code_tab(i),
EXPENDITURE_TYPE = l_expenditure_type_tab(i),
EXPENDITURE_CATEGORY = l_expenditure_category_tab(i),
EVENT_TYPE = l_event_type_tab(i),
REVENUE_CATEGORY_CODE = l_revenue_category_code_tab(i),
SUPPLIER_ID = l_supplier_id_tab(i),
SPREAD_CURVE_ID = l_spread_curve_id_tab(i),
ETC_METHOD_CODE = l_etc_method_code_tab(i),
MFC_COST_TYPE_ID = l_mfc_cost_type_id_tab(i),
INCURRED_BY_RES_FLAG = l_incurred_by_res_flag_tab(i),
INCUR_BY_RES_CLASS_CODE = l_incur_by_res_cls_code_tab(i),
INCUR_BY_ROLE_ID = l_incur_by_role_id_tab(i),
UNIT_OF_MEASURE = l_unit_of_measure_tab(i),
RATE_BASED_FLAG = l_rate_based_flag_tab(i),
RESOURCE_RATE_BASED_FLAG = l_res_rate_based_flag_tab(i), -- Added for IPM ER
RATE_EXPENDITURE_TYPE = l_rate_expenditure_type_tab(i),
RATE_EXP_FUNC_CURR_CODE = l_rate_func_curr_code_tab(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
RATE_EXPENDITURE_ORG_ID = l_org_id_tab(i)
WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
( resource_assignment_id )
SELECT DISTINCT txn_resource_assignment_id
FROM pa_res_list_map_tmp4;
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 )
SELECT rbc.resource_assignment_id,
rbc.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override
FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_target_bv_id
AND rbc.txn_bill_rate_override IS NOT NULL
AND EXISTS ( SELECT null
FROM pa_res_list_map_tmp4 tmp4,
pa_resource_assignments ra,
pa_tasks ta
WHERE rbc.resource_assignment_id = tmp4.txn_resource_assignment_id
AND tmp4.txn_resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = ta.task_id
AND NVL(ta.billable_flag,'Y') = 'N' );
P_ROLLUP_FLAG => 'N', -- 'N' indicates Insert
--P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
UPDATE pa_resource_assignments
SET spread_curve_id = NULL,
sp_fixed_date = NULL
WHERE budget_version_id = p_target_bv_id
AND EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_res_list_map_tmp4 tmp
WHERE tmp.txn_resource_assignment_id = resource_assignment_id
AND rownum = 1 );
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 = l_fp_cols_rec_target.x_budget_version_id;
l_tmp_tgt_res_asg_id_tab.delete;
l_tmp_tgt_rate_based_flag_tab.delete;
l_tmp_start_date_tab.delete;
l_tmp_end_date_tab.delete;
l_tmp_periiod_name_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_src_quantity_tab.delete;
l_tmp_txn_raw_cost_tab.delete;
l_tmp_txn_brdn_cost_tab.delete;
l_tmp_txn_revenue_tab.delete;
l_tmp_unr_txn_raw_cost_tab.delete;
l_tmp_unr_txn_brdn_cost_tab.delete;
l_tmp_unr_txn_revenue_tab.delete;
l_tmp_pc_raw_cost_tab.delete;
l_tmp_pc_brdn_cost_tab.delete;
l_tmp_pc_revenue_tab.delete;
l_tmp_pfc_raw_cost_tab.delete;
l_tmp_pfc_brdn_cost_tab.delete;
l_tmp_pfc_revenue_tab.delete;
l_tmp_cost_rate_override_tab.delete;
l_tmp_bcost_rate_override_tab.delete;
l_tmp_bill_rate_override_tab.delete;
l_tmp_billable_flag_tab.delete;
l_tmp_markup_percent_tab.delete; -- Added for Bug 5166047
l_tmp_tgt_res_asg_id_tab.delete;
l_tmp_tgt_rate_based_flag_tab.delete;
l_tmp_start_date_tab.delete;
l_tmp_end_date_tab.delete;
l_tmp_periiod_name_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_src_quantity_tab.delete;
l_tmp_txn_raw_cost_tab.delete;
l_tmp_txn_brdn_cost_tab.delete;
l_tmp_txn_revenue_tab.delete;
l_tmp_unr_txn_raw_cost_tab.delete;
l_tmp_unr_txn_brdn_cost_tab.delete;
l_tmp_unr_txn_revenue_tab.delete;
l_tmp_pc_raw_cost_tab.delete;
l_tmp_pc_brdn_cost_tab.delete;
l_tmp_pc_revenue_tab.delete;
l_tmp_pfc_raw_cost_tab.delete;
l_tmp_pfc_brdn_cost_tab.delete;
l_tmp_pfc_revenue_tab.delete;
l_tmp_cost_rate_override_tab.delete;
l_tmp_bcost_rate_override_tab.delete;
l_tmp_bill_rate_override_tab.delete;
l_tmp_billable_flag_tab.delete;
l_tmp_markup_percent_tab.delete; -- Added for Bug 5166047
new_currency_tab.DELETE;
l_tmp_tgt_res_asg_id_tab.delete;
l_tmp_tgt_rate_based_flag_tab.delete;
l_tmp_start_date_tab.delete;
l_tmp_end_date_tab.delete;
l_tmp_periiod_name_tab.delete;
l_tmp_txn_currency_code_tab.delete;
l_tmp_src_quantity_tab.delete;
l_tmp_txn_raw_cost_tab.delete;
l_tmp_txn_brdn_cost_tab.delete;
l_tmp_txn_revenue_tab.delete;
l_tmp_unr_txn_raw_cost_tab.delete;
l_tmp_unr_txn_brdn_cost_tab.delete;
l_tmp_unr_txn_revenue_tab.delete;
l_tmp_pc_raw_cost_tab.delete;
l_tmp_pc_brdn_cost_tab.delete;
l_tmp_pc_revenue_tab.delete;
l_tmp_pfc_raw_cost_tab.delete;
l_tmp_pfc_brdn_cost_tab.delete;
l_tmp_pfc_revenue_tab.delete;
l_tmp_cost_rate_override_tab.delete;
l_tmp_bcost_rate_override_tab.delete;
l_tmp_bill_rate_override_tab.delete;
l_tmp_billable_flag_tab.delete;
l_tmp_markup_percent_tab.delete; -- Added for Bug 5166047
l_res_asg_uom_update_tab.DELETE;
SELECT DISTINCT txn_resource_assignment_id
BULK COLLECT INTO l_res_asg_uom_update_tab
FROM pa_res_list_map_tmp4;
FORALL i IN 1..l_res_asg_uom_update_tab.count
UPDATE pa_resource_assignments
SET unit_of_measure = 'DOLLARS',
rate_based_flag = 'N'
WHERE resource_assignment_id = l_res_asg_uom_update_tab(i);
DELETE pa_fp_gen_rate_tmp;
INSERT INTO pa_fp_gen_rate_tmp
( TARGET_RES_ASG_ID,
TXN_CURRENCY_CODE,
PERIOD_NAME,
RAW_COST_RATE,
BURDENED_COST_RATE,
REVENUE_BILL_RATE ) /* Added for Bug 4568011 */
VALUES ( l_tgt_res_asg_id_tab(i),
l_txn_currency_code_tab(i),
l_periiod_name_tab(i),
l_cost_rate_override_tab(i),
l_b_cost_rate_override_tab(i),
l_bill_rate_override_tab(i) ); /* Added for Bug 4568011 */
hr_utility.trace('??before insert, l_tgt_res_asg_id_tab.count:'||l_tgt_res_asg_id_tab.count);
DELETE PA_FP_CALC_AMT_TMP2;
INSERT INTO PA_FP_CALC_AMT_TMP2 (
ETC_PLAN_QUANTITY,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE )
VALUES (
l_index_tab(i),
l_tgt_res_asg_id_tab(i),
l_txn_currency_code_tab(i) );
/* Get indices for budget lines we need to UPDATE */
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N2)*/
tmp.ETC_PLAN_QUANTITY
BULK COLLECT INTO l_upd_index_tab
FROM pa_budget_lines bl,
pa_fp_calc_amt_tmp2 tmp
WHERE bl.budget_version_id = P_TARGET_BV_ID
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
ORDER BY tmp.ETC_PLAN_QUANTITY ASC;
/* Separate budget line data into INSERT and UPDATE tables */
-- These indexes are used for the insert/update pl/sql tables
l_upd_index := 0;
-- This is index of the next entry in l_tgt_res_asg_id_tab that is an update
l_next_update := 0;
IF l_next_update < i AND l_tab_index <= l_upd_index_tab.count THEN
l_next_update := l_upd_index_tab(l_tab_index);
IF i = l_next_update THEN
-- Populate update pl/sql tables
l_upd_index := l_upd_index + 1;
-- Populate insert pl/sql tables
l_ins_index := l_ins_index + 1;
/* Now that we have insert/update tables populated, do Insert */
FORALL i IN 1..l_ins_tgt_res_asg_id_tab.count
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
REVENUE,
PROJECT_REVENUE,
END_DATE,
PERIOD_NAME,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE)
VALUES (
pa_budget_lines_s.nextval,
P_TARGET_BV_ID,
l_ins_tgt_res_asg_id_tab(i),
l_ins_start_date_tab(i),
l_ins_txn_currency_code_tab(i),
l_ins_txn_raw_cost_tab(i),
l_ins_txn_brdn_cost_tab(i),
l_ins_txn_revenue_tab(i),
l_ins_pfc_revenue_tab(i),
l_ins_pc_revenue_tab(i),
l_ins_end_date_tab(i),
l_ins_periiod_name_tab(i),
l_ins_src_quantity_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_fp_cols_rec_target.X_PROJECT_CURRENCY_CODE,
l_fp_cols_rec_target.X_PROJFUNC_CURRENCY_CODE,
l_ins_cost_rate_override_tab(i),
l_ins_bcost_rate_override_tab(i),
l_ins_bill_rate_override_tab(i));
/* Now, go through the Update logic */
IF l_rev_gen_method = 'C' AND
l_fp_cols_rec_target.x_version_type = 'REVENUE' THEN
-- ER 4376722: Changed the update logic as follows.
-- Before: Set amount = NVL(actual amount,0) + update amount.
-- After: If actual amount is null, then set amount = update amount.
-- If actual amount is not null, then
-- set amount = actual amount + NVL(update amount, 0)
-- The new logic preserves the non-null actual amounts.
-- This change is necessary in case update revenue is Null. Using the
-- old logic, we would set revenue to NVL(actual revenue,0) + Null,
-- which is just Null. In other words, the actual revenue would be lost.
-- Using the new logic, we would set revenue to actual revenue +
-- NVL(NULL,0) = actual revenue.
--
-- Also, modified range of iteration to use l_upd_tgt_res_asg_id_tab.count
-- instead of l_ins_tgt_res_asg_id_tab.count.
-- Add Actuals to Plan columns since Calculate API not called in this flow.
FORALL i IN 1..l_upd_tgt_res_asg_id_tab.count
UPDATE PA_BUDGET_LINES
SET TXN_RAW_COST =
DECODE(TXN_INIT_RAW_COST, null, l_upd_txn_raw_cost_tab(i),
TXN_INIT_RAW_COST + NVL(l_upd_txn_raw_cost_tab(i),0)),
TXN_BURDENED_COST =
DECODE(TXN_INIT_BURDENED_COST, null, l_upd_txn_brdn_cost_tab(i),
TXN_INIT_BURDENED_COST + NVL(l_upd_txn_brdn_cost_tab(i),0)),
TXN_REVENUE =
DECODE(TXN_INIT_REVENUE, null, l_upd_txn_revenue_tab(i),
TXN_INIT_REVENUE + NVL(l_upd_txn_revenue_tab(i),0)),
REVENUE =
DECODE(INIT_REVENUE, null, l_upd_pfc_revenue_tab(i),
INIT_REVENUE + NVL(l_upd_pfc_revenue_tab(i),0)),
PROJECT_REVENUE =
DECODE(PROJECT_INIT_REVENUE, null, l_upd_pc_revenue_tab(i),
PROJECT_INIT_REVENUE + NVL(l_upd_pc_revenue_tab(i),0)),
QUANTITY =
DECODE(INIT_QUANTITY, null, l_upd_src_quantity_tab(i),
INIT_QUANTITY + NVL(l_upd_src_quantity_tab(i),0)),
START_DATE = l_upd_start_date_tab(i),
END_DATE = l_upd_end_date_tab(i),
PERIOD_NAME = l_upd_periiod_name_tab(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(i),
BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(i),
TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(i)
WHERE budget_version_id = P_TARGET_BV_ID
AND resource_assignment_id = l_upd_tgt_res_asg_id_tab(i)
AND txn_currency_code = l_upd_txn_currency_code_tab(i);
UPDATE PA_BUDGET_LINES
SET TXN_RAW_COST = l_upd_txn_raw_cost_tab(i),
TXN_BURDENED_COST = l_upd_txn_brdn_cost_tab(i),
TXN_REVENUE = l_upd_txn_revenue_tab(i),
REVENUE = l_upd_pfc_revenue_tab(i),
PROJECT_REVENUE = l_upd_pc_revenue_tab(i),
QUANTITY = l_upd_src_quantity_tab(i),
START_DATE = l_upd_start_date_tab(i),
END_DATE = l_upd_end_date_tab(i),
PERIOD_NAME = l_upd_periiod_name_tab(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(i),
BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(i),
TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(i)
WHERE budget_version_id = P_TARGET_BV_ID
AND resource_assignment_id = l_upd_tgt_res_asg_id_tab(i)
AND txn_currency_code = l_upd_txn_currency_code_tab(i);
END IF; -- Update logic
INSERT INTO PA_BUDGET_LINES (
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
START_DATE,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
REVENUE,
PROJECT_REVENUE,
END_DATE,
PERIOD_NAME,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE,
TXN_MARKUP_PERCENT ) /* Added for Bug 5166047 */
VALUES (
pa_budget_lines_s.nextval,
P_TARGET_BV_ID,
l_tgt_res_asg_id_tab(i),
l_start_date_tab(i),
l_txn_currency_code_tab(i),
l_txn_raw_cost_tab(i),
l_txn_brdn_cost_tab(i),
l_txn_revenue_tab(i),
l_pfc_revenue_tab(i),
l_pc_revenue_tab(i),
l_end_date_tab(i),
l_periiod_name_tab(i),
l_src_quantity_tab(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_fp_cols_rec_target.X_PROJECT_CURRENCY_CODE,
l_fp_cols_rec_target.X_PROJFUNC_CURRENCY_CODE,
l_cost_rate_override_tab(i),
l_b_cost_rate_override_tab(i),
'', /* bug 7693017 */
l_markup_percent_tab(i)); /* Added for Bug 5166047 */
( p_msg => 'After inserting into target budget lines',
p_module_name => l_module_name,
p_log_level => 5 );
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
( resource_assignment_id,
txn_currency_code,
txn_raw_cost_rate_override,
txn_burden_cost_rate_override,
txn_bill_rate_override )
SELECT DISTINCT
bl.resource_assignment_id,
bl.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override,
rbc.txn_bill_rate_override
FROM pa_resource_assignments ra,
pa_budget_lines bl,
pa_resource_asgn_curr rbc,
pa_res_list_map_tmp4 tmp4
WHERE ra.budget_version_id = p_target_bv_id
AND ra.project_id = p_project_id
AND ra.resource_assignment_id = tmp4.txn_resource_assignment_id
AND bl.resource_assignment_id = ra.resource_assignment_id
AND bl.resource_assignment_id = rbc.resource_assignment_id (+)
AND bl.txn_currency_code = rbc.txn_currency_code (+);
* Lastly, this description should be updated any time the layout of
* x_calculate_api_code is modified.
*/
PROCEDURE GET_CALC_API_FLAG_PARAMS
(P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
P_FP_COLS_REC_SOURCE IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_FP_COLS_REC_TARGET IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_CONTEXT IN VARCHAR2,
X_CALCULATE_API_CODE OUT NOCOPY VARCHAR2,
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_WP_GEN_BUDGET_AMT_PUB.'
|| 'GET_CALC_API_FLAG_PARAMS';