1 PACKAGE BODY OKL_PERD_STATUS_PVT AS
2 /* $Header: OKLRPSMB.pls 120.8.12010000.3 2008/12/11 23:33:05 sgiyer ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.ACCOUNTING.PERIOD';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8
9
10
11 -- End of wraper code generated automatically by Debug code generator
12 G_WARNING_STATUS VARCHAR2(1) := 'W';
13
14
15 -- Added by kthiruva on 26-Sep-2003 Bug No.3126403
16 -- Procedure to clear the message except the first error message
17
18 PROCEDURE Clear_Message IS
19 BEGIN
20
21 FOR i IN 1..(fnd_msg_pub.count_msg-1) LOOP
22 IF fnd_msg_pub.count_msg <> 1 THEN
23 fnd_msg_pub.delete_MSG(p_msg_index => 1);
24 END IF;
25 END LOOP;
26
27 END Clear_Message;
28
29
30 PROCEDURE SEARCH_PERIOD_STATUS(p_api_version IN NUMBER,
31 p_init_msg_list IN VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 p_period_rec IN PERIOD_REC_TYPE,
36 x_period_tbl OUT NOCOPY PERIOD_TBL_TYPE )
37 IS
38
39 l_api_name CONSTANT VARCHAR2(40) := 'SEARCH_PERIOD_STATUS';
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_row_count NUMBER;
42 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
43
44 l_period_rec period_rec_type;
45 l_stmt VARCHAR2(3000);
46 l_application_id NUMBER := 540;
47 l_set_of_books_id NUMBER ;
48 l_perd_rec period_rec_type;
49
50
51 i NUMBER := 0;
52
53 TYPE ref_cursor IS REF CURSOR ;
54 perd_csr ref_cursor;
55
56
57
58 BEGIN
59
60
61 x_return_status := OKL_API.G_RET_STS_SUCCESS;
62
63 l_set_of_books_id := p_period_rec.SET_OF_BOOKS_ID;
64
65
66 l_stmt := ' SELECT APPLICATION_ID
67 ,LEDGER_ID
68 ,PERIOD_NAME
69 ,LAST_UPDATE_DATE
70 ,LAST_UPDATED_BY
71 ,CLOSING_STATUS
72 ,START_DATE
73 ,END_DATE
74 ,PERIOD_TYPE
75 ,PERIOD_YEAR
76 ,PERIOD_NUM
77 ,QUARTER_NUM
78 ,ADJUSTMENT_PERIOD_FLAG
79 ,CREATION_DATE
80 ,CREATED_BY
81 ,LAST_UPDATE_LOGIN
82 ,ATTRIBUTE1
83 ,ATTRIBUTE2
84 ,ATTRIBUTE3
85 ,ATTRIBUTE4
86 ,ATTRIBUTE5
87 ,CONTEXT
88 ,YEAR_START_DATE
89 ,QUARTER_START_DATE
90 ,EFFECTIVE_PERIOD_NUM
91 ,ELIMINATION_CONFIRMED_FLAG
92 FROM GL_PERIOD_STATUSES
93 WHERE Application_id = ' || ':1' ||
94 ' AND ledger_id = ' || ':2' ;
95
96
97
98 l_stmt := l_stmt || ' AND period_name = '|| NVL(':3','period_name') ;
99
100
101 l_stmt := l_stmt || ' AND period_num = ' ||NVL(':4','period_num');
102
103
104 l_stmt := l_stmt || ' AND period_year = ' ||NVL(':5','period_year');
105
106
107 l_stmt := l_stmt || ' AND closing_status = ' || NVL(':6','closing_status');
108
109
110
111
112 OPEN perd_csr FOR l_stmt USING l_application_id ,
113 l_set_of_books_id ,
114 p_period_rec.period_name ,
115 p_period_rec.period_num ,
116 p_period_rec.period_year,
117 p_period_rec.closing_status ;
118
119
120
121 LOOP
122
123 i := i + 1;
124
125 FETCH perd_csr INTO l_perd_rec;
126 EXIT WHEN perd_csr%NOTFOUND;
127
128 l_period_rec.APPLICATION_ID := l_perd_rec.APPLICATION_ID;
129 l_period_rec.SET_OF_BOOKS_ID := l_perd_rec.SET_OF_BOOKS_ID;
130 l_period_rec.PERIOD_NAME := l_perd_rec.PERIOD_NAME ;
131 l_period_rec.LAST_UPDATE_DATE := l_perd_rec.LAST_UPDATE_DATE ;
132 l_period_rec.LAST_UPDATED_BY := l_perd_rec.LAST_UPDATED_BY;
133 l_period_rec.CLOSING_STATUS := l_perd_rec.CLOSING_STATUS;
134 l_period_rec.START_DATE := l_perd_rec.START_DATE;
135 l_period_rec.END_DATE := l_perd_rec.END_DATE;
136 l_period_rec.PERIOD_TYPE := l_perd_rec.PERIOD_TYPE ;
137 l_period_rec.PERIOD_YEAR := l_perd_rec.PERIOD_YEAR;
138 l_period_rec.PERIOD_NUM := l_perd_rec.PERIOD_NUM ;
139 l_period_rec.QUARTER_NUM := l_perd_rec.QUARTER_NUM ;
140 l_period_rec.ADJUSTMENT_PERIOD_FLAG := l_perd_rec.ADJUSTMENT_PERIOD_FLAG ;
141 l_period_rec.CREATION_DATE := l_perd_rec.CREATION_DATE;
142 l_period_rec.CREATED_BY := l_perd_rec.CREATED_BY ;
143 l_period_rec.LAST_UPDATE_LOGIN := l_perd_rec.LAST_UPDATE_LOGIN;
144 l_period_rec.ATTRIBUTE1 := l_perd_rec.ATTRIBUTE1;
145 l_period_rec.ATTRIBUTE2 := l_perd_rec.ATTRIBUTE2 ;
146 l_period_rec.ATTRIBUTE3 := l_perd_rec.ATTRIBUTE3;
147 l_period_rec.ATTRIBUTE4 := l_perd_rec.ATTRIBUTE4 ;
148 l_period_rec.ATTRIBUTE5 := l_perd_rec.ATTRIBUTE5 ;
149 l_period_rec.CONTEXT := l_perd_rec.CONTEXT;
150 l_period_rec.YEAR_START_DATE := l_perd_rec.YEAR_START_DATE;
151 l_period_rec.QUARTER_START_DATE := l_perd_rec.QUARTER_START_DATE;
152 l_period_rec.EFFECTIVE_PERIOD_NUM := l_perd_rec.EFFECTIVE_PERIOD_NUM;
153 l_period_rec.ELIMINATION_CONFIRMED_FLAG := l_perd_rec.ELIMINATION_CONFIRMED_FLAG ;
154
155 x_period_tbl(i) := l_period_rec;
156
157 END LOOP;
158
159 CLOSE perd_csr;
160
161 EXCEPTION
162
163 WHEN OKL_API.G_EXCEPTION_ERROR THEN
164 x_return_status := OKL_API.G_RET_STS_ERROR;
165
166
167 END SEARCH_PERIOD_STATUS;
168
169
170
171 PROCEDURE UPDATE_PERIOD_STATUS(p_api_version IN NUMBER,
172 p_init_msg_list IN VARCHAR2,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 p_period_tbl IN PERIOD_TBL_TYPE)
177 IS
178
179 l_api_name CONSTANT VARCHAR2(40) := 'UPDATE_PERIOD_STATUS';
180 l_api_version CONSTANT NUMBER := 1.0;
181 l_row_count NUMBER;
182
183 l_stmt VARCHAR2(2000);
184 l_count_rec NUMBER := 0;
185 i NUMBER := 0;
186
187 CURSOR perd_csr(p_period_name VARCHAR2,p_ledger_id NUMBER) IS
188 SELECT closing_status
189 FROM gl_period_statuses
190 WHERE application_id = 540
191 AND ledger_id = p_ledger_id
192 AND period_name = p_period_name;
193
194 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
195 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
196 l_closing_status GL_PERIOD_STATUSES.closing_status%TYPE;
197 l_msg_data_comp VARCHAR2(2000);
198 l_msg_data VARCHAR2(2000);
199 l_msg_count NUMBER;
200
201 BEGIN
202
203 x_return_status := OKL_API.G_RET_STS_SUCCESS;
204
205 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
206 G_PKG_NAME,
207 p_init_msg_list,
208 l_api_version,
209 p_api_version,
210 '_PVT',
211 x_return_status);
212 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
213 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
214 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
215 RAISE OKL_API.G_EXCEPTION_ERROR;
216 END IF;
217
218
219 FOR i IN 1..p_period_tbl.COUNT
220
221 LOOP
222
223
224 OPEN perd_csr(p_period_tbl(i).period_name,p_period_tbl(i).set_of_books_id);
225 FETCH perd_csr INTO l_closing_status;
226 CLOSE perd_csr;
227
228 ---- Call update only if period status is changed
229
230 IF (l_closing_status <> p_period_tbl(i).closing_status) THEN
231
232 UPDATE_PERD_ROW(p_api_version => 1.0,
233 p_init_msg_list => p_init_msg_list,
234 x_return_status => l_return_status,
235 x_msg_count => x_msg_count,
236 x_msg_data => x_msg_data,
237 p_period_rec => p_period_tbl(i));
238
239
240 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
241 Clear_Message;
242 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
243 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
244 Clear_Message;
245 RAISE OKL_API.G_EXCEPTION_ERROR;
246 END IF;
247
248 IF l_return_status = G_WARNING_STATUS THEN
249 l_overall_status := G_WARNING_STATUS;
250 END IF;
251
252 END IF;
253 END LOOP;
254
255 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
256
257
258 -- Added by kthiruva on 26-Sep-2003 Bug No.3126403
259 -- To get the warning messages in message stack incase of warnings
260
261 IF l_overall_status = G_WARNING_STATUS THEN
262 FOR i IN 1..fnd_msg_pub.count_msg LOOP
263 fnd_msg_pub.get(
264 p_msg_index => i,
265 p_encoded => fnd_api.g_false,
266 p_data => l_msg_data,
267 p_msg_index_out => l_msg_count);
268
269 l_msg_data_comp := l_msg_data_comp || ' ' || SUBSTR(l_msg_data,1,150);
270 END LOOP;
271 ELSE
272 Clear_Message;
273 END IF;
274
275 x_return_status := l_overall_status;
276 x_msg_data := l_msg_data_comp;
277
278
279 EXCEPTION
280 WHEN OKL_API.G_EXCEPTION_ERROR THEN
281 x_return_status := OKL_API.HANDLE_EXCEPTIONS
282 (
283 l_api_name,
284 G_PKG_NAME,
285 'OKL_API.G_RET_STS_ERROR',
286 x_msg_count,
287 x_msg_data,
288 '_PVT'
289 );
290 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
291 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
292 (
293 l_api_name,
294 G_PKG_NAME,
295 'OKL_API.G_RET_STS_UNEXP_ERROR',
296 x_msg_count,
297 x_msg_data,
298 '_PVT'
299 );
300 WHEN OTHERS THEN
301 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
302 (
303 l_api_name,
304 G_PKG_NAME,
305 'OTHERS',
306 x_msg_count,
307 x_msg_data,
308 '_PVT'
309 );
310
311
312 END UPDATE_PERIOD_STATUS;
313
314
315
316 PROCEDURE UPDATE_PERD_ROW (p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 p_period_rec IN PERIOD_REC_TYPE)
322
323 IS
324
325 l_api_name CONSTANT VARCHAR2(40) := 'UPDATE_PERD_ROW';
326 l_api_version CONSTANT NUMBER := 1.0;
327 l_row_count NUMBER;
328 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
329
330 CURSOR prd_csr (v_period_name VARCHAR2,p_ledger_id NUMBER) IS
331 SELECT closing_status
332 FROM GL_PERIOD_STATUSES
333 WHERE application_id = 540
334 AND ledger_id = p_ledger_id
335 AND period_name = v_period_name
336 FOR UPDATE;
337 /* This cursor used to check if there are some unprocessed distributions which
338 needs to be posted to gl but are still not posted .
339 CURSOR dist_csr(p_start_date DATE,p_end_date DATE,p_ledger_id NUMBER) IS
340 SELECT '1'
341 FROM okl_trns_acc_dstrs
342 WHERE gl_date BETWEEN p_start_date AND p_end_date
343 AND post_to_gl ='Y'
344 AND posted_yn = 'N'
345 AND org_id IN
346 (SELECT org_id
347 FROM OKL_SYS_ACCT_OPTS
348 WHERE set_of_books_id =p_ledger_id);
349 */
350
351 -- Bug Number: 5707866 dpsingh SLA Uptake Changes for Accounting Period Status
352 CURSOR unprocessed_events_csr(p_start_date DATE,p_end_date DATE,p_ledger_id NUMBER) IS
353 SELECT 1
354 FROM xla_events xle, xla_transaction_entities xte
355 WHERE xle.event_date BETWEEN p_start_date AND p_end_date
356 AND xle.entity_id = xte.entity_id
357 AND xle.event_status_code IN ('I','U')
358 AND xte.ledger_id = p_ledger_id
359 AND xle.application_id = 540
360 AND rownum = 1;
361
362 --Bug 6034826 dpsingh
363 -- Bug 7634078. Commenting below check
364
365 /*CURSOR aeh_csr(p_ledger_id NUMBER) IS
366 SELECT 1
367 FROM xla_ae_headers
368 WHERE application_id = 540
369 AND gl_transfer_status_code = 'N'
370 AND ledger_id = p_ledger_id
371 AND PERIOD_NAME = p_period_rec.period_name;
372 */
373 --- Cursor to find the first ever opened period (or closed period)
374
375 -- Fixed Bug 3621515 by Santonyr on 15-May-2004
376
377 CURSOR gl_csr1(p_start_date DATE,p_ledger_id NUMBER) IS
378 SELECT period_name
379 FROM gl_period_statuses
380 WHERE application_id = 101
381 AND ledger_id = p_ledger_id
382 AND start_date <= p_start_date
383 AND closing_status IN ('O', 'F');
384
385
386 --- Cursor to find out status of a period in GL
387 CURSOR gl_csr2(p_period_name VARCHAR2,p_ledger_id NUMBER) IS
388 SELECT closing_status
389 FROM gl_period_statuses
390 WHERE application_id = 101
391 AND ledger_id = p_ledger_id
392 AND period_name = p_period_name;
393
394
398 CURSOR prior_csr(p_start_date DATE,p_ledger_id NUMBER) IS
395 --- Cursor to find out if there is a period in OKL with 'F' or 'O' status prior to current period
396 --- which is being closed
397
399 SELECT period_name
400 FROM gl_period_statuses
401 WHERE application_id = 540
402 AND ledger_id = p_ledger_id
403 AND closing_status IN ('F','O')
404 AND start_date < p_start_date;
405
406
407
408 i NUMBER := 0;
409 l_start_date DATE;
410 l_end_date DATE;
411 l_dummy VARCHAR2(1);
412 l_duplicate VARCHAR2(1);
413 l_period_name GL_PERIOD_STATUSES.period_name%TYPE;
414
415 l_closing_status GL_PERIOD_STATUSES.closing_status%TYPE;
416 l_gl_closing_status GL_PERIOD_STATUSES.closing_status%TYPE;
417
418 BEGIN
419
420 x_return_status := OKL_API.G_RET_STS_SUCCESS;
421
422 IF (p_period_rec.CLOSING_STATUS IS NULL) OR
423 (p_period_rec.CLOSING_STATUS = OKL_API.G_MISS_CHAR) THEN
424
425 OKL_API.SET_MESSAGE(p_app_name => g_app_name
426 ,p_msg_name => g_required_value
427 ,p_token1 => g_col_name_token
428 ,p_token1_value => 'PERIOD STATUS');
429
430 RAISE OKL_API.G_EXCEPTION_ERROR;
431
432 END IF;
433
434 OPEN prd_csr(p_period_rec.period_name,p_period_rec.set_of_books_id);
435 FETCH prd_csr INTO l_closing_status;
436
437 IF prd_csr%NOTFOUND THEN
438 CLOSE prd_csr;
439 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
440 p_msg_name => 'OKL_PERIOD_NOT_FOUND',
441 p_token1 => 'PERIOD_NAME',
442 p_token1_value => p_period_rec.period_name);
443 RAISE OKL_API.G_EXCEPTION_ERROR;
444 END IF;
445
446 CLOSE prd_csr;
447
448 ------ Check for Valid status changes
449 ------ YOu can go from N to F,O; From F to O; from O to C; from C to N and from O to C
450
451 IF (l_closing_status = 'N') THEN
452 IF NOT (p_period_rec.closing_status IN('F','O','N')) THEN
453
454 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
455 p_msg_name => 'OKL_PERD_INVALID_CHANGE',
456 p_token1 => 'PERIOD_NAME',
457 p_token1_value => p_period_rec.period_name);
458
459 RAISE OKL_API.G_EXCEPTION_ERROR;
460
461 END IF;
462 ELSIF (l_closing_status = 'F') THEN
463 IF NOT (p_period_rec.closing_status IN ('O','F')) THEN
464
465 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
466 p_msg_name => 'OKL_PERD_INVALID_CHANGE',
467 p_token1 => 'PERIOD_NAME',
468 p_token1_value => p_period_rec.period_name);
469
470 RAISE OKL_API.G_EXCEPTION_ERROR;
471
472 END IF;
473 ELSIF (l_closing_status = 'O') THEN
474 IF NOT (p_period_rec.closing_status IN ('C','O')) THEN
475
476 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
477 p_msg_name => 'OKL_PERD_INVALID_CHANGE',
478 p_token1 => 'PERIOD_NAME',
479 p_token1_value => p_period_rec.period_name);
480
481 RAISE OKL_API.G_EXCEPTION_ERROR;
482
483 END IF;
484 ELSIF (l_closing_status = 'C') THEN
485 IF NOT (p_period_rec.closing_status IN ('O','P','C')) THEN
486
487 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
488 p_msg_name => 'OKL_PERD_INVALID_CHANGE',
489 p_token1 => 'PERIOD_NAME',
490 p_token1_value => p_period_rec.period_name);
491
492 RAISE OKL_API.G_EXCEPTION_ERROR;
493
494 END IF;
495 ELSIF (l_closing_status = 'P') THEN
496 IF NOT (p_period_rec.closing_status = 'P') THEN
497
498 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
499 p_msg_name => 'OKL_PERD_INVALID_CHANGE',
500 p_token1 => 'PERIOD_NAME',
501 p_token1_value => p_period_rec.period_name);
502
503 RAISE OKL_API.G_EXCEPTION_ERROR;
504 END IF;
505
506 END IF;
507
508 ----- End of status changes validation
509
510 ----- Get the period information
511 -- rkuttiya changed for Multi Gaap Project bug 7263041
512 OKL_ACCOUNTING_UTIL.get_period_info(p_period_name => p_period_rec.period_name,
513 p_start_date => l_start_date,
514 p_end_date => l_end_date,
515 p_ledger_id =>
516 p_period_rec.set_of_books_id);
517
518 ----- Validate that the period being opened is later than the first ever opened GL period
519
520 IF (l_closing_status IN ('N') ) AND
521 (p_period_rec.closing_status IN ('F','O')) THEN --- You are trying to open a period
522
523 l_period_name := NULL;
527
524 OPEN gl_csr1(l_start_date,p_period_rec.set_of_books_id);
525 FETCH gl_csr1 INTO l_period_name;
526 CLOSE gl_csr1;
528 IF (l_period_name IS NULL) THEN
529 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
530 p_msg_name => 'OKL_PERD_LATER_THAN_GL',
531 p_token1 => 'PERIOD_NAME',
532 p_token1_value => p_period_rec.period_name);
533 RAISE OKL_API.G_EXCEPTION_ERROR;
534
535 END IF;
536
537 END IF;
538
539 ---- If you are trying to re-open a closed period, make sure that GL period is Open
540
541 IF (l_closing_status IN ('C')) AND
542 (p_period_rec.closing_status IN ('O')) THEN --- You are trying to re-open a closed period
543
544 l_gl_closing_status := NULL;
545
546 OPEN gl_csr2(p_period_rec.period_name,p_period_rec.set_of_books_id);
547 FETCH gl_csr2 INTO l_gl_closing_status;
548 CLOSE gl_csr2;
549
550 IF (l_gl_closing_status IN ('C','P')) THEN
551
552 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
553 p_msg_name => 'OKL_GL_PERD_NOT_OPEN',
554 p_token1 => 'PERIOD_NAME',
555 p_token1_value => p_period_rec.period_name);
556
557 RAISE OKL_API.G_EXCEPTION_ERROR;
558
559 END IF;
560
561 END IF;
562
563
564 ---- If you are trying to close a period, make sure that prior periods are not open
565 ---- In this case, just issue error message, no need to abort.
566
567 IF (l_closing_status IN ('O')) AND
568 (p_period_rec.closing_status IN ('C')) THEN --- You are trying to close a period
569
570 l_period_name := NULL;
571
572 OPEN prior_csr(l_start_date,p_period_rec.set_of_books_id);
573 FETCH prior_csr INTO l_period_name;
574 CLOSE prior_csr;
575
576 IF (l_period_name IS NOT NULL) THEN
577
578 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
579 p_msg_name => 'OKL_PRIOR_PERD_NOT_OPEN',
580 p_token1 => 'PERIOD_NAME',
581 p_token1_value => p_period_rec.period_name);
582
583 x_return_status := G_WARNING_STATUS;
584
585 END IF;
586
587 /* Bug 6017488 dpsingh
588 -- Check in the distribution that there are no un-accounted distribution for this period.
589 OPEN dist_csr(l_start_date,l_end_date,p_period_rec.set_of_books_id);
590 FETCH dist_csr INTO l_dummy;
591
592 IF (l_dummy = '1')THEN
593 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
594 p_msg_name => 'OKL_PERD_INVALID_CLOSE',
595 p_token1 => 'PERIOD_NAME',
596 p_token1_value => p_period_rec.period_name);
597 CLOSE dist_csr;
598 RAISE OKL_API.G_EXCEPTION_ERROR;
599
600 END IF;
601
602 CLOSE dist_csr;
603 */
604 -- Bug Number: 5707866 dpsingh SLA Uptake Changes for Accounting Period Status
605 ---- Error should be thrown on closing the period if there are any accounting events that are not accounted i.e. events are still in the status of Incomplete or Unprocessed
606 l_dummy := NULL;
607 OPEN unprocessed_events_csr(l_start_date,l_end_date,p_period_rec.set_of_books_id);
608 FETCH unprocessed_events_csr INTO l_dummy;
609 CLOSE unprocessed_events_csr;
610
611 IF (l_dummy = '1')THEN
612 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
613 p_msg_name => 'OKL_PERD_INVALID_CLOSE',
614 p_token1 => 'PERIOD_NAME',
615 p_token1_value => p_period_rec.period_name);
616 CLOSE unprocessed_events_csr;
617 RAISE OKL_API.G_EXCEPTION_ERROR;
618
619 END IF;
620
621
622 ---- Check in the accounting tables that there is no un-transferred records
623 -- Bug 7634078. As per vphanse & mmittal, no need to check untranferred records
624 -- commenting below code.
625 /*
626 l_dummy := NULL;
627 OPEN aeh_csr(p_period_rec.set_of_books_id);
628 FETCH aeh_csr INTO l_dummy;
629
630 IF (l_dummy = '1') THEN
631 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
632 p_msg_name => 'OKL_RUN_GL_TRANSFER',
633 p_token1 => 'PERIOD_NAME',
634 p_token1_value => p_period_rec.period_name);
635 CLOSE aeh_csr;
636 RAISE OKL_API.G_EXCEPTION_ERROR;
637 END IF;
638 CLOSE aeh_csr;
639 */
640 END IF;
641
642 ---- All validations done. Now update the GL period Status table.
643
644 UPDATE GL_PERIOD_STATUSES
645 SET CLOSING_STATUS = p_period_rec.closing_status,
646 LAST_UPDATE_DATE = SYSDATE,
647 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
648 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
649 WHERE APPLICATION_ID = 540
650 AND LEDGER_ID = p_period_rec.set_of_books_id
651 AND PERIOD_NAME = p_period_rec.period_name;
652
653
654 EXCEPTION
655
656 WHEN OKL_API.G_EXCEPTION_ERROR THEN
657 x_return_status := OKL_API.G_RET_STS_ERROR;
658
659 WHEN OTHERS THEN
660 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
661
662 END UPDATE_PERD_ROW;
663
664 END OKL_PERD_STATUS_PVT ;
[Home] [Help]