[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO PA_FP_CALCULATE_LOG
(SESSIONID
,SEQ_NUMBER
,LOG_MESSAGE)
VALUES
(userenv('sessionid')
,HR.PAY_US_GARN_FEE_RULES_S.nextval
,substr(P_MSG,1,240)
);
SELECT nvl(pfo.cost_resource_list_id, nvl(pfo.revenue_resource_list_id, pfo.all_resource_list_id)) resource_list_id
,pfo.rbs_version_id rbs_version_id
,pbv.ci_id ci_id
,pbv.etc_start_date etc_start_date
,pbv.wp_version_flag wp_version_flag
FROM pa_proj_fp_options pfo
,pa_budget_versions pbv
WHERE pfo.fin_plan_version_id=p_budget_version_id
AND pbv.budget_version_id=p_budget_version_id;
SELECT sum(quantity) quantity
FROM pa_budget_lines
WHERE resource_assignment_id = c_resource_asg_id;
SELECT ppa.name project_name
,pt.name task_name
,prl.alias resource_name
FROM pa_projects_all ppa
,pa_proj_elements pt
,pa_resource_list_members prl
,pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND ppa.project_id = pra.project_id
AND pt.proj_element_id(+) = pra.task_id
AND prl.resource_list_member_id = pra.resource_list_member_id;
SELECT rtx.txn_raw_cost_rate_override
,rtx.txn_burden_cost_rate_override
,rtx.txn_bill_rate_override
FROM pa_resource_asgn_curr rtx
WHERE rtx.resource_assignment_id = p_resource_assignment_id;
pa_debug.g_err_stage:='All the resource attrs passed are NULL and hence No change. Not firing the Select';
SELECT rbs_element_id,
txn_accum_header_id
INTO x_rbs_element_id_tbl(i),
x_txn_accum_header_id_tbl(i)
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id_tbl(i);
SELECT
DECODE(DECODE(NVL(p_project_role_id_tbl(i),project_role_id),
FND_API.G_MISS_NUM, decode(project_role_id,null,0,1),
project_role_id,0,
1)+
DECODE(NVL(p_resource_list_member_id_tbl(i),resource_list_member_id),
FND_API.G_MISS_NUM, decode(resource_list_member_id,null,0,1),
resource_list_member_id, 0,
1),
0, 'N',
'Y'),--Indicates whether the rbs mapping api should be called or not
mfc_cost_type_id,
NVL(p_mfc_cost_type_id_tbl(i),mfc_cost_type_id),
spread_curve_id,
NVL(p_spread_curve_id_tbl(i),spread_curve_id),
sp_fixed_date,
DECODE(nvl(p_spread_curve_id_tbl(i),spread_curve_id),
p_fixed_date_sp_id,DECODE(DECODE(p_sp_fixed_date_tbl(i),
FND_API.G_MISS_DATE,to_date(null),
nvl(p_sp_fixed_date_tbl(i),sp_fixed_date))
,to_date(null),DECODE (p_planning_start_date_tbl(i),
FND_API.G_MISS_DATE,to_date(null),
nvl(p_planning_start_date_tbl(i),planning_start_date))
,nvl(p_sp_fixed_date_tbl(i),sp_fixed_date))
,to_date(null)),
planning_start_date,
NVL(p_planning_start_date_tbl(i),planning_start_date),
planning_end_date,
NVL(p_planning_end_date_tbl(i),planning_end_date),
DECODE (p_txn_currency_code_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_txn_currency_code_tbl(i),pbl.txn_currency_code)),
DECODE (p_inventory_item_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_inventory_item_id_tbl(i),inventory_item_id)) ,
DECODE (p_expenditure_type_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_expenditure_type_tbl(i),expenditure_type)),
DECODE (p_person_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_person_id_tbl(i),person_id)) ,
DECODE (p_job_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_job_id_tbl(i),job_id)) ,
DECODE (p_organization_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_organization_id_tbl(i),organization_id)) ,
DECODE (p_event_type_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_event_type_tbl(i),event_type)) ,
DECODE (p_expenditure_category_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_expenditure_category_tbl(i),expenditure_category)) ,
DECODE (p_revenue_category_code_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_revenue_category_code_tbl(i),revenue_category_code)) ,
DECODE (p_item_category_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_item_category_id_tbl(i),item_category_id)) ,
DECODE (p_bom_resource_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_bom_resource_id_tbl(i),bom_resource_id)) ,
DECODE (p_project_role_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_project_role_id_tbl(i),project_role_id)) ,
DECODE (p_person_type_code_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_person_type_code_tbl(i),person_type_code)) ,
DECODE (p_supplier_id_tbl(i), FND_API.G_MISS_NUM,null,nvl(p_supplier_id_tbl(i),supplier_id)),
DECODE (p_named_role_tbl(i), FND_API.G_MISS_CHAR,null,nvl(p_named_role_tbl(i),named_role )),
resource_class_code,
rate_based_flag,
rbs_element_id,
non_labor_resource,
txn_accum_header_id,
task_id
INTO
x_rlm_id_change_flag_tbl(i),
x_mfc_cost_type_id_old_tbl(i),
x_mfc_cost_type_id_new_tbl(i),
x_spread_curve_id_old_tbl(i),
x_spread_curve_id_new_tbl(i),
x_sp_fixed_date_old_tbl(i),
x_sp_fixed_date_new_tbl(i),
x_plan_start_date_old_tbl(i),
x_plan_start_date_new_tbl(i),
x_plan_end_date_old_tbl(i),
x_plan_end_date_new_tbl(i),
l_txn_currency_code_tbl(i),
l_inventory_item_id_tbl(i),
l_expenditure_type_tbl(i),
l_person_id_tbl(i),
l_job_id_tbl(i),
l_organization_id_tbl(i),
l_event_type_tbl(i),
l_expenditure_category_tbl(i),
l_revenue_category_code_tbl(i),
l_item_category_id_tbl(i),
l_bom_resource_id_tbl(i),
l_project_role_id_tbl(i),
l_person_type_code_tbl(i),
l_supplier_id_tbl(i),
l_named_role_tbl(i),
l_resource_class_code_tbl(i),
l_rate_based_flag_tbl(i),
x_rbs_element_id_tbl(i),
l_non_labor_resource_tbl(i),
x_txn_accum_header_id_tbl(i),
l_task_id_tbl(i)
FROM pa_resource_assignments pra,
(SELECT pra.resource_assignment_id
,pbl.txn_currency_code
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pbl.resource_assignment_id(+)=pra.resource_assignment_id
AND pra.resource_assignment_id=p_resource_assignment_id_tbl(i)
AND ROWNUM=1) pbl
WHERE pra.resource_assignment_id=p_resource_assignment_id_tbl(i);
SELECT min(start_date), max(end_date)
INTO l_actuals_start_date, l_actuals_end_date
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND resource_assignment_id = p_resource_assignment_id_tbl(i)
AND end_date < l_plan_ver_settings_rec.etc_start_date;
SELECT ppa.name project_name
,pt.name task_name
,prl.alias resource_name
INTO l_project_name
,l_task_name
,l_resource_name
FROM pa_projects_all ppa
,pa_proj_elements pt
,pa_resource_list_members prl
,pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_resource_assignment_id_tbl(i)
AND ppa.project_id = pra.project_id
AND pt.proj_element_id(+) = pra.task_id
/* Bug fix:4200168 AND prl.resource_list_member_id(+) = pra.resource_list_member_id;*/
pa_debug.g_err_stage:='Spread curve id not chosen to be updated and value in db for..';
the entries in reporting data and then also deleted all the budget lines. This
is important as this is one reason of data corruption happening in the reporting
integration. Calculate cannot handle this case as the data seen by calculate is
the changed data and not the old one.
Pass the following to calculate API: Total QTY, ETC AMT (Plan-Actual)/ ETC
QTY (Plan-actual) as ETC Rate (if qty and amounts are both present), Total
Amount (Raw Cost, Burdened Cost and or Revenue) (These may ( if qty is null) or
may not be passed) . No delete flag is required as all the lines have already
been removed. */
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='Calling API delete_planning_transactions ';
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => p_context
,p_calling_context => p_calling_context -- Added for Bug 6856934
,p_task_or_res => 'ASSIGNMENT'
,p_resource_assignment_tbl => l_ra_id_rbs_prm_tbl
,p_validate_delete_flag => 'N'
,p_calling_module => 'PROCESS_RES_CHG_DERV_CALC_PRMS'
,p_task_id_tbl => l_task_id_rbs_prm_tbl
,p_rbs_element_id_tbl => l_rbs_elem_id_rbs_prm_tbl
,p_rate_based_flag_tbl => l_rbf_rbs_prm_tbl
,p_resource_class_code_tbl => l_res_class_code_rbs_prm_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.g_err_stage:='Called API delete_planning_transactions returned error';
l_delete_budget_lines_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
SELECT project_currency_code, projfunc_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = c_elem_version_id;
l_delete_budget_lines_tbl.extend((l_rlm_id_no_of_rows)*(l_elem_ver_id_no_of_rows));
SELECT fin_plan_type_id
INTO l_fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE use_for_workplan_flag ='Y';
SELECT nvl(cost_resource_list_id, nvl(revenue_resource_list_id, all_resource_list_id))
,rbs_version_id
INTO l_resource_list_id
,l_rbs_version_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id=l_budget_version_id;
Select spread_curve_id
into l_fixed_date_sp_id
from pa_spread_curves_b
where spread_curve_code = 'FIXED_DATE';
pa_debug.g_err_stage:='data is deleted in bulk from pa_budget_lines and pa_resource_assignment based on the resource_assignment_id ';
pa_debug.g_err_stage:='BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS ';
l_ppl_index:=1; --This will be used in the bulk insert for people -- -- Bug 3749516
pa_debug.g_err_stage:='BULK INSERTING DATA - p_context - Workplan :'||p_context ;
pa_debug.g_err_stage:='BULK INSERTING Workplan DATA - rlm id :'||l_eligible_rlm_ids_tbl(l_ppl_index) ;
l_ins_proj_element_id_tbl.delete(l_ins_index,l_ins_proj_element_id_tbl.count);
l_ins_task_elem_version_id_tbl.delete(l_ins_index,l_ins_task_elem_version_id_tbl.count);
l_ins_start_date_tbl.delete(l_ins_index,l_ins_start_date_tbl.count);
l_ins_end_date_tbl.delete(l_ins_index,l_ins_end_date_tbl.count);
l_ins_cal_people_effort_tbl.delete(l_ins_index,l_ins_cal_people_effort_tbl.count);
l_ins_cal_burdened_cost_tbl.delete(l_ins_index,l_ins_cal_burdened_cost_tbl.count);
l_ins_cal_raw_cost_tbl.delete(l_ins_index,l_ins_cal_raw_cost_tbl.count);
INSERT INTO PA_RESOURCE_ASSIGNMENTS (
RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
,TOTAL_LENT_RESOURCE_COST,TOTAL_TP_COST_IN,TOTAL_TP_COST_OUT,TOTAL_COST_ADJ,TOTAL_UNASSIGNED_TIME_COST
,TOTAL_UTILIZATION_PERCENT,TOTAL_UTILIZATION_HOURS,TOTAL_UTILIZATION_ADJ,TOTAL_CAPACITY,TOTAL_HEAD_COUNT
,TOTAL_HEAD_COUNT_ADJ,RESOURCE_ASSIGNMENT_TYPE,TOTAL_PROJECT_RAW_COST,TOTAL_PROJECT_BURDENED_COST,TOTAL_PROJECT_REVENUE
,PARENT_ASSIGNMENT_ID,WBS_ELEMENT_VERSION_ID,RBS_ELEMENT_ID,PLANNING_START_DATE,PLANNING_END_DATE,SCHEDULE_START_DATE,SCHEDULE_END_DATE
,SPREAD_CURVE_ID,ETC_METHOD_CODE,RES_TYPE_CODE,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25
,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,ATTRIBUTE29,ATTRIBUTE30,FC_RES_TYPE_CODE,RESOURCE_CLASS_CODE,ORGANIZATION_ID,JOB_ID
,PERSON_ID,EXPENDITURE_TYPE,EXPENDITURE_CATEGORY,REVENUE_CATEGORY_CODE,EVENT_TYPE,SUPPLIER_ID,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID,INVENTORY_ITEM_ID,ITEM_CATEGORY_ID,RECORD_VERSION_NUMBER,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE,MFC_COST_TYPE_ID,PROCURE_RESOURCE_FLAG,ASSIGNMENT_DESCRIPTION
,INCURRED_BY_RES_FLAG,RATE_JOB_ID,RATE_EXPENDITURE_TYPE,TA_DISPLAY_FLAG
,SP_FIXED_DATE,PERSON_TYPE_CODE,RATE_BASED_FLAG,USE_TASK_SCHEDULE_FLAG,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID
,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE, resource_rate_based_flag)
VALUES (
PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL -- RESOURCE_ASSIGNMENT_ID
,l_budget_version_id -- BUDGET_VERSION_ID
,p_project_id -- PROJECT_ID
,l_ins_proj_element_id_tbl(i) -- TASK_ID
,l_people_res_class_rlm_id -- RESOURCE_LIST_MEMBER_ID
,sysdate -- LAST_UPDATE_DATE
,fnd_global.user_id -- LAST_UPDATED_BY
,sysdate -- CREATION_DATE
,fnd_global.user_id -- CREATED_BY
,fnd_global.login_id -- LAST_UPDATE_LOGIN
,l_unit_of_measure_tbl(l_ppl_index) -- UNIT_OF_MEASURE
,NULL -- TRACK_AS_LABOR_FLAG
,NULL -- STANDARD_BILL_RATE
,NULL -- AVERAGE_BILL_RATE
,NULL -- AVERAGE_COST_RATE
,-1 -- PROJECT_ASSIGNMENT_ID
,NULL -- PLAN_ERROR_CODE
,NULL -- TOTAL_PLAN_REVENUE
,NULL -- TOTAL_PLAN_RAW_COST
,NULL -- TOTAL_PLAN_BURDENED_COST
,NULL -- TOTAL_PLAN_QUANTITY
,NULL -- AVERAGE_DISCOUNT_PERCENTAGE
,NULL -- TOTAL_BORROWED_REVENUE
,NULL -- TOTAL_TP_REVENUE_IN
,NULL -- TOTAL_TP_REVENUE_OUT
,NULL -- TOTAL_REVENUE_ADJ
,NULL -- TOTAL_LENT_RESOURCE_COST
,NULL -- TOTAL_TP_COST_IN
,NULL -- TOTAL_TP_COST_OUT
,NULL -- TOTAL_COST_ADJ
,NULL -- TOTAL_UNASSIGNED_TIME_COST
,NULL -- TOTAL_UTILIZATION_PERCENT
,NULL -- TOTAL_UTILIZATION_HOURS
,NULL -- TOTAL_UTILIZATION_ADJ
,NULL -- TOTAL_CAPACITY
,NULL -- TOTAL_HEAD_COUNT
,NULL -- TOTAL_HEAD_COUNT_ADJ
,'USER_ENTERED' -- RESOURCE_ASSIGNMENT_TYPE
,NULL -- TOTAL_PROJECT_RAW_COST
,NULL -- TOTAL_PROJECT_BURDENED_COST
,NULL -- TOTAL_PROJECT_REVENUE
,NULL -- PARENT_ASSIGNMENT_ID
,l_ins_task_elem_version_id_tbl(i) -- WBS_ELEMENT_VERSION_ID
,l_rbs_element_id_tbl(l_ppl_index) -- RBS_ELEMENT_ID
,l_ins_start_date_tbl(i) -- PLANNING_START_DATE
,l_ins_end_date_tbl(i) -- PLANNING_END_DATE
,l_ins_start_date_tbl(i) -- SCHEDULE_START_DATE
,l_ins_end_date_tbl(i) -- SCHEDULE_END_DATE
,l_spread_curve_id_tbl(l_ppl_index) -- SPREAD_CURVE_ID
,l_etc_method_code_tbl(l_ppl_index) -- ETC_METHOD_CODE
,l_res_type_code_tbl(l_ppl_index) -- RES_TYPE_CODE
,NULL -- ATTRIBUTE_CATEGORY
,NULL -- ATTRIBUTE1
,NULL -- ATTRIBUTE2
,NULL -- ATTRIBUTE3
,NULL -- ATTRIBUTE4
,NULL -- ATTRIBUTE5
,NULL -- ATTRIBUTE6
,NULL -- ATTRIBUTE7
,NULL -- ATTRIBUTE8
,NULL -- ATTRIBUTE9
,NULL -- ATTRIBUTE10
,NULL -- ATTRIBUTE11
,NULL -- ATTRIBUTE12
,NULL -- ATTRIBUTE13
,NULL -- ATTRIBUTE14
,NULL -- ATTRIBUTE15
,NULL -- ATTRIBUTE16
,NULL -- ATTRIBUTE17
,NULL -- ATTRIBUTE18
,NULL -- ATTRIBUTE19
,NULL -- ATTRIBUTE20
,NULL -- ATTRIBUTE21
,NULL -- ATTRIBUTE22
,NULL -- ATTRIBUTE23
,NULL -- ATTRIBUTE24
,NULL -- ATTRIBUTE25
,NULL -- ATTRIBUTE26
,NULL -- ATTRIBUTE27
,NULL -- ATTRIBUTE28
,NULL -- ATTRIBUTE29
,NULL -- ATTRIBUTE30
,l_fc_res_type_code_tbl(l_ppl_index) -- FC_RES_TYPE_CODE
,l_resource_class_code_tbl(l_ppl_index) -- RESOURCE_CLASS_CODE
,l_organization_id_tbl(l_ppl_index) -- ORGANIZATION_ID
,l_job_id_tbl(l_ppl_index) -- JOB_ID
,l_person_id_tbl(l_ppl_index) -- PERSON_ID
,l_expenditure_type_tbl(l_ppl_index) -- EXPENDITURE_TYPE
,l_expenditure_category_tbl(l_ppl_index) -- EXPENDITURE_CATEGORY
,l_revenue_category_code_tbl(l_ppl_index) -- REVENUE_CATEGORY_CODE
,l_event_type_tbl(l_ppl_index) -- EVENT_TYPE
,l_supplier_id_tbl(l_ppl_index) -- SUPPLIER_ID
,l_non_labor_resource_tbl(l_ppl_index) -- NON_LABOR_RESOURCE
,l_bom_resource_id_tbl(l_ppl_index) -- BOM_RESOURCE_ID
,l_inventory_item_id_tbl(l_ppl_index) -- INVENTORY_ITEM_ID
,l_item_category_id_tbl(l_ppl_index) -- ITEM_CATEGORY_ID
,1 -- RECORD_VERSION_NUMBER
,NULL -- BILLABLE_PERCENT
,NULL -- TRANSACTION_SOURCE_CODE
,l_mfc_cost_type_id_tbl(l_ppl_index) -- MFC_COST_TYPE_ID
,NULL -- PROCURE_RESOURCE_FLAG
,NULL -- ASSIGNMENT_DESCRIPTION
,l_incurred_by_res_flag_tbl(l_ppl_index) -- INCURRED_BY_RES_FLAG
,NULL -- RATE_JOB_ID
,l_rate_expenditure_type_tbl(l_ppl_index) -- RATE_EXPENDITURE_TYPE
,'N' -- TA_DISPLAY_FLAG
,decode(l_spread_curve_id_tbl(l_ppl_index),l_fixed_date_sp_id,l_ins_start_date_tbl(i),null)-- SP_FIXED_DATE -- Bug 3607061
,l_person_type_code_tbl(l_ppl_index) -- PERSON_TYPE_CODE
,l_rate_based_flag_tbl(l_ppl_index) -- RATE_BASED_FLAG
,NULL -- USE_TASK_SCHEDULE_FLAG
,l_rate_func_curr_code_tbl(l_ppl_index) -- RATE_EXP_FUNC_CURR_CODE
,l_org_id_tbl(l_ppl_index) -- RATE_EXPENDITURE_ORG_ID
,l_incur_by_res_class_code_tbl(l_ppl_index) -- INCUR_BY_RES_CLASS_CODE
,l_incur_by_role_id_tbl(l_ppl_index) -- INCUR_BY_ROLE_ID
,l_project_role_id_tbl(l_ppl_index) -- PROJECT_ROLE_ID
,l_resource_class_flag_tbl(l_ppl_index) -- RESOURCE_CLASS_FLAG
,l_named_role_tbl(l_ppl_index) -- NAMED_ROLE
,l_txn_accum_header_id_tbl(l_ppl_index) -- TXN ACCUM HEADER ID
,l_pm_product_code_tbl(l_ppl_index) -- PM_PRODUCT_CODE
,l_pm_res_asgmt_ref_tbl(l_ppl_index) -- PM_RES_ASSIGNMENT_REFERENCE
,l_rate_based_flag_tbl(l_ppl_index) -- RESOURCE_RATE_BASED_FLAG IPM
)
RETURNING resource_assignment_id BULK COLLECT INTO l_res_assignment_id_temp_tbl ;
pa_debug.g_err_stage:='BULK INSERTING DATA - WORPLAN - PEOPLE '||l_res_assignment_id_temp_tbl(k);
pa_debug.g_err_stage:='BULK INSERTING DATA - Context TASK p_context : '||p_context;
INSERT INTO PA_RESOURCE_ASSIGNMENTS (
RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
,TOTAL_LENT_RESOURCE_COST,TOTAL_TP_COST_IN,TOTAL_TP_COST_OUT,TOTAL_COST_ADJ,TOTAL_UNASSIGNED_TIME_COST
,TOTAL_UTILIZATION_PERCENT,TOTAL_UTILIZATION_HOURS,TOTAL_UTILIZATION_ADJ,TOTAL_CAPACITY,TOTAL_HEAD_COUNT
,TOTAL_HEAD_COUNT_ADJ,RESOURCE_ASSIGNMENT_TYPE,TOTAL_PROJECT_RAW_COST,TOTAL_PROJECT_BURDENED_COST,TOTAL_PROJECT_REVENUE
,PARENT_ASSIGNMENT_ID,WBS_ELEMENT_VERSION_ID,RBS_ELEMENT_ID,PLANNING_START_DATE,PLANNING_END_DATE,SCHEDULE_START_DATE,SCHEDULE_END_DATE
,SPREAD_CURVE_ID,ETC_METHOD_CODE,RES_TYPE_CODE,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25
,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,ATTRIBUTE29,ATTRIBUTE30,FC_RES_TYPE_CODE,RESOURCE_CLASS_CODE,ORGANIZATION_ID,JOB_ID
,PERSON_ID,EXPENDITURE_TYPE,EXPENDITURE_CATEGORY,REVENUE_CATEGORY_CODE,EVENT_TYPE,SUPPLIER_ID,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID,INVENTORY_ITEM_ID,ITEM_CATEGORY_ID,RECORD_VERSION_NUMBER,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE,MFC_COST_TYPE_ID,PROCURE_RESOURCE_FLAG,ASSIGNMENT_DESCRIPTION
,INCURRED_BY_RES_FLAG,RATE_JOB_ID,RATE_EXPENDITURE_TYPE,TA_DISPLAY_FLAG
,SP_FIXED_DATE,PERSON_TYPE_CODE,RATE_BASED_FLAG,USE_TASK_SCHEDULE_FLAG,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID,UNPLANNED_FLAG
,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE,SCHEDULED_DELAY, resource_rate_based_flag)
VALUES(PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL -- RESOURCE_ASSIGNMENT_ID
,l_budget_version_id -- BUDGET_VERSION_ID
,p_project_id -- PROJECT_ID
,decode(p_one_to_one_mapping_flag,'Y',l_proj_elem_rlm_tbl(j)
,l_proj_element_id_tbl(i)) -- TASK_ID
,l_eligible_rlm_ids_tbl(j) -- RESOURCE_LIST_MEMBER_ID
,sysdate -- LAST_UPDATE_DATE
,fnd_global.user_id -- LAST_UPDATED_BY
,sysdate -- CREATION_DATE
,fnd_global.user_id -- CREATED_BY
,fnd_global.login_id -- LAST_UPDATE_LOGIN
,l_unit_of_measure_tbl(j) -- UNIT_OF_MEASURE
,NULL -- TRACK_AS_LABOR_FLAG
,NULL -- STANDARD_BILL_RATE
,NULL -- AVERAGE_BILL_RATE
,NULL -- AVERAGE_COST_RATE
,nvl(l_project_assignment_id_tbl(j),-1) -- PROJECT_ASSIGNMENT_ID
,NULL -- PLAN_ERROR_CODE
,NULL -- TOTAL_PLAN_REVENUE
,NULL -- TOTAL_PLAN_RAW_COST
,NULL -- TOTAL_PLAN_BURDENED_COST
,NULL -- TOTAL_PLAN_QUANTITY
,NULL -- AVERAGE_DISCOUNT_PERCENTAGE
,NULL -- TOTAL_BORROWED_REVENUE
,NULL -- TOTAL_TP_REVENUE_IN
,NULL -- TOTAL_TP_REVENUE_OUT
,NULL -- TOTAL_REVENUE_ADJ
,NULL -- TOTAL_LENT_RESOURCE_COST
,NULL -- TOTAL_TP_COST_IN
,NULL -- TOTAL_TP_COST_OUT
,NULL -- TOTAL_COST_ADJ
,NULL -- TOTAL_UNASSIGNED_TIME_COST
,NULL -- TOTAL_UTILIZATION_PERCENT
,NULL -- TOTAL_UTILIZATION_HOURS
,NULL -- TOTAL_UTILIZATION_ADJ
,NULL -- TOTAL_CAPACITY
,NULL -- TOTAL_HEAD_COUNT
,NULL -- TOTAL_HEAD_COUNT_ADJ
,'USER_ENTERED' -- RESOURCE_ASSIGNMENT_TYPE
,NULL -- TOTAL_PROJECT_RAW_COST
,NULL -- TOTAL_PROJECT_BURDENED_COST
,NULL -- TOTAL_PROJECT_REVENUE
,NULL -- PARENT_ASSIGNMENT_ID
,decode(p_one_to_one_mapping_flag,'Y',l_task_elem_rlm_tbl(j)
,p_task_elem_version_id_tbl(i)) -- WBS_ELEMENT_VERSION_ID
,l_rbs_element_id_tbl(j) -- RBS_ELEMENT_ID
,l_planning_start_date_tbl(j) -- PLANNING_START_DATE
,l_planning_end_date_tbl(j) -- PLANNING_END_DATE
,l_schedule_start_date_tbl(j) -- SCHEDULE_START_DATE
,l_schedule_end_date_tbl(j) -- SCHEDULE_END_DATE
,l_spread_curve_id_tbl(j) -- SPREAD_CURVE_ID
,l_etc_method_code_tbl(j) -- ETC_METHOD_CODE
,l_res_type_code_tbl(j) -- RES_TYPE_CODE
,l_attribute_category_tbl(j) -- ATTRIBUTE_CATEGORY
,l_ATTRIBUTE1_tbl(j) -- ATTRIBUTE1
,l_ATTRIBUTE2_tbl(j) -- ATTRIBUTE2
,l_ATTRIBUTE3_tbl(j) -- ATTRIBUTE3
,l_ATTRIBUTE4_tbl(j) -- ATTRIBUTE4
,l_ATTRIBUTE5_tbl(j) -- ATTRIBUTE5
,l_ATTRIBUTE6_tbl(j) -- ATTRIBUTE6
,l_ATTRIBUTE7_tbl(j) -- ATTRIBUTE7
,l_ATTRIBUTE8_tbl(j) -- ATTRIBUTE8
,l_ATTRIBUTE9_tbl(j) -- ATTRIBUTE9
,l_ATTRIBUTE10_tbl(j) -- ATTRIBUTE10
,l_ATTRIBUTE11_tbl(j) -- ATTRIBUTE11
,l_ATTRIBUTE12_tbl(j) -- ATTRIBUTE12
,l_ATTRIBUTE13_tbl(j) -- ATTRIBUTE13
,l_ATTRIBUTE14_tbl(j) -- ATTRIBUTE14
,l_ATTRIBUTE15_tbl(j) -- ATTRIBUTE15
,l_ATTRIBUTE16_tbl(j) -- ATTRIBUTE16
,l_ATTRIBUTE17_tbl(j) -- ATTRIBUTE17
,l_ATTRIBUTE18_tbl(j) -- ATTRIBUTE18
,l_ATTRIBUTE19_tbl(j) -- ATTRIBUTE19
,l_ATTRIBUTE20_tbl(j) -- ATTRIBUTE20
,l_ATTRIBUTE21_tbl(j) -- ATTRIBUTE21
,l_ATTRIBUTE22_tbl(j) -- ATTRIBUTE22
,l_ATTRIBUTE23_tbl(j) -- ATTRIBUTE23
,l_ATTRIBUTE24_tbl(j) -- ATTRIBUTE24
,l_ATTRIBUTE25_tbl(j) -- ATTRIBUTE25
,l_ATTRIBUTE26_tbl(j) -- ATTRIBUTE26
,l_ATTRIBUTE27_tbl(j) -- ATTRIBUTE27
,l_ATTRIBUTE28_tbl(j) -- ATTRIBUTE28
,l_ATTRIBUTE29_tbl(j) -- ATTRIBUTE29
,l_ATTRIBUTE30_tbl(j) -- ATTRIBUTE30
,l_fc_res_type_code_tbl(j) -- FC_RES_TYPE_CODE
,l_resource_class_code_tbl(j) -- RESOURCE_CLASS_CODE
,l_organization_id_tbl(j) -- ORGANIZATION_ID
,l_job_id_tbl(j) -- JOB_ID
,l_person_id_tbl(j) -- PERSON_ID
,l_expenditure_type_tbl(j) -- EXPENDITURE_TYPE
,l_expenditure_category_tbl(j) -- EXPENDITURE_CATEGORY
,l_revenue_category_code_tbl(j) -- REVENUE_CATEGORY_CODE
,l_event_type_tbl(j) -- EVENT_TYPE
,l_supplier_id_tbl(j) -- SUPPLIER_ID
,l_non_labor_resource_tbl(j) -- NON_LABOR_RESOURCE
,l_bom_resource_id_tbl(j) -- BOM_RESOURCE_ID
,l_inventory_item_id_tbl(j) -- INVENTORY_ITEM_ID
,l_item_category_id_tbl(j) -- ITEM_CATEGORY_ID
,1 -- RECORD_VERSION_NUMBER
,l_billable_percent_tbl(j) -- BILLABLE_PERCENT
,NULL -- TRANSACTION_SOURCE_CODE
,l_mfc_cost_type_id_tbl(j) -- MFC_COST_TYPE_ID
,l_procure_resource_flag_tbl(j) -- PROCURE_RESOURCE_FLAG
,l_assignment_description_tbl(j) -- ASSIGNMENT_DESCRIPTION
,l_incurred_by_res_flag_tbl(j) -- INCURRED_BY_RES_FLAG
,NULL -- RATE_JOB_ID
,l_rate_expenditure_type_tbl(j) -- RATE_EXPENDITURE_TYPE
,'Y' -- TA_DISPLAY_FLAG
,decode(l_spread_curve_id_tbl(j),l_fixed_date_sp_id,nvl(l_sp_fixed_date_tbl(j),l_planning_start_date_tbl(j)),null) -- SP_FIXED_DATE -- Bug 3607061
,l_person_type_code_tbl(j) -- PERSON_TYPE_CODE
,l_rate_based_flag_tbl(j) -- RATE_BASED_FLAG
,l_use_task_schedule_flag_tbl(j) -- USE_TASK_SCHEDULE_FLAG
,l_rate_func_curr_code_tbl(j) -- RATE_EXP_FUNC_CURR_CODE
,l_org_id_tbl(j) -- RATE_EXPENDITURE_ORG_ID
,l_incur_by_res_class_code_tbl(j) -- INCUR_BY_RES_CLASS_CODE
,l_incur_by_role_id_tbl(j) -- INCUR_BY_ROLE_ID
,l_project_role_id_tbl(j) -- PROJECT_ROLE_ID
,l_resource_class_flag_tbl(j) -- RESOURCE_CLASS_FLAG
,l_named_role_tbl(j) -- NAMED_ROLE
,l_txn_accum_header_id_tbl(j) -- TXN ACCUM HEADER ID
,l_unplanned_flag_tbl(j) -- UNPLANNED_FLAG
,l_pm_product_code_tbl(j) -- PM_PRODUCT_CODE
,l_pm_res_asgmt_ref_tbl(j) -- PM_RES_ASSIGNMENT_REFERENCE
,l_scheduled_delay(j) -- SCHEDULED_DELAY. For bug 3948128
,l_rate_based_flag_tbl(j) -- resource_RATE_BASED_FLAG
)
RETURNING resource_assignment_id
BULK COLLECT INTO l_res_assignment_id_temp_tbl ;
pa_debug.g_err_stage:='BULK INSERTING DATA - TASK Setting DATA - raid count : '||l_res_assignment_id_temp_tbl.COUNT;
pa_debug.g_err_stage:='BULK INSERTING DATA - TASK spread amount flag';
bulk insert into PA_RESOURCE_ASSIGNMENT.
However If quantity/amounts are passed for the planning elment which has
been skipped for Insert. Calculate API would still be called for it.
As of Now Version - 115.122, the usage of p_skip_duplicate_flags is from
1) Add task and resource page. When Resources are selected to be added as
planning elements for multiple tasks. One or Resource Assignments would
already exists and Insert in RA table would have to be skipped.
But in this case Quantities/Amounts are not passed so Calculate API is
not getting called.
2) Edit Plan Page "Add Another Row" feature.
Consider the following input data.
1) t1 r1 c1
2) t1 r1 c2
3) t2 r2 c2
4) t3 r3 c2
5) t4 r4 c4
System State is such that RA Already Exists for
1) t1 r1
2) t3 r3
In this case Only the following RAs will be inserted.
1)t2 r2 and
3)t4 r4
Basically records 1)2) and 4) have to be skipped.
However calculate API still Will be called for All the 5 records.
This will be taken care by using save exceptions clause in the FORALL Insert below
*/
----------------------------------------------------------------
-----------------------------------------------------------------
-- The Insert Statement below has been modified for changes due to
-- Bug 3719918. When p_one_to_one_mapping_flag is passed as Y for
-- Bugdet/Forecast context. The Bulk insert is run once once for
-- the same index as of rlm_ids.
-- The Exit Condition below takes care of the insert running only once.
-------------------------------------------------------------------
BEGIN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='BULK INSERTING DATA - Context TASK p_context : '||p_context;
INSERT INTO PA_RESOURCE_ASSIGNMENTS (
RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
,TOTAL_LENT_RESOURCE_COST,TOTAL_TP_COST_IN,TOTAL_TP_COST_OUT,TOTAL_COST_ADJ,TOTAL_UNASSIGNED_TIME_COST
,TOTAL_UTILIZATION_PERCENT,TOTAL_UTILIZATION_HOURS,TOTAL_UTILIZATION_ADJ,TOTAL_CAPACITY,TOTAL_HEAD_COUNT
,TOTAL_HEAD_COUNT_ADJ,RESOURCE_ASSIGNMENT_TYPE,TOTAL_PROJECT_RAW_COST,TOTAL_PROJECT_BURDENED_COST,TOTAL_PROJECT_REVENUE
,PARENT_ASSIGNMENT_ID,WBS_ELEMENT_VERSION_ID,RBS_ELEMENT_ID,PLANNING_START_DATE,PLANNING_END_DATE,SCHEDULE_START_DATE,SCHEDULE_END_DATE
,SPREAD_CURVE_ID,ETC_METHOD_CODE,RES_TYPE_CODE,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25
,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,ATTRIBUTE29,ATTRIBUTE30,FC_RES_TYPE_CODE,RESOURCE_CLASS_CODE,ORGANIZATION_ID,JOB_ID
,PERSON_ID,EXPENDITURE_TYPE,EXPENDITURE_CATEGORY,REVENUE_CATEGORY_CODE,EVENT_TYPE,SUPPLIER_ID,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID,INVENTORY_ITEM_ID,ITEM_CATEGORY_ID,RECORD_VERSION_NUMBER,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE,MFC_COST_TYPE_ID,PROCURE_RESOURCE_FLAG,ASSIGNMENT_DESCRIPTION
,INCURRED_BY_RES_FLAG,RATE_JOB_ID,RATE_EXPENDITURE_TYPE,TA_DISPLAY_FLAG
,SP_FIXED_DATE,PERSON_TYPE_CODE,RATE_BASED_FLAG,USE_TASK_SCHEDULE_FLAG,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID
,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE, resource_rate_based_flag)
VALUES
( pa_resource_assignments_s.nextval -- RESOURCE_ASSIGNMENT_ID
,l_budget_version_id -- BUDGET_VERSION_ID
,p_project_id -- PROJECT_ID
,decode(p_one_to_one_mapping_flag,'Y',l_bf_proj_elem_tbl(j)
,l_proj_element_id_tbl(i)) -- TASK_ID
,l_eligible_rlm_ids_tbl(j) -- RESOURCE_LIST_MEMBER_ID
,sysdate -- LAST_UPDATE_DATE
,fnd_global.user_id -- LAST_UPDATED_BY
,sysdate -- CREATION_DATE
,fnd_global.user_id -- CREATED_BY
,fnd_global.login_id -- LAST_UPDATE_LOGIN
,l_unit_of_measure_tbl(j) -- UNIT_OF_MEASURE
,NULL -- TRACK_AS_LABOR_FLAG
,NULL -- STANDARD_BILL_RATE
,NULL -- AVERAGE_BILL_RATE
,NULL -- AVERAGE_COST_RATE
,-1 -- PROJECT_ASSIGNMENT_ID
,NULL -- PLAN_ERROR_CODE
,NULL -- TOTAL_PLAN_REVENUE
,NULL -- TOTAL_PLAN_RAW_COST
,NULL -- TOTAL_PLAN_BURDENED_COST
,NULL -- TOTAL_PLAN_QUANTITY
,NULL -- AVERAGE_DISCOUNT_PERCENTAGE
,NULL -- TOTAL_BORROWED_REVENUE
,NULL -- TOTAL_TP_REVENUE_IN
,NULL -- TOTAL_TP_REVENUE_OUT
,NULL -- TOTAL_REVENUE_ADJ
,NULL -- TOTAL_LENT_RESOURCE_COST
,NULL -- TOTAL_TP_COST_IN
,NULL -- TOTAL_TP_COST_OUT
,NULL -- TOTAL_COST_ADJ
,NULL -- TOTAL_UNASSIGNED_TIME_COST
,NULL -- TOTAL_UTILIZATION_PERCENT
,NULL -- TOTAL_UTILIZATION_HOURS
,NULL -- TOTAL_UTILIZATION_ADJ
,NULL -- TOTAL_CAPACITY
,NULL -- TOTAL_HEAD_COUNT
,NULL -- TOTAL_HEAD_COUNT_ADJ
,'USER_ENTERED' -- RESOURCE_ASSIGNMENT_TYPE
,NULL -- TOTAL_PROJECT_RAW_COST
,NULL -- TOTAL_PROJECT_BURDENED_COST
,NULL -- TOTAL_PROJECT_REVENUE
,NULL -- PARENT_ASSIGNMENT_ID
,NULL -- WBS_ELEMENT_VERSION_ID --Bug 3546208
,l_rbs_element_id_tbl(j) -- RBS_ELEMENT_ID
,decode(p_one_to_one_mapping_flag,'Y',l_bf_start_date_tbl(j)
,l_start_date_tbl(i)) -- PLANNING_START_DATE
,decode(p_one_to_one_mapping_flag,'Y',l_bf_compl_date_tbl(j)
,l_compl_date_tbl(i)) -- PLANNING_END_DATE
,decode(p_one_to_one_mapping_flag,'Y',l_bf_start_date_tbl(j)
,l_start_date_tbl(i)) -- SCHEDULE_START_DATE
,decode(p_one_to_one_mapping_flag,'Y',l_bf_compl_date_tbl(j)
,l_compl_date_tbl(i)) -- SCHEDULE_END_DATE
,l_spread_curve_id_tbl(j) -- SPREAD_CURVE_ID
,l_etc_method_code_tbl(j) -- ETC_METHOD_CODE
,l_res_type_code_tbl(j) -- RES_TYPE_CODE
,NULL -- ATTRIBUTE_CATEGORY
,NULL -- ATTRIBUTE1
,NULL -- ATTRIBUTE2
,NULL -- ATTRIBUTE3
,NULL -- ATTRIBUTE4
,NULL -- ATTRIBUTE5
,NULL -- ATTRIBUTE6
,NULL -- ATTRIBUTE7
,NULL -- ATTRIBUTE8
,NULL -- ATTRIBUTE9
,NULL -- ATTRIBUTE10
,NULL -- ATTRIBUTE11
,NULL -- ATTRIBUTE12
,NULL -- ATTRIBUTE13
,NULL -- ATTRIBUTE14
,NULL -- ATTRIBUTE15
,NULL -- ATTRIBUTE16
,NULL -- ATTRIBUTE17
,NULL -- ATTRIBUTE18
,NULL -- ATTRIBUTE19
,NULL -- ATTRIBUTE20
,NULL -- ATTRIBUTE21
,NULL -- ATTRIBUTE22
,NULL -- ATTRIBUTE23
,NULL -- ATTRIBUTE24
,NULL -- ATTRIBUTE25
,NULL -- ATTRIBUTE26
,NULL -- ATTRIBUTE27
,NULL -- ATTRIBUTE28
,NULL -- ATTRIBUTE29
,NULL -- ATTRIBUTE30
,l_fc_res_type_code_tbl(j) -- FC_RES_TYPE_CODE
,l_resource_class_code_tbl(j) -- RESOURCE_CLASS_CODE
,l_organization_id_tbl(j) -- ORGANIZATION_ID
,l_job_id_tbl(j) -- JOB_ID
,l_person_id_tbl(j) -- PERSON_ID
,l_expenditure_type_tbl(j) -- EXPENDITURE_TYPE
,l_expenditure_category_tbl(j) -- EXPENDITURE_CATEGORY
,l_revenue_category_code_tbl(j) -- REVENUE_CATEGORY_CODE
,l_event_type_tbl(j) -- EVENT_TYPE
,l_supplier_id_tbl(j) -- SUPPLIER_ID
,l_non_labor_resource_tbl(j) -- NON_LABOR_RESOURCE
,l_bom_resource_id_tbl(j) -- BOM_RESOURCE_ID
,l_inventory_item_id_tbl(j) -- INVENTORY_ITEM_ID
,l_item_category_id_tbl(j) -- ITEM_CATEGORY_ID
,1 -- RECORD_VERSION_NUMBER
,NULL -- BILLABLE_PERCENT
,NULL -- TRANSACTION_SOURCE_CODE
,l_mfc_cost_type_id_tbl(j) -- MFC_COST_TYPE_ID
,NULL -- PROCURE_RESOURCE_FLAG
,NULL -- ASSIGNMENT_DESCRIPTION
,l_incurred_by_res_flag_tbl(j) -- INCURRED_BY_RES_FLAG
,NULL -- RATE_JOB_ID
,l_rate_expenditure_type_tbl(j) -- RATE_EXPENDITURE_TYPE
,NULL -- TA_DISPLAY_FLAG
,decode(p_one_to_one_mapping_flag,'Y',decode(l_spread_curve_id_tbl(j),l_fixed_date_sp_id,l_bf_start_date_tbl(j),null)
,decode(l_spread_curve_id_tbl(j),l_fixed_date_sp_id,l_start_date_tbl(i),null)) -- SP_FIXED_DATE -- Bug 3607061
,l_person_type_code_tbl(j) -- PERSON_TYPE_CODE
,l_rate_based_flag_tbl(j) -- RATE_BASED_FLAG
,l_use_task_schedule_flag_tbl(j) -- USE_TASK_SCHEDULE_FLAG
,l_rate_func_curr_code_tbl(j) -- RATE_EXP_FUNC_CURR_CODE
,l_org_id_tbl(j) -- RATE_EXPENDITURE_ORG_ID
,l_incur_by_res_class_code_tbl(j) -- INCUR_BY_RES_CLASS_CODE
,l_incur_by_role_id_tbl(j) -- INCUR_BY_ROLE_ID
,l_project_role_id_tbl(j) -- PROJECT_ROLE_ID
,l_resource_class_flag_tbl(j) -- RESOURCE_CLASS_FLAG
,l_named_role_tbl(j) -- NAMED_ROLE
,l_txn_accum_header_id_tbl(j) -- TXN ACCUM HEADER ID
,l_pm_product_code_tbl(j) -- PM_PRODUCT_CODE
,l_pm_res_asgmt_ref_tbl(j) -- PM_RES_ASSIGNMENT_REFERENCE
,l_rate_based_flag_tbl(j) -- resource_RATE_BASED_FLAG
)
RETURNING
task_id,
resource_list_member_id,
resource_assignment_id,
l_bf_quantity_tbl(j),
l_bf_raw_cost_tbl(j),
l_bf_burdened_cost_tbl(j),
l_bf_revenue_tbl(j),
l_bf_currency_code_tbl(j),
l_bf_cost_rate_tbl(j),
l_bf_bill_rate_tbl(j),
l_bf_burdened_rate_tbl(j)
BULK COLLECT INTO
l_bf_task_id_tbl,
l_bf_rlm_id_tbl,
l_bf_ra_id_tbl,
l_bf_ins_quantity_tbl,
l_bf_ins_raw_cost_tbl,
l_bf_ins_burdened_cost_tbl,
l_bf_ins_revenue_tbl,
l_bf_ins_currency_code_tbl,
l_bf_ins_cost_rate_tbl,
l_bf_ins_bill_rate_tbl,
l_bf_ins_burdened_rate_tbl;
pa_debug.g_err_stage:='No of inserted records + No. of errored records is not equal to total no. of input records';
SELECT task_id,
resource_list_member_id,
resource_assignment_id,
l_bf_quantity_tbl(l_temp),
l_bf_raw_cost_tbl(l_temp),
l_bf_burdened_cost_tbl(l_temp),
l_bf_revenue_tbl(l_temp),
l_bf_currency_code_tbl(l_temp),
l_bf_cost_rate_tbl(l_temp),
l_bf_bill_rate_tbl(l_temp),
l_bf_burdened_rate_tbl(l_temp)
INTO l_bf_task_id_tbl(l_index+kk),
l_bf_rlm_id_tbl(l_index+kk),
l_bf_ra_id_tbl(l_index+kk),
l_bf_ins_quantity_tbl(l_index+kk),
l_bf_ins_raw_cost_tbl(l_index+kk),
l_bf_ins_burdened_cost_tbl(l_index+kk),
l_bf_ins_revenue_tbl(l_index+kk),
l_bf_ins_currency_code_tbl(l_index+kk),
l_bf_ins_cost_rate_tbl(l_index+kk),
l_bf_ins_bill_rate_tbl(l_index+kk),
l_bf_ins_burdened_rate_tbl(l_index+kk)
FROM pa_resource_assignments
WHERE project_id=p_project_id
AND budget_version_id=l_budget_version_id
AND task_id =l_bf_proj_elem_tbl(l_temp)
AND resource_list_member_id=l_eligible_rlm_ids_tbl(l_temp)
AND project_assignment_id=-1;
pa_debug.g_err_stage:='INSIDE Bulk Data insert for budget/forecast';
l_res_assignment_id_tbl.delete(l_index,l_res_assignment_id_tbl.count);
l_delete_budget_lines_tbl.delete(l_index,l_delete_budget_lines_tbl.count);
l_spread_amount_flags_tbl.delete(l_index,l_spread_amount_flags_tbl.count);
l_currency_code_tbl.delete(l_index,l_currency_code_tbl.count);
l_quantity_tbl.delete(l_index,l_quantity_tbl.count);
l_raw_cost_tbl.delete(l_index,l_raw_cost_tbl.count);
l_burdened_cost_tbl.delete(l_index,l_burdened_cost_tbl.count);
l_revenue_tbl.delete(l_index,l_revenue_tbl.count);
l_cost_rate_tbl.delete(l_index,l_cost_rate_tbl.count);
l_burden_multiplier_tbl.delete(l_index,l_burden_multiplier_tbl.count);
l_bill_rate_tbl.delete(l_index,l_bill_rate_tbl.count);
l_line_start_date_tbl.delete(l_index,l_line_start_date_tbl.count);
l_line_end_date_tbl.delete(l_index,l_line_end_date_tbl.count);
,p_delete_budget_lines_tab => l_delete_budget_lines_tbl
-- bug fix:5726773,p_spread_amts_flag_tab => l_spread_amount_flags_tbl
,p_txn_currency_code_tab => l_currency_code_tbl -- derive
-- as told by sanjay ,p_txn_currency_override_tab => l_currency_code_tbl
,p_total_qty_tab => l_quantity_tbl -- derive
,p_total_raw_cost_tab => l_raw_cost_tbl -- dervie
,p_total_burdened_cost_tab => l_burdened_cost_tbl -- dervie
,p_total_revenue_tab => l_revenue_tbl -- derive
,p_raw_cost_rate_tab => l_cost_rate_tbl -- derive
,p_rw_cost_rate_override_tab => l_cost_rate_tbl
,p_b_cost_rate_tab => l_burden_multiplier_tbl -- derive
,p_b_cost_rate_override_tab => l_burden_multiplier_tbl
,p_bill_rate_tab => l_bill_rate_tbl -- derive
,p_bill_rate_override_tab => l_bill_rate_tbl
,p_line_start_date_tab => l_line_start_date_tbl --PA_PLSQL_DATATYPES.EmptyDateTab
,p_line_end_date_tab => l_line_end_date_tbl --PA_PLSQL_DATATYPES.EmptyDateTab
,p_apply_progress_flag => p_apply_progress_flag
,p_rollup_required_flag => l_pji_rollup_required --Bug 4200168
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
( p_calling_context => 'ASGMT_PLAN_CHANGE'
,p_project_id => p_project_id
,p_structure_version_id => pa_project_structure_utils.get_latest_wp_version(p_project_id)
,p_pub_struc_ver_id => pa_project_structure_utils.get_latest_wp_version(p_project_id)
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
pa_debug.g_err_stage:='Called API PA_PROJ_TASK_STRUC_PUB.process_wbs_updates_wrp';
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_ra_id_tbl => l_ra_id_temp_tbl,
p_curr_code_tbl => l_curr_code_temp_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
delete pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
,DELETE_FLAG
,TXN_RAW_COST_RATE_OVERRIDE -- 6839167
,TXN_BURDEN_COST_RATE_OVERRIDE
,TXN_BILL_RATE_OVERRIDE
)
SELECT pa_resource_asgn_curr_s.nextval
,l_ra_id_temp_tbl(i)
,l_curr_code_temp_tbl(i)
,NULL
,prac.TXN_RAW_COST_RATE_OVERRIDE --6839167
,prac.TXN_BURDEN_COST_RATE_OVERRIDE
,prac.TXN_BILL_RATE_OVERRIDE
from pa_resource_asgn_curr prac
where prac.RESOURCE_ASSIGNMENT_ID=l_ra_id_temp_tbl(i);
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => 'N',
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => 'Y',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
/*This procedure should be called to update planning transactions
valid values for p_context are 'BUDGET' , 'FORECAST', 'WORKPLAN' and 'TASK_ASSIGNMENT'
*/
/*******************************************************************************************************
As part of Bug 3749516 All References to Equipment Effort or Equip Resource Class has been removed in
PROCEDURE update_planning_transactions.
All _addl_ and p_equip_people_effort_tbl IN parameters have also been removed as they were not being
used/referred.
********************************************************************************************************/
PROCEDURE update_planning_transactions
(
p_context IN VARCHAR2
,p_calling_context IN VARCHAR2 DEFAULT NULL -- Added for Bug 6856934
,p_struct_elem_version_id IN Pa_proj_element_versions.element_version_id%TYPE
,p_budget_version_id IN Pa_budget_versions.budget_version_id%TYPE
,p_task_elem_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_task_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE
,p_task_number_tbl IN SYSTEM.PA_VARCHAR2_100_TBL_TYPE
,p_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_planned_people_effort_tbl IN SYSTEM.PA_NUM_TBL_TYPE
-- One pl/sql record in The Above tables
,p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_resource_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_assignment_description_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE
,p_project_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
,p_resource_alias_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE
,p_resource_class_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE
,p_resource_class_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_resource_class_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_res_type_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_resource_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_resource_name IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE -- bug fix 3461537
,p_person_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_job_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_person_type_code IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_bom_resource_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_non_labor_resource_tbl IN SYSTEM.PA_VARCHAR2_20_TBL_TYPE
,p_inventory_item_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_item_category_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_project_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_project_role_name_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_organization_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_organization_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE
,p_fc_res_type_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_financial_category_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_expenditure_category_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_event_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_revenue_category_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_supplier_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_unit_of_measure_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_spread_curve_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_etc_method_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_mfc_cost_type_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_procure_resource_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE
,p_incurred_by_res_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE
,p_incur_by_resource_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_incur_by_resource_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE
,p_incur_by_res_class_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_incur_by_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_use_task_schedule_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE
,p_planning_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_planning_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_schedule_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_schedule_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
,p_txn_currency_override_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
,p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_revenue_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_cost_rate_override_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_burdened_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_burdened_rate_override_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_bill_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_bill_rate_override_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_billable_percent_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_sp_fixed_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_named_role_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE
,p_financial_category_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE
,p_supplier_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE
,p_attribute_category_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_attribute1_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute2_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute3_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute4_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute5_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute6_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute7_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute8_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute9_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute10_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute11_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute12_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute13_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute14_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute15_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute16_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute17_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute18_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute19_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute20_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute21_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute22_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute23_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute24_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute25_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute26_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute27_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute28_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute29_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_attribute30_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
,p_apply_progress_flag IN VARCHAR2 /* Passed from apply_progress api (sakthi's team) */
,p_scheduled_delay IN SYSTEM.pa_num_tbl_type --For bug 3948128
,p_pji_rollup_required IN VARCHAR2 DEFAULT 'Y' /* Bug# 4200168 */
,p_upd_cost_amts_too_for_ta_flg IN VARCHAR2 DEFAULT 'N' --Added for bug #4538286
,p_distrib_amts IN VARCHAR2 DEFAULT 'Y' -- Bug 5684639.
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(2000);
l_module_name VARCHAR2(100) := 'Update_Planning_Transactions' || 'pa.plsql.pa_fp_planning_transaction_pub';
l_delete_budget_lines_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
pa_debug.set_curr_function( p_function => 'Update_Planning_Transactions',
p_debug_mode => l_debug_mode );
SAVEPOINT Update_Planning_Transactions;
SELECT project_id
INTO l_project_id
FROM pa_struct_task_wbs_v
WHERE parent_Structure_version_id=p_struct_elem_version_id
AND ROWNUM=1;
SELECT project_id
INTO l_project_id
FROM pa_budget_versions
WHERE budget_version_id = l_budget_version_id;
pa_debug.g_err_stage:='Select failed on pa_budget_versions.';
pa_debug.write('PA_FP_PLANNING_TXN_PUB.update_planning_transactions:'||l_module_name,pa_debug.g_err_stage,3);
* If not present, the called procedure would insert it.
*/
--dbms_output.put_line ('pq1 is '||p_planned_people_effort_tbl(1));
Select spread_curve_id
into l_fixed_date_sp_id
from pa_spread_curves_b
where spread_curve_code = 'FIXED_DATE';
SELECT wbs_element_version_id
INTO l_resource_rec_tbl(i).wbs_element_version_id
FROM pa_resource_assignments
WHERE resource_assignment_id = l_resource_assignment_id_tbl(i);
pa_debug.g_err_stage:='Calling API pa_task_assignment_utils.Validate_Update_Assignment';
PA_TASK_ASSIGNMENT_UTILS.Validate_Update_Assignment(
p_calling_context => p_calling_context, -- Added for Bug 6856934
p_task_assignment_tbl => l_resource_rec_tbl,
x_return_status => l_return_status);
pa_debug.g_err_stage:='Called API PA_TASK_ASSIGNMENT_UTILS.Validate_Update_Assignment returned error';
SELECT NVL(pbl.txn_currency_code,l_existing_curr_code)
INTO l_currency_code_tbl(i)
FROM pa_resource_assignments b,
(SELECT pbl.txn_currency_code,
pra.resource_assignment_id
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pbl.resource_assignment_id(+)=pra.resource_assignment_id
AND pra.resource_assignment_id=l_resource_assignment_id_tbl(i)
AND ROWNUM=1) pbl
WHERE b.resource_assignment_id=l_resource_assignment_id_tbl(i);*/
SELECT NVL(rac.txn_currency_code,l_existing_curr_code)
INTO l_currency_code_tbl(i)
FROM pa_resource_asgn_curr rac,
pa_resource_assignments pra
WHERE rac.resource_assignment_id(+)=pra.resource_assignment_id
AND pra.resource_assignment_id=l_resource_assignment_id_tbl(i);
UPDATE PA_RESOURCE_ASSIGNMENTS
SET resource_list_member_id = DECODE (l_resource_list_member_id_tbl(i),l_g_miss_num,null,nvl(l_resource_list_member_id_tbl(i),resource_list_member_id))
,last_update_date = sysdate
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
,unit_of_measure = DECODE (l_unit_of_measure_tbl(i), l_g_miss_char,null,nvl( l_unit_of_measure_tbl(i),unit_of_measure))
,project_assignment_id = DECODE (l_project_assignment_id_tbl(i), l_g_miss_num,null,nvl(l_project_assignment_id_tbl(i),project_assignment_id))
,planning_start_date = DECODE (l_planning_start_date_tbl(i), l_g_miss_date,null,nvl(l_planning_start_date_tbl(i),planning_start_date))
,planning_end_date = DECODE (l_planning_end_date_tbl(i), l_g_miss_date,null,nvl(l_planning_end_date_tbl(i),planning_end_date))
,schedule_start_date = DECODE (l_schedule_start_date_tbl(i), l_g_miss_date,null,nvl(l_schedule_start_date_tbl(i),schedule_start_date))
,schedule_end_date = DECODE (l_schedule_end_date_tbl(i), l_g_miss_date,null,nvl(l_schedule_end_date_tbl(i),schedule_end_date))
,spread_curve_id = DECODE (l_spread_curve_id_tbl(i), l_g_miss_num,null,nvl(l_spread_curve_id_tbl(i),spread_curve_id ))
,etc_method_code = DECODE (l_etc_method_code_tbl(i), l_g_miss_char,null,nvl(l_etc_method_code_tbl(i),etc_method_code))
,res_type_code = DECODE (l_res_type_code_tbl(i), l_g_miss_char,null,nvl(l_res_type_code_tbl(i),res_type_code))
,attribute_category = DECODE (l_attribute_category_tbl(i), l_g_miss_char,null,nvl(l_attribute_category_tbl(i),attribute_category))
,attribute1 = DECODE (l_attribute1_tbl(i), l_g_miss_char,null,nvl(l_attribute1_tbl(i),attribute1))
,attribute2 = DECODE (l_attribute2_tbl(i), l_g_miss_char,null,nvl(l_attribute2_tbl(i),attribute2))
,attribute3 = DECODE (l_attribute3_tbl(i), l_g_miss_char,null,nvl(l_attribute3_tbl(i),attribute3 ))
,attribute4 = DECODE (l_attribute4_tbl(i), l_g_miss_char,null,nvl(l_attribute4_tbl(i),attribute4))
,attribute5 = DECODE (l_attribute5_tbl(i), l_g_miss_char,null,nvl(l_attribute5_tbl(i),attribute5 ))
,attribute6 = DECODE (l_attribute6_tbl(i), l_g_miss_char,null,nvl(l_attribute6_tbl(i),attribute6 ))
,attribute7 = DECODE (l_attribute7_tbl(i), l_g_miss_char,null,nvl(l_attribute7_tbl(i),attribute7))
,attribute8 = DECODE (l_attribute8_tbl(i), l_g_miss_char,null,nvl(l_attribute8_tbl(i),attribute8))
,attribute9 = DECODE (l_attribute9_tbl(i), l_g_miss_char,null,nvl(l_attribute9_tbl(i),attribute9))
,attribute10 = DECODE (l_attribute10_tbl(i), l_g_miss_char,null, nvl(l_attribute10_tbl(i),attribute10))
,attribute11 = DECODE (l_attribute11_tbl(i), l_g_miss_char,null,nvl(l_attribute11_tbl(i),attribute11))
,attribute12 = DECODE (l_attribute12_tbl(i), l_g_miss_char,null,nvl(l_attribute12_tbl(i),attribute12))
,attribute13 = DECODE (l_attribute13_tbl(i), l_g_miss_char,null,nvl(l_attribute13_tbl(i),attribute13))
,attribute14 = DECODE (l_attribute14_tbl(i), l_g_miss_char,null,nvl(l_attribute14_tbl(i),attribute14)) -- for bug 6944671
,attribute15 = DECODE (l_attribute15_tbl(i), l_g_miss_char,null,nvl(l_attribute15_tbl(i),attribute15))
,attribute16 = DECODE (l_attribute16_tbl(i), l_g_miss_char,null,nvl(l_attribute16_tbl(i),attribute16))
,attribute17 = DECODE (l_attribute17_tbl(i), l_g_miss_char,null,nvl(l_attribute17_tbl(i),attribute17))
,attribute18 = DECODE (l_attribute18_tbl(i), l_g_miss_char,null,nvl(l_attribute18_tbl(i),attribute18))
,attribute19 = DECODE (l_attribute19_tbl(i), l_g_miss_char,null,nvl(l_attribute19_tbl(i),attribute19))
,attribute20 = DECODE (l_attribute20_tbl(i), l_g_miss_char,null,nvl(l_attribute20_tbl(i),attribute20))
,attribute21 = DECODE (l_attribute21_tbl(i), l_g_miss_char,null,nvl(l_attribute21_tbl(i),attribute21))
,attribute22 = DECODE (l_attribute22_tbl(i), l_g_miss_char,null,nvl(l_attribute22_tbl(i),attribute22))
,attribute23 = DECODE (l_attribute23_tbl(i), l_g_miss_char,null,nvl(l_attribute23_tbl(i),attribute23))
,attribute24 = DECODE (l_attribute24_tbl(i), l_g_miss_char,null,nvl(l_attribute24_tbl(i),attribute24))
,attribute25 = DECODE (l_attribute25_tbl(i), l_g_miss_char,null,nvl(l_attribute25_tbl(i),attribute25))
,attribute26 = DECODE (l_attribute26_tbl(i), l_g_miss_char,null,nvl(l_attribute26_tbl(i),attribute26))
,attribute27 = DECODE (l_attribute27_tbl(i), l_g_miss_char,null,nvl(l_attribute27_tbl(i),attribute27))
,attribute28 = DECODE (l_attribute28_tbl(i), l_g_miss_char,null,nvl(l_attribute28_tbl(i),attribute28))
,attribute29 = DECODE (l_attribute29_tbl(i), l_g_miss_char,null,nvl(l_attribute29_tbl(i),attribute29))
,attribute30 = DECODE (l_attribute30_tbl(i), l_g_miss_char,null,nvl(l_attribute30_tbl(i),attribute30))
,fc_res_type_code = DECODE (l_fc_res_type_code_tbl(i), l_g_miss_char,null,nvl(l_fc_res_type_code_tbl(i),fc_res_type_code))
,resource_class_code = DECODE (l_resource_class_code_tbl(i), l_g_miss_char,null,nvl(l_resource_class_code_tbl(i),resource_class_code))
,organization_id = DECODE (l_organization_id_tbl(i), l_g_miss_num,null,nvl(l_organization_id_tbl(i),organization_id))
,job_id = DECODE (l_job_id_tbl(i), l_g_miss_num,null,nvl(l_job_id_tbl(i),job_id))
,person_id = DECODE (l_person_id_tbl(i), l_g_miss_num,null,nvl(l_person_id_tbl(i),person_id))
,expenditure_type = DECODE (l_expenditure_type_tbl(i), l_g_miss_char,null,nvl(l_expenditure_type_tbl(i),expenditure_type))
,expenditure_category = DECODE (l_expenditure_category_tbl(i), l_g_miss_char,null,nvl(l_expenditure_category_tbl(i),expenditure_category))
,revenue_category_code = DECODE (l_revenue_category_code_tbl(i), l_g_miss_char,null,nvl(l_revenue_category_code_tbl(i),revenue_category_code))
,event_type = DECODE (l_event_type_tbl(i), l_g_miss_char,null,nvl(l_event_type_tbl(i),event_type))
,supplier_id = DECODE (l_supplier_id_tbl(i), l_g_miss_num,null,nvl(l_supplier_id_tbl(i),supplier_id))
,non_labor_resource = DECODE (l_non_labor_resource_tbl(i), l_g_miss_char,null,nvl(l_non_labor_resource_tbl(i),non_labor_resource))
,bom_resource_id = DECODE (l_bom_resource_id_tbl(i), l_g_miss_num,null,nvl(l_bom_resource_id_tbl(i),bom_resource_id))
,inventory_item_id = DECODE (l_inventory_item_id_tbl(i), l_g_miss_num,null,nvl(l_inventory_item_id_tbl(i),inventory_item_id))
,item_category_id = DECODE (l_item_category_id_tbl(i), l_g_miss_num,null,nvl(l_item_category_id_tbl(i),item_category_id))
,record_version_number = nvl(record_version_number,0)+1
,billable_percent = DECODE (l_billable_percent_tbl(i), l_g_miss_num,null,nvl(l_billable_percent_tbl(i),billable_percent))
,mfc_cost_type_id = DECODE (l_mfc_cost_type_id_tbl(i), l_g_miss_num,null,nvl(l_mfc_cost_type_id_tbl(i),mfc_cost_type_id ))
,procure_resource_flag = DECODE (l_procure_resource_flag_tbl(i), l_g_miss_char,null,nvl(l_procure_resource_flag_tbl(i),procure_resource_flag))
,assignment_description = DECODE (l_assignment_description_tbl(i), l_g_miss_char,null,nvl(l_assignment_description_tbl(i),assignment_description))
,incurred_by_res_flag = DECODE (l_incurred_by_res_flag_tbl(i), l_g_miss_char,null,nvl(l_incurred_by_res_flag_tbl(i),incurred_by_res_flag))
,sp_fixed_date = DECODE (l_sp_fixed_date_tbl(i), l_g_miss_date,null,nvl(l_sp_fixed_date_tbl(i),sp_fixed_date))
,person_type_code = DECODE (l_person_type_code_tbl(i), l_g_miss_char,null,nvl(l_person_type_code_tbl(i),person_type_code))
,use_task_schedule_flag = DECODE (l_use_task_schedule_flag_tbl(i), l_g_miss_char,null,nvl(l_use_task_schedule_flag_tbl(i),use_task_schedule_flag))
,incur_by_res_class_code = DECODE (l_Incur_by_res_class_code_tbl(i), l_g_miss_char,null,nvl(l_Incur_by_res_class_code_tbl(i),incur_by_res_class_code ))
,incur_by_role_id = DECODE (l_Incur_by_role_id_tbl(i), l_g_miss_num,null,nvl(l_Incur_by_role_id_tbl(i),incur_by_role_id))
,project_role_id = DECODE (l_project_role_id_tbl(i), l_g_miss_num,null,nvl(l_project_role_id_tbl(i),project_role_id))
,resource_class_flag = DECODE (l_resource_class_flag_tbl(i), l_g_miss_char,null,nvl(l_resource_class_flag_tbl(i),resource_class_flag ))
,named_role = DECODE (l_named_role_tbl(i), l_g_miss_char,null,nvl(l_named_role_tbl(i),named_role ))
,rbs_element_id = l_rbs_element_id_tbl(i)
,txn_accum_header_id = l_txn_accum_header_id_tbl(i)
,rate_expenditure_org_id = DECODE (p_context
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK
,DECODE(l_rate_exp_org_id_tbl(i)
,l_g_miss_num
,null
,nvl(l_rate_exp_org_id_tbl(i),rate_expenditure_org_id))
,rate_expenditure_org_id)
,rate_expenditure_type = DECODE (p_context
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK
,DECODE(l_rate_exp_type_tbl(i)
,l_g_miss_char
,null
,nvl(l_rate_exp_type_tbl(i),rate_expenditure_type))
,rate_expenditure_type)
,rate_exp_func_curr_code = DECODE (p_context
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK
,DECODE(l_rate_func_curr_code_tbl(i)
,l_g_miss_char
,null
,nvl(l_rate_func_curr_code_tbl(i),rate_exp_func_curr_code))
,rate_exp_func_curr_code)
,rate_based_flag = DECODE (p_context /* Bug 3678814 */
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK
,DECODE(l_rate_based_flag_tbl(i),
l_g_miss_char,'N',
nvl(l_rate_based_flag_tbl(i),rate_based_flag))
,rate_based_flag)
/* Bug fix:5759413 */
,resource_rate_based_flag = DECODE (p_context,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK
,DECODE(nvl(l_rlm_id_change_flag_tbl(i),'N'), 'N'
,resource_rate_based_flag
,DECODE(l_rate_based_flag_tbl(i),
l_g_miss_char,'N',
nvl(l_rate_based_flag_tbl(i),resource_rate_based_flag)))
,resource_rate_based_flag)
,scheduled_delay = DECODE (p_context /* Bug 3678814 */
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK
,DECODE(l_scheduled_delay(i),
l_g_miss_num,null,
nvl(l_scheduled_delay(i),scheduled_delay))
,scheduled_delay)
WHERE resource_assignment_id= l_resource_assignment_id_tbl(i);
If the user has selected not to distribute the amounts, then
pass the l_plan_end_date_old_tbl as l_plan_end_date_new_tbl AND
l_plan_start_date_old_tbl as l_plan_start_date_new_tbl so that there is
no distribution of amounts as the old and the new dates are the same.
*/
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='p_distrib_amts - '||p_distrib_amts;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_resource_assignment_id_tbl(i)
,l_currency_code_tbl(i)
,NULL
FROM DUAL;
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => 'N',
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => 'Y',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
( p_calling_context => 'ASGMT_PLAN_CHANGE'
,p_project_id => l_project_id
,p_structure_version_id => p_struct_elem_version_id
,p_pub_struc_ver_id => p_struct_elem_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage:='Called API PA_PROJ_TASK_STRUC_PUB.process_wbs_updates_wrp';
pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.update_planning_transactions: ' || g_module_name,pa_debug.g_err_stage,3);
ROLLBACK TO Update_Planning_Transactions;
,p_procedure_name => 'Update_Planning_Transactions');
ROLLBACK TO Update_Planning_Transactions;
END Update_Planning_Transactions;
SELECT pfrmt.source_res_assignment_id
,pfrmt.target_res_assignment_id
FROM pa_fp_ra_map_tmp pfrmt;
SELECT pbv.budget_version_id
INTO l_src_budget_version_id
FROM pa_struct_task_wbs_v pt,
pa_budget_versions pbv
WHERE pbv.project_structure_version_id= pt.parent_structure_version_id
AND pt.element_version_id=p_src_version_id_tbl(1)
AND pbv.wp_version_flag='Y';
SELECT pbv.budget_version_id
INTO l_targ_budget_version_id
FROM pa_budget_versions pbv
,pa_struct_task_wbs_v pt
WHERE pt.element_version_id=p_targ_version_id_tbl(1)
AND pbv.project_structure_version_id=pt.parent_structure_version_id
AND pbv.wp_version_flag='Y';
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
ra.RESOURCE_ASSIGNMENT_ID,
src_rbc.txn_currency_code,
src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
src_rbc.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_asgn_curr src_rbc,
pa_fp_ra_map_tmp map,
--pa_budget_lines bl
pa_resource_assignments ra
WHERE ra.budget_version_id =l_targ_budget_version_id
-- AND src_rbc.budget_version_id = l_src_budget_version_id
AND map.target_res_assignment_id = ra.resource_assignment_id
AND src_rbc.resource_assignment_id = map.source_res_assignment_id;
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_rollup_flag => 'N',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:='About to update named role/parent assignment id in pa_resource_assignments';
SELECT rlm.resource_class_code, rlm.inventory_item_id,
rlm.expenditure_type
INTO l_resource_class_code, l_inv_item_id, l_exp_type
FROM pa_resource_list_members rlm,
pa_resource_assignments ra
WHERE ra.resource_assignment_id = l_ra_id_tbl(i)
AND ra.resource_list_member_id = rlm.resource_list_member_id;
SELECT 'Y'
INTO l_res_rate_based_flag_tbl(i)
FROM DUAL
WHERE NOT EXISTS (select 'Y'
from mtl_system_items_b item,
mtl_units_of_measure meas
where item.inventory_item_id = l_inv_item_id
and item.primary_uom_code = meas.uom_code
and meas.uom_class = 'Currency');
SELECT c.cost_rate_flag
INTO l_res_rate_based_flag_tbl(i)
FROM pa_expenditure_types c
WHERE c.expenditure_type = l_exp_type;
UPDATE pa_resource_assignments
SET named_role = l_named_role_tbl(i),
parent_assignment_id=NULL,
/* bug fix:5135927 : Added nvl for l_res_rate_base_flag */
resource_rate_based_flag =NVL(l_res_rate_based_flag_tbl(i),'N')--IPM
WHERE parent_assignment_id = l_ra_id_tbl(i)
AND budget_version_id = l_targ_budget_version_id;
SELECT nvl(txn_currency_code,l_proj_curr_code)--For workplan txn curr code can be null
INTO l_currency_code_tbl(i)
FROM pa_budget_lines
WHERE resource_assignment_id=
c_calc_api_param_rec.source_res_assignment_id -- bug 3781932 l_calc_ra_id_tbl(i)
AND ROWNUM=1;
l_quantity_tbl.DELETE(i,l_quantity_tbl.count);
l_tot_rc_tbl.DELETE(i,l_tot_rc_tbl.count);
l_currency_code_tbl.DELETE(i,l_currency_code_tbl.count);
l_calc_ra_id_tbl.DELETE(i,l_calc_ra_id_tbl.count);
l_spread_amt_flag_tbl.DELETE(i,l_spread_amt_flag_tbl.count);
,p_delete_budget_lines_tab => SYSTEM.PA_VARCHAR2_1_TBL_TYPE()
-- bug fix:5726773 ,p_spread_amts_flag_tab => l_spread_amt_flag_tbl
,p_txn_currency_code_tab => l_currency_code_tbl
,p_txn_currency_override_tab => SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
,p_total_qty_tab => l_quantity_tbl
,p_total_raw_cost_tab => l_tot_rc_tbl
,p_total_burdened_cost_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_total_revenue_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_raw_cost_rate_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_rw_cost_rate_override_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_b_cost_rate_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_b_cost_rate_override_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_bill_rate_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_bill_rate_override_tab => SYSTEM.PA_NUM_TBL_TYPE()
,p_line_start_date_tab => SYSTEM.PA_DATE_TBL_TYPE()
,p_line_end_date_tab => SYSTEM.PA_DATE_TBL_TYPE()
,p_rollup_required_flag => l_pji_rollup_required
,p_raTxn_rollup_api_call_flag => 'N' -- Added for bug 5017855
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage:='About to update named role/parent assignment id in pa_resource_assignments';
UPDATE pa_resource_assignments
SET named_role = l_named_role_tbl(i),
parent_assignment_id=NULL
WHERE parent_assignment_id = l_ra_id_tbl(i)
AND budget_version_id = l_targ_budget_version_id;
SELECT DISTINCT BL.TXN_CURRENCY_CODE
BULK COLLECT
INTO l_txn_curr_code_tbl
FROM PA_BUDGET_LINES BL
WHERE BL.BUDGET_VERSION_ID = l_targ_budget_version_id
AND NOT EXISTS
(SELECT 1
FROM PA_FP_TXN_CURRENCIES TC
,PA_PROJ_FP_OPTIONS pfo -- Bug 4872216
WHERE tc.fin_plan_version_id =
l_targ_budget_version_id
AND pfo.project_id = p_target_project_id --4872216
AND pfo.fin_plan_version_id =
tc.fin_plan_version_id --4872216
AND pfo.proj_fp_options_id =
tc.proj_fp_options_id --4872216
AND TC.txn_currency_code = BL.txn_currency_code);
select proj_fp_options_id, project_id, fin_plan_type_id
INTO l_proj_fp_options_id, l_project_id, l_fin_plan_type_id
from pa_proj_fp_options
WHERE fin_plan_version_id = l_targ_budget_version_id;
select project_currency_code, projfunc_currency_code
INTO l_pc, l_pfc
from pa_projects_all
WHERE project_id = l_project_id;
INSERT INTO PA_FP_TXN_CURRENCIES
(
FP_TXN_CURRENCY_ID,
PROJ_FP_OPTIONS_ID,
PROJECT_ID,
FIN_PLAN_TYPE_ID,
FIN_PLAN_VERSION_ID,
TXN_CURRENCY_CODE,
DEFAULT_REV_CURR_FLAG,
DEFAULT_COST_CURR_FLAG,
DEFAULT_ALL_CURR_FLAG,
PROJECT_CURRENCY_FLAG,
PROJFUNC_CURRENCY_FLAG,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE
)
VALUES
(
PA_FP_TXN_CURRENCIES_S.NEXTVAL,
l_proj_fp_options_id ,
l_project_id,
l_fin_plan_type_id,
l_targ_budget_version_id,
l_txn_curr_code_tbl(j),
'N',
'N',
'N',
Decode(l_txn_curr_code_tbl(j),l_pc,'Y','N'),
Decode(l_txn_curr_code_tbl(j),l_pfc,'Y','N'),
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate);
PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api
( p_source => 'PA_FP_RA_MAP_TMP'
,p_budget_version_id => l_targ_budget_version_id
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
pa_debug.g_err_stage:='PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api returned error';
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
( p_calling_context => 'ASGMT_PLAN_CHANGE'
,p_project_id => p_target_project_id
,p_structure_version_id => pa_project_structure_utils.get_latest_wp_version(p_target_project_id)
,p_pub_struc_ver_id => pa_project_structure_utils.get_latest_wp_version(p_target_project_id)
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
pa_debug.g_err_stage:='Called API PA_PROJ_TASK_STRUC_PUB.process_wbs_updates_wrp';
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
ra.RESOURCE_ASSIGNMENT_ID,
src_rbc.txn_currency_code,
src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
src_rbc.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_asgn_curr src_rbc,
pa_fp_ra_map_tmp map,
--pa_budget_lines bl
pa_resource_assignments ra
WHERE ra.budget_version_id =l_targ_budget_version_id
-- AND src_rbc.budget_version_id = l_src_budget_version_id
AND map.target_res_assignment_id = ra.resource_assignment_id
AND src_rbc.resource_assignment_id = map.source_res_assignment_id;
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_rollup_flag => 'Y',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Procedure Name: delete_planning_transactions
Purpose: This procedure should be called to delete planning
transactions
Valid values for p_context are 'BUDGET','FORECAST',
'WORKPLAN' and 'TASK_ASSIGNMENT'
Valid values for p_task_or_res are 'TASKS' and
'ASSIGNMENT'
In the context of 'TASK_ASSIGNMENT' the fields
task_number and task_name are required in
p_task_rec_tbl
If p_task_or_res is TASKS,
p_element_version_id_tbl,p_task_number_tbl,
p_task_name_tbl are used.
If p_task_or_res is ASSIGNMENT,
p_resource_assignment_tbl is used
p_calling_module can be NULL or PROCESS_RES_CHG_DERV_CALC_PRMS. If passed as Y
resource assignments will be deleted otherwise they
will not be deleted.(Please note that budget lines will be deleted
always irrespective of the value for this parameter).
Please note that this parameter cannot be PROCESS_RES_CHG_DERV_CALC_PRMS
when p_task_or_res is passed as TASKS
Whenever p_calling_module is passed as PROCESS_RES_CHG_DERV_CALC_PRMS,
the parameters p_task_id_tbl,p_resource_class_code_tbl
p_rbs_element_id_tbl and p_rate_based_flag_tbl should ALSO be
passed. These tbls must be equal in length to p_resource_assignment_tbl
and should contain the task id, rbs element id and rate based flag
for the resource assignment
Bug - 3719918. New param p_currency_code_tbl is added below
When p_context - Budget/Forecast and p_task_or_res is Assignment then only the bugdet lines
Corresponding to currency code passed will be deleted. After deleting of the budget lines
the corresponding RA will only we deleted if the budget line count is 0 from the RA.
p_calling_module will be'EDIT_PLAN' when called from edit plan pages.
=======================================================================*/
PROCEDURE delete_planning_transactions
(
p_context IN VARCHAR2
,p_calling_context IN VARCHAR2 DEFAULT NULL -- Added for Bug 6856934
,p_task_or_res IN VARCHAR2 DEFAULT 'TASKS'
,p_element_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
,p_task_number_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
,p_task_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
,p_resource_assignment_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
--Introduced for bug 3589130. If this parameter is passed as Y then an error will be thrown
--When its required to delete a resource assignment containing budget lines. This parameter
--will be considered only for BUDGET and FORECAST context
,p_validate_delete_flag IN VARCHAR2 DEFAULT 'N'
-- This param will be used for B/F Context. Bug - 3719918
,p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
,p_calling_module IN VARCHAR2 DEFAULT NULL
,p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
,p_rbs_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
,p_rate_based_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_1_TBL_TYPE()
,p_resource_class_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
--For Bug 3937716. Calls to PJI and budget version rollup APIs will be skipped if p_rollup_required_flag is N.
,p_rollup_required_flag IN VARCHAR2 DEFAULT 'Y'
,p_pji_rollup_required IN VARCHAR2 DEFAULT 'Y' /* Bug 4200168 */
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
--Start of variables used for debugging
l_return_status VARCHAR2(1);
l_module_name VARCHAR2(100):='PAFPPTPB.delete_planning_transactions';
l_delete_task_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_delete_assmt_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
delete pa_resource_asgn_curr_tmp;
p_function =>'PA_FP_PLAN_TXN_PUB.delete_planning_transactions'
,p_debug_mode => l_debug_mode );
pa_debug.g_err_stage := pa_debug.g_err_stage||'p_validate_delete_flag=>'||p_validate_delete_flag||']';
l_delete_task_flag_tbl.extend(p_element_version_id_tbl.LAST);
l_delete_assmt_flag_tbl.extend(p_resource_assignment_tbl.LAST);
p_value1 => 'PAFPPTPB.Delete_planning_transactions');
p_value1 => 'PAFPPTPB.Delete_planning_transactions',
p_token2 => 'STAGE',
p_value2 => 'Invalid Data : B/F - TASK');
p_value1 => 'PAFPPTPB.Delete_planning_transactions');
* Modified Delete logic for Bug 3808720. Since Validate Delete Assignments API
* needs to be called for WORKPLAN context as well. Merginng Delete Logic for
* G_CALLING_MODULE_TASK and G_CALLING_MODULE_WORKPLAN below.
*/
IF p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK OR
p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN THEN --{
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='The calling context is task assignment. Calling the validation API';
PA_TASK_ASSIGNMENT_UTILS.VALIDATE_DELETE_ASSIGNMENT
( p_context => p_context
,p_calling_context => p_calling_context -- Added for Bug 6856934
,p_task_or_res => p_task_or_res
,p_elem_ver_id_tbl => p_element_version_id_tbl
,p_task_name_tbl => p_task_name_tbl
,p_task_number_tbl => p_task_number_tbl
,p_resource_assignment_id_tbl => p_resource_assignment_tbl
,x_delete_task_flag_tbl => l_delete_task_flag_tbl
,x_delete_asgmt_flag_tbl => l_delete_assmt_flag_tbl
,x_task_assmt_ids_tbl => l_task_assmt_ids_tbl --Bug 4951422
,x_return_status => x_return_status);
pa_debug.g_err_stage:= 'After calling Validate OutParms: l_delete_task_flag_tblCount[';
pa_debug.g_err_stage:= pa_debug.g_err_stage||l_delete_task_flag_tbl.count||']';
pa_debug.g_err_stage:= pa_debug.g_err_stage||'l_delete_assmt_flag_tblcount[';
pa_debug.g_err_stage:= pa_debug.g_err_stage||l_delete_assmt_flag_tbl.count||']';
* nothing to delete any budget lines or resource assignments.
* Note: executing this sql at many places to get budget version is failing with
* ORA-No data found error
*/ --{
IF (p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_TASK OR
p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN) THEN --{
If p_task_or_res = 'TASKS' --- bug 6076066: added the checking of p_task_res to the existing IF loop
and p_element_version_id_tbl.count > 0
and NVL(p_element_version_id_tbl(1),0) <> 0
and p_element_version_id_tbl(1) <> fnd_api.g_miss_num then
/* Bug fix: LOOP is required to get the budget version id. when multiple tasks are deleted
* some of the task may not have assignments, but some may have assignments
* without loop, the process skips all the records
*/
FOR i IN p_element_version_id_tbl.FIRST .. p_element_version_id_tbl.LAST LOOP
l_cntr := i;
SELECT pbv.project_id
,pbv.budget_version_id
,pbv.ci_id
INTO l_project_id
,l_budget_version_id
,l_ci_id
FROM pa_resource_assignments pra
,pa_budget_Versions pbv
WHERE pbv.budget_version_id=pra.budget_version_id
AND pbv.wp_version_flag='Y'
AND pra.wbs_element_version_id=p_element_version_id_tbl(l_cntr)
AND rownum < 2 ;
* validate_delete_assignment api.
*/
IF l_budget_version_id is NULL and
l_task_assmt_ids_tbl is NOT NULL and -- Bug 5408333 fix - ORA-06531: Reference to uninitialized collection
l_task_assmt_ids_tbl.COUNT > 0 Then
BEGIN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='2..Getting budget version id from l_task_assmt_ids_tbl';
SELECT pbv.project_id
,pbv.budget_version_id
,pbv.ci_id
INTO l_project_id
,l_budget_version_id
,l_ci_id
FROM pa_resource_assignments pra
,pa_budget_Versions pbv
WHERE pbv.budget_version_id=pra.budget_version_id
AND pra.resource_assignment_id =l_task_assmt_ids_tbl(1);
SELECT pbv.project_id
,pbv.budget_version_id
,pbv.ci_id
INTO l_project_id
,l_budget_version_id
,l_ci_id
FROM pa_resource_assignments pra
,pa_budget_Versions pbv
WHERE pbv.budget_version_id=pra.budget_version_id
AND pra.resource_assignment_id =p_resource_assignment_tbl(1);
pa_debug.g_err_stage:='Exiting delete_planning_transactions as No budget version exists';
IF l_delete_task_flag_tbl.count=0 THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='No elements in the l_delete_task_flag_tbl';
IF l_delete_task_flag_tbl.count<>p_element_version_id_tbl.count THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='Num elements in l_delete_task_flag_tbl, p_element_version_id_tbl dont match';
DELETE
FROM pa_budget_lines pbl
WHERE pbl.resource_assignment_id=l_task_assmt_ids_tbl(i)
RETURNING
pbl.period_name,
pbl.start_date,
pbl.end_date,
pbl.txn_currency_code,
-pbl.txn_raw_cost,
-pbl.txn_burdened_cost,
-pbl.txn_revenue,
-pbl.project_raw_cost,
-pbl.project_burdened_cost,
-pbl.project_revenue,
-pbl.raw_cost,
-pbl.burdened_cost,
-pbl.revenue,
-pbl.quantity,
pbl.cost_rejection_code ,
pbl.revenue_rejection_code ,
pbl.burden_rejection_code ,
pbl.other_rejection_code ,
pbl.pc_cur_conv_rejection_code,
pbl.pfc_cur_conv_rejection_code,
pbl.resource_assignment_id
BULK COLLECT INTO
l_period_name_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_txn_currency_code_tbl,
l_txn_raw_cost_tbl,
l_txn_burdened_cost_tbl,
l_txn_revenue_tbl,
l_project_raw_cost_tbl,
l_project_burdened_cost_tbl,
l_project_revenue_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_quantity_tbl,
l_cost_rejection_code_tbl,
l_revenue_rejection_code_tbl,
l_burden_rejection_code_tbl,
l_other_rejection_code,
l_pc_cur_conv_rej_code_tbl,
l_pfc_cur_conv_rej_code_tbl,
l_resource_assignment_id_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_resource_assignment_id_tbl(i)
,l_txn_currency_code_tbl(i)
,'Y'
FROM DUAL;
DELETE
FROM pa_resource_assignments pra
WHERE resource_assignment_id=l_task_assmt_ids_tbl(i)
RETURNING
pra.resource_assignment_id,
pra.task_id,
pra.rbs_element_id,
pra.resource_class_code,
pra.rate_based_flag
BULK COLLECT INTO
l_ra_id_in_pra_tbl,
l_task_id_in_pra_tbl,
l_rbs_element_id_in_pra_tbl,
l_res_class_code_in_pra_tbl,
l_rate_based_flag_in_pra_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
-- ,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_ra_id_in_pra_tbl(i)
-- ,l_txn_currency_code_tbl(i)
,'Y'
FROM DUAL;
IF l_delete_assmt_flag_tbl.count=0 THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='No elements in the l_delete_assmt_flag_tbl';
IF l_delete_assmt_flag_tbl.count<>p_resource_assignment_tbl.count THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='No of elements in l_delete_task_flag_tbl ';
FORALL i IN l_delete_assmt_flag_tbl.first..l_delete_assmt_flag_tbl.last
DELETE
FROM pa_budget_lines pbl
WHERE resource_assignment_id=p_resource_assignment_tbl(i)
AND l_delete_assmt_flag_tbl(i)='Y'
AND ( nvl(p_calling_module,'-99') <> 'PROCESS_RES_CHG_DERV_CALC_PRMS' OR
(init_quantity is NULL AND
txn_init_raw_cost is NULL AND
txn_init_burdened_cost is NULL AND
txn_init_revenue is NULL)
)
RETURNING
pbl.period_name,
pbl.start_date,
pbl.end_date,
pbl.txn_currency_code,
-pbl.txn_raw_cost,
-pbl.txn_burdened_cost,
-pbl.txn_revenue,
-pbl.project_raw_cost,
-pbl.project_burdened_cost,
-pbl.project_revenue,
-pbl.raw_cost,
-pbl.burdened_cost,
-pbl.revenue,
-pbl.quantity,
pbl.cost_rejection_code ,
pbl.revenue_rejection_code ,
pbl.burden_rejection_code ,
pbl.other_rejection_code ,
pbl.pc_cur_conv_rejection_code,
pbl.pfc_cur_conv_rejection_code,
pbl.resource_assignment_id
BULK COLLECT INTO
l_period_name_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_txn_currency_code_tbl,
l_txn_raw_cost_tbl,
l_txn_burdened_cost_tbl,
l_txn_revenue_tbl,
l_project_raw_cost_tbl,
l_project_burdened_cost_tbl,
l_project_revenue_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_quantity_tbl,
l_cost_rejection_code_tbl,
l_revenue_rejection_code_tbl,
l_burden_rejection_code_tbl,
l_other_rejection_code,
l_pc_cur_conv_rej_code_tbl,
l_pfc_cur_conv_rej_code_tbl,
l_resource_assignment_id_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
-- ,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_resource_assignment_id_tbl(i)
-- ,l_txn_currency_code_tbl(i)
,'Y'
FROM DUAL;
FORALL i IN l_delete_assmt_flag_tbl.first..l_delete_assmt_flag_tbl.last
DELETE
FROM pa_resource_assignments pra
WHERE resource_assignment_id=p_resource_assignment_tbl(i)
AND l_delete_assmt_flag_tbl(i)='Y'
RETURNING
pra.resource_assignment_id,
pra.task_id,
pra.rbs_element_id,
pra.resource_class_code,
pra.rate_based_flag
BULK COLLECT INTO
l_ra_id_in_pra_tbl,
l_task_id_in_pra_tbl,
l_rbs_element_id_in_pra_tbl,
l_res_class_code_in_pra_tbl,
l_rate_based_flag_in_pra_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
-- ,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_ra_id_in_pra_tbl(i)
-- ,l_project_currency_code
,'Y'
FROM DUAL;
DELETE
FROM pa_budget_lines pbl
WHERE pbl.resource_assignment_id IN (SELECT pra.resource_assignment_id
FROM pa_resource_assignments pra,
pa_budget_versions pbv
WHERE pra.budget_Version_id=pbv.budget_Version_id
AND nvl(pbv.wp_version_flag,'N')=l_wp_version_flag
AND pra.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
AND pbv.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
AND pra.wbs_element_version_id=p_element_version_id_tbl(i)
AND nvl(l_ta_display_flag , '-99')=nvl(ta_display_flag,'-99'))
AND pbl.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
RETURNING
pbl.period_name,
pbl.start_date,
pbl.end_date,
pbl.txn_currency_code,
-pbl.txn_raw_cost,
-pbl.txn_burdened_cost,
-pbl.txn_revenue,
-pbl.project_raw_cost,
-pbl.project_burdened_cost,
-pbl.project_revenue,
-pbl.raw_cost,
-pbl.burdened_cost,
-pbl.revenue,
-pbl.quantity,
pbl.cost_rejection_code ,
pbl.revenue_rejection_code ,
pbl.burden_rejection_code ,
pbl.other_rejection_code ,
pbl.pc_cur_conv_rejection_code,
pbl.pfc_cur_conv_rejection_code,
pbl.resource_assignment_id
BULK COLLECT INTO
l_period_name_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_txn_currency_code_tbl,
l_txn_raw_cost_tbl,
l_txn_burdened_cost_tbl,
l_txn_revenue_tbl,
l_project_raw_cost_tbl,
l_project_burdened_cost_tbl,
l_project_revenue_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_quantity_tbl,
l_cost_rejection_code_tbl,
l_revenue_rejection_code_tbl,
l_burden_rejection_code_tbl,
l_other_rejection_code,
l_pc_cur_conv_rej_code_tbl,
l_pfc_cur_conv_rej_code_tbl,
l_resource_assignment_id_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
-- ,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_resource_assignment_id_tbl(i)
-- ,l_txn_currency_code_tbl(i)
,'Y'
FROM DUAL;
DELETE
FROM pa_resource_assignments pra
WHERE pra.wbs_element_version_id=p_element_version_id_tbl(i)
AND EXISTS (SELECT 'X'
FROM pa_budget_Versions pbv
WHERE pbv.budget_version_id=pra.budget_Version_id
AND pbv.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
AND nvl(pbv.wp_version_flag,'N')=l_wp_version_flag
AND nvl(l_ta_display_flag , '-99')=nvl(ta_display_flag,'-99'))
AND pra.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
RETURNING
pra.resource_assignment_id,
pra.task_id,
pra.rbs_element_id,
pra.resource_class_code,
pra.rate_based_flag
BULK COLLECT INTO
l_ra_id_in_pra_tbl,
l_task_id_in_pra_tbl,
l_rbs_element_id_in_pra_tbl,
l_res_class_code_in_pra_tbl,
l_rate_based_flag_in_pra_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
-- ,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_ra_id_in_pra_tbl(i)
-- ,l_project_currency_code
,'Y'
FROM DUAL;
p_value1 => 'PAFPPTPB.Delete_planning_transactions',
p_token2 => 'STAGE',
p_value2 => 'Curr Code - RA Mismatch');
IF p_validate_delete_flag='Y' THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='Checking for the existence of budget lines';
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pra.resource_assignment_id=p_resource_assignment_tbl(i)
AND pbl.budget_Version_id=pra.budget_version_id
AND pbl.resource_assignment_id=pra.resource_assignment_id);
END IF ; --IF p_validate_delete_flag='Y' THEN
DELETE
FROM pa_budget_lines pbl
WHERE pbl.resource_assignment_id=p_resource_assignment_tbl(i)
AND ( nvl(p_calling_module,'-99') <> 'PROCESS_RES_CHG_DERV_CALC_PRMS' OR
(init_quantity is NULL AND
txn_init_raw_cost is NULL AND
txn_init_burdened_cost is NULL AND
txn_init_revenue is NULL)
)
AND nvl(l_currency_code_tbl(i),pbl.txn_currency_code) = pbl.txn_currency_code -- 3719918
RETURNING
pbl.period_name,
pbl.start_date,
pbl.end_date,
pbl.txn_currency_code,
-pbl.txn_raw_cost,
-pbl.txn_burdened_cost,
-pbl.txn_revenue,
-pbl.project_raw_cost,
-pbl.project_burdened_cost,
-pbl.project_revenue,
-pbl.raw_cost,
-pbl.burdened_cost,
-pbl.revenue,
-pbl.quantity,
pbl.cost_rejection_code ,
pbl.revenue_rejection_code ,
pbl.burden_rejection_code ,
pbl.other_rejection_code ,
pbl.pc_cur_conv_rejection_code,
pbl.pfc_cur_conv_rejection_code,
pbl.resource_assignment_id
BULK COLLECT INTO
l_period_name_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_txn_currency_code_tbl,
l_txn_raw_cost_tbl,
l_txn_burdened_cost_tbl,
l_txn_revenue_tbl,
l_project_raw_cost_tbl,
l_project_burdened_cost_tbl,
l_project_revenue_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_quantity_tbl,
l_cost_rejection_code_tbl,
l_revenue_rejection_code_tbl,
l_burden_rejection_code_tbl,
l_other_rejection_code,
l_pc_cur_conv_rej_code_tbl,
l_pfc_cur_conv_rej_code_tbl,
l_resource_assignment_id_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE -- Bug 5057010
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_resource_assignment_id_tbl(i)
,l_txn_currency_code_tbl(i) -- Bug 5057010
,'Y'
FROM DUAL;
DELETE
FROM pa_resource_assignments pra
WHERE pra.resource_assignment_id=p_resource_assignment_tbl(i)
AND (l_currency_code_tbl(i) IS NULL
OR
NOT EXISTS ( SELECT 'EXISTS'
FROM PA_BUDGET_LINES PBL
WHERE PBL.RESOURCE_ASSIGNMENT_ID = pra.resource_assignment_id))
RETURNING
pra.resource_assignment_id,
pra.task_id,
pra.rbs_element_id,
pra.resource_class_code,
pra.rate_based_flag
BULK COLLECT INTO
l_ra_id_in_pra_tbl,
l_task_id_in_pra_tbl,
l_rbs_element_id_in_pra_tbl,
l_res_class_code_in_pra_tbl,
l_rate_based_flag_in_pra_tbl;
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
-- ,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.nextval
,l_ra_id_in_pra_tbl(i)
-- ,l_project_currency_code
,'Y'
FROM DUAL;
pa_debug.g_err_stage:='No of Rec Deleted from RA : ' || l_ra_id_in_pra_tbl.COUNT;
pa_debug.g_err_stage:='Deleted RA Id : ' || l_ra_id_in_pra_tbl(i);
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => 'Y',
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => 'N',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
/* If there was nothing to delete, l_budget_version_id would be null and rollup need not be done for that case */
--Added for bug 4160258
IF (p_calling_module = 'PROCESS_RES_CHG_DERV_CALC_PRMS') THEN
l_mode := null;
l_mode := 'DELETE_RA';
SELECT TASK_ID,
RBS_ELEMENT_ID,
RESOURCE_CLASS_CODE,
RATE_BASED_FLAG
INTO l_task_id,
l_rbs_element_id,
l_res_class_code,
l_rate_based_flag
FROM PA_RESOURCE_ASSIGNMENTS
WHERE RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id_tbl(i);
pa_debug.g_err_stage:='No Data Found in RA Table for Bl deleted.';
pa_debug.g_err_stage:='Data for Update Rep Lines';
pa_debug.g_err_stage:='No of rows deleted from pa_budget_lines= '||l_resource_assignment_id_tbl.count;
pa_planning_transaction_utils.call_update_rep_lines_api
( p_source => 'PL-SQL'
,p_budget_Version_id => l_budget_version_id
,p_resource_assignment_id_tbl => l_resource_assignment_id_tbl
,p_period_name_tbl => l_period_name_tbl
,p_start_date_tbl => l_start_date_tbl
,p_end_date_tbl => l_end_date_tbl
,p_txn_currency_code_tbl => l_txn_currency_code_tbl
,p_txn_raw_cost_tbl => l_txn_raw_cost_tbl
,p_txn_burdened_cost_tbl => l_txn_burdened_cost_tbl
,p_txn_revenue_tbl => l_txn_revenue_tbl
,p_project_raw_cost_tbl => l_project_raw_cost_tbl
,p_project_burdened_cost_tbl => l_project_burdened_cost_tbl
,p_project_revenue_tbl => l_project_revenue_tbl
,p_raw_cost_tbl => l_raw_cost_tbl
,p_burdened_cost_tbl => l_burdened_cost_tbl
,p_revenue_tbl => l_revenue_tbl
,p_cost_rejection_code_tbl => l_cost_rejection_code_tbl
,p_revenue_rejection_code_tbl => l_revenue_rejection_code_tbl
,p_burden_rejection_code_tbl => l_burden_rejection_code_tbl
,p_other_rejection_code => l_other_rejection_code
,p_pc_cur_conv_rej_code_tbl => l_pc_cur_conv_rej_code_tbl
,p_pfc_cur_conv_rej_code_tbl => l_pfc_cur_conv_rej_code_tbl
,p_quantity_tbl => l_quantity_tbl
,p_rbs_element_id_tbl => l_rbs_element_id_tbl
,p_task_id_tbl => l_task_id_tbl
,p_res_class_code_tbl => l_res_class_code_tbl
,p_rate_based_flag_tbl => l_rate_based_flag_tbl
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
pa_debug.g_err_stage:='The API pa_planning_transaction_utils.call_update_rep_lines_api returned error';
pa_debug.g_err_stage:='Calling PA_PROJ_TASK_STRUC_PUB.process_wbs_updates_wrp API';
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
( p_calling_context => 'ASGMT_PLAN_CHANGE'
,p_project_id => l_project_id
,p_structure_version_id => pa_project_structure_utils.get_latest_wp_version(l_project_id)
,p_pub_struc_ver_id => pa_project_structure_utils.get_latest_wp_version(l_project_id)
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
pa_debug.g_err_stage:='After Called process_wbs_updates_wrp:retSts['||x_return_status||']';
pa_debug.g_err_stage:='Exiting delete_planning_transactions';
,p_procedure_name => 'delete_planning_transactions');
END delete_planning_transactions;
SELECT fin_plan_type_id,approved_rev_plan_type_flag
INTO l_wp_type_id,l_appr_rev_plan_type_flag
FROM pa_fin_plan_types_b
WHERE nvl(use_for_workplan_flag,'N') = 'Y';
SELECT 1
INTO l_proj_wp_type_exists
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM pa_proj_fp_options
WHERE fin_plan_type_id = l_wp_type_id
AND project_id = p_targ_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE');
SELECT proj_fp_options_id
INTO l_src_fp_option_id
FROM pa_proj_fp_options
WHERE project_id=p_src_project_id
AND fin_plan_type_id=l_wp_type_id
AND fin_plan_option_level_code = 'PLAN_TYPE';
SELECT plan_in_multi_curr_flag
INTO l_plan_in_multi_curr_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_proj_fp_options_id;
update_planning_transactions api This API checks
for the existence of the element version id passed
in pa_resource_assignments. If some of the element
version Ids are not there then it call
add_planning_transactions API to create records in
pa_resource_assignments. This API will be called
only when the context is WORKPLAN
=======================================================================*/
/*******************************************************************************************************
As part of Bug 3749516 All References to Equipment Effort or Equip Resource Class has been removed in
PROCEDURE check_and_create_task_rec_info.
p_planned_equip_effort_tbl IN parameter has also been removed as they were not being used/referred.
********************************************************************************************************/
PROCEDURE check_and_create_task_rec_info
(
p_project_id IN Pa_projects_all.project_id%TYPE
,p_struct_elem_version_id IN Pa_proj_element_versions.element_version_id%TYPE
,p_element_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_planning_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_planning_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_planned_people_effort_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE /* Bug 3720357 */
,p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE /* Bug 3720357 */
,p_apply_progress_flag IN VARCHAR2 /* Bug 3720357 */
,x_element_version_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
,x_planning_start_date_tbl OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE --File.Sql.39 bug 4440895
,x_planning_end_date_tbl OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE --File.Sql.39 bug 4440895
,x_planned_effort_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
,x_resource_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
,x_raw_cost_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE /* Bug 3720357 */ --File.Sql.39 bug 4440895
,x_burdened_cost_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE /* Bug 3720357 */ --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) AS
--Start of variables used for debugging
l_msg_count NUMBER :=0;
SELECT resource_assignment_id,resource_class_code
FROM pa_resource_assignments
WHERE wbs_element_version_id = c_wbs_element_version_id
AND ta_display_flag = 'N' -- Bug 3749516
AND resource_class_code in (PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_PPL);
/* In Update Flow for Workplan Context FND_API.G_MISS_XXXX will be considered
as a valid value for effort/Quantity -- Bug 3640498*/
IF (p_raw_cost_tbl.EXISTS(i)) THEN --AND
-- NVL(p_raw_cost_tbl(i),FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM AND
-- p_raw_cost_tbl(i) <> 0) THEN
x_raw_cost_tbl(l_out_tbl_index) := p_raw_cost_tbl(i);
l_element_version_id_tbl_tmp.DELETE;
l_planning_start_date_tbl_tmp.DELETE;
l_planning_end_date_tbl_tmp.DELETE;
l_raw_cost_tbl_tmp.DELETE;
l_burdened_cost_tbl_tmp.DELETE;
all the resource assignments would be deleted
default planning resources are created
If resource list changes
all the task, resource mappings are deleted
for task, financial element planning resources are updated with
new rlm id and rbs id
If RBS changes
all the res assignments are updated with new rbs mapping
Bug 3867302 Sep 21 2004 For ci versions reporting data is not maintained
-- Note : This api is also called from PaFinPlanControlItemImpactAMImpl.java with p_time_phase_change_flag as 'Y' to
-- delete the budget lines.
-- Bug 4724017: CDM Enhancement: Changes in behavior:
Whenever the planning level is changed for an existing version,
default planning transaction would be created only for the vesions
which uses an uncategorized resource list.
-- Bug 5754758: Modified to delete lines from pa_resource_asgn_curr before going to
create_default_plan_txn api.
==============================================================================*/
PROCEDURE Refresh_Plan_Txns(
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_plan_level_change IN VARCHAR2
,p_resource_list_change IN VARCHAR2
,p_rbs_version_change IN VARCHAR2
,p_time_phase_change_flag IN VARCHAR2
,p_ci_ver_agr_change_flag IN VARCHAR2 DEFAULT 'N' --IPM Arch Enhancement Bug 4865563
,p_rev_der_method_change IN VARCHAR2 DEFAULT 'N' --bug 5152892
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
--Start of variables used for debugging
l_return_status VARCHAR2(1);
l_delete_ra_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
l_delete_flag VARCHAR2(1); -- Bug 5003827 Issue 28
SELECT bv.project_id project_id
,bv.resource_list_id
,Decode(bv.version_type
,'COST', cost_fin_plan_level_code
,'REVENUE', revenue_fin_plan_level_code
,'ALL', all_fin_plan_level_code) fin_plan_level_code
,pfo.rbs_version_id rbs_version_id
,pfo.fin_plan_type_id fin_plan_type_id
,bv.ci_id ci_id
FROM pa_proj_fp_options pfo, pa_budget_versions bv
WHERE bv.project_id = pfo.project_id
AND pfo.fin_plan_version_id = bv.budget_version_id
AND bv.budget_version_id = p_budget_version_id;
DELETE
FROM pa_mc_budget_lines
WHERE budget_version_id = p_budget_version_id;
DELETE
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id;
l_delete_flag := 'Y';
l_delete_flag := 'N';
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => l_delete_flag, -- Bug 5003827 Issue 28
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => l_rollup_flag, -- Bug 5003827 Issue 28
p_version_level_flag => 'Y',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DELETE
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id
-- IPM changes Bug 5003827 Issue 22
RETURNING resource_assignment_id BULK COLLECT INTO l_delete_ra_id_tbl;
/* Bug 5754758 - Commenting the code, as the maintain_data is now called in version level mode to delete the RACs.
IF l_delete_ra_id_tbl.COUNT > 0 THEN
FORALL i IN l_delete_ra_id_tbl.first .. l_delete_ra_id_tbl.last
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.NEXTVAL
,l_delete_ra_id_tbl(i)
,'Y'
FROM DUAL;
IF l_delete_ra_id_tbl.COUNT > 0 THEN --{
pa_fp_gen_amount_utils.get_plan_version_dtls
(p_budget_version_id => p_budget_version_id,
x_fp_cols_rec => l_fp_cols_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => 'Y',
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => 'N',
p_version_level_flag => 'Y',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT nvl(uncategorized_flag,'N')
INTO l_res_list_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = budget_version_info_rec.resource_list_id;
DELETE FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND
NOT (resource_class_code = 'FINANCIAL_ELEMENTS' AND nvl(resource_class_flag,'N') = 'Y')
-- IPM changes Bug 5003827 Issue 22
RETURNING resource_assignment_id BULK COLLECT INTO l_delete_ra_id_tbl;
IF l_delete_ra_id_tbl.COUNT > 0 THEN
FORALL i IN l_delete_ra_id_tbl.first .. l_delete_ra_id_tbl.last
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID
,RESOURCE_ASSIGNMENT_ID
,DELETE_FLAG
)
SELECT pa_resource_asgn_curr_s.NEXTVAL
,l_delete_ra_id_tbl(i)
,'Y'
FROM DUAL;
UPDATE pa_resource_assignments
SET resource_list_member_id = l_fin_res_class_rlm_id
WHERE budget_version_id = p_budget_version_id;
IF l_delete_ra_id_tbl.COUNT > 0 THEN --{
pa_fp_gen_amount_utils.get_plan_version_dtls
(p_project_id => budget_version_info_rec.project_id,
p_budget_version_id => p_budget_version_id,
x_fp_cols_rec => l_fp_cols_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => 'Y',
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => 'N',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END IF; --} IF l_delete_ra_id_tbl.COUNT > 0 THEN
pa_debug.g_err_stage:='Calling PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE';
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_budget_version_id_tbl,
x_return_status => l_return_status,
x_msg_code => l_error_msg_code);
pa_debug.g_err_stage:='Call complete to PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE';
This will be passed as SELECT_TASKS from the select tasks page. The add planning
transactions will be called from here passing the context as create_version. This
is because this flow is also used from the add tasks and resources page to add the
tasks and resources as planning elements depending on the choice.*/
PROCEDURE Create_Default_Task_Plan_Txns (
P_budget_version_id IN Number
,P_version_plan_level_code IN VARCHAR2
,p_calling_context IN VARCHAR2
,p_add_all_resources_flag IN VARCHAR2
,X_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,X_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,X_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
AS
l_module_name varchar2(100):= 'pa.plsql.pa_fp_planning_transaction_pub';
select bv.project_id
,PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id) STRUCTURE_VERSION_ID --Bug 3546208
,bv.fin_plan_type_id
,Decode(bv.version_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST,pfo.cost_fin_plan_level_code
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,pfo.revenue_fin_plan_level_code
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL,pfo.all_fin_plan_level_code) plan_level_code
,DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, pfo.all_resource_list_id,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, pfo.cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, pfo.revenue_resource_list_id) resource_list_id
from pa_budget_versions bv,
pa_proj_fp_options pfo
where bv.budget_version_id = c_budget_version_id
and pfo.project_id = bv.project_id
and pfo.fin_plan_type_id = bv.fin_plan_type_id
and pfo.fin_plan_version_id = bv.budget_version_id;
select v.element_version_id
from pa_struct_task_wbs_v v
where v.parent_structure_version_id = c_parent_structure_version_id
and v.financial_task_flag = 'Y' -- raja bug 3690418
and v.task_level = 'L'
and not exists (select 'x'
from pa_resource_assignments pra
where pra.budget_version_id = P_budget_version_id
and pra.task_id = v.task_id
and p_calling_context = 'SELECT_TASKS');
select b.element_version_id
from pa_tasks a, pa_proj_element_versions b
/* Replaced pa_struct_task_wbs_v with base tables for performance reasons.
* Note that financial_task_flag of pa_proj_element_versions cannot be used
* since it is set to Y even for tasks that are part of fin struct ver but not
* yet published. (I.e., It could be Y for tasks not present in pa_tasks too). This
* required a join with pa_tasks to identify true fin tasks which can be
* budgeted for */
where b.parent_structure_version_id = c_parent_structure_version_id
and b.object_type = 'PA_TASKS'
and a.project_id = c_project_id
and a.project_id = b.project_id
and a.task_id = b.proj_element_id
and a.task_id = a.top_task_id
and not exists (select 'x'
from pa_resource_assignments pra
where pra.budget_version_id = P_budget_version_id
and pra.task_id = a.task_id
and p_calling_context = 'SELECT_TASKS');
(nvl(p_calling_context,'-1') NOT IN ('CREATE_VERSION','SELECT_TASKS')) OR
(nvl(p_add_all_resources_flag,'x') NOT IN ('Y','N'))
THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='P_budget_version_id = '||P_budget_version_id;
SELECT start_date,completion_date
INTO l_start_date,l_completion_date
FROM pa_projects_all
WHERE project_id = version_info_rec.project_id;
SELECT plan_class_code
INTO l_plan_class_code
FROM pa_fin_plan_types_b
where fin_plan_type_id = version_info_rec.fin_plan_type_id;
SELECT resource_list_member_id BULK COLLECT
INTO l_resource_list_member_id_tbl
FROM pa_resource_list_members prl,
PA_PLAN_RES_DEFAULTS pr, /*7291493*/
(SELECT control_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = version_info_rec.resource_list_id) rl_control_flag
WHERE resource_list_id = version_info_rec.resource_list_id
AND ((rl_control_flag.control_flag = 'N' AND
prl.object_type = 'PROJECT' AND
prl.object_id = version_info_rec.project_id)
OR
(rl_control_flag.control_flag = 'Y' AND
prl.object_type = 'RESOURCE_LIST' AND
prl.object_id = version_info_rec.resource_list_id)) AND
prl.resource_class_id = pr.resource_class_id AND
pr.enabled_flag = 'Y';
pa_debug.g_err_stage:= 'Planning at project level: Inserting a record';
pa_debug.g_err_stage:= 'Fetching cursor values for top tasks and doing bulk insert';
pa_debug.g_err_stage:= 'Fetching cursor values for lowest tasks and doing bulk insert';
For versioning disabled case working version should be updated with values
that of parent plan type record. The changes include deleting all the
existing budget lines, resource assignments. Pa_proj_fp_options and
pa_budget_versions should be updated with changed values.
Bug 3595063 For a shared structure, update current working version with the
new settings.
Bug 3619687 **** Completely changed as per the new business rules ********
Whenever there is a change in the Additional Workplan setting page,
all the chages should be propagated to all the underlying workplan
versions immediately upon save. If there is a change in RBS header,
effort data for all the versions including published versions
should be re-mapped and re-summarized
Bug 3619687 **** 15-Jun-2004 Additional Change Request for RBS change ****
Whenever there is a change for RBS if versioning is disabled for
the workplan structure, the change should be propagated to the
workplan version immediately. If versioning is enabled, the change
is applicable for all the future versions.
Bug 3619687 **** 25-Jun-2004 Additional Change Request ****
Whenever there is a change to track workplan costs flag, calculate
should be called for the costs to be calculted or nulled out as per
the change.
Bug 3725414 **** 28-Jun-2004 rbs_version_change should be propagated to working
workplan version(s) of shared + versioning enabled structure
Bug 3937716 **** 07-Oct-2004 When time phasing has changed, pji data is not
correct at the end of the process. Reason: delete_planning_transactions
and calculate() do not have the old time phased code to pass it to
the PJI update api for negating existing data. So, its decided that we
change the above two apis not to call PJI apis in this flow and call
plan_delete(), plan_create() at the end.
===============================================================================*/
PROCEDURE REFRESH_WP_SETTINGS(
p_project_id IN pa_budget_versions.project_id%TYPE
,p_resource_list_change IN VARCHAR2 DEFAULT 'N' -- Bug 3619687
,p_time_phase_change IN VARCHAR2 DEFAULT 'N' -- Bug 3619687
,p_rbs_version_change IN VARCHAR2 DEFAULT 'N' -- Bug 3619687
,p_track_costs_flag_change IN VARCHAR2 DEFAULT 'N' -- Bug 3619687
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
--Start of variables used for debugging
l_return_status VARCHAR2(1);
SELECT pfo.proj_fp_options_id
,pfo.track_workplan_costs_flag
,pfo.plan_in_multi_curr_flag
,pfo.rbs_version_id
,pfo.margin_derived_from_code
,pfo.factor_by_code
,pfo.cost_resource_list_id
,pfo.select_cost_res_auto_flag
,pfo.cost_time_phased_code
,pfo.cost_current_planning_period
,pfo.cost_period_mask_id
,pfo.projfunc_cost_rate_type
,pfo.projfunc_cost_rate_date_type
,pfo.projfunc_cost_rate_date
,pfo.project_cost_rate_type
,pfo.project_cost_rate_date_type
,pfo.project_cost_rate_date
,pfo.use_planning_rates_flag
,pfo.res_class_raw_cost_sch_id
,pfo.cost_emp_rate_sch_id
,pfo.cost_job_rate_sch_id
,pfo.cost_non_labor_res_rate_sch_id
,pfo.cost_res_class_rate_sch_id
,pfo.cost_burden_rate_sch_id
FROM pa_proj_fp_options pfo
,pa_fin_plan_types_b fpt
WHERE pfo.project_id = p_project_id
AND pfo.fin_plan_type_id = fpt.fin_plan_type_id
AND fpt.use_for_workplan_flag = 'Y'
AND pfo.fin_plan_option_level_code = 'PLAN_TYPE';
SELECT bv.budget_version_id
,pfo.proj_fp_options_id
,bv.project_structure_version_id
FROM pa_budget_versions bv,
pa_proj_elem_ver_structure ver,
pa_proj_fp_options pfo
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
AND bv.project_id = ver.project_id
AND bv.project_structure_version_id = ver.element_version_id
AND (l_wp_versioning_enabled_flag = 'N' OR -- UT
ver.status_code IN('STRUCTURE_WORKING','STRUCTURE_SUBMITTED'))
AND pfo.project_id = p_project_id
AND pfo.fin_plan_version_id = bv.budget_version_id;
SELECT bv.budget_version_id
,pfo.proj_fp_options_id
FROM pa_budget_versions bv,
pa_proj_elem_ver_structure ver,
pa_proj_fp_options pfo
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
AND bv.project_id = ver.project_id
AND bv.project_structure_version_id = ver.element_version_id
AND ver.status_code IN ('STRUCTURE_PUBLISHED')
AND pfo.project_id = p_project_id
AND pfo.fin_plan_version_id = bv.budget_version_id;
CURSOR data_for_delete_plan_txns_cur (c_budget_version_id NUMBER) IS
SELECT wbs_element_version_id
,name
,element_number
,resource_assignment_id
FROM pa_resource_assignments pra
,pa_proj_elements ppe
WHERE pra.project_id = p_project_id
AND pra.budget_version_id = c_budget_version_id
AND pra.ta_display_flag = 'Y'
AND pra.task_id = ppe.proj_element_id;
SELECT bv.budget_version_id
,pfo.proj_fp_options_id
FROM pa_budget_versions bv
,pa_proj_fp_options pfo
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
AND pfo.fin_plan_version_id = bv.budget_version_id
AND pfo.project_id = bv.project_id;
/* Collecting all the working version records and inserting into the pji table
with negative values of budget lines */
IF nvl(p_resource_list_change, 'N') = 'Y' OR nvl(p_time_phase_change, 'N') = 'Y'
OR nvl(p_track_costs_flag_change, 'N') = 'Y' OR (nvl(p_rbs_version_change, 'N') = 'Y' AND
(nvl(PA_WORKPLAN_ATTR_UTILS.Check_Wp_Versioning_Enabled(p_project_id),'N') = 'N'
OR nvl(PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(p_project_id),'N') = 'Y')) THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='About to insert negative lines';
pa_debug.g_err_stage:='Calling call_update_rep_lines' || l_budget_version_id_tbl(i);
PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api
--( p_source => 'POPULATE_PJI_TABLE' --Commented for bug 5073350.
( p_source => 'REFRESH_WP_SETTINGS'
,p_budget_version_id => l_budget_version_id_tbl(i)
,p_qty_sign => -1
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
pa_debug.g_err_stage:='PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api returned error';
END IF; -- inserting negative rows
pa_debug.g_err_stage:='inserted -ve lines';
UPDATE pa_resource_assignments
SET resource_list_member_id = l_people_res_class_rlm_id
WHERE budget_version_id = l_pub_budget_version_id_tbl(i)
/* The only records present in pa_resource_assignments for published versions with resource
list NONE would fall under the below cateogry. Not including them as part of select as they
dont add any value to performance interms of better index usage. Retaining them in the comment
for understanding purpose */
AND resource_class_code = 'PEOPLE'
AND resource_class_flag = 'Y'
AND ta_display_flag = 'N';
UPDATE pa_progress_rollup
SET object_id = l_people_res_class_rlm_id
WHERE project_id = p_project_id AND
object_type = 'PA_ASSIGNMENTS' AND
structure_type = 'WORKPLAN' AND
structure_Version_id is NULL; /* Only published versions */
processing done for working wp versions in the loop below (delete planning transactions etc
and we want to ensure this update is done after the processing */
/* We also need to update the resource list id in the pa_budget_versions table as well as the
pa_proj_fp_options table .*/
FORALL i IN l_pub_proj_fp_options_id_tbl.first .. l_pub_proj_fp_options_id_tbl.last
UPDATE pa_proj_fp_options
SET cost_resource_list_id = parent_plan_type_rec.cost_resource_list_id
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
where proj_fp_options_id = l_pub_proj_fp_options_id_tbl(i);
UPDATE pa_budget_versions
SET resource_list_id = parent_plan_type_rec.cost_resource_list_id
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
where budget_version_id = l_pub_budget_version_id_tbl(i);
UPDATE pa_proj_fp_options
SET track_workplan_costs_flag = parent_plan_type_rec.track_workplan_costs_flag
,plan_in_multi_curr_flag = parent_plan_type_rec.plan_in_multi_curr_flag
-- Raja ,rbs_version_id = parent_plan_type_rec.rbs_version_id
,margin_derived_from_code = parent_plan_type_rec.margin_derived_from_code
,factor_by_code = parent_plan_type_rec.factor_by_code
,cost_resource_list_id = parent_plan_type_rec.cost_resource_list_id
,select_cost_res_auto_flag = parent_plan_type_rec.select_cost_res_auto_flag
,cost_time_phased_code = parent_plan_type_rec.cost_time_phased_code
,cost_current_planning_period = parent_plan_type_rec.cost_current_planning_period
,cost_period_mask_id = parent_plan_type_rec.cost_period_mask_id
,projfunc_cost_rate_type = parent_plan_type_rec.projfunc_cost_rate_type
,projfunc_cost_rate_date_type = parent_plan_type_rec.projfunc_cost_rate_date_type
,projfunc_cost_rate_date = parent_plan_type_rec.projfunc_cost_rate_date
,project_cost_rate_type = parent_plan_type_rec.project_cost_rate_type
,project_cost_rate_date_type = parent_plan_type_rec.project_cost_rate_date_type
,project_cost_rate_date = parent_plan_type_rec.project_cost_rate_date
,use_planning_rates_flag = parent_plan_type_rec.use_planning_rates_flag
,res_class_raw_cost_sch_id = parent_plan_type_rec.res_class_raw_cost_sch_id
,cost_emp_rate_sch_id = parent_plan_type_rec.cost_emp_rate_sch_id
,cost_job_rate_sch_id = parent_plan_type_rec.cost_job_rate_sch_id
,cost_non_labor_res_rate_sch_id = parent_plan_type_rec.cost_non_labor_res_rate_sch_id
,cost_res_class_rate_sch_id = parent_plan_type_rec.cost_res_class_rate_sch_id
,cost_burden_rate_sch_id = parent_plan_type_rec.cost_burden_rate_sch_id
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE proj_fp_options_id = l_proj_fp_options_id_tbl(i);
UPDATE pa_budget_versions
SET resource_list_id = parent_plan_type_rec.cost_resource_list_id
,current_planning_period = parent_plan_type_rec.cost_current_planning_period
,period_mask_id = parent_plan_type_rec.cost_period_mask_id
-- Bug 3630069 Amounts should not be updated with 0. These columns are taken care of
-- by delete planning transactions api if there is any change to this amount
/***
,raw_cost = 0
,burdened_cost = 0
,total_project_raw_cost = 0
,total_project_burdened_cost = 0
,labor_quantity = 0
,equipment_quantity = 0
***/
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
,record_version_number = record_version_number + 1
WHERE budget_version_id = l_budget_version_id_tbl(i);
OPEN data_for_delete_plan_txns_cur(l_budget_version_id_tbl(i));
FETCH data_for_delete_plan_txns_cur
BULK COLLECT INTO l_task_version_id_tbl
,l_task_name_tbl
,l_task_number_tbl
,l_res_assignment_id_tbl ;
CLOSE data_for_delete_plan_txns_cur;
pa_debug.g_err_stage:='about to call delete palnning trans';
PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions (
p_context => 'TASK_ASSIGNMENT'
,p_task_or_res => 'ASSIGNMENT'
,p_element_version_id_tbl => l_task_version_id_tbl
,p_task_number_tbl => l_task_number_tbl
,p_task_name_tbl => l_task_name_tbl
,p_resource_assignment_tbl => l_res_assignment_id_tbl
,p_rollup_required_flag => 'N' --For Bug 3937716
,p_pji_rollup_required => 'N' /* Bug 4200168 */
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
l_task_version_id_tbl.delete;
l_task_name_tbl.delete;
l_task_number_tbl.delete;
l_res_assignment_id_tbl.delete;
UPDATE pa_resource_assignments
SET resource_list_member_id = l_people_res_class_rlm_id
WHERE budget_version_id = l_budget_version_id_tbl(i)
AND resource_class_code = 'PEOPLE'
AND resource_class_flag = 'Y';
would have got deleted above.Hence we are updating all records in pa_progress_rollup
with the new rlmid petaining to people class rlm for working versions. */
UPDATE pa_progress_rollup
SET object_id = l_people_res_class_rlm_id
WHERE project_id = p_project_id AND
object_type = 'PA_ASSIGNMENTS' AND
structure_type = 'WORKPLAN' AND
structure_Version_id = l_proj_struct_ver_id_tbl(i); /* for Working versions */
SELECT count(*)
INTO l_res_assignment_count
FROM pa_resource_assignments
WHERE budget_version_id = l_budget_version_id_tbl(i);
UPDATE pa_proj_fp_options
SET rbs_version_id = parent_plan_type_rec.rbs_version_id
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE proj_fp_options_id = l_proj_fp_options_id_tbl(i);
UPDATE pa_resource_assignments
SET rbs_element_id = l_rbs_element_id_tbl(j)
,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = l_budget_version_id_tbl(i)
AND resource_assignment_id = l_txn_source_id_tbl(j);
UPDATE pa_resource_assignments
SET rbs_element_id = null
,txn_accum_header_id = null
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = l_budget_version_id_tbl(i);
l_txn_source_id_tbl.delete;
l_res_list_member_id_tbl.delete;
l_rbs_element_id_tbl.delete;
l_txn_accum_header_id_tbl.delete;
/* Collecting all the working version records and inserting into the pji table
with positive values of budget lines */
IF nvl(p_resource_list_change, 'N') = 'Y' OR nvl(p_time_phase_change, 'N') = 'Y'
OR nvl(p_track_costs_flag_change, 'N') = 'Y' OR ( nvl(p_rbs_version_change, 'N') = 'Y' AND
(nvl(PA_WORKPLAN_ATTR_UTILS.Check_Wp_Versioning_Enabled(p_project_id),'N') = 'N'
OR nvl(PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(p_project_id),'N') = 'Y')) THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='About to insert positive values with new rbs element ids';
PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api
--( p_source => 'POPULATE_PJI_TABLE' --Commented for bug 5073350.
( p_source => 'REFRESH_WP_SETTINGS'
,p_budget_version_id => l_budget_version_id_tbl(i)
,p_qty_sign => 1
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
pa_debug.g_err_stage:='PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api returned error';
This call will update all the plan versions of a project which are affected due to the
workplan paln settings change.If any of those versions is in pending processing status,
as per the PJI design error will be thrown.So, commenting out this code.
The PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE api will be called seperately for each plan version
in PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api.
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (
x_return_status => x_return_status,
x_msg_code => l_error_msg_code);
END IF; -- inserting positive rows
SELECT o.proj_fp_options_id,
o.fin_plan_version_id,
bv.resource_list_id
FROM pa_proj_fp_options o,
pa_budget_versions bv
WHERE o.project_id = p_project_id
AND o.fin_plan_type_id = p_fin_plan_type_id
AND o.fin_plan_version_id = bv.budget_version_id
AND bv.ci_id IS NULL -- bug 3867302
AND bv.budget_status_code IN ('W', 'S');
SELECT o.proj_fp_options_id
,o.fin_plan_version_id
,bv.resource_list_id
,o.fin_plan_type_id
,bv.ci_id
FROM pa_budget_versions bv,
pa_proj_fp_options o
WHERE bv.project_id = o.project_id
AND bv.fin_plan_type_id = o.fin_plan_type_id
AND bv.budget_version_id = o.fin_plan_version_id
AND bv.budget_version_id = p_budget_version_id;
Select rbs_version_id
into l_rbs_version_id
from pa_proj_fp_options
where project_id = p_project_id
and fin_plan_type_id = p_fin_plan_type_id
and fin_plan_option_level_code = 'PLAN_TYPE';
update pa_proj_fp_options
set rbs_version_id = l_rbs_version_id
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE proj_fp_options_id = l_proj_fp_options_id_tbl(i);
UPDATE pa_resource_assignments
SET rbs_element_id = l_rbs_element_id_tbl(j)
,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = l_budget_version_id_tbl(i)
AND resource_assignment_id = l_txn_source_id_tbl(j);
UPDATE pa_resource_assignments
SET rbs_element_id = null
,txn_accum_header_id = null
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = l_budget_version_id_tbl(i);
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_budget_version_id_tbl,
x_return_status => x_return_status,
x_msg_code => l_error_msg_code);
SELECT 'Y'
INTO l_dup_exists
FROM pa_resource_assignments
WHERE task_id=p_task_id
AND resource_list_member_id=p_resource_list_member_id
AND budget_version_id=p_budget_version_id
AND project_assignment_id=-1
AND project_id=p_project_id;