1 PACKAGE BODY GL_GLWACCTR_XMLP_PKG AS
2 /* $Header: GLWACCTRB.pls 120.2 2008/01/09 07:28:17 vijranga noship $ */
3
4 UNKNOWN_USER_EXIT EXCEPTION;
5
6 USER_EXIT_FAILURE EXCEPTION;
7
8 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
9
10 T_ERRORBUFFER VARCHAR2(132);
11 AMOUNT_WHERE VARCHAR2(500);
12 AMOUNT_WHERE2 VARCHAR2(500);
13 JOURNAL_FROM VARCHAR2(500);
14 JOURNAL_WHERE VARCHAR2(1000);
15 AFF_WHERE VARCHAR2(500);
16 AFF_WHERE2 VARCHAR2(4000);
17 L_LEDGER_TYPE VARCHAR2(100);
18 L_LEDGER_CURR VARCHAR2(100);
19 L_LEDGER_FROM VARCHAR2(1000);
20 L_LEDGER_WHERE VARCHAR2(2000);
21 L_DAS_WHERE VARCHAR2(2000);
22 ERR_IN_REP EXCEPTION;
23
24 BEGIN
25
26 ---------------------------------------------------------
27 -- INITIALIZE SRW.USER_EXIT
28 ---------------------------------------------------------
29
30 BEGIN
31 -- SRW.USER_EXIT('FND SRWINIT');
32
33 /*EXCEPTION
34 WHEN SRW.UNKNOWN_USER_EXIT THEN
35 SRW.MESSAGE(01,'FND SRWINIT USER EXIT IS UNKNOWN.');
36 RAISE;
37 WHEN SRW.USER_EXIT_FAILURE THEN
38 SRW.MESSAGE(02,'FND SRWINIT USER EXIT IS FAILED.');
39 RAISE;*/NULL;
40 END;
41
42 SELECT
43 FND_DATE.CANONICAL_TO_DATE(P_START_DATE),
44 FND_DATE.CANONICAL_TO_DATE(P_END_DATE),
45 FND_NUMBER.CANONICAL_TO_NUMBER(P_LEDGER_ID),
46 FND_DATE.CANONICAL_TO_DATE(P_START_UPDATE_DATE),
47 FND_DATE.CANONICAL_TO_DATE(P_END_UPDATE_DATE)
48 -- FND_NUMBER.CANONICAL_TO_NUMBER(:P_DOC_ID),
49 -- FND_NUMBER.CANONICAL_TO_NUMBER(:P_DOC_VALUE),
50 -- FND_NUMBER.CANONICAL_TO_NUMBER(:P_AMOUNT_FROM),
51 -- FND_NUMBER.CANONICAL_TO_NUMBER(:P_AMOUNT_TO),
52 -- FND_NUMBER.CANONICAL_TO_NUMBER(:P_SUB_DOC_ID),
53 -- FND_NUMBER.CANONICAL_TO_NUMBER(:P_SUB_DOC_VALUE)
54 INTO
55 H_START_DATE,
56 H_END_DATE,
57 H_SET_OF_BOOKS_ID,
58 H_START_UPDATE_DATE,
59 H_END_UPDATE_DATE
60 -- :H_DOC_ID,
61 -- :H_DOC_VALUE,
62 -- :H_AMOUNT_FROM,
63 -- :H_AMOUNT_TO,
64 -- :H_SUB_DOC_ID,
65 -- :H_SUB_DOC_VALUE
66 FROM DUAL;
67
68 /* GET ACCESS SET INFO */
69 BEGIN
70 SELECT NAME , CHART_OF_ACCOUNTS_ID
71 INTO ACCESS_SET_NAME, CHART_OF_ACCOUNTS_ID
72 FROM GL_ACCESS_SETS
73 WHERE ACCESS_SET_ID = P_ACCESS_SET_ID;
74
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 T_ERRORBUFFER := GL_MESSAGE.GET_MESSAGE('GL_PLL_INVALID_DATA_ACCESS_SET', 'Y',
78 'DASID', TO_CHAR(P_ACCESS_SET_ID));
79 -- SRW.MESSAGE('00', T_ERRORBUFFER);
80 -- RAISE SRW.PROGRAM_ABORT;
81
82 WHEN OTHERS THEN
83 T_ERRORBUFFER := SQLERRM;
84 -- SRW.MESSAGE('00', T_ERRORBUFFER);
85 -- RAISE SRW.PROGRAM_ABORT;
86 END;
87
88 -- BUILD DATA ACCESS SECURITY WHERE CLAUSE
89 P_DAS_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
90 P_ACCESS_SET_ID,
91 'R', -- FOR READ ACCESS
92 'LEDGER_COLUMN',
93 'LEDGER_ID',
94 'GLH', -- TABLE ALIAS FOR GL_JE_HEADERS
95 'SEG_COLUMN',
96 NULL,
97 'CC', -- TABLE ALIAS FOR GL_CODE_COMBINATIONS.
98 NULL);
99
100 L_DAS_WHERE := GL_ACCESS_SET_SECURITY_PKG.GET_SECURITY_CLAUSE(
101 P_ACCESS_SET_ID,
102 'R', -- FOR READ ACCESS
103 'LEDGER_COLUMN',
104 'LEDGER_ID',
105 'GLH2', -- TABLE ALIAS FOR GL_JE_HEADERS
106 'SEG_COLUMN',
107 NULL,
108 'GCC2', -- TABLE ALIAS FOR GL_CODE_COMBINATIONS.
109 NULL);
110
111 IF (P_DAS_WHERE IS NOT NULL) THEN
112 P_DAS_WHERE := ' AND ' || P_DAS_WHERE;
113 L_DAS_WHERE := ' AND ' || L_DAS_WHERE;
114 END IF;
115
116 --##############
117
118 P_LEDGER_FROM := ' ';
119 P_LEDGER_WHERE := ' ';
120 L_LEDGER_FROM := ' ';
121 L_LEDGER_WHERE := ' ';
122 P_JOURNAL_WHERE_CLAUSE := ' ';
123 /* GET THE LEDGER INFO WHEN A LEDGER/LEDGER SET IS SELECTED BY THE USER*/
124
125 IF P_LEDGER_ID IS NOT NULL THEN
126
127
128 /* GET INFORMATION OF LEDGER PARAMETER. */
129
130 BEGIN
131 SELECT CURRENCY_CODE, OBJECT_TYPE_CODE
132 INTO L_LEDGER_CURR,
133 L_LEDGER_TYPE
134 FROM GL_LEDGERS
135 WHERE LEDGER_ID = P_LEDGER_ID;
136 EXCEPTION
137 WHEN OTHERS THEN
138 T_ERRORBUFFER := SQLERRM;
139 -- SRW.MESSAGE('00', T_ERRORBUFFER);
140 -- RAISE SRW.PROGRAM_ABORT;
141 END;
142
143 IF (L_LEDGER_TYPE = 'S') THEN -- IT IS A LEDGER SET
144 L_LEDGER_FROM := ', GL_LEDGER_SET_ASSIGNMENTS LS1';
145 P_LEDGER_FROM := ', GL_LEDGER_SET_ASSIGNMENTS LS';
146 L_LEDGER_WHERE := ' AND LS1.LEDGER_SET_ID = ' || TO_CHAR(P_LEDGER_ID) || ' AND ' ||
147 'LGR1.LEDGER_ID = LS1.LEDGER_ID';
148 P_LEDGER_WHERE := ' AND LS.LEDGER_SET_ID = ' || TO_CHAR(P_LEDGER_ID) || ' AND ' ||
149 'LGR.LEDGER_ID = LS.LEDGER_ID';
150 ELSE
151 P_LEDGER_FROM := ' ';
152 P_LEDGER_WHERE := ' AND LGR.LEDGER_ID = ' || TO_CHAR(P_LEDGER_ID);
153
154 L_LEDGER_FROM := '';
155 L_LEDGER_WHERE := ' AND LGR1.LEDGER_ID = ' || TO_CHAR(P_LEDGER_ID);
156 END IF;
157
158 END IF; --IF :P_LEDGER_ID IS NOT NULL
159
160 --###############
161
162
163 IF P_STATUS IS NOT NULL THEN
164
165 IF (P_STATUS = 'E') THEN
166 P_POSTING_STATUS := ' AND '|| ' GLB.STATUS IN ' ||
167 '(SELECT LOOKUP_CODE FROM GL_LOOKUPS ' ||
168 'WHERE LOOKUP_TYPE = ''MJE_BATCH_STATUS'' '||
169 'AND LOOKUP_CODE NOT IN (''S'', ''I'', ''U'', ''P'')) ';
170 P_HEADER_POSTING_STATUS :=
171 ' AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+)
172 AND GLL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID(+) ';
173
174 ELSE
175 P_POSTING_STATUS := 'AND GLB.STATUS = '''||P_STATUS||'''';
176 P_HEADER_POSTING_STATUS :=
177 'AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
178 AND GLL.LEDGER_ID = LGR.LEDGER_ID
179 AND GLL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID';
180
181 END IF;
182
183 ELSE
184 P_HEADER_POSTING_STATUS :=
185 'AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
186 AND GLL.LEDGER_ID = LGR.LEDGER_ID
187 AND GLL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID';
188 P_POSTING_STATUS := ' ';
189 END IF;
190
191 IF (P_ACTUAL_FLAG IS NOT NULL) THEN
192
193 P_ACTUAL_TYPE_WHERE := ' AND GLH.ACTUAL_FLAG = '''||P_ACTUAL_FLAG||'''';
194 ELSE
195 P_ACTUAL_TYPE_WHERE := ' ';
196
197 END IF;
198
199
200 -----------------------------------------------------------
201 -- FLEXFIELDS
202 -- FILL OUT ACCT_SEGMENT_NAME, BAL_SEGMENT_NAME, SECONDARY_SEG
203 -----------------------------------------------------------
204
205 BEGIN
206
207 -- SRW.REFERENCE(:CHART_OF_ACCOUNTS_ID);
208
209 /*SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
210 NUM=":CHART_OF_ACCOUNTS_ID"
211 APPL_SHORT_NAME="SQLGL"
212 OUTPUT=":SELECT_ACCOUNT"
213 MODE="SELECT"
214 DISPLAY="ALL"
215 TABLEALIAS="CC"');
216
217 SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
218 NUM=":CHART_OF_ACCOUNTS_ID"
219 APPL_SHORT_NAME="SQLGL"
220 OUTPUT=":SELECT_ACCT_SEGMENT"
221 MODE="SELECT"
222 DISPLAY="GL_ACCOUNT"
223 IDISPLAY="GL_ACCOUNT"
224 TABLEALIAS="CC"');
225
226 SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
227 NUM=":CHART_OF_ACCOUNTS_ID"
228 APPL_SHORT_NAME="SQLGL"
229 OUTPUT=":SELECT_BAL_SEGMENT"
230 MODE="SELECT"
231 DISPLAY="GL_BALANCING"
232 TABLEALIAS="CC"');
233
234 SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
235 NUM=":CHART_OF_ACCOUNTS_ID"
236 APPL_SHORT_NAME="SQLGL"
237 OUTPUT=":SELECT_COST_CTR_SEGMENT"
238 MODE="SELECT"
239 DISPLAY="FA_COST_CTR"
240 IDISPLAY="FA_COST_CTR"
241 TABLEALIAS="CC"');*/
242
243
244 IF (P_SEC_SEG_NUM IS NOT NULL) THEN
245
246
247 /* SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
248 NUM=":CHART_OF_ACCOUNTS_ID"
249 APPL_SHORT_NAME="SQLGL"
250 OUTPUT=":SELECT_SECONDARY_SEGMENT"
251 MODE="SELECT"
252 DISPLAY="GL_SECONDARY_TRACKING"
253 TABLEALIAS="CC"'); */
254
255 SELECT 'CC.'||APPLICATION_COLUMN_NAME , FORM_LEFT_PROMPT
256 INTO SELECT_SECONDARY_SEGMENT, PARAM_SEC_SEG_NAME
257 FROM FND_ID_FLEX_SEGMENTS_VL S
258 WHERE S.ID_FLEX_CODE = 'GL#'
259 AND S.ID_FLEX_NUM = CHART_OF_ACCOUNTS_ID
260 AND S.ENABLED_FLAG = 'Y'
261 AND S.SEGMENT_NUM = P_SEC_SEG_NUM;
262
263
264
265 END IF;
266
267
268
269 IF (P_BAL_SEG_VAL IS NOT NULL) THEN
270 /*SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
271 NUM=":CHART_OF_ACCOUNTS_ID"
272 APPL_SHORT_NAME="SQLGL"
273 OUTPUT=":P_BAL_WHERE"
274 MODE="WHERE"
275 DISPLAY="GL_BALANCING"
276 OPERATOR = "="
277 OPERAND1=":P_BAL_SEG_VAL"
278 TABLEALIAS="CC"');*/
279
280 P_BAL_WHERE := ' AND '||P_BAL_WHERE;
281
282
283 -- ' AND '||:P_BAL_WHERE|| ' = '''||:P_BAL_SEG_VAL||'''';
284 END IF;
285
286
287 -- IF :P_SEC_SEG_NUM IS NULL THEN
288 -- :P_SEC_SEGMENT_WHERE:= 'A';
289 -- END IF;
290
291
292 /* THE FOLLOWING IS NOT USED ANY WHERE IN THE UNIT TESTING
293 SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
294 NUM=":CHART_OF_ACCOUNTS_ID"
295 APPL_SHORT_NAME="SQLGL"
296 OUTPUT=":P_ACC_SEGMENT_SELECT"
297 MODE="SELECT"
298 DISPLAY="ALL"
299 TABLEALIAS="GCC"');
300 */
301
302 IF P_CURRENCY IS NOT NULL THEN
303 IF P_CURRENCY = 'STAT' THEN
304 P_CURRENCY_WHERE := 'AND GLH.CURRENCY_CODE = ''STAT'' ';
305 ELSE
306 P_CURRENCY_WHERE := 'AND GLH.CURRENCY_CODE <> ''STAT'' ';
307 END IF;
308
309 END IF;
310
311 -- SOURCE PARAMETER
312 IF P_SOURCE IS NOT NULL THEN
313 P_SOURCE_WHERE := 'AND GLH.JE_SOURCE = '''||P_SOURCE||'''';
314 ELSE
315 P_SOURCE_WHERE := ' ';
316 END IF;
317
318 -- CATEGORY PARAMATER
319 IF P_CATEGORY IS NOT NULL THEN
320 P_CATEGORY_WHERE := 'AND GLH.JE_CATEGORY = '''||P_CATEGORY||'''';
321 ELSE
322 P_CATEGORY_WHERE := ' ';
323 END IF;
324
325 -- BATCH NAME PARAMETER
326 IF P_BATCH_NAME IS NOT NULL THEN
327 P_BATCH_WHERE := 'AND GLB.NAME = '''||P_BATCH_NAME||'''';
328 ELSE
329 P_BATCH_WHERE := ' ';
330 END IF;
331
332 IF P_DOC_ID IS NOT NULL THEN
333 P_DOC_ID_WHERE := 'AND GLH.DOC_SEQUENCE_ID = '''||P_DOC_ID||'''';
334 ELSE
335 P_DOC_ID_WHERE := ' ';
336 END IF;
337
338 P_DOC_VAL_WHERE := ' ';
339 IF ((P_DOC_VALUE IS NOT NULL)
340 OR (P_START_DOC_VALUE IS NOT NULL)
341 OR (P_END_DOC_VALUE IS NOT NULL)) THEN
342
343
344 IF P_DOC_VALUE IS NOT NULL THEN
345 P_DOC_VAL_WHERE := ' AND GLH.DOC_SEQUENCE_VALUE = '||P_DOC_VALUE;
346 ELSE
347 P_DOC_VAL_WHERE := ' ';
348 END IF;
349
350 IF (P_START_DOC_VALUE IS NULL)
351 AND (P_END_DOC_VALUE IS NOT NULL) THEN
352 P_DOC_VAL_WHERE := P_DOC_VAL_WHERE || ' AND GLH.DOC_SEQUENCE_VALUE <= '''||P_END_DOC_VALUE ||'''';
353
354 ELSIF (P_START_DOC_VALUE IS NOT NULL)
355 AND (P_END_DOC_VALUE IS NULL) THEN
356 P_DOC_VAL_WHERE := P_DOC_VAL_WHERE || ' AND GLH.DOC_SEQUENCE_VALUE >= '''||P_START_DOC_VALUE||'''';
357
358 ELSIF (P_START_DOC_VALUE IS NOT NULL)
359 AND (P_END_DOC_VALUE IS NOT NULL) THEN
360 P_DOC_VAL_WHERE := P_DOC_VAL_WHERE ||' AND GLH.DOC_SEQUENCE_VALUE BETWEEN '''
361 ||P_START_DOC_VALUE||''' AND '''||P_END_DOC_VALUE||'''';
362 END IF;
363
364 END IF;
365
366
367
368
369 -- SUBLEDGER DOC SEQ ID AND VALUES.
370
371 IF P_SUB_DOC_ID IS NOT NULL THEN
372 P_SUB_DOC_ID_WHERE := ' AND GLL.SUBLEDGER_DOC_SEQUENCE_ID = '||P_SUB_DOC_ID;
373 ELSE
374 P_SUB_DOC_ID_WHERE := ' ';
375 END IF;
376
377 IF P_SUB_DOC_VALUE IS NOT NULL THEN
378 P_SUB_DOC_VAL_WHERE := ' AND GLL.SUBLEDGER_DOC_SEQUENCE_VALUE = '''||P_SUB_DOC_VALUE||'''';
379 ELSE
380 P_SUB_DOC_VAL_WHERE := ' ';
381 END IF;
382
383
384 -- DO NOT SELECT ZERO JOURANL AMOUNTS LINE AMOUNTS.
385
386 IF NVL(P_STATUS, 'P') <> 'E' THEN
387 P_NOT_ZERO_LINE_WHERE:= ' AND ((NVL(GLL.ACCOUNTED_DR, 0) <> 0) OR (NVL(GLL.ACCOUNTED_CR, 0) <> 0)) ';
388 ELSE
389 P_NOT_ZERO_LINE_WHERE := ' ';
390 END IF;
391
392 -- CONTRA ACCOUNT
393
394 P_CONTRA_ACCOUNT_WHERE := ' ';
395 IF P_CONTRA_ACCT IS NOT NULL THEN
396 --SRW.REFERENCE(:CHART_OF_ACCOUNTS_ID);
397 /*SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
398 NUM=":CHART_OF_ACCOUNTS_ID"
399 APPL_SHORT_NAME="SQLGL"
400 OUTPUT=":P_ACC_SEGMENT_WHERE"
401 MODE="WHERE"
402 DISPLAY="ALL"
403 OPERATOR="BETWEEN"
404 OPERAND1=:P_AFF_FROM
405 OPERAND2=:P_AFF_TO
406 TABLEALIAS="CC"'); */
407
408 IF (P_ACC_SEGMENT_WHERE IS NOT NULL) THEN
409 P_ACC_SEGMENT_WHERE := ' AND ' ||P_ACC_SEGMENT_WHERE;
410 END IF;
411
412 --WHEN THE USER DEFINED THE CONTRA_ACCOUNT IN THE PARAMETER, THE REPORT SELECTS THE
413 -- JOURNALS WHICH HAVE THE SAME SUBLEDGER_DOC_SEQUENCE_ID AND INCLUDE AT LEAST ONE
414 -- MATCHED LINE.
415
416 -- SRW.REFERENCE(:CHART_OF_ACCOUNTS_ID);
417 /* SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
418 NUM=":CHART_OF_ACCOUNTS_ID"
419 APPL_SHORT_NAME="SQLGL"
420 OUTPUT=":P_CONTRA_ACCOUNT"
421 MODE="WHERE"
422 DISPLAY="GL_ACCOUNT"
423 OPERATOR = "="
424 OPERAND1 = ":P_CONTRA_ACCT"
425 TABLEALIAS="GCC2"');*/
426
427
428
429 P_CONTRA_ACCOUNT_WHERE := ' AND EXISTS (SELECT GJL2.JE_LINE_NUM
430 FROM GL_JE_LINES GJL2, GL_CODE_COMBINATIONS GCC2
431 WHERE
432 GJL2.JE_HEADER_ID = GLH.JE_HEADER_ID AND
433 GJL2.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID AND
434 ((GJL2.SUBLEDGER_DOC_SEQUENCE_ID = GLL.SUBLEDGER_DOC_SEQUENCE_ID AND
435 GJL2.SUBLEDGER_DOC_SEQUENCE_VALUE = GLL.SUBLEDGER_DOC_SEQUENCE_VALUE) OR
436 (GLL.SUBLEDGER_DOC_SEQUENCE_ID IS NULL AND GJL2.SUBLEDGER_DOC_SEQUENCE_ID IS NULL AND
437 GJL2.SUBLEDGER_DOC_SEQUENCE_VALUE IS NULL)) AND
438 ((GLL.ACCOUNTED_DR IS NOT NULL AND GLL.ACCOUNTED_DR<> 0 AND GJL2.ACCOUNTED_CR IS NOT NULL
439 AND GJL2.ACCOUNTED_CR <> 0) OR
440 (GLL.ACCOUNTED_CR IS NOT NULL AND GLL.ACCOUNTED_CR<> 0 AND GJL2.ACCOUNTED_DR IS NOT NULL
441 AND GJL2.ACCOUNTED_DR <> 0)) AND '||P_CONTRA_ACCOUNT|| ' )';
442 END IF;
443
444
445
446
447 IF (P_START_UPDATE_DATE IS NOT NULL OR P_END_UPDATE_DATE IS NOT NULL) THEN
448 IF P_START_UPDATE_DATE IS NULL THEN
449 P_JOURNAL_UPD_DATE_WHERE :=
450 ' AND TRUNC(GLH.LAST_UPDATE_DATE) <= '''|| H_END_UPDATE_DATE ||'''';
451 ELSIF P_END_UPDATE_DATE IS NULL THEN
452 P_JOURNAL_UPD_DATE_WHERE := ' AND TRUNC(GLH.LAST_UPDATE_DATE) >= '''|| H_START_UPDATE_DATE ||'''';
453 ELSIF ((P_START_UPDATE_DATE IS NOT NULL) AND (P_END_UPDATE_DATE IS NOT NULL)) THEN
454 P_JOURNAL_UPD_DATE_WHERE := ' AND TRUNC(GLH.LAST_UPDATE_DATE) BETWEEN ''' || H_START_UPDATE_DATE || ''' AND ''' || H_END_UPDATE_DATE || '''';
455 END IF;
456 ELSE
457 P_JOURNAL_UPD_DATE_WHERE := ' ';
458 END IF;
459
460
461 IF(P_LAST_UPDATED_BY IS NOT NULL) THEN
462
463 P_JOURNAL_LAST_UPD_WHERE := ' AND GLH.LAST_UPDATED_BY = '||P_LAST_UPDATED_BY;
464 ELSE
465 P_JOURNAL_LAST_UPD_WHERE := ' ';
466
467 END IF;
468 ---*****************************************--------
469
470
471 IF NVL(P_JOURNALS_LINE_FLAG,'L') = 'J' THEN
472
473 IF NVL(P_STATUS,'U')<> 'P' THEN
474 P_USE_DATE_COL := 'GLH2.DEFAULT_EFFECTIVE_DATE';
475 ELSIF P_STATUS = 'P' THEN
476 P_USE_DATE_COL := 'GLB2.POSTED_DATE';
477 END IF;
478
479
480 IF P_USE_DATE_COL = 'GLH2.DEFAULT_EFFECTIVE_DATE' AND
481 (P_START_DATE IS NOT NULL OR P_END_DATE IS NOT NULL) THEN
482 -- SUB QUERY START
483 P_JOURNAL_DATE_WHERE_2 := ' AND GLH2.PERIOD_NAME IN (SELECT PERIOD_NAME FROM GL_PERIOD_STATUSES PS
484 WHERE PS.APPLICATION_ID = 101 AND PS.LEDGER_ID = LGR1.LEDGER_ID ';
485 /* BUGFIX - 3804059 AND 4043565 CHANGED VARIABLES TO BIND */
486 IF P_START_DATE IS NULL THEN
487 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND ( PS.START_DATE <= ''' || H_END_DATE || ''' )';
488 ELSIF P_END_DATE IS NULL THEN
489 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND (PS.END_DATE >= ''' || H_START_DATE || ''' )';
490 ELSE
491 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND (PS.END_DATE >= ''' || H_START_DATE ||''' AND PS.START_DATE <= ''' || H_END_DATE || ''')';
492 END IF;
493 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||')';
494 -- SUB QUERY END
495
496 ELSIF P_USE_DATE_COL = 'GLL2.EFFECTIVE_DATE' AND --:P_JOURNAL_DATE_WHERE_2 IS NOT NULL THEN
497 (P_START_DATE IS NOT NULL OR P_END_DATE IS NOT NULL) THEN
498
499 -- SUB QUERY START
500 P_JOURNAL_DATE_WHERE_2 := ' AND GLL2.PERIOD_NAME IN (SELECT PERIOD_NAME FROM GL_PERIOD_STATUSES PS
501 WHERE PS.APPLICATION_ID = 101 AND PS.SET_OF_BOOKS_ID = ' || P_LEDGER_ID || ' ';
502 -- BUGFIX - 3804059 AND 4043565 CHANGED VARIABLES TO BIND
503 IF P_START_DATE IS NULL THEN
504 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND ( PS.START_DATE <= ''' || H_END_DATE || ''' )';
505 ELSIF P_END_DATE IS NULL THEN
506 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND (PS.END_DATE >= ''' || H_START_DATE || ''' )';
507 ELSE
508 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND (PS.END_DATE >= ''' || H_START_DATE || ''' AND PS.START_DATE <= ''' || H_END_DATE ||''')';
509 END IF;
510 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||')';
511 -- SUB QUERY END
512
513 -- BUGFIX - 3804059 AND 4043565 CHANGED VARIABLES TO BIND
514 /* ELSIF (:P_START_DATE IS NULL AND :P_END_DATE IS NULL) THEN
515 :P_JOURNAL_DATE_WHERE_2 := ' AND ( ' ||:P_START_DATE || ' IS NULL AND ' || :H_END_DATE || ' IS NULL) ';
516
517 ELSIF ((:P_START_DATE IS NOT NULL) AND (:P_END_DATE IS NOT NULL))THEN
518 :P_JOURNAL_DATE_WHERE_2 := ' AND (( ' || :H_START_DATE || ' IS NOT NULL OR ' || :H_END_DATE || ' IS NOT NULL)) ';
519 */
520 END IF;
521
522 -- BUGFIX - 3804059 AND 4043565 CHANGE VARIABLES TO BIND
523 IF ((P_START_DATE IS NOT NULL) OR (P_END_DATE IS NOT NULL))THEN
524 IF P_START_DATE IS NULL THEN
525 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND TRUNC('|| P_USE_DATE_COL ||') <= ''' || H_END_DATE||'''' ;
526 ELSIF P_END_DATE IS NULL THEN
527 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND TRUNC('|| P_USE_DATE_COL ||') >= ''' || H_START_DATE||'''';
528 ELSIF ((P_START_DATE IS NOT NULL) AND (P_END_DATE IS NOT NULL)) THEN
529 P_JOURNAL_DATE_WHERE_2 := P_JOURNAL_DATE_WHERE_2 ||' AND TRUNC('|| P_USE_DATE_COL ||') BETWEEN '''|| H_START_DATE || ''' AND '''|| H_END_DATE || '''';
530 END IF;
531 END IF;
532
533
534 ELSE -- LINE LEVEL REQUEST
535
536
537 IF P_PERIOD_NAME IS NOT NULL THEN
538
539 -- BUGFIX - 3804059 AND 4043565 CHANGE TO BIND VARIABLE
540
541 P_PERIOD_WHERE := 'AND GLB.DEFAULT_PERIOD_NAME = '''||P_PERIOD_NAME||'''' ;
542
543
544 ELSE
545 -- BUGFIX - 3804059 AND 4043565 CHANGE TO BIND VARIABLE
546 P_PERIOD_WHERE := ' ';
547 IF P_REPORT_NAME = 'CONTRA' OR P_REPORT_NAME = 'VOUCHER' OR
548 (P_JOURNALS_LINE_FLAG IS NOT NULL AND NVL(P_STATUS,'U') <> 'P') THEN
549
550 -- USE GLL.EFFECTIVE_DATE FOR "ACCOUNT ANALYSIS - CONTRA ACCOUNT" AND "JOURNALS VOUCHER"
551
552 P_USE_DATE_COL := 'GLL.EFFECTIVE_DATE';
553
554 ELSIF P_STATUS = 'P' THEN
555
556 P_USE_DATE_COL := 'GLB.POSTED_DATE';
557
558 ELSE
559
560 P_USE_DATE_COL := 'GLH.DEFAULT_EFFECTIVE_DATE';
561
562 END IF;
563
564 --
565 -- PERFORMANCE ENHANCEMENT BOOST
566 --
567 IF (P_USE_DATE_COL = 'GLH.DEFAULT_EFFECTIVE_DATE' OR
568 P_USE_DATE_COL = 'NVL(GLL.EFFECTIVE_DATE, GLH.DEFAULT_EFFECTIVE_DATE)') AND
569 (P_START_DATE IS NOT NULL OR P_END_DATE IS NOT NULL) THEN
570
571 /* -- SUB QUERY START
572 :P_JOURNAL_DATE_WHERE := ' AND GLH.PERIOD_NAME IN (SELECT + CARDINALITY(PS,2 ) PERIOD_NAME
573 FROM GL_PERIOD_STATUSES PS
574 WHERE PS.APPLICATION_ID = 101 AND PS.LEDGER_ID = LGR.LEDGER_ID ';
575 */
576 -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND
577 IF P_START_DATE IS NULL THEN
578 P_JOURNAL_DATE_WHERE_3 := ' AND ( PS.START_DATE <= ''' || H_END_DATE ||''' )';
579 ELSIF P_END_DATE IS NULL THEN
580 P_JOURNAL_DATE_WHERE_3 := ' AND (PS.END_DATE >= ''' || H_START_DATE || ''' )';
581 ELSE
582 P_JOURNAL_DATE_WHERE_3 := ' AND (PS.END_DATE >= ''' || H_START_DATE || ''' AND PS.START_DATE <= ''' || H_END_DATE || ''' )';
583 END IF;
584 -- :P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||')';
585 -- SUB QUERY END
586
587 ELSIF NVL(P_USE_DATE_COL,'NULL') = 'GLL.EFFECTIVE_DATE' AND
588 (P_START_DATE IS NOT NULL OR P_END_DATE IS NOT NULL) THEN
589
590 -- SUB QUERY START
591 P_JOURNAL_DATE_WHERE := ' AND GLL.PERIOD_NAME IN (SELECT /*+ CARDINAILTY(PS,2 ) */ PERIOD_NAME
592 FROM GL_PERIOD_STATUSES PS
593 WHERE PS.APPLICATION_ID = 101 AND PS.LEDGER_ID = LGR.LEDGER_ID ';
594
595 -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND
596 IF P_START_DATE IS NULL THEN
597 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||' AND ( PS.START_DATE <= ''' || H_END_DATE || ''' )';
598 ELSIF P_END_DATE IS NULL THEN
599 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||' AND (PS.END_DATE >= ''' || H_START_DATE || ''' )';
600
601 ELSE
602 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||' AND (PS.END_DATE >= ''' || H_START_DATE ||
603 ''' AND PS.START_DATE <= ''' || H_END_DATE || ''' )';
604
605 END IF;
606 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||')';
607 -- SUB QUERY END
608
609 /* ELSIF ((P_START_DATE IS NOT NULL) AND (P_END_DATE IS NOT NULL)) THEN
610 P_JOURNAL_DATE_WHERE := ' ( AND (''' || P_START_DATE || ''' IS NOT NULL OR '''|| P_END_DATE || ''' IS NOT NULL)) '; */
611
612 END IF;
613
614 /* BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND */
615 IF (P_START_DATE IS NOT NULL OR P_END_DATE IS NOT NULL) THEN
616 IF P_START_DATE IS NULL THEN
617 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||' AND TRUNC('|| P_USE_DATE_COL ||') <= ''' || H_END_DATE || '''';
618 ELSIF P_END_DATE IS NULL THEN
619 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||' AND TRUNC('|| P_USE_DATE_COL ||') >= ''' || H_START_DATE || '''';
620 ELSIF ((P_START_DATE IS NOT NULL) AND (P_END_DATE IS NOT NULL)) THEN
621 P_JOURNAL_DATE_WHERE := P_JOURNAL_DATE_WHERE ||' AND TRUNC('|| P_USE_DATE_COL ||') BETWEEN ''' || H_START_DATE || ''' AND ''' || H_END_DATE || '''';
622 END IF;
623 ELSE
624 P_JOURNAL_DATE_WHERE := ' ';
625 END IF;
626
627
628 END IF;
629
630
631 END IF;
632
633 ---*******************************************---------
634
635
636 IF NVL(P_JOURNALS_LINE_FLAG,'L') = 'J' THEN -- SELECTION BY JOURNAL BASE
637 AMOUNT_WHERE:= NULL;
638 AFF_WHERE:= NULL;
639
640
641 IF P_USE_DATE_COL = 'GLB2.POSTED_DATE' OR P_USE_DATE_COL = 'GLH2.DEFAULT_EFFECTIVE_DATE' THEN
642 JOURNAL_FROM:=' GL_JE_BATCHES GLB2, ';
643
644 JOURNAL_WHERE:= ' AND GLB2.JE_BATCH_ID = GLH2.JE_BATCH_ID ';
645 ELSE
646 JOURNAL_FROM:= NULL;
647 JOURNAL_WHERE:= NULL;
648 END IF;
649
650 IF P_AMOUNT_FROM IS NOT NULL OR P_AMOUNT_TO IS NOT NULL THEN
651
652 IF P_AMT_FLAG = 'DR' THEN
653 AMOUNT_WHERE2:= ' AND GLL2.ACCOUNTED_DR <> 0 AND GLL2.ACCOUNTED_DR ';
654 ELSIF P_AMT_FLAG = 'CR' THEN
655 AMOUNT_WHERE2:= ' AND GLL2.ACCOUNTED_CR <> 0 AND GLL2.ACCOUNTED_CR ';
656 ELSE
657 AMOUNT_WHERE2:= ' AND ((GLL2.ACCOUNTED_DR <> 0 AND GLL2.ACCOUNTED_DR ';
658 END IF;
659
660 -- BUGFIX - 3804059 AND 4043565
661 IF P_AMOUNT_FROM IS NOT NULL AND P_AMOUNT_TO IS NULL THEN
662 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' >= '|| P_AMOUNT_FROM ||' ';
663 ELSIF P_AMOUNT_FROM IS NULL AND P_AMOUNT_TO IS NOT NULL THEN
664 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' <= '|| P_AMOUNT_TO||' ';
665 ELSIF (P_AMOUNT_FROM IS NOT NULL) AND (P_AMOUNT_TO IS NOT NULL) THEN
666 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' BETWEEN '||P_AMOUNT_FROM ||' AND '|| P_AMOUNT_TO||' ';
667 END IF;
668
669 -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND
670 IF P_AMT_FLAG IS NULL THEN
671 IF P_AMOUNT_FROM IS NOT NULL AND P_AMOUNT_TO IS NULL THEN
672 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ') OR (GLL2.ACCOUNTED_CR <> 0 AND GLL2.ACCOUNTED_CR ';
673 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' >= '||P_AMOUNT_FROM || '))';
674 ELSIF P_AMOUNT_FROM IS NULL AND P_AMOUNT_TO IS NOT NULL THEN
675 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ') OR (GLL2.ACCOUNTED_CR <> 0 AND GLL2.ACCOUNTED_CR ';
676 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' <= '||P_AMOUNT_TO|| ' ))';
677 ELSIF ((P_AMOUNT_FROM IS NOT NULL) AND (P_AMOUNT_TO IS NOT NULL)) THEN
678 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ') OR (GLL2.ACCOUNTED_CR <> 0 AND GLL2.ACCOUNTED_CR ';
679 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' BETWEEN '|| P_AMOUNT_FROM ||' AND '|| P_AMOUNT_TO ||' ))';
680 END IF;
681 /* ELSIF (:P_AMOUNT_FROM IS NOT NULL AND
682 AMOUNT_WHERE2:= AMOUNT_WHERE2 || ' AND ('||:P_AMOUNT_FROM ||' IS NOT NULL OR '||:P_AMOUNT_TO ||' IS NOT NULL) ';*/
683 END IF;
684
685 END IF;
686
687 -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND
688 IF P_AFF_FROM IS NOT NULL AND P_AFF_TO IS NOT NULL THEN
689
690
691 /* SRW.REFERENCE(:CHART_OF_ACCOUNTS_ID);
692 SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
693 NUM=":CHART_OF_ACCOUNTS_ID"
694 APPL_SHORT_NAME="SQLGL"
695 OUTPUT=":P_AFF_WHERE_JRL"
696 MODE="WHERE"
697 DISPLAY="ALL"
698 OPERATOR="BETWEEN"
699 OPERAND1=:P_AFF_FROM
700 OPERAND2=:P_AFF_TO
701 TABLEALIAS="GCC3"'); */
702
703
704 IF (P_AFF_WHERE_JRL IS NOT NULL) THEN
705 P_AFF_WHERE_JRL := ' AND '||P_AFF_WHERE_JRL;
706 END IF;
707
708
709
710 END IF;
711
712 P_JOURNAL_FROM_CLAUSE:= ', (SELECT DISTINCT GLL2.JE_HEADER_ID JE_HEADER_ID,
713 GLL2.SUBLEDGER_DOC_SEQUENCE_ID SUB_DOC_SEQ_ID,
714 GLL2.SUBLEDGER_DOC_SEQUENCE_VALUE SUB_DOC_SEQ_VALUE
715 FROM ' || JOURNAL_FROM ||
716 ' GL_LEDGERS LGR1,
717 GL_JE_LINES GLL2,
718 GL_JE_HEADERS GLH2,
719 GL_CODE_COMBINATIONS GCC3 '||L_LEDGER_FROM||'
720 WHERE LGR1.LEDGER_ID = GLH2.LEDGER_ID
721 '|| JOURNAL_WHERE ||'
722 '||L_LEDGER_WHERE||'
723 '|| P_JOURNAL_DATE_WHERE_2 || '
724 '|| AMOUNT_WHERE2 || '
725 '|| P_AFF_WHERE_JRL ||'
726 '|| L_DAS_WHERE||
727 ' AND GLL2.JE_HEADER_ID = GLH2.JE_HEADER_ID
728 AND GLH2.LEDGER_ID = GLL2.LEDGER_ID
729 AND GLL2.CODE_COMBINATION_ID = GCC3.CODE_COMBINATION_ID
730 AND (NVL(GLL2.ACCOUNTED_DR, 0) <> 0 OR NVL(GLL2.ACCOUNTED_CR, 0) <> 0 )) SUB_TABLE
731 ';
732
733 P_JOURNAL_WHERE_CLAUSE:=' AND SUB_TABLE.JE_HEADER_ID = GLL.JE_HEADER_ID
734 AND (SUB_TABLE.SUB_DOC_SEQ_ID IS NULL OR
735 SUB_TABLE.SUB_DOC_SEQ_ID = GLL.SUBLEDGER_DOC_SEQUENCE_ID)
736 AND (SUB_TABLE.SUB_DOC_SEQ_VALUE IS NULL OR
737 SUB_TABLE.SUB_DOC_SEQ_VALUE = GLL.SUBLEDGER_DOC_SEQUENCE_VALUE)';
738
739
740
741 ELSE -- SELECTION BY LINE BASE
742
743 P_JOURNAL_FROM_CLAUSE:= ' ';
744
745 -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLE TO BIND
746
747 IF P_AFF_FROM IS NOT NULL AND P_AFF_TO IS NOT NULL THEN
748
749 /* SRW.REFERENCE(:CHART_OF_ACCOUNTS_ID);
750 SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
751 NUM=":CHART_OF_ACCOUNTS_ID"
752 APPL_SHORT_NAME="SQLGL"
753 OUTPUT=":P_AFF_WHERE"
754 MODE="WHERE"
755 DISPLAY="ALL"
756 OPERATOR="BETWEEN"
757 OPERAND1=:P_AFF_FROM
758 OPERAND2=:P_AFF_TO
759 TABLEALIAS="CC"');*/
760
761 IF (P_AFF_WHERE IS NOT NULL) THEN
762 P_AFF_WHERE := ' AND '||P_AFF_WHERE;
763 END IF;
764
765 END IF;
766
767
768 IF (P_AMOUNT_FROM IS NOT NULL OR P_AMOUNT_TO IS NOT NULL) THEN
769
770 IF P_AMT_FLAG = 'DR' THEN
771 AMOUNT_WHERE:= ' AND GLL.ACCOUNTED_DR <> 0 AND GLL.ACCOUNTED_DR ';
772 ELSIF P_AMT_FLAG = 'CR' THEN
773 AMOUNT_WHERE:= ' AND GLL.ACCOUNTED_CR <> 0 AND GLL.ACCOUNTED_CR ';
774 ELSE
775 AMOUNT_WHERE:= ' AND ((GLL.ACCOUNTED_DR <> 0 AND GLL.ACCOUNTED_DR ';
776 END IF;
777
778 IF P_AMOUNT_FROM IS NOT NULL AND P_AMOUNT_TO IS NULL THEN
779 AMOUNT_WHERE:= AMOUNT_WHERE || ' >= '|| P_AMOUNT_FROM;
780 ELSIF P_AMOUNT_FROM IS NULL AND P_AMOUNT_TO IS NOT NULL THEN
781 AMOUNT_WHERE:= AMOUNT_WHERE || ' <= '||P_AMOUNT_TO;
782 ELSE
783 AMOUNT_WHERE:= AMOUNT_WHERE || ' BETWEEN ' || P_AMOUNT_FROM ||' AND '|| P_AMOUNT_TO ||' ';
784 END IF;
785
786 -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND
787 IF P_AMT_FLAG IS NULL THEN
788 IF P_AMOUNT_FROM IS NOT NULL AND P_AMOUNT_TO IS NULL THEN
789 AMOUNT_WHERE:= AMOUNT_WHERE || ') OR (GLL.ACCOUNTED_CR <> 0 AND GLL.ACCOUNTED_CR ';
790 AMOUNT_WHERE:= AMOUNT_WHERE || ' >= '||P_AMOUNT_FROM||' ))';
791 ELSIF P_AMOUNT_FROM IS NULL AND P_AMOUNT_TO IS NOT NULL THEN
792 AMOUNT_WHERE:= AMOUNT_WHERE || ') OR (GLL.ACCOUNTED_CR <> 0 AND GLL.ACCOUNTED_CR ';
793 AMOUNT_WHERE:= AMOUNT_WHERE || ' <= '||P_AMOUNT_TO||' ))';
794 ELSE
795 AMOUNT_WHERE:= AMOUNT_WHERE || ') OR (GLL.ACCOUNTED_CR <> 0 AND GLL.ACCOUNTED_CR ';
796 AMOUNT_WHERE:= AMOUNT_WHERE || ' BETWEEN '||P_AMOUNT_FROM ||' AND '|| P_AMOUNT_TO || ' ))';
797 END IF;
798 /* -- BUGFIX - 3804059 AND 4043565 CHANGES VARIABLES TO BIND
799 ELSE
800 AMOUNT_WHERE := AMOUNT_WHERE || ' AND (P_AMOUNT_FROM IS NOT NULL OR P_AMOUNT_TO IS NOT NULL) '; */
801
802 END IF;
803
804 P_AMOUNT_WHERE := AMOUNT_WHERE;
805 ELSE
806 P_AMOUNT_WHERE := ' ';
807 END IF;
808
809
810
811 END IF;
812
813 --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%---
814 /* EXCEPTION
815 WHEN SRW.UNKNOWN_USER_EXIT THEN
816 -- SRW.MESSAGE(07,'FND FLEXSQL USER EXIT IS UNKNOWN.');
817 RAISE;
818 WHEN SRW.USER_EXIT_FAILURE THEN
819 -- SRW.MESSAGE(08,'FND FLEXSQL USER EXIT FAILED.');
820 RAISE;*/
821
822 END;
823
824
825
826 /***** COPY/RESOLVE PARAMETERS *****/
827
828 IF P_LEDGER_ID IS NOT NULL THEN
829 SELECT NAME
830 INTO PARAM_LEDGER_NAME
831 FROM GL_LEDGERS
832 WHERE LEDGER_ID = P_LEDGER_ID;
833 END IF;
834
835 IF P_STATUS IS NOT NULL THEN
836 GL_INFO.GL_GET_LOOKUP_VALUE('M', P_STATUS, 'JOURNAL_REPORT_TYPE',
837 PARAM_POSTING_STATUS, T_ERRORBUFFER);
838 /* IF (T_ERRORBUFFER IS NOT NULL) THEN
839 SRW.MESSAGE('00', T_ERRORBUFFER);
840 END IF;*/
841 END IF;
842
843 PARAM_CURRENCY_CODE := P_CURRENCY;
844
845
846 IF (P_REPORT_NAME = 'CHECK') THEN
847 PARAM_START_DATE := H_START_UPDATE_DATE;
848 PARAM_END_DATE := H_END_UPDATE_DATE;
849 ELSE
850 PARAM_START_DATE := H_START_DATE;
851 PARAM_END_DATE := H_END_DATE;
852 END IF;
853 C_DATE_FORMAT := 'DD-MON-YY';
854 C_PARAM_START_DATE := to_char(PARAM_START_DATE,C_DATE_FORMAT);
855 C_PARAM_END_DATE := to_char(PARAM_END_DATE,C_DATE_FORMAT);
856 IF P_SOURCE IS NOT NULL THEN
857 SELECT USER_JE_SOURCE_NAME
858 INTO PARAM_SOURCE
859 FROM GL_JE_SOURCES
860 WHERE JE_SOURCE_NAME = P_SOURCE;
861 END IF;
862
863 IF P_CATEGORY IS NOT NULL THEN
864 SELECT USER_JE_CATEGORY_NAME
865 INTO PARAM_CATEGORY
866 FROM GL_JE_CATEGORIES
867 WHERE JE_CATEGORY_NAME = P_CATEGORY;
868 END IF;
869
870 PARAM_BATCH_NAME := P_BATCH_NAME;
871
872 IF P_LAST_UPDATED_BY IS NOT NULL THEN
873 SELECT USER_NAME
874 INTO PARAM_LAST_UPDATED_BY
875 FROM FND_USER
876 WHERE USER_ID = P_LAST_UPDATED_BY;
877 END IF;
878
879 PARAM_BALANCING_SEGMENT := P_BAL_SEG_VAL;
880 PARAM_PERIOD_NAME := P_PERIOD_NAME;
881
882 IF P_JOURNALS_LINE_FLAG IS NOT NULL THEN
883 GL_INFO.GL_GET_LOOKUP_VALUE('M', P_JOURNALS_LINE_FLAG, 'SELECTION_BASE',
884 PARAM_BASIS, T_ERRORBUFFER);
885 /* IF (T_ERRORBUFFER IS NOT NULL) THEN
886 SRW.MESSAGE('00', T_ERRORBUFFER);
887 END IF;*/
888 END IF;
889
890 IF P_DOC_ID IS NOT NULL THEN
891 SELECT NAME
892 INTO PARAM_DOC_SEQ_NAME
893 FROM FND_DOCUMENT_SEQUENCES
894 WHERE DOC_SEQUENCE_ID = P_DOC_ID;
895 END IF;
896 PARAM_DOC_SEQ_VALUE := P_DOC_VALUE;
897 PARAM_START_DOC_SEQ_VALUE := P_START_DOC_VALUE;
898 PARAM_END_DOC_SEQ_VALUE := P_END_DOC_VALUE;
899
900 IF P_SUB_DOC_ID IS NOT NULL THEN
901 SELECT NAME
902 INTO PARAM_SUB_DOC_SEQ_NAME
903 FROM FND_DOCUMENT_SEQUENCES
904 WHERE DOC_SEQUENCE_ID = P_SUB_DOC_ID;
905 END IF;
906 PARAM_SUB_DOC_SEQ_VALUE := P_SUB_DOC_VALUE;
907
908 PARAM_ACCT_FROM := P_AFF_FROM;
909 PARAM_ACCT_TO := P_AFF_TO;
910
911 IF P_AMT_FLAG IS NOT NULL THEN
912 GL_INFO.GL_GET_LOOKUP_VALUE('M', P_AMT_FLAG, 'GL_DR_CR',
913 PARAM_AMT_FLAG, T_ERRORBUFFER);
914 /* IF (T_ERRORBUFFER IS NOT NULL) THEN
915 SRW.MESSAGE('00', T_ERRORBUFFER);
916 END IF;*/
917 END IF;
918
919 PARAM_AMOUNT_LOW := P_AMOUNT_FROM;
920 PARAM_AMOUNT_HIGH := P_AMOUNT_TO;
921 PARAM_CONTRA_ACCT := P_CONTRA_ACCT;
922
923 /* THE SEGMENT PROMPTS*/
924 --SRW.REFERENCE(:CHART_OF_ACCOUNTS_ID);
925
926 /*SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
927 APPL_SHORT_NAME="SQLGL"
928 NUM=":CHART_OF_ACCOUNTS_ID"
929 DATA=":P_AFF_FROM"
930 LPROMPT=":BAL_SEG_NAME_DSP"
931 DISPLAY="GL_BALANCING"
932 IDISPLAY="GL_BALANCING"');*/
933
934 /*SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
935 APPL_SHORT_NAME="SQLGL"
936 NUM=":CHART_OF_ACCOUNTS_ID"
937 DATA=":P_AFF_FROM"
938 LPROMPT=":ACCT_SEG_NAME_DSP"
939 DISPLAY="GL_ACCOUNT"
940 IDISPLAY="GL_ACCOUNT"');*/
941 /*IF ( P_SEC_SEG_NUM IS NOT NULL AND PARAM_SEC_SEG_NAME IS NULL) THEN
942
943 SRW.REFERENCE(:P_SEC_SEG_NUM);
944 SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
945 APPL_SHORT_NAME="SQLGL"
946 NUM=":CHART_OF_ACCOUNTS_ID"
947 DATA=":P_AFF_FROM"
948 LPROMPT=":PARAM_SEC_SEG_NAME"
949 DISPLAY=":P_SEC_SEG_NUM"
950 IDISPLAY=":P_SEC_SEG_NUM"');
951 END IF*/
952
953 /***** END PARAMETER COPYING *****/
954
955
956 /******* START PERFORMANCE HINTS *******/
957
958 IF P_REPORT_NAME = 'CHECK' THEN
959
960 P_HINT_CLAUSE := '/*+ ORDERED */';
961
962 IF ((P_STATUS = 'E') OR (P_STATUS = 'U') OR (P_BATCH_NAME IS NOT NULL)) THEN
963
964 P_PERF_FROM :=
965 ' GL_JE_BATCHES GLB, GL_JE_HEADERS GLH '||P_LEDGER_FROM||
966 ' , GL_JE_LINES GLL, GL_CODE_COMBINATIONS CC, GL_LEDGERS LGR, GL_JE_SOURCES GJS, GL_JE_CATEGORIES GJC, '||
967 ' GL_DAILY_CONVERSION_TYPES DCT, FND_DOCUMENT_SEQUENCES DOCSEQ, '||
968 ' FND_DOCUMENT_SEQUENCES SUBDOCSEQ, FND_USER FU ' ;
969
970 -- SINCE THE FOLLOWING VARIABLE IS IN THE MAIN QUERY TO HANDLE THE REGULAR CASE
971 -- AND IN THIS CASE IT IS INCLUDED INTO THE P_PERF_FROM
972
973 P_LEDGER_FROM :=' ';
974
975
976 ELSIF ((P_STATUS <> 'E') OR (P_STATUS <> 'U')) THEN
977
978
979 IF P_LEDGER_FROM IS NOT NULL THEN
980
981 P_HINT_CLAUSE := '/*+ ORDERED USE_NL(LS) INDEX(LS GL_LEDGER_SET_ASSIGNMENTS_N1)
982 USE_NL(GLL) INDEX (GLL GL_JE_LINES_U1)
983 USE_NL(GLL)INDEX(CC GL_CODE_COMBINATIONS_U1)
984 USE_NL(GLB) INDEX(GLB GL_JE_BATCHES_U1)
985 USE_NL(GJC) INDEX (GJC GL_JE_CATEGORIES_TL_U1)
986 USE_NL(GJS) INDEX (GJS GL_JE_SOURCES_TL_U1)
987 USE_NL(DCT) INDEX (DCT GL_DAILY_CONVERSION_TYPES_U1)
988 USE_NL(DOCSEQ) INDEX(DOCSEQ FND_DOCUMENT_SEQUENCES_U1)
989 USE_NL(SUBDOCSEQ) INDEX(SUBDOCSEQ FND_DOCUMENT_SEQUENCES_U1)
990 USE_NL(FU) INDEX(FU FND_USER_U1) */';
991 ELSE
992 P_HINT_CLAUSE := '/*+ ORDERED
993 USE_NL(GLL) INDEX (GLL GL_JE_LINES_U1)
994 USE_NL(GLL)INDEX(CC GL_CODE_COMBINATIONS_U1)
995 USE_NL(GLB) INDEX(GLB GL_JE_BATCHES_U1)
996 USE_NL(GJC) INDEX (GJC GL_JE_CATEGORIES_TL_U1)
997 USE_NL(GJS) INDEX (GJS GL_JE_SOURCES_TL_U1)
998 USE_NL(DCT) INDEX (DCT GL_DAILY_CONVERSION_TYPES_U1)
999 USE_NL(DOCSEQ) INDEX(DOCSEQ FND_DOCUMENT_SEQUENCES_U1)
1000 USE_NL(SUBDOCSEQ) INDEX(SUBDOCSEQ FND_DOCUMENT_SEQUENCES_U1)
1001 USE_NL(FU) INDEX(FU FND_USER_U1) */';
1002 END IF;
1003
1004 P_PERF_FROM :=
1005 ' GL_JE_HEADERS GLH '||P_LEDGER_FROM||
1006 ' , GL_JE_BATCHES GLB, GL_JE_LINES GLL, GL_CODE_COMBINATIONS CC, GL_JE_SOURCES GJS, GL_LEDGERS LGR,'||
1007 ' GL_JE_CATEGORIES GJC, GL_DAILY_CONVERSION_TYPES DCT, FND_DOCUMENT_SEQUENCES DOCSEQ, '||
1008 ' FND_DOCUMENT_SEQUENCES SUBDOCSEQ, FND_USER FU ';
1009
1010 -- SINCE THE FOLLOWING VARIABLE IS IN THE MAIN QUERY TO HANDLE THE REGULAR CASE
1011 -- AND IN THIS CASE IT IS INCLUDED INTO THE P_PERF_FROM
1012
1013 P_LEDGER_FROM :=' ';
1014
1015 END IF;
1016
1017 IF P_PERF_FROM IS NULL THEN
1018 P_HINT_CLAUSE :=' ';
1019 END IF;
1020
1021 ELSE
1022
1023 P_HINT_CLAUSE := ' ';
1024 /* :P_PERF_FROM :=
1025 ' GL_JE_HEADERS GLH, GL_LEDGERS LGR,'||P_LEDGER_FROM||
1026 ' GL_JE_LINES GLL, GL_CODE_COMBINATIONS CC, GL_JE_BATCHES GLB, GL_JE_SOURCES GJS, '||
1027 ' GL_JE_CATEGORIES GJC, GL_DAILY_CONVERSION_TYPES DCT, FND_DOCUMENT_SEQUNCES DOCSEQ, '||
1028 ' FND_DOCUMENT_SEQUNCES SUBDOCSEQ, FND_USER FU'; */
1029 END IF;
1030
1031
1032 /******** END PERFORMANCE HINTS ********/
1033
1034
1035
1036 RETURN (TRUE);
1037 END BEFOREREPORT;
1038
1039 function AfterReport return boolean is
1040 begin
1041
1042 /*srw.user_exit('FND SRWEXIT');*/null;
1043 return (TRUE);
1044 end;
1045
1046 PROCEDURE SET_DATE_FORMAT IS
1047 BEGIN
1048
1049 /*SRW.ATTR.MASK := SRW.FORMATMASK_ATTR;*/null;
1050
1051 /*SRW.ATTR.FORMATMASK := H_DATEFORMAT;*/null;
1052
1053 /*SRW.SET_ATTR(0,SRW.ATTR);*/null;
1054
1055
1056
1057 END;
1058
1059 function CF_1Formula return Char is
1060 MULTIPLE_NAME varchar2(20);
1061 begin
1062
1063
1064 multiple_name := 'MULTIPLE';
1065
1066 RETURN(multiple_name);
1067 end;
1068
1069 function H_START_PERIOD_DATEFormula return DATE is
1070
1071 START_PERIOD_DATE date := SYSDATE;
1072
1073 begin
1074
1075
1076
1077 RETURN(START_PERIOD_DATE);
1078
1079
1080
1081 EXCEPTION
1082 WHEN NO_DATA_FOUND THEN
1083 SELECT MIN(start_date)
1084 INTO start_period_date
1085 FROM gl_period_statuses
1086 WHERE application_id = 101
1087 AND set_of_books_id = H_SET_OF_BOOKS_ID
1088 AND adjustment_period_flag <> 'Y';
1089
1090 RETURN(START_PERIOD_DATE);
1091
1092 WHEN OTHERS THEN
1093 RETURN NULL;
1094 end;
1095
1096 function H_START_PERIOD_NAMEFormula return Char is
1097
1098
1099 START_PERIOD_NAME varchar2(30) := SYSDATE;
1100
1101 begin
1102
1103
1104
1105 RETURN(START_PERIOD_NAME);
1106
1107
1108 EXCEPTION
1109 WHEN NO_DATA_FOUND THEN
1110 SELECT p1.period_name
1111 INTO start_period_name
1112 FROM gl_period_statuses p1
1113 WHERE p1.application_id = 101
1114 AND p1.set_of_books_id = H_SET_OF_BOOKS_ID
1115 AND p1.adjustment_period_flag <> 'Y'
1116 AND p1.start_date =
1117 (SELECT MIN(p2.start_date)
1118 FROM gl_period_statuses p2
1119 WHERE p2.application_id = p1.application_id
1120 AND p2.set_of_books_id = p1.set_of_books_id
1121 AND p2.adjustment_period_flag <> 'Y');
1122
1123 RETURN(START_PERIOD_NAME);
1124
1125 WHEN OTHERS THEN
1126 RETURN NULL;
1127 end;
1128
1129 function NO_CONTRA_ACCOUNTFormula return Char is
1130
1131 NO_CONTRA_NAME varchar2(20);
1132 begin
1133
1134
1135 NO_CONTRA_NAME := 'NO CONTRA ACCOUNT';
1136
1137 RETURN(NO_CONTRA_NAME);
1138 end;
1139
1140 function tax_typeformula(TAX_TYPE_CODE in varchar2) return char is
1141 l_tax_type VARCHAR2(80);
1142 errorbuffer VARCHAR2(132);
1143 begin
1144 /*srw.reference(TAX_TYPE_CODE);*/null;
1145
1146 if (TAX_TYPE_CODE is not null) then
1147 gl_info.gl_get_lookup_value('M', TAX_TYPE_CODE, 'TAX_TYPE',
1148 l_tax_type, errorbuffer);
1149 end if;
1150 return (l_tax_type);
1151 end;
1152
1153 function approval_statusformula(APPROVAL_STATUS_CODE in varchar2) return char is
1154 l_appr_status VARCHAR2(80);
1155 errorbuffer VARCHAR2(132);
1156 begin
1157 /*srw.reference(APPROVAL_STATUS_CODE);*/null;
1158
1159 gl_info.gl_get_lookup_value('M', APPROVAL_STATUS_CODE, 'JE_BATCH_APPROVAL_STATUS',
1160 l_appr_status, errorbuffer);
1161 return (l_appr_status);
1162 end;
1163
1164 function batch_statusformula(BATCH_STATUS_CODE in varchar2) return char is
1165 l_batch_status VARCHAR2(80);
1166 errorbuffer VARCHAR2(132);
1167 begin
1168 /*srw.reference(BATCH_STATUS_CODE);*/null;
1169
1170 gl_info.gl_get_lookup_value('M', BATCH_STATUS_CODE, 'MJE_BATCH_STATUS',
1171 l_batch_status, errorbuffer);
1172 return (l_batch_status);
1173 end;
1174
1175 function batch_typeformula(ACTUAL_FLAG in varchar2) return char is
1176 l_batch_type VARCHAR2(80);
1177 errorbuffer VARCHAR2(132);
1178 begin
1179 /*srw.reference(ACTUAL_FLAG);*/null;
1180
1181 gl_info.gl_get_lookup_value('D', ACTUAL_FLAG, 'BATCH_TYPE',
1182 l_batch_type, errorbuffer);
1183 return (l_batch_type);
1184 end;
1185
1186 function budget_ctrl_statusformula(BUD_CONTROL_STATUS in varchar2) return char is
1187 l_bc_status VARCHAR2(80);
1188 errorbuffer VARCHAR2(132);
1189 begin
1190 /*srw.reference(BUD_CONTROL_STATUS);*/null;
1191
1192 gl_info.gl_get_lookup_value('M', BUD_CONTROL_STATUS, 'JE_BATCH_BC_STATUS',
1193 l_bc_status, errorbuffer);
1194 return (l_bc_status);
1195 end;
1196
1197 function jrnl_typeformula(AVERAGE_JOURNAL_FLAG in varchar2) return char is
1198 l_journal_type VARCHAR2(80);
1199 errorbuffer VARCHAR2(132);
1200 begin
1201 /*srw.reference(AVERAGE_JOURNAL_FLAG);*/null;
1202
1203 gl_info.gl_get_lookup_value('M', AVERAGE_JOURNAL_FLAG, 'AB_JOURNAL_TYPE',
1204 l_journal_type, errorbuffer);
1205 return (l_journal_type);
1206 end;
1207
1208 function tax_statusformula(TAX_STATUS_CODE in varchar2) return char is
1209 l_tax_status VARCHAR2(80);
1210 errorbuffer VARCHAR2(132);
1211 begin
1212 /*srw.reference(TAX_STATUS_CODE);*/null;
1213
1214 gl_info.gl_get_lookup_value('M', TAX_STATUS_CODE, 'TAX_STATUS',
1215 l_tax_status, errorbuffer);
1216 return (l_tax_status);
1217 end;
1218
1219 function budget_nameformula(BUDGET_VERSION_ID in number) return char is
1220 l_budget_name VARCHAR2(15);
1221 begin
1222 /*srw.reference(BUDGET_VERSION_ID);*/null;
1223
1224 IF (BUDGET_VERSION_ID IS NOT NULL) THEN
1225 SELECT budget_name
1226 INTO l_budget_name
1227 FROM GL_BUDGET_VERSIONS
1228 WHERE budget_version_id = BUDGET_VERSION_ID;
1229 END IF;
1230 return l_budget_name;
1231 exception
1232 WHEN NO_DATA_FOUND THEN
1233 return null;
1234 end;
1235
1236 function encumbrance_typeformula(ENCUMBRANCE_TYPE_ID in number) return char is
1237 l_enc_type VARCHAR2(30);
1238 begin
1239 /*srw.reference(ENCUMBRANCE_TYPE_ID);*/null;
1240
1241 IF (ENCUMBRANCE_TYPE_ID IS NOT NULL) THEN
1242 SELECT encumbrance_type
1243 INTO l_enc_type
1244 FROM GL_ENCUMBRANCE_TYPES
1245 WHERE encumbrance_type_id = ENCUMBRANCE_TYPE_ID;
1246 END IF;
1247 return l_enc_type;
1248 exception
1249 WHEN NO_DATA_FOUND THEN
1250 return null;
1251 end;
1252
1253 function tax_codeformula(TAX_CODE_ID in number, TAX_TYPE_CODE in varchar2) return char is
1254 l_tax_code VARCHAR2(50);
1255 begin
1256 /*srw.reference(TAX_CODE_ID);*/null;
1257
1258 /*srw.reference(TAX_TYPE_CODE);*/null;
1259
1260 IF (TAX_CODE_ID IS NOT NULL) THEN
1261 SELECT GLT.TAX_CODE
1262 INTO l_tax_code
1263 FROM GL_TAX_CODES_V GLT
1264 WHERE to_char(GLT.TAX_CODE_ID) = TAX_CODE_ID
1265 AND GLT.TAX_TYPE_CODE = TAX_TYPE_CODE;
1266 END IF;
1267
1268 return (l_tax_code);
1269 exception
1270 WHEN NO_DATA_FOUND THEN
1271 return null;
1272 end;
1273
1274 function contra_acct_valueformula(HDR_ID in number, SUB_DOC_SEQ_ID in number, SUB_DOC_SEQ_VAL in number, ACCOUNTED_DR in number, ACCOUNTED_CR in number) return char is
1275
1276 CONTRA_ACCT_SEGMENT VARCHAR2(2000);
1277 l_CONTRA_ACCOUNT_NAME VARCHAR2(2000);
1278 v_sql_stmt VARCHAr2(2000);
1279
1280
1281
1282 BEGIN
1283
1284 If (P_REPORT_NAME = 'CONTRA' ) THEN
1285
1286
1287 l_CONTRA_ACCOUNT_NAME := GL_XML_JOURNAL_RPT_PKG.Get_Contra_Account(SELECT_ACCT_SEGMENT,
1288 HDR_ID,
1289 SUB_DOC_SEQ_ID,
1290 SUB_DOC_SEQ_VAL,
1291 ACCOUNTED_DR,
1292 ACCOUNTED_CR);
1293 RETURN (l_contra_account_name);
1294
1295 ELSE
1296
1297 RETURN NULL;
1298
1299 END IF;
1300
1301
1302
1303 END;
1304
1305 FUNCTION CF_LINE_BALANCEFORMULA(BAL_SEG_VAL IN VARCHAR2, ACCT_SEG_VAL IN VARCHAR2, ADDITIONAL_SEGMENT_VALUE IN VARCHAR2, LEDGER_ID IN NUMBER, BATCH_STATUS_CODE IN VARCHAR2) RETURN NUMBER IS
1306 L_LINE_BALANCE NUMBER;
1307
1308 BEGIN
1309
1310 IF (P_REPORT_NAME = 'CONTRA' ) THEN
1311
1312
1313
1314 IF ( (BAL_SEG_VAL <> PREV_BAL_SEG_VAL) OR
1315 ( ACCT_SEG_VAL <> PREV_ACCT_SEG_VAL) OR
1316 (NVL(ADDITIONAL_SEGMENT_VALUE, 'NULL') <> PREV_SEC_SEG_VAL) OR
1317 (LEDGER_ID <> PREV_LED_ID) ) THEN
1318
1319
1320 L_LINE_BALANCE :=
1321 GL_XML_JOURNAL_RPT_PKG.NET_LINE_BALANCE
1322 (P_ACCT_SEG_WHERE => NVL(P_AFF_WHERE, P_ACC_SEGMENT_WHERE),
1323 P_STATUS => BATCH_STATUS_CODE,
1324 P_START_DATE => H_START_DATE,
1325 P_CURRENCY => PARAM_CURRENCY_CODE,
1326 P_LED_ID => LEDGER_ID,
1327 P_BAL_SEG_NAME => SELECT_BAL_SEGMENT,
1328 P_BAL_SEG_VAL => BAL_SEG_VAL,
1329 P_ACCT_SEG_NAME => SELECT_ACCT_SEGMENT,
1330 P_ACCT_SEG_VAL => ACCT_SEG_VAL,
1331 P_SEC_SEG_NAME => SELECT_SECONDARY_SEGMENT,
1332 P_SEC_SEG_VAL => ADDITIONAL_SEGMENT_VALUE);
1333
1334 PREV_BAL_SEG_VAL := BAL_SEG_VAL;
1335 PREV_ACCT_SEG_VAL := ACCT_SEG_VAL;
1336 PREV_SEC_SEG_VAL := NVL(ADDITIONAL_SEGMENT_VALUE,'NULL1');
1337 PREV_LED_ID := LEDGER_ID;
1338 PREV_LINE_BAL := L_LINE_BALANCE;
1339
1340
1341 RETURN L_LINE_BALANCE;
1342
1343 ELSE
1344
1345 RETURN PREV_LINE_BAL;
1346
1347 END IF;
1348
1349 ELSE
1350
1351 RETURN 0;
1352
1353 END IF;
1354
1355 END CF_LINE_BALANCEFORMULA;
1356
1357 function cf_gl_balanceformula(BAL_SEG_VAL in varchar2, ACCT_SEG_VAL in varchar2, ADDITIONAL_SEGMENT_VALUE in varchar2, LEDGER_ID in number, BATCH_STATUS_CODE in varchar2) return number is
1358 l_beg_balance NUMBER;
1359
1360 BEGIN
1361
1362 If (P_REPORT_NAME = 'CONTRA' ) THEN
1363
1364
1365 IF ( (BAL_SEG_VAL <> NVL(PREV_BAL_SEG_VAL,NULL)) OR
1366 ( ACCT_SEG_VAL <> NVL(PREV_ACCT_SEG_VAL,'NULL')) OR
1367 (NVL(ADDITIONAL_SEGMENT_VALUE,'NULL') <> NVL(PREV_SEC_SEG_VAL,'NULL1')) OR
1368 (LEDGER_ID <> NVL(PREV_LED_ID,0)) ) THEN
1369
1370 l_beg_Balance :=
1371 GL_XML_JOURNAL_RPT_PKG.Net_Begin_Balance
1372 (P_ACCT_SEG_WHERE => NVL(P_AFF_WHERE, P_ACC_SEGMENT_WHERE),
1373 P_STATUS => BATCH_STATUS_CODE,
1374 P_START_DATE => H_START_DATE,
1375 P_CURRENCY => PARAM_CURRENCY_CODE,
1376 P_LED_ID => LEDGER_ID,
1377 P_BAL_SEG_NAME => SELECT_BAL_SEGMENT,
1378 P_BAL_SEG_VAL => BAL_SEG_VAL,
1379 P_ACCT_SEG_NAME => SELECT_ACCT_SEGMENT,
1380 P_ACCT_SEG_VAL => ACCT_SEG_VAL,
1381 P_SEC_SEG_NAME => SELECT_SECONDARY_SEGMENT,
1382 P_SEC_SEG_VAL => ADDITIONAL_SEGMENT_VALUE);
1383
1384 PREV_BAL_SEG_VAL := BAL_SEG_VAL;
1385 PREV_ACCT_SEG_VAL := ACCT_SEG_VAL;
1386 PREV_SEC_SEG_VAL := NVL(ADDITIONAL_SEGMENT_VALUE,'NULL1');
1387 PREV_LED_ID := LEDGER_ID;
1388 PREV_GL_BAL := l_Beg_balance;
1389
1390 RETURN l_Beg_Balance;
1391
1392 ELSE
1393
1394 RETURN PREV_GL_BAL;
1395
1396 END IF;
1397
1398 ELSE
1399 RETURN 0;
1400 END IF;
1401
1402 END;
1403
1404 function begin_balanceformula(CF_LINE_BALANCE in number, CF_GL_BALANCE in number) return number is
1405 begin
1406 If (P_REPORT_NAME = 'CONTRA' ) THEN
1407
1408 IF ((CF_LINE_BALANCE IS NOT NULL)
1409 OR (CF_GL_BALANCE IS NOT NULL)) THEN
1410 Return (NVL(CF_LINE_BALANCE,0)) + (NVL(CF_GL_BALANCE,0));
1411 ELSE
1412 Return (CF_LINE_BALANCE) + (CF_GL_BALANCE);
1413 END IF;
1414 ELSE
1415 Return 0;
1416 End If;
1417 end;
1418
1419 --Functions to refer Oracle report placeholders--
1420
1421 Function ACCESS_SET_NAME_p return varchar2 is
1422 Begin
1423 return ACCESS_SET_NAME;
1424 END;
1425 Function PARAM_CURRENCY_CODE_p return varchar2 is
1426 Begin
1427 return PARAM_CURRENCY_CODE;
1428 END;
1429 Function CHART_OF_ACCOUNTS_ID_p return number is
1430 Begin
1431 return CHART_OF_ACCOUNTS_ID;
1432 END;
1433 Function PARAM_START_DATE_p return date is
1434 Begin
1435 return PARAM_START_DATE;
1436 END;
1437 Function PARAM_END_DATE_p return date is
1438 Begin
1439 return PARAM_END_DATE;
1440 END;
1441 Function PARAM_DOC_SEQ_VALUE_p return number is
1442 Begin
1443 return PARAM_DOC_SEQ_VALUE;
1444 END;
1445 Function PARAM_AMOUNT_LOW_p return number is
1446 Begin
1447 return PARAM_AMOUNT_LOW;
1448 END;
1449 Function PARAM_AMOUNT_HIGH_p return number is
1450 Begin
1451 return PARAM_AMOUNT_HIGH;
1452 END;
1453 Function PARAM_SUB_DOC_SEQ_VALUE_p return number is
1454 Begin
1455 return PARAM_SUB_DOC_SEQ_VALUE;
1456 END;
1457 Function SELECT_ACCT_SEGMENT_p return varchar2 is
1458 Begin
1459 return SELECT_ACCT_SEGMENT;
1460 END;
1461 Function SELECT_BAL_SEGMENT_p return varchar2 is
1462 Begin
1463 return SELECT_BAL_SEGMENT;
1464 END;
1465 Function PARAM_SEC_SEG_NAME_p return varchar2 is
1466 Begin
1467 return PARAM_SEC_SEG_NAME;
1468 END;
1469 Function PARAM_POSTING_STATUS_p return varchar2 is
1470 Begin
1471 return PARAM_POSTING_STATUS;
1472 END;
1473 Function NO_CONTRA_ACCOUNT_p return varchar2 is
1474 Begin
1475 return NO_CONTRA_ACCOUNT;
1476 END;
1477 Function P_ACC_SEGMENT_WHERE_p return varchar2 is
1478 Begin
1479 return P_ACC_SEGMENT_WHERE;
1480 END;
1481 Function P_CURRENCY_WHERE_p return varchar2 is
1482 Begin
1483 return P_CURRENCY_WHERE;
1484 END;
1485 Function P_SEC_SEGMENT_WHERE_p return varchar2 is
1486 Begin
1487 return P_SEC_SEGMENT_WHERE;
1488 END;
1489 Function P_DAS_WHERE_p return varchar2 is
1490 Begin
1491 return P_DAS_WHERE;
1492 END;
1493 Function SELECT_ACCOUNT_p return varchar2 is
1494 Begin
1495 return SELECT_ACCOUNT;
1496 END;
1497 Function SELECT_SECONDARY_SEGMENT_p return varchar2 is
1498 Begin
1499 return SELECT_SECONDARY_SEGMENT;
1500 END;
1501 Function P_POSTING_STATUS_p return varchar2 is
1502 Begin
1503 return P_POSTING_STATUS;
1504 END;
1505 Function P_HEADER_POSTING_STATUS_p return varchar2 is
1506 Begin
1507 return P_HEADER_POSTING_STATUS;
1508 END;
1509 Function P_SOURCE_WHERE_p return varchar2 is
1510 Begin
1511 return P_SOURCE_WHERE;
1512 END;
1513 Function P_BATCH_WHERE_p return varchar2 is
1514 Begin
1515 return P_BATCH_WHERE;
1516 END;
1517 Function P_CATEGORY_WHERE_p return varchar2 is
1518 Begin
1519 return P_CATEGORY_WHERE;
1520 END;
1521 Function P_BAL_WHERE_p return varchar2 is
1522 Begin
1523 return P_BAL_WHERE;
1524 END;
1525 Function P_DOC_ID_WHERE_p return varchar2 is
1526 Begin
1527 return P_DOC_ID_WHERE;
1528 END;
1529 Function P_DOC_VAL_WHERE_p return varchar2 is
1530 Begin
1531 return P_DOC_VAL_WHERE;
1532 END;
1533 Function P_SUB_DOC_ID_WHERE_p return varchar2 is
1534 Begin
1535 return P_SUB_DOC_ID_WHERE;
1536 END;
1537 Function P_SUB_DOC_VAL_WHERE_p return varchar2 is
1538 Begin
1539 return P_SUB_DOC_VAL_WHERE;
1540 END;
1541 Function P_NOT_ZERO_LINE_WHERE_p return varchar2 is
1542 Begin
1543 return P_NOT_ZERO_LINE_WHERE;
1544 END;
1545 Function P_CONTRA_ACCOUNT_WHERE_p return varchar2 is
1546 Begin
1547 return P_CONTRA_ACCOUNT_WHERE;
1548 END;
1549 Function P_CONTRA_ACCOUNT_p return varchar2 is
1550 Begin
1551 return P_CONTRA_ACCOUNT;
1552 END;
1553 Function P_PERIOD_WHERE_p return varchar2 is
1554 Begin
1555 return P_PERIOD_WHERE;
1556 END;
1557 Function P_JOURNAL_DATE_WHERE_p return varchar2 is
1558 Begin
1559 return P_JOURNAL_DATE_WHERE;
1560 END;
1561 Function P_JOURNAL_DATE_WHERE_2_p return varchar2 is
1562 Begin
1563 return P_JOURNAL_DATE_WHERE_2;
1564 END;
1565 Function P_USE_DATE_COL_p return varchar2 is
1566 Begin
1567 return P_USE_DATE_COL;
1568 END;
1569 Function P_JOURNAL_FROM_CLAUSE_p return varchar2 is
1570 Begin
1571 return P_JOURNAL_FROM_CLAUSE;
1572 END;
1573 Function P_JOURNAL_WHERE_CLAUSE_p return varchar2 is
1574 Begin
1575 return P_JOURNAL_WHERE_CLAUSE;
1576 END;
1577 Function PARAM_SOURCE_p return varchar2 is
1578 Begin
1579 return PARAM_SOURCE;
1580 END;
1581 Function PARAM_CATEGORY_p return varchar2 is
1582 Begin
1583 return PARAM_CATEGORY;
1584 END;
1585 Function PARAM_BATCH_NAME_p return varchar2 is
1586 Begin
1587 return PARAM_BATCH_NAME;
1588 END;
1589 Function PARAM_LAST_UPDATED_BY_p return varchar2 is
1590 Begin
1591 return PARAM_LAST_UPDATED_BY;
1592 END;
1593 Function PARAM_BALANCING_SEGMENT_p return varchar2 is
1594 Begin
1595 return PARAM_BALANCING_SEGMENT;
1596 END;
1597 Function PARAM_PERIOD_NAME_p return varchar2 is
1598 Begin
1599 return PARAM_PERIOD_NAME;
1600 END;
1601 Function PARAM_BASIS_p return varchar2 is
1602 Begin
1603 return PARAM_BASIS;
1604 END;
1605 Function PARAM_DOC_SEQ_NAME_p return varchar2 is
1606 Begin
1607 return PARAM_DOC_SEQ_NAME;
1608 END;
1609 Function PARAM_ACCT_FROM_p return varchar2 is
1610 Begin
1611 return PARAM_ACCT_FROM;
1612 END;
1613 Function PARAM_ACCT_TO_p return varchar2 is
1614 Begin
1615 return PARAM_ACCT_TO;
1616 END;
1617 Function PARAM_AMT_FLAG_p return varchar2 is
1618 Begin
1619 return PARAM_AMT_FLAG;
1620 END;
1621 Function PARAM_SUB_DOC_SEQ_NAME_p return varchar2 is
1622 Begin
1623 return PARAM_SUB_DOC_SEQ_NAME;
1624 END;
1625 Function PARAM_CONTRA_ACCT_p return varchar2 is
1626 Begin
1627 return PARAM_CONTRA_ACCT;
1628 END;
1629 Function PARAM_LEDGER_NAME_p return varchar2 is
1630 Begin
1631 return PARAM_LEDGER_NAME;
1632 END;
1633 Function PARAM_START_DOC_SEQ_VALUE_p return number is
1634 Begin
1635 return PARAM_START_DOC_SEQ_VALUE;
1636 END;
1637 Function PARAM_END_DOC_SEQ_VALUE_p return number is
1638 Begin
1639 return PARAM_END_DOC_SEQ_VALUE;
1640 END;
1641 Function H_SET_OF_BOOKS_ID_p return number is
1642 Begin
1643 return H_SET_OF_BOOKS_ID;
1644 END;
1645 Function BAL_SEG_NAME_DSP_p return varchar2 is
1646 Begin
1647 return BAL_SEG_NAME_DSP;
1648 END;
1649 Function ACCT_SEG_NAME_DSP_p return varchar2 is
1650 Begin
1651 return ACCT_SEG_NAME_DSP;
1652 END;
1653 Function SELECT_COST_CTR_SEGMENT_p return varchar2 is
1654 Begin
1655 return SELECT_COST_CTR_SEGMENT;
1656 END;
1657 Function H_START_DATE_p return date is
1658 Begin
1659 return H_START_DATE;
1660 END;
1661 Function H_END_DATE_p return date is
1662 Begin
1663 return H_END_DATE;
1664 END;
1665 Function PREV_BAL_SEG_VAL_p return varchar2 is
1666 Begin
1667 return PREV_BAL_SEG_VAL;
1668 END;
1669 Function P_LEDGER_WHERE_p return varchar2 is
1670 Begin
1671 return P_LEDGER_WHERE;
1672 END;
1673 Function P_LEDGER_FROM_p return varchar2 is
1674 Begin
1675 return P_LEDGER_FROM;
1676 END;
1677 Function PREV_ACCT_SEG_VAL_p return varchar2 is
1678 Begin
1679 return PREV_ACCT_SEG_VAL;
1680 END;
1681 Function PREV_SEC_SEG_VAL_p return varchar2 is
1682 Begin
1683 return PREV_SEC_SEG_VAL;
1684 END;
1685 Function PREV_LED_ID_p return number is
1686 Begin
1687 return PREV_LED_ID;
1688 END;
1689 Function PREV_LINE_BAL_p return number is
1690 Begin
1691 return PREV_LINE_BAL;
1692 END;
1693 Function PREV_GL_BAL_p return number is
1694 Begin
1695 return PREV_GL_BAL;
1696 END;
1697 Function P_AFF_WHERE_p return varchar2 is
1698 Begin
1699 return P_AFF_WHERE;
1700 END;
1701 Function P_AMOUNT_WHERE_p return varchar2 is
1702 Begin
1703 return P_AMOUNT_WHERE;
1704 END;
1705 Function P_AFF_WHERE_JRL_p return varchar2 is
1706 Begin
1707 return P_AFF_WHERE_JRL;
1708 END;
1709 Function P_ACC_SEGMENT_SELECT_p return varchar2 is
1710 Begin
1711 return P_ACC_SEGMENT_SELECT;
1712 END;
1713 Function P_JOURNAL_UPD_DATE_WHERE_p return varchar2 is
1714 Begin
1715 return P_JOURNAL_UPD_DATE_WHERE;
1716 END;
1717 Function H_START_UPDATE_DATE_p return date is
1718 Begin
1719 return H_START_UPDATE_DATE;
1720 END;
1721 Function H_END_UPDATE_DATE_p return date is
1722 Begin
1723 return H_END_UPDATE_DATE;
1724 END;
1725 Function P_JOURNAL_LAST_UPD_WHERE_p return varchar2 is
1726 Begin
1727 return P_JOURNAL_LAST_UPD_WHERE;
1728 END;
1729 Function P_JOURNAL_DATE_WHERE_3_p return varchar2 is
1730 Begin
1731 return P_JOURNAL_DATE_WHERE_3;
1732 END;
1733 Function P_HINT_CLAUSE_p return varchar2 is
1734 Begin
1735 return P_HINT_CLAUSE;
1736 END;
1737 Function P_PERF_FROM_p return varchar2 is
1738 Begin
1739 return P_PERF_FROM;
1740 END;
1741 Function P_ACTUAL_TYPE_WHERE_p return varchar2 is
1742 Begin
1743 return P_ACTUAL_TYPE_WHERE;
1744 END;
1745 END GL_GLWACCTR_XMLP_PKG ;
1746
[Home] [Help]