[Home] [Help]
PACKAGE BODY: APPS.FA_ASSET_TRACE_PKG
Source
1 PACKAGE BODY FA_ASSET_TRACE_PKG AS
2 /* $Header: faxtrcb.pls 120.38.12020000.6 2013/02/07 07:03:38 rmandali ship $ */
3
4 FUNCTION fafsc (p_col VARCHAR2) RETURN VARCHAR2;
5 FUNCTION fparse_header(p_in_str VARCHAR2) RETURN VARCHAR2;
6 PROCEDURE load_tbls (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null);
7 PROCEDURE load_setup_tbls (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null);
8 PROCEDURE deprn_calc_info (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null);
9 PROCEDURE load_xla_info (p_banner IN varchar2,
10 x_retcode OUT NOCOPY NUMBER,
11 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null);
12 PROCEDURE submit_subrequest(p_parent_request IN NUMBER,
13 x_retcode OUT NOCOPY NUMBER,
14 x_errbuf OUT NOCOPY VARCHAR2);
15 FUNCTION enqueue_request RETURN BOOLEAN;
16 FUNCTION dequeue_request (p_request IN VARCHAR2,
17 x_desc OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
18 PROCEDURE prt_opt_tbl (p_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl);
19 FUNCTION get_event_list RETURN VARCHAR2;
20 FUNCTION get_schema (p_app_short_name VARCHAR2) RETURN VARCHAR2;
21 PROCEDURE log(p_calling_fn IN VARCHAR2,
22 p_msg IN VARCHAR2 default null,
23 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null);
24
25 TYPE t_asset_tbl IS TABLE OF VARCHAR2(32767)
26 INDEX BY BINARY_INTEGER;
27
28 TYPE c_stmt IS REF CURSOR;
29
30 Type t_cc_cols_rec IS RECORD (cTbl VARCHAR2(100), cCol VARCHAR2(100));
31 Type t_cc_cols IS TABLE OF t_cc_cols_rec INDEX BY BINARY_INTEGER;
32
33 TYPE t_num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
34
35 g_log_level_rec fa_api_types.log_level_rec_type;
36 g_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
37 g_col_exclusions FA_ASSET_TRACE_PUB.t_excl_tbl;
38 g_tmpc_tbl t_col_tbl;
39 g_tbl_hldr t_col_tbl;
40 g_anchor_tbl t_col_tbl;
41 g_hdr_tbl t_asset_tbl;
42 g_sel_tbl FA_ASSET_TRACE_PVT.t_asset_tbl;
43 g_no_rec_tbl t_asset_tbl;
44 g_tmp_tbl t_asset_tbl;
45 g_output_tbl t_asset_tbl;
46 g_asset_number FA_ADDITIONS.ASSET_NUMBER%TYPE;
47 g_asset_id FA_ADDITIONS.ASSET_ID%TYPE;
48 g_book FA_BOOK_CONTROLS.BOOK_TYPE_CODE%TYPE;
49 g_book_class FA_BOOK_CONTROLS.BOOK_CLASS%TYPE;
50 g_source_book FA_BOOK_CONTROLS.DISTRIBUTION_SOURCE_BOOK%TYPE;
51 g_sob_id FA_BOOK_CONTROLS.SET_OF_BOOKS_ID%TYPE;
52 g_fiscal_year FA_BOOK_CONTROLS.CURRENT_FISCAL_YEAR%TYPE;
53 g_fiscal_year_name FA_BOOK_CONTROLS.FISCAL_YEAR_NAME%TYPE;
54 g_deprn_calendar FA_BOOK_CONTROLS.DEPRN_CALENDAR%TYPE;
55 g_prorate_calendar FA_BOOK_CONTROLS.PRORATE_CALENDAR%TYPE;
56 g_mrc_enabled VARCHAR2(1);
57 g_jx_enabled VARCHAR2(1);
58 g_chk_cnt BOOLEAN;
59 g_no_header VARCHAR2(1):='N';
60 g_trc_tbl VARCHAR2(100);
61 g_dyn_head VARCHAR2(32767);
62 g_anchor VARCHAR2(32767);
63 g_trc_idx NUMBER :=1;
64
65 g_qname varchar2(80) := 'GAT_REQ_Q';
66 g_qtable varchar2(80) := 'GAT_REQ_QTBL';
67 g_payload GAT_message_type;
68
69 g_submit_sub boolean;
70 g_req_data VARCHAR2(10);
71
72 g_print_debug boolean; -- := fa_cache_pkg.fa_print_debug;
73
74 --
75 -- Entry point.
76 --
77 PROCEDURE do_trace (errbuf OUT NOCOPY VARCHAR2,
78 retcode OUT NOCOPY NUMBER,
79 p_book IN VARCHAR2,
80 p_asset_number IN VARCHAR2,
81 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
82
83 l_asset_id NUMBER;
84 l_book VARCHAR2(15);
85 l_tblcount NUMBER;
86 l_count NUMBER;
87 l_country_code VARCHAR2(150); --financials_system_params_all.global_attribute1%type; --need 2597346 to get these columns.
88 l_product_code VARCHAR2(150); --financials_system_params_all.global_attribute2%type;
89 l_app_id number;
90 l_app_version fnd_product_groups.release_name%type;
91 l_stmt VARCHAR2(4000);
92 l_temp_head VARCHAR2(2000);
93 l_desc varchar2(200);
94 l_req_count number :=0;
95 l_banner VARCHAR2(32767);
96 l_filename VARCHAR2(80); --for utl_file
97
98 l_parent_request number;
99 l_submit_sub varchar2(1);
100
101 l_qstarted varchar2(3);
102
103 l_calling_fn varchar2(80) := 'fa_asset_trace_pkg.do_trace';
104 l_msg_count number;
105 l_msg_data varchar2(512);
106
107 error_found1 EXCEPTION;
108 error_found2 EXCEPTION;
109
110 /* Added for Bug 16266481 / 16053785 */
111 cursor c_playload_mesg is
112 SELECT OWNER||'.'||OBJECT_NAME
113 FROM DBA_OBJECTS OBJ, USER_SYNONYMS SYN
114 WHERE OBJ.OBJECT_NAME = UPPER('GAT_message_type')
115 AND OBJ.OBJECT_TYPE = 'TYPE'
116 AND SYN.SYNONYM_NAME = OBJ.OBJECT_NAME
117 AND OBJ.OWNER = SYN.TABLE_OWNER
118 AND obj.object_name = syn.table_name;
119
120 l_playload_mesg varchar2(100);
121
122 BEGIN
123 dbms_lock.sleep(3); --sleeping for no reason, but leave it anyway :-)
124
125 IF (fa_asset_trace_pkg.g_use_utl_file = 'Y') THEN
126 if (nvl(fa_asset_trace_pkg.g_sla_only, 'N') = 'Y') then
127 l_filename := 'GTUSLA_'||replace(ltrim(rtrim(p_book)),' ','_') || '_' || replace(p_asset_number,' ','_') ||'.log';
128 else
129 l_filename := 'GTU_'||replace(ltrim(rtrim(p_book)),' ','_') || '_' || replace(p_asset_number,' ','_') ||'.log';
130 g_submit_sub := TRUE;
131 end if;
132 log(l_calling_fn, 'l_filename: '||l_filename);
133 FA_ASSET_TRACE_PVT.g_use_utl_file := 'Y';
134 FA_ASSET_TRACE_PVT.ocfile (FA_ASSET_TRACE_PVT.g_logfile, l_filename,'O');
135 END IF;
136 log(l_calling_fn, 'use utl file: '|| fa_asset_trace_pkg.g_use_utl_file);
137 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N') then
138 l_parent_request := fnd_global.conc_request_id;
139 log(l_calling_fn, 'l_parent_request: '|| l_parent_request);
140
141 open c_playload_mesg;
142 fetch c_playload_mesg into l_playload_mesg;
143 close c_playload_mesg;
144 if (l_playload_mesg is NULL) then
145 l_playload_mesg := 'GAT_message_type';
146 end if;
147
148 l_qstarted := FA_ASSET_TRACE_PUB.start_queue (p_qtable => g_qtable,
149 p_qpayload_type => l_playload_mesg,
150 p_qname => g_qname);
151
152 log(l_calling_fn, 'l_qstarted: '|| l_qstarted);
153
154 if (l_qstarted <> 'F') then
155 l_submit_sub := dequeue_request('S'||to_char(l_parent_request), l_desc);
156 if (NVL(l_submit_sub, 'N') = 'S') then
157 g_submit_sub := FALSE; --subrequest already submitted
158 else
159 g_submit_sub := TRUE;
160 end if;
161 end if;
162
163 --Check number of requests in the queue + add to queue.
164 --Doing this to force serial processing in case requests were submitted in
165 --the same manner as bug 3184059.
166 l_req_count := FA_ASSET_TRACE_PUB.g_req_tbl.count +1;
167 FA_ASSET_TRACE_PUB.g_req_tbl(l_req_count) := l_parent_request;
168 FA_ASSET_TRACE_PUB.wait_for_req;
169
170 end if; --(nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N') ...
171
172 g_print_debug := fa_cache_pkg.fa_print_debug;
173
174 if (not g_log_level_rec.initialized) then
175 if (NOT fa_util_pub.get_log_level_rec (x_log_level_rec => g_log_level_rec)) then
176 raise FND_API.G_EXC_ERROR;
177 else
178 if (not g_print_debug) then
179 g_print_debug := TRUE;
180 end if;
181 end if; -- (NOT fa_util_pub....
182 end if;
183
184 l_book := p_book;
185 if not (fa_cache_pkg.fazcbc(X_book => p_book,
186 p_log_level_rec => g_log_level_rec)) then
187 raise error_found1;
188 end if;
189
190 if g_print_debug then
191 fa_debug_pkg.add(l_calling_fn, 'setting', 'initial globals',
192 p_log_level_rec => p_log_level_rec);
193 end if;
194
195 g_book := p_book;
196
197 if (NVL(l_submit_sub, 'N') = 'S') then
198 g_asset_id := to_number(p_asset_number);
199
200 SELECT asset_number
201 INTO g_asset_number
202 FROM fa_additions_b
203 where asset_id = g_asset_id;
204 else
205 g_asset_number := p_asset_number;
206 BEGIN
207 SELECT asset_id
208 INTO l_asset_id
209 FROM fa_additions_b
210 WHERE asset_number = p_asset_number;
211
212 g_asset_id := l_asset_id;
213
214 SELECT count(*)
215 INTO l_count
216 FROM fa_books
217 WHERE asset_id = g_asset_id
218 AND book_type_code = g_book;
219
220 if l_count = 0 then
221 raise ERROR_FOUND2;
222 end if;
223
224 EXCEPTION
225 WHEN ERROR_FOUND2 THEN
226 -- 'FA_EXP_GET_ASSET_INFO';
227 raise error_found1;
228 WHEN OTHERS THEN
229 -- 'FA_EXP_GET_ASSET_INFO';
230 raise error_found1;
231 END;
232 end if; -- (NVL(l_submit_sub, 'N') = 'S')
233
234 if (nvl(fa_asset_trace_pkg.g_sla_only, 'N') = 'Y') then
235 l_desc := 'FATRACE - SLA Subrequest from backend: '||g_book||'-'||g_asset_id||'-'||to_char(sysdate);
236 end if;
237
238 --populate param tbl.
239 l_count :=FA_ASSET_TRACE_PVT.g_param_tbl.count + 1;
240 FA_ASSET_TRACE_PVT.g_param_tbl(l_count).param_column :='ASSET_ID';
241 FA_ASSET_TRACE_PVT.g_param_tbl(l_count).nValue := g_asset_id;
242 l_count := l_count +1;
243 FA_ASSET_TRACE_PVT.g_param_tbl(l_count).param_column :='BOOK_TYPE_CODE';
244 FA_ASSET_TRACE_PVT.g_param_tbl(l_count).cValue := g_book;
245
246 if g_print_debug then
247 fa_debug_pkg.add(l_calling_fn, 'setting', 'localizations info');
248 end if;
249
250 -- Doing this check for globalizations until a better way is found.
251 FND_PROFILE.get('JGZZ_COUNTRY_CODE',l_country_code);
252
253 -- Derive Product Code from Country
254 -- Application Id: 7000-JA, 7002-JE, 7003-JG, 7004-JL
255 -- Right now just getting the info to make sure we select the global_attributexx columns
256 -- In future, we may want to select globalization data from the country-specific tables.
257
258 IF l_country_code IS NOT NULL THEN
259
260 IF l_country_code IN ('AU', 'CA', 'KR', 'SG', 'TH', 'TW') THEN
261 l_product_code := 'JA';
262 l_app_id :=7000;
263 g_jx_enabled := 'Y';
264 ELSIF l_country_code in ('AT', 'BE', 'CH', 'CZ', 'DE',
265 'DK', 'ES', 'FI', 'FR', 'GR',
266 'HU', 'IT', 'NL', 'NO', 'PL',
267 'PT', 'SE', 'TR') THEN
268 l_product_code := 'JE';
269 l_app_id :=7002;
270 g_jx_enabled := 'Y';
271 ELSIF l_country_code in ('AR', 'BR', 'CL', 'CO', 'MX') THEN
272 l_product_code := 'JL';
273 l_app_id :=7004;
274 g_jx_enabled := 'Y';
275 ELSE
276 l_product_code := NULL;
277 l_app_id :=NULL;
278 g_jx_enabled := 'N';
279 END IF;
280 ELSE
281 g_jx_enabled := 'N';
282 END IF;
283
284 if g_print_debug then
285 fa_debug_pkg.add(l_calling_fn, 'setting', 'book control info',
286 p_log_level_rec => p_log_level_rec);
287 end if;
288
289 g_mrc_enabled := NVL(fa_cache_pkg.fazcbc_record.mc_source_flag,'N');
290 g_book_class := fa_cache_pkg.fazcbc_record.book_class;
291 g_source_book := fa_cache_pkg.fazcbc_record.distribution_source_book;
292 g_sob_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
293 g_fiscal_year := fa_cache_pkg.fazcbc_record.current_fiscal_year;
294 g_fiscal_year_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
295 g_deprn_calendar := fa_cache_pkg.fazcbc_record.deprn_calendar;
296 g_prorate_calendar:= fa_cache_pkg.fazcbc_record.prorate_calendar;
297
298 log (l_calling_fn,'g_source_book: '||g_source_book);
299
300 FA_ASSET_TRACE_PVT.g_jx_enabled := g_jx_enabled;
301 FA_ASSET_TRACE_PVT.g_mrc_enabled := g_mrc_enabled;
302
303 select release_name
304 into l_app_version
305 from fnd_product_groups;
306
307 DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD-MON-YYYY HH24:MI:SS''');
308 --set banner section
309 l_temp_head :='<tr>
310 <td bgcolor="#cfe0f1" align="center"><font color="#343434"><b>Asset Number</b></font></td>
311 <td bgcolor="#cfe0f1" align="center"><font color="#343434"><b>Asset ID</b></font></td>
312 <td bgcolor="#cfe0f1" align="center"><font color="#343434"><b>Book Type</b></font></td>
313 <td bgcolor="#cfe0f1" align="center"><font color="#343434"><b>Book Class</b></font></td>
314 <td bgcolor="#cfe0f1" align="center"><font color="#343434"><b>App. Version</b></font></td>
315 <td bgcolor="#cfe0f1" align="center"><font color="#343434"><b>Run Date</b></font></td></tr>
316 <tr><td bgcolor="#f2f2f5" align="center">'||g_asset_number||'</td>
317 <td bgcolor="#f2f2f5" align="center">'||g_asset_id||'</td>
318 <td bgcolor="#f2f2f5" align="center">'||g_book||'</td>
319 <td bgcolor="#f2f2f5" align="center">'||g_book_class||'</td>
320 <td bgcolor="#f2f2f5" align="center">'||l_app_version||'</td>
321 <td bgcolor="#f2f2f5" align="center">'||sysdate||'</td></tr></table></center><br><hr width="93%" align="left" size="5"color="#343434"><br>';
322
323 FA_ASSET_TRACE_PUB.set_temp_head (l_temp_head);
324 --
325 if (g_submit_sub) then
326
327 l_stmt :=''''||'<TD bgcolor="#f2f2f5">'||''''||'||bk.BOOK_TYPE_CODE||'||''''||'</TD>'||''''||'||''''||';
328 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.DISTRIBUTION_SOURCE_BOOK||'||''''||'</TD>'||''''||'||''''||';
329 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.BOOK_CLASS||'||''''||'</TD>'||''''||'||''''||';
330 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.DATE_INEFFECTIVE||'||''''||'</TD>'||''''||'||''''||';
331 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.SET_OF_BOOKS_ID||'||''''||'</TD>'||''''||'||''''||';
332 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.LAST_DEPRN_RUN_DATE||'||''''||'</TD>'||''''||'||''''||';
333 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.DEPRN_STATUS||'||''''||'</TD>'||''''||'||''''||';
334 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.MASS_REQUEST_ID||'||''''||'</TD>'||''''||'||''''||';
335 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||bc.LAST_PERIOD_COUNTER||'||''''||'</TD>'||''''||'||''''||';
336 l_stmt :=l_stmt||''''||'<TD bgcolor="#f2f2f5">'||''''||'||dp.PERIOD_COUNTER||'||''''||'</TD>'||'''';
337 l_stmt :=l_stmt||' from fa_books bk, fa_book_controls bc, fa_deprn_periods dp
338 where bc.book_type_code = bk.book_type_code
339 and dp.book_type_code = bk.book_type_code
340 and dp.period_close_date is null
341 and bk.date_ineffective is null
342 and bk.asset_id = '|| l_asset_id;
343
344 l_banner :='NH <TABLE BORDER=0 width="93%" cellpadding="5"><TR><TD BGCOLOR="#3a5a87"><font color=white><B>List of books to which this asset was added'
345 ||'</B></TD></TR></TABLE><TABLE BORDER=0 width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR>';
346 l_banner := l_banner||fparse_header('BOOK_TYPE_CODE')||fparse_header('DISTRIBUTION_SOURCE_BOOK')
347 ||fparse_header('BOOK_CLASS')||fparse_header('DATE_INEFFECTIVE')
348 ||fparse_header('SET_OF_BOOKS_ID')||fparse_header('LAST_DEPRN_RUN_DATE')
349 ||fparse_header('DEPRN_STATUS')||fparse_header('MASS_REQUEST_ID')
350 ||fparse_header('LAST_PERIOD_COUNTER')||fparse_header('CURRENT_PERIOD_COUNTER')||'</TR>';
351
352 load_tbls;
353 FA_ASSET_TRACE_PUB.run_trace(p_opt_tbl => g_options_tbl,
354 p_exc_tbl => g_col_exclusions,
355 p_tdyn_head => l_banner,
356 p_stmt => l_stmt,
357 p_sys_opt_tbl => 'FA_SYSTEM_CONTROLS',
358 p_use_utl_file => fa_asset_trace_pkg.g_use_utl_file,
359 p_debug_flag => g_print_debug,
360 p_calling_prog => 'Asset Trace Utility',
361 p_retcode => retcode,
362 p_log_level_rec => g_log_level_rec);
363
364 if ((nvl(fa_asset_trace_pkg.g_sla_only, 'Y') = 'N') and
365 (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N')) then
366 submit_subrequest (l_parent_request, retcode, errbuf);
367 end if;
368
369 log(l_calling_fn,'Parent request, retcode is '||retcode);
370 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'N') = 'Y') then
371 log(l_calling_fn, 'Trying to close log file');
372 FA_ASSET_TRACE_PVT.ocfile (FA_ASSET_TRACE_PVT.g_logfile, null,'C');
373 end if;
374 g_submit_sub := FALSE;
375 elsif ((NOT g_submit_sub) OR ((nvl(fa_asset_trace_pkg.g_sla_only, 'N') = 'Y'))) then
376
377 log(l_calling_fn,'Calling load_xla_info.');
378 load_xla_info(l_desc, retcode, g_log_level_rec);
379 log(l_calling_fn,'sla subrequest retcode is '||retcode);
380
381 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'N') = 'Y') then
382 FA_ASSET_TRACE_PVT.ocfile (FA_ASSET_TRACE_PVT.g_logfile, null,'C');
383 end if;
384
385 end if;
386
387 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N') then
388 if g_print_debug then
389 fa_debug_pkg.add(l_calling_fn, 'retcode', retcode, p_log_level_rec => p_log_level_rec);
390 end if;
391 if (g_print_debug) then
392 fa_debug_pkg.Write_Debug_Log;
393 end if;
394 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
395 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data);
396 end if;
397
398 EXCEPTION
399
400 WHEN ERROR_FOUND1 THEN
401 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
402 p_log_level_rec => p_log_level_rec);
403 if ((g_print_debug) and (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N')) then
404 fa_debug_pkg.Write_Debug_Log;
405 end if;
406 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
407 p_data => l_msg_data);
408 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N') then
409 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data);
410 else
411 log (l_calling_fn,'Others: '||l_msg_count||' - '||l_msg_data);
412 end if;
413 retcode := 2;
414
415 WHEN OTHERS THEN
416 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
417 p_log_level_rec => p_log_level_rec);
418 if ((g_print_debug) and (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N')) then
419 fa_debug_pkg.Write_Debug_Log;
420 end if;
421 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
422 p_data => l_msg_data);
423 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'Y') = 'N') then
424 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data);
425 else
426 log (l_calling_fn,'Others: '||l_msg_count||' - '||l_msg_data);
427 end if;
428 retcode := 2;
429
430 END do_trace;
431 --
432 --Load tables to be processed
433 --
434 PROCEDURE load_tbls (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
435
436 l_nmrc_count NUMBER;
437 l_check_reval NUMBER;
438 l_idx number := 0;
439 l_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
440
441 l_calling_fn varchar2(40) := 'fa_asset_trace_pkg.load_tbls';
442
443 BEGIN
444 select count(1)
445 into l_check_reval
446 from fa_transaction_headers
447 where transaction_type_code = 'REVALUATION'
448 and asset_id = g_asset_id
449 and book_type_code = g_book;
450
451 l_idx:= l_idx+ 1;
452 l_options_tbl(l_idx).l_tbl := 'FA_ADDITIONS_B';
453 l_options_tbl(l_idx).l_gen_select := 'Y';
454 l_options_tbl(l_idx).l_col_order := 'ASSET_NUMBER,ASSET_TYPE,ASSET_CATEGORY_ID,CURRENT_UNITS';
455 l_idx:= l_idx+ 1;
456 l_options_tbl(l_idx).l_tbl := 'FA_ADDITIONS_TL';
457 l_options_tbl(l_idx).l_gen_select := 'Y';
458 l_idx:= l_idx+ 1;
459 l_options_tbl(l_idx).l_tbl := 'FA_TRANSACTION_HEADERS';
460 l_options_tbl(l_idx).l_gen_select := 'Y';
461 l_options_tbl(l_idx).l_lcs := 'BOOK_TYPE_CODE,TRANSACTION_HEADER_ID';
462 l_options_tbl(l_idx).l_leading_cols := fafsc('BOOK_TYPE_CODE')||fafsc('TRANSACTION_HEADER_ID');
463 l_options_tbl(l_idx).l_order_by := 'TRANSACTION_HEADER_ID';
464 l_options_tbl(l_idx).l_lc_header := fparse_header('BOOK_TYPE_CODE')||fparse_header('TRANSACTION_HEADER_ID');
465 l_options_tbl(l_idx).l_num_cols := 2;
466 l_options_tbl(l_idx).l_col_order := 'TRANSACTION_TYPE_CODE,TRANSACTION_DATE_ENTERED,DATE_EFFECTIVE,AMORTIZATION_START_DATE'
467 ||',CALLING_INTERFACE,MASS_REFERENCE_ID,TRANSACTION_NAME,TRANSACTION_SUBTYPE,TRANSACTION_KEY,SOURCE_TRANSACTION_HEADER_ID'
468 ||',MASS_TRANSACTION_ID,INVOICE_TRANSACTION_ID';
469 l_options_tbl(l_idx).l_add_clause := ' FROM FA_TRANSACTION_HEADERS Where asset_id = '||g_asset_id
470 ||' and book_type_code in ('||''''||g_source_book||''''||','||''''||g_book||''''||') ';
471 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(1) FROM FA_TRANSACTION_HEADERS'
472 ||' WHERE asset_id = '||g_asset_id||' and book_type_code in ('||''''||g_source_book||''''||','||''''||g_book||''''||')';
473 l_idx:= l_idx+ 1;
474 l_options_tbl(l_idx).l_tbl := 'FA_ASSET_HISTORY';
475 l_options_tbl(l_idx).l_gen_select := 'Y';
476 l_idx:= l_idx+ 1;
477 l_options_tbl(l_idx).l_tbl := 'FA_DISTRIBUTION_HISTORY';
478 l_options_tbl(l_idx).l_gen_select := 'Y';
479 l_options_tbl(l_idx).l_lcs := 'DISTRIBUTION_ID';
480 l_options_tbl(l_idx).l_leading_cols := fafsc('DISTRIBUTION_ID');
481 l_options_tbl(l_idx).l_order_by := 'DISTRIBUTION_ID';
482 l_options_tbl(l_idx).l_lc_header := fparse_header('DISTRIBUTION_ID');
483 l_options_tbl(l_idx).l_num_cols := 1;
484 l_options_tbl(l_idx).l_col_order := 'TRANSACTION_HEADER_ID_IN,TRANSACTION_HEADER_ID_OUT'
485 ||',CODE_COMBINATION_ID,LOCATION_ID,ASSIGNED_TO,RETIREMENT_ID,UNITS_ASSIGNED,TRANSACTION_UNITS';
486 l_options_tbl(l_idx).l_add_clause := ' FROM FA_DISTRIBUTION_HISTORY Where asset_id = '||g_asset_id
487 ||' and book_type_code in ('||''''||g_source_book||''''||','||''''||g_book||''''||') ';
488 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(1) FROM FA_DISTRIBUTION_HISTORY'
489 ||' WHERE asset_id = '||g_asset_id||' and book_type_code = '||''''||g_source_book||'''';
490 l_idx:= l_idx+ 1;
491 l_options_tbl(l_idx).l_tbl := 'FA_ADJUSTMENTS';
492 l_options_tbl(l_idx).l_gen_select := 'Y';
493 l_options_tbl(l_idx).l_lcs := 'TRANSACTION_HEADER_ID';
494 l_options_tbl(l_idx).l_leading_cols := fafsc('TRANSACTION_HEADER_ID');
495 l_options_tbl(l_idx).l_order_by := 'TRANSACTION_HEADER_ID';
496 l_options_tbl(l_idx).l_lc_header := fparse_header('TRANSACTION_HEADER_ID');
497 l_options_tbl(l_idx).l_num_cols := 1;
498 l_options_tbl(l_idx).l_col_order := 'SOURCE_TYPE_CODE,ADJUSTMENT_TYPE,DEBIT_CREDIT_FLAG,CODE_COMBINATION_ID,'
499 ||'ADJUSTMENT_AMOUNT,DISTRIBUTION_ID,PERIOD_COUNTER_CREATED,PERIOD_COUNTER_ADJUSTED';
500 l_idx:= l_idx+ 1;
501 l_options_tbl(l_idx).l_tbl := 'FA_MC_ADJUSTMENTS';
502 l_options_tbl(l_idx).l_gen_select := 'Y';
503 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,TRANSACTION_HEADER_ID';
504 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('TRANSACTION_HEADER_ID');
505 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,TRANSACTION_HEADER_ID';
506 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('TRANSACTION_HEADER_ID');
507 l_options_tbl(l_idx).l_num_cols := 2;
508 l_options_tbl(l_idx).l_col_order := 'SOURCE_TYPE_CODE,ADJUSTMENT_TYPE'
509 ||',DEBIT_CREDIT_FLAG,CODE_COMBINATION_ID,ADJUSTMENT_AMOUNT,DISTRIBUTION_ID'
510 ||',PERIOD_COUNTER_CREATED,PERIOD_COUNTER_ADJUSTED';
511 if (l_check_reval > 1) then
512 l_idx:= l_idx+ 1;
513 l_options_tbl(l_idx).l_tbl := 'FA_MASS_REVALUATIONS';
514 l_options_tbl(l_idx).l_gen_select := 'Y';
515 l_options_tbl(l_idx).l_lcs := 'MASS_REVAL_ID';
516 l_options_tbl(l_idx).l_leading_cols := fafsc('MASS_REVAL_ID');
517 l_options_tbl(l_idx).l_lc_header := fparse_header('MASS_REVAL_ID');
518 l_options_tbl(l_idx).l_add_clause := ' FROM FA_MASS_REVALUATIONS where book_type_code = '||''''||g_book||''''
519 ||' and mass_reval_id in (select mass_transaction_id from fa_transaction_headers where transaction_type_code = ''REVALUATION'''
520 ||' and asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||')';
521 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
522 l_idx:= l_idx+ 1;
523 l_options_tbl(l_idx).l_tbl := 'FA_MASS_REVALUATIONS_RULES';
524 l_options_tbl(l_idx).l_gen_select := 'Y';
525 l_options_tbl(l_idx).l_lcs := 'MASS_REVAL_ID';
526 l_options_tbl(l_idx).l_leading_cols := fafsc('MASS_REVAL_ID');
527 l_options_tbl(l_idx).l_lc_header := fparse_header('MASS_REVAL_ID');
528 l_options_tbl(l_idx).l_add_clause := ' FROM FA_MASS_REVALUATIONS_RULES where asset_id = '||g_asset_id
529 ||' and mass_reval_id in (select mass_transaction_id from fa_transaction_headers where transaction_type_code = ''REVALUATION'''
530 ||' and asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||')';
531 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
532 end if;
533 l_idx:= l_idx+ 1;
534 l_options_tbl(l_idx).l_tbl := 'FA_BOOKS';
535 l_options_tbl(l_idx).l_gen_select := 'Y';
536 l_options_tbl(l_idx).l_lcs := 'TRANSACTION_HEADER_ID_IN';
537 l_options_tbl(l_idx).l_leading_cols := fafsc('TRANSACTION_HEADER_ID_IN');
538 l_options_tbl(l_idx).l_order_by := 'TRANSACTION_HEADER_ID_IN';
539 l_options_tbl(l_idx).l_lc_header := fparse_header('TRANSACTION_HEADER_ID_IN');
540 l_options_tbl(l_idx).l_num_cols := 1;
541 l_options_tbl(l_idx).l_col_order := 'TRANSACTION_HEADER_ID_OUT,DATE_EFFECTIVE,DATE_INEFFECTIVE,DATE_PLACED_IN_SERVICE,'
542 ||'ORIGINAL_COST,COST,ADJUSTED_COST,ADJUSTED_RECOVERABLE_COST,RECOVERABLE_COST,UNREVALUED_COST,SALVAGE_VALUE,'
543 ||'PRORATE_CONVENTION_CODE,PRORATE_DATE,DEPRN_START_DATE,DEPRN_METHOD_CODE,LIFE_IN_MONTHS,DEPRECIATE_FLAG,CAPITALIZE_FLAG'
544 ||',RATE_ADJUSTMENT_FACTOR,ANNUAL_DEPRN_ROUNDING_FLAG,ANNUAL_ROUNDING_FLAG,ADJUSTMENT_REQUIRED_STATUS';
545 l_idx:= l_idx+ 1;
546 l_options_tbl(l_idx).l_tbl := 'FA_MC_BOOKS';
547 l_options_tbl(l_idx).l_gen_select := 'Y';
548 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,TRANSACTION_HEADER_ID_IN';
549 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('TRANSACTION_HEADER_ID_IN');
550 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,TRANSACTION_HEADER_ID_IN';
551 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('TRANSACTION_HEADER_ID_IN');
552 l_options_tbl(l_idx).l_num_cols := 2;
553 l_options_tbl(l_idx).l_col_order := 'TRANSACTION_HEADER_ID_OUT,DATE_EFFECTIVE,DATE_INEFFECTIVE,DATE_PLACED_IN_SERVICE,'
554 ||'ORIGINAL_COST,COST,ADJUSTED_COST,ADJUSTED_RECOVERABLE_COST,RECOVERABLE_COST,UNREVALUED_COST,SALVAGE_VALUE,'
555 ||'PRORATE_CONVENTION_CODE,PRORATE_DATE,DEPRN_START_DATE,DEPRN_METHOD_CODE,LIFE_IN_MONTHS,DEPRECIATE_FLAG,CAPITALIZE_FLAG'
556 ||',RATE_ADJUSTMENT_FACTOR,ANNUAL_DEPRN_ROUNDING_FLAG,ANNUAL_ROUNDING_FLAG,ADJUSTMENT_REQUIRED_STATUS';
557 l_idx:= l_idx+ 1;
558 l_options_tbl(l_idx).l_tbl := 'FA_MC_BOOKS_RATES';
559 l_options_tbl(l_idx).l_gen_select := 'Y';
560 l_idx:= l_idx+ 1;
561 l_options_tbl(l_idx).l_tbl := 'FA_DEPRN_SUMMARY';
562 l_options_tbl(l_idx).l_gen_select := 'Y';
563 l_options_tbl(l_idx).l_lcs := 'PERIOD_COUNTER';
564 l_options_tbl(l_idx).l_leading_cols := fafsc('PERIOD_COUNTER');
565 l_options_tbl(l_idx).l_order_by := 'PERIOD_COUNTER';
566 l_options_tbl(l_idx).l_lc_header := fparse_header('PERIOD_COUNTER');
567 l_options_tbl(l_idx).l_num_cols := 1;
568 l_options_tbl(l_idx).l_col_order := 'DEPRN_SOURCE_CODE,DEPRN_RUN_DATE,DEPRN_AMOUNT,YTD_DEPRN,DEPRN_RESERVE,ADJUSTED_COST';
569 l_idx:= l_idx+ 1;
570 l_options_tbl(l_idx).l_tbl := 'FA_DEPRN_SUMMARY_H';
571 l_options_tbl(l_idx).l_gen_select := 'Y';
572 l_options_tbl(l_idx).l_lcs := 'PERIOD_COUNTER';
573 l_options_tbl(l_idx).l_leading_cols := fafsc('PERIOD_COUNTER');
574 l_options_tbl(l_idx).l_order_by := 'PERIOD_COUNTER';
575 l_options_tbl(l_idx).l_lc_header := fparse_header('PERIOD_COUNTER');
576 l_options_tbl(l_idx).l_num_cols := 1;
577 l_options_tbl(l_idx).l_col_order := 'EVENT_ID,REVERSAL_EVENT_ID,REVERSAL_DATE';
578 l_idx:= l_idx+ 1;
579 l_options_tbl(l_idx).l_tbl := 'FA_MC_DEPRN_SUMMARY';
580 l_options_tbl(l_idx).l_gen_select := 'Y';
581 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,PERIOD_COUNTER';
582 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('PERIOD_COUNTER');
583 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,PERIOD_COUNTER';
584 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('PERIOD_COUNTER');
585 l_options_tbl(l_idx).l_num_cols := 2;
586 l_options_tbl(l_idx).l_col_order := 'DEPRN_SOURCE_CODE,DEPRN_RUN_DATE,DEPRN_AMOUNT,YTD_DEPRN,DEPRN_RESERVE,ADJUSTED_COST';
587 l_idx:= l_idx+ 1;
588 l_options_tbl(l_idx).l_tbl := 'FA_MC_DEPRN_SUMMARY_H';
589 l_options_tbl(l_idx).l_gen_select := 'Y';
590 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,PERIOD_COUNTER';
591 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('PERIOD_COUNTER');
592 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,PERIOD_COUNTER';
593 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('PERIOD_COUNTER');
594 l_options_tbl(l_idx).l_num_cols := 2;
595 l_options_tbl(l_idx).l_col_order := 'EVENT_ID,REVERSAL_EVENT_ID,REVERSAL_DATE';
596 l_idx:= l_idx+ 1;
597 l_options_tbl(l_idx).l_tbl := 'FA_DEPRN_DETAIL';
598 l_options_tbl(l_idx).l_gen_select := 'Y';
599 l_options_tbl(l_idx).l_lcs := 'PERIOD_COUNTER,DISTRIBUTION_ID';
600 l_options_tbl(l_idx).l_leading_cols := fafsc('PERIOD_COUNTER')||fafsc('DISTRIBUTION_ID');
601 l_options_tbl(l_idx).l_order_by := 'PERIOD_COUNTER,DISTRIBUTION_ID';
602 l_options_tbl(l_idx).l_lc_header := fparse_header('PERIOD_COUNTER')||fparse_header('DISTRIBUTION_ID');
603 l_options_tbl(l_idx).l_num_cols := 2;
604 l_options_tbl(l_idx).l_col_order := 'DEPRN_SOURCE_CODE,DEPRN_RUN_DATE,DEPRN_AMOUNT'
605 ||',YTD_DEPRN,DEPRN_RESERVE,ADDITION_COST_TO_CLEAR,COST,DEPRN_ADJUSTMENT_AMOUNT';
606 l_idx:= l_idx+ 1;
607 l_options_tbl(l_idx).l_tbl := 'FA_DEPRN_DETAIL_H';
608 l_options_tbl(l_idx).l_gen_select := 'Y';
609 l_options_tbl(l_idx).l_lcs := 'PERIOD_COUNTER,DISTRIBUTION_ID';
610 l_options_tbl(l_idx).l_leading_cols := fafsc('PERIOD_COUNTER')||fafsc('DISTRIBUTION_ID');
611 l_options_tbl(l_idx).l_order_by := 'PERIOD_COUNTER,DISTRIBUTION_ID';
612 l_options_tbl(l_idx).l_lc_header := fparse_header('PERIOD_COUNTER')||fparse_header('DISTRIBUTION_ID');
613 l_options_tbl(l_idx).l_num_cols := 2;
614 l_options_tbl(l_idx).l_col_order := 'EVENT_ID,REVERSAL_EVENT_ID,REVERSAL_DATE';
615 l_idx:= l_idx+ 1;
616 l_options_tbl(l_idx).l_tbl := 'FA_MC_DEPRN_DETAIL';
617 l_options_tbl(l_idx).l_gen_select := 'Y';
618 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,PERIOD_COUNTER,DISTRIBUTION_ID';
619 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('PERIOD_COUNTER')||fafsc('DISTRIBUTION_ID');
620 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,PERIOD_COUNTER,DISTRIBUTION_ID';
621 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('PERIOD_COUNTER')||fparse_header('DISTRIBUTION_ID');
622 l_options_tbl(l_idx).l_num_cols := 3;
623 l_options_tbl(l_idx).l_col_order := 'DEPRN_SOURCE_CODE,DEPRN_RUN_DATE,DEPRN_AMOUNT'
624 ||',YTD_DEPRN,DEPRN_RESERVE,ADDITION_COST_TO_CLEAR,COST,DEPRN_ADJUSTMENT_AMOUNT';
625 l_idx:= l_idx+ 1;
626 l_options_tbl(l_idx).l_tbl := 'FA_MC_DEPRN_DETAIL_H';
627 l_options_tbl(l_idx).l_gen_select := 'Y';
628 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,PERIOD_COUNTER,DISTRIBUTION_ID';
629 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('PERIOD_COUNTER')||fafsc('DISTRIBUTION_ID');
630 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,PERIOD_COUNTER,DISTRIBUTION_ID';
631 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('PERIOD_COUNTER')||fparse_header('DISTRIBUTION_ID');
632 l_options_tbl(l_idx).l_num_cols := 3;
633 l_options_tbl(l_idx).l_col_order := 'EVENT_ID,REVERSAL_EVENT_ID,REVERSAL_DATE';
634 l_idx:= l_idx+ 1;
635 l_options_tbl(l_idx).l_tbl := 'FA_DEPRN_EVENTS';
636 l_options_tbl(l_idx).l_gen_select := 'Y';
637 l_options_tbl(l_idx).l_lcs := 'PERIOD_COUNTER';
638 l_options_tbl(l_idx).l_leading_cols := fafsc('PERIOD_COUNTER');
639 l_options_tbl(l_idx).l_order_by := 'PERIOD_COUNTER';
640 l_options_tbl(l_idx).l_lc_header := fparse_header('PERIOD_COUNTER');
641 l_options_tbl(l_idx).l_num_cols := 1;
642 l_idx:= l_idx+ 1;
643 l_options_tbl(l_idx).l_tbl := 'FA_TRACK_MEMBERS';
644 l_options_tbl(l_idx).l_gen_select := 'Y';
645 l_options_tbl(l_idx).l_lcs := 'TRACK_MEMBER_ID';
646 l_options_tbl(l_idx).l_leading_cols := fafsc('TRACK_MEMBER_ID');
647 l_options_tbl(l_idx).l_order_by := 'TRACK_MEMBER_ID';
648 l_options_tbl(l_idx).l_lc_header := fparse_header('TRACK_MEMBER_ID');
649 l_options_tbl(l_idx).l_num_cols := 1;
650 l_options_tbl(l_idx).l_col_order := 'GROUP_ASSET_ID,PERIOD_COUNTER,FISCAL_YEAR';
651 l_options_tbl(l_idx).l_add_clause := ' FROM FA_TRACK_MEMBERS where member_asset_id = '||g_asset_id||' or group_asset_id = '||g_asset_id;
652 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
653 l_idx:= l_idx+ 1;
654 l_options_tbl(l_idx).l_tbl := 'FA_DEFERRED_DEPRN';
655 l_options_tbl(l_idx).l_gen_select := 'Y';
656 l_options_tbl(l_idx).l_lcs := 'CORP_PERIOD_COUNTER,TAX_PERIOD_COUNTER';
657 l_options_tbl(l_idx).l_leading_cols := fafsc('CORP_PERIOD_COUNTER')||fafsc('TAX_PERIOD_COUNTER');
658 l_options_tbl(l_idx).l_order_by := 'CORP_PERIOD_COUNTER';
659 l_options_tbl(l_idx).l_lc_header := fparse_header('CORP_PERIOD_COUNTER')||fparse_header('TAX_PERIOD_COUNTER');
660 l_options_tbl(l_idx).l_num_cols := 2;
661 l_idx:= l_idx+ 1;
662 l_options_tbl(l_idx).l_tbl := 'FA_DEPRN_OVERRIDE';
663 l_options_tbl(l_idx).l_gen_select := 'Y';
664 l_options_tbl(l_idx).l_lcs := 'DEPRN_OVERRIDE_ID';
665 l_options_tbl(l_idx).l_leading_cols := fafsc('DEPRN_OVERRIDE_ID');
666 l_options_tbl(l_idx).l_order_by := 'DEPRN_OVERRIDE_ID';
667 l_options_tbl(l_idx).l_lc_header := fparse_header('DEPRN_OVERRIDE_ID');
668 l_options_tbl(l_idx).l_num_cols := 1;
669 l_options_tbl(l_idx).l_col_order := 'PERIOD_NAME';
670 l_idx:= l_idx+ 1;
671 l_options_tbl(l_idx).l_tbl := 'FA_RETIREMENTS';
672 l_options_tbl(l_idx).l_gen_select := 'Y';
673 l_options_tbl(l_idx).l_lcs := 'RETIREMENT_ID';
674 l_options_tbl(l_idx).l_leading_cols := fafsc('RETIREMENT_ID');
675 l_options_tbl(l_idx).l_order_by := 'RETIREMENT_ID';
676 l_options_tbl(l_idx).l_lc_header := fparse_header('RETIREMENT_ID');
677 l_options_tbl(l_idx).l_num_cols := 1;
678 l_options_tbl(l_idx).l_col_order := 'TRANSACTION_HEADER_ID_IN,TRANSACTION_HEADER_ID_OUT'
679 ||',DATE_RETIRED,STATUS,DATE_EFFECTIVE,COST_RETIRED,NBV_RETIRED,GAIN_LOSS_AMOUNT';
680 l_idx:= l_idx+ 1;
681 l_options_tbl(l_idx).l_tbl := 'FA_MC_RETIREMENTS';
682 l_options_tbl(l_idx).l_gen_select := 'Y';
683 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,RETIREMENT_ID';
684 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('RETIREMENT_ID');
685 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,RETIREMENT_ID';
686 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('RETIREMENT_ID');
687 l_options_tbl(l_idx).l_num_cols := 2;
688 l_options_tbl(l_idx).l_col_order := 'TRANSACTION_HEADER_ID_IN,TRANSACTION_HEADER_ID_OUT,DATE_RETIRED,STATUS'
689 ||',DATE_EFFECTIVE,COST_RETIRED,NBV_RETIRED,GAIN_LOSS_AMOUNT';
690 l_idx:= l_idx+ 1;
691 l_options_tbl(l_idx).l_tbl := 'FA_ASSET_INVOICES';
692 l_options_tbl(l_idx).l_gen_select := 'Y';
693 l_options_tbl(l_idx).l_lcs := 'SOURCE_LINE_ID';
694 l_options_tbl(l_idx).l_leading_cols := fafsc('SOURCE_LINE_ID');
695 l_options_tbl(l_idx).l_order_by := 'SOURCE_LINE_ID';
696 l_options_tbl(l_idx).l_lc_header := fparse_header('SOURCE_LINE_ID');
697 l_options_tbl(l_idx).l_num_cols := 1;
698 l_options_tbl(l_idx).l_col_order := 'INVOICE_DATE,INVOICE_ID,INVOICE_NUMBER,INVOICE_TRANSACTION_ID_IN,INVOICE_TRANSACTION_ID_OUT';
699 l_idx:= l_idx+ 1;
700 l_options_tbl(l_idx).l_tbl := 'FA_MC_ASSET_INVOICES';
701 l_options_tbl(l_idx).l_gen_select := 'Y';
702 l_options_tbl(l_idx).l_lcs := 'SOURCE_LINE_ID';
703 l_options_tbl(l_idx).l_leading_cols := fafsc('SOURCE_LINE_ID');
704 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,SOURCE_LINE_ID';
705 l_options_tbl(l_idx).l_lc_header := fparse_header('SOURCE_LINE_ID');
706 l_options_tbl(l_idx).l_num_cols := 1;
707 l_options_tbl(l_idx).l_col_order := 'INVOICE_DATE,INVOICE_ID,INVOICE_NUMBER,INVOICE_TRANSACTION_ID_IN,INVOICE_TRANSACTION_ID_OUT';
708 l_idx:= l_idx+ 1;
709 l_options_tbl(l_idx).l_tbl := 'FA_INVOICE_TRANSACTIONS';
710 l_options_tbl(l_idx).l_gen_select := 'Y';
711 l_options_tbl(l_idx).l_lcs := 'INVOICE_TRANSACTION_ID,DATE_EFFECTIVE';
712 l_options_tbl(l_idx).l_leading_cols := fafsc('INVOICE_TRANSACTION_ID')||fafsc('IT.DATE_EFFECTIVE');
713 l_options_tbl(l_idx).l_lc_header := fparse_header('INVOICE_TRANSACTION_ID')||fparse_header('DATE_EFFECTIVE');
714 l_options_tbl(l_idx).l_add_clause := ' FROM FA_INVOICE_TRANSACTIONS it, fa_asset_invoices ai where ai.asset_id = ' || g_asset_id
715 ||' and (ai.invoice_transaction_id_in = it.invoice_transaction_id or ai.invoice_transaction_id_out = it.invoice_transaction_id)'
716 ||' order by INVOICE_TRANSACTION_ID';
717 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(it.invoice_transaction_id) FROM FA_INVOICE_TRANSACTIONS it, fa_asset_invoices ai'
718 ||' where ai.asset_id = ' || g_asset_id ||' and (ai.invoice_transaction_id_in = it.invoice_transaction_id '
719 ||' or ai.invoice_transaction_id_out = it.invoice_transaction_id)';
720 l_idx:= l_idx+ 1;
721 l_options_tbl(l_idx).l_tbl := 'FA_TRX_REFERENCES';
722 l_options_tbl(l_idx).l_gen_select := 'Y';
723 l_options_tbl(l_idx).l_lcs := 'TRX_REFERENCE_ID';
724 l_options_tbl(l_idx).l_leading_cols := fafsc('TRX_REFERENCE_ID');
725 l_options_tbl(l_idx).l_lc_header := fparse_header('TRX_REFERENCE_ID');
726 l_options_tbl(l_idx).l_num_cols := 1;
727 l_options_tbl(l_idx).l_add_clause := ' FROM FA_TRX_REFERENCES WHERE book_type_code = '||''''||g_book||''''
728 ||' and (member_asset_id = '||g_asset_id||' or src_asset_id = '||g_asset_id||' or dest_asset_id = '||g_asset_id||')';
729 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(1) FROM FA_TRX_REFERENCES WHERE book_type_code = '||''''||g_book||''''
730 ||' and (member_asset_id = '||g_asset_id||' or src_asset_id = '||g_asset_id||' or dest_asset_id = '||g_asset_id||')';
731 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
732 l_idx:= l_idx+ 1;
733 l_options_tbl(l_idx).l_tbl := 'FA_BOOKS_SUMMARY';
734 l_options_tbl(l_idx).l_gen_select := 'Y';
735 l_options_tbl(l_idx).l_lcs := 'FISCAL_YEAR,PERIOD_NUM,PERIOD_COUNTER';
736 l_options_tbl(l_idx).l_leading_cols := fafsc('FISCAL_YEAR')||fafsc('PERIOD_NUM')||fafsc('PERIOD_COUNTER');
737 l_options_tbl(l_idx).l_order_by := 'PERIOD_COUNTER';
738 l_options_tbl(l_idx).l_lc_header := fparse_header('FISCAL_YEAR')||fparse_header('PERIOD_NUM')||fparse_header('PERIOD_COUNTER');
739 l_options_tbl(l_idx).l_num_cols := 3;
740
741 --reset index for reuse
742 l_idx:= 0;
743
744 --do exclusions
745 l_idx:= l_idx+ 1; g_col_exclusions(l_idx).cValue := 'ASSET_ID';
746 l_idx:= l_idx+ 1; g_col_exclusions(l_idx).cValue := 'BOOK_TYPE_CODE';
747 l_idx:= l_idx+ 1; g_col_exclusions(l_idx).cValue := 'APPLICATION_ID';
748 l_idx:= l_idx+ 1; g_col_exclusions(l_idx).cValue := 'CREATED_BY';
749 l_idx:= l_idx+ 1; g_col_exclusions(l_idx).cValue := 'CREATION_DATE';
750 l_idx:= l_idx+ 1;
751 g_col_exclusions(l_idx).cValue := 'ATTRIBUTE%'; g_col_exclusions(l_idx).cType := 'P';
752 l_idx:= l_idx+ 1;
753 g_col_exclusions(l_idx).cValue := 'SEGMENT%'; g_col_exclusions(l_idx).cType := 'P';
754 l_idx:= l_idx+ 1;
755 g_col_exclusions(l_idx).cValue := 'TH_ATTRIBUTE%'; g_col_exclusions(l_idx).cType := 'P';
756 l_idx:= l_idx+ 1;
757 g_col_exclusions(l_idx).cValue := 'TRX_ATTRIBUTE%'; g_col_exclusions(l_idx).cType := 'P';
758
759 g_options_tbl :=l_options_tbl;
760
761 --load non-primary
762 load_setup_tbls;
763 --load derpn calc info
764 deprn_calc_info;
765 get_dist_info;
766
767 l_options_tbl := g_options_tbl;
768
769 -- Doing this so as not to needlessly generate statements if MRC is not enabled.
770 IF NVL(g_mrc_enabled,'N') <>'Y' THEN
771 g_options_tbl.delete;
772 l_nmrc_count := g_options_tbl.count + 1;
773 FOR i IN l_options_tbl.first .. l_options_tbl.last LOOP
774 IF substr(l_options_tbl(i).l_tbl,instr(l_options_tbl(i).l_tbl,'_'),4) <> '_MC_' THEN
775 g_options_tbl(l_nmrc_count) :=l_options_tbl(i);
776 l_nmrc_count := l_nmrc_count + 1;
777 END IF;
778 END LOOP;
779 ELSE
780 g_options_tbl :=l_options_tbl;
781 END IF;
782
783 EXCEPTION
784 WHEN OTHERS THEN
785 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
786 p_log_level_rec => p_log_level_rec);
787 raise;
788
789 END load_tbls;
790 --
791 -- Gets setup-related tbls.
792 --
793
794 PROCEDURE load_setup_tbls (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
795
796 l_counter NUMBER;
797 l_idx number;
798 l_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
799
800 l_calling_fn varchar2(40) := 'fa_asset_trace_pkg.load_setup_tbls';
801
802 BEGIN
803
804 l_counter := g_options_tbl.count +1;
805 l_idx := 0;
806
807 l_idx:= l_idx+ 1;
808 l_options_tbl(l_idx).l_tbl :='FA_DEPRN_PERIODS';
809 l_options_tbl(l_idx).l_gen_select := 'Y';
810 l_options_tbl(l_idx).l_lcs := 'PERIOD_COUNTER,PERIOD_NAME';
811 l_options_tbl(l_idx).l_leading_cols := fafsc('PERIOD_COUNTER')||fafsc('PERIOD_NAME');
812 l_options_tbl(l_idx).l_lc_header := fparse_header('PERIOD_COUNTER')||fparse_header('PERIOD_NAME');
813 l_options_tbl(l_idx).l_num_cols := 2;
814 l_options_tbl(l_idx).l_col_order := 'FISCAL_YEAR,PERIOD_NUM,PERIOD_OPEN_DATE,PERIOD_CLOSE_DATE,'
815 ||'CALENDAR_PERIOD_OPEN_DATE,CALENDAR_PERIOD_CLOSE_DATE';
816 l_options_tbl(l_idx).l_add_clause := ' FROM FA_DEPRN_PERIODS WHERE book_type_code = '
817 ||''''||g_book||'''' ||' ORDER BY period_counter';
818 l_idx:= l_idx+ 1;
819 l_options_tbl(l_idx).l_tbl :='FA_MC_DEPRN_PERIODS';
820 l_options_tbl(l_idx).l_gen_select := 'Y';
821 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,PERIOD_COUNTER,PERIOD_NAME';
822 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('PERIOD_COUNTER')||fafsc('PERIOD_NAME');
823 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('PERIOD_COUNTER')||fparse_header('PERIOD_NAME');
824 l_options_tbl(l_idx).l_num_cols := 3;
825 l_options_tbl(l_idx).l_col_order := 'FISCAL_YEAR,PERIOD_NUM,PERIOD_OPEN_DATE,PERIOD_CLOSE_DATE,'
826 ||'CALENDAR_PERIOD_OPEN_DATE,CALENDAR_PERIOD_CLOSE_DATE';
827 l_options_tbl(l_idx).l_add_clause := ' FROM FA_MC_DEPRN_PERIODS WHERE book_type_code = '||''''||g_book||''''
828 ||' ORDER BY SET_OF_BOOKS_ID, period_counter';
829 l_idx:= l_idx+ 1;
830 l_options_tbl(l_idx).l_tbl :='FA_MASS_ADDITIONS';
831 l_options_tbl(l_idx).l_gen_select := 'Y';
832 l_options_tbl(l_idx).l_lcs := 'MASS_ADDITION_ID';
833 l_options_tbl(l_idx).l_leading_cols := fafsc('MASS_ADDITION_ID');
834 l_options_tbl(l_idx).l_order_by := 'MASS_ADDITION_ID';
835 l_options_tbl(l_idx).l_lc_header := fparse_header('MASS_ADDITION_ID');
836 l_options_tbl(l_idx).l_num_cols := 1;
837 l_options_tbl(l_idx).l_col_order := 'ASSET_NUMBER,QUEUE_NAME,POSTING_STATUS,TAG_NUMBER,DESCRIPTION,ASSET_CATEGORY_ID';
838 l_options_tbl(l_idx).l_add_clause := ' FROM fa_mass_additions WHERE asset_number = '||''''||g_asset_number||''''
839 ||' AND book_type_code = '||''''||g_book||''''||' OR add_to_asset_id = '||g_asset_id;
840 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(*) FROM fa_mass_additions WHERE asset_number = '
841 ||''''||g_asset_number||'''' ||' OR add_to_asset_id = '||g_asset_id;
842 l_idx:= l_idx+ 1;
843 l_options_tbl(l_idx).l_tbl :='FA_MASSADD_DISTRIBUTIONS';
844 l_options_tbl(l_idx).l_gen_select := 'Y';
845 l_options_tbl(l_idx).l_lcs := 'MASS_ADDITION_ID';
846 l_options_tbl(l_idx).l_leading_cols := fafsc('MASS_ADDITION_ID');
847 l_options_tbl(l_idx).l_order_by := 'MASS_ADDITION_ID,MASSADD_DIST_ID';
848 l_options_tbl(l_idx).l_lc_header := fparse_header('MASS_ADDITION_ID');
849 l_options_tbl(l_idx).l_num_cols := 1;
850 l_options_tbl(l_idx).l_add_clause := ' FROM FA_MASSADD_DISTRIBUTIONS WHERE mass_addition_id IN (SELECT mass_addition_id FROM '
851 ||'fa_mass_additions WHERE asset_number =' ||''''||g_asset_number||''''||')';
852 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(*) FROM fa_mass_additions WHERE asset_number = '
853 ||''''||g_asset_number||'''' ||' OR add_to_asset_id = '||g_asset_id;
854 l_idx:= l_idx+ 1;
855 l_options_tbl(l_idx).l_tbl :='FA_MC_MASS_RATES';
856 l_options_tbl(l_idx).l_gen_select := 'Y';
857 l_options_tbl(l_idx).l_lcs := 'SET_OF_BOOKS_ID,MASS_ADDITION_ID';
858 l_options_tbl(l_idx).l_leading_cols := fafsc('SET_OF_BOOKS_ID')||fafsc('MASS_ADDITION_ID');
859 l_options_tbl(l_idx).l_order_by := 'SET_OF_BOOKS_ID,MASS_ADDITION_ID';
860 l_options_tbl(l_idx).l_lc_header := fparse_header('SET_OF_BOOKS_ID')||fparse_header('MASS_ADDITION_ID');
861 l_options_tbl(l_idx).l_num_cols := 2;
862 l_options_tbl(l_idx).l_add_clause := ' FROM FA_MC_MASS_RATES WHERE mass_addition_id IN (SELECT mass_addition_id FROM fa_mass_additions '
863 ||'WHERE asset_number =' ||''''||g_asset_number||''''||')';
864 l_options_tbl(l_idx).l_cnt_stmt := 'SELECT count(*) FROM fa_mass_additions WHERE asset_number = '
865 ||''''||g_asset_number||'''' ||' OR add_to_asset_id = '||g_asset_id;
866 l_idx:= l_idx+ 1;
867 l_options_tbl(l_idx).l_tbl :='FA_BOOK_CONTROLS';
868 l_options_tbl(l_idx).l_gen_select := 'Y';
869 l_options_tbl(l_idx).l_add_clause := ' FROM FA_BOOK_CONTROLS WHERE book_type_code = '||''''||g_book||'''';
870 l_idx:= l_idx+ 1;
871 l_options_tbl(l_idx).l_tbl :='FA_MC_BOOK_CONTROLS';
872 l_options_tbl(l_idx).l_gen_select := 'Y';
873 l_options_tbl(l_idx).l_add_clause := ' FROM FA_MC_BOOK_CONTROLS WHERE book_type_code = '||''''||g_book||'''';
874 l_idx:= l_idx+ 1;
875 l_options_tbl(l_idx).l_tbl := 'GL_MC_REPORTING_OPTIONS';
876 l_options_tbl(l_idx).l_gen_select := 'Y';
877 l_options_tbl(l_idx).l_schema := 'GL';
878 l_options_tbl(l_idx).l_lcs := 'REPORTING_SET_OF_BOOKS_ID';
879 l_options_tbl(l_idx).l_leading_cols := fafsc('REPORTING_SET_OF_BOOKS_ID');
880 l_options_tbl(l_idx).l_order_by := 'REPORTING_SET_OF_BOOKS_ID';
881 l_options_tbl(l_idx).l_lc_header := fparse_header('REPORTING_SET_OF_BOOKS_ID');
882 l_options_tbl(l_idx).l_num_cols := 1;
883 l_options_tbl(l_idx).l_add_clause := ' FROM GL_MC_REPORTING_OPTIONS WHERE fa_book_type_code = '||''''||g_book||'''';
884 l_idx:= l_idx+ 1;
885 l_options_tbl(l_idx).l_tbl :='FA_BOOK_CONTROLS_HISTORY';
886 l_options_tbl(l_idx).l_gen_select := 'Y';
887 l_options_tbl(l_idx).l_add_clause := ' FROM FA_BOOK_CONTROLS_HISTORY WHERE book_type_code = '||''''||g_book||''''||' ORDER BY date_active';
888 l_idx:= l_idx+ 1;
889 l_options_tbl(l_idx).l_tbl := 'FA_CATEGORIES_B';
890 l_options_tbl(l_idx).l_gen_select := 'Y';
891 l_options_tbl(l_idx).l_lcs := 'CATEGORY_ID';
892 l_options_tbl(l_idx).l_leading_cols := fafsc('CATEGORY_ID');
893 l_options_tbl(l_idx).l_lc_header := fparse_header('CATEGORY_ID');
894 l_options_tbl(l_idx).l_num_cols := 1;
895 l_options_tbl(l_idx).l_add_clause := ' FROM FA_CATEGORIES_B WHERE category_id IN (SELECT DISTINCT ah.category_id FROM fa_asset_history ah'
896 ||' WHERE ah.asset_id = '||g_asset_id|| ') ORDER BY category_id';
897 l_idx:= l_idx+ 1;
898 l_options_tbl(l_idx).l_tbl := 'FA_CATEGORIES_TL';
899 l_options_tbl(l_idx).l_gen_select := 'Y';
900 l_options_tbl(l_idx).l_lcs := 'CATEGORY_ID';
901 l_options_tbl(l_idx).l_leading_cols := fafsc('CATEGORY_ID');
902 l_options_tbl(l_idx).l_lc_header := fparse_header('CATEGORY_ID');
903 l_options_tbl(l_idx).l_num_cols := 1;
904 l_options_tbl(l_idx).l_add_clause := ' FROM FA_CATEGORIES_TL WHERE category_id IN (SELECT DISTINCT ah.category_id FROM fa_asset_history ah'
905 ||' WHERE ah.asset_id = '||g_asset_id|| ') ORDER BY category_id';
906 l_idx:= l_idx+ 1;
907 l_options_tbl(l_idx).l_tbl := 'FA_CATEGORY_BOOKS';
908 l_options_tbl(l_idx).l_gen_select := 'Y';
909 l_options_tbl(l_idx).l_lcs := 'CATEGORY_ID';
910 l_options_tbl(l_idx).l_leading_cols := fafsc('CATEGORY_ID');
911 l_options_tbl(l_idx).l_lc_header := fparse_header('CATEGORY_ID');
912 l_options_tbl(l_idx).l_num_cols := 1;
913 l_options_tbl(l_idx).l_add_clause := ' FROM fa_category_books WHERE category_id IN (SELECT DISTINCT ah.category_id FROM fa_asset_history ah'
914 ||' WHERE ah.asset_id = '||g_asset_id|| ') AND book_type_code = '||''''||g_book||'''' ||' ORDER BY category_id';
915 l_idx:= l_idx+ 1;
916 l_options_tbl(l_idx).l_tbl := 'FA_CATEGORY_BOOK_DEFAULTS';
917 l_options_tbl(l_idx).l_gen_select := 'Y';
918 l_options_tbl(l_idx).l_lcs := 'CATEGORY_ID';
919 l_options_tbl(l_idx).l_leading_cols := fafsc('CATEGORY_ID');
920 l_options_tbl(l_idx).l_lc_header := fparse_header('CATEGORY_ID');
921 l_options_tbl(l_idx).l_num_cols := 1;
922 l_options_tbl(l_idx).l_add_clause := ' FROM fa_category_book_defaults WHERE category_id IN (SELECT DISTINCT ah.category_id '
923 ||'FROM fa_asset_history ah WHERE ah.asset_id = '||g_asset_id||') AND book_type_code = '||''''||g_book||''''
924 ||' ORDER BY category_id, start_dpis';
925
926 --load global tbl.
927 FOR i IN l_options_tbl.first .. l_options_tbl.last LOOP
928 g_options_tbl(l_counter) :=l_options_tbl(i);
929 l_counter := l_counter + 1;
930 END LOOP;
931
932 EXCEPTION
933 WHEN OTHERS THEN
934 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
935 p_log_level_rec => p_log_level_rec);
936 raise;
937
938 END load_setup_tbls;
939
940 --
941 -- Gets account info for the active distribution(s).
942 -- Not happy with this solution, but works for now.
943 --
944
945 PROCEDURE get_dist_info (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
946
947 l_appid fnd_profile_option_values.level_value_application_id%type;
948 l_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
949 l_select_clause varchar2(2000);
950 l_stmt varchar2(32767);
951 l_tbl varchar2(100);
952 l_dist_tbl t_col_tbl;
953 l_tbl_cols t_cc_cols;
954 l_flex_num number;
955 l_idx number;
956 l_count number :=0;
957 l_cnt_stmt number :=0;
958
959 l_calling_fn varchar2(40) := 'fa_asset_trace_pkg.get_dist_info';
960
961 CURSOR c_coa_segs IS
962 SELECT APPLICATION_COLUMN_NAME, SEGMENT_NAME
963 from fnd_id_flex_segments
964 where application_id = 101
965 and id_flex_code = 'GL#'
966 AND ID_FLEX_NUM = l_flex_num;
967
968 BEGIN
969 --
970 l_appid := FND_GLOBAL.resp_appl_id;
971 --
972 l_idx:= 1;
973 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'ASSET_COST_ACCOUNT_CCID';
974 l_idx:= l_idx+ 1;
975 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'ASSET_CLEARING_ACCOUNT_CCID';
976 l_idx:= l_idx+ 1;
977 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'WIP_COST_ACCOUNT_CCID';
978 l_idx:= l_idx+ 1;
979 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'WIP_CLEARING_ACCOUNT_CCID';
980 l_idx:= l_idx+ 1;
981 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'RESERVE_ACCOUNT_CCID';
982 l_idx:= l_idx+ 1;
983 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'REVAL_AMORT_ACCOUNT_CCID';
984 l_idx:= l_idx+ 1;
985 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'REVAL_RESERVE_ACCOUNT_CCID';
986 l_idx:= l_idx+ 1;
987 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'BONUS_RESERVE_ACCT_CCID';
988 l_idx:= l_idx+ 1;
989 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'IMPAIR_EXPENSE_ACCOUNT_CCID';
990 l_idx:= l_idx+ 1;
991 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'IMPAIR_RESERVE_ACCOUNT_CCID';
992 l_idx:= l_idx+ 1;
993 l_tbl_cols(l_idx).cTbl := 'FA_CATEGORY_BOOKS';l_tbl_cols(l_idx).cCol := 'UNPLAN_EXPENSE_ACCOUNT_CCID';
994 l_idx:= l_idx+ 1;
995 l_tbl_cols(l_idx).cTbl := 'FA_ADJUSTMENTS';l_tbl_cols(l_idx).cCol := 'CODE_COMBINATION_ID';
996 l_idx:= l_idx+ 1;
997 l_tbl_cols(l_idx).cTbl := 'XLA_AE_LINES';l_tbl_cols(l_idx).cCol := 'CODE_COMBINATION_ID';
998 l_idx:= l_idx+ 1;
999 l_tbl_cols(l_idx).cTbl := 'FA_DISTRIBUTION_HISTORY';l_tbl_cols(l_idx).cCol := 'CODE_COMBINATION_ID';
1000 l_idx:= l_idx+ 1;
1001 l_tbl_cols(l_idx).cTbl := 'FA_BOOK_CONTROLS';l_tbl_cols(l_idx).cCol := 'FLEXBUILDER_DEFAULTS_CCID';
1002 l_idx:= l_idx+ 1;
1003 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS'; l_tbl_cols(l_idx).cCol :='ASSET_COST_ACCOUNT_CCID';
1004 l_idx:= l_idx+ 1;
1005 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='ASSET_CLEARING_ACCOUNT_CCID';
1006 l_idx:= l_idx+ 1;
1007 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='DEPRN_EXPENSE_ACCOUNT_CCID';
1008 l_idx:= l_idx+ 1;
1009 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='DEPRN_RESERVE_ACCOUNT_CCID';
1010 l_idx:= l_idx+ 1;
1011 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='CIP_COST_ACCOUNT_CCID';
1012 l_idx:= l_idx+ 1;
1013 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='CIP_CLEARING_ACCOUNT_CCID';
1014 l_idx:= l_idx+ 1;
1015 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='NBV_RETIRED_GAIN_CCID';
1016 l_idx:= l_idx+ 1;
1017 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='NBV_RETIRED_LOSS_CCID';
1018 l_idx:= l_idx+ 1;
1019 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='PROCEEDS_SALE_GAIN_CCID';
1020 l_idx:= l_idx+ 1;
1021 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='PROCEEDS_SALE_LOSS_CCID';
1022 l_idx:= l_idx+ 1;
1023 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='COST_REMOVAL_GAIN_CCID';
1024 l_idx:= l_idx+ 1;
1025 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='COST_REMOVAL_LOSS_CCID';
1026 l_idx:= l_idx+ 1;
1027 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='PROCEEDS_SALE_CLEARING_CCID';
1028 l_idx:= l_idx+ 1;
1029 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='COST_REMOVAL_CLEARING_CCID';
1030 l_idx:= l_idx+ 1;
1031 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='REVAL_RSV_GAIN_ACCOUNT_CCID';
1032 l_idx:= l_idx+ 1;
1033 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='REVAL_RSV_LOSS_ACCOUNT_CCID';
1034 l_idx:= l_idx+ 1;
1035 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='DEFERRED_EXP_ACCOUNT_CCID';
1036 l_idx:= l_idx+ 1;
1037 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='DEFERRED_RSV_ACCOUNT_CCID';
1038 l_idx:= l_idx+ 1;
1039 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='DEPRN_ADJ_ACCOUNT_CCID';
1040 l_idx:= l_idx+ 1;
1041 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='REVAL_AMORT_ACCOUNT_CCID';
1042 l_idx:= l_idx+ 1;
1043 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='REVAL_RSV_ACCOUNT_CCID';
1044 l_idx:= l_idx+ 1;
1045 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='BONUS_EXP_ACCOUNT_CCID';
1046 l_idx:= l_idx+ 1;
1047 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='BONUS_RSV_ACCOUNT_CCID';
1048 l_idx:= l_idx+ 1;
1049 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='IMPAIR_RESERVE_ACCOUNT_CCID';
1050 l_idx:= l_idx+ 1;
1051 l_tbl_cols(l_idx).cTbl :='FA_DISTRIBUTION_ACCOUNTS';l_tbl_cols(l_idx).cCol :='IMPAIR_EXPENSE_ACCOUNT_CCID';
1052
1053 l_idx:= 0; --reset
1054 l_dist_tbl(1) := 'CODE_COMBINATION_ID';
1055 l_dist_tbl(2) := 'ACCOUNT_TYPE';
1056 l_dist_tbl(3) := 'ENABLED_FLAG';
1057 l_dist_tbl(4) := 'START_DATE_ACTIVE';
1058 l_dist_tbl(5) := 'END_DATE_ACTIVE';
1059 --l_dist_tbl(6) := 'ALTERNATE_CODE_COMBINATION_ID';
1060
1061 l_flex_num := fa_cache_pkg.fazcbc_record.accounting_flex_structure;
1062
1063 log(l_calling_fn, 'l_flex_num: '||l_flex_num);
1064
1065 l_idx :=l_dist_tbl.count +1;
1066 FOR crec IN c_coa_segs LOOP
1067 l_dist_tbl(l_idx):=crec.application_column_name;
1068 l_idx:= l_idx+ 1;
1069 END LOOP;
1070
1071 FOR i IN l_dist_tbl.first .. l_dist_tbl.last LOOP
1072 l_select_clause := l_select_clause||','||l_dist_tbl(i);
1073 END LOOP;
1074
1075 l_select_clause := substr(l_select_clause,2,length(l_select_clause));
1076 if g_print_debug then
1077 fa_debug_pkg.add(l_calling_fn, 'getting', 'distribution info',
1078 p_log_level_rec => p_log_level_rec);
1079 fa_debug_pkg.add(l_calling_fn, 'l_select_clause', l_select_clause,
1080 p_log_level_rec => p_log_level_rec);
1081 end if;
1082
1083 l_idx:= 0;
1084
1085 FOR i IN l_tbl_cols.first .. l_tbl_cols.last LOOP
1086
1087 l_count := l_count +1;
1088 l_idx:= l_idx+ 1;
1089 if (l_count = 1) then
1090 l_options_tbl(l_idx).l_tbl := 'CODE_COMBINATIONS';
1091 l_options_tbl(l_idx).l_no_anchor := 'N';
1092 else
1093 l_options_tbl(l_idx).l_tbl := 'NH'||l_tbl_cols(i).cTbl||i;
1094 l_options_tbl(l_idx).l_no_anchor := 'Y';
1095 --l_options_tbl(l_idx).l_no_header := 'Y';
1096 end if;
1097
1098 l_options_tbl(l_idx).l_gen_select := 'N';
1099 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1100 l_options_tbl(l_idx).l_col_order := l_select_clause;
1101 l_options_tbl(l_idx).l_lc_header := fparse_header('SOURCE_TABLE')||fparse_header('CCID_COLUMN');
1102 l_options_tbl(l_idx).l_leading_cols := fafsc(''''||l_tbl_cols(i).cTbl||'''')||fafsc(''''||l_tbl_cols(i).cCol||'''');
1103
1104 IF l_tbl_cols(i).cTbl = 'FA_CATEGORY_BOOKS' THEN
1105 l_options_tbl(l_idx).l_add_clause := ' FROM GL_CODE_COMBINATIONS WHERE code_combination_id IN
1106 (SELECT '||l_tbl_cols(i).cCol||' from fa_category_books where book_type_code = '||''''||g_book||'''' ||
1107 ' and category_id in (SELECT DISTINCT ah.category_id FROM fa_asset_history ah
1108 WHERE ah.asset_id = '||g_asset_id||')) ORDER BY code_combination_id';
1109 l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1110 ELSIF l_tbl_cols(i).cTbl ='FA_ADJUSTMENTS' THEN
1111 l_options_tbl(l_idx).l_add_clause := ' FROM GL_CODE_COMBINATIONS WHERE code_combination_id IN
1112 (SELECT distinct code_combination_id from fa_adjustments where book_type_code = '||''''||g_book||''''||
1113 ' and asset_id = '||g_asset_id||' and code_combination_id is not null) ORDER BY code_combination_id';
1114 l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1115 ELSIF l_tbl_cols(i).cTbl ='XLA_AE_LINES' THEN
1116 l_options_tbl(l_idx).l_add_clause := ' FROM GL_CODE_COMBINATIONS WHERE code_combination_id IN
1117 (select distinct code_combination_id from XLA_AE_LINES where application_id in (140,'||l_appid||') and ae_header_id in
1118 (select ae_header_id from xla_ae_headers where application_id in (140,'||l_appid||') and event_id '||get_event_list
1119 ||')) ORDER BY code_combination_id';
1120 l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1121 ELSIF l_tbl_cols(i).cTbl ='FA_DISTRIBUTION_HISTORY' THEN
1122 l_options_tbl(l_idx).l_add_clause := ' FROM GL_CODE_COMBINATIONS WHERE code_combination_id IN
1123 (SELECT code_combination_id FROM fa_distribution_history
1124 WHERE asset_id = '||g_asset_id||' AND transaction_header_id_out IS NULL) ORDER BY code_combination_id';
1125 l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1126 ELSIF l_tbl_cols(i).cTbl = 'FA_BOOK_CONTROLS' THEN
1127 l_options_tbl(l_idx).l_add_clause := ' FROM GL_CODE_COMBINATIONS WHERE code_combination_id IN
1128 (SELECT FLEXBUILDER_DEFAULTS_CCID FROM fa_book_controls WHERE book_type_code = '||''''||g_book||''''||')';
1129 l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1130 ELSIF l_tbl_cols(i).cTbl='FA_DISTRIBUTION_ACCOUNTS' THEN
1131 l_options_tbl(l_idx).l_lc_header := l_options_tbl(l_idx).l_lc_header||fparse_header('DISTRIBUTION_ID');
1132 l_options_tbl(l_idx).l_leading_cols := l_options_tbl(l_idx).l_leading_cols||fafsc('DISTRIBUTION_ID');
1133 l_options_tbl(l_idx).l_add_clause := ' FROM GL_CODE_COMBINATIONS gcc, fa_distribution_accounts fda
1134 WHERE gcc.code_combination_id = fda.'||l_tbl_cols(i).cCol||' and fda.book_type_code = '||''''||g_book||''''||
1135 ' and fda.distribution_id IN (SELECT distribution_id FROM fa_distribution_history WHERE asset_id = '||g_asset_id||')
1136 ORDER BY code_combination_id';
1137 l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1138 END IF;
1139
1140 EXECUTE IMMEDIATE l_stmt INTO l_cnt_stmt;
1141 if (l_cnt_stmt = 0) then
1142 l_options_tbl.delete(l_idx);
1143 l_idx:= l_idx - 1;
1144 end if;
1145
1146 END LOOP; -- FOR i IN l_tbl_cols.first...
1147
1148 l_count :=0;
1149 l_dist_tbl.delete;
1150
1151 if g_print_debug then
1152 fa_debug_pkg.add(l_calling_fn, 'getting', 'KEY FF info',
1153 p_log_level_rec => p_log_level_rec);
1154 end if;
1155
1156 l_idx:= l_idx+ 1;
1157 l_options_tbl(l_idx).l_tbl := 'KEY_FLEXFIELD_INFO';
1158 l_options_tbl(l_idx).l_gen_select := 'N';
1159 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1160 --l_options_tbl(l_idx).l_no_header := 'N';
1161 l_options_tbl(l_idx).l_col_order := 'SEG.SEGMENT_NUM,SEG.SEGMENT_NAME,SEG.APPLICATION_COLUMN_NAME,VAL.SEGMENT_ATTRIBUTE_TYPE,VAL.ATTRIBUTE_VALUE'
1162 ||',SEG.ENABLED_FLAG,SEG.REQUIRED_FLAG,SEG.DISPLAY_FLAG,SETS.FLEX_VALUE_SET_NAME,SEG.FLEX_VALUE_SET_ID,SEG.SECURITY_ENABLED_FLAG'
1163 ||',SETS.SECURITY_ENABLED_FLAG,STRU.FREEZE_FLEX_DEFINITION_FLAG,STRU.DYNAMIC_INSERTS_ALLOWED_FLAG,STRU.CROSS_SEGMENT_VALIDATION_FLAG';
1164 l_options_tbl(l_idx).l_lcs := 'ID_FLEX_CODE';
1165 l_options_tbl(l_idx).l_lc_header := fparse_header('ID_FLEX_CODE');
1166 l_options_tbl(l_idx).l_leading_cols := fafsc('SEG.ID_FLEX_CODE');
1167 l_options_tbl(l_idx).l_num_cols := 2;
1168 l_options_tbl(l_idx).l_add_clause := ' FROM FND_ID_FLEX_SEGMENTS_VL SEG, FND_FLEX_VALUE_SETS SETS, FND_SEGMENT_ATTRIBUTE_VALUES VAL,'
1169 ||' FA_BOOK_CONTROLS FABC, FND_ID_FLEX_STRUCTURES_VL STRU WHERE SEG.flex_value_set_id = SETS.flex_value_set_id(+)'
1170 ||' AND SEG.id_flex_code in (''LOC#'',''CAT#'',''KEY#'',''GL#'') AND SEG.id_flex_code = VAL.id_flex_code(+)'
1171 ||' AND SEG.id_flex_num = VAL.id_flex_num(+) AND SEG.application_column_name = VAL.application_column_name(+)'
1172 ||' AND VAL.attribute_value(+) = ''Y'' AND VAL.segment_attribute_type(+) <> ''GL_GLOBAL'' AND SEG.id_flex_num = FABC.accounting_flex_structure'
1173 ||' AND FABC.book_type_code = '||'''' || g_book || ''''||' AND STRU.id_flex_code = SEG.id_flex_code AND STRU.id_flex_num = SEG.id_flex_num'
1174 ||' AND STRU.application_id = SEG.application_id order by SEG.ID_FLEX_CODE,SEG.SEGMENT_NUM';
1175
1176 l_count := g_options_tbl.count +1;
1177
1178 --load global tbl.
1179 FOR i IN l_options_tbl.first .. l_options_tbl.last LOOP
1180 g_options_tbl(l_count) :=l_options_tbl(i);
1181 l_count := l_count + 1;
1182 END LOOP;
1183
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
1187 p_log_level_rec => p_log_level_rec);
1188 raise;
1189
1190 END get_dist_info;
1191 --
1192 PROCEDURE deprn_calc_info (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1193
1194 l_stmt varchar2(4000);
1195 l_cnt_stmt varchar2(4000);
1196 l_select_clause varchar2(4000);
1197 l_count number:=0;
1198 l_cols t_col_tbl;
1199 l_tbls t_col_tbl;
1200 l_var_tbls t_col_tbl;
1201 l_tbl varchar2(100);
1202 l_method_cur c_stmt;
1203
1204
1205
1206 l_counter NUMBER;
1207 l_idx number;
1208 l_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
1209
1210 l_calling_fn varchar2(80):= 'fa_asset_trace_pkg.deprn_calc_tbls';
1211
1212 BEGIN
1213
1214 l_counter := g_options_tbl.count +1;
1215 l_idx := 0;
1216
1217 l_idx:= l_idx+ 1;
1218 l_options_tbl(l_idx).l_tbl :='CALENDAR';
1219 l_options_tbl(l_idx).l_gen_select := 'N';
1220 l_options_tbl(l_idx).l_col_order := 'CP.Period_Num,CP.Period_Name,cp.start_date,cp.end_date,'
1221 ||'ct.fiscal_year_name,ct.period_suffix_type,ct.number_per_fiscal_year,ct.description';
1222 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1223 l_options_tbl(l_idx).l_add_clause := ' FROM fa_calendar_types ct,fa_fiscal_year fy,fa_calendar_periods cp'||
1224 ' where CT.calendar_type = cp.calendar_type and cp.calendar_type = '||'''' ||g_deprn_calendar|| ''''||
1225 ' AND fy.fiscal_year_name = '||'''' ||g_fiscal_year_name|| ''''||
1226 ' and fy.fiscal_year BETWEEN '||(g_fiscal_year-1)||' AND '||g_fiscal_year||
1227 ' and fy.fiscal_year_name = ct.fiscal_year_name and cp.start_date >= fy.start_date '||
1228 ' and cp.end_date <= fy.end_date order by fy.fiscal_year, cp.period_num';
1229 l_idx:= l_idx+ 1;
1230 l_options_tbl(l_idx).l_tbl :='CONVENTIONS';
1231 l_options_tbl(l_idx).l_gen_select := 'N';
1232 l_options_tbl(l_idx).l_col_order := 'CT.PRORATE_CONVENTION_CODE,CT.DESCRIPTION,CO.START_DATE,'
1233 ||'CO.END_DATE,CO.PRORATE_DATE,CT.DEPR_WHEN_ACQUIRED_FLAG';
1234 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1235 l_options_tbl(l_idx).l_add_clause := ' FROM FA_CONVENTIONS CO, FA_CONVENTION_TYPES CT, FA_FISCAL_YEAR FY '
1236 ||'WHERE FY.FISCAL_YEAR BETWEEN '||(g_fiscal_year-1)||' AND '||g_fiscal_year||
1237 ' AND FY.FISCAL_YEAR_NAME = '||'''' ||g_fiscal_year_name|| ''''||
1238 ' AND (CO.PRORATE_CONVENTION_CODE IN (select distinct PRORATE_CONVENTION_CODE from fa_books '||
1239 ' where asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||') '||
1240 ' OR CO.PRORATE_CONVENTION_CODE in (select distinct RETIREMENT_PRORATE_CONVENTION from fa_retirements '||
1241 ' where asset_id = '||g_asset_id|| ' and book_type_code = '||'''' || g_book || ''''||')) '||
1242 ' AND CO.START_DATE BETWEEN FY.START_DATE AND FY.END_DATE '||
1243 ' AND CO.PRORATE_CONVENTION_CODE = CT.PRORATE_CONVENTION_CODE ORDER BY CT.PRORATE_CONVENTION_CODE, CO.START_DATE';
1244 l_idx:= l_idx+ 1;
1245 l_options_tbl(l_idx).l_tbl := 'BONUS_RULES';
1246 l_options_tbl(l_idx).l_gen_select := 'N';
1247 l_options_tbl(l_idx).l_col_order := 'br.Bonus_Rule,br.Start_Year,br.End_Year,br.Bonus_Rate * 100,brl.ONE_TIME_FLAG';
1248 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1249 l_options_tbl(l_idx).l_add_clause := ' from FA_BONUS_RATES br, FA_BONUS_RULES brl '||
1250 ' where BR.BONUS_RULE=BRL.BONUS_RULE and BR.BONUS_RULE IN (select distinct BONUS_RULE from fa_books '||
1251 ' where asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||') '||
1252 ' order by BR.Bonus_Rule,BR.Start_Year';
1253 l_options_tbl(l_idx).l_cnt_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1254 l_idx:= l_idx+ 1;
1255 l_options_tbl(l_idx).l_tbl :='FA_CEILINGS';
1256 l_options_tbl(l_idx).l_gen_select := 'N';
1257 l_options_tbl(l_idx).l_col_order := 'CT.CEILING_TYPE,CT.Currency_Code,CL.Ceiling_Name,CL.Start_Date,CL.End_Date,'
1258 ||'CL.Year_Of_Life,CL.Limit';
1259 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1260 l_options_tbl(l_idx).l_add_clause := ' FROM FA_CEILINGS CL, FA_CEILING_TYPES CT '
1261 ||' WHERE cl.ceiling_name = ct.ceiling_name AND (cl.ceiling_name in (select distinct Ceiling_Name '||
1262 ' from fa_books where asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||') '
1263 ||' OR cl.ceiling_name in (select Ceiling_Name from fa_category_book_defaults '||
1264 ' where category_id in (SELECT DISTINCT ah.category_id FROM fa_asset_history ah '||
1265 ' WHERE ah.asset_id = '||g_asset_id|| ') and book_type_code = '||'''' || g_book || ''''||'))';
1266 l_options_tbl(l_idx).l_cnt_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1267 l_idx:= l_idx+ 1;
1268 l_options_tbl(l_idx).l_tbl :='RATES';
1269 l_options_tbl(l_idx).l_gen_select := 'N';
1270 l_options_tbl(l_idx).l_col_order := 'mt.method_code,mt.name,mt.life_in_months,mt.stl_method_flag,'
1271 ||'mt.depreciate_lastyear_flag,rt.year,rt.period_placed_in_service,rt.rate';
1272 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1273 l_options_tbl(l_idx).l_add_clause := ' from fa_methods mt, fa_rates rt, fa_books bk, fa_calendar_periods cp'
1274 ||' where mt.method_id = rt.method_id and mt.method_code = bk.deprn_method_code and mt.life_in_months = bk.life_in_months'
1275 ||' and bk.asset_id = '||g_asset_id||' and bk.book_type_code = '||'''' || g_book || ''''||' and cp.calendar_type = '
1276 ||'''' ||g_prorate_calendar|| ''''||' and bk.prorate_date between cp.start_date and cp.end_date'
1277 ||' and rt.PERIOD_PLACED_IN_SERVICE = cp.period_num order by mt.method_code, mt.life_in_months, rt.year';
1278 l_options_tbl(l_idx).l_cnt_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1279 l_idx:= l_idx+ 1;
1280 l_options_tbl(l_idx).l_tbl :='FA_METHODS';
1281 l_options_tbl(l_idx).l_lcs := 'METHOD_ID';
1282 l_options_tbl(l_idx).l_leading_cols := fafsc('METHOD_ID');
1283 l_options_tbl(l_idx).l_order_by := 'METHOD_ID';
1284 l_options_tbl(l_idx).l_lc_header := fparse_header('METHOD_ID');
1285 l_options_tbl(l_idx).l_num_cols := 1;
1286 l_options_tbl(l_idx).l_col_order := 'METHOD_CODE,NAME,LIFE_IN_MONTHS';
1287 l_options_tbl(l_idx).l_add_clause := ' FROM fa_methods WHERE METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999))'
1288 ||' IN (select DEPRN_METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999)) from fa_books'
1289 ||' where book_type_code = '||'''' || g_book || ''''||' and asset_id = '||g_asset_id||')';
1290 l_idx:= l_idx+ 1;
1291 l_options_tbl(l_idx).l_tbl :='FA_DEPRN_BASIS_RULES';
1292 l_options_tbl(l_idx).l_add_clause := ' FROM fa_deprn_basis_rules WHERE deprn_basis_rule_id IN (SELECT deprn_basis_rule_id'
1293 ||' FROM fa_methods WHERE METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999)) IN '
1294 ||'(select DEPRN_METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999)) from fa_books where book_type_code = '
1295 ||'''' || g_book || ''''||' and asset_id = '||g_asset_id||'))';
1296 l_options_tbl(l_idx).l_cnt_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1297 l_idx:= l_idx+ 1;
1298 l_options_tbl(l_idx).l_tbl :='FA_FLAT_RATES';
1299 l_options_tbl(l_idx).l_add_clause := ' From fa_flat_rates Where to_char(method_id) || to_char(basic_rate) in '
1300 ||'(SELECT to_char(mt.method_id) || to_char(basic_rate) FROM FA_METHODS mt, fa_books bk '
1301 ||'where mt.method_code = bk.deprn_method_code and nvl(mt.life_in_months,0) = nvl(bk.life_in_months,0) '
1302 ||'and bk.book_type_code = '||'''' || g_book || ''''||' and bk.asset_id = '||g_asset_id||')';
1303 l_options_tbl(l_idx).l_cnt_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1304 l_idx:= l_idx+ 1;
1305 l_options_tbl(l_idx).l_tbl :='FA_FISCAL_YEAR';
1306 l_options_tbl(l_idx).l_lcs := 'FISCAL_YEAR_NAME';
1307 l_options_tbl(l_idx).l_leading_cols := fafsc('FISCAL_YEAR_NAME');
1308 l_options_tbl(l_idx).l_lc_header := fparse_header('FISCAL_YEAR_NAME');
1309 l_options_tbl(l_idx).l_num_cols := 1;
1310 l_options_tbl(l_idx).l_col_order := 'FISCAL_YEAR,START_DATE,END_DATE';
1311 l_options_tbl(l_idx).l_add_clause := ' FROM fa_fiscal_year where fiscal_year_name = '||'''' ||g_fiscal_year_name|| ''''
1312 ||' and fiscal_year BETWEEN '||(g_fiscal_year-1)||' AND '||g_fiscal_year||' Order by FISCAL_YEAR, START_DATE';
1313 l_idx:= l_idx+ 1;
1314 l_options_tbl(l_idx).l_tbl :='FA_FORMULAS';
1315 l_options_tbl(l_idx).l_add_clause := ' From fa_formulas Where method_id in (SELECT mt.method_id '
1316 ||'FROM FA_METHODS mt, fa_books bk where mt.method_code = bk.deprn_method_code '
1317 ||'and nvl(mt.life_in_months,0) = nvl(bk.life_in_months,0) and bk.book_type_code = '||'''' || g_book || ''''
1318 ||' and bk.asset_id = '||g_asset_id||')';
1319 l_options_tbl(l_idx).l_cnt_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;
1320
1321 --load global tbl.
1322 FOR i IN l_options_tbl.first .. l_options_tbl.last LOOP
1323 g_options_tbl(l_counter) :=l_options_tbl(i);
1324 l_counter := l_counter + 1;
1325 END LOOP;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
1330 p_log_level_rec => p_log_level_rec);
1331 log(l_calling_fn, 'Exception, tbl: '||l_options_tbl(l_idx).l_tbl);
1332 raise;
1333
1334 END deprn_calc_info;
1335 --
1336 FUNCTION get_event_list RETURN VARCHAR2 IS
1337
1338 l_appid fnd_profile_option_values.level_value_application_id%type;
1339 l_event_list VARCHAR2(32767) := ' in (';
1340
1341 l_calling_fn varchar2(80) := 'fa_asset_trace_pkg.get_event_list';
1342
1343 BEGIN
1344 l_appid := FND_GLOBAL.resp_appl_id;
1345 if (l_appid = -1) then
1346 --must have been called from backend. Assuming 140.
1347 l_appid := 140;
1348 end if;
1349
1350 /* 1- trx 2/3- dist-related 4- inter-asset 5- deprn */
1351
1352 l_event_list := l_event_list|| 'select ev.event_id
1353 from fa_transaction_headers th, xla_transaction_entities en, xla_events ev
1354 where th.book_type_code = '||'''' || g_book || ''''||
1355 ' and th.asset_id = '||g_asset_id||
1356 ' and en.application_id in ('||l_appid||',140)
1357 and en.ledger_id = '||g_sob_id||
1358 ' and en.entity_code in (''MANUAL'', ''TRANSACTIONS'')
1359 and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
1360 and en.valuation_method = '||'''' || g_book || ''''||
1361 ' and ev.application_id in ('||l_appid||',140)
1362 and ev.entity_id = en.entity_id
1363 union
1364 select ev.event_id
1365 from fa_transaction_headers th, xla_transaction_entities en, xla_events ev
1366 where th.book_type_code = '||'''' || g_source_book || ''''||
1367 ' and th.asset_id = '||g_asset_id||
1368 ' and th.transaction_type_code in (''REINSTATEMENT'')
1369 and en.application_id in ('||l_appid||',140)
1370 and en.ledger_id = '||g_sob_id||
1371 ' and en.entity_code in (''TRANSACTIONS'')
1372 and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
1373 and en.valuation_method = '||'''' || g_book || ''''||
1374 ' and en.valuation_method <> '||'''' || g_source_book || ''''||
1375 ' and ev.application_id in ('||l_appid||',140)
1376 and ev.entity_id = en.entity_id
1377 union
1378 select ev.event_id
1379 from fa_transaction_headers th, xla_transaction_entities en, xla_events ev
1380 where th.book_type_code = '||'''' || g_source_book || ''''||
1381 ' and th.asset_id = '||g_asset_id||
1382 ' and th.transaction_type_code in (''UNIT ADJUSTMENT'',''TRANSFER OUT'',''TRANSFER'',''RECLASS'')
1383 and en.application_id in ('||l_appid||',140)
1384 and en.ledger_id = '||g_sob_id||
1385 ' and en.entity_code in (''MANUAL'', ''TRANSACTIONS'')
1386 and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
1387 and en.valuation_method = '||'''' || g_book || ''''||
1388 ' and ev.application_id in ('||l_appid||',140)
1389 and ev.entity_id = en.entity_id
1390 union
1391 select ev.event_id
1392 from fa_transaction_headers th, xla_transaction_entities en, xla_events ev
1393 where th.book_type_code = '||'''' || g_book || ''''||
1394 ' and th.asset_id = '||g_asset_id||
1395 ' and en.application_id in ('||l_appid||',140)
1396 and en.ledger_id = '||g_sob_id||
1397 ' and en.entity_code in (''MANUAL'', ''INTER_ASSET_TRANSACTIONS'')
1398 and nvl(en.source_id_int_1, (-99)) = th.trx_reference_id
1399 and ev.application_id in ('||l_appid||',140)
1400 and ev.entity_id = en.entity_id
1401 union
1402 select ev.event_id
1403 from xla_transaction_entities en, xla_events ev, fa_book_controls bc
1404 where bc.book_type_code = '||'''' || g_book || ''''||
1405 ' and en.application_id in ('||l_appid||',140)
1406 and en.ledger_id = '||g_sob_id||
1407 ' and en.entity_code in (''MANUAL'', ''DEPRECIATION'',''DEFERRED_DEPRECIATION'')
1408 and nvl(en.source_id_int_1, (-99)) = '||g_asset_id||
1409 ' and nvl(en.source_id_char_1, '''') = bc.book_type_code
1410 and ev.application_id in ('||l_appid||',140)
1411 and ev.entity_id = en.entity_id ) ' ;
1412
1413 if (g_print_debug) then
1414 log(l_calling_fn,'l_event_list: '||l_event_list);
1415 end if;
1416
1417 RETURN l_event_list;
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 log(l_calling_fn,'l_event_list: '||l_event_list);
1422 raise;
1423 END get_event_list;
1424 --
1425 PROCEDURE load_xla_info (p_banner IN varchar2,
1426 x_retcode OUT NOCOPY NUMBER,
1427 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1428
1429 l_event_list VARCHAR2(32767);
1430 l_tblcount number;
1431 l_retcode number;
1432
1433 l_counter NUMBER;
1434 l_idx number;
1435 l_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
1436 l_col_exclusions FA_ASSET_TRACE_PUB.t_excl_tbl;
1437 l_appid fnd_profile_option_values.level_value_application_id%type;
1438
1439 l_calling_fn varchar2(80) := 'fa_asset_trace_pkg.load_xla_info';
1440
1441 BEGIN
1442
1443 l_counter := g_options_tbl.count +1;
1444 l_idx := 0;
1445 l_appid := FND_GLOBAL.resp_appl_id;
1446 if (l_appid = -1) then
1447 --must have been called from backend. Assuming 140.
1448 l_appid := 140;
1449 end if;
1450
1451 l_event_list := get_event_list;
1452
1453 if (l_event_list <> ' in (-6741301)') then
1454
1455 l_idx:= l_idx+ 1;
1456 l_options_tbl(l_idx).l_tbl := 'XLA_EVENTS';
1457 l_options_tbl(l_idx).l_schema := get_schema('XLA');
1458 l_options_tbl(l_idx).l_gen_select := 'Y';
1459 l_options_tbl(l_idx).l_lcs := 'EVENT_ID,ENTITY_ID';
1460 l_options_tbl(l_idx).l_col_order := 'EVENT_TYPE_CODE,EVENT_STATUS_CODE,PROCESS_STATUS_CODE,EVENT_DATE';
1461 l_options_tbl(l_idx).l_leading_cols := fafsc('EVENT_ID')||fafsc('ENTITY_ID');
1462 l_options_tbl(l_idx).l_lc_header := fparse_header('EVENT_ID')||fparse_header('ENTITY_ID');
1463 l_options_tbl(l_idx).l_num_cols := 2;
1464 l_options_tbl(l_idx).l_add_clause := ' from XLA_EVENTS where application_id in (140,'||l_appid||') and event_id '||l_event_list;
1465 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1466 l_idx:= l_idx+ 1;
1467 l_options_tbl(l_idx).l_tbl := 'XLA_TRANSACTION_ENTITIES';
1468 l_options_tbl(l_idx).l_schema := get_schema('XLA');
1469 l_options_tbl(l_idx).l_gen_select := 'Y';
1470 l_options_tbl(l_idx).l_col_order := 'ENTITY_CODE,VALUATION_METHOD';
1471 l_options_tbl(l_idx).l_lcs := 'ENTITY_ID,LEDGER_ID';
1472 l_options_tbl(l_idx).l_leading_cols := fafsc('ENTITY_ID')||fafsc('LEDGER_ID');
1473 l_options_tbl(l_idx).l_lc_header := fparse_header('ENTITY_ID')||fparse_header('LEDGER_ID');
1474 l_options_tbl(l_idx).l_num_cols := 2;
1475 l_options_tbl(l_idx).l_add_clause := ' from XLA_TRANSACTION_ENTITIES where application_id in (140,'||l_appid||')'
1476 ||' and entity_id in (select entity_id from xla_events where event_id '||l_event_list||')';
1477 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1478 l_idx:= l_idx+ 1;
1479 l_options_tbl(l_idx).l_tbl := 'XLA_AE_HEADERS';
1480 l_options_tbl(l_idx).l_schema := get_schema('XLA');
1481 l_options_tbl(l_idx).l_gen_select := 'Y';
1482 l_options_tbl(l_idx).l_lcs := 'AE_HEADER_ID,EVENT_ID,LEDGER_ID';
1483 l_options_tbl(l_idx).l_leading_cols := fafsc('AE_HEADER_ID')||fafsc('EVENT_ID')||fafsc('LEDGER_ID');
1484 l_options_tbl(l_idx).l_lc_header := fparse_header('AE_HEADER_ID')||fparse_header('EVENT_ID')||fparse_header('LEDGER_ID');
1485 l_options_tbl(l_idx).l_col_order := 'EVENT_TYPE_CODE,ACCOUNTING_DATE,GL_TRANSFER_STATUS_CODE,GL_TRANSFER_DATE,'
1486 ||'JE_CATEGORY_NAME,ACCOUNTING_ENTRY_STATUS_CODE,ACCOUNTING_ENTRY_TYPE_CODE';
1487 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1488 l_options_tbl(l_idx).l_num_cols := 3;
1489 l_options_tbl(l_idx).l_add_clause := ' from XLA_AE_HEADERS where application_id in (140,'||l_appid||') and event_id '||l_event_list;
1490 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1491 l_idx:= l_idx+ 1;
1492 l_options_tbl(l_idx).l_tbl := 'XLA_AE_LINES';
1493 l_options_tbl(l_idx).l_schema := get_schema('XLA');
1494 l_options_tbl(l_idx).l_gen_select := 'Y';
1495 l_options_tbl(l_idx).l_lcs := 'AE_HEADER_ID,AE_LINE_NUM,LEDGER_ID';
1496 l_options_tbl(l_idx).l_leading_cols := fafsc('AE_HEADER_ID')||fafsc('AE_LINE_NUM')||fafsc('LEDGER_ID');
1497 l_options_tbl(l_idx).l_lc_header := fparse_header('AE_HEADER_ID')||fparse_header('AE_LINE_NUM')||fparse_header('LEDGER_ID');
1498 l_options_tbl(l_idx).l_col_order := 'CODE_COMBINATION_ID,ENTERED_DR,ENTERED_CR,ACCOUNTED_DR,ACCOUNTED_CR,GL_TRANSFER_MODE_CODE,ACCOUNTING_CLASS_CODE';
1499 l_options_tbl(l_idx).l_num_cols := 3;
1500 l_options_tbl(l_idx).l_add_clause := ' from XLA_AE_LINES where application_id in (140,'||l_appid||')'
1501 ||' and ae_header_id in (select ae_header_id from xla_ae_headers where application_id in (140,'||l_appid||') and event_id '||l_event_list||')';
1502 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1503 l_idx:= l_idx+ 1;
1504 l_options_tbl(l_idx).l_tbl := 'XLA_DISTRIBUTION_LINKS';
1505 l_options_tbl(l_idx).l_schema := get_schema('XLA');
1506 l_options_tbl(l_idx).l_gen_select := 'Y';
1507 l_options_tbl(l_idx).l_lcs := 'EVENT_ID,AE_HEADER_ID,AE_LINE_NUM';
1508 l_options_tbl(l_idx).l_leading_cols := fafsc('EVENT_ID')||fafsc('AE_HEADER_ID')||fafsc('AE_LINE_NUM');
1509 l_options_tbl(l_idx).l_lc_header := fparse_header('EVENT_ID')||fparse_header('AE_HEADER_ID')||fparse_header('AE_LINE_NUM');
1510 l_options_tbl(l_idx).l_num_cols := 3;
1511 l_options_tbl(l_idx).l_add_clause := ' from XLA_DISTRIBUTION_LINKS where application_id in (140,'||l_appid||') AND ae_header_id IN '
1512 ||'(SELECT ae_header_id FROM xla_ae_headers WHERE application_id in (140,'||l_appid||') AND event_id '||l_event_list||' )';
1513 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1514 l_idx:= l_idx+ 1;
1515 l_options_tbl(l_idx).l_tbl := 'XLA_ACCTG_METHOD_RULES_FVL';
1516 l_options_tbl(l_idx).l_schema := 'APPS';
1517 l_options_tbl(l_idx).l_gen_select := 'Y';
1518 l_options_tbl(l_idx).l_lcs := 'ACCTG_METHOD_RULE_ID';
1519 l_options_tbl(l_idx).l_leading_cols := fafsc('ACCTG_METHOD_RULE_ID');
1520 l_options_tbl(l_idx).l_lc_header := fparse_header('ACCTG_METHOD_RULE_ID');
1521 l_options_tbl(l_idx).l_num_cols := 1;
1522 l_options_tbl(l_idx).l_add_clause := ' FROM xla_acctg_method_rules_fvl where application_id in (140,'||l_appid||') and ACCOUNTING_METHOD_CODE in ' ||
1523 '(select SLA_ACCOUNTING_METHOD_CODE from gl_ledgers where ledger_id = '||g_sob_id||')';
1524 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1525 l_idx:= l_idx+ 1;
1526 l_options_tbl(l_idx).l_tbl := 'XLA_PRODUCT_RULES_FVL';
1527 l_options_tbl(l_idx).l_schema := 'APPS';
1528 l_options_tbl(l_idx).l_gen_select := 'Y';
1529 l_options_tbl(l_idx).l_lcs := 'PRODUCT_RULE_CODE';
1530 l_options_tbl(l_idx).l_leading_cols := fafsc('PRODUCT_RULE_CODE');
1531 l_options_tbl(l_idx).l_lc_header := fparse_header('PRODUCT_RULE_CODE');
1532 l_options_tbl(l_idx).l_num_cols := 1;
1533 l_options_tbl(l_idx).l_add_clause := ' from xla_product_rules_fvl where application_id in (140,'||l_appid||') and product_rule_code in '||
1534 '(select distinct product_rule_code from xla_ae_headers where application_id in (140,'||l_appid||') and event_id '||l_event_list||')';
1535 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1536 l_idx:= l_idx+ 1;
1537 l_options_tbl(l_idx).l_tbl := 'XLA_LEDGER_OPTIONS';
1538 l_options_tbl(l_idx).l_schema := get_schema('XLA');
1539 l_options_tbl(l_idx).l_gen_select := 'Y';
1540 l_options_tbl(l_idx).l_lcs := 'APPLICATION_ID';
1541 l_options_tbl(l_idx).l_leading_cols := fafsc('APPLICATION_ID');
1542 l_options_tbl(l_idx).l_lc_header := fparse_header('APPLICATION_ID');
1543 l_options_tbl(l_idx).l_num_cols := 1;
1544 l_options_tbl(l_idx).l_add_clause := ' from xla_ledger_options where (ledger_id = '||g_sob_id||' or ledger_id in (select target_ledger_id '||
1545 ' FROM gl_ledger_relationships WHERE primary_ledger_id = '||g_sob_id||' AND relationship_type_code <> ''NONE'' '||
1546 ' AND application_id IN (101,140) AND relationship_enabled_flag = ''Y'')) and application_id IN (101,140) order by APPLICATION_ID, LEDGER_ID';
1547 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1548 l_idx:= l_idx+ 1;
1549 l_options_tbl(l_idx).l_tbl := 'GL_LEDGERS';
1550 l_options_tbl(l_idx).l_schema := get_schema('SQLGL');
1551 l_options_tbl(l_idx).l_gen_select := 'N';
1552 l_options_tbl(l_idx).l_fullcol_list := 'Y';
1553 l_options_tbl(l_idx).l_lc_header := fparse_header('LEDGER_ID');
1554 l_options_tbl(l_idx).l_leading_cols := fafsc('LEDGER_ID');
1555 l_options_tbl(l_idx).l_col_order := 'NAME,ACCOUNTED_PERIOD_TYPE,ALC_LEDGER_TYPE_CODE,ALLOW_INTERCOMPANY_POST_FLAG,CHART_OF_ACCOUNTS_ID,'||
1556 ' COMPLETE_FLAG,CONFIGURATION_ID,CURRENCY_CODE,IMPLICIT_ACCESS_SET_ID,LE_LEDGER_TYPE_CODE,LEDGER_CATEGORY_CODE,SLA_ACCOUNTING_METHOD_CODE,'||
1557 ' SLA_ACCOUNTING_METHOD_TYPE,SLA_ENTERED_CUR_BAL_SUS_CCID,SLA_SEQUENCING_FLAG,SLA_BAL_BY_LEDGER_CURR_FLAG,SLA_LEDGER_CUR_BAL_SUS_CCID';
1558 l_options_tbl(l_idx).l_add_clause := ' from gl_ledgers where ledger_id = '||g_sob_id||' or ledger_id in (select target_ledger_id '||
1559 ' FROM gl_ledger_relationships WHERE primary_ledger_id = '||g_sob_id||' AND relationship_type_code <> ''NONE'' '||
1560 ' AND application_id IN (101,140) AND relationship_enabled_flag = ''Y'') order by ledger_id';
1561 l_options_tbl(l_idx).l_cnt_stmt := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;
1562
1563 --do exclusions
1564 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'LAST_UPDATE_LOGIN';
1565 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'SOURCE_APPLICATION_ID';
1566 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'APPLICATION_ID';
1567 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'BUDGET_VERSION_ID';
1568 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'ENCUMBRANCE_TYPE_ID';
1569 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'USSGL_TRANSACTION_CODE';
1570 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'ACCOUNT_OVERLAY_SOURCE_ID';
1571 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'ANALYTICAL_BALANCE_FLAG';
1572 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'BUSINESS_CLASS_CODE';
1573 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'CONTROL_BALANCE_FLAG';
1574 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'PACKET_ID';
1575 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'PROGRAM_ID';
1576 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'PROGRAM_APPLICATION_ID';
1577 l_idx:= l_idx+ 1; l_col_exclusions(l_idx).cValue := 'ROW_ID';
1578 l_idx:= l_idx+ 1;
1579 l_col_exclusions(l_idx).cValue := 'SECURITY_ID%'; l_col_exclusions(l_idx).cType := 'P';
1580 l_idx:= l_idx+ 1;
1581 l_col_exclusions(l_idx).cValue := 'ATTRIBUTE%'; l_col_exclusions(l_idx).cType := 'P';
1582 l_idx:= l_idx+ 1;
1583 l_col_exclusions(l_idx).cValue := 'CLOSE_ACCT%'; l_col_exclusions(l_idx).cType := 'P';
1584 l_idx:= l_idx+ 1;
1585 l_col_exclusions(l_idx).cValue := 'REFERENCE%'; l_col_exclusions(l_idx).cType := 'P';
1586 l_idx:= l_idx+ 1;
1587 l_col_exclusions(l_idx).cValue := 'COMPLETION_ACCT%'; l_col_exclusions(l_idx).cType := 'P';
1588 l_idx:= l_idx+ 1;
1589 l_col_exclusions(l_idx).cValue := 'DOC%'; l_col_exclusions(l_idx).cType := 'P';
1590 l_idx:= l_idx+ 1;
1591 l_col_exclusions(l_idx).cValue := 'UPG_TAX%'; l_col_exclusions(l_idx).cType := 'P';
1592 l_idx:= l_idx+ 1;
1593 l_col_exclusions(l_idx).cValue := 'UNROUNDED%'; l_col_exclusions(l_idx).cType := 'P';
1594 l_idx:= l_idx+ 1;
1595 l_col_exclusions(l_idx).cValue := 'APPLIED_TO%'; l_col_exclusions(l_idx).cType := 'P';
1596 l_idx:= l_idx+ 1;
1597 l_col_exclusions(l_idx).cValue := 'ALLOC_TO%'; l_col_exclusions(l_idx).cType := 'P';
1598 l_idx:= l_idx+ 1;
1599 l_col_exclusions(l_idx).cValue := 'TAX%'; l_col_exclusions(l_idx).cType := 'P';
1600 l_idx:= l_idx+ 1;
1601 l_col_exclusions(l_idx).cValue := '%PARTY%'; l_col_exclusions(l_idx).cType := 'P';
1602 --
1603 FA_ASSET_TRACE_PUB.run_trace (p_opt_tbl => l_options_tbl,
1604 p_exc_tbl => l_col_exclusions,
1605 p_tdyn_head => null,
1606 p_stmt => null,
1607 p_sys_opt_tbl => NULL,
1608 p_use_utl_file => fa_asset_trace_pkg.g_use_utl_file,
1609 p_debug_flag => g_print_debug,
1610 p_calling_prog => p_banner,
1611 p_retcode => x_retcode,
1612 p_log_level_rec => g_log_level_rec);
1613
1614 log(l_calling_fn,'back in load_xla, p_retcode is '||x_retcode);
1615 else
1616 log(l_calling_fn,'No events found in SLA for this asset and book combination.');
1617 x_retcode := 1;
1618 end if; --length(l_event_list)
1619
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1623 ,p_log_level_rec => p_log_level_rec);
1624 raise;
1625
1626 END load_xla_info;
1627 --
1628 PROCEDURE submit_subrequest(p_parent_request IN NUMBER,
1629 x_retcode OUT NOCOPY NUMBER,
1630 x_errbuf OUT NOCOPY VARCHAR2) IS
1631
1632 l_desc VARCHAR2(100);
1633 l_sub_rule varchar2(200);
1634 l_xla_reqID number;
1635 l_Result boolean;
1636
1637 l_calling_fn varchar2(80) := 'fa_asset_trace_pkg.submit_subrequest';
1638
1639 BEGIN
1640
1641 l_desc := 'FATRACE - SLA Subrequest of '||p_parent_request;
1642 log(l_calling_fn, 'submit_request: '||l_desc);
1643
1644 if (nvl(fa_asset_trace_pkg.g_use_utl_file, 'N') = 'N') then
1645 --do not use this method when calling from backend.
1646 l_xla_reqID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'OFA',
1647 PROGRAM => 'FATRACE',
1648 DESCRIPTION => l_desc,
1649 ARGUMENT1 => g_book,
1650 ARGUMENT2 => to_char(g_asset_id));
1651 log(l_calling_fn, 'after fnd submit_request, l_xla_reqID: '||l_xla_reqID);
1652 end if;
1653
1654 if (nvl(l_xla_reqID,99) <= 0) then
1655 log(l_calling_fn, 'Failed to submit xla request.');
1656 x_errbuf := fnd_message.get;
1657 x_retcode := 2;
1658 else
1659 l_sub_rule := 'tab.user_data.req_id = '||l_xla_reqID||' AND tab.user_data.req_type = ''S''';
1660
1661 log(l_calling_fn,'Adding subscriber S'||to_char(l_xla_reqID));
1662 log(l_calling_fn,'l_sub_rule '||l_sub_rule);
1663
1664 IF (NOT FA_ASSET_TRACE_PUB.add_subscriber (p_qname => g_qname,
1665 p_subscriber => 'S'||to_char(l_xla_reqID),
1666 p_sub_rule => l_sub_rule)) THEN
1667 log(l_calling_fn,'Failed to add subscriber.');
1668 END IF;
1669 g_payload := GAT_message_type(l_xla_reqID, 'S', g_book, g_asset_id, l_desc);
1670 IF (NOT enqueue_request) THEN
1671 log(l_calling_fn,'Failed to save message to the queue.');
1672 END IF;
1673 log(l_calling_fn, 'Successfully submitted xla request '||l_xla_reqID);
1674 end if;
1675
1676 EXCEPTION
1677 WHEN OTHERS THEN
1678 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1679 ,p_log_level_rec => g_log_level_rec);
1680 x_retcode := 2;
1681 raise;
1682
1683 END submit_subrequest;
1684 --
1685 FUNCTION fafsc (p_col VARCHAR2) RETURN VARCHAR2 IS
1686 BEGIN
1687 RETURN FA_ASSET_TRACE_PVT.fafsc(p_col);
1688 END fafsc;
1689 --
1690 FUNCTION fparse_header(p_in_str VARCHAR2) RETURN VARCHAR2 IS
1691 BEGIN
1692 RETURN FA_ASSET_TRACE_PVT.fparse_header(p_in_str);
1693 END fparse_header;
1694 --
1695 FUNCTION enqueue_request RETURN BOOLEAN IS
1696
1697 enqueue_options DBMS_AQ.enqueue_options_t;
1698 message_properties DBMS_AQ.message_properties_t;
1699 message_handle RAW(16);
1700
1701 BEGIN
1702 DBMS_AQ.ENQUEUE(queue_name => g_qname,
1703 enqueue_options => enqueue_options,
1704 message_properties => message_properties,
1705 payload => g_payload,
1706 msgid => message_handle);
1707 COMMIT;
1708 RETURN TRUE;
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 RETURN FALSE;
1712
1713 END enqueue_request;
1714 --
1715 FUNCTION dequeue_request (p_request IN VARCHAR2,
1716 x_desc OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1717
1718 dequeue_options DBMS_AQ.dequeue_options_t;
1719 message_properties DBMS_AQ.message_properties_t;
1720 message_handle RAW(16);
1721 l_agent sys.aq$_agent;
1722 l_req_type VARCHAR2(1) :='P';
1723
1724 no_messages exception;
1725 pragma exception_init(no_messages, -25228);
1726 no_agent exception;
1727 pragma exception_init(no_agent, -24047);
1728 no_subscriber exception;
1729 pragma exception_init(no_agent, -24035);
1730
1731 l_calling_fn varchar2(80) := 'fa_asset_trace_pkg.dequeue_request';
1732
1733 BEGIN
1734 dequeue_options.wait := DBMS_AQ.NO_WAIT;
1735 dequeue_options.consumer_name := p_request;
1736 dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
1737
1738 begin
1739 LOOP
1740 DBMS_AQ.DEQUEUE(queue_name => g_qname,
1741 dequeue_options => dequeue_options,
1742 message_properties => message_properties,
1743 payload => g_payload,
1744 msgid => message_handle);
1745 l_req_type := g_payload.req_type;
1746 x_desc := g_payload.l_desc;
1747 dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
1748 END LOOP;
1749 --remove subscriber.
1750 l_agent := sys.aq$_agent(p_request,null,null);
1751 dbms_aqadm.remove_subscriber(queue_name => g_qname, subscriber => l_agent);
1752 COMMIT;
1753 exception
1754 WHEN no_messages THEN
1755 log(l_calling_fn,'Queue is empty.');
1756 COMMIT;
1757 WHEN no_agent THEN
1758 log(l_calling_fn,'no agent '||p_request);
1759 COMMIT;
1760 WHEN no_subscriber THEN
1761 log(l_calling_fn,'No such subscriber as '||p_request);
1762 COMMIT;
1763 end;
1764
1765 -- Just in case something happened to the initialized value...
1766 if (l_req_type is null) then
1767 l_req_type :='P';
1768 end if;
1769
1770 log(l_calling_fn,'returning l_req_type is '||l_req_type);
1771 log(l_calling_fn,'returning p_desc is '||x_desc);
1772
1773 RETURN l_req_type;
1774
1775 EXCEPTION
1776 WHEN others THEN
1777 log(l_calling_fn,'Unexpected error in dequeue.');
1778 COMMIT;
1779
1780 END dequeue_request;
1781 --
1782 --Used for debugging.
1783 --
1784 PROCEDURE prt_opt_tbl (p_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl) IS
1785
1786 BEGIN
1787
1788 for i in p_options_tbl.first .. p_options_tbl.last loop
1789 log('prt_opt_tbl',p_options_tbl(i).l_tbl);
1790 log('prt_opt_tbl',p_options_tbl(i).l_schema);
1791 log('prt_opt_tbl',p_options_tbl(i).l_lcs);
1792 log('prt_opt_tbl',p_options_tbl(i).l_leading_cols);
1793 log('prt_opt_tbl',p_options_tbl(i).l_order_by);
1794 log('prt_opt_tbl',p_options_tbl(i).l_lc_header);
1795 log('prt_opt_tbl',p_options_tbl(i).l_num_cols);
1796 log('prt_opt_tbl',p_options_tbl(i).l_col_order);
1797 log('prt_opt_tbl',p_options_tbl(i).l_fullcol_list);
1798 log('prt_opt_tbl',p_options_tbl(i).l_gen_select);
1799 log('prt_opt_tbl',p_options_tbl(i).l_no_header);
1800 log('prt_opt_tbl',p_options_tbl(i).l_no_anchor);
1801 log('prt_opt_tbl',p_options_tbl(i).l_add_clause);
1802 log('prt_opt_tbl',p_options_tbl(i).l_cnt_stmt);
1803 end loop;
1804
1805 END prt_opt_tbl;
1806 --
1807 FUNCTION get_schema (p_app_short_name VARCHAR2) RETURN VARCHAR2 IS
1808
1809 l_schema varchar2(50);
1810 l_status varchar2(100);
1811 l_industry varchar2(100);
1812 schema_err exception;
1813
1814 l_calling_fn varchar2(80) := 'fa_asset_trace_pkg.get_schema';
1815
1816 BEGIN
1817
1818 -- Get schema
1819 if not (fnd_installation.get_app_info (
1820 application_short_name => p_app_short_name,
1821 status => l_status,
1822 industry => l_industry,
1823 oracle_schema => l_schema)) then
1824 raise schema_err;
1825 end if;
1826
1827 RETURN l_schema;
1828
1829 EXCEPTION
1830 WHEN schema_err THEN
1831 log(l_calling_fn, 'Error getting schema for '||p_app_short_name);
1832 raise;
1833 WHEN OTHERS THEN
1834 log(l_calling_fn, 'Unexpected Error');
1835 raise;
1836
1837 END get_schema;
1838 --
1839 PROCEDURE log(p_calling_fn IN VARCHAR2,
1840 p_msg IN VARCHAR2 default null,
1841 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1842
1843 BEGIN
1844
1845 FA_ASSET_TRACE_PVT.LOG(p_calling_fn,p_msg,g_log_level_rec);
1846
1847 END log;
1848 --
1849 END FA_ASSET_TRACE_PKG;