[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_AMT_WRP_PKG
Source
1 PACKAGE BODY PA_FP_GEN_AMT_WRP_PKG AS
2 /* $Header: PAFPGAWB.pls 120.16.12020000.2 2013/01/22 17:20:10 kmaddi ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 PROCEDURE GEN_AMT_WRP(
6 errbuff OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 p_organization_id IN NUMBER,
9 p_project_type_id IN NUMBER,
10 p_proj_manager_id IN NUMBER,
11 p_from_project_no IN VARCHAR2,
12 p_to_project_no IN VARCHAR2,
13 p_plan_type_id IN NUMBER)
14 IS
15 l_module_name VARCHAR2(200) :=
16 'pa.plsql.PA_FP_GEN_AMT_WRP_PKG.GEN_AMT_WRP';
17
18 l_from_proj_no varchar2(100) := NULL;
19 l_to_proj_no varchar2(100) := NULL;
20
21 l_sel_clause varchar2(2000);
22 l_where_clause varchar2(2000);
23 l_where_clause0 varchar2(2000);
24 l_where_clause1 varchar2(2000);
25 l_where_clause2 varchar2(2000);
26 l_from_clause varchar2(2000);
27 l_stmt varchar2(2000);
28 l_proj_id number;
29 l_plan_type_id number;
30 l_fin_plan_preference_code varchar2(30);
31 l_plan_class_code varchar2(30);
32 l_count number := 0;
33 l_element_type_tab pa_plsql_datatypes.Char30TabTyp;
34 l_rows number;
35 l_rows1 number;
36 sql_cursor number;
37
38 lx_budget_version_id number;
39 lx_proj_fp_option_id number;
40 l_unspent_amt_period varchar2(50);
41 l_act_from_period varchar2(50);
42 l_act_to_period varchar2(50);
43 l_etc_from_period varchar2(50);
44 l_etc_to_period varchar2(50);
45 l_act_thru_period varchar2(50);
46 l_act_thru_date date;
47
48 l_versioning_enabled varchar2(10);
49
50 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
51 l_gen_src_wp_ver_code varchar2(50);
52 l_gen_src_plan_ver_code varchar2(50);
53 l_etc_wp_struct_ver_id number;
54 l_etc_wp_fin_ver_id number;
55 l_etc_fp_type_id number;
56 l_etc_fp_ver_id number;
57 l_etc_fp_ver_name VARCHAR2(60);
58 l_fp_options_id number;
59 l_project_name VARCHAR2(200);
60 l_plan_type_name pa_fin_plan_types_tl.name%type;
61 l_struct_sharing_code pa_projects_all.structure_sharing_code%type;
62 l_return_status varchar2(10);
63 l_msg_count number;
64 l_msg_data varchar2(2000);
65 l_msg_data_in varchar2(2000);
66
67 l_data VARCHAR2(2000);
68 l_msg_index_out number;
69
70 l_called_mode Varchar2(50) := 'CONCURRENT';
71
72 l_version_name Varchar2(2000);
73 /* Added two flag variables to check for create_version and return_status from APIs called
74 -bug 4029915 */
75 l_generation_flag VARCHAR2(10) := 'Y';
76 l_gen_api_call_flag VARCHAR2(10) := 'Y';
77
78 l_project_name_err_token VARCHAR2(500);
79 l_fin_plan_type_err_token VARCHAR2(500);
80
81 l_fin_plan_src_dtls_flag VARCHAR2(1);
82 l_workplan_src_dtls_flag VARCHAR2(1);
83 lc_FinancialPlan CONSTANT VARCHAR2(30) := 'FINANCIAL_PLAN';
84 lc_WorkplanResources CONSTANT VARCHAR2(30) := 'WORKPLAN_RESOURCES';
85 lc_TaskLevelSel CONSTANT VARCHAR2(30) := 'TASK_LEVEL_SEL';
86 l_fin_plan_count NUMBER;
87 l_workplan_count NUMBER;
88
89 /* Added for ER 4391321 */
90 -- Added for VALIDATE_SUPPORT_CASES API. Value not used by Concurrent Program.
91 l_warning_message VARCHAR2(2000);
92
93 -- gboomina added for AAI requirement 8318932 - start
94 l_copy_etc_from_plan_flag PA_PROJ_FP_OPTIONS.COPY_ETC_FROM_PLAN_FLAG%TYPE;
95 l_gen_cost_etc_src_code PA_PROJ_FP_OPTIONS.GEN_COST_ETC_SRC_CODE%TYPE;
96 l_cost_time_phased_code PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE;
97 l_plan_type_validated VARCHAR2(1) := 'Y';
98 -- gboomina added for AAI requirement 8318932 - end
99
100 --Bug 16181483 Start
101 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
102 l_version_type pa_budget_versions.version_type%TYPE;
103 --Bug 16181483 End
104
105 BEGIN
106 -- hr_utility.trace_on(null,'Sharmila');
107 IF P_PA_DEBUG_MODE = 'Y' THEN
108 pa_debug.set_curr_function( p_function => 'GEN_AMT_WRP',
109 p_debug_mode => p_pa_debug_mode );
110 END IF;
111
112 /* Getting token values for use by possible error messages. */
113 FND_MESSAGE.SET_NAME('PA','PA_FP_GEN_EXCEPTION_INFO');
114 l_project_name_err_token := FND_MESSAGE.GET;
115 FND_MESSAGE.SET_NAME('PA','PA_FP_GEN_EXCEPTION_INFO1');
116 l_fin_plan_type_err_token := FND_MESSAGE.GET;
117
118 retcode := '0';
119
120 sql_cursor := DBMS_SQL.OPEN_CURSOR;
121 l_sel_clause := ' SELECT distinct pj.project_id, opt.fin_plan_type_id,'
122 ||' opt.FIN_PLAN_PREFERENCE_CODE,pt.PLAN_CLASS_CODE'
123 ||',pj.name'||
124 -- gboomina added for AAI Requirement 8318932 - start
125 -- select ETC source code, Time Phase and Copy ETC from Plan flag
126 -- to validate the target financial plan if Copy ETC from plan flag
127 -- is selected
128 ' ,opt.GEN_COST_ETC_SRC_CODE, opt.COST_TIME_PHASED_CODE, ' || --Bug 9322311
129 'opt.COPY_ETC_FROM_PLAN_FLAG';
130 -- gboomina added for AAI Requirement 8318932 - end
131
132 -- Bug 4367064: Instead of checking if pj.project_status_code <> 'CLOSED'
133 -- directly, we make use of the Is_Project_Status_Closed function, which
134 -- returns 'Y' if the project_status_code is equivalent to 'CLOSED'.
135
136 -- bug 5657334: Changed pa_projects_all to refer pa_projects as the concurrent
137 -- program has been modified to make it single org compliant.
138
139 l_from_clause := ' FROM pa_projects pj, pa_proj_fp_options opt,' ||
140 ' pa_fin_plan_types_b pt, pa_project_types_all pta';
141 l_where_clause := ' WHERE 1 = 1 AND pj.project_id = opt.project_id' ||
142 ' AND nvl(pj.template_flag,''N'') <> ''Y''' ||
143 ' AND NVL(PA_PROJECT_STUS_UTILS.Is_Project_Status_Closed' ||
144 '(pj.project_status_code),''N'') <> ''Y''' ||
145 ' AND opt.FIN_PLAN_OPTION_LEVEL_CODE = ''PLAN_TYPE''' ||
146 'AND (pj.project_type = pta.project_type AND pta.org_project_flag <> ''Y'')';
147 l_where_clause0:= ' AND pt.fin_plan_type_id = opt.fin_plan_type_id';
148
149 --****p_organization_id****--
150 IF (P_ORGANIZATION_ID) IS NOT NULL THEN
151 -- SQL Repository Bug 5112175; SQL ID 16507224
152 -- Replaced SQL literals with bind variables.
153 l_where_clause1 := ' AND pj.CARRYING_OUT_ORGANIZATION_ID = '
154 ||':organization_id';
155 END IF;
156
157 --****p_project_type_id****--
158 IF (P_PROJECT_TYPE_ID IS NOT NULL) THEN
159 l_from_clause := l_from_clause || ', pa_project_types pt'; /* Bug 5657334 */
160 -- SQL Repository Bug 5112175; SQL ID 16507225
161 -- Replaced SQL literals with bind variables.
162 -- R12 MOAC 4447573: AND NVL(pj.org_id, -99) = NVL(pt.org_id, -99)
163 l_where_clause1 := l_where_clause1 ||
164 ' AND pj.org_id = pt.org_id ' ||
165 ' AND pj.project_type = pt.project_type AND pt.PROJECT_TYPE_ID = '
166 ||':project_type_id';
167 END IF;
168
169 --*****p_proj_manager_id****--
170 -- SQL Repository Bug 4884427; SQL ID 14901662
171 -- Replaced SQL literal with a bind variable.
172 IF (p_proj_manager_id IS NOT NULL) THEN
173 l_from_clause := l_from_clause ||', pa_project_parties pp';
174 l_where_clause1 := l_where_clause1 ||' AND pp.project_id = pj.project_id'
175 ||' AND pp.object_type = ''PA_PROJECTS'' AND pp.resource_source_id = '
176 ||' :proj_manager_id';
177 END IF;
178
179 -- Bug 4367064: Instead of checking if pj.project_status_code <> 'CLOSED'
180 -- directly, we make use of the Is_Project_Status_Closed function, which
181 -- returns 'Y' if the project_status_code is equivalent to 'CLOSED'.
182
183 --*****p_from_project_no ande p_to_project_no****--
184 IF (p_from_project_no IS NULL) THEN
185 SELECT min(pj.segment1)
186 INTO l_from_proj_no
187 FROM pa_projects pj /* Bug 5657334 */
188 WHERE NVL(PA_PROJECT_STUS_UTILS.Is_Project_Status_Closed(pj.project_status_code),'N') <> 'Y';
189
190 ELSE
191 l_from_proj_no := p_from_project_no;
192 -- hr_utility.trace('l_from_proj_no '||l_from_proj_no);
193 END IF;
194 -- hr_utility.trace('l_from_proj_no '||l_from_proj_no);
195
196 -- Bug 4367064: Instead of checking if pj.project_status_code <> 'CLOSED'
197 -- directly, we make use of the Is_Project_Status_Closed function, which
198 -- returns 'Y' if the project_status_code is equivalent to 'CLOSED'.
199
200 IF (p_to_project_no IS NULL) THEN
201 SELECT max(pj.segment1)
202 INTO l_to_proj_no
203 FROM pa_projects pj /* Bug 5657334 */
204 WHERE NVL(PA_PROJECT_STUS_UTILS.Is_Project_Status_Closed(pj.project_status_code),'N') <> 'Y';
205 ELSE
206 l_to_proj_no := p_to_project_no;
207 -- hr_utility.trace('l_to_proj_no '||l_to_proj_no);
208 END IF;
209 -- SQL Repository Bug 4884427; SQL ID 14901706
210 -- Instead of concatenating quotes around l_from_proj_no and
211 -- l_to_proj_no, just leave them as normal strings for binding.
212 /*********************** BEGIN Commenting ***********************
213 -- l_from_proj_no := ''''||l_from_proj_no||'''';
214 -- l_to_proj_no := ''''||l_to_proj_no||'''';
215 ************************ END Commenting** ***********************/
216
217 -- hr_utility.trace('l_from_proj_no2 '||l_from_proj_no);
218 -- hr_utility.trace('l_to_proj_no2 '||l_to_proj_no);
219
220 -- SQL Repository Bug 4884427; SQL ID 14901706
221 -- Replaced SQL literals with bind variables.
222 l_where_clause2 := ' AND pj.segment1 BETWEEN :from_proj_no and :to_proj_no';
223
224 --*****p_plan_type_id****--
225 IF (P_PLAN_TYPE_ID) IS NOT NULL THEN
226 -- SQL Repository Bug 5112175; SQL ID 16507275
227 -- Replaced SQL literals with bind variables.
228 l_where_clause2 := l_where_clause2 ||
229 ' AND opt.FIN_PLAN_TYPE_ID = '
230 ||':plan_type_id';
231 END IF;
232
233 l_stmt := l_sel_clause || l_from_clause || l_where_clause||l_where_clause0
234 ||l_where_clause1||l_where_clause2;
235
236 --dbms_output.put_line('Sel Clause: '||l_sel_clause);
237 --dbms_output.put_line('From Clause: '||l_from_clause);
238 --dbms_output.put_line('Where Clause: '||l_where_clause);
239 --dbms_output.put_line('Where Clause0: '||l_where_clause0);
240 --dbms_output.put_line('Where Clause1 : ' || l_where_clause1);
241 --dbms_output.put_line('Where Clause2 : ' || l_where_clause2);
242 /* hr_utility.trace('Sel Clause: '||l_sel_clause);
243 hr_utility.trace('From Clause: '||l_from_clause);
244 hr_utility.trace('Where Clause: '||l_where_clause);
245 hr_utility.trace('Where Clause0: '||l_where_clause0);
246 hr_utility.trace('Where Clause1 : ' || l_where_clause1);
247 hr_utility.trace('Where Clause2 : ' || l_where_clause2);
248 hr_utility.trace('Dynamic SQL ' || l_stmt); */
249
250 DBMS_SQL.PARSE(sql_cursor, l_stmt, dbms_sql.v7);
251
252 -- SQL Repository Bugs 4884427, 5112175;
253 -- SQL IDs 14901706, 14901662, 16507224, 16507225, 16507275
254 -- Bind values to the new bind variables.
255 IF (P_ORGANIZATION_ID) IS NOT NULL THEN
256 DBMS_SQL.BIND_VARIABLE(sql_cursor, ':organization_id', p_organization_id);
257 END IF;
258 IF (P_PROJECT_TYPE_ID IS NOT NULL) THEN
259 DBMS_SQL.BIND_VARIABLE(sql_cursor, ':project_type_id', p_project_type_id);
260 END IF;
261 IF (p_proj_manager_id IS NOT NULL) THEN
262 DBMS_SQL.BIND_VARIABLE(sql_cursor, ':proj_manager_id', p_proj_manager_id);
263 END IF;
264 IF (P_PLAN_TYPE_ID) IS NOT NULL THEN
265 DBMS_SQL.BIND_VARIABLE(sql_cursor, ':plan_type_id', p_plan_type_id);
266 END IF;
267 DBMS_SQL.BIND_VARIABLE(sql_cursor, ':from_proj_no', l_from_proj_no);
268 DBMS_SQL.BIND_VARIABLE(sql_cursor, ':to_proj_no', l_to_proj_no);
269
270 l_rows := DBMS_SQL.EXECUTE (sql_cursor);
271
272 --dbms_output.put_line('After execute, l_rows is: '||l_rows);
273 -- hr_utility.trace('After execute, l_rows is: '||l_rows);
274
275 IF (l_rows < 0) THEN
276 IF p_pa_debug_mode = 'Y' THEN
277 pa_fp_gen_amount_utils.fp_debug
278 (p_called_mode => l_called_mode,
279 p_msg =>'After dbms parse '||to_char(l_rows),
280 p_module_name => l_module_name,
281 p_log_level => 5);
282 PA_DEBUG.RESET_CURR_FUNCTION;
283 END IF;
284 RETURN;
285 END IF;
286
287 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 1, l_proj_id);
288 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 2, l_plan_type_id);
289 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 3, l_fin_plan_preference_code,30);
290 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 4, l_plan_class_code,30);
291 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 5, l_project_name,200);
292 -- gboomina added for AAI Requirement 8318932 - start
293 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 6, l_gen_cost_etc_src_code,30);
294 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 7, l_cost_time_phased_code,30);
295 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 8, l_copy_etc_from_plan_flag,1);
296 -- gboomina added for AAI Requirement 8318932 - end
297
298 LOOP
299 l_rows1 := DBMS_SQL.FETCH_ROWS(sql_cursor);
300 --dbms_output.put_line('l_rows1 value inside the loop is : ' || l_rows1);
301 --hr_utility.trace('l_rows1 value inside the loop is : ' || l_rows1);
302 IF l_rows1 = 0 then
303 EXIT;
304 END IF;
305 DBMS_SQL.COLUMN_VALUE(sql_cursor,1,l_proj_id);
306 DBMS_SQL.COLUMN_VALUE(sql_cursor,2,l_plan_type_id);
307 DBMS_SQL.COLUMN_VALUE(sql_cursor,3,l_fin_plan_preference_code);
308 DBMS_SQL.COLUMN_VALUE(sql_cursor,4,l_plan_class_code);
309 DBMS_SQL.COLUMN_VALUE(sql_cursor,5,l_project_name);
310 -- gboomina added for AAI Requirement 8318932 - start
311 DBMS_SQL.COLUMN_VALUE(sql_cursor,6,l_gen_cost_etc_src_code);
312 DBMS_SQL.COLUMN_VALUE(sql_cursor,7,l_cost_time_phased_code);
313 DBMS_SQL.COLUMN_VALUE(sql_cursor,8,l_copy_etc_from_plan_flag);
314 -- gboomina added for AAI Requirement 8318932 - end
315
316 --dbms_output.put_line('====l_proj_id is : ' || l_proj_id);
317 --dbms_output.put_line('====l_plan_type_id is : ' || l_plan_type_id);
318 --dbms_output.put_line('====l_fin_plan_preference_code is : ' || l_fin_plan_preference_code);
319 --dbms_output.put_line('====l_plan_class_code is : ' || l_plan_class_code);
320
321 /**Under the context of one project_id and one plan_type_id, a new
322 *budget version is to be created. **/
323
324 -- gboomina added for AAI requirement 8318932 - start
325 -- Moving the plan type code here to get the plan type value beforehand
326 -- so that this will be used blow to display the plan type in output log file
327 /* The hard-coded values Project Name and the Financial Plan Type
328 should be replaced with FND Message. (This will be done
329 based on the release team response for the processing of
330 the SEED bug. */
331 BEGIN
332 SELECT name INTO l_plan_type_name FROM pa_fin_plan_types_tl
333 WHERE
334 fin_plan_type_id = l_plan_type_id AND
335 language = USERENV('LANG');
336 EXCEPTION
337 WHEN OTHERS THEN
338 l_plan_type_name := NULL;
339 END;
340
341 IF l_plan_class_code = 'FORECAST' THEN
342 IF l_copy_etc_from_plan_flag = 'Y' THEN
343 -- Check whether the target version type is 'COST' and
344 -- ETC Source is 'Task Level Selection'. Only this combo is supported for
345 -- Copy ETC from plan flow.
346 IF ( l_fin_plan_preference_code <> 'COST_ONLY' ) THEN
347 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
348 p_msg_name => 'PA_FP_COST_PLAN_TYPE_ONLY_SUPP');
349 l_plan_type_validated := 'N';
350 END IF;
351 IF ( l_gen_cost_etc_src_code <> 'TASK_LEVEL_SEL' ) THEN
352 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
353 p_msg_name => 'PA_FP_TASK_LEVEL_SEL_ONLY');
354 l_plan_type_validated := 'N';
355 END IF;
356 -- Check whether destination financial plan is non time phased.
357 -- if so, throw an error.
358 -- Only time phased plan is supported for Copy ETC From plan AAI requirement
359 IF ( l_cost_time_phased_code ) = 'N' THEN
360 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
361 p_msg_name => 'PA_FP_NON_TIME_PHASE_NOT_SUPP',
362 p_token1 => 'PLAN_TYPE',
363 p_value1 => 'Financial Plan');
364
365 l_plan_type_validated := 'N';
366 END IF;
367 END IF;
368 END IF;
369
370 IF l_plan_type_validated = 'Y' THEN
371 -- gboomina added for AAI requirement 8318932 - end
372
373 l_element_type_tab.delete;
374 IF l_fin_plan_preference_code = 'COST_ONLY' THEN
375 l_count := 1;
376 l_element_type_tab(1) := 'COST';
377 ELSIF l_fin_plan_preference_code = 'REVENUE_ONLY' THEN
378 l_count := 1;
379 l_element_type_tab(1) := 'REVENUE';
380 ELSIF l_fin_plan_preference_code = 'COST_AND_REV_SEP' THEN
381 l_count := 2;
382 l_element_type_tab(1) := 'COST';
383 l_element_type_tab(2) := 'REVENUE';
384 ELSIF l_fin_plan_preference_code = 'COST_AND_REV_SAME' THEN
385 --dbms_output.put_line('HERE++++++++');
386 l_count := 1;
387 l_element_type_tab(1) := 'ALL';
388 END IF;
389
390
391 IF l_plan_class_code = 'BUDGET' THEN
392 FND_MESSAGE.SET_NAME( 'PA', 'PA_FP_BUDGET_GENERATION' );
393 l_version_name := FND_MESSAGE.GET;
394 ELSIF l_plan_class_code = 'FORECAST' THEN
395 FND_MESSAGE.SET_NAME( 'PA', 'PA_FP_FORECAST_GENERATION' );
396 l_version_name := FND_MESSAGE.GET;
397 END IF;
398
399 FOR i in 1..l_count LOOP
400 l_generation_flag := 'Y';
401 l_gen_api_call_flag := 'Y';
402
403 IF p_pa_debug_mode = 'Y' THEN
404 pa_fp_gen_amount_utils.fp_debug
405 (p_called_mode => l_called_mode,
406 p_msg =>'Before calling
407 pa_fin_plan_pub.Create_Version',
408 p_module_name => l_module_name,
409 p_log_level => 5);
410 pa_fp_gen_amount_utils.fp_debug
411 (p_called_mode => l_called_mode,
412 p_msg =>'Value of Project_id: '||l_proj_id,
413 p_module_name => l_module_name,
414 p_log_level => 5);
415 pa_fp_gen_amount_utils.fp_debug
416 (p_called_mode => l_called_mode,
417 p_msg =>'Value of fin_plan_type_id : '||l_plan_type_id,
418 p_module_name => l_module_name,
419 p_log_level => 5);
420 pa_fp_gen_amount_utils.fp_debug
421 (p_called_mode => l_called_mode,
422 p_msg =>'Element Type : '|| l_element_type_tab(i),
423 p_module_name => l_module_name,
424 p_log_level => 5);
425 END IF;
426
427 --dbms_output.put_line('before create_version');
428 --hr_utility.trace('before create_version');
429 --dbms_output.put_line('Value of Project_id before calling create_version: '||l_proj_id);
430 --hr_utility.trace('Value of Project_id before calling create_version:'||l_proj_id);
431 --dbms_output.put_line('Value of fin_plan_type_id before calling create_version: '||l_plan_type_id);
432 --hr_utility.trace('Value of fin_plan_type_id before calling create_version: '||l_plan_type_id);
433 --dbms_output.put_line('Value of element_type before calling create_version: '||l_element_type_tab(i));
434 --hr_utility.trace('Value of element_type before calling create_version: '||l_element_type_tab(i));
435 --hr_utility.trace('Value of Version_Name before calling create_version: '||l_version_name);
436 --hr_utility.trace('Value of px_budget_version_id before calling create_version: '||lx_budget_version_id);
437 --hr_utility.trace('Value of x_proj_fp_option_id before calling create_version: '||lx_proj_fp_option_id);
438
439 -- 3831449: pass 'GENERATE' as p_calling_context parameter to Create Version API
440
441 /* Reseting lx_budget_version_id and lx_proj_fp_option_id Bug 4029915 */
442 FND_MSG_PUB.initialize;
443 lx_budget_version_id := NULL;
444 lx_proj_fp_option_id := NULL;
445
446 BEGIN
447 pa_fin_plan_pub.Create_Version(
448 p_project_id => l_proj_id,
449 p_fin_plan_type_id => l_plan_type_id,
450 p_element_type => l_element_type_tab(i),
451 p_version_name => l_version_name|| ' '||
452 to_char(sysdate,'rrrr:mm:dd hh24:mi:ss'),
453 p_description => l_version_name|| ' '||
454 to_char(sysdate,'rrrr:mm:dd hh24:mi:ss'),
455 px_budget_version_id => lx_budget_version_id,
456 x_proj_fp_option_id => lx_proj_fp_option_id,
457 p_calling_context => 'GENERATE',
458 x_return_status => l_return_status,
459 x_msg_count => l_msg_count,
460 x_msg_data => l_msg_data);
461
462 --dbms_output.put_line('l_return_status for create_version:' || l_return_status);
463 -- hr_utility.trace('l_return_status for create_version:' || l_return_status);
464 -- dbms_output.put_line('Newly created version is: '||lx_budget_version_id);
465 --hr_utility.trace('Newly created version is: '||lx_budget_version_id);
466 EXCEPTION
467 WHEN OTHERS THEN
468 l_generation_flag := 'N';
469 END;
470
471 IF p_pa_debug_mode = 'Y' THEN
472 pa_fp_gen_amount_utils.fp_debug
473 (p_called_mode => l_called_mode,
474 p_msg =>'Status aft calling pa_fin_plan_pub.Create_Version:'
475 ||l_return_status,
476 p_module_name => l_module_name,
477 p_log_level => 5);
478 END IF;
479
480 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
481 IF p_pa_debug_mode = 'Y' THEN
482 pa_fp_gen_amount_utils.fp_debug
483 (p_called_mode => l_called_mode,
484 p_msg =>'Raising invalid arg exc after create version api call',
485 p_module_name => l_module_name,
486 p_log_level => 5);
487 END IF;
488 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
489 END IF;
490
491 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
492 l_generation_flag := 'N';
493 l_msg_count := FND_MSG_PUB.Count_Msg;
494 IF p_pa_debug_mode = 'Y' THEN
495 pa_fp_gen_amount_utils.fp_debug
496 (p_called_mode => l_called_mode,
497 p_msg =>
498 'Value of l_msg_count after calling create_version api: '||l_msg_count ,
499 p_module_name => l_module_name,
500 p_log_level => 5);
501 END IF;
502
503 PA_DEBUG.g_err_stage := l_project_name_err_token || l_project_name ||
504 ' ' ||l_fin_plan_type_err_token || l_plan_type_name;
505 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
506 p_write_file => 'OUT',
507 p_write_mode => 1);
508 IF l_msg_count > 0 THEN
509 IF l_msg_count = 1 THEN
510 l_msg_data_in := l_msg_data;
511 PA_INTERFACE_UTILS_PUB.get_messages
512 (p_encoded => FND_API.G_FALSE,
513 p_msg_index => 1,
514 p_msg_count => 1,
515 p_msg_data => l_msg_data_in,
516 p_data => l_msg_data,
517 p_msg_index_out => l_msg_index_out);
518 --dbms_output.put_line('x msg data in msg count 1 '||x_msg_data);
519 --dbms_output.put_line('l msg data in msg count 1: '||l_msg_data);
520 --hr_utility.trace('l msg data in msg count 1: '||l_msg_data);
521 --dbms_output.put_line('p msg index out in msg count 1: '||l_msg_index_out);
522 PA_DEBUG.g_err_stage := l_msg_data;
523 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
524 p_write_file => 'OUT',
525 p_write_mode => 1);
526 ELSE
527 FOR j in 1 .. l_msg_count LOOP
528 --dbms_output.put_line('inside the error loop ');
529 l_msg_data_in := l_msg_data;
530 pa_interface_utils_pub.get_messages
531 (p_encoded => FND_API.G_FALSE,
532 p_msg_index => j,
533 p_msg_count => l_msg_count ,
534 p_msg_data => l_msg_data_in ,
535 p_data => l_msg_data,
536 p_msg_index_out => l_msg_index_out );
537 PA_DEBUG.g_err_stage := l_msg_data;
538 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
539 p_write_file => 'OUT',
540 p_write_mode => 1);
541 --dbms_output.put_line('error # '||j||' '|| substr(l_msg_Data,1,200));
542 END LOOP;
543 END IF;
544 END IF;
545 ELSE
546 COMMIT;
547 END IF; -- for the return status chk
548
549 --dbms_output.put_line('Value of plan_class_code b4 calling get_plan_version_dtls api: '||l_plan_class_code );
550 --hr_utility.trace('Value of plan_class_code b4 calling get_plan_version_dtls api: '||l_plan_class_code );
551 IF l_plan_class_code = 'FORECAST' AND
552 l_generation_flag = 'Y' THEN
553
554 IF p_pa_debug_mode = 'Y' THEN
555 pa_fp_gen_amount_utils.fp_debug
556 (p_called_mode => l_called_mode,
557 p_msg => 'Before calling
558 pa_fp_gen_amount_utils.get_plan_version_dtls',
559 p_module_name => l_module_name,
560 p_log_level => 5);
561 END IF;
562 --dbms_output.put_line('b4 calling get_plan_version_dtls api');
563 BEGIN
564 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
565 (P_BUDGET_VERSION_ID => lx_budget_version_id,
566 X_FP_COLS_REC => l_fp_cols_rec,
567 X_RETURN_STATUS => l_return_status,
568 X_MSG_COUNT => l_msg_count,
569 X_MSG_DATA => l_msg_data);
570 EXCEPTION
571 WHEN OTHERS THEN
572 l_gen_api_call_flag := 'N';
573 END;
574 -- dbms_output.put_line('Status after calling get_plan_version_dtls api: '||l_return_status);
575 -- hr_utility.trace('L_RETURN_Status after calling get_plan_version_dtls api: '||l_return_status);
576 IF p_pa_debug_mode = 'Y' THEN
577 pa_fp_gen_amount_utils.fp_debug
578 (p_called_mode => l_called_mode,
579 p_msg => 'Status after calling
580 pa_fp_gen_amount_utils.get_plan_version_dtls'
581 ||l_return_status,
582 p_module_name => l_module_name,
583 p_log_level => 5);
584 END IF;
585
586 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
587 IF p_pa_debug_mode = 'Y' THEN
588 pa_fp_gen_amount_utils.fp_debug
589 (p_called_mode => l_called_mode,
590 p_msg =>'Raising invalid arg exc after Get Plan ver dtls api call',
591 p_module_name => l_module_name,
592 p_log_level => 5);
593 END IF;
594 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
595 END IF;
596 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
597 l_gen_api_call_flag := 'N';
598 END IF;
599
600 l_unspent_amt_period :=
601 PA_FP_GEN_FCST_PG_PKG.GET_UNSPENT_AMT_PERIOD(lx_budget_version_id);
602 -- dbms_output.put_line('Value of l_unspent_amt_period: '||l_unspent_amt_period);
603 -- hr_utility.trace('Value of l_unspent_amt_period: '||l_unspent_amt_period);
604 l_act_from_period :=
605 PA_FP_GEN_FCST_PG_PKG.GET_ACT_FRM_PERIOD(lx_budget_version_id);
606 -- dbms_output.put_line('Value of l_act_from_period: '||l_act_from_period);
607 -- hr_utility.trace('Value of l_act_from_period: '||l_act_from_period);
608 l_act_to_period :=
609 PA_FP_GEN_FCST_PG_PKG.GET_ACT_TO_PERIOD(lx_budget_version_id);
610 -- hr_utility.trace('Value of l_act_from_period: '||l_act_from_period);
611 -- hr_utility.trace('Value of l_act_to_period: '||l_act_to_period);
612 --dbms_output.put_line('Value of l_act_to_period: '||l_act_to_period);
613 l_etc_from_period :=
614 PA_FP_GEN_FCST_PG_PKG.GET_ETC_FRM_PERIOD(lx_budget_version_id);
615 --dbms_output.put_line('Value of l_etc_from_period: '||l_etc_from_period);
616 --hr_utility.trace('Value of l_etc_frm_period: '||l_etc_from_period);
617 l_etc_to_period :=
618 PA_FP_GEN_FCST_PG_PKG.GET_ETC_TO_PERIOD(lx_budget_version_id);
619 --dbms_output.put_line('Value of l_etc_to_period: '||l_etc_to_period);
620 --hr_utility.trace('Value of l_etc_to_period: '||l_etc_to_period);
621 l_act_thru_period :=
622 PA_FP_GEN_FCST_PG_PKG.GET_ACTUALS_THRU_PERIOD_DTLS(lx_budget_version_id, 'PERIOD');
623 --dbms_output.put_line('Value of l_act_thru_period: '||l_act_thru_period);
624 --hr_utility.trace('Value of l_act_thru_period: '||l_act_thru_period);
625 l_act_thru_date := to_date(
626 PA_FP_GEN_FCST_PG_PKG.GET_ACTUALS_THRU_PERIOD_DTLS(lx_budget_version_id,'END_DATE'),'RRRRMMDD');
627 --dbms_output.put_line('Value of l_act_thru_date: '||l_act_thru_date);
628 --hr_utility.trace('Value of l_act_thru_date: '||l_act_thru_date);
629 --------------------------------------
630
631 -- Bug 4197956: We need to derive the Financial Plan / Work Plan generation
632 -- source plan version information only in the following cases:
633 -- 1) The generation source is Financial Plan / Work Plan respectively
634 -- (i.e. the Target is a Revenue only version).
635 -- 2) The generation source is Task Level Selection and at least one of the
636 -- financial tasks has ETC generation source as Financial Plan / Work Plan,
637 -- respectively.
638
639 l_fin_plan_src_dtls_flag := 'N';
640 l_workplan_src_dtls_flag := 'N';
641 --bug 9195544 : START : skkoppul : reset the local variables to NULL
642 l_etc_wp_struct_ver_id := NULL;
643 l_etc_fp_ver_name := NULL;
644 l_etc_fp_ver_id := NULL;
645 l_etc_wp_fin_ver_id := NULL;
646 l_etc_fp_type_id := NULL;
647 --bug 9195544 : END : skkoppul : reset the local variables to NULL
648
649 IF l_fp_cols_rec.x_gen_etc_src_code = lc_FinancialPlan THEN
650 l_fin_plan_src_dtls_flag := 'Y';
651 ELSIF l_fp_cols_rec.x_gen_etc_src_code = lc_WorkplanResources THEN
652 l_workplan_src_dtls_flag := 'Y';
653 ELSIF l_fp_cols_rec.x_gen_etc_src_code = lc_TaskLevelSel THEN
654 SELECT count(*) INTO l_fin_plan_count
655 FROM pa_tasks
656 WHERE project_id = l_proj_id
657 AND gen_etc_source_code = lc_FinancialPlan
658 AND rownum < 2;
659
660 IF l_fin_plan_count > 0 THEN
661 l_fin_plan_src_dtls_flag := 'Y';
662 END IF;
663
664 SELECT count(*) INTO l_workplan_count
665 FROM pa_tasks
666 WHERE project_id = l_proj_id
667 AND gen_etc_source_code = lc_WorkplanResources
668 AND rownum < 2;
669
670 IF l_workplan_count > 0 THEN
671 l_workplan_src_dtls_flag := 'Y';
672 END IF;
673 END IF;
674
675 IF l_workplan_src_dtls_flag = 'Y' THEN
676
677 IF l_fp_cols_rec.x_gen_src_wp_version_id is not NULL THEN
678
679 -- Mani
680 --hr_utility.trace('fp cols rec gen src wp version id not null '||
681 --l_fp_cols_rec.x_gen_src_wp_version_id );
682 l_etc_wp_fin_ver_id := l_fp_cols_rec.x_gen_src_wp_version_id;
683 select project_structure_version_id into l_etc_wp_struct_ver_id
684 from pa_budget_versions
685 where budget_version_id = l_etc_wp_fin_ver_id;
686
687 END IF;
688
689 IF l_fp_cols_rec.x_gen_src_wp_version_id is NULL THEN
690 l_versioning_enabled :=
691 PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_proj_id);
692 IF l_versioning_enabled = 'Y' THEN
693 /*Get latest published/current working/baselined work plan version id*/
694 l_gen_src_wp_ver_code := l_fp_cols_rec.x_gen_src_wp_ver_code;
695 IF (l_gen_src_wp_ver_code = 'LAST_PUBLISHED') THEN
696 l_etc_wp_struct_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(
697 P_PROJECT_ID => l_proj_id ) ;
698 -- hr_utility.trace('inside last published wp : '|| l_etc_wp_struct_ver_id );
699 IF l_etc_wp_struct_ver_id is null THEN
700 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
701 p_msg_name => 'PA_LATEST_WPID_NULL');
702 l_gen_api_call_flag := 'N';
703 --raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
704 END IF;
705 ELSIF (l_gen_src_wp_ver_code = 'CURRENT_WORKING') THEN
706 l_etc_wp_struct_ver_id :=
707 PA_PROJECT_STRUCTURE_UTILS.GET_CURRENT_WORKING_VER_ID(P_PROJECT_ID => l_proj_id );
708 IF l_etc_wp_struct_ver_id is null THEN
709 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
710 p_msg_name => 'PA_CW_WPID_NULL');
711 l_gen_api_call_flag := 'N';
712 --raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
713 END IF;
714 -- Bug 4426511: Changed 'BASELINE', which was INCORRECT, to 'BASELINED'.
715 ELSIF (l_gen_src_wp_ver_code = 'BASELINED') THEN
716 l_etc_wp_struct_ver_id :=
717 PA_PROJECT_STRUCTURE_UTILS.GET_BASELINE_STRUCT_VER(P_PROJECT_ID => l_proj_id );
718 IF l_etc_wp_struct_ver_id is null THEN
719 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
720 p_msg_name => 'PA_BASELINED_WPID_NULL');
721 l_gen_api_call_flag := 'N';
722 --raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
723 END IF;
724 END IF; -- end if for ver code
725 ELSE
726 -- versioning disabled case
727 l_etc_wp_struct_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(
728 P_PROJECT_ID => l_proj_id);
729 IF l_etc_wp_struct_ver_id is null THEN
730 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
731 p_msg_name => 'PA_LATEST_WPID_NULL');
732 l_gen_api_call_flag := 'N';
733 END IF;
734 END IF; -- version enabled chk
735 l_etc_wp_fin_ver_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id(
736 p_project_id => l_proj_id,
737 p_plan_type_id => l_plan_type_id,
738 p_proj_str_ver_id => l_etc_wp_struct_ver_id);
739 --hr_utility.trace('after calling fn etc wp fin ver id : '||
740 --l_etc_wp_fin_ver_id );
741 END IF; -- fp cols rec check
742 /*We need the strcut_ver_id for fcst_amt_gen,
743 wp_fin_plan_ver_id to update back to pa_proj_fp_options*/
744
745 END IF; -- l_workplan_src_dtls_flag
746
747 IF l_fin_plan_src_dtls_flag = 'Y' THEN
748
749 -- Bug 16181483 Start
750 -- Forecost Cost or Rev can only be generated from Cost Plan type source. For Cost and Rev Together Version type should be ALL.
751 SELECT fin_plan_preference_code
752 INTO l_fp_preference_code
753 FROM pa_proj_fp_options
754 WHERE project_id = l_proj_id
755 AND fin_plan_type_id = l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID
756 AND fin_plan_option_level_code =
757 PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
758
759 IF l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
760 l_version_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL;
761 ELSE
762 l_version_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST;
763 END IF;
764 -- Bug 16181483 End
765
766 l_gen_src_plan_ver_code := l_fp_cols_rec.X_GEN_SRC_PLAN_VER_CODE;
767 IF l_gen_src_plan_ver_code = 'CURRENT_BASELINED'
768 OR l_gen_src_plan_ver_code = 'ORIGINAL_BASELINED'
769 OR l_gen_src_plan_ver_code = 'CURRENT_APPROVED'
770 OR l_gen_src_plan_ver_code = 'ORIGINAL_APPROVED' THEN
771 /*Get the current baselined or original baselined version*/
772 IF P_PA_DEBUG_MODE = 'Y' THEN
773 pa_fp_gen_amount_utils.fp_debug
774 (p_called_mode => l_called_mode,
775 p_msg => 'Before calling pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info',
776 p_module_name => l_module_name,
777 p_log_level => 5);
778 END IF;
779 --hr_utility.trace('pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info');
780 pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info(
781 p_project_id => l_proj_id,
782 p_fin_plan_type_id => l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID,
783 p_version_type => l_version_type, --Bug 16181483 -- bug 7032123 skkoppul : changed COST to NULL
784 p_status_code => l_gen_src_plan_ver_code,
785 x_fp_options_id => l_fp_options_id,
786 x_fin_plan_version_id => l_etc_fp_ver_id,
787 x_return_status => l_return_status,
788 x_msg_count => l_msg_count,
789 x_msg_data => l_msg_data);
790 IF P_PA_DEBUG_MODE = 'Y' THEN
791 pa_fp_gen_amount_utils.fp_debug
792 (p_called_mode => l_called_mode,
793 p_msg =>'After calling pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info,return status is: '
794 ||l_return_status,
795 p_module_name => l_module_name,
796 p_log_level => 5);
797 END IF;
798
799
800 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
801 IF p_pa_debug_mode = 'Y' THEN
802 pa_fp_gen_amount_utils.fp_debug
803 (p_called_mode => l_called_mode,
804 p_msg=>'Raising invalid arg exc aft Get Curr Original ver api call',
805 p_module_name => l_module_name,
806 p_log_level => 5);
807 END IF;
808 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
809 END IF;
810 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
811 l_gen_api_call_flag := 'N';
812 END IF;
813
814
815 ELSIF l_gen_src_plan_ver_code = 'CURRENT_WORKING' THEN
816 /*Get the current working version*/
817 IF P_PA_DEBUG_MODE = 'Y' THEN
818 pa_fp_gen_amount_utils.fp_debug
819 (p_called_mode => l_called_mode,
820 p_msg => 'Before calling pa_fin_plan_utils.Get_Curr_Working_Version_Info',
821 p_module_name => l_module_name,
822 p_log_level => 5);
823 END IF;
824 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
825 p_project_id => l_proj_id,
826 p_fin_plan_type_id => l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID,
827 p_version_type => l_version_type, --Bug 16181483 -- bug 7032123 skkoppul : changed COST to NULL
828 x_fp_options_id => l_fp_options_id,
829 x_fin_plan_version_id => l_etc_fp_ver_id,
830 x_return_status => l_return_status,
831 x_msg_count => l_msg_count,
832 x_msg_data => l_msg_data);
833 IF P_PA_DEBUG_MODE = 'Y' THEN
834 pa_fp_gen_amount_utils.fp_debug
835 (p_called_mode => l_called_mode,
836 p_msg =>
837 'Aft calling pa_fin_plan_utils.Get_Curr_Working_Version_Info ret sta:'
838 ||l_return_status,
839 p_module_name => l_module_name,
840 p_log_level => 5);
841 END IF;
842
843 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
844 IF p_pa_debug_mode = 'Y' THEN
845 pa_fp_gen_amount_utils.fp_debug
846 (p_called_mode => l_called_mode,
847 p_msg=>'Raising invalid arg exc aft Get Curr Wkg ver api call',
848 p_module_name => l_module_name,
849 p_log_level => 5);
850 END IF;
851 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
852 END IF;
853 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
854 l_gen_api_call_flag := 'N';
855 END IF;
856 END IF;
857
858 IF l_etc_fp_ver_id IS NULL THEN
859 --hr_utility.trace('l_etc_fp_ver_id is null chk and raising error');
860 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
861 p_msg_name => 'PA_SRC_FP_VER_NULL');
862 l_gen_api_call_flag := 'N';
863 ELSE
864 SELECT version_name INTO l_etc_fp_ver_name
865 FROM pa_budget_versions
866 WHERE budget_version_id = l_etc_fp_ver_id;
867
868 l_etc_fp_type_id := l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID;
869 END IF;
870
871 END IF; -- l_fin_plan_src_dtls_flag
872
873 -- gboomina added for AAI requirement 8318932 - start
874 -- If Copy ETC from Plan flag is selected, then validate whether
875 -- Source Work plan and Financial Plan time phased and ETC Source for
876 -- Source Work plan is either 'Workplan Resources' or 'Financial Plan'
877 IF l_copy_etc_from_plan_flag = 'Y' THEN
878 BEGIN --Begining of the block for validation_for_copy_etc_flag
879 IF p_pa_debug_mode = 'Y' THEN
880 pa_fp_gen_amount_utils.fp_debug
881 (p_msg => 'Before calling
882 pa_fp_gen_fcst_pg_pkg.validation_for_copy_etc_flag',
883 p_module_name => l_module_name,
884 p_log_level => 5);
885 END IF;
886 -- Calling the following method to validate time phase and ETC source of
887 -- source plan
888 pa_fp_gen_fcst_pg_pkg.validation_for_copy_etc_flag
889 (p_project_id => l_fp_cols_rec.x_project_id,
890 p_wp_version_id => l_etc_wp_fin_ver_id,
891 p_etc_plan_version_id => l_etc_fp_ver_id,
892 x_return_status => l_return_status,
893 x_msg_count => l_msg_count,
894 x_msg_data => l_msg_data);
895 IF p_pa_debug_mode = 'Y' THEN
896 pa_fp_gen_amount_utils.fp_debug
897 (p_msg => 'Status after calling
898 pa_fp_gen_fcst_pg_pkg.validation_for_copy_etc_flag: '
899 ||l_return_status,
900 p_module_name => l_module_name,
901 p_log_level => 5);
902 END IF;
903 EXCEPTION
904 WHEN OTHERS THEN
905 rollback;
906 l_gen_api_call_flag := 'N';
907 l_return_status := FND_API.G_RET_STS_ERROR;
908 l_msg_count := 1;
909 l_msg_data := substr(sqlerrm,1,240);
910 IF P_PA_DEBUG_MODE = 'Y' THEN
911 pa_fp_gen_amount_utils.fp_debug
912 (p_called_mode => l_called_mode,
913 p_msg => 'Error: Validate Copy ETC from Plan'||l_msg_data,
914 p_module_name => l_module_name,
915 p_log_level => 5);
916 END IF;
917 END; --End of the block for validation_for_copy_etc_flag
918
919 END IF;
920 -- gboomina added for AAI requirement 8318932 - end
921
922 /*As of now, we have the l_etc_wp_struct_ver_id and l_etc_fp_ver_id*/
923 /*update the etc source version id back to pa_proj_fp_options*/
924 IF l_element_type_tab(i) = 'COST' THEN
925 UPDATE PA_PROJ_FP_OPTIONS
926 SET GEN_SRC_COST_PLAN_TYPE_ID = l_etc_fp_type_id,
927 GEN_SRC_COST_PLAN_VERSION_ID = l_etc_fp_ver_id,
928 GEN_SRC_COST_WP_VERSION_ID = l_etc_wp_fin_ver_id
929 WHERE fin_plan_version_id = lx_budget_version_id;
930 ELSIF l_element_type_tab(i) = 'REVENUE' THEN
931 UPDATE PA_PROJ_FP_OPTIONS
932 SET GEN_SRC_REV_PLAN_TYPE_ID = l_etc_fp_type_id,
933 GEN_SRC_REV_PLAN_VERSION_ID = l_etc_fp_ver_id,
934 GEN_SRC_REV_WP_VERSION_ID = l_etc_wp_fin_ver_id
935 WHERE fin_plan_version_id = lx_budget_version_id;
936 ELSIF l_element_type_tab(i) = 'ALL' THEN
937 UPDATE PA_PROJ_FP_OPTIONS
938 SET GEN_SRC_ALL_PLAN_TYPE_ID = l_etc_fp_type_id,
939 GEN_SRC_ALL_PLAN_VERSION_ID = l_etc_fp_ver_id,
940 GEN_SRC_ALL_WP_VERSION_ID = l_etc_wp_fin_ver_id
941 WHERE fin_plan_version_id = lx_budget_version_id;
942 END IF;
943
944 l_struct_sharing_code := PA_PROJECT_STRUCTURE_UTILS.
945 get_structure_sharing_code(p_project_id=>l_proj_id);
946
947 -- bug 11691789 : skkoppul added
948 UPDATE PA_BUDGET_VERSIONS
949 SET ACTUAL_AMTS_THRU_PERIOD = l_act_thru_period
950 WHERE BUDGET_VERSION_ID = lx_budget_version_id;
951
952 --hr_utility.trace('l_etc_fp_type_id : '||l_etc_fp_type_id);
953 IF P_PA_DEBUG_MODE = 'Y' THEN
954 pa_fp_gen_amount_utils.fp_debug
955 (p_called_mode => l_called_mode,
956 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB.GENERATE_FCST_AMT_WRP'||
957 'l_proj_id = '||l_proj_id,
958 p_module_name => l_module_name,
959 p_log_level => 5);
960
961 pa_fp_gen_amount_utils.fp_debug
962 (p_called_mode => l_called_mode,
963 p_msg => 'Budget version id :'||lx_budget_version_id,
964 p_module_name => l_module_name,
965 p_log_level => 5);
966 pa_fp_gen_amount_utils.fp_debug
967 (p_called_mode => l_called_mode,
968 p_msg => 'ver type : Unspent amt flag '||
969 l_element_type_tab(i)|| ' : '||
970 l_fp_cols_rec.x_gen_incl_unspent_amt_flag,
971 p_module_name => l_module_name,
972 p_log_level => 5);
973 pa_fp_gen_amount_utils.fp_debug
974 (p_called_mode => l_called_mode,
975 p_msg => 'unspent amt pd : Incu chg doc flag '||
976 l_unspent_amt_period|| ' : ' ||
977 l_fp_cols_rec.x_gen_incl_change_doc_flag,
978 p_module_name => l_module_name,
979 p_log_level => 5);
980 pa_fp_gen_amount_utils.fp_debug
981 (p_called_mode => l_called_mode,
982 p_msg => 'Incl Open cmt : Incu bill events ' ||
983 l_fp_cols_rec.x_gen_incl_open_comm_flag|| ' : ' ||
984 l_fp_cols_rec.x_gen_incl_bill_event_flag,
985 p_module_name => l_module_name,
986 p_log_level => 5);
987 pa_fp_gen_amount_utils.fp_debug
988 (p_called_mode => l_called_mode,
989 p_msg => 'Ret manual lines : ETC plan type id ' ||
990 l_fp_cols_rec.x_gen_ret_manual_line_flag || ' : ' ||
991 l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID,
992 p_module_name => l_module_name,
993 p_log_level => 5);
994 pa_fp_gen_amount_utils.fp_debug
995 (p_called_mode => l_called_mode,
996 p_msg => 'ver name : etc fp ver id ' ||
997 l_etc_fp_ver_name || ' : ' ||
998 l_etc_fp_ver_id,
999 p_module_name => l_module_name,
1000 p_log_level => 5);
1001 pa_fp_gen_amount_utils.fp_debug
1002 (p_called_mode => l_called_mode,
1003 p_msg => 'Act fr pd : Act to Pd ' ||
1004 l_act_from_period || ' : ' ||
1005 l_act_to_period,
1006 p_module_name => l_module_name,
1007 p_log_level => 5);
1008 pa_fp_gen_amount_utils.fp_debug
1009 (p_called_mode => l_called_mode,
1010 p_msg => 'ETC fr pd : ETC to Pd ' ||
1011 l_etc_from_period || ' : ' ||
1012 l_etc_to_period,
1013 p_module_name => l_module_name,
1014 p_log_level => 5);
1015 /* sysdate is used just for log message. The date should not be
1016 modified. */
1017 pa_fp_gen_amount_utils.fp_debug
1018 (p_called_mode => l_called_mode,
1019 p_msg => 'Actu thru pd : Actu thru Dt '||
1020 l_act_thru_period || ' : ' ||
1021 to_char(nvl(l_act_thru_date,trunc(sysdate)),'mm/dd/yyyy'),
1022 p_module_name => l_module_name,
1023 p_log_level => 5);
1024 pa_fp_gen_amount_utils.fp_debug
1025 (p_called_mode => l_called_mode,
1026 p_msg => 'ETC WP Str Ver id :' ||
1027 l_etc_wp_struct_ver_id,
1028 p_module_name => l_module_name,
1029 p_log_level => 5);
1030 END IF;
1031 /*
1032 hr_utility.trace('l_proj_id '||l_proj_id);
1033 hr_utility.trace('lx_budget_version_id '||lx_budget_version_id);
1034 hr_utility.trace('l_element_type_tab(i) ' ||l_element_type_tab(i));
1035 hr_utility.trace('l_fp_cols_rec.x_gen_incl_unspent_amt_flag '||l_fp_cols_rec.x_gen_incl_unspent_amt_flag);
1036 hr_utility.trace('l_unspent_amt_period '||l_unspent_amt_period);
1037 hr_utility.trace('l_fp_cols_rec.x_gen_incl_change_doc_flag'||l_fp_cols_rec.x_gen_incl_change_doc_flag);
1038 hr_utility.trace('l_fp_cols_rec.x_gen_incl_open_comm_flag '||l_fp_cols_rec.x_gen_incl_open_comm_flag);
1039 hr_utility.trace('l_fp_cols_rec.x_gen_incl_bill_event_flag '||l_fp_cols_rec.x_gen_incl_bill_event_flag);
1040 hr_utility.trace('l_fp_cols_rec.x_gen_ret_manual_line_flag '||l_fp_cols_rec.x_gen_ret_manual_line_flag);
1041 hr_utility.trace('l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID '||l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID);
1042 hr_utility.trace('l_etc_fp_ver_id '||l_etc_fp_ver_id);
1043 hr_utility.trace('l_etc_fp_ver_name '||l_etc_fp_ver_name);
1044 hr_utility.trace('l_act_from_period '||l_act_from_period);
1045 hr_utility.trace('l_act_to_period '||l_act_to_period);
1046 hr_utility.trace('l_etc_from_period '||l_etc_from_period);
1047 hr_utility.trace('l_etc_to_period '||l_etc_to_period);
1048 hr_utility.trace('l_act_thru_period l_Orig_Version_flag'||l_act_thru_period);
1049 hr_utility.trace('l_act_thru_date '||l_act_thru_date);
1050 hr_utility.trace('l_etc_wp_struct_ver_id '||l_etc_wp_struct_ver_id);
1051 hr_utility.trace('l_return_status before gen fcst amt '||l_return_status);
1052 hr_utility.trace('l_gen_api_call_flag before gen fcst amt '||l_gen_api_call_flag);
1053 */
1054
1055 IF l_gen_api_call_flag = 'Y' THEN
1056 BEGIN
1057 IF p_pa_debug_mode = 'Y' THEN
1058 pa_fp_gen_amount_utils.fp_debug
1059 ( p_called_mode => l_called_mode,
1060 p_msg => 'Before calling
1061 pa_fp_gen_amount_utils.validate_support_cases',
1062 p_module_name => l_module_name,
1063 p_log_level => 5 );
1064 END IF;
1065 PA_FP_GEN_AMOUNT_UTILS.VALIDATE_SUPPORT_CASES (
1066 P_FP_COLS_REC_TGT => l_fp_cols_rec,
1067 P_CALLING_CONTEXT => 'CONCURRENT',
1068 X_WARNING_MESSAGE => l_warning_message, /* Added for ER 4391321 */
1069 X_RETURN_STATUS => l_return_status,
1070 X_MSG_COUNT => l_msg_count,
1071 X_MSG_DATA => l_msg_data );
1072 IF p_pa_debug_mode = 'Y' THEN
1073 pa_fp_gen_amount_utils.fp_debug
1074 ( p_called_mode => l_called_mode,
1075 p_msg => 'Status after calling
1076 pa_fp_gen_amount_utils.validate_support_cases: '
1077 ||l_return_status,
1078 p_module_name => l_module_name,
1079 p_log_level => 5 );
1080 END IF;
1081 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1082 IF p_pa_debug_mode = 'Y' THEN
1083 pa_fp_gen_amount_utils.fp_debug
1084 (p_called_mode => l_called_mode,
1085 p_msg=>'Raising invalid arg exc aft validation api call',
1086 p_module_name => l_module_name,
1087 p_log_level => 5);
1088 END IF;
1089 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1090 END IF;
1091 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1092 l_gen_api_call_flag := 'N';
1093 END IF;
1094 PA_FP_GEN_FCST_AMT_PUB.GENERATE_FCST_AMT_WRP
1095 (P_PROJECT_ID => l_proj_id,
1096 P_BUDGET_VERSION_ID => lx_budget_version_id,
1097 P_FP_COLS_REC => l_fp_cols_rec,
1098 P_CALLED_MODE => 'CONCURRENT',
1099 P_COMMIT_FLAG => 'Y',
1100 P_INIT_MSG_FLAG => 'Y',
1101 P_VERSION_TYPE => l_element_type_tab(i),
1102 P_UNSPENT_AMT_FLAG => l_fp_cols_rec.x_gen_incl_unspent_amt_flag,
1103 P_UNSPENT_AMT_PERIOD => l_unspent_amt_period,
1104 P_INCL_CHG_DOC_FLAG => l_fp_cols_rec.x_gen_incl_change_doc_flag,
1105 P_INCL_OPEN_CMT_FLAG => l_fp_cols_rec.x_gen_incl_open_comm_flag,
1106 P_INCL_BILL_EVT_FLAG => l_fp_cols_rec.x_gen_incl_bill_event_flag,
1107 P_RET_MANUAL_LNS_FLAG => l_fp_cols_rec.x_gen_ret_manual_line_flag,
1108 P_PLAN_TYPE_ID => NULL,
1109 P_PLAN_VERSION_ID => NULL,
1110 P_PLAN_VERSION_NAME => NULL,
1111 P_ETC_PLAN_TYPE_ID => l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID,
1112 P_ETC_PLAN_VERSION_ID => l_etc_fp_ver_id,
1113 P_ETC_PLAN_VERSION_NAME=> l_etc_fp_ver_name,
1114 P_ACTUALS_FROM_PERIOD => l_act_from_period,
1115 P_ACTUALS_TO_PERIOD => l_act_to_period,
1116 P_ETC_FROM_PERIOD => l_etc_from_period,
1117 P_ETC_TO_PERIOD => l_etc_to_period,
1118 P_ACTUALS_THRU_PERIOD => l_act_thru_period,
1119 P_ACTUALS_THRU_DATE => l_act_thru_date,
1120 P_WP_STRUCTURE_VERSION_ID =>l_etc_wp_struct_ver_id,
1121 X_RETURN_STATUS => l_return_status,
1122 X_MSG_COUNT => l_msg_count,
1123 X_MSG_DATA => l_msg_data );
1124 IF P_PA_DEBUG_MODE = 'Y' THEN
1125 pa_fp_gen_amount_utils.fp_debug
1126 (p_called_mode => l_called_mode,
1127 p_msg =>
1128 'After calling PA_FP_GEN_FCST_AMT_PUB.GENERATE_FCST_AMT_WRP'||
1129 ' ret status is:'||l_return_status,
1130 p_module_name => l_module_name,
1131 p_log_level => 5);
1132 END IF;
1133 -- hr_utility.trace('l_return_status after GENERATE_FCST_AMT_WRP '||l_return_status);
1134 -- hr_utility.trace('l_version_generation GENERATE_FCST_AMT_WRP '||l_generation_flag);
1135
1136 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1137 IF p_pa_debug_mode = 'Y' THEN
1138 pa_fp_gen_amount_utils.fp_debug
1139 (p_called_mode => l_called_mode,
1140 p_msg=>'Raising invalid arg exc aft Fcst Gen api call',
1141 p_module_name => l_module_name,
1142 p_log_level => 5);
1143 END IF;
1144 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1145 END IF;
1146 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1147 -- hr_utility.trace('EXCEPTION ERROR FROM GENERATE_FCST_AMT_WRP');
1148 l_gen_api_call_flag := 'N';
1149 END IF;
1150 EXCEPTION
1151 /*xxxxx*/
1152 WHEN OTHERS THEN
1153 -- hr_utility.trace('Inside Forecast Gen others Ex');
1154 rollback;
1155 l_return_status := FND_API.G_RET_STS_ERROR;
1156 l_msg_count := 1;
1157 l_msg_data := substr(sqlerrm,1,240);
1158 --dbms_output.put_line('error msg :'||x_msg_data);
1159 IF P_PA_DEBUG_MODE = 'Y' THEN
1160 pa_fp_gen_amount_utils.fp_debug
1161 (p_called_mode => l_called_mode,
1162 p_msg => 'Forecast Gen Error'||l_msg_data,
1163 p_module_name => l_module_name,
1164 p_log_level => 5);
1165 END IF;
1166 END; --End of the block for FORCAST AMT GEN
1167
1168
1169 END IF; --for IF condition checking the l_gen_api_call_flag
1170 -- hr_utility.trace('l_return_status after FCST GEN '||l_return_status);
1171 -- hr_utility.trace('l_gen_api_call_flag _flag after FCST GEN '||l_gen_api_call_flag);
1172 ELSIF l_plan_class_code = 'BUDGET' AND
1173 l_generation_flag = 'Y' AND
1174 l_gen_api_call_flag = 'Y' THEN
1175 IF p_pa_debug_mode = 'Y' THEN
1176 pa_fp_gen_amount_utils.fp_debug
1177 (p_called_mode => l_called_mode,
1178 p_msg =>
1179 'Before calling PA_FP_WP_GEN_BUDGET_AMT_PUB.GENERATE_WP_BUDGET_AMT'||
1180 'Proj Id : '||to_char(l_proj_id) ||
1181 'Budget Version id : '|| lx_budget_version_id,
1182 p_module_name => l_module_name,
1183 p_log_level => 5);
1184 END IF;
1185 --dbms_output.put_line('before budget generation');
1186 --dbms_output.put_line('before calling GENERATE_BUDGET_AMT_WRP');
1187 -- hr_utility.trace('before calling GENERATE_BUDGET_AMT_WRP');
1188 -- hr_utility.trace('l_return_status after FCST GEN '||l_return_status);
1189 -- hr_utility.trace('l_gen_api_call_flag after FCST GEN '||l_gen_api_call_flag);
1190 -- hr_utility.trace('l_proj_id GENERATE_BUDGET_AMT_WRP '|| l_proj_id);
1191 -- hr_utility.trace('lx_budget_version_id GENERATE_BUDGET_AMT_WRP '||lx_budget_version_id);
1192 -- hr_utility.trace(' l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID '|| l_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID);
1193
1194 BEGIN
1195 PA_FP_GEN_BUDGET_AMT_PUB.GENERATE_BUDGET_AMT_WRP (
1196 P_PROJECT_ID => l_proj_id,
1197 P_BUDGET_VERSION_ID => lx_budget_version_id,
1198 P_CALLED_MODE => 'CONCURRENT',
1199 P_COMMIT_FLAG => 'Y',
1200 P_INIT_MSG_FLAG => 'Y',
1201 X_WARNING_MESSAGE => l_warning_message, /* Added for ER 4391321 */
1202 X_RETURN_STATUS => l_return_status,
1203 X_MSG_COUNT => l_msg_count,
1204 X_MSG_DATA => l_msg_data );
1205 -- dbms_output.put_line('after budget generation l_rest_status: '||l_return_status);
1206 -- hr_utility.trace('after budget generation l_rest_status: '||l_return_status);
1207 -- dbms_output.put_line('after budget generation lx_budget_version_id: '||lx_budget_version_id);
1208 -- hr_utility.trace('after budget generation lx_budget_version_id: '||lx_budget_version_id);
1209 -- dbms_output.put_line('after budget generation l_proj_id: '||l_proj_id);
1210 -- hr_utility.trace('after budget generation l_proj_id: '||l_proj_id);
1211 -- dbms_output.put_line(': '||FND_API.G_RET_STS_SUCCESS);
1212 -- hr_utility.trace('FND API. G RET STS SUCCESS: '||FND_API.G_RET_STS_SUCCESS);
1213 IF p_pa_debug_mode = 'Y' THEN
1214 pa_fp_gen_amount_utils.fp_debug
1215 (p_called_mode => l_called_mode,
1216 p_msg => 'Status after calling
1217 PA_FP_WP_GEN_BUDGET_AMT_PUB.GENERATE_WP_BUDGET_AMT:'
1218 ||l_return_status,
1219 p_module_name => l_module_name,
1220 p_log_level => 5);
1221 END IF;
1222 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1223 IF p_pa_debug_mode = 'Y' THEN
1224 pa_fp_gen_amount_utils.fp_debug
1225 (p_called_mode => l_called_mode,
1226 p_msg=>'Raising invalid arg exc aft Bdgt Gen api call',
1227 p_module_name => l_module_name,
1228 p_log_level => 5);
1229 END IF;
1230 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1231 END IF;
1232 /*xxxxxx*/
1233 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1234 -- hr_utility.trace('EXCEPTION ERROR FROM GENERATE_BUDGET_AMT_WRP');
1235 l_gen_api_call_flag := 'N';
1236 END IF;
1237 EXCEPTION
1238 WHEN OTHERS THEN
1239 -- hr_utility.trace('Inside others Ex');
1240 rollback;
1241 l_return_status := FND_API.G_RET_STS_ERROR;
1242 l_msg_count := 1;
1243 l_msg_data := substr(sqlerrm,1,240);
1244 --dbms_output.put_line('error msg :'||x_msg_data);
1245 IF P_PA_DEBUG_MODE = 'Y' THEN
1246 pa_fp_gen_amount_utils.fp_debug
1247 (p_called_mode => l_called_mode,
1248 p_msg => 'Budget Gen Error'||l_msg_data,
1249 p_module_name => l_module_name,
1250 p_log_level => 5);
1251 END IF;
1252 END; --End of block for Budget Amt Gen
1253
1254
1255
1256 END IF;--For IF condition for BUDGET
1257
1258 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1259 -- hr_utility.trace('INVALID EXCEPTION!!!');
1260 IF p_pa_debug_mode = 'Y' THEN
1261 pa_fp_gen_amount_utils.fp_debug
1262 (p_called_mode => l_called_mode,
1263 p_msg=>'Raising invalid arg exc aft Fcst / Bdgt Gen api call',
1264 p_module_name => l_module_name,
1265 p_log_level => 5);
1266 END IF;
1267 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1268 END IF;
1269 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1270 l_gen_api_call_flag := 'N';
1271 END IF;
1272
1273 IF l_gen_api_call_flag = 'N' THEN
1274 l_msg_count := FND_MSG_PUB.Count_Msg;
1275 IF p_pa_debug_mode = 'Y' THEN
1276 pa_fp_gen_amount_utils.fp_debug
1277 (p_called_mode => l_called_mode,
1278 p_msg =>
1279 'Value of l_msg_count after calling Budget / Forecast Gen API'
1280 ||l_msg_count,
1281 p_module_name => l_module_name,
1282 p_log_level => 5);
1283 END IF;
1284 IF l_msg_count > 0 THEN
1285 PA_DEBUG.g_err_stage := l_project_name_err_token || l_project_name ||
1286 ' ' ||l_fin_plan_type_err_token || l_plan_type_name;
1287 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1288 p_write_file => 'OUT',
1289 p_write_mode => 1);
1290 IF l_msg_count = 1 THEN
1291 l_msg_data_in := l_msg_data;
1292 PA_INTERFACE_UTILS_PUB.get_messages
1293 (p_encoded => FND_API.G_FALSE,
1294 p_msg_index => 1,
1295 p_msg_count => 1,
1296 p_msg_data => l_msg_data_in ,
1297 p_data => l_msg_data,
1298 p_msg_index_out => l_msg_index_out);
1299
1300 PA_DEBUG.g_err_stage := l_msg_data;
1301 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1302 p_write_file => 'OUT',
1303 p_write_mode => 1);
1304 --dbms_output.put_line('x msg data in msg count 1 '||x_msg_data);
1305 --dbms_output.put_line('l msg data in msg count 1: '||l_msg_data);
1306 --dbms_output.put_line('p msg index out in msg count 1: '||l_msg_index_out);
1307 ELSE
1308 FOR j in 1 .. l_msg_count LOOP
1309 --dbms_output.put_line('inside the error loop ');
1310 l_msg_data_in := l_msg_data;
1311 pa_interface_utils_pub.get_messages
1312 (p_encoded => FND_API.G_FALSE,
1313 p_msg_index => j,
1314 p_msg_count => l_msg_count ,
1315 p_msg_data => l_msg_data_in ,
1316 p_data => l_msg_data,
1317 p_msg_index_out => l_msg_index_out );
1318
1319 PA_DEBUG.g_err_stage := l_msg_data;
1320 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1321 p_write_file => 'OUT',
1322 p_write_mode => 1);
1323 -- dbms_output.put_line('error # '||j||' '||substr(l_msg_Data,1,200));
1324 END LOOP;
1325 END IF;
1326 END IF;
1327 END IF;
1328 /* end if for plan class code */
1329
1330 END LOOP;
1331 -- gboomina added for AAI requirements 8318932 - start
1332 -- Added code to handle error after processing each plan type
1333 END IF;
1334 IF l_plan_type_validated = 'N' THEN
1335 l_msg_count := FND_MSG_PUB.Count_Msg;
1336 IF p_pa_debug_mode = 'Y' THEN
1337 pa_fp_gen_amount_utils.fp_debug
1338 (p_called_mode => l_called_mode,
1339 p_msg =>
1340 'Value of l_msg_count after processing Plan Type :'||l_plan_type_name
1341 ||l_msg_count,
1342 p_module_name => l_module_name,
1343 p_log_level => 5);
1344 END IF;
1345 IF l_msg_count > 0 THEN
1346 PA_DEBUG.g_err_stage := l_project_name_err_token || l_project_name ||
1347 ' ' ||l_fin_plan_type_err_token || l_plan_type_name;
1348 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1349 p_write_file => 'OUT',
1350 p_write_mode => 1);
1351 IF l_msg_count = 1 THEN
1352 PA_INTERFACE_UTILS_PUB.get_messages
1353 (p_encoded => FND_API.G_FALSE,
1354 p_msg_index => 1,
1355 p_msg_count => 1,
1356 p_msg_data => l_msg_data ,
1357 p_data => l_msg_data,
1358 p_msg_index_out => l_msg_index_out);
1359
1360 PA_DEBUG.g_err_stage := l_msg_data;
1361 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1362 p_write_file => 'OUT',
1363 p_write_mode => 1);
1364 ELSE
1365 FOR j in 1 .. l_msg_count LOOP
1366 pa_interface_utils_pub.get_messages
1367 (p_encoded => FND_API.G_FALSE,
1368 p_msg_index => j,
1369 p_msg_count => l_msg_count ,
1370 p_msg_data => l_msg_data ,
1371 p_data => l_msg_data,
1372 p_msg_index_out => l_msg_index_out );
1373
1374 PA_DEBUG.g_err_stage := l_msg_data;
1375 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1376 p_write_file => 'OUT',
1377 p_write_mode => 1);
1378 END LOOP;
1379 END IF;
1380 END IF;
1381 END IF;
1382 -- gboomina added for AAI requirements 8318932 - end
1383 END LOOP;
1384
1385 DBMS_SQL.CLOSE_CURSOR(sql_cursor); -- Bug 5715252 Cursor is closed
1386
1387 IF P_PA_DEBUG_MODE = 'Y' THEN
1388 PA_DEBUG.RESET_CURR_FUNCTION;
1389 END IF;
1390 EXCEPTION
1391 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1392 -- hr_utility.trace('Inside invalid arg Ex');
1393 l_msg_count := FND_MSG_PUB.count_msg;
1394 IF l_msg_count = 1 THEN
1395 PA_INTERFACE_UTILS_PUB.get_messages
1396 ( p_encoded => FND_API.G_TRUE,
1397 p_msg_index => 1,
1398 p_msg_count => l_msg_count,
1399 p_msg_data => l_msg_data,
1400 p_data => l_data,
1401 p_msg_index_out => l_msg_index_out);
1402 l_msg_data := l_data;
1403 END IF;
1404
1405 ROLLBACK;
1406
1407 -- hr_utility.trace('Outside Ex');
1408 -- hr_utility.trace('Outside-l_return_status'||l_return_status);
1409 l_return_status := FND_API.G_RET_STS_ERROR;
1410 -- hr_utility.trace('Outside Error-l_return_status'||l_return_status);
1411
1412 IF P_PA_DEBUG_MODE = 'Y' THEN
1413 pa_fp_gen_amount_utils.fp_debug
1414 (p_called_mode => l_called_mode,
1415 p_msg => 'Invalid Arg Exc Error'||substr(sqlerrm, 1, 240),
1416 p_module_name => l_module_name,
1417 p_log_level => 5);
1418 PA_DEBUG.RESET_CURR_FUNCTION;
1419 END IF;
1420
1421 retcode := '2';
1422 errbuff := substr(sqlerrm,1,240);
1423
1424 /* Start Bug 5715252 */
1425 IF DBMS_SQL.IS_OPEN(sql_cursor) THEN
1426 DBMS_SQL.CLOSE_CURSOR(sql_cursor);
1427 END IF;
1428 /* End Bug 5715252 */
1429
1430 RAISE;
1431
1432 WHEN OTHERS THEN
1433 -- hr_utility.trace('Inside others Ex');
1434 rollback;
1435 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436 l_msg_count := 1;
1437 l_msg_data := substr(sqlerrm,1,240);
1438 --dbms_output.put_line('error msg :'||x_msg_data);
1439 FND_MSG_PUB.add_exc_msg
1440 ( p_pkg_name => ' PA_FP_GEN_AMT_WRP_PKG',
1441 p_procedure_name => 'GEN_AMT_WRP',
1442 p_error_text => substr(sqlerrm,1,240));
1443 IF P_PA_DEBUG_MODE = 'Y' THEN
1444 pa_fp_gen_amount_utils.fp_debug
1445 (p_called_mode => l_called_mode,
1446 p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1447 p_module_name => l_module_name,
1448 p_log_level => 5);
1449 PA_DEBUG.RESET_CURR_FUNCTION;
1450 END IF;
1451 retcode := '2';
1452 errbuff := substr(sqlerrm,1,240);
1453
1454 /* Start Bug 5715252 */
1455 IF DBMS_SQL.IS_OPEN(sql_cursor) THEN
1456 DBMS_SQL.CLOSE_CURSOR(sql_cursor);
1457 END IF;
1458 /* End Bug 5715252 */
1459
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461
1462 END GEN_AMT_WRP;
1463
1464 END PA_FP_GEN_AMT_WRP_PKG;