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