[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inf.budget_version_id budget_version_id,
inf.resource_list_member_id resource_list_member_id,
inf.cbs_element_id cbs_element_id, -- cbs enhancement changes bug#16445106
inf.task_id task_id,
inf.amount_type_code amount_type_code,
inf.txn_currency_code txn_currency_code,
DECODE (inf.delete_flag, g_hidden_col_char, 'N', DECODE(inf.delete_flag, 'Y', 'Y', 'N')) delete_flag,
DECODE (inf.planning_start_date, g_hidden_col_date, TO_DATE(NULL), inf.planning_start_date) planning_start_date,
DECODE (inf.planning_end_date, g_hidden_col_date, TO_DATE(NULL), inf.planning_end_date) planning_end_date,
'-99' unit_of_measure,
DECODE (inf.description, g_hidden_col_char, NULL, DECODE(inf.description, NULL, l_fnd_miss_char, inf.description)) description,
DECODE (inf.change_reason_code, g_hidden_col_char, NULL,
'MULTIPLE', NULL,
NULL,l_fnd_miss_char,
inf.change_reason_code) change_reason,
DECODE (c_allow_qty_flag,'N',NULL,
DECODE (inf.quantity, g_hidden_col_num, NULL, DECODE(inf.quantity, NULL, l_fnd_miss_num, inf.quantity))) quantity,
DECODE (c_allow_qty_flag,'N',NULL,
DECODE (inf.etc_quantity, g_hidden_col_num, NULL, DECODE(inf.etc_quantity, NULL, l_fnd_miss_num, inf.etc_quantity))) etc_quantity,
DECODE (c_allow_raw_cost_flag,'N',NULL,
DECODE (inf.raw_cost, g_hidden_col_num, NULL, DECODE(inf.raw_cost, NULL, l_fnd_miss_num, inf.raw_cost))) raw_cost,
DECODE (c_allow_raw_cost_flag,'N',NULL,
DECODE (inf.etc_raw_cost, g_hidden_col_num, NULL, DECODE(inf.etc_raw_cost, NULL, l_fnd_miss_num, inf.etc_raw_cost))) etc_raw_cost,
DECODE (c_allow_raw_cost_rate_flag,'N',NULL,
DECODE (inf.raw_cost_over_rate, g_hidden_col_num, NULL, DECODE(inf.raw_cost_over_rate, NULL, l_fnd_miss_num, inf.raw_cost_over_rate))) raw_cost_rate,
DECODE (c_allow_burd_cost_flag,'N',NULL,
DECODE (inf.burdened_cost, g_hidden_col_num, NULL, DECODE(inf.burdened_cost, NULL, l_fnd_miss_num, inf.burdened_cost))) burdened_cost,
DECODE (c_allow_burd_cost_flag,'N',NULL,
DECODE (inf.etc_burdened_cost, g_hidden_col_num, NULL, DECODE(inf.etc_burdened_cost, NULL, l_fnd_miss_num, inf.etc_burdened_cost))) etc_burdened_cost,
DECODE (c_allow_burd_cost_rate_flag,'N',NULL,
DECODE (inf.burdened_cost_over_rate, g_hidden_col_num, NULL, DECODE(inf.burdened_cost_over_rate, NULL, l_fnd_miss_num, inf.burdened_cost_over_rate))) burdened_cost_rate,
DECODE (c_allow_revenue_flag,'N',NULL,
DECODE (inf.revenue, g_hidden_col_num, NULL, DECODE(inf.revenue, NULL, l_fnd_miss_num, inf.revenue))) revenue,
DECODE (c_allow_revenue_flag,'N',NULL,
DECODE (inf.etc_revenue, g_hidden_col_num, NULL, DECODE(inf.etc_revenue, NULL, l_fnd_miss_num, inf.etc_revenue))) etc_revenue,
DECODE (c_allow_bill_rate_flag,'N',NULL,
DECODE (inf.bill_over_rate, g_hidden_col_num, NULL, DECODE(inf.bill_over_rate, NULL, l_fnd_miss_num, inf.bill_over_rate))) bill_rate,
DECODE (c_version_type, 'REVENUE', NULL,
DECODE(inf.projfunc_cost_rate_type, g_hidden_col_char, NULL, DECODE(inf.projfunc_cost_rate_type, NULL, l_fnd_miss_char, inf.projfunc_cost_rate_type))) projfunc_cost_rate_type,
DECODE (c_version_type, 'REVENUE', NULL,
DECODE(inf.projfunc_cost_rate_date_type, g_hidden_col_char, NULL, DECODE(inf.projfunc_cost_rate_date_type, NULL, l_fnd_miss_char, inf.projfunc_cost_rate_date_type))) projfunc_cost_rate_date_type,
DECODE (c_version_type, 'REVENUE', NULL,
DECODE(inf.projfunc_cost_exchange_rate, g_hidden_col_num, NULL, DECODE(inf.projfunc_cost_exchange_rate, NULL, l_fnd_miss_num, inf.projfunc_cost_exchange_rate))) projfunc_cost_exchange_rate,
DECODE (c_version_type, 'REVENUE', TO_DATE(NULL),
DECODE(inf.projfunc_cost_rate_date, g_hidden_col_date, TO_DATE(NULL), DECODE(inf.projfunc_cost_rate_date, NULL, l_fnd_miss_date, inf.projfunc_cost_rate_date))) projfunc_cost_rate_date,
DECODE (c_version_type, 'REVENUE', NULL,
DECODE(inf.project_cost_rate_type, g_hidden_col_char, NULL, DECODE(inf.project_cost_rate_type, NULL, l_fnd_miss_char, inf.project_cost_rate_type))) project_cost_rate_type,
DECODE (c_version_type, 'REVENUE', NULL,
DECODE(inf.project_cost_rate_date_type, g_hidden_col_char, NULL, DECODE(inf.project_cost_rate_date_type, NULL, l_fnd_miss_char, inf.project_cost_rate_date_type))) project_cost_rate_date_type,
DECODE (c_version_type, 'REVENUE', NULL,
DECODE(inf.project_cost_exchange_rate, g_hidden_col_num, NULL, DECODE(inf.project_cost_exchange_rate, NULL, l_fnd_miss_num, inf.project_cost_exchange_rate))) project_cost_exchange_rate,
DECODE (c_version_type, 'REVENUE', TO_DATE(NULL),
DECODE(inf.project_cost_rate_date, g_hidden_col_date, TO_DATE(NULL), DECODE(inf.project_cost_rate_date, NULL, l_fnd_miss_date, inf.project_cost_rate_date))) project_cost_rate_date,
DECODE (c_version_type, 'COST', NULL,
DECODE(inf.projfunc_rev_rate_type, g_hidden_col_char, NULL, DECODE(inf.projfunc_rev_rate_type, NULL, l_fnd_miss_char, inf.projfunc_rev_rate_type))) projfunc_rev_rate_type,
DECODE (c_version_type, 'COST', NULL,
DECODE(inf.projfunc_rev_rate_date_type, g_hidden_col_char, NULL, DECODE(inf.projfunc_rev_rate_date_type, NULL, l_fnd_miss_char, inf.projfunc_rev_rate_date_type))) projfunc_rev_rate_date_type,
DECODE (c_version_type, 'COST', NULL,
DECODE(inf.projfunc_rev_exchange_rate, g_hidden_col_num, NULL, DECODE(inf.projfunc_rev_exchange_rate, NULL, l_fnd_miss_num, inf.projfunc_rev_exchange_rate))) projfunc_rev_exchange_rate,
DECODE (c_version_type, 'COST', TO_DATE(NULL),
DECODE(inf.projfunc_rev_rate_date, g_hidden_col_date, TO_DATE(NULL), DECODE(inf.projfunc_rev_rate_date, NULL, l_fnd_miss_date, inf.projfunc_rev_rate_date))) projfunc_rev_rate_date,
DECODE (c_version_type, 'COST', NULL,
DECODE(inf.project_rev_rate_type, g_hidden_col_char, NULL, DECODE(inf.project_rev_rate_type, NULL, l_fnd_miss_char, inf.project_rev_rate_type))) project_rev_rate_type,
DECODE (c_version_type, 'COST', NULL,
DECODE(inf.project_rev_rate_date_type, g_hidden_col_char, NULL, DECODE(inf.project_rev_rate_date_type, NULL, l_fnd_miss_char, inf.project_rev_rate_date_type))) project_rev_rate_date_type,
DECODE (c_version_type, 'COST', NULL,
DECODE(inf.project_rev_exchange_rate, g_hidden_col_num, NULL, DECODE(inf.project_rev_exchange_rate, NULL, l_fnd_miss_num, inf.project_rev_exchange_rate))) project_rev_exchange_rate,
DECODE (c_version_type, 'COST', TO_DATE(NULL),
DECODE(inf.project_rev_rate_date, g_hidden_col_date, TO_DATE(NULL), DECODE(inf.project_rev_rate_date, NULL, l_fnd_miss_date, inf.project_rev_rate_date))) project_rev_rate_date,
DECODE (inf.projfunc_rate_type, g_hidden_col_char, NULL, DECODE(inf.projfunc_rate_type, NULL, l_fnd_miss_char, inf.projfunc_rate_type)) projfunc_rate_type,
DECODE (inf.projfunc_rate_date_type, g_hidden_col_char, NULL, DECODE(inf.projfunc_rate_date_type, NULL, l_fnd_miss_char, inf.projfunc_rate_date_type)) projfunc_rate_date_type,
DECODE (inf.projfunc_exchange_rate, g_hidden_col_num, NULL, DECODE(inf.projfunc_exchange_rate, NULL, l_fnd_miss_num, inf.projfunc_exchange_rate)) projfunc_exchange_rate,
DECODE (inf.projfunc_rate_date, g_hidden_col_date, TO_DATE(NULL), DECODE(inf.projfunc_rate_date, NULL, l_fnd_miss_date, inf.projfunc_rate_date)) projfunc_rate_date,
DECODE (inf.project_rate_type, g_hidden_col_char, NULL, DECODE(inf.project_rate_type, NULL, l_fnd_miss_char, inf.project_rate_type)) project_rate_type,
DECODE (inf.project_rate_date_type, g_hidden_col_char, NULL, DECODE(inf.project_rate_date_type, NULL, l_fnd_miss_char, inf.project_rate_date_type)) project_rate_date_type,
DECODE (inf.project_exchange_rate, g_hidden_col_num, NULL, DECODE(inf.project_exchange_rate, NULL, l_fnd_miss_num, inf.project_exchange_rate)) project_exchange_rate,
DECODE (inf.project_rate_date, g_hidden_col_date, TO_DATE(NULL), DECODE(inf.project_rate_date, NULL, l_fnd_miss_date, inf.project_rate_date)) project_rate_date,
--DECODE (inf.spread_curve_id, g_hidden_col_char, NULL, DECODE(inf.spread_curve_name, NULL, l_fnd_miss_char, inf.spread_curve_name))
'-99' spread_curve_name,
--DECODE (inf.etc_method_name, g_hidden_col_char, NULL, DECODE(inf.etc_method_name, NULL, l_fnd_miss_char, inf.etc_method_name))
'-99' etc_method_name,
--DECODE (inf.mfc_cost_type_name, g_hidden_col_char, NULL, DECODE(inf.mfc_cost_type_name, NULL, l_fnd_miss_char, inf.mfc_cost_type_name))
'-99' mfc_cost_type_name,
DECODE (inf.pd_prd, g_hidden_col_num, NULL, DECODE(inf.pd_prd, NULL, l_fnd_miss_num, inf.pd_prd)) pd_prd,
DECODE (inf.prd1, g_hidden_col_num, NULL, DECODE(inf.prd1, NULL, l_fnd_miss_num, inf.prd1)) prd1,
DECODE (inf.prd2, g_hidden_col_num, NULL, DECODE(inf.prd2, NULL, l_fnd_miss_num, inf.prd2)) prd2,
DECODE (inf.prd3, g_hidden_col_num, NULL, DECODE(inf.prd3, NULL, l_fnd_miss_num, inf.prd3)) prd3,
DECODE (inf.prd4, g_hidden_col_num, NULL, DECODE(inf.prd4, NULL, l_fnd_miss_num, inf.prd4)) prd4,
DECODE (inf.prd5, g_hidden_col_num, NULL, DECODE(inf.prd5, NULL, l_fnd_miss_num, inf.prd5)) prd5,
DECODE (inf.prd6, g_hidden_col_num, NULL, DECODE(inf.prd6, NULL, l_fnd_miss_num, inf.prd6)) prd6,
DECODE (inf.prd7, g_hidden_col_num, NULL, DECODE(inf.prd7, NULL, l_fnd_miss_num, inf.prd7)) prd7,
DECODE (inf.prd8, g_hidden_col_num, NULL, DECODE(inf.prd8, NULL, l_fnd_miss_num, inf.prd8)) prd8,
DECODE (inf.prd9, g_hidden_col_num, NULL, DECODE(inf.prd9, NULL, l_fnd_miss_num, inf.prd9)) prd9,
DECODE (inf.prd10, g_hidden_col_num, NULL, DECODE(inf.prd10, NULL, l_fnd_miss_num, inf.prd10)) prd10,
DECODE (inf.prd11, g_hidden_col_num, NULL, DECODE(inf.prd11, NULL, l_fnd_miss_num, inf.prd11)) prd11,
DECODE (inf.prd12, g_hidden_col_num, NULL, DECODE(inf.prd12, NULL, l_fnd_miss_num, inf.prd12)) prd12,
DECODE (inf.prd13, g_hidden_col_num, NULL, DECODE(inf.prd13, NULL, l_fnd_miss_num, inf.prd13)) prd13,
DECODE (inf.prd14, g_hidden_col_num, NULL, DECODE(inf.prd14, NULL, l_fnd_miss_num, inf.prd14)) prd14,
DECODE (inf.prd15, g_hidden_col_num, NULL, DECODE(inf.prd15, NULL, l_fnd_miss_num, inf.prd15)) prd15,
DECODE (inf.prd16, g_hidden_col_num, NULL, DECODE(inf.prd16, NULL, l_fnd_miss_num, inf.prd16)) prd16,
DECODE (inf.prd17, g_hidden_col_num, NULL, DECODE(inf.prd17, NULL, l_fnd_miss_num, inf.prd17)) prd17,
DECODE (inf.prd18, g_hidden_col_num, NULL, DECODE(inf.prd18, NULL, l_fnd_miss_num, inf.prd18)) prd18,
DECODE (inf.prd19, g_hidden_col_num, NULL, DECODE(inf.prd19, NULL, l_fnd_miss_num, inf.prd19)) prd19,
DECODE (inf.prd20, g_hidden_col_num, NULL, DECODE(inf.prd20, NULL, l_fnd_miss_num, inf.prd20)) prd20,
DECODE (inf.prd21, g_hidden_col_num, NULL, DECODE(inf.prd21, NULL, l_fnd_miss_num, inf.prd21)) prd21,
DECODE (inf.prd22, g_hidden_col_num, NULL, DECODE(inf.prd22, NULL, l_fnd_miss_num, inf.prd22)) prd22,
DECODE (inf.prd23, g_hidden_col_num, NULL, DECODE(inf.prd23, NULL, l_fnd_miss_num, inf.prd23)) prd23,
DECODE (inf.prd24, g_hidden_col_num, NULL, DECODE(inf.prd24, NULL, l_fnd_miss_num, inf.prd24)) prd24,
DECODE (inf.prd25, g_hidden_col_num, NULL, DECODE(inf.prd25, NULL, l_fnd_miss_num, inf.prd25)) prd25,
DECODE (inf.prd26, g_hidden_col_num, NULL, DECODE(inf.prd26, NULL, l_fnd_miss_num, inf.prd26)) prd26,
DECODE (inf.prd27, g_hidden_col_num, NULL, DECODE(inf.prd27, NULL, l_fnd_miss_num, inf.prd27)) prd27,
DECODE (inf.prd28, g_hidden_col_num, NULL, DECODE(inf.prd28, NULL, l_fnd_miss_num, inf.prd28)) prd28,
DECODE (inf.prd29, g_hidden_col_num, NULL, DECODE(inf.prd29, NULL, l_fnd_miss_num, inf.prd29)) prd29,
DECODE (inf.prd30, g_hidden_col_num, NULL, DECODE(inf.prd30, NULL, l_fnd_miss_num, inf.prd30)) prd30,
DECODE (inf.prd31, g_hidden_col_num, NULL, DECODE(inf.prd31, NULL, l_fnd_miss_num, inf.prd31)) prd31,
DECODE (inf.prd32, g_hidden_col_num, NULL, DECODE(inf.prd32, NULL, l_fnd_miss_num, inf.prd32)) prd32,
DECODE (inf.prd33, g_hidden_col_num, NULL, DECODE(inf.prd33, NULL, l_fnd_miss_num, inf.prd33)) prd33,
DECODE (inf.prd34, g_hidden_col_num, NULL, DECODE(inf.prd34, NULL, l_fnd_miss_num, inf.prd34)) prd34,
DECODE (inf.prd35, g_hidden_col_num, NULL, DECODE(inf.prd35, NULL, l_fnd_miss_num, inf.prd35)) prd35,
DECODE (inf.prd36, g_hidden_col_num, NULL, DECODE(inf.prd36, NULL, l_fnd_miss_num, inf.prd36)) prd36,
DECODE (inf.prd37, g_hidden_col_num, NULL, DECODE(inf.prd37, NULL, l_fnd_miss_num, inf.prd37)) prd37,
DECODE (inf.prd38, g_hidden_col_num, NULL, DECODE(inf.prd38, NULL, l_fnd_miss_num, inf.prd38)) prd38,
DECODE (inf.prd39, g_hidden_col_num, NULL, DECODE(inf.prd39, NULL, l_fnd_miss_num, inf.prd39)) prd39,
DECODE (inf.prd40, g_hidden_col_num, NULL, DECODE(inf.prd40, NULL, l_fnd_miss_num, inf.prd40)) prd40,
DECODE (inf.prd41, g_hidden_col_num, NULL, DECODE(inf.prd41, NULL, l_fnd_miss_num, inf.prd41)) prd41,
DECODE (inf.prd42, g_hidden_col_num, NULL, DECODE(inf.prd42, NULL, l_fnd_miss_num, inf.prd42)) prd42,
DECODE (inf.prd43, g_hidden_col_num, NULL, DECODE(inf.prd43, NULL, l_fnd_miss_num, inf.prd43)) prd43,
DECODE (inf.prd44, g_hidden_col_num, NULL, DECODE(inf.prd44, NULL, l_fnd_miss_num, inf.prd44)) prd44,
DECODE (inf.prd45, g_hidden_col_num, NULL, DECODE(inf.prd45, NULL, l_fnd_miss_num, inf.prd45)) prd45,
DECODE (inf.prd46, g_hidden_col_num, NULL, DECODE(inf.prd46, NULL, l_fnd_miss_num, inf.prd46)) prd46,
DECODE (inf.prd47, g_hidden_col_num, NULL, DECODE(inf.prd47, NULL, l_fnd_miss_num, inf.prd47)) prd47,
DECODE (inf.prd48, g_hidden_col_num, NULL, DECODE(inf.prd48, NULL, l_fnd_miss_num, inf.prd48)) prd48,
DECODE (inf.prd49, g_hidden_col_num, NULL, DECODE(inf.prd49, NULL, l_fnd_miss_num, inf.prd49)) prd49,
DECODE (inf.prd50, g_hidden_col_num, NULL, DECODE(inf.prd50, NULL, l_fnd_miss_num, inf.prd50)) prd50,
DECODE (inf.prd51, g_hidden_col_num, NULL, DECODE(inf.prd51, NULL, l_fnd_miss_num, inf.prd51)) prd51,
DECODE (inf.prd52, g_hidden_col_num, NULL, DECODE(inf.prd52, NULL, l_fnd_miss_num, inf.prd52)) prd52,
DECODE (inf.sd_prd, g_hidden_col_num, NULL, DECODE(inf.sd_prd, NULL, l_fnd_miss_num, inf.sd_prd)) sd_prd
FROM pa_fp_webadi_upload_inf inf
WHERE inf.run_id=c_run_id
AND Nvl(inf.val_error_flag, 'N') <> 'Y'
AND inf.val_error_code IS NULL
AND (inf.amount_type_name IS NULL OR
(inf.amount_type_code IN ('TOTAL_QTY','FCST_QTY','ETC_QTY') AND
c_allow_qty_flag='Y') OR
(inf.amount_type_code IN ('TOTAL_RAW_COST','FCST_RAW_COST','ETC_RAW_COST') AND
c_allow_raw_cost_flag='Y') OR
(inf.amount_type_code IN ('TOTAL_BURDENED_COST','FCST_BURDENED_COST','ETC_BURDENED_COST') AND
c_allow_burd_cost_flag='Y') OR
(inf.amount_type_code IN ('TOTAL_REV','FCST_REVENUE','ETC_REVENUE') AND
c_allow_revenue_flag='Y') OR
(inf.amount_type_code IN ('RAW_COST_RATE','ETC_RAW_COST_RATE') AND
c_allow_raw_cost_rate_flag='Y') OR
(inf.amount_type_code IN ('BURDENED_COST_RATE','ETC_BURDENED_COST_RATE') AND
c_allow_burd_cost_rate_flag='Y') OR
(inf.amount_type_code IN ('BILL_RATE','ETC_BILL_RATE') AND
c_allow_bill_rate_flag='Y') )
AND Nvl(c_request_id, -99) = Nvl(inf.request_id, -99)
ORDER BY inf.task_id, inf.resource_list_member_id, Nvl(inf.txn_currency_code, '-99'),
DECODE (inf.amount_type_code, 'TOTAL_BURDENED_COST', 1,
'TOTAL_RAW_COST', 2,
'TOTAL_REV', 3,
'TOTAL_QTY', 4,
'BURDENED_COST_RATE', 5,
'RAW_COST_RATE', 6,
'BILL_RATE', 7,
'FCST_BURDENED_COST', 10,
'ETC_BURDENED_COST', 11,
'FCST_RAW_COST', 12,
'ETC_RAW_COST', 13,
'FCST_REVENUE', 14,
'ETC_REVENUE', 15,
'FCST_QTY', 16,
'ETC_QTY', 17,
'ETC_BURDENED_COST_RATE',18,
'ETC_RAW_COST_RATE', 19,
'ETC_BILL_RATE', 20, 0);
INSERT INTO FND_LOG_MESSAGES
(module
,log_level
,message_text
,session_id
,user_id
,timestamp
,log_sequence)
values
(p_module_name
,p_debug_level
,p_err_msg
,-1
,fnd_global.user_id
,sysdate
,fnd_log_messages_s.nextval
);
INSERT INTO t1
(c1,
c2)
values
(pa_fp_elements_s.nextval,
p_err_msg
);
PROCEDURE delete_xface
( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_calling_module IN VARCHAR2 DEFAULT NULL
)
IS
l_debug_mode VARCHAR2(1) ;
l_nothing_delete_flag VARCHAR2(1);
pa_debug.set_curr_function( p_function => 'DELETE_XFACE'
,p_debug_mode => l_debug_mode );
pa_debug.g_err_stage := ':In pa_fp_webadi_pkg.DELETE_XFACE' ;
l_nothing_delete_flag := 'N';
SELECT 'Y'
INTO l_preserve_rows_flag
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM pa_fp_webadi_upload_inf inf,
pa_budget_versions bv
WHERE inf.run_id = p_run_id
AND inf.budget_version_id = bv.budget_version_id
AND Nvl(inf.request_id, -99) = Nvl(bv.request_id, -99)
AND bv.plan_processing_code IN ('XLUP', 'XLUE'));
DELETE FROM PA_FP_WEBADI_UPLOAD_INF
WHERE run_id = p_run_id;
pa_debug.g_err_stage := ':Deleted '||l_del_record_count||' records from PA_FP_WEBADI_UPLOAD_INF';
,p_procedure_name => 'DELETE_XFACE' );
pa_debug.write('DELETE_XFACE' || g_module_name,SQLERRM,4);
pa_debug.write('DELETE_XFACE' || g_module_name,pa_debug.G_Err_Stack,4);
END DELETE_XFACE;
SELECT bv.record_version_number,
bv.period_mask_id,
bv.project_id,
bv.fin_plan_type_id,
Nvl(bv.ci_id, -1),
bv.version_type,
Nvl(bv.approved_cost_plan_type_flag, 'N'),
Nvl(bv.approved_rev_plan_type_flag, 'N'),
Nvl(bv.primary_cost_forecast_flag, 'N'),
Nvl(bv.primary_rev_forecast_flag, 'N'),
Nvl(bv.current_working_flag, 'N'),
fpt.plan_class_code,
Decode(bv.version_type, 'COST', fpo.cost_time_phased_code,
'REVENUE', fpo.revenue_time_phased_code, 'ALL', fpo.all_time_phased_code) time_phased_code
FROM pa_budget_versions bv,
pa_fin_plan_types_b fpt,
pa_proj_fp_options fpo
WHERE bv.budget_version_id = c_budget_version_id
AND bv.budget_version_id = fpo.fin_plan_version_id
AND bv.project_id = fpo.project_id
AND bv.fin_plan_type_id = fpt.fin_plan_type_id;
SELECT 'Y'
INTO is_periodic_layout
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM pa_fp_webadi_upload_inf
WHERE run_id = p_run_id
AND amount_type_name IS NOT NULL
AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
SELECT record_version_number
INTO l_pm_rec_version_number
FROM pa_period_masks_b
WHERE period_mask_id = l_period_mask_id;
p_function_name => 'PA_PM_UPDATE_BUDGET',
p_version_type => l_version_type,
x_return_status => l_return_status,
x_ret_code => l_sec_ret_code);
p_function_name => 'PA_PM_UPDATE_CHG_DOC',
p_version_type => l_version_type,
x_return_status => l_return_status,
x_ret_code => l_sec_ret_code);
pa_debug.g_err_stage := 'Checking if the CO can be updated';
SELECT pjs.project_system_status_code
INTO l_ci_status_code
FROM pa_control_items pci,
pa_project_statuses pjs
WHERE pci.ci_id = l_ci_id
and pci.status_code = pjs.project_status_code;
pa_debug.g_err_stage := 'CO version cannot be updated';
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WEBADI_VER_MODIFIED',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WEBADI_TP_MODIFIED',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WEBADI_NO_BV_MAINT_PVLG',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = l_val_err_code,
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WA_CI_VER_NON_EDITABLE',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WA_BV_NOT_CURR_WORKING',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WA_CI_VER_SUBMIT_FLAG',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
* from the task number/ task name provided i.e task_id is returned as null from the select
* in prepare_val_input api. The valid value of this is 'INV_TASK.
* For other validation errors, the corresponding error code would be passed to the api
* along with other informations like task_id, resource_alias, amount_type_code
*/
-- Making this procedure an autonomous transition block as the errors reported
-- on the interface table have to be retained even after rolling back all the
-- other DML performed in the course of validation processing till the point
-- a validation failure occurs.
PROCEDURE process_errors
( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
p_context IN VARCHAR2,
p_periodic_flag IN VARCHAR2,
p_error_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_rlm_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_txn_curr_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
p_amount_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
p_request_id IN pa_budget_versions.request_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
SELECT 'Y'
INTO l_periodic_flag
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pa_fp_webadi_upload_inf
WHERE run_id=p_run_id
AND amount_type_name IS NOT NULL
AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'INVALID_TASK_INFO',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id = p_run_id
AND task_id IS NULL
AND val_error_flag IS NULL
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'INVALID_RESOURCE_INFO',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id = p_run_id
AND resource_list_member_id IS NULL
AND val_error_flag IS NULL
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = p_error_code_tbl(i),
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id = p_run_id
AND task_id = p_task_id_tbl(i)
AND resource_list_member_id = p_rlm_id_tbl(i)
AND Nvl(txn_currency_code, '-11') = Nvl(p_txn_curr_tbl(i), '-11')
AND amount_type_code = p_amount_type_tbl(i)
AND Nvl(p_request_id, -99) = Nvl(request_id, -99)
AND ROWNUM=1;
UPDATE pa_fp_webadi_upload_inf inf
SET inf.val_error_flag = 'Y',
inf.val_error_code = p_error_code_tbl(i),
inf.err_task_name = nvl(inf.task_name,'-98'),
inf.err_task_number = nvl(inf.task_number,'-98'),
inf.err_alias = nvl(inf.resource_alias,'-98'),
inf.err_amount_type_code = nvl(inf.amount_type_code,'-98')
WHERE inf.run_id = p_run_id
AND inf.task_id = p_task_id_tbl(i)
AND inf.resource_list_member_id = p_rlm_id_tbl(i)
AND Nvl(inf.txn_currency_code, '-11') = Nvl(p_txn_curr_tbl(i), '-11')
AND Nvl(p_request_id, -99) = Nvl(inf.request_id, -99)
AND ROWNUM=1;
g_fp_webadi_rec_tbl.DELETE;
g_fp_webadi_rec_tbl.DELETE;
g_fp_webadi_rec_tbl.DELETE;
PROCEDURE check_and_update_txn_curr_code
(p_budget_line_tbl IN PA_BUDGET_PUB.G_budget_lines_in_tbl%TYPE,
px_inf_cur_rec_tbl IN OUT NOCOPY inf_cur_tbl_typ, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_return_status VARCHAR2(1);
l_module_name VARCHAR2(100) := 'PAFPWAPB.check_and_update_txn_curr_code';
pa_debug.g_err_stage := 'Entering into pa_fp_webadi_pkg.check_and_update_txn_curr_code';
pa_debug.g_err_stage:=' Nothing to be updated, returning';
pa_debug.g_err_stage := 'Leaving into pa_fp_webadi_pkg.check_and_update_txn_curr_code';
,p_procedure_name => 'check_and_update_txn_curr_code');
END check_and_update_txn_curr_code;
l_module_name VARCHAR2(100) := 'PAFPWAPB.check_and_update_txn_curr_code';
SELECT projfunc_rate_type,
projfunc_rate_date_type,
projfunc_rate_date,
project_rate_type,
project_rate_date_type,
project_rate_date
INTO l_projfunc_rate_type,
l_projfunc_rate_date_type,
l_projfunc_rate_date,
l_project_rate_type,
l_project_rate_date_type,
l_project_rate_date
FROM pa_fp_webadi_upload_inf
WHERE run_id = p_run_id
AND ROWNUM = 1;
x_delete_flag_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE, --File.Sql.39 bug 4440895
x_ra_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE, --File.Sql.39 bug 4440895
x_res_class_code_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE, --File.Sql.39 bug 4440895
x_rate_based_flag_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE, --File.Sql.39 bug 4440895
x_rbs_elem_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE, --File.Sql.39 bug 4440895
x_cbs_elem_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE, -- cbs enhancement changes bug#16445106
x_amt_type_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE, --File.Sql.39 bug 4440895
x_first_pd_bf_pm_en_dt OUT NOCOPY DATE, --File.Sql.39 bug 4440895
x_last_pd_af_pm_st_dt OUT NOCOPY DATE, --File.Sql.39 bug 4440895
x_inf_tbl_rec_tbl OUT NOCOPY inf_cur_tbl_typ, --File.Sql.39 bug 4440895
x_num_of_rec_processed OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_return_status VARCHAR2(30);
l_delete_flag VARCHAR2(1);
l_delete_flag_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
SELECT gl.start_date start_date,
gl.end_date end_date,
gl.period_name period_name
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE gl.end_date < c_first_prd_st_dt
AND gl.period_set_name = DECODE(c_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(c_time_phased_code,'P',pim.pa_period_type,'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND pim.org_id = c_org_id
AND gsb.set_of_books_id = pim.set_of_books_id
ORDER BY gl.start_date DESC;
SELECT gl.start_date start_date,
gl.end_date end_date,
gl.period_name period_name
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE gl.end_date > c_last_prd_en_dt
AND gl.period_set_name = DECODE(c_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(c_time_phased_code,'P',pim.pa_period_type,'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND pim.org_id = c_org_id
AND gsb.set_of_books_id = pim.set_of_books_id
ORDER BY gl.start_date ;
SELECT rac.total_display_quantity,
rac.total_txn_raw_cost,
rac.total_txn_burdened_cost,
rac.total_txn_revenue,
rac.total_display_quantity-NVL(rac.total_init_quantity,0),
rac.total_txn_raw_cost-NVL(rac.total_txn_init_raw_cost,0),
rac.total_txn_burdened_cost-NVL(rac.total_txn_init_burdened_cost,0),
rac.total_txn_revenue-NVL(rac.total_txn_init_revenue,0),
rac.txn_raw_cost_rate_override,
rac.txn_burden_cost_rate_override,
rac.txn_bill_rate_override,
pra.rate_based_flag
FROM pa_resource_asgn_curr rac,
pa_resource_assignments pra
WHERE rac.budget_version_id = c_budget_version_id
AND rac.budget_version_id = pra.budget_version_id
AND rac.resource_assignment_id = pra.resource_assignment_id
AND pra.task_id = c_task_id
AND pra.resource_list_member_id = c_resource_list_member_id
AND nvl(pra.cbs_element_id,-1) = nvl(c_cbs_element_id,-1) -- cbs enhancement changes bug#16445106
AND rac.txn_currency_code = c_txn_currency_code;
SELECT control_flag,
uncategorized_flag
INTO l_rl_control_flag,
l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_version_info_rec.x_resource_list_id;
UPDATE pa_fp_webadi_upload_inf inf
SET inf.task_id = (SELECT pt.task_id
FROM (SELECT pt.name task_name, /* Bug 4929163. Modified the select statement to refer to pa_proj_elements instead of pa_tasks*/
pt.element_number task_number,
pt.proj_element_id task_id
FROM pa_proj_elements pt
WHERE pt.project_id = p_version_info_rec.x_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_id = l_fin_struct_id
UNION ALL
SELECT p.long_name task_name, /* Bug 5345336 */
p.segment1 task_number,
0 task_id
FROM pa_projects_all p
WHERE p.project_id = p_version_info_rec.x_project_id) pt
WHERE (inf.task_name IS NOT NULL
AND inf.task_number IS NULL
AND inf.task_name = pt.task_name) OR
(inf.task_name IS NULL
AND inf.task_number IS NOT NULL
AND inf.task_number = pt.task_number) OR
(inf.task_name IS NOT NULL
AND inf.task_number IS NOT NULL
AND inf.task_name = pt.task_name
AND inf.task_number = pt.task_number)),
inf.change_reason_code = DECODE(inf.delete_flag,'Y',NULL,inf.change_reason_code),
inf.quantity = DECODE(inf.delete_flag,'Y',NULL,inf.quantity),
inf.raw_cost = DECODE(inf.delete_flag,'Y',NULL,inf.raw_cost),
inf.raw_cost_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.raw_cost_over_rate),
inf.burdened_cost = DECODE(inf.delete_flag,'Y',NULL,inf.burdened_cost),
inf.burdened_cost_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.burdened_cost_over_rate),
inf.revenue = DECODE(inf.delete_flag,'Y',NULL,inf.revenue),
inf.bill_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.bill_over_rate ),
inf.projfunc_cost_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_rate_type),
inf.projfunc_cost_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_rate_date_type),
inf.projfunc_cost_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_exchange_rate ),
inf.projfunc_cost_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_cost_rate_date),
inf.project_cost_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_rate_type),
inf.project_cost_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_rate_date_type),
inf.project_cost_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_exchange_rate),
inf.project_cost_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_cost_rate_date),
inf.projfunc_rev_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_rate_type ),
inf.projfunc_rev_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_rate_date_type ),
inf.projfunc_rev_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_exchange_rate ),
inf.projfunc_rev_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_rev_rate_date),
inf.project_rev_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_rate_type ),
inf.project_rev_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_rate_date_type ),
inf.project_rev_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_exchange_rate ),
inf.project_rev_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_rev_rate_date),
inf.projfunc_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rate_type ),
inf.projfunc_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rate_date_type ),
inf.projfunc_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_exchange_rate ),
inf.projfunc_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_rate_date),
inf.project_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rate_type ),
inf.project_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rate_date_type ),
inf.project_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_exchange_rate ),
inf.project_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_rate_date),
inf.pd_prd = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.pd_prd),
inf.prd1 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd1 ),
inf.prd2 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd2 ),
inf.prd3 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd3 ),
inf.prd4 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd4 ),
inf.prd5 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd5 ),
inf.prd6 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd6 ),
inf.prd7 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd7 ),
inf.prd8 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd8 ),
inf.prd9 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd9 ),
inf.prd10 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd10 ),
inf.prd11 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd11 ),
inf.prd12 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd12 ),
inf.prd13 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd13 ),
inf.prd14 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd14 ),
inf.prd15 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd15 ),
inf.prd16 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd16 ),
inf.prd17 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd17 ),
inf.prd18 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd18 ),
inf.prd19 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd19 ),
inf.prd20 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd20 ),
inf.prd21 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd21 ),
inf.prd22 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd22 ),
inf.prd23 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd23 ),
inf.prd24 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd24 ),
inf.prd25 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd25 ),
inf.prd26 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd26 ),
inf.prd27 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd27 ),
inf.prd28 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd28 ),
inf.prd29 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd29 ),
inf.prd30 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd30 ),
inf.prd31 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd31 ),
inf.prd32 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd32 ),
inf.prd33 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd33 ),
inf.prd34 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd34 ),
inf.prd35 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd35 ),
inf.prd36 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd36 ),
inf.prd37 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd37 ),
inf.prd38 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd38 ),
inf.prd39 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd39 ),
inf.prd40 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd40 ),
inf.prd41 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd41 ),
inf.prd42 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd42 ),
inf.prd43 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd43 ),
inf.prd44 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd44 ),
inf.prd45 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd45 ),
inf.prd46 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd46 ),
inf.prd47 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd47 ),
inf.prd48 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd48 ),
inf.prd49 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd49 ),
inf.prd50 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd50 ),
inf.prd51 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd51 ),
inf.prd52 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd52 ),
inf.sd_prd = DECODE(inf.delete_flag,'Y',l_fnd_miss_num , inf.sd_prd),
inf.resource_list_member_id = (SELECT rlm.resource_list_member_id
FROM pa_resource_list_members rlm
WHERE inf.resource_alias = rlm.alias
AND rlm.resource_list_id = p_version_info_rec.x_resource_list_id
AND rlm.object_type = 'PROJECT'
AND rlm.object_id = p_version_info_rec.x_project_id),
inf.cbs_element_id = DECODE(l_Cbs_Enabled,'N',NULL,
(select pre.rbs_element_id from pa_rbs_elements pre where
pre.rbs_version_id = (select cbs_version_id from pa_projects_all where project_id = p_version_info_rec.x_project_id) and
inf.cost_code = pre.cost_code))-- cbs enhancement changes bug#16445106
WHERE inf.run_id= p_run_id
AND Nvl(p_request_id, -99) = Nvl(inf.request_id, -99);
--DEBUG_NS('updated pa_fp_webadi_upload_inf :: ');
UPDATE pa_fp_webadi_upload_inf inf
SET inf.task_id = (SELECT pt.task_id
FROM (SELECT pt.name task_name, /* Bug 4929163. Modified the select statement to refer to pa_proj_elements instead of pa_tasks*/
pt.element_number task_number,
pt.proj_element_id task_id
FROM pa_proj_elements pt
WHERE pt.project_id = p_version_info_rec.x_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_id = l_fin_struct_id
UNION ALL
SELECT p.long_name task_name, /* Bug 5345336 */
p.segment1 task_number,
0 task_id
FROM pa_projects_all p
WHERE p.project_id = p_version_info_rec.x_project_id) pt
WHERE (inf.task_name IS NOT NULL
AND inf.task_number IS NULL
AND inf.task_name = pt.task_name) OR
(inf.task_name IS NULL
AND inf.task_number IS NOT NULL
AND inf.task_number = pt.task_number) OR
(inf.task_name IS NOT NULL
AND inf.task_number IS NOT NULL
AND inf.task_name = pt.task_name
AND inf.task_number = pt.task_number)),
inf.change_reason_code = DECODE(inf.delete_flag,'Y',NULL,inf.change_reason_code),
inf.quantity = DECODE(inf.delete_flag,'Y',NULL,inf.quantity),
inf.raw_cost = DECODE(inf.delete_flag,'Y',NULL,inf.raw_cost),
inf.raw_cost_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.raw_cost_over_rate),
inf.burdened_cost = DECODE(inf.delete_flag,'Y',NULL,inf.burdened_cost),
inf.burdened_cost_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.burdened_cost_over_rate),
inf.revenue = DECODE(inf.delete_flag,'Y',NULL,inf.revenue),
inf.bill_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.bill_over_rate ),
inf.projfunc_cost_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_rate_type),
inf.projfunc_cost_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_rate_date_type),
inf.projfunc_cost_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_exchange_rate ),
inf.projfunc_cost_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_cost_rate_date),
inf.project_cost_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_rate_type),
inf.project_cost_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_rate_date_type),
inf.project_cost_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_exchange_rate),
inf.project_cost_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_cost_rate_date),
inf.projfunc_rev_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_rate_type ),
inf.projfunc_rev_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_rate_date_type ),
inf.projfunc_rev_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_exchange_rate ),
inf.projfunc_rev_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_rev_rate_date),
inf.project_rev_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_rate_type ),
inf.project_rev_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_rate_date_type ),
inf.project_rev_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_exchange_rate ),
inf.project_rev_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_rev_rate_date),
inf.projfunc_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rate_type ),
inf.projfunc_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rate_date_type ),
inf.projfunc_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_exchange_rate ),
inf.projfunc_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_rate_date),
inf.project_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rate_type ),
inf.project_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rate_date_type ),
inf.project_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_exchange_rate ),
inf.project_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_rate_date),
inf.pd_prd = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.pd_prd),
inf.prd1 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd1 ),
inf.prd2 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd2 ),
inf.prd3 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd3 ),
inf.prd4 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd4 ),
inf.prd5 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd5 ),
inf.prd6 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd6 ),
inf.prd7 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd7 ),
inf.prd8 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd8 ),
inf.prd9 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd9 ),
inf.prd10 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd10 ),
inf.prd11 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd11 ),
inf.prd12 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd12 ),
inf.prd13 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd13 ),
inf.prd14 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd14 ),
inf.prd15 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd15 ),
inf.prd16 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd16 ),
inf.prd17 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd17 ),
inf.prd18 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd18 ),
inf.prd19 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd19 ),
inf.prd20 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd20 ),
inf.prd21 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd21 ),
inf.prd22 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd22 ),
inf.prd23 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd23 ),
inf.prd24 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd24 ),
inf.prd25 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd25 ),
inf.prd26 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd26 ),
inf.prd27 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd27 ),
inf.prd28 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd28 ),
inf.prd29 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd29 ),
inf.prd30 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd30 ),
inf.prd31 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd31 ),
inf.prd32 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd32 ),
inf.prd33 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd33 ),
inf.prd34 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd34 ),
inf.prd35 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd35 ),
inf.prd36 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd36 ),
inf.prd37 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd37 ),
inf.prd38 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd38 ),
inf.prd39 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd39 ),
inf.prd40 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd40 ),
inf.prd41 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd41 ),
inf.prd42 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd42 ),
inf.prd43 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd43 ),
inf.prd44 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd44 ),
inf.prd45 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd45 ),
inf.prd46 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd46 ),
inf.prd47 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd47 ),
inf.prd48 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd48 ),
inf.prd49 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd49 ),
inf.prd50 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd50 ),
inf.prd51 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd51 ),
inf.prd52 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd52 ),
inf.sd_prd = DECODE(inf.delete_flag,'Y',l_fnd_miss_num , inf.sd_prd),
inf.resource_list_member_id = (SELECT rlm.resource_list_member_id
FROM pa_resource_list_members rlm
WHERE inf.resource_alias = rlm.alias
AND rlm.resource_list_id = p_version_info_rec.x_resource_list_id
AND rlm.object_type = 'RESOURCE_LIST'
AND rlm.object_id = p_version_info_rec.x_resource_list_id),
inf.cbs_element_id = DECODE(l_Cbs_Enabled,'N',NULL,
(select pre.rbs_element_id from pa_rbs_elements pre where
pre.rbs_version_id = (select cbs_version_id from pa_projects_all where project_id = p_version_info_rec.x_project_id) and
inf.cost_code = pre.cost_code))-- cbs enhancement changes bug#16445106
WHERE inf.run_id= p_run_id
AND Nvl(p_request_id, -99) = Nvl(inf.request_id, -99);
SELECT resource_list_member_id
INTO l_unct_rlm_id
FROM pa_resource_list_members
WHERE resource_list_id = p_version_info_rec.x_resource_list_id
AND resource_class_flag = 'Y'
AND resource_class_code = 'FINANCIAL_ELEMENTS'
AND object_type = 'RESOURCE_LIST'
AND object_id = p_version_info_rec.x_resource_list_id;
UPDATE pa_fp_webadi_upload_inf inf
SET inf.task_id = (SELECT pt.task_id
FROM (SELECT pt.name task_name, /* Bug 4929163. Modidfied the select statement to refer to pa_proj_elements instead of pa_tasks*/
pt.element_number task_number,
pt.proj_element_id task_id
FROM pa_proj_elements pt
WHERE pt.project_id = p_version_info_rec.x_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_id = l_fin_struct_id
UNION ALL
SELECT p.long_name task_name, /* Bug 5345336 */
p.segment1 task_number,
0 task_id
FROM pa_projects_all p
WHERE p.project_id = p_version_info_rec.x_project_id) pt
WHERE (inf.task_name IS NOT NULL
AND inf.task_number IS NULL
AND inf.task_name = pt.task_name) OR
(inf.task_name IS NULL
AND inf.task_number IS NOT NULL
AND inf.task_number = pt.task_number) OR
(inf.task_name IS NOT NULL
AND inf.task_number IS NOT NULL
AND inf.task_name = pt.task_name
AND inf.task_number = pt.task_number)),
inf.change_reason_code = DECODE(inf.delete_flag,'Y',NULL,inf.change_reason_code),
inf.quantity = DECODE(inf.delete_flag,'Y',NULL,inf.quantity),
inf.raw_cost = DECODE(inf.delete_flag,'Y',NULL,inf.raw_cost),
inf.raw_cost_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.raw_cost_over_rate),
inf.burdened_cost = DECODE(inf.delete_flag,'Y',NULL,inf.burdened_cost),
inf.burdened_cost_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.burdened_cost_over_rate),
inf.revenue = DECODE(inf.delete_flag,'Y',NULL,inf.revenue),
inf.bill_over_rate = DECODE(inf.delete_flag,'Y',NULL,inf.bill_over_rate ),
inf.projfunc_cost_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_rate_type),
inf.projfunc_cost_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_rate_date_type),
inf.projfunc_cost_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_cost_exchange_rate ),
inf.projfunc_cost_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_cost_rate_date),
inf.project_cost_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_rate_type),
inf.project_cost_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_rate_date_type),
inf.project_cost_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_cost_exchange_rate),
inf.project_cost_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.project_cost_rate_date),
inf.projfunc_rev_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_rate_type ),
inf.projfunc_rev_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_rate_date_type ),
inf.projfunc_rev_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rev_exchange_rate ),
inf.projfunc_rev_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL),inf.projfunc_rev_rate_date),
inf.project_rev_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_rate_type ),
inf.project_rev_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_rate_date_type ),
inf.project_rev_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_rev_exchange_rate ),
inf.project_rev_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL), inf.project_rev_rate_date),
inf.projfunc_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rate_type ),
inf.projfunc_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_rate_date_type ),
inf.projfunc_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.projfunc_exchange_rate ),
inf.projfunc_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL), inf.projfunc_rate_date),
inf.project_rate_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rate_type ),
inf.project_rate_date_type = DECODE(inf.delete_flag,'Y',NULL,inf.project_rate_date_type ),
inf.project_exchange_rate = DECODE(inf.delete_flag,'Y',NULL,inf.project_exchange_rate ),
inf.project_rate_date = DECODE(inf.delete_flag,'Y',TO_DATE(NULL), inf.project_rate_date),
inf.pd_prd = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.pd_prd),
inf.prd1 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd1 ),
inf.prd2 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd2 ),
inf.prd3 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd3 ),
inf.prd4 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd4 ),
inf.prd5 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd5 ),
inf.prd6 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd6 ),
inf.prd7 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd7 ),
inf.prd8 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd8 ),
inf.prd9 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd9 ),
inf.prd10 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd10 ),
inf.prd11 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd11 ),
inf.prd12 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd12 ),
inf.prd13 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd13 ),
inf.prd14 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd14 ),
inf.prd15 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd15 ),
inf.prd16 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd16 ),
inf.prd17 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd17 ),
inf.prd18 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd18 ),
inf.prd19 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd19 ),
inf.prd20 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd20 ),
inf.prd21 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd21 ),
inf.prd22 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd22 ),
inf.prd23 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd23 ),
inf.prd24 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd24 ),
inf.prd25 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd25 ),
inf.prd26 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd26 ),
inf.prd27 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd27 ),
inf.prd28 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd28 ),
inf.prd29 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd29 ),
inf.prd30 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd30 ),
inf.prd31 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd31 ),
inf.prd32 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd32 ),
inf.prd33 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd33 ),
inf.prd34 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd34 ),
inf.prd35 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd35 ),
inf.prd36 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd36 ),
inf.prd37 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd37 ),
inf.prd38 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd38 ),
inf.prd39 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd39 ),
inf.prd40 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd40 ),
inf.prd41 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd41 ),
inf.prd42 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd42 ),
inf.prd43 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd43 ),
inf.prd44 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd44 ),
inf.prd45 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd45 ),
inf.prd46 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd46 ),
inf.prd47 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd47 ),
inf.prd48 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd48 ),
inf.prd49 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd49 ),
inf.prd50 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd50 ),
inf.prd51 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd51 ),
inf.prd52 = DECODE(inf.delete_flag,'Y',l_fnd_miss_num,inf.prd52 ),
inf.sd_prd = DECODE(inf.delete_flag,'Y',l_fnd_miss_num , inf.sd_prd),
inf.resource_list_member_id = l_unct_rlm_id,
inf.cbs_element_id = DECODE(l_Cbs_Enabled,'N',NULL,
(select pre.rbs_element_id from pa_rbs_elements pre where
pre.rbs_version_id = (select cbs_version_id from pa_projects_all where project_id = p_version_info_rec.x_project_id) and
inf.cost_code = pre.cost_code))-- cbs enhancement changes bug#16445106
WHERE inf.run_id= p_run_id
AND Nvl(p_request_id, -99) = Nvl(inf.request_id, -99);
pa_debug.g_err_stage := 'Task ids updated';
SELECT 'Y'
INTO is_periodic_setup
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM pa_fp_webadi_upload_inf
WHERE amount_type_name IS NOT NULL
AND run_id = p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
UPDATE pa_fp_webadi_upload_inf inf
SET amount_type_code = (SELECT pl.lookup_code
FROM pa_lookups pl,pa_fp_proj_xl_amt_types xlt
WHERE lookup_type=l_lookup_type
AND meaning=inf.amount_type_name
AND xlt.project_id=p_version_info_rec.x_project_id
AND xlt.fin_plan_type_id=p_version_info_rec.x_fin_plan_type_id
AND xlt.option_type=p_version_info_rec.x_version_type
AND xlt.amount_type_code=pl.lookup_code),
val_error_flag = NVL(val_error_flag,
DECODE(task_id,
NULL,'Y',
DECODE(resource_list_member_id,
NULL,'Y',
DECODE(l_Cbs_Enabled,'N',NULL,
DECODE(cbs_element_id,
NULL,'Y',NULL))))),-- cbs enhancement changes bug#16445106
val_error_code = NVL(val_error_code,
DECODE(task_id,
NULL,'INVALID_TASK_INFO',
DECODE(resource_list_member_id,
NULL,'INVALID_RESOURCE_INFO',
DECODE(l_Cbs_Enabled,'N',NULL,
DECODE(cbs_element_id,
NULL,'INVALID_CBS_INFO',NULL))))),-- cbs enhancement changes bug#16445106
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
UPDATE pa_fp_webadi_upload_inf inf
SET val_error_flag = NVL(val_error_flag,
DECODE(task_id,
NULL,'Y',
DECODE(resource_list_member_id,
NULL,'Y',
DECODE(l_Cbs_Enabled,'N',NULL,
DECODE(cbs_element_id,
NULL,'Y',NULL))))),-- cbs enhancement changes bug#16445106
val_error_code = NVL(val_error_code,
DECODE(task_id,
NULL,'INVALID_TASK_INFO',
DECODE(resource_list_member_id,
NULL,'INVALID_RESOURCE_INFO',
DECODE(l_Cbs_Enabled,'N',NULL,
DECODE(cbs_element_id,
NULL,'INVALID_CBS_INFO', NULL))))),-- cbs enhancement changes bug#16445106
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
--DEBUG_NS('before insert_test_dump1:: ' );
--insert_test_dump1;
SELECT t.name,
a.spread_curve_id
INTO l_fixed_spread_curve_name,
l_fixed_spread_curve_id
FROM pa_spread_curves_b a,
pa_spread_curves_tl t
WHERE a.spread_curve_id = t.spread_curve_id
AND a.spread_curve_code = 'FIXED_DATE'
AND t.language = userenv('LANG');
SELECT project_cost_rate_type,
project_cost_rate_date_type,
project_cost_rate_date,
projfunc_cost_rate_type,
projfunc_cost_rate_date_type,
projfunc_cost_rate_date,
project_rev_rate_type,
project_rev_rate_date_type,
project_rev_rate_date,
projfunc_rev_rate_type,
projfunc_rev_rate_date_type,
projfunc_rev_rate_date
INTO d_project_cost_rate_type,
d_project_cost_rate_date_type,
d_project_cost_rate_date,
d_projfunc_cost_rate_type,
d_projfunc_cost_rate_date_type,
d_projfunc_cost_rate_date,
d_project_rev_rate_type,
d_project_rev_rate_date_type,
d_project_rev_rate_date,
d_projfunc_rev_rate_type,
d_projfunc_rev_rate_date_type,
d_projfunc_rev_rate_date
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_version_info_rec.x_budget_version_id
AND fin_plan_preference_code = 'COST_AND_REV_SAME';
SELECT txn_currency_code,
project_cost_exchange_rate,
project_rev_exchange_rate,
projfunc_cost_exchange_rate,
projfunc_rev_exchange_rate
BULK COLLECT INTO
d_txn_curr_tab,
d_project_cost_exc_rate_tab,
d_project_rev_exc_rate_tab,
d_projfunc_cost_exc_rate_tab,
d_projfunc_rev_exc_rate_tab
FROM pa_fp_txn_currencies
WHERE proj_fp_options_id = p_version_info_rec.x_proj_fp_options_id;
SELECT resource_assignment_id,
planning_start_date,
planning_end_date,
spread_curve_id,
sp_fixed_date,
rbs_element_id,
cbs_element_id, -- cbs enhancement changes bug#16445106
resource_class_code,
rate_based_flag
INTO l_ra_id,
l_plan_start_date,
l_plan_end_date,
l_spread_curve_id,
l_sp_fixed_date,
l_rbs_elem_id,
l_cbs_elem_id, -- cbs enhancement changes bug#16445106
l_res_class_code,
l_rate_based_flag
FROM pa_resource_assignments
WHERE project_id = p_version_info_rec.x_project_id
AND budget_version_id = p_version_info_rec.x_budget_version_id
AND task_id = l_inf_tbl_data_curr_rec.task_id
AND resource_list_member_id = l_inf_tbl_data_curr_rec.resource_list_member_id
AND nvl(cbs_element_id,-1) = nvl(l_inf_tbl_data_curr_rec.cbs_element_id,-1)
AND project_assignment_id = -1;
SELECT spread_curve_id
INTO l_spread_curve_id
FROM pa_resource_list_members
WHERE resource_list_member_id = l_inf_tbl_data_curr_rec.resource_list_member_id;
IF l_inf_tbl_data_curr_rec.delete_flag = 'Y' THEN
l_bdgt_line_start_date := l_plan_start_date;
IF l_inf_tbl_data_curr_rec.delete_flag = 'Y' THEN
IF is_periodic_setup = 'Y' THEN
IF l_inf_tbl_data_curr_rec.amount_type_code = 'TOTAL_RAW_COST' THEN
l_raw_cost := l_fnd_miss_num;
IF l_inf_tbl_data_curr_rec.delete_flag = 'N' THEN
IF l_plan_trans_attr_copied_flag = 'N' THEN
l_bdgt_ln_tbl_description := l_inf_tbl_data_curr_rec.description;
l_delete_flag := 'Y';
l_delete_flag := 'N';
l_delete_flag_tbl.EXTEND(1);
l_delete_flag_tbl(bl_count) := l_delete_flag;
x_delete_flag_tbl := l_delete_flag_tbl;
SELECT SUM(NVL(pbl.quantity,0)),
SUM(NVL(pbl.quantity,0)-NVL(pbl.init_quantity,0)),
SUM(NVL(pbl.txn_raw_cost,0)),
SUM(NVL(pbl.txn_raw_cost,0)-NVL(pbl.txn_init_raw_cost,0)),
SUM(NVL(pbl.txn_burdened_cost,0)),
SUM(NVL(pbl.txn_burdened_cost,0)-NVL(pbl.txn_init_burdened_cost,0)),
SUM(NVL(pbl.txn_revenue,0)),
SUM(NVL(pbl.txn_revenue,0)-NVL(txn_init_revenue,0)),
pbl.txn_currency_code
BULK COLLECT INTO
l_fcst_qty_tbl,
l_etc_qty_tbl,
l_fcst_raw_cost_tbl,
l_etc_raw_cost_tbl,
l_fcst_burd_cost_tbl,
l_etc_burd_cost_tbl,
l_fcst_revenue_tbl,
l_etc_revenue_tbl,
l_txn_currency_code_tbl
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pra.budget_version_id=p_budget_version_id
AND pra.project_id=p_project_id
AND pra.task_id=p_task_id
AND pra.resource_list_member_id=p_resource_list_member_id
AND nvl(pra.cbs_element_id,-1)=nvl(p_cbs_element_id,-1) -- cbs enhancement changes bug#16445106
AND pra.project_assignment_id=-1
AND pbl.resource_assignment_id=pra.resource_assignment_id
GROUP BY pbl.txn_currency_code;
SELECT NVL(pbl.quantity,0),
NVL(pbl.quantity,0)-NVL(pbl.init_quantity,0),
NVL(pbl.txn_raw_cost,0),
NVL(pbl.txn_raw_cost,0)-NVL(pbl.txn_init_raw_cost,0),
NVL(pbl.txn_burdened_cost,0),
NVL(pbl.txn_burdened_cost,0)-NVL(pbl.txn_init_burdened_cost,0),
NVL(pbl.txn_revenue,0),
NVL(pbl.txn_revenue,0)-NVL(txn_init_revenue,0),
pbl.txn_currency_code,
pbl.start_date,
pbl.end_date
BULK COLLECT INTO
l_fcst_qty_tbl,
l_etc_qty_tbl,
l_fcst_raw_cost_tbl,
l_etc_raw_cost_tbl,
l_fcst_burd_cost_tbl,
l_etc_burd_cost_tbl,
l_fcst_revenue_tbl,
l_etc_revenue_tbl,
l_txn_currency_code_tbl,
l_start_date_tbl,
l_end_date_tbl
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pra.budget_version_id=p_budget_version_id
AND pra.project_id=p_project_id
AND pra.task_id=p_task_id
AND pra.resource_list_member_id=p_resource_list_member_id
AND nvl(pra.cbs_element_id,-1)=nvl(p_cbs_element_id,-1) -- cbs enhancement changes bug#16445106
AND pra.project_assignment_id=-1
AND pbl.resource_assignment_id=pra.resource_assignment_id
ORDER BY pbl.start_date,pbl.end_date,pbl.txn_currency_code;
p_delete_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type ,
x_task_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_rlm_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_cbs_element_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- cbs enhancement changes bug#16445106
x_ra_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_txn_currency_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_15_tbl_type , --File.Sql.39 bug 4440895
x_planning_start_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_planning_end_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_total_qty_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_total_raw_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_total_burdened_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_total_revenue_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_raw_cost_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_burdened_cost_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_bill_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_line_start_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_line_end_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_proj_cost_rate_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_proj_cost_rate_date_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_proj_cost_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_proj_cost_rate_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_proj_rev_rate_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_proj_rev_rate_date_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_proj_rev_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_proj_rev_rate_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_pfunc_cost_rate_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_pfunc_cost_rate_date_typ_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_pfunc_cost_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_pfunc_cost_rate_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_pfunc_rev_rate_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_pfunc_rev_rate_date_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_pfunc_rev_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_pfunc_rev_rate_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_delete_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
x_spread_curve_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_mfc_cost_type_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_etc_method_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_sp_fixed_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type, --File.Sql.39 bug 4440895
x_res_class_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type , --File.Sql.39 bug 4440895
x_rate_based_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type , --File.Sql.39 bug 4440895
x_rbs_elem_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
x_change_reason_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type, --File.Sql.39 bug 4440895
x_description_tbl OUT NOCOPY SYSTEM.pa_varchar2_2000_tbl_type, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
-- variables used for debugging
l_module_name VARCHAR2(100) := 'pa_fp_webadi_pkg.prepare_pbl_input';
p_planning_start_date_tbl.COUNT <> p_delete_flag_tbl.COUNT OR
p_planning_start_date_tbl.COUNT <> p_ra_id_tbl.COUNT OR
p_planning_start_date_tbl.COUNT <> p_res_class_code_tbl.COUNT OR
p_planning_start_date_tbl.COUNT <> p_rate_based_flag_tbl.COUNT OR
p_planning_start_date_tbl.COUNT <> p_rbs_elem_id_tbl.COUNT OR
p_planning_start_date_tbl.COUNT <> p_cbs_elem_id_tbl.COUNT THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage := ' p_planning_start_date_tbl.COUNT '|| p_planning_start_date_tbl.COUNT;
pa_debug.g_err_stage := ' p_delete_flag_tbl.COUNT '|| p_delete_flag_tbl.COUNT;
x_delete_flag_tbl := SYSTEM.pa_varchar2_1_tbl_type();
SELECT gl.start_date
INTO l_prd_mask_st_date_tbl(1)
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE pim.org_id = p_version_info_rec.x_org_id
AND gsb.set_of_books_id = pim.set_of_books_id
AND gl.period_set_name= DECODE(p_version_info_rec.x_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(p_version_info_rec.x_time_phased_code,
'P',pim.pa_period_type,
'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND gl.start_date <= p_planning_start_date_tbl(l_bl_index)
AND gl.end_date >=p_planning_start_date_tbl(l_bl_index);
SELECT gl.end_date
INTO l_prd_mask_end_date_tbl(l_prd_mask_end_date_tbl.COUNT)
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE pim.org_id = p_version_info_rec.x_org_id
AND gsb.set_of_books_id = pim.set_of_books_id
AND gl.period_set_name= DECODE(p_version_info_rec.x_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(p_version_info_rec.x_time_phased_code,
'P',pim.pa_period_type,
'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND gl.start_date <= p_planning_end_date_tbl(l_bl_index)
AND gl.end_date >=p_planning_end_date_tbl(l_bl_index);
x_delete_flag_tbl.extend(l_num_of_prds_for_plan_txn);
x_delete_flag_tbl(l_tmp_index) :='N';
IF l_curr_rec.delete_flag ='Y' THEN
l_amount := l_g_miss_num;
IF nvl(l_curr_rec.delete_flag,'N') <> 'Y' AND
l_plan_txn_attrs_copied_flag ='N' THEN
FOR kk IN l_st_index_in_prd_mask..l_end_index_in_prd_mask LOOP
l_tmp_index := l_prev_pbl_tbl_count + kk - l_st_index_in_prd_mask + 1;
END LOOP;--IF nvl(l_curr_rec.delete_flag,'N') <> 'Y' AND
IF l_curr_rec.delete_flag='N' THEN
x_task_id_tbl.extend(1);
x_delete_flag_tbl.extend(1);
x_delete_flag_tbl(x_delete_flag_tbl.COUNT) :='N';
x_delete_flag_tbl.EXTEND(1);
x_delete_flag_tbl(kk) := p_delete_flag_tbl(kk);
p_delete_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type(),
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
-- variables used for debugging
l_module_name VARCHAR2(100) := 'pa_fp_webadi_pkg.process_budget_lines';
l_upd_delete_flag_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
p_task_id_tbl.COUNT <> p_delete_flag_tbl.COUNT OR
p_task_id_tbl.COUNT <> p_cbs_element_id_tbl.COUNT -- cbs enhancement changes bug#16445106
THEN
--log1(' 1 '||p_rlm_id_tbl.COUNT);
IF Nvl(p_delete_flag_tbl(i), 'N') <> 'Y' THEN
l_ra_id_tbl.extend(1);
ELSIF Nvl(p_delete_flag_tbl(i), 'N') = 'Y' THEN
-- if the delete flag is Y then collecting them separately
l_df_ra_id_tbl.EXTEND(1);
END IF; -- delete_flag
SELECT pev.element_version_id
INTO l_new_elem_ver_id
FROM pa_proj_element_versions pev
WHERE pev.proj_element_id = l_task_id_tbl(i)
AND pev.parent_structure_version_id = p_version_info_rec.x_project_structure_version_id;
SELECT pa_resource_assignments_s.nextval
INTO l_curr_ra_id_seq
FROM DUAL
WHERE 1 = 1;
SELECT pra.resource_assignment_id,
pra.rbs_element_id,
pra.resource_class_code,
pra.rate_based_flag
INTO l_new_ra_id,
l_new_res_asg_rbs_elem_id,
l_new_res_asg_res_class_code,
l_new_res_asg_rate_based_flag
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = p_budget_version_id
AND pra.project_id = p_version_info_rec.x_project_id
AND pra.task_id = l_task_id_tbl(i)
AND pra.resource_list_member_id = l_rlm_id_tbl(i)
AND pra.project_assignment_id = -1;
pa_debug.g_err_stage := 'Deleting from pa_budget_lines for the records with delete_flag = Y';
for which delete flag is set as 'Y' in Excel will be populated in pa_fp_rollup_tmp as
deletion of those planning transactions are taken care by delete_planning_transaction api.
This is done as part of merging the MRUP3 changes done in 11i into R12.
*/
/* commenting for bug 10160317 ..
FOR i IN 1..l_df_ra_id_tbl.COUNT LOOP
l_bl_del_flag_ra_id_tbl.extend();
SELECT
pbl.resource_assignment_id,
pbl.start_date,
pbl.end_date,
pbl.txn_currency_code,
pbl.txn_raw_cost,
pbl.txn_burdened_cost,
pbl.txn_revenue,
pbl.projfunc_currency_code,
pbl.projfunc_cost_rate_type,
pbl.projfunc_cost_rate_date_type,
pbl.projfunc_cost_exchange_rate,
pbl.projfunc_cost_rate_date,
pbl.projfunc_rev_rate_type,
pbl.projfunc_rev_rate_date_type,
pbl.projfunc_rev_exchange_rate,
pbl.projfunc_rev_rate_date,
pbl.project_currency_code,
pbl.project_cost_rate_type,
pbl.project_cost_rate_date_type,
pbl.project_cost_exchange_rate,
pbl.project_cost_rate_date,
pbl.project_rev_rate_type,
pbl.project_rev_rate_date_type,
pbl.project_rev_exchange_rate,
pbl.project_rev_rate_date,
pbl.budget_line_id,
pbl.period_name,
pbl.project_raw_cost,
pbl.project_burdened_cost,
pbl.project_revenue,
pbl.raw_cost,
pbl.burdened_cost,
pbl.revenue,
pbl.quantity,
pbl.cost_rejection_code,
pbl.burden_rejection_code,
pbl.revenue_rejection_code,
pbl.other_rejection_code,
pbl.pc_cur_conv_rejection_code,
pbl.pfc_cur_conv_rejection_code
INTO
l_bl_del_flag_ra_id_tbl(i),
l_bl_del_flag_st_dt_tbl(i),
l_bl_del_flag_en_dt_tbl(i),
l_bl_del_flag_txn_curr_tbl(i),
l_bl_del_flag_txn_rc_tbl(i),
l_bl_del_flag_txn_bc_tbl(i),
l_bl_del_flag_txn_rev_tbl(i),
l_bl_del_flag_pf_curr_tbl(i),
l_bl_del_flag_pf_cr_typ_tbl(i),
l_bl_del_flag_pf_cr_dt_typ_tbl(i),
l_bl_del_flag_pf_cexc_rate_tbl(i),
l_bl_del_flag_pf_cr_date_tbl(i),
l_bl_del_flag_pf_rr_typ_tbl(i),
l_bl_del_flag_pf_rr_dt_typ_tbl(i),
l_bl_del_flag_pf_rexc_rate_tbl(i),
l_bl_del_flag_pf_rr_date_tbl(i),
l_bl_del_flag_pj_curr_tbl(i),
l_bl_del_flag_pj_cr_typ_tbl(i),
l_bl_del_flag_pj_cr_dt_typ_tbl(i),
l_bl_del_flag_pj_cexc_rate_tbl(i),
l_bl_del_flag_pj_cr_date_tbl(i),
l_bl_del_flag_pj_rr_typ_tbl(i),
l_bl_del_flag_pj_rr_dt_typ_tbl(i),
l_bl_del_flag_pj_rexc_rate_tbl(i),
l_bl_del_flag_pj_rr_date_tbl(i),
l_bl_del_flag_bl_id_tbl(i),
l_bl_del_flag_per_name_tbl(i),
l_bl_del_flag_pj_raw_cost_tbl(i),
l_bl_del_flag_pj_burd_cost_tbl(i),
l_bl_del_flag_pj_rev_tbl(i),
l_bl_del_flag_raw_cost_tbl(i),
l_bl_del_flag_burd_cost_tbl(i),
l_bl_del_flag_rev_tbl(i),
l_bl_del_flag_qty_tbl(i),
l_bl_del_flag_c_rej_code_tbl(i),
l_bl_del_flag_b_rej_code_tbl(i),
l_bl_del_flag_r_rej_code_tbl(i),
l_bl_del_flag_o_rej_code_tbl(i),
l_bl_del_fg_pc_cnv_rej_cd_tbl(i),
l_bl_del_fg_pf_cnv_rej_cd_tbl(i)
FROM pa_budget_lines pbl
WHERE pbl.resource_assignment_id = l_df_ra_id_tbl(i)
AND pbl.txn_currency_code = l_df_txn_currency_code_tbl(i);
/*Bug 5144013: Calling delete_planning_transactions api to delete the planning transaction
from pa_budget_lines and from new entity when delete flag is set as 'Y' in Excel for a
planning transaction. This is done as part of merging the MRUP3 changes done in 11i into R12.
*/
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage := 'Before Calling pa_fp_planning_transaction_pub.delete_planning_transactions';
pa_fp_planning_transaction_pub.delete_planning_transactions(
p_context => 'BUDGET'
,p_task_or_res => 'ASSIGNMENT'
,p_resource_assignment_tbl => l_df_ra_id_tbl
,p_currency_code_tbl => l_df_txn_currency_code_tbl
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
pa_debug.g_err_stage:='Called API pa_fp_planning_transactions_pub.delete_planning_transactions returned error';
pa_debug.g_err_stage := 'After Calling pa_fp_planning_transaction_pub.delete_planning_transactions';
pa_debug.g_err_stage := l_bl_del_flag_ra_id_tbl.COUNT || ' records deleted from pa_budget_lines';
pa_debug.g_err_stage := 'Inserting these records into pa_fp_rollup_tmp';
SELECT 'Y'
INTO l_bls_proccessed_flag
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pa_fp_rollup_tmp);
pa_debug.g_err_stage := l_bl_del_flag_ra_id_tbl.COUNT || ' records deleted from pa_budget_lines';
pa_debug.g_err_stage := 'Inserting these records into pa_fp_rollup_tmp';
INSERT INTO
pa_fp_rollup_tmp
(resource_assignment_id,
start_date,
end_date,
txn_currency_code,
txn_raw_cost,
txn_burdened_cost,
txn_revenue,
projfunc_currency_code,
projfunc_cost_rate_type,
projfunc_cost_rate_date_type,
projfunc_cost_exchange_rate,
projfunc_cost_rate_date,
projfunc_rev_rate_type,
projfunc_rev_rate_date_type,
projfunc_rev_exchange_rate,
projfunc_rev_rate_date,
project_currency_code,
project_cost_rate_type,
project_cost_rate_date_type,
project_cost_exchange_rate,
project_cost_rate_date,
project_rev_rate_type,
project_rev_rate_date_type,
project_rev_exchange_rate,
project_rev_rate_date,
budget_line_id,
delete_flag,
period_name,
project_raw_cost,
project_burdened_cost,
project_revenue,
projfunc_raw_cost,
projfunc_burdened_cost,
projfunc_revenue,
quantity,
cost_rejection_code,
burden_rejection_code,
revenue_rejection_code,
pc_cur_conv_rejection_code,
pfc_cur_conv_rejection_code,
system_reference4) -- for other_rejection_code
VALUES
(l_bl_del_flag_ra_id_tbl(i),
l_bl_del_flag_st_dt_tbl(i),
l_bl_del_flag_en_dt_tbl(i),
l_bl_del_flag_txn_curr_tbl(i),
l_bl_del_flag_txn_rc_tbl(i),
l_bl_del_flag_txn_bc_tbl(i),
l_bl_del_flag_txn_rev_tbl(i),
l_bl_del_flag_pf_curr_tbl(i),
l_bl_del_flag_pf_cr_typ_tbl(i),
l_bl_del_flag_pf_cr_dt_typ_tbl(i),
l_bl_del_flag_pf_cexc_rate_tbl(i),
l_bl_del_flag_pf_cr_date_tbl(i),
l_bl_del_flag_pf_rr_typ_tbl(i),
l_bl_del_flag_pf_rr_dt_typ_tbl(i),
l_bl_del_flag_pf_rexc_rate_tbl(i),
l_bl_del_flag_pf_rr_date_tbl(i),
l_bl_del_flag_pj_curr_tbl(i),
l_bl_del_flag_pj_cr_typ_tbl(i),
l_bl_del_flag_pj_cr_dt_typ_tbl(i),
l_bl_del_flag_pj_cexc_rate_tbl(i),
l_bl_del_flag_pj_cr_date_tbl(i),
l_bl_del_flag_pj_rr_typ_tbl(i),
l_bl_del_flag_pj_rr_dt_typ_tbl(i),
l_bl_del_flag_pj_rexc_rate_tbl(i),
l_bl_del_flag_pj_rr_date_tbl(i),
l_bl_del_flag_bl_id_tbl(i),
'Y', -- delete_flag
l_bl_del_flag_per_name_tbl(i),
l_bl_del_flag_pj_raw_cost_tbl(i),
l_bl_del_flag_pj_burd_cost_tbl(i),
l_bl_del_flag_pj_rev_tbl(i),
l_bl_del_flag_raw_cost_tbl(i),
l_bl_del_flag_burd_cost_tbl(i),
l_bl_del_flag_rev_tbl(i),
l_bl_del_flag_qty_tbl(i),
l_bl_del_flag_c_rej_code_tbl(i),
l_bl_del_flag_b_rej_code_tbl(i),
l_bl_del_flag_r_rej_code_tbl(i),
l_bl_del_fg_pc_cnv_rej_cd_tbl(i),
l_bl_del_fg_pf_cnv_rej_cd_tbl(i),
l_bl_del_flag_o_rej_code_tbl(i));
pa_debug.g_err_stage := 'records inserted into pa_fp_rollup_tmp';
pa_debug.g_err_stage := 'No records deleted from pa_budget_lines';
END IF; -- records with delete_flag
l_df_task_id_tbl.DELETE;
l_df_rlm_id_tbl.DELETE;
l_df_cbs_element_id_tbl.DELETE;-- cbs enhancement changes bug#16445106
l_df_spread_curve_id_tbl.DELETE;
l_df_mfc_cost_type_id_tbl.DELETE;
l_df_etc_method_code.DELETE;
l_df_sp_fixed_date_tbl.DELETE;
l_df_txn_currency_code_tbl.DELETE;
l_df_planning_start_date_tbl.DELETE;
l_df_planning_end_date_tbl.DELETE;
l_df_total_qty_tbl.DELETE;
l_df_total_raw_cost_tbl.DELETE;
l_df_total_burdened_cost_tbl.DELETE;
l_df_total_revenue_tbl.DELETE;
l_df_raw_cost_rate_tbl.DELETE;
l_df_burdened_cost_rate_tbl.DELETE;
l_df_bill_rate_tbl.DELETE;
l_df_line_start_date_tbl.DELETE;
l_df_line_end_date_tbl.DELETE;
l_df_pj_cost_rate_typ_tbl.DELETE;
l_df_pj_cost_rate_date_typ_tbl.DELETE;
l_df_pj_cost_rate_tbl.DELETE;
l_df_pj_cost_rate_date_tbl.DELETE;
l_df_pj_rev_rate_typ_tbl.DELETE;
l_df_pj_rev_rate_date_typ_tbl.DELETE;
l_df_pj_rev_rate_tbl.DELETE;
l_df_pj_rev_rate_date_tbl.DELETE;
l_df_pf_cost_rate_typ_tbl.DELETE;
l_df_pf_cost_rate_date_typ_tbl.DELETE;
l_df_pf_cost_rate_tbl.DELETE;
l_df_pf_cost_rate_date_tbl.DELETE;
l_df_pf_rev_rate_typ_tbl.DELETE;
l_df_pf_rev_rate_date_typ_tbl.DELETE;
l_df_pf_rev_rate_tbl.DELETE;
l_df_pf_rev_rate_date_tbl.DELETE;
l_df_change_reason_code_tbl.DELETE;
l_df_description_tbl.DELETE;
l_bl_del_flag_st_dt_tbl.DELETE;
l_bl_del_flag_en_dt_tbl.DELETE;
l_bl_del_flag_txn_curr_tbl.DELETE;
l_bl_del_flag_txn_rc_tbl.DELETE;
l_bl_del_flag_txn_bc_tbl.DELETE;
l_bl_del_flag_txn_rev_tbl.DELETE;
l_bl_del_flag_pf_curr_tbl.DELETE;
l_bl_del_flag_pf_cr_typ_tbl.DELETE;
l_bl_del_flag_pf_cr_dt_typ_tbl.DELETE;
l_bl_del_flag_pf_cexc_rate_tbl.DELETE;
l_bl_del_flag_pf_cr_date_tbl.DELETE;
l_bl_del_flag_pf_rr_typ_tbl.DELETE;
l_bl_del_flag_pf_rr_dt_typ_tbl.DELETE;
l_bl_del_flag_pf_rexc_rate_tbl.DELETE;
l_bl_del_flag_pf_rr_date_tbl.DELETE;
l_bl_del_flag_pj_curr_tbl.DELETE;
l_bl_del_flag_pj_cr_typ_tbl.DELETE;
l_bl_del_flag_pj_cr_dt_typ_tbl.DELETE;
l_bl_del_flag_pj_cexc_rate_tbl.DELETE;
l_bl_del_flag_pj_cr_date_tbl.DELETE;
l_bl_del_flag_pj_rr_typ_tbl.DELETE;
l_bl_del_flag_pj_rr_dt_typ_tbl.DELETE;
l_bl_del_flag_pj_rexc_rate_tbl.DELETE;
l_bl_del_flag_pj_rr_date_tbl.DELETE;
l_bl_del_flag_bl_id_tbl.DELETE;
l_bl_del_flag_per_name_tbl.DELETE;
l_bl_del_flag_pj_raw_cost_tbl.DELETE;
l_bl_del_flag_pj_burd_cost_tbl.DELETE;
l_bl_del_flag_pj_rev_tbl.DELETE;
l_bl_del_flag_raw_cost_tbl.DELETE;
l_bl_del_flag_burd_cost_tbl.DELETE;
l_bl_del_flag_rev_tbl.DELETE;
l_bl_del_flag_qty_tbl.DELETE;
l_bl_del_flag_c_rej_code_tbl.DELETE;
l_bl_del_flag_b_rej_code_tbl.DELETE;
l_bl_del_flag_r_rej_code_tbl.DELETE;
l_bl_del_flag_o_rej_code_tbl.DELETE;
l_bl_del_fg_pc_cnv_rej_cd_tbl.DELETE;
l_bl_del_fg_pf_cnv_rej_cd_tbl.DELETE;
UPDATE pa_fp_rollup_tmp pbl
SET pbl.projfunc_cost_rate_type = DECODE (l_pfunc_cost_rate_type_tbl(i), NULL, pbl.projfunc_cost_rate_type,
DECODE(l_pfunc_cost_rate_type_tbl(i), l_g_miss_char, NULL, l_pfunc_cost_rate_type_tbl(i))),
pbl.projfunc_cost_rate_date_type = DECODE (l_pfunc_cost_rate_date_typ_tbl(i), NULL, pbl.projfunc_cost_rate_date_type,
DECODE(l_pfunc_cost_rate_date_typ_tbl(i), l_g_miss_char, NULL, l_pfunc_cost_rate_date_typ_tbl(i))),
pbl.projfunc_cost_exchange_rate = DECODE (l_pfunc_cost_rate_tbl(i), NULL, pbl.projfunc_cost_exchange_rate,
DECODE(l_pfunc_cost_rate_tbl(i), l_g_miss_num, NULL, l_pfunc_cost_rate_tbl(i))),
pbl.projfunc_cost_rate_date = DECODE (l_pfunc_cost_rate_date_tbl(i), NULL, pbl.projfunc_cost_rate_date,
DECODE(l_pfunc_cost_rate_date_tbl(i), l_g_miss_date, NULL, l_pfunc_cost_rate_date_tbl(i))),
pbl.projfunc_rev_rate_type = DECODE (l_pfunc_rev_rate_type_tbl(i), NULL, pbl.projfunc_rev_rate_type,
DECODE(l_pfunc_rev_rate_type_tbl(i), l_g_miss_char, NULL, l_pfunc_rev_rate_type_tbl(i))),
pbl.projfunc_rev_rate_date_type = DECODE (l_pfunc_rev_rate_date_type_tbl(i), NULL, pbl.projfunc_rev_rate_date_type,
DECODE(l_pfunc_rev_rate_date_type_tbl(i), l_g_miss_char, NULL, l_pfunc_rev_rate_date_type_tbl(i))),
pbl.projfunc_rev_exchange_rate = DECODE (l_pfunc_rev_rate_tbl(i), NULL, pbl.projfunc_rev_exchange_rate,
DECODE(l_pfunc_rev_rate_tbl(i), l_g_miss_num, NULL, l_pfunc_rev_rate_tbl(i))),
pbl.projfunc_rev_rate_date = DECODE (l_pfunc_rev_rate_date_tbl(i), NULL, pbl.projfunc_rev_rate_date,
DECODE(l_pfunc_rev_rate_date_tbl(i), l_g_miss_date, NULL, l_pfunc_rev_rate_date_tbl(i))),
pbl.project_cost_rate_type = DECODE (l_proj_cost_rate_type_tbl(i), NULL, pbl.project_cost_rate_type,
DECODE(l_proj_cost_rate_type_tbl(i), l_g_miss_char, NULL, l_proj_cost_rate_type_tbl(i))),
pbl.project_cost_rate_date_type = DECODE (l_proj_cost_rate_date_type_tbl(i), NULL, pbl.project_cost_rate_date_type,
DECODE(l_proj_cost_rate_date_type_tbl(i), l_g_miss_char, NULL, l_proj_cost_rate_date_type_tbl(i))),
pbl.project_cost_exchange_rate = DECODE (l_proj_cost_rate_tbl(i), NULL, pbl.project_cost_exchange_rate,
DECODE(l_proj_cost_rate_tbl(i), l_g_miss_num, NULL, l_proj_cost_rate_tbl(i))),
pbl.project_cost_rate_date = DECODE (l_proj_cost_rate_date_tbl(i), NULL, pbl.project_cost_rate_date,
DECODE(l_proj_cost_rate_date_tbl(i), l_g_miss_date, NULL, l_proj_cost_rate_date_tbl(i))),
pbl.project_rev_rate_type = DECODE (l_proj_rev_rate_type_tbl(i), NULL, pbl.project_rev_rate_type,
DECODE(l_proj_rev_rate_type_tbl(i), l_g_miss_char, NULL, l_proj_rev_rate_type_tbl(i))),
pbl.project_rev_rate_date_type = DECODE (l_proj_rev_rate_date_type_tbl(i), NULL, pbl.project_rev_rate_date_type,
DECODE(l_proj_rev_rate_date_type_tbl(i), l_g_miss_char, NULL, l_proj_rev_rate_date_type_tbl(i))),
pbl.project_rev_exchange_rate = DECODE (l_proj_rev_rate_tbl(i), NULL, pbl.project_rev_exchange_rate,
DECODE(l_proj_rev_rate_tbl(i), l_g_miss_num, NULL, l_proj_rev_rate_tbl(i))),
pbl.project_rev_rate_date = DECODE (l_proj_rev_rate_date_tbl(i), NULL, pbl.project_rev_rate_date,
DECODE(l_proj_rev_rate_date_tbl(i), l_g_miss_date, NULL, l_proj_rev_rate_date_tbl(i)))
WHERE pbl.resource_assignment_id = l_ra_id_tbl(i)
AND pbl.txn_currency_code = l_txn_currency_code_tbl(i)
AND pbl.start_date >= l_line_start_date_tbl(i)
AND pbl.end_date <= l_line_end_date_tbl(i);
UPDATE pa_fp_rollup_tmp pbl
SET pbl.change_reason_code = l_change_reason_code_tbl(i),
pbl.description = l_description_tbl(i)
WHERE pbl.resource_assignment_id = l_ra_id_tbl(i)
AND pbl.txn_currency_code = l_txn_currency_code_tbl(i);
UPDATE pa_fp_rollup_tmp pbl
SET pbl.change_reason_code = l_change_reason_code_tbl(i),
pbl.description = DECODE(l_description_tbl(i), NULL, pbl.description,
DECODE(l_description_tbl(i), l_g_miss_char, NULL, l_description_tbl(i))),
pbl.projfunc_cost_rate_type = DECODE (l_pfunc_cost_rate_type_tbl(i), NULL, pbl.projfunc_cost_rate_type,
DECODE(l_pfunc_cost_rate_type_tbl(i), l_g_miss_char, NULL, l_pfunc_cost_rate_type_tbl(i))),
pbl.projfunc_cost_rate_date_type = DECODE (l_pfunc_cost_rate_date_typ_tbl(i), NULL, pbl.projfunc_cost_rate_date_type,
DECODE(l_pfunc_cost_rate_date_typ_tbl(i), l_g_miss_char, NULL, l_pfunc_cost_rate_date_typ_tbl(i))),
pbl.projfunc_cost_exchange_rate = DECODE (l_pfunc_cost_rate_tbl(i), NULL, pbl.projfunc_cost_exchange_rate,
DECODE(l_pfunc_cost_rate_tbl(i), l_g_miss_num, NULL, l_pfunc_cost_rate_tbl(i))),
pbl.projfunc_cost_rate_date = DECODE (l_pfunc_cost_rate_date_tbl(i), NULL, pbl.projfunc_cost_rate_date,
DECODE(l_pfunc_cost_rate_date_tbl(i), l_g_miss_date, NULL, l_pfunc_cost_rate_date_tbl(i))),
pbl.projfunc_rev_rate_type = DECODE (l_pfunc_rev_rate_type_tbl(i), NULL, pbl.projfunc_rev_rate_type,
DECODE(l_pfunc_rev_rate_type_tbl(i), l_g_miss_char, NULL, l_pfunc_rev_rate_type_tbl(i))),
pbl.projfunc_rev_rate_date_type = DECODE (l_pfunc_rev_rate_date_type_tbl(i), NULL, pbl.projfunc_rev_rate_date_type,
DECODE(l_pfunc_rev_rate_date_type_tbl(i), l_g_miss_char, NULL, l_pfunc_rev_rate_date_type_tbl(i))),
pbl.projfunc_rev_exchange_rate = DECODE (l_pfunc_rev_rate_tbl(i), NULL, pbl.projfunc_rev_exchange_rate,
DECODE(l_pfunc_rev_rate_tbl(i), l_g_miss_num, NULL, l_pfunc_rev_rate_tbl(i))),
pbl.projfunc_rev_rate_date = DECODE (l_pfunc_rev_rate_date_tbl(i), NULL, pbl.projfunc_rev_rate_date,
DECODE(l_pfunc_rev_rate_date_tbl(i), l_g_miss_date, NULL, l_pfunc_rev_rate_date_tbl(i))),
pbl.project_cost_rate_type = DECODE (l_proj_cost_rate_type_tbl(i), NULL, pbl.project_cost_rate_type,
DECODE(l_proj_cost_rate_type_tbl(i), l_g_miss_char, NULL, l_proj_cost_rate_type_tbl(i))),
pbl.project_cost_rate_date_type = DECODE (l_proj_cost_rate_date_type_tbl(i), NULL, pbl.project_cost_rate_date_type,
DECODE(l_proj_cost_rate_date_type_tbl(i), l_g_miss_char, NULL, l_proj_cost_rate_date_type_tbl(i))),
pbl.project_cost_exchange_rate = DECODE (l_proj_cost_rate_tbl(i), NULL, pbl.project_cost_exchange_rate,
DECODE(l_proj_cost_rate_tbl(i), l_g_miss_num, NULL, l_proj_cost_rate_tbl(i))),
pbl.project_cost_rate_date = DECODE (l_proj_cost_rate_date_tbl(i), NULL, pbl.project_cost_rate_date,
DECODE(l_proj_cost_rate_date_tbl(i), l_g_miss_date, NULL, l_proj_cost_rate_date_tbl(i))),
pbl.project_rev_rate_type = DECODE (l_proj_rev_rate_type_tbl(i), NULL, pbl.project_rev_rate_type,
DECODE(l_proj_rev_rate_type_tbl(i), l_g_miss_char, NULL, l_proj_rev_rate_type_tbl(i))),
pbl.project_rev_rate_date_type = DECODE (l_proj_rev_rate_date_type_tbl(i), NULL, pbl.project_rev_rate_date_type,
DECODE(l_proj_rev_rate_date_type_tbl(i), l_g_miss_char, NULL, l_proj_rev_rate_date_type_tbl(i))),
pbl.project_rev_exchange_rate = DECODE (l_proj_rev_rate_tbl(i), NULL, pbl.project_rev_exchange_rate,
DECODE(l_proj_rev_rate_tbl(i), l_g_miss_num, NULL, l_proj_rev_rate_tbl(i))),
pbl.project_rev_rate_date = DECODE (l_proj_rev_rate_date_tbl(i), NULL, pbl.project_rev_rate_date,
DECODE(l_proj_rev_rate_date_tbl(i), l_g_miss_date, NULL, l_proj_rev_rate_date_tbl(i)))
WHERE pbl.resource_assignment_id = l_ra_id_tbl(i)
AND pbl.txn_currency_code = l_txn_currency_code_tbl(i);
END IF; -- end of update
pa_debug.g_err_stage:='pa_budget_lines updated with change reason code, desc and conv attr';
pa_debug.g_err_stage := 'About to select data for updating the budget lines '||l_bls_proccessed_flag;
SELECT rlp.resource_assignment_id,
max(rlp.period_name),
rlp.start_date,
max(rlp.end_date),
rlp.txn_currency_code,
max(rlp.budget_line_id),
sum(rlp.txn_raw_cost),
sum(rlp.txn_burdened_cost),
sum(rlp.txn_revenue),
sum(rlp.project_raw_cost),
sum(rlp.project_burdened_cost),
sum(rlp.project_revenue),
sum(rlp.projfunc_raw_cost),
sum(rlp.projfunc_burdened_cost),
sum(rlp.projfunc_revenue),
sum(rlp.init_quantity),
sum(rlp.init_raw_cost),
sum(rlp.init_burdened_cost),
sum(rlp.init_revenue),
sum(rlp.project_init_raw_cost),
sum(rlp.project_init_burdened_cost),
sum(rlp.project_init_revenue),
sum(rlp.txn_init_raw_cost),
sum(rlp.txn_init_burdened_cost),
sum(rlp.txn_init_revenue),
max(rlp.cost_rejection_code),
max(rlp.revenue_rejection_code),
max(rlp.burden_rejection_code),
max(rlp.system_reference4), -- for other_rejection_code
max(rlp.pc_cur_conv_rejection_code),
max(rlp.pfc_cur_conv_rejection_code),
max(rlp.delete_flag),
sum(rlp.quantity),
max(rlp.project_cost_rate_type),
avg(rlp.project_cost_exchange_rate),
max(rlp.project_cost_rate_date_type),
max(rlp.project_cost_rate_date),
max(rlp.project_rev_rate_type),
avg(rlp.project_rev_exchange_rate),
max(rlp.project_rev_rate_date_type),
max(rlp.project_rev_rate_date),
max(rlp.projfunc_cost_rate_type),
avg(rlp.projfunc_cost_exchange_rate),
max(rlp.projfunc_cost_rate_date_type),
max(rlp.projfunc_cost_rate_date),
max(rlp.projfunc_rev_rate_type),
avg(rlp.projfunc_rev_exchange_rate),
max(rlp.projfunc_rev_rate_date_type),
max(rlp.projfunc_rev_rate_date),
count(*),
max(change_reason_code),
max(description)
BULK COLLECT INTO
l_pji_res_ass_id_tbl,
l_pji_period_name_tbl,
l_pji_start_date_tbl,
l_pji_end_date_tbl,
l_pji_txn_curr_code_tbl,
l_upd_budget_line_id_tbl,
l_pji_txn_raw_cost_tbl,
l_pji_txn_burd_cost_tbl,
l_pji_txn_revenue_tbl,
l_pji_project_raw_cost_tbl,
l_pji_project_burd_cost_tbl,
l_pji_project_revenue_tbl,
l_pji_raw_cost_tbl,
l_pji_burd_cost_tbl,
l_pji_revenue_tbl,
l_upd_init_quantity_tbl,
l_upd_init_raw_cost_tbl,
l_upd_init_burdened_cost_tbl,
l_upd_init_revenue_tbl,
l_upd_proj_init_raw_cost_tbl,
l_upd_proj_init_burd_cost_tbl,
l_upd_proj_init_revenue_tbl,
l_upd_txn_init_raw_cost_tbl,
l_upd_txn_init_burd_cost_tbl,
l_upd_txn_init_revenue_tbl,
l_pji_cost_rej_code_tbl,
l_pji_revenue_rej_code_tbl,
l_pji_burden_rej_code_tbl,
l_pji_other_rej_code,
l_pji_pc_cur_conv_rej_code_tbl,
l_pji_pf_cur_conv_rej_code_tbl,
l_upd_delete_flag_tbl,
l_pji_quantity_tbl,
l_upd_pj_cost_rate_typ_tbl,
l_upd_pj_cost_exc_rate_tbl,
l_upd_pj_cost_rate_dt_typ_tbl,
l_upd_pj_cost_rate_date_tbl,
l_upd_pj_rev_rate_typ_tbl,
l_upd_pj_rev_exc_rate_tbl,
l_upd_pj_rev_rate_dt_typ_tbl,
l_upd_pj_rev_rate_date_tbl,
l_upd_pf_cost_rate_typ_tbl,
l_upd_pf_cost_exc_rate_tbl,
l_upd_pf_cost_rate_dt_typ_tbl,
l_upd_pf_cost_rate_date_tbl,
l_upd_pf_rev_rate_typ_tbl,
l_upd_pf_rev_exc_rate_tbl,
l_upd_pf_rev_rate_dt_typ_tbl,
l_upd_pf_rev_rate_date_tbl,
l_bl_count_tbl,
l_chg_reason_code_tbl,
l_desc_tbl
FROM pa_fp_rollup_tmp rlp
WHERE delete_flag IS NULL OR delete_flag <> 'Y'
GROUP BY rlp.resource_assignment_id,rlp.txn_currency_code,rlp.start_date;
l_upd_delete_flag_tbl.extend(1);
l_upd_delete_flag_tbl(l_upd_delete_flag_tbl.count) := null;
UPDATE pa_budget_lines pbl
SET pbl.project_raw_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_raw_cost,'N',l_pji_project_raw_cost_tbl(i)),
pbl.project_burdened_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_burdened_cost,'N',l_pji_project_burd_cost_tbl(i)),
pbl.project_revenue = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_revenue,'N',l_pji_project_revenue_tbl(i)),
pbl.raw_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.raw_cost,'N',l_pji_raw_cost_tbl(i)),
pbl.burdened_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.burdened_cost,'N',l_pji_burd_cost_tbl(i)),
pbl.revenue = decode(l_extra_bl_flag_tbl(i),'Y',pbl.revenue,'N',l_pji_revenue_tbl(i)),
pbl.quantity = decode(l_extra_bl_flag_tbl(i),'Y',pbl.quantity,'N',l_pji_quantity_tbl(i)),
pbl.pc_cur_conv_rejection_code = decode(l_extra_bl_flag_tbl(i),'Y',pbl.pc_cur_conv_rejection_code,'N',l_pji_pc_cur_conv_rej_code_tbl(i)),
pbl.pfc_cur_conv_rejection_code = decode(l_extra_bl_flag_tbl(i),'Y',pbl.pfc_cur_conv_rejection_code,'N',l_pji_pf_cur_conv_rej_code_tbl(i)),
pbl.init_quantity = decode(l_extra_bl_flag_tbl(i),'Y',pbl.init_quantity,'N',l_upd_init_quantity_tbl(i)),
pbl.init_raw_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.init_raw_cost,'N',l_upd_init_raw_cost_tbl(i)),
pbl.init_burdened_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.init_burdened_cost,'N',l_upd_init_burdened_cost_tbl(i)),
pbl.init_revenue = decode(l_extra_bl_flag_tbl(i),'Y',pbl.init_revenue,'N',l_upd_init_revenue_tbl(i)),
pbl.project_init_raw_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_init_raw_cost,'N',l_upd_proj_init_raw_cost_tbl(i)),
pbl.project_init_burdened_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_init_burdened_cost,'N',l_upd_proj_init_burd_cost_tbl(i)),
pbl.project_init_revenue = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_init_revenue,'N',l_upd_proj_init_revenue_tbl(i)),
pbl.txn_init_raw_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.txn_init_raw_cost,'N',l_upd_txn_init_raw_cost_tbl(i)),
pbl.txn_init_burdened_cost = decode(l_extra_bl_flag_tbl(i),'Y',pbl.txn_init_burdened_cost,'N',l_upd_txn_init_burd_cost_tbl(i)),
pbl.txn_init_revenue = decode(l_extra_bl_flag_tbl(i),'Y',pbl.txn_init_revenue,'N',l_upd_txn_init_revenue_tbl(i)),
pbl.project_cost_rate_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_cost_rate_type,'N',l_upd_pj_cost_rate_typ_tbl(i)),
pbl.project_cost_exchange_rate = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_cost_exchange_rate,'N',l_upd_pj_cost_exc_rate_tbl(i)),
pbl.project_cost_rate_date_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_cost_rate_date_type,'N',l_upd_pj_cost_rate_dt_typ_tbl(i)),
pbl.project_cost_rate_date = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_cost_rate_date,'N',l_upd_pj_cost_rate_date_tbl(i)),
pbl.project_rev_rate_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_rev_rate_type,'N',l_upd_pj_rev_rate_typ_tbl(i)),
pbl.project_rev_exchange_rate = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_rev_exchange_rate,'N',l_upd_pj_rev_exc_rate_tbl(i)),
pbl.project_rev_rate_date_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_rev_rate_date_type,'N',l_upd_pj_rev_rate_dt_typ_tbl(i)),
pbl.project_rev_rate_date = decode(l_extra_bl_flag_tbl(i),'Y',pbl.project_rev_rate_date,'N',l_upd_pj_rev_rate_date_tbl(i)),
pbl.projfunc_cost_rate_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_cost_rate_type,'N',l_upd_pf_cost_rate_typ_tbl(i)),
pbl.projfunc_cost_exchange_rate = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_cost_exchange_rate,'N',l_upd_pf_cost_exc_rate_tbl(i)),
pbl.projfunc_cost_rate_date_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_cost_rate_date_type,'N',l_upd_pf_cost_rate_dt_typ_tbl(i)),
pbl.projfunc_cost_rate_date = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_cost_rate_date,'N',l_upd_pf_cost_rate_date_tbl(i)),
pbl.projfunc_rev_rate_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_rev_rate_type,'N',l_upd_pf_rev_rate_typ_tbl(i)),
pbl.projfunc_rev_exchange_rate = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_rev_exchange_rate,'N',l_upd_pf_rev_exc_rate_tbl(i)),
pbl.projfunc_rev_rate_date_type = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_rev_rate_date_type,'N',l_upd_pf_rev_rate_dt_typ_tbl(i)),
pbl.projfunc_rev_rate_date = decode(l_extra_bl_flag_tbl(i),'Y',pbl.projfunc_rev_rate_date,'N',l_upd_pf_rev_rate_date_tbl(i)),
pbl.change_reason_code = decode(l_extra_bl_flag_tbl(i),'Y',l_ex_chg_rsn_code_tbl(i),'N',DECODE(l_chg_reason_code_tbl(i), NULL, pbl.change_reason_code,
l_fnd_miss_char, NULL,
l_chg_reason_code_tbl(i))), -- Bug 5014538.
pbl.description=decode(l_extra_bl_flag_tbl(i),'Y',l_ex_desc_tbl(i),'N',l_desc_tbl(i)),
pbl.last_updated_by = fnd_global.user_id,
pbl.last_update_date = SYSDATE,
pbl.last_update_login = fnd_global.login_id
WHERE pbl.budget_version_id = p_budget_version_id
AND pbl.resource_assignment_id = l_pji_res_ass_id_tbl(i)
AND pbl.txn_currency_code = l_pji_txn_curr_code_tbl(i)
AND l_bl_count_tbl(i)=1
AND (l_upd_delete_flag_tbl(i) IS NULL OR
l_upd_delete_flag_tbl(i) <> 'Y');
UPDATE pa_budget_lines pbl
SET pbl.project_raw_cost = l_pji_project_raw_cost_tbl(i),
pbl.project_burdened_cost = l_pji_project_burd_cost_tbl(i),
pbl.project_revenue = l_pji_project_revenue_tbl(i),
pbl.raw_cost = l_pji_raw_cost_tbl(i),
pbl.burdened_cost = l_pji_burd_cost_tbl(i),
pbl.revenue = l_pji_revenue_tbl(i),
pbl.quantity = l_pji_quantity_tbl(i),
pbl.pc_cur_conv_rejection_code = l_pji_pc_cur_conv_rej_code_tbl(i),
pbl.pfc_cur_conv_rejection_code = l_pji_pf_cur_conv_rej_code_tbl(i),
pbl.init_quantity = l_upd_init_quantity_tbl(i),
pbl.init_raw_cost = l_upd_init_raw_cost_tbl(i),
pbl.init_burdened_cost = l_upd_init_burdened_cost_tbl(i),
pbl.init_revenue = l_upd_init_revenue_tbl(i),
pbl.project_init_raw_cost = l_upd_proj_init_raw_cost_tbl(i),
pbl.project_init_burdened_cost = l_upd_proj_init_burd_cost_tbl(i),
pbl.project_init_revenue = l_upd_proj_init_revenue_tbl(i),
pbl.txn_init_raw_cost = l_upd_txn_init_raw_cost_tbl(i),
pbl.txn_init_burdened_cost = l_upd_txn_init_burd_cost_tbl(i),
pbl.txn_init_revenue = l_upd_txn_init_revenue_tbl(i),
pbl.project_cost_rate_type = l_upd_pj_cost_rate_typ_tbl(i),
pbl.project_cost_exchange_rate = l_upd_pj_cost_exc_rate_tbl(i),
pbl.project_cost_rate_date_type = l_upd_pj_cost_rate_dt_typ_tbl(i),
pbl.project_cost_rate_date = l_upd_pj_cost_rate_date_tbl(i),
pbl.project_rev_rate_type = l_upd_pj_rev_rate_typ_tbl(i),
pbl.project_rev_exchange_rate = l_upd_pj_rev_exc_rate_tbl(i),
pbl.project_rev_rate_date_type = l_upd_pj_rev_rate_dt_typ_tbl(i),
pbl.project_rev_rate_date = l_upd_pj_rev_rate_date_tbl(i),
pbl.projfunc_cost_rate_type = l_upd_pf_cost_rate_typ_tbl(i),
pbl.projfunc_cost_exchange_rate = l_upd_pf_cost_exc_rate_tbl(i),
pbl.projfunc_cost_rate_date_type = l_upd_pf_cost_rate_dt_typ_tbl(i),
pbl.projfunc_cost_rate_date = l_upd_pf_cost_rate_date_tbl(i),
pbl.projfunc_rev_rate_type = l_upd_pf_rev_rate_typ_tbl(i),
pbl.projfunc_rev_exchange_rate = l_upd_pf_rev_exc_rate_tbl(i),
pbl.projfunc_rev_rate_date_type = l_upd_pf_rev_rate_dt_typ_tbl(i),
pbl.projfunc_rev_rate_date = l_upd_pf_rev_rate_date_tbl(i),
pbl.change_reason_code = DECODE(l_chg_reason_code_tbl(i), NULL, pbl.change_reason_code,
l_fnd_miss_char, NULL,
l_chg_reason_code_tbl(i)), --Bug 5144013.
pbl.description=l_desc_tbl(i),
pbl.last_updated_by = fnd_global.user_id,
pbl.last_update_date = SYSDATE,
pbl.last_update_login = fnd_global.login_id
WHERE pbl.budget_version_id = p_budget_version_id
AND pbl.resource_assignment_id = l_pji_res_ass_id_tbl(i)
AND pbl.txn_currency_code = l_pji_txn_curr_code_tbl(i)
AND pbl.start_date =l_pji_start_date_tbl(i)
AND l_bl_count_tbl(i)=1
AND (l_upd_delete_flag_tbl(i) IS NULL OR
l_upd_delete_flag_tbl(i) <> 'Y');
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_dest_ver_id_tbl,
x_return_status => x_return_status,
x_msg_code => l_error_msg_code);
pa_debug.g_err_stage := 'API PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE returned ERROR 1 '|| l_error_msg_code;
l_pji_res_ass_id_tbl.DELETE;
l_pji_period_name_tbl.DELETE;
l_pji_start_date_tbl.DELETE;
l_pji_end_date_tbl.DELETE;
l_pji_txn_curr_code_tbl.DELETE;
l_pji_txn_raw_cost_tbl.DELETE;
l_pji_txn_burd_cost_tbl.DELETE;
l_pji_txn_revenue_tbl.DELETE;
l_pji_project_raw_cost_tbl.DELETE;
l_pji_project_burd_cost_tbl.DELETE;
l_pji_project_revenue_tbl.DELETE;
l_pji_raw_cost_tbl.DELETE;
l_pji_burd_cost_tbl.DELETE;
l_pji_revenue_tbl.DELETE;
l_pji_cost_rej_code_tbl.DELETE;
l_pji_revenue_rej_code_tbl.DELETE;
l_pji_burden_rej_code_tbl.DELETE;
l_pji_other_rej_code.DELETE;
l_pji_pc_cur_conv_rej_code_tbl.DELETE;
l_pji_pf_cur_conv_rej_code_tbl.DELETE;
l_pji_quantity_tbl.DELETE;
l_upd_pj_cost_rate_typ_tbl.DELETE;
l_upd_pj_cost_exc_rate_tbl.DELETE;
l_upd_pj_cost_rate_dt_typ_tbl.DELETE;
l_upd_pj_cost_rate_date_tbl.DELETE;
l_upd_pj_rev_rate_typ_tbl.DELETE;
l_upd_pj_rev_exc_rate_tbl.DELETE;
l_upd_pj_rev_rate_dt_typ_tbl.DELETE;
l_upd_pj_rev_rate_date_tbl.DELETE;
l_upd_pf_cost_rate_typ_tbl.DELETE;
l_upd_pf_cost_exc_rate_tbl.DELETE;
l_upd_pf_cost_rate_dt_typ_tbl.DELETE;
l_upd_pf_cost_rate_date_tbl.DELETE;
l_upd_pf_rev_rate_typ_tbl.DELETE;
l_upd_pf_rev_exc_rate_tbl.DELETE;
l_upd_pf_rev_rate_dt_typ_tbl.DELETE;
l_upd_pf_rev_rate_date_tbl.DELETE;
l_upd_budget_line_id_tbl.DELETE;
l_upd_init_quantity_tbl.DELETE;
l_upd_init_raw_cost_tbl.DELETE;
l_upd_init_burdened_cost_tbl.DELETE;
l_upd_init_revenue_tbl.DELETE;
l_upd_proj_init_raw_cost_tbl.DELETE;
l_upd_proj_init_burd_cost_tbl.DELETE;
l_upd_proj_init_revenue_tbl.DELETE;
l_upd_txn_init_raw_cost_tbl.DELETE;
l_upd_txn_init_burd_cost_tbl.DELETE;
l_upd_txn_init_revenue_tbl.DELETE;
l_upd_delete_flag_tbl.DELETE;
PROCEDURE update_xl_data_for_new_req
(p_request_id IN pa_budget_versions.request_id%TYPE,
p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
p_plan_processing_code IN pa_budget_versions.plan_processing_code%TYPE,
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
p_null_out_cols IN VARCHAR2)
IS
BEGIN
--Record Version Number is not updated since the web ADI code checks the record Version Number at downloand
--with the record version number at upload and throws an error if they dont match.
UPDATE pa_budget_versions
SET request_id = p_request_id,
plan_processing_code=p_plan_processing_code
WHERE budget_version_id = p_budget_version_id;
UPDATE pa_fp_webadi_upload_inf
SET task_id =DECODE(p_null_out_cols,'Y',NULL,task_id),
resource_list_member_id=DECODE(p_null_out_cols,'Y',NULL,resource_list_member_id),
val_error_flag =DECODE(p_null_out_cols,'Y',NULL,val_error_flag),
val_error_code =DECODE(p_null_out_cols,'Y',NULL,val_error_code),
err_task_name =DECODE(p_null_out_cols,'Y',NULL,err_task_name),
err_task_number =DECODE(p_null_out_cols,'Y',NULL,err_task_number),
err_alias =DECODE(p_null_out_cols,'Y',NULL,err_alias),
err_amount_type_code =DECODE(p_null_out_cols,'Y',NULL,err_amount_type_code),
request_id=p_request_id
WHERE run_id=p_run_id
AND request_id IS NULL;
l_delete_flag_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
l_delete_flag_tbl_1 SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
SELECT glsd.start_date start_date,
gled.end_date end_date
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type
ORDER BY gl.start_date) rn,
gl.start_date start_date,
gl.end_Date end_date,
gl.period_name period_name,
gl.period_set_name period_set_name,
gl.period_type period_type
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE gl.period_set_name = DECODE(c_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(c_time_phased_code,'P',pim.pa_period_type,'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND pim.org_id = c_org_id
AND gsb.set_of_books_id = pim.set_of_books_id) glsd,
(SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type
ORDER BY gl.start_date) rn,
gl.start_date start_date,
gl.end_Date end_date,
gl.period_name period_name,
gl.period_set_name period_set_name,
gl.period_type period_type
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE gl.period_set_name = DECODE(c_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(c_time_phased_code,'P',pim.pa_period_type,'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND pim.org_id = c_org_id
AND gsb.set_of_books_id = pim.set_of_books_id) gled,
(SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type
ORDER BY gl.start_date) rn,
gl.start_date start_date,
gl.end_Date end_date,
gl.period_name period_name,
gl.period_set_name period_set_name,
gl.period_type period_type
FROM gl_periods gl,
pa_implementations_all pim,
gl_sets_of_books gsb
WHERE gl.period_set_name = DECODE(c_time_phased_code,'P',pim.period_set_name,'G',gsb.period_set_name)
AND gl.period_type = DECODE(c_time_phased_code,'P',pim.pa_period_type,'G',gsb.accounted_period_type)
AND gl.adjustment_period_flag='N'
AND pim.org_id = c_org_id
AND gsb.set_of_books_id = pim.set_of_books_id)glcp,
pa_period_mask_details pmd
WHERE pmd.period_mask_id = c_prd_mask_id
AND glcp.period_name = c_current_planning_period
AND glsd.rn = pmd.from_anchor_start + glcp.rn
AND gled.rn = pmd.from_anchor_end + glcp.rn
AND pmd.from_anchor_position NOT IN (99999,-99999)
ORDER BY pmd.from_anchor_position;
SELECT budget_version_id,
record_version_number,
prd_mask_rec_ver_number
INTO l_budget_version_id,
l_rec_version_number,
l_pm_rec_version_number
FROM pa_fp_webadi_upload_inf
WHERE run_id = p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99)
AND rownum = 1;
SELECT 'Y'
INTO is_periodic_setup
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM pa_fp_webadi_upload_inf
WHERE amount_type_name IS NOT NULL
AND run_id = p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
SELECT bv.project_id,
Nvl(pl.org_id, -99),
etc_start_date
INTO l_project_id,
l_org_id,
l_etc_start_date
FROM pa_budget_versions bv,
pa_projects_all pl
WHERE bv.budget_version_id = l_budget_version_id
AND bv.project_id = pl.project_id;
UPDATE pa_fp_webadi_upload_inf
SET val_error_flag = 'Y',
val_error_code = 'PA_FP_WEBADI_VER_MODIFIED',
err_task_name = nvl(task_name,'-98'),
err_task_number = nvl(task_number,'-98'),
err_alias = nvl(resource_alias,'-98'),
err_amount_type_code = nvl(amount_type_code,'-98')
WHERE run_id=p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
x_delete_flag_tbl => l_delete_flag_tbl,
x_ra_id_tbl => l_ra_id_tbl,
x_res_class_code_tbl => l_res_class_code_tbl,
x_rate_based_flag_tbl => l_rate_based_flag_tbl,
x_rbs_elem_id_tbl => l_rbs_elem_id_tbl,
x_cbs_elem_id_tbl => l_cbs_elem_id_tbl, -- cbs enhancement changes bug#16445106
x_amt_type_tbl => l_amt_type_tbl,
x_first_pd_bf_pm_en_dt => l_first_pd_bf_pm_en_dt,
x_last_pd_af_pm_st_dt => l_last_pd_af_pm_st_dt,
x_inf_tbl_rec_tbl => l_inf_tbl_rec_tbl,
x_num_of_rec_processed => l_record_counter,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
x_delete_flag_tbl => l_delete_flag_tbl,
x_ra_id_tbl => l_ra_id_tbl,
x_res_class_code_tbl => l_res_class_code_tbl,
x_rate_based_flag_tbl => l_rate_based_flag_tbl,
x_rbs_elem_id_tbl => l_rbs_elem_id_tbl,
x_cbs_elem_id_tbl => l_cbs_elem_id_tbl, -- cbs enhancement changes bug#16445106
x_amt_type_tbl => l_amt_type_tbl,
x_first_pd_bf_pm_en_dt => l_first_pd_bf_pm_en_dt,
x_last_pd_af_pm_st_dt => l_last_pd_af_pm_st_dt,
x_inf_tbl_rec_tbl => l_inf_tbl_rec_tbl,
x_num_of_rec_processed => l_record_counter,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
,p_delete_flag_tbl => l_delete_flag_tbl
,p_mfc_cost_type_tbl => l_mfc_cost_type_tbl
,p_spread_curve_name_tbl => l_spread_curve_name_tbl
,p_sp_fixed_date_tbl => l_sp_fixed_date_tbl
,p_etc_method_name_tbl => l_etc_method_name_tbl
,p_spread_curve_id_tbl => l_spread_curve_id_tbl_1
,p_amount_type_tbl => l_amt_type_tbl
,px_budget_lines_in => l_budget_line_in_tbl
,x_budget_lines_out => l_budget_line_out_tbl
,x_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl
,x_etc_method_code_tbl => l_etc_method_code_tbl
,x_spread_curve_id_tbl => l_spread_curve_id_tbl
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => x_return_status);
SELECT 'X'
INTO l_error_indicator_flag
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM pa_fp_webadi_upload_inf inf--pa_fp_webadi_upload_inf inf
WHERE run_id = p_run_id
AND Nvl(p_request_id, -99) = Nvl(request_id, -99)
AND (inf.val_error_flag = 'Y'
OR inf.val_error_code IS NOT NULL));
check_and_update_txn_curr_code
(p_budget_line_tbl => l_budget_line_in_tbl,
px_inf_cur_rec_tbl => l_inf_tbl_rec_tbl,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.g_err_stage := 'Call to check_and_update_txn_curr_code returned with error';
SELECT to_number(l_profile_thsld_val)
INTO l_profile_thsld_num
FROM dual;
UPDATE pa_budget_versions
SET plan_processing_code = 'XLUP',
request_id = l_request_id
WHERE budget_version_id = l_budget_version_id;
UPDATE pa_fp_webadi_upload_inf
SET request_id = l_request_id
WHERE run_id = p_run_id
AND request_id IS NULL;
l_delete_flag_tbl_1 := l_delete_flag_tbl;
p_delete_flag_tbl => l_delete_flag_tbl_1,
p_request_id => p_request_id,
x_task_id_tbl => l_task_id_tbl,
x_rlm_id_tbl => l_rlm_id_tbl,
x_ra_id_tbl => l_prc_ra_id_tbl,
x_txn_currency_code_tbl => l_txn_currency_code_tbl,
x_planning_start_date_tbl => l_planning_start_date_tbl,
x_planning_end_date_tbl => l_planning_end_date_tbl,
x_total_qty_tbl => l_total_qty_tbl,
x_total_raw_cost_tbl => l_total_raw_cost_tbl,
x_total_burdened_cost_tbl => l_total_burdened_cost_tbl,
x_total_revenue_tbl => l_total_revenue_tbl,
x_raw_cost_rate_tbl => l_raw_cost_rate_tbl,
x_burdened_cost_rate_tbl => l_burdened_cost_rate_tbl,
x_bill_rate_tbl => l_bill_rate_tbl,
x_line_start_date_tbl => l_line_start_date_tbl,
x_line_end_date_tbl => l_line_end_date_tbl,
x_proj_cost_rate_type_tbl => l_proj_cost_rate_type_tbl,
x_proj_cost_rate_date_type_tbl => l_proj_cost_rate_date_type_tbl,
x_proj_cost_rate_tbl => l_proj_cost_rate_tbl,
x_proj_cost_rate_date_tbl => l_proj_cost_rate_date_tbl,
x_proj_rev_rate_type_tbl => l_proj_rev_rate_type_tbl,
x_proj_rev_rate_date_type_tbl => l_proj_rev_rate_date_type_tbl,
x_proj_rev_rate_tbl => l_proj_rev_rate_tbl,
x_proj_rev_rate_date_tbl => l_proj_rev_rate_date_tbl,
x_pfunc_cost_rate_type_tbl => l_pfunc_cost_rate_type_tbl,
x_pfunc_cost_rate_date_typ_tbl => l_pfunc_cost_rate_date_typ_tbl,
x_pfunc_cost_rate_tbl => l_pfunc_cost_rate_tbl,
x_pfunc_cost_rate_date_tbl => l_pfunc_cost_rate_date_tbl,
x_pfunc_rev_rate_type_tbl => l_pfunc_rev_rate_type_tbl,
x_pfunc_rev_rate_date_type_tbl => l_pfunc_rev_rate_date_type_tbl,
x_pfunc_rev_rate_tbl => l_pfunc_rev_rate_tbl,
x_pfunc_rev_rate_date_tbl => l_pfunc_rev_rate_date_tbl,
x_delete_flag_tbl => l_delete_flag_tbl,
x_spread_curve_id_tbl => l_spread_curve_id_tbl,
x_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl,
x_etc_method_code_tbl => l_etc_method_code_tbl,
x_sp_fixed_date_tbl => l_sp_fixed_date_tbl,
x_res_class_code_tbl => l_prc_res_class_code_tbl,
x_rate_based_flag_tbl => l_prc_rate_based_flag_tbl,
x_rbs_elem_id_tbl => l_prc_rbs_elem_id_tbl,
x_cbs_element_id_tbl => l_prc_cbs_elem_id_tbl,-- cbs enhancement changes bug#16445106
x_change_reason_code_tbl => l_change_reason_code_tbl,
x_description_tbl => l_description_tbl,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_delete_flag_tbl => l_delete_flag_tbl,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_fp_webadi_pkg.DELETE_XFACE
( p_run_id => p_run_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_msg_data --x_msg_data Bug 2764950
) ;
update_xl_data_for_new_req
(p_request_id => l_request_id,
p_run_id => p_run_id,
p_plan_processing_code => 'XLUE',
p_budget_version_id => l_budget_version_id,
p_null_out_cols => 'N');
update_xl_data_for_new_req
(p_request_id => l_request_id,
p_run_id => p_run_id,
p_plan_processing_code => 'XLUE',
p_budget_version_id => l_budget_version_id,
p_null_out_cols => 'N');
SELECT inf.task_number || '/' || inf.task_name task_info,
inf.resource_alias resource_info,
inf.txn_currency_code currency,
plu1.meaning amount_type,
plu2.meaning error
FROM pa_fp_webadi_upload_inf inf,
pa_lookups plu1,
pa_lookups plu2
WHERE inf.run_id = c_run_id
AND Nvl(inf.val_error_flag, 'N') = 'Y'
AND inf.val_error_code IS NOT NULL
AND plu1.lookup_type = DECODE (c_plan_class, 'BUDGET',
DECODE(c_version_type,
'COST', 'PA_FP_XL_COST_BDGT_AMT_TYPES',
'REVENUE', 'PA_FP_XL_REV_BDGT_AMT_TYPES',
'PA_FP_XL_ALL_BDGT_AMT_TYPES'),
'FORECAST', DECODE(c_version_type,
'COST', 'PA_FP_XL_COST_FCST_AMT_TYPES',
'REVENUE', 'PA_FP_XL_REV_FCST_AMT_TYPES',
'PA_FP_XL_ALL_FCST_AMT_TYPES'))
AND (inf.amount_type_code IS NULL OR plu1.lookup_code = Nvl(inf.amount_type_code, '-99'))
AND plu2.lookup_type = 'PA_FP_WEBADI_ERR_1'
AND inf.val_error_code = plu2.lookup_code
AND Nvl(c_request_id, -99) = Nvl(inf.request_id, -99);
SELECT budget_version_id
INTO l_budget_version_id
FROM pa_fp_webadi_upload_inf inf
WHERE inf.run_id = p_run_id
AND Nvl(l_request_id,-99) = Nvl(request_id,-99)
AND rownum=1;
SELECT fin.plan_class_code,
pbv.version_type
INTO l_plan_class_code,
l_version_type
FROM pa_budget_versions pbv,
pa_fin_plan_types_b fin
WHERE pbv.budget_version_id = l_budget_version_id
AND fin.fin_plan_type_id = pbv.fin_plan_type_id;
pa_debug.g_err_stage:='Callng update_xl_data_for_new_req';
update_xl_data_for_new_req
(p_request_id => l_request_id,
p_run_id => p_run_id,
p_plan_processing_code => 'XLUP',
p_budget_version_id => l_budget_version_id,
p_null_out_cols => 'Y');
UPDATE pa_budget_versions
SET plan_processing_code = 'XLUS',
record_version_number = (record_version_number + 1)
WHERE budget_version_id = l_budget_version_id;
update_xl_data_for_new_req
(p_request_id => l_request_id,
p_run_id => p_run_id,
p_plan_processing_code => 'XLUE',
p_budget_version_id => l_budget_version_id,
p_null_out_cols => 'N');
update_xl_data_for_new_req
(p_request_id => l_request_id,
p_run_id => p_run_id,
p_plan_processing_code => 'XLUE',
p_budget_version_id => l_budget_version_id,
p_null_out_cols => 'N');
SELECT pmd.period_mask_id,
pmd.num_of_periods,
pmd.anchor_period_flag,
pmd.from_anchor_start,
pmd.from_anchor_end,
pmd.from_anchor_position
FROM pa_period_mask_details pmd
WHERE pmd.period_mask_id = c_period_mask_id
AND pmd.from_anchor_position not in(-99999,99999)
ORDER BY pmd.from_anchor_position;
SELECT org_id,
period_set_name,
pa_period_type,
set_of_books_id
FROM pa_implementations_all
WHERE org_id = c_org_id;
SELECT ROW_NUMBER() OVER( PARTITION BY gl.period_set_name,gl.period_type ORDER BY gl.start_date ) row_num,
gl.start_date start_date,
gl.end_Date end_date,
gl.period_name period_name,
gl.period_type period_type,
gl.period_set_name period_set_name,
gsb.accounted_period_type accounted_period_type,
c_org_id
FROM gl_periods gl,
gl_sets_of_books gsb
WHERE gl.period_set_name=decode(c_time_phased_code,'P',c_period_set_name,'G',gsb.period_set_name)
AND gsb.set_of_books_id=c_set_of_books_id
AND gl.ADJUSTMENT_PERIOD_FLAG ='N'
AND gl.period_type = decode(c_time_phased_code,'P',c_pa_period_type,
'G',gsb.accounted_period_type);
PROCEDURE insert_periodic_tmp_table
(p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 )
IS
/* Bug 5144013: Changes are made in the cursor to make use of the new entity pa_resource_asgn_curr
which is introduced in MRUP3 of 11i. The changes are done as part of merging the MRUP3 changes
done in 11i into R12.
*/
--This Cursor is used to get Resource Assignment information associated with the budget version.
CURSOR res_ass_cur(c_budget_version_id pa_budget_versions.budget_version_id%TYPE,
c_project_id pa_projects_all.project_id%TYPE,
c_parent_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE,
c_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE)
IS
SELECT pra.resource_assignment_id resource_assignment_id,
nvl(pe.name,ppa.long_name) task_name,
nvl(pe.element_number,ppa.segment1) task_number,
nvl(pev.display_sequence,-1) task_display_sequence,
prlm.alias resource_name,
pra.resource_list_member_id,
uom.meaning unit_of_measure,
pra.spread_curve_id spread_curve_id,
psc.name spread_curve,
pra.planning_start_date,
pra.planning_end_date,
pra.mfc_cost_type_id mfc_cost_type_id,
cct.cost_type mfc_cost_type,
pra.etc_method_code etc_method_code,
etc.meaning etc_method,
pev.proj_element_id project_element_id,
decode(pra.transaction_source_code,NULL,NULL,
(SELECT meaning
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL'
AND LOOKUP_CODE= nvl(pra.transaction_source_code, (
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type='PA_FP_FCST_GEN_SRC_ALL'
AND rownum=1)))) etc_source,
pftc.txn_currency_code txn_currency_code,
pftc.project_cost_exchange_rate project_cost_exchange_rate,
pftc.project_rev_exchange_rate project_rev_exchange_rate,
pftc.projfunc_cost_exchange_rate projfunc_cost_exchange_rate,
pftc.projfunc_rev_exchange_rate projfunc_rev_exchange_rate,
(select COST_CODE from pa_rbs_elements where RBS_ELEMENT_ID = pra.cbs_element_id) cost_code, -- cbs enhancement changes bug#16445106
(select COST_CODE_NAME from pa_rbs_elements where RBS_ELEMENT_ID = pra.cbs_element_id) cost_code_name -- cbs enhancement changes bug#16445106
FROM pa_resource_assignments pra,
pa_resource_asgn_curr prac,
pa_fp_txn_currencies pftc,
pa_proj_elements pe,
pa_proj_element_versions pev,
pa_resource_list_members prlm,
pa_lookups uom,
pa_spread_curves_tl psc,
cst_cost_types cct,
pa_lookups etc,
pa_projects_all ppa
WHERE pra.budget_version_id = c_budget_version_id
AND prac.budget_version_id = pra.budget_version_id
AND prac.resource_assignment_id = pra.resource_assignment_id
AND pftc.fin_plan_version_id = prac.budget_version_id
AND pftc.txn_currency_code = prac.txn_currency_code
AND pftc.proj_fp_options_id = c_proj_fp_options_id
AND pra.resource_list_member_id = prlm.resource_list_member_id
AND decode(pra.task_id,0,pev.proj_element_id,pra.task_id)=pev.proj_element_id
AND decode(pra.task_id,0,pev.parent_structure_version_id,pev.element_version_id)=pev.element_version_id
AND pev.proj_element_id = pe.proj_element_id(+)
AND pe.object_type(+)='PA_TASKS'
AND pev.parent_structure_version_id = c_parent_structure_version_id
AND nvl(pra.spread_curve_id,1) = psc.spread_curve_id
AND etc.lookup_type = 'PA_FP_ETC_METHOD'
AND etc.lookup_code(+) = pra.etc_method_code
AND cct.cost_type_id(+) = pra.mfc_cost_type_id
AND uom.lookup_type = 'UNIT'
AND uom.LOOKUP_CODE = nvl(prlm.UNIT_OF_MEASURE,'HOURS')
AND psc.language = userenv('LANG')
AND ppa.project_id = c_project_id
ORDER BY task_display_sequence;
SELECT amt.amount_type_code,
amt_lu.meaning amount_type_name,
decode(decode(amt.amount_type_code,'TOTAL_REV',l_proj_fp_options_rec.projfunc_rev_rate_type,l_proj_fp_options_rec.projfunc_cost_rate_type),null,null,
(SELECT pctv1.USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES pctv1
WHERE pctv1.CONVERSION_TYPE= nvl(decode(amt.amount_type_code,'TOTAL_REV',l_proj_fp_options_rec.projfunc_rev_rate_type,l_proj_fp_options_rec.projfunc_cost_rate_type),'Corporate'))) projfunc_rate_type,
decode(decode(amt.amount_type_code,'TOTAL_REV',decode(l_proj_fp_options_rec.projfunc_rev_rate_type,'User',null,l_proj_fp_options_rec.projfunc_rev_rate_date_type),
decode(l_proj_fp_options_rec.projfunc_cost_rate_type,'User',null,l_proj_fp_options_rec.projfunc_cost_rate_date_type)),null,null,
(SELECT plk_d1.meaning
FROM pa_lookups plk_d1
WHERE plk_d1.lookup_type='PA_FP_RATE_DATE_TYPE'
AND plk_d1.lookup_code=NVL(decode(amt.amount_type_code,'TOTAL_REV',
decode(l_proj_fp_options_rec.projfunc_rev_rate_type,'User',null,l_proj_fp_options_rec.projfunc_rev_rate_date_type),
decode(l_proj_fp_options_rec.projfunc_cost_rate_type,'User',null,l_proj_fp_options_rec.projfunc_cost_rate_date_type)),'FIXED_DATE'))) projfunc_rate_date_type,
decode(amt.amount_type_code,'TOTAL_REV',decode(l_proj_fp_options_rec.projfunc_rev_rate_date_type,'FIXED_DATE',l_proj_fp_options_rec.projfunc_rev_rate_date,TO_DATE(null)),
decode(l_proj_fp_options_rec.projfunc_cost_rate_date_type,'FIXED_DATE',l_proj_fp_options_rec.projfunc_cost_rate_date,TO_DATE(null))) projfunc_rate_date,
decode(decode(amt.amount_type_code,'TOTAL_REV',l_proj_fp_options_rec.project_rev_rate_type,l_proj_fp_options_rec.project_cost_rate_type),null,null,
(SELECT pctv2.USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES pctv2
WHERE pctv2.CONVERSION_TYPE= nvl(decode(amt.amount_type_code,'TOTAL_REV',l_proj_fp_options_rec.project_rev_rate_type,l_proj_fp_options_rec.project_cost_rate_type),'Corporate'))) project_rate_type,
decode(decode(amt.amount_type_code,'TOTAL_REV',decode(l_proj_fp_options_rec.project_rev_rate_type,'User',null,l_proj_fp_options_rec.PROJECT_REV_RATE_DATE_TYPE),
decode(l_proj_fp_options_rec.project_cost_rate_type,'User',null,l_proj_fp_options_rec.project_cost_rate_date_type)),null,null,
(SELECT plk_d2.meaning
FROM pa_lookups plk_d2
WHERE plk_d2.lookup_type='PA_FP_RATE_DATE_TYPE'
AND plk_d2.lookup_code=NVL(decode(amt.amount_type_code,'TOTAL_REV',
decode(l_proj_fp_options_rec.project_rev_rate_type,'User',null,l_proj_fp_options_rec.project_rev_rate_date_type),
decode(l_proj_fp_options_rec.project_cost_rate_type,'User',null,l_proj_fp_options_rec.project_cost_rate_date_type)),'FIXED_DATE'))) project_rate_date_type,
decode(amt.amount_type_code,'TOTAL_REV',decode(l_proj_fp_options_rec.project_rev_rate_date_type,'FIXED_DATE',l_proj_fp_options_rec.project_rev_rate_date,TO_DATE(null)),
decode(l_proj_fp_options_rec.project_cost_rate_date_type,'FIXED_DATE',l_proj_fp_options_rec.project_cost_rate_date,TO_DATE(null))) project_rate_date
FROM pa_fp_proj_xl_amt_types amt,
pa_lookups amt_lu
WHERE amt.project_id = c_project_id
AND amt.fin_plan_type_id = c_fin_plan_type_id
AND amt.option_type = c_option_type
AND amt_lu.lookup_type = decode(c_plan_class_code,'BUDGET', decode(c_option_type,'COST','PA_FP_XL_COST_BDGT_AMT_TYPES','REVENUE','PA_FP_XL_REV_BDGT_AMT_TYPES','ALL','PA_FP_XL_ALL_BDGT_AMT_TYPES'),
'FORECAST',decode(c_option_type,'COST','PA_FP_XL_COST_FCST_AMT_TYPES','REVENUE','PA_FP_XL_REV_FCST_AMT_TYPES','ALL','PA_FP_XL_ALL_FCST_AMT_TYPES'))
AND amt_lu.lookup_code = amt.amount_type_code
ORDER BY amount_type_name;
SELECT DISTINCT(txn_currency_code)
FROM pa_budget_lines pbl
WHERE pbl.budget_version_id = c_budget_version_id
AND pbl.resource_assignment_id = c_ra_id;
SELECT pftc.txn_currency_code,
pftc.project_cost_exchange_rate,
pftc.project_rev_exchange_rate,
pftc.projfunc_cost_exchange_rate,
pftc.projfunc_rev_exchange_rate
FROM pa_fp_txn_currencies pftc
WHERE pftc.fin_plan_version_id = c_budget_version_id
AND pftc.proj_fp_options_id = c_proj_fp_options_id;
l_module_name VARCHAR2(100) := 'pa_fp_webadi_pkg.insert_periodic_tmp_table';
pa_debug.set_curr_function( p_function => 'INSERT_PERIODIC_TMP_TABLE'
,p_debug_mode => l_debug_mode );
pa_debug.g_err_stage := ':In pa_fp_webadi_pkg.INSERT_PERIODIC_TMP_TABLE' ;
delete from pa_fp_webadi_xface_tmp where budget_version_id = l_budget_version_id;
SELECT ppa.project_id,
ppa.name,
ppa.segment1,
pbv.record_version_number --Bug 7863205
INTO l_project_id,
l_project_name,
l_project_number,
l_rec_version_number --Bug 7863205
FROM pa_budget_versions pbv,
pa_projects_all ppa
WHERE pbv.budget_version_id = l_budget_version_id
AND pbv.project_id = ppa.project_id;
SELECT ci_id,
approved_rev_plan_type_flag
INTO l_ci_id,
l_AR_flag
FROM pa_budget_versions
WHERE budget_version_id = l_budget_version_id;
SELECT fin_plan_preference_code,
projfunc_cost_rate_type,
projfunc_cost_rate_date_type,
projfunc_cost_rate_date,
project_cost_rate_type,
project_cost_rate_date_type,
project_cost_rate_date,
projfunc_rev_rate_type,
projfunc_rev_rate_date_type,
projfunc_rev_rate_date,
project_rev_rate_type,
project_rev_rate_date_type,
project_rev_rate_date
INTO l_proj_fp_options_rec
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_proj_fp_options_id;
SELECT DISTINCT(nvl(pbl.txn_currency_code,DECODE(l_ci_id,
null, DECODE(l_AR_flag,
'Y', l_projfunc_currency_code,
l_project_currency_code),
DECODE(l_version_type,
'ALL', l_agr_curr_code,
'REVENUE', l_agr_curr_code,
DECODE(l_AR_flag,
'Y', l_projfunc_currency_code,
l_project_currency_code))))) as txn_currency_code,
pftc.project_cost_exchange_rate,
pftc.project_rev_exchange_rate,
pftc.projfunc_cost_exchange_rate,
pftc.projfunc_rev_exchange_rate
BULK COLLECT INTO l_txn_curr_tbl
FROM pa_budget_lines pbl,
pa_fp_txn_currencies pftc
WHERE pbl.budget_version_id = l_budget_version_id
AND pftc.fin_plan_version_id = l_budget_version_id
AND pftc.proj_fp_options_id = l_proj_fp_options_id
and pbl.txn_currency_code = pftc.txn_currency_code;
l_global_tmp_rec.delete_flag := null;
l_txn_curr_tbl.DELETE;
SELECT (DECODE(l_ci_id,
null, DECODE(l_AR_flag,
'Y', l_projfunc_currency_code,
l_project_currency_code),
DECODE(l_version_type,
'ALL', l_agr_curr_code,
'REVENUE', l_agr_curr_code,
l_project_currency_code)))
INTO l_txn_curr_tbl(1).txn_currency_code
FROM dual;
/* Bug 5144013. Made changes in the below select to get the exchange rates of the transaction
currencies from pl/sql table l_res_ass_tbl.
*/
-- Bug 5330532. Used newly introduced rate table l_txn_curr_rate_tbl in the following select statement.
SELECT
decode(l_amt_type_tbl(amt).amount_type_code,'TOTAL_REV',decode(l_proj_fp_options_rec.projfunc_rev_rate_type,'User',l_res_ass_tbl(res).projfunc_rev_exchange_rate,TO_NUMBER(null)),
decode(l_proj_fp_options_rec.projfunc_cost_rate_type,'User',null,l_res_ass_tbl(res).projfunc_cost_exchange_rate,TO_NUMBER(null))),
decode(l_amt_type_tbl(amt).amount_type_code,'TOTAL_REV',decode(l_proj_fp_options_rec.project_rev_rate_type,'User',l_res_ass_tbl(res).project_rev_exchange_rate,TO_NUMBER(null)),
decode(l_proj_fp_options_rec.project_cost_rate_type,'User',null,l_res_ass_tbl(res).project_cost_exchange_rate,TO_NUMBER(null)))
INTO projfunc_exchange_rate,
project_exchange_rate
FROM dual;
INSERT INTO pa_fp_webadi_xface_tmp
VALUES l_global_tmp_tbl(tmp);
INSERT INTO pa_fp_webadi_xface_tmp
VALUES l_global_tmp_tbl(tmp);
pa_debug.g_err_stage := 'Finished inserting into Global temparory table';
INSERT INTO pa_fp_webadi_xface_tmp (BUDGET_VERSION_ID, PROJECT_ID, PROJECT_NAME, PROJECT_NUMBER, RECORD_VERSION_NUMBER)
VALUES (l_budget_version_id, l_project_id, l_project_name, l_project_number, l_rec_version_number);
,p_procedure_name => 'insert_periodic_tmp_table');
END insert_periodic_tmp_table;