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