[Home] [Help]
PACKAGE BODY: APPS.FARX_DP
Source
1 PACKAGE BODY FARX_DP AS
2 /* $Header: farxdpb.pls 120.18 2005/11/29 13:45:16 dfred ship $ */
3
4 --
5 -- Structure to hold values of all parameters
6 --
7 type param_t is record (
8 from_bal varchar2(25),
9 to_bal varchar2(25),
10 from_acct varchar2(25),
11 to_acct varchar2(25),
12 from_cc varchar2(25),
13 to_cc varchar2(25),
14 from_maj_cat varchar2(30),
15 to_maj_cat varchar2(30),
16 from_min_cat varchar2(30),
17 to_min_cat varchar2(30),
18 cat_seg_num varchar2(30),
19 from_cat_seg_val varchar2(30),
20 to_cat_seg_val varchar2(30),
21 prop_type varchar2(25),
22 from_asset_num varchar2(25),
23 to_asset_num varchar2(25),
24 report_style varchar2(1)
25 );
26 param param_t;
27
28 mesg_name varchar2(30);
29 mesg_str varchar2(2000);
30 flex_error varchar2(30);
31 ccid_error number;
32 error_errbuf varchar2(250);
33 error_retcode number;
34
35
36 /*
37 ||
38 || Reserve Ledger Report
39 ||
40 */
41
42 /*
43 * Main Reserve Ledger RX Report Procedure
44 */
45 --
46 -- Backward compatibility version
47 --
48 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
49
50 PROCEDURE deprn_run (
51 book in varchar2,
52 period in varchar2,
53 from_bal in varchar2,
54 to_bal in varchar2,
55 from_acct in varchar2,
56 to_acct in varchar2,
57 from_cc in varchar2,
58 to_cc in varchar2,
59 major_category in varchar2,
60 minor_category in varchar2,
61 cat_seg_num in varchar2,
62 cat_seg_val in varchar2,
63 prop_type in varchar2,
64 request_id in number,
65 login_id in number,
66 retcode out nocopy number,
67 errbuf out nocopy varchar2
68 ) is
69
70 l_to_major_category varchar2(30);
71 l_to_minor_category varchar2(30);
72 l_to_cat_seg_val varchar2(30);
73
74 begin
75
76 -- Fix for Bug #2709865. Do not re-use the same variable names
77 l_to_major_category := major_category;
78 l_to_minor_category := minor_category;
79 l_to_cat_seg_val := cat_seg_val;
80
81 deprn_run(
82 book,
83 period,
84 from_bal,
85 to_bal,
86 from_acct,
87 to_acct,
88 from_cc,
89 to_cc,
90 major_category,
91 l_to_major_category,
92 minor_category,
93 l_to_minor_category,
94 cat_seg_num,
95 cat_seg_val,
96 l_to_cat_seg_val,
97 prop_type,
98 null, null, -- from/to asset number
99 'S', -- For Standard Report
100 request_id,
101 login_id,
102 retcode,
103 errbuf);
104 end deprn_run; /* Backward compatible version */
105
106 --
107 -- Main version
108 --
109 procedure deprn_run (
110 book in varchar2,
111 period in varchar2,
112 from_bal in varchar2,
113 to_bal in varchar2,
114 from_acct in varchar2,
115 to_acct in varchar2,
116 from_cc in varchar2,
117 to_cc in varchar2,
118 from_maj_cat in varchar2,
119 to_maj_cat in varchar2,
120 from_min_cat in varchar2,
121 to_min_cat in varchar2,
122 cat_seg_num in varchar2,
123 from_cat_seg_val in varchar2,
124 to_cat_seg_val in varchar2,
125 prop_type in varchar2,
126 from_asset_num in varchar2,
127 to_asset_num in varchar2,
128 report_style in varchar2,
129 request_id in number,
130 login_id in number,
131 retcode out nocopy number,
132 errbuf out nocopy varchar2
133 )
134 is
135 BEGIN
136 IF (g_print_debug) THEN
137 fa_rx_util_pkg.debug('farx_dp.deprn_run()+');
138 fa_rx_util_pkg.debug('deprn_run: ' || '********login_id:' || login_id);
139 fa_rx_util_pkg.debug('deprn_run: ' || '********request_id:' || request_id);
140 END IF;
141 --
142 -- Assign parameters to global variable
143 -- These values will be used within the before_report trigger
144 --
145 param.from_bal := from_bal;
146 param.to_bal := to_bal;
147 param.from_acct:= from_acct;
148 param.to_acct := to_acct;
149 param.from_cc := from_cc;
150 param.to_cc := to_cc;
151 param.from_maj_cat := from_maj_cat;
152 param.to_maj_cat := to_maj_cat;
153 param.from_min_cat := from_min_cat;
154 param.to_min_cat := to_min_cat;
155 param.cat_seg_num := cat_seg_num;
156 param.from_cat_seg_val := from_cat_seg_val;
157 param.to_cat_seg_val := to_cat_seg_val;
158 param.prop_type := prop_type;
159 param.from_asset_num := from_asset_num;
160 param.to_asset_num := to_asset_num;
161 param.report_style := nvl(report_style,'S');
162
163 var.book := book;
164 var.period := period;
165 var.report_style := nvl(report_style,'S');
166
167 fnd_profile.get('USER_ID',farx_dp.var.user_id);
168
169 farx_dp.var.login_id := login_id;
170
171 IF (g_print_debug) THEN
172 fa_rx_util_pkg.debug('deprn_run: ' || 'Book = '||var.book);
173 fa_rx_util_pkg.debug('deprn_run: ' || 'Period = '||var.period);
174 fa_rx_util_pkg.debug('deprn_run: ' || 'Report_Style = '||var.report_style);
175 END IF;
176
177 --
178 -- Initialize request
179 --
180 fa_rx_util_pkg.init_request('farx_dp.deprn_rep', request_id, 'FA_DEPRN_REP_ITF');
181
182 --
183 -- Assign report triggers for this report.
184 --
185 fa_rx_util_pkg.assign_report('RESERVE LEDGER',
186 true,
187 'farx_dp.before_report;',
188 'farx_dp.bind(:CURSOR_SELECT);',
189 'farx_dp.after_fetch;',
190 null);
191
192 --
193 -- Run the report
194 --
195 fa_rx_util_pkg.run_report('farx_dp.deprn_rep', retcode, errbuf);
196
197 IF (g_print_debug) THEN
198 fa_rx_util_pkg.debug('farx_dp.deprn_run()-');
199 END IF;
200 exception
201 when others then
202 fa_rx_util_pkg.log(sqlcode);
203 fa_rx_util_pkg.log(sqlerrm);
204
205 fnd_message.set_name('OFA', mesg_name);
206 if mesg_name in ('FA_SHARED_DELETE_FAILED', 'FA_SHARED_INSERT_FAILED') then
207 fnd_message.set_token('TABLE', 'FA_DEPRN_REP_ITF', FALSE);
208 elsif mesg_name = 'FA_RX_CONCAT_SEGS' then
209 fnd_message.set_token('CCID', to_char(ccid_error), FALSE);
210 fnd_message.set_token('FLEX_CODE', flex_error, FALSE);
211 end if;
212
213 mesg_str := fnd_message.get;
214 IF (g_print_debug) THEN
215 fa_rx_util_pkg.debug('deprn_run: ' || mesg_name);
216 END IF;
217 fa_rx_util_pkg.log(mesg_str);
218
219 if error_errbuf is not null then
220 retcode := error_retcode;
221 errbuf := error_errbuf;
222 else
223 retcode := 2;
224 errbuf := mesg_str;
225 end if;
226
227 IF (g_print_debug) THEN
228 fa_rx_util_pkg.debug('farx_dp.deprn_run(EXCEPTION)-');
229 END IF;
230 end deprn_run;
231
232
233 /*
234 * This is the before report trigger
235 * for the main Reserve Ledger Report.
236 */
237 procedure before_report
238 is
239 return_status boolean;
240 period_closed varchar2(3);
241 l_param_where varchar2(2000);
242 maj_select_statement varchar2(50);
243 min_select_statement varchar2(50);
244 spec_select_statement varchar2(50);
245
246 -- Bug3499862
247 calendar_period_open_date date;
248 calendar_period_close_date date;
249
250 begin
251 fa_rx_util_pkg.debug('farx_dp.before_report()+');
252
253 mesg_name := 'FA_SHARED_NO_FLEX_CHART_ACCTID';
254
255 select category_flex_structure, location_flex_structure,
256 asset_key_flex_structure
257 into var.cat_flex_struct, var.loc_flex_struct, var.assetkey_flex_struct
258 from fa_system_controls;
259
260 mesg_name := 'FA_RX_SEGNUMS';
261
262
263 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
264 BOOK => var.book,
265 BALANCING_SEGNUM => var.bal_segnum,
266 ACCOUNT_SEGNUM => var.acct_segnum,
267 CC_SEGNUM => var.cc_segnum,
268 CALLING_FN => 'DEPRN_REP');
269
270 mesg_name := 'FA_AMT_GET_ASSET_NUM';
271
272 select
273 bc.book_class,
274 bc.accounting_flex_structure,
275 bc.distribution_source_book,
276 substrb(sob.currency_code, 1, 15),
277 cur.precision,
278 bc.fiscal_year_name,
279 sob.chart_of_accounts_id,
280 substrb(sob.name, 1, 80),
281 sob.set_of_books_id
282 into var.book_class,
283 var.acct_flex_struct,
284 var.dist_source_book,
285 var.currency_code,
286 var.precision,
287 var.fy_name,
288 var.chart_of_accounts_id,
289 var.organization_name,
290 var.set_of_books_id
291 from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
292 WHERE bc.book_type_code = var.book
293 AND sob.set_of_books_id = bc.set_of_books_id
294 AND sob.currency_code = cur.currency_code;
295
296
297 /* StatReq - The following statement has been added to get the natural account segment's valueset */
298
299 return_status := FND_FLEX_APIS.GET_SEGMENT_INFO
300 (101, 'GL#', var.Acct_Flex_Struct, var.Acct_Segnum,
301 var.Acct_Appl_Col, var.Acct_Segname, var.Acct_Prompt, var.Acct_Valueset_Name);
302
303 mesg_name := 'FA_AMT_SEL_PERIODS';
304
305 select period_counter, period_open_date,
306 nvl(period_close_date, sysdate),
307 decode(period_close_date, null, 'NO','YES'),
308 fiscal_year,
309 trunc(calendar_period_open_date), -- Bug3499862
310 trunc(calendar_period_close_date) -- Bug3499862
311 into var.period_counter, var.period_open_date, var.period_close_date,
312 period_closed, var.period_fy,
313 var.calendar_period_open_date,var.calendar_period_close_date -- Bug3499862
314 from fa_deprn_periods
315 where book_type_code = var.book
316 and period_name = var.period;
317
318 mesg_name := 'FA_RX_RESERVE_LEDGER';
319
320 fa_rx_util_pkg.debug('********book:' || var.book);
321 fa_rx_util_pkg.debug('******period:' || var.period);
322 fa_rx_util_pkg.debug('period_close:' || period_closed);
323
324 /* Removed check of period_closed status on 24th Nov 2000
325 to populate assets information of current open period. */
326
327 -- if period_closed = 'YES' then
328 fa_rx_shared_pkg.fa_rsvldg (
329 book => var.book,
330 period => var.period,
331 report_style => var.report_style,
332 errbuf => error_errbuf,
333 retcode => error_retcode);
334 -- end if;
335
336 --
337 -- Figure out the where clause for the parameters
338 --
339 l_param_where := null;
340
341 -- BALANCING --
342 if param.from_bal is not null and param.to_bal is not null then
343
344 l_param_where := l_param_where || ' AND (' ||
345 fa_rx_flex_pkg.flex_sql(101,'GL#', var.chart_of_accounts_id,'CC',
346 'SELECT', 'GL_BALANCING')||' between :from_bal and :to_bal)';
347
348 end if;
349
350 --
351
352 -- ACCOUNT --
353 if param.from_acct is not null and param.to_acct is not null then
354 l_param_where := l_param_where || ' AND (' ||
355 fa_rx_flex_pkg.flex_sql(101,'GL#', var.chart_of_accounts_id,'CC',
356 'SELECT', 'GL_ACCOUNT') || ' between :from_acct and :to_acct)';
357
358 end if;
359
360 -- COST CENTER --
361 if param.from_cc is not null and param.to_cc is not null then
362 l_param_where := l_param_where || ' AND (' ||
363 fa_rx_flex_pkg.flex_sql(101,'GL#', var.chart_of_accounts_id,'CC',
364 'SELECT', 'FA_COST_CTR') ||' between :from_cc and :to_cc)';
365 end if;
366
367 -- Major Category --
368 if param.from_maj_cat is not null and param.to_maj_cat is not null then
369 l_param_where := l_param_where || ' AND (' ||
370 fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT',
371 'SELECT', 'BASED_CATEGORY') ||' between :from_maj_cat and :to_maj_cat)';
372
373 end if;
374
375 -- Minor Category --
376 begin
377 if param.from_min_cat is not null and param.to_min_cat is not null then
378 l_param_where := l_param_where || ' AND (' ||
379 fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT',
380 'SELECT', 'MINOR_CATEGORY') ||' between :from_min_cat and :to_min_cat)';
381
382 elsif param.from_min_cat is not null and param.to_min_cat is null then
383 l_param_where := l_param_where || ' AND (' ||
384 fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT',
385 'SELECT', 'MINOR_CATEGORY') ||'>= :from_min_cat)';
386 elsif param.from_min_cat is null and param.to_min_cat is not null then
387 l_param_where := l_param_where || ' AND (' ||
388 fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT',
389 'SELECT', 'MINOR_CATEGORY') ||'<= :to_min_cat )';
390 end if;
391 exception
392 when others then
393 l_param_where := l_param_where || ' AND (:from_min_cat is NULL and :from_min_cat is NULL'||
394 ' and :to_min_cat is NULL and :to_min_cat is NULL)';
395 end;
396
397
398 -- Property Type --
399 if param.prop_type is not null then
400 l_param_where := l_param_where || ' AND (CAT.PROPERTY_TYPE_CODE = :prop_type) ';
401 end if;
402 -- Asset Number --
403 if param.from_asset_num is not null and param.to_asset_num is not null then
404 l_param_where := l_param_where || ' AND (AD.ASSET_NUMBER between :from_asset_num and :to_asset_num)';
405 elsif param.from_asset_num is not null and param.to_asset_num is null then
406
407 l_param_where := l_param_where || ' AND (AD.ASSET_NUMBER >= :from_asset_num)';
408 end if;
409
410 -- Category Segment Number --
411 IF (param.cat_seg_num IS NOT NULL) THEN
412 var.cat_seg_num := param.cat_seg_num;
413 if param.from_cat_seg_val is not null and param.to_cat_seg_val is not null then
414 l_param_where := l_param_where || ' AND (' ||
415 fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT',
416 'SELECT',param.cat_seg_num) ||' between from_cat_seg_val and :to_cat_seg_val)';
417
418 end if;
419 END IF;
420
421 --
422 -- Get Columns for Major_category, Minor_category and Specified_category
423 --
424
425 maj_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT','SELECT', 'BASED_CATEGORY');
426
427 begin
428 min_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT','SELECT', 'MINOR_CATEGORY');
429 exception
430 when others then
431 min_select_statement := 'null';
432 var.minor_category := null;
436 if param.cat_seg_num <> '' then
433 end;
434
435 begin
437 spec_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', var.cat_flex_struct,'CAT','SELECT', param.cat_seg_num);
438 else
439 spec_select_statement := 'null';
440 var.specified_cat_seg := null;
441 end if;
442 exception
443 when others then
444 spec_select_statement := 'null';
445 var.specified_cat_seg := null;
446 end;
447
448
449 --
450 -- Assign SELECT list
451 --
452 -->>SELECT_START<<--
453 fa_rx_util_pkg.assign_column('1','cc.code_combination_id', null,'farx_dp.var.ccid','NUMBER');
454 fa_rx_util_pkg.assign_column('2','fy.fiscal_year', null,'farx_dp.var.fy','NUMBER');
455 fa_rx_util_pkg.assign_column('3','cb.asset_cost_acct', 'asset_cost_acct','farx_dp.var.asset_cost_acct','VARCHAR2', 25);
456 fa_rx_util_pkg.assign_column('4','rsv.deprn_reserve_acct', 'accum_deprn_acct','farx_dp.var.deprn_rsv_acct','VARCHAR2', 25);
457 fa_rx_util_pkg.assign_column('5','ad.asset_number', 'asset_number','farx_dp.var.asset_number','VARCHAR2', 15);
458 fa_rx_util_pkg.assign_column('6','ad.description', 'description','farx_dp.var.description','VARCHAR2', 80);
459 fa_rx_util_pkg.assign_column('7','ad.tag_number', 'tag_number','farx_dp.var.tag_number','VARCHAR2', 15);
460 fa_rx_util_pkg.assign_column('8','ad.serial_number', 'serial_number','farx_dp.var.serial_number','VARCHAR2', 35);
461 fa_rx_util_pkg.assign_column('9','ad.inventorial', 'inventorial','farx_dp.var.inventorial','VARCHAR2', 3);
462 fa_rx_util_pkg.assign_column('10','rsv.date_placed_in_service','date_placed_in_service','farx_dp.var.date_placed_in_service','DATE');
463 fa_rx_util_pkg.assign_column('11','rsv.method_code', 'deprn_method','farx_dp.var.method_code', 'VARCHAR2', 15);
464 fa_rx_util_pkg.assign_column('12','rsv.life', null,'farx_dp.var.life','NUMBER');
465 fa_rx_util_pkg.assign_column('13','rsv.rate', 'adjusted_rate','farx_dp.var.rate','NUMBER');
466 fa_rx_util_pkg.assign_column('14','ds.bonus_rate', null,'farx_dp.var.bonus_rate','NUMBER');
467 fa_rx_util_pkg.assign_column('15','rsv.capacity', null,'farx_dp.var.capacity','NUMBER');
468 fa_rx_util_pkg.assign_column('16','rsv.cost', 'cost','farx_dp.var.cost','NUMBER');
469 fa_rx_util_pkg.assign_column('17','rsv.deprn_amount', 'deprn_amount','farx_dp.var.deprn_amount','NUMBER');
470 fa_rx_util_pkg.assign_column('18','rsv.ytd_deprn', 'ytd_deprn','farx_dp.var.ytd_deprn','NUMBER');
471 fa_rx_util_pkg.assign_column('19','rsv.deprn_reserve', 'ltd_deprn','farx_dp.var.reserve','NUMBER');
472 fa_rx_util_pkg.assign_column('20','nvl(dh.units_assigned,0)/nvl(ah.units,1)*100','percent','farx_dp.var.percent', 'NUMBER');
473 fa_rx_util_pkg.assign_column('21','rsv.transaction_type', null,'farx_dp.var.transaction_type','VARCHAR2', 1);
474 fa_rx_util_pkg.assign_column('22','dh.location_id', null,'farx_dp.var.location_id','NUMBER');
475 fa_rx_util_pkg.assign_column('23','ah.category_id', null,'farx_dp.var.category_id','NUMBER');
476 fa_rx_util_pkg.assign_column('24','ad.asset_key_ccid', null,'farx_dp.var.asset_key_ccid','NUMBER');
477 fa_rx_util_pkg.assign_column('25', null, 'life_yr_mo','farx_dp.var.life_yr_mo','NUMBER');
478 fa_rx_util_pkg.assign_column('26',null, 'nbv','farx_dp.var.nbv','NUMBER');
479 fa_rx_util_pkg.assign_column('27',null, 'period_name','farx_dp.var.period','VARCHAR2', 15);
480 fa_rx_util_pkg.assign_column('28',null, 'deprn_expense_acct','farx_dp.var.acct_all_segs(farx_dp.var.acct_segnum)','VARCHAR2', 25);
481 fa_rx_util_pkg.assign_column('29',null, 'account_description','farx_dp.var.account_description', 'VARCHAR2', 240);
482 fa_rx_util_pkg.assign_column('30',null, 'company','farx_dp.var.acct_all_segs(farx_dp.var.bal_segnum)','VARCHAR2', 25);
483 fa_rx_util_pkg.assign_column('31',null, 'cost_center','farx_dp.var.acct_all_segs(farx_dp.var.cc_segnum)','VARCHAR2', 25);
484 fa_rx_util_pkg.assign_column('32',null, 'book_type_code','farx_dp.var.book','VARCHAR2', 15);
485 fa_rx_util_pkg.assign_column('33',null, 'category','farx_dp.var.concat_cat_str','VARCHAR2', 500);
486 fa_rx_util_pkg.assign_column('34',null, 'location','farx_dp.var.concat_loc_str','VARCHAR2', 500);
487 fa_rx_util_pkg.assign_column('35',null, 'asset_key','farx_dp.var.concat_key_str','VARCHAR2', 240);
488 fa_rx_util_pkg.assign_column('36','cat.description', 'category_description', 'farx_dp.var.category_description','VARCHAR2',240);
489 fa_rx_util_pkg.assign_column('37','substrb(emp.full_name, 1,50)', 'employee_name','farx_dp.var.emp_name','VARCHAR2',50);
490 fa_rx_util_pkg.assign_column('38','substrb(emp.employee_number, 1, 15)', 'employee_number','farx_dp.var.emp_number','VARCHAR2',15);
491 fa_rx_util_pkg.assign_column('39','dh.units_assigned', 'units','farx_dp.var.units','NUMBER');
492 fa_rx_util_pkg.assign_column('40',null, 'company_description',
493 'farx_dp.var.company_description','VARCHAR2',240);
494 fa_rx_util_pkg.assign_column('41',null, 'expense_acct_description',
495 'farx_dp.var.expense_acct_description','VARCHAR2',240);
496 fa_rx_util_pkg.assign_column('42',null, 'cost_center_description',
497 'farx_dp.var.cost_center_description','VARCHAR2',240);
498 fa_rx_util_pkg.assign_column('43',null, 'organization_name','farx_dp.var.organization_name','VARCHAR2',80);
499 fa_rx_util_pkg.assign_column('44',null, 'functional_currency_code','farx_dp.var.currency_code','VARCHAR2',15);
500 fa_rx_util_pkg.assign_column('45',null, 'nbv_beginning_fy','farx_dp.var.nbv_beginning_fy','NUMBER');
501 fa_rx_util_pkg.assign_column('46',null, 'set_of_books_id','farx_dp.var.set_of_books_id','NUMBER');
502 fa_rx_util_pkg.assign_column('47',maj_select_statement, 'major_category','farx_dp.var.major_category','VARCHAR2',240);
506 fa_rx_util_pkg.assign_column('51',spec_select_statement, 'specified_category_segment','farx_dp.var.specified_cat_seg','VARCHAR2',240);
503 fa_rx_util_pkg.assign_column('48',min_select_statement, 'minor_category','farx_dp.var.minor_category','VARCHAR2',240);
504 fa_rx_util_pkg.assign_column('49',null, 'major_category_description','farx_dp.var.major_category_desc','VARCHAR2',240);
505 fa_rx_util_pkg.assign_column('50',null, 'minor_category_description','farx_dp.var.minor_category_desc','VARCHAR2',240);
507 fa_rx_util_pkg.assign_column('52',null, 'specified_category_seg_desc','farx_dp.var.specified_cat_seg_desc','VARCHAR2',240);
508 fa_rx_util_pkg.assign_column('53',null, 'reserve_acct_desc','farx_dp.var.reserve_acct_desc','VARCHAR2',240);
509 fa_rx_util_pkg.assign_column('54','decode(ad.asset_type,''GROUP'',ad.asset_number,nvl(ad1.asset_number,lu.meaning))','group_asset_number','farx_dp.var.group_asset_number','VARCHAR2',15);
510 fa_rx_util_pkg.assign_column('55','books.salvage_value', 'salvage_value','farx_dp.var.salvage_value','NUMBER');
511 -->>SELECT_END<<--
512
513
514 --
515 -- Assign From Clause
516 --
517 fa_rx_util_pkg.From_Clause := 'fa_reserve_ledger_gt rsv,
518 fa_distribution_history dh,
519 fa_additions ad,
520 fa_additions ad1,
521 fa_asset_history ah,
522 fa_fiscal_year fy,
523 fa_category_books cb,
524 gl_code_combinations cc,
525 fa_deprn_summary ds,
526 fa_books books,
527 fa_categories cat,
528 fa_category_book_defaults cbd,
529 per_all_people_f emp,
530 fa_deprn_detail dd,
531 fa_lookups lu';
532
533 --
534 -- Assign Where Clause
535 --
536 fa_rx_util_pkg.Where_Clause := '
537 rsv.asset_id = ad.asset_id
538 and rsv.asset_id = dh.asset_id
539 and rsv.dh_ccid = dh.code_combination_id
540 and rsv.distribution_id = dh.distribution_id
541 and dh.date_effective < rsv.date_effective and
542 nvl(dh.date_ineffective,sysdate) >= rsv.date_effective
543 and rsv.dh_ccid = cc.code_combination_id
544 and cb.book_type_code = :b_book and
545 cb.category_id = ah.category_id
546 and ah.asset_id = ad.asset_id and
547 ah.date_effective < rsv.date_effective and
548 nvl(ah.date_ineffective,sysdate) >= rsv.date_effective
549 and ad1.asset_id (+) = books.group_asset_id -- added for drill down report
550 and lu.lookup_code (+) = ad.asset_type and
551 lu.lookup_type (+) = ''ASSET TYPE''
552 and ds.period_counter (+) = rsv.period_counter and
553 ds.book_type_code (+) = :b_book and
554 ds.asset_id (+) = rsv.asset_id
555 and fy.fiscal_year_name = :b_fy_name and
556 rsv.date_placed_in_service between fy.start_date and fy.end_date
557 and rsv.date_placed_in_service <= :b_period_close_date -- Added for Bug#3499862
558 and books.book_type_code = :b_book
559 and books.asset_id = rsv.asset_id
560 and books.date_effective < rsv.date_effective and
561 nvl(books.date_ineffective,sysdate) >= rsv.date_effective
562 and cat.category_id = ah.category_id
563 and cbd.category_id = ah.category_id
564 and cbd.book_type_code = :b_book
565 and rsv.date_placed_in_service between
566 cbd.start_dpis and nvl(cbd.end_dpis,rsv.date_placed_in_service)
567 and emp.person_id(+) = dh.assigned_to
568 and trunc(sysdate) between
569 effective_start_date(+) and effective_end_date(+)
570 and dd.book_type_code = :b_book
571 and dd.asset_id = rsv.asset_id
572 and dd.distribution_id = dh.distribution_id
573 and (books.group_asset_id is null
574 or (
575 books.group_asset_id is not null
576 and exists (select 1
577 from fa_books oldbk
578 , fa_transaction_headers oldth
579 , fa_deprn_periods dp
580 where oldbk.transaction_header_id_out = books.transaction_header_id_in
581 and oldbk.transaction_header_id_out = oldth.transaction_header_id
582 and dp.book_type_code = :b_book
583 and dp.period_counter = dd.period_counter
584 and oldth.date_effective between dp.period_open_date
585 and nvl(dp.period_close_date, oldth.date_effective)
586 and oldbk.group_asset_id is null)
587 )
588 or (nvl(:b_report_style,''S'') = ''D'')
589 )
590 and dd.period_counter = rsv.period_counter ' || l_param_where ;
591
592 mesg_name := 'FA_DEPRN_SQL_DCUR';
593 fa_rx_util_pkg.debug('farx_dp.before_report()-');
594 end before_report;
595
596
597 /*
598 * This is the bind trigger
599 * for the main Reserve Ledger Report.
600 */
601 procedure bind(c in integer)
602 is
603 BEGIN
604 --
605 -- These bind variables were included in the WHERE clause.
606 --
607
608 IF (g_print_debug) THEN
609 fa_rx_util_pkg.debug('farx_dp.bind()+');
610 END IF;
611 dbms_sql.bind_variable(c, 'b_book', var.book);
612 dbms_sql.bind_variable(c, 'b_fy_name', var.fy_name);
616 dbms_sql.bind_variable(c, 'to_bal', param.to_bal);
613 dbms_sql.bind_variable(c, 'b_report_style', var.report_style);
614 if (param.from_bal is not null and param.to_bal is not null) then
615 dbms_sql.bind_variable(c, 'from_bal', param.from_bal);
617 end if;
618 if param.from_acct is not null and param.to_acct is not null then
619 dbms_sql.bind_variable(c, 'from_acct', param.from_acct);
620 dbms_sql.bind_variable(c, 'to_acct', param.to_acct);
621 end if;
622 if param.from_cc is not null and param.to_cc is not null then
623 dbms_sql.bind_variable(c, 'from_cc', param.from_cc);
624 dbms_sql.bind_variable(c, 'to_cc', param.to_cc);
625 end if;
626 if param.from_maj_cat is not null and param.to_maj_cat is not null then
627 dbms_sql.bind_variable(c, 'from_maj_cat', param.from_maj_cat);
628 dbms_sql.bind_variable(c, 'to_maj_cat', param.to_maj_cat);
629 end if;
630 if param.from_min_cat is not null then
631 dbms_sql.bind_variable(c, 'from_min_cat', param.from_min_cat);
632 end if;
633 if param.to_min_cat is not null then
634 dbms_sql.bind_variable(c, 'to_min_cat', param.to_min_cat);
635 end if;
636 if param.prop_type is not null then
637 dbms_sql.bind_variable(c, 'prop_type', param.prop_type);
638 end if;
639 if param.from_asset_num is not null and param.to_asset_num is not null then
640 dbms_sql.bind_variable(c, 'from_asset_num', param.from_asset_num);
641 dbms_sql.bind_variable(c, 'to_asset_num', param.to_asset_num);
642 elsif param.from_asset_num is not null and param.to_asset_num is null then
643 dbms_sql.bind_variable(c, 'from_asset_num', param.from_asset_num);
644 end if;
645
646 dbms_sql.bind_variable(c, 'b_period_close_date', var.calendar_period_close_date); -- Added for Bug#3499862.
647
648 IF (param.cat_seg_num IS NOT NULL) THEN
649 dbms_sql.bind_variable(c, 'from_cat_seg_val', param.from_cat_seg_val);
650 dbms_sql.bind_variable(c, 'to_cat_seg_val', param.to_cat_seg_val);
651 END IF;
652 IF (g_print_debug) THEN
653 fa_rx_util_pkg.debug('farx_dp.bind()-');
654 END IF;
655 end bind;
656
657
658 /*
659 * This is the after fetch trigger
660 * for the main Reserve Ledger Report.
661 */
662 procedure after_fetch
663 is
664 begin
665 fa_rx_util_pkg.debug('farx_dp.after_fetch()+');
666 var.account_description := fa_rx_shared_pkg.get_flex_val_meaning(
667 NULL, var.acct_valueset_name, var.asset_cost_acct);
668
669 mesg_name := 'FA_RX_CONCAT_SEGS';
670 flex_error := 'GL#';
671 ccid_error := var.ccid;
672
673 fa_rx_shared_pkg.concat_acct (
674 struct_id => var.acct_flex_struct,
675 ccid => var.ccid,
676 concat_string => var.concat_acct_str,
677 segarray => var.acct_all_segs);
678
679 flex_error := 'CAT#';
680 ccid_error := var.category_id;
681
682 fa_rx_shared_pkg.concat_category (
683 struct_id => var.cat_flex_struct,
684 ccid => var.category_id,
685 concat_string => var.concat_cat_str,
686 segarray => var.cat_segs);
687
688 flex_error := 'LOC#';
689 ccid_error := var.location_id;
690
691 fa_rx_shared_pkg.concat_location (
692 struct_id => var.loc_flex_struct,
693 ccid => var.location_id,
694 concat_string => var.concat_loc_str,
695 segarray => var.loc_segs);
696
697 /* StatReq - The following three statement have been added to get the
698 concatenated asset key flexfield value */
699
700 if (var.asset_key_ccid is not NULL)
701 then
702 DECLARE
703 buf VARCHAR2(500);
704 BEGIN
705 flex_error := 'KEY#';
706 ccid_error := var.asset_key_ccid;
707
708 fa_rx_shared_pkg.concat_asset_key (
709 struct_id => var.assetkey_flex_struct,
710 ccid => var.asset_key_ccid,
711 concat_string => buf,
712 segarray => var.key_segs);
713 var.concat_key_str := substrb(buf, 1,240);
714 END;
715 end if;
716
717 var.company_description :=
718 fa_rx_flex_pkg.get_description(
719 p_application_id => 101,
720 p_id_flex_code => 'GL#',
721 p_id_flex_num => var.chart_of_accounts_id,
722 p_qualifier => 'GL_BALANCING',
723 p_data => var.acct_all_segs(var.bal_segnum));
724
725 var.expense_acct_description :=
726 fa_rx_flex_pkg.get_description(
727 p_application_id => 101,
728 p_id_flex_code => 'GL#',
729 p_id_flex_num => var.chart_of_accounts_id,
730 p_qualifier => 'GL_ACCOUNT',
731 p_data => var.acct_all_segs(var.acct_segnum));
732
733 var.reserve_acct_desc :=
734 fa_rx_flex_pkg.get_description(
735 p_application_id => 101,
736 p_id_flex_code => 'GL#',
737 p_id_flex_num => var.chart_of_accounts_id,
738 p_qualifier => 'GL_ACCOUNT',
739 p_data => var.deprn_rsv_acct);
740
741 var.cost_center_description :=
742 fa_rx_flex_pkg.get_description(
743 p_application_id => 101,
744 p_id_flex_code => 'GL#',
748
745 p_id_flex_num => var.chart_of_accounts_id,
746 p_qualifier => 'FA_COST_CTR',
747 p_data => var.acct_all_segs(var.cc_segnum));
749 begin
750 var.major_category_desc :=
751 fa_rx_flex_pkg.get_description(
752 p_application_id => 140,
753 p_id_flex_code => 'CAT#',
754 p_id_flex_num => var.cat_flex_struct,
755 p_qualifier => 'BASED_CATEGORY',
756 p_data => var.major_category);
757 exception
758 when others then
759 var.major_category_desc := null;
760 end;
761
762 begin
763 var.minor_category_desc :=
764 fa_rx_flex_pkg.get_description(
765 p_application_id => 140,
766 p_id_flex_code => 'CAT#',
767 p_id_flex_num => var.cat_flex_struct,
768 p_qualifier => 'MINOR_CATEGORY',
769 p_data => var.minor_category);
770 exception
771 when others then
772 var.minor_category_desc := null;
773 end;
774
775 begin
776 var.specified_cat_seg_desc :=
777 fa_rx_flex_pkg.get_description(
778 p_application_id => 140,
779 p_id_flex_code => 'CAT#',
780 p_id_flex_num => var.cat_flex_struct,
781 p_qualifier => param.cat_seg_num,
782 p_data => var.specified_cat_seg);
783 exception
784 when others then
785 var.specified_cat_seg_desc := null;
786 end;
787
788 var.nbv := var.cost - var.reserve;
789 var.nbv_beginning_fy := var.nbv + var.ytd_deprn;
790
791 IF (var.life IS NULL) THEN
792 var.life_yr_mo := NULL;
793 ELSE
794 var.life_yr_mo :=
795 fnd_number.canonical_to_number(
796 to_char(floor(var.life/12))||'.'||to_char(mod(var.life,12), 'FM00'));
797 END IF;
798 mesg_name := 'FA_SHARED_INSERT_FAILED';
799 fa_rx_util_pkg.debug('farx_dp.after_fetch()-');
800 end after_fetch;
801 END FARX_DP;