1 PACKAGE pa_fin_plan_utils AUTHID CURRENT_USER AS
2 /* $Header: PAFPUTLS.pls 120.5.12010000.4 2009/06/18 09:16:05 gboomina ship $
3 Start of Comments
4 Package name : PA_FIN_PLAN_UTILS
5 Purpose : utility API's for Org Forecast pages
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 Check_Locked_By_User_Exception EXCEPTION;
12
13 /*
14 Declaration of global variables to be used in webadi - Currently these
15 globals are used in the procedure VALIDATE_CURRENCY_ATTRIBUTES. In the
16 beginning of the procedure these globals are set to null and then populated
17 down the line depending on the error.
18 */
19 g_first_error_code varchar2(30);
20 g_pc_pfc_context PA_LOOKUPS.LOOKUP_CODE%TYPE;
21 g_cost_rev_context varchar2(30);
22
23
24 function get_lookup_value
25 (p_lookup_type pa_lookups.lookup_type%TYPE,
26 p_lookup_code pa_lookups.lookup_code%TYPE) return VARCHAR2;
27
28 procedure Check_Record_Version_Number
29 (p_unique_index IN NUMBER,
30 p_record_version_number IN NUMBER,
31 x_valid_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
33 x_error_msg_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
34
35 function Retrieve_Record_Version_Number
36 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
37 return number;
38
39 FUNCTION Plan_Amount_Exists
40 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
41 RETURN VARCHAR2;
42 --PRAGMA RESTRICT_REFERENCES ( Plan_Amount_Exists, WNDS, WNPS);
43
44 /*
45 API Name : Plan_Amount_Exists_Task_Res
46 API Description : Return 'Y' if at least one record exists in Resource Assignments (pa_resource_assignments)
47 for the given Budget Version Id, Task Id, Resource List Member Id
48 API Created By : Vthakkar
49 API Creation Date : 15-MAR-2004
50 */
51
52 FUNCTION Plan_Amount_Exists_Task_Res
53 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE ,
54 p_task_id IN pa_tasks.task_id%TYPE Default Null,
55 p_resource_list_member_id IN pa_resource_list_members.RESOURCE_LIST_MEMBER_ID%TYPE Default Null
56 ) RETURN VARCHAR2;
57
58
59
60 FUNCTION Get_Resource_List_Id (
61 p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id %TYPE )
62 RETURN pa_proj_fp_options.all_resource_list_id%TYPE;
63 --PRAGMA RESTRICT_REFERENCES ( Get_Resource_List_Id, WNDS, WNPS);
64
65 FUNCTION Get_Time_Phased_code (
66 p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id %TYPE )
67 RETURN pa_proj_fp_options.all_time_phased_code%TYPE;
68 --PRAGMA RESTRICT_REFERENCES ( Get_Time_Phased_code , WNDS, WNPS);
69
70 FUNCTION Get_Multi_Curr_Flag(
71 p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id %TYPE )
72 RETURN pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
73
74 FUNCTION Get_Fin_Plan_Level_Code(
75 p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id %TYPE )
76 RETURN pa_proj_fp_options.all_fin_plan_level_code%TYPE;
77 --PRAGMA RESTRICT_REFERENCES ( Get_Fin_Plan_Level_Code, WNDS, WNPS);
78
79 FUNCTION Get_Amount_Set_Id(
80 p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id %TYPE )
81 RETURN pa_proj_fp_options.all_amount_set_id%TYPE;
82 --PRAGMA RESTRICT_REFERENCES ( Get_Amount_Set_Id, WNDS, WNPS);
83
84 FUNCTION Get_Period_Profile_Start_Date
85 (p_period_profile_id IN pa_budget_versions.period_profile_id%TYPE)
86 return pa_proj_period_profiles.period_name1%TYPE;
87
88 FUNCTION Get_Period_Profile_End_Date
89 (p_period_profile_id IN pa_budget_versions.period_profile_id%TYPE)
90 return pa_proj_period_profiles.profile_end_period_name%TYPE;
91
92 /* This fuction will return workplan budget version res_list_id */
93 FUNCTION Get_wp_bv_res_list_id
94 ( p_proj_structure_version_id NUMBER)
95 RETURN NUMBER ;
96
97 /*=============================================================================
98 This function will return the time phase code
99 of the budget_version_id for a given wp_structure_version_id.
100 P->PA, G->Gl, N->None
101 ==============================================================================*/
102 FUNCTION Get_wp_bv_time_phase
103 (p_wp_structure_version_id IN PA_BUDGET_VERSIONS.PROJECT_STRUCTURE_VERSION_ID%TYPE)
104 RETURN VARCHAR2;
105
106 /*=============================================================================
107 This function will return the approved cost budget current baselined version.
108 If version is not available then it will return null value.
109 ==============================================================================*/
110 FUNCTION Get_app_budget_cost_cb_ver
111 (p_project_id IN pa_projects_all.project_id%TYPE)
112 RETURN NUMBER;
113
114
115 PROCEDURE Get_Appr_Cost_Plan_Type_Info(
116 p_project_id IN pa_projects_all.project_id%TYPE
117 ,x_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
118 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
119 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
120 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
121
122 PROCEDURE Get_Appr_Rev_Plan_Type_Info(
123 p_project_id IN pa_projects_all.project_id%TYPE
124 ,x_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
125 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
126 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
127 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
128
129 PROCEDURE Get_Baselined_Version_Info(
130 p_project_id IN pa_projects_all.project_id%TYPE
131 ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
132 ,p_version_type IN pa_budget_versions.version_type%TYPE
133 ,x_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE --File.Sql.39 bug 4440895
134 ,x_fin_plan_version_id OUT NOCOPY pa_proj_fp_options.fin_plan_version_id%TYPE --File.Sql.39 bug 4440895
135 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
136 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
137 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
138
139
140 /*bug 3224177 start*/
141 -- Refer to Update "16-JAN-04 sagarwal" in the history above.
142 -- This has been added as part of code merge
143 PROCEDURE Delete_Fp_Options
144 (p_project_id IN PA_FP_TXN_CURRENCIES.PROJECT_ID%TYPE
145 ,x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
146
147
148 PROCEDURE Update_Txn_Currencies
149 ( p_project_id IN PA_FP_TXN_CURRENCIES.PROJECT_ID%TYPE
150 ,p_proj_curr_code IN PA_FP_TXN_CURRENCIES.TXN_CURRENCY_CODE%TYPE);
151 /*bug 3224177 end*/
152
153
154
155 PROCEDURE Get_Curr_Working_Version_Info(
156 p_project_id IN pa_projects_all.project_id%TYPE
157 ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
158 ,p_version_type IN pa_budget_versions.version_type%TYPE
159 ,x_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE --File.Sql.39 bug 4440895
160 ,x_fin_plan_version_id OUT NOCOPY pa_proj_fp_options.fin_plan_version_id%TYPE --File.Sql.39 bug 4440895
161 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
162 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
163 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
164
165 PROCEDURE GET_OR_CREATE_AMOUNT_SET_ID
166 (
167 p_raw_cost_flag IN pa_fin_plan_amount_sets.raw_cost_flag%TYPE,
168 p_burdened_cost_flag IN pa_fin_plan_amount_sets.burdened_cost_flag%TYPE,
169 p_revenue_flag IN pa_fin_plan_amount_sets.revenue_flag%TYPE,
170 p_cost_qty_flag IN pa_fin_plan_amount_sets.cost_qty_flag%TYPE,
171 p_revenue_qty_flag IN pa_fin_plan_amount_sets.revenue_qty_flag%TYPE,
172 p_all_qty_flag IN pa_fin_plan_amount_sets.all_qty_flag%TYPE,
173 p_plan_pref_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE,
174 /* Changes for FP.M, Tracking Bug No - 3354518
175 Adding three new IN parameters p_bill_rate_flag,
176 p_cost_rate_flag, p_burden_rate below for
177 new columns in pa_fin_plan_amount_sets */
178 p_bill_rate_flag IN pa_fin_plan_amount_sets.bill_rate_flag%TYPE,
179 p_cost_rate_flag IN pa_fin_plan_amount_sets.cost_rate_flag%TYPE,
180 p_burden_rate_flag IN pa_fin_plan_amount_sets.burden_rate_flag%TYPE,
181 x_cost_amount_set_id OUT NOCOPY pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE, --File.Sql.39 bug 4440895
182 x_revenue_amount_set_id OUT NOCOPY pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE, --File.Sql.39 bug 4440895
183 x_all_amount_set_id OUT NOCOPY pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE, --File.Sql.39 bug 4440895
184 x_message_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
185 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
186 x_message_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
187 );
188
189 PROCEDURE GET_PLAN_AMOUNT_FLAGS(
190 P_AMOUNT_SET_ID IN PA_FIN_PLAN_AMOUNT_SETS.fin_plan_amount_set_id%TYPE,
191 X_RAW_COST_FLAG OUT NOCOPY PA_FIN_PLAN_AMOUNT_SETS.raw_cost_flag%TYPE, --File.Sql.39 bug 4440895
192 X_BURDENED_FLAG OUT NOCOPY PA_FIN_PLAN_AMOUNT_SETS.burdened_cost_flag%TYPE, --File.Sql.39 bug 4440895
193 X_REVENUE_FLAG OUT NOCOPY PA_FIN_PLAN_AMOUNT_SETS.revenue_flag%TYPE, --File.Sql.39 bug 4440895
194 X_COST_QUANTITY_FLAG OUT NOCOPY PA_FIN_PLAN_AMOUNT_SETS.cost_qty_flag%TYPE, --File.Sql.39 bug 4440895
195 X_REV_QUANTITY_FLAG OUT NOCOPY PA_FIN_PLAN_AMOUNT_SETS.revenue_qty_flag%TYPE, --File.Sql.39 bug 4440895
196 X_ALL_QUANTITY_FLAG OUT NOCOPY PA_FIN_PLAN_AMOUNT_SETS.all_qty_flag%TYPE, --File.Sql.39 bug 4440895
197 /* Changes for FP.M, Tracking Bug No - 3354518
198 Adding three new OUT parameters x_bill_rate_flag,
199 x_cost_rate_flag, x_burden_rate below for
200 new columns in pa_fin_plan_amount_sets */
201 X_BILL_RATE_FLAG OUT NOCOPY pa_fin_plan_amount_sets.bill_rate_flag%TYPE, --File.Sql.39 bug 4440895
202 X_COST_RATE_FLAG OUT NOCOPY pa_fin_plan_amount_sets.cost_rate_flag%TYPE, --File.Sql.39 bug 4440895
203 X_BURDEN_RATE_FLAG OUT NOCOPY pa_fin_plan_amount_sets.burden_rate_flag%TYPE, --File.Sql.39 bug 4440895
204 x_message_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
205 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
206 x_message_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
207
208 FUNCTION is_orgforecast_plan
209 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
210 return VARCHAR2;
211
212
213 /* Changes for FP.M, Tracking Bug No - 3354518
214 Modifying the type of the IN parameter p_element_type
215 below as pa_fp_elements is being obsoleted. */
216 /*
217 FUNCTION GET_OPTION_PLANNING_LEVEL(
218 P_PROJ_FP_OPTIONS_ID IN pa_proj_fp_options.proj_fp_options_id%TYPE,
219 P_ELEMENT_TYPE IN pa_fp_elements.element_type%TYPE)
220 RETURN pa_proj_fp_options.all_fin_plan_level_code%TYPE;
221 */
222 FUNCTION GET_OPTION_PLANNING_LEVEL(
223 P_PROJ_FP_OPTIONS_ID IN pa_proj_fp_options.proj_fp_options_id%TYPE,
224 P_ELEMENT_TYPE IN pa_budget_versions.version_type%TYPE)
225 RETURN pa_proj_fp_options.all_fin_plan_level_code%TYPE;
226
227
228 FUNCTION get_person_name
229 (p_person_id IN NUMBER) return VARCHAR2;
230
231 /*This is due to bug 2607945*/
232 FUNCTION is_plan_type_addition_allowed
233 (p_project_id IN pa_projects_all.project_id%TYPE
234 ,P_FIN_PLAN_TYPE_ID IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
235 ) RETURN VARCHAR2 ;
236
237
238 PROCEDURE Get_Peceding_Suceeding_Pd_Info
239 ( p_resource_assignment_id IN pa_budget_lines.RESOURCE_ASSIGNMENT_ID%TYPE
240 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
241 ,x_preceding_prd_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
242 ,x_preceding_prd_end_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
243 ,x_succeeding_prd_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
244 ,x_succeeding_prd_end_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
245 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
246 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
247 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
248 ) ;
249
250 PROCEDURE Get_Element_Proj_PF_Amounts
251 (
252 p_resource_assignment_id IN pa_budget_lines.RESOURCE_ASSIGNMENT_ID%TYPE
253 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
254 ,x_quantity OUT NOCOPY pa_budget_lines.QUANTITY%TYPE --File.Sql.39 bug 4440895
255 ,x_project_raw_cost OUT NOCOPY pa_budget_lines.TXN_RAW_COST%TYPE --File.Sql.39 bug 4440895
256 ,x_project_burdened_cost OUT NOCOPY pa_budget_lines.TXN_BURDENED_COST%TYPE --File.Sql.39 bug 4440895
257 ,x_project_revenue OUT NOCOPY pa_budget_lines.TXN_REVENUE%TYPE --File.Sql.39 bug 4440895
258 ,x_projfunc_raw_cost OUT NOCOPY pa_budget_lines.TXN_RAW_COST%TYPE --File.Sql.39 bug 4440895
259 ,x_projfunc_burdened_cost OUT NOCOPY pa_budget_lines.TXN_BURDENED_COST%TYPE --File.Sql.39 bug 4440895
260 ,x_projfunc_revenue OUT NOCOPY pa_budget_lines.TXN_REVENUE%TYPE --File.Sql.39 bug 4440895
261 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
262 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
263 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
264 );
265
266 PROCEDURE Check_Version_Name_Or_id
267 (
268 p_budget_version_id IN pa_budget_versions.BUDGET_VERSION_ID%TYPE
269 ,p_project_id IN pa_budget_versions.project_id%TYPE -- Bug 2770562
270 ,p_version_name IN pa_budget_versions.VERSION_NAME%TYPE
271 ,p_check_id_flag IN VARCHAR2
272 ,x_budget_version_id OUT NOCOPY pa_budget_versions.BUDGET_VERSION_ID%TYPE --File.Sql.39 bug 4440895
273 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
274 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
275 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
276 );
277
278 PROCEDURE Check_Currency_Name_Or_Code
279 (
280 p_txn_currency_code IN pa_fp_txn_currencies.txn_currency_code%TYPE
281 ,p_currency_code_name IN VARCHAR2
282 ,p_check_id_flag IN VARCHAR2
283 ,x_txn_currency_code OUT NOCOPY pa_fp_txn_currencies.txn_currency_code%TYPE --File.Sql.39 bug 4440895
284 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
285 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
286 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
287 ) ;
288
289
290 /* Changes for FP.M, Tracking Bug No - 3354518
291 Replacing all references of PA_TASKS by PA_STRUCT_TASK_WBS_V*/
292 /* Commenting code below for FP.M changes, Tracking Bug No - 3354518 */
293 /*PROCEDURE check_task_name_or_id
294 (p_project_id IN pa_tasks.project_id%TYPE,
295 p_task_id IN pa_tasks.task_id%TYPE,
296 p_task_name IN pa_tasks.task_name%TYPE,
297 p_check_id_flag IN VARCHAR2,
298 x_task_id OUT pa_tasks.task_id%TYPE,
299 x_return_status OUT VARCHAR2,
300 x_msg_count OUT NUMBER,
301 x_error_msg OUT VARCHAR2);*/
302 /* Rewriting procedure declaration below to refer to pa_struct_task_wbs_v
303 instead of pa_tasks - as part of worplan structure model changes in FP.M */
304 PROCEDURE check_task_name_or_id
305 (p_project_id IN PA_STRUCT_TASK_WBS_V.project_id%TYPE,
306 p_task_id IN PA_STRUCT_TASK_WBS_V.task_id%TYPE,
307 p_task_name IN PA_STRUCT_TASK_WBS_V.task_name%TYPE,
308 p_check_id_flag IN VARCHAR2,
309 x_task_id OUT NOCOPY PA_STRUCT_TASK_WBS_V.task_id%TYPE, --File.Sql.39 bug 4440895
310 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
311 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
312 x_error_msg OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
313
314 /* Changes for FP.M, Tracking Bug No - 3354518
315 The procedure check_resource_gp_name_or_id is being obsoleted as the
316 concept of Resource group is no longer there in case of the New dev
317 model of FP.M. However we are adding code in the procedure to raise
318 a exception unconditionally for tracking/debuging purposes at the moment.
319 Basically to note any calls made to this procedure. Eventually we shall be
320 commenting out this procedure because of its nonusage. */
321 PROCEDURE check_resource_gp_name_or_id
322 (p_resource_id IN pa_resources.resource_id%TYPE,
323 p_resource_name IN pa_resources.name%TYPE,
324 p_check_id_flag IN VARCHAR2,
325 x_resource_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
326 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
327 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
328 x_error_msg OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
329
330 PROCEDURE check_resource_name_or_id
331 (p_resource_id IN pa_resources.resource_id%TYPE,
332 p_resource_name IN pa_resources.name%TYPE,
333 p_check_id_flag IN VARCHAR2,
334 x_resource_id OUT NOCOPY pa_resources.resource_id%TYPE, --File.Sql.39 bug 4440895
335 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
336 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
337 x_error_msg OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
338
339 FUNCTION check_proj_fp_options_exists
340 (p_project_id IN pa_proj_fp_options.project_id%TYPE)
341 return NUMBER;
342
343 FUNCTION get_amttype_id
344 ( p_amt_typ_code IN pa_amount_types_b.amount_type_code%TYPE) RETURN NUMBER;
345
346 PROCEDURE Check_Locked_By_User
347 (p_user_id IN NUMBER,
348 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
349 x_is_locked_by_userid OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
350 x_locked_by_person_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
351 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
352 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
353 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
354
355 PROCEDURE Check_Both_Locked_By_User
356 (p_user_id IN NUMBER,
357 p_budget_version_id1 IN pa_budget_versions.budget_version_id%TYPE,
358 p_budget_version_id2 IN pa_budget_versions.budget_version_id%TYPE,
359 x_is_locked_by_userid OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
360 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
361 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
362 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
363
364 FUNCTION check_budget_trans_exists
365 (p_project_id IN pa_projects_all.project_id%TYPE)
366 return VARCHAR2;
367
368 FUNCTION enable_auto_baseline
369 (p_project_id IN pa_projects_all.project_id%TYPE)
370 return VARCHAR2;
371
372 PROCEDURE Get_Resource_List_Info
373 (p_resource_list_id IN pa_resource_lists.RESOURCE_LIST_ID%TYPE
374 ,x_res_list_is_uncategorized OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
375 ,x_is_resource_list_grouped OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
376 ,x_group_resource_type_id OUT NOCOPY pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE --File.Sql.39 bug 4440895
377 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
378 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
379 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
380
381
382 /* Changes for FPM, Tracking Bug - 3354518
383 Adding Procedure Get_Resource_List_Info below.
384 Please note that this proceedure is a overloaded procedure.
385 The reason behind overloading this procedure below is the
386 is the addiditon of three fields use_for_wp_flag,control_flag
387 and migration_code to pa_resource_lists_all_bg */
388 PROCEDURE Get_Resource_List_Info
389 (p_resource_list_id IN pa_resource_lists.RESOURCE_LIST_ID%TYPE
390 ,x_res_list_is_uncategorized OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
391 ,x_is_resource_list_grouped OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
392 ,x_group_resource_type_id OUT NOCOPY pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE --File.Sql.39 bug 4440895
393 ,x_use_for_wp_flag OUT NOCOPY pa_resource_lists_all_bg.use_for_wp_flag%TYPE /*New Column added for FPM */ --File.Sql.39 bug 4440895
394 ,x_control_flag OUT NOCOPY pa_resource_lists_all_bg.control_flag%TYPE /*New Column added for FPM */ --File.Sql.39 bug 4440895
395 ,x_migration_code OUT NOCOPY pa_resource_lists_all_bg.migration_code%TYPE /*New Column added for FPM */ --File.Sql.39 bug 4440895
396 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
397 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
398 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
399
400
401 PROCEDURE Get_Uncat_Resource_List_Info
402 (x_resource_list_id OUT NOCOPY pa_resource_lists.RESOURCE_LIST_ID%TYPE --File.Sql.39 bug 4440895
403 ,x_resource_list_member_id OUT NOCOPY pa_resource_list_members.RESOURCE_LIST_MEMBER_ID%TYPE --File.Sql.39 bug 4440895
404 ,x_track_as_labor_flag OUT NOCOPY pa_resource_list_members.TRACK_AS_LABOR_FLAG%TYPE --File.Sql.39 bug 4440895
405 ,x_unit_of_measure OUT NOCOPY pa_resources.UNIT_OF_MEASURE%TYPE --File.Sql.39 bug 4440895
406 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
407 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
408 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
409
410 PROCEDURE Is_AC_PT_Attached_After_UPG(
411 p_project_id IN pa_projects_all.project_id%TYPE
412 ,x_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
413 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
414 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
415 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
416
417 PROCEDURE Is_AR_PT_Attached_After_UPG(
418 p_project_id IN pa_projects_all.project_id%TYPE
419 ,x_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
420 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
421 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
422 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
423
424 PROCEDURE Get_Max_Budget_Version_Number
425 (p_project_id IN pa_budget_versions.project_id%TYPE
426 ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
427 ,p_version_type IN pa_budget_versions.version_type%TYPE
428 ,p_copy_mode IN VARCHAR2
429 ,p_ci_id IN NUMBER
430 ,p_lock_required_flag IN VARCHAR2
431 ,x_version_number OUT NOCOPY pa_budget_versions.version_number%TYPE --File.Sql.39 bug 4440895
432 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
433 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
434 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
435
436 FUNCTION get_period_start_date (p_input_date IN pa_periods_all.start_date%TYPE,
437 p_time_phased_code IN pa_proj_fp_options.cost_time_phased_Code%TYPE) RETURN DATE;
438
439 FUNCTION get_period_end_date (p_input_date IN pa_periods_all.end_date%TYPE,
440 p_time_phased_code IN pa_proj_fp_options.cost_time_phased_Code%TYPE) RETURN DATE;
441 /*==============================================================================
442 This api returns the current baselined version for a given project and
443 budget_type_code or fin_plan_type combination.
444 1)If the plan type is COST_AND_REV_SAME, then it returns 'ALL' version
445 2)If it is REVENUE_ONLY or COST_AND_REV_SEP then it returns 'REVENUE' version
446 ===============================================================================*/
447
448 PROCEDURE GET_REV_BASE_VERSION_INFO
449 ( p_project_id IN pa_budget_versions.project_id%TYPE
450 ,p_fin_plan_Type_id IN pa_budget_versions.fin_plan_type_id%TYPE
451 ,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
452 ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
453 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
454 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
455 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
456
457 /*==============================================================================
458 This api returns the Current Baselined Version for a given project and
459 budget_type_code or fin_plan_type combination.
460
461 1)If the plan type is COST_AND_REV_SAME, then it returns 'ALL' version
462 2)If it is COST_ONLY or COST_AND_REV_SEP then it returns 'COST' version
463 ===============================================================================*/
464
465 PROCEDURE GET_COST_BASE_VERSION_INFO
466 ( p_project_id IN pa_budget_versions.project_id%TYPE
467 ,p_fin_plan_Type_id IN pa_budget_versions.fin_plan_type_id%TYPE
468 ,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
469 ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
470 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
471 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
472 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
473
474
475 FUNCTION ACQUIRE_USER_LOCK
476 ( X_LOCK_NAME IN VARCHAR2 )
477 RETURN NUMBER ;
478
479 FUNCTION RELEASE_USER_LOCK
480 ( X_LOCK_NAME IN VARCHAR2 )
481 RETURN NUMBER ;
482
483 PROCEDURE get_converted_amounts
484 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
485 ,p_txn_raw_cost IN pa_budget_versions.est_project_raw_cost%TYPE
486 ,p_txn_burdened_cost IN pa_budget_versions.est_project_burdened_cost%TYPE
487 ,p_txn_revenue IN pa_budget_versions.est_project_revenue%TYPE
488 ,p_txn_currency_Code IN pa_projects_all.project_currency_code%TYPE
489 ,p_project_currency_code IN pa_projects_all.project_currency_code%TYPE
490 ,p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE
491 ,x_project_raw_cost OUT NOCOPY pa_budget_versions.est_projfunc_raw_cost%TYPE --File.Sql.39 bug 4440895
492 ,x_project_burdened_cost OUT NOCOPY pa_budget_versions.est_projfunc_burdened_cost%TYPE --File.Sql.39 bug 4440895
493 ,x_project_revenue OUT NOCOPY pa_budget_versions.est_projfunc_revenue%TYPE --File.Sql.39 bug 4440895
494 ,x_projfunc_raw_cost OUT NOCOPY pa_budget_versions.est_projfunc_raw_cost%TYPE --File.Sql.39 bug 4440895
495 ,x_projfunc_burdened_cost OUT NOCOPY pa_budget_versions.est_projfunc_burdened_cost%TYPE --File.Sql.39 bug 4440895
496 ,x_projfunc_revenue OUT NOCOPY pa_budget_versions.est_projfunc_revenue%TYPE --File.Sql.39 bug 4440895
497 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
498 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
499 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
500
501 FUNCTION check_proj_fin_plan_exists (x_project_id IN NUMBER,
502 x_budget_version_id IN NUMBER,
503 x_budget_status_code IN VARCHAR2,
504 x_plan_type_code IN VARCHAR2,
505 x_fin_plan_type_id IN NUMBER,
506 x_version_type IN VARCHAR2
507 ) RETURN NUMBER;
508
509 FUNCTION check_task_fin_plan_exists (x_task_id IN NUMBER,
510 x_budget_version_id IN NUMBER,
511 x_budget_status_code IN VARCHAR2,
512 x_plan_type_code IN VARCHAR2,
513 x_fin_plan_type_id IN NUMBER,
514 x_version_type IN VARCHAR2
515 ) RETURN NUMBER;
516
517 /* Changes for FPM. Tracking Bug - 3354518
518 Modifying the datatype of parameter p_plan_period_type below to varchar2
519 and x_shifted_period_start_date and x_shifted_period_end_date as date*/
520 PROCEDURE Get_Period_Details
521 ( p_period_name IN pa_periods.period_name%TYPE
522 /* ,p_plan_period_type IN pa_proj_period_profiles.plan_period_type%TYPE */
523 ,p_plan_period_type IN VARCHAR2
524 ,x_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
525 ,x_end_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
526 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
527 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
528 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
529
530 /* Changes for FPM. Tracking Bug - 3354518
531 Modifying the datatype of parameter p_plan_period_type below to varchar2
532 and x_shifted_period_start_date and x_shifted_period_end_date as date*/
533 PROCEDURE Get_Shifted_Period (
534 p_period_name IN pa_periods.period_name%TYPE
535 /* ,p_plan_period_type IN pa_proj_period_profiles.plan_period_type%TYPE */
536 ,p_plan_period_type IN VARCHAR2
537 ,p_number_of_periods IN NUMBER
538 ,x_shifted_period OUT NOCOPY pa_periods.period_name%TYPE --File.Sql.39 bug 4440895
539 /* ,x_shifted_period_start_date OUT pa_proj_period_profiles.period1_start_date%TYPE
540 ,x_shifted_period_end_date OUT pa_proj_period_profiles.period1_end_date%TYPE */
541 ,x_shifted_period_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
542 ,x_shifted_period_end_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
543 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
544 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
545 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
546
547
548 FUNCTION Get_Approved_Budget_Ver_Qty (
549 p_project_id IN NUMBER
550 ,p_version_code IN VARCHAR2
551 ,p_quantity_type IN VARCHAR2
552 ,p_ci_id IN NUMBER DEFAULT NULL) RETURN pa_budget_lines.quantity%TYPE;
553
554 PROCEDURE VALIDATE_CURRENCY_ATTRIBUTES
555 ( px_project_cost_rate_type IN OUT NOCOPY pa_proj_fp_options.project_cost_rate_type%TYPE --File.Sql.39 bug 4440895
556 ,px_project_cost_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.project_cost_rate_date_type%TYPE --File.Sql.39 bug 4440895
557 ,px_project_cost_rate_date IN OUT NOCOPY pa_proj_fp_options.project_cost_rate_date%TYPE --File.Sql.39 bug 4440895
558 ,px_project_cost_exchange_rate IN OUT NOCOPY pa_budget_lines.project_cost_exchange_rate%TYPE --File.Sql.39 bug 4440895
559 ,px_projfunc_cost_rate_type IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_type%TYPE --File.Sql.39 bug 4440895
560 ,px_projfunc_cost_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE --File.Sql.39 bug 4440895
561 ,px_projfunc_cost_rate_date IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_date%TYPE --File.Sql.39 bug 4440895
562 ,px_projfunc_cost_exchange_rate IN OUT NOCOPY pa_budget_lines.projfunc_cost_exchange_rate%TYPE --File.Sql.39 bug 4440895
563 ,px_project_rev_rate_type IN OUT NOCOPY pa_proj_fp_options.project_rev_rate_type%TYPE --File.Sql.39 bug 4440895
564 ,px_project_rev_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.project_rev_rate_date_type%TYPE --File.Sql.39 bug 4440895
565 ,px_project_rev_rate_date IN OUT NOCOPY pa_proj_fp_options.project_rev_rate_date%TYPE --File.Sql.39 bug 4440895
566 ,px_project_rev_exchange_rate IN OUT NOCOPY pa_budget_lines.project_rev_exchange_rate%TYPE --File.Sql.39 bug 4440895
567 ,px_projfunc_rev_rate_type IN OUT NOCOPY pa_proj_fp_options.projfunc_rev_rate_type%TYPE --File.Sql.39 bug 4440895
568 ,px_projfunc_rev_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.projfunc_rev_rate_date_type%TYPE --File.Sql.39 bug 4440895
569 ,px_projfunc_rev_rate_date IN OUT NOCOPY pa_proj_fp_options.projfunc_rev_rate_date%TYPE --File.Sql.39 bug 4440895
570 ,px_projfunc_rev_exchange_rate IN OUT NOCOPY pa_budget_lines.projfunc_rev_exchange_rate%TYPE --File.Sql.39 bug 4440895
571 ,p_project_currency_code IN pa_projects_all.project_currency_code%TYPE
572 ,p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE
573 ,p_txn_currency_code IN pa_projects_all.projfunc_currency_code%TYPE DEFAULT NULL
574 ,p_context IN VARCHAR2
575 ,p_attrs_to_be_validated IN VARCHAR2 -- valid values are COST, REVENUE , BOTH
576 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
577 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
578 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
579
580 PROCEDURE VALIDATE_CONV_ATTRIBUTES
581 ( px_rate_type IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_type%TYPE --File.Sql.39 bug 4440895
582 ,px_rate_date_type IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE --File.Sql.39 bug 4440895
583 ,px_rate_date IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_date%TYPE --File.Sql.39 bug 4440895
584 ,px_rate IN OUT NOCOPY pa_budget_lines.project_cost_exchange_rate%TYPE --File.Sql.39 bug 4440895
585 ,p_amount_type_code IN VARCHAR2
586 ,p_currency_type_code IN VARCHAR2
587 ,p_calling_context IN VARCHAR2
588 ,x_first_error_code OUT NOCOPY VARCHAR2 -- Removed validate code and introduce this parameter for WEBADI. --File.Sql.39 bug 4440895
589 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
590 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
591 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
592 ) ;
593
594 PROCEDURE GET_PLAN_TYPE_OPTS_FOR_VER
595 (
596 p_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
597 ,x_fin_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
598 ,x_plan_type_option_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE --File.Sql.39 bug 4440895
599 ,x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE --File.Sql.39 bug 4440895
600 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
601 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
602 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
603 );
604
605 PROCEDURE Get_Project_Curr_Attributes
606 ( p_project_id IN pa_projects_all.project_id%TYPE
607 ,x_multi_currency_billing_flag OUT NOCOPY pa_projects_all.multi_currency_billing_flag%TYPE --File.Sql.39 bug 4440895
608 ,x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE --File.Sql.39 bug 4440895
609 ,x_projfunc_currency_code OUT NOCOPY pa_projects_all.projfunc_currency_code%TYPE --File.Sql.39 bug 4440895
610 ,x_project_cost_rate_type OUT NOCOPY pa_projects_all.project_rate_type%TYPE --File.Sql.39 bug 4440895
611 ,x_projfunc_cost_rate_type OUT NOCOPY pa_projects_all.projfunc_cost_rate_type%TYPE --File.Sql.39 bug 4440895
612 ,x_project_bil_rate_type OUT NOCOPY pa_projects_all.project_bil_rate_type%TYPE --File.Sql.39 bug 4440895
613 ,x_projfunc_bil_rate_type OUT NOCOPY pa_projects_all.projfunc_bil_rate_type%TYPE --File.Sql.39 bug 4440895
614 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
615 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
616 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
617
618 PROCEDURE IsRevVersionCreationAllowed
619 ( p_project_id IN pa_projects_all.project_id%TYPE
620 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
621 ,x_creation_allowed OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
622 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
623 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
624 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
625
626 PROCEDURE GET_LOOKUP_CODE
627 (
628 p_lookup_type IN pa_lookups.lookup_type%TYPE
629 ,p_lookup_meaning IN pa_lookups.meaning%TYPE
630 ,x_lookup_code OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
631 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
632 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
633 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
634 );
635
636 FUNCTION HAS_PLANNABLE_ELEMENTS
637 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
638 RETURN VARCHAR2;
639
640 -- This procedure is used to derive the version type given the project id and
641 -- fin plan type id
642 PROCEDURE get_version_type
643 ( p_project_id IN pa_projects_all.project_id%TYPE
644 ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
645 ,px_version_type IN OUT NOCOPY pa_budget_Versions.version_type%TYPE --File.Sql.39 bug 4440895
646 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
647 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
648 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
649 );
650
651 PROCEDURE get_version_id
652 ( p_project_id IN pa_projects_all.project_id%TYPE
653 ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
654 ,p_version_type IN pa_budget_Versions.version_type%TYPE
655 ,p_version_number IN pa_budget_Versions.version_number%TYPE
656 ,x_budget_version_id OUT NOCOPY pa_budget_Versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
657 ,x_ci_id OUT NOCOPY pa_budget_Versions.ci_id%TYPE --File.Sql.39 bug 4440895
658 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
659 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
660 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
661 );
662
663
664 PROCEDURE perform_autobasline_checks
665 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
666 ,x_result OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
667 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
668 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
669 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
670
671 PROCEDURE get_version_type_for_bdgt_type
672 ( p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
673 ,x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE --File.Sql.39 bug 4440895
674 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
675 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
676 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
677
678 PROCEDURE validate_editable_bv
679 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
680 p_user_id IN NUMBER,
681
682 --Bug 3986129: FP.M Web ADI Dev changes, a new parameter added
683 p_context IN VARCHAR2 DEFAULT 'ATTACHMENTS',
684 p_excel_calling_mode IN VARCHAR2 DEFAULT NULL,
685 x_locked_by_person_id OUT NOCOPY pa_budget_versions.locked_by_person_id%TYPE, --File.Sql.39 bug 4440895
686 x_err_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
687 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
688 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
689 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
690
691 PROCEDURE check_delete_task_ok
692 (/* p_task_id IN pa_tasks.task_id%TYPE Commenting out NOCOPY for to replace --File.Sql.39 bug 4440895
693 pa_tasks by PA_STRUCT_TASK_WBS_V as part of FP.M, Tracking Bug No - 3354518 */
694 p_task_id IN pa_struct_task_wbs_v.task_id%TYPE
695 ,p_validation_mode IN VARCHAR2 DEFAULT 'U'
696 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
697 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
698 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
699
700 PROCEDURE check_reparent_task_ok
701 (p_task_id IN pa_tasks.task_id%TYPE
702 ,p_old_parent_task_id IN pa_tasks.task_id%TYPE
703 ,p_new_parent_task_id IN pa_tasks.task_id%TYPE
704 ,p_validation_mode IN VARCHAR2 DEFAULT 'U'
705 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
706 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
707 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
708
709 /* Part of changes for FP.M, Tracking Bug No - 3354518
710 This function is being called by PA_FP_ELEMENTS_PUB currently to verify,
711 if PA_FP_ELEMENTS contains a entry for this task. This procedure is now
712 obsoleted.However noticing that this procedure is re-usable, we change
713 all references to pa_tasks to pa_struct_task_wbs_v and all references of
714 pa_fp_elements to pa_resource_assignments */
715 /* FUNCTION check_task_in_fp_option
716 (p_task_id IN pa_tasks.task_id%TYPE)
717 RETURN VARCHAR2; Re-writing function declaration below*/
718 FUNCTION check_task_in_fp_option
719 (p_task_id IN pa_struct_task_wbs_v.task_id%TYPE)
720 RETURN VARCHAR2;
721
722
723 --Name: Get_Budgeted_Amount
724 --Type: Function
725 --
726 --Description: This function is used by Capital-Project calling
727 -- objects to get the following:
728 --
729 -- 1) Project or lowest-level task for a given project
730 -- 2) Approved Cost budget or FP plan version
731 -- 3) raw cost or burden cost amount
732 --
733 -- Amount is the project functional amount for the
734 -- the current baseline budget/plan version.
735 --
736 --
737 --Called subprograms: None.
738 --
739 --Notes:
740 --
741 --History:
742 -- 27-MAY-03 jwhite - Created
743 --
744 -- IN Parameters
745 -- p_project_id - Always passed.
746 --
747 -- p_task_id - Passed as NULL if project-level amounts requested.
748 --
749 -- p_fin_plan_type_id - If passed as NON-null, query FP model.
750 --
751 -- p_budget_type_code - Query pre-FP model if p_fin_plan_type_id is NULL.
752 --
753 -- p_amount_type - Passed as 'R' to return raw cost; 'B' to return burdened cost.
754
755
756 FUNCTION Get_Budgeted_Amount
757 (
758 p_project_id IN pa_projects_all.project_id%TYPE
759 , p_task_id IN pa_tasks.task_id%TYPE
760 , p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
761 , p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
762 , p_amount_type IN VARCHAR2
763 )
764 RETURN NUMBER ;
765
766 PROCEDURE Check_if_plan_type_editable (
767 P_project_id In Number
768 ,P_fin_plan_type_id IN Number
769 ,P_version_type IN VARCHAR2
770 ,X_editable_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
771 ,X_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
772 ,X_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
773 ,X_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
774
775 PROCEDURE End_date_active_val (
776 p_start_date_active IN Date
777 ,p_end_date_active IN Date
778 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
779 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
780 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
781
782 /*=============================================================================
783 This api is used to return current original version info for given plan type,
784 project id and version type
785 ==============================================================================*/
786
787 PROCEDURE Get_Curr_Original_Version_Info(
788 p_project_id IN pa_projects_all.project_id%TYPE
789 ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
790 ,p_version_type IN pa_budget_versions.version_type%TYPE
791 ,x_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE --File.Sql.39 bug 4440895
792 ,x_fin_plan_version_id OUT NOCOPY pa_proj_fp_options.fin_plan_version_id%TYPE --File.Sql.39 bug 4440895
793 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
794 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
795 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
796
797 /*=============================================================================
798 This api is used to derive actual_amts_thru_period for a version. The api also
799 returns first future PA/GL periods if they are available. This api is called
800 from plan setting pages to maintain Include unspent amount through period lov.
801 ==============================================================================*/
802
803 PROCEDURE GET_ACTUAL_AMTS_THRU_PERIOD(
804 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
805 ,x_record_version_number OUT NOCOPY pa_budget_versions.record_version_number%TYPE --File.Sql.39 bug 4440895
806 ,x_actual_amts_thru_period OUT NOCOPY pa_budget_versions.actual_amts_thru_period%TYPE --File.Sql.39 bug 4440895
807 ,x_first_future_pa_period OUT NOCOPY pa_periods_all.period_name%TYPE --File.Sql.39 bug 4440895
808 ,x_first_future_gl_period OUT NOCOPY pa_periods_all.period_name%TYPE --File.Sql.39 bug 4440895
809 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
810 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
811 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
812
813 /* To determine if a task is a planning element or not */
814 -- Modified for Bug 3840993 --sagarwal
815 FUNCTION IS_TASK_A_PLANNING_ELEMENT(
816 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
817 ,p_task_id IN pa_tasks.task_id%TYPE)
818 RETURN VARCHAR2;
819
820 /* To determine if a task has resources attached to it as planning element */
821 FUNCTION IS_RESOURCE_ATTACHED_TO_TASK(
822 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
823 ,p_task_id IN pa_resource_assignments.task_id%TYPE)
824 --,p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE)
825 RETURN VARCHAR2;
826
827 /*=============================================================================
828 This api would be called to check if workplan res list can be updated.
829 The api returns 'Y' or 'N' accordingly. If can not be updated without throwing
830 any error appropriate erroer message code is returned.
831
832 Bug 3651620 Added a new out parameter to indicate if task assignments
833 exist for any of the workplan versions. If resource list can be
834 updated, then only if task assignments data exists then a warning
835 message would be shown to the user.
836 =============================================================================*/
837 PROCEDURE IS_WP_RL_UPDATEABLE(
838 p_project_id IN pa_budget_versions.project_id%TYPE
839 ,x_wp_rl_update_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
840 ,x_reason_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
841 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
842 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
843 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
844
845
846 /*=============================================================================
847 This api checks if any plan type marked for primary forecast cost usage has been
848 attached to the project and returns id of that plan type if found. Else null
849 would be returned
850 ==============================================================================*/
851
852 PROCEDURE IS_PRI_FCST_COST_PT_ATTACHED(
853 p_project_id IN pa_projects_all.project_id%TYPE
854 ,x_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
855 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
856 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
857 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
858
859 /*=============================================================================
860 This api checks if any plan type marked for primary forecast revenue usage has
861 been attached to the project and returns id of that plan type if found. Else null
862 would be returned
863 ==============================================================================*/
864
865 PROCEDURE IS_PRI_FCST_REV_PT_ATTACHED(
866 p_project_id IN pa_projects_all.project_id%TYPE
867 ,x_plan_type_id OUT NOCOPY pa_proj_fp_options.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
868 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
869 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
870 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
871
872
873 /* Used by Resource foundation team to know the resource lists used in WP */
874 FUNCTION is_wp_resource_list
875 (p_project_id IN pa_projects_all.project_id%TYPE
876 ,p_resource_list_id IN
877 pa_resource_lists_all_bg.resource_list_id%TYPE)
878 RETURN VARCHAR2;
879
880 /* Used by Resource foundation team to know the resource lists used in FP */
881 FUNCTION is_fp_resource_list
882 (p_project_id IN pa_projects_all.project_id%TYPE
883 ,p_resource_list_id IN
884 pa_resource_lists_all_bg.resource_list_id%TYPE)
885 RETURN VARCHAR2;
886
887 PROCEDURE GET_CURR_WORKING_VERSION_IDS(P_fin_plan_type_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE -- Id of the plan types
888 ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
889 ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI --File.Sql.39 bug 4440895
890 ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI --File.Sql.39 bug 4440895
891 ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI --File.Sql.39 bug 4440895
892 ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
893 ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
894 ,X_msg_count OUT NOCOPY NUMBER); -- Indicates the number of error messages --File.Sql.39 bug 4440895
895
896 /* GET_SUMMARY_AMOUNTS */
897 -- Added New Params for Quantity in Get_Summary_Amounts - Bug 3902176
898
899 -- p_version parameter was earlier used to retieve the cost or revenue or all quantity figures.
900 -- Since cost and revenue quantity figures are now both alreayd being retrieved and are passed
901 -- in separate out params, p_version parameter is no longer required.
902 -- Commenting out references of p_version_type_below - Bug 3902176
903
904 PROCEDURE GET_SUMMARY_AMOUNTS(p_context IN VARCHAR2
905 ,P_project_id IN Pa_projects_all.project_id%TYPE -- Id of the project .
906 ,P_ci_id IN Pa_budget_versions.ci_id%TYPE DEFAULT NULL -- Controm item id of the change document
907 ,P_fin_plan_type_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE DEFAULT NULL -- Name of default staffing owner.
908 -- ,p_version_type IN pa_budget_versions.version_type%TYPE DEFAULT NULL --Bug 3902176
909 ,X_proj_raw_cost OUT NOCOPY Pa_budget_versions.total_project_raw_cost%TYPE -- Raw Cost in PC --File.Sql.39 bug 4440895
910 ,X_proj_burdened_cost OUT NOCOPY Pa_budget_versions.total_project_burdened_cost%TYPE -- Burdened Cost in PC --File.Sql.39 bug 4440895
911 ,X_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE -- Revenue in PC --File.Sql.39 bug 4440895
912 ,X_margin OUT NOCOPY NUMBER -- MARGIN --File.Sql.39 bug 4440895
913 ,X_margin_percent OUT NOCOPY NUMBER -- MARGIN percent --File.Sql.39 bug 4440895
914 ,X_labor_hrs_cost OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE -- Labor Hours Cost --File.Sql.39 bug 4440895
915 ,X_equipment_hrs_cost OUT NOCOPY Pa_budget_versions.equipment_quantity%TYPE -- Equipment Hours Cost --File.Sql.39 bug 4440895
916 ,X_labor_hrs_rev OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE -- Labor Hours Revenue --File.Sql.39 bug 4440895
917 ,X_equipment_hrs_rev OUT NOCOPY Pa_budget_versions.equipment_quantity%TYPE -- Equipment Hours Revenue --File.Sql.39 bug 4440895
918 ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- Cost Budget Verison Id --File.Sql.39 bug 4440895
919 ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- Revenue Budget Verison Id --File.Sql.39 bug 4440895
920 ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- All Budget Verison Id --File.Sql.39 bug 4440895
921 ,X_margin_derived_from_code OUT NOCOPY pa_proj_fp_options.margin_derived_from_code%TYPE -- margin_derived_from_code of cost version - Bug 3734840 --File.Sql.39 bug 4440895
922 ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
923 ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
924 ,X_msg_count OUT NOCOPY NUMBER); -- Indicates the number of error messages --File.Sql.39 bug 4440895
925
926
927 /* GET_PROJ_IMPACT_AMOUNTS */
928 -- Added New Params for Quantity in Get_Summary_Amounts - Bug 3902176
929
930 -- p_version parameter was earlier used to retieve the cost or revenue or all quantity figures.
931 -- Since cost and revenue quantity figures are now both alreayd being retrieved and are passed
932 -- in separate out params, p_version parameter is no longer required.
933 -- Commenting out references of p_version_type_below - Bug 3902176
934
935 PROCEDURE GET_PROJ_IMPACT_AMOUNTS(p_cost_budget_version_id IN Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI
936 ,p_rev_budget_version_id IN Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI
937 ,p_all_budget_version_id IN Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI
938 -- ,p_version_type IN pa_budget_versions.version_type%TYPE DEFAULT NULL --Bug 3902176
939 ,X_proj_raw_cost OUT NOCOPY Pa_budget_versions.total_project_raw_cost%TYPE -- Raw Cost in PC --File.Sql.39 bug 4440895
940 ,X_proj_burdened_cost OUT NOCOPY Pa_budget_versions.total_project_burdened_cost%TYPE -- Burdened Cost in PC --File.Sql.39 bug 4440895
941 ,X_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE -- Revenue in PC --File.Sql.39 bug 4440895
942 ,X_labor_hrs_cost OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE -- Labor Hours Cost --File.Sql.39 bug 4440895
943 ,X_equipment_hrs_cost OUT NOCOPY Pa_budget_versions.equipment_quantity%TYPE -- Equipment Hours Cost --File.Sql.39 bug 4440895
944 ,X_labor_hrs_rev OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE -- Labor Hours Revenue --File.Sql.39 bug 4440895
945 ,X_equipment_hrs_rev OUT NOCOPY Pa_budget_versions.equipment_quantity%TYPE -- Equipment Hours Revenue --File.Sql.39 bug 4440895
946 ,X_margin OUT NOCOPY Number -- Margin --File.Sql.39 bug 4440895
947 ,X_margin_percent OUT NOCOPY Number -- Margin percent --File.Sql.39 bug 4440895
948 ,X_margin_derived_from_code OUT NOCOPY pa_proj_fp_options.margin_derived_from_code%TYPE -- margin_derived_from_code - Bug 3734840 --File.Sql.39 bug 4440895
949 ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
950 ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
951 ,X_msg_count OUT NOCOPY NUMBER); -- Indicates the number of error messages --File.Sql.39 bug 4440895
952
953
954 /* Function returns 'Y' if budget version has budget lines with rejection code. */
955 FUNCTION does_bv_have_rej_lines(p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
956 RETURN VARCHAR2;
957
958 --------------------------------------------------------------------------------
959 -- This API is called during deleting a Rate Sch to check if the Rate
960 -- Schedule is being reference by any Plan Type or not.
961 -- In case if it is referenced then the 'N' is returned , or else 'Y' is returned
962 --------------------------------------------------------------------------------
963 FUNCTION check_delete_sch_ok(
964 p_bill_rate_sch_id IN pa_std_bill_rate_schedules_all.bill_rate_sch_id%TYPE)
965 RETURN VARCHAR2;
966
967 --------------------------------------------------------------------------------
968 -- This API is called during deleting a Burden Rate Sch to check if the Burden Rate
969 -- Schedule is being reference by any Plan Type or not.
970 -- In case if it is referenced then the 'N' is returned , or else 'Y' is returned
971 --------------------------------------------------------------------------------
972 FUNCTION check_delete_burd_sch_ok(
973 p_ind_rate_sch_id IN pa_ind_rate_schedules_all_bg.ind_rate_sch_id%TYPE)
974 RETURN VARCHAR2;
975
976 /* -------------------------------------------------------------------------------------------
977 * Function to check for the validity of the event of unchecking of 'Plan in Multi Currency'
978 * check box in the 'Edit Planning Options' screen. This api is called just before committing
979 * the changes done in the page and is called for both workplan and budgeting and forecasting
980 * context and this is indicated by the value of input parameter p_context, for which the
981 * valid values are 'WORKPLAN' and 'FINPLAN'. If the context is 'WORKPLAN' the input parameter
982 * p_budget_version_id would be null. The api returns 'Y' if the event is valid and allowed
983 * and returns 'N' otherwise.
984 *--------------------------------------------------------------------------------------------*/
985 FUNCTION Validate_Uncheck_MC_Flag (
986 p_project_id IN pa_projects_all.project_id%TYPE,
987 p_context IN VARCHAR2,
988 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
989 RETURN VARCHAR2;
990
991 /*=============================================================================
992 This api is called to check if a txn currency can be deleted for an fp option.
993 For workplan case,
994 A txn currency can not be deleted if
995 1. the currency is project currency or
996 2. the currency is project functional currency or
997 3. amounts exist against the currency in any of the workplan versions
998
999 For Budgets and Forecasting case,
1000 A txn currency can not be deleted if
1001 1. the currency is project currency or
1002 2. the currency is project functional currency or
1003 3. option is a version and amounts exist against the currency
1004 ==============================================================================*/
1005
1006 FUNCTION Check_delete_txn_cur_ok(
1007 p_project_id IN pa_projects_all.project_id%TYPE
1008 ,p_context IN VARCHAR2 -- FINPLAN or WORKPLAN
1009 ,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
1010 ,p_txn_currency_code IN fnd_currencies.currency_code%TYPE
1011 ) RETURN VARCHAR2;
1012
1013 /*=============================================================================
1014 This api is called to check if amounts exist for any of the workplan versions
1015 of the project in budgets data model.
1016 ==============================================================================*/
1017
1018 FUNCTION check_if_amounts_exist_for_wp(
1019 p_project_id IN pa_projects_all.project_id%TYPE
1020 ) RETURN VARCHAR2;
1021
1022 /*=============================================================================
1023 This api is called to check if task assignments exist for any of the workplan
1024 versions of the given project
1025 ==============================================================================*/
1026
1027 FUNCTION check_if_task_asgmts_exist(
1028 p_project_id IN pa_projects_all.project_id%TYPE
1029 ) RETURN VARCHAR2;
1030
1031 /*=============================================================================
1032 This api is called to check if amounts exist for any of the budget versions
1033 of the project - plan type combination. This is used as of now to restrict
1034 RBS change at plan type level.
1035 ==============================================================================*/
1036
1037 FUNCTION check_if_amounts_exist_for_fp(
1038 p_project_id IN pa_projects_all.project_id%TYPE
1039 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id %TYPE
1040 ) RETURN VARCHAR2;
1041
1042 /*===================================================================================
1043 This api is used to validate the plan processing code if it passed, or to return
1044 the same for the budget version id that is passed in budget context or for the
1045 ci_id passed for the CI version context and throw an error in case they are not valid
1046 =====================================================================================*/
1047
1048
1049
1050
1051 PROCEDURE return_and_vldt_plan_prc_code
1052 (
1053 p_add_msg_to_stack IN VARCHAR2 DEFAULT 'Y'
1054 ,p_calling_context IN VARCHAR2 DEFAULT 'BUDGET'
1055 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE DEFAULT NULL
1056 ,p_source_ci_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
1057 ,p_target_ci_id IN pa_control_items.ci_id%TYPE DEFAULT NULL
1058 ,p_plan_processing_code IN pa_budget_versions.plan_processing_code%TYPE DEFAULT NULL
1059 ,x_final_plan_prc_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE --File.Sql.39 bug 4440895
1060 ,x_targ_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE --File.Sql.39 bug 4440895
1061 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1062 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1063 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1064 );
1065
1066 FUNCTION Is_source_for_gen_options
1067 (p_project_id IN pa_projects_all.project_id%TYPE
1068 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
1069 ,p_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
1070 ) RETURN VARCHAR2;
1071
1072
1073 /* bug 4494740: the following global variable added so that they
1074 * can be used in view pa_fp_webadi_periodic_v
1075 */
1076
1077 g_fp_wa_struct_ver_id pa_proj_element_versions.parent_structure_version_id%TYPE;
1078 g_fp_wa_struct_status_flag VARCHAR2(1);
1079 g_fp_wa_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
1080
1081 TYPE fp_wa_task_pc_compl_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1082
1083 g_fp_wa_task_pc_compl_tbl fp_wa_task_pc_compl_tab;
1084
1085 /* bug 4494740: The following function is included here which would return
1086 the percent complete for the financial structure version when the financial
1087 structure_version_id and the status_flags are passed as input. This function
1088 is introduced as part of performance improvement for FP.M excel download
1089 in the view pa_fp_webadi_periodic_v.
1090 */
1091
1092 FUNCTION get_physical_pc_complete
1093 ( p_project_id IN pa_projects_all.project_id%TYPE,
1094 p_proj_element_id IN pa_proj_element_versions.proj_element_id%TYPE)
1095
1096 RETURN NUMBER;
1097
1098 FUNCTION set_webadi_download_var
1099 (p_structure_version_id IN pa_proj_element_versions.parent_structure_version_id%TYPE,
1100 p_structure_status_flag IN VARCHAR2)
1101
1102 RETURN VARCHAR2;
1103
1104 PRAGMA RESTRICT_REFERENCES (set_webadi_download_var, RNDS,WNDS, TRUST);
1105
1106 FUNCTION get_fp_wa_struct_ver_id RETURN NUMBER;
1107
1108 PRAGMA RESTRICT_REFERENCES (get_fp_wa_struct_ver_id, RNDS,WNDS, TRUST);
1109
1110
1111 /* This procedure is called from FPWebadiAMImpl.java to get the structure version id
1112 * and the structure version status flag to be used as URL parameter for BNE URL
1113 */
1114 PROCEDURE return_struct_ver_info
1115 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
1116 x_struct_version_id OUT NOCOPY pa_proj_element_versions.parent_structure_version_id%TYPE,
1117 x_struct_status_flag OUT NOCOPY VARCHAR2,
1118 x_return_status OUT NOCOPY VARCHAR2,
1119 x_msg_count OUT NOCOPY NUMBER,
1120 x_msg_data OUT NOCOPY VARCHAR2);
1121
1122 FUNCTION get_cached_time_phased_code (bv_id IN pa_budget_versions.budget_version_id%TYPE)
1123 RETURN VARCHAR2;
1124 -- bug 4494740: ends
1125
1126 /*=============================================================================
1127 This api is used as a wrapper API to pa_budget_pub.create_draft_budget
1128 ==============================================================================*/
1129
1130 PROCEDURE create_draft_budget_wrp(
1131 p_api_version_number IN NUMBER
1132 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1133 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1134 ,p_msg_count OUT NOCOPY NUMBER
1135 ,p_msg_data OUT NOCOPY VARCHAR2
1136 ,p_return_status OUT NOCOPY VARCHAR2
1137 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1138 ,p_pm_budget_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1139 , p_budget_version_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1140 ,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1141 ,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1142 ,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1143 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1144 ,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1145 ,p_entry_method_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1146 ,p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1147 ,p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1148 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1149 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1150 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1151 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1152 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1153 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1154 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1155 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1156 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1157 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1158 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1159 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1160 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1161 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1162 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1163 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1164 ,p_budget_lines_in IN PA_BUDGET_PUB.budget_line_in_tbl_type
1165 ,p_budget_lines_out OUT NOCOPY PA_BUDGET_PUB.budget_line_out_tbl_type
1166
1167 /*Parameters due fin plan model */
1168 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1169 ,p_fin_plan_type_name IN pa_fin_plan_types_vl.name%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1170 ,p_version_type IN pa_budget_versions.version_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1171 ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1172 ,p_time_phased_code IN pa_proj_fp_options.cost_time_phased_code%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1173 ,p_plan_in_multi_curr_flag IN pa_proj_fp_options.plan_in_multi_curr_flag%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1174 ,p_projfunc_cost_rate_type IN pa_proj_fp_options.projfunc_cost_rate_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1175 ,p_projfunc_cost_rate_date_typ IN pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1176 ,p_projfunc_cost_rate_date IN pa_proj_fp_options.projfunc_cost_rate_date%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1177 ,p_projfunc_rev_rate_type IN pa_proj_fp_options.projfunc_rev_rate_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1178 ,p_projfunc_rev_rate_date_typ IN pa_proj_fp_options.projfunc_rev_rate_date_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1179 ,p_projfunc_rev_rate_date IN pa_proj_fp_options.projfunc_rev_rate_date%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1180 ,p_project_cost_rate_type IN pa_proj_fp_options.project_cost_rate_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1181 ,p_project_cost_rate_date_typ IN pa_proj_fp_options.project_cost_rate_date_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1182 ,p_project_cost_rate_date IN pa_proj_fp_options.project_cost_rate_date%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1183 ,p_project_rev_rate_type IN pa_proj_fp_options.project_rev_rate_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1184 ,p_project_rev_rate_date_typ IN pa_proj_fp_options.project_rev_rate_date_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1185 ,p_project_rev_rate_date IN pa_proj_fp_options.project_rev_rate_date%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1186 ,p_raw_cost_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1187 ,p_burdened_cost_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1188 ,p_revenue_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1189 ,p_cost_qty_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1190 ,p_revenue_qty_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1191 ,P_all_qty_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1192 ,p_create_new_curr_working_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1193 ,p_replace_current_working_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1194 ,p_using_resource_lists_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1195 );
1196
1197 /*
1198 API Name : Get_NP_RA_Description
1199 API Description : Returns the description for the Non Periodic Resource Assignment
1200 API Created By : kchaitan
1201 API Creation Date : 07-MAY-2007
1202 */
1203
1204 FUNCTION Get_NP_RA_Description
1205 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE Default Null,
1206 p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE Default Null
1207 ) RETURN VARCHAR2;
1208
1209 /*
1210 API Name : Get_Change_Reason
1211 API Description : Returns the Change Reason Meaning for the Non Periodic and Periodic Resource Assignment
1212 API Created By : kchaitan
1213 API Creation Date : 07-MAY-2007
1214 */
1215
1216 FUNCTION Get_Change_Reason
1217 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE Default Null,
1218 p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE Default Null,
1219 p_time_phased_code IN varchar2
1220 ) RETURN VARCHAR2;
1221
1222 -- gboomina added for bug 8318932 - start
1223 /* B&F -This function is used to get the
1224 copy_etc_from_plan_flag in the generation options in case of cost forecast*/
1225 FUNCTION get_copy_etc_from_plan_flag
1226 (p_project_id IN pa_proj_fp_options.project_id%TYPE,
1227 p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE,
1228 p_fin_plan_option_code IN pa_proj_fp_options.fin_plan_option_level_code%TYPE,
1229 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
1230 RETURN pa_proj_fp_options.copy_etc_from_plan_flag%type;
1231 -- gboomina added for bug 8318932 - end
1232
1233 END pa_fin_plan_utils;
1234
[Home] [Help]