[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_FCST_PG_PKG
Source
1 PACKAGE body PA_FP_GEN_FCST_PG_PKG as
2 /* $Header: PAFPGFPB.pls 120.13.12020000.2 2013/05/16 06:37:12 djambhek ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 FUNCTION GET_REV_GEN_METHOD( P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE)
7 RETURN VARCHAR2 IS
8
9 x_rev_gen_method VARCHAR2(3);
10 l_error_msg VARCHAR2(30);
11 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_REV_GEN_METHOD';
12
13 BEGIN
14 IF p_pa_debug_mode = 'Y' THEN
15 pa_debug.set_curr_function( p_function => 'GET_REV_GEN_METHOD',
16 p_debug_mode => p_pa_debug_mode);
17 END IF;
18
19 -- Bug 4711164: Previously, distribution_rule was selected from
20 -- pa_projects_all and passed to Get_Revenue_Generation_Method.
21 -- However, it turns out that the lower level API ignores this
22 -- parameter value and derives the value on its own. Code to get
23 -- the distribution_rule has been removed to improve performance.
24
25 --Calling the get rev gen method to get the value for l_rev_gen_method
26 IF p_pa_debug_mode = 'Y' THEN
27 pa_fp_gen_amount_utils.fp_debug
28 (p_msg => 'Before calling
29 PA_RATE_PVT_PKG.Get_Revenue_Generation_Method',
30 p_module_name => l_module_name,
31 p_log_level => 5);
32 END IF;
33 PA_RATE_PVT_PKG.Get_Revenue_Generation_Method
34 (P_PROJECT_ID => p_project_id,
35 P_DISTRIBUTION_RULE => null, -- Modified for Bug 4711164
36 X_REV_GEN_METHOD => x_rev_gen_method,
37 X_ERROR_MSG => l_error_msg );
38 IF p_pa_debug_mode = 'Y' THEN
39 pa_fp_gen_amount_utils.fp_debug
40 (p_msg => 'After calling
41 PA_RATE_PVT_PKG.Get_Revenue_Generation_Method',
42 p_module_name => l_module_name,
43 p_log_level => 5);
44 END IF;
45 IF p_pa_debug_mode = 'Y' THEN
46 PA_DEBUG.Reset_Curr_Function;
47 END IF;
48 RETURN x_rev_gen_method;
49
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 IF p_pa_debug_mode = 'Y' THEN
53 PA_DEBUG.Reset_Curr_Function;
54 END IF;
55 RETURN NULL;
56 WHEN OTHERS THEN
57 IF p_pa_debug_mode = 'Y' THEN
58 PA_DEBUG.Reset_Curr_Function;
59 END IF;
60 RETURN NULL;
61 END;
62
63 FUNCTION GET_ACTUALS_THRU_PERIOD_DTLS(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
64 P_CONTEXT IN VARCHAR2)
65 RETURN VARCHAR2 IS
66
67 x_period_name PA_PERIODS_ALL.PERIOD_NAME%TYPE;
68 l_end_date PA_PERIODS_ALL.END_DATE%TYPE;
69 l_end_date1 PA_PERIODS_ALL.END_DATE%TYPE;
70 l_end_date2 PA_PERIODS_ALL.END_DATE%TYPE;
71 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
72 l_ret_status VARCHAR2(100);
73 l_msg_count NUMBER;
74 l_msg_data VARCHAR2(2000);
75 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_ACTUALS_THRU_PERIOD_DTLS';
76
77 BEGIN
78 IF p_pa_debug_mode = 'Y' THEN
79 pa_debug.set_curr_function( p_function => 'GET_ACTUALS_THRU_PERIOD_DTLS',
80 p_debug_mode => p_pa_debug_mode);
81 END IF;
82 --Calling the Util API
83 IF p_pa_debug_mode = 'Y' THEN
84 pa_fp_gen_amount_utils.fp_debug
85 (p_msg => 'Before calling
86 pa_fp_gen_amount_utils.get_plan_version_dtls',
87 p_module_name => l_module_name,
88 p_log_level => 5);
89 END IF;
90 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
91 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
92 X_FP_COLS_REC => l_fp_cols_rec,
93 X_RETURN_STATUS => L_RET_STATUS,
94 X_MSG_COUNT => L_MSG_COUNT,
95 X_MSG_DATA => L_MSG_DATA);
96 IF L_RET_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
97 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
98 END IF;
99 IF p_pa_debug_mode = 'Y' THEN
100 pa_fp_gen_amount_utils.fp_debug
101 (p_msg => 'Status after calling
102 pa_fp_gen_amount_utils.get_plan_version_dtls: '
103 ||L_RET_STATUS,
104 p_module_name => l_module_name,
105 p_log_level => 5);
106 END IF;
107 --dbms_output.put_line('Status of get plan version dtls api: '||L_RETURN_STATUS);
108 --Last period means Last Closed Period
109 IF l_fp_cols_rec.x_gen_actual_amts_thru_code = 'LAST_PERIOD' AND
110 l_fp_cols_rec.x_time_phased_code = 'P' THEN
111
112 SELECT PERIOD_NAME, END_DATE
113 INTO x_period_name,l_end_date
114 FROM PA_PERIODS_ALL
115 WHERE ORG_ID = l_fp_cols_rec.x_org_id
116 AND STATUS = 'C'
117 AND END_DATE = (SELECT MAX(END_DATE)
118 FROM PA_PERIODS_ALL
119 WHERE ORG_ID = l_fp_cols_rec.x_org_id
120 AND END_DATE < TRUNC(SYSDATE)
121 AND STATUS = 'C');
122
123 ELSIF l_fp_cols_rec.x_gen_actual_amts_thru_code = 'LAST_PERIOD' AND
124 l_fp_cols_rec.x_time_phased_code = 'G' THEN
125
126 SELECT PERIOD_NAME, END_DATE
127 INTO x_period_name,l_end_date
128 FROM GL_PERIOD_STATUSES
129 WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
130 AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
131 AND ADJUSTMENT_PERIOD_FLAG = 'N'
132 AND CLOSING_STATUS = 'C'
133 AND END_DATE = (SELECT MAX(END_DATE)
134 FROM GL_PERIOD_STATUSES
135 WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
136 AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
137 AND ADJUSTMENT_PERIOD_FLAG = 'N'
138 AND END_DATE < TRUNC(SYSDATE)
139 AND CLOSING_STATUS = 'C');
140
141 ELSIF l_fp_cols_rec.x_gen_actual_amts_thru_code = 'PRIOR_PERIOD' AND
142 l_fp_cols_rec.x_time_phased_code = 'P' THEN
143
144 SELECT period_name, end_date
145 INTO x_period_name,l_end_date
146 FROM pa_periods_all
147 WHERE end_date =
148 (SELECT max(end_date)
149 FROM pa_periods_all
150 WHERE org_id = l_fp_cols_rec.x_org_id
151 AND end_date <
152 (SELECT end_date
153 FROM pa_periods_all
154 WHERE trunc(sysdate) between start_date and end_date
155 AND org_id = l_fp_cols_rec.x_org_id) )
156 AND org_id = l_fp_cols_rec.x_org_id;
157
158 ELSIF l_fp_cols_rec.x_gen_actual_amts_thru_code = 'PRIOR_PERIOD' AND
159 l_fp_cols_rec.x_time_phased_code = 'G' THEN
160
161 SELECT period_name, end_date
162 INTO x_period_name, l_end_date
163 FROM gl_period_statuses
164 WHERE end_date =
165 (SELECT max(end_date)
166 FROM gl_period_statuses
167 WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
168 AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
169 AND adjustment_period_flag = 'N'
170 AND end_date <
171 (SELECT end_date
172 FROM gl_period_statuses
173 WHERE trunc(sysdate) between start_date and end_date
174 AND APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
175 AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
176 AND ADJUSTMENT_PERIOD_FLAG = 'N'))
177 AND APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
178 AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
179 AND ADJUSTMENT_PERIOD_FLAG = 'N';
180 /* CURRENT_PERIOD - last summarization run date */
181 ELSIF l_fp_cols_rec.x_gen_actual_amts_thru_code = 'CURRENT_PERIOD' THEN
182 --bug#11695214
183 l_end_date := PJI_PJP_EXTRACTION_UTILS.LAST_PJP_EXTR_DATE(l_fp_cols_rec.X_PROJECT_ID);
184
185 /* Get period_name based on the l_end_date calculated for CURRENT_PERIOD bug4034021 */
186 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
187 SELECT period_name, end_date
188 INTO x_period_name, l_end_date2
189 FROM pa_periods_all
190 WHERE org_id = l_fp_cols_rec.x_org_id
191 AND l_end_date between start_date and end_date;
192
193 ELSIF ltrim(rtrim(l_fp_cols_rec.x_time_phased_code)) = 'G' THEN
194 SELECT period_name, end_date
195 INTO x_period_name, l_end_date2
196 FROM gl_period_statuses
197 WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
198 AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
199 AND adjustment_period_flag = 'N'
200 AND l_end_date between start_date and end_date;
201 END IF;
202 l_end_date := l_end_date2;
203 END IF;
204
205 IF P_CONTEXT = 'PERIOD' THEN
206 IF p_pa_debug_mode = 'Y' THEN
207 PA_DEBUG.Reset_Curr_Function;
208 END IF;
209 RETURN x_period_name;
210 ELSIF P_CONTEXT = 'END_DATE' THEN
211 IF l_end_date IS NULL THEN
212 l_end_date := trunc(sysdate);
213 END IF;
214 l_end_date1 := l_end_date;
215 IF l_fp_cols_rec.x_time_phased_code = 'G' THEN
216 SELECT end_date into l_end_date1
217 FROM gl_period_statuses
218 WHERE l_end_date between start_date and end_date
219 AND APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
220 AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
221 AND ADJUSTMENT_PERIOD_FLAG = 'N';
222 ELSIF l_fp_cols_rec.x_time_phased_code = 'P' THEN
223 SELECT end_date into l_end_date1
224 FROM pa_periods_all
225 WHERE l_end_date between start_date and end_date
226 AND org_id = l_fp_cols_rec.x_org_id;
227 END IF;
228 IF p_pa_debug_mode = 'Y' THEN
229 PA_DEBUG.Reset_Curr_Function;
230 END IF;
231 RETURN to_char(nvl(l_end_date1,trunc(sysdate)),'RRRRMMDD');
232 END IF;
233 IF p_pa_debug_mode = 'Y' THEN
234 PA_DEBUG.Reset_Curr_Function;
235 END IF;
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 IF P_CONTEXT = 'PERIOD' THEN
239 IF p_pa_debug_mode = 'Y' THEN
240 PA_DEBUG.Reset_Curr_Function;
241 END IF;
242 RETURN NULL;
243 ELSIF P_CONTEXT = 'END_DATE' THEN
244 /*To address bug 4233703: when specified period doesn't exist,
245 NUll becomes actuals_thru_period; end date of the current period
246 where sysdate falls into becomes actuals_thru_date.*/
247 BEGIN
248 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
249 SELECT end_date
250 INTO l_end_date
251 FROM pa_periods_all
252 WHERE org_id = l_fp_cols_rec.x_org_id
253 AND trunc(sysdate) between start_date and end_date;
254 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
255 SELECT end_date
256 INTO l_end_date
257 FROM gl_period_statuses
258 WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
259 AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
260 AND adjustment_period_flag = 'N'
261 AND trunc(sysdate) between start_date and end_date;
262 ELSE
263 l_end_date := trunc(sysdate);
264 END IF;
265 EXCEPTION
266 WHEN OTHERS THEN
267 l_end_date := trunc(sysdate);
268 END ;
269 IF p_pa_debug_mode = 'Y' THEN
270 PA_DEBUG.Reset_Curr_Function;
271 END IF;
272 RETURN to_char(NVL(l_end_date, trunc(sysdate)),'RRRRMMDD');
273 END IF;
274 WHEN OTHERS THEN
275 IF P_CONTEXT = 'PERIOD' THEN
276 IF p_pa_debug_mode = 'Y' THEN
277 PA_DEBUG.Reset_Curr_Function;
278 END IF;
279 RETURN NULL;
280 ELSIF P_CONTEXT = 'END_DATE' THEN
281 IF p_pa_debug_mode = 'Y' THEN
282 PA_DEBUG.Reset_Curr_Function;
283 END IF;
284 RETURN to_char(trunc(sysdate),'RRRRMMDD');
285 END IF;
286 END;
287
288 FUNCTION GET_ACT_FRM_PERIOD(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
289 RETURN VARCHAR2 IS
290 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_ACT_FRM_PERIOD';
291
292
293 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
294 l_return_status VARCHAR2(10);
295 l_msg_count NUMBER;
296 l_msg_data VARCHAR2(2000);
297 l_data VARCHAR2(2000);
298
299 l_period_name PA_PERIODS_ALL.PERIOD_NAME%TYPE;
300
301 BEGIN
302 IF p_pa_debug_mode = 'Y' THEN
303 pa_debug.set_curr_function( p_function => 'GET_ACT_FRM_PERIOD',
304 p_debug_mode => p_pa_debug_mode);
305 END IF;
306 --Calling the Util API
307 IF p_pa_debug_mode = 'Y' THEN
308 pa_fp_gen_amount_utils.fp_debug
309 (p_msg => 'Before calling
310 pa_fp_gen_amount_utils.get_plan_version_dtls',
311 p_module_name => l_module_name,
312 p_log_level => 5);
313 END IF;
314 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
315 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
316 X_FP_COLS_REC => l_fp_cols_rec,
317 X_RETURN_STATUS => l_RETURN_STATUS,
318 X_MSG_COUNT => l_MSG_COUNT,
319 X_MSG_DATA => l_MSG_DATA);
320 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
321 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
322 END IF;
323 IF p_pa_debug_mode = 'Y' THEN
324 pa_fp_gen_amount_utils.fp_debug
325 (p_msg => 'Status after calling
326 pa_fp_gen_amount_utils.get_plan_version_dtls: '
327 ||l_RETURN_STATUS,
328 p_module_name => l_module_name,
329 p_log_level => 5);
330 END IF;
331
332 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
333
334 SELECT p.period_name
335 INTO l_period_name
336 FROM pa_periods_all p, pa_projects_all proj
337 WHERE p.org_id = l_fp_cols_rec.x_org_id
338 AND proj.project_id = l_fp_cols_rec.x_project_id
339 AND proj.start_date between p.start_date and p.end_date;
340
341 IF p_pa_debug_mode = 'Y' THEN
342 PA_DEBUG.Reset_Curr_Function;
343 END IF;
344 RETURN l_period_name;
345
346 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
347
348 SELECT g.period_name
349 INTO l_period_name
350 FROM gl_period_statuses g, pa_projects_all proj
351 WHERE g.application_id = PA_PERIOD_PROCESS_PKG.Application_id
352 AND g.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
353 AND g.adjustment_period_flag = 'N'
354 AND proj.project_id = l_fp_cols_rec.x_project_id
355 AND proj.start_date between g.start_date and g.end_date;
356
357 IF p_pa_debug_mode = 'Y' THEN
358 PA_DEBUG.Reset_Curr_Function;
359 END IF;
360 RETURN l_period_name;
361
362 ELSE
363 IF p_pa_debug_mode = 'Y' THEN
364 PA_DEBUG.Reset_Curr_Function;
365 END IF;
366 RETURN null;
367
368 END IF;
369 IF p_pa_debug_mode = 'Y' THEN
370 PA_DEBUG.Reset_Curr_Function;
371 END IF;
372 EXCEPTION
373 WHEN OTHERS THEN
374 IF p_pa_debug_mode = 'Y' THEN
375 PA_DEBUG.Reset_Curr_Function;
376 END IF;
377 RETURN null;
378 END;
379
380 FUNCTION GET_ACT_TO_PERIOD(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
381 RETURN VARCHAR2 IS
382 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_ACT_TO_PERIOD';
383 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
384 l_return_status VARCHAR2(10);
385 l_msg_count NUMBER;
386 l_msg_data VARCHAR2(2000);
387 l_data VARCHAR2(2000);
388
389 l_act_to_period_date DATE;
390 l_act_to_period_name PA_PERIODS_ALL.PERIOD_NAME%TYPE;
391 l_act_from_period_name varchar2(1000); -- bug 6142328 added for comparing act_from_period with act_to_period
392
393 BEGIN
394 IF p_pa_debug_mode = 'Y' THEN
395 pa_debug.set_curr_function( p_function => 'GET_ACT_TO_PERIOD',
396 p_debug_mode => p_pa_debug_mode);
397 END IF;
398 --Calling the Util API
399 IF p_pa_debug_mode = 'Y' THEN
400 pa_fp_gen_amount_utils.fp_debug
401 (p_msg => 'Before calling
402 pa_fp_gen_amount_utils.get_plan_version_dtls',
403 p_module_name => l_module_name,
404 p_log_level => 5);
405 END IF;
406 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
407 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
408 X_FP_COLS_REC => l_fp_cols_rec,
409 X_RETURN_STATUS => l_RETURN_STATUS,
410 X_MSG_COUNT => l_MSG_COUNT,
411 X_MSG_DATA => l_MSG_DATA);
412 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
413 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
414 END IF;
415
416 IF p_pa_debug_mode = 'Y' THEN
417 pa_fp_gen_amount_utils.fp_debug
418 (p_msg => 'Status after calling
419 pa_fp_gen_amount_utils.get_plan_version_dtls: '
420 ||l_RETURN_STATUS,
421 p_module_name => l_module_name,
422 p_log_level => 5);
423 END IF;
424 l_act_to_period_date := to_date(GET_ACTUALS_THRU_PERIOD_DTLS(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
425 P_CONTEXT => 'END_DATE'),'RRRRMMDD');
426 --bug6120919 retrieving the act_from_period_name by calling GET_ACT_FRM_PERIOD()
427 l_act_from_period_name := GET_ACT_FRM_PERIOD(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID);
428
429 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
430 BEGIN
431 SELECT period_name
432 INTO l_act_to_period_name
433 FROM pa_periods_all
434 WHERE org_id = l_fp_cols_rec.x_org_id
435 AND l_act_to_period_date BETWEEN start_date AND end_date
436 AND start_date >= (SELECT start_date -- bug6142328 added one more select query for comparing the start date of act_to_period with act_from_period
437 FROM pa_periods_all
438 WHERE period_name = l_act_from_period_name
439 AND org_id = l_fp_cols_rec.x_org_id);
440 EXCEPTION
441 WHEN NO_DATA_FOUND THEN
442 l_act_to_period_name := l_act_from_period_name;
443 END;
444 IF p_pa_debug_mode = 'Y' THEN
445 PA_DEBUG.Reset_Curr_Function;
446 END IF;
447 RETURN l_act_to_period_name;
448 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
449 BEGIN
450 SELECT period_name
451 INTO l_act_to_period_name
452 FROM gl_period_statuses
453 WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
454 AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
455 AND adjustment_period_flag = 'N'
456 AND l_act_to_period_date BETWEEN start_date AND end_date
457 AND start_date >= (SELECT start_date FROM gl_period_statuses -- bug6142328 added one more select query for comparing the start date of act_to_period with act_from_period
458 WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
459 AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
460 AND period_name = l_act_from_period_name);
461
462 EXCEPTION
463 WHEN NO_DATA_FOUND THEN
464 l_act_to_period_name := l_act_from_period_name;
465 END;
466
467 IF p_pa_debug_mode = 'Y' THEN
468 PA_DEBUG.Reset_Curr_Function;
469 END IF;
470 -- bug 6142328; adding the below if condition for validating the l_act_to_period
471 --bug6142328 l_act_from_period_name := GET_ACT_FRM_PERIOD(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID);
472
473 /* IF(l_act_from_period_name > l_act_to_period_name)
474 THEN
475 l_act_to_period_name := l_act_from_period_name;
476 END IF; */
477 RETURN l_act_to_period_name;
478
479 ELSE
480 IF p_pa_debug_mode = 'Y' THEN
481 PA_DEBUG.Reset_Curr_Function;
482 END IF;
483 RETURN null;
484
485 END IF;
486 IF p_pa_debug_mode = 'Y' THEN
487 PA_DEBUG.Reset_Curr_Function;
488 END IF;
489 EXCEPTION
490 WHEN OTHERS THEN
491 IF p_pa_debug_mode = 'Y' THEN
492 PA_DEBUG.Reset_Curr_Function;
493 END IF;
494 RETURN null;
495 END;
496
497 FUNCTION GET_ETC_FRM_PERIOD(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
498 RETURN VARCHAR2 IS
499 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_ETC_FRM_PERIOD';
500 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
501 l_return_status VARCHAR2(10);
502 l_msg_count NUMBER;
503 l_msg_data VARCHAR2(2000);
504 l_data VARCHAR2(2000);
505 l_msg_index_out NUMBER:=0;
506
507 l_etc_from_period_date DATE;
508 l_etc_from_period_name PA_PERIODS_ALL.PERIOD_NAME%TYPE;
509
510 BEGIN
511 IF p_pa_debug_mode = 'Y' THEN
512 pa_debug.set_curr_function( p_function => 'GET_ETC_FRM_PERIOD',
513 p_debug_mode => p_pa_debug_mode);
514 END IF;
515 --Calling the Util API
516 IF p_pa_debug_mode = 'Y' THEN
517 pa_fp_gen_amount_utils.fp_debug
518 (p_msg => 'Before calling
519 pa_fp_gen_amount_utils.get_plan_version_dtls',
520 p_module_name => l_module_name,
521 p_log_level => 5);
522 END IF;
523 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
524 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
525 X_FP_COLS_REC => l_fp_cols_rec,
526 X_RETURN_STATUS => l_RETURN_STATUS,
527 X_MSG_COUNT => l_MSG_COUNT,
528 X_MSG_DATA => l_MSG_DATA);
529 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
530 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
531 END IF;
532
533 IF p_pa_debug_mode = 'Y' THEN
534 pa_fp_gen_amount_utils.fp_debug
535 (p_msg => 'Status after calling
536 pa_fp_gen_amount_utils.get_plan_version_dtls: '
537 ||l_RETURN_STATUS,
538 p_module_name => l_module_name,
539 p_log_level => 5);
540 END IF;
541
542 l_etc_from_period_date := to_date(GET_ACTUALS_THRU_PERIOD_DTLS(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
543 P_CONTEXT => 'END_DATE'),'RRRRMMDD')+1;
544
545 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
546
547 SELECT period_name
548 INTO l_etc_from_period_name
549 FROM pa_periods_all
550 WHERE org_id = l_fp_cols_rec.x_org_id
551 AND l_etc_from_period_date between start_date and end_date;
552
553 IF p_pa_debug_mode = 'Y' THEN
554 PA_DEBUG.Reset_Curr_Function;
555 END IF;
556 RETURN l_etc_from_period_name;
557 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
558
559 SELECT period_name
560 INTO l_etc_from_period_name
561 FROM gl_period_statuses
562 WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
563 AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
564 AND adjustment_period_flag = 'N'
565 AND l_etc_from_period_date between start_date and end_date;
566
567 IF p_pa_debug_mode = 'Y' THEN
568 PA_DEBUG.Reset_Curr_Function;
569 END IF;
570 RETURN l_etc_from_period_name;
571
572 ELSE
573 IF p_pa_debug_mode = 'Y' THEN
574 PA_DEBUG.Reset_Curr_Function;
575 END IF;
576 RETURN null;
577
578 END IF;
579 IF p_pa_debug_mode = 'Y' THEN
580 PA_DEBUG.Reset_Curr_Function;
581 END IF;
582 EXCEPTION
583 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
584 -- Bug Fix: 4569365. Removed MRC code.
585 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
586 l_msg_count := FND_MSG_PUB.count_msg;
587 IF l_msg_count = 1 THEN
588 PA_INTERFACE_UTILS_PUB.get_messages
589 (p_encoded => FND_API.G_TRUE
590 ,p_msg_index => 1
591 ,p_msg_count => l_msg_count
592 ,p_msg_data => l_msg_data
593 ,p_data => l_data
594 ,p_msg_index_out => l_msg_index_out);
595 --x_msg_data := l_data;
596 l_msg_data := l_data;
597 --x_msg_count := l_msg_count;
598 /*ELSE
599 x_msg_count := l_msg_count; */
600 END IF;
601 ROLLBACK;
602
603 l_return_status := FND_API.G_RET_STS_ERROR;
604
605 IF p_pa_debug_mode = 'Y' THEN
606 PA_DEBUG.Reset_Curr_Function;
607 END IF;
608
609 WHEN OTHERS THEN
610 IF p_pa_debug_mode = 'Y' THEN
611 PA_DEBUG.Reset_Curr_Function;
612 END IF;
613 RETURN null;
614 END;
615
616 FUNCTION GET_ETC_TO_PERIOD(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
617 RETURN VARCHAR2 IS
618 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_ETC_TO_PERIOD';
619
620 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
621 l_return_status VARCHAR2(10);
622 l_msg_count NUMBER;
623 l_msg_data VARCHAR2(2000);
624 l_data VARCHAR2(2000);
625 l_proj_comp_date DATE;
626 l_etc_to_period PA_PERIODS_ALL.PERIOD_NAME%TYPE;
627 l_actual_thru_date DATE;
628
629 BEGIN
630 IF p_pa_debug_mode = 'Y' THEN
631 pa_debug.set_curr_function( p_function => 'GET_ETC_TO_PERIOD',
632 p_debug_mode => p_pa_debug_mode);
633 END IF;
634 --Calling the Util API
635 IF p_pa_debug_mode = 'Y' THEN
636 pa_fp_gen_amount_utils.fp_debug
637 (p_msg => 'Before calling
638 pa_fp_gen_amount_utils.get_plan_version_dtls',
639 p_module_name => l_module_name,
640 p_log_level => 5);
641 END IF;
642 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
643 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
644 X_FP_COLS_REC => l_fp_cols_rec,
645 X_RETURN_STATUS => l_RETURN_STATUS,
646 X_MSG_COUNT => l_MSG_COUNT,
647 X_MSG_DATA => l_MSG_DATA);
648 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
649 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
650 END IF;
651 IF p_pa_debug_mode = 'Y' THEN
652 pa_fp_gen_amount_utils.fp_debug
653 (p_msg => 'Status after calling
654 pa_fp_gen_amount_utils.get_plan_version_dtls: '
655 ||l_RETURN_STATUS,
656 p_module_name => l_module_name,
657 p_log_level => 5);
658 END IF;
659
660 l_actual_thru_date := to_date(GET_ACTUALS_THRU_PERIOD_DTLS(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
661 P_CONTEXT => 'END_DATE'),'RRRRMMDD');
662
663 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
664
665 SELECT p.period_name, NVL(proj.completion_date, trunc(SYSDATE))
666 INTO l_etc_to_period, l_proj_comp_date
667 FROM pa_periods_all p, pa_projects_all proj
668 WHERE NVL(proj.completion_date, trunc(SYSDATE)) between p.start_date and p.end_date
669 AND p.org_id = l_fp_cols_rec.x_org_id
670 AND proj.project_id = l_fp_cols_rec.x_project_id;
671
672 IF l_actual_thru_date+1 >= l_proj_comp_date THEN
673 l_etc_to_period := GET_ETC_FRM_PERIOD(P_BUDGET_VERSION_ID);
674 END IF;
675
676 IF p_pa_debug_mode = 'Y' THEN
677 PA_DEBUG.Reset_Curr_Function;
678 END IF;
679 RETURN l_etc_to_period;
680
681 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
682
683 SELECT g.period_name, NVL(proj.completion_date, trunc(SYSDATE))
684 INTO l_etc_to_period, l_proj_comp_date
685 FROM gl_period_statuses g, pa_projects_all proj
686 WHERE g.application_id = PA_PERIOD_PROCESS_PKG.Application_id
687 AND g.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
688 AND g.adjustment_period_flag = 'N'
689 AND proj.project_id = l_fp_cols_rec.x_project_id
690 AND NVL(proj.completion_date, trunc(SYSDATE)) between g.start_date and g.end_date;
691
692 IF l_actual_thru_date+1 >= l_proj_comp_date THEN
693 l_etc_to_period := GET_ETC_FRM_PERIOD(P_BUDGET_VERSION_ID);
694 END IF;
695
696 IF p_pa_debug_mode = 'Y' THEN
697 PA_DEBUG.Reset_Curr_Function;
698 END IF;
699 RETURN l_etc_to_period;
700
701 ELSE
702 IF p_pa_debug_mode = 'Y' THEN
703 PA_DEBUG.Reset_Curr_Function;
704 END IF;
705 RETURN null;
706
707 END IF;
708 IF p_pa_debug_mode = 'Y' THEN
709 PA_DEBUG.Reset_Curr_Function;
710 END IF;
711 EXCEPTION
712 WHEN OTHERS THEN
713 IF p_pa_debug_mode = 'Y' THEN
714 PA_DEBUG.Reset_Curr_Function;
715 END IF;
716 RETURN null;
717 END;
718
719
720
721 FUNCTION GET_UNSPENT_AMT_PERIOD(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
722 RETURN VARCHAR2 IS
723 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.GET_UNSPENT_AMT_PERIOD';
724 x_period_name PA_PERIODS_ALL.PERIOD_NAME%TYPE;
725 BEGIN
726 x_period_name := GET_ETC_FRM_PERIOD(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID);
727 RETURN x_period_name;
728 END;
729
730
731 PROCEDURE GET_VERSION_DETAILS
732 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
733 X_VERSION_TYPE OUT NOCOPY PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
734 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
735 X_MSG_COUNT OUT NOCOPY NUMBER,
736 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
737
738 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.get_version_details';
739 l_msg_count NUMBER;
740 l_msg_data VARCHAR2(2000);
741 l_data VARCHAR2(2000);
742 l_msg_index_out NUMBER:=0;
743
744 BEGIN
745 --Setting initial values
746 X_MSG_COUNT := 0;
747 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
748
749 IF p_pa_debug_mode = 'Y' THEN
750 pa_debug.set_curr_function( p_function => 'GET_VERSION_DETAILS'
751 ,p_debug_mode => p_pa_debug_mode);
752 END IF;
753
754 SELECT VERSION_TYPE
755 INTO X_VERSION_TYPE
756 FROM PA_BUDGET_VERSIONS
757 WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
758 --dbms_output.put_line('Version type from get_version_dtls api:'||X_VERSION_TYPE);
759
760 IF P_PA_DEBUG_MODE = 'Y' THEN
761 PA_DEBUG.Reset_Curr_Function;
762
763 END IF;
764
765 EXCEPTION
766 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
767 l_msg_count := FND_MSG_PUB.count_msg;
768 IF l_msg_count = 1 THEN
769 PA_INTERFACE_UTILS_PUB.get_messages
770 (p_encoded => FND_API.G_TRUE
771 ,p_msg_index => 1
772 ,p_msg_count => l_msg_count
773 ,p_msg_data => l_msg_data
774 ,p_data => l_data
775 ,p_msg_index_out => l_msg_index_out);
776 x_msg_data := l_data;
777 x_msg_count := l_msg_count;
778 ELSE
779 x_msg_count := l_msg_count;
780 END IF;
781 ROLLBACK;
782
783 x_return_status := FND_API.G_RET_STS_ERROR;
784 IF P_PA_DEBUG_MODE = 'Y' THEN
785 pa_fp_gen_amount_utils.fp_debug
786 (p_msg => 'Invalid Arguments Passed',
787 p_module_name => l_module_name,
788 p_log_level => 5);
789 PA_DEBUG.Reset_Curr_Function;
790 END IF;
791 RAISE;
792
793 WHEN OTHERS THEN
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 x_msg_data := SUBSTR(SQLERRM,1,240);
796 FND_MSG_PUB.add_exc_msg
797 ( p_pkg_name => 'PA_FP_GEN_FCST_PG_PKG'
798 ,p_procedure_name => 'GET_VERSION_DETAILS');
799 IF P_PA_DEBUG_MODE = 'Y' THEN
800 pa_fp_gen_amount_utils.fp_debug
801 (p_msg => 'Unexpected Error'||SUBSTR(SQLERRM,1,240),
802 p_module_name => l_module_name,
803 p_log_level => 5);
804 PA_DEBUG.Reset_Curr_Function;
805 END IF;
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807
808 END GET_VERSION_DETAILS;
809
810 -- gboomina added for AAI requirement 8318932 - start
811 /**
812 * This method is used to validate Time Phase and ETC Source of
813 * source plan
814 */
815 PROCEDURE VALIDATION_FOR_COPY_ETC_FLAG
816 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
817 P_WP_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
818 P_ETC_PLAN_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
819 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
820 X_MSG_COUNT OUT NOCOPY NUMBER,
821 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
822
823 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.validation_for_copy_etc_flag';
824 l_plan_version_id PA_BUDGET_VERSIONS.VERSION_NAME%TYPE;
825 l_msg_count NUMBER;
826 l_msg_data VARCHAR2(2000);
827 l_data VARCHAR2(2000);
828 l_msg_index_out NUMBER:=0;
829 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
830
831 CURSOR get_etc_source_code_csr
832 IS
833 SELECT GEN_ETC_SOURCE_CODE,TASK_NAME
834 FROM PA_TASKS T
835 WHERE PROJECT_ID = P_PROJECT_ID;
836
837 l_gen_etc_source_code get_etc_source_code_csr%ROWTYPE;
838
839 BEGIN
840 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
841
842 IF p_pa_debug_mode = 'Y' THEN
843 pa_debug.set_curr_function( p_function => 'VALIDATE_PLAN_TYPE_OR_VERSION'
844 ,p_debug_mode => p_pa_debug_mode);
845 END IF;
846
847 -- Get the work plan details by calling the following method
848 -- with work plan version id
849 IF P_WP_VERSION_ID IS NOT NULL THEN
850 IF p_pa_debug_mode = 'Y' THEN
851 pa_fp_gen_amount_utils.fp_debug
852 (p_msg => 'Before calling
853 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id)',
854 p_module_name => l_module_name,
855 p_log_level => 5);
856 END IF;
857 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
858 (P_BUDGET_VERSION_ID => P_WP_VERSION_ID,
859 X_FP_COLS_REC => l_fp_cols_rec,
860 X_RETURN_STATUS => X_RETURN_STATUS,
861 X_MSG_COUNT => X_MSG_COUNT,
862 X_MSG_DATA => X_MSG_DATA);
863 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
864 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
865 END IF;
866 IF p_pa_debug_mode = 'Y' THEN
867 pa_fp_gen_amount_utils.fp_debug
868 (p_msg => 'Status after calling
869 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id): '
870 ||x_return_status,
871 p_module_name => l_module_name,
872 p_log_level => 5);
873 END IF;
874
875 -- Check whether work plan is non time phased. if so, throw an error
876 -- Only time phased plan is supported for Copy ETC From plan AAI requirement
877 IF l_fp_cols_rec.x_time_phased_code = 'N' THEN
878 x_return_status := FND_API.G_RET_STS_ERROR;
879 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
880 p_msg_name => 'PA_FP_NON_TIME_PHASE_NOT_SUPP',
881 p_token1 => 'PLAN_TYPE',
882 p_value1 => 'Work Plan');
883
884 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
885 END IF;
886 END IF;
887
888 -- Get the financial plan details by calling the following
889 -- method with financial paln id
890 IF P_ETC_PLAN_VERSION_ID IS NOT NULL THEN
891 IF p_pa_debug_mode = 'Y' THEN
892 pa_fp_gen_amount_utils.fp_debug
893 (p_msg => 'Before calling
894 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id)',
895 p_module_name => l_module_name,
896 p_log_level => 5);
897 END IF;
898 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
899 (P_BUDGET_VERSION_ID => P_ETC_PLAN_VERSION_ID,
900 X_FP_COLS_REC => l_fp_cols_rec,
901 X_RETURN_STATUS => X_RETURN_STATUS,
902 X_MSG_COUNT => X_MSG_COUNT,
903 X_MSG_DATA => X_MSG_DATA);
904 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
905 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
906 END IF;
907 IF p_pa_debug_mode = 'Y' THEN
908 pa_fp_gen_amount_utils.fp_debug
909 (p_msg => 'Status after calling
910 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id): '
911 ||x_return_status,
912 p_module_name => l_module_name,
913 p_log_level => 5);
914 END IF;
915
916 -- Check whether work plan is non time phased. if so, throw an error.
917 -- Only time phased plan is supported for Copy ETC From plan AAI requirement
918 IF l_fp_cols_rec.x_time_phased_code = 'N' THEN
919 x_return_status := FND_API.G_RET_STS_ERROR;
920 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
921 p_msg_name => 'PA_FP_NON_TIME_PHASE_NOT_SUPP',
922 p_token1 => 'PLAN_TYPE',
923 p_value1 => 'Source Financial Plan');
924
925 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
926 END IF;
927
928 END IF;
929
930 -- Validation for ETC source
931 -- Either Financial plan or Work plan are only supported for
932 -- Copy ETC From plan AAI requirement
933 FOR l_gen_etc_source_code IN get_etc_source_code_csr
934 LOOP
935 IF l_gen_etc_source_code.gen_etc_source_code NOT IN ('FINANCIAL_PLAN', 'WORKPLAN_RESOURCES') THEN
936 PA_UTILS.ADD_MESSAGE
937 ( p_app_short_name => 'PA',
938 p_msg_name => 'PA_FP_ETC_SRC_NOT_SUPPORTED',
939 p_token1 => 'TASK_NAME',
940 p_value1 => l_gen_etc_source_code.task_name);
941 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
942 END IF;
943 END LOOP;
944
945 IF P_PA_DEBUG_MODE = 'Y' THEN
946 PA_DEBUG.Reset_Curr_Function;
947 END IF;
948
949 EXCEPTION
950 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
951 l_msg_count := FND_MSG_PUB.count_msg;
952 IF l_msg_count = 1 THEN
953 PA_INTERFACE_UTILS_PUB.get_messages
954 (p_encoded => FND_API.G_TRUE
955 ,p_msg_index => 1
956 ,p_msg_count => l_msg_count
957 ,p_msg_data => l_msg_data
958 ,p_data => l_data
959 ,p_msg_index_out => l_msg_index_out);
960 x_msg_data := l_data;
961 x_msg_count := l_msg_count;
962 ELSE
963 x_msg_count := l_msg_count;
964 END IF;
965 ROLLBACK;
966
967 x_return_status := FND_API.G_RET_STS_ERROR;
968 IF P_PA_DEBUG_MODE = 'Y' THEN
969 pa_fp_gen_amount_utils.fp_debug
970 (p_msg => 'Validation failed',
971 p_module_name => l_module_name,
972 p_log_level => 5);
973 PA_DEBUG.Reset_Curr_Function;
974 END IF;
975 RAISE;
976 WHEN OTHERS THEN
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 x_msg_data := SUBSTR(SQLERRM,1,240);
979 FND_MSG_PUB.add_exc_msg
980 ( p_pkg_name => 'PA_FP_GEN_FCST_PG_PKG'
981 ,p_procedure_name => 'VALIDATE_PLAN_TYPE_OR_VERSION');
982 IF P_PA_DEBUG_MODE = 'Y' THEN
983 pa_fp_gen_amount_utils.fp_debug
984 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
985 p_module_name => l_module_name,
986 p_log_level => 5);
987 PA_DEBUG.Reset_Curr_Function;
988 END IF;
989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
990
991 END VALIDATION_FOR_COPY_ETC_FLAG;
992 -- gboomina added for AAI requirement 8318932 - end
993
994 /**
995 * 23-MAY-05 dkuo Added parameters P_CHECK_SRC_ERRORS, X_WARNING_MESSAGE.
996 * Please check body of VALIDATE_SUPPORT_CASES in PAFPGAUB.pls
997 * for list of valid parameter values.
998 **/
999 PROCEDURE UPD_VER_DTLS_AND_GEN_AMT
1000 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1001 P_VERSION_TYPE IN PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
1002 P_UNSPENT_AMT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_UNSPENT_AMT_FLAG%TYPE,
1003 P_UNSPENT_AMT_PERIOD IN VARCHAR2,
1004 P_INCL_CHG_DOC_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_CHANGE_DOC_FLAG%TYPE,
1005 P_INCL_OPEN_CMT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_OPEN_COMM_FLAG%TYPE,
1006 P_INCL_BILL_EVT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_REV_INCL_BILL_EVENT_FLAG%TYPE,
1007 P_RET_MANUAL_LNS_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_RET_MANUAL_LINE_FLAG%TYPE,
1008 P_PLAN_TYPE_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
1009 P_PLAN_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
1010 P_PLAN_VERSION_NAME IN PA_BUDGET_VERSIONS.VERSION_NAME%TYPE,
1011 P_ETC_PLAN_TYPE_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
1012 P_ETC_PLAN_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
1013 P_ETC_PLAN_VERSION_NAME IN PA_BUDGET_VERSIONS.VERSION_NAME%TYPE,
1014 P_ACTUALS_FROM_PERIOD IN VARCHAR2,
1015 P_ACTUALS_TO_PERIOD IN VARCHAR2,
1016 P_ETC_FROM_PERIOD IN VARCHAR2,
1017 P_ETC_TO_PERIOD IN VARCHAR2,
1018 P_ACTUALS_THRU_PERIOD IN PA_BUDGET_VERSIONS.ACTUAL_AMTS_THRU_PERIOD%TYPE,
1019 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
1020 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
1021 P_CHECK_SRC_ERRORS_FLAG IN VARCHAR2,
1022 X_WARNING_MESSAGE OUT NOCOPY VARCHAR2,
1023 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1024 X_MSG_COUNT OUT NOCOPY NUMBER,
1025 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1026
1027 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.upd_ver_dtls_and_gen_amt';
1028 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1029 l_PLAN_VERSION_ID PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE;
1030 l_ETC_PLAN_VERSION_ID PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE;
1031 l_return_status VARCHAR2(10);
1032 l_msg_count NUMBER;
1033 l_msg_data VARCHAR2(2000);
1034 l_data VARCHAR2(2000);
1035 l_msg_index_out NUMBER:=0;
1036
1037 l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE;
1038 l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE;
1039 l_sysdate DATE;
1040
1041 l_record_version_number PA_BUDGET_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
1042 l_wp_version_id number;
1043 l_res_asg_id_del_tab PA_PLSQL_DATATYPES.IdTabTyp;
1044
1045 -- gboomina added for AAI requirement 8318932 - start
1046 -- Cursor to get 'Copy ETC from Plan' flag
1047 CURSOR get_copy_etc_from_plan_csr
1048 IS
1049 SELECT COPY_ETC_FROM_PLAN_FLAG
1050 FROM PA_PROJ_FP_OPTIONS
1051 WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
1052
1053 l_copy_etc_from_plan_flag PA_PROJ_FP_OPTIONS.COPY_ETC_FROM_PLAN_FLAG%TYPE;
1054 -- gboomina added for AAI requirement 8318932 - end
1055
1056 BEGIN
1057 --Setting initial values
1058 --hr_utility.trace_on(null,'mftest');
1059 --hr_utility.trace('bv id :'|| P_BUDGET_VERSION_ID);
1060 --hr_utility.trace('ver type: '|| P_VERSION_TYPE);
1061 --hr_utility.trace('unspent : '|| P_UNSPENT_AMT_FLAG);
1062 --hr_utility.trace('unspent pd : '|| P_UNSPENT_AMT_PERIOD);
1063 --hr_utility.trace('inc chg doc : '|| P_INCL_CHG_DOC_FLAG);
1064 --hr_utility.trace('inc cmt flag: '|| P_INCL_OPEN_CMT_FLAG);
1065 --hr_utility.trace('inc bil flag: '|| P_INCL_BILL_EVT_FLAG);
1066 --hr_utility.trace('ret man flag: '|| P_RET_MANUAL_LNS_FLAG);
1067 --hr_utility.trace('plan type id: '|| P_PLAN_TYPE_ID);
1068 --hr_utility.trace('plan vers id: '|| P_PLAN_VERSION_ID);
1069 --hr_utility.trace('plan vers name: '|| P_PLAN_VERSION_NAME);
1070 --hr_utility.trace('etc plan type : '|| P_ETC_PLAN_TYPE_ID);
1071 --hr_utility.trace('etc plan ver : '|| P_ETC_PLAN_VERSION_ID);
1072 --hr_utility.trace('etc plan ver name : '|| P_ETC_PLAN_VERSION_NAME);
1073 --hr_utility.trace('actu from pd name : '|| P_ACTUALS_FROM_PERIOD);
1074 --hr_utility.trace('actu to pd name : '|| P_ACTUALS_TO_PERIOD);
1075 --hr_utility.trace('etc from pd name : '|| P_ETC_FROM_PERIOD);
1076 --hr_utility.trace('etc to pd name : '|| P_ETC_TO_PERIOD);
1077 --hr_utility.trace('act thru pd name : '|| P_ACTUALS_THRU_PERIOD);
1078 --hr_utility.trace('act thru date : '|| to_char(P_ACTUALS_THRU_DATE));
1079 --hr_utility.trace('wp str ver id : '|| P_WP_STRUCTURE_VERSION_ID);
1080
1081 FND_MSG_PUB.initialize;
1082 X_MSG_COUNT := 0;
1083 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1084 l_return_status := FND_API.G_RET_STS_SUCCESS;
1085
1086 IF p_pa_debug_mode = 'Y' THEN
1087 pa_debug.set_curr_function( p_function => 'UPD_VER_DTLS_AND_GEN_AMT'
1088 ,p_debug_mode => p_pa_debug_mode);
1089 END IF;
1090
1091 /*CBS enhancement to validate Planning Options of the budget bug#16604989 */
1092 PA_ALTERNATE_TASK_PVT.Is_Planning_Options_Valid(
1093 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1094 X_RETURN_STATUS => X_RETURN_STATUS,
1095 X_MSG_COUNT => X_MSG_COUNT,
1096 X_MSG_DATA => X_MSG_DATA);
1097 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1098 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1099 p_msg_name => x_msg_data);
1100 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1101 END IF;
1102
1103 /*Bug fix:3818180 for locking*/
1104 --acquire version lock
1105
1106 SELECT record_version_number
1107 INTO l_record_version_number
1108 FROM pa_budget_versions
1109 WHERE budget_version_id = p_budget_version_id;
1110 IF p_pa_debug_mode = 'Y' THEN
1111 pa_fp_gen_amount_utils.fp_debug
1112 (p_msg => 'Before calling pa_fin_plan_pvt.lock_unlock_version',
1113 p_module_name => l_module_name);
1114 END IF;
1115 pa_fin_plan_pvt.lock_unlock_version
1116 (p_budget_version_id => P_BUDGET_VERSION_ID,
1117 p_record_version_number => l_record_version_number,
1118 p_action => 'L',
1119 p_user_id => FND_GLOBAL.USER_ID,
1120 p_person_id => NULL,
1121 x_return_status => x_return_status,
1122 x_msg_count => x_msg_count,
1123 x_msg_data => x_msg_data);
1124 IF p_pa_debug_mode = 'Y' THEN
1125 pa_fp_gen_amount_utils.fp_debug
1126 (p_msg => 'Status after calling pa_fin_plan_pvt.lock_unlock_version:'
1127 ||x_return_status,
1128 p_module_name => l_module_name);
1129 END IF;
1130
1131 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1132 IF p_pa_debug_mode = 'Y' THEN
1133 PA_DEBUG.Reset_Curr_Function;
1134 END IF;
1135 RETURN;
1136 END IF;
1137
1138 COMMIT;
1139
1140 /* we need to commit the changes so that the locked by person info
1141 will be available for other sessions. */
1142
1143 --acquire lock for copy_actual
1144 IF p_pa_debug_mode = 'Y' THEN
1145 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1146 P_MSG => 'Before calling PA_FP_COPY_FROM_PKG.'
1147 ||'ACQUIRE_LOCKS_FOR_COPY_ACTUAL',
1148 P_MODULE_NAME => l_module_name);
1149 END IF;
1150
1151 PA_FP_COPY_FROM_PKG.ACQUIRE_LOCKS_FOR_COPY_ACTUAL
1152 (P_PLAN_VERSION_ID => P_BUDGET_VERSION_ID,
1153 X_RETURN_STATUS => X_RETURN_STATUS,
1154 X_MSG_COUNT => X_MSG_COUNT,
1155 X_MSG_DATA => X_MSG_DATA);
1156 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1157 --If can't acquire lock, customized message is thrown from within
1158 -- the API, so we should suppress exception error
1159 IF p_pa_debug_mode = 'Y' THEN
1160 PA_DEBUG.Reset_Curr_Function;
1161 END IF;
1162 RETURN;
1163 END IF;
1164
1165 IF p_pa_debug_mode = 'Y' THEN
1166 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1167 P_MSG => 'After calling PA_FP_COPY_FROM_PKG.'
1168 ||'ACQUIRE_LOCKS_FOR_COPY_ACTUAL: '
1169 ||x_return_status,
1170 P_MODULE_NAME => l_module_name);
1171 END IF;
1172 /*End Bug fix:3818180 for locking*/
1173
1174
1175 l_PLAN_VERSION_ID := P_PLAN_VERSION_ID;
1176 l_ETC_PLAN_VERSION_ID := P_ETC_PLAN_VERSION_ID;
1177
1178 --Calling the Util API
1179 IF p_pa_debug_mode = 'Y' THEN
1180 pa_fp_gen_amount_utils.fp_debug
1181 (p_msg => 'Before calling
1182 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id)',
1183 p_module_name => l_module_name,
1184 p_log_level => 5);
1185 END IF;
1186 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
1187 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1188 X_FP_COLS_REC => l_fp_cols_rec,
1189 X_RETURN_STATUS => X_RETURN_STATUS,
1190 X_MSG_COUNT => X_MSG_COUNT,
1191 X_MSG_DATA => X_MSG_DATA);
1192 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1193 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1194 END IF;
1195 IF p_pa_debug_mode = 'Y' THEN
1196 pa_fp_gen_amount_utils.fp_debug
1197 (p_msg => 'Status after calling
1198 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id): '
1199 ||x_return_status,
1200 p_module_name => l_module_name,
1201 p_log_level => 5);
1202 END IF;
1203 --dbms_output.put_line('Status of get plan version dtls api: '||X_RETURN_STATUS);
1204
1205 IF P_WP_STRUCTURE_VERSION_ID IS NOT NULL THEN
1206 l_wp_version_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id
1207 ( p_project_id => l_fp_cols_rec.x_project_id,
1208 p_plan_type_id => -1,
1209 p_proj_str_ver_id => P_WP_STRUCTURE_VERSION_ID );
1210 END IF;
1211
1212 -- gboomina added for AAI requirement 8318932 - start
1213 OPEN get_copy_etc_from_plan_csr;
1214 FETCH get_copy_etc_from_plan_csr INTO l_copy_etc_from_plan_flag;
1215 CLOSE get_copy_etc_from_plan_csr;
1216
1217 IF l_copy_etc_from_plan_flag = 'Y' THEN
1218 -- Check whether the target version type is 'COST' and
1219 -- ETC Source is 'Task Level Selection'. Only this combo is supported for
1220 -- Copy ETC from plan flow.
1221 IF ( P_VERSION_TYPE <> 'COST' ) THEN
1222 x_return_status := FND_API.G_RET_STS_ERROR;
1223 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1224 p_msg_name => 'PA_FP_COST_PLAN_TYPE_ONLY_SUPP');
1225 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1226 END IF;
1227 IF ( L_FP_COLS_REC.X_GEN_ETC_SRC_CODE <> 'TASK_LEVEL_SEL' ) THEN
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1230 p_msg_name => 'PA_FP_TASK_LEVEL_SEL_ONLY');
1231 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1232 END IF;
1233 -- Check whether destination financial plan is non time phased.
1234 -- if so, throw an error.
1235 -- Only time phased plan is supported for Copy ETC From plan AAI requirement
1236 IF L_FP_COLS_REC.X_TIME_PHASED_CODE = 'N' THEN
1237 x_return_status := FND_API.G_RET_STS_ERROR;
1238 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1239 p_msg_name => 'PA_FP_NON_TIME_PHASE_NOT_SUPP',
1240 p_token1 => 'PLAN_TYPE',
1241 p_value1 => 'Financial Plan');
1242
1243 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1244 END IF;
1245
1246 IF p_pa_debug_mode = 'Y' THEN
1247 pa_fp_gen_amount_utils.fp_debug
1248 (p_msg => 'Before calling
1249 pa_fp_gen_fcst_pg_pkg.validation_for_copy_etc_flag',
1250 p_module_name => l_module_name,
1251 p_log_level => 5);
1252 END IF;
1253 -- Calling the following method to validate time phase and ETC source of
1254 -- source plan
1255 VALIDATION_FOR_COPY_ETC_FLAG
1256 (P_PROJECT_ID => L_FP_COLS_REC.X_PROJECT_ID,
1257 P_WP_VERSION_ID => L_WP_VERSION_ID,
1258 P_ETC_PLAN_VERSION_ID => L_ETC_PLAN_VERSION_ID,
1259 X_RETURN_STATUS => X_RETURN_STATUS,
1260 X_MSG_COUNT => X_MSG_COUNT,
1261 X_MSG_DATA => X_MSG_DATA);
1262 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1263 l_return_status := X_RETURN_STATUS;
1264 END IF;
1265 IF p_pa_debug_mode = 'Y' THEN
1266 pa_fp_gen_amount_utils.fp_debug
1267 (p_msg => 'Status after calling
1268 pa_fp_gen_fcst_pg_pkg.validation_for_copy_etc_flag: '
1269 ||x_return_status,
1270 p_module_name => l_module_name,
1271 p_log_level => 5);
1272 END IF;
1273 END IF;
1274 -- gboomina added for AAI requirement 8318932 - end
1275
1276 --Calling the validation for the periods
1277 --dbms_output.put_line('before validate_periods');
1278 /* the validation should not happen when the forecast gen source is
1279 RESOURCE SCHEDULE. */
1280
1281 IF L_FP_COLS_REC.X_GEN_ETC_SRC_CODE <> 'RESOURCE_SCHEDULE' THEN
1282 IF p_pa_debug_mode = 'Y' THEN
1283 pa_fp_gen_amount_utils.fp_debug
1284 (p_msg => 'Before calling
1285 pa_fp_gen_fcst_pg_pkg.validate_periods',
1286 p_module_name => l_module_name,
1287 p_log_level => 5);
1288 END IF;
1289 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PERIODS
1290 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1291 P_FP_COLS_REC => l_fp_cols_rec,
1292 P_UNSPENT_AMT_FLAG => P_UNSPENT_AMT_FLAG,
1293 P_UNSPENT_AMT_PERIOD => P_UNSPENT_AMT_PERIOD,
1294 P_ACTUALS_FROM_PERIOD => P_ACTUALS_FROM_PERIOD,
1295 P_ACTUALS_TO_PERIOD => P_ACTUALS_TO_PERIOD,
1296 P_ETC_FROM_PERIOD => P_ETC_FROM_PERIOD,
1297 P_ETC_TO_PERIOD => P_ETC_TO_PERIOD,
1298 X_RETURN_STATUS => X_RETURN_STATUS,
1299 X_MSG_COUNT => X_MSG_COUNT,
1300 X_MSG_DATA => X_MSG_DATA);
1301 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1302 l_return_status := X_RETURN_STATUS;
1303 --RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1304 END IF;
1305 IF p_pa_debug_mode = 'Y' THEN
1306 pa_fp_gen_amount_utils.fp_debug
1307 (p_msg => 'Status after calling
1308 pa_fp_gen_fcst_pg_pkg.validate_periods: '
1309 ||x_return_status,
1310 p_module_name => l_module_name,
1311 p_log_level => 5);
1312 END IF;
1313
1314
1315 /* Calling Validate plan type or version api -> etc_generation_source'*/
1316 IF P_ETC_PLAN_TYPE_ID IS NOT NULL AND
1317 P_ETC_PLAN_VERSION_NAME IS NOT NULL AND
1318 l_ETC_PLAN_VERSION_ID IS NULL THEN
1319 IF p_pa_debug_mode = 'Y' THEN
1320 pa_fp_gen_amount_utils.fp_debug
1321 (p_msg => 'Before calling
1322 pa_fp_gen_fcst_pg_pkg.validate_plan_type_or_version',
1323 p_module_name => l_module_name,
1324 p_log_level => 5);
1325 END IF;
1326 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PLAN_TYPE_OR_VERSION
1327 (P_PROJECT_ID => l_fp_cols_rec.X_PROJECT_ID,
1328 P_PLAN_TYPE_ID => P_ETC_PLAN_TYPE_ID,
1329 PX_PLAN_VERSION_ID => l_ETC_PLAN_VERSION_ID,
1330 P_PLAN_VERSION_NAME => P_ETC_PLAN_VERSION_NAME,
1331 P_CALLING_CONTEXT => 'ETC_GENERATION_SOURCE',
1332 X_RETURN_STATUS => X_RETURN_STATUS,
1333 X_MSG_COUNT => X_MSG_COUNT,
1334 X_MSG_DATA => X_MSG_DATA);
1335 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1336 l_return_status := X_RETURN_STATUS;
1337 --RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1338 END IF;
1339 IF p_pa_debug_mode = 'Y' THEN
1340 pa_fp_gen_amount_utils.fp_debug
1341 ( p_msg => 'Status after calling
1342 pa_fp_gen_fcst_pg_pkg.validate_plan_type_or_version for etc_generation_source: '
1343 ||x_return_status,
1344 p_module_name => l_module_name,
1345 p_log_level => 5);
1346 END IF;
1347 --dbms_output.put_line('Status of validate plan type or version(for etc gen src) api: '||X_RETURN_STATUS);
1348
1349 END IF;
1350
1351 END IF;
1352 /* end if for chking gen src code not equal to resource schedule */
1353
1354 IF p_ret_manual_lns_flag = 'N' THEN
1355 DELETE FROM pa_budget_lines
1356 WHERE budget_version_id = P_BUDGET_VERSION_ID;
1357
1358 DELETE FROM pa_resource_assignments
1359 WHERE budget_version_id = P_BUDGET_VERSION_ID;
1360
1361 -- IPM: New Entity ER ------------------------------------------
1362 -- Call the maintenance api in DELETE mode
1363 IF p_pa_debug_mode = 'Y' THEN
1364 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1365 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
1366 || 'MAINTAIN_DATA',
1367 --P_CALLED_MODE => p_called_mode,
1368 P_MODULE_NAME => l_module_name,
1369 P_LOG_LEVEL => 5 );
1370 END IF;
1371 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
1372 ( P_FP_COLS_REC => l_fp_cols_rec,
1373 P_CALLING_MODULE => 'FORECAST_GENERATION',
1374 P_DELETE_FLAG => 'Y',
1375 P_VERSION_LEVEL_FLAG => 'Y',
1376 --P_CALLED_MODE => p_called_mode,
1377 X_RETURN_STATUS => x_return_status,
1378 X_MSG_COUNT => x_msg_count,
1379 X_MSG_DATA => x_msg_data );
1380 IF p_pa_debug_mode = 'Y' THEN
1381 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1382 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
1383 || 'MAINTAIN_DATA: ' || x_return_status,
1384 --P_CALLED_MODE => p_called_mode,
1385 P_MODULE_NAME => l_module_name,
1386 P_LOG_LEVEL => 5 );
1387 END IF;
1388 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1389 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1390 END IF;
1391 -- END OF IPM: New Entity ER ------------------------------------------
1392
1393 -- Bug 4136545: Delete actuals budget lines when target timephasing is PA
1394 -- or GL. Subtract and NULL out actual amounts when timephasing is None.
1395 ELSIF p_ret_manual_lns_flag = 'Y' THEN
1396
1397 -- 8947560: As per functional requirement, we need to retain the user
1398 -- entered etc values for the generated lines with etc source of None
1399 -- i.e. these lines should be treated similar to manually added lines
1400 UPDATE pa_resource_assignments
1401 SET transaction_source_code = NULL
1402 WHERE budget_version_id = p_budget_version_id AND
1403 transaction_source_code = 'NONE';
1404
1405 -- Bug 4344111: We should delete budget lines for all resources with
1406 -- non-null transaction source code and then null-out the transaction
1407 -- source code for these resources. Moved the logic for Bug 4227963
1408 -- up before the logic for cleaning up actuals budget line data and
1409 -- modified the SELECT statement's WHERE clause to check that the
1410 -- transaction_source_code IS NOT NULL (which includes the previous
1411 -- check that transaction_source_code was either Open Commitments,
1412 -- Billing Events, or Change Documents).
1413
1414 /* Bug 4227963: Clean up additional options' budget line data. */
1415 SELECT resource_assignment_id
1416 BULK COLLECT INTO
1417 l_res_asg_id_del_tab
1418 FROM PA_RESOURCE_ASSIGNMENTS
1419 WHERE budget_version_id = p_budget_version_id AND
1420 transaction_source_code IS NOT NULL;
1421
1422 IF (l_res_asg_id_del_tab.count > 0) THEN
1423 FORALL i IN 1 .. l_res_asg_id_del_tab.count
1424 DELETE FROM PA_BUDGET_LINES
1425 WHERE resource_assignment_id = l_res_asg_id_del_tab(i);
1426
1427 FORALL j IN 1 .. l_res_asg_id_del_tab.count
1428 UPDATE PA_RESOURCE_ASSIGNMENTS
1429 SET transaction_source_code = null
1430 WHERE resource_assignment_id = l_res_asg_id_del_tab(j);
1431
1432 -- IPM: New Entity ER ------------------------------------------
1433 DELETE pa_resource_asgn_curr_tmp;
1434
1435 FORALL k IN 1..l_res_asg_id_del_tab.count
1436 INSERT INTO pa_resource_asgn_curr_tmp (
1437 RESOURCE_ASSIGNMENT_ID,
1438 DELETE_FLAG )
1439 VALUES (
1440 l_res_asg_id_del_tab(k),
1441 'Y' );
1442
1443 -- Call the maintenance api in DELETE mode
1444 IF p_pa_debug_mode = 'Y' THEN
1445 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1446 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
1447 || 'MAINTAIN_DATA',
1448 --P_CALLED_MODE => p_called_mode,
1449 P_MODULE_NAME => l_module_name,
1450 P_LOG_LEVEL => 5 );
1451 END IF;
1452 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
1453 ( P_FP_COLS_REC => l_fp_cols_rec,
1454 P_CALLING_MODULE => 'FORECAST_GENERATION',
1455 P_DELETE_FLAG => 'Y',
1456 P_VERSION_LEVEL_FLAG => 'N',
1457 --P_CALLED_MODE => p_called_mode,
1458 X_RETURN_STATUS => x_return_status,
1459 X_MSG_COUNT => x_msg_count,
1460 X_MSG_DATA => x_msg_data );
1461 IF p_pa_debug_mode = 'Y' THEN
1462 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1463 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
1464 || 'MAINTAIN_DATA: ' || x_return_status,
1465 --P_CALLED_MODE => p_called_mode,
1466 P_MODULE_NAME => l_module_name,
1467 P_LOG_LEVEL => 5 );
1468 END IF;
1469 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1470 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1471 END IF;
1472 -- END OF IPM: New Entity ER ------------------------------------------
1473
1474 END IF; --IF (l_res_asg_id_del_tab.count > 0) THEN
1475
1476 /* Clean up actuals budget line data. */
1477 IF l_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1478 DELETE FROM pa_budget_lines
1479 WHERE budget_version_id = p_budget_version_id
1480 AND start_date <= p_actuals_thru_date;
1481 ELSIF l_fp_cols_rec.x_time_phased_code = 'N' THEN
1482 UPDATE pa_budget_lines
1483 SET quantity = quantity - NVL(init_quantity,0),
1484 raw_cost = raw_cost - NVL(init_raw_cost,0),
1485 burdened_cost = burdened_cost - NVL(init_burdened_cost,0),
1486 revenue = revenue - NVL(init_revenue,0),
1487 project_raw_cost = project_raw_cost - NVL(project_init_raw_cost,0),
1488 project_burdened_cost = project_burdened_cost - NVL(project_init_burdened_cost,0),
1489 project_revenue = project_revenue - NVL(project_init_revenue,0),
1490 txn_raw_cost = txn_raw_cost - NVL(txn_init_raw_cost,0),
1491 txn_burdened_cost = txn_burdened_cost - NVL(txn_init_burdened_cost,0),
1492 txn_revenue = txn_revenue - NVL(txn_init_revenue,0)
1493 WHERE budget_version_id = p_budget_version_id;
1494
1495 l_last_updated_by := FND_GLOBAL.USER_ID;
1496 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1497 l_sysdate := SYSDATE;
1498
1499 UPDATE pa_budget_lines
1500 SET init_quantity = null,
1501 init_raw_cost = null,
1502 init_burdened_cost = null,
1503 init_revenue = null,
1504 project_init_raw_cost = null,
1505 project_init_burdened_cost = null,
1506 project_init_revenue = null,
1507 txn_init_raw_cost = null,
1508 txn_init_burdened_cost = null,
1509 txn_init_revenue = null,
1510 last_update_date = l_sysdate,
1511 last_updated_by = l_last_updated_by,
1512 last_update_login = l_last_update_login
1513 WHERE budget_version_id = p_budget_version_id;
1514 END IF;
1515 END IF;
1516
1517 IF P_VERSION_TYPE = 'COST' THEN
1518 --Updating the pa_proj_fp_options table for cost version type
1519 UPDATE PA_PROJ_FP_OPTIONS
1520 SET GEN_COST_INCL_UNSPENT_AMT_FLAG = P_UNSPENT_AMT_FLAG,
1521 GEN_COST_INCL_CHANGE_DOC_FLAG = P_INCL_CHG_DOC_FLAG,
1522 GEN_COST_INCL_OPEN_COMM_FLAG = P_INCL_OPEN_CMT_FLAG,
1523 GEN_COST_RET_MANUAL_LINE_FLAG = P_RET_MANUAL_LNS_FLAG,
1524 GEN_SRC_COST_PLAN_TYPE_ID = P_ETC_PLAN_TYPE_ID,
1525 GEN_SRC_COST_PLAN_VERSION_ID = l_ETC_PLAN_VERSION_ID,
1526 GEN_SRC_COST_WP_VERSION_ID = l_wp_version_id
1527 WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
1528 --dbms_output.put_line('No. of rows updated in pa_proj_fp_options for cost:'||sql%rowcount);
1529
1530 ELSIF P_VERSION_TYPE = 'REVENUE' THEN
1531 --Updating the pa_proj_fp_options table for revenue version type
1532 UPDATE PA_PROJ_FP_OPTIONS
1533 SET
1534 GEN_REV_INCL_CHANGE_DOC_FLAG = P_INCL_CHG_DOC_FLAG,
1535 GEN_REV_INCL_BILL_EVENT_FLAG = P_INCL_BILL_EVT_FLAG,
1536 GEN_REV_RET_MANUAL_LINE_FLAG = P_RET_MANUAL_LNS_FLAG,
1537 GEN_SRC_REV_PLAN_TYPE_ID = P_ETC_PLAN_TYPE_ID,
1538 GEN_SRC_REV_PLAN_VERSION_ID = l_ETC_PLAN_VERSION_ID,
1539 GEN_SRC_REV_WP_VERSION_ID = l_wp_version_id
1540 WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
1541 --dbms_output.put_line('No. of rows updated in pa_proj_fp_options for revenue:'||sql%rowcount);
1542
1543 ELSIF P_VERSION_TYPE = 'ALL' THEN
1544 --Updating the pa_proj_fp_options table for all version type
1545 UPDATE PA_PROJ_FP_OPTIONS
1546 SET GEN_ALL_INCL_UNSPENT_AMT_FLAG = P_UNSPENT_AMT_FLAG,
1547 GEN_ALL_INCL_CHANGE_DOC_FLAG = P_INCL_CHG_DOC_FLAG,
1548 GEN_ALL_INCL_OPEN_COMM_FLAG = P_INCL_OPEN_CMT_FLAG,
1549 GEN_ALL_INCL_BILL_EVENT_FLAG = P_INCL_BILL_EVT_FLAG,
1550 GEN_ALL_RET_MANUAL_LINE_FLAG = P_RET_MANUAL_LNS_FLAG,
1551 GEN_SRC_ALL_PLAN_TYPE_ID = P_ETC_PLAN_TYPE_ID,
1552 GEN_SRC_ALL_PLAN_VERSION_ID = l_ETC_PLAN_VERSION_ID,
1553 GEN_SRC_ALL_WP_VERSION_ID = l_wp_version_id
1554 WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
1555 --dbms_output.put_line('No. of rows updated in pa_proj_fp_options for all:'||sql%rowcount);
1556 END IF;
1557
1558 UPDATE PA_BUDGET_VERSIONS
1559 SET ACTUAL_AMTS_THRU_PERIOD = P_ACTUALS_THRU_PERIOD
1560 WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
1561
1562 /* We need to get version details again after validation logic and
1563 * updates to the budget version so that we pass the most current
1564 * information to lower level APIs via the l_fp_cols_rec parameter. */
1565 IF p_pa_debug_mode = 'Y' THEN
1566 pa_fp_gen_amount_utils.fp_debug
1567 ( p_msg => 'Before calling
1568 pa_fp_gen_amount_utils.get_plan_version_dtls',
1569 p_module_name => l_module_name,
1570 p_log_level => 5 );
1571 END IF;
1572 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
1573 ( P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1574 X_FP_COLS_REC => l_fp_cols_rec,
1575 X_RETURN_STATUS => X_RETURN_STATUS,
1576 X_MSG_COUNT => X_MSG_COUNT,
1577 X_MSG_DATA => X_MSG_DATA );
1578
1579 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1580 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1581 END IF;
1582 IF p_pa_debug_mode = 'Y' THEN
1583 pa_fp_gen_amount_utils.fp_debug
1584 ( p_msg => 'Status after calling
1585 pa_fp_gen_amount_utils.get_plan_version_dtls: '
1586 ||x_return_status,
1587 p_module_name => l_module_name,
1588 p_log_level => 5 );
1589 END IF;
1590
1591 /* This API validates that the current generation is supported.
1592 * For a list of unsupported cases, please see comments at the
1593 * beginning of the VALIDATE_SUPPORT_CASES API (PAFPGAUB.pls) */
1594
1595 IF p_pa_debug_mode = 'Y' THEN
1596 pa_fp_gen_amount_utils.fp_debug
1597 ( p_msg => 'Before calling
1598 pa_fp_gen_amount_utils.validate_support_cases',
1599 p_module_name => l_module_name,
1600 p_log_level => 5 );
1601 END IF;
1602 PA_FP_GEN_AMOUNT_UTILS.VALIDATE_SUPPORT_CASES (
1603 P_FP_COLS_REC_TGT => l_fp_cols_rec,
1604 P_CHECK_SRC_ERRORS_FLAG => P_CHECK_SRC_ERRORS_FLAG,
1605 X_WARNING_MESSAGE => X_WARNING_MESSAGE,
1606 X_RETURN_STATUS => X_RETURN_STATUS,
1607 X_MSG_COUNT => X_MSG_COUNT,
1608 X_MSG_DATA => X_MSG_DATA );
1609 IF p_pa_debug_mode = 'Y' THEN
1610 pa_fp_gen_amount_utils.fp_debug
1611 ( p_msg => 'Status after calling
1612 pa_fp_gen_amount_utils.validate_support_cases: '
1613 ||x_return_status,
1614 p_module_name => l_module_name,
1615 p_log_level => 5 );
1616 END IF;
1617 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1618 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1619 END IF;
1620
1621 /* When VALIDATE_SUPPORT_CASES returns a non-null warning message,
1622 * we need to Return control to the page/front-end so that a warning
1623 * can be displayed asking the user whether or not to proceed.
1624 */
1625 /* Bug 4901256 : If P_CHECK_SRC_ERRORS_FLAG is passed as 'Y' always
1626 Return irrespective of X_WARNING_MESSAGE. */
1627 IF P_CHECK_SRC_ERRORS_FLAG = 'Y' THEN
1628 -- Added the above IF and Commented below if condition bug 4901256
1629 -- IF X_WARNING_MESSAGE IS NOT NULL THEN
1630 -- Before returning, we always have the following check.
1631 IF p_pa_debug_mode = 'Y' THEN
1632 PA_DEBUG.Reset_Curr_Function;
1633 END IF;
1634
1635 RETURN;
1636 END IF;
1637
1638 --Calling Gen FCST Amt Wrapper API
1639 IF p_pa_debug_mode = 'Y' THEN
1640 pa_fp_gen_amount_utils.fp_debug
1641 (p_msg => 'Before calling
1642 pa_fp_gen_fcst_amt_pub.generate_fcst_amt_wrp',
1643 p_module_name => l_module_name,
1644 p_log_level => 5);
1645 END IF;
1646 PA_FP_GEN_FCST_AMT_PUB.GENERATE_FCST_AMT_WRP
1647 ( P_PROJECT_ID => l_fp_cols_rec.X_PROJECT_ID,
1648 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1649 P_FP_COLS_REC => l_fp_cols_rec,
1650 P_VERSION_TYPE => P_VERSION_TYPE,
1651 P_UNSPENT_AMT_FLAG => P_UNSPENT_AMT_FLAG,
1652 P_UNSPENT_AMT_PERIOD => P_UNSPENT_AMT_PERIOD,
1653 P_INCL_CHG_DOC_FLAG => P_INCL_CHG_DOC_FLAG,
1654 P_INCL_OPEN_CMT_FLAG => P_INCL_OPEN_CMT_FLAG,
1655 P_INCL_BILL_EVT_FLAG => P_INCL_BILL_EVT_FLAG,
1656 P_RET_MANUAL_LNS_FLAG => P_RET_MANUAL_LNS_FLAG,
1657 P_PLAN_TYPE_ID => P_PLAN_TYPE_ID,
1658 P_PLAN_VERSION_ID => P_PLAN_VERSION_ID,
1659 P_PLAN_VERSION_NAME => P_PLAN_VERSION_NAME,
1660 P_ETC_PLAN_TYPE_ID => P_ETC_PLAN_TYPE_ID,
1661 P_ETC_PLAN_VERSION_ID => l_ETC_PLAN_VERSION_ID,
1662 P_ETC_PLAN_VERSION_NAME => P_ETC_PLAN_VERSION_NAME,
1663 P_ACTUALS_FROM_PERIOD => P_ACTUALS_FROM_PERIOD,
1664 P_ACTUALS_TO_PERIOD => P_ACTUALS_TO_PERIOD,
1665 P_ETC_FROM_PERIOD => P_ETC_FROM_PERIOD,
1666 P_ETC_TO_PERIOD => P_ETC_TO_PERIOD,
1667 P_ACTUALS_THRU_PERIOD => P_ACTUALS_THRU_PERIOD,
1668 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1669 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
1670 X_RETURN_STATUS => X_RETURN_STATUS,
1671 X_MSG_COUNT => X_MSG_COUNT,
1672 X_MSG_DATA => X_MSG_DATA);
1673 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1674 l_return_status := X_RETURN_STATUS;
1675 --RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1676 END IF;
1677 IF p_pa_debug_mode = 'Y' THEN
1678 pa_fp_gen_amount_utils.fp_debug
1679 (p_msg => 'Status after calling
1680 pa_fp_gen_fcst_amt_pub.generate_fcst_amt_wrp: '
1681 ||x_return_status,
1682 p_module_name => l_module_name,
1683 p_log_level => 5);
1684 END IF;
1685 --dbms_output.put_line('Status of gen_fcst_amt_wrp api: '||X_RETURN_STATUS);
1686
1687 x_return_status := l_return_status;
1688
1689 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1690 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1691 END IF;
1692
1693 IF p_pa_debug_mode = 'Y' THEN
1694 PA_DEBUG.Reset_Curr_Function;
1695 END IF;
1696
1697 EXCEPTION
1698 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1699 -- Bug Fix: 4569365. Removed MRC code.
1700 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
1701 l_msg_count := FND_MSG_PUB.count_msg;
1702 IF l_msg_count = 1 THEN
1703 PA_INTERFACE_UTILS_PUB.get_messages
1704 (p_encoded => FND_API.G_TRUE
1705 ,p_msg_index => 1
1706 ,p_msg_count => l_msg_count
1707 ,p_msg_data => l_msg_data
1708 ,p_data => l_data
1709 ,p_msg_index_out => l_msg_index_out);
1710 x_msg_data := l_data;
1711 x_msg_count := l_msg_count;
1712 ELSE
1713 x_msg_count := l_msg_count;
1714 END IF;
1715 ROLLBACK;
1716
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718
1719 IF P_PA_DEBUG_MODE = 'Y' THEN
1720 pa_fp_gen_amount_utils.fp_debug
1721 (p_msg => 'Invalid Arguments Passed',
1722 p_module_name => l_module_name,
1723 p_log_level => 5);
1724 PA_DEBUG.Reset_Curr_Function;
1725 END IF;
1726 RAISE;
1727
1728 WHEN OTHERS THEN
1729 --dbms_output.put_line('inside excep');
1730 --dbms_output.put_line(SUBSTR(SQLERRM,1,240));
1731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1732 x_msg_data := SUBSTR(SQLERRM,1,240);
1733
1734 FND_MSG_PUB.add_exc_msg
1735 ( p_pkg_name => 'PA_FP_GEN_FCST_PG_PKG'
1736 ,p_procedure_name => 'UPD_VER_DTLS_AND_GEN_AMT');
1737 IF P_PA_DEBUG_MODE = 'Y' THEN
1738 pa_fp_gen_amount_utils.fp_debug
1739 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1740 p_module_name => l_module_name,
1741 p_log_level => 5);
1742 PA_DEBUG.Reset_Curr_Function;
1743 END IF;
1744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1745
1746 END UPD_VER_DTLS_AND_GEN_AMT;
1747
1748
1749 PROCEDURE VALIDATE_PERIODS
1750 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1751 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1752 P_UNSPENT_AMT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_UNSPENT_AMT_FLAG%TYPE,
1753 P_UNSPENT_AMT_PERIOD IN VARCHAR2,
1754 P_ACTUALS_FROM_PERIOD IN VARCHAR2,
1755 P_ACTUALS_TO_PERIOD IN VARCHAR2,
1756 P_ETC_FROM_PERIOD IN VARCHAR2,
1757 P_ETC_TO_PERIOD IN VARCHAR2,
1758 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1759 X_MSG_COUNT OUT NOCOPY NUMBER,
1760 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1761
1762 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.validate_periods';
1763 l_return_status VARCHAR2(30);
1764 l_msg_count NUMBER;
1765 l_msg_data VARCHAR2(2000);
1766 l_data VARCHAR2(2000);
1767 l_msg_index_out NUMBER:=0;
1768
1769 l_unspent_date DATE;
1770 l_act_frm_date DATE;
1771 l_act_to_date DATE;
1772 l_etc_frm_date DATE;
1773 l_etc_to_date DATE;
1774 l_act_thru_date DATE;
1775 l_valid_act_frm_flag VARCHAR2(1) := 'N';
1776 l_valid_act_to_flag VARCHAR2(1) := 'N';
1777 l_valid_etc_frm_flag VARCHAR2(1) := 'N';
1778 l_valid_etc_to_flag VARCHAR2(1) := 'N';
1779
1780 BEGIN
1781 --Setting initial values
1782 --FND_MSG_PUB.initialize;
1783 --X_MSG_COUNT := 0;
1784 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1785 l_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1786
1787 IF p_pa_debug_mode = 'Y' THEN
1788 pa_debug.set_curr_function( p_function => 'VALIDATE_PERIODS'
1789 ,p_debug_mode => p_pa_debug_mode);
1790 END IF;
1791
1792 --Validating unspent amount period
1793 IF P_FP_COLS_REC.X_TIME_PHASED_CODE <> 'N'
1794 AND P_UNSPENT_AMT_FLAG = 'Y'
1795 AND P_UNSPENT_AMT_PERIOD IS NULL THEN
1796 l_return_status := FND_API.G_RET_STS_ERROR;
1797 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1798 p_msg_name => 'PA_FP_NO_UNSPENT_PERIOD');
1799 END IF;
1800
1801 IF P_UNSPENT_AMT_FLAG = 'Y' AND P_UNSPENT_AMT_PERIOD IS NOT NULL THEN
1802 --Calling pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(unspent date) api
1803 IF p_pa_debug_mode = 'Y' THEN
1804 pa_fp_gen_amount_utils.fp_debug
1805 (p_msg => 'Before calling
1806 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(unspent date)',
1807 p_module_name => l_module_name,
1808 p_log_level => 5);
1809 END IF;
1810 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PA_GL_PERIODS
1811 (P_PERIOD_NAME => P_UNSPENT_AMT_PERIOD,
1812 P_FP_COLS_REC => P_FP_COLS_REC,
1813 P_CONTEXT => 'UNSPENT_PERIOD',
1814 X_END_DATE => l_unspent_date ,
1815 X_RETURN_STATUS => X_RETURN_STATUS,
1816 X_MSG_COUNT => X_MSG_COUNT,
1817 X_MSG_DATA => X_MSG_DATA);
1818 l_return_status := X_RETURN_STATUS;
1819 IF p_pa_debug_mode = 'Y' THEN
1820 pa_fp_gen_amount_utils.fp_debug
1821 (p_msg => 'Status after calling
1822 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(unspent date): '
1823 ||x_return_status,
1824 p_module_name => l_module_name,
1825 p_log_level => 5);
1826 END IF;
1827 --dbms_output.put_line('Status of validate_pa_gl_period api(unspent date): '||X_RETURN_STATUS);
1828 END IF;
1829 /* --Validating actuals from period
1830 IF P_ACTUALS_FROM_PERIOD IS NULL THEN
1831 x_return_status := FND_API.G_RET_STS_ERROR;
1832 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1833 p_msg_name => 'PA_FP_NO_ACTUALS_FROM_PERIOD');
1834 ELSE*/
1835
1836 IF P_ACTUALS_FROM_PERIOD IS NOT NULL THEN
1837 --Calling pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(actuals from) api
1838 IF p_pa_debug_mode = 'Y' THEN
1839 pa_fp_gen_amount_utils.fp_debug
1840 (p_msg => 'Before calling
1841 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(actuals from)',
1842 p_module_name => l_module_name,
1843 p_log_level => 5);
1844 END IF;
1845 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PA_GL_PERIODS
1846 (P_PERIOD_NAME => P_ACTUALS_FROM_PERIOD,
1847 P_FP_COLS_REC => P_FP_COLS_REC,
1848 P_CONTEXT => 'ACTUALS_FROM_PERIOD',
1849 X_END_DATE => l_act_frm_date,
1850 X_RETURN_STATUS => X_RETURN_STATUS,
1851 X_MSG_COUNT => X_MSG_COUNT,
1852 X_MSG_DATA => X_MSG_DATA);
1853 IF p_pa_debug_mode = 'Y' THEN
1854 pa_fp_gen_amount_utils.fp_debug
1855 (p_msg => 'Status after calling
1856 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(actuals from): '
1857 ||x_return_status,
1858 p_module_name => l_module_name,
1859 p_log_level => 5);
1860 END IF;
1861 --dbms_output.put_line('Status of validate_pa_gl_period api(actuals from): '||X_RETURN_STATUS);
1862
1863 IF X_RETURN_STATUS = 'S' THEN
1864 l_valid_act_frm_flag := 'Y';
1865 ELSE
1866 l_return_status := X_RETURN_STATUS;
1867 END IF;
1868 END IF;
1869 /* --Validating actuals to period
1870 IF P_ACTUALS_TO_PERIOD IS NULL THEN
1871 x_return_status := FND_API.G_RET_STS_ERROR;
1872 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1873 p_msg_name => 'PA_FP_NO_ACTUALS_TO_PERIOD');
1874 ELSE */
1875
1876 IF P_ACTUALS_TO_PERIOD IS NOT NULL THEN
1877 --Calling pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(actuals to) api
1878 IF p_pa_debug_mode = 'Y' THEN
1879 pa_fp_gen_amount_utils.fp_debug
1880 (p_msg => 'Before calling
1881 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(actuals to)',
1882 p_module_name => l_module_name,
1883 p_log_level => 5);
1884 END IF;
1885 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PA_GL_PERIODS
1886 (P_PERIOD_NAME => P_ACTUALS_TO_PERIOD,
1887 P_FP_COLS_REC => P_FP_COLS_REC,
1888 P_CONTEXT => 'ACTUALS_TO_PERIOD',
1889 X_END_DATE => l_act_to_date,
1890 X_RETURN_STATUS => X_RETURN_STATUS,
1891 X_MSG_COUNT => X_MSG_COUNT,
1892 X_MSG_DATA => X_MSG_DATA);
1893 IF p_pa_debug_mode = 'Y' THEN
1894 pa_fp_gen_amount_utils.fp_debug
1895 ( p_msg => 'Status after calling
1896 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(actuals to): '
1897 ||x_return_status,
1898 p_module_name => l_module_name,
1899 p_log_level => 5);
1900 END IF;
1901 --dbms_output.put_line('Status of validate_pa_gl_period api(actuals to): '||X_RETURN_STATUS);
1902
1903 IF X_RETURN_STATUS = 'S' THEN
1904 l_valid_act_to_flag := 'Y';
1905 ELSE
1906 l_return_status := X_RETURN_STATUS;
1907 END IF;
1908 END IF;
1909 /* --Validating ETC from period
1910 IF P_ETC_FROM_PERIOD IS NULL THEN
1911 x_return_status := FND_API.G_RET_STS_ERROR;
1912 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1913 p_msg_name => 'PA_FP_NO_ETC_FROM_PERIOD');
1914 ELSE*/
1915 IF P_ETC_FROM_PERIOD IS NOT NULL THEN
1916 --Calling pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(etc from) api
1917 IF p_pa_debug_mode = 'Y' THEN
1918 pa_fp_gen_amount_utils.fp_debug
1919 (p_msg => 'Before calling
1920 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(etc from)',
1921 p_module_name => l_module_name,
1922 p_log_level => 5);
1923 END IF;
1924 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PA_GL_PERIODS
1925 (P_PERIOD_NAME => P_ETC_FROM_PERIOD,
1926 P_FP_COLS_REC => P_FP_COLS_REC,
1927 P_CONTEXT => 'ETC_FROM_PERIOD',
1928 X_END_DATE => l_etc_frm_date,
1929 X_RETURN_STATUS => X_RETURN_STATUS,
1930 X_MSG_COUNT => X_MSG_COUNT,
1931 X_MSG_DATA => X_MSG_DATA);
1932 IF p_pa_debug_mode = 'Y' THEN
1933 pa_fp_gen_amount_utils.fp_debug
1934 (p_msg => 'Status after calling
1935 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(etc from): '
1936 ||x_return_status,
1937 p_module_name => l_module_name,
1938 p_log_level => 5);
1939 END IF;
1940 --dbms_output.put_line('Status of validate_pa_gl_period api(etc from): '||X_RETURN_STATUS);
1941
1942 IF X_RETURN_STATUS = 'S' THEN
1943 l_valid_etc_frm_flag := 'Y';
1944 ELSE
1945 l_return_status := X_RETURN_STATUS;
1946 END IF;
1947 END IF;
1948
1949 /* --Validating ETC to period
1950 IF P_ETC_TO_PERIOD IS NULL THEN
1951 x_return_status := FND_API.G_RET_STS_ERROR;
1952 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1953 p_msg_name => 'PA_FP_NO_ETC_TO_PERIOD');
1954 ELSE */
1955 --Calling pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(etc to) api
1956 IF P_ETC_TO_PERIOD IS NOT NULL THEN
1957 IF p_pa_debug_mode = 'Y' THEN
1958 pa_fp_gen_amount_utils.fp_debug
1959 (p_msg => 'Before calling
1960 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(etc to)',
1961 p_module_name => l_module_name,
1962 p_log_level => 5);
1963 END IF;
1964 PA_FP_GEN_FCST_PG_PKG.VALIDATE_PA_GL_PERIODS
1965 (P_PERIOD_NAME => P_ETC_TO_PERIOD,
1966 P_FP_COLS_REC => P_FP_COLS_REC,
1967 P_CONTEXT => 'ETC_TO_PERIOD',
1968 X_END_DATE => l_etc_to_date,
1969 X_RETURN_STATUS => X_RETURN_STATUS,
1970 X_MSG_COUNT => X_MSG_COUNT,
1971 X_MSG_DATA => X_MSG_DATA);
1972 IF p_pa_debug_mode = 'Y' THEN
1973 pa_fp_gen_amount_utils.fp_debug
1974 (p_msg => 'Status after calling
1975 pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods(etc to): '
1976 ||x_return_status,
1977 p_module_name => l_module_name,
1978 p_log_level => 5);
1979 END IF;
1980 --dbms_output.put_line('Status of validate_pa_gl_period api(etc to): '||X_RETURN_STATUS);
1981
1982 IF X_RETURN_STATUS = 'S' THEN
1983 l_valid_etc_to_flag := 'Y';
1984 ELSE
1985 l_return_status := X_RETURN_STATUS;
1986 END IF;
1987 END IF;
1988
1989 --dbms_output.put_line('Value of valid act frm flag: '||l_valid_act_frm_flag);
1990 --dbms_output.put_line('Value of valid act to flag: '||l_valid_act_to_flag);
1991
1992 IF l_valid_act_frm_flag = 'Y' AND l_valid_act_to_flag = 'Y' THEN
1993 --dbms_output.put_line('all flags are Y');
1994 --dbms_output.put_line('act_to_date:'||l_act_to_date);
1995 --dbms_output.put_line('act_frm_date:'||l_act_frm_date);
1996 IF l_act_to_date < l_act_frm_date THEN
1997 l_return_status := FND_API.G_RET_STS_ERROR;
1998 --dbms_output.put_line(l_return_status);
1999 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2000 p_msg_name => 'PA_FP_INV_ACT_PD_RANGE');
2001 END IF;
2002 END IF;
2003
2004 --dbms_output.put_line('Value of valid etc frm flag: '||l_valid_etc_frm_flag);
2005 --dbms_output.put_line('Value of valid etc to flag: '||l_valid_etc_to_flag);
2006
2007 IF l_valid_etc_frm_flag = 'Y' AND l_valid_etc_to_flag = 'Y' THEN
2008 IF l_etc_to_date < l_etc_frm_date THEN
2009 l_return_status := FND_API.G_RET_STS_ERROR;
2010 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2011 p_msg_name => 'PA_FP_INV_ETC_PD_RANGE');
2012 END IF;
2013 END IF;
2014
2015 l_act_thru_date := to_date(GET_ACTUALS_THRU_PERIOD_DTLS(
2016 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2017 P_CONTEXT => 'END_DATE'),'RRRRMMDD');
2018
2019 --dbms_output.put_line('Value of act_thru_date: '||l_act_thru_date);
2020
2021 IF l_act_frm_date > l_act_thru_date THEN
2022 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2023 p_msg_name => 'PA_FP_ACT_FP_NOT_IN_ATP');
2024 ELSIF l_act_to_date > l_act_thru_date THEN
2025 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2026 p_msg_name => 'PA_FP_ACT_TP_NOT_IN_ATP');
2027 ELSIF l_etc_frm_date < l_act_thru_date THEN
2028 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2029 p_msg_name => 'PA_FP_ETC_FP_NOT_IN_ATP');
2030 ELSIF l_etc_to_date < l_act_thru_date THEN
2031 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2032 p_msg_name => 'PA_FP_ETC_TP_NOT_IN_ATP');
2033 END IF;
2034
2035 x_return_status := l_return_status;
2036
2037 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2038 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2039 END IF;
2040
2041 IF p_pa_debug_mode = 'Y' THEN
2042 PA_DEBUG.Reset_Curr_Function;
2043 END IF;
2044
2045 EXCEPTION
2046 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2047 -- Bug Fix: 4569365. Removed MRC code.
2048 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
2049 l_msg_count := FND_MSG_PUB.count_msg;
2050 IF l_msg_count = 1 THEN
2051 PA_INTERFACE_UTILS_PUB.get_messages
2052 (p_encoded => FND_API.G_TRUE
2053 ,p_msg_index => 1
2054 ,p_msg_count => l_msg_count
2055 ,p_msg_data => l_msg_data
2056 ,p_data => l_data
2057 ,p_msg_index_out => l_msg_index_out);
2058 x_msg_data := l_data;
2059 x_msg_count := l_msg_count;
2060 ELSE
2061 x_msg_count := l_msg_count;
2062 END IF;
2063 ROLLBACK;
2064
2065 x_return_status := FND_API.G_RET_STS_ERROR;
2066 IF P_PA_DEBUG_MODE = 'Y' THEN
2067 pa_fp_gen_amount_utils.fp_debug
2068 (p_msg => 'Invalid Arguments Passed',
2069 p_module_name => l_module_name,
2070 p_log_level => 5);
2071 PA_DEBUG.Reset_Curr_Function;
2072 END IF;
2073 RAISE;
2074
2075 WHEN OTHERS THEN
2076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2077 x_msg_data := SUBSTR(SQLERRM,1,240);
2078 FND_MSG_PUB.add_exc_msg
2079 ( p_pkg_name => 'PA_FP_GEN_FCST_PG_PKG'
2080 ,p_procedure_name => 'VALIDATE_PERIODS');
2081 IF P_PA_DEBUG_MODE = 'Y' THEN
2082 pa_fp_gen_amount_utils.fp_debug
2083 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2084 p_module_name => l_module_name,
2085 p_log_level => 5);
2086 PA_DEBUG.Reset_Curr_Function;
2087 END IF;
2088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2089 END VALIDATE_PERIODS;
2090
2091
2092 PROCEDURE VALIDATE_PA_GL_PERIODS
2093 (P_PERIOD_NAME IN PA_PERIODS_ALL.PERIOD_NAME%TYPE,
2094 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2095 P_CONTEXT IN VARCHAR2,
2096 P_ERROR_MSG_CODE IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE,
2097 X_END_DATE OUT NOCOPY DATE,
2098 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2099 X_MSG_COUNT OUT NOCOPY NUMBER,
2100 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
2101
2102 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.validate_pa_gl_periods';
2103 l_return_status VARCHAR2(30);
2104 l_msg_count NUMBER;
2105 l_msg_data VARCHAR2(2000);
2106 l_data VARCHAR2(2000);
2107 l_msg_index_out NUMBER:=0;
2108
2109 BEGIN
2110 --Setting initial values
2111 --FND_MSG_PUB.initialize;
2112 --X_MSG_COUNT := 0;
2113 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2114
2115 IF p_pa_debug_mode = 'Y' THEN
2116 pa_debug.set_curr_function( p_function => 'VALIDATE_PA_GL_PERIODS'
2117 ,p_debug_mode => p_pa_debug_mode);
2118 END IF;
2119
2120 IF P_FP_COLS_REC.X_TIME_PHASED_CODE = 'P' THEN
2121 SELECT END_DATE
2122 INTO X_END_DATE
2123 FROM PA_PERIODS_ALL
2124 WHERE ORG_ID = p_fp_cols_rec.x_org_id
2125 AND PERIOD_NAME = p_period_name;
2126 --dbms_output.put_line('End date from validate_pa_gl_periods(P) api:'||X_END_DATE);
2127
2128 ELSIF P_FP_COLS_REC.X_TIME_PHASED_CODE = 'G' THEN
2129 SELECT END_DATE
2130 INTO X_END_DATE
2131 FROM GL_PERIOD_STATUSES
2132 WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
2133 AND SET_OF_BOOKS_ID = p_fp_cols_rec.x_set_of_books_id
2134 AND ADJUSTMENT_PERIOD_FLAG = 'N'
2135 AND PERIOD_NAME = p_period_name;
2136 --dbms_output.put_line('End date from validate_pa_gl_periods(G) api:'||X_END_DATE);
2137
2138 END IF;
2139
2140 IF P_PA_DEBUG_MODE = 'Y' THEN
2141 PA_DEBUG.Reset_Curr_Function;
2142 END IF;
2143
2144 --dbms_output.put_line('return status from pa_gl_periods api before exception: '||x_return_status);
2145 EXCEPTION
2146 WHEN NO_DATA_FOUND THEN
2147 x_return_status := FND_API.G_RET_STS_ERROR;
2148
2149 IF P_CONTEXT = 'UNSPENT_PERIOD' THEN
2150 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2151 p_msg_name => 'PA_FP_INV_UNSPENT_PERIOD');
2152 ELSIF P_CONTEXT = 'ACTUALS_FROM_PERIOD' THEN
2153 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2154 p_msg_name => 'PA_FP_INV_ACT_FP');
2155 ELSIF P_CONTEXT = 'ACTUALS_TO_PERIOD' THEN
2156 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2157 p_msg_name => 'PA_FP_INV_ACT_TP');
2158 ELSIF P_CONTEXT = 'ETC_FROM_PERIOD' THEN
2159 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2160 p_msg_name => 'PA_FP_INV_ETC_FP');
2161 ELSIF P_CONTEXT = 'ETC_TO_PERIOD' THEN
2162 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2163 p_msg_name => 'PA_FP_INV_ETC_TP');
2164 END IF;
2165 --dbms_output.put_line('return status from pa_gl_periods api inside exception(NDF): '||x_return_status);
2166 -- Bug Fix: 4569365. Removed MRC code.
2167 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
2168 l_msg_count := FND_MSG_PUB.count_msg;
2169 IF l_msg_count = 1 THEN
2170 PA_INTERFACE_UTILS_PUB.get_messages
2171 (p_encoded => FND_API.G_TRUE
2172 ,p_msg_index => 1
2173 ,p_msg_count => l_msg_count
2174 ,p_msg_data => l_msg_data
2175 ,p_data => l_data
2176 ,p_msg_index_out => l_msg_index_out);
2177 x_msg_data := l_data;
2178 x_msg_count := l_msg_count;
2179 ELSE
2180 x_msg_count := l_msg_count;
2181 END IF;
2182 ROLLBACK;
2183
2184 x_return_status := FND_API.G_RET_STS_ERROR;
2185 IF P_PA_DEBUG_MODE = 'Y' THEN
2186 pa_fp_gen_amount_utils.fp_debug
2187 (p_msg => 'Invalid periods',
2188 p_module_name => l_module_name,
2189 p_log_level => 5);
2190 PA_DEBUG.Reset_Curr_Function;
2191 END IF;
2192 RAISE;
2193
2194
2195 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2196 -- Bug Fix: 4569365. Removed MRC code.
2197 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
2198 l_msg_count := FND_MSG_PUB.count_msg;
2199 IF l_msg_count = 1 THEN
2200 PA_INTERFACE_UTILS_PUB.get_messages
2201 (p_encoded => FND_API.G_TRUE
2202 ,p_msg_index => 1
2203 ,p_msg_count => l_msg_count
2204 ,p_msg_data => l_msg_data
2205 ,p_data => l_data
2206 ,p_msg_index_out => l_msg_index_out);
2207 x_msg_data := l_data;
2208 x_msg_count := l_msg_count;
2209 ELSE
2210 x_msg_count := l_msg_count;
2211 END IF;
2212 ROLLBACK;
2213
2214 x_return_status := FND_API.G_RET_STS_ERROR;
2215 IF P_PA_DEBUG_MODE = 'Y' THEN
2216 pa_fp_gen_amount_utils.fp_debug
2217 (p_msg => 'Invalid Arguments Passed',
2218 p_module_name => l_module_name,
2219 p_log_level => 5);
2220 PA_DEBUG.Reset_Curr_Function;
2221 END IF;
2222 RAISE;
2223
2224 WHEN OTHERS THEN
2225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2226 x_msg_data := SUBSTR(SQLERRM,1,240);
2227 FND_MSG_PUB.add_exc_msg
2228 ( p_pkg_name => 'PA_FP_GEN_FCST_PG_PKG'
2229 ,p_procedure_name => 'VALIDATE_PA_GL_PERIODS');
2230 IF P_PA_DEBUG_MODE = 'Y' THEN
2231 pa_fp_gen_amount_utils.fp_debug
2232 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2233 p_module_name => l_module_name,
2234 p_log_level => 5);
2235 PA_DEBUG.Reset_Curr_Function;
2236 END IF;
2237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2238 --dbms_output.put_line('return status from pa_gl_periods api inside exception(O): '||x_return_status);
2239
2240 END VALIDATE_PA_GL_PERIODS;
2241
2242 PROCEDURE VALIDATE_PLAN_TYPE_OR_VERSION
2243 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
2244 P_PLAN_TYPE_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
2245 PX_PLAN_VERSION_ID IN OUT NOCOPY PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
2246 P_PLAN_VERSION_NAME IN PA_BUDGET_VERSIONS.VERSION_NAME%TYPE,
2247 P_CALLING_CONTEXT IN VARCHAR2,
2248 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2249 X_MSG_COUNT OUT NOCOPY NUMBER,
2250 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
2251
2252 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_pg_pkg.validate_plan_type_or_version';
2253 l_plan_version_id PA_BUDGET_VERSIONS.VERSION_NAME%TYPE;
2254 l_msg_count NUMBER;
2255 l_msg_data VARCHAR2(2000);
2256 l_data VARCHAR2(2000);
2257 l_msg_index_out NUMBER:=0;
2258
2259 BEGIN
2260 --Setting initial values
2261 --FND_MSG_PUB.initialize;
2262 --X_MSG_COUNT := 0;
2263 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2264
2265 IF p_pa_debug_mode = 'Y' THEN
2266 pa_debug.set_curr_function( p_function => 'VALIDATE_PLAN_TYPE_OR_VERSION'
2267 ,p_debug_mode => p_pa_debug_mode);
2268 END IF;
2269
2270 IF p_plan_type_id IS NULL THEN
2271 IF p_calling_context = 'GENERATION_SOURCE' THEN
2272 x_return_status := FND_API.G_RET_STS_ERROR;
2273 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2274 p_msg_name => 'PA_FP_NO_PLAN_TYPE_ID_SRC');
2275 ELSIF p_calling_context = 'ETC_GENERATION_SOURCE' THEN
2276 x_return_status := FND_API.G_RET_STS_ERROR;
2277 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2278 p_msg_name => 'PA_FP_NO_PLAN_TYPE_ID_ETC_SRC');
2279 END IF;
2280 END IF;
2281
2282 IF px_plan_version_id IS NOT NULL THEN
2283 IF P_PA_DEBUG_MODE = 'Y' THEN
2284 PA_DEBUG.RESET_CURR_FUNCTION;
2285 END IF;
2286 RETURN;
2287 END IF;
2288
2289 IF p_plan_version_name IS NULL THEN
2290 x_return_status := FND_API.G_RET_STS_ERROR;
2291 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2292 p_msg_name => 'PA_FP_NO_PLAN_VERSION_NAME');
2293 END IF;
2294
2295 SELECT bv.budget_version_id
2296 INTO l_plan_version_id
2297 FROM pa_budget_versions bv
2298 WHERE bv.project_id = p_project_id
2299 AND bv.fin_plan_type_id = p_plan_type_id
2300 AND bv.version_name = p_plan_version_name
2301 AND bv.version_type in ('COST','ALL');
2302
2303 px_plan_version_id := l_plan_version_id;
2304
2305 IF P_PA_DEBUG_MODE = 'Y' THEN
2306 PA_DEBUG.Reset_Curr_Function;
2307
2308 END IF;
2309
2310
2311 EXCEPTION
2312 WHEN NO_DATA_FOUND THEN
2313 x_return_status := FND_API.G_RET_STS_ERROR;
2314 IF p_calling_context = 'GENERATION_SOURCE' THEN
2315 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2316 p_msg_name => 'PA_FP_INV_GEN_BV');
2317 ELSIF p_calling_context = 'ETC_GENERATION_SOURCE' THEN
2318 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2319 p_msg_name => 'PA_FP_INV_ETC_BV');
2320 END IF;
2321 IF P_PA_DEBUG_MODE = 'Y' THEN
2322 pa_fp_gen_amount_utils.fp_debug
2323 (p_msg => 'Invalid source',
2324 p_module_name => l_module_name,
2325 p_log_level => 5);
2326 PA_DEBUG.Reset_Curr_Function;
2327 END IF;
2328 RAISE;
2329
2330 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2331 l_msg_count := FND_MSG_PUB.count_msg;
2332 IF l_msg_count = 1 THEN
2333 PA_INTERFACE_UTILS_PUB.get_messages
2334 (p_encoded => FND_API.G_TRUE
2335 ,p_msg_index => 1
2336 ,p_msg_count => l_msg_count
2337 ,p_msg_data => l_msg_data
2338 ,p_data => l_data
2339 ,p_msg_index_out => l_msg_index_out);
2340 x_msg_data := l_data;
2341 x_msg_count := l_msg_count;
2342 ELSE
2343 x_msg_count := l_msg_count;
2344 END IF;
2345 ROLLBACK;
2346
2347 x_return_status := FND_API.G_RET_STS_ERROR;
2348 IF P_PA_DEBUG_MODE = 'Y' THEN
2349 pa_fp_gen_amount_utils.fp_debug
2350 (p_msg => 'Invalid Arguments Passed',
2351 p_module_name => l_module_name,
2352 p_log_level => 5);
2353 PA_DEBUG.Reset_Curr_Function;
2354 END IF;
2355 RAISE;
2356
2357 WHEN OTHERS THEN
2358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2359 x_msg_data := SUBSTR(SQLERRM,1,240);
2360 FND_MSG_PUB.add_exc_msg
2361 ( p_pkg_name => 'PA_FP_GEN_FCST_PG_PKG'
2362 ,p_procedure_name => 'VALIDATE_PLAN_TYPE_OR_VERSION');
2363 IF P_PA_DEBUG_MODE = 'Y' THEN
2364 pa_fp_gen_amount_utils.fp_debug
2365 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2366 p_module_name => l_module_name,
2367 p_log_level => 5);
2368 PA_DEBUG.Reset_Curr_Function;
2369 END IF;
2370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2371
2372 END VALIDATE_PLAN_TYPE_OR_VERSION;
2373
2374 END PA_FP_GEN_FCST_PG_PKG;