4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_ACC_PERIODS_PVT';
1 PACKAGE BODY CN_ACC_PERIODS_PVT AS
2 /*$Header: cnvsyprb.pls 120.7 2006/02/28 04:42:05 vensrini noship $*/
3
5
6 TYPE str_tbl_type IS TABLE OF gl_lookups.meaning%TYPE INDEX BY BINARY_INTEGER;
7
8 g_code_tbl str_tbl_type;
9 g_meaning_tbl str_tbl_type;
10
11 -- Changed cursor fix bug#2804029
12 CURSOR lookup_table IS
13 SELECT lookup_code, meaning
14 FROM CN_lookups
15 WHERE lookup_type = 'PERIOD_CLOSING_STATUS';
16
17 FUNCTION get_closing_status(p_closing_status_meaning gl_lookups.meaning%TYPE) RETURN gl_lookups.lookup_code%TYPE IS
18 BEGIN
19 -- FOR i IN 1..g_meaning_tbl.COUNT LOOP
20 -- IF (p_closing_status_meaning = g_meaning_tbl(i)) THEN
21 -- RETURN g_code_tbl(i);
22 -- END IF;
23 -- END LOOP;
24 --
25 -- RETURN NULL;
26 RETURN p_closing_status_meaning; -- vensrini
27 END get_closing_status;
28
29 -- Procedure to start concurrent request (in single-org context)
30 PROCEDURE open_period
31 (errbuf OUT NOCOPY VARCHAR2,
32 retcode OUT NOCOPY NUMBER,
33 p_period_name IN VARCHAR2,
34 p_freeze_flag IN VARCHAR2) IS
35
36 l_acc_period_tbl acc_period_tbl_type;
37 l_set_of_books_id cn_repositories.set_of_books_id%TYPE;
38 l_period_set_id cn_repositories.period_set_id%TYPE;
39 l_period_type_id cn_repositories.period_type_id%TYPE;
40
41 CURSOR repository_info IS
42 SELECT set_of_books_id,
43 period_set_id,
44 period_type_id
45 FROM cn_repositories
46 WHERE repository_id > 0
47 AND application_type = 'CN';
48
49 -- copy of cursor in get_acc_periods proc
50 -- Changed cursor fix bug#2804029
51 CURSOR periods IS
52 SELECT cn.period_name,
53 cn.period_year,
54 cn.start_date,
55 cn.end_date,
56 'O' closing_status_meaning,
57 cp.meaning processing_status,
58 p_freeze_flag,
59 cn.object_version_number
60 FROM cn_period_statuses cn,
61 cn_lookups cp
62 WHERE cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
63 AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
64 AND cn.period_set_id = l_period_set_id
65 AND cn.period_type_id = l_period_type_id
66 AND cn.period_name = p_period_name
67 UNION
68 SELECT gl.period_name,
69 gl.period_year,
70 gl.start_date,
71 gl.end_date,
72 'O' closing_status_meaning,
73 cp.meaning processing_status,
74 p_freeze_flag,
75 cn.object_version_number
76 FROM gl_period_statuses gl,
77 cn_period_statuses cn,
78 cn_lookups cp
79 WHERE gl.set_of_books_id = l_set_of_books_id
80 AND gl.application_id = 283
81 AND gl.adjustment_period_flag = 'N'
82 AND gl.period_name = cn.period_name(+)
83 AND cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
84 AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
85 AND cn.period_type_id(+) = l_period_type_id
86 AND cn.period_set_id(+) = l_period_set_id
87 AND gl.period_name = p_period_name
88 AND not exists
89 (select 's' from cn_period_statuses cn1
90 where gl.period_name = cn1.period_name
91 and cn1.period_set_id = l_period_set_id
92 and cn1.period_type_id = l_period_type_id);
93
94 l_period_rec periods%ROWTYPE;
95 l_msgs varchar2(2000);
96 l_org_id NUMBER;
97 l_request_id NUMBER;
98 my_message varchar2(2000);
99 x_return_status VARCHAR2(1);
100 x_msg_count NUMBER;
101 x_msg_data VARCHAR2(240);
102
103 BEGIN
104 retcode := 0; -- success = 0, warning = 1, fail = 2
105
106 -- get current working org ID
110 RAISE fnd_api.g_exc_unexpected_error;
107 l_org_id := mo_global.get_current_org_id;
108 IF l_org_id IS NULL THEN
109 -- org ID is not set... raise error
111 END IF;
112
113 fnd_file.put_line(fnd_file.Log, 'start open period');
114
115 -- wrapper for the open period procedure
116 OPEN repository_info;
117 FETCH repository_info
118 INTO l_set_of_books_id, l_period_set_id, l_period_type_id;
119 CLOSE repository_info;
120
121 OPEN periods;
122 FETCH periods INTO l_period_rec;
123 CLOSE periods;
124
125 l_acc_period_tbl(0) := l_period_rec;
126
127 fnd_file.put_line(fnd_file.Log, 'open acc periods');
128
129 update_acc_periods
130 (p_api_version => 1.0,
131 p_init_msg_list => fnd_api.g_true,
132 p_org_id => l_org_id,
133 p_acc_period_tbl => l_acc_period_tbl,
134 x_return_status => x_return_status,
135 x_msg_count => x_msg_count,
136 x_msg_data => x_msg_data);
137
138 IF x_return_status <> fnd_api.g_ret_sts_success THEN
139 RAISE fnd_api.g_exc_error;
140 END IF;
141
142 fnd_file.put_line(fnd_file.Log, 'start request');
143
144 -- submit concurrent program
145 start_request(l_org_id, l_request_id);
146
147 IF l_request_id = 0 THEN
148 RAISE fnd_api.g_exc_unexpected_error;
149 END IF;
150
151 COMMIT;
152 EXCEPTION
153 WHEN OTHERS THEN
154 rollback;
155 retcode := 2;
156 -- capture messages
157 l_msgs := '';
158 FOR l_counter IN 1..x_msg_count LOOP
159 my_message := FND_MSG_PUB.get(p_msg_index => l_counter,
160 p_encoded => FND_API.G_FALSE);
161 fnd_file.put_line(fnd_file.Log, my_message);
162 l_msgs := l_msgs || my_message || ' ';
163 end loop;
164 errbuf := l_msgs;
165 -- any other periods left PROCESSING should be FAILED
166
167 update cn_period_statuses
168 set processing_status_code = 'FAILED'
169 where processing_status_code = 'PROCESSING'
170 and period_name = l_period_rec.period_name;
171 commit;
172 END open_period;
173
174 -- Procedure to start concurrent request
175 PROCEDURE start_request(p_org_id IN NUMBER, x_request_id OUT NOCOPY NUMBER) IS
176 BEGIN
177 -- set org ID
178 fnd_request.set_org_id(p_org_id);
179 x_request_id := fnd_request.submit_request('CN', 'CN_OPEN_PERIODS',
180 NULL, NULL, NULL);
181 COMMIT;
182 END;
183
184
185 -- Start of comments
189 -- Pre-reqs : None.
186 -- API name : Update_Acc_Periods
187 -- Type : Private.
188 -- Function :
190 -- Parameters :
191 -- IN : p_api_version IN NUMBER Required
192 -- p_init_msg_list IN VARCHAR2 Optional
193 -- Default = FND_API.G_FALSE
194 -- p_commit IN VARCHAR2 Optional
195 -- Default = FND_API.G_FALSE
196 -- p_validation_level IN NUMBER Optional
197 -- Default = FND_API.G_VALID_LEVEL_FULL
198 -- p_acc_period_tbl IN acc_period_tbl_type Required
199 -- Default = null
200 -- IN p_org_id IN NUMBER Required
201 -- OUT : x_return_status OUT VARCHAR2(1)
202 -- x_msg_count OUT NUMBER
203 -- x_msg_data OUT VARCHAR2(2000)
204 -- Version : Current version 1.0
205 -- Initial version 1.0
206 --
207 -- Notes : 1) update period_status, insert period record into cn_period_statuses if the
208 -- the corresponding record does not exist in cn_period_statuses
209 -- 2) update cn_repositories.status if it is the first time to touch accumulation periods
210 --
211 -- End of comments
212
213 PROCEDURE Update_Acc_Periods
214 (p_api_version IN NUMBER ,
218 p_acc_period_tbl IN acc_period_tbl_type ,
215 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
216 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
219 p_org_id IN NUMBER,
220 x_return_status OUT NOCOPY VARCHAR2 ,
221 x_msg_count OUT NOCOPY NUMBER ,
222 x_msg_data OUT NOCOPY VARCHAR2 )
223 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'Update_Acc_Periods';
225 l_api_version CONSTANT NUMBER := 1.0;
226 l_request_id NUMBER;
227
228 l_set_of_books_id cn_repositories.set_of_books_id%TYPE;
229 l_repository_id cn_repositories.repository_id%TYPE;
230 l_calendar cn_period_sets.period_set_name%TYPE;
231 l_period_set_id cn_period_sets.period_set_id%TYPE;
232 l_period_type_id cn_period_types.period_type_id%TYPE;
233 l_period_type cn_period_types.period_type%TYPE;
234 l_quarter_num gl_period_statuses.quarter_num%TYPE;
235 l_period_num gl_period_statuses.period_num%TYPE;
236 l_object_version_number cn_period_statuses.object_version_number%TYPE;
237 l_closing_status gl_period_statuses.closing_status%TYPE;
238 l_closing_status_old gl_period_statuses.closing_status%TYPE;
239 l_proc_status_old cn_period_statuses.processing_status_code%TYPE;
240 l_freeze_flag_old cn_period_statuses.freeze_flag%TYPE;
241 l_pre_status gl_period_statuses.closing_status%TYPE;
242 l_next_status gl_period_statuses.closing_status%TYPE;
243 l_update_flag VARCHAR2(1) := 'N';
244 -- Added as part of bug fix bug#2804029
245 l_period_status_count NUMBER := 0;
246 l_pre_gl_status_count NUMBER := 0;
247 l_next_gl_status_count NUMBER := 0;
248 l_temp_start_date cn_period_statuses.start_date%TYPE;
249 l_org_id cn_period_statuses.org_id%TYPE;
250
251
252 CURSOR repository_info IS
253 SELECT r.set_of_books_id,
254 r.repository_id,
255 ps.period_set_id,
256 ps.period_set_name,
257 pt.period_type,
258 pt.period_type_id
259 FROM cn_repositories r,
260 cn_period_sets ps,
261 cn_period_types pt
262 WHERE r.repository_id > 0
263 AND r.application_type = 'CN'
264 AND r.period_set_id = ps.period_set_id
265 AND r.period_type_id = pt.period_type_id
266 AND r.org_id = p_org_id -- MOAC Change
267 AND r.org_id = ps.org_id -- MOAC Change
268 AND ps.org_id = pt.org_id; -- MOAC Change
269
270 CURSOR period_info(p_period_name gl_period_statuses.period_name%TYPE) IS
271 SELECT nvl(cn.org_id, p_org_id) org_id, -- MOAC Change
272 gl.quarter_num,
273 gl.period_num,
274 cn.object_version_number,
275 gl.closing_status,
276 cn.freeze_flag
277 FROM gl_period_statuses gl, cn_period_statuses cn
278 WHERE gl.application_id = 283
279 AND gl.set_of_books_id = l_set_of_books_id
280 AND gl.period_name = p_period_name
281 AND gl.adjustment_period_flag = 'N'
282 AND gl.period_name = cn.period_name(+)
283 AND cn.org_id(+) = p_org_id -- MOAC Change
284 FOR UPDATE OF gl.closing_status nowait;
285
286 CURSOR cn_period_info(p_period_name cn_period_statuses.period_name%TYPE,
287 p_period_year cn_period_statuses.period_year%TYPE) IS
288 SELECT cn.period_status, processing_status_code
289 FROM cn_period_statuses cn
290 WHERE cn.period_name = p_period_name
291 AND cn.period_year = p_period_year
292 AND cn.org_id = p_org_id -- MOAC Change
293 AND period_type_id = l_period_type_id
294 AND period_set_id = l_period_set_id;
295
296 -- Changed cursor fix bug#2804029
297 CURSOR pre_status_gl(p_period_year gl_period_statuses.period_year%TYPE, p_start_date DATE) IS
298 SELECT count(gl.closing_status)
299 FROM gl_period_statuses gl
300 WHERE gl.set_of_books_id = l_set_of_books_id
301 AND gl.application_id = 283
302 AND gl.adjustment_period_flag = 'N'
303 --AND gl.period_year = p_period_year
304 AND gl.start_date < p_start_date
305 ORDER BY gl.start_date;
306
307 -- Changed cursor to refer to cn_period_status instead of gl_period_statuses
308 -- as part of bug fix bug#2804029
309 CURSOR pre_status(p_start_date DATE) IS
310 SELECT 'N', start_date
311 FROM gl_period_statuses gl
312 WHERE gl.set_of_books_id = l_set_of_books_id
313 AND gl.application_id = 283
314 AND gl.adjustment_period_flag = 'N'
315 AND start_date < p_start_date
316 and not exists
317 (select 's' from cn_period_statuses cn1
318 where gl.period_name = cn1.period_name
319 and cn1.period_set_id = l_period_set_id
320 and cn1.PERIOD_TYPE_id = l_period_type_id
324 FROM cn_period_statuses cn
321 and cn1.org_id = p_org_id) -- MOAC Change
322 UNION
323 SELECT cn.period_status,start_date
325 WHERE cn.period_set_id = l_period_set_id
326 AND cn.period_type_id = l_period_type_id
327 AND cn.org_id = p_org_id -- MOAC Change
328 AND cn.start_date < p_start_date
329 ORDER BY start_date DESC;
330
331 -- Changed cursor fix bug#2804029
332 CURSOR next_status_gl(p_period_year gl_period_statuses.period_year%TYPE, p_start_date DATE) IS
333 SELECT count(gl.closing_status)
334 FROM gl_period_statuses gl
335 WHERE gl.set_of_books_id = l_set_of_books_id
336 AND gl.application_id = 283
337 AND gl.adjustment_period_flag = 'N'
338 --AND gl.period_year = p_period_year
339 AND gl.start_date > p_start_date
340 ORDER BY gl.start_date;
341
342 -- Changed cursor to refer to cn_period_status instead of gl_period_statuses
343 -- as part of bug fix bug#2804029
344
345 CURSOR next_status(p_start_date DATE) IS
346 SELECT cn.period_status
347 FROM cn_period_statuses cn
348 WHERE cn.period_set_id = l_period_set_id
349 AND cn.PERIOD_TYPE_id = l_period_type_id
350 AND cn.org_id = p_org_id -- MOAC Change
351 AND cn.start_date > p_start_date
352 ORDER BY cn.start_date;
353
354
355 BEGIN
356 -- Standard call to check for call compatibility.
357 IF NOT FND_API.Compatible_API_Call
358 (l_api_version ,
359 p_api_version ,
360 l_api_name ,
361 G_PKG_NAME )
362 THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365 -- Initialize message list if p_init_msg_list is set to TRUE.
366 IF FND_API.to_Boolean( p_init_msg_list ) THEN
367 FND_MSG_PUB.initialize;
368 END IF;
369 -- Initialize API return status to success
370 x_return_status := FND_API.G_RET_STS_SUCCESS;
371
372 -- API body
373 UPDATE cn_repositories
374 SET status = 'A'
375 WHERE repository_id > 0
376 AND application_type = 'CN'
377 AND org_id = p_org_id; -- MOAC Change
378
379 OPEN repository_info;
380 FETCH repository_info INTO l_set_of_books_id, l_repository_id,l_period_set_id, l_calendar, l_period_type,l_period_type_id;
381 CLOSE repository_info;
382
383 FOR i IN p_acc_period_tbl.first..p_acc_period_tbl.last LOOP
384 l_org_id := -1;
385 OPEN period_info(p_acc_period_tbl(i).period_name);
386 LOOP
387 FETCH period_info INTO l_org_id, l_quarter_num, l_period_num, l_object_version_number, l_closing_status_old, l_freeze_flag_old;
388 EXIT WHEN period_info%NOTFOUND OR l_org_id = p_org_id;
389 END LOOP;
390 CLOSE period_info;
391
392 -- IF (period_info%notfound) THEN
393 -- CLOSE period_info;
394 IF l_org_id <> p_org_id THEN
395 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
396 fnd_msg_pub.add;
397 RAISE fnd_api.g_exc_unexpected_error;
398 END IF;
399 -- CLOSE period_info;
400
401 IF (l_object_version_number <> p_acc_period_tbl(i).object_version_number) THEN
402 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
403 fnd_msg_pub.add;
404 RAISE fnd_api.g_exc_unexpected_error;
405 END IF;
406
407 l_closing_status := get_closing_status(p_acc_period_tbl(i).closing_status_meaning);
408 IF l_closing_status = 'X' THEN
409 l_closing_status := 'N';
410 END IF;
411
412 -- Added as part of bug fix bug#2804029
413 OPEN cn_period_info(p_acc_period_tbl(i).period_name,p_acc_period_tbl(i).period_year );
414 FETCH cn_period_info INTO l_closing_status_old,l_proc_status_old;
415 IF cn_period_info%NOTFOUND THEN
416 l_period_status_count := 0;
417 l_closing_status_old := 'N';
418 l_proc_status_old := 'CLEAN';
419 ELSE
420 l_period_status_count := 1;
421 END IF;
422 CLOSE cn_period_info;
423 -- End add as part of bug fix bug#2804029
424
425 IF (l_closing_status <> l_closing_status_old OR
426 nvl(l_freeze_flag_old, 'N') <> nvl(p_acc_period_tbl(i).freeze_flag, 'N' ) OR
427 l_proc_status_old = 'FAILED') THEN
428 l_update_flag := 'Y';
429
430 -- update the existing gl_period_statuses record
431 IF (i > p_acc_period_tbl.first) THEN
432 l_pre_status := get_closing_status(p_acc_period_tbl(i-1).closing_status_meaning);
433 ELSE
434 OPEN pre_status(p_acc_period_tbl(i).start_date);
435 FETCH pre_status INTO l_pre_status,l_temp_start_date;
436 IF (pre_status%notfound) THEN
437 -- changes for bug#2804029
438 OPEN pre_status_gl(p_acc_period_tbl(i).period_year, p_acc_period_tbl(i).start_date);
439 FETCH pre_status_gl INTO l_pre_gl_status_count;
440 CLOSE pre_status_gl;
441 IF l_pre_gl_status_count = 0 THEN
442 l_pre_status := 'B';
443 ELSE
444 l_pre_status := 'N';
445 END IF;
446 END IF;
447 CLOSE pre_status;
448 END IF;
449
450 IF l_pre_status = 'X' THEN
451 l_pre_status := 'N';
452 END IF;
453
454 IF (i < p_acc_period_tbl.last) THEN
458 FETCH next_status INTO l_next_status;
455 l_next_status := get_closing_status(p_acc_period_tbl(i+1).closing_status_meaning);
456 ELSE
457 OPEN next_status( p_acc_period_tbl(i).start_date);
459 IF (next_status%notfound) THEN
460 -- changes for bug#2804029
461 OPEN next_status_gl(p_acc_period_tbl(i).period_year, p_acc_period_tbl(i).start_date);
462 FETCH next_status_gl INTO l_pre_gl_status_count;
463 CLOSE next_status_gl;
464 IF l_next_gl_status_count = 0 THEN
465 l_next_status := 'L';
466 ELSE
467 l_next_status := 'N';
468 END IF;
469 END IF;
470 CLOSE next_status;
471
472 END IF;
473
474 IF l_next_status = 'X' THEN
475 l_next_status := 'N';
476 END IF;
477
478 /* commented out for bug 5035044
479 IF (l_quarter_num NOT IN (1, 2, 3, 4)) THEN
480 fnd_message.set_name('CN', 'CNSYPR_QUARTER_NUMBER');
481 fnd_msg_pub.add;
482 RAISE fnd_api.g_exc_error;
483 END IF;
484 */
485
486 IF (l_closing_status = 'O' AND l_closing_status_old <> 'O' ) THEN
487 IF (l_closing_status_old = 'P') THEN
488 fnd_message.set_name('CN', 'CNSYPR_OPEN_PERIOD');
489 fnd_msg_pub.add;
490 RAISE fnd_api.g_exc_error;
491 END IF;
492
496 fnd_msg_pub.add;
493 -- can not open a period whose previous period is Never Opened
494 IF (l_pre_status = 'N') THEN
495 fnd_message.set_name('CN', 'CNSYPR_OPEN_PRE_NEVER');
497 RAISE fnd_api.g_exc_error;
498 -- can not open a period whose previous period is Future Enterable
499 elsif (l_pre_status = 'F') then
500 fnd_message.set_name('CN', 'CNSYPR_OPEN_PRE_FUTURE');
501 fnd_msg_pub.add;
502 raise fnd_api.g_exc_error;
503
504 -- can not open a period whose next period is Closed or Permanently Closed
505 elsif (l_next_status IN ('P', 'C')) then
506 fnd_message.set_name('CN', 'CNSYPR_OPEN_NEXT_CLOSE');
507 fnd_msg_pub.add;
508 raise fnd_api.g_exc_error;
509 end if;
510 END IF;
511
512 IF ((l_closing_status = 'C' AND l_closing_status_old <> 'C') OR (l_closing_status = 'P' AND l_closing_status_old <> 'P')) THEN
513 --IF ((l_closing_status = 'C' AND l_closing_status_old <> 'O') OR (l_closing_status = 'P' AND l_closing_status_old NOT IN ('O', 'C'))) THEN
514 -- fnd_message.set_name('CN', 'CN_CLOSE_PERIOD');
515 -- fnd_msg_pub.add;
516 -- RAISE fnd_api.g_exc_error;
517 --END IF;
518
519 -- can not close/permanently close a period whose previous period is not closed
520 -- Note: when a period is open, it is impossible for its previous period to be Never Opened or Future Entry
521 if (l_pre_status = 'O') then
522 fnd_message.set_name('CN', 'CNSYPR_CLOSE_PRE_OPEN');
523 fnd_msg_pub.add;
524 raise fnd_api.g_exc_error;
525 END IF;
526 END IF;
527
528 IF (l_closing_status = 'F' AND l_closing_status_old <> 'F') THEN
529 IF (l_closing_status_old <> 'N') THEN
530 fnd_message.set_name('CN', 'CNSYPR_FUTURE_ENTERABLE');
531 fnd_msg_pub.add;
532 RAISE fnd_api.g_exc_error;
533 END IF;
534
535 -- can not set a period to F whose previous period is Never Opened
536 if (l_pre_status = 'N') then
537 fnd_message.set_name('CN', 'CNSYPR_FUTURE_PRE_NEVER');
538 fnd_msg_pub.add;
539 raise fnd_api.g_exc_error;
540 END IF;
541 END IF;
542
543 cn_periods_api.update_gl_status(p_org_id, -- MOAC Change
544 p_acc_period_tbl(i).period_name,
545 l_closing_status,
546 'Y',
547 283,
548 l_set_of_books_id,
549 p_acc_period_tbl(i).freeze_flag,
550 sysdate,
551 fnd_global.login_id,
552 fnd_global.user_id);
553
554 -- create a matching CN_PERIOD_STATUSES record if necessary
555 cn_periods_api.check_cn_period_record
556 (x_org_id => p_org_id, -- MOAC Change
557 x_period_name => p_acc_period_tbl(i).period_name,
558 x_closing_status => l_closing_status,
559 x_period_type => l_period_type,
560 x_period_year => p_acc_period_tbl(i).period_year,
561 x_quarter_num => l_quarter_num,
562 x_period_num => l_period_num,
563 x_period_set_name => l_calendar,
564 x_start_date => p_acc_period_tbl(i).start_date,
565 x_end_date => p_acc_period_tbl(i).end_date,
566 x_freeze_flag => p_acc_period_tbl(i).freeze_flag,
567 x_repository_id => l_repository_id);
568
569 END IF;
570 END LOOP;
571
572 -- bug 1979768 : move all the srp table insert into a concurrent program
573 -- commit here to force the update on cn_period_statuses.
574 --commit;
575
576 --this section is added for intimating the user that updates were not saved
577 --because the information was identical to what exists in the db.
578 --bug # 2471028
579
580 IF (l_update_flag = 'N') THEN
581 fnd_message.set_name('CN', 'CN_NO_CHANGES');
582 fnd_msg_pub.add;
583 END IF;
584
585 -- Call concurrent program
586 -- commented since it has moved to start_request procedure
587 -- IF (l_update_flag = 'Y') THEN
588 -- l_request_id := fnd_request.submit_request
589 -- (
590 -- application => 'CN'
591 -- ,program => 'CN_OPEN_PERIODS'
592 -- ,description => NULL
593 -- ,start_time => NULL
594 -- ,sub_request => NULL);
595 --
596 -- IF l_request_id = 0 THEN
597 -- RAISE FND_API.g_exc_unexpected_error;
598 -- END IF;
599 -- END IF;
600
601 -- End of API body.
602
603 -- Standard check of p_commit.
604 IF FND_API.To_Boolean( p_commit ) THEN
605 COMMIT WORK;
606 END IF;
607 -- Standard call to get message count and if count is 1, get message info.
608 FND_MSG_PUB.count_and_get
609 (p_count => x_msg_count ,
610 p_data => x_msg_data ,
611 p_encoded => FND_API.G_FALSE );
612 EXCEPTION
613 WHEN FND_API.G_EXC_ERROR THEN
614 x_return_status := FND_API.G_RET_STS_ERROR ;
615 FND_MSG_PUB.count_and_get
616 (p_count => x_msg_count ,
617 p_data => x_msg_data ,
618 p_encoded => FND_API.G_FALSE );
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
621 FND_MSG_PUB.count_and_get
622 (p_count => x_msg_count ,
623 p_data => x_msg_data ,
627 IF FND_MSG_PUB.check_msg_level
624 p_encoded => FND_API.G_FALSE );
625 WHEN OTHERS THEN
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
628 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
629 THEN
630 FND_MSG_PUB.add_exc_msg
631 (G_PKG_NAME ,
632 l_api_name );
633 END IF;
634 FND_MSG_PUB.count_and_get
635 (p_count => x_msg_count ,
636 p_data => x_msg_data ,
637 p_encoded => FND_API.G_FALSE );
638 END update_acc_periods;
639
640 -- populate the accumulation periods screen
641 PROCEDURE get_acc_periods
642 (p_year IN NUMBER,
643 x_system_status OUT NOCOPY cn_lookups.meaning%TYPE,
644 x_calendar OUT NOCOPY cn_period_sets.period_set_name%TYPE,
645 x_period_type OUT NOCOPY cn_period_types.period_type%TYPE,
646 x_acc_period_tbl OUT NOCOPY acc_period_tbl_type)
647 IS
648 l_set_of_books_id cn_repositories.set_of_books_id%TYPE;
649 l_period_set_id cn_repositories.period_set_id%TYPE;
650 l_period_type_id cn_repositories.period_type_id%TYPE;
651 l_current_year gl_period_statuses.period_year%TYPE;
652
653 CURSOR current_year IS
654 SELECT period_year
655 FROM gl_period_statuses
656 WHERE trunc(sysdate) BETWEEN start_date AND end_date
657 AND application_id = 283
658 AND adjustment_period_flag = 'N'
659 AND set_of_books_id = (SELECT set_of_books_id
660 FROM cn_repositories
661 WHERE repository_id > 0
662 AND application_type = 'CN')
663 AND ROWNUM = 1;
664
665 CURSOR first_year IS
666 SELECT max(period_year)
667 FROM gl_period_statuses
668 WHERE application_id = 283
669 AND adjustment_period_flag = 'N'
670 AND set_of_books_id = (SELECT set_of_books_id
671 FROM cn_repositories
672 WHERE repository_id > 0
673 AND application_type = 'CN')
674 ;
675
676 CURSOR repository_info IS
677 SELECT status,
678 set_of_books_id,
679 period_set_id,
680 period_type_id
681 FROM cn_repositories
682 WHERE repository_id > 0
683 AND application_type = 'CN';
684
685 CURSOR calendar IS
686 SELECT period_set_name
687 FROM cn_period_sets
688 WHERE period_set_id = l_period_set_id;
689
690 CURSOR period_type IS
691 SELECT period_type
692 FROM cn_period_types
693 WHERE period_type_id = l_period_type_id;
694
695 -- Changed cursor fix bug#2804029
696 CURSOR periods IS
697 SELECT cn.period_name,
698 cn.period_year,
699 cn.start_date,
700 cn.end_date,
701 gp.meaning closing_status_meaning,
702 cp.meaning processing_status,
703 cn.freeze_flag,
704 cn.object_version_number
705 FROM cn_period_statuses cn,
706 cn_lookups gp,
707 cn_lookups cp
708 WHERE
709 gp.lookup_type = 'PERIOD_CLOSING_STATUS'
710 AND gp.lookup_code = cn.PERIOD_STATUS
711 AND cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
712 AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
713 AND cn.period_year = nvl(p_year, l_current_year)
714 AND cn.period_set_id = l_period_set_id
715 AND cn.period_type_id = l_period_type_id
716 UNION
717 SELECT gl.period_name,
718 gl.period_year,
719 gl.start_date,
720 gl.end_date,
721 gp.meaning closing_status_meaning,
722 cp.meaning processing_status,
723 cn.freeze_flag,
724 cn.object_version_number
725 FROM gl_period_statuses gl,
726 cn_period_statuses cn,
727 cn_lookups gp,
728 cn_lookups cp
729 WHERE gl.set_of_books_id = l_set_of_books_id
730 AND gl.application_id = 283
731 AND gl.adjustment_period_flag = 'N'
732 AND gl.period_name = cn.period_name(+)
733 AND gp.lookup_type = 'PERIOD_CLOSING_STATUS'
734 AND gp.lookup_code = DECODE(gl.CLOSING_STATUS,'N','N','X')
735 AND cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
736 AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
737 AND gl.period_year = nvl(p_year, l_current_year)
738 AND cn.period_type_id(+) = l_period_type_id
739 AND cn.period_set_id (+) = l_period_set_id
740 and not exists
741 (select 's' from cn_period_statuses cn1
742 where gl.period_name = cn1.period_name
743 and cn1.period_set_id = l_period_set_id
744 and cn1.period_type_id = l_period_type_id)
745 order by 2,3;
746
747
748
749
750
751 BEGIN
752 OPEN repository_info;
753 FETCH repository_info INTO x_system_status, l_set_of_books_id, l_period_set_id, l_period_type_id;
754 CLOSE repository_info;
755
756 OPEN calendar;
757 FETCH calendar INTO x_calendar;
758 CLOSE calendar;
759
760 OPEN period_type;
761 FETCH period_type INTO x_period_type;
762 CLOSE period_type;
763
764 x_system_status := cn_api.get_lkup_meaning(x_system_status, 'REPOSITORY_STATUS');
765
766 IF (p_year IS NULL) THEN
767 OPEN current_year;
768 FETCH current_year INTO l_current_year;
769 CLOSE current_year;
770 IF l_current_year is null then
771 OPEN first_year;
772 FETCH first_year INTO l_current_year;
773 CLOSE first_year;
774 END IF;
775 ELSE
776 l_current_year := p_year;
777 END IF;
778
779 FOR period IN periods LOOP
783 END IF;
780 x_acc_period_tbl(x_acc_period_tbl.COUNT + 1) := period;
781 IF (x_acc_period_tbl(x_acc_period_tbl.COUNT).freeze_flag IS NULL) THEN
782 x_acc_period_tbl(x_acc_period_tbl.COUNT).freeze_flag := 'N';
784 END LOOP;
785 END get_acc_periods;
786
787 BEGIN
788 FOR lkup IN lookup_table LOOP
789 g_code_tbl(g_code_tbl.COUNT + 1) := lkup.lookup_code;
790 g_meaning_tbl(g_meaning_tbl.COUNT + 1) := lkup.meaning;
791 END LOOP;
792
793 END CN_ACC_PERIODS_PVT;
[Home] [Help]