[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_deleted_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
SELECT record_version_number
INTO l_record_version_number
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
/* Records are deleted from pa_budget_lines and PA_RESOURCE_ASSIGNMENTS
tables when the flag is set to N */
IF l_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
DELETE FROM PA_BUDGET_LINES
WHERE budget_version_id = p_budget_version_id ;
DELETE FROM PA_RESOURCE_ASSIGNMENTS
WHERE budget_version_id = p_budget_version_id ;
P_DELETE_FLAG => 'Y',
P_VERSION_LEVEL_FLAG => 'Y',
P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
SELECT resource_assignment_id
BULK COLLECT INTO
l_res_as_id
FROM PA_RESOURCE_ASSIGNMENTS
WHERE budget_version_id = p_budget_version_id AND
transaction_source_code IS NOT NULL;
DELETE FROM PA_BUDGET_LINES
WHERE resource_assignment_id = l_res_as_id(i);
UPDATE PA_RESOURCE_ASSIGNMENTS
SET transaction_source_code = null
WHERE resource_assignment_id = l_res_as_id(j);
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp (
RESOURCE_ASSIGNMENT_ID,
DELETE_FLAG )
VALUES (
l_res_as_id(k),
'Y' );
P_DELETE_FLAG => 'Y',
P_VERSION_LEVEL_FLAG => 'N',
P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
P_COMMIT_FLAG => P_COMMIT_FLAG,
P_INIT_MSG_FLAG => P_INIT_MSG_FLAG,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
P_COMMIT_FLAG => P_COMMIT_FLAG,
P_INIT_MSG_FLAG => P_INIT_MSG_FLAG,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
P_INIT_MSG_FLAG => P_INIT_MSG_FLAG,
P_COMMIT_FLAG => P_COMMIT_FLAG,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
P_INIT_MSG_FLAG => P_INIT_MSG_FLAG,
P_COMMIT_FLAG => P_COMMIT_FLAG,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT
DECODE(bv.version_type,
'COST', opt.gen_src_cost_plan_version_id,
'REVENUE',opt.gen_src_rev_plan_version_id,
'ALL',opt.gen_src_all_plan_version_id)
INTO l_source_bv_id
FROM pa_proj_fp_options opt, pa_budget_versions bv
WHERE bv.budget_version_id = opt.fin_plan_version_id
AND bv.budget_version_id = p_budget_version_id ;
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT person_id INTO x_person_id FROM
PA_RESOURCE_TXN_ATTRIBUTES WHERE
RESOURCE_ID = p_res_id;
PROCEDURE UPDATE_BUDG_VERSION(p_budget_version_id IN NUMBER ) IS
BEGIN
UPDATE PA_BUDGET_VERSIONS SET PLAN_PROCESSING_CODE = 'E'
WHERE BUDGET_VERSION_ID = p_budget_version_id;
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
--this pl/sql table will have res_asg_id for which manual budget lines are already deleted
P_COMMIT_FLAG IN VARCHAR2,
P_INIT_MSG_FLAG IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.GENERATE_BUDGET_AMT_RES_SCH';
SELECT P.PROJECT_TYPE,
P.PROJECT_CURRENCY_CODE,
P.CARRYING_OUT_ORGANIZATION_ID,
P.PROJECT_VALUE,
P.JOB_BILL_RATE_SCHEDULE_ID,
P.EMP_BILL_RATE_SCHEDULE_ID,
P.DISTRIBUTION_RULE,
P.BILL_JOB_GROUP_ID,
NVL(P.ORG_ID,-99),
P.COMPLETION_DATE,
NVL(P.TEMPLATE_FLAG,'N'),
P.PROJFUNC_CURRENCY_CODE,
P.PROJFUNC_BIL_RATE_DATE_CODE,
P.PROJFUNC_BIL_RATE_TYPE,
P.PROJFUNC_BIL_RATE_DATE,
P.PROJFUNC_BIL_EXCHANGE_RATE,
P.COST_JOB_GROUP_ID,
P.PROJECT_RATE_DATE,
P.PROJECT_RATE_TYPE,
P.PROJECT_BIL_RATE_DATE_CODE,
P.PROJECT_BIL_RATE_TYPE,
P.PROJECT_BIL_RATE_DATE,
P.PROJECT_BIL_EXCHANGE_RATE,
P.PROJFUNC_COST_RATE_TYPE,
P.PROJFUNC_COST_RATE_DATE,
P.LABOR_TP_SCHEDULE_ID,
P.LABOR_TP_FIXED_DATE,
P.LABOR_SCHEDULE_DISCOUNT,
NVL(P.ASSIGN_PRECEDES_TASK,'N'),
NVL(P.LABOR_BILL_RATE_ORG_ID,-99),
P.LABOR_STD_BILL_RATE_SCHDL,
P.LABOR_SCHEDULE_FIXED_DATE,
P.LABOR_SCH_TYPE
FROM PA_PROJECTS_ALL P
WHERE PROJECT_ID = P_PROJECT_ID;
SELECT DISTINCT PA.ASSIGNMENT_ID,
PA.START_DATE,
PA.RESOURCE_ID,
PA.PROJECT_ROLE_ID,
PA.FCST_JOB_ID,
PA.FCST_JOB_GROUP_ID,
PR.MEANING,
PA.ASSIGNMENT_TYPE ,
NVL(PA.EXPENDITURE_ORGANIZATION_ID,
NVL(p.CARRYING_OUT_ORGANIZATION_ID,-99))
EXPENDITURE_ORGANIZATION_ID,
PA.EXPENDITURE_TYPE,
PA.REVENUE_BILL_RATE,
NVL(PA.EXPENDITURE_ORG_ID,
NVL(p.ORG_ID,-99)) EXPENDITURE_ORG_ID,
PA.STATUS_CODE,
WB.BILLABLE_CAPITALIZABLE_FLAG,
'Y' PROCESS_CODE,
to_char(null) ERROR_MSG_CODE,
PA.END_DATE,
RTA.PERSON_ID,
PA.ASSIGNMENT_NAME,
FI.EXPENDITURE_ORGANIZATION_ID,
DECODE(PA.EXPENDITURE_TYPE,null,NULL,'EXPENDITURE_TYPE')
FROM PA_PROJECT_ASSIGNMENTS PA,
PA_WORK_TYPES_B WB,
PA_PROJECT_ROLE_TYPES PR,
PA_PROJECTS_ALL P,
PA_RESOURCE_TXN_ATTRIBUTES RTA,
PA_FORECAST_ITEMS FI
WHERE PA.PROJECT_ID = p_project_id
AND PA.PROJECT_ROLE_ID = PR.PROJECT_ROLE_ID
AND P.PROJECT_ID = p_project_id
AND WB.WORK_TYPE_ID = PA.WORK_TYPE_ID(+)
AND PA.RESOURCE_ID = RTA.RESOURCE_ID(+)
AND DECODE(PA.STATUS_CODE,NULL,'Y',
DECODE(PA.ASSIGNMENT_TYPE,
'OPEN_ASSIGNMENT',
PA_ASSIGNMENT_UTILS.Is_Asgmt_In_Open_Status(PA.STATUS_CODE,'OPEN_ASGMT'),
'STAFFED_ASSIGNMENT',
DECODE(PA_ASSIGNMENT_UTILS.Is_Staffed_Asgmt_Cancelled(PA.STATUS_CODE,'STAFFED_ASGMT'),
'Y','N','N','Y'),
'STAFFED_ADMIN_ASSIGNMENT',
DECODE(PA_ASSIGNMENT_UTILS.Is_Staffed_Asgmt_Cancelled(PA.STATUS_CODE,'STAFFED_ASGMT'),
'Y','N','N','Y'))) = 'Y'
AND PA.ASSIGNMENT_ID = FI.ASSIGNMENT_ID
AND FI.DELETE_FLAG = 'N' -- Added for Bug 5029939
AND FI.ERROR_FLAG = 'N'; -- Added for Bug 5029939
/* Bug 5657334: Added org_id join in pa_periods_all to avoid multiple row selection */
CURSOR FCST_PA(p_prj_assignment_id NUMBER, c_act_thru_date DATE, c_exp_organization_id NUMBER, c_org_id NUMBER) IS
SELECT FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
FI.RCVR_PA_PERIOD_NAME,
P.START_DATE,
P.END_DATE,
SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID)
FROM PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
PA_PERIODS_ALL P
WHERE FI.PROJECT_ORG_ID = NVL(P.ORG_ID,-99)
AND P.ORG_ID = c_org_id /* Bug 5657334 */
AND P.PERIOD_NAME = FI.RCVR_PA_PERIOD_NAME
AND FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
AND FID.FORECAST_SUMMARIZED_CODE = 'N'
AND FID.NET_ZERO_FLAG = 'N'
AND FI.ERROR_FLAG = 'N'
AND FI.DELETE_FLAG = 'N'
AND ASSIGNMENT_ID = p_prj_assignment_id
AND FI.EXPENDITURE_ORG_ID <>-88
AND FI.ITEM_DATE >= NVL(c_act_thru_date+1,FI.ITEM_DATE)
AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
GROUP BY FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
P.START_DATE,
P.END_DATE,
FI.RCVR_PA_PERIOD_NAME;
SELECT FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
FI.RCVR_GL_PERIOD_NAME,
GLP.START_DATE,
GLP.END_DATE,
SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID)
FROM PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
GL_PERIODS GLP
WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
AND FID.FORECAST_SUMMARIZED_CODE = 'N'
AND FID.NET_ZERO_FLAG = 'N'
AND FI.ERROR_FLAG = 'N'
AND FI.DELETE_FLAG = 'N'
AND GLP.PERIOD_SET_NAME = FI.RCVR_PERIOD_SET_NAME
AND GLP.PERIOD_NAME = FI.RCVR_GL_PERIOD_NAME
AND ASSIGNMENT_ID = p_prj_assignment_id
AND FI.EXPENDITURE_ORG_ID <> -88
AND FI.ITEM_DATE >= NVL(c_act_thru_date+1,FI.ITEM_DATE)
AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
GROUP BY FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
GLP.START_DATE,
GLP.END_DATE,
FI.RCVR_GL_PERIOD_NAME;
SELECT FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID),
null period_name,
min(fi.item_Date) start_date,
max(fi.item_Date) end_date -- Bug 4549862: Changed min to max.
FROM PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID
WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
AND FID.FORECAST_SUMMARIZED_CODE = 'N'
AND FID.NET_ZERO_FLAG = 'N'
AND FI.ERROR_FLAG = 'N'
AND FI.DELETE_FLAG = 'N'
AND ASSIGNMENT_ID = p_prj_assignment_id
AND FI.EXPENDITURE_ORG_ID <> -88
AND FI.ITEM_DATE >= NVL(c_act_thru_date+1,FI.ITEM_DATE)
AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
GROUP BY FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID;
SELECT BL.PERIOD_NAME,
BL.START_DATE,
BL.BURDENED_COST
FROM PA_BUDGET_LINES BL,
PA_RESOURCE_ASSIGNMENTS RA
WHERE BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID
AND RA.BUDGET_VERSION_ID = c_budget_version_id
AND RA.PROJECT_ID = c_project_id
AND RA.RESOURCE_LIST_MEMBER_ID = 103
ORDER BY BL.START_DATE;
/* Bug 5657334: Added org_id join in pa_periods_all to avoid multiple row selection */
CURSOR FCST_RATE_PA(p_prj_assignment_id NUMBER, c_act_thru_date DATE, c_exp_organization_id NUMBER, c_org_id NUMBER) IS
SELECT FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
FI.RCVR_PA_PERIOD_NAME,
P.START_DATE,
P.END_DATE,
SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID),
RA.RESOURCE_ASSIGNMENT_ID,
RA.UNIT_OF_MEASURE,
RA.RESOURCE_CLASS_CODE,
RA.ORGANIZATION_ID,
RA.JOB_ID,
RA.PERSON_ID,
RA.EXPENDITURE_TYPE,
RA.NON_LABOR_RESOURCE,
RA.BOM_RESOURCE_ID,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.MFC_COST_TYPE_ID,
RA.RATE_EXPENDITURE_TYPE,
NVL(RA.RATE_BASED_FLAG, 'N') RATE_BASED_FLAG,
RA.RATE_EXPENDITURE_ORG_ID,
RLM.RES_FORMAT_ID,
RLM.RESOURCE_LIST_MEMBER_ID,
RLM.RESOURCE_ID,
RLM.RESOURCE_LIST_ID,
RLM.ALIAS
FROM PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
PA_PERIODS_ALL P,
PA_RES_LIST_MAP_TMP4 TMP4,
PA_RESOURCE_ASSIGNMENTS RA,
PA_RESOURCE_LIST_MEMBERS RLM
WHERE FI.PROJECT_ORG_ID = NVL(P.ORG_ID,-99)
AND P.ORG_ID = c_org_id /* Bug 5657334 */
AND P.PERIOD_NAME = FI.RCVR_PA_PERIOD_NAME
AND FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
AND FID.FORECAST_SUMMARIZED_CODE = 'N'
AND FID.NET_ZERO_FLAG = 'N'
AND FI.ERROR_FLAG = 'N'
AND FI.DELETE_FLAG = 'N'
AND ASSIGNMENT_ID = p_prj_assignment_id
AND FI.EXPENDITURE_ORG_ID <>-88
AND FI.ITEM_DATE >= NVL(c_act_thru_date+1,FI.ITEM_DATE)
AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
AND ASSIGNMENT_ID = TMP4.TXN_SOURCE_ID
AND RA.RESOURCE_ASSIGNMENT_ID = TMP4.TXN_RESOURCE_ASSIGNMENT_ID
AND RA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
GROUP BY FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
P.START_DATE,
P.END_DATE,
FI.RCVR_PA_PERIOD_NAME,
RA.RESOURCE_ASSIGNMENT_ID,
RA.UNIT_OF_MEASURE,
RA.RESOURCE_CLASS_CODE,
RA.ORGANIZATION_ID,
RA.JOB_ID,
RA.PERSON_ID,
RA.EXPENDITURE_TYPE,
RA.NON_LABOR_RESOURCE,
RA.BOM_RESOURCE_ID,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.MFC_COST_TYPE_ID,
RA.RATE_EXPENDITURE_TYPE,
RA.RATE_BASED_FLAG,
RA.RATE_EXPENDITURE_ORG_ID,
RLM.RES_FORMAT_ID,
RLM.RESOURCE_LIST_MEMBER_ID,
RLM.RESOURCE_ID,
RLM.RESOURCE_LIST_ID,
RLM.ALIAS;
SELECT FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
FI.RCVR_GL_PERIOD_NAME,
GLP.START_DATE,
GLP.END_DATE,
SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID),
RA.RESOURCE_ASSIGNMENT_ID,
RA.UNIT_OF_MEASURE,
RA.RESOURCE_CLASS_CODE,
RA.ORGANIZATION_ID,
RA.JOB_ID,
RA.PERSON_ID,
RA.EXPENDITURE_TYPE,
RA.NON_LABOR_RESOURCE,
RA.BOM_RESOURCE_ID,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.MFC_COST_TYPE_ID,
RA.RATE_EXPENDITURE_TYPE,
NVL(RA.RATE_BASED_FLAG, 'N') RATE_BASED_FLAG,
RA.RATE_EXPENDITURE_ORG_ID,
RLM.RES_FORMAT_ID,
RLM.RESOURCE_LIST_MEMBER_ID,
RLM.RESOURCE_ID,
RLM.RESOURCE_LIST_ID,
RLM.ALIAS
FROM PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
GL_PERIODS GLP,
PA_RES_LIST_MAP_TMP4 TMP4,
PA_RESOURCE_ASSIGNMENTS RA,
PA_RESOURCE_LIST_MEMBERS RLM
WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
AND FID.FORECAST_SUMMARIZED_CODE = 'N'
AND FID.NET_ZERO_FLAG = 'N'
AND FI.ERROR_FLAG = 'N'
AND FI.DELETE_FLAG = 'N'
AND GLP.PERIOD_SET_NAME = FI.RCVR_PERIOD_SET_NAME
AND GLP.PERIOD_NAME = FI.RCVR_GL_PERIOD_NAME
AND ASSIGNMENT_ID = p_prj_assignment_id
AND FI.EXPENDITURE_ORG_ID <> -88
AND FI.ITEM_DATE >= NVL(c_act_thru_date+1,FI.ITEM_DATE)
AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
AND ASSIGNMENT_ID = TMP4.TXN_SOURCE_ID
AND RA.RESOURCE_ASSIGNMENT_ID = TMP4.TXN_RESOURCE_ASSIGNMENT_ID
AND RA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
GROUP BY FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
GLP.START_DATE,
GLP.END_DATE,
FI.RCVR_GL_PERIOD_NAME,
RA.RESOURCE_ASSIGNMENT_ID,
RA.UNIT_OF_MEASURE,
RA.RESOURCE_CLASS_CODE,
RA.ORGANIZATION_ID,
RA.JOB_ID,
RA.PERSON_ID,
RA.EXPENDITURE_TYPE,
RA.NON_LABOR_RESOURCE,
RA.BOM_RESOURCE_ID,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.MFC_COST_TYPE_ID,
RA.RATE_EXPENDITURE_TYPE,
RA.RATE_BASED_FLAG,
RA.RATE_EXPENDITURE_ORG_ID,
RLM.RES_FORMAT_ID,
RLM.RESOURCE_LIST_MEMBER_ID,
RLM.RESOURCE_ID,
RLM.RESOURCE_LIST_ID,
RLM.ALIAS;
SELECT FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID),
null period_name,
min(fi.item_Date) start_date,
max(fi.item_Date) end_date, -- Bug 4621534: Changed min to max.
RA.RESOURCE_ASSIGNMENT_ID,
RA.UNIT_OF_MEASURE,
RA.RESOURCE_CLASS_CODE,
RA.ORGANIZATION_ID,
RA.JOB_ID,
RA.PERSON_ID,
RA.EXPENDITURE_TYPE,
RA.NON_LABOR_RESOURCE,
RA.BOM_RESOURCE_ID,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.MFC_COST_TYPE_ID,
RA.RATE_EXPENDITURE_TYPE,
NVL(RA.RATE_BASED_FLAG, 'N') RATE_BASED_FLAG,
RA.RATE_EXPENDITURE_ORG_ID,
RLM.RES_FORMAT_ID,
RLM.RESOURCE_LIST_MEMBER_ID,
RLM.RESOURCE_ID,
RLM.RESOURCE_LIST_ID,
RLM.ALIAS
FROM PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
PA_RES_LIST_MAP_TMP4 TMP4,
PA_RESOURCE_ASSIGNMENTS RA,
PA_RESOURCE_LIST_MEMBERS RLM
WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
AND FID.FORECAST_SUMMARIZED_CODE = 'N'
AND FID.NET_ZERO_FLAG = 'N'
AND FI.ERROR_FLAG = 'N'
AND FI.DELETE_FLAG = 'N'
AND ASSIGNMENT_ID = p_prj_assignment_id
AND FI.EXPENDITURE_ORG_ID <> -88
AND FI.ITEM_DATE >= NVL(c_act_thru_date+1,FI.ITEM_DATE)
AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
AND ASSIGNMENT_ID = TMP4.TXN_SOURCE_ID
AND RA.RESOURCE_ASSIGNMENT_ID = TMP4.TXN_RESOURCE_ASSIGNMENT_ID
AND RA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
GROUP BY FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
RA.RESOURCE_ASSIGNMENT_ID,
RA.UNIT_OF_MEASURE,
RA.RESOURCE_CLASS_CODE,
RA.ORGANIZATION_ID,
RA.JOB_ID,
RA.PERSON_ID,
RA.EXPENDITURE_TYPE,
RA.NON_LABOR_RESOURCE,
RA.BOM_RESOURCE_ID,
RA.INVENTORY_ITEM_ID,
RA.ITEM_CATEGORY_ID,
RA.MFC_COST_TYPE_ID,
RA.RATE_EXPENDITURE_TYPE,
RA.RATE_BASED_FLAG,
RA.RATE_EXPENDITURE_ORG_ID,
RLM.RES_FORMAT_ID,
RLM.RESOURCE_LIST_MEMBER_ID,
RLM.RESOURCE_ID,
RLM.RESOURCE_LIST_ID,
RLM.ALIAS;
SELECT DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_bill_rate_sch_id,
DECODE(bv.version_type,'REVENUE',pfo.rev_res_class_rate_sch_id,
'ALL' ,pfo.rev_res_class_rate_sch_id,
NULL)) res_class_bill_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_raw_cost_sch_id,
DECODE(bv.version_type,'COST',pfo.cost_res_class_rate_sch_id,
'ALL' ,pfo.cost_res_class_rate_sch_id,
NULL)) res_class_raw_cost_sch_id
,NVL(pfo.use_planning_rates_flag,'N') use_planning_rates_flag
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'REVENUE',pfo.rev_job_rate_sch_id,
'ALL' ,pfo.rev_job_rate_sch_id,
NULL)) rev_job_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'COST' ,pfo.cost_job_rate_sch_id,
'ALL' ,pfo.cost_job_rate_sch_id,
NULL)) cost_job_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'REVENUE',pfo.rev_emp_rate_sch_id,
'ALL' ,pfo.rev_emp_rate_sch_id,
NULL)) rev_emp_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'COST' ,pfo.cost_emp_rate_sch_id,
'ALL' ,pfo.cost_emp_rate_sch_id,
NULL)) cost_emp_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'REVENUE',pfo.rev_non_labor_res_rate_sch_id,
'ALL' ,pfo.rev_non_labor_res_rate_sch_id,
NULL)) rev_non_labor_res_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'COST' ,pfo.cost_non_labor_res_rate_sch_id,
'ALL' ,pfo.cost_non_labor_res_rate_sch_id,
NULL)) cost_non_labor_res_rate_sch_id
,DECODE(NVL(pfo.use_planning_rates_flag,'N'),'N',NULL,
DECODE(bv.version_type,'COST' ,pfo.cost_burden_rate_sch_id,
'ALL' ,pfo.cost_burden_rate_sch_id,
NULL)) cost_burden_rate_sch_id
,bv.version_type fp_budget_version_type
,NVL(bv.approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
,NVL(pfo.plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
,pp.assign_precedes_task
,pp.bill_job_group_id
,pp.carrying_out_organization_id
,NVL(pp.multi_currency_billing_flag,'N') multi_currency_billing_flag
,pp.org_id
,pp.non_labor_bill_rate_org_id
,pp.project_currency_code
,pp.non_labor_schedule_discount
,pp.non_labor_schedule_fixed_date
,pp.non_lab_std_bill_rt_sch_id
,pp.project_type
,pp.projfunc_currency_code
,pp.emp_bill_rate_schedule_id
,pp.job_bill_rate_schedule_id
,pp.labor_bill_rate_org_id
,pp.labor_sch_type
,pp.non_labor_sch_type
,pp.name project_name
FROM pa_proj_fp_options pfo
,pa_budget_versions bv
,pa_projects_all pp
WHERE pfo.fin_plan_version_id = bv.budget_version_id
AND bv.budget_version_id = p_budget_version_id
AND pp.project_id = bv.project_id
AND pfo.project_id = pp.project_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT assignment_id
FROM pa_project_assignments
WHERE project_id = p_project_id
AND bill_rate_override is not null);
CURSOR GROUP_TO_INSERT_INTO_PA_GL_BL IS
SELECT RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
PERIOD_NAME,
SUM(QUANTITY),
TXN_CURRENCY_CODE,
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
BILL_MARKUP_PERCENTAGE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
COST_IND_COMPILED_SET_ID
FROM pa_fp_rollup_tmp
GROUP BY resource_assignment_id,
txn_currency_code,
start_date,
end_date,
period_name,
BILL_MARKUP_PERCENTAGE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
COST_IND_COMPILED_SET_ID;
CURSOR GROUP_TO_INSERT_INTO_NTP_BL IS
SELECT RESOURCE_ASSIGNMENT_ID,
MIN(START_DATE),
MAX(END_DATE),
NULL, --PERIOD_NAME,
SUM(QUANTITY),
TXN_CURRENCY_CODE,
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
NULL, --BILL_MARKUP_PERCENTAGE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
NULL --COST_IND_COMPILED_SET_ID
FROM pa_fp_rollup_tmp
GROUP BY resource_assignment_id,
txn_currency_code,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE;
SELECT RESOURCE_ASSIGNMENT_ID,
MIN(START_DATE),
MAX(END_DATE),
NULL, --PERIOD_NAME,
SUM(QUANTITY),
TXN_CURRENCY_CODE,
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
NULL, --BILL_MARKUP_PERCENTAGE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
NULL --COST_IND_COMPILED_SET_ID
FROM pa_fp_rollup_tmp tmp
GROUP BY resource_assignment_id,
txn_currency_code,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE
HAVING ( SELECT count(*)
FROM pa_budget_lines bl
WHERE tmp.resource_assignment_id = bl.resource_assignment_id
AND tmp.txn_currency_code = bl.txn_currency_code ) = 0;
SELECT RESOURCE_ASSIGNMENT_ID,
MIN(START_DATE),
MAX(END_DATE),
NULL, --PERIOD_NAME,
SUM(QUANTITY),
TXN_CURRENCY_CODE,
SUM(TXN_RAW_COST),
SUM(TXN_BURDENED_COST),
SUM(TXN_REVENUE),
NULL, --BILL_MARKUP_PERCENTAGE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
NULL --COST_IND_COMPILED_SET_ID
FROM pa_fp_rollup_tmp tmp
GROUP BY resource_assignment_id,
txn_currency_code,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE
HAVING ( SELECT count(*)
FROM pa_budget_lines bl
WHERE tmp.resource_assignment_id = bl.resource_assignment_id
AND tmp.txn_currency_code = bl.txn_currency_code ) > 0;
SELECT tmp1.resource_assignment_id,
tmp1.start_date,
tmp1.txn_currency_code,
tmp1.revenue_rejection_code
FROM pa_fp_rollup_tmp tmp1,
pa_fp_rollup_tmp tmp2
WHERE tmp2.txn_revenue IS NOT NULL
AND tmp1.revenue_rejection_code IS NOT NULL
AND tmp1.resource_assignment_id = tmp2.resource_assignment_id
AND tmp1.txn_currency_code = tmp2.txn_currency_code
AND tmp1.start_date = tmp2.start_date;
l_program_update_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
l_insert_Txn_Currency_Code VARCHAR2(100); -- Bug 4615589
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
l_deleted_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
l_t_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
are deleted completely for the target version. when ret_manual_lines flag is 'Y', for
budget version,all budget lines that under resource assignment whose transaction source
code is NOT NULL should be deleted. for forecast version, 'P' and 'G' time phased, all
ETC budget lines should be deleted; 'N' time phased, ETC should be deleted or negated.*/
DELETE FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND budget_line_id IN
(SELECT bl.budget_line_id
FROM pa_budget_lines bl, pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND bl.budget_version_id = p_budget_version_id
AND ra.transaction_source_code IS NOT NULL
AND ra.resource_assignment_id = bl.resource_assignment_id);
DELETE FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND budget_line_id IN
(SELECT bl.budget_line_id
FROM pa_budget_lines bl, pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND bl.budget_version_id = p_budget_version_id
AND ra.transaction_source_code IS NOT NULL
AND ra.resource_assignment_id = bl.resource_assignment_id
AND bl.start_date > p_actuals_thru_date);
DELETE FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND NVL(init_quantity,0) = 0
AND NVL(init_raw_cost,0) = 0
AND NVL(init_burdened_cost,0) = 0
AND NVL(init_revenue,0) = 0;
UPDATE pa_budget_lines
SET quantity = init_quantity,
txn_raw_cost = txn_init_raw_cost,
txn_burdened_cost = txn_init_burdened_cost,
txn_revenue = txn_init_revenue,
project_raw_cost = project_init_raw_cost,
project_burdened_cost = project_init_burdened_cost,
project_revenue = project_init_revenue,
raw_cost = init_raw_cost,
burdened_cost = init_burdened_cost,
revenue = init_revenue,
txn_cost_rate_override = DECODE(NVL(init_quantity,0),0,NULL,txn_init_raw_cost/init_quantity),
txn_bill_rate_override = DECODE(NVL(init_quantity,0),0,NULL,txn_init_revenue/init_quantity),
project_cost_exchange_rate = DECODE(NVL(txn_init_raw_cost,0),0,NULL,project_init_raw_cost/txn_init_raw_cost),
project_rev_exchange_rate = DECODE(NVL(txn_init_revenue,0),0,NULL,project_init_revenue/txn_init_revenue),
projfunc_cost_exchange_rate = DECODE(NVL(txn_init_raw_cost,0),0,NULL,init_raw_cost/txn_init_raw_cost),
projfunc_rev_exchange_rate = DECODE(NVL(txn_init_revenue,0),0,NULL,init_revenue/txn_init_revenue)
WHERE budget_version_id = p_budget_version_id
AND budget_line_id IN
(SELECT bl.budget_line_id
FROM pa_budget_lines bl, pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND bl.budget_version_id = p_budget_version_id
AND ra.transaction_source_code IS NOT NULL
AND ra.resource_assignment_id = bl.resource_assignment_id);
l_budget_lines_tot_tbl.DELETE;
SELECT p_type.SYSTEM_PERSON_TYPE
INTO l_person_type_code_tab(i)
FROM PER_PERSON_TYPES p_type,
PER_PERSON_TYPE_USAGES_F p_usg
WHERE p_type.SYSTEM_PERSON_TYPE IN ('EMP', 'CWK')
AND p_type.PERSON_TYPE_ID = p_usg.PERSON_TYPE_ID
AND l_proj_person_id(i) = p_usg.PERSON_ID
AND l_proj_start_date(i) BETWEEN p_usg.EFFECTIVE_START_DATE AND p_usg.EFFECTIVE_END_DATE;
SELECT p_asg.vendor_id
INTO l_vendor_id_tab(i)
FROM PER_ALL_ASSIGNMENTS_F p_asg
WHERE l_proj_person_id(i) = p_asg.PERSON_ID
AND l_proj_start_date(i) BETWEEN p_asg.EFFECTIVE_START_DATE AND p_asg.EFFECTIVE_END_DATE
AND p_asg.PRIMARY_FLAG = 'Y';
SELECT PR.DEFAULT_JOB_ID,PJ.JOB_GROUP_ID
INTO l_proj_fcst_job_id(i),l_proj_fcst_job_group_id(i)
FROM PA_PROJECT_ROLE_TYPES PR, PER_JOBS PJ
WHERE PR.PROJECT_ROLE_ID = l_proj_project_role_id(i)
AND PJ.JOB_ID = PR.DEFAULT_JOB_ID;
DELETE FROM PA_RES_LIST_MAP_TMP1;
DELETE FROM PA_RES_LIST_MAP_TMP4;
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ count(*) INTO l_count
FROM PA_RES_LIST_MAP_TMP4
WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
SELECT count(*) INTO l_count
FROM PA_RES_LIST_MAP_TMP4;
/* Calling the API to update the tmp4
table with resource_assignment_id */
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_called_mode => p_called_mode,
p_msg => 'Before calling
pa_fp_gen_budget_amt_pub.update_res_asg',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_STRU_SHARING_CODE => l_stru_sharing_code,
P_GEN_SRC_CODE => 'RESOURCE_SCHEDULE',
P_FP_COLS_REC => p_FP_COLS_REC,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
pa_fp_gen_budget_amt_pub.update_res_asg: '
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
l_proj_res_assgn_id_tab.delete;
l_proj_exp_organization_id_tab.delete;
l_proj_assgn_id_tab.delete;
l_proj_bill_rate_override_tab.delete;
l_proj_bill_rate_cur_ovrd_tab.delete;
SELECT tmp4.TXN_SOURCE_ID,
tmp4.ORGANIZATION_ID,
tmp4.TXN_RESOURCE_ASSIGNMENT_ID,
PA.BILL_RATE_OVERRIDE, -- M-Closeout ER: Bill Rate Override ER
PA.BILL_RATE_CURR_OVERRIDE, -- M-Closeout ER: Bill Rate Override ER
nvl(WB.BILLABLE_CAPITALIZABLE_FLAG, 'N') -- M-Closeout ER: Honor billability flag ER
BULK COLLECT
INTO l_proj_assgn_id_tab,
l_proj_exp_organization_id_tab,
l_proj_res_assgn_id_tab,
l_proj_bill_rate_override_tab, -- M-Closeout ER: Bill Rate Override ER
l_proj_bill_rate_cur_ovrd_tab, -- M-Closeout ER: Bill Rate Override ER
l_proj_billable_flag -- M-Closeout ER: Honor billability flag ER
FROM PA_RES_LIST_MAP_TMP4 tmp4,
PA_PROJECT_ASSIGNMENTS PA,
PA_WORK_TYPES_B WB
WHERE tmp4.txn_source_id = pa.assignment_id
AND WB.WORK_TYPE_ID = PA.WORK_TYPE_ID(+);
DELETE FROM pa_fp_rollup_tmp;
l_budget_lines_tbl.delete;
l_rt_forecast_item_id_tab.delete;
l_rt_pd_name_tab.delete;
l_rt_start_date_tab.delete;
l_rt_end_date_tab.delete;
l_rt_qty_tab.delete;
l_rt_res_assignment_id_tab.delete;
l_rt_uom_tab.delete;
l_rt_res_class_code_tab.delete;
l_rt_organization_id_tab.delete;
l_rt_job_id_tab.delete;
l_rt_person_id_tab.delete;
l_rt_expenditure_type_tab.delete;
l_rt_non_labor_resource_tab.delete;
l_rt_bom_resource_id_tab.delete;
l_rt_inventory_item_id_tab.delete;
l_rt_item_category_id_tab.delete;
l_rt_mfc_cost_type_id_tab.delete;
l_rt_rate_expenditure_type_tab.delete;
l_rt_rate_based_flag_tab.delete;
l_rt_rate_exp_org_id_tab.delete;
l_rt_res_format_id_tab.delete;
l_insert_Txn_Currency_Code := l_Final_Txn_Currency_Code; -- Bug 4615589
l_insert_Txn_Currency_Code := x_cost_txn_curr_code; -- Bug 4615589
' to '||l_insert_Txn_Currency_Code, -- Bug 4615589
p_module_name => l_module_name,
p_log_level => 5);
,p_project_curr_code => l_insert_Txn_Currency_Code -- Bug 4615589 TO currency code
,p_project_rate_type => l_Final_txn_rate_type
,p_project_rate_date => l_Final_txn_rate_date
,p_project_exch_rate => l_Final_txn_exch_rate
,p_project_raw_cost => x_dummy_cost
,p_projfunc_curr_code => x_rev_txn_curr_code -- 4615656: Should convert based on PC rate type
,p_projfunc_cost_rate_type => x_dummy_rate_type
,p_projfunc_cost_rate_date => x_dummy_rate_date
,p_projfunc_cost_exch_rate => x_dummy_exch_rate
,p_projfunc_raw_cost => x_dummy_cost
,p_system_linkage => 'NER'
,p_structure_version_id => NULL -- always NULL for finplan
,p_status => l_status
,p_stage => l_stage) ;
,p_value3 => l_insert_Txn_Currency_Code -- Bug 4615589
,p_token4 => 'CONVERSION_TYPE'
,p_value4 => l_Final_txn_rate_type
,p_token5 => 'CONVERSION_DATE'
,p_value5 => l_Final_txn_rate_date
);
x_raw_revenue := pa_currency.round_trans_currency_amt1(x_raw_revenue,l_insert_Txn_Currency_Code); -- Bug 4615589
l_insert_Txn_Currency_Code := x_cost_txn_curr_code; -- Bug 4615589
p_msg => 'txn currency code:'||l_insert_Txn_Currency_Code,
p_module_name => l_module_name,
p_log_level => 5);
INSERT INTO pa_fp_rollup_tmp(
RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
BILL_MARKUP_PERCENTAGE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
COST_IND_COMPILED_SET_ID,
BUDGET_LINE_ID, -- Added for Bug 4549862
BILLABLE_FLAG, -- Added for Bug 4549862
BUDGET_VERSION_ID ) -- Added for Bug 6207688
VALUES(
l_rt_res_assignment_id_tab(k),
l_rt_start_date_tab(k),
l_rt_end_date_tab(k),
l_rt_pd_name_tab(k),
l_rt_qty_tab(k),
l_insert_Txn_Currency_Code, -- Bug 4615589
x_raw_cost,
x_burden_cost,
x_raw_revenue,
x_bill_markup_percentage,
x_raw_cost_rejection_code,
x_burden_cost_rejection_code,
x_revenue_rejection_code,
x_cost_ind_compiled_set_id,
l_bl_id_counter, -- Added for Bug 4549862
l_proj_billable_flag(j), -- Added for Bug 4549862
P_BUDGET_VERSION_ID ); -- Added for Bug 6207688
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(COST_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE cost_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET TXN_RAW_COST = NULL,
PROJECT_RAW_COST = NULL,
PROJFUNC_RAW_COST = NULL,
COST_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(BURDEN_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE burden_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET TXN_BURDENED_COST = NULL,
PROJECT_BURDENED_COST = NULL,
PROJFUNC_BURDENED_COST = NULL,
BURDEN_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(PC_CUR_CONV_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE pc_cur_conv_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET PROJECT_RAW_COST = NULL,
PROJECT_BURDENED_COST = NULL,
PROJECT_REVENUE = NULL,
PC_CUR_CONV_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(PFC_CUR_CONV_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE pfc_cur_conv_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET PROJFUNC_RAW_COST = NULL,
PROJFUNC_BURDENED_COST = NULL,
PROJFUNC_REVENUE = NULL,
PFC_CUR_CONV_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(COST_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE cost_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET TXN_RAW_COST = NULL,
COST_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(BURDEN_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE burden_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET TXN_BURDENED_COST = NULL,
BURDEN_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
SELECT DISTINCT
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
MIN(REVENUE_REJECTION_CODE)
BULK COLLECT INTO
l_rej_code_ra_id_tab,
l_rej_code_txn_currency_tab,
l_rej_code_msg_name_tab
FROM pa_fp_rollup_tmp
WHERE revenue_rejection_code is not null
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE;
UPDATE pa_fp_rollup_tmp
SET TXN_REVENUE = NULL,
REVENUE_REJECTION_CODE = l_rej_code_msg_name_tab(i)
WHERE resource_assignment_id = l_rej_code_ra_id_tab(i)
AND txn_currency_code = l_rej_code_txn_currency_tab(i);
UPDATE pa_fp_rollup_tmp
SET TXN_REVENUE = NULL,
REVENUE_REJECTION_CODE = l_rej_revenue_rej_code_tab(m)
WHERE resource_assignment_id = l_rej_res_assignment_id_tab(m)
AND start_date = l_rej_start_date_tab(m)
AND txn_currency_code = l_rej_txn_currency_code_tab(m)
AND (txn_revenue is not null OR
revenue_rejection_code is null);
p_msg => 'Group temp table data by res asgmt, txn cur and period. Insert into budget lines',
p_module_name => l_module_name,
p_log_level => 5);
OPEN GROUP_TO_INSERT_INTO_PA_GL_BL;
FETCH GROUP_TO_INSERT_INTO_PA_GL_BL BULK COLLECT INTO
l_bl_RES_ASSIGNMENT_ID_tab,
l_bl_START_DATE_tab,
l_bl_END_DATE_tab,
l_bl_PERIOD_NAME_tab,
l_bl_QUANTITY_tab,
l_bl_TXN_CURRENCY_CODE_tab,
l_bl_TXN_RAW_COST_tab,
l_bl_TXN_BURDENED_COST_tab,
l_bl_TXN_REVENUE_tab,
l_bl_BILL_MARKUP_PERCENT_tab,
l_bl_COST_REJECTION_CODE_tab,
l_bl_BURDEN_REJECTION_CODE_tab,
l_bl_REV_REJECTION_CODE_tab,
l_bl_COST_IND_C_SET_ID_tab;
CLOSE GROUP_TO_INSERT_INTO_PA_GL_BL;
OPEN GROUP_TO_INSERT_INTO_NTP_BL;
FETCH GROUP_TO_INSERT_INTO_NTP_BL BULK COLLECT INTO
l_bl_RES_ASSIGNMENT_ID_tab,
l_bl_START_DATE_tab,
l_bl_END_DATE_tab,
l_bl_PERIOD_NAME_tab,
l_bl_QUANTITY_tab,
l_bl_TXN_CURRENCY_CODE_tab,
l_bl_TXN_RAW_COST_tab,
l_bl_TXN_BURDENED_COST_tab,
l_bl_TXN_REVENUE_tab,
l_bl_BILL_MARKUP_PERCENT_tab,
l_bl_COST_REJECTION_CODE_tab,
l_bl_BURDEN_REJECTION_CODE_tab,
l_bl_REV_REJECTION_CODE_tab,
l_bl_COST_IND_C_SET_ID_tab;
CLOSE GROUP_TO_INSERT_INTO_NTP_BL;
INSERT INTO PA_BUDGET_LINES(
RESOURCE_ASSIGNMENT_ID,
START_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_CURRENCY_CODE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
-- PROJECT_CURRENCY_CODE,
-- PROJFUNC_CURRENCY_CODE,
TXN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE , -- override rate on project assignment
BURDEN_COST_RATE_OVERRIDE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
TXN_MARKUP_PERCENT_OVERRIDE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
COST_IND_COMPILED_SET_ID)
VALUES(
l_bl_RES_ASSIGNMENT_ID_tab(bl_index),
l_bl_START_DATE_tab(bl_index),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_bl_END_DATE_tab(bl_index),
l_bl_PERIOD_NAME_tab(bl_index),
l_bl_QUANTITY_tab(bl_index),
l_bl_TXN_CURRENCY_CODE_tab(bl_index),
PA_BUDGET_LINES_S.nextval,
P_BUDGET_VERSION_ID,
DECODE(l_bl_QUANTITY_tab(bl_index), 0, NULL, l_bl_TXN_RAW_COST_tab(bl_index)/l_bl_QUANTITY_tab(bl_index)),
DECODE(l_bl_QUANTITY_tab(bl_index), 0, NULL, l_bl_TXN_REVENUE_tab(bl_index)/l_bl_QUANTITY_tab(bl_index)),
DECODE(l_bl_QUANTITY_tab(bl_index), 0, NULL, l_bl_TXN_BURDENED_COST_tab(bl_index)/l_bl_QUANTITY_tab(bl_index)),
l_bl_TXN_RAW_COST_tab(bl_index),
l_bl_TXN_BURDENED_COST_tab(bl_index),
l_bl_TXN_REVENUE_tab(bl_index),
l_bl_BILL_MARKUP_PERCENT_tab(bl_index),
l_bl_COST_REJECTION_CODE_tab(bl_index),
l_bl_BURDEN_REJECTION_CODE_tab(bl_index),
l_bl_REV_REJECTION_CODE_tab(bl_index),
l_bl_COST_IND_C_SET_ID_tab(bl_index));
UPDATE PA_BUDGET_LINES
SET LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
START_DATE = LEAST(START_DATE, l_upd_bl_START_DATE_tab(bl_index)),
END_DATE = GREATEST(END_DATE, l_upd_bl_END_DATE_tab(bl_index)),
QUANTITY =
DECODE(INIT_QUANTITY, null, l_upd_bl_QUANTITY_tab(bl_index),
INIT_QUANTITY + NVL(l_upd_bl_QUANTITY_tab(bl_index),0)),
TXN_RAW_COST =
DECODE(TXN_INIT_RAW_COST, null, l_upd_bl_TXN_RAW_COST_tab(bl_index),
TXN_INIT_RAW_COST + NVL(l_upd_bl_TXN_RAW_COST_tab(bl_index),0)),
TXN_BURDENED_COST =
DECODE(TXN_INIT_BURDENED_COST, null, l_upd_bl_TXN_BURDENED_COST_tab(bl_index),
TXN_INIT_BURDENED_COST + NVL(l_upd_bl_TXN_BURDENED_COST_tab(bl_index),0)),
TXN_REVENUE =
DECODE(TXN_INIT_REVENUE, null, l_upd_bl_TXN_REVENUE_tab(bl_index),
TXN_INIT_REVENUE + NVL(l_upd_bl_TXN_REVENUE_tab(bl_index),0)),
TXN_COST_RATE_OVERRIDE =
DECODE(l_upd_bl_QUANTITY_tab(bl_index), 0, NULL,
l_upd_bl_TXN_RAW_COST_tab(bl_index)/l_upd_bl_QUANTITY_tab(bl_index)),
-- override rate on project assignment
TXN_BILL_RATE_OVERRIDE =
DECODE(l_upd_bl_QUANTITY_tab(bl_index), 0, NULL,
l_upd_bl_TXN_REVENUE_tab(bl_index)/l_upd_bl_QUANTITY_tab(bl_index)),
BURDEN_COST_RATE_OVERRIDE =
DECODE(l_upd_bl_QUANTITY_tab(bl_index), 0, NULL,
l_upd_bl_TXN_BURDENED_COST_tab(bl_index)/l_upd_bl_QUANTITY_tab(bl_index)),
TXN_MARKUP_PERCENT_OVERRIDE = l_upd_bl_BILL_MARKUP_PRCNT_tab(bl_index),
COST_REJECTION_CODE = l_upd_bl_COST_REJ_CODE_tab(bl_index),
BURDEN_REJECTION_CODE = l_upd_bl_BURDEN_REJ_CODE_tab(bl_index),
REVENUE_REJECTION_CODE = l_upd_bl_REV_REJ_CODE_tab(bl_index),
COST_IND_COMPILED_SET_ID = l_upd_bl_COST_IND_C_SET_ID_tab(bl_index)
WHERE RESOURCE_ASSIGNMENT_ID = l_upd_bl_RES_ASSIGNMENT_ID_tab(bl_index)
AND TXN_CURRENCY_CODE = l_upd_bl_TXN_CURRENCY_CODE_tab(bl_index);
l_budget_lines_tbl.delete;
l_rt_forecast_item_id_tab.delete;
l_rt_pd_name_tab.delete;
l_rt_start_date_tab.delete;
l_rt_end_date_tab.delete;
l_rt_qty_tab.delete;
l_rt_exp_org_id_tab.delete;
l_rt_exp_organization_id_tab.delete;
l_rt_exp_func_raw_cst_rt_tab.delete;
l_rt_exp_func_raw_cst_tab.delete;
l_rt_exp_func_bur_cst_rt_tab.delete;
l_rt_exp_func_burdned_cst_tab.delete;
l_rt_projfunc_bill_rt_tab.delete;
l_rt_projfunc_raw_revenue_tab.delete;
l_rt_projfunc_raw_cst_tab.delete;
l_rt_projfunc_raw_cst_rt_tab.delete;
l_rt_projfunc_burdned_cst_tab.delete;
l_rt_projfunc_bd_cst_rt_tab.delete;
l_rt_rev_rejct_reason_tab.delete;
l_rt_cst_rejct_reason_tab.delete;
l_rt_burdned_rejct_reason_tab.delete;
l_rt_others_rejct_reason_tab.delete;
SELECT count(*)
INTO l_count
FROM pa_budget_lines
WHERE resource_assignment_id = l_proj_res_assgn_id_tab(j)
AND rownum <2;
INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
START_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_CURRENCY_CODE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE)
VALUES(l_proj_res_assgn_id_tab(j),
l_rt_start_date_tab(fp),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_rt_end_date_tab(fp),
l_rt_pd_name_tab(fp),
l_rt_qty_tab(fp),
l_project_currency_code,
PA_BUDGET_LINES_S.nextval,
P_BUDGET_VERSION_ID,
p_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
p_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
SELECT count(*)
INTO l_count1
FROM pa_budget_lines
WHERE resource_assignment_id = l_proj_res_assgn_id_tab(j)
AND txn_currency_code = l_project_currency_code
AND start_date = l_rt_start_date_tab(fp);
SELECT count(*)
INTO l_count1
FROM pa_budget_lines
WHERE resource_assignment_id = l_proj_res_assgn_id_tab(j)
AND txn_currency_code = l_project_currency_code;
INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
START_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_CURRENCY_CODE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE)
VALUES(l_proj_res_assgn_id_tab(j),
l_rt_start_date_tab(fp),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_rt_end_date_tab(fp),
l_rt_pd_name_tab(fp),
l_rt_qty_tab(fp),
l_project_currency_code,
PA_BUDGET_LINES_S.nextval,
P_BUDGET_VERSION_ID,
p_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
p_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
UPDATE pa_budget_lines
SET quantity = nvl(quantity,0) +
l_rt_qty_tab(fp)
WHERE resource_assignment_id = l_proj_res_assgn_id_tab(j)
AND txn_currency_code = l_project_currency_code
AND start_date = l_rt_start_date_tab(fp);
UPDATE pa_budget_lines
SET quantity = nvl(quantity,0) + l_rt_qty_tab(fp),
start_date = least(start_date, l_rt_start_date_tab(fp)),
end_date = greatest(end_date, l_rt_end_date_tab(fp))
WHERE resource_assignment_id = l_proj_res_assgn_id_tab(j)
AND txn_currency_code = l_project_currency_code;
delete from pa_fp_calc_amt_tmp2;
SELECT SUM(quantity)
INTO l_total_plan_quantity
FROM pa_budget_lines
WHERE resource_assignment_id = l_res_assgn_id_tmp_tab(i); /* Bug 4093872 - Column name corrected from budget_version_id to resource_assignment_id */
INSERT INTO pa_fp_calc_amt_tmp2(
resource_assignment_id,
total_plan_quantity)
VALUES(
l_res_assgn_id_tmp_tab(i),
l_total_plan_quantity);
l_delete_budget_lines_tab.extend;
l_delete_budget_lines_tab(i) := Null;
,p_delete_budget_lines_tab => l_delete_budget_lines_tab
,p_spread_amts_flag_tab => l_spread_amts_flag_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_txn_currency_override_tab => l_txn_currency_override_tab
,p_total_qty_tab => l_total_qty_tab
,p_addl_qty_tab => l_addl_qty_tab
,p_total_raw_cost_tab => l_total_raw_cost_tab
,p_addl_raw_cost_tab => l_addl_raw_cost_tab
,p_total_burdened_cost_tab => l_total_burdened_cost_tab
,p_addl_burdened_cost_tab => l_addl_burdened_cost_tab
,p_total_revenue_tab => l_total_revenue_tab
,p_addl_revenue_tab => l_addl_revenue_tab
,p_raw_cost_rate_tab => l_raw_cost_rate_tab
,p_rw_cost_rate_override_tab => l_rw_cost_rate_override_tab
,p_b_cost_rate_tab => l_b_cost_rate_tab
,p_b_cost_rate_override_tab => l_b_cost_rate_override_tab
,p_bill_rate_tab => l_bill_rate_tab
,p_bill_rate_override_tab => l_bill_rate_override_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,p_fp_task_billable_flag_tab => l_calc_billable_flag_tab /* Added for Bug 4548733 */
,p_raTxn_rollup_api_call_flag => l_raTxn_rollup_api_call_flag, --Added for IPM new entity ER
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT T.RESOURCE_LIST_MEMBER_ID,
MIN(T.TXN_PLANNING_START_DATE),
MAX(T.TXN_PLANNING_END_DATE)
FROM PA_RES_LIST_MAP_TMP4 T
WHERE NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_ASSIGNMENTS P
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(P.TASK_ID,0) = 0
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID)
GROUP BY T.RESOURCE_LIST_MEMBER_ID;
SELECT T.RESOURCE_LIST_MEMBER_ID,
NVL(T.TXN_TASK_ID,0),
MIN(T.TXN_PLANNING_START_DATE),
MAX(T.TXN_PLANNING_END_DATE)
FROM PA_RES_LIST_MAP_TMP4 T
WHERE NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID)
GROUP BY T.RESOURCE_LIST_MEMBER_ID,
NVL(T.TXN_TASK_ID,0);
SELECT T.RESOURCE_LIST_MEMBER_ID,
NVL(PAT.TOP_TASK_ID,0),
MIN(T.TXN_PLANNING_START_DATE),
MAX(T.TXN_PLANNING_END_DATE)
FROM PA_RES_LIST_MAP_TMP4 T,
PA_TASKS PAT
WHERE NVL(T.TXN_TASK_ID,0) > 0
AND NVL(T.TXN_TASK_ID,0) = PAT.TASK_ID
AND NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(T.TXN_TASK_ID,0) > 0
AND TS.TASK_ID = NVL(T.TXN_TASK_ID,0)
AND NVL(TS.TOP_TASK_ID,0) = NVL(P.TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID)
GROUP BY T.RESOURCE_LIST_MEMBER_ID,
NVL(PAT.TOP_TASK_ID,0)
UNION
SELECT T.RESOURCE_LIST_MEMBER_ID,
0,
MIN(T.TXN_PLANNING_START_DATE),
MAX(T.TXN_PLANNING_END_DATE)
FROM PA_RES_LIST_MAP_TMP4 T
WHERE NVL(T.TXN_TASK_ID,0) = 0
AND NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(T.TXN_TASK_ID,0) = 0
AND NVL(P.TASK_ID,0) = 0
AND P.RESOURCE_LIST_MEMBER_ID = T.RESOURCE_LIST_MEMBER_ID
AND P.PROJECT_ASSIGNMENT_ID = -1)
GROUP BY T.RESOURCE_LIST_MEMBER_ID,
0;
SELECT T.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
NVL(V1.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id,
NVL(V1.MAPPED_FIN_TASK_VERSION_ID,0) mapped_fin_task_version_id,
MIN(T.TXN_PLANNING_START_DATE) txn_planning_start_date,
MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
FROM PA_RES_LIST_MAP_TMP4 T,
PA_MAP_WP_TO_FIN_TASKS_V V1
WHERE NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P,PA_MAP_WP_TO_FIN_TASKS_V V
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND nvl(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(V.MAPPED_FIN_TASK_ID,0) = NVL(P.TASK_ID,0))
AND V1.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND NVL(T.TXN_TASK_ID,0) = NVL(V1.PROJ_ELEMENT_ID,0)
AND NVL(T.TXN_TASK_ID,0) > 0
GROUP BY
T.RESOURCE_LIST_MEMBER_ID,
NVL(V1.MAPPED_FIN_TASK_ID,0),
NVL(V1.MAPPED_FIN_TASK_VERSION_ID,0)
union
SELECT T.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
0 mapped_fin_task_id,
0 mapped_fin_task_version_id,
MIN(T.TXN_PLANNING_START_DATE) txn_planning_start_date,
MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
FROM PA_RES_LIST_MAP_TMP4 T
WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = 0 )
GROUP BY T.RESOURCE_LIST_MEMBER_ID,
0,
0;
SELECT T.RESOURCE_LIST_MEMBER_ID,
NVL(PAT.TOP_TASK_ID,0),
NVL(pa_proj_elements_utils.get_task_version_id(
v1.MAPPED_FIN_STR_VERSION_ID,pat.top_task_id),0),
MIN(T.TXN_PLANNING_START_DATE),
MAX(T.TXN_PLANNING_END_DATE)
FROM PA_RES_LIST_MAP_TMP4 T,
PA_TASKS PAT,
PA_MAP_WP_TO_FIN_TASKS_V V1
WHERE NVL(V1.MAPPED_FIN_TASK_ID,0) = PAT.TASK_ID (+)
AND NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS,PA_MAP_WP_TO_FIN_TASKS_V V
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
AND TS.TASK_ID(+) = NVL(V.MAPPED_FIN_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(TS.TOP_TASK_ID,0) = NVL(P.TASK_ID,0))
AND V1.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND NVL(T.TXN_TASK_ID,0) = NVL(V1.PROJ_ELEMENT_ID,0)
AND NVL(T.TXN_TASK_ID,0) > 0
GROUP BY T.RESOURCE_LIST_MEMBER_ID,
NVL(PAT.TOP_TASK_ID,0),
NVL(pa_proj_elements_utils.get_task_version_id(
v1.MAPPED_FIN_STR_VERSION_ID,pat.top_task_id),0)
union
SELECT T.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
0,
0,
MIN(T.TXN_PLANNING_START_DATE) txn_planning_start_date,
MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
FROM PA_RES_LIST_MAP_TMP4 T
WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
NOT EXISTS
(SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
FROM PA_RESOURCE_ASSIGNMENTS P
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = 0)
GROUP BY T.RESOURCE_LIST_MEMBER_ID, 0,
0;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
/* Performance-Variables to replace the literals in the Insert into
pa_resource_assignments stmts. */
l_task_id_01 NUMBER:=0;
SELECT NVL(wp_version_flag,'N')
INTO l_wp_version_flag
FROM pa_budget_versions
WHERE budget_version_id=P_BUDGET_VERSION_ID;
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,
PROJECT_ASSIGNMENT_ID,
resource_assignment_type,
record_version_number,
planning_start_date,
planning_end_date,
transaction_source_code)
VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
l_task_id_01,
l_res_list_member_id(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_proj_asg_id_minus1 ,
l_res_as_type_USER_ENTERED,
l_rec_ver_number_1 ,
l_start_date_tab(i),
l_end_date_tab(i),
l_gen_src_code )
RETURNING resource_assignment_id BULK COLLECT INTO l_ins_ra_id_tbl;
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,
PROJECT_ASSIGNMENT_ID,
resource_assignment_type,
record_version_number,
planning_start_date,
planning_end_date,
transaction_source_code)
VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
l_task_id(i),
l_res_list_member_id(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_proj_asg_id_minus1,
l_res_as_type_USER_ENTERED,
l_rec_ver_number_1,
l_start_date_tab(i),
l_end_date_tab(i),
l_gen_src_code )
RETURNING resource_assignment_id BULK COLLECT INTO l_ins_ra_id_tbl;
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,
PROJECT_ASSIGNMENT_ID,
resource_assignment_type,
record_version_number,
planning_start_date,
planning_end_date,
transaction_source_code)
VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
l_task_id(i),
l_res_list_member_id(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_proj_asg_id_minus1,
l_res_as_type_USER_ENTERED,
l_rec_ver_number_1,
l_start_date_tab(i),
l_end_date_tab(i),
l_gen_src_code )
RETURNING resource_assignment_id BULK COLLECT INTO l_ins_ra_id_tbl;
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,
PROJECT_ASSIGNMENT_ID,
resource_assignment_type,
planning_start_Date,
planning_end_date,
record_version_number,
wbs_element_version_id,
transaction_source_code)
VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
l_task_id(i),
l_res_list_member_id(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_proj_asg_id_minus1,
l_res_as_type_USER_ENTERED,
l_start_date_tab(i),
l_end_date_tab(i),
l_rec_ver_number_1,
DECODE(l_wp_version_flag,'Y',l_mapped_fin_task_version_id(i),
NULL),
l_gen_src_code )
RETURNING resource_assignment_id BULK COLLECT INTO l_ins_ra_id_tbl;
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,
PROJECT_ASSIGNMENT_ID,
resource_assignment_type,
record_version_number,
wbs_element_version_id,
planning_start_date,
planning_end_date,
transaction_source_code)
VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
P_BUDGET_VERSION_ID,
P_PROJECT_ID,
l_task_id(i),
l_res_list_member_id(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_proj_asg_id_minus1,
l_res_as_type_USER_ENTERED,
l_rec_ver_number_1,
DECODE(l_wp_version_flag,'Y',l_mapped_fin_task_version_id(i),
NULL),
l_start_date_tab(i),
l_end_date_tab(i),
l_gen_src_code )
RETURNING resource_assignment_id BULK COLLECT INTO l_ins_ra_id_tbl;
DELETE from pa_res_list_map_tmp1;
INSERT INTO pa_res_list_map_tmp1
(txn_resource_list_member_id)
VALUES
(l_res_list_member_id(pp));
l_unique_rlm_id_tab.delete;
SELECT DISTINCT txn_resource_list_member_id
BULK COLLECT
INTO l_unique_rlm_id_tab
FROM pa_res_list_map_tmp1;
DELETE FROM pa_res_list_map_tmp1;
UPDATE PA_RESOURCE_ASSIGNMENTS
SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
RES_TYPE_CODE = l_da_res_type_code_tab(i),
PERSON_ID = l_da_person_id_tab(i),
JOB_ID = l_da_job_id_tab(i),
PERSON_TYPE_CODE = l_da_person_type_code_tab(i),
NAMED_ROLE = l_da_named_role_tab(i),
BOM_RESOURCE_ID = l_da_bom_resource_id_tab(i),
NON_LABOR_RESOURCE = l_da_non_labor_resource_tab(i),
INVENTORY_ITEM_ID = l_da_inventory_item_id_tab(i),
ITEM_CATEGORY_ID = l_da_item_category_id_tab(i),
PROJECT_ROLE_ID = l_da_project_role_id_tab(i),
ORGANIZATION_ID = l_da_organization_id_tab(i),
FC_RES_TYPE_CODE = l_da_fc_res_type_code_tab(i),
EXPENDITURE_TYPE = l_da_expenditure_type_tab(i),
EXPENDITURE_CATEGORY = l_da_expenditure_category_tab(i),
EVENT_TYPE = l_da_event_type_tab(i),
REVENUE_CATEGORY_CODE = l_da_revenue_category_code_tab(i),
SUPPLIER_ID = l_da_supplier_id_tab(i),
SPREAD_CURVE_ID = l_da_spread_curve_id_tab(i),
ETC_METHOD_CODE = l_da_etc_method_code_tab(i),
MFC_COST_TYPE_ID = l_da_mfc_cost_type_id_tab(i),
INCURRED_BY_RES_FLAG = l_da_incurred_by_res_flag_tab(i),
INCUR_BY_RES_CLASS_CODE = l_da_incur_by_res_cls_code_tab(i),
INCUR_BY_ROLE_ID = l_da_incur_by_role_id_tab(i),
UNIT_OF_MEASURE = l_da_unit_of_measure_tab(i),
RATE_BASED_FLAG = l_da_rate_based_flag_tab(i),
RESOURCE_RATE_BASED_FLAG = l_da_rate_based_flag_tab(i), -- Added for IPM ER
RATE_EXPENDITURE_TYPE = l_da_rate_expenditure_type_tab(i),
RATE_EXP_FUNC_CURR_CODE = l_da_rate_func_curr_code_tab(i),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
RATE_EXPENDITURE_ORG_ID = l_da_org_id_tab(i)
WHERE budget_version_id = p_budget_version_id
AND RESOURCE_LIST_MEMBER_ID = l_da_resource_list_members_tab(i)
AND (resource_assignment_id
BETWEEN l_ins_ra_id_tbl(l_ins_ra_id_tbl.FIRST) AND l_ins_ra_id_tbl(l_ins_ra_id_tbl.LAST));
/* Procedure to update the reosurce_assignment_id
in the resource assignment table*/
PROCEDURE UPDATE_RES_ASG
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_STRU_SHARING_CODE IN PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE,
P_GEN_SRC_CODE IN PA_PROJ_FP_OPTIONS.GEN_ALL_SRC_CODE%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_WP_STRUCTURE_VER_ID IN PA_BUDGET_VERSIONS.PROJECT_STRUCTURE_VERSION_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG';
pa_debug.set_curr_function( p_function => 'UPDATE_RES_ASG'
,p_debug_mode => p_pa_debug_mode);
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
distinct P.RESOURCE_ASSIGNMENT_ID,
P.RESOURCE_LIST_MEMBER_ID
BULK COLLECT
INTO l_res_assgn_id_tab,
l_rlm_id_tab
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(P.TASK_ID,0) = 0
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i);
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID,
P.RESOURCE_LIST_MEMBER_ID,
NVL(T.TXN_TASK_ID,0)
BULK COLLECT
INTO l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_task_id_tab
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID,
P.RESOURCE_LIST_MEMBER_ID,
NVL(P.TASK_ID,0),
NVL(T.TXN_TASK_ID,0)
BULK COLLECT
INTO l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_top_task_id_tab,
l_txn_sub_task_id_tab
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T,
PA_TASKS TS
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND TS.TASK_ID(+) = NVL(T.TXN_TASK_ID,0)
AND NVL(P.TASK_ID,0) = NVL(TS.TOP_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4 tmp4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_sub_task_id_tab(i);
SELECT resource_assignment_id,
resource_list_member_id,
txn_task_id,
mapped_fin_task_id
BULK COLLECT INTO
l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_task_id_tab,
l_mapped_task_id_tab
FROM
(
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
NVL(T.TXN_TASK_ID,0) txn_task_id ,
NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T,
PA_MAP_WP_TO_FIN_TASKS_V V
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = NVL(V.MAPPED_FIN_TASK_ID,0)
AND NVL(T.TXN_TASK_ID,0) > 0
union
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
0 txn_task_id,
0 mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = 0 );
(p_msg => 'within update when share partial and planning at lowest task i:'
||i||'; ra id in cursor:'||l_res_assgn_id_tab(i)
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
SELECT resource_assignment_id,
resource_list_member_id,
txn_task_id,
mapped_fin_task_id
BULK COLLECT INTO
l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_task_id_tab,
l_mapped_task_id_tab
FROM
(
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
NVL(T.TXN_TASK_ID,0) txn_task_id,
NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T,
PA_MAP_WP_TO_FIN_TASKS_V V,
PA_TASKS TS
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND t.txn_task_id = v.PROJ_ELEMENT_ID
AND NVL(TS.top_TASK_ID,0) = NVL(p.task_id,0)
AND TS.TASK_ID(+) = NVL(V.MAPPED_FIN_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(T.TXN_TASK_ID,0) > 0
union
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
DISTINCT P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
0 txn_task_id,
0 mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = 0
AND NVL(T.TXN_TASK_ID,0) = NVL(P.TASK_ID,0) );
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
DELETE FROM pa_res_list_map_tmp4 tmp
WHERE EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
AND ra.transaction_source_code IS NULL
AND EXISTS
( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = ra.resource_assignment_id
AND rownum = 1 ));
DELETE FROM pa_res_list_map_tmp4 tmp
WHERE EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
AND ra.transaction_source_code IS NULL
AND EXISTS
( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = ra.resource_assignment_id
AND bl.start_date >= l_etc_start_date
AND rownum = 1 ));
SELECT txn_resource_assignment_id,
min(txn_planning_start_date),
max(txn_planning_end_date)
BULK COLLECT
INTO l_txn_res_asg_id_tab,
l_txn_plan_start_date_tab,
l_txn_plan_end_date_tab
FROM PA_RES_LIST_MAP_TMP4
--WHERE txn_budget_version_id = p_budget_version_id
GROUP BY txn_resource_assignment_id;
UPDATE PA_RESOURCE_ASSIGNMENTS
SET PLANNING_START_DATE = l_txn_plan_start_date_tab(j),
PLANNING_END_DATE = l_txn_plan_end_date_tab(j)
WHERE RESOURCE_ASSIGNMENT_ID = l_txn_res_asg_id_tab(j);
SELECT spread_curve_id
INTO l_spread_curve_id
FROM pa_spread_curves_b
WHERE spread_curve_code = 'FIXED_DATE';
UPDATE PA_RESOURCE_ASSIGNMENTS
SET SP_FIXED_DATE = PLANNING_START_DATE
WHERE SP_FIXED_DATE IS NULL
AND SPREAD_CURVE_ID = l_spread_curve_id
AND budget_version_id = p_budget_version_id
AND EXISTS ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_res_list_map_tmp4 tmp
WHERE tmp.txn_resource_assignment_id = resource_assignment_id
AND rownum = 1 );
UPDATE PA_RESOURCE_ASSIGNMENTS
SET transaction_source_code = p_gen_src_code,
sp_fixed_date = decode (p_gen_src_code, 'RESOURCE_SCHEDULE', NULL, sp_fixed_date),
spread_curve_id = decode (p_gen_src_code, 'RESOURCE_SCHEDULE', NULL, spread_curve_id)
WHERE budget_version_id = p_budget_version_id
AND EXISTS ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_res_list_map_tmp4 tmp
WHERE tmp.txn_resource_assignment_id = resource_assignment_id
AND rownum = 1 );
,p_procedure_name => 'UPDATE_RES_ASG');
END UPDATE_RES_ASG;
/*Procedure to delete the manually entered budget line records
PX_RES_ASG_ID_TAB ->this pl sql table will have res asg id
for which the budget lines has to be deleted.
PX_DELETED_RES_ASG_ID_TAB->this pl sql table will have res asg ids
for which the budget_lines are deleted by this API.
These two pl sql tables are used to make sure that
we are not deleting budget lines records that was generated
by the previous source in the same run.
*/
PROCEDURE DEL_MANUAL_BDGT_LINES
( P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
PX_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.DEL_MANUAL_BDGT_LINES';
FOR j IN 1..PX_DELETED_RES_ASG_ID_TAB.count LOOP
IF PX_RES_ASG_ID_TAB(i) = PX_DELETED_RES_ASG_ID_TAB(j) THEN
l_exist_flag := 'Y';
PX_DELETED_RES_ASG_ID_TAB.delete;
PX_DELETED_RES_ASG_ID_TAB(k) := l_del_res_asg_id_tab(k);
FORALL i in 1..PX_DELETED_RES_ASG_ID_TAB.count
DELETE FROM PA_BUDGET_LINES
WHERE RESOURCE_ASSIGNMENT_ID = PX_DELETED_RES_ASG_ID_TAB(i);
PROCEDURE UPDATE_INIT_AMOUNTS
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
--this pl/sql table will have newly created res_asg_id from the source
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_INIT_AMOUNTS';
pa_debug.set_curr_function( p_function => 'UPDATE_INIT_AMOUNTS'
,p_debug_mode => p_pa_debug_mode);
UPDATE PA_BUDGET_LINES
SET INIT_QUANTITY = QUANTITY,
INIT_QUANTITY_SOURCE = QUANTITY_SOURCE,
INIT_RAW_COST = RAW_COST,
INIT_BURDENED_COST = BURDENED_COST,
INIT_REVENUE = REVENUE,
INIT_RAW_COST_SOURCE = RAW_COST_SOURCE,
INIT_BURDENED_COST_SOURCE = BURDENED_COST_SOURCE,
INIT_REVENUE_SOURCE = REVENUE_SOURCE,
PROJECT_INIT_RAW_COST = PROJECT_RAW_COST,
PROJECT_INIT_BURDENED_COST = PROJECT_BURDENED_COST,
PROJECT_INIT_REVENUE = PROJECT_REVENUE,
TXN_INIT_RAW_COST = TXN_RAW_COST,
TXN_INIT_BURDENED_COST = TXN_BURDENED_COST,
TXN_INIT_REVENUE = TXN_REVENUE
WHERE RESOURCE_ASSIGNMENT_ID = P_RES_ASG_ID_TAB(i);
,p_procedure_name => 'UPDATE_INIT_AMOUNTS');
END UPDATE_INIT_AMOUNTS;
/* Procedure to update the latest amount
generation date in the budget versions table*/
PROCEDURE UPDATE_BV_FOR_GEN_DATE
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_ETC_START_DATE IN PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
pa_debug.set_curr_function( p_function => 'UPDATE_BV_FOR_GEN_DATE'
,p_debug_mode => p_pa_debug_mode);
UPDATE PA_BUDGET_VERSIONS
SET LAST_AMT_GEN_DATE = l_sysdate,
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
CREATION_DATE = l_sysdate,
CREATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
record_version_number = nvl(record_version_number,0)+1,
ETC_START_DATE = p_etc_start_date
WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
,p_procedure_name => 'UPDATE_BV_FOR_GEN_DATE');
END UPDATE_BV_FOR_GEN_DATE;
SELECT DISTINCT TXN_RESOURCE_ASSIGNMENT_ID
BULK COLLECT
INTO PX_GEN_RES_ASG_ID_TAB
FROM PA_RES_LIST_MAP_TMP4;
SELECT DISTINCT TXN_RESOURCE_ASSIGNMENT_ID
BULK COLLECT
INTO l_cmt_res_id_tab
FROM PA_RES_LIST_MAP_TMP4;
PROCEDURE INSERT_TXN_CURRENCY
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.INSERT_TXN_CURRENCY';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
pa_debug.set_curr_function( p_function => 'INSERT_TXN_CURRENCY'
,p_debug_mode => p_pa_debug_mode);
SELECT nvl(wp_version_flag,'N')
INTO l_wp_version_flag
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT bv.budget_version_id
,pfo.proj_fp_options_id
BULK COLLECT INTO
l_budget_version_id_tbl,
l_proj_fp_options_id_tbl
FROM pa_budget_versions bv,
--bug 3919127 pa_proj_elem_ver_structure ver,
pa_proj_fp_options pfo
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
/* bug 3919127
AND bv.project_id = ver.project_id
AND bv.project_structure_version_id = ver.element_version_id
AND (PA_WORKPLAN_ATTR_UTILS.Check_Wp_Versioning_Enabled(p_project_id) = 'N' OR
ver.status_code IN('STRUCTURE_WORKING'))
*/
AND pfo.project_id = p_project_id
AND pfo.fin_plan_version_id = bv.budget_version_id;
SELECT proj_fp_options_id,
null
INTO l_proj_fp_options_id_tbl(nvl(l_proj_fp_options_id_tbl.count,0) + 1),
l_budget_version_id_tbl(nvl(l_proj_fp_options_id_tbl.count,0) + 1)
FROM pa_proj_fp_options
WHERE project_id = p_project_id AND
fin_plan_type_id = l_fp_cols_rec.X_FIN_PLAN_TYPE_ID AND
fin_plan_option_level_code = 'PLAN_TYPE';
l_txn_curr_code_tab.DELETE; -- this is really not necessary but kept here for clarity
SELECT DISTINCT BL.TXN_CURRENCY_CODE
BULK COLLECT
INTO l_txn_curr_code_tab
FROM PA_BUDGET_LINES BL
WHERE BL.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NOT EXISTS
(SELECT 1
FROM PA_FP_TXN_CURRENCIES TC
WHERE TC.proj_fp_options_id = l_proj_fp_options_id_tbl(i) AND
TC.txn_currency_code = BL.txn_currency_code);
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_tbl(i),
l_fp_cols_rec.X_PROJECT_ID,
l_fp_cols_rec.X_FIN_PLAN_TYPE_ID,
l_budget_version_id_tbl(i),
l_txn_curr_code_tab(j),
'N',
'N',
'N',
Decode(l_txn_curr_code_tab(j),l_pc,'Y','N'),
Decode(l_txn_curr_code_tab(j),l_pfc,'Y','N'),
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_last_updated_by,
l_sysdate );
,p_procedure_name => 'INSERT_TXN_CURRENCY');
END INSERT_TXN_CURRENCY;
UPDATE PA_BUDGET_LINES
SET RAW_COST = null,
BURDENED_COST = null,
PROJECT_RAW_COST = null,
PROJECT_BURDENED_COST = null,
TXN_RAW_COST = null,
TXN_BURDENED_COST = null,
PROJFUNC_COST_RATE_TYPE = null,
PROJFUNC_COST_EXCHANGE_RATE = null,
PROJFUNC_COST_RATE_DATE_TYPE= null,
PROJFUNC_COST_RATE_DATE = null,
PROJECT_COST_RATE_TYPE = null,
PROJECT_COST_EXCHANGE_RATE = null,
PROJECT_COST_RATE_DATE_TYPE = null,
PROJECT_COST_RATE_DATE = null
WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
P_COMMIT_FLAG IN VARCHAR2,
P_INIT_MSG_FLAG IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.GEN_REV_BDGT_AMT_RES_SCH_WRP';
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
PX_DELETED_RES_ASG_ID_TAB => PX_DELETED_RES_ASG_ID_TAB,
P_COMMIT_FLAG => P_COMMIT_FLAG,
P_INIT_MSG_FLAG => P_INIT_MSG_FLAG,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N2)*/
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.total_plan_quantity,
ra.task_id,
ra.resource_list_member_id,
P_FP_COLS_REC.x_project_currency_code,
ra.planning_start_date,
ra.unit_of_measure,
ra.rate_based_flag,
ra.resource_class_code,
ra.organization_id,
ra.job_id,
ra.person_id,
ra.expenditure_type,
ra.non_labor_resource,
ra.bom_resource_id,
ra.inventory_item_id,
ra.item_category_id,
ra.mfc_cost_type_id,
ra.organization_id,
null,
ra.rate_expenditure_org_id,
ra.rate_expenditure_type,
ra.organization_id,
ra.project_assignment_id,
proj.assign_precedes_task,
proj.bill_job_group_id,
proj.carrying_out_organization_id,
proj.multi_currency_billing_flag,
proj.org_id,
proj.non_labor_bill_rate_org_id,
proj.non_labor_schedule_discount,
proj.non_labor_schedule_fixed_date,
proj.project_type,
proj.labor_bill_rate_org_id,
t.LABOR_SCHEDULE_FIXED_DATE,
t.top_task_id,
t.scheduled_start_date,
t.labor_schedule_discount,
t.labor_sch_type,
t.non_labor_sch_type,
decode(fp.use_planning_rates_flag,'N',fp.res_class_bill_rate_sch_id,
fp.rev_res_class_rate_sch_id),
decode(fp.use_planning_rates_flag,'N',fp.res_class_raw_cost_sch_id,
NULL),
res_format_id
BULK COLLECT INTO
l_res_asg_id_tab,
l_ra_quantity_tab,
l_task_id_tab,
l_res_list_member_id_tab,
l_txn_currency_code_tab,
l_ra_start_date_tab,
l_uom_tab,
l_rate_based_flag_tab,
l_resource_class_code_tab,
l_organization_id_tab,
l_job_id_tab,
l_person_id_tab,
l_expenditure_type_tab,
l_non_labor_resource_tab,
l_bom_resource_id_tab,
l_inventory_item_id_tab,
l_item_category_id_tab,
l_mfc_cost_type_id_tab,
l_rate_incur_by_organz_id_tab,
l_rate_ovrd_to_organz_id_tab,
l_rate_expenditure_org_id_tab,
l_rate_expenditure_type_tab,
l_rate_organization_id_tab,
l_project_assignment_id_tab,
l_assign_precedes_task_tab,
l_bill_job_group_id_tab,
l_carry_out_organiz_id_tab,
l_multi_currency_bill_flag_tab,
l_org_id_tab,
l_non_lab_bill_rate_org_id_tab,
l_non_lab_sch_discount_tab,
l_non_lab_sch_fixed_date_tab,
l_project_type_tab,
l_lab_bill_rate_org_id_tab,
l_lab_sch_FIXED_DATE_tab,
l_top_task_id_tab,
l_scheduled_start_date_tab,
l_labor_scheduled_discount_tab,
l_labor_sch_type_tab,
l_non_labor_sch_type_tab,
l_rev_res_class_rt_sch_id_tab,
l_cost_res_class_rt_sch_id_tab,
l_res_format_id_tab
FROM pa_fp_calc_amt_tmp2 tmp, pa_resource_assignments ra,
pa_projects_all proj, pa_tasks t,
pa_proj_fp_options fp,
pa_resource_list_members rlm
WHERE tmp.resource_assignment_id = ra.resource_assignment_id
AND ra.project_id = proj.project_id
AND ra.task_id = t.task_id(+)
AND fp.fin_plan_version_id = ra.budget_version_id
AND ra.resource_list_member_id = rlm.resource_list_member_id;
l_delete_budget_lines_tab.extend;
l_delete_budget_lines_tab(i) := Null;
,p_delete_budget_lines_tab => l_delete_budget_lines_tab
,p_spread_amts_flag_tab => l_spread_amts_flag_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_txn_currency_override_tab => l_txn_currency_override_tab
,p_total_qty_tab => l_ra_quantity_tab --l_total_qty_tab
,p_addl_qty_tab => l_addl_qty_tab
,p_total_raw_cost_tab => l_total_raw_cost_tab
,p_addl_raw_cost_tab => l_addl_raw_cost_tab
,p_total_burdened_cost_tab => l_total_burdened_cost_tab
,p_addl_burdened_cost_tab => l_addl_burdened_cost_tab
,p_total_revenue_tab => l_total_revenue_tab
,p_addl_revenue_tab => l_addl_revenue_tab
,p_raw_cost_rate_tab => l_raw_cost_rate_tab
,p_rw_cost_rate_override_tab => l_rw_cost_rate_override_tab
,p_b_cost_rate_tab => l_b_cost_rate_tab
,p_b_cost_rate_override_tab => l_b_cost_rate_override_tab
,p_bill_rate_tab => l_bill_rate_tab
,p_bill_rate_override_tab => l_bill_rate_override_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
PX_GEN_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BUDGET_AMT_PUB.GEN_WP_REV_BDGT_AMT_WRP';
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
PX_DELETED_RES_ASG_ID_TAB => PX_DELETED_RES_ASG_ID_TAB,
P_INIT_MSG_FLAG => P_INIT_MSG_FLAG,
P_COMMIT_FLAG => P_COMMIT_FLAG,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.total_plan_quantity,
ra.task_id,
ra.resource_list_member_id,
tmp.txn_currency_code,
ra.planning_start_date,
ra.unit_of_measure,
ra.rate_based_flag,
ra.resource_class_code,
ra.organization_id,
ra.job_id,
ra.person_id,
ra.expenditure_type,
ra.non_labor_resource,
ra.bom_resource_id,
ra.inventory_item_id,
ra.item_category_id,
ra.mfc_cost_type_id,
ra.organization_id,
null,
ra.rate_expenditure_org_id,
ra.rate_expenditure_type,
ra.organization_id,
ra.project_assignment_id,
proj.assign_precedes_task,
proj.bill_job_group_id,
proj.carrying_out_organization_id,
proj.multi_currency_billing_flag,
proj.org_id,
proj.non_labor_bill_rate_org_id,
proj.non_labor_schedule_discount,
proj.non_labor_schedule_fixed_date,
proj.project_type,
proj.labor_bill_rate_org_id,
t.LABOR_SCHEDULE_FIXED_DATE,
t.top_task_id,
t.scheduled_start_date,
t.labor_schedule_discount,
t.labor_sch_type,
t.non_labor_sch_type,
decode(fp.use_planning_rates_flag,'N',fp.res_class_bill_rate_sch_id,
fp.rev_res_class_rate_sch_id),
decode(fp.use_planning_rates_flag,'N',fp.res_class_raw_cost_sch_id,
NULL),
res_format_id
BULK COLLECT INTO
l_res_asg_id_tab,
l_ra_quantity_tab,
l_task_id_tab,
l_res_list_member_id_tab,
l_txn_currency_code_tab,
l_ra_start_date_tab,
l_uom_tab,
l_rate_based_flag_tab,
l_resource_class_code_tab,
l_organization_id_tab,
l_job_id_tab,
l_person_id_tab,
l_expenditure_type_tab,
l_non_labor_resource_tab,
l_bom_resource_id_tab,
l_inventory_item_id_tab,
l_item_category_id_tab,
l_mfc_cost_type_id_tab,
l_rate_incur_by_organz_id_tab,
l_rate_ovrd_to_organz_id_tab,
l_rate_expenditure_org_id_tab,
l_rate_expenditure_type_tab,
l_rate_organization_id_tab,
l_project_assignment_id_tab,
l_assign_precedes_task_tab,
l_bill_job_group_id_tab,
l_carry_out_organiz_id_tab,
l_multi_currency_bill_flag_tab,
l_org_id_tab,
l_non_lab_bill_rate_org_id_tab,
l_non_lab_sch_discount_tab,
l_non_lab_sch_fixed_date_tab,
l_project_type_tab,
l_lab_bill_rate_org_id_tab,
l_lab_sch_FIXED_DATE_tab,
l_top_task_id_tab,
l_scheduled_start_date_tab,
l_labor_scheduled_discount_tab,
l_labor_sch_type_tab,
l_non_labor_sch_type_tab,
l_rev_res_class_rt_sch_id_tab,
l_cost_res_class_rt_sch_id_tab,
l_res_format_id_tab
FROM pa_fp_calc_amt_tmp2 tmp, pa_resource_assignments ra,
pa_projects_all proj, pa_tasks t,
pa_proj_fp_options fp,
pa_resource_list_members rlm
WHERE tmp.resource_assignment_id = ra.resource_assignment_id
AND ra.project_id = proj.project_id
AND ra.task_id = t.task_id(+)
AND fp.fin_plan_version_id = ra.budget_version_id
AND ra.resource_list_member_id = rlm.resource_list_member_id;
l_delete_budget_lines_tab.extend;
l_delete_budget_lines_tab(i) := Null;
,p_delete_budget_lines_tab => l_delete_budget_lines_tab
,p_spread_amts_flag_tab => l_spread_amts_flag_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_txn_currency_override_tab => l_txn_currency_override_tab
,p_total_qty_tab => l_ra_quantity_tab --l_total_qty_tab
,p_addl_qty_tab => l_addl_qty_tab
,p_total_raw_cost_tab => l_total_raw_cost_tab
,p_addl_raw_cost_tab => l_addl_raw_cost_tab
,p_total_burdened_cost_tab => l_total_burdened_cost_tab
,p_addl_burdened_cost_tab => l_addl_burdened_cost_tab
,p_total_revenue_tab => l_total_revenue_tab
,p_addl_revenue_tab => l_addl_revenue_tab
,p_raw_cost_rate_tab => l_raw_cost_rate_tab
,p_rw_cost_rate_override_tab => l_rw_cost_rate_override_tab
,p_b_cost_rate_tab => l_b_cost_rate_tab
,p_b_cost_rate_override_tab => l_b_cost_rate_override_tab
,p_bill_rate_tab => l_bill_rate_tab
,p_bill_rate_override_tab => l_bill_rate_override_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA);