[Home] [Help]
PACKAGE BODY: APPS.JGRX_FAREG
Source
1 PACKAGE BODY JGRX_FAREG AS
2 /* $Header: jgrxfrb.pls 115.13 2001/11/30 07:42:02 pkm ship $ */
3
4 -- Structure to hold values of parameters
5 -- These include parameters which are passed in the core
6
7 TYPE param_t IS RECORD (
8 p_book_type_code VARCHAR2(15),
9 p_period_from VARCHAR2(20),
10 p_period_to VARCHAR2(20),
11 p_major_category VARCHAR2(150),
12 p_minor_category VARCHAR2(150),
13 p_begin_period_from_date DATE, -- for where clause
14 p_begin_period_to_date DATE, -- for where clause
15 p_end_period_from_date DATE, -- for where clause
16 p_end_period_to_date DATE, -- for where clause
17 p_begin_period_counter NUMBER(15),
18 p_end_period_counter NUMBER(15),
19 p_fiscal_year_start_date DATE
20 );
21 parm param_t;
22 X_Account_Segment VArchar2(50); -- used to store the Account Segment Name
23 X_major_segment VARCHAR2(20); -- used to store major segment
24 X_minor_segment VARCHAR2(20); -- used to store minor segment
25 X_minor_segment_sel VARCHAR2(20); -- used to store minor segment
26 X_where_clause_tmp VARCHAR2(1000);
27 X_transaction_id_initial NUMBER; -- used to stored first transaction for asset
28 X_transaction_id_final NUMBER; -- used to stored last transaction for asset
29 X_fiscal_year NUMBER(4);
30 X_fiscal_year_start_date DATE;
31 X_fiscal_year_end_date DATE;
32
33 X_first_row BOOLEAN;
34 X_request_id NUMBER;
35 X_last_update_date DATE;
36 X_last_updated_by NUMBER;
37 X_last_update_login NUMBER;
38 X_creation_date DATE;
39 X_created_by NUMBER;
40 X_deprn_calendar VARCHAR2(15);
41 X_prorate_calendar VARCHAR2(15);
42 /*===================================================================+
43 | fa_get_report |
44 +====================================================================*/
45
46 PROCEDURE fa_get_report(
47 p_book_type_code IN VARCHAR2,
48 p_period_from IN VARCHAR2,
49 p_period_to IN VARCHAR2,
50 p_dummy IN NUMBER,
51 p_major_category IN VARCHAR2,
52 p_minor_category IN VARCHAR2,
53 p_type IN VARCHAR2, -- ASSET or RTRMNT
54 request_id IN NUMBER,
55 retcode OUT NUMBER,
56 errbuf OUT VARCHAR2
57 )
58 IS
59
60 X_section_name VARCHAR2(20);
61 X_calling_proc VARCHAR2(70);
62 X_before_report VARCHAR2(300);
63 X_bind VARCHAR2(300) := NULL;
64 X_after_fetch VARCHAR2(300) := NULL;
65 X_after_report VARCHAR2(300) := NULL;
66
67
68 BEGIN
69
70 -- Inizialize Variables
71 X_section_name := 'Get_' || p_type || '_Details';
72 X_calling_proc := 'JGRX_FAREG.fa_get_report';
73 X_before_report := 'JGRX_FAREG.fa_' || p_type || '_before_report'|| ';';
74 X_bind := 'JGRX_FAREG.fa_' || p_type || '_bind' || '(:CURSOR_SELECT);';
75 X_after_fetch := 'JGRX_FAREG.fa_' || p_type || '_after_fetch'|| ';';
76 X_after_report := 'JGRX_FAREG.fa_' || p_type || '_after_report'|| ';';
77
78 FA_RX_UTIL_PKG.debug(X_calling_proc || '()+');
79
80 FA_RX_UTIL_PKG.init_request(X_calling_proc, request_id);
81
82 -- Store the parameters in a variable which can be accesed globally accross all procedures
83 PARM.p_book_type_code := p_book_type_code;
84 PARM.p_period_from := p_period_from;
85 PARM.p_period_to := p_period_to;
86 PARM.p_major_category := p_major_category;
87 PARM.p_minor_category := p_minor_category;
88
89
90 X_request_id := request_id;
91
92 -- Who columns
93 X_last_update_date := SYSDATE;
94 X_last_updated_by := FND_GLOBAL.user_id;
95 X_last_update_login := FND_GLOBAL.login_id;
96 X_creation_date := SYSDATE;
97 X_created_by := FND_GLOBAL.user_id;
98
99
100 -- Call the procedure to find date of parm_period
101 Get_period_date(p_period_from, p_period_to);
102 Get_fiscal_year_date;
103 PARM.p_fiscal_year_start_date := X_fiscal_year_start_date ;
104
105 -- Call the core report.This executes the core report and the SELECT statement of the core
106 -- is built.
107 -- No data is inserted into the interface table.
108
109 FA_RX_UTIL_PKG.debug('FA_RX_UTIL_PKG.assign_report(' || X_section_name);
110 FA_RX_UTIL_PKG.debug(' TRUE');
111 FA_RX_UTIL_PKG.debug(' ' || X_before_report);
112 FA_RX_UTIL_PKG.debug(' ' || X_bind);
113 FA_RX_UTIL_PKG.debug(' ' || X_after_fetch);
114 FA_RX_UTIL_PKG.debug(' ' || X_after_report);
115 FA_RX_UTIL_PKG.debug(' )');
116
117 FA_RX_UTIL_PKG.assign_report(X_section_name,
118 TRUE,
119 X_before_report,
120 X_bind,
121 X_after_fetch,
122 X_after_report);
123
124 FA_RX_UTIL_PKG.run_report(X_calling_proc, retcode, errbuf);
125
126 FA_RX_UTIL_PKG.debug(X_calling_proc || '()-');
127
128 END fa_get_report;
129
130
131 /*****************************************************************************************************
132
133 A S S E T
134
135 *****************************************************************************************************/
136
137 /*===================================================================+
138 | fa_ASSET_before_report |
139 +====================================================================*/
140 -- This is the before report trigger for the main Report. The code which is written in the " BEFORE
141 -- REPORT " triggers has been incorporated over here. The code is the common code accross all the
142 -- reports.
143
144 PROCEDURE fa_ASSET_before_report
148 BEGIN
145 IS
146
147
149 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_before_report()+');
150
151
152 -- It takes the segment condition, Company name, currency code and store in placeholder variable
153 Startup;
154
155 -- This gets the segment name of the account flexfield qualifier which corresponds to this chart of accounts
156 X_Account_Segment := Get_Account_segment;
157
158 -- Assign SELECT list
159 -- the Select statement is build over here
160
161 -- FA_RX_UTIL_PKG.assign_column(#, select, insert, place, type, len);
162 fa_rx_util_pkg.debug(' checking if value is available'||to_char(X_fiscal_year_start_date));
163
164 -->>SELECT_START<<--
165
166 FA_RX_UTIL_PKG.assign_column('1', NULL, 'organization_name','JGRX_FAREG.var.organization_name','VARCHAR2',60);
167 FA_RX_UTIL_PKG.assign_column('2', NULL, 'functional_currency_code','JGRX_FAREG.var.functional_currency_code', 'VARCHAR2',15);
168
169 FA_RX_UTIL_PKG.assign_column('3', 'CA.' || X_major_segment, 'major_category','JGRX_FAREG.var.major_category','VARCHAR2', 30);
170 FA_RX_UTIL_PKG.assign_column('4', X_minor_segment_sel, 'minor_category','JGRX_FAREG.var.minor_category','VARCHAR2', 30);
171
172 FA_RX_UTIL_PKG.assign_column('5', NULL, 'deprn_rate','JGRX_FAREG.var.deprn_rate','NUMBER');
173
174 FA_RX_UTIL_PKG.assign_column('6', NULL,'starting_deprn_year','JGRX_FAREG.var.starting_deprn_year','VARCHAR2', 4);
175
176 /* FA_RX_UTIL_PKG.assign_column('7', 'TO_CHAR(BO.date_placed_in_service,"YYYY")','starting_deprn_year','JGRX_FAREG.var.starting_deprn_year','VARCHAR2', 4);
177 */
178 FA_RX_UTIL_PKG.assign_column('7', 'BO.date_placed_in_service','date_placed_in_service','JGRX_FAREG.var.date_placed_in_service','DATE');
179
180 FA_RX_UTIL_PKG.assign_column('8', 'BO.asset_id', NULL,'JGRX_FAREG.var.asset_id','NUMBER');
184 FA_RX_UTIL_PKG.assign_column('11', 'AD.parent_asset_id', NULL,'JGRX_FAREG.var.parent_asset_id','NUMBER');
181 FA_RX_UTIL_PKG.assign_column('9', 'AD.asset_number', 'asset_number','JGRX_FAREG.var.asset_number','VARCHAR2', 25);
182 FA_RX_UTIL_PKG.assign_column('10', 'AD.description', 'description','JGRX_FAREG.var.description','VARCHAR2', 80);
183
185 FA_RX_UTIL_PKG.assign_column('12', NULL, 'parent_asset_number','JGRX_FAREG.var.parent_asset_number','VARCHAR2', 15);
186
187 FA_RX_UTIL_PKG.assign_column('13', 'BO.original_cost', 'asset_cost_orig','JGRX_FAREG.var.asset_cost_orig','NUMBER');
188
189 FA_RX_UTIL_PKG.assign_column('14', NULL, 'bonus_rate','JGRX_FAREG.var.bonus_rate','NUMBER');
190
191 FA_RX_UTIL_PKG.assign_column('15', NULL, 'invoice_number','JGRX_FAREG.var.invoice_number','VARCHAR2', 50);
192 FA_RX_UTIL_PKG.assign_column('16', NULL, 'supplier_name','JGRX_FAREG.var.supplier_name','VARCHAR2', 80);
193
194 FA_RX_UTIL_PKG.assign_column('17', 'CB.asset_cost_acct', 'cost_account','JGRX_FAREG.var.cost_account','VARCHAR2', 25);
195
196 FA_RX_UTIL_PKG.assign_column('18', NULL, 'expense_account','JGRX_FAREG.var.expense_account','VARCHAR2', 25);
197 FA_RX_UTIL_PKG.assign_column('19', 'CB.deprn_reserve_acct', 'reserve_account','JGRX_FAREG.var.reserve_account','VARCHAR2', 25);
198
199 FA_RX_UTIL_PKG.assign_column('20', 'CB.bonus_deprn_expense_acct', 'bonus_deprn_account','JGRX_FAREG.var.bonus_deprn_account','VARCHAR2', 25);
200 FA_RX_UTIL_PKG.assign_column('21', 'CB.bonus_deprn_reserve_acct', 'bonus_reserve_account','JGRX_FAREG.var.bonus_reserve_account','VARCHAR2', 25);
201
202 FA_RX_UTIL_PKG.assign_column('22', NULL, 'asset_cost_initial','JGRX_FAREG.var.asset_cost_initial', 'NUMBER');
203 FA_RX_UTIL_PKG.assign_column('23', NULL, 'asset_cost_increase','JGRX_FAREG.var.asset_cost_increase', 'NUMBER');
204 FA_RX_UTIL_PKG.assign_column('24', NULL, 'asset_cost_decrease','JGRX_FAREG.var.asset_cost_decrease', 'NUMBER');
205 FA_RX_UTIL_PKG.assign_column('25', NULL, 'asset_cost_final','JGRX_FAREG.var.asset_cost_final', 'NUMBER');
206
207 FA_RX_UTIL_PKG.assign_column('26', NULL, 'revaluation_initial','JGRX_FAREG.var.revaluation_initial', 'NUMBER');
208 FA_RX_UTIL_PKG.assign_column('27', NULL, 'revaluation_increase','JGRX_FAREG.var.revaluation_increase', 'NUMBER');
209 FA_RX_UTIL_PKG.assign_column('28', NULL, 'revaluation_decrease','JGRX_FAREG.var.revaluation_decrease', 'NUMBER');
210 FA_RX_UTIL_PKG.assign_column('29', NULL, 'revaluation_final','JGRX_FAREG.var.revaluation_final', 'NUMBER');
211
212 FA_RX_UTIL_PKG.assign_column('30', NULL, 'deprn_reserve_initial','JGRX_FAREG.var.deprn_reserve_initial', 'NUMBER');
213 FA_RX_UTIL_PKG.assign_column('31', NULL, 'deprn_reserve_increase','JGRX_FAREG.var.deprn_reserve_increase', 'NUMBER');
214 FA_RX_UTIL_PKG.assign_column('32', NULL, 'deprn_reserve_decrease','JGRX_FAREG.var.deprn_reserve_decrease', 'NUMBER');
215 FA_RX_UTIL_PKG.assign_column('33', NULL, 'deprn_reserve_final','JGRX_FAREG.var.deprn_reserve_final', 'NUMBER');
216
217 FA_RX_UTIL_PKG.assign_column('34', NULL, 'bonus_reserve_initial','JGRX_FAREG.var.bonus_reserve_initial', 'NUMBER');
218 FA_RX_UTIL_PKG.assign_column('35', NULL, 'bonus_reserve_increase','JGRX_FAREG.var.bonus_reserve_increase', 'NUMBER');
219 FA_RX_UTIL_PKG.assign_column('36', NULL, 'bonus_reserve_decrease','JGRX_FAREG.var.bonus_reserve_decrease', 'NUMBER');
220 FA_RX_UTIL_PKG.assign_column('37', NULL, 'bonus_reserve_final','JGRX_FAREG.var.bonus_reserve_final', 'NUMBER');
221
222 FA_RX_UTIL_PKG.assign_column('38', NULL, 'net_book_value_initial','JGRX_FAREG.var.net_book_value_initial', 'NUMBER');
223 FA_RX_UTIL_PKG.assign_column('39', NULL, 'net_book_value_increase','JGRX_FAREG.var.net_book_value_increase', 'NUMBER');
224 FA_RX_UTIL_PKG.assign_column('40', NULL, 'net_book_value_decrease','JGRX_FAREG.var.net_book_value_decrease', 'NUMBER');
225 FA_RX_UTIL_PKG.assign_column('41', NULL, 'net_book_value_final','JGRX_FAREG.var.net_book_value_final', 'NUMBER');
226
227 FA_RX_UTIL_PKG.assign_column('42', NULL, 'transaction_date','JGRX_FAREG.var.transaction_date', 'DATE');
228 FA_RX_UTIL_PKG.assign_column('43', NULL, 'transaction_number','JGRX_FAREG.var.transaction_number', 'NUMBER');
229 FA_RX_UTIL_PKG.assign_column('44', NULL, 'transaction_code','JGRX_FAREG.var.transaction_code', 'VARCHAR2', 20);
230 FA_RX_UTIL_PKG.assign_column('45', NULL, 'transaction_amount','JGRX_FAREG.var.transaction_amount', 'NUMBER');
231
232 -- FA_RX_UTIL_PKG.assign_column('46', 'BO.date_placed_in_service', NULL,'JGRX_FAREG.var.date_placed_in_service','DATE');
233 FA_RX_UTIL_PKG.assign_column('47', 'BO.prorate_date', NULL,'JGRX_FAREG.var.prorate_date','DATE');
234 FA_RX_UTIL_PKG.assign_column('48', 'ME.method_id', NULL,'JGRX_FAREG.var.method_id','NUMBER');
235 FA_RX_UTIL_PKG.assign_column('49', 'ME.rate_source_rule', NULL,'JGRX_FAREG.var.rate_source_rule','VARCHAR2', 10);
236 FA_RX_UTIL_PKG.assign_column('50', 'BO.adjusted_rate', NULL,'JGRX_FAREG.var.adjusted_rate','NUMBER');
240 FA_RX_UTIL_PKG.assign_column('54', NULL,'initial_heading','JGRX_FAREG.var.initial_heading','VARCHAR2', 15); -- 09/08/00 AFERRARA
237 FA_RX_UTIL_PKG.assign_column('51', 'BO.life_in_months', NULL,'JGRX_FAREG.var.life_in_months','NUMBER');
238 FA_RX_UTIL_PKG.assign_column('52', 'BO.bonus_rule', NULL,'JGRX_FAREG.var.bonus_rule','VARCHAR2', 30);
239 FA_RX_UTIL_PKG.assign_column('53', NULL,'asset_heading','JGRX_FAREG.var.asset_heading','VARCHAR2', 15);
241 FA_RX_UTIL_PKG.assign_column('55', '''Variation''','variation_heading','JGRX_FAREG.var.variation_heading','VARCHAR2', 15); -- 09/08/00 AFERRARA
242 FA_RX_UTIL_PKG.assign_column('56', '''------------------------------------------------------------------------------------------------------------------------------------''','final_heading',
243 'JGRX_FAREG.var.final_heading','VARCHAR2', 132); -- 09/08/00 AFERRARA
244 FA_RX_UTIL_PKG.assign_column('57', NULL, 'asset_variation','JGRX_FAREG.var.asset_variation', 'NUMBER'); -- 09/08/00 AFERRARA
245 FA_RX_UTIL_PKG.assign_column('58', NULL, 'reval_variation','JGRX_FAREG.var.reval_variation', 'NUMBER'); -- 09/08/00 AFERRARA
246 FA_RX_UTIL_PKG.assign_column('59', NULL, 'deprn_variation','JGRX_FAREG.var.deprn_variation', 'NUMBER'); -- 09/08/00 AFERRARA
247 FA_RX_UTIL_PKG.assign_column('60', NULL, 'bonus_variation','JGRX_FAREG.var.bonus_variation', 'NUMBER'); -- 09/08/00 AFERRARA
248 FA_RX_UTIL_PKG.assign_column('61', NULL, 'netbo_variation','JGRX_FAREG.var.netbo_variation', 'NUMBER'); -- 09/08/00 AFERRARA
249 FA_RX_UTIL_PKG.assign_column('62', NULL, 'revaluation_total','JGRX_FAREG.var.revaluation_total', 'NUMBER'); -- 09/08/00 AFERRARA
250 /* FA_RX_UTIL_PKG.assign_column('7', 'BO.date_placed_in_service','date_placed_in_service','JGRX_FAREG.var.date_placed_in_service','DATE');
251 */
252 -->>SELECT_END<<--
253
254
255 --
256 -- Assign From Clause
257 --
258 FA_RX_UTIL_PKG.From_Clause :=
259 'fa_books BO,
260 fa_additions AD,
261 fa_categories CA,
262 fa_category_books CB,
263 fa_methods ME';
264
265
266 --
267 -- Assign Where Clause
268 --
269
270 FA_RX_UTIL_PKG.Where_clause:=
271 ' AD.asset_id = BO.asset_id' ||
272 ' AND AD.in_use_flag = ''YES''' ||
273 ' AND AD.asset_category_id = CA.category_id' ||
274 ' AND (BO.date_effective,transaction_header_id_in) = (SELECT MAX(date_effective),'||
275 ' max(transaction_header_id_in)' ||
276 ' FROM fa_books' ||
277 ' WHERE date_placed_in_service <= :b_period_to_date' ||
278 -- ' WHERE date_effective < :b_period_to_date' || // Date_effective -> Date_placed_in_service
279 ' AND book_type_code = bo.book_type_code' ||
280 ' AND asset_id = BO.asset_id)' ||
281 ' AND AD.asset_type = ''CAPITALIZED''' ||
282 ' AND ((BO.period_counter_fully_retired IS NULL)'||
283 ' OR ((BO.period_counter_fully_retired IS NOT NULL)'||
284 ' AND BO.TRANSACTION_HEADER_ID_IN ='||
285 ' ( SELECT RE.TRANSACTION_HEADER_ID_IN'||
286 ' FROM FA_RETIREMENTS RE'||
287 ' WHERE BO.ASSET_ID = RE.ASSET_ID'||
288 ' AND BO.TRANSACTION_HEADER_ID_IN = RE.TRANSACTION_HEADER_ID_IN'||
289 ' AND RE.DATE_RETIRED >=:b_fiscal_year_start_date)))'||
290 ' AND CB.category_id = CA.category_id' ||
291 ' AND CB.book_type_code = BO.book_type_code' ||
292 ' AND BO.deprn_method_code = ME.method_code' ||
293 ' AND NVL(BO.life_in_months,-99) = NVL(ME.life_in_months,-99)';
294
295
296
297 -- It takes the segment condition
298 IF X_where_clause_tmp IS NOT NULL THEN
299 FA_RX_UTIL_PKG.Where_Clause := FA_RX_UTIL_PKG.Where_clause || X_where_clause_tmp;
300 END IF;
301
302 IF PARM.p_book_type_code IS NOT NULL THEN
303 FA_RX_UTIL_PKG.Where_clause := FA_RX_UTIL_PKG.Where_clause ||
304 ' AND BO.book_type_code = :b_book_type_code ';
305 END IF;
306
307 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_before_report()-');
308
309 END fa_ASSET_before_report;
310
311
312
313 /*===================================================================+
314 | fa_ASSET_bind |
315 +====================================================================*/
316 -- This is the bind trigger for the Assets
317 PROCEDURE fa_ASSET_bind (c IN INTEGER)
318 IS
319 b_major_category VARCHAR2(20);
320 b_minor_category VARCHAR2(20);
321 b_period_from_date VARCHAR2(20);
322 b_period_to_date VARCHAR2(20);
323 b_book_type_code VARCHAR2(15);
324 b_fiscal_year_start_date DATE;
325
326 BEGIN
327 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_bind()+');
328
329 IF PARM.p_end_period_to_date IS NOT NULL THEN
330 FA_RX_UTIL_PKG.debug('Binding b_period_to_date');
331 DBMS_SQL.bind_variable(c, 'b_period_to_date', PARM.p_end_period_to_date);
332 END IF;
333
334 IF PARM.p_major_category IS NOT NULL THEN
335 FA_RX_UTIL_PKG.debug('Binding major_category');
336 DBMS_SQL.bind_variable(c, 'b_major_category', PARM.p_major_category);
337 END IF;
338
339 IF PARM.p_minor_category IS NOT NULL THEN
340 FA_RX_UTIL_PKG.debug('Binding b_minor_category');
341 DBMS_SQL.bind_variable(c, 'b_minor_category', PARM.p_minor_category);
342 END IF;
343
344 IF PARM.p_book_type_code IS NOT NULL THEN
345 FA_RX_UTIL_PKG.debug('Binding b_book_type_code');
346 DBMS_SQL.bind_variable(c, 'b_book_type_code', PARM.p_book_type_code);
347 END IF;
348
349 IF PARM.p_fiscal_year_start_date IS NOT NULL THEN
350 FA_RX_UTIL_PKG.debug('Binding b_fiscal_year_start_date');
351 DBMS_SQL.bind_variable(c, 'b_fiscal_year_start_date', PARM.p_fiscal_year_start_date);
352 END IF;
353
354
355 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_bind()-');
356
357 END fa_ASSET_bind;
358
359
360 /*===================================================================+
361 | fa_ASSET_after_fetch |
362 +====================================================================*/
363 -- The after fetch trigger fires after the Select statement has executed
364 PROCEDURE fa_ASSET_after_fetch IS
365 BEGIN
366 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_after_fetch()+');
367
368 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_id =' || TO_CHAR(JGRX_FAREG.var.asset_id));
369
370 --Clear the Parent Asset Number
371 JGRX_FAREG.var.parent_asset_number := NULL;
372 -- It takes the parent asset number
373 IF JGRX_FAREG.var.parent_asset_id IS NOT NULL THEN
374 JGRX_FAREG.var.parent_asset_number := Get_parent_asset_number;
375 END IF;
376 JGRX_FAREG.var.asset_heading := 'ASSET NUMBER ';
377 JGRX_FAREG.var.starting_deprn_year := to_char(JGRX_FAREG.var.date_placed_in_service,'YYYY');
378 FA_RX_UTIL_PKG.debug('date_placed_in_service'||to_char(JGRX_FAREG.var.date_placed_in_service));
379 FA_RX_UTIL_PKG.debug('Starting deprn Year' || JGRX_FAREG.var.starting_deprn_year);
380 -- It takes the cost values
381
382
383 FA_RX_UTIL_PKG.debug('PARM.p_period_from'||PARM.p_period_from);
384 FA_RX_UTIL_PKG.debug('PARM.p_period_to'||PARM.p_period_to);
385
386 JGRX_FAREG.var.expense_account := null;
387 if X_Account_segment <> 'NONE'then
388 Get_Deprn_Accounts;
389 end if;
390
391 Get_cost_value;
392
393 -- It takes the revaluation values
394 Get_revaluation;
395
396 -- It takes the cost values
397 Get_deprn_reserve_value;
398
399 -- It takes the cost values
400 Get_bonus_reserve_value;
401
402 -- It takes the fiscal year start and end date
403 Get_fiscal_year_date;
404
405 -- It takes the bonus rate
406 Get_bonus_rate;
407
408 -- It takes the depreciation rate
409 Get_depreciation_rate;
410
411 -- It gets the invoice_number and the supplier name
412 Get_invoice_number;
413
414
415 -- It takes the net book value
416 Get_Net_Book_Value;
417
418 -- It takes the transactions
419 X_first_row := FALSE; -- It must be FALSE the first time
420 JGRX_FAREG.var.transaction_date := NULL;
421 JGRX_FAREG.var.transaction_number := NULL;
422 JGRX_FAREG.var.transaction_code := NULL;
423 JGRX_FAREG.var.transaction_amount := NULL;
424 Get_Transactions;
425
426 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_after_fetch()-');
427 END fa_ASSET_after_fetch;
428
429
430 /*===================================================================+
431 | fa_ASSET_after_report |
432 +====================================================================*/
433 PROCEDURE fa_ASSET_after_report IS
434 BEGIN
435 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_after_report()+');
436
437 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_ASSET_after_report()-');
438 END fa_ASSET_after_report;
439
440
441 /*****************************************************************************************************
442
443 R E T I R E M E N T
444
445 *****************************************************************************************************/
446
447 /*===================================================================+
448 | fa_RTRMNT_before_report |
449 +====================================================================*/
450 -- This is the before report trigger for the main Report. The code which is written in the " BEFORE
451 -- REPORT " triggers has been incorporated over here. The code is the common code accross all the
452 -- reports.
453
454 PROCEDURE fa_RTRMNT_before_report
455 IS
456
457
458 BEGIN
459 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_before_report()+');
460
461
462 -- It takes the segment condition, Company name, currency code and store in placeholder variable
463 Startup;
464
465 --Assign SELECT list
466 -- the Select statement is build over here
467
468 -- FA_RX_UTIL_PKG.assign_column(#, select, insert, place, type, len);
469
470
471 -->>SELECT_START<<--
472
473 FA_RX_UTIL_PKG.assign_column('1', NULL, 'organization_name','JGRX_FAREG.var.organization_name','VARCHAR2',60);
474 FA_RX_UTIL_PKG.assign_column('2', NULL, 'functional_currency_code','JGRX_FAREG.var.functional_currency_code', 'VARCHAR2',15);
475
476 FA_RX_UTIL_PKG.assign_column('3', 'CA.' || X_major_segment, 'major_category','JGRX_FAREG.var.major_category','VARCHAR2', 30);
477 FA_RX_UTIL_PKG.assign_column('4', X_minor_segment_sel, 'minor_category','JGRX_FAREG.var.minor_category','VARCHAR2', 30);
478
479 FA_RX_UTIL_PKG.assign_column('5', 'AD.asset_number', 'asset_number','JGRX_FAREG.var.asset_number','VARCHAR2', 15);
480 FA_RX_UTIL_PKG.assign_column('6', 'AD.description', 'description','JGRX_FAREG.var.description','VARCHAR2', 80);
481
482 FA_RX_UTIL_PKG.assign_column('7', 'AD.parent_asset_id', NULL,'JGRX_FAREG.var.parent_asset_id','NUMBER');
483 FA_RX_UTIL_PKG.assign_column('8', NULL, 'parent_asset_number','JGRX_FAREG.var.parent_asset_number','VARCHAR2', 15);
484
485 FA_RX_UTIL_PKG.assign_column('9', 'BO.original_cost', 'asset_cost_orig','JGRX_FAREG.var.asset_cost_orig','NUMBER');
486 FA_RX_UTIL_PKG.assign_column('10', 'RE.proceeds_of_sale', 'sales_amount','JGRX_FAREG.var.sales_amount','NUMBER');
487 FA_RX_UTIL_PKG.assign_column('11', 'RE.cost_retired', 'cost_retired','JGRX_FAREG.var.cost_retired','NUMBER');
488 FA_RX_UTIL_PKG.assign_column('12', NULL, 'deprn_reserve','JGRX_FAREG.var.deprn_reserve','NUMBER');
489 FA_RX_UTIL_PKG.assign_column('13', NULL, 'bonus_reserve','JGRX_FAREG.var.bonus_reserve','NUMBER');
490 FA_RX_UTIL_PKG.assign_column('14', 'RE.nbv_retired', 'net_book_value','JGRX_FAREG.var.net_book_value','NUMBER');
491 FA_RX_UTIL_PKG.assign_column('15', 'RE.gain_loss_amount', 'gain_loss','JGRX_FAREG.var.gain_loss','NUMBER');
492 FA_RX_UTIL_PKG.assign_column('16', 'RE.reference_num', 'invoice_number','JGRX_FAREG.var.invoice_number','VARCHAR2', 50);
493 FA_RX_UTIL_PKG.assign_column('17', 'RE.asset_id', NULL,'JGRX_FAREG.var.asset_id','NUMBER');
494 FA_RX_UTIL_PKG.assign_column('18', 'RE.date_retired', 'date_retired','JGRX_FAREG.var.date_retired','DATE');
495 FA_RX_UTIL_PKG.assign_column('19', 'RE.transaction_header_id_in', NULL,'JGRX_FAREG.var.transaction_header_id','NUMBER');
496 FA_RX_UTIL_PKG.assign_column('20', NULL,'asset_heading','JGRX_FAREG.var.asset_heading','VARCHAR2', 15);
497 -->>SELECT_END<<--
498
499
500 --
501 -- Assign From Clause
502 --
503 FA_RX_UTIL_PKG.From_Clause :=
504 'fa_retirements re,
505 fa_additions ad,
506 fa_categories ca,
507 fa_books bo';
508
509
510 --
511 -- Assign Where Clause
512 --
513
514 FA_RX_UTIL_PKG.Where_clause:=
515 ' AD.asset_id = RE.ASSET_ID' ||
516 ' AND RE.STATUS = ''PROCESSED'''||
517 ' AND AD.asset_category_id = CA.category_id' ||
518 ' AND AD.asset_id = BO.asset_id' ||
519 ' AND BO.date_effective = (SELECT MAX(date_effective)' ||
520 ' FROM fa_books' ||
521 ' WHERE TO_CHAR(date_effective,''DD-MON-YYYY HH:MI:SS'') < ' ||
522 ' TO_CHAR(:b_period_to_date,''DD-MON-YYYY HH:MI:SS'')' ||
523 ' AND book_type_code = BO.book_type_code' ||
524 ' AND asset_id = BO.asset_id) '||
525 ' AND RE.date_effective = (SELECT MAX(date_effective)' ||
526 ' FROM fa_retirements' ||
527 ' WHERE TO_CHAR(date_effective,''DD-MON-YYYY HH:MI:SS'') < ' ||
528 ' TO_CHAR(:b_period_to_date,''DD-MON-YYYY HH:MI:SS'')' ||
529 ' AND book_type_code = BO.book_type_code' ||
530 ' AND asset_id = BO.asset_id)';
531
532 -- It takes the segment condition
533 IF X_where_clause_tmp IS NOT NULL THEN
534 FA_RX_UTIL_PKG.Where_Clause := FA_RX_UTIL_PKG.Where_clause || X_where_clause_tmp;
538 FA_RX_UTIL_PKG.Where_clause := FA_RX_UTIL_PKG.Where_clause ||
535 END IF;
536
537 IF PARM.p_book_type_code IS NOT NULL THEN
539 ' AND BO.book_type_code = :b_book_type_code ';
540 END IF;
541 -- Both parameters are not null...anyway
542 IF PARM.p_begin_period_from_date IS NOT NULL AND PARM.p_end_period_to_date IS NOT NULL THEN
543 FA_RX_UTIL_PKG.Where_clause := FA_RX_UTIL_PKG.Where_clause || ' AND RE.date_retired ' ||
544 'BETWEEN :b_period_from_date AND :b_period_to_date ';
545 END IF;
546
547 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_before_report()-');
548
549 END fa_RTRMNT_before_report;
550
551
552
553 /*===================================================================+
554 | fa_RTRMNT_bind |
555 +====================================================================*/
556 -- This is the bind trigger for the
557 PROCEDURE fa_RTRMNT_bind (c IN INTEGER)
558 IS
559 b_major_category VARCHAR2(20);
560 b_minor_category VARCHAR2(20);
561 b_period_from_date VARCHAR2(20);
562 b_period_to_date VARCHAR2(20);
563 b_book_type_code VARCHAR2(15);
564
565 BEGIN
566 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_bind()+');
567
568 IF PARM.p_begin_period_from_date IS NOT NULL THEN
569 FA_RX_UTIL_PKG.debug('Binding b_period_from_date');
570 DBMS_SQL.bind_variable(c, 'b_period_from_date', PARM.p_begin_period_from_date);
571 END IF;
572
573 IF PARM.p_end_period_to_date IS NOT NULL THEN
574 FA_RX_UTIL_PKG.debug('Binding b_period_to_date');
575 DBMS_SQL.bind_variable(c, 'b_period_to_date', PARM.p_end_period_to_date);
576 END IF;
577
578 IF PARM.p_major_category IS NOT NULL THEN
579 FA_RX_UTIL_PKG.debug('Binding major_category');
580 DBMS_SQL.bind_variable(c, 'b_major_category', PARM.p_major_category);
581 END IF;
582
583 IF PARM.p_minor_category IS NOT NULL THEN
584 FA_RX_UTIL_PKG.debug('Binding b_minor_category');
585 DBMS_SQL.bind_variable(c, 'b_minor_category', PARM.p_minor_category);
586 END IF;
587
588 IF PARM.p_book_type_code IS NOT NULL THEN
589 FA_RX_UTIL_PKG.debug('Binding b_book_type_code');
590 DBMS_SQL.bind_variable(c, 'b_book_type_code', PARM.p_book_type_code);
591 END IF;
592
593 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_bind()-');
594
595 END fa_RTRMNT_bind;
596
597
598 /*===================================================================+
599 | fa_RTRMNT_after_fetch |
600 +====================================================================*/
601 -- The after fetch trigger fires after the Select statement has executed
602 PROCEDURE fa_RTRMNT_after_fetch IS
603 BEGIN
604 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_after_fetch()+');
605
606 -- It takes the parent asset number
607
608 JGRX_FAREG.var.parent_asset_number := null;
609 IF JGRX_FAREG.var.parent_asset_id IS NOT NULL THEN
610 JGRX_FAREG.var.parent_asset_number := Get_parent_asset_number;
611 END IF;
612
613 JGRX_FAREG.var.asset_heading := 'ASSET NUMBER ';
614
615 -- It takes the reserve values
616 Get_RTRMNT_reserve;
617
618 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_after_fetch()-');
619 END fa_RTRMNT_after_fetch;
620
621
622 /*===================================================================+
623 | fa_RTRMNT_after_report |
624 +====================================================================*/
625 PROCEDURE fa_RTRMNT_after_report IS
626 BEGIN
627 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_after_report()+');
628
629
630 FA_RX_UTIL_PKG.debug('JGRX_FAREG.fa_RTRMNT_after_report()-');
631 END fa_RTRMNT_after_report;
632
633
634 /*===================================================================+
635 | Get_Account_Segment |
636 +====================================================================*/
637 FUNCTION Get_Account_Segment RETURN VARCHAR2 IS
638
639 X_Account_Segment Varchar2(50);
640 X_ret BOOLEAN := TRUE;
641 X_id_flex_num Number(15);
642 Begin
643 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_Account_segment()+');
644
645 select sob.chart_of_accounts_id
646 into X_id_flex_num
647 from gl_sets_of_books sob,
648 fa_book_controls bkc
649 where bkc.book_type_code = PARM.p_Book_type_code and
650 bkc.set_of_books_id = sob.set_of_books_id;
651
652 X_ret := FND_FLEX_APIS.get_segment_column(
653 101, -- x_application_id
654 'GL#', -- x_id_flex_code in
655 X_id_flex_Num, -- x_id_flex_num in
656 'GL_ACCOUNT', -- x_seg_attr_type
657 X_Account_segment); -- x_app_column_name
658 If X_ret = TRUE then
659 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Account_segment'||X_account_segment);
660 return (X_Account_Segment);
661 else
665 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_Account_segment()-');
662 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Account_segment'||'NONE');
663 return ('NONE');
664 end if;
666
667 end Get_Account_Segment;
668
669 /*===================================================================+
670 | Get_category_segment |
671 +====================================================================*/
672 FUNCTION Get_category_segment RETURN VARCHAR2
673 IS
674
675 X_where_clause VARCHAR2(2000);
676 X_ret BOOLEAN := TRUE;
677 X_msg VARCHAR2(100);
678
679 BEGIN
680 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_category_segment()+');
681
682 -- Takes major segment
683 X_ret := FND_FLEX_APIS.get_segment_column(
684 140, -- x_application_id
685 'CAT#', -- x_id_flex_code in
686 101, -- x_id_flex_num in
687 'BASED_CATEGORY', -- x_seg_attr_type
688 X_major_segment); -- x_app_column_name
689
690 IF X_ret = FALSE THEN
691 X_msg := 'JGRX_FAREG.Get_category_segment: error retrieving MAJOR SEGMENT';
692 FA_RX_UTIL_PKG.debug(X_msg);
693 RAISE_APPLICATION_ERROR(-20010,X_msg);
694 ELSIF PARM.p_major_category IS NOT NULL THEN
695 -- Building major segment condition
696 FA_RX_UTIL_PKG.debug('Major Category');
697 X_where_clause := ' AND CA.' || X_major_segment || ' = :b_major_category ';
698 END IF;
699
700 -- Takes minor segment
701 X_ret := FND_FLEX_APIS.get_segment_column(
702 140, -- x_application_id
703 'CAT#', -- x_id_flex_code in
704 101, -- x_id_flex_num in
705 'MINOR_CATEGORY', -- x_seg_attr_type
706 X_minor_segment); -- x_app_column_name
707
708 IF X_ret = FALSE THEN
709 X_msg := 'JGRX_FAREG.Get_category_segment: error retrieving MINOR SEGMENT';
710 FA_RX_UTIL_PKG.debug(X_msg);
711 X_minor_segment_sel := 'NULL';
712 -- RAISE_APPLICATION_ERROR(-20010,X_msg);
713 ELSIF PARM.p_minor_category IS NOT NULL THEN
714 -- Building minor segment condition
715 X_minor_segment_sel := 'CA.'||X_minor_segment;
716 X_where_clause := X_where_clause || ' AND CA.' || X_minor_segment ||
717 ' = :b_minor_category ';
718 END IF;
719 IF X_ret <> FALSE and PARM.p_minor_category IS NULL THEN
720 X_minor_segment_sel := 'CA.'||X_minor_segment;
721 END IF;
722
723 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_category_segment()-');
724
725 RETURN X_where_clause;
726
727 END Get_category_segment;
728
729
730
731 /*===================================================================+
732 | Get_period_date |
733 +====================================================================*/
734 PROCEDURE Get_period_date (
735 p_period_from IN VARCHAR2,
736 p_period_to IN VARCHAR2)
737 IS
738
739 X_msg VARCHAR2(100);
740 BEGIN
741 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_period_date()+');
742
743 PARM.p_period_from := p_period_from;
744 PARM.p_period_to := p_period_to;
745
746 -- Both period are required by anyway...
747 IF PARM.p_period_from IS NOT NULL THEN
748 FA_RX_UTIL_PKG.debug('Get period date from');
749
750 SELECT calendar_period_open_date,
751 calendar_period_close_date,
752 period_counter,
753 fiscal_year -- fiscal year for periods
754 INTO PARM.p_begin_period_from_date,
755 PARM.p_begin_period_to_date,
756 PARM.p_begin_period_counter,
757 X_fiscal_year
758 FROM fa_deprn_periods
759 WHERE book_type_code = PARM.p_book_type_code
760 AND period_counter > (SELECT MIN(DP2.period_counter)
761 FROM fa_deprn_periods DP2
762 WHERE DP2.book_type_code = PARM.p_book_type_code)
763 AND period_name = PARM.p_period_from;
764 END IF;
765
766 IF PARM.p_period_to IS NOT NULL THEN
767 FA_RX_UTIL_PKG.debug('Get period date to');
768
769 SELECT calendar_period_open_date,
770 calendar_period_close_date,
771 period_counter
772 INTO PARM.p_end_period_from_date,
773 PARM.p_end_period_to_date,
774 PARM.p_end_period_counter
775 FROM fa_deprn_periods
776 WHERE book_type_code = PARM.p_book_type_code
777 AND period_counter > (SELECT MIN(DP2.period_counter)
778 FROM fa_deprn_periods DP2
779 WHERE DP2.book_type_code = PARM.p_book_type_code)
780 AND period_name = PARM.p_period_to;
781 END IF;
782
783
784 FA_RX_UTIL_PKG.debug('PARM.p_begin_period_from_date ' || to_char(PARM.p_begin_period_from_date));
785 FA_RX_UTIL_PKG.debug('PARM.p_begin_period_to_date ' || to_char(PARM.p_begin_period_to_date));
789 FA_RX_UTIL_PKG.debug('PARM.p_end_period_counter'|| to_char(PARM.p_end_period_counter));
786 FA_RX_UTIL_PKG.debug('PARM.p_end_period_from_date ' || to_char(PARM.p_end_period_from_date));
787 FA_RX_UTIL_PKG.debug('PARM.p_end_period_to_date ' || to_char(PARM.p_end_period_to_date));
788 FA_RX_UTIL_PKG.debug('PARM.p_begin_period_counter ' || to_char(PARM.p_begin_period_counter));
790
791 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_period_date()-');
792
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 X_msg := 'JGRX_FAREG.Get_period_date: NO_DATA_FOUND';
796 FA_RX_UTIL_PKG.debug(X_msg);
797 RAISE_APPLICATION_ERROR(-20010,X_msg);
798
799 WHEN TOO_MANY_ROWS THEN
800 X_msg := 'JGRX_FAREG.Get_period_date: TOO_MANY_ROWS';
801 FA_RX_UTIL_PKG.debug(X_msg);
802 RAISE_APPLICATION_ERROR(-20010,X_msg);
803
804 END Get_period_date;
805
806 /*===================================================================+
807 | Get_RTRMNT_reserve |
808 +====================================================================*/
809 PROCEDURE Get_RTRMNT_reserve
810 IS
811 BEGIN
812 SELECT SUM((DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
813 (DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0)))
814 INTO JGRX_FAREG.var.deprn_reserve
815 FROM fa_adjustments AD
816 WHERE AD.source_type_code in ('RETIREMENT')
817 AND AD.book_type_code = PARM.p_book_type_code
818 AND AD.asset_id = JGRX_FAREG.var.asset_id
819 AND AD.debit_credit_flag = 'DR'
820 AND transaction_header_id = JGRX_FAREG.var.transaction_header_id;
821
822 SELECT SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0))
823 INTO JGRX_FAREG.var.bonus_reserve
824 FROM fa_adjustments AD
825 WHERE AD.source_type_code in ('RETIREMENT')
826 AND AD.book_type_code = PARM.p_book_type_code
827 AND AD.asset_id = JGRX_FAREG.var.asset_id
828 AND AD.debit_credit_flag = 'DR'
829 AND transaction_header_id = JGRX_FAREG.var.transaction_header_id;
830
831 END Get_RTRMNT_reserve ;
832
833
834 /*===================================================================+
835 | Get_starting_depreciation_year |
836 +====================================================================*/
837 FUNCTION Get_starting_depreciation_year RETURN NUMBER
838 IS
839
840 X_ret NUMBER(4);
841 X_msg VARCHAR2(100);
842
843 BEGIN
844 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_starting_depreciation_year()+');
845
846 SELECT FY.fiscal_year
847 INTO X_ret
848 FROM fa_fiscal_year FY,
849 fa_convention_types CT,
850 fa_books BO
851 WHERE CT.prorate_convention_code = BO.prorate_convention_code
852 AND FY.fiscal_year_name = CT.fiscal_year_name
853 AND BO.date_ineffective IS NULL
854 AND BO.date_placed_in_service BETWEEN FY.start_date
855 AND FY.end_date
856 AND BO.asset_id = JGRX_FAREG.var.asset_id
857 AND BO.book_type_code = PARM.p_book_type_code;
858
859 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_starting_depreciation_year()-');
860
861 RETURN X_ret;
862
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 X_msg := 'JGRX_FAREG.Get_starting_depreciation_year: NO_DATA_FOUND';
866 FA_RX_UTIL_PKG.debug(X_msg);
867 RAISE_APPLICATION_ERROR(-20010,X_msg);
868
869 WHEN TOO_MANY_ROWS THEN
870 X_msg := 'JGRX_FAREG.Get_starting_depreciation_year: TOO_MANY_ROWS';
871 FA_RX_UTIL_PKG.debug(X_msg);
872 RAISE_APPLICATION_ERROR(-20010,X_msg);
873
874 END Get_starting_depreciation_year;
875
876 /*===================================================================+
877 | Get_depreciation_rate |
878 +====================================================================*/
879 PROCEDURE Get_depreciation_rate
880 IS
881
882 X_msg VARCHAR2(100);
883 X_life_in_years NUMBER;
884 X_number_per_fiscal_year NUMBER;
885 X_life_of_asset NUMBER;
886
887 BEGIN
888 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_depreciation_rate()+');
889
890 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.rate_source_rule = ' || JGRX_FAREG.var.rate_source_rule);
891
892 -- FLAT
893 IF JGRX_FAREG.var.rate_source_rule = 'FLAT' THEN
894 JGRX_FAREG.var.deprn_rate := JGRX_FAREG.var.adjusted_rate;
895 END IF;
896
897
898 -- CALCULATED
899 IF JGRX_FAREG.var.rate_source_rule IN ('CALCULATED','TABLE') THEN
900 SELECT number_per_fiscal_year
901 INTO X_number_per_fiscal_year
902 FROM fa_calendar_types
903 WHERE calendar_type = X_deprn_calendar; -- X_deprn_calendar is retrieved in Get_fiscal_year_date()
904 IF JGRX_FAREG.var.rate_source_rule = 'CALCULATED' THEN
905 X_life_in_years := JGRX_FAREG.var.life_in_months / X_number_per_fiscal_year;
906 FA_RX_UTIL_PKG.debug('Life in years ='||to_char(X_life_in_years));
907 JGRX_FAREG.var.deprn_rate := 1 / X_life_in_years;
908 FA_RX_UTIL_PKG.debug('deprn rate'||to_char(JGRX_FAREG.var.deprn_rate));
909 END IF;
910 END IF;
911
912
913 -- TABLE
914 IF JGRX_FAREG.var.rate_source_rule = 'TABLE' THEN
918 FROM dual;
915 SELECT (ROUND(MONTHS_BETWEEN(PARM.p_end_period_to_date,JGRX_FAREG.var.date_placed_in_service)/
916 X_number_per_fiscal_year))+1
917 INTO X_life_of_asset
919
920 FA_RX_UTIL_PKG.debug('Life of the asset= ' || to_char(x_life_of_asset));
921 FA_RX_UTIL_PKG.debug('deprn calendar = ' || x_deprn_calendar);
922 FA_RX_UTIL_PKG.debug('start date = ' || JGRX_FAREG.var.prorate_date);
923 FA_RX_UTIL_PKG.debug(' date placed in service = ' || JGRX_FAREG.var.date_placed_in_service);
924 FA_RX_UTIL_PKG.debug('number per fiscal year= ' || to_char(x_number_per_fiscal_year));
925 FA_RX_UTIL_PKG.debug('METHOD ID= ' || to_char(JGRX_FAREG.var.method_id));
926
927 SELECT rate
928 INTO JGRX_FAREG.var.deprn_rate
929 FROM fa_rates
930 WHERE method_id = JGRX_FAREG.var.method_id
931 AND year = X_life_of_asset
932 AND period_placed_in_service = (SELECT period_num
933 FROM fa_calendar_periods
934 WHERE calendar_type = X_deprn_calendar -- X_deprn_calendar is retrieved in Get_fiscal_year_date()
935 AND JGRX_FAREG.var.prorate_date BETWEEN start_date -- X_prorate_date is retrieved in Get_fiscal_year_date()
936 AND end_date);
937
938 END IF;
939 IF JGRX_FAREG.var.rate_source_rule IN ('CALCULATED','FLAT','TABLE') THEN
940
941 JGRX_FAREG.var.deprn_rate := JGRX_FAREG.var.deprn_rate *100;
942 END IF;
943
944 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.deprn_rate =' || TO_CHAR(JGRX_FAREG.var.deprn_rate));
945
946 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_depreciation_rate()-');
947
948 EXCEPTION
949 WHEN NO_DATA_FOUND THEN
950 X_msg := 'JGRX_FAREG.Get_depreciation_rate: NO_DATA_FOUND';
951 FA_RX_UTIL_PKG.debug(X_msg);
952 RAISE_APPLICATION_ERROR(-20010,X_msg);
953
954 WHEN TOO_MANY_ROWS THEN
955 X_msg := 'JGRX_FAREG.Get_depreciation_rate: TOO_MANY_ROWS';
956 FA_RX_UTIL_PKG.debug(X_msg);
957 RAISE_APPLICATION_ERROR(-20010,X_msg);
958
959 END Get_depreciation_rate;
960
961
962 /*===================================================================+
963 | Get_Deprn_Accounts |
964 +====================================================================*/
965
966 PROCEDURE Get_Deprn_Accounts
967 IS
968 X_msg VARCHAR2(100);
969 V_CURSORiD iNTEGER;
970 v_Selectstmnt varchar2(20000);
971 v_Selectstmnt1 varchar2(10000);
972 v_selectstmnt2 varchar2(10000);
973 v_selectstmnt3 varchar2(10000);
974 V_expense_account varchar2(100);
975 v_dummy integer ;
976 BEGIN
977 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_Deprn_Account()+');
978 JGRX_FAREG.var.expense_account := NULL;
979 v_cursorId := DBMS_SQL.OPEN_CURSOR;
980 v_Selectstmnt1 :=
981 'select '||X_account_segment||' from '||
982 ' fa_distribution_history dih,'||
983 ' gl_code_combinations gcc'||
984 ' where dih.asset_id ='||JGRX_FAREG.var.asset_id || 'and '||
985 ' dih.book_type_code = '||''''||PARM.p_book_type_code ||''''||' and'||
986 ' gcc.code_combination_id = dih.code_combination_id'||
987 ' and dih.transaction_header_id_in =';
988 v_selectstmnt2 := '(select to_char(MAX(transaction_heaDer_id))'||
989 ' from fa_transaction_headers trh,'||
990 ' fa_distribution_history dih1'||
991 ' where dih1.asset_id= dih.asset_id and'||
992 ' dih1.book_type_code =dih.book_type_code and'||
993 ' dih1.transaction_header_id_in = trh.transaction_header_id and';
994 v_selectstmnt3 := ' transaction_date_entered <= '||''''||PARM.p_end_period_to_date||''''||')';
995
996 DBMS_SQL.PARSE(V_cursorId,v_selectstmnt1||v_selectstmnt2||v_selectstmnt3,DBMS_SQL.V7);
997 DBMS_SQL.DEFINE_COLUMN(V_cursorId,1,V_expense_account,100);
998 V_DUMMY := DBMS_SQL.EXECUTE(V_cursorId);
999 LOOP
1000 IF DBMS_SQL.FETCH_ROWS(V_cursorId) = 0 THEN
1001 EXIT;
1002 END IF;
1003 DBMS_SQL.COLUMN_VALUE(V_cursorId,1,V_expense_account);
1004 JGRX_FAREG.var.expense_account:= JGRX_FAREG.var.expense_account||V_expense_account;
1005 END LOOP;
1006 dbms_sql.close_cursor(v_cursorId);
1007 FA_RX_UTIL_PKG.debug('JGRX_FAREG.expense_account = ' || JGRX_FAREG.var.expense_account);
1008 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_depr_account()-');
1009 END Get_deprn_accounts;
1010
1011
1012
1013
1014 /*===================================================================+
1015 | Get_Invoice Number and Supplier Name |
1016 +====================================================================*/
1017
1018 PROCEDURE Get_invoice_number
1019 IS
1020 X_msg VARCHAR2(100);
1021 CURSOR c_invoice_supplier IS
1022 select invoice_number,vendor_name
1023 from fa_asset_invoices ai,po_vendors ve,fa_invoice_transactions IT
1024 where ai.po_vendor_id= ve.vendor_id and
1025 ai.asset_id = JGRX_FAREG.var.asset_id and
1026 ai.invoice_transaction_id_in = IT.invoice_transaction_id and
1027 IT.book_type_code = PARM.p_book_type_code;
1028 BEGIN
1029 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_inv_number()+');
1030 JGRX_FAREG.var.invoice_number := null;
1031 JGRX_FAREG.var.supplier_name := null;
1032 FOR c_inv in c_invoice_supplier LOOP
1036 JGRX_FAREG.var.invoice_number := JGRX_FAREG.var.invoice_number||c_inv.invoice_number;
1033 IF c_invoice_supplier%ROWCOUNT=0 then
1034 exit;
1035 END IF;
1037 JGRX_FAREG.var.supplier_name := JGRX_FAREG.var.supplier_name ||c_inv.vendor_name;
1038 END LOOP;
1039 FA_RX_UTIL_PKG.debug('JGRX_FAREG.invoice_number = ' || JGRX_FAREG.var.invoice_number);
1040 FA_RX_UTIL_PKG.debug('JGRX_FAREG.supplier_name = ' || JGRX_FAREG.var.supplier_name);
1041 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_inv_number()-');
1042 END Get_invoice_number;
1043
1044 /*
1045 BEGIN
1046 select invoice_number,vendor_name
1047 into JGRX_FAREG.var.invoice_number,
1048 JGRX_FAREG.var.supplier_name
1049 from fa_asset_invoices ai,po_vendors ve
1050 where ai.po_vendor_id= ve.vendor_id and
1051 ai.asset_invoice_id= (SELECT MIN(asset_invoice_id)
1052 FROM fa_asset_invoices AI1,fa_invoice_transactions IT
1053 WHERE AI1.asset_id = JGRX_FAREG.var.asset_id
1054 AND AI1.invoice_transaction_id_in = IT.invoice_transaction_id
1055 AND IT.book_type_code = PARM.p_book_type_code);
1056
1057 FA_RX_UTIL_PKG.debug('JGRX_FAREG.invoice_number = ' || JGRX_FAREG.var.invoice_number);
1058 FA_RX_UTIL_PKG.debug('JGRX_FAREG.supplier_name = ' || JGRX_FAREG.var.supplier_name);
1059 EXCEPTION
1060 WHEN NO_DATA_FOUND THEN
1061 X_msg := 'JGRX_FAREG.Get_inv_number: NO_DATA_FOUND';
1062 FA_RX_UTIL_PKG.debug(X_msg);
1063 JGRX_FAREG.var.invoice_number := null;
1064 JGRX_FAREG.var.supplier_name := null;
1065 END;
1066 */
1067
1068
1069
1070 /*===================================================================+
1071 | Get_parent_asset_number |
1072 +====================================================================*/
1073 FUNCTION Get_parent_asset_number RETURN VARCHAR2
1074 IS
1075
1076 X_msg VARCHAR2(100);
1077 X_parent_asset_number VARCHAR2(15);
1078
1079 BEGIN
1080 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_parent_asset_number()+');
1081
1082 SELECT asset_number
1083 INTO X_parent_asset_number
1084 FROM fa_additions
1085 WHERE asset_id = JGRX_FAREG.var.parent_asset_id;
1086
1087 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_parent_asset_number()-');
1088
1089 RETURN X_parent_asset_number;
1090
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 X_msg := 'JGRX_FAREG.Get_parent_asset_number: NO_DATA_FOUND';
1094 FA_RX_UTIL_PKG.debug(X_msg);
1095 RAISE_APPLICATION_ERROR(-20010,X_msg);
1096
1097 END Get_parent_asset_number;
1098
1099
1100 /*===================================================================+
1101 | Startup |
1102 +====================================================================*/
1103 PROCEDURE Startup
1104 IS
1105
1106 X_msg VARCHAR2(100);
1107
1108 BEGIN
1109 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Startup()+');
1110
1111 -- It takes the segment condition
1112 X_where_clause_tmp := Get_category_segment;
1113
1114 FA_RX_UTIL_PKG.debug('Get Company name');
1115 -- Get Company name and store in placeholder variable
1116 SELECT company_name
1117 INTO JGRX_FAREG.var.organization_name
1118 FROM fa_system_controls;
1119
1120
1121 FA_RX_UTIL_PKG.debug('Get currency code');
1122 -- Get currency code and store in placeholder variable
1123 SELECT currency_code
1124 INTO JGRX_FAREG.var.functional_currency_code
1125 FROM gl_sets_of_books
1126 WHERE set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
1127
1128 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Startup()-');
1129
1130 EXCEPTION
1131 WHEN NO_DATA_FOUND THEN
1132 X_msg := 'JGRX_FAREG.Startup: NO_DATA_FOUND';
1133 FA_RX_UTIL_PKG.debug(X_msg);
1134 RAISE_APPLICATION_ERROR(-20010,X_msg);
1135
1136 WHEN TOO_MANY_ROWS THEN
1137 X_msg := 'JGRX_FAREG.Startup: TOO_MANY_ROWS';
1138 FA_RX_UTIL_PKG.debug(X_msg);
1139 RAISE_APPLICATION_ERROR(-20010,X_msg);
1140
1141 END Startup;
1142
1143
1144 /*===================================================================+
1145 | Get_cost_value |
1146 +====================================================================*/
1147 PROCEDURE Get_cost_value
1148 IS
1149
1150 X_msg VARCHAR2(100);
1151 X_capitalized NUMBER(10);
1152
1153 BEGIN
1154 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_cost_value()+');
1155
1156 BEGIN
1157 FA_RX_UTIL_PKG.debug('Get cost initial value');
1158 -- Get initial value
1159 SELECT count(*)
1160 INTO X_capitalized
1161 FROM fa_books BO
1162 WHERE BO.book_type_code = PARM.p_book_type_code
1163 AND BO.asset_id = JGRX_FAREG.var.asset_id
1164 AND BO.period_counter_capitalized BETWEEN PARM.p_begin_period_counter AND
1165 PARM.p_end_period_counter;
1166 IF X_capitalized > 0 THEN
1167
1168 JGRX_FAREG.var.asset_cost_initial := 0;
1169 ELSE
1170 SELECT cost,
1171 transaction_header_id_in
1172 INTO JGRX_FAREG.var.asset_cost_initial,
1176 AND BO.asset_id = JGRX_FAREG.var.asset_id
1173 X_transaction_id_initial
1174 FROM fa_books BO
1175 WHERE BO.book_type_code = PARM.p_book_type_code
1177 AND (TO_CHAR(BO.date_effective, 'DD-MON-YYYY HH:MI:SS'),transaction_header_id_in) =
1178 (SELECT TO_CHAR(MAX(BO1.date_effective), 'DD-MON-YYYY HH:MI:SS'),
1179 max(transaction_header_id_in)
1180 FROM fa_books BO1, fa_transaction_headers TRH
1181 WHERE BO1.book_type_code = BO.book_type_code
1182 AND BO1.asset_id = BO.asset_id
1183 AND TRH.transaction_header_id= BO1.transaction_header_id_in
1184 AND TRH.transaction_date_entered < PARM.p_begin_period_from_date);
1185 END IF;
1186 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_cost_initial =' || TO_CHAR(JGRX_FAREG.var.asset_cost_initial));
1187
1188 EXCEPTION
1189 WHEN NO_DATA_FOUND THEN
1190 JGRX_FAREG.var.asset_cost_initial := 0;
1191 END;
1192
1193 FA_RX_UTIL_PKG.debug('Get cost final value');
1194 -- Get final value
1195 BEGIN
1196 SELECT cost,
1197 transaction_header_id_in
1198 INTO JGRX_FAREG.var.asset_cost_final,
1199 X_transaction_id_final
1200 FROM fa_books BO
1201 WHERE BO.book_type_code = PARM.p_book_type_code
1202 AND BO.asset_id = JGRX_FAREG.var.asset_id
1203 AND (TO_CHAR(BO.date_effective, 'DD-MON-YYYY HH:MI:SS'),transaction_header_id_in) =
1204 (SELECT TO_CHAR(MAX(BO1.date_effective), 'DD-MON-YYYY HH:MI:SS'),
1205 max(transaction_header_id_in)
1206 FROM fa_books BO1,FA_TRANSACTION_HEADERS TRH
1207 WHERE BO1.book_type_code = BO.book_type_code
1208 AND BO1.asset_id = BO.asset_id
1209 AND TRH.transaction_header_id= BO1.transaction_header_id_in
1210 AND TRH.transaction_date_entered <= PARM.p_end_period_to_date);
1211 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_cost_final =' || TO_CHAR(JGRX_FAREG.var.asset_cost_final));
1212 EXCEPTION
1213 WHEN NO_DATA_FOUND THEN
1214 JGRX_FAREG.var.asset_cost_final := 0;
1215 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_cost_final =' ||'no data found');
1216
1217 END;
1218
1219 -- It takes the asset cost increase
1220 Get_cost_increase;
1221
1222 -- It takes the asset cost increase
1223 Get_cost_decrease;
1224 -- 09/08/00 AFERRARA
1225 JGRX_FAREG.var.asset_variation := JGRX_FAREG.var.asset_cost_increase -
1226 JGRX_FAREG.var.asset_cost_decrease;
1227
1228 JGRX_FAREG.var.revaluation_total := JGRX_FAREG.var.asset_cost_final -
1229 JGRX_FAREG.var.asset_cost_orig;
1230 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_variation =' || JGRX_FAREG.var.asset_variation);
1231 -- 09/08/00 AFERRARA
1232
1233 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_cost_value()-');
1234
1235 EXCEPTION
1236 WHEN NO_DATA_FOUND THEN
1237 X_msg := 'JGRX_FAREG.Get_cost_value: NO_DATA_FOUND';
1238 FA_RX_UTIL_PKG.debug(X_msg);
1239 RAISE_APPLICATION_ERROR(-20010,X_msg);
1240
1241 WHEN TOO_MANY_ROWS THEN
1242 X_msg := 'JGRX_FAREG.Get_cost_value: TOO_MANY_ROWS';
1243 FA_RX_UTIL_PKG.debug(X_msg);
1244 RAISE_APPLICATION_ERROR(-20010,X_msg);
1245
1246 END Get_cost_value;
1247
1248
1249 /*===================================================================+
1250 | Get_cost_increase |
1251 +====================================================================*/
1252 PROCEDURE Get_cost_increase
1253 IS
1254
1255 X_partial_addition NUMBER := 0;
1256 X_manual_adjustment_plus NUMBER := 0;
1257 X_revaluation_plus NUMBER := 0;
1258 X_reinstatements NUMBER := 0;
1259
1260 BEGIN
1261 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_cost_increase()+');
1262
1263 -------------------------------------------------------------------------------
1264 FA_RX_UTIL_PKG.debug('Get partial addition ');
1265 fa_rx_util_pkg.debug('transaction id'||X_transaction_id_initial);
1266 fa_rx_util_pkg.debug('PARM.p_begin_period_to_date'||to_char(PARM.p_begin_period_to_date));
1267 fa_rx_util_pkg.debug('PARM.p_end_period_from_date'||to_char(PARM.p_end_period_from_date));
1268
1269 SELECT SUM(AD.adjustment_amount)
1270 INTO X_partial_addition
1271 FROM fa_books BO,
1272 fa_transaction_headers TH,
1273 fa_adjustments AD
1274 WHERE BO.book_type_code = PARM.p_book_type_code
1275 AND BO.asset_id = JGRX_FAREG.var.asset_id
1276 AND BO.transaction_header_id_in <> NVL(X_transaction_id_initial, 0)
1277 AND BO.book_type_code = TH.book_type_code
1278 AND BO.asset_id = TH.asset_id
1279 AND BO.transaction_header_id_in = TH.transaction_header_id
1280 AND TH.transaction_type_code = 'ADDITION'
1281 AND AD.transaction_header_id = TH.transaction_header_id
1282 AND AD.source_type_code = TH.TRANSACTION_TYPE_CODE
1283 AND AD.book_type_code = TH.book_type_code
1284 AND AD.asset_id = TH.asset_id
1285 AND AD.adjustment_type = 'COST'
1289 AND (PARM.p_end_period_to_date);
1286 AND AD.debit_credit_flag = 'DR'
1287 AND AD.adjustment_amount > 0
1288 AND TH.transaction_date_entered BETWEEN (PARM.p_begin_period_from_date)
1290 -- changed bo.date_effective to TH.transaction_date_entered
1291
1292 FA_RX_UTIL_PKG.debug('partial addition =' || TO_CHAR(X_partial_addition));
1293
1294
1295 -------------------------------------------------------------------------------
1296 FA_RX_UTIL_PKG.debug('Get manual adjustment upwards');
1297
1298 SELECT SUM(AD.adjustment_amount)
1299 INTO X_manual_adjustment_plus
1300 FROM fa_transaction_headers TH,
1301 fa_adjustments AD
1302 WHERE AD.transaction_header_id = TH.transaction_header_id
1303 AND AD.book_type_code = TH.book_type_code
1304 AND AD.asset_id = TH.asset_id
1305 AND TH.transaction_type_code = 'ADJUSTMENT'
1306 AND TH.book_type_code = PARM.p_book_type_code
1307 AND TH.asset_id = JGRX_FAREG.var.asset_id
1308 AND AD.adjustment_type = 'COST'
1309 AND AD.debit_credit_flag = 'DR'
1310 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1311 AND PARM.p_end_period_to_date;
1312
1313 FA_RX_UTIL_PKG.debug('Get manual adjustment upwards =' || TO_CHAR(X_manual_adjustment_plus));
1314
1315
1316 -------------------------------------------------------------------------------
1317 FA_RX_UTIL_PKG.debug('Get revaluations upward ');
1318
1319 SELECT SUM(AD.adjustment_amount)
1320 INTO X_revaluation_plus
1321 FROM fa_transaction_headers TH,
1322 fa_adjustments AD
1323 WHERE AD.transaction_header_id = TH.transaction_header_id
1324 AND AD.book_type_code = TH.book_type_code
1325 AND AD.asset_id = TH.asset_id
1326 AND TH.transaction_type_code = 'REVALUATION'
1327 AND TH.book_type_code = PARM.p_book_type_code
1328 AND TH.asset_id = JGRX_FAREG.var.asset_id
1329 AND AD.adjustment_type = 'COST'
1330 AND AD.debit_credit_flag = 'DR'
1331 AND AD.adjustment_amount > 0
1332 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1333 AND PARM.p_end_period_to_date;
1334
1335 FA_RX_UTIL_PKG.debug('Revaluations Upward =' || TO_CHAR(X_revaluation_plus));
1336
1337
1338 -------------------------------------------------------------------------------
1339 FA_RX_UTIL_PKG.debug('Get reinstatements ');
1340
1341 SELECT SUM(RE.cost_retired)
1342 INTO X_reinstatements
1343 FROM fa_transaction_headers TH,
1344 fa_retirements RE
1345 WHERE RE.transaction_header_id_out = TH.transaction_header_id
1346 AND RE.book_type_code = TH.book_type_code
1347 AND RE.asset_id = TH.asset_id
1348 AND TH.transaction_type_code = 'REINSTATEMENT'
1349 AND TH.book_type_code = PARM.p_book_type_code
1350 AND TH.asset_id = JGRX_FAREG.var.asset_id
1351 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1352 AND PARM.p_end_period_to_date;
1353 -- jmary changing date_retired to transaction_date
1354 FA_RX_UTIL_PKG.debug('Reinstatements =' || TO_CHAR(X_reinstatements));
1355
1356
1357
1358 JGRX_FAREG.var.asset_cost_increase := NVL(X_partial_addition,0) +
1359 NVL(X_manual_adjustment_plus,0) +
1360 NVL(X_revaluation_plus,0) +
1361 NVL(X_reinstatements,0);
1362
1363 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_cost_increase =' || JGRX_FAREG.var.asset_cost_increase);
1364
1365 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_cost_increase()-');
1366
1367
1368 END Get_cost_increase;
1369
1370
1371
1372 /*===================================================================+
1373 | Get_cost_decrease |
1374 +====================================================================*/
1380 X_revaluation_minus NUMBER := 0;
1375 PROCEDURE Get_cost_decrease
1376 IS
1377
1378 X_credit_memos NUMBER := 0;
1379 X_manual_adjustment_minus NUMBER := 0;
1381 X_retirements NUMBER := 0;
1382
1383 BEGIN
1384 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_cost_decrease()+');
1385
1386
1387 -------------------------------------------------------------------------------
1388 FA_RX_UTIL_PKG.debug('Get credit memos ');
1389
1390 SELECT SUM(AD.adjustment_amount)
1391 INTO X_credit_memos
1392 FROM fa_books BO,
1393 fa_transaction_headers TH,
1394 fa_adjustments AD
1395 WHERE BO.book_type_code = PARM.p_book_type_code
1396 AND BO.asset_id = JGRX_FAREG.var.asset_id
1397 AND BO.transaction_header_id_in <> NVL(X_transaction_id_initial, 0)
1398 AND BO.book_type_code = TH.book_type_code
1399 AND BO.asset_id = TH.asset_id
1400 AND BO.transaction_header_id_in = TH.transaction_header_id
1401 AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
1402 AND AD.transaction_header_id = TH.transaction_header_id
1403 AND AD.source_type_code = TH.TRANSACTION_TYPE_CODE
1404 AND AD.book_type_code = TH.book_type_code
1405 AND AD.asset_id = TH.asset_id
1406 AND AD.adjustment_type = 'COST'
1407 AND AD.debit_credit_flag = 'CR'
1408 AND AD.adjustment_amount < 0
1409 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1410 AND PARM.p_end_period_to_date;
1411 -- changed date_effective to transaction_date_entered
1412
1413 FA_RX_UTIL_PKG.debug('credit memos =' || TO_CHAR(X_credit_memos));
1414
1415
1416 -------------------------------------------------------------------------------
1417 FA_RX_UTIL_PKG.debug('Get manual adjustment downwards');
1418
1419 SELECT SUM(AD.adjustment_amount)
1420 INTO X_manual_adjustment_minus
1421 FROM fa_transaction_headers TH,
1422 fa_adjustments AD
1423 WHERE AD.transaction_header_id = TH.transaction_header_id
1424 AND AD.book_type_code = TH.book_type_code
1425 AND AD.asset_id = TH.asset_id
1426 AND TH.transaction_type_code = 'ADJUSTMENT'
1427 AND TH.book_type_code = PARM.p_book_type_code
1428 AND TH.asset_id = JGRX_FAREG.var.asset_id
1429 AND AD.adjustment_type = 'COST'
1430 AND AD.debit_credit_flag = 'CR'
1431 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1432 AND PARM.p_end_period_to_date;
1433
1434 FA_RX_UTIL_PKG.debug('Get manual adjustment downwards =' || TO_CHAR(X_manual_adjustment_minus));
1435
1436
1437 -------------------------------------------------------------------------------
1438 FA_RX_UTIL_PKG.debug('Get revaluations downward ');
1439
1440 SELECT SUM(AD.adjustment_amount)
1441 INTO X_revaluation_minus
1442 FROM fa_transaction_headers TH,
1443 fa_adjustments AD
1444 WHERE AD.transaction_header_id = TH.transaction_header_id
1445 AND AD.book_type_code = TH.book_type_code
1446 AND AD.asset_id = TH.asset_id
1447 AND TH.transaction_type_code = 'REVALUATION'
1448 AND TH.book_type_code = PARM.p_book_type_code
1449 AND TH.asset_id = JGRX_FAREG.var.asset_id
1450 AND AD.adjustment_type = 'COST'
1451 AND AD.debit_credit_flag = 'CR'
1452 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1453 AND PARM.p_end_period_to_date;
1454
1455 FA_RX_UTIL_PKG.debug('Revaluations downward =' || TO_CHAR(X_revaluation_minus));
1456
1457
1458 -------------------------------------------------------------------------------
1459 FA_RX_UTIL_PKG.debug('Get retirements ');
1460
1461 SELECT SUM(RE.cost_retired)
1462 INTO X_retirements
1463 FROM fa_transaction_headers TH,
1464 fa_retirements RE
1465 WHERE RE.transaction_header_id_in = TH.transaction_header_id
1466 AND RE.book_type_code = TH.book_type_code
1467 AND RE.asset_id = TH.asset_id
1468 AND (TH.TRANSACTION_TYPE_CODE = 'PARTIAL RETIREMENT' OR
1469 TH.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT' )
1470 AND TH.book_type_code = PARM.p_book_type_code
1471 AND TH.asset_id = JGRX_FAREG.var.asset_id
1472 AND RE.date_retired BETWEEN PARM.p_begin_period_from_date /* changed Transaction Date to Retirement Date */
1473 AND PARM.p_end_period_to_date;
1474
1475 FA_RX_UTIL_PKG.debug('Retirements =' || TO_CHAR(X_retirements));
1476
1477
1478
1479 JGRX_FAREG.var.asset_cost_decrease := NVL(X_credit_memos,0) +
1480 NVL(X_manual_adjustment_minus,0) +
1481 NVL(X_revaluation_minus,0) +
1482 NVL(X_retirements,0);
1483
1484 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.asset_cost_decrease =' || JGRX_FAREG.var.asset_cost_decrease);
1485
1486 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_cost_decrease()-');
1487
1488
1489 END Get_cost_decrease;
1490
1491
1495 PROCEDURE Get_revaluation
1492 /*===================================================================+
1493 | Get_revaluation |
1494 +====================================================================*/
1496 IS
1497
1498 BEGIN
1499 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation()+');
1500
1501 BEGIN
1502 FA_RX_UTIL_PKG.debug('Get_revaluation initial value');
1503 -- Get initial value
1504 SELECT SUM(DECODE(debit_credit_flag, 'DR', adjustment_amount, 0)) -
1505 SUM(DECODE(debit_credit_flag, 'CR', adjustment_amount, 0))
1506 INTO JGRX_FAREG.var.revaluation_initial
1507 FROM fa_adjustments
1508 WHERE book_type_code = PARM.p_book_type_code
1509 AND asset_id = JGRX_FAREG.var.asset_id
1510 AND source_type_code = 'REVALUATION'
1511 AND adjustment_type = 'COST'
1512 AND period_counter_adjusted <= (PARM.p_begin_period_counter -1);
1513
1517
1514 IF JGRX_FAREG.var.revaluation_initial IS NULL THEN
1515 JGRX_FAREG.var.revaluation_initial := 0;
1516 END IF;
1518 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.revaluation_initial =' || TO_CHAR(JGRX_FAREG.var.revaluation_initial));
1519 END;
1520
1521
1522 BEGIN
1523 FA_RX_UTIL_PKG.debug('Get_revaluation final value');
1524 -- Get final value
1525 SELECT SUM(DECODE(debit_credit_flag, 'DR', adjustment_amount, 0)) -
1526 SUM(DECODE(debit_credit_flag, 'CR', adjustment_amount, 0))
1527 INTO JGRX_FAREG.var.revaluation_final
1528 FROM fa_adjustments
1529 WHERE book_type_code = PARM.p_book_type_code
1530 AND asset_id = JGRX_FAREG.var.asset_id
1531 AND source_type_code = 'REVALUATION'
1532 AND adjustment_type = 'COST'
1533 AND period_counter_adjusted <= PARM.p_end_period_counter;
1534
1535 IF JGRX_FAREG.var.revaluation_final IS NULL THEN
1536 JGRX_FAREG.var.revaluation_final := 0;
1537 END IF;
1538
1539
1540 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.revaluation_final =' || TO_CHAR(JGRX_FAREG.var.revaluation_final));
1541 END;
1542
1543 -- It takes the asset revaluation increase
1544 Get_revaluation_change;
1545
1546 -- 09/08/00 AFERRARA
1547 JGRX_FAREG.var.reval_variation := JGRX_FAREG.var.revaluation_increase -
1548 JGRX_FAREG.var.revaluation_decrease;
1549
1550 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.reval_variation =' || JGRX_FAREG.var.reval_variation);
1551 -- 09/08/00 AFERRARA
1552
1553 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation()-');
1554
1555 END Get_revaluation;
1556
1557 /*===================================================================+
1558 | Get_revaluation_change |
1559 +====================================================================*/
1560 PROCEDURE Get_revaluation_change
1561 IS
1562
1563 BEGIN
1564 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation_change()+');
1565
1566 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',AD.adjustment_amount,0)),
1567 SUM(DECODE(DEBIT_CREDIT_FLAG,'CR',AD.adjustment_amount,0))
1568 INTO JGRX_FAREG.var.revaluation_increase,JGRX_FAREG.var.revaluation_decrease
1569 FROM fa_adjustments AD
1570 WHERE AD.book_type_code = PARM.p_book_type_code
1571 AND AD.asset_id = JGRX_FAREG.var.asset_id
1572 AND AD.source_type_code = 'REVALUATION'
1573 AND AD.adjustment_type = 'COST'
1574 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1575 AND PARM.p_end_period_counter ;
1576 IF JGRX_FAREG.var.revaluation_increase IS NULL THEN
1577 JGRX_FAREG.var.revaluation_increase := 0;
1578 END IF;
1579 IF JGRX_FAREG.var.revaluation_decrease IS NULL THEN
1580 JGRX_FAREG.var.revaluation_decrease := 0;
1581 END IF;
1582
1583 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.revaluation_increase =' || JGRX_FAREG.var.revaluation_increase);
1584 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.revaluation_decrease =' || JGRX_FAREG.var.revaluation_decrease);
1585
1586 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation_change()-');
1587
1588
1589 END Get_revaluation_change;
1590
1591 /*===================================================================+
1592 | Get_depr_reserve_value |
1593 +====================================================================*/
1594 PROCEDURE Get_deprn_reserve_value
1595 IS
1596
1597 X_msg VARCHAR2(100);
1598
1599 BEGIN
1603 BEGIN
1600 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_deprn_reserve_value()+');
1601
1602 -------------------------------------------------------------------------------
1604 FA_RX_UTIL_PKG.debug('Get deprn reserve initial value');
1605 -- Get initial value
1606
1607 SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0))
1608 INTO JGRX_FAREG.var.deprn_reserve_initial
1609 FROM fa_deprn_summary
1610 WHERE book_type_code = PARM.p_book_type_code
1611 AND asset_id = JGRX_FAREG.var.asset_id
1612 AND period_counter = (select max(period_counter)
1613 from fa_deprn_summary
1614 where period_counter <= (PARM.p_begin_period_counter-1)
1615 and asset_id= JGRX_FAREG.var.asset_id
1616 and book_type_code = PARM.p_book_type_code );
1617
1618
1619 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.deprn_reserve_initial =' || TO_CHAR(JGRX_FAREG.var.deprn_reserve_initial));
1620 EXCEPTION
1621 WHEN NO_DATA_FOUND THEN
1622 JGRX_FAREG.var.deprn_reserve_initial := 0;
1623 END;
1624
1625
1626 -------------------------------------------------------------------------------
1627 BEGIN
1628 FA_RX_UTIL_PKG.debug('Get deprn reserve final value');
1629 -- Get final value
1630 SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0))
1631 INTO JGRX_FAREG.var.deprn_reserve_final
1632 FROM fa_deprn_summary
1633 WHERE book_type_code = PARM.p_book_type_code
1634 AND asset_id = JGRX_FAREG.var.asset_id
1635 AND period_counter =
1636 (select max(period_counter)
1637 from fa_deprn_summary
1638 where period_counter <= PARM.p_end_period_counter
1639 and asset_id= JGRX_FAREG.var.asset_id
1640 and book_type_code = PARM.p_book_type_code );
1641
1642
1643 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.deprn_reserve_final =' || TO_CHAR(JGRX_FAREG.var.deprn_reserve_final));
1644 EXCEPTION
1645 WHEN NO_DATA_FOUND THEN
1646 JGRX_FAREG.var.deprn_reserve_final := 0;
1647 END;
1648
1649
1650 -- It takes the deprn reserve increase
1651 Get_deprn_reserve_increase;
1652
1653 -- It takes the deprn reserve decrease
1654 Get_deprn_reserve_decrease;
1655
1656 -- 09/08/00 AFERRARA
1657 JGRX_FAREG.var.deprn_variation := JGRX_FAREG.var.deprn_reserve_increase -
1658 JGRX_FAREG.var.deprn_reserve_decrease;
1659
1660 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.deprn_variation =' || JGRX_FAREG.var.deprn_variation);
1661 -- 09/08/00 AFERRARA
1662
1663 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_deprn_reserve_value()-');
1664
1665 EXCEPTION
1666 WHEN TOO_MANY_ROWS THEN
1670
1667 X_msg := 'JGRX_FAREG.Get_deprn_reserve_value: TOO_MANY_ROWS';
1668 FA_RX_UTIL_PKG.debug(X_msg);
1669 RAISE_APPLICATION_ERROR(-20010,X_msg);
1671
1672 END Get_deprn_reserve_value;
1673
1674
1675
1676 /*===================================================================+
1677 | Get_deprn_reserve_increase |
1678 +====================================================================*/
1679 PROCEDURE Get_deprn_reserve_increase
1680 IS
1681
1682 X_ord_deprn NUMBER := 0;
1683 X_reinstatements NUMBER := 0;
1684 X_tax_re_adjustment_plus NUMBER := 0;
1685
1686 BEGIN
1687 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_deprn_reserve_increase()+');
1688
1689 -------------------------------------------------------------------------------
1690 FA_RX_UTIL_PKG.debug('Get ordinary depreciation ');
1691
1692 SELECT SUM((NVL(deprn_amount,0) - NVL(bonus_deprn_amount,0)))
1693 INTO X_ord_deprn
1694 FROM fa_deprn_summary
1695 WHERE book_type_code = PARM.p_book_type_code
1696 AND asset_id = JGRX_FAREG.var.asset_id
1697 AND period_counter BETWEEN PARM.p_begin_period_counter
1698 AND PARM.p_end_period_counter;
1699
1700 FA_RX_UTIL_PKG.debug('Ordinary depreciation =' || TO_CHAR(X_ord_deprn));
1701
1702
1703 -------------------------------------------------------------------------------
1704 FA_RX_UTIL_PKG.debug('Get increase due to reinstatements and Revaluations ');
1705
1706 SELECT abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
1707 SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0)))
1708 INTO X_reinstatements
1709 FROM fa_adjustments AD
1710 WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
1711 AND AD.book_type_code = PARM.p_book_type_code
1712 AND AD.asset_id = JGRX_FAREG.var.asset_id
1713 AND AD.debit_credit_flag = 'CR'
1714 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1715 AND PARM.p_end_period_counter;
1716
1717
1718 FA_RX_UTIL_PKG.debug('Reinstatements =' || TO_CHAR(X_reinstatements));
1719
1720 -------------------------------------------------------------------------------
1721 FA_RX_UTIL_PKG.debug('Get positive tax reserve adjustment ');
1722
1723 SELECT SUM(adjustment_amount)
1724 INTO X_tax_re_adjustment_plus
1725 FROM fa_adjustments AD
1726 WHERE AD.source_type_code = 'TAX'
1727 AND AD.adjustment_type = 'RESERVE'
1728 AND AD.debit_credit_flag = 'CR'
1729 AND AD.book_type_code = PARM.p_book_type_code
1730 AND AD.asset_id = JGRX_FAREG.var.asset_id
1731 AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
1732 AND PARM.p_end_period_counter;
1733 -- changed period_counter_adjusted to period_counter_created
1734 FA_RX_UTIL_PKG.debug('Positive tax reserve adjustment =' || TO_CHAR(X_tax_re_adjustment_plus));
1735
1736
1737 JGRX_FAREG.var.deprn_reserve_increase := NVL(X_ord_deprn,0) +
1738 NVL(X_reinstatements,0) +
1739 NVL(X_tax_re_adjustment_plus,0);
1740
1741 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.deprn_reserve_increase =' || JGRX_FAREG.var.deprn_reserve_increase);
1742
1743
1744 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_deprn_reserve_increase()-');
1745
1746
1747 END Get_deprn_reserve_increase;
1748
1749
1750 /*===================================================================+
1751 | Get_deprn_reserve_decrease |
1752 +====================================================================*/
1753 PROCEDURE Get_deprn_reserve_decrease
1754 IS
1755
1756 X_retirements NUMBER := 0;
1757 X_financ_adjustment_minus NUMBER := 0;
1758 X_tax_re_adjustment_minus NUMBER := 0;
1759
1760 BEGIN
1761 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_deprn_reserve_decrease()+');
1762
1763
1764 -------------------------------------------------------------------------------
1765 FA_RX_UTIL_PKG.debug('Get deprn reserve decrease due to retirements and revaluations');
1766
1767 SELECT abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
1768 SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0)))
1769 INTO X_retirements
1770 FROM fa_adjustments AD
1771 WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
1772 AND AD.book_type_code = PARM.p_book_type_code
1773 AND AD.asset_id = JGRX_FAREG.var.asset_id
1774 AND AD.debit_credit_flag = 'DR'
1775 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1776 AND PARM.p_end_period_counter;
1777
1778
1779
1780 FA_RX_UTIL_PKG.debug('Retirements =' || TO_CHAR(X_retirements));
1781
1782
1783
1784 -------------------------------------------------------------------------------
1785 FA_RX_UTIL_PKG.debug('Get negative financial adjustment ');
1786
1787 SELECT abs(SUM(DECODE(adjustment_type,'EXPENSE',adjustment_amount,0)) -
1788 SUM(DECODE(adjustment_type,'BONUS EXPENSE',adjustment_amount,0)))
1789 INTO X_financ_adjustment_minus
1790 FROM fa_adjustments AD
1791 WHERE AD.source_type_code = 'DEPRECIATION'
1792 AND AD.adjustment_amount < 0
1793 AND AD.book_type_code = PARM.p_book_type_code
1794 AND AD.asset_id = JGRX_FAREG.var.asset_id
1795 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1796 AND PARM.p_end_period_counter;
1797
1798 FA_RX_UTIL_PKG.debug('Negative financial adjustment =' || TO_CHAR(X_financ_adjustment_minus));
1799
1800 JGRX_FAREG.var.deprn_reserve_increase := JGRX_FAREG.var.deprn_reserve_increase +nvl(X_financ_adjustment_minus,0);
1801
1802 /* to show the increase and then minus it */
1803
1804 -------------------------------------------------------------------------------
1805 FA_RX_UTIL_PKG.debug('Get negative tax reserve adjustment ');
1806
1807 SELECT SUM(adjustment_amount)
1808 INTO X_tax_re_adjustment_minus
1809 FROM fa_adjustments AD
1810 WHERE AD.source_type_code = 'TAX'
1811 AND AD.adjustment_type = 'RESERVE'
1812 AND AD.debit_credit_flag = 'DR'
1813 AND AD.book_type_code = PARM.p_book_type_code
1814 AND AD.asset_id = JGRX_FAREG.var.asset_id
1815 AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
1816 AND PARM.p_end_period_counter;
1817 -- jmary changed period_counter_adjusted to period_counter_created
1818 FA_RX_UTIL_PKG.debug('Negative tax reserve adjustment =' || TO_CHAR(X_tax_re_adjustment_minus));
1819
1820
1821 JGRX_FAREG.var.deprn_reserve_decrease := NVL(X_retirements,0) +
1822 NVL(X_financ_adjustment_minus,0) +
1823 NVL(X_tax_re_adjustment_minus,0);
1824
1825 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.deprn_reserve_decrease =' || JGRX_FAREG.var.deprn_reserve_decrease);
1826
1827
1828 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_deprn_reserve_decrease()-');
1829
1830
1831 END Get_deprn_reserve_decrease;
1832
1833
1834
1835 /*===================================================================+
1836 | Get_bonus_reserve_value |
1837 +====================================================================*/
1838 PROCEDURE Get_bonus_reserve_value
1839 IS
1840
1841 X_msg VARCHAR2(100);
1842
1843 BEGIN
1844 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_reserve_value()+');
1845
1846 -------------------------------------------------------------------------------
1847 BEGIN
1848 FA_RX_UTIL_PKG.debug('Get bonus reserve initial value');
1849 -- Get initial value
1850
1851 SELECT NVL(bonus_deprn_reserve,0)
1852 INTO JGRX_FAREG.var.bonus_reserve_initial
1853 FROM fa_deprn_summary
1854 WHERE book_type_code = PARM.p_book_type_code
1855 AND asset_id = JGRX_FAREG.var.asset_id
1856 AND period_counter =
1857 (SELECT max(period_counter)
1858 FROM fa_deprn_summary
1859 WHERE period_counter <= (PARM.p_begin_period_counter-1)
1860 AND asset_id= JGRX_FAREG.var.asset_id
1861 AND book_type_code = PARM.p_book_type_code );
1862
1863
1864 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.bonus_reserve_initial =' || TO_CHAR(JGRX_FAREG.var.bonus_reserve_initial));
1865 EXCEPTION
1866 WHEN NO_DATA_FOUND THEN
1867 JGRX_FAREG.var.bonus_reserve_initial := 0;
1868 END;
1869
1870
1871 -------------------------------------------------------------------------------
1872 BEGIN
1873 FA_RX_UTIL_PKG.debug('Get bonus reserve final value');
1874 -- Get final value
1875 SELECT NVL(bonus_deprn_reserve,0)
1876 INTO JGRX_FAREG.var.bonus_reserve_final
1877 FROM fa_deprn_summary
1878 WHERE book_type_code = PARM.p_book_type_code
1879 AND asset_id = JGRX_FAREG.var.asset_id
1880 AND period_counter =
1881 (SELECT max(period_counter)
1882 FROM fa_deprn_summary
1883 WHERE period_counter <= PARM.p_end_period_counter
1884 AND asset_id= JGRX_FAREG.var.asset_id
1885 AND book_type_code = PARM.p_book_type_code );
1886
1887
1888 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.bonus_reserve_final =' || TO_CHAR(JGRX_FAREG.var.bonus_reserve_final));
1889 EXCEPTION
1890 WHEN NO_DATA_FOUND THEN
1891 JGRX_FAREG.var.bonus_reserve_final := 0;
1892 END;
1893
1894
1895 -- It takes the bonus reserve increase
1896 Get_bonus_reserve_increase;
1897
1898 -- It takes the deprn reserve decrease
1899 Get_bonus_reserve_decrease;
1900 -- 09/08/00 AFERRARA
1901 JGRX_FAREG.var.bonus_variation := JGRX_FAREG.var.bonus_reserve_increase -
1902 JGRX_FAREG.var.bonus_reserve_decrease;
1903
1904 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.bonus_variation =' || JGRX_FAREG.var.bonus_variation);
1905 -- 09/08/00 AFERRARA
1906
1907
1908
1909 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_reserve_value()-');
1910
1911 EXCEPTION
1912 WHEN TOO_MANY_ROWS THEN
1913 X_msg := 'JGRX_FAREG.Get_bonus_reserve_value: TOO_MANY_ROWS';
1914 FA_RX_UTIL_PKG.debug(X_msg);
1915 RAISE_APPLICATION_ERROR(-20010,X_msg);
1916
1917
1918 END Get_bonus_reserve_value;
1919
1920
1921
1922 /*===================================================================+
1923 | Get_bonus_reserve_increase |
1924 +====================================================================*/
1925 PROCEDURE Get_bonus_reserve_increase
1926 IS
1927
1928 X_bonus_deprn NUMBER := 0;
1929 X_reinstatements NUMBER := 0;
1930 X_financ_adjustment_plus NUMBER := 0;
1931 X_tax_re_adjustment_plus NUMBER := 0;
1932
1933 BEGIN
1934 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_reserve_increase()+');
1935
1936 -------------------------------------------------------------------------------
1937 FA_RX_UTIL_PKG.debug('Get bonus depreciation ');
1938
1939 SELECT sum(bonus_deprn_amount)
1940 INTO X_bonus_deprn
1941 FROM fa_deprn_summary
1942 WHERE book_type_code = PARM.p_book_type_code
1943 AND asset_id = JGRX_FAREG.var.asset_id
1944 AND period_counter BETWEEN PARM.p_begin_period_counter
1945 AND PARM.p_end_period_counter;
1946
1947 FA_RX_UTIL_PKG.debug('Bonus depreciation =' || TO_CHAR(X_bonus_deprn));
1948
1949
1950 -------------------------------------------------------------------------------
1951 FA_RX_UTIL_PKG.debug('Get reinstatements ');
1952
1953 SELECT SUM(adjustment_amount)
1954 INTO X_reinstatements
1955 FROM fa_adjustments AD
1956 WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
1957 AND AD.book_type_code = PARM.p_book_type_code
1958 AND AD.asset_id = JGRX_FAREG.var.asset_id
1959 AND AD.adjustment_type = 'BONUS RESERVE'
1960 AND AD.debit_credit_flag = 'CR'
1961 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1962 AND PARM.p_end_period_counter;
1963 FA_RX_UTIL_PKG.debug('Reinstatements =' || TO_CHAR(X_reinstatements));
1964
1965
1966 JGRX_FAREG.var.bonus_reserve_increase := NVL(X_bonus_deprn,0) +
1967 NVL(X_reinstatements,0) ;
1968
1969 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.bonus_reserve_increase =' || JGRX_FAREG.var.bonus_reserve_increase);
1970
1971
1972 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_reserve_increase()-');
1973
1974
1975 END Get_bonus_reserve_increase;
1976
1977
1978 /*===================================================================+
1979 | Get_bonus_reserve_decrease |
1980 +====================================================================*/
1981 PROCEDURE Get_bonus_reserve_decrease
1982 IS
1983
1984 X_retirements NUMBER := 0;
1985 X_financ_adjustment_minus NUMBER := 0;
1986
1987 BEGIN
1988 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_reserve_decrease()+');
1989
1990
1991 -------------------------------------------------------------------------------
1992 FA_RX_UTIL_PKG.debug('Get retirements and revaluations ');
1993 SELECT ABS(SUM(adjustment_amount))
1994 INTO X_retirements
1995 FROM fa_adjustments AD
1996 WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
1997 AND AD.adjustment_type = 'BONUS RESERVE'
1998 AND AD.debit_credit_flag = 'DR'
1999 AND AD.book_type_code = PARM.p_book_type_code
2000 AND AD.asset_id = JGRX_FAREG.var.asset_id
2001 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2002 AND PARM.p_end_period_counter;
2003
2004
2005 FA_RX_UTIL_PKG.debug('Retirements =' || TO_CHAR(X_retirements));
2006
2007
2008
2009 -------------------------------------------------------------------------------
2010 FA_RX_UTIL_PKG.debug('Get Negative financial adjustment ');
2011 SELECT ABS(SUM(adjustment_amount))
2012 INTO X_financ_adjustment_minus
2013 FROM fa_adjustments AD
2014 WHERE AD.source_type_code = 'DEPRECIATION'
2015 AND AD.adjustment_type = 'BONUS EXPENSE'
2016 AND AD.adjustment_amount < 0
2017 AND AD.book_type_code = PARM.p_book_type_code
2018 AND AD.asset_id = JGRX_FAREG.var.asset_id
2019 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2020 AND PARM.p_end_period_counter;
2021 JGRX_FAREG.var.bonus_reserve_increase := JGRX_FAREG.var.bonus_reserve_increase +nvl(X_financ_adjustment_minus,0);
2022
2023 FA_RX_UTIL_PKG.debug('Negative financial adjustment =' || TO_CHAR(X_financ_adjustment_minus));
2024
2025
2026 JGRX_FAREG.var.bonus_reserve_decrease := NVL(X_retirements,0) +
2027 NVL(X_financ_adjustment_minus,0);
2028
2029 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.bonus_reserve_decrease =' || JGRX_FAREG.var.bonus_reserve_decrease);
2030
2031
2032 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_reserve_decrease()-');
2033
2034
2035 END Get_bonus_reserve_decrease;
2036
2037
2038 /*===================================================================+
2042 IS
2039 | Get_fiscal_year_date |
2040 +====================================================================*/
2041 PROCEDURE Get_fiscal_year_date
2043
2044 X_msg VARCHAR2(100);
2045
2046 BEGIN
2047 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_fiscal_year_date()+');
2048
2049 SELECT start_date,
2050 end_date,
2051 deprn_calendar,
2052 prorate_calendar
2053 INTO X_fiscal_year_start_date,
2054 X_fiscal_year_end_date,
2055 X_deprn_calendar,
2056 X_prorate_calendar
2057 FROM fa_fiscal_year FY,
2058 fa_book_controls BC
2059 WHERE FY.fiscal_year = X_fiscal_year
2060 AND FY.fiscal_year_name = BC.fiscal_year_name
2061 AND BC.book_type_code = PARM.p_book_type_code;
2062
2063
2064 FA_RX_UTIL_PKG.debug('X_fiscal_year_start_date =' || TO_CHAR(X_fiscal_year_start_date));
2065 FA_RX_UTIL_PKG.debug('X_fiscal_year_end_date =' || TO_CHAR(X_fiscal_year_end_date));
2066
2067
2068 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_fiscal_year_date()-');
2069
2070 EXCEPTION
2071 WHEN NO_DATA_FOUND THEN
2072 X_msg := 'JGRX_FAREG.Get_fiscal_year_date: NO_DATA_FOUND';
2073 FA_RX_UTIL_PKG.debug(X_msg);
2074 RAISE_APPLICATION_ERROR(-20010,X_msg);
2075
2076 WHEN TOO_MANY_ROWS THEN
2077 X_msg := 'JGRX_FAREG.Get_fiscal_year_date: TOO_MANY_ROWS';
2078 FA_RX_UTIL_PKG.debug(X_msg);
2079 RAISE_APPLICATION_ERROR(-20010,X_msg);
2080
2081 END Get_fiscal_year_date;
2082
2083
2084 /*===================================================================+
2085 | Get_bonus_rate |
2086 +====================================================================*/
2087 PROCEDURE Get_bonus_rate
2088 IS
2089
2090 X_year NUMBER;
2091 X_starting_depreciation_year NUMBER(4);
2092
2093 BEGIN
2094 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_rate()+');
2095
2096 JGRX_FAREG.var.bonus_rate := 0; -- To print 0 instead of NULL
2097
2098 IF JGRX_FAREG.var.bonus_rule IS NOT NULL THEN
2099 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_bonus_rate: JGRX_FAREG.var.bonus_rule IS NOT NULL');
2100
2101 X_starting_depreciation_year := Get_starting_depreciation_year;
2102
2103 X_year := TO_NUMBER(X_fiscal_year) - X_starting_depreciation_year + 1;
2104
2105 SELECT (bonus_rate*100)
2106 INTO JGRX_FAREG.var.bonus_rate
2107 FROM fa_bonus_rates
2108 WHERE bonus_rule = JGRX_FAREG.var.bonus_rule
2109 AND X_year BETWEEN start_year
2110 AND end_year;
2111
2112 END IF;
2113
2114 EXCEPTION
2115 WHEN NO_DATA_FOUND THEN
2116 JGRX_FAREG.var.bonus_rate := 0;
2117
2118 END Get_bonus_rate;
2119
2120
2121 /*===================================================================+
2122 | Get_transactions |
2123 +====================================================================*/
2124 PROCEDURE Get_transactions
2125 IS
2126
2127 X_msg VARCHAR2(100);
2128
2129 BEGIN
2130 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_transactions()+');
2131
2132 Get_addition_transactions;
2133
2134 Get_adjustment_transactions;
2135
2136 Get_retirement_transactions;
2137
2141
2138 Get_revaluation_transactions;
2139
2140 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_transactions()-');
2142 END Get_transactions;
2143
2144
2145 /*===================================================================+
2146 | Get_addition_transactions |
2147 +====================================================================*/
2148 PROCEDURE Get_addition_transactions
2149 IS
2150
2151 CURSOR c_additon_trans IS
2152 SELECT TH.transaction_type_code,
2153 TH.transaction_header_id,
2154 BO.date_placed_in_service,
2155 BO.cost
2156 FROM fa_transaction_headers TH,
2157 fa_books BO
2158 WHERE BO.transaction_header_id_in = TH.transaction_header_id
2159 AND BO.book_type_code = TH.book_type_code
2160 AND BO.asset_id = TH.asset_id
2161 AND TH.transaction_type_code = 'ADDITION'
2162 AND TH.book_type_code = PARM.p_book_type_code
2163 AND TH.asset_id = JGRX_FAREG.var.asset_id
2164 AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
2165 AND PARM.p_end_period_to_date;
2166 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2167 BEGIN
2168 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_addition_transactions()+');
2169 FOR c_addition IN c_additon_trans LOOP
2170 IF (X_first_row = FALSE and c_additon_trans%ROWCOUNT=0) then
2171 exit ;
2172 end if;
2173 IF X_first_row = FALSE THEN -- It is the first time...
2174 JGRX_FAREG.var.transaction_date := c_addition.date_placed_in_service;
2175 JGRX_FAREG.var.transaction_number := c_addition.transaction_header_id;
2176 JGRX_FAREG.var.transaction_code := c_addition.transaction_type_code;
2177 JGRX_FAREG.var.transaction_amount := c_addition.cost;
2178 X_first_row := TRUE;
2179 ELSE -- The current row is alredy used
2180 Insert_transaction(p_transaction_date => c_addition.date_placed_in_service,
2181 p_transaction_number => c_addition.transaction_header_id,
2182 p_transaction_code => c_addition.transaction_type_code,
2183 p_transaction_amount => c_addition.cost);
2184 END IF;
2185
2186 END LOOP;
2187 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_addition_transactions()-');
2188
2189 END Get_addition_transactions;
2190
2191
2192 /*===================================================================+
2193 | Get_adjustment_transactions |
2194 +====================================================================*/
2195 PROCEDURE Get_adjustment_transactions
2196 IS
2197
2198 CURSOR c_adjustment_trans IS
2199 SELECT TH.transaction_type_code,
2200 TH.transaction_header_id,
2201 TH.transaction_date_entered,
2202 decode(debit_credit_flag,'CR',(-1*AD.ADJUSTMENT_AMOUNT),AD.ADJUSTMENT_AMOUNT) ADJUSTMENT_AMOUNT
2203 FROM fa_transaction_headers TH,
2204 fa_ADJUSTMENTS AD
2205 WHERE AD.transaction_header_id = TH.transaction_header_id
2206 AND AD.book_type_code = TH.book_type_code
2207 AND AD.asset_id = TH.asset_id
2208 AND TH.transaction_type_code = 'ADJUSTMENT'
2209 AND AD.source_type_code = TH.transaction_type_code
2210 AND AD.adjustment_type = 'COST'
2211 AND TH.book_type_code = PARM.p_book_type_code
2212 AND TH.asset_id = JGRX_FAREG.var.asset_id
2213 AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
2214 AND PARM.p_end_period_to_date;
2215
2216 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2217
2218 BEGIN
2219 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_adjustment_transactions()+');
2220 FOR c_adjustment IN c_adjustment_trans LOOP
2221 IF (X_first_row = FALSE and c_adjustment_trans%ROWCOUNT=0) then
2222 exit ;
2223 end if;
2224 IF X_first_row = FALSE THEN -- It is the first time...
2225 JGRX_FAREG.var.transaction_date := c_adjustment.transaction_date_entered;
2226 JGRX_FAREG.var.transaction_number := c_adjustment.transaction_header_id;
2227 JGRX_FAREG.var.transaction_code := c_adjustment.transaction_type_code;
2228 JGRX_FAREG.var.transaction_amount := c_adjustment.adjustment_amount;
2229 X_first_row := TRUE;
2230 ELSE -- The current row is alredy used
2231 Insert_transaction(p_transaction_date => c_adjustment.transaction_date_entered,
2232 p_transaction_number => c_adjustment.transaction_header_id,
2233 p_transaction_code => c_adjustment.transaction_type_code,
2234 p_transaction_amount => c_adjustment.adjustment_amount);
2235 END IF;
2236
2237 END LOOP;
2238
2239 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_adjustment_transactions()-');
2240
2241 END Get_adjustment_transactions;
2242
2243
2244
2245 /*===================================================================+
2246 | Get_retirement_transactions |
2247 +====================================================================*/
2248 PROCEDURE Get_retirement_transactions
2249 IS
2250
2251 CURSOR c_retirement_trans IS
2252 SELECT TH.transaction_type_code,
2253 TH.transaction_header_id,
2254 th.transaction_date_entered,
2255 RE.cost_retired
2259 OR RE.transaction_header_id_out = TH.transaction_header_id)
2256 FROM fa_transaction_headers TH,
2257 fa_retirements RE
2258 WHERE (RE.transaction_header_id_in = TH.transaction_header_id
2260 AND RE.book_type_code = TH.book_type_code
2261 AND RE.asset_id = TH.asset_id
2262 AND TH.TRANSACTION_TYPE_CODE IN ('PARTIAL RETIREMENT','FULL RETIREMENT','REINSTATEMENT') -- AND RE.STATUS = 'PROCESSED'
2263 AND TH.book_type_code = PARM.p_book_type_code
2264 AND TH.asset_id = JGRX_FAREG.var.asset_id
2265 AND th.transaction_date_entered BETWEEN X_fiscal_year_start_date
2266 AND PARM.p_end_period_to_date;
2267
2268 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2269
2270 BEGIN
2271 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_retirement_transactions()+');
2272
2273 FOR c_retirement IN c_retirement_trans LOOP
2274 IF (X_first_row = FALSE and c_retirement_trans%ROWCOUNT=0) then
2275 exit ;
2276 end if;
2277 IF X_first_row = FALSE THEN -- It is the first time...
2278 JGRX_FAREG.var.transaction_date := c_retirement.transaction_date_entered;
2279 JGRX_FAREG.var.transaction_number := c_retirement.transaction_header_id;
2280 JGRX_FAREG.var.transaction_code := c_retirement.transaction_type_code;
2281 JGRX_FAREG.var.transaction_amount := c_retirement.cost_retired;
2282 X_first_row := TRUE;
2283 ELSE -- The current row is alredy used
2284 Insert_transaction(p_transaction_date => c_retirement.transaction_date_entered,
2285 p_transaction_number => c_retirement.transaction_header_id,
2286 p_transaction_code => c_retirement.transaction_type_code,
2287 p_transaction_amount => c_retirement.cost_retired);
2288 END IF;
2289
2290 END LOOP;
2291
2292 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_retirement_transactions()-');
2293
2294 END Get_retirement_transactions;
2295
2296 /*===================================================================+
2297 | Get_revaluation_transactions |
2298 +====================================================================*/
2299 PROCEDURE Get_revaluation_transactions
2300 IS
2301
2302 CURSOR c_revaluation_trans IS
2303 SELECT TH.transaction_type_code,
2304 TH.transaction_header_id,
2305 TH.transaction_date_entered,
2306 decode(debit_credit_flag,'CR',(-1*sum(AD.ADJUSTMENT_AMOUNT)),sum(AD.ADJUSTMENT_AMOUNT)) ADJUSTMENT_AMOUNT
2307 -- SUM(AD.adjustment_amount) adjustment_amount
2308 FROM fa_transaction_headers TH,
2309 fa_adjustments AD
2310 WHERE AD.transaction_header_id = TH.transaction_header_id
2311 AND AD.book_type_code = TH.book_type_code
2312 AND AD.asset_id = TH.asset_id
2313 AND AD.adjustment_type = 'COST'
2314 AND TH.TRANSACTION_TYPE_CODE = 'REVALUATION'
2315 AND TH.book_type_code = PARM.p_book_type_code
2316 AND TH.asset_id = JGRX_FAREG.var.asset_id
2317 AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
2318 AND PARM.p_end_period_to_date
2319 GROUP BY TH.transaction_type_code,
2320 TH.transaction_header_id,
2321 TH.transaction_date_entered,debit_credit_flag;
2322
2323 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2324
2325 BEGIN
2326 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation_transactions()+');
2327 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation_transactions()+');
2328 FOR c_revaluation IN c_revaluation_trans LOOP
2329 IF X_first_row = FALSE THEN -- It is the first time...
2330 JGRX_FAREG.var.transaction_date := c_revaluation.transaction_date_entered;
2331 JGRX_FAREG.var.transaction_number := c_revaluation.transaction_header_id;
2332 JGRX_FAREG.var.transaction_code := c_revaluation.transaction_type_code;
2333 JGRX_FAREG.var.transaction_amount := c_revaluation.adjustment_amount;
2334 X_first_row := TRUE;
2335 ELSE -- The current row is alredy used
2336 FA_RX_UTIL_PKG.debug('JGRX_FAREG.rowcount'||to_char(c_revaluation_trans%ROWCOUNT));
2337 Insert_transaction(p_transaction_date => c_revaluation.transaction_date_entered,
2338 p_transaction_number => c_revaluation.transaction_header_id,
2339 p_transaction_code => c_revaluation.transaction_type_code,
2340 p_transaction_amount => c_revaluation.adjustment_amount);
2341 END IF;
2342
2343 END LOOP;
2344
2345 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_revaluation_transactions()-');
2346
2347 END Get_revaluation_transactions;
2348
2349
2350 /*===================================================================+
2351 | Insert_transaction |
2352 +====================================================================*/
2353 PROCEDURE Insert_transaction( p_transaction_date DATE,
2354 p_transaction_number NUMBER,
2355 p_transaction_code VARCHAR2,
2356 p_transaction_amount NUMBER)
2357 IS
2358
2359 BEGIN
2360 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Insert_transaction()+');
2361
2362 INSERT INTO jg_zz_fa_reg_itf(
2363 request_id,
2364 organization_name,
2365 functional_currency_code,
2366 last_update_date,
2367 last_updated_by,
2368 last_update_login,
2369 creation_date,
2370 created_by,
2371 major_category,
2372 minor_category,
2373 deprn_rate,
2374 starting_deprn_year,
2375 date_placed_in_service,
2376 asset_heading,
2377 asset_number,
2378 description,
2379 parent_asset_id,
2380 parent_asset_number,
2381 asset_cost_orig,
2382 bonus_rate,
2383 invoice_number,
2384 supplier_name,
2385 cost_account,
2386 expense_account,
2387 reserve_account,
2388 bonus_deprn_account,
2389 bonus_reserve_account,
2390 asset_cost_initial,
2391 asset_cost_increase,
2392 asset_cost_decrease,
2393 asset_cost_final,
2394 revaluation_initial,
2395 revaluation_increase,
2396 revaluation_decrease,
2397 revaluation_final,
2398 deprn_reserve_initial,
2399 deprn_reserve_increase,
2400 deprn_reserve_decrease,
2401 deprn_reserve_final,
2402 bonus_reserve_initial,
2403 bonus_reserve_increase,
2404 bonus_reserve_decrease,
2405 bonus_reserve_final,
2406 net_book_value_initial,
2407 net_book_value_increase,
2408 net_book_value_decrease,
2409 net_book_value_final,
2410 transaction_date,
2411 transaction_number,
2412 transaction_code,
2413 transaction_amount,
2414 sales_amount,
2415 cost_retired,
2416 deprn_reserve,
2417 bonus_reserve,
2418 net_book_value,
2419 gain_loss,
2420 date_retired,
2421 initial_heading, -- 09/08/00 AFERRARA
2422 variation_heading, -- 09/08/00 AFERRARA
2423 final_heading, -- 09/08/00 AFERRARA
2424 asset_variation, -- 09/08/00 AFERRARA
2425 reval_variation, -- 09/08/00 AFERRARA
2426 deprn_variation, -- 09/08/00 AFERRARA
2427 bonus_variation, -- 09/08/00 AFERRARA
2428 netbo_variation, -- 09/08/00 AFERRARA
2429 revaluation_total -- 09/08/00 AFERRARA
2430 )
2431 VALUES(
2432 X_request_id,
2433 JGRX_FAREG.var.organization_name,
2434 JGRX_FAREG.var.functional_currency_code,
2435 X_last_update_date,
2436 X_last_updated_by,
2437 X_last_update_login,
2438 X_creation_date,
2439 X_created_by,
2440 JGRX_FAREG.var.major_category,
2441 JGRX_FAREG.var.minor_category,
2442 JGRX_FAREG.var.deprn_rate,
2443 JGRX_FAREG.var.starting_deprn_year,
2444 JGRX_FAREG.var.date_placed_in_service,
2445 JGRX_FAREG.var.asset_heading,
2446 JGRX_FAREG.var.asset_number,
2447 JGRX_FAREG.var.description,
2448 JGRX_FAREG.var.parent_asset_id,
2449 JGRX_FAREG.var.parent_asset_number,
2450 JGRX_FAREG.var.asset_cost_orig,
2451 JGRX_FAREG.var.bonus_rate,
2452 JGRX_FAREG.var.invoice_number,
2453 JGRX_FAREG.var.supplier_name,
2454 JGRX_FAREG.var.cost_account,
2455 JGRX_FAREG.var.expense_account,
2456 JGRX_FAREG.var.reserve_account,
2457 JGRX_FAREG.var.bonus_deprn_account,
2458 JGRX_FAREG.var.bonus_reserve_account,
2459 JGRX_FAREG.var.asset_cost_initial,
2460 JGRX_FAREG.var.asset_cost_increase,
2461 JGRX_FAREG.var.asset_cost_decrease,
2462 JGRX_FAREG.var.asset_cost_final,
2463 JGRX_FAREG.var.revaluation_initial,
2464 JGRX_FAREG.var.revaluation_increase,
2465 JGRX_FAREG.var.revaluation_decrease,
2466 JGRX_FAREG.var.revaluation_final,
2467 JGRX_FAREG.var.deprn_reserve_initial,
2468 JGRX_FAREG.var.deprn_reserve_increase,
2469 JGRX_FAREG.var.deprn_reserve_decrease,
2470 JGRX_FAREG.var.deprn_reserve_final,
2471 JGRX_FAREG.var.bonus_reserve_initial,
2472 JGRX_FAREG.var.bonus_reserve_increase,
2473 JGRX_FAREG.var.bonus_reserve_decrease,
2474 JGRX_FAREG.var.bonus_reserve_final,
2475 JGRX_FAREG.var.net_book_value_initial,
2476 JGRX_FAREG.var.net_book_value_increase,
2477 JGRX_FAREG.var.net_book_value_decrease,
2478 JGRX_FAREG.var.net_book_value_final,
2479 p_transaction_date,
2480 p_transaction_number,
2481 p_transaction_code,
2482 p_transaction_amount,
2483 NULL,
2484 NULL,
2485 NULL,
2486 NULL,
2487 NULL,
2488 NULL,
2489 NULL,
2490 JGRX_FAREG.var.initial_heading, -- 09/08/00 AFERRARA
2491 JGRX_FAREG.var.variation_heading, -- 09/08/00 AFERRARA
2492 JGRX_FAREG.var.final_heading, -- 09/08/00 AFERRARA
2493 JGRX_FAREG.var.asset_variation, -- 09/08/00 AFERRARA
2494 JGRX_FAREG.var.reval_variation, -- 09/08/00 AFERRARA
2495 JGRX_FAREG.var.deprn_variation, -- 09/08/00 AFERRARA
2496 JGRX_FAREG.var.bonus_variation, -- 09/08/00 AFERRARA
2497 JGRX_FAREG.var.netbo_variation, -- 09/08/00 AFERRARA
2498 JGRX_FAREG.var.revaluation_total -- 09/08/00 AFERRARA
2499 );
2500
2501
2502 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Insert_transaction()-');
2503
2504 END Insert_transaction;
2505
2506
2507 /*===================================================================+
2508 | Get_Net_Book_Value |
2509 +====================================================================*/
2510 PROCEDURE Get_Net_Book_Value
2511 IS
2512
2513 BEGIN
2514 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_Net_Book_Value()+');
2515
2516 JGRX_FAREG.var.net_book_value_initial := NVL(JGRX_FAREG.var.asset_cost_initial,0) -
2517 NVL(JGRX_FAREG.var.deprn_reserve_initial,0) -
2518 NVL(JGRX_FAREG.var.bonus_reserve_initial,0) ;
2519
2520 JGRX_FAREG.var.net_book_value_increase := NVL(JGRX_FAREG.var.asset_cost_increase,0) +
2521 NVL(JGRX_FAREG.var.deprn_reserve_decrease,0) +
2522 NVL(JGRX_FAREG.var.bonus_reserve_decrease,0) ;
2523
2524
2525
2526 JGRX_FAREG.var.net_book_value_decrease := NVL(JGRX_FAREG.var.asset_cost_decrease,0) +
2527 NVL(JGRX_FAREG.var.deprn_reserve_increase,0) +
2528 NVL(JGRX_FAREG.var.bonus_reserve_increase,0) ;
2529
2530 -- + 09/08/00 AFERRARA
2531 JGRX_FAREG.var.netbo_variation := JGRX_FAREG.var.net_book_value_increase -
2532 JGRX_FAREG.var.net_book_value_decrease;
2533
2534 FA_RX_UTIL_PKG.debug('JGRX_FAREG.var.netbo_variation =' || JGRX_FAREG.var.netbo_variation);
2535 -- - 09/08/00 AFERRARA
2536
2537 JGRX_FAREG.var.net_book_value_final := NVL(JGRX_FAREG.var.asset_cost_final,0) -
2538 NVL(JGRX_FAREG.var.deprn_reserve_final,0) -
2539 NVL(JGRX_FAREG.var.bonus_reserve_final,0) ;
2540
2541 FA_RX_UTIL_PKG.debug('JGRX_FAREG.Get_Net_Book_Value()-');
2542
2543 END Get_Net_Book_Value;
2544
2545
2546 END JGRX_FAREG;