[Home] [Help]
PACKAGE BODY: APPS.JA_JAINYEDE_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINYEDE_XMLP_PKG AS
2 /* $Header: JAINYEDEB.pls 120.2 2008/01/22 11:06:33 dwkrishn noship $ */
3 FUNCTION CF_1FORMULA RETURN VARCHAR2 IS
4 N VARCHAR2(100);
5 BEGIN
6 SELECT
7 COMPANY_NAME
8 INTO N
9 FROM
10 FA_SYSTEM_CONTROLS;
11 /*SRW.MESSAGE(1275
12 ,'CF_1 company_name -> ' || N)*/NULL;
13 RETURN (N);
14 END CF_1FORMULA;
15
16 FUNCTION CF_2FORMULA(OP_TOT IN NUMBER
17 ,COS_TOT IN NUMBER
18 ,CS_4 IN NUMBER) RETURN NUMBER IS
19 N NUMBER;
20 BEGIN
21 N := NVL(OP_TOT
22 ,0) + NVL(COS_TOT
23 ,0) - NVL(CS_4
24 ,0);
25 /*SRW.MESSAGE(1275
26 ,'CF_2 op_tot -> ' || OP_TOT || ', cos_tot -> ' || COS_TOT || ', cs_4 -> ' || CS_4 || ', n -> ' || N)*/NULL;
27 IF N <= 0 THEN
28 N := 0;
29 END IF;
30 RETURN (N);
31 END CF_2FORMULA;
32
33 FUNCTION CF_3FORMULA(ASSET_ID_v IN NUMBER
34 ,BLOCK_ID1 IN NUMBER
35 ,COST IN NUMBER
36 ,SLNO IN NUMBER
37 ,BLOCK_ID IN NUMBER
38 ,RATE IN NUMBER
39 ,TYPE1 IN VARCHAR2
40 ,OPENING_WDV IN NUMBER
41 ,CLOSING_WDV IN NUMBER
42 ,DEPN_OF_ASSETS IN NUMBER
43 ,TOTAL IN NUMBER
44 ,UNPLANNED_DEPN IN NUMBER) RETURN NUMBER IS
45 V_SALE NUMBER;
46 V_BLOCK_ID NUMBER;
47 CURSOR SALE_CUR IS
48 SELECT
49 ( NVL(A.PROCEEDS_OF_SALE
50 ,0) - NVL(A.COST_OF_REMOVAL
51 ,0) ) SALE
52 FROM
53 FA_RETIREMENTS A,
54 JAI_FA_AST_BLOCK_DTLS B,
55 JAI_FA_AST_BLOCKS C
56 WHERE A.ASSET_ID = B.ASSET_ID
57 AND A.STATUS = 'PROCESSED'
58 AND A.ASSET_ID = ASSET_ID_v
59 AND B.BLOCK_ID = TO_CHAR(BLOCK_ID1)
60 AND C.BOOK_TYPE_CODE = BOOK_NAME
61 AND A.DATE_RETIRED BETWEEN C.START_DATE
62 AND C.YEAR_ENDED
63 AND C.START_DATE BETWEEN START_DATE
64 AND END_DATE_N;
65 BEGIN
66 OPEN SALE_CUR;
67 FETCH SALE_CUR
68 INTO V_SALE;
69 CLOSE SALE_CUR;
70 NULL;
71 RETURN (V_SALE);
72 END CF_3FORMULA;
73
74 FUNCTION AFTERPFORM RETURN BOOLEAN IS
75 V_END_DATE DATE;
76 CURSOR YEAR_END_CUR IS
77 SELECT
78 YEAR_END
79 FROM
80 JAI_FA_AST_YEARS
81 WHERE YEAR_START = START_DATE;
82 BEGIN
83 OPEN YEAR_END_CUR;
84 FETCH YEAR_END_CUR
85 INTO V_END_DATE;
86 CLOSE YEAR_END_CUR;
87 -- END_DATE := V_END_DATE;
88 END_DATE_N := V_END_DATE;
89 START_DATE_N := START_DATE;
90 START_DATE_N1:=TO_CHAR(START_DATE,'DD/MON/YYYY');
91 END_DATE_N1:=TO_CHAR(END_DATE_N,'DD/MON/YYYY');
92
93 RUN_DEP(START_DATE
94 ,V_END_DATE
95 ,BOOK_NAME);
96 RETURN (TRUE);
97 END AFTERPFORM;
98
99 FUNCTION CF_4FORMULA RETURN NUMBER IS
100 V_TYPE JAI_FA_AST_BLOCKS.TYPE%TYPE;
101 V_RATE NUMBER;
102 V_SALE NUMBER;
103 V_BLOCK_ID NUMBER;
104 V_BLOCK_ID1 NUMBER;
105 CURSOR BLOCK_RET_CUR IS
106 SELECT
107 A.BLOCK_ID
108 FROM
109 JAI_FA_AST_BLOCKS A,
110 JAI_FA_AST_BLOCK_DTLS B,
111 FA_RETIREMENTS C
112 WHERE A.BLOCK_ID = B.BLOCK_ID
113 AND B.ASSET_ID = C.ASSET_ID
114 GROUP BY
115 A.BLOCK_ID;
116 CURSOR TYPE_RATE_CUR(P_BLOCK_ID IN NUMBER) IS
117 SELECT
118 TYPE,
119 RATE
120 FROM
121 JAI_FA_AST_BLOCKS
122 WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
123 AND BOOK_TYPE_CODE = BOOK_NAME;
124 CURSOR BLOCK_ID_CUR_RET(P_TYPE IN VARCHAR2,P_RATE IN NUMBER) IS
125 SELECT
126 BLOCK_ID
127 FROM
128 JAI_FA_AST_BLOCKS
129 WHERE TYPE = P_TYPE
130 AND RATE = P_RATE
131 AND BOOK_TYPE_CODE = BOOK_NAME
132 AND START_DATE BETWEEN START_DATE
133 AND END_DATE_N;
134 CURSOR SALE_CUR(P_BLOCK_ID IN NUMBER,P_TYPE IN VARCHAR2,P_RATE IN NUMBER) IS
135 SELECT
136 DISTINCT
137 ( NVL(A.PROCEEDS_OF_SALE
138 ,0) - NVL(A.COST_OF_REMOVAL
139 ,0) ) SALE
140 FROM
141 FA_RETIREMENTS A,
142 JAI_FA_AST_BLOCK_DTLS B,
143 JAI_FA_AST_BLOCKS C
144 WHERE A.ASSET_ID = B.ASSET_ID
145 AND B.BLOCK_ID = P_BLOCK_ID
146 AND C.TYPE = P_TYPE
147 AND C.RATE = P_RATE
148 AND A.DATE_RETIRED between START_DATE
149 AND END_DATE_N;
150 BEGIN
151 OPEN BLOCK_RET_CUR;
152 FETCH BLOCK_RET_CUR
153 INTO V_BLOCK_ID;
154 CLOSE BLOCK_RET_CUR;
155 OPEN TYPE_RATE_CUR(V_BLOCK_ID);
156 FETCH TYPE_RATE_CUR
157 INTO V_TYPE,V_RATE;
158 CLOSE TYPE_RATE_CUR;
159 OPEN BLOCK_ID_CUR_RET(V_TYPE,V_RATE);
160 FETCH BLOCK_ID_CUR_RET
161 INTO V_BLOCK_ID1;
162 CLOSE BLOCK_ID_CUR_RET;
163 OPEN SALE_CUR(V_BLOCK_ID,V_TYPE,V_RATE);
164 FETCH SALE_CUR
165 INTO V_SALE;
166 CLOSE SALE_CUR;
167 NULL;
168 RETURN (V_SALE);
169 END CF_4FORMULA;
170
171 FUNCTION CF_5FORMULA(BLOCK_ID IN NUMBER
172 ,TYPE1 IN VARCHAR2
173 ,RATE IN NUMBER) RETURN NUMBER IS
174 V_TYPE JAI_FA_AST_BLOCKS.TYPE%TYPE;
175 V_RATE NUMBER;
176 V_SALE NUMBER;
177 V_BLOCK_ID NUMBER;
178 V_BLOCK_ID1 NUMBER;
179 CURSOR TYPE_RATE_CUR IS
180 SELECT
181 TYPE,
182 RATE
183 FROM
184 JAI_FA_AST_BLOCKS
185 WHERE BLOCK_ID = BLOCK_ID
186 AND BOOK_TYPE_CODE = BOOK_NAME;
187 CURSOR BLOCK_ID_CUR_RET IS
188 SELECT
189 BLOCK_ID
190 FROM
191 JAI_FA_AST_BLOCKS
192 WHERE TYPE = TYPE1
193 AND RATE = RATE
194 AND START_DATE < START_DATE;
195 CURSOR SALE_CUR(CP_OWNED_LEASED IN FA_ADDITIONS.OWNED_LEASED%TYPE,CP_STATUS IN FA_RETIREMENTS.STATUS%TYPE) IS
196 SELECT
197 NVL(SUM(FAR.PROCEEDS_OF_SALE - FAR.COST_OF_REMOVAL)
198 ,0) SALE
199 FROM
200 JAI_FA_AST_BLOCK_DTLS JBA,
201 FA_ADDITIONS FA,
202 JAI_FA_AST_BLOCKS JABLOA,
203 FA_RETIREMENTS FAR
204 WHERE FA.OWNED_LEASED = CP_OWNED_LEASED
205 AND FA.ASSET_ID = JBA.ASSET_ID
206 AND JBA.BLOCK_ID = JABLOA.BLOCK_ID
207 AND FAR.BOOK_TYPE_CODE = JABLOA.BOOK_TYPE_CODE
208 AND JABLOA.BOOK_TYPE_CODE = BOOK_NAME
209 AND JABLOA.TYPE = TYPE1
210 AND JABLOA.RATE = RATE
211 AND JBA.ASSET_ID = FAR.ASSET_ID
212 AND FAR.STATUS = CP_STATUS
213 AND FAR.DATE_RETIRED between START_DATE
214 AND END_DATE_N;
215 BEGIN
216 OPEN TYPE_RATE_CUR;
217 FETCH TYPE_RATE_CUR
218 INTO V_TYPE,V_RATE;
219 CLOSE TYPE_RATE_CUR;
220 OPEN BLOCK_ID_CUR_RET;
221 FETCH BLOCK_ID_CUR_RET
222 INTO V_BLOCK_ID;
223 CLOSE BLOCK_ID_CUR_RET;
224 OPEN SALE_CUR('OWNED','PROCESSED');
225 FETCH SALE_CUR
226 INTO V_SALE;
227 CLOSE SALE_CUR;
228 IF NVL(V_SALE
229 ,0) < 0 THEN
230 V_SALE := 0;
231 END IF;
232 NULL;
233 RETURN (V_SALE);
234 END CF_5FORMULA;
235
236 FUNCTION CF_6FORMULA(BLOCK_ID_v IN NUMBER
237 ,TYPE1 IN VARCHAR2
238 ,OP_TOT IN NUMBER
239 ,COS_TOT IN NUMBER
240 ,RATE IN NUMBER) RETURN NUMBER IS
241 LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
242 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
243 LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
244 START_DATE_T date;
245 CURSOR CUR_GET_VALID_ASSETS IS
246 SELECT
247 '1'
248 FROM
249 JAI_FA_AST_BLOCK_DTLS JBA,
250 FA_BOOKS FAB,
251 JAI_FA_AST_BLOCKS JABOA
252 WHERE JBA.ASSET_ID = FAB.ASSET_ID
253 AND ( ( JBA.ASSET_TYPE = LV_CAPITALIZED
254 AND FAB.CAPITALIZE_FLAG = LV_FLAG )
255 OR JBA.ASSET_TYPE = LV_EXPENSED )
256 AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
257 AND NVL(JABOA.YEAR_ENDED
258 ,END_DATE_N)
259 AND FAB.DATE_INEFFECTIVE IS NULL
260 AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
261 AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
262 AND JBA.BLOCK_ID = JABOA.BLOCK_ID
263 AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
264 AND JABOA.BOOK_TYPE_CODE = BOOK_NAME
265 AND JABOA.TYPE = (
266 SELECT
267 TYPE
268 FROM
269 JAI_FA_AST_BLOCKS
270 WHERE BLOCK_ID = BLOCK_ID_v )
271 AND ( JABOA.START_DATE <= NVL(START_DATE
272 ,JABOA.START_DATE)
273 OR JABOA.START_DATE BETWEEN NVL(START_DATE
274 ,JABOA.START_DATE)
275 AND NVL(END_DATE_N
276 ,JABOA.YEAR_ENDED) );
277 CURSOR CUR_GET_ST_OP_BAL(CP_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) IS
278 SELECT
279 OPENING_WDV
280 FROM
281 JAI_FA_AST_BLOCKS JABOA
282 WHERE JABOA.BOOK_TYPE_CODE = BOOK_NAME
283 AND JABOA.TYPE = (
284 SELECT
285 TYPE
286 FROM
287 JAI_FA_AST_BLOCKS
288 WHERE BLOCK_ID = CP_BLOCK_ID )
289 ORDER BY
290 START_DATE ASC;
291 LV_EXISTS VARCHAR2(1);
292 LN_OPENING_WDV JAI_FA_AST_BLOCKS.OPENING_WDV%TYPE;
293 CURSOR DEPR_CUR1 IS
294 SELECT
295 A.ASSET_ID,
296 A.COST,
297 A.TRANSACTION_HEADER_ID_IN,
298 A.DATE_EFFECTIVE
299 FROM
300 FA_BOOKS A,
301 JAI_FA_AST_BLOCKS B,
302 JAI_FA_AST_BLOCK_DTLS C,
303 JAI_FA_AST_PERIOD_RATES D
304 WHERE A.ASSET_ID = C.ASSET_ID
305 AND ( ( C.ASSET_TYPE = LV_CAPITALIZED
306 AND A.CAPITALIZE_FLAG = LV_FLAG )
307 OR C.ASSET_TYPE = LV_EXPENSED )
308 AND B.BLOCK_ID = BLOCK_ID_v
309 AND B.BLOCK_ID = C.BLOCK_ID
310 AND A.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
311 AND A.BOOK_TYPE_CODE = BOOK_NAME
312 AND C.DATE_OF_ACQUISITION between D.START_DATE
313 AND D.END_DATE
314 AND B.START_DATE between NVL(START_DATE_T,B.START_DATE)
315 AND NVL(END_DATE_N ,B.YEAR_ENDED)
316 AND D.START_DATE >= ADD_MONTHS(START_DATE_T,6)
317 AND D.SLNO = 2
318 AND A.DATE_INEFFECTIVE IS null
319 AND A.TRANSACTION_HEADER_ID_OUT IS null;
320 CURSOR BLOCK_RET_CUR_FIR(P_BLOCK_TYPE IN VARCHAR2) IS
321 SELECT
322 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
323 ,0) SALE
324 FROM
325 JAI_FA_AST_BLOCKS A,
326 FA_ADDITIONS B,
327 FA_RETIREMENTS C,
328 JAI_FA_AST_PERIOD_RATES D
329 WHERE B.CONTEXT = P_CONTEXT_VALUE
330 AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
331 AND A.BOOK_TYPE_CODE = BOOK_NAME
332 AND B.OWNED_LEASED = 'OWNED'
333 AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
334 AND TO_DATE(B.ATTRIBUTE1
335 ,'DD-MON-RRRR') between D.START_DATE
336 AND D.END_DATE
337 AND D.START_DATE < ADD_MONTHS(START_DATE_T
338 ,6)
339 AND D.SLNO = 1
340 AND B.ASSET_ID = C.ASSET_ID
341 AND C.STATUS = 'PROCESSED'
342 AND A.TYPE = P_BLOCK_TYPE
343 AND A.BLOCK_ID = BLOCK_ID_V
344 AND C.DATE_RETIRED BETWEEN START_DATE_T
345 AND END_DATE_N;
346 CURSOR BLOCK_RET_CUR_SEC(P_BLOCK_TYPE IN VARCHAR2) IS
347 SELECT
348 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
349 ,0) SALE
350 FROM
351 JAI_FA_AST_BLOCKS A,
352 FA_ADDITIONS B,
353 FA_RETIREMENTS C,
354 JAI_FA_AST_PERIOD_RATES D
355 WHERE B.CONTEXT = P_CONTEXT_VALUE
356 AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
357 AND A.BOOK_TYPE_CODE = BOOK_NAME
358 AND B.OWNED_LEASED = 'OWNED'
359 AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
360 AND TO_DATE(B.ATTRIBUTE1
361 ,'DD-MON-RRRR') between D.START_DATE
362 AND D.END_DATE
363 AND D.START_DATE >= ADD_MONTHS(START_DATE_T
364 ,6)
365 AND D.SLNO = 2
366 AND B.ASSET_ID = C.ASSET_ID
367 AND C.STATUS = 'PROCESSED'
368 AND A.TYPE = P_BLOCK_TYPE
369 AND A.BLOCK_ID = BLOCK_ID_V
370 AND C.DATE_RETIRED BETWEEN START_DATE_T
371 AND END_DATE_N;
372 V_COST NUMBER;
373 V_TOTAL1 NUMBER;
374 V_TOTAL2 NUMBER;
375 V_DEPRN NUMBER;
376 V_DEPRNAFTER NUMBER;
377 V_DEPRNBEFORE NUMBER;
378 LN_TOTAL_SEC_HF_COST NUMBER := 0;
379 V_FIR_DEDUCTION NUMBER;
380 V_SEC_DEDUCTION NUMBER;
381 BEGIN
382 START_DATE_t:=START_DATE;
383 LV_CAPITALIZED := 'CAPITALIZED';
384 LV_FLAG := 'YES';
385 LV_EXPENSED := 'EXPENSED';
386 OPEN CUR_GET_VALID_ASSETS;
387 FETCH CUR_GET_VALID_ASSETS
388 INTO LV_EXISTS;
389 OPEN CUR_GET_ST_OP_BAL(CP_BLOCK_ID => BLOCK_ID_v);
390 FETCH CUR_GET_ST_OP_BAL
391 INTO LN_OPENING_WDV;
392 CLOSE CUR_GET_ST_OP_BAL;
393 IF CUR_GET_VALID_ASSETS%NOTFOUND AND LN_OPENING_WDV = 0 THEN
394 /*SRW.MESSAGE(1275
395 ,'5.6.2 opening wdv for the first period record for the type and book name is 0')*/NULL;
396 V_DEPRN := 0;
397 ELSE
398 LV_CAPITALIZED := 'CAPITALIZED';
399 LV_FLAG := 'YES';
400 LV_EXPENSED := 'EXPENSED';
401 V_FIR_DEDUCTION := NULL;
402 V_SEC_DEDUCTION := NULL;
403 OPEN BLOCK_RET_CUR_FIR(TYPE1);
404 FETCH BLOCK_RET_CUR_FIR
405 INTO V_FIR_DEDUCTION;
409 INTO V_SEC_DEDUCTION;
406 CLOSE BLOCK_RET_CUR_FIR;
407 OPEN BLOCK_RET_CUR_SEC(TYPE1);
408 FETCH BLOCK_RET_CUR_SEC
410 CLOSE BLOCK_RET_CUR_SEC;
411 FOR rec_depr_cur IN DEPR_CUR1 LOOP
412 LN_TOTAL_SEC_HF_COST := CALCULATE_ACTUAL_COST(P_TRANSACTION_HEADER_ID_IN => REC_DEPR_CUR.TRANSACTION_HEADER_ID_IN
413 ,P_DATE_EFFECTIVE => REC_DEPR_CUR.DATE_EFFECTIVE
414 ,P_BOOK_NAME => BOOK_NAME
415 ,P_ASSET_ID => REC_DEPR_CUR.ASSET_ID
416 ,P_COST => NVL(REC_DEPR_CUR.COST
417 ,0)) + LN_TOTAL_SEC_HF_COST;
418 END LOOP;
419 /*SRW.MESSAGE(1275
420 ,'CF_6 1.ln_total_sec_hf_cost ->' || LN_TOTAL_SEC_HF_COST)*/NULL;
421 V_TOTAL2 := NVL(OP_TOT
422 ,0) + NVL(COS_TOT
423 ,0) - NVL(LN_TOTAL_SEC_HF_COST
424 ,0) - NVL(V_FIR_DEDUCTION
425 ,0);
426 V_DEPRNBEFORE := V_TOTAL2 * (RATE / 100);
427 /*SRW.MESSAGE(1275
428 ,'CF_6 2.1 V_Total2 -> ' || V_TOTAL2 || ', v_DeprnBefore -> ' || V_DEPRNBEFORE)*/NULL;
429 IF V_TOTAL2 < 0 THEN
430 V_DEPRNBEFORE := 0;
431 LN_TOTAL_SEC_HF_COST := NVL(LN_TOTAL_SEC_HF_COST
432 ,0) + V_TOTAL2;
433 ELSE
434 V_DEPRNBEFORE := V_TOTAL2 * (RATE / 100);
435 END IF;
436 LN_TOTAL_SEC_HF_COST := NVL(LN_TOTAL_SEC_HF_COST
437 ,0) - NVL(V_SEC_DEDUCTION
438 ,0);
439 V_DEPRNAFTER := NVL(LN_TOTAL_SEC_HF_COST
440 ,0) * (RATE / 100) * 0.5;
441 V_DEPRN := V_DEPRNBEFORE + V_DEPRNAFTER;
442 IF V_DEPRN <= 0 THEN
443 V_DEPRN := 0;
444 END IF;
445 END IF;
446 CLOSE CUR_GET_VALID_ASSETS;
447 NULL;
448 RETURN (V_DEPRN);
449 END CF_6FORMULA;
450
451 PROCEDURE DEPRECIATION1(P_BLOCK_ID IN NUMBER
452 ,P_OPENING_WDV IN NUMBER
453 ,P_CLOSING_BALANCE IN NUMBER
454 ,P_OPENING_WDV_ADJ IN NUMBER
455 ,P_DEPN_ADJ IN NUMBER
456 ,P_YEAR_END IN DATE) IS
457 V_BLOCK_HISTORY_ID NUMBER;
458 V_COUNT NUMBER;
459 CURSOR V_DEPN_ADJUSTMENT_CUR IS
460 SELECT
461 count(*)
462 FROM
463 JAI_FA_DEP_BLOCKS
464 WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
465 AND SLNO = - 1;
466 BEGIN
467 /*SRW.MESSAGE(1051
468 ,'Entered depreciation1 ....')*/NULL;
469 OPEN V_DEPN_ADJUSTMENT_CUR;
470 FETCH V_DEPN_ADJUSTMENT_CUR
471 INTO V_COUNT;
472 CLOSE V_DEPN_ADJUSTMENT_CUR;
473 SELECT
474 MAX(BLOCK_HISTORY_ID)
475 INTO V_BLOCK_HISTORY_ID
476 FROM
477 JAI_FA_AST_BLOCK_H;
478 IF V_BLOCK_HISTORY_ID IS NULL THEN
479 V_BLOCK_HISTORY_ID := 1;
480 /*SRW.MESSAGE(1052
481 ,'in the IF of v_block_history' || V_BLOCK_HISTORY_ID)*/NULL;
482 ELSE
483 V_BLOCK_HISTORY_ID := V_BLOCK_HISTORY_ID + 1;
484 END IF;
485 /*SRW.MESSAGE(1053
486 ,'v_block_history ' || V_BLOCK_HISTORY_ID)*/NULL;
487 INSERT INTO JAI_FA_AST_BLOCK_H
488 (BLOCK_HISTORY_ID
489 ,BLOCK_ID
490 ,OPENING_WDV
491 ,CLOSING_WDV
492 ,OPENING_WDV_ADJ
493 ,DEPN_ADJ
494 ,CREATION_DATE
495 ,CREATED_BY
496 ,LAST_UPDATE_DATE
497 ,LAST_UPDATE_LOGIN
498 ,LAST_UPDATED_BY)
499 VALUES (V_BLOCK_HISTORY_ID
500 ,P_BLOCK_ID
501 ,P_OPENING_WDV
502 ,P_CLOSING_BALANCE
503 ,P_OPENING_WDV_ADJ
504 ,P_DEPN_ADJ
505 ,SYSDATE
506 ,UID
507 ,SYSDATE
508 ,UID
509 ,UID);
510 /*SRW.MESSAGE(1053
511 ,'Before updating JAI_FA_AST_BLOCKS in DEPRECIATION1.. ')*/NULL;
512 UPDATE
513 JAI_FA_AST_BLOCKS
514 SET
515 OPENING_WDV = NVL(P_OPENING_WDV
516 ,0) + NVL(P_OPENING_WDV_ADJ
517 ,0)
518 WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID);
519 /*SRW.MESSAGE(1054
520 ,'updated JAI_FA_AST_BLOCKS...in DEPRECIATION1..')*/NULL;
521 /*SRW.MESSAGE(1054
522 ,'No of rows updated in JAI_FA_AST_BLOCKS.....' || SQL%ROWCOUNT)*/NULL;
523 IF P_DEPN_ADJ IS NOT NULL THEN
524 /*SRW.MESSAGE(1055
525 ,'p_depn_adj is not null...DEPRECIATION1')*/NULL;
526 IF V_COUNT = 0 THEN
527 /*SRW.MESSAGE(1056
528 ,'v_count is 0...DEPRECIATION1')*/NULL;
529 INSERT INTO JAI_FA_DEP_BLOCKS
530 (BLOCK_DEPN_ID
531 ,BLOCK_ID
532 ,SLNO
533 ,DEPN_OF_ASSETS
534 ,UNPLANNED_DEPN
535 ,YEAR_ENDED
536 ,CREATION_DATE
537 ,CREATED_BY
538 ,LAST_UPDATE_DATE
539 ,LAST_UPDATE_LOGIN
540 ,LAST_UPDATED_BY)
541 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
542 ,P_BLOCK_ID
543 ,-1
544 ,NULL
545 ,P_DEPN_ADJ
546 ,P_YEAR_END
547 ,SYSDATE
548 ,UID
549 ,SYSDATE
550 ,UID
551 ,UID);
552 /*SRW.MESSAGE(1057
553 ,'inserted into JAI_FA_DEP_BLOCKS...DEPRECIATION1')*/NULL;
554 ELSE
555 UPDATE
556 JAI_FA_DEP_BLOCKS
560 AND SLNO = - 1;
557 SET
558 UNPLANNED_DEPN = P_DEPN_ADJ
559 WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
561 /*SRW.MESSAGE(1058
562 ,'updating JAI_FA_DEP_BLOCKS..3...DEPRECIATION1')*/NULL;
563 /*SRW.MESSAGE(1059
564 ,'no.of rows updated JAI_FA_DEP_BLOCKS...DEPRECIATION1' || SQL%ROWCOUNT)*/NULL;
565 END IF;
566 END IF;
567 END DEPRECIATION1;
568
569 FUNCTION CF_7FORMULA(ASSET_ID IN NUMBER
570 ,BLOCK_ID1 IN NUMBER) RETURN NUMBER IS
571 CURSOR DEPN_OF_ASSET_CUR IS
572 SELECT
573 NVL(DEPN_OF_ASSETS
574 ,0)
575 FROM
576 JAI_FA_DEP_BLOCKS
577 WHERE UNPLANNED_DEPN = ASSET_ID
578 AND TO_CHAR(BLOCK_ID) = TO_CHAR(BLOCK_ID1);
579 V_DEPN_OF_ASSET NUMBER;
580 BEGIN
581 OPEN DEPN_OF_ASSET_CUR;
582 FETCH DEPN_OF_ASSET_CUR
583 INTO V_DEPN_OF_ASSET;
584 CLOSE DEPN_OF_ASSET_CUR;
585 /*SRW.MESSAGE(1275
586 ,'CF_7 Asset_Id -> ' || ASSET_ID || ', block_id1 -> ' || BLOCK_ID1 || ', v_depn_of_asset -> ' || V_DEPN_OF_ASSET)*/NULL;
587 RETURN (V_DEPN_OF_ASSET);
588 END CF_7FORMULA;
589
590 PROCEDURE RETIRE(P_YEAR_START IN DATE
591 ,P_YEAR_END IN DATE
592 ,P_BOOK_NAME IN VARCHAR2) IS
593 LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
594 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
595 LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
596 LV_OWNED VARCHAR2(10);
597 K NUMBER;
598 I NUMBER := 0;
599 V_COUNT1 NUMBER;
600 V_COUNTER NUMBER;
601 V_ASSET_ID NUMBER;
602 V_OWNED_LEASED VARCHAR2(15);
603 V_COST NUMBER;
604 V_SLNO NUMBER;
605 V_START_DATE DATE;
606 V_END_DATE DATE;
607 V_RATE NUMBER;
608 V_OPENING_BALANCE NUMBER;
609 V_BLOCK_ID NUMBER;
610 V_PROCEEDS_OF_SALE NUMBER;
611 V_AMOUNT NUMBER;
612 V_BLOCK_RATE NUMBER;
613 V_EXEMPT_UPTO NUMBER;
614 V_COST_LESS NUMBER;
615 V_AMOUNT1 NUMBER;
616 V_CLOSING NUMBER;
617 V_MORE_AMOUNT NUMBER;
618 V_MORE_AMOUNT1 NUMBER;
619 V_LESS_AMOUNT NUMBER;
620 V_TYPE VARCHAR2(40);
621 V_LEFT_AMOUNT NUMBER;
622 V_LEFT_BLOCK_ID NUMBER;
623 V_NEG_DEP NUMBER;
624 V_ASSET_COUNT NUMBER;
625 V_RETIRE_COUNT1 NUMBER;
626 V_ORIGINAL_COUNT NUMBER;
627 V_OPENING_WDV NUMBER;
628 V_PERIOD_RATE NUMBER;
629 V_BLOCK_ID1 NUMBER;
630 V_DEPRECIATION NUMBER;
631 V_CLOSING_BALANCE NUMBER;
632 V_OPENING_WDV1 NUMBER;
633 V_COUNT NUMBER;
634 V_AQUISATION_DATE DATE;
635 V_COUNT2 NUMBER;
636 V_DATE_PLACED_IN_SERVICE DATE;
637 V_RETIRE_COUNT NUMBER;
638 V_BLOCK_ID2 NUMBER;
639 V_CURRENT_UNITS NUMBER;
640 V_CLOSING1 NUMBER;
641 V_DEPRECIATION_COST NUMBER;
642 V_DEPRECIATION_COST1 NUMBER;
643 V_OLD_CLOSING_WDV NUMBER;
644 V_OPENING_WDV_ADJ NUMBER;
645 V_BLOCK_HISTORY_ID NUMBER;
646 V_DEPN_ADJ NUMBER;
647 V_YEAR_END DATE;
648 V_PREV_CLOSING_BALANCE NUMBER;
649 V_DEPN_SLNO NUMBER;
650 V_SERIAL_NUM1 NUMBER;
651 V_DEPN_AMOUNT NUMBER := 0;
652 V_FLAG BOOLEAN := FALSE;
653 V_CLOSING_BLOCK_ID NUMBER;
654 V_UNPLANNED_DEPN NUMBER;
655 V_RETIRED NUMBER;
656 CURSOR ASSET_BLOCK_CUR IS
657 SELECT
658 DISTINCT
659 A.ASSET_ID,
660 B.BLOCK_ID,
661 B.OPENING_WDV,
662 B.RATE BLOCK_RATE,
663 C.SLNO,
664 C.START_DATE,
665 C.END_DATE,
666 C.RATE PERIOD_RATE,
667 C.EXEMPT_UPTO,
668 A.DATE_OF_ACQUISITION AQUISATION_DATE,
669 D.DATE_PLACED_IN_SERVICE,
670 E.CURRENT_UNITS
671 FROM
672 JAI_FA_AST_BLOCK_DTLS A,
673 JAI_FA_AST_BLOCKS B,
674 JAI_FA_AST_PERIOD_RATES C,
675 FA_BOOKS D,
676 FA_ADDITIONS E
677 WHERE E.OWNED_LEASED = LV_OWNED
678 AND E.ASSET_ID = A.ASSET_ID
679 AND NVL(A.ASSET_ID
680 ,0) = NVL(D.ASSET_ID
681 ,0)
682 AND D.DATE_PLACED_IN_SERVICE between C.START_DATE
683 AND C.END_DATE
684 AND B.START_DATE >= P_YEAR_START
685 AND B.START_DATE <= P_YEAR_END
686 AND C.YEAR_START = P_YEAR_START
687 AND C.YEAR_END = P_YEAR_END
688 AND A.BLOCK_ID = B.BLOCK_ID
689 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
690 AND D.DATE_INEFFECTIVE is null
691 AND D.TRANSACTION_HEADER_ID_OUT is null
692 AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
693 AND D.CAPITALIZE_FLAG = LV_FLAG )
694 OR A.ASSET_TYPE = LV_EXPENSED )
695 ORDER BY
696 A.ASSET_ID;
697 CURSOR COUNT_CUR(P_BLOCK_ID IN NUMBER) IS
698 SELECT
699 count(*)
700 FROM
701 JAI_FA_AST_BLOCK_DTLS A,
702 JAI_FA_AST_BLOCKS B,
703 JAI_FA_AST_PERIOD_RATES C,
704 FA_BOOKS D,
705 FA_ADDITIONS E
706 WHERE E.OWNED_LEASED = 'OWNED'
707 AND E.ASSET_ID = A.ASSET_ID
708 AND NVL(A.ASSET_ID
709 ,0) = NVL(D.ASSET_ID
710 ,0)
711 AND D.DATE_PLACED_IN_SERVICE between C.START_DATE
712 AND C.END_DATE
713 AND B.START_DATE >= P_YEAR_START
714 AND B.START_DATE <= P_YEAR_END
715 AND C.YEAR_START = P_YEAR_START
716 AND C.YEAR_END = P_YEAR_END
717 AND A.BLOCK_ID = B.BLOCK_ID
718 AND B.BLOCK_ID = P_BLOCK_ID
719 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
723 AND B.YEAR_ENDED is NOT null
720 AND D.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
721 AND NVL(B.CLOSING_WDV
722 ,0) <> 0
724 AND D.DATE_INEFFECTIVE is null
725 AND D.TRANSACTION_HEADER_ID_OUT is null
726 AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
727 AND D.CAPITALIZE_FLAG = LV_FLAG )
728 OR A.ASSET_TYPE = LV_EXPENSED )
729 ORDER BY
730 A.ASSET_ID;
731 CURSOR BLOCK_RET_CUR IS
732 SELECT
733 A.BLOCK_ID,
734 NVL(SUM(C.PROCEEDS_OF_SALE)
735 ,0) SALE
736 FROM
737 JAI_FA_AST_BLOCKS A,
738 JAI_FA_AST_BLOCK_DTLS B,
739 FA_RETIREMENTS C
740 WHERE A.BLOCK_ID = B.BLOCK_ID
741 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
742 AND B.ASSET_ID = C.ASSET_ID
743 AND C.DATE_RETIRED between P_YEAR_START
744 AND P_YEAR_END
745 GROUP BY
746 A.BLOCK_ID;
747 CURSOR TYPE_RATE_CUR(P_BLOCK_ID IN NUMBER) IS
748 SELECT
749 TYPE,
750 RATE
751 FROM
752 JAI_FA_AST_BLOCKS
753 WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
754 AND BOOK_TYPE_CODE = P_BOOK_NAME;
755 CURSOR BLOCK_ID_CUR_RET(P_TYPE IN VARCHAR2,P_RATE IN NUMBER) IS
756 SELECT
757 BLOCK_ID,
758 OPENING_WDV,
759 RATE,
760 TYPE,
761 OPENING_WDV_ADJ,
762 DEPN_ADJ
763 FROM
764 JAI_FA_AST_BLOCKS
765 WHERE TYPE = P_TYPE
766 AND RATE = P_RATE
767 AND BOOK_TYPE_CODE = P_BOOK_NAME
768 AND START_DATE BETWEEN P_YEAR_START
769 AND P_YEAR_END;
770 CURSOR FA_RETIREMENTS_CUR(P_BLOCK_ID IN NUMBER) IS
771 SELECT
772 DISTINCT
773 SUM(NVL(A.PROCEEDS_OF_SALE
774 ,0)) SALE
775 FROM
776 FA_RETIREMENTS A,
777 JAI_FA_AST_BLOCK_DTLS B,
778 JAI_FA_AST_BLOCKS C
779 WHERE A.ASSET_ID = B.ASSET_ID
780 AND B.BLOCK_ID = P_BLOCK_ID
781 AND B.BLOCK_ID = C.BLOCK_ID
782 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
783 AND A.DATE_RETIRED between P_YEAR_START
784 AND P_YEAR_END
785 ORDER BY
786 B.BLOCK_ID;
787 CURSOR COST_LESS_EXEMPT_UPTO_CUR(P_ASSET_ID IN NUMBER,P_START_DATE IN DATE,P_END_DATE IN DATE,P_EXEMPT_UPTO IN NUMBER,P_BLOCK_ID IN NUMBER) IS
788 SELECT
789 SUM(A.ORIGINAL_COST) COSTING,
790 A.ASSET_ID,
791 A.DATE_PLACED_IN_SERVICE
792 FROM
793 FA_BOOKS A,
794 JAI_FA_AST_BLOCK_DTLS B,
795 FA_ADDITIONS C
796 WHERE A.ASSET_ID = P_ASSET_ID
797 AND A.ASSET_ID = B.ASSET_ID
798 AND A.DATE_INEFFECTIVE is null
799 AND A.TRANSACTION_HEADER_ID_OUT is null
800 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
801 AND A.CAPITALIZE_FLAG = LV_FLAG )
802 OR B.ASSET_TYPE = LV_EXPENSED )
803 AND B.BLOCK_ID = P_BLOCK_ID
804 AND A.ASSET_ID = C.ASSET_ID
805 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
806 AND A.DATE_PLACED_IN_SERVICE between P_START_DATE
807 AND P_END_DATE
808 AND ( A.ORIGINAL_COST / C.CURRENT_UNITS ) <= NVL(P_EXEMPT_UPTO
809 ,0)
810 GROUP BY
811 A.ASSET_ID,
812 A.DATE_PLACED_IN_SERVICE;
813 CURSOR COST_MORE_EXEMPT_UPTO_CUR(P_ASSET_ID IN NUMBER,P_START_DATE IN DATE,P_END_DATE IN DATE,P_EXEMPT_UPTO IN NUMBER,P_BLOCK_ID IN NUMBER) IS
814 SELECT
815 SUM(A.ORIGINAL_COST) COSTING,
816 A.ASSET_ID,
817 A.DATE_PLACED_IN_SERVICE
818 FROM
819 FA_BOOKS A,
820 JAI_FA_AST_BLOCK_DTLS B,
821 FA_ADDITIONS C
822 WHERE A.ASSET_ID = P_ASSET_ID
823 AND A.ASSET_ID = B.ASSET_ID
824 AND A.ASSET_ID = B.ASSET_ID
825 AND A.DATE_INEFFECTIVE is null
826 AND A.TRANSACTION_HEADER_ID_OUT is null
827 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
828 AND A.CAPITALIZE_FLAG = LV_FLAG )
829 OR B.ASSET_TYPE = LV_EXPENSED )
830 AND B.BLOCK_ID = P_BLOCK_ID
831 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
832 AND A.DATE_PLACED_IN_SERVICE between P_START_DATE
833 AND P_END_DATE
834 AND ( A.ORIGINAL_COST / C.CURRENT_UNITS ) > NVL(P_EXEMPT_UPTO
835 ,0)
836 GROUP BY
837 A.ASSET_ID,
838 A.DATE_PLACED_IN_SERVICE;
839 CURSOR TEMP_TAB IS
840 SELECT
841 SUM(DEPN_OF_ASSETS) ASSETS,
842 SUM(TOTAL_BALANCE) BALANCE,
843 SUM(SLNO) COST,
844 SUM(FULL_EXEMPT) COST_FULL,
845 BLOCK_ID
846 FROM
847 JAI_FA_DEP_BLOCKS_T
848 GROUP BY
849 BLOCK_ID;
850 CURSOR TYPE_CUR(P_BLOCK_ID IN NUMBER) IS
851 SELECT
852 TYPE,
853 RATE,
854 OPENING_WDV,
855 OPENING_WDV_ADJ,
856 DEPN_ADJ
857 FROM
858 JAI_FA_AST_BLOCKS
859 WHERE BLOCK_ID = P_BLOCK_ID
860 AND BOOK_TYPE_CODE = P_BOOK_NAME;
861 CURSOR LEFT_AMOUNT(P_BLOCK_ID IN NUMBER) IS
862 SELECT
863 SUM(EXEMPT_AMOUNT) L_AMOUNT,
864 BLOCK_ID
865 FROM
866 JAI_FA_EXEMPTIONS
867 WHERE BLOCK_ID = P_BLOCK_ID
868 GROUP BY
869 BLOCK_ID;
870 CURSOR RETIRE_ASSET_CUR(P_ASSET_ID IN NUMBER,P_BLOCK_ID IN NUMBER) IS
871 SELECT
872 A.UNITS,
873 B.BLOCK_ID
874 FROM
875 FA_RETIREMENTS A,
876 JAI_FA_AST_BLOCK_DTLS B
877 WHERE A.DATE_RETIRED <= P_YEAR_END
878 AND A.ASSET_ID = B.ASSET_ID
882 CURSOR ASSET_COUNT_CUR(P_ASSET_ID IN NUMBER,P_BLOCK_ID IN NUMBER) IS
879 AND A.STATUS = 'PROCESSED'
880 AND A.ASSET_ID = P_ASSET_ID
881 AND B.BLOCK_ID = TO_CHAR(P_BLOCK_ID);
883 SELECT
884 DISTINCT
885 H.UNITS,
886 H.ASSET_ID
887 FROM
888 FA_ASSET_HISTORY H,
889 JAI_FA_AST_BLOCK_DTLS A
890 WHERE TRANSACTION_HEADER_ID_IN IN (
891 SELECT
892 MIN(TRANSACTION_HEADER_ID_IN)
893 FROM
894 FA_ASSET_HISTORY
895 GROUP BY
896 ASSET_ID )
897 AND A.ASSET_ID = H.ASSET_ID
898 AND A.BLOCK_ID = P_BLOCK_ID
899 AND A.ASSET_ID = P_ASSET_ID;
900 CURSOR BLOCK_ID_CUR IS
901 SELECT
902 BLOCK_ID
903 FROM
904 JAI_FA_AST_BLOCKS
905 WHERE TO_CHAR(BLOCK_ID) NOT IN (
906 SELECT
907 BLOCK_ID
908 FROM
909 JAI_FA_AST_BLOCK_DTLS )
910 AND START_DATE >= P_YEAR_START
911 AND START_DATE <= P_YEAR_END;
912 CURSOR CLOSING_BALANCE_CUR(P_BLOCK_ID IN NUMBER) IS
913 SELECT
914 DISTINCT
915 B.OPENING_WDV,
916 B.OPENING_WDV_ADJ,
917 B.DEPN_ADJ,
918 B.RATE BLOCK_RATE,
919 B.BLOCK_ID,
920 B.TYPE
921 FROM
922 JAI_FA_AST_BLOCKS B,
923 JAI_FA_AST_PERIOD_RATES C
924 WHERE B.START_DATE >= P_YEAR_START
925 AND B.START_DATE <= P_YEAR_END
926 AND C.YEAR_START >= P_YEAR_START
927 AND C.YEAR_END <= P_YEAR_END
928 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
929 AND B.BLOCK_ID = P_BLOCK_ID;
930 CURSOR ASSET_COUNT_CUR1(P_BLOCK_ID IN NUMBER) IS
931 SELECT
932 count(*)
933 FROM
934 JAI_FA_AST_BLOCKS
935 WHERE CLOSING_WDV is not null
936 AND YEAR_ENDED is not null
937 AND BLOCK_ID = P_BLOCK_ID
938 AND BOOK_TYPE_CODE = P_BOOK_NAME
939 AND START_DATE >= P_YEAR_START
940 AND START_DATE <= P_YEAR_END;
941 CURSOR RETIRE_COUNT IS
942 SELECT
943 COUNT(*)
944 FROM
945 FA_RETIREMENTS
946 WHERE DATE_RETIRED BETWEEN P_YEAR_START
947 AND P_YEAR_END;
948 CURSOR RET_ASSET_BLOCK_CUR IS
949 SELECT
950 count(*)
951 FROM
952 FA_RETIREMENTS A,
953 JAI_FA_AST_BLOCK_DTLS B,
954 JAI_FA_AST_BLOCKS C
955 WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
956 AND P_YEAR_END
957 AND A.ASSET_ID = B.ASSET_ID
958 AND B.BLOCK_ID = C.BLOCK_ID
959 AND C.START_DATE between P_YEAR_START
960 AND P_YEAR_END;
961 CURSOR ASSET_ID_CURSOR(P_BLOCK_ID IN NUMBER) IS
962 SELECT
963 ASSET_ID
964 FROM
965 JAI_FA_AST_BLOCK_DTLS
966 WHERE BLOCK_ID = P_BLOCK_ID;
967 CURSOR OPENING_WDV_ADJ_CUR IS
968 SELECT
969 RATE,
970 TYPE
971 FROM
972 JAI_FA_AST_BLOCKS
973 WHERE START_DATE BETWEEN P_YEAR_START
974 AND P_YEAR_END
975 AND OPENING_WDV_ADJ is not null
976 OR DEPN_ADJ is not null
977 AND BOOK_TYPE_CODE = P_BOOK_NAME
978 AND TO_CHAR(BLOCK_ID) not in (
979 SELECT
980 BLOCK_ID
981 FROM
982 JAI_FA_AST_BLOCK_DTLS );
983 CURSOR ASSET_ADD_PR_CUR IS
984 SELECT
985 RATE,
986 TYPE
987 FROM
988 JAI_FA_AST_BLOCKS
989 WHERE START_DATE BETWEEN P_YEAR_START
990 AND P_YEAR_END
991 AND BOOK_TYPE_CODE = P_BOOK_NAME
992 AND CLOSING_WDV > 0
993 AND TO_CHAR(BLOCK_ID) in (
994 SELECT
995 BLOCK_ID
996 FROM
997 JAI_FA_AST_BLOCK_DTLS );
998 CURSOR ADJUST_OPEN_CUR(P_RATE IN NUMBER,P_TYPE IN VARCHAR2) IS
999 SELECT
1000 BLOCK_ID,
1001 OPENING_WDV,
1002 CLOSING_WDV,
1003 RATE,
1004 START_DATE
1005 FROM
1006 JAI_FA_AST_BLOCKS
1007 WHERE RATE = P_RATE
1008 AND TYPE = P_TYPE
1009 AND START_DATE > P_YEAR_END
1010 AND BOOK_TYPE_CODE = P_BOOK_NAME;
1011 CURSOR YEAR_END_CUR(P_START_DATE IN DATE) IS
1012 SELECT
1013 YEAR_END
1014 FROM
1015 JAI_FA_AST_YEARS
1016 WHERE YEAR_START = P_START_DATE;
1017 CURSOR COUNT_DEPN_CUR(P_BLOCK_ID IN NUMBER,P_SLNO IN NUMBER,P_ASSET_ID IN NUMBER) IS
1018 SELECT
1019 count(*)
1020 FROM
1021 JAI_FA_DEP_BLOCKS
1022 WHERE SLNO > 0
1023 AND BLOCK_ID = P_BLOCK_ID
1024 AND SLNO = P_SLNO
1025 AND UNPLANNED_DEPN = P_ASSET_ID;
1026 CURSOR UNPLANNED_DEPN_CUR(P_BLOCK_ID IN NUMBER) IS
1027 SELECT
1028 UNPLANNED_DEPN
1029 FROM
1030 JAI_FA_DEP_BLOCKS
1031 WHERE BLOCK_ID = P_BLOCK_ID
1032 AND SLNO = - 1;
1033 BEGIN
1034 LV_CAPITALIZED := 'CAPITALIZED';
1035 LV_FLAG := 'YES';
1036 LV_EXPENSED := 'EXPENSED';
1037 LV_OWNED := 'OWNED';
1038 FOR block_ret IN BLOCK_RET_CUR LOOP
1039 FOR type_rate IN TYPE_RATE_CUR(block_ret.block_id) LOOP
1040 FOR block_id_new IN BLOCK_ID_CUR_RET(type_rate.type,type_rate.rate) LOOP
1041 V_DEPRECIATION := NVL(NVL(BLOCK_ID_NEW.OPENING_WDV
1042 ,0) + NVL(BLOCK_ID_NEW.OPENING_WDV_ADJ
1043 ,0)
1044 ,0) * (BLOCK_ID_NEW.RATE / 100);
1048 ,0) - NVL(BLOCK_RET.SALE
1045 V_CLOSING_BALANCE := NVL(BLOCK_ID_NEW.OPENING_WDV
1046 ,0) + NVL(BLOCK_ID_NEW.OPENING_WDV_ADJ
1047 ,0) - NVL(V_DEPRECIATION
1049 ,0) - NVL(BLOCK_ID_NEW.DEPN_ADJ
1050 ,0);
1051 V_DEPRECIATION := ROUND(V_DEPRECIATION
1052 ,2) + NVL(BLOCK_ID_NEW.DEPN_ADJ
1053 ,0);
1054 V_CLOSING_BALANCE := ROUND(V_CLOSING_BALANCE
1055 ,2);
1056 OPEN ASSET_COUNT_CUR1(BLOCK_ID_NEW.BLOCK_ID);
1057 FETCH ASSET_COUNT_CUR1
1058 INTO V_COUNT2;
1059 CLOSE ASSET_COUNT_CUR1;
1060 OPEN UNPLANNED_DEPN_CUR(BLOCK_ID_NEW.BLOCK_ID);
1061 FETCH UNPLANNED_DEPN_CUR
1062 INTO V_UNPLANNED_DEPN;
1063 CLOSE UNPLANNED_DEPN_CUR;
1064 IF V_CLOSING_BALANCE > 0 THEN
1065 V_CLOSING_BALANCE := V_CLOSING_BALANCE;
1066 ELSE
1067 V_CLOSING_BALANCE := 0;
1068 END IF;
1069 IF NVL(V_COUNT2
1070 ,0) = 0 THEN
1071 UPDATE
1072 JAI_FA_AST_BLOCKS
1073 SET
1074 CLOSING_WDV = NVL(V_CLOSING_BALANCE
1075 ,0) - NVL(BLOCK_ID_NEW.DEPN_ADJ
1076 ,0)
1077 ,YEAR_ENDED = P_YEAR_END
1078 WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID;
1079 SELECT
1080 JAI_FA_AST_BLOCKS_S.NEXTVAL
1081 INTO V_BLOCK_ID1
1082 FROM
1083 DUAL;
1084 INSERT INTO JAI_FA_AST_BLOCKS
1085 (BLOCK_ID
1086 ,TYPE
1087 ,RATE
1088 ,BOOK_TYPE_CODE
1089 ,OPENING_WDV
1090 ,START_DATE
1091 ,CREATION_DATE
1092 ,CREATED_BY
1093 ,LAST_UPDATE_DATE
1094 ,LAST_UPDATE_LOGIN
1095 ,LAST_UPDATED_BY)
1096 VALUES (V_BLOCK_ID1
1097 ,BLOCK_ID_NEW.TYPE
1098 ,BLOCK_ID_NEW.RATE
1099 ,P_BOOK_NAME
1100 ,NVL(V_CLOSING_BALANCE
1101 ,0) - NVL(BLOCK_ID_NEW.DEPN_ADJ
1102 ,0)
1103 ,P_YEAR_END + 1
1104 ,SYSDATE
1105 ,UID
1106 ,SYSDATE
1107 ,UID
1108 ,UID);
1109 INSERT INTO JAI_FA_DEP_BLOCKS
1110 (BLOCK_DEPN_ID
1111 ,BLOCK_ID
1112 ,SLNO
1113 ,DEPN_OF_ASSETS
1114 ,YEAR_ENDED
1115 ,CREATION_DATE
1116 ,CREATED_BY
1117 ,LAST_UPDATE_DATE
1118 ,LAST_UPDATE_LOGIN
1119 ,LAST_UPDATED_BY)
1120 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
1121 ,BLOCK_ID_NEW.BLOCK_ID
1122 ,0
1123 ,V_DEPRECIATION
1124 ,P_YEAR_END
1125 ,SYSDATE
1126 ,UID
1127 ,SYSDATE
1128 ,UID
1129 ,UID);
1130 ELSE
1131 UPDATE
1132 JAI_FA_DEP_BLOCKS
1133 SET
1134 DEPN_OF_ASSETS = V_DEPRECIATION
1135 WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID
1136 AND SLNO = 0;
1137 UPDATE
1138 JAI_FA_AST_BLOCKS
1139 SET
1140 OPENING_WDV = NVL(V_CLOSING_BALANCE
1141 ,0) - NVL(V_UNPLANNED_DEPN
1142 ,0)
1143 WHERE START_DATE = P_YEAR_END + 1
1144 AND TYPE = BLOCK_ID_NEW.TYPE
1145 AND RATE = BLOCK_ID_NEW.RATE;
1146 UPDATE
1147 JAI_FA_AST_BLOCKS
1148 SET
1149 CLOSING_WDV = NVL(V_CLOSING_BALANCE
1150 ,0) - NVL(V_UNPLANNED_DEPN
1151 ,0)
1152 ,YEAR_ENDED = P_YEAR_END
1153 WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID;
1154 IF BLOCK_ID_NEW.DEPN_ADJ IS NOT NULL THEN
1155 UPDATE
1156 JAI_FA_DEP_BLOCKS
1157 SET
1158 UNPLANNED_DEPN = BLOCK_ID_NEW.DEPN_ADJ
1159 WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID
1160 AND SLNO = 0;
1161 END IF;
1162 END IF;
1163 END LOOP;
1164 END LOOP;
1165 END LOOP;
1166 END RETIRE;
1167
1168 PROCEDURE RUN_DEP(P_YEAR_START IN DATE
1169 ,P_YEAR_END IN DATE
1170 ,P_BOOK_NAME IN VARCHAR2) IS
1171 LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
1172 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
1173 LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
1174 LV_OWNED_LEASED FA_ADDITIONS.OWNED_LEASED%TYPE;
1175 LV_STATUS FA_RETIREMENTS.STATUS%TYPE;
1176 V_C NUMBER := 0;
1177 K NUMBER := 0;
1178 I NUMBER := 0;
1179 V_DEPR_VAL NUMBER;
1180 V_COUNT1 NUMBER;
1181 V_COUNTER NUMBER;
1182 V_ASSET_ID NUMBER;
1183 V_OWNED_LEASED VARCHAR2(15);
1184 V_COST NUMBER;
1185 V_SLNO NUMBER;
1186 V_START_DATE DATE;
1187 V_END_DATE DATE;
1188 V_RATE NUMBER;
1189 V_OPENING_BALANCE NUMBER;
1190 V_BLOCK_ID NUMBER;
1191 T_BLOCK_ID NUMBER;
1192 C_BLOCK_ID NUMBER;
1193 V_PROCEEDS_OF_SALE NUMBER;
1194 V_AMOUNT NUMBER;
1195 V_BLOCK_RATE NUMBER;
1196 V_EXEMPT_UPTO NUMBER;
1197 V_COST_LESS NUMBER;
1198 V_AMOUNT1 NUMBER;
1199 V_CLOSING NUMBER;
1200 V_MORE_AMOUNT NUMBER;
1204 V_LEFT_AMOUNT NUMBER;
1201 V_MORE_AMOUNT1 NUMBER;
1202 V_LESS_AMOUNT NUMBER;
1203 V_TYPE JAI_FA_AST_BLOCKS.TYPE%TYPE;
1205 V_LEFT_BLOCK_ID NUMBER;
1206 V_NEG_DEP NUMBER;
1207 V_ASSET_COUNT NUMBER;
1208 V_RETIRE_COUNT1 NUMBER;
1209 V_ORIGINAL_COUNT NUMBER;
1210 V_OPENING_WDV NUMBER;
1211 V_PERIOD_RATE NUMBER;
1212 V_BLOCK_ID1 NUMBER;
1213 V_DEPRECIATION NUMBER;
1214 V_CLOSING_BALANCE NUMBER;
1215 V_OPENING_WDV1 NUMBER;
1216 V_COUNT NUMBER;
1217 V_AQUISATION_DATE DATE;
1218 V_COUNT2 NUMBER;
1219 V_DATE_PLACED_IN_SERVICE DATE;
1220 V_RETIRE_COUNT NUMBER;
1221 V_BLOCK_ID2 NUMBER;
1222 V_CURRENT_UNITS NUMBER;
1223 V_CLOSING1 NUMBER;
1224 V_DEPRECIATION_COST NUMBER;
1225 V_DEPRECIATION_COST1 NUMBER;
1226 V_OLD_CLOSING_WDV NUMBER;
1227 V_OPENING_WDV_ADJ NUMBER;
1228 V_BLOCK_HISTORY_ID NUMBER;
1229 V_DEPN_ADJ NUMBER;
1230 V_YEAR_END DATE;
1231 V_PREV_CLOSING_BALANCE NUMBER;
1232 V_DEPN_SLNO NUMBER;
1233 V_SERIAL_NUM1 NUMBER;
1234 V_DEPN_AMOUNT NUMBER := 0;
1235 V_FLAG BOOLEAN := FALSE;
1236 V_CLOSING_BLOCK_ID NUMBER;
1237 V_UNPLANNED_DEPN NUMBER;
1238 V_RETIRED NUMBER;
1239 V_RETIRED1 NUMBER;
1240 LN_TRANSACTION_HEADER_ID_IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE;
1241 V_NO_ASSETS VARCHAR2(3);
1242 CURSOR ASSET_BLOCK_CUR IS
1243 SELECT
1244 DISTINCT
1245 A.ASSET_ID,
1246 B.BLOCK_ID,
1247 B.OPENING_WDV,
1248 B.RATE BLOCK_RATE,
1249 C.SLNO,
1250 C.START_DATE,
1251 C.END_DATE,
1252 C.RATE PERIOD_RATE,
1253 C.EXEMPT_UPTO,
1254 NVL(E.DATE_OF_ACQUISITION
1255 ,B.START_DATE) AQUISATION_DATE,
1256 D.DATE_PLACED_IN_SERVICE,
1257 A.CURRENT_UNITS
1258 FROM
1259 FA_ADDITIONS A,
1260 JAI_FA_AST_BLOCKS B,
1261 JAI_FA_AST_PERIOD_RATES C,
1262 FA_BOOKS D,
1263 JAI_FA_AST_BLOCK_DTLS E
1264 WHERE A.OWNED_LEASED = LV_OWNED_LEASED
1265 AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
1266 AND D.CAPITALIZE_FLAG = LV_FLAG )
1267 OR A.ASSET_TYPE = LV_EXPENSED )
1268 AND A.ASSET_ID = D.ASSET_ID
1269 AND A.ASSET_ID = E.ASSET_ID
1270 AND E.DATE_OF_ACQUISITION BETWEEN C.START_DATE
1271 AND C.END_DATE
1272 AND B.START_DATE >= P_YEAR_START
1273 AND B.START_DATE <= P_YEAR_END
1274 AND C.YEAR_START = P_YEAR_START
1275 AND C.YEAR_END = P_YEAR_END
1276 AND E.BLOCK_ID = B.BLOCK_ID
1277 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
1278 AND D.DATE_INEFFECTIVE IS null
1279 AND D.TRANSACTION_HEADER_ID_OUT IS null;
1280 CURSOR BLOCK_ID_CUR IS
1281 SELECT
1282 BLOCK_ID,
1283 TYPE
1284 FROM
1285 JAI_FA_AST_BLOCKS
1286 WHERE BLOCK_ID NOT IN (
1287 SELECT
1288 BLOCK_ID
1289 FROM
1290 JAI_FA_AST_BLOCK_DTLS
1291 WHERE DATE_OF_ACQUISITION BETWEEN P_YEAR_START
1292 AND P_YEAR_END )
1293 AND START_DATE >= P_YEAR_START
1294 AND START_DATE <= P_YEAR_END
1295 AND BOOK_TYPE_CODE = P_BOOK_NAME;
1296 CURSOR CLOSING_BALANCE_CUR(P_BLOCK_ID IN NUMBER) IS
1297 SELECT
1298 DISTINCT
1299 B.OPENING_WDV,
1300 B.OPENING_WDV_ADJ,
1301 B.DEPN_ADJ,
1302 B.RATE BLOCK_RATE,
1303 B.BLOCK_ID,
1304 B.TYPE
1305 FROM
1306 JAI_FA_AST_BLOCKS B,
1307 JAI_FA_AST_PERIOD_RATES C
1308 WHERE B.START_DATE >= P_YEAR_START
1309 AND B.START_DATE <= P_YEAR_END
1310 AND C.YEAR_START >= P_YEAR_START
1311 AND C.YEAR_END <= P_YEAR_END
1312 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
1313 AND B.BLOCK_ID = P_BLOCK_ID;
1314 CURSOR RET_CAL(P_TYPE IN VARCHAR2) IS
1315 SELECT
1316 NVL(SUM(A.PROCEEDS_OF_SALE - A.COST_OF_REMOVAL)
1317 ,0) SALE
1318 FROM
1319 FA_RETIREMENTS A,
1320 JAI_FA_AST_BLOCK_DTLS B,
1321 JAI_FA_AST_BLOCKS C,
1322 FA_BOOKS D
1323 WHERE A.ASSET_ID = B.ASSET_ID
1324 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
1325 AND D.CAPITALIZE_FLAG = LV_FLAG )
1326 OR B.ASSET_TYPE = LV_EXPENSED )
1327 AND D.ASSET_ID = B.ASSET_ID
1328 AND C.BLOCK_ID = B.BLOCK_ID
1329 AND A.STATUS = LV_STATUS
1330 AND C.TYPE = P_TYPE
1331 AND A.DATE_RETIRED BETWEEN P_YEAR_START
1332 AND P_YEAR_END
1333 AND A.RETIREMENT_ID = D.RETIREMENT_ID
1334 AND D.BOOK_TYPE_CODE = C.BOOK_TYPE_CODE
1335 AND C.BOOK_TYPE_CODE = P_BOOK_NAME;
1336 CURSOR BLOCK_RET_CUR(P_BLOCK_TYPE IN VARCHAR2) IS
1337 SELECT
1338 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
1339 ,0) SALE
1340 FROM
1341 JAI_FA_AST_BLOCKS A,
1342 JAI_FA_AST_BLOCK_DTLS B,
1343 FA_RETIREMENTS C
1344 WHERE A.BLOCK_ID = B.BLOCK_ID
1345 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
1346 AND B.ASSET_ID = C.ASSET_ID
1347 AND C.STATUS = LV_STATUS
1348 AND A.TYPE = P_BLOCK_TYPE
1349 AND C.DATE_RETIRED BETWEEN P_YEAR_START
1350 AND P_YEAR_END;
1351 CURSOR FA_RETIREMENTS_CUR(P_BLOCK_ID IN NUMBER) IS
1352 SELECT
1353 DISTINCT
1354 SUM(NVL(A.PROCEEDS_OF_SALE
1355 ,0)) SALE
1356 FROM
1357 FA_RETIREMENTS A,
1358 JAI_FA_AST_BLOCK_DTLS B,
1359 JAI_FA_AST_BLOCKS C
1360 WHERE A.ASSET_ID = B.ASSET_ID
1364 AND B.BLOCK_ID = C.BLOCK_ID
1361 AND B.ASSET_TYPE IN ( LV_CAPITALIZED , LV_EXPENSED )
1362 AND A.STATUS = LV_STATUS
1363 AND B.BLOCK_ID = P_BLOCK_ID
1365 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
1366 AND A.DATE_RETIRED BETWEEN P_YEAR_START
1367 AND P_YEAR_END
1368 ORDER BY
1369 B.BLOCK_ID;
1370 CURSOR COST_LESS_EXEMPT_UPTO_CUR(P_ASSET_ID IN NUMBER,P_START_DATE IN DATE,P_END_DATE IN DATE,P_EXEMPT_UPTO IN NUMBER,P_BLOCK_ID IN NUMBER,CP_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE) IS
1371 SELECT
1372 A.COST COSTING,
1373 A.ASSET_ID,
1374 A.DATE_PLACED_IN_SERVICE
1375 FROM
1376 FA_BOOKS A,
1377 FA_ADDITIONS B,
1378 JAI_FA_AST_BLOCK_DTLS C
1379 WHERE A.ASSET_ID = P_ASSET_ID
1380 AND A.ASSET_ID = C.ASSET_ID
1381 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
1382 AND A.CAPITALIZE_FLAG = LV_FLAG )
1383 OR B.ASSET_TYPE = LV_EXPENSED )
1384 AND A.ASSET_ID = B.ASSET_ID
1385 AND A.TRANSACTION_HEADER_ID_IN = CP_TRANSACTION_HEADER_ID_IN
1386 AND C.BLOCK_ID = TO_CHAR(P_BLOCK_ID)
1387 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
1388 AND C.DATE_OF_ACQUISITION BETWEEN P_START_DATE
1389 AND P_END_DATE
1390 AND ( A.COST / B.CURRENT_UNITS ) <= NVL(P_EXEMPT_UPTO
1391 ,0);
1392 CURSOR COST_MORE_EXEMPT_UPTO_CUR(P_ASSET_ID IN NUMBER,P_START_DATE IN DATE,P_END_DATE IN DATE,P_EXEMPT_UPTO IN NUMBER,P_BLOCK_ID IN NUMBER,CP_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE) IS
1393 SELECT
1394 A.COST COSTING,
1395 A.ASSET_ID,
1396 A.DATE_PLACED_IN_SERVICE
1397 FROM
1398 FA_BOOKS A,
1399 FA_ADDITIONS B,
1400 JAI_FA_AST_BLOCK_DTLS C
1401 WHERE A.ASSET_ID = P_ASSET_ID
1402 AND A.ASSET_ID = C.ASSET_ID
1403 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
1404 AND A.CAPITALIZE_FLAG = LV_FLAG )
1405 OR B.ASSET_TYPE = LV_EXPENSED )
1406 AND A.ASSET_ID = B.ASSET_ID
1407 AND A.TRANSACTION_HEADER_ID_IN = CP_TRANSACTION_HEADER_ID_IN
1408 AND C.BLOCK_ID = TO_CHAR(P_BLOCK_ID)
1409 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
1410 AND C.DATE_OF_ACQUISITION BETWEEN P_START_DATE
1411 AND P_END_DATE
1412 AND ( A.COST / B.CURRENT_UNITS ) > NVL(P_EXEMPT_UPTO
1413 ,0);
1414 EXEMPT COST_MORE_EXEMPT_UPTO_CUR%ROWTYPE;
1415 CURSOR TEMP_TAB IS
1416 SELECT
1417 BLOCK_ID BLOCKID,
1418 SUM(DEPN_OF_ASSETS) ASSETS,
1419 SUM(TOTAL_BALANCE) BALANCE,
1420 SUM(SLNO) COST,
1421 SUM(FULL_EXEMPT) COST_FULL,
1422 BLOCK_ID
1423 FROM
1424 JAI_FA_DEP_BLOCKS_T
1425 GROUP BY
1426 BLOCK_ID;
1427 CURSOR TYPE_CUR(P_BLOCK_ID IN NUMBER) IS
1428 SELECT
1429 TYPE,
1430 RATE,
1431 OPENING_WDV,
1432 OPENING_WDV_ADJ,
1433 DEPN_ADJ
1434 FROM
1435 JAI_FA_AST_BLOCKS
1436 WHERE BLOCK_ID = P_BLOCK_ID
1437 AND BOOK_TYPE_CODE = P_BOOK_NAME
1438 AND START_DATE >= P_YEAR_START
1439 AND START_DATE <= P_YEAR_END;
1440 CURSOR LEFT_AMOUNT(P_BLOCK_ID IN NUMBER) IS
1441 SELECT
1442 SUM(EXEMPT_AMOUNT) L_AMOUNT,
1443 BLOCK_ID
1444 FROM
1445 JAI_FA_EXEMPTIONS
1446 WHERE BLOCK_ID = P_BLOCK_ID
1447 GROUP BY
1448 BLOCK_ID;
1449 CURSOR RETIRE_ASSET_CUR(P_ASSET_ID IN NUMBER,P_BLOCK_ID IN NUMBER) IS
1450 SELECT
1451 A.UNITS,
1452 TO_NUMBER(B.BLOCK_ID)
1453 FROM
1454 FA_RETIREMENTS A,
1455 JAI_FA_AST_BLOCK_DTLS B
1456 WHERE A.DATE_RETIRED <= P_YEAR_END
1457 AND A.ASSET_ID = B.ASSET_ID
1458 AND A.STATUS = LV_STATUS
1459 AND A.ASSET_ID = P_ASSET_ID
1460 AND B.BLOCK_ID = P_BLOCK_ID;
1461 CURSOR ASSET_COUNT_CUR(P_ASSET_ID IN NUMBER,P_BLOCK_ID IN NUMBER) IS
1462 SELECT
1463 DISTINCT
1464 H.UNITS,
1465 H.ASSET_ID
1466 FROM
1467 FA_ASSET_HISTORY H,
1468 JAI_FA_AST_BLOCK_DTLS A
1469 WHERE H.TRANSACTION_HEADER_ID_IN IN (
1470 SELECT
1471 MIN(TRANSACTION_HEADER_ID_IN)
1472 FROM
1473 FA_ASSET_HISTORY
1474 GROUP BY
1475 ASSET_ID )
1476 AND A.ASSET_ID = H.ASSET_ID
1477 AND A.BLOCK_ID = P_BLOCK_ID
1478 AND A.ASSET_ID = P_ASSET_ID;
1479 CURSOR COUNT_CUR(P_BLOCK_ID IN NUMBER) IS
1480 SELECT
1481 count(*)
1482 FROM
1483 FA_ADDITIONS A,
1484 JAI_FA_AST_BLOCKS B,
1485 JAI_FA_AST_PERIOD_RATES C,
1486 FA_BOOKS D,
1487 JAI_FA_AST_BLOCK_DTLS E
1488 WHERE A.OWNED_LEASED = LV_OWNED_LEASED
1489 AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
1490 AND D.CAPITALIZE_FLAG = LV_FLAG )
1491 OR A.ASSET_TYPE = LV_EXPENSED )
1492 AND NVL(A.ASSET_ID
1493 ,0) = NVL(D.ASSET_ID
1494 ,0)
1495 AND NVL(A.ASSET_ID
1496 ,0) = E.ASSET_ID
1497 AND E.DATE_OF_ACQUISITION BETWEEN C.START_DATE
1498 AND C.END_DATE
1499 AND B.START_DATE >= P_YEAR_START
1500 AND B.START_DATE <= P_YEAR_END
1501 AND C.YEAR_START = P_YEAR_START
1502 AND C.YEAR_END = P_YEAR_END
1503 AND E.BLOCK_ID = B.BLOCK_ID
1504 AND B.BLOCK_ID = P_BLOCK_ID
1505 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
1506 AND D.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
1507 AND B.CLOSING_WDV IS NOT NULL
1508 AND B.YEAR_ENDED IS NOT NULL
1509 AND D.DATE_INEFFECTIVE IS NULL
1510 AND D.TRANSACTION_HEADER_ID_OUT IS NULL
1514 SELECT
1511 ORDER BY
1512 1;
1513 CURSOR ASSET_COUNT_CUR1(P_BLOCK_ID IN NUMBER) IS
1515 count(*)
1516 FROM
1517 JAI_FA_AST_BLOCKS
1518 WHERE CLOSING_WDV IS not null
1519 AND YEAR_ENDED IS not null
1520 AND BLOCK_ID = P_BLOCK_ID
1521 AND BOOK_TYPE_CODE = P_BOOK_NAME
1522 AND START_DATE >= P_YEAR_START
1523 AND START_DATE <= P_YEAR_END;
1524 CURSOR RETIRE_COUNT IS
1525 SELECT
1526 count(*)
1527 FROM
1528 FA_RETIREMENTS
1529 WHERE DATE_RETIRED BETWEEN P_YEAR_START
1530 AND P_YEAR_END;
1531 CURSOR RET_ASSET_BLOCK_CUR IS
1532 SELECT
1533 COUNT(*)
1534 FROM
1535 FA_RETIREMENTS A,
1536 JAI_FA_AST_BLOCK_DTLS B,
1537 JAI_FA_AST_BLOCKS C
1538 WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
1539 AND P_YEAR_END
1540 AND A.ASSET_ID = B.ASSET_ID
1541 AND B.BLOCK_ID = C.BLOCK_ID
1542 AND C.START_DATE BETWEEN P_YEAR_START
1543 AND P_YEAR_END;
1544 CURSOR ASSET_ID_CURSOR(P_BLOCK_ID IN NUMBER) IS
1545 SELECT
1546 ASSET_ID
1547 FROM
1548 JAI_FA_AST_BLOCK_DTLS
1549 WHERE BLOCK_ID = P_BLOCK_ID;
1550 CURSOR OPENING_WDV_ADJ_CUR IS
1551 SELECT
1552 RATE,
1553 TYPE
1554 FROM
1555 JAI_FA_AST_BLOCKS
1556 WHERE START_DATE BETWEEN P_YEAR_START
1557 AND P_YEAR_END
1558 AND NVL(OPENING_WDV_ADJ
1559 ,DEPN_ADJ) IS NOT NULL
1560 AND BOOK_TYPE_CODE = P_BOOK_NAME;
1561 CURSOR ASSET_ADD_PR_CUR IS
1562 SELECT
1563 JABOA.RATE,
1564 JABOA.TYPE
1565 FROM
1566 JAI_FA_AST_BLOCKS JABOA,
1567 JAI_FA_AST_BLOCK_DTLS JBA
1568 WHERE JABOA.START_DATE BETWEEN P_YEAR_START
1569 AND P_YEAR_END
1570 AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
1571 AND JABOA.CLOSING_WDV > 0
1572 AND JABOA.BLOCK_ID = JBA.BLOCK_ID;
1573 CURSOR ADJUST_OPEN_CUR(P_RATE IN NUMBER,P_TYPE IN VARCHAR2) IS
1574 SELECT
1575 BLOCK_ID,
1576 OPENING_WDV,
1577 CLOSING_WDV,
1578 RATE,
1579 START_DATE
1580 FROM
1581 JAI_FA_AST_BLOCKS
1582 WHERE RATE = P_RATE
1583 AND TYPE = P_TYPE
1584 AND START_DATE > P_YEAR_END
1585 AND BOOK_TYPE_CODE = P_BOOK_NAME;
1586 CURSOR YEAR_END_CUR(P_START_DATE IN DATE) IS
1587 SELECT
1588 YEAR_END
1589 FROM
1590 JAI_FA_AST_YEARS
1591 WHERE YEAR_START = P_START_DATE;
1592 CURSOR COUNT_DEPN_CUR(P_BLOCK_ID IN NUMBER,P_SLNO IN NUMBER,P_ASSET_ID IN NUMBER) IS
1593 SELECT
1594 COUNT(*)
1595 FROM
1596 JAI_FA_DEP_BLOCKS
1597 WHERE SLNO > 0
1598 AND BLOCK_ID = P_BLOCK_ID
1599 AND SLNO = P_SLNO
1600 AND UNPLANNED_DEPN = P_ASSET_ID;
1601 CURSOR UNPLANNED_DEPN_CUR(P_BLOCK_ID IN NUMBER) IS
1602 SELECT
1603 UNPLANNED_DEPN
1604 FROM
1605 JAI_FA_DEP_BLOCKS
1606 WHERE BLOCK_ID = P_BLOCK_ID
1607 AND SLNO = - 1;
1608 CURSOR COUNTER_CUR(P_BLOCK_ID IN NUMBER) IS
1609 SELECT
1610 COUNT(*)
1611 FROM
1612 JAI_FA_DEP_BLOCKS
1613 WHERE BLOCK_ID = P_BLOCK_ID
1614 AND SLNO = 0;
1615 BEGIN
1616 LV_CAPITALIZED := 'CAPITALIZED';
1617 LV_FLAG := 'YES';
1618 LV_EXPENSED := 'EXPENSED';
1619 LV_OWNED_LEASED := 'OWNED';
1620 LV_STATUS := 'PROCESSED';
1621 OPEN RETIRE_COUNT;
1622 FETCH RETIRE_COUNT
1623 INTO V_COUNTER;
1624 CLOSE RETIRE_COUNT;
1625 /*SRW.MESSAGE(1000
1626 ,'1 RETIRE_COUNT ' || TO_CHAR(V_COUNTER))*/NULL;
1627 BEGIN
1628 /*SRW.MESSAGE(1001
1629 ,'1.1 Before AsSET block Cur ' || TO_CHAR(V_COUNTER))*/NULL;
1630 OPEN RET_ASSET_BLOCK_CUR;
1631 FETCH RET_ASSET_BLOCK_CUR
1632 INTO V_RETIRE_COUNT1;
1633 CLOSE RET_ASSET_BLOCK_CUR;
1634 /*SRW.MESSAGE(1002
1635 ,'1.2 After AsSET block Cur ' || TO_CHAR(V_COUNTER))*/NULL;
1636 EXCEPTION
1637 WHEN OTHERS THEN
1638 /*SRW.MESSAGE(1003
1639 ,'1.3 In Message FIRST OTHERS ' || SQLERRM)*/NULL;
1640 END;
1641 /*SRW.MESSAGE(1004
1642 ,'2 ret_asset_block_cur -> ' || TO_CHAR(V_RETIRE_COUNT1))*/NULL;
1643 OPEN ASSET_BLOCK_CUR;
1644 V_COUNTER := 0;
1645 LOOP
1646 V_C := V_C + 1;
1647 EXEMPT := NULL;
1648 V_COST := NULL;
1649 V_COUNT := ASSET_BLOCK_CUR%ROWCOUNT;
1650 /*SRW.MESSAGE(1006
1651 ,'2.1 v_count, v_counter -> ' || TO_CHAR(V_COUNT) || ', ' || V_C)*/NULL;
1652 FETCH ASSET_BLOCK_CUR
1653 INTO V_ASSET_ID,V_BLOCK_ID,V_OPENING_WDV,V_BLOCK_RATE,V_SLNO,V_START_DATE,V_END_DATE,V_PERIOD_RATE,V_EXEMPT_UPTO,V_AQUISATION_DATE,V_DATE_PLACED_IN_SERVICE,V_CURRENT_UNITS;
1654 NULL;
1655 IF ASSET_BLOCK_CUR%NOTFOUND THEN
1656 /*SRW.MESSAGE(1006
1657 ,'2.1B No assets found in fa_additions_b')*/NULL;
1658 FOR block_id IN BLOCK_ID_CUR LOOP
1659 NULL;
1660 FOR closing IN CLOSING_BALANCE_CUR(block_id.block_id) LOOP
1661 NULL;
1662 V_RETIRED1 := NULL;
1663 /*SRW.MESSAGE(1007
1664 ,'2.2 before ret Cal')*/NULL;
1665 OPEN RET_CAL(BLOCK_ID.TYPE);
1666 FETCH RET_CAL
1667 INTO V_RETIRED1;
1668 CLOSE RET_CAL;
1669 /*SRW.MESSAGE(10070
1670 ,'2.21 value of v_retired1 is' || V_RETIRED1)*/NULL;
1674 ,0)
1671 V_DEPRECIATION := NVL(NVL(CLOSING.OPENING_WDV
1672 ,0) + NVL(CLOSING.OPENING_WDV_ADJ
1673 ,0) - NVL(V_RETIRED1
1675 ,0) * (CLOSING.BLOCK_RATE / 100);
1676 /*SRW.MESSAGE(1008
1677 ,'2.3 v_depreciation -> ' || TO_CHAR(V_DEPRECIATION))*/NULL;
1678 V_CLOSING_BALANCE := NVL(NVL(CLOSING.OPENING_WDV
1679 ,0) + NVL(CLOSING.OPENING_WDV_ADJ
1680 ,0)
1681 ,0) - NVL(V_DEPRECIATION
1682 ,0) - NVL(V_RETIRED1
1683 ,0);
1684 /*SRW.MESSAGE(1009
1685 ,'2.4 v_closing_balance -> ' || TO_CHAR(V_CLOSING_BALANCE))*/NULL;
1686 V_DEPRECIATION := ROUND(V_DEPRECIATION
1687 ,2);
1688 /*SRW.MESSAGE(1010
1689 ,'2.5 v_depreciation after rounding-> ' || TO_CHAR(V_DEPRECIATION))*/NULL;
1690 V_CLOSING_BALANCE := ROUND(V_CLOSING_BALANCE
1691 ,2);
1692 /*SRW.MESSAGE(1011
1693 ,'2.6 v_closing_balance after rounding-> ' || TO_CHAR(V_CLOSING_BALANCE) || ' value of v_depreciation after rounding is ' || V_DEPRECIATION)*/NULL;
1694 IF V_CLOSING_BALANCE > 0 THEN
1695 V_CLOSING_BALANCE := V_CLOSING_BALANCE;
1696 ELSE
1697 V_CLOSING_BALANCE := 0;
1698 END IF;
1699 V_COUNT2 := NULL;
1700 OPEN ASSET_COUNT_CUR1(CLOSING.BLOCK_ID);
1701 FETCH ASSET_COUNT_CUR1
1702 INTO V_COUNT2;
1703 CLOSE ASSET_COUNT_CUR1;
1704 /*SRW.MESSAGE(1275
1705 ,'2.7 v_count2 -> ' || V_COUNT2 || ', owa -> ' || NVL(CLOSING.OPENING_WDV_ADJ
1706 ,-1) || ', depna -> ' || NVL(CLOSING.DEPN_ADJ
1707 ,-1))*/NULL;
1708 IF CLOSING.OPENING_WDV_ADJ IS NOT NULL OR CLOSING.DEPN_ADJ IS NOT NULL THEN
1709 NULL;
1710 DEPRECIATION1(CLOSING.BLOCK_ID
1711 ,CLOSING.OPENING_WDV
1712 ,V_CLOSING_BALANCE
1713 ,CLOSING.OPENING_WDV_ADJ
1714 ,CLOSING.DEPN_ADJ
1715 ,P_YEAR_END);
1716 END IF;
1717 V_UNPLANNED_DEPN := NULL;
1718 OPEN UNPLANNED_DEPN_CUR(CLOSING.BLOCK_ID);
1719 FETCH UNPLANNED_DEPN_CUR
1720 INTO V_UNPLANNED_DEPN;
1721 CLOSE UNPLANNED_DEPN_CUR;
1722 /*SRW.MESSAGE(1275
1723 ,'2.9 v_UNPLANNED_DEPN -> ' || V_UNPLANNED_DEPN)*/NULL;
1724 IF NVL(V_COUNT2
1725 ,0) = 0 THEN
1726 UPDATE
1727 JAI_FA_AST_BLOCKS
1728 SET
1729 CLOSING_WDV = NVL(V_CLOSING_BALANCE
1730 ,0) - NVL(CLOSING.DEPN_ADJ
1731 ,0)
1732 ,YEAR_ENDED = P_YEAR_END
1733 WHERE BLOCK_ID = CLOSING.BLOCK_ID;
1734 SELECT
1735 JAI_FA_AST_BLOCKS_S.NEXTVAL
1736 INTO V_BLOCK_ID1
1737 FROM
1738 DUAL;
1739 /*SRW.MESSAGE(1012
1740 ,'2.10 closing.type -> ' || CLOSING.TYPE)*/NULL;
1741 INSERT INTO JAI_FA_AST_BLOCKS
1742 (BLOCK_ID
1743 ,TYPE
1744 ,RATE
1745 ,BOOK_TYPE_CODE
1746 ,OPENING_WDV
1747 ,START_DATE
1748 ,CREATION_DATE
1749 ,CREATED_BY
1750 ,LAST_UPDATE_DATE
1751 ,LAST_UPDATE_LOGIN
1752 ,LAST_UPDATED_BY)
1753 VALUES (V_BLOCK_ID1
1754 ,CLOSING.TYPE
1755 ,CLOSING.BLOCK_RATE
1756 ,P_BOOK_NAME
1757 ,NVL(V_CLOSING_BALANCE
1758 ,0) - NVL(CLOSING.DEPN_ADJ
1759 ,0)
1760 ,P_YEAR_END + 1
1761 ,SYSDATE
1762 ,UID
1763 ,SYSDATE
1764 ,UID
1765 ,UID);
1766 /*SRW.MESSAGE(1014
1767 ,'2.11 INSERT INTO JAI_FA_AST_BLOCKS')*/NULL;
1768 INSERT INTO JAI_FA_DEP_BLOCKS
1769 (BLOCK_DEPN_ID
1770 ,BLOCK_ID
1771 ,SLNO
1772 ,DEPN_OF_ASSETS
1773 ,YEAR_ENDED
1774 ,CREATION_DATE
1775 ,CREATED_BY
1776 ,LAST_UPDATE_DATE
1777 ,LAST_UPDATE_LOGIN
1778 ,LAST_UPDATED_BY
1779 ,UNPLANNED_DEPN)
1780 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
1781 ,CLOSING.BLOCK_ID
1782 ,0
1783 ,V_DEPRECIATION
1784 ,P_YEAR_END
1785 ,SYSDATE
1786 ,UID
1787 ,SYSDATE
1788 ,UID
1789 ,UID
1790 ,CLOSING.DEPN_ADJ);
1791 /*SRW.MESSAGE(1015
1792 ,'UPDATE INTO JAI_FA_DEP_BLOCKS')*/NULL;
1793 ELSE
1794 UPDATE
1795 JAI_FA_DEP_BLOCKS
1796 SET
1797 DEPN_OF_ASSETS = V_DEPRECIATION
1798 WHERE BLOCK_ID = CLOSING.BLOCK_ID
1799 AND SLNO = 0;
1800 /*SRW.MESSAGE(1016
1801 ,'2.12 update JAI_FA_DEP_BLOCKS ')*/NULL;
1802 UPDATE
1806 ,0) - NVL(V_UNPLANNED_DEPN
1803 JAI_FA_AST_BLOCKS
1804 SET
1805 OPENING_WDV = NVL(V_CLOSING_BALANCE
1807 ,0)
1808 WHERE START_DATE = P_YEAR_END + 1
1809 AND TYPE = CLOSING.TYPE
1810 AND BOOK_TYPE_CODE = P_BOOK_NAME;
1811 /*SRW.MESSAGE(1017
1812 ,'2.13 Update opening wdv JAI_FA_AST_BLOCKS -> ' || SQL%ROWCOUNT)*/NULL;
1813 UPDATE
1814 JAI_FA_AST_BLOCKS
1815 SET
1816 CLOSING_WDV = NVL(V_CLOSING_BALANCE
1817 ,0) - NVL(V_UNPLANNED_DEPN
1818 ,0)
1819 ,YEAR_ENDED = P_YEAR_END
1820 WHERE BLOCK_ID = CLOSING.BLOCK_ID;
1821 /*SRW.MESSAGE(1017
1822 ,'2.14 Update closing wdv JAI_FA_AST_BLOCKS -> ' || SQL%ROWCOUNT)*/NULL;
1823 IF CLOSING.DEPN_ADJ IS NOT NULL THEN
1824 /*SRW.MESSAGE(1018
1825 ,'2.15 IF closing.depn_adj IS NOT NULL THEN')*/NULL;
1826 UPDATE
1827 JAI_FA_DEP_BLOCKS
1828 SET
1829 UNPLANNED_DEPN = CLOSING.DEPN_ADJ
1830 WHERE BLOCK_ID = CLOSING.BLOCK_ID
1831 AND SLNO = 0;
1832 END IF;
1833 END IF;
1834 END LOOP;
1835 END LOOP;
1836 CLOSE ASSET_BLOCK_CUR;
1837 EXIT;
1838 /*SRW.MESSAGE(1018
1839 ,'2.16 Before EXIT statement')*/NULL;
1840 ELSE
1841 /*SRW.MESSAGE(1019
1842 ,'3.0 Assets found for block_id ' || V_BLOCK_ID)*/NULL;
1843 OPEN COUNT_DEPN_CUR(V_BLOCK_ID,V_SLNO,V_ASSET_ID);
1844 FETCH COUNT_DEPN_CUR
1845 INTO V_DEPN_SLNO;
1846 CLOSE COUNT_DEPN_CUR;
1847 /*SRW.MESSAGE(1019
1848 ,'3.1 entered v_depn_slno -> ' || V_DEPN_SLNO)*/NULL;
1849 LN_TRANSACTION_HEADER_ID_IN := GET_TRANSACTION_HEADER_ID(P_BOOK_NAME => P_BOOK_NAME
1850 ,P_ASSET_ID => V_ASSET_ID
1851 ,P_BLOCK_ID => V_BLOCK_ID);
1852 IF LN_TRANSACTION_HEADER_ID_IN IS NOT NULL THEN
1853 /*SRW.MESSAGE(1019
1854 ,'3.1.1 actual transaction_header_id found -> ' || LN_TRANSACTION_HEADER_ID_IN)*/NULL;
1855 OPEN COST_MORE_EXEMPT_UPTO_CUR(V_ASSET_ID,V_START_DATE,V_END_DATE,NVL(V_EXEMPT_UPTO
1856 ,0),V_BLOCK_ID,LN_TRANSACTION_HEADER_ID_IN);
1857 FETCH COST_MORE_EXEMPT_UPTO_CUR
1858 INTO EXEMPT;
1859 CLOSE COST_MORE_EXEMPT_UPTO_CUR;
1860 V_COST := EXEMPT.COSTING;
1861 /*SRW.MESSAGE(1019
1862 ,'3.1.2 v_cost is -> ' || V_COST)*/NULL;
1863 ELSE
1864 /*SRW.MESSAGE(1019
1865 ,'3.1.3 ACTUAL TRANSACTION_HEADER_ID NOT FOUND CLOSING BALANCE WOULD NOT GET POPULATED')*/NULL;
1866 V_COST := 0;
1867 END IF;
1868 LN_TRANSACTION_HEADER_ID_IN := NULL;
1869 /*SRW.MESSAGE(1020
1870 ,'3.2 cost_more_exempt_upto_cur, v_depn_slno -> ' || V_DEPN_SLNO || ', v_serial_num1 -> ' || V_SERIAL_NUM1)*/NULL;
1871 IF V_DEPN_SLNO = 0 THEN
1872 IF V_SERIAL_NUM1 IS NULL THEN
1873 V_SERIAL_NUM1 := V_SLNO;
1874 I := 1;
1875 V_DEPN_AMOUNT := 0;
1876 ELSIF V_SERIAL_NUM1 <> V_SLNO THEN
1877 I := 0;
1878 /*SRW.MESSAGE(1022
1879 ,'3.3 inserting INTO JAI_FA_DEP_BLOCKS, v_block_id -> ' || V_BLOCK_ID || ', v_depn_amount -> ' || NVL(V_DEPN_AMOUNT
1880 ,0))*/NULL;
1881 INSERT INTO JAI_FA_DEP_BLOCKS
1882 (BLOCK_DEPN_ID
1883 ,BLOCK_ID
1884 ,SLNO
1885 ,DEPN_OF_ASSETS
1886 ,YEAR_ENDED
1887 ,CREATION_DATE
1888 ,CREATED_BY
1889 ,LAST_UPDATE_DATE
1890 ,LAST_UPDATE_LOGIN
1891 ,LAST_UPDATED_BY
1892 ,UNPLANNED_DEPN)
1893 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
1894 ,V_BLOCK_ID
1895 ,V_SERIAL_NUM1
1896 ,NVL(V_DEPN_AMOUNT
1897 ,0)
1898 ,P_YEAR_END
1899 ,SYSDATE
1900 ,UID
1901 ,SYSDATE
1902 ,UID
1903 ,UID
1904 ,V_ASSET_ID);
1905 V_DEPN_AMOUNT := 0;
1906 V_FLAG := TRUE;
1907 V_SERIAL_NUM1 := V_SLNO;
1908 END IF;
1909 END IF;
1910 NULL;
1911 IF NVL(V_AQUISATION_DATE
1912 ,SYSDATE) >= NVL(V_START_DATE
1913 ,SYSDATE) AND NVL(V_AQUISATION_DATE
1914 ,SYSDATE) <= NVL(V_END_DATE
1915 ,SYSDATE) THEN
1916 IF V_PERIOD_RATE = 100 THEN
1917 V_MORE_AMOUNT1 := NVL(V_COST
1918 ,0) * NVL(V_BLOCK_RATE
1919 ,0) / 100;
1920 END IF;
1921 END IF;
1922 IF NVL(V_AQUISATION_DATE
1923 ,V_DATE_PLACED_IN_SERVICE) <= NVL(P_YEAR_START
1924 ,SYSDATE) THEN
1925 V_MORE_AMOUNT := NVL(V_COST
1926 ,0) * NVL(V_BLOCK_RATE
1927 ,0) / 100;
1928 END IF;
1929 IF NVL(V_AQUISATION_DATE
1930 ,SYSDATE) >= NVL(V_START_DATE
1931 ,SYSDATE) AND NVL(V_AQUISATION_DATE
1932 ,SYSDATE) <= NVL(V_END_DATE
1933 ,SYSDATE) THEN
1934 IF NVL(V_DATE_PLACED_IN_SERVICE
1935 ,SYSDATE) >= NVL(P_YEAR_START
1939 IF V_PERIOD_RATE <> 100 THEN
1936 ,SYSDATE) AND NVL(V_DATE_PLACED_IN_SERVICE
1937 ,SYSDATE) <= NVL(P_YEAR_END
1938 ,SYSDATE) THEN
1940 V_MORE_AMOUNT := NVL((NVL(V_COST
1941 ,0) * NVL(V_BLOCK_RATE
1942 ,0) / 100 * NVL(V_PERIOD_RATE
1943 ,0) / 100)
1944 ,0);
1945 END IF;
1946 END IF;
1947 END IF;
1948 IF V_AQUISATION_DATE IS NULL AND (NVL(V_DATE_PLACED_IN_SERVICE
1949 ,SYSDATE) >= NVL(V_START_DATE
1950 ,SYSDATE) AND NVL(V_DATE_PLACED_IN_SERVICE
1951 ,SYSDATE) <= NVL(V_END_DATE
1952 ,SYSDATE)) THEN
1953 /*SRW.MESSAGE(1006
1954 ,'3.5 v_aquisation_date IS NULL, v_period_rate -> ' || V_PERIOD_RATE)*/NULL;
1955 IF V_PERIOD_RATE <> 100 THEN
1956 V_MORE_AMOUNT := NVL((NVL(V_COST
1957 ,0) * NVL(V_BLOCK_RATE
1958 ,0) / 100 * NVL(V_PERIOD_RATE
1959 ,0) / 100)
1960 ,0);
1961 END IF;
1962 END IF;
1963 IF NVL(V_AQUISATION_DATE
1964 ,V_DATE_PLACED_IN_SERVICE) >= NVL(V_START_DATE
1965 ,SYSDATE) AND NVL(V_AQUISATION_DATE
1966 ,V_DATE_PLACED_IN_SERVICE) <= NVL(V_END_DATE
1967 ,SYSDATE) THEN
1968 /*SRW.MESSAGE(1006
1969 ,'3.6 v_period_rate -> ' || V_PERIOD_RATE)*/NULL;
1970 IF V_PERIOD_RATE = 0 THEN
1971 V_MORE_AMOUNT := 0;
1972 END IF;
1973 END IF;
1974 IF EXEMPT.ASSET_ID = V_ASSET_ID THEN
1975 /*SRW.MESSAGE(1275
1976 ,'3.7 v_period_rate -> ' || V_PERIOD_RATE || ', v_asset_id -> ' || V_ASSET_ID)*/NULL;
1977 IF V_PERIOD_RATE = 100 THEN
1978 V_MORE_AMOUNT := NVL(V_MORE_AMOUNT1
1979 ,0);
1980 ELSE
1981 V_MORE_AMOUNT := NVL(V_MORE_AMOUNT
1982 ,0);
1983 END IF;
1984 END IF;
1985 V_DEPN_AMOUNT := V_DEPN_AMOUNT + V_MORE_AMOUNT;
1986 V_CLOSING_BALANCE := NVL(V_COST
1987 ,0) - NVL(V_MORE_AMOUNT
1988 ,0);
1989 /*SRW.MESSAGE(1275
1990 ,'3.8 v_depn_amount -> ' || V_DEPN_AMOUNT || ', v_closing_balance -> ' || V_CLOSING_BALANCE || ', v_cost -> ' || V_COST || ', v_more_amount -> ' || V_MORE_AMOUNT)*/NULL;
1991 /*SRW.MESSAGE(1275
1992 ,'3.81 Before insert into JAI_FA_DEP_BLOCKS_T block_id ' || V_BLOCK_ID || ' , ' || 'slno' || ROUND(NVL(V_COST
1993 ,0)
1994 ,2) || ' , ' || 'depn_of_assets ' || ROUND(NVL(V_MORE_AMOUNT
1995 ,0)
1996 ,2) || ' , ' || 'year_ended ' || P_YEAR_END || ' , ' || 'full_exempt ' || NVL(V_MORE_AMOUNT1
1997 ,0) || ' , ' || 'total_balance ' || ROUND(NVL(V_CLOSING_BALANCE
1998 ,0)
1999 ,2) || ' , ' || 'unplanned_depn ' || V_SLNO || ' , ' || 'asset_id ' || V_ASSET_ID)*/NULL;
2000 INSERT INTO JAI_FA_DEP_BLOCKS_T
2001 (BLOCK_ID
2002 ,SLNO
2003 ,DEPN_OF_ASSETS
2004 ,YEAR_ENDED
2005 ,FULL_EXEMPT
2006 ,CREATION_DATE
2007 ,CREATED_BY
2008 ,LAST_UPDATE_DATE
2009 ,LAST_UPDATE_LOGIN
2010 ,LAST_UPDATED_BY
2011 ,TOTAL_BALANCE
2012 ,UNPLANNED_DEPN
2013 ,ASSET_ID)
2014 VALUES (V_BLOCK_ID
2015 ,ROUND(NVL(V_COST
2016 ,0)
2017 ,2)
2018 ,ROUND(NVL(V_MORE_AMOUNT
2019 ,0)
2020 ,2)
2021 ,P_YEAR_END
2022 ,NVL(V_MORE_AMOUNT1
2023 ,0)
2024 ,SYSDATE
2025 ,UID
2026 ,SYSDATE
2027 ,UID
2028 ,UID
2029 ,ROUND(NVL(V_CLOSING_BALANCE
2030 ,0)
2031 ,2)
2032 ,V_SLNO
2033 ,V_ASSET_ID);
2034 /*SRW.MESSAGE(1275
2035 ,'3.82 After insert into JAI_FA_DEP_BLOCKS_T')*/NULL;
2036 /*SRW.MESSAGE(1275
2037 ,'3.9 v_depn_slno -> ' || V_DEPN_SLNO)*/NULL;
2038 IF V_DEPN_SLNO = 0 THEN
2039 IF V_FLAG OR I = 1 THEN
2040 /*SRW.MESSAGE(1275
2041 ,'3.10 v_serial_num1 -> ' || V_SERIAL_NUM1)*/NULL;
2042 IF V_SERIAL_NUM1 = V_SLNO THEN
2043 /*SRW.MESSAGE(1275
2044 ,'3.11 v_depn_amount -> ' || V_DEPN_AMOUNT)*/NULL;
2045 INSERT INTO JAI_FA_DEP_BLOCKS
2046 (BLOCK_DEPN_ID
2047 ,BLOCK_ID
2048 ,SLNO
2049 ,DEPN_OF_ASSETS
2050 ,YEAR_ENDED
2051 ,CREATION_DATE
2052 ,CREATED_BY
2053 ,LAST_UPDATE_DATE
2054 ,LAST_UPDATE_LOGIN
2055 ,LAST_UPDATED_BY
2056 ,UNPLANNED_DEPN)
2057 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
2058 ,V_BLOCK_ID
2059 ,V_SERIAL_NUM1
2060 ,NVL(V_DEPN_AMOUNT
2061 ,0)
2062 ,P_YEAR_END
2063 ,SYSDATE
2064 ,UID
2065 ,SYSDATE
2066 ,UID
2067 ,UID
2068 ,V_ASSET_ID);
2069 V_SERIAL_NUM1 := NULL;
2070 V_DEPN_AMOUNT := 0;
2071 END IF;
2075 /*SRW.MESSAGE(1275
2072 END IF;
2073 I := 0;
2074 END IF;
2076 ,'4.1 before cost_less_exempt_upto_cur')*/NULL;
2077 LN_TRANSACTION_HEADER_ID_IN := NULL;
2078 LN_TRANSACTION_HEADER_ID_IN := GET_TRANSACTION_HEADER_ID(P_BOOK_NAME => P_BOOK_NAME
2079 ,P_ASSET_ID => V_ASSET_ID
2080 ,P_BLOCK_ID => V_BLOCK_ID);
2081 /*SRW.MESSAGE(1019
2082 ,'4.1 actual transaction_header_id is -> ' || LN_TRANSACTION_HEADER_ID_IN)*/NULL;
2083 FOR less_exempt IN COST_LESS_EXEMPT_UPTO_CUR( v_asset_id ,
2084 v_start_date ,
2085 v_end_date ,
2086 nvl(v_exempt_upto,0) ,
2087 v_block_id ,
2088 ln_transaction_header_id_in
2089 ) LOOP
2090 V_COST_LESS := LESS_EXEMPT.COSTING;
2091 INSERT INTO JAI_FA_EXEMPTIONS
2092 (BLOCK_ID
2093 ,EXEMPT_AMOUNT
2094 ,FA_EXEMPTION_ID
2095 ,CREATED_BY
2096 ,CREATION_DATE
2097 ,LAST_UPDATED_BY
2098 ,LAST_UPDATE_DATE
2099 ,LAST_UPDATE_LOGIN
2100 ,OBJECT_VERSION_NUMBER)
2101 VALUES (V_BLOCK_ID
2102 ,ROUND(NVL(V_COST_LESS
2103 ,0)
2104 ,2)
2105 ,JAI_FA_EXEMPTIONS_S.NEXTVAL
2106 ,FND_GLOBAL.USER_ID
2107 ,SYSDATE
2108 ,FND_GLOBAL.USER_ID
2109 ,SYSDATE
2110 ,FND_GLOBAL.LOGIN_ID
2111 ,NULL);
2112 /*SRW.MESSAGE(1275
2113 ,'4.2 Inserted into JAI_FA_EXEMPTIONS, v_cost_less -> ' || V_COST_LESS || ', v_depn_slno -> ' || V_DEPN_SLNO)*/NULL;
2114 IF V_DEPN_SLNO = 0 THEN
2115 /*SRW.MESSAGE(1275
2116 ,'4.3 Inserted into JAI_FA_DEP_BLOCKS, V_block_id -> ' || V_BLOCK_ID || ', v_cost_less -> ' || NVL(V_COST_LESS
2117 ,0) || ', v_slno -> ' || V_SLNO)*/NULL;
2118 INSERT INTO JAI_FA_DEP_BLOCKS
2119 (BLOCK_DEPN_ID
2120 ,BLOCK_ID
2121 ,SLNO
2122 ,DEPN_OF_ASSETS
2123 ,FULL_EXEMPT
2124 ,YEAR_ENDED
2125 ,CREATION_DATE
2126 ,CREATED_BY
2127 ,LAST_UPDATE_DATE
2128 ,LAST_UPDATE_LOGIN
2129 ,LAST_UPDATED_BY
2130 ,UNPLANNED_DEPN)
2131 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
2132 ,V_BLOCK_ID
2133 ,V_SLNO
2134 ,NVL(V_COST_LESS
2135 ,0)
2136 ,NVL(V_COST_LESS
2137 ,0)
2138 ,P_YEAR_END
2139 ,SYSDATE
2140 ,UID
2141 ,SYSDATE
2142 ,UID
2143 ,UID
2144 ,V_ASSET_ID);
2145 END IF;
2146 END LOOP;
2147 LN_TRANSACTION_HEADER_ID_IN := NULL;
2148 IF ASSET_BLOCK_CUR%FOUND IS NULL THEN
2149 CLOSE ASSET_BLOCK_CUR;
2150 EXIT;
2151 END IF;
2152 END IF;
2153 END LOOP;
2154 /*SRW.MESSAGE(1275
2155 ,'5.1 Start of processing tempoprary table JAI_FA_DEP_BLOCKS_T cursor temp_tab')*/NULL;
2156 FOR temp_rec IN TEMP_TAB LOOP
2157 V_CLOSING := 0;
2158 V_CLOSING1 := 0;
2159 V_CLOSING_BALANCE := 0;
2160 /*SRW.MESSAGE(1275
2161 ,'5.2 V_block_id -> ' || TEMP_REC.BLOCK_ID || ', V_ASSET_ID -> ' || V_ASSET_ID)*/NULL;
2162 OPEN COUNTER_CUR(TEMP_REC.BLOCK_ID);
2163 FETCH COUNTER_CUR
2164 INTO V_COUNTER;
2165 CLOSE COUNTER_CUR;
2166 OPEN LEFT_AMOUNT(TEMP_REC.BLOCK_ID);
2167 FETCH LEFT_AMOUNT
2168 INTO V_LEFT_AMOUNT,V_LEFT_BLOCK_ID;
2169 CLOSE LEFT_AMOUNT;
2170 OPEN ASSET_ID_CURSOR(TEMP_REC.BLOCK_ID);
2171 FETCH ASSET_ID_CURSOR
2172 INTO V_ASSET_ID;
2173 CLOSE ASSET_ID_CURSOR;
2174 OPEN ASSET_COUNT_CUR(V_ASSET_ID,TEMP_REC.BLOCK_ID);
2175 FETCH ASSET_COUNT_CUR
2176 INTO V_ASSET_COUNT,V_BLOCK_ID1;
2177 CLOSE ASSET_COUNT_CUR;
2178 OPEN RETIRE_ASSET_CUR(V_ASSET_ID,TEMP_REC.BLOCK_ID);
2179 FETCH RETIRE_ASSET_CUR
2180 INTO V_RETIRE_COUNT,V_BLOCK_ID2;
2181 CLOSE RETIRE_ASSET_CUR;
2182 OPEN UNPLANNED_DEPN_CUR(TEMP_REC.BLOCK_ID);
2183 V_UNPLANNED_DEPN := NULL;
2184 FETCH UNPLANNED_DEPN_CUR
2185 INTO V_UNPLANNED_DEPN;
2186 CLOSE UNPLANNED_DEPN_CUR;
2187 NULL;
2188 IF V_BLOCK_ID1 = V_BLOCK_ID2 THEN
2189 V_ORIGINAL_COUNT := NVL(V_ASSET_COUNT
2190 ,0) - NVL(V_RETIRE_COUNT
2191 ,0);
2192 ELSE
2193 V_ORIGINAL_COUNT := NULL;
2194 END IF;
2195 IF TEMP_REC.BLOCK_ID = V_LEFT_BLOCK_ID THEN
2196 V_LEFT_AMOUNT := NVL(V_LEFT_AMOUNT
2197 ,0);
2198 ELSE
2199 V_LEFT_AMOUNT := 0;
2200 END IF;
2201 OPEN COUNT_CUR(TEMP_REC.BLOCK_ID);
2202 FETCH COUNT_CUR
2203 INTO V_COUNT1;
2204 CLOSE COUNT_CUR;
2205 OPEN TYPE_CUR(TEMP_REC.BLOCK_ID);
2206 FETCH TYPE_CUR
2207 INTO V_TYPE,V_BLOCK_RATE,V_OPENING_WDV1,V_OPENING_WDV_ADJ,V_DEPN_ADJ;
2208 CLOSE TYPE_CUR;
2209 OPEN FA_RETIREMENTS_CUR(TEMP_REC.BLOCK_ID);
2210 FETCH FA_RETIREMENTS_CUR
2214 OPEN BLOCK_RET_CUR(V_TYPE);
2211 INTO V_PROCEEDS_OF_SALE;
2212 CLOSE FA_RETIREMENTS_CUR;
2213 V_RETIRED := NULL;
2215 FETCH BLOCK_RET_CUR
2216 INTO V_RETIRED;
2217 CLOSE BLOCK_RET_CUR;
2218 V_RETIRED1 := NULL;
2219 OPEN RET_CAL(V_TYPE);
2220 FETCH RET_CAL
2221 INTO V_RETIRED1;
2222 CLOSE RET_CAL;
2223 NULL;
2224 V_DEPR_VAL := TOTALDEPRN(P_BOOK_NAME
2225 ,P_YEAR_START
2226 ,P_YEAR_END
2227 ,TEMP_REC.BLOCK_ID
2228 ,V_NO_ASSETS);
2229 V_AMOUNT1 := NVL(NVL(V_OPENING_WDV1
2230 ,0) + NVL(V_OPENING_WDV_ADJ
2231 ,0) - NVL(V_RETIRED1
2232 ,0)
2233 ,0) * NVL(V_BLOCK_RATE
2234 ,0) / 100;
2235 V_CLOSING := NVL(V_OPENING_WDV1
2236 ,0) + NVL(TEMP_REC.COST
2237 ,0) - NVL(V_RETIRED
2238 ,0);
2239 V_CLOSING1 := NVL(V_OPENING_WDV1
2240 ,0) + NVL(TEMP_REC.COST_FULL
2241 ,0) - NVL(V_RETIRED
2242 ,0);
2243 V_CLOSING_BALANCE := NVL(V_OPENING_WDV1
2244 ,0) + NVL(V_OPENING_WDV_ADJ
2245 ,0) + NVL(TEMP_REC.COST
2246 ,0) + NVL(V_LEFT_AMOUNT
2247 ,0) - NVL(V_LEFT_AMOUNT
2248 ,0) - NVL(V_RETIRED
2249 ,0) - NVL(V_DEPN_ADJ
2250 ,0) - NVL(V_UNPLANNED_DEPN
2251 ,0) - V_DEPR_VAL;
2252 IF NVL(V_DEPR_VAL
2253 ,0) <= 0 AND V_NO_ASSETS = 'YES' THEN
2254 V_CLOSING_BALANCE := 0;
2255 END IF;
2256 NULL;
2257 IF V_OPENING_WDV_ADJ IS NOT NULL OR V_DEPN_ADJ IS NOT NULL THEN
2258 NULL;
2259 DEPRECIATION1(TEMP_REC.BLOCK_ID
2260 ,V_OPENING_WDV1
2261 ,V_CLOSING_BALANCE
2262 ,V_OPENING_WDV_ADJ
2263 ,V_DEPN_ADJ
2264 ,P_YEAR_END);
2265 END IF;
2266 IF V_AMOUNT1 > 0 THEN
2267 V_AMOUNT1 := V_AMOUNT1;
2268 ELSE
2269 V_AMOUNT1 := 0;
2270 END IF;
2271 DECLARE
2272 CURSOR CUR_GET_VALID_ASSETS(CP_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) IS
2273 SELECT
2274 '1'
2275 FROM
2276 JAI_FA_AST_BLOCK_DTLS JBA,
2277 FA_BOOKS FAB,
2278 JAI_FA_AST_BLOCKS JABOA
2279 WHERE JBA.ASSET_ID = FAB.ASSET_ID
2280 AND ( ( JBA.ASSET_TYPE = 'CAPITALIZED'
2281 AND FAB.CAPITALIZE_FLAG = 'YES' )
2282 OR JBA.ASSET_TYPE = 'EXPENSED' )
2283 AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
2284 AND NVL(JABOA.YEAR_ENDED
2285 ,P_YEAR_END)
2286 AND FAB.DATE_INEFFECTIVE IS NULL
2287 AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
2288 AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
2289 AND JBA.BLOCK_ID = JABOA.BLOCK_ID
2290 AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
2291 AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
2292 AND JABOA.TYPE = (
2293 SELECT
2294 TYPE
2295 FROM
2296 JAI_FA_AST_BLOCKS
2297 WHERE BLOCK_ID = CP_BLOCK_ID )
2298 AND ( JABOA.START_DATE <= NVL(P_YEAR_START
2299 ,JABOA.START_DATE)
2300 OR JABOA.START_DATE BETWEEN NVL(P_YEAR_START
2301 ,JABOA.START_DATE)
2302 AND NVL(P_YEAR_END
2303 ,JABOA.YEAR_ENDED) );
2304 CURSOR CUR_GET_ST_OP_BAL(CP_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) IS
2305 SELECT
2306 OPENING_WDV
2307 FROM
2308 JAI_FA_AST_BLOCKS JABOA
2309 WHERE JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
2310 AND JABOA.TYPE = (
2311 SELECT
2312 TYPE
2313 FROM
2314 JAI_FA_AST_BLOCKS
2315 WHERE BLOCK_ID = CP_BLOCK_ID )
2316 ORDER BY
2317 START_DATE ASC;
2318 LV_EXISTS VARCHAR2(1);
2319 LN_OPENING_WDV JAI_FA_AST_BLOCKS.OPENING_WDV%TYPE;
2320 BEGIN
2321 OPEN CUR_GET_VALID_ASSETS(CP_BLOCK_ID => TEMP_REC.BLOCK_ID);
2322 FETCH CUR_GET_VALID_ASSETS
2323 INTO LV_EXISTS;
2324 /*SRW.MESSAGE(1275
2325 ,'p_book_name -> ' || P_BOOK_NAME || ',p_year_start -> ' || P_YEAR_START || ',p_year_end -> ' || P_YEAR_END)*/NULL;
2326 IF CUR_GET_VALID_ASSETS%NOTFOUND THEN
2327 /*SRW.MESSAGE(1275
2328 ,'5.6.1 all assets retired , temp_rec.block_id -> ' || TEMP_REC.BLOCK_ID)*/NULL;
2329 OPEN CUR_GET_ST_OP_BAL(CP_BLOCK_ID => TEMP_REC.BLOCK_ID);
2330 FETCH CUR_GET_ST_OP_BAL
2331 INTO LN_OPENING_WDV;
2332 CLOSE CUR_GET_ST_OP_BAL;
2333 /*SRW.MESSAGE(1275
2334 ,'5.6.2 value of opening wdv ' || LN_OPENING_WDV)*/NULL;
2335 IF LN_OPENING_WDV = 0 THEN
2336 /*SRW.MESSAGE(1275
2337 ,'5.6.3 opening wdv for the first period record for the type and book name is 0')*/NULL;
2338 V_CLOSING_BALANCE := 0;
2339 V_AMOUNT1 := 0;
2340 END IF;
2341 END IF;
2342 /*SRW.MESSAGE(1275
2343 ,'5.6.4 v_closing_balance ' || V_CLOSING_BALANCE || ' v_amount1 ' || V_AMOUNT1)*/NULL;
2344 CLOSE CUR_GET_VALID_ASSETS;
2345 END;
2346 IF V_CLOSING > 0 THEN
2347 IF NVL(V_COUNT1
2351 SET
2348 ,0) = 0 THEN
2349 UPDATE
2350 JAI_FA_AST_BLOCKS
2352 CLOSING_WDV = NVL(V_CLOSING_BALANCE
2353 ,0)
2354 ,YEAR_ENDED = P_YEAR_END
2355 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2356 /*SRW.MESSAGE(1275
2357 ,'5.7 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2358 INSERT INTO JAI_FA_DEP_BLOCKS
2359 (BLOCK_DEPN_ID
2360 ,BLOCK_ID
2361 ,SLNO
2362 ,DEPN_OF_ASSETS
2363 ,YEAR_ENDED
2364 ,CREATION_DATE
2365 ,CREATED_BY
2366 ,LAST_UPDATE_DATE
2367 ,LAST_UPDATE_LOGIN
2368 ,LAST_UPDATED_BY
2369 ,UNPLANNED_DEPN)
2370 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
2371 ,TEMP_REC.BLOCK_ID
2372 ,0
2373 ,NVL(V_AMOUNT1
2374 ,0)
2375 ,P_YEAR_END
2376 ,SYSDATE
2377 ,UID
2378 ,SYSDATE
2379 ,UID
2380 ,UID
2381 ,V_DEPN_ADJ);
2382 SELECT
2383 JAI_FA_AST_BLOCKS_S.NEXTVAL
2384 INTO V_BLOCK_ID
2385 FROM
2386 DUAL;
2387 NULL;
2388 INSERT INTO JAI_FA_AST_BLOCKS
2389 (BLOCK_ID
2390 ,TYPE
2391 ,RATE
2392 ,OPENING_WDV
2393 ,START_DATE
2394 ,BOOK_TYPE_CODE
2395 ,CREATION_DATE
2396 ,CREATED_BY
2397 ,LAST_UPDATE_DATE
2398 ,LAST_UPDATE_LOGIN
2399 ,LAST_UPDATED_BY)
2400 VALUES (V_BLOCK_ID
2401 ,V_TYPE
2402 ,V_BLOCK_RATE
2403 ,NVL(V_CLOSING_BALANCE
2404 ,0)
2405 ,P_YEAR_END + 1
2406 ,P_BOOK_NAME
2407 ,SYSDATE
2408 ,UID
2409 ,SYSDATE
2410 ,UID
2411 ,UID);
2412 ELSE
2413 UPDATE
2414 JAI_FA_AST_BLOCKS
2415 SET
2416 CLOSING_WDV = NVL(V_CLOSING_BALANCE
2417 ,0)
2418 ,YEAR_ENDED = P_YEAR_END
2419 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2420 /*SRW.MESSAGE(1275
2421 ,'5.9 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2422 UPDATE
2423 JAI_FA_DEP_BLOCKS
2424 SET
2425 DEPN_OF_ASSETS = NVL(V_AMOUNT1
2426 ,0)
2427 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
2428 AND SLNO = 0;
2429 /*SRW.MESSAGE(1275
2430 ,'5.10 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2431 UPDATE
2432 JAI_FA_AST_BLOCKS
2433 SET
2434 OPENING_WDV = NVL(V_CLOSING_BALANCE
2435 ,0)
2436 WHERE START_DATE = P_YEAR_END + 1
2437 AND TYPE = V_TYPE
2438 AND BOOK_TYPE_CODE = P_BOOK_NAME;
2439 K := 1;
2440 /*SRW.MESSAGE(1275
2441 ,'5.11 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2442 IF V_DEPN_ADJ IS NOT NULL THEN
2443 UPDATE
2444 JAI_FA_DEP_BLOCKS
2445 SET
2446 UNPLANNED_DEPN = V_DEPN_ADJ
2447 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
2448 AND SLNO = 0;
2449 /*SRW.MESSAGE(1275
2450 ,'5.12 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2451 END IF;
2452 END IF;
2453 END IF;
2454 IF V_CLOSING < 0 THEN
2455 UPDATE
2456 JAI_FA_AST_BLOCKS
2457 SET
2458 CLOSING_WDV = 0
2459 ,YEAR_ENDED = P_YEAR_END
2460 ,CAPITAL_GAINS = ABS(V_CLOSING)
2461 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2462 /*SRW.MESSAGE(1275
2463 ,'6.1 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2464 IF NVL(V_COUNTER
2465 ,0) = 0 THEN
2466 /*SRW.MESSAGE(1275
2467 ,'6.2 insert into JAI_FA_AST_BLOCKS, temp_rec.block_id -> ' || TEMP_REC.BLOCK_ID)*/NULL;
2468 INSERT INTO JAI_FA_DEP_BLOCKS
2469 (BLOCK_DEPN_ID
2470 ,BLOCK_ID
2471 ,SLNO
2472 ,DEPN_OF_ASSETS
2473 ,YEAR_ENDED
2474 ,CREATION_DATE
2475 ,CREATED_BY
2476 ,LAST_UPDATE_DATE
2477 ,LAST_UPDATE_LOGIN
2478 ,LAST_UPDATED_BY
2479 ,UNPLANNED_DEPN)
2480 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
2481 ,TEMP_REC.BLOCK_ID
2482 ,0
2483 ,0
2484 ,P_YEAR_END
2485 ,SYSDATE
2486 ,UID
2487 ,SYSDATE
2488 ,UID
2489 ,UID
2490 ,V_DEPN_ADJ);
2491 ELSE
2492 UPDATE
2493 JAI_FA_DEP_BLOCKS
2494 SET
2495 DEPN_OF_ASSETS = 0
2496 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2497 /*SRW.MESSAGE(1275
2498 ,'6.3 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2499 END IF;
2500 UPDATE
2501 JAI_FA_AST_BLOCKS
2502 SET
2503 OPENING_WDV = 0
2504 WHERE START_DATE = P_YEAR_END + 1
2505 AND BOOK_TYPE_CODE = P_BOOK_NAME
2506 AND TYPE = V_TYPE;
2507 /*SRW.MESSAGE(1275
2511 JAI_FA_AST_BLOCKS
2508 ,'6.4 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2509 ELSIF V_CLOSING = 0 THEN
2510 UPDATE
2512 SET
2513 CLOSING_WDV = 0
2514 ,YEAR_ENDED = P_YEAR_END
2515 ,CAPITAL_GAINS = NULL
2516 ,CAPITAL_LOSS = NULL
2517 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2518 /*SRW.MESSAGE(1275
2519 ,'6.5 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2520 IF NVL(V_COUNTER
2521 ,0) = 0 THEN
2522 INSERT INTO JAI_FA_DEP_BLOCKS
2523 (BLOCK_DEPN_ID
2524 ,BLOCK_ID
2525 ,SLNO
2526 ,DEPN_OF_ASSETS
2527 ,YEAR_ENDED
2528 ,CREATION_DATE
2529 ,CREATED_BY
2530 ,LAST_UPDATE_DATE
2531 ,LAST_UPDATE_LOGIN
2532 ,LAST_UPDATED_BY
2533 ,UNPLANNED_DEPN)
2534 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
2535 ,TEMP_REC.BLOCK_ID
2536 ,0
2537 ,0
2538 ,P_YEAR_END
2539 ,SYSDATE
2540 ,UID
2541 ,SYSDATE
2542 ,UID
2543 ,UID
2544 ,V_DEPN_ADJ);
2545 /*SRW.MESSAGE(1275
2546 ,'6.6 After insert into JAI_FA_DEP_BLOCKS')*/NULL;
2547 ELSE
2548 UPDATE
2549 JAI_FA_DEP_BLOCKS
2550 SET
2551 DEPN_OF_ASSETS = 0
2552 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2553 /*SRW.MESSAGE(1275
2554 ,'6.7 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2555 END IF;
2556 UPDATE
2557 JAI_FA_AST_BLOCKS
2558 SET
2559 OPENING_WDV = 0
2560 WHERE START_DATE = P_YEAR_END + 1
2561 AND BOOK_TYPE_CODE = P_BOOK_NAME
2562 AND TYPE = V_TYPE;
2563 /*SRW.MESSAGE(1275
2564 ,'6.8 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2565 END IF;
2566 IF V_CLOSING > 0 AND V_ORIGINAL_COUNT = 0 THEN
2567 UPDATE
2568 JAI_FA_AST_BLOCKS
2569 SET
2570 CLOSING_WDV = 0
2571 ,YEAR_ENDED = P_YEAR_END
2572 ,CAPITAL_GAINS = NULL
2573 ,CAPITAL_LOSS = V_CLOSING
2574 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2575 /*SRW.MESSAGE(1275
2576 ,'7.1 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2577 UPDATE
2578 JAI_FA_DEP_BLOCKS
2579 SET
2580 DEPN_OF_ASSETS = 0
2581 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
2582 /*SRW.MESSAGE(1275
2583 ,'7.2 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2584 UPDATE
2585 JAI_FA_AST_BLOCKS
2586 SET
2587 OPENING_WDV = 0
2588 WHERE START_DATE = P_YEAR_END + 1
2589 AND BOOK_TYPE_CODE = P_BOOK_NAME
2590 AND TYPE = V_TYPE;
2591 /*SRW.MESSAGE(1275
2592 ,'7.3 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2593 END IF;
2594 K := 0;
2595 C_BLOCK_ID := TEMP_REC.BLOCKID;
2596 END LOOP;
2597 UPDATE
2598 JAI_FA_AST_BLOCKS
2599 SET
2600 OPENING_WDV_ADJ = NULL
2601 ,DEPN_ADJ = NULL
2602 WHERE START_DATE BETWEEN P_YEAR_START
2603 AND P_YEAR_END
2604 AND OPENING_WDV_ADJ IS NOT NULL
2605 OR DEPN_ADJ IS NOT NULL
2606 AND BOOK_TYPE_CODE = P_BOOK_NAME;
2607 /*SRW.MESSAGE(1275
2608 ,'8.1 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2609 DELETE FROM JAI_FA_EXEMPTIONS;
2610 /*SRW.MESSAGE(1275
2611 ,'8.2 before commit and after delete of JAI_FA_EXEMPTIONS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
2612 COMMIT;
2613 /*SRW.MESSAGE(1275
2614 ,'Committed the records....RUN_DEP')*/NULL;
2615 EXCEPTION
2616 WHEN OTHERS THEN
2617 /*SRW.MESSAGE(1050
2618 ,'Last OTHERS ' || SQLERRM)*/NULL;
2619 ROLLBACK;
2620 END RUN_DEP;
2621
2622 PROCEDURE RUN_DEP_MASS(P_YEAR_START IN DATE
2623 ,P_YEAR_END IN DATE
2624 ,P_BOOK_NAME IN VARCHAR2) IS
2625 LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
2626 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
2627 LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
2628 K NUMBER := 0;
2629 I NUMBER := 0;
2630 V_COUNT1 NUMBER;
2631 V_COUNTER NUMBER;
2632 V_ASSET_ID NUMBER;
2633 V_OWNED_LEASED VARCHAR2(15);
2634 V_COST NUMBER;
2635 V_SLNO NUMBER;
2636 V_START_DATE DATE;
2637 V_END_DATE DATE;
2638 V_RATE NUMBER;
2639 V_OPENING_BALANCE NUMBER;
2640 V_BLOCK_ID NUMBER;
2641 T_BLOCK_ID NUMBER;
2642 C_BLOCK_ID NUMBER;
2643 V_PROCEEDS_OF_SALE NUMBER;
2644 V_AMOUNT NUMBER;
2645 V_BLOCK_RATE NUMBER;
2646 V_EXEMPT_UPTO NUMBER;
2647 V_COST_LESS NUMBER;
2648 V_AMOUNT1 NUMBER;
2649 V_CLOSING NUMBER;
2650 V_MORE_AMOUNT NUMBER;
2651 V_MORE_AMOUNT1 NUMBER;
2652 V_LESS_AMOUNT NUMBER;
2653 V_TYPE VARCHAR2(40);
2654 V_LEFT_AMOUNT NUMBER;
2655 V_LEFT_BLOCK_ID NUMBER;
2656 V_NEG_DEP NUMBER;
2657 V_ASSET_COUNT NUMBER;
2658 V_RETIRE_COUNT1 NUMBER;
2659 V_ORIGINAL_COUNT NUMBER;
2660 V_OPENING_WDV NUMBER;
2661 V_PERIOD_RATE NUMBER;
2662 V_BLOCK_ID1 NUMBER;
2663 V_DEPRECIATION NUMBER;
2664 V_CLOSING_BALANCE NUMBER;
2665 V_OPENING_WDV1 NUMBER;
2666 V_COUNT NUMBER;
2670 V_RETIRE_COUNT NUMBER;
2667 V_AQUISATION_DATE DATE;
2668 V_COUNT2 NUMBER;
2669 V_DATE_PLACED_IN_SERVICE DATE;
2671 V_BLOCK_ID2 NUMBER;
2672 V_CURRENT_UNITS NUMBER;
2673 V_CLOSING1 NUMBER;
2674 V_DEPRECIATION_COST NUMBER;
2675 V_DEPRECIATION_COST1 NUMBER;
2676 V_OLD_CLOSING_WDV NUMBER;
2677 V_OPENING_WDV_ADJ NUMBER;
2678 V_BLOCK_HISTORY_ID NUMBER;
2679 V_DEPN_ADJ NUMBER;
2680 V_YEAR_END DATE;
2681 V_PREV_CLOSING_BALANCE NUMBER;
2682 V_DEPN_SLNO NUMBER;
2683 V_SERIAL_NUM1 NUMBER;
2684 V_DEPN_AMOUNT NUMBER := 0;
2685 V_FLAG BOOLEAN := FALSE;
2686 V_CLOSING_BLOCK_ID NUMBER;
2687 V_UNPLANNED_DEPN NUMBER;
2688 V_RETIRED NUMBER;
2689 CURSOR ASSET_BLOCK_CUR IS
2690 SELECT
2691 DISTINCT
2692 A.MASS_ADDITION_ID,
2693 B.BLOCK_ID,
2694 B.OPENING_WDV,
2695 B.RATE BLOCK_RATE,
2696 C.SLNO,
2697 C.START_DATE,
2698 C.END_DATE,
2699 C.RATE PERIOD_RATE,
2700 C.EXEMPT_UPTO,
2701 NVL(TO_DATE(A.ATTRIBUTE1
2702 ,'DD-MON-RR')
2703 ,B.START_DATE) AQUISATION_DATE,
2704 D.DATE_PLACED_IN_SERVICE,
2705 A.FIXED_ASSETS_UNITS
2706 FROM
2707 FA_MASS_ADDITIONS A,
2708 JAI_FA_AST_BLOCKS B,
2709 JAI_FA_AST_PERIOD_RATES C,
2710 FA_BOOKS D
2711 WHERE NVL(A.MASS_ADDITION_ID
2712 ,0) = NVL(D.ASSET_ID
2713 ,0)
2714 AND TO_DATE(A.ATTRIBUTE1
2715 ,'DD-MON-RR') between C.START_DATE
2716 AND C.END_DATE
2717 AND B.START_DATE >= P_YEAR_START
2718 AND B.START_DATE <= P_YEAR_END
2719 AND C.YEAR_START = P_YEAR_START
2720 AND C.YEAR_END = P_YEAR_END
2721 AND A.ATTRIBUTE2 = TO_CHAR(B.BLOCK_ID)
2722 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
2723 AND D.DATE_INEFFECTIVE is null
2724 AND D.TRANSACTION_HEADER_ID_OUT is null
2725 AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
2726 AND D.CAPITALIZE_FLAG = LV_FLAG )
2727 OR A.ASSET_TYPE = LV_EXPENSED )
2728 ORDER BY
2729 1;
2730 CURSOR COUNT_CUR(P_BLOCK_ID IN NUMBER) IS
2731 SELECT
2732 count(*)
2733 FROM
2734 FA_MASS_ADDITIONS A,
2735 JAI_FA_AST_BLOCKS B,
2736 JAI_FA_AST_PERIOD_RATES C,
2737 FA_BOOKS D
2738 WHERE NVL(A.MASS_ADDITION_ID
2739 ,0) = NVL(D.ASSET_ID
2740 ,0)
2741 AND TO_DATE(A.ATTRIBUTE1
2742 ,'DD-MON-RR') between C.START_DATE
2743 AND C.END_DATE
2744 AND B.START_DATE >= P_YEAR_START
2745 AND B.START_DATE <= P_YEAR_END
2746 AND C.YEAR_START = P_YEAR_START
2747 AND C.YEAR_END = P_YEAR_END
2748 AND A.ATTRIBUTE2 = TO_CHAR(B.BLOCK_ID)
2749 AND B.BLOCK_ID = P_BLOCK_ID
2750 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
2751 AND D.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
2752 AND NVL(B.CLOSING_WDV
2753 ,0) <> 0
2754 AND B.YEAR_ENDED is NOT null
2755 AND D.DATE_INEFFECTIVE is null
2756 AND D.TRANSACTION_HEADER_ID_OUT is null
2757 AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
2758 AND D.CAPITALIZE_FLAG = LV_FLAG )
2759 OR A.ASSET_TYPE = LV_EXPENSED )
2760 ORDER BY
2761 1;
2762 CURSOR BLOCK_RET_CUR(P_BLOCK_ID IN NUMBER) IS
2763 SELECT
2764 A.BLOCK_ID,
2765 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
2766 ,0) SALE
2767 FROM
2768 JAI_FA_AST_BLOCKS A,
2769 FA_MASS_ADDITIONS B,
2770 FA_RETIREMENTS C
2771 WHERE A.BLOCK_ID = B.ATTRIBUTE2
2772 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
2773 AND B.MASS_ADDITION_ID = C.ASSET_ID
2774 AND A.BLOCK_ID = P_BLOCK_ID
2775 AND C.DATE_RETIRED between P_YEAR_START
2776 AND P_YEAR_END
2777 GROUP BY
2778 A.BLOCK_ID;
2779 CURSOR FA_RETIREMENTS_CUR(P_BLOCK_ID IN NUMBER) IS
2780 SELECT
2781 DISTINCT
2782 SUM(NVL(A.PROCEEDS_OF_SALE
2783 ,0)) SALE
2784 FROM
2785 FA_RETIREMENTS A,
2786 FA_MASS_ADDITIONS B,
2787 JAI_FA_AST_BLOCKS C
2788 WHERE A.ASSET_ID = B.MASS_ADDITION_ID
2789 AND B.ATTRIBUTE2 = P_BLOCK_ID
2790 AND B.ATTRIBUTE2 = C.BLOCK_ID
2791 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
2792 AND A.DATE_RETIRED between P_YEAR_START
2793 AND P_YEAR_END
2794 ORDER BY
2795 BLOCK_ID;
2796 CURSOR COST_LESS_EXEMPT_UPTO_CUR(P_ASSET_ID IN NUMBER,P_START_DATE IN DATE,P_END_DATE IN DATE,P_EXEMPT_UPTO IN NUMBER,P_BLOCK_ID IN NUMBER) IS
2797 SELECT
2798 SUM(A.ORIGINAL_COST) COSTING,
2799 A.ASSET_ID,
2800 A.DATE_PLACED_IN_SERVICE
2801 FROM
2802 FA_BOOKS A,
2803 FA_MASS_ADDITIONS B
2804 WHERE A.ASSET_ID = P_ASSET_ID
2805 AND A.ASSET_ID = B.MASS_ADDITION_ID
2806 AND A.DATE_INEFFECTIVE is null
2807 AND A.TRANSACTION_HEADER_ID_OUT is null
2808 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
2809 AND A.CAPITALIZE_FLAG = LV_FLAG )
2810 OR B.ASSET_TYPE = LV_EXPENSED )
2811 AND B.ATTRIBUTE2 = P_BLOCK_ID
2812 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
2813 AND TO_DATE(B.ATTRIBUTE1
2814 ,'DD-MON-RR') between P_START_DATE
2815 AND P_END_DATE
2816 AND ( A.ORIGINAL_COST / B.FIXED_ASSETS_UNITS ) <= NVL(P_EXEMPT_UPTO
2817 ,0)
2818 GROUP BY
2819 A.ASSET_ID,
2820 A.DATE_PLACED_IN_SERVICE;
2824 A.ASSET_ID,
2821 CURSOR COST_MORE_EXEMPT_UPTO_CUR(P_ASSET_ID IN NUMBER,P_START_DATE IN DATE,P_END_DATE IN DATE,P_EXEMPT_UPTO IN NUMBER,P_BLOCK_ID IN NUMBER) IS
2822 SELECT
2823 SUM(A.ORIGINAL_COST) COSTING,
2825 A.DATE_PLACED_IN_SERVICE
2826 FROM
2827 FA_BOOKS A,
2828 FA_MASS_ADDITIONS B
2829 WHERE A.ASSET_ID = P_ASSET_ID
2830 AND A.ASSET_ID = B.MASS_ADDITION_ID
2831 AND A.DATE_INEFFECTIVE is null
2832 AND A.TRANSACTION_HEADER_ID_OUT is null
2833 AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
2834 AND A.CAPITALIZE_FLAG = LV_FLAG )
2835 OR B.ASSET_TYPE = LV_EXPENSED )
2836 AND B.ATTRIBUTE2 = P_BLOCK_ID
2837 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
2838 AND TO_DATE(B.ATTRIBUTE1
2839 ,'DD-MON-RR') between P_START_DATE
2840 AND P_END_DATE
2841 AND ( A.ORIGINAL_COST / B.FIXED_ASSETS_UNITS ) > NVL(P_EXEMPT_UPTO
2842 ,0)
2843 GROUP BY
2844 A.ASSET_ID,
2845 A.DATE_PLACED_IN_SERVICE;
2846 CURSOR TEMP_TAB IS
2847 SELECT
2848 BLOCK_ID BLOCKID,
2849 SUM(DEPN_OF_ASSETS) ASSETS,
2850 SUM(TOTAL_BALANCE) BALANCE,
2851 SUM(SLNO) COST,
2852 SUM(FULL_EXEMPT) COST_FULL,
2853 BLOCK_ID
2854 FROM
2855 JAI_FA_DEP_BLOCKS_T
2856 GROUP BY
2857 BLOCK_ID;
2858 CURSOR TYPE_CUR(P_BLOCK_ID IN NUMBER) IS
2859 SELECT
2860 TYPE,
2861 RATE,
2862 OPENING_WDV,
2863 OPENING_WDV_ADJ,
2864 DEPN_ADJ
2865 FROM
2866 JAI_FA_AST_BLOCKS
2867 WHERE BLOCK_ID = P_BLOCK_ID
2868 AND BOOK_TYPE_CODE = P_BOOK_NAME
2869 AND START_DATE >= P_YEAR_START
2870 AND START_DATE <= P_YEAR_END;
2871 CURSOR LEFT_AMOUNT(P_BLOCK_ID IN NUMBER) IS
2872 SELECT
2873 SUM(EXEMPT_AMOUNT) L_AMOUNT,
2874 BLOCK_ID
2875 FROM
2876 JAI_FA_EXEMPTIONS
2877 WHERE BLOCK_ID = P_BLOCK_ID
2878 GROUP BY
2879 BLOCK_ID;
2880 CURSOR RETIRE_ASSET_CUR(P_ASSET_ID IN NUMBER,P_BLOCK_ID IN NUMBER) IS
2881 SELECT
2882 A.UNITS,
2883 TO_NUMBER(B.ATTRIBUTE2)
2884 FROM
2885 FA_RETIREMENTS A,
2886 FA_MASS_ADDITIONS B
2887 WHERE A.DATE_RETIRED <= P_YEAR_END
2888 AND A.ASSET_ID = B.MASS_ADDITION_ID
2889 AND A.STATUS = 'PROCESSED'
2890 AND A.ASSET_ID = P_ASSET_ID
2891 AND B.ATTRIBUTE2 = P_BLOCK_ID;
2892 CURSOR ASSET_COUNT_CUR(P_ASSET_ID IN NUMBER,P_BLOCK_ID IN NUMBER) IS
2893 SELECT
2894 DISTINCT
2895 H.UNITS,
2896 H.ASSET_ID
2897 FROM
2898 FA_ASSET_HISTORY H,
2899 FA_MASS_ADDITIONS A
2900 WHERE TRANSACTION_HEADER_ID_IN IN (
2901 SELECT
2902 MIN(TRANSACTION_HEADER_ID_IN)
2903 FROM
2904 FA_ASSET_HISTORY
2905 GROUP BY
2906 ASSET_ID )
2907 AND A.MASS_ADDITION_ID = H.ASSET_ID
2908 AND A.ATTRIBUTE2 = P_BLOCK_ID
2909 AND A.MASS_ADDITION_ID = P_ASSET_ID;
2910 CURSOR BLOCK_ID_CUR IS
2911 SELECT
2912 BLOCK_ID
2913 FROM
2914 JAI_FA_AST_BLOCKS
2915 WHERE BLOCK_ID NOT IN (
2916 SELECT
2917 TO_NUMBER(NVL(ATTRIBUTE2
2918 ,0))
2919 FROM
2920 FA_MASS_ADDITIONS )
2921 AND START_DATE >= P_YEAR_START
2922 AND START_DATE <= P_YEAR_END;
2923 CURSOR CLOSING_BALANCE_CUR(P_BLOCK_ID IN NUMBER) IS
2924 SELECT
2925 DISTINCT
2926 B.OPENING_WDV,
2927 B.OPENING_WDV_ADJ,
2928 B.DEPN_ADJ,
2929 B.RATE BLOCK_RATE,
2930 B.BLOCK_ID,
2931 B.TYPE
2932 FROM
2933 JAI_FA_AST_BLOCKS B,
2934 JAI_FA_AST_PERIOD_RATES C
2935 WHERE B.START_DATE >= P_YEAR_START
2936 AND B.START_DATE <= P_YEAR_END
2937 AND C.YEAR_START >= P_YEAR_START
2938 AND C.YEAR_END <= P_YEAR_END
2939 AND B.BOOK_TYPE_CODE = P_BOOK_NAME
2940 AND B.BLOCK_ID = P_BLOCK_ID;
2941 CURSOR ASSET_COUNT_CUR1(P_BLOCK_ID IN NUMBER) IS
2942 SELECT
2943 count(*)
2944 FROM
2945 JAI_FA_AST_BLOCKS
2946 WHERE CLOSING_WDV is not null
2947 AND YEAR_ENDED is not null
2948 AND BLOCK_ID = P_BLOCK_ID
2949 AND BOOK_TYPE_CODE = P_BOOK_NAME
2950 AND START_DATE >= P_YEAR_START
2951 AND START_DATE <= P_YEAR_END;
2952 CURSOR RETIRE_COUNT IS
2953 SELECT
2954 COUNT(*)
2955 FROM
2956 FA_RETIREMENTS
2957 WHERE DATE_RETIRED BETWEEN P_YEAR_START
2958 AND P_YEAR_END;
2959 CURSOR RET_ASSET_BLOCK_CUR IS
2960 SELECT
2961 count(*)
2962 FROM
2963 FA_RETIREMENTS A,
2964 FA_MASS_ADDITIONS B,
2965 JAI_FA_AST_BLOCKS C
2966 WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
2967 AND P_YEAR_END
2968 AND A.ASSET_ID = B.MASS_ADDITION_ID
2969 AND B.ATTRIBUTE2 = C.BLOCK_ID
2970 AND C.START_DATE between P_YEAR_START
2971 AND P_YEAR_END;
2972 CURSOR ASSET_ID_CURSOR(P_BLOCK_ID IN NUMBER) IS
2973 SELECT
2974 MASS_ADDITION_ID
2975 FROM
2976 FA_MASS_ADDITIONS
2977 WHERE ATTRIBUTE2 = P_BLOCK_ID;
2978 CURSOR OPENING_WDV_ADJ_CUR IS
2979 SELECT
2980 RATE,
2981 TYPE
2982 FROM
2983 JAI_FA_AST_BLOCKS
2984 WHERE START_DATE BETWEEN P_YEAR_START
2985 AND P_YEAR_END
2986 AND NVL(OPENING_WDV_ADJ
2990 SELECT
2987 ,DEPN_ADJ) is not null
2988 AND BOOK_TYPE_CODE = P_BOOK_NAME;
2989 CURSOR ASSET_ADD_PR_CUR IS
2991 RATE,
2992 TYPE
2993 FROM
2994 JAI_FA_AST_BLOCKS
2995 WHERE START_DATE BETWEEN P_YEAR_START
2996 AND P_YEAR_END
2997 AND BOOK_TYPE_CODE = P_BOOK_NAME
2998 AND CLOSING_WDV > 0
2999 AND BLOCK_ID in (
3000 SELECT
3001 TO_NUMBER(ATTRIBUTE2)
3002 FROM
3003 FA_MASS_ADDITIONS );
3004 CURSOR ADJUST_OPEN_CUR(P_RATE IN NUMBER,P_TYPE IN VARCHAR2) IS
3005 SELECT
3006 BLOCK_ID,
3007 OPENING_WDV,
3008 CLOSING_WDV,
3009 RATE,
3010 START_DATE
3011 FROM
3012 JAI_FA_AST_BLOCKS
3013 WHERE RATE = P_RATE
3014 AND TYPE = P_TYPE
3015 AND START_DATE > P_YEAR_END
3016 AND BOOK_TYPE_CODE = P_BOOK_NAME;
3017 CURSOR YEAR_END_CUR(P_START_DATE IN DATE) IS
3018 SELECT
3019 YEAR_END
3020 FROM
3021 JAI_FA_AST_YEARS
3022 WHERE YEAR_START = P_START_DATE;
3023 CURSOR COUNT_DEPN_CUR(P_BLOCK_ID IN NUMBER,P_SLNO IN NUMBER,P_ASSET_ID IN NUMBER) IS
3024 SELECT
3025 count(*)
3026 FROM
3027 JAI_FA_DEP_BLOCKS
3028 WHERE SLNO > 0
3029 AND BLOCK_ID = P_BLOCK_ID
3030 AND SLNO = P_SLNO
3031 AND UNPLANNED_DEPN = P_ASSET_ID;
3032 CURSOR UNPLANNED_DEPN_CUR(P_BLOCK_ID IN NUMBER) IS
3033 SELECT
3034 UNPLANNED_DEPN
3035 FROM
3036 JAI_FA_DEP_BLOCKS
3037 WHERE BLOCK_ID = P_BLOCK_ID
3038 AND SLNO = - 1;
3039 CURSOR COUNTER_CUR(P_BLOCK_ID IN NUMBER) IS
3040 SELECT
3041 COUNT(*)
3042 FROM
3043 JAI_FA_DEP_BLOCKS
3044 WHERE BLOCK_ID = P_BLOCK_ID
3045 AND SLNO = 0;
3046 BEGIN
3047 LV_CAPITALIZED := 'CAPITALIZED';
3048 LV_FLAG := 'YES';
3049 LV_EXPENSED := 'EXPENSED';
3050 OPEN RETIRE_COUNT;
3051 FETCH RETIRE_COUNT
3052 INTO V_COUNTER;
3053 CLOSE RETIRE_COUNT;
3054 OPEN RET_ASSET_BLOCK_CUR;
3055 FETCH RET_ASSET_BLOCK_CUR
3056 INTO V_RETIRE_COUNT1;
3057 CLOSE RET_ASSET_BLOCK_CUR;
3058 OPEN ASSET_BLOCK_CUR;
3059 LOOP
3060 V_COUNT := ASSET_BLOCK_CUR%ROWCOUNT;
3061 FETCH ASSET_BLOCK_CUR
3062 INTO V_ASSET_ID,V_BLOCK_ID,V_OPENING_WDV,V_BLOCK_RATE,V_SLNO,V_START_DATE,V_END_DATE,V_PERIOD_RATE,V_EXEMPT_UPTO,V_AQUISATION_DATE,V_DATE_PLACED_IN_SERVICE,V_CURRENT_UNITS;
3063 IF ASSET_BLOCK_CUR%NOTFOUND THEN
3064 FOR block_id IN BLOCK_ID_CUR LOOP
3065 FOR closing IN CLOSING_BALANCE_CUR(block_id.block_id) LOOP
3066 OPEN BLOCK_RET_CUR(BLOCK_ID.BLOCK_ID);
3067 FETCH BLOCK_RET_CUR
3068 INTO V_CLOSING_BLOCK_ID,V_RETIRED;
3069 CLOSE BLOCK_RET_CUR;
3070 V_DEPRECIATION := NVL(NVL(CLOSING.OPENING_WDV
3071 ,0) + NVL(CLOSING.OPENING_WDV_ADJ
3072 ,0) - NVL(V_RETIRED
3073 ,0)
3074 ,0) * (CLOSING.BLOCK_RATE / 100);
3075 V_CLOSING_BALANCE := NVL(NVL(CLOSING.OPENING_WDV
3076 ,0) + NVL(CLOSING.OPENING_WDV_ADJ
3077 ,0)
3078 ,0) - NVL(V_DEPRECIATION
3079 ,0) - NVL(V_RETIRED
3080 ,0);
3081 V_DEPRECIATION := ROUND(V_DEPRECIATION
3082 ,2);
3083 V_CLOSING_BALANCE := ROUND(V_CLOSING_BALANCE
3084 ,2);
3085 IF V_CLOSING_BALANCE > 0 THEN
3086 V_CLOSING_BALANCE := V_CLOSING_BALANCE;
3087 ELSE
3088 V_CLOSING_BALANCE := 0;
3089 END IF;
3090 OPEN ASSET_COUNT_CUR1(CLOSING.BLOCK_ID);
3091 FETCH ASSET_COUNT_CUR1
3092 INTO V_COUNT2;
3093 CLOSE ASSET_COUNT_CUR1;
3094 IF CLOSING.OPENING_WDV_ADJ IS NOT NULL OR CLOSING.DEPN_ADJ IS NOT NULL THEN
3095 DEPRECIATION1(CLOSING.BLOCK_ID
3096 ,CLOSING.OPENING_WDV
3097 ,V_CLOSING_BALANCE
3098 ,CLOSING.OPENING_WDV_ADJ
3099 ,CLOSING.DEPN_ADJ
3100 ,P_YEAR_END);
3101 END IF;
3102 OPEN UNPLANNED_DEPN_CUR(CLOSING.BLOCK_ID);
3103 FETCH UNPLANNED_DEPN_CUR
3104 INTO V_UNPLANNED_DEPN;
3105 CLOSE UNPLANNED_DEPN_CUR;
3106 IF NVL(V_COUNT2
3107 ,0) = 0 THEN
3108 UPDATE
3109 JAI_FA_AST_BLOCKS
3110 SET
3111 CLOSING_WDV = NVL(V_CLOSING_BALANCE
3112 ,0) - NVL(CLOSING.DEPN_ADJ
3113 ,0)
3114 ,YEAR_ENDED = P_YEAR_END
3115 WHERE BLOCK_ID = CLOSING.BLOCK_ID;
3116 SELECT
3117 JAI_FA_AST_BLOCKS_S.NEXTVAL
3118 INTO V_BLOCK_ID1
3119 FROM
3120 DUAL;
3121 INSERT INTO JAI_FA_AST_BLOCKS
3122 (BLOCK_ID
3123 ,TYPE
3124 ,RATE
3125 ,BOOK_TYPE_CODE
3126 ,OPENING_WDV
3127 ,START_DATE
3128 ,CREATION_DATE
3129 ,CREATED_BY
3130 ,LAST_UPDATE_DATE
3131 ,LAST_UPDATE_LOGIN
3132 ,LAST_UPDATED_BY)
3133 VALUES (V_BLOCK_ID1
3137 ,NVL(V_CLOSING_BALANCE
3134 ,CLOSING.TYPE
3135 ,CLOSING.BLOCK_RATE
3136 ,P_BOOK_NAME
3138 ,0) - NVL(CLOSING.DEPN_ADJ
3139 ,0)
3140 ,P_YEAR_END + 1
3141 ,SYSDATE
3142 ,UID
3143 ,SYSDATE
3144 ,UID
3145 ,UID);
3146 INSERT INTO JAI_FA_DEP_BLOCKS
3147 (BLOCK_DEPN_ID
3148 ,BLOCK_ID
3149 ,SLNO
3150 ,DEPN_OF_ASSETS
3151 ,YEAR_ENDED
3152 ,CREATION_DATE
3153 ,CREATED_BY
3154 ,LAST_UPDATE_DATE
3155 ,LAST_UPDATE_LOGIN
3156 ,LAST_UPDATED_BY
3157 ,UNPLANNED_DEPN)
3158 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3159 ,CLOSING.BLOCK_ID
3160 ,0
3161 ,V_DEPRECIATION
3162 ,P_YEAR_END
3163 ,SYSDATE
3164 ,UID
3165 ,SYSDATE
3166 ,UID
3167 ,UID
3168 ,CLOSING.DEPN_ADJ);
3169 ELSE
3170 UPDATE
3171 JAI_FA_DEP_BLOCKS
3172 SET
3173 DEPN_OF_ASSETS = V_DEPRECIATION
3174 WHERE BLOCK_ID = CLOSING.BLOCK_ID
3175 AND SLNO = 0;
3176 UPDATE
3177 JAI_FA_AST_BLOCKS
3178 SET
3179 OPENING_WDV = NVL(V_CLOSING_BALANCE
3180 ,0) - NVL(V_UNPLANNED_DEPN
3181 ,0)
3182 WHERE START_DATE = P_YEAR_END + 1
3183 AND TYPE = CLOSING.TYPE
3184 AND RATE = CLOSING.BLOCK_RATE;
3185 UPDATE
3186 JAI_FA_AST_BLOCKS
3187 SET
3188 CLOSING_WDV = NVL(V_CLOSING_BALANCE
3189 ,0) - NVL(V_UNPLANNED_DEPN
3190 ,0)
3191 ,YEAR_ENDED = P_YEAR_END
3192 WHERE BLOCK_ID = CLOSING.BLOCK_ID;
3193 IF CLOSING.DEPN_ADJ IS NOT NULL THEN
3194 UPDATE
3195 JAI_FA_DEP_BLOCKS
3196 SET
3197 UNPLANNED_DEPN = CLOSING.DEPN_ADJ
3198 WHERE BLOCK_ID = CLOSING.BLOCK_ID
3199 AND SLNO = 0;
3200 END IF;
3201 END IF;
3202 END LOOP;
3203 END LOOP;
3204 CLOSE ASSET_BLOCK_CUR;
3205 EXIT;
3206 ELSE
3207 OPEN COUNT_DEPN_CUR(V_BLOCK_ID,V_SLNO,V_ASSET_ID);
3208 FETCH COUNT_DEPN_CUR
3209 INTO V_DEPN_SLNO;
3210 CLOSE COUNT_DEPN_CUR;
3211 FOR exempt IN COST_MORE_EXEMPT_UPTO_CUR(v_asset_id,v_start_date, v_end_date ,
3212 nvl(v_exempt_upto,0),v_block_id ) LOOP
3213 V_COST := EXEMPT.COSTING;
3214 IF V_DEPN_SLNO = 0 THEN
3215 IF V_SERIAL_NUM1 IS NULL THEN
3216 V_SERIAL_NUM1 := V_SLNO;
3217 I := 1;
3218 V_DEPN_AMOUNT := 0;
3219 ELSIF V_SERIAL_NUM1 <> V_SLNO THEN
3220 I := 0;
3221 INSERT INTO JAI_FA_DEP_BLOCKS
3222 (BLOCK_DEPN_ID
3223 ,BLOCK_ID
3224 ,SLNO
3225 ,DEPN_OF_ASSETS
3226 ,YEAR_ENDED
3227 ,CREATION_DATE
3228 ,CREATED_BY
3229 ,LAST_UPDATE_DATE
3230 ,LAST_UPDATE_LOGIN
3231 ,LAST_UPDATED_BY
3232 ,UNPLANNED_DEPN)
3233 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3234 ,V_BLOCK_ID
3235 ,V_SERIAL_NUM1
3236 ,NVL(V_DEPN_AMOUNT
3237 ,0)
3238 ,P_YEAR_END
3239 ,SYSDATE
3240 ,UID
3241 ,SYSDATE
3242 ,UID
3243 ,UID
3244 ,V_ASSET_ID);
3245 V_DEPN_AMOUNT := 0;
3246 V_FLAG := TRUE;
3247 V_SERIAL_NUM1 := V_SLNO;
3248 END IF;
3249 END IF;
3250 IF NVL(V_AQUISATION_DATE
3251 ,SYSDATE) >= NVL(V_START_DATE
3252 ,SYSDATE) AND NVL(V_AQUISATION_DATE
3253 ,SYSDATE) <= NVL(V_END_DATE
3254 ,SYSDATE) THEN
3255 IF V_PERIOD_RATE = 100 THEN
3256 V_MORE_AMOUNT1 := NVL(V_COST
3257 ,0) * NVL(V_BLOCK_RATE
3258 ,0) / 100;
3259 END IF;
3260 END IF;
3261 IF NVL(V_AQUISATION_DATE
3262 ,V_DATE_PLACED_IN_SERVICE) <= NVL(P_YEAR_START
3263 ,SYSDATE) THEN
3264 V_MORE_AMOUNT := NVL(V_COST
3265 ,0) * NVL(V_BLOCK_RATE
3266 ,0) / 100;
3267 END IF;
3268 IF NVL(V_AQUISATION_DATE
3269 ,SYSDATE) >= NVL(V_START_DATE
3270 ,SYSDATE) AND NVL(V_AQUISATION_DATE
3271 ,SYSDATE) <= NVL(V_END_DATE
3272 ,SYSDATE) THEN
3273 IF NVL(V_DATE_PLACED_IN_SERVICE
3274 ,SYSDATE) >= NVL(P_YEAR_START
3275 ,SYSDATE) AND NVL(V_DATE_PLACED_IN_SERVICE
3276 ,SYSDATE) <= NVL(P_YEAR_END
3277 ,SYSDATE) THEN
3278 IF V_PERIOD_RATE <> 100 THEN
3279 V_MORE_AMOUNT := NVL((NVL(V_COST
3280 ,0) * NVL(V_BLOCK_RATE
3281 ,0) / 100 * NVL(V_PERIOD_RATE
3285 END IF;
3282 ,0) / 100)
3283 ,0);
3284 END IF;
3286 END IF;
3287 IF V_AQUISATION_DATE IS NULL AND (NVL(V_DATE_PLACED_IN_SERVICE
3288 ,SYSDATE) >= NVL(V_START_DATE
3289 ,SYSDATE) AND NVL(V_DATE_PLACED_IN_SERVICE
3290 ,SYSDATE) <= NVL(V_END_DATE
3291 ,SYSDATE)) THEN
3292 IF V_PERIOD_RATE <> 100 THEN
3293 V_MORE_AMOUNT := NVL((NVL(V_COST
3294 ,0) * NVL(V_BLOCK_RATE
3295 ,0) / 100 * NVL(V_PERIOD_RATE
3296 ,0) / 100)
3297 ,0);
3298 END IF;
3299 END IF;
3300 IF NVL(V_AQUISATION_DATE
3301 ,V_DATE_PLACED_IN_SERVICE) >= NVL(V_START_DATE
3302 ,SYSDATE) AND NVL(V_AQUISATION_DATE
3303 ,V_DATE_PLACED_IN_SERVICE) <= NVL(V_END_DATE
3304 ,SYSDATE) THEN
3305 IF V_PERIOD_RATE = 0 THEN
3306 V_MORE_AMOUNT := 0;
3307 END IF;
3308 END IF;
3309 IF EXEMPT.ASSET_ID = V_ASSET_ID THEN
3310 IF V_PERIOD_RATE = 100 THEN
3311 V_MORE_AMOUNT := NVL(V_MORE_AMOUNT1
3312 ,0);
3313 ELSE
3314 V_MORE_AMOUNT := NVL(V_MORE_AMOUNT
3315 ,0);
3316 END IF;
3317 END IF;
3318 V_DEPN_AMOUNT := V_DEPN_AMOUNT + V_MORE_AMOUNT;
3319 V_CLOSING_BALANCE := NVL(V_COST
3320 ,0) - NVL(V_MORE_AMOUNT
3321 ,0);
3322 INSERT INTO JAI_FA_DEP_BLOCKS_T
3323 (BLOCK_ID
3324 ,SLNO
3325 ,DEPN_OF_ASSETS
3326 ,YEAR_ENDED
3327 ,FULL_EXEMPT
3328 ,CREATION_DATE
3329 ,CREATED_BY
3330 ,LAST_UPDATE_DATE
3331 ,LAST_UPDATE_LOGIN
3332 ,LAST_UPDATED_BY
3333 ,TOTAL_BALANCE
3334 ,UNPLANNED_DEPN)
3335 VALUES (V_BLOCK_ID
3336 ,ROUND(NVL(V_COST
3337 ,0)
3338 ,2)
3339 ,ROUND(NVL(V_MORE_AMOUNT
3340 ,0)
3341 ,2)
3342 ,P_YEAR_END
3343 ,NVL(V_MORE_AMOUNT1
3344 ,0)
3345 ,SYSDATE
3346 ,UID
3347 ,SYSDATE
3348 ,UID
3349 ,UID
3350 ,ROUND(NVL(V_CLOSING_BALANCE
3351 ,0)
3352 ,2)
3353 ,V_SLNO);
3354 END LOOP;
3355 IF V_DEPN_SLNO = 0 THEN
3356 IF V_FLAG OR I = 1 THEN
3357 IF V_SERIAL_NUM1 = V_SLNO THEN
3358 INSERT INTO JAI_FA_DEP_BLOCKS
3359 (BLOCK_DEPN_ID
3360 ,BLOCK_ID
3361 ,SLNO
3362 ,DEPN_OF_ASSETS
3363 ,YEAR_ENDED
3364 ,CREATION_DATE
3365 ,CREATED_BY
3366 ,LAST_UPDATE_DATE
3367 ,LAST_UPDATE_LOGIN
3368 ,LAST_UPDATED_BY
3369 ,UNPLANNED_DEPN)
3370 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3371 ,V_BLOCK_ID
3372 ,V_SERIAL_NUM1
3373 ,NVL(V_DEPN_AMOUNT
3374 ,0)
3375 ,P_YEAR_END
3376 ,SYSDATE
3377 ,UID
3378 ,SYSDATE
3379 ,UID
3380 ,UID
3381 ,V_ASSET_ID);
3382 V_SERIAL_NUM1 := NULL;
3383 V_DEPN_AMOUNT := 0;
3384 END IF;
3385 END IF;
3386 I := 0;
3387 END IF;
3388 FOR less_exempt IN COST_LESS_EXEMPT_UPTO_CUR(v_asset_id,v_start_date,
3389 v_end_date ,nvl(v_exempt_upto,0),v_block_id ) LOOP
3390 V_COST_LESS := LESS_EXEMPT.COSTING;
3391 INSERT INTO JAI_FA_EXEMPTIONS
3392 (BLOCK_ID
3393 ,EXEMPT_AMOUNT
3394 ,FA_EXEMPTION_ID
3395 ,CREATED_BY
3396 ,CREATION_DATE
3397 ,LAST_UPDATED_BY
3398 ,LAST_UPDATE_DATE
3399 ,LAST_UPDATE_LOGIN
3400 ,OBJECT_VERSION_NUMBER)
3401 VALUES (V_BLOCK_ID
3402 ,ROUND(NVL(V_COST_LESS
3403 ,0)
3404 ,2)
3405 ,JAI_FA_EXEMPTIONS_S.NEXTVAL
3406 ,FND_GLOBAL.USER_ID
3407 ,SYSDATE
3408 ,FND_GLOBAL.USER_ID
3409 ,SYSDATE
3410 ,FND_GLOBAL.LOGIN_ID
3411 ,NULL);
3412 IF V_DEPN_SLNO = 0 THEN
3413 INSERT INTO JAI_FA_DEP_BLOCKS
3414 (BLOCK_DEPN_ID
3415 ,BLOCK_ID
3416 ,SLNO
3417 ,DEPN_OF_ASSETS
3418 ,FULL_EXEMPT
3419 ,YEAR_ENDED
3420 ,CREATION_DATE
3421 ,CREATED_BY
3422 ,LAST_UPDATE_DATE
3423 ,LAST_UPDATE_LOGIN
3424 ,LAST_UPDATED_BY
3425 ,UNPLANNED_DEPN)
3426 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3427 ,V_BLOCK_ID
3428 ,V_SLNO
3429 ,NVL(V_COST_LESS
3430 ,0)
3431 ,NVL(V_COST_LESS
3432 ,0)
3433 ,P_YEAR_END
3434 ,SYSDATE
3435 ,UID
3436 ,SYSDATE
3437 ,UID
3441 END LOOP;
3438 ,UID
3439 ,V_ASSET_ID);
3440 END IF;
3442 IF ASSET_BLOCK_CUR%FOUND IS NULL THEN
3443 CLOSE ASSET_BLOCK_CUR;
3444 EXIT;
3445 END IF;
3446 END IF;
3447 END LOOP;
3448 FOR temp_rec IN TEMP_TAB LOOP
3449 V_CLOSING := 0;
3450 V_CLOSING1 := 0;
3451 V_CLOSING_BALANCE := 0;
3452 OPEN COUNTER_CUR(TEMP_REC.BLOCK_ID);
3453 FETCH COUNTER_CUR
3454 INTO V_COUNTER;
3455 CLOSE COUNTER_CUR;
3456 OPEN LEFT_AMOUNT(TEMP_REC.BLOCK_ID);
3457 FETCH LEFT_AMOUNT
3458 INTO V_LEFT_AMOUNT,V_LEFT_BLOCK_ID;
3459 CLOSE LEFT_AMOUNT;
3460 OPEN ASSET_ID_CURSOR(TEMP_REC.BLOCK_ID);
3461 FETCH ASSET_ID_CURSOR
3462 INTO V_ASSET_ID;
3463 CLOSE ASSET_ID_CURSOR;
3464 OPEN ASSET_COUNT_CUR(V_ASSET_ID,TEMP_REC.BLOCK_ID);
3465 FETCH ASSET_COUNT_CUR
3466 INTO V_ASSET_COUNT,V_BLOCK_ID1;
3467 CLOSE ASSET_COUNT_CUR;
3468 OPEN RETIRE_ASSET_CUR(V_ASSET_ID,TEMP_REC.BLOCK_ID);
3469 FETCH RETIRE_ASSET_CUR
3470 INTO V_RETIRE_COUNT,V_BLOCK_ID2;
3471 CLOSE RETIRE_ASSET_CUR;
3472 OPEN UNPLANNED_DEPN_CUR(TEMP_REC.BLOCK_ID);
3473 FETCH UNPLANNED_DEPN_CUR
3474 INTO V_UNPLANNED_DEPN;
3475 CLOSE UNPLANNED_DEPN_CUR;
3476 IF V_BLOCK_ID1 = V_BLOCK_ID2 THEN
3477 V_ORIGINAL_COUNT := NVL(V_ASSET_COUNT
3478 ,0) - NVL(V_RETIRE_COUNT
3479 ,0);
3480 ELSE
3481 V_ORIGINAL_COUNT := NULL;
3482 END IF;
3483 IF TEMP_REC.BLOCK_ID = V_LEFT_BLOCK_ID THEN
3484 V_LEFT_AMOUNT := NVL(V_LEFT_AMOUNT
3485 ,0);
3486 ELSE
3487 V_LEFT_AMOUNT := 0;
3488 END IF;
3489 OPEN COUNT_CUR(TEMP_REC.BLOCK_ID);
3490 FETCH COUNT_CUR
3491 INTO V_COUNT1;
3492 CLOSE COUNT_CUR;
3493 OPEN TYPE_CUR(TEMP_REC.BLOCK_ID);
3494 FETCH TYPE_CUR
3495 INTO V_TYPE,V_BLOCK_RATE,V_OPENING_WDV1,V_OPENING_WDV_ADJ,V_DEPN_ADJ;
3496 CLOSE TYPE_CUR;
3497 OPEN FA_RETIREMENTS_CUR(TEMP_REC.BLOCK_ID);
3498 FETCH FA_RETIREMENTS_CUR
3499 INTO V_PROCEEDS_OF_SALE;
3500 CLOSE FA_RETIREMENTS_CUR;
3501 OPEN BLOCK_RET_CUR(TEMP_REC.BLOCK_ID);
3502 FETCH BLOCK_RET_CUR
3503 INTO V_CLOSING_BLOCK_ID,V_RETIRED;
3504 CLOSE BLOCK_RET_CUR;
3505 V_AMOUNT1 := NVL(NVL(V_OPENING_WDV1
3506 ,0) + NVL(V_OPENING_WDV_ADJ
3507 ,0) - NVL(V_PROCEEDS_OF_SALE
3508 ,0) - NVL(V_RETIRED
3509 ,0)
3510 ,0) * NVL(V_BLOCK_RATE
3511 ,0) / 100;
3512 V_CLOSING := NVL(V_OPENING_WDV1
3513 ,0) + NVL(TEMP_REC.COST
3514 ,0) - NVL(V_PROCEEDS_OF_SALE
3515 ,0) - NVL(V_RETIRED
3516 ,0);
3517 V_CLOSING1 := NVL(V_OPENING_WDV1
3518 ,0) + NVL(TEMP_REC.COST_FULL
3519 ,0) - NVL(V_PROCEEDS_OF_SALE
3520 ,0) - NVL(V_RETIRED
3521 ,0);
3522 V_CLOSING_BALANCE := NVL(V_OPENING_WDV1
3523 ,0) + NVL(V_OPENING_WDV_ADJ
3524 ,0) + NVL(TEMP_REC.BALANCE
3525 ,0) + NVL(V_LEFT_AMOUNT
3526 ,0) - NVL(V_LEFT_AMOUNT
3527 ,0) - NVL(V_AMOUNT1
3528 ,0) - NVL(V_PROCEEDS_OF_SALE
3529 ,0) - NVL(V_DEPN_ADJ
3530 ,0) - NVL(V_RETIRED
3531 ,0) - NVL(V_UNPLANNED_DEPN
3532 ,0);
3533 IF V_OPENING_WDV_ADJ IS NOT NULL OR V_DEPN_ADJ IS NOT NULL THEN
3534 DEPRECIATION1(TEMP_REC.BLOCK_ID
3535 ,V_OPENING_WDV1
3536 ,V_CLOSING_BALANCE
3537 ,V_OPENING_WDV_ADJ
3538 ,V_DEPN_ADJ
3539 ,P_YEAR_END);
3540 END IF;
3541 IF V_AMOUNT1 > 0 THEN
3542 V_AMOUNT1 := V_AMOUNT1;
3543 ELSE
3544 V_AMOUNT1 := 0;
3545 END IF;
3546 IF V_CLOSING > 0 THEN
3547 IF NVL(V_COUNT1
3548 ,0) = 0 THEN
3549 UPDATE
3550 JAI_FA_AST_BLOCKS
3551 SET
3552 CLOSING_WDV = NVL(V_CLOSING_BALANCE
3553 ,0)
3554 ,YEAR_ENDED = P_YEAR_END
3555 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3556 INSERT INTO JAI_FA_DEP_BLOCKS
3557 (BLOCK_DEPN_ID
3558 ,BLOCK_ID
3559 ,SLNO
3560 ,DEPN_OF_ASSETS
3561 ,YEAR_ENDED
3562 ,CREATION_DATE
3563 ,CREATED_BY
3564 ,LAST_UPDATE_DATE
3565 ,LAST_UPDATE_LOGIN
3566 ,LAST_UPDATED_BY
3567 ,UNPLANNED_DEPN)
3568 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3569 ,TEMP_REC.BLOCK_ID
3570 ,0
3571 ,NVL(V_AMOUNT1
3572 ,0)
3573 ,P_YEAR_END
3574 ,SYSDATE
3575 ,UID
3576 ,SYSDATE
3577 ,UID
3578 ,UID
3579 ,V_DEPN_ADJ);
3580 SELECT
3581 JAI_FA_AST_BLOCKS_S.NEXTVAL
3582 INTO V_BLOCK_ID
3583 FROM
3584 DUAL;
3585 INSERT INTO JAI_FA_AST_BLOCKS
3586 (BLOCK_ID
3587 ,TYPE
3588 ,RATE
3589 ,OPENING_WDV
3593 ,CREATED_BY
3590 ,START_DATE
3591 ,BOOK_TYPE_CODE
3592 ,CREATION_DATE
3594 ,LAST_UPDATE_DATE
3595 ,LAST_UPDATE_LOGIN
3596 ,LAST_UPDATED_BY)
3597 VALUES (V_BLOCK_ID
3598 ,V_TYPE
3599 ,V_BLOCK_RATE
3600 ,NVL(V_CLOSING_BALANCE
3601 ,0)
3602 ,P_YEAR_END + 1
3603 ,P_BOOK_NAME
3604 ,SYSDATE
3605 ,UID
3606 ,SYSDATE
3607 ,UID
3608 ,UID);
3609 ELSE
3610 UPDATE
3611 JAI_FA_AST_BLOCKS
3612 SET
3613 CLOSING_WDV = NVL(V_CLOSING_BALANCE
3614 ,0)
3615 ,YEAR_ENDED = P_YEAR_END
3616 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3617 UPDATE
3618 JAI_FA_DEP_BLOCKS
3619 SET
3620 DEPN_OF_ASSETS = NVL(V_AMOUNT1
3621 ,0)
3622 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
3623 AND SLNO = 0;
3624 UPDATE
3625 JAI_FA_AST_BLOCKS
3626 SET
3627 OPENING_WDV = NVL(V_CLOSING_BALANCE
3628 ,0)
3629 WHERE START_DATE = P_YEAR_END + 1
3630 AND RATE = V_BLOCK_RATE
3631 AND TYPE = V_TYPE;
3632 K := 1;
3633 IF V_DEPN_ADJ IS NOT NULL THEN
3634 UPDATE
3635 JAI_FA_DEP_BLOCKS
3636 SET
3637 UNPLANNED_DEPN = V_DEPN_ADJ
3638 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
3639 AND SLNO = 0;
3640 END IF;
3641 END IF;
3642 END IF;
3643 IF V_CLOSING < 0 THEN
3644 UPDATE
3645 JAI_FA_AST_BLOCKS
3646 SET
3647 CLOSING_WDV = 0
3648 ,YEAR_ENDED = P_YEAR_END
3649 ,CAPITAL_GAINS = ABS(V_CLOSING)
3650 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3651 IF NVL(V_COUNTER
3652 ,0) = 0 THEN
3653 INSERT INTO JAI_FA_DEP_BLOCKS
3654 (BLOCK_DEPN_ID
3655 ,BLOCK_ID
3656 ,SLNO
3657 ,DEPN_OF_ASSETS
3658 ,YEAR_ENDED
3659 ,CREATION_DATE
3660 ,CREATED_BY
3661 ,LAST_UPDATE_DATE
3662 ,LAST_UPDATE_LOGIN
3663 ,LAST_UPDATED_BY
3664 ,UNPLANNED_DEPN)
3665 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3666 ,TEMP_REC.BLOCK_ID
3667 ,0
3668 ,0
3669 ,P_YEAR_END
3670 ,SYSDATE
3671 ,UID
3672 ,SYSDATE
3673 ,UID
3674 ,UID
3675 ,V_DEPN_ADJ);
3676 ELSE
3677 UPDATE
3678 JAI_FA_DEP_BLOCKS
3679 SET
3680 DEPN_OF_ASSETS = 0
3681 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3682 END IF;
3683 UPDATE
3684 JAI_FA_AST_BLOCKS
3685 SET
3686 OPENING_WDV = 0
3687 WHERE START_DATE = P_YEAR_END + 1
3688 AND RATE = V_BLOCK_RATE
3689 AND TYPE = V_TYPE;
3690 ELSIF V_CLOSING = 0 THEN
3691 UPDATE
3692 JAI_FA_AST_BLOCKS
3693 SET
3694 CLOSING_WDV = 0
3695 ,YEAR_ENDED = P_YEAR_END
3696 ,CAPITAL_GAINS = NULL
3697 ,CAPITAL_LOSS = NULL
3698 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3699 IF NVL(V_COUNTER
3700 ,0) = 0 THEN
3701 INSERT INTO JAI_FA_DEP_BLOCKS
3702 (BLOCK_DEPN_ID
3703 ,BLOCK_ID
3704 ,SLNO
3705 ,DEPN_OF_ASSETS
3706 ,YEAR_ENDED
3707 ,CREATION_DATE
3708 ,CREATED_BY
3709 ,LAST_UPDATE_DATE
3710 ,LAST_UPDATE_LOGIN
3711 ,LAST_UPDATED_BY
3712 ,UNPLANNED_DEPN)
3713 VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
3714 ,TEMP_REC.BLOCK_ID
3715 ,0
3716 ,0
3717 ,P_YEAR_END
3718 ,SYSDATE
3719 ,UID
3720 ,SYSDATE
3721 ,UID
3722 ,UID
3723 ,V_DEPN_ADJ);
3724 ELSE
3725 UPDATE
3726 JAI_FA_DEP_BLOCKS
3727 SET
3728 DEPN_OF_ASSETS = 0
3729 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3730 END IF;
3731 UPDATE
3732 JAI_FA_AST_BLOCKS
3733 SET
3734 OPENING_WDV = 0
3735 WHERE START_DATE = P_YEAR_END + 1
3736 AND RATE = V_BLOCK_RATE
3737 AND TYPE = V_TYPE;
3738 END IF;
3739 IF V_CLOSING > 0 AND V_ORIGINAL_COUNT = 0 THEN
3740 UPDATE
3741 JAI_FA_AST_BLOCKS
3742 SET
3743 CLOSING_WDV = 0
3744 ,YEAR_ENDED = P_YEAR_END
3745 ,CAPITAL_GAINS = NULL
3746 ,CAPITAL_LOSS = V_CLOSING
3747 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3748 UPDATE
3749 JAI_FA_DEP_BLOCKS
3750 SET
3751 DEPN_OF_ASSETS = 0
3752 WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
3753 UPDATE
3754 JAI_FA_AST_BLOCKS
3755 SET
3756 OPENING_WDV = 0
3757 WHERE START_DATE = P_YEAR_END + 1
3758 AND RATE = V_BLOCK_RATE
3759 AND TYPE = V_TYPE;
3760 END IF;
3761 IF K = 0 THEN
3762 FOR opening_adjust IN OPENING_WDV_ADJ_CUR LOOP
3763 V_PREV_CLOSING_BALANCE := NULL;
3767 INTO V_YEAR_END;
3764 FOR adjust IN ADJUST_OPEN_CUR(opening_adjust.rate,opening_adjust.type) LOOP
3765 OPEN YEAR_END_CUR(ADJUST.START_DATE);
3766 FETCH YEAR_END_CUR
3768 CLOSE YEAR_END_CUR;
3769 V_DEPRECIATION := NVL(V_PREV_CLOSING_BALANCE
3770 ,ADJUST.OPENING_WDV) * NVL(ADJUST.RATE
3771 ,0) / 100;
3772 V_CLOSING_BALANCE := NVL(V_PREV_CLOSING_BALANCE
3773 ,ADJUST.OPENING_WDV) - NVL(V_DEPRECIATION
3774 ,0);
3775 UPDATE
3776 JAI_FA_AST_BLOCKS
3777 SET
3778 OPENING_WDV = NVL(V_PREV_CLOSING_BALANCE
3779 ,ADJUST.OPENING_WDV)
3780 WHERE START_DATE = ADJUST.START_DATE
3781 AND BLOCK_ID = ADJUST.BLOCK_ID;
3782 IF ADJUST.CLOSING_WDV IS NOT NULL THEN
3783 UPDATE
3784 JAI_FA_DEP_BLOCKS
3785 SET
3786 DEPN_OF_ASSETS = V_DEPRECIATION
3787 WHERE BLOCK_ID = ADJUST.BLOCK_ID
3788 AND SLNO = 0;
3789 UPDATE
3790 JAI_FA_AST_BLOCKS
3791 SET
3792 CLOSING_WDV = NVL(V_CLOSING_BALANCE
3793 ,0)
3794 ,YEAR_ENDED = V_YEAR_END
3795 WHERE BLOCK_ID = ADJUST.BLOCK_ID;
3796 END IF;
3797 V_PREV_CLOSING_BALANCE := V_CLOSING_BALANCE;
3798 END LOOP;
3799 END LOOP;
3800 ELSIF K = 1 THEN
3801 FOR asset_add IN ASSET_ADD_PR_CUR LOOP
3802 V_PREV_CLOSING_BALANCE := NULL;
3803 FOR adjust IN ADJUST_OPEN_CUR(asset_add.rate,asset_add.type) LOOP
3804 OPEN YEAR_END_CUR(ADJUST.START_DATE);
3805 FETCH YEAR_END_CUR
3806 INTO V_YEAR_END;
3807 CLOSE YEAR_END_CUR;
3808 V_DEPRECIATION := NVL(V_PREV_CLOSING_BALANCE
3809 ,ADJUST.OPENING_WDV) * NVL(ADJUST.RATE
3810 ,0) / 100;
3811 V_CLOSING_BALANCE := NVL(V_PREV_CLOSING_BALANCE
3812 ,ADJUST.OPENING_WDV) - NVL(V_DEPRECIATION
3813 ,0);
3814 UPDATE
3815 JAI_FA_AST_BLOCKS
3816 SET
3817 OPENING_WDV = NVL(V_PREV_CLOSING_BALANCE
3818 ,ADJUST.OPENING_WDV)
3819 WHERE START_DATE = ADJUST.START_DATE
3820 AND BLOCK_ID = ADJUST.BLOCK_ID;
3821 IF ADJUST.CLOSING_WDV IS NOT NULL THEN
3822 UPDATE
3823 JAI_FA_DEP_BLOCKS
3824 SET
3825 DEPN_OF_ASSETS = V_DEPRECIATION
3826 WHERE BLOCK_ID = ADJUST.BLOCK_ID
3827 AND SLNO = 0;
3828 UPDATE
3829 JAI_FA_AST_BLOCKS
3830 SET
3831 CLOSING_WDV = NVL(V_CLOSING_BALANCE
3832 ,0)
3833 ,YEAR_ENDED = V_YEAR_END
3834 WHERE BLOCK_ID = ADJUST.BLOCK_ID;
3835 END IF;
3836 V_PREV_CLOSING_BALANCE := V_CLOSING_BALANCE;
3837 END LOOP;
3838 END LOOP;
3839 END IF;
3840 K := 0;
3841 C_BLOCK_ID := TEMP_REC.BLOCKID;
3842 END LOOP;
3843 UPDATE
3844 JAI_FA_AST_BLOCKS
3845 SET
3846 OPENING_WDV_ADJ = NULL
3847 ,DEPN_ADJ = NULL
3848 WHERE START_DATE BETWEEN P_YEAR_START
3849 AND P_YEAR_END
3850 AND OPENING_WDV_ADJ is not null
3851 OR DEPN_ADJ is not null
3852 AND BOOK_TYPE_CODE = P_BOOK_NAME;
3853 DELETE FROM JAI_FA_EXEMPTIONS;
3854 COMMIT;
3855 END RUN_DEP_MASS;
3856
3857 FUNCTION AFTERREPORT RETURN BOOLEAN IS
3858 BEGIN
3859 DELETE FROM JAI_FA_DEP_BLOCKS_T;
3860 COMMIT;
3861 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
3862 RETURN (TRUE);
3863 END AFTERREPORT;
3864
3865 FUNCTION CF_9FORMULA(BLOCK_ID IN NUMBER) RETURN NUMBER IS
3866 CURSOR ASSET_COST(CP_STATUS IN FA_RETIREMENTS.STATUS%TYPE) IS
3867 SELECT
3868 NVL(SUM(SLNO)
3869 ,0) COST
3870 FROM
3871 JAI_FA_DEP_BLOCKS_T
3872 WHERE ASSET_ID Not In (
3873 SELECT
3874 ASSET_ID
3875 FROM
3876 FA_RETIREMENTS
3877 WHERE BOOK_TYPE_CODE = BOOK_NAME
3878 AND STATUS = CP_STATUS
3879 AND DATE_RETIRED between START_DATE
3880 AND END_DATE_N )
3881 AND BLOCK_ID = BLOCK_ID
3882 GROUP BY
3883 BLOCK_ID;
3884 V_ASSET_COST NUMBER;
3885 BEGIN
3886 OPEN ASSET_COST('PROCESSED');
3887 FETCH ASSET_COST
3888 INTO V_ASSET_COST;
3889 CLOSE ASSET_COST;
3890 /*SRW.MESSAGE(1275
3891 ,'CF_9 book_name -> ' || BOOK_NAME || ', Block_id -> ' || BLOCK_ID || ', START_date -> ' || START_DATE || ', END_date -> ' || END_DATE || ', v_asset_cost -> ' || V_ASSET_COST)*/NULL;
3892 RETURN (V_ASSET_COST);
3893 END CF_9FORMULA;
3894
3895 FUNCTION TOTALDEPRN(P_BOOK_NAME IN JAI_FA_AST_BLOCKS.BOOK_TYPE_CODE%TYPE
3896 ,P_YEAR_START IN JAI_FA_AST_YEARS.YEAR_START%TYPE
3897 ,P_YEAR_END IN JAI_FA_AST_YEARS.YEAR_END%TYPE
3898 ,P_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE
3899 ,P_NO_ASSETS OUT NOCOPY VARCHAR2) RETURN NUMBER IS
3900 LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
3901 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
3902 LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
3903 LV_STATUS FA_RETIREMENTS.STATUS%TYPE;
3904 CURSOR CUR_GET_VALID_ASSETS IS
3905 SELECT
3906 '1'
3907 FROM
3911 WHERE JBA.ASSET_ID = FAB.ASSET_ID
3908 JAI_FA_AST_BLOCK_DTLS JBA,
3909 FA_BOOKS FAB,
3910 JAI_FA_AST_BLOCKS JABOA
3912 AND ( ( JBA.ASSET_TYPE = LV_CAPITALIZED
3913 AND FAB.CAPITALIZE_FLAG = LV_FLAG )
3914 OR JBA.ASSET_TYPE = LV_EXPENSED )
3915 AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
3916 AND NVL(JABOA.YEAR_ENDED
3917 ,P_YEAR_END)
3918 AND FAB.DATE_INEFFECTIVE IS NULL
3919 AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
3920 AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
3921 AND JBA.BLOCK_ID = JABOA.BLOCK_ID
3922 AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
3923 AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
3924 AND JABOA.TYPE = (
3925 SELECT
3926 TYPE
3927 FROM
3928 JAI_FA_AST_BLOCKS
3929 WHERE BLOCK_ID = P_BLOCK_ID )
3930 AND ( JABOA.START_DATE <= NVL(P_YEAR_START
3931 ,JABOA.START_DATE)
3932 OR JABOA.START_DATE BETWEEN NVL(P_YEAR_START
3933 ,JABOA.START_DATE)
3934 AND NVL(P_YEAR_END
3935 ,JABOA.YEAR_ENDED) );
3936 CURSOR CUR_GET_ST_OP_BAL(CP_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) IS
3937 SELECT
3938 OPENING_WDV
3939 FROM
3940 JAI_FA_AST_BLOCKS JABOA
3941 WHERE JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
3942 AND JABOA.TYPE = (
3943 SELECT
3944 TYPE
3945 FROM
3946 JAI_FA_AST_BLOCKS
3947 WHERE BLOCK_ID = CP_BLOCK_ID )
3948 ORDER BY
3949 START_DATE ASC;
3950 LV_EXISTS VARCHAR2(1);
3951 LN_OPENING_WDV JAI_FA_AST_BLOCKS.OPENING_WDV%TYPE;
3952 CURSOR DEPR_CUR1 IS
3953 SELECT
3954 A.ASSET_ID,
3955 A.COST,
3956 A.TRANSACTION_HEADER_ID_IN,
3957 A.DATE_EFFECTIVE
3958 FROM
3959 FA_BOOKS A,
3960 JAI_FA_AST_BLOCKS B,
3961 JAI_FA_AST_BLOCK_DTLS C,
3962 JAI_FA_AST_PERIOD_RATES D
3963 WHERE A.ASSET_ID = C.ASSET_ID
3964 AND ( ( C.ASSET_TYPE = LV_CAPITALIZED
3965 AND A.CAPITALIZE_FLAG = LV_FLAG )
3966 OR C.ASSET_TYPE = LV_EXPENSED )
3967 AND B.BLOCK_ID = P_BLOCK_ID
3968 AND B.BLOCK_ID = C.BLOCK_ID
3969 AND A.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
3970 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
3971 AND C.DATE_OF_ACQUISITION between D.START_DATE
3972 AND D.END_DATE
3973 AND B.START_DATE between NVL(P_YEAR_START
3974 ,B.START_DATE)
3975 AND NVL(P_YEAR_END
3976 ,B.YEAR_ENDED)
3977 AND D.START_DATE >= ADD_MONTHS(P_YEAR_START
3978 ,6)
3979 AND D.SLNO = 2
3980 AND A.DATE_INEFFECTIVE is null
3981 AND A.TRANSACTION_HEADER_ID_OUT is null;
3982 CURSOR GETRATE IS
3983 SELECT
3984 NVL(RATE
3985 ,0) RATE,
3986 TYPE
3987 FROM
3988 JAI_FA_AST_BLOCKS
3989 WHERE BLOCK_ID = P_BLOCK_ID;
3990 CURSOR BLOCK_RET_CUR_FIR(P_BLOCK_TYPE IN VARCHAR2) IS
3991 SELECT
3992 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
3993 ,0) SALE
3994 FROM
3995 JAI_FA_AST_BLOCKS A,
3996 FA_ADDITIONS B,
3997 FA_RETIREMENTS C,
3998 JAI_FA_AST_PERIOD_RATES D
3999 WHERE B.CONTEXT = P_CONTEXT_VALUE
4000 AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
4001 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
4002 AND B.OWNED_LEASED = 'OWNED'
4003 AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
4004 AND TO_DATE(B.ATTRIBUTE1
4005 ,'DD-MON-RRRR') between D.START_DATE
4006 AND D.END_DATE
4007 AND D.START_DATE < ADD_MONTHS(P_YEAR_START
4008 ,6)
4009 AND D.SLNO = 1
4010 AND B.ASSET_ID = C.ASSET_ID
4011 AND C.STATUS = 'PROCESSED'
4012 AND A.TYPE = P_BLOCK_TYPE
4013 AND A.BLOCK_ID = P_BLOCK_ID
4014 AND C.DATE_RETIRED BETWEEN P_YEAR_START
4015 AND P_YEAR_END;
4016 CURSOR BLOCK_RET_CUR_SEC(P_BLOCK_TYPE IN VARCHAR2) IS
4017 SELECT
4018 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
4019 ,0) SALE
4020 FROM
4021 JAI_FA_AST_BLOCKS A,
4022 FA_ADDITIONS B,
4023 FA_RETIREMENTS C,
4024 JAI_FA_AST_PERIOD_RATES D
4025 WHERE B.CONTEXT = P_CONTEXT_VALUE
4026 AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
4027 AND A.BOOK_TYPE_CODE = P_BOOK_NAME
4028 AND B.OWNED_LEASED = 'OWNED'
4029 AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
4030 AND TO_DATE(B.ATTRIBUTE1
4031 ,'DD-MON-RRRR') between D.START_DATE
4032 AND D.END_DATE
4033 AND D.START_DATE >= ADD_MONTHS(P_YEAR_START
4034 ,6)
4035 AND D.SLNO = 2
4036 AND B.ASSET_ID = C.ASSET_ID
4037 AND C.STATUS = 'PROCESSED'
4038 AND A.TYPE = P_BLOCK_TYPE
4039 AND A.BLOCK_ID = P_BLOCK_ID
4040 AND C.DATE_RETIRED BETWEEN P_YEAR_START
4041 AND P_YEAR_END;
4042 CURSOR TOTCOST_CUR IS
4043 SELECT
4044 NVL(SUM(SLNO)
4045 ,0) COST
4046 FROM
4047 JAI_FA_DEP_BLOCKS_T
4048 WHERE BLOCK_ID = P_BLOCK_ID;
4049 CURSOR TOTOPBAL_CUR IS
4050 SELECT
4051 NVL(OPENING_WDV
4052 ,0)
4053 FROM
4054 JAI_FA_AST_BLOCKS
4055 WHERE BLOCK_ID = P_BLOCK_ID;
4056 CURSOR ADJ_AMOUNT_CUR IS
4057 SELECT
4058 NVL(OPENING_WDV_ADJ
4059 ,0)
4060 FROM
4061 JAI_FA_AST_BLOCKS
4062 WHERE BLOCK_ID = P_BLOCK_ID;
4063 V_COST NUMBER;
4064 V_TOTAL1 NUMBER;
4068 V_DEPRNBEFORE NUMBER;
4065 V_TOTAL2 NUMBER;
4066 V_DEPRN NUMBER;
4067 V_DEPRNAFTER NUMBER;
4069 V_RATE NUMBER;
4070 V_FIR_DEDUCTION NUMBER;
4071 V_SEC_DEDUCTION NUMBER;
4072 V_DEDUCTION NUMBER;
4073 V_TOTCOST NUMBER;
4074 V_OPBAL NUMBER;
4075 V_TYPE JAI_FA_AST_BLOCKS.TYPE%TYPE;
4076 V_ADJ NUMBER;
4077 LN_TOTAL_BLK_SEC_HF_COST NUMBER := 0;
4078 BEGIN
4079 /*SRW.MESSAGE(1275
4080 ,'1 in totaldeprn function ')*/NULL;
4081 LV_CAPITALIZED := 'CAPITALIZED';
4082 LV_FLAG := 'YES';
4083 LV_EXPENSED := 'EXPENSED';
4084 LV_STATUS := 'PROCESSED';
4085 P_NO_ASSETS := 'NO';
4086 V_COST := NULL;
4087 V_TOTAL1 := NULL;
4088 V_TOTAL2 := NULL;
4089 V_DEPRN := NULL;
4090 V_DEPRNAFTER := NULL;
4091 V_DEPRNBEFORE := NULL;
4092 V_RATE := NULL;
4093 V_DEDUCTION := NULL;
4094 V_TOTCOST := NULL;
4095 V_OPBAL := NULL;
4096 V_ADJ := NULL;
4097 OPEN CUR_GET_VALID_ASSETS;
4098 FETCH CUR_GET_VALID_ASSETS
4099 INTO LV_EXISTS;
4100 OPEN CUR_GET_ST_OP_BAL(CP_BLOCK_ID => P_BLOCK_ID);
4101 FETCH CUR_GET_ST_OP_BAL
4102 INTO LN_OPENING_WDV;
4103 CLOSE CUR_GET_ST_OP_BAL;
4104 IF CUR_GET_VALID_ASSETS%NOTFOUND AND LN_OPENING_WDV = 0 THEN
4105 /*SRW.MESSAGE(1275
4106 ,'5.6.2 opening wdv for the first period record for the type and book name is 0')*/NULL;
4107 V_DEPRN := 0;
4108 P_NO_ASSETS := 'YES';
4109 ELSE
4110 /*SRW.MESSAGE(1275
4111 ,'5.6.3 Some assets are active, so depreciation calculations can proceed ')*/NULL;
4112 OPEN GETRATE;
4113 FETCH GETRATE
4114 INTO V_RATE,V_TYPE;
4115 CLOSE GETRATE;
4116 OPEN BLOCK_RET_CUR_FIR(V_TYPE);
4117 FETCH BLOCK_RET_CUR_FIR
4118 INTO V_FIR_DEDUCTION;
4119 CLOSE BLOCK_RET_CUR_FIR;
4120 OPEN BLOCK_RET_CUR_SEC(V_TYPE);
4121 FETCH BLOCK_RET_CUR_SEC
4122 INTO V_SEC_DEDUCTION;
4123 CLOSE BLOCK_RET_CUR_SEC;
4124 OPEN TOTCOST_CUR;
4125 FETCH TOTCOST_CUR
4126 INTO V_TOTCOST;
4127 CLOSE TOTCOST_CUR;
4128 OPEN TOTOPBAL_CUR;
4129 FETCH TOTOPBAL_CUR
4130 INTO V_OPBAL;
4131 CLOSE TOTOPBAL_CUR;
4132 OPEN ADJ_AMOUNT_CUR;
4133 FETCH ADJ_AMOUNT_CUR
4134 INTO V_ADJ;
4135 CLOSE ADJ_AMOUNT_CUR;
4136 /*SRW.MESSAGE(1275
4137 ,'5.6.4 values V_Rate -> ' || V_RATE || 'v_type -> ' || V_TYPE || ' v_deduction -> ' || V_DEDUCTION || ' v_totcost-> ' || V_TOTCOST || 'v_opbal -> ' || V_OPBAL || 'v_adj -> ' || V_ADJ)*/NULL;
4138 FOR rec_depr_cur IN DEPR_CUR1 LOOP
4139 LN_TOTAL_BLK_SEC_HF_COST := CALCULATE_ACTUAL_COST(P_TRANSACTION_HEADER_ID_IN => REC_DEPR_CUR.TRANSACTION_HEADER_ID_IN
4140 ,P_DATE_EFFECTIVE => REC_DEPR_CUR.DATE_EFFECTIVE
4141 ,P_BOOK_NAME => P_BOOK_NAME
4142 ,P_ASSET_ID => REC_DEPR_CUR.ASSET_ID
4143 ,P_COST => NVL(REC_DEPR_CUR.COST
4144 ,0)) + LN_TOTAL_BLK_SEC_HF_COST;
4145 END LOOP;
4146 /*SRW.MESSAGE(1275
4147 ,'5.6.7 ln_total_blk_sec_hf_cost ->' || LN_TOTAL_BLK_SEC_HF_COST)*/NULL;
4148 V_TOTAL2 := NVL(V_OPBAL
4149 ,0) + NVL(V_ADJ
4150 ,0) + NVL(V_TOTCOST
4151 ,0) - NVL(LN_TOTAL_BLK_SEC_HF_COST
4152 ,0) - NVL(V_FIR_DEDUCTION
4153 ,0);
4154 IF V_TOTAL2 < 0 THEN
4155 V_DEPRNBEFORE := 0;
4156 LN_TOTAL_BLK_SEC_HF_COST := NVL(LN_TOTAL_BLK_SEC_HF_COST
4157 ,0) + V_TOTAL2;
4158 ELSE
4159 V_DEPRNBEFORE := V_TOTAL2 * (V_RATE / 100);
4160 END IF;
4161 LN_TOTAL_BLK_SEC_HF_COST := NVL(LN_TOTAL_BLK_SEC_HF_COST
4162 ,0) - NVL(V_SEC_DEDUCTION
4163 ,0);
4164 V_DEPRNAFTER := NVL(LN_TOTAL_BLK_SEC_HF_COST
4165 ,0) * (V_RATE / 100) * 0.5;
4166 V_DEPRN := V_DEPRNBEFORE + V_DEPRNAFTER;
4167 IF V_DEPRN <= 0 THEN
4168 V_DEPRN := 0;
4169 END IF;
4170 NULL;
4171 END IF;
4172 CLOSE CUR_GET_VALID_ASSETS;
4173 RETURN (V_DEPRN);
4174 END TOTALDEPRN;
4175
4176 PROCEDURE DEL_ATTR1_NULL IS
4177 CURSOR GET_NULL_ATTR1 IS
4178 SELECT
4179 COUNT(*)
4180 FROM
4181 FA_ADDITIONS
4182 WHERE LENGTH(ATTRIBUTE1) = 1
4183 AND CONTEXT = P_CONTEXT_VALUE;
4184 V_COUNT NUMBER;
4185 CURSOR GET_VAL_ATTR1 IS
4186 SELECT
4187 count(*)
4188 FROM
4189 FA_ADDITIONS
4190 WHERE ATTRIBUTE1 IS NOT NULL
4191 AND LENGTH(ATTRIBUTE1) <> 0
4192 AND CONTEXT = P_CONTEXT_VALUE;
4193 V_COUNT_ATTR NUMBER;
4194 BEGIN
4195 OPEN GET_NULL_ATTR1;
4196 FETCH GET_NULL_ATTR1
4197 INTO V_COUNT;
4198 CLOSE GET_NULL_ATTR1;
4199 OPEN GET_VAL_ATTR1;
4200 FETCH GET_VAL_ATTR1
4201 INTO V_COUNT_ATTR;
4202 CLOSE GET_VAL_ATTR1;
4203 IF V_COUNT > 0 THEN
4204 UPDATE
4205 FA_ADDITIONS_B
4206 SET
4207 ATTRIBUTE1 = NULL
4208 WHERE LENGTH(ATTRIBUTE1) = 1
4209 AND CONTEXT = P_CONTEXT_VALUE;
4210 COMMIT;
4211 END IF;
4212 IF V_COUNT_ATTR > 0 THEN
4213 UPDATE
4214 FA_ADDITIONS_B
4215 SET
4216 ATTRIBUTE1 = TO_CHAR(TO_DATE(ATTRIBUTE1
4220 AND LENGTH(ATTRIBUTE1) <> 1
4217 ,'DD-MON-RRRR')
4218 ,'DD-MON-RRRR')
4219 WHERE ATTRIBUTE1 IS NOT NULL
4221 AND CONTEXT = P_CONTEXT_VALUE;
4222 COMMIT;
4223 END IF;
4224 EXCEPTION
4225 WHEN OTHERS THEN
4226 NULL;
4227 END DEL_ATTR1_NULL;
4228
4229 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
4230 BEGIN
4231 RETURN (TRUE);
4232 END BEFOREPFORM;
4233
4234 PROCEDURE UPD_FA_ADD_CONTXT IS
4235 CURSOR GET_ATTR_CONTEXT IS
4236 SELECT
4237 CONTEXT
4238 FROM
4239 FA_ADDITIONS_B
4240 WHERE ATTRIBUTE1 is NOT NULL
4241 AND ATTRIBUTE2 IN (
4242 SELECT
4243 DISTINCT
4244 TO_CHAR(BLOCK_ID)
4245 FROM
4246 JAI_FA_AST_BLOCKS );
4247 GET_ATTR_CONTEXT_REC GET_ATTR_CONTEXT%ROWTYPE;
4248 BEGIN
4249 OPEN GET_ATTR_CONTEXT;
4250 FETCH GET_ATTR_CONTEXT
4251 INTO GET_ATTR_CONTEXT_REC;
4252 IF GET_ATTR_CONTEXT%FOUND THEN
4253 UPDATE
4254 FA_ADDITIONS_B
4255 SET
4256 CONTEXT = 'India B Of Assets'
4257 WHERE ATTRIBUTE1 IS NOT NULL
4258 AND ATTRIBUTE2 IN (
4259 SELECT
4260 DISTINCT
4261 TO_CHAR(BLOCK_ID)
4262 FROM
4263 JAI_FA_AST_BLOCKS );
4264 COMMIT;
4265 END IF;
4266 CLOSE GET_ATTR_CONTEXT;
4267 EXCEPTION
4268 WHEN NO_DATA_FOUND THEN
4269 /*SRW.MESSAGE(2000
4270 ,'SORRY NO DATA')*/NULL;
4271 WHEN OTHERS THEN
4272 NULL;
4273 END UPD_FA_ADD_CONTXT;
4274
4275 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4276 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
4277 SELECT
4278 CONCURRENT_PROGRAM_ID,
4279 NVL(ENABLE_TRACE
4280 ,'N')
4281 FROM
4282 FND_CONCURRENT_REQUESTS
4283 WHERE REQUEST_ID = P_REQUEST_ID;
4284 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
4285 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
4286 BEGIN
4287 /*SRW.MESSAGE(1275
4288 ,'Report Version is 120.5 Last modified date is 13/10/2005')*/NULL;
4289 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
4290 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
4291 BEGIN
4292 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
4293 FETCH C_PROGRAM_ID
4294 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
4295 CLOSE C_PROGRAM_ID;
4296 /*SRW.MESSAGE(1275
4297 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
4298 IF V_ENABLE_TRACE = 'Y' THEN
4299 EXECUTE IMMEDIATE
4300 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
4301 END IF;
4302 EXCEPTION
4303 WHEN OTHERS THEN
4304 /*SRW.MESSAGE(1275
4305 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
4306 END;
4307 P_CONTEXT_VALUE := 'India B Of Assets';
4308 RETURN (TRUE);
4309 END BEFOREREPORT;
4310
4311 FUNCTION CF_8FORMULA(COST IN NUMBER
4312 ,TRANSACTION_HEADER_ID_IN IN NUMBER
4313 ,DATE_EFFECTIVE IN DATE
4314 ,ASSET_ID IN NUMBER) RETURN NUMBER IS
4315 CURSOR CUR_GET_ADDITION_COST(CP_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE,CP_DATE_EFFECTIVE IN FA_BOOKS.DATE_EFFECTIVE%TYPE,CP_BOOK_NAME IN FA_BOOKS.BOOK_TYPE_CODE%TYPE,CP_ASSET_ID IN FA_BOOKS.ASSET_ID%TYPE) IS
4316 SELECT
4317 TRANSACTION_HEADER_ID_IN,
4318 DATE_EFFECTIVE,
4319 FAB.COST
4320 FROM
4321 FA_BOOKS FAB
4322 WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
4323 AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
4324 AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
4325 AND FAB.ASSET_ID = CP_ASSET_ID
4326 AND FAB.RETIREMENT_ID IS NOT NULL;
4327 REC_CUR_GET_ADDITION_COST CUR_GET_ADDITION_COST%ROWTYPE;
4328 LN_ADDITION_COST FA_BOOKS.COST%TYPE;
4329 LN_TRANSACTION_HEADER_ID_IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE;
4330 LD_DATE_EFFECTIVE FA_BOOKS.DATE_EFFECTIVE%TYPE;
4331 BEGIN
4332 /*SRW.MESSAGE(1275
4333 ,'in cf8 formula column processing')*/NULL;
4334 IF NVL(COST
4335 ,0) <> 0 THEN
4336 LN_ADDITION_COST := COST;
4337 LN_TRANSACTION_HEADER_ID_IN := TRANSACTION_HEADER_ID_IN;
4338 LD_DATE_EFFECTIVE := DATE_EFFECTIVE;
4339 LOOP
4340 OPEN CUR_GET_ADDITION_COST(CP_TRANSACTION_HEADER_ID_IN => LN_TRANSACTION_HEADER_ID_IN,CP_DATE_EFFECTIVE => LD_DATE_EFFECTIVE,CP_BOOK_NAME => BOOK_NAME,CP_ASSET_ID => ASSET_ID);
4341 FETCH CUR_GET_ADDITION_COST
4342 INTO REC_CUR_GET_ADDITION_COST;
4343 IF CUR_GET_ADDITION_COST%NOTFOUND THEN
4344 CLOSE CUR_GET_ADDITION_COST;
4345 EXIT;
4346 ELSE
4347 LN_TRANSACTION_HEADER_ID_IN := REC_CUR_GET_ADDITION_COST.TRANSACTION_HEADER_ID_IN;
4348 LD_DATE_EFFECTIVE := REC_CUR_GET_ADDITION_COST.DATE_EFFECTIVE;
4349 LN_ADDITION_COST := REC_CUR_GET_ADDITION_COST.COST;
4350 CLOSE CUR_GET_ADDITION_COST;
4351 END IF;
4352 END LOOP;
4353 /*SRW.MESSAGE(1275
4354 ,'value of ln_addition_cost is ' || LN_ADDITION_COST)*/NULL;
4355 RETURN (LN_ADDITION_COST);
4356 ELSE
4357 RETURN 0;
4358 END IF;
4359 END CF_8FORMULA;
4360
4361 FUNCTION GET_TRANSACTION_HEADER_ID(P_BOOK_NAME IN FA_BOOKS.BOOK_TYPE_CODE%TYPE
4362 ,P_ASSET_ID IN FA_BOOKS.ASSET_ID%TYPE
4366 FAB.TRANSACTION_HEADER_ID_IN,
4363 ,P_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) RETURN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE IS
4364 CURSOR CUR_GET_ACTIVE_TRANSACTION_ID IS
4365 SELECT
4367 FAB.DATE_EFFECTIVE,
4368 FAB.COST
4369 FROM
4370 FA_BOOKS FAB,
4371 JAI_FA_AST_BLOCK_DTLS JBA
4372 WHERE FAB.ASSET_ID = JBA.ASSET_ID
4373 AND JBA.BLOCK_ID = P_BLOCK_ID
4374 AND JBA.ASSET_ID = P_ASSET_ID
4375 AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
4376 AND FAB.DATE_INEFFECTIVE IS NULL
4377 AND FAB.BOOK_TYPE_CODE = P_BOOK_NAME;
4378 CURSOR CUR_GET_TRANSACTION_ID_IN(CP_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE,CP_DATE_EFFECTIVE IN FA_BOOKS.DATE_EFFECTIVE%TYPE,CP_BOOK_NAME IN FA_BOOKS.BOOK_TYPE_CODE%TYPE,CP_ASSET_ID IN FA_BOOKS.ASSET_ID%TYPE) IS
4379 SELECT
4380 TRANSACTION_HEADER_ID_IN,
4381 DATE_EFFECTIVE
4382 FROM
4383 FA_BOOKS FAB
4384 WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
4385 AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
4386 AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
4387 AND FAB.ASSET_ID = CP_ASSET_ID
4388 AND FAB.RETIREMENT_ID IS NOT NULL;
4389 REC_CUR_GET_ACT_TRANS_ID CUR_GET_ACTIVE_TRANSACTION_ID%ROWTYPE;
4390 REC_CUR_GET_TRANS_ID_IN CUR_GET_TRANSACTION_ID_IN%ROWTYPE;
4391 LN_TRANSACTION_HEADER_ID_IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE;
4392 LD_DATE_EFFECTIVE FA_BOOKS.DATE_EFFECTIVE%TYPE;
4393 BEGIN
4394 OPEN CUR_GET_ACTIVE_TRANSACTION_ID;
4395 FETCH CUR_GET_ACTIVE_TRANSACTION_ID
4396 INTO REC_CUR_GET_ACT_TRANS_ID;
4397 IF CUR_GET_ACTIVE_TRANSACTION_ID%NOTFOUND THEN
4398 CLOSE CUR_GET_ACTIVE_TRANSACTION_ID;
4399 RETURN (NULL);
4400 END IF;
4401 CLOSE CUR_GET_ACTIVE_TRANSACTION_ID;
4402 LN_TRANSACTION_HEADER_ID_IN := REC_CUR_GET_ACT_TRANS_ID.TRANSACTION_HEADER_ID_IN;
4403 LD_DATE_EFFECTIVE := REC_CUR_GET_ACT_TRANS_ID.DATE_EFFECTIVE;
4404 LOOP
4405 /*SRW.MESSAGE(1275
4406 ,'get_transaction_header_id 1 in loop 2 - transaction_header_id_in is ' || LN_TRANSACTION_HEADER_ID_IN)*/NULL;
4407 OPEN CUR_GET_TRANSACTION_ID_IN(CP_TRANSACTION_HEADER_ID_IN => LN_TRANSACTION_HEADER_ID_IN,CP_DATE_EFFECTIVE => LD_DATE_EFFECTIVE,CP_BOOK_NAME => P_BOOK_NAME,CP_ASSET_ID => P_ASSET_ID);
4408 FETCH CUR_GET_TRANSACTION_ID_IN
4409 INTO REC_CUR_GET_TRANS_ID_IN;
4410 IF CUR_GET_TRANSACTION_ID_IN%NOTFOUND THEN
4411 /*SRW.MESSAGE(1275
4412 ,'get_transaction_header_id - 2 in loop 3 - abt to exit transaction_header_id_in is ' || LN_TRANSACTION_HEADER_ID_IN)*/NULL;
4413 CLOSE CUR_GET_TRANSACTION_ID_IN;
4414 EXIT;
4415 ELSE
4416 /*SRW.MESSAGE(1275
4417 ,' get_transaction_header_id-3 loop 4 in else part, transaction_header_id ' || REC_CUR_GET_TRANS_ID_IN.TRANSACTION_HEADER_ID_IN)*/NULL;
4418 LN_TRANSACTION_HEADER_ID_IN := REC_CUR_GET_TRANS_ID_IN.TRANSACTION_HEADER_ID_IN;
4419 LD_DATE_EFFECTIVE := REC_CUR_GET_TRANS_ID_IN.DATE_EFFECTIVE;
4420 CLOSE CUR_GET_TRANSACTION_ID_IN;
4421 END IF;
4422 END LOOP;
4423 RETURN (LN_TRANSACTION_HEADER_ID_IN);
4424 END GET_TRANSACTION_HEADER_ID;
4425
4426 FUNCTION CF_CLOSING_WDV_TOTFORMULA(CF_6 IN NUMBER
4427 ,CF_2 IN NUMBER) RETURN NUMBER IS
4428 LN_CLOSING_WDV_TOTAL JAI_FA_AST_BLOCKS.CLOSING_WDV%TYPE;
4429 BEGIN
4430 IF NVL(CF_6
4431 ,0) = 0 THEN
4432 LN_CLOSING_WDV_TOTAL := 0;
4433 ELSE
4434 LN_CLOSING_WDV_TOTAL := NVL(CF_2 - CF_6
4435 ,0);
4436 END IF;
4437 IF LN_CLOSING_WDV_TOTAL < 0 THEN
4438 LN_CLOSING_WDV_TOTAL := 0;
4439 END IF;
4440 RETURN (LN_CLOSING_WDV_TOTAL);
4441 END CF_CLOSING_WDV_TOTFORMULA;
4442
4443 FUNCTION CALCULATE_ACTUAL_COST(P_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE
4444 ,P_DATE_EFFECTIVE IN FA_BOOKS.DATE_EFFECTIVE%TYPE
4445 ,P_BOOK_NAME IN FA_BOOKS.BOOK_TYPE_CODE%TYPE
4446 ,P_ASSET_ID IN FA_BOOKS.ASSET_ID%TYPE
4447 ,P_COST IN FA_BOOKS.COST%TYPE) RETURN NUMBER IS
4448 CURSOR CUR_GET_ADDITION_COST(CP_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE,CP_DATE_EFFECTIVE IN FA_BOOKS.DATE_EFFECTIVE%TYPE,CP_BOOK_NAME IN FA_BOOKS.BOOK_TYPE_CODE%TYPE,CP_ASSET_ID IN FA_BOOKS.ASSET_ID%TYPE) IS
4449 SELECT
4450 TRANSACTION_HEADER_ID_IN,
4451 DATE_EFFECTIVE,
4452 FAB.COST
4453 FROM
4454 FA_BOOKS FAB
4455 WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
4456 AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
4457 AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
4458 AND FAB.ASSET_ID = CP_ASSET_ID
4459 AND FAB.RETIREMENT_ID IS NOT NULL;
4460 REC_CUR_GET_ADDITION_COST CUR_GET_ADDITION_COST%ROWTYPE;
4461 LN_ADDITION_COST FA_BOOKS.COST%TYPE;
4462 LN_TRANSACTION_HEADER_ID_IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE;
4463 LD_DATE_EFFECTIVE FA_BOOKS.DATE_EFFECTIVE%TYPE;
4464 BEGIN
4465 /*SRW.MESSAGE(1275
4466 ,'in function calculate_actual_cost for actual cost processing')*/NULL;
4467 LN_ADDITION_COST := P_COST;
4468 LN_TRANSACTION_HEADER_ID_IN := P_TRANSACTION_HEADER_ID_IN;
4469 LD_DATE_EFFECTIVE := P_DATE_EFFECTIVE;
4470 LOOP
4471 OPEN CUR_GET_ADDITION_COST(CP_TRANSACTION_HEADER_ID_IN => LN_TRANSACTION_HEADER_ID_IN,CP_DATE_EFFECTIVE => LD_DATE_EFFECTIVE,CP_BOOK_NAME => P_BOOK_NAME,CP_ASSET_ID => P_ASSET_ID);
4472 FETCH CUR_GET_ADDITION_COST
4473 INTO REC_CUR_GET_ADDITION_COST;
4474 IF CUR_GET_ADDITION_COST%NOTFOUND THEN
4475 CLOSE CUR_GET_ADDITION_COST;
4476 EXIT;
4477 ELSE
4481 CLOSE CUR_GET_ADDITION_COST;
4478 LN_TRANSACTION_HEADER_ID_IN := REC_CUR_GET_ADDITION_COST.TRANSACTION_HEADER_ID_IN;
4479 LD_DATE_EFFECTIVE := REC_CUR_GET_ADDITION_COST.DATE_EFFECTIVE;
4480 LN_ADDITION_COST := REC_CUR_GET_ADDITION_COST.COST;
4482 END IF;
4483 END LOOP;
4484 /*SRW.MESSAGE(1275
4485 ,'Asset ->' || P_ASSET_ID || ' Book_name ' || P_BOOK_NAME || 'value of ln_addition_cost is ' || LN_ADDITION_COST)*/NULL;
4486 RETURN (LN_ADDITION_COST);
4487 END CALCULATE_ACTUAL_COST;
4488
4489 END JA_JAINYEDE_XMLP_PKG;
4490
4491