eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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