1 PACKAGE BODY JA_JAINASST_XMLP_PKG AS
2 /* $Header: JAINASSTB.pls 120.1 2007/12/25 16:13:14 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 RETURN (N);
12 END CF_1FORMULA;
13
14 FUNCTION CF_3FORMULA(OP_TOT IN NUMBER
15 ,COS_TOT IN NUMBER
16 ,CS_4 IN NUMBER) RETURN NUMBER IS
17 N NUMBER;
18 BEGIN
19 N := NVL(OP_TOT
20 ,0) + NVL(COS_TOT
21 ,0) - NVL(CS_4
22 ,0);
23 IF N <= 0 THEN
24 N := 0;
25 END IF;
26 RETURN (N);
27 END CF_3FORMULA;
28
29 FUNCTION CF_4FORMULA(BLOCK_ID IN NUMBER
30 ,TYPE IN VARCHAR2
31 ,RATE IN NUMBER) RETURN NUMBER IS
32 V_TYPE JAI_FA_AST_BLOCKS.TYPE%TYPE;
33 V_RATE NUMBER;
34 V_SALE NUMBER;
35 V_BLOCK_ID NUMBER;
36 V_BLOCK_ID1 NUMBER;
37 CURSOR TYPE_RATE_CUR IS
38 SELECT
39 TYPE,
40 RATE
41 FROM
42 JAI_FA_AST_BLOCKS
43 WHERE BLOCK_ID = CF_4FORMULA.BLOCK_ID
44 AND BOOK_TYPE_CODE = BOOK_NAME;
45 CURSOR BLOCK_ID_CUR_RET IS
46 SELECT
47 BLOCK_ID
48 FROM
49 JAI_FA_AST_BLOCKS
50 WHERE TYPE = CF_4FORMULA.TYPE
51 AND RATE = CF_4FORMULA.RATE
52 AND START_DATE < JA_JAINASST_XMLP_PKG.START_DATE
53 AND BOOK_TYPE_CODE = BOOK_NAME;
54 CURSOR SALE_CUR(CP_OWNED_LEASED IN FA_ADDITIONS.OWNED_LEASED%TYPE,CP_STATUS IN FA_RETIREMENTS.STATUS%TYPE) IS
55 SELECT
56 NVL(SUM(FAR.PROCEEDS_OF_SALE - FAR.COST_OF_REMOVAL)
57 ,0) SALE
58 FROM
59 JAI_FA_AST_BLOCK_DTLS JBA,
60 FA_ADDITIONS FA,
61 JAI_FA_AST_BLOCKS JABLOA,
62 FA_RETIREMENTS FAR
63 WHERE FA.OWNED_LEASED = CP_OWNED_LEASED
64 AND FA.ASSET_ID = JBA.ASSET_ID
65 AND JBA.BLOCK_ID = JABLOA.BLOCK_ID
66 AND FAR.BOOK_TYPE_CODE = JABLOA.BOOK_TYPE_CODE
67 AND JABLOA.BOOK_TYPE_CODE = BOOK_NAME
68 AND JABLOA.TYPE = CF_4FORMULA.TYPE
69 AND JABLOA.RATE = CF_4FORMULA.RATE
70 AND JBA.ASSET_ID = FAR.ASSET_ID
71 AND FAR.STATUS = CP_STATUS
72 AND FAR.DATE_RETIRED between JA_JAINASST_XMLP_PKG.START_DATE
73 AND JA_JAINASST_XMLP_PKG.END_DATE;
74 BEGIN
75 OPEN TYPE_RATE_CUR;
76 FETCH TYPE_RATE_CUR
77 INTO V_TYPE,V_RATE;
78 CLOSE TYPE_RATE_CUR;
79 OPEN BLOCK_ID_CUR_RET;
80 FETCH BLOCK_ID_CUR_RET
81 INTO V_BLOCK_ID;
82 CLOSE BLOCK_ID_CUR_RET;
83 OPEN SALE_CUR('OWNED','PROCESSED');
84 FETCH SALE_CUR
85 INTO V_SALE;
86 CLOSE SALE_CUR;
87 IF NVL(V_SALE
88 ,0) < 0 THEN
89 V_SALE := 0;
90 END IF;
91 RETURN (V_SALE);
92 END CF_4FORMULA;
93
94 FUNCTION CF_5FORMULA(ASSET_ID IN NUMBER
95 ,BLOCK_ID1 IN NUMBER) RETURN NUMBER IS
96 CURSOR DEPN_OF_ASSET_CUR IS
97 SELECT
98 DEPN_OF_ASSETS
99 FROM
100 JAI_FA_DEP_BLOCKS
101 WHERE UNPLANNED_DEPN = ASSET_ID
102 AND BLOCK_ID = BLOCK_ID1;
103 V_DEPN_OF_ASSET NUMBER;
104 BEGIN
105 OPEN DEPN_OF_ASSET_CUR;
106 FETCH DEPN_OF_ASSET_CUR
107 INTO V_DEPN_OF_ASSET;
108 CLOSE DEPN_OF_ASSET_CUR;
109 RETURN (V_DEPN_OF_ASSET);
110 END CF_5FORMULA;
111
112 FUNCTION CF_6FORMULA(BLOCK_ID IN NUMBER
113 ,TYPE IN VARCHAR2
114 ,OP_TOT IN NUMBER
115 ,COS_TOT IN NUMBER
116 ,RATE IN NUMBER) RETURN NUMBER IS
117 LV_ASSEST_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
118 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
119 LV_ASSET_TYPE JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
120 CURSOR CUR_GET_VALID_ASSETS IS
121 SELECT
122 '1'
123 FROM
124 JAI_FA_AST_BLOCK_DTLS JBA,
125 FA_BOOKS FAB,
126 JAI_FA_AST_BLOCKS JABOA
127 WHERE JBA.ASSET_ID = FAB.ASSET_ID
128 AND ( ( JBA.ASSET_TYPE = LV_ASSEST_CAPITALIZED
129 AND FAB.CAPITALIZE_FLAG = LV_FLAG )
130 OR JBA.ASSET_TYPE = LV_ASSET_TYPE )
131 AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
132 AND NVL(JABOA.YEAR_ENDED
133 ,END_DATE)
134 AND FAB.DATE_INEFFECTIVE IS NULL
135 AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
136 AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
137 AND JBA.BLOCK_ID = TO_CHAR(JABOA.BLOCK_ID)
138 AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
139 AND JABOA.BOOK_TYPE_CODE = BOOK_NAME
140 AND JABOA.TYPE = (
141 SELECT
142 TYPE
143 FROM
144 JAI_FA_AST_BLOCKS
145 WHERE BLOCK_ID = CF_6FORMULA.BLOCK_ID )
146 AND ( JABOA.START_DATE <= NVL(JA_JAINASST_XMLP_PKG.START_DATE
147 ,JABOA.START_DATE)
148 OR JABOA.START_DATE BETWEEN NVL(JA_JAINASST_XMLP_PKG.START_DATE
149 ,JABOA.START_DATE)
150 AND NVL(END_DATE
151 ,JABOA.YEAR_ENDED) );
152 CURSOR CUR_GET_ST_OP_BAL(CP_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) IS
153 SELECT
154 OPENING_WDV
155 FROM
156 JAI_FA_AST_BLOCKS JABOA
157 WHERE JABOA.BOOK_TYPE_CODE = BOOK_NAME
158 AND JABOA.TYPE = (
159 SELECT
160 TYPE
161 FROM
162 JAI_FA_AST_BLOCKS
163 WHERE BLOCK_ID = CP_BLOCK_ID )
164 ORDER BY
165 START_DATE ASC;
166 LV_EXISTS VARCHAR2(1);
167 LN_OPENING_WDV JAI_FA_AST_BLOCKS.OPENING_WDV%TYPE;
168 CURSOR DEPR_CUR1 IS
169 SELECT
170 A.ASSET_ID,
171 A.COST,
172 A.TRANSACTION_HEADER_ID_IN,
173 A.DATE_EFFECTIVE
174 FROM
175 FA_BOOKS A,
176 JAI_FA_AST_BLOCKS B,
177 JAI_FA_AST_BLOCK_DTLS C,
178 JAI_FA_AST_PERIOD_RATES D
179 WHERE A.ASSET_ID = C.ASSET_ID
180 AND ( ( C.ASSET_TYPE = LV_ASSEST_CAPITALIZED
181 AND A.CAPITALIZE_FLAG = LV_FLAG )
182 OR C.ASSET_TYPE = LV_ASSET_TYPE )
183 AND B.BLOCK_ID = CF_6FORMULA.BLOCK_ID
184 AND TO_CHAR(B.BLOCK_ID) = C.BLOCK_ID
185 AND A.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
186 AND A.BOOK_TYPE_CODE = BOOK_NAME
187 AND C.DATE_OF_ACQUISITION between D.START_DATE
188 AND D.END_DATE
189 AND B.START_DATE between NVL(JA_JAINASST_XMLP_PKG.START_DATE
190 ,B.START_DATE)
191 AND NVL(END_DATE
192 ,B.YEAR_ENDED)
193 AND D.START_DATE >= ADD_MONTHS(JA_JAINASST_XMLP_PKG.START_DATE,6)
194 AND D.SLNO = 2
195 AND A.DATE_INEFFECTIVE is null
196 AND A.TRANSACTION_HEADER_ID_OUT is null;
197 CURSOR BLOCK_RET_CUR_FIR(P_BLOCK_TYPE IN VARCHAR2) IS
198 SELECT
199 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
200 ,0) SALE
201 FROM
202 JAI_FA_AST_BLOCKS A,
203 FA_ADDITIONS B,
204 FA_RETIREMENTS C,
205 JAI_FA_AST_PERIOD_RATES D
206 WHERE B.CONTEXT = P_CONTEXT_VALUE
207 AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
208 AND A.BOOK_TYPE_CODE = BOOK_NAME
209 AND B.OWNED_LEASED = 'OWNED'
210 AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
211 AND TO_DATE(B.ATTRIBUTE1
212 ,'DD-MON-RRRR') between D.START_DATE
213 AND D.END_DATE
214 AND D.START_DATE < ADD_MONTHS(JA_JAINASST_XMLP_PKG.START_DATE
215 ,6)
216 AND D.SLNO = 1
217 AND B.ASSET_ID = C.ASSET_ID
218 AND C.STATUS = 'PROCESSED'
219 AND A.TYPE = P_BLOCK_TYPE
220 AND A.BLOCK_ID = CF_6FORMULA.BLOCK_ID
221 AND C.DATE_RETIRED BETWEEN JA_JAINASST_XMLP_PKG.START_DATE
222 AND JA_JAINASST_XMLP_PKG.END_DATE;
223 CURSOR BLOCK_RET_CUR_SEC(P_BLOCK_TYPE IN VARCHAR2) IS
224 SELECT
225 NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
226 ,0) SALE
227 FROM
228 JAI_FA_AST_BLOCKS A,
229 FA_ADDITIONS B,
230 FA_RETIREMENTS C,
231 JAI_FA_AST_PERIOD_RATES D
232 WHERE B.CONTEXT = P_CONTEXT_VALUE
233 AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
234 AND A.BOOK_TYPE_CODE = BOOK_NAME
235 AND B.OWNED_LEASED = 'OWNED'
236 AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
237 AND TO_DATE(B.ATTRIBUTE1
238 ,'DD-MON-RRRR') between D.START_DATE
239 AND D.END_DATE
240 AND D.START_DATE >= ADD_MONTHS(JA_JAINASST_XMLP_PKG.START_DATE
241 ,6)
242 AND D.SLNO = 2
243 AND B.ASSET_ID = C.ASSET_ID
244 AND C.STATUS = 'PROCESSED'
245 AND A.TYPE = P_BLOCK_TYPE
246 AND A.BLOCK_ID = CF_6FORMULA.BLOCK_ID
247 AND C.DATE_RETIRED BETWEEN JA_JAINASST_XMLP_PKG.START_DATE
248 AND JA_JAINASST_XMLP_PKG.END_DATE;
249 V_COST NUMBER;
250 V_TOTAL1 NUMBER;
251 V_TOTAL2 NUMBER;
252 V_DEPRN NUMBER;
253 V_DEPRNAFTER NUMBER;
254 V_DEPRNBEFORE NUMBER;
255 LN_TOTAL_SEC_HF_COST NUMBER := 0;
256 V_FIR_DEDUCTION NUMBER;
257 V_SEC_DEDUCTION NUMBER;
258 BEGIN
259 LV_ASSEST_CAPITALIZED := 'CAPITALIZED';
260 LV_FLAG := 'YES';
261 LV_ASSET_TYPE := 'EXPENSED';
262 OPEN CUR_GET_VALID_ASSETS;
263 FETCH CUR_GET_VALID_ASSETS
264 INTO LV_EXISTS;
265 OPEN CUR_GET_ST_OP_BAL(CP_BLOCK_ID => BLOCK_ID);
266 FETCH CUR_GET_ST_OP_BAL
267 INTO LN_OPENING_WDV;
268 CLOSE CUR_GET_ST_OP_BAL;
269 IF CUR_GET_VALID_ASSETS%NOTFOUND AND LN_OPENING_WDV = 0 THEN
270 /*SRW.MESSAGE(1275
271 ,'1.1 opening wdv for the first period record for the type and book name is 0')*/NULL;
272 V_DEPRN := 0;
273 ELSE
274 V_FIR_DEDUCTION := NULL;
275 V_SEC_DEDUCTION := NULL;
276 OPEN BLOCK_RET_CUR_FIR(TYPE);
277 FETCH BLOCK_RET_CUR_FIR
278 INTO V_FIR_DEDUCTION;
279 CLOSE BLOCK_RET_CUR_FIR;
280 OPEN BLOCK_RET_CUR_SEC(TYPE);
281 FETCH BLOCK_RET_CUR_SEC
282 INTO V_SEC_DEDUCTION;
283 CLOSE BLOCK_RET_CUR_SEC;
284 LV_ASSEST_CAPITALIZED := 'CAPITALIZED';
285 LV_FLAG := 'YES';
286 LV_ASSET_TYPE := 'EXPENSED';
287 FOR rec_depr_cur IN DEPR_CUR1 LOOP
288 LN_TOTAL_SEC_HF_COST := CALCULATE_ACTUAL_COST(P_TRANSACTION_HEADER_ID_IN => REC_DEPR_CUR.TRANSACTION_HEADER_ID_IN
289 ,P_DATE_EFFECTIVE => REC_DEPR_CUR.DATE_EFFECTIVE
290 ,P_BOOK_NAME => BOOK_NAME
291 ,P_ASSET_ID => REC_DEPR_CUR.ASSET_ID
292 ,P_COST => NVL(REC_DEPR_CUR.COST
293 ,0)) + LN_TOTAL_SEC_HF_COST;
294 END LOOP;
295 /*SRW.MESSAGE(1275
296 ,'CF_6 1.ln_total_sec_hf_cost ->' || LN_TOTAL_SEC_HF_COST)*/NULL;
297 V_TOTAL2 := NVL(OP_TOT
298 ,0) + NVL(COS_TOT
299 ,0) - NVL(LN_TOTAL_SEC_HF_COST
300 ,0) - NVL(V_FIR_DEDUCTION
301 ,0);
302 V_DEPRNBEFORE := V_TOTAL2 * (RATE / 100);
303 /*SRW.MESSAGE(1275
304 ,'CF_6 2.1 V_Total2 -> ' || V_TOTAL2 || ', v_DeprnBefore -> ' || V_DEPRNBEFORE)*/NULL;
305 IF V_TOTAL2 < 0 THEN
306 V_DEPRNBEFORE := 0;
307 LN_TOTAL_SEC_HF_COST := NVL(LN_TOTAL_SEC_HF_COST
308 ,0) + V_TOTAL2;
309 ELSE
310 V_DEPRNBEFORE := V_TOTAL2 * (RATE / 100);
311 END IF;
312 LN_TOTAL_SEC_HF_COST := NVL(LN_TOTAL_SEC_HF_COST
313 ,0) - NVL(V_SEC_DEDUCTION
314 ,0);
315 V_DEPRNAFTER := NVL(LN_TOTAL_SEC_HF_COST
316 ,0) * (RATE / 100) * 0.5;
317 V_DEPRN := V_DEPRNBEFORE + V_DEPRNAFTER;
318 IF V_DEPRN <= 0 THEN
319 V_DEPRN := 0;
320 END IF;
321 END IF;
322 CLOSE CUR_GET_VALID_ASSETS;
323 RETURN (V_DEPRN);
324 END CF_6FORMULA;
325
326 FUNCTION AFTERPFORM RETURN BOOLEAN IS
327 BEGIN
328 RETURN (TRUE);
329 END AFTERPFORM;
330
331 FUNCTION AFTERREPORT RETURN BOOLEAN IS
332 BEGIN
333 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
334 RETURN (TRUE);
335 END AFTERREPORT;
336
337 FUNCTION CF_8FORMULA(BLOCK_ID IN NUMBER) RETURN NUMBER IS
338 LV_ASSEST_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
339 LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
340 LV_ASSET_TYPE JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
341 CURSOR ASSET_COST IS
342 SELECT
343 NVL(SUM(COST)
344 ,0) COST
345 FROM
346 FA_BOOKS A,
347 JAI_FA_AST_BLOCK_DTLS B
348 WHERE A.ASSET_ID NOT IN (
349 SELECT
350 ASSET_ID
351 FROM
352 FA_RETIREMENTS
353 WHERE BOOK_TYPE_CODE = BOOK_NAME
354 AND STATUS = 'PROCESSED'
355 AND DATE_RETIRED between START_DATE
356 AND END_DATE )
357 AND B.ASSET_ID = A.ASSET_ID
358 AND ( ( B.ASSET_TYPE = LV_ASSEST_CAPITALIZED
359 AND A.CAPITALIZE_FLAG = LV_FLAG )
360 OR B.ASSET_TYPE = LV_ASSET_TYPE )
361 AND B.BLOCK_ID = TO_CHAR(BLOCK_ID)
362 AND A.BOOK_TYPE_CODE = BOOK_NAME
363 AND B.DATE_OF_ACQUISITION between START_DATE
364 AND END_DATE
365 AND A.RETIREMENT_PENDING_FLAG = 'NO'
366 GROUP BY
367 B.BLOCK_ID;
368 V_ASSET_COST NUMBER;
369 BEGIN
370 LV_ASSEST_CAPITALIZED := 'CAPITALIZED';
371 LV_FLAG := 'YES';
372 LV_ASSET_TYPE := 'EXPENSED';
373 OPEN ASSET_COST;
374 FETCH ASSET_COST
375 INTO V_ASSET_COST;
376 CLOSE ASSET_COST;
377 RETURN (V_ASSET_COST);
378 END CF_8FORMULA;
379
380 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
381 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
382 SELECT
383 CONCURRENT_PROGRAM_ID,
384 NVL(ENABLE_TRACE
385 ,'N')
386 FROM
387 FND_CONCURRENT_REQUESTS
388 WHERE REQUEST_ID = P_REQUEST_ID;
389 CURSOR GET_AUDSID IS
390 SELECT
391 A.SID,
392 A.SERIAL#,
393 B.SPID
394 FROM
395 V$SESSION A,
396 V$PROCESS B
397 WHERE AUDSID = USERENV('SESSIONID')
398 AND A.PADDR = B.ADDR;
399 CURSOR GET_DBNAME IS
400 SELECT
401 NAME
402 FROM
403 V$DATABASE;
404 V_AUDSID NUMBER := USERENV('SESSIONID');
405 V_SID NUMBER;
406 V_SERIAL NUMBER;
407 V_SPID VARCHAR2(9);
408 V_DBNAME VARCHAR2(25);
409 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
410 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
411 BEGIN
412 /*SRW.MESSAGE(1275
413 ,'Report Version is 120.4 Last modified date is 13/10/2006')*/NULL;
414 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
415 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
416 BEGIN
417 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
418 FETCH C_PROGRAM_ID
419 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
420 CLOSE C_PROGRAM_ID;
421 /*SRW.MESSAGE(1275
422 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
423 IF V_ENABLE_TRACE = 'Y' THEN
424 OPEN GET_AUDSID;
425 FETCH GET_AUDSID
426 INTO V_SID,V_SERIAL,V_SPID;
427 CLOSE GET_AUDSID;
428 OPEN GET_DBNAME;
429 FETCH GET_DBNAME
430 INTO V_DBNAME;
431 CLOSE GET_DBNAME;
432 /*SRW.MESSAGE(1275
433 ,'TraceFile Name = ' || LOWER(V_DBNAME) || '_ora_' || V_SPID || '.trc')*/NULL;
434 EXECUTE IMMEDIATE
435 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
436 END IF;
437 EXCEPTION
438 WHEN OTHERS THEN
439 /*SRW.MESSAGE(1275
440 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
441 END;
442 P_CONTEXT_VALUE := 'India B Of Assets';
443 LSTART_DATE:=to_char(START_DATE,'DD/MON/YYYY');
444 LEND_DATE:=to_char(END_DATE,'DD/MON/YYYY');
445
446 RETURN (TRUE);
447 END BEFOREREPORT;
448
449 FUNCTION CF_ADDITIONS_COSTFORMULA(COST IN NUMBER
450 ,TRANSACTION_HEADER_ID_IN IN NUMBER
451 ,DATE_EFFECTIVE IN DATE
452 ,ASSET_ID IN NUMBER) RETURN NUMBER IS
453 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
454 SELECT
455 TRANSACTION_HEADER_ID_IN,
456 DATE_EFFECTIVE,
457 FAB.COST
458 FROM
459 FA_BOOKS FAB
460 WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
461 AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
462 AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
463 AND FAB.ASSET_ID = CP_ASSET_ID
464 AND FAB.RETIREMENT_ID IS NOT NULL;
465 REC_CUR_GET_ADDITION_COST CUR_GET_ADDITION_COST%ROWTYPE;
466 LN_ADDITION_COST FA_BOOKS.COST%TYPE;
467 LN_TRANSACTION_HEADER_ID_IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE;
468 LD_DATE_EFFECTIVE FA_BOOKS.DATE_EFFECTIVE%TYPE;
469 BEGIN
470 /*SRW.MESSAGE(1275
471 ,'in cf8 formula column processing')*/NULL;
472 IF NVL(COST
473 ,0) <> 0 THEN
474 LN_ADDITION_COST := COST;
475 LN_TRANSACTION_HEADER_ID_IN := TRANSACTION_HEADER_ID_IN;
476 LD_DATE_EFFECTIVE := DATE_EFFECTIVE;
477 LOOP
478 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);
479 FETCH CUR_GET_ADDITION_COST
480 INTO REC_CUR_GET_ADDITION_COST;
481 IF CUR_GET_ADDITION_COST%NOTFOUND THEN
482 CLOSE CUR_GET_ADDITION_COST;
483 EXIT;
484 ELSE
485 LN_TRANSACTION_HEADER_ID_IN := REC_CUR_GET_ADDITION_COST.TRANSACTION_HEADER_ID_IN;
486 LD_DATE_EFFECTIVE := REC_CUR_GET_ADDITION_COST.DATE_EFFECTIVE;
487 LN_ADDITION_COST := REC_CUR_GET_ADDITION_COST.COST;
488 CLOSE CUR_GET_ADDITION_COST;
489 END IF;
490 END LOOP;
491 /*SRW.MESSAGE(1275
492 ,'value of ln_addition_cost is ' || LN_ADDITION_COST)*/NULL;
493 RETURN (LN_ADDITION_COST);
494 ELSE
495 RETURN 0;
496 END IF;
497 END CF_ADDITIONS_COSTFORMULA;
498
499 FUNCTION CF_CLOSING_WDV_TOTALFORMULA(DEP_TOT IN NUMBER
500 ,CF_3 IN NUMBER) RETURN NUMBER IS
501 LN_CLOSING_WDV_TOTAL JAI_FA_AST_BLOCKS.CLOSING_WDV%TYPE;
502 BEGIN
503 IF NVL(DEP_TOT
504 ,0) = 0 THEN
505 LN_CLOSING_WDV_TOTAL := 0;
506 ELSE
507 LN_CLOSING_WDV_TOTAL := NVL(CF_3 - DEP_TOT
508 ,0);
509 END IF;
510 IF LN_CLOSING_WDV_TOTAL < 0 THEN
511 LN_CLOSING_WDV_TOTAL := 0;
512 END IF;
513 RETURN (LN_CLOSING_WDV_TOTAL);
514 END CF_CLOSING_WDV_TOTALFORMULA;
515
516 FUNCTION CALCULATE_ACTUAL_COST(P_TRANSACTION_HEADER_ID_IN IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE
517 ,P_DATE_EFFECTIVE IN FA_BOOKS.DATE_EFFECTIVE%TYPE
518 ,P_BOOK_NAME IN FA_BOOKS.BOOK_TYPE_CODE%TYPE
519 ,P_ASSET_ID IN FA_BOOKS.ASSET_ID%TYPE
520 ,P_COST IN FA_BOOKS.COST%TYPE) RETURN NUMBER IS
521 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
522 SELECT
523 TRANSACTION_HEADER_ID_IN,
524 DATE_EFFECTIVE,
525 FAB.COST
526 FROM
527 FA_BOOKS FAB
528 WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
529 AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
530 AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
531 AND FAB.ASSET_ID = CP_ASSET_ID
532 AND FAB.RETIREMENT_ID IS NOT NULL;
533 REC_CUR_GET_ADDITION_COST CUR_GET_ADDITION_COST%ROWTYPE;
534 LN_ADDITION_COST FA_BOOKS.COST%TYPE;
535 LN_TRANSACTION_HEADER_ID_IN FA_BOOKS.TRANSACTION_HEADER_ID_IN%TYPE;
536 LD_DATE_EFFECTIVE FA_BOOKS.DATE_EFFECTIVE%TYPE;
537 BEGIN
538 /*SRW.MESSAGE(1275
539 ,'in function calculate_actual_cost for actual cost processing')*/NULL;
540 LN_ADDITION_COST := P_COST;
541 LN_TRANSACTION_HEADER_ID_IN := P_TRANSACTION_HEADER_ID_IN;
542 LD_DATE_EFFECTIVE := P_DATE_EFFECTIVE;
543 LOOP
544 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);
545 FETCH CUR_GET_ADDITION_COST
546 INTO REC_CUR_GET_ADDITION_COST;
547 IF CUR_GET_ADDITION_COST%NOTFOUND THEN
548 CLOSE CUR_GET_ADDITION_COST;
549 EXIT;
550 ELSE
551 LN_TRANSACTION_HEADER_ID_IN := REC_CUR_GET_ADDITION_COST.TRANSACTION_HEADER_ID_IN;
552 LD_DATE_EFFECTIVE := REC_CUR_GET_ADDITION_COST.DATE_EFFECTIVE;
553 LN_ADDITION_COST := REC_CUR_GET_ADDITION_COST.COST;
554 CLOSE CUR_GET_ADDITION_COST;
555 END IF;
556 END LOOP;
557 /*SRW.MESSAGE(1275
558 ,'Asset ->' || P_ASSET_ID || ' Book_name ' || P_BOOK_NAME || 'value of ln_addition_cost is ' || LN_ADDITION_COST)*/NULL;
559 RETURN (LN_ADDITION_COST);
560 END CALCULATE_ACTUAL_COST;
561
562 END JA_JAINASST_XMLP_PKG;
563
564
[Home] [Help]