[Home] [Help]
PACKAGE BODY: APPS.FARX_RT
Source
1 PACKAGE BODY FARX_RT as
2 /* $Header: farxrtb.pls 120.11 2010/06/02 09:36:21 mswetha ship $ */
3
4 -- g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5 g_print_debug boolean := TRUE;
6
7 procedure ret (
8 book in varchar2,
9 begin_period in varchar2,
10 end_period in varchar2,
11 from_maj_cat in varchar2,
12 to_maj_cat in varchar2,
13 from_min_cat in varchar2,
14 to_min_cat in varchar2,
15 from_cc in varchar2,
16 to_cc in varchar2,
17 cat_seg_num in varchar2,
18 from_cat_seg_val in varchar2,
19 to_cat_seg_val in varchar2,
20 from_asset_num in varchar2,
21 to_asset_num in varchar2,
22 request_id in number,
23 user_id in number,
24 retcode out nocopy number,
25 errbuf out nocopy varchar2) is
26
27
28 h_count number;
29 h_book varchar2(30);
30 h_period1_pod date; -- Bug#9491496
31 h_period2_pcd date;
32 h_period1_pod_char varchar2(60);
33 h_period2 varchar2(60);
34 h_precision number;
35 h_dist_source_book varchar2(30);
36 h_request_id number;
37 h_login_id number;
38
39 h_acct_segs fa_rx_shared_pkg.Seg_Array;
40 h_loc_segs fa_rx_shared_pkg.Seg_Array;
41 h_cat_segs fa_rx_shared_pkg.Seg_Array;
42 h_concat_acct varchar2(500);
43 h_concat_loc varchar2(500);
44 h_concat_cat varchar2(500);
45 h_acct_struct number;
46 h_loc_struct number;
47 h_cat_struct number;
48 h_bal_seg number;
49 h_cc_seg number;
50 h_acct_seg number;
51
52 h_ccid number;
53 h_emp_name varchar2(240);
54 h_emp_number varchar2(30);
55 h_location_id number;
56 h_category_id number;
57 h_cost_acct varchar2(25);
58 h_reserve_acct varchar2(25);
59 h_asset_number varchar2(15);
60 h_description varchar2(80);
61 h_serial_number varchar2(35);
62 h_tag_number varchar2(15);
63 h_date_retired date;
64 h_units number;
65 h_trx_id number;
66 h_cost_retired number;
67 h_nbv_retired number;
68 h_proceeds_of_sale number;
69 h_gain_loss_amount number;
70 h_removal_cost number;
71 h_itc_captured number;
72 h_flag varchar2(1);
73 h_dpis date;
74 h_inventorial varchar2(3);
75 h_set_of_books_id number;
76 h_currency_code varchar2(15);
77 h_organization_name varchar2(80);
78
79 h_period_name varchar2(25);
80 h_period_name_to varchar2(25);
81 h_cat_seg_num varchar2(25);
82 h_account_desc varchar2(240);
83 h_cost_center_desc varchar2(240);
84 h_deprn_reserve number;
85 h_maj_cat varchar2(240);
86 h_maj_cat_desc varchar2(240);
87 h_min_cat varchar2(240);
88 h_min_cat_desc varchar2(240);
89 h_specified_cat varchar2(240);
90 h_specified_cat_desc varchar2(240);
91 h_tran_header_id number;
92
93 h_mesg_name varchar2(50);
94 h_mesg_str varchar2(2000);
95 h_flex_error varchar2(5);
96 h_ccid_error number;
97
98 h_assetkey_flex_structure number;
99 h_chart_of_accounts_id number;
100
101 maj_select_statement varchar2(50);
102 min_select_statement varchar2(50);
103 spec_select_statement varchar2(50);
104
105 l_param_where varchar2(1000);
106 from_clause varchar2(1000);
107 where_clause varchar2(3000);
108 select_statement varchar2(15000);
109
110 type var_cur is ref cursor;
111 ret_lines var_cur;
112
113 begin
114 IF (g_print_debug) THEN
115 fa_rx_util_pkg.debug('farx_rt.ret()+');
116 END IF;
117
118 h_book := book;
119 h_request_id := request_id;
120 h_period_name := begin_period;
121 h_period_name_to := end_period;
122 h_cat_seg_num := cat_seg_num;
123
124 select fcr.last_update_login into h_login_id
125 from fnd_concurrent_requests fcr
126 where fcr.request_id = h_request_id;
127
128 IF (g_print_debug) THEN
129 fa_rx_util_pkg.debug('ret: ' || '********login_id:' || h_login_id);
130 fa_rx_util_pkg.debug('ret: ' || '********login_id:' || h_login_id);
131 END IF;
132
133 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
134
135 select category_flex_structure, location_flex_structure,asset_key_flex_structure
136 into h_cat_struct, h_loc_struct, h_assetkey_flex_structure
137 from fa_system_controls;
138
139 h_mesg_name := 'FA_AMT_SEL_PERIODS';
140
141 select period_open_date --Bug#9491496
142 into h_period1_pod
143 from fa_deprn_periods
144 where book_type_code = h_book and period_name = begin_period;
145
146 /* BUG# 2939771
147
148 h_period1_pod_char := 'to_date(''' || h_period1_pod || ''',''dd-mm-yyyy hh24:mi:ss'')';
149 */
150
151 select count(*) into h_count
152 from fa_deprn_periods where period_name = end_period
153 and book_type_code = h_book;
154
155 if (h_count > 0) then
156 select period_close_date --Bug#9491496
157 into h_period2_pcd
158 from fa_deprn_periods
159 where book_type_code = h_book and period_name = end_period;
160 else
161 h_period2_pcd := null;
162 end if;
163
164
165 /* BUG# 2939771
166 if (h_period2_pcd is NULL) then
167 h_period2 := ' <= SYSDATE ';
168 else
169 h_period2 := ' <= to_date(''' || h_period2_pcd || ''',''dd-mm-yyyy hh24:mi:ss'')';
170 end if;
171 */
172
173 IF (g_print_debug) THEN
174 fa_rx_util_pkg.debug('ret: ' || 'h_period2:' || h_period2);
175 END IF;
176
177 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
178
179 select accounting_flex_structure, distribution_source_book
180 into h_acct_struct, h_dist_source_book
181 from fa_book_controls
182 where book_type_code = h_book;
183
184 h_mesg_name := 'FA_DYN_CURRENCY';
185
186
187 select cur.precision into h_precision
188 from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
189 where bc.book_type_code = h_book
190 and sob.set_of_books_id = bc.set_of_books_id
191 and sob.currency_code = cur.currency_code;
192
193 h_mesg_name := 'FA_RX_SEGNUMS';
194
195 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
196 BOOK => h_book,
197 BALANCING_SEGNUM => h_bal_seg,
198 ACCOUNT_SEGNUM => h_acct_seg,
199 CC_SEGNUM => h_cc_seg,
200 CALLING_FN => 'RET');
201
202 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
203
204 select sob.chart_of_accounts_id,
205 sob.set_of_books_id,
206 substr(sob.currency_code,1,15),
207 substr(sob.name,1 ,80)
208 into h_chart_of_accounts_id,
209 h_set_of_books_id,
210 h_currency_code,
211 h_organization_name
212 from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
213 WHERE bc.book_type_code = h_book
214 AND sob.set_of_books_id = bc.set_of_books_id
215 AND sob.currency_code = cur.currency_code; -- Added set_of_books_id and currency_code to display those on report
216
217
218
219 IF (g_print_debug) THEN
220 fa_rx_util_pkg.debug('ret: ' || 'chart of account ID:' || h_chart_of_accounts_id);
221 END IF;
222
223 --
224 -- Get Columns for Major_category, Minor_category and Specified_category
225 --
226 maj_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT','SELECT', 'BASED_CATEGORY');
227
228 begin
229 min_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT','SELECT', 'MINOR_CATEGORY');
230 exception
231 when others then
232 min_select_statement := 'null';
233 end;
234
235 begin
236 spec_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT','SELECT', cat_seg_num);
237 exception
238 when others then
239 spec_select_statement := 'null';
240 end;
241
242 --
243 -- Figure out the from and where clause for the parameters
244 --
245
246 -- default from clause
247
248 from_clause := '
249 fa_additions ad,
250 gl_code_combinations dhcc,
251 fa_asset_history ah,
252 fa_category_books cb,
253 per_all_people_f emp,
254 fa_locations loc,
255 fa_distribution_history dh,
256 fa_books books,
257 fa_retirements ret,
258 fa_transaction_headers th,
259 fa_deprn_detail dd,
260 fa_deprn_periods dp,
261 fa_categories cat';
262
263 -- parameter where clause --
264
265
266 l_param_where := null;
267
268 -- Major Category --
269 l_param_where := l_param_where || ' AND (' ||
270 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
271 'SELECT', 'BASED_CATEGORY') ||' >= :from_maj_cat or :from_maj_cat is NULL)';
272
273 l_param_where := l_param_where || ' AND (' ||
274 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
275 'SELECT', 'BASED_CATEGORY') ||' <= :to_maj_cat or :to_maj_cat is NULL)';
276
277 -- Minor Category --
278 /*
279 l_param_where := l_param_where || ' AND (' ||
280 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
281 'SELECT', 'MINOR_CATEGORY') ||' >= :from_min_cat or :from_min_cat is NULL)';
282
283 l_param_where := l_param_where || ' AND (' ||
284 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
285 'SELECT', 'MINOR_CATEGORY') ||' <= :to_min_cat or :to_min_cat is NULL)';
286 */
287
288 /* Fix for Bug# 2973255: Added expection handling to proceed
289 in case that flex_sql fails when from_min_cat or to_min_cat are null
290 */
291 begin
292 l_param_where := l_param_where || ' AND (' ||
293 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
294 'SELECT', 'MINOR_CATEGORY') ||' >= :from_min_cat or :from_min_cat is NULL)';
295 exception
296 when others then
297 l_param_where := l_param_where || ' AND (:from_min_cat is NULL and :from_min_cat is NULL)';
298 end;
299
300 begin
301 l_param_where := l_param_where || ' AND (' ||
302 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
303 'SELECT', 'MINOR_CATEGORY') ||' <= :to_min_cat or :to_min_cat is NULL)';
304 exception
305 when others then
306 l_param_where := l_param_where || ' AND (:to_min_cat is NULL and :to_min_cat is NULL)';
307 end;
308
309
310
311 -- Category Segment Number --
312 IF (cat_seg_num IS NOT NULL) THEN
313 h_cat_seg_num := cat_seg_num;
314 l_param_where := l_param_where || ' AND (' ||
315 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
316 'SELECT', cat_seg_num) ||' >= :from_cat_seg_val or :from_cat_seg_val is NULL)';
317
318 l_param_where := l_param_where || ' AND (' ||
319 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
320 'SELECT', cat_seg_num) ||' <= :to_cat_seg_val or :to_cat_seg_val is NULL)';
321 ELSE
322 l_param_where := l_param_where || ' AND ( nvl(:from_cat_seg_val,-999) = -999 or :from_cat_seg_val is null)';
323 l_param_where := l_param_where || ' AND ( nvl(:to_cat_seg_val,-999) = -999 or :to_cat_seg_val is null)';
324 END IF;
325
326
327 /*
328 -- Category Conditions --
329 IF (l_param_where is not NULL) THEN
330 from_clause := from_clause || ',
331 fa_categories cat';
332 l_param_where := l_param_where || ' AND CB.CATEGORY_ID = CAT.CATEGORY_ID';
333 END IF;
334 */
335
336 l_param_where := l_param_where || ' AND (' ||
337 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
338 'SELECT', 'FA_COST_CTR') ||' >= :from_cc or :from_cc is NULL)';
339
340 l_param_where := l_param_where || ' AND (' ||
341 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
342 'SELECT', 'FA_COST_CTR') ||' <= :to_cc or :to_cc is NULL)';
343
344
345 /* BUG# 2939771
346 -- Asset Number --
347 IF (from_asset_num = to_asset_num) THEN
348 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER = '''
349 || from_asset_num || '''';
350 elsif (from_asset_num is not NULL) and (to_asset_num is not NULL) THEN
351 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER BETWEEN '''
352 || from_asset_num || '''' || ' AND ''' || to_asset_num || '''';
353 elsif (from_asset_num is not NULL) THEN
354 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER >= '''
355 || from_asset_num || '''';
356 elsif (to_asset_num is not NULL) THEN
357 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER <= '''
358 || to_asset_num || '''';
359 END IF;
360 */
361 -- Asset Number --
362 l_param_where := l_param_where || ' AND (AD.ASSET_NUMBER >= :from_asset_num OR :from_asset_num is NULL)';
363 l_param_where := l_param_where || ' AND (AD.ASSET_NUMBER <= :to_asset_num OR :to_asset_num is NULL)';
364
365 IF (g_print_debug) THEN
366 fa_rx_util_pkg.debug('ret: ' || 'l_param_where:' || l_param_where);
367 END IF;
368
369
370 where_clause := 'th.date_effective between :h_period1_pod AND nvl(:h_period2_pcd,sysdate) AND
371 th.book_type_code = :h_book AND
372 th.transaction_key = ''R''
373 AND BOOKS.TRANSACTION_HEADER_ID_OUT = TH.TRANSACTION_HEADER_ID AND
374 BOOKS.BOOK_TYPE_CODE = :h_book AND
375 books.asset_id = th.asset_id
376 AND
377 th.transaction_header_id = decode(th.transaction_type_code,''REINSTATEMENT'',
378 ret.transaction_header_id_out, ret.transaction_header_id_in)
379 AND
380 ad.asset_id = th.asset_id
381 AND
382 cb.category_id = ah.category_id AND
383 cb.book_type_code = :h_book
384 AND
385 ah.asset_id = ad.asset_id AND
386 ah.date_effective <= th.date_effective AND
387 nvl(ah.date_ineffective, th.date_effective+1) > th.date_effective
388 AND
389 dh.asset_id = th.asset_id AND
390 dh.book_type_code = :h_dist_source_book AND
391 ( dh.retirement_id = ret.retirement_id
392 or
393 (ret.date_effective >= dh.date_effective and
394 ret.date_effective <= nvl(dh.date_ineffective,sysdate) and
395 ret.units is null) )
396 AND
397 dhcc.code_combination_id = dh.code_combination_id
398 AND
399 dh.location_id = loc.location_id
400 AND
401 dh.assigned_to = emp.person_id(+)
402 AND
403 trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
404 AND
405 dd.book_type_code = :h_book AND
406 dd.asset_id = ad.asset_id AND
407 dd.period_counter = dp.period_counter AND
408 dd.distribution_id = dh.distribution_id AND
409 dp.book_type_code = dd.book_type_code AND
410 ret.asset_id = dd.asset_id AND
411 ret.date_effective >= dp.period_open_date AND
412 ret.date_effective <= nvl(dp.period_close_date,sysdate)
413 AND CB.CATEGORY_ID = CAT.CATEGORY_ID';
414
415 IF (l_param_where is not NULL) THEN
416 where_clause := where_clause || l_param_where;
417 END IF;
418
419 h_mesg_name := 'FA_RETIREMENTS_SQL_DCUR';
420
421 IF (g_print_debug) THEN
422 fa_rx_util_pkg.debug('ret: ' || 'where_clause:' || where_clause);
423 fa_rx_util_pkg.debug('ret: ' || 'from_clause:' || from_clause);
424 END IF;
425
426 select_statement := '
427 SELECT /*+ leading(th ad) use_nl(ad) index(FA_ADDITIONS_B_U1 )*/ --Bug# 7587861
428 dhcc.code_combination_id,
429 emp.full_name,
430 emp.employee_number,
431 loc.location_id,
432 cb.category_id,
433 decode(ah.asset_type,''CIP'',cb.cip_cost_acct,cb.asset_cost_acct),
434 cb.deprn_reserve_acct,
435 ad.inventorial,
436 ad.asset_number,
437 ad.description,
438 ad.serial_number,
439 ad.tag_number,
440 ret.date_retired,
441 decode(sign(dh.transaction_units),-1,-dh.transaction_units,dh.transaction_units),
442 th.transaction_header_id,
443 ROUND(decode(ret.units, NULL,
444 (decode(th.transaction_type_code, ''REINSTATEMENT'',
445 -ret.cost_retired, ret.cost_retired)
446 * (dh.units_assigned /ah.units)),
447 (decode(th.transaction_type_code, ''REINSTATEMENT'',
448 -ret.cost_retired, ret.cost_retired)
449 * -dh.transaction_units / ret.units)) ,:h_precision),
450 ROUND(decode(ret.units, NULL,
451 (decode(th.transaction_type_code, ''REINSTATEMENT'',
452 -ret.nbv_retired, ret.nbv_retired)
453 * (dh.units_assigned /ah.units)),
454 (decode(th.transaction_type_code, ''REINSTATEMENT'',
455 -ret.nbv_retired, ret.nbv_retired)
456 * -dh.transaction_units / ret.units)),:h_precision),
457 ROUND(decode(ret.units, NULL,
458 (decode(th.transaction_type_code, ''REINSTATEMENT'',
459 -ret.proceeds_of_sale, ret.proceeds_of_sale)
460 * (dh.units_assigned /ah.units)),
461 (decode(th.transaction_type_code, ''REINSTATEMENT'',
462 -ret.proceeds_of_sale, ret.proceeds_of_sale)
463 * -dh.transaction_units / ret.units)),:h_precision),
464 ROUND(decode(ret.units, NULL,
465 (decode(th.transaction_type_code, ''REINSTATEMENT'',
466 -ret.gain_loss_amount, ret.gain_loss_amount)
467 * (dh.units_assigned /ah.units)),
468 (decode(th.transaction_type_code, ''REINSTATEMENT'',
469 -ret.gain_loss_amount, ret.gain_loss_amount)
470 * -dh.transaction_units / ret.units)),:h_precision),
471 round(decode(ret.units, NULL,
472 (decode(th.transaction_type_code,''REINSTATEMENT'',
473 -ret.cost_of_removal, ret.cost_of_removal)
474 * (dh.units_assigned / ah.units)),
475 (decode(th.transaction_type_code, ''REINSTATEMENT'',
476 -ret.cost_of_removal, ret.cost_of_removal)
477 * -dh.transaction_units / ret.units)),:h_precision),
478 round(decode(ret.units, NULL,
479 (decode(th.transaction_type_code,''REINSTATEMENT'',
480 -ret.itc_recaptured, ret.itc_recaptured)
481 * (dh.units_assigned / ah.units)),
482 (decode(th.transaction_type_code, ''REINSTATEMENT'',
483 -ret.itc_recaptured, ret.itc_recaptured)
484 * -dh.transaction_units / ret.units)),:h_precision),
485 decode(th.transaction_type_code, ''REINSTATEMENT'', ''*'', ''PARTIAL RETIREMENT'',''P'',NULL),
486 books.date_placed_in_service,
487 dd.ytd_deprn,'||
488 maj_select_statement ||','||
489 min_select_statement ||','||
490 spec_select_statement ||'
491 FROM ' || from_clause || '
492 WHERE ' || where_clause;
493
494 IF (g_print_debug) THEN
495 fa_rx_util_pkg.debug('ret: ' || 'select_statement:' || select_statement);
496 END IF;
497
498 /* BUG# 2939771
499 open ret_lines for select_statement ;
500 */
501 open ret_lines for select_statement using
502 h_precision, -- select
503 h_precision,
504 h_precision,
505 h_precision,
506 h_precision,
507 h_precision,
508 h_period1_pod, -- where_clause
509 h_period2_pcd,
510 h_book,
511 h_book,
512 h_book,
513 h_dist_source_book,
514 h_book,
515 from_maj_cat, -- l_param_where
516 from_maj_cat,
517 to_maj_cat,
518 to_maj_cat,
519 from_min_cat,
520 from_min_cat,
521 to_min_cat,
522 to_min_cat,
523 from_cat_seg_val,
524 from_cat_seg_val,
525 to_cat_seg_val,
526 to_cat_seg_val,
527 from_cc,
528 from_cc,
529 to_cc,
530 to_cc,
531 from_asset_num,
532 from_asset_num,
533 to_asset_num,
534 to_asset_num;
535
536
537 IF (g_print_debug) THEN
538 fa_rx_util_pkg.debug('ret: ' || 'after_open');
539 END IF;
540
541 loop
542
543 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
544
545 fetch ret_lines into
546 h_ccid,
547 h_emp_name,
548 h_emp_number,
549 h_location_id,
550 h_category_id,
551 h_cost_acct,
552 h_reserve_acct,
553 h_inventorial,
554 h_asset_number,
555 h_description,
556 h_serial_number,
557 h_tag_number,
558 h_date_retired,
559 h_units,
560 h_trx_id,
561 h_cost_retired,
562 h_nbv_retired,
563 h_proceeds_of_sale,
564 h_gain_loss_amount,
565 h_removal_cost,
566 h_itc_captured,
567 h_flag,
568 h_dpis,
569 h_deprn_reserve,
570 h_maj_cat,
571 h_min_cat,
572 h_specified_cat;
573
574
575
576 if (ret_lines%NOTFOUND) then exit; end if;
577
578 IF (g_print_debug) THEN
579 fa_rx_util_pkg.debug('ret_lines:');
580 END IF;
581
582 h_mesg_name := 'FA_RX_CONCAT_SEGS';
583 h_flex_error := 'GL#';
584 h_ccid_error := h_ccid;
585
586 fa_rx_shared_pkg.concat_acct (
587 struct_id => h_acct_struct,
588 ccid => h_ccid,
589 concat_string => h_concat_acct,
590 segarray => h_acct_segs);
591
592 h_flex_error := 'CAT#';
593 h_ccid_error := h_category_id;
594
595 fa_rx_shared_pkg.concat_category (
596 struct_id => h_cat_struct,
597 ccid => h_category_id,
598 concat_string => h_concat_cat,
599 segarray => h_cat_segs);
600
601 h_flex_error := 'LOC#';
602 h_ccid_error := h_location_id;
603
604 fa_rx_shared_pkg.concat_location (
605 struct_id => h_loc_struct,
606 ccid => h_location_id,
607 concat_string => h_concat_loc,
608 segarray => h_loc_segs);
609
610 h_account_desc :=
611 fa_rx_flex_pkg.get_description(
612 p_application_id => 101,
613 p_id_flex_code => 'GL#',
614 p_id_flex_num => h_chart_of_accounts_id,
615 p_qualifier => 'GL_ACCOUNT',
616 p_data => h_cost_acct);
617
618 h_cost_center_desc :=
619 fa_rx_flex_pkg.get_description(
620 p_application_id => 101,
621 p_id_flex_code => 'GL#',
622 p_id_flex_num => h_chart_of_accounts_id,
623 p_qualifier => 'FA_COST_CTR',
624 p_data => h_acct_segs(h_cc_seg));
625
626
627 IF (g_print_debug) THEN
628 fa_rx_util_pkg.debug('ret: ' || '** assetkey_flex_struct:' || h_assetkey_flex_structure);
629 fa_rx_util_pkg.debug('ret: ' || '** category_id:' || h_category_id);
630 fa_rx_util_pkg.debug('ret: ' || '** specified_cat:' || h_cat_seg_num);
631 END IF;
632
633 /*
634 h_maj_cat :=
635 fa_rx_flex_pkg.get_value(
636 p_application_id => 140,
637 p_id_flex_code => 'CAT#',
638 p_id_flex_num => h_assetkey_flex_structure,
639 p_qualifier => 'BASED_CATEGORY',
640 p_ccid => h_category_id);
641 */
642 begin
643 h_maj_cat_desc :=
644 fa_rx_flex_pkg.get_description(
645 p_application_id => 140,
646 p_id_flex_code => 'CAT#',
647 p_id_flex_num => h_assetkey_flex_structure,
648 p_qualifier => 'BASED_CATEGORY',
649 p_data => h_maj_cat);
650 exception
651 when others then
652 h_maj_cat_desc := null;
653 end;
654
655 /*
656 BEGIN
657 h_min_cat :=
658 fa_rx_flex_pkg.get_value(
659 p_application_id => 140,
660 p_id_flex_code => 'CAT#',
661 p_id_flex_num => h_assetkey_flex_structure,
662 p_qualifier => 'MINOR_CATEGORY',
663 p_ccid => h_category_id);
664 EXCEPTION
665 WHEN OTHERS THEN
666 h_min_cat := null;
667 end;
668 */
669 begin
670 h_min_cat_desc :=
671 fa_rx_flex_pkg.get_description(
672 p_application_id => 140,
673 p_id_flex_code => 'CAT#',
674 p_id_flex_num => h_assetkey_flex_structure,
675 p_qualifier => 'MINOR_CATEGORY',
676 p_data => h_min_cat);
677 EXCEPTION
678 WHEN OTHERS THEN
679 h_min_cat_desc := null;
680 end;
681 /*
682 BEGIN
683 h_specified_cat :=
684 fa_rx_flex_pkg.get_value(
685 p_application_id => 140,
686 p_id_flex_code => 'CAT#',
687 p_id_flex_num => h_assetkey_flex_structure,
688 p_qualifier => h_cat_seg_num,
689 p_ccid => h_category_id);
690 EXCEPTION
691 WHEN OTHERS THEN
692 h_specified_cat := null;
693 end;
694 */
695 begin
696 h_specified_cat_desc :=
697 fa_rx_flex_pkg.get_description(
698 p_application_id => 140,
699 p_id_flex_code => 'CAT#',
700 p_id_flex_num => h_assetkey_flex_structure,
701 p_qualifier => h_cat_seg_num,
702 p_data => h_specified_cat);
703 EXCEPTION
704 WHEN OTHERS THEN
705 h_specified_cat_desc := null;
706 end;
707
708 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
709
710 insert into fa_retire_rep_itf (
711 request_id, company, cost_Center, expense_acct,
712 location, category, cost_acct, reserve_acct,
713 asset_number, description, serial_number, tag_number,
714 date_retired, units_retired, cost_retired, nbv_retired,
715 proceeds_of_sale, gain_loss_amount, removal_cost,
716 itc_captured, flag, date_placed_in_service, inventorial,
717 employee_name, employee_number, transaction_header_id,
718 created_by, creation_date, last_updated_by,
719 last_update_date, last_update_login,
720 set_of_books_id, functional_currency_code,organization_name,
721 book_type_code,period_name,period_name_to,account_description,
722 cost_center_description,
723 deprn_reserve,
724 major_category,
725 major_category_desc,minor_category,minor_category_desc,
726 specified_category_seg,specified_cat_seg_desc) values (
727 request_id, h_acct_segs(h_bal_seg),
728 h_acct_segs(h_cc_seg), h_acct_segs(h_acct_seg),
729 h_concat_loc, h_concat_cat, h_cost_acct, h_reserve_acct,
730 h_asset_number, h_description, h_serial_number,
731 h_tag_number, h_date_retired, h_units,
732 h_cost_retired, h_nbv_retired, h_proceeds_of_sale,
733 h_gain_loss_amount, h_removal_cost, h_itc_captured,
734 h_flag, h_dpis, h_inventorial, h_emp_name, h_emp_number, h_trx_id,
735 user_id, sysdate, user_id, sysdate, h_login_id,
736 h_set_of_books_id, h_currency_code, h_organization_name,
737 h_book,h_period_name,h_period_name_to,h_account_desc,
738 h_cost_center_desc,
739 h_deprn_reserve,
740 h_maj_cat,h_maj_cat_desc,h_min_cat,
741 h_min_cat_desc,h_specified_cat,h_specified_cat_desc);
742
743
744
745 IF (g_print_debug) THEN
746 fa_rx_util_pkg.debug('ret: ' || 'During loop');
747 END IF;
748
749 end loop;
750
751 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
752 close ret_lines;
753
754
755 exception when others then
756 if SQLCODE <> 0 then
757 fa_Rx_conc_mesg_pkg.log(SQLERRM);
758 end if;
759 fnd_message.set_name('OFA',h_mesg_name);
760 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
761 fnd_message.set_token('TABLE','FA_RETIRE_REP_ITF',FALSE);
762 end if;
763 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
764 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
765 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
766 end if;
767
768 h_mesg_str := fnd_message.get;
769 fa_rx_conc_mesg_pkg.log(h_mesg_str);
770 retcode := 2;
771
772 end ret;
773
774 END FARX_RT;