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