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