[Home] [Help]
PACKAGE BODY: APPS.CSTPPPSC
Source
1 PACKAGE BODY CSTPPPSC AS
2 /* $Header: CSTPPSCB.pls 120.8.12010000.3 2008/11/25 08:59:10 anjha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPPPSC';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 /* **************************************************************************** */
8 /* This procedure is called from PAC Periods form to open a fiscal accounting */
9 /* period. The explanation of the various IN and OUT params are as follows */
10 /* IN Params: 1. l_entity_id: Legal Entity ID of the pac Period */
11 /* 2. l_cost_type_id: Cost Type ID of the pac Period */
12 /* 3. l_user_id : user id */
13 /* 4. l_login_id: login id */
14 /* 5. open_period_name: Name of the period beging opened */
15 /* 6. open_period_num : Opening period number */
16 /* 7. open_period_year: Opening period year */
17 /* 8. open_period_set_name: Set of Books name for the LE-CT */
18 /* 9. l_period_end_date : Period end date to be opened */
19 /* IN OUT Params: */
20 /* 1. last_scheduled_close_date: It is a user defined param which */
21 /* holds the value of the max(end_date) of all the periods in */
22 /* in cst_pac_periods table for a particular LE-CT */
23 /* OUT Params: 1. prior_open_period: TRUE if this is the duplicate period to */
24 /* be opened for an LE-CT combination */
25 /* 2. improper_order: TRUE if the period being opened is not the */
26 /* subsequent period accounding to the Calender */
27 /* 3. new_pac_period_id: New ID of the currently opened PAC Period */
28 /* 4. duplicate_open_period: TRUE if another user is simultaneously */
29 /* opening this period */
30 /* 5. undefined_cost_groups: TRUE if no cost groups are defined for */
31 /* the LE */
32 /* 6. commit_complete: TRUE if the periods has been opened */
33 /* successfully. */
34 /* **************************************************************************** */
35
36
37
38
39 PROCEDURE validate_open_period(
40 l_entity_id IN NUMBER,
41 l_cost_type_id IN NUMBER,
42 l_user_id IN NUMBER,
43 l_login_id IN NUMBER,
44 open_period_name IN VARCHAR2,
45 open_period_num IN NUMBER,
46 open_period_year IN NUMBER,
47 open_period_set_name IN VARCHAR2,
48 open_period_type IN VARCHAR2,
49 last_scheduled_close_date IN OUT NOCOPY DATE,
50 l_period_end_date IN DATE,
51
52 prior_open_period OUT NOCOPY BOOLEAN,
53 improper_order OUT NOCOPY BOOLEAN,
54 new_pac_period_id OUT NOCOPY NUMBER,
55 duplicate_open_period OUT NOCOPY BOOLEAN,
56 undefined_cost_groups OUT NOCOPY BOOLEAN,
57 user_defined_error OUT NOCOPY BOOLEAN,
58 commit_complete OUT NOCOPY BOOLEAN
59
60 ) IS
61
62
63 /* **************************************************************************** */
64 /* This section defines the local variables for the open procedure */
65 /* **************************************************************************** */
66
67 low_period_id NUMBER;
68 period_count NUMBER;
69 first_period_to_be_opened BOOLEAN;
70 dummy_id NUMBER;
71 current_cost_group_id NUMBER;
72 no_cost_groups NUMBER;
73 phase_count NUMBER;
74 no_available_cost_group BOOLEAN;
75 proper_period_name VARCHAR2(15);
76 proper_period_num NUMBER;
77 proper_period_year NUMBER;
78 dummy_date DATE;
79 distributions_flag VARCHAR2(1);
80
81
82 /* **************************************************************************** */
83 /* Cursor to check whether this is the first period beging opened for a */
84 /* particular legal entity and cost type combination */
85 /* **************************************************************************** */
86
87 CURSOR first_period_cur IS
88 select
89 count(1)
90 from cst_pac_periods
91 where legal_entity = l_entity_id
92 and cost_type_id = l_cost_type_id
93 AND rownum < 2;
94
95
96 /* **************************************************************************** */
97 /* Cursor to check whether there are any current open periods for the legal */
98 /* entity and cost type combination */
99 /* **************************************************************************** */
100
101 CURSOR prior_period_open_cur IS
102 select pac_period_id
103 from cst_pac_periods
104 where
105 legal_entity = l_entity_id
106 and cost_type_id = l_cost_type_id
107 and pac_period_id NOT in
108 (select pac_period_id
109 from cst_pac_periods
110 where legal_entity = l_entity_id
111 and cost_type_id = l_cost_type_id
112 and open_flag = 'N'
113 and period_close_date IS NOT NULL);
114
115
116 /* **************************************************************************** */
117 /* Cursor to check this is the next period that should be opened as per */
118 /* the calender defined in the set of books */
119 /* **************************************************************************** */
120
121
122 CURSOR proper_period_to_open_cur IS
123 select end_date, period_name, period_year, period_num
124 from gl_periods gp
125 WHERE gp.ADJUSTMENT_PERIOD_FLAG = 'N'
126 and gp.period_set_name = open_period_set_name
127 and gp.end_date > last_scheduled_close_date
128 and gp.period_type = open_period_type
129 and gp.end_date = (
130 select min(gp1.end_date)
131 from gl_periods gp1
132 where gp1.ADJUSTMENT_PERIOD_FLAG = 'N'
133 and gp1.period_set_name = open_period_set_name
134 and gp1.period_type = open_period_type
135 and gp1.end_date > last_scheduled_close_date );
136
137
138 l_stmt_num NUMBER;
139 l_api_name CONSTANT VARCHAR2(30) := 'validate_open_period';
140 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
141 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
142
143 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
144 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
145 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
146 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
147 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
148 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
149
150 BEGIN
151
152
153 IF (l_pLog) THEN
154 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
155 l_module || '.begin',
156 l_api_name || ' <<< Parameters:
157 l_entity_id = ' || l_entity_id || '
158 l_cost_type_id = ' || l_cost_type_id || '
159 open_period_name = ' || open_period_name || '
160 open_period_num = ' || open_period_num || '
161 open_period_year = ' || open_period_year || '
162 open_period_set_name = ' || open_period_set_name || '
163 open_period_type = ' || open_period_type || '
164 l_period_end_date = ' || l_period_end_date || '
165 last_scheduled_close_date = ' || last_scheduled_close_date);
166
167 END IF;
168
169 /* **************************************************************************** */
170 /* Initialize all local and OUT params of the open procedure */
171 /* **************************************************************************** */
172 l_stmt_num := 0;
173 low_period_id := 0;
174 period_count := 0;
175 first_period_to_be_opened := false;
176 commit_complete := false;
177 duplicate_open_period := false;
178 prior_open_period := false;
179 current_cost_group_id := 0;
180 no_cost_groups := 0;
181 phase_count := 0;
182 undefined_cost_groups := false;
183 new_pac_period_id := 0;
184 dummy_id := 0;
185 improper_order := false;
186 proper_period_name := NULL;
187 dummy_date := NULL;
188 proper_period_num := 0;
189 proper_period_year := 0;
190 distributions_flag := 'N';
191 user_defined_error := false;
192
193 /* **************************************************************************** */
194 /* Section below checks whether this is the first period being opened for this */
195 /* LE-CT combination. If YES => first_period_to_be_opened = TRUE */
196 /* If NO => first_period_to_be_opened = FALSE */
197 /* **************************************************************************** */
198
199
200 open first_period_cur;
201 fetch first_period_cur into period_count;
202
203 if (period_count = 0) then
204 first_period_to_be_opened := true;
205 else
206 first_period_to_be_opened := false;
207 end if;
208 close first_period_cur;
209
210 /* **************************************************************************** */
211 /* If this is not the first periods being opened for the LE-CT combination... */
212 /* Check whether there are any open periods for this LE-CT */
213 /* If YES => prior_open_period = TRUE */
214 /* If NO => prior_open_period = FALSE */
215 /* **************************************************************************** */
216
217 l_stmt_num := 10;
218 if ( NOT first_period_to_be_opened ) then
219 open prior_period_open_cur;
220 fetch prior_period_open_cur into dummy_id;
221 if (prior_period_open_cur%FOUND) then
222 prior_open_period := true;
223 commit_complete := false;
224 goto procedure_end_label;
225 else
226 prior_open_period := false;
227 end if;
228 close prior_period_open_cur;
229
230
231 /* **************************************************************************** */
232 /* Check if this is the proper period tp open accourding to the calender */
233 /* defined in the set of books for the LE-CT combination */
234 /* **************************************************************************** */
235
236 open proper_period_to_open_cur;
237 fetch proper_period_to_open_cur into dummy_date, proper_period_name, proper_period_year, proper_period_num;
238 if( proper_period_name = open_period_name ) AND
239 ( proper_period_year = open_period_year ) AND
240 ( proper_period_num = open_period_num ) then
241 improper_order := false;
242 else
243 improper_order := true;
244 commit_complete := false;
245 goto procedure_end_label;
246 end if;
247
248
249 end if;
250
251
252
253 <<error_label>>
254 rollback;
255
256 /* **************************************************************************** */
257 /* In case of an error, rollback will take care of the unwanted rows in */
258 /* the cst_pac_process_phases table. However the row inserted into */
259 /* cst_pac_periods have already been commited, hence need to be explicitly */
260 /* deleted. The section below does that... */
261 /* **************************************************************************** */
262 l_stmt_num := 20;
263 delete from cst_pac_periods
264 where pac_period_id = new_pac_period_id;
265
266 commit_complete := false;
267
268
269 goto procedure_end_label;
270
271
272 <<procedure_end_label>>
273 NULL;
274
275 IF (l_pLog) THEN
276 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
277 l_module || '.end',
281 EXCEPTION
278 l_api_name || ' >>>');
279 END IF;
280
282 WHEN OTHERS THEN
283 IF (l_uLog) THEN
284 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
285 l_module || '.' || l_stmt_num,
286 SQLERRM);
287 END IF;
288 rollback;
289 user_defined_error := true;
290 commit_complete := false;
291
292 END validate_open_period;
293
294
295 PROCEDURE open_period(
296
297 l_entity_id IN NUMBER,
298 l_cost_type_id IN NUMBER,
299 l_user_id IN NUMBER,
300 l_login_id IN NUMBER,
301 open_period_name IN VARCHAR2,
302 open_period_num IN NUMBER,
303 open_period_year IN NUMBER,
304 open_period_set_name IN VARCHAR2,
305 open_period_type IN VARCHAR2,
306 last_scheduled_close_date IN OUT NOCOPY DATE,
307 l_period_end_date IN DATE,
308
309 prior_open_period OUT NOCOPY BOOLEAN,
310 improper_order OUT NOCOPY BOOLEAN,
311 new_pac_period_id OUT NOCOPY NUMBER,
312 duplicate_open_period OUT NOCOPY BOOLEAN,
313 undefined_cost_groups OUT NOCOPY BOOLEAN,
314 user_defined_error OUT NOCOPY BOOLEAN,
315 commit_complete OUT NOCOPY BOOLEAN
316
317 ) IS
318
319 /* **************************************************************************** */
320 /* This section defines the local variables for the open procedure */
321 /* **************************************************************************** */
322
323 low_period_id NUMBER;
324 period_count NUMBER;
325 first_period_to_be_opened BOOLEAN;
326 dummy_id NUMBER;
327 current_cost_group_id NUMBER;
328 no_cost_groups NUMBER;
329 phase_count NUMBER;
330 no_available_cost_group BOOLEAN;
331 proper_period_name VARCHAR2(15);
332 proper_period_num NUMBER;
333 proper_period_year NUMBER;
334 dummy_date DATE;
335 distributions_flag VARCHAR2(1);
336 transfer_cost_flag VARCHAR2(1);
337
338 /* **************************************************************************** */
339 /* Cursor to obtain the new pac period id from cst_pac_periods_s sequence */
340 /* **************************************************************************** */
341
342 CURSOR get_new_period_id_cur IS
343 select cst_pac_periods_s.nextval
344 from dual;
345
346
347 /* **************************************************************************** */
348 /* Cursor whether another user is opening this period simultaneously */
349 /* **************************************************************************** */
350
351 CURSOR check_if_duplicating_cur IS
352 select new_pac_period_id
353 from cst_pac_periods
354 where legal_entity = l_entity_id
355 and cost_type_id = l_cost_type_id
356 and period_name = open_period_name
357 and period_year = open_period_year
358 and period_num = open_period_num
359 and pac_period_id <> new_pac_period_id;
360
361 /* **************************************************************************** */
362 /* Cursor for all cost groups defined for this legal entity */
363 /* **************************************************************************** */
364
365
366 CURSOR all_cost_groups_cur IS
367 select cost_group_id
368 from cst_cost_groups ccg
369 where ccg.legal_entity = l_entity_id
370 and ccg.cost_group_type = 2
371 and NVL(ccg.disable_date, sysdate) >= sysdate;
372
373 l_stmt_num NUMBER;
374 l_api_name CONSTANT VARCHAR2(30) := 'open_period';
375 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
376 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
377
378 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
379 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
380 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
381 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
382 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
383 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
384
385 BEGIN
386
387 IF (l_pLog) THEN
388 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
389 l_module || '.begin',
390 l_api_name || ' <<< Parameters:
391 l_entity_id = ' || l_entity_id || '
392 l_cost_type_id = ' || l_cost_type_id || '
393 open_period_name = ' || open_period_name || '
394 open_period_num = ' || open_period_num || '
395 open_period_year = ' || open_period_year || '
396 open_period_set_name = ' || open_period_set_name || '
397 open_period_type = ' || open_period_type || '
398 l_period_end_date = ' || l_period_end_date || '
399 last_scheduled_close_date = ' || last_scheduled_close_date);
400 END IF;
401
402
403 /* **************************************************************************** */
404 /* Initialize all local and OUT params of the open procedure */
405 /* **************************************************************************** */
406 l_stmt_num := 0;
407 low_period_id := 0;
408 period_count := 0;
409 first_period_to_be_opened := false;
410 commit_complete := false;
411 duplicate_open_period := false;
412 prior_open_period := false;
413 current_cost_group_id := 0;
414 no_cost_groups := 0;
415 phase_count := 0;
416 undefined_cost_groups := false;
417 new_pac_period_id := 0;
418 dummy_id := 0;
419 improper_order := false;
420 proper_period_name := NULL;
421 dummy_date := NULL;
422 proper_period_num := 0;
423 proper_period_year := 0;
424 distributions_flag := 'N';
425 user_defined_error := false;
426
427 /* **************************************************************************** */
428 /* Validate period to be opened */
429 /* **************************************************************************** */
430
431 CSTPPPSC.validate_open_period(
432 l_entity_id,
433 l_cost_type_id,
434 l_user_id,
435 l_login_id,
436 open_period_name,
437 open_period_num,
438 open_period_year,
439 open_period_set_name,
440 open_period_type,
441 last_scheduled_close_date,
442 l_period_end_date,
443 prior_open_period,
444 improper_order,
445 new_pac_period_id,
446 duplicate_open_period,
447 undefined_cost_groups,
448 user_defined_error,
449 commit_complete
450 );
451
452 IF ( (prior_open_period = true) OR
453 (improper_order = true) OR
454 (duplicate_open_period = true) OR
455 (user_defined_error = true) OR
456 (undefined_cost_groups = true)) THEN
457
461
458 commit_complete := false;
459 goto procedure_end_label;
460 END IF;
462
463 /* **************************************************************************** */
464 /* To obtain the new pac period id for the period being opened from */
465 /* a sequence */
466 /* **************************************************************************** */
467 l_stmt_num := 10;
468 open get_new_period_id_cur;
469 fetch get_new_period_id_cur into new_pac_period_id;
470 if (get_new_period_id_cur%NOTFOUND) then
471 new_pac_period_id := 0;
472 commit_complete := false;
473 goto procedure_end_label;
474 end if;
475 close get_new_period_id_cur;
476
477 /* **************************************************************************** */
478 /* To obtain the Distributions Flag for the LE-CT */
479 /* **************************************************************************** */
480
481 distributions_flag := 'N';
482 transfer_cost_flag := 'N';
483
484 l_stmt_num := 20;
485 SELECT NVL(CREATE_ACCT_ENTRIES,'N')
486 , nvl(transfer_cost_flag,'N')
487 INTO distributions_flag
488 , transfer_cost_flag
489 FROM CST_LE_COST_TYPES
490 WHERE LEGAL_ENTITY = l_entity_id
491 AND COST_TYPE_ID = l_cost_type_id
492 AND PRIMARY_COST_METHOD > 2;
493
494 /* **************************************************************************** */
495 /* START OPENING THE PERIOD. The steps are... */
496 /* 1. Insert into cst_pac_periods form gl_periods table */
497 /* 2. For each an every cost group defined in the legal entity */
498 /* Insert five rows for five process statuses into */
499 /* cst_pac_process_phases */
500 /* **************************************************************************** */
501
502
503
504 /* **************************************************************************** */
505 /* Insert a single row for the pac period being opened into cst_pac_periods */
506 /* Insert the rows with 'P' (Pending) status and period close date = sysdate */
507 /* **************************************************************************** */
508
509 l_stmt_num := 30;
510 INSERT INTO cst_pac_periods (
511 pac_period_id,
512 legal_entity,
513 cost_type_id,
514 period_start_date,
515 period_end_date,
516 open_flag,
517 period_year,
518 period_num,
519 period_name,
520 period_set_name,
521 period_close_date,
522 last_update_date,
523 last_updated_by,
524 creation_date,
525 created_by,
526 last_update_login )
527 SELECT new_pac_period_id,
528 l_entity_id,
529 l_cost_type_id,
530 gp.start_date,
531 gp.end_date,
532 'P',
533 gp.period_year,
534 gp.period_num,
535 gp.period_name,
536 gp.period_set_name,
537 SYSDATE,
538 SYSDATE,
539 l_user_id,
540 SYSDATE,
541 l_user_id,
542 -1
543 FROM gl_periods gp
544 WHERE gp.period_name = open_period_name
545 AND gp.period_num = open_period_num
546 AND gp.period_year = open_period_year
547 AND gp.period_set_name = (select gsob.period_set_name
548 from gl_sets_of_books gsob, cst_le_cost_types clct
549 where gsob.set_of_books_id = clct.set_of_books_id
550 and clct.legal_entity = l_entity_id
551 and clct.cost_type_id = l_cost_type_id
552 and clct.primary_cost_method > 2)
553 AND (gp.period_name, gp.period_num, gp.period_year) NOT IN
554 (select period_name, period_num, period_year
555 from cst_pac_periods
556 where legal_entity = l_entity_id
557 and cost_type_id = l_cost_type_id);
558
559 IF(SQL%ROWCOUNT = 0) THEN
560 goto procedure_end_label;
561 END IF;
562
563
564 COMMIT;
565 SAVEPOINT before_process_phases_table;
566
570
567 /* **************************************************************************** */
568 /* Open cursor for all cost groups defined in the legal entity */
569 /* **************************************************************************** */
571
572 no_available_cost_group := true;
573 no_cost_groups := 0;
574
575 l_stmt_num := 40;
576 open all_cost_groups_cur;
577 LOOP
578 current_cost_group_id := 0;
579 fetch all_cost_groups_cur into current_cost_group_id;
580 if (all_cost_groups_cur%NOTFOUND) then
581 if(no_available_cost_group) then
582 /* No cost groups defined */
583 undefined_cost_groups := true;
584 commit_complete := false;
585 goto error_label;
586 else
587 /* All cost group processing done */
588 undefined_cost_groups := false;
589 goto check_duplicate_label;
590 end if;
591 end if;
592
593
594 /* Start Phase Count =1 and loop for all five phases */
595 no_available_cost_group := false;
596 phase_count := 0;
597
598
599 /* **************************************************************************** */
600 /* Loop for five process phases */
601 /* **************************************************************************** */
602
603 LOOP
604
605 /* Increment Phase_count by 1 */
606 phase_count := phase_count +1;
607 no_cost_groups := phase_count;
608
609
610 /* **************************************************************************** */
611 /* Insert a row for each and every cost group and phases into */
612 /* cst_pac_process_phases table */
613 /* **************************************************************************** */
614
615 l_stmt_num := 50;
616 INSERT INTO cst_pac_process_phases (
617 pac_period_id,
618 cost_group_id,
619 process_phase,
620 process_status,
621 process_date,
622 last_update_date,
623 last_updated_by,
624 creation_date,
625 created_by,
626 last_update_login )
627 SELECT
628 new_pac_period_id,
629 current_cost_group_id,
630 phase_count,
631 DECODE(phase_count,6,DECODE(distributions_flag,'Y',1,0), 7,DECODE(transfer_cost_flag,'Y',1,0),8,DECODE(transfer_cost_flag,'Y',1,0),1),
632 NULL,
633 SYSDATE,
634 l_user_id,
635 SYSDATE,
636 l_user_id,
637 -1
638 FROM dual;
639
640 if (phase_count = 8) then
641 goto cppp_insert_done_label;
642 end if;
643
644 END LOOP;
645
646
647
648 <<cppp_insert_done_label>>
649 NULL;
650
651 END LOOP;
652 close all_cost_groups_cur;
653
654
655
656 <<check_duplicate_label>>
657
658 /* **************************************************************************** */
659 /* This section is to check whether another is simultaneously trying to open */
660 /* this period. This section catches such a condition */
661 /* If YES => duplicate_open_period = TRUE */
662 /* If NO => duplicate_open_period = FALSE */
663 /* **************************************************************************** */
664 l_stmt_num := 60;
665 open check_if_duplicating_cur;
666 fetch check_if_duplicating_cur into dummy_id;
667 if (check_if_duplicating_cur%FOUND) then
668 /* Duplicate open period found */
669 duplicate_open_period := true;
670 commit_complete := false;
671 rollback to before_process_phases_table;
672 delete from cst_pac_process_phases where pac_period_id = dummy_id;
673 goto error_label;
674 else
675 /* No Duplicating rows found */
679
676 duplicate_open_period := false;
677 end if;
678 close check_if_duplicating_cur;
680
681 /* **************************************************************************** */
682 /* Update the the new pac period row in cst_pac_periods with open_flag = 'Y' */
683 /* and the period close date = NULL, this declaring the period as open */
684 /* **************************************************************************** */
685 l_stmt_num := 70;
686 UPDATE cst_pac_periods
687 SET open_flag = 'Y',
688 period_close_date = NULL,
689 last_update_date = trunc(sysdate),
690 last_updated_by = l_user_id,
691 last_update_login = l_login_id
692 WHERE pac_period_id = new_pac_period_id;
693
694
695 <<sucess_label>>
696 commit;
697
698 /* **************************************************************************** */
699 /* Reset the last_scheduled_close_date OUT param with the period_end_date */
700 /* of the newly opened period */
701 /* **************************************************************************** */
702 l_stmt_num := 80;
703 SELECT NVL(MAX(period_end_date),sysdate)
704 INTO last_scheduled_close_date
705 FROM cst_pac_periods
706 WHERE legal_entity = l_entity_id
707 AND cost_type_id = l_cost_type_id;
708
709 commit_complete := true;
710
711 goto procedure_end_label;
712
713
714 <<error_label>>
715 rollback;
716
717 /* **************************************************************************** */
718 /* In case of an error, rollback will take care of the unwanted rows in */
719 /* the cst_pac_process_phases table. However the row inserted into */
720 /* cst_pac_periods have already been commited, hence need to be explicitly */
721 /* deleted. The section below does that... */
722 /* **************************************************************************** */
723
724 delete from cst_pac_periods
725 where pac_period_id = new_pac_period_id;
726
727 commit_complete := false;
728
729
730 goto procedure_end_label;
731
732
733 <<procedure_end_label>>
734 NULL;
735
736 IF (l_pLog) THEN
737 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
738 l_module || '.end',
739 l_api_name || ' >>>');
740 END IF;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 IF (l_uLog) THEN
745 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
746 l_module || '.' || l_stmt_num,
747 SQLERRM);
748 END IF;
749 rollback;
750 user_defined_error := true;
751 commit_complete := false;
752
753 END open_period;
754
755
756
757
758 /* **************************************************************************** */
759 /* **************************************************************************** */
760 /* **************************************************************************** */
761
762
763
764 /* **************************************************************************** */
765 /* This procedure is called from PAC Period form to close a period with proper */
766 /* validations. Below are a description of the various IN and OUT params */
767 /* */
768 /* IN Params: 1. l_entity_id: legal entity id */
769 /* 2. l_cost_type_id; Cost type ID */
770 /* 3. closing_pac_period_id : PAC period id of the period being */
771 /* closed */
772 /* 4. closing_end_date: Period end date of the PAC Period being */
773 /* closed */
774 /* 5. l_user_id: User ID */
775 /* 6. l_login_id : Login ID */
776 /* IN OUT Params: */
777 /* 1. last_scheduled_close_date: It is a user defined param which */
778 /* holds the value of the max(end_date) of all the periods in */
779 /* in cst_pac_periods table for a particular LE-CT */
780 /* OUT Params: */
781 /* 1. end_date_is_passed: TRUE of the user is trying to close */
782 /* whose period end date is in future */
783 /* 2. incomplete_processing: TRUE if the process status of all the */
784 /* cost groups for the period are not completely processes */
788 /* 4. prompt_to_reclose: TRUE id another is trying to close this */
785 /* 3. rerun_processor: TRUE if the processor has been in an */
786 /* intermediate date and should be rerun to process txn after */
787 /* that date till the period end date */
789 /* period simultaneously */
790 /* 5. undefined_cost_groups: TRUE if no cost groups are defined */
791 /* for the legal entity */
792 /* 6. commit_complete: TRUE is the period has been sucessfully */
793 /* closed */
794 /* **************************************************************************** */
795
796
797
798 PROCEDURE validate_close_period (
799 l_entity_id IN NUMBER,
800 l_cost_type_id IN NUMBER,
801 closing_pac_period_id IN NUMBER,
802 closing_period_type IN VARCHAR2,
803 closing_end_date IN DATE,
804 l_user_id IN NUMBER,
805 l_login_id IN NUMBER,
806
807 last_scheduled_close_date IN OUT NOCOPY DATE,
808 end_date_is_passed OUT NOCOPY BOOLEAN,
809 incomplete_processing OUT NOCOPY BOOLEAN,
810 pending_transactions OUT NOCOPY BOOLEAN,
811 rerun_processor OUT NOCOPY BOOLEAN,
812 prompt_to_reclose OUT NOCOPY BOOLEAN,
813 undefined_cost_groups OUT NOCOPY BOOLEAN,
814 backdated_transactions OUT NOCOPY BOOLEAN,
815 perpetual_periods_open OUT NOCOPY BOOLEAN,
816 ap_period_open OUT NOCOPY BOOLEAN,
817 ar_period_open OUT NOCOPY BOOLEAN,
818 cogsgen_phase2_notrun OUT NOCOPY BOOLEAN,
819 cogsgen_phase3_notrun OUT NOCOPY BOOLEAN,
820 user_defined_error OUT NOCOPY BOOLEAN,
821 commit_complete OUT NOCOPY BOOLEAN
822 ) IS
823
824
825 /* **************************************************************************** */
826 /* This section declares all the local variable for the close procedure */
827 /* **************************************************************************** */
828
829 dummy_id NUMBER;
830 no_cost_groups NUMBER;
831 current_cost_group_id NUMBER;
832 no_cost_groups_available BOOLEAN;
833 count_rows NUMBER;
834 rerun_process_date DATE;
835
836 -- Variables for Revenue / COGS Matching checks
837 l_effective_period_num NUMBER;
838 l_ledger_id NUMBER;
839 l_create_acct_entries VARCHAR2(1);
840 l_ar_period_status VARCHAR2(1);
841 l_return_status VARCHAR2(1);
842 l_msg_count NUMBER;
843 l_msg_data VARCHAR2(1000);
844 l_phase2_required NUMBER;
845 l_phase3_required NUMBER;
846
847
848 /* **************************************************************************** */
849 /* Cursor for checking open AP periods */
850 /* **************************************************************************** */
851 CURSOR ap_period_open_cur( p_entity_id NUMBER,
852 p_cost_type_id NUMBER,
853 p_closing_end_date DATE) IS
854 SELECT count(1)
855 FROM gl_period_statuses gps
856 WHERE gps.application_id = 200
857 AND gps.closing_status <> 'C'
858 AND trunc(gps.end_date) = trunc(p_closing_end_date)
859 AND gps.set_of_books_id = ( SELECT distinct clct.set_of_books_id
860 FROM cst_le_cost_types clct
861 WHERE clct.cost_type_id = p_cost_type_id
862 AND clct.legal_entity = p_entity_id
863 )
864 AND rownum < 2;
865
866
867 /* **************************************************************************** */
868 /* Cursor for checking open perpetual periods */
869 /* **************************************************************************** */
870 CURSOR perpetual_periods_cur(l_current_cost_group_id NUMBER,
871 l_closing_pac_period_id NUMBER) IS
872 SELECT count(1)
873 FROM org_acct_periods
874 WHERE open_flag IN ('Y','P')
875 AND trunc(schedule_close_date) <=
876 (select trunc(period_end_date)
877 from cst_pac_periods
881 WHERE ccga.cost_group_id = l_current_cost_group_id
878 where pac_period_id = l_closing_pac_period_id )
879 AND organization_id IN ( SELECT ccga.organization_id
880 FROM cst_cost_group_assignments ccga
882 )
883 AND rownum < 2;
884
885
886 /* **************************************************************************** */
887 /* Cursor for checking backdated txns in MMT */
888 /* **************************************************************************** */
889 CURSOR back_dated_mmt_cur( l_current_cost_group_id NUMBER,
890 l_closing_pac_period_id NUMBER,
891 l_entity_id NUMBER,
892 l_cost_type_id NUMBER) IS
893 SELECT count(1)
894 FROM mtl_material_transactions mmt
895 WHERE mmt.creation_date > ( SELECT MIN(cppp.process_date)
896 FROM cst_pac_process_phases cppp
897 WHERE
898 (( cppp.process_phase <= 5
899 AND cppp.process_upto_date IS NOT NULL)
900 OR
901 ( cppp.process_phase = 6
902 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
903 FROM CST_LE_COST_TYPES
904 WHERE LEGAL_ENTITY = l_entity_id
905 AND COST_TYPE_ID = l_cost_type_id
906 AND PRIMARY_COST_METHOD > 2
907 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
908 AND cppp.process_upto_date IS NOT NULL
909 ))
910 AND cppp.pac_period_id = l_closing_pac_period_id
911 /* bug 2658552 */
912 AND cppp.cost_group_id = l_current_cost_group_id
913 )
914 AND mmt.organization_id IN
915 (select ccga.organization_id
916 from cst_cost_group_assignments ccga
917 where ccga.cost_group_id = l_current_cost_group_id)
918 AND mmt.transaction_date >=
919 (select trunc(period_start_date)
920 from cst_pac_periods
921 where pac_period_id = l_closing_pac_period_id )
922 AND mmt.transaction_date <=
923 (select (trunc(period_end_date) + 0.99999)
924 from cst_pac_periods
925 where pac_period_id = l_closing_pac_period_id )
926 AND rownum < 2;
927
928 /* **************************************************************************** */
929 /* Cursor for checking backdated txns in WT */
930 /* **************************************************************************** */
931 CURSOR back_dated_wt_cur( l_current_cost_group_id NUMBER,
932 l_closing_pac_period_id NUMBER,
933 l_entity_id NUMBER,
934 l_cost_type_id NUMBER) IS
935 SELECT count(1)
936 FROM wip_transactions wt
937 WHERE wt.creation_date > ( SELECT MIN(cppp.process_date)
938 FROM cst_pac_process_phases cppp
939 WHERE
940 (( cppp.process_phase <= 5
941 AND cppp.process_upto_date IS NOT NULL)
942 OR
943 ( cppp.process_phase = 6
944 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
945 FROM CST_LE_COST_TYPES
946 WHERE LEGAL_ENTITY = l_entity_id
947 AND COST_TYPE_ID = l_cost_type_id
948 AND PRIMARY_COST_METHOD > 2
949 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
950 AND cppp.process_upto_date IS NOT NULL
951 ))
952 AND cppp.pac_period_id = l_closing_pac_period_id
953 /* bug 2658552 */
954 AND cppp.cost_group_id = l_current_cost_group_id
955 )
956 AND wt.organization_id IN
957 (select ccga.organization_id
961 (select trunc(period_start_date)
958 from cst_cost_group_assignments ccga
959 where ccga.cost_group_id = l_current_cost_group_id)
960 AND wt.transaction_date >=
962 from cst_pac_periods
963 where pac_period_id = l_closing_pac_period_id )
964 AND wt.transaction_date <=
965 (select (trunc(period_end_date) + 0.99999)
966 from cst_pac_periods
967 where pac_period_id = l_closing_pac_period_id )
968 AND rownum < 2;
969
970
971 /* **************************************************************************** */
972 /* Cursor for checking backdated txns in RT */
973 /* **************************************************************************** */
974 CURSOR back_dated_rt_cur( l_current_cost_group_id NUMBER,
975 l_closing_pac_period_id NUMBER,
976 l_entity_id NUMBER,
977 l_cost_type_id NUMBER) IS
978 SELECT count(1)
979 FROM rcv_transactions rt
980 WHERE rt.creation_date > ( SELECT MIN(cppp.process_date)
981 FROM cst_pac_process_phases cppp
982 WHERE
983 (( cppp.process_phase <= 5
984 AND cppp.process_upto_date IS NOT NULL)
985 OR
986 ( cppp.process_phase = 6
987 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
988 FROM CST_LE_COST_TYPES
989 WHERE LEGAL_ENTITY = l_entity_id
990 AND COST_TYPE_ID = l_cost_type_id
991 AND PRIMARY_COST_METHOD > 2
992 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
993 AND cppp.process_upto_date IS NOT NULL
994 ))
995 AND cppp.pac_period_id = l_closing_pac_period_id
996 /* bug 2658552 */
997 AND cppp.cost_group_id = l_current_cost_group_id
998 )
999 AND rt.organization_id IN
1000 (select ccga.organization_id
1001 from cst_cost_group_assignments ccga
1002 where ccga.cost_group_id = l_current_cost_group_id)
1003 AND rt.transaction_date >=
1004 (select trunc(period_start_date)
1005 from cst_pac_periods
1006 where pac_period_id = l_closing_pac_period_id )
1007 AND rt.transaction_date <=
1008 (select (trunc(period_end_date) + 0.99999)
1009 from cst_pac_periods
1010 where pac_period_id = l_closing_pac_period_id )
1011 AND rownum < 2;
1012
1013 /* **************************************************************************** */
1014 /* Cursor for checking backdated txns in RAE */
1015 /* **************************************************************************** */
1016 CURSOR back_dated_rae_cur( l_current_cost_group_id NUMBER,
1017 l_closing_pac_period_id NUMBER,
1018 l_entity_id NUMBER,
1019 l_cost_type_id NUMBER) IS
1020 SELECT count(1)
1021 FROM rcv_accounting_events rae
1022 WHERE rae.creation_date > ( SELECT MIN(cppp.process_date)
1023 FROM cst_pac_process_phases cppp
1024 WHERE
1025 (( cppp.process_phase <= 5
1026 AND cppp.process_upto_date IS NOT NULL)
1027 OR
1028 ( cppp.process_phase = 6
1029 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
1030 FROM CST_LE_COST_TYPES
1031 WHERE LEGAL_ENTITY = l_entity_id
1032 AND COST_TYPE_ID = l_cost_type_id
1033 AND PRIMARY_COST_METHOD > 2
1034 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
1035 AND cppp.process_upto_date IS NOT NULL
1036 ))
1037 AND cppp.pac_period_id = l_closing_pac_period_id
1038 AND cppp.cost_group_id = l_current_cost_group_id
1039 )
1043 where ccga.cost_group_id = l_current_cost_group_id)
1040 AND rae.organization_id IN
1041 (select ccga.organization_id
1042 from cst_cost_group_assignments ccga
1044 AND rae.transaction_date >=
1045 (select trunc(period_start_date)
1046 from cst_pac_periods
1047 where pac_period_id = l_closing_pac_period_id )
1048 AND rae.transaction_date <=
1049 (select (trunc(period_end_date) + 0.99999)
1050 from cst_pac_periods
1051 where pac_period_id = l_closing_pac_period_id )
1052 AND rae.event_type_id IN (7,8, 9, 10)
1053 AND rownum < 2;
1054
1055
1056 /* **************************************************************************** */
1057 /* Cursor for checking backdated txns in LCM ADJ TXN */
1058 /* **************************************************************************** */
1059 CURSOR back_dated_lcadj_cur( l_current_cost_group_id NUMBER,
1060 l_closing_pac_period_id NUMBER,
1061 l_entity_id NUMBER,
1062 l_cost_type_id NUMBER) IS
1063 SELECT count(1)
1064 FROM cst_lc_adj_transactions clat
1065 WHERE clat.creation_date > ( SELECT MIN(cppp.process_date)
1066 FROM cst_pac_process_phases cppp
1067 WHERE
1068 (( cppp.process_phase <= 5
1069 AND cppp.process_upto_date IS NOT NULL)
1070 OR
1071 ( cppp.process_phase = 6
1072 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
1073 FROM CST_LE_COST_TYPES
1074 WHERE LEGAL_ENTITY = l_entity_id
1075 AND COST_TYPE_ID = l_cost_type_id
1076 AND PRIMARY_COST_METHOD > 2
1077 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
1078 AND cppp.process_upto_date IS NOT NULL
1079 ))
1080 AND cppp.pac_period_id = l_closing_pac_period_id
1081 AND cppp.cost_group_id = l_current_cost_group_id
1082 )
1083 AND clat.organization_id IN
1084 (select ccga.organization_id
1085 from cst_cost_group_assignments ccga
1086 where ccga.cost_group_id = l_current_cost_group_id)
1087 AND clat.transaction_date >=
1088 (select trunc(period_start_date)
1089 from cst_pac_periods
1090 where pac_period_id = l_closing_pac_period_id )
1091 AND clat.transaction_date <=
1092 (select (trunc(period_end_date) + 0.99999)
1093 from cst_pac_periods
1094 where pac_period_id = l_closing_pac_period_id )
1095 AND rownum < 2;
1096
1097
1098 /* **************************************************************************** */
1099 /* Cursor for all cost groups defined in the legal entity */
1100 /* **************************************************************************** */
1101
1102 CURSOR all_cost_groups_cur IS
1103 select cost_group_id
1104 from cst_cost_groups ccg
1105 where ccg.legal_entity = l_entity_id
1106 and ccg.cost_group_type = 2
1107 and NVL(ccg.disable_date, sysdate) >= sysdate
1108 and ccg.cost_group_id IN (
1109 SELECT distinct cost_group_id
1110 FROM cst_cost_group_assignments
1111 WHERE legal_entity = l_entity_id );
1112
1113 /* **************************************************************************** */
1114 /* Cursor to check for pending txn in MMTT */
1115 /* **************************************************************************** */
1116
1117 CURSOR cur_mmtt(l_current_cost_group_id NUMBER,l_closing_pac_period_id NUMBER) IS
1118 SELECT count(1)
1119 FROM mtl_material_transactions_temp mmtt
1120 WHERE NVL(mmtt.transaction_status,0) <> 2
1121 AND mmtt.organization_id IN
1122 (select ccga.organization_id
1123 from cst_cost_group_assignments ccga
1124 where ccga.cost_group_id = l_current_cost_group_id)
1125 AND mmtt.transaction_date >=
1126 (select trunc(period_start_date)
1127 from cst_pac_periods
1128 where pac_period_id = l_closing_pac_period_id )
1129 AND mmtt.transaction_date <=
1130 (select (trunc(period_end_date)+0.99999)
1134
1131 from cst_pac_periods
1132 where pac_period_id = l_closing_pac_period_id )
1133 AND rownum < 2;
1135 /* **************************************************************************** */
1136 /* Cursor to check for pending txn in MTI */
1137 /* **************************************************************************** */
1138
1139 CURSOR cur_mti(l_current_cost_group_id NUMBER,l_closing_pac_period_id NUMBER) IS
1140 SELECT count(1)
1141 FROM mtl_transactions_interface mti
1142 WHERE mti.organization_id IN
1143 (select ccga.organization_id
1144 from cst_cost_group_assignments ccga
1145 where ccga.cost_group_id = l_current_cost_group_id)
1146 AND mti.transaction_date >=
1147 (select trunc(period_start_date)
1148 from cst_pac_periods
1149 where pac_period_id = l_closing_pac_period_id )
1150 AND mti.transaction_date <=
1151 (select (trunc(period_end_date)+0.99999)
1152 from cst_pac_periods
1153 where pac_period_id = l_closing_pac_period_id )
1154 AND rownum < 2;
1155
1156 /* **************************************************************************** */
1157 /* Cursor to check for pending txn in WCTI */
1158 /* **************************************************************************** */
1159
1160 CURSOR cur_wcti(l_current_cost_group_id NUMBER,l_closing_pac_period_id NUMBER) IS
1161 SELECT count(1)
1162 FROM wip_cost_txn_interface wcti
1163 WHERE wcti.organization_id IN
1164 (select ccga.organization_id
1165 from cst_cost_group_assignments ccga
1166 where ccga.cost_group_id = l_current_cost_group_id)
1167 AND wcti.transaction_date >=
1168 (select trunc(period_start_date)
1169 from cst_pac_periods
1170 where pac_period_id = l_closing_pac_period_id )
1171 AND wcti.transaction_date <=
1172 (select (trunc(period_end_date)+0.99999)
1173 from cst_pac_periods
1174 where pac_period_id = l_closing_pac_period_id )
1175 AND rownum < 2;
1176
1177 /* **************************************************************************** */
1178 /* Cursor to check for pending txn in RTI */
1179 /* **************************************************************************** */
1180
1181 CURSOR cur_rti(l_current_cost_group_id NUMBER,l_closing_pac_period_id NUMBER) IS
1182 SELECT count(1)
1183 FROM rcv_transactions_interface rti
1184 WHERE rti.to_organization_code IN
1185 (select mp.organization_code
1186 from cst_cost_group_assignments ccga,
1187 mtl_parameters mp
1188 where ccga.cost_group_id = l_current_cost_group_id
1189 and ccga.organization_id = mp.organization_id)
1190 AND rti.transaction_date >=
1191 (select trunc(period_start_date)
1192 from cst_pac_periods
1193 where pac_period_id = l_closing_pac_period_id )
1194 AND rti.transaction_date <=
1195 (select (trunc(period_end_date)+0.99999)
1196 from cst_pac_periods
1197 where pac_period_id = l_closing_pac_period_id )
1198 AND rownum < 2;
1199
1200 /* **************************************************************************** */
1201 /* Cursor to check for pending txn in LCM INTERFACE */
1202 /* **************************************************************************** */
1203 CURSOR cur_lci(l_current_cost_group_id NUMBER,l_closing_pac_period_id NUMBER) IS
1204 SELECT count(1)
1205 FROM cst_lc_adj_interface lci
1206 WHERE lci.organization_id IN
1207 (select ccga.organization_id
1208 from cst_cost_group_assignments ccga
1209 where ccga.cost_group_id = l_current_cost_group_id)
1210 AND lci.transaction_date >=
1211 (select trunc(period_start_date)
1212 from cst_pac_periods
1213 where pac_period_id = l_closing_pac_period_id )
1214 AND lci.transaction_date <=
1215 (select (trunc(period_end_date)+0.99999)
1216 from cst_pac_periods
1217 where pac_period_id = l_closing_pac_period_id )
1218 AND rownum < 2;
1219
1220
1221 l_err_msg VARCHAR2(255);
1222 l_stmt_num NUMBER;
1223
1224 l_api_name CONSTANT VARCHAR2(30) := 'validate_close_period';
1225 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1226 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1227
1228 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1229 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1230 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1231 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1235
1232 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1233 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1234
1236 BEGIN
1237
1238 IF (l_pLog) THEN
1239 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1240 l_module || '.begin',
1241 l_api_name || ' <<< Parameters:
1242 l_entity_id = ' || l_entity_id || '
1243 l_cost_type_id = ' || l_cost_type_id || '
1244 closing_pac_period_id = ' || closing_pac_period_id || '
1245 closing_period_type = ' || closing_period_type || '
1246 closing_end_date = ' || closing_end_date );
1247 END IF;
1248
1249 /* **************************************************************************** */
1250 /* This section initializes all local and OUT Params of the close procedure */
1251 /* **************************************************************************** */
1252
1253 dummy_id := 0;
1254 end_date_is_passed := false;
1255 prompt_to_reclose := false;
1256 incomplete_processing := false;
1257 pending_transactions := false;
1258 current_cost_group_id := 0;
1259 no_cost_groups := 0;
1260 undefined_cost_groups := false;
1261 rerun_processor := false;
1262 commit_complete := false;
1263 no_cost_groups_available:= false;
1264 backdated_transactions := false;
1265 perpetual_periods_open := false;
1266 ap_period_open := false;
1267 ar_period_open := false;
1268 cogsgen_phase2_notrun := false;
1269 cogsgen_phase3_notrun := false;
1270
1271
1272 user_defined_error := false;
1273 count_rows := 0;
1274 rerun_process_date := trunc(closing_end_date) +1;
1275 l_stmt_num := 0;
1276
1277 /* **************************************************************************** */
1278 /* This section checks whether the period end date lies in future */
1279 /* if YES => end_date_is_passed = TRUE */
1280 /* if No => end_date_is_passed = FALSE */
1281 /* **************************************************************************** */
1282
1283 if (trunc(closing_end_date)+1 > SYSDATE ) then
1284 end_date_is_passed := true;
1285 commit_complete := false;
1286 goto procedure_end_label;
1287 else
1288 end_date_is_passed := false;
1289 end if;
1290
1291
1292 no_cost_groups := 0;
1293 no_cost_groups_available := true;
1294
1295
1296
1297 /* **************************************************************************** */
1298 /* Start LOOP to check whether process status = 4 (Complete) for all cost */
1299 /* in the PAC Period (check Phase 6 only if clct.CREATE_ACCT_ENTRIES='Y' */
1300 /* Logic is as follows... */
1301 /* IF process_status <> 4 => incomplete_processing = TRUE */
1302 /* IF process_status = 4 and process_date < period_end_date */
1303 /* => rerun_processor = TRUE */
1304 /* ELSE go ahead with closing this period */
1305 /* **************************************************************************** */
1306
1307 l_stmt_num := 10;
1308 open all_cost_groups_cur;
1309
1310 LOOP
1311 current_cost_group_id := 0;
1312 fetch all_cost_groups_cur into current_cost_group_id;
1313 if ( all_cost_groups_cur%NOTFOUND) then
1314 if ( no_cost_groups_available ) then
1315 /* No cost groups associated */
1316 undefined_cost_groups := true;
1317 rerun_processor := false;
1318 commit_complete := false;
1319 goto procedure_end_label;
1320 else
1321 /* All cost group processing done */
1322 undefined_cost_groups := false;
1323 rerun_processor := false;
1324 incomplete_processing := false;
1325 goto check_ar_label;
1326 end if;
1327 end if;
1328
1329 no_cost_groups_available := false;
1330 no_cost_groups := no_cost_groups + 1;
1331
1332
1333 count_rows := 0;
1334 dummy_id := 0;
1335
1336 /* Bug 3591905. The following Select statement was checking for process_phase < 5 instead of process_phase <= 5 */
1337 l_stmt_num := 20;
1338 SELECT count(1)
1339 INTO count_rows
1340 FROM cst_pac_process_phases
1341 WHERE pac_period_id = closing_pac_period_id
1342 AND cost_group_id = current_cost_group_id
1343 AND (( process_status <> 4
1344 AND process_phase <= 5
1345 )
1346 OR
1350 FROM CST_LE_COST_TYPES
1347 ( process_status <> 4
1348 AND process_phase = 6
1349 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
1351 WHERE LEGAL_ENTITY = l_entity_id
1352 AND COST_TYPE_ID = l_cost_type_id
1353 AND PRIMARY_COST_METHOD > 2
1354 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
1355 ))
1356 AND rownum < 2;
1357
1358
1359 /* Bug 3591905. The following Select statement was checking for process_phase < 5 instead of process_phase <= 5 */
1360 l_stmt_num := 30;
1361 if ( count_rows <> 0) then
1362
1363 SELECT distinct NVL(pac_period_id,0)
1364 INTO dummy_id
1365 FROM cst_pac_process_phases
1366 WHERE pac_period_id = closing_pac_period_id
1367 AND cost_group_id = current_cost_group_id
1368 AND (( process_status <> 4
1369 AND process_phase <= 5
1370 )
1371 OR
1372 ( process_status <> 4
1373 AND process_phase = 6
1374 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
1375 FROM CST_LE_COST_TYPES
1376 WHERE LEGAL_ENTITY = l_entity_id
1377 AND COST_TYPE_ID = l_cost_type_id
1378 AND PRIMARY_COST_METHOD > 2
1379 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
1380 ));
1381
1382
1383 end if;
1384
1385
1386 if ( dummy_id <> 0 ) then
1387 incomplete_processing := true;
1388 rerun_processor := false;
1389 commit_complete := false;
1390 goto procedure_end_label;
1391 else
1392
1393 /* Bug 3591905. The following Select statement was checking for process_phase < 5 instead of process_phase <= 5 */
1394 l_stmt_num := 40;
1395 count_rows := 0;
1396 rerun_process_date := trunc(closing_end_date)+1;
1397
1398 SELECT count(1)
1399 INTO count_rows
1400 FROM cst_pac_process_phases
1401 WHERE pac_period_id = closing_pac_period_id
1402 AND cost_group_id = current_cost_group_id
1403 AND (
1404 (( process_date < trunc(closing_end_date)+1
1405 AND process_phase <= 5
1406 )
1407 OR
1408 ( process_date < trunc(closing_end_date)+1
1409 AND process_phase = 6
1410 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
1411 FROM CST_LE_COST_TYPES
1412 WHERE LEGAL_ENTITY = l_entity_id
1413 AND COST_TYPE_ID = l_cost_type_id
1414 AND PRIMARY_COST_METHOD > 2
1415 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
1416 ))
1417 OR
1418 (( trunc(NVL(process_upto_date,closing_end_date-1)) < trunc(closing_end_date)
1419 AND process_phase <= 5
1420 )
1421 OR
1422 ( trunc(NVL(process_upto_date,closing_end_date-1)) < trunc(closing_end_date)
1423 AND process_phase = 6
1424 AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
1425 FROM CST_LE_COST_TYPES
1426 WHERE LEGAL_ENTITY = l_entity_id
1427 AND COST_TYPE_ID = l_cost_type_id
1428 AND PRIMARY_COST_METHOD > 2
1429 AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
1430 ))
1431 )
1432 AND rownum < 2;
1433
1434
1435 if ( count_rows <> 0 ) then
1436 incomplete_processing := false;
1437 rerun_processor := true;
1438 commit_complete := false;
1439 goto procedure_end_label;
1440 else
1441
1442 /* **********section to check perpetual periods **************** */
1443
1447 fetch perpetual_periods_cur into count_rows;
1444 count_rows := 0;
1445 open perpetual_periods_cur(current_cost_group_id,
1446 closing_pac_period_id);
1448 if(count_rows <> 0 ) then
1449 perpetual_periods_open := true;
1450 commit_complete := false;
1451 goto procedure_end_label;
1452 end if;
1453 close perpetual_periods_cur;
1454
1455 /* **********section to check pending txns **************** */
1456
1457 /* check for pending rows in MMTT */
1458 count_rows := 0;
1459 open cur_mmtt(current_cost_group_id,closing_pac_period_id);
1460 fetch cur_mmtt into count_rows;
1461 if(count_rows <> 0 ) then
1462 pending_transactions := true;
1463 commit_complete := false;
1464 goto procedure_end_label;
1465 end if;
1466 close cur_mmtt;
1467
1468 /* check for pending rows in MTI */
1469 count_rows := 0;
1470 open cur_mti(current_cost_group_id,closing_pac_period_id);
1471 fetch cur_mti into count_rows;
1472 if(count_rows <> 0 ) then
1473 pending_transactions := true;
1474 commit_complete := false;
1475 goto procedure_end_label;
1476 end if;
1477 close cur_mti;
1478
1479 /* check for pending rows in WCTI */
1480 count_rows := 0;
1481 open cur_wcti(current_cost_group_id,closing_pac_period_id);
1482 fetch cur_wcti into count_rows;
1483 if(count_rows <> 0 ) then
1484 pending_transactions := true;
1485 goto procedure_end_label;
1486 end if;
1487 close cur_wcti;
1488
1489 /* check for pending rows in RTI */
1490 count_rows := 0;
1491 open cur_rti(current_cost_group_id,closing_pac_period_id);
1492 fetch cur_rti into count_rows;
1493 if(count_rows <> 0 ) then
1494 pending_transactions := true;
1495 goto procedure_end_label;
1496 end if;
1497 close cur_rti;
1498
1499 /* check for pending rows in LCI */
1500 count_rows := 0;
1501 open cur_lci(current_cost_group_id,closing_pac_period_id);
1502 fetch cur_lci into count_rows;
1503 if(count_rows <> 0 ) then
1504 pending_transactions := true;
1505 goto procedure_end_label;
1506 end if;
1507 close cur_lci;
1508
1509 /* **********section to check backdated txns **************** */
1510 /* check for backdated txns in MMT */
1511 count_rows := 0;
1512 open back_dated_mmt_cur(current_cost_group_id,
1513 closing_pac_period_id,
1514 l_entity_id,
1515 l_cost_type_id);
1516 fetch back_dated_mmt_cur into count_rows;
1517 if(count_rows <> 0 ) then
1518 backdated_transactions := true;
1519 commit_complete := false;
1520 goto procedure_end_label;
1521 end if;
1522 close back_dated_mmt_cur;
1523
1524 /* check for backdated txns in WT */
1525 count_rows := 0;
1526 open back_dated_wt_cur(current_cost_group_id,
1527 closing_pac_period_id,
1528 l_entity_id,
1529 l_cost_type_id);
1530 fetch back_dated_wt_cur into count_rows;
1531 if(count_rows <> 0 ) then
1532 backdated_transactions := true;
1533 commit_complete := false;
1534 goto procedure_end_label;
1535 end if;
1536 close back_dated_wt_cur;
1537
1541 closing_pac_period_id,
1538 /* check for backdated txns in RT */
1539 count_rows := 0;
1540 open back_dated_rt_cur(current_cost_group_id,
1542 l_entity_id,
1543 l_cost_type_id);
1544 fetch back_dated_rt_cur into count_rows;
1545 if(count_rows <> 0 ) then
1546 backdated_transactions := true;
1547 commit_complete := false;
1548 goto procedure_end_label;
1549 end if;
1550 close back_dated_rt_cur;
1551
1552
1553 /* check for backdated txns in RAE */
1554 count_rows := 0;
1555 open back_dated_rae_cur(current_cost_group_id,
1556 closing_pac_period_id,
1557 l_entity_id,
1558 l_cost_type_id);
1559 fetch back_dated_rae_cur into count_rows;
1560 if(count_rows <> 0 ) then
1561 backdated_transactions := true;
1562 commit_complete := false;
1563 goto procedure_end_label;
1564 end if;
1565 close back_dated_rae_cur;
1566
1567 /* check for backdated txns in LCM ADJ */
1568 count_rows := 0;
1569 open back_dated_lcadj_cur(current_cost_group_id,
1570 closing_pac_period_id,
1571 l_entity_id,
1572 l_cost_type_id);
1573 fetch back_dated_lcadj_cur into count_rows;
1574 if(count_rows <> 0 ) then
1575 backdated_transactions := true;
1576 commit_complete := false;
1577 goto procedure_end_label;
1578 end if;
1579 close back_dated_lcadj_cur;
1580
1581
1582
1583 end if;
1584
1585 end if;
1586
1587 END LOOP;
1588
1589 close all_cost_groups_cur;
1590
1591 /* **********section to check AP periods **************** */
1592 l_stmt_num := 50;
1593 count_rows := 0;
1594 open ap_period_open_cur( l_entity_id, l_cost_type_id, closing_end_date);
1595 fetch ap_period_open_cur into count_rows;
1596 if(count_rows <> 0 ) then
1597 l_err_msg := l_err_msg || 'I am Here';
1598 ap_period_open := true;
1599 commit_complete := false;
1600 goto procedure_end_label;
1601 end if;
1602 close ap_period_open_cur;
1603
1604 <<check_ar_label>>
1605 /* ********** Perform validations for Revenue / COGS Matching *********** */
1606 l_stmt_num := 60;
1607 -- First get the ledger ID for this legal entity
1608 SELECT distinct clct.set_of_books_id,
1609 nvl(clct.create_acct_entries,'N')
1610 INTO l_ledger_id,
1611 l_create_acct_entries
1612 FROM cst_le_cost_types clct
1613 WHERE clct.cost_type_id = l_cost_type_id
1614 AND clct.legal_entity = l_entity_id;
1615
1616 -- If the create_acct_entries field is not YES for this LE/CT, there is
1617 -- no need to perform any of the Revenue / COGS validations.
1618
1619 IF (l_create_acct_entries = 'N') THEN
1620 goto procedure_end_label;
1621 END IF;
1622
1623 /* ********** check the AR period **************** */
1624 l_stmt_num := 70;
1625 -- Get the effective period number to pass to the AR procedure
1626 SELECT effective_period_num
1627 INTO l_effective_period_num
1628 FROM gl_period_statuses
1629 WHERE ledger_id = l_ledger_id
1630 AND application_id = 101
1631 AND adjustment_period_flag = 'N' -- Added for bug#4634513
1632 AND closing_end_date BETWEEN start_date AND end_date;
1633
1634 l_stmt_num := 80;
1635 -- Call AR's API to find out if the period is closed
1636 ar_match_rev_cogs_grp.period_status(
1637 p_api_version => 1.0,
1638 p_eff_period_num => l_effective_period_num,
1639 p_sob_id => l_ledger_id,
1640 x_status => l_ar_period_status,
1641 x_return_status => l_return_status,
1642 x_msg_count => l_msg_count,
1643 x_msg_data => l_msg_data
1644 );
1645
1646 IF (l_ar_period_status <> 'C') THEN
1647 ar_period_open := true;
1648 commit_complete := false;
1649 goto procedure_end_label;
1650 END IF;
1651
1652 /* check whether phase 2 of the Generate COGS concurrent request has been run for this period */
1656 -- would have taken care of that phase for this period, and we already have a check that the
1653 -- Phases 2 and 3 of the concurrent request to generate COGS recognition events must be run before
1654 -- closing the PAC period because those phases may generate events that need to be processed in
1655 -- this PAC period. The check for phase 1 is not necessary since the perpetual cost processor
1657 -- perpetual period is closed.
1658
1659 -- If the last process date of phase 2 is less than the period close date, then it still needs
1660 -- to be run for this period to load revenue recognition events into CRRL.
1661 l_stmt_num := 90;
1662 SELECT count(1)
1663 INTO l_phase2_required
1664 FROM cst_revenue_cogs_control
1665 WHERE control_id = 1
1666 AND last_process_upto_date < closing_end_date
1667 AND rownum < 2;
1668
1669 IF (l_phase2_required > 0) THEN
1670 cogsgen_phase2_notrun := true;
1671 commit_complete := false;
1672 goto procedure_end_label;
1673 END IF;
1674
1675 -- If there are any rows in CRRL that may lead to a potential mismatch between revenue and
1676 -- COGS for all organizations in this cost group, then we cannot close the period until
1677 -- those unmatched rows in CRRL get matching events created in CCE via phase 3 of the
1678 -- Generate COGS Recognition Events concurrent request.
1679 l_stmt_num := 100;
1680 SELECT min(crrl.acct_period_num)
1681 INTO l_phase3_required
1682 FROM cst_revenue_recognition_lines crrl,
1683 cst_revenue_cogs_match_lines crcml,
1684 cst_cost_group_assignments ccga,
1685 cst_cost_groups ccg
1686 WHERE crrl.ledger_id = l_ledger_id
1687 AND crrl.potentially_unmatched_flag = 'Y'
1688 AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
1689 AND crcml.organization_id = ccga.organization_id
1690 AND ccga.cost_group_id = ccg.cost_group_id
1691 AND ccg.legal_entity = l_entity_id
1692 AND ccg.cost_group_type = 2
1693 AND NVL(ccg.disable_date, sysdate) >= sysdate;
1694
1695 IF (l_phase3_required IS NOT NULL) THEN
1696 cogsgen_phase3_notrun := true;
1697 commit_complete := false;
1698 goto procedure_end_label;
1699 END IF;
1700
1701 <<procedure_end_label>>
1702 NULL;
1703
1704 IF (l_pLog) THEN
1705 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1706 l_module || '.end',
1707 l_api_name || ' >>>');
1708 END IF;
1709
1710 EXCEPTION
1711 when OTHERS then
1712 IF (l_uLog) THEN
1713 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1714 l_module || '.' || l_stmt_num,
1715 SQLERRM);
1716 END IF;
1717 rollback;
1718 user_defined_error := true;
1719 commit_complete := false;
1720
1721 END validate_close_period;
1722
1723
1724 PROCEDURE close_period (
1725 l_entity_id IN NUMBER,
1726 l_cost_type_id IN NUMBER,
1727 closing_pac_period_id IN NUMBER,
1728 closing_period_type IN VARCHAR2,
1729 closing_end_date IN DATE,
1730 l_user_id IN NUMBER,
1731 l_login_id IN NUMBER,
1732
1733 last_scheduled_close_date IN OUT NOCOPY DATE,
1734 end_date_is_passed OUT NOCOPY BOOLEAN,
1735 incomplete_processing OUT NOCOPY BOOLEAN,
1736 pending_transactions OUT NOCOPY BOOLEAN,
1737 rerun_processor OUT NOCOPY BOOLEAN,
1738 prompt_to_reclose OUT NOCOPY BOOLEAN,
1739 undefined_cost_groups OUT NOCOPY BOOLEAN,
1740 backdated_transactions OUT NOCOPY BOOLEAN,
1741 perpetual_periods_open OUT NOCOPY BOOLEAN,
1742 ap_period_open OUT NOCOPY BOOLEAN,
1743 ar_period_open OUT NOCOPY BOOLEAN,
1744 cogsgen_phase2_notrun OUT NOCOPY BOOLEAN,
1745 cogsgen_phase3_notrun OUT NOCOPY BOOLEAN,
1746 user_defined_error OUT NOCOPY BOOLEAN,
1747 commit_complete OUT NOCOPY BOOLEAN,
1748 req_id OUT NOCOPY NUMBER
1749 ) IS
1750
1751
1752 /* **************************************************************************** */
1753 /* This section declares all the local variable for the close procedure */
1754 /* **************************************************************************** */
1755
1756 dummy_id NUMBER;
1757 no_cost_groups NUMBER;
1758 current_cost_group_id NUMBER;
1759 no_cost_groups_available BOOLEAN;
1760 count_rows NUMBER;
1761 rerun_process_date DATE;
1762 l_err_num NUMBER;
1766
1763 l_err_code VARCHAR2(240);
1764 l_err_msg VARCHAR2(240);
1765 l_open_flag VARCHAR2(1);
1767 l_stmt_num NUMBER;
1768
1769 l_api_name CONSTANT VARCHAR2(30) := 'close_period';
1770 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1771 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1772
1773 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1774 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1775 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1776 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1777 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1778 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1779
1780 BEGIN
1781
1782 IF (l_pLog) THEN
1783 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1784 l_module || '.begin',
1785 l_api_name || ' <<< Parameters:
1786 l_entity_id = ' || l_entity_id || '
1787 l_cost_type_id = ' || l_cost_type_id || '
1788 closing_pac_period_id = ' || closing_pac_period_id || '
1789 closing_period_type = ' || closing_period_type || '
1790 closing_end_date = ' || closing_end_date ||'
1791 last_scheduled_close_date = ' || last_scheduled_close_date);
1792 END IF;
1793
1794 /* **************************************************************************** */
1795 /* This section initializes all local and OUT Params of the close procedure */
1796 /* **************************************************************************** */
1797 l_stmt_num := 0;
1798 dummy_id := 0;
1799 end_date_is_passed := false;
1800 prompt_to_reclose := false;
1801 incomplete_processing := false;
1802 pending_transactions := false;
1803 current_cost_group_id := 0;
1804 no_cost_groups := 0;
1805 undefined_cost_groups := false;
1806 backdated_transactions := false;
1807 perpetual_periods_open := false;
1808 ap_period_open := false;
1809 ar_period_open := false;
1810 cogsgen_phase2_notrun := false;
1811 cogsgen_phase3_notrun := false;
1812 user_defined_error := false;
1813 rerun_processor := false;
1814 commit_complete := false;
1815 no_cost_groups_available:= false;
1816 count_rows := 0;
1817 rerun_process_date := trunc(closing_end_date) +1;
1818 l_err_num := 0;
1819 l_err_code := NULL;
1820 l_err_msg := NULL;
1821
1822
1823
1824 CSTPPPSC.validate_close_period
1825 ( l_entity_id,
1826 l_cost_type_id,
1827 closing_pac_period_id,
1828 closing_period_type,
1829 closing_end_date,
1830 l_user_id,
1831 l_login_id,
1832 last_scheduled_close_date,
1833 end_date_is_passed,
1834 incomplete_processing,
1835 pending_transactions,
1836 rerun_processor,
1837 prompt_to_reclose,
1838 undefined_cost_groups,
1839 backdated_transactions,
1840 perpetual_periods_open,
1841 ap_period_open,
1845 user_defined_error,
1842 ar_period_open,
1843 cogsgen_phase2_notrun,
1844 cogsgen_phase3_notrun,
1846 commit_complete
1847 );
1848
1849 l_stmt_num := 10;
1850 IF( (end_date_is_passed ) OR
1851 (incomplete_processing) OR
1852 (pending_transactions ) OR
1853 (rerun_processor ) OR
1854 (prompt_to_reclose) OR
1855 (undefined_cost_groups ) OR
1856 (backdated_transactions) OR
1857 (perpetual_periods_open) OR
1858 (ap_period_open ) OR
1859 (ar_period_open ) OR
1860 (cogsgen_phase2_notrun) OR
1861 (cogsgen_phase3_notrun) OR
1862 (user_defined_error ) ) THEN
1863
1864 commit_complete := false;
1865 goto procedure_end_label;
1866 END IF;
1867
1868 l_stmt_num := 20;
1869 req_id := FND_REQUEST.submit_request('BOM',
1870 'CSTPPPSC',
1871 NULL,
1872 NULL,
1873 FALSE,
1874 l_entity_id,
1875 l_cost_type_id,
1876 closing_pac_period_id,
1877 closing_period_type,
1878 fnd_date.date_to_canonical(closing_end_date),
1879 l_user_id,
1880 l_login_id,
1881 fnd_date.date_to_canonical(last_scheduled_close_date)
1882 );
1883
1884 if (req_id = 0) then
1885 commit_complete := false;
1886 goto error_label;
1887 else
1888 /* Change the period status to pending by changing the open_flag = 'P' */
1889
1890 COMMIT;
1891 l_stmt_num := 30;
1892 l_open_flag := 'P';
1893 UPDATE cst_pac_periods
1894 SET open_flag = l_open_flag,
1895 period_close_date = trunc(sysdate),
1896 last_update_date = trunc(sysdate),
1897 last_updated_by = l_user_id,
1898 last_update_login = l_login_id
1899 WHERE pac_period_id = closing_pac_period_id;
1900
1901 COMMIT;
1902 goto procedure_end_label;
1903 end if;
1904
1905 <<error_label>>
1906 commit_complete := false;
1907
1908
1909 <<procedure_end_label>>
1910 NULL;
1911
1912 IF (l_pLog) THEN
1913 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1914 l_module || '.end',
1915 l_api_name || ' >>>');
1916 END IF;
1917
1918 EXCEPTION
1919 when OTHERS then
1920 IF (l_uLog) THEN
1921 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1922 l_module || '.' || l_stmt_num,
1923 SQLERRM);
1924 END IF;
1925 rollback;
1926 user_defined_error := true;
1927 commit_complete := false;
1928
1929
1930 END close_period;
1931
1932
1933 PROCEDURE api_close_period(
1934 errbuf OUT NOCOPY VARCHAR2,
1935 retcode OUT NOCOPY NUMBER,
1936 l_entity_id IN NUMBER,
1937 l_cost_type_id IN NUMBER,
1938 closing_pac_period_id IN NUMBER,
1939 closing_period_type IN VARCHAR2,
1940 l_closing_end_date IN VARCHAR2,
1941 l_user_id IN NUMBER,
1942 l_login_id IN NUMBER,
1943 l_last_scheduled_close_date IN VARCHAR2
1944 ) IS
1945
1946 /* **************************************************************************** */
1947 /* Cursor for all cost groups defined in the legal entity */
1948 /* **************************************************************************** */
1949 CURSOR all_cost_groups_cur IS
1950 select cost_group_id
1951 from cst_cost_groups ccg
1952 where ccg.legal_entity = l_entity_id
1956 SELECT distinct cost_group_id
1953 and ccg.cost_group_type = 2
1954 and NVL(ccg.disable_date, sysdate) >= sysdate
1955 and ccg.cost_group_id IN (
1957 FROM cst_cost_group_assignments
1958 WHERE legal_entity = l_entity_id );
1959
1960 /* **************************************************************************** */
1961 /* Cursor to check whether the period is still open to be closed */
1962 /* **************************************************************************** */
1963
1964 CURSOR check_still_open_to_close_cur IS
1965 SELECT pac_period_id
1966 FROM cst_pac_periods
1967 WHERE legal_entity = l_entity_id
1968 AND cost_type_id = l_cost_type_id
1969 AND pac_period_id = closing_pac_period_id
1970 AND open_flag = 'P';
1971
1972
1973
1974 no_cost_groups NUMBER;
1975 no_cost_groups_available BOOLEAN;
1976 current_cost_group_id NUMBER;
1977 l_err_num NUMBER;
1978 l_err_code VARCHAR2(2000);
1979 l_err_msg VARCHAR2(2000);
1980 prompt_to_reclose BOOLEAN;
1981 dummy_id NUMBER;
1982 last_scheduled_close_date DATE;
1983 closing_end_date DATE;
1984 conc_status BOOLEAN;
1985 distributions_flag VARCHAR2(1);
1986 l_open_flag VARCHAR2(1);
1987
1988
1989 l_stmt_num NUMBER;
1990
1991 l_api_name CONSTANT VARCHAR2(30) := 'api_close_period';
1992 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1993 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1994
1995 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1996 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1997 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1998 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1999 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2000 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2001
2002 BEGIN
2003
2004 IF (l_pLog) THEN
2005 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2006 l_module || '.begin',
2007 l_api_name || ' <<< Parameters:
2008 l_entity_id = ' || l_entity_id || '
2009 l_cost_type_id = ' || l_cost_type_id || '
2010 closing_pac_period_id = ' || closing_pac_period_id || '
2011 closing_period_type = ' || closing_period_type || '
2012 l_closing_end_date = ' || l_closing_end_date || '
2013 l_last_scheduled_close_date = ' || l_last_scheduled_close_date);
2014 END IF;
2015
2016
2017 /* **************************************************************************** */
2018 /* This section is to create distribution entries while period close */
2019 /* **************************************************************************** */
2020 l_stmt_num := 0;
2021 no_cost_groups := 0;
2022 no_cost_groups_available := true;
2023 current_cost_group_id := 0;
2024 l_err_num := 0;
2025 l_err_code := NULL;
2026 l_err_msg := NULL;
2027 prompt_to_reclose := false;
2028 dummy_id := 0;
2029 last_scheduled_close_date := fnd_date.canonical_to_date(l_last_scheduled_close_date);
2030 closing_end_date := fnd_date.canonical_to_date(l_closing_end_date);
2031
2032 FND_MESSAGE.set_name('BOM', 'CST_BEGIN_PERIOD_END');
2033 l_err_msg := FND_MESSAGE.Get;
2034 fnd_file.put_line(fnd_file.log,l_err_msg);
2035
2036 l_stmt_num := 10;
2037 open all_cost_groups_cur;
2038
2039 LOOP
2040 current_cost_group_id := 0;
2041 fetch all_cost_groups_cur into current_cost_group_id;
2042 if ( all_cost_groups_cur%NOTFOUND) then
2043 goto check_duplicating_label;
2044 end if;
2045
2046 no_cost_groups_available := false;
2047 no_cost_groups := no_cost_groups + 1;
2048
2049 l_stmt_num := 20;
2050 SELECT NVL(CREATE_ACCT_ENTRIES,'N')
2051 INTO distributions_flag
2052 FROM CST_LE_COST_TYPES
2053 WHERE LEGAL_ENTITY = l_entity_id
2054 AND COST_TYPE_ID = l_cost_type_id
2055 AND PRIMARY_COST_METHOD > 2;
2056
2057 if (distributions_flag = 'Y') then
2058
2059 /* Call the period end process only if accounting is turned on */
2060 l_stmt_num := 30;
2061 CSTPDPPC.dist_processor_main(
2062 errbuf => l_err_code,
2063 retcode => l_err_num,
2064 i_mode => 1,
2065 i_period_id => closing_pac_period_id,
2066 i_cost_type_id => l_cost_type_id,
2067 i_cost_group_id => current_cost_group_id,
2068 i_legal_entity => l_entity_id);
2069
2070 fnd_file.put_line(fnd_file.log,' ');
2071
2072 if (l_err_num <> 0) then
2073 goto error_label;
2074 end if;
2075
2076 end if;
2077
2078 END LOOP;
2079
2080 close all_cost_groups_cur;
2081
2082
2083 /* **************************************************************************** */
2084 /* This section check whether another user is trying to close the same period */
2085 /* simultaneously. */
2086 /* IF yes => prompt_to_reclose = TRUE */
2087 /* IF no => prompt_to_reclose = FALSE */
2088 /* **************************************************************************** */
2089
2090
2091 <<check_duplicating_label>>
2092
2093
2094 l_stmt_num := 40;
2095 open check_still_open_to_close_cur;
2096 fetch check_still_open_to_close_cur into dummy_id;
2097 if ( check_still_open_to_close_cur%FOUND ) then
2098 prompt_to_reclose := false;
2099 else
2100 prompt_to_reclose := true;
2101 goto error_label;
2102 end if;
2103 close check_still_open_to_close_cur;
2104
2105
2106 /* **************************************************************************** */
2107 /* Declare the period closed by updating the following... */
2108 /* open_flag = 'N' and period_close_date = SYSDATE */
2109 /* **************************************************************************** */
2110
2111 /* Close the period by updating the open flag to 'N' */
2112 l_stmt_num := 50;
2113 l_open_flag := 'N';
2114 UPDATE cst_pac_periods
2115 SET open_flag = l_open_flag,
2116 period_close_date = trunc(sysdate),
2117 last_update_date = trunc(sysdate),
2118 last_updated_by = l_user_id,
2119 last_update_login = l_login_id
2120 WHERE pac_period_id = closing_pac_period_id;
2121
2122 <<sucess_label>>
2123 commit;
2124 FND_MESSAGE.set_name('BOM', 'CST_PERIOD_END_SUCCESS');
2125 l_err_msg := FND_MESSAGE.Get;
2126 fnd_file.put_line(fnd_file.log,l_err_msg);
2127
2128 goto procedure_end_label;
2129
2130 <<error_label>>
2131 ROLLBACK;
2132 l_stmt_num := 60;
2133 UPDATE cst_pac_periods
2134 SET open_flag = 'Y',
2135 period_close_date = trunc(sysdate),
2136 last_update_date = trunc(sysdate),
2137 last_updated_by = l_user_id,
2138 last_update_login = l_login_id
2139 WHERE pac_period_id = closing_pac_period_id;
2140
2141 COMMIT;
2142 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
2143
2144 FND_MESSAGE.set_name('BOM', 'CST_PERIOD_END_FAILURE');
2145 l_err_msg := FND_MESSAGE.Get;
2146 fnd_file.put_line(fnd_file.log,l_err_msg);
2147
2148
2149 <<procedure_end_label>>
2150 NULL;
2151
2152 IF (l_pLog) THEN
2153 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2154 l_module || '.end',
2155 l_api_name || ' >>>');
2156 END IF;
2157
2158
2159 EXCEPTION
2160 when OTHERS then
2161 IF (l_uLog) THEN
2162 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
2163 l_module || '.' || l_stmt_num,
2164 SQLERRM);
2165 END IF;
2166
2167 ROLLBACK;
2168 UPDATE cst_pac_periods
2169 SET open_flag = 'Y',
2170 period_close_date = trunc(sysdate),
2171 last_update_date = trunc(sysdate),
2172 last_updated_by = l_user_id,
2173 last_update_login = l_login_id
2174 WHERE pac_period_id = closing_pac_period_id;
2175
2176 COMMIT;
2177
2178 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
2179 FND_MESSAGE.set_name('BOM', 'CST_PERIOD_END_SUCCESS');
2180 l_err_msg := FND_MESSAGE.Get;
2181 fnd_file.put_line(fnd_file.log,l_err_msg);
2182
2183
2184 END api_close_period;
2185
2186
2187
2188 END CSTPPPSC;