[Home] [Help]
PACKAGE BODY: APPS.FA_BALREP_PKG
Source
1 PACKAGE BODY fa_balrep_pkg AS
2 /*$Header: fabalrepb.pls 120.15.12020000.2 2013/03/18 10:57:29 rmandali ship $*/
3 PROCEDURE load_workers(p_book_type_code IN VARCHAR2,
4 p_request_id IN NUMBER ,
5 p_errbuf OUT NOCOPY VARCHAR2,
6 p_retcode OUT NOCOPY NUMBER) IS
7 l_batch_size NUMBER;
8 p_total_requests NUMBER;
9
10 TYPE char_tbl_type IS TABLE OF VARCHAR2(120) INDEX BY BINARY_INTEGER;
11 TYPE num_tbl_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
12
13 l_rowid_tbl char_tbl_type;
14 l_worker_num_tbl num_tbl_type;
15
16 CURSOR c_workers(request_id_in NUMBER) IS
17 SELECT ROWID FROM fa_worker_jobs WHERE request_id = request_id_in;
18
19 BEGIN
20 fnd_profile.get('FA_NUM_PARALLEL_REQUESTS', p_total_requests);
21 fnd_profile.get('FA_BATCH_SIZE', l_batch_size);
22 l_batch_size := nvl(l_batch_size, 1000);
23 p_total_requests := nvl(p_total_requests, 1);
24
25 IF (NVL (p_total_requests, 0) > 0 and NVL (p_total_requests, 0) < 2)
26 THEN
27 BEGIN
28 INSERT INTO FA_WORKER_JOBS
29 ( START_RANGE
30 , END_RANGE
31 , WORKER_NUM
32 , STATUS
33 ,REQUEST_ID
34 )
35 SELECT min(asset_id),
36 max(asset_id),
37 1,
38 'UNASSIGNED',
39 p_request_id
40 FROM fa_books
41 WHERE book_type_code = p_book_type_code
42 AND transaction_header_id_out is null;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 raise;
47 END;
48 else
49 INSERT INTO fa_worker_jobs
50 (start_range, end_range, worker_num, status, request_id)
51 SELECT MIN(asset_id), MAX(asset_id), 0, 'UNASSIGNED', p_request_id
52 FROM (SELECT /*+ parallel(BK) */
53 asset_id,
54 floor(rank() over(ORDER BY asset_id) / l_batch_size) unit_id
55 FROM fa_books bk
56 WHERE bk.book_type_code = p_book_type_code
57 AND bk.transaction_header_id_out IS NULL)
58 GROUP BY unit_id;
59
60 OPEN c_workers(p_request_id);
61 LOOP
62 FETCH c_workers BULK COLLECT
63 INTO l_rowid_tbl;
64
65 IF l_rowid_tbl.count = 0 THEN
66 EXIT;
67 END IF;
68
69 FOR i IN 1 .. l_rowid_tbl.count LOOP
70 l_worker_num_tbl(i) := MOD(i, p_total_requests) + 1;
71
72 END LOOP;
73
74 FORALL i IN 1 .. l_rowid_tbl.count
75 UPDATE fa_worker_jobs
76 SET worker_num = l_worker_num_tbl(i)
77 WHERE ROWID = l_rowid_tbl(i);
78 END LOOP;
79 CLOSE c_workers;
80 END IF;
81 COMMIT; --ANUJ
82 p_retcode := 1;
83 EXCEPTION
84 WHEN OTHERS THEN
85 p_retcode := SQLCODE;
86 p_errbuf := SQLERRM;
87 IF (c_workers%ISOPEN) THEN
88 CLOSE c_workers;
89 END IF;
90 RAISE fnd_api.g_exc_unexpected_error;
91 END load_workers;
92
93 PROCEDURE launch_workers(book IN VARCHAR2,
94 report_type IN VARCHAR2,
95 report_style IN VARCHAR2,
96 l_request_id IN NUMBER,
97 period1_pc IN NUMBER,
98 period1_pod IN DATE,
99 period1_pcd IN DATE,
100 period2_pc IN NUMBER,
101 period2_pcd IN DATE,
102 distribution_source_book IN VARCHAR2,
103 p_total_requests1 IN NUMBER,
104 l_errbuf OUT NOCOPY VARCHAR2,
105 l_retcode OUT NOCOPY NUMBER) IS
106 child_request_id NUMBER;
107 BEGIN
108
109 FOR i IN 1 .. nvl(p_total_requests1, 1) LOOP
110
111 child_request_id := fnd_request.submit_request('OFA',
112 'RXFAPOGT',
113 NULL,
114 SYSDATE,
115 FALSE,
116 book,
117 report_type, --'COST'
118 report_style,
119 l_request_id,
120 i, --worker number
121 period1_pc,
122 period1_pod,
123 period1_pcd,
124 period2_pc,
125 period2_pcd,
126 distribution_source_book);
127
128 IF (child_request_id = 0) THEN
129 ROLLBACK;
130 --raise G_NO_CHILD_PROCESS;
131 END IF;
132 COMMIT;
133 END LOOP;
134 END launch_workers;
135
136 -- Bug 8902344 : Changed UNION to UNION ALL in all the inserts
137 PROCEDURE get_adjustments(book IN VARCHAR2,
138 distribution_source_book IN VARCHAR2,
139 period1_pc IN NUMBER,
140 period2_pc IN NUMBER,
141 report_type IN VARCHAR2,
142 balance_type IN VARCHAR2,
143 start_range IN NUMBER,
144 end_range IN NUMBER,
145 h_request_id IN NUMBER) IS
146 h_set_of_books_id NUMBER;
147 h_reporting_flag VARCHAR2(1);
148 BEGIN
149
150 -- get mrc related info
151 BEGIN
152 -- h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
153 SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
154 INTO h_set_of_books_id
155 FROM dual;
156
157 IF (h_set_of_books_id = -1) THEN
158 h_set_of_books_id := NULL;
159 END IF;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 h_set_of_books_id := NULL;
164 END;
165
166 IF (h_set_of_books_id IS NOT NULL) THEN
167 IF NOT
168 fa_cache_pkg.fazcsob(x_set_of_books_id => h_set_of_books_id,
169 x_mrc_sob_type_code => h_reporting_flag) THEN
170 RAISE fnd_api.g_exc_unexpected_error;
171 END IF;
172 ELSE
173 SELECT set_of_books_id
174 INTO h_set_of_books_id
175 FROM fa_book_controls
176 WHERE book_type_code = book;
177
178 h_reporting_flag := 'P';
179 END IF;
180
181 -- Fix for Bug #1892406. Run only if CRL not installed.
182 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N') THEN
183
184 IF (h_reporting_flag = 'R') THEN
185 /* Bug 7498880: Added new query for upgraded periods */
186 INSERT INTO fa_balances_reports_itf
187 (asset_id,
188 distribution_ccid,
189 adjustment_ccid,
190 category_books_account,
191 source_type_code,
192 amount,
193 request_id)
194 SELECT dh.asset_id,
195 dh.code_combination_id,
196 aj.code_combination_id,
197 NULL,
198 aj.source_type_code,
199 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
200 aj.adjustment_amount),
201 h_request_id
202 FROM fa_distribution_history dh,
203 fa_transaction_headers th,
204 fa_asset_history ah,
205 fa_adjustments_mrc_v aj,
206 fa_deprn_periods dp
207 WHERE dh.book_type_code = distribution_source_book
208 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
209 AND aj.asset_id = dh.asset_id
210 AND aj.book_type_code = book
211 AND aj.distribution_id = dh.distribution_id
212 AND aj.adjustment_type IN
213 (report_type,
214 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
215 AND aj.period_counter_created BETWEEN period1_pc AND period2_pc
216 AND dp.book_type_code = aj.book_type_code
217 AND dp.period_counter = aj.period_counter_created
218 AND dp.xla_conversion_status IS NOT NULL
219 AND th.transaction_header_id = aj.transaction_header_id
220 AND ah.asset_id = dh.asset_id
221 AND ((ah.asset_type <> 'EXPENSED' AND
222 report_type IN ('COST', 'CIP COST')) OR
223 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
224 report_type IN ('RESERVE', 'REVAL RESERVE')))
225 AND th.transaction_header_id BETWEEN
226 ah.transaction_header_id_in AND
227 nvl(ah.transaction_header_id_out - 1,
228 th.transaction_header_id)
229 AND (decode(report_type, aj.adjustment_type, 1, 0) *
230 aj.adjustment_amount) <> 0
231 GROUP BY dh.asset_id,
232 dh.code_combination_id,
233 aj.code_combination_id,
234 aj.source_type_code
235 UNION ALL
236 SELECT dh.asset_id,
237 dh.code_combination_id,
238 lines.code_combination_id, --AJ.Code_Combination_ID,
239 NULL,
240 aj.source_type_code,
241 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
242 aj.adjustment_amount),
243 h_request_id
244 FROM fa_distribution_history dh,
245 fa_transaction_headers th,
246 fa_asset_history ah,
247 fa_adjustments_mrc_v aj,
248 fa_deprn_periods dp
249 /* SLA Changes */,
250 xla_ae_headers headers,
251 xla_ae_lines lines,
252 xla_distribution_links links
253 WHERE dh.book_type_code = distribution_source_book
254 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
255 AND aj.asset_id = dh.asset_id
256 AND aj.book_type_code = book
257 AND aj.distribution_id = dh.distribution_id
258 AND aj.adjustment_type IN
259 (report_type,
260 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
261 AND aj.period_counter_created BETWEEN period1_pc AND
262 period2_pc
263 AND dp.book_type_code = aj.book_type_code
264 AND dp.period_counter = aj.period_counter_created
265 AND dp.xla_conversion_status IS NULL
266 AND th.transaction_header_id = aj.transaction_header_id
267 AND ah.asset_id = dh.asset_id
268 AND ((ah.asset_type <> 'EXPENSED' AND
269 report_type IN ('COST', 'CIP COST')) OR
270 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
271 report_type IN ('RESERVE', 'REVAL RESERVE')))
272 AND th.transaction_header_id BETWEEN
273 ah.transaction_header_id_in AND
274 nvl(ah.transaction_header_id_out - 1,
275 th.transaction_header_id)
276 AND (decode(report_type, aj.adjustment_type, 1, 0) *
277 aj.adjustment_amount) <> 0
278 /* SLA Changes */
279 AND links.source_distribution_id_num_1 =
280 aj.transaction_header_id
281 AND links.source_distribution_id_num_2 = aj.adjustment_line_id
282 AND links.application_id = 140
283 AND links.source_distribution_type = 'TRX'
284 AND headers.application_id = 140
285 AND headers.ae_header_id = links.ae_header_id
286 AND headers.ledger_id = h_set_of_books_id
287 AND lines.ae_header_id = links.ae_header_id
288 AND lines.ae_line_num = links.ae_line_num
289 AND lines.application_id = 140
290 GROUP BY dh.asset_id,
291 dh.code_combination_id,
292 lines.code_combination_id, --AJ.Code_Combination_ID,
293 aj.source_type_code;
294
295 ELSE
296 /* Bug 7498880: Added new query for upgraded periods */
297 INSERT INTO fa_balances_reports_itf
298 (asset_id,
299 distribution_ccid,
300 adjustment_ccid,
301 category_books_account,
302 source_type_code,
303 amount,
304 request_id)
305 SELECT dh.asset_id,
306 dh.code_combination_id,
307 aj.code_combination_id,
308 NULL,
309 aj.source_type_code,
310 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
311 aj.adjustment_amount),
312 h_request_id
313 FROM fa_distribution_history dh,
314 fa_transaction_headers th,
315 fa_asset_history ah,
316 fa_adjustments aj,
317 fa_deprn_periods dp
318 WHERE dh.book_type_code = distribution_source_book
319 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
320 AND aj.asset_id = dh.asset_id
321 AND aj.book_type_code = book
322 AND aj.distribution_id = dh.distribution_id
323 AND aj.adjustment_type IN
324 (report_type,
325 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
326 AND aj.period_counter_created BETWEEN period1_pc AND
327 period2_pc
328 AND dp.book_type_code = aj.book_type_code
329 AND dp.period_counter = aj.period_counter_created
330 AND dp.xla_conversion_status IS NOT NULL
331 AND aj.code_combination_id IS NOT NULL -- suju
332 AND th.transaction_header_id = aj.transaction_header_id
333 AND ah.asset_id = dh.asset_id
334 AND ((ah.asset_type <> 'EXPENSED' AND
335 report_type IN ('COST', 'CIP COST')) OR
336 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
337 report_type IN ('RESERVE', 'REVAL RESERVE')))
338 AND th.transaction_header_id BETWEEN
339 ah.transaction_header_id_in AND
340 nvl(ah.transaction_header_id_out - 1,
341 th.transaction_header_id)
342 AND (decode(report_type, aj.adjustment_type, 1, 0) *
343 aj.adjustment_amount) <> 0
344 GROUP BY dh.asset_id,
345 dh.code_combination_id,
346 aj.code_combination_id,
347 aj.source_type_code
348 UNION ALL
349 SELECT dh.asset_id,
350 dh.code_combination_id,
351 lines.code_combination_id, --AJ.Code_Combination_ID,
352 NULL,
353 aj.source_type_code,
354 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
355 aj.adjustment_amount),
356 h_request_id
357 FROM fa_distribution_history dh,
358 fa_transaction_headers th,
359 fa_asset_history ah,
360 fa_adjustments aj,
361 fa_deprn_periods dp
362 /* SLA Changes */,
363 xla_ae_headers headers,
364 xla_ae_lines lines,
365 xla_distribution_links links
366 WHERE dh.book_type_code = distribution_source_book
367 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
368 AND aj.asset_id = dh.asset_id
369 AND aj.book_type_code = book
370 AND aj.distribution_id = dh.distribution_id
371 AND aj.adjustment_type IN
372 (report_type,
373 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
374 AND aj.period_counter_created BETWEEN period1_pc AND
375 period2_pc
376 AND dp.book_type_code = aj.book_type_code
377 AND dp.period_counter = aj.period_counter_created
378 AND (dp.xla_conversion_status IS NULL OR
379 aj.code_combination_id IS NULL)
380 AND th.transaction_header_id = aj.transaction_header_id
381 AND ah.asset_id = dh.asset_id
382 AND ((ah.asset_type <> 'EXPENSED' AND
383 report_type IN ('COST', 'CIP COST')) OR
384 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
385 report_type IN ('RESERVE', 'REVAL RESERVE')))
386 AND th.transaction_header_id BETWEEN
387 ah.transaction_header_id_in AND
388 nvl(ah.transaction_header_id_out - 1,
389 th.transaction_header_id)
390 AND (decode(report_type, aj.adjustment_type, 1, 0) *
391 aj.adjustment_amount) <> 0
392 /* SLA Changes */
393 AND links.source_distribution_id_num_1 =
394 aj.transaction_header_id
395 AND links.source_distribution_id_num_2 = aj.adjustment_line_id
396 AND links.application_id = 140
397 AND links.source_distribution_type = 'TRX'
398 AND headers.application_id = 140
399 AND headers.ae_header_id = links.ae_header_id
400 AND headers.ledger_id = h_set_of_books_id
401 AND lines.ae_header_id = links.ae_header_id
402 AND lines.ae_line_num = links.ae_line_num
403 AND lines.application_id = 140
404 GROUP BY dh.asset_id,
405 dh.code_combination_id,
406 lines.code_combination_id, --AJ.Code_Combination_ID,
407 aj.source_type_code;
408 END IF;
409
410 -- Fix for Bug #1892406. Run only if CRL installed.
411 ELSIF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
412
413 IF (h_reporting_flag = 'R') THEN
414 /* Bug 7498880: Added new query for upgraded periods */
415 INSERT INTO fa_balances_reports_itf
416 (asset_id,
417 distribution_ccid,
418 adjustment_ccid,
419 category_books_account,
420 source_type_code,
421 amount,
422 request_id)
423 SELECT dh.asset_id,
424 dh.code_combination_id,
425 aj.code_combination_id,
426 NULL,
427 aj.source_type_code,
428 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
429 aj.adjustment_amount),
430 h_request_id
431 FROM fa_distribution_history dh,
432 fa_transaction_headers th,
433 fa_asset_history ah,
434 fa_adjustments_mrc_v aj,
435 fa_deprn_periods dp
436 WHERE dh.book_type_code = distribution_source_book
437 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
438 AND aj.asset_id = dh.asset_id
439 AND aj.book_type_code = book
440 AND aj.distribution_id = dh.distribution_id
441 AND aj.adjustment_type IN
442 (report_type,
443 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
444 AND aj.period_counter_created BETWEEN period1_pc AND
445 period2_pc
446 AND dp.book_type_code = aj.book_type_code
447 AND dp.period_counter = aj.period_counter_created
448 AND dp.xla_conversion_status IS NOT NULL
449 AND th.transaction_header_id = aj.transaction_header_id
450 AND ah.asset_id = dh.asset_id
451 AND ((ah.asset_type <> 'EXPENSED' AND
452 report_type IN ('COST', 'CIP COST')) OR
453 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
454 report_type IN ('RESERVE', 'REVAL RESERVE')))
455 AND th.transaction_header_id BETWEEN
456 ah.transaction_header_id_in AND
457 nvl(ah.transaction_header_id_out - 1,
458 th.transaction_header_id)
459 AND (decode(report_type, aj.adjustment_type, 1, 0) *
460 aj.adjustment_amount) <> 0
461 -- start of cua
462 AND NOT EXISTS
463 (SELECT 'x'
464 FROM fa_books_mrc_v bks
465 WHERE bks.book_type_code = book
466 AND bks.asset_id = aj.asset_id
467 AND bks.group_asset_id IS NOT NULL
468 AND bks.date_ineffective IS NOT NULL)
469 -- end of cua
470 GROUP BY dh.asset_id,
471 dh.code_combination_id,
472 aj.code_combination_id,
473 aj.source_type_code
474 UNION ALL
475 SELECT dh.asset_id,
476 dh.code_combination_id,
477 lines.code_combination_id, --AJ.Code_Combination_ID,
478 NULL,
479 aj.source_type_code,
480 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
481 aj.adjustment_amount),
482 h_request_id
483 FROM fa_distribution_history dh,
484 fa_transaction_headers th,
485 fa_asset_history ah,
486 fa_adjustments_mrc_v aj,
487 fa_deprn_periods dp
488 /* SLA Changes */,
489 xla_ae_headers headers,
490 xla_ae_lines lines,
491 xla_distribution_links links
492 WHERE dh.book_type_code = distribution_source_book
493 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
494 AND aj.asset_id = dh.asset_id
495 AND aj.book_type_code = book
496 AND aj.distribution_id = dh.distribution_id
497 AND aj.adjustment_type IN
498 (report_type,
499 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
500 AND aj.period_counter_created BETWEEN period1_pc AND
501 period2_pc
502 AND dp.book_type_code = aj.book_type_code
503 AND dp.period_counter = aj.period_counter_created
504 AND dp.xla_conversion_status IS NULL
505 AND th.transaction_header_id = aj.transaction_header_id
506 AND ah.asset_id = dh.asset_id
507 AND ((ah.asset_type <> 'EXPENSED' AND
508 report_type IN ('COST', 'CIP COST')) OR
509 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
510 report_type IN ('RESERVE', 'REVAL RESERVE')))
511 AND th.transaction_header_id BETWEEN
512 ah.transaction_header_id_in AND
513 nvl(ah.transaction_header_id_out - 1,
514 th.transaction_header_id)
515 AND (decode(report_type, aj.adjustment_type, 1, 0) *
516 aj.adjustment_amount) <> 0
517 -- start of cua
518 AND NOT EXISTS
519 (SELECT 'x'
520 FROM fa_books_mrc_v bks
521 WHERE bks.book_type_code = book
522 AND bks.asset_id = aj.asset_id
523 AND bks.group_asset_id IS NOT NULL
524 AND bks.date_ineffective IS NOT NULL)
525 -- end of cua
526 /* SLA Changes */
527 AND links.source_distribution_id_num_1 =
528 aj.transaction_header_id
529 AND links.source_distribution_id_num_2 = aj.adjustment_line_id
530 AND links.application_id = 140
531 AND links.source_distribution_type = 'TRX'
532 AND headers.application_id = 140
533 AND headers.ae_header_id = links.ae_header_id
534 AND headers.ledger_id = h_set_of_books_id
535 AND lines.ae_header_id = links.ae_header_id
536 AND lines.ae_line_num = links.ae_line_num
537 AND lines.application_id = 140
538 GROUP BY dh.asset_id,
539 dh.code_combination_id,
540 lines.code_combination_id, --AJ.Code_Combination_ID,
541 aj.source_type_code;
542
543 ELSE
544 /* Bug 7498880: Added new query for upgraded periods */
545 INSERT INTO fa_balances_reports_itf
546 (asset_id,
547 distribution_ccid,
548 adjustment_ccid,
549 category_books_account,
550 source_type_code,
551 amount,
552 request_id)
553 SELECT dh.asset_id,
554 dh.code_combination_id,
555 aj.code_combination_id,
556 NULL,
557 aj.source_type_code,
558 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
559 aj.adjustment_amount),
560 h_request_id
561 FROM fa_distribution_history dh,
562 fa_transaction_headers th,
563 fa_asset_history ah,
564 fa_adjustments aj,
565 fa_deprn_periods dp
566
567 WHERE dh.book_type_code = distribution_source_book
568 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
569 AND aj.asset_id = dh.asset_id
570 AND aj.book_type_code = book
571 AND aj.distribution_id = dh.distribution_id
572 AND aj.adjustment_type IN
573 (report_type,
574 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
575 AND aj.period_counter_created BETWEEN period1_pc AND
576 period2_pc
577 AND dp.book_type_code = aj.book_type_code
578 AND dp.period_counter = aj.period_counter_created
579 AND dp.xla_conversion_status IS NOT NULL
580 AND th.transaction_header_id = aj.transaction_header_id
581 AND ah.asset_id = dh.asset_id
582 AND ((ah.asset_type <> 'EXPENSED' AND
583 report_type IN ('COST', 'CIP COST')) OR
584 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
585 report_type IN ('RESERVE', 'REVAL RESERVE')))
586 AND th.transaction_header_id BETWEEN
587 ah.transaction_header_id_in AND
588 nvl(ah.transaction_header_id_out - 1,
589 th.transaction_header_id)
590 AND (decode(report_type, aj.adjustment_type, 1, 0) *
591 aj.adjustment_amount) <> 0
592 -- start of cua
593 AND NOT EXISTS
594 (SELECT 'x'
595 FROM fa_books bks
596 WHERE bks.book_type_code = book
597 AND bks.asset_id = aj.asset_id
598 AND bks.group_asset_id IS NOT NULL
599 AND bks.date_ineffective IS NOT NULL)
600 -- end of cua
601 GROUP BY dh.asset_id,
602 dh.code_combination_id,
603 aj.code_combination_id,
604 aj.source_type_code
605 UNION ALL
606 SELECT dh.asset_id,
607 dh.code_combination_id,
608 lines.code_combination_id, --AJ.Code_Combination_ID,
609 NULL,
610 aj.source_type_code,
611 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
612 aj.adjustment_amount),
613 h_request_id
614 FROM fa_distribution_history dh,
615 fa_transaction_headers th,
616 fa_asset_history ah,
617 fa_adjustments aj,
618 fa_deprn_periods dp
619 /* SLA Changes */,
620 xla_ae_headers headers,
621 xla_ae_lines lines,
622 xla_distribution_links links
623 WHERE dh.book_type_code = distribution_source_book
624 AND dh.asset_id BETWEEN start_range AND end_range --Anuj
625 AND aj.asset_id = dh.asset_id
626 AND aj.book_type_code = book
627 AND aj.distribution_id = dh.distribution_id
628 AND aj.adjustment_type IN
629 (report_type,
630 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
631 AND aj.period_counter_created BETWEEN period1_pc AND
632 period2_pc
633 AND dp.book_type_code = aj.book_type_code
634 AND dp.period_counter = aj.period_counter_created
635 AND dp.xla_conversion_status IS NULL
636 AND th.transaction_header_id = aj.transaction_header_id
637 AND ah.asset_id = dh.asset_id
638 AND ((ah.asset_type <> 'EXPENSED' AND
639 report_type IN ('COST', 'CIP COST')) OR
640 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
641 report_type IN ('RESERVE', 'REVAL RESERVE')))
642 AND th.transaction_header_id BETWEEN
643 ah.transaction_header_id_in AND
644 nvl(ah.transaction_header_id_out - 1,
645 th.transaction_header_id)
646 AND (decode(report_type, aj.adjustment_type, 1, 0) *
647 aj.adjustment_amount) <> 0
648 -- start of cua
649 AND NOT EXISTS
650 (SELECT 'x'
651 FROM fa_books bks
652 WHERE bks.book_type_code = book
653 AND bks.asset_id = aj.asset_id
654 AND bks.group_asset_id IS NOT NULL
655 AND bks.date_ineffective IS NOT NULL)
656 -- end of cua
657 /* SLA Changes */
658 AND links.source_distribution_id_num_1 =
659 aj.transaction_header_id
660 AND links.source_distribution_id_num_2 = aj.adjustment_line_id
661 AND links.application_id = 140
662 AND links.source_distribution_type = 'TRX'
663 AND headers.application_id = 140
664 AND headers.ae_header_id = links.ae_header_id
665 AND headers.ledger_id = h_set_of_books_id
666 AND lines.ae_header_id = links.ae_header_id
667 AND lines.ae_line_num = links.ae_line_num
668 AND lines.application_id = 140
669 GROUP BY dh.asset_id,
670 dh.code_combination_id,
671 lines.code_combination_id, --AJ.Code_Combination_ID,
672 aj.source_type_code;
673
674 END IF;
675
676 END IF;
677
678 IF report_type = 'RESERVE' THEN
679 IF (h_reporting_flag = 'R') THEN
680 INSERT INTO fa_balances_reports_itf
681 (asset_id,
682 distribution_ccid,
683 adjustment_ccid,
684 category_books_account,
685 source_type_code,
686 amount,
687 request_id)
688 SELECT dh.asset_id,
689 dh.code_combination_id,
690 NULL,
691 cb.deprn_reserve_acct,
692 'ADDITION',
693 SUM(dd.deprn_reserve),
694 h_request_id
695 FROM fa_distribution_history dh,
696 fa_category_books cb,
697 fa_asset_history ah,
698 fa_deprn_detail_mrc_v dd
699 WHERE NOT EXISTS
700 (SELECT asset_id
701 FROM fa_balances_reports_itf
702 WHERE asset_id = dh.asset_id
703 AND distribution_ccid = dh.code_combination_id
704 AND source_type_code = 'ADDITION'
705 AND request_id = h_request_id)
706 AND dd.book_type_code = book
707 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
708 AND (dd.period_counter + 1) BETWEEN period1_pc AND period2_pc
709 AND dd.deprn_source_code = 'B'
710 AND dd.asset_id = dh.asset_id
711 AND dd.deprn_reserve <> 0
712 AND dd.distribution_id = dh.distribution_id
713 AND dd.asset_id = ah.asset_id
714 AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
715 AND nvl(dh.date_ineffective, SYSDATE) <=
716 nvl(ah.date_ineffective, SYSDATE)
717 AND dd.book_type_code = cb.book_type_code
718 AND ah.category_id = cb.category_id
719 GROUP BY dh.asset_id,
720 dh.code_combination_id,
721 cb.deprn_reserve_acct;
722 ELSE
723 INSERT INTO fa_balances_reports_itf
724 (asset_id,
725 distribution_ccid,
726 adjustment_ccid,
727 category_books_account,
728 source_type_code,
729 amount,
730 request_id)
731 SELECT dh.asset_id,
732 dh.code_combination_id,
733 NULL,
734 cb.deprn_reserve_acct,
735 'ADDITION',
736 SUM(nvl(dd.deprn_reserve, 0)),
737 h_request_id
738 FROM fa_distribution_history dh,
739 fa_category_books cb,
740 fa_asset_history ah,
741 fa_book_controls bc,
742 fa_deprn_detail dd
743 WHERE NOT EXISTS
744 (SELECT asset_id
745 FROM fa_balances_reports_itf
746 WHERE asset_id = dh.asset_id
747 AND distribution_ccid = dh.code_combination_id
748 AND source_type_code = 'ADDITION'
749 AND request_id = h_request_id)
750 AND dd.book_type_code = book
751 AND bc.book_type_code = book
752 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
753 AND (dd.period_counter + 1) BETWEEN period1_pc AND period2_pc
754 AND dd.deprn_source_code = 'B'
755 AND dd.asset_id = dh.asset_id
756 AND bc.distribution_source_book = dh.book_type_code
757 AND dd.deprn_reserve <> 0
758 AND dd.distribution_id = dh.distribution_id
759 AND dd.asset_id = ah.asset_id
760 AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
761 AND nvl(dh.date_ineffective, SYSDATE) <=
762 nvl(ah.date_ineffective, SYSDATE)
763 AND dd.book_type_code = cb.book_type_code
764 AND ah.category_id = cb.category_id
765 GROUP BY dh.asset_id,
766 dh.code_combination_id,
767 cb.deprn_reserve_acct;
768 END IF;
769
770 END IF;
771
772 END get_adjustments;
773
774 -- Bug 8902344 : Changed UNION to UNION ALL in all the inserts
775 PROCEDURE get_adjustments_for_group(book IN VARCHAR2,
776 distribution_source_book IN VARCHAR2,
777 period1_pc IN NUMBER,
778 period2_pc IN NUMBER,
779 report_type IN VARCHAR2,
780 balance_type IN VARCHAR2,
781 start_range IN NUMBER,
782 end_range IN NUMBER,
783 h_request_id IN NUMBER) IS
784 h_set_of_books_id NUMBER;
785 h_reporting_flag VARCHAR2(1);
786 BEGIN
787
788 -- get mrc related info
789 BEGIN
790 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
791 SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
792 INTO h_set_of_books_id
793 FROM dual;
794
795 IF (h_set_of_books_id = -1) THEN
796 h_set_of_books_id := NULL;
797 END IF;
798
799 EXCEPTION
800 WHEN OTHERS THEN
801 h_set_of_books_id := NULL;
802 END;
803
804 IF (h_set_of_books_id IS NOT NULL) THEN
805 IF NOT
806 fa_cache_pkg.fazcsob(x_set_of_books_id => h_set_of_books_id,
807 x_mrc_sob_type_code => h_reporting_flag) THEN
808 RAISE fnd_api.g_exc_unexpected_error;
809 END IF;
810 ELSE
811 SELECT set_of_books_id
812 INTO h_set_of_books_id
813 FROM fa_book_controls
814 WHERE book_type_code = book;
815
816 h_reporting_flag := 'P';
817 END IF;
818
819 -- run only if CRL installed
820 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
821
822 IF (h_reporting_flag = 'R') THEN
823 /* Bug 7498880: Added new query for upgraded periods */
824 INSERT INTO fa_balances_reports_itf
825 (asset_id,
826 distribution_ccid,
827 adjustment_ccid,
828 category_books_account,
829 source_type_code,
830 amount,
831 request_id)
832 SELECT aj.asset_id,
833 -- Changed for BMA1
834 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
835 gad.deprn_expense_acct_ccid,
836 decode(aj.adjustment_type,
837 'COST',
838 gad.asset_cost_acct_ccid,
839 aj.code_combination_id),
840 NULL,
841 aj.source_type_code,
842 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
843 aj.adjustment_amount),
844 h_request_id
845 FROM fa_adjustments_mrc_v aj,
846 fa_books_mrc_v bk,
847 fa_group_asset_default gad,
848 fa_deprn_periods dp
849 WHERE bk.asset_id = aj.asset_id
850 AND bk.book_type_code = book
851 AND bk.group_asset_id = gad.group_asset_id
852 AND bk.book_type_code = gad.book_type_code
853 AND bk.date_ineffective IS NULL
854 AND aj.asset_id IN
855 (SELECT asset_id
856 FROM fa_books_mrc_v
857 WHERE group_asset_id IS NOT NULL
858 AND date_ineffective IS NULL)
859 AND aj.asset_id = bk.asset_id
860 AND aj.asset_id BETWEEN start_range AND end_range --anuj
861 AND aj.book_type_code = book
862 AND aj.adjustment_type IN
863 (report_type,
864 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
865 AND aj.period_counter_created BETWEEN period1_pc AND
866 period2_pc
867 AND dp.book_type_code = aj.book_type_code
868 AND dp.period_counter = aj.period_counter_created
869 AND dp.xla_conversion_status IS NOT NULL
870 AND (decode(report_type, aj.adjustment_type, 1, 0) *
871 aj.adjustment_amount) <> 0
872 GROUP BY aj.asset_id,
873 -- Changed for BMA1
874 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
875 gad.deprn_expense_acct_ccid,
876 decode(aj.adjustment_type,
877 'COST',
878 gad.asset_cost_acct_ccid,
879 aj.code_combination_id),
880 aj.source_type_code
881 UNION ALL
882 SELECT aj.asset_id,
883 -- Changed for BMA1
884 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
885 gad.deprn_expense_acct_ccid,
886 decode(aj.adjustment_type,
887 'COST',
888 gad.asset_cost_acct_ccid,
889 lines.code_combination_id /*AJ.Code_Combination_ID*/),
890 NULL,
891 aj.source_type_code,
892 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
893 aj.adjustment_amount),
894 h_request_id
895 FROM fa_adjustments_mrc_v aj,
896 fa_books_mrc_v bk,
897 fa_group_asset_default gad,
898 fa_deprn_periods dp
899 /* SLA Changes */,
900 xla_ae_headers headers,
901 xla_ae_lines lines,
902 xla_distribution_links links
903 WHERE bk.asset_id = aj.asset_id
904 AND bk.book_type_code = book
905 AND bk.group_asset_id = gad.group_asset_id
906 AND bk.book_type_code = gad.book_type_code
907 AND bk.date_ineffective IS NULL
908 AND aj.asset_id BETWEEN start_range AND end_range --anuj
909 AND aj.asset_id IN
910 (SELECT asset_id
911 FROM fa_books_mrc_v
912 WHERE group_asset_id IS NOT NULL
913 AND date_ineffective IS NULL)
914 AND aj.asset_id = bk.asset_id
915 AND aj.book_type_code = book
916 AND aj.adjustment_type IN
917 (report_type,
918 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
919 AND aj.period_counter_created BETWEEN period1_pc AND
920 period2_pc
921 AND dp.book_type_code = aj.book_type_code
922 AND dp.period_counter = aj.period_counter_created
923 AND dp.xla_conversion_status IS NULL
924 AND (decode(report_type, aj.adjustment_type, 1, 0) *
925 aj.adjustment_amount) <> 0
926 /* SLA Changes */
927 AND links.source_distribution_id_num_1 =
928 aj.transaction_header_id
929 AND links.source_distribution_id_num_2 = aj.adjustment_line_id
930 AND links.application_id = 140
931 AND links.source_distribution_type = 'TRX'
932 AND headers.application_id = 140
933 AND headers.ae_header_id = links.ae_header_id
934 AND headers.ledger_id = h_set_of_books_id
935 AND lines.ae_header_id = links.ae_header_id
936 AND lines.ae_line_num = links.ae_line_num
937 AND lines.application_id = 140
938 GROUP BY aj.asset_id,
939 -- Changed for BMA1
940 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
941 gad.deprn_expense_acct_ccid,
942 decode(aj.adjustment_type,
943 'COST',
944 gad.asset_cost_acct_ccid,
945 lines.code_combination_id /*AJ.Code_Combination_ID*/),
946 aj.source_type_code;
947 ELSE
948 /* Bug 7498880: Added new query for upgraded periods */
949 INSERT INTO fa_balances_reports_itf
950 (asset_id,
951 distribution_ccid,
952 adjustment_ccid,
953 category_books_account,
954 source_type_code,
955 amount,
956 request_id)
957 SELECT aj.asset_id,
958 -- Changed for BMA1
959 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
960 gad.deprn_expense_acct_ccid,
961 decode(aj.adjustment_type,
962 'COST',
963 gad.asset_cost_acct_ccid,
964 aj.code_combination_id),
965 NULL,
966 aj.source_type_code,
967 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
968 aj.adjustment_amount),
969 h_request_id
970 FROM fa_adjustments aj,
971 fa_books bk,
972 fa_group_asset_default gad,
973 fa_deprn_periods dp
974 WHERE bk.asset_id = aj.asset_id
975 AND bk.book_type_code = book
976 AND aj.asset_id BETWEEN start_range AND end_range --anuj
977 AND bk.group_asset_id = gad.group_asset_id
978 AND bk.book_type_code = gad.book_type_code
979 AND bk.date_ineffective IS NULL
980 AND aj.asset_id IN
981 (SELECT asset_id
982 FROM fa_books
983 WHERE group_asset_id IS NOT NULL
984 AND date_ineffective IS NULL)
985 AND aj.asset_id = bk.asset_id
986 AND aj.book_type_code = book
987 AND aj.adjustment_type IN
988 (report_type,
989 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
990 AND aj.period_counter_created BETWEEN period1_pc AND
991 period2_pc
992 AND dp.book_type_code = aj.book_type_code
993 AND dp.period_counter = aj.period_counter_created
994 AND dp.xla_conversion_status IS NOT NULL
995 AND (decode(report_type, aj.adjustment_type, 1, 0) *
996 aj.adjustment_amount) <> 0
997 GROUP BY aj.asset_id,
998 -- Changed for BMA1
999 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
1000 gad.deprn_expense_acct_ccid,
1001 decode(aj.adjustment_type,
1002 'COST',
1003 gad.asset_cost_acct_ccid,
1004 aj.code_combination_id),
1005 aj.source_type_code
1006 UNION ALL
1007 SELECT aj.asset_id,
1008 -- Changed for BMA1
1009 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
1010 gad.deprn_expense_acct_ccid,
1011 decode(aj.adjustment_type,
1012 'COST',
1013 gad.asset_cost_acct_ccid,
1014 lines.code_combination_id /*AJ.Code_Combination_ID*/),
1015 NULL,
1016 aj.source_type_code,
1017 SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
1018 aj.adjustment_amount),
1019 h_request_id
1020 FROM fa_adjustments aj,
1021 fa_books bk,
1022 fa_group_asset_default gad,
1023 fa_deprn_periods dp
1024
1025 /* SLA Changes */,
1026 xla_ae_headers headers,
1027 xla_ae_lines lines,
1028 xla_distribution_links links
1029 WHERE bk.asset_id = aj.asset_id
1030 AND bk.book_type_code = book
1031 AND aj.asset_id BETWEEN start_range AND end_range --anuj
1032 AND bk.group_asset_id = gad.group_asset_id
1033 AND bk.book_type_code = gad.book_type_code
1034 AND bk.date_ineffective IS NULL
1035 AND aj.asset_id IN
1036 (SELECT asset_id
1037 FROM fa_books
1038 WHERE group_asset_id IS NOT NULL
1039 AND date_ineffective IS NULL)
1040 AND aj.asset_id = bk.asset_id
1041 AND aj.book_type_code = book
1042 AND aj.adjustment_type IN
1043 (report_type,
1044 decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
1045 AND aj.period_counter_created BETWEEN period1_pc AND
1046 period2_pc
1047 AND dp.book_type_code = aj.book_type_code
1048 AND dp.period_counter = aj.period_counter_created
1049 AND dp.xla_conversion_status IS NULL
1050 AND (decode(report_type, aj.adjustment_type, 1, 0) *
1051 aj.adjustment_amount) <> 0
1052
1053 /* SLA Changes */
1054 AND links.source_distribution_id_num_1 =
1055 aj.transaction_header_id
1056 AND links.source_distribution_id_num_2 = aj.adjustment_line_id
1057 AND links.application_id = 140
1058 AND links.source_distribution_type = 'TRX'
1059 AND headers.application_id = 140
1060 AND headers.ae_header_id = links.ae_header_id
1061 AND headers.ledger_id = h_set_of_books_id
1062 AND lines.ae_header_id = links.ae_header_id
1063 AND lines.ae_line_num = links.ae_line_num
1064 AND lines.application_id = 140
1065 GROUP BY aj.asset_id,
1066 -- Changed for BMA1
1067 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
1068 gad.deprn_expense_acct_ccid,
1069 decode(aj.adjustment_type,
1070 'COST',
1071 gad.asset_cost_acct_ccid,
1072 lines.code_combination_id /* AJ.Code_Combination_ID*/),
1073 aj.source_type_code;
1074 END IF;
1075
1076 END IF;
1077
1078 END get_adjustments_for_group;
1079
1080 PROCEDURE get_balance(book IN VARCHAR2,
1081 distribution_source_book IN VARCHAR2,
1082 period_pc IN NUMBER,
1083 earliest_pc IN NUMBER,
1084 period_date IN DATE,
1085 additions_date IN DATE,
1086 report_type IN VARCHAR2,
1087 balance_type IN VARCHAR2,
1088 begin_or_end IN VARCHAR2,
1089 start_range IN NUMBER,
1090 end_range IN NUMBER,
1091 h_request_id IN NUMBER) IS
1092 p_date DATE := period_date;
1093 a_date DATE := additions_date;
1094 h_set_of_books_id NUMBER;
1095 h_reporting_flag VARCHAR2(1);
1096 h_book_id VARCHAR2(240);
1097 BEGIN
1098
1099 -- get mrc related info
1100 BEGIN
1101 SELECT argument2
1102 INTO h_book_id
1103 FROM fnd_concurrent_requests
1104 WHERE request_id = h_request_id;
1105
1106 fnd_client_info.set_currency_context(to_number(h_book_id));
1107
1108 SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
1109 INTO h_set_of_books_id
1110 FROM dual;
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 h_set_of_books_id := NULL;
1115 END;
1116
1117 IF (h_set_of_books_id IS NOT NULL) THEN
1118 IF NOT
1119 fa_cache_pkg.fazcsob(x_set_of_books_id => h_set_of_books_id,
1120 x_mrc_sob_type_code => h_reporting_flag) THEN
1121 RAISE fnd_api.g_exc_unexpected_error;
1122 END IF;
1123 ELSE
1124 h_reporting_flag := 'P';
1125 END IF;
1126
1127 -- Fix for Bug #1892406. Run only if CRL not installed.
1128 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N') THEN
1129
1130 IF (h_reporting_flag = 'R') THEN
1131
1132 INSERT INTO fa_balances_reports_itf
1133 (asset_id,
1134 distribution_ccid,
1135 adjustment_ccid,
1136 category_books_account,
1137 source_type_code,
1138 amount,
1139 request_id)
1140 SELECT dh.asset_id,
1141 dh.code_combination_id,
1142 NULL,
1143 decode(report_type,
1144 'COST',
1145 cb.asset_cost_acct,
1146 'CIP COST',
1147 cb.cip_cost_acct,
1148 'RESERVE',
1149 cb.deprn_reserve_acct,
1150 'REVAL RESERVE',
1151 cb.reval_reserve_acct),
1152 decode(report_type,
1153 'RESERVE',
1154 decode(dd.deprn_source_code,
1155 'D',
1156 begin_or_end,
1157 'ADDITION'),
1158 'REVAL RESERVE',
1159 decode(dd.deprn_source_code,
1160 'D',
1161 begin_or_end,
1162 'ADDITION'),
1163 begin_or_end),
1164 decode(report_type,
1165 'COST',
1166 dd.cost,
1167 'CIP COST',
1168 dd.cost,
1169 'RESERVE',
1170 dd.deprn_reserve,
1171 'REVAL RESERVE',
1172 dd.reval_reserve),
1173 h_request_id
1174 FROM fa_distribution_history dh,
1175 fa_deprn_detail_mrc_v dd,
1176 fa_asset_history ah,
1177 fa_category_books cb,
1178 fa_books_mrc_v bk
1179 WHERE dh.book_type_code = distribution_source_book
1180 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1181 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1182 AND dd.asset_id = dh.asset_id
1183 AND dd.book_type_code = book
1184 AND dd.distribution_id = dh.distribution_id
1185 AND dd.period_counter <= period_pc
1186 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1187 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1188 AND decode(report_type,
1189 'CIP COST',
1190 dd.deprn_source_code,
1191 decode(begin_or_end,
1192 'BEGIN',
1193 dd.deprn_source_code,
1194 'D')) = dd.deprn_source_code
1195 AND
1196 /* DECODE(Begin_or_End,
1197 'BEGIN', DD.Deprn_Source_Code, 'D') =
1198 DD.Deprn_Source_Code AND */
1199 -- End bug fix 5076193
1200 dd.period_counter =
1201 (SELECT MAX(sub_dd.period_counter)
1202 FROM fa_deprn_detail_mrc_v sub_dd
1203 WHERE sub_dd.book_type_code = book
1204 AND sub_dd.distribution_id = dh.distribution_id
1205 AND dh.distribution_id = dd.distribution_id
1206 AND sub_dd.period_counter <= period_pc)
1207 AND ah.asset_id = dd.asset_id
1208 AND ((ah.asset_type <> 'EXPENSED' AND
1209 report_type IN ('COST', 'CIP COST')) OR
1210 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
1211 report_type IN ('RESERVE', 'REVAL RESERVE')))
1212 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1213 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1214 AND cb.category_id = ah.category_id
1215 AND cb.book_type_code = dd.book_type_code -- changed from book var to column
1216 AND bk.book_type_code = cb.book_type_code
1217 AND -- changed from book var to column
1218 bk.asset_id = dd.asset_id
1219 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1220 bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
1221 AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
1222 earliest_pc
1223 AND decode(report_type,
1224 'COST',
1225 decode(ah.asset_type,
1226 'CAPITALIZED',
1227 cb.asset_cost_acct,
1228 NULL),
1229 'CIP COST',
1230 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
1231 'RESERVE',
1232 cb.deprn_reserve_acct,
1233 'REVAL RESERVE',
1234 cb.reval_reserve_acct) IS NOT NULL;
1235 ELSE
1236 -- split for 'COST','CIP COST' and 'RESERVE','REVAL RESERVE' for better performance.
1237
1238 IF report_type IN ('COST', 'CIP COST') THEN
1239 INSERT INTO fa_balances_reports_itf
1240 (asset_id,
1241 distribution_ccid,
1242 adjustment_ccid,
1243 category_books_account,
1244 source_type_code,
1245 amount,
1246 request_id)
1247 SELECT /*+ USE_HASH(SUB_DD,BK) */
1248 dh.asset_id,
1249 dh.code_combination_id,
1250 NULL,
1251 decode(report_type,
1252 'COST',
1253 cb.asset_cost_acct,
1254 'CIP COST',
1255 cb.cip_cost_acct,
1256 'RESERVE',
1257 cb.deprn_reserve_acct,
1258 'REVAL RESERVE',
1259 cb.reval_reserve_acct),
1260 decode(report_type,
1261 'RESERVE',
1262 decode(dd.deprn_source_code,
1263 'D',
1264 begin_or_end,
1265 'ADDITION'),
1266 'REVAL RESERVE',
1267 decode(dd.deprn_source_code,
1268 'D',
1269 begin_or_end,
1270 'ADDITION'),
1271 begin_or_end),
1272 decode(report_type,
1273 'COST',
1274 dd.cost,
1275 'CIP COST',
1276 dd.cost,
1277 'RESERVE',
1278 dd.deprn_reserve,
1279 'REVAL RESERVE',
1280 dd.reval_reserve),
1281 h_request_id
1282 FROM fa_deprn_detail dd,
1283 fa_distribution_history dh,
1284 fa_asset_history ah,
1285 fa_category_books cb,
1286 fa_books bk,
1287 (SELECT asset_id,
1288 distribution_id,
1289 MAX(period_counter) mpc
1290 FROM fa_deprn_detail
1291 WHERE book_type_code = book
1292 AND period_counter <= period_pc
1293 GROUP BY asset_id, distribution_id) sub_dd
1294 WHERE dh.book_type_code = distribution_source_book
1295 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1296 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1297 AND dd.asset_id = dh.asset_id
1298 AND dd.book_type_code = book
1299 AND dd.distribution_id = dh.distribution_id
1300 AND dd.period_counter <= period_pc
1301 AND dd.asset_id BETWEEN start_range AND end_range
1302 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1303 AND decode(report_type,
1304 'CIP COST',
1305 dd.deprn_source_code,
1306 decode(begin_or_end,
1307 'BEGIN',
1308 dd.deprn_source_code,
1309 'D')) = dd.deprn_source_code
1310 AND dd.period_counter = sub_dd.mpc
1311 AND dd.distribution_id = sub_dd.distribution_id
1312 AND sub_dd.asset_id = dd.asset_id
1313 AND ah.asset_id = dd.asset_id
1314 --AND ah.asset_type <> 'EXPENSED' /* Commented and added below for Bug 16326387 */
1315 AND ah.asset_type IN ('CAPITALIZED', 'CIP', 'GROUP')
1316 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1317 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1318 AND cb.category_id = ah.category_id
1319 AND cb.book_type_code = dd.book_type_code
1320 AND bk.book_type_code = cb.book_type_code
1321 AND bk.asset_id = dd.asset_id
1322 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1323 bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
1324 AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
1325 earliest_pc
1326 AND decode(report_type,
1327 'COST',
1328 decode(ah.asset_type,
1329 'CAPITALIZED',
1330 cb.asset_cost_acct,
1331 NULL),
1332 'CIP COST',
1333 decode(ah.asset_type,
1334 'CIP',
1335 cb.cip_cost_acct,
1336 NULL),
1337 'RESERVE',
1338 cb.deprn_reserve_acct,
1339 'REVAL RESERVE',
1340 cb.reval_reserve_acct) IS NOT NULL;
1341
1342 ELSE
1343 -- report_type in ('RESERVE','REVAL RESERVE')
1344
1345 /* Bug 6998035 */
1346 INSERT INTO fa_balances_reports_itf
1347 (asset_id,
1348 distribution_ccid,
1349 adjustment_ccid,
1350 category_books_account,
1351 source_type_code,
1352 amount,
1353 request_id)
1354 SELECT dh.asset_id,
1355 dh.code_combination_id,
1356 NULL,
1357 decode(report_type,
1358 'COST',
1359 cb.asset_cost_acct,
1360 'CIP COST',
1361 cb.cip_cost_acct,
1362 'RESERVE',
1363 cb.deprn_reserve_acct,
1364 'REVAL RESERVE',
1365 cb.reval_reserve_acct),
1366 decode(report_type,
1367 'RESERVE',
1368 decode(dd.deprn_source_code,
1369 'D',
1370 begin_or_end,
1371 'ADDITION'),
1372 'REVAL RESERVE',
1373 decode(dd.deprn_source_code,
1374 'D',
1375 begin_or_end,
1376 'ADDITION'),
1377 begin_or_end),
1378 decode(report_type,
1379 'COST',
1380 dd.cost,
1381 'CIP COST',
1382 dd.cost,
1383 'RESERVE',
1384 dd.deprn_reserve,
1385 'REVAL RESERVE',
1386 dd.reval_reserve),
1387 h_request_id
1388 FROM fa_deprn_detail dd,
1389 fa_distribution_history dh,
1390 fa_asset_history ah,
1391 fa_category_books cb,
1392 fa_books bk
1393 WHERE dh.book_type_code = distribution_source_book
1394 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1395 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1396 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1397 AND dd.asset_id = dh.asset_id
1398 AND dd.book_type_code = book
1399 AND dd.distribution_id = dh.distribution_id
1400 AND dd.period_counter <= period_pc
1401 AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
1402 dd.deprn_source_code
1403 AND dd.period_counter =
1404 (SELECT MAX(sub_dd.period_counter)
1405 FROM fa_deprn_detail sub_dd
1406 WHERE sub_dd.book_type_code = book
1407 AND sub_dd.distribution_id = dh.distribution_id
1408 AND dh.distribution_id = dd.distribution_id
1409 AND sub_dd.period_counter <= period_pc)
1410 AND ah.asset_id = dd.asset_id
1411 AND ah.asset_type IN ('CAPITALIZED', 'CIP')
1412 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1413 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1414 AND cb.category_id = ah.category_id
1415 AND cb.book_type_code = dd.book_type_code -- changed from book var to column
1416 AND bk.book_type_code = cb.book_type_code
1417 AND -- changed from book var to column
1418 bk.asset_id = dd.asset_id
1419 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1420 bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
1421 AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
1422 earliest_pc
1423 AND decode(report_type,
1424 'COST',
1425 decode(ah.asset_type,
1426 'CAPITALIZED',
1427 cb.asset_cost_acct,
1428 NULL),
1429 'CIP COST',
1430 decode(ah.asset_type,
1431 'CIP',
1432 cb.cip_cost_acct,
1433 NULL),
1434 'RESERVE',
1435 cb.deprn_reserve_acct,
1436 'REVAL RESERVE',
1437 cb.reval_reserve_acct) IS NOT NULL;
1438
1439 END IF;
1440
1441 END IF;
1442
1443 -- Fix for Bug #1892406. Run only if CRL installed.
1444 ELSIF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
1445
1446 IF (h_reporting_flag = 'R') THEN
1447 INSERT INTO fa_balances_reports_itf
1448 (asset_id,
1449 distribution_ccid,
1450 adjustment_ccid,
1451 category_books_account,
1452 source_type_code,
1453 amount,
1454 request_id)
1455 SELECT dh.asset_id,
1456 dh.code_combination_id,
1457 NULL,
1458 decode(report_type,
1459 'COST',
1460 cb.asset_cost_acct,
1461 'CIP COST',
1462 cb.cip_cost_acct,
1463 'RESERVE',
1464 cb.deprn_reserve_acct,
1465 'REVAL RESERVE',
1466 cb.reval_reserve_acct),
1467 decode(report_type,
1468 'RESERVE',
1469 decode(dd.deprn_source_code,
1470 'D',
1471 begin_or_end,
1472 'ADDITION'),
1473 'REVAL RESERVE',
1474 decode(dd.deprn_source_code,
1475 'D',
1476 begin_or_end,
1477 'ADDITION'),
1478 begin_or_end),
1479 decode(report_type,
1480 'COST',
1481 dd.cost,
1482 'CIP COST',
1483 dd.cost,
1484 'RESERVE',
1485 dd.deprn_reserve,
1486 'REVAL RESERVE',
1487 dd.reval_reserve),
1488 h_request_id
1489 FROM fa_distribution_history dh,
1490 fa_deprn_detail_mrc_v dd,
1491 fa_asset_history ah,
1492 fa_category_books cb,
1493 fa_books_mrc_v bk
1494 WHERE dh.book_type_code = distribution_source_book
1495 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1496 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1497 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1498 AND dd.asset_id = dh.asset_id
1499 AND dd.book_type_code = book
1500 AND dd.distribution_id = dh.distribution_id
1501 AND dd.period_counter <= period_pc
1502 AND
1503 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1504 decode(report_type,
1505 'CIP COST',
1506 dd.deprn_source_code,
1507 decode(begin_or_end,
1508 'BEGIN',
1509 dd.deprn_source_code,
1510 'D')) = dd.deprn_source_code
1511 AND
1512 /* DECODE(Begin_or_End,
1513 'BEGIN', DD.Deprn_Source_Code, 'D') =
1514 DD.Deprn_Source_Code AND */
1515 -- end bug fix 5076193
1516 dd.period_counter =
1517 (SELECT MAX(sub_dd.period_counter)
1518 FROM fa_deprn_detail_mrc_v sub_dd
1519 WHERE sub_dd.book_type_code = book
1520 AND sub_dd.distribution_id = dh.distribution_id
1521 AND dh.distribution_id = dd.distribution_id
1522 AND sub_dd.period_counter <= period_pc)
1523 AND ah.asset_id = dd.asset_id
1524 AND ((ah.asset_type <> 'EXPENSED' AND
1525 report_type IN ('COST', 'CIP COST')) OR
1526 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
1527 report_type IN ('RESERVE', 'REVAL RESERVE')))
1528 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1529 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1530 AND cb.category_id = ah.category_id
1531 AND cb.book_type_code = dd.book_type_code -- changed from book var to column
1532 AND bk.book_type_code = cb.book_type_code
1533 AND -- changed from book var to column
1534 bk.asset_id = dd.asset_id
1535 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1536 bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
1537 AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
1538 earliest_pc
1539 AND decode(report_type,
1540 'COST',
1541 decode(ah.asset_type,
1542 'CAPITALIZED',
1543 cb.asset_cost_acct,
1544 NULL),
1545 'CIP COST',
1546 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
1547 'RESERVE',
1548 cb.deprn_reserve_acct,
1549 'REVAL RESERVE',
1550 cb.reval_reserve_acct) IS NOT NULL
1551 -- start of CUA - This is to exclude the Group Asset Members
1552 AND bk.group_asset_id IS NULL;
1553 ELSE
1554 INSERT INTO fa_balances_reports_itf
1555 (asset_id,
1556 distribution_ccid,
1557 adjustment_ccid,
1558 category_books_account,
1559 source_type_code,
1560 amount,
1561 request_id)
1562 SELECT dh.asset_id,
1563 dh.code_combination_id,
1564 NULL,
1565 decode(report_type,
1566 'COST',
1567 cb.asset_cost_acct,
1568 'CIP COST',
1569 cb.cip_cost_acct,
1570 'RESERVE',
1571 cb.deprn_reserve_acct,
1572 'REVAL RESERVE',
1573 cb.reval_reserve_acct),
1574 decode(report_type,
1575 'RESERVE',
1576 decode(dd.deprn_source_code,
1577 'D',
1578 begin_or_end,
1579 'ADDITION'),
1580 'REVAL RESERVE',
1581 decode(dd.deprn_source_code,
1582 'D',
1583 begin_or_end,
1584 'ADDITION'),
1585 begin_or_end),
1586 decode(report_type,
1587 'COST',
1588 dd.cost,
1589 'CIP COST',
1590 dd.cost,
1591 'RESERVE',
1592 dd.deprn_reserve,
1593 'REVAL RESERVE',
1594 dd.reval_reserve),
1595 h_request_id
1596 FROM fa_distribution_history dh,
1597 fa_deprn_detail dd,
1598 fa_asset_history ah,
1599 fa_category_books cb,
1600 fa_books bk
1601 WHERE dh.book_type_code = distribution_source_book
1602 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1603 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1604 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1605 AND dd.asset_id = dh.asset_id
1606 AND dd.book_type_code = book
1607 AND dd.distribution_id = dh.distribution_id
1608 AND dd.period_counter <= period_pc
1609 AND
1610 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1611 decode(report_type,
1612 'CIP COST',
1613 dd.deprn_source_code,
1614 decode(begin_or_end,
1615 'BEGIN',
1616 dd.deprn_source_code,
1617 'D')) = dd.deprn_source_code
1618 AND
1619 /* DECODE(Begin_or_End,
1620 'BEGIN', DD.Deprn_Source_Code, 'D') =
1621 DD.Deprn_Source_Code AND */
1622 -- End bug fix 5076193
1623 dd.period_counter =
1624 (SELECT MAX(sub_dd.period_counter)
1625 FROM fa_deprn_detail sub_dd
1626 WHERE sub_dd.book_type_code = book
1627 AND sub_dd.distribution_id = dh.distribution_id
1628 AND dh.distribution_id = dd.distribution_id
1629 AND sub_dd.period_counter <= period_pc)
1630 AND ah.asset_id = dd.asset_id
1631 AND ((ah.asset_type <> 'EXPENSED' AND
1632 report_type IN ('COST', 'CIP COST')) OR
1633 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
1634 report_type IN ('RESERVE', 'REVAL RESERVE')))
1635 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1636 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1637 AND cb.category_id = ah.category_id
1638 AND cb.book_type_code = dd.book_type_code -- changed from book var to column
1639 AND bk.book_type_code = cb.book_type_code
1640 AND -- changed from book var to column
1641 bk.asset_id = dd.asset_id
1642 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1643 bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
1644 AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
1645 earliest_pc
1646 AND decode(report_type,
1647 'COST',
1648 decode(ah.asset_type,
1649 'CAPITALIZED',
1650 cb.asset_cost_acct,
1651 NULL),
1652 'CIP COST',
1653 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
1654 'RESERVE',
1655 cb.deprn_reserve_acct,
1656 'REVAL RESERVE',
1657 cb.reval_reserve_acct) IS NOT NULL
1658 -- start of CUA - This is to exclude the Group Asset Members
1659 AND bk.group_asset_id IS NULL;
1660 END IF;
1661 -- end of cua
1662
1663 COMMIT;
1664 END IF;
1665 END get_balance;
1666
1667 PROCEDURE get_balance_group_begin(book IN VARCHAR2,
1668 distribution_source_book IN VARCHAR2,
1669 period_pc IN NUMBER,
1670 earliest_pc IN NUMBER,
1671 period_date IN DATE,
1672 additions_date IN DATE,
1673 report_type IN VARCHAR2,
1674 balance_type IN VARCHAR2,
1675 begin_or_end IN VARCHAR2,
1676 start_range IN NUMBER,
1677 end_range IN NUMBER,
1678 h_request_id NUMBER) IS
1679 p_date DATE := period_date;
1680 a_date DATE := additions_date;
1681 h_set_of_books_id NUMBER;
1682 h_reporting_flag VARCHAR2(1);
1683 BEGIN
1684
1685 -- get mrc related info
1686 BEGIN
1687 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
1688 SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
1689 INTO h_set_of_books_id
1690 FROM dual;
1691
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 h_set_of_books_id := NULL;
1695 END;
1696
1697 IF (h_set_of_books_id IS NOT NULL) THEN
1698 IF NOT
1699 fa_cache_pkg.fazcsob(x_set_of_books_id => h_set_of_books_id,
1700 x_mrc_sob_type_code => h_reporting_flag) THEN
1701 RAISE fnd_api.g_exc_unexpected_error;
1702 END IF;
1703 ELSE
1704 h_reporting_flag := 'P';
1705 END IF;
1706
1707 -- run only if CRL installed
1708 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
1709
1710 IF (report_type NOT IN ('RESERVE')) THEN
1711 IF (h_reporting_flag = 'R') THEN
1712 INSERT INTO fa_balances_reports_itf
1713 (asset_id,
1714 distribution_ccid,
1715 adjustment_ccid,
1716 category_books_account,
1717 source_type_code,
1718 amount,
1719 request_id)
1720 SELECT dh.asset_id,
1721 --DH.Code_Combination_ID,
1722 nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
1723 -- Changed for BMA1
1724 -- nvl(gad.asset_cost_acct_ccid,1127),
1725 gad.asset_cost_acct_ccid,
1726 NULL,
1727 decode(report_type,
1728 'RESERVE',
1729 decode(dd.deprn_source_code,
1730 'D',
1731 begin_or_end,
1732 'ADDITION'),
1733 'REVAL RESERVE',
1734 decode(dd.deprn_source_code,
1735 'D',
1736 begin_or_end,
1737 'ADDITION'),
1738 begin_or_end),
1739 decode(report_type,
1740 -- Commented by Prabakar
1741 'COST',
1742 decode(nvl(bk.group_asset_id, -2),
1743 -2,
1744 dd.cost,
1745 bk.cost),
1746 -- 'COST', DD.Cost,
1747 'CIP COST',
1748 dd.cost,
1749 'RESERVE',
1750 dd.deprn_reserve,
1751 'REVAL RESERVE',
1752 dd.reval_reserve),
1753 h_request_id
1754 FROM fa_books_mrc_v bk,
1755 fa_category_books cb,
1756 fa_asset_history ah,
1757 fa_deprn_detail_mrc_v dd,
1758 fa_distribution_history dh,
1759 -- Commented by Prabakar
1760 fa_group_asset_default gad
1761 WHERE
1762 -- Commented by Prabakar
1763 gad.book_type_code = bk.book_type_code
1764 AND gad.group_asset_id = bk.group_asset_id
1765 AND
1766 -- This is to include only the Group Asset Members
1767 bk.group_asset_id IS NOT NULL
1768 AND dh.book_type_code = distribution_source_book
1769 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1770 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1771 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1772 AND dd.asset_id = dh.asset_id
1773 AND dd.book_type_code = book
1774 AND dd.distribution_id = dh.distribution_id
1775 AND dd.period_counter <= period_pc
1776 AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
1777 dd.deprn_source_code
1778 AND dd.period_counter =
1779 (SELECT MAX(sub_dd.period_counter)
1780 FROM fa_deprn_detail_mrc_v sub_dd
1781 WHERE sub_dd.book_type_code = book
1782 AND sub_dd.distribution_id = dh.distribution_id
1783 AND sub_dd.period_counter <= period_pc)
1784 AND ah.asset_id = dd.asset_id
1785 AND ((ah.asset_type <> 'EXPENSED' AND
1786 report_type IN ('COST', 'CIP COST')) OR
1787 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
1788 report_type IN ('RESERVE', 'REVAL RESERVE')))
1789 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1790 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1791 AND cb.category_id = ah.category_id
1792 AND cb.book_type_code = book
1793 AND bk.book_type_code = book
1794 AND bk.asset_id = dd.asset_id
1795 AND
1796 -- Commented by Prabakar
1797 (bk.transaction_header_id_in =
1798 (SELECT MIN(fab.transaction_header_id_in)
1799 FROM fa_books_groups_mrc_v bg, fa_books_mrc_v fab
1800 WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
1801 AND bg.book_type_code = fab.book_type_code
1802 AND fab.transaction_header_id_in <=
1803 bg.transaction_header_id_in
1804 AND nvl(fab.transaction_header_id_out,
1805 bg.transaction_header_id_in) >=
1806 bg.transaction_header_id_in
1807 AND bg.period_counter = period_pc + 1
1808 AND fab.asset_id = bk.asset_id
1809 AND fab.book_type_code = bk.book_type_code
1810 AND bg.beginning_balance_flag IS NOT NULL))
1811 AND decode(report_type,
1812 'COST',
1813 decode(ah.asset_type,
1814 'CAPITALIZED',
1815 cb.asset_cost_acct,
1816 NULL),
1817 'CIP COST',
1818 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
1819 'RESERVE',
1820 cb.deprn_reserve_acct,
1821 'REVAL RESERVE',
1822 cb.reval_reserve_acct) IS NOT NULL;
1823 ELSE
1824 INSERT INTO fa_balances_reports_itf
1825 (asset_id,
1826 distribution_ccid,
1827 adjustment_ccid,
1828 category_books_account,
1829 source_type_code,
1830 amount,
1831 request_id)
1832 SELECT dh.asset_id,
1833 --DH.Code_Combination_ID,
1834 nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
1835 -- Changed for BMA1
1836 -- nvl(gad.asset_cost_acct_ccid,1127),
1837 gad.asset_cost_acct_ccid,
1838 NULL,
1839 decode(report_type,
1840 'RESERVE',
1841 decode(dd.deprn_source_code,
1842 'D',
1843 begin_or_end,
1844 'ADDITION'),
1845 'REVAL RESERVE',
1846 decode(dd.deprn_source_code,
1847 'D',
1848 begin_or_end,
1849 'ADDITION'),
1850 begin_or_end),
1851 decode(report_type,
1852 -- Commented by Prabakar
1853 'COST',
1854 decode(nvl(bk.group_asset_id, -2),
1855 -2,
1856 dd.cost,
1857 bk.cost),
1858 -- 'COST', DD.Cost,
1859 'CIP COST',
1860 dd.cost,
1861 'RESERVE',
1862 dd.deprn_reserve,
1863 'REVAL RESERVE',
1864 dd.reval_reserve),
1865 h_request_id
1866 FROM fa_books bk,
1867 fa_category_books cb,
1868 fa_asset_history ah,
1869 fa_deprn_detail dd,
1870 fa_distribution_history dh,
1871 -- Commented by Prabakar
1872 fa_group_asset_default gad
1873 WHERE
1874 -- Commented by Prabakar
1875 gad.book_type_code = bk.book_type_code
1876 AND gad.group_asset_id = bk.group_asset_id
1877 AND
1878 -- This is to include only the Group Asset Members
1879 bk.group_asset_id IS NOT NULL
1880 AND dh.book_type_code = distribution_source_book
1881 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1882 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
1883 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1884 AND dd.asset_id = dh.asset_id
1885 AND dd.book_type_code = book
1886 AND dd.distribution_id = dh.distribution_id
1887 AND dd.period_counter <= period_pc
1888 AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
1889 dd.deprn_source_code
1890 AND dd.period_counter =
1891 (SELECT MAX(sub_dd.period_counter)
1892 FROM fa_deprn_detail sub_dd
1893 WHERE sub_dd.book_type_code = book
1894 AND sub_dd.distribution_id = dh.distribution_id
1895 AND sub_dd.period_counter <= period_pc)
1896 AND ah.asset_id = dd.asset_id
1897 AND ((ah.asset_type <> 'EXPENSED' AND
1898 report_type IN ('COST', 'CIP COST')) OR
1899 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
1900 report_type IN ('RESERVE', 'REVAL RESERVE')))
1901 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
1902 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
1903 AND cb.category_id = ah.category_id
1904 AND cb.book_type_code = book
1905 AND bk.book_type_code = book
1906 AND bk.asset_id = dd.asset_id
1907 AND
1908 -- Commented by Prabakar
1909 (bk.transaction_header_id_in =
1910 (SELECT MIN(fab.transaction_header_id_in)
1911 FROM fa_books_groups bg, fa_books fab
1912 WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
1913 AND bg.book_type_code = fab.book_type_code
1914 AND fab.transaction_header_id_in <=
1915 bg.transaction_header_id_in
1916 AND nvl(fab.transaction_header_id_out,
1917 bg.transaction_header_id_in) >=
1918 bg.transaction_header_id_in
1919 AND bg.period_counter = period_pc + 1
1920 AND fab.asset_id = bk.asset_id
1921 AND fab.book_type_code = bk.book_type_code
1922 AND bg.beginning_balance_flag IS NOT NULL))
1923 AND decode(report_type,
1924 'COST',
1925 decode(ah.asset_type,
1926 'CAPITALIZED',
1927 cb.asset_cost_acct,
1928 NULL),
1929 'CIP COST',
1930 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
1931 'RESERVE',
1932 cb.deprn_reserve_acct,
1933 'REVAL RESERVE',
1934 cb.reval_reserve_acct) IS NOT NULL;
1935 END IF;
1936 ELSE
1937
1938 -- Get the Depreciation reserve begin balance
1939
1940 IF (h_reporting_flag = 'R') THEN
1941 INSERT INTO fa_balances_reports_itf
1942 (asset_id,
1943 distribution_ccid,
1944 adjustment_ccid,
1945 category_books_account,
1946 source_type_code,
1947 amount,
1948 request_id)
1949 SELECT gar.group_asset_id asset_id,
1950 gad.deprn_expense_acct_ccid,
1951 gad.deprn_reserve_acct_ccid,
1952 NULL,
1953 /* DECODE(Report_Type,
1954 'RESERVE', DECODE(DD.Deprn_Source_Code,
1955 'D', Begin_or_End, 'ADDITION'),
1956 'REVAL RESERVE',
1957 DECODE(DD.Deprn_Source_Code,
1958 'D', Begin_or_End, 'ADDITION'),
1959 Begin_or_End),
1960 */
1961 'BEGIN',
1962 dd.deprn_reserve,
1963 h_request_id
1964 FROM fa_deprn_summary_mrc_v dd,
1965 fa_group_asset_rules gar,
1966 fa_group_asset_default gad
1967 WHERE dd.book_type_code = book
1968 AND dd.asset_id = gar.group_asset_id
1969 AND gar.book_type_code = dd.book_type_code
1970 AND gad.book_type_code = gar.book_type_code
1971 AND gad.group_asset_id = gar.group_asset_id
1972 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
1973 AND dd.period_counter =
1974 (SELECT MAX(dd_sub.period_counter)
1975 FROM fa_deprn_detail_mrc_v dd_sub
1976 WHERE dd_sub.book_type_code = book
1977 AND dd_sub.asset_id = gar.group_asset_id
1978 AND dd_sub.period_counter <= period_pc);
1979 ELSE
1980 INSERT INTO fa_balances_reports_itf
1981 (asset_id,
1982 distribution_ccid,
1983 adjustment_ccid,
1984 category_books_account,
1985 source_type_code,
1986 amount,
1987 request_id)
1988 SELECT gar.group_asset_id asset_id,
1989 gad.deprn_expense_acct_ccid,
1990 gad.deprn_reserve_acct_ccid,
1991 NULL,
1992 /* DECODE(Report_Type,
1993 'RESERVE', DECODE(DD.Deprn_Source_Code,
1994 'D', Begin_or_End, 'ADDITION'),
1995 'REVAL RESERVE',
1996 DECODE(DD.Deprn_Source_Code,
1997 'D', Begin_or_End, 'ADDITION'),
1998 Begin_or_End),
1999 */
2000 'BEGIN',
2001 dd.deprn_reserve,
2002 h_request_id
2003 FROM fa_deprn_summary dd,
2004 fa_group_asset_rules gar,
2005 fa_group_asset_default gad
2006 WHERE dd.book_type_code = book
2007 AND dd.asset_id = gar.group_asset_id
2008 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2009 AND gar.book_type_code = dd.book_type_code
2010 AND gad.book_type_code = gar.book_type_code
2011 AND gad.group_asset_id = gar.group_asset_id
2012 AND dd.period_counter =
2013 (SELECT MAX(dd_sub.period_counter)
2014 FROM fa_deprn_detail dd_sub
2015 WHERE dd_sub.book_type_code = book
2016 AND dd_sub.asset_id = gar.group_asset_id
2017 AND dd_sub.period_counter <= period_pc);
2018 END IF;
2019 --NULL;
2020 END IF;
2021
2022 END IF; --end of CRL check
2023 END get_balance_group_begin;
2024
2025 PROCEDURE get_balance_group_end(book IN VARCHAR2,
2026 distribution_source_book IN VARCHAR2,
2027 period_pc IN NUMBER,
2028 earliest_pc IN NUMBER,
2029 period_date IN DATE,
2030 additions_date IN DATE,
2031 report_type IN VARCHAR2,
2032 balance_type IN VARCHAR2,
2033 begin_or_end IN VARCHAR2,
2034 start_range IN NUMBER,
2035 end_range IN NUMBER,
2036 h_request_id IN NUMBER) IS
2037 p_date DATE := period_date;
2038 a_date DATE := additions_date;
2039 h_set_of_books_id NUMBER;
2040 h_reporting_flag VARCHAR2(1);
2041 BEGIN
2042
2043 -- get mrc related info
2044 BEGIN
2045 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
2046 SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
2047 INTO h_set_of_books_id
2048 FROM dual;
2049
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 h_set_of_books_id := NULL;
2053 END;
2054
2055 IF (h_set_of_books_id IS NOT NULL) THEN
2056 IF NOT
2057 fa_cache_pkg.fazcsob(x_set_of_books_id => h_set_of_books_id,
2058 x_mrc_sob_type_code => h_reporting_flag) THEN
2059 RAISE fnd_api.g_exc_unexpected_error;
2060 END IF;
2061 ELSE
2062 h_reporting_flag := 'P';
2063 END IF;
2064
2065 -- run only if CRL installed
2066 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
2067
2068 IF report_type NOT IN ('RESERVE') THEN
2069 IF (h_reporting_flag = 'R') THEN
2070 INSERT INTO fa_balances_reports_itf
2071 (asset_id,
2072 distribution_ccid,
2073 adjustment_ccid,
2074 category_books_account,
2075 source_type_code,
2076 amount,
2077 request_id)
2078 SELECT dh.asset_id,
2079 -- DH.Code_Combination_ID,
2080 nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
2081 -- Changed for BMA1
2082 -- nvl(gad.asset_cost_acct_ccid,1127),
2083 gad.asset_cost_acct_ccid,
2084 NULL,
2085 decode(report_type,
2086 'RESERVE',
2087 decode(dd.deprn_source_code,
2088 'D',
2089 begin_or_end,
2090 'ADDITION'),
2091 'REVAL RESERVE',
2092 decode(dd.deprn_source_code,
2093 'D',
2094 begin_or_end,
2095 'ADDITION'),
2096 begin_or_end),
2097 decode(report_type,
2098 'COST',
2099 decode(nvl(bk.group_asset_id, -2),
2100 -2,
2101 dd.cost,
2102 bk.cost),
2103 'CIP COST',
2104 dd.cost,
2105 'RESERVE',
2106 dd.deprn_reserve,
2107 'REVAL RESERVE',
2108 dd.reval_reserve),
2109 h_request_id
2110 FROM fa_books_mrc_v bk,
2111 fa_category_books cb,
2112 fa_asset_history ah,
2113 fa_deprn_detail_mrc_v dd,
2114 fa_distribution_history dh,
2115 -- Commented by Prabakar
2116 fa_group_asset_default gad
2117 WHERE
2118 -- Commented by Prabakar
2119 gad.book_type_code = bk.book_type_code
2120 AND gad.group_asset_id = bk.group_asset_id
2121 -- This is to include only the Group Asset Members
2122 AND bk.group_asset_id IS NOT NULL
2123 AND dh.book_type_code = distribution_source_book
2124 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
2125 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
2126 AND dd.asset_id = dh.asset_id
2127 AND dd.book_type_code = book
2128 AND dd.distribution_id = dh.distribution_id
2129 AND dd.period_counter <= period_pc
2130 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2131 AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
2132 dd.deprn_source_code
2133 AND dd.period_counter =
2134 (SELECT MAX(sub_dd.period_counter)
2135 FROM fa_deprn_detail_mrc_v sub_dd
2136 WHERE sub_dd.book_type_code = book
2137 AND sub_dd.distribution_id = dh.distribution_id
2138 AND sub_dd.period_counter <= period_pc)
2139 AND ah.asset_id = dd.asset_id
2140 AND ((ah.asset_type <> 'EXPENSED' AND
2141 report_type IN ('COST', 'CIP COST')) OR
2142 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
2143 report_type IN ('RESERVE', 'REVAL RESERVE')))
2144 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
2145 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
2146 AND cb.category_id = ah.category_id
2147 AND cb.book_type_code = book
2148 AND bk.book_type_code = book
2149 AND bk.asset_id = dd.asset_id
2150 AND
2151 -- Commented by Prabakar
2152 (bk.transaction_header_id_in =
2153 (SELECT MIN(fab.transaction_header_id_in)
2154 FROM fa_books_groups_mrc_v bg, fa_books_mrc_v fab
2155 WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
2156 AND bg.book_type_code = fab.book_type_code
2157 AND fab.transaction_header_id_in <=
2158 bg.transaction_header_id_in
2159 AND nvl(fab.transaction_header_id_out,
2160 bg.transaction_header_id_in) >=
2161 bg.transaction_header_id_in
2162 AND bg.period_counter = period_pc + 1
2163 AND fab.asset_id = bk.asset_id
2164 AND fab.book_type_code = bk.book_type_code
2165 AND bg.beginning_balance_flag IS NOT NULL))
2166 AND decode(report_type,
2167 'COST',
2168 decode(ah.asset_type,
2169 'CAPITALIZED',
2170 cb.asset_cost_acct,
2171 NULL),
2172 'CIP COST',
2173 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
2174 'RESERVE',
2175 cb.deprn_reserve_acct,
2176 'REVAL RESERVE',
2177 cb.reval_reserve_acct) IS NOT NULL;
2178 ELSE
2179 INSERT INTO fa_balances_reports_itf
2180 (asset_id,
2181 distribution_ccid,
2182 adjustment_ccid,
2183 category_books_account,
2184 source_type_code,
2185 amount,
2186 request_id)
2187 SELECT dh.asset_id,
2188 -- DH.Code_Combination_ID,
2189 nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
2190 -- Changed for BMA1
2191 -- nvl(gad.asset_cost_acct_ccid,1127),
2192 gad.asset_cost_acct_ccid,
2193 NULL,
2194 decode(report_type,
2195 'RESERVE',
2196 decode(dd.deprn_source_code,
2197 'D',
2198 begin_or_end,
2199 'ADDITION'),
2200 'REVAL RESERVE',
2201 decode(dd.deprn_source_code,
2202 'D',
2203 begin_or_end,
2204 'ADDITION'),
2205 begin_or_end),
2206 decode(report_type,
2207 'COST',
2208 decode(nvl(bk.group_asset_id, -2),
2209 -2,
2210 dd.cost,
2211 bk.cost),
2212 'CIP COST',
2213 dd.cost,
2214 'RESERVE',
2215 dd.deprn_reserve,
2216 'REVAL RESERVE',
2217 dd.reval_reserve),
2218 h_request_id
2219 FROM fa_books bk,
2220 fa_category_books cb,
2221 fa_asset_history ah,
2222 fa_deprn_detail dd,
2223 fa_distribution_history dh,
2224 -- Commented by Prabakar
2225 fa_group_asset_default gad
2226 WHERE
2227 -- Commented by Prabakar
2228 gad.book_type_code = bk.book_type_code
2229 AND gad.group_asset_id = bk.group_asset_id
2230 -- This is to include only the Group Asset Members
2231 AND bk.group_asset_id IS NOT NULL
2232 AND dh.book_type_code = distribution_source_book
2233 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
2234 dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
2235 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2236 AND dd.asset_id = dh.asset_id
2237 AND dd.book_type_code = book
2238 AND dd.distribution_id = dh.distribution_id
2239 AND dd.period_counter <= period_pc
2240 AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
2241 dd.deprn_source_code
2242 AND dd.period_counter =
2243 (SELECT MAX(sub_dd.period_counter)
2244 FROM fa_deprn_detail sub_dd
2245 WHERE sub_dd.book_type_code = book
2246 AND sub_dd.distribution_id = dh.distribution_id
2247 AND sub_dd.period_counter <= period_pc)
2248 AND ah.asset_id = dd.asset_id
2249 AND ((ah.asset_type <> 'EXPENSED' AND
2250 report_type IN ('COST', 'CIP COST')) OR
2251 (ah.asset_type IN ('CAPITALIZED', 'CIP') AND
2252 report_type IN ('RESERVE', 'REVAL RESERVE')))
2253 AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
2254 ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
2255 AND cb.category_id = ah.category_id
2256 AND cb.book_type_code = book
2257 AND bk.book_type_code = book
2258 AND bk.asset_id = dd.asset_id
2259 AND
2260 -- Commented by Prabakar
2261 (bk.transaction_header_id_in =
2262 (SELECT MIN(fab.transaction_header_id_in)
2263 FROM fa_books_groups bg, fa_books fab
2264 WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
2265 AND bg.book_type_code = fab.book_type_code
2266 AND fab.transaction_header_id_in <=
2267 bg.transaction_header_id_in
2268 AND nvl(fab.transaction_header_id_out,
2269 bg.transaction_header_id_in) >=
2270 bg.transaction_header_id_in
2271 AND bg.period_counter = period_pc + 1
2272 AND fab.asset_id = bk.asset_id
2273 AND fab.book_type_code = bk.book_type_code
2274 AND bg.beginning_balance_flag IS NOT NULL))
2275 AND decode(report_type,
2276 'COST',
2277 decode(ah.asset_type,
2278 'CAPITALIZED',
2279 cb.asset_cost_acct,
2280 NULL),
2281 'CIP COST',
2282 decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
2283 'RESERVE',
2284 cb.deprn_reserve_acct,
2285 'REVAL RESERVE',
2286 cb.reval_reserve_acct) IS NOT NULL;
2287 END IF;
2288
2289 ELSE
2290
2291 IF (h_reporting_flag = 'R') THEN
2292 INSERT INTO fa_balances_reports_itf
2293 (asset_id,
2294 distribution_ccid,
2295 adjustment_ccid,
2296 category_books_account,
2297 source_type_code,
2298 amount,
2299 request_id)
2300 SELECT gar.group_asset_id asset_id,
2301 gad.deprn_expense_acct_ccid,
2302 gad.deprn_reserve_acct_ccid,
2303 NULL,
2304 /* DECODE(Report_Type,
2305 'RESERVE', DECODE(DD.Deprn_Source_Code,
2306 'D', Begin_or_End, 'ADDITION'),
2307 'REVAL RESERVE',
2308 DECODE(DD.Deprn_Source_Code,
2309 'D', Begin_or_End, 'ADDITION'),
2310 Begin_or_End),*/
2311 'END',
2312 dd.deprn_reserve,
2313 h_request_id
2314 FROM fa_deprn_summary_mrc_v dd,
2315 fa_group_asset_rules gar,
2316 fa_group_asset_default gad
2317 WHERE dd.book_type_code = book
2318 AND dd.asset_id = gar.group_asset_id
2319 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2320 AND gar.book_type_code = dd.book_type_code
2321 AND gad.book_type_code = gar.book_type_code
2322 AND gad.group_asset_id = gar.group_asset_id
2323 AND dd.period_counter =
2324 (SELECT MAX(dd_sub.period_counter)
2325 FROM fa_deprn_detail_mrc_v dd_sub
2326 WHERE dd_sub.book_type_code = book
2327 AND dd_sub.asset_id = gar.group_asset_id
2328 AND dd_sub.period_counter <= period_pc);
2329 ELSE
2330 INSERT INTO fa_balances_reports_itf
2331 (asset_id,
2332 distribution_ccid,
2333 adjustment_ccid,
2334 category_books_account,
2335 source_type_code,
2336 amount,
2337 request_id)
2338 SELECT gar.group_asset_id asset_id,
2339 gad.deprn_expense_acct_ccid,
2340 gad.deprn_reserve_acct_ccid,
2341 NULL,
2342 /* DECODE(Report_Type,
2343 'RESERVE', DECODE(DD.Deprn_Source_Code,
2344 'D', Begin_or_End, 'ADDITION'),
2345 'REVAL RESERVE',
2346 DECODE(DD.Deprn_Source_Code,
2347 'D', Begin_or_End, 'ADDITION'),
2348 Begin_or_End),*/
2349 'END',
2350 dd.deprn_reserve,
2351 h_request_id
2352 FROM fa_deprn_summary dd,
2353 fa_group_asset_rules gar,
2354 fa_group_asset_default gad
2355 WHERE dd.book_type_code = book
2356 AND dd.asset_id = gar.group_asset_id
2357 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2358 AND gar.book_type_code = dd.book_type_code
2359 AND gad.book_type_code = gar.book_type_code
2360 AND gad.group_asset_id = gar.group_asset_id
2361 AND dd.period_counter =
2362 (SELECT MAX(dd_sub.period_counter)
2363 FROM fa_deprn_detail dd_sub
2364 WHERE dd_sub.book_type_code = book
2365 AND dd_sub.asset_id = gar.group_asset_id
2366 AND dd_sub.period_counter <= period_pc);
2367 END IF;
2368 END IF;
2369
2370 END IF; -- end of CRL check
2371 END get_balance_group_end;
2372
2373 PROCEDURE get_deprn_effects(book IN VARCHAR2,
2374 distribution_source_book IN VARCHAR2,
2375 period1_pc IN NUMBER,
2376 period2_pc IN NUMBER,
2377 report_type IN VARCHAR2,
2378 start_range IN NUMBER,
2379 end_range IN NUMBER,
2380 h_request_id IN NUMBER) IS
2381 h_set_of_books_id NUMBER;
2382 h_reporting_flag VARCHAR2(1);
2383 BEGIN
2384
2385 -- get mrc related info
2386 BEGIN
2387 -- h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
2388 SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
2389 INTO h_set_of_books_id
2390 FROM dual;
2391
2392 EXCEPTION
2393 WHEN OTHERS THEN
2394 h_set_of_books_id := NULL;
2395 END;
2396
2397 IF (h_set_of_books_id IS NOT NULL) THEN
2398 IF NOT
2399 fa_cache_pkg.fazcsob(x_set_of_books_id => h_set_of_books_id,
2400 x_mrc_sob_type_code => h_reporting_flag) THEN
2401 RAISE fnd_api.g_exc_unexpected_error;
2402 END IF;
2403 ELSE
2404 h_reporting_flag := 'P';
2405 END IF;
2406
2407 IF (h_reporting_flag = 'R') THEN
2408 INSERT INTO fa_balances_reports_itf
2409 (asset_id,
2410 distribution_ccid,
2411 adjustment_ccid,
2412 category_books_account,
2413 source_type_code,
2414 amount,
2415 request_id)
2416 SELECT dh.asset_id,
2417 dh.code_combination_id,
2418 NULL,
2419 decode(report_type,
2420 'RESERVE',
2421 cb.deprn_reserve_acct,
2422 'REVAL RESERVE',
2423 cb.reval_reserve_acct),
2424 decode(dd.deprn_source_code, 'D', 'DEPRECIATION', 'ADDITION'),
2425 SUM(decode(report_type,
2426 'RESERVE',
2427 dd.deprn_amount -
2428 decode(adj.debit_credit_flag, 'DR', 1, -1) *
2429 nvl(adj.adjustment_amount, 0),
2430 'REVAL RESERVE',
2431 -dd.reval_amortization)),
2432 h_request_id
2433 FROM fa_category_books cb,
2434 fa_distribution_history dh,
2435 fa_asset_history ah,
2436 fa_deprn_detail_mrc_v dd,
2437 fa_deprn_periods_mrc_v dp,
2438 fa_adjustments_mrc_v adj
2439 WHERE dh.book_type_code = distribution_source_book
2440 AND ah.asset_id = dd.asset_id
2441 AND ah.asset_type IN ('CAPITALIZED', 'CIP')
2442 AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
2443 AND nvl(dh.date_ineffective, SYSDATE) <=
2444 nvl(ah.date_ineffective, SYSDATE)
2445 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2446 AND cb.category_id = ah.category_id
2447 AND cb.book_type_code = book
2448 AND ((dd.deprn_source_code = 'B' AND
2449 (dd.period_counter + 1) < period2_pc) OR
2450 (dd.deprn_source_code = 'D'))
2451 AND dd.book_type_code || '' = book
2452 AND dd.asset_id = dh.asset_id
2453 AND dd.distribution_id = dh.distribution_id
2454 AND dd.period_counter BETWEEN period1_pc AND period2_pc
2455 AND dp.book_type_code = dd.book_type_code
2456 AND dp.period_counter = dd.period_counter
2457 AND decode(report_type,
2458 'RESERVE',
2459 cb.deprn_reserve_acct,
2460 'REVAL RESERVE',
2461 cb.reval_reserve_acct) IS NOT NULL
2462 AND (decode(report_type,
2463 'RESERVE',
2464 dd.deprn_amount,
2465 'REVAL RESERVE',
2466 nvl(dd.reval_amortization, 0)) <> 0 OR
2467 decode(report_type,
2468 'RESERVE',
2469 dd.deprn_amount - nvl(dd.deprn_adjustment_amount, 0),
2470 'REVAL RESERVE',
2471 nvl(dd.reval_amortization, 0)) <> 0)
2472 AND adj.asset_id(+) = dd.asset_id
2473 AND adj.book_type_code(+) = dd.book_type_code
2474 AND adj.period_counter_created(+) = dd.period_counter
2475 AND adj.distribution_id(+) = dd.distribution_id
2476 AND adj.source_type_code(+) = 'REVALUATION'
2477 AND adj.adjustment_type(+) = 'EXPENSE'
2478 AND adj.adjustment_amount(+) <> 0
2479 GROUP BY dh.asset_id,
2480 dh.code_combination_id,
2481 decode(report_type,
2482 'RESERVE',
2483 cb.deprn_reserve_acct,
2484 'REVAL RESERVE',
2485 cb.reval_reserve_acct),
2486 dd.deprn_source_code;
2487 ELSE
2488 INSERT INTO fa_balances_reports_itf
2489 (asset_id,
2490 distribution_ccid,
2491 adjustment_ccid,
2492 category_books_account,
2493 source_type_code,
2494 amount,
2495 request_id)
2496 SELECT dh.asset_id,
2497 dh.code_combination_id,
2498 NULL,
2499 decode(report_type,
2500 'RESERVE',
2501 cb.deprn_reserve_acct,
2502 'REVAL RESERVE',
2503 cb.reval_reserve_acct),
2504 decode(dd.deprn_source_code, 'D', 'DEPRECIATION', 'ADDITION'),
2505 SUM(decode(report_type,
2506 'RESERVE',
2507 dd.deprn_amount -
2508 decode(adj.debit_credit_flag, 'DR', 1, -1) *
2509 nvl(adj.adjustment_amount, 0),
2510 'REVAL RESERVE',
2511 -dd.reval_amortization)),
2512 h_request_id
2513 FROM --fa_lookups_b rt, Bug fix 11727910 fa_lookups_b is not used in this report
2514 fa_category_books cb,
2515 fa_distribution_history dh,
2516 fa_asset_history ah,
2517 fa_deprn_detail dd,
2518 fa_deprn_periods dp,
2519 fa_adjustments adj
2520 WHERE dh.book_type_code = distribution_source_book
2521 AND ah.asset_id = dd.asset_id
2522 AND ah.asset_type IN ('CAPITALIZED', 'CIP')
2523 AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
2524 AND nvl(dh.date_ineffective, SYSDATE) <=
2525 nvl(ah.date_ineffective, SYSDATE)
2526 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2527 AND cb.category_id = ah.category_id
2528 AND cb.book_type_code = book
2529 AND ((dd.deprn_source_code = 'B' AND
2530 (dd.period_counter + 1) < period2_pc) OR
2531 (dd.deprn_source_code = 'D'))
2532 AND dd.book_type_code || '' = book
2533 AND dd.asset_id = dh.asset_id
2534 AND dd.distribution_id = dh.distribution_id
2535 AND dd.period_counter BETWEEN period1_pc AND period2_pc
2536 AND dp.book_type_code = dd.book_type_code
2537 AND dp.period_counter = dd.period_counter
2538 AND decode(report_type,
2539 'RESERVE',
2540 cb.deprn_reserve_acct,
2541 'REVAL RESERVE',
2542 cb.reval_reserve_acct) IS NOT NULL
2543 AND (decode(report_type,
2544 'RESERVE',
2545 dd.deprn_amount,
2546 'REVAL RESERVE',
2547 nvl(dd.reval_amortization, 0)) <> 0 OR
2548 decode(report_type,
2549 'RESERVE',
2550 dd.deprn_amount - nvl(dd.deprn_adjustment_amount, 0),
2551 'REVAL RESERVE',
2552 nvl(dd.reval_amortization, 0)) <> 0)
2553 AND adj.asset_id(+) = dd.asset_id
2554 AND adj.book_type_code(+) = dd.book_type_code
2555 AND adj.period_counter_created(+) = dd.period_counter
2556 AND adj.distribution_id(+) = dd.distribution_id
2557 AND adj.source_type_code(+) = 'REVALUATION'
2558 AND adj.adjustment_type(+) = 'EXPENSE'
2559 AND adj.adjustment_amount(+) <> 0
2560 GROUP BY dh.asset_id,
2561 dh.code_combination_id,
2562 decode(report_type,
2563 'RESERVE',
2564 cb.deprn_reserve_acct,
2565 'REVAL RESERVE',
2566 cb.reval_reserve_acct),
2567 dd.deprn_source_code;
2568 END IF;
2569
2570 -- run only if CRL installed
2571 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
2572
2573 -- Get the Group Depreciation Effects
2574
2575 IF (h_reporting_flag = 'R') THEN
2576 INSERT INTO fa_balances_reports_itf
2577 (asset_id,
2578 distribution_ccid,
2579 adjustment_ccid,
2580 category_books_account,
2581 source_type_code,
2582 amount,
2583 request_id)
2584 SELECT dd.asset_id,
2585 gad.deprn_expense_acct_ccid,
2586 gad.deprn_reserve_acct_ccid,
2587 NULL,
2588 'DEPRECIATION',
2589 SUM(dd.deprn_amount),
2590 h_request_id
2591 FROM fa_deprn_summary_mrc_v dd,
2592 fa_group_asset_rules gar,
2593 fa_group_asset_default gad
2594 WHERE dd.book_type_code = book
2595 AND dd.asset_id = gar.group_asset_id
2596 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2597 AND gar.book_type_code = dd.book_type_code
2598 AND gad.book_type_code = gar.book_type_code
2599 AND gad.group_asset_id = gar.group_asset_id
2600 AND dd.period_counter BETWEEN period1_pc AND period2_pc
2601 GROUP BY dd.asset_id,
2602 gad.deprn_expense_acct_ccid,
2603 gad.deprn_reserve_acct_ccid,
2604 NULL,
2605 'DEPRECIATION';
2606 ELSE
2607 INSERT INTO fa_balances_reports_itf
2608 (asset_id,
2609 distribution_ccid,
2610 adjustment_ccid,
2611 category_books_account,
2612 source_type_code,
2613 amount,
2614 request_id)
2615 SELECT dd.asset_id,
2616 gad.deprn_expense_acct_ccid,
2617 gad.deprn_reserve_acct_ccid,
2618 NULL,
2619 'DEPRECIATION',
2620 SUM(dd.deprn_amount),
2621 h_request_id
2622 FROM fa_deprn_summary dd,
2623 fa_group_asset_rules gar,
2624 fa_group_asset_default gad
2625 WHERE dd.book_type_code = book
2626 AND dd.asset_id = gar.group_asset_id
2627 AND dd.asset_id BETWEEN start_range AND end_range --Anuj
2628 AND gar.book_type_code = dd.book_type_code
2629 AND gad.book_type_code = gar.book_type_code
2630 AND gad.group_asset_id = gar.group_asset_id
2631 AND dd.period_counter BETWEEN period1_pc AND period2_pc
2632 GROUP BY dd.asset_id,
2633 gad.deprn_expense_acct_ccid,
2634 gad.deprn_reserve_acct_ccid,
2635 NULL,
2636 'DEPRECIATION';
2637 END IF;
2638 END IF; -- end of CRL check
2639
2640 END get_deprn_effects;
2641
2642 PROCEDURE populate_gt_table(errbuf IN OUT NOCOPY VARCHAR2,
2643 retcode IN OUT NOCOPY VARCHAR2,
2644 book IN VARCHAR2,
2645 report_type IN VARCHAR2,
2646 report_style IN VARCHAR2,
2647 request_id IN NUMBER,
2648 worker_number IN NUMBER,
2649 period1_pc IN NUMBER,
2650 period1_pod IN DATE,
2651 period1_pcd IN DATE,
2652 period2_pc IN NUMBER,
2653 period2_pcd IN DATE,
2654 distribution_source_book IN VARCHAR2) IS
2655 --Define worker cursor here ..
2656
2657 CURSOR c_range(request_id_in NUMBER,
2658 worker_number_in NUMBER) IS
2659 SELECT start_range, end_range
2660 FROM fa_worker_jobs
2661 WHERE request_id = request_id_in
2662 AND worker_num = worker_number_in
2663 AND status = 'IN PROCESS';
2664
2665 start_asset_id NUMBER;
2666 end_asset_id NUMBER;
2667 balance_type VARCHAR2(10);
2668
2669 beg_period_open_date DATE;
2670 beg_period_close_date DATE;
2671 end_period_open_date DATE;
2672 end_period_close_date DATE;
2673 l_request_id NUMBER; -- Bug# 8936484
2674 l_worker_number number;
2675
2676 BEGIN
2677
2678 IF (report_type = 'RESERVE' OR report_type = 'REVAL RESERVE') THEN
2679 balance_type := 'CR';
2680 ELSE
2681 balance_type := 'DR';
2682 END IF;
2683 l_worker_number := worker_number;
2684
2685 l_request_id := request_id;
2686
2687 UPDATE fa_worker_jobs
2688 SET status = 'IN PROCESS'
2689 WHERE status = 'UNASSIGNED'
2690 AND request_id = l_request_id
2691 AND worker_number = l_worker_number;
2692 commit;
2693 OPEN c_range(l_request_id, l_worker_number);
2694 LOOP
2695 BEGIN
2696 FETCH c_range
2697 INTO start_asset_id, end_asset_id;
2698 IF c_range%NOTFOUND THEN
2699 CLOSE c_range;
2700 EXIT;
2701 END IF;
2702
2703 SELECT period_open_date, nvl(period_close_date, SYSDATE)
2704 INTO beg_period_open_date, beg_period_close_date
2705 FROM fa_deprn_periods
2706 WHERE book_type_code = book
2707 AND period_counter = period1_pc;
2708
2709 get_balance(book,
2710 distribution_source_book,
2711 period1_pc - 1,
2712 period1_pc - 1,
2713 beg_period_open_date,
2714 beg_period_close_date,
2715 report_type,
2716 balance_type,
2717 'BEGIN',
2718 start_asset_id,
2719 end_asset_id,
2720 l_request_id);
2721
2722 -- run only if CRL installed
2723
2724 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
2725 get_balance_group_begin(book,
2726 distribution_source_book,
2727 period1_pc - 1,
2728 period1_pc - 1,
2729 beg_period_open_date,
2730 beg_period_close_date,
2731 report_type,
2732 balance_type,
2733 'BEGIN',
2734 start_asset_id,
2735 end_asset_id,
2736 l_request_id);
2737 END IF;
2738
2739 SELECT period_open_date, nvl(period_close_date, SYSDATE)
2740 INTO end_period_open_date, end_period_close_date
2741 FROM fa_deprn_periods
2742 WHERE book_type_code = book
2743 AND period_counter = period2_pc;
2744
2745 -- Get Ending Balance
2746 get_balance(book,
2747 distribution_source_book,
2748 period2_pc,
2749 period1_pc - 1,
2750 end_period_close_date,
2751 end_period_close_date,
2752 report_type,
2753 balance_type,
2754 'END',
2755 start_asset_id,
2756 end_asset_id,
2757 l_request_id);
2758
2759 -- run only if CRL installed
2760 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
2761 get_balance_group_end(book,
2762 distribution_source_book,
2763 period2_pc,
2764 period1_pc - 1,
2765 end_period_close_date,
2766 end_period_close_date,
2767 report_type,
2768 balance_type,
2769 'END',
2770 start_asset_id,
2771 end_asset_id,
2772 l_request_id);
2773 END IF;
2774
2775 get_adjustments(book,
2776 distribution_source_book,
2777 period1_pc,
2778 period2_pc,
2779 report_type,
2780 balance_type,
2781 start_asset_id,
2782 end_asset_id,
2783 l_request_id);
2784
2785 -- run only if CRL installed
2786 IF (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') THEN
2787 get_adjustments_for_group(book,
2788 distribution_source_book,
2789 period1_pc,
2790 period2_pc,
2791 report_type,
2792 balance_type,
2793 start_asset_id,
2794 end_asset_id,
2795 l_request_id);
2796 END IF;
2797
2798 IF (report_type = 'RESERVE' OR report_type = 'REVAL RESERVE') THEN
2799 get_deprn_effects(book,
2800 distribution_source_book,
2801 period1_pc,
2802 period2_pc,
2803 report_type,
2804 start_asset_id,
2805 end_asset_id,
2806 l_request_id);
2807 END IF;
2808 --=================================
2809 -- Update the child status as completed
2810 -- after the end of loop
2811 --=================================
2812 UPDATE fa_worker_jobs
2813 SET status = 'COMPLETED'
2814 WHERE status = 'IN PROCESS'
2815 AND request_id = l_request_id
2816 AND start_range = start_asset_id
2817 AND end_range = end_asset_id;
2818
2819 COMMIT;
2820 EXCEPTION
2821 WHEN OTHERS THEN
2822 UPDATE fa_worker_jobs
2823 SET status = 'FAILED'
2824 WHERE status IN ('IN PROCESS', 'UNASSIGNED')
2825 AND request_id = l_request_id
2826 AND start_range = start_asset_id
2827 AND end_range = end_asset_id;
2828 COMMIT;
2829 EXIT;
2830 END;
2831 END LOOP;
2832
2833 END populate_gt_table;
2834
2835 END fa_balrep_pkg;