1 PACKAGE BODY GL_GLRGNJ_XMLP_PKG AS
5 SELECT PERIOD_NAME
2 /* $Header: GLRGNJB.pls 120.0 2007/12/27 14:36:50 vijranga noship $ */
3 function BeforeReport return boolean is
4 cursor get_to_period(T_END_DATE DATE, T_PERIOD_SET_NAME VARCHAR) is
6 FROM GL_PERIODS
7 WHERE PERIOD_SET_NAME = T_PERIOD_SET_NAME
8 AND END_DATE = T_END_DATE
9 ORDER BY PERIOD_NUM DESC;
10 cursor get_from_period(T_START_DATE DATE, T_PERIOD_SET_NAME VARCHAR) is
11 SELECT PERIOD_NAME
12 FROM GL_PERIODS
13 WHERE PERIOD_SET_NAME = T_PERIOD_SET_NAME
14 AND START_DATE = T_START_DATE
15 ORDER BY PERIOD_NUM DESC;
16 t_p_start_date DATE;
17 t_p_end_date DATE;
18 t_period_from VARCHAR2(15);
19 t_period_to VARCHAR2(15);
20 t_errorbuffer VARCHAR2(132);
21 t_period_set_name VARCHAR(15);
22 begin
23 /*srw.user_exit('FND SRWINIT');*/null;
24 INV_FLEX_MSG := gl_message.get_message(
25 'GL_PLL_INVALID_FLEXFIELD','N');
26 STAT:= FND_CURRENCY.GET_FORMAT_MASK('STAT',19);
27 begin
28 SELECT name, chart_of_accounts_id, period_set_name
29 INTO ACCESS_SET_NAME, STRUCT_NUM, T_PERIOD_SET_NAME
30 FROM gl_access_sets
31 WHERE access_set_id = P_ACCESS_SET_ID;
32 exception
33 WHEN NO_DATA_FOUND THEN
34 t_errorbuffer := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
35 'DASID', to_char(P_ACCESS_SET_ID));
36 /*srw.message('00', t_errorbuffer);*/null;
37 raise_application_error(-20101,null);/*srw.program_abort;*/null;
38 WHEN OTHERS THEN
39 t_errorbuffer := SQLERRM;
40 /*srw.message('00', t_errorbuffer);*/null;
41 raise_application_error(-20101,null);/*srw.program_abort;*/null;
42 end;
43 /*SRW.REFERENCE(STRUCT_NUM);*/null;
44 null;
45 IF (P_KIND = 'S') THEN
46 POSTING_STATUS_SELECT := 'J.reference_4';
47 ELSE
48 POSTING_STATUS_SELECT := 'J.reference_1';
49 END IF;
50 IF (P_CURRENCY_CODE = 'STAT') THEN
51 CURRENCY_WHERE := 'D.currency_code = ''STAT''';
52 ELSE
53 CURRENCY_WHERE := 'D.currency_code <> ''STAT'' AND L.currency_code = ''' ||
54 P_CURRENCY_CODE || '''';
55 END IF;
56 IF (P_POSTING_STATUS = 'E') THEN
57 POSTING_STATUS_WHERE := 'B.status NOT in (''S'',''I'',''U'',''P'')';
58 ELSE
59 POSTING_STATUS_WHERE :=
60 'B.status = ' || '''' || P_POSTING_STATUS || '''';
61 END IF;
62 IF (P_PERIOD_NAME IS NOT NULL) THEN
63 PERIOD_WHERE := 'B.default_period_name = ''' || P_PERIOD_NAME || '''';
64 ELSE
65 IF (P_POSTING_STATUS = 'P') THEN
66 IF (P_START_DATE IS NOT NULL AND P_END_DATE IS NOT NULL) THEN
67 PERIOD_WHERE := 'trunc(B.posted_date) between to_date(' || '''' || to_char(P_START_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'') AND to_date(' || '''' || to_char(P_END_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
68 ELSIF (P_START_DATE IS NULL AND P_END_DATE IS NOT NULL) THEN
69 PERIOD_WHERE := 'trunc(B.posted_date) <= to_date(' || '''' || to_char(P_END_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
70 ELSIF (P_START_DATE IS NOT NULL AND P_END_DATE IS NULL) THEN
71 PERIOD_WHERE := 'trunc(B.posted_date) >= to_date(' || '''' || to_char(P_START_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
72 END IF;
73 ELSE
74 IF (P_START_DATE IS NOT NULL AND P_END_DATE IS NOT NULL) THEN
75 PERIOD_WHERE := 'trunc(D.DEFAULT_EFFECTIVE_DATE) between to_date(' || '''' || to_char(P_START_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'') and to_date(' || '''' || to_char(P_END_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
76 ELSIF (P_START_DATE IS NULL AND P_END_DATE IS NOT NULL) THEN
77 PERIOD_WHERE := 'trunc(D.DEFAULT_EFFECTIVE_DATE) <= to_date(' || '''' || to_char(P_END_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
78 ELSIF (P_START_DATE IS NOT NULL AND P_END_DATE IS NULL) THEN
79 PERIOD_WHERE := 'trunc(D.DEFAULT_EFFECTIVE_DATE) >= to_date(' || '''' || to_char(P_START_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
80 END IF;
81 END IF;
82 END IF;
83 DAS_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
84 P_ACCESS_SET_ID,
85 'R',
86 'LEDGER_COLUMN',
87 'LEDGER_ID',
88 'D',
89 'SEG_COLUMN',
90 null,
91 'CC',
92 null);
93 IF (DAS_WHERE is not null) THEN
94 DAS_WHERE := ' AND ' || DAS_WHERE;
95 END IF;
96 IF (P_PAGESIZE = 180) THEN
97 WIDTH := 19;
98 ELSE
99 WIDTH := 16;
100 END IF;
101 IF (P_LEDGER_ID IS NOT NULL) THEN
102 begin
103 SELECT name, object_type_code, consolidation_ledger_flag
104 INTO PARAM_LEDGER_NAME, PARAM_LEDGER_TYPE, CONSOLIDATION_LEDGER_FLAG
105 FROM gl_ledgers
106 WHERE ledger_id = P_LEDGER_ID;
107 exception
108 WHEN OTHERS THEN
112 end;
109 t_errorbuffer := SQLERRM;
110 /*srw.message('00', t_errorbuffer);*/null;
111 raise_application_error(-20101,null);/*srw.program_abort;*/null;
113 IF (PARAM_LEDGER_TYPE = 'S') THEN
114 LEDGER_FROM := 'GL_LEDGER_SET_ASSIGNMENTS LS,';
115 LEDGER_WHERE := 'LS.ledger_set_id = ' || to_char(P_LEDGER_ID) || ' AND ' ||
116 'D.ledger_id = LS.ledger_id AND ';
117 ELSE
118 LEDGER_FROM := ' ';
119 LEDGER_WHERE := 'D.ledger_id = ' || to_char(P_LEDGER_ID) || ' AND ';
120 END IF;
121 END IF;
122 IF(LEDGER_FROM IS NULL) THEN
123 LEDGER_FROM := ' ';
124 END IF;
125 IF (PARAM_LEDGER_TYPE = 'S') THEN
126 begin
127 SELECT consolidation_ledger_flag
128 INTO CONSOLIDATION_LEDGER_FLAG
129 FROM gl_system_usages
130 WHERE rownum = 1;
131 exception
132 WHEN OTHERS THEN
133 t_errorbuffer := SQLERRM;
134 /*srw.message('00', t_errorbuffer);*/null;
135 raise_application_error(-20101,null);/*srw.program_abort;*/null;
136 end;
137 END IF;
138 /*SRW.REFERENCE(P_JE_SOURCE_NAME);*/null;
139 IF (P_JE_SOURCE_NAME IS NOT NULL) THEN
140 SELECT USER_JE_SOURCE_NAME
141 INTO JE_USER_SOURCE_DSP
142 FROM GL_JE_SOURCES
143 WHERE JE_SOURCE_NAME = P_JE_SOURCE_NAME;
144 END IF;
145 IF (p_period_name IS NULL AND (p_start_date IS NULL OR p_end_date IS NULL)) THEN
146 IF (p_ledger_id is null) THEN
147 SELECT min(a.start_date),max(a.end_date)
148 INTO t_p_start_date, t_p_end_date
149 FROM gl_period_statuses a,
150 gl_access_set_assignments b
154 AND a.ledger_id = b.ledger_id;
151 WHERE b.access_set_id = p_access_set_id
152 AND a.application_id = 101
153 AND a.closing_status <> 'N'
155 ELSIF (p_ledger_id is not null and param_ledger_type = 'S') THEN
156 SELECT min(a.start_date),max(a.end_date)
157 INTO t_p_start_date, t_p_end_date
158 FROM gl_period_statuses a,
159 gl_ledger_set_assignments b
160 WHERE b.ledger_set_id = p_ledger_id
161 AND a.application_id = 101
162 AND a.closing_status <> 'N'
163 AND a.ledger_id = b.ledger_id;
164 ELSE
165 SELECT min(start_date),max(end_date)
166 INTO t_p_start_date, t_p_end_date
167 FROM gl_period_statuses
168 WHERE application_id = 101
169 AND ledger_id = p_ledger_id
170 AND closing_status <> 'N';
171 END IF;
172 IF (p_start_date IS NULL AND p_end_date IS NULL) THEN
173 OPEN get_from_period(t_p_start_date, t_period_set_name);
174 FETCH get_from_period INTO T_PERIOD_FROM;
175 IF (get_from_period%FOUND) THEN
176 CLOSE get_from_period;
177 ELSE
178 CLOSE get_from_period;
179 END IF;
180 OPEN get_to_period(t_p_end_date, t_period_set_name);
181 FETCH get_to_period INTO T_PERIOD_TO;
182 IF (get_to_period%FOUND) THEN
183 CLOSE get_to_period;
184 ELSE
185 CLOSE get_to_period;
186 END IF;
187 PERIOD_FROM := T_PERIOD_FROM;
188 PERIOD_TO := T_PERIOD_TO;
189 ELSIF (p_start_date IS NULL AND p_end_date IS NOT NULL) THEN
190 p_start_date := t_p_start_date;
191 ELSIF (P_START_DATE IS NOT NULL AND P_END_DATE IS NULL) THEN
192 p_end_date := t_p_end_date;
193 END IF;
194 END IF ;
195 PARAM_CURRENCY := P_CURRENCY_CODE;
196 PARAM_PERIOD_NAME := P_PERIOD_NAME;
197 PARAM_START_DATE := P_START_DATE;
201 if (t_errorbuffer IS NOT NULL) then
198 PARAM_END_DATE := P_END_DATE;
199 gl_info.gl_get_lookup_value('M', P_POSTING_STATUS, 'JOURNAL_REPORT_TYPE',
200 PARAM_POSTING_STATUS, t_errorbuffer);
202 /*SRW.MESSAGE('00', t_errorbuffer);*/null;
203 end if;
204 gl_info.gl_get_lookup_value('M', P_KIND, 'ACCOUNT_RPT_KIND',
205 PARAM_REFERENCE_TYPE, t_errorbuffer);
206 if (t_errorbuffer IS NOT NULL) then
207 /*srw.message('00', t_errorbuffer);*/null;
208 end if;
209 return (TRUE);
210 end;
211 function AfterReport return boolean is
212 begin
213 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
214 return (TRUE);
215 end;
216 function g_linesgroupfilter(FLEXDATA_SECURE in varchar2) return boolean is
217 begin
218 /*SRW.REFERENCE(STRUCT_NUM);*/null;
219 /*SRW.REFERENCE(FLEXDATA_H);*/null;
220 if (FLEXDATA_SECURE = 'S') then
221 return (FALSE);
222 else
223 return (TRUE);
224 end if;
225 return (TRUE);
226 end;
227 --Functions to refer Oracle report placeholders--
228 Function FLEXDATA_p return varchar2 is
229 Begin
230 return FLEXDATA;
231 END;
232 Function POSTING_STATUS_SELECT_p return varchar2 is
233 Begin
234 return POSTING_STATUS_SELECT;
235 END;
236 Function POSTING_STATUS_WHERE_p return varchar2 is
237 Begin
238 return POSTING_STATUS_WHERE;
239 END;
240 Function PERIOD_WHERE_p return varchar2 is
241 Begin
242 return PERIOD_WHERE;
243 END;
244 Function CURRENCY_WHERE_p return varchar2 is
245 Begin
246 return CURRENCY_WHERE;
247 END;
248 Function STRUCT_NUM_p return varchar2 is
249 Begin
250 return STRUCT_NUM;
251 END;
252 Function PARAM_LEDGER_NAME_p return varchar2 is
253 Begin
254 return PARAM_LEDGER_NAME;
255 END;
256 Function INV_FLEX_MSG_p return varchar2 is
257 Begin
258 return INV_FLEX_MSG;
259 END;
260 Function WIDTH_p return number is
261 Begin
262 return WIDTH;
263 END;
264 Function JE_USER_SOURCE_DSP_p return varchar2 is
265 Begin
266 return JE_USER_SOURCE_DSP;
267 END;
268 Function ACCESS_SET_NAME_p return varchar2 is
269 Begin
270 return ACCESS_SET_NAME;
271 END;
272 Function DAS_WHERE_p return varchar2 is
273 Begin
274 return DAS_WHERE;
275 END;
276 Function LEDGER_FROM_p return varchar2 is
277 Begin
278 return LEDGER_FROM;
279 END;
280 Function LEDGER_WHERE_p return varchar2 is
281 Begin
282 return LEDGER_WHERE;
283 END;
284 Function PARAM_LEDGER_TYPE_p return varchar2 is
285 Begin
286 return PARAM_LEDGER_TYPE;
287 END;
288 Function CONSOLIDATION_LEDGER_FLAG_p return varchar2 is
289 Begin
290 return CONSOLIDATION_LEDGER_FLAG;
291 END;
292 Function PARAM_CURRENCY_p return varchar2 is
293 Begin
294 return PARAM_CURRENCY;
295 END;
296 Function PARAM_PERIOD_NAME_p return varchar2 is
297 Begin
298 return PARAM_PERIOD_NAME;
299 END;
300 Function PARAM_START_DATE_p return date is
301 Begin
302 return PARAM_START_DATE;
303 END;
304 Function PARAM_END_DATE_p return date is
305 Begin
306 return PARAM_END_DATE;
307 END;
308 Function PARAM_REFERENCE_TYPE_p return varchar2 is
309 Begin
310 return PARAM_REFERENCE_TYPE;
311 END;
312 Function PARAM_POSTING_STATUS_p return varchar2 is
313 Begin
314 return PARAM_POSTING_STATUS;
315 END;
316 Function PERIOD_FROM_p return varchar2 is
317 Begin
318 return PERIOD_FROM;
319 END;
320 Function PERIOD_TO_p return varchar2 is
321 Begin
322 return PERIOD_TO;
323 END;
324 END GL_GLRGNJ_XMLP_PKG ;
325
[Home] [Help]