[Home] [Help]
PACKAGE BODY: APPS.ARP_DATES
Source
1 PACKAGE BODY ARP_DATES AS
2 /* $Header: ARTUDATB.pls 120.10 2010/07/06 12:29:43 npanchak ship $ */
3
4
5 pg_ai_pds_exist_cursor integer;
6 pg_ai_overlapping_pds_cursor integer;
7 pg_form_pds_exist_cursor integer;
8 pg_form_overlapping_pds_cursor integer;
9
10 pg_set_of_books_id binary_integer;
11 pg_application_id binary_integer;
12
13 IN_ADVANCE CONSTANT NUMBER := -2;
14 IN_ARREARS CONSTANT NUMBER := -3;
15
16
20 | |
17 /*===========================================================================+
18 | PROCEDURE |
19 | prepare_val_gl_pds_sql_stmt |
21 | DESCRIPTION |
22 | Constructs and parses the SQL statements that perform the GL date rule |
23 | validation. |
24 | |
25 | The cursor IDs are assigned to package level variables so that the |
26 | parses can be reused. |
27 | |
28 | If the SQL statements have already been parsed, the procedure exits |
29 | without doing anything. |
30 | |
31 | SCOPE - PRIVATE |
32 | |
33 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
34 | arp_util.debug |
35 | arp_util.print_fcn_label |
36 | dbms_sql.parse |
37 | dbms_sql.open_cursor |
38 | |
39 | ARGUMENTS : IN: |
40 | p_ai_flag |
41 | OUT: |
42 | None |
43 | IN/ OUT: |
44 | None |
45 | |
46 | RETURNS : NONE |
47 | |
48 | NOTES |
49 | |
50 | MODIFICATION HISTORY |
51 | 26-JUL-95 Charlie Tomberg Created |
52 | 19-Dec-05 GyanaJyothi Included the Partial Period Rule types |
53 | |
54 +===========================================================================*/
55
56 PROCEDURE prepare_val_gl_pds_sql_stmt(
57 p_ai_flag IN boolean
58 ) IS
59
60 l_all_pds_exist_sql varchar2(5000);
61 l_overlapping_pds_sql varchar2(2000);
62 l_temp_duration varchar2(40);
63 l_temp_rule varchar2(40);
64 l_temp_sob varchar2(40);
65 l_temp_rule_start_date varchar2(40);
66 l_temp_bind varchar2(50);
67 l_temp_end varchar2(40);
68 l_temp_end2 varchar2(40);
69
70 l_all_pds_exist_cursor integer;
71 l_overlapping_pds_cursor integer;
72
73 BEGIN
74
75 arp_util.print_fcn_label('arp_dates.prepare_val_gl_pds_sql_stmt()+ ');
76
77 arp_util.debug('p_ai_flag = ' ||
78 arp_trx_util.boolean_to_varchar2(p_ai_flag) ,
79 arp_global.MSG_LEVEL_DEBUG);
80
81 /*------------------------------------------------------------+
82 | Don't do anything if the desired statements have already |
83 | been constructed and parsed. |
84 +------------------------------------------------------------*/
85
86 IF (
87 (
88 p_ai_flag = TRUE AND
89 pg_ai_pds_exist_cursor IS NOT NULL
90 )
91 OR
92 (
93 p_ai_flag = FALSE AND
94 pg_form_pds_exist_cursor IS NOT NULL
95 )
96 )
97 THEN
98 arp_util.debug('No SQL statement constuction or parse is required',
99 arp_global.MSG_LEVEL_DEBUG);
100 arp_util.print_fcn_label('arp_dates.construct_val_gl_pds_sql_stmt()- ');
101 RETURN;
102
103 END IF;
104
105 l_all_pds_exist_sql := null;
106
107
108 /*--------------------------------------------------+
109 | Set AutoInvoice and form specific SQL fragments |
110 +--------------------------------------------------*/
111
112 IF (p_ai_flag = TRUE)
113 THEN
114 l_temp_duration := 'l.accounting_rule_duration';
115 l_temp_rule := 'l.accounting_rule_id';
116 l_temp_sob := 'l.set_of_books_id';
117 l_temp_rule_start_date := 'l.rule_start_date';
118 l_temp_bind := '
119 )
120 AND 2 =
121 (';
122 l_temp_end := '
123 )
124 )';
125 l_temp_end2 := '
126 )';
127 ELSE
128 l_temp_duration := 'nvl(:accounting_rule_duration, 1)';
129 l_temp_rule := ':accounting_rule_id';
130 l_temp_sob := ':sob_id';
131 l_temp_rule_start_date := ':rule_start_date';
132 l_temp_bind := '
133 UNION ';
134 l_temp_end := '
135 ORDER BY 1';
136 l_temp_end2 := null;
137 END IF;
138
139 /*-----------------------------------------------+
140 | Validation #1: Check that all periods exist |
141 +-----------------------------------------------*/
142
143 IF (p_ai_flag = TRUE)
147 interface_line_id,
144 THEN l_all_pds_exist_sql :=
145 'INSERT INTO ra_interface_errors
146 (
148 message_text,
149 invalid_value,
150 org_id
151 )
152 SELECT l.interface_line_id,
153 :all_pds_error_msg,
154 l.rule_start_date,
155 l.org_id
156 FROM ra_interface_lines_gt l
157 WHERE l.request_id = :request_id
158 AND nvl(l.interface_status,
159 ''~'') <> ''P''
160 AND l.customer_trx_id IS NOT NULL
161 AND l.invoicing_rule_id IS NOT NULL
162 AND l.rule_start_date IS NOT NULL
163 AND l.link_to_line_id IS NULL
164 AND (
165 NOT EXISTS
166 (
167 ';
168 END IF;
169
170
171 l_all_pds_exist_sql := l_all_pds_exist_sql ||
172 ' SELECT 1
173 FROM gl_periods p1, /* to get the first period */
174 gl_periods p2, /* to get the last period */
175 gl_sets_of_books b,
176 gl_period_types t,
177 ra_rules r
178 WHERE r.rule_id = ' || l_temp_rule || '
179 AND b.set_of_books_id = ' || l_temp_sob || '
180 AND ' || l_temp_rule_start_date || ' BETWEEN p1.start_date
181 AND p1.end_date
182 AND r.frequency <> ''SPECIFIC''
183 AND p1.period_set_name = b.period_set_name
184 AND p1.adjustment_period_flag = ''N''
185 AND p2.adjustment_period_flag = ''N''
186 AND p1.period_type = r.frequency
187 AND t.period_type = p1.period_type
188 AND p2.period_set_name = b.period_set_name
189 AND p2.period_type = p1.period_type
190 AND ( p2.end_date,
191 ( TO_CHAR(p2.period_year,999999)||''-''||
192 TO_CHAR(p2.period_num,999999)||''-''||
193 TO_CHAR( DECODE(r.type, ''ACC_DUR'',
194 ' || l_temp_duration ||',
195 ''PP_DR_ALL'',
196 ' || l_temp_duration ||',
197 ''PP_DR_PP'',
198 ' || l_temp_duration ||',
199
200 r.occurrences),
201 9999)
202 )
203 ) =
204 ( SELECT MAX(p9.end_date),
205 MAX( TO_CHAR(p9.period_year,999999)||''-''||
206 TO_CHAR(p9.period_num,999999)||''-''||
207 TO_CHAR(rownum,9999)
208 )
209 FROM gl_periods p9
210 WHERE p9.period_set_name = p1.period_set_name
211 AND p9.period_type = p1.period_type
212 AND p9.start_date >= p1.start_date
213 AND p9.adjustment_period_flag = ''N''
214 AND rownum <= ( DECODE( r.type, ''ACC_DUR'',
215 ' || l_temp_duration ||',
216 ''PP_DR_ALL'',
217 ' || l_temp_duration ||',
218 ''PP_DR_PP'',
219 ' || l_temp_duration ||',
220 r.occurrences)
221 )
222 )
223 AND DECODE( r.type, ''ACC_DUR'',
224 ' || l_temp_duration ||',
225 ''PP_DR_ALL'',
226 ' || l_temp_duration ||',
227 ''PP_DR_PP'',
228 ' || l_temp_duration ||',
229 r.occurrences) =
230 ( SELECT COUNT(p3.period_set_name)
231 FROM gl_periods p3
232 WHERE p3.period_set_name = b.period_set_name
233 AND p3.period_type = p1.period_type
234 AND p3.adjustment_period_flag = ''N''
235 AND p3.start_date >= p1.start_date
236 AND p3.start_date <= p2.start_date
237 ) ' ||
238 l_temp_bind || '
239 SELECT DECODE(
240 COUNT(*),
241 0, 2,
242 DECODE(
243 SUM( r.occurrences ) / COUNT(*),
244 COUNT(*), 1,
245 2
246 )
247 )
248 FROM gl_periods p,
249 gl_sets_of_books b,
250 ra_rules r,
251 ra_rule_schedules rl
252 WHERE r.rule_id =' || l_temp_rule || '
253 AND r.frequency =''SPECIFIC''
254 AND rl.rule_id = r.rule_id
255 AND b.set_of_books_id = ' || l_temp_sob || '
256 AND p.period_set_name = b.period_set_name
257 AND p.period_type = b.accounted_period_type
258 AND p.adjustment_period_flag = ''N''
259 AND rl.rule_date BETWEEN p.start_date AND p.end_date
260 GROUP BY r.frequency ' ||
261 l_temp_end;
262
266
263 /*------------------------------------------------------------+
264 | Validation #2: Check for overlapping periods |
265 +------------------------------------------------------------*/
267 l_overlapping_pds_sql := null;
268
269 IF ( p_ai_flag = TRUE )
270 THEN l_overlapping_pds_sql :=
271 'INSERT INTO ra_interface_errors
272 (
273 interface_line_id,
274 message_text,
275 invalid_value,
276 org_id
277 )
278 SELECT l.interface_line_id,
279 :overlapping_pds_error_msg,
280 l.rule_start_date,
281 l.org_id
282 FROM ra_interface_lines_gt l
283 WHERE l.request_id = :request_id
284 AND nvl(
285 l.interface_status,
286 ''~''
287 ) <> ''P''
288 AND l.invoicing_rule_id IS NOT NULL
289 AND l.rule_start_date IS NOT NULL
290 AND l.customer_trx_id IS NOT NULL
291 AND l.link_to_line_id IS NULL
292 AND EXISTS
293 (
294 ';
295 END IF;
296
297
298 l_overlapping_pds_sql := l_overlapping_pds_sql ||
299 ' SELECT 1
300 FROM gl_periods p1,
301 gl_periods p2,
302 gl_sets_of_books b,
303 ra_rules r
304 WHERE r.rule_id = ' || l_temp_rule || '
305 AND b.set_of_books_id = ' || l_temp_sob || '
306 AND p1.period_set_name = b.period_set_name
307 AND p1.period_type = DECODE(
308 r.frequency,
309 ''SPECIFIC'', b.accounted_period_type,
310 r.frequency
311 )
312 AND p2.period_set_name = b.period_set_name
313 AND p2.period_type = p1.period_type
314 AND p1.adjustment_period_flag = ''N''
315 AND p2.adjustment_period_flag = ''N''
316 AND p2.start_date <= p1.end_date
317 AND p2.end_date >= p1.end_date
318 /* don''t check period with itself */
319 AND p2.period_set_name || p2.period_name <>
320 p1.period_set_name || p1.period_name
321 ' || l_temp_end2;
322
323
324 arp_util.debug('', arp_global.MSG_LEVEL_DEBUG);
325 arp_util.debug('------------------ All Periods Exist SQL ---------------',
326 arp_global.MSG_LEVEL_DEBUG);
327 arp_util.debug(l_all_pds_exist_sql, arp_global.MSG_LEVEL_DEBUG);
328 arp_util.debug('', arp_global.MSG_LEVEL_DEBUG);
329 arp_util.debug('length: ' || length(l_all_pds_exist_sql),
330 arp_global.MSG_LEVEL_DEBUG);
331
332 arp_util.debug('', arp_global.MSG_LEVEL_DEBUG);
333 arp_util.debug('', arp_global.MSG_LEVEL_DEBUG);
334 arp_util.debug('----------------- Overlapping Periods SQL ---------------',
335 arp_global.MSG_LEVEL_DEBUG);
336 arp_util.debug(l_overlapping_pds_sql, arp_global.MSG_LEVEL_DEBUG);
337 arp_util.debug('', arp_global.MSG_LEVEL_DEBUG);
338 arp_util.debug('length: ' || length(l_overlapping_pds_sql),
339 arp_global.MSG_LEVEL_DEBUG);
340 arp_util.debug('', arp_global.MSG_LEVEL_DEBUG);
341
342 /*-----------------------------------------------+
343 | Open and Parse the All Periods Exist cursor |
344 +-----------------------------------------------*/
345
346 l_all_pds_exist_cursor := dbms_sql.open_cursor;
347
348 dbms_sql.parse( l_all_pds_exist_cursor ,
349 l_all_pds_exist_sql,
350 dbms_sql.v7 );
351
352 /*-------------------------------------------------+
353 | Open and Parse the Overlapping Periods cursor |
354 +-------------------------------------------------*/
355
356 l_overlapping_pds_cursor := dbms_sql.open_cursor;
357
358 dbms_sql.parse( l_overlapping_pds_cursor,
359 l_overlapping_pds_sql,
360 dbms_sql.v7 );
361
362 /*------------------------------------------------------------+
363 | Set the package globals to hold the newly parsed cursors |
364 +------------------------------------------------------------*/
365
366 IF ( p_ai_flag = TRUE )
367 THEN
368 pg_ai_pds_exist_cursor := l_all_pds_exist_cursor;
369 pg_ai_overlapping_pds_cursor := l_overlapping_pds_cursor;
370 ELSE
371 pg_form_pds_exist_cursor := l_all_pds_exist_cursor;
372 pg_form_overlapping_pds_cursor := l_overlapping_pds_cursor;
373 END IF;
374
375 arp_util.print_fcn_label('arp_dates.prepare_val_gl_pds_sql_stmt()- ');
376
377
378 EXCEPTION
379 WHEN OTHERS THEN
380
381 /*---------------------------------------------+
382 | Display parameters and raise the exception |
383 +---------------------------------------------*/
384
385 arp_util.debug(
386 'EXCEPTION: arp_dates.prepare_val_gl_pds_sql_stmt()');
387
388 arp_util.debug('');
389 arp_util.debug('---- parameters for prepare_val_gl_pds_sql_stmt() -----');
390 arp_util.debug('p_ai_flag = ' ||
391 arp_trx_util.boolean_to_varchar2(p_ai_flag) );
392
393 RAISE;
394
395 END;
396
397 /*===========================================================================+
398 | PROCEDURE |
399 | val_gl_periods_for_rules |
400 | |
401 | DESCRIPTION |
405 | |
402 | Validates for an accounting rule and rule start date, that: |
403 | - GL periods exist |
404 | - that there are no overlapping periods |
406 | SCOPE - PUBLIC |
407 | |
408 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
409 | arp_util.debug |
410 | arp_util.print_fcn_label |
411 | dbms_sql.bind_variable |
412 | dbms_sql.define_column |
413 | dbms_sql.execute |
414 | dbms_sql.execute_and_fetch |
415 | dbms_sql.column_value |
416 | |
417 | ARGUMENTS : IN: |
418 | p_line_rec |
419 | p_customer_trx_line_id |
420 | OUT: |
421 | l_derive_gldate_flag |
422 | l_amount_changed_flag |
423 | IN/ OUT: |
424 | None |
425 | |
426 | RETURNS : NONE |
427 | |
428 | NOTES |
429 | |
430 | MODIFICATION HISTORY |
431 | 17-JUL-95 Charlie Tomberg Created |
432 | |
433 +===========================================================================*/
434
435 PROCEDURE val_gl_periods_for_rules(
436 p_request_id IN ra_customer_trx.request_id%type,
437 p_acc_rule_id IN ra_rules.rule_id%type,
438 p_acc_duration IN
439 ra_customer_trx_lines.accounting_rule_duration%type,
440 p_rule_start_date IN
441 ra_customer_trx_lines.rule_start_date%type,
442 p_sob_id IN
443 gl_sets_of_books.set_of_books_id%type ) IS
444 l_ai_flag boolean;
445 l_count integer;
446 l_result integer;
447
448 BEGIN
449
450 arp_util.print_fcn_label('arp_dates.val_gl_periods_for_rules()+ ');
451
452 /*---------------------------------------------+
453 | Do nothing if the line does not use rules |
454 +---------------------------------------------*/
455
456 IF ( p_acc_rule_id IS NULL )
457 THEN arp_util.debug('accounting_rule_id is null - no action taken');
458 arp_util.print_fcn_label('arp_dates.val_gl_periods_for_rules()- ');
459 RETURN;
460 END IF;
461
462 /*-----------------------------------------------------------------+
463 | Set the AutoInvoice flag to TRUE if a request_id was provided |
464 +-----------------------------------------------------------------*/
465
466 IF (p_request_id IS NULL)
467 THEN l_ai_flag := FALSE;
468 ELSE l_ai_flag := TRUE;
469 END IF;
470
471 /*------------------------------------------------------------------------+
472 | Construct and parse the SQL statements for the two validation SQLs. |
473 | |
474 | If the required SQL statements have already been constructed and |
475 | parsed in this session, they are not constructed and parsed again. |
476 | In that case, this function returns without doing anything and the |
477 | existing cursors will be used. |
478 +------------------------------------------------------------------------*/
479
480 prepare_val_gl_pds_sql_stmt( l_ai_flag );
481
482
483 /*-----------------------------+
484 | Bind the parameter values |
485 +-----------------------------*/
486
487 IF (l_ai_flag = TRUE )
488 THEN
489 dbms_sql.bind_variable( pg_ai_pds_exist_cursor,
490 ':all_pds_error_msg',
491 'AR_RAXTRX-1783');
492
493 dbms_sql.bind_variable( pg_ai_pds_exist_cursor,
494 ':request_id',
495 p_request_id);
496
497
498 dbms_sql.bind_variable( pg_ai_overlapping_pds_cursor,
499 ':overlapping_pds_error_msg',
500 'AR_RAXTRX-1784');
501
502 dbms_sql.bind_variable( pg_ai_overlapping_pds_cursor,
503 ':request_id',
504 p_request_id);
505 ELSE
506
507 dbms_sql.bind_variable( pg_form_pds_exist_cursor,
508 ':accounting_rule_duration',
509 p_acc_duration);
510
511 dbms_sql.bind_variable( pg_form_pds_exist_cursor,
512 ':accounting_rule_id',
513 p_acc_rule_id);
514
515 dbms_sql.bind_variable( pg_form_pds_exist_cursor,
516 ':sob_id',
517 p_sob_id);
518
519
520 dbms_sql.bind_variable( pg_form_pds_exist_cursor,
521 ':rule_start_date',
525 dbms_sql.bind_variable( pg_form_overlapping_pds_cursor,
522 p_rule_start_date);
523
524
526 ':accounting_rule_id',
527 p_acc_rule_id);
528
529 dbms_sql.bind_variable( pg_form_overlapping_pds_cursor,
530 ':sob_id',
531 p_sob_id);
532
533 dbms_sql.define_column(pg_form_pds_exist_cursor, 1, l_result);
534
535 END IF;
536
537
538 /*-------------------------------------+
539 | Execute the validation statements |
540 +-------------------------------------*/
541
542 IF ( l_ai_flag = TRUE )
543 THEN
544
545 l_count := dbms_sql.execute(pg_ai_pds_exist_cursor);
546
547 arp_util.debug('AI periods exist validation count: ' || l_count,
548 arp_global.MSG_LEVEL_DEBUG);
549
550
551 l_count := dbms_sql.execute(pg_ai_overlapping_pds_cursor);
552
553 arp_util.debug('AI overlapping validation count : ' || l_count,
554 arp_global.MSG_LEVEL_DEBUG );
555 ELSE
556
557 /*---------------------------------------------------------------+
558 | Execute the periods exist validation. |
559 | The validation fails if either no rows were returned by the |
560 | query or the query returned a value of 2. |
561 +---------------------------------------------------------------*/
562
563 l_count := dbms_sql.execute_and_fetch( pg_form_pds_exist_cursor,
564 FALSE);
565
566 dbms_sql.column_value(pg_form_pds_exist_cursor, 1, l_result);
567
568 arp_util.debug('Form periods exist validation count: ' || l_count ||
569 ' result: ' || l_result, arp_global.MSG_LEVEL_DEBUG);
570
571 IF (
572 l_count = 0 OR
573 l_result = 2
574 )
575 THEN
576 arp_util.debug('Form periods exist validation failed');
577 fnd_message.set_name('AR', 'AR_RAXTRX-1783');
578 app_exception.raise_exception;
579 END IF;
580
581
582 /*----------------------------------------------+
583 | Execute the overlapping periods validation. |
584 +-----------------------------------------------*/
585
586 l_count := dbms_sql.execute_and_fetch( pg_form_overlapping_pds_cursor,
587 FALSE);
588
589 arp_util.debug('Form overlapping validation count: ' || l_count,
590 arp_global.MSG_LEVEL_DEBUG);
591
592 IF ( l_count <> 0 )
593 THEN
594 arp_util.debug('Form overlapping periods validation failed');
595 fnd_message.set_name('AR', 'AR_RAXTRX-1784');
596 app_exception.raise_exception;
597 END IF;
598
599
600 END IF;
601
602 arp_util.print_fcn_label('arp_dates.val_gl_periods_for_rules()- ');
603
604
605 EXCEPTION
606 WHEN OTHERS THEN
607
608 /*---------------------------------------------+
609 | Display parameters and raise the exception |
610 +---------------------------------------------*/
611
612 arp_util.debug('EXCEPTION: arp_dates.val_gl_periods_for_rules()');
613
614 arp_util.debug('');
615 arp_util.debug('------- parameters for val_gl_periods_for_rules() -------');
616
617 arp_util.debug( 'p_request_id = ' || p_request_id );
618 arp_util.debug( 'p_acc_rule_id = ' || p_acc_rule_id );
619 arp_util.debug( 'p_acc_duration = ' || p_acc_duration );
620 arp_util.debug( 'p_rule_start_date = ' || p_rule_start_date );
621 arp_util.debug( 'p_sob_id = ' || p_sob_id );
622
623 RAISE;
624
625 END;
626
627
628
629 /*===========================================================================+
630 | PROCEDURE |
631 | get_date_based_on_rev_sched |
632 | |
633 | DESCRIPTION |
634 | Validates for an accounting rule and rule start date, that: |
635 | - GL periods exist |
636 | - that there are no overlapping periods |
637 | |
638 | SCOPE - PUBLIC |
639 | |
640 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
641 | arp_util.debug |
642 | arp_util.print_fcn_label |
643 | |
644 | ARGUMENTS : IN: |
645 | p_customer_trx_id |
646 | p_invoicing_rule_id |
647 | OUT: |
648 | p_candidate_date |
649 | IN/ OUT: |
650 | None |
651 | |
652 | RETURNS : NONE |
653 | |
654 | NOTES |
655 | |
656 | MODIFICATION HISTORY |
657 | 28-JUL-95 Charlie Tomberg Created |
658 | |
659 | 10-Nov-00 Y Rakotonirainy Bug 1296673 |
660 | Modified to exclude adjustment period |
661 | while calculating gl periods | |
662 | |
663 +===========================================================================*/
664
665 PROCEDURE get_date_based_on_rev_sched(
666 p_customer_trx_id IN
667 ra_customer_trx.customer_trx_id%type,
668 p_invoicing_rule_id IN
669 ra_customer_trx.invoicing_rule_id%type,
670 p_candidate_date OUT NOCOPY DATE
671 ) IS
672
673 actual_num_fiscal_year gl_periods.period_year%TYPE;
674
675 BEGIN
676
677 arp_util.print_fcn_label('arp_dates.get_date_based_on_rev_sched()+ ');
678
679
680 IF (p_invoicing_rule_id = IN_ADVANCE )
681 THEN
682
683 arp_util.debug('Invoicing Rule: In Advance',
684 arp_global.MSG_LEVEL_DEBUG);
685
686 SELECT MIN(rl.rule_start_date)
687 INTO p_candidate_date
688 FROM ra_customer_trx_lines rl
689 WHERE rl.customer_trx_id = p_customer_trx_id
690 AND line_type = 'LINE';
691
692 ELSE
693
694 arp_util.debug('Invoicing Rule: In Arrears',
695 arp_global.MSG_LEVEL_DEBUG);
696 /*Bug 2505767, Added an additional check in the where clause to pick up only
697 those periods which match with the type defined in the set of books*/
698 SELECT COUNT(gp1.period_set_name)
699 INTO actual_num_fiscal_year
700 FROM ra_customer_trx_lines ctl,
701 gl_sets_of_books b,
702 gl_periods gp1
703 WHERE ctl.customer_trx_id = p_customer_trx_id
704 AND ctl.set_of_books_id = b.set_of_books_id
705 AND gp1.period_set_name = b.period_set_name
706 AND gp1.period_type = b.accounted_period_type
707 AND gp1.period_year = to_number(to_char(ctl.rule_start_date,'YYYY'))
708 AND gp1.adjustment_period_flag ='N' ;
709
710 SELECT MAX(
711 DECODE(
712 r.frequency,
713 'SPECIFIC', MAX(rl.rule_date),
714 LEAST(
715 ctl.rule_start_date -
716 gp1.start_date +
717 gp2.start_date,
718 gp2.end_date,
719 nvl(ctl.rule_end_date,gp2.end_date) --Bug5022614
720 )
721 )
722 )
723 INTO p_candidate_date
724 FROM ra_customer_trx_lines ctl,
725 gl_periods gp1,
726 gl_periods gp2,
727 gl_sets_of_books b,
728 gl_period_types t,
729 ra_rules r,
730 ra_rule_schedules rl
731 WHERE ctl.customer_trx_id = p_customer_trx_id
732 AND ctl.accounting_rule_id = r.rule_id
733 AND rl.rule_id (+) = decode(r.frequency,
734 'SPECIFIC', r.rule_id,
735 -9.9)
736 AND ctl.set_of_books_id = b.set_of_books_id
737 AND ctl.set_of_books_id = pg_set_of_books_id
738 AND ctl.rule_start_date BETWEEN gp1.start_date
739 AND gp1.end_date
740 AND gp1.period_set_name = b.period_set_name
741 AND UPPER(gp1.period_type) =
742 UPPER(
743 DECODE(r.frequency,
744 'SPECIFIC', b.accounted_period_type,
745 r.frequency
746 )
747 )
748 AND t.period_type = gp1.period_type
749 AND gp2.period_set_name = gp1.period_set_name
750 AND gp2.period_type = gp1.period_type
751 AND gp2.period_year =
752 gp1.period_year +
753 TRUNC(
754 (
755 gp1.period_num -1 +
756 DECODE(
757 r.type,
758 'ACC_DUR', ctl.accounting_rule_duration,
759 'PP_DR_PP',ctl.accounting_rule_duration,
760 'PP_DR_ALL',ctl.accounting_rule_duration,
761 r.occurrences
762 ) -1
763 ) /
764 actual_num_fiscal_year
765 )
766 AND gp2.period_num =
767 MOD(
768 (
769 gp1.period_num -1 +
770 DECODE(
771 r.type,
772 'ACC_DUR', ctl.accounting_rule_duration,
773 'PP_DR_PP',ctl.accounting_rule_duration,
774 'PP_DR_ALL',ctl.accounting_rule_duration,
775 r.occurrences
776 ) -1
777 ),
778 actual_num_fiscal_year
779 ) + 1
780 GROUP BY
781 ctl.customer_trx_id,
782 r.frequency,
783 rl.rule_date,
784 ctl.rule_start_date,
785 gp1.start_date,
786 gp2.start_date,
787 gp2.end_date,
788 ctl.rule_end_Date;
789
790 END IF;
791
792 arp_util.print_fcn_label('arp_dates.get_date_based_on_rev_sched()- ');
793
794
795 EXCEPTION
796 WHEN OTHERS THEN
797
798 /*---------------------------------------------+
799 | Display parameters and raise the exception |
800 +---------------------------------------------*/
801
802 arp_util.debug('EXCEPTION: arp_dates.get_date_based_on_rev_sched()');
803
804 arp_util.debug('');
805 arp_util.debug('---- parameters for get_date_based_on_rev_sched() ----');
806
807 arp_util.debug('p_customer_trx_id = ' || p_customer_trx_id );
808 arp_util.debug('p_invoicing_rule_id = ' || p_invoicing_rule_id );
809
810 RAISE;
811
812 END;
813
814 /*===========================================================================+
815 | PROCEDURE |
816 | derive_gl_trx_dates_from_rules |
817 | |
818 | DESCRIPTION |
819 | This procedure accepts the customer trx id that is passed and Checks |
820 | to see whether there is a rule for this Customer trx id. If there |
821 | are no rules then the user exit return to the calling module. If the |
822 | rule is of type Advanced Invoice then the minimum rule start date is |
823 | selected. If the rule is of type Arrears Invoice then the Maximum |
824 | distributions GL dates for the transaction lines is selected. The |
825 | candidate date is then validated. If it succeeds, that date is used. |
826 | if the validation fails, the default gl date is obtaioned and used. |
827 | |
828 | SCOPE - PUBLIC |
829 | |
830 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
831 | arp_util.debug |
832 | arp_util.print_fcn_label |
833 | |
834 | ARGUMENTS : IN: |
835 | p_customer_trx_id |
836 | OUT: |
837 | p_gl_date |
838 | p_trx_date |
839 | IN/ OUT: |
840 | None |
841 | |
842 | RETURNS : NONE |
843 | |
844 | NOTES |
845 | |
846 | MODIFICATION HISTORY |
847 | 31-JUL-95 Charlie Tomberg Created |
848 | 08-FEB-96 Martin Johnson Removed updates to database. |
849 | Removed set_names for displaying |
850 | new dates. |
851 | 29-MAY-96 Martin Johnson BugNo:368206. Fixed so that derived |
852 | trx_date gets returned for Arrears |
853 | case. |
854 | |
855 +===========================================================================*/
856
857 PROCEDURE derive_gl_trx_dates_from_rules (
858 p_customer_trx_id IN
859 ra_customer_trx.customer_trx_id%type,
860 p_gl_date IN OUT NOCOPY
861 ra_cust_trx_line_gl_dist.gl_date%type,
862 p_trx_date IN OUT NOCOPY
863 ra_customer_trx.trx_date%type,
864 p_recalculate_tax_flag IN OUT NOCOPY boolean,
865 P_created_from IN ar_trx_header_gt.created_from%type default NULL,
866 p_defaulted_gl_date_flag IN ar_trx_header_gt.defaulted_gl_date_flag%type default NULL
867 ) IS
868
869
870 l_candidate_date date;
871 l_db_trx_date ra_customer_trx.trx_date%type;
872 l_db_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
873
877 l_exchange_rate ra_customer_trx.exchange_rate%type;
874 l_trx_date ra_customer_trx.trx_date%type;
875 l_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
876
878 l_currency_code ra_customer_trx.invoice_currency_code%type;
879
880 l_period_name varchar2(15);
881 l_start_date date;
882 l_end_date date;
883 l_closing_status varchar2(1);
884 l_period_type varchar2(15);
885 l_period_year number;
886 l_period_num number;
887 l_quarter_num number;
888 l_allow_not_open_flag varchar2(2);
889 l_default_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
890 l_defaulting_rule_used varchar2(100);
891 l_error_message varchar2(100);
892
893 l_invoicing_rule_id ra_customer_trx.invoicing_rule_id%type;
894
895 l_trx_rec ra_customer_trx%rowtype;
896 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
897
898
899 BEGIN
900
901 arp_util.print_fcn_label('arp_dates.derive_gl_trx_dates_from_rules()+ ');
902
903
904 /*---------------------------+
905 | Validate the parameters |
906 +---------------------------*/
907
908 IF ( p_customer_trx_id IS NULL )
909 THEN
910 fnd_message.set_name('AR', 'AR_INV_ARGS');
911 fnd_message.set_token('PROCEDURE',
912 'derive_gl_trx_dates_from_rules()');
913 app_exception.raise_exception;
914
915 END IF;
916
917
918 /*--------------------------------------------------------------+
919 | Get the existing dates, currency and exchange information |
920 | from the database |
921 +--------------------------------------------------------------*/
922
923 SELECT MIN(ct.trx_date),
924 MIN(ctlgd.gl_date),
925 MAX(ct.exchange_rate),
926 MAX(ct.invoice_currency_code),
927 MAX(ct.invoicing_rule_id)
928 INTO l_db_trx_date,
929 l_db_gl_date,
930 l_exchange_rate,
931 l_currency_code,
932 l_invoicing_rule_id
933 FROM ra_customer_trx ct,
934 ra_cust_trx_line_gl_dist ctlgd
935 WHERE ct.customer_trx_id = ctlgd.customer_trx_id
936 AND ct.customer_trx_id = p_customer_trx_id
937 AND ctlgd.account_class = 'REC'
938 AND ctlgd.latest_rec_flag = 'Y';
939
940 arp_util.debug('DB trx_date: ' || l_db_trx_date ||
941 ', DB gl_date: ' || l_db_gl_date,
942 arp_global.MSG_LEVEL_DEBUG);
943 arp_util.debug('exchange rate: ' || l_exchange_rate ||
944 ', currency code: ' || l_currency_code,
945 arp_global.MSG_LEVEL_DEBUG);
946
947 arp_util.debug('invoicing_rule_id: ' || l_invoicing_rule_id,
948 arp_global.MSG_LEVEL_DEBUG);
949
950 /*--------------------------------------------------------------------------+
951 | If the rule is In Advance, get the first date that revenue is recognized |
952 | If the rule is In Arrears, get the last date that revenue is recognized |
953 +--------------------------------------------------------------------------*/
954
955 get_date_based_on_rev_sched(
956 p_customer_trx_id,
957 l_invoicing_rule_id,
958 l_candidate_date
959 );
960 arp_util.debug('candidate date: ' || l_candidate_date,
961 arp_global.MSG_LEVEL_DEBUG);
962
963 /*-------------------------------------------------------------------------+
964 | If l_candidate_date is null, it means that all of the lines have |
965 | null rule_start_date, or there are no lines. In this case just return |
966 | the current dates and return exit. |
967 +-------------------------------------------------------------------------*/
968
969 IF (l_candidate_date IS NULL )
970 THEN l_trx_date := l_db_trx_date;
971 l_gl_date := l_db_gl_date;
972
973 arp_util.debug('Candidate date is null - no action taken',
974 arp_global.MSG_LEVEL_DEBUG);
975 ELSE
976
977 /*-------------------------------------------------------+
978 | Determine the closing status of the candidate date. |
979 +-------------------------------------------------------*/
980
981 arp_standard.gl_period_info( l_candidate_date,
982 l_period_name,
983 l_start_date,
984 l_end_date,
985 l_closing_status,
986 l_period_type,
987 l_period_year,
988 l_period_num,
989 l_quarter_num
990 );
991
992 arp_util.debug('closing status: ' || l_closing_status,
993 arp_global.MSG_LEVEL_DEBUG);
994
995 IF (
996 l_closing_status = 'O' OR
997 l_closing_status = 'F' OR
998 (
999 l_closing_status = 'N' AND
1000 l_invoicing_rule_id = IN_ARREARS
1001 )
1002 )
1003 THEN
1004
1005 arp_util.debug('candidate date passed closing status ' ||
1006 'validation', arp_global.MSG_LEVEL_DEBUG);
1007
1008 IF (l_db_gl_date <> l_candidate_date )
1009 THEN
1010 /* bug 5884520 Added condition to avoid re defaulting gl date*/
1011 IF ((nvl(p_created_from,'X') = 'AR_INVOICE_API' ) AND
1012 (nvl(p_defaulted_gl_date_flag,'Y') = 'N' ) AND
1013 (l_invoicing_rule_id = IN_ADVANCE) ) THEN
1014 l_gl_date := l_db_gl_date;
1015 ELSE
1016 l_gl_date := l_candidate_date;
1017 END IF;
1018 END IF;
1019
1020 IF (
1021 l_invoicing_rule_id = IN_ADVANCE AND
1022 l_db_trx_date > l_candidate_date
1023 )
1024 OR
1025 ( l_invoicing_rule_id = IN_ARREARS AND
1026 l_db_trx_date <> l_candidate_date
1027 )
1028 THEN
1029 -- bug 6350680
1030 --l_trx_date := l_candidate_date;
1031 l_trx_date := l_db_trx_date;
1032 END IF;
1033 ELSE
1034
1035 arp_util.debug('candidate date failed closing status ' ||
1036 'validation', arp_global.MSG_LEVEL_DEBUG);
1037
1038 /*----------------------------------------------------+
1039 | If the candidate date is not in a closed period, |
1040 | then it cannot be used. Exit with an error. |
1041 +----------------------------------------------------*/
1042
1043 /* Bug 858875: Check closing status 'W' (close pending) */
1044 IF ( nvl(l_closing_status, 'Z') not in ( 'C', 'N', 'W') )
1045 THEN
1046 arp_util.debug('No accounting period defined for ' ||
1047 to_char( l_candidate_date ) ||
1048 ' closing status: ' ||
1049 l_closing_status);
1050
1051 fnd_message.set_name('AR', 'AR_TW_NO_PERIOD_DEFINED');
1052 fnd_message.set_token('DATE',
1053 to_char( l_candidate_date ));
1054 app_exception.raise_exception;
1055 END IF;
1056
1057 IF ( l_invoicing_rule_id = IN_ARREARS )
1058 THEN l_allow_not_open_flag := 'Y';
1059 ELSE l_allow_not_open_flag := 'N';
1060 END IF;
1061
1062 arp_util.debug('app_id: ' || pg_application_id ||
1063 ' , sob: ' || pg_set_of_books_id,
1064 arp_global.MSG_LEVEL_DEBUG);
1065
1066 IF (arp_standard.validate_and_default_gl_date(
1067 l_candidate_date,
1068 null,
1069 null,
1070 null,
1071 null,
1072 null,
1073 null,
1074 null,
1075 l_allow_not_open_flag,
1076 l_invoicing_rule_id,
1077 pg_set_of_books_id,
1078 pg_application_id,
1079 l_default_gl_date,
1080 l_defaulting_rule_used,
1081 l_error_message
1082 ) = FALSE
1083 )
1084 THEN fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1085 fnd_message.set_token('GENERIC_TEXT',
1086 l_error_message);
1087 fnd_message.set_name('AR', 'AR_TW_DERIVE_FAILURE');
1088 arp_util.debug(l_error_message);
1089 app_exception.raise_exception;
1090 ELSE arp_util.debug('defaulting rule used: ' ||
1091 l_defaulting_rule_used,
1092 arp_global.MSG_LEVEL_DEBUG);
1093
1094
1095 arp_util.debug('validate_and_default_gl_date() returned '
1096 || ' date : ' || l_default_gl_date,
1097 arp_global.MSG_LEVEL_DEBUG);
1098
1099 IF (l_db_gl_date <> l_default_gl_date OR l_db_gl_date is NULL)
1100 THEN
1101 l_gl_date := l_default_gl_date;
1102 arp_util.debug('Preparing to set gl_date to ' ||
1103 l_default_gl_date);
1104 END IF;
1105
1106 IF (
1107 l_invoicing_rule_id = IN_ADVANCE AND
1108 l_db_trx_date > l_default_gl_date
1109 )
1110 OR
1111 ( l_invoicing_rule_id = IN_ARREARS AND
1112 l_db_trx_date <> l_default_gl_date
1113 )
1114 THEN
1115 -- Bug 6350680
1116 --l_trx_date := l_default_gl_date;
1117 l_trx_date := l_db_trx_date;
1118 arp_util.debug('Preparing to set trx_date to ' ||
1119 l_default_gl_date);
1120
1121 END IF;
1122
1123 END IF; /* Default date was generated */
1124
1125 END IF; /* Candidate date is not valid case */
1126
1127 END IF; /* Candidate date is not null case */
1128
1129 IF ( l_db_trx_date <> nvl(l_trx_date, l_db_trx_date) )
1130 THEN
1131 p_recalculate_tax_flag := TRUE;
1132 END IF;
1133
1134 /*---------------------------------------------+
1135 | Copy the new dates to the out NOCOPY parameters. |
1136 +---------------------------------------------*/
1137
1138 p_trx_date := nvl(l_trx_date, p_trx_date);
1139 p_gl_date := nvl(l_gl_date, p_gl_date);
1140
1141
1142 arp_util.print_fcn_label('arp_dates.derive_gl_trx_dates_from_rules()- ');
1143
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146
1147 /*---------------------------------------------+
1148 | Display parameters and raise the exception |
1149 +---------------------------------------------*/
1150
1151 arp_util.debug('EXCEPTION: arp_dates.derive_gl_trx_dates_from_rules()');
1152
1153 arp_util.debug('');
1154 arp_util.debug('---- parameters for derive_gl_trx_dates_from_rules() ----');
1155
1156 arp_util.debug('p_customer_trx_id = ' || p_customer_trx_id);
1157 arp_util.debug('l_invoicing_rule_id = ' || l_invoicing_rule_id);
1158
1159 RAISE;
1160
1161 END;
1162
1163 BEGIN
1164 pg_set_of_books_id :=
1165 arp_trx_global.system_info.system_parameters.set_of_books_id;
1166
1167 pg_application_id := 222;
1168 -- arp_trx_global.profile_info.application_id;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 arp_util.debug('EXCEPTION: arp_dates');
1173 RAISE;
1174
1175 END ARP_DATES;