[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pra.budget_version_id
,pbv.period_profile_id
,pra.project_id
,pra.task_id
INTO x_fin_plan_version_id
,x_period_profile_id
,x_project_id
,l_task_id
FROM pa_resource_assignments pra,
pa_budget_versions pbv
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND pra.budget_version_id = pbv.budget_version_id;
SELECT start_date
,completion_date
,project_currency_code
,projfunc_currency_code
INTO x_project_start_date
,x_project_end_date
,x_project_currency_code
,x_projfunc_currency_code
FROM pa_projects_all p
WHERE p.project_id = x_project_id;
SELECT start_date
,completion_date
INTO x_task_start_date
,x_task_end_date
FROM pa_tasks pt
WHERE pt.task_id = l_task_id;
SELECT project_id, budget_version_id
INTO l_project_id, l_budget_version_id
FROM PA_RESOURCE_ASSIGNMENTS
WHERE RESOURCE_ASSIGNMENT_ID = p_resource_assignment_id;
pa_debug.g_err_stage:= 'Error while selecting for the input resource assignment id ' ||
p_resource_assignment_id;
SELECT start_date
,completion_date
INTO l_project_start_date
,l_project_end_date
FROM pa_projects_all p
WHERE p.project_id = l_project_id;
pa_debug.g_err_stage := 'Error while selecting for the project id ' || l_project_id;
we are sure there would be no budget lines and hence the following update need not
be done */
IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
UPDATE PA_BUDGET_LINES
SET BUCKETING_PERIOD_CODE = NULL
WHERE TXN_CURRENCY_CODE = p_transaction_currency_code
AND RESOURCE_ASSIGNMENT_ID = p_resource_assignment_id
AND BUDGET_VERSION_ID = l_budget_version_id
AND BUCKETING_PERIOD_CODE = PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PE;
we are sure there would be no budget lines and hence the following update need not
be done */
IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
UPDATE PA_BUDGET_LINES
SET BUCKETING_PERIOD_CODE = NULL
WHERE TXN_CURRENCY_CODE = p_transaction_currency_code
AND RESOURCE_ASSIGNMENT_ID = p_resource_assignment_id
AND BUDGET_VERSION_ID = l_budget_version_id
AND BUCKETING_PERIOD_CODE = PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SE;
SELECT
pfo.PROJECT_COST_RATE_TYPE
,decode(pfo.PROJECT_COST_RATE_TYPE,'User',pftc.PROJECT_COST_EXCHANGE_RATE,null) PROJECT_COST_EXCHANGE_RATE
,pfo.PROJECT_COST_RATE_DATE_TYPE
,pfo.PROJECT_COST_RATE_DATE
,pfo.PROJECT_REV_RATE_TYPE
,decode(pfo.PROJECT_REV_RATE_TYPE,'User',pftc.PROJECT_REV_EXCHANGE_RATE,null) PROJECT_REV_EXCHANGE_RATE
,pfo.PROJECT_REV_RATE_DATE_TYPE
,pfo.PROJECT_REV_RATE_DATE
,pfo.PROJFUNC_COST_RATE_TYPE
,decode(pfo.PROJFUNC_COST_RATE_TYPE,'User',pftc.PROJFUNC_COST_EXCHANGE_RATE,null) PROJFUNC_COST_EXCHANGE_RATE
,pfo.PROJFUNC_COST_RATE_DATE_TYPE
,pfo.PROJFUNC_COST_RATE_DATE
,pfo.PROJFUNC_REV_RATE_TYPE
,decode(pfo.PROJFUNC_REV_RATE_TYPE,'User',pftc.PROJFUNC_REV_EXCHANGE_RATE,null) PROJFUNC_REV_EXCHANGE_RATE
,pfo.PROJFUNC_REV_RATE_DATE_TYPE
,pfo.PROJFUNC_REV_RATE_DATE
FROM pa_proj_fp_options pfo
,pa_fp_txn_currencies pftc
WHERE pfo.proj_fp_options_id = pftc.proj_fp_options_id(+)
AND pfo.fin_plan_version_id = p_fin_plan_version_id
AND pftc.txn_currency_code(+) = p_txn_currency_code;
PROCEDURE insert_dummy_record_pvt (mc_rec IN mc_cur_rec%TYPE)
IS
BEGIN
INSERT INTO PA_FP_ROLLUP_TMP
(ROLLUP_ID
,RESOURCE_ASSIGNMENT_ID
,BUDGET_LINE_ID
,OLD_START_DATE
,START_DATE
,END_DATE
,PERIOD_NAME
,txn_currency_code
,old_quantity
,old_txn_raw_cost
,old_txn_burdened_cost
,old_txn_revenue
,quantity
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,bucketing_period_code
,delete_flag
,parent_assignment_id
,project_currency_code
,projfunc_currency_code
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
)
SELECT pa_fp_rollup_tmp_s.nextval
,p_resource_assignment_id
,NULL /* BUDGET_LINE_ID */
,NULL
,NULL
,NULL
,NULL /* period name */
,p_txn_currency_code
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,'N'
,parent_assignment_id
,l_project_currency_code
,l_projfunc_currency_code
,mc_rec.PROJECT_COST_RATE_TYPE
,mc_rec.PROJECT_COST_EXCHANGE_RATE
,mc_rec.PROJECT_COST_RATE_DATE_TYPE
,mc_rec.PROJECT_COST_RATE_DATE
,mc_rec.PROJECT_REV_RATE_TYPE
,mc_rec.PROJECT_REV_EXCHANGE_RATE
,mc_rec.PROJECT_REV_RATE_DATE_TYPE
,mc_rec.PROJECT_REV_RATE_DATE
,mc_rec.PROJFUNC_COST_RATE_TYPE
,mc_rec.PROJFUNC_COST_EXCHANGE_RATE
,mc_rec.PROJFUNC_COST_RATE_DATE_TYPE
,mc_rec.PROJFUNC_COST_RATE_DATE
,mc_rec.PROJFUNC_REV_RATE_TYPE
,mc_rec.PROJFUNC_REV_EXCHANGE_RATE
,mc_rec.PROJFUNC_REV_RATE_DATE_TYPE
,mc_rec.PROJFUNC_REV_RATE_DATE
FROM pa_resource_assignments pra
where resource_assignment_id = p_resource_assignment_id;
END insert_dummy_record_pvt;
SELECT MIN(start_date), MAX(start_date)
INTO l_min_start_date,l_max_start_date
FROM pa_budget_lines
WHERE budget_version_id = l_fin_plan_version_id
AND resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code
AND start_date >= l_period_profile_start_date
AND end_date <= l_period_profile_end_date;
DELETE FROM PA_FP_ROLLUP_TMP;
pa_debug.g_err_stage:='deleted '||sql%rowcount || ' records from PA_FP_ROLLUP_TMP table' ;
INSERT INTO PA_FP_ROLLUP_TMP
( ROLLUP_ID
,RESOURCE_ASSIGNMENT_ID
,BUDGET_LINE_ID
,OLD_START_DATE
,START_DATE
,END_DATE
,PERIOD_NAME
,CHANGE_REASON_CODE
,DESCRIPTION
,BUCKETING_PERIOD_CODE
,TXN_CURRENCY_CODE
,PARENT_ASSIGNMENT_ID
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,OLD_PROJ_RAW_COST
,OLD_PROJ_BURDENED_COST
,OLD_PROJ_REVENUE
,OLD_PROJFUNC_RAW_COST
,OLD_PROJFUNC_BURDENED_COST
,OLD_PROJFUNC_REVENUE
,OLD_QUANTITY
,PROJECT_RAW_COST
,PROJECT_BURDENED_COST
,PROJECT_REVENUE
,PROJFUNC_RAW_COST
,PROJFUNC_BURDENED_COST
,PROJFUNC_REVENUE
,OLD_TXN_RAW_COST
,OLD_TXN_BURDENED_COST
,OLD_TXN_REVENUE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,QUANTITY
,DELETE_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,QUANTITY_SOURCE
,REVENUE_SOURCE
,PM_PRODUCT_CODE
)
(
SELECT PA_FP_ROLLUP_TMP_S.NEXTVAL
,P_RESOURCE_ASSIGNMENT_ID
,pbl.BUDGET_LINE_ID
,pbl.START_DATE OLD_START_DATE /* when old_start_Date is null then the record should be inserted in pbl */
,tmp.START_DATE START_DATE
,tmp.END_DATE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_period_text,
l_succeeding_prd_start_date,l_succeeding_period_text,
tmp.PERIOD_NAME) PERIOD_NAME
,pbl.CHANGE_REASON_CODE
,pbl.DESCRIPTION
,decode(tmp.start_date,
l_preceding_prd_start_date,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD,
l_succeeding_prd_start_date,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD)
BUCKETING_PERIOD_CODE
,P_TXN_CURRENCY_CODE
,pra.PARENT_ASSIGNMENT_ID
,nvl(pbl.PROJECT_CURRENCY_CODE,l_project_currency_code)
,nvl(pbl.PROJFUNC_CURRENCY_CODE,l_projfunc_currency_code) /* decode is used here since there are some outer joined records and we want to default only for such cases */
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_RATE_TYPE,pbl.PROJECT_COST_RATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_EXCHANGE_RATE,pbl.PROJECT_COST_EXCHANGE_RATE) /* due to changes in the mc page */
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_RATE_DATE_TYPE,pbl.PROJECT_COST_RATE_DATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_RATE_DATE,pbl.PROJECT_COST_RATE_DATE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_RATE_TYPE,pbl.PROJECT_REV_RATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_EXCHANGE_RATE,pbl.PROJECT_REV_EXCHANGE_RATE) /* due to changes in the mc page */
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_RATE_DATE_TYPE,pbl.PROJECT_REV_RATE_DATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_RATE_DATE,pbl.PROJECT_REV_RATE_DATE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_RATE_TYPE,pbl.PROJFUNC_COST_RATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_EXCHANGE_RATE,pbl.PROJFUNC_COST_EXCHANGE_RATE)/* due to changes in the mc page */
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE_TYPE,pbl.PROJFUNC_COST_RATE_DATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE,pbl.PROJFUNC_COST_RATE_DATE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_RATE_TYPE,pbl.PROJFUNC_REV_RATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_EXCHANGE_RATE,pbl.PROJFUNC_REV_EXCHANGE_RATE) /* due to changes in the mc page */
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE_TYPE,pbl.PROJFUNC_REV_RATE_DATE_TYPE)
,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE,pbl.PROJFUNC_REV_RATE_DATE)
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pc_raw_cost,
l_succeeding_prd_start_date,l_sd_pc_raw_cost,
pbl.PROJECT_RAW_COST) OLD_PROJ_RAW_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pc_burdened_cost,
l_succeeding_prd_start_date,l_sd_pc_burdened_cost,
pbl.PROJECT_BURDENED_COST) OLD_PROJ_BURDENED_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pc_revenue,
l_succeeding_prd_start_date,l_sd_pc_revenue,
pbl.PROJECT_REVENUE) OLD_PROJ_REVENUE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pfc_raw_cost,
l_succeeding_prd_start_date,l_sd_pfc_raw_cost,
pbl.RAW_COST) OLD_PROJFUNC_RAW_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pfc_burdened_cost,
l_succeeding_prd_start_date,l_sd_pfc_burdened_cost,
pbl.BURDENED_COST) OLD_PROJFUNC_BURDENED_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pfc_revenue,
l_succeeding_prd_start_date,l_sd_pfc_revenue,
pbl.REVENUE) OLD_PROJFUNC_REVENUE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_quantity,
l_succeeding_prd_start_date,l_succeeding_quantity,
pbl.QUANTITY) OLD_QUANTITY
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pc_raw_cost,
l_succeeding_prd_start_date,l_sd_pc_raw_cost,
pbl.PROJECT_RAW_COST) PROJECT_RAW_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pc_burdened_cost,
l_succeeding_prd_start_date,l_sd_pc_burdened_cost,
pbl.PROJECT_BURDENED_COST) PROJECT_BURDENED_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pc_revenue,
l_succeeding_prd_start_date,l_sd_pc_revenue,
pbl.PROJECT_REVENUE) PROJECT_REVENUE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pfc_raw_cost,
l_succeeding_prd_start_date,l_sd_pfc_raw_cost,
pbl.RAW_COST) RAW_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pfc_burdened_cost,
l_succeeding_prd_start_date,l_sd_pfc_burdened_cost,
pbl.BURDENED_COST) BURDENED_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_pd_pfc_revenue,
l_succeeding_prd_start_date,l_sd_pfc_revenue,
pbl.REVENUE) REVENUE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_raw_cost,
l_succeeding_prd_start_date,l_succeeding_raw_cost,
pbl.TXN_RAW_COST) OLD_TXN_RAW_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_burdened_cost,
l_succeeding_prd_start_date,l_succeeding_burdened_cost,
pbl.TXN_BURDENED_COST) OLD_TXN_BURDENED_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_revenue,
l_succeeding_prd_start_date,l_succeeding_revenue,
pbl.TXN_REVENUE) OLD_TXN_REVENUE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_raw_cost,
l_succeeding_prd_start_date,l_succeeding_raw_cost,
pbl.TXN_RAW_COST) TXN_RAW_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_burdened_cost,
l_succeeding_prd_start_date,l_succeeding_burdened_cost,
pbl.TXN_BURDENED_COST) TXN_BURDENED_COST
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_revenue,
l_succeeding_prd_start_date,l_succeeding_revenue,
pbl.TXN_REVENUE) TXN_REVENUE
,decode(tmp.start_date,
l_preceding_prd_start_date,l_preceding_quantity,
l_succeeding_prd_start_date,l_succeeding_quantity,
pbl.QUANTITY) QUANTITY
,'N' DELETE_FLAG
,pbl.ATTRIBUTE_CATEGORY
,pbl.ATTRIBUTE1
,pbl.ATTRIBUTE2
,pbl.ATTRIBUTE3
,pbl.ATTRIBUTE4
,pbl.ATTRIBUTE5
,pbl.ATTRIBUTE6
,pbl.ATTRIBUTE7
,pbl.ATTRIBUTE8
,pbl.ATTRIBUTE9
,pbl.ATTRIBUTE10
,pbl.ATTRIBUTE11
,pbl.ATTRIBUTE12
,pbl.ATTRIBUTE13
,pbl.ATTRIBUTE14
,pbl.ATTRIBUTE15
,pbl.RAW_COST_SOURCE
,pbl.BURDENED_COST_SOURCE
,pbl.QUANTITY_SOURCE
,pbl.REVENUE_SOURCE
,pbl.PM_PRODUCT_CODE
FROM pa_resource_assignments pra
,pa_budget_lines pbl
,pa_fp_cpy_periods_tmp tmp
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND pbl.resource_assignment_id(+) = p_resource_assignment_id
AND pbl.start_date(+) = tmp.start_date
AND pbl.txn_currency_code(+) = p_txn_currency_code);
pa_debug.g_err_stage := ':inserted ' || sql%rowcount || ' records ';
we need to insert either project start/end date or task start/end date
based upon planning level.
in case time phasing is date range. insert a row with these values as null
User will enter the required start and end dates.
*/
IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N THEN
IF l_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
l_start_date := l_project_start_date;
INSERT INTO PA_FP_ROLLUP_TMP
( ROLLUP_ID
,RESOURCE_ASSIGNMENT_ID
,BUDGET_LINE_ID
,OLD_START_DATE
,START_DATE
,END_DATE
,PERIOD_NAME
,CHANGE_REASON_CODE
,DESCRIPTION
,BUCKETING_PERIOD_CODE
,TXN_CURRENCY_CODE
,PARENT_ASSIGNMENT_ID
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,OLD_PROJ_RAW_COST
,OLD_PROJ_BURDENED_COST
,OLD_PROJ_REVENUE
,OLD_PROJFUNC_RAW_COST
,OLD_PROJFUNC_BURDENED_COST
,OLD_PROJFUNC_REVENUE
,OLD_QUANTITY
,PROJECT_RAW_COST
,PROJECT_BURDENED_COST
,PROJECT_REVENUE
,PROJFUNC_RAW_COST
,PROJFUNC_BURDENED_COST
,PROJFUNC_REVENUE
,OLD_TXN_RAW_COST
,OLD_TXN_BURDENED_COST
,OLD_TXN_REVENUE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,QUANTITY
,DELETE_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,QUANTITY_SOURCE
,REVENUE_SOURCE
,PM_PRODUCT_CODE
)
(
SELECT pa_fp_rollup_tmp_s.nextval
,p_resource_assignment_id /* Fix for bug # 2586514 */
,pbl.BUDGET_LINE_ID
,pbl.start_date OLD_START_DATE /* when old_start_Date is null then the record should be inserted in pbl */
,nvl(pbl.start_date,l_start_date)
,nvl(pbl.end_Date,l_end_date)
,pbl.PERIOD_NAME
,pbl.CHANGE_REASON_CODE
,pbl.DESCRIPTION
,pbl.BUCKETING_PERIOD_CODE
,P_TXN_CURRENCY_CODE /* Fix for bug # 2590361 */
,pra.PARENT_ASSIGNMENT_ID
,nvl(pbl.PROJECT_CURRENCY_CODE,l_project_currency_code)
,nvl(pbl.PROJFUNC_CURRENCY_CODE,l_projfunc_currency_code)
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_RATE_TYPE,pbl.PROJECT_COST_RATE_TYPE) /* remove decode for start date now */
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_EXCHANGE_RATE,pbl.PROJECT_COST_EXCHANGE_RATE) /* due to changes on mc page */
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_RATE_DATE_TYPE,pbl.PROJECT_COST_RATE_DATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_RATE_DATE,pbl.PROJECT_COST_RATE_DATE)
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_RATE_TYPE,pbl.PROJECT_REV_RATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_EXCHANGE_RATE,pbl.PROJECT_REV_EXCHANGE_RATE) /* due to changes on mc page */
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_RATE_DATE_TYPE,pbl.PROJECT_REV_RATE_DATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_RATE_DATE,pbl.PROJECT_REV_RATE_DATE)
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_RATE_TYPE,pbl.PROJFUNC_COST_RATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_EXCHANGE_RATE,pbl.PROJFUNC_COST_EXCHANGE_RATE)/* due to changes on mc page */
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE_TYPE,pbl.PROJFUNC_COST_RATE_DATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE,pbl.PROJFUNC_COST_RATE_DATE)
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_RATE_TYPE,pbl.PROJFUNC_REV_RATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_EXCHANGE_RATE,pbl.PROJFUNC_REV_EXCHANGE_RATE) /* due to changes on mc page */
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE_TYPE,pbl.PROJFUNC_REV_RATE_DATE_TYPE)
,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE,pbl.PROJFUNC_REV_RATE_DATE)
,pbl.PROJECT_RAW_COST OLD_PROJ_RAW_COST
,pbl.PROJECT_BURDENED_COST OLD_PROJ_BURDENED_COST
,pbl.PROJECT_REVENUE OLD_PROJ_REVENUE
,pbl.RAW_COST OLD_PROJFUNC_RAW_COST
,pbl.BURDENED_COST OLD_PROJFUNC_BURDENED_COST
,pbl.REVENUE OLD_PROJFUNC_REVENUE
,pbl.QUANTITY OLD_QUANTITY
,pbl.PROJECT_RAW_COST
,pbl.PROJECT_BURDENED_COST
,pbl.PROJECT_REVENUE
,pbl.RAW_COST
,pbl.BURDENED_COST
,pbl.REVENUE
,pbl.TXN_RAW_COST OLD_TXN_RAW_COST
,pbl.TXN_BURDENED_COST OLD_TXN_BURDENED_COST
,pbl.TXN_REVENUE OLD_TXN_REVENUE
,pbl.TXN_RAW_COST
,pbl.TXN_BURDENED_COST
,pbl.TXN_REVENUE
,pbl.QUANTITY
,'N' DELETE_FLAG
,pbl.ATTRIBUTE_CATEGORY
,pbl.ATTRIBUTE1
,pbl.ATTRIBUTE2
,pbl.ATTRIBUTE3
,pbl.ATTRIBUTE4
,pbl.ATTRIBUTE5
,pbl.ATTRIBUTE6
,pbl.ATTRIBUTE7
,pbl.ATTRIBUTE8
,pbl.ATTRIBUTE9
,pbl.ATTRIBUTE10
,pbl.ATTRIBUTE11
,pbl.ATTRIBUTE12
,pbl.ATTRIBUTE13
,pbl.ATTRIBUTE14
,pbl.ATTRIBUTE15
,pbl.RAW_COST_SOURCE
,pbl.BURDENED_COST_SOURCE
,pbl.QUANTITY_SOURCE
,pbl.REVENUE_SOURCE
,pbl.PM_PRODUCT_CODE
FROM pa_resource_assignments pra
,pa_budget_lines pbl
,pa_proj_fp_options pfo
-- ,pa_fp_txn_currencies pftc -- Bug # 2615998
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND pbl.txn_currency_code = p_txn_currency_code
AND pbl.resource_assignment_id = pra.resource_assignment_id
AND pfo.fin_plan_version_id = pra.budget_version_id
-- AND pfo.proj_fp_options_id = pftc.proj_fp_options_id -- Bug # 2615998
-- AND pftc.txn_currency_code = p_txn_currency_code -- Bug # 2615998
);
/* Bug found during Unit Testing. Do the insert only in EDIT / EDIT ANOTHER CURRENCY modes */
IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_VIEW THEN
IF nvl(l_count,0) = 0 THEN
IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_R THEN
FOR i IN 1..5 LOOP
insert_dummy_record_pvt(mc_cur_rec);
insert_dummy_record_pvt(mc_cur_rec);
UPDATE PA_FP_ROLLUP_TMP
SET START_DATE = l_start_date,
END_DATE = l_end_date
WHERE resource_assignment_id = p_resource_assignment_id;
pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
If budget_line_id is null then it will be considered as new record (to be inserted)
else records will be updated.
*/
PROCEDURE PROCESS_MODIFIED_LINES
(
-- Bug Fix: 4569365. Removed MRC code.
p_calling_context IN VARCHAR2 -- pa_mrc_finplan.g_calling_module%TYPE /* Bug# 2674353 */
,p_resource_assignment_id IN pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE
,p_fin_plan_version_id IN pa_resource_assignments.budget_version_id%TYPE -- DEFAULT NULL
,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 ) IS --File.Sql.39 bug 4440895
/* Variables to be used for debugging purpose */
l_msg_count NUMBER := 0;
select 1 from dual
where exists (select prlm.resource_list_member_id
from pa_resource_list_members prlm, pa_resource_assignments pra, pa_fp_rollup_tmp tmp
where pra.resource_assignment_id = p_resource_assignment_id
and pra.resource_list_member_id= prlm.resource_list_member_id
and nvl(prlm.enabled_flag,'Y') = 'N'
and tmp.resource_assignment_id = pra.resource_assignment_id
and tmp.budget_line_id is null);
Delete all such lines from rollup_tmp table that are not existing in
pa_budget_lines and also marked for delete by user.
*/
pa_debug.g_err_stage := TO_CHAR(l_stage)||'Deleting records from pa_fp_rollup_tmp that are not present in the budget lines table ';
delete from pa_fp_rollup_tmp
where budget_line_id is null /* FPB3: bug 2645574: Instead of old_start_date refer budget_line_id */
and (delete_flag = 'Y' or
(txn_raw_cost is null and
txn_burdened_cost is null and
quantity is null and
txn_revenue is null)); /* Bug 2684537 */
pa_debug.g_err_stage := TO_CHAR(l_stage)||': Deleted '||sql%rowcount||' records ';
deleted quantum of PD/SD records and updating the delete_flag back to N. This is done
because deletion of PD/SD means reducing the PD/SD bucket amounts by that amount and
we should not be deleting the records as such. This means PD/SD bucket amounts should
be set to zero if delete flag = Y for them. PC/PFC buckets of PD/SD need not be touched
since they will be maintained by the call to convert_mc api
UPDATE pa_fp_rollup_tmp
SET delete_flag = 'N'
,txn_raw_cost = DECODE(old_txn_raw_cost, NULL,NULL,0)
,txn_burdened_cost = DECODE(old_txn_burdened_cost,NULL,NULL,0)
,txn_revenue = DECODE(old_txn_revenue,NULL,NULL,0)
,quantity = DECODE(old_quantity,NULL,NULL,0)
WHERE delete_flag = 'Y'
AND bucketing_period_code IN
(PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD);
pa_debug.g_err_stage := TO_CHAR(l_stage)||': updated '||sql%rowcount||' records ';
SELECT budget_version_id, task_id, resource_list_member_id
INTO l_budget_version_id, l_task_id, l_resource_list_member_id
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
x_msg_data := 'PA_FP_EPL_TASK_UPDATED';
p_msg_name => 'PA_FP_EPL_TASK_UPDATED');
DELETE FROM pa_budget_lines bl
WHERE (bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date ) IN (SELECT tmp.resource_assignment_id
,tmp.txn_currency_code
,tmp.old_start_date
FROM pa_fp_rollup_tmp tmp
WHERE nvl(tmp.delete_flag,'N') = 'Y') ;
DELETE /*+ INDEX( bl PA_BUDGET_LINES_U2 )*/ FROM pa_budget_lines bl --Bug 2782166
WHERE (budget_line_id) IN (SELECT budget_line_id
FROM pa_fp_rollup_tmp tmp
WHERE nvl(tmp.delete_flag,'N') = 'Y');
pa_debug.g_err_stage := TO_CHAR(l_stage)||': Deleted '||sql%rowcount||' records';
UPDATE /*+ INDEX( bl PA_BUDGET_LINES_U2 )*/ PA_BUDGET_LINES bl --Bug 2782166
SET (
START_DATE
,END_DATE
,QUANTITY
,RAW_COST
,BURDENED_COST
,REVENUE
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_RAW_COST
,PROJECT_BURDENED_COST
,PROJECT_REVENUE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,QUANTITY_SOURCE
,REVENUE_SOURCE
/* Code Addition for bug 3394907 starts */
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
/* Code Addition for bug 3394907 ends */
) =
(
SELECT
START_DATE
,END_DATE
,decode(bucketing_period_code,NULL,QUANTITY,
decode(bl.QUANTITY||tmp.QUANTITY||tmp.old_QUANTITY,null,null,
nvl(bl.QUANTITY,0) + (nvl(tmp.QUANTITY,0) - nvl(tmp.old_QUANTITY,0))))
,decode(bucketing_period_code,NULL,tmp.PROJFUNC_RAW_COST,
decode(bl.RAW_COST||tmp.PROJFUNC_RAW_COST||tmp.OLD_PROJFUNC_RAW_COST,null,null,
nvl(bl.RAW_COST,0) + (nvl(tmp.PROJFUNC_RAW_COST,0) - nvl(tmp.OLD_PROJFUNC_RAW_COST,0)))) /* Bug 2774811 */
,decode(bucketing_period_code,NULL,tmp.PROJFUNC_BURDENED_COST,
decode(bl.BURDENED_COST||tmp.PROJFUNC_BURDENED_COST||tmp.OLD_PROJFUNC_BURDENED_COST,null,null,
nvl(bl.BURDENED_COST,0) + (nvl(tmp.PROJFUNC_BURDENED_COST,0) - nvl(tmp.OLD_PROJFUNC_BURDENED_COST,0)))) /* Bug 2774811 */
,decode(bucketing_period_code,NULL,tmp.PROJFUNC_REVENUE,
decode(bl.REVENUE||tmp.PROJFUNC_REVENUE||tmp.OLD_PROJFUNC_REVENUE,null,null,
nvl(bl.REVENUE,0) + (nvl(tmp.PROJFUNC_REVENUE,0) - nvl(tmp.OLD_PROJFUNC_REVENUE,0)))) /* Bug 2774811 */
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,decode(bucketing_period_code,NULL,tmp.PROJECT_RAW_COST,
decode(bl.PROJECT_RAW_COST||tmp.PROJECT_RAW_COST||tmp.OLD_PROJ_RAW_COST,null,null,
nvl(bl.PROJECT_RAW_COST,0) + (nvl(tmp.PROJECT_RAW_COST,0) - nvl(tmp.OLD_PROJ_RAW_COST,0)))) /* Bug 2774811 */
,decode(bucketing_period_code,NULL,tmp.PROJECT_BURDENED_COST,
decode(bl.PROJECT_BURDENED_COST||tmp.PROJECT_BURDENED_COST||tmp.OLD_PROJ_BURDENED_COST,null,null,
nvl(bl.PROJECT_BURDENED_COST,0) + (nvl(tmp.PROJECT_BURDENED_COST,0) - nvl(tmp.OLD_PROJ_BURDENED_COST,0)))) /* Bug 2774811 */
,decode(bucketing_period_code,NULL,tmp.PROJECT_REVENUE,
decode(bl.PROJECT_REVENUE||tmp.PROJECT_REVENUE||tmp.OLD_PROJ_REVENUE,null,null,
nvl(bl.PROJECT_REVENUE,0) + (nvl(tmp.PROJECT_REVENUE,0) - nvl(tmp.OLD_PROJ_REVENUE,0)))) /* Bug 2774811 */
,decode(bucketing_period_code,NULL,TXN_RAW_COST,
decode(bl.txn_raw_cost||tmp.txn_raw_cost||tmp.old_txn_raw_cost,null,null,
nvl(bl.txn_raw_cost,0) + (nvl(tmp.txn_raw_cost,0) - nvl(tmp.old_txn_raw_cost,0))))
,decode(bucketing_period_code,NULL,TXN_BURDENED_COST,
decode(bl.txn_burdened_cost||tmp.txn_burdened_cost||tmp.old_txn_burdened_cost,null,null,
nvl(bl.txn_burdened_cost,0) + (nvl(tmp.txn_burdened_cost,0) - nvl(tmp.old_txn_burdened_cost,0))))
,decode(bucketing_period_code,NULL,TXN_REVENUE,
decode(bl.TXN_REVENUE||tmp.TXN_REVENUE||tmp.old_TXN_REVENUE,null,null,
nvl(bl.TXN_REVENUE,0) + (nvl(tmp.TXN_REVENUE,0) - nvl(tmp.old_TXN_REVENUE,0))))
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
/* Code Addition for bug 3394907 starts */
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
/* Code Addition for bug 3394907 ends */
FROM pa_fp_rollup_tmp tmp
WHERE bl.budget_line_id = tmp.budget_line_id
AND tmp.budget_line_id IS NOT NULL
AND nvl(tmp.delete_flag,'N') <> 'Y')
WHERE ( bl.budget_line_id ) IN (SELECT tmp.budget_line_id
FROM pa_fp_rollup_tmp tmp
where nvl(tmp.delete_flag,'N') <> 'Y'
AND tmp.budget_line_id IS NOT NULL);
pa_debug.g_err_stage := TO_CHAR(l_stage)||'updated '||sql%rowcount||' budget lines ';
,bl.txn_currency_code ) IN (SELECT tmp.resource_assignment_id
,tmp.old_start_date
,tmp.txn_currency_code
FROM pa_fp_rollup_tmp tmp
where nvl(tmp.delete_flag,'N') <> 'Y'
AND tmp.old_start_date IS NOT NULL) ;
INSERT INTO pa_budget_lines
( RESOURCE_ASSIGNMENT_ID
,BUDGET_LINE_ID
,BUDGET_VERSION_ID
,START_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,END_DATE
,PERIOD_NAME
,QUANTITY
,RAW_COST
,BURDENED_COST
,REVENUE
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,QUANTITY_SOURCE
,REVENUE_SOURCE
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_RAW_COST
,PROJECT_BURDENED_COST
,PROJECT_REVENUE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PM_PRODUCT_CODE
,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3858543
(SELECT
RESOURCE_ASSIGNMENT_ID
,pa_budget_lines_s.nextval
,l_budget_version_id
,START_DATE
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,END_DATE
,PERIOD_NAME
,QUANTITY
,PROJFUNC_RAW_COST
,PROJFUNC_BURDENED_COST
,PROJFUNC_REVENUE
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_RAW_COST
,PROJECT_BURDENED_COST
,PROJECT_REVENUE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,pm_product_code -- , l_pm_product_code changed to pm_product_code for bug 3858543
,pm_budget_line_reference -- Added for bug 3858543
FROM pa_fp_rollup_tmp tmp
/* bug 2645574 changed the condition to look into budget_line_id
WHERE tmp.old_start_date IS NULL
*/
WHERE tmp.budget_line_id IS NULL
/* manokuma: added following as PD and SD should not be inserted in this procedure */
/* Bug 2779688 - PD/SD can be inserted in this procedure and hence commenting the where clause
AND nvl(tmp.bucketing_period_code,'XYZ') NOT IN
(PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD) */
AND (tmp.txn_raw_cost IS NOT NULL
or tmp.txn_burdened_cost IS NOT NULL
or tmp.quantity IS NOT NULL
or tmp.txn_revenue IS NOT NULL));
pa_debug.g_err_stage := TO_CHAR(l_stage)||'inserted '|| sql%rowcount ||' budget lines ';
UPDATE pa_budget_versions
SET record_version_number = nvl(record_version_number,0) + 1
WHERE budget_version_id = l_budget_version_id;
/* Bug# 2641475 - MRC call moved here from before the insert/update of pa_budget_lines */
/* Call MRC API */
/*
pa_debug.g_err_stage := TO_CHAR(l_stage)||'Calling MRC API ';
SELECT margin_derived_from_code
INTO l_margin_derived_from_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id = l_fin_plan_version_id;
pa_debug.g_err_stage := TO_CHAR(l_stage)||':Starting the main processing. Inserting into temp table';
delete from pa_fp_cpy_periods_tmp;
pa_debug.g_err_stage:='deleted '||sql%rowcount || ' records from tmp table' ;
pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for preceding period' ;
INSERT INTO pa_fp_cpy_periods_tmp
( start_date
,end_date
,pa_period_name
,gl_period_name
,period_name )
SELECT start_date start_date
,end_date end_date
,period_name pa_period
,gl_period_name gl_period
,period_name period_name
FROM PA_PERIODS
WHERE start_date = p_preceding_prd_start_date;
INSERT INTO pa_fp_cpy_periods_tmp
( start_date
,end_date
,pa_period_name
,gl_period_name
,period_name )
SELECT start_date start_date
,end_date end_date
,period_name pa_period
,gl_period_name gl_period
,period_name period_name
FROM PA_PERIODS
WHERE start_date BETWEEN p_period_profile_start_date AND p_period_profile_end_date;
pa_debug.g_err_stage := TO_CHAR(l_stage)||'inserted ' || sql%rowcount || ' records';
pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for succeeding period' ;
INSERT INTO pa_fp_cpy_periods_tmp
( start_date
,end_date
,pa_period_name
,gl_period_name
,period_name )
SELECT start_date start_date
,end_date end_date
,period_name pa_period
,gl_period_name gl_period
,period_name period_name
FROM PA_PERIODS
WHERE start_date = p_succeeding_prd_start_date;
pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for preceding period' ;
INSERT INTO pa_fp_cpy_periods_tmp
( start_date
,end_date
,pa_period_name
,gl_period_name
,period_name )
SELECT g.start_date start_date
,g.end_date end_date
,'null' pa_period
,g.period_name gl_period
,g.period_name period_name
FROM PA_IMPLEMENTATIONS i
,GL_PERIOD_STATUSES g
WHERE g.set_of_books_id = i.set_of_books_id
AND g.application_id = pa_period_process_pkg.application_id
AND g.adjustment_period_flag = 'N'
AND g.start_date = p_preceding_prd_start_date;
INSERT INTO pa_fp_cpy_periods_tmp(
start_date
,end_date
,pa_period_name
,gl_period_name
,period_name )
SELECT g.start_date start_date
,g.end_date end_period
,'null' pa_period /* this value is never used */
,g.period_name gl_period
,g.period_name period_name
FROM PA_IMPLEMENTATIONS i
,GL_PERIOD_STATUSES g
WHERE g.set_of_books_id = i.set_of_books_id
AND g.application_id = pa_period_process_pkg.application_id
AND g.adjustment_period_flag = 'N'
AND g.start_date BETWEEN p_period_profile_start_date AND p_period_profile_end_date;
pa_debug.g_err_stage := TO_CHAR(l_stage)||'inserted ' || sql%rowcount || ' records';
pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for preceding period' ;
INSERT INTO pa_fp_cpy_periods_tmp
( start_date
,end_date
,pa_period_name
,gl_period_name
,period_name )
SELECT g.start_date start_date
,g.end_date end_date
,'null' pa_period
,g.period_name gl_period
,g.period_name period_name
FROM PA_IMPLEMENTATIONS i
,GL_PERIOD_STATUSES g
WHERE g.set_of_books_id = i.set_of_books_id
AND g.application_id = pa_period_process_pkg.application_id
AND g.adjustment_period_flag = 'N'
AND g.start_date = p_succeeding_prd_start_date;
SELECT sum(nvl(raw_cost,0)),
sum(nvl(burdened_cost,0)),
sum(nvl(revenue,0)),
sum(nvl(project_raw_cost,0)),
sum(nvl(project_burdened_cost,0)),
sum(nvl(project_revenue,0))
INTO x_pd_pfc_raw_cost,
x_pd_pfc_burdened_cost,
x_pd_pfc_revenue,
x_pd_pc_raw_cost,
x_pd_pc_burdened_cost,
x_pd_pc_revenue
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code
AND bucketing_period_code in
(PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PE);
SELECT sum(nvl(raw_cost,0)),
sum(nvl(burdened_cost,0)),
sum(nvl(revenue,0)),
sum(nvl(project_raw_cost,0)),
sum(nvl(project_burdened_cost,0)),
sum(nvl(project_revenue,0))
INTO x_sd_pfc_raw_cost,
x_sd_pfc_burdened_cost,
x_sd_pfc_revenue,
x_sd_pc_raw_cost,
x_sd_pc_burdened_cost,
x_sd_pc_revenue
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code
AND bucketing_period_code in
(PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SE);
SELECT amount_type_code
,amount_subtype_code
,preceding_periods_amount
,succeeding_periods_amount
FROM pa_proj_periods_denorm
WHERE budget_version_id = p_budget_version_id
AND resource_assignment_id = p_resource_assignment_id
AND object_type_code = PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT
AND object_id = p_resource_assignment_id
AND currency_code = p_txn_currency_code
AND period_profile_id = p_period_profile_id
AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION ;
SELECT fin_plan_preference_code
INTO l_fin_plan_preference_code
FROM pa_proj_fp_options pfo
,pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND pra.budget_version_id = pfo.fin_plan_version_id;
SELECT fin_plan_preference_code
INTO l_fp_preference_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_budget_version_id;
SELECT resource_assignment_id,start_date,txn_currency_code
FROM pa_fp_rollup_tmp
GROUP BY resource_assignment_id,txn_currency_code,start_date
HAVING COUNT(*)>1;
SELECT pra.project_id
,pra.task_id
,pra.resource_list_member_id
,pbv.budget_type_code
,pbv.fin_plan_type_id
FROM pa_resource_assignments pra
,pa_budget_versions pbv
WHERE pra.resource_assignment_id = c_resource_assignment_id
AND pra.budget_version_id = pbv.budget_version_id ;
SELECT alias
INTO l_resource_alias
FROM pa_resource_list_members
WHERE resource_list_member_id = l_res_assignment_details_rec.resource_list_member_id;
SELECT segment1
INTO l_segment1
FROM pa_projects_all
WHERE project_id=l_res_assignment_details_rec.project_id;
SELECT name
INTO l_context_info
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = l_res_assignment_details_rec.fin_plan_type_id;
UPDATE pa_budget_versions
SET record_version_number = nvl(record_version_number,0) + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = p_budget_version_id;