[Home] [Help]
1: package body PA_PROJ_FP_OPTIONS_PUB as
2: /* $Header: PAFPOPPB.pls 120.15 2012/01/19 08:16:46 a5sharma ship $ */
3:
4: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
5: P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1: package body PA_PROJ_FP_OPTIONS_PUB as
2: /* $Header: PAFPOPPB.pls 120.15 2012/01/19 08:16:46 a5sharma ship $ */
3:
4: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
5: P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6:
7: /*==================================================================================================
8: CREATE_FP_OPTION: This procedure inserts or updates records in 3 table depending
30:
31: -- 26-JUN-2003 jwhite - Plannable Task HQ Dev Effort:
32: -- Make code changes to Create_FP_Option procedure to
33: -- enable population of new parameters on
34: -- PA_PROJ_FP_OPTIONS_PKG.Insert_Row table handler.
35:
36: --
37: r11.5 FP.M Developement ----------------------------------
38:
38:
39: 08-JAN-2004 jwhite Bug 3362316 (HQ)
40: Rewrote Create_Fp_Option.
41: - FP_COL Record specifiation definition
42: - PA_PROJ_FP_OPTIONS_PKG.update_row parm list
43: - PA_PROJ_FP_OPTIONS_PKG.insert_row parm list
44:
45: 3/30/2004 Raja FP M Dev Effort Copy Project Impact:
46: When versions are being copied across projects genration source plan versions
39: 08-JAN-2004 jwhite Bug 3362316 (HQ)
40: Rewrote Create_Fp_Option.
41: - FP_COL Record specifiation definition
42: - PA_PROJ_FP_OPTIONS_PKG.update_row parm list
43: - PA_PROJ_FP_OPTIONS_PKG.insert_row parm list
44:
45: 3/30/2004 Raja FP M Dev Effort Copy Project Impact:
46: When versions are being copied across projects genration source plan versions
47: can not be copied as they are. So, all gen source version id columns would be null
63: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
64: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
65: ,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
66:
67: FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
68: FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
64: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
65: ,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
66:
67: FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
68: FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
65: ,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
66:
67: FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
68: FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
66:
67: FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
68: FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74: l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
67: FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
68: FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74: l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
75: l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
68: FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74: l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
75: l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
76: l_copy_project_context VARCHAR2(1);
69: l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74: l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
75: l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
76: l_copy_project_context VARCHAR2(1);
77: /* Variables added for autobase line Bug#2619022*/
70: l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74: l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
75: l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
76: l_copy_project_context VARCHAR2(1);
77: /* Variables added for autobase line Bug#2619022*/
78: l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
71: l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72: l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73: l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74: l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
75: l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
76: l_copy_project_context VARCHAR2(1);
77: /* Variables added for autobase line Bug#2619022*/
78: l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
79: l_approved_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE;
75: l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
76: l_copy_project_context VARCHAR2(1);
77: /* Variables added for autobase line Bug#2619022*/
78: l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
79: l_approved_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE;
80: l_source_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
81:
82: /* Bug # 2702000 */
83: FP_Cols_Rec_Rev_Def PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
76: l_copy_project_context VARCHAR2(1);
77: /* Variables added for autobase line Bug#2619022*/
78: l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
79: l_approved_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE;
80: l_source_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
81:
82: /* Bug # 2702000 */
83: FP_Cols_Rec_Rev_Def PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
84:
79: l_approved_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE;
80: l_source_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
81:
82: /* Bug # 2702000 */
83: FP_Cols_Rec_Rev_Def PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
84:
85: l_debug_mode VARCHAR2(30);
86: l_msg_count NUMBER := 0;
87: l_data VARCHAR2(2000);
100: -- -------------------------------------------------------
101:
102: -- FP M Dev Effort new variables
103:
104: l_default_gen_options_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
105: l_source_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
106:
107: -- 3/30/2004 FP M Phase II Dev Effort
108:
116: l_prj_start_date VARCHAR2(100);
117: l_prj_end_date VARCHAR2(100);
118:
119: --Added for webAdi development
120: l_source_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
121: -- begin: Bug 5941436: fnd_profile.value_specific('PA_FP_WEBADI_ENABLE'); has been changed with fnd_profile.value('PA_FP_WEBADI_ENABLE'); to perform less sqls and use caching and therefore to improve the performance
122: /* Bug 6413612 : Added substr to fetch only 1 character of profile value */
123: l_webadi_profile VARCHAR(1) := UPPER(SUBSTR(fnd_profile.value_specific('PA_FP_WEBADI_ENABLE'), 1, 1));
124: -- end Bug 5941436:
137:
138: CURSOR opt_info_Cur (c_proj_fp_options_id NUMBER) IS
139: SELECT
140: nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
141: FROM pa_proj_fp_options
142: where proj_fp_options_id = c_proj_fp_options_id;
143:
144: opt_info_rec opt_info_Cur%ROWTYPE;
145: plan_type_info_rec plan_type_info_cur%ROWTYPE;
154:
155: BEGIN
156: FND_MSG_PUB.initialize;
157: IF P_PA_DEBUG_MODE = 'Y' THEN
158: pa_debug.init_err_stack('PA_PROJ_FP_OPTIONS_PUB.Create_FP_Option');
159: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
160: l_debug_mode := NVL(l_debug_mode, 'Y');
161: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
162: END IF;
240: ELSE /* M22-AUG: if target is not null get values from target */
241: /* If the Target Project Option ID is not NULL, then get the Plan_Type_ID
242: and other columns from the database for this Proj FP Option ID so that even if
243: NULL values are passed in the parameters, the database values retrieved are passed
244: to the Table Handlers PA_PROJ_FP_OPTIONS_PKG.update_row and insert_row. */
245:
246: IF P_PA_DEBUG_MODE = 'Y' THEN
247: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': target option id is not null. Populating from target';
248: pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
251: SELECT fin_plan_type_id, fin_plan_version_id, fin_plan_option_level_code,
252: nvl(l_fp_preference_code, fin_plan_preference_code) /* get only if l_fp_preference_code is not null */
253: INTO l_plan_type_id, l_plan_version_id, l_target_option_level_code,
254: l_fp_preference_code
255: FROM pa_proj_fp_options
256: WHERE proj_fp_options_id = px_target_proj_fp_option_id;
257:
258: END IF;
259:
299: l_source_option_level_code,
300: l_source_project_id,
301: l_source_plan_class_code,
302: l_source_plan_type_id -- Added this to get the source plan type id for copying the amount types
303: FROM pa_proj_fp_options pfo,
304: pa_fin_plan_types_b pt
305: WHERE pfo.proj_fp_options_id = p_source_proj_fp_option_id
306: AND pfo.fin_plan_type_id = pt.fin_plan_type_id(+);
307:
519: pt.plan_class_code
520: INTO l_source_fp_preference_code,
521: l_source_option_level_code,
522: l_source_plan_class_code
523: FROM pa_proj_fp_options pfo,
524: pa_fin_plan_types_b pt
525: WHERE pfo.proj_fp_options_id = l_par_proj_fp_options_id
526: AND pfo.fin_plan_type_id = pt.fin_plan_type_id(+);
527: EXCEPTION
1158: END IF;
1159:
1160: -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1161:
1162: PA_PROJ_FP_OPTIONS_PKG.update_row
1163: ( p_proj_fp_options_id => px_target_proj_fp_option_id
1164: ,p_record_version_number => NULL
1165: ,p_project_id => p_target_project_id
1166: ,p_fin_plan_option_level_code => l_target_option_level_code
1311: END IF;
1312:
1313: -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1314:
1315: PA_PROJ_FP_OPTIONS_PKG.Insert_Row
1316: ( px_proj_fp_options_id => px_target_proj_fp_option_id
1317: ,p_project_id => p_target_project_id
1318: ,p_fin_plan_option_level_code => l_target_option_level_code
1319: ,p_fin_plan_type_id => l_plan_type_id
1525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526: x_msg_count := 1;
1527: x_msg_data := SQLERRM;
1528: FND_MSG_PUB.add_exc_msg
1529: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Create_FP_Option'
1530: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1531: IF P_PA_DEBUG_MODE = 'Y' THEN
1532: pa_debug.write('Create_FP_Option: ' || l_module_name,SQLERRM,5);
1533: pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.G_Err_Stack,5);
1556: r11.5 FP.M Developement ----------------------------------
1557:
1558: 08-JAN-2004 jwhite Bug 3362316 (HQ)
1559: Extensively rewrote Get_Fp_Options
1560: - All SELECTS from pa_proj_fp_options
1561:
1562: 23-JAN-2004 rravipat Bug 3354518 (IDC)
1563: ===========================================================================================*/
1564: PROCEDURE Get_FP_Options (
1576: l_data VARCHAR2(2000);
1577: l_msg_data VARCHAR2(2000);
1578: l_msg_index_out NUMBER;
1579:
1580: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1581: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1582:
1583: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1584: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1577: l_msg_data VARCHAR2(2000);
1578: l_msg_index_out NUMBER;
1579:
1580: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1581: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1582:
1583: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1584: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1585:
1579:
1580: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1581: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1582:
1583: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1584: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1585:
1586: l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1587: l_revenue_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1580: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1581: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1582:
1583: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1584: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1585:
1586: l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1587: l_revenue_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1588: l_all_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1609: l_target_burd_rate_flag pa_fin_plan_amount_sets.burden_rate_flag%TYPE;
1610:
1611: BEGIN
1612: IF P_PA_DEBUG_MODE = 'Y' THEN
1613: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_FP_Options');
1614: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1615: l_debug_mode := NVL(l_debug_mode, 'Y');
1616: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1617: END IF;
1623: project_id
1624: INTO l_source_fin_plan_pref,
1625: l_fin_plan_option_level_code,
1626: l_project_id
1627: FROM pa_proj_fp_options
1628: WHERE proj_fp_options_id = p_proj_fp_options_id;
1629:
1630: l_target_fin_plan_pref := Nvl(p_fin_plan_preference_code, l_source_fin_plan_pref);
1631:
1698: END IF;
1699:
1700: SELECT all_amount_set_id
1701: INTO l_target_all_amount_set_id
1702: FROM pa_proj_fp_options
1703: WHERE proj_fp_options_id = p_target_fp_options_id;
1704:
1705: /* We need to use the ALL amount set id only if the target preference code is ALL.
1706: l_target_fin_plan_pref doesnt indicate the preference code of the target option,
1735: ,all_amount_set_id
1736: INTO l_cost_amount_set_id
1737: ,l_revenue_amount_set_id
1738: ,l_all_amount_set_id
1739: FROM pa_proj_fp_options
1740: WHERE proj_fp_options_id = p_proj_fp_options_id;
1741:
1742:
1743: IF l_cost_amount_set_id IS NOT NULL THEN
1934: ,def_markup_percentage --CBS
1935: ,def_raw_cost_currency_code --CBS
1936: ,def_bill_rate_currency_code --CBS
1937: INTO x_fp_cols_rec
1938: FROM pa_proj_fp_options
1939: WHERE proj_fp_options_id = p_proj_fp_options_id;
1940:
1941: -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1942:
2103: ,def_markup_percentage --CBS
2104: ,def_raw_cost_currency_code --CBS
2105: ,def_bill_rate_currency_code --CBS
2106: INTO x_fp_cols_rec
2107: FROM pa_proj_fp_options
2108: WHERE proj_fp_options_id = p_proj_fp_options_id;
2109:
2110:
2111:
2268: ,def_markup_percentage --CBS
2269: ,def_raw_cost_currency_code --CBS
2270: ,def_bill_rate_currency_code --CBS
2271: INTO x_fp_cols_rec
2272: FROM pa_proj_fp_options
2273: WHERE proj_fp_options_id = p_proj_fp_options_id;
2274:
2275: -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
2276:
2444: ,def_markup_percentage --CBS
2445: ,def_raw_cost_currency_code --CBS
2446: ,def_bill_rate_currency_code --CBS
2447: INTO x_fp_cols_rec
2448: FROM pa_proj_fp_options
2449: WHERE proj_fp_options_id = p_proj_fp_options_id;
2450:
2451: END IF;
2452:
2480: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2481: x_msg_count := 1;
2482: x_msg_data := SQLERRM;
2483: FND_MSG_PUB.add_exc_msg
2484: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_FP_Options'
2485: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2486: IF P_PA_DEBUG_MODE = 'Y' THEN
2487: pa_debug.write('Get_FP_Options: ' || l_module_name,SQLERRM,5);
2488: pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.G_Err_Stack,5);
2502: option id of it's parent (i.e FP Option ID of the Option Level Code PROJECT is returned for
2503: the project_id of the input).
2504: ============================================================================================*/
2505: FUNCTION Get_Parent_FP_Option_ID(
2506: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2507:
2508: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2509: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2510: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2504: ============================================================================================*/
2505: FUNCTION Get_Parent_FP_Option_ID(
2506: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2507:
2508: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2509: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2510: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2511: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2512: l_debug_mode VARCHAR2(30);
2505: FUNCTION Get_Parent_FP_Option_ID(
2506: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2507:
2508: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2509: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2510: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2511: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2512: l_debug_mode VARCHAR2(30);
2513:
2506: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2507:
2508: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2509: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2510: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2511: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2512: l_debug_mode VARCHAR2(30);
2513:
2514: BEGIN
2507:
2508: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2509: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2510: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2511: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2512: l_debug_mode VARCHAR2(30);
2513:
2514: BEGIN
2515: IF P_PA_DEBUG_MODE = 'Y' THEN
2512: l_debug_mode VARCHAR2(30);
2513:
2514: BEGIN
2515: IF P_PA_DEBUG_MODE = 'Y' THEN
2516: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID');
2517: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2518: l_debug_mode := NVL(l_debug_mode, 'Y');
2519: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2520: END IF;
2520: END IF;
2521:
2522: SELECT fin_plan_option_level_code, project_id, fin_plan_type_id
2523: INTO l_fp_option_level_code, l_proj_id, l_fp_type_id
2524: FROM pa_proj_fp_options
2525: WHERE proj_fp_options_id = p_proj_fp_options_id;
2526:
2527: /* To get the Parent Option of a PLAN_VERSION, PLAN_TYPE option for the
2528: Project and Plan Type has to be selected. */
2535: END IF;
2536:
2537: SELECT proj_fp_options_id
2538: INTO x_proj_fp_options_id
2539: FROM pa_proj_fp_options
2540: WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
2541: AND project_id = l_proj_id
2542: AND fin_plan_type_id = l_fp_type_id;
2543:
2551:
2552: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2553: SELECT proj_fp_options_id
2554: INTO x_proj_fp_options_id
2555: FROM pa_proj_fp_options
2556: WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
2557: AND project_id = l_proj_id;
2558:
2559: END IF;
2578: RETURN NULL;
2579:
2580: WHEN OTHERS THEN
2581: FND_MSG_PUB.add_exc_msg
2582: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID'
2583: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2584: IF P_PA_DEBUG_MODE = 'Y' THEN
2585: pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,SQLERRM,5);
2586: pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.G_Err_Stack,5);
2592: /*============================================================================================
2593: GET_FP_OPTION_ID: This procedure returns the Proj FP Option ID based on the input Project_ID,
2594: Plan_Type_ID and the Plan_Version_ID.
2595: The Option_Level_Code is determined using the input parameters. The Proj FP Option ID is then
2596: got from the table PA_Proj_FP_Options by using the appropriate conditions based on the Option
2597: Level Code. (i.e. if the Option Level Code is PROJECT, only the Project_ID is checked for
2598: in the table etc.)
2599: ============================================================================================*/
2600: FUNCTION Get_FP_Option_ID(
2599: ============================================================================================*/
2600: FUNCTION Get_FP_Option_ID(
2601: p_project_id IN NUMBER
2602: ,p_plan_type_id IN NUMBER
2603: ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2604:
2605: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2606: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2607: l_debug_mode VARCHAR2(30);
2601: p_project_id IN NUMBER
2602: ,p_plan_type_id IN NUMBER
2603: ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2604:
2605: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2606: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2607: l_debug_mode VARCHAR2(30);
2608:
2609: BEGIN
2602: ,p_plan_type_id IN NUMBER
2603: ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2604:
2605: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2606: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2607: l_debug_mode VARCHAR2(30);
2608:
2609: BEGIN
2610:
2608:
2609: BEGIN
2610:
2611: IF P_PA_DEBUG_MODE = 'Y' THEN
2612: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_FP_Option_ID');
2613: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2614: l_debug_mode := NVL(l_debug_mode, 'Y');
2615: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2616: END IF;
2642: IF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT) THEN
2643:
2644: SELECT proj_fp_options_id
2645: INTO x_proj_fp_options_id
2646: FROM pa_proj_fp_options
2647: WHERE project_id = p_project_id
2648: AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
2649:
2650: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2650: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2651:
2652: SELECT proj_fp_options_id
2653: INTO x_proj_fp_options_id
2654: FROM pa_proj_fp_options
2655: WHERE project_id = p_project_id
2656: AND fin_plan_type_id = p_plan_type_id
2657: AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
2658:
2659: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
2660:
2661: SELECT proj_fp_options_id
2662: INTO x_proj_fp_options_id
2663: FROM pa_proj_fp_options
2664: WHERE project_id = p_project_id
2665: AND fin_plan_type_id = p_plan_type_id
2666: AND fin_plan_version_id = p_plan_version_id
2667: AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
2687: RETURN NULL;
2688:
2689: WHEN OTHERS THEN
2690: FND_MSG_PUB.add_exc_msg
2691: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_FP_Option_ID'
2692: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2693: IF P_PA_DEBUG_MODE = 'Y' THEN
2694: pa_debug.write('Get_FP_Option_ID: ' || l_module_name,SQLERRM,5);
2695: pa_debug.write('Get_FP_Option_ID: ' || l_module_name,pa_debug.G_Err_Stack,5);
2734: =====================================================================================*/
2735: FUNCTION Get_Default_FP_Options(
2736: p_fin_plan_preference_code IN VARCHAR2 ,
2737: p_target_project_id IN pa_projects_all.project_id%TYPE,
2738: p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2739:
2740: /* Declaring Constants */
2741: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2742: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2737: p_target_project_id IN pa_projects_all.project_id%TYPE,
2738: p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2739:
2740: /* Declaring Constants */
2741: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2742: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2743: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2744:
2745: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2738: p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2739:
2740: /* Declaring Constants */
2741: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2742: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2743: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2744:
2745: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2746:
2739:
2740: /* Declaring Constants */
2741: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2742: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2743: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2744:
2745: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2746:
2747: l_return_status VARCHAR2(2000);
2741: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2742: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2743: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2744:
2745: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2746:
2747: l_return_status VARCHAR2(2000);
2748: l_msg_count NUMBER := 0;
2749: l_msg_data VARCHAR2(2000);
2753: l_msg_index_out NUMBER := 0;
2754: l_data VARCHAR2(2000);
2755: l_debug_mode VARCHAR2(30);
2756:
2757: l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2758: l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2759: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2760: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2761:
2754: l_data VARCHAR2(2000);
2755: l_debug_mode VARCHAR2(30);
2756:
2757: l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2758: l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2759: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2760: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2761:
2762: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2755: l_debug_mode VARCHAR2(30);
2756:
2757: l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2758: l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2759: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2760: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2761:
2762: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2763: --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2759: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2760: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2761:
2762: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2763: --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2764: l_multi_curr_flag pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2765: l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2766: l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2767: l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2760: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2761:
2762: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2763: --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2764: l_multi_curr_flag pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2765: l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2766: l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2767: l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2768:
2766: l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2767: l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2768:
2769: /* added following local variables as part of changes due to autobaseline */
2770: l_rev_fin_plan_level_code pa_proj_fp_options.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2771: l_autobaseline_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE := 'N';
2772: l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2773: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2774:
2769: /* added following local variables as part of changes due to autobaseline */
2770: l_rev_fin_plan_level_code pa_proj_fp_options.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2771: l_autobaseline_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE := 'N';
2772: l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2773: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2774:
2775: -- FP M Dev Effort Variables used for calling rate schedules util api
2776: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2772: l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2773: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2774:
2775: -- FP M Dev Effort Variables used for calling rate schedules util api
2776: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2773: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2774:
2775: -- FP M Dev Effort Variables used for calling rate schedules util api
2776: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2781: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2774:
2775: -- FP M Dev Effort Variables used for calling rate schedules util api
2776: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2781: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2782:
2775: -- FP M Dev Effort Variables used for calling rate schedules util api
2776: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2781: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2782:
2783: --Adding the variables to get the default vaules for the seeded webadi layouts
2776: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2781: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2782:
2783: --Adding the variables to get the default vaules for the seeded webadi layouts
2784: l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2777: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2781: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2782:
2783: --Adding the variables to get the default vaules for the seeded webadi layouts
2784: l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2785: l_non_periodic_forecast_layout VARCHAR2(30) := 'NPE_FORECAST';
2784: l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2785: l_non_periodic_forecast_layout VARCHAR2(30) := 'NPE_FORECAST';
2786: l_webadi_profile VARCHAR(1);
2787:
2788: l_revenue_derivation_method pa_proj_fp_options.revenue_derivation_method%TYPE; --Bug 5462471
2789:
2790: CURSOR plan_type_info_cur (c_plan_type_id NUMBER) IS
2791: SELECT plan_class_code
2792: ,nvl(approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
2823:
2824: BEGIN
2825:
2826: IF P_PA_DEBUG_MODE = 'Y' THEN
2827: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_Default_FP_Options');
2828: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2829: l_debug_mode := NVL(l_debug_mode, 'Y');
2830: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2831: END IF;
3229: 'BUDGET','CURRENT_BASELINED',
3230: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3231: INTO l_fp_cols_rec.gen_src_cost_plan_type_id
3232: ,l_fp_cols_rec.gen_src_cost_plan_ver_code -- Bug 3572548
3233: FROM pa_proj_fp_options o
3234: ,pa_fin_plan_types_b pt
3235: WHERE o.project_id = p_target_project_id
3236: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3237: AND o.fin_plan_type_id = pt.fin_plan_type_id
3247: (SELECT pt.fin_plan_type_id
3248: ,DECODE(pt.plan_class_code,
3249: 'BUDGET','CURRENT_BASELINED',
3250: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3251: FROM pa_proj_fp_options o
3252: ,pa_fin_plan_types_vl pt
3253: WHERE o.project_id = p_target_project_id
3254: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3255: AND o.fin_plan_preference_code <> 'REVENUE_ONLY'
3368: 'BUDGET','CURRENT_BASELINED',
3369: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3370: INTO l_fp_cols_rec.gen_src_rev_plan_type_id
3371: ,l_fp_cols_rec.gen_src_rev_plan_ver_code -- Bug 3572548
3372: FROM pa_proj_fp_options o
3373: ,pa_fin_plan_types_b pt
3374: WHERE o.project_id = p_target_project_id
3375: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3376: AND o.fin_plan_type_id = pt.fin_plan_type_id
3387: SELECT pt.fin_plan_type_id
3388: ,DECODE(pt.plan_class_code,
3389: 'BUDGET','CURRENT_BASELINED',
3390: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3391: FROM pa_proj_fp_options o
3392: ,pa_fin_plan_types_vl pt
3393: WHERE o.project_id = p_target_project_id
3394: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3395: AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3463: 'BUDGET','CURRENT_BASELINED',
3464: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3465: INTO l_fp_cols_rec.gen_src_all_plan_type_id
3466: ,l_fp_cols_rec.gen_src_all_plan_ver_code -- Bug 3572548
3467: FROM pa_proj_fp_options o
3468: ,pa_fin_plan_types_b pt
3469: WHERE o.project_id = p_target_project_id
3470: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3471: AND o.fin_plan_type_id = pt.fin_plan_type_id
3482: (SELECT pt.fin_plan_type_id
3483: ,DECODE(pt.plan_class_code,
3484: 'BUDGET','CURRENT_BASELINED',
3485: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3486: FROM pa_proj_fp_options o
3487: ,pa_fin_plan_types_vl pt
3488: WHERE o.project_id = p_target_project_id
3489: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3490: AND o.fin_plan_type_id = pt.fin_plan_type_id
3583: END IF;
3584: RAISE;
3585: WHEN OTHERS THEN
3586: FND_MSG_PUB.add_exc_msg
3587: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_Default_FP_Options'
3588: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3589: IF P_PA_DEBUG_MODE = 'Y' THEN
3590: pa_debug.write('Get_Default_FP_Options: ' || l_module_name,SQLERRM,5);
3591: pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.G_Err_Stack,5);
3600: passed along with the approved_cost_plan_type_flag and approved_rev_plan_type_flag
3601: ===========================================================================================*/
3602: FUNCTION Get_FP_Proj_Mc_Options (p_proj_fp_options_id IN NUMBER) Return FP_MC_COLS
3603: IS
3604: l_fp_mc_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
3605: BEGIN
3606:
3607: SELECT approved_cost_plan_type_flag
3608: ,approved_rev_plan_type_flag
3620: ,project_rev_rate_type
3621: ,project_rev_rate_date_type
3622: ,project_rev_rate_date
3623: INTO l_fp_mc_cols_rec
3624: FROM pa_proj_fp_options
3625: WHERE proj_fp_options_id = p_proj_fp_options_id;
3626:
3627: RETURN l_fp_mc_cols_rec;
3628: EXCEPTION
3635: FP Option ID passed along with approved_cost_plan_type_flag and approved_rev_plan_type_flag
3636: ===========================================================================================*/
3637: FUNCTION Get_FP_Plan_Type_Mc_Options (p_fin_plan_type_id IN NUMBER) Return FP_MC_COLS
3638: IS
3639: l_fp_mc_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
3640: BEGIN
3641: SELECT approved_cost_plan_type_flag
3642: ,approved_rev_plan_type_flag
3643: ,primary_cost_forecast_flag
3676: the columns that are part of both pa_budget_versions and also pa_proj_fp_otions.
3677: They are resource_list_id, period_mask_id and current_planning_period.
3678: actual_amts_thru_period is a column present only in budget versions table. So,
3679: only this column is passed and rest of the values should be read from
3680: pa_proj_fp_options table
3681:
3682: Note: In FP M, period profile concept has been changed to period masks. Commenting
3683: all the related existing code
3684: */
3691: )
3692: IS
3693:
3694: l_project_id pa_budget_versions.project_id%TYPE;
3695: l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
3696: l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
3697: /*
3698: l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3699: l_curr_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3692: IS
3693:
3694: l_project_id pa_budget_versions.project_id%TYPE;
3695: l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
3696: l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
3697: /*
3698: l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3699: l_curr_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3700: l_period_type pa_proj_period_profiles.plan_period_type%TYPE;
3728: fin_plan_preference_code,
3729: cost_resource_list_id,
3730: revenue_resource_list_id,
3731: all_resource_list_id
3732: FROM pa_proj_fp_options
3733: WHERE fin_plan_version_id = p_budget_version_id;
3734:
3735: version_option_info_rec version_option_info_cur%ROWTYPE;
3736:
3737: BEGIN
3738:
3739: FND_MSG_PUB.initialize;
3740: IF P_PA_DEBUG_MODE = 'Y' THEN
3741: pa_debug.init_err_stack('PA_PROJ_FP_OPTIONS_PUB.SYNCHRONIZE_BUDGET_VERSION');
3742: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3743: l_debug_mode := NVL(l_debug_mode, 'Y');
3744: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3745: END IF;
3984: WHEN Others THEN
3985: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3986: x_msg_count := 1;
3987: x_msg_data := SQLERRM;
3988: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB'
3989: ,p_procedure_name => 'SYNCHRONIZE_BUDGET_VERSION');
3990: IF P_PA_DEBUG_MODE = 'Y' THEN
3991: pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3992: pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
4005: =====================================================================================*/
4006: FUNCTION Gen_Src_Plan_Version_Id(
4007: p_target_project_id IN pa_projects_all.project_id%TYPE,
4008: p_target_version_type IN pa_budget_versions.version_type%TYPE,
4009: p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
4010: p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
4011: RETURN pa_budget_versions.budget_version_id%TYPE is
4012:
4013: l_return_status VARCHAR2(2000);
4006: FUNCTION Gen_Src_Plan_Version_Id(
4007: p_target_project_id IN pa_projects_all.project_id%TYPE,
4008: p_target_version_type IN pa_budget_versions.version_type%TYPE,
4009: p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
4010: p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
4011: RETURN pa_budget_versions.budget_version_id%TYPE is
4012:
4013: l_return_status VARCHAR2(2000);
4014: l_msg_count NUMBER := 0;
4019: l_msg_index_out NUMBER := 0;
4020: l_data VARCHAR2(2000);
4021: l_debug_mode VARCHAR2(30);
4022:
4023: l_dummy_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4024: l_gen_src_plan_version_id pa_budget_versions.budget_version_id%TYPE;
4025:
4026: CURSOR src_plan_type_info_cur (c_fin_plan_type_id NUMBER, c_project_id NUMBER) IS
4027: SELECT fin_plan_preference_code
4024: l_gen_src_plan_version_id pa_budget_versions.budget_version_id%TYPE;
4025:
4026: CURSOR src_plan_type_info_cur (c_fin_plan_type_id NUMBER, c_project_id NUMBER) IS
4027: SELECT fin_plan_preference_code
4028: FROM pa_proj_fp_options
4029: WHERE project_id = c_project_id
4030: AND fin_plan_type_id = c_fin_plan_type_id
4031: AND fin_plan_option_level_code = 'PLAN_TYPE';
4032:
4034:
4035: BEGIN
4036:
4037: IF P_PA_DEBUG_MODE = 'Y' THEN
4038: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Gen_Src_Plan_Version_Id');
4039: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4040: l_debug_mode := NVL(l_debug_mode, 'Y');
4041: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4042: END IF;
4232: END IF;
4233: RAISE;
4234: WHEN OTHERS THEN
4235: FND_MSG_PUB.add_exc_msg
4236: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Gen_Src_Plan_Version_Id'
4237: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4238: IF P_PA_DEBUG_MODE = 'Y' THEN
4239: pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,SQLERRM,5);
4240: pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.G_Err_Stack,5);
4251: Initial Creation
4252: =====================================================================================*/
4253: FUNCTION Gen_Src_WP_Version_Id(
4254: p_target_project_id IN pa_projects_all.project_id%TYPE,
4255: p_gen_src_wp_ver_code IN pa_proj_fp_options.gen_src_cost_wp_ver_code%TYPE)
4256: RETURN pa_budget_versions.budget_version_id%TYPE is
4257:
4258: l_return_status VARCHAR2(2000);
4259: l_msg_count NUMBER := 0;
4358: pa_debug.reset_curr_function;
4359: RAISE;
4360: WHEN OTHERS THEN
4361: FND_MSG_PUB.add_exc_msg
4362: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Gen_Src_WP_Version_Id'
4363: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4364: IF P_PA_DEBUG_MODE = 'Y' THEN
4365: pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,SQLERRM,5);
4366: pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,pa_debug.G_Err_Stack,5);
4381:
4382: PROCEDURE Create_amt_types (
4383: p_project_id IN pa_projects_all.project_id%TYPE
4384: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4385: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4386: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4387: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4388: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4389: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4382: PROCEDURE Create_amt_types (
4383: p_project_id IN pa_projects_all.project_id%TYPE
4384: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4385: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4386: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4387: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4388: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4389: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4390: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4383: p_project_id IN pa_projects_all.project_id%TYPE
4384: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4385: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4386: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4387: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4388: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4389: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4390: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4391: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4384: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4385: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4386: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4387: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4388: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4389: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4390: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4391: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4392: ) IS
4399: l_revenue_layout_type_code VARCHAR2(30);
4400: l_all_layout_type_code VARCHAR2(30);
4401: l_debug_mode VARCHAR2(30);
4402: l_stage NUMBER := 100;
4403: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
4404: P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4405: l_msg_count NUMBER :=0;
4406: l_msg_data VARCHAR2(2000);
4407: l_data VARCHAR2(2000);
4427: PA_DEBUG.Set_Curr_Function( p_function => l_module_name,
4428: p_debug_mode => l_debug_mode );
4429:
4430: IF P_PA_DEBUG_MODE = 'Y' THEN
4431: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Create_amt_types');
4432: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4433: l_debug_mode := NVL(l_debug_mode, 'Y');
4434: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4435: END IF;
4821: RAISE;
4822:
4823: WHEN OTHERS THEN
4824: FND_MSG_PUB.add_exc_msg
4825: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB' ||
4826: 'Create_amt_types'
4827: ,p_procedure_name => PA_DEBUG.G_Err_Stack);
4828: pa_debug.g_err_stage := 'Unexpected error in Create_amt_types:';
4829: pa_debug.write('Create_amt_types: ' || l_module_name,pa_debug.g_err_stage,5);
4852: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4853: ) IS
4854:
4855: l_stage NUMBER := 100;
4856: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub.copy_amt_types';
4857: P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4858: --Start of variables used for debugging
4859: l_return_status VARCHAR2(1);
4860: l_msg_count NUMBER := 0;
4956: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4957: x_msg_count := 1;
4958: x_msg_data := SQLERRM;
4959:
4960: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_proj_fp_options_pub'
4961: ,p_procedure_name => 'copy_amt_types');
4962:
4963: IF l_debug_mode = 'Y' THEN
4964: pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4984: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4985: ) IS
4986:
4987: l_stage NUMBER := 100;
4988: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
4989: P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4990: --Start of variables used for debugging
4991: l_return_status VARCHAR2(1);
4992: l_msg_count NUMBER := 0;
5204: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5205: x_msg_count := 1;
5206: x_msg_data := SQLERRM;
5207:
5208: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_proj_fp_options_pub'
5209: ,p_procedure_name => 'update_amt_types');
5210:
5211: IF l_debug_mode = 'Y' THEN
5212: pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
5217: RAISE;
5218:
5219: END update_amt_types;
5220:
5221: END PA_PROJ_FP_OPTIONS_PUB;