[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT project_id
,fin_plan_type_id
,fin_plan_version_id
,NVL(p_target_fp_preference_code,fin_plan_preference_code) fin_plan_preference_code
,nvl(p_approved_rev_plan_type_flag,nvl(approved_rev_plan_type_flag,'N')) approved_rev_plan_type_flag--For Bug 2998696
-- ,plan_in_multi_curr_flag Bug:- 2706430
/* commented out as we should be using the passed value always */
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_target_fp_option_id;
SELECT project_currency_code
,projfunc_currency_code
FROM pa_projects_all
WHERE project_id = c_project_id;
SELECT txn_currency_code
FROM pa_fp_txn_currencies
WHERE proj_fp_options_id = p_target_fp_option_id
AND default_all_curr_flag = 'Y';
SELECT txn_currency_code
FROM pa_fp_txn_currencies
WHERE proj_fp_options_id = p_target_fp_option_id
AND default_cost_curr_flag = 'Y';
SELECT txn_currency_code
FROM pa_fp_txn_currencies
WHERE proj_fp_options_id = p_target_fp_option_id
AND default_rev_curr_flag = 'Y';
DELETE FROM pa_fp_txn_currencies
WHERE proj_fp_options_id = p_target_fp_option_id;
pa_debug.g_err_stage:='Calling Insert_Default_Currencies api';
Insert_Default_Currencies(
p_project_id => target_fp_options_rec.project_id
,p_fin_plan_type_id => target_fp_options_rec.fin_plan_type_id
,p_fin_plan_preference_code => target_fp_options_rec.fin_plan_preference_code
,p_fin_plan_version_id => target_fp_options_rec.fin_plan_version_id
,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code
,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
,p_target_fp_option_id => p_target_fp_option_id );
SELECT project_id
INTO l_source_project_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_source_fp_option_id;
/* #2632410: Modified the below logic to insert only Project Functional Records
when the l_only_projfunc_curr_flg returned by Insert_Only_Projfunc_Curr is TRUE. */
/* Getting the l_only_projfunc_curr_flg to determine if only the Project
Functional currency has to be inserted. */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage:='Calling Insert_Only_Projfunc_Curr';
l_only_projfunc_curr := Insert_Only_Projfunc_Curr( p_proj_fp_options_id => p_target_fp_option_id
,p_approved_rev_plan_type_flag => p_approved_rev_plan_type_flag );
IF l_only_projfunc_curr = TRUE THEN -- Call Insert Default currencies to insert only proj func record.
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage:='Calling Insert_Default_Currencies to insert projfunc record.';
Insert_Default_Currencies(
p_project_id => target_fp_options_rec.project_id
,p_fin_plan_type_id => target_fp_options_rec.fin_plan_type_id
,p_fin_plan_preference_code => target_fp_options_rec.fin_plan_preference_code
,p_fin_plan_version_id => target_fp_options_rec.fin_plan_version_id
,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code
,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
,p_target_fp_option_id => p_target_fp_option_id );
pa_debug.g_err_stage:='Inserting records into pa_fp_txn_currencies for the target ';
INSERT INTO PA_FP_TXN_CURRENCIES (
fp_txn_currency_id
,proj_fp_options_id
,project_id
,fin_plan_type_id
,fin_plan_version_id
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,project_cost_exchange_rate --fix for bug 2613901
,project_rev_exchange_rate
,projfunc_cost_exchange_Rate
,projfunc_rev_exchange_Rate)
SELECT pa_fp_txn_currencies_s.NEXTVAL
,p_target_fp_option_id
,target_fp_options_rec.project_id --project_id of target fp option
,target_fp_options_rec.fin_plan_type_id --plan_type of target fp option
,target_fp_options_rec.fin_plan_version_id --plan version of target fp option
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,project_cost_exchange_rate --fix for bug 2613901
,project_rev_exchange_rate
,projfunc_cost_exchange_Rate
,projfunc_rev_exchange_Rate
FROM pa_fp_txn_currencies
WHERE proj_fp_options_id = l_source_fp_option_id;
pa_debug.g_err_stage:='Calling Insert_Default_Currencies api ';
Insert_Default_Currencies(
p_project_id => target_fp_options_rec.project_id
,p_fin_plan_type_id => target_fp_options_rec.fin_plan_type_id
,p_fin_plan_preference_code => target_fp_options_rec.fin_plan_preference_code
,p_fin_plan_version_id => target_fp_options_rec.fin_plan_version_id
,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code
,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
,p_target_fp_option_id => p_target_fp_option_id );
This api is called from copy_fp_txn_currencies to insert default currencies
for target fp option if source option is null and parent option is not present
=============================================================================*/
PROCEDURE Insert_Default_Currencies(
p_project_id IN NUMBER
,p_fin_plan_type_id IN NUMBER
,p_fin_plan_preference_code IN VARCHAR2
,p_fin_plan_version_id IN NUMBER
,p_project_currency_code IN VARCHAR2
,p_projfunc_currency_code IN VARCHAR2
,p_approved_rev_plan_type_flag IN VARCHAR2
,p_target_fp_option_id IN NUMBER )
AS
l_only_proj_func_curr BOOLEAN; -- Added for #2632410
/* #2632410: Modified the below logic to insert the Project Currency Record
when the l_only_proj_func_curr returned by Insert_Only_Projfunc_Curr is
FALSE. */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage:='Calling Insert_Only_Projfunc_Curr - 1';
pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
l_only_proj_func_curr := Insert_Only_Projfunc_Curr( p_proj_fp_options_id => p_target_fp_option_id
,p_approved_rev_plan_type_flag => p_approved_rev_plan_type_flag );--For bug 2998696
IF l_only_proj_func_curr = FALSE THEN --Do not insert any proj currency rec if flag is TRUE
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage:='Inserting project currency as default currency ';
pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
INSERT INTO PA_FP_TXN_CURRENCIES (
fp_txn_currency_id
,proj_fp_options_id
,project_id
,fin_plan_type_id
,fin_plan_version_id
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,project_cost_exchange_rate --fix for bug 2613901
,project_rev_exchange_rate
,projfunc_cost_exchange_Rate
,projfunc_rev_exchange_Rate
)
SELECT pa_fp_txn_currencies_s.NEXTVAL
,p_target_fp_option_id
,p_project_id --project_id of target fp option
,p_fin_plan_type_id --plan_type of target fp option
,p_fin_plan_version_id --plan version of target fp option
,p_project_currency_code
,'N' --default_rev_curr_flag
,'N' --default_cost_curr_flag
,'N' --default_all_curr_flag
,'Y'--project_currency_flag
,DECODE(p_projfunc_currency_code,p_project_currency_code,'Y','N') --projfunc_currency_flag
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL --fix for bug 2613901
,NULL
,NULL
,NULL
FROM DUAL;
/* #2632410: The Project Functional Currency record has to be inserted
even when l_only_proj_func_curr is TRUE */
IF (p_projfunc_currency_code <> p_project_currency_code OR
l_only_proj_func_curr = TRUE) THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage:='Inserting projfunc currency ';
pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
INSERT INTO PA_FP_TXN_CURRENCIES (
fp_txn_currency_id
,proj_fp_options_id
,project_id
,fin_plan_type_id
,fin_plan_version_id
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,project_cost_exchange_rate --fix for bug 2613901
,project_rev_exchange_rate
,projfunc_cost_exchange_Rate
,projfunc_rev_exchange_Rate)
SELECT pa_fp_txn_currencies_s.NEXTVAL
,p_target_fp_option_id
,p_project_id --project_id of target fp option
,p_fin_plan_type_id --plan_type of target fp option
,p_fin_plan_version_id --plan version of target fp option
,p_projfunc_currency_code
,'N' --default_rev_curr_flag
,'N' --default_cost_curr_flag
,'N' --default_all_curr_flag
,DECODE(p_projfunc_currency_code,p_project_currency_code,'Y','N') --project_currency_flag
,'Y' --projfunc_currency_flag
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL --fix for bug 2613901
,NULL
,NULL
,NULL
FROM DUAL;
pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.g_err_stage:='Exiting Insert_Default_Currencies ';
pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.g_err_stage:='EXCEPTION Insert_Default_Currencies ' || SQLERRM;
pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
END Insert_Default_Currencies;
This api is called from copy_fp_txn_currencies and Insert_Default_Currencies
this api sets the default currency flags appropriately
=============================================================================*/
PROCEDURE Set_Default_Currencies(
p_target_fp_option_id IN NUMBER
,p_target_preference_code IN VARCHAR2
,p_approved_rev_plan_type_flag IN VARCHAR2
,p_srce_all_default_curr_code IN VARCHAR2
,p_srce_rev_default_curr_code IN VARCHAR2
,p_srce_cost_default_curr_code IN VARCHAR2
,p_project_currency_code IN VARCHAR2
,p_projfunc_currency_code IN VARCHAR2 )
AS
l_srce_cost_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
UPDATE pa_fp_txn_currencies
SET default_cost_curr_flag = DECODE(txn_currency_code,l_srce_cost_default_curr_code,'Y','N')
,default_rev_curr_flag = 'N'
,default_all_curr_flag = 'N'
WHERE proj_fp_options_id = p_target_fp_option_id ;
UPDATE pa_fp_txn_currencies
SET default_cost_curr_flag = 'N'
,default_rev_curr_flag = DECODE(txn_currency_code,l_srce_rev_default_curr_code,'Y','N')
,default_all_curr_flag = 'N'
WHERE proj_fp_options_id = p_target_fp_option_id ;
UPDATE pa_fp_txn_currencies
SET default_cost_curr_flag = 'N'
,default_rev_curr_flag = 'N'
,default_all_curr_flag = DECODE(txn_currency_code,l_srce_all_default_curr_code,'Y','N')
WHERE proj_fp_options_id = p_target_fp_option_id ;
pa_debug.g_err_stage:='About to update ';
UPDATE pa_fp_txn_currencies
SET default_cost_curr_flag = DECODE(txn_currency_code,l_srce_cost_default_curr_code,'Y','N')
,default_rev_curr_flag = DECODE(txn_currency_code,l_srce_rev_default_curr_code,'Y','N')
,default_all_curr_flag = 'N'
WHERE proj_fp_options_id = p_target_fp_option_id ;
SELECT proj_fp_options_id,
fin_plan_type_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_fin_plan_version_id
AND project_id = p_project_id;
DELETE FROM PA_FP_TXN_CURRENCIES
WHERE proj_fp_options_id = version_details_rec.proj_fp_options_id;
INSERT INTO PA_FP_TXN_CURRENCIES (
fp_txn_currency_id
,proj_fp_options_id
,project_id
,fin_plan_type_id
,fin_plan_version_id
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,project_cost_exchange_rate
,project_rev_exchange_rate
,projfunc_cost_exchange_Rate
,projfunc_rev_exchange_Rate)
SELECT pa_fp_txn_currencies_s.NEXTVAL
,version_details_rec.proj_fp_options_id
,p_project_id
,version_details_rec.fin_plan_type_id
,p_fin_plan_version_id
,l_agreement_currency_code -- txn_currency_code
,'Y' -- default_rev_curr_flag
,'Y' -- default_cost_curr_flag
,'Y' -- default_all_curr_flag
,DECODE(l_agreement_currency_code,l_project_currency_code,'Y','N') -- project_currency_flag
,DECODE(l_agreement_currency_code,l_projfunc_currency_code,'Y','N') -- projfunc_currency_flag
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL -- project_cost_exchange_rate
,NULL -- project_rev_exchange_rate
,NULL -- projfunc_cost_exchange_Rate
,NULL -- projfunc_rev_exchange_Rate
FROM DUAL;
the Project Functional currency attributes have to be inserted into pa_fp_txn_currencies table.
Only Project Functional Currency has to be inserted in the following situations:
- The Approved Revenue Flag for the Proj FP Option ID is 'Y'
- The Plan level is either 'PLAN_TYPE' or 'PLAN_VERSION'
- The Preference Code is either 'COST_AND_REV_SAME' or 'REVENUE_ONLY'
This function will be called from Copy_Fp_Txn_Currencies and also Insert_Default_Currencies to
get the l_insert_only_projfunc_curr flag.
Bug 3668370 Raja FP M changes Even for AR versions there can be multiple txn currencies
So, changed the api to always return false so that all the currencies from
parent record are added
===============================================================================================*/
FUNCTION Insert_Only_Projfunc_Curr( p_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE
,p_approved_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE)--for bug 2998696
RETURN BOOLEAN
IS
l_planning_level pa_proj_fp_options.fin_plan_option_level_code%TYPE;
l_insert_only_proj_func_curr BOOLEAN;
pa_debug.g_err_stage:='In Insert_Only_Projfunc_Curr';
pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
l_insert_only_proj_func_curr := FALSE;
pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
SELECT fin_plan_option_level_code
,fin_plan_preference_code
,nvl(p_approved_rev_plan_type_flag,nvl(approved_rev_plan_type_flag,'N'))--Bug 2998696
INTO l_planning_level
,l_fp_preference_code
,l_approved_rev_plan_type_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
pa_debug.g_err_stage:='Setting the l_insert_only_proj_func_curr as TRUE';
pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
l_insert_only_proj_func_curr := TRUE;
RETURN l_insert_only_proj_func_curr;
END Insert_Only_Projfunc_Curr;