[Home] [Help]
PACKAGE BODY: APPS.GL_GLRJED_XMLP_PKG
Source
1 PACKAGE BODY GL_GLRJED_XMLP_PKG AS
2 /* $Header: GLRJEDB.pls 120.0 2007/12/27 14:38:48 vijranga noship $ */
3
4 function AfterReport return boolean is
5 begin
6 /*srw.user_exit('FND SRWEXIT');*/null;
7
8 return (TRUE);
9 end;
10
11 function begin_balformula(BEGIN_DR in number, BEGIN_CR in number) return number is
12 begin
13
14 return (abs(BEGIN_DR - BEGIN_CR));
15 end;
16
17 function end_balformula(END_DR in number, END_CR in number) return number is
18 begin
19 return (abs(END_DR - END_CR));
20 end;
21
22 function BUD_ENC_TYPE_NAMEFormula return VARCHAR2 is
23 name VARCHAR2(30);
24 errbuf VARCHAR2(132);
25 errbuf2 VARCHAR2(132);
26 begin
27
28 gl_info.gl_get_bud_or_enc_name(P_ACTUAL_FLAG,
29 P_BUD_ENC_TYPE_ID,
30 name, errbuf);
31
32 if (errbuf is not null) then
33
34 errbuf2 := gl_message.get_message(
35 'GL_PLL_ROUTINE_ERROR', 'N',
36 'ROUTINE','gl_get_bud_enc_name'
37 );
38 /*srw.message('00', errbuf2);*/null;
39
40
41 /*srw.message('00', errbuf);*/null;
42
43
44 raise_application_error(-20101,null);/*srw.program_abort;*/null;
45
46 end if;
47
48 return(name);
49 end;
50
51 function DISP_ACTUAL_FLAGFormula return VARCHAR2 is
52 name VARCHAR2(240);
53 errbuf VARCHAR2(132);
54 errbuf2 VARCHAR2(132);
55 begin
56
57 gl_info.gl_get_lookup_value(
58 'D', P_ACTUAL_FLAG, 'BATCH_TYPE',
59 name, errbuf);
60
61 if (errbuf is not null) then
62
63
64 errbuf2 := gl_message.get_message(
65 'GL_PLL_ROUTINE_ERROR', 'N',
66 'ROUTINE','gl_get_lookup_value'
67 );
68 /*srw.message('00', errbuf2);*/null;
69
70
71 /*srw.message('00', errbuf);*/null;
72
73
74 raise_application_error(-20101,null);/*srw.program_abort;*/null;
75
76 end if;
77
78 return(name);
79 end;
80
81 function START_EFFECTIVE_PERIOD_NUMForm return Number is
82 effective_period_num NUMBER;
83 errbuf VARCHAR2(132);
84 errbuf2 VARCHAR2(132);
85 begin
86
87 gl_get_effective_num(P_LEDGER_ID,
88 P_START_PERIOD,
89 effective_period_num,
90 errbuf);
91
92 if (errbuf is not null) then
93 errbuf2 := gl_message.get_message(
94 'GL_PLL_ROUTINE_ERROR', 'N',
95 'ROUTINE','gl_get_period_dates'
96 );
97 /*srw.message('00', errbuf2);*/null;
98
99
100 /*srw.message('00', errbuf);*/null;
101
102
103 raise_application_error(-20101,null);/*srw.program_abort;*/null;
104
105 end if;
106
107 return(effective_period_num);
108 end;
109
110 function END_EFFECTIVE_PERIOD_NUMFormul return Number is
111 effective_period_num NUMBER;
112 errbuf VARCHAR2(132);
113 errbuf2 VARCHAR2(132);
114 begin
115
116 gl_get_effective_num(P_LEDGER_ID,
117 P_END_PERIOD,
118 effective_period_num,
119 errbuf);
120
121 if (errbuf is not null) then
122 errbuf2 := gl_message.get_message(
123 'GL_PLL_ROUTINE_ERROR', 'N',
124 'ROUTINE','gl_get_period_dates'
125 );
126 /*srw.message('00', errbuf2);*/null;
127
128
129 /*srw.message('00', errbuf);*/null;
130
131
132 raise_application_error(-20101,null);/*srw.program_abort;*/null;
133
134 end if;
135
136 return(effective_period_num);
137 end;
138
139 function BeforeReport return boolean is
140 security_mode VARCHAR2(1);
141 errbuf VARCHAR2(132);
142 begin
143
144 /*srw.user_exit('FND SRWINIT');*/null;
145
146
147
148 begin
149 SELECT name, chart_of_accounts_id
150 INTO ACCESS_SET_NAME, STRUCT_NUM
151 FROM gl_access_sets
152 WHERE access_set_id = P_ACCESS_SET_ID;
153
154 exception
155 WHEN NO_DATA_FOUND THEN
156 errbuf := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
157 'DASID', to_char(P_ACCESS_SET_ID));
158 /*srw.message('00', errbuf);*/null;
159
160 raise_application_error(-20101,null);/*srw.program_abort;*/null;
161
162
163 WHEN OTHERS THEN
164 errbuf := SQLERRM;
165 /*srw.message('00', errbuf);*/null;
166
167 raise_application_error(-20101,null);/*srw.program_abort;*/null;
168
169 end;
170
171 /*srw.reference(STRUCT_NUM);*/null;
172
173
174 null;
175
176 /*srw.reference(STRUCT_NUM);*/null;
177
178
179 null;
180
181 /*srw.reference(STRUCT_NUM);*/null;
182
183
184 null;
185
186 /*srw.reference(STRUCT_NUM);*/null;
187
188
189 null;
190
191 /*srw.reference(STRUCT_NUM);*/null;
192
193
194 null;
195
196 /*srw.reference(STRUCT_NUM);*/null;
197
198
199 null;
200
201 /*srw.reference(STRUCT_NUM);*/null;
202
203
204 null;
205
206
207
208 WHERE_DAS_BAL := gl_access_set_security_pkg.get_security_clause(
209 P_ACCESS_SET_ID,
210 'R',
211 'LEDGER_COLUMN',
212 'LEDGER_ID',
213 'bal',
214 'SEG_COLUMN',
215 null,
216 'cc',
217 null);
218 IF (WHERE_DAS_BAL IS NOT NULL) THEN
219 WHERE_DAS_BAL := ' AND ' || WHERE_DAS_BAL;
220 END IF;
221
222 WHERE_DAS_JE := gl_access_set_security_pkg.get_security_clause(
223 P_ACCESS_SET_ID,
224 'R',
225 'LEDGER_COLUMN',
226 'LEDGER_ID',
227 'jeh',
228 'SEG_COLUMN',
229 null,
230 'cc',
231 null);
232 IF (WHERE_DAS_JE IS NOT NULL) THEN
233 WHERE_DAS_JE := ' AND ' || WHERE_DAS_JE;
234 END IF;
235
236
237
238 if (P_KIND = 'L') then
239 SELECT_REFERENCE := 'jel.reference_1';
240 elsif (P_KIND = 'H') then
241 SELECT_REFERENCE := 'jeh.external_reference';
242 elsif (P_KIND = 'S') then
243 SELECT_REFERENCE := 'jel.reference_4';
244 else
245 SELECT_REFERENCE := 'jel.reference_1';
246 end if;
247
248
249
250 IF (P_CURRENCY_TYPE = 'T') THEN
251 RESULTING_CURRENCY := P_LEDGER_CURRENCY;
252 ELSE
253 RESULTING_CURRENCY := P_ENTERED_CURRENCY;
254 END IF;
255
256
257
258
259 WHERE_CURRENCY_BAL := 'bal.translated_flag is null';
260 SELECT_BEGIN_DR := 'sum(nvl(bal.begin_balance_dr,0))';
261 SELECT_BEGIN_CR := 'sum(nvl(bal.begin_balance_cr,0))';
262 SELECT_END_DR := 'sum(nvl(bal.begin_balance_dr,0) + nvl(bal.period_net_dr,0))';
263 SELECT_END_CR := 'sum(nvl(bal.begin_balance_cr,0) + nvl(bal.period_net_cr,0))';
264
265 IF (P_CURRENCY_TYPE = 'E') THEN
266
267 IF (P_ACTUAL_FLAG = 'A') THEN
268 SELECT_BEGIN_DR := 'sum(nvl(decode(bal.translated_flag, ''R'', bal.begin_balance_dr, bal.begin_balance_dr_beq),0))';
269 SELECT_BEGIN_CR := 'sum(nvl(decode(bal.translated_flag, ''R'', bal.begin_balance_cr, bal.begin_balance_cr_beq),0))';
270 SELECT_END_DR := 'sum(nvl(decode(bal.translated_flag, ''R'', bal.begin_balance_dr, bal.begin_balance_dr_beq),0)' ||
271 ' + nvl(decode(bal.translated_flag, ''R'', bal.period_net_dr, bal.period_net_dr_beq),0))';
272 SELECT_END_CR := 'sum(nvl(decode(bal.translated_flag, ''R'', bal.begin_balance_cr, bal.begin_balance_cr_beq),0)' ||
273 ' + nvl(decode(bal.translated_flag, ''R'', bal.period_net_cr, bal.period_net_cr_beq),0))';
274 END IF;
275
276
277 WHERE_CURRENCY_BAL := '(bal.translated_flag = ''R'' or bal.translated_flag is null)';
278 END IF;
279
280
281
282 if (P_ACTUAL_FLAG = 'A') then
283 WHERE_ACTUAL_TYPE := '1 = 1';
284 elsif (P_ACTUAL_FLAG = 'B') then
285 WHERE_ACTUAL_TYPE := 'budget_version_id = ' || to_char(P_BUD_ENC_TYPE_ID);
286 else
287 WHERE_ACTUAL_TYPE := 'encumbrance_type_id = ' || to_char(P_BUD_ENC_TYPE_ID);
288 end if;
289
290
291
292 IF (P_CURRENCY_TYPE = 'S') THEN
293 WHERE_CURRENCY_CODE :=
294 '( jeh.currency_code = ''STAT''' ||
295 ' OR jel.stat_amount is NOT NULL)';
296 SELECT_DR :=
297 'decode(jeh.currency_code, ''STAT'',' ||
298 'decode(nvl(jel.stat_amount,0),' ||
299 '0, jel.accounted_dr,' ||
300 'decode(sign(nvl(jel.stat_amount,0)),' ||
301 '-1, jel.accounted_dr,' ||
302 ' 1, (nvl(jel.accounted_dr,0) + ' ||
303 'jel.stat_amount),' ||
304 'jel.accounted_dr)),' ||
305 'decode(sign(nvl(jel.stat_amount,0)),' ||
306 '1, nvl(jel.stat_amount,0), ' ||
307 'NULL))';
308
309 SELECT_CR :=
310 'decode(jeh.currency_code, ''STAT'',' ||
311 'decode(nvl(jel.stat_amount,0),' ||
312 '0, jel.accounted_cr,' ||
313 'decode(sign(nvl(jel.stat_amount,0)),' ||
314 '-1, (nvl(jel.accounted_cr,0) - ' ||
315 'jel.stat_amount),' ||
316 ' 1, jel.accounted_cr,' ||
317 'jel.accounted_cr)),' ||
318 'decode(sign(nvl(jel.stat_amount,0)),' ||
319 '-1, (0 - nvl(jel.stat_amount,0)), ' ||
320 'NULL))';
321
322 ELSIF (P_CURRENCY_TYPE = 'T') THEN
323 WHERE_CURRENCY_CODE := 'jeh.currency_code <> ''STAT''';
324 SELECT_DR := 'jel.accounted_dr';
325 SELECT_CR := 'jel.accounted_cr';
326
327 ELSE
328 WHERE_CURRENCY_CODE := 'jeh.currency_code = ''' || P_ENTERED_CURRENCY || '''';
329 SELECT_DR := 'jel.entered_dr';
330 SELECT_CR := 'jel.entered_cr';
331 END IF;
332
333
334
335 if (P_ORDER_TYPE = 'A') then
336 ORDER_BY := ORDERBY_ACCT || ', ' ||
337 ORDERBY_ACCT2 || ', ' ||
338 ORDERBY_BAL || ', ' ||
339 ORDERBY_BAL2 || ', ' ||
340 ORDERBY_ALL || ', ' ||
341 'src.user_je_source_name, ' ||
342 'cat.user_je_category_name, ' ||
343 'jeb.name, jeh.name, jel.je_line_num';
344 elsif (P_ORDER_TYPE = 'B') then
345 ORDER_BY := ORDERBY_BAL || ', ' ||
346 ORDERBY_BAL2 || ', ' ||
347 ORDERBY_ACCT || ', ' ||
348 ORDERBY_ACCT2 || ', ' ||
349 ORDERBY_ALL || ', ' ||
350 'src.user_je_source_name, ' ||
351 'cat.user_je_category_name, ' ||
352 'jeb.name, jeh.name, jel.je_line_num';
353 else
354 ORDER_BY := 'src.user_je_source_name, ' ||
355 'cat.user_je_category_name, ' ||
356 'jeb.name, jeh.name, ' ||
357 ORDERBY_BAL || ', ' ||
358 ORDERBY_BAL2 || ', ' ||
359 ORDERBY_ACCT || ', ' ||
360 ORDERBY_ACCT2 || ', ' ||
361 ORDERBY_ALL || ', ' ||
362 'jel.je_line_num';
363 end if;
364
365
366
367 begin
368 fnd_profile.get('GL_STD_ANALYSIS_REPORT_BALANCE_SECURITY', security_mode);
369 exception
370 WHEN OTHERS THEN
371 security_mode := 'N';
372 end;
373
374 if( nvl(security_mode,'N') = 'Y') then
375
376 gl_security_pkg.init_segval;
377
378 SECURITY_FILTER_STR :=
379 'AND gl_security_pkg.validate_access(' || P_LEDGER_ID ||
380 ', cc.code_combination_id) = ''TRUE'' ';
381 else
382 SECURITY_FILTER_STR := ' ';
383 end if;
384
385
386
387 PARAM_ACCT_FROM := P_MIN_FLEX;
388 PARAM_ACCT_TO := P_MAX_FLEX;
389 PARAM_PERIOD_FROM := P_START_PERIOD;
390 PARAM_PERIOD_TO := P_END_PERIOD;
391 PARAM_CURRENCY_TYPE := P_CURRENCY_TYPE;
392
393 gl_info.gl_get_lookup_value('M', P_KIND, 'ACCOUNT_RPT_KIND',
394 PARAM_REFERENCE_TYPE, errbuf);
395 if (errbuf IS NOT NULL) then
396 /*srw.message('00', errbuf);*/null;
397
398 end if;
399
400 gl_info.gl_get_lookup_value('M', 'DR', 'GL_DR_CR', DR_MEANING, errbuf);
401 if (errbuf IS NOT NULL) then
402 /*srw.message('00', errbuf);*/null;
403
404 end if;
405
406 gl_info.gl_get_lookup_value('M', 'CR', 'GL_DR_CR', CR_MEANING, errbuf);
407 if (errbuf IS NOT NULL) then
408 /*srw.message('00', errbuf);*/null;
409
410 end if;
411
412
413 return (TRUE);
414 end;
415
416 procedure gl_get_effective_num (tledger_id in number,
417 tperiod_name in varchar2,
418 teffnum out NOCOPY number,
419 errbuf out NOCOPY varchar2)
420 is
421 ledger_obj_type VARCHAR2(1);
422 single_ledger_id NUMBER;
423 BEGIN
424 select object_type_code
425 into ledger_obj_type
426 from gl_ledgers
427 where ledger_id = tledger_id;
428
429 if (ledger_obj_type = 'L') then
430 single_ledger_id := tledger_id;
431 else
432 select l.ledger_id
433 into single_ledger_id
434 from gl_ledger_set_assignments ls, gl_ledgers l
435 where ls.ledger_set_id = tledger_id
436 and l.ledger_id = ls.ledger_id
437 and l.object_type_code = 'L'
438 and rownum = 1;
439 end if;
440
441 select effective_period_num
442 into teffnum
443 from gl_period_statuses
444 where period_name = tperiod_name
445 and ledger_id = single_ledger_id
446 and application_id = 101;
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 errbuf := gl_message.get_message('GL_PLL_INVALID_PERIOD', 'Y',
451 'PERIOD', tperiod_name,
452 'LDGID', to_char(tledger_id));
453
454 WHEN OTHERS THEN
455 errbuf := SQLERRM;
456
457 END;
458
459 function g_maingroupfilter(FLEX_SECURE in varchar2) return boolean is
460 begin
461 /*srw.reference(STRUCT_NUM);*/null;
462
463 /*srw.reference(FLEXDATA);*/null;
464
465
466
467 if(FLEX_SECURE ='S') then
468 return (FALSE);
469 else
470 return (TRUE);
471 end if;
472
473 RETURN NULL;
474 end;
475
476 function begin_bal_dr_crformula(BEGIN_DR in number, BEGIN_CR in number) return char is
477 begin
478 if (BEGIN_DR >= BEGIN_CR) then
479 return (DR_MEANING);
480 else
481 return (CR_MEANING);
482 end if;
483 end;
484
485 function end_bal_dr_crformula(END_DR in number, END_CR in number) return char is
486 begin
487 if (END_DR >= END_CR) then
488 return (DR_MEANING);
489 else
490 return (CR_MEANING);
491 end if;
492 end;
493
494 --Functions to refer Oracle report placeholders--
495
496 Function STRUCT_NUM_p return varchar2 is
497 Begin
498 return STRUCT_NUM;
499 END;
500 Function ACCESS_SET_NAME_p return varchar2 is
501 Begin
502 return ACCESS_SET_NAME;
503 END;
504 Function SELECT_ALL_p return varchar2 is
505 Begin
506 return SELECT_ALL;
507 END;
508 Function WHERE_FLEX_p return varchar2 is
509 Begin
510 return WHERE_FLEX;
511 END;
512 Function ORDERBY_BAL_p return varchar2 is
513 Begin
514 return ORDERBY_BAL;
515 END;
516 Function ORDERBY_ACCT_p return varchar2 is
517 Begin
518 return ORDERBY_ACCT;
519 END;
520 Function ORDERBY_ALL_p return varchar2 is
521 Begin
522 return ORDERBY_ALL;
523 END;
524 Function WHERE_ACTUAL_TYPE_p return varchar2 is
525 Begin
526 return WHERE_ACTUAL_TYPE;
527 END;
528 Function WHERE_CURRENCY_CODE_p return varchar2 is
529 Begin
530 return WHERE_CURRENCY_CODE;
531 END;
532 Function SELECT_REFERENCE_p return varchar2 is
533 Begin
534 return SELECT_REFERENCE;
535 END;
536 Function SELECT_CR_p return varchar2 is
537 Begin
538 return SELECT_CR;
539 END;
540 Function SELECT_DR_p return varchar2 is
541 Begin
542 return SELECT_DR;
543 END;
544 Function ORDER_BY_p return varchar2 is
545 Begin
546 return ORDER_BY;
547 END;
548 Function ORDERBY_BAL2_p return varchar2 is
549 Begin
550 return ORDERBY_BAL2;
551 END;
552 Function ORDERBY_ACCT2_p return varchar2 is
553 Begin
554 return ORDERBY_ACCT2;
555 END;
556 Function WHERE_CURRENCY_BAL_p return varchar2 is
557 Begin
558 return WHERE_CURRENCY_BAL;
559 END;
560 Function SECURITY_FILTER_STR_p return varchar2 is
561 Begin
562 return SECURITY_FILTER_STR;
563 END;
564 Function RESULTING_CURRENCY_p return varchar2 is
565 Begin
566 return RESULTING_CURRENCY;
567 END;
568 Function WHERE_DAS_BAL_p return varchar2 is
569 Begin
570 return WHERE_DAS_BAL;
571 END;
572 Function WHERE_DAS_JE_p return varchar2 is
573 Begin
574 return WHERE_DAS_JE;
575 END;
576 Function SELECT_BEGIN_DR_p return varchar2 is
577 Begin
578 return SELECT_BEGIN_DR;
579 END;
580 Function SELECT_BEGIN_CR_p return varchar2 is
581 Begin
582 return SELECT_BEGIN_CR;
583 END;
584 Function SELECT_END_DR_p return varchar2 is
585 Begin
586 return SELECT_END_DR;
587 END;
588 Function SELECT_END_CR_p return varchar2 is
589 Begin
590 return SELECT_END_CR;
591 END;
592 Function PARAM_ACCT_FROM_p return varchar2 is
593 Begin
594 return PARAM_ACCT_FROM;
595 END;
596 Function PARAM_ACCT_TO_p return varchar2 is
597 Begin
598 return PARAM_ACCT_TO;
599 END;
600 Function PARAM_PERIOD_FROM_p return varchar2 is
601 Begin
602 return PARAM_PERIOD_FROM;
603 END;
604 Function PARAM_PERIOD_TO_p return varchar2 is
605 Begin
606 return PARAM_PERIOD_TO;
607 END;
608 Function PARAM_REFERENCE_TYPE_p return varchar2 is
609 Begin
610 return PARAM_REFERENCE_TYPE;
611 END;
612 Function DR_MEANING_p return varchar2 is
613 Begin
614 return DR_MEANING;
615 END;
616 Function CR_MEANING_p return varchar2 is
617 Begin
618 return CR_MEANING;
619 END;
620 Function PARAM_CURRENCY_TYPE_p return varchar2 is
621 Begin
622 return PARAM_CURRENCY_TYPE;
623 END;
624 END GL_GLRJED_XMLP_PKG ;
625
626