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