[Home] [Help]
PACKAGE BODY: APPS.JL_JLCOGLAN_XMLP_PKG
Source
1 PACKAGE BODY JL_JLCOGLAN_XMLP_PKG AS
2 /* $Header: JLCOGLANB.pls 120.1 2007/12/25 16:48:11 dwkrishn noship $ */
3 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 RETURN NULL;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RETURN (FALSE);
10 END CUSTOM_INIT;
11
12 FUNCTION AFTERREPORT RETURN BOOLEAN IS
13 BEGIN
14 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
15 RETURN (TRUE);
16 END AFTERREPORT;
17
18 FUNCTION CF_BALFORMULA(CS_DEBITS IN NUMBER
19 ,CS_CREDITS IN NUMBER
20 ,C_BAL_SUM IN NUMBER) RETURN NUMBER IS
21 BEGIN
22 DECLARE
23 BAL NUMBER;
24 BEGIN
25 BAL := CS_DEBITS - CS_CREDITS + C_BAL_SUM;
26 RETURN (BAL);
27 END;
28 RETURN NULL;
29 END CF_BALFORMULA;
30
31 FUNCTION CF_OBALFORMULA(CS_ODEBITS IN NUMBER
32 ,CS_OCREDITS IN NUMBER) RETURN NUMBER IS
33 BEGIN
34 DECLARE
35 OBAL NUMBER;
36 BEGIN
37 OBAL := CS_ODEBITS - CS_OCREDITS;
38 RETURN (OBAL);
39 END;
40 RETURN NULL;
41 END CF_OBALFORMULA;
42
43 FUNCTION C_CCIDFORMULA(NIT IN NUMBER
44 ,ACCOUNT IN VARCHAR2) RETURN NUMBER IS
45 BEGIN
46 DECLARE
47 PN JL_CO_GL_BALANCES.PERIOD_NAME%TYPE;
48 ACODE JL_CO_GL_BALANCES.ACCOUNT_CODE%TYPE;
49 BBDR NUMBER;
50 BBCR NUMBER;
51 PNDR NUMBER;
52 PNCR NUMBER;
53 BEBAL NUMBER;
54 BEGIN
55 SELECT
56 BAL.PERIOD_NAME,
57 SUM(BAL.BEGIN_BALANCE_DR),
58 SUM(BAL.BEGIN_BALANCE_CR),
59 SUM(BAL.PERIOD_NET_DR),
60 SUM(BAL.PERIOD_NET_CR),
61 BAL.ACCOUNT_CODE
62 INTO PN,BBDR,BBCR,PNDR,PNCR,ACODE
63 FROM
64 JL_CO_GL_BALANCES BAL
65 WHERE BAL.NIT_ID = NIT
66 AND BAL.ACCOUNT_CODE = ACCOUNT
67 AND BAL.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
68 AND ( BAL.PERIOD_YEAR * 100 + BAL.PERIOD_NUM ) = (
69 SELECT
70 MAX(B1.PERIOD_YEAR * 100 + B1.PERIOD_NUM)
71 FROM
72 JL_CO_GL_BALANCES B1,
73 GL_CODE_COMBINATIONS GLCC
74 WHERE BAL.SET_OF_BOOKS_ID = B1.SET_OF_BOOKS_ID
75 AND BAL.ACCOUNT_CODE = B1.ACCOUNT_CODE
76 AND B1.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
77 AND BAL.NIT_ID = B1.NIT_ID
78 AND ( B1.PERIOD_YEAR * 100 + B1.PERIOD_NUM ) < ( NVL(C_PYEAR
79 ,0) * 100 + NVL(C_PNUM
80 ,0) )
81 AND B1.PERIOD_YEAR BETWEEN DECODE(GLCC.ACCOUNT_TYPE
82 ,'R'
83 ,C_PYEAR
84 ,'E'
85 ,C_PYEAR
86 ,C_PYEAR - 200)
87 AND C_PYEAR )
88 GROUP BY
89 BAL.PERIOD_NAME,
90 BAL.ACCOUNT_CODE;
91 IF PN <> P_START_PERIOD THEN
92 BEBAL := BBDR - BBCR + PNDR - PNCR;
93 RETURN (BEBAL);
94 ELSE
95 BEBAL := BBDR - BBCR;
96 RETURN (BEBAL);
97 END IF;
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 BEBAL := 0;
101 RETURN (BEBAL);
102 END;
103 RETURN NULL;
104 END C_CCIDFORMULA;
105
106 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
107 NLS_NO_DATA_FOUND VARCHAR2(45);
108 NLS_END_OF_REPORT VARCHAR2(45);
109 BEGIN
110 FND_MESSAGE.SET_NAME('JL'
111 ,'JL_ZZ_NO_DATA_FOUND');
112 NLS_NO_DATA_FOUND := SUBSTR(FND_MESSAGE.GET
113 ,1
114 ,35);
115 FND_MESSAGE.SET_NAME('JL'
116 ,'JL_ZZ_END_OF_REPORT');
117 NLS_END_OF_REPORT := SUBSTR(FND_MESSAGE.GET
118 ,1
119 ,35);
120 P_NO_DATA_FOUND := NLS_NO_DATA_FOUND;
121 P_END_OF_REPORT := NLS_END_OF_REPORT;
122 RETURN (TRUE);
123 RETURN NULL;
124 EXCEPTION
125 WHEN OTHERS THEN
126 RETURN (FALSE);
127 END GET_NLS_STRINGS;
128
129 FUNCTION STRUCT_NUM_P RETURN NUMBER IS
130 BEGIN
131 RETURN STRUCT_NUM;
132 END STRUCT_NUM_P;
133
134 FUNCTION SET_OF_BOOKS_NAME_P RETURN VARCHAR2 IS
135 BEGIN
136 RETURN SET_OF_BOOKS_NAME;
137 END SET_OF_BOOKS_NAME_P;
138
139 FUNCTION WHERE_FLEX_P RETURN VARCHAR2 IS
140 BEGIN
141 RETURN WHERE_FLEX;
142 END WHERE_FLEX_P;
143
144 FUNCTION C_PNUM_P RETURN NUMBER IS
145 BEGIN
146 RETURN C_PNUM;
147 END C_PNUM_P;
148
149 FUNCTION C_PYEAR_P RETURN NUMBER IS
150 BEGIN
151 RETURN C_PYEAR;
152 END C_PYEAR_P;
153
154 FUNCTION DISPLAY_P RETURN VARCHAR2 IS
155 BEGIN
156 RETURN DISPLAY;
157 END DISPLAY_P;
158
159 FUNCTION C_PNUM1_P RETURN NUMBER IS
160 BEGIN
161 RETURN C_PNUM1;
162 END C_PNUM1_P;
163
164 FUNCTION C_PYEAR1_P RETURN NUMBER IS
165 BEGIN
166 RETURN C_PYEAR1;
167 END C_PYEAR1_P;
168
169 FUNCTION C_COMPANY_NAME_P RETURN VARCHAR2 IS
170 BEGIN
171 RETURN C_COMPANY_NAME;
172 END C_COMPANY_NAME_P;
173
174 FUNCTION C_NIT_ID_P RETURN VARCHAR2 IS
175 BEGIN
176 RETURN C_NIT_ID;
177 END C_NIT_ID_P;
178
179 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
180 BEGIN
181 declare
182 t_set_of_books_id NUMBER;
183 t_chart_of_accounts_id NUMBER;
184 t_set_of_books_name VARCHAR2(30);
185 t_func_curr VARCHAR2(15);
186 t_period_name VARCHAR2(15);
187 t_errorbuffer VARCHAR2(132);
188 t_legal_entity_id NUMBER;
189
190
191 BEGIN
192
193 /*SRW.USER_EXIT('FND SRWINIT');*/
194 --
195 -- Here we fetch the chart_of_accounts_id for the
196 -- given set_of_books_id
197 --
198 t_set_of_books_id := P_set_of_books_id;
199 gl_info.gl_get_ledger_info (t_set_of_books_id,
200 t_chart_of_accounts_id,
201 t_set_of_books_name,
202 t_func_curr,
203 t_errorbuffer);
204
205
206 if (t_errorbuffer is not NULL) then
207 /*SRW.MESSAGE(0,t_errorbuffer);
208 raise SRW.PROGRAM_ABORT;*/null;
209 else
210 STRUCT_NUM := to_char(t_chart_of_accounts_id);
211 SET_OF_BOOKS_NAME := t_set_of_books_name;
212 P_CURRENCY := t_func_curr;
213
214 end if;
215 /* Get NLS strings */
216 IF (get_nls_strings <> TRUE) THEN -- Call report level PL/SQL function
217 NULL;
218 END IF;
219
220 /* Retrieving LOCATION_ID */
221 BEGIN
222 t_legal_entity_id := P_LEGAL_ENTITY_ID;
223 -- :C_LOCATION_ID := jg_zz_company_info.get_location_id;
224 EXCEPTION
225 when others then
226 /*srw.message(02, 'Error while retreiving legal entity information');*/null;
227 raise;
228 -- app_exception.raise_exception(null,null,null);
229 END;
230
231
232 /* Retrieving Company Information Attributes */
233 begin
234 select name,
235 registration_number
236 into C_COMPANY_NAME,
237 C_NIT_ID
238 from xle_firstparty_information_v
239 where legal_entity_id = t_legal_entity_id;
240 exception
241 when others then
242 /*srw.message(03, 'Failed to retrieve Company Information Attributes.');*/null;
243 raise;
244 end;
245
246 -- Here we format all the necessary
247 -- flexfield parts
248 --
249 /*SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
250 OUTPUT=":WHERE_FLEX"
251 APPL_SHORT_NAME="SQLGL"
252 MODE="WHERE"
253 DISPLAY="ALL"
254 NUM=":STRUCT_NUM"
255 TABLEALIAS="CC"
256 OPERATOR="BETWEEN"
257 OPERAND1=":P_ACCOUNT_START"
258 OPERAND2=":P_ACCOUNT_END"');*/
259
260
261
262 END;
263
264 DECLARE
265 p_num number;
266 p_year number;
267 BEGIN
268 SELECT glp.period_num,glp.period_year into p_num,p_year from gl_periods glp,gl_sets_of_books gls
269 WHERE glp.period_name = P_start_period
270 AND glp.period_set_name = gls.period_set_name
271 AND gls.set_of_books_id = P_set_of_books_id;
272
273 C_PNUM := p_num;
274 C_PYEAR := p_year;
275 exception
276 when NO_DATA_FOUND then
277 null;
278 end;
279 declare
280 p_num1 number;
281 p_year1 number;
282 begin
283 SELECT glp.period_num,glp.period_year into p_num1,p_year1 from gl_periods glp,gl_sets_of_books gls
284 WHERE glp.period_name = P_end_period
285 AND glp.period_set_name = gls.period_set_name
286 AND gls.set_of_books_id = P_set_of_books_id;
287
288 C_PNUM1 := p_num1;
289 C_PYEAR1 := p_year1;
290 exception
291 when NO_DATA_FOUND then
292 null;
293 END;
294
295 return (TRUE);
296
297 END BEFOREREPORT;
298
299
300 END JL_JLCOGLAN_XMLP_PKG;
301
302