[Home] [Help]
PACKAGE BODY: APPS.GL_GLRGNL_XMLP_PKG
Source
1 PACKAGE BODY GL_GLRGNL_XMLP_PKG AS
2 /* $Header: GLRGNLB.pls 120.0 2007/12/27 14:37:54 vijranga noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 /*srw.user_exit('FND SRWINIT');*/null;
8
9
10 declare
11 errbuf VARCHAR2(132);
12 errbuf2 VARCHAR2(132);
13 begin
14
15
16
17
18
19 begin
20 SELECT name, chart_of_accounts_id
21 INTO ACCESS_SET_NAME, STRUCT_NUM
22 FROM gl_access_sets
23 WHERE access_set_id = P_ACCESS_SET_ID;
24
25 exception
26 WHEN NO_DATA_FOUND THEN
27 errbuf := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
28 'DASID', to_char(P_ACCESS_SET_ID));
29 /*srw.message('00', errbuf);*/null;
30
31 raise_application_error(-20101,null);/*srw.program_abort;*/null;
32
33
34 WHEN OTHERS THEN
35 errbuf := SQLERRM;
36 /*srw.message('00', errbuf);*/null;
37
38 raise_application_error(-20101,null);/*srw.program_abort;*/null;
39
40 end;
41
42 /*srw.reference(STRUCT_NUM);*/null;
43
44
45 null;
46
47 /*srw.reference(STRUCT_NUM);*/null;
48
49
50 null;
51
52 /*srw.reference(STRUCT_NUM);*/null;
53
54
55 null;
56
57 /*srw.reference(STRUCT_NUM);*/null;
58
59
60 null;
61
62 /*srw.reference(STRUCT_NUM);*/null;
63
64
65 null;
66
67 /*srw.reference(STRUCT_NUM);*/null;
68
69
70 null;
71
72 /*srw.reference(STRUCT_NUM);*/null;
73
74
75 null;
76
77 /*srw.reference(STRUCT_NUM);*/null;
78
79
80 null;
81
82
83
84 if (P_KIND = 'L') then
85 SELECT_REFERENCE := 'jel.reference_1';
86 elsif (P_KIND = 'H') then
87 SELECT_REFERENCE := 'jeh.external_reference';
88 elsif (P_KIND = 'R') or (P_KIND = 'S') then
89 SELECT_REFERENCE := 'jel.reference_4';
90 else
91 SELECT_REFERENCE := 'jel.reference_1';
92 end if;
93
94
95
96 if (p_actual_flag = 'A') then
97 where_actual_type := '1 = 1';
98 elsif (p_actual_flag = 'B') then
99 where_actual_type := 'budget_version_id = ' ||
100 to_char(P_BUD_ENC_TYPE_ID);
101 else
102 where_actual_type := 'encumbrance_type_id = '|| to_char(P_BUD_ENC_TYPE_ID);
103 end if;
104
105
106 if (p_currency_type = 'S') then
107 where_currency_code := '( jeh.currency_code = ''STAT''' ||
108 ' OR jel.stat_amount is NOT NULL)';
109 select_dr := 'decode(jeh.currency_code, ''STAT'',' ||
110 'decode(nvl(jel.stat_amount,0),' ||
111 '0, jel.accounted_dr,' ||
112 'decode(sign(nvl(jel.stat_amount,0)),' ||
113 '-1, jel.accounted_dr,' ||
114 ' 1, (nvl(jel.accounted_dr,0) + ' ||
115 'jel.stat_amount),' ||
116 'jel.accounted_dr)),' ||
117 'decode(sign(nvl(jel.stat_amount,0)),' ||
118 '1, nvl(jel.stat_amount,0), ' ||
119 'NULL))';
120 select_cr := 'decode(jeh.currency_code, ''STAT'',' ||
121 'decode(nvl(jel.stat_amount,0),' ||
122 '0, jel.accounted_cr,' ||
123 'decode(sign(nvl(jel.stat_amount,0)),' ||
124 '-1, (nvl(jel.accounted_cr,0) - ' ||
125 'jel.stat_amount),' ||
126 ' 1, jel.accounted_cr,' ||
127 'jel.accounted_cr)),' ||
128 'decode(sign(nvl(jel.stat_amount,0)),' ||
129 '-1, (0 - nvl(jel.stat_amount,0)), ' ||
130 'NULL))';
131 else
132 where_currency_code := 'jeh.currency_code <> ''STAT''';
133 select_dr := 'nvl(jel.accounted_dr, 0)';
134
135 select_cr := 'nvl(jel.accounted_cr, 0)';
136 end if;
137
138 WHERE_INDEX := '1 = 1';
139
140 IF(P_CURRENCY_TYPE = 'S') THEN
141 RESULTING_CURRENCY := 'STAT';
142 ELSE
143 RESULTING_CURRENCY := P_LEDGER_CURRENCY;
144 END IF;
145
146 WHERE_DAS := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
147 P_ACCESS_SET_ID,
148 'R',
149 'LEDGER_COLUMN',
150 'LEDGER_ID',
151 'L2',
152 'SEG_COLUMN',
153 null,
154 'CC',
155 null);
156
157 if (WHERE_DAS is not null) then
158 WHERE_DAS := ' and ' || WHERE_DAS ;
159 end if;
160
161 end;
162 return (TRUE);
163 end;
164
165 function AfterReport return boolean is
166 begin
167
168 /*srw.user_exit('FND SRWEXIT');*/null;
169 return (TRUE);
170 end;
171
172 function OLD_CCIDFormula return Number is
173 begin
174
175 /*srw.reference(NEW_RECORD);*/null;
176
177 RETURN NULL; end;
178
179 function OLD_END_DRFormula return Number is
180 begin
181
182 /*srw.reference(BAD_START);*/null;
183
184 RETURN NULL; end;
185
186 function OLD_END_CRFormula return Number is
187 begin
188
189 /*srw.reference(BAD_START);*/null;
190
191 RETURN NULL; end;
192
193 function new_recordformula(ccid in number) return varchar2 is
194 begin
195
196 if ((old_ccid is null) or
197 (ccid <> old_ccid)) then
198 old_ccid := ccid;
199
200 return('Y');
201 else
202 return('N');
203 end if;
204 RETURN NULL; end;
205
206 function bad_startformula(new_record in varchar2, period_num in number, period_year in number, begin_dr in number, begin_cr in number, period_dr in number, period_cr in number) return varchar2 is
207 begin
208
209
210
211 if (new_record <> 'Y') and
212 (period_num <> 1) and
213 (period_year = last_period_year) and
214 ((begin_dr <> old_end_dr) or
215 (begin_cr <> old_end_cr)) then
216 old_end_dr := begin_dr + period_dr;
217 old_end_cr := begin_cr + period_cr;
218 last_period_year := period_year;
219 return('Y');
220 else
221 old_end_dr := begin_dr + period_dr;
222 old_end_cr := begin_cr + period_cr;
223 last_period_year := period_year;
224 return('N');
225 end if;
226 RETURN NULL; end;
227
228 function bad_endformula(period_dr in number, total_dr in number, period_cr in number, total_cr in number, template_id in number) return varchar2 is
229 begin
230
231
232 if ((period_dr <> total_dr) or
233 (period_cr <> total_cr)) and
234 (template_id is null) then
235 return('Y');
236 else
237 return('N');
238 end if;
239 RETURN NULL; end;
240
241 function BUD_ENC_TYPE_NAMEFormula return VARCHAR2 is
242 begin
243
244
245 declare
246 name VARCHAR2(30);
247 errbuf VARCHAR2(132);
248 errbuf2 VARCHAR2(132);
249 begin
250 gl_info.gl_get_bud_or_enc_name(P_ACTUAL_FLAG,
251 P_BUD_ENC_TYPE_ID,
252 name, errbuf);
253
254 if (errbuf is not null) then
255
256
257 errbuf2 := gl_message.get_message(
258 'GL_PLL_ROUTINE_ERROR', 'N',
259 'ROUTINE','gl_get_bud_enc_name'
260 );
261 /*srw.message('00', errbuf2);*/null;
262
263
264 /*srw.message('00', errbuf);*/null;
265
266
267 raise_application_error(-20101,null);/*srw.program_abort;*/null;
268
269 end if;
270
271 return(name);
272 end;
273 RETURN NULL; end;
274
275 function DISP_ACTUAL_FLAGFormula return VARCHAR2 is
276 begin
277
278
279 declare
280 value VARCHAR2(240);
281 errbuf VARCHAR2(132);
282 errbuf2 VARCHAR2(132);
283 begin
284 gl_info.gl_get_lookup_value('D', P_ACTUAL_FLAG,
285 'BATCH_TYPE', value, errbuf);
286
287 if (errbuf is not null) then
288
289
290 errbuf2 := gl_message.get_message(
291 'GL_PLL_ROUTINE_ERROR', 'N',
292 'ROUTINE','gl_get_lookup_value'
293 );
294 /*srw.message('00', errbuf2);*/null;
295
296
297 /*srw.message('00', errbuf);*/null;
298
299
300 raise_application_error(-20101,null);/*srw.program_abort;*/null;
301
302 end if;
303
304 return(value);
305 end;
306
307 RETURN NULL; end;
308
309 function START_DATEFormula return Date is
310 begin
311
312
313 declare
314 pstart DATE;
315 pend DATE;
316 errbuf VARCHAR2(132);
317 errbuf2 VARCHAR2(132);
318 begin
319 gl_get_period_dates(P_LEDGER_ID,
320 P_START_PERIOD,
321 pstart,
322 pend,
323 errbuf);
324
325 if (errbuf is not null) then
326
327
328 errbuf2 := gl_message.get_message(
329 'GL_PLL_ROUTINE_ERROR', 'N',
330 'ROUTINE','gl_get_period_dates'
331 );
332 /*srw.message('00', errbuf2);*/null;
333
334
335 /*srw.message('00', errbuf);*/null;
336
337
338 raise_application_error(-20101,null);/*srw.program_abort;*/null;
339
340 end if;
341
342 return(pstart);
343 end;
344 RETURN NULL; end;
345
346 function END_DATEFormula return Date is
347 begin
348
349
350 declare
351 pstart DATE;
352 pend DATE;
353 errbuf VARCHAR2(132);
354 errbuf2 VARCHAR2(132);
355 begin
356 gl_get_period_dates(P_LEDGER_ID,
357 P_END_PERIOD,
358 pstart,
359 pend,
360 errbuf);
361
362 if (errbuf is not null) then
363
364
365 errbuf2 := gl_message.get_message(
366 'GL_PLL_ROUTINE_ERROR', 'N',
367 'ROUTINE','gl_get_period_dates'
368 );
369 /*srw.message('00', errbuf2);*/null;
370
371
372 /*srw.message('00', errbuf);*/null;
373
374
375 raise_application_error(-20101,null);/*srw.program_abort;*/null;
376
377 end if;
378
379 return(pend);
380 end;
381 RETURN NULL; end;
382
383 function DISP_CRFormula return VARCHAR2 is
384 begin
385
386
387 declare
388 value VARCHAR2(80);
389 errbuf VARCHAR2(132);
390 errbuf2 VARCHAR2(132);
391 begin
392 gl_info.gl_get_lookup_value('M', 'C', 'DR_CR',
393 value, errbuf);
394
395 if (errbuf is not null) then
396
397
398 errbuf2 := gl_message.get_message(
399 'GL_PLL_ROUTINE_ERROR', 'N',
403
400 'ROUTINE','gl_get_lookup_value'
401 );
402 /*srw.message('00', errbuf2);*/null;
404
405 /*srw.message('00', errbuf);*/null;
406
407
408 raise_application_error(-20101,null);/*srw.program_abort;*/null;
409
410 end if;
411
412 return(value);
413 end;
414
415 RETURN NULL; end;
416
417 function begin_balformula(BEGIN_CR in number, BEGIN_DR in number) return number is
418 begin
419
420 if abs(BEGIN_CR) > abs (BEGIN_DR) then
421 return (BEGIN_CR - BEGIN_DR);
422 else
423 return (BEGIN_DR - BEGIN_CR);
424 end if;
425
426 end;
427
428 function end_balformula(BEGIN_CR in number, PERIOD_CR in number, BEGIN_DR in number, PERIOD_DR in number) return number is
429 begin
430
431 if abs(BEGIN_CR + PERIOD_CR ) > abs (BEGIN_DR + PERIOD_DR) then
432 return (BEGIN_CR + PERIOD_CR - (BEGIN_DR + PERIOD_DR));
433 else
434 return (BEGIN_DR + PERIOD_DR - (BEGIN_CR + PERIOD_CR));
435 end if;
436
437
438 end;
439
440 function accounted_balformula(ACCOUNTED_CR in number, ACCOUNTED_DR in number) return number is
441 begin
442
443 if ( abs (ACCOUNTED_CR) > abs (ACCOUNTED_DR)) then
444 return ( nvl( ACCOUNTED_CR ,0) - nvl(ACCOUNTED_DR , 0)) ;
445 else
446 return (nvl(ACCOUNTED_DR, 0) - nvl(ACCOUNTED_CR, 0));
447 end if;
448 end;
449
450 function LAST_PERIOD_YEARFormula return Number is
451 begin
452
453 /*srw.reference(BAD_START);*/null;
454
455 RETURN NULL; end;
456
457 procedure gl_get_period_dates (tledger_id in number,
458 tperiod_name in varchar2,
459 tstart_date out NOCOPY date ,
460 tend_date out NOCOPY date,
461 errbuf out NOCOPY varchar2)
462 is
463 ledger_obj_type VARCHAR2(1);
464 single_ledger_id NUMBER;
465 BEGIN
466 select object_type_code
467 into ledger_obj_type
468 from gl_ledgers
469 where ledger_id = tledger_id;
470
471 if (ledger_obj_type = 'L') then
472 single_ledger_id := tledger_id;
473 else
474 select l.ledger_id
475 into single_ledger_id
476 from gl_ledger_set_assignments ls, gl_ledgers l
477 where ls.ledger_set_id = tledger_id
478 and l.ledger_id = ls.ledger_id
479 and l.object_type_code = 'L'
480 and rownum = 1;
481 end if;
482
483 select start_date, end_date
484 into tstart_date, tend_date
485 from gl_period_statuses
486 where period_name = tperiod_name
487 and ledger_id = single_ledger_id
488 and application_id = 101;
489
490 EXCEPTION
491
492 WHEN NO_DATA_FOUND THEN
493
494 errbuf := gl_message.get_message('GL_PLL_INVALID_PERIOD', 'Y',
495 'PERIOD', tperiod_name,
496 'LDGID', to_char(tledger_id) );
497
498 WHEN OTHERS THEN
499
500 errbuf := SQLERRM;
501
502 END;
503
504 procedure gl_get_eff_period_num (tledger_id in number,
505 tperiod_name in varchar2,
506 teffnum out NOCOPY number,
507 errbuf out NOCOPY varchar2)
508 is
509 ledger_obj_type VARCHAR2(1);
510 single_ledger_id NUMBER;
511 BEGIN
512 select object_type_code
513 into ledger_obj_type
514 from gl_ledgers
515 where ledger_id = tledger_id;
516
517 if (ledger_obj_type = 'L') then
518 single_ledger_id := tledger_id;
519 else
520 select l.ledger_id
521 into single_ledger_id
522 from gl_ledger_set_assignments ls, gl_ledgers l
523 where ls.ledger_set_id = tledger_id
524 and l.ledger_id = ls.ledger_id
525 and l.object_type_code = 'L'
526 and rownum = 1;
527 end if;
528
529 select effective_period_num
530 into teffnum
531 from gl_period_statuses
532 where period_name = tperiod_name
533 and ledger_id = single_ledger_id
534 and application_id = 101;
535
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN
538 errbuf := gl_message.get_message('GL_PLL_INVALID_PERIOD', 'Y',
539 'PERIOD', tperiod_name,
540 'LDGID', to_char(tledger_id));
541
542 WHEN OTHERS THEN
543 errbuf := SQLERRM;
544
545 END;
546
547 function START_EFF_PERIOD_NUMFormula return Number is
548 begin
549
550
551 declare
552 peffperiod_num NUMBER;
553 errbuf VARCHAR2(132);
554 errbuf2 VARCHAR2(132);
555 begin
556 gl_get_eff_period_num(P_LEDGER_ID,
557 P_START_PERIOD,
558 peffperiod_num,
559 errbuf);
560
561 if (errbuf is not null) then
562
563
564 errbuf2 := gl_message.get_message(
565 'GL_PLL_ROUTINE_ERROR', 'N',
566 'ROUTINE','gl_get_eff_period_num'
567 );
568 /*srw.message('00', errbuf2);*/null;
569
570
571 /*srw.message('00', errbuf);*/null;
572
573
574 raise_application_error(-20101,null);/*srw.program_abort;*/null;
575
576 end if;
577
578 return(peffperiod_num);
579 end;
580 RETURN NULL; end;
581
582 function END_EFF_PERIOD_NUMFormula return Number is
583 begin
584
585
586 declare
587 peffperiod_num NUMBER;
588 errbuf VARCHAR2(132);
589 errbuf2 VARCHAR2(132);
590 begin
591 gl_get_eff_period_num(P_LEDGER_ID,
592 P_END_PERIOD,
593 peffperiod_num,
594 errbuf);
595
596 if (errbuf is not null) then
597
598
599 errbuf2 := gl_message.get_message(
600 'GL_PLL_ROUTINE_ERROR', 'N',
601 'ROUTINE','gl_get_eff_period_num'
602 );
603 /*srw.message('00', errbuf2);*/null;
604
605
606 /*srw.message('00', errbuf);*/null;
607
608
609 raise_application_error(-20101,null);/*srw.program_abort;*/null;
610
611 end if;
612
613 return(peffperiod_num);
614 end;
615 RETURN NULL; end;
616
617 function g_balancing_segmentgroupfilter(BAL_SECURE in varchar2) return boolean is
618 begin
619 /*srw.reference(STRUCT_NUM);*/null;
620
621 /*srw.reference(BAL_DATA);*/null;
622
623
624
625 if (BAL_SECURE ='S') then
626 return(FALSE);
627 else
628 return (TRUE);
629 end if;
630
631 end;
632
633 function g_accounting_flexfieldgroupfil(FLEX_SECURE in varchar2) return boolean is
634 begin
635
636 /*srw.reference(STRUCT_NUM);*/null;
637
638 /*srw.reference(FLEXDATA);*/null;
639
640
641
642 if(FLEX_SECURE = 'S') then
643 return(FALSE);
644 else
645 return (TRUE);
646 end if;
647
648 end;
649
650 function BeforePForm return boolean is
651 begin
652
653 return (TRUE);
654 end;
655
656 function AfterPForm return boolean is
657 begin
658
659 return (TRUE);
660 end;
661
662 function BetweenPage return boolean is
663 begin
664
665 return (TRUE);
666 end;
667
668 --Functions to refer Oracle report placeholders--
669
670 Function CP_1_p return number is
671 Begin
672 return CP_1;
673 END;
674 Function OLD_CCID_p return number is
675 Begin
676 return OLD_CCID;
677 END;
678 Function OLD_END_DR_p return number is
679 Begin
680 return OLD_END_DR;
681 END;
682 Function OLD_END_CR_p return number is
683 Begin
684 return OLD_END_CR;
685 END;
686 Function LAST_PERIOD_YEAR_p return number is
687 Begin
688 return LAST_PERIOD_YEAR;
689 END;
690 Function STRUCT_NUM_p return varchar2 is
691 Begin
692 return STRUCT_NUM;
693 END;
694 Function SELECT_ALL_p return varchar2 is
695 Begin
696 return SELECT_ALL;
697 END;
698 Function WHERE_p return varchar2 is
699 Begin
700 -- return WHERE;
701 return L_WHERE;
702 END;
703 Function ORDERBY_BAL_p return varchar2 is
704 Begin
705 return ORDERBY_BAL;
706 END;
707 Function ORDERBY_ACCT_p return varchar2 is
708 Begin
709 return ORDERBY_ACCT;
710 END;
711 Function ORDERBY_ALL_p return varchar2 is
712 Begin
713 return ORDERBY_ALL;
714 END;
715 Function SELECT_BAL_p return varchar2 is
716 Begin
717 return SELECT_BAL;
718 END;
719 Function EXCLAIMATION_POINT_p return varchar2 is
720 Begin
721 return EXCLAIMATION_POINT;
722 END;
723 Function STAR_p return varchar2 is
724 Begin
725 return STAR;
726 END;
727 Function WHERE_ACTUAL_TYPE_p return varchar2 is
728 Begin
729 return WHERE_ACTUAL_TYPE;
730 END;
731 Function WHERE_CURRENCY_CODE_p return varchar2 is
732 Begin
733 return WHERE_CURRENCY_CODE;
734 END;
735 Function SELECT_REFERENCE_p return varchar2 is
736 Begin
737 return SELECT_REFERENCE;
738 END;
739 Function WHERE_INDEX_p return varchar2 is
740 Begin
741 return WHERE_INDEX;
742 END;
743 Function SELECT_CR_p return varchar2 is
744 Begin
745 return SELECT_CR;
746 END;
747 Function SELECT_DR_p return varchar2 is
748 Begin
749 return SELECT_DR;
750 END;
751 Function ORDERBY_BAL2_p return varchar2 is
752 Begin
753 return ORDERBY_BAL2;
754 END;
755 Function ORDERBY_ACCT2_p return varchar2 is
756 Begin
757 return ORDERBY_ACCT2;
758 END;
759 Function WHERE_DAS_p return varchar2 is
760 Begin
761 return WHERE_DAS;
762 END;
763 Function ACCESS_SET_NAME_p return varchar2 is
764 Begin
765 return ACCESS_SET_NAME;
766 END;
767 Function RESULTING_CURRENCY_p return varchar2 is
768 Begin
769 return RESULTING_CURRENCY;
770 END;
771 begin
772 old_ccid:=null;
773
774 END GL_GLRGNL_XMLP_PKG ;
775
776