[Home] [Help]
1: package body PA_PROJ_FP_OPTIONS_PUB as
2: /* $Header: PAFPOPPB.pls 120.10 2007/11/26 15:50:53 rthumma 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.10 2007/11/26 15:50:53 rthumma 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
1149: END IF;
1150:
1151: -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1152:
1153: PA_PROJ_FP_OPTIONS_PKG.update_row
1154: ( p_proj_fp_options_id => px_target_proj_fp_option_id
1155: ,p_record_version_number => NULL
1156: ,p_project_id => p_target_project_id
1157: ,p_fin_plan_option_level_code => l_target_option_level_code
1296: END IF;
1297:
1298: -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1299:
1300: PA_PROJ_FP_OPTIONS_PKG.Insert_Row
1301: ( px_proj_fp_options_id => px_target_proj_fp_option_id
1302: ,p_project_id => p_target_project_id
1303: ,p_fin_plan_option_level_code => l_target_option_level_code
1304: ,p_fin_plan_type_id => l_plan_type_id
1504: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1505: x_msg_count := 1;
1506: x_msg_data := SQLERRM;
1507: FND_MSG_PUB.add_exc_msg
1508: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Create_FP_Option'
1509: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1510: IF P_PA_DEBUG_MODE = 'Y' THEN
1511: pa_debug.write('Create_FP_Option: ' || l_module_name,SQLERRM,5);
1512: pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.G_Err_Stack,5);
1535: r11.5 FP.M Developement ----------------------------------
1536:
1537: 08-JAN-2004 jwhite Bug 3362316 (HQ)
1538: Extensively rewrote Get_Fp_Options
1539: - All SELECTS from pa_proj_fp_options
1540:
1541: 23-JAN-2004 rravipat Bug 3354518 (IDC)
1542: ===========================================================================================*/
1543: PROCEDURE Get_FP_Options (
1555: l_data VARCHAR2(2000);
1556: l_msg_data VARCHAR2(2000);
1557: l_msg_index_out NUMBER;
1558:
1559: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1560: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1561:
1562: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1563: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1556: l_msg_data VARCHAR2(2000);
1557: l_msg_index_out NUMBER;
1558:
1559: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1560: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1561:
1562: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1563: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1564:
1558:
1559: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1560: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1561:
1562: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1563: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1564:
1565: l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1566: l_revenue_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1559: l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1560: l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1561:
1562: l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1563: l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1564:
1565: l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1566: l_revenue_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1567: l_all_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1588: l_target_burd_rate_flag pa_fin_plan_amount_sets.burden_rate_flag%TYPE;
1589:
1590: BEGIN
1591: IF P_PA_DEBUG_MODE = 'Y' THEN
1592: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_FP_Options');
1593: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1594: l_debug_mode := NVL(l_debug_mode, 'Y');
1595: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1596: END IF;
1602: project_id
1603: INTO l_source_fin_plan_pref,
1604: l_fin_plan_option_level_code,
1605: l_project_id
1606: FROM pa_proj_fp_options
1607: WHERE proj_fp_options_id = p_proj_fp_options_id;
1608:
1609: l_target_fin_plan_pref := Nvl(p_fin_plan_preference_code, l_source_fin_plan_pref);
1610:
1677: END IF;
1678:
1679: SELECT all_amount_set_id
1680: INTO l_target_all_amount_set_id
1681: FROM pa_proj_fp_options
1682: WHERE proj_fp_options_id = p_target_fp_options_id;
1683:
1684: /* We need to use the ALL amount set id only if the target preference code is ALL.
1685: l_target_fin_plan_pref doesnt indicate the preference code of the target option,
1714: ,all_amount_set_id
1715: INTO l_cost_amount_set_id
1716: ,l_revenue_amount_set_id
1717: ,l_all_amount_set_id
1718: FROM pa_proj_fp_options
1719: WHERE proj_fp_options_id = p_proj_fp_options_id;
1720:
1721:
1722: IF l_cost_amount_set_id IS NOT NULL THEN
1907: ,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,revenue_layout_code,null)
1908: ,NULL all_layout_code
1909: ,revenue_derivation_method -- Bug 5462471
1910: INTO x_fp_cols_rec
1911: FROM pa_proj_fp_options
1912: WHERE proj_fp_options_id = p_proj_fp_options_id;
1913:
1914: -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1915:
2070: ,NULL revenue_layout_code
2071: ,NULL all_layout_code
2072: ,NULL -- Bug 5462471 For cost only version revenue_derivation_method should be null always
2073: INTO x_fp_cols_rec
2074: FROM pa_proj_fp_options
2075: WHERE proj_fp_options_id = p_proj_fp_options_id;
2076:
2077:
2078:
2229: ,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,nvl(revenue_layout_code, all_layout_code ) ,null) revenue_layout_code
2230: ,NULL all_layout_code
2231: ,revenue_derivation_method -- Bug 5462471
2232: INTO x_fp_cols_rec
2233: FROM pa_proj_fp_options
2234: WHERE proj_fp_options_id = p_proj_fp_options_id;
2235:
2236: -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
2237:
2399: ,NULL revenue_layout_code
2400: ,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,nvl(cost_layout_code, nvl(revenue_layout_code ,all_layout_code)) ,null) all_layout_code
2401: ,revenue_derivation_method -- Bug 5462471
2402: INTO x_fp_cols_rec
2403: FROM pa_proj_fp_options
2404: WHERE proj_fp_options_id = p_proj_fp_options_id;
2405:
2406: END IF;
2407:
2435: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436: x_msg_count := 1;
2437: x_msg_data := SQLERRM;
2438: FND_MSG_PUB.add_exc_msg
2439: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_FP_Options'
2440: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2441: IF P_PA_DEBUG_MODE = 'Y' THEN
2442: pa_debug.write('Get_FP_Options: ' || l_module_name,SQLERRM,5);
2443: pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.G_Err_Stack,5);
2457: option id of it's parent (i.e FP Option ID of the Option Level Code PROJECT is returned for
2458: the project_id of the input).
2459: ============================================================================================*/
2460: FUNCTION Get_Parent_FP_Option_ID(
2461: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2462:
2463: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2464: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2465: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2459: ============================================================================================*/
2460: FUNCTION Get_Parent_FP_Option_ID(
2461: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2462:
2463: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2464: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2465: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2466: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2467: l_debug_mode VARCHAR2(30);
2460: FUNCTION Get_Parent_FP_Option_ID(
2461: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2462:
2463: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2464: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2465: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2466: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2467: l_debug_mode VARCHAR2(30);
2468:
2461: p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2462:
2463: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2464: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2465: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2466: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2467: l_debug_mode VARCHAR2(30);
2468:
2469: BEGIN
2462:
2463: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2464: l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2465: l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2466: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2467: l_debug_mode VARCHAR2(30);
2468:
2469: BEGIN
2470: IF P_PA_DEBUG_MODE = 'Y' THEN
2467: l_debug_mode VARCHAR2(30);
2468:
2469: BEGIN
2470: IF P_PA_DEBUG_MODE = 'Y' THEN
2471: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID');
2472: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2473: l_debug_mode := NVL(l_debug_mode, 'Y');
2474: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2475: END IF;
2475: END IF;
2476:
2477: SELECT fin_plan_option_level_code, project_id, fin_plan_type_id
2478: INTO l_fp_option_level_code, l_proj_id, l_fp_type_id
2479: FROM pa_proj_fp_options
2480: WHERE proj_fp_options_id = p_proj_fp_options_id;
2481:
2482: /* To get the Parent Option of a PLAN_VERSION, PLAN_TYPE option for the
2483: Project and Plan Type has to be selected. */
2490: END IF;
2491:
2492: SELECT proj_fp_options_id
2493: INTO x_proj_fp_options_id
2494: FROM pa_proj_fp_options
2495: WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
2496: AND project_id = l_proj_id
2497: AND fin_plan_type_id = l_fp_type_id;
2498:
2506:
2507: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2508: SELECT proj_fp_options_id
2509: INTO x_proj_fp_options_id
2510: FROM pa_proj_fp_options
2511: WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
2512: AND project_id = l_proj_id;
2513:
2514: END IF;
2533: RETURN NULL;
2534:
2535: WHEN OTHERS THEN
2536: FND_MSG_PUB.add_exc_msg
2537: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID'
2538: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2539: IF P_PA_DEBUG_MODE = 'Y' THEN
2540: pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,SQLERRM,5);
2541: pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.G_Err_Stack,5);
2547: /*============================================================================================
2548: GET_FP_OPTION_ID: This procedure returns the Proj FP Option ID based on the input Project_ID,
2549: Plan_Type_ID and the Plan_Version_ID.
2550: The Option_Level_Code is determined using the input parameters. The Proj FP Option ID is then
2551: got from the table PA_Proj_FP_Options by using the appropriate conditions based on the Option
2552: Level Code. (i.e. if the Option Level Code is PROJECT, only the Project_ID is checked for
2553: in the table etc.)
2554: ============================================================================================*/
2555: FUNCTION Get_FP_Option_ID(
2554: ============================================================================================*/
2555: FUNCTION Get_FP_Option_ID(
2556: p_project_id IN NUMBER
2557: ,p_plan_type_id IN NUMBER
2558: ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2559:
2560: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2561: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2562: l_debug_mode VARCHAR2(30);
2556: p_project_id IN NUMBER
2557: ,p_plan_type_id IN NUMBER
2558: ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2559:
2560: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2561: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2562: l_debug_mode VARCHAR2(30);
2563:
2564: BEGIN
2557: ,p_plan_type_id IN NUMBER
2558: ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2559:
2560: l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2561: x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2562: l_debug_mode VARCHAR2(30);
2563:
2564: BEGIN
2565:
2563:
2564: BEGIN
2565:
2566: IF P_PA_DEBUG_MODE = 'Y' THEN
2567: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_FP_Option_ID');
2568: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2569: l_debug_mode := NVL(l_debug_mode, 'Y');
2570: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2571: END IF;
2597: IF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT) THEN
2598:
2599: SELECT proj_fp_options_id
2600: INTO x_proj_fp_options_id
2601: FROM pa_proj_fp_options
2602: WHERE project_id = p_project_id
2603: AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
2604:
2605: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2605: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2606:
2607: SELECT proj_fp_options_id
2608: INTO x_proj_fp_options_id
2609: FROM pa_proj_fp_options
2610: WHERE project_id = p_project_id
2611: AND fin_plan_type_id = p_plan_type_id
2612: AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
2613:
2614: ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
2615:
2616: SELECT proj_fp_options_id
2617: INTO x_proj_fp_options_id
2618: FROM pa_proj_fp_options
2619: WHERE project_id = p_project_id
2620: AND fin_plan_type_id = p_plan_type_id
2621: AND fin_plan_version_id = p_plan_version_id
2622: AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
2642: RETURN NULL;
2643:
2644: WHEN OTHERS THEN
2645: FND_MSG_PUB.add_exc_msg
2646: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_FP_Option_ID'
2647: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2648: IF P_PA_DEBUG_MODE = 'Y' THEN
2649: pa_debug.write('Get_FP_Option_ID: ' || l_module_name,SQLERRM,5);
2650: pa_debug.write('Get_FP_Option_ID: ' || l_module_name,pa_debug.G_Err_Stack,5);
2689: =====================================================================================*/
2690: FUNCTION Get_Default_FP_Options(
2691: p_fin_plan_preference_code IN VARCHAR2 ,
2692: p_target_project_id IN pa_projects_all.project_id%TYPE,
2693: p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2694:
2695: /* Declaring Constants */
2696: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2697: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2692: p_target_project_id IN pa_projects_all.project_id%TYPE,
2693: p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2694:
2695: /* Declaring Constants */
2696: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2697: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2698: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2699:
2700: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2693: p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2694:
2695: /* Declaring Constants */
2696: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2697: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2698: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2699:
2700: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2701:
2694:
2695: /* Declaring Constants */
2696: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2697: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2698: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2699:
2700: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2701:
2702: l_return_status VARCHAR2(2000);
2696: l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2697: l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2698: l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2699:
2700: l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2701:
2702: l_return_status VARCHAR2(2000);
2703: l_msg_count NUMBER := 0;
2704: l_msg_data VARCHAR2(2000);
2708: l_msg_index_out NUMBER := 0;
2709: l_data VARCHAR2(2000);
2710: l_debug_mode VARCHAR2(30);
2711:
2712: l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2713: l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2714: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2715: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2716:
2709: l_data VARCHAR2(2000);
2710: l_debug_mode VARCHAR2(30);
2711:
2712: l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2713: l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2714: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2715: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2716:
2717: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2710: l_debug_mode VARCHAR2(30);
2711:
2712: l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2713: l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2714: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2715: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2716:
2717: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2718: --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2714: l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2715: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2716:
2717: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2718: --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2719: l_multi_curr_flag pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2720: l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2721: l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2722: l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2715: l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2716:
2717: -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2718: --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2719: l_multi_curr_flag pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2720: l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2721: l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2722: l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2723:
2721: l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2722: l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2723:
2724: /* added following local variables as part of changes due to autobaseline */
2725: l_rev_fin_plan_level_code pa_proj_fp_options.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2726: l_autobaseline_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE := 'N';
2727: l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2728: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2729:
2724: /* added following local variables as part of changes due to autobaseline */
2725: l_rev_fin_plan_level_code pa_proj_fp_options.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2726: l_autobaseline_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE := 'N';
2727: l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2728: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2729:
2730: -- FP M Dev Effort Variables used for calling rate schedules util api
2731: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2727: l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2728: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2729:
2730: -- FP M Dev Effort Variables used for calling rate schedules util api
2731: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2733: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2734: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2735: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2728: l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2729:
2730: -- FP M Dev Effort Variables used for calling rate schedules util api
2731: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2733: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2734: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2735: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2736: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2729:
2730: -- FP M Dev Effort Variables used for calling rate schedules util api
2731: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2733: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2734: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2735: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2736: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2737:
2730: -- FP M Dev Effort Variables used for calling rate schedules util api
2731: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2733: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2734: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2735: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2736: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2737:
2738: --Adding the variables to get the default vaules for the seeded webadi layouts
2731: l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2733: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2734: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2735: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2736: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2737:
2738: --Adding the variables to get the default vaules for the seeded webadi layouts
2739: l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2732: l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2733: l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2734: l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2735: l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2736: l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2737:
2738: --Adding the variables to get the default vaules for the seeded webadi layouts
2739: l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2740: l_non_periodic_forecast_layout VARCHAR2(30) := 'NPE_FORECAST';
2739: l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2740: l_non_periodic_forecast_layout VARCHAR2(30) := 'NPE_FORECAST';
2741: l_webadi_profile VARCHAR(1);
2742:
2743: l_revenue_derivation_method pa_proj_fp_options.revenue_derivation_method%TYPE; --Bug 5462471
2744:
2745: CURSOR plan_type_info_cur (c_plan_type_id NUMBER) IS
2746: SELECT plan_class_code
2747: ,nvl(approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
2778:
2779: BEGIN
2780:
2781: IF P_PA_DEBUG_MODE = 'Y' THEN
2782: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_Default_FP_Options');
2783: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2784: l_debug_mode := NVL(l_debug_mode, 'Y');
2785: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2786: END IF;
3184: 'BUDGET','CURRENT_BASELINED',
3185: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3186: INTO l_fp_cols_rec.gen_src_cost_plan_type_id
3187: ,l_fp_cols_rec.gen_src_cost_plan_ver_code -- Bug 3572548
3188: FROM pa_proj_fp_options o
3189: ,pa_fin_plan_types_b pt
3190: WHERE o.project_id = p_target_project_id
3191: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3192: AND o.fin_plan_type_id = pt.fin_plan_type_id
3202: (SELECT pt.fin_plan_type_id
3203: ,DECODE(pt.plan_class_code,
3204: 'BUDGET','CURRENT_BASELINED',
3205: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3206: FROM pa_proj_fp_options o
3207: ,pa_fin_plan_types_vl pt
3208: WHERE o.project_id = p_target_project_id
3209: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3210: AND o.fin_plan_preference_code <> 'REVENUE_ONLY'
3323: 'BUDGET','CURRENT_BASELINED',
3324: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3325: INTO l_fp_cols_rec.gen_src_rev_plan_type_id
3326: ,l_fp_cols_rec.gen_src_rev_plan_ver_code -- Bug 3572548
3327: FROM pa_proj_fp_options o
3328: ,pa_fin_plan_types_b pt
3329: WHERE o.project_id = p_target_project_id
3330: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3331: AND o.fin_plan_type_id = pt.fin_plan_type_id
3342: SELECT pt.fin_plan_type_id
3343: ,DECODE(pt.plan_class_code,
3344: 'BUDGET','CURRENT_BASELINED',
3345: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3346: FROM pa_proj_fp_options o
3347: ,pa_fin_plan_types_vl pt
3348: WHERE o.project_id = p_target_project_id
3349: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3350: AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3418: 'BUDGET','CURRENT_BASELINED',
3419: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3420: INTO l_fp_cols_rec.gen_src_all_plan_type_id
3421: ,l_fp_cols_rec.gen_src_all_plan_ver_code -- Bug 3572548
3422: FROM pa_proj_fp_options o
3423: ,pa_fin_plan_types_b pt
3424: WHERE o.project_id = p_target_project_id
3425: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3426: AND o.fin_plan_type_id = pt.fin_plan_type_id
3437: (SELECT pt.fin_plan_type_id
3438: ,DECODE(pt.plan_class_code,
3439: 'BUDGET','CURRENT_BASELINED',
3440: 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3441: FROM pa_proj_fp_options o
3442: ,pa_fin_plan_types_vl pt
3443: WHERE o.project_id = p_target_project_id
3444: AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3445: AND o.fin_plan_type_id = pt.fin_plan_type_id
3538: END IF;
3539: RAISE;
3540: WHEN OTHERS THEN
3541: FND_MSG_PUB.add_exc_msg
3542: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_Default_FP_Options'
3543: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3544: IF P_PA_DEBUG_MODE = 'Y' THEN
3545: pa_debug.write('Get_Default_FP_Options: ' || l_module_name,SQLERRM,5);
3546: pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.G_Err_Stack,5);
3555: passed along with the approved_cost_plan_type_flag and approved_rev_plan_type_flag
3556: ===========================================================================================*/
3557: FUNCTION Get_FP_Proj_Mc_Options (p_proj_fp_options_id IN NUMBER) Return FP_MC_COLS
3558: IS
3559: l_fp_mc_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
3560: BEGIN
3561:
3562: SELECT approved_cost_plan_type_flag
3563: ,approved_rev_plan_type_flag
3575: ,project_rev_rate_type
3576: ,project_rev_rate_date_type
3577: ,project_rev_rate_date
3578: INTO l_fp_mc_cols_rec
3579: FROM pa_proj_fp_options
3580: WHERE proj_fp_options_id = p_proj_fp_options_id;
3581:
3582: RETURN l_fp_mc_cols_rec;
3583: EXCEPTION
3590: FP Option ID passed along with approved_cost_plan_type_flag and approved_rev_plan_type_flag
3591: ===========================================================================================*/
3592: FUNCTION Get_FP_Plan_Type_Mc_Options (p_fin_plan_type_id IN NUMBER) Return FP_MC_COLS
3593: IS
3594: l_fp_mc_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
3595: BEGIN
3596: SELECT approved_cost_plan_type_flag
3597: ,approved_rev_plan_type_flag
3598: ,primary_cost_forecast_flag
3631: the columns that are part of both pa_budget_versions and also pa_proj_fp_otions.
3632: They are resource_list_id, period_mask_id and current_planning_period.
3633: actual_amts_thru_period is a column present only in budget versions table. So,
3634: only this column is passed and rest of the values should be read from
3635: pa_proj_fp_options table
3636:
3637: Note: In FP M, period profile concept has been changed to period masks. Commenting
3638: all the related existing code
3639: */
3646: )
3647: IS
3648:
3649: l_project_id pa_budget_versions.project_id%TYPE;
3650: l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
3651: l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
3652: /*
3653: l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3654: l_curr_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3647: IS
3648:
3649: l_project_id pa_budget_versions.project_id%TYPE;
3650: l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
3651: l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
3652: /*
3653: l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3654: l_curr_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3655: l_period_type pa_proj_period_profiles.plan_period_type%TYPE;
3683: fin_plan_preference_code,
3684: cost_resource_list_id,
3685: revenue_resource_list_id,
3686: all_resource_list_id
3687: FROM pa_proj_fp_options
3688: WHERE fin_plan_version_id = p_budget_version_id;
3689:
3690: version_option_info_rec version_option_info_cur%ROWTYPE;
3691:
3692: BEGIN
3693:
3694: FND_MSG_PUB.initialize;
3695: IF P_PA_DEBUG_MODE = 'Y' THEN
3696: pa_debug.init_err_stack('PA_PROJ_FP_OPTIONS_PUB.SYNCHRONIZE_BUDGET_VERSION');
3697: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3698: l_debug_mode := NVL(l_debug_mode, 'Y');
3699: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3700: END IF;
3939: WHEN Others THEN
3940: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3941: x_msg_count := 1;
3942: x_msg_data := SQLERRM;
3943: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB'
3944: ,p_procedure_name => 'SYNCHRONIZE_BUDGET_VERSION');
3945: IF P_PA_DEBUG_MODE = 'Y' THEN
3946: pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3947: pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
3960: =====================================================================================*/
3961: FUNCTION Gen_Src_Plan_Version_Id(
3962: p_target_project_id IN pa_projects_all.project_id%TYPE,
3963: p_target_version_type IN pa_budget_versions.version_type%TYPE,
3964: p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
3965: p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
3966: RETURN pa_budget_versions.budget_version_id%TYPE is
3967:
3968: l_return_status VARCHAR2(2000);
3961: FUNCTION Gen_Src_Plan_Version_Id(
3962: p_target_project_id IN pa_projects_all.project_id%TYPE,
3963: p_target_version_type IN pa_budget_versions.version_type%TYPE,
3964: p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
3965: p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
3966: RETURN pa_budget_versions.budget_version_id%TYPE is
3967:
3968: l_return_status VARCHAR2(2000);
3969: l_msg_count NUMBER := 0;
3974: l_msg_index_out NUMBER := 0;
3975: l_data VARCHAR2(2000);
3976: l_debug_mode VARCHAR2(30);
3977:
3978: l_dummy_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3979: l_gen_src_plan_version_id pa_budget_versions.budget_version_id%TYPE;
3980:
3981: CURSOR src_plan_type_info_cur (c_fin_plan_type_id NUMBER, c_project_id NUMBER) IS
3982: SELECT fin_plan_preference_code
3979: l_gen_src_plan_version_id pa_budget_versions.budget_version_id%TYPE;
3980:
3981: CURSOR src_plan_type_info_cur (c_fin_plan_type_id NUMBER, c_project_id NUMBER) IS
3982: SELECT fin_plan_preference_code
3983: FROM pa_proj_fp_options
3984: WHERE project_id = c_project_id
3985: AND fin_plan_type_id = c_fin_plan_type_id
3986: AND fin_plan_option_level_code = 'PLAN_TYPE';
3987:
3989:
3990: BEGIN
3991:
3992: IF P_PA_DEBUG_MODE = 'Y' THEN
3993: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Gen_Src_Plan_Version_Id');
3994: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3995: l_debug_mode := NVL(l_debug_mode, 'Y');
3996: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3997: END IF;
4187: END IF;
4188: RAISE;
4189: WHEN OTHERS THEN
4190: FND_MSG_PUB.add_exc_msg
4191: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Gen_Src_Plan_Version_Id'
4192: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4193: IF P_PA_DEBUG_MODE = 'Y' THEN
4194: pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,SQLERRM,5);
4195: pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.G_Err_Stack,5);
4206: Initial Creation
4207: =====================================================================================*/
4208: FUNCTION Gen_Src_WP_Version_Id(
4209: p_target_project_id IN pa_projects_all.project_id%TYPE,
4210: p_gen_src_wp_ver_code IN pa_proj_fp_options.gen_src_cost_wp_ver_code%TYPE)
4211: RETURN pa_budget_versions.budget_version_id%TYPE is
4212:
4213: l_return_status VARCHAR2(2000);
4214: l_msg_count NUMBER := 0;
4313: pa_debug.reset_curr_function;
4314: RAISE;
4315: WHEN OTHERS THEN
4316: FND_MSG_PUB.add_exc_msg
4317: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Gen_Src_WP_Version_Id'
4318: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4319: IF P_PA_DEBUG_MODE = 'Y' THEN
4320: pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,SQLERRM,5);
4321: pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,pa_debug.G_Err_Stack,5);
4336:
4337: PROCEDURE Create_amt_types (
4338: p_project_id IN pa_projects_all.project_id%TYPE
4339: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4340: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4341: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4342: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4343: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4344: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4337: PROCEDURE Create_amt_types (
4338: p_project_id IN pa_projects_all.project_id%TYPE
4339: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4340: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4341: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4342: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4343: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4344: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4345: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4338: p_project_id IN pa_projects_all.project_id%TYPE
4339: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4340: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4341: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4342: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4343: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4344: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4345: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4346: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4339: ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4340: ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4341: ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4342: ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4343: ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4344: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4345: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4346: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4347: ) IS
4354: l_revenue_layout_type_code VARCHAR2(30);
4355: l_all_layout_type_code VARCHAR2(30);
4356: l_debug_mode VARCHAR2(30);
4357: l_stage NUMBER := 100;
4358: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
4359: P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4360: l_msg_count NUMBER :=0;
4361: l_msg_data VARCHAR2(2000);
4362: l_data VARCHAR2(2000);
4382: PA_DEBUG.Set_Curr_Function( p_function => l_module_name,
4383: p_debug_mode => l_debug_mode );
4384:
4385: IF P_PA_DEBUG_MODE = 'Y' THEN
4386: pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Create_amt_types');
4387: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4388: l_debug_mode := NVL(l_debug_mode, 'Y');
4389: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4390: END IF;
4776: RAISE;
4777:
4778: WHEN OTHERS THEN
4779: FND_MSG_PUB.add_exc_msg
4780: ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB' ||
4781: 'Create_amt_types'
4782: ,p_procedure_name => PA_DEBUG.G_Err_Stack);
4783: pa_debug.g_err_stage := 'Unexpected error in Create_amt_types:';
4784: pa_debug.write('Create_amt_types: ' || l_module_name,pa_debug.g_err_stage,5);
4807: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4808: ) IS
4809:
4810: l_stage NUMBER := 100;
4811: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub.copy_amt_types';
4812: P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4813: --Start of variables used for debugging
4814: l_return_status VARCHAR2(1);
4815: l_msg_count NUMBER := 0;
4911: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4912: x_msg_count := 1;
4913: x_msg_data := SQLERRM;
4914:
4915: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_proj_fp_options_pub'
4916: ,p_procedure_name => 'copy_amt_types');
4917:
4918: IF l_debug_mode = 'Y' THEN
4919: pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4939: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4940: ) IS
4941:
4942: l_stage NUMBER := 100;
4943: l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
4944: P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4945: --Start of variables used for debugging
4946: l_return_status VARCHAR2(1);
4947: l_msg_count NUMBER := 0;
5159: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5160: x_msg_count := 1;
5161: x_msg_data := SQLERRM;
5162:
5163: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_proj_fp_options_pub'
5164: ,p_procedure_name => 'update_amt_types');
5165:
5166: IF l_debug_mode = 'Y' THEN
5167: pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
5172: RAISE;
5173:
5174: END update_amt_types;
5175:
5176: END PA_PROJ_FP_OPTIONS_PUB;