The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_update_plan_type IN VARCHAR2
, p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_budget_version_number IN PA_BUDGET_VERSIONS.version_number%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_version_type IN PA_BUDGET_VERSIONS.version_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_finplan_type_id IN PA_BUDGET_VERSIONS.fin_plan_type_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_finplan_type_name IN PA_FIN_PLAN_TYPES_VL.name%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_resource_class_code_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
, p_resource_asgn_id_tab IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
, p_txn_curr_code_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
, p_refresh_cost_bill_rates_flag IN VARCHAR2 := 'N'
, p_refresh_conv_rates_flag IN VARCHAR2 := 'N'
, p_budget_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, x_return_status OUT VARCHAR2
, x_msg_count OUT NUMBER
, x_msg_data OUT VARCHAR2
)
IS
l_msg_count NUMBER := 0;
SELECT '1'
FROM DUAL
WHERE EXISTS
(
SELECT resource_assignment_id
FROM PA_RESOURCE_ASSIGNMENTS
WHERE PROJECT_ID = l_project_id
AND BUDGET_VERSION_ID = l_budget_version_id
AND RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id
);
select
pra.resource_assignment_id raid,
pbl.txn_currency_code
from
pa_resource_assignments pra,
pa_budget_lines pbl
where pra.project_id = l_project_id
and pra.budget_version_id = l_budget_version_id
AND pra.resource_class_code = l_resource_class_code
and pra.resource_assignment_id = pbl.resource_assignment_id
AND pra.budget_version_id = pbl.budget_version_id
GROUP BY pra.resource_assignment_id, pbl.txn_currency_code;
SELECT distinct txn_currency_code
FROM pa_budget_lines
WHERE resource_assignment_id = l_resource_assignment_id;
SELECT bv.fin_plan_type_id , bv.version_type, bv.version_number
FROM pa_budget_versions bv
WHERE bv.budget_version_id = l_budget_version_id
AND bv.project_id = l_project_id
AND bv.ci_id IS NULL;
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(
SELECT TXN_CURRENCY_CODE
FROM pa_budget_lines
WHERE txn_currency_code = l_currency_code
AND resource_assignment_id = l_resource_assignment_id
AND budget_version_id = l_budget_version_id
);
Pa_Debug.WRITE(g_module_name,'p_update_plan_type'||':'||p_update_plan_type,
l_debug_level3);
IF ( p_update_plan_type NOT IN ('WORKPLAN', 'FINPLAN')) THEN
IF l_debug_mode = 'Y' THEN
Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : p_update_plan_type is not WORKPLAN or FINPLAN ';
IF ( p_update_plan_type = 'WORKPLAN' AND
( p_structure_version_id is null or p_structure_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) AND
( p_budget_version_id is null or p_budget_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
) THEN
IF l_debug_mode = 'Y' THEN
Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : p_update_plan_type is WORKPLAN but str version id is not passed';
IF ( p_update_plan_type = 'FINPLAN' AND
(
( p_budget_version_number is null or p_budget_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) AND
( p_finplan_type_id is null or p_finplan_type_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
( p_finplan_type_name is null or p_finplan_type_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
) AND
( p_budget_version_id is null or p_budget_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
) THEN
IF l_debug_mode = 'Y' THEN
Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : p_update_plan_type is FINPLAN but params not passed';
IF (p_update_plan_type = 'WORKPLAN') THEN
IF ( p_budget_version_id is not null AND p_budget_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) THEN
l_budget_version_id := p_budget_version_id;
ELSIF (p_update_plan_type = 'FINPLAN') THEN
IF (p_budget_version_id is not null and p_budget_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
OPEN C_VALIDATE_FP_BUDGET_VERSION(p_budget_version_id, l_project_id);
,p_function_name => 'PA_PM_UPDATE_BUDGET'
,p_change_reason_code => l_change_reason_code
,x_budget_entry_method_code => l_budget_entry_method_code
,x_resource_list_id => l_resource_list_id
,x_budget_version_id => l_budget_version_id
,x_fin_plan_level_code => l_fin_plan_level_code
,x_time_phased_code => l_time_phased_code
,x_plan_in_multi_curr_flag => l_plan_in_multi_curr_flag
,x_budget_amount_code => l_budget_amount_code
,x_categorization_code => l_categorization_code
,x_project_number => l_project_number
/* Plan Amount Entry flags introduced by bug 6378555 */
/*Passing all as G_PA_MISS_CHAR since validations not required*/
,px_raw_cost_flag => l_pa_miss_char
,px_burdened_cost_flag => l_pa_miss_char
,px_revenue_flag => l_pa_miss_char
,px_cost_qty_flag => l_pa_miss_char
,px_revenue_qty_flag => l_pa_miss_char
,px_all_qty_flag => l_pa_miss_char
,px_bill_rate_flag => l_pa_miss_char
,px_cost_rate_flag => l_pa_miss_char
,px_burden_rate_flag => l_pa_miss_char
/* Plan Amount Entry flags introduced by bug 6378555 */
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
END IF;--update_plan_type FINPLAN
l_resource_asgn_id_tab.delete;
l_txn_curr_code_tab.delete;
In Case Selective Refresh is required, we can comment above and use following
After discussion with Venky, it is assumed that if user is passinf txn curr
code tbl he/she will pass it correctly with each txn curr code.
In case if it is decided to supprot selective refresh, following code can be uncommented
*/
OPEN c_get_txn_curr_code(l_resource_asgn_id);
l_raid_tmp_tbl.delete;
l_trxn_curr_code_tmp_tbl.delete;
l_raid_tmp_tbl.delete;
l_trxn_curr_code_tmp_tbl.delete;