[Home] [Help]
PACKAGE BODY: APPS.PA_REPORT_UTIL
Source
1 PACKAGE BODY PA_REPORT_UTIL AS
2 /* $Header: PARFRULB.pls 120.1 2005/07/04 06:57:50 appldev ship $ */
3
4 ------------------------------------------------------------------------------------------------------------------
5 -- This procedure will populate the values for screen U1 ,and U2
6 -- Input parameters
7 -- Parameters Type Required Description
8 --
9 -- Out parameters
10 -- x_org_id NUMBER YES It store the org id
11 -- x_def_period_typ VARCHAR2 YES It store the default period type
12 -- x_def_period_yr VARCHAR2 YES It store the default period year
13 -- x_def_period_name VARCHAR2 YES It store the default period name
14 -- x_def_period_per VARCHAR2 YES It store the default period percentage
15 -- x_billing_installed VARCHAR2 YES It store billing installed check
16 -- x_prm_installed VARCHAR2 YES It store prm installed check
17 --
18 --------------------------------------------------------------------------------------------------------------------
19 PROCEDURE get_default_val(
20 p_calling_screen IN VARCHAR2,
21 x_org_id OUT NOCOPY NUMBER,
22 x_org_name OUT NOCOPY VARCHAR2,
23 x_def_period_typ OUT NOCOPY VARCHAR2,
24 x_def_period_typ_desc OUT NOCOPY VARCHAR2,
25 x_def_period_yr OUT NOCOPY VARCHAR2,
26 x_def_period_name OUT NOCOPY VARCHAR2,
27 x_def_period_name_desc OUT NOCOPY VARCHAR2,
28 x_def_show_percentages_by OUT NOCOPY VARCHAR2,
29 x_billing_installed OUT NOCOPY VARCHAR2,
30 x_prm_installed OUT NOCOPY VARCHAR2,
31 x_login_person_name OUT NOCOPY VARCHAR2,
32 x_login_person_id OUT NOCOPY NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2)
36
37 IS
38 l_prd_name gl_periods.period_name%TYPE;
39 l_quarter_num gl_periods.quarter_num%TYPE;
40 l_prd_yr gl_periods.period_year%TYPE;
41 l_meaning gl_lookups.meaning%TYPE;
42 l_name_desc VARCHAR2(120);
43
44 l_period_type gl_periods.period_type%TYPE;
45 l_week_ending_date DATE;
46 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
47 l_msg_index_out INTEGER:=1;
48 l_forecast_thru_date DATE;
49 l_default_date DATE := trunc(SYSDATE);
50 l_date_format VARCHAR2(100); -- Added for Bug 2387429 and 2091182
51 /* NPE Changes Begin*/
52 l_def_show_percentages_by varchar2(240);
53 l_global_week_start_day varchar2(240);
54 l_user_profile_option_name1 varchar2(1000);
55 l_user_profile_option_name2 varchar2(1000);
56 l_user_profile_option_name3 varchar2(1000);
57 l_user_profile_option_name4 varchar2(1000);
58 l_msg_count NUMBER := 0;
59 l_data VARCHAR2(2000);
60 l_msg_data VARCHAR2(2000);
61 l_return_status VARCHAR2(2000);
62 l_err_msg VARCHAR2(3000);
63 /* NPE Changes End */
64
65 dummy char ; -- Added for bug2440313
66
67
68 BEGIN
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 --Clear the global PL/SQL message table
72 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 /* Bug2440313 Begin */
77 BEGIN
78 select 'x' into dummy
79 from pa_implementations;
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 x_return_status := FND_API.G_RET_STS_ERROR;
83 PA_UTILS.Add_Message( p_app_short_name =>'PA',
84 p_msg_name => 'PA_INCORRECT_MO_OPERATING_UNIT');
85
86 l_msg_count := FND_MSG_PUB.count_msg;
87 IF l_msg_count > 0 then
88 IF l_msg_count = 1 then
89 PA_INTERFACE_UTILS_PUB.get_messages
90 (p_encoded => FND_API.G_TRUE,
91 p_msg_index => 1,
92 p_msg_count => l_msg_count,
93 p_msg_data => l_msg_data,
94 p_data => l_data,
95 p_msg_index_out => l_msg_index_out);
96
97 x_msg_data := l_data;
98 x_msg_count := l_msg_count;
99 ELSE
100 x_msg_count := l_msg_count;
101 END IF;
102 pa_debug.reset_err_stack;
103 return;
104 END IF;
105 END;
106 /* Bug2440313 End */
107
108 IF (p_calling_screen = 'U1') THEN
109 -- Populate org id
110 BEGIN
111 SELECT parent_organization_id,parent_org_name
112 INTO x_org_id,x_org_name
113 FROM ( SELECT DISTINCT parent_organization_id,parent_org_name
114 FROM pa_rep_org_util_v
115 ORDER BY parent_org_name )
116 WHERE ROWNUM = 1;
117
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120 /* NPE Suggestion - Ideally here we should give message that the user does not have
121 the organization authorities on any organizations for the current Operatin Unit.
122 Note that no need to "return" here after populating the message, as further other
123 profile options are being checked that whether they have been populated or not*/
124
125 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_ORG_NAME');
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 x_msg_data := 'PA_UTIL_INVALID_ORG_NAME';
128 x_msg_count := 1;
129 IF x_msg_count = 1 THEN
130 PA_INTERFACE_UTILS_PUB.get_messages
131 (p_encoded => FND_API.G_TRUE,
132 p_msg_index => 1,
133 p_data => x_msg_data,
134 p_msg_index_out => l_msg_index_out );
135 END IF;
136 END;
137 END IF;
138
139
140 -- Populating Billing product is installed
141 x_billing_installed := PA_INSTALL.is_costing_licensed;
142
143 -- Populating Prm product is installed
144 x_prm_installed := PA_INSTALL.is_prm_licensed;
145
146 IF FND_GLOBAL.USER_ID IS NOT NULL THEN
147 -- Populating person id person name
148 BEGIN
149 SELECT rsd.resource_name,rsd.person_id
150 INTO x_login_person_name,x_login_person_id
151 FROM pa_resources_denorm rsd, fnd_user usr
152 WHERE rsd.person_id = usr.employee_id
153 AND usr.user_id = fnd_global.user_id
154 AND rsd.resource_effective_end_date >= sysdate
155 AND rownum < 2
156 ;
157
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 NULL;
161 WHEN OTHERS THEN
162 NULL;
163 END;
164 END IF;
165
166
167 -- Populating default period type using profile options
168 l_period_type := FND_PROFILE.VALUE('PA_ORG_UTIL_DEF_PERIOD_TYPE');
169
170 /* NPE Changes Begin */
171 -- Populating default period percentage using profile options
172 l_def_show_percentages_by := FND_PROFILE.VALUE('PA_ORG_UTIL_DEF_CALC_METHOD');
173
174 l_global_week_start_day := FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY');
175
176 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name1
177 FROM fnd_profile_options_tl
178 WHERE profile_option_name='PA_ORG_UTIL_DEF_CALC_METHOD'
179 AND language=userenv('LANG');
180
181 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name2
182 FROM fnd_profile_options_tl
183 WHERE profile_option_name='PA_ORG_UTIL_DEF_PERIOD_TYPE'
184 AND language=userenv('LANG');
185
186 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name3
187 FROM fnd_profile_options_tl
188 WHERE profile_option_name='PA_GLOBAL_WEEK_START_DAY'
189 AND language=userenv('LANG');
190
191
192 IF l_period_type is NULL AND l_def_show_percentages_by is NULL THEN
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 l_err_msg := l_user_profile_option_name1;
195 l_err_msg:=l_err_msg||', '||l_user_profile_option_name2;
196 ELSIF l_period_type is NULL AND l_def_show_percentages_by is NOT NULL THEN
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 l_err_msg:=l_user_profile_option_name2;
199 ELSIF l_period_type is NOT NULL AND l_def_show_percentages_by is NULL THEN
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 l_err_msg:=l_user_profile_option_name1;
202 IF l_period_type = 'GE' AND l_global_week_start_day IS NULL THEN
203 l_err_msg:=l_err_msg||', '||l_user_profile_option_name3;
204 END IF;
205 END IF;
206
207 IF x_return_status = FND_API.G_RET_STS_ERROR and l_err_msg is not null THEN
208 PA_UTILS.Add_Message( p_app_short_name =>'PA',
209 p_msg_name => 'PA_UNDEFINED_PROFILES',
210 p_token1 => 'PROFILES',
211 p_value1 => l_err_msg);
212
213 l_msg_count := FND_MSG_PUB.count_msg;
214 IF l_msg_count > 0 then
215 IF l_msg_count = 1 then
216 PA_INTERFACE_UTILS_PUB.get_messages
217 (p_encoded => FND_API.G_TRUE,
218 p_msg_index => 1,
219 p_msg_count => l_msg_count,
220 p_msg_data => l_msg_data,
221 p_data => l_data,
222 p_msg_index_out => l_msg_index_out);
223
224 x_msg_data := l_data;
225 x_msg_count := l_msg_count;
226 ELSE
227 x_msg_count := l_msg_count;
228 END IF;
229 END IF;
230 END IF;
231
232 /*NPE changes ends */
233
234 -- Fetching the default forecast thru date information from utilization options
235 BEGIN
236 select forecast_thru_date
237 into l_forecast_thru_date
238 from pa_utilization_options ut ;
239
240 /*Bug2440313 -- Commented this, the check for pa_implementaions is being done
241 above in the code
242 ,pa_implementations imp --NPE changes
243 where nvl(ut.org_id, -99) = nvl(imp.org_id,-99); --NPE changes
244 */
245
246 IF (l_forecast_thru_date > sysdate
247 OR l_forecast_thru_date is NULL) then
248 l_default_date := sysdate;
249 ELSE
250 l_default_date := l_forecast_thru_date;
251 END IF;
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254
255 /* NPE Changes Begins */
256 /* l_default_date := sysdate; -- Commented this code */
257 /* Bug2440313 Begin -- Undone the check for MO: Operating Unit here. The Operating Unit check
258 is now done above in this code. Here we do check for utilization options defined or not */
259
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 PA_UTILS.Add_Message( p_app_short_name =>'PA',
262 p_msg_name => 'PA_UNDEFINED_UTIL_OPTIONS');
263
264 l_msg_count := FND_MSG_PUB.count_msg;
265 IF l_msg_count > 0 then
266 IF l_msg_count = 1 then
267 PA_INTERFACE_UTILS_PUB.get_messages
268 (p_encoded => FND_API.G_TRUE,
269 p_msg_index => 1,
270 p_msg_count => l_msg_count,
271 p_msg_data => l_msg_data,
272 p_data => l_data,
273 p_msg_index_out => l_msg_index_out);
274
275 x_msg_data := l_data;
276 x_msg_count := l_msg_count;
277 ELSE
278 x_msg_count := l_msg_count;
279 END IF;
280 END IF;
281 /* Bug2440313 End */
282 END;
283
284 -- Collectively check for x_return_status, if error then return
285 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
286 pa_debug.reset_err_stack;
287 return;
288 END IF;
289
290 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
291 x_def_show_percentages_by := l_def_show_percentages_by;
292
293 /*NPE changes ends */
294
295 PA_REP_UTIL_GLOB.SETU1SHOWPRCTGBY (x_def_show_percentages_by);
296
297 l_default_date := trunc(l_default_date);
298
299 -- Populating default period year taking from forecast thru date or sysdate
300 x_def_period_yr := TO_CHAR(l_default_date,'YYYY');
301
302 x_def_period_typ := l_period_type;
303
304 -- Populating period desc
305 SELECT meaning
306 INTO x_def_period_typ_desc
307 FROM pa_lookups
308 WHERE lookup_type = 'PA_REP_PERIOD_TYPES'
309 AND lookup_code = l_period_type;
310
311
312 -- Populating default period name
313 IF ( l_period_type = 'PA') THEN
314 BEGIN -- NPE Changes
315 SELECT paperiod.period_name,
316 paperiod.period_year,
317 paperiod.status_meaning
318 INTO l_prd_name,l_prd_yr,l_meaning
319 FROM pa_periods_v paperiod,
320 pa_implementations imp,
321 gl_period_statuses glpersts
322 WHERE glpersts.period_type = imp.pa_period_type
323 AND imp.set_of_books_id = paperiod.set_of_books_id
324 AND glpersts.set_of_books_id = imp.set_of_books_id
325 AND glpersts.period_name = paperiod.period_name
326 AND glpersts.period_year = paperiod.period_year
327 AND glpersts.application_id = 275
328 AND l_default_date BETWEEN paperiod.pa_start_date AND paperiod.pa_end_date
329 AND ROWNUM=1
330 ORDER BY paperiod.period_year;
331
332 -- Populating default period name,period desc, and default period year
333 x_def_period_name := l_prd_name;
334 x_def_period_yr := l_prd_yr;
335 x_def_period_name_desc := l_prd_name||' '||l_meaning;
336
337 /*NPE changes Begins */
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 PA_UTILS.Add_Message( p_app_short_name =>'PA',
342 p_msg_name => 'PA_PRD_NOT_DEFINED_FOR_DATE',
343 p_token1 => 'PA_DATE',
344 p_value1 => to_char(l_default_date,l_date_format));
345 l_msg_count := FND_MSG_PUB.count_msg;
346 IF l_msg_count > 0 then
347 IF l_msg_count = 1 then
348 PA_INTERFACE_UTILS_PUB.get_messages
349 (p_encoded => FND_API.G_TRUE,
350 p_msg_index => 1,
351 p_msg_count => l_msg_count,
352 p_msg_data => l_msg_data,
353 p_data => l_data,
354 p_msg_index_out => l_msg_index_out);
355
356 x_msg_data := l_data;
357 x_msg_count := l_msg_count;
358 ELSE
359 x_msg_count := l_msg_count;
360 END IF;
361 pa_debug.reset_err_stack;
362 return;
363 END IF;
364 END;
365 /*NPE changes Ends */
366 ELSIF( l_period_type = 'GL') THEN
367 BEGIN -- NPE Changes
368
369 SELECT glper.period_name,glper.period_year,gllkups.meaning
370 INTO l_prd_name,l_prd_yr,l_meaning
371 FROM pa_implementations imp,
372 gl_sets_of_books gl,
373 gl_periods glper,
374 gl_period_statuses glst,
375 gl_lookups gllkups
376 WHERE imp.set_of_books_id = gl.set_of_books_id
377 AND gl.period_set_name = glper.period_set_name
378 AND gl.accounted_period_type = glper.period_type
379 AND glper.period_name = glst.period_name
380 AND glper.period_type = glst.period_type
381 AND glst.set_of_books_id=imp.set_of_books_id
382 AND glst.application_id = PA_Period_Process_PKG.Application_ID
383 AND gllkups.lookup_code = glst.closing_status
384 AND gllkups.lookup_type = 'CLOSING_STATUS'
385 /* Bug 2288460 - Start */
386 /* Added the following clause */
387 AND glper.adjustment_period_flag = 'N'
388 /* Bug 2288460 - End */
389 AND l_default_date BETWEEN glper.start_date AND glper.end_date
390 AND ROWNUM=1
391 ORDER BY glper.period_year;
392
393 -- Populating default period name,period desc, and default period year
394 x_def_period_name := l_prd_name;
395 x_def_period_yr := l_prd_yr;
396 x_def_period_name_desc := l_prd_name||' '||l_meaning;
397
398 /*NPE changes starts */
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 PA_UTILS.Add_Message( p_app_short_name =>'PA',
403 p_msg_name => 'PA_GL_PRD_NOT_DEFINED_FOR_DATE',
404 p_token1 => 'GL_DATE',
405 p_value1 => to_char(l_default_date,l_date_format));
406 l_msg_count := FND_MSG_PUB.count_msg;
407 IF l_msg_count > 0 then
408 IF l_msg_count = 1 then
409 PA_INTERFACE_UTILS_PUB.get_messages
410 (p_encoded => FND_API.G_TRUE,
411 p_msg_index => 1,
412 p_msg_count => l_msg_count,
413 p_msg_data => l_msg_data,
414 p_data => l_data,
415 p_msg_index_out => l_msg_index_out);
416
417 x_msg_data := l_data;
418 x_msg_count := l_msg_count;
419 ELSE
420 x_msg_count := l_msg_count;
421 END IF;
422 pa_debug.reset_err_stack;
423 return;
424 END IF;
425 END;
426 /*NPE changes Ends */
427 ELSIF ( l_period_type = 'GE') THEN
428 /* NPE Changes Begins */
429 IF l_global_week_start_day IS NULL THEN
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 PA_UTILS.Add_Message( p_app_short_name =>'PA',
432 p_msg_name => 'PA_UNDEFINED_PROFILES',
433 p_token1 => 'PROFILES',
434 p_value1 => l_user_profile_option_name3);
435
436 l_msg_count := FND_MSG_PUB.count_msg;
437 IF l_msg_count > 0 then
438 IF l_msg_count = 1 then
439 PA_INTERFACE_UTILS_PUB.get_messages
440 (p_encoded => FND_API.G_TRUE,
441 p_msg_index => 1,
442 p_msg_count => l_msg_count,
443 p_msg_data => l_msg_data,
444 p_data => l_data,
445 p_msg_index_out => l_msg_index_out);
446
447 x_msg_data := l_data;
448 x_msg_count := l_msg_count;
449 ELSE
450 x_msg_count := l_msg_count;
451 END IF;
452 pa_debug.reset_err_stack;
453 return;
454 END IF;
455 END IF;
456 /* NPE Changes Ends */
457
458 -- Populating period name
459 /** Fix for bug 2387429 and 2091182 starts here **/
460 /* -- NPE Changes, it is shifted to above in the code l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT); */
461
462 SELECT
463 TO_CHAR(NEXT_DAY(l_default_date, NVL(TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1,l_date_format)
464 INTO x_def_period_name
465 FROM sys.dual;
466 /*
467 SELECT
468 TO_CHAR( NEXT_DAY(l_default_date, NVL( TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1, 'DD-MON-YYYY')
469 INTO x_def_period_name
470 FROM sys.dual;
471 */
472 /** Bug 2387429 and 2091182 Code Fix Ends **/
473 x_def_period_name_desc := NULL;
474 ELSIF ( l_period_type = 'YR') THEN
475 -- Populating default period year taking from sysdate
476
477 x_def_period_yr := TO_CHAR(SYSDATE,'YYYY');
478 x_def_period_name := NULL;
479 x_def_period_name_desc := NULL;
480 ELSIF ( l_period_type = 'QR') THEN
481 SELECT glper.quarter_num
482 INTO l_quarter_num
483 FROM pa_implementations imp,
484 gl_sets_of_books gl,
485 gl_periods glper,
486 gl_period_statuses glst
487 WHERE imp.set_of_books_id = gl.set_of_books_id
488 AND gl.period_set_name = glper.period_set_name
489 AND gl.accounted_period_type = glper.period_type
490 AND glper.period_name = glst.period_name
491 AND glper.period_type = glst.period_type
492 AND glst.set_of_books_id=imp.set_of_books_id
493 AND glst.application_id = PA_Period_Process_PKG.Application_ID
494 /* Bug 2288460 - Start */
495 /* Added the following clause */
496 AND glper.adjustment_period_flag = 'N'
497 /* Bug 2288460 - End */
498 AND l_default_date BETWEEN glper.start_date AND glper.end_date
499 AND ROWNUM=1
500 ORDER BY glper.period_year;
501 -- Populating default period name and default period year
502 l_prd_name := TO_CHAR(l_quarter_num);
503 x_def_period_name := l_prd_name;
504 x_def_period_name_desc := x_def_period_typ_desc||' '||l_prd_name;
505 END IF;
506 EXCEPTION
507 WHEN OTHERS THEN
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 x_msg_count := 1;
510 x_msg_data := SQLERRM;
511 -- dbms_output.put_line(SQLERRM);
512 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REPORT_UTIL',
513 p_procedure_name => 'get_default_val');
514 END get_default_val;
515
516
517 ------------------------------------------------------------------------------------------------------------------
518 -- This procedure will validate the passed values for screen U1
519 -- Input parameters
520 -- Parameters Type Required Description
521 -- p_org_mgr_id NUMBER YES It is having the manager org id
522 -- p_period_typ VARCHAR2 YES It store the period type
523 -- p_select_yr VARCHAR2 YES It store the period year
524 -- p_period_name VARCHAR2 YES It store the period name
525 -- Out parameters
526 --
527 --------------------------------------------------------------------------------------------------------------------
528
529 PROCEDURE validate_u1 (p_org_name IN VARCHAR2,
530 p_period_type_desc IN VARCHAR2,
531 p_select_yr IN NUMBER,
532 p_period_name IN VARCHAR2,
533 p_calling_mode IN VARCHAR2,
534 p_showprctgby IN VARCHAR2,
535 x_org_id OUT NOCOPY NUMBER,
536 x_period_type OUT NOCOPY VARCHAR2,
537 x_period_name OUT NOCOPY VARCHAR2,
538 x_return_status OUT NOCOPY VARCHAR2,
539 x_msg_count OUT NOCOPY NUMBER,
540 x_msg_data OUT NOCOPY VARCHAR2)
541
542 IS
543 l_invalid_value EXCEPTION;
544 l_exist VARCHAR2(1) := 'N';
545 l_period_type pa_rep_period_types_v.period_type%TYPE;
546 l_period_year pa_rep_periods_v.period_year%TYPE;
547 l_msg_index_out INTEGER:=1;
548 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
549 BEGIN
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 --Clear the global PL/SQL message table
553 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
554 FND_MSG_PUB.initialize;
555 END IF;
556
557 PA_REP_UTIL_GLOB.update_util_cache; -- Bug 2447797 added this call
558 -- Validate org name
559 BEGIN
560 x_org_id := NULL;
561 IF (p_calling_mode = 'ORG') THEN
562 SELECT distinct parent_organization_id
563 INTO x_org_id
564 FROM pa_rep_org_util_v
565 WHERE parent_org_name = p_org_name;
566 END IF;
567
568 EXCEPTION
569 WHEN NO_DATA_FOUND THEN
570 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_ORG_NAME');
571 x_return_status := FND_API.G_RET_STS_ERROR;
572 x_msg_data := 'PA_UTIL_INVALID_ORG_NAME';
573 x_msg_count := 1;
574 IF x_msg_count = 1 THEN
575 PA_INTERFACE_UTILS_PUB.get_messages
576 (p_encoded => FND_API.G_TRUE,
577 p_msg_index => 1,
578 p_data => x_msg_data,
579 p_msg_index_out => l_msg_index_out );
580 END IF;
581 RAISE l_invalid_value;
582 END;
583
584
585 -- Validating period type
586 BEGIN
587 SELECT period_type
588 INTO l_period_type
589 FROM pa_rep_period_types_v
590 WHERE period_type_desc = p_period_type_desc;
591
592 x_period_type := l_period_type;
593
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_TYPE');
597 x_return_status := FND_API.G_RET_STS_ERROR;
598 x_msg_data := 'PA_UTIL_INVALID_PRD_TYPE';
599 x_msg_count := 1;
600 IF x_msg_count = 1 THEN
601 PA_INTERFACE_UTILS_PUB.get_messages
602 (p_encoded => FND_API.G_TRUE,
603 p_msg_index => 1,
604 p_data => x_msg_data,
605 p_msg_index_out => l_msg_index_out );
606 END IF;
607 RAISE l_invalid_value;
608 END;
609
610 IF (l_period_type = 'YR') THEN
611 -- Validating select year
612 BEGIN
613 SELECT period_year
614 INTO l_period_year
615 FROM pa_rep_period_years_v
616 WHERE period_type = l_period_type
617 AND period_year = p_select_yr
618 AND ROWNUM = 1;
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_YEAR');
622 x_return_status := FND_API.G_RET_STS_ERROR;
623 x_msg_data := 'PA_UTIL_INVALID_PRD_YEAR';
624 x_msg_count := 1;
625 IF x_msg_count = 1 THEN
626 PA_INTERFACE_UTILS_PUB.get_messages
627 (p_encoded => FND_API.G_TRUE,
628 p_msg_index => 1,
629 p_data => x_msg_data,
630 p_msg_index_out => l_msg_index_out );
631 END IF;
632 RAISE l_invalid_value;
633 END;
634 ELSE
635 -- Validating select year
636 BEGIN
637 SELECT period_year
638 INTO l_period_year
639 FROM pa_rep_periods_v
640 WHERE period_type = l_period_type
641 AND period_year = p_select_yr
642 AND ROWNUM = 1;
643 EXCEPTION
644 WHEN NO_DATA_FOUND THEN
645 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_YEAR');
646 x_return_status := FND_API.G_RET_STS_ERROR;
647 x_msg_data := 'PA_UTIL_INVALID_PRD_YEAR';
648 x_msg_count := 1;
649 IF x_msg_count = 1 THEN
650 PA_INTERFACE_UTILS_PUB.get_messages
651 (p_encoded => FND_API.G_TRUE,
652 p_msg_index => 1,
653 p_data => x_msg_data,
654 p_msg_index_out => l_msg_index_out );
655 END IF;
656 RAISE l_invalid_value;
657 END;
658 END IF;
659
660
661 -- Validating period name
662 BEGIN
663 IF (l_period_type = 'PA' OR l_period_type = 'GL' OR l_period_type = 'GE') THEN
664 SELECT period_name
665 INTO x_period_name
666 FROM pa_rep_periods_v
667 WHERE period_type = l_period_type
668 AND ( period_name = p_period_name
669 OR period_name||' '||period_status = p_period_name)
670 AND period_year = p_select_yr;
671 ELSIF (l_period_type = 'QR') THEN
672 SELECT TO_CHAR(mon_or_qtr)
673 INTO x_period_name
674 FROM pa_rep_periods_v
675 WHERE period_type = l_period_type
676 AND ( period_name = p_period_name
677 OR period_name||' '||period_status = p_period_name)
678 AND period_year = p_select_yr;
679 END IF;
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_NAME');
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 x_msg_data := 'PA_UTIL_INVALID_PRD_NAME';
685 x_msg_count := 1;
686 IF x_msg_count = 1 THEN
687 PA_INTERFACE_UTILS_PUB.get_messages
688 (p_encoded => FND_API.G_TRUE,
689 p_msg_index => 1,
690 p_data => x_msg_data,
691 p_msg_index_out => l_msg_index_out );
692 END IF;
693 RAISE l_invalid_value;
694
695 END;
696
697 --Calling set u1 show percentage API
698 PA_REP_UTIL_GLOB.setu1showprctgby(p_showprctgby);
699
700 EXCEPTION
701 WHEN l_invalid_value THEN
702 NULL;
703 WHEN OTHERS THEN
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 x_msg_count := 1;
706 x_msg_data := SQLERRM;
707 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REPORT_UTIL',
708 p_procedure_name => 'validate_u1');
709 END validate_u1;
710
711
712 ------------------------------------------------------------------------------------------------------------------
713 -- This procedure will validate the passed values for screen U2
714 -- Input parameters
715 -- Parameters Type Required Description
716 -- p_mgr_name VARCHAR2 YES It is having the manager name
717 -- p_org_id NUMBER YES It is having the org id
718 -- p_assignment_sts VARCHAR2 YES It store the assignment status
719 -- p_period_yr VARCHAR2 YES It store the period year
720 -- p_period_typ VARCHAR2 YES It store the period type
721 -- p_util_category VARCHAR2 YES It store the util category
722 -- Out parameters
723 --
724 --------------------------------------------------------------------------------------------------------------------
725 PROCEDURE validate_u2 (p_mgr_name IN VARCHAR ,
726 p_org_name IN VARCHAR2,
727 p_org_id IN NUMBER,
728 p_mgr_id IN NUMBER,
729 p_assignment_sts IN VARCHAR2,
730 p_period_year IN NUMBER,
731 p_period_type_desc IN VARCHAR2,
732 p_period_name IN VARCHAR2,
733 p_util_category IN NUMBER,
734 p_Show_Percentage_By IN VARCHAR2,
735 p_Utilization_Method IN VARCHAR2,
736 p_calling_mode IN VARCHAR2,
737 x_return_status OUT NOCOPY VARCHAR2,
738 x_msg_count OUT NOCOPY NUMBER,
739 x_msg_data OUT NOCOPY VARCHAR2)
740 IS
741 l_invalid_value EXCEPTION;
742 l_exist VARCHAR2(1) := 'N';
743 l_period_type pa_rep_period_types_v.period_type_desc%type;
744 l_period_name pa_rep_periods_v.period_name%TYPE;
745 l_org_id pa_rep_util_res_orgs_v.organization_id%TYPE;
746 l_mgr_id pa_rep_util_mgr_org_v.manager_id%TYPE;
747 l_orgz_id pa_rep_util_res_orgs_v.organization_id%TYPE;
748 l_mgnr_id pa_rep_util_res_orgs_v.organization_id%TYPE;
749 l_prd_quarter pa_rep_periods_v.mon_or_qtr%type;
750 l_glb_wek_dt pa_rep_periods_v.ge_week_dt%type;
751 l_period_year pa_rep_periods_v.period_year%TYPE;
752 l_msg_index_out INTEGER:=1;
753 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
754 l_calling_mode VARCHAR2(15);
755
756 BEGIN
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 --Clear the global PL/SQL message table
760 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
761 FND_MSG_PUB.initialize;
762 END IF;
763
764 PA_REP_UTIL_GLOB.update_util_cache; -- Bug 2447797 added this call
765
766 -- Check mgr name or org name is valid or not
767 /* BEGIN
768 IF (p_calling_mode = 'RESMGR') THEN
769 SELECT organization_id
770 INTO l_org_id
771 FROM pa_rep_util_res_orgs_v
772 WHERE organization_name = p_org_name;
773 l_orgz_id := l_org_id;
774 l_mgnr_id := p_mgr_id;
775 ELSIF (p_calling_mode = 'ORGMGR') THEN
776 SELECT manager_id
777 INTO l_mgr_id
778 FROM pa_rep_util_mgr_org_v
779 WHERE manager_name = p_mgr_name;
780 l_orgz_id := p_org_id;
781 l_mgnr_id := l_mgr_id;
782 END IF;
783 EXCEPTION
784 WHEN NO_DATA_FOUND THEN
785 l_text := 'ORGNIZATION';
786 RAISE l_invalid_value;
787 END; */
788
789 l_orgz_id := p_org_id;
790 l_mgnr_id := p_mgr_id;
791 -- Validating period type
792 BEGIN
793 SELECT PERIOD_TYPE
794 INTO l_period_type
795 FROM pa_rep_period_types_v
796 WHERE period_type_desc = p_period_type_desc;
797
798 EXCEPTION
799 WHEN NO_DATA_FOUND THEN
800 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_TYPE');
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 x_msg_data := 'PA_UTIL_INVALID_PRD_TYPE';
803 x_msg_count := 1;
804 IF x_msg_count = 1 THEN
805 PA_INTERFACE_UTILS_PUB.get_messages
806 (p_encoded => FND_API.G_TRUE,
807 p_msg_index => 1,
808 p_data => x_msg_data,
809 p_msg_index_out => l_msg_index_out );
810 END IF;
811 RAISE l_invalid_value;
812 END;
813
814 -- dbms_output.put_line('prd yer '||p_period_year);
815 -- dbms_output.put_line('prd typ '||l_period_type);
816 -- dbms_output.put_line('prd nam '||p_period_name);
817
818 IF ( l_period_type = 'YR' ) THEN
819 -- Validating period year
820 BEGIN
821 SELECT period_year
822 INTO l_period_year
823 FROM pa_rep_period_years_v
824 WHERE period_type = l_period_type
825 AND period_year = p_period_year
826 AND ROWNUM =1;
827
828 EXCEPTION
829 WHEN NO_DATA_FOUND THEN
830 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_YEAR');
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 x_msg_data := 'PA_UTIL_INVALID_PRD_YEAR';
833 x_msg_count := 1;
834 IF x_msg_count = 1 THEN
835 PA_INTERFACE_UTILS_PUB.get_messages
836 (p_encoded => FND_API.G_TRUE,
837 p_msg_index => 1,
838 p_data => x_msg_data,
839 p_msg_index_out => l_msg_index_out );
840 END IF;
841 RAISE l_invalid_value;
842 END;
843 ELSE
844 -- Validating period year
845 BEGIN
846 SELECT period_year
847 INTO l_period_year
848 FROM pa_rep_periods_v
849 WHERE period_type = l_period_type
850 AND period_year = p_period_year
851 AND ROWNUM =1;
852
853 EXCEPTION
854 WHEN NO_DATA_FOUND THEN
855 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_YEAR');
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 x_msg_data := 'PA_UTIL_INVALID_PRD_YEAR';
858 x_msg_count := 1;
859 IF x_msg_count = 1 THEN
860 PA_INTERFACE_UTILS_PUB.get_messages
861 (p_encoded => FND_API.G_TRUE,
862 p_msg_index => 1,
863 p_data => x_msg_data,
864 p_msg_index_out => l_msg_index_out );
865 END IF;
866 RAISE l_invalid_value;
867 END;
868 END IF;
869
870 IF ( l_period_type <> 'YR') THEN
871 -- Validating period name
872 BEGIN
873 SELECT period_name
874 INTO l_period_name
875 FROM pa_rep_periods_v
876 WHERE period_type = l_period_type
877 AND ( period_name = p_period_name
878 OR period_name||' '||period_status = p_period_name)
879 AND period_year = p_period_year;
880
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 PA_UTILS.Add_Message( 'PA', 'PA_UTIL_INVALID_PRD_NAME');
884 x_return_status := FND_API.G_RET_STS_ERROR;
885 x_msg_data := 'PA_UTIL_INVALID_PRD_NAME';
886 x_msg_count := 1;
887 IF x_msg_count = 1 THEN
888 PA_INTERFACE_UTILS_PUB.get_messages
889 (p_encoded => FND_API.G_TRUE,
890 p_msg_index => 1,
891 p_data => x_msg_data,
892 p_msg_index_out => l_msg_index_out );
893 END IF;
894 RAISE l_invalid_value;
895 END;
896 END IF;
897
898 -- Calling Temporary table population API
899
900 IF (l_period_type = 'QR') THEN
901 SELECT MON_OR_QTR
902 INTO l_prd_quarter
903 FROM pa_rep_periods_v
904 WHERE period_type = l_period_type
905 AND ( period_name = p_period_name
906 OR period_name||' '||period_status = p_period_name)
907 AND period_year = p_period_year;
908 ELSE
909 l_prd_quarter := 0;
910 END IF;
911
912 IF (l_period_type = 'GE') THEN
913 SELECT ge_week_dt
914 INTO l_glb_wek_dt
915 FROM pa_rep_periods_v
916 WHERE period_type = l_period_type
917 AND ( period_name = p_period_name
918 OR period_name||' '||period_status = p_period_name)
919 AND period_year = p_period_year;
920 ELSE
921 l_glb_wek_dt := TO_DATE('10/09/1492','MM/DD/YYYY');
922 END IF;
923
924
925 l_calling_mode := p_calling_mode;
926
927 /* */PA_REP_UTIL_SCREEN.poplt_screen_tmp_table(
928 p_Organization_ID => l_orgz_id
929 , p_Manager_ID => l_mgnr_id
930 , p_Period_Type => l_period_type
931 , p_Period_Year => p_period_year
932 , p_Period_Quarter => l_prd_quarter
933 , p_Period_Name => l_period_name
934 , p_Global_Week_End_Date => l_glb_wek_dt
935 , p_Assignment_Status => NVL(p_assignment_sts,'ALL')
936 , p_Show_Percentage_By => p_Show_Percentage_By
937 , p_Utilization_Method => p_Utilization_Method
938 , p_Utilization_Category_Id => NVL(p_util_category,0)
939 , p_calling_mode => l_calling_mode);
940
941
942
943 EXCEPTION
944 WHEN l_invalid_value THEN
945 NULL;
946 -- dbms_output.put_line(' Invalid value '||l_text||' '||l_text1);
947 WHEN OTHERS THEN
948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 x_msg_count := 1;
950 x_msg_data := SUBSTR(SQLERRM,1,240);
951 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REPORT_UTIL',
952 p_procedure_name => 'validate_u2');
953 END validate_u2;
954
955
956 ------------------------------------------------------------------------------------------------------------------
957 -- This procedure will populate U3 screen
958 -- Input parameters
959 -- Parameters Type Required Description
960 -- Out parameters
961 --
962 ----------------------------------------------------------------------------------------------------------
963 PROCEDURE get_default_period_val(
964 x_def_period OUT NOCOPY VARCHAR2,
965 x_def_period_typ IN OUT NOCOPY VARCHAR2,
966 x_def_period_yr IN OUT NOCOPY VARCHAR2,
967 x_def_period_name IN OUT NOCOPY VARCHAR2,
968 x_def_period_sts_code OUT NOCOPY VARCHAR2,
969 x_def_period_sts OUT NOCOPY VARCHAR2,
970 x_def_mon_or_qtr OUT NOCOPY VARCHAR2,
971 x_def_period_num OUT NOCOPY VARCHAR2,
972 x_return_status OUT NOCOPY VARCHAR2,
973 x_msg_count OUT NOCOPY NUMBER,
974 x_msg_data OUT NOCOPY VARCHAR2) IS
975
976 /* CURSOR C1 IS SELECT glper.period_name,glper.period_year,glst.closing_status,glper.quarter_num,glst.effective_period_num
977 FROM pa_implementations imp,
978 gl_sets_of_books gl,
979 gl_periods glper,
980 gl_period_statuses glst
981 WHERE imp.set_of_books_id = gl.set_of_books_id
982 AND gl.period_set_name = glper.period_set_name
983 AND imp.pa_period_type = glper.period_type
984 AND glper.period_name = glst.period_name
985 AND glper.period_type = glst.period_type
986 AND glst.set_of_books_id=imp.set_of_books_id
987 AND glst.closing_status ='O'
988 AND glst.application_id = 275
989 ORDER BY glper.period_year;*/
990
991 CURSOR C1 IS SELECT
992 paperiod.period_name,
993 paperiod.period_year,
994 paperiod.status,
995 paperiod.quarter_num,
996 glpersts.effective_period_num,
997 paperiod.status_meaning
998 FROM pa_periods_v paperiod,
999 pa_implementations imp,
1000 gl_period_statuses glpersts
1001 WHERE glpersts.period_type = imp.pa_period_type
1002 AND imp.set_of_books_id = paperiod.set_of_books_id
1003 AND glpersts.set_of_books_id = imp.set_of_books_id
1004 AND glpersts.period_name = paperiod.period_name
1005 AND glpersts.period_year = paperiod.period_year
1006 AND glpersts.application_id = 275
1007 AND paperiod.status = 'O'
1008 ORDER BY paperiod.period_year;
1009
1010
1011 CURSOR C2 IS SELECT glper.period_name
1012 ,glper.period_year
1013 ,glst.closing_status
1014 ,glper.quarter_num
1015 ,glst.effective_period_num
1016 ,gllkups.meaning
1017 FROM pa_implementations imp,
1018 gl_sets_of_books gl,
1019 gl_periods glper,
1020 gl_period_statuses glst,
1021 gl_lookups gllkups
1022 WHERE imp.set_of_books_id = gl.set_of_books_id
1023 AND gl.period_set_name = glper.period_set_name
1024 AND gl.accounted_period_type = glper.period_type
1025 AND glper.period_name = glst.period_name
1026 AND glper.period_type = glst.period_type
1027 AND glst.set_of_books_id=imp.set_of_books_id
1028 AND glst.closing_status ='O'
1029 AND glst.application_id = PA_Period_Process_PKG.Application_ID
1030 AND gllkups.lookup_code = glst.closing_status
1031 AND gllkups.lookup_type = 'CLOSING_STATUS'
1032 ORDER BY glper.period_year;
1033
1034 /* CURSOR C4(p_sts in varchar2) IS
1035 SELECT meaning, lookup_code
1036 FROM gl_lookups
1037 WHERE lookup_type = 'CLOSING_STATUS'
1038 ANd lookup_code = p_sts;*/
1039
1040 l_default_date DATE := SYSDATE;
1041 l_forecast_thru_date DATE;
1042
1043 l_prd_name gl_periods.period_name%TYPE;
1044 l_prd_yr gl_periods.period_year%TYPE;
1045 l_sts gl_period_statuses.closing_status%TYPE;
1046 l_mon_qtr gl_periods.quarter_num%TYPE;
1047
1048 l_mon NUMBER;
1049
1050 l_period_type gl_periods.period_type%TYPE;
1051 l_week_ending_date DATE;
1052
1053 l_period_num gl_period_statuses.effective_period_num%TYPE;
1054 l_sts_meaning gl_lookups.meaning%TYPE;
1055 l_use_default_logic VARCHAR2(1) := 'Y';
1056 l_date_format VARCHAR2(100); -- Added for Bug 2091182 and 2387429
1057 /* NPE Changes Begin*/
1058 l_def_show_percentages_by varchar2(240);
1059 l_global_week_start_day varchar2(240);
1060 l_user_profile_option_name1 varchar2(1000);
1061 l_user_profile_option_name2 varchar2(1000);
1062 l_user_profile_option_name3 varchar2(1000);
1063 l_msg_count NUMBER := 0;
1064 l_data VARCHAR2(2000);
1065 l_msg_data VARCHAR2(2000);
1066 l_msg_index_out NUMBER;
1067 l_return_status VARCHAR2(2000);
1068 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
1069 l_err_msg VARCHAR2(3000);
1070 /* NPE Changes End */
1071
1072 dummy char ; -- Added for bug2440313
1073 BEGIN
1074
1075 x_return_status := FND_API.G_RET_STS_SUCCESS;
1076
1077 /* NPE Changes Begins - The following initialize is added,
1078 otherwise it will show same message again and give misleading information */
1079
1080 --Clear the global PL/SQL message table
1081 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
1082 FND_MSG_PUB.initialize;
1083 END IF;
1084
1085 /* NPE Changes Ends */
1086
1087 /* Bug2440313 Begin */
1088 BEGIN
1089 select 'x' into dummy
1090 from pa_implementations;
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 PA_UTILS.Add_Message( p_app_short_name =>'PA',
1095 p_msg_name => 'PA_INCORRECT_MO_OPERATING_UNIT');
1096
1097 l_msg_count := FND_MSG_PUB.count_msg;
1098 IF l_msg_count > 0 then
1099 IF l_msg_count = 1 then
1100 PA_INTERFACE_UTILS_PUB.get_messages
1101 (p_encoded => FND_API.G_TRUE,
1102 p_msg_index => 1,
1103 p_msg_count => l_msg_count,
1104 p_msg_data => l_msg_data,
1105 p_data => l_data,
1106 p_msg_index_out => l_msg_index_out);
1107
1108 x_msg_data := l_data;
1109 x_msg_count := l_msg_count;
1110 ELSE
1111 x_msg_count := l_msg_count;
1112 END IF;
1113 pa_debug.reset_err_stack;
1114 return;
1115 END IF;
1116 END;
1117 /* Bug2440313 End */
1118
1119 -- Populating default period type using profile options
1120 l_period_type := FND_PROFILE.VALUE_SPECIFIC('PA_RES_UTIL_DEF_PERIOD_TYPE');
1121
1122 /* NPE Changes Begins */
1123
1124 l_def_show_percentages_by := FND_PROFILE.VALUE('PA_RES_UTIL_DEF_CALC_METHOD');
1125 l_global_week_start_day := FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY');
1126
1127 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name1
1128 FROM fnd_profile_options_tl
1129 WHERE profile_option_name='PA_RES_UTIL_DEF_CALC_METHOD'
1130 AND language=userenv('LANG');
1131
1132 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name2
1133 FROM fnd_profile_options_tl
1134 WHERE profile_option_name='PA_RES_UTIL_DEF_PERIOD_TYPE'
1135 AND language=userenv('LANG');
1136
1137 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name3
1138 FROM fnd_profile_options_tl
1139 WHERE profile_option_name='PA_GLOBAL_WEEK_START_DAY'
1140 AND language=userenv('LANG');
1141
1142
1143 IF l_period_type is NULL AND l_def_show_percentages_by is NULL THEN
1144 x_return_status := FND_API.G_RET_STS_ERROR;
1145 l_err_msg:=l_user_profile_option_name1;
1146 l_err_msg:=l_err_msg||', '||l_user_profile_option_name2;
1147 ELSIF l_period_type is NULL AND l_def_show_percentages_by is NOT NULL THEN
1148 x_return_status := FND_API.G_RET_STS_ERROR;
1149 l_err_msg:=l_user_profile_option_name2;
1150 ELSIF l_period_type is NOT NULL AND l_def_show_percentages_by is NULL THEN
1151 x_return_status := FND_API.G_RET_STS_ERROR;
1152 l_err_msg:=l_user_profile_option_name1;
1153 IF l_period_type = 'GE' and l_global_week_start_day IS NULL THEN
1154 l_err_msg:=l_err_msg||', '||l_user_profile_option_name3;
1155 END IF;
1156 END IF;
1157
1158 IF x_return_status = FND_API.G_RET_STS_ERROR and l_err_msg is not null THEN
1159 PA_UTILS.Add_Message( p_app_short_name =>'PA',
1160 p_msg_name => 'PA_UNDEFINED_PROFILES',
1161 p_token1 => 'PROFILES',
1162 p_value1 => l_err_msg);
1163
1164 l_msg_count := FND_MSG_PUB.count_msg;
1165 IF l_msg_count > 0 then
1166 IF l_msg_count = 1 then
1167 PA_INTERFACE_UTILS_PUB.get_messages
1168 (p_encoded => FND_API.G_TRUE,
1169 p_msg_index => 1,
1170 p_msg_count => l_msg_count,
1171 p_msg_data => l_msg_data,
1172 p_data => l_data,
1173 p_msg_index_out => l_msg_index_out);
1174
1175 x_msg_data := l_data;
1176 x_msg_count := l_msg_count;
1177 ELSE
1178 x_msg_count := l_msg_count;
1179 END IF;
1180 END IF;
1181 END IF;
1182
1183 /*NPE changes ends */
1184
1185 -- Shifted the following code of selecting forecast_thru_date to
1186 -- here, Also added message if NO_data_found occurs
1187
1188 -- Fetching the default forecast thru date information from utilization options
1189 BEGIN
1190
1191 select forecast_thru_date
1192 into l_forecast_thru_date
1193 from pa_utilization_options ut;
1194
1195 /*Bug2440313 -- Commented this, the check for pa_implementaions is being done
1196 above in the code
1197 ,pa_implementations imp --NPE changes
1198 where nvl(ut.org_id, -99) = nvl(imp.org_id,-99); --NPE changes
1199 */
1200
1201 IF (l_forecast_thru_date > sysdate
1202 OR l_forecast_thru_date is NULL) then
1203 l_default_date := sysdate;
1204 ELSE
1205 l_default_date := l_forecast_thru_date;
1206 END IF;
1207 EXCEPTION
1208 WHEN NO_DATA_FOUND THEN
1209 /* NPE Changes Begins */
1210 /* l_default_date := sysdate; -- Commented this code */
1211
1212 /* Bug2440313 Begin -- Undone the check for MO: Operating Unit here. The Operating Unit check
1213 is now done above in this code. Here we do check for utilization options defined or not */
1214
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216 PA_UTILS.Add_Message( p_app_short_name =>'PA',
1217 p_msg_name => 'PA_UNDEFINED_UTIL_OPTIONS');
1218
1219 l_msg_count := FND_MSG_PUB.count_msg;
1220 IF l_msg_count > 0 then
1221 IF l_msg_count = 1 then
1222 PA_INTERFACE_UTILS_PUB.get_messages
1223 (p_encoded => FND_API.G_TRUE,
1224 p_msg_index => 1,
1225 p_msg_count => l_msg_count,
1226 p_msg_data => l_msg_data,
1227 p_data => l_data,
1228 p_msg_index_out => l_msg_index_out);
1229
1230 x_msg_data := l_data;
1231 x_msg_count := l_msg_count;
1232 ELSE
1233 x_msg_count := l_msg_count;
1234 END IF;
1235 END IF;
1236 /* Bug2440313 End */
1237 END;
1238
1239 -- Collectively check for x_return_status, if error then return
1240 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1241 pa_debug.reset_err_stack;
1242 return;
1243 END IF;
1244
1245 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
1246
1247 /*NPE changes ends */
1248
1249 l_default_date := trunc(l_default_date);
1250
1251 IF nvl(x_def_period_typ,'NA') <> l_period_type THEN
1252 x_def_period_typ := l_period_type;
1253 l_use_default_logic := 'Y';
1254 ELSE
1255 l_use_default_logic := 'N';
1256 END IF;
1257
1258 IF l_use_default_logic = 'Y' THEN
1259 -- Populating default period name
1260 IF ( l_period_type = 'PA') THEN
1261 BEGIN -- NPE Changes
1262 SELECT
1263 paperiod.period_name,
1264 paperiod.period_year,
1265 paperiod.status,
1266 paperiod.quarter_num,
1267 glpersts.effective_period_num,
1268 paperiod.status_meaning
1269 INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
1270 FROM pa_periods_v paperiod,
1271 pa_implementations imp,
1272 gl_period_statuses glpersts
1273 WHERE glpersts.period_type = imp.pa_period_type
1274 AND imp.set_of_books_id = paperiod.set_of_books_id
1275 AND glpersts.set_of_books_id = imp.set_of_books_id
1276 AND glpersts.period_name = paperiod.period_name
1277 AND glpersts.period_year = paperiod.period_year
1278 AND glpersts.application_id = 275
1279 -- AND paperiod.status = 'O'
1280 AND l_default_date BETWEEN PA_START_DATE and PA_END_DATE
1281 ORDER BY paperiod.period_year;
1282 --DBMS_OUTPUT.PUT_LINE('VALUES '||l_prd_name||' '||l_prd_yr||' '||l_sts);
1283 -- Populating default period name and default period year
1284
1285 x_def_period_name := l_prd_name;
1286 x_def_period_yr := l_prd_yr;
1287 x_def_mon_or_qtr := l_mon_qtr;
1288 x_def_period_num := l_period_num;
1289 x_def_period_sts := l_sts_meaning;
1290 x_def_period_sts_code := l_sts;
1291 /*NPE changes Begins */
1292 EXCEPTION
1293 WHEN NO_DATA_FOUND THEN
1294 x_return_status := FND_API.G_RET_STS_ERROR;
1295 PA_UTILS.Add_Message( p_app_short_name =>'PA',
1296 p_msg_name => 'PA_PRD_NOT_DEFINED_FOR_DATE',
1297 p_token1 => 'PA_DATE',
1298 p_value1 => to_char(l_default_date,l_date_format));
1299 l_msg_count := FND_MSG_PUB.count_msg;
1300 IF l_msg_count > 0 then
1301 IF l_msg_count = 1 then
1302 PA_INTERFACE_UTILS_PUB.get_messages
1303 (p_encoded => FND_API.G_TRUE,
1304 p_msg_index => 1,
1305 p_msg_count => l_msg_count,
1306 p_msg_data => l_msg_data,
1307 p_data => l_data,
1308 p_msg_index_out => l_msg_index_out);
1309
1310 x_msg_data := l_data;
1311 x_msg_count := l_msg_count;
1312 ELSE
1313 x_msg_count := l_msg_count;
1314 END IF;
1315 pa_debug.reset_err_stack;
1316 return;
1317 END IF;
1318 END;
1319 /*NPE changes Ends */
1320 ELSIF( l_period_type = 'GL') THEN
1321 BEGIN -- NPE Changes
1322 SELECT glper.period_name
1323 ,glper.period_year
1324 ,glst.closing_status
1325 ,glper.quarter_num
1326 ,glst.effective_period_num
1327 ,gllkups.meaning
1328 INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
1329 FROM pa_implementations imp,
1330 gl_sets_of_books gl,
1331 gl_periods glper,
1332 gl_period_statuses glst,
1333 gl_lookups gllkups
1334 WHERE imp.set_of_books_id = gl.set_of_books_id
1335 AND gl.period_set_name = glper.period_set_name
1336 AND gl.accounted_period_type = glper.period_type
1337 AND glper.period_name = glst.period_name
1338 AND glper.period_type = glst.period_type
1339 AND glst.set_of_books_id=imp.set_of_books_id
1340 AND glst.application_id = PA_Period_Process_PKG.Application_ID
1341 AND gllkups.lookup_code = glst.closing_status
1342 AND gllkups.lookup_type = 'CLOSING_STATUS'
1343 AND l_default_date BETWEEN glper.start_date AND glper.end_date
1344 /* Bug 2288460 - Start */
1345 /* Added the following clause */
1346 AND glper.adjustment_period_flag = 'N'
1347 /* Bug 2288460 - End */
1348 ORDER BY glper.period_year;
1349 --DBMS_OUTPUT.PUT_LINE('VALUES '||l_prd_name||' '||l_prd_yr||' '||l_sts);
1350
1351 -- Populating default period name and default period year
1352 x_def_period_name := l_prd_name;
1353 x_def_period_yr := l_prd_yr;
1354 x_def_mon_or_qtr := l_mon_qtr;
1355 x_def_period_num := l_period_num;
1356 x_def_period_sts := l_sts_meaning;
1357 x_def_period_sts_code := l_sts;
1358 /*NPE changes starts */
1359 EXCEPTION
1360 WHEN NO_DATA_FOUND THEN
1361 x_return_status := FND_API.G_RET_STS_ERROR;
1362 PA_UTILS.Add_Message( p_app_short_name =>'PA',
1363 p_msg_name => 'PA_GL_PRD_NOT_DEFINED_FOR_DATE',
1364 p_token1 => 'GL_DATE',
1365 p_value1 => to_char(l_default_date,l_date_format));
1366 l_msg_count := FND_MSG_PUB.count_msg;
1367 IF l_msg_count > 0 then
1368 IF l_msg_count = 1 then
1369 PA_INTERFACE_UTILS_PUB.get_messages
1370 (p_encoded => FND_API.G_TRUE,
1371 p_msg_index => 1,
1372 p_msg_count => l_msg_count,
1373 p_msg_data => l_msg_data,
1374 p_data => l_data,
1375 p_msg_index_out => l_msg_index_out);
1376
1377 x_msg_data := l_data;
1378 x_msg_count := l_msg_count;
1379 ELSE
1380 x_msg_count := l_msg_count;
1381 END IF;
1382 pa_debug.reset_err_stack;
1383 return;
1384 END IF;
1385 END;
1386 /*NPE changes Ends */
1387 ELSIF ( l_period_type = 'GE') THEN
1388 /* NPE Changes Begins */
1389 IF l_global_week_start_day IS NULL THEN
1390 x_return_status := FND_API.G_RET_STS_ERROR;
1391 PA_UTILS.Add_Message( p_app_short_name =>'PA',
1392 p_msg_name => 'PA_UNDEFINED_PROFILES',
1393 p_token1 => 'PROFILES',
1394 p_value1 => l_user_profile_option_name3);
1395
1396 l_msg_count := FND_MSG_PUB.count_msg;
1397 IF l_msg_count > 0 then
1398 IF l_msg_count = 1 then
1399 PA_INTERFACE_UTILS_PUB.get_messages
1400 (p_encoded => FND_API.G_TRUE,
1401 p_msg_index => 1,
1402 p_msg_count => l_msg_count,
1403 p_msg_data => l_msg_data,
1404 p_data => l_data,
1405 p_msg_index_out => l_msg_index_out);
1406
1407 x_msg_data := l_data;
1408 x_msg_count := l_msg_count;
1409 ELSE
1410 x_msg_count := l_msg_count;
1411 END IF;
1412 pa_debug.reset_err_stack;
1413 return;
1414 END IF;
1415 END IF;
1416 /* NPE Changes Ends */
1417
1418 -- Populating period name
1419 /** Bug 2387429 and 2091182 Code Fix Starts **/
1420 /*
1421 SELECT
1422 TO_CHAR(NEXT_DAY(l_default_date, NVL(TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1,'DD-MON-YYYY')
1423 INTO x_def_period_name
1424 FROM sys.dual;
1425 */
1426
1427 /* NPE Changes -- Shifted this code to the above in this code l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);*/
1428
1429 SELECT
1430 TO_CHAR(NEXT_DAY(l_default_date, NVL(TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1,l_date_format)
1431 INTO x_def_period_name
1432 FROM sys.dual;
1433
1434 /** Bug 2387429 and 2091182 Code Fix Ends **/
1435
1436 -- Populating default period year taking from sysdate
1437 x_def_period_yr := TO_CHAR(l_default_date,'YYYY');
1438 -- Populating Mon or Qtr
1439 l_mon := TO_CHAR(l_default_date,'MM');
1440
1441 x_def_mon_or_qtr := l_mon;
1442 x_def_period_num := 0;
1443 x_def_period_sts := ' ';
1444 x_def_period_sts_code := ' ';
1445 END IF;
1446 ELSE --l_use_default_logic <> 'Y'
1447 /* Note that no need to do the NPE changes here as the value of the period are coming
1448 from other windows and would have been already validated */
1449 IF ( l_period_type = 'PA') THEN
1450 SELECT
1451 paperiod.period_name,
1452 paperiod.period_year,
1453 paperiod.status,
1454 paperiod.quarter_num,
1455 glpersts.effective_period_num,
1456 paperiod.status_meaning
1457 INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
1458 FROM pa_periods_v paperiod,
1459 pa_implementations imp,
1460 gl_period_statuses glpersts
1461 WHERE glpersts.period_type = imp.pa_period_type
1462 AND imp.set_of_books_id = paperiod.set_of_books_id
1463 AND glpersts.set_of_books_id = imp.set_of_books_id
1464 AND glpersts.period_name = paperiod.period_name
1465 AND glpersts.period_year = paperiod.period_year
1466 AND glpersts.period_name = x_def_period_name
1467 AND glpersts.period_year = x_def_period_yr
1468 AND glpersts.application_id = 275
1469 -- AND paperiod.status = 'O'
1470 -- AND l_default_date BETWEEN PA_START_DATE and PA_END_DATE
1471 ORDER BY paperiod.period_year;
1472 --DBMS_OUTPUT.PUT_LINE('VALUES '||l_prd_name||' '||l_prd_yr||' '||l_sts);
1473 -- Populating default period name and default period year
1474 x_def_period_name := l_prd_name;
1475 x_def_period_yr := l_prd_yr;
1476 x_def_mon_or_qtr := l_mon_qtr;
1477 x_def_period_num := l_period_num;
1478 x_def_period_sts := l_sts_meaning;
1479 x_def_period_sts_code := l_sts;
1480 ELSIF( l_period_type = 'GL') THEN
1481 SELECT glper.period_name
1482 ,glper.period_year
1483 ,glst.closing_status
1484 ,glper.quarter_num
1485 ,glst.effective_period_num
1486 ,gllkups.meaning
1487 INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
1488 FROM pa_implementations imp,
1489 gl_sets_of_books gl,
1490 gl_periods glper,
1491 gl_period_statuses glst,
1492 gl_lookups gllkups
1493 WHERE imp.set_of_books_id = gl.set_of_books_id
1494 AND gl.period_set_name = glper.period_set_name
1495 AND gl.accounted_period_type = glper.period_type
1496 AND glper.period_name = glst.period_name
1497 AND glper.period_name = x_def_period_name
1498 AND glper.period_type = glst.period_type
1499 AND glst.set_of_books_id=imp.set_of_books_id
1500 AND glst.application_id = PA_Period_Process_PKG.Application_ID
1501 AND gllkups.lookup_code = glst.closing_status
1502 AND gllkups.lookup_type = 'CLOSING_STATUS'
1503 /* Bug 2288460 - Start */
1504 /* Added the following clause */
1505 AND glper.adjustment_period_flag = 'N'
1506 /* Bug 2288460 - End */
1507 -- AND l_default_date BETWEEN glper.start_date AND glper.end_date
1508 ORDER BY glper.period_year;
1509 --DBMS_OUTPUT.PUT_LINE('VALUES '||l_prd_name||' '||l_prd_yr||' '||l_sts);
1510
1511 -- Populating default period name and default period year
1512 x_def_period_name := l_prd_name;
1513 x_def_period_yr := l_prd_yr;
1514 x_def_mon_or_qtr := l_mon_qtr;
1515 x_def_period_num := l_period_num;
1516 x_def_period_sts := l_sts_meaning;
1517 x_def_period_sts_code := l_sts;
1518
1519 ELSIF ( l_period_type = 'GE') THEN
1520
1521 -- Populating Mon or Qtr
1522 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
1523 l_mon := TO_CHAR(to_date(x_def_period_name, l_date_format),'MM');
1524
1525 x_def_mon_or_qtr := l_mon;
1526 x_def_period_num := 0;
1527 x_def_period_sts := ' ';
1528 x_def_period_sts_code := ' ';
1529 END IF;
1530 END IF;
1531 --Added the below cos for GE status = NULL
1532 --Select from pa_rep_periods_v gives ' ' but this was giving NULL
1533 x_def_period := x_def_period_typ||'#'||x_def_period_name||'#'||x_def_period_sts_code||'#'||x_def_period_sts||'#'||x_def_mon_or_qtr||'#'||x_def_period_yr||'#'||x_def_period_num;
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 x_msg_count := 1;
1538 x_msg_data := SQLERRM;
1539 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REGENRATE_ORGNIZATION_FORECAST',
1540 p_procedure_name => 'get_default_period_val');
1541 RAISE;
1542
1543 END get_default_period_val;
1544
1545 ------------------------------------------------------------------------------------------------------------------
1546 -- This procedure will get the GE flag
1547 -- Input parameters
1548 -- Parameters Type Required Description
1549 -- p_periodname VARCHAR2 YES Period name
1550 -- Out parameters
1551 -- x_flag VARCHAR2 YES It stores flag value
1552 --
1553 ----------------------------------------------------------------------------------------------------------
1554 PROCEDURE Get_GE_Flag(
1555 p_periodname IN VARCHAR2,
1556 x_flag OUT NOCOPY VARCHAR2) IS
1557
1558 l_gedate DATE ;
1559 l_date_format varchar2(100);
1560 l_ge_end_date DATE;
1561
1562 x_def_period VARCHAR2(100);
1563 x_def_period_typ VARCHAR2(2);
1564 x_def_period_yr VARCHAR2(10);
1565 x_def_period_name VARCHAR2(30);
1566 x_def_period_sts_code VARCHAR2(1);
1567 x_def_period_sts VARCHAR2(30);
1568 x_def_mon_or_qtr VARCHAR2(10);
1569 x_def_period_num VARCHAR2(20);
1570 x_return_status VARCHAR2(1);
1571 x_msg_count NUMBER;
1572 x_msg_data VARCHAR2(100);
1573 BEGIN
1574
1575 PA_REP_UTIL_GLOB.update_util_cache; -- Bug 2447797 added this call
1576
1577 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
1578 --dbms_output.put_line('Date Format ---- ' || l_date_format);
1579
1580 l_gedate := to_date(p_periodname, l_date_format);
1581
1582 pa_report_util.get_default_period_val(
1583 x_def_period,
1584 x_def_period_typ,
1585 x_def_period_yr ,
1586 x_def_period_name,
1587 x_def_period_sts_code,
1588 x_def_period_sts,
1589 x_def_mon_or_qtr,
1590 x_def_period_num,
1591 x_return_status,
1592 x_msg_count,
1593 x_msg_data);
1594
1595 l_ge_end_date := to_date(x_def_period_name, l_date_format);
1596 -- dbms_output.put_line('GE End Date : '|| to_char(l_ge_end_date));
1597
1598 IF l_gedate < l_ge_end_date THEN
1599 x_flag := 'A';
1600 ELSIF l_gedate > l_ge_end_date THEN
1601 x_flag := 'F';
1602 ELSIF l_gedate = l_ge_end_date THEN
1603 x_flag := 'B';
1604 END IF;
1605
1606 EXCEPTION
1607 WHEN OTHERS THEN
1608 RAISE;
1609 END Get_GE_Flag;
1610 END PA_REPORT_UTIL;