[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_RXI_I_WRAP_ASSET_BAL
Source
1 PACKAGE BODY igi_iac_rxi_i_wrap_asset_bal AS
2 /* $Header: igiiaxcb.pls 120.1.12000000.1 2007/08/01 16:20:09 npandya noship $ */
3
4 --===========================FND_LOG.START=====================================
5 g_state_level NUMBER;
6 g_proc_level NUMBER;
7 g_event_level NUMBER;
8 g_excep_level NUMBER;
9 g_error_level NUMBER;
10 g_unexp_level NUMBER;
11 g_path VARCHAR2(100);
12 --===========================FND_LOG.END=======================================
13
14 /****** Start Forward Declarations *****/
15
16 FUNCTION get_flex_segments (p_bookType IN VARCHAR2 )
17 RETURN BOOLEAN;
18
19 FUNCTION get_period_name (p_bookType IN VARCHAR2
20 ,p_period IN VARCHAR2)
21 RETURN BOOLEAN;
22
23 FUNCTION run_s_deprec_report ( p_bookType IN VARCHAR2
24 ,p_period IN VARCHAR2
25 ,p_categoryId IN VARCHAR2
26 ,p_request_id IN NUMBER
27 ,l_user_id IN NUMBER
28 ,p_login_id IN VARCHAR2)
29 RETURN BOOLEAN;
30
31 FUNCTION run_d_deprec_report ( p_bookType IN VARCHAR2
32 ,p_period IN VARCHAR2
33 ,p_categoryId IN VARCHAR2
34 ,p_from_cc IN VARCHAR2
35 ,p_to_cc IN VARCHAR2
36 ,p_from_asset IN VARCHAR2
37 ,p_to_asset IN VARCHAR2
38 ,p_request_id IN NUMBER
39 ,l_user_id IN NUMBER
40 ,p_login_id IN VARCHAR2)
41 RETURN BOOLEAN;
42
43 FUNCTION run_s_operating_report( p_bookType IN VARCHAR2
44 ,p_period IN VARCHAR2
45 ,p_categoryId IN VARCHAR2
46 ,p_request_id IN NUMBER
47 ,l_user_id IN NUMBER
48 ,p_login_id IN VARCHAR2)
49 RETURN BOOLEAN;
50
51 FUNCTION run_d_operating_report( p_bookType IN VARCHAR2
52 ,p_period IN VARCHAR2
53 ,p_categoryId IN VARCHAR2
54 ,p_from_cc IN VARCHAR2
55 ,p_to_cc IN VARCHAR2
56 ,p_from_asset IN VARCHAR2
57 ,p_to_asset IN VARCHAR2
58 ,p_request_id IN NUMBER
59 ,l_user_id IN NUMBER
60 ,p_login_id IN VARCHAR2)
61 RETURN BOOLEAN;
62
63 FUNCTION run_s_reval_report ( p_bookType IN VARCHAR2
64 ,p_period IN VARCHAR2
65 ,p_categoryId IN VARCHAR2
66 ,p_request_id IN NUMBER
67 ,l_user_id IN NUMBER
68 ,p_login_id IN VARCHAR2)
69 RETURN BOOLEAN;
70
71 FUNCTION run_d_reval_report ( p_bookType IN VARCHAR2
72 ,p_period IN VARCHAR2
73 ,p_categoryId IN VARCHAR2
74 ,p_from_cc IN VARCHAR2
75 ,p_to_cc IN VARCHAR2
76 ,p_from_asset IN VARCHAR2
77 ,p_to_asset IN VARCHAR2
78 ,p_request_id IN NUMBER
79 ,l_user_id IN NUMBER
80 ,p_login_id IN VARCHAR2)
81 RETURN BOOLEAN;
82
83 FUNCTION run_s_summary_report ( p_bookType IN VARCHAR2
84 ,p_period IN VARCHAR2
85 ,p_categoryId IN VARCHAR2
86 ,p_request_id IN NUMBER
87 ,l_user_id IN NUMBER
88 ,p_login_id IN VARCHAR2)
89 RETURN BOOLEAN;
90
91 FUNCTION run_d_summary_report ( p_bookType IN VARCHAR2
92 ,p_period IN VARCHAR2
93 ,p_categoryId IN VARCHAR2
94 ,p_from_cc IN VARCHAR2
95 ,p_to_cc IN VARCHAR2
96 ,p_from_asset IN VARCHAR2
97 ,p_to_asset IN VARCHAR2
98 ,p_request_id IN NUMBER
99 ,l_user_id IN NUMBER
100 ,p_login_id IN VARCHAR2)
101 RETURN BOOLEAN;
102
103 FUNCTION get_acct_seg_values ( p_bookType IN VARCHAR2
104 ,p_categoryId IN NUMBER
105 ,p_deprn_res_acct IN OUT NOCOPY fa_category_books.deprn_reserve_acct%TYPE
106 ,p_deprn_exp_acct IN OUT NOCOPY fa_category_books.deprn_expense_acct%TYPE)
107 RETURN BOOLEAN;
108
109 FUNCTION get_acct_seg_val_from_ccid ( p_bookType IN VARCHAR2
110 ,p_categoryId IN NUMBER
111 ,p_deprn_backlog IN OUT NOCOPY VARCHAR2
112 ,p_gen_fund_acct IN OUT NOCOPY VARCHAR2
113 ,p_oper_exp_acct IN OUT NOCOPY VARCHAR2
114 ,p_reval_rsv_acct IN OUT NOCOPY VARCHAR2)
115 RETURN BOOLEAN;
116
117 FUNCTION Delete_Zero_Rows(p_bookType IN VARCHAR2,
118 p_request_id IN NUMBER,
119 p_reptShrtName IN VARCHAR2)
120 RETURN BOOLEAN;
121 /****** End Forward Declarations *****/
122
123 /****** Start of local Global variables ******/
124 l_g_loc_struct NUMBER;
125 l_g_asset_key_struct NUMBER;
126 l_g_cat_struct NUMBER;
127
128 balancing_seg_no VARCHAR2(50);
129 cost_ctr_seg_no VARCHAR2(50);
130 account_seg_no VARCHAR2(50);
131 major_cat_seg_no VARCHAR2(50);
132 minor_cat_seg_no VARCHAR2(50);
133
134 l_company_name VARCHAR2(30);
135 l_fiscal_year_name VARCHAR2(30);
136 l_currency_code VARCHAR2(15);
137 l_period_name VARCHAR2(15);
138 l_fiscal_year NUMBER(4);
139 /****** End of local Global variables *****/
140
141 PROCEDURE run_report ( p_reptShrtName IN VARCHAR2
142 ,p_bookType IN VARCHAR2
143 ,p_period IN VARCHAR2
144 ,p_categoryId IN VARCHAR2
145 ,p_chartOfAccts IN VARCHAR2 DEFAULT NULL
146 ,p_from_cc IN VARCHAR2 DEFAULT NULL
147 ,p_to_cc IN VARCHAR2 DEFAULT NULL
148 ,p_from_asset_num IN VARCHAR2 DEFAULT NULL
149 ,p_to_asset_num IN VARCHAR2 DEFAULT NULL
150 ,p_request_id IN NUMBER
151 ,p_retcode OUT NOCOPY NUMBER
152 ,p_errbuf OUT NOCOPY VARCHAR2)
153 IS
154
155 l_login_id NUMBER;
156 l_user_id NUMBER;
157 l_finalCategoryId VARCHAR(50);
158 l_from_cc VARCHAR2(25);
159 l_to_cc VARCHAR2(25);
160 l_from_asset VARCHAR2(25);
161 l_to_asset VARCHAR2(25);
162 l_path VARCHAR2(150);
163
164 CURSOR c_get_fiscal_year IS
165 SELECT fiscal_year
166 FROM fa_deprn_periods
167 WHERE book_type_code = p_bookType
168 AND period_counter = p_period;
169
170 BEGIN
171
172 l_path := g_path||'run_report';
173
174 -- Enable this for write to log file when testing or debug!!
175 --fa_rx_util_pkg.enable_debug; -- Does not now follow debug standards!!!
176 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_reptShrtName' || p_reptShrtName);
177 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_bookType' || p_bookType);
178 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_period ' || p_period);
179 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_categoryId' || p_categoryId);
180 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_chartOfAccts' || p_chartOfAccts);
181 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_cc' || p_from_cc);
182 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_cc' || p_to_cc);
183 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_asset_num' || p_from_asset_num);
184 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_asset_num ' || p_to_asset_num);
185 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_request_id' || p_request_id);
186
187 l_login_id := NVL(to_number(fnd_profile.value('LOGIN_ID')),-1);
188 l_user_id := NVL(to_number(fnd_profile.value('USER_ID' )),-1);
189
190 IF get_flex_segments (p_bookType) AND get_period_name (p_bookType, p_period) THEN
191 IF p_categoryId IS NULL THEN
192 l_finalCategoryId := 'cf.category_id'; -- Do all categories
193 ELSE
194 l_finalCategoryId := p_categoryId;
195 END IF;
196
197 OPEN c_get_fiscal_year;
198 FETCH c_get_fiscal_year INTO l_fiscal_year;
199 CLOSE c_get_fiscal_year;
200
201 -- Ensure correct value is passed variable cursor, otherwise query will fail
202 IF p_from_cc IS NULL THEN
203 l_from_cc := 'NULL';
204 ELSE
205 l_from_cc := p_from_cc;
206 END IF;
207
208 IF p_to_cc IS NULL THEN
209 l_to_cc := 'NULL';
210 ELSE
211 l_to_cc := p_to_cc;
212 END IF;
213
214 IF p_from_asset_num IS NULL THEN
215 l_from_asset := 'NULL';
216 ELSE
217 l_from_asset := p_from_asset_num;
218 END IF;
219
220 IF p_to_asset_num IS NULL THEN
221 l_to_asset := 'NULL';
222 ELSE
223 l_to_asset := p_to_asset_num;
224 END IF;
225
226 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_s_deprec_report');
227 -- Process Summary Depreciation report
228 IF p_reptShrtName = 'RXIGIIAK' THEN
229 IF NOT run_s_deprec_report ( p_bookType
230 ,p_period
231 ,l_finalCategoryId
232 ,p_request_id
233 ,l_user_id
234 ,l_login_id)
235 THEN
236 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_reptShrtName' || p_reptShrtName);
237 p_retcode := 2;
238 ELSE
239 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
240 p_retcode := 2;
241 END IF;
242 END IF;
243 -- Process Detailed Depreciation report
244 ELSIF p_reptShrtName = 'RXIGIIAD' THEN
245 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_d_deprec_report');
246 IF NOT run_d_deprec_report(p_bookType
247 ,p_period
248 ,l_finalCategoryId
249 ,l_from_cc
250 ,l_to_cc
251 ,l_from_asset
252 ,l_to_asset
253 ,p_request_id
254 ,l_user_id
255 ,l_login_id)
256 THEN
257 p_retcode := 2;
258 ELSE
259 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
260 p_retcode := 2;
261 END IF;
262 END IF;
263 -- Process Summary Operating report
264 ELSIF p_reptShrtName = 'RXIGIIAM' THEN
265 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_s_operating_report');
266 IF NOT run_s_operating_report(p_bookType
267 ,p_period
268 ,l_finalCategoryId
269 ,p_request_id
270 ,l_user_id
271 ,l_login_id)
272 THEN
273 p_retcode := 2;
274 ELSE
275 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
276 p_retcode := 2;
277 END IF;
278 END IF;
279 -- Process Detailed Operating report
280 ELSIF p_reptShrtName = 'RXIGIIAO' THEN
281 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_d_operating_report');
282 IF NOT run_d_operating_report(p_bookType
283 ,p_period
284 ,l_finalCategoryId
285 ,l_from_cc
286 ,l_to_cc
287 ,l_from_asset
288 ,l_to_asset
289 ,p_request_id
290 ,l_user_id
291 ,l_login_id)
292 THEN
293 p_retcode := 2;
294 ELSE
295 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
296 p_retcode := 2;
297 END IF;
298 END IF;
299 -- Process Summary Revaluation report
300 ELSIF p_reptShrtName = 'RXIGIIAL' THEN
301 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_s_reval_report');
302 IF NOT run_s_reval_report(p_bookType
303 ,p_period
304 ,l_finalCategoryId
305 ,p_request_id
306 ,l_user_id
307 ,l_login_id)
308 THEN
309 p_retcode := 2;
310 ELSE
311 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
312 p_retcode := 2;
313 END IF;
314 END IF;
315 -- Process Detailed Revaluation report
316 ELSIF p_reptShrtName = 'RXIGIIAR' THEN
317 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_d_reval_report');
318 IF NOT run_d_reval_report(p_bookType
319 ,p_period
320 ,l_finalCategoryId
321 ,l_from_cc
322 ,l_to_cc
323 ,l_from_asset
324 ,l_to_asset
325 ,p_request_id
326 ,l_user_id
327 ,l_login_id)
328 THEN
329 p_retcode := 2;
330 ELSE
331 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
332 p_retcode := 2;
333 END IF;
334 END IF;
335 -- Process Summary Summary report
336 ELSIF p_reptShrtName = 'RXIGIIAJ' THEN
337 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_s_summary_report');
338 IF NOT run_s_summary_report( p_bookType
339 ,p_period
340 ,l_finalCategoryId
341 ,p_request_id
342 ,l_user_id
343 ,l_login_id)
344 THEN
345 p_retcode := 2;
346 ELSE
347 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
348 p_retcode := 2;
349 END IF;
350 END IF;
351 -- Process Detailed summary report
352 ELSIF p_reptShrtName = 'RXIGIIAB' THEN
353 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'run_d_summary_report');
354 IF NOT run_d_summary_report( p_bookType
355 ,p_period
356 ,l_finalCategoryId
357 ,l_from_cc
358 ,l_to_cc
359 ,l_from_asset
360 ,l_to_asset
361 ,p_request_id
362 ,l_user_id
363 ,l_login_id)
364 THEN
365 p_retcode := 2;
366 ELSE
367 IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
368 p_retcode := 2;
369 END IF;
370 END IF;
371 ELSE
372 p_retcode := 0; -- Nothing to process!!!
373 END IF;
374
375 ELSE
376 p_retcode := 2;
377 END IF;
378 p_retcode := 0;
379
380 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** END ** ');
381
382 EXCEPTION WHEN OTHERS THEN
383 IF SQLCODE <> 0 THEN
384 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
385 END IF;
386 p_retcode := 2;
387
388 END run_report;
389
390 FUNCTION get_flex_segments (p_bookType IN VARCHAR2 )
391 RETURN BOOLEAN IS
392
393 CURSOR c_flex(cp_bookType VARCHAR2) IS
394 SELECT
395 sc.Company_Name,
396 sc.Category_Flex_Structure,
397 sc.Location_Flex_Structure,
398 sc.asset_key_flex_structure,
399 bc.Accounting_Flex_Structure,
400 ct.fiscal_year_name,
401 sob.Currency_Code
402 FROM
403 fa_system_controls sc,
404 fa_book_controls bc,
405 gl_sets_of_books sob,
406 fa_calendar_types ct
407 WHERE
408 bc.Book_Type_Code = cp_bookType
409 AND sob.Set_Of_Books_ID = BC.Set_Of_Books_ID
410 AND bc.deprn_calendar = ct.calendar_type;
411
412 l_cat_struct NUMBER;
413 l_loc_struct NUMBER;
414 l_asset_key_struct NUMBER;
415 l_acct_struct NUMBER;
416 l_selOk BOOLEAN;
417 l_path VARCHAR2(150);
418 BEGIN
419 l_selOk := FALSE;
420 l_path := g_path||'get_flex_segments';
421
422 FOR l_flex in c_flex (p_bookType) LOOP
423 l_company_name := l_flex.Company_Name;
424 l_cat_struct := l_flex.Category_Flex_Structure;
425 l_loc_struct := l_flex.Location_Flex_Structure;
426 l_asset_key_struct := l_flex.asset_key_flex_structure;
427 l_acct_struct := l_flex.Accounting_Flex_Structure;
428 l_fiscal_year_name := l_flex.fiscal_year_name;
429 l_currency_code := l_flex.Currency_Code;
430 l_selOk := TRUE;
431 END LOOP;
432
433 IF NOT l_selOk THEN
434 RETURN FALSE;
435 END IF;
436
437 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_company_name ' || l_company_name);
438 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_cat_struct ' || l_cat_struct);
439 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_loc_struct ' || l_loc_struct);
440 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_key_struct ' || l_asset_key_struct);
441 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_acct_struct ' || l_acct_struct);
442 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path, 'l_fiscal_year_name ' || l_fiscal_year_name);
443 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_currency_code ' || l_currency_code);
444
445 l_g_loc_struct := l_loc_struct;
446 l_g_asset_key_struct := l_asset_key_struct;
447 l_g_cat_struct := l_cat_struct;
448
449 BEGIN
450 balancing_seg_no :=
451 fa_rx_flex_pkg.flex_sql(p_application_id => 101,
452 p_id_flex_code => 'GL#',
453 p_id_flex_num => l_acct_struct,
454 p_table_alias => 'cc',
455 p_mode => 'SELECT',
456 p_qualifier => 'GL_BALANCING');
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Unable to get "Balancing Segment No" : '|| sqlerrm);
461 IF c_flex%ISOPEN THEN
462 CLOSE c_flex;
463 END IF;
464 RETURN FALSE;
465 END;
466
467 BEGIN
468 cost_ctr_seg_no :=
469 fa_rx_flex_pkg.flex_sql(p_application_id => 101,
470 p_id_flex_code => 'GL#',
471 p_id_flex_num => l_acct_struct,
472 p_table_alias => 'cc',
473 p_mode => 'SELECT',
474 p_qualifier => 'FA_COST_CTR');
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 -- bug 3421784, start 1
479 IF c_flex%ISOPEN THEN
480 CLOSE c_flex;
481 END IF;
482 -- bug 3421784, end 1
483 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Unable to get "Cost Centre Segment No" : '|| sqlerrm);
484 RETURN FALSE;
485 END;
486
487 BEGIN
488 account_seg_no :=
489 fa_rx_flex_pkg.flex_sql(p_application_id => 101,
490 p_id_flex_code => 'GL#',
491 p_id_flex_num => l_acct_struct,
492 p_table_alias => 'cc',
493 p_mode => 'SELECT',
494 p_qualifier => 'GL_ACCOUNT');
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Unable to get "Account Segment No" : '|| sqlerrm);
499 IF c_flex%ISOPEN THEN
500 CLOSE c_flex;
501 END IF;
502 RETURN FALSE;
503 END;
504 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'company_seg_no is: ' || balancing_seg_no);
505 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'cost_ctr_seg_no is: ' || cost_ctr_seg_no);
506 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'account_seg_no is: ' || account_seg_no);
507 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_acct_struct is: ' || l_acct_struct);
508
509 BEGIN
510 major_cat_seg_no :=
511 fa_rx_flex_pkg.flex_sql(p_application_id => 140,
512 p_id_flex_code => 'CAT#',
513 p_id_flex_num => l_cat_struct,
514 p_table_alias => 'cf',
515 p_mode => 'SELECT',
516 p_qualifier => 'BASED_CATEGORY');
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 -- bug 3421784, start 2
521 IF c_flex%ISOPEN THEN
522 CLOSE c_flex;
523 END IF;
524 -- bug 3421784, end 2
525 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Unable to get "Major Category Segment No" : '|| sqlerrm);
526 RETURN FALSE;
527 END;
528 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'major_cat_seg_no is: ' || major_cat_seg_no);
529
530 BEGIN
531 minor_cat_seg_no :=
532 fa_rx_flex_pkg.flex_sql(p_application_id => 140,
533 p_id_flex_code => 'CAT#',
534 p_id_flex_num => l_cat_struct,
535 p_table_alias => 'cf',
536 p_mode => 'SELECT',
537 p_qualifier => 'MINOR_CATEGORY');
538
539 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'minor_cat_seg_no is: ' || minor_cat_seg_no);
540 EXCEPTION
541 WHEN OTHERS THEN
542 -- bug 3421784, start 3
543 IF c_flex%ISOPEN THEN
544 CLOSE c_flex;
545 END IF;
546 -- bug 3421784, end 3
547
548 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Unable to get "Minor Category Segment No" : '|| sqlerrm);
549 RETURN FALSE;
550 END;
551
552 RETURN TRUE;
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 -- bug 3421784, start 4
557 IF c_flex%ISOPEN THEN
558 CLOSE c_flex;
559 END IF;
560 -- bug 3421784, end 4
561
562 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within "get_flex_segments" : '|| sqlerrm);
563 RETURN FALSE;
564
565 END get_flex_segments;
566
567 FUNCTION get_period_name (p_bookType IN VARCHAR2
568 ,p_period IN VARCHAR2)
569 RETURN BOOLEAN IS
570
571 CURSOR c_period (cp_bookType VARCHAR2, cp_period VARCHAR2) IS
572 SELECT period_name
573 FROM fa_deprn_periods
574 WHERE Book_Type_Code = cp_bookType
575 AND period_counter = TO_NUMBER(cp_period);
576
577 l_selOk BOOLEAN ;
578 l_path VARCHAR2(150);
579 BEGIN
580 l_selOk := FALSE;
581 l_path := g_path||'get_period_name';
582
583 FOR l_period in c_period (p_bookType, p_period) LOOP
584 l_period_name := l_period.period_name;
585 l_selOk := TRUE;
586 END LOOP;
587
588 IF NOT l_selOk THEN
589 RETURN FALSE;
590 END IF;
591
592 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_period_name ' || l_period_name);
593
594 RETURN TRUE;
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 -- bug 3421784, start 5
599 IF c_period%ISOPEN THEN
600 CLOSE c_period;
601 END IF;
602 -- bug 3421784, end 5
603
604 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path, 'Exception within "get_period_name" : '|| sqlerrm);
605 RETURN FALSE;
606 END get_period_name;
607
608 FUNCTION run_s_deprec_report ( p_bookType IN VARCHAR2
609 ,p_period IN VARCHAR2
610 ,p_categoryId IN VARCHAR2
611 ,p_request_id IN NUMBER
612 ,l_user_id IN NUMBER
613 ,p_login_id IN VARCHAR2)
614 RETURN BOOLEAN IS
615
616 l_select_statement VARCHAR2(15000);
617 TYPE var_cur IS REF CURSOR;
618 ret_lines var_cur;
619
620 l_cost_center VARCHAR2(25);
621 l_book_code VARCHAR2(15);
622 l_reval_cost NUMBER;
623 l_gl_code_seg1 VARCHAR2(25);
624 l_gl_code_seg2 VARCHAR2(25);
625 l_gl_code_seg3 VARCHAR2(25);
626 l_fa_cat_seg1 VARCHAR2(25);
627 l_fa_cat_seg2 VARCHAR2(25);
628 l_deprn_period NUMBER;
629 l_ytd_deprn NUMBER;
630 l_deprn_resv NUMBER;
631 l_deprn_backlog NUMBER;
632 l_deprn_total NUMBER;
633 l_asset_cat_id NUMBER(15);
634 l_asset_tag fa_additions.tag_number%TYPE;
635 l_serial_number fa_additions.serial_number%TYPE;
636 l_life_in_months fa_Books.life_in_months%TYPE;
637 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
638 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
639 l_depreciation_method fa_Books.deprn_method_code%TYPE;
640 l_location_id fa_distribution_history.location_id%TYPE;
641 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
642 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
643 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
644 l_dep_backlog NUMBER;
645 l_gen_fund_acct NUMBER;
646 l_oper_exp_acct NUMBER;
647 l_reval_rsv_acct NUMBER;
648 l_concat_loc VARCHAR2(200);
649 l_concat_asset_key VARCHAR2(200);
650 l_concat_cat VARCHAR2(500);
651 l_loc_segs fa_rx_shared_pkg.Seg_Array;
652 l_asset_segs fa_rx_shared_pkg.Seg_Array;
653 l_cat_segs fa_rx_shared_pkg.Seg_Array;
654 l_stl_rate NUMBER;
655 l_CFDescription VARCHAR2(40);
656 l_path VARCHAR2(150);
657
658 BEGIN
659 l_path := g_path||'run_s_deprec_report';
660
661 l_select_statement := 'SELECT ' ||
662 balancing_seg_no || ', ' ||
663 cost_ctr_seg_no || ', ' ||
664 account_seg_no || ', ' ||
665 major_cat_seg_no || ', ' ||
666 minor_cat_seg_no || ', ' ||
667 'bk.book_type_Code book_type_code,
668 ah.category_id asset_category_id,
669 cf.description category_description,
670 ad.asset_key_ccid asset_key_ccid,
671 bk.deprn_method_code depreciation_method,
672 dh.location_id location_id,
673 cb.deprn_reserve_acct depreciation_reserve_account,
674 cb.asset_cost_acct asset_cost_account,
675 sum(nvl(dd.cost,0)) Reval_Cost,
676 sum(decode(dd.period_counter,'||p_period||',nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0),0)) Period_Deprn,
677 sum(nvl(dd.ytd_deprn,0)) YTD_Deprn,
678 sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Normal,
679 0 Acc_Deprn_backlog,
680 sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Total
681 FROM fa_additions ad,
682 fa_Books bk,
683 fa_distribution_history dh,
684 fa_deprn_Detail dd,
685 gl_code_combinations cc,
686 fa_categories cf,
687 fa_asset_history ah,
688 fa_category_books cb,
689 fa_book_controls fb,
690 fa_deprn_periods fdp
691 WHERE ad.asset_id = bk.asset_id
692 AND cf.category_id = ah.category_id
693 AND cb.category_id = ah.category_id
694 AND bk.book_type_code = :v_bookType
695 AND cf.category_id = ' || p_categoryId || '
696 AND fdp.book_type_code = bk.book_type_code
697 AND fdp.period_counter = :v_period
698 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
699 AND dd.asset_id = bk.asset_id
700 AND dd.book_type_code = bk.book_type_code
701 AND cb.book_type_code = bk.book_type_code
702 AND dh.distribution_id = dd.distribution_id
703 AND dh.code_combination_id = cc.code_combination_id
704 AND dh.asset_id = ah.asset_id
705 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
706 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
707 AND fb.book_type_code = bk.book_type_code
708 AND dd.period_counter =
709 (SELECT max(period_counter)
710 FROM fa_deprn_detail ids
711 WHERE asset_id = bk.asset_id
712 AND book_type_code = bk.book_type_code
713 AND period_counter <= fdp.period_counter )
714 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
715 FROM fa_books ifb
716 WHERE ifb.book_type_code = bk.book_type_code
717 AND ifb.asset_id = bk.asset_id
718 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
719 AND bk.asset_id not in
720 (SELECT asset_id
721 FROM igi_iac_asset_balances
722 WHERE book_type_code = bk.book_type_code
723 AND asset_id = bk.asset_id)
724 GROUP BY bk.book_type_Code , ' ||
725 balancing_seg_no || ', ' ||
726 cost_ctr_seg_no || ', ' ||
727 account_seg_no || ', ' ||
728 major_cat_seg_no || ', ' ||
729 'ah.category_id,
730 cf.description,
731 ad.asset_key_ccid, ' ||
732 minor_cat_seg_no || ', ' ||
733 'bk.deprn_method_code ,
734 dh.location_id ,
735 cb.deprn_reserve_acct ,
736 cb.asset_cost_acct ' ||
737
738 ' UNION
739 SELECT ' ||
740 balancing_seg_no || ', ' ||
741 cost_ctr_seg_no || ', ' ||
742 account_seg_no || ', ' ||
743 major_cat_seg_no || ', ' ||
744 minor_cat_seg_no || ', ' ||
745 'bk.book_type_Code book_type_code,
746 ah.category_id asset_category_id ,
747 cf.description category_description,
748 ad.asset_key_ccid asset_key_ccid,
749 bk.deprn_method_code depreciation_method,
750 dh.location_id location_id,
751 cb.deprn_reserve_acct depreciation_reserve_account,
752 cb.asset_cost_acct asset_cost_account,
753 sum(nvl(( id.adjustment_cost + dd.cost), 0)) Reval_Cost,
754 sum(decode(id.period_counter,'||p_period||',nvl(id.Deprn_Period+ifd.Deprn_Period, 0),0)) Period_Deprn,
755 sum(decode(fd.fiscal_year,'||l_fiscal_year||',nvl(id.Deprn_YTD+ifd.deprn_ytd, 0),0)) YTD_Deprn,
756 sum(nvl(id.Deprn_Reserve + dd.deprn_Reserve, 0)) Acc_Deprn_Normal ,
757 sum(nvl(id.Deprn_Reserve_backlog, 0) ) Acc_Deprn_Backlog ,
758 sum(nvl(id.Deprn_Reserve+dd.deprn_reserve+id.deprn_Reserve_backlog, 0)) Acc_Deprn_Total
759 FROM fa_additions ad ,
760 fa_Books bk ,
761 fa_distribution_history dh,
762 fa_deprn_Detail dd ,
763 igi_iac_det_balances id,
764 igi_iac_fa_deprn ifd,
765 gl_code_combinations cc,
766 fa_categories cf,
767 fa_asset_history ah,
768 fa_category_books cb,
769 fa_book_controls fb,
770 fa_deprn_periods fd,
771 fa_deprn_periods fdp
772 WHERE ad.asset_id = bk.asset_id
773 AND cf.category_id = ah.category_id
774 AND cb.category_id = ah.category_id
775 AND bk.book_Type_code = :v_bookType1
776 AND fdp.book_type_code = bk.book_type_code
777 AND fdp.period_counter = :v_period1
778 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
779 AND dh.book_type_Code = bk.book_type_code
780 AND dh.book_type_code = dd.book_type_code
781 AND cb.book_type_Code = bk.book_type_code
782 AND cf.category_id = ' || p_categoryId || '
783 AND dh.asset_id = dd.asset_id
784 AND dh.distribution_id = dd.distribution_id
785 AND dh.asset_id = ah.asset_id
786 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
787 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
788 AND fb.book_type_code = bk.book_type_code
789 AND dd.period_counter = (SELECT MAX(period_counter)
790 FROM fa_deprn_detail ids
791 WHERE asset_id = bk.asset_id
792 AND book_type_code = bk.book_type_code
793 AND ids.distribution_id = dd.distribution_id
794 AND period_counter <= fdp.period_counter )
795 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
796 FROM fa_books ifb
797 WHERE ifb.book_type_code = bk.book_type_code
798 AND ifb.asset_id = bk.asset_id
799 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
800 AND dh.distribution_id = id.distribution_id
801 AND dh.code_Combination_id = cc.code_combination_id
802 AND id.adjustment_id = ifd.adjustment_id
803 AND id.distribution_id = ifd.distribution_id
804 AND id.period_counter = ifd.period_counter
805 AND id.adjustment_id = ( SELECT max(adjustment_id)
806 FROM igi_iac_transaction_headers it
807 WHERE it.asset_id = bk.asset_id
808 AND it.book_type_code = bk.book_type_Code
809 AND it.period_counter <= fdp.period_counter
810 AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
811 AND fd.period_counter = id.period_counter
812 AND fd.book_type_code = bk.book_type_code
813 GROUP BY bk.book_type_Code , ' ||
814 balancing_seg_no || ', ' ||
815 cost_ctr_seg_no || ', ' ||
816 account_seg_no || ', ' ||
817 major_cat_seg_no || ', ' ||
818 'ah.category_id,
819 cf.description,
820 ad.asset_key_ccid, ' ||
821 minor_cat_seg_no || ', ' ||
822 'bk.deprn_method_code ,
823 dh.location_id ,
824 cb.deprn_reserve_acct ,
825 cb.asset_cost_acct ';
826
827 -- bug 3421784, start 6
828 -- commenting out
829 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
830 -- bug 3421784, end 6
831
832 /* Bug 3490402 */
833 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
834 p_period, /* :v_period */
835 p_bookType, /* :v_bookType1 */
836 p_period; /* :v_period1 */
837
838 LOOP
839
840 fetch ret_lines into
841 l_gl_code_seg1,
842 l_gl_code_seg2,
843 l_gl_code_seg3,
844 l_fa_cat_seg1,
845 l_fa_cat_seg2,
846 l_book_code,
847 l_asset_cat_id,
848 l_CFDescription,
849 l_asset_key_ccid,
850 l_depreciation_method,
851 l_location_id,
852 l_depreciation_reserve_account,
853 l_asset_cost_account,
854 l_reval_cost,
855 l_deprn_period,
856 l_ytd_deprn,
857 l_deprn_resv,
858 l_deprn_backlog,
859 l_deprn_total;
860
861 IF (ret_lines%NOTFOUND) THEN
862 EXIT;
863 END IF;
864
865 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
866
867 -- This will get the CONCATANATED LOCATION
868 fa_rx_shared_pkg.concat_location (
869 struct_id => l_g_loc_struct
870 ,ccid => l_location_id
871 ,concat_string => l_concat_loc
872 ,segarray => l_loc_segs);
873
874 -- This will get the CONCATANATED ASSETKEY
875 fa_rx_shared_pkg.concat_asset_key (
876 struct_id => l_g_asset_key_struct
877 ,ccid => l_asset_key_ccid
878 ,concat_string => l_concat_asset_key
879 ,segarray => l_asset_segs);
880
881 -- This gets the CONCATENATED CATEGORY NAME
882 fa_rx_shared_pkg.concat_category (
883 struct_id => l_g_cat_struct,
884 ccid => l_asset_cat_id,
885 concat_string => l_concat_cat,
886 segarray => l_cat_segs);
887
888 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
889 ,l_asset_cat_id
890 ,l_dep_backlog
891 ,l_gen_fund_acct
892 ,l_oper_exp_acct
893 ,l_reval_rsv_acct
894 )
895 THEN
896 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
897 END IF;*/
898
899 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
900 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
901 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
902 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
903 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
904 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
905 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
906 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
907 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
908
909 INSERT INTO igi_iac_asset_rep_itf (
910 request_id,
911 company_name,
912 book_type_code,
913 period,
914 fiscal_year_name,
915 major_category,
916 cost_center,
917 depreciation_method,
918 conc_asset_key,
919 conc_location,
920 --deprn_exp_acct,
921 --deprn_res_acct,
922 cost_acct,
923 --iac_reval_resv_acct,
924 balancing_segment,
925 --deprn_backlog_acct,
926 --gen_fund_acct,
927 --oper_exp_acct,
928 concat_category,
929 reval_cost,
930 minor_category,
931 deprn_period,
932 ytd_deprn,
933 deprn_resv,
934 deprn_backlog,
935 deprn_total,
936 functional_currency_code,
937 created_by,
938 creation_date,
939 last_updated_by,
940 last_update_date,
941 last_update_login
942 )
943 VALUES
944 (
945 p_request_id,
946 l_company_name,
947 l_book_code,
948 l_period_name,
949 l_fiscal_year_name,
950 l_fa_cat_seg1,
951 l_gl_code_seg2,
952 l_depreciation_method,
953 l_concat_asset_key,
954 l_concat_loc,
955 --l_gl_code_seg3,
956 --l_depreciation_reserve_account,
957 l_asset_cost_account,
958 --l_reval_rsv_acct,
959 l_gl_code_seg1,
960 --l_dep_backlog,
961 --l_gen_fund_acct,
962 --l_oper_exp_acct,
963 l_concat_cat,
964 l_reval_cost,
965 l_fa_cat_seg2,
966 l_deprn_period,
967 l_ytd_deprn,
968 l_deprn_resv,
969 l_deprn_backlog,
970 l_deprn_total,
971 l_currency_code,
972 l_user_id,
973 sysdate,
974 l_user_id,
975 sysdate,
976 p_login_id
977 );
978
979 END LOOP;
980 CLOSE ret_lines;
981 RETURN TRUE;
982 EXCEPTION
983 WHEN OTHERS THEN
984 -- bug 3421784, start 7
985 IF ret_lines%ISOPEN THEN
986 CLOSE ret_lines;
987 END IF;
988 -- bug 3421784, end 7
989
990 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception in run_s_deprec_report: '|| sqlerrm);
991 RETURN FALSE;
992 END run_s_deprec_report;
993
994 FUNCTION run_d_deprec_report ( p_bookType IN VARCHAR2
995 ,p_period IN VARCHAR2
996 ,p_categoryId IN VARCHAR2
997 ,p_from_cc IN VARCHAR2
998 ,p_to_cc IN VARCHAR2
999 ,p_from_asset IN VARCHAR2
1000 ,p_to_asset IN VARCHAR2
1001 ,p_request_id IN NUMBER
1002 ,l_user_id IN NUMBER
1003 ,p_login_id IN VARCHAR2)
1004 RETURN BOOLEAN IS
1005
1006 l_select_statement VARCHAR2(15000);
1007 TYPE var_cur IS REF CURSOR;
1008 ret_lines var_cur;
1009
1010 l_cost_center VARCHAR2(25);
1011 l_book_code VARCHAR2(15);
1012 l_reval_cost NUMBER;
1013 l_asset_number VARCHAR2(15);
1014 l_gl_code_seg1 VARCHAR2(25);
1015 l_gl_code_seg2 VARCHAR2(25);
1016 l_gl_code_seg3 VARCHAR2(25);
1017 l_fa_cat_seg1 VARCHAR2(25);
1018 l_fa_cat_seg2 VARCHAR2(25);
1019 l_deprn_period NUMBER;
1020 l_ytd_deprn NUMBER;
1021 l_deprn_resv NUMBER;
1022 l_deprn_backlog NUMBER;
1023 l_deprn_total NUMBER;
1024 l_asset_cat_id NUMBER(15);
1025 l_asset_tag fa_additions.tag_number%TYPE;
1026 l_parent_id fa_additions.parent_asset_id%TYPE;
1027 l_parent_no VARCHAR2(15);
1028 l_serial_number fa_additions.serial_number%TYPE;
1029 l_life_in_months fa_Books.life_in_months%TYPE;
1030 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
1031 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
1032 l_depreciation_method fa_Books.deprn_method_code%TYPE;
1033 l_location_id fa_distribution_history.location_id%TYPE;
1034 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
1035 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
1036 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
1037 l_dep_backlog NUMBER;
1038 l_gen_fund_acct NUMBER;
1039 l_oper_exp_acct NUMBER;
1040 l_reval_rsv_acct NUMBER;
1041 l_concat_loc VARCHAR2(240);
1042 l_concat_asset_key VARCHAR2(240);
1043 l_concat_cat VARCHAR2(600);
1044 l_loc_segs fa_rx_shared_pkg.Seg_Array;
1045 l_asset_segs fa_rx_shared_pkg.Seg_Array;
1046 l_cat_segs fa_rx_shared_pkg.Seg_Array;
1047 l_stl_rate NUMBER;
1048 l_CFDescription VARCHAR2(40);
1049 l_ADDescription fa_additions.description%type;
1050 l_from_asset VARCHAR2(100);
1051 l_to_asset VARCHAR2(100);
1052 l_from_cc VARCHAR2(100);
1053 l_to_cc VARCHAR2(100);
1054 l_path VARCHAR2(150);
1055
1056 BEGIN
1057 l_path := g_path||'run_d_deprec_report';
1058
1059 l_select_statement := 'SELECT ' ||
1060 balancing_seg_no || ', ' ||
1061 cost_ctr_seg_no || ', ' ||
1062 account_seg_no || ', ' ||
1063 major_cat_seg_no || ', ' ||
1064 minor_cat_seg_no || ', ' ||
1065 'bk.book_type_Code book_type_code,
1066 ah.category_id asset_category_id,
1067 cf.description category_description,
1068 ad.asset_number asset_number,
1069 ad.description asset_description,
1070 ad.tag_number asset_tag,
1071 ad.parent_asset_id parent_id,
1072 ad.serial_number serial_number,
1073 ad.asset_key_ccid asset_key_ccid,
1074 bk.life_in_months life_in_months,
1075 bk.date_placed_in_service date_placed_in_service,
1076 bk.deprn_method_code depreciation_method,
1077 dh.location_id location_id,
1078 cb.deprn_reserve_acct depreciation_reserve_account,
1079 cb.asset_cost_acct asset_cost_account,
1080 sum(nvl(dd.cost,0)) Reval_Cost,
1081 sum(decode(dd.period_counter,'||p_period||',nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0),0)) Period_Deprn,
1082 sum(nvl(dd.ytd_deprn,0)) YTD_Deprn,
1083 sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Normal,
1084 0 Acc_Deprn_backlog,
1085 sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Total
1086 FROM fa_additions ad,
1087 fa_Books bk,
1088 fa_distribution_history dh,
1089 fa_deprn_Detail dd,
1090 gl_code_combinations cc,
1091 fa_categories cf,
1092 fa_asset_history ah,
1093 fa_category_books cb,
1094 fa_book_controls fb,
1095 fa_deprn_periods fdp
1096 WHERE ad.asset_id = bk.asset_id
1097 AND cf.category_id = ah.category_id
1098 AND cb.category_id = ah.category_id
1099 AND bk.book_type_code = :v_bookType
1100 AND NOT EXISTS
1101 (SELECT 1
1102 FROM igi_iac_det_balances db
1103 WHERE db.book_type_code = bk.book_type_code
1104 AND db.asset_id = bk.asset_id)
1105 AND fdp.period_counter = :v_period
1106 AND fdp.book_type_code = bk.book_type_code
1107 AND cf.category_id = ' || p_categoryId || '
1108 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
1109 AND dd.asset_id = bk.asset_id
1110 AND dd.book_type_code = bk.book_type_code
1111 AND cb.book_type_code = bk.book_type_code
1112 AND dh.book_type_code = bk.book_type_code
1113 AND dh.asset_id = bk.asset_id
1114 AND dh.distribution_id = dd.distribution_id
1115 AND dh.code_combination_id = cc.code_combination_id
1116 AND ah.asset_id = bk.asset_id
1117 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
1118 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
1119 AND fb.book_type_code = bk.book_type_code
1120 AND dd.period_counter =
1121 (SELECT max(period_counter)
1122 FROM fa_deprn_detail ids
1123 WHERE asset_id = bk.asset_id
1124 AND book_type_code = bk.book_type_code
1125 AND period_counter <= fdp.period_counter )
1126 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
1127 FROM fa_books ifb
1128 WHERE ifb.book_type_code = bk.book_type_code
1129 AND ifb.asset_id = bk.asset_id
1130 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
1131 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc, ' || cost_ctr_seg_no || ' )
1132 AND nvl( :v_to_cc,' || cost_ctr_seg_no || ')
1133 AND ad.asset_number between nvl( :v_from_asset, ad.asset_number)
1134 AND nvl( :v_to_asset, ad.asset_number)
1135 GROUP BY bk.book_type_Code , ' ||
1136 balancing_seg_no || ', ' ||
1137 cost_ctr_seg_no || ', ' ||
1138 account_seg_no || ', ' ||
1139 major_cat_seg_no || ', ' ||
1140 'ah.category_id,
1141 cf.description, ' ||
1142 minor_cat_seg_no || ', ' ||
1143 'ad.asset_number,
1144 ad.description,
1145 ad.tag_number ,
1146 ad.parent_asset_id ,
1147 ad.serial_number ,
1148 ad.asset_key_ccid ,
1149 bk.life_in_months ,
1150 bk.date_placed_in_service ,
1151 bk.deprn_method_code ,
1152 dh.location_id ,
1153 cb.deprn_reserve_acct ,
1154 cb.asset_cost_acct ' ||
1155 ' UNION
1156 SELECT ' ||
1157 balancing_seg_no || ', ' ||
1158 cost_ctr_seg_no || ', ' ||
1159 account_seg_no || ', ' ||
1160 major_cat_seg_no || ', ' ||
1161 minor_cat_seg_no || ', ' ||
1162 'bk.book_type_Code book_type_code,
1163 ah.category_id asset_category_id ,
1164 cf.description category_description,
1165 ad.asset_number asset_number ,
1166 ad.description asset_description,
1167 ad.tag_number asset_tag,
1168 ad.parent_asset_id parent_id,
1169 ad.serial_number serial_number,
1170 ad.asset_key_ccid asset_key_ccid,
1171 bk.life_in_months life_in_months,
1172 bk.date_placed_in_service date_placed_in_service,
1173 bk.deprn_method_code depreciation_method,
1174 dh.location_id location_id,
1175 cb.deprn_reserve_acct depreciation_reserve_account,
1176 cb.asset_cost_acct asset_cost_account,
1177 sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
1178 sum(nvl(decode(id.period_counter,'||p_period||',id.Deprn_Period+ifd.Deprn_Period,0), 0)) Period_Deprn,
1179 sum(nvl(decode(fd.fiscal_year,'||l_fiscal_year||',id.Deprn_YTD+ifd.deprn_ytd, 0),0)) YTD_Deprn,
1180 sum(nvl(id.Deprn_Reserve + dd.deprn_Reserve, 0)) Acc_Deprn_Normal ,
1181 sum(nvl(id.Deprn_Reserve_backlog, 0) ) Acc_Deprn_Backlog ,
1182 sum(nvl(id.Deprn_Reserve+dd.deprn_reserve+id.deprn_Reserve_backlog, 0)) Acc_Deprn_Total
1183 FROM fa_additions ad ,
1184 fa_Books bk ,
1185 fa_distribution_history dh,
1186 fa_deprn_Detail dd ,
1187 igi_iac_det_balances id,
1188 igi_iac_fa_deprn ifd,
1189 gl_code_combinations cc,
1190 fa_categories cf,
1191 fa_asset_history ah,
1192 fa_category_books cb,
1193 fa_book_controls fb,
1194 fa_deprn_periods fd,
1195 fa_deprn_periods fdp
1196 WHERE ad.asset_id = bk.asset_id
1197 AND bk.transaction_header_id_out IS NULL
1198 AND cf.category_id = ah.category_id
1199 AND cb.category_id = ah.category_id
1200 AND bk.book_Type_code = :v_bookType1
1201 AND fdp.book_type_code = bk.book_type_code
1202 AND fdp.period_counter = :v_period1
1203 AND dh.book_type_Code = bk.book_type_code
1204 AND dd.book_type_code = bk.book_type_code
1205 AND cb.book_type_Code = bk.book_type_code
1206 AND cf.category_id = ' || p_categoryId || '
1207 AND dd.asset_id = bk.asset_id
1208 AND dh.distribution_id = dd.distribution_id
1209 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
1210 AND dh.asset_id = bk.asset_id
1211 AND dh.asset_id = ah.asset_id
1212 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
1213 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
1214 AND fb.book_type_code = bk.book_type_code
1215 AND dd.period_counter = (SELECT MAX(period_counter)
1216 FROM fa_deprn_detail ids
1217 WHERE ids.asset_id = bk.asset_id
1218 AND ids.book_type_code = bk.book_type_code
1219 AND ids.distribution_id = dd.distribution_id
1220 AND ids.period_counter <= fdp.period_counter )
1221 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
1222 FROM fa_books ifb
1223 WHERE ifb.book_type_code = bk.book_type_code
1224 AND ifb.asset_id = bk.asset_id
1225 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
1226 AND id.distribution_id = dd.distribution_id
1227 AND dh.code_Combination_id = cc.code_combination_id
1228 AND id.adjustment_id = ifd.adjustment_id
1229 AND id.distribution_id = ifd.distribution_id
1230 AND id.period_counter = ifd.period_counter
1231 AND id.adjustment_id = ( SELECT max(adjustment_id)
1232 FROM igi_iac_transaction_headers it
1233 WHERE it.asset_id = bk.asset_id
1234 AND it.book_type_code = bk.book_type_Code
1235 AND it.period_counter <= fdp.period_counter
1236 AND it.adjustment_status not in( ''PREVIEW'', ''OBSOLETE''))
1237 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1, ' || cost_ctr_seg_no || ' )
1238 AND nvl( :v_to_cc1,' || cost_ctr_seg_no || ')
1239 AND ad.asset_number between nvl( :v_from_asset1, ad.asset_number)
1240 AND nvl( :v_to_asset1, ad.asset_number)
1241 AND fd.period_counter = id.period_counter
1242 AND fd.book_type_code = bk.book_type_code
1243 GROUP BY bk.book_type_Code , ' ||
1244 balancing_seg_no || ', ' ||
1245 cost_ctr_seg_no || ', ' ||
1246 account_seg_no || ', ' ||
1247 major_cat_seg_no || ', ' ||
1248 'ah.category_id,
1249 cf.description, ' ||
1250 minor_cat_seg_no || ', ' ||
1251 'ad.asset_number,
1252 ad.description,
1253 ad.tag_number ,
1254 ad.parent_asset_id ,
1255 ad.serial_number ,
1256 ad.asset_key_ccid,
1257 bk.life_in_months ,
1258 bk.date_placed_in_service ,
1259 bk.deprn_method_code ,
1260 dh.location_id ,
1261 cb.deprn_reserve_acct ,
1262 cb.asset_cost_acct';
1263
1264 -- bug 3421784, start 8
1265 -- commenting out
1266 --igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
1267 -- bug 3421784, end 8
1268
1269 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
1270 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_categoryId --> ' || p_categoryId);
1271 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_bookType --> ' || p_bookType);
1272 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_period --> ' || p_period);
1273 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_cc --> ' || nvl(p_from_cc,1));
1274 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_cc --> ' || nvl(p_to_cc,1));
1275 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_asset --> ' || nvl(p_from_asset,1));
1276 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_asset --> ' || nvl(p_to_asset,1));
1277
1278 IF p_from_asset = 'NULL' THEN
1279 l_from_asset := NULL;
1280 ELSE
1281 l_from_asset := p_from_asset;
1282 END IF;
1283
1284 IF p_to_asset = 'NULL' THEN
1285 l_to_asset := NULL;
1286 ELSE
1287 l_to_asset := p_to_asset;
1288 END IF;
1289
1290 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_asset --> ' || nvl(l_from_asset,1));
1291 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_asset --> ' || nvl(l_to_asset,1));
1292
1293 IF p_from_cc = 'NULL' THEN
1294 l_from_cc := NULL;
1295 ELSE
1296 l_from_cc := p_from_cc;
1297 END IF;
1298
1299 IF p_to_cc = 'NULL' THEN
1300 l_to_cc := NULL;
1301 ELSE
1302 l_to_cc := p_to_cc;
1303 END IF;
1304
1305 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_cc --> ' || nvl(l_from_cc,1));
1306 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_cc --> ' || nvl(l_to_cc,1));
1307
1308 /* Bug 3490402 */
1309 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
1310 p_period, /* :v_period */
1311 l_from_cc, /* :v_from_cc */
1312 l_to_cc, /* :v_to_cc, */
1313 l_from_asset, /* :v_from_asset */
1314 l_to_asset, /* :v_to_asset */
1315 p_bookType, /* :v_bookType1 */
1316 p_period, /* :v_period1 */
1317 l_from_cc, /* :v_from_cc1 */
1318 l_to_cc, /* :v_to_cc1 */
1319 l_from_asset, /* :v_from_asset1 */
1320 l_to_asset; /* :v_to_asset1 */
1321
1322 LOOP
1323 fetch ret_lines into
1324 l_gl_code_seg1,
1325 l_gl_code_seg2,
1326 l_gl_code_seg3,
1327 l_fa_cat_seg1,
1328 l_fa_cat_seg2,
1329 l_book_code,
1330 l_asset_cat_id,
1331 l_CFDescription,
1332 l_asset_number,
1333 l_ADDescription,
1334 l_asset_tag,
1335 l_parent_id,
1336 l_serial_number,
1337 l_asset_key_ccid,
1338 l_life_in_months,
1339 l_date_placed_in_service,
1340 l_depreciation_method,
1341 l_location_id,
1342 l_depreciation_reserve_account,
1343 l_asset_cost_account,
1344 l_reval_cost,
1345 l_deprn_period,
1346 l_ytd_deprn,
1347 l_deprn_resv,
1348 l_deprn_backlog,
1349 l_deprn_total;
1350
1351 IF (ret_lines%NOTFOUND) THEN
1352 EXIT;
1353 END IF;
1354
1355 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
1356
1357 /* StatReq - The following if statement has been added to calculate the annual depreciation rate
1358 for straight-line, calculated depreciation methods */
1359
1360 -- Calculate STL_RATE from life_in_months
1361 IF (l_life_in_months > 0)
1362 THEN
1363 l_stl_rate := 12 / l_life_in_months * 100;
1364 ELSE
1365 l_stl_rate := NULL;
1366 END IF;
1367
1368 -- This will get the CONCATANATED LOCATION
1369 fa_rx_shared_pkg.concat_location (
1370 struct_id => l_g_loc_struct
1371 ,ccid => l_location_id
1372 ,concat_string => l_concat_loc
1373 ,segarray => l_loc_segs);
1374
1375 -- This will get the CONCATANATED ASSETKEY
1376 fa_rx_shared_pkg.concat_asset_key (
1377 struct_id => l_g_asset_key_struct
1378 ,ccid => l_asset_key_ccid
1379 ,concat_string => l_concat_asset_key
1380 ,segarray => l_asset_segs);
1381
1382 -- This gets the CONCATENATED CATEGORY NAME
1383 fa_rx_shared_pkg.concat_category (
1384 struct_id => l_g_cat_struct,
1385 ccid => l_asset_cat_id,
1386 concat_string => l_concat_cat,
1387 segarray => l_cat_segs);
1388
1389 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
1390 ,l_asset_cat_id
1391 ,l_dep_backlog
1392 ,l_gen_fund_acct
1393 ,l_oper_exp_acct
1394 ,l_reval_rsv_acct
1395 )
1396 THEN
1397 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
1398 END IF;*/
1399
1400 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_number ' || l_asset_number);
1401 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_parent_id ' || l_parent_id);
1402 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
1403 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
1404 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
1405 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
1406 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path, 'l_concat_cat ' || l_concat_cat);
1407 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
1408 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
1409 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path, 'l_reval_rsv_acct ' || l_reval_rsv_acct);
1410
1411 l_parent_no := NULL;
1412
1413 IF l_parent_id IS NOT NULL THEN
1414 l_parent_no := l_asset_number;
1415 END IF;
1416
1417 INSERT INTO igi_iac_asset_rep_itf (
1418 request_id,
1419 company_name,
1420 book_type_code,
1421 period,
1422 fiscal_year_name,
1423 major_category,
1424 cost_center,
1425 asset_number,
1426 asset_description,
1427 asset_tag,
1428 parent_no,
1429 serial_no,
1430 life_months,
1431 stl_rate,
1432 dpis,
1433 depreciation_method,
1434 conc_asset_key,
1435 conc_location,
1436 --deprn_exp_acct,
1437 --deprn_res_acct,
1438 cost_acct,
1439 --iac_reval_resv_acct,
1440 balancing_segment,
1441 --deprn_backlog_acct,
1442 --gen_fund_acct,
1443 --oper_exp_acct,
1444 concat_category,
1445 reval_cost,
1446 minor_category,
1447 deprn_period,
1448 ytd_deprn,
1449 deprn_resv,
1450 deprn_backlog,
1451 deprn_total,
1452 functional_currency_code,
1453 created_by,
1454 creation_date,
1455 last_updated_by,
1456 last_update_date,
1457 last_update_login
1458 )
1459 VALUES
1460 (
1461 p_request_id,
1462 l_company_name,
1463 l_book_code,
1464 l_period_name,
1465 l_fiscal_year_name,
1466 l_fa_cat_seg1,
1467 l_gl_code_seg2,
1468 l_asset_number,
1469 l_ADDescription,
1470 l_asset_tag,
1471 l_parent_no,
1472 l_serial_number,
1473 l_life_in_months,
1474 l_stl_rate,
1475 l_date_placed_in_service,
1476 l_depreciation_method,
1477 l_concat_asset_key,
1478 l_concat_loc,
1479 --l_gl_code_seg3,
1480 --l_depreciation_reserve_account,
1481 l_asset_cost_account,
1482 --l_reval_rsv_acct,
1483 l_gl_code_seg1,
1484 --l_dep_backlog,
1485 --l_gen_fund_acct,
1486 --l_oper_exp_acct,
1487 l_concat_cat,
1488 l_reval_cost,
1489 l_fa_cat_seg2,
1490 l_deprn_period,
1491 l_ytd_deprn,
1492 l_deprn_resv,
1493 l_deprn_backlog,
1494 l_deprn_total,
1495 l_currency_code,
1496 l_user_id,
1497 sysdate,
1498 l_user_id,
1499 sysdate,
1500 p_login_id
1501 );
1502
1503 END LOOP;
1504 CLOSE ret_lines;
1505
1506 RETURN TRUE;
1507
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510 -- bug 3421784, start 9
1511 IF ret_lines%ISOPEN THEN
1512 CLOSE ret_lines;
1513 END IF;
1514 -- bug 3421784, end 9
1515
1516 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception in run_d_deprec_report: '|| sqlerrm);
1517 RETURN FALSE;
1518 END run_d_deprec_report;
1519
1520 FUNCTION run_s_operating_report( p_bookType IN VARCHAR2
1521 ,p_period IN VARCHAR2
1522 ,p_categoryId IN VARCHAR2
1523 ,p_request_id IN NUMBER
1524 ,l_user_id IN NUMBER
1525 ,p_login_id IN VARCHAR2)
1526 RETURN BOOLEAN IS
1527
1528 l_select_statement VARCHAR2(15000);
1529 TYPE var_cur IS REF CURSOR;
1530 ret_lines var_cur;
1531
1532 l_cost_center VARCHAR2(25);
1533 l_book_code VARCHAR2(15);
1534 l_reval_cost NUMBER;
1535 l_gl_code_seg1 VARCHAR2(25);
1536 l_gl_code_seg2 VARCHAR2(25);
1537 l_gl_code_seg3 VARCHAR2(25);
1538 l_fa_cat_seg1 VARCHAR2(25);
1539 l_fa_cat_seg2 VARCHAR2(25);
1540 l_oper_exp NUMBER;
1541 l_oper_exp_backlog NUMBER;
1542 l_oper_exp_net NUMBER;
1543 l_asset_cat_id NUMBER(15);
1544 l_asset_tag fa_additions.tag_number%TYPE;
1545 l_serial_number fa_additions.serial_number%TYPE;
1546 l_life_in_months fa_Books.life_in_months%TYPE;
1547 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
1548 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
1549 l_depreciation_method fa_Books.deprn_method_code%TYPE;
1550 l_location_id fa_distribution_history.location_id%TYPE;
1551 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
1552 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
1553 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
1554 l_dep_backlog NUMBER;
1555 l_gen_fund_acct NUMBER;
1556 l_oper_exp_acct NUMBER;
1557 l_reval_rsv_acct NUMBER;
1558 l_concat_loc VARCHAR2(200);
1559 l_concat_asset_key VARCHAR2(200);
1560 l_concat_cat VARCHAR2(500);
1561 l_loc_segs fa_rx_shared_pkg.Seg_Array;
1562 l_asset_segs fa_rx_shared_pkg.Seg_Array;
1563 l_cat_segs fa_rx_shared_pkg.Seg_Array;
1564 l_stl_rate NUMBER;
1565 l_CFDescription VARCHAR2(40);
1566 l_path VARCHAR2(150);
1567
1568 BEGIN
1569 l_path := g_path||'run_s_operating_report';
1570
1571 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** START run_s_operating_report ** ');
1572
1573 l_select_statement := 'SELECT ' ||
1574 balancing_seg_no || ', ' ||
1575 cost_ctr_seg_no || ', ' ||
1576 account_seg_no || ', ' ||
1577 major_cat_seg_no || ', ' ||
1578 minor_cat_seg_no || ', ' ||
1579 'bk.book_type_Code book_type_code,
1580 ah.category_id category_id,
1581 cf.description category_description,
1582 ad.asset_key_ccid asset_key_ccid,
1583 bk.deprn_method_code depreciation_method,
1584 dh.location_id location_id,
1585 cb.deprn_reserve_acct depreciation_reserve_account,
1586 cb.asset_cost_acct asset_cost_account,
1587 sum(nvl(dd.cost,0)) Reval_Cost,
1588 0 Oper_Acct_Cost,
1589 0 Oper_Acct_Backlog,
1590 0 Oper_Acct_Net
1591 FROM fa_additions ad,
1592 fa_Books bk,
1593 fa_distribution_history dh,
1594 fa_deprn_Detail dd,
1595 gl_code_combinations cc,
1596 fa_categories cf,
1597 fa_category_books cb,
1598 fa_book_controls fb,
1599 fa_deprn_periods fdp,
1600 fa_asset_history ah
1601 WHERE ad.asset_id = bk.asset_id
1602 AND ah.asset_id = bk.asset_id
1603 AND cf.category_id=ah.category_id
1604 AND cb.category_id = ah.category_id
1605 AND cf.category_id = ' || p_categoryId || '
1606 AND bk.book_type_code = :v_bookType
1607 AND fdp.book_type_code = bk.book_type_code
1608 AND fdp.period_counter = :v_period
1609 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
1610 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
1611 FROM fa_books ifb
1612 WHERE ifb.book_type_code = bk.book_type_code
1613 AND ifb.asset_id = bk.asset_id
1614 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
1615 AND dd.asset_id = bk.asset_id
1616 AND dd.book_type_code = bk.book_type_code
1617 AND cb.book_type_code = bk.book_type_code
1618 AND dh.distribution_id = dd.distribution_id
1619 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
1620 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
1621 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
1622 AND dh.code_combination_id = cc.code_combination_id
1623 AND fb.book_type_code = bk.book_type_code
1624 AND dd.period_counter =
1625 (SELECT max(period_counter)
1626 FROM fa_deprn_summary
1627 WHERE asset_id = bk.asset_id
1628 AND book_type_code = bk.book_type_code
1629 AND period_counter <= fdp.period_counter )
1630 AND bk.asset_id not in
1631 (SELECT asset_id
1632 FROM igi_iac_asset_balances
1633 WHERE book_type_code = bk.book_type_code
1634 AND asset_id = bk.asset_id)
1635 GROUP BY ' || minor_cat_seg_no || ', ' ||
1636 'bk.book_type_Code ,
1637 ah.category_id,
1638 cf.description ,
1639 ad.asset_key_ccid , ' ||
1640 balancing_seg_no || ', ' ||
1641 cost_ctr_seg_no || ', ' ||
1642 account_seg_no || ', ' ||
1643 major_cat_seg_no || ', ' ||
1644 'bk.deprn_method_code ,
1645 dh.location_id ,
1646 cb.deprn_reserve_acct ,
1647 cb.asset_cost_acct ' ||
1648 ' UNION
1649 SELECT ' ||
1650 balancing_seg_no || ', ' ||
1651 cost_ctr_seg_no || ', ' ||
1652 account_seg_no || ', ' ||
1653 major_cat_seg_no || ', ' ||
1654 minor_cat_seg_no || ', ' ||
1655 'bk.book_type_Code book_type_code,
1656 ah.category_id category_id,
1657 cf.description category_description,
1658 ad.asset_key_ccid asset_key_ccid,
1659 bk.deprn_method_code depreciation_method,
1660 dh.location_id location_id,
1661 cb.deprn_reserve_acct depreciation_reserve_account,
1662 cb.asset_cost_acct asset_cost_account,
1663 sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
1664 sum(nvl(id.operating_acct_cost * -1,0)) Oper_Acct_Cost,
1665 sum(nvl(id.operating_acct_backlog * -1 ,0)) Oper_Acct_Backlog,
1666 sum(nvl(id.operating_acct_net * -1 ,0)) Oper_Acct_Net
1667 FROM fa_additions ad ,
1668 fa_Books bk ,
1669 fa_distribution_history dh,
1670 fa_deprn_Detail dd ,
1671 igi_iac_det_balances id ,
1672 gl_code_combinations cc,
1673 fa_categories cf,
1674 fa_category_books cb,
1675 fa_book_controls fb,
1676 fa_deprn_periods fdp,
1677 fa_asset_history ah
1678 WHERE ad.asset_id = bk.asset_id
1679 AND ad.asset_id =dh.asset_id
1680 AND ah.asset_id = bk.asset_id
1681 AND cf.category_id=ah.category_id
1682 AND cb.category_id = ah.category_id
1683 AND cf.category_id = ' || p_categoryId || '
1684 AND bk.book_Type_code = :v_bookType1
1685 AND fdp.book_type_code = bk.book_type_code
1686 AND fdp.period_counter = :v_period1
1687 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
1688 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
1689 FROM fa_books ifb
1690 WHERE ifb.book_type_code = bk.book_type_code
1691 AND ifb.asset_id = bk.asset_id
1692 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
1693 AND dh.book_type_Code = bk.book_type_code
1694 AND dh.book_type_code = dd.book_type_code
1695 AND cb.book_type_Code = bk.book_type_code
1696 AND dh.asset_id = dd.asset_id
1697 AND dh.distribution_id = dd.distribution_id
1698 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
1699 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
1700 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
1701 AND fb.book_type_code = bk.book_type_code
1702 AND dd.period_counter = (SELECT MAX(period_counter)
1703 FROM fa_deprn_summary
1704 WHERE asset_id = bk.asset_id
1705 AND book_type_code = bk.book_type_code
1706 AND period_counter <= fdp.period_counter )
1707 AND dh.distribution_id = id.distribution_id
1708 AND dh.code_Combination_id = cc.code_combination_id
1709 AND id.adjustment_id =
1710 ( SELECT max(adjustment_id)
1711 FROM igi_iac_transaction_headers it
1712 WHERE it.asset_id = bk.asset_id
1713 AND it.book_type_code = bk.book_type_Code
1714 AND period_counter <= fdp.period_counter
1715 AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
1716 GROUP BY ' || minor_cat_seg_no || ', ' ||
1717 'bk.book_type_Code ,
1718 ah.category_id,
1719 cf.description ,
1720 ad.asset_key_ccid,
1721 bk.deprn_method_code ,
1722 dh.location_id ,
1723 cb.deprn_reserve_acct ,
1724 cb.asset_cost_acct, ' ||
1725 balancing_seg_no || ', ' ||
1726 cost_ctr_seg_no || ', ' ||
1727 account_seg_no || ', ' ||
1728 major_cat_seg_no;
1729
1730 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after select ');
1731 -- bug 3421784, start 10
1732 -- commenting out
1733 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
1734 -- bug 3421784, end 10
1735
1736 /* Bug 3490402 */
1737 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
1738 p_period, /* :v_period */
1739 p_bookType, /* :v_bookType1 */
1740 p_period; /* :v_period1 */
1741 LOOP
1742 FETCH ret_lines INTO
1743 l_gl_code_seg1,
1744 l_gl_code_seg2,
1745 l_gl_code_seg3,
1746 l_fa_cat_seg1,
1747 l_fa_cat_seg2,
1748 l_book_code,
1749 l_asset_cat_id,
1750 l_CFDescription,
1751 l_asset_key_ccid,
1752 l_depreciation_method,
1753 l_location_id,
1754 l_depreciation_reserve_account,
1755 l_asset_cost_account,
1756 l_reval_cost,
1757 l_oper_exp,
1758 l_oper_exp_backlog,
1759 l_oper_exp_net;
1760
1761 IF (ret_lines%NOTFOUND) THEN
1762 EXIT;
1763 END IF;
1764
1765 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
1766
1767 -- This will get the CONCATANATED LOCATION
1768 fa_rx_shared_pkg.concat_location (
1769 struct_id => l_g_loc_struct
1770 ,ccid => l_location_id
1771 ,concat_string => l_concat_loc
1772 ,segarray => l_loc_segs);
1773
1774 -- This will get the CONCATANATED ASSETKEY
1775 fa_rx_shared_pkg.concat_asset_key (
1776 struct_id => l_g_asset_key_struct
1777 ,ccid => l_asset_key_ccid
1778 ,concat_string => l_concat_asset_key
1779 ,segarray => l_asset_segs);
1780
1781
1782
1783 -- This gets the CONCATENATED CATEGORY NAME
1784 fa_rx_shared_pkg.concat_category (
1785 struct_id => l_g_cat_struct,
1786 ccid => l_asset_cat_id,
1787 concat_string => l_concat_cat,
1788 segarray => l_cat_segs);
1789
1790 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
1791 ,l_asset_cat_id
1792 ,l_dep_backlog
1793 ,l_gen_fund_acct
1794 ,l_oper_exp_acct
1795 ,l_reval_rsv_acct
1796 )
1797 THEN
1798 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
1799 END IF;*/
1800 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
1801 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
1802 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
1803 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
1804 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
1805 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
1806 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
1807 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
1808 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
1809
1810 INSERT INTO igi_iac_asset_rep_itf (
1811 request_id,
1812 company_name,
1813 book_type_code,
1814 period,
1815 fiscal_year_name,
1816 major_category,
1817 cost_center,
1818 depreciation_method,
1819 conc_asset_key,
1820 conc_location,
1821 --deprn_exp_acct,
1822 --deprn_res_acct,
1823 cost_acct,
1824 --iac_reval_resv_acct,
1825 balancing_segment,
1826 --deprn_backlog_acct,
1827 --gen_fund_acct,
1828 --oper_exp_acct,
1829 concat_category,
1830 reval_cost,
1831 minor_category,
1832 oper_exp,
1833 oper_exp_backlog,
1834 oper_exp_net,
1835 functional_currency_code,
1836 created_by,
1837 creation_date,
1838 last_updated_by,
1839 last_update_date,
1840 last_update_login
1841 )
1842 VALUES
1843 (
1844 p_request_id,
1845 l_company_name,
1846 l_book_code,
1847 l_period_name,
1848 l_fiscal_year_name,
1849 l_fa_cat_seg1,
1850 l_gl_code_seg2,
1851 l_depreciation_method,
1852 l_concat_asset_key,
1853 l_concat_loc,
1854 --l_gl_code_seg3,
1855 --l_depreciation_reserve_account,
1856 l_asset_cost_account,
1857 --l_reval_rsv_acct,
1858 l_gl_code_seg1,
1859 --l_dep_backlog,
1860 --l_gen_fund_acct,
1861 --l_oper_exp_acct,
1862 l_concat_cat,
1863 l_reval_cost,
1864 l_fa_cat_seg2,
1865 l_oper_exp,
1866 l_oper_exp_backlog,
1867 l_oper_exp_net,
1868 l_currency_code,
1869 l_user_id,
1870 sysdate,
1871 l_user_id,
1872 sysdate,
1873 p_login_id
1874 );
1875 END LOOP;
1876 CLOSE ret_lines;
1877 RETURN TRUE;
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 -- bug 3421784, start 11
1881 IF ret_lines%ISOPEN THEN
1882 CLOSE ret_lines;
1883 END IF;
1884 -- bug 3421784, start 12
1885 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within run_s_operating_report: '|| sqlerrm);
1886 RETURN FALSE;
1887 END run_s_operating_report;
1888
1889 FUNCTION run_d_operating_report( p_bookType IN VARCHAR2
1890 ,p_period IN VARCHAR2
1891 ,p_categoryId IN VARCHAR2
1892 ,p_from_cc IN VARCHAR2
1893 ,p_to_cc IN VARCHAR2
1894 ,p_from_asset IN VARCHAR2
1895 ,p_to_asset IN VARCHAR2
1896 ,p_request_id IN NUMBER
1897 ,l_user_id IN NUMBER
1898 ,p_login_id IN VARCHAR2)
1899 RETURN BOOLEAN IS
1900
1901 l_select_statement VARCHAR2(15000);
1902 TYPE var_cur IS REF CURSOR;
1903 ret_lines var_cur;
1904
1905 l_cost_center VARCHAR2(25);
1906 l_book_code VARCHAR2(15);
1907 l_reval_cost NUMBER;
1908 l_asset_number VARCHAR2(15);
1909 l_gl_code_seg1 VARCHAR2(25);
1910 l_gl_code_seg2 VARCHAR2(25);
1911 l_gl_code_seg3 VARCHAR2(25);
1912 l_fa_cat_seg1 VARCHAR2(25);
1913 l_fa_cat_seg2 VARCHAR2(25);
1914 l_oper_exp NUMBER;
1915 l_oper_exp_backlog NUMBER;
1916 l_oper_exp_net NUMBER;
1917 l_asset_cat_id NUMBER(15);
1918 l_asset_tag fa_additions.tag_number%TYPE;
1919 l_parent_id fa_additions.parent_asset_id%TYPE;
1920 l_parent_no VARCHAR2(15);
1921 l_serial_number fa_additions.serial_number%TYPE;
1922 l_life_in_months fa_Books.life_in_months%TYPE;
1923 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
1924 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
1925 l_depreciation_method fa_Books.deprn_method_code%TYPE;
1926 l_location_id fa_distribution_history.location_id%TYPE;
1927 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
1928 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
1929 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
1930 l_dep_backlog NUMBER;
1931 l_gen_fund_acct NUMBER;
1932 l_oper_exp_acct NUMBER;
1933 l_reval_rsv_acct NUMBER;
1934 l_concat_loc VARCHAR2(240);
1935 l_concat_asset_key VARCHAR2(240);
1936 l_concat_cat VARCHAR2(600);
1937 l_loc_segs fa_rx_shared_pkg.Seg_Array;
1938 l_asset_segs fa_rx_shared_pkg.Seg_Array;
1939 l_cat_segs fa_rx_shared_pkg.Seg_Array;
1940 l_stl_rate NUMBER;
1941 l_CFDescription VARCHAR2(40);
1942 l_ADDescription fa_additions.description%type;
1943 l_from_asset VARCHAR2(100);
1944 l_to_asset VARCHAR2(100);
1945 l_from_cc VARCHAR2(100);
1946 l_to_cc VARCHAR2(100);
1947 l_path VARCHAR2(150);
1948
1949 BEGIN
1950 l_path := g_path||'run_d_operating_report';
1951
1952 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** START run_d_operating_report ** ');
1953
1954 l_select_statement := 'SELECT ' ||
1955 balancing_seg_no || ', ' ||
1956 cost_ctr_seg_no || ', ' ||
1957 account_seg_no || ', ' ||
1958 major_cat_seg_no || ', ' ||
1959 minor_cat_seg_no || ', ' ||
1960 'bk.book_type_Code book_type_code,
1961 ah.category_id category_id,
1962 cf.description category_description,
1963 ad.asset_number asset_number,
1964 ad.description asset_description,
1965 ad.tag_number asset_tag,
1966 ad.parent_asset_id parent_id,
1967 ad.serial_number serial_number,
1968 ad.asset_key_ccid asset_key_ccid,
1969 bk.life_in_months life_in_months,
1970 bk.date_placed_in_service date_placed_in_service,
1971 bk.deprn_method_code depreciation_method,
1972 dh.location_id location_id,
1973 cb.deprn_reserve_acct depreciation_reserve_account,
1974 cb.asset_cost_acct asset_cost_account,
1975 sum(nvl(dd.cost,0)) Reval_Cost,
1976 0 Oper_Acct_Cost,
1977 0 Oper_Acct_Backlog,
1978 0 Oper_Acct_Net
1979 FROM fa_additions ad,
1980 fa_Books bk,
1981 fa_distribution_history dh,
1982 fa_deprn_Detail dd,
1983 gl_code_combinations cc,
1984 fa_categories cf,
1985 fa_category_books cb,
1986 fa_book_controls fb,
1987 fa_deprn_periods fdp,
1988 fa_asset_history ah
1989 WHERE ad.asset_id = bk.asset_id
1990 AND ah.asset_id = bk.asset_id
1991 AND cf.category_id=ah.category_id
1992 AND cb.category_id = ah.category_id
1993 AND cf.category_id = ' || p_categoryId || '
1994 AND bk.book_type_code = :v_bookType
1995 AND fdp.book_type_code = bk.book_type_code
1996 AND fdp.period_counter = :v_period
1997 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
1998 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
1999 FROM fa_books ifb
2000 WHERE ifb.book_type_code = bk.book_type_code
2001 AND ifb.asset_id = bk.asset_id
2002 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
2003 AND dd.asset_id = bk.asset_id
2004 AND dd.book_type_code = bk.book_type_code
2005 AND cb.book_type_code = bk.book_type_code
2006 AND dh.distribution_id = dd.distribution_id
2007 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
2008 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
2009 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
2010 AND dh.code_combination_id = cc.code_combination_id
2011 AND fb.book_type_code = bk.book_type_code
2012 AND dd.period_counter =
2013 (SELECT max(period_counter)
2014 FROM fa_deprn_summary
2015 WHERE asset_id = bk.asset_id
2016 AND book_type_code = bk.book_type_code
2017 AND period_counter <= fdp.period_counter )
2018 AND bk.asset_id not in
2019 (SELECT asset_id
2020 FROM igi_iac_asset_balances
2021 WHERE book_type_code = bk.book_type_code
2022 AND asset_id = bk.asset_id)
2023 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc, ' || cost_ctr_seg_no || ' )
2024 AND nvl( :v_to_cc,' || cost_ctr_seg_no || ')
2025 AND ad.asset_number between nvl( :v_from_asset, ad.asset_number)
2026 AND nvl( :v_to_asset, ad.asset_number)
2027 GROUP BY ad.asset_number ,
2028 ad.description , ' ||
2029 minor_cat_seg_no || ', ' ||
2030 'bk.book_type_Code ,
2031 ah.category_id,
2032 cf.description , ' ||
2033 balancing_seg_no || ', ' ||
2034 cost_ctr_seg_no || ', ' ||
2035 account_seg_no || ', ' ||
2036 major_cat_seg_no || ', ' ||
2037 'ad.tag_number ,
2038 ad.parent_asset_id ,
2039 ad.serial_number ,
2040 ad.asset_key_ccid ,
2041 bk.life_in_months ,
2042 bk.date_placed_in_service ,
2043 bk.deprn_method_code ,
2044 dh.location_id ,
2045 cb.deprn_reserve_acct ,
2046 cb.asset_cost_acct ' ||
2047
2048 ' UNION
2049 SELECT ' ||
2050 balancing_seg_no || ', ' ||
2051 cost_ctr_seg_no || ', ' ||
2052 account_seg_no || ', ' ||
2053 major_cat_seg_no || ', ' ||
2054 minor_cat_seg_no || ', ' ||
2055 'bk.book_type_Code book_type_code,
2056 ah.category_id category_id,
2057 cf.description category_description,
2058 ad.asset_number asset_number,
2059 ad.description asset_description,
2060 ad.tag_number asset_tag,
2061 ad.parent_asset_id parent_id,
2062 ad.serial_number serial_number,
2063 ad.asset_key_ccid asset_key_ccid,
2064 bk.life_in_months life_in_months,
2065 bk.date_placed_in_service date_placed_in_service,
2066 bk.deprn_method_code depreciation_method,
2067 dh.location_id location_id,
2068 cb.deprn_reserve_acct depreciation_reserve_account,
2069 cb.asset_cost_acct asset_cost_account,
2070 sum (nvl( id.adjustment_cost,0) + dd.cost) Reval_Cost,
2071 sum(nvl(id.operating_acct_cost * -1 ,0)) Oper_Acct_Cost,
2072 sum(nvl(id.operating_acct_backlog * -1,0)) Oper_Acct_Backlog,
2073 sum(nvl(id.operating_acct_net * -1 ,0)) Oper_Acct_Net
2074 FROM fa_additions ad ,
2075 fa_Books bk ,
2076 fa_distribution_history dh,
2077 fa_deprn_Detail dd ,
2078 igi_iac_det_balances id ,
2079 gl_code_combinations cc,
2080 fa_categories cf,
2081 fa_category_books cb,
2082 fa_book_controls fb,
2083 fa_deprn_periods fdp,
2084 fa_asset_history ah
2085 WHERE ad.asset_id = bk.asset_id
2086 AND ad.asset_id = id.asset_id
2087 AND ah.asset_id = bk.asset_id
2088 AND cf.category_id=ah.category_id
2089 AND cb.category_id = ah.category_id
2090 AND cf.category_id = ' || p_categoryId || '
2091 AND bk.book_Type_code = :v_bookType1
2092 AND fdp.book_type_code = bk.book_type_code
2093 AND fdp.period_counter = :v_period1
2094 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
2095 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
2096 FROM fa_books ifb
2097 WHERE ifb.book_type_code = bk.book_type_code
2098 AND ifb.asset_id = bk.asset_id
2099 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
2100 AND dh.book_type_Code = bk.book_type_code
2101 AND dh.book_type_code = dd.book_type_code
2102 AND cb.book_type_Code = bk.book_type_code
2103 AND dh.asset_id = dd.asset_id
2104 AND dh.distribution_id = dd.distribution_id
2105 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
2106 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
2107 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
2108 AND fb.book_type_code = bk.book_type_code
2109 AND dd.period_counter = (SELECT MAX(period_counter)
2110 FROM fa_deprn_summary
2111 WHERE asset_id = bk.asset_id
2112 AND book_type_code = bk.book_type_code
2113 AND period_counter <= fdp.period_counter )
2114 AND dh.distribution_id = id.distribution_id
2115 AND dh.code_Combination_id = cc.code_combination_id
2116 AND id.adjustment_id =
2117 ( SELECT max(adjustment_id)
2118 FROM igi_iac_transaction_headers it
2119 WHERE it.asset_id = bk.asset_id
2120 AND it.book_type_code = bk.book_type_Code
2121 AND period_counter <= fdp.period_counter
2122 AND it.adjustment_status not in ( ''PREVIEW'', ''OBSOLETE''))
2123 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1, ' || cost_ctr_seg_no || ' )
2124 AND nvl( :v_to_cc1,' || cost_ctr_seg_no || ')
2125 AND ad.asset_number between nvl( :v_from_asset1, ad.asset_number)
2126 AND nvl( :v_to_asset1, ad.asset_number)
2127 GROUP BY ad.asset_number ,
2128 ad.description , ' ||
2129 minor_cat_seg_no || ', ' ||
2130 'bk.book_type_Code ,
2131 ah.category_id,
2132 cf.description ,
2133 ad.tag_number ,
2134 ad.parent_asset_id ,
2135 ad.serial_number ,
2136 ad.asset_key_ccid ,
2137 bk.life_in_months ,
2138 bk.date_placed_in_service ,
2139 bk.deprn_method_code ,
2140 dh.location_id ,
2141 cb.deprn_reserve_acct ,
2142 cb.asset_cost_acct, ' ||
2143 balancing_seg_no || ', ' ||
2144 cost_ctr_seg_no || ', ' ||
2145 account_seg_no || ', ' ||
2146 major_cat_seg_no;
2147
2148 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after select ');
2149
2150 -- bug 3421784, start 12
2151 -- commenting out
2152 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
2153 -- bug 3421784, end 12
2154 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
2155
2156 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_categoryId --> ' || p_categoryId);
2157 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_bookType --> ' || p_bookType);
2158 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_period --> ' || p_period);
2159 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_cc --> ' || nvl(p_from_cc,1));
2160 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_cc --> ' || nvl(p_to_cc,1));
2161 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_asset --> ' || nvl(p_from_asset,1));
2162 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_asset --> ' || nvl(p_to_asset,1));
2163
2164 IF p_from_asset = 'NULL' THEN
2165 l_from_asset := NULL;
2166 ELSE
2167 l_from_asset := p_from_asset;
2168 END IF;
2169
2170 IF p_to_asset = 'NULL' THEN
2171 l_to_asset := NULL;
2172 ELSE
2173 l_to_asset := p_to_asset;
2174 END IF;
2175
2176 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_asset --> ' || nvl(l_from_asset,1));
2177 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_asset --> ' || nvl(l_to_asset,1));
2178
2179 IF p_from_cc = 'NULL' THEN
2180 l_from_cc := NULL;
2181 ELSE
2182 l_from_cc := p_from_cc;
2183 END IF;
2184
2185 IF p_to_cc = 'NULL' THEN
2186 l_to_cc := NULL;
2187 ELSE
2188 l_to_cc := p_to_cc;
2189 END IF;
2190
2191 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_cc --> ' || nvl(l_from_cc,1));
2192 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_cc --> ' || nvl(l_to_cc,1));
2193
2194 /* Bug 3490402 */
2195 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
2196 p_period, /* :v_period */
2197 l_from_cc, /* :v_from_cc */
2198 l_to_cc, /* :v_to_cc, */
2199 l_from_asset, /* :v_from_asset */
2200 l_to_asset, /* :v_to_asset */
2201 p_bookType, /* :v_bookType1 */
2202 p_period, /* :v_period1 */
2203 l_from_cc, /* :v_from_cc1 */
2204 l_to_cc, /* :v_to_cc1 */
2205 l_from_asset, /* :v_from_asset1 */
2206 l_to_asset; /* :v_to_asset1 */
2207 LOOP
2208 FETCH ret_lines INTO
2209 l_gl_code_seg1,
2210 l_gl_code_seg2,
2211 l_gl_code_seg3,
2212 l_fa_cat_seg1,
2213 l_fa_cat_seg2,
2214 l_book_code,
2215 l_asset_cat_id,
2216 l_CFDescription,
2217 l_asset_number,
2218 l_ADDescription,
2219 l_asset_tag,
2220 l_parent_id,
2221 l_serial_number,
2222 l_asset_key_ccid,
2223 l_life_in_months,
2224 l_date_placed_in_service,
2225 l_depreciation_method,
2226 l_location_id,
2227 l_depreciation_reserve_account,
2228 l_asset_cost_account,
2229 l_reval_cost,
2230 l_oper_exp,
2231 l_oper_exp_backlog,
2232 l_oper_exp_net;
2233
2234 IF (ret_lines%NOTFOUND) THEN
2235 EXIT;
2236 END IF;
2237
2238 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
2239
2240 /* StatReq - The following if statement has been added to calculate the annual depreciation rate
2241 for straight-line, calculated depreciation methods */
2242
2243 -- Calculate STL_RATE from life_in_months
2244 IF (l_life_in_months > 0)
2245 THEN
2246 l_stl_rate := 12 / l_life_in_months * 100;
2247 ELSE
2248 l_stl_rate := NULL;
2249 END IF;
2250
2251 -- This will get the CONCATANATED LOCATION
2252 fa_rx_shared_pkg.concat_location (
2253 struct_id => l_g_loc_struct
2254 ,ccid => l_location_id
2255 ,concat_string => l_concat_loc
2256 ,segarray => l_loc_segs);
2257
2258 -- This will get the CONCATANATED ASSETKEY
2259 fa_rx_shared_pkg.concat_asset_key (
2260 struct_id => l_g_asset_key_struct
2261 ,ccid => l_asset_key_ccid
2262 ,concat_string => l_concat_asset_key
2263 ,segarray => l_asset_segs);
2264
2265 -- This gets the CONCATENATED CATEGORY NAME
2266 fa_rx_shared_pkg.concat_category (
2267 struct_id => l_g_cat_struct,
2268 ccid => l_asset_cat_id,
2269 concat_string => l_concat_cat,
2270 segarray => l_cat_segs);
2271
2272 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
2273 ,l_asset_cat_id
2274 ,l_dep_backlog
2275 ,l_gen_fund_acct
2276 ,l_oper_exp_acct
2277 ,l_reval_rsv_acct
2278 )
2279 THEN
2280 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
2281 END IF;*/
2282
2283 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
2284 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
2285 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
2286 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
2287 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
2288 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
2289 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
2290 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
2291 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
2292
2293 l_parent_no := NULL;
2294 IF l_parent_id IS NOT NULL THEN
2295 l_parent_no := l_asset_number;
2296 END IF;
2297
2298 INSERT INTO igi_iac_asset_rep_itf (
2299 request_id,
2300 company_name,
2301 book_type_code,
2302 period,
2303 fiscal_year_name,
2304 major_category,
2305 cost_center,
2306 asset_number,
2307 asset_description,
2308 asset_tag,
2309 parent_no,
2310 serial_no,
2311 life_months,
2312 stl_rate,
2313 dpis,
2314 depreciation_method,
2315 conc_asset_key,
2316 conc_location,
2317 --deprn_exp_acct,
2318 --deprn_res_acct,
2319 cost_acct,
2320 --iac_reval_resv_acct,
2321 balancing_segment,
2322 --deprn_backlog_acct,
2323 --gen_fund_acct,
2324 --oper_exp_acct,
2325 concat_category,
2326 reval_cost,
2327 minor_category,
2328 oper_exp,
2329 oper_exp_backlog,
2330 oper_exp_net,
2331 functional_currency_code,
2332 created_by,
2333 creation_date,
2334 last_updated_by,
2335 last_update_date,
2336 last_update_login
2337 )
2338 VALUES
2339 (
2340 p_request_id,
2341 l_company_name,
2342 l_book_code,
2343 l_period_name,
2344 l_fiscal_year_name,
2345 l_fa_cat_seg1,
2346 l_gl_code_seg2,
2347 l_asset_number,
2348 l_ADDescription,
2349 l_asset_tag,
2350 l_parent_no,
2351 l_serial_number,
2352 l_life_in_months,
2353 l_stl_rate,
2354 l_date_placed_in_service,
2355 l_depreciation_method,
2356 l_concat_asset_key,
2357 l_concat_loc,
2358 --l_gl_code_seg3,
2359 --l_depreciation_reserve_account,
2360 l_asset_cost_account,
2361 --l_reval_rsv_acct,
2362 l_gl_code_seg1,
2363 --l_dep_backlog,
2364 --l_gen_fund_acct,
2365 --l_oper_exp_acct,
2366 l_concat_cat,
2367 l_reval_cost,
2368 l_fa_cat_seg2,
2369 l_oper_exp,
2370 l_oper_exp_backlog,
2371 l_oper_exp_net,
2372 l_currency_code,
2373 l_user_id,
2374 sysdate,
2375 l_user_id,
2376 sysdate,
2377 p_login_id
2378 );
2379
2380 END LOOP;
2381 CLOSE ret_lines;
2382 RETURN TRUE;
2383
2384 EXCEPTION
2385 WHEN OTHERS THEN
2386 -- bug 3421784, start 13
2387 IF ret_lines%ISOPEN THEN
2388 CLOSE ret_lines;
2389 END IF;
2390 -- bug 3421784, end 13
2391 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within run_d_operating_report: '|| sqlerrm);
2392 RETURN FALSE;
2393 END run_d_operating_report;
2394
2395 FUNCTION run_s_reval_report ( p_bookType IN VARCHAR2
2396 ,p_period IN VARCHAR2
2397 ,p_categoryId IN VARCHAR2
2398 ,p_request_id IN NUMBER
2399 ,l_user_id IN NUMBER
2400 ,p_login_id IN VARCHAR2)
2401 RETURN BOOLEAN IS
2402
2403 l_select_statement VARCHAR2(15000);
2404 TYPE var_cur IS REF CURSOR;
2405 ret_lines var_cur;
2406
2407 l_cost_center VARCHAR2(25);
2408 l_book_code VARCHAR2(15);
2409 l_reval_cost NUMBER;
2410 l_reval_resv_cost NUMBER;
2411 l_reval_resv_blog NUMBER;
2412 l_reval_resv_gen_fund NUMBER;
2413 l_gen_fund NUMBER;
2414 l_reval_resv_net NUMBER;
2415 l_depr_reserve NUMBER;
2416 l_oper_acct NUMBER;
2417 l_backlog NUMBER;
2418 l_gl_code_seg1 VARCHAR2(25);
2419 l_gl_code_seg2 VARCHAR2(25);
2420 l_gl_code_seg3 VARCHAR2(25);
2421 l_fa_cat_seg1 VARCHAR2(25);
2422 l_fa_cat_seg2 VARCHAR2(25);
2423 l_deprn_period NUMBER;
2424 l_ytd_deprn NUMBER;
2425 l_deprn_backlog NUMBER;
2426 l_deprn_total NUMBER;
2427 l_asset_cat_id NUMBER(15);
2428 l_asset_tag fa_additions.tag_number%TYPE;
2429 l_serial_number fa_additions.serial_number%TYPE;
2430 l_life_in_months fa_Books.life_in_months%TYPE;
2431 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
2432 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
2433 l_depreciation_method fa_Books.deprn_method_code%TYPE;
2434 l_location_id fa_distribution_history.location_id%TYPE;
2435 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
2436 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
2437 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
2438 l_dep_backlog NUMBER;
2439 l_gen_fund_acct NUMBER;
2440 l_oper_exp_acct NUMBER;
2441 l_reval_rsv_acct NUMBER;
2442 l_concat_loc VARCHAR2(200);
2443 l_concat_asset_key VARCHAR2(200);
2444 l_concat_cat VARCHAR2(500);
2445 l_loc_segs fa_rx_shared_pkg.Seg_Array;
2446 l_asset_segs fa_rx_shared_pkg.Seg_Array;
2447 l_cat_segs fa_rx_shared_pkg.Seg_Array;
2448 l_stl_rate NUMBER;
2449 l_CFDescription VARCHAR2(40);
2450 l_path VARCHAR2(150);
2451
2452 BEGIN
2453 l_path := g_path||'run_s_reval_report';
2454 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** START run_s_reval_report ** ');
2455
2456 l_select_statement := 'SELECT ' ||
2457 balancing_seg_no || ', ' ||
2458 cost_ctr_seg_no || ', ' ||
2459 account_seg_no || ', ' ||
2460 major_cat_seg_no || ', ' ||
2461 minor_cat_seg_no || ', ' ||
2462 'bk.book_type_Code book_type_code,
2463 ah.category_id category_id,
2464 cf.description category_description,
2465 ad.asset_key_ccid asset_key_ccid,
2466 bk.deprn_method_code depreciation_method,
2467 dh.location_id location_id,
2468 cb.deprn_reserve_acct depreciation_reserve_account,
2469 cb.asset_cost_acct asset_cost_account,
2470 sum(nvl(dd.cost,0)) Reval_Cost,
2471 0 Reval_Reserve_Cost,
2472 0 Reval_Reserve_Backlog,
2473 0 Reval_Reserve_Gen_Fund,
2474 0 Reval_Reserve_Net
2475 FROM fa_additions ad,
2476 fa_Books bk,
2477 fa_distribution_history dh,
2478 fa_deprn_Detail dd,
2479 gl_code_combinations cc,
2480 fa_categories cf,
2481 fa_category_books cb,
2482 fa_book_controls fb,
2483 fa_deprn_periods fdp,
2484 fa_asset_history ah
2485 WHERE ad.asset_id = bk.asset_id
2486 AND ah.asset_id = bk.asset_id
2487 AND cf.category_id=ah.category_id
2488 AND cb.category_id = ah.category_id
2489 AND cf.category_id = ' || p_categoryId || '
2490 AND bk.book_type_code = :v_bookType
2491 AND fdp.book_type_code = bk.book_type_code
2492 AND fdp.period_counter = :v_period
2493 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
2494 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
2495 FROM fa_books ifb
2496 WHERE ifb.book_type_code = bk.book_type_code
2497 AND ifb.asset_id = bk.asset_id
2498 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
2499 AND dd.asset_id = bk.asset_id
2500 AND dd.book_type_code = bk.book_type_code
2501 AND cb.book_type_code = bk.book_type_code
2502 AND dh.distribution_id = dd.distribution_id
2503 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
2504 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
2505 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
2506 AND dh.code_combination_id = cc.code_combination_id
2507 AND fb.book_type_code = bk.book_type_code
2508 AND dd.period_counter =
2509 (SELECT max(period_counter)
2510 FROM fa_deprn_summary
2511 WHERE asset_id = bk.asset_id
2512 AND book_type_code = bk.book_type_code
2513 AND period_counter <= fdp.period_counter)
2514 AND bk.asset_id not in
2515 (SELECT asset_id
2516 FROM igi_iac_asset_balances
2517 WHERE book_type_code = bk.book_type_code
2518 AND asset_id = bk.asset_id)
2519 GROUP BY ' || balancing_seg_no || ', ' ||
2520 cost_ctr_seg_no || ', ' ||
2521 account_seg_no || ', ' ||
2522 'bk.book_type_code,
2523 ah.category_id,
2524 cf.description,
2525 ad.asset_key_ccid, ' ||
2526 major_cat_seg_no || ', ' ||
2527 minor_cat_seg_no || ', ' ||
2528 'bk.deprn_method_code ,
2529 dh.location_id ,
2530 cb.deprn_reserve_acct ,
2531 cb.asset_cost_acct ' ||
2532
2533 ' UNION
2534 SELECT ' ||
2535 balancing_seg_no || ', ' ||
2536 cost_ctr_seg_no || ', ' ||
2537 account_seg_no || ', ' ||
2538 major_cat_seg_no || ', ' ||
2539 minor_cat_seg_no || ', ' ||
2540 'bk.book_type_Code book_type_code ,
2541 ah.category_id category_id,
2542 cf.description category_description,
2543 ad.asset_key_ccid asset_key_ccid,
2544 bk.deprn_method_code depreciation_method,
2545 dh.location_id location_id,
2546 cb.deprn_reserve_acct depreciation_reserve_account,
2547 cb.asset_cost_acct asset_cost_account,
2548 sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
2549 sum(nvl(id.reval_reserve_cost, 0) ) Reval_Reserve_Cost,
2550 sum(nvl(id.reval_reserve_backlog, 0) ) Reval_Reserve_Backlog,
2551 sum(nvl(id.reval_reserve_gen_fund, 0)) Reval_Reserve_Gen_Fund,
2552 sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve_Net
2553 FROM fa_additions ad ,
2554 fa_Books bk ,
2555 fa_distribution_history dh ,
2556 fa_deprn_Detail dd ,
2557 igi_iac_det_balances id ,
2558 gl_code_combinations cc,
2559 fa_categories cf,
2560 fa_category_books cb,
2561 fa_book_controls fb,
2562 fa_deprn_periods fdp,
2563 fa_asset_history ah
2564 WHERE ad.asset_id = dh.asset_id
2565 AND ah.asset_id = bk.asset_id
2566 AND cf.category_id=ah.category_id
2567 AND cb.category_id = ah.category_id
2568 AND cf.category_id = ' || p_categoryId || '
2569 AND bk.book_Type_code = :v_bookType1
2570 AND fdp.book_type_code = bk.book_type_code
2571 AND fdp.period_counter = :v_period1
2572 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
2573 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
2574 FROM fa_books ifb
2575 WHERE ifb.book_type_code = bk.book_type_code
2576 AND ifb.asset_id = bk.asset_id
2577 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
2578 AND bk.asset_id = ad.asset_id
2579 AND dh.book_type_Code = bk.book_type_code
2580 AND dh.book_type_code = dd.book_type_code
2581 AND cb.book_type_Code = bk.book_type_code
2582 AND dh.asset_id = dd.asset_id
2583 AND dh.distribution_id = dd.distribution_id
2584 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
2585 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
2586 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
2587 AND fb.book_type_code = bk.book_type_code
2588 AND dd.period_counter = (SELECT MAX(period_counter)
2589 FROM fa_deprn_summary
2590 WHERE asset_id =bk.asset_id
2591 AND book_type_code = bk.book_type_code
2592 AND period_counter <= fdp.period_counter )
2593 AND dh.distribution_id = id.distribution_id
2594 AND dh.code_Combination_id = cc.code_combination_id
2595 AND id.adjustment_id = ( SELECT max(adjustment_id)
2596 FROM igi_iac_transaction_headers it
2597 WHERE it.asset_id = bk.asset_id
2598 AND it.book_type_code = bk.book_type_Code
2599 AND it.period_counter <= fdp.period_counter
2600 AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
2601 GROUP BY ' || balancing_seg_no || ', ' ||
2602 cost_ctr_seg_no || ', ' ||
2603 account_seg_no || ', ' ||
2604 'bk.book_type_Code ,
2605 ah.category_id,
2606 cf.description,
2607 ad.asset_key_ccid, ' ||
2608 major_cat_seg_no || ', ' ||
2609 minor_cat_seg_no || ', ' ||
2610 'bk.deprn_method_code ,
2611 dh.location_id ,
2612 cb.deprn_reserve_acct ,
2613 cb.asset_cost_acct';
2614
2615 -- bug 3421784, start 14
2616 -- commenting out
2617 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
2618 -- bug 3421784, end 14
2619
2620 /* Bug 3490402 */
2621 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
2622 p_period, /* :v_period */
2623 p_bookType, /* :v_bookType1 */
2624 p_period; /* :v_period1 */
2625
2626 LOOP
2627 FETCH ret_lines INTO
2628 l_gl_code_seg1,
2629 l_gl_code_seg2,
2630 l_gl_code_seg3,
2631 l_fa_cat_seg1,
2632 l_fa_cat_seg2,
2633 l_book_code,
2634 l_asset_cat_id,
2635 l_CFDescription,
2636 l_asset_key_ccid,
2637 l_depreciation_method,
2638 l_location_id,
2639 l_depreciation_reserve_account,
2640 l_asset_cost_account,
2641 l_reval_cost,
2642 l_reval_resv_cost,
2643 l_reval_resv_blog,
2644 l_reval_resv_gen_fund,
2645 l_reval_resv_net;
2646
2647 IF (ret_lines%NOTFOUND) THEN
2648 EXIT;
2649 END IF;
2650
2651 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
2652
2653 -- This will get the CONCATANATED LOCATION
2654 fa_rx_shared_pkg.concat_location (
2655 struct_id => l_g_loc_struct
2656 ,ccid => l_location_id
2657 ,concat_string => l_concat_loc
2658 ,segarray => l_loc_segs);
2659
2660 -- This will get the CONCATANATED ASSETKEY
2661 fa_rx_shared_pkg.concat_asset_key (
2662 struct_id => l_g_asset_key_struct
2663 ,ccid => l_asset_key_ccid
2664 ,concat_string => l_concat_asset_key
2665 ,segarray => l_asset_segs);
2666
2667
2668
2669 -- This gets the CONCATENATED CATEGORY NAME
2670 fa_rx_shared_pkg.concat_category (
2671 struct_id => l_g_cat_struct,
2672 ccid => l_asset_cat_id,
2673 concat_string => l_concat_cat,
2674 segarray => l_cat_segs);
2675
2676 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
2677 ,l_asset_cat_id
2678 ,l_dep_backlog
2679 ,l_gen_fund_acct
2680 ,l_oper_exp_acct
2681 ,l_reval_rsv_acct
2682 )
2683 THEN
2684 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
2685 END IF;*/
2686 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
2687 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
2688 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
2689 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
2690 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
2691 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
2692 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
2693 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
2694 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
2695
2696 INSERT INTO igi_iac_asset_rep_itf (
2697 request_id,
2698 company_name,
2699 book_type_code,
2700 period,
2701 fiscal_year_name,
2702 major_category,
2703 cost_center,
2704 depreciation_method,
2705 conc_asset_key,
2706 conc_location,
2707 --deprn_exp_acct,
2708 --deprn_res_acct,
2709 cost_acct,
2710 --iac_reval_resv_acct,
2711 balancing_segment,
2712 --deprn_backlog_acct,
2713 --gen_fund_acct,
2714 --oper_exp_acct,
2715 concat_category,
2716 reval_cost,
2717 minor_category,
2718 reval_resv_cost,
2719 reval_resv_blog,
2720 reval_resv_gen_fund,
2721 reval_resv_net,
2722 functional_currency_code,
2723 created_by,
2724 creation_date,
2725 last_updated_by,
2726 last_update_date,
2727 last_update_login
2728 )
2729 VALUES
2730 (
2731 p_request_id,
2732 l_company_name,
2733 l_book_code,
2734 l_period_name,
2735 l_fiscal_year_name,
2736 l_fa_cat_seg1,
2737 l_gl_code_seg2,
2738 l_depreciation_method,
2739 l_concat_asset_key,
2740 l_concat_loc,
2741 --l_gl_code_seg3,
2742 --l_depreciation_reserve_account,
2743 l_asset_cost_account,
2744 --l_reval_rsv_acct,
2745 l_gl_code_seg1,
2746 --l_dep_backlog,
2747 --l_gen_fund_acct,
2748 --l_oper_exp_acct,
2749 l_concat_cat,
2750 l_reval_cost,
2751 l_fa_cat_seg2,
2752 l_reval_resv_cost,
2753 l_reval_resv_blog,
2754 l_reval_resv_gen_fund,
2755 l_reval_resv_net,
2756 l_currency_code,
2757 l_user_id,
2758 sysdate,
2759 l_user_id,
2760 sysdate,
2761 p_login_id
2762 );
2763
2764 END LOOP;
2765 CLOSE ret_lines;
2766 RETURN TRUE;
2767
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 -- bug 3421784, start 15
2771 IF ret_lines%ISOPEN THEN
2772 CLOSE ret_lines;
2773 END IF;
2774 -- bug 3421784, end 15
2775 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within run_s_reval_report : '|| sqlerrm);
2776 RETURN FALSE;
2777 END run_s_reval_report;
2778
2779 FUNCTION run_d_reval_report ( p_bookType IN VARCHAR2
2780 ,p_period IN VARCHAR2
2781 ,p_categoryId IN VARCHAR2
2782 ,p_from_cc IN VARCHAR2
2783 ,p_to_cc IN VARCHAR2
2784 ,p_from_asset IN VARCHAR2
2785 ,p_to_asset IN VARCHAR2
2786 ,p_request_id IN NUMBER
2787 ,l_user_id IN NUMBER
2788 ,p_login_id IN VARCHAR2)
2789 RETURN BOOLEAN IS
2790
2791 l_select_statement VARCHAR2(15000);
2792 TYPE var_cur IS REF CURSOR;
2793 ret_lines var_cur;
2794
2795 l_cost_center VARCHAR2(25);
2796 l_book_code VARCHAR2(15);
2797 l_reval_cost NUMBER;
2798 l_reval_resv_cost NUMBER;
2799 l_reval_resv_blog NUMBER;
2800 l_reval_resv_gen_fund NUMBER;
2801 l_gen_fund NUMBER;
2802 l_reval_resv_net NUMBER;
2803 l_depr_reserve NUMBER;
2804 l_oper_acct NUMBER;
2805 l_backlog NUMBER;
2806 l_asset_number VARCHAR2(15);
2807 l_gl_code_seg1 VARCHAR2(25);
2808 l_gl_code_seg2 VARCHAR2(25);
2809 l_gl_code_seg3 VARCHAR2(25);
2810 l_fa_cat_seg1 VARCHAR2(25);
2811 l_fa_cat_seg2 VARCHAR2(25);
2812 l_deprn_period NUMBER;
2813 l_ytd_deprn NUMBER;
2814 l_deprn_backlog NUMBER;
2815 l_deprn_total NUMBER;
2816 l_asset_cat_id NUMBER(15);
2817 l_asset_tag fa_additions.tag_number%TYPE;
2818 l_parent_id fa_additions.parent_asset_id%TYPE;
2819 l_parent_no VARCHAR2(15);
2820 l_serial_number fa_additions.serial_number%TYPE;
2821 l_life_in_months fa_Books.life_in_months%TYPE;
2822 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
2823 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
2824 l_depreciation_method fa_Books.deprn_method_code%TYPE;
2825 l_location_id fa_distribution_history.location_id%TYPE;
2826 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
2827 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
2828 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
2829 l_dep_backlog NUMBER;
2830 l_gen_fund_acct NUMBER;
2831 l_oper_exp_acct NUMBER;
2832 l_reval_rsv_acct NUMBER;
2833 l_concat_loc VARCHAR2(240);
2834 l_concat_asset_key VARCHAR2(240);
2835 l_concat_cat VARCHAR2(600);
2836 l_loc_segs fa_rx_shared_pkg.Seg_Array;
2837 l_asset_segs fa_rx_shared_pkg.Seg_Array;
2838 l_cat_segs fa_rx_shared_pkg.Seg_Array;
2839 l_stl_rate NUMBER;
2840 l_CFDescription VARCHAR2(40);
2841 l_ADDescription fa_additions.description%type;
2842 l_from_asset VARCHAR2(100);
2843 l_to_asset VARCHAR2(100);
2844 l_from_cc VARCHAR2(100);
2845 l_to_cc VARCHAR2(100);
2846 l_path VARCHAR2(150);
2847
2848 BEGIN
2849 l_path := g_path||'run_d_reval_report';
2850 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** START run_d_reval_report ** ');
2851
2852 l_select_statement := 'SELECT ' ||
2853 balancing_seg_no || ', ' ||
2854 cost_ctr_seg_no || ', ' ||
2855 account_seg_no || ', ' ||
2856 major_cat_seg_no || ', ' ||
2857 minor_cat_seg_no || ', ' ||
2858 'bk.book_type_Code book_type_code,
2859 ah.category_id category_id,
2860 cf.description category_description,
2861 ad.asset_number asset_number,
2862 ad.description asset_description,
2863 ad.tag_number asset_tag,
2864 ad.parent_asset_id parent_id,
2865 ad.serial_number serial_number,
2866 ad.asset_key_ccid asset_key_ccid,
2867 bk.life_in_months life_in_months,
2868 bk.date_placed_in_service date_placed_in_service,
2869 bk.deprn_method_code depreciation_method,
2870 dh.location_id location_id,
2871 cb.deprn_reserve_acct depreciation_reserve_account,
2872 cb.asset_cost_acct asset_cost_account,
2873 sum(nvl(dd.cost,0)) Reval_Cost,
2874 0 Reval_Reserve_Cost,
2875 0 Reval_Reserve_Backlog,
2876 0 Reval_Reserve_Gen_Fund,
2877 0 Reval_Reserve_Net
2878 FROM fa_additions ad,
2879 fa_Books bk,
2880 fa_distribution_history dh,
2881 fa_deprn_Detail dd,
2882 gl_code_combinations cc,
2883 fa_categories cf,
2884 fa_category_books cb,
2885 fa_book_controls fb,
2886 fa_deprn_periods fdp,
2887 fa_asset_history ah
2888 WHERE ad.asset_id = bk.asset_id
2889 AND ah.asset_id = bk.asset_id
2890 AND cf.category_id=ah.category_id
2891 AND cb.category_id = ah.category_id
2892 AND cf.category_id = ' || p_categoryId || '
2893 AND bk.book_type_code = :v_bookType
2894 AND fdp.book_type_code = bk.book_type_code
2895 AND fdp.period_counter = :v_period
2896 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
2897 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
2898 FROM fa_books ifb
2899 WHERE ifb.book_type_code = bk.book_type_code
2900 AND ifb.asset_id = bk.asset_id
2901 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
2902 AND dd.asset_id = bk.asset_id
2903 AND dd.book_type_code = bk.book_type_code
2904 AND cb.book_type_code = bk.book_type_code
2905 AND dh.distribution_id = dd.distribution_id
2906 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
2907 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
2908 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
2909 AND dh.code_combination_id = cc.code_combination_id
2910 AND fb.book_type_code = bk.book_type_code
2911 AND dd.period_counter =
2912 (SELECT max(period_counter)
2913 FROM fa_deprn_summary
2914 WHERE asset_id = bk.asset_id
2915 AND book_type_code = bk.book_type_code
2916 AND period_counter <= fdp.period_counter)
2917 AND bk.asset_id not in
2918 (SELECT asset_id
2919 FROM igi_iac_asset_balances
2920 WHERE book_type_code = bk.book_type_code
2921 AND asset_id = bk.asset_id)
2922 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc, ' || cost_ctr_seg_no || ' )
2923 AND nvl( :v_to_cc,' || cost_ctr_seg_no || ')
2924 AND ad.asset_number between nvl( :v_from_asset, ad.asset_number)
2925 AND nvl( :v_to_asset, ad.asset_number)
2926 GROUP BY ad.asset_number,
2927 ad.description , ' ||
2928 balancing_seg_no || ', ' ||
2929 cost_ctr_seg_no || ', ' ||
2930 account_seg_no || ', ' ||
2931 'bk.book_type_code,
2932 ah.category_id,
2933 cf.description, ' ||
2934 major_cat_seg_no || ', ' ||
2935 minor_cat_seg_no || ', ' ||
2936 'ad.tag_number ,
2937 ad.parent_asset_id ,
2938 ad.serial_number ,
2939 ad.asset_key_ccid ,
2940 bk.life_in_months ,
2941 bk.date_placed_in_service ,
2942 bk.deprn_method_code ,
2943 dh.location_id ,
2944 cb.deprn_reserve_acct ,
2945 cb.asset_cost_acct ' ||
2946
2947 ' UNION
2948 SELECT ' ||
2949 balancing_seg_no || ', ' ||
2950 cost_ctr_seg_no || ', ' ||
2951 account_seg_no || ', ' ||
2952 major_cat_seg_no || ', ' ||
2953 minor_cat_seg_no || ', ' ||
2954 'bk.book_type_Code book_type_code ,
2955 ah.category_id category_id,
2956 cf.description category_description,
2957 ad.asset_number asset_number ,
2958 ad.description asset_description,
2959 ad.tag_number asset_tag,
2960 ad.parent_asset_id parent_id,
2961 ad.serial_number serial_number,
2962 ad.asset_key_ccid asset_key_ccid,
2963 bk.life_in_months life_in_months,
2964 bk.date_placed_in_service date_placed_in_service,
2965 bk.deprn_method_code depreciation_method,
2966 dh.location_id location_id,
2967 cb.deprn_reserve_acct depreciation_reserve_account,
2968 cb.asset_cost_acct asset_cost_account,
2969 sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
2970 sum(nvl(id.reval_reserve_cost, 0) ) Reval_Reserve_Cost,
2971 sum(nvl(id.reval_reserve_backlog, 0) ) Reval_Reserve_Backlog,
2972 sum(nvl(id.reval_reserve_gen_fund, 0)) Reval_Reserve_Gen_Fund,
2973 sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve_Net
2974 FROM fa_additions ad ,
2975 fa_Books bk ,
2976 fa_distribution_history dh ,
2977 fa_deprn_Detail dd ,
2978 igi_iac_det_balances id ,
2979 gl_code_combinations cc,
2980 fa_categories cf,
2981 fa_category_books cb,
2982 fa_book_controls fb,
2983 fa_deprn_periods fdp,
2984 fa_asset_history ah
2985 WHERE ad.asset_id = dh.asset_id
2986 AND ah.asset_id = bk.asset_id
2987 AND cf.category_id=ah.category_id
2988 AND cb.category_id = ah.category_id
2989 AND cf.category_id = ' || p_categoryId || '
2990 AND bk.book_Type_code = :v_bookType1
2991 AND fdp.book_type_code = bk.book_type_code
2992 AND fdp.period_counter = :v_period1
2993 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
2994 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
2995 FROM fa_books ifb
2996 WHERE ifb.book_type_code = bk.book_type_code
2997 AND ifb.asset_id = bk.asset_id
2998 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
2999 AND bk.asset_id = ad.asset_id
3000 AND dh.book_type_Code = bk.book_type_code
3001 AND dh.book_type_code = dd.book_type_code
3002 AND cb.book_type_Code = bk.book_type_code
3003 AND dh.asset_id = dd.asset_id
3004 AND dh.distribution_id = dd.distribution_id
3005 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
3006 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
3007 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
3008 AND fb.book_type_code = bk.book_type_code
3009 AND dd.period_counter = (SELECT MAX(period_counter)
3010 FROM fa_deprn_summary
3011 WHERE asset_id =bk.asset_id
3012 AND book_type_code = bk.book_type_code
3013 AND period_counter <= fdp.period_counter )
3014 AND dh.distribution_id = id.distribution_id
3015 AND dh.code_Combination_id = cc.code_combination_id
3016 AND id.adjustment_id = ( SELECT max(adjustment_id)
3017 FROM igi_iac_transaction_headers it
3018 WHERE it.asset_id = bk.asset_id
3019 AND it.book_type_code = bk.book_type_Code
3020 AND it.period_counter <= fdp.period_counter
3021 AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
3022 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1, ' || cost_ctr_seg_no || ' )
3023 AND nvl( :v_to_cc1,' || cost_ctr_seg_no || ')
3024 AND ad.asset_number between nvl( :v_from_asset1, ad.asset_number)
3025 AND nvl( :v_to_asset1, ad.asset_number)
3026 GROUP BY ad.asset_number ,
3027 ad.description , ' ||
3028 balancing_seg_no || ', ' ||
3029 cost_ctr_seg_no || ', ' ||
3030 account_seg_no || ', ' ||
3031 'bk.book_type_Code ,
3032 ah.category_id,
3033 cf.description, ' ||
3034 major_cat_seg_no || ', ' ||
3035 minor_cat_seg_no || ', ' ||
3036 'ad.tag_number ,
3037 ad.parent_asset_id ,
3038 ad.serial_number ,
3039 ad.asset_key_ccid ,
3040 bk.life_in_months ,
3041 bk.date_placed_in_service ,
3042 bk.deprn_method_code ,
3043 dh.location_id ,
3044 cb.deprn_reserve_acct ,
3045 cb.asset_cost_acct' ;
3046
3047 -- bug 3421784, start 16
3048 -- commenting out
3049 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
3050 -- bug 3421784, end 16
3051
3052 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
3053 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_categoryId --> ' || p_categoryId);
3054 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_bookType --> ' || p_bookType);
3055 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_period --> ' || p_period);
3056 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_cc --> ' || nvl(p_from_cc,1));
3057 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_cc --> ' || nvl(p_to_cc,1));
3058 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_asset --> ' || nvl(p_from_asset,1));
3059 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_asset --> ' || nvl(p_to_asset,1));
3060
3061 IF p_from_asset = 'NULL' THEN
3062 l_from_asset := NULL;
3063 ELSE
3064 l_from_asset := p_from_asset;
3065 END IF;
3066
3067 IF p_to_asset = 'NULL' THEN
3068 l_to_asset := NULL;
3069 ELSE
3070 l_to_asset := p_to_asset;
3071 END IF;
3072
3073 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_asset --> ' || nvl(l_from_asset,1));
3074 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_asset --> ' || nvl(l_to_asset,1));
3075
3076 IF p_from_cc = 'NULL' THEN
3077 l_from_cc := NULL;
3078 ELSE
3079 l_from_cc := p_from_cc;
3080 END IF;
3081
3082 IF p_to_cc = 'NULL' THEN
3083 l_to_cc := NULL;
3084 ELSE
3085 l_to_cc := p_to_cc;
3086 END IF;
3087
3088 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_cc --> ' || nvl(l_from_cc,1));
3089 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_cc --> ' || nvl(l_to_cc,1));
3090
3091 /* Bug 3490402 */
3092 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
3093 p_period, /* :v_period */
3094 l_from_cc, /* :v_from_cc */
3095 l_to_cc, /* :v_to_cc, */
3096 l_from_asset, /* :v_from_asset */
3097 l_to_asset, /* :v_to_asset */
3098 p_bookType, /* :v_bookType1 */
3099 p_period, /* :v_period1 */
3100 l_from_cc, /* :v_from_cc1 */
3101 l_to_cc, /* :v_to_cc1 */
3102 l_from_asset, /* :v_from_asset1 */
3103 l_to_asset; /* :v_to_asset1 */
3104 LOOP
3105 FETCH ret_lines INTO
3106 l_gl_code_seg1,
3107 l_gl_code_seg2,
3108 l_gl_code_seg3,
3109 l_fa_cat_seg1,
3110 l_fa_cat_seg2,
3111 l_book_code,
3112 l_asset_cat_id,
3113 l_CFDescription,
3114 l_asset_number,
3115 l_ADDescription,
3116 l_asset_tag,
3117 l_parent_id,
3118 l_serial_number,
3119 l_asset_key_ccid,
3120 l_life_in_months,
3121 l_date_placed_in_service,
3122 l_depreciation_method,
3123 l_location_id,
3124 l_depreciation_reserve_account,
3125 l_asset_cost_account,
3126 l_reval_cost,
3127 l_reval_resv_cost,
3128 l_reval_resv_blog,
3129 l_reval_resv_gen_fund,
3130 l_reval_resv_net;
3131
3132 IF (ret_lines%NOTFOUND) THEN
3133 EXIT;
3134 END IF;
3135
3136 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
3137 /* StatReq - The following if statement has been added to calculate the annual depreciation rate
3138 for straight-line, calculated depreciation methods */
3139
3140 -- Calculate STL_RATE from life_in_months
3141 IF (l_life_in_months > 0)
3142 THEN
3143 l_stl_rate := 12 / l_life_in_months * 100;
3144 ELSE
3145 l_stl_rate := NULL;
3146 END IF;
3147
3148 -- This will get the CONCATANATED LOCATION
3149 fa_rx_shared_pkg.concat_location (
3150 struct_id => l_g_loc_struct
3151 ,ccid => l_location_id
3152 ,concat_string => l_concat_loc
3153 ,segarray => l_loc_segs);
3154
3155 -- This will get the CONCATANATED ASSETKEY
3156 fa_rx_shared_pkg.concat_asset_key (
3157 struct_id => l_g_asset_key_struct
3158 ,ccid => l_asset_key_ccid
3159 ,concat_string => l_concat_asset_key
3160 ,segarray => l_asset_segs);
3161
3162 -- This gets the CONCATENATED CATEGORY NAME
3163 fa_rx_shared_pkg.concat_category (
3164 struct_id => l_g_cat_struct,
3165 ccid => l_asset_cat_id,
3166 concat_string => l_concat_cat,
3167 segarray => l_cat_segs);
3168
3169 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
3170 ,l_asset_cat_id
3171 ,l_dep_backlog
3172 ,l_gen_fund_acct
3173 ,l_oper_exp_acct
3174 ,l_reval_rsv_acct
3175 )
3176 THEN
3177 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
3178 END IF;*/
3179 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
3180 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
3181 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
3182 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
3183 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
3184 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
3185 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
3186 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
3187 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
3188
3189 l_parent_no := NULL;
3190
3191 IF l_parent_id IS NOT NULL THEN
3192 l_parent_no := l_asset_number;
3193 END IF;
3194
3195 INSERT INTO igi_iac_asset_rep_itf (
3196 request_id,
3197 company_name,
3198 book_type_code,
3199 period,
3200 fiscal_year_name,
3201 major_category,
3202 cost_center,
3203 asset_number,
3204 asset_description,
3205 asset_tag,
3206 parent_no,
3207 serial_no,
3208 life_months,
3209 stl_rate,
3210 dpis,
3211 depreciation_method,
3212 conc_asset_key,
3213 conc_location,
3214 --deprn_exp_acct,
3215 --deprn_res_acct,
3216 cost_acct,
3217 --iac_reval_resv_acct,
3218 balancing_segment,
3219 --deprn_backlog_acct,
3220 --gen_fund_acct,
3221 --oper_exp_acct,
3222 concat_category,
3223 reval_cost,
3224 minor_category,
3225 reval_resv_cost,
3226 reval_resv_blog,
3227 reval_resv_gen_fund,
3228 reval_resv_net,
3229 functional_currency_code,
3230 created_by,
3231 creation_date,
3232 last_updated_by,
3233 last_update_date,
3234 last_update_login
3235 )
3236 VALUES
3237 (
3238 p_request_id,
3239 l_company_name,
3240 l_book_code,
3241 l_period_name,
3242 l_fiscal_year_name,
3243 l_fa_cat_seg1,
3244 l_gl_code_seg2,
3245 l_asset_number,
3246 l_ADDescription,
3247 l_asset_tag,
3248 l_parent_no,
3249 l_serial_number,
3250 l_life_in_months,
3251 l_stl_rate,
3252 l_date_placed_in_service,
3253 l_depreciation_method,
3254 l_concat_asset_key,
3255 l_concat_loc,
3256 --l_gl_code_seg3,
3257 --l_depreciation_reserve_account,
3258 l_asset_cost_account,
3259 --l_reval_rsv_acct,
3260 l_gl_code_seg1,
3261 --l_dep_backlog,
3262 --l_gen_fund_acct,
3263 --l_oper_exp_acct,
3264 l_concat_cat,
3265 l_reval_cost,
3266 l_fa_cat_seg2,
3267 l_reval_resv_cost,
3268 l_reval_resv_blog,
3269 l_reval_resv_gen_fund,
3270 l_reval_resv_net,
3271 l_currency_code,
3272 l_user_id,
3273 sysdate,
3274 l_user_id,
3275 sysdate,
3276 p_login_id
3277 );
3278
3279 END LOOP;
3280 CLOSE ret_lines;
3281 RETURN TRUE;
3282
3283 EXCEPTION
3284 WHEN OTHERS THEN
3285 -- bug 3421784, start 17
3286 IF ret_lines%ISOPEN THEN
3287 CLOSE ret_lines;
3288 END IF;
3289 -- bug 3421784, end 17
3290 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within run_d_reval_report : '|| sqlerrm);
3291 RETURN FALSE;
3292 END run_d_reval_report;
3293
3294 FUNCTION run_s_summary_report ( p_bookType IN VARCHAR2
3295 ,p_period IN VARCHAR2
3296 ,p_categoryId IN VARCHAR2
3297 ,p_request_id IN NUMBER
3298 ,l_user_id IN NUMBER
3299 ,p_login_id IN VARCHAR2)
3300 RETURN BOOLEAN IS
3301
3302 l_select_statement VARCHAR2(15000);
3303 TYPE var_cur IS REF CURSOR;
3304 ret_lines var_cur;
3305
3306 l_cost_center VARCHAR2(25);
3307 l_book_code VARCHAR2(15);
3308 l_reval_cost NUMBER;
3309 l_reval_reserve NUMBER;
3310 l_general_fund NUMBER;
3311 l_oper_acct NUMBER;
3312 l_deprn_resv NUMBER;
3313 l_backlog NUMBER;
3314 l_gl_code_seg1 VARCHAR2(25);
3315 l_gl_code_seg2 VARCHAR2(25);
3316 l_gl_code_seg3 VARCHAR2(25);
3317 l_fa_cat_seg1 VARCHAR2(25);
3318 l_fa_cat_seg2 VARCHAR2(25);
3319 l_deprn_period NUMBER;
3320 l_ytd_deprn NUMBER;
3321 l_deprn_backlog NUMBER;
3322 l_deprn_total NUMBER;
3323 l_asset_cat_id NUMBER(15);
3324 l_asset_tag fa_additions.tag_number%TYPE;
3325 l_serial_number fa_additions.serial_number%TYPE;
3326 l_life_in_months fa_Books.life_in_months%TYPE;
3327 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
3328 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
3329 l_depreciation_method fa_Books.deprn_method_code%TYPE;
3330 l_location_id fa_distribution_history.location_id%TYPE;
3331 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
3332 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
3333 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
3334 l_dep_backlog NUMBER;
3335 l_gen_fund_acct NUMBER;
3336 l_oper_exp_acct NUMBER;
3337 l_reval_rsv_acct NUMBER;
3338 l_concat_loc VARCHAR2(200);
3339 l_concat_asset_key VARCHAR2(200);
3340 l_concat_cat VARCHAR2(500);
3341 l_loc_segs fa_rx_shared_pkg.Seg_Array;
3342 l_asset_segs fa_rx_shared_pkg.Seg_Array;
3343 l_cat_segs fa_rx_shared_pkg.Seg_Array;
3344 l_stl_rate NUMBER;
3345 l_CFDescription VARCHAR2(40);
3346 l_path VARCHAR2(150);
3347
3348 BEGIN
3349 l_path := g_path||'run_s_summary_report';
3350 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** START run_s_summary_report ** ');
3351
3352 l_select_statement := 'SELECT ' ||
3353 balancing_seg_no || ', ' ||
3354 cost_ctr_seg_no || ', ' ||
3355 account_seg_no || ', ' ||
3356 major_cat_seg_no || ', ' ||
3357 minor_cat_seg_no || ', ' ||
3358 'bk.book_type_Code book_type_code,
3359 ah.category_id category_id,
3360 cf.description category_description,
3361 ad.asset_key_ccid asset_key_ccid,
3362 bk.deprn_method_code depreciation_method,
3363 dh.location_id location_id,
3364 cb.deprn_reserve_acct depreciation_reserve_account,
3365 cb.asset_cost_acct asset_cost_account,
3366 sum(nvl(dd.cost,0)) Reval_Cost,
3367 0 Reval_Reserve,
3368 0 Gen_Fund,
3369 0 Operating_Acct,
3370 sum(nvl(dd.deprn_reserve,0)) Acct_Deprn,
3371 0 Backlog
3372 FROM fa_additions ad,
3373 fa_Books bk,
3374 fa_distribution_history dh,
3375 fa_deprn_Detail dd,
3376 gl_code_combinations cc,
3377 fa_categories cf,
3378 fa_category_books cb,
3379 fa_book_controls fb,
3380 fa_deprn_periods fdp,
3381 fa_asset_history ah
3382 WHERE ad.asset_id = bk.asset_id
3383 AND ah.asset_id = bk.asset_id
3384 AND cf.category_id=ah.category_id
3385 AND cb.category_id = ah.category_id
3386 AND cf.category_id = ' || p_categoryId || '
3387 AND bk.book_type_code = :v_bookType
3388 AND fdp.book_type_code = bk.book_type_code
3389 AND fdp.period_counter = :v_period
3390 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
3391 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
3392 FROM fa_books ifb
3393 WHERE ifb.book_type_code = bk.book_type_code
3394 AND ifb.asset_id = bk.asset_id
3395 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
3396 AND dd.asset_id = bk.asset_id
3397 AND dd.book_type_code = bk.book_type_code
3398 AND cb.book_type_code = bk.book_type_code
3399 AND dh.distribution_id = dd.distribution_id
3400 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
3401 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
3402 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
3403 AND dh.code_combination_id = cc.code_combination_id
3404 AND fb.book_type_code = bk.book_type_code
3405 AND dd.period_counter =
3406 (SELECT max(period_counter)
3407 FROM fa_deprn_summary
3408 WHERE asset_id = bk.asset_id
3409 AND book_type_code = bk.book_type_code
3410 AND period_counter <= fdp.period_counter)
3411 AND bk.asset_id not in
3412 (SELECT asset_id
3413 FROM igi_iac_asset_balances
3414 WHERE book_type_code = bk.book_type_code
3415 AND asset_id = bk.asset_id)
3416 GROUP BY ' || balancing_seg_no || ', ' ||
3417 cost_ctr_seg_no || ', ' ||
3418 account_seg_no || ', ' ||
3419 'bk.book_type_code,
3420 ah.category_id,
3421 cf.description,
3422 ad.asset_key_ccid, ' ||
3423 major_cat_seg_no || ', ' ||
3424 minor_cat_seg_no || ', ' ||
3425 'bk.deprn_method_code ,
3426 dh.location_id ,
3427 cb.deprn_reserve_acct ,
3428 cb.asset_cost_acct ' ||
3429
3430 ' UNION
3431 SELECT ' ||
3432 balancing_seg_no || ', ' ||
3433 cost_ctr_seg_no || ', ' ||
3434 account_seg_no || ', ' ||
3435 major_cat_seg_no || ', ' ||
3436 minor_cat_seg_no || ', ' ||
3437 'bk.book_type_Code book_type_code ,
3438 ah.category_id category_id,
3439 cf.description category_description,
3440 ad.asset_key_ccid asset_key_ccid,
3441 bk.deprn_method_code depreciation_method,
3442 dh.location_id location_id,
3443 cb.deprn_reserve_acct depreciation_reserve_account,
3444 cb.asset_cost_acct asset_cost_account,
3445 sum(nvl(( (id.adjustment_cost) + dd.cost), 0)) Reval_Cost,
3446 sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve,
3447 sum(nvl(id.reval_reserve_gen_fund, 0)) Gen_Fund,
3448 sum(nvl(id.operating_acct_net * -1, 0)) Operating_Acct,
3449 sum(nvl(id.deprn_reserve + dd.deprn_reserve, 0) ) Acct_Deprn,
3450 sum(nvl( id.deprn_reserve_backlog , 0)) Backlog
3451 FROM fa_additions ad ,
3452 fa_Books bk ,
3453 fa_distribution_history dh,
3454 fa_deprn_Detail dd ,
3455 igi_iac_det_balances id ,
3456 gl_code_combinations cc,
3457 fa_categories cf,
3458 fa_category_books cb,
3459 fa_book_controls fb,
3460 fa_deprn_periods fdp,
3461 fa_asset_history ah
3462 WHERE ad.asset_id = dh.asset_id
3463 AND ah.asset_id = bk.asset_id
3464 AND cf.category_id=ah.category_id
3465 AND cb.category_id = ah.category_id
3466 AND cf.category_id = ' || p_categoryId || '
3467 AND bk.book_Type_code = :v_bookType1
3468 AND fdp.book_type_code = bk.book_type_code
3469 AND fdp.period_counter = :v_period1
3470 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
3471 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
3472 FROM fa_books ifb
3473 WHERE ifb.book_type_code = bk.book_type_code
3474 AND ifb.asset_id = bk.asset_id
3475 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
3476 AND bk.asset_id = ad.asset_id
3477 AND dh.book_type_Code = bk.book_type_code
3478 AND dh.book_type_code = dd.book_type_code
3479 AND cb.book_type_Code = bk.book_type_code
3480 AND dh.asset_id = dd.asset_id
3481 AND dh.distribution_id = dd.distribution_id
3482 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
3483 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
3484 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
3485 AND fb.book_type_code = bk.book_type_code
3486 AND dd.period_counter = (SELECT MAX(period_counter)
3487 FROM fa_deprn_summary
3488 WHERE asset_id =bk.asset_id
3489 AND book_type_code = bk.book_type_code
3490 AND period_counter <= fdp.period_counter )
3491 AND dh.distribution_id = id.distribution_id
3492 AND dh.code_Combination_id = cc.code_combination_id
3493 AND id.adjustment_id = ( SELECT max(adjustment_id)
3494 FROM igi_iac_transaction_headers it
3495 WHERE it.asset_id = bk.asset_id
3496 AND it.book_type_code = bk.book_type_Code
3497 AND it.period_counter <= fdp.period_counter
3498 AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
3499 GROUP BY ' || balancing_seg_no || ', ' ||
3500 cost_ctr_seg_no || ', ' ||
3501 account_seg_no || ', ' ||
3502 'bk.book_type_Code ,
3503 ah.category_id,
3504 cf.description,
3505 ad.asset_key_ccid, ' ||
3506 major_cat_seg_no || ', ' ||
3507 minor_cat_seg_no || ', ' ||
3508 'bk.deprn_method_code ,
3509 dh.location_id ,
3510 cb.deprn_reserve_acct ,
3511 cb.asset_cost_acct';
3512
3513 -- bug 3421784, start 18
3514 -- commenting out
3515 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
3516 -- bug 3421784, end 18
3517
3518 /* Bug 3490402 */
3519 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
3520 p_period, /* :v_period */
3521 p_bookType, /* :v_bookType1 */
3522 p_period; /* :v_period1 */
3523 LOOP
3524 FETCH ret_lines INTO
3525 l_gl_code_seg1,
3526 l_gl_code_seg2,
3527 l_gl_code_seg3,
3528 l_fa_cat_seg1,
3529 l_fa_cat_seg2,
3530 l_book_code,
3531 l_asset_cat_id,
3532 l_CFDescription,
3533 l_asset_key_ccid,
3534 l_depreciation_method,
3535 l_location_id,
3536 l_depreciation_reserve_account,
3537 l_asset_cost_account,
3538 l_reval_cost,
3539 l_reval_reserve,
3540 l_general_fund,
3541 l_oper_acct,
3542 l_deprn_resv,
3543 l_backlog;
3544
3545 IF (ret_lines%NOTFOUND) THEN
3546 EXIT;
3547 END IF;
3548 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
3549
3550 -- This will get the CONCATANATED LOCATION
3551 fa_rx_shared_pkg.concat_location (
3552 struct_id => l_g_loc_struct
3553 ,ccid => l_location_id
3554 ,concat_string => l_concat_loc
3555 ,segarray => l_loc_segs);
3556
3557 -- This will get the CONCATANATED ASSETKEY
3558 fa_rx_shared_pkg.concat_asset_key (
3559 struct_id => l_g_asset_key_struct
3560 ,ccid => l_asset_key_ccid
3561 ,concat_string => l_concat_asset_key
3562 ,segarray => l_asset_segs);
3563
3564
3565
3566 -- This gets the CONCATENATED CATEGORY NAME
3567 fa_rx_shared_pkg.concat_category (
3568 struct_id => l_g_cat_struct,
3569 ccid => l_asset_cat_id,
3570 concat_string => l_concat_cat,
3571 segarray => l_cat_segs);
3572
3573 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
3574 ,l_asset_cat_id
3575 ,l_dep_backlog
3576 ,l_gen_fund_acct
3577 ,l_oper_exp_acct
3578 ,l_reval_rsv_acct
3579 )
3580 THEN
3581 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
3582 END IF;*/
3583 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
3584 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
3585 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
3586 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
3587 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
3588 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
3589 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
3590 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
3591 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
3592
3593 INSERT INTO igi_iac_asset_rep_itf (
3594 request_id,
3595 company_name,
3596 book_type_code,
3597 period,
3598 fiscal_year_name,
3599 major_category,
3600 cost_center,
3601 depreciation_method,
3602 conc_asset_key,
3603 conc_location,
3604 --deprn_exp_acct,
3605 --deprn_res_acct,
3606 cost_acct,
3607 --iac_reval_resv_acct,
3608 balancing_segment,
3609 --deprn_backlog_acct,
3610 --gen_fund_acct,
3611 --oper_exp_acct,
3612 concat_category,
3613 reval_cost,
3614 minor_category,
3615 reval_reserve,
3616 general_fund,
3617 oper_acct,
3618 deprn_resv,
3619 backlog,
3620 functional_currency_code,
3621 created_by,
3622 creation_date,
3623 last_updated_by,
3624 last_update_date,
3625 last_update_login
3626 )
3627 VALUES
3628 (
3629 p_request_id,
3630 l_company_name,
3631 l_book_code,
3632 l_period_name,
3633 l_fiscal_year_name,
3634 l_fa_cat_seg1,
3635 l_gl_code_seg2,
3636 l_depreciation_method,
3637 l_concat_asset_key,
3638 l_concat_loc,
3639 --l_gl_code_seg3,
3640 --l_depreciation_reserve_account,
3641 l_asset_cost_account,
3642 --l_reval_rsv_acct,
3643 l_gl_code_seg1,
3644 --l_dep_backlog,
3645 --l_gen_fund_acct,
3646 --l_oper_exp_acct,
3647 l_concat_cat,
3648 l_reval_cost,
3649 l_fa_cat_seg2,
3650 l_reval_reserve,
3651 l_general_fund,
3652 l_oper_acct,
3653 l_deprn_resv,
3654 l_backlog,
3655 l_currency_code,
3656 l_user_id,
3657 sysdate,
3658 l_user_id,
3659 sysdate,
3660 p_login_id
3661 );
3662
3663 END LOOP;
3664 CLOSE ret_lines;
3665 RETURN TRUE;
3666 EXCEPTION
3667 WHEN OTHERS THEN
3668 -- bug 3421784, start 19
3669 IF ret_lines%ISOPEN THEN
3670 CLOSE ret_lines;
3671 END IF;
3672 -- bug 3421784, end 19
3673 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within run_s_summary_report : '|| sqlerrm);
3674 RETURN FALSE;
3675 END run_s_summary_report;
3676
3677 FUNCTION run_d_summary_report ( p_bookType IN VARCHAR2
3678 ,p_period IN VARCHAR2
3679 ,p_categoryId IN VARCHAR2
3680 ,p_from_cc IN VARCHAR2
3681 ,p_to_cc IN VARCHAR2
3682 ,p_from_asset IN VARCHAR2
3683 ,p_to_asset IN VARCHAR2
3684 ,p_request_id IN NUMBER
3685 ,l_user_id IN NUMBER
3686 ,p_login_id IN VARCHAR2)
3687 RETURN BOOLEAN IS
3688
3689 l_select_statement VARCHAR2(15000);
3690 TYPE var_cur IS REF CURSOR;
3691 ret_lines var_cur;
3692
3693 l_cost_center VARCHAR2(25);
3694 l_book_code VARCHAR2(15);
3695 l_reval_cost NUMBER;
3696 l_reval_reserve NUMBER;
3697 l_general_fund NUMBER;
3698 l_oper_acct NUMBER;
3699 l_deprn_resv NUMBER;
3700 l_backlog NUMBER;
3701 l_asset_number VARCHAR2(15);
3702 l_gl_code_seg1 VARCHAR2(25);
3703 l_gl_code_seg2 VARCHAR2(25);
3704 l_gl_code_seg3 VARCHAR2(25);
3705 l_fa_cat_seg1 VARCHAR2(25);
3706 l_fa_cat_seg2 VARCHAR2(25);
3707 l_deprn_period NUMBER;
3708 l_ytd_deprn NUMBER;
3709 l_deprn_backlog NUMBER;
3710 l_deprn_total NUMBER;
3711 l_asset_cat_id NUMBER(15);
3712 l_asset_tag fa_additions.tag_number%TYPE;
3713 l_parent_id fa_additions.parent_asset_id%TYPE;
3714 l_parent_no VARCHAR2(15);
3715 l_serial_number fa_additions.serial_number%TYPE;
3716 l_life_in_months fa_Books.life_in_months%TYPE;
3717 l_date_placed_in_service fa_Books.date_placed_in_service%TYPE;
3718 l_depreciation_reserve_account fa_category_books.deprn_reserve_acct%TYPE;
3719 l_depreciation_method fa_Books.deprn_method_code%TYPE;
3720 l_location_id fa_distribution_history.location_id%TYPE;
3721 l_asset_key_ccid fa_asset_keywords.code_combination_id%TYPE;
3722 l_asset_cost_account fa_category_books.asset_cost_acct%TYPE;
3723 l_deprn_res_acct fa_category_books.deprn_reserve_acct%TYPE;
3724 l_dep_backlog NUMBER;
3725 l_gen_fund_acct NUMBER;
3726 l_oper_exp_acct NUMBER;
3727 l_reval_rsv_acct NUMBER;
3728 l_concat_loc VARCHAR2(200);
3729 l_concat_asset_key VARCHAR2(200);
3730 l_concat_cat VARCHAR2(500);
3731 l_loc_segs fa_rx_shared_pkg.Seg_Array;
3732 l_asset_segs fa_rx_shared_pkg.Seg_Array;
3733 l_cat_segs fa_rx_shared_pkg.Seg_Array;
3734 l_stl_rate NUMBER;
3735 l_CFDescription VARCHAR2(40);
3736 l_ADDescription fa_additions.description%type;
3737 l_from_asset VARCHAR2(100);
3738 l_to_asset VARCHAR2(100);
3739 l_from_cc VARCHAR2(100);
3740 l_to_cc VARCHAR2(100);
3741 l_path VARCHAR2(150);
3742
3743 BEGIN
3744 l_path := g_path||'run_d_summary_report';
3745 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** START run_d_summary_report ** ');
3746
3747 l_select_statement := 'SELECT ' ||
3748 balancing_seg_no || ', ' ||
3749 cost_ctr_seg_no || ', ' ||
3750 account_seg_no || ', ' ||
3751 major_cat_seg_no || ', ' ||
3752 minor_cat_seg_no || ', ' ||
3753 'bk.book_type_Code book_type_code,
3754 ah.category_id category_id,
3755 cf.description category_description,
3756 ad.asset_number asset_number,
3757 ad.description asset_description,
3758 ad.tag_number asset_tag,
3759 ad.parent_asset_id parent_id,
3760 ad.serial_number serial_number,
3761 ad.asset_key_ccid asset_key_ccid,
3762 bk.life_in_months life_in_months,
3763 bk.date_placed_in_service date_placed_in_service,
3764 bk.deprn_method_code depreciation_method,
3765 dh.location_id location_id,
3766 cb.deprn_reserve_acct depreciation_reserve_account,
3767 cb.asset_cost_acct asset_cost_account,
3768 sum(nvl(dd.cost,0)) Reval_Cost,
3769 0 Reval_Reserve,
3770 0 Gen_Fund,
3771 0 Operating_Acct,
3772 sum(nvl(dd.deprn_reserve,0)) Acct_Deprn,
3773 0 Backlog
3774 FROM fa_additions ad,
3775 fa_Books bk,
3776 fa_distribution_history dh,
3777 fa_deprn_Detail dd,
3778 gl_code_combinations cc,
3779 fa_categories cf,
3780 fa_category_books cb,
3781 fa_book_controls fb,
3782 fa_deprn_periods fdp,
3783 fa_asset_history ah
3784 WHERE ad.asset_id = bk.asset_id
3785 AND ah.asset_id = bk.asset_id
3786 AND cf.category_id=ah.category_id
3787 AND cb.category_id = ah.category_id
3788 AND cf.category_id = ' || p_categoryId || '
3789 AND bk.book_type_code = :v_bookType
3790 AND fdp.book_type_code = bk.book_type_code
3791 AND fdp.period_counter = :v_period
3792 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
3793 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
3794 FROM fa_books ifb
3795 WHERE ifb.book_type_code = bk.book_type_code
3796 AND ifb.asset_id = bk.asset_id
3797 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
3798 AND dd.asset_id = bk.asset_id
3799 AND dd.book_type_code = bk.book_type_code
3800 AND cb.book_type_code = bk.book_type_code
3801 AND dh.distribution_id = dd.distribution_id
3802 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
3803 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
3804 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
3805 AND dh.code_combination_id = cc.code_combination_id
3806 AND fb.book_type_code = bk.book_type_code
3807 AND dd.period_counter =
3808 (SELECT max(period_counter)
3809 FROM fa_deprn_summary
3810 WHERE asset_id = bk.asset_id
3811 AND book_type_code = bk.book_type_code
3812 AND period_counter <= fdp.period_counter)
3813 AND bk.asset_id not in
3814 (SELECT asset_id
3815 FROM igi_iac_asset_balances
3816 WHERE book_type_code = bk.book_type_code
3817 AND asset_id = bk.asset_id)
3818 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc , ' || cost_ctr_seg_no || ' )
3819 AND nvl( :v_to_cc ,' || cost_ctr_seg_no || ')
3820 AND ad.asset_number between nvl( :v_from_asset , ad.asset_number) AND nvl( :v_to_asset , ad.asset_number)
3821 GROUP BY ad.asset_number,
3822 ad.description , ' ||
3823 balancing_seg_no || ', ' ||
3824 cost_ctr_seg_no || ', ' ||
3825 account_seg_no || ', ' ||
3826 'bk.book_type_code,
3827 ah.category_id,
3828 cf.description, ' ||
3829 major_cat_seg_no || ', ' ||
3830 minor_cat_seg_no || ', ' ||
3831 'ad.tag_number ,
3832 ad.parent_asset_id ,
3833 ad.serial_number ,
3834 ad.asset_key_ccid,
3835 bk.life_in_months ,
3836 bk.date_placed_in_service ,
3837 bk.deprn_method_code ,
3838 dh.location_id ,
3839 cb.deprn_reserve_acct ,
3840 cb.asset_cost_acct ' ||
3841
3842 ' UNION
3843 SELECT ' ||
3844 balancing_seg_no || ', ' ||
3845 cost_ctr_seg_no || ', ' ||
3846 account_seg_no || ', ' ||
3847 major_cat_seg_no || ', ' ||
3848 minor_cat_seg_no || ', ' ||
3849 'bk.book_type_Code book_type_code ,
3850 ah.category_id category_id,
3851 cf.description category_description,
3852 ad.asset_number asset_number ,
3853 ad.description asset_description,
3854 ad.tag_number asset_tag,
3855 ad.parent_asset_id parent_id,
3856 ad.serial_number serial_number,
3857 ad.asset_key_ccid asset_key_ccid,
3858 bk.life_in_months life_in_months,
3859 bk.date_placed_in_service date_placed_in_service,
3860 bk.deprn_method_code depreciation_method,
3861 dh.location_id location_id,
3862 cb.deprn_reserve_acct depreciation_reserve_account,
3863 cb.asset_cost_acct asset_cost_account,
3864 sum(nvl(( id.adjustment_cost + dd.cost), 0)) Reval_Cost,
3865 sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve,
3866 sum(nvl(id.reval_reserve_gen_fund, 0)) Gen_Fund,
3867 sum(nvl(id.operating_acct_net * -1, 0)) Operating_Acct,
3868 sum(nvl(id.deprn_reserve + dd.deprn_reserve, 0) ) Acct_Deprn,
3869 sum(nvl( id.deprn_reserve_backlog , 0)) Backlog
3870 FROM fa_additions ad ,
3871 fa_Books bk ,
3872 fa_distribution_history dh,
3873 fa_deprn_Detail dd ,
3874 igi_iac_det_balances id ,
3875 gl_code_combinations cc,
3876 fa_categories cf,
3877 fa_category_books cb,
3878 fa_book_controls fb,
3879 fa_deprn_periods fdp,
3880 fa_asset_history ah
3881 WHERE ad.asset_id = dh.asset_id
3882 AND ah.asset_id = bk.asset_id
3883 AND cf.category_id=ah.category_id
3884 AND cb.category_id = ah.category_id
3885 AND cf.category_id = ' || p_categoryId || '
3886 AND bk.book_Type_code = :v_bookType1
3887 AND fdp.book_type_code = bk.book_type_code
3888 AND fdp.period_counter = :v_period1
3889 AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
3890 AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
3891 FROM fa_books ifb
3892 WHERE ifb.book_type_code = bk.book_type_code
3893 AND ifb.asset_id = bk.asset_id
3894 AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
3895 AND bk.asset_id = ad.asset_id
3896 AND dh.book_type_Code = bk.book_type_code
3897 AND dh.book_type_code = dd.book_type_code
3898 AND cb.book_type_Code = bk.book_type_code
3899 AND dh.asset_id = dd.asset_id
3900 AND dh.distribution_id = dd.distribution_id
3901 AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
3902 AND dh.transaction_header_id_in >= ah.transaction_header_id_in
3903 AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
3904 AND fb.book_type_code = bk.book_type_code
3905 AND dd.period_counter = (SELECT MAX(period_counter)
3906 FROM fa_deprn_summary
3907 WHERE asset_id =bk.asset_id
3908 AND book_type_code = bk.book_type_code
3909 AND period_counter <= fdp.period_counter )
3910 AND dh.distribution_id = id.distribution_id
3911 AND dh.code_Combination_id = cc.code_combination_id
3912 AND id.adjustment_id = ( SELECT max(adjustment_id)
3913 FROM igi_iac_transaction_headers it
3914 WHERE it.asset_id = bk.asset_id
3915 AND it.book_type_code = bk.book_type_Code
3916 AND it.period_counter <= fdp.period_counter
3917 AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
3918 AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1 , ' || cost_ctr_seg_no || ' )
3919 AND nvl( :v_to_cc1 ,' || cost_ctr_seg_no || ')
3920 AND ad.asset_number between nvl( :v_from_asset1 , ad.asset_number) AND nvl( :v_to_asset1 , ad.asset_number)
3921 GROUP BY ad.asset_number ,
3922 ad.description , ' ||
3923 balancing_seg_no || ', ' ||
3924 cost_ctr_seg_no || ', ' ||
3925 account_seg_no || ', ' ||
3926 'bk.book_type_Code ,
3927 ah.category_id,
3928 cf.description, ' ||
3929 major_cat_seg_no || ', ' ||
3930 minor_cat_seg_no || ', ' ||
3931 'ad.tag_number ,
3932 ad.parent_asset_id ,
3933 ad.serial_number ,
3934 ad.asset_key_ccid,
3935 bk.life_in_months ,
3936 bk.date_placed_in_service ,
3937 bk.deprn_method_code ,
3938 dh.location_id ,
3939 cb.deprn_reserve_acct ,
3940 cb.asset_cost_acct';
3941
3942 -- bug 3421784, start 20
3943 -- commenting out
3944 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_select_statement ' || l_select_statement);
3945 -- bug 3421784, end 20
3946
3947 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
3948 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_categoryId --> ' || p_categoryId);
3949 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_bookType --> ' || p_bookType);
3950 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_period --> ' || p_period);
3951 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_cc --> ' || nvl(p_from_cc,1));
3952 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_cc --> ' || nvl(p_to_cc,1));
3953 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_from_asset --> ' || nvl(p_from_asset,1));
3954 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_to_asset --> ' || nvl(p_to_asset,1));
3955
3956 IF p_from_asset = 'NULL' THEN
3957 l_from_asset := NULL;
3958 ELSE
3959 l_from_asset := p_from_asset;
3960 END IF;
3961
3962 IF p_to_asset = 'NULL' THEN
3963 l_to_asset := NULL;
3964 ELSE
3965 l_to_asset := p_to_asset;
3966 END IF;
3967
3968 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_asset --> ' || nvl(l_from_asset,1));
3969 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_asset --> ' || nvl(l_to_asset,1));
3970
3971 IF p_from_cc = 'NULL' THEN
3972 l_from_cc := NULL;
3973 ELSE
3974 l_from_cc := p_from_cc;
3975 END IF;
3976
3977 IF p_to_cc = 'NULL' THEN
3978 l_to_cc := NULL;
3979 ELSE
3980 l_to_cc := p_to_cc;
3981 END IF;
3982
3983 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_from_cc --> ' || nvl(l_from_cc,1));
3984 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_to_cc --> ' || nvl(l_to_cc,1));
3985
3986 /* Bug 3490402 */
3987 OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
3988 p_period, /* :v_period */
3989 l_from_cc, /* :v_from_cc */
3990 l_to_cc, /* :v_to_cc, */
3991 l_from_asset, /* :v_from_asset */
3992 l_to_asset, /* :v_to_asset */
3993 p_bookType, /* :v_bookType1 */
3994 p_period, /* :v_period1 */
3995 l_from_cc, /* :v_from_cc1 */
3996 l_to_cc, /* :v_to_cc1 */
3997 l_from_asset, /* :v_from_asset1 */
3998 l_to_asset; /* :v_to_asset1 */
3999 LOOP
4000 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FETCH');
4001 FETCH ret_lines INTO
4002 l_gl_code_seg1,
4003 l_gl_code_seg2,
4004 l_gl_code_seg3,
4005 l_fa_cat_seg1,
4006 l_fa_cat_seg2,
4007 l_book_code,
4008 l_asset_cat_id,
4009 l_CFDescription,
4010 l_asset_number,
4011 l_ADDescription,
4012 l_asset_tag,
4013 l_parent_id,
4014 l_serial_number,
4015 l_asset_key_ccid,
4016 l_life_in_months,
4017 l_date_placed_in_service,
4018 l_depreciation_method,
4019 l_location_id, -- number
4020 l_depreciation_reserve_account,
4021 l_asset_cost_account,
4022 l_reval_cost,
4023 l_reval_reserve,
4024 l_general_fund,
4025 l_oper_acct,
4026 l_deprn_resv,
4027 l_backlog;
4028
4029 IF (ret_lines%NOTFOUND) THEN
4030 EXIT;
4031 END IF;
4032
4033 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after fetch ');
4034
4035 /* StatReq - The following if statement has been added to calculate the annual depreciation rate
4036 for straight-line, calculated depreciation methods */
4037
4038 -- Calculate STL_RATE from life_in_months
4039 IF (l_life_in_months > 0)
4040 THEN
4041 l_stl_rate := 12 / l_life_in_months * 100;
4042 ELSE
4043 l_stl_rate := NULL;
4044 END IF;
4045
4046 -- This will get the CONCATANATED LOCATION
4047 fa_rx_shared_pkg.concat_location (
4048 struct_id => l_g_loc_struct
4049 ,ccid => l_location_id
4050 ,concat_string => l_concat_loc
4051 ,segarray => l_loc_segs);
4052
4053 -- This will get the CONCATANATED ASSETKEY
4054 fa_rx_shared_pkg.concat_asset_key (
4055 struct_id => l_g_asset_key_struct
4056 ,ccid => l_asset_key_ccid
4057 ,concat_string => l_concat_asset_key
4058 ,segarray => l_asset_segs);
4059
4060 -- This gets the CONCATENATED CATEGORY NAME
4061 fa_rx_shared_pkg.concat_category (
4062 struct_id => l_g_cat_struct,
4063 ccid => l_asset_cat_id,
4064 concat_string => l_concat_cat,
4065 segarray => l_cat_segs);
4066
4067 /*IF NOT get_acct_seg_val_from_ccid ( p_bookType
4068 ,l_asset_cat_id
4069 ,l_dep_backlog
4070 ,l_gen_fund_acct
4071 ,l_oper_exp_acct
4072 ,l_reval_rsv_acct
4073 )
4074 THEN
4075 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failed to get Account segement values - will continue.... ');
4076 END IF;*/
4077 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_stl_rate ' || l_stl_rate);
4078 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_loc ' || l_concat_loc);
4079 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_asset_key ' || l_concat_asset_key);
4080 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_cat_id ' || l_asset_cat_id);
4081 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_concat_cat ' || l_concat_cat);
4082 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_dep_backlog ' || l_dep_backlog);
4083 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_gen_fund_acct ' || l_gen_fund_acct);
4084 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_oper_exp_acct ' || l_oper_exp_acct);
4085 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_reval_rsv_acct ' || l_reval_rsv_acct);
4086 l_parent_no := NULL;
4087
4088 IF l_parent_id IS NOT NULL THEN
4089 l_parent_no := l_asset_number;
4090 END IF;
4091
4092 INSERT INTO igi_iac_asset_rep_itf (
4093 request_id,
4094 company_name,
4095 book_type_code,
4096 period,
4097 fiscal_year_name,
4098 major_category,
4099 cost_center,
4100 asset_number,
4101 asset_description,
4102 asset_tag,
4103 parent_no,
4104 serial_no,
4105 life_months,
4106 stl_rate,
4107 dpis,
4108 depreciation_method,
4109 conc_asset_key,
4110 conc_location,
4111 --deprn_exp_acct,
4112 --deprn_res_acct,
4113 cost_acct,
4114 --iac_reval_resv_acct,
4115 balancing_segment,
4116 --deprn_backlog_acct,
4117 --gen_fund_acct,
4118 --oper_exp_acct,
4119 concat_category,
4120 reval_cost,
4121 minor_category,
4122 reval_reserve,
4123 general_fund,
4124 oper_acct,
4125 deprn_resv,
4126 backlog,
4127 functional_currency_code,
4128 created_by,
4129 creation_date,
4130 last_updated_by,
4131 last_update_date,
4132 last_update_login
4133 )
4134 VALUES
4135 (
4136 p_request_id,
4137 l_company_name,
4138 l_book_code,
4139 l_period_name,
4140 l_fiscal_year_name,
4141 l_fa_cat_seg1,
4142 l_gl_code_seg2,
4143 l_asset_number,
4144 l_ADDescription,
4145 l_asset_tag,
4146 l_parent_no,
4147 l_serial_number,
4148 l_life_in_months,
4149 l_stl_rate,
4150 l_date_placed_in_service,
4151 l_depreciation_method,
4152 l_concat_asset_key,
4153 l_concat_loc,
4154 --l_gl_code_seg3,
4155 --l_depreciation_reserve_account,
4156 l_asset_cost_account,
4157 --l_reval_rsv_acct,
4158 l_gl_code_seg1,
4159 --l_dep_backlog,
4160 --l_gen_fund_acct,
4161 --l_oper_exp_acct,
4162 l_concat_cat,
4163 l_reval_cost,
4164 l_fa_cat_seg2,
4165 l_reval_reserve,
4166 l_general_fund,
4167 l_oper_acct,
4168 l_deprn_resv,
4169 l_backlog,
4170 l_currency_code,
4171 l_user_id,
4172 sysdate,
4173 l_user_id,
4174 sysdate,
4175 p_login_id
4176 );
4177
4178 END LOOP;
4179 CLOSE ret_lines;
4180 RETURN TRUE;
4181
4182 EXCEPTION
4183 WHEN OTHERS THEN
4184 -- bug 3421784, start 21
4185 IF ret_lines%ISOPEN THEN
4186 CLOSE ret_lines;
4187 END IF;
4188 -- bug 3421784, end 21
4189 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within run_d_summary_report : '|| sqlerrm);
4190 RETURN FALSE;
4191 END run_d_summary_report;
4192
4193 FUNCTION Delete_Zero_Rows(p_bookType IN VARCHAR2,
4194 p_request_id IN NUMBER,
4195 p_reptShrtName IN VARCHAR2)
4196 RETURN BOOLEAN IS
4197 l_path VARCHAR2(150);
4198 BEGIN
4199 l_path := g_path||'Delete_Zero_Rows';
4200 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting rows with all zero values');
4201 IF p_reptShrtName IN ('RXIGIIAK','RXIGIIAD') THEN
4202 DELETE FROM igi_iac_asset_rep_itf
4203 WHERE book_type_code = p_bookType
4204 AND request_id = p_request_id
4205 AND reval_cost = 0
4206 AND deprn_period = 0
4207 AND ytd_deprn = 0
4208 AND deprn_resv = 0
4209 AND deprn_backlog = 0
4210 AND deprn_total = 0;
4211 ELSIF p_reptShrtName IN ('RXIGIIAM','RXIGIIAO') THEN
4212 DELETE FROM igi_iac_asset_rep_itf
4213 WHERE book_type_code = p_bookType
4214 AND request_id = p_request_id
4215 AND reval_cost = 0
4216 AND oper_exp = 0
4217 AND oper_exp_backlog = 0
4218 AND oper_exp_net = 0;
4219 ELSIF p_reptShrtName IN ('RXIGIIAL','RXIGIIAR') THEN
4220 DELETE FROM igi_iac_asset_rep_itf
4221 WHERE book_type_code = p_bookType
4222 AND request_id = p_request_id
4223 AND reval_cost = 0
4224 AND reval_resv_cost = 0
4225 AND reval_resv_blog = 0
4226 AND reval_resv_gen_fund = 0
4227 AND reval_resv_net = 0;
4228 ELSIF p_reptShrtName IN ('RXIGIIAJ','RXIGIIAB') THEN
4229 DELETE FROM igi_iac_asset_rep_itf
4230 WHERE book_type_code = p_bookType
4231 AND request_id = p_request_id
4232 AND reval_cost = 0
4233 AND reval_reserve = 0
4234 AND general_fund = 0
4235 AND oper_acct = 0
4236 AND deprn_resv = 0
4237 AND backlog = 0;
4238 END IF;
4239 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleted rows with all zero values');
4240 EXCEPTION
4241 WHEN OTHERS THEN
4242 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within Delete_Zero_Rows : '|| sqlerrm);
4243 RETURN FALSE;
4244 END Delete_Zero_Rows;
4245
4246 FUNCTION get_acct_seg_values ( p_bookType IN VARCHAR2
4247 ,p_categoryId IN NUMBER
4248 ,p_deprn_res_acct IN OUT NOCOPY
4249 fa_category_books.deprn_reserve_acct%TYPE
4250 ,p_deprn_exp_acct IN OUT NOCOPY
4251 fa_category_books.deprn_expense_acct%TYPE)
4252 RETURN BOOLEAN IS
4253
4254 CURSOR c_acct(cp_bookType VARCHAR2, cp_categoryId NUMBER) IS
4255 SELECT fc.deprn_reserve_acct,
4256 fc.deprn_expense_acct
4257 FROM fa_category_books fc
4258 WHERE fc.Book_Type_Code = cp_bookType
4259 AND fc.Category_id = cp_categoryId;
4260
4261 l_retVal BOOLEAN;
4262 l_path VARCHAR2(150);
4263 BEGIN
4264
4265 l_retVal := FALSE;
4266 l_path := g_path||'get_acct_seg_values';
4267
4268 FOR l_acct in c_acct (p_bookType, p_categoryId ) LOOP
4269 p_deprn_res_acct := l_acct.deprn_reserve_acct ;
4270 p_deprn_exp_acct := l_acct.deprn_expense_acct;
4271 l_retVal := TRUE;
4272 END LOOP;
4273 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_deprn_res_acct ' || p_deprn_res_acct);
4274 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_deprn_exp_acct ' || p_deprn_exp_acct);
4275 RETURN l_retVal;
4276 EXCEPTION
4277 WHEN OTHERS THEN
4278 -- bug 3421784, start 21
4279 IF c_acct%ISOPEN THEN
4280 CLOSE c_acct;
4281 END IF;
4282 -- bug 3421784, end 21
4283 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within get_acct_seg_values : '|| sqlerrm);
4284 RETURN FALSE;
4285 END get_acct_seg_values;
4286
4287 FUNCTION get_acct_seg_val_from_ccid ( p_bookType IN VARCHAR2
4288 ,p_categoryId IN NUMBER
4289 ,p_deprn_backlog IN OUT NOCOPY VARCHAR2
4290 ,p_gen_fund_acct IN OUT NOCOPY VARCHAR2
4291 ,p_oper_exp_acct IN OUT NOCOPY VARCHAR2
4292 ,p_reval_rsv_acct IN OUT NOCOPY VARCHAR2)
4293 RETURN BOOLEAN IS
4294
4295 CURSOR c_acct(cp_bookType VARCHAR2, cp_categoryId NUMBER) IS
4296 SELECT cb.backlog_deprn_rsv_ccid,
4297 cb.general_fund_ccid,
4298 cb.operating_expense_ccid,
4299 cb.reval_rsv_ccid
4300 FROM igi_iac_category_books cb
4301 WHERE cb.Book_Type_Code = cp_bookType
4302 AND cb.Category_id = cp_categoryId;
4303
4304 l_backlog_deprn_rsv_ccid NUMBER;
4305 l_general_fund_ccid NUMBER;
4306 l_operating_expense_ccid NUMBER;
4307 l_reval_rsv_ccid NUMBER;
4308 l_set_of_books_id NUMBER;
4309 l_chart_of_accts NUMBER;
4310 l_currency VARCHAR2(50);
4311 l_precision NUMBER;
4312 l_path VARCHAR2(150);
4313
4314 BEGIN
4315 l_backlog_deprn_rsv_ccid := -1;
4316 l_general_fund_ccid := -1;
4317 l_operating_expense_ccid := -1;
4318 l_reval_rsv_ccid := -1;
4319
4320 l_path := g_path||'get_acct_seg_val_from_ccid';
4321
4322 FOR l_acct in c_acct (p_bookType, p_categoryId ) LOOP
4323 l_backlog_deprn_rsv_ccid := l_acct.backlog_deprn_rsv_ccid ;
4324 l_general_fund_ccid := l_acct.general_fund_ccid;
4325 l_operating_expense_ccid := l_acct.operating_expense_ccid;
4326 l_reval_rsv_ccid := l_acct.reval_rsv_ccid;
4327 END LOOP;
4328
4329 IF (l_backlog_deprn_rsv_ccid = -1 OR l_general_fund_ccid = -1 OR
4330 l_operating_expense_ccid = -1 OR l_reval_rsv_ccid = -1) THEN
4331 RETURN FALSE;
4332 END IF;
4333
4334 IF NOT IGI_IAC_COMMON_UTILS.Get_Book_GL_Info (
4335 p_bookType
4336 ,l_set_of_books_id
4337 ,l_chart_of_accts
4338 ,l_currency
4339 ,l_precision
4340 )
4341 THEN
4342 RETURN FALSE;
4343 ELSE
4344 IF NOT IGI_IAC_COMMON_UTILS.Get_Account_Segment_Value (
4345 l_set_of_books_id
4346 ,l_backlog_deprn_rsv_ccid
4347 ,'GL_ACCOUNT'
4348 ,p_deprn_backlog
4349 )
4350 THEN
4351 p_deprn_backlog := NULL;
4352 END IF;
4353
4354 IF NOT IGI_IAC_COMMON_UTILS.Get_Account_Segment_Value (
4355 l_set_of_books_id
4356 ,l_general_fund_ccid
4357 ,'GL_ACCOUNT'
4358 ,p_gen_fund_acct
4359 )
4360 THEN
4361 p_gen_fund_acct := NULL;
4362 END IF;
4363
4364 IF NOT IGI_IAC_COMMON_UTILS.Get_Account_Segment_Value (
4365 l_set_of_books_id
4366 ,l_operating_expense_ccid
4367 ,'GL_ACCOUNT'
4368 ,p_oper_exp_acct
4369 )
4370 THEN
4371 p_oper_exp_acct := NULL;
4372 END IF;
4373
4374 IF NOT IGI_IAC_COMMON_UTILS.Get_Account_Segment_Value (
4375 l_set_of_books_id
4376 ,l_reval_rsv_ccid
4377 ,'GL_ACCOUNT'
4378 ,p_reval_rsv_acct
4379 )
4380 THEN
4381 p_reval_rsv_acct := NULL;
4382 END IF;
4383
4384 END IF;
4385
4386 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_set_of_books_id ' || l_set_of_books_id);
4387 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_chart_of_accts ' || l_chart_of_accts);
4388 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_deprn_backlog ' || p_deprn_backlog);
4389 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_gen_fund_acct ' || p_gen_fund_acct);
4390 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_oper_exp_acct ' || p_oper_exp_acct);
4391 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_reval_rsv_acct ' || p_reval_rsv_acct);
4392
4393 RETURN TRUE;
4394
4395 EXCEPTION
4396 WHEN OTHERS THEN
4397 -- bug 3421784, start 22
4398 IF c_acct%ISOPEN THEN
4399 CLOSE c_acct;
4400 END IF;
4401 -- bug 3421784, end 22
4402
4403 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within get_acct_seg_val_from_ccid : '|| sqlerrm);
4404 RETURN FALSE;
4405 END get_acct_seg_val_from_ccid;
4406
4407 BEGIN
4408 --===========================FND_LOG.START=====================================
4409 g_state_level := FND_LOG.LEVEL_STATEMENT;
4410 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
4411 g_event_level := FND_LOG.LEVEL_EVENT;
4412 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
4413 g_error_level := FND_LOG.LEVEL_ERROR;
4414 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
4415 g_path := 'IGI.PLSQL.igiiaxcb.igi_iac_rxi_i_wrap_asset_bal.';
4416 --===========================FND_LOG.END=====================================
4417
4418 END igi_iac_rxi_i_wrap_asset_bal;