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