1 PACKAGE BODY FA_ASSET_SUMM_RPT_PKG AS
2 -- $Header: FASSUMRPTPB.pls 120.7.12020000.2 2012/07/23 10:10:55 rmandali ship $
3 FUNCTION ASSIGNED_UNITS(p_asset_id_in IN NUMBER
4 ,p_ccid_in IN NUMBER
5 ,p_transaction_units_in IN NUMBER /* what does this parameter do? */
6 ,p_original_cost_in IN NUMBER
7 ,p_units_in IN NUMBER)
8 RETURN NUMBER
9 IS
10 ln_units NUMBER;
11 ln_amount NUMBER;
12 BEGIN
13
14 SELECT NVL(SUM(fdh.units_assigned),0)
15 INTO ln_units
16 FROM fa_distribution_history fdh
17 WHERE fdh.asset_id = p_asset_id_in
18 AND fdh.book_type_code = P_DIST_SOURCE_BOOK
19 AND fdh.code_combination_id = p_ccid_in
20 AND fdh.transaction_header_id_in =
21 (SELECT MAX(fdh1.transaction_header_id_in)
22 FROM fa_distribution_history fdh1
23 ,fa_transaction_headers fth1
24 WHERE fdh1.asset_id = p_asset_id_in
25 AND fdh1.book_type_code = P_DIST_SOURCE_BOOK /* why P_BOOK_NAME it should be P_BOOK_TYPE_CODE, both are not synonymous */
26 AND fdh1.code_combination_id = p_ccid_in
27 AND fdh1.transaction_header_id_in = fth1.transaction_header_id
28 AND fth1.date_effective <= gd_per_close_date);
29
30 SELECT (p_original_cost_in/p_units_in) * ln_units
31 INTO ln_amount
32 FROM DUAL;
33 /* Dev comments:
34 what happened to rounding of the amount!!
35 */
36 RETURN (ln_amount);
37 END ASSIGNED_UNITS;
38 --=====================================================================
39 --=====================================================================
40
41 FUNCTION CURRENT_AMOUNT(p_transaction_header_id IN NUMBER
42 ,p_asset_id_in IN NUMBER
43 ,p_category_id_in IN NUMBER
44 ,p_asset_type_in IN VARCHAR2
45 ,p_ccid_in IN NUMBER
46 ,p_location_id_in IN NUMBER)
47 RETURN NUMBER
48 IS
49 ln_amount NUMBER;
50 BEGIN
51 BEGIN
52 fnd_file.put_line(fnd_file.log,'Begin Original Amount::');
53
54 SELECT NVL(fdd.cost,0)
55 INTO ln_amount
56 FROM fa_deprn_detail fdd
57 ,fa_distribution_history fdh
58 ,fa_asset_history fah
59 ,fa_transaction_headers fth
60 WHERE fdd.period_counter = (
61 SELECT MAX(fdd1.period_counter)
62 FROM fa_deprn_detail fdd1
63 WHERE fdd1.book_type_code = P_BOOK_NAME
64 AND fdd1.distribution_id = fdd.distribution_id
65 AND fdd1.asset_id = fdd.asset_id
66 AND fdd1.period_counter < gn_lex_begin_period_counter)
67 AND fdd.book_type_code = P_BOOK_NAME
68 AND fdh.asset_id = fth.asset_id
69 AND fdh.transaction_header_id_in = p_transaction_header_id
70 AND fah.category_id = p_category_id_in
71 AND fah.asset_type = p_asset_type_in
72 AND fah.asset_id = p_asset_id_in
73 AND fdd.asset_id = p_asset_id_in
74 AND fdd.distribution_id = fdh.distribution_id
75 AND fah.asset_type IN ('CAPITALIZED','CIP')
76 AND fdh.location_id = p_location_id_in
77 AND ((fdh.date_effective >= fah.date_effective
78 AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1)
79 AND fdh.transaction_header_id_in = fth.transaction_header_id)
80 OR(fah.date_effective > fdh.date_effective
81 AND fah.transaction_header_id_in = fth.transaction_header_id
82 AND fth.transaction_type_code = 'ADDITION' /* CIP ADDITION is not considered? */
83 AND fah.date_effective < gd_per_open_date))
84 AND fdh.code_combination_id = p_ccid_in
85 AND fdd.distribution_id = (
86 SELECT MAX(fdd1.distribution_id)
87 FROM fa_deprn_detail fdd1
88 ,fa_distribution_history fdh1
89 WHERE fdd1.book_type_code = fdd.book_type_code
90 AND fdd1.asset_id = fdd.asset_id
91 AND fdd1.distribution_id = fdh1.distribution_id
92 AND fdh1.code_combination_id = fdh.code_combination_id
93 AND fdh1.location_id = p_location_id_in
94 AND fdd1.period_counter < gn_lex_begin_period_counter);
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 ln_amount := 0;
98 WHEN OTHERS THEN
99 FND_FILE.PUT_LINE(FND_FILE.LOG,'In CURRENT_AMOUNT');
100 --RAISE;
101 END;
102 RETURN ln_amount;
103 END CURRENT_AMOUNT;
104
105 --=====================================================================
106 --=====================================================================
107
108 FUNCTION ADDITIONS_AMOUNT(p_transaction_header_id IN NUMBER
109 ,p_asset_id_in IN NUMBER
110 ,p_category_id_in IN NUMBER
111 ,p_asset_type_in IN VARCHAR2
112 ,p_ccid_in IN NUMBER
113 ,p_location_id_in IN NUMBER
114 ,p_fah_trx_header_id IN NUMBER)
115 RETURN NUMBER
116 IS
117 ln_amount NUMBER;
118 BEGIN
119 BEGIN
120 SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount
121 , fadj.adjustment_amount), 0)),0) additions
122 INTO ln_amount
123 FROM fa_transaction_headers fth
124 ,fa_adjustments fadj
125 ,fa_asset_history fah
126 ,fa_distribution_history fdh
127 WHERE fth.asset_id = fah.asset_id
128 AND fth.asset_id = fdh.asset_id
129 AND fdh.distribution_id = fadj.distribution_id
130 AND fdh.transaction_header_id_in = p_transaction_header_id
131 AND fah.category_id = p_category_id_in
132 AND fah.asset_type = p_asset_type_in
133 AND fth.asset_id = p_asset_id_in
134 AND fth.book_type_code = P_BOOK_NAME
135 AND fadj.transaction_header_id = fth.transaction_header_id
136 AND ((fth.transaction_type_code IN ('CIP ADDITION','CIP ADJUSTMENT')
137 AND fah.asset_type = 'CIP'
138 AND fadj.adjustment_type = 'CIP COST')
139 OR (fth.transaction_type_code = 'ADDITION' AND fah.asset_type = 'CAPITALIZED'
140 AND fadj.adjustment_type = 'COST'))
141 AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
142 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
143 AND ((fdh.date_effective >= fah.date_effective
144 AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1))
145 OR (fah.date_effective > fdh.date_effective
146 AND fth.transaction_type_code = 'ADDITION'))
147 AND fah.transaction_header_id_in = p_fah_trx_header_id
148 AND fdh.code_combination_id = p_ccid_in
149 AND fdh.location_id = p_location_id_in
150 AND (fah.asset_type <> 'CAPITALIZED' OR ((fah.asset_type='CAPITALIZED') AND (NOT EXISTS (-- Added these lines as part of the fix to the SR 7284007.992
151 SELECT 'Y'
152 FROM fa_asset_history fah1
153 WHERE fah1.asset_type='CIP'
154 and fah1.transaction_header_id_out = fah.transaction_header_id_in
155 and fah1.asset_id = fah.asset_id
156 ))));
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 ln_amount := 0;
160 WHEN OTHERS THEN
161 FND_FILE.PUT_LINE(FND_FILE.LOG,'In ADDITIONS_AMOUNT');
162 --raise; /* why is this missed out? */ /* This is missed out as the program completes in error when run with few Null parameters*/
163 END;
164 /* IF ln_amount IS NULL THEN is this condition needed if we add nvls to the above SUM statement?
165 ln_amount := 0;
166 END IF;*/
167 RETURN ln_amount;
168 END ADDITIONS_AMOUNT;
169
170 --=====================================================================
171 --=====================================================================
172
173 FUNCTION RETIREMENT_AMOUNT(p_transaction_header_id IN NUMBER
174 ,p_asset_id_in IN NUMBER
175 ,p_category_id_in IN NUMBER
176 ,p_asset_type_in IN VARCHAR2
177 ,p_ccid_in IN NUMBER
178 ,p_location_id_in IN NUMBER)
179 RETURN NUMBER
180 IS
181 ln_amount NUMBER := 0;
182 BEGIN
183 BEGIN
184 SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'DR',(-1)*fadj.adjustment_amount,fadj.adjustment_amount), 0)),0) retirements
185 INTO ln_amount
186 FROM fa_books fb
187 ,fa_transaction_headers fth
188 ,fa_adjustments fadj
189 ,fa_asset_history fah
190 ,fa_distribution_history fdh
191 WHERE fth.asset_id = fah.asset_id
192 AND fth.asset_id = fdh.asset_id
193 AND fah.asset_type = p_asset_type_in
194 AND fdh.distribution_id = fadj.distribution_id
195 AND fah.category_id = p_category_id_in
196 AND fdh.transaction_header_id_in = p_transaction_header_id
197 AND fth.book_type_code = P_BOOK_NAME
198 AND fb.asset_id = p_asset_id_in
199 AND fdh.code_combination_id = p_ccid_in
200 AND fadj.transaction_header_id = fth.transaction_header_id
201 AND fth.transaction_type_code IN ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT')
202 AND fadj.adjustment_type IN ('COST', 'CIP COST')
203 AND fadj.source_type_code IN ('RETIREMENT', 'CIP RETIREMENT')
204 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
205 AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
206 AND fth.transaction_header_id >= fah.transaction_header_id_in
207 AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1)
208 AND fb.transaction_header_id_in = fth.transaction_header_id
209 AND fb.book_type_code = P_BOOK_NAME
210 AND fdh.location_id = p_location_id_in;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 ln_amount := 0;
214 WHEN OTHERS THEN
215 FND_FILE.PUT_LINE(FND_FILE.LOG,'In RETIREMENT_AMOUNT');
216 END;
217 /* IF ln_amount IS NULL THEN is this condition needed if we add nvls to the above SUM statement?
218 ln_amount := 0;
219 END IF; */
220 RETURN ln_amount;
221 END RETIREMENT_AMOUNT;
222
223 --=====================================================================
224 --=====================================================================
225
226 FUNCTION CHANGES_OF_ACCOUNTS(p_transaction_header_id IN NUMBER
227 ,p_asset_id_in IN NUMBER
228 ,p_category_id_in IN NUMBER
229 ,p_asset_type_in IN VARCHAR2
230 ,p_ccid_in IN NUMBER
231 ,p_location_id_in IN NUMBER
232 ,p_fah_trx_header_id IN NUMBER)
233 RETURN NUMBER
234 IS
235 ln_amount NUMBER := 0;
236 BEGIN
237 BEGIN
238 SELECT NVL(SUM(x.all_transferred),0)
239 INTO ln_amount
240 FROM (
241 SELECT NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount),0) all_transferred
242 FROM fa_transaction_headers fth
243 ,fa_adjustments fadj
244 ,fa_asset_history fah1
245 ,fa_distribution_history fdh
246 WHERE EXISTS (
247 SELECT 1
248 FROM fa_deprn_detail fdd
249 WHERE fdd.asset_id = fth.asset_id
250 AND fdd.book_type_code = P_BOOK_NAME
251 AND fdd.deprn_source_code = 'D'
252 )
253 AND (fah1.asset_type = p_asset_type_in
254 OR (fah1.asset_type = 'CIP'
255 AND EXISTS (
256 SELECT period_counter_capitalized
257 FROM fa_books fb2
258 WHERE fb2.asset_id = fah1.asset_id
259 AND fb2.book_type_code = P_BOOK_NAME
260 AND fb2.period_counter_capitalized < gn_lex_begin_period_counter
261 )))
262 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
263 AND fadj.source_type_code = 'TRANSFER'
264 AND fadj.adjustment_type IN ('COST', 'CIP COST')
265 AND fth.transaction_type_code <> 'TRANSFER IN'
266 AND fth.asset_id = fah1.asset_id
267 AND fth.asset_id = fdh.asset_id
268 AND fdh.distribution_id = fadj.distribution_id
269 AND fdh.transaction_header_id_in = p_transaction_header_id
270 AND fth.asset_id = p_asset_id_in
271 AND fah1.category_id = p_category_id_in
272 AND fth.book_type_code = P_BOOK_NAME
273 AND fadj.transaction_header_id = fth.transaction_header_id
274 AND fth.transaction_date_entered <= gd_period_close_date
275 AND fadj.asset_id = fah1.asset_id
276 AND fadj.book_type_code = P_BOOK_NAME
277 AND fdh.code_combination_id = p_ccid_in
278 AND fdh.location_id = p_location_id_in
279 AND fth.transaction_header_id >= fah1.transaction_header_id_in
280 AND fth.transaction_header_id < NVL(fah1.transaction_header_id_out, fth.transaction_header_id + 1)
281 UNION ALL -- Added this query as part of the fix to the SR 7284007.992
282 SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount
283 , fadj.adjustment_amount),0)),0) all_transferred
284 FROM fa_transaction_headers fth
285 ,fa_adjustments fadj
286 ,fa_asset_history fah
287 ,fa_distribution_history fdh
288 WHERE fth.asset_id = fah.asset_id
289 AND fth.asset_id = fdh.asset_id
290 AND fdh.distribution_id = fadj.distribution_id
291 AND fdh.transaction_header_id_in = p_transaction_header_id
292 AND fah.category_id = p_category_id_in
293 AND fah.asset_type = p_asset_type_in
294 AND fth.asset_id = p_asset_id_in
295 AND fth.book_type_code = P_BOOK_NAME
296 AND fadj.transaction_header_id = fth.transaction_header_id
297 AND fth.transaction_type_code = 'ADDITION'
298 AND fah.asset_type = 'CAPITALIZED'
299 AND fadj.adjustment_type = 'COST'
300 AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
301 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
302 AND ((fdh.date_effective >= fah.date_effective
303 AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1))
304 OR (fah.date_effective > fdh.date_effective
305 AND fth.transaction_type_code = 'ADDITION'))
306 AND fah.transaction_header_id_in = p_fah_trx_header_id
307 AND fdh.code_combination_id = p_ccid_in
308 AND fdh.location_id = p_location_id_in
309 AND ((fah.asset_type='CAPITALIZED') AND (EXISTS (-- Added these lines as part of the fix to the SR 7284007.992
310 SELECT 'Y'
311 FROM fa_asset_history fah1
312 WHERE fah1.asset_type='CIP'
313 and fah1.transaction_header_id_out = fah.transaction_header_id_in
314 and fah1.asset_id = fah.asset_id
315 )))
316 UNION ALL
317 SELECT (-1)*(NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount -- Added this query as part of the fix to the SR 7284007.992
318 , fadj.adjustment_amount), 0)),0)) all_transferred
319 FROM fa_transaction_headers fth
320 ,fa_adjustments fadj
321 ,fa_asset_history fah
322 ,fa_distribution_history fdh
323 WHERE fth.asset_id = fah.asset_id
324 AND fth.asset_id = fdh.asset_id
325 AND fdh.distribution_id = fadj.distribution_id
326 AND fdh.transaction_header_id_in = p_transaction_header_id
327 AND fah.category_id = p_category_id_in
328 AND fah.asset_type = p_asset_type_in
329 AND fth.asset_id = p_asset_id_in
330 AND fth.book_type_code = P_BOOK_NAME
334 AND fadj.adjustment_type = 'CIP COST'
331 AND fadj.transaction_header_id = fth.transaction_header_id
332 AND fth.transaction_type_code IN ('CIP ADDITION','CIP ADJUSTMENT')
333 AND fah.asset_type = 'CIP'
335 AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
336 AND fdh.date_effective >= fah.date_effective
337 AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1)
338 AND fah.transaction_header_id_in = p_fah_trx_header_id
339 AND fdh.code_combination_id = p_ccid_in
340 AND fdh.location_id = p_location_id_in
341 AND (EXISTS (
342 SELECT 'Y'
343 FROM fa_asset_history fah1
344 ,fa_transaction_headers fth1
345 WHERE fah1.asset_type='CAPITALIZED'
346 AND fah1.transaction_header_id_in = fah.transaction_header_id_out
347 AND fah1.asset_id = fah.asset_id
348 AND fth1.transaction_header_id = fah1.transaction_header_id_in
349 AND fth1.transaction_type_code = 'ADDITION'
350 AND fth1.transaction_date_entered BETWEEN gd_period_open_date AND gd_period_close_date
351 ))
352 UNION ALL
353 SELECT NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount),0) all_transferred
354 FROM fa_transaction_headers fth
355 ,fa_adjustments fadj
356 ,fa_asset_history fah1
357 ,fa_asset_history fah2
358 ,fa_distribution_history fdh
359 WHERE EXISTS (
360 SELECT 1
361 FROM fa_deprn_detail fdd
362 WHERE fdd.asset_id = fth.asset_id
363 AND fdd.book_type_code = P_BOOK_NAME
364 AND fdd.deprn_source_code = 'D'
365 )
366 AND (fah1.asset_type = p_asset_type_in
367 OR (fah1.asset_type = 'CIP'
368 AND EXISTS (
369 SELECT fb2.period_counter_capitalized
370 FROM fa_books fb2
371 WHERE fb2.asset_id = fah1.asset_id
372 AND fb2.book_type_code = P_BOOK_NAME
373 AND fb2.period_counter_capitalized < gn_lex_begin_period_counter
374 )
375 )
376 )
377 AND fadj.source_type_code = 'RECLASS'
378 AND fadj.adjustment_type IN ('COST', 'CIP COST')
379 AND fth.transaction_type_code <> 'TRANSFER IN'
380 AND fth.asset_id = fah1.asset_id
381 AND fth.asset_id = fdh.asset_id
382 AND fdh.distribution_id = fadj.distribution_id
383 AND fdh.transaction_header_id_in = p_transaction_header_id
384 AND fdh.location_id = p_location_id_in
385 AND fth.book_type_code = P_BOOK_NAME
386 AND fah2.category_id = p_category_id_in
387 AND fth.asset_id = p_asset_id_in
388 AND fdh.code_combination_id = p_ccid_in
389 AND fadj.transaction_header_id = fth.transaction_header_id
390 AND fth.transaction_date_entered <= gd_period_close_date
391 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
392 AND fadj.asset_id = fah1.asset_id
393 AND fadj.book_type_code = P_BOOK_NAME
394 AND fah1.asset_id = fah2.asset_id
395 AND fth.transaction_header_id >= fah1.transaction_header_id_in
396 AND fth.transaction_header_id < NVL(fah1.transaction_header_id_out, fth.transaction_header_id + 1)
397 AND fdh.date_effective >= fah2.date_effective
398 AND fdh.date_effective < NVL(fah2.date_ineffective, fdh.date_effective + 1)
399 ) X;
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 ln_amount := 0;
403 WHEN OTHERS THEN
404 FND_FILE.PUT_LINE(FND_FILE.LOG,'In CHANGES_OF_ACCOUNTS');
405 -- RAISE;
406 END;
407 RETURN ln_amount;
408 END CHANGES_OF_ACCOUNTS;
409
410 --=====================================================================
411 --=====================================================================
412
413 FUNCTION ADJUSTMENT_AMOUNT(p_transaction_header_id IN NUMBER
414 ,p_asset_id_in IN NUMBER
415 ,p_category_id_in IN NUMBER
416 ,p_asset_type_in IN VARCHAR2
417 ,p_location_id_in IN NUMBER
418 ,p_ccid_in IN NUMBER)
419 RETURN NUMBER
420 IS
421 ln_amount NUMBER;
422 BEGIN
423 BEGIN
424 SELECT NVL(SUM(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount)),0)
425 INTO ln_amount
426 FROM fa_transaction_headers fth
427 ,fa_adjustments fadj
428 ,fa_asset_history fah
429 ,fa_distribution_history fdh
430 WHERE fth.asset_id = fah.asset_id
431 AND fth.asset_id = fdh.asset_id
432 AND fdh.distribution_id = fadj.distribution_id
433 AND fdh.transaction_header_id_in = p_transaction_header_id
434 AND fah.category_id = p_category_id_in
435 AND fdh.location_id = p_location_id_in
436 AND fah.asset_type = p_asset_type_in
437 AND fth.book_type_code = P_BOOK_NAME
438 AND fdh.code_combination_id = p_ccid_in
439 AND fth.asset_id = p_asset_id_in
440 AND fadj.transaction_header_id = fth.transaction_header_id
441 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
442 AND fth.transaction_type_code IN ('ADJUSTMENT') /* CIP ADJUSTMENT excluded ? */
446 AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1);
443 AND fadj.adjustment_type IN ('COST')
444 AND fth.transaction_date_entered <= gd_period_close_date
445 AND fth.transaction_header_id >= fah.transaction_header_id_in
447 EXCEPTION
448 WHEN NO_DATA_FOUND THEN
449 ln_amount := 0;
450 WHEN OTHERS THEN
451 FND_FILE.PUT_LINE(FND_FILE.LOG,'In ADJUSTMENT_AMOUNT');
452 -- RAISE;
453 END;
454 /* IF ln_amount IS NULL THEN
455 ln_amount := 0;
456 END IF; */
457 RETURN ln_amount ;
458 END ADJUSTMENT_AMOUNT;
459
460 --=====================================================================
461 --=====================================================================
462 /* what is this appreciation amount? */
463 FUNCTION APPRECIATION_AMOUNT(p_transaction_header_id IN NUMBER
464 ,p_asset_id_in IN NUMBER
465 ,p_category_id_in IN NUMBER
466 ,p_asset_type_in IN VARCHAR2
467 ,p_location_id_in IN NUMBER
468 ,p_ccid_in IN NUMBER)
469 RETURN NUMBER
470 IS
471 ln_amount NUMBER;
472 BEGIN
473 BEGIN
474 SELECT NVL(SUM(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount)),0)
475 INTO ln_amount
476 FROM fa_books fb
477 ,fa_transaction_headers fth
478 ,fa_adjustments fadj
479 ,fa_asset_history fah
480 ,fa_distribution_history fdh
481 WHERE fth.asset_id = fah.asset_id
482 AND fth.asset_id = fdh.asset_id
483 AND fdh.distribution_id = fadj.distribution_id
484 AND fdh.transaction_header_id_in = p_transaction_header_id
485 AND fah.category_id = p_category_id_in
486 AND fdh.location_id = p_location_id_in
487 AND fah.asset_type = p_asset_type_in
488 AND fth.book_type_code = P_BOOK_NAME
489 AND fdh.code_combination_id = p_ccid_in
490 AND fth.asset_id = p_asset_id_in
491 AND fadj.transaction_header_id = fth.transaction_header_id
492 AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
493 AND fth.transaction_type_code IN ('ADJUSTMENT','CIP ADJUSTMENT')
494 AND fadj.adjustment_type = 'EXPENSE'
495 AND fth.transaction_subtype = 'APPREC'
496 AND fth.transaction_date_entered <= gd_period_close_date
497 AND fth.transaction_header_id >= fah.transaction_header_id_in
498 AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1)
499 AND fb.transaction_header_id_in = fth.transaction_header_id
500 AND fb.book_type_code = P_BOOK_NAME;
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 ln_amount := 0;
504 WHEN OTHERS THEN
505 FND_FILE.PUT_LINE(FND_FILE.LOG,'In ADJUSTMENT_AMOUNT');
506 -- RAISE;
507 END;
508 /* IF ln_amount IS NULL THEN
509 ln_amount := 0;
510 END IF; */
511 RETURN ln_amount ;
512 END APPRECIATION_AMOUNT;
513
514 --=====================================================================
515 --=====================================================================
516 -- Bit unclear of this requirement for this routine
517 FUNCTION ACCM_DEPRN_AMT(p_transaction_header_id IN NUMBER
518 ,p_asset_id_in IN NUMBER
519 ,p_ccid_in IN NUMBER
520 ,p_location_id_in IN NUMBER)
521 RETURN NUMBER
522 IS
523 ln_amount NUMBER;
524 BEGIN
525 BEGIN
526 SELECT NVL(fdd.deprn_reserve,0)
527 INTO ln_amount
528 FROM fa_deprn_summary fds
529 ,fa_deprn_detail fdd
530 ,fa_distribution_history fdh
531 WHERE fds.book_type_code = fdd.book_type_code
532 AND fds.asset_id = fdd.asset_id
533 AND fds.period_counter = fdd.period_counter
534 AND fdd.distribution_id = fdh.distribution_id
535 AND fdh.transaction_header_id_in = p_transaction_header_id
536 AND fdh.book_type_code = P_DIST_SOURCE_BOOK
537 AND fdd.asset_id = fdh.asset_id
538 AND fds.book_type_code = P_BOOK_NAME
539 AND fds.asset_id = p_asset_id_in
540 AND fdh.code_combination_id = p_ccid_in
541 AND fdh.location_id = p_location_id_in
542 AND fds.period_counter = (
543 SELECT MAX(fds1.period_counter)
544 FROM fa_deprn_summary fds1
545 ,fa_deprn_detail fdd1
546 ,fa_distribution_history fdh1
547 WHERE fds1.book_type_code = fdd1.book_type_code
548 AND fds1.asset_id = fdd1.asset_id
549 AND fds1.period_counter = fdd1.period_counter
550 AND fdd1.distribution_id = fdh1.distribution_id
551 AND fdh1.book_type_code = P_DIST_SOURCE_BOOK
552 AND fdd1.asset_id = fdh1.asset_id
553 AND fds1.book_type_code = fds.book_type_code
554 AND fds1.asset_id = fds.asset_id
555 AND fdh1.code_combination_id = fdh.code_combination_id
556 AND fdh1.location_id = p_location_id_in
557 AND fds1.period_counter BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
558 );
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 BEGIN
562 SELECT fdd.deprn_reserve
563 INTO ln_amount
564 FROM fa_deprn_summary fds
565 ,fa_deprn_detail fdd
569 AND fds.asset_id = fdd.asset_id
566 ,fa_distribution_history fdh
567 ,fa_books fb
568 WHERE fds.book_type_code = fdd.book_type_code
570 AND fds.period_counter = fdd.period_counter
571 AND fdd.distribution_id = fdh.distribution_id
572 AND fdh.book_type_code = P_DIST_SOURCE_BOOK
573 AND fdd.asset_id = fdh.asset_id
574 AND fds.book_type_code = P_BOOK_NAME
575 AND fds.asset_id = p_asset_id_in
576 AND fdh.code_combination_id = p_ccid_in
577 AND fdh.location_id = p_location_id_in
578 AND fb.asset_id = fdd.asset_id
579 AND fb.book_type_code = fdd.book_type_code
580 AND fb.date_ineffective IS NULL
581 AND fb.period_counter_life_complete IS NOT NULL
582 AND fds.period_counter = (
583 SELECT MAX(fds1.period_counter)
584 FROM fa_deprn_summary fds1
585 ,fa_deprn_detail fdd1
586 ,fa_distribution_history fdh1
587 WHERE fds1.book_type_code = fdd1.book_type_code
588 AND fds1.asset_id = fdd1.asset_id
589 AND fds1.period_counter = fdd1.period_counter
590 AND fdd1.distribution_id = fdh1.distribution_id
591 AND fdh1.book_type_code = P_DIST_SOURCE_BOOK
592 AND fdd1.asset_id = fdh1.asset_id
593 AND fds1.book_type_code = fds.book_type_code
594 AND fds1.asset_id = fds.asset_id
595 AND fdh1.code_combination_id = fdh.code_combination_id
596 AND fdh1.location_id = p_location_id_in
597 AND fds1.period_counter < = gn_lex_begin_period_counter);
598 EXCEPTION
599 WHEN NO_DATA_FOUND THEN
600 ln_amount := 0;
601 WHEN OTHERS THEN
602 FND_FILE.PUT_LINE(FND_FILE.LOG,'In ACCM_DEPRN_AMT (Inner)');
603 END;
604 WHEN TOO_MANY_ROWS THEN
605 SELECT NVL(MAX(fdd.deprn_reserve),0)
606 INTO ln_amount
607 FROM fa_deprn_summary fds
608 ,fa_deprn_detail fdd
609 ,fa_distribution_history fdh
610 WHERE fds.book_type_code = fdd.book_type_code
611 AND fds.asset_id = fdd.asset_id
612 AND fds.period_counter = fdd.period_counter
613 AND fdd.distribution_id = fdh.distribution_id
614 AND fdh.transaction_header_id_in = p_transaction_header_id
615 AND fdh.book_type_code = P_DIST_SOURCE_BOOK
616 AND fdd.asset_id = fdh.asset_id
617 AND fds.book_type_code = P_BOOK_NAME
618 AND fds.asset_id = p_asset_id_in
619 AND fdh.code_combination_id = p_ccid_in
620 AND fdh.location_id = p_location_id_in
621 AND fds.period_counter = (
622 SELECT MAX(fds1.period_counter)
623 FROM fa_deprn_summary fds1
624 ,fa_deprn_detail fdd1
625 ,fa_distribution_history fdh1
626 WHERE fds1.book_type_code = fdd1.book_type_code
627 AND fds1.asset_id = fdd1.asset_id
628 AND fds1.period_counter = fdd1.period_counter
629 AND fdd1.distribution_id = fdh1.distribution_id
630 AND fdh.transaction_header_id_in = p_transaction_header_id
631 AND fdh1.book_type_code = P_DIST_SOURCE_BOOK
632 AND fdd1.asset_id = fdh1.asset_id
633 AND fds1.book_type_code = fds.book_type_code
634 AND fds1.asset_id = fds.asset_id
635 AND fdh1.code_combination_id = fdh.code_combination_id
636 AND fdh1.location_id = p_location_id_in
637 AND fds1.period_counter BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
638 );
639 WHEN OTHERS THEN
640 FND_FILE.PUT_LINE(FND_FILE.LOG,'In ACCM_DEPRN_AMT');
641 --RAISE;
642 END;
643 /* IF ln_amount IS NULL THEN
644 ln_amount := 0;
645 END IF;*/
646 RETURN ln_amount;
647 END ACCM_DEPRN_AMT;
648
649 --=====================================================================
650 --=====================================================================
651
652 FUNCTION CATEGORY_ACCM_DEPRN_AMT(p_transaction_header_id IN NUMBER
653 ,p_asset_id_in IN NUMBER
654 ,p_ccid_in IN NUMBER
655 ,p_location_id_in IN NUMBER)
656 RETURN NUMBER
657 IS
658 ln_amount NUMBER;
659 BEGIN
660 BEGIN
661 SELECT NVL(SUM(DECODE(fdd.deprn_source_code,'B',fdd.deprn_reserve,fdd.deprn_amount)),0)
662 INTO ln_amount
663 FROM fa_deprn_summary fds
664 ,fa_deprn_detail fdd
665 ,fa_distribution_history fdh
666 WHERE fds.book_type_code = fdd.book_type_code
667 AND fds.asset_id = fdd.asset_id
668 AND fds.period_counter = fdd.period_counter
669 AND fdd.distribution_id = fdh.distribution_id
670 AND fdh.transaction_header_id_in = p_transaction_header_id
671 AND fdh.book_type_code = P_DIST_SOURCE_BOOK
672 AND fdd.asset_id = fdh.asset_id
673 AND fds.book_type_code = P_BOOK_NAME
674 AND fds.asset_id = p_asset_id_in
675 AND fdh.code_combination_id = p_ccid_in
676 AND fdh.location_id = p_location_id_in
677 AND fds.period_counter <= gn_lex_end_period_counter;
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 ln_amount := 0;
684 END;
681 WHEN OTHERS THEN
682 FND_FILE.PUT_LINE(FND_FILE.LOG,'In ACCM_DEPRN_AMT');
683 --RAISE;
685 /* IF ln_amount IS NULL THEN
686 ln_amount := 0;
687 END IF;*/
688 RETURN ln_amount;
689 END CATEGORY_ACCM_DEPRN_AMT;
690
691 --=====================================================================
692 --=====================================================================
693
694 FUNCTION ACCM_DEPRN_AMT_PR_YEAR(p_transaction_header_id IN NUMBER
695 ,p_asset_id_in IN NUMBER
696 ,p_ccid_in IN NUMBER
697 ,p_location_id_in IN NUMBER)
698 RETURN NUMBER
699 IS
700 ln_amount NUMBER;
701 BEGIN
702 BEGIN
703 SELECT NVL(fdd.deprn_reserve,0)
704 INTO ln_amount
705 FROM fa_books fb
706 ,fa_deprn_summary fds
707 ,fa_deprn_detail fdd
708 ,fa_distribution_history fdh
709 WHERE fb.book_type_code = P_BOOK_NAME
710 AND fb.date_ineffective IS NULL
711 AND fb.book_type_code = fds.book_type_code
712 AND fb.asset_id = fds.asset_id
713 AND fds.period_counter = gn_lex_begin_period_counter - 1
714 AND fdh.transaction_header_id_in = p_transaction_header_id
715 AND fb.asset_id = p_asset_id_in
716 AND fds.book_type_code = fdd.book_type_code
717 AND fds.asset_id = fdd.asset_id
718 AND fds.period_counter = fdd.period_counter
719 AND fdd.distribution_id = fdh.distribution_id
720 AND fdh.book_type_code = P_DIST_SOURCE_BOOK
721 AND fdd.asset_id = fdh.asset_id
722 AND fdh.code_combination_id = p_ccid_in
723 AND fdh.location_id = p_location_id_in;
724 EXCEPTION
725 WHEN NO_DATA_FOUND THEN
726 ln_amount := 0;
727 WHEN OTHERS THEN
728 FND_FILE.PUT_LINE(FND_FILE.LOG,'In NBV_VALUE');
729 --RAISE;
730 END;
731 RETURN ln_amount;
732 END ACCM_DEPRN_AMT_PR_YEAR;
733
734 --=====================================================================
735 --=====================================================================
736
737 FUNCTION DEPRN_EXPENSE(p_transaction_header_id IN NUMBER
738 ,p_asset_id_in IN NUMBER
739 ,p_ccid_in IN NUMBER
740 ,p_location_id_in IN NUMBER)
741 RETURN NUMBER
742 IS
743 ln_amount NUMBER;
744 BEGIN
745 BEGIN
746 SELECT NVL(SUM(fdd.deprn_amount - fdd.deprn_adjustment_amount + NVL(fx.adjustment_amount,0)),0)
747 INTO ln_amount
748 FROM fa_deprn_detail fdd
749 ,fa_distribution_history fdh
750 ,(SELECT fadj.distribution_id
751 ,fadj.period_counter_created
752 ,SUM(DECODE(fth.transaction_subtype,'APPREC',0,
753 DECODE(fadj.debit_credit_flag,'CR',-1*fadj.adjustment_amount
754 ,fadj.adjustment_amount))) adjustment_amount
755 FROM fa_adjustments fadj
756 ,fa_transaction_headers fth
757 WHERE fadj.transaction_header_id = fth.transaction_header_id(+)
758 and fth.transaction_type_code IN ('ADJUSTMENT','ADDITION')
759 and fadj.adjustment_type(+) = 'EXPENSE'
760 and fadj.book_type_code = P_BOOK_NAME
761 AND fadj.asset_id = p_asset_id_in
762 and fadj.asset_id = fth.asset_id
763 and fadj.book_type_code = fth.book_type_code
764 group by fadj.distribution_id
765 ,fadj.period_counter_created) fx
766 WHERE fdd.distribution_id = fx.distribution_id(+)
767 and fdd.period_counter = fx.period_counter_created(+)
768 and fdd.distribution_id = fdh.distribution_id
769 and fdd.period_counter BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
770 AND fdd.book_type_code = P_BOOK_NAME
771 AND fdd.asset_id = p_asset_id_in
772 AND fdh.code_combination_id = p_ccid_in
773 AND fdh.transaction_header_id_in = p_transaction_header_id
774 AND fdh.location_id = p_location_id_in;
775 EXCEPTION
776 WHEN NO_DATA_FOUND THEN
777 ln_amount := 0;
778 WHEN OTHERS THEN
779 FND_FILE.PUT_LINE(FND_FILE.LOG,'In DEPRN_EXPENSE');
780 --RAISE;
781 END;
782 /* IF ln_amount IS NULL THEN
783 ln_amount := 0;
784 END IF;*/
785 RETURN ln_amount;
786 END DEPRN_EXPENSE;
787
788 --=====================================================================
789 --=====================================================================
790
791 FUNCTION GAIN_LOSS_AMOUNT(p_transaction_header_id IN NUMBER
792 ,p_asset_id_in IN NUMBER
793 ,p_category_id_in IN NUMBER
794 ,p_asset_type_in IN VARCHAR2
795 ,p_ccid_in IN NUMBER
796 ,p_location_id_in IN NUMBER)
797 RETURN NUMBER
798 IS
799 ln_amount NUMBER := 0;
800 BEGIN
801 BEGIN
802 SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'DR',(-1)*fadj.adjustment_amount,fadj.adjustment_amount), 0)),0) retirements
803 INTO ln_amount
804 FROM fa_books fb
805 ,fa_transaction_headers fth
806 ,fa_adjustments fadj
807 ,fa_asset_history fah
808 ,fa_distribution_history fdh
809 WHERE fth.asset_id = fah.asset_id
810 AND fth.asset_id = fdh.asset_id
811 AND fah.asset_type = p_asset_type_in
815 AND fth.book_type_code = P_BOOK_NAME
812 AND fdh.distribution_id = fadj.distribution_id
813 AND fah.category_id = p_category_id_in
814 AND fdh.transaction_header_id_in = p_transaction_header_id
816 AND fb.asset_id = p_asset_id_in
817 AND fdh.code_combination_id = p_ccid_in
818 AND fadj.transaction_header_id = fth.transaction_header_id
819 AND fth.transaction_type_code IN ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT')
820 AND fadj.adjustment_type IN ('RESERVE')
821 AND fadj.source_type_code IN ('RETIREMENT', 'CIP RETIREMENT')
822 AND fadj.period_counter_adjusted <= gn_lex_end_period_counter
823 AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
824 AND fth.transaction_header_id >= fah.transaction_header_id_in
825 AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1)
826 AND fb.transaction_header_id_in = fth.transaction_header_id
827 AND fb.book_type_code = P_BOOK_NAME
828 AND fdh.location_id = p_location_id_in;
829 EXCEPTION
830 WHEN NO_DATA_FOUND THEN
831 ln_amount := 0;
832 WHEN OTHERS THEN
833 FND_FILE.PUT_LINE(FND_FILE.LOG,'In RETIREMENT_AMOUNT');
834 --RAISE;
835 END;
836 /* IF ln_amount IS NULL THEN
837 ln_amount := 0;
838 END IF;*/
839 RETURN ln_amount;
840 END GAIN_LOSS_AMOUNT;
841
842 --=====================================================================
843 --=====================================================================
844
845 FUNCTION beforeReport
846 RETURN BOOLEAN
847 IS
848 lc_ledger_id NUMBER;
849 lc_maj_seg VARCHAR2(30);
850 lc_min_seg VARCHAR2(30);
851 lc_separator VARCHAR2(10);
852 lc_maj_segnum number(10);--bug13861637
853 lc_min_segnum number(10);--bug13861637
854 BEGIN
855
856 --*************************************************
857 --Used to obtain the Book Controls data
858 --*************************************************
859 SELECT FSC.category_flex_structure
860 INTO gc_cat_flex_struc
861 FROM fa_system_controls FSC;
862 fnd_file.put_line(fnd_file.log,'gc_cat_flex_struc::'||gc_cat_flex_struc);
863
864 --*************************************************
865 --Used to obtain the Category Flexfield Columns
866 --dynamically
867 --*************************************************
868 SELECT fsav.application_column_name
869 INTO lc_maj_seg
870 FROM fnd_segment_attribute_values fsav
871 WHERE fsav.id_flex_code = 'CAT#'
872 AND fsav.id_flex_num = gc_cat_flex_struc
873 AND fsav.attribute_value = 'Y'
874 AND fsav.segment_attribute_type = 'BASED_CATEGORY';
875
876 SELECT fsav.application_column_name
877 INTO lc_min_seg
878 FROM fnd_segment_attribute_values fsav
879 WHERE fsav.id_flex_code = 'CAT#'
880 AND fsav.id_flex_num = gc_cat_flex_struc
881 AND fsav.attribute_value = 'Y'
882 AND fsav.segment_attribute_type = 'MINOR_CATEGORY';
883
884 SELECT fifs.concatenated_segment_delimiter
885 INTO lc_separator
886 FROM fnd_id_flex_structures fifs
887 WHERE fifs.id_flex_num = gc_cat_flex_struc
888 AND fifs.id_flex_code = 'CAT#';
889
890 /*Added for bug13861637*/
891 select substr(lc_maj_seg,8) into lc_maj_segnum from dual;
892 select substr(lc_min_seg,8) into lc_min_segnum from dual;
893
894 --*************************************************
895 --Based on P_FROM_CATEGORY and P_TO_CATEGORY the
896 -- dynamic filter is created
897 --*************************************************
898
899 /*Start: Code added to get segment value based on segment number for bug13861637*/
900 IF P_FROM_CATEGORY IS NOT NULL AND TRIM(P_FROM_CATEGORY) <> lc_separator THEN
901 if lc_maj_segnum > 1 then
902 gc_from_maj_seg := LTRIM(SUBSTR(P_FROM_CATEGORY,INSTR(P_FROM_CATEGORY,lc_separator,1,lc_maj_segnum-1)),lc_separator);
903 if instr(gc_from_maj_seg,'.') > 0 then
904 gc_from_maj_seg := rtrim(substr(gc_from_maj_seg,1,INSTR(gc_from_maj_seg,lc_separator)),lc_separator);
905 end if;
906 else
907 gc_from_maj_seg := RTRIM(SUBSTR(P_FROM_CATEGORY,1,INSTR(P_FROM_CATEGORY,lc_separator)),lc_separator);
908 end if;
909
910 gc_from_min_seg := LTRIM(SUBSTR(P_FROM_CATEGORY,INSTR(P_FROM_CATEGORY,lc_separator,1,lc_min_segnum-1)),lc_separator);
911 if instr(gc_from_min_seg,'.') > 0 then
912 gc_from_min_seg := rtrim(substr(gc_from_min_seg,1,INSTR(gc_from_min_seg,lc_separator)),lc_separator);
913 end if;
914 ELSE
915 gc_from_maj_seg := 'A';
916 gc_from_min_seg := 'A';
917 END IF;
918 fnd_file.put_line(fnd_file.log, 'gc_from_maj_seg::'||gc_from_maj_seg);
919 fnd_file.put_line(fnd_file.log, 'gc_from_min_seg::'||gc_from_min_seg);
920
921 IF P_TO_CATEGORY IS NOT NULL AND TRIM(P_TO_CATEGORY) <> lc_separator THEN
922 if lc_maj_segnum > 1 then
923 gc_to_maj_seg := LTRIM(SUBSTR(P_TO_CATEGORY,INSTR(P_TO_CATEGORY,lc_separator,1,lc_maj_segnum-1)),lc_separator);
924 if instr(gc_to_maj_seg,'.') > 0 then
925 gc_to_maj_seg := rtrim(substr(gc_to_maj_seg,1,INSTR(gc_to_maj_seg,lc_separator)),lc_separator);
926 end if;
927 else
928 gc_to_maj_seg := RTRIM(SUBSTR(P_TO_CATEGORY,1,INSTR(P_TO_CATEGORY,lc_separator)),lc_separator);
929 end if;
930 if lc_min_segnum > 1 then
931 gc_to_min_seg := LTRIM(SUBSTR(P_TO_CATEGORY,INSTR(P_TO_CATEGORY,lc_separator,1,lc_min_segnum-1)),lc_separator);
932 if instr(gc_to_min_seg,'.') > 0 then
933 gc_to_min_seg := rtrim(substr(gc_to_min_seg,1,INSTR(gc_to_min_seg,lc_separator)),lc_separator);
934 end if;
935 else
936 gc_to_min_seg := RTRIM(SUBSTR(P_TO_CATEGORY,1,INSTR(P_TO_CATEGORY,lc_separator)),lc_separator);
940 gc_to_min_seg := 'Z';
937 end if;
938 ELSE
939 gc_to_maj_seg := 'Z';
941 END IF;
942 /*End: Code added to get segment value based on segment number for bug13861637*/
943 fnd_file.put_line(fnd_file.log, 'gc_to_maj_seg::'||gc_to_maj_seg);
944 fnd_file.put_line(fnd_file.log, 'gc_to_min_seg::'||gc_to_min_seg);
945
946 IF P_ASSET_DETAILS IS NULL THEN
947 gc_asset_details := 'N';
948 ELSE
949 gc_asset_details := P_ASSET_DETAILS;
950 END IF;
951
952 IF P_FROM_CATEGORY IS NOT NULL AND P_TO_CATEGORY IS NOT NULL THEN
953 IF gc_from_maj_seg <> gc_to_maj_seg THEN
954 gc_category_where := '((fc.'||lc_maj_seg||' = '''||gc_from_maj_seg||''' AND fc.'||lc_min_seg||' >= '''||gc_from_min_seg||''')
955 OR (fc.'||lc_maj_seg||' = '''||gc_to_maj_seg||''' AND fc.'||lc_min_seg||' <= '''||gc_to_min_seg||''')
956 OR (fc.'||lc_maj_seg||' > '''||gc_from_maj_seg||''' AND fc.'||lc_maj_seg||' < '''||gc_to_maj_seg||'''))';
957 ELSE
958 gc_category_where := ' ((fc.'||lc_maj_seg||' = '''||gc_from_maj_seg||''' AND fc.'||lc_min_seg||' BETWEEN '''||gc_from_min_seg||''' AND '''||gc_to_min_seg||'''))';
959 END IF;
960 END IF;
961
962 IF P_FROM_CATEGORY IS NULL AND P_TO_CATEGORY IS NULL THEN
963 gc_category_where := ' 1=1';
964 END IF;
965
966 IF P_FROM_CATEGORY IS NULL AND P_TO_CATEGORY IS NOT NULL THEN
967 IF gc_from_maj_seg <> gc_to_maj_seg THEN
968 gc_category_where := '((fc.'||lc_maj_seg||' = '''||gc_from_maj_seg||''' AND fc.'||lc_min_seg||' >= '''||gc_from_min_seg||''')
969 OR (fc.'||lc_maj_seg||' = '''||gc_to_maj_seg||''' AND fc.'||lc_min_seg||' <= '''||gc_to_min_seg||''')
970 OR (fc.'||lc_maj_seg||' > '''||gc_from_maj_seg||''' AND fc.'||lc_maj_seg||' < '''||gc_to_maj_seg||'''))';
971 ELSE
972 gc_category_where := ' ((fc.'||lc_maj_seg||' = '''||gc_from_maj_seg||''' AND fc.'||lc_min_seg||' BETWEEN '''||gc_from_min_seg||''' AND '''||gc_to_min_seg||'''))';
973 END IF;
974 END IF;
975
976 IF P_FROM_CATEGORY IS NOT NULL AND P_TO_CATEGORY IS NULL THEN
977 IF gc_from_maj_seg <> gc_to_maj_seg THEN
978 gc_category_where := '((fc.'||lc_maj_seg||' = '''||gc_from_maj_seg||''' AND fc.'||lc_min_seg||' >= '''||gc_from_min_seg||''')
979 OR (fc.'||lc_maj_seg||' = '''||gc_to_maj_seg||''' AND fc.'||lc_min_seg||' <= '''||gc_to_min_seg||''')
980 OR (fc.'||lc_maj_seg||' > '''||gc_from_maj_seg||''' AND fc.'||lc_maj_seg||' < '''||gc_to_maj_seg||'''))';
981 ELSE
982 gc_category_where := ' ((fc.'||lc_maj_seg||' = '''||gc_from_maj_seg||''' AND fc.'||lc_min_seg||' BETWEEN '''||gc_from_min_seg||''' AND '''||gc_to_min_seg||'''))';
983 END IF;
984 END IF;
985
986 fnd_file.put_line(fnd_file.log,'gc_asset_details::'||gc_asset_details);
987 fnd_file.put_line(fnd_file.log,'gc_category_where::'||gc_category_where);
988
989 --*************************************************
990 --Used to obtain the Begin period data
991 --*************************************************
992 SELECT FDP.period_counter
993 ,FDP.calendar_period_open_date
994 ,fdp.period_open_date
995 INTO gn_lex_begin_period_counter
996 ,gd_period_open_date
997 ,gd_per_open_date
998 FROM fa_deprn_periods FDP
999 WHERE FDP.book_type_code = P_BOOK_NAME
1000 AND FDP.period_name = P_BEGIN_PERIOD;
1001
1002 fnd_file.put_line(fnd_file.log,'gn_lex_begin_period_counter::'||gn_lex_begin_period_counter);
1003 fnd_file.put_line(fnd_file.log,'gd_period_open_date::'||gd_period_open_date);
1004 fnd_file.put_line(fnd_file.log,'P_BEGIN_PERIOD::'||P_BEGIN_PERIOD);
1005
1006 --*************************************************
1007 --Used to obtain the End period data
1008 --*************************************************
1009 SELECT FDP.period_counter
1010 ,NVL(FDP.calendar_period_close_date,SYSDATE)
1011 ,NVL(fdp.period_close_date,SYSDATE)
1012 INTO gn_lex_end_period_counter
1013 ,gd_period_close_date
1014 ,gd_per_close_date
1015 FROM fa_deprn_periods FDP
1016 WHERE FDP.book_type_code = P_BOOK_NAME
1017 AND FDP.period_name = P_END_PERIOD;
1018
1019 fnd_file.put_line(fnd_file.log,'gn_lex_end_period_counter::'||gn_lex_end_period_counter);
1020 fnd_file.put_line(fnd_file.log,'gd_period_close_date::'||gd_period_close_date);
1021 fnd_file.put_line(fnd_file.log,'gd_per_close_date::'||to_char(gd_per_close_date,'dd/mm/yyyy hh24:mi:ss'));
1022 fnd_file.put_line(fnd_file.log,'P_END_PERIOD::'||P_END_PERIOD);
1023
1024 --*************************************************
1025 --Used to obtain the Book Controls data
1026 --*************************************************
1027 SELECT FBC.book_class
1028 ,FBC.accounting_flex_structure
1029 ,FBC.set_of_books_id
1030 ,FBC.distribution_source_book
1031 INTO gc_book_class
1032 ,gc_acct_flex_struc
1033 ,lc_ledger_id
1034 ,P_DIST_SOURCE_BOOK
1035 FROM fa_book_controls FBC
1036 WHERE FBC.book_type_code = P_BOOK_NAME;
1037 fnd_file.put_line(fnd_file.log,'gc_book_class::'||gc_book_class);
1038
1039 --*************************************************
1040 --Used to obtain the Ledger Name AND Currency
1041 --*************************************************
1042 SELECT GLED.name
1043 ,GLED.currency_code
1044 INTO gc_ledger_name
1045 ,gc_currency_code
1046 FROM gl_ledgers GLED
1047 WHERE GLED.ledger_id = lc_ledger_id;
1048 fnd_file.put_line(fnd_file.log,'gc_ledger_name::'||gc_ledger_name);
1049
1050 RETURN (TRUE);
1051 END beforeReport;
1052
1053 END FA_ASSET_SUMM_RPT_PKG;
[Home] [Help]