[Home] [Help]
PACKAGE BODY: APPS.JE_JENOGEIS_XMLP_PKG
Source
1 PACKAGE BODY JE_JENOGEIS_XMLP_PKG AS
2 /* $Header: JENOGEISB.pls 120.2 2008/01/11 08:04:05 abraghun 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 BEFOREREPORT RETURN BOOLEAN IS
10 BEGIN
11 EXECUTE IMMEDIATE
12 'alter session set NLS_TERRITORY = AMERICA';
13 BEGIN
14 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
15 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
16 EXCEPTION
17 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
18 BEGIN
19 /*SRW.MESSAGE(100
20 ,'Foundation is not initialised')*/NULL;
21 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
22 END;
23 END;
24 DECLARE
25 XXNO_REQUEST_ID1 NUMBER;
26 COAID NUMBER;
27 SOBNAME VARCHAR2(30);
28 FUNCTCURR VARCHAR2(15);
29 ERRBUF VARCHAR2(132);
30 BEGIN
31 JG_INFO.JG_GET_SET_OF_BOOKS_INFO(P_LEDGER_ID
32 ,COAID
33 ,SOBNAME
34 ,FUNCTCURR
35 ,ERRBUF);
36 IF (ERRBUF IS NOT NULL) THEN
37 /*SRW.MESSAGE('00'
38 ,ERRBUF)*/NULL;
39 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
40 END IF;
41 SELECT
42 PRECISION
43 INTO PRECISION
44 FROM
45 FND_CURRENCIES
46 WHERE CURRENCY_CODE = FUNCTCURR;
47 STRUCT_NUM := COAID;
48 SET_OF_BOOKS_NAME := SOBNAME;
49 FUNC_CURRENCY := FUNCTCURR;
50 P_GEI_VIEW;
51 P_INSERT_PERIOD_NAME(P_PERIOD_NAME
52 ,P_INCOME
53 ,P_EXPENSE);
54 IF P_FINAL = 'Y' THEN
55 XXNO_REQUEST_ID1 := FND_REQUEST.SUBMIT_REQUEST('JE'
56 ,'JENOGEIR'
57 ,'Norwegian GEI Spool S-Report'
58 ,''
59 ,FALSE
60 ,P_FILENAME
61 ,NULL
62 ,NULL
63 ,NULL
64 ,NULL
65 ,NULL
66 ,NULL
67 ,NULL
68 ,NULL
69 ,NULL
70 ,NULL
71 ,NULL
72 ,NULL
73 ,NULL
74 ,NULL
75 ,NULL
76 ,NULL
77 ,NULL
78 ,NULL
79 ,NULL
80 ,NULL
81 ,NULL
82 ,NULL
83 ,NULL
84 ,'N'
85 ,CHR(0)
86 ,''
87 ,''
88 ,''
89 ,''
90 ,''
91 ,''
92 ,''
93 ,''
94 ,''
95 ,''
96 ,''
97 ,''
98 ,''
99 ,''
100 ,''
101 ,''
102 ,''
103 ,''
104 ,''
105 ,''
106 ,''
107 ,''
108 ,''
109 ,''
110 ,''
111 ,''
112 ,''
113 ,''
114 ,''
115 ,''
116 ,''
117 ,''
118 ,''
119 ,''
120 ,''
121 ,''
122 ,''
123 ,''
124 ,''
125 ,''
126 ,''
127 ,''
128 ,''
129 ,''
130 ,''
131 ,''
132 ,''
133 ,''
134 ,''
135 ,''
136 ,''
137 ,''
138 ,''
139 ,''
140 ,''
141 ,''
142 ,''
143 ,''
144 ,''
145 ,''
146 ,''
147 ,''
148 ,''
149 ,''
150 ,''
151 ,''
152 ,''
153 ,''
154 ,''
155 ,''
156 ,''
157 ,''
158 ,''
159 ,'');
160 COMMIT;
161 END IF;
162 END;
163 RETURN (TRUE);
164 END BEFOREREPORT;
165
166 PROCEDURE P_GEI_VIEW IS
167 BEGIN
168 DECLARE
169 V_ACTUAL_FLAG GL_BALANCES.ACTUAL_FLAG%TYPE;
170 V_AUT_TYPE_SEGMENTS GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
171 V_STRENG VARCHAR2(900);
172 C_PEKER INTEGER;
173 V_ACCOUNT_SEGMENTS GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
174 V_DETAIL_TEMPLATE GL_CODE_COMBINATIONS.TEMPLATE_ID%TYPE;
175 BEGIN
176 V_AUT_TYPE_SEGMENTS := NULL;
177 V_ACCOUNT_SEGMENTS := NULL;
178 V_AUT_TYPE_SEGMENTS := FND_PROFILE.VALUE('JENO_GEI_AUT_TYPE');
179 V_ACCOUNT_SEGMENTS := REPLACE(FND_PROFILE.VALUE('JENO_GEI_ACCOUNT_SEGMENT')
180 ,'-'
181 ,'||');
182 V_DETAIL_TEMPLATE := FND_PROFILE.VALUE('JENO_GEI_DETAIL_TEMPL');
183 V_STRENG := 'create or replace view je_no_gei_sreport_v as select gcc.' || V_AUT_TYPE_SEGMENTS || ' authority_type ,gbs.period_name period,gbs.actual_flag,
184 ' || V_ACCOUNT_SEGMENTS || ' account_number,(gbs.period_net_dr - gbs.period_net_cr) balance,gbs.template_id, gbs.period_num, gbs.period_year from gl_code_combinations gcc,
185 gl_balances gbs, gl_sets_of_books gso where gbs.code_combination_id = gcc.code_combination_id
186 and gbs.ledger_id = gso.set_of_books_id and gso.currency_code = gbs.currency_code and gbs.template_id = ' || V_DETAIL_TEMPLATE;
187 EXECUTE IMMEDIATE
188 V_STRENG;
189 END;
190 END P_GEI_VIEW;
191
192 PROCEDURE P_INS_ASCII(P_LINE_NUMBER IN JE_NO_GEI_ASCIIS_T.LINE_NUMBER%TYPE
193 ,P_TEXT IN JE_NO_GEI_ASCIIS_T.TEXT%TYPE) IS
194 BEGIN
195 INSERT INTO JE_NO_GEI_ASCIIS_T(LINE_NUMBER,TEXT)
196 VALUES (P_LINE_NUMBER
197 ,P_TEXT);
198 EXCEPTION
199 WHEN OTHERS THEN
200 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
201 END P_INS_ASCII;
202
203 PROCEDURE P_INSERT_PERIOD_NAME(P_PERIOD_NAME IN GL_BALANCES.PERIOD_NAME%TYPE
204 ,P_ACCOUNT_INCOME IN GL_BALANCES.PERIOD_NET_DR%TYPE
205 ,P_ACCOUNT_EXPENSE IN GL_BALANCES.PERIOD_NET_DR%TYPE) IS
206 TMP_PERIOD_NUM VARCHAR2(2);
207 TMP_PERIOD VARCHAR2(6);
208 TMP_TEXT VARCHAR2(34);
209 T_ORDER_NUMBER VARCHAR2(35);
210 REF_NUM VARCHAR2(4);
211 BALANCE_SUM GL_BALANCES.PERIOD_NET_DR%TYPE;
212 T_CLOS GL_PERIOD_STATUSES.CLOSING_STATUS%TYPE;
213 TMP_NUMBER NUMBER(34);
214 T_PERIOD_YEAR GL_PERIOD_STATUSES.PERIOD_YEAR%TYPE;
215 T_PERIOD_NUM GL_PERIOD_STATUSES.PERIOD_NUM%TYPE;
216 T_SUM_INCOME GL_BALANCES.PERIOD_NET_DR%TYPE;
217 T_SUM_EXPENSE GL_BALANCES.PERIOD_NET_DR%TYPE;
218 T_LINE_NUMBER JE_NO_GEI_ASCIIS_T.LINE_NUMBER%TYPE;
219 T_TEXT JE_NO_GEI_ASCIIS_T.TEXT%TYPE;
220 T_SET_OF_BOOKS GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
221 T_DETAIL_TEMPL GL_BALANCES.TEMPLATE_ID%TYPE;
222 T_INCOME_ACCOUNT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
223 T_EXPENSE_ACCOUNT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
224 PERIOD_NOT_GUILTY EXCEPTION;
225 CURSOR PART_1_CUR IS
226 SELECT
227 JUG.AUTHORITY_TYPE AUT_TYPE,
228 JUG.ACCOUNT_NUMBER ACCOUNT,
229 SUM(NVL(JUG.BALANCE
230 ,0)) BALANCE
231 FROM
232 JE_NO_GEI_SREPORT_V JUG
233 WHERE JUG.PERIOD_NUM <= T_PERIOD_NUM
234 AND JUG.PERIOD_YEAR = T_PERIOD_YEAR
235 AND NVL(JUG.BALANCE
236 ,0) <> 0
237 AND JUG.TEMPLATE_ID = T_DETAIL_TEMPL
238 AND JUG.ACTUAL_FLAG = 'A'
239 GROUP BY
240 JUG.AUTHORITY_TYPE,
241 JUG.ACCOUNT_NUMBER;
242 PART_1_CUR_REC PART_1_CUR%ROWTYPE;
243 CURSOR PART_2_CUR IS
244 SELECT
245 JUG.AUTHORITY_TYPE AUT_TYPE,
246 JUG.ACCOUNT_NUMBER ACCOUNT,
247 SUM(NVL(JUG.BALANCE
248 ,0)) BALANCE
249 FROM
250 JE_NO_GEI_SREPORT_V JUG
251 WHERE JUG.PERIOD_NUM > 12
252 AND JUG.PERIOD_YEAR = T_PERIOD_YEAR
253 AND NVL(JUG.BALANCE
254 ,0) <> 0
255 AND JUG.TEMPLATE_ID = T_DETAIL_TEMPL
256 AND JUG.ACTUAL_FLAG = 'A'
257 GROUP BY
258 JUG.AUTHORITY_TYPE,
259 JUG.ACCOUNT_NUMBER;
260 PART_2_CUR_REC PART_2_CUR%ROWTYPE;
261 BEGIN
262 T_DETAIL_TEMPL := FND_PROFILE.VALUE('JENO_GEI_DETAIL_TEMPL');
263 T_LINE_NUMBER := 0;
264 REF_NUM := NULL;
265 BEGIN
266 T_SET_OF_BOOKS := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
267 SELECT
268 CLOSING_STATUS,
269 PERIOD_YEAR,
270 PERIOD_NUM
271 INTO T_CLOS,T_PERIOD_YEAR,T_PERIOD_NUM
272 FROM
273 GL_PERIOD_STATUSES,
274 GL_SETS_OF_BOOKS
275 WHERE GL_PERIOD_STATUSES.PERIOD_NAME = P_PERIOD_NAME
276 AND GL_PERIOD_STATUSES.APPLICATION_ID = '101'
277 AND GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID = T_SET_OF_BOOKS
278 AND GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID = GL_PERIOD_STATUSES.SET_OF_BOOKS_ID;
279 IF T_PERIOD_NUM > 13 THEN
280 /*SRW.MESSAGE('100'
281 ,'Period is invalid.')*/NULL;
282 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
283 END IF;
284 EXCEPTION
285 WHEN OTHERS THEN
286 /*SRW.MESSAGE('100'
287 ,'Unable to derive closing_st')*/NULL;
288 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
289 END;
290 BEGIN
291 IF T_CLOS = 'P' THEN
292 /*SRW.MESSAGE('100'
293 ,'Period is closed.')*/NULL;
294 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
295 END IF;
296 END;
297 BEGIN
298 DELETE FROM JE_NO_GEI_ASCIIS_T;
299 EXCEPTION
300 WHEN OTHERS THEN
301 /*SRW.MESSAGE('101'
302 ,'Cant delete from JE_NO_GEI_ASCII_S.')*/NULL;
303 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
304 END;
305 BEGIN
306 T_TEXT := NULL;
307 T_LINE_NUMBER := T_LINE_NUMBER + 1;
308 REF_NUM := 'H1 ';
309 T_TEXT := REF_NUM || 'S';
310 EXCEPTION
311 WHEN OTHERS THEN
312 /*SRW.MESSAGE('102'
313 ,'Unable to derive type of report')*/NULL;
314 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
315 END;
316 BEGIN
317 T_TEXT := T_TEXT || LPAD(FND_PROFILE.VALUE('JENO_GEI_ACCOUNTANT_PROFILE')
318 ,9
319 ,'0');
320 EXCEPTION
321 WHEN OTHERS THEN
322 /*SRW.MESSAGE('103'
323 ,'Unable to find ACCOUNTANT_NUMBER.')*/NULL;
324 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
325 END;
326 BEGIN
327 TMP_PERIOD_NUM := TO_CHAR(T_PERIOD_NUM);
328 TMP_PERIOD := T_PERIOD_YEAR || LPAD(TMP_PERIOD_NUM
329 ,2
330 ,'0');
331 IF TMP_PERIOD > TO_CHAR(SYSDATE
332 ,'YYYYMM') THEN
333 RAISE PERIOD_NOT_GUILTY;
334 ELSE
335 T_TEXT := T_TEXT || TMP_PERIOD;
336 END IF;
337 EXCEPTION
338 WHEN PERIOD_NOT_GUILTY THEN
339 /*SRW.MESSAGE('104'
340 ,'Period must be earlier or equal to ' || TO_CHAR(SYSDATE
341 ,'YYYYMM') || '( present date)')*/NULL;
342 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
343 WHEN OTHERS THEN
344 /*SRW.MESSAGE('104'
345 ,'Unable to derive valid period')*/NULL;
346 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
347 END;
348 BEGIN
349 T_TEXT := T_TEXT || TO_CHAR(SYSDATE
350 ,'YYYYMMDDHH24MI');
351 EXCEPTION
352 WHEN OTHERS THEN
353 /*SRW.MESSAGE('105'
354 ,'Unable to derive SYSDATE')*/NULL;
355 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
356 END;
357 BEGIN
358 SELECT
359 JE_NO_GEI_CONTROLS_S.NEXTVAL
360 INTO TMP_NUMBER
361 FROM
362 DUAL;
363 TMP_TEXT := TO_CHAR(TMP_NUMBER);
364 T_ORDER_NUMBER := '1' || LPAD(TMP_TEXT
365 ,34
366 ,'0');
367 T_TEXT := T_TEXT || T_ORDER_NUMBER;
368 EXCEPTION
369 WHEN OTHERS THEN
370 /*SRW.MESSAGE('106'
371 ,'Unable to derive order_number')*/NULL;
372 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
373 END;
374 BEGIN
375 T_TEXT := T_TEXT || LPAD(FND_PROFILE.VALUE('JENO_GEI_ACC_CENT_PROFILE')
376 ,9
377 ,'0');
378 P_INS_ASCII(T_LINE_NUMBER
379 ,T_TEXT);
380 EXCEPTION
381 WHEN OTHERS THEN
382 /*SRW.MESSAGE('107'
383 ,'Unable to derive Central unit')*/NULL;
384 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
385 END;
386 BEGIN
387 IF T_PERIOD_NUM = 13 THEN
388 OPEN PART_2_CUR;
389 LOOP
390 FETCH PART_2_CUR
391 INTO PART_2_CUR_REC;
392 EXIT WHEN PART_2_CUR%NOTFOUND;
393 BEGIN
394 T_TEXT := NULL;
395 T_LINE_NUMBER := T_LINE_NUMBER + 1;
396 REF_NUM := 'S1 ';
397 T_TEXT := REF_NUM || SUBSTR(PART_2_CUR_REC.AUT_TYPE
398 ,1
399 ,2) || RPAD(PART_2_CUR_REC.ACCOUNT
400 ,10
401 ,' ');
402 EXCEPTION
403 WHEN OTHERS THEN
404 IF (PART_2_CUR%ISOPEN) THEN
405 CLOSE PART_2_CUR;
406 END IF;
407 /*SRW.MESSAGE('108'
408 ,'Unable to derive Account')*/NULL;
409 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
410 END;
411 BEGIN
412 T_TEXT := T_TEXT || TO_CHAR(PART_2_CUR_REC.BALANCE
413 ,'S099999999999999.99');
414 P_INS_ASCII(T_LINE_NUMBER
415 ,T_TEXT);
416 EXCEPTION
417 WHEN OTHERS THEN
418 IF (PART_2_CUR%ISOPEN) THEN
419 CLOSE PART_2_CUR;
420 END IF;
421 /*SRW.MESSAGE('109'
422 ,'Unable to derive amount')*/NULL;
423 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
424 END;
425 END LOOP;
426 CLOSE PART_2_CUR;
427 ELSE
428 OPEN PART_1_CUR;
429 LOOP
430 FETCH PART_1_CUR
431 INTO PART_1_CUR_REC;
432 EXIT WHEN PART_1_CUR%NOTFOUND;
433 BEGIN
434 T_TEXT := NULL;
435 T_LINE_NUMBER := T_LINE_NUMBER + 1;
436 REF_NUM := 'S1 ';
437 T_TEXT := REF_NUM || SUBSTR(PART_1_CUR_REC.AUT_TYPE
438 ,1
439 ,2) || RPAD(PART_1_CUR_REC.ACCOUNT
440 ,10
441 ,' ');
442 EXCEPTION
443 WHEN OTHERS THEN
444 IF (PART_1_CUR%ISOPEN) THEN
445 CLOSE PART_1_CUR;
446 END IF;
447 /*SRW.MESSAGE('108'
448 ,'Unable to derive Account')*/NULL;
449 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
450 END;
451 BEGIN
452 T_TEXT := T_TEXT || TO_CHAR(PART_1_CUR_REC.BALANCE
453 ,'S099999999999999.99');
454 P_INS_ASCII(T_LINE_NUMBER
455 ,T_TEXT);
456 EXCEPTION
457 WHEN OTHERS THEN
458 IF (PART_1_CUR%ISOPEN) THEN
459 CLOSE PART_1_CUR;
460 END IF;
461 /*SRW.MESSAGE('110'
462 ,'Unable to derive amount')*/NULL;
463 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
464 END;
465 END LOOP;
466 CLOSE PART_1_CUR;
467 END IF;
468 END;
469 BEGIN
470 T_TEXT := NULL;
471 SELECT
472 SUM(NVL(BALANCE
473 ,0))
474 INTO BALANCE_SUM
475 FROM
476 JE_NO_GEI_SREPORT_V
477 WHERE PERIOD = P_PERIOD_NAME;
478 T_LINE_NUMBER := T_LINE_NUMBER + 1;
479 REF_NUM := 'S2 ';
480 T_TEXT := REF_NUM || TO_CHAR(BALANCE_SUM
481 ,'S099999999999999.99');
482 P_INS_ASCII(T_LINE_NUMBER
483 ,T_TEXT);
484 EXCEPTION
485 WHEN OTHERS THEN
486 /*SRW.MESSAGE('111'
487 ,'Unable to derive balance sum')*/NULL;
488 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
489 END;
490 BEGIN
491 T_TEXT := NULL;
492 T_LINE_NUMBER := T_LINE_NUMBER + 1;
493 REF_NUM := 'S3.1';
494 T_TEXT := REF_NUM || REPLACE(TO_CHAR(P_ACCOUNT_INCOME
495 ,'S099999999999999.99')
496 ,' '
497 ,'');
498 P_INS_ASCII(T_LINE_NUMBER
499 ,T_TEXT);
500 EXCEPTION
501 WHEN OTHERS THEN
502 /*SRW.MESSAGE('112'
503 ,'Unable to derive Income BANK')*/NULL;
504 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
505 END;
506 BEGIN
507 T_INCOME_ACCOUNT := FND_PROFILE.VALUE('JENO_GEI_INCOME_ACCOUNT');
508 T_TEXT := NULL;
509 SELECT
510 NVL(SUM(BALANCE)
511 ,0)
512 INTO T_SUM_INCOME
513 FROM
514 JE_NO_GEI_SREPORT_V JUG
515 WHERE JUG.PERIOD_NUM <= T_PERIOD_NUM
516 AND JUG.PERIOD_YEAR = T_PERIOD_YEAR
517 AND JUG.ACCOUNT_NUMBER = T_INCOME_ACCOUNT
518 AND JUG.TEMPLATE_ID = T_DETAIL_TEMPL;
519 T_LINE_NUMBER := T_LINE_NUMBER + 1;
520 REF_NUM := 'S3.2';
521 T_TEXT := REF_NUM || TO_CHAR(T_SUM_INCOME
522 ,'S099999999999999.99');
523 P_INS_ASCII(T_LINE_NUMBER
524 ,T_TEXT);
525 EXCEPTION
526 WHEN OTHERS THEN
527 /*SRW.MESSAGE('113'
528 ,'Unable to derive Income GL')*/NULL;
529 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
530 END;
531 BEGIN
532 T_TEXT := NULL;
533 T_LINE_NUMBER := T_LINE_NUMBER + 1;
534 REF_NUM := 'S3.3';
535 T_TEXT := REF_NUM || TO_CHAR((T_SUM_INCOME + P_ACCOUNT_INCOME)
536 ,'S099999999999999.99');
537 P_INS_ASCII(T_LINE_NUMBER
538 ,T_TEXT);
539 EXCEPTION
540 WHEN OTHERS THEN
541 /*SRW.MESSAGE('115'
542 ,'Unable to derive Difference in income')*/NULL;
543 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
544 END;
545 BEGIN
546 T_TEXT := NULL;
547 T_LINE_NUMBER := T_LINE_NUMBER + 1;
548 REF_NUM := 'S3.4';
549 T_TEXT := REF_NUM || REPLACE(TO_CHAR(P_ACCOUNT_EXPENSE
550 ,'S099999999999999.99')
551 ,' '
552 ,'');
553 P_INS_ASCII(T_LINE_NUMBER
554 ,T_TEXT);
555 EXCEPTION
556 WHEN OTHERS THEN
557 /*SRW.MESSAGE('115'
558 ,'Unable to derive Expense')*/NULL;
559 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
560 END;
561 BEGIN
562 T_EXPENSE_ACCOUNT := FND_PROFILE.VALUE('JENO_GEI_EXPENSE_ACCOUNT');
563 T_TEXT := NULL;
564 SELECT
565 NVL(SUM(BALANCE)
566 ,0)
567 INTO T_SUM_EXPENSE
568 FROM
569 JE_NO_GEI_SREPORT_V JUG
570 WHERE JUG.PERIOD_NUM <= T_PERIOD_NUM
571 AND JUG.PERIOD_YEAR = T_PERIOD_YEAR
572 AND JUG.ACCOUNT_NUMBER = T_EXPENSE_ACCOUNT
573 AND JUG.TEMPLATE_ID = T_DETAIL_TEMPL;
574 T_LINE_NUMBER := T_LINE_NUMBER + 1;
575 REF_NUM := 'S3.5';
576 T_TEXT := REF_NUM || TO_CHAR(T_SUM_EXPENSE
577 ,'S099999999999999.99');
578 P_INS_ASCII(T_LINE_NUMBER
579 ,T_TEXT);
580 EXCEPTION
581 WHEN OTHERS THEN
582 /*SRW.MESSAGE('116'
583 ,'Unable to derive Expense GL')*/NULL;
584 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
585 END;
586 BEGIN
587 T_TEXT := NULL;
588 T_LINE_NUMBER := T_LINE_NUMBER + 1;
589 REF_NUM := 'S3.6';
590 T_TEXT := REF_NUM || TO_CHAR((T_SUM_EXPENSE + P_ACCOUNT_EXPENSE)
591 ,'S099999999999999.99');
592 P_INS_ASCII(T_LINE_NUMBER
593 ,T_TEXT);
594 EXCEPTION
595 WHEN OTHERS THEN
596 /*SRW.MESSAGE('117'
597 ,'Unable to derive BANK')*/NULL;
598 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
599 DBMS_SESSION.SET_SQL_TRACE(FALSE);
600 END;
601 BEGIN
602 INSERT INTO JE_NO_GEI_CONTROLS
603 (ORDER_NUMBER
604 ,CREATED_BY
605 ,CREATION_DATE
606 ,LAST_UPDATED_BY
607 ,LAST_UPDATE_DATE
608 ,LAST_UPDATE_LOGIN)
609 VALUES (TO_NUMBER(T_ORDER_NUMBER)
610 ,FND_GLOBAL.USER_ID
611 ,SYSDATE
612 ,FND_GLOBAL.USER_ID
613 ,SYSDATE
614 ,FND_GLOBAL.LOGIN_ID);
615 EXCEPTION
616 WHEN OTHERS THEN
617 /*SRW.MESSAGE('118'
618 ,'Unable to assign values to JE_NO_GEI_CONTROLS')*/NULL;
619 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
620 END;
621 COMMIT;
622 END P_INSERT_PERIOD_NAME;
623
624 FUNCTION CF_DATEFORMULA RETURN CHAR IS
625 BEGIN
626 DECLARE
627 V_DATE DATE;
628 BEGIN
629 SELECT
630 FND_DATE.DATE_TO_CHARDATE(SYSDATE)
631 INTO V_DATE
632 FROM
633 DUAL;
634 RETURN (V_DATE);
635 END;
636 END CF_DATEFORMULA;
637
638 FUNCTION CF_REPORT_TITLEFORMULA RETURN CHAR IS
639 BEGIN
640 DECLARE
641 L_REPORT_NAME VARCHAR2(80);
642 BEGIN
643 SELECT
644 CP.USER_CONCURRENT_PROGRAM_NAME
645 INTO L_REPORT_NAME
646 FROM
647 FND_CONCURRENT_PROGRAMS_VL CP,
648 FND_APPLICATION_VL AP
649 WHERE AP.APPLICATION_ID = CP.APPLICATION_ID
650 AND CP.CONCURRENT_PROGRAM_NAME = 'JENOGEIS'
651 AND AP.APPLICATION_SHORT_NAME = 'JE';
652 RETURN (L_REPORT_NAME);
653 EXCEPTION
654 WHEN NO_DATA_FOUND THEN
655 L_REPORT_NAME := 'S-Report';
656 RETURN ('S-Report');
657 END;
658 END CF_REPORT_TITLEFORMULA;
659
660 FUNCTION STRUCT_NUM_P RETURN VARCHAR2 IS
661 BEGIN
662 RETURN STRUCT_NUM;
663 END STRUCT_NUM_P;
664
665 FUNCTION SET_OF_BOOKS_NAME_P RETURN VARCHAR2 IS
666 BEGIN
667 RETURN SET_OF_BOOKS_NAME;
668 END SET_OF_BOOKS_NAME_P;
669
670 FUNCTION SELECT_ALL_P RETURN VARCHAR2 IS
671 BEGIN
672 RETURN SELECT_ALL;
673 END SELECT_ALL_P;
674
675 FUNCTION WHERE_FLEX_P RETURN VARCHAR2 IS
676 BEGIN
677 RETURN WHERE_FLEX;
678 END WHERE_FLEX_P;
679
680 FUNCTION ORDERBY_ACCT_P RETURN VARCHAR2 IS
681 BEGIN
682 RETURN ORDERBY_ACCT;
683 END ORDERBY_ACCT_P;
684
685 FUNCTION PRECISION_P RETURN NUMBER IS
686 BEGIN
687 RETURN PRECISION;
688 END PRECISION_P;
689
690 FUNCTION FUNC_CURRENCY_P RETURN VARCHAR2 IS
691 BEGIN
692 RETURN FUNC_CURRENCY;
693 END FUNC_CURRENCY_P;
694
695 END JE_JENOGEIS_XMLP_PKG;
696
697
698