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