1 PACKAGE BODY GL_GLYRLJGE_XMLP_PKG AS
2 /* $Header: GLYRLJGEB.pls 120.2 2008/06/25 11:44:46 vijranga noship $ */
3 function BeforeReport return boolean is
4 coaid NUMBER;
5 ledgername VARCHAR2(30);
6 func_curr VARCHAR2(15);
7 currency_name VARCHAR2(30);
8 errbuf VARCHAR2(132);
9 errbuf2 VARCHAR2(132);
10 start_date DATE;
11 end_date DATE;
12 dummy DATE;
13 fiscal_year_s VARCHAR2(15) :='1900';
14 fiscal_year_e VARCHAR2(15) :='1900';
15 per_type VARCHAR2(15);
16 per_set VARCHAR2(15);
17 begin
18 /*SRW.USER_EXIT('FND SRWINIT');*/null;
19 begin
20 SELECT name, chart_of_accounts_id
21 INTO C_ACCESS_SET_NAME, C_CHART_OF_ACCTS_ID
22 FROM gl_access_sets
23 WHERE access_set_id = P_ACCESS_SET_ID;
24 exception
25 WHEN NO_DATA_FOUND THEN
26 errbuf := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
27 'DASID', to_char(P_ACCESS_SET_ID));
28 /*srw.message('00', errbuf);*/null;
29 raise_application_error(-20101,null);/*srw.program_abort;*/null;
30 WHEN OTHERS THEN
31 errbuf := SQLERRM;
32 /*srw.message('00', errbuf);*/null;
33 raise_application_error(-20101,null);/*srw.program_abort;*/null;
34 end;
35 C_START_DATE := to_date(substr(P_START_PERIOD,1,10),'yyyy/mm/dd');
36 C_END_DATE := to_date(substr(P_END_PERIOD,1,10),'yyyy/mm/dd');
37 C_START_DATE1 := to_char(C_START_DATE,'DD-MON-YYYY');
38 C_END_DATE1 := to_char(C_END_DATE,'DD-MON-YYYY');
39 P_STRUCT_NUM := C_CHART_OF_ACCTS_ID;
40 C_ACCESS_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
41 P_ACCESS_SET_ID,
42 'R',
43 'LEDGER_COLUMN',
44 'LEDGER_ID',
45 'T2', 'SEG_COLUMN',
46 null,
47 'T4', null);
48 IF (C_ACCESS_WHERE is not null) THEN
49 C_ACCESS_WHERE := ' AND ' || C_ACCESS_WHERE;
50 END IF;
51 C_LEDGER_FROM := '';
52 C_LEDGER_WHERE := '';
53 IF P_LEDGER_ID IS NOT NULL THEN
54 begin
55 SELECT name, currency_code, object_type_code, consolidation_ledger_flag,
56 period_set_name, accounted_period_type
57 INTO PARAM_LEDGER_NAME, PARAM_LEDGER_CURR,
58 PARAM_LEDGER_TYPE, CONSOLIDATION_LEDGER_FLAG,
59 per_set, per_type
60 FROM gl_ledgers
61 WHERE ledger_id = P_LEDGER_ID;
62 exception
63 WHEN OTHERS THEN
64 errbuf := SQLERRM;
65 /*srw.message('00', errbuf);*/null;
66 raise_application_error(-20101,null);/*srw.program_abort;*/null;
67 end;
68 IF (PARAM_LEDGER_TYPE = 'S') THEN C_LEDGER_FROM := 'GL_LEDGER_SET_ASSIGNMENTS LS,';
69 C_LEDGER_WHERE := ' AND LS.ledger_set_id = ' || to_char(P_LEDGER_ID) || ' AND ' ||
70 'T2.ledger_id = LS.ledger_id';
71 ELSE
72 C_LEDGER_FROM := '';
73 C_LEDGER_WHERE := ' AND T2.ledger_id = ' || to_char(P_LEDGER_ID);
74 END IF;
75 ELSE
76 BEGIN
77 SELECT PERIOD_SET_NAME, ACCOUNTED_PERIOD_TYPE
78 INTO per_set, per_type
79 FROM GL_ACCESS_SETS
80 WHERE ACCESS_SET_ID = P_ACCESS_SET_ID;
81 exception
82 WHEN NO_DATA_FOUND THEN
83 errbuf := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
84 'DASID', to_char(P_ACCESS_SET_ID)); /*srw.message('00', errbuf);*/null;
85 raise_application_error(-20101,null);/*srw.program_abort;*/null;
86 WHEN OTHERS THEN
87 errbuf := SQLERRM;
88 /*srw.message('00', errbuf);*/null;
89 raise_application_error(-20101,null);/*srw.program_abort;*/null;
90 END;
91 END IF;
92 IF (C_LEDGER_FROM IS NULL) THEN
93 C_LEDGER_FROM := ' ';
94 END IF;
95 IF (PARAM_LEDGER_TYPE = 'S') THEN
96 begin
97 SELECT consolidation_ledger_flag
98 INTO CONSOLIDATION_LEDGER_FLAG
99 FROM gl_system_usages;
100 exception
101 WHEN OTHERS THEN
102 errbuf := SQLERRM;
103 /*srw.message('00', errbuf);*/null;
104 raise_application_error(-20101,null);/*srw.program_abort;*/null;
105 end;
106 END IF;
107 IF (PARAM_LEDGER_TYPE = 'S') THEN
108 begin
109 SELECT substr(ltrim(to_char(11, '9G9')), 2, 1)
110 INTO THOUSANDS_SEPARATOR
111 FROM dual;
112 exception
113 WHEN OTHERS THEN
114 errbuf := SQLERRM;
115 /*srw.message('00', errbuf);*/null;
116 raise_application_error(-20101,null);/*srw.program_abort;*/null;
117 end;
118 MIXED_PRECISION := to_number(FND_PROFILE.value('CURRENCY:MIXED_PRECISION'));
119 FND_CURRENCY.build_format_mask(CURR_FORMAT_MASK, 38, MIXED_PRECISION, null);
120 CURR_FORMAT_MASK := REPLACE(CURR_FORMAT_MASK, 'FM');
121 CURR_FORMAT_MASK := REPLACE(CURR_FORMAT_MASK, 'FX');
122 END IF;
123 C_MESSAGE := '';
124 if (P_END_PERIOD is null) then
125 C_END_DATE := C_START_DATE;
126 end if;
127 begin
128 select t.period_year
129 into fiscal_year_s
130 from gl_periods t
131 where t.period_set_name = per_set
132 and t.period_type = per_type
133 and C_START_DATE between t.start_date and t.end_date
134 and t.adjustment_period_flag like
135 decode(P_ADJUSTMENT_PERIODS,'N','N',
136 '%')
137 and rownum = 1;
138 select t.period_year
139 into fiscal_year_e
140 from gl_periods t
141 where t.period_set_name = per_set
142 and t.period_type = per_type
143 and C_END_DATE between t.start_date and t.end_date
144 and t.adjustment_period_flag like
145 decode(P_ADJUSTMENT_PERIODS,'N','N',
146 '%')
147 and rownum = 1;
148 exception
149 when no_data_found
150 then
151 errbuf2 := gl_message.get_message(
152 'GL_PLL_ROUTINE_ERROR', 'N',
153 'ROUTINE','Check dates available'
154 );
155 /*srw.message('00', errbuf2);*/null;
156 raise_application_error(-20101,null);/*srw.program_abort;*/null;
157 end;
158 C_WHERE_PERIOD := '';
159 if P_ADJUSTMENT_PERIODS = 'N' then
160 if (fiscal_year_s <> fiscal_year_e) then
161 C_MESSAGE := 'This report was run across a fiscal year';
162 end if;
163 C_WHERE_PERIOD := ' and T6.adjustment_period_flag = ''N''';
164 end if;
165 IF (C_WHERE_PERIOD IS NULL) THEN
166 C_WHERE_PERIOD := ' ';
167 END IF;
168 C_WHERE := '';
169 /*SRW.REFERENCE(P_STRUCT_NUM);*/null;
170 null;
171 /*srw.reference(P_STRUCT_NUM);*/null;
172 null;
173 /*srw.reference(P_STRUCT_NUM);*/null;
174 null;
175 if P_COMPANY is not null then
176 /*SRW.REFERENCE(P_STRUCT_NUM);*/null;
177 null;
178 -- C_WHERE := ' AND ' || C_WHERE;
179 end if;
180 if P_JOURNAL_CAT is not null then
181 P_JOURNAL_CAT_1 := replace(P_JOURNAL_CAT,'''');
182 C_WHERE := C_WHERE||' and replace(T2.je_category,'''''''') like '''||P_JOURNAL_CAT_1||'''';
183 end if;
184 if P_CURRENCY_CODE is not null then
185 C_WHERE := C_WHERE||' and T2.currency_code = '''||P_CURRENCY_CODE||'''';
186 else
187 C_WHERE := C_WHERE||' and T2.currency_code <> ''STAT''';
188 end if;
189 get_industry_code ;
190 return (TRUE);
191 end;
192 function AfterReport return boolean is
193 begin
194 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
195 return (TRUE);
196 end;
197 function c_curr_nameformula(currency1 in varchar2) return varchar2 is
198 begin
199 declare
200 cursor get_curr_name is
201 select name
202 from fnd_currencies_vl
203 where currency_code = currency1;
204 curr_name varchar(80);
205 begin
206 open get_curr_name;
207 fetch get_curr_name into curr_name;
208 close get_curr_name;
209 return(curr_name);
210 end;
211 RETURN NULL; end;
212 function c_bal_lpromptformula(C_Bal_lprompt in varchar2) return varchar2 is
213 begin
214 /*srw.reference(C_Company_name) ;*/null;
215 declare
216 temp_char varchar2 (60) ;
217 begin
218 temp_char := initcap(substr(C_Bal_lprompt, 1,16))||':' ;
219 return(temp_char) ;
220 end ;
221 RETURN NULL; end;
222 function set_display_for_core return boolean is
223 begin
224 if c_industry_code = 'C' then
225 return(TRUE);
226 else
227 return(FALSE);
228 end if;
229 RETURN NULL; end;
230 function set_display_for_gov return boolean is
231 begin
232 if c_industry_code = 'C' then
233 return(FALSE);
234 else
235 return(TRUE);
236 end if;
237 RETURN NULL; end ;
238 procedure get_industry_code is
239 w_industry_code varchar2(20);
240 w_industry_stat varchar2(20);
241 begin
242 if fnd_installation.get(0, 0,
243 w_industry_stat,
244 w_industry_code) then
245 if w_industry_code = 'C' then
246 c_industry_code := w_industry_code ;
247 end if;
248 end if;
249 end ;
250 function c_entryformula(doc_sequence in number, entry_name in varchar2) return varchar2 is
251 begin
252 if doc_sequence is NOT NULL then
253 return(substr(to_char(doc_sequence),1,7));
254 else
255 return(substr(entry_name,1,7));
256 end if;
257 RETURN NULL; end;
258 function g_journalgroupfilter(FLEX_SECURE in varchar2, ACCOUNTED_CR in number, ACCOUNTED_DR in number, ENTERED_DR in number, ENTERED_CR in number) return boolean is
259 begin
260 /*srw.reference(P_STRUCT_NUM);*/null;
261 /*srw.reference(C_FLEXDATA);*/null;
262 if(FLEX_SECURE = 'S') then
263 return(FALSE);
264 else
265 return (TRUE);
266 end if;
267 IF ACCOUNTED_CR <> 0 OR
268 ACCOUNTED_DR <> 0 OR
269 ENTERED_DR <> 0 OR
270 ENTERED_CR <> 0 THEN
271 RETURN(TRUE);
272 ELSE
273 RETURN(FALSE);
274 END IF;
275 return (TRUE);
276 end;
277 function g_company_mastergroupfilter(BAL_SECURE in varchar2) return boolean is
278 begin
279 /*SRW.REFERENCE(Company);*/null;
280 if(BAL_SECURE = 'S') then
281 return(FALSE);
282 else
283 return (TRUE);
284 end if;
285 end;
286 function BetweenPage return boolean is
287 begin
288 return (TRUE);
289 end;
290 --function gl_format_currency(amount number)(Amount NUMBER) return varchar2 is
291 function gl_format_currency(amount number)return varchar2 is
292 num_amount NUMBER;
293 dsp_amount VARCHAR2(100);
294 BEGIN
295 num_amount := ROUND(Amount, MIXED_PRECISION);
296 dsp_amount := LTRIM(TO_CHAR(num_amount, CURR_FORMAT_MASK));
297 IF (LENGTH(dsp_amount) > WIDTH) THEN
298 dsp_amount := REPLACE(dsp_amount, ' ');
299 IF (LENGTH(dsp_amount) > WIDTH) THEN
300 dsp_amount := REPLACE(dsp_amount, THOUSANDS_SEPARATOR);
301 IF (LENGTH(dsp_amount) > WIDTH) THEN
302 dsp_amount := RPAD('*', WIDTH, '*');
303 END IF;
304 END IF;
305 END IF;
306 RETURN dsp_amount;
307 END;
308 function company_lprompt_ndformula(COMPANY_LPROMPT_ND in varchar2) return char is
309 begin
310 /*srw.reference(COMPANY_NAME_ND);*/null;
311 declare
312 temp_char VARCHAR2(240);
313 begin
314 temp_char := COMPANY_LPROMPT_ND||':' ;
315 return(temp_char) ;
316 end ;
317 RETURN NULL; end;
318 function zero_indicatorformula(ACCOUNTED_CR in number, ACCOUNTED_DR in number, ENTERED_DR in number, ENTERED_CR in number) return number is
319 begin
320 IF (ACCOUNTED_CR <> 0 OR
321 ACCOUNTED_DR <> 0 OR
322 ENTERED_DR <> 0 OR
323 ENTERED_CR <> 0) THEN
324 RETURN(1);
325 ELSE
326 RETURN(0);
327 END IF;
328 return null;
329 end;
330 function C_MESSAGEFormula return Char is
331 begin
332 return('Period is across the year');
333 end;
334 function g_journal_entriesgroupfilter(ACCOUNTED_CR in number, ACCOUNTED_DR in number, ENTERED_DR in number, ENTERED_CR in number) return boolean is
335 begin
336 IF ACCOUNTED_CR <> 0 OR
337 ACCOUNTED_DR <> 0 OR
338 ENTERED_DR <> 0 OR
339 ENTERED_CR <> 0 THEN
340 RETURN(TRUE);
341 ELSE
342 RETURN(FALSE);
343 END IF;
344 return (TRUE);
345 end;
346 function g_batchesgroupfilter(ACCOUNTED_CR in number, ACCOUNTED_DR in number, ENTERED_DR in number, ENTERED_CR in number) return varchar2 is
347 begin
348 IF ACCOUNTED_CR <> 0 OR
349 ACCOUNTED_DR <> 0 OR
350 ENTERED_DR <> 0 OR
351 ENTERED_CR <> 0 THEN
352 RETURN('TRUE');
353 ELSE
354 RETURN('FALSE');
355 END IF;
356 return ('TRUE');
357 end;
358 --Functions to refer Oracle report placeholders--
359 Function C_WHERE_p return varchar2 is
360 Begin
361 return C_WHERE;
362 END;
363 Function C_CHART_OF_ACCTS_ID_p return varchar2 is
364 Begin
365 return C_CHART_OF_ACCTS_ID;
366 END;
367 Function C_START_DATE_p return date is
368 Begin
369 return C_START_DATE;
370 END;
371 Function C_END_DATE_p return date is
372 Begin
373 return C_END_DATE;
374 END;
375 Function C_MESSAGE_p return varchar2 is
376 Begin
377 return C_MESSAGE;
378 END;
379 Function C_industry_code_p return varchar2 is
380 Begin
381 return C_industry_code;
382 END;
383 Function C_WHERE_PERIOD_p return varchar2 is
384 Begin
385 return C_WHERE_PERIOD;
386 END;
387 Function C_ACCESS_SET_NAME_p return varchar2 is
388 Begin
389 return C_ACCESS_SET_NAME;
390 END;
391 Function C_ACCESS_WHERE_p return varchar2 is
392 Begin
393 return C_ACCESS_WHERE;
394 END;
395 Function C_LEDGER_FROM_p return varchar2 is
396 Begin
397 return C_LEDGER_FROM;
398 END;
399 Function C_LEDGER_WHERE_p return varchar2 is
400 Begin
401 return C_LEDGER_WHERE;
402 END;
403 Function PARAM_LEDGER_TYPE_p return varchar2 is
404 Begin
405 return PARAM_LEDGER_TYPE;
406 END;
407 Function PARAM_LEDGER_NAME_p return varchar2 is
408 Begin
409 return PARAM_LEDGER_NAME;
410 END;
411 Function PARAM_LEDGER_CURR_p return varchar2 is
412 Begin
413 return PARAM_LEDGER_CURR;
414 END;
415 Function CONSOLIDATION_LEDGER_FLAG_p return varchar2 is
416 Begin
417 return CONSOLIDATION_LEDGER_FLAG;
418 END;
419 Function CURR_FORMAT_MASK_p return varchar2 is
420 Begin
421 return CURR_FORMAT_MASK;
422 END;
423 Function MIXED_PRECISION_p return number is
424 Begin
425 return MIXED_PRECISION;
426 END;
427 Function THOUSANDS_SEPARATOR_p return varchar2 is
428 Begin
429 return THOUSANDS_SEPARATOR;
430 END;
431 Function WIDTH_p return number is
432 Begin
433 return WIDTH;
434 END;
435 END GL_GLYRLJGE_XMLP_PKG ;
436
[Home] [Help]