eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GL_GLXAVTRB_XMLP_PKG

Source


1 PACKAGE BODY GL_GLXAVTRB_XMLP_PKG AS
2 /* $Header: GLXAVTRBB.pls 120.0 2007/12/27 14:44:24 vijranga noship $ */
3 function BeforeReport return boolean is
4 errbuf       VARCHAR2(133);
5 begin
6   begin
7     SELECT name, chart_of_accounts_id
8     INTO   ACCESS_SET_NAME, STRUCT_NUM
9     FROM   gl_access_sets
10     WHERE  access_set_id = P_ACCESS_SET_ID;
11   exception
12     WHEN NO_DATA_FOUND THEN
13       errbuf := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
14                                        'DASID', to_char(P_ACCESS_SET_ID));
15       /*srw.message('00', errbuf);*/null;
16       raise_application_error(-20101,null);/*srw.program_abort;*/null;
17     WHEN OTHERS THEN
18       errbuf := SQLERRM;
19       /*srw.message('00', errbuf);*/null;
20       raise_application_error(-20101,null);/*srw.program_abort;*/null;
21   end;
22   begin
23     SELECT
24       dpm.period_name,
25       prd.start_date,
26       prd.quarter_start_date,
27       prd.year_start_date
28     INTO
29       PERIOD_NAME,
30       PERIOD_START_DATE,
31       QUARTER_START_DATE,
32       YEAR_START_DATE
33     FROM
34       gl_access_sets       acc,
35       gl_date_period_map   dpm,
36       gl_periods           prd
37     WHERE
38         acc.access_set_id	= P_ACCESS_SET_ID
39     AND dpm.period_set_name	= acc.period_set_name
40     AND dpm.period_type		= acc.accounted_period_type
41     AND dpm.accounting_date	= P_REPORTING_DATE
42     AND prd.period_set_name	= dpm.period_set_name
43     AND prd.period_name		= dpm.period_name
44     AND prd.period_type		= dpm.period_type;
45   exception
46     WHEN NO_DATA_FOUND THEN
47       errbuf := gl_message.get_message(
48 		'GL_AVG_INVALID_DATE', 'Y',
49 		'DATE', to_char(P_Reporting_Date));
50       /*srw.message('00', errbuf);*/null;
51       raise_application_error(-20101,errbuf);/*srw.program_abort;*/null;
52     WHEN OTHERS THEN
53       errbuf := SQLERRM;
54       /*srw.message('00', errbuf);*/null;
55       raise_application_error(-20101,errbuf);/*srw.program_abort;*/null;
56   end;
57   IF (P_CURRENCY_TYPE = 'T') THEN
58     CURR_TYPE := 'dbs.currency_type in (''U'', ''T'', ''O'')';
59     RESULTING_CURRENCY := P_LEDGER_CURRENCY;
60   ELSIF (P_CURRENCY_TYPE = 'E') THEN
61     CURR_TYPE := 'dbs.currency_type = ''E''';
62     RESULTING_CURRENCY := P_ENTERED_CURRENCY;
63   ELSIF (P_CURRENCY_TYPE = 'S') THEN
64     CURR_TYPE := 'dbs.currency_type = ''U''';
65     RESULTING_CURRENCY := P_ENTERED_CURRENCY;
66   END IF;
67   if P_CURRENCY_TYPE is null then
68 	  P_CURRENCY_TYPE:='1=1';
69   end if;
70   begin
71     SELECT
72 	PRECISION,
73 	MINIMUM_ACCOUNTABLE_UNIT
74     INTO
75 	PRECISION,
76 	MAU
77     FROM
78 	FND_CURRENCIES
79     WHERE
80 	CURRENCY_CODE = RESULTING_CURRENCY;
81   exception
82     WHEN NO_DATA_FOUND THEN
83       errbuf := gl_message.get_message(
84                 'GL invalid currency code', 'Y');
85       /*srw.message('00', errbuf);*/null;
86       raise_application_error(-20101,null);/*srw.program_abort;*/null;
87     WHEN OTHERS THEN
88       errbuf := SQLERRM;
89       /*srw.message('00', errbuf);*/null;
90       raise_application_error(-20101,errbuf);/*srw.program_abort;*/null;
91   end;
92   /*SRW.USER_EXIT('FND SRWINIT');*/null;
93   /*SRW.REFERENCE(STRUCT_NUM);*/null;
94  null;
95   /*SRW.REFERENCE(STRUCT_NUM);*/null;
96  null;
97   /*SRW.REFERENCE(STRUCT_NUM);*/null;
98  null;
99   /*SRW.REFERENCE(STRUCT_NUM);*/null;
100  null;
101   WHERE_DAS := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
102                   P_ACCESS_SET_ID,
103                   'R',
104                   'LEDGER_COLUMN',
105                   'LEDGER_ID',
106                   'DBS',
107                   'SEG_COLUMN',
108                   null,
109                   'CC',
110                   null);
111   IF (WHERE_DAS is not null) THEN
112     WHERE_DAS := ' AND ' || WHERE_DAS;
113   else
114      WHERE_DAS:='and 1=1';
115   END IF;
116   PTD_POSITION := trunc(P_Reporting_Date) -
117 		   trunc(PERIOD_START_DATE) + 1;
118   IF (PTD_POSITION = 1) THEN
119     P_ENDING_BALANCE := 'dbs.Period_Aggregate1';
120   ELSE
121     P_ENDING_BALANCE := '(dbs.Period_Aggregate' || to_char(PTD_POSITION) || ' - ' ||
122 			 'dbs.Period_Aggregate' || to_char(PTD_POSITION - 1) || ')';
123   END IF;
124   IF (P_CURRENCY_TYPE = 'T') THEN
125     P_ENDING_BALANCE := 'decode(dbs.currency_type, ''U'', ' ||
126                          P_ENDING_BALANCE ||
127                          ', dbs.end_of_day' || to_char(PTD_POSITION) || ')';
128   END IF;
129   P_PTD_AGGREGATE := 'dbs.Period_Aggregate' || to_char(PTD_POSITION);
130   IF (MAU IS NULL) THEN
131     P_PATD := 'round((dbs.Period_Aggregate' || to_char(PTD_POSITION) ||
132 			' / ' || to_char(PTD_POSITION) || '), ' ||
133 		to_char(PRECISION) || ')';
134   ELSE
135     P_PATD := 'round((dbs.Period_Aggregate' || to_char(PTD_POSITION) ||
136 			' / (' || to_char(PTD_POSITION) ||
137 			' * ' || to_char(MAU) || ') ) )' ||
138 		' * ' || to_char(MAU);
139   END IF;
140   QTD_POSITION := trunc(P_Reporting_Date) -
141 		   trunc(QUARTER_START_DATE) + 1;
142   P_QTD_AGGREGATE := 'dbs.Opening_Quarter_Aggregate + dbs.Period_Aggregate' ||
143 		      to_char(PTD_POSITION);
144   IF (P_CURRENCY_TYPE = 'T') THEN
145     P_QTD_AGGREGATE := 'decode(dbs.currency_type, ''U'', ' ||
146 			P_QTD_AGGREGATE ||
147 			', dbs.Quarter_Aggregate' || to_char(PTD_POSITION) || ')';
148   END IF;
149   IF (MAU IS NULL) THEN
150     P_QATD := 'round(((dbs.Opening_Quarter_Aggregate + dbs.Period_Aggregate' ||
151 			to_char(PTD_POSITION) ||
152 			') / ' || to_char(QTD_POSITION) || '), ' ||
153 		to_char(PRECISION) || ')';
154     IF (P_CURRENCY_TYPE = 'T') THEN
155       P_QATD := 'decode(dbs.currency_type, ''U'', ' ||
156 		 P_QATD ||
157 		 ', round((dbs.Quarter_Aggregate' || to_char(PTD_POSITION) ||
158 			' / ' || to_char(QTD_POSITION) || '), ' ||
159 		 to_char(PRECISION) || ') )';
160     END IF;
161   ELSE
162     P_QATD := 'round(((dbs.Opening_Quarter_Aggregate + dbs.Period_Aggregate' ||
163 			to_char(PTD_POSITION) ||
164 			') / (' || to_char(QTD_POSITION) ||
165 			' * ' || to_char(MAU) || ') ) )' ||
166 		' * ' || to_char(MAU);
167     IF (P_CURRENCY_TYPE = 'T') THEN
168       P_QATD := 'decode(dbs.currency_type, ''U'', ' ||
169 		 P_QATD ||
170 		 ', round((dbs.Quarter_Aggregate' || to_char(PTD_POSITION) ||
171 			' / (' || to_char(QTD_POSITION) ||
172 			' * ' || to_char(MAU) || ') ) )' ||
173 		 ' * ' || to_char(MAU) || ')';
174     END IF;
175   END IF;
176   YTD_POSITION := trunc(P_Reporting_Date) -
177 		   trunc(YEAR_START_DATE) + 1;
178   P_YTD_AGGREGATE := 'dbs.Opening_Year_Aggregate + dbs.Period_Aggregate' ||
179 		      to_char(PTD_POSITION);
180   IF (P_CURRENCY_TYPE = 'T') THEN
181     P_YTD_AGGREGATE := 'decode(dbs.currency_type, ''U'', ' ||
182 			P_YTD_AGGREGATE ||
183 			', dbs.Year_Aggregate' || to_char(PTD_POSITION) || ')';
184   END IF;
185   IF (MAU IS NULL) THEN
186     P_YATD := 'round(((dbs.Opening_Year_Aggregate + dbs.Period_Aggregate' ||
187 			to_char(PTD_POSITION) ||
188 			') / ' || to_char(YTD_POSITION) || '), ' ||
189 		to_char(PRECISION) || ')';
190     IF (P_CURRENCY_TYPE = 'T') THEN
191       P_YATD := 'decode(dbs.currency_type, ''U'', ' ||
192 		 P_YATD ||
193 		 ', round((dbs.Year_Aggregate' || to_char(PTD_POSITION) ||
194 			   ' / ' || to_char(YTD_POSITION) || '), ' ||
195 		 to_char(PRECISION) || ') )';
196     END IF;
197   ELSE
198     P_YATD := 'round(((dbs.Opening_Year_Aggregate + dbs.Period_Aggregate' ||
199 			to_char(PTD_POSITION) ||
200 			') / (' || to_char(YTD_POSITION) ||
201 			' * ' || to_char(MAU) || ') ) )' ||
202 		' * ' || to_char(MAU);
203     IF (P_CURRENCY_TYPE = 'T') THEN
204       P_YATD := 'decode(dbs.currency_type, ''U'', ' ||
205 		 P_YATD ||
206 		 ', round((dbs.Year_Aggregate' || to_char(PTD_POSITION) ||
207 			' / (' || to_char(YTD_POSITION) ||
208 			' * ' || to_char(MAU) || ') ) )' ||
209 		 ' * ' || to_char(MAU) || ')';
210     END IF;
211   END IF;
212   return (TRUE);
213 end;
214 FUNCTION AfterReportTrigger return boolean is
215 begin
216 	/*SRW.USER_EXIT('FND SRWEXIT');*/null;
217 	return(TRUE);
218 end;
219 function patd(PTD_Aggregate_Total in number) return number is
220   Average NUMBER;
221 begin
222   IF (MAU IS NULL) THEN
223 	Average	:= round(PTD_Aggregate_Total /
224 		   PTD_POSITION, PRECISION);
225   ELSE
226 	Average	:= round(PTD_Aggregate_Total /
227 		   (PTD_POSITION * MAU))
228 		   * MAU ;
229   END IF;
230   RETURN(Average);
231 end;
232 function qatd(QTD_Aggregate_Total in number) return number is
233   Average NUMBER;
234 begin
235   IF (MAU IS NULL) THEN
236 	Average	:= round(QTD_Aggregate_Total /
237 		   QTD_POSITION, PRECISION);
238   ELSE
239 	Average	:= round(QTD_Aggregate_Total /
240 		   (QTD_POSITION * MAU))
241 		   * MAU ;
242   END IF;
243   RETURN(Average);
244 end;
245 function yatd(YTD_Aggregate_Total in number) return number is
246   Average NUMBER;
247 begin
248   IF (MAU IS NULL) THEN
249 	Average	:= round(YTD_Aggregate_Total /
250 		   YTD_POSITION, PRECISION);
251   ELSE
252 	Average	:= round(YTD_Aggregate_Total /
253 		   (YTD_POSITION * MAU))
254 		   * MAU ;
255   END IF;
256   RETURN(Average);
257 end;
258 function end_bal(END_BAL_Total in number) return number is
259   Average NUMBER;
260 begin
261   IF (MAU IS NULL) THEN
262 	Average	:= round(END_BAL_Total, PRECISION);
263   ELSE
264 	Average	:= round(END_BAL_Total / MAU)
265 		   * MAU ;
266   END IF;
267   RETURN(Average);
268 end;
269 function g_headergroupfilter(BAL_SECURE in varchar2) return boolean is
270 begin
271        /*SRW.REFERENCE(STRUCT_NUM);*/null;
272        /*SRW.REFERENCE(C_FLEXDATA_MASTER);*/null;
273   if (BAL_SECURE = 'S') then
274      return (FALSE);
275   else
276      return (TRUE);
277   end if;
278   RETURN NULL;
279 end;
280 function g_detailgroupfilter(FLEX_SECURE in varchar2) return boolean is
281 begin
282         /*SRW.REFERENCE(STRUCT_NUM);*/null;
283 	/*SRW.REFERENCE(C_FLEXDATA_MASTER);*/null;
284   if (FLEX_SECURE ='S') then
285      return (FALSE);
286   else
287      return (TRUE);
288   end if;
289   RETURN NULL;
290 end;
291 --Functions to refer Oracle report placeholders--
292  Function WHERE_p return varchar2 is
293 	Begin
294 	 return WHERE_lexical;
295 	 END;
296  Function CURR_TYPE_p return varchar2 is
297 	Begin
298 	 return CURR_TYPE;
299 	 END;
300  Function WHERE_DAS_p return varchar2 is
301 	Begin
302 	 return WHERE_DAS;
303 	 END;
304  Function ACCESS_SET_NAME_p return varchar2 is
305 	Begin
306 	 return ACCESS_SET_NAME;
307 	 END;
308  Function STRUCT_NUM_p return varchar2 is
309 	Begin
310 	 return STRUCT_NUM;
311 	 END;
312  Function RESULTING_CURRENCY_p return varchar2 is
313 	Begin
314 	 return RESULTING_CURRENCY;
315 	 END;
316  Function SELECT_BAL_p return varchar2 is
317 	Begin
318 	 return SELECT_BAL;
319 	 END;
320  Function SELECT_FLEXDATA_p return varchar2 is
321 	Begin
322 	 return SELECT_FLEXDATA;
323 	 END;
324  Function ORDERBY_p return varchar2 is
325 	Begin
326 	 return ORDERBY;
327 	 END;
328  Function PERIOD_NAME_p return varchar2 is
329 	Begin
330 	 return PERIOD_NAME;
331 	 END;
332  Function PERIOD_START_DATE_p return date is
333 	Begin
334 	 return PERIOD_START_DATE;
335 	 END;
336  Function QUARTER_START_DATE_p return date is
337 	Begin
338 	 return QUARTER_START_DATE;
339 	 END;
340  Function YEAR_START_DATE_p return date is
341 	Begin
342 	 return YEAR_START_DATE;
343 	 END;
344  Function PTD_POSITION_p return number is
345 	Begin
346 	 return PTD_POSITION;
347 	 END;
348  Function QTD_POSITION_p return number is
349 	Begin
350 	 return QTD_POSITION;
351 	 END;
352  Function YTD_POSITION_p return number is
353 	Begin
354 	 return YTD_POSITION;
355 	 END;
356  Function MAU_p return number is
357 	Begin
358 	 return MAU;
359 	 END;
360  Function PRECISION_p return number is
361 	Begin
362 	 return PRECISION;
363 	 END;
364  Function P_ENDING_BALANCE_p return varchar2 is
365 	Begin
366 	 return P_ENDING_BALANCE;
367 	 END;
368  Function P_PATD_p return varchar2 is
369 	Begin
370 	 return P_PATD;
371 	 END;
372  Function P_QATD_p return varchar2 is
373 	Begin
374 	 return P_QATD;
375 	 END;
376  Function P_YATD_p return varchar2 is
377 	Begin
378 	 return P_YATD;
379 	 END;
380  Function P_PTD_AGGREGATE_p return varchar2 is
381 	Begin
382 	 return P_PTD_AGGREGATE;
383 	 END;
384  Function P_QTD_AGGREGATE_p return varchar2 is
385 	Begin
386 	 return P_QTD_AGGREGATE;
387 	 END;
388  Function P_YTD_AGGREGATE_p return varchar2 is
389 	Begin
390 	 return P_YTD_AGGREGATE;
391 	 END;
392 END GL_GLXAVTRB_XMLP_PKG ;
393 
394