[Home] [Help]
PACKAGE BODY: APPS.GL_SRS_INCREMENTOR_API
Source
1 PACKAGE BODY GL_SRS_INCREMENTOR_API AS
2 /* $Header: gluschpb.pls 120.10 2005/05/05 01:43:31 kvora ship $ */
3
4 -- public functions
5
6 ------------------------------------------------------
7 -- Increment Journal date by business days offset method
8 -- then find corresponding period
9 ------------------------------------------------------
10 FUNCTION increment_bus_date(
11 x_ledger_id NUMBER,
12 x_last_anchor_date DATE,
13 x_last_para_date DATE,
14 x_new_anchor_date DATE,
15 x_new_para_date IN OUT NOCOPY DATE,
16 x_new_para_period IN OUT NOCOPY VARCHAR2)
17 RETURN NUMBER IS
18 cant_find_bus_day EXCEPTION;
19 not_bus_day EXCEPTION;
20 --cons_sob_not_allowed EXCEPTION;
21 error_code NUMBER;
22 days_offset NUMBER DEFAULT 0;
23 num_rows NUMBER DEFAULT 0;
24 CURRENT_DATE DATE;
25 bus_day_flag VARCHAR2(1);
26 v_trxn_calendar_id NUMBER(15);
27 v_last_anchor_date DATE := TRUNC(x_last_anchor_date);
28 v_last_para_date DATE := TRUNC(x_last_para_date);
29 v_new_anchor_date DATE := TRUNC(x_new_anchor_date);
30
31 CURSOR get_future_date IS
32 SELECT transaction_date
33 FROM gl_transaction_dates
34 WHERE transaction_calendar_id = v_trxn_calendar_id
35 AND business_day_flag = 'Y'
36 AND transaction_date > v_new_anchor_date
37 ORDER BY transaction_date ASC;
38
39 CURSOR get_past_date IS
40 SELECT transaction_date
41 FROM gl_transaction_dates
42 WHERE transaction_calendar_id = v_trxn_calendar_id
43 AND business_day_flag = 'Y'
44 AND transaction_date < v_new_anchor_date
45 ORDER BY transaction_date DESC;
46
47 CURSOR get_bus_day_flag IS
48 SELECT business_day_flag
49 FROM gl_transaction_dates
50 WHERE transaction_calendar_id = v_trxn_calendar_id
51 AND transaction_date = CURRENT_DATE;
52 BEGIN
53 --dbms_output.put_line('x_last_anchor_date = '||to_char(V_Last_Anchor_Date));
54 --dbms_output.put_line('x_last_para_date = '||to_char(V_Last_Para_Date));
55 error_buffer := '';
56 error_code := -30;
57
58 SELECT transaction_calendar_id
59 INTO v_trxn_calendar_id
60 FROM gl_ledgers
61 WHERE ledger_id = x_ledger_id;
62
63
64 ------------------------------------------------------
65 -- Exit if the date to increment is not a business day
66 ------------------------------------------------------
67 error_code := -20;
68 CURRENT_DATE := v_last_para_date;
69 OPEN get_bus_day_flag;
70 FETCH get_bus_day_flag INTO bus_day_flag;
71
72 IF bus_day_flag <> 'Y' THEN
73 RAISE not_bus_day;
74 END IF;
75
76 CLOSE get_bus_day_flag;
77
78
79 ------------------------------------------------------
80 -- Increment Date Parameter
81 ------------------------------------------------------
82 IF v_last_para_date = v_last_anchor_date THEN
83 CURRENT_DATE := v_new_anchor_date;
84 OPEN get_bus_day_flag;
85 FETCH get_bus_day_flag INTO bus_day_flag;
86
87 IF bus_day_flag <> 'Y' THEN
88 RAISE cant_find_bus_day;
89 END IF;
90
91 CLOSE get_bus_day_flag;
92 CURRENT_DATE := v_new_anchor_date;
93 ELSIF v_last_para_date < v_last_anchor_date THEN
94 error_code := -21;
95
96 SELECT COUNT(*)
97 INTO days_offset
98 FROM gl_transaction_dates
99 WHERE transaction_calendar_id = v_trxn_calendar_id
100 AND business_day_flag = 'Y'
101 AND transaction_date >= v_last_para_date
102 AND transaction_date < v_last_anchor_date;
103
104 --dbms_output.put_line('d past offset = '||to_char(days_offset));
105
106 error_code := -22;
107 OPEN get_past_date;
108
109 LOOP
110 FETCH get_past_date INTO CURRENT_DATE;
111 EXIT WHEN get_past_date%NOTFOUND;
112 num_rows := num_rows
113 + 1;
114 EXIT WHEN num_rows >= days_offset;
115 END LOOP;
116
117 CLOSE get_past_date;
118 ELSE
119 error_code := -23;
120
121 SELECT COUNT(*)
122 INTO days_offset
123 FROM gl_transaction_dates
124 WHERE transaction_calendar_id = v_trxn_calendar_id
125 AND business_day_flag = 'Y'
126 AND transaction_date > v_last_anchor_date
127 AND transaction_date <= v_last_para_date;
128
129 --dbms_output.put_line('d offset = '||to_char(days_offset));
130
131 error_code := -24;
132 OPEN get_future_date;
133
134 LOOP
135 FETCH get_future_date INTO CURRENT_DATE;
136 EXIT WHEN get_future_date%NOTFOUND;
137 num_rows := num_rows
138 + 1;
139 EXIT WHEN num_rows >= days_offset;
140 END LOOP;
141
142 CLOSE get_future_date;
143 END IF;
144
145 IF num_rows = days_offset THEN
146 x_new_para_date := TRUNC(CURRENT_DATE);
147 ELSE
148 RAISE cant_find_bus_day;
149 END IF;
150
151
152 ----------------------------------------------------------
153 -- Get the corresponding period for the incremented date
154 ----------------------------------------------------------
155 error_code := -25;
156
157 SELECT period_name
158 INTO x_new_para_period
159 FROM gl_date_period_map m, gl_ledgers b
160 WHERE m.period_set_name = b.period_set_name
161 AND m.period_type = b.accounted_period_type
162 AND b.ledger_id = x_ledger_id
163 AND m.accounting_date = x_new_para_date;
164
165 RETURN (1);
166 EXCEPTION
167 WHEN not_bus_day THEN
168 -- Cannot increment the day DAY because it is not a business day
169 fnd_message.set_name('SQLGL', 'GL_SCH_INC_NONBUS_DATE');
170 fnd_message.set_token('DAY', TO_CHAR(v_last_para_date, 'DD-MON-YYYY'));
171 error_buffer := fnd_message.get;
172 RETURN error_code;
173 WHEN cant_find_bus_day THEN
174 -- Cannot find a business day to use for the next request after incrementing DAY
175 fnd_message.set_name('SQLGL', 'GL_SCH_INC_NO_NEXT_DAY');
176 fnd_message.set_token('DAY', TO_CHAR(v_last_para_date, 'DD-MON-YYYY'));
177 error_buffer := fnd_message.get;
178 RETURN error_code;
179 WHEN OTHERS THEN
180 error_buffer := 'gl_srs_incrementor_api error #'
181 || TO_CHAR(error_code)
182 || ': ( Last Anchor Date='
183 || TO_CHAR(v_last_anchor_date, 'DD-MON-YYYY')
184 || 'This Anchor Date='
185 || TO_CHAR(v_new_anchor_date, 'DD-MON-YYYY')
186 || 'This Date='
187 || TO_CHAR(x_new_para_date, 'DD-MON-YYYY')
188 || ' ) '
189 || SUBSTR(SQLERRM, 1, 50);
190 RETURN error_code;
191 END increment_bus_date;
192
193
194 ------------------------------------------------------
195 -- Increment Period using journals days offset method
196 -- This method is used for Non-ADB and ADB consolidation
197 -- ledgers.
198 ------------------------------------------------------
199 FUNCTION inc_period_by_days_offset(
200 x_ledger_id NUMBER,
201 x_start_date_last_run DATE,
202 x_period_last_run VARCHAR2,
203 x_start_date_this_run DATE,
204 x_period_this_run IN OUT NOCOPY VARCHAR2)
205 RETURN NUMBER IS
206 no_period_this_run EXCEPTION;
207 error_code NUMBER;
208 dummy VARCHAR2(15);
209 l_eff_period_num NUMBER;
210 l_start_period_num NUMBER;
211 c_start_period_num NUMBER;
212 is_future BOOLEAN;
213 num_rows NUMBER DEFAULT 0;
214 period_offset NUMBER DEFAULT 0;
215 current_period VARCHAR2(15);
216 c_start_period VARCHAR2(15);
217
218 CURSOR get_future_period_this_run IS
219 SELECT period_name
220 FROM gl_period_statuses
221 WHERE application_id = 101
222 AND ledger_id = x_ledger_id
223 AND adjustment_period_flag = 'N'
224 AND effective_period_num >= c_start_period_num
225 ORDER BY effective_period_num ASC;
226
227 CURSOR get_past_period_this_run IS
228 SELECT period_name
229 FROM gl_period_statuses
230 WHERE application_id = 101
231 AND ledger_id = x_ledger_id
232 AND adjustment_period_flag = 'N'
233 AND effective_period_num <= c_start_period_num
234 ORDER BY effective_period_num DESC;
235 BEGIN
236 -- dbms_output.put_line('X_last_start_date='||to_char(X_Start_Date_Last_Run));
237 -- dbms_output.put_line('X_last_period='||X_Period_Last_Run);
238 -- dbms_output.put_line('X_this_start_date='||to_char(X_Start_Date_this_Run));
239
240 error_buffer := '';
241 -- Get period info of last run period
242 error_code := -11;
243
244 SELECT effective_period_num
245 INTO l_eff_period_num
246 FROM gl_period_statuses p
247 WHERE p.application_id = 101
248 AND p.ledger_id = x_ledger_id
249 AND p.period_name = x_period_last_run
250 AND p.adjustment_period_flag = 'N';
251
252 -- Get period of last start run date
253 -- We add 0.99998843 to the end_date because the dates are stored with their
254 -- timestamp information truncated in GL_PERIOD_STATUSES. This means that
255 -- a daily period will have exactly the same start date/time and end
256 -- date/time. Since the scheduling feature is extremely time sensitive,
257 -- we need to add the timestamp while retrieving the value from the table.
258 -- 0.99998843 stands for 23 Hours, 59 Minutes and 59 seconds.
259 error_code := -12;
260
261 SELECT effective_period_num, period_name
262 INTO l_start_period_num, dummy
263 FROM gl_period_statuses p
264 WHERE p.application_id = 101
265 AND p.ledger_id = x_ledger_id
266 AND x_start_date_last_run BETWEEN p.start_date
267 AND p.end_date
268 + 0.99998843
269 AND p.adjustment_period_flag = 'N';
270
271 -- dbms_output.put_line('last start = '||dummy);
272 -- dbms_output.put_line('last start num = '||to_char(l_start_period_num));
273
274 -- Get period of current start run date
275 -- We add 0.99998843 to the end_date because the dates are stored with their
276 -- timestamp information truncated in GL_PERIOD_STATUSES. This means that
280 -- 0.99998843 stands for 23 Hours, 59 Minutes and 59 seconds.
277 -- a daily period will have exactly the same start date/time and end
278 -- date/time. Since the scheduling feature is extremely time sensitive,
279 -- we need to add the timestamp while retrieving the value from the table.
281 error_code := -13;
282
283 SELECT effective_period_num, period_name
284 INTO c_start_period_num, c_start_period
285 FROM gl_period_statuses p
286 WHERE p.application_id = 101
287 AND p.ledger_id = x_ledger_id
288 AND x_start_date_this_run BETWEEN p.start_date
289 AND p.end_date
290 + 0.99998843
291 AND p.adjustment_period_flag = 'N';
292
293 -- dbms_output.put_line('current start = '||c_start_period);
294 -- dbms_output.put_line('current start num = '||to_char(c_start_period_num));
295
296 -- Calculate period increment
297 error_code := -14;
298
299 IF l_eff_period_num = l_start_period_num THEN
300 x_period_this_run := c_start_period;
301 RETURN (1);
302 ELSIF l_eff_period_num > l_start_period_num THEN
303 SELECT COUNT(*)
304 INTO period_offset
305 FROM gl_period_statuses p
306 WHERE p.application_id = 101
307 AND p.ledger_id = x_ledger_id
308 AND p.effective_period_num BETWEEN l_start_period_num
309 AND l_eff_period_num
310 AND p.adjustment_period_flag = 'N';
311
312 is_future := TRUE;
313 -- dbms_output.put_line('period_offset = '||to_char(period_offset));
314
315 OPEN get_future_period_this_run;
316
317 LOOP
318 FETCH get_future_period_this_run INTO current_period;
319 EXIT WHEN get_future_period_this_run%NOTFOUND;
320 num_rows := num_rows
321 + 1;
322 EXIT WHEN num_rows >= period_offset;
323 END LOOP;
324
325 CLOSE get_future_period_this_run;
326 ELSE
327 SELECT COUNT(*)
328 INTO period_offset
329 FROM gl_period_statuses p
330 WHERE p.application_id = 101
331 AND p.ledger_id = x_ledger_id
332 AND p.effective_period_num BETWEEN l_eff_period_num
333 AND l_start_period_num
334 AND p.adjustment_period_flag = 'N';
335
336 is_future := FALSE;
337 -- dbms_output.put_line('period_offset = '||to_char(period_offset));
338
339 OPEN get_past_period_this_run;
340
341 LOOP
342 FETCH get_past_period_this_run INTO current_period;
343 EXIT WHEN get_past_period_this_run%NOTFOUND;
344 num_rows := num_rows
345 + 1;
346 EXIT WHEN num_rows >= period_offset;
347 END LOOP;
348
349 CLOSE get_past_period_this_run;
350 END IF;
351
352 IF num_rows = period_offset THEN
353 x_period_this_run := current_period;
354 ELSE
355 RAISE no_period_this_run;
356 END IF;
357
358 RETURN (1);
359 EXCEPTION
360 WHEN no_period_this_run THEN
361 -- Cannot find a period to use for the next request after period increment
362 error_buffer :=
363 fnd_message.get_string('SQLGL', 'GL_SCH_INC_NO_NEXT_PERIOD');
364 RETURN (error_code);
365 WHEN OTHERS THEN
366 IF error_code = -11 THEN
367 -- Cannot increment an adjusting period
368 error_buffer :=
369 fnd_message.get_string('SQLGL', 'GL_SCH_INC_ADJ_PERIOD');
370 error_buffer :=
371 SUBSTR(error_buffer, 1, 100)
372 || SUBSTR(SQLERRM, 1, 100);
373 ELSIF error_code = -12 THEN
374 -- The schedule start date DAY must map to a period in your calendar.
375 fnd_message.set_name('SQLGL', 'GL_SCH_INC_START_DAY_NO_PERIOD');
376 fnd_message.set_token(
377 'DAY',
378 TO_CHAR(x_start_date_last_run, 'DD-MON-YYYY'));
379 error_buffer := fnd_message.get;
380 error_buffer :=
381 SUBSTR(error_buffer, 1, 100)
382 || SUBSTR(SQLERRM, 1, 100);
383 ELSIF error_code = -13 THEN
384 -- The resubmission schedule start date DAY must map
385 -- to a period in your calendar.
386 fnd_message.set_name('SQLGL', 'GL_SCH_INC_RESUB_DAY_NO_PERIOD');
387 fnd_message.set_token(
388 'DAY',
389 TO_CHAR(x_start_date_this_run, 'DD-MON-YYYY'));
390 error_buffer := fnd_message.get;
391 error_buffer :=
392 SUBSTR(error_buffer, 1, 100)
393 || SUBSTR(SQLERRM, 1, 100);
394 ELSE
395 error_buffer := 'gl_srs_incrementor_api error #'
396 || TO_CHAR(error_code)
397 || ' : '
398 || SUBSTR(SQLERRM, 1, 100);
399 END IF;
400
401 RETURN (error_code);
402 END inc_period_by_days_offset;
403
404
405 ----------------------------------------------------------
406 -- Increment GL Period and Date for ADB Consolidation ledger
407 -- Incrementing period for Consolidation ADB ledger is similar
408 -- to Standard ledger, and the journal date is set to
409 -- the first day of the period.
410 ----------------------------------------------------------
411 PROCEDURE cons_inc_private(
415 x_calc_date_para VARCHAR2,
412 x_ledger_id NUMBER,
413 x_period_para VARCHAR2,
414 x_je_date_para VARCHAR2,
416 x_date_format VARCHAR2) IS
417 error_code NUMBER;
418 v_last_period VARCHAR2(15);
419 v_period VARCHAR2(15);
420 v_pstart_date DATE;
421 v_last_sch_date DATE;
422 v_sch_date DATE;
423 v_last_je_date DATE;
424 v_je_date DATE;
425 v_last_calc_date DATE;
426 v_calc_date DATE;
427 v_days_elapsed NUMBER;
428 period_pnum NUMBER;
429 je_date_pnum NUMBER;
430 calc_date_pnum NUMBER;
431 exit_fail EXCEPTION;
432 BEGIN
433 error_buffer := '';
434 v_sch_date := fnd_resub.get_requested_start_date;
435 v_days_elapsed := fnd_resub.get_rusub_delta;
436 v_last_sch_date := TRUNC( v_sch_date
437 - v_days_elapsed);
438
439 IF fnd_resub.get_param_number(x_period_para, period_pnum) <> 0 THEN
440 error_code := -230;
441 -- Cannot get parameter number for PARA. Please check your
442 -- concurrent program definition
443 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
444 fnd_message.set_token('PARA', x_period_para);
445 error_buffer := fnd_message.get;
446 RAISE exit_fail;
447 END IF;
448
449 v_last_period := fnd_resub.get_parameter(period_pnum);
450 error_code := inc_period_by_days_offset(
451 x_ledger_id,
452 v_last_sch_date,
453 v_last_period,
454 v_sch_date,
455 v_period);
456
457 IF error_code < 0 THEN
458 RAISE exit_fail;
459 END IF;
460
461 fnd_resub.set_parameter(period_pnum, v_period);
462 error_code := -200;
463
464 SELECT start_date
465 INTO v_pstart_date
466 FROM gl_period_statuses
467 WHERE application_id = 101
468 AND ledger_id = x_ledger_id
469 AND period_name = v_period;
470
471 IF fnd_resub.get_param_number(x_je_date_para, je_date_pnum) <> 0 THEN
472 error_code := -210;
473 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
474 fnd_message.set_token('PARA', x_je_date_para);
475 error_buffer := fnd_message.get;
476 RAISE exit_fail;
477 ELSE
478 IF fnd_resub.get_param_number(x_calc_date_para, calc_date_pnum) <> 0 THEN
479 error_code := -220;
480 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
481 fnd_message.set_token('PARA', x_calc_date_para);
482 error_buffer := fnd_message.get;
483 RAISE exit_fail;
484 END IF;
485 END IF;
486
487 fnd_resub.set_parameter(
488 je_date_pnum,
489 TO_CHAR(v_pstart_date, x_date_format));
490 fnd_resub.set_parameter(
491 calc_date_pnum,
492 TO_CHAR(v_pstart_date, x_date_format));
493 fnd_resub.return_info(0, error_buffer);
494 EXCEPTION
495 WHEN exit_fail THEN
496 fnd_resub.return_info(error_code, error_buffer);
497 WHEN OTHERS THEN
498 error_buffer := 'gl_srs_incrementor_api.cons_inc_private error #'
499 || TO_CHAR(error_code)
500 || ' : '
501 || SUBSTR(SQLERRM, 1, 100);
502 fnd_resub.return_info(error_code, error_buffer);
503 END cons_inc_private;
504
505
506 ------------------------------------------------------
507 -- Increment GL Period for Standard (Non-ADB) ledger
508 ------------------------------------------------------
509 PROCEDURE increment_period(
510 x_ledger_id NUMBER,
511 x_period_para VARCHAR2) IS
512 l_value fnd_profile_option_values.profile_option_value%TYPE;
513 error_code NUMBER;
514 v_last_period VARCHAR2(15);
515 v_period VARCHAR2(15);
516 v_last_sch_date DATE;
517 v_sch_date DATE;
518 v_days_elapsed NUMBER;
519 period_pnum NUMBER;
520 exit_fail EXCEPTION;
521 BEGIN
522 error_buffer := '';
523
524 IF fnd_resub.get_increment_flag = 'N' THEN
525 fnd_resub.return_info(0, error_buffer);
526 RETURN;
527 END IF;
528
529 --fnd_profile.get('GL_SET_OF_BKS_ID', l_value);
530 --v_sob_id := TO_NUMBER(l_value);
531 v_sch_date := fnd_resub.get_requested_start_date;
532 v_days_elapsed := fnd_resub.get_rusub_delta;
533 v_last_sch_date := TRUNC( v_sch_date
534 - v_days_elapsed);
535
536 IF fnd_resub.get_param_number(x_period_para, period_pnum) <> 0 THEN
537 error_code := -100;
538 -- Cannot get parameter number for PARA. Please check your
539 -- concurrent program definition
540 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
541 fnd_message.set_token('PARA', x_period_para);
542 error_buffer := fnd_message.get;
543 RAISE exit_fail;
544 END IF;
545
546 v_last_period := fnd_resub.get_parameter(period_pnum);
547 error_code := inc_period_by_days_offset(
548 x_ledger_id,
552 v_period);
549 v_last_sch_date,
550 v_last_period,
551 v_sch_date,
553
554 IF error_code >= 0 THEN
555 fnd_resub.set_parameter(period_pnum, v_period);
556 ELSE
557 RAISE exit_fail;
558 END IF;
559
560 fnd_resub.return_info(0, error_buffer);
561 EXCEPTION
562 WHEN exit_fail THEN
563 fnd_resub.return_info(error_code, error_buffer);
564 WHEN OTHERS THEN
565 error_buffer := 'gl_srs_incrementor_api.increment_period error #'
566 || TO_CHAR(error_code)
567 || ' : '
568 || SUBSTR(SQLERRM, 1, 100);
569 fnd_resub.return_info(error_code, error_buffer);
570 END increment_period;
571
572 --PROCEDURE increment_period IS
573 --BEGIN
574 -- increment_period('PERIOD_NAME');
575 --END increment_period;
576
577
578 ----------------------------------------------------------
579 -- Increment GL Date and Period for Standard ADB ledger
580 ----------------------------------------------------------
581 PROCEDURE increment_adb(
582 x_ledger_id NUMBER,
583 x_period_para VARCHAR2,
584 x_je_date_para VARCHAR2,
585 x_calc_date_para VARCHAR2,
586 x_date_format VARCHAR2) IS
587 v_ledger_id NUMBER := x_ledger_id;
588 error_code NUMBER;
589 v_period VARCHAR2(15);
590 dummy VARCHAR2(15);
591 v_last_sch_date DATE;
592 v_sch_date DATE;
593 v_last_je_date DATE;
594 v_je_date DATE;
595 v_last_calc_date DATE;
596 v_calc_date DATE;
597 v_days_elapsed NUMBER;
598 period_pnum NUMBER;
599 je_date_pnum NUMBER;
600 calc_date_pnum NUMBER;
601 exit_fail EXCEPTION;
602 BEGIN
603 error_buffer := '';
604 error_code := -300;
605 v_sch_date := fnd_resub.get_requested_start_date;
606 v_days_elapsed := fnd_resub.get_rusub_delta;
607 v_last_sch_date := TRUNC( v_sch_date
608 - v_days_elapsed);
609
610 IF fnd_resub.get_param_number(x_period_para, period_pnum) <> 0 THEN
611 error_code := -310;
612 -- Cannot get parameter number for PARA. Please check your
613 -- concurrent program definition
614 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
615 fnd_message.set_token('PARA', x_period_para);
616 error_buffer := fnd_message.get;
617 RAISE exit_fail;
618 END IF;
619
620 IF fnd_resub.get_param_number(x_je_date_para, je_date_pnum) <> 0 THEN
621 error_code := -320;
622 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
623 fnd_message.set_token('PARA', x_je_date_para);
624 error_buffer := fnd_message.get;
625 RAISE exit_fail;
626 ELSE
627 IF fnd_resub.get_param_number(x_calc_date_para, calc_date_pnum) <> 0 THEN
628 error_code := -330;
629 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
630 fnd_message.set_token('PARA', x_calc_date_para);
631 error_buffer := fnd_message.get;
632 RAISE exit_fail;
633 END IF;
634 END IF;
635
636 v_last_je_date :=
637 TO_DATE(fnd_resub.get_parameter(je_date_pnum), x_date_format);
638 v_last_calc_date :=
639 TO_DATE(fnd_resub.get_parameter(calc_date_pnum), x_date_format);
640 -- Get journal effective date
641 error_code := increment_bus_date(
642 v_ledger_id,
643 v_last_sch_date,
644 v_last_je_date,
645 v_sch_date,
646 v_je_date,
647 v_period);
648
649 IF error_code < 0 THEN
650 RAISE exit_fail;
651 END IF;
652
653 -- Get calculation effective date
654 error_code := increment_bus_date(
655 v_ledger_id,
656 v_last_je_date,
657 v_last_calc_date,
658 v_je_date,
659 v_calc_date,
660 dummy);
661
662 IF error_code < 0 THEN
663 RAISE exit_fail;
664 END IF;
665
666 fnd_resub.set_parameter(period_pnum, v_period);
667 fnd_resub.set_parameter(je_date_pnum, TO_CHAR(v_je_date, x_date_format));
668 fnd_resub.set_parameter(
669 calc_date_pnum,
670 TO_CHAR(v_calc_date, x_date_format));
671 fnd_resub.return_info(0, error_buffer);
672 EXCEPTION
673 WHEN exit_fail THEN
674 fnd_resub.return_info(error_code, error_buffer);
675 WHEN OTHERS THEN
676 error_buffer := 'gl_srs_incrementor_api.incremnt_adb error #'
677 || TO_CHAR(error_code)
678 || ' : '
679 || SUBSTR(SQLERRM, 1, 100);
680 fnd_resub.return_info(error_code, error_buffer);
681 END increment_adb;
682
683 PROCEDURE increment_parameters IS
684 l_value fnd_profile_option_values.profile_option_value%TYPE;
685 v_adb_ledger_id NUMBER := NULL;
686 v_con_ledger_id NUMBER := NULL;
687 program_name VARCHAR2(30);
691 current_bid NUMBER;
688 application_name VARCHAR2(30);
689 error_code NUMBER;
690 random_ledger_id NUMBER;
692 batch_code VARCHAR2(1);
693 usage_flag VARCHAR2(1);
694 con_ledger_flag VARCHAR2(1) := 'N';
695 v_last_period VARCHAR2(15);
696 v_period VARCHAR2(15);
697 v_pstart_date DATE;
698 v_last_sch_date DATE;
699
700 v_sch_date DATE;
701 v_last_je_date DATE;
702 v_je_date DATE;
703 v_last_calc_date DATE;
704 v_calc_date DATE;
705 v_days_elapsed NUMBER;
706 v_batch_id NUMBER;
707 v_ledger_id NUMBER;
708 period_pnum NUMBER;
709 je_date_pnum NUMBER;
710 calc_date_pnum NUMBER;
711 batch_pnum NUMBER;
712 ledger_pnum NUMBER;
713 usage_pnum NUMBER;
714 exit_fail EXCEPTION;
715
716 CURSOR get_adb_batches IS
717 SELECT batch_id,batch_type_code
718 FROM gl_auto_alloc_batches
719 WHERE allocation_set_id = v_batch_id
720 AND batch_type_code IN ('A','R','E','B');
721
722 BEGIN
723 error_buffer := '';
724
725 -- Do not increment if user did not check the increment date flag
726 IF fnd_resub.get_increment_flag = 'N' THEN
727 fnd_resub.return_info(0, error_buffer);
728 RETURN;
729 END IF;
730 error_code := -1;
731
732 -- get program name;
733 fnd_resub.get_program(program_name, application_name);
734 error_code := -2;
735
736 --con_ledger_flag := 'Y';
737 SELECT consolidation_ledger_flag
738 into con_ledger_flag
739 FROM gl_system_usages;
740 error_code := -3;
741
742 IF (program_name = 'GLCRVL') THEN
743 IF fnd_resub.get_param_number('Ledger Id',ledger_pnum) <> 0 THEN
744 error_code := -31;
745 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
746 fnd_message.set_token('PARA','Ledger Id');
747 error_buffer := fnd_message.get;
748 RAISE exit_fail;
749 END IF;
750 v_ledger_id := fnd_resub.get_parameter(ledger_pnum);
751 increment_date('Effective Date', 'Y', 'Period', v_ledger_id);
752 increment_date('Rate Date', 'N', NULL, v_ledger_id);
753 ELSIF program_name = 'GLPRJE' THEN
754 IF fnd_resub.get_param_number('Recurring Batch Id',batch_pnum) <>0 THEN
755 error_code := -32;
756 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
757 fnd_message.set_token('PARA','Recurring Batch Id');
758 error_buffer := fnd_message.get;
759 RAISE exit_fail;
760 END IF;
761 v_batch_id := fnd_resub.get_parameter(batch_pnum);
762
763 IF(con_ledger_flag = 'Y') THEN
764 IF fnd_resub.get_param_number('Average Journal Flag',usage_pnum) <>0 THEN
765 error_code := -33;
766 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
767 fnd_message.set_token('PARA','Average Journal Flag');
768 error_buffer := fnd_message.get;
769 RAISE exit_fail;
770 END IF;
771 usage_flag := fnd_resub.get_parameter(usage_pnum);
772 ELSE
773 usage_flag := 'N';
774 END IF;
775
776 -- Check if ADB ledgers exist in the batch
777 BEGIN
778 SELECT lgr.ledger_id
779 into v_adb_ledger_id
780 FROM gl_recurring_headers rh, gl_ledgers lgr
781 WHERE rh.recurring_batch_id = v_batch_id
782 AND lgr.ledger_id = rh.ledger_id
783 AND lgr.enable_average_balances_flag = 'Y'
784 AND lgr.consolidation_ledger_flag = 'N'
785 AND rownum = 1;
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 v_adb_ledger_id := NULL;
789 END;
790
791 -- Check if consolidation ledgers exist in the batch
792 BEGIN
793 SELECT lgr.ledger_id
794 into v_con_ledger_id
795 FROM gl_recurring_headers rh, gl_ledgers lgr
796 WHERE rh.recurring_batch_id = v_batch_id
797 AND lgr.ledger_id = rh.ledger_id
798 AND lgr.enable_average_balances_flag = 'Y'
799 AND lgr.consolidation_ledger_flag = 'Y'
800 AND rownum =1;
801 EXCEPTION
802 WHEN NO_DATA_FOUND THEN
803 v_con_ledger_id := NULL;
804 END;
805
806 IF (v_adb_ledger_id IS NULL and v_con_ledger_id IS NULL )THEN
807 random_ledger_id := get_random_ledger('GLPRJE',NULL,v_batch_id);
808 increment_period(random_ledger_id,'PERIOD_NAME');
809 ELSIF (v_adb_ledger_id IS NOT NULL AND v_con_ledger_id IS NOT NULL ) THEN
810 IF(usage_flag = 'Y') THEN
811 cons_inc_private(
812 v_con_ledger_id,
813 'PERIOD_NAME',
814 'JOURNAL_EFFECTIVE_DATE',
815 'CALCULATION_EFFECTIVE_DATE',
816 'YYYY/MM/DD');
817 ELSE
818 increment_adb(
819 v_adb_ledger_id,
820 'PERIOD_NAME',
821 'JOURNAL_EFFECTIVE_DATE',
822 'CALCULATION_EFFECTIVE_DATE',
826 increment_adb(
823 'YYYY/MM/DD');
824 END IF;
825 ELSIF (v_adb_ledger_id IS NOT NULL) THEN
827 v_adb_ledger_id,
828 'PERIOD_NAME',
829 'JOURNAL_EFFECTIVE_DATE',
830 'CALCULATION_EFFECTIVE_DATE',
831 'YYYY/MM/DD');
832 ELSIF (v_con_ledger_id IS NOT NULL) THEN
833 cons_inc_private(
834 v_con_ledger_id,
835 'PERIOD_NAME',
836 'JOURNAL_EFFECTIVE_DATE',
837 'CALCULATION_EFFECTIVE_DATE',
838 'YYYY/MM/DD');
839 END IF;
840 ELSIF (program_name = 'GLAMAS') THEN
841 IF fnd_resub.get_param_number('allocation_batch_id',batch_pnum) <>0 THEN
842 error_code := -34;
843 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
844 fnd_message.set_token('PARA','allocation_batch_id');
845 error_buffer := fnd_message.get;
846 RAISE exit_fail;
847 END IF;
848
849 v_batch_id := fnd_resub.get_parameter(batch_pnum);
850 IF fnd_resub.get_param_number('ledger_override_id',ledger_pnum) <> 0 THEN
851 error_code := -35;
852 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
853 fnd_message.set_token('PARA','ledger_override_id');
854 error_buffer := fnd_message.get;
855 RAISE exit_fail;
856 END IF;
857 v_ledger_id := fnd_resub.get_parameter(ledger_pnum);
858
859 IF(con_ledger_flag = 'Y') THEN
860 IF fnd_resub.get_param_number('average_je_flag',usage_pnum) <>0 THEN
861 error_code := -36;
862 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
863 fnd_message.set_token('PARA','average_je_flag');
864 error_buffer := fnd_message.get;
865 RAISE exit_fail;
866 END IF;
867 usage_flag := fnd_resub.get_parameter(usage_pnum);
868 ELSE
869 usage_flag := 'N';
870 END IF;
871
872 -- Check if ADB ledgers exist in the batch
873 BEGIN
874 SELECT lgr.ledger_id
875 into v_adb_ledger_id
876 FROM gl_alloc_formulas af,
877 gl_alloc_formula_lines al,
878 gl_ledger_set_assignments lsa,
879 gl_ledgers lgr
880 WHERE af.allocation_batch_id = v_batch_id
881 AND al.allocation_formula_id = af.allocation_formula_id
882 AND al.line_number IN (4, 5)
883 AND lsa.ledger_set_id (+) = nvl(al.ledger_id,v_ledger_id)
884 AND sysdate BETWEEN
885 nvl(trunc(lsa.start_date), sysdate - 1)
886 AND nvl(trunc(lsa.end_date), sysdate + 1)
887 AND lgr.ledger_id = nvl(lsa.ledger_id,
888 nvl(al.ledger_id,v_ledger_id))
889 AND lgr.object_type_code = 'L'
890 AND lgr.enable_average_balances_flag= 'Y'
891 AND lgr.consolidation_ledger_flag = 'N'
892 AND rownum = 1;
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 v_adb_ledger_id := NULL;
896 END;
897
898 -- Check if consolidation ledgers exist in the batch
899 BEGIN
900 SELECT lgr.ledger_id
901 into v_con_ledger_id
902 FROM gl_alloc_formulas af,
903 gl_alloc_formula_lines al,
904 gl_ledger_set_assignments ls,
905 gl_ledgers lgr
906 WHERE af.allocation_batch_id = v_batch_id
907 AND al.allocation_formula_id = af.allocation_formula_id
908 AND al.line_number IN (4, 5)
909 AND ls.ledger_set_id (+) = nvl(al.ledger_id,v_ledger_id)
910 AND sysdate BETWEEN
911 nvl(trunc(ls.start_date), sysdate - 1)
912 AND nvl(trunc(ls.end_date), sysdate + 1)
913 AND lgr.ledger_id = nvl(ls.ledger_id,
914 nvl(al.ledger_id,v_ledger_id))
915 AND lgr.object_type_code = 'L'
916 AND lgr.enable_average_balances_flag = 'Y'
917 AND lgr.consolidation_ledger_flag = 'Y'
918 AND rownum = 1;
919 EXCEPTION
920 WHEN NO_DATA_FOUND THEN
921 v_con_ledger_id := NULL;
922 END;
923
924 IF (v_adb_ledger_id IS NULL and v_con_ledger_id IS NULL) THEN
925 random_ledger_id := get_random_ledger('GLAMAS',v_ledger_id,v_batch_id);
926 increment_period(random_ledger_id,'PERIOD_NAME');
927 ELSIF (v_adb_ledger_id IS NOT NULL AND v_con_ledger_id IS NOT NULL ) THEN
928 IF(usage_flag = 'Y') THEN
929 cons_inc_private(
930 v_con_ledger_id,
931 'PERIOD_NAME',
932 'JOURNAL_EFFECTIVE_DATE',
933 'CALCULATION_EFFECTIVE_DATE',
934 'YYYY/MM/DD HH24:MI:SS');
935 ELSE
936 increment_adb(
937 v_adb_ledger_id,
938 'PERIOD_NAME',
939 'JOURNAL_EFFECTIVE_DATE',
940 'CALCULATION_EFFECTIVE_DATE',
941 'YYYY/MM/DD HH24:MI:SS');
942 END IF;
943 ELSIF (v_adb_ledger_id IS NOT NULL) THEN
944 increment_adb(
945 v_adb_ledger_id,
946 'PERIOD_NAME',
947 'JOURNAL_EFFECTIVE_DATE',
948 'CALCULATION_EFFECTIVE_DATE',
949 'YYYY/MM/DD HH24:MI:SS');
950 ELSIF (v_con_ledger_id IS NOT NULL) THEN
951 cons_inc_private(
955 'CALCULATION_EFFECTIVE_DATE',
952 v_con_ledger_id,
953 'PERIOD_NAME',
954 'JOURNAL_EFFECTIVE_DATE',
956 'YYYY/MM/DD HH24:MI:SS');
957 END IF;
958 ELSIF program_name = 'GLALGEN' THEN
959 IF fnd_resub.get_param_number('allocation_set_id',batch_pnum)<> 0 THEN
960 error_code := -37;
961 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
962 fnd_message.set_token('PARA','allocation_set_id');
963 error_buffer := fnd_message.get;
964 RAISE exit_fail;
965 END IF;
966
967 v_batch_id := fnd_resub.get_parameter(batch_pnum);
968
969 IF fnd_resub.get_param_number('LEDGER_ID',ledger_pnum) <>0 THEN
970 error_code := -38;
971 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
972 fnd_message.set_token('PARA','LEDGER_ID');
973 error_buffer := fnd_message.get;
974 RAISE exit_fail;
975 END IF;
976
977 v_ledger_id := fnd_resub.get_parameter(ledger_pnum);
978
979 IF(con_ledger_flag = 'Y') THEN
980 IF fnd_resub.get_param_number('average_journal_flag',usage_pnum) <>0 THEN
981 error_code := -39;
982 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
983 fnd_message.set_token('PARA','average_journal_flag');
984 error_buffer := fnd_message.get;
985 RAISE exit_fail;
986 END IF;
987 usage_flag := fnd_resub.get_parameter(usage_pnum);
988 ELSE
989 usage_flag := 'N';
990 END IF;
991
992 OPEN get_adb_batches;
993 LOOP
994 FETCH get_adb_batches into current_bid,batch_code;
995 EXIT WHEN get_adb_batches%NOTFOUND;
996
997 IF (batch_code = 'A') THEN
998 if(v_adb_ledger_id IS NULL) THEN
999 -- Check if ADB ledgers exist in the batch
1000 BEGIN
1001 SELECT lgr.ledger_id
1002 into v_adb_ledger_id
1003 FROM gl_alloc_formulas af,
1004 gl_alloc_formula_lines al,
1005 gl_ledger_set_assignments lsa,
1006 gl_ledgers lgr
1007 WHERE af.allocation_batch_id = current_bid
1008 AND al.allocation_formula_id = af.allocation_formula_id
1009 AND al.line_number IN (4, 5)
1010 AND lsa.ledger_set_id (+) = nvl(al.ledger_id,v_ledger_id)
1011 AND sysdate BETWEEN
1012 nvl(trunc(lsa.start_date), sysdate - 1)
1013 AND nvl(trunc(lsa.end_date), sysdate + 1)
1014 AND lgr.ledger_id = nvl(lsa.ledger_id,
1015 nvl(al.ledger_id,v_ledger_id))
1016 AND lgr.object_type_code = 'L'
1017 AND lgr.enable_average_balances_flag = 'Y'
1018 AND lgr.consolidation_ledger_flag = 'N'
1019 AND rownum = 1;
1020 EXCEPTION
1021 WHEN NO_DATA_FOUND THEN
1022 v_adb_ledger_id := NULL;
1023 END;
1024 END IF;
1025
1026 if(v_con_ledger_id IS NULL) THEN
1027 -- Check if consolidation ledgers exist in the batch
1028 BEGIN
1029 SELECT lgr.ledger_id
1030 into v_con_ledger_id
1031 FROM gl_alloc_formulas af,
1032 gl_alloc_formula_lines al,
1033 gl_ledger_set_assignments ls,
1034 gl_ledgers lgr
1035 WHERE af.allocation_batch_id = current_bid
1036 AND al.allocation_formula_id = af.allocation_formula_id
1037 AND al.line_number IN (4, 5)
1038 AND ls.ledger_set_id (+) = nvl(al.ledger_id,v_ledger_id)
1039 AND sysdate BETWEEN
1040 nvl(trunc(ls.start_date), sysdate - 1)
1041 AND nvl(trunc(ls.end_date), sysdate + 1)
1042 AND lgr.ledger_id = nvl(ls.ledger_id,
1043 nvl(al.ledger_id,v_ledger_id))
1044 AND lgr.object_type_code = 'L'
1045 AND lgr.enable_average_balances_flag = 'Y'
1046 AND lgr.consolidation_ledger_flag = 'Y'
1047 AND rownum = 1;
1048 EXCEPTION
1049 WHEN NO_DATA_FOUND THEN
1050 v_con_ledger_id := NULL;
1051 END;
1052 END IF;
1053
1054 if(v_con_ledger_id IS NOT NULL AND v_adb_ledger_id IS NOT NULL) THEN
1055 EXIT;
1056 end if;
1057 ELSIF (batch_code = 'R') THEN
1058 IF(v_adb_ledger_id IS NULL) THEN
1059 -- Check if ADB ledgers exist in the batch
1060 BEGIN
1061 SELECT lgr.ledger_id
1062 into v_adb_ledger_id
1063 FROM gl_recurring_headers rh, gl_ledgers lgr
1064 WHERE rh.recurring_batch_id = current_bid
1065 AND lgr.ledger_id = rh.ledger_id
1066 AND lgr.enable_average_balances_flag = 'Y'
1067 AND lgr.consolidation_ledger_flag = 'N'
1068 AND rownum = 1;
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND THEN
1071 v_adb_ledger_id := NULL;
1072 END;
1073 END IF;
1074
1075 IF(v_con_ledger_id IS NULL) THEN
1079 into v_con_ledger_id
1076 -- Check if consolidation ledgers exist in the batch
1077 BEGIN
1078 SELECT lgr.ledger_id
1080 FROM gl_recurring_headers rh, gl_ledgers lgr
1081 WHERE rh.recurring_batch_id = current_bid
1082 AND lgr.ledger_id = rh.ledger_id
1083 AND lgr.enable_average_balances_flag = 'Y'
1084 AND lgr.consolidation_ledger_flag = 'Y'
1085 AND rownum =1;
1086 EXCEPTION
1087 WHEN NO_DATA_FOUND THEN
1088 v_con_ledger_id := NULL;
1089 END;
1090 END IF;
1091
1092 IF(v_con_ledger_id IS NOT NULL AND v_adb_ledger_id IS NOT NULL) THEN
1093 EXIT;
1094 END IF;
1095
1096 END IF;
1097 END LOOP;
1098 CLOSE get_adb_batches;
1099
1100 IF (v_adb_ledger_id IS NULL and v_con_ledger_id IS NULL) THEN
1101 random_ledger_id := get_random_ledger('GLALGEN',v_ledger_id,v_batch_id);
1102 increment_period(random_ledger_id,'PERIOD_NAME');
1103 ELSIF (v_adb_ledger_id IS NOT NULL AND v_con_ledger_id IS NOT NULL ) THEN
1104 IF(usage_flag = 'Y') THEN
1105 cons_inc_private(
1106 v_con_ledger_id,
1107 'PERIOD_NAME',
1108 'JOURNAL_EFFECTIVE_DATE',
1109 'CALCULATION_EFFECTIVE_DATE',
1110 'YYYY/MM/DD HH24:MI:SS');
1111 ELSE
1112 increment_adb(
1113 v_adb_ledger_id,
1114 'PERIOD_NAME',
1115 'JOURNAL_EFFECTIVE_DATE',
1116 'CALCULATION_EFFECTIVE_DATE',
1117 'YYYY/MM/DD HH24:MI:SS');
1118 END IF;
1119 ELSIF (v_adb_ledger_id IS NOT NULL) THEN
1120 increment_adb(
1121 v_adb_ledger_id,
1122 'PERIOD_NAME',
1123 'JOURNAL_EFFECTIVE_DATE',
1124 'CALCULATION_EFFECTIVE_DATE',
1125 'YYYY/MM/DD HH24:MI:SS');
1126 ELSIF (v_con_ledger_id IS NOT NULL) THEN
1127 cons_inc_private(
1128 v_con_ledger_id,
1129 'PERIOD_NAME',
1130 'JOURNAL_EFFECTIVE_DATE',
1131 'CALCULATION_EFFECTIVE_DATE',
1132 'YYYY/MM/DD HH24:MI:SS');
1133 END IF;
1134
1135 ELSIF program_name = 'GLPRBE' THEN
1136 IF fnd_resub.get_param_number('Budget Batch Id',batch_pnum) <> 0 THEN
1137 error_code := -40;
1138 fnd_message.set_name('SQLGL','GL_SCH_INC_GET_PARA_NUM');
1139 fnd_message.set_token('PARA','Ledger Id');
1140 error_buffer := fnd_message.get;
1141 RAISE exit_fail;
1142 END IF;
1143
1144 v_batch_id := fnd_resub.get_parameter(batch_pnum);
1145 random_ledger_id := get_random_ledger('GLPRBE',NULL,v_batch_id);
1146 increment_period(random_ledger_id,'PERIOD_NAME_START');
1147 increment_period(random_ledger_id,'PERIOD_NAME_END');
1148 END IF;
1149
1150 fnd_resub.return_info(0, error_buffer);
1151 EXCEPTION
1152 WHEN exit_fail THEN
1153 fnd_resub.return_info(error_code, error_buffer);
1154 WHEN OTHERS THEN
1155 error_buffer :=
1156 'gl_srs_incrementor_api.increment_parameters error #'
1157 || TO_CHAR(error_code)
1158 || ' : '
1159 || SUBSTR(SQLERRM, 1, 100);
1160 fnd_resub.return_info(error_code, error_buffer);
1161 END increment_parameters;
1162
1163 PROCEDURE increment_date(
1164 x_date_para VARCHAR2,
1165 x_period_flag VARCHAR2,
1166 x_period_para VARCHAR2,
1167 x_ledger_id NUMBER) IS
1168 l_value fnd_profile_option_values.profile_option_value%TYPE;
1169 error_code NUMBER;
1170 v_last_period VARCHAR2(15);
1171 temp_date VARCHAR2(45);
1172 c_date DATE;
1173 v_date VARCHAR2(45);
1174 v_last_sch_date DATE;
1175 v_sch_date DATE;
1176 v_days_elapsed NUMBER;
1177 date_pnum NUMBER;
1178 period_pnum NUMBER;
1179 exit_fail EXCEPTION;
1180 c_start_period VARCHAR2(15);
1181 BEGIN
1182 error_buffer := '';
1183
1184 -- Do not increment if user did not check the increment date flag
1185 IF fnd_resub.get_increment_flag = 'N' THEN
1186 fnd_resub.return_info(0, error_buffer);
1187 RETURN;
1188 END IF;
1189
1190 --fnd_profile.get('GL_SET_OF_BKS_ID', l_value);
1191 --v_sob_id := TO_NUMBER(l_value);
1192 v_sch_date := fnd_resub.get_requested_start_date;
1193 v_days_elapsed := fnd_resub.get_rusub_delta;
1194 v_last_sch_date := TRUNC( v_sch_date
1195 - v_days_elapsed);
1196
1197 IF fnd_resub.get_param_number(x_date_para, date_pnum) <> 0 THEN
1198 error_code := -400;
1199 -- Cannot get parameter number for PARA. Please check your
1200 -- concurrent program definition
1201 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
1202 fnd_message.set_token('PARA', x_date_para);
1203 error_buffer := fnd_message.get;
1204 RAISE exit_fail;
1205 END IF;
1206
1207 temp_date := fnd_resub.get_parameter(date_pnum);
1208 temp_date := SUBSTR(temp_date, 1, 10);
1209 c_date := TO_DATE(temp_date, 'YYYY/MM/DD');
1213 || ' 00:00:00';
1210 c_date := c_date
1211 + v_days_elapsed;
1212 v_date := TO_CHAR(c_date, 'YYYY/MM/DD')
1214 fnd_resub.set_parameter(date_pnum, v_date);
1215
1216 IF (x_period_flag = 'Y') THEN
1217 BEGIN
1218 SELECT period_name
1219 INTO c_start_period
1220 FROM gl_period_statuses p
1221 WHERE p.application_id = 101
1222 AND p.ledger_id = x_ledger_id
1223 AND c_date BETWEEN p.start_date
1224 AND p.end_date + 0.99998843
1225 AND p.adjustment_period_flag = 'N';
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228 error_code := -410;
1229 fnd_message.set_name('SQLGL', 'GL_SCH_INC_ADJ_PERIOD');
1230 error_buffer := fnd_message.get;
1231 RAISE exit_fail;
1232 END;
1233
1234 IF fnd_resub.get_param_number(x_period_para, period_pnum) <> 0 THEN
1235 error_code := -420;
1236 -- Cannot get parameter number for PARA. Please check your
1237 -- concurrent program definition
1238 fnd_message.set_name('SQLGL', 'GL_SCH_INC_GET_PARA_NUM');
1239 fnd_message.set_token('PARA', x_period_para);
1240 error_buffer := fnd_message.get;
1241 RAISE exit_fail;
1242 END IF;
1243
1244 fnd_resub.set_parameter(period_pnum, c_start_period);
1245 END IF;
1246
1247 fnd_resub.return_info(0, error_buffer);
1248 EXCEPTION
1249 WHEN exit_fail THEN
1250 fnd_resub.return_info(error_code, error_buffer);
1251 WHEN OTHERS THEN
1252 error_buffer := 'gl_srs_incrementor_api.increment_date error #'
1253 || TO_CHAR(error_code)
1254 || ' : '
1255 || SUBSTR(SQLERRM, 1, 100);
1256 fnd_resub.return_info(error_code, error_buffer);
1257 END increment_date;
1258
1259
1260 FUNCTION get_random_ledger(
1261 x_batch_type VARCHAR2,
1262 x_ledger_id NUMBER,
1263 x_batch_id NUMBER) return NUMBER IS
1264
1265 CURSOR random_batch IS
1266 SELECT batch_id,batch_type_code
1267 FROM gl_auto_alloc_batches
1268 WHERE allocation_set_id = x_batch_id;
1269
1270 CURSOR rje_ledger(random_bid number) IS
1271 SELECT ledger_id
1272 FROM gl_recurring_headers
1273 WHERE recurring_batch_id = random_bid;
1274
1275
1276 CURSOR ma_ledger (random_bid number) IS
1277 SELECT lgr.ledger_id
1278 FROM gl_alloc_formulas af,
1279 gl_alloc_formula_lines afl,
1280 gl_ledger_set_assignments lsa,
1281 gl_ledgers lgr
1282 WHERE af.allocation_batch_id = random_bid
1283 AND afl.allocation_formula_id = af.allocation_formula_id
1284 AND afl.line_number IN (4, 5)
1285 AND lsa.ledger_set_id (+) = nvl(afl.ledger_id, x_ledger_id)
1286 AND sysdate BETWEEN
1287 nvl(trunc(lsa.start_date), sysdate - 1)
1288 AND nvl(trunc(lsa.end_date), sysdate + 1)
1289 AND lgr.ledger_id = nvl(lsa.ledger_id,
1290 nvl(afl.ledger_id, x_ledger_id))
1291 AND lgr.object_type_code = 'L';
1292
1293 CURSOR mb_ledger (random_bid number)IS
1294 SELECT lgr.ledger_id
1295 FROM gl_alloc_formulas af,
1296 gl_alloc_formula_lines afl,
1297 gl_ledger_set_assignments lsa,
1298 gl_ledgers lgr
1299 WHERE af.allocation_batch_id = random_bid
1300 AND afl.allocation_formula_id = af.allocation_formula_id
1301 AND afl.line_number IN (4, 5)
1302 AND lsa.ledger_set_id (+) = afl.ledger_id
1303 AND sysdate BETWEEN
1304 nvl(trunc(lsa.start_date), sysdate - 1)
1305 AND nvl(trunc(lsa.end_date), sysdate + 1)
1306 AND lgr.ledger_id = nvl(lsa.ledger_id, afl.ledger_id)
1307 AND lgr.object_type_code = 'L';
1308
1309 random_id NUMBER;
1310 random_bid NUMBER;
1311 random_btype VARCHAR2(1);
1312 BEGIN
1313
1314 IF (x_batch_type = 'GLALGEN') THEN
1315
1316 OPEN random_batch;
1317 FETCH random_batch into random_bid, random_btype;
1318 CLOSE random_batch;
1319
1320 IF(random_btype = 'R') THEN
1321 OPEN rje_ledger(random_bid);
1322 FETCH rje_ledger INTO random_id;
1323 CLOSE rje_ledger;
1324 ELSIF (random_btype = 'B') THEN
1325 OPEN mb_ledger(random_bid);
1326 FETCH mb_ledger INTO random_id;
1327 CLOSE mb_ledger;
1328 ELSIF (random_btype = 'A' or random_btype = 'E') THEN
1329 OPEN ma_ledger(random_bid);
1330 FETCH ma_ledger INTO random_id;
1331 CLOSE ma_ledger;
1332 END IF;
1333
1334 ELSIF (x_batch_type = 'GLAMAS') THEN
1335
1336 random_bid := x_batch_id;
1337 OPEN ma_ledger(random_bid);
1338 FETCH ma_ledger INTO random_id;
1339 CLOSE ma_ledger;
1340
1341 ELSIF (x_batch_type = 'GLPRJE' OR x_batch_type = 'GLPRBE') THEN
1342
1343 random_bid := x_batch_id;
1344 OPEN rje_ledger(random_bid);
1345 FETCH rje_ledger INTO random_id;
1346 CLOSE rje_ledger;
1347 ELSE
1348
1349 random_id := -1;
1350
1351 END IF;
1352
1353 RETURN random_id;
1354
1355 END get_random_ledger;
1356
1357 END gl_srs_incrementor_api;