[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_TAX_REP_PKG
Source
4 -- ER 7661628: Adding this variable to make the code changes for 7661628 specific to R12 only
1 PACKAGE BODY FA_DEPRN_TAX_REP_PKG as
2 /* $Header: fadptxb.pls 120.48.12020000.5 2013/02/12 11:43:37 adaluru ship $ */
3
5 g_release number := fa_cache_pkg.fazarel_release;
6
7 --toru
8 --g_print_debug boolean := fa_cache_pkg.fa_print_debug;
9 g_print_debug boolean := TRUE;
10
11 /*===========================================================================
12 PROCEDURE
13 FADPTX_INSERT
14
15 DESCRIPTION
16 This procedure insert the data to interim table:FA_DEPRN_TAX_REP_ITF.
17 ===========================================================================*/
18
19 procedure fadptx_insert (
20 errbuf out nocopy varchar2,
21 retcode out nocopy number,
22 book in varchar2, /* Book type code */
23 year in number, /* Target Year */
24 state_from in varchar2, /* Print Location State from */
25 state_to in varchar2, /* Print Location State to */
26 tax_asset_type_seg in varchar2, /* Category Segment Number or Qualifier */
27 category_from in varchar2, /* Tax Asset Type Category From */
28 category_to in varchar2, /* Tax Asset Type Category To */
29 sale_code in varchar2, /* Ritirement type for reason code */
30 all_state in boolean, -- Obsolete this parameter
31 rounding in boolean, -- Round bug4919991
32 eval_nbv_round in varchar2, -- Bug 9145830
33 request_id in number, /* Request id */
34 login_id in number /* login id */
35 ) is
36
37 v_MainCursor number;
38 v_MainReturn number;
39 v_MainFetch number;
40
41 v_SubCursor number;
42 v_SubReturn number;
43 v_SubFetch number;
44
45 h_login_id number;
46 h_request_id number;
47
48 /* Client Extension */
49 v_ExtCursor number;
50 l_ExtString varchar2(1500);
51 v_ExtReturn number;
52 no_package exception;
53 PRAGMA EXCEPTION_INIT (no_package, -6550);
54
55 /* Parameter */
56 h_book varchar2(30);
57 h_year number;
58 h_category_from varchar2(150);
59 h_category_to varchar2(150);
60 h_sale_code varchar2(30);
61
62 /* If h_book is tax book, need source corp book */
63 h_corp_book varchar2(30);
64
65 /* Start date and End date */
66 h_target_date date;
67 h_prior_date date;
68
69 /* FA flexfield structure information */
70 cat_flex_struct number;
71 loc_flex_struct number;
72 l_parm_minor varchar2(50);
73 l_parm_state varchar2(50);
74
75 /* Precision infomation */
76 h_currency_code varchar2(15);
77 h_precision number;
78 h_ext_precision number;
79 h_min_acct_unit number := null;
80
81 h_company_name varchar2(80);
82
83 /* Dynamic SQL */
84 sql_base varchar2(20000);
85 sql_both_base varchar2(20000);
86 l_parm_view varchar2(5000);
90 l_select_sm varchar2(1000);
87 l_base_where varchar2(3000);
88 l_sm_where varchar2(3000);
89
91 l_base_from varchar2(7000);
92 l_select_base varchar2(2500);
93 l_select_both varchar2(2500);
94 l_select_end varchar2(2500);
95 l_select_start varchar2(2500);
96 l_from_stmt varchar2(2000);
97 l_group_by varchar2(500);
98
99 l_main dbms_sql.varchar2s;
100
101 l_sub_sql varchar2(3000); -- Bug3896299 Extended.
102 l_asset_id number;
103 l_total_cost number; -- bug#2661575: asset total cost at target date
104 l_total_prior_cost number; -- bug#2661575: asset total cost at prior date
105 l_total_units number; -- bug#2661575: asset total units at target date
106 l_total_prior_units number; -- bug#2661575: asset total units at prior date
107
108 /* Valiable for output */
109
110 h_asset_id number;
111 h_asset_number varchar2(15);
112 h_asset_desc varchar2(80);
113 h_new_used varchar2(4);
114 h_book_type_code varchar2(30);
115 h_minor_category varchar2(150);
116 h_start_asset_type varchar2(15); -- Treate UTF8
117 h_tax_asset_type varchar2(15); -- Treate UTF8
118 h_minor_cat_desc varchar2(240);
119 h_start_state varchar2(150);
120 h_state varchar2(150);
121 h_start_units_total number;
122 h_end_units_total number;
123 h_start_units_assigned number;
124 h_end_units_assigned number;
125 h_end_cost number;
126 h_increase_cost number;
127 h_start_cost number;
128 h_decrease_cost number;
129 h_theoretical_nbv number;
130 h_evaluated_nbv number;
131 h_date_in_service date;
132 h_era_name_num varchar2(1);
133 h_add_era_year number; /* Japaese Imperial year */
134 h_add_year number; /* RRRR */
135 h_add_month number;
136 h_start_life number;
137 h_end_life number;
138 h_theoretical_residual_rate number;
139 h_evaluated_residual_rate number;
140 h_adjusted_rate number;
141 h_theoretical_taxable_cost number;
142 h_evaluated_taxable_cost number;
143 h_all_reason_type varchar2(30);
144 h_all_reason_code varchar2(1);
145 h_adddec_reason_type varchar2(30);
146 h_adddec_reason_code varchar2(1);
147 h_dec_type varchar2(1);
148 h_add_dec_flag varchar2(1);
149 /* bug 2082460 */
150 h_all_description varchar2(80);
151 h_adddec_description varchar2(80);
155 /* Variable fro Cost Distirbute Calculation */
152 h_action_flag varchar2(1);
153
154
156
157 h_end_cost_total number :=0; /* Cost total of an asst */
158 h_end_units_accm number :=0; /* Units accumlate */
159 h_end_cost_accm number :=0; /* Cost accumlate */
160 h_end_asset_id number :=0; /* Asset_id for cost distiribute */
161
162 h_start_cost_total number :=0; /* Cost total of an asst */
163 h_start_units_accm number :=0; /* Units accumlate */
164 h_start_cost_accm number :=0; /* Cost accumlate */
165 h_start_asset_id number :=0; /* Asset_id for cost distiribute */
166
167 /* FLAG for Reason Code */
168 r_addition_flag varchar2(1);
169 r_ret_flag varchar2(1);
170 r_ret_id number;
171 r_ret_type_code varchar2(15);
172 r_sold_to varchar2(30); -- Bug#3560574 Expanded to 30 bites
173 r_ret_transaction_name varchar2(30);
174 r_transfer_flag varchar2(1);
175 r_transfer_date varchar2(10);
176 r_trn_transaction_name varchar2(30);
177 /* bug 2082460 */
178 r_change_life_desc varchar2(80);
179
180 /* Report Flag */
181 h_sum_rep BOOLEAN;
182 h_all_rep BOOLEAN;
183 h_add_rep BOOLEAN;
184 h_dec_rep BOOLEAN;
185
186 /* Request Id */
187 h_req1 number;
188 h_req2 number;
192 /* NBV CALCULATION */
189 h_req3 number;
190 h_req4 number;
191
193 h_half_rate number; /* half year residual rate */
194 h_full_rate number; /* full year residual rate */
195 h_diff_year number; /* target year - add_year */
196 i number :=0; /* Loop counter */
197 k number :=0; /* Loop counter for main sql */
198
199 /* Restructure of Logic */
200 sql_nbv varchar2(20000);
201 l_nbv_where varchar2(3000);
202 l_select_nbv varchar2(2500);
203 l_from_nbv varchar2(2000);
204
205 sql_base_ba2 varchar2(20000);
206 l_select_base_ba2 varchar2(2500);
207 l_from_stmt_ba2 varchar2(100);
208 l_where_ba2 varchar2(250);
209
210 /* Bind Variable Project */
211 h_method_code varchar2(10) := 'JP-DB %';
212
213 /* Bug#3305784 - Enhancement to make category flexfield segment flexible */
214 h_tax_asset_type_segment varchar2(30);
215
216 /* bug#2433829 -- Supported Rate chenges */ -- Obsolete
217
218 --
219 /* bug#2448122 -- Treate FA_DEPRN_TAX_REP_NBVS */
220
221 CURSOR c_last_update
222 (
223 p_asset_id NUMBER,
224 p_book_type_code VARCHAR2,
225 p_state VARCHAR2,
226 p_year NUMBER
227 ) IS
228 SELECT cost,
229 tax_asset_type,
230 units_assigned,
231 life
232 FROM FA_DEPRN_TAX_REP_NBVS dtn
233 WHERE dtn.asset_id = p_asset_id
234 AND dtn.book_type_code = p_book_type_code
235 AND dtn.state = p_state
236 AND dtn.year = p_year -1;
237
238
239 CURSOR c_nbv_update --bug#2661575 Removed parameter p_add_year
240 (
241 p_asset_id NUMBER,
242 p_book_type_code VARCHAR2,
243 p_state VARCHAR2,
244 p_year NUMBER
245 ) IS
246 SELECT cost,
247 theoretical_nbv,
248 evaluated_nbv,
249 year,
250 units_assigned
251 FROM FA_DEPRN_TAX_REP_NBVS dtn
252 WHERE dtn.asset_id = p_asset_id
253 AND dtn.book_type_code = p_book_type_code
254 AND dtn.state = p_state
255 AND dtn.year = p_year;
256
257
258 h_up_cost NUMBER;
259 h_up_last_cost NUMBER;
260 h_up_tax_asset_type VARCHAR2(15);
261 h_up_units_assigned NUMBER;
262 h_up_life NUMBER;
263 h_up_theoretical_nbv NUMBER;
264 h_up_evaluated_nbv NUMBER;
265 h_up_year NUMBER;
266 h_up_nbv_flag VARCHAR2(1);
267 h_last_up_flag VARCHAR2(1);
268 l_start_loop number;
269 h_store_theoretical_nbv NUMBER;
270 h_store_evaluated_nbv NUMBER;
271
272 l_state_query VARCHAR2(1000);
273 v_state_cursor NUMBER;
274 v_state_return NUMBER;
275 v_state_fetch NUMBER;
276 h_state_range VARCHAR2(150);
277 h_state_flag VARCHAR2(1);
278 h_deprn_tax_rep_nbv_id NUMBER(15);
279
280 BOTH_NBV_ERROR EXCEPTION;
281 --
282 -- bug#2629893: Treate the transfer NBV distributions
283 h_tmp_units_assigned NUMBER;
284 dist_asset_id NUMBER(15);
285 dist_year NUMBER;
286 dist_total_cost NUMBER; -- bug#2661575
287 dist_total_evaluated_nbv NUMBER;
288 dist_total_theoretical_nbv NUMBER;
289 dist_last_total_units NUMBER; -- bug#2661575
290 dist_total_units NUMBER;
291 dist_units_assigned NUMBER;
292 h_abs_units NUMBER; -- Output variable
293 h_last_nbv_total_flag VARCHAR2(1); -- flag for c_last_nbv_total
294
295 CURSOR c_last_nbv_total (
296 p_asset_id NUMBER,
297 p_book_type_code VARCHAR2,
298 p_year NUMBER
299 )
300 is
301 SELECT sum(dtn.cost), -- bug#2661575
302 sum(dtn.evaluated_nbv),
303 sum(dtn.theoretical_nbv),
304 sum(dtn.units_assigned), -- bug#2661575
305 dtn.year
306 FROM FA_DEPRN_TAX_REP_NBVS dtn
307 WHERE asset_id = p_asset_id
308 AND book_type_code = p_book_type_code
309 AND year =
310 (SELECT MAX(year) FROM fa_deprn_tax_rep_nbvs dtn2
314 group by dtn.year;
311 WHERE dtn2.asset_id = p_asset_id
312 AND dtn2.book_type_code = p_book_type_code
313 AND dtn2.year < p_year)
315
316 --
317 -- Get total asset untis from FA_ASSET_HISTORY at target_date
318 CURSOR c_total_units_cost (
319 p_book_type_code VARCHAR2,
320 p_asset_id NUMBER,
321 p_target_date DATE)
322
323 is
324 select AH.UNITS,
325 bk.cost
326 from FA_ASSET_HISTORY AH,
327 FA_BOOKS BK
328 Where AH.ASSET_ID = p_asset_id
329 and AH.ASSET_ID = BK.ASSET_ID
330 and BK.BOOK_TYPE_CODE = p_book_type_code
331 and AH.TRANSACTION_HEADER_ID_IN =
332 (select max(AH.TRANSACTION_HEADER_ID_IN )
333 from FA_ASSET_HISTORY AH,
334 FA_TRANSACTION_HEADERS TH1,
335 FA_TRANSACTION_HEADERS TH2
336 where AH.ASSET_ID = p_asset_id
337 and AH.TRANSACTION_HEADER_ID_IN =TH1.TRANSACTION_HEADER_ID
338 and AH.TRANSACTION_HEADER_ID_OUT=TH2.TRANSACTION_HEADER_ID(+)
339 and TH1.TRANSACTION_DATE_ENTERED <= p_target_date
340 and nvl(TH2.TRANSACTION_DATE_ENTERED,p_target_date +1)
341 > p_target_date
342 )
343 and bk.TRANSACTION_HEADER_ID_IN =
344 (SELECT MAX(bk.TRANSACTION_HEADER_ID_IN)
345 from FA_BOOKS bk ,
346 FA_TRANSACTION_HEADERS TH1,
347 FA_TRANSACTION_HEADERS TH2
348 where bk.asset_id= p_asset_id
349 and bk.book_type_code= p_book_type_code
350 and BK.transaction_header_id_in = TH1.transaction_header_id
351 and bk.transaction_header_id_out= TH2.transaction_header_id (+)
352 and th1.transaction_date_entered <= p_target_date
353 and nvl(th2.transaction_date_entered,p_target_date+1) > p_target_date
354 );
355
356
357
358 -- Exception message
359 l_calling_fn varchar2(50) :='fa_deprn_tax_rep_pkg.fadptx_insert';
360 err_msg varchar2(100);
361
362 -- Check the values of theoretical nbv and evaluated nbv on all states
363 l_chk_nbv_total number;
364 l_chk_theoretical_nbv number;
365 l_chk_evaluated_nbv number;
366
367
368 -- For calling upgrade package
369 v_MigCursor number;
370 l_MigString varchar2(1500);
371 v_MigReturn number;
372 no_package2 exception;
373 PRAGMA EXCEPTION_INIT (no_package2, -6550);
374
375 -- Bug#3327616
376 -- New corsur to query MAX amounts separated from main SQL
377 cursor c_bk_max_date_effective(p_asset_id number,
378 p_book varchar2,
379 p_prior_date date) is
380 SELECT MAX(bk.date_effective)
381 from FA_BOOKS bk ,
382 FA_TRANSACTION_HEADERS TH1,
383 FA_TRANSACTION_HEADERS TH2
384 where bk.asset_id= p_asset_id
385 and bk.book_type_code= p_book
386 and BK.transaction_header_id_in = TH1.transaction_header_id
387 and bk.transaction_header_id_out= TH2.transaction_header_id (+)
388 and th1.transaction_date_entered <= p_prior_date
389 and nvl(th2.transaction_date_entered,p_prior_date+1) > p_prior_date;
390
391 cursor c_ah_max_date_effective(p_asset_id number,
392 p_prior_date date) is
393 SELECT MAX(ah1.date_effective)
394 from FA_ASSET_HISTORY ah1 ,
395 FA_TRANSACTION_HEADERS TH1,
396 FA_TRANSACTION_HEADERS TH2
397 where ah1.asset_id= p_asset_id
398 and ah1.transaction_header_id_in = TH1.transaction_header_id
399 and ah1.transaction_header_id_out= TH2.transaction_header_id (+)
400 and th1.transaction_date_entered <= p_prior_date
401 and nvl(th2.transaction_date_entered, p_prior_date+1) > p_prior_date;
402
403 h_prior_bk_date_effective date;
404 h_prior_ah_date_effective date;
405 h_target_bk_date_effective date;
406
407 cursor c_sum_nbvs_cost(p_asset_id number,
408 p_book varchar2,
409 p_year number) is
410 SELECT SUM(cost),asset_id
411 from FA_DEPRN_TAX_REP_NBVS
412 where book_type_code = p_book
413 and asset_id = p_asset_id
414 and year = p_year
415 group by asset_id;
416
417 h_sum_nbvs_cost number;
418 h_sum_nbvs_asset_id number;
419
420 /* Bug3859151 */
421 /* Need to check THIDIN with Date Effective to support same date-time case */
422 cursor c_bk_max_thid_in(p_asset_id number,
423 p_book varchar2,
424 p_date_effective date) is
425 SELECT MAX(bk.transaction_header_id_in)
426 from FA_BOOKS bk
427 where bk.asset_id= p_asset_id
428 and bk.book_type_code= p_book
429 and bk.date_effective = p_date_effective;
430
431 cursor c_ah_max_thid_in(p_asset_id number,
432 p_date_effective date) is
433 SELECT MAX(ah1.transaction_header_id_in)
437
434 from FA_ASSET_HISTORY ah1
435 where ah1.asset_id= p_asset_id
436 and ah1.date_effective = p_date_effective;
438 h_prior_bk_thid_in number;
439 h_prior_ah_thid_in number;
440 h_target_bk_thid_in number;
441
442 --Bug6200581
443 h_current_state_flag varchar2(1);
444 l_transfer_sql varchar2(3000); -- Bug 9071204
445 v_TransferCursor number;
446 v_TransferFetch number;
447 v_TransferReturn number;
448
449 -- ER 7661628
450 -- To insert a record for tax authority code without any assets so that the asset types are displayed.
451 cursor c_missing_states(p_request_id number,
452 p_book varchar2,
453 p_state_from varchar2,
454 p_state_to varchar2) is
455 select ffv.flex_value
456 from fnd_flex_value_sets ffvs,
457 fnd_flex_values ffv,
458 fnd_flex_values_tl ffvt
459 where ffvs.flex_value_set_id = ffv.flex_value_set_id
460 and ffv.flex_value_id = ffvt.flex_value_id
461 and ffvs.flex_value_set_name = 'Vision FA State'
462 and ffvt.language = 'US'
463 and flex_value between p_state_from and p_state_to
464 and ffv.flex_value not in (select distinct state
465 from fa_deprn_Tax_rep_itf
466 where request_id = p_request_id)
467 and ffv.flex_value in (select fdta.code
471 and fdte.book_type_code = p_book)
468 from fa_deprn_tax_entities fdte,
469 fa_deprn_tax_Authorities fdta
470 where fdte.company_id = fdta.company_id -- Bug 8677658
472 order by ffv.flex_value;
473 -- End ER 7661628
474
475 --Start for 9935602
476
477 TYPE tax_asset_type IS TABLE OF VARCHAR2(15) INDEX BY PLS_INTEGER;
478 tax_asset_v tax_asset_type;
479
480 TYPE state_code_type IS TABLE OF VARCHAR2(150) INDEX BY PLS_INTEGER;
481 state_code_v state_code_type;
482
483 l_oth_type_desc varchar2(240) := NULL;
484 l_match_found NUMBER := 0;
485 l_local_cntr NUMBER := 0;
486 l_tax_type_cntr NUMBER := 0;
487
488 --End for 9935602
489
490 begin
491
492 IF (g_print_debug) THEN
493 fa_rx_util_pkg.debug ('*****START FA_DEPRN_TAX_REP_PKG.FADPTX_INSERT*****');
494 END IF;
495
496 --Start for 9935602
497
498 tax_asset_v(0) := '0';
499 state_code_v(0) := '0';
500
501 --End for 9935602
502
503
504 /*======================================================================
505 SET DEFALUT
506
507 h_target_date : This is target date to print all report.
508 And end date to print addition and decrease
509 report.
510 h_prior_date : This is start date to print addition and
511 decrease report.
512 ======================================================================*/
513
514 errbuf :=null;
515 retcode := 0;
516 h_year := year;
517 h_book := book;
518
519 h_category_from := category_from;
520 h_category_to := category_to;
521 h_target_date := to_date('01-01-'||h_year,'DD-MM-YYYY');
522 /*
523 bug 1978681
524 Prior date is changed '01-JAN' from '02-JAN'
525 */
526 h_prior_date := to_date('01-01-'||(h_year-1),'DD-MM-YYYY');
527
528 /* Bug#3305784 - Enhancement to make flexfield segment flexible */
529 h_tax_asset_type_segment := nvl(tax_asset_type_seg,'MINOR_CATEGORY');
530
531 h_request_id := request_id;
532 h_login_id := login_id;
533
534 IF (g_print_debug) THEN
535 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Book: '||h_book);
536 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Year: '||h_year);
537 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****State from: '||state_from);
538 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****State to :'||state_to);
539 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Tax Asset Type Segment: '||h_tax_asset_type_segment);
540 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Category low: '||h_category_from);
541 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Category high: '||h_category_to);
542 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Target date: '||h_target_date);
543 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Prior date: '||h_prior_date);
544 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Request id: '||h_request_id);
545 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****login id: '||h_login_id);
546 -- bug4919991
547 if (rounding) then
551 end if;
548 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Round: Y');
549 else
550 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Round: N');
552 fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Rounding of Evaluated NBV: '||eval_nbv_round); -- Bug 9145830
553 END IF;
554
555 /* Get Distribution Source CORPORATE BOOK */
556
557 Select DISTRIBUTION_SOURCE_BOOK
558 Into h_corp_book
559 From FA_BOOK_CONTROLS
560 where BOOK_TYPE_CODE =h_book;
561
562 IF (g_print_debug) THEN
563 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Distribution Source Book : '||h_corp_book);
564 END IF;
565
566 /* Get Category FF and Loacation FF ,Currency information */
567
568 SELECT SOB.CURRENCY_CODE,
569 SC.COMPANY_NAME,
570 SC.CATEGORY_FLEX_STRUCTURE,
571 SC.LOCATION_FLEX_STRUCTURE
572 INTO h_currency_code,
573 h_company_name,
574 cat_flex_struct,
575 loc_flex_struct
576 FROM FA_SYSTEM_CONTROLS SC,
577 FA_BOOK_CONTROLS BC,
578 GL_SETS_OF_BOOKS SOB
579 WHERE BC.BOOK_TYPE_CODE = h_book
580 and SOB.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID;
581
582 IF (g_print_debug) THEN
583 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Currency Code: '||h_currency_code);
584 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Category struct id: '||cat_flex_struct);
585 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Location struct id: '||loc_flex_struct);
586 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Company Name: '||h_company_name);
587 END IF;
588
589 /* Get FA_LOOKUP CODE values */
590 select meaning
591 into r_change_life_desc
592 from FA_LOOKUPS
593 where lookup_type = 'JP_REASON_TYPE'
594 and lookup_code = 'CHANGE LIFE';
595
596 /* Get Precision */
597 FND_CURRENCY.GET_INFO(
598 currency_code => h_currency_code,
599 precision => h_precision,
600 ext_precision => h_ext_precision,
601 min_acct_unit => h_min_acct_unit
602 );
603
604 IF (g_print_debug) THEN
605 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Precision: '||h_currency_code);
606 END IF;
607
608 /* Get Minor Category Segment */
609 /* Bug#3305764 - Enhancement to make flexfield segment flexible */
610 /* Changed hard-coded 'MINOR_CATEGORY' to h_tax_Asset_type_segment */
611 l_parm_minor := fa_rx_flex_pkg.flex_sql(140,'CAT#', cat_flex_struct,'CAT',
612 'SELECT', h_tax_asset_type_segment);
613
614 IF (g_print_debug) THEN
615 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Tax Asset Type Category Segment: '||l_parm_minor);
616 END IF;
617
618 /* Get Location Segment */
619 l_parm_state := fa_rx_flex_pkg.flex_sql(140,'LOC#', loc_flex_struct,'LOC',
620 'SELECT', 'LOC_STATE');
621
622 IF (g_print_debug) THEN
623 fa_rx_util_pkg.debug('fadptx_insert: ' || 'State Segment: '||l_parm_state);
624 END IF;
625
626 /*============================================================================
627 Dynamic SQL valiable set
628
629 The followings are Dynamic SQL.
630 This query diagram image is,
631
632 +------------------+
633 +--------|FA_LOCATIONS(LOC) |
634 | +------------------+
635 | |
636 | |
637 | +----------------------------+
638 | |FA_DISTRIBUTION_HISTORY(DH) |----|
642 | | +-----------------------------+
639 | +----------------------------+ |
640 | | +-----------------------------+
641 | |-|FA_TRANSACTION_HEADERS (THD1)|
643 | |
644 | | +-----------------------------+
645 | |-|FA_TRANSACTION_HEADERS (THD2)|
646 | +-----------------------------+
647 |
648 | +-----------------+ +----------------------+
649 |--------------|FA_ADDITIONS (AD)|----|FA_ASSET_HISTORY (AH) |
650 | +-----------------+ +----------------------+
651 | | |
652 +--------------+ | | +--------------------+
653 |TEMP VIEW (SM)| | |-|FA_CATEGORIES (CAT) |
654 +--------------+ | | +--------------------+
655 | | |
656 | | |
657 | | | +-----------------------------+
658 | | |-|FA_TRANSACTION_HEADERS (THA1)|
659 | | | +-----------------------------+
660 | | |
661 | | | +-----------------------------+
662 | | |-|FA_TRANSACTION_HEADERS (THA2)|
663 | | +-----------------------------+
664 | +-------------+ +-----------------------------+
665 |--------------|FA_BOOKS(BK) |------------|FA_METHODS (MTH) |
666 +-------------+ | +-----------------------------+
667 | +-----------------------------+
668 |------|FA_TRANSACTION_HEADERS (THB1)|
669 | +-----------------------------+
670 |
671 | +-----------------------------+
672 |------|FA_TRANSACTION_HEADERS (THB2)|
673 +-----------------------------+
674
675 TMEP VIEW(SM) is not database view.
676 And this is BK.COST and DH.UNITS_ASSIGNED
677 group by asset_id, book_type_code, state.
678 =============================================================================*/
679 l_select_sm :=
680 'Select
681 AD.ASSET_ID ASSET_ID,
682 BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
683 BK.COST SUM_COST,
684 '||l_parm_state||' STATE,
685 SUM(DH.UNITS_ASSIGNED) SUM_UNITS_ASSIGNED ';
686
687 l_select_base :=
688 'Select
689 AD.ASSET_ID ASSET_ID,
690 AD.ASSET_NUMBER ASSET_NUMBER,
691 AD.DESCRIPTION ASSET_DESCRIPTION,
692 AD.NEW_USED NEW_USED,
693 BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
694 '||l_parm_minor||' MINOR_CATEGORY,
695 substr('||l_parm_minor||',1,1) TAX_ASSET_TYPE,
696 SM.STATE STATE,
697 AH.UNITS UNITS,
698 SM.SUM_UNITS_ASSIGNED UNITS_ASSIGNED,
699 SM.SUM_COST COST,
700 BK.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
701 decode(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
702 ''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
703 ''E'',''nls_calendar=''''Japanese Imperial''''''),
704 ''M'',''1'',''T'',''2'',''S'',''3'',''H'',''4'',''0'') ERA_NAME_NUM,
705 to_number(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
706 ''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
707 ''YY'',''nls_calendar=''''Japanese Imperial'''''')) ADD_ERA_YEAR,
708 decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
709 ''01-01'',to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))-1,
710 to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))) ADD_YEAR,
711 decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
712 ''01-01'',12,
713 to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''MM''))) ADD_MONTH,
714 to_number(translate(MTH.METHOD_CODE,''JP-DBYR'','' '')) LIFE,
715 BK.ADJUSTED_RATE ADJUSTED_RATE ';
716
717 l_select_base_ba2 :=
718 'Select
719 AD.ASSET_ID ASSET_ID,
720 AD.ASSET_NUMBER ASSET_NUMBER,
721 AD.DESCRIPTION ASSET_DESCRIPTION,
722 AD.NEW_USED NEW_USED,
723 BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
724 decode(NBV.MINOR_CATEGORY,NULL,decode(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1),'||l_parm_minor||',
725 nvl(NBV.TAX_ASSET_TYPE,'||l_parm_minor||'))
726 ,NBV.MINOR_CATEGORY) MINOR_CATEGORY,
727 decode(NBV.MINOR_CATEGORY,NULL,nvl(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1)),
728 substr(NBV.MINOR_CATEGORY,1,1)) TAX_ASSET_TYPE,
729 decode(NBV.ASSET_ID,NULL,SM.STATE,NBV.STATE) STATE,
730 AH.UNITS UNITS,
731 decode(NBV.ASSET_ID,NULL,SM.SUM_UNITS_ASSIGNED,
732 NBV.UNITS_ASSIGNED) UNITS_ASSIGNED,
733 decode(NBV.ASSET_ID,NULL,SM.SUM_COST,
734 decode(nvl(BK.COST,0),0,:p_sum_nbvs_cost,SM.SUM_COST)) COST,
735 BK.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
736 decode(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
737 ''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
738 ''E'',''nls_calendar=''''Japanese Imperial''''''),
739 ''M'',''1'',''T'',''2'',''S'',''3'',''H'',''4'',''0'') ERA_NAME_NUM,
740 to_number(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
741 ''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
742 ''YY'',''nls_calendar=''''Japanese Imperial'''''')) ADD_ERA_YEAR,
743 decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
744 ''01-01'',to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))-1,
745 to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))) ADD_YEAR,
746 decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
747 ''01-01'',12,
748 to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''MM''))) ADD_MONTH,
754 -- BA1:end date base table, BA2: start date base table
749 to_number(translate(MTH.METHOD_CODE,''JP-DBYR'','' '')) LIFE,
750 BK.ADJUSTED_RATE ADJUSTED_RATE,
751 NBV.ACTION_FLAG ACTION_FLAG ';
752
753 -- For both start and end date select statement
755
756 l_select_both :=
757 'Select BA1.ASSET_ID ASSET_ID,
758 BA1.ASSET_NUMBER ASSET_NUMBER,
759 BA1.ASSET_DESCRIPTION ASSET_DESCRIPTION,
760 BA1.NEW_USED NEW_USED,
761 BA1.BOOK_TYPE_CODE BOOK_TYPE_CODE,
762 BA1.MINOR_CATEGORY MINOR_CATEGORY,
763 BA1.TAX_ASSET_TYPE TAX_ASSET_TYPE,
764 BA1.STATE STATE,
765 decode(NBV1.ASSET_ID,NULL,0,BA2.UNITS) START_UNITS,
766 BA1.UNITS END_UNITS,
767 decode(NBV1.ASSET_ID,NULL,0,BA2.UNITS_ASSIGNED) START_UNITS_ASSIGNED,
768 BA1.UNITS_ASSIGNED END_UNITS_ASSIGNED,
769 decode(NBV1.ASSET_ID,NULL,0,BA2.COST) START_COST,
770 BA1.COST END_COST,
771 BA1.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
772 BA1.ERA_NAME_NUM ERA_NAME_NUM,
773 BA1.ADD_ERA_YEAR ADD_ERA_YEAR,
774 BA1.ADD_YEAR ADD_YEAR,
775 BA1.ADD_MONTH ADD_MONTH,
776 BA2.LIFE START_LIFE,
777 BA1.LIFE END_LIFE,
778 BA1.ADJUSTED_RATE ADJUSTED_RATE,
779 abs(BA1.UNITS_ASSIGNED - BA2.UNITS_ASSIGNED) ABS_UNITS'; -- Added for bug#2629893
780
781 l_select_end :=
782 'Select BA1.ASSET_ID ASSET_ID,
783 BA1.ASSET_NUMBER ASSET_NUMBER,
784 BA1.ASSET_DESCRIPTION ASSET_DESCRIPTION,
785 BA1.NEW_USED NEW_USED,
786 BA1.BOOK_TYPE_CODE BOOK_TYPE_CODE,
787 BA1.MINOR_CATEGORY MINOR_CATEGORY,
788 BA1.TAX_ASSET_TYPE TAX_ASSET_TYPE,
789 BA1.STATE STATE,
790 nvl(BA2.UNITS,0) START_UNITS,
791 nvl(BA1.UNITS,0) END_UNITS,
792 nvl(BA2.UNITS_ASSIGNED,0) START_UNITS_ASSIGNED,
793 nvl(BA1.UNITS_ASSIGNED,0) END_UNITS_ASSIGNED,
794 nvl(BA2.COST,0) START_COST,
795 nvl(BA1.COST,0) END_COST,
796 BA1.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
797 BA1.ERA_NAME_NUM ERA_NAME_NUM,
798 BA1.ADD_ERA_YEAR ADD_ERA_YEAR,
799 BA1.ADD_YEAR ADD_YEAR,
800 BA1.ADD_MONTH ADD_MONTH,
801 nvl(BA2.LIFE,to_number(null)) START_LIFE,
802 nvl(BA1.LIFE,to_number(null)) END_LIFE,
803 BA1.ADJUSTED_RATE ADJUSTED_RATE,
804 abs(nvl(BA1.UNITS_ASSIGNED,0) - nvl(BA2.UNITS_ASSIGNED,0)) ABS_UNITS,
805 BA2.ACTION_FLAG ACTION_FLAG
806 ';
807
808 l_select_start :=
809 'Select decode(BA1.ASSET_ID,NULL,BA2.ASSET_ID,
810 BA1.ASSET_ID) ASSET_ID,
811 decode(BA1.ASSET_ID,NULL,BA2.ASSET_NUMBER,
812 BA1.ASSET_NUMBER) ASSET_NUMBER,
813 decode(BA1.ASSET_ID,NULL,BA2.ASSET_DESCRIPTION,
814 BA1.ASSET_DESCRIPTION) ASSET_DESCRIPTION,
815 decode(BA1.ASSET_ID,NULL,BA2.NEW_USED,
816 BA1.NEW_USED) NEW_USED,
817 decode(BA1.ASSET_ID,NULL,BA2.BOOK_TYPE_CODE,
818 BA1.BOOK_TYPE_CODE) BOOK_TYPE_CODE,
819 decode(BA1.ASSET_ID,NULL,BA2.MINOR_CATEGORY,
820 BA1.MINOR_CATEGORY) MINOR_CATEGORY,
821 decode(BA1.ASSET_ID,NULL,BA2.TAX_ASSET_TYPE,
822 BA1.TAX_ASSET_TYPE) TAX_ASSET_TYPE,
823 decode(BA1.ASSET_ID,NULL,BA2.STATE,BA1.STATE) STATE,
824 nvl(BA2.UNITS,0) START_UNITS,
825 nvl(BA1.UNITS,0) END_UNITS,
826 nvl(BA2.UNITS_ASSIGNED,0) START_UNITS_ASSIGNED,
827 nvl(BA1.UNITS_ASSIGNED,0) END_UNITS_ASSIGNED,
828 nvl(BA2.COST,0) START_COST,
829 nvl(BA1.COST,0) END_COST,
830 decode(BA1.ASSET_ID,NULL,BA2.DATE_PLACED_IN_SERVICE,
831 BA1.DATE_PLACED_IN_SERVICE) DATE_PLACED_IN_SERVICE,
832 decode(BA1.ASSET_ID,NULL,BA2.ERA_NAME_NUM,
833 BA1.ERA_NAME_NUM) ERA_NAME_NUM,
834 decode(BA1.ASSET_ID,NULL,BA2.ADD_ERA_YEAR,
835 BA1.ADD_ERA_YEAR) ADD_ERA_YEAR,
839 BA1.ADD_MONTH) ADD_MONTH,
836 decode(BA1.ASSET_ID,NULL,BA2.ADD_YEAR,
837 BA1.ADD_YEAR) ADD_YEAR,
838 decode(BA1.ASSET_ID,NULL,BA2.ADD_MONTH,
840 nvl(BA2.LIFE,to_number(null)) START_LIFE,
841 nvl(BA1.LIFE,to_number(null)) END_LIFE,
842 decode(BA1.ASSET_ID,NULL,BA2.ADJUSTED_RATE,
843 BA1.ADJUSTED_RATE) ADJUSTED_RATE,
844 abs(nvl(BA1.UNITS_ASSIGNED,0) - nvl(BA2.UNITS_ASSIGNED,0)) ABS_UNITS,
845 BA2.ACTION_FLAG ACTION_FLAG
846 ';
847
848 l_from_stmt :=
849 ' From
850 FA_ADDITIONS AD,
851 FA_BOOKS BK,
852 FA_DISTRIBUTION_HISTORY DH,
853 FA_ASSET_HISTORY AH,
854 FA_TRANSACTION_HEADERS THA1,
855 FA_TRANSACTION_HEADERS THA2,
856 FA_TRANSACTION_HEADERS THB1,
857 FA_TRANSACTION_HEADERS THB2,
858 FA_TRANSACTION_HEADERS THD1,
859 FA_TRANSACTION_HEADERS THD2,
860 FA_CATEGORIES CAT,
861 FA_LOCATIONS LOC,
862 FA_METHODS MTH
863 ';
864
865 l_from_stmt_ba2 :=
866 ' , FA_DEPRN_TAX_REP_NBVS NBV
867 ';
868 -- bug 12401201 added not exist condition below so that any transaction which becomes effective before target
869 -- and ineffective before target date is not picked
870
871 l_sm_where :=
872 ' where
873 AD.ASSET_ID = BK.ASSET_ID
874 and AD.ASSET_ID = DH.ASSET_ID
875 and AD.ASSET_ID = AH.ASSET_ID
876 and DH.BOOK_TYPE_CODE = :p_corp_book
877 and BK.DEPRN_METHOD_CODE = MTH.METHOD_CODE
878 and AH.CATEGORY_ID = CAT.CATEGORY_ID
879 and DH.LOCATION_ID = LOC.LOCATION_ID
880 and AH.transaction_header_id_in =THA1.transaction_header_id
881 and AH.transaction_header_id_out=THA2.transaction_header_id(+)
885 and DH.transaction_header_id_out=THD2.transaction_header_id(+)
882 and BK.transaction_header_id_in =THB1.transaction_header_id
883 and BK.transaction_header_id_out=THB2.transaction_header_id(+)
884 and DH.transaction_header_id_in =THD1.transaction_header_id
886 and AH.ASSET_TYPE <> ''EXPENSED''
887 and NOT EXISTS ( SELECT ''next_trx_ineffective''
888 FROM FA_DISTRIBUTION_HISTORY DH1,
889 FA_TRANSACTION_HEADERS THD3
890 WHERE DH1.BOOK_TYPE_CODE = :p_corp_book
891 and DH1.ASSET_ID = DH.ASSET_ID
892 and DH1.transaction_header_id_in=NVL (DH.transaction_header_id_out ,-111)
893 and NVL (DH1.transaction_header_id_out, -111) = THD3.transaction_header_id
894 and THD3.TRANSACTION_DATE_ENTERED <= :p_target_date )
895 and AD.ASSET_ID = :p_asset_id
896 and BK.BOOK_TYPE_CODE = :p_book
897 and THA1.TRANSACTION_DATE_ENTERED <= :p_target_date
898 and nvl(THA2.TRANSACTION_DATE_ENTERED,:p_target_date+1) > :p_target_date
899 and THB1.TRANSACTION_DATE_ENTERED <= :p_target_date
900 and nvl(THB2.TRANSACTION_DATE_ENTERED,:p_target_date+1) > :p_target_date
901 and THD1.TRANSACTION_DATE_ENTERED <= :p_target_date
902 and nvl(THD2.TRANSACTION_DATE_ENTERED,:p_target_date+1) > :p_target_date
903 and MTH.METHOD_CODE like :p_method_code
904 and MTH.RATE_SOURCE_RULE =''FLAT''
905 and MTH.CREATED_BY = 1 -- Added to avoid customized method use
906 and not (BK.period_counter_fully_retired is not null and BK.COST=0)
907 and '||l_parm_minor||' between :p_category_from and :p_category_to
908 AND bk.date_effective = :p_target_bk_date_effective
909 AND bk.transaction_header_id_in = :p_target_bk_thid_in -- Bug3859151
910 ';
911
912 -- bug 13498760. Added check for category below.
913
914 l_where_ba2 :=
915 ' and NBV.BOOK_TYPE_CODE(+) = :p_book
916 and NBV.ASSET_ID(+) = :p_asset_id
917 and NBV.YEAR(+) = :p_year - 1
918 and '||l_parm_state||' = NBV.STATE(+)
919 and nvl(NBV.MINOR_CATEGORY,'||l_parm_minor||') between :p_category_from and :p_category_to
920 ';
921
922 --------------------------------------------------------------------------------
923 -- Following parts are added to restruct the logic for Deprn Asset Tax report --
924 --------------------------------------------------------------------------------
925 l_select_nbv :=
926 'Select
927 AD.ASSET_ID ASSET_ID,
928 AD.ASSET_NUMBER ASSET_NUMBER,
929 AD.DESCRIPTION ASSET_DESCRIPTION,
930 AD.NEW_USED NEW_USED,
931 BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
932 decode(NBV.MINOR_CATEGORY,NULL,decode(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1),'||l_parm_minor||',
933 nvl(NBV.TAX_ASSET_TYPE,'||l_parm_minor||'))
937 NBV.STATE STATE,
934 ,NBV.MINOR_CATEGORY) MINOR_CATEGORY,
935 decode(NBV.MINOR_CATEGORY,NULL,nvl(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1)),
936 substr(NBV.MINOR_CATEGORY,1,1)) TAX_ASSET_TYPE,
938 AH.UNITS UNITS,
939 NBV.UNITS_ASSIGNED UNITS_ASSIGNED,
940 decode(nvl(BK.COST,0),0,:p_sum_nbvs_cost,BK.COST) COST, -- Bug3975288 Changed from BK.COST
941 BK.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
942 decode(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
943 ''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
944 ''E'',''nls_calendar=''''Japanese Imperial''''''),
945 ''M'',''1'',''T'',''2'',''S'',''3'',''H'',''4'',''0'') ERA_NAME_NUM,
946 to_number(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
947 ''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
948 ''YY'',''nls_calendar=''''Japanese Imperial'''''')) ADD_ERA_YEAR,
949 decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
950 ''01-01'',to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))-1,
951 to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))) ADD_YEAR,
952 decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
953 ''01-01'',12,
954 to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''MM''))) ADD_MONTH,
955
956 to_number(translate(MTH.METHOD_CODE,''JP-DBYR'','' '')) LIFE,
957 BK.ADJUSTED_RATE ADJUSTED_RATE,
958 NBV.ACTION_FLAG ACTION_FLAG ';
959
960 l_from_nbv :=
961 'From
962 FA_ADDITIONS AD,
963 FA_BOOKS BK,
964 FA_ASSET_HISTORY AH,
965 FA_CATEGORIES CAT,
966 FA_METHODS MTH,
967 FA_DEPRN_TAX_REP_NBVS NBV
968 ';
969
970 l_nbv_where :=
971 ' where
972 AD.ASSET_ID = BK.ASSET_ID
973 and AD.ASSET_ID = AH.ASSET_ID
974 and BK.DEPRN_METHOD_CODE = MTH.METHOD_CODE
975 and AH.CATEGORY_ID = CAT.CATEGORY_ID
976 and AH.ASSET_TYPE <> ''EXPENSED''
977 and AD.ASSET_ID = :p_asset_id
978 and BK.BOOK_TYPE_CODE = :p_book
979 and NBV.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
980 and NBV.ASSET_ID = AD.ASSET_ID
981 and NBV.YEAR = :p_year - 1
982 and MTH.METHOD_CODE like :p_method_code
983 and MTH.RATE_SOURCE_RULE =''FLAT''
984 and MTH.CREATED_BY = 1 -- Added to avoid customized method use
985 and not (BK.period_counter_fully_retired is not null and BK.COST=0)
986 and nvl(NBV.MINOR_CATEGORY,'||l_parm_minor||') between :p_category_from and :p_category_to
987 AND bk.date_effective = :p_prior_bk_date_effective
988 AND bk.transaction_header_id_in = :p_prior_bk_thid_in -- Bug3859151
989 AND ah.date_effective = :p_prior_ah_date_effective
990 AND ah.transaction_header_id_in = :p_prior_ah_thid_in -- Bug3859151
991 ';
992
993 ------------------------------
994 -- End of Restructure Logic --
995 ------------------------------
996
997 l_group_by :=' group by AD.ASSET_ID,BK.BOOK_TYPE_CODE,BK.COST, '||l_parm_state;
998
999 /* set temporary view SM as variable */
1000
1001 l_parm_view :='('||l_select_sm||l_from_stmt||l_sm_where||l_group_by||') ';
1002
1003 l_base_where := l_sm_where
1004 ||'and SM.ASSET_ID = AD.ASSET_ID
1005 and SM.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
1006 and '||l_parm_state||' = SM.STATE'
1007 ;
1008
1009
1010 --
1011
1012 /*===========================================================================
1013 Set Select Statement:
1014 This query is at target date.
1015 ===========================================================================*/
1016
1020 sql_base_ba2 := l_select_base_ba2||l_base_from||l_from_stmt_ba2||l_base_where||l_where_ba2;
1017 l_base_from := l_from_stmt||','||l_parm_view||' SM ';
1018
1019 sql_base := l_select_base||l_base_from||l_base_where;
1021 sql_nbv := l_select_nbv||l_from_nbv||l_nbv_where;
1022
1023 Loop
1024
1025 -- l_main(k) := substrb(fa_deprn_tax_rep_pkg.debug( -- bug#2434220
1026 l_main(k) := fa_deprn_tax_rep_pkg.debug(substrb(
1027 l_select_end||' from ('||sql_base||') BA1,
1028 ('||replace(sql_base_ba2,':p_target_',':p_prior_')||'
1029 UNION '||sql_nbv||') BA2
1030 where BA1.ASSET_ID=BA2.ASSET_ID (+)
1031 and BA1.BOOK_TYPE_CODE = BA2.BOOK_TYPE_CODE (+)
1032 and BA1.TAX_ASSET_TYPE = BA2.TAX_ASSET_TYPE (+)
1033 and BA1.STATE = BA2.STATE (+)
1034 and not (BA1.COST=0 AND nvl(BA2.COST,-1)=0)
1035 union '||l_select_start||' from ('||sql_base||') BA1,
1036 ('||replace(sql_base_ba2,':p_target_',':p_prior_')||'
1037 UNION '||sql_nbv||') BA2
1038 where BA1.ASSET_ID (+)=BA2.ASSET_ID
1039 and BA1.BOOK_TYPE_CODE (+)= BA2.BOOK_TYPE_CODE
1040 and BA1.TAX_ASSET_TYPE (+)= BA2.TAX_ASSET_TYPE
1041 and BA1.STATE (+)= BA2.STATE
1042 and not (nvl(BA1.COST,-1)=0 AND BA2.COST=0)
1043 order by ASSET_ID, BOOK_TYPE_CODE,ABS_UNITS,STATE' -- Added ABS_UNITS for bug#2629893
1044 -- ,k),256*k+1,256); -- bug#2434220
1045 ,256*k+1,256),k);
1046
1047 if l_main(k) is null then
1048 EXIT;
1049 end if;
1050 k := k+1;
1051 End Loop;
1052
1053 fa_rx_util_pkg.debug('debug: ***** Main SQL: *******');
1054
1055 /* Bug3896299 - Performance Fix */
1056 /* Changed sub_sql to check the state range */
1057 l_sub_sql :=
1058 'Select distinct
1059 BK.ASSET_ID
1060 From FA_BOOKS BK,
1061 FA_ADDITIONS AD,
1062 FA_DISTRIBUTION_HISTORY DH,
1063 FA_ASSET_HISTORY AH,
1064 FA_LOCATIONS LOC,
1065 FA_CATEGORIES CAT,
1066 FA_METHODS MTH,
1067 FA_TRANSACTION_HEADERS TH1,
1068 FA_TRANSACTION_HEADERS TH2,
1069 FA_TRANSACTION_HEADERS TH_DH1,
1070 FA_TRANSACTION_HEADERS TH_DH2,
1071 FA_TRANSACTION_HEADERS TH_AH1,
1072 FA_TRANSACTION_HEADERS TH_AH2
1073 Where AD.ASSET_ID = BK.ASSET_ID
1074 and BK.BOOK_TYPE_CODE =:p_book
1075 and AD.ASSET_TYPE <> ''EXPENSED''
1076 and AD.ASSET_ID = DH.ASSET_ID
1077 and DH.BOOK_TYPE_CODE = :p_corp_book
1078 and DH.LOCATION_ID = LOC.LOCATION_ID
1079 and '||l_parm_state||' between :p_state_from and :p_state_to
1080 and DH.TRANSACTION_HEADER_ID_IN = TH_DH1.TRANSACTION_HEADER_ID
1081 and DH.TRANSACTION_HEADER_ID_OUT = TH_DH2.TRANSACTION_HEADER_ID(+)
1082 and AD.ASSET_ID = AH.ASSET_ID
1083 and AH.CATEGORY_ID = CAT.CATEGORY_ID
1084 and AH.TRANSACTION_HEADER_ID_IN = TH_AH1.TRANSACTION_HEADER_ID
1085 and AH.TRANSACTION_HEADER_ID_OUT = TH_AH2.TRANSACTION_HEADER_ID(+)
1086 and '||l_parm_minor||' between :p_category_from and :p_category_to
1087 and BK.DEPRN_METHOD_CODE = MTH.METHOD_CODE
1088 and BK.TRANSACTION_HEADER_ID_IN =TH1.TRANSACTION_HEADER_ID
1089 and BK.TRANSACTION_HEADER_ID_OUT=TH2.TRANSACTION_HEADER_ID(+)
1090 and MTH.METHOD_CODE like :p_method_code
1091 and MTH.RATE_SOURCE_RULE =''FLAT''
1092 and MTH.CREATED_BY = 1 -- Added to avoid customized method use
1093 Having min(TH1.TRANSACTION_DATE_ENTERED) <= :p_target_date
1094 and max(nvl(TH2.TRANSACTION_DATE_ENTERED,:p_prior_date+1)) > :p_prior_date
1095 and min(TH_DH1.TRANSACTION_DATE_ENTERED) <= :p_target_date
1096 and max(nvl(TH_DH2.TRANSACTION_DATE_ENTERED, :p_prior_date+1)) > :p_prior_date
1097 and min(TH_AH1.TRANSACTION_DATE_ENTERED) <= :p_target_date
1098 and max(nvl(TH_AH2.TRANSACTION_DATE_ENTERED, :p_prior_date+1)) > :p_prior_date
1099 group by BK.ASSET_ID
1100 UNION
1101 select distinct asset_id
1102 from FA_DEPRN_TAX_REP_NBVS NBVS
1103 where NBVS.BOOK_TYPE_CODE = :p_book
1104 and NBVS.STATE between :p_state_from and :p_state_to
1105 and NBVS.YEAR = :p_year - 1
1106 ';
1107 /* Bug3896299 */
1108
1109 IF (g_print_debug) THEN
1110 fa_rx_util_pkg.debug('fadptx_insert: SUB SQL: '|| l_sub_sql);
1111 END IF;
1112
1113
1114
1115 -- Open v_SubCursor
1116 /* v_SubCursor is created for performance issue. the cursor fetch asset_id */
1117
1118 v_SubCursor := DBMS_SQL.OPEN_CURSOR;
1119 IF (g_print_debug) THEN
1120 fa_rx_util_pkg.debug('fadptx_insert: ***** OPEN CURSOR: v_SubCursor *****');
1121 fa_rx_util_pkg.debug('fadptx_insert: v_SubCursor :'|| v_SubCursor);
1122 END IF;
1123
1124 DBMS_SQL.PARSE (v_SubCursor,l_sub_sql,DBMS_SQL.V7);
1125
1126 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_book',h_book);
1127 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_prior_date',h_prior_date);
1128 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_target_date',h_target_date);
1129 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_method_code',h_method_code);
1130 /* Bug3896299 */
1131 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_corp_book',h_corp_book);
1132 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_state_from',state_from);
1133 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_state_to',state_to);
1134 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_category_from',h_category_from);
1135 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_category_to',h_category_to);
1136 DBMS_SQL.BIND_VARIABLE(v_SubCursor,':p_year',h_year);
1137 /* Bug3896299 */
1138
1139 DBMS_SQL.DEFINE_COLUMN(v_SubCursor,1,l_asset_id);
1140
1141 IF (g_print_debug) THEN
1142 fa_rx_util_pkg.debug('fadptx_insert: ***** BIND/DEFINE COLUMN: v_SubCursor *****');
1143 END IF;
1144
1145 v_SubReturn := DBMS_SQL.EXECUTE(v_SubCursor);
1146 IF (g_print_debug) THEN
1147 fa_rx_util_pkg.debug('fadptx_insert: ***** EXECUTE: v_SubCursor *****');
1148 fa_rx_util_pkg.debug('fadptx_insert: v_SubReturn :'|| v_SubReturn);
1149 END IF;
1150
1151 --Open v_MainCursor
1152 /* v_MainCursor is for fetch main data */
1153
1154 v_MainCursor := DBMS_SQL.OPEN_CURSOR;
1155 IF (g_print_debug) THEN
1156 fa_rx_util_pkg.debug('fadptx_insert: ***** OPEN CURSOR: v_MainCursor *****');
1157 fa_rx_util_pkg.debug('fadptx_insert: v_MainCursor :' || v_MainCursor);
1158 END IF;
1162
1159
1160 --DBMS_SQL.PARSE (v_MainCursor, main_sql,DBMS_SQL.V7);
1161 DBMS_SQL.PARSE (v_MainCursor, l_main,0,k,FALSE,DBMS_SQL.V7);
1163 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,1,h_asset_id);
1164 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,2,h_asset_number,15);
1165 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,3,h_asset_desc,80);
1166 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,4,h_new_used,4);
1167 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,5,h_book_type_code,15);
1168 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,6,h_minor_category,150);
1169 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,7,h_tax_asset_type,15); -- Treate UTF8
1170 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,8,h_state,150);
1171 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,9,h_start_units_total);
1172 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,10,h_end_units_total);
1173 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,11,h_start_units_assigned);
1174 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,12,h_end_units_assigned);
1175 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,13,h_start_cost_total);
1176 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,14,h_end_cost_total);
1177 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,15,h_date_in_service);
1178 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,16,h_era_name_num,1);
1179 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,17,h_add_era_year);
1180 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,18,h_add_year);
1181 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,19,h_add_month);
1182 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,20,h_start_life);
1183 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,21,h_end_life);
1184 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,22,h_adjusted_rate);
1185 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,23,h_abs_units);
1186 DBMS_SQL.DEFINE_COLUMN(v_MainCursor,24,h_action_flag, 1);
1187 IF (g_print_debug) THEN
1188 fa_rx_util_pkg.debug('fadptx_insert: ***** DEFINE COLUMN: v_MainCursor *****');
1189 END IF;
1190
1191 --
1192 /* Loop For v_SubCursor */
1193
1194 h_sum_nbvs_asset_id := -1;
1195 Loop
1196 v_SubFetch := DBMS_SQL.FETCH_ROWS(v_SubCursor);
1197 IF (g_print_debug) THEN
1198 fa_rx_util_pkg.debug('fadptx_insert: ***** FETCH LOW: v_SubCursor *****');
1199 fa_rx_util_pkg.debug('fadptx_insert: v_SubFetch :'||v_SubFetch);
1200 END IF;
1201
1202 If v_SubFetch =0 then
1203 Exit;
1204 end if;
1205
1206 DBMS_SQL.COLUMN_VALUE(v_SubCursor,1,l_asset_id);
1207
1208 IF (g_print_debug) THEN
1209 fa_rx_util_pkg.debug('fadptx_insert: l_asset_id : h_sum_nbvs_asset_id'||l_asset_id||':'||h_sum_nbvs_asset_id);
1210 END IF;
1211
1212 -- Bug#3327616
1213 -- Added max query instead of doing it in main SQL
1214 open c_bk_max_date_effective(l_asset_id,h_book,h_prior_date);
1215 fetch c_bk_max_date_effective into h_prior_bk_date_effective;
1216 close c_bk_max_date_effective;
1217 if h_prior_bk_date_effective is null then
1218 h_prior_bk_date_effective := to_date(null);
1219 h_prior_bk_thid_in := to_number(null); -- BUg3859151
1220 else -- Bug3859151
1221 open c_bk_max_thid_in(l_asset_id,h_book,h_prior_bk_date_effective); -- Bug3859151
1222 fetch c_bk_max_thid_in into h_prior_bk_thid_in; -- Bug3859151
1223 close c_bk_max_thid_in; -- Bug3859151
1224 end if;
1225
1226 open c_ah_max_date_effective(l_asset_id,h_prior_date);
1227 fetch c_ah_max_date_effective into h_prior_ah_date_effective;
1228 close c_ah_max_date_effective;
1229 if h_prior_ah_date_effective is null then
1230 h_prior_ah_date_effective := to_date(null);
1231 h_prior_ah_thid_in := to_number(null); -- Bug3859151
1232 else -- Bug3859151
1233 open c_ah_max_thid_in(l_asset_id,h_prior_ah_date_effective); -- Bug3859151
1234 fetch c_ah_max_thid_in into h_prior_ah_thid_in; -- Bug3859151
1235 close c_ah_max_thid_in; -- Bug3859151
1236 end if;
1237
1238 open c_bk_max_date_effective(l_asset_id,h_book,h_target_date);
1239 fetch c_bk_max_date_effective into h_target_bk_date_effective;
1240 close c_bk_max_date_effective;
1241 if h_target_bk_date_effective is null then
1242 h_target_bk_date_effective := to_date(null);
1243 h_target_bk_thid_in := to_number(null); -- Bug3859151
1244 else -- Bug3859151
1245 open c_bk_max_thid_in(l_asset_id,h_book,h_target_bk_date_effective); -- Bug3859151
1246 fetch c_bk_max_thid_in into h_target_bk_thid_in; -- Bug3859151
1247 close c_bk_max_thid_in; -- Bug3859151
1248 end if;
1249
1250 if nvl(h_sum_nbvs_asset_id,-1) <> l_asset_id then
1251 open c_sum_nbvs_cost(l_asset_id,h_book,h_year-1);
1252 fetch c_sum_nbvs_cost into h_sum_nbvs_cost, h_sum_nbvs_asset_id;
1253 close c_sum_nbvs_cost;
1254 if h_sum_nbvs_cost is null then
1255 h_sum_nbvs_cost := 0;
1256 end if;
1257 end if;
1258
1259 IF (g_print_debug) THEN
1260 fa_rx_util_pkg.debug('fadptx_insert: h_prior_bk_date_effective :'||to_char(h_prior_bk_date_effective,'YYYY-MON-DD HH24:MI:SS'));
1261 fa_rx_util_pkg.debug('fadptx_insert: h_prior_ah_date_effective :'||to_char(h_prior_ah_date_effective,'YYYY-MON-DD HH24:MI:SS'));
1262 fa_rx_util_pkg.debug('fadptx_insert: h_target_bk_date_effective :'||to_Char(h_target_bk_date_effective,'YYYY-MON-DD HH24:MI:SS'));
1263 fa_rx_util_pkg.debug('fadptx_insert: h_sum_nbvs_cost :'||h_sum_nbvs_cost);
1264 fa_rx_util_pkg.debug('fadptx_insert: h_prior_bk_thid_in :'||h_prior_bk_thid_in);
1265 fa_rx_util_pkg.debug('fadptx_insert: h_prior_ah_thid_in :'||h_prior_ah_thid_in);
1266 fa_rx_util_pkg.debug('fadptx_insert: h_target_bk_thid_in :'||h_target_bk_thid_in);
1267 END IF;
1268
1269 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_asset_id',l_asset_id);
1270 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_book',h_book);
1271 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_corp_book',h_corp_book);
1272 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_target_date',h_target_date);
1273 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_prior_date', h_prior_date);
1274 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_category_from',h_category_from);
1275 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_category_to',h_category_to);
1276 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_year',h_year);
1277 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_method_code',h_method_code);
1278 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_prior_bk_date_effective',h_prior_bk_date_effective);
1279 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_prior_ah_date_effective',h_prior_ah_date_effective);
1280 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_target_bk_date_effective',h_target_bk_date_effective);
1281 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_sum_nbvs_cost',h_sum_nbvs_cost);
1282 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_prior_bk_thid_in',h_prior_bk_thid_in); -- Bug3859151
1283 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_prior_ah_thid_in',h_prior_ah_thid_in); -- bug3859151
1284 DBMS_SQL.BIND_VARIABLE(v_MainCursor,':p_target_bk_thid_in',h_target_bk_thid_in); -- Bug3859151
1285
1286 v_MainReturn := DBMS_SQL.EXECUTE(v_MainCursor);
1287 IF (g_print_debug) THEN
1288 fa_rx_util_pkg.debug('fadptx_insert: ***** EXECUTE: v_MainCursor *****');
1289 fa_rx_util_pkg.debug('fadptx_insert: v_MainReturn :'||v_MainReturn);
1290 END IF;
1291
1292 /* Loop For v_MainCursor */
1293
1294 -- bug#2629893
1298
1295 -- Initialized dist_asset_id
1296 -- This is moved from just before Sub_Cursor Loop
1297 dist_asset_id :=0;
1299 Loop
1300 -- bug#2448145: Initialized update nbv flag
1301 h_up_nbv_flag := 'N'; -- bug#2661575
1302
1303 v_MainFetch := DBMS_SQL.FETCH_ROWS(v_MainCursor);
1304 IF (g_print_debug) THEN
1305 fa_rx_util_pkg.debug('fadptx_insert: ***** FETCH LOW: v_MainCursor *****');
1306 fa_rx_util_pkg.debug('fadptx_insert: v_MainFetch :'|| v_MainFetch);
1307 END IF;
1308
1309 If v_MainFetch =0 then
1310 Exit;
1311 end if;
1312
1313 DBMS_SQL.COLUMN_VALUE(v_MainCursor,1,h_asset_id);
1314 DBMS_SQL.COLUMN_VALUE(v_MainCursor,2,h_asset_number);
1315 DBMS_SQL.COLUMN_VALUE(v_MainCursor,3,h_asset_desc);
1316 DBMS_SQL.COLUMN_VALUE(v_MainCursor,4,h_new_used);
1317 DBMS_SQL.COLUMN_VALUE(v_MainCursor,5,h_book_type_code);
1318 DBMS_SQL.COLUMN_VALUE(v_MainCursor,6,h_minor_category);
1319 DBMS_SQL.COLUMN_VALUE(v_MainCursor,7,h_tax_asset_type);
1320 DBMS_SQL.COLUMN_VALUE(v_MainCursor,8,h_state);
1321 DBMS_SQL.COLUMN_VALUE(v_MainCursor,9,h_start_units_total);
1322 DBMS_SQL.COLUMN_VALUE(v_MainCursor,10,h_end_units_total);
1323 DBMS_SQL.COLUMN_VALUE(v_MainCursor,11,h_start_units_assigned);
1324 DBMS_SQL.COLUMN_VALUE(v_MainCursor,12,h_end_units_assigned);
1325 DBMS_SQL.COLUMN_VALUE(v_MainCursor,13,h_start_cost_total);
1326 DBMS_SQL.COLUMN_VALUE(v_MainCursor,14,h_end_cost_total);
1327 DBMS_SQL.COLUMN_VALUE(v_MainCursor,15,h_date_in_service);
1328 DBMS_SQL.COLUMN_VALUE(v_MainCursor,16,h_era_name_num);
1329 DBMS_SQL.COLUMN_VALUE(v_MainCursor,17,h_add_era_year);
1330 DBMS_SQL.COLUMN_VALUE(v_MainCursor,18,h_add_year);
1331 DBMS_SQL.COLUMN_VALUE(v_MainCursor,19,h_add_month);
1332 DBMS_SQL.COLUMN_VALUE(v_MainCursor,20,h_start_life);
1333 DBMS_SQL.COLUMN_VALUE(v_MainCursor,21,h_end_life);
1334 DBMS_SQL.COLUMN_VALUE(v_MainCursor,22,h_adjusted_rate);
1335 DBMS_SQL.COLUMN_VALUE(v_MainCursor,23,h_abs_units);
1336 DBMS_SQL.COLUMN_VALUE(v_MainCursor,24,h_action_flag);
1337
1338 /* Get Minor Category Description */
1339 IF (g_print_debug) THEN
1340 fa_rx_util_pkg.debug('fadptx_insert: h_minor_category(tax asset type) '|| h_minor_category);
1341 END If;
1342
1343 if h_minor_category is not null then
1344 h_minor_cat_desc :=
1345 fa_rx_flex_pkg.get_description(
1346 p_application_id => 140,
1347 p_id_flex_code => 'CAT#',
1348 p_id_flex_num => cat_flex_struct,
1349 -- p_qualifier => 'MINOR_CATEGORY',
1350 p_qualifier => h_tax_asset_type_segment, -- Bug#3305764 - Enhancement to make Category Flexfield flexible
1351 p_data => h_minor_category);
1352
1353 IF (g_print_debug) THEN
1354 fa_rx_util_pkg.debug('fadptx_insert: h_minor_category(after get_description) '|| h_minor_category);
1355 END If;
1356 end if;
1357
1358 -- IF (g_print_debug) THEN
1359 fa_rx_util_pkg.debug('fadptx_insert: **** Queried values by MainCursor ****');
1360 fa_rx_util_pkg.debug('fadptx_insert: asset id: ' || h_asset_id);
1361 fa_rx_util_pkg.debug('fadptx_insert: asset number:' || h_asset_number);
1362 fa_rx_util_pkg.debug('fadptx_insert: asset desc:'|| h_asset_desc);
1363 fa_rx_util_pkg.debug('fadptx_insert: new use:'||h_new_used);
1364 fa_rx_util_pkg.debug('fadptx_insert: minor category:'||h_minor_category);
1365 fa_rx_util_pkg.debug('fadptx_insert: tax asset type: '||h_tax_asset_type);
1366 fa_rx_util_pkg.debug('fadptx_insert: state: '||h_state);
1367 fa_rx_util_pkg.debug('fadptx_insert: start total units: '||h_start_units_total);
1368 fa_rx_util_pkg.debug('fadptx_insert: end total units: '||h_end_units_total);
1369 fa_rx_util_pkg.debug('fadptx_insert: start units assigned:'||h_start_units_assigned);
1370 fa_rx_util_pkg.debug('fadptx_insert: end units assigned: '||h_end_units_assigned);
1371 fa_rx_util_pkg.debug('fadptx_insert: start total cost:'||h_start_cost_total);
1372 fa_rx_util_pkg.debug('fadptx_insert: end total cost: '||h_end_cost_total);
1373 fa_rx_util_pkg.debug('fadptx_insert: date in service:' ||h_date_in_service);
1374 fa_rx_util_pkg.debug('fadptx_insert: era name num: '||h_era_name_num);
1375 fa_rx_util_pkg.debug('fadptx_insert: add era year: '||h_add_era_year);
1376 fa_rx_util_pkg.debug('fadptx_insert: add year: '||h_add_year);
1377 fa_rx_util_pkg.debug('fadptx_insert: add month: '||h_add_month);
1378 fa_rx_util_pkg.debug('fadptx_insert: start life: '||h_start_life);
1379 fa_rx_util_pkg.debug('fadptx_insert: end life: '||h_end_life);
1380 fa_rx_util_pkg.debug('fadptx_insert: adjusted rate: '||h_adjusted_rate);
1381 fa_rx_util_pkg.debug('fadptx_insert: h_abs_units: '||h_abs_units);
1382 fa_rx_util_pkg.debug('fadptx_insert: action flag: '||h_action_flag);
1386 /* Get theoretical and evaluated nbv,residual rate */
1383 fa_rx_util_pkg.debug('fadptx_insert: **** End of queried values list for '||h_asset_id||' ****');
1384 -- END IF;
1385
1387
1388 /* following logic must be run only once for each asset */
1389 if dist_asset_id <> h_asset_id then
1390
1391 -- Store processed asset_id
1392 dist_asset_id := h_asset_id;
1393
1394 -- Check the values of theoretical nbv and evaluated nbv on all states
1395 select count(*),count(theoretical_nbv),count(evaluated_nbv)
1396 into l_chk_nbv_total,l_chk_theoretical_nbv,l_chk_evaluated_nbv
1397 from FA_DEPRN_TAX_REP_NBVS
1398 where asset_id = l_asset_id
1399 and book_type_code = h_book
1400 and year = h_year -1;
1401
1402 if nvl(l_chk_theoretical_nbv,0) <> nvl(l_chk_evaluated_nbv,0) then
1403 RAISE BOTH_NBV_ERROR;
1404 elsif (nvl(l_chk_theoretical_nbv,0) <> 0 and nvl(l_chk_nbv_total,0) <> nvl(l_chk_theoretical_nbv,0)) then
1405 RAISE BOTH_NBV_ERROR;
1406 end if; -- End of check the values of theoretical nbv and evaluated nbv
1407
1408 -- Delete the data parameter's year on FA_DEPRN_TAX_REP_NBVS to refresh data
1409 delete from FA_DEPRN_TAX_REP_NBVS
1410 where asset_id = l_asset_id
1411 and book_type_code = h_book
1412 and year = h_year;
1413
1414 -- Fetch theoretical and evaluated NBV total
1415 -- Initialization
1416 dist_total_units := null;
1417 dist_total_cost := null;
1418 l_total_cost := null;
1419 l_total_prior_cost := null;
1420 l_total_units :=null;
1421 l_total_prior_units := null;
1422
1423 open c_total_units_cost(h_book, l_asset_id, h_target_date);
1424 fetch c_total_units_cost into l_total_units, l_total_cost;
1425 close c_total_units_cost;
1426
1427 open c_total_units_cost(h_book, l_asset_id, h_prior_date);
1428 fetch c_total_units_cost into l_total_prior_units, l_total_prior_cost;
1429 close c_total_units_cost;
1430
1431 /*
1432 -- Set asset cost and Units from FA_BOOKS at prior date
1433 l_total_prior_cost := h_prior_total_cost;
1434 l_total_prior_units := h_prior_total_units;
1435
1436 -- Set asset cost from FA_BOOKS at target date
1437 l_total_cost := h_total_cost;
1438 l_total_units := h_total_units;
1439 */
1440
1441 IF (g_print_debug) THEN
1442 fa_rx_util_pkg.debug('fadptx_insert: l_asset_id (before c_last_nbv_total):'||l_asset_id);
1443 fa_rx_util_pkg.debug('fadptx_insert: h_book:'||h_book);
1444 fa_rx_util_pkg.debug('fadptx_insert: h_year:'||h_year);
1445 END IF;
1446 --
1447 open c_last_nbv_total(l_asset_id,h_book,h_year);
1448 fetch c_last_nbv_total into dist_total_cost, -- bug#2661575
1449 dist_total_evaluated_nbv, dist_total_theoretical_nbv,
1450 dist_last_total_units,
1451 dist_year;
1452
1453 -- Fetch total units at target date
1454 if c_last_nbv_total%FOUND then
1455 if (g_print_debug) then
1456 fa_rx_util_pkg.debug('fadptx_insert: c_last_nbv_total: return with values');
1457 fa_rx_util_pkg.debug('fadptx_insert: dist_last_total_cost(0.0):'||dist_total_cost);
1458 fa_rx_util_pkg.debug('fadptx_insert: dist_last_total_units(0.0):'||dist_last_total_units);
1459 fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv(0.0):'||dist_total_evaluated_nbv);
1460 fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv(0.0):'||dist_total_theoretical_nbv);
1461 fa_rx_util_pkg.debug('fadptx_insert: dist_year(0.0):'||dist_year);
1462 end if;
1463
1464 h_last_nbv_total_flag :='Y';
1465 dist_total_units := l_total_units;
1466
1467 -- Calculate dist_total_evaluated_nbv and dist_total_theoretical_nbv
1468 if dist_total_cost =0 then
1469 dist_total_evaluated_nbv := 0;
1470 dist_total_theoretical_nbv := 0;
1471 else
1472 if dist_total_evaluated_nbv is not null then
1473 dist_total_evaluated_nbv
1474 := round(l_total_cost/dist_total_cost * dist_total_evaluated_nbv,h_precision);
1475 end if;
1476 if dist_total_theoretical_nbv is not null then
1477 dist_total_theoretical_nbv
1478 := round(l_total_cost/dist_total_cost * dist_total_theoretical_nbv,h_precision);
1479 end if;
1480 end if;
1481 else
1482 -- bug#2661575 initialization
1483 h_last_nbv_total_flag :='N';
1484 dist_total_evaluated_nbv := null;
1485 dist_total_theoretical_nbv := null;
1486 dist_year := null;
1487 dist_total_cost := null;
1488 end if;
1489
1490 close c_last_nbv_total;
1491
1492 IF (g_print_debug) THEN
1496 fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv(0):'||dist_total_evaluated_nbv);
1493 fa_rx_util_pkg.debug('fadptx_insert: dist_total_cost(0):'||dist_total_cost);
1494 fa_rx_util_pkg.debug('fadptx_insert: dist_total_units(0):'||dist_total_units);
1495 fa_rx_util_pkg.debug('fadptx_insert: dist_last_total_units(0):'||dist_last_total_units);
1497 fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv(0):'||dist_total_theoretical_nbv);
1498 fa_rx_util_pkg.debug('fadptx_insert: dist_year:'||dist_year);
1499 END IF;
1500
1501 end if; -- if dist_asset_id <> h_asset_id
1502 --
1503 -- Get the latest data from NBV table.
1504
1505 IF (g_print_debug) THEN
1506 fa_rx_util_pkg.debug('fadptx_insert: l_total_prior_cost(0):'||l_total_prior_cost);
1507 fa_rx_util_pkg.debug('fadptx_insert: l_total_prior_units(0):'||l_total_prior_units);
1508 fa_rx_util_pkg.debug('fadptx_insert: l_total_cost(0):'||l_total_cost);
1509 fa_rx_util_pkg.debug('fadptx_insert: l_total_units(0):'||l_total_units);
1510 fa_rx_util_pkg.debug('fadptx_insert: dist_total_units(0.5):'||dist_total_units);
1511 END IF;
1512
1513 -- Initializations
1514 h_up_cost := NULL;
1515 h_up_theoretical_nbv := NULL;
1516 h_up_evaluated_nbv := NULL;
1517 h_up_year := NULL;
1518 h_tmp_units_assigned := NULL;
1519 h_up_last_cost := NULL;
1520 h_up_tax_asset_type := NULL;
1521 h_up_units_assigned := NULL;
1522 h_up_life := NULL;
1523
1524
1525 OPEN c_nbv_update (h_asset_id,h_book,h_state, dist_year);
1526 FETCH c_nbv_update INTO h_up_cost,h_up_theoretical_nbv,
1527 h_up_evaluated_nbv,h_up_year, h_tmp_units_assigned;
1528
1529 IF (g_print_debug) THEN
1530 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_up_theoretical_nbv(0):'||h_up_theoretical_nbv);
1531 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_up_evaluated_nbv(0):'||h_up_evaluated_nbv);
1532 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_up_year(0):'||h_up_year);
1533 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_tmp_units_assigned(0):'||h_tmp_units_assigned);
1534 END IF;
1535
1536 -- bug#2661575: Remove flag logic
1537
1538 -- bug#2629893: Distribute NBVs
1539 if dist_total_units is null then
1540 dist_total_units := h_end_units_total;
1541 end if;
1542
1543 IF (g_print_debug) THEN
1544 fa_rx_util_pkg.debug('fadptx_insert: h_up_evaluated_nbv(0):'||h_up_evaluated_nbv);
1545 fa_rx_util_pkg.debug('fadptx_insert: h_up_year(0):'||h_up_year);
1546 fa_rx_util_pkg.debug('fadptx_insert: h_tmp_units_assigned(0):'||h_tmp_units_assigned);
1547 fa_rx_util_pkg.debug('fadptx_insert: dist_total_units(1):'||dist_total_units);
1548 fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv(1):'||dist_total_theoretical_nbv);
1549 fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv(1):'||dist_total_evaluated_nbv);
1550 END IF;
1551
1552
1553 IF dist_total_theoretical_nbv is not null and dist_total_evaluated_nbv is not null then
1554 IF (nvl(h_tmp_units_assigned,0) <> h_end_units_assigned
1555 or dist_total_cost <> l_total_cost
1556 or dist_last_total_units <> l_total_units)
1557 and dist_year =nvl(h_up_year,dist_year) then
1558 if dist_total_units = 0 then
1559 h_up_theoretical_nbv := 0;
1560 h_up_evaluated_nbv := 0;
1561 else
1562 h_up_theoretical_nbv
1563 := round(dist_total_theoretical_nbv*h_end_units_assigned/dist_total_units,h_precision);
1564 h_up_evaluated_nbv
1565 := round(dist_total_evaluated_nbv *h_end_units_assigned/dist_total_units,h_precision);
1566 end if;
1567 END IF;
1568
1569 dist_total_units := dist_total_units - h_end_units_assigned;
1570
1571 -- bug#2661575 Treated rounding error
1572 if dist_total_units =0 then
1573 h_up_theoretical_nbv := dist_total_theoretical_nbv;
1574 h_up_evaluated_nbv := dist_total_evaluated_nbv;
1575 end if;
1576
1577 dist_total_theoretical_nbv := dist_total_theoretical_nbv - nvl(h_up_theoretical_nbv,0);
1578 dist_total_evaluated_nbv := dist_total_evaluated_nbv - nvl(h_up_evaluated_nbv,0);
1579 h_up_nbv_flag := 'Y'; -- bug#2661575
1580 h_up_year := dist_year; -- bug#2661575
1581 END IF;
1582 -- End of bug#2629893
1583
1584 OPEN c_last_update (h_asset_id,h_book,h_state, h_year);
1585 FETCH c_last_update INTO h_up_last_cost, h_up_tax_asset_type,h_up_units_assigned,h_up_life;
1586
1587
1588 IF c_last_update%found THEN
1589 h_last_up_flag :='Y';
1590 ELSE
1591 h_last_up_flag :='N';
1592 END IF;
1593
1594 CLOSE c_last_update;
1595
1596 IF (g_print_debug) THEN
1597 fa_rx_util_pkg.debug('fadptx_insert: h_up_nbv_flag:'||h_up_nbv_flag);
1598 fa_rx_util_pkg.debug('fadptx_insert: h_last_up_flag:'||h_last_up_flag);
1599 fa_rx_util_pkg.debug('fadptx_insert: h_up_cost:'||h_up_cost);
1600 fa_rx_util_pkg.debug('fadptx_insert: h_up_theoretical_nbv:'||h_up_theoretical_nbv);
1601 fa_rx_util_pkg.debug('fadptx_insert: h_up_evaluated_nbv:'||h_up_evaluated_nbv);
1602 fa_rx_util_pkg.debug('fadptx_insert: h_up_year:'||h_up_year);
1603 fa_rx_util_pkg.debug('fadptx_insert: h_up_last_cost:'||h_up_last_cost);
1604 fa_rx_util_pkg.debug('fadptx_insert: h_up_tax_asset_type:'||h_up_tax_asset_type);
1605 fa_rx_util_pkg.debug('fadptx_insert: h_up_units_assigned:'||h_up_units_assigned);
1606 fa_rx_util_pkg.debug('fadptx_insert: h_up_life:'||h_up_life);
1607 fa_rx_util_pkg.debug('fadptx_insert: dist_total_units:'||dist_total_units);
1608 fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv:'||dist_total_theoretical_nbv);
1609 fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv:'||dist_total_evaluated_nbv);
1610 END IF;
1611
1612
1613 -- check h_up_theoretical_nbv and h_up_evaluated_nbv
1614 IF (h_up_theoretical_nbv IS NULL AND h_up_evaluated_nbv IS NOT NULL)
1615 OR (h_up_theoretical_nbv IS NOT NULL AND h_up_evaluated_nbv IS NULL)
1616 THEN
1617 RAISE BOTH_NBV_ERROR;
1618 END IF;
1619
1620 /* Start Cost distribute */ -- bug#2629893 Moved this logic to here
1621
1622 if h_start_cost_total =0 then
1623 h_start_cost :=0;
1624 h_start_units_assigned :=0;
1625 else
1626 if h_start_asset_id <> h_asset_id then
1627 h_start_units_accm :=0;
1628 h_start_cost_accm :=0;
1629 h_start_asset_id := h_asset_id;
1630 end if;
1631
1632 IF (g_print_debug) THEN
1633 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_start_asset_id:'||h_start_asset_id);
1634 END IF;
1635
1636 if h_min_acct_unit is not null then
1637
1638 h_start_cost := round((h_start_units_assigned + h_start_units_accm) /
1639 h_start_units_total * h_start_cost_total/h_min_acct_unit
1640 ,h_precision) * h_min_acct_unit- h_start_cost_accm;
1641
1642 else
1643 h_start_cost :=round((h_start_units_assigned + h_start_units_accm) /
1644 h_start_units_total * h_start_cost_total,h_precision) -
1645 h_start_cost_accm;
1646 end if;
1647
1648 h_start_units_accm := h_start_units_accm + h_start_units_assigned;
1649 h_start_cost_accm := h_start_cost_accm + h_start_cost;
1650 end if;
1651
1652 IF (g_print_debug) THEN
1653 fa_rx_util_pkg.debug('fadptx_insert: ' || 'start units accm :'||h_start_units_accm);
1654 fa_rx_util_pkg.debug('fadptx_insert: ' || 'start cost accm :'||h_start_cost_accm);
1655 fa_rx_util_pkg.debug('fadptx_insert: ' || 'start cost: '||h_start_cost);
1656 END IF;
1657
1658 if h_end_cost_total = 0 then
1659 h_end_cost :=0;
1660 h_theoretical_nbv := 0;
1661 h_evaluated_nbv := 0;
1662 h_end_units_assigned :=0;
1663
1664 else
1665
1666 ----------------------------------------------------------------
1667 -- Cost distribute: Prevent rounding error, this logic is used.
1668 ----------------------------------------------------------------
1669
1670 /* End Cost distribute */
1671 if h_end_asset_id <> h_asset_id then
1672 h_end_units_accm:=0;
1673 h_end_cost_accm :=0;
1674 h_end_asset_id := h_asset_id;
1675 end if;
1676
1677 IF (g_print_debug) THEN
1678 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_end_asset_id:'||h_end_asset_id);
1679 END IF;
1680
1681 if h_min_acct_unit is not null then
1682
1683 h_end_cost := round((h_end_units_assigned + h_end_units_accm) /
1684 h_end_units_total * h_end_cost_total/h_min_acct_unit
1685 ,h_precision) * h_min_acct_unit - h_end_cost_accm;
1686
1687 else
1688 h_end_cost := round((h_end_units_assigned + h_end_units_accm) /
1689 h_end_units_total * h_end_cost_total,h_precision) -
1690 h_end_cost_accm;
1691 end if;
1692
1693 -- Treate rounding error for end cost
1694
1695 if nvl(h_tmp_units_assigned,h_start_units_assigned) = h_end_units_assigned
1696 and nvl(dist_total_cost,l_total_prior_cost) = l_total_cost
1697 and nvl(dist_last_total_units,l_total_prior_units) = l_total_units
1698 then
1699 if h_last_up_flag ='Y' then -- If NBVs table has cost, update end cost
1700 h_end_cost := h_up_last_cost;
1701 elsif h_start_cost >0 and h_end_cost >0 then
1702 -- If NBVs table doesn't have cost and start cost and end cost are not 0
1703 h_end_cost := h_start_cost;
1704 end if;
1705 end if;
1706
1707 h_end_units_accm := h_end_units_accm + h_end_units_assigned;
1708 h_end_cost_accm := h_end_cost_accm + h_end_cost;
1709
1710 IF (g_print_debug) THEN
1711 fa_rx_util_pkg.debug('fadptx_insert: ' || 'end units accm:'||h_end_units_accm);
1712 fa_rx_util_pkg.debug('fadptx_insert: ' || 'end cost accm:'||h_end_cost_accm);
1713 fa_rx_util_pkg.debug('fadptx_insert: ' || 'end cost:'||h_end_cost);
1714 END IF;
1715
1716 /*==========================================================================
1717 NBV and Residual Rate Calculation: After half_rate and full_rate,
1718 calculate NBV and Residual Rate. [First year Theoretical NBV]
1719 = [COST]*(1-[adjusted rate]*(13- [addition month])/12
1720 [Fist year Evaluated NBV]=[COST]*[half_rate] [After next year
1721 Theoretical NBV]=[Prior year Theoretical NBV]*[full_rate] [After next
1722 year Evaluated NBV] =[Prior year Evaluated NBV]*[full_rate]
1723 ===========================================================================
1724 */
1725
1726 /* Calculate half and full rate */
1727
1728 --bug4919991: Now user can choose where to use round in middle or not
1729 if not (rounding) then
1730 h_half_rate := 1 - h_adjusted_rate/2;
1731 else
1732 h_half_rate := trunc(1 - h_adjusted_rate/2,3);
1733 end if;
1734
1735 h_full_rate := 1 - h_adjusted_rate;
1736
1737 /* Set parameter for calculation */
1738 h_diff_year := to_number(to_char(h_target_date,'YYYY')) - h_add_year;
1739
1740 IF (g_print_debug) THEN
1741 fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_diff_year:'||h_diff_year);
1742 END IF;
1743
1744 -- Set the variable for starting loop counter
1745 IF h_up_nbv_flag ='Y'
1746 AND (h_up_theoretical_nbv IS NOT NULL
1747 AND h_up_evaluated_nbv IS NOT NULL) THEN
1748 l_start_loop := h_up_year +1 - h_add_year;
1749 ELSE
1750 l_start_loop := 1;
1751 END IF;
1752
1753 IF (g_print_debug) THEN
1754 fa_rx_util_pkg.debug('fadptx_insert: ' || 'l_start_loop:'||l_start_loop);
1755 END IF;
1756
1757 /* Evaluated and Theoretical NBV calculation */
1758 For i in l_start_loop..h_diff_year LOOP
1759
1760 -- Bug#2629893
1761 -- Remove rate history logic for Cost base calculation
1762
1763 /* bug#2433829 -- Supported Rate chenges */
1764
1765 -- Bug#2629893
1766 -- Treate asset transfer
1767
1768 if i=1 then
1769 --bug4919991: Now user can choose where to use round in middle or not
1770 if not (rounding) then
1771 h_theoretical_nbv:= round(h_end_cost * (1-h_adjusted_rate*
1772 (13- h_add_month)/12),h_precision);
1773 else
1774 h_theoretical_nbv:= round(h_end_cost * round(1-h_adjusted_rate*
1775 (13- h_add_month)/12,3),h_precision);
1776 end if;
1777 -- Bug 9145830
1778 if eval_nbv_round = 'DOWN' then
1779 h_evaluated_nbv:= trunc(h_end_cost * h_half_rate,h_precision);
1780 else
1781 h_evaluated_nbv:= round(h_end_cost * h_half_rate,h_precision);
1782 end if;
1783 -- End Bug 9145830
1784 ELSE
1785 IF i=l_start_loop AND h_up_theoretical_nbv IS NOT NULL THEN
1786 h_theoretical_nbv:= round(h_up_theoretical_nbv*h_full_rate,h_precision);
1787 ELSE
1788 h_theoretical_nbv:= round(h_theoretical_nbv*h_full_rate,h_precision);
1789 END IF;
1790
1791 IF i=l_start_loop AND h_up_evaluated_nbv IS NOT NULL THEN
1792 -- Bug 9145830
1793 if eval_nbv_round = 'DOWN' then
1794 h_evaluated_nbv:= trunc(h_up_evaluated_nbv * h_full_rate,h_precision);
1795 else
1796 h_evaluated_nbv:= round(h_up_evaluated_nbv * h_full_rate,h_precision);
1797 end if;
1798 -- End Bug 9145830
1799 ELSE
1800 -- Bug 9145830
1801 if eval_nbv_round = 'DOWN' then
1802 h_evaluated_nbv:= trunc(h_evaluated_nbv * h_full_rate,h_precision);
1803 else
1804 h_evaluated_nbv:= round(h_evaluated_nbv * h_full_rate,h_precision);
1805 end if;
1806 -- End Bug 9145830
1807 END IF;
1808
1809 -- End of bug#2433829 changes --
1810 end if;
1811 end Loop;
1812
1813 /* Mimimun NBV limitation -- bug#1797751 */
1814
1815 if (g_print_debug) then
1816 fa_rx_util_pkg.debug('Test:asset_id:'||h_asset_id);
1817 fa_rx_util_pkg.debug('Test:state:'||h_state);
1818 fa_rx_util_pkg.debug('Test:tax_asset_type:'||h_tax_asset_type);
1819 fa_rx_util_pkg.debug('Test:h_theoretical_nbv:'||h_theoretical_nbv);
1820 fa_rx_util_pkg.debug('Test:h_evaluated_nbv:'||h_evaluated_nbv);
1821 end if;
1822
1823 if h_theoretical_nbv < round(h_end_cost*0.05, h_precision) then
1824 h_theoretical_nbv := round(h_end_cost*0.05, h_precision);
1825 end if;
1826 -- Bug 9145830
1827 if eval_nbv_round = 'DOWN' then
1828 if h_evaluated_nbv < trunc(h_end_cost*0.05, h_precision) then
1829 h_evaluated_nbv := trunc(h_end_cost*0.05, h_precision);
1830 end if;
1831 else
1832 if h_evaluated_nbv < round(h_end_cost*0.05, h_precision) then
1833 h_evaluated_nbv := round(h_end_cost*0.05, h_precision);
1834 end if;
1835 end if;
1836 -- End Bug 9145830
1837
1838
1839 /* Residual rates calculation */
1840
1841 -- The following code is being added as part of Japan tax reforms 2008
1842 if h_year >= 2008 then
1843 h_theoretical_nbv := 0;
1844 end if;
1845 -- End of Addition for of Japan tax reforms 2008
1846 end if;
1847 CLOSE c_nbv_update;
1848
1849 -- Bug: :Design Change
1850 -- Changed as followings
1851 -- At 1st year: Residual rate is h_half_rate
1852 -- After 2nd years: Residula rate is h_full_rate
1853
1854 /* Bug 7422776 changed the formula for calculating h_theoretical_residual_rate for first year*/
1855 if h_diff_year = 1 then -- At 1st year
1856 h_theoretical_residual_rate := (1-h_adjusted_rate*
1857 (13- h_add_month)/12);
1858 if (rounding) then
1859 h_theoretical_residual_rate := trunc(h_theoretical_residual_rate,3);
1860 end if;
1861 h_evaluated_residual_rate := h_half_rate;
1862 else -- After 2 years
1863 h_theoretical_residual_rate := h_full_rate;
1864 h_evaluated_residual_rate := h_full_rate;
1865 end if;
1866
1867 IF (g_print_debug) THEN
1868 fa_rx_util_pkg.debug('fadptx_insert: ' || 'theoretical nbv: '||h_theoretical_nbv);
1869 fa_rx_util_pkg.debug('fadptx_insert: ' || 'evaluated nbv: '||h_evaluated_nbv);
1870 fa_rx_util_pkg.debug('fadptx_insert: ' || 'theoretical residual rate: '||h_theoretical_residual_rate);
1871 fa_rx_util_pkg.debug('fadptx_insert: ' || 'evaluated residual rate: '||h_evaluated_residual_rate);
1872 END IF;
1873
1874
1875 /* Set Taxable Cost */
1876
1877 h_theoretical_taxable_cost := h_theoretical_nbv;
1878 h_evaluated_taxable_cost := h_evaluated_nbv;
1879
1880 -- Set the values of nbv table
1881 IF h_last_up_flag='Y' THEN
1882 -- bug#2629893 : Treate tax asset type changing
1883 if not h_tax_asset_type <> Nvl(substr(h_up_tax_asset_type,1,1),h_start_asset_type)
1884 then
1885 h_start_cost := h_up_last_cost;
1886 h_start_asset_type :=Nvl(substr(h_up_tax_asset_type,1,1),h_start_asset_type);
1887 h_start_units_assigned := Nvl(h_up_units_assigned,h_start_units_assigned);
1888 h_start_life := Nvl(h_up_life,h_start_life);
1889 end if;
1890 END IF;
1891
1892 IF (g_print_debug) THEN
1893 fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start cost: '||h_start_cost);
1894 fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start asset type :'||h_start_asset_type);
1895 fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start units assigned :'||h_start_units_assigned);
1896 fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start life :'||h_start_life);
1897 END IF;
1898
1899 /*==========================================================================
1900 Check if current processed state is necessary state or not.
1901 Only if it is Yes, reason code process will be run
1902 ===========================================================================*/
1903 IF (g_print_debug) THEN
1904 fa_rx_util_pkg.debug('fadptx_insert: ' || 'State check h_state:'||h_state);
1905 END IF;
1906
1907 IF h_state >= nvl(state_from,h_state) and h_state <= nvl(state_to,h_state) THEN
1908 h_state_flag := 'Y';
1909 ELSE
1910 h_state_flag := 'N';
1911 END IF;
1912
1916 and reason code.
1913 /*============================================================================
1914 Set INCREASE COST, DECREASE COST and REASON CODE: Compaire start date
1915 information and end date infomation, Set increase cost, decrease cost
1917
1918 Priority of reason: [1]INCREASE
1919 [1-1] NEW ADDITION [1-2] USED ADDITION [1-3] TRANSFER IN [1-4]
1920 OTHER
1921 [2]DECREASE
1922 [2-1] FULL [2-2] PARTIAL
1923
1924 [2-A] SALE RETIREMENT [2-B] RETIREMENT [2-C] TRANSFER OUT
1925 [2-D] OTHER
1926
1927 [3]OTHER
1928 [3-1] CHANGE LIFE [3-2] OTHER
1929
1930 If the asset is not changed for a year, 'All' Report print as 'NEW
1931 ADDITION' or 'USED ADDITION'.
1932
1933 ============================================================================*/
1934
1935 /*==========================================================================
1936 The followings are set flag if addition, transfer, retirement transaction
1937 from prior date to taraget date.
1938 ==========================================================================*/
1939
1940 if not (h_start_cost =0 AND h_end_cost=0) THEN
1941 if h_state_flag = 'Y' then
1942
1943 /* Set flag:Transaction ADDTION */
1944 begin
1945 Select distinct 'Y'
1946 into r_addition_flag
1947 from FA_TRANSACTION_HEADERS
1948 where ASSET_ID = h_asset_id
1949 and BOOK_TYPE_CODE =h_book_type_code
1950 and TRANSACTION_DATE_ENTERED >= h_prior_date
1951 and TRANSACTION_DATE_ENTERED <= h_target_date
1952 and (TRANSACTION_TYPE_CODE ='CIP ADDITION'
1953 or (TRANSACTION_TYPE_CODE ='ADDITION'
1954 and not exists
1955 (Select * from FA_TRANSACTION_HEADERS
1956 where ASSET_ID = h_asset_id
1957 and BOOK_TYPE_CODE =h_book_type_code
1958 and TRANSACTION_DATE_ENTERED < h_prior_date
1959 and TRANSACTION_TYPE_CODE ='CIP ADDITION')));
1960
1961 exception
1962 when NO_DATA_FOUND then r_addition_flag :='N';
1963 end;
1964
1965 /* Set flag:Transaction Retirement */
1966 begin
1967 Select TRANSACTION_HEADER_ID
1968 into r_ret_id
1969 from FA_TRANSACTION_HEADERS
1970 where ASSET_ID = h_asset_id
1971 and BOOK_TYPE_CODE = h_book_type_code
1972 and TRANSACTION_HEADER_ID =
1973 (select max(TRANSACTION_HEADER_ID)
1974 from FA_TRANSACTION_HEADERS
1975 where ASSET_ID = h_asset_id
1976 and BOOK_TYPE_CODE= h_book_type_code
1977 and TRANSACTION_DATE_ENTERED >= h_prior_date
1978 and TRANSACTION_DATE_ENTERED <= h_target_date
1979 and (TRANSACTION_TYPE_CODE ='FULL RETIREMENT'
1980 or TRANSACTION_TYPE_CODE ='PARTIAL RETIREMENT'));
1981
1982 r_ret_flag :='Y';
1983
1984 Exception
1985 when NO_DATA_FOUND then
1986 r_ret_flag :='N';
1987 end;
1988
1989 /* Set flag:Transaction Transfer */
1990 begin
1991 Select decode (to_char(TRANSACTION_DATE_ENTERED,'MM-DD'),
1992 '01-01',to_char(TRANSACTION_DATE_ENTERED -1,'E YY.MM',
1993 'NLS_CALENDAR=''Japanese Imperial'''),
1994 to_char(TRANSACTION_DATE_ENTERED,'E YY.MM',
1995 'NLS_CALENDAR=''Japanese Imperial''')),
1996 TRANSACTION_NAME
1997 into r_transfer_date,
1998 r_trn_transaction_name
1999 from FA_TRANSACTION_HEADERS
2000 where ASSET_ID = h_asset_id
2001 and BOOK_TYPE_CODE = h_corp_book
2002 and TRANSACTION_HEADER_ID =
2003 (select max(TRANSACTION_HEADER_ID)
2004 from FA_TRANSACTION_HEADERS
2005 where ASSET_ID = h_asset_id
2006 and BOOK_TYPE_CODE = h_corp_book
2007 and TRANSACTION_DATE_ENTERED >= h_prior_date
2008 and TRANSACTION_DATE_ENTERED <= h_target_date
2009 and TRANSACTION_TYPE_CODE ='TRANSFER');
2010 --Bug6200581 begins
2011 --Checking if the asset is in the current state or not ( has been transferred).
2012 --Bug13574667: Modified cursor
2013 begin
2014 l_transfer_sql := 'select ''N''
2015 from FA_DISTRIBUTION_HISTORY FDH,
2016 FA_LOCATIONS LOC
2017 where FDH.ASSET_ID = '||h_asset_id ||'
2021 and BOOK_TYPE_CODE = '''||h_corp_book ||'''
2018 and FDH.TRANSACTION_HEADER_ID_in in ( select TRANSACTION_HEADER_ID
2019 from FA_TRANSACTION_HEADERS
2020 where ASSET_ID = '|| h_asset_id ||'
2022 and TRANSACTION_DATE_ENTERED >= '''|| h_prior_date ||'''
2023 and TRANSACTION_DATE_ENTERED <= '''|| h_target_date ||'''
2024 and TRANSACTION_TYPE_CODE =''TRANSFER'')
2025 and FDH.LOCATION_ID = LOC.LOCATION_ID
2026 and '||l_parm_state||' <> '''||h_state ||'''';
2027 v_TransferCursor := DBMS_SQL.OPEN_CURSOR;
2028
2029 DBMS_SQL.PARSE (v_TransferCursor,l_transfer_sql,DBMS_SQL.V7);
2030 DBMS_SQL.DEFINE_COLUMN(v_TransferCursor,1,h_current_state_flag,1);
2031
2032 v_TransferReturn := DBMS_SQL.EXECUTE(v_TransferCursor);
2033 v_TransferFetch := DBMS_SQL.FETCH_ROWS(v_TransferCursor);
2034 DBMS_SQL.COLUMN_VALUE(v_TransferCursor,1,h_current_state_flag);
2035 If v_TransferFetch = 0 then
2036 h_current_state_flag:='Y';
2037 end if;
2038
2039 DBMS_SQL.CLOSE_CURSOR(v_TransferCursor);
2040
2041 end;
2042 ----Bug6200581 ends
2043
2044 r_transfer_flag :='Y';
2045
2046 Exception
2047 when NO_DATA_FOUND then
2048 r_transfer_flag :='N';
2049 --Bug6200581
2050 --Setting the flag to Yes if no transfer has been performed on the asset
2051 h_current_state_flag:='Y';
2052 end;
2053
2054 IF (g_print_debug) THEN
2055 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - addition flag:'||r_addition_flag);
2056 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - retirement flag:'||r_ret_flag);
2057 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - transfer flag:'||r_transfer_flag);
2058 END IF;
2059
2060 /* Reset Reason and taxable cost information */
2061
2062 h_all_reason_type := to_char(null);
2063 h_all_reason_code := to_char(null);
2064 h_all_description := to_char(null);
2065 h_adddec_reason_type := to_char(null);
2066 h_adddec_reason_code := to_char(null);
2067 h_dec_type := to_char(null);
2068 h_adddec_description := to_char(null);
2069 h_add_dec_flag := to_char(null);
2070
2071 /*===========================================================================
2072 Get RETIREMENT_TYPE_CODE and SOLD_TO :
2073 If sale code used, get SOLD_TO for printing decrease report.
2074 ========================================================================== */
2075 if r_ret_flag ='Y' then
2076
2077 Select RETIREMENT_TYPE_CODE,
2078 SOLD_TO
2079 Into r_ret_type_code,
2080 r_sold_to
2081 From FA_RETIREMENTS
2082 Where TRANSACTION_HEADER_ID_IN = r_ret_id;
2083
2084
2085 IF (g_print_debug) THEN
2086 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - Retirement type code:'||r_ret_type_code);
2087 END IF;
2088
2089 end if;
2090
2091 /*==========================================================================
2092 Set ADD_DEC_FLAG, INCREASE_COST, DECREASE_COST
2093 ==========================================================================*/
2094
2095 -- bug4954665: Set start cost to 0 if prior year data is dummy data
2096 if h_action_flag = 'Z' then
2097 h_start_cost := 0;
2098 end if;
2099
2100 /* ADDTION for a year, set adddec flag is A */
2101
2102 if (h_end_cost - h_start_cost) >0 then
2103 h_increase_cost := h_end_cost - h_start_cost;
2104 h_decrease_cost := 0;
2105 h_add_dec_flag := 'A';
2106
2107 /* Set ADDDEC_REASON_CODE, ADD_DEC_REASON_TYPE, ADDDEC_DESCRIPTION when ADDDEC_FLAG ='A'*/
2108
2109 /* Set NEW ADDITION */
2110 if r_addition_flag = 'Y' and h_new_used = 'NEW' then
2111
2112 h_adddec_reason_code := '1'; /* NEW ADDITION */
2113 h_adddec_reason_type :='NEW ADDITION';
2114 h_adddec_description := to_char(null);
2115
2116 /* Set USED ADDTION */
2117 elsif r_addition_flag ='Y' and h_new_used = 'USED' then
2118
2119 h_adddec_reason_code := '2'; /* USED ADDITION */
2120 h_adddec_reason_type :='USED ADDITION';
2121 h_adddec_description := to_char(null);
2122
2123 /* Set Transfer in */
2124 elsif r_addition_flag ='N' and r_transfer_flag ='Y'
2125 and h_end_units_assigned - h_start_units_assigned >0 then
2126
2127 h_adddec_reason_code := '3'; /* TRANSFER */
2131 else
2128 h_adddec_reason_type :='TRANSFER';
2129 h_adddec_description := r_transfer_date;
2130
2132 /* Set Other Addition */
2133 h_adddec_reason_code := '4'; /* OTHER */
2134 h_adddec_reason_type :='OTHER';
2135 h_adddec_description := to_char(null);
2136 end if;
2137
2138 /* Set ADDDEC_REASON_CODE, ADD_DEC_REASON_TYPE, ADDDEC_DESCRIPTION when ADDDEC_FLAG ='D'*/
2139
2140 elsif (h_end_cost - h_start_cost) <0 then
2141 h_increase_cost := 0;
2142 h_decrease_cost := h_start_cost - h_end_cost;
2143 h_add_dec_flag := 'D';
2144
2145 /* Set DEC_TYPE == Partial decrease or Full decrease */
2146
2147 if h_end_cost = 0 then
2148 h_dec_type :='1'; /* Decrease type -- Full */
2149 else
2150 h_dec_type :='2'; /* Decrease type -- Partial */
2151 end if;
2152
2153 /* Set ADDDEC_REASON_CODE, ADD_DEC_REASON_TYPE, ADDDEC_DESCRIPTION when ADDDEC_FLAG ='D'*/
2154
2155 --Bug6200581 Added the condition to check if the asset is in the current state
2156 if r_ret_flag ='Y' and h_current_state_flag = 'Y' then
2157 /* Set SALE RETIREMENT */
2158
2159 if nvl(r_ret_type_code,'NULL') = nvl(sale_code,'NULL') then
2160 h_adddec_reason_code :='1'; /* Reason Code -- Sale */
2161 h_adddec_reason_type :='SALE';
2162 h_adddec_description := r_sold_to;
2163
2164 /* Set Normal Retirmenet */
2165 else
2166 h_adddec_reason_code :='2'; /* Reason Code -- Retirement */
2167 h_adddec_reason_type :='RETIREMENT';
2168
2169 /* Set Retirement Reason from FA Lookup code */
2170 if r_ret_type_code is not null then
2171
2172 Select MEANING
2173 into h_adddec_description
2174 from FA_LOOKUPS
2175 where LOOKUP_TYPE = 'RETIREMENT'
2176 and LOOKUP_CODE = r_ret_type_code;
2177
2178 else
2179 h_adddec_description := to_char(null);
2180 end if;
2181
2182 end if;
2183
2184 /* Set Transfer out */
2185
2186 elsif r_transfer_flag ='Y' and h_current_state_flag <> 'Y' then
2187
2188 h_adddec_reason_code := '3'; /* Reason Code -- Transfer */
2189 h_adddec_reason_type := 'TRANSFER';
2190 h_adddec_description := r_trn_transaction_name;
2191
2192 /* Set Other Decease */
2193 else
2194 h_adddec_reason_code := '4'; /* Reason Code -- Other */
2195 h_adddec_reason_type := 'OTHER';
2196 h_adddec_description := to_char(null);
2197 end if;
2198
2199 /* Set ADDDEC_REASON_CODE, ADD_DEC_REASON_TYPE, ADDDEC_DESCRIPTION when ADDDEC_FLAG is null */
2200
2201 else
2202 h_increase_cost := 0;
2203 h_decrease_cost :=0;
2204 h_add_dec_flag := to_char(null);
2205 end if;
2206
2207 /* Set ALL_REASON_CODE, ALL_REASON_TYPE, ALL_DESCRIPTION */
2208
2209 IF (g_print_debug) THEN
2210 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - Addition/Decrease Flag:'||h_add_dec_flag);
2211 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - ADD/DEC reason type:'||h_adddec_reason_type);
2212 END IF;
2213
2214 if h_add_dec_flag ='A' and h_adddec_reason_code ='3' then
2215
2216 h_all_reason_code := '3'; /* TRANSFER IN */
2217 h_all_reason_type := h_adddec_reason_type;
2218 h_all_description := h_adddec_description;
2219
2220 elsif h_add_dec_flag is null and h_start_life <> h_end_life
2221 and h_start_life >0 and h_end_life >0 then
2222
2223 h_all_reason_code := '4'; /* Change life*/
2224 h_all_reason_type := 'CHANGE LIFE';
2225
2226 h_all_description := h_start_life||r_change_life_desc;
2227
2228 elsif (h_add_dec_flag ='A' or h_add_dec_flag ='D')
2229 and h_adddec_reason_code ='4' then
2230
2231 h_all_reason_code := '4'; /* OTHER Addition */
2232 h_all_reason_type := h_adddec_reason_type;
2233 h_all_description := h_adddec_description;
2234
2235 elsif h_new_used ='NEW' then
2236
2237 h_all_reason_code := '1'; /* NEW ASSET */
2238 h_all_reason_type := 'NEW ASSET';
2239 h_all_description := to_char(null);
2240
2241 elsif h_new_used ='USED' then
2242
2243 h_all_reason_code := '2'; /* NEW ASSET */
2244 h_all_reason_type := 'USED ASSET';
2245 h_all_description := to_char(null);
2246
2247 else
2248
2249 h_all_reason_code := '4'; /* OTHER */
2250 h_all_reason_type := 'OTHER';
2251 h_all_description := to_char(null);
2252 end if;
2253
2254 IF (g_print_debug) THEN
2255 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- increase_cost:'||h_increase_cost);
2256 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- decrease_cost:'||h_decrease_cost);
2257 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- all reason type:'||h_all_reason_type);
2258 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- all reason code:'||h_all_reason_code);
2259 fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- all description:'||h_all_description);
2260 END IF;
2261
2262 /* bug 2082460 */
2263 h_adddec_description := substrb(h_adddec_description,1,30);
2264 h_all_description := substrb(h_all_description,1,30);
2265
2266 /* Insert end date's data to FA_DEPRN_TAX_REP_ITF */
2267
2268 IF (g_print_debug) THEN
2269 fa_rx_util_pkg.debug ('fadptx_insert: ' || 'h_state_flag :'||h_state_flag);
2270 END IF;
2271
2272 -- bug#2629893
2273 -- For FA_DEPRN_TAX_REP_ITF, insert and update date for state ranages.
2274 -- For FA_DEPRN_TAX_REP_NBVS, insert and update date for all states.
2275
2276 --Start for 9935602
2277
2278 l_match_found := 0;
2279 FOR l_local_cntr IN 1..(tax_asset_v.COUNT -1)
2280 LOOP
2281 IF (tax_asset_v(l_local_cntr) = h_tax_asset_type) THEN
2282 l_match_found := l_match_found + 1;
2283 END IF;
2284 END LOOP;
2285
2286 IF (l_match_found = 0) THEN
2287 tax_asset_v(tax_asset_v.COUNT) := h_tax_asset_type;
2288 END IF;
2289
2290 --End for 9935602
2291
2292 --Start for 9935602
2293
2294 l_match_found := 0;
2295 FOR l_local_cntr IN 1..(state_code_v.COUNT -1)
2296 LOOP
2297 IF (state_code_v(l_local_cntr) = h_state) THEN
2298 l_match_found := l_match_found + 1;
2299 END IF;
2300 END LOOP;
2301
2302 IF (l_match_found = 0) THEN
2303 state_code_v(state_code_v.COUNT) := h_state;
2304 END IF;
2305
2306 --End for 9935602
2307
2308 Insert into FA_DEPRN_TAX_REP_ITF (
2309 REQUEST_ID,
2310 YEAR,
2311 ASSET_ID,
2312 ASSET_NUMBER,
2313 ASSET_DESCRIPTION,
2314 NEW_USED,
2315 BOOK_TYPE_CODE,
2316 MINOR_CATEGORY,
2317 TAX_ASSET_TYPE,
2318 MINOR_CAT_DESC,
2319 STATE,
2320 START_UNITS_ASSIGNED,
2321 END_UNITS_ASSIGNED,
2322 END_COST,
2323 START_COST,
2324 THEORETICAL_NBV,
2325 EVALUATED_NBV,
2326 DATE_PLACED_IN_SERVICE,
2327 ERA_NAME_NUM,
2328 ADD_ERA_YEAR,
2329 ADD_MONTH,
2330 START_LIFE,
2331 END_LIFE,
2332 THEORETICAL_RESIDUAL_RATE,
2333 EVALUATED_RESIDUAL_RATE,
2334 THEORETICAL_TAXABLE_COST,
2335 EVALUATED_TAXABLE_COST,
2336 ADJUSTED_RATE,
2337 INCREASE_COST,
2338 DECREASE_COST,
2339 ALL_REASON_TYPE,
2340 ALL_REASON_CODE,
2341 ALL_DESCRIPTION,
2342 ADDDEC_REASON_TYPE,
2343 ADDDEC_REASON_CODE,
2344 DEC_TYPE,
2345 ADDDEC_DESCRIPTION,
2346 ADD_DEC_FLAG,
2347 CREATED_BY,
2348 CREATION_DATE,
2349 LAST_UPDATE_DATE,
2350 LAST_UPDATED_BY,
2351 LAST_UPDATE_LOGIN,
2352 FUNCTIONAL_CURRENCY_CODE,
2353 ORGANIZATION_NAME
2354 )
2355 values (
2356 h_request_id,
2357 h_year,
2358 h_asset_id,
2359 h_asset_number,
2360 h_asset_desc,
2361 h_new_used,
2362 h_book_type_code,
2363 h_minor_category,
2364 h_tax_asset_type,
2365 h_minor_cat_desc,
2369 h_end_cost,
2366 h_state,
2367 h_start_units_assigned,
2368 h_end_units_assigned,
2370 h_start_cost,
2371 h_theoretical_nbv,
2372 h_evaluated_nbv,
2373 h_date_in_service,
2374 h_era_name_num,
2375 h_add_era_year,
2376 h_add_month,
2377 h_start_life,
2378 h_end_life,
2379 h_theoretical_residual_rate,
2380 h_evaluated_residual_rate,
2381 h_theoretical_taxable_cost,
2382 h_evaluated_taxable_cost,
2383 h_adjusted_rate,
2384 h_increase_cost,
2385 h_decrease_cost,
2386 h_all_reason_type,
2387 h_all_reason_code,
2388 h_all_description,
2389 h_adddec_reason_type,
2390 h_adddec_reason_code,
2391 h_dec_type,
2392 h_adddec_description,
2393 h_add_dec_flag,
2394 h_login_id,
2395 sysdate,
2396 sysdate,
2397 h_login_id,
2398 h_login_id,
2399 h_currency_code,
2400 h_company_name
2401 );
2402
2403 -- IF (g_print_debug) THEN
2404 fa_rx_util_pkg.debug ('fadptx_insert: ' || 'Inserted - asset id:'||h_asset_id||', state: '||h_state||', asset type:'||h_tax_asset_type);
2405 -- END IF;
2406
2407 else
2408 -- IF (g_print_debug) THEN
2409 fa_rx_util_pkg.debug ('fadptx_insert: ' || 'Reject - asset id:'||h_asset_id||', state: '||h_state||', asset type:'||h_tax_asset_type);
2410 -- END IF;
2411
2412 end if; -- End of h_state_flag = 'Y' condition
2413
2414
2415 IF h_last_nbv_total_flag='Y'
2416 AND dist_total_theoretical_nbv IS null
2417 AND dist_total_evaluated_nbv IS null
2418 THEN
2419 h_store_theoretical_nbv := null;
2420 h_store_evaluated_nbv := null;
2421 ELSE
2422 h_store_theoretical_nbv := h_theoretical_nbv;
2423 h_store_evaluated_nbv := h_evaluated_nbv;
2424 END IF;
2425
2426 -- OPEN c_chk_nbv(h_asset_id, h_book_type_code, h_state, h_year);
2427 -- FETCH c_chk_nbv INTO h_chk_nbv_flag;
2428 --
2429 IF h_end_cost >0 then
2430 -- IF c_chk_nbv%found then
2431 -- UPDATE FA_DEPRN_TAX_REP_NBVS
2432 -- SET cost = h_end_cost,
2433 -- theoretical_nbv = h_store_theoretical_nbv,
2434 -- evaluated_nbv = h_store_evaluated_nbv,
2435 -- tax_asset_type = h_tax_asset_type,
2436 -- units_assigned = h_end_units_assigned,
2437 -- life = h_end_life, -- Added for bug#2468448
2438 -- last_updated_by = h_login_id,
2439 -- last_update_date = sysdate,
2440 -- last_update_login = h_login_id
2441 -- WHERE asset_id = h_asset_id
2442 -- AND book_type_code = h_book_type_code
2443 -- AND state= h_state
2444 -- AND year = h_year
2445 -- ;
2446 -- fa_rx_util_pkg.debug ('Updated - asset id:'||h_asset_id||', state: '||h_state||' to NBV table.');
2447 -- ELSE
2448 SELECT FA_DEPRN_TAX_REP_NBVS_S.NEXTVAL
2449 INTO h_deprn_tax_rep_nbv_id
2450 FROM dual;
2451
2452 INSERT INTO FA_DEPRN_TAX_REP_NBVS
2453 (
2454 deprn_tax_rep_nbv_id,
2455 asset_id,
2456 book_type_code,
2457 state,
2458 year,
2459 cost,
2460 theoretical_nbv,
2461 evaluated_nbv,
2462 tax_asset_type,
2463 units_assigned,
2464 life,
2465 created_by,
2466 creation_date,
2467 last_updated_by,
2468 last_update_date,
2469 last_update_login,
2470 minor_category
2471 )
2472 VALUES
2473 (
2474 h_deprn_tax_rep_nbv_id,
2475 h_asset_id,
2476 h_book_type_code,
2477 h_state,
2478 h_year,
2479 h_end_cost,
2480 h_store_theoretical_nbv,
2481 h_store_evaluated_nbv,
2482 h_tax_asset_type,
2483 h_end_units_assigned,
2484 h_end_life,
2485 h_login_id,
2486 sysdate,
2487 h_login_id,
2488 sysdate,
2492 -- IF (g_print_debug) THEN
2489 h_login_id,
2490 h_minor_category
2491 );
2493 fa_rx_util_pkg.debug ('fadptx_insert: ' || 'Inserted - asset id:'||h_asset_id||', state: '||h_state||' to NBV table.');
2494 -- END IF;
2495
2496 END IF; -- ENd of h_end_cost>0 condition
2497 -- END IF; -- End of c_chk_nbv condition
2498 -- CLOSE c_chk_nbv;
2499 end if; -- End of condition 'not (h_start_cost =0 AND h_end_cost=0)'
2500
2501 END LOOP;
2502
2503
2504
2505 IF (g_print_debug) THEN
2506 fa_rx_util_pkg.debug('fadptx_insert: ' || '***** End Loop for v_MainCursor *****');
2507 END IF;
2508
2509 END LOOP;
2510
2511 -- DBMS_SQL.close_cursor(v_state_cursor);
2512
2513 DBMS_SQL.CLOSE_CURSOR(v_MainCursor);
2514
2515 IF (g_print_debug) THEN
2516 fa_rx_util_pkg.debug('fadptx_insert: ' || '***** Close Cursor v_MainCursor *****');
2517 END IF;
2518
2519 DBMS_SQL.CLOSE_CURSOR(v_SubCursor);
2520
2521 IF (g_print_debug) THEN
2522 fa_rx_util_pkg.debug('fadptx_insert: ' || '***** End Loop and Close Cursor v_SubCursor *****');
2523 END IF;
2524
2525 ------------------------------------------------------
2526 -- Upgrade logic for Migration
2527 -- If the user apply Upgrade patch for Migration,
2528 -- call upgrade package for Migration
2529 --
2530 -- After restructure of the logic, migration package has been obsolete
2531 ------------------------------------------------------
2532
2533 /* ====================================================================
2534 Client Extension:
2535 if the client would update exception of standard code,rate,taxable cost,
2536 he create procedure FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE by himself
2537 and can insert their date.
2538 ==================================================================== */
2539
2540 begin
2541 v_ExtCursor := DBMS_SQL.OPEN_CURSOR;
2542
2543 l_ExtString := '
2544 Begin
2545 FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE(
2546 c_request_id => :h_request_id,
2547 c_year => :h_year,
2548 c_book_type_code => :h_book_type_code,
2549 c_state => :h_state);
2550
2551 fa_rx_util_pkg.debug(''fadptx_insert: '' ||
2552 ''There is FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE'');
2553
2554 Exception
2555 when others then
2556 FND_FILE.PUT_LINE(fnd_file.log,
2557 ''Unhandled error in FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE'');
2558 FND_FILE.PUT_LINE(fnd_file.log,
2559 ''Please check your custom procedure FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE'');
2560 :retcode :=2;
2561 :errbuf := sqlerrm;
2562
2563 end;';
2564
2565 DBMS_SQL.PARSE (v_ExtCursor, l_ExtString, DBMS_SQL.V7);
2566
2567 DBMS_SQL.BIND_VARIABLE (v_ExtCursor,':h_request_id' ,h_request_id );
2568 DBMS_SQL.BIND_VARIABLE (v_ExtCursor,':h_book_type_code' ,h_book_type_code );
2569 DBMS_SQL.BIND_VARIABLE (v_ExtCursor,':h_year' ,h_year);
2570 DBMS_SQL.BIND_VARIABLE (v_ExtCursor,':h_state' ,h_state );
2571 DBMS_SQL.BIND_VARIABLE (v_ExtCursor,':retcode' ,retcode );
2572 DBMS_SQL.BIND_VARIABLE (v_ExtCursor,':errbuf' ,errbuf );
2573
2574 v_ExtReturn := DBMS_SQL.EXECUTE(v_ExtCursor);
2575
2576 DBMS_SQL.CLOSE_CURSOR(v_ExtCursor);
2577
2578 if retcode = 2 then
2579 return;
2580 end if;
2581
2582 Exception
2583 when no_package then
2584 -- IF (g_print_debug) THEN
2585 fa_rx_util_pkg.debug('fadptx_insert: ' || 'There is no FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE');
2586 -- END IF;
2587
2588 end;
2589
2590 -- ER 7661628
2591 if g_release <> 11 then -- Making this code not applicable to 11i
2592 FOR c_missing_states_rec in c_missing_states(request_id, book, state_from, state_to)
2593 LOOP
2594
2595 --Start for 9935602
2596
2597 l_match_found := 0;
2598 FOR l_local_cntr IN 1..(state_code_v.COUNT -1)
2599 LOOP
2600 IF (state_code_v(l_local_cntr) = c_missing_states_rec.flex_value) THEN
2601 l_match_found := l_match_found + 1;
2602 END IF;
2603 END LOOP;
2604
2605 IF (l_match_found = 0) THEN
2606 state_code_v(state_code_v.COUNT) := c_missing_states_rec.flex_value;
2607 END IF;
2608
2609 --End for 9935602
2610
2611 INSERT INTO FA_DEPRN_TAX_REP_ITF(
2612 request_id,
2613 year,
2614 book_type_code,
2615 state,
2616 start_cost,
2617 end_cost,
2618 theoretical_nbv,
2619 evaluated_nbv,
2620 theoretical_taxable_cost,
2621 evaluated_taxable_cost,
2622 increase_cost,
2623 decrease_cost,
2624 created_by,
2625 creation_date,
2626 last_update_date,
2627 last_updated_by,
2628 last_update_login
2629 )
2630 VALUES(
2631 request_id,
2632 year,
2633 book,
2634 c_missing_states_rec.flex_value,
2635 0,
2636 0,
2637 0,
2638 0,
2639 0,
2640 0,
2641 0,
2642 0,
2643 login_id,
2644 sysdate,
2648 );
2645 sysdate,
2646 login_id,
2647 login_id
2649
2650 END LOOP;
2651 end if;
2652 -- End ER 7661628
2653
2654 fa_rx_util_pkg.debug ('*****END FA_DEPRN_TAX_REP_PKG.FADPTX_INSERT*****');
2655
2656 --Start for change 9935602
2657
2658 FOR l_tax_type_cntr IN 1..6
2659 LOOP
2660
2661 /* bug 13489595
2662
2663 l_oth_type_desc := NULL;
2664 l_match_found := 0;
2665
2666 FOR l_local_cntr IN 1..(tax_asset_v.COUNT -1)
2667 LOOP
2668 IF (to_char(l_tax_type_cntr) = tax_asset_v(l_local_cntr)) THEN
2669 l_match_found := l_match_found + 1;
2670 END IF;
2671 END LOOP;
2672
2673 IF (l_match_found = 0) THEN
2674 l_oth_type_desc :=
2675 fa_rx_flex_pkg.get_description(
2676 p_application_id => 140,
2677 p_id_flex_code => 'CAT#',
2678 p_id_flex_num => cat_flex_struct,
2679 p_qualifier => h_tax_asset_type_segment,
2680 p_data => TO_CHAR(l_tax_type_cntr));
2681 END IF;
2682
2683 IF (l_match_found = 0) THEN
2684 end bug 13489595*/
2685
2686 FOR l_local_cntr IN 1..(state_code_v.COUNT -1)
2687 LOOP
2688 /* Bug13892777: After further testing can comment the prior loop */
2689 l_oth_type_desc :=
2690 fa_rx_flex_pkg.get_description(
2691 p_application_id => 140,
2692 p_id_flex_code => 'CAT#',
2693 p_id_flex_num => cat_flex_struct,
2694 p_qualifier => h_tax_asset_type_segment,
2695 p_data => TO_CHAR(l_tax_type_cntr));
2696
2697 Insert into FA_DEPRN_TAX_REP_ITF (
2698 REQUEST_ID,
2699 YEAR,
2700 ASSET_ID,
2701 ASSET_NUMBER,
2702 ASSET_DESCRIPTION,
2703 NEW_USED,
2704 BOOK_TYPE_CODE,
2705 MINOR_CATEGORY,
2706 TAX_ASSET_TYPE,
2707 MINOR_CAT_DESC,
2708 STATE,
2709 START_UNITS_ASSIGNED,
2710 END_UNITS_ASSIGNED,
2711 END_COST,
2712 START_COST,
2713 THEORETICAL_NBV,
2714 EVALUATED_NBV,
2715 DATE_PLACED_IN_SERVICE,
2716 ERA_NAME_NUM,
2717 ADD_ERA_YEAR,
2718 ADD_MONTH,
2719 START_LIFE,
2720 END_LIFE,
2721 THEORETICAL_RESIDUAL_RATE,
2722 EVALUATED_RESIDUAL_RATE,
2723 THEORETICAL_TAXABLE_COST,
2724 EVALUATED_TAXABLE_COST,
2725 ADJUSTED_RATE,
2726 INCREASE_COST,
2727 DECREASE_COST,
2728 ALL_REASON_TYPE,
2729 ALL_REASON_CODE,
2730 ALL_DESCRIPTION,
2731 ADDDEC_REASON_TYPE,
2732 ADDDEC_REASON_CODE,
2733 DEC_TYPE,
2734 ADDDEC_DESCRIPTION,
2735 ADD_DEC_FLAG,
2736 CREATED_BY,
2737 CREATION_DATE,
2738 LAST_UPDATE_DATE,
2739 LAST_UPDATED_BY,
2740 LAST_UPDATE_LOGIN,
2741 FUNCTIONAL_CURRENCY_CODE,
2742 ORGANIZATION_NAME
2743 )
2744 SELECT
2745 request_id,
2746 year,
2747 0,
2748 NULL,
2749 NULL,
2750 NULL,
2751 book,
2752 TO_CHAR(l_tax_type_cntr),
2753 TO_CHAR(l_tax_type_cntr),
2754 l_oth_type_desc,
2755 state_code_v(l_local_cntr),
2756 0,
2757 0,
2758 0,
2759 0,
2760 0,
2761 0,
2762 NULL,
2763 NULL,
2764 NULL,
2765 NULL,
2766 NULL,
2767 NULL,
2768 0,
2769 0,
2770 0,
2771 0,
2772 0,
2773 0,
2774 0,
2775 NULL,
2776 NULL,
2777 NULL,
2778 NULL,
2779 NULL,
2780 NULL,
2781 NULL,
2782 NULL,
2783 h_login_id,
2787 h_login_id,
2784 sysdate,
2785 sysdate,
2786 h_login_id,
2788 NULL,
2789 NULL
2790 FROM dual
2791 WHERE NOT EXISTS (SELECT 1 FROM fa_deprn_tax_rep_itf
2792 WHERE request_id=h_request_id
2793 and year=h_year
2794 and state=state_code_v(l_local_cntr)
2795 and tax_asset_type=TO_CHAR(l_tax_type_cntr));
2796
2797 END LOOP;
2798
2799 -- END IF;
2800
2801 END LOOP;
2802
2803 --End of 9935602
2804
2805 commit;
2806
2807 EXCEPTION
2808 WHEN BOTH_NBV_ERROR THEN
2809
2810 if c_nbv_update%ISOPEN then
2811 CLOSE c_nbv_update;
2812 end if;
2813
2814 FND_MESSAGE.SET_NAME('OFA','FA_INVALID_COMBINATION');
2815 FND_MESSAGE.SET_TOKEN('COLUMN1','THEORETICAL_NBV',TRUE);
2816 FND_MESSAGE.SET_TOKEN('COLUMN2','EVALUATED_NBV',TRUE);
2817 FND_FILE.PUT_LINE(fnd_file.log,fnd_message.get);
2818 retcode :=2;
2819 errbuf := sqlerrm;
2820
2821 WHEN OTHERS THEN
2822 err_msg := SUBSTRB(SQLERRM,1,100);
2823 FND_FILE.PUT_LINE(fnd_file.log,err_msg);
2824 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
2825 retcode :=2;
2826 errbuf := sqlerrm;
2827
2828 end fadptx_insert;
2829
2830 /* ======================================================================
2831 Function
2832 FA_DEPRN_TAX_REP_PKG.DEBUG
2833
2834 DESCRIPTION
2835 This function is to print debug main sql.
2836 =======================================================================*/
2837
2838 function debug (p_print varchar2, k number) return varchar2 is
2839
2840 l_calling_fn varchar2(50) :='fa_deprn_tax_rep_pkg.debug';
2841
2842 begin
2843 if k =0 then
2844 -- IF (g_print_debug) THEN
2845 fa_rx_util_pkg.debug('debug: ***** Main SQL: ******');
2846 -- END IF;
2847 -- fa_rx_util_pkg.debug(p_print); -- bug#2434220 Commented out
2848 end if;
2849 -- IF (g_print_debug) THEN
2850 fa_rx_util_pkg.debug(p_print);
2851 -- END IF; -- bug#2434220
2852
2853 -- fa_rx_util_pkg.debug('fadpxtb.pls','debug: ', p_print);
2854
2855 return p_print;
2856 exception
2857 when others then
2858 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
2859
2860 end debug;
2861
2862 end FA_DEPRN_TAX_REP_PKG;