[Home] [Help]
PACKAGE BODY: APPS.FA_RX_SHARED_PKG
Source
1 PACKAGE BODY FA_RX_SHARED_PKG as
2 /* $Header: farxb.pls 120.28 2011/02/16 14:21:05 gigupta ship $ */
3
4
5 type flex_val_rec is record (
6 flex_value_set_name varchar2(60),
7 flex_value_id varchar2(240)
8 );
9 type flex_val_tab is table of flex_val_rec index by binary_integer;
10 flex_val_cache flex_val_tab;
11 flex_val_count number := 0;
12
13 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
14
15 PROCEDURE GET_ACCT_SEGMENT_NUMBERS (
16 BOOK IN VARCHAR2,
17 BALANCING_SEGNUM OUT NOCOPY NUMBER,
18 ACCOUNT_SEGNUM OUT NOCOPY NUMBER,
19 CC_SEGNUM OUT NOCOPY NUMBER,
20 CALLING_FN IN VARCHAR2) IS
21
22 structure_num number;
23 this_segment_num number;
24
25 h_mesg_name varchar2(50);
26 h_mesg_str varchar2(2000);
27
28 BEGIN
29 -- get structure ID for this book
30
31 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
32
33 select accounting_flex_structure
34 into structure_num
35 from fa_book_controls
36 where book_type_code = BOOK;
37
38 h_mesg_name := 'FA_RX_SEGNUMS';
39
40 -- get Balancing Segment
41 -- (Code copied from fnd_flex_apis.get_qualifier_segnum in
42 -- FND source control.)
43
44 SELECT s.segment_num INTO this_segment_num
45 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
46 fnd_segment_attribute_types sat
47 WHERE s.application_id = 101
48 AND s.id_flex_code = 'GL#'
49 AND s.id_flex_num = structure_num
50 AND s.enabled_flag = 'Y'
51 AND s.application_column_name = sav.application_column_name
52 AND sav.application_id = 101
53 AND sav.id_flex_code = 'GL#'
54 AND sav.id_flex_num = structure_num
55 AND sav.attribute_value = 'Y'
56 AND sav.segment_attribute_type = sat.segment_attribute_type
57 AND sat.application_id = 101
58 AND sat.id_flex_code = 'GL#'
59 AND sat.unique_flag = 'Y'
60 AND sat.segment_attribute_type = 'GL_BALANCING';
61
62 SELECT count(segment_num) INTO balancing_segnum
63 FROM fnd_id_flex_segments
64 WHERE application_id = 101
65 AND id_flex_code = 'GL#'
66 AND id_flex_num = structure_num
67 AND enabled_flag = 'Y'
68 AND segment_num <= this_segment_num;
69
70 -- get Account segment
71
72 SELECT s.segment_num INTO this_segment_num
73 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
74 fnd_segment_attribute_types sat
75 WHERE s.application_id = 101
76 AND s.id_flex_code = 'GL#'
77 AND s.id_flex_num = structure_num
78 AND s.enabled_flag = 'Y'
79 AND s.application_column_name = sav.application_column_name
80 AND sav.application_id = 101
81 AND sav.id_flex_code = 'GL#'
82 AND sav.id_flex_num = structure_num
83 AND sav.attribute_value = 'Y'
84 AND sav.segment_attribute_type = sat.segment_attribute_type
85 AND sat.application_id = 101
86 AND sat.id_flex_code = 'GL#'
87 AND sat.unique_flag = 'Y'
88 AND sat.segment_attribute_type = 'GL_ACCOUNT';
89
90 SELECT count(segment_num) INTO account_segnum
91 FROM fnd_id_flex_segments
92 WHERE application_id = 101
93 AND id_flex_code = 'GL#'
94 AND id_flex_num = structure_num
95 AND enabled_flag = 'Y'
96 AND segment_num <= this_segment_num;
97
98
99 -- Get Cost Center segment
100
101 SELECT s.segment_num INTO this_segment_num
102 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
103 fnd_segment_attribute_types sat
104 WHERE s.application_id = 101
105 AND s.id_flex_code = 'GL#'
106 AND s.id_flex_num = structure_num
107 AND s.enabled_flag = 'Y'
108 AND s.application_column_name = sav.application_column_name
109 AND sav.application_id = 101
110 AND sav.id_flex_code = 'GL#'
111 AND sav.id_flex_num = structure_num
112 AND sav.attribute_value = 'Y'
113 AND sav.segment_attribute_type = sat.segment_attribute_type
114 AND sat.application_id = 101
115 AND sat.id_flex_code = 'GL#'
116 AND sat.unique_flag = 'Y'
117 AND sat.segment_attribute_type = 'FA_COST_CTR';
118
119 SELECT count(segment_num) INTO cc_segnum
120 FROM fnd_id_flex_segments
121 WHERE application_id = 101
122 AND id_flex_code = 'GL#'
123 AND id_flex_num = structure_num
124 AND enabled_flag = 'Y'
125 AND segment_num <= this_segment_num;
126
127
128
129 EXCEPTION
130 when others then
131 fnd_message.set_name('OFA',h_mesg_name);
132 h_mesg_str := fnd_message.get;
133 fa_rx_conc_mesg_pkg.log(h_mesg_str);
134 raise;
135
136 END GET_ACCT_SEGMENT_NUMBERS;
137
138 PROCEDURE GET_ACCT_SEGMENT_INDEX (
139 BOOK IN VARCHAR2,
140 BALANCING_SEGNUM OUT NOCOPY NUMBER,
141 ACCOUNT_SEGNUM OUT NOCOPY NUMBER,
142 CC_SEGNUM OUT NOCOPY NUMBER,
143 CALLING_FN IN VARCHAR2) IS
144
145 structure_num number;
146 this_segment_num number;
147
148 h_mesg_name varchar2(50);
149 h_mesg_str varchar2(2000);
150
151 BEGIN
152
153 -- get structure ID for this book
154
155 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
156
157 select accounting_flex_structure
158 into structure_num
159 from fa_book_controls
160 where book_type_code = BOOK;
161
162 h_mesg_name := 'FA_RX_SEGNUMS';
163
164 -- get Balancing Segment
165 -- (Code copied from fnd_flex_apis.get_qualifier_segnum in
166 -- FND source control.)
167
168 SELECT to_number(substr(s.application_column_name,8,2)) INTO this_segment_num
169 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
170 fnd_segment_attribute_types sat
171 WHERE s.application_id = 101
172 AND s.id_flex_code = 'GL#'
173 AND s.id_flex_num = structure_num
174 AND s.enabled_flag = 'Y'
175 AND s.application_column_name = sav.application_column_name
176 AND sav.application_id = 101
177 AND sav.id_flex_code = 'GL#'
178 AND sav.id_flex_num = structure_num
179 AND sav.attribute_value = 'Y'
180 AND sav.segment_attribute_type = sat.segment_attribute_type
181 AND sat.application_id = 101
182 AND sat.id_flex_code = 'GL#'
183 AND sat.unique_flag = 'Y'
184 AND sat.segment_attribute_type = 'GL_BALANCING';
185
186 -- bug 1796224, changed where-clause below.
187
188 SELECT count(segment_num) INTO balancing_segnum
189 FROM fnd_id_flex_segments
190 WHERE application_id = 101
191 AND id_flex_code = 'GL#'
192 AND id_flex_num = structure_num
193 AND enabled_flag = 'Y'
194 AND to_number(substr(application_column_name,8,2)) <= this_segment_num;
195
196
197 -- get Account segment
198
199 SELECT to_number(substr(s.application_column_name,8,2)) INTO this_segment_num
200 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
201 fnd_segment_attribute_types sat
202 WHERE s.application_id = 101
203 AND s.id_flex_code = 'GL#'
204 AND s.id_flex_num = structure_num
205 AND s.enabled_flag = 'Y'
206 AND s.application_column_name = sav.application_column_name
207 AND sav.application_id = 101
208 AND sav.id_flex_code = 'GL#'
209 AND sav.id_flex_num = structure_num
210 AND sav.attribute_value = 'Y'
211 AND sav.segment_attribute_type = sat.segment_attribute_type
212 AND sat.application_id = 101
213 AND sat.id_flex_code = 'GL#'
214 AND sat.unique_flag = 'Y'
215 AND sat.segment_attribute_type = 'GL_ACCOUNT';
216
217 -- bug 1796224, changed where-clause below.
218
219 SELECT count(segment_num) INTO account_segnum
220 FROM fnd_id_flex_segments
221 WHERE application_id = 101
222 AND id_flex_code = 'GL#'
223 AND id_flex_num = structure_num
224 AND enabled_flag = 'Y'
225 AND to_number(substr(application_column_name,8,2)) <= this_segment_num;
226
227 -- Get Cost Center segment
228
229 SELECT to_number(substr(s.application_column_name,8,2)) INTO this_segment_num
230 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
231 fnd_segment_attribute_types sat
232 WHERE s.application_id = 101
233 AND s.id_flex_code = 'GL#'
234 AND s.id_flex_num = structure_num
235 AND s.enabled_flag = 'Y'
236 AND s.application_column_name = sav.application_column_name
237 AND sav.application_id = 101
238 AND sav.id_flex_code = 'GL#'
239 AND sav.id_flex_num = structure_num
240 AND sav.attribute_value = 'Y'
241 AND sav.segment_attribute_type = sat.segment_attribute_type
242 AND sat.application_id = 101
243 AND sat.id_flex_code = 'GL#'
244 AND sat.unique_flag = 'Y'
245 AND sat.segment_attribute_type = 'FA_COST_CTR';
246
247 -- bug 1796224, changed where-clause below.
248
249 SELECT count(segment_num) INTO cc_segnum
250 FROM fnd_id_flex_segments
251 WHERE application_id = 101
252 AND id_flex_code = 'GL#'
253 AND id_flex_num = structure_num
254 AND enabled_flag = 'Y'
255 AND to_number(substr(application_column_name,8,2)) <= this_segment_num;
256
257 EXCEPTION
258 when others then
259 fnd_message.set_name('OFA',h_mesg_name);
260 h_mesg_str := fnd_message.get;
261 fa_rx_conc_mesg_pkg.log(h_mesg_str);
262 raise;
263
264 END GET_ACCT_SEGMENT_INDEX;
265
266
267 PROCEDURE GET_ACCT_SEGMENTS (
268 combination_id IN NUMBER,
269 n_segments IN OUT NOCOPY NUMBER,
270 segments IN OUT NOCOPY Seg_Array,
271 calling_fn IN VARCHAR2) IS
272
273 ii number;
274 selectedsegs Seg_Array;
275
276 l_ccid number;
277
278
279 BEGIN
280 n_segments := 0;
281 l_ccid := combination_id;
282
283 select segment1, segment2, segment3, segment4, segment5,
284 segment6, segment7, segment8, segment9, segment10,
285 segment11, segment12, segment13, segment14, segment15,
286 segment16, segment17, segment18, segment19, segment20,
287 segment21, segment22, segment23, segment24, segment25,
288 segment26, segment27, segment28, segment29, segment30
289 into selectedsegs(1), selectedsegs(2), selectedsegs(3), selectedsegs(4), selectedsegs(5),
290 selectedsegs(6), selectedsegs(7), selectedsegs(8), selectedsegs(9), selectedsegs(10),
291 selectedsegs(11), selectedsegs(12), selectedsegs(13), selectedsegs(14), selectedsegs(15),
292 selectedsegs(16), selectedsegs(17), selectedsegs(18), selectedsegs(19), selectedsegs(20),
293 selectedsegs(21), selectedsegs(22), selectedsegs(23), selectedsegs(24), selectedsegs(25),
294 selectedsegs(26), selectedsegs(27), selectedsegs(28), selectedsegs(29), selectedsegs(30)
295 from gl_code_combinations
296 where code_combination_id = l_ccid;
297
298 for i in 1..30 loop
299 if (selectedsegs(i) is not null) then
300 n_segments := n_segments + 1;
301 segments(n_segments) := selectedsegs(i);
302 end if;
303 end loop;
304
305
306
307 EXCEPTION
308 when others then raise;
309
310
311 END GET_ACCT_SEGMENTS;
312
313
314
315 procedure fadolif (
316 life in number default null,
317 adj_rate in number default null,
318 bonus_rate in number default null,
319 prod in number default null,
320 retval out nocopy varchar2) IS
321
322 BEGIN
323
324 IF life IS NOT NULL
325 THEN
326
327 retval := (LPAD(TO_CHAR(TRUNC(life/12, 0), '90'),3,' ') || '.' ||
328 SUBSTR(TO_CHAR(MOD(life, 12), '00'), 2, 2)) || ' ';
329
330 ELSIF adj_rate IS NOT NULL
331 THEN
332 retval := TO_CHAR(ROUND((adj_rate + NVL(bonus_rate, 0))*100, 2), '90.99') || '%';
333 ELSIF prod IS NOT NULL
334 THEN
335 --test for length of production_capacity; if it's longer
336 --than 7 characters, then display in exponential notation
337
338 --IF prod <= 9999999
339 --THEN
340 -- retval := TO_CHAR(prod);
341 --ELSE
342 -- retval := SUBSTR(LTRIM(TO_CHAR(prod, '9.9EEEE')), 1, 7);
343 --END IF;
344
345 --display nothing for UOP assets
346 retval := '';
347 ELSE
348 --should not occur
349 retval := ' ';
350 END IF;
351
352
353
354 end fadolif;
355
356
357 procedure fa_rsvldg (
358 book in varchar2,
359 period in varchar2,
360 report_style in varchar2,
361 sob_id in number, -- MRC
362 errbuf out nocopy varchar2,
363 retcode out nocopy number) IS
364
365 operation varchar2(200);
366 dist_book varchar2(30);
367 ucd date;
368 upc number;
369 tod date;
370 tpc number;
371
372
373 h_mesg_name varchar2(50);
374 h_mesg_str varchar2(2000);
375 h_table_token varchar2(30);
376 h_mrcsobtype varchar2(1); -- MRC
377
378 BEGIN
379
380 h_mesg_name := 'FA_SHARED_DELETE_FAILED';
381 h_table_token := 'FA_RESERVE_LEDGER_GT';
382
383 /*
384 no longer needed when using global temp table
385 DELETE FROM FA_RESERVE_LEDGER;
386
387 if (SQL%ROWCOUNT > 0) then
388 COMMIT;
389 else
390 ROLLBACK;
391 end if;
392 */
393
394 -- MRC
395 if sob_id is not null then
396 begin
397 select 'P'
398 into h_mrcsobtype
399 from fa_book_controls
400 where book_type_code = book
401 and set_of_books_id = sob_id;
402 exception
403 when no_data_found then
404 h_mrcsobtype := 'R';
405 end;
406 else
407 h_mrcsobtype := 'P';
408 end if;
409 -- End MRC
410
411 h_mesg_name := 'FA_AMT_SEL_DP';
412
413 SELECT
414 BC.DISTRIBUTION_SOURCE_BOOK dbk,
415 nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
416 DP.PERIOD_COUNTER upc,
417 min (DP_FY.PERIOD_OPEN_DATE) tod,
418 min (DP_FY.PERIOD_COUNTER) tpc
419 INTO
420 dist_book,
421 ucd,
422 upc,
423 tod,
424 tpc
425 FROM
426 FA_DEPRN_PERIODS DP,
427 FA_DEPRN_PERIODS DP_FY,
428 FA_BOOK_CONTROLS BC
429 WHERE
430 DP.BOOK_TYPE_CODE = book AND
431 DP.PERIOD_NAME = period AND
432 DP_FY.BOOK_TYPE_CODE = book AND
433 DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
434 AND BC.BOOK_TYPE_CODE = book
435 GROUP BY
436 BC.DISTRIBUTION_SOURCE_BOOK,
437 DP.PERIOD_CLOSE_DATE,
438 DP.PERIOD_COUNTER;
439
440 operation := 'Inserting into FA_RESERVE_LEDGER_GT';
441
442 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
443 h_table_token := 'FA_RESERVE_LEDGER_GT';
444
445 if(h_mrcsobtype <> 'R') then -- MRC
446
447 INSERT INTO FA_RESERVE_LEDGER_GT
448 (ASSET_ID,
449 DH_CCID,
450 DEPRN_RESERVE_ACCT,
451 DATE_PLACED_IN_SERVICE,
452 METHOD_CODE,
453 LIFE,
454 RATE,
455 CAPACITY,
456 COST,
457 DEPRN_AMOUNT,
458 YTD_DEPRN,
459 DEPRN_RESERVE,
460 PERCENT,
461 TRANSACTION_TYPE,
462 PERIOD_COUNTER,
463 DATE_EFFECTIVE,
464 DISTRIBUTION_ID,
465 IMPAIRMENT_RESERVE)
466 SELECT
467 DH.ASSET_ID ASSET_ID,
468 DH.CODE_COMBINATION_ID DH_CCID,
469 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
470 BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
471 BOOKS.DEPRN_METHOD_CODE METHOD,
472 BOOKS.LIFE_IN_MONTHS LIFE,
473 BOOKS.ADJUSTED_RATE RATE,
474 BOOKS.PRODUCTION_CAPACITY CAPACITY,
475 DD.COST COST,
476 decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
477 DEPRN_AMOUNT,
478 decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
479 YTD_DEPRN,
480 DD.DEPRN_RESERVE DEPRN_RESERVE,
481 round (decode (TH.TRANSACTION_TYPE_CODE, null,
482 DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
483 PERCENT,
484 decode (TH.TRANSACTION_TYPE_CODE, null,
485 decode (TH_RT.TRANSACTION_TYPE_CODE,
486 'FULL RETIREMENT', 'F',
487 decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
488 'TRANSFER', 'T',
489 'TRANSFER OUT', 'P',
490 'RECLASS', 'R') T_TYPE,
491 DD.PERIOD_COUNTER,
492 NVL(TH.DATE_EFFECTIVE, ucd),
493 DH.DISTRIBUTION_ID,
494 DD.IMPAIRMENT_RESERVE
495 FROM
496 FA_DEPRN_DETAIL DD,
497 FA_ASSET_HISTORY AH,
498 FA_TRANSACTION_HEADERS TH,
499 FA_TRANSACTION_HEADERS TH_RT,
500 FA_BOOKS BOOKS,
501 FA_DISTRIBUTION_HISTORY DH,
502 FA_CATEGORY_BOOKS CB
503 WHERE
504 CB.BOOK_TYPE_CODE = book AND
505 CB.CATEGORY_ID = AH.CATEGORY_ID
506 AND
507 AH.ASSET_ID = DH.ASSET_ID AND
508 AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
509 nvl(AH.DATE_INEFFECTIVE,sysdate)
510 >= nvl(TH.DATE_EFFECTIVE, ucd) AND
511 -- AH.ASSET_TYPE = 'CAPITALIZED'
512 ( ( AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP') AND
513 BOOKS.GROUP_ASSET_ID is null
514 ) OR
515 ( AH.ASSET_TYPE = 'CAPITALIZED' AND
516 BOOKS.GROUP_ASSET_ID is not null
517 and exists (select 1
518 from fa_books oldbk
519 , fa_transaction_headers oldth
520 , fa_deprn_periods dp
521 where oldbk.transaction_header_id_out = books.transaction_header_id_in
522 and oldbk.transaction_header_id_out = oldth.transaction_header_id
523 and dp.book_type_code = book
524 and dp.period_counter = dd.period_counter
525 and oldth.date_effective between dp.period_open_date
526 and nvl(dp.period_close_date, oldth.date_effective)
527 and oldbk.group_asset_id is null)
528 ) OR
529 ( nvl(report_style,'S') = 'D' AND
530 AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP')
531 )
532 )
533 AND
534 DD.BOOK_TYPE_CODE = book AND
535 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
536 DD.PERIOD_COUNTER =
537 (SELECT max (DD_SUB.PERIOD_COUNTER)
538 FROM FA_DEPRN_DETAIL DD_SUB
539 WHERE DD_SUB.BOOK_TYPE_CODE = book
540 AND DD_SUB.ASSET_ID = DH.ASSET_ID
541 AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
542 AND DD_SUB.PERIOD_COUNTER <= upc)
543 AND
544 TH_RT.BOOK_TYPE_CODE = book AND
545 TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
546 AND
547 BOOKS.BOOK_TYPE_CODE = book AND
548 BOOKS.ASSET_ID = DH.ASSET_ID AND
549 nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
550 -- Rolling back fix for bug 4610445
551 -- nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= upc AND
552 BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
553 nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
554 AND
555 TH.BOOK_TYPE_CODE (+) = dist_book AND
556 TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
557 TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
558 AND
559 DH.BOOK_TYPE_CODE = dist_book AND
560 DH.DATE_EFFECTIVE <= ucd AND
561 nvl(DH.DATE_INEFFECTIVE, sysdate) > tod;
562 -- Rolling back fix for bug 4610445
563 -- ucd between dh.date_effective and nvl(dh.date_ineffective,ucd);
564
565 -- MRC
566 else
567 INSERT INTO FA_RESERVE_LEDGER_GT
568 (ASSET_ID,
569 DH_CCID,
570 DEPRN_RESERVE_ACCT,
571 DATE_PLACED_IN_SERVICE,
572 METHOD_CODE,
573 LIFE,
574 RATE,
575 CAPACITY,
576 COST,
577 DEPRN_AMOUNT,
578 YTD_DEPRN,
579 DEPRN_RESERVE,
580 PERCENT,
581 TRANSACTION_TYPE,
582 PERIOD_COUNTER,
583 DATE_EFFECTIVE,
584 DISTRIBUTION_ID,
585 IMPAIRMENT_RESERVE)
586 SELECT
587 DH.ASSET_ID ASSET_ID,
588 DH.CODE_COMBINATION_ID DH_CCID,
589 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
590 BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
591 BOOKS.DEPRN_METHOD_CODE METHOD,
592 BOOKS.LIFE_IN_MONTHS LIFE,
593 BOOKS.ADJUSTED_RATE RATE,
594 BOOKS.PRODUCTION_CAPACITY CAPACITY,
595 DD.COST COST,
596 decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
597 DEPRN_AMOUNT,
598 decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
599 YTD_DEPRN,
600 DD.DEPRN_RESERVE DEPRN_RESERVE,
601 round (decode (TH.TRANSACTION_TYPE_CODE, null,
602 DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
603 PERCENT,
604 decode (TH.TRANSACTION_TYPE_CODE, null,
605 decode (TH_RT.TRANSACTION_TYPE_CODE,
606 'FULL RETIREMENT', 'F',
607 decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
608 'TRANSFER', 'T',
609 'TRANSFER OUT', 'P',
610 'RECLASS', 'R') T_TYPE,
611 DD.PERIOD_COUNTER,
612 NVL(TH.DATE_EFFECTIVE, ucd),
613 DH.DISTRIBUTION_ID,
614 DD.IMPAIRMENT_RESERVE
615 FROM
616 FA_MC_DEPRN_DETAIL DD,
617 FA_ASSET_HISTORY AH,
618 FA_TRANSACTION_HEADERS TH,
619 FA_TRANSACTION_HEADERS TH_RT,
620 FA_MC_BOOKS BOOKS,
621 FA_DISTRIBUTION_HISTORY DH,
622 FA_CATEGORY_BOOKS CB
623 WHERE
624 CB.BOOK_TYPE_CODE = book AND
625 CB.CATEGORY_ID = AH.CATEGORY_ID
626 AND
627 AH.ASSET_ID = DH.ASSET_ID AND
628 AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
629 nvl(AH.DATE_INEFFECTIVE,sysdate)
630 >= nvl(TH.DATE_EFFECTIVE, ucd) AND
631 -- AH.ASSET_TYPE = 'CAPITALIZED'
632 ( ( AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP') AND
633 BOOKS.GROUP_ASSET_ID is null
634 ) OR
635 ( AH.ASSET_TYPE = 'CAPITALIZED' AND
636 BOOKS.GROUP_ASSET_ID is not null
637 and exists (select 1
638 from fa_mc_books oldbk
639 , fa_transaction_headers oldth
640 , fa_mc_deprn_periods dp
641 where oldbk.transaction_header_id_out = books.transaction_header_id_in
642 and oldbk.transaction_header_id_out = oldth.transaction_header_id
643 and dp.book_type_code = book
644 and dp.period_counter = dd.period_counter
645 and oldth.date_effective between dp.period_open_date
646 and nvl(dp.period_close_date, oldth.date_effective)
647 and oldbk.group_asset_id is null
648 and oldbk.set_of_books_id = sob_id
649 and dp.set_of_books_id = sob_id)
650 ) OR
651 ( nvl(report_style,'S') = 'D' AND
652 AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP')
653 )
654 )
655 AND
656 DD.BOOK_TYPE_CODE = book AND
657 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
658 DD.PERIOD_COUNTER =
659 (SELECT max (DD_SUB.PERIOD_COUNTER)
660 FROM FA_MC_DEPRN_DETAIL DD_SUB
661 WHERE DD_SUB.BOOK_TYPE_CODE = book
662 AND DD_SUB.ASSET_ID = DH.ASSET_ID
663 AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
664 AND DD_SUB.PERIOD_COUNTER <= upc
665 AND DD_SUB.SET_OF_BOOKS_ID = sob_id)
666 AND
667 TH_RT.BOOK_TYPE_CODE = book AND
668 TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
669 AND
670 BOOKS.BOOK_TYPE_CODE = book AND
671 BOOKS.ASSET_ID = DH.ASSET_ID AND
672 nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
673 -- Rolling back fix for bug 4610445
674 -- nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= upc AND
675 BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
676 nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
677 AND
678 TH.BOOK_TYPE_CODE (+) = dist_book AND
679 TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
680 TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
681 AND
682 DH.BOOK_TYPE_CODE = dist_book AND
683 DH.DATE_EFFECTIVE <= ucd AND
684 nvl(DH.DATE_INEFFECTIVE, sysdate) > tod
685 AND
686 DD.SET_OF_BOOKS_ID = sob_id
687 AND
688 BOOKS.SET_OF_BOOKS_ID = sob_id;
689 -- Rolling back fix for bug 4610445
690 -- ucd between dh.date_effective and nvl(dh.date_ineffective,ucd);
691 end if;
692 -- End MRC
693
694 retcode := 1;
695
696 exception
697 when others then
698 retcode := 2;
699 fnd_message.set_name('OFA',h_mesg_name);
700 if h_mesg_name in ('FA_FLEX_DELETE_FAILED','FA_FLEX_INSERT_FAILED') then
701 fnd_message.set_token('TABLE',h_table_token,FALSE);
702 end if;
703 h_mesg_str := fnd_message.get;
704 fa_rx_conc_mesg_pkg.log(h_mesg_str);
705
706 end fa_rsvldg;
707
708
709 procedure concat_general (
710 table_id in number,
711 table_name in varchar2,
712 ccid_col_name in varchar2,
713 struct_id in number,
714 flex_code in varchar2,
715 ccid in number,
716 appl_id in number,
717 appl_short_name in varchar2,
718 concat_string in out nocopy varchar2,
719 segarray in out nocopy Seg_Array) is
720
721 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl
722 cursor segcolumns is
723 select g.application_column_name, g.segment_num
724 from fnd_columns c, fnd_id_flex_segments g
725 WHERE g.application_id = appl_id
726 AND g.id_flex_code = flex_code
727 AND g.id_flex_num = struct_id
728 AND g.enabled_flag = 'Y'
729 AND c.application_id = appl_id
730 AND c.table_id = table_id
731 AND c.column_name = g.application_column_name
732 group by g.application_column_name, g.segment_num
733 ORDER BY g.segment_num;
734
735 i number;
736 delim varchar2(1);
737 col_name varchar2(25);
738
739 num_segs integer;
740 seg_ctr integer;
741
742 v_cursorid integer;
743 v_sqlstmt varchar2(500);
744 v_return integer;
745
746 h_mesg_name varchar2(30);
747 h_mesg_str varchar2(2000);
748
749 l_use_global_table varchar2(10);
750
751 BEGIN
752
753 if (fa_rx_shared_pkg.g_seg_count = 0) then
754 l_use_global_table := 'NO';
755 else
756 l_use_global_table := 'YES';
757 end if;
758
759 concat_string := '';
760
761 h_mesg_name := 'FA_BUDGET_NO_SEG_DELIM';
762
763 num_segs := 0;
764 seg_ctr := 0;
765
766 v_sqlstmt := 'select ';
767
768
769 h_mesg_name := 'FA_SHARED_FLEX_SEGCOLUMNS';
770
771
772 -- global table is currently initialized when called
773 -- from what if, to improve performance. When time
774 -- allows all other RX procedures should use the
775 -- global table instead of selects same data over and over.
776 -- What needs to be done is to find all entry points and
777 -- fill the global table there.
778
779 if l_use_global_table = 'NO' then
780
781 Select s.concatenated_segment_delimiter into delim
782 FROM fnd_id_flex_structures s, fnd_application a
783 WHERE s.application_id = a.application_id
784 AND s.id_flex_code = flex_code
785 AND s.id_flex_num = struct_id
786 AND a.application_short_name = appl_short_name;
787
788 open segcolumns;
789 loop
790
791 fetch segcolumns into col_name, v_return;
792
793 if (segcolumns%NOTFOUND) then exit; end if;
794
795 v_sqlstmt := v_sqlstmt || col_name || ', ';
796 num_segs := num_segs + 1;
797
798 segarray(num_segs) := 'seeded';
799
800 end loop;
801 close segcolumns;
802
803
804 else
805 FOR i IN fa_rx_shared_pkg.g_seg_table.FIRST .. fa_rx_shared_pkg.g_seg_table.LAST LOOP
806
807 if table_name = fa_rx_shared_pkg.g_seg_table(i).tabname then
808
809
810 v_sqlstmt := v_sqlstmt || fa_rx_shared_pkg.g_seg_table(i).colname || ', ';
811 num_segs := num_segs + 1;
812 delim := fa_rx_shared_pkg.g_seg_table(i).delimiter;
813
814 segarray(num_segs) := 'seeded';
815
816 end if;
817
818 end loop;
819 end if;
820 --
821
822
823 h_mesg_name := 'FA_SHARED_FLEX_DYNAMIC_SQL';
824
825 v_sqlstmt := rtrim(v_sqlstmt,', ');
826 v_sqlstmt := v_sqlstmt || ' from ' || table_name;
827 /*Modified code for bug 9351332
828 v_sqlstmt := v_sqlstmt || ' where ' || ccid_col_name || ' = ';
829 v_sqlstmt := v_sqlstmt || to_char(ccid);*/
830 v_sqlstmt := v_sqlstmt || ' where ' || ccid_col_name || ' =:x ';
831
832 v_cursorid := dbms_sql.open_cursor;
833 dbms_sql.parse(v_cursorid, v_sqlstmt, DBMS_SQL.V7);
834 --Added code for bug 9351332
835 dbms_sql.bind_variable(v_cursorid,':x', to_char(ccid));
836
837 for seg_ctr in 1 .. num_segs loop
838
839 dbms_sql.define_column(v_cursorid, seg_ctr, segarray(seg_ctr), 30);
840
841 end loop;
842
843 v_return := dbms_sql.execute(v_cursorid);
844 v_return := dbms_sql.fetch_rows(v_cursorid);
845
846 for seg_ctr in 1 .. num_segs loop
847 dbms_sql.column_value(v_cursorid, seg_ctr, segarray(seg_ctr));
848
849 end loop;
850
851 for seg_ctr in 1 .. num_segs loop
852 concat_string := concat_string || segarray(seg_ctr) || delim;
853
854 end loop;
855
856 concat_string := rtrim(concat_string,delim);
857
858 dbms_sql.close_cursor(v_cursorid);
859
860 exception
861 when others then
862
863 fnd_message.set_name('OFA',h_mesg_name);
864 if h_mesg_name like 'FA_SHARED_FLEX%' then
865 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
866 fnd_message.set_token('FLEX_CODE',flex_Code,FALSE);
867 end if;
868 h_mesg_str := fnd_message.get;
869 fa_rx_conc_mesg_pkg.log(h_mesg_str);
870 raise;
871
872 end concat_general;
873
874 procedure concat_category (
875 struct_id in number,
876 ccid in number,
877 concat_string in out nocopy varchar2,
878 segarray in out nocopy Seg_Array) is
879
880 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
881 h_table_id number;
882 h_id_flex_code varchar2(4);
883
884 h_mesg_name varchar2(30);
885 h_mesg_str varchar2(2000);
886
887 begin
888
889 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
890
891 select table_id into h_table_id from fnd_tables
892 where table_name = 'FA_CATEGORIES_B' and application_id = 140;
893
894 concat_general (
895 table_id => h_table_id,
896 table_name => 'FA_CATEGORIES_B',
897 ccid_col_name => 'CATEGORY_ID',
898 struct_id => struct_id,
899 flex_code => 'CAT#',
900 ccid => ccid,
901 appl_id => 140,
902 appl_short_name => 'OFA',
903 concat_string => concat_string,
904 segarray => segarray);
905
906 -- seg_table => seg_table,
907 exception
908 when others then
909
910 fnd_message.set_name('OFA',h_mesg_name);
911 if h_mesg_name like 'FA_SHARED_FLEX%' then
912 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
913 fnd_message.set_token('FLEX_CODE','CAT#',FALSE);
914 end if;
915 h_mesg_str := fnd_message.get;
916 fa_rx_conc_mesg_pkg.log(h_mesg_str);
917 raise;
918
919 end concat_category;
920
921 procedure concat_location (
922 struct_id in number,
923 ccid in number,
924 concat_string in out nocopy varchar2,
925 segarray in out nocopy Seg_Array) is
926
927 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
928
929 h_id_flex_code varchar2(4);
930 h_table_id number;
931
932 h_mesg_name varchar2(30);
933 h_mesg_str varchar2(2000);
934
935 begin
936
937 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
938
939
940 select table_id into h_table_id from fnd_tables
941 where table_name = 'FA_LOCATIONS' and application_id = 140;
942
943 concat_general (
944 table_id => h_table_id,
945 table_name => 'FA_LOCATIONS',
946 ccid_col_name => 'LOCATION_ID',
947 struct_id => struct_id,
948 flex_code => 'LOC#',
949 ccid => ccid,
950 appl_id => 140,
951 appl_short_name => 'OFA',
952 concat_string => concat_string,
953 segarray => segarray);
954
955 -- seg_table => seg_table,
956
957 exception
958 when others then
959
960 fnd_message.set_name('OFA',h_mesg_name);
961 if h_mesg_name like 'FA_SHARED_FLEX%' then
962 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
963 fnd_message.set_token('FLEX_CODE','LOC#',FALSE);
964 end if;
965 h_mesg_str := fnd_message.get;
966 fa_rx_conc_mesg_pkg.log(h_mesg_str);
967 raise;
968
969 end concat_location;
970
971 procedure concat_asset_key (
972 struct_id in number,
973 ccid in number,
974 concat_string in out nocopy varchar2,
975 segarray in out nocopy Seg_Array) is
976
977 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
978
979 h_id_flex_code varchar2(4);
980 h_table_id number;
981
982 h_mesg_name varchar2(30);
983 h_mesg_str varchar2(2000);
984
985 begin
986 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
987
988
989 select table_id into h_table_id from fnd_tables
990 where table_name = 'FA_ASSET_KEYWORDS' and application_id = 140;
991
992 concat_general (
993 table_id => h_table_id,
994 table_name => 'FA_ASSET_KEYWORDS',
995 ccid_col_name => 'CODE_COMBINATION_ID',
996 struct_id => struct_id,
997 flex_code => 'KEY#',
998 ccid => ccid,
999 appl_id => 140,
1000 appl_short_name => 'OFA',
1001 concat_string => concat_string,
1002 segarray => segarray);
1003
1004 -- seg_table => seg_table,
1005
1006 exception
1007 when others then
1008
1009 fnd_message.set_name('OFA',h_mesg_name);
1010 if h_mesg_name like 'FA_SHARED_FLEX%' then
1011 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
1012 fnd_message.set_token('FLEX_CODE','KEY#',FALSE);
1013 end if;
1014 h_mesg_str := fnd_message.get;
1015 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1016 raise;
1017
1018 end concat_asset_key;
1019
1020 procedure concat_acct (
1021 struct_id in number,
1022 ccid in number,
1023 concat_string in out nocopy varchar2,
1024 segarray in out nocopy Seg_Array) is
1025
1026 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
1027
1028 h_id_flex_code varchar2(4);
1029 h_table_id number;
1030
1031 h_mesg_name varchar2(30);
1032 h_mesg_str varchar2(2000);
1033
1034 begin
1035
1036 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
1037
1038 select table_id into h_table_id from fnd_tables
1039 where table_name = 'GL_CODE_COMBINATIONS' and application_id = 101;
1040
1041 concat_general (
1042 table_id => h_table_id,
1043 table_name => 'GL_CODE_COMBINATIONS',
1044 ccid_col_name => 'CODE_COMBINATION_ID',
1045 struct_id => struct_id,
1046 flex_code => 'GL#',
1047 ccid => ccid,
1048 appl_id => 101,
1049 appl_short_name => 'SQLGL',
1050 concat_string => concat_string,
1051 segarray => segarray);
1052 -- seg_table => seg_table,
1053
1054 exception
1055 when others then
1056
1057 fnd_message.set_name('OFA',h_mesg_name);
1058 if h_mesg_name like 'FA_SHARED_FLEX%' then
1059 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
1060 fnd_message.set_token('FLEX_CODE','GL#',FALSE);
1061 end if;
1062 h_mesg_str := fnd_message.get;
1063 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1064 raise;
1065
1066
1067 end concat_acct;
1068
1069
1070 -- This procedure, get_request_info doesn't seem to be called
1071 -- from anywhere, at some point try to remove it.
1072 procedure get_request_info (
1073 userid in number,
1074 prog_name_template in varchar2,
1075 max_requests in number,
1076 dateform in varchar2,
1077 applid in number,
1078 user_conc_prog_names out nocopy largevarchar2table,
1079 conc_prog_names out nocopy varchar2table,
1080 arg_texts out nocopy largevarchar2table,
1081 request_ids out nocopy numbertable,
1082 phases out nocopy varchar2table,
1083 statuses out nocopy varchar2table,
1084 dev_phases out nocopy smallvarchar2table,
1085 dev_statuses out nocopy smallvarchar2table,
1086 timestamps out nocopy varchar2table,
1087 num_requests out nocopy number) is
1088
1089 ii integer;
1090
1091 cursor request_info is
1092 select ltrim(ltrim(t.user_concurrent_program_name, 'RX-only:')),
1093 b.concurrent_program_name, cr.argument_text,
1094 cr.request_id, lp.meaning, ls.meaning, cr.phase_code, cr.status_code,
1095 to_char(cr.request_date, dateform || ' HH24:MI:SS')
1096 from fnd_lookups ls, fnd_lookups lp, fnd_concurrent_programs_tl t,
1097 fnd_concurrent_programs b, fnd_concurrent_requests cr
1098 where lp.lookup_type = 'CP_PHASE_CODE' and
1099 lp.lookup_code = cr.phase_code and
1100 ls.lookup_type = 'CP_STATUS_CODE' and
1101 ls.lookup_code = cr.status_code and
1102 cr.requested_by = userid and
1103 b.concurrent_program_id = cr.concurrent_program_id and
1104 b.application_id = applid and
1105 B.CONCURRENT_PROGRAM_ID = T.CONCURRENT_PROGRAM_ID and
1106 B.APPLICATION_ID = T.APPLICATION_ID and
1107 T.LANGUAGE = userenv('LANG') and
1108 b.concurrent_program_name like prog_name_template
1109 order by cr.request_id desc;
1110
1111
1112
1113 userconcprogname varchar2(250);
1114 concprogname varchar2(50);
1115 argtext varchar2(250);
1116 requestid number;
1117 phase varchar2(50);
1118 status varchar2(50);
1119 devphase varchar2(1);
1120 devstatus varchar2(1);
1121 timestamp varchar2(50);
1122
1123 indarg varchar2(25);
1124 remargtext varchar2(250);
1125 oldremargtext varchar2(250);
1126 datestr varchar2(25);
1127
1128 begin
1129 open request_info;
1130 ii := 1;
1131 loop
1132
1133 -- Can't fetch directly into table type due to bug 334538,
1134 -- so we need to use temporary variables
1135
1136 fetch request_info into
1137 userconcprogname, concprogname, argtext, requestid, phase, status,
1138 devphase, devstatus, timestamp;
1139 exit when request_info%notfound;
1140
1141 -- remove trailing user_id argument
1142 -- argtext := substr(argtext,1,instr(argtext,',',-1,1)-1);
1143
1144 -- remove date format from argument string
1145 -- argtext := replace(argtext,'_'||dateform);
1146
1147
1148 user_conc_prog_names(ii) := userconcprogname;
1149 conc_prog_names(ii) := concprogname;
1150 arg_texts(ii) := argtext;
1151 request_ids(ii) := requestid;
1152 phases(ii) := phase;
1153 statuses(ii) := status;
1154 dev_phases(ii) := devphase;
1155 dev_statuses(ii) := devstatus;
1156 timestamps(ii) := timestamp;
1157
1158 ii := ii + 1;
1159 exit when ii = max_requests + 1;
1160 end loop;
1161 close request_info;
1162 num_requests := ii - 1;
1163
1164 end get_request_info;
1165
1166
1167
1168 procedure get_arguments (
1169 req_id in number,
1170 arg1 out nocopy varchar2,
1171 arg2 out nocopy varchar2,
1172 arg3 out nocopy varchar2,
1173 arg4 out nocopy varchar2,
1174 arg5 out nocopy varchar2,
1175 arg6 out nocopy varchar2,
1176 arg7 out nocopy varchar2,
1177 arg8 out nocopy varchar2,
1178 arg9 out nocopy varchar2,
1179 arg10 out nocopy varchar2,
1180 arg11 out nocopy varchar2,
1181 arg12 out nocopy varchar2,
1182 arg13 out nocopy varchar2,
1183 arg14 out nocopy varchar2,
1184 arg15 out nocopy varchar2,
1185 arg16 out nocopy varchar2,
1186 arg17 out nocopy varchar2,
1187 arg18 out nocopy varchar2,
1188 arg19 out nocopy varchar2,
1189 arg20 out nocopy varchar2,
1190 arg21 out nocopy varchar2,
1191 arg22 out nocopy varchar2,
1192 arg23 out nocopy varchar2,
1193 arg24 out nocopy varchar2,
1194 arg25 out nocopy varchar2) is
1195
1196
1197 begin
1198
1199 select argument1, argument2, argument3, argument4, argument5,
1200 argument6, argument7, argument8, argument9, argument10,
1201 argument11, argument12, argument13, argument14, argument15,
1202 argument16, argument17, argument18, argument19, argument20,
1203 argument21, argument22, argument23, argument24, argument25
1204 into arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10,
1205 arg11, arg12, arg13, arg14, arg15, arg16, arg17, arg18,
1206 arg19, arg20, arg21, arg22, arg23, arg24, arg25
1207 from fnd_concurrent_requests
1208 where request_id = req_id;
1209
1210 end get_arguments;
1211
1212
1213
1214 procedure add_dynamic_column (
1215 X_request_id in number,
1216 X_attribute_name in varchar2,
1217 X_column_name in varchar2,
1218 X_ordering in varchar2,
1219 X_BREAK in VARCHAR2,
1220 X_DISPLAY_LENGTH in NUMBER,
1221 X_DISPLAY_FORMAT in VARCHAR2,
1222 X_DISPLAY_STATUS in VARCHAR2,
1223 calling_fn in varchar2) is
1224 h_user_id number;
1225 h_login_id number;
1226 h_mesg_str varchar2(2000);
1227 begin
1228
1229 h_user_id := fnd_profile.value('USER_ID');
1230 h_login_id := fnd_profile.value('LOGIN_ID');
1231 insert into fa_rx_dynamic_columns (
1232 request_id, attribute_name, column_name, ordering, break,
1233 display_length, display_format, display_status, last_update_date,
1234 last_update_login, last_updated_by, created_by, creation_date)
1235 values (X_request_id, X_attribute_name, X_column_name, X_ordering,
1236 X_break, X_display_length, X_display_format, X_display_status,
1237 sysdate, h_login_id, h_user_id, h_user_id, sysdate);
1238
1239
1240 EXCEPTION
1241 when others then
1242 fnd_message.set_name('OFA','FA_FLEX_INSERT_FAILED');
1243 fnd_message.set_token('TABLE','FA_RX_DYNAMIC_COLUMNS',FALSE);
1244 h_mesg_str := fnd_message.get;
1245 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1246 raise;
1247
1248 end add_dynamic_column;
1249
1250 /* -------------------------------------------------------------------------------------------*/
1251 /* StatReq - The following two functions have been added for statutory reporting requirements */
1252 /* -------------------------------------------------------------------------------------------*/
1253
1254 PROCEDURE Initialize_Where(vset in out nocopy fnd_vset.valueset_r,v_flex_value in varchar2)
1255 is
1256
1257 where_clause long;
1258
1259 replace_string VARCHAR2(200);
1260 src VARCHAR2(100);
1261 value VARCHAR2(240);
1262 default_value VARCHAR2(240);
1263 idx NUMBER;
1264 ch VARCHAR2(10);
1265
1266 isprof BOOLEAN;
1267 flex_len NUMBER;
1268 prof_len NUMBER;
1269
1270 begin
1271 if vset.validation_type <> 'F' then return;
1272 elsif vset.table_info.where_clause is null then return;
1273 end if;
1274
1275 flex_len := length(':$FLEX$.');
1276 prof_len := length(':$PROFILES$.');
1277
1278 where_clause := vset.table_info.where_clause;
1279
1280 LOOP
1281 src := NULL;
1282 idx := instr(Upper(where_clause), ':$PROFILES$.');
1283 IF idx = 0 THEN
1284 idx := instr(Upper(where_clause), ':$FLEX$.');
1285
1286 IF idx <> 0 THEN
1287 isprof := FALSE;
1288 replace_string := substr(where_clause, idx, flex_len);
1289 idx := idx + flex_len;
1290 END IF;
1291 ELSE
1292 isprof := TRUE;
1293 replace_string := substr(where_clause, idx, prof_len);
1294 idx := idx + prof_len;
1295 END IF;
1296 EXIT WHEN idx = 0;
1297
1298 LOOP
1299 ch := substr(where_clause, idx, 1);
1300 EXIT WHEN ch IS NULL OR NOT (Upper(ch) BETWEEN 'A' AND 'Z' OR ch BETWEEN '0' and '9' OR ch = '_');
1301
1302 src := src || ch;
1303 idx := idx+1;
1304 END LOOP;
1305
1306 IF ch = ':' THEN
1307 idx := idx + 1;
1308 default_value := null;
1309 LOOP
1310 ch := substr(where_clause, idx, 1);
1311 EXIT WHEN ch IS NULL OR NOT (Upper(ch) BETWEEN 'A' AND 'Z' OR ch BETWEEN '0' and '9' OR ch = '_');
1312
1313 default_value := default_value || ch;
1314 idx := idx + 1;
1315 END LOOP;
1316 END IF;
1317
1318 value := null;
1319 IF isprof THEN
1320 fnd_profile.get(Upper(src), value);
1321 ELSE
1322 for i in 1..flex_val_count loop
1323 if upper(src) = flex_val_cache(i).flex_value_set_name then
1324 value := flex_val_cache(i).flex_value_id;
1325 exit;
1326 end if;
1327 end loop;
1328 END IF;
1329 if value is null then
1330 value := default_value;
1331 end if;
1332 if value is null then
1333 value := 'NULL';
1334 end if;
1335
1336 replace_string := replace_string||src;
1337
1338 IF (g_print_debug) THEN
1339 fa_rx_util_pkg.debug('Initialize_Where: ' || src);
1340 END IF;
1341
1342 where_clause := REPLACE(where_clause, replace_string, ''''||value||'''');
1343 END LOOP;
1344
1345 IF (g_print_debug) THEN
1346 fa_rx_util_pkg.debug('Initialize_Where: ' || where_clause);
1347 END IF;
1348
1349 if v_flex_value is Not null then
1350 fa_rx_util_pkg.debug('Initialize_Where: to change the where clause for long list ' ||where_clause);
1351 where_clause := upper(nvl(where_clause,'WHERE 1=1'));
1352 where_clause := substr(where_clause,instr(where_clause,'WHERE')+5);
1353 where_clause := ' WHERE to_char('||vset.table_info.value_column_name||') = '||''''||v_flex_value||''''||' AND '||where_clause;
1354 end if;
1355
1356 fa_rx_util_pkg.debug('Initialize_Where: after ** ' ||where_clause);
1357
1358 vset.table_info.where_clause := where_clause;
1359
1360 end initialize_where;
1361
1362 FUNCTION get_flex_val_meaning (
1363 v_flex_value_set_id IN NUMBER,
1364 v_flex_value_set_name IN VARCHAR2,
1365 v_flex_value IN VARCHAR2)
1366 RETURN VARCHAR2 IS
1367 vsid number;
1368 vset fnd_vset.valueset_r;
1369 fmt fnd_vset.valueset_dr;
1370 found BOOLEAN;
1371 row NUMBER;
1372 value fnd_vset.value_dr;
1373 meaning varchar2(240) := '';
1374 vsname varchar2(150);
1375
1376 i BINARY_INTEGER := 0;
1377 y BINARY_INTEGER := 0;
1378
1379 /* This function returns:
1380 - the meaning of a passed flex value, if the valueset is found and it has an entry
1381 for the flex value and the entry has a meaning associated with it.
1382 - the flex value, if the flex value passed is null or if the flex value is
1383 not found in the valueset or if both valueset parameters are NULL or if the
1384 flex value is found in the valueset but doesn't have a meaning */
1385
1386 BEGIN
1387
1388 /* Return NULL if flex value is null */
1389
1390 if (v_flex_value is null) then return (v_flex_value); end if;
1391
1392 /* If flex value set id is null and flex value set name is null too,
1393 return the flex value passed into the function.
1394
1395 If either of the flex value set id or the flex valueset name is null,
1396 select it and move on with the rest of the function. */
1397
1398 if (v_flex_value_set_name is null)
1399 then
1400 if (v_flex_value_set_id is null)
1401 then
1402 return(v_flex_value);
1403 else
1404 select flex_value_set_name
1405 into vsname
1406 from fnd_flex_value_sets
1407 where flex_value_set_id = v_flex_value_set_id;
1408
1409 vsid := v_flex_value_set_id;
1410
1411 end if;
1412 else
1413 if (v_flex_value_set_id is null)
1414 then
1415 select flex_value_set_id
1416 into vsid
1417 from fnd_flex_value_sets
1418 where flex_value_set_name = v_flex_value_set_name;
1419
1420 else
1421 vsid := v_flex_value_set_id;
1422 end if;
1423
1424 vsname := v_flex_value_set_name;
1425
1426 end if;
1427
1428 IF (g_print_debug) THEN
1429 fa_rx_util_pkg.debug('get_flex_val_meaning: ' || 'Caching values for value set '||vsname);
1430 END IF;
1431 /* Search PL/SQL tables for valuesets and values that have already been loaded */
1432
1433 if (g_value_set_counter >= 1)
1434 then
1435 FOR i in 1..g_value_set_counter LOOP
1436 if (g_value_set_tab(i).value_set_name = vsname)
1437 then
1438 FOR y in g_value_set_tab(i).from_counter..g_value_set_tab(i).to_counter LOOP
1439 if (g_values_tab(y).value = v_flex_value)
1440 then
1441
1442 meaning := nvl(g_values_tab(y).meaning, v_flex_value);
1443 return(meaning);
1444 end if;
1445 END LOOP;
1446 return(v_flex_value);
1447 end if;
1448 END LOOP;
1449 end if;
1450
1451 g_value_set_counter := g_value_set_counter + 1;
1452 g_value_set_tab(g_value_set_counter).value_set_name := vsname;
1453 g_value_set_tab(g_value_set_counter).from_counter := g_value_counter + 1;
1454 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter + 1;
1455
1456 /* Get valueset info */
1457 fnd_vset.get_valueset(vsid, vset, fmt);
1458
1459 If nvl(vset.validation_type,'*') = 'F' and nvl(fmt.longlist_flag,'N') = 'Y' Then
1460 /* Initialize WHERE Clause for Table validated value sets */
1461 Initialize_Where(vset,v_flex_value);
1462 Else
1463 Initialize_Where(vset,null);
1464 End If;
1465
1466 /* Initialize valueset variables */
1467 fnd_vset.get_value_init(vset, TRUE);
1468
1469 /* Fetch first value of valueset */
1470 fnd_vset.get_value(vset, row, found, value);
1471
1472 WHILE(found) LOOP
1473 /* Increase session flex value counter by 1 */
1474
1475 g_value_counter := g_value_counter + 1;
1476
1477 /* Store flex values away */
1478 fa_rx_util_pkg.debug(':Meaning = '||Nvl(value.meaning, '<<<NULL>>>')||', Value = '||value.value||':');
1479 g_values_tab(g_value_counter).meaning := substr(nvl(value.meaning, value.value), 1, 240);
1480 g_values_tab(g_value_counter).value := substr(Nvl(value.id, value.value), 1, 150);
1481 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter;
1482
1483 /* Check if fetched value matches the passed flex value, if yes
1484 store the meaning in variable meaning */
1485
1486 if (v_flex_value = Nvl(value.id, value.value))
1487 then
1488 meaning := nvl(value.meaning, value.value);
1489 end if;
1490
1491 /* Get next flex value in set */
1492
1493 fnd_vset.get_value(vset, row, found, value);
1494
1495 END LOOP;
1496
1497 fnd_vset.get_value_end(vset);
1498
1499 /* cache the value of this value set */
1500 flex_val_count := flex_val_count + 1;
1501 flex_val_cache(flex_val_count).flex_value_set_name := upper(vsname);
1502 flex_val_cache(flex_val_count).flex_value_id := v_flex_value;
1503
1504 /* Return meaning (if found) otherwise the flex value. */
1505
1506 return(nvl(meaning, v_flex_value));
1507
1508 EXCEPTION
1509 WHEN NO_DATA_FOUND THEN
1510 fnd_vset.get_value_end(vset);
1511 return(v_flex_value);
1512 WHEN OTHERS THEN
1513 IF (g_print_debug) THEN
1514 fa_rx_util_pkg.debug('EXCEPTION in get_flex_val_meaning');
1515 fa_rx_util_pkg.debug('get_flex_val_meaning: ' || sqlerrm);
1516 END IF;
1517 -- return(NULL);
1518 return(v_flex_value);
1519 END get_flex_val_meaning;
1520
1521
1522 --* Bug2991482, rravunny
1523 --* new overridden function to support parent value.
1524 --*
1525 FUNCTION get_flex_val_meaning (
1526 v_flex_value_set_id IN NUMBER,
1527 v_flex_value_set_name IN VARCHAR2,
1528 v_flex_value IN VARCHAR2,
1529 v_parent_flex_val IN VARCHAR2)
1530 RETURN VARCHAR2 IS
1531 vsid number;
1532 vset fnd_vset.valueset_r;
1533 fmt fnd_vset.valueset_dr;
1534 found BOOLEAN;
1535 row NUMBER;
1536 value fnd_vset.value_dr;
1537 meaning varchar2(240) := '';
1538 vsname varchar2(150);
1539
1540 i BINARY_INTEGER := 0;
1541 y BINARY_INTEGER := 0;
1542
1543 /* This function returns:
1544 - the meaning of a passed flex value, if the valueset is found and it has an entry
1545 for the flex value and the entry has a meaning associated with it.
1546 - the flex value, if the flex value passed is null or if the flex value is
1547 not found in the valueset or if both valueset parameters are NULL or if the
1548 flex value is found in the valueset but doesn't have a meaning */
1549
1550 BEGIN
1551
1552 /* Return NULL if flex value is null */
1553
1554 if (v_flex_value is null) then return (v_flex_value); end if;
1555
1556 /* If flex value set id is null and flex value set name is null too,
1557 return the flex value passed into the function.
1558
1559 If either of the flex value set id or the flex valueset name is null,
1560 select it and move on with the rest of the function. */
1561
1562 if (v_flex_value_set_name is null)
1563 then
1564 if (v_flex_value_set_id is null)
1565 then
1566 return(v_flex_value);
1567 else
1568 select flex_value_set_name
1569 into vsname
1570 from fnd_flex_value_sets
1571 where flex_value_set_id = v_flex_value_set_id;
1572
1573 vsid := v_flex_value_set_id;
1574
1575 end if;
1576 else
1577 if (v_flex_value_set_id is null)
1578 then
1579 select flex_value_set_id
1580 into vsid
1581 from fnd_flex_value_sets
1582 where flex_value_set_name = v_flex_value_set_name;
1583
1584 else
1585 vsid := v_flex_value_set_id;
1586 end if;
1587
1588 vsname := v_flex_value_set_name;
1589
1590 end if;
1591
1592 /* Search PL/SQL tables for valuesets and values that have already been loaded */
1593
1594 if (g_value_set_counter >= 1)
1595 then
1596 FOR i in 1..g_value_set_counter LOOP
1597 if (g_value_set_tab(i).value_set_name = vsname)
1598 then
1599 FOR y in g_value_set_tab(i).from_counter..g_value_set_tab(i).to_counter LOOP
1600 --* if dependant value set.
1601 if (v_parent_flex_val is not null and g_values_tab(y).value = v_flex_value
1602 and g_values_tab(y).parent_flex_value_low = v_parent_flex_val)
1603 then
1604 meaning := nvl(g_values_tab(y).meaning, v_flex_value);
1605 return(meaning);
1606 end if;
1607 --* if other than dependant value set.
1608
1609 if (g_values_tab(y).value = v_flex_value and v_parent_flex_val is null)
1610 then
1611 meaning := nvl(g_values_tab(y).meaning, v_flex_value);
1612 return(meaning);
1613 end if;
1614 END LOOP;
1615 return(v_flex_value);
1616 end if;
1617
1618 END LOOP;
1619 end if;
1620 g_value_set_counter := g_value_set_counter + 1;
1621 g_value_set_tab(g_value_set_counter).value_set_name := vsname;
1622 g_value_set_tab(g_value_set_counter).from_counter := g_value_counter + 1;
1623 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter + 1;
1624
1625 /* Get valueset info */
1626 fnd_vset.get_valueset(vsid, vset, fmt);
1627
1628 If nvl(vset.validation_type,'*') = 'F' and nvl(fmt.longlist_flag,'N') = 'Y' Then
1629 /* Initialize WHERE Clause for Table validated value sets */
1630 Initialize_Where(vset,v_flex_value);
1631 Else
1632 Initialize_Where(vset,null);
1633 End If;
1634
1635 /* Initialize valueset variables */
1636 fnd_vset.get_value_init(vset, TRUE);
1637
1638 /* Fetch first value of valueset */
1639 fnd_vset.get_value(vset, row, found, value);
1640
1641 WHILE(found) LOOP
1642 /* Increase session flex value counter by 1 */
1643
1644 g_value_counter := g_value_counter + 1;
1645
1646 /* Store flex values away */
1647
1648 g_values_tab(g_value_counter).meaning := substr(nvl(value.meaning, value.value), 1, 240);
1649 g_values_tab(g_value_counter).value := substr(Nvl(value.id, value.value), 1, 150);
1650 g_values_tab(g_value_counter).parent_flex_value_low := value.parent_flex_value_low;
1651 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter;
1652
1653 /* Check if fetched value matches the passed flex value, if yes
1654 store the meaning in variable meaning */
1655
1656 if (v_flex_value = Nvl(value.id, value.value) and v_parent_flex_val is not null and v_parent_flex_val = value.parent_flex_value_low)
1657 then
1658 meaning := nvl(value.meaning, value.value);
1659 end if;
1660
1661 if (v_flex_value = Nvl(value.id, value.value) and v_parent_flex_val is null)
1662 then
1663 meaning := nvl(value.meaning, value.value);
1664 end if;
1665 /* Get next flex value in set */
1666 fnd_vset.get_value(vset, row, found, value);
1667
1668 END LOOP;
1669
1670 fnd_vset.get_value_end(vset);
1671 /* cache the value of this value set */
1672 flex_val_count := flex_val_count + 1;
1673 flex_val_cache(flex_val_count).flex_value_set_name := upper(vsname);
1674 flex_val_cache(flex_val_count).flex_value_id := v_flex_value;
1675 /* Return meaning (if found) otherwise the flex value. */
1676
1677 return(nvl(meaning, v_flex_value));
1678
1679 EXCEPTION
1680 WHEN NO_DATA_FOUND THEN
1681 fnd_vset.get_value_end(vset);
1682 return(v_flex_value);
1683 WHEN OTHERS THEN
1684 fnd_vset.get_value_end(vset);
1685 return(v_flex_value);
1686 END get_flex_val_meaning;
1687
1688 FUNCTION get_asset_info (
1689 v_info_type IN VARCHAR2,
1690 v_asset_id IN NUMBER,
1691 v_from_date IN DATE,
1692 v_to_date IN DATE,
1693 v_book_type_code IN VARCHAR2,
1694 v_balancing_segment IN VARCHAR2)
1695 return VARCHAR2 is
1696
1697 CURSOR c_vendor_name (c_asset_id NUMBER, c_to_date DATE) IS
1698 select
1699 distinct v.vendor_name
1700 from
1701 po_vendors v,
1702 fa_asset_invoices i
1703 where
1704 i.asset_id = c_asset_id and
1705 i.date_effective <= c_to_date and
1706 nvl(i.date_ineffective,
1707 sysdate) > c_to_date
1708 and
1709 v.vendor_id = i.po_vendor_id;
1710
1711 CURSOR c_invoice (c_asset_id NUMBER, c_to_date DATE) IS
1712 select
1713 distinct ap_i.invoice_num, ap_i.description
1714 from
1715 ap_invoices_all ap_i,
1716 fa_asset_invoices i
1717 where
1718 i.asset_id = c_asset_id and
1719
1720 i.date_effective <= c_to_date and
1721 nvl(i.date_ineffective,
1722 sysdate) > c_to_date
1723 and
1724 ap_i.invoice_id = i.invoice_id;
1725
1726 CURSOR c_retirement_type (c_asset_id NUMBER, c_from_date DATE, c_to_date DATE, c_book_type_code VARCHAR2) IS
1727 select
1728 distinct lu.meaning
1729 from
1730 fa_lookups lu,
1731 fa_transaction_headers th,
1732 fa_retirements r
1733 where
1734 r.asset_id = c_asset_id and
1735 r.book_type_code = c_book_type_code and
1736 th.transaction_header_id = r.transaction_header_id_in and
1737 th.date_effective between c_from_date and c_to_date
1738 and
1739 lu.lookup_type = 'RETIREMENT' and
1740 lu.lookup_code = r.retirement_type_code;
1741
1742 CURSOR c_location (c_asset_id NUMBER, c_to_date DATE, c_book_type_code VARCHAR2) IS
1743 select
1744 distinct dh.location_id, dh.code_combination_id
1745 from
1746 fa_distribution_history dh
1747 where
1748 dh.asset_id = c_asset_id and
1749 dh.book_type_code = c_book_type_code and
1750 dh.date_effective <= c_to_date and
1751 nvl(dh.date_ineffective,
1752 sysdate) > c_to_date;
1753
1754
1755 h_vendor_name VARCHAR2(240);
1756 h_invoice_number VARCHAR2(50);
1757 h_invoice_descr VARCHAR2(240);
1758 h_retirement_type VARCHAR2(80);
1759 h_location VARCHAR2(240);
1760 h_location_id NUMBER;
1761 h_loc_segs fa_rx_shared_pkg.Seg_Array;
1762 h_ccid NUMBER;
1763
1764 first_vendor BOOLEAN := TRUE;
1765 first_invoice_number BOOLEAN := TRUE;
1766 first_invoice_descr BOOLEAN := TRUE;
1767 first_retirement_type BOOLEAN := TRUE;
1768 first_location BOOLEAN := TRUE;
1769
1770 concat_vendor_name VARCHAR2(1000);
1771 concat_invoice_number VARCHAR2(1000);
1772 concat_invoice_descr VARCHAR2(1000);
1773 concat_retirement_type VARCHAR2(1000);
1774 concat_location VARCHAR2(1000);
1775
1776 acct_all_segs fa_rx_shared_pkg.Seg_Array;
1777 n_segs number;
1778 gl_balancing_seg number;
1779 gl_account_seg number;
1780 fa_cost_ctr_seg number;
1781 max_length number := 500;
1782
1783
1784 BEGIN
1785
1786 /* Get location flex structure if it's not there already */
1787
1788 if (g_loc_flex_struct is NULL)
1789 then
1790 select location_flex_structure
1791 into g_loc_flex_struct
1792 from fa_system_controls;
1793 end if;
1794
1795 /* Get vendor name */
1796
1797 if (v_info_type = 'VENDOR_NAME')
1798 then
1799 open c_vendor_name (v_asset_id, v_to_date);
1800 loop
1801 fetch c_vendor_name into h_vendor_name;
1802 if (c_vendor_name%NOTFOUND) then exit; end if;
1803 if (first_vendor)
1804 then
1805 concat_vendor_name := h_vendor_name;
1806 first_vendor := FALSE;
1807 else
1808 if (length(concat_vendor_name || ', ' || h_vendor_name ) > max_length)
1809 then
1810 exit;
1811 else
1812 concat_vendor_name := concat_vendor_name || ', ' || h_vendor_name ;
1813 end if;
1814 end if;
1815 end loop;
1816 close c_vendor_name;
1817 return(concat_vendor_name);
1818 end if;
1819
1820 /* Get invoice */
1821
1822 if (v_info_type = 'INVOICE_NUMBER')
1823 then
1824
1825 open c_invoice (v_asset_id, v_to_date);
1826 loop
1827 fetch c_invoice into h_invoice_number, h_invoice_descr;
1828 if (c_invoice%NOTFOUND) then exit; end if;
1829 if (first_invoice_number)
1830 then
1831 concat_invoice_number := h_invoice_number;
1832 first_invoice_number := FALSE;
1833 else
1834 if (length(concat_invoice_number || ', ' || h_invoice_number) > max_length)
1835 then
1836 exit;
1837 else
1838 concat_invoice_number := concat_invoice_number || ', ' || h_invoice_number ;
1839 end if;
1840 end if;
1841 end loop;
1842 close c_invoice;
1843 return(concat_invoice_number);
1844 end if;
1845
1846 /* Get invoice description */
1847
1848 if (v_info_type = 'INVOICE_DESCR')
1849 then
1850 open c_invoice (v_asset_id, v_to_date);
1851 loop
1852
1853 fetch c_invoice into h_invoice_number, h_invoice_descr;
1854 if (c_invoice%NOTFOUND) then exit; end if;
1855
1856 if (h_invoice_descr is not null)
1857 then
1858 if (first_invoice_descr)
1859 then
1860 concat_invoice_descr := h_invoice_descr;
1861 first_invoice_descr := FALSE;
1862 else
1863 if (length(concat_invoice_descr || ', ' || h_invoice_descr) > max_length)
1864 then
1865 exit;
1866 else
1867 concat_invoice_descr := concat_invoice_descr || ', ' || h_invoice_descr;
1868 end if;
1869 end if;
1870 end if;
1871
1872 end loop;
1873 close c_invoice;
1874 return(concat_invoice_descr);
1875 end if;
1876
1877 /* Get retirement type */
1878
1879 if (v_info_type = 'RETIREMENT_TYPE')
1880 then
1881 open c_retirement_type (v_asset_id, v_from_date, v_to_date, v_book_type_code);
1882 loop
1883 fetch c_retirement_type into h_retirement_type;
1884 if (c_retirement_type%NOTFOUND) then exit; end if;
1885 if (first_retirement_type)
1886 then
1887 concat_retirement_type := h_retirement_type;
1888 first_retirement_type := FALSE;
1889 else
1890 if (length(concat_retirement_type || ', ' || h_retirement_type) > max_length)
1891 then
1892 exit;
1893 else
1894 concat_retirement_type := concat_retirement_type || ', ' || h_retirement_type;
1895 end if;
1896 end if;
1897 end loop;
1898 close c_retirement_type;
1899 return(concat_retirement_type);
1900 end if;
1901
1902 /* Get location */
1903
1904 if (v_info_type = 'LOCATION')
1905 then
1906
1907 /* Get accounting flexfield's segment numbers */
1908
1909 fa_rx_shared_pkg.get_acct_segment_numbers (
1910 BOOK => v_book_type_code,
1911 BALANCING_SEGNUM => gl_balancing_seg,
1912 ACCOUNT_SEGNUM => gl_account_seg,
1913 CC_SEGNUM => fa_cost_ctr_seg,
1914 CALLING_FN => 'FA_BALANCES_REPORT');
1915
1916 open c_location (v_asset_id, v_to_date, v_book_type_code);
1917 loop
1918 fetch c_location into h_location_id, h_ccid;
1919
1920 if (c_location%NOTFOUND) then exit; end if;
1921
1922 /* Get accounting flexfield segment values */
1923
1924 fa_rx_shared_pkg.get_acct_segments (
1925 combination_id => h_ccid,
1926 n_segments => n_segs,
1927 segments => acct_all_segs,
1928 calling_fn => 'FA_BALANCES_REPORT');
1929
1930 /* Add location only if it is for the appropriate balancing segment */
1931
1932 if (acct_all_segs(gl_balancing_seg) = v_balancing_segment)
1933 then
1934
1935 /* Get concatenated location */
1936
1937 fa_rx_shared_pkg.concat_location (
1938 struct_id => g_loc_flex_struct,
1939 ccid => h_location_id,
1940 concat_string => h_location,
1941 segarray => h_loc_segs);
1942
1943 if (first_location)
1944 then
1945 concat_location := h_location;
1946 first_location := FALSE;
1947 else
1948
1949 if (length(concat_location || ', ' || h_location) > max_length) then
1950 exit;
1951 else
1952 concat_location := concat_location || ', ' || h_location;
1953 end if;
1954
1955 end if;
1956 end if;
1957
1958 end loop;
1959
1960 close c_location;
1961
1962 return(concat_location);
1963
1964 end if;
1965
1966 END get_asset_info;
1967
1968 PROCEDURE clear_flex_val_cache
1969 IS
1970 BEGIN
1971 flex_val_count := 0;
1972 END clear_flex_val_cache;
1973
1974 END FA_RX_SHARED_PKG;