[Home] [Help]
PACKAGE BODY: APPS.ARP_VIEW_CONSTANTS
Source
1 PACKAGE BODY ARP_VIEW_CONSTANTS AS
2 /* $Header: ARCUVIEB.pls 120.6 2005/11/14 18:40:26 jypandey ship $ */
3
4 pg_customer_id NUMBER;
5 pg_apply_date DATE;
6 pg_receipt_gl_date DATE;
7 pg_sales_order VARCHAR2(50) := NULL;
8 pg_status VARCHAR2(50) := NULL;
9 pg_receipt_currency VARCHAR2(15);
10 pg_incl_receipts_at_risk VARCHAR2(1) := NULL;
11
12 pg_ps_autorct_batch VARCHAR2(1) := NULL;
13
14 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
15
16 PROCEDURE set_customer_id (pn_customer_id IN NUMBER) IS
17 BEGIN
18 pg_customer_id := pn_customer_id;
19 END set_customer_id;
20
21 FUNCTION get_customer_id RETURN NUMBER IS
22 BEGIN
23 return pg_customer_id;
24 END get_customer_id;
25
26 PROCEDURE set_apply_date (pd_apply_date IN DATE) IS
27 BEGIN
28 pg_apply_date := pd_apply_date;
29 END set_apply_date;
30
31 FUNCTION get_apply_date RETURN DATE IS
32 BEGIN
33 return pg_apply_date;
34 END get_apply_date;
35
36 PROCEDURE set_receipt_gl_date (pd_receipt_gl_date IN DATE) IS
37 BEGIN
38 pg_receipt_gl_date := pd_receipt_gl_date;
39 END set_receipt_gl_date;
40
41 FUNCTION get_receipt_gl_date RETURN DATE IS
42 BEGIN
43 return pg_receipt_gl_date;
44 END get_receipt_gl_date;
45
46 PROCEDURE set_receipt_currency (pd_receipt_currency IN VARCHAR2) IS
47 BEGIN
48 pg_receipt_currency := pd_receipt_currency;
49 END;
50
51 FUNCTION get_receipt_currency RETURN VARCHAR2 IS
52 BEGIN
53 return pg_receipt_currency;
54 END;
55
56 /**********************************************/
57 /* Copied from ARP_STANDARD.IS_GL_DATE_VALID */
58 /**********************************************/
59
60 function is_gl_date_valid(
61 p_gl_date in date,
62 p_trx_date in date,
63 p_validation_date1 in date,
64 p_validation_date2 in date,
65 p_validation_date3 in date,
66 p_allow_not_open_flag in varchar2,
67 p_set_of_books_id in number,
68 p_application_id in number,
69 p_check_period_status in boolean default TRUE)
70 return boolean is
71
72 return_value boolean;
73 num_return_value number;
74 l_gl_date date;
75 l_trx_date date;
76 l_validation_date1 date;
77 l_validation_date2 date;
78 l_validation_date3 date;
79
80 begin
81 /* Bug fix: 955813 */
82 /*------------------------------+
83 | Initialize input variables |
84 +------------------------------*/
85 l_gl_date := trunc(p_gl_date);
86 l_trx_date := trunc(p_trx_date);
87 l_validation_date1 := trunc(p_validation_date1);
88 l_validation_date2 := trunc(p_validation_date2);
89 l_validation_date3 := trunc(p_validation_date3);
90
91 if (l_gl_date is null)
92 then return(FALSE);
93 end if;
94
95 if (l_gl_date < nvl(l_validation_date1, l_gl_date) )
96 then return(FALSE);
97 end if;
98
99 if (l_gl_date < nvl(l_validation_date2, l_gl_date) )
100 then return(FALSE);
101 end if;
102
103 if (l_gl_date < nvl(l_validation_date3, l_gl_date) )
104 then return(FALSE);
105 end if;
106
107 if (p_check_period_status = TRUE)
108 then
109
110
111 /* Bug 3828312 - Check value of p_allow_not_open_flag
112 and execute sql specifically based on its value
113 to improve sql performance */
114 IF (p_allow_not_open_flag = 'Y')
115 THEN
116 select decode(max(period_name),
117 '', 0,
118 1)
119 into num_return_value
120 from gl_period_statuses
121 where application_id = p_application_id
122 and set_of_books_id = p_set_of_books_id
123 and adjustment_period_flag = 'N'
124 and l_gl_date between start_date and end_date
125 and closing_status in ('O', 'F', 'N');
126 ELSE
127 select decode(max(period_name),
128 '', 0,
129 1)
130 into num_return_value
131 from gl_period_statuses
132 where application_id = p_application_id
133 and set_of_books_id = p_set_of_books_id
134 and adjustment_period_flag = 'N'
135 and l_gl_date between start_date and end_date
136 and closing_status in ('O', 'F');
137 END IF;
138
139 if (num_return_value = 1)
140 then return_value := TRUE;
141 else return_value := FALSE;
142 end if;
143
144 else return_value := TRUE;
145 end if;
146
147 return(return_value);
148
149 end; /* function is_gl_date_valid() */
150
151 /***********************************************************/
152 /* Copied from ARP_STANDARD.VALIDATE_AND_DEFAULT_GL_DATE */
153 /***********************************************************/
154
155 function mass_apps_default_gl_date(
156 gl_date in date,
157 trx_date in date,
158 validation_date1 in date,
159 validation_date2 in date,
160 validation_date3 in date,
161 default_date1 in date,
162 default_date2 in date,
163 default_date3 in date,
164 p_allow_not_open_flag in varchar2,
165 p_invoicing_rule_id in varchar2,
166 p_set_of_books_id in number,
167 p_application_id in number,
168 default_gl_date out NOCOPY date,
169 defaulting_rule_used out NOCOPY varchar2,
170 error_message out NOCOPY varchar2
171 ) return boolean is
172
173 allow_not_open_flag varchar2(2);
174 h_application_id number;
175 h_set_of_books_id number;
176 candidate_gl_date date;
177 candidate_start_gl_date date;
178 candidate_end_gl_date date;
179
180 l_gl_date date;
181 l_trx_date date;
182 l_validation_date1 date;
183 l_validation_date2 date;
184 l_validation_date3 date;
185 l_default_date1 date;
186 l_default_date2 date;
187 l_default_date3 date;
188
189 begin
190 /* Bug fix: 956649 */
191 /*------------------------------+
192 | Initialize input variables |
193 +------------------------------*/
194
195 l_gl_date := trunc(gl_date);
196 l_trx_date := trunc(trx_date);
197 l_validation_date1 := trunc(validation_date1);
198 l_validation_date2 := trunc(validation_date2);
199 l_validation_date3 := trunc(validation_date3);
200 l_default_date1 := trunc(default_date1);
201 l_default_date2 := trunc(default_date2);
202 l_default_date3 := trunc(default_date3);
203
204 /*------------------------------+
205 | Initialize output variables |
206 +------------------------------*/
207
208 defaulting_rule_used := '';
209 error_message := '';
210 default_gl_date := '';
211 candidate_gl_date := '';
212
213 /*---------------------------+
214 | Populate default values |
215 +---------------------------*/
216
217
218 if (p_allow_not_open_flag is null)
219 then allow_not_open_flag := 'N';
220 else allow_not_open_flag := p_allow_not_open_flag;
221 end if;
222
223 if (p_invoicing_rule_id = '-3')
224 then allow_not_open_flag := 'Y';
225 end if;
226
227 if (p_application_id is null)
228 then h_application_id := 222;
229 else h_application_id := p_application_id;
230 end if;
231
232 if (p_set_of_books_id is null)
233 then h_set_of_books_id := 2; -- sysparm.set_of_books_id;
234 else h_set_of_books_id := p_set_of_books_id;
235 end if;
236
237
238 /*--------------------------+
239 | Apply defaulting rules |
240 +--------------------------*/
241
242
243 /* Try the gl_date that was passed in */
244
245 if is_gl_date_valid(l_gl_date,
246 l_trx_date,
247 l_validation_date1,
248 l_validation_date2,
249 l_validation_date3,
250 allow_not_open_flag,
251 h_set_of_books_id,
252 h_application_id,
253 TRUE)
254 then default_gl_date := l_gl_date;
255 defaulting_rule_used := 'ORIGINAL GL_DATE';
256 return(TRUE);
257 end if;
258
259
260 /* Try the default dates that were passed in */
261
262 if is_gl_date_valid(l_default_date1,
263 l_trx_date,
264 l_validation_date1,
265 l_validation_date2,
266 l_validation_date3,
267 allow_not_open_flag,
268 h_set_of_books_id,
269 h_application_id,
270 TRUE)
271 then default_gl_date := l_default_date1;
272 defaulting_rule_used := 'DEFAULT_DATE1';
273 return(TRUE);
274 end if;
275
276 if is_gl_date_valid(l_default_date2,
277 l_trx_date,
278 l_validation_date1,
279 l_validation_date2,
280 l_validation_date3,
281 allow_not_open_flag,
282 h_set_of_books_id,
283 h_application_id,
284 TRUE)
285 then default_gl_date := l_default_date2;
286 defaulting_rule_used := 'DEFAULT_DATE2';
287 return(TRUE);
288 end if;
289
290 if is_gl_date_valid(l_default_date3,
291 l_trx_date,
292 l_validation_date1,
293 l_validation_date2,
294 l_validation_date3,
295 allow_not_open_flag,
296 h_set_of_books_id,
297 h_application_id,
298 TRUE)
299 then default_gl_date := l_default_date3;
300 defaulting_rule_used := 'DEFAULT_DATE3';
301 return(TRUE);
302 end if;
303
304
305 /*-----------------------------------------------------------------+
306 | If sysdate is in a Future period, |
307 | Then use the last day of the last Open period before sysdate. |
308 +-----------------------------------------------------------------*/
309
310
311 select max(d.end_date)
312 into candidate_gl_date
313 from gl_period_statuses d,
314 gl_period_statuses s
315 where d.application_id = s.application_id
316 and d.set_of_books_id = s.set_of_books_id
317 and d.adjustment_period_flag = 'N'
318 and d.end_date < sysdate
319 and d.closing_status = 'O'
320 and s.application_id = h_application_id
321 and s.set_of_books_id = h_set_of_books_id
322 and s.adjustment_period_flag = 'N'
323 and s.closing_status = 'F'
324 and sysdate between s.start_date and s.end_date;
325
326 if ( candidate_gl_date is not null )
327 then
328 if is_gl_date_valid(candidate_gl_date,
329 l_trx_date,
330 l_validation_date1,
331 l_validation_date2,
332 l_validation_date3,
333 allow_not_open_flag,
334 h_set_of_books_id,
335 h_application_id,
336 FALSE)
337 then default_gl_date := candidate_gl_date;
338 defaulting_rule_used :=
339 'LAST DAY OF OPEN PERIOD BEFORE FUTURE PERIOD';
340 return(TRUE);
341 end if;
342 end if;
343
344 /* Try sysdate */
345 if is_gl_date_valid(sysdate,
346 l_trx_date,
347 l_validation_date1,
348 l_validation_date2,
349 l_validation_date3,
350 allow_not_open_flag,
351 h_set_of_books_id,
352 h_application_id,
353 TRUE)
354 then default_gl_date := trunc(sysdate);
355 defaulting_rule_used := 'SYSDATE';
356 return(TRUE);
357 end if;
358
359 /* Try trx_date */
360 if ( trx_date is not null )
361 then
362
363 /* Try trx_date */
364 if is_gl_date_valid(l_trx_date,
365 l_trx_date,
366 l_validation_date1,
367 l_validation_date2,
368 l_validation_date3,
369 allow_not_open_flag,
370 h_set_of_books_id,
371 h_application_id,
372 TRUE)
373 then default_gl_date := l_trx_date;
374 defaulting_rule_used := 'TRX_DATE';
375 return(TRUE);
376 end if;
377
378 /* Try first Open period after trx_date */
379
380 select min(start_date)
381 into candidate_gl_date
382 from gl_period_statuses
383 where application_id = h_application_id
384 and set_of_books_id = h_set_of_books_id
385 and adjustment_period_flag = 'N'
386 and closing_status = 'O'
387 and start_date >= l_trx_date;
388
389 if ( candidate_gl_date is not null )
390 then
391 if is_gl_date_valid(candidate_gl_date,
392 l_trx_date,
393 l_validation_date1,
394 l_validation_date2,
395 l_validation_date3,
396 allow_not_open_flag,
397 h_set_of_books_id,
398 h_application_id,
399 FALSE)
400 then default_gl_date := candidate_gl_date;
401 defaulting_rule_used :=
402 'FIRST OPEN PERIOD AFTER TRX_DATE';
403 return(TRUE);
404 end if;
405 end if; /* candidate_gl_date is not null case */
406
407
408 /* Try first Future period after trx_date */
409
410 select min(start_date)
411 into candidate_gl_date
412 from gl_period_statuses
413 where application_id = h_application_id
414 and set_of_books_id = h_set_of_books_id
415 and adjustment_period_flag = 'N'
416 and closing_status = 'F'
417 and start_date >= l_trx_date;
418
419 if ( candidate_gl_date is not null )
420 then
421 if is_gl_date_valid(candidate_gl_date,
422 l_trx_date,
423 l_validation_date1,
424 l_validation_date2,
425 l_validation_date3,
426 allow_not_open_flag,
427 h_set_of_books_id,
428 h_application_id,
429 FALSE)
430 then default_gl_date := candidate_gl_date;
431 defaulting_rule_used :=
432 'FIRST FUTURE PERIOD AFTER TRX_DATE';
433 return(TRUE);
434 end if;
435 end if; /* candidate_gl_date is not null case */
436
437 else /* trx_date is not known case */
438
439 /* try the first open period after sysdate */
440
441 select max(start_date)
442 into candidate_gl_date
443 from gl_period_statuses
444 where application_id = h_application_id
445 and set_of_books_id = h_set_of_books_id
446 and adjustment_period_flag = 'N'
447 and closing_status = 'O'
448 and start_date >= sysdate;
449
450 if ( candidate_gl_date is not null )
451 then
452 if is_gl_date_valid(candidate_gl_date,
453 l_trx_date,
454 l_validation_date1,
455 l_validation_date2,
456 l_validation_date3,
457 allow_not_open_flag,
458 h_set_of_books_id,
459 h_application_id,
460 FALSE)
461 then default_gl_date := candidate_gl_date;
462 defaulting_rule_used :=
463 'FIRST OPEN PERIOD AFTER SYSDATE';
464 return(TRUE);
465 end if;
466 end if; /* candidate_gl_date is not null case */
467
468
469 /* try the last open period */
470
471 select max(start_date), max(end_date)
472 into candidate_start_gl_date,
473 candidate_end_gl_date
474 from gl_period_statuses
475 where application_id = h_application_id
476 and set_of_books_id = h_set_of_books_id
477 and adjustment_period_flag = 'N'
478 and closing_status = 'O';
479
480 if (sysdate > candidate_start_gl_date)
481 then candidate_gl_date := candidate_end_gl_date;
482 else candidate_gl_date := candidate_start_gl_date;
483 end if;
484
485 if ( candidate_gl_date is not null )
486 then
487 if is_gl_date_valid(candidate_gl_date,
488 l_trx_date,
489 l_validation_date1,
490 l_validation_date2,
491 l_validation_date3,
492 allow_not_open_flag,
493 h_set_of_books_id,
494 h_application_id,
495 FALSE)
496 then default_gl_date := candidate_gl_date;
497 defaulting_rule_used :=
498 'LAST OPEN PERIOD';
499 return(TRUE);
500 end if;
501 end if; /* candidate_gl_date is not null case */
502
503
504 /* try the first Future period >= sysdate */
505
506 select min(start_date)
507 into candidate_gl_date
508 from gl_period_statuses
509 where application_id = h_application_id
510 and set_of_books_id = h_set_of_books_id
511 and adjustment_period_flag = 'N'
512 and closing_status = 'F'
513 and start_date >= sysdate;
514
515
516 if ( candidate_gl_date is not null )
517 then
518 if is_gl_date_valid(candidate_gl_date,
519 l_trx_date,
520 l_validation_date1,
521 l_validation_date2,
522 l_validation_date3,
523 allow_not_open_flag,
524 h_set_of_books_id,
525 h_application_id,
526 FALSE)
527 then default_gl_date := candidate_gl_date;
528 defaulting_rule_used :=
529 'FIRST FUTURE PERIOD AFTER SYSDATE';
530 return(TRUE);
531 end if;
532 end if; /* candidate_gl_date is not null case */
533
534
535 /* try the last Future period */
536
537 select max(start_date), max(end_date)
538 into candidate_start_gl_date,
539 candidate_end_gl_date
540 from gl_period_statuses
541 where application_id = h_application_id
542 and set_of_books_id = h_set_of_books_id
543 and adjustment_period_flag = 'N'
544 and closing_status = 'F';
545
546 if (sysdate > candidate_start_gl_date)
547 then candidate_gl_date := candidate_end_gl_date;
548 else candidate_gl_date := candidate_start_gl_date;
549 end if;
550
551 if ( candidate_gl_date is not null )
552 then
553 if is_gl_date_valid(candidate_gl_date,
554 l_trx_date,
555 l_validation_date1,
556 l_validation_date2,
557 l_validation_date3,
558 allow_not_open_flag,
559 h_set_of_books_id,
560 h_application_id,
561 FALSE)
562 then default_gl_date := candidate_gl_date;
563 defaulting_rule_used :=
564 'LAST FUTURE PERIOD';
565 return(TRUE);
569
566 end if;
567 end if; /* candidate_gl_date is not null case */
568
570 end if; /* trx_date is null or not null */
571
572
573 return(TRUE);
574
575 EXCEPTION
576 WHEN OTHERS THEN
577 error_message := 'Error trapped by WHEN OTHERS exception';
578 -- ARP_STANDARD.VALIDATE_AND_DEFAULT_GL_DATE difference ...
579 -- Can't use sqleerm as it is not considered a "pure" function.
580 -- Replaced the following line with the above line.
581 -- error_message := 'arplbstd(): ' || sqlerrm;
582 return(FALSE);
583
584 end mass_apps_default_gl_date;
585
586 FUNCTION get_default_gl_date (pd_candidate_gl_date IN DATE) RETURN DATE IS
587
588 l_default_gl_date DATE;
589 l_defaulting_rule_used VARCHAR2(50);
590 l_error_message VARCHAR2(128);
591
592 l_set_of_books_id AR_SYSTEM_PARAMETERS.SET_OF_BOOKS_ID%TYPE;
593
594 BEGIN
595
596 -- Get the set of books id.
597 select set_of_books_id
598 into l_set_of_books_id
599 from ar_system_parameters ;
600
601 IF arp_view_constants.mass_apps_default_gl_date (
602 pd_candidate_gl_date,
603 NULL,
604 NULL,
605 NULL,
606 NULL,
607 NULL,
608 NULL,
609 NULL,
610 'N',
611 NULL,
612 l_set_of_books_id,
613 222,
614 l_default_gl_date,
615 l_defaulting_rule_used,
616 l_error_message
617 ) THEN
618 return l_default_gl_date;
619 ELSE
620 return null;
621 END IF;
622 END get_default_gl_date;
623 --
624 PROCEDURE set_sales_order (p_sales_order IN VARCHAR2) IS
625 BEGIN
626 pg_sales_order := p_sales_order;
627 END set_sales_order;
628
629 FUNCTION get_sales_order RETURN VARCHAR2 IS
630 BEGIN
631 return pg_sales_order;
632 END get_sales_order;
633
634 PROCEDURE set_status (p_status IN VARCHAR2) IS
635 BEGIN
636 pg_status := p_status;
637 END set_status;
638
639 FUNCTION get_status RETURN VARCHAR2 IS
640 BEGIN
641 return pg_status;
642 END get_status;
643
644 PROCEDURE set_incl_receipts_at_risk (p_incl_receipts_at_risk IN VARCHAR2) IS
645 BEGIN
646 pg_incl_receipts_at_risk := p_incl_receipts_at_risk;
647 END set_incl_receipts_at_risk;
648
649 FUNCTION get_incl_receipts_at_risk RETURN VARCHAR2 IS
650 BEGIN
651 return pg_incl_receipts_at_risk;
652 END get_incl_receipts_at_risk;
653
654 PROCEDURE set_ps_selected_in_batch (p_ps_autorct_batch IN varchar2) IS
655 BEGIN
656 pg_ps_autorct_batch := p_ps_autorct_batch;
657 END;
658
659 FUNCTION get_ps_selected_in_batch RETURN varchar2 IS
660 BEGIN
661 return pg_ps_autorct_batch;
662 END;
663
664 END ARP_VIEW_CONSTANTS;