[Home] [Help]
PACKAGE BODY: APPS.FA_RX_SHARED_PKG
Source
1 PACKAGE BODY FA_RX_SHARED_PKG as
2 /* $Header: farxb.pls 120.21 2006/02/02 06:25:46 tdewanga 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 errbuf out nocopy varchar2,
362 retcode out nocopy number) IS
363
364 operation varchar2(200);
365 dist_book varchar2(15);
366 ucd date;
367 upc number;
368 tod date;
369 tpc number;
370
371
372 h_mesg_name varchar2(50);
373 h_mesg_str varchar2(2000);
374 h_table_token varchar2(30);
375
376 BEGIN
377
378 h_mesg_name := 'FA_SHARED_DELETE_FAILED';
379 h_table_token := 'FA_RESERVE_LEDGER_GT';
380
381 /*
382 no longer needed when using global temp table
383 DELETE FROM FA_RESERVE_LEDGER;
384
385 if (SQL%ROWCOUNT > 0) then
386 COMMIT;
387 else
388 ROLLBACK;
389 end if;
390 */
391
392 h_mesg_name := 'FA_AMT_SEL_DP';
393
394 SELECT
395 BC.DISTRIBUTION_SOURCE_BOOK dbk,
396 nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
397 DP.PERIOD_COUNTER upc,
398 min (DP_FY.PERIOD_OPEN_DATE) tod,
399 min (DP_FY.PERIOD_COUNTER) tpc
400 INTO
401 dist_book,
402 ucd,
403 upc,
404 tod,
405 tpc
406 FROM
407 FA_DEPRN_PERIODS DP,
408 FA_DEPRN_PERIODS DP_FY,
409 FA_BOOK_CONTROLS BC
410 WHERE
411 DP.BOOK_TYPE_CODE = book AND
412 DP.PERIOD_NAME = period AND
413 DP_FY.BOOK_TYPE_CODE = book AND
414 DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
415 AND BC.BOOK_TYPE_CODE = book
416 GROUP BY
417 BC.DISTRIBUTION_SOURCE_BOOK,
418 DP.PERIOD_CLOSE_DATE,
419 DP.PERIOD_COUNTER;
420
421 operation := 'Inserting into FA_RESERVE_LEDGER_GT';
422
423 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
424 h_table_token := 'FA_RESERVE_LEDGER_GT';
425
426 INSERT INTO FA_RESERVE_LEDGER_GT
427 (ASSET_ID,
428 DH_CCID,
429 DEPRN_RESERVE_ACCT,
430 DATE_PLACED_IN_SERVICE,
431 METHOD_CODE,
432 LIFE,
433 RATE,
434 CAPACITY,
435 COST,
436 DEPRN_AMOUNT,
437 YTD_DEPRN,
438 DEPRN_RESERVE,
442 DATE_EFFECTIVE,
439 PERCENT,
440 TRANSACTION_TYPE,
441 PERIOD_COUNTER,
443 DISTRIBUTION_ID)
444 SELECT
445 DH.ASSET_ID ASSET_ID,
446 DH.CODE_COMBINATION_ID DH_CCID,
447 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
448 BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
449 BOOKS.DEPRN_METHOD_CODE METHOD,
450 BOOKS.LIFE_IN_MONTHS LIFE,
451 BOOKS.ADJUSTED_RATE RATE,
452 BOOKS.PRODUCTION_CAPACITY CAPACITY,
453 DD.COST COST,
454 decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
455 DEPRN_AMOUNT,
456 decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
457 YTD_DEPRN,
458 DD.DEPRN_RESERVE DEPRN_RESERVE,
459 round (decode (TH.TRANSACTION_TYPE_CODE, null,
460 DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
461 PERCENT,
462 decode (TH.TRANSACTION_TYPE_CODE, null,
463 decode (TH_RT.TRANSACTION_TYPE_CODE,
464 'FULL RETIREMENT', 'F',
465 decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
466 'TRANSFER', 'T',
467 'TRANSFER OUT', 'P',
468 'RECLASS', 'R') T_TYPE,
469 DD.PERIOD_COUNTER,
470 NVL(TH.DATE_EFFECTIVE, ucd),
471 DH.DISTRIBUTION_ID
472 FROM
473 FA_DEPRN_DETAIL DD,
474 FA_ASSET_HISTORY AH,
475 FA_TRANSACTION_HEADERS TH,
476 FA_TRANSACTION_HEADERS TH_RT,
477 FA_BOOKS BOOKS,
478 FA_DISTRIBUTION_HISTORY DH,
479 FA_CATEGORY_BOOKS CB
480 WHERE
481 CB.BOOK_TYPE_CODE = book AND
482 CB.CATEGORY_ID = AH.CATEGORY_ID
483 AND
484 AH.ASSET_ID = DH.ASSET_ID AND
485 AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
486 nvl(AH.DATE_INEFFECTIVE,sysdate)
487 >= nvl(TH.DATE_EFFECTIVE, ucd) AND
488 -- AH.ASSET_TYPE = 'CAPITALIZED'
489 ( ( AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP') AND
490 BOOKS.GROUP_ASSET_ID is null
491 ) OR
492 ( AH.ASSET_TYPE = 'CAPITALIZED' AND
493 BOOKS.GROUP_ASSET_ID is not null
494 and exists (select 1
495 from fa_books oldbk
496 , fa_transaction_headers oldth
497 , fa_deprn_periods dp
498 where oldbk.transaction_header_id_out = books.transaction_header_id_in
499 and oldbk.transaction_header_id_out = oldth.transaction_header_id
500 and dp.book_type_code = book
501 and dp.period_counter = dd.period_counter
502 and oldth.date_effective between dp.period_open_date
503 and nvl(dp.period_close_date, oldth.date_effective)
504 and oldbk.group_asset_id is null)
505 ) OR
506 ( nvl(report_style,'S') = 'D' AND
507 AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP')
508 )
509 )
510 AND
511 DD.BOOK_TYPE_CODE = book AND
512 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
513 DD.PERIOD_COUNTER =
514 (SELECT max (DD_SUB.PERIOD_COUNTER)
515 FROM FA_DEPRN_DETAIL DD_SUB
516 WHERE DD_SUB.BOOK_TYPE_CODE = book
517 AND DD_SUB.ASSET_ID = DH.ASSET_ID
518 AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
519 AND DD_SUB.PERIOD_COUNTER <= upc)
520 AND
521 TH_RT.BOOK_TYPE_CODE = book AND
522 TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
523 AND
524 BOOKS.BOOK_TYPE_CODE = book AND
525 BOOKS.ASSET_ID = DH.ASSET_ID AND
526 -- Commented for bugfix #4610445
527 -- nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
528 -- Added for bugfix #4610445
529 nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= upc AND
530 BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
531 nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
532 AND
533 TH.BOOK_TYPE_CODE (+) = dist_book AND
534 TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
535 TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
536 AND
537 DH.BOOK_TYPE_CODE = dist_book AND
538 -- Commented for bugfix #4610445
539 -- DH.DATE_EFFECTIVE <= ucd AND
540 -- nvl(DH.DATE_INEFFECTIVE, sysdate) > tod and
541 -- Added for bugfix #4610445
542 ucd between dh.date_effective and nvl(dh.date_ineffective,ucd);
543
544
545 retcode := 1;
546
547 exception
548 when others then
549 retcode := 2;
550 fnd_message.set_name('OFA',h_mesg_name);
551 if h_mesg_name in ('FA_FLEX_DELETE_FAILED','FA_FLEX_INSERT_FAILED') then
552 fnd_message.set_token('TABLE',h_table_token,FALSE);
553 end if;
554 h_mesg_str := fnd_message.get;
555 fa_rx_conc_mesg_pkg.log(h_mesg_str);
556
557 end fa_rsvldg;
558
562 table_name in varchar2,
559
560 procedure concat_general (
561 table_id in number,
563 ccid_col_name in varchar2,
564 struct_id in number,
565 flex_code in varchar2,
566 ccid in number,
567 appl_id in number,
568 appl_short_name in varchar2,
569 concat_string in out nocopy varchar2,
570 segarray in out nocopy Seg_Array) is
571
572 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl
573 cursor segcolumns is
574 select g.application_column_name, g.segment_num
575 from fnd_columns c, fnd_id_flex_segments g
576 WHERE g.application_id = appl_id
577 AND g.id_flex_code = flex_code
578 AND g.id_flex_num = struct_id
579 AND g.enabled_flag = 'Y'
580 AND c.application_id = appl_id
581 AND c.table_id = table_id
582 AND c.column_name = g.application_column_name
583 group by g.application_column_name, g.segment_num
584 ORDER BY g.segment_num;
585
586 i number;
587 delim varchar2(1);
588 col_name varchar2(25);
589
590 num_segs integer;
591 seg_ctr integer;
592
593 v_cursorid integer;
594 v_sqlstmt varchar2(500);
595 v_return integer;
596
597 h_mesg_name varchar2(30);
598 h_mesg_str varchar2(2000);
599
600 l_use_global_table varchar2(10);
601
602 BEGIN
603
604 if (fa_rx_shared_pkg.g_seg_count = 0) then
605 l_use_global_table := 'NO';
606 else
607 l_use_global_table := 'YES';
608 end if;
609
610 concat_string := '';
611
612 h_mesg_name := 'FA_BUDGET_NO_SEG_DELIM';
613
614 num_segs := 0;
615 seg_ctr := 0;
616
617 v_sqlstmt := 'select ';
618
619
620 h_mesg_name := 'FA_SHARED_FLEX_SEGCOLUMNS';
621
622
623 -- global table is currently initialized when called
624 -- from what if, to improve performance. When time
625 -- allows all other RX procedures should use the
626 -- global table instead of selects same data over and over.
627 -- What needs to be done is to find all entry points and
628 -- fill the global table there.
629
630 if l_use_global_table = 'NO' then
631
632 Select s.concatenated_segment_delimiter into delim
633 FROM fnd_id_flex_structures s, fnd_application a
634 WHERE s.application_id = a.application_id
635 AND s.id_flex_code = flex_code
636 AND s.id_flex_num = struct_id
637 AND a.application_short_name = appl_short_name;
638
639 open segcolumns;
640 loop
641
642 fetch segcolumns into col_name, v_return;
643
644 if (segcolumns%NOTFOUND) then exit; end if;
645
646 v_sqlstmt := v_sqlstmt || col_name || ', ';
647 num_segs := num_segs + 1;
648
649 segarray(num_segs) := 'seeded';
650
651 end loop;
652 close segcolumns;
653
654
655 else
656 FOR i IN fa_rx_shared_pkg.g_seg_table.FIRST .. fa_rx_shared_pkg.g_seg_table.LAST LOOP
657
658 if table_name = fa_rx_shared_pkg.g_seg_table(i).tabname then
659
660
661 v_sqlstmt := v_sqlstmt || fa_rx_shared_pkg.g_seg_table(i).colname || ', ';
662 num_segs := num_segs + 1;
663 delim := fa_rx_shared_pkg.g_seg_table(i).delimiter;
664
665 segarray(num_segs) := 'seeded';
666
667 end if;
668
669 end loop;
670 end if;
671 --
672
673
674 h_mesg_name := 'FA_SHARED_FLEX_DYNAMIC_SQL';
675
676 v_sqlstmt := rtrim(v_sqlstmt,', ');
677 v_sqlstmt := v_sqlstmt || ' from ' || table_name;
678 v_sqlstmt := v_sqlstmt || ' where ' || ccid_col_name || ' = ';
679 v_sqlstmt := v_sqlstmt || to_char(ccid);
680
681
682 v_cursorid := dbms_sql.open_cursor;
683 dbms_sql.parse(v_cursorid, v_sqlstmt, DBMS_SQL.V7);
684
685 for seg_ctr in 1 .. num_segs loop
686
687 dbms_sql.define_column(v_cursorid, seg_ctr, segarray(seg_ctr), 30);
688
689 end loop;
690
691 v_return := dbms_sql.execute(v_cursorid);
692 v_return := dbms_sql.fetch_rows(v_cursorid);
693
694 for seg_ctr in 1 .. num_segs loop
695 dbms_sql.column_value(v_cursorid, seg_ctr, segarray(seg_ctr));
696
697 end loop;
698
699 for seg_ctr in 1 .. num_segs loop
700 concat_string := concat_string || segarray(seg_ctr) || delim;
701
702 end loop;
703
704 concat_string := rtrim(concat_string,delim);
705
706 dbms_sql.close_cursor(v_cursorid);
707
708 exception
709 when others then
710
711 fnd_message.set_name('OFA',h_mesg_name);
712 if h_mesg_name like 'FA_SHARED_FLEX%' then
713 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
714 fnd_message.set_token('FLEX_CODE',flex_Code,FALSE);
715 end if;
716 h_mesg_str := fnd_message.get;
717 fa_rx_conc_mesg_pkg.log(h_mesg_str);
718 raise;
719
720 end concat_general;
721
722 procedure concat_category (
723 struct_id in number,
724 ccid in number,
725 concat_string in out nocopy varchar2,
726 segarray in out nocopy Seg_Array) is
727
731
728 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
729 h_table_id number;
730 h_id_flex_code varchar2(4);
732 h_mesg_name varchar2(30);
733 h_mesg_str varchar2(2000);
734
735 begin
736
737 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
738
739 select table_id into h_table_id from fnd_tables
740 where table_name = 'FA_CATEGORIES_B' and application_id = 140;
741
742 concat_general (
743 table_id => h_table_id,
744 table_name => 'FA_CATEGORIES_B',
745 ccid_col_name => 'CATEGORY_ID',
746 struct_id => struct_id,
747 flex_code => 'CAT#',
748 ccid => ccid,
749 appl_id => 140,
750 appl_short_name => 'OFA',
751 concat_string => concat_string,
752 segarray => segarray);
753
754 -- seg_table => seg_table,
755 exception
756 when others then
757
758 fnd_message.set_name('OFA',h_mesg_name);
759 if h_mesg_name like 'FA_SHARED_FLEX%' then
760 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
761 fnd_message.set_token('FLEX_CODE','CAT#',FALSE);
762 end if;
763 h_mesg_str := fnd_message.get;
764 fa_rx_conc_mesg_pkg.log(h_mesg_str);
765 raise;
766
767 end concat_category;
768
769 procedure concat_location (
770 struct_id in number,
771 ccid in number,
772 concat_string in out nocopy varchar2,
773 segarray in out nocopy Seg_Array) is
774
775 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
776
777 h_id_flex_code varchar2(4);
778 h_table_id number;
779
780 h_mesg_name varchar2(30);
781 h_mesg_str varchar2(2000);
782
783 begin
784
785 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
786
787
788 select table_id into h_table_id from fnd_tables
789 where table_name = 'FA_LOCATIONS' and application_id = 140;
790
791 concat_general (
792 table_id => h_table_id,
793 table_name => 'FA_LOCATIONS',
794 ccid_col_name => 'LOCATION_ID',
795 struct_id => struct_id,
796 flex_code => 'LOC#',
797 ccid => ccid,
798 appl_id => 140,
799 appl_short_name => 'OFA',
800 concat_string => concat_string,
801 segarray => segarray);
802
803 -- seg_table => seg_table,
804
805 exception
806 when others then
807
808 fnd_message.set_name('OFA',h_mesg_name);
809 if h_mesg_name like 'FA_SHARED_FLEX%' then
810 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
811 fnd_message.set_token('FLEX_CODE','LOC#',FALSE);
812 end if;
813 h_mesg_str := fnd_message.get;
814 fa_rx_conc_mesg_pkg.log(h_mesg_str);
815 raise;
816
817 end concat_location;
818
819 procedure concat_asset_key (
820 struct_id in number,
821 ccid in number,
822 concat_string in out nocopy varchar2,
823 segarray in out nocopy Seg_Array) is
824
825 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
826
827 h_id_flex_code varchar2(4);
828 h_table_id number;
829
830 h_mesg_name varchar2(30);
831 h_mesg_str varchar2(2000);
832
833 begin
834 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
835
836
837 select table_id into h_table_id from fnd_tables
838 where table_name = 'FA_ASSET_KEYWORDS' and application_id = 140;
839
840 concat_general (
841 table_id => h_table_id,
842 table_name => 'FA_ASSET_KEYWORDS',
843 ccid_col_name => 'CODE_COMBINATION_ID',
844 struct_id => struct_id,
845 flex_code => 'KEY#',
846 ccid => ccid,
847 appl_id => 140,
848 appl_short_name => 'OFA',
849 concat_string => concat_string,
850 segarray => segarray);
851
852 -- seg_table => seg_table,
853
854 exception
855 when others then
856
857 fnd_message.set_name('OFA',h_mesg_name);
858 if h_mesg_name like 'FA_SHARED_FLEX%' then
859 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
860 fnd_message.set_token('FLEX_CODE','KEY#',FALSE);
861 end if;
862 h_mesg_str := fnd_message.get;
863 fa_rx_conc_mesg_pkg.log(h_mesg_str);
864 raise;
865
866 end concat_asset_key;
867
868 procedure concat_acct (
869 struct_id in number,
870 ccid in number,
871 concat_string in out nocopy varchar2,
872 segarray in out nocopy Seg_Array) is
873
874 -- seg_table in fa_whatif_deprn2_pkg.seg_data_tbl,
875
876 h_id_flex_code varchar2(4);
877 h_table_id number;
878
879 h_mesg_name varchar2(30);
880 h_mesg_str varchar2(2000);
881
882 begin
883
884 h_mesg_name := 'FA_SHARED_FLEX_UNHANDLED';
885
886 select table_id into h_table_id from fnd_tables
887 where table_name = 'GL_CODE_COMBINATIONS' and application_id = 101;
888
889 concat_general (
890 table_id => h_table_id,
891 table_name => 'GL_CODE_COMBINATIONS',
892 ccid_col_name => 'CODE_COMBINATION_ID',
893 struct_id => struct_id,
894 flex_code => 'GL#',
895 ccid => ccid,
899 segarray => segarray);
896 appl_id => 101,
897 appl_short_name => 'SQLGL',
898 concat_string => concat_string,
900 -- seg_table => seg_table,
901
902 exception
903 when others then
904
905 fnd_message.set_name('OFA',h_mesg_name);
906 if h_mesg_name like 'FA_SHARED_FLEX%' then
907 fnd_message.set_token('STRUCT_ID',struct_id,FALSE);
908 fnd_message.set_token('FLEX_CODE','GL#',FALSE);
909 end if;
910 h_mesg_str := fnd_message.get;
911 fa_rx_conc_mesg_pkg.log(h_mesg_str);
912 raise;
913
914
915 end concat_acct;
916
917
918 -- This procedure, get_request_info doesn't seem to be called
919 -- from anywhere, at some point try to remove it.
920 procedure get_request_info (
921 userid in number,
922 prog_name_template in varchar2,
923 max_requests in number,
924 dateform in varchar2,
925 applid in number,
926 user_conc_prog_names out nocopy largevarchar2table,
927 conc_prog_names out nocopy varchar2table,
928 arg_texts out nocopy largevarchar2table,
929 request_ids out nocopy numbertable,
930 phases out nocopy varchar2table,
931 statuses out nocopy varchar2table,
932 dev_phases out nocopy smallvarchar2table,
933 dev_statuses out nocopy smallvarchar2table,
934 timestamps out nocopy varchar2table,
935 num_requests out nocopy number) is
936
937 ii integer;
938
939 cursor request_info is
940 select ltrim(ltrim(t.user_concurrent_program_name, 'RX-only:')),
941 b.concurrent_program_name, cr.argument_text,
942 cr.request_id, lp.meaning, ls.meaning, cr.phase_code, cr.status_code,
943 to_char(cr.request_date, dateform || ' HH24:MI:SS')
944 from fnd_lookups ls, fnd_lookups lp, fnd_concurrent_programs_tl t,
945 fnd_concurrent_programs b, fnd_concurrent_requests cr
946 where lp.lookup_type = 'CP_PHASE_CODE' and
947 lp.lookup_code = cr.phase_code and
948 ls.lookup_type = 'CP_STATUS_CODE' and
949 ls.lookup_code = cr.status_code and
950 cr.requested_by = userid and
951 b.concurrent_program_id = cr.concurrent_program_id and
952 b.application_id = applid and
953 B.CONCURRENT_PROGRAM_ID = T.CONCURRENT_PROGRAM_ID and
954 B.APPLICATION_ID = T.APPLICATION_ID and
955 T.LANGUAGE = userenv('LANG') and
956 b.concurrent_program_name like prog_name_template
957 order by cr.request_id desc;
958
959
960
961 userconcprogname varchar2(250);
962 concprogname varchar2(50);
963 argtext varchar2(250);
964 requestid number;
965 phase varchar2(50);
966 status varchar2(50);
967 devphase varchar2(1);
968 devstatus varchar2(1);
969 timestamp varchar2(50);
970
971 indarg varchar2(25);
972 remargtext varchar2(250);
973 oldremargtext varchar2(250);
974 datestr varchar2(25);
975
976 begin
977 open request_info;
978 ii := 1;
979 loop
980
981 -- Can't fetch directly into table type due to bug 334538,
982 -- so we need to use temporary variables
983
984 fetch request_info into
985 userconcprogname, concprogname, argtext, requestid, phase, status,
986 devphase, devstatus, timestamp;
987 exit when request_info%notfound;
988
989 -- remove trailing user_id argument
990 -- argtext := substr(argtext,1,instr(argtext,',',-1,1)-1);
991
992 -- remove date format from argument string
993 -- argtext := replace(argtext,'_'||dateform);
994
995
996 user_conc_prog_names(ii) := userconcprogname;
997 conc_prog_names(ii) := concprogname;
998 arg_texts(ii) := argtext;
999 request_ids(ii) := requestid;
1000 phases(ii) := phase;
1001 statuses(ii) := status;
1002 dev_phases(ii) := devphase;
1003 dev_statuses(ii) := devstatus;
1004 timestamps(ii) := timestamp;
1005
1006 ii := ii + 1;
1007 exit when ii = max_requests + 1;
1008 end loop;
1009 close request_info;
1010 num_requests := ii - 1;
1011
1012 end get_request_info;
1013
1014
1015
1016 procedure get_arguments (
1017 req_id in number,
1018 arg1 out nocopy varchar2,
1019 arg2 out nocopy varchar2,
1020 arg3 out nocopy varchar2,
1021 arg4 out nocopy varchar2,
1022 arg5 out nocopy varchar2,
1023 arg6 out nocopy varchar2,
1024 arg7 out nocopy varchar2,
1025 arg8 out nocopy varchar2,
1026 arg9 out nocopy varchar2,
1027 arg10 out nocopy varchar2,
1028 arg11 out nocopy varchar2,
1029 arg12 out nocopy varchar2,
1030 arg13 out nocopy varchar2,
1031 arg14 out nocopy varchar2,
1032 arg15 out nocopy varchar2,
1033 arg16 out nocopy varchar2,
1034 arg17 out nocopy varchar2,
1035 arg18 out nocopy varchar2,
1036 arg19 out nocopy varchar2,
1037 arg20 out nocopy varchar2,
1038 arg21 out nocopy varchar2,
1039 arg22 out nocopy varchar2,
1040 arg23 out nocopy varchar2,
1041 arg24 out nocopy varchar2,
1042 arg25 out nocopy varchar2) is
1043
1044
1045 begin
1046
1047 select argument1, argument2, argument3, argument4, argument5,
1051 argument21, argument22, argument23, argument24, argument25
1048 argument6, argument7, argument8, argument9, argument10,
1049 argument11, argument12, argument13, argument14, argument15,
1050 argument16, argument17, argument18, argument19, argument20,
1052 into arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10,
1053 arg11, arg12, arg13, arg14, arg15, arg16, arg17, arg18,
1054 arg19, arg20, arg21, arg22, arg23, arg24, arg25
1055 from fnd_concurrent_requests
1056 where request_id = req_id;
1057
1058 end get_arguments;
1059
1060
1061
1062 procedure add_dynamic_column (
1063 X_request_id in number,
1064 X_attribute_name in varchar2,
1065 X_column_name in varchar2,
1066 X_ordering in varchar2,
1067 X_BREAK in VARCHAR2,
1068 X_DISPLAY_LENGTH in NUMBER,
1069 X_DISPLAY_FORMAT in VARCHAR2,
1070 X_DISPLAY_STATUS in VARCHAR2,
1071 calling_fn in varchar2) is
1072 h_user_id number;
1073 h_login_id number;
1074 h_mesg_str varchar2(2000);
1075 begin
1076
1077 h_user_id := fnd_profile.value('USER_ID');
1078 h_login_id := fnd_profile.value('LOGIN_ID');
1079 insert into fa_rx_dynamic_columns (
1080 request_id, attribute_name, column_name, ordering, break,
1081 display_length, display_format, display_status, last_update_date,
1082 last_update_login, last_updated_by, created_by, creation_date)
1083 values (X_request_id, X_attribute_name, X_column_name, X_ordering,
1084 X_break, X_display_length, X_display_format, X_display_status,
1085 sysdate, h_login_id, h_user_id, h_user_id, sysdate);
1086
1087
1088 EXCEPTION
1089 when others then
1090 fnd_message.set_name('OFA','FA_FLEX_INSERT_FAILED');
1091 fnd_message.set_token('TABLE','FA_RX_DYNAMIC_COLUMNS',FALSE);
1092 h_mesg_str := fnd_message.get;
1093 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1094 raise;
1095
1096 end add_dynamic_column;
1097
1098 /* -------------------------------------------------------------------------------------------*/
1099 /* StatReq - The following two functions have been added for statutory reporting requirements */
1100 /* -------------------------------------------------------------------------------------------*/
1101
1102 PROCEDURE Initialize_Where(vset in out nocopy fnd_vset.valueset_r,v_flex_value in varchar2)
1103 is
1104
1105 where_clause long;
1106
1107 replace_string VARCHAR2(200);
1108 src VARCHAR2(100);
1109 value VARCHAR2(240);
1110 default_value VARCHAR2(240);
1111 idx NUMBER;
1112 ch VARCHAR2(10);
1113
1114 isprof BOOLEAN;
1115 flex_len NUMBER;
1116 prof_len NUMBER;
1117
1118 begin
1119 if vset.validation_type <> 'F' then return;
1120 elsif vset.table_info.where_clause is null then return;
1121 end if;
1122
1123 flex_len := length(':$FLEX$.');
1124 prof_len := length(':$PROFILES$.');
1125
1126 where_clause := vset.table_info.where_clause;
1127
1128 LOOP
1129 src := NULL;
1130 idx := instr(Upper(where_clause), ':$PROFILES$.');
1131 IF idx = 0 THEN
1132 idx := instr(Upper(where_clause), ':$FLEX$.');
1133
1134 IF idx <> 0 THEN
1135 isprof := FALSE;
1136 replace_string := substr(where_clause, idx, flex_len);
1137 idx := idx + flex_len;
1138 END IF;
1139 ELSE
1140 isprof := TRUE;
1141 replace_string := substr(where_clause, idx, prof_len);
1142 idx := idx + prof_len;
1143 END IF;
1144 EXIT WHEN idx = 0;
1145
1146 LOOP
1147 ch := substr(where_clause, idx, 1);
1148 EXIT WHEN ch IS NULL OR NOT (Upper(ch) BETWEEN 'A' AND 'Z' OR ch BETWEEN '0' and '9' OR ch = '_');
1149
1150 src := src || ch;
1151 idx := idx+1;
1152 END LOOP;
1153
1154 IF ch = ':' THEN
1155 idx := idx + 1;
1156 default_value := null;
1157 LOOP
1158 ch := substr(where_clause, idx, 1);
1159 EXIT WHEN ch IS NULL OR NOT (Upper(ch) BETWEEN 'A' AND 'Z' OR ch BETWEEN '0' and '9' OR ch = '_');
1160
1161 default_value := default_value || ch;
1162 idx := idx + 1;
1163 END LOOP;
1164 END IF;
1165
1166 value := null;
1167 IF isprof THEN
1168 fnd_profile.get(Upper(src), value);
1169 ELSE
1170 for i in 1..flex_val_count loop
1171 if upper(src) = flex_val_cache(i).flex_value_set_name then
1172 value := flex_val_cache(i).flex_value_id;
1173 exit;
1174 end if;
1175 end loop;
1176 END IF;
1177 if value is null then
1178 value := default_value;
1179 end if;
1180 if value is null then
1181 value := 'NULL';
1182 end if;
1183
1184 replace_string := replace_string||src;
1185
1186 IF (g_print_debug) THEN
1187 fa_rx_util_pkg.debug('Initialize_Where: ' || src);
1188 END IF;
1189
1190 where_clause := REPLACE(where_clause, replace_string, ''''||value||'''');
1191 END LOOP;
1192
1193 IF (g_print_debug) THEN
1194 fa_rx_util_pkg.debug('Initialize_Where: ' || where_clause);
1195 END IF;
1196
1197 if v_flex_value is Not null then
1201 where_clause := ' WHERE to_char('||vset.table_info.value_column_name||') = '||''''||v_flex_value||''''||' AND '||where_clause;
1198 fa_rx_util_pkg.debug('Initialize_Where: to change the where clause for long list ' ||where_clause);
1199 where_clause := upper(nvl(where_clause,'WHERE 1=1'));
1200 where_clause := substr(where_clause,instr(where_clause,'WHERE')+5);
1202 end if;
1203
1204 fa_rx_util_pkg.debug('Initialize_Where: after ** ' ||where_clause);
1205
1206 vset.table_info.where_clause := where_clause;
1207
1208 end initialize_where;
1209
1210 FUNCTION get_flex_val_meaning (
1211 v_flex_value_set_id IN NUMBER,
1212 v_flex_value_set_name IN VARCHAR2,
1213 v_flex_value IN VARCHAR2)
1214 RETURN VARCHAR2 IS
1215 vsid number;
1216 vset fnd_vset.valueset_r;
1217 fmt fnd_vset.valueset_dr;
1218 found BOOLEAN;
1219 row NUMBER;
1220 value fnd_vset.value_dr;
1221 meaning varchar2(240) := '';
1222 vsname varchar2(150);
1223
1224 i BINARY_INTEGER := 0;
1225 y BINARY_INTEGER := 0;
1226
1227 /* This function returns:
1228 - the meaning of a passed flex value, if the valueset is found and it has an entry
1229 for the flex value and the entry has a meaning associated with it.
1230 - the flex value, if the flex value passed is null or if the flex value is
1231 not found in the valueset or if both valueset parameters are NULL or if the
1232 flex value is found in the valueset but doesn't have a meaning */
1233
1234 BEGIN
1235
1236 /* Return NULL if flex value is null */
1237
1238 if (v_flex_value is null) then return (v_flex_value); end if;
1239
1240 /* If flex value set id is null and flex value set name is null too,
1241 return the flex value passed into the function.
1242
1243 If either of the flex value set id or the flex valueset name is null,
1244 select it and move on with the rest of the function. */
1245
1246 if (v_flex_value_set_name is null)
1247 then
1248 if (v_flex_value_set_id is null)
1249 then
1250 return(v_flex_value);
1251 else
1252 select flex_value_set_name
1253 into vsname
1254 from fnd_flex_value_sets
1255 where flex_value_set_id = v_flex_value_set_id;
1256
1257 vsid := v_flex_value_set_id;
1258
1259 end if;
1260 else
1261 if (v_flex_value_set_id is null)
1262 then
1263 select flex_value_set_id
1264 into vsid
1265 from fnd_flex_value_sets
1266 where flex_value_set_name = v_flex_value_set_name;
1267
1268 else
1269 vsid := v_flex_value_set_id;
1270 end if;
1271
1272 vsname := v_flex_value_set_name;
1273
1274 end if;
1275
1276 IF (g_print_debug) THEN
1277 fa_rx_util_pkg.debug('get_flex_val_meaning: ' || 'Caching values for value set '||vsname);
1278 END IF;
1279 /* Search PL/SQL tables for valuesets and values that have already been loaded */
1280
1281 if (g_value_set_counter >= 1)
1282 then
1283 FOR i in 1..g_value_set_counter LOOP
1284 if (g_value_set_tab(i).value_set_name = vsname)
1285 then
1286 FOR y in g_value_set_tab(i).from_counter..g_value_set_tab(i).to_counter LOOP
1287 if (g_values_tab(y).value = v_flex_value)
1288 then
1289
1290 meaning := nvl(g_values_tab(y).meaning, v_flex_value);
1291 return(meaning);
1292 end if;
1293 END LOOP;
1294 return(v_flex_value);
1295 end if;
1296 END LOOP;
1297 end if;
1298
1299 g_value_set_counter := g_value_set_counter + 1;
1300 g_value_set_tab(g_value_set_counter).value_set_name := vsname;
1301 g_value_set_tab(g_value_set_counter).from_counter := g_value_counter + 1;
1302 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter + 1;
1303
1304 /* Get valueset info */
1305 fnd_vset.get_valueset(vsid, vset, fmt);
1306
1307 If nvl(vset.validation_type,'*') = 'F' and nvl(fmt.longlist_flag,'N') = 'Y' Then
1308 /* Initialize WHERE Clause for Table validated value sets */
1309 Initialize_Where(vset,v_flex_value);
1310 Else
1311 Initialize_Where(vset,null);
1312 End If;
1313
1314 /* Initialize valueset variables */
1315 fnd_vset.get_value_init(vset, TRUE);
1316
1317 /* Fetch first value of valueset */
1318 fnd_vset.get_value(vset, row, found, value);
1319
1320 WHILE(found) LOOP
1321 /* Increase session flex value counter by 1 */
1322
1323 g_value_counter := g_value_counter + 1;
1324
1325 /* Store flex values away */
1326 fa_rx_util_pkg.debug(':Meaning = '||Nvl(value.meaning, '<<<NULL>>>')||', Value = '||value.value||':');
1327 g_values_tab(g_value_counter).meaning := substr(nvl(value.meaning, value.value), 1, 240);
1328 g_values_tab(g_value_counter).value := substr(Nvl(value.id, value.value), 1, 150);
1329 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter;
1330
1331 /* Check if fetched value matches the passed flex value, if yes
1332 store the meaning in variable meaning */
1333
1334 if (v_flex_value = Nvl(value.id, value.value))
1335 then
1339 /* Get next flex value in set */
1336 meaning := nvl(value.meaning, value.value);
1337 end if;
1338
1340
1341 fnd_vset.get_value(vset, row, found, value);
1342
1343 END LOOP;
1344
1345 fnd_vset.get_value_end(vset);
1346
1347 /* cache the value of this value set */
1348 flex_val_count := flex_val_count + 1;
1349 flex_val_cache(flex_val_count).flex_value_set_name := upper(vsname);
1350 flex_val_cache(flex_val_count).flex_value_id := v_flex_value;
1351
1352 /* Return meaning (if found) otherwise the flex value. */
1353
1354 return(nvl(meaning, v_flex_value));
1355
1356 EXCEPTION
1357 WHEN NO_DATA_FOUND THEN
1358 fnd_vset.get_value_end(vset);
1359 return(v_flex_value);
1360 WHEN OTHERS THEN
1361 IF (g_print_debug) THEN
1362 fa_rx_util_pkg.debug('EXCEPTION in get_flex_val_meaning');
1363 fa_rx_util_pkg.debug('get_flex_val_meaning: ' || sqlerrm);
1364 END IF;
1365 -- return(NULL);
1366 return(v_flex_value);
1367 END get_flex_val_meaning;
1368
1369
1370 --* Bug2991482, rravunny
1371 --* new overridden function to support parent value.
1372 --*
1373 FUNCTION get_flex_val_meaning (
1374 v_flex_value_set_id IN NUMBER,
1375 v_flex_value_set_name IN VARCHAR2,
1376 v_flex_value IN VARCHAR2,
1377 v_parent_flex_val IN VARCHAR2)
1378 RETURN VARCHAR2 IS
1379 vsid number;
1380 vset fnd_vset.valueset_r;
1381 fmt fnd_vset.valueset_dr;
1382 found BOOLEAN;
1383 row NUMBER;
1384 value fnd_vset.value_dr;
1385 meaning varchar2(240) := '';
1386 vsname varchar2(150);
1387
1388 i BINARY_INTEGER := 0;
1389 y BINARY_INTEGER := 0;
1390
1391 /* This function returns:
1392 - the meaning of a passed flex value, if the valueset is found and it has an entry
1393 for the flex value and the entry has a meaning associated with it.
1394 - the flex value, if the flex value passed is null or if the flex value is
1395 not found in the valueset or if both valueset parameters are NULL or if the
1396 flex value is found in the valueset but doesn't have a meaning */
1397
1398 BEGIN
1399
1400 /* Return NULL if flex value is null */
1401
1402 if (v_flex_value is null) then return (v_flex_value); end if;
1403
1404 /* If flex value set id is null and flex value set name is null too,
1405 return the flex value passed into the function.
1406
1407 If either of the flex value set id or the flex valueset name is null,
1408 select it and move on with the rest of the function. */
1409
1410 if (v_flex_value_set_name is null)
1411 then
1412 if (v_flex_value_set_id is null)
1413 then
1414 return(v_flex_value);
1415 else
1416 select flex_value_set_name
1417 into vsname
1418 from fnd_flex_value_sets
1419 where flex_value_set_id = v_flex_value_set_id;
1420
1421 vsid := v_flex_value_set_id;
1422
1423 end if;
1424 else
1425 if (v_flex_value_set_id is null)
1426 then
1427 select flex_value_set_id
1428 into vsid
1429 from fnd_flex_value_sets
1430 where flex_value_set_name = v_flex_value_set_name;
1431
1432 else
1433 vsid := v_flex_value_set_id;
1434 end if;
1435
1436 vsname := v_flex_value_set_name;
1437
1438 end if;
1439
1440 /* Search PL/SQL tables for valuesets and values that have already been loaded */
1441
1442 if (g_value_set_counter >= 1)
1443 then
1444 FOR i in 1..g_value_set_counter LOOP
1445 if (g_value_set_tab(i).value_set_name = vsname)
1446 then
1447 FOR y in g_value_set_tab(i).from_counter..g_value_set_tab(i).to_counter LOOP
1448 --* if dependant value set.
1449 if (v_parent_flex_val is not null and g_values_tab(y).value = v_flex_value
1450 and g_values_tab(y).parent_flex_value_low = v_parent_flex_val)
1451 then
1452 meaning := nvl(g_values_tab(y).meaning, v_flex_value);
1453 return(meaning);
1454 end if;
1455 --* if other than dependant value set.
1456
1457 if (g_values_tab(y).value = v_flex_value and v_parent_flex_val is null)
1458 then
1459 meaning := nvl(g_values_tab(y).meaning, v_flex_value);
1460 return(meaning);
1461 end if;
1462 END LOOP;
1463 return(v_flex_value);
1464 end if;
1465
1466 END LOOP;
1467 end if;
1468 g_value_set_counter := g_value_set_counter + 1;
1469 g_value_set_tab(g_value_set_counter).value_set_name := vsname;
1470 g_value_set_tab(g_value_set_counter).from_counter := g_value_counter + 1;
1471 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter + 1;
1472
1473 /* Get valueset info */
1474 fnd_vset.get_valueset(vsid, vset, fmt);
1475
1476 If nvl(vset.validation_type,'*') = 'F' and nvl(fmt.longlist_flag,'N') = 'Y' Then
1477 /* Initialize WHERE Clause for Table validated value sets */
1478 Initialize_Where(vset,v_flex_value);
1479 Else
1480 Initialize_Where(vset,null);
1481 End If;
1482
1483 /* Initialize valueset variables */
1487 fnd_vset.get_value(vset, row, found, value);
1484 fnd_vset.get_value_init(vset, TRUE);
1485
1486 /* Fetch first value of valueset */
1488
1489 WHILE(found) LOOP
1490 /* Increase session flex value counter by 1 */
1491
1492 g_value_counter := g_value_counter + 1;
1493
1494 /* Store flex values away */
1495
1496 g_values_tab(g_value_counter).meaning := substr(nvl(value.meaning, value.value), 1, 240);
1497 g_values_tab(g_value_counter).value := substr(Nvl(value.id, value.value), 1, 150);
1498 g_values_tab(g_value_counter).parent_flex_value_low := value.parent_flex_value_low;
1499 g_value_set_tab(g_value_set_counter).to_counter := g_value_counter;
1500
1501 /* Check if fetched value matches the passed flex value, if yes
1502 store the meaning in variable meaning */
1503
1504 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)
1505 then
1506 meaning := nvl(value.meaning, value.value);
1507 end if;
1508
1509 if (v_flex_value = Nvl(value.id, value.value) and v_parent_flex_val is null)
1510 then
1511 meaning := nvl(value.meaning, value.value);
1512 end if;
1513 /* Get next flex value in set */
1514 fnd_vset.get_value(vset, row, found, value);
1515
1516 END LOOP;
1517
1518 fnd_vset.get_value_end(vset);
1519 /* cache the value of this value set */
1520 flex_val_count := flex_val_count + 1;
1521 flex_val_cache(flex_val_count).flex_value_set_name := upper(vsname);
1522 flex_val_cache(flex_val_count).flex_value_id := v_flex_value;
1523 /* Return meaning (if found) otherwise the flex value. */
1524
1525 return(nvl(meaning, v_flex_value));
1526
1527 EXCEPTION
1528 WHEN NO_DATA_FOUND THEN
1529 fnd_vset.get_value_end(vset);
1530 return(v_flex_value);
1531 WHEN OTHERS THEN
1532 fnd_vset.get_value_end(vset);
1533 return(v_flex_value);
1534 END get_flex_val_meaning;
1535
1536 FUNCTION get_asset_info (
1537 v_info_type IN VARCHAR2,
1538 v_asset_id IN NUMBER,
1539 v_from_date IN DATE,
1540 v_to_date IN DATE,
1541 v_book_type_code IN VARCHAR2,
1542 v_balancing_segment IN VARCHAR2)
1543 return VARCHAR2 is
1544
1545 CURSOR c_vendor_name (c_asset_id NUMBER, c_to_date DATE) IS
1546 select
1547 distinct v.vendor_name
1548 from
1549 po_vendors v,
1550 fa_asset_invoices i
1551 where
1552 i.asset_id = c_asset_id and
1553 i.date_effective <= c_to_date and
1554 nvl(i.date_ineffective,
1555 sysdate) > c_to_date
1556 and
1557 v.vendor_id = i.po_vendor_id;
1558
1559 CURSOR c_invoice (c_asset_id NUMBER, c_to_date DATE) IS
1560 select
1561 distinct ap_i.invoice_num, ap_i.description
1562 from
1563 ap_invoices_all ap_i,
1564 fa_asset_invoices i
1565 where
1566 i.asset_id = c_asset_id and
1567
1568 i.date_effective <= c_to_date and
1569 nvl(i.date_ineffective,
1570 sysdate) > c_to_date
1571 and
1572 ap_i.invoice_id = i.invoice_id;
1573
1574 CURSOR c_retirement_type (c_asset_id NUMBER, c_from_date DATE, c_to_date DATE, c_book_type_code VARCHAR2) IS
1575 select
1576 distinct lu.meaning
1577 from
1578 fa_lookups lu,
1579 fa_transaction_headers th,
1580 fa_retirements r
1581 where
1582 r.asset_id = c_asset_id and
1583 r.book_type_code = c_book_type_code and
1584 th.transaction_header_id = r.transaction_header_id_in and
1585 th.date_effective between c_from_date and c_to_date
1586 and
1587 lu.lookup_type = 'RETIREMENT' and
1588 lu.lookup_code = r.retirement_type_code;
1589
1590 CURSOR c_location (c_asset_id NUMBER, c_to_date DATE, c_book_type_code VARCHAR2) IS
1591 select
1592 distinct dh.location_id, dh.code_combination_id
1593 from
1594 fa_distribution_history dh
1595 where
1596 dh.asset_id = c_asset_id and
1597 dh.book_type_code = c_book_type_code and
1598 dh.date_effective <= c_to_date and
1599 nvl(dh.date_ineffective,
1600 sysdate) > c_to_date;
1601
1602
1603 h_vendor_name VARCHAR2(240);
1604 h_invoice_number VARCHAR2(50);
1605 h_invoice_descr VARCHAR2(240);
1606 h_retirement_type VARCHAR2(80);
1607 h_location VARCHAR2(240);
1608 h_location_id NUMBER;
1609 h_loc_segs fa_rx_shared_pkg.Seg_Array;
1610 h_ccid NUMBER;
1611
1612 first_vendor BOOLEAN := TRUE;
1613 first_invoice_number BOOLEAN := TRUE;
1614 first_invoice_descr BOOLEAN := TRUE;
1615 first_retirement_type BOOLEAN := TRUE;
1616 first_location BOOLEAN := TRUE;
1617
1618 concat_vendor_name VARCHAR2(1000);
1619 concat_invoice_number VARCHAR2(1000);
1620 concat_invoice_descr VARCHAR2(1000);
1624 acct_all_segs fa_rx_shared_pkg.Seg_Array;
1621 concat_retirement_type VARCHAR2(1000);
1622 concat_location VARCHAR2(1000);
1623
1625 n_segs number;
1626 gl_balancing_seg number;
1627 gl_account_seg number;
1628 fa_cost_ctr_seg number;
1629 max_length number := 500;
1630
1631
1632 BEGIN
1633
1634 /* Get location flex structure if it's not there already */
1635
1636 if (g_loc_flex_struct is NULL)
1637 then
1638 select location_flex_structure
1639 into g_loc_flex_struct
1640 from fa_system_controls;
1641 end if;
1642
1643 /* Get vendor name */
1644
1645 if (v_info_type = 'VENDOR_NAME')
1646 then
1647 open c_vendor_name (v_asset_id, v_to_date);
1648 loop
1649 fetch c_vendor_name into h_vendor_name;
1650 if (c_vendor_name%NOTFOUND) then exit; end if;
1651 if (first_vendor)
1652 then
1653 concat_vendor_name := h_vendor_name;
1654 first_vendor := FALSE;
1655 else
1656 if (length(concat_vendor_name || ', ' || h_vendor_name ) > max_length)
1657 then
1658 exit;
1659 else
1660 concat_vendor_name := concat_vendor_name || ', ' || h_vendor_name ;
1661 end if;
1662 end if;
1663 end loop;
1664 close c_vendor_name;
1665 return(concat_vendor_name);
1666 end if;
1667
1668 /* Get invoice */
1669
1670 if (v_info_type = 'INVOICE_NUMBER')
1671 then
1672
1673 open c_invoice (v_asset_id, v_to_date);
1674 loop
1675 fetch c_invoice into h_invoice_number, h_invoice_descr;
1676 if (c_invoice%NOTFOUND) then exit; end if;
1677 if (first_invoice_number)
1678 then
1679 concat_invoice_number := h_invoice_number;
1680 first_invoice_number := FALSE;
1681 else
1682 if (length(concat_invoice_number || ', ' || h_invoice_number) > max_length)
1683 then
1684 exit;
1685 else
1686 concat_invoice_number := concat_invoice_number || ', ' || h_invoice_number ;
1687 end if;
1688 end if;
1689 end loop;
1690 close c_invoice;
1691 return(concat_invoice_number);
1692 end if;
1693
1694 /* Get invoice description */
1695
1696 if (v_info_type = 'INVOICE_DESCR')
1697 then
1698 open c_invoice (v_asset_id, v_to_date);
1699 loop
1700
1701 fetch c_invoice into h_invoice_number, h_invoice_descr;
1702 if (c_invoice%NOTFOUND) then exit; end if;
1703
1704 if (h_invoice_descr is not null)
1705 then
1706 if (first_invoice_descr)
1707 then
1708 concat_invoice_descr := h_invoice_descr;
1709 first_invoice_descr := FALSE;
1710 else
1711 if (length(concat_invoice_descr || ', ' || h_invoice_descr) > max_length)
1712 then
1713 exit;
1714 else
1715 concat_invoice_descr := concat_invoice_descr || ', ' || h_invoice_descr;
1716 end if;
1717 end if;
1718 end if;
1719
1720 end loop;
1721 close c_invoice;
1722 return(concat_invoice_descr);
1723 end if;
1724
1725 /* Get retirement type */
1726
1727 if (v_info_type = 'RETIREMENT_TYPE')
1728 then
1729 open c_retirement_type (v_asset_id, v_from_date, v_to_date, v_book_type_code);
1730 loop
1731 fetch c_retirement_type into h_retirement_type;
1732 if (c_retirement_type%NOTFOUND) then exit; end if;
1733 if (first_retirement_type)
1734 then
1735 concat_retirement_type := h_retirement_type;
1736 first_retirement_type := FALSE;
1737 else
1738 if (length(concat_retirement_type || ', ' || h_retirement_type) > max_length)
1739 then
1740 exit;
1741 else
1742 concat_retirement_type := concat_retirement_type || ', ' || h_retirement_type;
1743 end if;
1744 end if;
1745 end loop;
1746 close c_retirement_type;
1747 return(concat_retirement_type);
1748 end if;
1749
1750 /* Get location */
1751
1752 if (v_info_type = 'LOCATION')
1753 then
1754
1755 /* Get accounting flexfield's segment numbers */
1756
1757 fa_rx_shared_pkg.get_acct_segment_numbers (
1758 BOOK => v_book_type_code,
1759 BALANCING_SEGNUM => gl_balancing_seg,
1760 ACCOUNT_SEGNUM => gl_account_seg,
1761 CC_SEGNUM => fa_cost_ctr_seg,
1762 CALLING_FN => 'FA_BALANCES_REPORT');
1763
1764 open c_location (v_asset_id, v_to_date, v_book_type_code);
1765 loop
1766 fetch c_location into h_location_id, h_ccid;
1767
1768 if (c_location%NOTFOUND) then exit; end if;
1769
1770 /* Get accounting flexfield segment values */
1771
1772 fa_rx_shared_pkg.get_acct_segments (
1773 combination_id => h_ccid,
1774 n_segments => n_segs,
1775 segments => acct_all_segs,
1776 calling_fn => 'FA_BALANCES_REPORT');
1777
1778 /* Add location only if it is for the appropriate balancing segment */
1779
1780 if (acct_all_segs(gl_balancing_seg) = v_balancing_segment)
1781 then
1782
1783 /* Get concatenated location */
1784
1785 fa_rx_shared_pkg.concat_location (
1786 struct_id => g_loc_flex_struct,
1787 ccid => h_location_id,
1788 concat_string => h_location,
1789 segarray => h_loc_segs);
1790
1791 if (first_location)
1792 then
1793 concat_location := h_location;
1794 first_location := FALSE;
1795 else
1796
1797 if (length(concat_location || ', ' || h_location) > max_length) then
1798 exit;
1799 else
1800 concat_location := concat_location || ', ' || h_location;
1801 end if;
1802
1803 end if;
1804 end if;
1805
1806 end loop;
1807
1808 close c_location;
1809
1810 return(concat_location);
1811
1812 end if;
1813
1814 END get_asset_info;
1815
1816 PROCEDURE clear_flex_val_cache
1817 IS
1818 BEGIN
1819 flex_val_count := 0;
1820 END clear_flex_val_cache;
1821
1822 END FA_RX_SHARED_PKG;