[Home] [Help]
PACKAGE BODY: APPS.JE_JEHUFAMS_XMLP_PKG
Source
1 PACKAGE BODY JE_JEHUFAMS_XMLP_PKG AS
2 /* $Header: JEHUFAMSB.pls 120.1 2007/12/25 16:53:45 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 = 'CIP COST') THEN
29 RP_REPORT_NAME := ':CIP Summary Report:';
30 ELSE
31 RP_REPORT_NAME := ':Cost Summary Report:';
32 END IF;
33 RETURN (RP_REPORT_NAME);
34 END;
35 RETURN NULL;
36 END REPORT_NAMEFORMULA;
37
38 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
39 BEGIN
40 SELECT
41 PERIOD_CLOSE_DATE
42 INTO CP_END_DATE
43 FROM
44 FA_DEPRN_PERIODS
45 WHERE BOOK_TYPE_CODE = P_BOOK
46 AND PERIOD_NAME = P_PERIOD2;
47 IF CP_END_DATE IS NULL THEN
48 CP_END_PERIOD_OPEN := 'YES';
49 ELSE
50 CP_END_PERIOD_OPEN := 'NO';
51 END IF;
52 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
53 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
54 RETURN (TRUE);
55 END BEFOREREPORT;
56
57 FUNCTION AFTERREPORT RETURN BOOLEAN IS
58 BEGIN
59 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
60 BEGIN
61 ROLLBACK;
62 EXCEPTION
63 WHEN OTHERS THEN
64 NULL;
65 END;
66 RETURN (TRUE);
67 END AFTERREPORT;
68
69 FUNCTION PERIOD1_PCFORMULA RETURN NUMBER IS
70 BEGIN
71 DECLARE
72 L_PERIOD_POD DATE;
73 L_PERIOD_PCD DATE;
74 L_PERIOD_PC NUMBER(15);
75 L_PERIOD_FY NUMBER(15);
76 BEGIN
77 SELECT
78 PERIOD_COUNTER,
79 PERIOD_OPEN_DATE,
80 NVL(PERIOD_CLOSE_DATE
81 ,SYSDATE),
82 FISCAL_YEAR
83 INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
84 FROM
85 FA_DEPRN_PERIODS
86 WHERE BOOK_TYPE_CODE = P_BOOK
87 AND PERIOD_NAME = P_PERIOD1;
88 PERIOD1_POD := L_PERIOD_POD;
89 PERIOD1_PCD := L_PERIOD_PCD;
90 PERIOD1_FY := L_PERIOD_FY;
91 RETURN (L_PERIOD_PC);
92 END;
93 RETURN NULL;
94 END PERIOD1_PCFORMULA;
95
96 FUNCTION PERIOD2_PCFORMULA RETURN NUMBER IS
97 BEGIN
98 DECLARE
99 L_PERIOD_POD DATE;
100 L_PERIOD_PCD DATE;
101 L_PERIOD_PC NUMBER(15);
102 L_PERIOD_FY NUMBER(15);
103 BEGIN
104 SELECT
105 PERIOD_COUNTER,
106 PERIOD_OPEN_DATE,
107 NVL(PERIOD_CLOSE_DATE
108 ,SYSDATE),
109 FISCAL_YEAR
110 INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
111 FROM
112 FA_DEPRN_PERIODS
113 WHERE BOOK_TYPE_CODE = P_BOOK
114 AND PERIOD_NAME = P_PERIOD2;
115 PERIOD2_POD := L_PERIOD_POD;
116 PERIOD2_PCD := L_PERIOD_PCD;
117 PERIOD2_FY := L_PERIOD_FY;
118 RETURN (L_PERIOD_PC);
119 END;
120 RETURN NULL;
121 END PERIOD2_PCFORMULA;
122
123 FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
124 BEGIN
125 BEGIN
126 IF (P_REPORT_TYPE = 'COST' OR P_REPORT_TYPE = 'CIP COST') THEN
127 INSERT_INFO;
128 RETURN (1);
129 ELSE
130 RETURN (0);
131 END IF;
132 END;
133 RETURN NULL;
134 END DO_INSERTFORMULA;
135
136 FUNCTION OUT_OF_BALANCEFORMULA(BEGIN1 IN NUMBER
137 ,ADDITION IN NUMBER
138 ,REVALUATION IN NUMBER
139 ,RECLASS IN NUMBER
140 ,RETIREMENT IN NUMBER
141 ,ADJUSTMENT IN NUMBER
142 ,TRANSFER IN NUMBER
143 ,CAPITALIZATION IN NUMBER
144 ,END1 IN NUMBER) RETURN VARCHAR2 IS
145 BEGIN
146 DECLARE
147 MOCK_TOTAL NUMBER;
148 BEGIN
149 MOCK_TOTAL := NVL(BEGIN1
150 ,0) + NVL(ADDITION
151 ,0) + NVL(REVALUATION
152 ,0) + NVL(RECLASS
153 ,0) - NVL(RETIREMENT
154 ,0) + NVL(ADJUSTMENT
155 ,0) + NVL(TRANSFER
156 ,0) - NVL(CAPITALIZATION
157 ,0);
158 IF (MOCK_TOTAL = NVL(END1
159 ,0)) THEN
160 RETURN (' ');
161 ELSE
162 RETURN ('*');
163 END IF;
164 END;
165 RETURN NULL;
166 END OUT_OF_BALANCEFORMULA;
167
168 FUNCTION ACCT_OUT_OF_BALANCEFORMULA(ACCT_BEGIN IN NUMBER
169 ,ACCT_ADD IN NUMBER
170 ,ACCT_REVAL IN NUMBER
171 ,ACCT_RECLASS IN NUMBER
172 ,ACCT_RETIRE IN NUMBER
173 ,ACCT_ADJUST IN NUMBER
174 ,ACCT_TRANS IN NUMBER
175 ,ACCT_CAPITAL IN NUMBER
176 ,ACCT_END IN NUMBER) RETURN VARCHAR2 IS
177 BEGIN
178 DECLARE
179 MOCK_TOTAL NUMBER;
180 BEGIN
181 MOCK_TOTAL := NVL(ACCT_BEGIN
182 ,0) + NVL(ACCT_ADD
183 ,0) + NVL(ACCT_REVAL
184 ,0) + NVL(ACCT_RECLASS
185 ,0) - NVL(ACCT_RETIRE
186 ,0) + NVL(ACCT_ADJUST
187 ,0) + NVL(ACCT_TRANS
188 ,0) - NVL(ACCT_CAPITAL
189 ,0);
190 IF (MOCK_TOTAL = NVL(ACCT_END
191 ,0)) THEN
192 RETURN (' ');
193 ELSE
194 RETURN ('*');
195 END IF;
196 END;
197 RETURN NULL;
198 END ACCT_OUT_OF_BALANCEFORMULA;
199
200 FUNCTION BAL_OUT_OF_BALANCEFORMULA(BAL_BEGIN IN NUMBER
201 ,BAL_ADD IN NUMBER
202 ,BAL_REVAL IN NUMBER
203 ,BAL_RECLASS IN NUMBER
204 ,BAL_RETIRE IN NUMBER
205 ,BAL_ADJUST IN NUMBER
206 ,BAL_TRANS IN NUMBER
207 ,BAL_CAPITAL IN NUMBER
208 ,BAL_END IN NUMBER) RETURN VARCHAR2 IS
209 BEGIN
210 DECLARE
211 MOCK_TOTAL NUMBER;
212 BEGIN
213 MOCK_TOTAL := NVL(BAL_BEGIN
214 ,0) + NVL(BAL_ADD
215 ,0) + NVL(BAL_REVAL
216 ,0) + NVL(BAL_RECLASS
217 ,0) - NVL(BAL_RETIRE
218 ,0) + NVL(BAL_ADJUST
219 ,0) + NVL(BAL_TRANS
220 ,0) - NVL(BAL_CAPITAL
221 ,0);
222 IF (MOCK_TOTAL = NVL(BAL_END
223 ,0)) THEN
224 RETURN (' ');
225 ELSE
226 RETURN ('*');
227 END IF;
228 END;
229 RETURN NULL;
230 END BAL_OUT_OF_BALANCEFORMULA;
231
232 FUNCTION CAPREVALFORMULA(REVALUATION IN NUMBER
233 ,CAPITALIZATION IN NUMBER) RETURN NUMBER IS
234 BEGIN
235 IF (P_REPORT_TYPE = 'COST') THEN
236 RETURN (REVALUATION);
237 ELSE
238 RETURN (CAPITALIZATION);
239 END IF;
240 RETURN NULL;
241 END CAPREVALFORMULA;
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 ,'CR'
301 ,AH.TRANSACTION_HEADER_ID_OUT
302 ,'DR'
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,
341 FA_CATEGORY_BOOKS CB,
342 FA_ADDITIONS AD,
343 FA_DEPRN_DETAIL DD
344 WHERE NOT EXISTS (
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 GROUP BY
438 AH.CATEGORY_ID,
439 AH.ASSET_ID,
440 DH.CODE_COMBINATION_ID,
441 DECODE(RT.LOOKUP_CODE
442 ,'RESERVE'
443 ,CB.DEPRN_RESERVE_ACCT
444 ,'REVAL RESERVE'
445 ,CB.REVAL_RESERVE_ACCT),
446 DD.DEPRN_SOURCE_CODE;
447 END GET_DEPRN_EFFECTS;
448
449 PROCEDURE INSERT_INFO IS
450 PRAGMA AUTONOMOUS_TRANSACTION;
451 BEGIN
452 SELECT
453 P1.PERIOD_COUNTER,
454 P1.PERIOD_OPEN_DATE,
455 NVL(P1.PERIOD_CLOSE_DATE
456 ,BC.LAST_DEPRN_RUN_DATE),
457 P2.PERIOD_COUNTER,
458 NVL(P2.PERIOD_CLOSE_DATE
459 ,BC.LAST_DEPRN_RUN_DATE),
460 BC.DISTRIBUTION_SOURCE_BOOK
461 INTO CP_PERIOD1_PC,CP_PERIOD1_POD,CP_PERIOD1_PCD,CP_PERIOD2_PC,CP_PERIOD2_PCD,CP_DISTRIBUTION_SOURCE_BOOK
462 FROM
463 FA_DEPRN_PERIODS P1,
464 FA_DEPRN_PERIODS P2,
465 FA_BOOK_CONTROLS BC
466 WHERE BC.BOOK_TYPE_CODE = P_BOOK
467 AND P1.BOOK_TYPE_CODE = P_BOOK
468 AND P1.PERIOD_NAME = P_PERIOD1
469 AND P2.BOOK_TYPE_CODE = P_BOOK
470 AND P2.PERIOD_NAME = P_PERIOD2;
471 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
472 CP_BALANCE_TYPE := 'CR';
473 ELSE
474 CP_BALANCE_TYPE := 'DR';
475 END IF;
476 DELETE FROM JE_HU_BALANCES_REPORT;
477 DELETE FROM FA_LOOKUPS_B
478 WHERE LOOKUP_TYPE = 'REPORT TYPE';
479 DELETE FROM FA_LOOKUPS_TL
480 WHERE LOOKUP_TYPE = 'REPORT TYPE';
481 INSERT INTO FA_LOOKUPS_B
482 (LOOKUP_TYPE
483 ,LOOKUP_CODE
484 ,LAST_UPDATED_BY
485 ,LAST_UPDATE_DATE
486 ,ENABLED_FLAG)
487 VALUES ('REPORT TYPE'
488 ,P_REPORT_TYPE
489 ,1
490 ,SYSDATE
491 ,'Y');
492 INSERT INTO FA_LOOKUPS_TL
493 (LOOKUP_TYPE
494 ,LOOKUP_CODE
495 ,LANGUAGE
496 ,SOURCE_LANG
497 ,MEANING
498 ,LAST_UPDATED_BY
499 ,LAST_UPDATE_DATE)
500 SELECT
501 'REPORT TYPE',
502 P_REPORT_TYPE,
503 L.LANGUAGE_CODE,
504 USERENV('LANG'),
505 P_REPORT_TYPE,
506 1,
507 SYSDATE
508 FROM
509 FND_LANGUAGES L
510 WHERE L.INSTALLED_FLAG in ( 'I' , 'B' );
511 /*SRW.MESSAGE('001'
512 ,'Before First Balance')*/NULL;
513 CP_PERIOD_PC := CP_PERIOD1_PC - 1;
514 CP_PERIOD_PCD := CP_PERIOD1_PCD;
515 CP_PERIOD_POD := CP_PERIOD1_POD;
516 CP_BEGIN_OR_END := 'BEGIN';
517 GET_BALANCE;
518 /*SRW.MESSAGE('010'
519 ,'After first Balance')*/NULL;
520 /*SRW.MESSAGE('020'
521 ,'Before Second Balance')*/NULL;
522 CP_PERIOD_PC := CP_PERIOD2_PC;
523 CP_PERIOD_PCD := CP_PERIOD2_PCD;
524 CP_PERIOD_POD := CP_PERIOD2_PCD;
525 CP_BEGIN_OR_END := 'END';
526 /*SRW.MESSAGE('025'
527 ,'After Second Assignments')*/NULL;
528 GET_BALANCE;
529 /*SRW.MESSAGE('030'
530 ,'Before Get Adjustments')*/NULL;
531 GET_ADJUSTMENTS;
532 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
533 /*SRW.MESSAGE('040'
534 ,'Get Derpn Effects')*/NULL;
535 GET_DEPRN_EFFECTS;
536 /*SRW.MESSAGE('050'
537 ,'After Get Deprn Effects')*/NULL;
538 END IF;
539 COMMIT;
540 END INSERT_INFO;
541
542 PROCEDURE GET_BALANCE IS
543 BEGIN
544 INSERT INTO JE_HU_BALANCES_REPORT
545 (CATEGORY_ID
546 ,ASSET_ID
547 ,DISTRIBUTION_CCID
548 ,ADJUSTMENT_CCID
549 ,CATEGORY_BOOKS_ACCOUNT
550 ,SOURCE_TYPE_CODE
551 ,AMOUNT)
552 SELECT
553 AH.CATEGORY_ID,
554 AH.ASSET_ID,
555 DH.CODE_COMBINATION_ID,
556 null,
557 DECODE(P_REPORT_TYPE
558 ,'COST'
559 ,CB.ASSET_COST_ACCT
560 ,'CIP COST'
561 ,CB.CIP_COST_ACCT
562 ,'RESERVE'
563 ,CB.DEPRN_RESERVE_ACCT
564 ,'REVAL RESERVE'
565 ,CB.REVAL_RESERVE_ACCT),
566 DECODE(P_REPORT_TYPE
567 ,'RESERVE'
568 ,DECODE(DD.DEPRN_SOURCE_CODE
569 ,'D'
570 ,CP_BEGIN_OR_END
571 ,'ADDITION')
572 ,'REVAL RESERVE'
573 ,DECODE(DD.DEPRN_SOURCE_CODE
574 ,'D'
575 ,CP_BEGIN_OR_END
576 ,'ADDITION')
577 ,CP_BEGIN_OR_END),
578 DECODE(P_REPORT_TYPE
579 ,'COST'
580 ,DD.COST
581 ,'CIP COST'
582 ,DD.COST
583 ,'RESERVE'
584 ,DD.DEPRN_RESERVE
585 ,'REVAL RESERVE'
586 ,DD.REVAL_RESERVE)
587 FROM
588 FA_BOOKS BK,
589 FA_CATEGORY_BOOKS CB,
590 FA_ASSET_HISTORY AH,
591 FA_DEPRN_DETAIL DD,
592 FA_DISTRIBUTION_HISTORY DH
593 WHERE DH.BOOK_TYPE_CODE || '' = CP_DISTRIBUTION_SOURCE_BOOK
594 AND DECODE(DD.DEPRN_SOURCE_CODE
595 ,'D'
596 ,CP_PERIOD_POD
597 ,CP_PERIOD_PCD) BETWEEN DH.DATE_EFFECTIVE
598 AND NVL(DH.DATE_INEFFECTIVE
599 ,SYSDATE)
600 AND DD.ASSET_ID = DH.ASSET_ID + 0
601 AND DD.BOOK_TYPE_CODE = P_BOOK
602 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0
603 AND DD.PERIOD_COUNTER <= CP_PERIOD_PC
604 AND DECODE(CP_BEGIN_OR_END
605 ,'BEGIN'
606 ,DD.DEPRN_SOURCE_CODE
607 ,'D') = DD.DEPRN_SOURCE_CODE
608 AND DD.PERIOD_COUNTER = (
609 SELECT
610 MAX(SUB_DD.PERIOD_COUNTER)
611 FROM
612 FA_DEPRN_DETAIL SUB_DD
613 WHERE SUB_DD.BOOK_TYPE_CODE = P_BOOK
614 AND SUB_DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0
615 AND SUB_DD.PERIOD_COUNTER <= CP_PERIOD_PC )
616 AND AH.ASSET_ID = DH.ASSET_ID + 0
617 AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
618 AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
619 OR ( AH.ASSET_TYPE = 'CAPITALIZED'
620 AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
621 AND DECODE(DD.DEPRN_SOURCE_CODE
622 ,'D'
623 ,CP_PERIOD_POD
624 ,CP_PERIOD_PCD) BETWEEN AH.DATE_EFFECTIVE
625 AND NVL(AH.DATE_INEFFECTIVE
626 ,SYSDATE)
627 AND CB.CATEGORY_ID = AH.CATEGORY_ID
628 AND CB.BOOK_TYPE_CODE = P_BOOK
629 AND BK.BOOK_TYPE_CODE = P_BOOK
630 AND BK.ASSET_ID = DD.ASSET_ID
631 AND DECODE(DD.DEPRN_SOURCE_CODE
632 ,'D'
633 ,CP_PERIOD_POD
634 ,CP_PERIOD_PCD) BETWEEN BK.DATE_EFFECTIVE
635 AND NVL(BK.DATE_INEFFECTIVE
636 ,SYSDATE)
637 AND NVL(BK.PERIOD_COUNTER_FULLY_RETIRED
638 ,CP_PERIOD_PC + 1) > CP_PERIOD1_PC - 1
639 AND DECODE(P_REPORT_TYPE
640 ,'COST'
641 ,DECODE(AH.ASSET_TYPE
642 ,'CAPITALIZED'
643 ,CB.ASSET_COST_ACCT
644 ,NULL)
645 ,'CIP COST'
646 ,DECODE(AH.ASSET_TYPE
647 ,'CIP'
648 ,CB.CIP_COST_ACCT
649 ,NULL)
650 ,'RESERVE'
651 ,CB.DEPRN_RESERVE_ACCT
652 ,'REVAL RESERVE'
653 ,CB.REVAL_RESERVE_ACCT) is not null;
654 END GET_BALANCE;
655
656 PROCEDURE VERSION IS
657 BEGIN
658 CP_FDRCSID := '$Header: JEHUFAMSB.pls 120.1 2007/12/25 16:53:45 dwkrishn noship $';
659 END VERSION;
660
661 FUNCTION ACCT_BAL_APROMPT_P RETURN VARCHAR2 IS
662 BEGIN
663 RETURN ACCT_BAL_APROMPT;
664 END ACCT_BAL_APROMPT_P;
665
666 FUNCTION ACCT_CC_APROMPT_P RETURN VARCHAR2 IS
667 BEGIN
668 RETURN ACCT_CC_APROMPT;
669 END ACCT_CC_APROMPT_P;
670
671 FUNCTION CAT_MAJ_RPROMPT_P RETURN VARCHAR2 IS
672 BEGIN
673 RETURN CAT_MAJ_RPROMPT;
674 END CAT_MAJ_RPROMPT_P;
675
676 FUNCTION PERIOD1_POD_P RETURN DATE IS
677 BEGIN
678 RETURN PERIOD1_POD;
679 END PERIOD1_POD_P;
680
681 FUNCTION PERIOD1_PCD_P RETURN DATE IS
682 BEGIN
683 RETURN PERIOD1_PCD;
684 END PERIOD1_PCD_P;
685
686 FUNCTION PERIOD1_FY_P RETURN NUMBER IS
687 BEGIN
688 RETURN PERIOD1_FY;
689 END PERIOD1_FY_P;
690
691 FUNCTION PERIOD2_POD_P RETURN DATE IS
692 BEGIN
693 RETURN PERIOD2_POD;
694 END PERIOD2_POD_P;
695
696 FUNCTION PERIOD2_PCD_P RETURN DATE IS
697 BEGIN
698 RETURN PERIOD2_PCD;
699 END PERIOD2_PCD_P;
700
701 FUNCTION PERIOD2_FY_P RETURN NUMBER IS
702 BEGIN
703 RETURN PERIOD2_FY;
704 END PERIOD2_FY_P;
705
706 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
707 BEGIN
708 RETURN RP_COMPANY_NAME;
709 END RP_COMPANY_NAME_P;
710
711 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
712 BEGIN
713 RETURN RP_REPORT_NAME;
714 END RP_REPORT_NAME_P;
715
716 FUNCTION RP_BAL_LPROMPT_P RETURN VARCHAR2 IS
717 BEGIN
718 RETURN RP_BAL_LPROMPT;
719 END RP_BAL_LPROMPT_P;
720
721 FUNCTION RP_CTR_APROMPT_P RETURN VARCHAR2 IS
722 BEGIN
723 RETURN RP_CTR_APROMPT;
724 END RP_CTR_APROMPT_P;
725
726 FUNCTION CP_DISTRIBUTION_SOURCE_BOOK_P RETURN VARCHAR2 IS
727 BEGIN
728 RETURN CP_DISTRIBUTION_SOURCE_BOOK;
729 END CP_DISTRIBUTION_SOURCE_BOOK_P;
730
731 FUNCTION CP_PERIOD1_PC_P RETURN NUMBER IS
732 BEGIN
733 RETURN CP_PERIOD1_PC;
734 END CP_PERIOD1_PC_P;
735
736 FUNCTION CP_PERIOD1_POD_P RETURN DATE IS
737 BEGIN
738 RETURN CP_PERIOD1_POD;
739 END CP_PERIOD1_POD_P;
740
741 FUNCTION CP_PERIOD1_PCD_P RETURN DATE IS
742 BEGIN
743 RETURN CP_PERIOD1_PCD;
744 END CP_PERIOD1_PCD_P;
745
746 FUNCTION CP_PERIOD2_PC_P RETURN NUMBER IS
747 BEGIN
748 RETURN CP_PERIOD2_PC;
749 END CP_PERIOD2_PC_P;
750
751 FUNCTION CP_PERIOD2_PCD_P RETURN DATE IS
752 BEGIN
753 RETURN CP_PERIOD2_PCD;
754 END CP_PERIOD2_PCD_P;
755
756 FUNCTION CP_BALANCE_TYPE_P RETURN VARCHAR2 IS
757 BEGIN
758 RETURN CP_BALANCE_TYPE;
759 END CP_BALANCE_TYPE_P;
760
761 FUNCTION CP_BEGIN_OR_END_P RETURN VARCHAR2 IS
762 BEGIN
763 RETURN CP_BEGIN_OR_END;
764 END CP_BEGIN_OR_END_P;
765
766 FUNCTION CP_PERIOD_PC_P RETURN NUMBER IS
767 BEGIN
768 RETURN CP_PERIOD_PC;
769 END CP_PERIOD_PC_P;
770
771 FUNCTION CP_PERIOD_POD_P RETURN DATE IS
772 BEGIN
773 RETURN CP_PERIOD_POD;
774 END CP_PERIOD_POD_P;
775
776 FUNCTION CP_PERIOD_PCD_P RETURN DATE IS
777 BEGIN
778 RETURN CP_PERIOD_PCD;
779 END CP_PERIOD_PCD_P;
780
781 FUNCTION CP_FDRCSID_P RETURN VARCHAR2 IS
782 BEGIN
783 RETURN CP_FDRCSID;
784 END CP_FDRCSID_P;
785
786 FUNCTION CP_END_PERIOD_OPEN_P RETURN VARCHAR2 IS
787 BEGIN
788 RETURN CP_END_PERIOD_OPEN;
789 END CP_END_PERIOD_OPEN_P;
790
791 FUNCTION CP_END_DATE_P RETURN DATE IS
792 BEGIN
793 RETURN CP_END_DATE;
794 END CP_END_DATE_P;
795
796 END JE_JEHUFAMS_XMLP_PKG;
797
798
799