[Home] [Help]
PACKAGE BODY: APPS.JE_JEHUFAMD_XMLP_PKG
Source
1 PACKAGE BODY JE_JEHUFAMD_XMLP_PKG AS
2 /* $Header: JEHUFAMDB.pls 120.1 2007/12/25 16:53:15 dwkrishn noship $ */
3 FUNCTION REPORT_NAMEFORMULA(COMPANY_NAME IN VARCHAR2) RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 L_REPORT_NAME VARCHAR2(80);
7 L_CONC_PROGRAM_ID NUMBER;
8 BEGIN
9 RP_COMPANY_NAME := COMPANY_NAME;
10 SELECT
11 CR.CONCURRENT_PROGRAM_ID
12 INTO L_CONC_PROGRAM_ID
13 FROM
14 FND_CONCURRENT_REQUESTS CR
15 WHERE CR.PROGRAM_APPLICATION_ID = 7002
16 AND CR.REQUEST_ID = P_CONC_REQUEST_ID;
17 SELECT
18 CP.USER_CONCURRENT_PROGRAM_NAME
19 INTO L_REPORT_NAME
20 FROM
21 FND_CONCURRENT_PROGRAMS_VL CP
22 WHERE CP.CONCURRENT_PROGRAM_ID = L_CONC_PROGRAM_ID
23 AND CP.APPLICATION_ID = 7002;
24 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
25 RETURN (L_REPORT_NAME);
26 EXCEPTION
27 WHEN OTHERS THEN
28 IF (P_REPORT_TYPE = 'REVAL RESERVE') THEN
29 RP_REPORT_NAME := ':Revaluation Reserve Summary Report:';
30 ELSE
31 IF (P_REPORT_TYPE = 'RESERVE') THEN
32 RP_REPORT_NAME := ':Reserve Summary Report:';
33 ELSE
34 RP_REPORT_NAME := 'REPORT ERROR';
35 END IF;
36 END IF;
37 RETURN (RP_REPORT_NAME);
38 END;
39 RETURN NULL;
40 END REPORT_NAMEFORMULA;
41
42 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
43 BEGIN
44 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
45 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
46 SELECT
47 PERIOD_CLOSE_DATE
48 INTO CP_END_DATE
49 FROM
50 FA_DEPRN_PERIODS
51 WHERE BOOK_TYPE_CODE = P_BOOK
52 AND PERIOD_NAME = P_PERIOD2;
53 IF CP_END_DATE IS NULL THEN
54 CP_END_DATE_OPEN := 'YES';
55 ELSE
56 CP_END_DATE_OPEN := 'NO';
57 END IF;
58 RETURN (TRUE);
59 END BEFOREREPORT;
60
61 FUNCTION PERIOD1_PCFORMULA RETURN NUMBER IS
62 BEGIN
63 DECLARE
64 L_PERIOD_POD DATE;
65 L_PERIOD_PCD DATE;
66 L_PERIOD_PC NUMBER(15);
67 L_PERIOD_FY NUMBER(15);
68 BEGIN
69 SELECT
70 PERIOD_COUNTER,
71 PERIOD_OPEN_DATE,
72 NVL(PERIOD_CLOSE_DATE
73 ,SYSDATE),
74 FISCAL_YEAR
75 INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
76 FROM
77 FA_DEPRN_PERIODS
78 WHERE BOOK_TYPE_CODE = P_BOOK
79 AND PERIOD_NAME = P_PERIOD1;
80 PERIOD1_POD := L_PERIOD_POD;
81 PERIOD1_PCD := L_PERIOD_PCD;
82 PERIOD1_FY := L_PERIOD_FY;
83 RETURN (L_PERIOD_PC);
84 END;
85 RETURN NULL;
86 END PERIOD1_PCFORMULA;
87
88 FUNCTION PERIOD2_PCFORMULA RETURN NUMBER IS
89 BEGIN
90 DECLARE
91 L_PERIOD_POD DATE;
92 L_PERIOD_PCD DATE;
93 L_PERIOD_PC NUMBER(15);
94 L_PERIOD_FY NUMBER(15);
95 BEGIN
96 SELECT
97 PERIOD_COUNTER,
98 PERIOD_OPEN_DATE,
99 NVL(PERIOD_CLOSE_DATE
100 ,SYSDATE),
101 FISCAL_YEAR
102 INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
103 FROM
104 FA_DEPRN_PERIODS
105 WHERE BOOK_TYPE_CODE = P_BOOK
106 AND PERIOD_NAME = P_PERIOD2;
107 PERIOD2_POD := L_PERIOD_POD;
108 PERIOD2_PCD := L_PERIOD_PCD;
109 PERIOD2_FY := L_PERIOD_FY;
110 RETURN (L_PERIOD_PC);
111 END;
112 RETURN NULL;
113 END PERIOD2_PCFORMULA;
114
115 FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
116
117 BEGIN
118 BEGIN
119 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
120 INSERT_INFO;
121 RETURN (1);
122 ELSE
123 RETURN (0);
124 END IF;
125
126 END;
127 RETURN NULL;
128 END DO_INSERTFORMULA;
129
130 FUNCTION OUT_OF_BALANCEFORMULA(BEGIN1 IN NUMBER
131 ,ADDITION IN NUMBER
132 ,DEPRECIATION IN NUMBER
133 ,RECLASS IN NUMBER
134 ,RETIREMENT IN NUMBER
135 ,ADJUST IN NUMBER
136 ,TRANSFER IN NUMBER
137 ,END1 IN NUMBER) RETURN VARCHAR2 IS
138 BEGIN
139 DECLARE
140 MOCK_TOTAL NUMBER;
141 BEGIN
142 MOCK_TOTAL := NVL(BEGIN1
143 ,0) + NVL(ADDITION
144 ,0) + NVL(DEPRECIATION
145 ,0) + NVL(RECLASS
146 ,0) - NVL(RETIREMENT
147 ,0) + NVL(ADJUST
148 ,0) + NVL(TRANSFER
149 ,0);
150 IF (MOCK_TOTAL = NVL(END1
151 ,0)) THEN
152 RETURN (' ');
153 ELSE
154 RETURN ('*');
155 END IF;
156 END;
157 RETURN NULL;
158 END OUT_OF_BALANCEFORMULA;
159
160 FUNCTION ACCT_OUT_OF_BALANCEFORMULA(ACCT_BEGIN IN NUMBER
161 ,ACCT_ADD IN NUMBER
162 ,ACCT_DEPRN IN NUMBER
163 ,ACCT_RECLASS IN NUMBER
164 ,ACCT_RETIRE IN NUMBER
165 ,ACCT_ADJUST IN NUMBER
166 ,ACCT_TRANS IN NUMBER
167 ,ACCT_END IN NUMBER) RETURN VARCHAR2 IS
168 BEGIN
169 DECLARE
170 MOCK_TOTAL NUMBER;
171 BEGIN
172 MOCK_TOTAL := NVL(ACCT_BEGIN
173 ,0) + NVL(ACCT_ADD
174 ,0) + NVL(ACCT_DEPRN
175 ,0) + NVL(ACCT_RECLASS
176 ,0) - NVL(ACCT_RETIRE
177 ,0) + NVL(ACCT_ADJUST
178 ,0) + NVL(ACCT_TRANS
179 ,0);
180 IF (MOCK_TOTAL = NVL(ACCT_END
181 ,0)) THEN
182 RETURN (' ');
183 ELSE
184 RETURN ('*');
185 END IF;
186 END;
187 RETURN NULL;
188 END ACCT_OUT_OF_BALANCEFORMULA;
189
190 FUNCTION BAL_OUT_OF_BALANCEFORMULA(BAL_BEGIN IN NUMBER
191 ,BAL_ADD IN NUMBER
192 ,BAL_DEPRN IN NUMBER
193 ,BAL_RECLASS IN NUMBER
194 ,BAL_RETIRE IN NUMBER
195 ,BAL_ADJUST IN NUMBER
196 ,BAL_TRANS IN NUMBER
197 ,BAL_END IN NUMBER) RETURN VARCHAR2 IS
198 BEGIN
199 DECLARE
200 MOCK_TOTAL NUMBER;
201 BEGIN
202 MOCK_TOTAL := NVL(BAL_BEGIN
203 ,0) + NVL(BAL_ADD
204 ,0) + NVL(BAL_DEPRN
205 ,0) + NVL(BAL_RECLASS
206 ,0) - NVL(BAL_RETIRE
207 ,0) + NVL(BAL_ADJUST
208 ,0) + NVL(BAL_TRANS
209 ,0);
210 IF (MOCK_TOTAL = NVL(BAL_END
211 ,0)) THEN
212 RETURN (' ');
213 ELSE
214 RETURN ('*');
215 END IF;
216 END;
217 RETURN NULL;
218 END BAL_OUT_OF_BALANCEFORMULA;
219
220 FUNCTION ADJUSTFORMULA(TAX IN NUMBER
221 ,REVALUATION IN NUMBER) RETURN NUMBER IS
222 BEGIN
223 BEGIN
224 RETURN (NVL(TAX
225 ,0) + NVL(REVALUATION
226 ,0));
227 END;
228 RETURN NULL;
229 END ADJUSTFORMULA;
230
231 FUNCTION AFTERREPORT RETURN BOOLEAN IS
232 BEGIN
233 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
234 BEGIN
235 ROLLBACK;
236 EXCEPTION
237 WHEN OTHERS THEN
238 NULL;
239 END;
240 RETURN (TRUE);
241 END AFTERREPORT;
242
243 FUNCTION CF_REPORT_DATEFORMULA RETURN CHAR IS
244 BEGIN
245 RETURN (FND_DATE.DATE_TO_CHARDT(SYSDATE));
246 END CF_REPORT_DATEFORMULA;
247
248 PROCEDURE GET_ADJUSTMENTS IS
249 BEGIN
250 INSERT INTO JE_HU_BALANCES_REPORT
251 (CATEGORY_ID
252 ,ASSET_ID
253 ,DISTRIBUTION_CCID
254 ,ADJUSTMENT_CCID
255 ,CATEGORY_BOOKS_ACCOUNT
256 ,SOURCE_TYPE_CODE
257 ,AMOUNT)
258 SELECT
259 AH.CATEGORY_ID,
260 AH.ASSET_ID,
261 DH.CODE_COMBINATION_ID,
262 XAL.CODE_COMBINATION_ID,
263 null,
264 AJ.SOURCE_TYPE_CODE,
265 SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
266 ,CP_BALANCE_TYPE
267 ,1
268 ,-1) * AJ.ADJUSTMENT_AMOUNT)
269 FROM
270 FA_LOOKUPS RT,
271 FA_DISTRIBUTION_HISTORY DH,
272 FA_TRANSACTION_HEADERS TH,
273 FA_ASSET_HISTORY AH,
274 FA_ADJUSTMENTS AJ,
275 XLA_AE_LINES XAL,
276 XLA_DISTRIBUTION_LINKS XDL
277 WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
278 AND RT.LOOKUP_CODE = P_REPORT_TYPE
279 AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
280 AND AJ.ASSET_ID = DH.ASSET_ID
281 AND AJ.BOOK_TYPE_CODE = P_BOOK
282 AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
283 AND AJ.ADJUSTMENT_TYPE in ( P_REPORT_TYPE , DECODE(P_REPORT_TYPE
284 ,'REVAL RESERVE'
285 ,'REVAL AMORT') )
286 AND AJ.PERIOD_COUNTER_CREATED BETWEEN CP_PERIOD1_PC
287 AND CP_PERIOD2_PC
288 AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
289 AND AH.ASSET_ID = DH.ASSET_ID
290 AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
291 AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
292 OR ( AH.ASSET_TYPE = 'CAPITALIZED'
293 AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
294 AND ( ( AJ.SOURCE_TYPE_CODE <> 'RECLASS'
295 AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
296 AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
297 ,TH.TRANSACTION_HEADER_ID) )
298 OR ( AJ.SOURCE_TYPE_CODE = 'RECLASS'
299 AND DECODE(AJ.DEBIT_CREDIT_FLAG
300 ,'DR'
301 ,AH.TRANSACTION_HEADER_ID_OUT
302 ,'CR'
303 ,AH.TRANSACTION_HEADER_ID_IN) = TH.TRANSACTION_HEADER_ID ) )
304 AND ( DECODE(RT.LOOKUP_CODE
305 ,AJ.ADJUSTMENT_TYPE
306 ,1
307 ,0) * AJ.ADJUSTMENT_AMOUNT ) <> 0
308 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AJ.TRANSACTION_HEADER_ID
309 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = AJ.ADJUSTMENT_LINE_ID
310 AND XDL.APPLICATION_ID = 140
311 AND XDL.EVENT_ID = TH.EVENT_ID
312 AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
313 AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
314 AND XAL.APPLICATION_ID = 140
315 AND XDL.SOURCE_DISTRIBUTION_TYPE = 'TRX'
316 GROUP BY
317 AH.CATEGORY_ID,
318 AH.ASSET_ID,
319 DH.CODE_COMBINATION_ID,
320 XAL.CODE_COMBINATION_ID,
321 AJ.SOURCE_TYPE_CODE;
322 IF P_REPORT_TYPE = 'RESERVE' THEN
323 INSERT INTO JE_HU_BALANCES_REPORT
324 (CATEGORY_ID
325 ,ASSET_ID
326 ,DISTRIBUTION_CCID
327 ,ADJUSTMENT_CCID
328 ,CATEGORY_BOOKS_ACCOUNT
329 ,SOURCE_TYPE_CODE
330 ,AMOUNT)
331 SELECT
332 CB.CATEGORY_ID,
333 DH.ASSET_ID,
334 DH.CODE_COMBINATION_ID,
335 null,
336 CB.DEPRN_RESERVE_ACCT,
337 'ADDITION',
338 SUM(DD.DEPRN_RESERVE)
339 FROM
340 FA_DISTRIBUTION_HISTORY DH,
344 WHERE NOT EXISTS (
341 FA_CATEGORY_BOOKS CB,
342 FA_ADDITIONS AD,
343 FA_DEPRN_DETAIL DD
345 SELECT
346 BR.ASSET_ID
347 FROM
348 JE_HU_BALANCES_REPORT BR
349 WHERE BR.ASSET_ID = DH.ASSET_ID
350 AND BR.DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
351 AND BR.SOURCE_TYPE_CODE = 'ADDITION' )
352 AND DD.BOOK_TYPE_CODE = P_BOOK
353 AND ( DD.PERIOD_COUNTER + 1 ) BETWEEN CP_PERIOD1_PC
354 AND CP_PERIOD2_PC
355 AND DD.DEPRN_SOURCE_CODE = 'B'
356 AND DD.ASSET_ID = DH.ASSET_ID
357 AND DD.DEPRN_RESERVE <> 0
358 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
359 AND DD.ASSET_ID = AD.ASSET_ID
360 AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
361 AND AD.ASSET_CATEGORY_ID = CB.CATEGORY_ID
362 GROUP BY
363 CB.CATEGORY_ID,
364 DH.ASSET_ID,
365 DH.CODE_COMBINATION_ID,
366 CB.DEPRN_RESERVE_ACCT;
367 END IF;
368 END GET_ADJUSTMENTS;
369
370 PROCEDURE GET_DEPRN_EFFECTS IS
371 BEGIN
372 INSERT INTO JE_HU_BALANCES_REPORT
373 (CATEGORY_ID
374 ,ASSET_ID
375 ,DISTRIBUTION_CCID
376 ,ADJUSTMENT_CCID
377 ,CATEGORY_BOOKS_ACCOUNT
378 ,SOURCE_TYPE_CODE
379 ,AMOUNT)
380 SELECT
381 AH.CATEGORY_ID,
382 AH.ASSET_ID,
383 DH.CODE_COMBINATION_ID,
384 null,
385 DECODE(RT.LOOKUP_CODE
386 ,'RESERVE'
387 ,CB.DEPRN_RESERVE_ACCT
388 ,'REVAL RESERVE'
389 ,CB.REVAL_RESERVE_ACCT),
390 DECODE(DD.DEPRN_SOURCE_CODE
391 ,'D'
392 ,'DEPRECIATION'
393 ,'ADDITION'),
394 SUM(DECODE(RT.LOOKUP_CODE
395 ,'RESERVE'
396 ,DD.DEPRN_AMOUNT
397 ,'REVAL RESERVE'
398 ,-DD.REVAL_AMORTIZATION))
399 FROM
400 FA_LOOKUPS RT,
401 FA_CATEGORY_BOOKS CB,
402 FA_DISTRIBUTION_HISTORY DH,
403 FA_ASSET_HISTORY AH,
404 FA_DEPRN_DETAIL DD,
405 FA_DEPRN_PERIODS DP
406 WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
407 AND AH.ASSET_ID = DH.ASSET_ID
408 AND AH.ASSET_TYPE = 'CAPITALIZED'
409 AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
410 ,SYSDATE)
411 AND NVL(DH.DATE_INEFFECTIVE
412 ,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
413 ,SYSDATE)
414 AND CB.CATEGORY_ID = AH.CATEGORY_ID
415 AND CB.BOOK_TYPE_CODE = P_BOOK
416 AND ( ( DD.DEPRN_SOURCE_CODE = 'B'
417 AND ( DD.PERIOD_COUNTER + 1 ) < CP_PERIOD2_PC )
418 OR ( DD.DEPRN_SOURCE_CODE = 'D' ) )
419 AND DD.BOOK_TYPE_CODE || '' = P_BOOK
420 AND DD.ASSET_ID = DH.ASSET_ID
421 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
422 AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
423 AND CP_PERIOD2_PC
424 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
425 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
426 AND DECODE(RT.LOOKUP_CODE
427 ,'RESERVE'
428 ,CB.DEPRN_RESERVE_ACCT
429 ,'REVAL RESERVE'
430 ,CB.REVAL_RESERVE_ACCT) is not null
431 AND DECODE(RT.LOOKUP_CODE
432 ,'RESERVE'
433 ,DD.DEPRN_AMOUNT
434 ,'REVAL RESERVE'
435 ,NVL(DD.REVAL_AMORTIZATION
436 ,0)) <> 0
437 AND RT.LOOKUP_TYPE = 'REPORT TYPE'
438 GROUP BY
439 AH.CATEGORY_ID,
440 AH.ASSET_ID,
441 DH.CODE_COMBINATION_ID,
442 DECODE(RT.LOOKUP_CODE
443 ,'RESERVE'
444 ,CB.DEPRN_RESERVE_ACCT
445 ,'REVAL RESERVE'
446 ,CB.REVAL_RESERVE_ACCT),
447 DD.DEPRN_SOURCE_CODE;
448 END GET_DEPRN_EFFECTS;
449
450 PROCEDURE VERSION IS
451 BEGIN
452 CP_FDRCSID := '$Header: JEHUFAMDB.pls 120.1 2007/12/25 16:53:15 dwkrishn noship $';
453 END VERSION;
454
455 PROCEDURE INSERT_INFO IS
456 PRAGMA AUTONOMOUS_TRANSACTION;
457 BEGIN
458 SELECT
459 P1.PERIOD_COUNTER,
460 P1.PERIOD_OPEN_DATE,
461 NVL(P1.PERIOD_CLOSE_DATE
462 ,BC.LAST_DEPRN_RUN_DATE),
463 P2.PERIOD_COUNTER,
464 NVL(P2.PERIOD_CLOSE_DATE
465 ,BC.LAST_DEPRN_RUN_DATE),
466 BC.DISTRIBUTION_SOURCE_BOOK
467 INTO CP_PERIOD1_PC,CP_PERIOD1_POD,CP_PERIOD1_PCD,CP_PERIOD2_PC,CP_PERIOD2_PCD,CP_DISTRIBUTION_SOURCE_BOOK
468 FROM
469 FA_DEPRN_PERIODS P1,
470 FA_DEPRN_PERIODS P2,
471 FA_BOOK_CONTROLS BC
472 WHERE BC.BOOK_TYPE_CODE = P_BOOK
473 AND P1.BOOK_TYPE_CODE = P_BOOK
474 AND P1.PERIOD_NAME = P_PERIOD1
475 AND P2.BOOK_TYPE_CODE = P_BOOK
476 AND P2.PERIOD_NAME = P_PERIOD2;
477 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
478 CP_BALANCE_TYPE := 'CR';
479 ELSE
480 CP_BALANCE_TYPE := 'DR';
481 END IF;
482 DELETE FROM JE_HU_BALANCES_REPORT;
483 DELETE FROM FA_LOOKUPS_B
484 WHERE LOOKUP_TYPE = 'REPORT TYPE';
485 DELETE FROM FA_LOOKUPS_TL
486 WHERE LOOKUP_TYPE = 'REPORT TYPE';
487 INSERT INTO FA_LOOKUPS_B
488 (LOOKUP_TYPE
489 ,LOOKUP_CODE
490 ,LAST_UPDATED_BY
491 ,LAST_UPDATE_DATE
492 ,ENABLED_FLAG)
493 VALUES ('REPORT TYPE'
494 ,P_REPORT_TYPE
495 ,1
496 ,SYSDATE
497 ,'Y');
498 INSERT INTO FA_LOOKUPS_TL
499 (LOOKUP_TYPE
500 ,LOOKUP_CODE
501 ,LANGUAGE
502 ,SOURCE_LANG
503 ,MEANING
504 ,LAST_UPDATED_BY
505 ,LAST_UPDATE_DATE)
506 SELECT
507 'REPORT TYPE',
508 P_REPORT_TYPE,
509 L.LANGUAGE_CODE,
510 USERENV('LANG'),
511 P_REPORT_TYPE,
512 1,
513 SYSDATE
514 FROM
515 FND_LANGUAGES L
516 WHERE L.INSTALLED_FLAG in ( 'I' , 'B' );
517 CP_BEGIN_OR_END := 'BEGIN';
518 CP_PERIOD_PC := CP_PERIOD1_PC - 1;
519 CP_PERIOD_POD := CP_PERIOD1_POD;
520 CP_PERIOD_PCD := CP_PERIOD1_PCD;
521 GET_BALANCE;
522 CP_BEGIN_OR_END := 'END';
523 CP_PERIOD_PC := CP_PERIOD2_PC;
524 CP_PERIOD_POD := CP_PERIOD2_PCD;
525 CP_PERIOD_PCD := CP_PERIOD2_PCD;
526 GET_BALANCE;
527 GET_ADJUSTMENTS;
528 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
529 GET_DEPRN_EFFECTS;
530 END IF;
531 COMMIT;
532 END INSERT_INFO;
533
534 PROCEDURE GET_BALANCE IS
535 BEGIN
536 INSERT INTO JE_HU_BALANCES_REPORT
537 (CATEGORY_ID
538 ,ASSET_ID
539 ,DISTRIBUTION_CCID
540 ,ADJUSTMENT_CCID
541 ,CATEGORY_BOOKS_ACCOUNT
542 ,SOURCE_TYPE_CODE
543 ,AMOUNT)
544 SELECT
545 AH.CATEGORY_ID,
546 AH.ASSET_ID,
547 DH.CODE_COMBINATION_ID,
548 null,
549 DECODE(P_REPORT_TYPE
550 ,'COST'
551 ,CB.ASSET_COST_ACCT
552 ,'CIP COST'
553 ,CB.CIP_COST_ACCT
554 ,'RESERVE'
555 ,CB.DEPRN_RESERVE_ACCT
556 ,'REVAL RESERVE'
557 ,CB.REVAL_RESERVE_ACCT),
558 DECODE(P_REPORT_TYPE
559 ,'RESERVE'
560 ,DECODE(DD.DEPRN_SOURCE_CODE
561 ,'D'
562 ,CP_BEGIN_OR_END
563 ,'ADDITION')
564 ,'REVAL RESERVE'
565 ,DECODE(DD.DEPRN_SOURCE_CODE
566 ,'D'
567 ,CP_BEGIN_OR_END
568 ,'ADDITION')
569 ,CP_BEGIN_OR_END),
570 DECODE(P_REPORT_TYPE
571 ,'COST'
572 ,DD.COST
573 ,'CIP COST'
574 ,DD.COST
575 ,'RESERVE'
576 ,DD.DEPRN_RESERVE
577 ,'REVAL RESERVE'
578 ,DD.REVAL_RESERVE)
579 FROM
580 FA_BOOKS BK,
581 FA_CATEGORY_BOOKS CB,
582 FA_ASSET_HISTORY AH,
583 FA_DEPRN_DETAIL DD,
584 FA_DISTRIBUTION_HISTORY DH
585 WHERE DH.BOOK_TYPE_CODE || '' = CP_DISTRIBUTION_SOURCE_BOOK
586 AND DECODE(DD.DEPRN_SOURCE_CODE
587 ,'D'
588 ,CP_PERIOD_POD
589 ,CP_PERIOD_PCD) BETWEEN DH.DATE_EFFECTIVE
590 AND NVL(DH.DATE_INEFFECTIVE
591 ,SYSDATE)
592 AND DD.ASSET_ID = DH.ASSET_ID + 0
593 AND DD.BOOK_TYPE_CODE = P_BOOK
594 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0
595 AND DD.PERIOD_COUNTER <= CP_PERIOD_PC
596 AND DECODE(CP_BEGIN_OR_END
597 ,'BEGIN'
598 ,DD.DEPRN_SOURCE_CODE
599 ,'D') = DD.DEPRN_SOURCE_CODE
600 AND DD.PERIOD_COUNTER = (
601 SELECT
602 MAX(SUB_DD.PERIOD_COUNTER)
603 FROM
604 FA_DEPRN_DETAIL SUB_DD
605 WHERE SUB_DD.BOOK_TYPE_CODE = P_BOOK
606 AND SUB_DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0
607 AND SUB_DD.PERIOD_COUNTER <= CP_PERIOD_PC )
608 AND AH.ASSET_ID = DH.ASSET_ID + 0
609 AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
610 AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
611 OR ( AH.ASSET_TYPE = 'CAPITALIZED'
612 AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
613 AND DECODE(DD.DEPRN_SOURCE_CODE
614 ,'D'
615 ,CP_PERIOD_POD
616 ,CP_PERIOD_PCD) BETWEEN AH.DATE_EFFECTIVE
617 AND NVL(AH.DATE_INEFFECTIVE
618 ,SYSDATE)
619 AND CB.CATEGORY_ID = AH.CATEGORY_ID
620 AND CB.BOOK_TYPE_CODE = P_BOOK
621 AND BK.BOOK_TYPE_CODE = P_BOOK
622 AND BK.ASSET_ID = DD.ASSET_ID
623 AND DECODE(DD.DEPRN_SOURCE_CODE
624 ,'D'
625 ,CP_PERIOD_POD
626 ,CP_PERIOD_PCD) BETWEEN BK.DATE_EFFECTIVE
627 AND NVL(BK.DATE_INEFFECTIVE
628 ,SYSDATE)
629 AND NVL(BK.PERIOD_COUNTER_FULLY_RETIRED
630 ,CP_PERIOD_PC + 1) > CP_PERIOD1_PC - 1
631 AND DECODE(P_REPORT_TYPE
632 ,'COST'
633 ,DECODE(AH.ASSET_TYPE
634 ,'CAPITALIZED'
635 ,CB.ASSET_COST_ACCT
636 ,NULL)
637 ,'CIP COST'
638 ,DECODE(AH.ASSET_TYPE
639 ,'CIP'
640 ,CB.CIP_COST_ACCT
641 ,NULL)
642 ,'RESERVE'
643 ,CB.DEPRN_RESERVE_ACCT
644 ,'REVAL RESERVE'
645 ,CB.REVAL_RESERVE_ACCT) is not null;
646 END GET_BALANCE;
647
648 FUNCTION ACCT_BAL_APROMPT_P RETURN VARCHAR2 IS
649 BEGIN
650 RETURN ACCT_BAL_APROMPT;
651 END ACCT_BAL_APROMPT_P;
652
653 FUNCTION ACCT_CC_APROMPT_P RETURN VARCHAR2 IS
654 BEGIN
655 RETURN ACCT_CC_APROMPT;
656 END ACCT_CC_APROMPT_P;
657
658 FUNCTION CAT_MAJ_RPROMPT_P RETURN VARCHAR2 IS
659 BEGIN
660 RETURN CAT_MAJ_RPROMPT;
661 END CAT_MAJ_RPROMPT_P;
662
663 FUNCTION PERIOD1_POD_P RETURN DATE IS
664 BEGIN
665 RETURN PERIOD1_POD;
666 END PERIOD1_POD_P;
667
668 FUNCTION PERIOD1_PCD_P RETURN DATE IS
669 BEGIN
670 RETURN PERIOD1_PCD;
671 END PERIOD1_PCD_P;
672
673 FUNCTION PERIOD1_FY_P RETURN NUMBER IS
674 BEGIN
675 RETURN PERIOD1_FY;
676 END PERIOD1_FY_P;
677
678 FUNCTION PERIOD2_POD_P RETURN DATE IS
679 BEGIN
680 RETURN PERIOD2_POD;
681 END PERIOD2_POD_P;
682
683 FUNCTION PERIOD2_PCD_P RETURN DATE IS
684 BEGIN
685 RETURN PERIOD2_PCD;
686 END PERIOD2_PCD_P;
687
688 FUNCTION PERIOD2_FY_P RETURN NUMBER IS
689 BEGIN
690 RETURN PERIOD2_FY;
691 END PERIOD2_FY_P;
692
693 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
694 BEGIN
695 RETURN RP_COMPANY_NAME;
696 END RP_COMPANY_NAME_P;
697
698 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
699 BEGIN
700 RETURN RP_REPORT_NAME;
701 END RP_REPORT_NAME_P;
702
703 FUNCTION RP_BAL_LPROMPT_P RETURN VARCHAR2 IS
704 BEGIN
705 RETURN RP_BAL_LPROMPT;
706 END RP_BAL_LPROMPT_P;
707
708 FUNCTION RP_CTR_APROMPT_P RETURN VARCHAR2 IS
709 BEGIN
710 RETURN RP_CTR_APROMPT;
711 END RP_CTR_APROMPT_P;
712
713 FUNCTION CP_END_DATE_P RETURN DATE IS
714 BEGIN
715 RETURN CP_END_DATE;
716 END CP_END_DATE_P;
717
718 FUNCTION CP_END_DATE_OPEN_P RETURN VARCHAR2 IS
719 BEGIN
720 RETURN CP_END_DATE_OPEN;
721 END CP_END_DATE_OPEN_P;
722
723 FUNCTION CP_PERIOD1_PC_P RETURN NUMBER IS
724 BEGIN
725 RETURN CP_PERIOD1_PC;
726 END CP_PERIOD1_PC_P;
727
728 FUNCTION CP_PERIOD1_POD_P RETURN DATE IS
729 BEGIN
730 RETURN CP_PERIOD1_POD;
731 END CP_PERIOD1_POD_P;
732
733 FUNCTION CP_PERIOD1_PCD_P RETURN DATE IS
734 BEGIN
735 RETURN CP_PERIOD1_PCD;
736 END CP_PERIOD1_PCD_P;
737
738 FUNCTION CP_PERIOD2_PC_P RETURN NUMBER IS
739 BEGIN
740 RETURN CP_PERIOD2_PC;
741 END CP_PERIOD2_PC_P;
742
743 FUNCTION CP_PERIOD2_PCD_P RETURN DATE IS
744 BEGIN
745 RETURN CP_PERIOD2_PCD;
746 END CP_PERIOD2_PCD_P;
747
748 FUNCTION CP_DISTRIBUTION_SOURCE_BOOK_P RETURN VARCHAR2 IS
749 BEGIN
750 RETURN CP_DISTRIBUTION_SOURCE_BOOK;
751 END CP_DISTRIBUTION_SOURCE_BOOK_P;
752
753 FUNCTION CP_BALANCE_TYPE_P RETURN VARCHAR2 IS
754 BEGIN
755 RETURN CP_BALANCE_TYPE;
756 END CP_BALANCE_TYPE_P;
757
758 FUNCTION CP_FDRCSID_P RETURN VARCHAR2 IS
759 BEGIN
760 RETURN CP_FDRCSID;
761 END CP_FDRCSID_P;
762
763 FUNCTION CP_BEGIN_OR_END_P RETURN VARCHAR2 IS
764 BEGIN
765 RETURN CP_BEGIN_OR_END;
766 END CP_BEGIN_OR_END_P;
767
768 FUNCTION CP_PERIOD_PC_P RETURN NUMBER IS
769 BEGIN
770 RETURN CP_PERIOD_PC;
771 END CP_PERIOD_PC_P;
772
773 FUNCTION CP_PERIOD_POD_P RETURN DATE IS
774 BEGIN
775 RETURN CP_PERIOD_POD;
776 END CP_PERIOD_POD_P;
777
778 FUNCTION CP_PERIOD_PCD_P RETURN DATE IS
779 BEGIN
780 RETURN CP_PERIOD_PCD;
781 END CP_PERIOD_PCD_P;
782
783 END JE_JEHUFAMD_XMLP_PKG;
784
785