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