[Home] [Help]
PACKAGE BODY: APPS.GL_GLXAVADT_XMLP_PKG
Source
1 PACKAGE BODY GL_GLXAVADT_XMLP_PKG AS
2 /* $Header: GLXAVADTB.pls 120.3 2008/01/07 20:07:22 vijranga noship $ */
3
4 function BeforeReport return boolean is
5 errbuf VARCHAR2(132);
6 errbuf2 VARCHAR2(132);
7
8 v_period_year NUMBER;
9 v_quarter_num NUMBER;
10 v_period_num NUMBER;
11 v_start_period_name VARCHAR2(15);
12 v_start_date DATE;
13 begin
14
15 /*srw.user_exit('FND SRWINIT');*/null;
16
17
18
19 begin
20 SELECT name, chart_of_accounts_id, period_set_name, accounted_period_type
21 -- INTO ACCESS_SET_NAME, STRUCT_NUM, PERIOD_SET_NAME, PERIOD_TYPE
22 INTO ACCESS_SET_NAME, STRUCT_NUM, PERIOD_SET_NAME_1, PERIOD_TYPE_1
23 FROM gl_access_sets
24 WHERE access_set_id = P_ACCESS_SET_ID;
25
26 exception
27 WHEN NO_DATA_FOUND THEN
28 errbuf := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
29 'DASID', to_char(P_ACCESS_SET_ID));
30 /*srw.message('00', errbuf);*/null;
31
32 raise_application_error(-20101,null);/*srw.program_abort;*/null;
33
34
35 WHEN OTHERS THEN
36 errbuf := SQLERRM;
37 /*srw.message('00', errbuf);*/null;
38
39 raise_application_error(-20101,null);/*srw.program_abort;*/null;
40
41 end;
42
43
44 /* gl_get_period_info(P_LEDGER_ID,
45 P_REPORTING_DATE,
46 PERIOD_SET_NAME,
47 v_period_year,
48 v_quarter_num,
49 v_period_num,
50 errbuf);*/
51
52 gl_get_period_info(P_LEDGER_ID,
53 P_REPORTING_DATE,
54 PERIOD_SET_NAME_1,
55 v_period_year,
56 v_quarter_num,
57 v_period_num,
58 errbuf);
59 if (errbuf is not null) then
60
61 errbuf2 := gl_message.get_message(
62 'GL_PLL_ROUTINE_ERROR', 'N',
63 'ROUTINE','gl_get_period_info'
64 );
65 /*srw.message('00', errbuf2);*/null;
66
67 /*srw.message('00', errbuf);*/null;
68
69 raise_application_error(-20101,null);/*srw.program_abort;*/null;
70
71 end if;
72
73 -- PERIOD_YEAR := v_period_year;
74 PERIOD_YEAR_1:= v_period_year;
75 QUARTER_NUM := v_quarter_num;
76 PERIOD_NUM := v_period_num;
77
78 /*gl_get_first_date(P_LEDGER_ID,
79 P_BALANCE_TYPE,
80 PERIOD_YEAR,
81 QUARTER_NUM,
82 PERIOD_NUM,
83 v_start_period_name,
84 v_start_date,
85 errbuf);*/
86 gl_get_first_date(P_LEDGER_ID,
87 P_BALANCE_TYPE,
88 PERIOD_YEAR_1,
89 QUARTER_NUM,
90 PERIOD_NUM,
91 v_start_period_name,
92 v_start_date,
93 errbuf);
94
95 if (errbuf is not null) then
96
97 errbuf2 := gl_message.get_message(
98 'GL_PLL_ROUTINE_ERROR', 'N',
99 'ROUTINE','gl_get_first_date'
100 );
101 /*srw.message('00', errbuf2);*/null;
102
103 /*srw.message('00', errbuf);*/null;
104
105 raise_application_error(-20101,null);/*srw.program_abort;*/null;
106
107 end if;
108
109 START_DATE := v_start_date;
110 START_PERIOD_NAME := v_start_period_name;
111
112 /*srw.reference(STRUCT_NUM);*/null;
113
114
115 null;
116
117 /*srw.reference(STRUCT_NUM);*/null;
118
119
120 null;
121
122 /*srw.reference(STRUCT_NUM);*/null;
123
124
125 null;
126
127 /*srw.reference(STRUCT_NUM);*/null;
128
129
130 null;
131
132
133
134 /*srw.reference(STRUCT_NUM);*/null;
135
136
137 null;
138
139
140
141 /*srw.reference(STRUCT_NUM);*/null;
142
143
144 null;
145
146 WHERE_DAS := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
147 P_ACCESS_SET_ID,
148 'R',
149 'LEDGER_ID',
150 P_LEDGER_ID,
151 null,
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 else
160 WHERE_DAS:=' ';
161 END IF;
162
163
164 begin
165 SELECT name, currency_code
166 INTO LEDGER_NAME, LEDGER_CURRENCY
167 FROM gl_ledgers
168 WHERE ledger_id = P_LEDGER_ID;
169
170 exception
171 WHEN OTHERS THEN
172 errbuf := SQLERRM;
173 /*srw.message('00', errbuf);*/null;
174
175 raise_application_error(-20101,null);/*srw.program_abort;*/null;
176
177 end;
178
179 if (P_CURRENCY_TYPE = 'T') then
180 REPORTING_CURR := LEDGER_CURRENCY;
181 else
182 REPORTING_CURR := P_ENTERED_CURRENCY;
183 end if;
184
185 return (TRUE);
186 end;
187
188 function opening_balformula(CCID in number) return number is
189 min_startdate DATE;
190 eod_bal NUMBER;
191 open_bal NUMBER;
192 begin
193 IF (gl_code_combinations_pkg.check_net_income_account(CCID)) THEN
194
195
196 IF (P_balance_type = 'YATD') THEN
197 return(0);
198
199 ELSE
200 SELECT min(start_date)
201 INTO min_startdate
202 FROM GL_PERIOD_STATUSES
203 WHERE application_id = 101
204 AND ledger_id = P_LEDGER_ID
205 -- AND period_year = PERIOD_YEAR
206 AND period_year = PERIOD_YEAR_1
207 AND closing_status || '' in ('P', 'C', 'O')
208 AND adjustment_period_flag = 'N';
209
210
211
212 IF (min_startdate = START_DATE) THEN
213 return(0);
214 ELSE
215 SELECT nvl(end_of_date_balance_num, 0)
216 INTO eod_bal
217 FROM GL_DAILY_BALANCES_V DBAL
218 -- WHERE dbal.period_set_name = PERIOD_SET_NAME
219 WHERE dbal.period_set_name = PERIOD_SET_NAME_1
220 -- AND dbal.period_type = PERIOD_TYPE
221 AND dbal.period_type = PERIOD_TYPE_1
222 AND dbal.ledger_id = P_LEDGER_ID
223 AND dbal.currency_code = REPORTING_CURR
224 AND dbal.currency_type = decode(P_CURRENCY_TYPE, 'E', 'E', 'U')
225 AND dbal.code_combination_id = CCID
226 AND dbal.accounting_date =
227 (SELECT ps.end_date
228 FROM gl_period_statuses ps,
229 gl_date_period_map dpm
230 WHERE dpm.accounting_date = (START_DATE -1)
231 -- AND dpm.period_set_name = PERIOD_SET_NAME
232 AND dpm.period_set_name = PERIOD_SET_NAME_1
233 -- AND dpm.period_type = PERIOD_TYPE
234 AND dpm.period_type = PERIOD_TYPE_1
235 AND ps.period_name = dpm.period_name
236 AND ps.application_id = 101
237 AND ps.ledger_id = P_LEDGER_ID);
238 return(eod_bal);
239 END IF;
240 END IF ;
241
242 ELSE
243
244
245 SELECT nvl(begin_balance_dr, 0) - nvl(begin_balance_cr, 0)
246 INTO open_bal
247 FROM GL_BALANCES
248 WHERE code_combination_id = CCID
249 AND period_name = START_PERIOD_NAME
250 AND ledger_id = P_LEDGER_ID
251 AND currency_code = REPORTING_CURR
252 AND actual_flag = 'A'
253 AND nvl(translated_flag, 'R') = 'R';
254
255 return(open_bal);
256 END IF;
257
258 EXCEPTION
259 when NO_DATA_FOUND then
260 return(0);
261
262 end;
263
264 function last_ccidformula(last_ccid in number, ccid in number, opening_bal in number) return number is
265 begin
266 IF ((last_ccid IS NULL) OR (last_ccid <> ccid)) THEN
267 last_eod := opening_bal;
268 return(ccid);
269 ELSE
270 return(ccid);
271 end if;
272
273 RETURN NULL;
274 end;
275
276 function daily_activityformula(end_of_date_balance in number) return number is
277 da NUMBER;
278 begin
279 /*srw.reference(last_ccid);*/null;
280
281 da := end_of_date_balance - last_eod;
282 last_eod := end_of_date_balance;
283 return(da);
284 end;
285
286 function AfterReport return boolean is
287 begin
288 /*srw.user_exit('FND SRWEXIT');*/null;
289
290 return (TRUE);
291 end;
292
293 procedure gl_get_period_info (ldgrid in number,
294 reporting_date in date,
295 calendar_name in varchar2,
296 v_period_year out NOCOPY number,
297 v_quarter_num out NOCOPY number,
298 v_period_num out NOCOPY number,
299 errbuf out NOCOPY varchar2 )
300 is
301
302 BEGIN
303
304 select ps.period_year, ps.quarter_num, ps.period_num
305 into v_period_year, v_quarter_num, v_period_num
306 from gl_period_statuses ps, gl_date_period_map dpm
307 where dpm.accounting_date = reporting_date
308 and dpm.period_set_name = calendar_name
309 -- and dpm.period_type = PERIOD_TYPE
310 and dpm.period_type = PERIOD_TYPE_1
311 and dpm.period_name = ps.period_name
312 and ps.application_id = 101
313 and ps.ledger_id = ldgrid;
314
315 EXCEPTION
316
317 WHEN NO_DATA_FOUND THEN
318
319 errbuf := gl_message.get_message('GL_PLL_INVALID_DATE', 'Y');
320
321 WHEN OTHERS THEN
322
323 errbuf := SQLERRM;
324
325 END;
326
327 procedure gl_get_first_date(ldgrid in number,
328 balance_type in varchar2,
329 v_period_year in number,
330 v_quarter_num in number,
331 v_period_num in number,
332 v_period_name out NOCOPY varchar2,
333 v_start_date out NOCOPY date,
334 errbuf out NOCOPY varchar2)
335 is
336
337 BEGIN
338 select ps.period_name, ps.start_date
339 into v_period_name, v_start_date
340 from gl_period_statuses ps
341 where ps.application_id = 101
342 and ps.ledger_id = ldgrid
343 and ps.adjustment_period_flag = 'N'
344 and ps.start_date =
345 (select min(ps1.start_date)
346 from gl_period_statuses ps1
347 where ps1.application_id = 101
348 and ps1.ledger_id = ldgrid
349 and ps1.period_year = v_period_year
350 and ps1.quarter_num = decode(balance_type,
351 'QATD', v_quarter_num,
352 ps1.quarter_num)
353 and ps1.period_num = decode(balance_type,
354 'PATD', v_period_num,
355 ps1.period_num)
356 and ps1.closing_status in ('P', 'C', 'O')
357 and ps1.adjustment_period_flag = 'N');
358
359 EXCEPTION
360
361 WHEN NO_DATA_FOUND THEN
362
363 null;
364
365 WHEN OTHERS THEN
366
367 errbuf := SQLERRM;
368
369 END;
370
371 function g_balancing_seggroupfilter(BAL_SECURE in varchar2) return boolean is
372 begin
373 /*srw.reference(STRUCT_NUM);*/null;
374
375 /*srw.reference(BAL_DATA);*/null;
376
377
378
379 if(BAL_SECURE ='S') then
380 return (FALSE);
381 else
382 return (TRUE);
383 end if;
384
385 RETURN NULL;
386 end;
387
388 function g_opening_balgroupfilter(ACCT_SECURE in varchar2) return boolean is
389 begin
390 /*srw.reference(FLEXDATA);*/null;
391
392
393
394 if(ACCT_SECURE ='S') then
395 return (FALSE);
396 else
397 return (TRUE);
398 end if;
399
400 RETURN NULL;
401 end;
402
403 --Functions to refer Oracle report placeholders--
404
405 Function last_eod_p return number is
406 Begin
407 return last_eod;
408 END;
409 Function STRUCT_NUM_p return number is
410 Begin
411 return STRUCT_NUM;
412 END;
413 Function LEDGER_NAME_p return varchar2 is
414 Begin
415 return LEDGER_NAME;
416 END;
417 Function PERIOD_SET_NAME_p return varchar2 is
418 Begin
419 -- return PERIOD_SET_NAME;
420 return PERIOD_SET_NAME_1;
421 END;
422 Function PERIOD_YEAR_p return number is
423 Begin
424 --return PERIOD_YEAR;
425 return PERIOD_YEAR_1;
426 END;
427 Function QUARTER_NUM_p return number is
428 Begin
429 return QUARTER_NUM;
430 END;
431 Function PERIOD_NUM_p return number is
432 Begin
433 return PERIOD_NUM;
434 END;
435 Function REPORTING_CURR_p return varchar2 is
436 Begin
437 return REPORTING_CURR;
438 END;
439 Function START_DATE_p return date is
440 Begin
441 return START_DATE;
442 END;
443 Function START_PERIOD_NAME_p return varchar2 is
444 Begin
445 return START_PERIOD_NAME;
446 END;
447 Function SELECT_BAL_p return varchar2 is
448 Begin
449 return SELECT_BAL;
450 END;
451 Function WHERE_FLEX_RANGE_p return varchar2 is
452 Begin
453 return WHERE_FLEX_RANGE;
454 END;
455 Function SELECT_ALL_p return varchar2 is
456 Begin
457 return SELECT_ALL;
458 END;
459 Function ORDERBY_BAL_p return varchar2 is
460 Begin
461 return ORDERBY_BAL;
462 END;
463 Function ORDERBY_ALL_p return varchar2 is
464 Begin
465 return ORDERBY_ALL;
466 END;
467 Function ORDERBY_ACCT_p return varchar2 is
468 Begin
469 return ORDERBY_ACCT;
470 END;
471 Function PERIOD_TYPE_p return varchar2 is
472 Begin
473 -- return PERIOD_TYPE;
474 return PERIOD_TYPE_1;
475 END;
476 Function ACCESS_SET_NAME_p return varchar2 is
477 Begin
478 return ACCESS_SET_NAME;
479 END;
480 Function WHERE_DAS_p return varchar2 is
481 Begin
482 return WHERE_DAS;
483 END;
484 Function LEDGER_CURRENCY_p return varchar2 is
485 Begin
486 return LEDGER_CURRENCY;
487 END;
488 END GL_GLXAVADT_XMLP_PKG ;