[Home] [Help]
PACKAGE BODY: APPS.JE_JEHUFDAN_XMLP_PKG
Source
1 PACKAGE BODY JE_JEHUFDAN_XMLP_PKG AS
2 /* $Header: JEHUFDANB.pls 120.1 2007/12/25 16:54:17 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 CP_PERIOD_FROM := P_PERIOD1;
47 CP_PERIOD_TO := P_PERIOD2;
48 SELECT
49 DECODE(PERIOD_CLOSE_DATE
50 ,NULL
51 ,'OPEN'
52 ,'CLOSED')
53 INTO CP_OPEN_CLOSED
54 FROM
55 FA_DEPRN_PERIODS
56 WHERE BOOK_TYPE_CODE = P_BOOK
57 AND PERIOD_NAME = P_PERIOD2;
58 RETURN (TRUE);
59 END BEFOREREPORT;
60
61 FUNCTION AFTERREPORT RETURN BOOLEAN IS
62 BEGIN
63 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
64 BEGIN
65 NULL;
66 EXCEPTION
67 WHEN OTHERS THEN
68 NULL;
69 END;
70 RETURN (TRUE);
71 END AFTERREPORT;
72
73 FUNCTION PERIOD1_PCFORMULA RETURN NUMBER IS
74 BEGIN
75 DECLARE
76 L_PERIOD_POD DATE;
77 L_PERIOD_PCD DATE;
78 L_PERIOD_PC NUMBER(15);
79 L_PERIOD_FY NUMBER(15);
80 BEGIN
81 SELECT
82 PERIOD_COUNTER,
83 PERIOD_OPEN_DATE,
84 NVL(PERIOD_CLOSE_DATE
85 ,SYSDATE),
86 FISCAL_YEAR
87 INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
88 FROM
89 FA_DEPRN_PERIODS
90 WHERE BOOK_TYPE_CODE = P_BOOK
91 AND PERIOD_NAME = P_PERIOD1;
92 PERIOD1_POD := L_PERIOD_POD;
93 PERIOD1_PCD := L_PERIOD_PCD;
94 PERIOD1_FY := L_PERIOD_FY;
95 RETURN (L_PERIOD_PC);
96 END;
97 RETURN NULL;
98 END PERIOD1_PCFORMULA;
99
100 FUNCTION PERIOD2_PCFORMULA RETURN NUMBER IS
101 BEGIN
102 DECLARE
103 L_PERIOD_POD DATE;
104 L_PERIOD_PCD DATE;
105 L_PERIOD_PC NUMBER(15);
106 L_PERIOD_FY NUMBER(15);
107 BEGIN
108 SELECT
109 PERIOD_COUNTER,
110 PERIOD_OPEN_DATE,
111 NVL(PERIOD_CLOSE_DATE
112 ,SYSDATE),
113 FISCAL_YEAR
114 INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
115 FROM
116 FA_DEPRN_PERIODS
117 WHERE BOOK_TYPE_CODE = P_BOOK
118 AND PERIOD_NAME = P_PERIOD2;
119 PERIOD2_POD := L_PERIOD_POD;
120 PERIOD2_PCD := L_PERIOD_PCD;
121 PERIOD2_FY := L_PERIOD_FY;
122 RETURN (L_PERIOD_PC);
123 END;
124 RETURN NULL;
125 END PERIOD2_PCFORMULA;
126
127 FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
128 BEGIN
129 BEGIN
130 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
131 INSERT_INFO;
132 RETURN (1);
133 ELSE
134 RETURN (0);
135 END IF;
136 END;
137 RETURN NULL;
138 END DO_INSERTFORMULA;
139
140 FUNCTION ADJUSTFORMULA(TAX IN NUMBER
141 ,FLAT IN NUMBER
142 ,NON_STR_UNPLANNED IN NUMBER) RETURN NUMBER IS
143 BEGIN
144 BEGIN
145 IF (NVL(TAX
146 ,0) + NVL(FLAT
147 ,0) - NVL(NON_STR_UNPLANNED
148 ,0)) = 0 THEN
149 RETURN (0);
150 ELSE
151 RETURN (NVL(TAX
152 ,0) + NVL(FLAT
153 ,0) - NVL(NON_STR_UNPLANNED
154 ,0));
155 END IF;
156 END;
157 RETURN NULL;
158 END ADJUSTFORMULA;
159
160 FUNCTION ENDFORMULA(LIFE IN NUMBER
161 ,EXPENSE IN NUMBER
162 ,ADJUST IN NUMBER
163 ,PRODUCTION IN NUMBER) RETURN NUMBER IS
164 BEGIN
165 RETURN (NVL(LIFE
166 ,0) + NVL(EXPENSE
167 ,0) + NVL(ADJUST
168 ,0) + NVL(PRODUCTION
169 ,0));
170 END ENDFORMULA;
171
172 FUNCTION LIFEFORMULA(STRAIGHT IN NUMBER
173 ,STR_UNPLANNED IN NUMBER
174 ,EXPENSE IN NUMBER) RETURN NUMBER IS
175 BEGIN
176 RETURN (NVL(STRAIGHT
177 ,0) - NVL(STR_UNPLANNED
178 ,0) - NVL(EXPENSE
179 ,0));
180 END LIFEFORMULA;
181
182 PROCEDURE INSERT_INFO IS
183 PRAGMA AUTONOMOUS_TRANSACTION;
184 BEGIN
185 SELECT
186 P1.PERIOD_COUNTER,
187 P1.PERIOD_OPEN_DATE,
188 NVL(P1.PERIOD_CLOSE_DATE
189 ,SYSDATE),
190 P2.PERIOD_COUNTER,
191 NVL(P2.PERIOD_CLOSE_DATE
192 ,SYSDATE),
193 BC.DISTRIBUTION_SOURCE_BOOK
194 INTO CP_PERIOD1_PC,CP_PERIOD1_POD,CP_PERIOD1_PCD,CP_PERIOD2_PC,CP_PERIOD2_PCD,CP_DISTRIBUTION_SOURCE_BOOK
195 FROM
196 FA_DEPRN_PERIODS P1,
197 FA_DEPRN_PERIODS P2,
198 FA_BOOK_CONTROLS BC
199 WHERE BC.BOOK_TYPE_CODE = P_BOOK
200 AND P1.BOOK_TYPE_CODE = P_BOOK
201 AND P1.PERIOD_NAME = P_PERIOD1
202 AND P2.BOOK_TYPE_CODE = P_BOOK
203 AND P2.PERIOD_NAME = P_PERIOD2;
204 CP_BALANCE_TYPE := 'CR';
205 DELETE FROM JE_HU_BALANCES_REPORT;
206 DELETE FROM FA_LOOKUPS_B
207 WHERE LOOKUP_TYPE = 'REPORT TYPE';
208 DELETE FROM FA_LOOKUPS_TL
209 WHERE LOOKUP_TYPE = 'REPORT TYPE';
210 INSERT INTO FA_LOOKUPS_B
211 (LOOKUP_TYPE
212 ,LOOKUP_CODE
213 ,LAST_UPDATED_BY
214 ,LAST_UPDATE_DATE
215 ,ENABLED_FLAG)
216 VALUES ('REPORT TYPE'
217 ,P_REPORT_TYPE
218 ,1
219 ,SYSDATE
220 ,'Y');
221 INSERT INTO FA_LOOKUPS_TL
222 (LOOKUP_TYPE
223 ,LOOKUP_CODE
224 ,LANGUAGE
225 ,SOURCE_LANG
226 ,MEANING
227 ,LAST_UPDATED_BY
228 ,LAST_UPDATE_DATE)
229 SELECT
230 'REPORT TYPE',
231 P_REPORT_TYPE,
232 L.LANGUAGE_CODE,
233 USERENV('LANG'),
234 P_REPORT_TYPE,
235 1,
236 SYSDATE
237 FROM
238 FND_LANGUAGES L
239 WHERE L.INSTALLED_FLAG IN ( 'I' , 'B' );
240 GET_ADJUSTMENTS;
241 GET_ADJUSTMENTS_METHOD;
242 GET_ADJ_STR_UNPLANNED;
243 GET_ADJ_NON_STR_UNPLANNED;
244 GET_FIRST_MON_NON_STR_UNPL;
245 GET_FIRST_MON_STR_UNPL;
246 GET_DEPRN_EFFECTS;
247 GET_DEPRN_EFFECTS_METHOD;
248 GET_DEPRN_EFFECTS_FIRST;
249 COMMIT;
250 END INSERT_INFO;
251
252 PROCEDURE GET_ADJUSTMENTS IS
253 BEGIN
254 INSERT INTO JE_HU_BALANCES_REPORT
255 (CATEGORY_ID
256 ,ASSET_ID
257 ,DISTRIBUTION_CCID
258 ,ADJUSTMENT_CCID
259 ,CATEGORY_BOOKS_ACCOUNT
260 ,SOURCE_TYPE_CODE
261 ,AMOUNT)
262 SELECT
263 AH.CATEGORY_ID,
264 AH.ASSET_ID,
265 DH.CODE_COMBINATION_ID,
266 AJ.CODE_COMBINATION_ID,
267 null,
268 AJ.SOURCE_TYPE_CODE,
269 SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
270 ,CP_BALANCE_TYPE
271 ,1
272 ,-1) * AJ.ADJUSTMENT_AMOUNT)
273 FROM
274 FA_LOOKUPS RT,
275 FA_DISTRIBUTION_HISTORY DH,
276 FA_TRANSACTION_HEADERS TH,
277 FA_ASSET_HISTORY AH,
278 FA_ADJUSTMENTS AJ
279 WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
280 AND RT.LOOKUP_CODE = P_REPORT_TYPE
281 AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
282 AND AJ.ASSET_ID = DH.ASSET_ID
283 AND AJ.BOOK_TYPE_CODE = P_BOOK
284 AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
285 AND AJ.ADJUSTMENT_TYPE in ( P_REPORT_TYPE , DECODE(P_REPORT_TYPE
286 ,'REVAL RESERVE'
287 ,'REVAL AMORT') )
288 AND AJ.PERIOD_COUNTER_CREATED BETWEEN CP_PERIOD1_PC
289 AND CP_PERIOD2_PC
290 AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
291 AND AH.ASSET_ID = DH.ASSET_ID
292 AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
293 AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
294 OR ( AH.ASSET_TYPE = 'CAPITALIZED'
295 AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
296 AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
297 AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
298 ,TH.TRANSACTION_HEADER_ID)
299 AND ( DECODE(RT.LOOKUP_CODE
300 ,AJ.ADJUSTMENT_TYPE
301 ,1
302 ,0) * AJ.ADJUSTMENT_AMOUNT ) <> 0
303 GROUP BY
304 AH.CATEGORY_ID,
305 AH.ASSET_ID,
306 DH.CODE_COMBINATION_ID,
307 AJ.CODE_COMBINATION_ID,
308 AJ.SOURCE_TYPE_CODE;
309 IF P_REPORT_TYPE = 'RESERVE' THEN
310 INSERT INTO JE_HU_BALANCES_REPORT
311 (CATEGORY_ID
312 ,ASSET_ID
313 ,DISTRIBUTION_CCID
314 ,ADJUSTMENT_CCID
315 ,CATEGORY_BOOKS_ACCOUNT
316 ,SOURCE_TYPE_CODE
317 ,AMOUNT)
318 SELECT
319 CB.CATEGORY_ID,
320 DH.ASSET_ID,
321 DH.CODE_COMBINATION_ID,
322 null,
323 CB.DEPRN_RESERVE_ACCT,
324 'ADDITION',
325 SUM(DD.DEPRN_RESERVE)
326 FROM
327 FA_DISTRIBUTION_HISTORY DH,
328 FA_CATEGORY_BOOKS CB,
329 FA_ADDITIONS AD,
330 FA_DEPRN_DETAIL DD
331 WHERE NOT EXISTS (
332 SELECT
333 BR.ASSET_ID
334 FROM
335 JE_HU_BALANCES_REPORT BR
336 WHERE BR.ASSET_ID = DH.ASSET_ID
337 AND BR.DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
338 AND BR.SOURCE_TYPE_CODE = 'ADDITION' )
339 AND DD.BOOK_TYPE_CODE = P_BOOK
340 AND ( DD.PERIOD_COUNTER + 1 ) BETWEEN CP_PERIOD1_PC
341 AND CP_PERIOD2_PC
342 AND DD.DEPRN_SOURCE_CODE = 'B'
343 AND DD.ASSET_ID = DH.ASSET_ID
344 AND DD.DEPRN_RESERVE <> 0
345 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
346 AND DD.ASSET_ID = AD.ASSET_ID
347 AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
348 AND AD.ASSET_CATEGORY_ID = CB.CATEGORY_ID
349 GROUP BY
350 CB.CATEGORY_ID,
351 DH.ASSET_ID,
352 DH.CODE_COMBINATION_ID,
353 CB.DEPRN_RESERVE_ACCT;
354 END IF;
355 END GET_ADJUSTMENTS;
356
357 PROCEDURE GET_ADJUSTMENTS_METHOD IS
358 BEGIN
359 /*SRW.MESSAGE('001'
360 ,'PC1: ' || TO_CHAR(CP_PERIOD1_PC))*/NULL;
361 /*SRW.MESSAGE('002'
362 ,'PC2: ' || TO_CHAR(CP_PERIOD2_PC))*/NULL;
363 INSERT INTO JE_HU_BALANCES_REPORT
364 (CATEGORY_ID
365 ,ASSET_ID
366 ,DISTRIBUTION_CCID
367 ,ADJUSTMENT_CCID
368 ,CATEGORY_BOOKS_ACCOUNT
369 ,SOURCE_TYPE_CODE
370 ,AMOUNT)
371 SELECT
372 AH.CATEGORY_ID,
373 AH.ASSET_ID,
374 DH.CODE_COMBINATION_ID,
375 AJ.CODE_COMBINATION_ID,
376 null,
377 ME.RATE_SOURCE_RULE,
378 SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
379 ,CP_BALANCE_TYPE
380 ,1
381 ,-1) * AJ.ADJUSTMENT_AMOUNT)
382 FROM
383 FA_LOOKUPS RT,
384 FA_DISTRIBUTION_HISTORY DH,
385 FA_TRANSACTION_HEADERS TH,
386 FA_ASSET_HISTORY AH,
387 FA_ADJUSTMENTS AJ,
388 FA_METHODS ME,
389 FA_BOOKS BO,
390 FA_DEPRN_PERIODS DP
391 WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
392 AND RT.LOOKUP_CODE = P_REPORT_TYPE
393 AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
394 AND AJ.ASSET_ID = DH.ASSET_ID
395 AND AJ.BOOK_TYPE_CODE = P_BOOK
396 AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
397 AND AJ.ADJUSTMENT_TYPE in ( P_REPORT_TYPE , DECODE(P_REPORT_TYPE
398 ,'REVAL RESERVE'
399 ,'REVAL AMORT') )
400 AND AJ.PERIOD_COUNTER_CREATED BETWEEN CP_PERIOD1_PC
401 AND CP_PERIOD2_PC
402 AND AJ.SOURCE_TYPE_CODE = 'DEPRECIATION'
403 AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
404 AND AH.ASSET_ID = DH.ASSET_ID
405 AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
409 AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
406 AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
407 OR ( AH.ASSET_TYPE = 'CAPITALIZED'
408 AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
410 AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
411 ,TH.TRANSACTION_HEADER_ID)
412 AND ( DECODE(RT.LOOKUP_CODE
413 ,AJ.ADJUSTMENT_TYPE
414 ,1
415 ,0) * AJ.ADJUSTMENT_AMOUNT ) <> 0
416 AND DP.PERIOD_COUNTER = CP_PERIOD2_PC
417 AND DP.BOOK_TYPE_CODE = P_BOOK
418 AND NVL(DP.PERIOD_CLOSE_DATE
419 ,SYSDATE) between BO.DATE_EFFECTIVE
420 AND NVL(BO.DATE_INEFFECTIVE
421 ,SYSDATE)
422 AND BO.BOOK_TYPE_CODE = P_BOOK
423 AND BO.ASSET_ID = AJ.ASSET_ID
424 AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
425 AND NVL(ME.LIFE_IN_MONTHS
426 ,0) = NVL(BO.LIFE_IN_MONTHS
427 ,0)
428 GROUP BY
429 AH.CATEGORY_ID,
430 AH.ASSET_ID,
431 DH.CODE_COMBINATION_ID,
432 AJ.CODE_COMBINATION_ID,
433 ME.RATE_SOURCE_RULE;
434 END GET_ADJUSTMENTS_METHOD;
435
436 PROCEDURE GET_ADJ_STR_UNPLANNED IS
437 BEGIN
438 INSERT INTO JE_HU_BALANCES_REPORT
439 (CATEGORY_ID
440 ,ASSET_ID
441 ,DISTRIBUTION_CCID
442 ,ADJUSTMENT_CCID
443 ,CATEGORY_BOOKS_ACCOUNT
444 ,SOURCE_TYPE_CODE
445 ,AMOUNT)
446 SELECT
447 AH.CATEGORY_ID,
448 AH.ASSET_ID,
449 DH.CODE_COMBINATION_ID,
450 null,
451 CB.DEPRN_RESERVE_ACCT,
452 'STR UNPLANNED',
453 SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
454 ,CP_BALANCE_TYPE
455 ,-1
456 ,1) * AJ.ADJUSTMENT_AMOUNT)
457 FROM
458 FA_LOOKUPS RT,
459 FA_DISTRIBUTION_HISTORY DH,
460 FA_CATEGORY_BOOKS CB,
461 FA_TRANSACTION_HEADERS TH,
462 FA_ASSET_HISTORY AH,
463 FA_ADJUSTMENTS AJ,
464 FA_LOOKUPS LO,
465 FA_METHODS ME,
466 FA_BOOKS BO
467 WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
468 AND RT.LOOKUP_CODE = P_REPORT_TYPE
469 AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
470 AND AJ.ASSET_ID = DH.ASSET_ID
471 AND AJ.BOOK_TYPE_CODE = P_BOOK
472 AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
473 AND AJ.ADJUSTMENT_TYPE = 'EXPENSE'
474 AND AJ.PERIOD_COUNTER_ADJUSTED BETWEEN CP_PERIOD1_PC
475 AND CP_PERIOD2_PC
476 AND AJ.SOURCE_TYPE_CODE = 'DEPRECIATION'
477 AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
478 AND AH.ASSET_ID = DH.ASSET_ID
479 AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
480 AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
481 ,TH.TRANSACTION_HEADER_ID)
482 AND TH.TRANSACTION_SUBTYPE = LO.LOOKUP_CODE
483 AND LO.LOOKUP_TYPE = 'UNPLANNED DEPRN'
484 AND CB.BOOK_TYPE_CODE = AJ.BOOK_TYPE_CODE
485 AND CB.CATEGORY_ID = AH.CATEGORY_ID
486 AND AJ.ASSET_ID = BO.ASSET_ID
487 AND AJ.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
488 AND TH.TRANSACTION_HEADER_ID = BO.TRANSACTION_HEADER_ID_IN
489 AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
490 AND ME.STL_METHOD_FLAG = 'YES'
491 AND NVL(ME.LIFE_IN_MONTHS
492 ,0) = NVL(BO.LIFE_IN_MONTHS
493 ,0)
494 GROUP BY
495 AH.CATEGORY_ID,
496 AH.ASSET_ID,
497 DH.CODE_COMBINATION_ID,
498 AJ.CODE_COMBINATION_ID,
499 CB.DEPRN_RESERVE_ACCT;
500 END GET_ADJ_STR_UNPLANNED;
501
502 PROCEDURE GET_DEPRN_EFFECTS IS
503 BEGIN
504 INSERT INTO JE_HU_BALANCES_REPORT
505 (CATEGORY_ID
506 ,ASSET_ID
507 ,DISTRIBUTION_CCID
508 ,ADJUSTMENT_CCID
509 ,CATEGORY_BOOKS_ACCOUNT
510 ,SOURCE_TYPE_CODE
511 ,AMOUNT)
512 SELECT
513 AH.CATEGORY_ID,
514 AH.ASSET_ID,
515 DH.CODE_COMBINATION_ID,
516 null,
517 DECODE(RT.LOOKUP_CODE
518 ,'RESERVE'
519 ,CB.DEPRN_RESERVE_ACCT
520 ,'REVAL RESERVE'
521 ,CB.REVAL_RESERVE_ACCT),
522 DECODE(DD.DEPRN_SOURCE_CODE
523 ,'D'
524 ,'DEPRECIATION'
525 ,'ADDITION'),
526 SUM(DECODE(RT.LOOKUP_CODE
527 ,'RESERVE'
528 ,DD.DEPRN_AMOUNT
529 ,'REVAL RESERVE'
530 ,-DD.REVAL_AMORTIZATION))
531 FROM
532 FA_LOOKUPS RT,
533 FA_CATEGORY_BOOKS CB,
534 FA_DISTRIBUTION_HISTORY DH,
535 FA_ASSET_HISTORY AH,
536 FA_DEPRN_DETAIL DD,
537 FA_DEPRN_PERIODS DP
538 WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
539 AND AH.ASSET_ID = DH.ASSET_ID
540 AND AH.ASSET_TYPE = 'CAPITALIZED'
541 AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
542 ,SYSDATE)
543 AND NVL(DH.DATE_INEFFECTIVE
544 ,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
545 ,SYSDATE)
546 AND CB.CATEGORY_ID = AH.CATEGORY_ID
547 AND CB.BOOK_TYPE_CODE = P_BOOK
548 AND ( ( DD.DEPRN_SOURCE_CODE = 'B'
549 AND ( DD.PERIOD_COUNTER + 1 ) < CP_PERIOD2_PC )
550 OR ( DD.DEPRN_SOURCE_CODE = 'D' ) )
551 AND DD.BOOK_TYPE_CODE || '' = P_BOOK
552 AND DD.ASSET_ID = DH.ASSET_ID
553 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
554 AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
555 AND CP_PERIOD2_PC
556 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
557 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
558 AND DECODE(RT.LOOKUP_CODE
559 ,'RESERVE'
560 ,CB.DEPRN_RESERVE_ACCT
561 ,'REVAL RESERVE'
562 ,CB.REVAL_RESERVE_ACCT) is not null
563 AND DECODE(RT.LOOKUP_CODE
564 ,'RESERVE'
565 ,DD.DEPRN_AMOUNT
566 ,'REVAL RESERVE'
567 ,NVL(DD.REVAL_AMORTIZATION
568 ,0)) <> 0
569 GROUP BY
570 AH.CATEGORY_ID,
571 AH.ASSET_ID,
572 DH.CODE_COMBINATION_ID,
573 DECODE(RT.LOOKUP_CODE
574 ,'RESERVE'
575 ,CB.DEPRN_RESERVE_ACCT
576 ,'REVAL RESERVE'
577 ,CB.REVAL_RESERVE_ACCT),
578 DD.DEPRN_SOURCE_CODE;
579 END GET_DEPRN_EFFECTS;
580
581 PROCEDURE GET_DEPRN_EFFECTS_FIRST IS
582 BEGIN
583 INSERT INTO JE_HU_BALANCES_REPORT
584 (CATEGORY_ID
585 ,ASSET_ID
586 ,DISTRIBUTION_CCID
587 ,ADJUSTMENT_CCID
588 ,CATEGORY_BOOKS_ACCOUNT
589 ,SOURCE_TYPE_CODE
590 ,AMOUNT)
591 SELECT
592 AH.CATEGORY_ID,
593 AH.ASSET_ID,
594 DH.CODE_COMBINATION_ID,
595 null,
596 DECODE(RT.LOOKUP_CODE
597 ,'RESERVE'
598 ,CB.DEPRN_RESERVE_ACCT
599 ,'REVAL RESERVE'
600 ,CB.REVAL_RESERVE_ACCT),
601 'FIRST',
602 SUM(DECODE(RT.LOOKUP_CODE
603 ,'RESERVE'
604 ,DD.DEPRN_AMOUNT
605 ,'REVAL RESERVE'
606 ,-DD.REVAL_AMORTIZATION))
607 FROM
608 FA_LOOKUPS RT,
612 FA_DEPRN_DETAIL DD,
609 FA_CATEGORY_BOOKS CB,
610 FA_DISTRIBUTION_HISTORY DH,
611 FA_ASSET_HISTORY AH,
613 FA_DEPRN_PERIODS DP,
614 FA_DEPRN_PERIODS DP1,
615 FA_BOOKS BO
616 WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
617 AND AH.ASSET_ID = DH.ASSET_ID
618 AND AH.ASSET_TYPE = 'CAPITALIZED'
619 AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
620 ,SYSDATE)
621 AND NVL(DH.DATE_INEFFECTIVE
622 ,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
623 ,SYSDATE)
624 AND CB.CATEGORY_ID = AH.CATEGORY_ID
625 AND CB.BOOK_TYPE_CODE = P_BOOK
626 AND DD.DEPRN_SOURCE_CODE = 'D'
627 AND DD.BOOK_TYPE_CODE || '' = P_BOOK
628 AND DD.ASSET_ID = DH.ASSET_ID
629 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
630 AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
631 AND CP_PERIOD2_PC
632 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
633 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
634 AND DECODE(RT.LOOKUP_CODE
635 ,'RESERVE'
636 ,CB.DEPRN_RESERVE_ACCT
637 ,'REVAL RESERVE'
638 ,CB.REVAL_RESERVE_ACCT) is not null
639 AND DECODE(RT.LOOKUP_CODE
640 ,'RESERVE'
641 ,DD.DEPRN_AMOUNT
642 ,'REVAL RESERVE'
643 ,NVL(DD.REVAL_AMORTIZATION
644 ,0)) <> 0
645 AND DP1.PERIOD_COUNTER = CP_PERIOD2_PC
646 AND DP1.BOOK_TYPE_CODE = P_BOOK
647 AND NVL(DP1.PERIOD_CLOSE_DATE
648 ,SYSDATE) between BO.DATE_EFFECTIVE
649 AND NVL(BO.DATE_INEFFECTIVE
650 ,SYSDATE)
651 AND BO.BOOK_TYPE_CODE = P_BOOK
652 AND BO.ASSET_ID = AH.ASSET_ID
653 AND BO.PERIOD_COUNTER_FULLY_RESERVED = (
654 SELECT
655 MIN(PERIOD_COUNTER)
656 FROM
657 FA_DEPRN_DETAIL DD2
658 WHERE DD2.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
659 AND DD2.ASSET_ID = DD.ASSET_ID
660 AND DD2.DEPRN_SOURCE_CODE = 'D' )
661 GROUP BY
662 AH.CATEGORY_ID,
663 AH.ASSET_ID,
664 DH.CODE_COMBINATION_ID,
665 DECODE(RT.LOOKUP_CODE
666 ,'RESERVE'
667 ,CB.DEPRN_RESERVE_ACCT
668 ,'REVAL RESERVE'
669 ,CB.REVAL_RESERVE_ACCT);
670 END GET_DEPRN_EFFECTS_FIRST;
671
672 PROCEDURE GET_DEPRN_EFFECTS_METHOD IS
673 BEGIN
674 INSERT INTO JE_HU_BALANCES_REPORT
675 (CATEGORY_ID
676 ,ASSET_ID
677 ,DISTRIBUTION_CCID
678 ,ADJUSTMENT_CCID
679 ,CATEGORY_BOOKS_ACCOUNT
680 ,SOURCE_TYPE_CODE
681 ,AMOUNT)
682 SELECT
683 AH.CATEGORY_ID,
684 AH.ASSET_ID,
685 DH.CODE_COMBINATION_ID,
686 null,
687 DECODE(RT.LOOKUP_CODE
688 ,'RESERVE'
689 ,CB.DEPRN_RESERVE_ACCT
690 ,'REVAL RESERVE'
691 ,CB.REVAL_RESERVE_ACCT),
692 ME.RATE_SOURCE_RULE,
693 SUM(DECODE(RT.LOOKUP_CODE
694 ,'RESERVE'
695 ,DD.DEPRN_AMOUNT
696 ,'REVAL RESERVE'
697 ,-DD.REVAL_AMORTIZATION))
698 FROM
699 FA_LOOKUPS RT,
700 FA_CATEGORY_BOOKS CB,
701 FA_DISTRIBUTION_HISTORY DH,
702 FA_ASSET_HISTORY AH,
703 FA_DEPRN_DETAIL DD,
704 FA_DEPRN_PERIODS DP,
705 FA_DEPRN_PERIODS DP1,
706 FA_METHODS ME,
707 FA_BOOKS BO
708 WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
709 AND AH.ASSET_ID = DH.ASSET_ID
710 AND AH.ASSET_TYPE = 'CAPITALIZED'
711 AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
712 ,SYSDATE)
713 AND NVL(DH.DATE_INEFFECTIVE
714 ,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
715 ,SYSDATE)
716 AND CB.CATEGORY_ID = AH.CATEGORY_ID
717 AND CB.BOOK_TYPE_CODE = P_BOOK
718 AND DD.DEPRN_SOURCE_CODE = 'D'
719 AND DD.BOOK_TYPE_CODE || '' = P_BOOK
720 AND DD.ASSET_ID = DH.ASSET_ID
721 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
722 AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
723 AND CP_PERIOD2_PC
724 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
725 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
726 AND DECODE(RT.LOOKUP_CODE
727 ,'RESERVE'
728 ,CB.DEPRN_RESERVE_ACCT
729 ,'REVAL RESERVE'
730 ,CB.REVAL_RESERVE_ACCT) is not null
731 AND DECODE(RT.LOOKUP_CODE
732 ,'RESERVE'
733 ,DD.DEPRN_AMOUNT
734 ,'REVAL RESERVE'
735 ,NVL(DD.REVAL_AMORTIZATION
736 ,0)) <> 0
737 AND DP1.PERIOD_COUNTER = CP_PERIOD2_PC
738 AND DP1.BOOK_TYPE_CODE = P_BOOK
739 AND NVL(DP1.PERIOD_CLOSE_DATE
740 ,SYSDATE) between BO.DATE_EFFECTIVE
741 AND NVL(BO.DATE_INEFFECTIVE
742 ,SYSDATE)
743 AND BO.BOOK_TYPE_CODE = P_BOOK
744 AND BO.ASSET_ID = AH.ASSET_ID
745 AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
746 AND NVL(ME.LIFE_IN_MONTHS
747 ,0) = NVL(BO.LIFE_IN_MONTHS
748 ,0)
749 GROUP BY
750 AH.CATEGORY_ID,
751 AH.ASSET_ID,
752 ME.RATE_SOURCE_RULE,
753 DH.CODE_COMBINATION_ID,
754 DECODE(RT.LOOKUP_CODE
755 ,'RESERVE'
759 END GET_DEPRN_EFFECTS_METHOD;
756 ,CB.DEPRN_RESERVE_ACCT
757 ,'REVAL RESERVE'
758 ,CB.REVAL_RESERVE_ACCT);
760
761 FUNCTION CF_CURRENT_DATEFORMULA RETURN CHAR IS
762 DATE1 VARCHAR2(21);
763 BEGIN
764 SELECT
765 FND_DATE.DATE_TO_CHARDT(SYSDATE)
766 INTO DATE1
767 FROM
768 DUAL;
769 RETURN (DATE1);
770 END CF_CURRENT_DATEFORMULA;
771
772 PROCEDURE GET_ADJ_NON_STR_UNPLANNED IS
773 BEGIN
774 INSERT INTO JE_HU_BALANCES_REPORT
775 (CATEGORY_ID
776 ,ASSET_ID
777 ,DISTRIBUTION_CCID
778 ,ADJUSTMENT_CCID
779 ,CATEGORY_BOOKS_ACCOUNT
780 ,SOURCE_TYPE_CODE
781 ,AMOUNT)
782 SELECT
783 AH.CATEGORY_ID,
784 AH.ASSET_ID,
785 DH.CODE_COMBINATION_ID,
786 null,
787 CB.DEPRN_RESERVE_ACCT,
788 'NON STR UNPLN',
789 SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
790 ,CP_BALANCE_TYPE
791 ,-1
792 ,1) * AJ.ADJUSTMENT_AMOUNT)
793 FROM
794 FA_LOOKUPS RT,
795 FA_DISTRIBUTION_HISTORY DH,
796 FA_CATEGORY_BOOKS CB,
797 FA_TRANSACTION_HEADERS TH,
798 FA_ASSET_HISTORY AH,
799 FA_ADJUSTMENTS AJ,
800 FA_LOOKUPS LO,
801 FA_METHODS ME,
802 FA_BOOKS BO
803 WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
804 AND RT.LOOKUP_CODE = P_REPORT_TYPE
805 AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
806 AND AJ.ASSET_ID = DH.ASSET_ID
807 AND AJ.BOOK_TYPE_CODE = P_BOOK
808 AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
809 AND AJ.ADJUSTMENT_TYPE = 'EXPENSE'
810 AND AJ.PERIOD_COUNTER_ADJUSTED BETWEEN CP_PERIOD1_PC
811 AND CP_PERIOD2_PC
812 AND AJ.SOURCE_TYPE_CODE = 'DEPRECIATION'
813 AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
814 AND AH.ASSET_ID = DH.ASSET_ID
815 AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
816 AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
817 ,TH.TRANSACTION_HEADER_ID)
818 AND TH.TRANSACTION_SUBTYPE = LO.LOOKUP_CODE
819 AND LO.LOOKUP_TYPE = 'UNPLANNED DEPRN'
820 AND CB.BOOK_TYPE_CODE = AJ.BOOK_TYPE_CODE
821 AND CB.CATEGORY_ID = AH.CATEGORY_ID
822 AND AJ.ASSET_ID = BO.ASSET_ID
823 AND AJ.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
824 AND TH.TRANSACTION_HEADER_ID = BO.TRANSACTION_HEADER_ID_IN
825 AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
826 AND ME.STL_METHOD_FLAG = 'NO'
827 AND NVL(ME.LIFE_IN_MONTHS
828 ,0) = NVL(BO.LIFE_IN_MONTHS
829 ,0)
830 GROUP BY
831 AH.CATEGORY_ID,
832 AH.ASSET_ID,
833 DH.CODE_COMBINATION_ID,
834 AJ.CODE_COMBINATION_ID,
835 CB.DEPRN_RESERVE_ACCT;
836 END GET_ADJ_NON_STR_UNPLANNED;
837
838 FUNCTION CF_UNPLANNEDFORMULA(FIRST_STR IN NUMBER
839 ,STR_UNPLANNED IN NUMBER
840 ,NON_STR_UNPLANNED IN NUMBER
841 ,FIRST_NON_STR IN NUMBER) RETURN NUMBER IS
842 BEGIN
843 /*SRW.MESSAGE('001'
844 ,'First Str: ' || FIRST_STR)*/NULL;
845 RETURN (STR_UNPLANNED + NON_STR_UNPLANNED + FIRST_STR + FIRST_NON_STR);
846 END CF_UNPLANNEDFORMULA;
847
848 PROCEDURE GET_FIRST_MON_STR_UNPL IS
849 BEGIN
850 INSERT INTO JE_HU_BALANCES_REPORT
851 (CATEGORY_ID
852 ,ASSET_ID
853 ,DISTRIBUTION_CCID
854 ,ADJUSTMENT_CCID
855 ,CATEGORY_BOOKS_ACCOUNT
856 ,SOURCE_TYPE_CODE
857 ,AMOUNT)
858 SELECT
859 AH.CATEGORY_ID,
860 AH.ASSET_ID,
861 DH.CODE_COMBINATION_ID,
862 null,
863 DECODE(RT.LOOKUP_CODE
864 ,'RESERVE'
865 ,CB.DEPRN_RESERVE_ACCT
866 ,'REVAL RESERVE'
867 ,CB.REVAL_RESERVE_ACCT),
868 'FIRST STR',
869 SUM(NVL(DD.DEPRN_ADJUSTMENT_AMOUNT
870 ,0))
871 FROM
872 FA_LOOKUPS RT,
873 FA_CATEGORY_BOOKS CB,
874 FA_DISTRIBUTION_HISTORY DH,
875 FA_ASSET_HISTORY AH,
876 FA_DEPRN_DETAIL DD,
877 FA_DEPRN_PERIODS DP,
878 FA_BOOKS BO,
879 FA_METHODS ME,
880 FA_TRANSACTION_HEADERS TH
881 WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
882 AND AH.ASSET_ID = DH.ASSET_ID
883 AND AH.ASSET_TYPE = 'CAPITALIZED'
884 AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
885 ,SYSDATE)
886 AND NVL(DH.DATE_INEFFECTIVE
887 ,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
888 ,SYSDATE)
889 AND CB.CATEGORY_ID = AH.CATEGORY_ID
890 AND CB.BOOK_TYPE_CODE = P_BOOK
891 AND DD.DEPRN_SOURCE_CODE = 'B'
892 AND DD.BOOK_TYPE_CODE || '' = P_BOOK
893 AND DD.ASSET_ID = DH.ASSET_ID
894 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
895 AND DD.PERIOD_COUNTER between CP_PERIOD1_PC - 1
896 AND CP_PERIOD2_PC
897 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
898 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
899 AND DECODE(RT.LOOKUP_CODE
900 ,'RESERVE'
901 ,CB.DEPRN_RESERVE_ACCT
902 ,'REVAL RESERVE'
903 ,CB.REVAL_RESERVE_ACCT) is not null
904 AND DD.DEPRN_ADJUSTMENT_AMOUNT is not null
908 AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
905 AND DD.ASSET_ID = BO.ASSET_ID
906 AND DD.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
907 AND BO.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
909 AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
910 AND ME.STL_METHOD_FLAG = 'YES'
911 AND NVL(ME.LIFE_IN_MONTHS
912 ,0) = NVL(BO.LIFE_IN_MONTHS
913 ,0)
914 GROUP BY
915 AH.CATEGORY_ID,
916 AH.ASSET_ID,
917 DH.CODE_COMBINATION_ID,
918 DECODE(RT.LOOKUP_CODE
919 ,'RESERVE'
920 ,CB.DEPRN_RESERVE_ACCT
921 ,'REVAL RESERVE'
922 ,CB.REVAL_RESERVE_ACCT),
923 DD.DEPRN_SOURCE_CODE;
924 END GET_FIRST_MON_STR_UNPL;
925
926 PROCEDURE GET_FIRST_MON_NON_STR_UNPL IS
927 BEGIN
928 INSERT INTO JE_HU_BALANCES_REPORT
929 (CATEGORY_ID
930 ,ASSET_ID
931 ,DISTRIBUTION_CCID
932 ,ADJUSTMENT_CCID
933 ,CATEGORY_BOOKS_ACCOUNT
934 ,SOURCE_TYPE_CODE
935 ,AMOUNT)
936 SELECT
937 AH.CATEGORY_ID,
938 AH.ASSET_ID,
939 DH.CODE_COMBINATION_ID,
940 null,
941 DECODE(RT.LOOKUP_CODE
942 ,'RESERVE'
943 ,CB.DEPRN_RESERVE_ACCT
944 ,'REVAL RESERVE'
945 ,CB.REVAL_RESERVE_ACCT),
946 'FIRST NON STR',
947 SUM(NVL(DD.DEPRN_ADJUSTMENT_AMOUNT
948 ,0))
949 FROM
950 FA_LOOKUPS RT,
951 FA_CATEGORY_BOOKS CB,
952 FA_DISTRIBUTION_HISTORY DH,
953 FA_ASSET_HISTORY AH,
954 FA_DEPRN_DETAIL DD,
955 FA_DEPRN_PERIODS DP,
956 FA_BOOKS BO,
957 FA_METHODS ME,
958 FA_TRANSACTION_HEADERS TH
959 WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
960 AND AH.ASSET_ID = DH.ASSET_ID
961 AND AH.ASSET_TYPE = 'CAPITALIZED'
962 AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
963 ,SYSDATE)
964 AND NVL(DH.DATE_INEFFECTIVE
965 ,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
966 ,SYSDATE)
967 AND CB.CATEGORY_ID = AH.CATEGORY_ID
968 AND CB.BOOK_TYPE_CODE = P_BOOK
969 AND DD.DEPRN_SOURCE_CODE = 'B'
970 AND DD.BOOK_TYPE_CODE || '' = P_BOOK
971 AND DD.ASSET_ID = DH.ASSET_ID
972 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
973 AND DD.PERIOD_COUNTER between CP_PERIOD1_PC - 1
974 AND CP_PERIOD2_PC
975 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
976 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
977 AND DECODE(RT.LOOKUP_CODE
978 ,'RESERVE'
979 ,CB.DEPRN_RESERVE_ACCT
980 ,'REVAL RESERVE'
981 ,CB.REVAL_RESERVE_ACCT) is not null
982 AND DD.DEPRN_ADJUSTMENT_AMOUNT is not null
983 AND DD.ASSET_ID = BO.ASSET_ID
984 AND DD.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
985 AND BO.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
986 AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
987 AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
988 AND ME.STL_METHOD_FLAG = 'NO'
989 AND NVL(ME.LIFE_IN_MONTHS
990 ,0) = NVL(BO.LIFE_IN_MONTHS
991 ,0)
992 GROUP BY
993 AH.CATEGORY_ID,
994 AH.ASSET_ID,
995 DH.CODE_COMBINATION_ID,
996 DECODE(RT.LOOKUP_CODE
997 ,'RESERVE'
998 ,CB.DEPRN_RESERVE_ACCT
999 ,'REVAL RESERVE'
1000 ,CB.REVAL_RESERVE_ACCT),
1001 DD.DEPRN_SOURCE_CODE;
1002 END GET_FIRST_MON_NON_STR_UNPL;
1003
1004 FUNCTION CF_TOTAL_DEPRECIATIONFORMULA(CF_UNPLANNED IN NUMBER
1005 ,END1 IN NUMBER) RETURN NUMBER IS
1006 BEGIN
1007 RETURN (CF_UNPLANNED + END1);
1008 END CF_TOTAL_DEPRECIATIONFORMULA;
1009
1010 FUNCTION ACCT_BAL_APROMPT_P RETURN VARCHAR2 IS
1011 BEGIN
1012 RETURN ACCT_BAL_APROMPT;
1013 END ACCT_BAL_APROMPT_P;
1014
1015 FUNCTION ACCT_CC_APROMPT_P RETURN VARCHAR2 IS
1016 BEGIN
1017 RETURN ACCT_CC_APROMPT;
1018 END ACCT_CC_APROMPT_P;
1019
1020 FUNCTION CAT_MAJ_RPROMPT_P RETURN VARCHAR2 IS
1021 BEGIN
1022 RETURN CAT_MAJ_RPROMPT;
1023 END CAT_MAJ_RPROMPT_P;
1024
1025 FUNCTION PERIOD1_POD_P RETURN DATE IS
1026 BEGIN
1027 RETURN PERIOD1_POD;
1028 END PERIOD1_POD_P;
1029
1030 FUNCTION PERIOD1_PCD_P RETURN DATE IS
1031 BEGIN
1032 RETURN PERIOD1_PCD;
1033 END PERIOD1_PCD_P;
1034
1035 FUNCTION PERIOD1_FY_P RETURN NUMBER IS
1036 BEGIN
1037 RETURN PERIOD1_FY;
1038 END PERIOD1_FY_P;
1039
1040 FUNCTION PERIOD2_POD_P RETURN DATE IS
1041 BEGIN
1042 RETURN PERIOD2_POD;
1043 END PERIOD2_POD_P;
1044
1045 FUNCTION PERIOD2_PCD_P RETURN DATE IS
1046 BEGIN
1047 RETURN PERIOD2_PCD;
1048 END PERIOD2_PCD_P;
1049
1050 FUNCTION PERIOD2_FY_P RETURN NUMBER IS
1051 BEGIN
1052 RETURN PERIOD2_FY;
1053 END PERIOD2_FY_P;
1054
1055 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
1056 BEGIN
1057 RETURN RP_COMPANY_NAME;
1058 END RP_COMPANY_NAME_P;
1059
1060 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
1061 BEGIN
1062 RETURN RP_REPORT_NAME;
1063 END RP_REPORT_NAME_P;
1064
1065 FUNCTION RP_BAL_LPROMPT_P RETURN VARCHAR2 IS
1066 BEGIN
1067 RETURN RP_BAL_LPROMPT;
1068 END RP_BAL_LPROMPT_P;
1069
1070 FUNCTION RP_CTR_APROMPT_P RETURN VARCHAR2 IS
1071 BEGIN
1072 RETURN RP_CTR_APROMPT;
1073 END RP_CTR_APROMPT_P;
1074
1075 FUNCTION CP_DISTRIBUTION_SOURCE_BOOK_P RETURN VARCHAR2 IS
1076 BEGIN
1077 RETURN CP_DISTRIBUTION_SOURCE_BOOK;
1078 END CP_DISTRIBUTION_SOURCE_BOOK_P;
1079
1080 FUNCTION CP_PERIOD1_PC_P RETURN NUMBER IS
1081 BEGIN
1082 RETURN CP_PERIOD1_PC;
1083 END CP_PERIOD1_PC_P;
1084
1085 FUNCTION CP_PERIOD1_POD_P RETURN DATE IS
1086 BEGIN
1087 RETURN CP_PERIOD1_POD;
1088 END CP_PERIOD1_POD_P;
1089
1090 FUNCTION CP_PERIOD1_PCD_P RETURN DATE IS
1091 BEGIN
1092 RETURN CP_PERIOD1_PCD;
1093 END CP_PERIOD1_PCD_P;
1094
1095 FUNCTION CP_PERIOD2_PC_P RETURN NUMBER IS
1096 BEGIN
1097 RETURN CP_PERIOD2_PC;
1098 END CP_PERIOD2_PC_P;
1099
1100 FUNCTION CP_PERIOD2_PCD_P RETURN DATE IS
1101 BEGIN
1102 RETURN CP_PERIOD2_PCD;
1103 END CP_PERIOD2_PCD_P;
1104
1105 FUNCTION CP_BALANCE_TYPE_P RETURN VARCHAR2 IS
1106 BEGIN
1107 RETURN CP_BALANCE_TYPE;
1108 END CP_BALANCE_TYPE_P;
1109
1110 FUNCTION CP_PERIOD_FROM_P RETURN VARCHAR2 IS
1111 BEGIN
1112 RETURN CP_PERIOD_FROM;
1113 END CP_PERIOD_FROM_P;
1114
1115 FUNCTION CP_PERIOD_TO_P RETURN VARCHAR2 IS
1116 BEGIN
1117 RETURN CP_PERIOD_TO;
1118 END CP_PERIOD_TO_P;
1119
1120 FUNCTION CP_OPEN_CLOSED_P RETURN VARCHAR2 IS
1121 BEGIN
1122 RETURN CP_OPEN_CLOSED;
1123 END CP_OPEN_CLOSED_P;
1124
1125 FUNCTION B_PERIOD_OPENFORMATTRIGGER RETURN VARCHAR2 IS
1126 -- DAVID HAIMES JULY 1999 11.5 UPGRADE BUG
1127
1128 END_DATE DATE;
1129
1130 BEGIN
1131
1132 SELECT PERIOD_CLOSE_DATE
1133 INTO END_DATE
1134 FROM FA_DEPRN_PERIODS
1135 WHERE BOOK_TYPE_CODE = P_BOOK
1136 AND PERIOD_NAME = P_PERIOD2;
1137
1138
1139 IF END_DATE IS NULL THEN
1140 RETURN('TRUE');
1141 ELSE
1142 RETURN('FALSE');
1143 END IF;
1144 RETURN(NULL);
1145 END;
1146
1147 END JE_JEHUFDAN_XMLP_PKG;
1148
1149