[Home] [Help]
PACKAGE BODY: APPS.GL_GLRFGNJ_XMLP_PKG
Source
1 PACKAGE BODY GL_GLRFGNJ_XMLP_PKG AS
2 /* $Header: GLRFGNJB.pls 120.0 2007/12/27 14:35:21 vijranga noship $ */
3 function BeforeReport return boolean is
4 t_debit VARCHAR2(240);
5 t_credit VARCHAR2(240);
6 t_errorbuffer VARCHAR2(132);
7 begin
8 /*srw.user_exit('FND SRWINIT');*/null;
9 INV_FLEX_MSG := gl_message.get_message(
10 'GL_PLL_INVALID_FLEXFIELD','N');
11 STAT:= FND_CURRENCY.GET_FORMAT_MASK('STAT',19);
12 begin
13 SELECT name, chart_of_accounts_id
14 INTO ACCESS_SET_NAME, STRUCT_NUM
15 FROM gl_access_sets
16 WHERE access_set_id = P_ACCESS_SET_ID;
17 exception
18 WHEN NO_DATA_FOUND THEN
19 t_errorbuffer := gl_message.get_message('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
20 'DASID', to_char(P_ACCESS_SET_ID));
21 /*srw.message('00', t_errorbuffer);*/null;
22 raise_application_error(-20101,null);/*srw.program_abort;*/null;
23 WHEN OTHERS THEN
24 t_errorbuffer := SQLERRM;
25 /*srw.message('00', t_errorbuffer);*/null;
26 raise_application_error(-20101,null);/*srw.program_abort;*/null;
27 end;
28 gl_info.gl_get_lookup_value('D',
29 'D',
30 'DR_CR',
31 t_debit,
32 t_errorbuffer);
33 if (t_errorbuffer is not NULL) then
34 /*SRW.MESSAGE(0,t_errorbuffer);*/null;
35 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
36 else
37 DEBIT_DSP := t_debit;
38 end if;
39 gl_info.gl_get_lookup_value('D',
40 'C',
41 'DR_CR',
42 t_credit,
43 t_errorbuffer);
44 if (t_errorbuffer is not NULL) then
45 /*SRW.MESSAGE(0,t_errorbuffer);*/null;
46 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
47 else
48 CREDIT_DSP := t_credit;
49 end if;
50 /*SRW.REFERENCE(STRUCT_NUM);*/null;
51 null;
52 IF (P_KIND = 'S') THEN
53 POSTING_STATUS_SELECT := 'J.reference_4';
54 ELSE
55 POSTING_STATUS_SELECT := 'J.reference_1';
56 END IF;
57 IF (P_POSTING_STATUS = 'E') THEN
58 POSTING_STATUS_WHERE := 'B.status NOT in (''S'',''I'',''U'',''P'')';
59 ELSE
60 POSTING_STATUS_WHERE :=
61 'B.status = ' || '''' || P_POSTING_STATUS || '''';
62 END IF;
63 IF (P_PERIOD_NAME IS NOT NULL) THEN
64 PERIOD_WHERE := 'B.default_period_name = ''' || P_PERIOD_NAME || '''';
65 ELSE
66 IF (P_POSTING_STATUS = 'P') THEN
67 IF (P_START_DATE IS NOT NULL AND P_END_DATE IS NOT NULL) THEN
68 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'')';
69 ELSIF (P_START_DATE IS NOT NULL) THEN
70 PERIOD_WHERE := 'trunc(B.posted_date) >= to_date(' || '''' || to_char(P_START_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
71 ELSIF (P_END_DATE IS NOT NULL) THEN
72 PERIOD_WHERE := 'trunc(B.posted_date) <= to_date(' || '''' || to_char(P_END_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
73 END IF;
74 ELSE
75 IF (P_START_DATE IS NOT NULL AND P_END_DATE IS NOT NULL) THEN
76 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'')';
77 ELSIF (P_START_DATE IS NOT NULL) THEN
78 PERIOD_WHERE := 'trunc(D.DEFAULT_EFFECTIVE_DATE) >= to_date(' || '''' || to_char(P_START_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
79 ELSIF (P_END_DATE IS NOT NULL) THEN
80 PERIOD_WHERE := 'trunc(D.DEFAULT_EFFECTIVE_DATE) <= to_date(' || '''' || to_char(P_END_DATE,'DD-MON-YYYY') || '''' || ',''DD-MON-YYYY'')';
81 END IF;
82 END IF;
83 END IF;
84 DAS_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
85 P_ACCESS_SET_ID,
86 'R',
87 'LEDGER_COLUMN',
88 'LEDGER_ID',
89 'D',
90 'SEG_COLUMN',
91 null,
92 'CC',
93 null);
94 IF (DAS_WHERE is not null) THEN
95 DAS_WHERE := ' AND ' || DAS_WHERE;
96 END IF;
97 IF P_PAGESIZE = 180 THEN
98 WIDTH := 19;
99 ELSE
100 WIDTH := 16;
101 END IF;
102 IF (P_LEDGER_ID IS NOT NULL) THEN
103 begin
104 SELECT name, currency_code, object_type_code, consolidation_ledger_flag
105 INTO PARAM_LEDGER_NAME, PARAM_LEDGER_CURR,
106 PARAM_LEDGER_TYPE, CONSOLIDATION_LEDGER_FLAG
107 FROM gl_ledgers
108 WHERE ledger_id = P_LEDGER_ID;
109 exception
110 WHEN OTHERS THEN
111 t_errorbuffer := SQLERRM;
112 /*srw.message('00', t_errorbuffer);*/null;
113 raise_application_error(-20101,null);/*srw.program_abort;*/null;
114 end;
115 IF (PARAM_LEDGER_TYPE = 'S') THEN
116 LEDGER_FROM := 'GL_LEDGER_SET_ASSIGNMENTS LS,';
117 LEDGER_WHERE := 'LS.ledger_set_id = ' || to_char(P_LEDGER_ID) || ' AND ' ||
118 'D.ledger_id = LS.ledger_id AND ';
119 ELSE
120 --LEDGER_FROM := '';
121 LEDGER_FROM :=' ';
122 LEDGER_WHERE := 'D.ledger_id = ' || to_char(P_LEDGER_ID) || ' AND ';
123 END IF;
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 exception
131 WHEN OTHERS THEN
132 t_errorbuffer := SQLERRM;
133 /*srw.message('00', t_errorbuffer);*/null;
134 raise_application_error(-20101,null);/*srw.program_abort;*/null;
135 end;
136 END IF;
137 IF (PARAM_LEDGER_TYPE = 'S') THEN
138 begin
139 SELECT substr(ltrim(to_char(11, '9G9')), 2, 1)
140 INTO THOUSANDS_SEPARATOR
141 FROM dual;
142 exception
143 WHEN OTHERS THEN
144 t_errorbuffer := SQLERRM;
145 /*srw.message('00', t_errorbuffer);*/null;
146 raise_application_error(-20101,null);/*srw.program_abort;*/null;
147 end;
148 MIXED_PRECISION := to_number(FND_PROFILE.value('CURRENCY:MIXED_PRECISION'));
149 FND_CURRENCY.build_format_mask(CURR_FORMAT_MASK, 38, MIXED_PRECISION, null);
150 CURR_FORMAT_MASK := REPLACE(CURR_FORMAT_MASK, 'FM');
151 CURR_FORMAT_MASK := REPLACE(CURR_FORMAT_MASK, 'FX');
152 END IF;
153 return (TRUE);
154 end;
155 function AfterReport return boolean is
156 begin
157 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
158 return (TRUE);
159 end;
160 function ent_x_drcrformula(DR_CR in number, ENTERED_AMOUNT in number) return number is
161 begin
162 RETURN(DR_CR * ENTERED_AMOUNT);
163 end;
164 function acct_x_drcrformula(DR_CR in number, ACCOUNTED_AMOUNT in varchar2) return number is
165 begin
166 RETURN(DR_CR * ACCOUNTED_AMOUNT);
167 end;
168 function dr_cr_dspformula(DR_CR in number) return varchar2 is
169 begin
170 IF (DR_CR in (0,1)) THEN
171 RETURN(DEBIT_DSP);
172 ELSE
173 RETURN(CREDIT_DSP);
174 END IF;
175 RETURN NULL;
176 end;
177 function g_linesgroupfilter(FLEXDATA_SECURE in varchar2) return boolean is
178 begin
179 /*SRW.REFERENCE(STRUCT_NUM);*/null;
180 /*SRW.REFERENCE(FLEXDATA_H);*/null;
181 if (FLEXDATA_SECURE = 'S') then
182 return (FALSE);
183 else
184 return (TRUE);
185 end if;
186 return (TRUE);
187 end;
188 --function gl_format_currency(amount number)(Amount NUMBER) return varchar2 is
189 function gl_format_currency(amount IN number) return varchar2 is
190 num_amount NUMBER;
191 dsp_amount VARCHAR2(100);
192 BEGIN
193 num_amount := ROUND(Amount, MIXED_PRECISION);
194 dsp_amount := LTRIM(TO_CHAR(num_amount, CURR_FORMAT_MASK));
195 IF (LENGTH(dsp_amount) > WIDTH) THEN
196 dsp_amount := REPLACE(dsp_amount, ' ');
197 IF (LENGTH(dsp_amount) > WIDTH) THEN
198 dsp_amount := REPLACE(dsp_amount, THOUSANDS_SEPARATOR);
199 IF (LENGTH(dsp_amount) > WIDTH) THEN
200 dsp_amount := RPAD('*', WIDTH, '*');
201 END IF;
202 END IF;
203 END IF;
204 RETURN dsp_amount;
205 END;
206 --Functions to refer Oracle report placeholders--
207 Function FLEXDATA_p return varchar2 is
208 Begin
209 return FLEXDATA;
210 END;
211 Function POSTING_STATUS_SELECT_p return varchar2 is
212 Begin
213 return POSTING_STATUS_SELECT;
214 END;
215 Function POSTING_STATUS_WHERE_p return varchar2 is
216 Begin
217 return POSTING_STATUS_WHERE;
218 END;
219 Function PERIOD_WHERE_p return varchar2 is
220 Begin
221 return PERIOD_WHERE;
222 END;
223 Function STRUCT_NUM_p return varchar2 is
224 Begin
225 return STRUCT_NUM;
226 END;
227 Function PARAM_LEDGER_NAME_p return varchar2 is
228 Begin
229 return PARAM_LEDGER_NAME;
230 END;
231 Function INV_FLEX_MSG_p return varchar2 is
232 Begin
233 return INV_FLEX_MSG;
234 END;
235 Function DEBIT_DSP_p return varchar2 is
236 Begin
237 return DEBIT_DSP;
238 END;
239 Function CREDIT_DSP_p return varchar2 is
240 Begin
241 return CREDIT_DSP;
242 END;
243 Function WIDTH_p return number is
244 Begin
245 return WIDTH;
246 END;
247 Function DAS_WHERE_p return varchar2 is
248 Begin
249 return DAS_WHERE;
250 END;
251 Function ACCESS_SET_NAME_p return varchar2 is
252 Begin
253 return ACCESS_SET_NAME;
254 END;
255 Function PARAM_LEDGER_TYPE_p return varchar2 is
256 Begin
257 return PARAM_LEDGER_TYPE;
258 END;
259 Function CURR_FORMAT_MASK_p return varchar2 is
260 Begin
261 return CURR_FORMAT_MASK;
262 END;
263 Function THOUSANDS_SEPARATOR_p return varchar2 is
264 Begin
265 return THOUSANDS_SEPARATOR;
266 END;
267 Function MIXED_PRECISION_p return number is
268 Begin
269 return MIXED_PRECISION;
270 END;
271 Function LEDGER_WHERE_p return varchar2 is
272 Begin
273 return LEDGER_WHERE;
274 END;
275 Function LEDGER_FROM_p return varchar2 is
276 Begin
277 return LEDGER_FROM;
278 END;
279 Function CONSOLIDATION_LEDGER_FLAG_p return varchar2 is
280 Begin
281 return CONSOLIDATION_LEDGER_FLAG;
282 END;
283 Function PARAM_LEDGER_CURR_p return varchar2 is
284 Begin
285 return PARAM_LEDGER_CURR;
286 END;
287 END GL_GLRFGNJ_XMLP_PKG ;
288
289