[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_PRD_CONTROL_MEDIATOR
Source
1 PACKAGE BODY INV_MGD_PRD_CONTROL_MEDIATOR AS
2 /* $Header: INVMOCLB.pls 120.6.12010000.3 2009/01/13 11:54:23 mpuranik ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVMOCLB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Specification of INV_MGD_PRD_CONTROL_MEDIATOR |
13 --| |
14 --| HISTORY |
15 --| 25-Sep-2000 rajkrish Created |
16 --| 24-Jan-2001 rajkrish Updated Request |
17 --| 14-May-2001 vjavli updated for performance tuning |
18 --| 14-Jan-2002 vjavli period final date for the origin |
19 --| 14-Jan-2002 vjavli process the origin first |
20 --| 09-May-2002 vjavli leap frog from 115.24 115.27 |
21 --| 09-May-2002 vjavli Bug#2230141 fix done for 115.26 |
22 --| has to be coded again |
23 --| 22-May-2002 vjavli Bug#2386091 fix |
24 --| get_pending_tcount added with |
25 --| parameter pending_ship |
26 --| 27-May-2002 vjavli Bug#2384953 fix to open the |
27 --| period for the hierarchy origin |
28 --| 03-June-2002 vjavli Bug#2395514 fix: removed |
29 --| pending_ship parameter |
30 --| this is to provide a customer fix|
31 --| with leap frog version of core |
32 --| inv file: INVTTGPB.pls |
33 --| 21-Nov-2002 vma Added back pending_ship parameter|
34 --| for compactibility. |
35 --| Performance: modify code to print|
36 --| to log only if debug profile |
37 --| option is enabled |
38 --| 24-Nov-2002 tsimmond UTF8: changed l_org_name and org|
39 --| VARCHAR2(240) |
40 --| 16-jan-2003 vjavli Bug#2754073 fix: get_pending_tcount has |
41 --| additional parameter x_released_work_orders|
42 --| introduced in invoked procedure INVTTGP4 |
43 --| 18-Jul-2003 vjavli period close enhancement: INVTTGP4 is |
44 --| replaced with CST_AccountingPeriod_PUB with|
45 --| all the procedure parameters |
46 --| 18-Jul-2003 vjavli p_api_version assigned l_api_version |
47 --| 09-Sep-2003 vjavli NOCOPY added according to pl/sql standard |
48 --| 18-Sep-2003 vjavli verify_periodclose: verifications added |
49 --| as in the inventory accounting period form |
50 --| lrgos_report_rec:reason width to 255 |
51 --| p_closing_end_date assigned with schedule |
52 --| close date |
53 --| 28-Jan-2004 nkamaraj Validation for the calendar and ChartOf |
54 --| Accounts is added.fixed the incorrect |
55 --| opening of no of periods.Please refer |
56 --| 3296392 and 3263991 |
57 --| 08-APR-2004 nesoni Bug 3555234. Error/Exceptions should be |
58 --| logged irrespective of FND Debug Enabled. |
59 --| 24-APR-2004 nesoni Bug 3590042. Initialization of variable |
60 --| l_verify_flag is done in Close Period |
61 --| control block. |
62 --| 25-MAY-2004 nesoni Bug 3638081. org_organization_definitions|
63 --| view is replaced with its definition to |
64 --| improve performance. |
65 --| 09-Nov-2004 nesoni One validation is introduced during bug |
66 --| #3904824. User should not be able to close |
67 --| a period if its scheduled close date is |
68 --| after current date. Earlier this validation|
69 --| was applicable while submiting a request. |
70 --| Close Period lov used to show only those |
71 --| periods which were open and their scheduled|
72 --| close date was prior or equal to current |
73 --| date. This validation was not allowing user|
74 --| to schedule a close period request for |
75 --| future date. For this reason, this |
76 --| validation is transferred at backend. |
77 --| 24-Jan-2005 nesoni Sleep time is introduced between execution |
78 --| of Close Accounting Period concurrent |
79 --| program status checking query. Bug 3999140 |
80 --| 05-Jul-2005 nesoni Code modified for bug 4457006 to remove |
81 --| scheduling check and Period end date |
82 --| validation. |
83 --| 09-Sep-2005 myerrams Modified the call to close_period procedure|
84 --| as per procedure signature in |
85 --| CST_AccountingPeriod_PUB. Bug: 4599201 |
86 --+=======================================================================+
87 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_PRD_CONTROL_MEDIATOR';
88 g_log_level NUMBER := NULL;
89
90 /* Variable g_log_mode is commented becasue it is no more in use.
91 It was done during bug:3638081 fix to resolve GSCC warning
92 g_log_mode VARCHAR2(3) := 'OFF'; -- possible values: OFF,SQL,SRS
93 */
94 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
95
96 TYPE LRGOS_REPORT_REC IS RECORD
97 ( org VARCHAR2(240)
98 , period VARCHAR2(30)
99 , status VARCHAR2(30)
100 , reason VARCHAR2(255)
101 , request_id NUMBER
102 , closed VARCHAR2(1)
103 , request_status VARCHAR2(30)
104 , acct_period_id NUMBER
105 );
106
107
108 TYPE LRGOS_REPORT_TABLE IS TABLE OF LRGOS_REPORT_REC
109 INDEX BY BINARY_INTEGER;
110
111 G_LOG_REPORT_TABLE LRGOS_REPORT_TABLE ;
112
113 --===================
114 -- PROCEDURES AND FUNCTIONS
115 --===================
116
117
118 --========================================================================
119 -- PROCEDURE : GET_CLOSED_STATUS
120
121
122 -- COMMENT : Returns the status of the Concurrent program
123 --=======================================================================
124 FUNCTION GET_CLOSED_STATUS
125 ( p_acct_period_id IN NUMBER
126 ,p_org IN VARCHAR2)
127 RETURN VARCHAR2
128
129 IS
130
131 l_open_flag VARCHAR2(1);
132 l_return_flag VARCHAR2(1);
133
134 BEGIN
135
136 --- INV_ORGHIERARCHY_PVT.Log
137 --- (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
138 --- ,'> GET_CLOSED_STATUS for acct_period ' || p_acct_period_id
139 --- || ' Org Name:' || p_org
140 --- );
141
142 BEGIN
143 /* Bug: 3638081. Following query is modifed and view org_organization_definitions is replaced with view HR_ORGANIZATION_UNITS
144 SELECT
145 OPEN_FLAG
146 INTO
147 l_open_flag
148 FROM
149 org_acct_periods oop
150 ,org_organization_definitions ood
151 WHERE oop.acct_period_id = p_acct_period_id
152 AND oop.organization_id = ood.organization_id
153 AND ood.organization_name = p_org;
154 */
155
156 SELECT
157 OPEN_FLAG
158 INTO
159 l_open_flag
160 FROM
161 org_acct_periods oop
162 ,HR_ORGANIZATION_UNITS HOU
163 WHERE oop.acct_period_id = p_acct_period_id
164 AND oop.organization_id = HOU.organization_id
165 AND HOU.name = p_org;
166
167 EXCEPTION
168 WHEN NO_DATA_FOUND
169 THEN
170 l_open_flag := 'Y' ;
171
172 END ;
173
174 IF NVL(l_open_flag,'Y') = 'N'
175 THEN
176 l_return_flag := 'Y' ;
177 ELSE
178 l_return_flag := 'N' ;
179 END IF;
180
181
182 --- INV_ORGHIERARCHY_PVT.Log
183 --- (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
184 --- ,'< GET_CLOSED_STATUS with flag ' || l_return_flag
185 --- );
186
187 RETURN l_return_flag;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 /* This executable is used by concurrent program so
192 Error/Exception logging should not depend on
193 FND Debug Enabled profile otpion. Bug: 3555234
194 IF G_DEBUG = 'Y' THEN
195 */
196 INV_ORGHIERARCHY_PVT.Log( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
197 , 'SQLERRM '|| SQLERRM);
198 --END IF;
199
200 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
201 THEN
202 FND_MSG_PUB.Add_Exc_Msg
203 ( G_PKG_NAME
204 , ' GET_CLOSED_STATUS'
205 );
206 END IF;
207 ROLLBACK;
208 RAISE;
209 END ;
210
211
212 --========================================================================
213 -- PROCEDURE : GET_OPEN_REQUESTS_COUNT
214
215
216 -- COMMENT : Returns the number of Requests still running
217 --=======================================================================
218 FUNCTION GET_OPEN_REQUESTS_COUNT
219 RETURN NUMBER
220 IS
221
222 l_count NUMBER := 0 ;
223 l_dev_phase VARCHAR2(1);
224 l_request_status VARCHAR2(30);
225
226 -- Cursor to obtain the request status
227 CURSOR c_check_request_status(c_request_id NUMBER)
228 IS
229 SELECT phase_code
230 FROM FND_CONCURRENT_REQUESTS
231 WHERE request_id = c_request_id;
232
233 BEGIN
234 -- Loop through the PL/SQL table and verify the
235 -- status of the programs that are still not
236 -- COMPLETE status
237
238 ---INV_ORGHIERARCHY_PVT.Log
239 ---(INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
240 --- ,'> GET_OPEN_REQUESTS_COUNT '
241 ---);
242
243 FOR I IN 1 .. G_LOG_REPORT_TABLE.COUNT
244 LOOP
245 IF G_LOG_REPORT_TABLE(I).request_id is NOT NULL
246 THEN
247 IF NVL(G_LOG_REPORT_TABLE(I).request_status , 'X')
248 <> 'COMPLETE'
249 THEN
250 --- INV_ORGHIERARCHY_PVT.Log
251 --- (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
252 --- ,' Checking request status: '||
253 --- G_LOG_REPORT_TABLE(I).request_id
254 --- );
255
256 OPEN c_check_request_status(G_LOG_REPORT_TABLE(I).request_id);
257
258 FETCH c_check_request_status
259 INTO l_dev_phase;
260
261 CLOSE c_check_request_status;
262
263 -- assign request status with meaningful constants
264 IF (l_dev_phase = 'R') THEN
265 l_request_status := 'RUNNING';
266 ELSIF (l_dev_phase = 'P') THEN
267 l_request_status := 'PENDING';
268 ELSIF (l_dev_phase = 'I') THEN
269 l_request_status := 'INACTIVE';
270 ELSIF (l_dev_phase = 'C') THEN
271 l_request_status := 'COMPLETE';
272 END IF;
273
274
275 -- INV_ORGHIERARCHY_PVT.Log
276 -- (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
277 -- ,' Out with status: '|| l_request_status
278 -- );
279
280 IF l_request_status = 'COMPLETE'
281 THEN
282 G_LOG_REPORT_TABLE(I).request_status := l_request_status;
283 G_LOG_REPORT_TABLE(I).closed :=
284 GET_CLOSED_STATUS
285 ( p_acct_period_id =>
286 G_LOG_REPORT_TABLE(I).acct_period_id
287 ,p_org => G_LOG_REPORT_TABLE(I).org
288 );
289
290 ELSE
291 G_LOG_REPORT_TABLE(I).request_status := NULL;
292 l_count := NVL(l_count,0) + 1;
293 END IF;
294 END IF;
295 END IF;
296
297 IF NVL(G_LOG_REPORT_TABLE(I).closed,'N') <> 'Y'
298 THEN
299 IF G_LOG_REPORT_TABLE(I).request_id IS NOT NULL
300 THEN
301 G_LOG_REPORT_TABLE(I).reason := 'Review Request :'||
302 G_LOG_REPORT_TABLE(I).request_id ;
303 G_LOG_REPORT_TABLE(I).status := 'Processing' ;
304 END IF;
305 ELSE
306 G_LOG_REPORT_TABLE(I).reason := NULL ;
307 G_LOG_REPORT_TABLE(I).status := 'Closed' ;
308 END IF;
309 END LOOP ;
310
311 ---INV_ORGHIERARCHY_PVT.Log
312 ---(INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
313 ---,'< GET_OPEN_REQUESTS_COUNT with count '|| l_count
314 ---);
315
316 RETURN l_count ;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 /* This executable is used by concurrent program so
321 Error/Exception logging should not depend on
322 FND Debug Enabled profile otpion. Bug: 3555234
323 IF G_DEBUG = 'Y' THEN
324 */
325 INV_ORGHIERARCHY_PVT.Log( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
326 , 'SQLERRM '|| SQLERRM) ;
327 --END IF;
328
329 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
330 THEN
331 FND_MSG_PUB.Add_Exc_Msg
332 ( G_PKG_NAME
333 , ' GET_OPEN_REQUESTS_COUNT'
334 );
335 END IF;
336 ROLLBACK;
337 RAISE;
338
339
340 END GET_OPEN_REQUESTS_COUNT;
341
342
343
344 --========================================================================
345 -- PROCEDURE : ADD_ITEM
346
347
348 -- COMMENT : Includes a record into the PL/SQL report table
349 --=======================================================================
350 PROCEDURE ADD_ITEM
351 ( p_org IN VARCHAR2
352 , p_period IN VARCHAR2
353 , p_status IN VARCHAR2
354 , p_reason IN VARCHAR2
355 , p_request_id IN NUMBER
356 , p_closed IN VARCHAR2
357 , p_acct_period_id IN NUMBER
358 )
359
360 IS
361
362 I NUMBER ;
363
364 BEGIN
365
366 ---INV_ORGHIERARCHY_PVT.Log
367 ---(INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
368 --- ,'> ADD_ITEM for period ' || p_period
369 ---);
370
371 I := NVL(G_LOG_REPORT_TABLE.COUNT, 0 ) + 1 ;
372
373 ---INV_ORGHIERARCHY_PVT.Log
374 ---(INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
375 --- ,' No of records in the report table = '|| i
376 ---);
377
378
379 G_LOG_REPORT_TABLE(I).org := p_org ;
380 G_LOG_REPORT_TABLE(I).period := p_period ;
381 G_LOG_REPORT_TABLE(I).status := p_status ;
382 G_LOG_REPORT_TABLE(I).reason := p_reason ;
383 G_LOG_REPORT_TABLE(I).request_id := p_request_id ;
384 G_LOG_REPORT_TABLE(I).closed := p_closed ;
385 G_LOG_REPORT_TABLE(I).acct_period_id := p_acct_period_id ;
386
387 I := NULL ;
388
389
390 ---INV_ORGHIERARCHY_PVT.Log
391 ---(INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
392 --- ,'< ADD_ITEM '
393 ---);
394
395 END ADD_ITEM ;
396
397
398 --========================================================================
399 -- PROCEDURE : PRINT_REPORT
400
401
402 -- COMMENT : Prints the report from the PL/SQL report table
403 --=======================================================================
404 PROCEDURE PRINT_REPORT
405 IS
406
407 l_space VARCHAR2(10) ;
408
409 BEGIN
410 /* Variable initialization is shifted from declaration section. It was done during bug:3638081 fix to resolve GSCC warning */
411 l_space := ' ' ;
412 IF G_DEBUG = 'Y' THEN
413
414 INV_ORGHIERARCHY_PVT.Log
415 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
416 ,'> PRINT_REPORT '
417 );
418
419 INV_ORGHIERARCHY_PVT.Log
420 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
421 ,' '
422 );
423 END IF;
424
425 /* Bug: 3555234
426 Following code block is modified to change log level. Replaced
427 G_LOG_STATEMENT wtih G_LOG_PRINT becasue basic report will be printed
428 irrespective of FND Debug Enabled profile option.
429 */
430 INV_ORGHIERARCHY_PVT.Log
431 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
432 ,' '
433 );
434
435 INV_ORGHIERARCHY_PVT.Log
436 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
437 ,'************** Begin Report **************************** '
438 );
439
440 FOR I IN G_LOG_REPORT_TABLE.FIRST .. G_LOG_REPORT_TABLE.LAST
441 LOOP
442 IF G_LOG_REPORT_TABLE(I).org IS NOT NULL
443 THEN
444 INV_ORGHIERARCHY_PVT.Log
445 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
446 ,' '
447 );
448
449 INV_ORGHIERARCHY_PVT.Log
450 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
451 ,'.................................................. '
452 );
453
454 INV_ORGHIERARCHY_PVT.Log
455 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
456 ,'ORGANIZATION : ' || G_LOG_REPORT_TABLE(I).org
457 );
458
459 INV_ORGHIERARCHY_PVT.Log
460 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
461 ,' '
462 );
463
464 ELSE
465 INV_ORGHIERARCHY_PVT.Log
466 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
467 , G_LOG_REPORT_TABLE(I).period || l_space ||
468 G_LOG_REPORT_TABLE(I).status
469 || l_space || G_LOG_REPORT_TABLE(I).reason
470 );
471
472 END IF;
473 END LOOP;
474
475 INV_ORGHIERARCHY_PVT.Log
476 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
477 ,' '
478 );
479
480 INV_ORGHIERARCHY_PVT.Log
481 (INV_ORGHIERARCHY_PVT.G_LOG_PRINT
482 ,'************** End Report **************************** '
483 );
484
485 INV_ORGHIERARCHY_PVT.Log
486 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
487 ,' '
488 );
489
490 IF G_DEBUG = 'Y' THEN
491
492 INV_ORGHIERARCHY_PVT.Log
493 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
494 ,'< PRINT_REPORT '
495 );
496
497 END IF;
498 END PRINT_REPORT ;
499
500
501 --========================================================================
502 -- PROCEDURE : GET_MAX_OPEN_PERIOD
503
504
505 -- COMMENT : Returns the Max Open period for the Org
506
507 --=======================================================================
508 PROCEDURE GET_MAX_OPEN_PERIOD
509 ( p_org_id IN NUMBER
510 , p_period_set_name IN VARCHAR2
511 , p_period_type IN VARCHAR2
512 , x_period_start_date OUT NOCOPY DATE
513 , x_period_end_date OUT NOCOPY DATE
514 , x_period_name OUT NOCOPY VARCHAR2
515 )
516 IS
517
518 l_max_period_id NUMBER;
519
520 BEGIN
521
522 IF G_DEBUG = 'Y' THEN
523 INV_ORGHIERARCHY_PVT.Log
524 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
525 ,'> GET_MAX_OPEN_PERIOD '
526 );
527 END IF;
528
529 -- The Maximum open period is validated and is used
530 -- for calculating the starting period
531
532 BEGIN
533 SELECT
534 MAX( acct_period_id )
535 INTO
536 l_max_period_id
537 FROM
538 org_acct_periods orgp
539 WHERE orgp.organization_id = p_org_id
540 AND orgp.period_set_name = p_period_set_name
541 AND orgp.open_flag = 'Y' ;
542
543 IF G_DEBUG = 'Y' THEN
544 INV_ORGHIERARCHY_PVT.Log
545 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
546 ,' Max Open period num ' || l_max_period_id
547 );
548 END IF;
549
550 EXCEPTION
551 WHEN NO_DATA_FOUND
552 THEN
553 l_max_period_id := NULL;
554
555 IF G_DEBUG = 'Y' THEN
556 INV_ORGHIERARCHY_PVT.Log
557 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
558 ,' Open period not found '
559 );
560 END IF;
561 END ;
562
563 IF l_max_period_id IS NOT NULL
564 THEN
565 BEGIN
566 SELECT
567 glp.period_name
568 , glp.start_date
569 , glp.end_date
570 INTO
571 x_period_name
572 , x_period_start_date
573 , x_period_end_date
574 FROM
575 gl_periods glp
576 , org_acct_periods orgp
577 WHERE glp.period_name = orgp.period_name
578 AND glp.period_set_name = p_period_set_name
579 AND glp.period_type = p_period_type
580 AND orgp.acct_period_id = l_max_period_id
581 AND orgp.organization_id = p_org_id;
582
583 IF G_DEBUG = 'Y' THEN
584 INV_ORGHIERARCHY_PVT.Log
585 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
586 ,' gl period found '
587 );
588 END IF;
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN
592
593 IF G_DEBUG = 'Y' THEN
594 INV_ORGHIERARCHY_PVT.Log
595 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
596 ,' gl period not found '
597 );
598 END IF;
599
600 x_period_name := NULL;
601 x_period_start_date := NULL;
602 x_period_end_date := NULL;
603 END ;
604
605 ELSE
606
607 IF G_DEBUG = 'Y' THEN
608 INV_ORGHIERARCHY_PVT.Log
609 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
610 ,' gl period not selcted '
611 );
612 END IF;
613 x_period_name := NULL;
614 x_period_start_date := NULL;
615 x_period_end_date := NULL;
616
617 END IF;
618
619 IF G_DEBUG = 'Y' THEN
620 INV_ORGHIERARCHY_PVT.Log
621 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
622 ,'< GET_MAX_OPEN_PERIOD '
623 );
624 END IF;
625
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 /* This executable is used by concurrent program so
630 Error/Exception logging should not depend on
631 FND Debug Enabled profile otpion. Bug: 3555234
632 IF G_DEBUG = 'Y' THEN
633 */
634 INV_ORGHIERARCHY_PVT.Log( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
635 , 'SQLERRM '|| SQLERRM) ;
636 --END IF;
637
638 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
639 THEN
640 FND_MSG_PUB.Add_Exc_Msg
641 ( G_PKG_NAME
642 , ' GET_MAX_OPEN_PERIOD'
643 );
644 END IF;
645 ROLLBACK;
646 RAISE;
647
648 END GET_MAX_OPEN_PERIOD;
649
650
651 --========================================================================
652 -- PROCEDURE : GET_MIN_OPEN_PERIOD
653
654
655 -- COMMENT : Returns the Min Open period for the Org
656
657 --=======================================================================
658 PROCEDURE GET_MIN_OPEN_PERIOD
659 ( p_org_id IN NUMBER
660 , p_period_set_name IN VARCHAR2
661 , p_period_type IN VARCHAR2
662 , x_period_start_date OUT NOCOPY DATE
663 , x_period_end_date OUT NOCOPY DATE
664 , x_period_name OUT NOCOPY VARCHAR2
665 )
666 IS
667
668 l_min_period_id NUMBER;
669
670 BEGIN
671
672 IF G_DEBUG = 'Y' THEN
673 INV_ORGHIERARCHY_PVT.Log
674 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
675 ,'> GET_MIN_OPEN_PERIOD'
676 );
677 END IF;
678
679 BEGIN
680 SELECT
681 MIN( acct_period_id )
682 INTO
683 l_min_period_id
684 FROM
685 org_acct_periods orgp
686 WHERE orgp.organization_id = p_org_id
687 AND orgp.period_set_name = p_period_set_name ;
688
689 IF G_DEBUG = 'Y' THEN
690 INV_ORGHIERARCHY_PVT.Log
691 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
692 ,' Min Open period num ' || l_min_period_id
693 );
694 END IF;
695
696 EXCEPTION
697 WHEN NO_DATA_FOUND THEN
698 l_min_period_id := NULL;
699
700 IF G_DEBUG = 'Y' THEN
701 INV_ORGHIERARCHY_PVT.Log
702 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
703 ,' Open period not found '
704 );
705 END IF;
706 END ;
707
708 IF l_min_period_id IS NOT NULL
709 THEN
710 BEGIN
711 SELECT
712 glp.period_name
713 , glp.start_date
714 , glp.end_date
715 INTO
716 x_period_name
717 , x_period_start_date
718 , x_period_end_date
719 FROM
720 gl_periods glp
721 , org_acct_periods orgp
722 WHERE glp.period_name = orgp.period_name
723 AND glp.period_set_name = p_period_set_name
724 AND glp.period_type = p_period_type
725 AND orgp.acct_period_id = l_min_period_id
726 AND orgp.organization_id = p_org_id;
727
728 IF G_DEBUG = 'Y' THEN
729 INV_ORGHIERARCHY_PVT.Log
730 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
731 ,' gl period found '
732 );
733 END IF;
734
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737
738 IF G_DEBUG = 'Y' THEN
739 INV_ORGHIERARCHY_PVT.Log
740 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
741 ,' gl period not found '
742 );
743 END IF;
744 x_period_name := NULL;
745 x_period_start_date := NULL;
746 x_period_end_date := NULL;
747 END ;
748
749 ELSE
750 IF G_DEBUG = 'Y' THEN
751 INV_ORGHIERARCHY_PVT.Log
752 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
753 ,' gl period not selcted '
754 );
755 END IF;
756 x_period_name := NULL;
757 x_period_start_date := NULL;
758 x_period_end_date := NULL;
759
760 END IF;
761
762
763 IF G_DEBUG = 'Y' THEN
764 INV_ORGHIERARCHY_PVT.Log
765 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
766 ,'< GET_MIN_OPEN_PERIOD '
767 );
768 END IF;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 /* This executable is used by concurrent program so
773 Error/Exception logging should not depend on
774 FND Debug Enabled profile otpion. Bug: 3555234
775 IF G_DEBUG = 'Y' THEN
776 */
777 INV_ORGHIERARCHY_PVT.Log( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
778 , 'SQLERRM '|| SQLERRM) ;
779 --END IF;
780
781 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
782 THEN
783 FND_MSG_PUB.Add_Exc_Msg
784 ( G_PKG_NAME
785 , 'GET_MIN_OPEN_PERIOD'
786 );
787 END IF;
788 ROLLBACK;
789 RAISE;
790
791 END GET_MIN_OPEN_PERIOD ;
792
793
794
795 --========================================================================
796 -- PROCEDURE : Period_Control
797
798
799 -- COMMENT : This is the Wrapper program mediator that invokes the
800 -- Inventory API for Open and Close periods
801
802 --=======================================================================
803 PROCEDURE Period_Control
804 ( x_retcode OUT NOCOPY VARCHAR2
805 , x_errbuff OUT NOCOPY VARCHAR2
806 , p_org_hierarchy_origin IN NUMBER
807 , p_org_hierarchy_id IN NUMBER
808 , p_close_period_name IN VARCHAR2
809 , p_close_if_res_recmd IN VARCHAR2
810 , p_open_period_count IN NUMBER
811 , p_open_or_close_flag IN VARCHAR2
812 , p_requests_count IN NUMBER
813 )
814 IS
815
816 l_org_code_list INV_ORGHIERARCHY_PVT.OrgID_tbl_type;
817
818 l_orgid hr_organization_units.organization_id%TYPE;
819 l_org_name VARCHAR2(240) := NULL;
820
821
822 l_index BINARY_INTEGER;
823
824 l_login_id NUMBER;
825 l_user_id NUMBER;
826
827 l_requests_count NUMBER;
828
829 l_period_set_name VARCHAR2(30);
830 l_sob_period_type VARCHAR2(30);
831
832 l_final_period_name VARCHAR2(30);
833 l_period_final_start_date DATE;
834 l_period_final_end_date DATE;
835 l_cursor_final_end_date DATE;
836
837 l_wip_failed BOOLEAN;
838 l_close_failed BOOLEAN;
839 l_download_failed BOOLEAN;
840 l_unprocessed_txns BOOLEAN; --myerrams, Bug:4599201
841 l_rec_rpt_launch_failed BOOLEAN; --myerrams, Bug:4599201
842 l_req_id NUMBER;
843 l_list_count NUMBER:= 0;
844 l_last_scheduled_close_date DATE;
845 l_org_from_date DATE;
846
847 l_prior_period_open BOOLEAN;
848 l_new_acct_period_id NUMBER;
849 l_duplicate_open_period BOOLEAN;
850 l_commit_complete BOOLEAN;
851 l_return_status VARCHAR2(1);
852 l_api_version NUMBER := 1.0;
853
854 l_open_period_exists BOOLEAN;
855 l_proper_order BOOLEAN;
856 l_end_date_is_past BOOLEAN;
857 l_download_in_process BOOLEAN;
858 l_prompt_to_reclose BOOLEAN;
859
860 l_date_from DATE;
861 l_count NUMBER:= 0 ;
862
863 l_min_start_date DATE;
864 l_min_end_date DATE;
865 l_min_period VARCHAR2(30);
866
867 l_pend_receiving INTEGER;
868 l_unproc_matl INTEGER;
869 l_pend_matl INTEGER;
870 l_uncost_matl INTEGER;
871 l_pend_move INTEGER;
872 l_pend_WIP_cost INTEGER;
873 l_uncost_wsm INTEGER;
874 l_pending_wsm INTEGER;
875 l_pending_ship INTEGER;
876 l_pending_lcm INTEGER;
877
878 -- Bug#2754073 fix to include variable for the parameter
879 -- x_released_work_orders
880 l_released_work_orders INTEGER;
881
882 -- Following code line which was introduced during bug 3904824
883 -- has been commented during fix 4457006 because 11.5.10 CU2 onwards, scheduling can be
884 -- done for any date and user can close Period on any date they wish.So no new
885 -- exception is needed.
886 -- A new varaible l_legal_entity is defined to collect legal entity id.
887 -- New variable l_le_sysdate represents sysdate in legal entity timezone.
888 -- New variable l_period_end_date represents period end date in server timezone.
889 -- New variable l_reamining_hours represents remaining hours for period close date.
890
891
892 --Bug #3904824
893 --l_close_period_before_sch_dt EXCEPTION;
894
895 l_legal_entity NUMBER;
896 l_period_end_date DATE;
897 l_le_sysdate DATE;
898 l_hours_remaining NUMBER;
899
900 /* A new variable to define sleep time is introduced. Bug: 3999140 */
901 l_sleep_time NUMBER := 15;
902
903 -- Bug #3263991 and 3296392
904 l_hierarchy_validation EXCEPTION;
905 l_property_flag VARCHAR2(1);
906 l_hierarchy_name VARCHAR2(30);
907 l_property VARCHAR2(100);
908
909 -- Bug # 5078841 to generate warning if periods of closed when pending transactions' resolution is recommended
910 l_closed_if_res_recmd NUMBER := 0;
911
912 l_allow_close varchar2(240);
913
914 l_max_open_period_name ORG_ACCT_PERIODS.PERIOD_NAME%TYPE;
915 l_max_open_period_start_date DATE;
916 l_max_open_period_end_date DATE;
917 l_max_period_start_date DATE;
918
919
920 CURSOR c_org_name(c_org_id NUMBER) IS
921 SELECT
922 name
923 , date_from
924 FROM
925 HR_organization_units
926 WHERE ORGANIZATION_ID = c_org_id;
927
928 -- Select the periods from GL_PERIODS
929 -- that are eligible to be
930 -- opened for a given Org
931
932 CURSOR c_gl_period_future(c_org_id NUMBER) IS
933 SELECT
934 glp.PERIOD_SET_NAME open_period_set_name
935 , glp.PERIOD_NAME open_period_name
936 , glp.START_DATE period_start_date
937 , glp.END_DATE period_end_date
938 , glp.PERIOD_TYPE acct_period_type
939 , glp.PERIOD_YEAR open_period_year
940 , glp.PERIOD_NUM open_period_num
941 FROM
942 GL_PERIODS glp
943 WHERE glp.ADJUSTMENT_PERIOD_FLAG = 'N'
944 AND glp.period_type = l_sob_period_type
945 AND glp.PERIOD_SET_NAME = l_period_set_name
946 AND glp.PERIOD_NAME NOT IN
947 ( SELECT OAP.PERIOD_NAME
948 FROM ORG_ACCT_PERIODS OAP
949 WHERE OAP.PERIOD_SET_NAME = glp.PERIOD_SET_NAME
950 AND OAP.PERIOD_NAME = glp.PERIOD_NAME
951 AND OAP.organization_id = c_org_id
952 )
953 AND glp.end_date <= NVL( l_cursor_final_end_date + 1, glp.end_date )
954 AND glp.end_date >= l_org_from_date
955 ORDER BY glp.start_date ;
956
957 l_gl_period_future c_gl_period_future%ROWTYPE ;
958
959
960 -- Select the periods from GL_PERIODS
961 -- that are eligible to be
962 -- Closed for a given Org
963
964 CURSOR c_org_acct_periods_open IS
965 SELECT
966 orgp.rowid closing_rowid
967 , orgp.ACCT_PERIOD_ID closing_acct_period_id
968 , orgp.ORGANIZATION_ID organization_id
969 , orgp.period_start_date period_start_date
970 , orgp.PERIOD_CLOSE_DATE period_close_date
971 , orgp.SCHEDULE_CLOSE_DATE schedule_close_date
972 , orgp.PERIOD_YEAR open_period_year
973 , orgp.PERIOD_NUM open_period_num
974 , orgp.PERIOD_NAME open_period_name
975 , orgp.open_flag open_flag
976 FROM
977 org_acct_periods orgp
978 WHERE orgp.period_name = p_close_period_name
979 AND orgp.organization_id = l_orgid
980 AND orgp.period_set_name = l_period_set_name ;
981
982
983 l_org_acct_periods_val_open c_org_acct_periods_open%ROWTYPE ;
984
985 -- log error message
986 l_error_msg VARCHAR2(255);
987 --Initialization of variable l_verify_flag is removed. Bug: 3590042
988 -- Verify flag for verify_periodclose
989 l_verify_flag VARCHAR2(1);
990
991 BEGIN
992
993 IF G_DEBUG = 'Y' THEN
994 INV_ORGHIERARCHY_PVT.Log
995 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
996 ,'> INV_MGD_PRD_CONTROL_MEDIATOR.Period_Control '
997 );
998 END IF;
999 -- Following validation block which was introduced during bug 3904824
1000 -- has been commented during fix 4457006 because 11.5.10 CU2 onwards, scheduling can be
1001 -- done for any date and user can close Period on any date they wish.
1002
1003 -- Following validation block is introduced during bug 3904824.
1004 -- User should not be able to close a period if its scheduled close date is after current date.
1005 -- Earlier this validation was applicable while submiting a request. Close Period lov used to
1006 -- show only those period which are open and their scheduled close date is prior or equal to
1007 -- current date. This validation was not allowing user to schedule a close period request for future date.
1008 -- For this reason, this validation is transferred at backend.
1009 -- IF p_open_or_close_flag = 'C' THEN
1010 -- SELECT count(*)
1011 -- INTO l_count
1012 -- FROM ORG_ACCT_PERIODS
1013 -- WHERE SCHEDULE_CLOSE_DATE > SYSDATE
1014 -- AND PERIOD_NAME = p_close_period_name
1015 -- AND ORGANIZATION_ID = p_org_hierarchy_origin;
1016
1017 -- IF l_count > 0 THEN
1018 -- FND_MESSAGE.SET_NAME('INV', 'INV_SCHE_CLOSE_DATE_NOT_PASSED');
1019 -- x_errbuff := SUBSTR(FND_MESSAGE.Get, 1,255);
1020
1021 -- RAISE l_close_period_before_sch_dt;
1022 -- END IF;
1023 -- END IF;
1024
1025
1026 -- The Open and Close poeriod solution is acheived by using
1027 -- the underlying Inventory API's.
1028
1029 -- A report PL/SQL table is maintained for the sttaus to be
1030 -- printed for each period for each Org.
1031 -- The same table is also used to manipulate the
1032 -- number of requests running during the Close period program
1033
1034 -- The API's return status details the process outcome , which is
1035 -- inserted into the PL/SQL table and later printed as Log report
1036
1037 -- The periods for Closing are selected from the ORG_ACCT_PERIODS
1038 -- and the periods to Open are retreived from GL_PERIODS
1039
1040 G_LOG_REPORT_TABLE.DELETE ;
1041
1042 l_requests_count := p_requests_count ;
1043
1044 x_retcode := RETCODE_SUCCESS;
1045
1046 INV_ORGHIERARCHY_PVT.get_organization_List (
1047 p_hierarchy_id => p_org_hierarchy_id
1048 , p_origin_org_id => p_org_hierarchy_origin
1049 , x_org_id_tbl => l_org_code_list
1050 , p_include_origin => 'Y'
1051 );
1052
1053 IF G_DEBUG = 'Y' THEN
1054 INV_ORGHIERARCHY_PVT.Log
1055 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1056 ,' Out of Org List capture with count '|| l_org_code_list.COUNT
1057 );
1058 END IF;
1059
1060 -- Bug : 3296392 - Validate for the same Calendar of all orgs in hierarchy
1061
1062 l_property_flag := INV_ORGHIERARCHY_PVT.
1063 validate_property(l_org_code_list, 'CALENDAR');
1064
1065 IF G_DEBUG = 'Y' THEN
1066 INV_ORGHIERARCHY_PVT.Log
1067 ( INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1068 ,'Property Flag:' || l_property_flag );
1069 END IF;
1070
1071 IF l_property_flag = 'N' THEN
1072 -- get hierarchy name
1073 SELECT name
1074 INTO l_hierarchy_name
1075 FROM per_organization_structures
1076 WHERE organization_structure_id = p_org_hierarchy_id;
1077
1078 -- get the hierarchy property text
1079 SELECT meaning
1080 INTO l_property
1081 FROM mfg_lookups
1082 WHERE lookup_type = 'INV_MGD_HIER_PROPERTY_TYPE'
1083 AND lookup_code = 2;
1084
1085 -- raise hiearchy validation failure
1086 -- Set the message, tokens
1087 FND_MESSAGE.set_name('INV', 'INV_MGD_HIER_INVALID_PROPERTY');
1088 FND_MESSAGE.set_token('HIERARCHY', l_hierarchy_name);
1089 FND_MESSAGE.set_token('PROPERTY', l_property);
1090 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1091
1092 RAISE l_hierarchy_validation;
1093
1094 END IF;
1095 -- Bug : 3296392 - Validate for the same ChartOfAccounts of all orgs in hierarchy
1096
1097 l_property_flag := INV_ORGHIERARCHY_PVT.
1098 validate_property(l_org_code_list, 'CHART_OF_ACCOUNTS');
1099
1100 IF G_DEBUG = 'Y' THEN
1101 INV_ORGHIERARCHY_PVT.Log
1102 ( INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1103 ,'Property Flag:' || l_property_flag );
1104 END IF;
1105
1106 IF l_property_flag = 'N' THEN
1107 -- get hierarchy name
1108 SELECT name
1109 INTO l_hierarchy_name
1110 FROM per_organization_structures
1111 WHERE organization_structure_id = p_org_hierarchy_id;
1112
1113 -- get the hierarchy property text
1114 SELECT meaning
1115 INTO l_property
1116 FROM mfg_lookups
1117 WHERE lookup_type = 'INV_MGD_HIER_PROPERTY_TYPE'
1118 AND lookup_code = 3;
1119
1120 -- raise hiearchy validation failure
1121 -- Set the message, tokens
1122 FND_MESSAGE.set_name('INV', 'INV_MGD_HIER_INVALID_PROPERTY');
1123 FND_MESSAGE.set_token('HIERARCHY', l_hierarchy_name);
1124 FND_MESSAGE.set_token('PROPERTY', l_property);
1125 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1126
1127 RAISE l_hierarchy_validation;
1128
1129 END IF;
1130
1131
1132
1133
1134 IF G_DEBUG = 'Y' THEN
1135 INV_ORGHIERARCHY_PVT.Log
1136 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1137 ,' '
1138 );
1139
1140 INV_ORGHIERARCHY_PVT.Log
1141 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1142 ,'*********************** Start of Report *********************** '
1143 );
1144
1145 INV_ORGHIERARCHY_PVT.Log
1146 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1147 ,' '
1148 );
1149
1150 INV_ORGHIERARCHY_PVT.Log
1151 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1152 ,' ------------------- Running for Parameters --------------- '
1153 );
1154
1155 INV_ORGHIERARCHY_PVT.Log
1156 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1157 ,' p_org_hierarchy_origin '
1158 || p_org_hierarchy_origin
1159 );
1160
1161 INV_ORGHIERARCHY_PVT.Log
1162 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1163 ,' p_org_hierarchy_id '
1164 || p_org_hierarchy_id
1165 );
1166
1167 INV_ORGHIERARCHY_PVT.Log
1168 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1169 ,' p_close_period_name '
1170 || p_close_period_name
1171 );
1172
1173 INV_ORGHIERARCHY_PVT.Log
1174 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1175 ,' p_open_period_count '
1176 || p_open_period_count
1177 );
1178
1179 INV_ORGHIERARCHY_PVT.Log
1180 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1181 ,' p_open_or_close_flag '
1182 || p_open_or_close_flag
1183 );
1184
1185 INV_ORGHIERARCHY_PVT.Log
1186 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1187 ,' p_requests_count '
1188 || p_requests_count
1189 );
1190
1191 INV_ORGHIERARCHY_PVT.Log
1192 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1193 ,' '
1194 );
1195
1196 INV_ORGHIERARCHY_PVT.Log
1197 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1198 ,' --------------- End Parameters ----------------------------------------- '
1199 );
1200
1201 INV_ORGHIERARCHY_PVT.Log
1202 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1203 ,' '
1204 );
1205
1206 INV_ORGHIERARCHY_PVT.Log
1207 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1208 ,' ............................................................ '
1209 );
1210 END IF;
1211
1212 l_count := 1 ;
1213 l_login_id := NVL(TO_NUMBER(FND_PROFILE.Value('LOGIN_ID')),1) ;
1214 l_user_id := NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),1) ;
1215
1216 BEGIN
1217 /* Bug: 3638081. Following query is modifed and view org_organization_definitions is replaced with its definition.
1218 SELECT
1219 glstb.period_set_name
1220 , glstb.ACCOUNTED_PERIOD_TYPE
1221 , orgu.date_from
1222 INTO
1223 l_period_set_name
1224 , l_sob_period_type
1225 , l_org_from_date
1226 FROM
1227 gl_sets_of_books glstb
1228 , org_organization_definitions orgdef
1229 , hr_organization_units orgu
1230 WHERE orgdef.organization_id = p_org_hierarchy_origin
1231 AND glstb.set_of_books_id = orgdef.set_of_books_id
1232 AND orgu.organization_id = orgdef.organization_id
1233 AND orgu.business_group_id = orgdef.business_group_id ;
1234 */
1235 SELECT
1236 glstb.period_set_name
1237 , glstb.ACCOUNTED_PERIOD_TYPE
1238 , HOU.date_from from_date
1239 INTO
1240 l_period_set_name
1241 , l_sob_period_type
1242 , l_org_from_date
1243 FROM
1244 MTL_PARAMETERS MP
1245 , hr_organization_units HOU
1246 , HR_ORGANIZATION_INFORMATION HOI
1247 , gl_sets_of_books glstb
1248 WHERE HOU.ORGANIZATION_ID = p_org_hierarchy_origin
1249 AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
1250 AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
1251 AND UPPER( HOI.ORG_INFORMATION_CONTEXT || '') = 'ACCOUNTING INFORMATION'
1252 AND TO_NUMBER(HOI.ORG_INFORMATION1) = glstb.SET_OF_BOOKS_ID ;
1253
1254 IF G_DEBUG = 'Y' THEN
1255 INV_ORGHIERARCHY_PVT.Log
1256 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1257 ,' FOUND: Period Set name '
1258 || l_period_set_name );
1259
1260 INV_ORGHIERARCHY_PVT.Log
1261 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1262 ,' FOUND: Period Type '
1263 || l_sob_period_type );
1264
1265 INV_ORGHIERARCHY_PVT.Log
1266 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1267 ,' Calling GET_MAX_OPEN_PERIOD ' );
1268 END IF;
1269
1270 GET_MAX_OPEN_PERIOD
1271 ( p_org_id => p_org_hierarchy_origin
1272 , p_period_set_name => l_period_set_name
1273 , p_period_type => l_sob_period_type
1274 , x_period_start_date => l_period_final_start_date
1275 , x_period_end_date => l_period_final_end_date
1276 , x_period_name => l_final_period_name
1277 );
1278
1279 IF G_DEBUG = 'Y' THEN
1280 INV_ORGHIERARCHY_PVT.Log
1281 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1282 ,' Out of GET_MAX_OPEN_PERIOD with period name '|| l_final_period_name );
1283 END IF;
1284
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND
1287 THEN
1288
1289 IF G_DEBUG = 'Y' THEN
1290 INV_ORGHIERARCHY_PVT.Log
1291 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1292 ,' NO_DATA_FOUND: Period Set name not found for Hierarchy Origin '
1293 || p_org_hierarchy_origin
1294 );
1295 END IF;
1296 RAISE ;
1297
1298 END ;
1299
1300 -- for the open flag process the hierarchy origin first
1301 IF p_open_or_close_flag = 'O' THEN
1302
1303 OPEN c_org_name(p_org_hierarchy_origin);
1304 FETCH c_org_name
1305 INTO
1306 l_org_name
1307 , l_date_from ;
1308
1309 CLOSE c_org_name;
1310
1311 IF G_DEBUG = 'Y' THEN
1312 INV_ORGHIERARCHY_PVT.Log
1313 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1314 ,' Hierarchy Origin name = '||
1315 l_org_name
1316 );
1317
1318 INV_ORGHIERARCHY_PVT.Log
1319 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1320 ,' Calling GET_MIN_OPEN_PERIOD ' );
1321 END IF;
1322
1323 GET_MIN_OPEN_PERIOD
1324 ( p_org_id => p_org_hierarchy_origin
1325 , p_period_set_name => l_period_set_name
1326 , p_period_type => l_sob_period_type
1327 , x_period_start_date => l_min_start_date
1328 , x_period_end_date => l_min_end_date
1329 , x_period_name => l_min_period
1330 );
1331
1332 IF G_DEBUG = 'Y' THEN
1333 INV_ORGHIERARCHY_PVT.Log
1334 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1335 ,' Out of GET_MIN_OPEN_PERIOD with period name '|| l_min_period );
1336 END IF;
1337
1338 -- If there are no minimum periods opened for this Org
1339 -- the starting period will be determined by the
1340 -- Org from date. If not , the next period of the
1341 -- Minimum opened period is the starting point.
1342
1343 IF l_min_end_date IS NOT NULL
1344 THEN
1345 l_org_from_date := l_min_end_date + 1 ;
1346 ELSE
1347 -- Bug :3263991 for fixing the incorrect periods opening.
1348 -- l_org_from_date := l_date_from - 1 ;
1349 l_org_from_date := l_date_from ;
1350 END IF;
1351
1352 IF G_DEBUG = 'Y' THEN
1353 INV_ORGHIERARCHY_PVT.Log
1354 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1355 ,' l_org_from_date '|| l_org_from_date
1356 );
1357 END IF;
1358
1359 IF l_count > NVL(p_open_period_count,0) THEN
1360
1361 IF G_DEBUG = 'Y' THEN
1362 INV_ORGHIERARCHY_PVT.Log
1363 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1364 ,' No Processing for Hierarchy Origin as Input count 0 '
1365 );
1366 END IF;
1367 ELSE
1368 -- open the periods for the hierarchy origin
1369 FOR l_gl_period_future IN c_gl_period_future(p_org_hierarchy_origin)
1370 LOOP
1371 -- The Inventory Open period Api is called
1372 -- for each of the period
1373
1374 l_final_period_name := l_gl_period_future.open_period_name ;
1375 l_period_final_start_date := l_gl_period_future.period_start_date ;
1376 l_period_final_end_date := l_gl_period_future.period_end_date ;
1377
1378 IF G_DEBUG = 'Y' THEN
1379 INV_ORGHIERARCHY_PVT.Log
1380 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1381 ,'Period Final End Date: ' || to_char(l_period_final_end_date)
1382 );
1383
1384 INV_ORGHIERARCHY_PVT.Log
1385 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1386 ,'********* Start Processing for Hierarchy Origin ********* '
1387 );
1388
1389 INV_ORGHIERARCHY_PVT.Log
1390 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1391 ,'Period Count: ' || l_count
1392 );
1393 /* Following code line has been commented. It is shifted
1394 out of debug condition block.Bug 3555234.
1395 ADD_ITEM
1396 ( p_org => l_org_name
1397 , p_period => NULL
1398 , p_status => NULL
1399 , p_reason => NULL
1400 , p_request_id => NULL
1401 , p_closed => NULL
1402 , p_acct_period_id => NULL
1403 );
1404 */
1405
1406 INV_ORGHIERARCHY_PVT.Log
1407 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1408 ,' ======== Attempt to Open Period : ' || l_gl_period_future.open_period_name
1409 || ' ============================ ' );
1410 END IF;
1411
1412 /* Bug 3555234. Following code line shifted out of above block.*/
1413 ADD_ITEM
1414 ( p_org => l_org_name
1415 , p_period => NULL
1416 , p_status => NULL
1417 , p_reason => NULL
1418 , p_request_id => NULL
1419 , p_closed => NULL
1420 , p_acct_period_id => NULL
1421 );
1422
1423
1424 CST_AccountingPeriod_PUB.open_period
1425 ( p_api_version => l_api_version
1426 , p_org_id => p_org_hierarchy_origin
1427 , p_user_id => l_user_id
1428 , p_login_id => l_login_id
1429 , p_acct_period_type => l_gl_period_future.acct_period_type
1430 , p_org_period_set_name => l_period_set_name
1431 , p_open_period_name => l_gl_period_future.open_period_name
1432 , p_open_period_year => l_gl_period_future.open_period_year
1433 , p_open_period_num => l_gl_period_future.open_period_num
1434 , x_last_scheduled_close_date => l_last_scheduled_close_date
1435 , p_period_end_date => l_gl_period_future.period_end_date
1436 , x_prior_period_open => l_prior_period_open
1437 , x_new_acct_period_id => l_new_acct_period_id
1438 , x_duplicate_open_period => l_duplicate_open_period
1439 , x_commit_complete => l_commit_complete
1440 , x_return_status => l_return_status
1441 ) ;
1442
1443 IF G_DEBUG = 'Y' THEN
1444 INV_ORGHIERARCHY_PVT.Log
1445 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1446 ,' l_last_scheduled_close_date ' || l_last_scheduled_close_date
1447 );
1448
1449 INV_ORGHIERARCHY_PVT.Log
1450 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1451 ,' l_new_acct_period_id ' || l_new_acct_period_id
1452 );
1453 END IF;
1454
1455 IF (l_duplicate_open_period) = true
1456 THEN
1457 IF G_DEBUG = 'Y' THEN
1458 INV_ORGHIERARCHY_PVT.Log
1459 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1460 , ' duplicate_open_period Error for '
1461 || l_orgid
1462 );
1463 END IF;
1464
1465 ADD_ITEM
1466 ( p_org => NULL
1467 , p_period => l_gl_period_future.open_period_name
1468 , p_status => 'IGNORE'
1469 , p_reason => 'Duplicate Open Periods'
1470 , p_request_id => NULL
1471 , p_closed => NULL
1472 , p_acct_period_id => NULL
1473 );
1474
1475 END IF;
1476
1477 IF l_prior_period_open = true
1478 THEN
1479 IF G_DEBUG = 'Y' THEN
1480 INV_ORGHIERARCHY_PVT.Log
1481 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1482 , ' '
1483 );
1484 END IF;
1485 ELSE
1486 IF G_DEBUG = 'Y' THEN
1487 INV_ORGHIERARCHY_PVT.Log
1488 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1489 , ' EXCEPTION: l_prior_period_open FALSE '
1490 );
1491 END IF;
1492 ADD_ITEM
1493 ( p_org => NULL
1494 , p_period => l_gl_period_future.open_period_name
1495 , p_status => 'IGNORE'
1496 , p_reason => 'Prior period not Open'
1497 , p_request_id => NULL
1498 , p_closed => NULL
1499 , p_acct_period_id => NULL
1500 );
1501 END IF;
1502
1503 IF (l_commit_complete) = true
1504 THEN
1505 IF G_DEBUG = 'Y' THEN
1506 INV_ORGHIERARCHY_PVT.Log
1507 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1508 ,' Open SUCCESS for '
1509 || l_orgid || ' - '|| l_org_name
1510 );
1511 END IF;
1512 ADD_ITEM
1513 ( p_org => NULL
1514 , p_period => l_gl_period_future.open_period_name
1515 , p_status => 'OPEN'
1516 , p_reason => NULL
1517 , p_request_id => NULL
1518 , p_acct_period_id => NULL
1519 , p_closed => NULL
1520 );
1521
1522 COMMIT;
1523 ELSE
1524 IF G_DEBUG = 'Y' THEN
1525 INV_ORGHIERARCHY_PVT.Log
1526 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1527 ,' Open FAILED for '
1528 || l_orgid || ' - '|| l_org_name
1529 );
1530 END IF;
1531
1532 ADD_ITEM
1533 ( p_org => NULL
1534 , p_period => l_gl_period_future.open_period_name
1535 , p_status => 'FAILED'
1536 , p_reason => 'Open process Failed'
1537 , p_request_id => NULL
1538 , p_closed => NULL
1539 , p_acct_period_id => NULL
1540 );
1541 ROLLBACK ;
1542 END IF;
1543
1544 IF G_DEBUG = 'Y' THEN
1545 INV_ORGHIERARCHY_PVT.Log
1546 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1547 ,' Hierarchy Origin End of Period Name ' || l_gl_period_future.open_period_name
1548 || ' ============================ ' );
1549 END IF;
1550
1551 IF l_count >= p_open_period_count
1552 THEN
1553 EXIT ;
1554 ELSE
1555 l_count := l_count + 1 ;
1556 END IF;
1557
1558
1559 END LOOP;
1560 -- end loop for hierarchy origin
1561 END IF; -- input count 0 check
1562
1563 SELECT max(start_date)
1564 INTO l_max_period_start_date
1565 FROM GL_PERIODS
1566 WHERE ADJUSTMENT_PERIOD_FLAG = 'N'
1567 AND period_type = l_sob_period_type
1568 AND PERIOD_SET_NAME = l_period_set_name;
1569
1570 GET_MAX_OPEN_PERIOD
1571 ( p_org_id => p_org_hierarchy_origin
1572 , p_period_set_name => l_period_set_name
1573 , p_period_type => l_sob_period_type
1574 , x_period_start_date => l_max_open_period_start_date
1575 , x_period_end_date => l_max_open_period_end_date
1576 , x_period_name => l_max_open_period_name
1577 );
1578
1579 IF l_max_period_start_date = l_max_open_period_start_date THEN
1580 FND_MESSAGE.set_name('INV', 'INV_MGD_DEFINE_PERIODS');
1581 FND_MESSAGE.set_token('PERIOD_NAME', l_max_open_period_name);
1582 FND_MESSAGE.set_token('CALENDAR', l_period_set_name);
1583 ADD_ITEM ( p_org => NULL
1584 , p_period => l_period_set_name
1585 , p_status => NULL
1586 , p_reason => FND_MESSAGE.GET
1587 , p_request_id => NULL
1588 , p_closed => NULL
1589 , p_acct_period_id => NULL
1590 );
1591 END IF;
1592
1593 END IF; -- open period flag
1594
1595 -- re-initialize l_count
1596 l_count := 1;
1597
1598 -- The Organization list belonging to this Hierarchy Origin
1599 -- is retreived. organization list includes the hierarchy origin
1600
1601 IF G_DEBUG = 'Y' THEN
1602 INV_ORGHIERARCHY_PVT.Log
1603 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1604 ,' Calling INV_ORGHIERARCHY_PVT.get_organization_List '
1605 );
1606 END IF;
1607
1608
1609 IF NVL(l_org_code_list.COUNT,0) > 0
1610 THEN
1611 IF G_DEBUG = 'Y' THEN
1612 INV_ORGHIERARCHY_PVT.Log
1613 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1614 ,' count > 0 '
1615 );
1616 END IF;
1617
1618 l_list_count := l_org_code_list.COUNT ;
1619
1620 l_index := l_org_code_list.FIRST;
1621 l_orgid := l_org_code_list(l_index);
1622
1623 IF G_DEBUG = 'Y' THEN
1624 INV_ORGHIERARCHY_PVT.Log
1625 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1626 ,' Initial l_index ' || l_index
1627 );
1628
1629 INV_ORGHIERARCHY_PVT.Log
1630 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1631 ,' Initial l_orgid ' || l_orgid
1632 );
1633
1634 INV_ORGHIERARCHY_PVT.Log
1635 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1636 ,' About to Enter WHILE Loop for the Org List '
1637 );
1638
1639 INV_ORGHIERARCHY_PVT.Log
1640 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1641 ,' '
1642 );
1643
1644 INV_ORGHIERARCHY_PVT.Log
1645 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1646 ,' ............................................................ '
1647 );
1648 END IF;
1649
1650 WHILE ( l_list_count > 0 )
1651 LOOP
1652 IF G_DEBUG = 'Y' THEN
1653 INV_ORGHIERARCHY_PVT.Log
1654 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1655 ,' Into while loop with l_index ' || l_index
1656 );
1657 END IF;
1658
1659 l_orgid := l_org_code_list(l_index);
1660
1661 IF G_DEBUG = 'Y' THEN
1662 INV_ORGHIERARCHY_PVT.Log
1663 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1664 ,' Organization Id: ' || l_orgid
1665 );
1666 END IF;
1667
1668 -- Get Organization Name and Date from
1669 OPEN c_org_name(l_orgid);
1670 FETCH c_org_name
1671 INTO
1672 l_org_name
1673 , l_date_from ;
1674
1675 CLOSE c_org_name;
1676
1677 -- process only for the child organizations
1678 -- exclude hierarchy origin for the open flag
1679 IF p_open_or_close_flag = 'O' AND l_orgid <> p_org_hierarchy_origin
1680 THEN
1681
1682 IF G_DEBUG = 'Y' THEN
1683 INV_ORGHIERARCHY_PVT.Log
1684 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1685 ,' while loop l_orgid ' || l_orgid
1686 );
1687 END IF;
1688
1689
1690 IF G_DEBUG = 'Y' THEN
1691 INV_ORGHIERARCHY_PVT.Log
1692 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1693 ,' ************** START Processing Organization = '||
1694 l_org_name || ' ************************** '
1695 );
1696 END IF;
1697
1698 ADD_ITEM
1699 ( p_org => l_org_name
1700 , p_period => NULL
1701 , p_status => NULL
1702 , p_reason => NULL
1703 , p_request_id => NULL
1704 , p_closed => NULL
1705 , p_acct_period_id => NULL
1706 );
1707
1708
1709 IF G_DEBUG = 'Y' THEN
1710 INV_ORGHIERARCHY_PVT.Log
1711 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1712 ,'## Organization Date From '|| l_date_from
1713 );
1714 END IF;
1715
1716 -- Final period end date is the final period
1717 -- of the Hierarchy Origin being opened
1718
1719 l_cursor_final_end_date := l_period_final_end_date ;
1720
1721 IF G_DEBUG = 'Y' THEN
1722 INV_ORGHIERARCHY_PVT.Log
1723 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1724 ,' l_cursor_final_end_date '|| l_cursor_final_end_date
1725 );
1726
1727 -- The Minimum and Maximum periods for this Org
1728 -- is selected. This required to determine the
1729 -- range of periods that are eligible to be Opened
1730
1731 INV_ORGHIERARCHY_PVT.Log
1732 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1733 ,' Calling GET_MIN_OPEN_PERIOD ' );
1734 END IF;
1735
1736 GET_MIN_OPEN_PERIOD
1737 ( p_org_id => l_orgid
1738 , p_period_set_name => l_period_set_name
1739 , p_period_type => l_sob_period_type
1740 , x_period_start_date => l_min_start_date
1741 , x_period_end_date => l_min_end_date
1742 , x_period_name => l_min_period
1743 );
1744
1745 IF G_DEBUG = 'Y' THEN
1746 INV_ORGHIERARCHY_PVT.Log
1747 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1748 ,' Out of GET_MIN_OPEN_PERIOD with period name '|| l_min_period );
1749 END IF;
1750
1751 -- If there are no minimum periods opened for this Org
1752 -- the starting period will be determined by the
1753 -- Org from date. If not , the next period of the
1754 -- Minimum opened period is the starting point.
1755
1756 IF l_min_end_date IS NOT NULL
1757 THEN
1758 l_org_from_date := l_min_end_date + 1 ;
1759 ELSE
1760 --NKILLEDA : Modified for Bug 3263991 for fixing the
1761 -- incorrect periods opening.
1762 --l_org_from_date := l_date_from - 1 ;
1763 l_org_from_date := l_date_from;
1764 END IF;
1765
1766 IF G_DEBUG = 'Y' THEN
1767 INV_ORGHIERARCHY_PVT.Log
1768 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1769 ,' l_org_from_date '|| l_org_from_date
1770 );
1771 END IF;
1772
1773 FOR l_gl_period_future IN c_gl_period_future(l_orgid)
1774 LOOP
1775 -- The Inventory Open period Api is called
1776 -- for each of the period
1777
1778
1779 IF G_DEBUG = 'Y' THEN
1780 INV_ORGHIERARCHY_PVT.Log
1781 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1782 ,' ======== Attempt to Open Period : ' || l_gl_period_future.open_period_name
1783 || ' ============================ ' );
1784 END IF;
1785
1786 CST_AccountingPeriod_PUB.open_period
1787 ( p_api_version => l_api_version
1788 , p_org_id => l_orgid
1789 , p_user_id => l_user_id
1790 , p_login_id => l_login_id
1791 , p_acct_period_type => l_gl_period_future.acct_period_type
1792 , p_org_period_set_name => l_period_set_name
1793 , p_open_period_name => l_gl_period_future.open_period_name
1794 , p_open_period_year => l_gl_period_future.open_period_year
1795 , p_open_period_num => l_gl_period_future.open_period_num
1796 , x_last_scheduled_close_date => l_last_scheduled_close_date
1797 , p_period_end_date => l_gl_period_future.period_end_date
1798 , x_prior_period_open => l_prior_period_open
1799 , x_new_acct_period_id => l_new_acct_period_id
1800 , x_duplicate_open_period => l_duplicate_open_period
1801 , x_commit_complete => l_commit_complete
1802 , x_return_status => l_return_status
1803 ) ;
1804
1805 IF G_DEBUG = 'Y' THEN
1806 INV_ORGHIERARCHY_PVT.Log
1807 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1808 ,' l_last_scheduled_close_date ' || l_last_scheduled_close_date
1809 );
1810
1811 INV_ORGHIERARCHY_PVT.Log
1812 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1813 ,' l_new_acct_period_id ' || l_new_acct_period_id
1814 );
1815 END IF;
1816
1817 IF (l_duplicate_open_period) = true
1818 THEN
1819 IF G_DEBUG = 'Y' THEN
1820 INV_ORGHIERARCHY_PVT.Log
1821 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1822 , ' duplicate_open_period Error for '
1823 || l_orgid
1824 );
1825 END IF;
1826
1827 ADD_ITEM
1828 ( p_org => NULL
1829 , p_period => l_gl_period_future.open_period_name
1830 , p_status => 'IGNORE'
1831 , p_reason => 'Duplicate Open Periods'
1832 , p_request_id => NULL
1833 , p_closed => NULL
1834 , p_acct_period_id => NULL
1835 );
1836
1837 END IF;
1838
1839 IF (l_prior_period_open) = true
1840 THEN
1841 IF G_DEBUG = 'Y' THEN
1842 INV_ORGHIERARCHY_PVT.Log
1843 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1844 , ' '
1845 );
1846 END IF;
1847 ELSE
1848 IF G_DEBUG = 'Y' THEN
1849 INV_ORGHIERARCHY_PVT.Log
1850 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1851 , ' EXCEPTION: l_prior_period_open FALSE '
1852 );
1853 END IF;
1854
1855 ADD_ITEM
1856 ( p_org => NULL
1857 , p_period => l_gl_period_future.open_period_name
1858 , p_status => 'IGNORE'
1859 , p_reason => 'Prior period not Open'
1860 , p_request_id => NULL
1861 , p_closed => NULL
1862 , p_acct_period_id => NULL
1863 );
1864
1865 END IF;
1866
1867 IF (l_commit_complete) = true
1868 THEN
1869 IF G_DEBUG = 'Y' THEN
1870 INV_ORGHIERARCHY_PVT.Log
1871 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1872 ,' Open SUCCESS for '
1873 || l_orgid || ' - '|| l_org_name
1874 );
1875 END IF;
1876
1877 ADD_ITEM
1878 ( p_org => NULL
1879 , p_period => l_gl_period_future.open_period_name
1880 , p_status => 'OPEN'
1881 , p_reason => NULL
1882 , p_request_id => NULL
1883 , p_acct_period_id => NULL
1884 , p_closed => NULL
1885 );
1886
1887 COMMIT;
1888 ELSE
1889 IF G_DEBUG = 'Y' THEN
1890 INV_ORGHIERARCHY_PVT.Log
1891 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1892 ,' Open FAILED for '
1893 || l_orgid || ' - '|| l_org_name
1894 );
1895 END IF;
1896
1897 ADD_ITEM
1898 ( p_org => NULL
1899 , p_period => l_gl_period_future.open_period_name
1900 , p_status => 'FAILED'
1901 , p_reason => 'Open process Failed'
1902 , p_request_id => NULL
1903 , p_closed => NULL
1904 , p_acct_period_id => NULL
1905 );
1906 ROLLBACK ;
1907 END IF;
1908
1909 IF G_DEBUG = 'Y' THEN
1910 INV_ORGHIERARCHY_PVT.Log
1911 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1912 ,' ======== End of Period Name ' || l_gl_period_future.open_period_name
1913 || ' ============================ ' );
1914 END IF;
1915
1916 END LOOP; -- end gl fututre cursor loop
1917
1918 END IF; -- process only for child organizations
1919
1920
1921 IF p_open_or_close_flag = 'C' THEN
1922 FOR l_org_acct_periods_val_open IN c_org_acct_periods_open
1923 LOOP
1924 IF l_org_acct_periods_val_open.open_flag = 'Y'
1925 THEN
1926 -- The period close is first verified if it can be Opened
1927 -- If YES, the Close period Inventory API is called
1928
1929 ADD_ITEM
1930 ( p_org => l_org_name
1931 , p_period => NULL
1932 , p_status => NULL
1933 , p_reason => NULL
1934 , p_request_id => NULL
1935 , p_closed => NULL
1936 , p_acct_period_id => NULL
1937 );
1938
1939
1940
1941 IF G_DEBUG = 'Y' THEN
1942 INV_ORGHIERARCHY_PVT.Log
1943 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1944 ,' Calling CST_AccountingPeriod_PUB.verify_periodclose for Org ID '
1945 || l_orgid
1946 );
1947
1948 INV_ORGHIERARCHY_PVT.Log
1949 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1950 ,' Org ID ' || l_orgid || ' for period ' ||
1951 l_org_acct_periods_val_open.open_period_name ||
1952 ' Schedule Close Date:' || l_org_acct_periods_val_open.schedule_close_date
1953 );
1954 END IF;
1955
1956 -- Bug: 3590042. Initialization of variable l_verify_flag is added.
1957 -- Verify flag for verify_periodclose
1958 l_verify_flag := 'Y';
1959
1960 CST_AccountingPeriod_PUB.VERIFY_PERIODCLOSE
1961 ( p_api_version => l_api_version
1962 , p_org_id => l_orgid
1963 , p_closing_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
1964 , p_closing_end_date => l_org_acct_periods_val_open.schedule_close_date
1965 , x_open_period_exists => l_open_period_exists
1966 , x_proper_order => l_proper_order
1967 , x_end_date_is_past => l_end_date_is_past
1968 , x_download_in_process => l_download_in_process
1969 , x_prompt_to_reclose => l_prompt_to_reclose
1970 , x_return_status => l_return_status
1971 ) ;
1972
1973 -- ==================================================
1974 -- Display log error messages
1975 -- check for all the verifications success
1976 -- ==================================================
1977 -- check whether period is openend
1978 IF NOT l_open_period_exists THEN
1979 /*Message name in following method is modified from
1980 'INV_NON_NEXT_PERIOD' to 'INV_NO_NEXT_PERIOD'.
1981 Bug: 3555234
1982 */
1983 FND_MESSAGE.SET_NAME('INV', 'INV_NO_NEXT_PERIOD');
1984 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
1985
1986 IF G_DEBUG = 'Y' THEN
1987 INV_ORGHIERARCHY_PVT.Log
1988 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1989 ,l_error_msg
1990 );
1991 END IF;
1992
1993 l_verify_flag := 'N';
1994
1995 -- check whether this period is the next period to close
1996 ELSIF NOT l_proper_order THEN
1997 FND_MESSAGE.SET_NAME('INV', 'INV_CLOSE_IN_ORDER');
1998 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
1999
2000 IF G_DEBUG = 'Y' THEN
2001 INV_ORGHIERARCHY_PVT.Log
2002 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2003 ,l_error_msg
2004 );
2005 END IF;
2006
2007 l_verify_flag := 'N';
2008
2009 -- Following ELSEIF block has been modified during fix #4457006.
2010 -- Now it doesn't compare end date with today becasue period
2011 -- can be closed on any date if it has started.
2012 -- So if EndDate is not past then it should check whether
2013 -- open date is past otherwise period can not be closed.
2014
2015 ELSIF NOT l_end_date_is_past THEN
2016
2017 FND_PROFILE.GET('CST_ALLOW_EARLY_PERIOD_CLOSE',l_allow_close);
2018
2019 IF l_allow_close = '1' THEN
2020 SELECT TO_NUMBER(HOI.org_information2)
2021 INTO l_legal_entity
2022 FROM hr_organization_information HOI
2023 WHERE HOI.org_information_context = 'Accounting Information'
2024 AND HOI.organization_id = l_orgid;
2025
2026 l_le_sysdate := INV_LE_TIMEZONE_PUB.GET_LE_SYSDATE_FOR_OU(
2027 l_legal_entity);
2028 IF (l_org_acct_periods_val_open.period_start_date > l_le_sysdate) THEN
2029 FND_MESSAGE.SET_NAME('BOM','CST_CLOSE_FUTURE_PERIOD');
2030 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2031 IF G_DEBUG = 'Y' THEN
2032 INV_ORGHIERARCHY_PVT.Log
2033 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2034 ,l_error_msg
2035 );
2036 END IF;
2037 l_verify_flag := 'N';
2038 ELSE
2039 -- Convert Period end date into server time zone. Adding .99999 becasue
2040 -- Period end date does not store time factor.
2041 l_period_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
2042 l_org_acct_periods_val_open.schedule_close_date + .99999,
2043 l_legal_entity);
2044 -- Get remaining hours from sysdate
2045 l_hours_remaining := round((l_period_end_date - sysdate) * 24);
2046
2047 IF G_DEBUG = 'Y' THEN
2048 INV_ORGHIERARCHY_PVT.Log
2049 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2050 ,'Remaining hours to close period : ' || l_hours_remaining
2051 );
2052 END IF;
2053 ADD_ITEM
2054 ( p_org => NULL
2055 , p_period => l_org_acct_periods_val_open.open_period_name
2056 , p_status => 'Warning'
2057 , p_reason => 'Remaining hours to close period : ' || l_hours_remaining
2058 , p_request_id => NULL
2059 , p_closed => NULL
2060 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2061 );
2062 END IF; --IF (l_period_open_date > l_le_sysdate)
2063
2064
2065 ELSE
2066 FND_MESSAGE.SET_NAME('BOM','CST_EARLY_CLOSE_NOT_ALLOWED');
2067 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2068 IF G_DEBUG = 'Y' THEN
2069 INV_ORGHIERARCHY_PVT.Log
2070 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2071 ,l_error_msg
2072 );
2073 END IF;
2074 l_verify_flag := 'N';
2075 END IF;
2076
2077 -- period close already in process for org
2078 ELSIF l_download_in_process THEN
2079 FND_MESSAGE.SET_NAME('INV', 'INV_GL_DOWNLOAD_IN_PROGRESS');
2080 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2081
2082 IF G_DEBUG = 'Y' THEN
2083 INV_ORGHIERARCHY_PVT.Log
2084 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2085 ,l_error_msg
2086 );
2087 END IF;
2088
2089 l_verify_flag := 'N';
2090
2091 -- popup modal window asking to reclose period
2092 ELSIF l_prompt_to_reclose THEN
2093 FND_MESSAGE.SET_NAME('INV', 'INV_RECLOSE_PERIOD');
2094 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2095
2096 IF G_DEBUG = 'Y' THEN
2097 INV_ORGHIERARCHY_PVT.Log
2098 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2099 ,l_error_msg
2100 );
2101 END IF;
2102
2103 l_verify_flag := 'N';
2104
2105 END IF; -- verify conditions
2106
2107 -- insert error log on the report
2108 IF l_verify_flag = 'N' THEN
2109 ADD_ITEM
2110 ( p_org => NULL
2111 , p_period => l_org_acct_periods_val_open.open_period_name
2112 , p_status => 'Ignore'
2113 , p_reason => l_error_msg
2114 , p_request_id => NULL
2115 , p_closed => 'N'
2116 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2117 );
2118 END IF;
2119
2120 -- Proceed further to close the period only if the
2121 -- verify flag is 'Y'
2122 IF l_verify_flag = 'Y' THEN
2123 -- Bug#2230141 fix - check for all the pending transactions
2124 -- Bug#2386091 fix - added parameter pending_ship
2125 CST_AccountingPeriod_PUB.get_pendingtcount
2126 ( p_api_version => l_api_version
2127 , p_org_id => l_orgid
2128 , p_closing_period => l_org_acct_periods_val_open.closing_acct_period_id
2129 , p_sched_close_date => l_org_acct_periods_val_open.schedule_close_date
2130 , x_pend_receiving => l_pend_receiving
2131 , x_unproc_matl => l_unproc_matl
2132 , x_pend_matl => l_pend_matl
2133 , x_uncost_matl => l_uncost_matl
2134 , x_pend_move => l_pend_move
2135 , x_pend_WIP_cost => l_pend_WIP_cost
2136 , x_uncost_wsm => l_uncost_wsm
2137 , x_pending_wsm => l_pending_wsm
2138 , x_pending_ship => l_pending_ship
2139 /* Support for LCM */
2140 , x_pending_lcm => l_pending_lcm
2141 , x_released_work_orders => l_released_work_orders
2142 , x_return_status => l_return_status
2143 );
2144 -- check for pending transactions
2145 IF l_unproc_matl = 0 AND l_uncost_matl = 0 AND l_pend_WIP_cost = 0 AND
2146 l_uncost_wsm = 0 AND l_pending_wsm = 0 AND l_pending_lcm = 0 AND
2147 ( ( p_close_if_res_recmd = 'N' AND l_pend_receiving = 0 AND l_pend_matl = 0
2148 AND l_pend_move = 0 AND l_released_work_orders = 0 AND l_pending_ship = 0)
2149 OR
2150 ( p_close_if_res_recmd = 'Y' AND l_pend_receiving >= 0 AND l_pend_matl >= 0
2151 AND l_pend_move >= 0 AND l_released_work_orders >= 0 AND
2152 (l_pending_ship = 0 OR
2153 (cst_periodcloseoption_pub.get_shippingtxnhook_value(p_org_id => l_orgid, p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id) = 1
2154 AND l_pending_ship >= 0)
2155 )
2156 )
2157 )
2158 THEN
2159
2160 -- Request Loop
2161 -- The Close period is called only if the
2162 -- number of currently running Concurrent programs are
2163 -- lesser than the Input parameter for Request count
2164
2165 LOOP
2166 IF GET_OPEN_REQUESTS_COUNT < l_requests_count
2167 THEN
2168 IF G_DEBUG = 'Y' THEN
2169 INV_ORGHIERARCHY_PVT.Log
2170 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2171 ,' Exiting Request Loop '
2172 );
2173 END IF;
2174 EXIT;
2175 END IF;
2176
2177 /* Bug 3999140. Sleep time is introdued between execution of
2178 * Close Accounting Period concurrent program status checking query.
2179 */
2180 DBMS_LOCK.sleep(l_sleep_time);
2181
2182 END LOOP;
2183
2184 IF G_DEBUG = 'Y' THEN
2185 INV_ORGHIERARCHY_PVT.Log
2186 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2187 ,' Calling CLOSE_PERIOD for Org ID '
2188 || l_orgid || ' for period ' ||
2189 l_org_acct_periods_val_open.open_period_name
2190 );
2191 END IF;
2192
2193 /* myerrams, Bug:4599201. Modified the call to
2194 * CST_AccountingPeriod_PUB.close_period because of a
2195 * change of the signature.
2196 * The following parameters are removed:
2197 * p_period_close_date
2198 * p_schedule_close_date
2199 * p_closing_rowid
2200 * x_download_failed
2201 * And the following parameters are newly added:
2202 * x_unprocessed_txns
2203 * x_rec_rpt_launch_failed
2204 */
2205 CST_AccountingPeriod_PUB.close_period
2206 ( p_api_version => l_api_version
2207 , p_org_id => l_orgid
2208 , p_user_id => l_user_id
2209 , p_login_id => l_login_id
2210 , p_closing_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2211 , x_wip_failed => l_wip_failed
2212 , x_close_failed => l_close_failed
2213 , x_req_id => l_req_id
2214 , x_unprocessed_txns => l_unprocessed_txns
2215 , x_rec_rpt_launch_failed => l_rec_rpt_launch_failed
2216 , x_return_status => l_return_status
2217 );
2218
2219 IF G_DEBUG = 'Y' THEN
2220 INV_ORGHIERARCHY_PVT.Log
2221 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2222 ,' Out of CST_AccountingPeriod_PUB.close_period for Org ID ' || l_orgid
2223 );
2224 END IF;
2225
2226 IF (l_wip_failed) = true
2227 THEN
2228 IF G_DEBUG = 'Y' THEN
2229 INV_ORGHIERARCHY_PVT.Log
2230 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2231 ,' WIP Failed for '
2232 || l_orgid || ' Request ID ' || l_req_id
2233 ) ;
2234 END IF;
2235 ADD_ITEM
2236 ( p_org => NULL
2237 , p_period => l_org_acct_periods_val_open.open_period_name
2238 , p_status => 'Failed'
2239 , p_reason => 'WIP Failed'
2240 , p_request_id => l_req_id
2241 , p_closed => 'N'
2242 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2243 );
2244
2245 END IF;
2246
2247 IF (l_download_failed) = true
2248 THEN
2249 IF G_DEBUG = 'Y' THEN
2250 INV_ORGHIERARCHY_PVT.Log
2251 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2252 ,' download_failed for '
2253 || l_orgid || ' Request ID ' || l_req_id
2254 );
2255 END IF;
2256 ADD_ITEM
2257 ( p_org => NULL
2258 , p_period => l_org_acct_periods_val_open.open_period_name
2259 , p_status => 'Failed'
2260 , p_reason => 'Download Failed'
2261 , p_request_id => l_req_id
2262 , p_closed => 'N'
2263 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2264 );
2265
2266 END IF;
2267
2268 --myerrams, Bug:4599201
2269 IF (l_unprocessed_txns) = true
2270 THEN
2271
2272 FND_MESSAGE.SET_NAME('BOM','CST_UNPROCESSED_TXNS');
2273 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2274 IF G_DEBUG = 'Y' THEN
2275 INV_ORGHIERARCHY_PVT.Log
2276 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2277 ,l_error_msg
2278 );
2279 END IF;
2280
2281 FND_MESSAGE.SET_NAME('INV','INV_PERIOD_CLOSE_ABORTED');
2282 l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2283 IF G_DEBUG = 'Y' THEN
2284 INV_ORGHIERARCHY_PVT.Log
2285 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2286 ,l_error_msg
2287 );
2288 END IF;
2289
2290 END IF;
2291 --myerrams end, Bug:4599201
2292
2293 IF ( l_close_failed ) = true
2294 THEN
2295 IF G_DEBUG = 'Y' THEN
2296 INV_ORGHIERARCHY_PVT.Log
2297 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2298 ,' close Falied - for Org ID '
2299 || l_orgid || ' Request ID ' || l_req_id
2300 );
2301 END IF;
2302
2303 ADD_ITEM
2304 ( p_org => NULL
2305 , p_period => l_org_acct_periods_val_open.open_period_name
2306 , p_status => 'Failed'
2307 , p_reason => 'Close Failed'
2308 , p_request_id => l_req_id
2309 , p_closed => 'N'
2310 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2311 );
2312
2313 ROLLBACK ;
2314 ELSE --IF ( l_close_failed ) = true
2315 IF G_DEBUG = 'Y' THEN
2316 INV_ORGHIERARCHY_PVT.Log
2317 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2318 , ' Close in process REquest '|| l_req_id
2319 );
2320 END IF;
2321 IF p_close_if_res_recmd = 'Y' AND
2322 (l_pend_receiving > 0 OR l_pend_matl > 0 OR l_pend_move > 0
2323 OR l_released_work_orders > 0 OR l_pending_ship > 0) THEN
2324 l_closed_if_res_recmd := 1;
2325 IF G_DEBUG = 'Y' THEN
2326 INV_ORGHIERARCHY_PVT.Log
2327 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2328 , ' WARNING : There are pending transactions with resolutions recommended for '||l_org_acct_periods_val_open.open_period_name||
2329 ' in '||l_orgid
2330 );
2331 END IF;
2332 ADD_ITEM
2333 ( p_org => NULL
2334 , p_period => l_org_acct_periods_val_open.open_period_name
2335 , p_status => 'Warning'
2336 , p_reason => 'There are pending transactions with resolutions recommended'
2337 , p_request_id => NULL
2338 , p_closed => 'N'
2339 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2340 );
2341 END IF;
2342 ADD_ITEM
2343 ( p_org => NULL
2344 , p_period => l_org_acct_periods_val_open.open_period_name
2345 , p_status => 'Processing'
2346 , p_reason => NULL
2347 , p_request_id => l_req_id
2348 , p_closed => 'N'
2349 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2350 );
2351 COMMIT ;
2352 END IF; --IF ( l_close_failed ) = true
2353
2354 ELSE --IF l_unproc_matl = 0
2355 IF G_DEBUG = 'Y' THEN
2356 INV_ORGHIERARCHY_PVT.Log
2357 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2358 , 'Pending Transactions exists'
2359 );
2360 END IF;
2361 ADD_ITEM
2362 ( p_org => NULL
2363 , p_period => l_org_acct_periods_val_open.open_period_name
2364 , p_status => 'Ignore'
2365 , p_reason => 'Pending transactions exists'
2366 , p_request_id => NULL
2367 , p_closed => 'N'
2368 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2369 );
2370 END IF ; -- check for pending transaction
2371
2372 -- verify_periodclose failed
2373 ELSE
2374 IF G_DEBUG = 'Y' THEN
2375 INV_ORGHIERARCHY_PVT.Log
2376 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2377 , ' Period not eligible to be closed '
2378 );
2379 END IF;
2380
2381 END IF; -- verify_periodclose
2382
2383 ELSE
2384 IF G_DEBUG = 'Y' THEN
2385 INV_ORGHIERARCHY_PVT.Log
2386 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2387 , ' Period not Open '
2388 );
2389 END IF;
2390 /* Commented following code for showing details of only eligible
2391 periods in Summary report. Bug: 3555234
2392 ADD_ITEM
2393 ( p_org => NULL
2394 , p_period => l_org_acct_periods_val_open.open_period_name
2395 , p_status => 'Ignore'
2396 , p_reason => 'Not Open'
2397 , p_request_id => NULL
2398 , p_closed => 'N'
2399 , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2400 );*/
2401
2402 END IF; -- open flag check
2403 END LOOP;
2404 IF G_DEBUG = 'Y' THEN
2405 INV_ORGHIERARCHY_PVT.Log
2406 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2407 ,' Out of Close FOR loop ' );
2408 END IF;
2409 END IF; -- Open or close flag check
2410
2411 l_list_count := l_list_count - 1;
2412 l_index := l_index + 1;
2413
2414 IF G_DEBUG = 'Y' THEN
2415 INV_ORGHIERARCHY_PVT.Log
2416 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2417 ,' '
2418 );
2419
2420 INV_ORGHIERARCHY_PVT.Log
2421 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2422 ,' ************** END Processing Organization = '||
2423 l_org_name || ' ************************** '
2424 );
2425 END IF;
2426
2427 l_date_from := NULL;
2428 l_org_from_date := NULL;
2429
2430 IF G_DEBUG = 'Y' THEN
2431 INV_ORGHIERARCHY_PVT.Log
2432 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2433 ,' Out of Close FOR loop ' );
2434 END IF;
2435
2436 END LOOP; -- organization list loop
2437
2438 IF G_DEBUG = 'Y' THEN
2439 INV_ORGHIERARCHY_PVT.Log
2440 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2441 ,' Out of Org List WHILE Loop, start final request check '
2442 );
2443 END IF;
2444
2445 -- Final loop to check the status of the remaining
2446 -- concurrent close programs that could be running
2447
2448 LOOP
2449 IF GET_OPEN_REQUESTS_COUNT <= 0
2450 THEN
2451 IF G_DEBUG = 'Y' THEN
2452 INV_ORGHIERARCHY_PVT.Log
2453 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2454 ,' Exiting Request Loop '
2455 );
2456 END IF;
2457 EXIT;
2458 END IF;
2459
2460 /* Bug 3999140. Sleep time is introdued between execution of
2461 * Close Accounting Period concurrent program status checking query.
2462 */
2463 DBMS_LOCK.sleep(l_sleep_time);
2464
2465 END LOOP;
2466
2467 IF G_DEBUG = 'Y' THEN
2468 INV_ORGHIERARCHY_PVT.Log
2469 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2470 , ' Out of final Request Loop ' );
2471 END IF;
2472 ELSE
2473 IF G_DEBUG = 'Y' THEN
2474 INV_ORGHIERARCHY_PVT.Log
2475 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2476 , ' Org List Empty ' );
2477 END IF;
2478 END IF;
2479
2480 IF G_DEBUG = 'Y' THEN
2481 INV_ORGHIERARCHY_PVT.Log
2482 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2483 ,' '
2484 );
2485
2486 INV_ORGHIERARCHY_PVT.Log
2487 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2488 ,'*********************** End of Report *********************** '
2489 );
2490 END IF;
2491
2492 PRINT_REPORT ;
2493
2494 IF G_DEBUG = 'Y' THEN
2495 INV_ORGHIERARCHY_PVT.Log
2496 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2497 ,'< INV_MGD_PRD_CONTROL_MEDIATOR.Period_Control '
2498 );
2499 END IF;
2500
2501 IF l_closed_if_res_recmd = 1 THEN
2502 FND_MESSAGE.set_name('INV', 'INV_MGD_RES_RECMD_WARNING');
2503 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
2504 END IF;
2505 EXCEPTION
2506 -- Following exception handling block which was introduced during bug 3904824
2507 -- has been commented during fix 4457006 because 11.5.10 CU2 onwards, scheduling can be
2508 -- done for any date and user can close Period on any date they wish. So no need
2509 -- to handle scheduling related exception.
2510
2511 -- Bug #3904824.New exception introduced.
2512 -- WHEN l_close_period_before_sch_dt THEN
2513 -- INV_ORGHIERARCHY_PVT.Log
2514 -- (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,x_errbuff);
2515 -- x_retcode := RETCODE_ERROR;
2516
2517
2518 -- Bug:3296392 - Addeed the exception hanlding for the validaiton
2519 -- failure.
2520 WHEN l_hierarchy_validation THEN
2521 INV_ORGHIERARCHY_PVT.Log
2522 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,x_errbuff);
2523 x_retcode := RETCODE_ERROR;
2524
2525 WHEN OTHERS THEN
2526 /* This executable is used by concurrent program so
2527 Error/Exception logging should not depend on
2528 FND Debug Enabled profile otpion. Bug: 3555234
2529 IF G_DEBUG = 'Y' THEN
2530 */
2531 INV_ORGHIERARCHY_PVT.Log( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2532 , 'SQLERRM '|| SQLERRM) ;
2533 --END IF;
2534 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2535 THEN
2536 FND_MSG_PUB.Add_Exc_Msg
2537 ( G_PKG_NAME
2538 , ' Period_Control '
2539 );
2540 END IF;
2541 ROLLBACK;
2542 RAISE;
2543
2544 END Period_Control ;
2545
2546 END INV_MGD_PRD_CONTROL_MEDIATOR ;
2547