[Home] [Help]
PACKAGE BODY: APPS.JL_JLCLGCAL_XMLP_PKG
Source
1 PACKAGE BODY JL_JLCLGCAL_XMLP_PKG AS
2 /* $Header: JLCLGCALB.pls 120.1 2007/12/25 16:43:00 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 RETURN (TRUE);
7 END AFTERREPORT;
8
9 FUNCTION C_DOC_NUMFORMULA(SOURCE IN VARCHAR2
10 ,REFERENCE_6 IN VARCHAR2
11 ,REFERENCE_4 IN VARCHAR2
12 ,REFERENCE_5 IN VARCHAR2
13 ,REFERENCE_8 IN VARCHAR2) RETURN VARCHAR2 IS
14 BEGIN
15 DECLARE
16 DOC VARCHAR2(240);
17 BEGIN
18 IF SOURCE = 'Payables' THEN
19 IF REFERENCE_6 = 'AP Payments' THEN
20 DOC := REFERENCE_4;
21 ELSE
22 DOC := REFERENCE_5;
23 END IF;
24 ELSIF SOURCE = 'Receivables' THEN
25 IF REFERENCE_8 in ('TRADE','MISC') THEN
26 DOC := REFERENCE_4;
27 ELSE
28 DOC := REFERENCE_5;
29 END IF;
30 END IF;
31 RETURN (DOC);
32 END;
33 RETURN NULL;
34 END C_DOC_NUMFORMULA;
35
36 FUNCTION C_DOCUMENTFORMULA(SOURCE_ID IN VARCHAR2
37 ,REFERENCE_10 IN VARCHAR2
38 ,REFERENCE_9 IN VARCHAR2) RETURN VARCHAR2 IS
39 BEGIN
40 DECLARE
41 TRANS_TYPE VARCHAR2(240);
42 TRANS_MEANING VARCHAR2(240);
43 TRANS_SOURCE VARCHAR2(25);
44 BEGIN
45 SELECT
46 NVL(USER_JE_SOURCE_NAME
47 ,'**********')
48 INTO TRANS_SOURCE
49 FROM
50 GL_JE_SOURCES_TL
51 WHERE JE_SOURCE_NAME = SOURCE_ID
52 AND LANGUAGE = 'US';
53 IF TRANS_SOURCE = 'Payables' THEN
54 TRANS_TYPE := NVL(REFERENCE_10
55 ,'EXPENSE');
56 ELSIF TRANS_SOURCE = 'Receivables' THEN
57 TRANS_TYPE := REFERENCE_9;
58 END IF;
59 SELECT
60 DESCRIPTION
61 INTO TRANS_MEANING
62 FROM
63 GL_LOOKUPS
64 WHERE LOOKUP_TYPE = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
65 AND LOOKUP_CODE = TRANS_TYPE;
66 RETURN (TRANS_MEANING);
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 TRANS_MEANING := NULL;
70 RETURN (TRANS_MEANING);
71 END;
72 RETURN NULL;
73 END C_DOCUMENTFORMULA;
74
75 FUNCTION START_EFFECTIVE_PERIOD_NUMFORM RETURN NUMBER IS
76 BEGIN
77 DECLARE
78 EFF_PERIOD_NUM NUMBER;
79 ERRBUF VARCHAR2(132);
80 ERRBUF2 VARCHAR2(132);
81 BEGIN
82 SELECT
83 EFFECTIVE_PERIOD_NUM
84 INTO EFF_PERIOD_NUM
85 FROM
86 GL_PERIOD_STATUSES
87 WHERE APPLICATION_ID = 101
88 AND SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
89 AND UPPER(PERIOD_NAME) = UPPER(P_START_PERIOD);
90 RETURN (EFF_PERIOD_NUM);
91 END;
92 RETURN NULL;
93 END START_EFFECTIVE_PERIOD_NUMFORM;
94
95 FUNCTION END_EFFECTIVE_PERIOD_NUMFORMUL RETURN NUMBER IS
96 BEGIN
97 DECLARE
98 EFF_PERIOD_NUM NUMBER;
99 ERRBUF VARCHAR2(132);
100 ERRBUF2 VARCHAR2(132);
101 BEGIN
102 SELECT
103 EFFECTIVE_PERIOD_NUM
104 INTO EFF_PERIOD_NUM
105 FROM
106 GL_PERIOD_STATUSES
107 WHERE APPLICATION_ID = 101
108 AND SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
109 AND UPPER(PERIOD_NAME) = UPPER(P_END_PERIOD);
110 RETURN (EFF_PERIOD_NUM);
111 END;
112 RETURN NULL;
113 END END_EFFECTIVE_PERIOD_NUMFORMUL;
114
115 FUNCTION CF_1FORMULA(BEGIN_BAL_DR IN NUMBER
116 ,BEGIN_BAL_CR IN NUMBER) RETURN NUMBER IS
117 BEGIN
118 RETURN (NVL(BEGIN_BAL_DR
119 ,0) - NVL(BEGIN_BAL_CR
120 ,0));
121 END CF_1FORMULA;
122
123 FUNCTION C_END_BALFORMULA(END_BAL_DR IN NUMBER
124 ,END_BAL_CR IN NUMBER) RETURN NUMBER IS
125 BEGIN
126 RETURN (NVL(END_BAL_DR
127 ,0) - NVL(END_BAL_CR
128 ,0));
129 END C_END_BALFORMULA;
130
131 FUNCTION GET_DYNAMIC_WHERE RETURN BOOLEAN IS
132 L_MODEL_ID NUMBER := P_MODEL_ID;
133 L_DELIMITER VARCHAR2(10) := DELIMITER;
134 L_ACCT_LOW VARCHAR2(2000);
135 L_ACCT_HIGH VARCHAR2(2000);
136 L_DYNAMIC_WHERE1 VARCHAR2(2000);
137 L_DYNAMIC_WHERE2 VARCHAR2(10000);
138 CURSOR C_RANGES(P_MODEL_ID IN NUMBER) IS
139 SELECT
140 SEGMENT1_LOW || L_DELIMITER || SEGMENT2_LOW || L_DELIMITER || SEGMENT3_LOW || L_DELIMITER ||
141 SEGMENT4_LOW || L_DELIMITER || SEGMENT5_LOW || L_DELIMITER || SEGMENT6_LOW || L_DELIMITER ||
142 SEGMENT7_LOW || L_DELIMITER || SEGMENT8_LOW || L_DELIMITER || SEGMENT9_LOW || L_DELIMITER ||
143 SEGMENT10_LOW || L_DELIMITER || SEGMENT11_LOW || L_DELIMITER || SEGMENT12_LOW || L_DELIMITER ||
144 SEGMENT13_LOW || L_DELIMITER || SEGMENT14_LOW || L_DELIMITER || SEGMENT15_LOW || L_DELIMITER ||
145 SEGMENT16_LOW || L_DELIMITER || SEGMENT17_LOW || L_DELIMITER || SEGMENT18_LOW || L_DELIMITER ||
146 SEGMENT19_LOW || L_DELIMITER || SEGMENT20_LOW || L_DELIMITER || SEGMENT21_LOW || L_DELIMITER ||
147 SEGMENT22_LOW || L_DELIMITER || SEGMENT23_LOW || L_DELIMITER || SEGMENT24_LOW || L_DELIMITER ||
148 SEGMENT25_LOW || L_DELIMITER || SEGMENT26_LOW || L_DELIMITER || SEGMENT27_LOW || L_DELIMITER ||
149 SEGMENT28_LOW || L_DELIMITER || SEGMENT29_LOW || L_DELIMITER || SEGMENT30_LOW ACCOUNT_LOW,
150 SEGMENT1_HIGH || L_DELIMITER || SEGMENT2_HIGH || L_DELIMITER || SEGMENT3_HIGH || L_DELIMITER ||
151 SEGMENT4_HIGH || L_DELIMITER || SEGMENT5_HIGH || L_DELIMITER || SEGMENT6_HIGH || L_DELIMITER ||
152 SEGMENT7_HIGH || L_DELIMITER || SEGMENT8_HIGH || L_DELIMITER || SEGMENT9_HIGH || L_DELIMITER ||
153 SEGMENT10_HIGH || L_DELIMITER || SEGMENT11_HIGH || L_DELIMITER || SEGMENT12_HIGH || L_DELIMITER ||
154 SEGMENT13_HIGH || L_DELIMITER || SEGMENT14_HIGH || L_DELIMITER || SEGMENT15_HIGH || L_DELIMITER ||
155 SEGMENT16_HIGH || L_DELIMITER || SEGMENT17_HIGH || L_DELIMITER || SEGMENT18_HIGH || L_DELIMITER ||
156 SEGMENT19_HIGH || L_DELIMITER || SEGMENT20_HIGH || L_DELIMITER || SEGMENT21_HIGH || L_DELIMITER ||
157 SEGMENT22_HIGH || L_DELIMITER || SEGMENT23_HIGH || L_DELIMITER || SEGMENT24_HIGH || L_DELIMITER ||
158 SEGMENT25_HIGH || L_DELIMITER || SEGMENT26_HIGH || L_DELIMITER || SEGMENT27_HIGH || L_DELIMITER ||
159 SEGMENT28_HIGH || L_DELIMITER || SEGMENT29_HIGH || L_DELIMITER || SEGMENT30_HIGH ACCOUNT_HIGH
160 FROM
161 JL_ZZ_GL_AXI_MODEL_RANGES AMR
162 WHERE AMR.MODEL_ID = P_MODEL_ID;
163 BEGIN
164 OPEN C_RANGES(L_MODEL_ID);
165 LOOP
166 FETCH C_RANGES
167 INTO L_ACCT_LOW,L_ACCT_HIGH;
168 EXIT WHEN C_RANGES%NOTFOUND;
169 L_DYNAMIC_WHERE1 := JL_ZZ_GL_SEGS_PKG.GET_BETWEEN(STRUCT_NUM
170 ,'cc'
171 ,L_ACCT_LOW
172 ,L_ACCT_HIGH
173 ,'ALL');
174 L_DYNAMIC_WHERE1 := '(' || L_DYNAMIC_WHERE1 || ')';
175 L_DYNAMIC_WHERE2 := L_DYNAMIC_WHERE2 || L_DYNAMIC_WHERE1 || ' OR ';
176 END LOOP;
177 CLOSE C_RANGES;
178 L_DYNAMIC_WHERE2 := RTRIM(L_DYNAMIC_WHERE2
179 ,' OR ');
180 P_DYNAMIC_WHERE := 'AND (' || L_DYNAMIC_WHERE2 || ')';
181 RETURN (TRUE);
182 RETURN NULL;
183 EXCEPTION
184 WHEN OTHERS THEN
185 RETURN (FALSE);
186 END GET_DYNAMIC_WHERE;
187
188 FUNCTION STRUCT_NUM_P RETURN VARCHAR2 IS
189 BEGIN
190 RETURN STRUCT_NUM;
191 END STRUCT_NUM_P;
192
193 FUNCTION C_ACCOUNT_START_P RETURN VARCHAR2 IS
194 BEGIN
195 RETURN C_ACCOUNT_START;
196 END C_ACCOUNT_START_P;
197
198 FUNCTION C_ACCOUNT_END_P RETURN VARCHAR2 IS
199 BEGIN
200 RETURN C_ACCOUNT_END;
201 END C_ACCOUNT_END_P;
202
203 FUNCTION WHERE_FLEX_P RETURN VARCHAR2 IS
204 BEGIN
205 RETURN WHERE_FLEX;
206 END WHERE_FLEX_P;
207
208 FUNCTION C_ACCT_P RETURN VARCHAR2 IS
209 BEGIN
210 RETURN C_ACCT;
211 END C_ACCT_P;
212
213 FUNCTION SET_OF_BOOKS_NAME_P RETURN VARCHAR2 IS
214 BEGIN
215 RETURN SET_OF_BOOKS_NAME;
216 END SET_OF_BOOKS_NAME_P;
217
218 FUNCTION CP_COMP_NAME_P RETURN VARCHAR2 IS
219 BEGIN
220 RETURN CP_COMP_NAME;
221 END CP_COMP_NAME_P;
222
223 FUNCTION CP_COMP_ADDRESS1_P RETURN VARCHAR2 IS
224 BEGIN
225 RETURN CP_COMP_ADDRESS1;
226 END CP_COMP_ADDRESS1_P;
227
228 FUNCTION CP_COMP_TAXPAYER_ID_P RETURN VARCHAR2 IS
229 BEGIN
230 RETURN CP_COMP_TAXPAYER_ID;
231 END CP_COMP_TAXPAYER_ID_P;
232
233 FUNCTION CP_COMP_ESTAB_TYPE_P RETURN VARCHAR2 IS
234 BEGIN
235 RETURN CP_COMP_ESTAB_TYPE;
236 END CP_COMP_ESTAB_TYPE_P;
237
238 FUNCTION CP_COMP_ADDRESS2_P RETURN VARCHAR2 IS
239 BEGIN
240 RETURN CP_COMP_ADDRESS2;
241 END CP_COMP_ADDRESS2_P;
242
243 FUNCTION DELIMITER_P RETURN VARCHAR2 IS
244 BEGIN
245 RETURN DELIMITER;
246 END DELIMITER_P;
247
248 FUNCTION CP_DATE4_FORMAT_P RETURN VARCHAR2 IS
249 BEGIN
250 RETURN CP_DATE4_FORMAT;
251 END CP_DATE4_FORMAT_P;
252
253 FUNCTION CP_MODEL_NAME_P RETURN VARCHAR2 IS
254 BEGIN
255 RETURN CP_MODEL_NAME;
256 END CP_MODEL_NAME_P;
257
258 function BeforeReport return boolean is
259 begin
260
261 /*--**
262 srw.user_exit('FND SRWINIT');
263 srw.user_exit('FND GETPROFILE NAME="LANGUAGE"
264 FIELD=":SET_OF_BOOKS_NAME"');
265 gl_message.set_language(:SET_OF_BOOKS_NAME);
266 */--**
267 /* Get the legder info*/
268
269 declare
270 coaid NUMBER;
271 ledname VARCHAR2(30);
272 functcurr VARCHAR2(15);
273 errbuf VARCHAR2(132);
274 errbuf2 VARCHAR2(132);
275 begin
276 gl_info.gl_get_ledger_info(P_SET_OF_BOOKS_ID,
277 coaid,
278 ledname,
279 functcurr,
280 errbuf);
281
282 if (errbuf is not null) then
283
284 /* Error in PL/SQL routine
285 gl_get_bud_enc_name -
286 */
287
288 errbuf2 := gl_message.get_message(
289 'GL_PLL_ROUTINE_ERROR', 'N',
290 'ROUTINE','gl_get_ledger_info'
291 );
292 /* srw.message('00', errbuf2);
293
294 srw.message('00', errbuf);
295 raise srw.program_abort; */
296 RAISE_APPLICATION_ERROR(-20101,null);
297 end if;
298 -- :SET_OF_BOOKS_NAME := ledname;
299 end;
300
301 --
302 -- Retrieve Company information
303 --
304
305 -- P_COMPANY_ID := JG_ZZ_COMPANY_INFO.GET_LOCATION_ID ;
306 -- Taking LE as parameter
307 P_Company_ID := P_Legal_Entity_ID;
308
309 Begin
310
311 SELECT name comp_name,
312 registration_number,
313 activity_code,
314 ltrim(address_line_1 ||' '|| address_line_2),
315 ltrim(address_line_3 ||' '|| town_or_city)
316 INTO CP_COMP_NAME,
317 CP_COMP_TAXPAYER_ID,
318 CP_COMP_ESTAB_TYPE,
319 CP_COMP_ADDRESS1,
320 CP_COMP_ADDRESS2
321 FROM XLE_FIRSTPARTY_INFORMATION_V
322 WHERE Legal_entity_id = P_COMPANY_ID
323 AND LEGISLATIVE_CAT_CODE = 'INCOME_TAX';
324
325 Exception
326 WHEN NO_DATA_FOUND THEN
327 NULL ;
328 WHEN OTHERS THEN
329 /* srw.message('2', 'Failed to retrieve Company information.'); */ null;
330
331 End ;
332 BEGIN
333 SELECT concatenated_segment_delimiter
334 INTO delimiter
335 FROM fnd_id_flex_structures
336 WHERE application_id = 101
337 AND id_flex_code = 'GL#'
338 AND id_flex_num = P_CHART_OF_ACCTS_ID
339 AND ROWNUM < 2;
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 NULL ;
343 WHEN OTHERS THEN
344 /* srw.message('3', 'Failed to retrieve delemiter.'); */ null;
345 END;
346
347 --
348 -- Get Dateformat
349 --
350 /* srw.reference(:CP_DATE4_FORMAT);
351 srw.user_exit('FND DATE4FORMAT
352 RESULT=":CP_DATE4_FORMAT"'); */
353 --
354 -- get Model Name
355 --
356 BEGIN
357 SELECT name
358 INTO CP_MODEL_NAME
359 FROM jl_zz_gl_axi_models
360 WHERE model_id = P_MODEL_ID
361 AND set_of_books_id = P_SET_OF_BOOKS_ID;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 NULL ;
365 WHEN OTHERS THEN
366 /*srw.message('4', 'Failed to retrieve model name.');*/
367 NULL;
368 END;
369
370 -- Bug 2674960
371
372 STRUCT_NUM := P_CHART_OF_ACCTS_ID;
373
374 --
375 -- Dynamic where
376 --
377 if (get_dynamic_where <> TRUE) then
378 RAISE_APPLICATION_ERROR(-20101,null);
379 /*else
380 srw.message('999',:p_dynamic_where);*/
381 end if;
382
383 --srw.break;
384
385 /*srw.reference(:STRUCT_NUM);
386 srw.user_exit('FND FLEXSQL
387 CODE="GL#"
388 NUM=":STRUCT_NUM"
389 APPL_SHORT_NAME="SQLGL"
390 OUTPUT=":P_FLEXDATA" TABLEALIAS="CC"
391 MODE="SELECT" DISPLAY="ALL"'); */
392 /*
393 srw.reference(:STRUCT_NUM);
394 srw.user_exit('FND FLEXSQL
395 CODE="GL#"
396 NUM=":STRUCT_NUM"
397 APPL_SHORT_NAME="SQLGL"
398 OUTPUT=":WHERE_FLEX" TABLEALIAS="CC"
399 MODE="WHERE" DISPLAY="ALL"
400 OPERATOR="BETWEEN"
401 OPERAND1=":C_AC_LOW"
402 OPERAND2=":C_AC_HIGH"');
403 */
404 /*
405 srw.reference(:STRUCT_NUM);
406 srw.user_exit('FND FLEXSQL
407 CODE="GL#"
408 NUM=":STRUCT_NUM"
409 APPL_SHORT_NAME="SQLGL"
410 OUTPUT=":WHERE_FLEX" TABLEALIAS="CC"
411 MODE="WHERE" DISPLAY="ALL"
412 OPERATOR="BETWEEN"
413 OPERAND1=":P_MIN_FLEX"
414 OPERAND2=":P_MAX_FLEX"');
415 */
416
417 /* Generate the selected ORDER BY */
418 /*--**
419 if (:P_ORDER_TYPE = 'A') then
420 :ORDER_BY := :ORDERBY_ACCT || ', ' ||
421 :ORDERBY_ACCT2 || ', ' ||
422 :ORDERBY_BAL || ', ' ||
423 :ORDERBY_BAL2 || ', ' ||
424 :ORDERBY_ALL || ', ' ||
425 'src.user_je_source_name, ' ||
426 'cat.user_je_category_name, ' ||
427 'jeb.name, jeh.name, jel.je_line_num';
428 elsif (:P_ORDER_TYPE = 'B') then
429 :ORDER_BY := :ORDERBY_BAL || ', ' ||
430 :ORDERBY_BAL2 || ', ' ||
431 :ORDERBY_ACCT || ', ' ||
432 :ORDERBY_ACCT2 || ', ' ||
433 :ORDERBY_ALL || ', ' ||
434 'src.user_je_source_name, ' ||
435 'cat.user_je_category_name, ' ||
436 'jeb.name, jeh.name, jel.je_line_num';
437 else
438 :ORDER_BY := 'src.user_je_source_name, ' ||
439 'cat.user_je_category_name, ' ||
440 'jeb.name, jeh.name, ' ||
441 :ORDERBY_BAL || ', ' ||
442 :ORDERBY_BAL2 || ', ' ||
443 :ORDERBY_ACCT || ', ' ||
444 :ORDERBY_ACCT2 || ', ' ||
445 :ORDERBY_ALL || ', ' ||
446 'jel.je_line_num';
447 end if;
448
449 return (TRUE);
450 end;
451 */--**
452 return (TRUE);
453 end;
454
455 END JL_JLCLGCAL_XMLP_PKG;
456
457
458