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