[Home] [Help]
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