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