[Home] [Help]
PACKAGE BODY: APPS.JG_RX_FAREG
Source
1 PACKAGE BODY JG_RX_FAREG AS
2 /* $Header: jgrxfrb.pls 120.30.12020000.2 2012/08/02 09:05:33 khansen 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
77 X_section_name := 'Get_' || p_type || '_Details';
74 BEGIN
75
76 -- Inizialize Variables
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');
195
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');
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 ---
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');
269 --Added the above part to fix bug 3240485
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;
380
381
382 /*===================================================================+
383 | fa_ASSET_after_fetch |
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()+');
510
511 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.fa_ASSET_after_report()-');
512 END fa_ASSET_after_report;
513
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);
570 FA_RX_UTIL_PKG.assign_column('20', NULL,'asset_heading','JG_RX_FAREG.var.asset_heading','VARCHAR2', 15);
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');
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' ||
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)' ||
609 ' FROM fa_books' ||
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
680 END IF;
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);
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);
719 RAISE_APPLICATION_ERROR(-20010,X_msg);
720 END;
721
722 BEGIN
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 /*===================================================================+
817 IS
814 | Get_category_segment |
815 +====================================================================*/
816 FUNCTION Get_category_segment RETURN VARCHAR2
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(
846 140, -- x_application_id
847 'CAT#', -- x_id_flex_code in
848 101, -- x_id_flex_num in
849 'MINOR_CATEGORY', -- x_seg_attr_type
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));
936
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()-');
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
957 /* bug 14381966 */
958
959 SELECT SUM((DECODE(adjustment_type,'RESERVE',decode(debit_credit_flag,'DR',adjustment_amount),0))
960 -(DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'DR',adjustment_amount),0)))
961 - SUM((DECODE(adjustment_type,'RESERVE',decode(debit_credit_flag,'CR',adjustment_amount),0))
962 -(DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'CR',adjustment_amount),0)))
963 INTO JG_RX_FAREG.var.deprn_reserve
964 FROM fa_adjustments AD
965 WHERE AD.source_type_code in ('RETIREMENT')
966 AND AD.book_type_code = PARM.p_book_type_code
967 AND AD.asset_id = JG_RX_FAREG.var.asset_id
968 --AND AD.debit_credit_flag = 'DR'
969 AND transaction_header_id = JG_RX_FAREG.var.transaction_header_id;
970
971
972 SELECT SUM((DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'DR',adjustment_amount),0))
973 -(DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'CR',adjustment_amount),0)))
974 INTO JG_RX_FAREG.var.bonus_reserve
975 FROM fa_adjustments AD
976 WHERE AD.source_type_code in ('RETIREMENT')
977 AND AD.book_type_code = PARM.p_book_type_code
978 AND AD.asset_id = JG_RX_FAREG.var.asset_id
979 --AND AD.debit_credit_flag = 'DR'
980 AND transaction_header_id = JG_RX_FAREG.var.transaction_header_id;
981
982 END Get_RTRMNT_reserve ;
983
984
985 /*===================================================================+
986 | Get_starting_depreciation_year |
987 +====================================================================*/
988 FUNCTION Get_starting_depreciation_year RETURN NUMBER
989 IS
990
991 X_ret NUMBER(4);
992 X_msg VARCHAR2(100);
993
994 BEGIN
995 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_starting_depreciation_year()+');
996
997 SELECT FY.fiscal_year
998 INTO X_ret
999 FROM fa_fiscal_year FY,
1000 fa_convention_types CT,
1001 fa_books BO
1002 WHERE CT.prorate_convention_code = BO.prorate_convention_code
1003 AND FY.fiscal_year_name = CT.fiscal_year_name
1004 AND BO.date_ineffective IS NULL
1005 AND BO.date_placed_in_service BETWEEN FY.start_date
1006 AND FY.end_date
1007 AND BO.asset_id = JG_RX_FAREG.var.asset_id
1008 AND BO.book_type_code = PARM.p_book_type_code;
1009
1010 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_starting_depreciation_year()-');
1011
1012 RETURN X_ret;
1013
1014 EXCEPTION
1015 WHEN NO_DATA_FOUND THEN
1016 X_msg := 'JG_RX_FAREG.Get_starting_depreciation_year: NO_DATA_FOUND';
1017 FA_RX_UTIL_PKG.debug(X_msg);
1018 RAISE_APPLICATION_ERROR(-20010,X_msg);
1019
1020 WHEN TOO_MANY_ROWS THEN
1021 X_msg := 'JG_RX_FAREG.Get_starting_depreciation_year: TOO_MANY_ROWS';
1022 FA_RX_UTIL_PKG.debug(X_msg);
1023 RAISE_APPLICATION_ERROR(-20010,X_msg);
1024
1025 END Get_starting_depreciation_year;
1026
1027 /*===================================================================+
1028 | Get_depreciation_rate |
1029 +====================================================================*/
1030 PROCEDURE Get_depreciation_rate
1031 IS
1032
1033 X_msg VARCHAR2(100);
1034 X_life_in_years NUMBER;
1035 X_number_per_fiscal_year NUMBER;
1036 X_life_of_asset NUMBER;
1037 X_year_of_life NUMBER;
1038 X_current_fiscal_year NUMBER;
1039 X_prorate_fiscal_year NUMBER;
1040
1041 BEGIN
1042 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_depreciation_rate()+');
1043
1044 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.rate_source_rule = ' || JG_RX_FAREG.var.rate_source_rule);
1045
1046 -- FLAT
1047 IF JG_RX_FAREG.var.rate_source_rule = 'FLAT' THEN
1048 JG_RX_FAREG.var.deprn_rate := JG_RX_FAREG.var.adjusted_rate;
1049 END IF;
1050
1051
1052 -- CALCULATED
1053 IF JG_RX_FAREG.var.rate_source_rule IN ('CALCULATED','TABLE','FORMULA') THEN
1054 SELECT number_per_fiscal_year
1055 INTO X_number_per_fiscal_year
1056 FROM fa_calendar_types
1057 WHERE calendar_type = X_deprn_calendar; -- X_deprn_calendar is retrieved in Get_fiscal_year_date()
1058 IF JG_RX_FAREG.var.rate_source_rule = 'CALCULATED' THEN
1059 X_life_in_years := JG_RX_FAREG.var.life_in_months / 12; -- Bug 4066022
1060 FA_RX_UTIL_PKG.debug('Life in years ='||to_char(X_life_in_years));
1061 JG_RX_FAREG.var.deprn_rate := round(1 / (X_life_in_years * X_number_per_fiscal_year),4);-- Bug 4066022 ,5081410
1062 FA_RX_UTIL_PKG.debug('Method Type Calculate..'||JG_RX_FAREG.var.rate_source_rule);
1063 FA_RX_UTIL_PKG.debug('deprn rate'||to_char(JG_RX_FAREG.var.deprn_rate));
1064 END IF;
1065 --Added this part for Formula Column for bug 2906455
1066 IF JG_RX_FAREG.var.rate_source_rule = 'FORMULA' THEN
1067 X_life_in_years := JG_RX_FAREG.var.life_in_months / X_number_per_fiscal_year;
1068 FA_RX_UTIL_PKG.debug('Life in years ='||to_char(X_life_in_years));
1069 JG_RX_FAREG.var.deprn_rate := 1 / X_life_in_years;
1070 FA_RX_UTIL_PKG.debug('Method Type Formula is....'||JG_RX_FAREG.var.rate_source_rule);
1071 FA_RX_UTIL_PKG.debug('deprn rate'||to_char(JG_RX_FAREG.var.deprn_rate));
1072 END IF;
1073 --Changes finished for bug 2906455
1074 END IF;
1075
1076
1077 --Added this following code to calculate the depriciation rate for FORMULA methods
1078
1079 /* IF JG_RX_FAREG.var.rate_source_rule ='FORMULA' then
1080
1081 select method_id
1082 into X_METHOD_ID1
1083 from fa_methods
1084 where method_code = JG_RX_FAREG.var.deprn_method_code
1085 and nvl (life_in_months, -999) = nvl (JG_RX_FAREG.var.Life_In_Months, -999);
1086
1087 FA_RX_UTIL_PKG.debug('Asset Id '||to_char(JG_RX_FAREG.var.asset_id));
1088 FA_RX_UTIL_PKG.debug('life in months'||to_char(JG_RX_FAREG.var.life_in_months));
1089 FA_RX_UTIL_PKG.debug('Fiscal Year '||to_char(JG_RX_FAREG.var.x_fiscal_year1));
1090 FA_RX_UTIL_PKG.debug('Short Fiscal Year'||JG_RX_FAREG.var.short_fiscal_year_flag);
1091 FA_RX_UTIL_PKG.debug('Method Code'||JG_RX_FAREG.var.deprn_method_code);
1092 FA_RX_UTIL_PKG.debug('Current Period '||to_char(JG_RX_FAREG.var.begin_period_counter1));
1093 FA_RX_UTIL_PKG.debug('Prorate DAte is..'||to_char(JG_RX_FAREG.var.prorate_Date, 'DD/MM/YYYY'));
1094 FA_RX_UTIL_PKG.debug('Conversion DAte is..'||to_char(JG_RX_FAREG.var.conversion_date, 'DD/MM/YYYY'));
1095 FA_RX_UTIL_PKG.debug('Orginal Deprn DAte is..'||to_char(JG_RX_FAREG.var.original_deprn_start_date, 'DD/MM/YYYY'));
1096 FA_RX_UTIL_PKG.debug('deprn rate for Formula before is ..'||to_char(JG_RX_FAREG.var.deprn_rate));
1097
1098 FA_CDE_PKG.faxgfr(X_Book_Type_Code => PARM.p_book_type_code,
1099 X_Asset_Id => JG_RX_FAREG.var.asset_id,
1100 X_Short_Fiscal_Year_Flag => JG_RX_FAREG.var.short_fiscal_year_flag,
1101 X_Conversion_Date => JG_RX_FAREG.var.conversion_date,
1102 X_Prorate_Date => JG_RX_FAREG.var.prorate_date,
1103 X_Orig_Deprn_Start_Date => JG_RX_FAREG.var.original_deprn_start_date,
1104 C_Prorate_Date => NULL,
1105 C_Conversion_Date => NULL,
1106 C_Orig_Deprn_Start_Date => NULL,
1107 X_Method_Code => JG_RX_FAREG.var.deprn_method_code,
1108 X_Life_In_Months => JG_RX_FAREG.var.life_in_months,
1109 X_Fiscal_Year => JG_RX_FAREG.var.x_fiscal_year1,
1110 X_Current_Period => JG_RX_FAREG.var.begin_period_counter1,
1111 X_Rate => X_rate1,
1112 X_Method_Type => method_type,
1113 X_Success => success
1114 );
1115
1116 FA_CDE_PKG.faxgfr(X_Book_Type_Code => PARM.p_book_type_code,
1117 X_Asset_Id => JG_RX_FAREG.var.asset_id,
1118 X_Life_In_Months => JG_RX_FAREG.var.life_in_months,
1119 X_Short_Fiscal_Year_Flag => JG_RX_FAREG.var.short_fiscal_year_flag,
1120 X_Conversion_Date => JG_RX_FAREG.var.conversion_date,
1121 X_Prorate_Date => JG_RX_FAREG.var.prorate_date,
1122 X_Orig_Deprn_Start_Date => JG_RX_FAREG.var.original_deprn_start_date,
1123 C_Prorate_Date => NULL,
1124 C_Conversion_Date => NULL,
1125 C_Orig_Deprn_Start_Date => NULL,
1126 X_Method_Id => X_METHOD_ID1,
1127 X_Fiscal_Year => JG_RX_FAREG.var.x_fiscal_year1,
1128 -- X_Current_Period => JG_RX_FAREG.var.begin_period_counter1,
1129 X_Rate => X_rate1,
1130 X_Method_Type => method_type,
1131 X_Success => success
1132 );
1133
1134 JG_RX_FAREG.var.deprn_rate := X_rate1;
1135 FA_RX_UTIL_PKG.debug('deprn rate for Formula is ..'||to_char(JG_RX_FAREG.var.deprn_rate));
1136 FA_RX_UTIL_PKG.debug('Success Code for Formula is ..'||to_char(success));
1137
1138 END IF;*/
1139 -- Amar added above part to fix bug 2906455.
1140 -- TABLE
1141 IF JG_RX_FAREG.var.rate_source_rule = 'TABLE' THEN
1142 -- SELECT (ROUND(MONTHS_BETWEEN(PARM.p_end_period_to_date,JG_RX_FAREG.var.date_placed_in_service)/
1143 -- SELECT (ROUND(JG_RX_FAREG.var.life_in_months/ -- bug 4665510
1144 -- X_number_per_fiscal_year)) -- +1 -- bug 5364830
1145
1146 SELECT ROUND(JG_RX_FAREG.var.life_in_months/12)
1147 INTO X_life_of_asset
1148 FROM dual;
1149
1150 FA_RX_UTIL_PKG.debug('Life of the asset= ' || to_char(x_life_of_asset));
1151 FA_RX_UTIL_PKG.debug('deprn calendar = ' || x_deprn_calendar);
1152 FA_RX_UTIL_PKG.debug('start date = ' || JG_RX_FAREG.var.prorate_date);
1153 FA_RX_UTIL_PKG.debug(' date placed in service = ' || JG_RX_FAREG.var.date_placed_in_service);
1154 FA_RX_UTIL_PKG.debug('number per fiscal year= ' || to_char(x_number_per_fiscal_year));
1155 FA_RX_UTIL_PKG.debug('METHOD ID= ' || to_char(JG_RX_FAREG.var.method_id));
1156
1157 -- Fix for Bug #5384045. To find the correct rate, we need to use
1158 -- the current year of the asset's life rather than the entire life
1159 -- of the asset (X_life_of_asset) when joining to fa_rates.
1160 SELECT fiscal_year
1161 INTO X_current_fiscal_year
1162 FROM fa_deprn_periods
1163 WHERE book_type_code = PARM.p_book_type_code
1164 AND period_name = PARM.p_period_from;
1165
1166 SELECT fy.fiscal_year
1167 INTO X_prorate_fiscal_year
1168 FROM fa_calendar_periods cp,
1169 fa_fiscal_year fy,
1170 fa_book_controls bc
1171 WHERE fy.fiscal_year_name = bc.fiscal_year_name
1172 AND cp.calendar_type = bc.prorate_calendar
1173 AND bc.book_type_code = PARM.p_book_type_code
1174 AND JG_RX_FAREG.var.prorate_date between
1175 fy.start_date and fy.end_date
1176 AND cp.start_date between fy.start_date and fy.end_date
1177 AND cp.end_date between fy.start_date and fy.end_date
1178 AND JG_RX_FAREG.var.prorate_date between
1179 cp.start_date and cp.end_date;
1180
1181 X_year_of_life := X_current_fiscal_year - X_prorate_fiscal_year + 1;
1182
1183 IF x_life_of_asset > X_year_of_life THEN
1184
1185 SELECT rate
1186 INTO JG_RX_FAREG.var.deprn_rate
1187 FROM fa_rates
1188 WHERE method_id = JG_RX_FAREG.var.method_id
1189 AND year = X_year_of_life
1190 AND period_placed_in_service = (SELECT period_num
1191 FROM fa_calendar_periods
1192 WHERE calendar_type = X_deprn_calendar -- X_deprn_calendar is retrieved in Get_fiscal_year_date()
1193 AND JG_RX_FAREG.var.prorate_date BETWEEN start_date -- X_prorate_date is retrieved in Get_fiscal_year_date()
1194 AND end_date);
1195 ELSE
1196 JG_RX_FAREG.var.deprn_rate := 0;
1197 END IF;
1198
1199 END IF;
1200 IF JG_RX_FAREG.var.rate_source_rule IN ('CALCULATED','FLAT','TABLE', 'FORMULA') THEN
1201
1202 JG_RX_FAREG.var.deprn_rate := JG_RX_FAREG.var.deprn_rate *100;
1203 END IF;
1204
1205 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.deprn_rate =' || TO_CHAR(JG_RX_FAREG.var.deprn_rate));
1206
1207 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_depreciation_rate()-');
1208
1209 EXCEPTION
1210 WHEN NO_DATA_FOUND THEN
1211 X_msg := 'JG_RX_FAREG.Get_depreciation_rate: NO_DATA_FOUND';
1212 FA_RX_UTIL_PKG.debug(X_msg);
1213 RAISE_APPLICATION_ERROR(-20010,X_msg);
1214
1215 WHEN TOO_MANY_ROWS THEN
1216 X_msg := 'JG_RX_FAREG.Get_depreciation_rate: TOO_MANY_ROWS';
1217 FA_RX_UTIL_PKG.debug(X_msg);
1218 RAISE_APPLICATION_ERROR(-20010,X_msg);
1219
1220 END Get_depreciation_rate;
1221
1222
1223 /*===================================================================+
1224 | Get_Deprn_Accounts |
1225 +====================================================================*/
1226
1227 PROCEDURE Get_Deprn_Accounts
1228 IS
1229 X_msg VARCHAR2(100);
1230 V_CURSORiD iNTEGER;
1231 v_Selectstmnt varchar2(20000);
1232 v_Selectstmnt1 varchar2(10000);
1233 v_selectstmnt2 varchar2(10000);
1234 v_selectstmnt3 varchar2(10000);
1235 V_expense_account varchar2(100);
1236 v_dummy integer ;
1237 BEGIN
1238 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_Deprn_Account()+');
1239 JG_RX_FAREG.var.expense_account := NULL;
1240 v_cursorId := DBMS_SQL.OPEN_CURSOR;
1241 v_Selectstmnt1 :=
1242 'select distinct '||X_account_segment||' from '||
1243 ' fa_distribution_history dih,'||
1244 ' gl_code_combinations gcc'||
1245 ' where dih.asset_id ='||JG_RX_FAREG.var.asset_id || 'and '||
1246 ' dih.book_type_code = '||''''||PARM.p_book_type_code ||''''||' and'||
1247 ' gcc.code_combination_id = dih.code_combination_id'||
1248 ' and dih.transaction_header_id_in =';
1249 v_selectstmnt2 := '(select to_char(MAX(transaction_heaDer_id))'||
1250 ' from fa_transaction_headers trh,'||
1251 ' fa_distribution_history dih1'||
1252 ' where dih1.asset_id= dih.asset_id and'||
1253 ' dih1.book_type_code =dih.book_type_code and'||
1254 ' dih1.transaction_header_id_in = trh.transaction_header_id and';
1255 v_selectstmnt3 := ' transaction_date_entered <= '||''''||PARM.p_end_period_to_date||''''||')';
1256
1257 DBMS_SQL.PARSE(V_cursorId,v_selectstmnt1||v_selectstmnt2||v_selectstmnt3,DBMS_SQL.V7);
1258 DBMS_SQL.DEFINE_COLUMN(V_cursorId,1,V_expense_account,100);
1259 V_DUMMY := DBMS_SQL.EXECUTE(V_cursorId);
1260 LOOP
1261 IF DBMS_SQL.FETCH_ROWS(V_cursorId) = 0 THEN
1262 EXIT;
1263 END IF;
1264 DBMS_SQL.COLUMN_VALUE(V_cursorId,1,V_expense_account);
1265 JG_RX_FAREG.var.expense_account:= JG_RX_FAREG.var.expense_account||V_expense_account;
1266 END LOOP;
1267 dbms_sql.close_cursor(v_cursorId);
1268 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.expense_account = ' || JG_RX_FAREG.var.expense_account);
1269 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_depr_account()-');
1270 END Get_deprn_accounts;
1271
1272
1273
1277 +====================================================================*/
1274
1275 /*===================================================================+
1276 | Get_Invoice Number and Supplier Name |
1278
1279 PROCEDURE Get_invoice_number
1280 IS
1281 X_msg VARCHAR2(100);
1282 CURSOR c_invoice_supplier IS
1283 select substr(invoice_number,1,79) ||' ' invoice_number,
1284 substr(vendor_name,1,79) ||' ' vendor_name
1285 from fa_asset_invoices ai,po_vendors ve,fa_invoice_transactions IT,
1286 fa_book_controls fabc
1287 where ai.po_vendor_id= ve.vendor_id and
1288 ai.asset_id = JG_RX_FAREG.var.asset_id and
1289 ai.invoice_transaction_id_in = IT.invoice_transaction_id and
1290 -- IT.book_type_code = PARM.p_book_type_code;
1291 IT.book_type_code = fabc.DISTRIBUTION_SOURCE_BOOK and
1292 fabc.book_type_code = PARM.p_book_type_code;
1293 BEGIN
1294 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_inv_number()+');
1295 JG_RX_FAREG.var.invoice_number := null;
1296 JG_RX_FAREG.var.supplier_name := null;
1297 FOR c_inv in c_invoice_supplier LOOP
1298 IF c_invoice_supplier%ROWCOUNT=0 then
1299 exit;
1300 END IF;
1301 ---bug 4363678
1302 JG_RX_FAREG.var.supplier_name := JG_RX_FAREG.var.supplier_name ||
1303 substr(c_inv.vendor_name,1,80-nvl(length(JG_RX_FAREG.var.supplier_name),0));
1304 JG_RX_FAREG.var.invoice_number := JG_RX_FAREG.var.invoice_number||
1305 substr(c_inv.invoice_number,1,50-nvl(length(JG_RX_FAREG.var.invoice_number),0));
1306
1307 END LOOP;
1308 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.invoice_number = ' || JG_RX_FAREG.var.invoice_number);
1309 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.supplier_name = ' || JG_RX_FAREG.var.supplier_name);
1310 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_inv_number()-');
1311 END Get_invoice_number;
1312
1313
1314
1315
1316 /*===================================================================+
1317 | Get_parent_asset_number |
1318 +====================================================================*/
1319 FUNCTION Get_parent_asset_number RETURN VARCHAR2
1320 IS
1321
1322 X_msg VARCHAR2(100);
1323 X_parent_asset_number VARCHAR2(15);
1324
1325 BEGIN
1326 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_parent_asset_number()+');
1327
1328 SELECT asset_number
1329 INTO X_parent_asset_number
1330 FROM fa_additions
1331 WHERE asset_id = JG_RX_FAREG.var.parent_asset_id;
1332
1333 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_parent_asset_number()-');
1334
1335 RETURN X_parent_asset_number;
1336
1337 EXCEPTION
1338 WHEN NO_DATA_FOUND THEN
1339 X_msg := 'JG_RX_FAREG.Get_parent_asset_number: NO_DATA_FOUND';
1340 FA_RX_UTIL_PKG.debug(X_msg);
1341 RAISE_APPLICATION_ERROR(-20010,X_msg);
1342
1343 END Get_parent_asset_number;
1344
1345
1346 /*===================================================================+
1347 | Startup |
1348 +====================================================================*/
1349 PROCEDURE Startup
1350 IS
1351
1352 X_msg VARCHAR2(100);
1353
1354 BEGIN
1355 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Startup()+');
1356
1357 -- It takes the segment condition
1358 X_where_clause_tmp := Get_category_segment;
1359
1360 FA_RX_UTIL_PKG.debug('Get Company name');
1361 -- Get Company name and store in placeholder variable
1362 SELECT company_name
1363 INTO JG_RX_FAREG.var.organization_name
1364 FROM fa_system_controls;
1365
1366
1367 FA_RX_UTIL_PKG.debug('Get currency code');
1368 -- Get currency code and store in placeholder variable
1369 SELECT currency_code
1370 INTO JG_RX_FAREG.var.functional_currency_code
1371 FROM gl_sets_of_books
1372 WHERE set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
1373
1374 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Startup()-');
1375
1376 EXCEPTION
1377 WHEN NO_DATA_FOUND THEN
1378 X_msg := 'JG_RX_FAREG.Startup: NO_DATA_FOUND';
1379 FA_RX_UTIL_PKG.debug(X_msg);
1380 RAISE_APPLICATION_ERROR(-20010,X_msg);
1381
1382 WHEN TOO_MANY_ROWS THEN
1383 X_msg := 'JG_RX_FAREG.Startup: TOO_MANY_ROWS';
1384 FA_RX_UTIL_PKG.debug(X_msg);
1385 RAISE_APPLICATION_ERROR(-20010,X_msg);
1386
1387 END Startup;
1388
1389
1390 /*===================================================================+
1391 | Get_cost_value |
1392 +====================================================================*/
1393 PROCEDURE Get_cost_value
1394 IS
1395
1396 X_msg VARCHAR2(100);
1397 X_capitalized NUMBER(10);
1398
1399 BEGIN
1400 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_cost_value()+');
1401
1402 BEGIN
1403 FA_RX_UTIL_PKG.debug('Get cost initial value');
1404 -- Get initial value
1405 SELECT count(*)
1406 INTO X_capitalized
1407 FROM fa_books BO
1408 WHERE BO.book_type_code = PARM.p_book_type_code
1409 AND BO.asset_id = JG_RX_FAREG.var.asset_id
1410 AND BO.period_counter_capitalized BETWEEN PARM.p_begin_period_counter AND
1411 PARM.p_end_period_counter;
1412 IF X_capitalized > 0 THEN
1413
1417 transaction_header_id_in
1414 JG_RX_FAREG.var.asset_cost_initial := 0;
1415 ELSE
1416 SELECT cost,
1418 INTO JG_RX_FAREG.var.asset_cost_initial,
1419 X_transaction_id_initial
1420 FROM fa_books BO
1421 WHERE BO.book_type_code = PARM.p_book_type_code
1422 AND BO.asset_id = JG_RX_FAREG.var.asset_id
1423 AND (TO_CHAR(BO.date_effective, 'DD-MON-YYYY HH:MI:SS'),transaction_header_id_in) =
1424 (SELECT TO_CHAR(MAX(BO1.date_effective), 'DD-MON-YYYY HH:MI:SS'),
1425 max(transaction_header_id_in)
1426 FROM fa_books BO1, fa_transaction_headers TRH
1427 WHERE BO1.book_type_code = BO.book_type_code
1428 AND BO1.asset_id = BO.asset_id
1429 AND TRH.transaction_header_id= BO1.transaction_header_id_in
1430 AND TRH.transaction_date_entered < PARM.p_begin_period_from_date);
1431 END IF;
1432 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.asset_cost_initial =' || TO_CHAR(JG_RX_FAREG.var.asset_cost_initial));
1433
1434 EXCEPTION
1435 WHEN NO_DATA_FOUND THEN
1436 JG_RX_FAREG.var.asset_cost_initial := 0;
1437 END;
1438
1439 FA_RX_UTIL_PKG.debug('Get cost final value');
1440 -- Get final value
1441 BEGIN
1442 SELECT cost,
1443 transaction_header_id_in
1444 INTO JG_RX_FAREG.var.asset_cost_final,
1445 X_transaction_id_final
1446 FROM fa_books BO
1447 WHERE BO.book_type_code = PARM.p_book_type_code
1448 AND BO.asset_id = JG_RX_FAREG.var.asset_id
1449 AND (TO_CHAR(BO.date_effective, 'DD-MON-YYYY HH:MI:SS'),transaction_header_id_in) =
1450 (SELECT TO_CHAR(MAX(BO1.date_effective), 'DD-MON-YYYY HH:MI:SS'),
1451 max(transaction_header_id_in)
1452 FROM fa_books BO1,FA_TRANSACTION_HEADERS TRH
1453 WHERE BO1.book_type_code = BO.book_type_code
1454 AND BO1.asset_id = BO.asset_id
1455 AND TRH.transaction_header_id= BO1.transaction_header_id_in
1456 AND TRH.transaction_date_entered <= PARM.p_end_period_to_date);
1457 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.asset_cost_final =' || TO_CHAR(JG_RX_FAREG.var.asset_cost_final));
1458 EXCEPTION
1459 WHEN NO_DATA_FOUND THEN
1460 JG_RX_FAREG.var.asset_cost_final := 0;
1461 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.asset_cost_final =' ||'no data found');
1462
1463 END;
1464
1465 -- It takes the asset cost increase
1466 Get_cost_increase;
1467
1468 -- It takes the asset cost increase
1469 Get_cost_decrease;
1470 -- 09/08/00 AFERRARA
1471 JG_RX_FAREG.var.asset_variation := JG_RX_FAREG.var.asset_cost_increase -
1472 JG_RX_FAREG.var.asset_cost_decrease;
1473 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.revaluation_total(before) =' || JG_RX_FAREG.var.revaluation_total);
1477 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.asset_variation =' || JG_RX_FAREG.var.asset_variation);
1474 JG_RX_FAREG.var.revaluation_total := JG_RX_FAREG.var.revaluation_final;
1475 -- JG_RX_FAREG.var.asset_cost_final -
1476 -- JG_RX_FAREG.var.asset_cost_orig;
1478 -- 09/08/00 AFERRARA
1479
1480 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_cost_value()-');
1481
1482 EXCEPTION
1483 WHEN NO_DATA_FOUND THEN
1484 X_msg := 'JG_RX_FAREG.Get_cost_value: NO_DATA_FOUND';
1485 FA_RX_UTIL_PKG.debug(X_msg);
1486 RAISE_APPLICATION_ERROR(-20010,X_msg);
1487
1488 WHEN TOO_MANY_ROWS THEN
1489 X_msg := 'JG_RX_FAREG.Get_cost_value: TOO_MANY_ROWS';
1490 FA_RX_UTIL_PKG.debug(X_msg);
1491 RAISE_APPLICATION_ERROR(-20010,X_msg);
1492
1493 END Get_cost_value;
1494
1495
1496 /*===================================================================+
1497 | Get_cost_increase |
1498 +====================================================================*/
1499 PROCEDURE Get_cost_increase
1500 IS
1501
1502 X_partial_addition NUMBER := 0;
1503 X_manual_adjustment_plus NUMBER := 0;
1504 X_revaluation_plus NUMBER := 0;
1505 X_reinstatements NUMBER := 0;
1506
1507 BEGIN
1511 FA_RX_UTIL_PKG.debug('Get partial addition ');
1508 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_cost_increase()+');
1509
1510 -------------------------------------------------------------------------------
1512 fa_rx_util_pkg.debug('transaction id'||X_transaction_id_initial);
1513 fa_rx_util_pkg.debug('PARM.p_begin_period_to_date'||to_char(PARM.p_begin_period_to_date));
1514 fa_rx_util_pkg.debug('PARM.p_end_period_from_date'||to_char(PARM.p_end_period_from_date));
1515
1516 SELECT SUM(AD.adjustment_amount)
1517 INTO X_partial_addition
1518 FROM fa_books BO,
1519 fa_transaction_headers TH,
1520 fa_adjustments AD
1521 WHERE BO.book_type_code = PARM.p_book_type_code
1522 AND BO.asset_id = JG_RX_FAREG.var.asset_id
1523 AND BO.transaction_header_id_in <> NVL(X_transaction_id_initial, 0)
1524 AND BO.book_type_code = TH.book_type_code
1525 AND BO.asset_id = TH.asset_id
1526 AND BO.transaction_header_id_in = TH.transaction_header_id
1527 AND TH.transaction_type_code = 'ADDITION'
1528 AND AD.transaction_header_id = TH.transaction_header_id
1529 AND AD.source_type_code = TH.TRANSACTION_TYPE_CODE
1530 AND AD.book_type_code = TH.book_type_code
1531 AND AD.asset_id = TH.asset_id
1532 AND AD.adjustment_type = 'COST'
1533 AND AD.debit_credit_flag = 'DR'
1534 AND AD.adjustment_amount > 0
1535 AND TH.transaction_date_entered BETWEEN (PARM.p_begin_period_from_date)
1536 AND (PARM.p_end_period_to_date);
1537 -- changed bo.date_effective to TH.transaction_date_entered
1538
1539 FA_RX_UTIL_PKG.debug('partial addition =' || TO_CHAR(X_partial_addition));
1540
1541
1542 -------------------------------------------------------------------------------
1543 FA_RX_UTIL_PKG.debug('Get manual adjustment upwards');
1544
1545 SELECT SUM(AD.adjustment_amount)
1546 INTO X_manual_adjustment_plus
1547 FROM fa_transaction_headers TH,
1548 fa_adjustments AD
1549 WHERE AD.transaction_header_id = TH.transaction_header_id
1550 AND AD.book_type_code = TH.book_type_code
1551 AND AD.asset_id = TH.asset_id
1552 AND TH.transaction_type_code = 'ADJUSTMENT'
1553 AND TH.book_type_code = PARM.p_book_type_code
1554 AND TH.asset_id = JG_RX_FAREG.var.asset_id
1555 AND AD.adjustment_type = 'COST'
1556 AND AD.debit_credit_flag = 'DR'
1557 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1558 AND PARM.p_end_period_to_date;
1559
1560 FA_RX_UTIL_PKG.debug('Get manual adjustment upwards =' || TO_CHAR(X_manual_adjustment_plus));
1561
1562
1563 -------------------------------------------------------------------------------
1564 FA_RX_UTIL_PKG.debug('Get revaluations upward ');
1565
1566 SELECT SUM(AD.adjustment_amount)
1567 INTO X_revaluation_plus
1568 FROM fa_transaction_headers TH,
1569 fa_adjustments AD
1570 WHERE AD.transaction_header_id = TH.transaction_header_id
1571 AND AD.book_type_code = TH.book_type_code
1572 AND AD.asset_id = TH.asset_id
1573 AND TH.transaction_type_code = 'REVALUATION'
1574 AND TH.book_type_code = PARM.p_book_type_code
1575 AND TH.asset_id = JG_RX_FAREG.var.asset_id
1576 AND AD.adjustment_type = 'COST'
1577 AND AD.debit_credit_flag = 'DR'
1578 AND AD.adjustment_amount > 0
1579 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1580 AND PARM.p_end_period_to_date;
1581
1582 FA_RX_UTIL_PKG.debug('Revaluations Upward =' || TO_CHAR(X_revaluation_plus));
1583
1584
1585 -------------------------------------------------------------------------------
1586 FA_RX_UTIL_PKG.debug('Get reinstatements ');
1587
1588 SELECT SUM(RE.cost_retired)
1589 INTO X_reinstatements
1590 FROM fa_transaction_headers TH,
1591 fa_retirements RE
1592 WHERE RE.transaction_header_id_out = TH.transaction_header_id
1593 AND RE.book_type_code = TH.book_type_code
1594 AND RE.asset_id = TH.asset_id
1595 AND TH.transaction_type_code = 'REINSTATEMENT'
1596 AND TH.book_type_code = PARM.p_book_type_code
1597 AND TH.asset_id = JG_RX_FAREG.var.asset_id
1598 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1599 AND PARM.p_end_period_to_date;
1600 -- jmary changing date_retired to transaction_date
1601 FA_RX_UTIL_PKG.debug('Reinstatements =' || TO_CHAR(X_reinstatements));
1602
1603
1604
1605 JG_RX_FAREG.var.asset_cost_increase := NVL(X_partial_addition,0) +
1606 NVL(X_manual_adjustment_plus,0) +
1607 NVL(X_revaluation_plus,0) +
1608 NVL(X_reinstatements,0);
1609
1610 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.asset_cost_increase =' || JG_RX_FAREG.var.asset_cost_increase);
1611
1612 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_cost_increase()-');
1613
1614
1615 END Get_cost_increase;
1616
1617
1618
1619 /*===================================================================+
1620 | Get_cost_decrease |
1624
1621 +====================================================================*/
1622 PROCEDURE Get_cost_decrease
1623 IS
1625 X_credit_memos NUMBER := 0;
1626 X_manual_adjustment_minus NUMBER := 0;
1627 X_revaluation_minus NUMBER := 0;
1628 X_retirements NUMBER := 0;
1629
1630 BEGIN
1631 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_cost_decrease()+');
1632
1633
1634 -------------------------------------------------------------------------------
1635 FA_RX_UTIL_PKG.debug('Get credit memos ');
1636
1637 SELECT SUM(AD.adjustment_amount)
1638 INTO X_credit_memos
1639 FROM fa_books BO,
1640 fa_transaction_headers TH,
1641 fa_adjustments AD
1642 WHERE BO.book_type_code = PARM.p_book_type_code
1643 AND BO.asset_id = JG_RX_FAREG.var.asset_id
1644 AND BO.transaction_header_id_in <> NVL(X_transaction_id_initial, 0)
1645 AND BO.book_type_code = TH.book_type_code
1646 AND BO.asset_id = TH.asset_id
1647 AND BO.transaction_header_id_in = TH.transaction_header_id
1648 AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
1649 AND AD.transaction_header_id = TH.transaction_header_id
1650 AND AD.source_type_code = TH.TRANSACTION_TYPE_CODE
1651 AND AD.book_type_code = TH.book_type_code
1652 AND AD.asset_id = TH.asset_id
1653 AND AD.adjustment_type = 'COST'
1654 AND AD.debit_credit_flag = 'CR'
1655 AND AD.adjustment_amount < 0
1656 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1657 AND PARM.p_end_period_to_date;
1658 -- changed date_effective to transaction_date_entered
1659
1660 FA_RX_UTIL_PKG.debug('credit memos =' || TO_CHAR(X_credit_memos));
1661
1662
1663 -------------------------------------------------------------------------------
1664 FA_RX_UTIL_PKG.debug('Get manual adjustment downwards');
1665
1666 SELECT SUM(AD.adjustment_amount)
1667 INTO X_manual_adjustment_minus
1668 FROM fa_transaction_headers TH,
1669 fa_adjustments AD
1670 WHERE AD.transaction_header_id = TH.transaction_header_id
1671 AND AD.book_type_code = TH.book_type_code
1672 AND AD.asset_id = TH.asset_id
1673 AND TH.transaction_type_code = 'ADJUSTMENT'
1674 AND TH.book_type_code = PARM.p_book_type_code
1675 AND TH.asset_id = JG_RX_FAREG.var.asset_id
1676 AND AD.adjustment_type = 'COST'
1677 AND AD.debit_credit_flag = 'CR'
1678 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1679 AND PARM.p_end_period_to_date;
1680
1681 FA_RX_UTIL_PKG.debug('Get manual adjustment downwards =' || TO_CHAR(X_manual_adjustment_minus));
1682
1683
1684 -------------------------------------------------------------------------------
1685 FA_RX_UTIL_PKG.debug('Get revaluations downward ');
1686
1687 SELECT SUM(AD.adjustment_amount)
1688 INTO X_revaluation_minus
1689 FROM fa_transaction_headers TH,
1690 fa_adjustments AD
1691 WHERE AD.transaction_header_id = TH.transaction_header_id
1692 AND AD.book_type_code = TH.book_type_code
1693 AND AD.asset_id = TH.asset_id
1694 AND TH.transaction_type_code = 'REVALUATION'
1695 AND TH.book_type_code = PARM.p_book_type_code
1696 AND TH.asset_id = JG_RX_FAREG.var.asset_id
1697 AND AD.adjustment_type = 'COST'
1698 AND AD.debit_credit_flag = 'CR'
1699 AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
1700 AND PARM.p_end_period_to_date;
1701
1702 FA_RX_UTIL_PKG.debug('Revaluations downward =' || TO_CHAR(X_revaluation_minus));
1703
1704
1705 -------------------------------------------------------------------------------
1706 FA_RX_UTIL_PKG.debug('Get retirements ');
1707
1708 SELECT SUM(RE.cost_retired)
1709 INTO X_retirements
1710 FROM fa_transaction_headers TH,
1711 fa_retirements RE
1712 WHERE RE.transaction_header_id_in = TH.transaction_header_id
1713 AND RE.book_type_code = TH.book_type_code
1714 AND RE.asset_id = TH.asset_id
1715 AND (TH.TRANSACTION_TYPE_CODE = 'PARTIAL RETIREMENT' OR
1716 TH.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT' )
1717 AND TH.book_type_code = PARM.p_book_type_code
1718 AND TH.asset_id = JG_RX_FAREG.var.asset_id
1719 AND RE.date_retired BETWEEN PARM.p_begin_period_from_date /* changed Transaction Date to Retirement Date */
1720 AND PARM.p_end_period_to_date;
1721
1722 FA_RX_UTIL_PKG.debug('Retirements =' || TO_CHAR(X_retirements));
1723
1724
1725
1726 JG_RX_FAREG.var.asset_cost_decrease := NVL(X_credit_memos,0) +
1727 NVL(X_manual_adjustment_minus,0) +
1728 NVL(X_revaluation_minus,0) +
1729 NVL(X_retirements,0);
1730
1731 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.asset_cost_decrease =' || JG_RX_FAREG.var.asset_cost_decrease);
1732
1736 END Get_cost_decrease;
1733 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_cost_decrease()-');
1734
1735
1737
1738
1739 /*===================================================================+
1740 | Get_revaluation |
1741 +====================================================================*/
1742 PROCEDURE Get_revaluation
1743 IS
1744
1745 BEGIN
1746 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation()+');
1747
1748 BEGIN
1749 FA_RX_UTIL_PKG.debug('Get_revaluation initial value');
1750 -- Get initial value
1754 FROM fa_adjustments
1751 SELECT SUM(DECODE(debit_credit_flag, 'DR', adjustment_amount, 0)) -
1752 SUM(DECODE(debit_credit_flag, 'CR', adjustment_amount, 0))
1753 INTO JG_RX_FAREG.var.revaluation_initial
1755 WHERE book_type_code = PARM.p_book_type_code
1756 AND asset_id = JG_RX_FAREG.var.asset_id
1757 AND source_type_code = 'REVALUATION'
1758 AND adjustment_type = 'COST'
1759 AND period_counter_adjusted <= (PARM.p_begin_period_counter -1);
1760
1761 IF JG_RX_FAREG.var.revaluation_initial IS NULL THEN
1762 JG_RX_FAREG.var.revaluation_initial := 0;
1763 END IF;
1764
1765 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.revaluation_initial =' || TO_CHAR(JG_RX_FAREG.var.revaluation_initial));
1766 END;
1767
1768
1769 BEGIN
1770 FA_RX_UTIL_PKG.debug('Get_revaluation final value');
1771 -- Get final value
1772 SELECT SUM(DECODE(debit_credit_flag, 'DR', adjustment_amount, 0)) -
1773 SUM(DECODE(debit_credit_flag, 'CR', adjustment_amount, 0))
1774 INTO JG_RX_FAREG.var.revaluation_final
1775 FROM fa_adjustments
1776 WHERE book_type_code = PARM.p_book_type_code
1777 AND asset_id = JG_RX_FAREG.var.asset_id
1778 AND source_type_code = 'REVALUATION'
1779 AND adjustment_type = 'COST'
1780 AND period_counter_adjusted <= PARM.p_end_period_counter;
1781
1782 IF JG_RX_FAREG.var.revaluation_final IS NULL THEN
1783 JG_RX_FAREG.var.revaluation_final := 0;
1784 END IF;
1785
1786
1787 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.revaluation_final =' || TO_CHAR(JG_RX_FAREG.var.revaluation_final));
1788 END;
1789
1790 -- It takes the asset revaluation increase
1791 Get_revaluation_change;
1792
1793 -- 09/08/00 AFERRARA
1794 JG_RX_FAREG.var.reval_variation := JG_RX_FAREG.var.revaluation_increase -
1795 JG_RX_FAREG.var.revaluation_decrease;
1796
1797 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.reval_variation =' || JG_RX_FAREG.var.reval_variation);
1798 -- 09/08/00 AFERRARA
1799
1800 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation()-');
1801
1802 END Get_revaluation;
1803
1804 /*===================================================================+
1805 | Get_revaluation_change |
1806 +====================================================================*/
1807 PROCEDURE Get_revaluation_change
1808 IS
1809
1810 BEGIN
1811 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation_change()+');
1812
1813 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',AD.adjustment_amount,0)),
1814 SUM(DECODE(DEBIT_CREDIT_FLAG,'CR',AD.adjustment_amount,0))
1815 INTO JG_RX_FAREG.var.revaluation_increase,JG_RX_FAREG.var.revaluation_decrease
1816 FROM fa_adjustments AD
1817 WHERE AD.book_type_code = PARM.p_book_type_code
1818 AND AD.asset_id = JG_RX_FAREG.var.asset_id
1819 AND AD.source_type_code = 'REVALUATION'
1820 AND AD.adjustment_type = 'COST'
1821 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1822 AND PARM.p_end_period_counter ;
1823 IF JG_RX_FAREG.var.revaluation_increase IS NULL THEN
1824 JG_RX_FAREG.var.revaluation_increase := 0;
1825 END IF;
1826 IF JG_RX_FAREG.var.revaluation_decrease IS NULL THEN
1827 JG_RX_FAREG.var.revaluation_decrease := 0;
1828 END IF;
1829
1830 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.revaluation_increase =' || JG_RX_FAREG.var.revaluation_increase);
1831 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.revaluation_decrease =' || JG_RX_FAREG.var.revaluation_decrease);
1832
1833 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation_change()-');
1834
1835
1836 END Get_revaluation_change;
1837
1838 /*===================================================================+
1842 IS
1839 | Get_depr_reserve_value |
1840 +====================================================================*/
1841 PROCEDURE Get_deprn_reserve_value
1843
1844 X_msg VARCHAR2(100);
1845
1846 BEGIN
1847 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_deprn_reserve_value()+');
1848
1849 -------------------------------------------------------------------------------
1850 BEGIN
1851 FA_RX_UTIL_PKG.debug('Get deprn reserve initial value');
1852 -- Get initial value
1853
1854 SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0))
1855 INTO JG_RX_FAREG.var.deprn_reserve_initial
1856 FROM fa_deprn_summary
1857 WHERE book_type_code = PARM.p_book_type_code
1858 AND asset_id = JG_RX_FAREG.var.asset_id
1859 AND period_counter = (select max(period_counter)
1860 from fa_deprn_summary
1861 where period_counter <= (PARM.p_begin_period_counter-1)
1862 and asset_id= JG_RX_FAREG.var.asset_id
1863 and book_type_code = PARM.p_book_type_code );
1864
1865
1866 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.deprn_reserve_initial =' || TO_CHAR(JG_RX_FAREG.var.deprn_reserve_initial));
1867 EXCEPTION
1868 WHEN NO_DATA_FOUND THEN
1869 JG_RX_FAREG.var.deprn_reserve_initial := 0;
1870 END;
1871
1872
1873 -------------------------------------------------------------------------------
1874 BEGIN
1875 FA_RX_UTIL_PKG.debug('Get deprn reserve final value');
1876 -- Get final value
1877 SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0)),
1878 ytd_deprn
1879 INTO JG_RX_FAREG.var.deprn_reserve_final
1880 , JG_RX_FAREG.var.ytd_deprn
1881 FROM fa_deprn_summary
1882 WHERE book_type_code = PARM.p_book_type_code
1883 AND asset_id = JG_RX_FAREG.var.asset_id
1884 AND period_counter =
1885 (select max(period_counter)
1886 from fa_deprn_summary
1887 where period_counter <= PARM.p_end_period_counter
1888 and asset_id= JG_RX_FAREG.var.asset_id
1889 and book_type_code = PARM.p_book_type_code );
1890
1891
1892 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.deprn_reserve_final =' || TO_CHAR(JG_RX_FAREG.var.deprn_reserve_final));
1893 --Added this part to fix bug 3240485
1894 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.ytd_deprn=' || TO_CHAR(JG_RX_FAREG.var.ytd_deprn));
1895 --Added the above part to fix bug 3240485
1896 EXCEPTION
1897 WHEN NO_DATA_FOUND THEN
1898 JG_RX_FAREG.var.deprn_reserve_final := 0;
1899 END;
1900
1901
1902 -- It takes the deprn reserve increase
1903 Get_deprn_reserve_increase;
1904
1905 -- It takes the deprn reserve decrease
1906 Get_deprn_reserve_decrease;
1907
1908 -- 09/08/00 AFERRARA
1909 JG_RX_FAREG.var.deprn_variation := JG_RX_FAREG.var.deprn_reserve_increase -
1910 JG_RX_FAREG.var.deprn_reserve_decrease;
1911
1912 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.deprn_variation =' || JG_RX_FAREG.var.deprn_variation);
1913 -- 09/08/00 AFERRARA
1914
1915
1916 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_deprn_reserve_value()-');
1917
1918 EXCEPTION
1919 WHEN TOO_MANY_ROWS THEN
1920 X_msg := 'JG_RX_FAREG.Get_deprn_reserve_value: TOO_MANY_ROWS';
1921 FA_RX_UTIL_PKG.debug(X_msg);
1922 RAISE_APPLICATION_ERROR(-20010,X_msg);
1923
1924
1925 END Get_deprn_reserve_value;
1926
1927
1928
1929 /*===================================================================+
1930 | Get_deprn_reserve_increase |
1931 +====================================================================*/
1935 X_ord_deprn NUMBER := 0;
1932 PROCEDURE Get_deprn_reserve_increase
1933 IS
1934
1936 X_reinstatements NUMBER := 0;
1937 X_tax_re_adjustment_plus NUMBER := 0;
1938 X_revaluation_cr NUMBER := 0;
1939
1940 BEGIN
1941 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_deprn_reserve_increase()+');
1942
1943 -------------------------------------------------------------------------------
1944 FA_RX_UTIL_PKG.debug('Get ordinary depreciation ');
1945
1946 SELECT SUM((NVL(deprn_amount,0) - NVL(bonus_deprn_amount,0)))
1947 INTO X_ord_deprn
1948 FROM fa_deprn_summary
1949 WHERE book_type_code = PARM.p_book_type_code
1950 AND asset_id = JG_RX_FAREG.var.asset_id
1951 AND period_counter BETWEEN PARM.p_begin_period_counter
1952 AND PARM.p_end_period_counter;
1953
1954 FA_RX_UTIL_PKG.debug('Ordinary depreciation =' || TO_CHAR(X_ord_deprn));
1955
1956
1957 -------------------------------------------------------------------------------
1958 FA_RX_UTIL_PKG.debug('Get increase due to reinstatements and Revaluations ');
1959
1960 SELECT nvl(abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
1961 SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0))),0)
1962 INTO X_revaluation_cr
1963 FROM fa_adjustments AD
1964 WHERE AD.source_type_code = 'REVALUATION' -- bug 5208066 removed reinstatement
1965 AND AD.book_type_code = PARM.p_book_type_code
1966 AND AD.asset_id = JG_RX_FAREG.var.asset_id
1967 AND AD.debit_credit_flag = 'CR'
1968 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1969 AND PARM.p_end_period_counter;
1970
1971 --query added for bug 5208066 to separate revaluation and reinstatement
1972
1973 SELECT nvl(abs(SUM(DECODE(adjustment_type,'RESERVE', DECODE(debit_credit_flag , 'CR' ,adjustment_amount, 'DR', (-1*adjustment_amount),0),0)) -
1974 SUM(DECODE(adjustment_type,'BONUS RESERVE',DECODE(debit_credit_flag , 'CR' ,adjustment_amount, 'DR', (-1*adjustment_amount),0),0))),0)
1975
1976 INTO X_reinstatements
1977 FROM fa_adjustments AD, fa_transaction_headers ft
1978 WHERE AD.source_type_code = 'RETIREMENT'
1979 AND AD.book_type_code = PARM.p_book_type_code
1980 AND AD.asset_id = JG_RX_FAREG.var.asset_id
1981 and ft.asset_id = ad.asset_id
1982 and ft.book_type_code = ad.book_type_code
1983 and ft.transaction_header_id = ad.transaction_header_id
1984 and ft.transaction_type_code in ('REINSTATEMENT')
1985 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
1986 AND PARM.p_end_period_counter;
1987
1988 X_reinstatements := X_reinstatements+X_revaluation_cr;
1989
1990
1991 FA_RX_UTIL_PKG.debug('Reinstatements =' || TO_CHAR(X_reinstatements));
1992
1993 -------------------------------------------------------------------------------
1994 FA_RX_UTIL_PKG.debug('Get positive tax reserve adjustment ');
1995
1996 SELECT SUM(adjustment_amount)
1997 INTO X_tax_re_adjustment_plus
1998 FROM fa_adjustments AD
1999 WHERE AD.source_type_code = 'TAX'
2000 AND AD.adjustment_type = 'RESERVE'
2001 AND AD.debit_credit_flag = 'CR'
2002 AND AD.book_type_code = PARM.p_book_type_code
2003 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2004 AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
2005 AND PARM.p_end_period_counter;
2006 -- changed period_counter_adjusted to period_counter_created
2007 FA_RX_UTIL_PKG.debug('Positive tax reserve adjustment =' || TO_CHAR(X_tax_re_adjustment_plus));
2008
2009
2010 JG_RX_FAREG.var.deprn_reserve_increase := NVL(X_ord_deprn,0) +
2011 NVL(X_reinstatements,0) +
2012 NVL(X_tax_re_adjustment_plus,0);
2013
2014 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.deprn_reserve_increase =' || JG_RX_FAREG.var.deprn_reserve_increase);
2015
2016
2017 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_deprn_reserve_increase()-');
2018
2019
2020 END Get_deprn_reserve_increase;
2021
2022
2023 /*===================================================================+
2024 | Get_deprn_reserve_decrease |
2025 +====================================================================*/
2026 PROCEDURE Get_deprn_reserve_decrease
2027 IS
2028
2029 X_retirements NUMBER := 0;
2030 X_financ_adjustment_minus NUMBER := 0;
2031 X_tax_re_adjustment_minus NUMBER := 0;
2032 X_revaluation_dr NUMBER := 0;
2033
2034 BEGIN
2035 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_deprn_reserve_decrease()+');
2036
2037
2038 -------------------------------------------------------------------------------
2039 FA_RX_UTIL_PKG.debug('Get deprn reserve decrease due to retirements and revaluations');
2040
2041 SELECT nvl(abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
2042 SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0))),0)
2043 INTO X_revaluation_dr
2044 FROM fa_adjustments AD
2045 WHERE AD.source_type_code = 'REVALUATION' --bug 5208066 removed retirement
2049 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2046 AND AD.book_type_code = PARM.p_book_type_code
2047 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2048 AND AD.debit_credit_flag = 'DR'
2050 AND PARM.p_end_period_counter;
2051
2052 --query added for bug 5208066 to separate revaluation and retirement
2053 SELECT nvl(abs(SUM(DECODE(adjustment_type,'RESERVE', DECODE(debit_credit_flag , 'DR' ,adjustment_amount, 'CR', (-1*adjustment_amount),0),0)) -
2054 SUM(DECODE(adjustment_type,'BONUS RESERVE',DECODE(debit_credit_flag , 'DR' ,adjustment_amount, 'CR', (-1*adjustment_amount),0),0))),0)
2055
2056 INTO X_retirements
2057 FROM fa_adjustments AD, fa_transaction_headers ft
2058 WHERE AD.source_type_code = 'RETIREMENT'
2059 AND AD.book_type_code = PARM.p_book_type_code
2060 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2061 and ft.asset_id = ad.asset_id
2062 and ft.book_type_code = ad.book_type_code
2063 and ft.transaction_header_id = ad.transaction_header_id
2064 and ft.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
2065 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2066 AND PARM.p_end_period_counter;
2067
2068 X_retirements := X_retirements+X_revaluation_dr;
2069
2070 FA_RX_UTIL_PKG.debug('Retirements =' || TO_CHAR(X_retirements));
2071
2072
2073
2074 -------------------------------------------------------------------------------
2075 FA_RX_UTIL_PKG.debug('Get negative financial adjustment ');
2076
2077 SELECT abs(SUM(DECODE(adjustment_type,'EXPENSE',adjustment_amount,0)) -
2078 SUM(DECODE(adjustment_type,'BONUS EXPENSE',adjustment_amount,0)))
2079 INTO X_financ_adjustment_minus
2080 FROM fa_adjustments AD
2081 WHERE AD.source_type_code = 'DEPRECIATION'
2082 AND AD.adjustment_amount < 0
2083 AND AD.book_type_code = PARM.p_book_type_code
2084 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2085 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2086 AND PARM.p_end_period_counter;
2087
2088 FA_RX_UTIL_PKG.debug('Negative financial adjustment =' || TO_CHAR(X_financ_adjustment_minus));
2089
2090 JG_RX_FAREG.var.deprn_reserve_increase := JG_RX_FAREG.var.deprn_reserve_increase +nvl(X_financ_adjustment_minus,0);
2091
2092 /* to show the increase and then minus it */
2093
2094 -------------------------------------------------------------------------------
2095 FA_RX_UTIL_PKG.debug('Get negative tax reserve adjustment ');
2096
2097 SELECT SUM(adjustment_amount)
2098 INTO X_tax_re_adjustment_minus
2099 FROM fa_adjustments AD
2100 WHERE AD.source_type_code = 'TAX'
2101 AND AD.adjustment_type = 'RESERVE'
2102 AND AD.debit_credit_flag = 'DR'
2103 AND AD.book_type_code = PARM.p_book_type_code
2104 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2105 AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
2106 AND PARM.p_end_period_counter;
2107 -- jmary changed period_counter_adjusted to period_counter_created
2108 FA_RX_UTIL_PKG.debug('Negative tax reserve adjustment =' || TO_CHAR(X_tax_re_adjustment_minus));
2109
2110
2111 JG_RX_FAREG.var.deprn_reserve_decrease := NVL(X_retirements,0) +
2112 NVL(X_financ_adjustment_minus,0) +
2113 NVL(X_tax_re_adjustment_minus,0);
2114
2115 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.deprn_reserve_decrease =' || JG_RX_FAREG.var.deprn_reserve_decrease);
2116
2117
2118 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_deprn_reserve_decrease()-');
2119
2120
2121 END Get_deprn_reserve_decrease;
2122
2123
2124
2125 /*===================================================================+
2126 | Get_bonus_reserve_value |
2127 +====================================================================*/
2128 PROCEDURE Get_bonus_reserve_value
2129 IS
2130
2131 X_msg VARCHAR2(100);
2132
2133 BEGIN
2134 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_reserve_value()+');
2135
2136 -------------------------------------------------------------------------------
2137 BEGIN
2138 FA_RX_UTIL_PKG.debug('Get bonus reserve initial value');
2139 -- Get initial value
2140
2141 SELECT NVL(bonus_deprn_reserve,0)
2142 INTO JG_RX_FAREG.var.bonus_reserve_initial
2143 FROM fa_deprn_summary
2144 WHERE book_type_code = PARM.p_book_type_code
2145 AND asset_id = JG_RX_FAREG.var.asset_id
2146 AND period_counter =
2147 (SELECT max(period_counter)
2148 FROM fa_deprn_summary
2149 WHERE period_counter <= (PARM.p_begin_period_counter-1)
2150 AND asset_id= JG_RX_FAREG.var.asset_id
2151 AND book_type_code = PARM.p_book_type_code );
2152
2153
2154 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.bonus_reserve_initial =' || TO_CHAR(JG_RX_FAREG.var.bonus_reserve_initial));
2155 EXCEPTION
2156 WHEN NO_DATA_FOUND THEN
2157 JG_RX_FAREG.var.bonus_reserve_initial := 0;
2158 END;
2159
2160
2161 -------------------------------------------------------------------------------
2162 BEGIN
2163 FA_RX_UTIL_PKG.debug('Get bonus reserve final value');
2167 FROM fa_deprn_summary
2164 -- Get final value
2165 SELECT NVL(bonus_deprn_reserve,0)
2166 INTO JG_RX_FAREG.var.bonus_reserve_final
2168 WHERE book_type_code = PARM.p_book_type_code
2169 AND asset_id = JG_RX_FAREG.var.asset_id
2170 AND period_counter =
2171 (SELECT max(period_counter)
2172 FROM fa_deprn_summary
2173 WHERE period_counter <= PARM.p_end_period_counter
2174 AND asset_id= JG_RX_FAREG.var.asset_id
2175 AND book_type_code = PARM.p_book_type_code );
2176
2177
2178 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.bonus_reserve_final =' || TO_CHAR(JG_RX_FAREG.var.bonus_reserve_final));
2179 EXCEPTION
2180 WHEN NO_DATA_FOUND THEN
2181 JG_RX_FAREG.var.bonus_reserve_final := 0;
2182 END;
2183
2184
2185 -- It takes the bonus reserve increase
2186 Get_bonus_reserve_increase;
2187
2188 -- It takes the deprn reserve decrease
2189 Get_bonus_reserve_decrease;
2190 -- 09/08/00 AFERRARA
2191 JG_RX_FAREG.var.bonus_variation := JG_RX_FAREG.var.bonus_reserve_increase -
2192 JG_RX_FAREG.var.bonus_reserve_decrease;
2193
2194 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.bonus_variation =' || JG_RX_FAREG.var.bonus_variation);
2195 -- 09/08/00 AFERRARA
2196
2197
2198
2199 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_reserve_value()-');
2200
2201 EXCEPTION
2202 WHEN TOO_MANY_ROWS THEN
2203 X_msg := 'JG_RX_FAREG.Get_bonus_reserve_value: TOO_MANY_ROWS';
2204 FA_RX_UTIL_PKG.debug(X_msg);
2205 RAISE_APPLICATION_ERROR(-20010,X_msg);
2206
2207
2208 END Get_bonus_reserve_value;
2209
2210
2211
2212 /*===================================================================+
2213 | Get_bonus_reserve_increase |
2214 +====================================================================*/
2215 PROCEDURE Get_bonus_reserve_increase
2216 IS
2217
2218 X_bonus_deprn NUMBER := 0;
2219 X_reinstatements NUMBER := 0;
2220 X_financ_adjustment_plus NUMBER := 0;
2221 X_tax_re_adjustment_plus NUMBER := 0;
2222
2223 BEGIN
2224 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_reserve_increase()+');
2225
2226 -------------------------------------------------------------------------------
2227 FA_RX_UTIL_PKG.debug('Get bonus depreciation ');
2228
2229 SELECT sum(bonus_deprn_amount)
2230 INTO X_bonus_deprn
2231 FROM fa_deprn_summary
2232 WHERE book_type_code = PARM.p_book_type_code
2233 AND asset_id = JG_RX_FAREG.var.asset_id
2234 AND period_counter BETWEEN PARM.p_begin_period_counter
2235 AND PARM.p_end_period_counter;
2236
2237 FA_RX_UTIL_PKG.debug('Bonus depreciation =' || TO_CHAR(X_bonus_deprn));
2238
2239
2240 -------------------------------------------------------------------------------
2241 FA_RX_UTIL_PKG.debug('Get reinstatements ');
2242
2243 SELECT SUM(adjustment_amount)
2244 INTO X_reinstatements
2245 FROM fa_adjustments AD
2246 WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
2247 AND AD.book_type_code = PARM.p_book_type_code
2248 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2249 AND AD.adjustment_type = 'BONUS RESERVE'
2250 AND AD.debit_credit_flag = 'CR'
2251 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2252 AND PARM.p_end_period_counter;
2253 FA_RX_UTIL_PKG.debug('Reinstatements =' || TO_CHAR(X_reinstatements));
2254
2255
2256 JG_RX_FAREG.var.bonus_reserve_increase := NVL(X_bonus_deprn,0) +
2257 NVL(X_reinstatements,0) ;
2258
2259 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.bonus_reserve_increase =' || JG_RX_FAREG.var.bonus_reserve_increase);
2260
2261
2262 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_reserve_increase()-');
2263
2264
2265 END Get_bonus_reserve_increase;
2266
2267
2268 /*===================================================================+
2269 | Get_bonus_reserve_decrease |
2270 +====================================================================*/
2271 PROCEDURE Get_bonus_reserve_decrease
2272 IS
2273
2274 X_retirements NUMBER := 0;
2275 X_financ_adjustment_minus NUMBER := 0;
2276
2277 BEGIN
2278 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_reserve_decrease()+');
2279
2280
2281 -------------------------------------------------------------------------------
2282 FA_RX_UTIL_PKG.debug('Get retirements and revaluations ');
2283 SELECT ABS(SUM(adjustment_amount))
2284 INTO X_retirements
2285 FROM fa_adjustments AD
2286 WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
2287 AND AD.adjustment_type = 'BONUS RESERVE'
2288 AND AD.debit_credit_flag = 'DR'
2289 AND AD.book_type_code = PARM.p_book_type_code
2290 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2291 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2292 AND PARM.p_end_period_counter;
2293
2294
2295 FA_RX_UTIL_PKG.debug('Retirements =' || TO_CHAR(X_retirements));
2296
2297
2298
2299 -------------------------------------------------------------------------------
2303 FROM fa_adjustments AD
2300 FA_RX_UTIL_PKG.debug('Get Negative financial adjustment ');
2301 SELECT ABS(SUM(adjustment_amount))
2302 INTO X_financ_adjustment_minus
2304 WHERE AD.source_type_code = 'DEPRECIATION'
2305 AND AD.adjustment_type = 'BONUS EXPENSE'
2306 AND AD.adjustment_amount < 0
2307 AND AD.book_type_code = PARM.p_book_type_code
2308 AND AD.asset_id = JG_RX_FAREG.var.asset_id
2309 AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
2310 AND PARM.p_end_period_counter;
2311 JG_RX_FAREG.var.bonus_reserve_increase := JG_RX_FAREG.var.bonus_reserve_increase +nvl(X_financ_adjustment_minus,0);
2312
2313 FA_RX_UTIL_PKG.debug('Negative financial adjustment =' || TO_CHAR(X_financ_adjustment_minus));
2314
2315
2316 JG_RX_FAREG.var.bonus_reserve_decrease := NVL(X_retirements,0) +
2317 NVL(X_financ_adjustment_minus,0);
2318
2319 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.bonus_reserve_decrease =' || JG_RX_FAREG.var.bonus_reserve_decrease);
2320
2321
2322 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_reserve_decrease()-');
2323
2324
2325 END Get_bonus_reserve_decrease;
2326
2327
2328 /*===================================================================+
2329 | Get_fiscal_year_date |
2330 +====================================================================*/
2331 PROCEDURE Get_fiscal_year_date
2332 IS
2333
2334 X_msg VARCHAR2(100);
2335
2336 BEGIN
2337 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_fiscal_year_date()+');
2338
2339 SELECT start_date,
2340 end_date,
2341 deprn_calendar,
2342 prorate_calendar
2343 INTO X_fiscal_year_start_date,
2344 X_fiscal_year_end_date,
2345 X_deprn_calendar,
2346 X_prorate_calendar
2347 FROM fa_fiscal_year FY,
2348 fa_book_controls BC
2349 WHERE FY.fiscal_year = X_fiscal_year
2350 AND FY.fiscal_year_name = BC.fiscal_year_name
2351 AND BC.book_type_code = PARM.p_book_type_code;
2352
2353
2354 FA_RX_UTIL_PKG.debug('X_fiscal_year_start_date =' || TO_CHAR(X_fiscal_year_start_date));
2355 FA_RX_UTIL_PKG.debug('X_fiscal_year_end_date =' || TO_CHAR(X_fiscal_year_end_date));
2356
2357
2358 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_fiscal_year_date()-');
2359
2360 -- Added for ECE Project
2361 BEGIN
2362 SELECT number_per_fiscal_year
2363 INTO X_number_per_fiscal_year
2364 FROM fa_calendar_types
2365 WHERE calendar_type = X_deprn_calendar;
2366
2367 JG_RX_FAREG.var.life_in_years := JG_RX_FAREG.var.life_in_months / X_number_per_fiscal_year;
2368
2369 END;
2370
2371 EXCEPTION
2372 WHEN NO_DATA_FOUND THEN
2373 X_msg := 'JG_RX_FAREG.Get_fiscal_year_date: NO_DATA_FOUND';
2374 FA_RX_UTIL_PKG.debug(X_msg);
2375 RAISE_APPLICATION_ERROR(-20010,X_msg);
2376
2377 WHEN TOO_MANY_ROWS THEN
2378 X_msg := 'JG_RX_FAREG.Get_fiscal_year_date: TOO_MANY_ROWS';
2379 FA_RX_UTIL_PKG.debug(X_msg);
2380 RAISE_APPLICATION_ERROR(-20010,X_msg);
2381
2382 END Get_fiscal_year_date;
2383
2384
2385 /*===================================================================+
2386 | Get_bonus_rate |
2387 +====================================================================*/
2388 PROCEDURE Get_bonus_rate
2389 IS
2390
2391 X_year NUMBER;
2392 X_starting_depreciation_year NUMBER(4);
2393
2394 BEGIN
2395 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_rate()+');
2396
2397 JG_RX_FAREG.var.bonus_rate := 0; -- To print 0 instead of NULL
2398
2399 IF JG_RX_FAREG.var.bonus_rule IS NOT NULL THEN
2400 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_bonus_rate: JG_RX_FAREG.var.bonus_rule IS NOT NULL');
2401
2402 X_starting_depreciation_year := Get_starting_depreciation_year;
2403
2404 X_year := TO_NUMBER(X_fiscal_year) - X_starting_depreciation_year + 1;
2405
2406 SELECT (bonus_rate*100)
2407 INTO JG_RX_FAREG.var.bonus_rate
2408 FROM fa_bonus_rates
2409 WHERE bonus_rule = JG_RX_FAREG.var.bonus_rule
2410 AND X_year BETWEEN start_year
2411 AND end_year;
2412
2413 END IF;
2414
2415 EXCEPTION
2416 WHEN NO_DATA_FOUND THEN
2417 JG_RX_FAREG.var.bonus_rate := 0;
2418
2419 END Get_bonus_rate;
2420
2421
2422 /*===================================================================+
2423 | Get_transactions |
2424 +====================================================================*/
2425 PROCEDURE Get_transactions
2426 IS
2427
2428 X_msg VARCHAR2(100);
2429
2430 BEGIN
2431 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_transactions()+');
2432
2433 Get_addition_transactions;
2434
2435 Get_adjustment_transactions;
2436
2437 Get_retirement_transactions;
2438
2439 Get_revaluation_transactions;
2440
2441 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_transactions()-');
2442
2443 END Get_transactions;
2444
2445
2446 /*===================================================================+
2447 | Get_addition_transactions |
2448 +====================================================================*/
2449 PROCEDURE Get_addition_transactions
2450 IS
2451
2455 BO.date_placed_in_service,
2452 CURSOR c_additon_trans IS
2453 SELECT TH.transaction_type_code,
2454 TH.transaction_header_id,
2456 BO.cost
2457 FROM fa_transaction_headers TH,
2458 fa_books BO
2459 WHERE BO.transaction_header_id_in = TH.transaction_header_id
2460 AND BO.book_type_code = TH.book_type_code
2461 AND BO.asset_id = TH.asset_id
2462 AND TH.transaction_type_code = 'ADDITION'
2463 AND TH.book_type_code = PARM.p_book_type_code
2464 AND TH.asset_id = JG_RX_FAREG.var.asset_id
2465 AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
2466 AND PARM.p_end_period_to_date;
2467 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2468 BEGIN
2469 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_addition_transactions()+');
2470 FOR c_addition IN c_additon_trans LOOP
2471 IF (X_first_row = FALSE and c_additon_trans%ROWCOUNT=0) then
2472 exit ;
2473 end if;
2474 IF X_first_row = FALSE THEN -- It is the first time...
2475 JG_RX_FAREG.var.transaction_date := c_addition.date_placed_in_service;
2476 JG_RX_FAREG.var.transaction_number := c_addition.transaction_header_id;
2477 JG_RX_FAREG.var.transaction_code := c_addition.transaction_type_code;
2478 JG_RX_FAREG.var.transaction_amount := c_addition.cost;
2479 X_first_row := TRUE;
2480 ELSE -- The current row is alredy used
2481 Insert_transaction(p_transaction_date => c_addition.date_placed_in_service,
2482 p_transaction_number => c_addition.transaction_header_id,
2483 p_transaction_code => c_addition.transaction_type_code,
2484 p_transaction_amount => c_addition.cost);
2485 END IF;
2486
2487 END LOOP;
2488 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_addition_transactions()-');
2489
2490 END Get_addition_transactions;
2491
2492
2493 /*===================================================================+
2494 | Get_adjustment_transactions |
2495 +====================================================================*/
2496 PROCEDURE Get_adjustment_transactions
2497 IS
2498
2499 CURSOR c_adjustment_trans IS
2500 SELECT TH.transaction_type_code,
2501 TH.transaction_header_id,
2502 TH.transaction_date_entered,
2503 decode(debit_credit_flag,'CR',(-1*AD.ADJUSTMENT_AMOUNT),AD.ADJUSTMENT_AMOUNT) ADJUSTMENT_AMOUNT
2504 FROM fa_transaction_headers TH,
2505 fa_ADJUSTMENTS AD
2506 WHERE AD.transaction_header_id = TH.transaction_header_id
2507 AND AD.book_type_code = TH.book_type_code
2508 AND AD.asset_id = TH.asset_id
2509 AND TH.transaction_type_code = 'ADJUSTMENT'
2510 AND AD.source_type_code = TH.transaction_type_code
2511 AND AD.adjustment_type = 'COST'
2512 AND TH.book_type_code = PARM.p_book_type_code
2513 AND TH.asset_id = JG_RX_FAREG.var.asset_id
2514 AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
2515 AND PARM.p_end_period_to_date;
2516
2517 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2518
2519 BEGIN
2520 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_adjustment_transactions()+');
2521 FOR c_adjustment IN c_adjustment_trans LOOP
2522 IF (X_first_row = FALSE and c_adjustment_trans%ROWCOUNT=0) then
2523 exit ;
2524 end if;
2525 IF X_first_row = FALSE THEN -- It is the first time...
2526 JG_RX_FAREG.var.transaction_date := c_adjustment.transaction_date_entered;
2527 JG_RX_FAREG.var.transaction_number := c_adjustment.transaction_header_id;
2528 JG_RX_FAREG.var.transaction_code := c_adjustment.transaction_type_code;
2529 JG_RX_FAREG.var.transaction_amount := c_adjustment.adjustment_amount;
2530 X_first_row := TRUE;
2531 ELSE -- The current row is alredy used
2532 Insert_transaction(p_transaction_date => c_adjustment.transaction_date_entered,
2533 p_transaction_number => c_adjustment.transaction_header_id,
2534 p_transaction_code => c_adjustment.transaction_type_code,
2535 p_transaction_amount => c_adjustment.adjustment_amount);
2536 END IF;
2537
2538 END LOOP;
2539
2540 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_adjustment_transactions()-');
2541
2542 END Get_adjustment_transactions;
2543
2544
2545
2546 /*===================================================================+
2547 | Get_retirement_transactions |
2548 +====================================================================*/
2549 PROCEDURE Get_retirement_transactions
2550 IS
2551
2552 CURSOR c_retirement_trans IS
2553 SELECT TH.transaction_type_code,
2554 TH.transaction_header_id,
2555 th.transaction_date_entered,
2556 RE.cost_retired
2557 FROM fa_transaction_headers TH,
2558 fa_retirements RE
2559 WHERE (RE.transaction_header_id_in = TH.transaction_header_id
2560 OR RE.transaction_header_id_out = TH.transaction_header_id)
2561 AND RE.book_type_code = TH.book_type_code
2562 AND RE.asset_id = TH.asset_id
2566 AND th.transaction_date_entered BETWEEN X_fiscal_year_start_date
2563 AND TH.TRANSACTION_TYPE_CODE IN ('PARTIAL RETIREMENT','FULL RETIREMENT','REINSTATEMENT') -- AND RE.STATUS = 'PROCESSED'
2564 AND TH.book_type_code = PARM.p_book_type_code
2565 AND TH.asset_id = JG_RX_FAREG.var.asset_id
2567 AND PARM.p_end_period_to_date;
2568
2569 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2570
2571 BEGIN
2572 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_retirement_transactions()+');
2573
2574 FOR c_retirement IN c_retirement_trans LOOP
2575 IF (X_first_row = FALSE and c_retirement_trans%ROWCOUNT=0) then
2576 exit ;
2577 end if;
2578 IF X_first_row = FALSE THEN -- It is the first time...
2579 JG_RX_FAREG.var.transaction_date := c_retirement.transaction_date_entered;
2580 JG_RX_FAREG.var.transaction_number := c_retirement.transaction_header_id;
2581 JG_RX_FAREG.var.transaction_code := c_retirement.transaction_type_code;
2582 JG_RX_FAREG.var.transaction_amount := c_retirement.cost_retired;
2583 X_first_row := TRUE;
2584 ELSE -- The current row is alredy used
2585 Insert_transaction(p_transaction_date => c_retirement.transaction_date_entered,
2586 p_transaction_number => c_retirement.transaction_header_id,
2587 p_transaction_code => c_retirement.transaction_type_code,
2588 p_transaction_amount => c_retirement.cost_retired);
2589 END IF;
2590
2591 END LOOP;
2592
2593 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_retirement_transactions()-');
2594
2595 END Get_retirement_transactions;
2596
2597 /*===================================================================+
2598 | Get_revaluation_transactions |
2599 +====================================================================*/
2600 PROCEDURE Get_revaluation_transactions
2601 IS
2602
2603 CURSOR c_revaluation_trans IS
2604 SELECT TH.transaction_type_code,
2605 TH.transaction_header_id,
2606 TH.transaction_date_entered,
2607 decode(debit_credit_flag,'CR',(-1*sum(AD.ADJUSTMENT_AMOUNT)),sum(AD.ADJUSTMENT_AMOUNT)) ADJUSTMENT_AMOUNT
2608 -- SUM(AD.adjustment_amount) adjustment_amount
2609 FROM fa_transaction_headers TH,
2610 fa_adjustments AD
2611 WHERE AD.transaction_header_id = TH.transaction_header_id
2612 AND AD.book_type_code = TH.book_type_code
2613 AND AD.asset_id = TH.asset_id
2614 AND AD.adjustment_type = 'COST'
2615 AND TH.TRANSACTION_TYPE_CODE = 'REVALUATION'
2616 AND TH.book_type_code = PARM.p_book_type_code
2617 AND TH.asset_id = JG_RX_FAREG.var.asset_id
2618 AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
2619 AND PARM.p_end_period_to_date
2620 GROUP BY TH.transaction_type_code,
2621 TH.transaction_header_id,
2622 TH.transaction_date_entered,debit_credit_flag;
2623
2624 -- changing X_fiscal_year_end_date to PARM.p_end_period_to_date
2625
2626 BEGIN
2627 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation_transactions()+');
2628 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation_transactions()+');
2629 FOR c_revaluation IN c_revaluation_trans LOOP
2630 IF X_first_row = FALSE THEN -- It is the first time...
2631 JG_RX_FAREG.var.transaction_date := c_revaluation.transaction_date_entered;
2632 JG_RX_FAREG.var.transaction_number := c_revaluation.transaction_header_id;
2633 JG_RX_FAREG.var.transaction_code := c_revaluation.transaction_type_code;
2634 JG_RX_FAREG.var.transaction_amount := c_revaluation.adjustment_amount;
2635 X_first_row := TRUE;
2636 ELSE -- The current row is alredy used
2637 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.rowcount'||to_char(c_revaluation_trans%ROWCOUNT));
2638 Insert_transaction(p_transaction_date => c_revaluation.transaction_date_entered,
2639 p_transaction_number => c_revaluation.transaction_header_id,
2640 p_transaction_code => c_revaluation.transaction_type_code,
2641 p_transaction_amount => c_revaluation.adjustment_amount);
2642 END IF;
2643
2644 END LOOP;
2645
2646 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_revaluation_transactions()-');
2647
2648 END Get_revaluation_transactions;
2649
2650
2651 /*===================================================================+
2652 | Insert_transaction |
2653 +====================================================================*/
2654 PROCEDURE Insert_transaction( p_transaction_date DATE,
2655 p_transaction_number NUMBER,
2656 p_transaction_code VARCHAR2,
2657 p_transaction_amount NUMBER)
2658 IS
2659
2660 BEGIN
2661 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Insert_transaction()+');
2662
2663 INSERT INTO jg_zz_fa_reg_itf(
2664 request_id,
2665 organization_name,
2666 functional_currency_code,
2667 last_update_date,
2668 last_updated_by,
2669 last_update_login,
2670 creation_date,
2671 created_by,
2672 major_category,
2673 minor_category,
2674 deprn_rate,
2678 asset_number,
2675 starting_deprn_year,
2676 date_placed_in_service,
2677 asset_heading,
2679 description,
2680 parent_asset_id,
2681 parent_asset_number,
2682 asset_cost_orig,
2683 bonus_rate,
2684 invoice_number,
2685 supplier_name,
2686 cost_account,
2687 expense_account,
2688 reserve_account,
2689 bonus_deprn_account,
2690 bonus_reserve_account,
2691 asset_cost_initial,
2692 asset_cost_increase,
2693 asset_cost_decrease,
2694 asset_cost_final,
2695 revaluation_initial,
2696 revaluation_increase,
2697 revaluation_decrease,
2698 revaluation_final,
2699 deprn_reserve_initial,
2700 deprn_reserve_increase,
2701 deprn_reserve_decrease,
2702 deprn_reserve_final,
2703 bonus_reserve_initial,
2704 bonus_reserve_increase,
2705 bonus_reserve_decrease,
2706 bonus_reserve_final,
2707 net_book_value_initial,
2708 net_book_value_increase,
2709 net_book_value_decrease,
2710 net_book_value_final,
2711 transaction_date,
2712 transaction_number,
2713 transaction_code,
2714 transaction_amount,
2715 sales_amount,
2716 cost_retired,
2717 deprn_reserve,
2718 bonus_reserve,
2719 net_book_value,
2720 gain_loss,
2721 date_retired,
2722 initial_heading, -- 09/08/00 AFERRARA
2723 variation_heading, -- 09/08/00 AFERRARA
2724 final_heading, -- 09/08/00 AFERRARA
2725 asset_variation, -- 09/08/00 AFERRARA
2726 reval_variation, -- 09/08/00 AFERRARA
2727 deprn_variation, -- 09/08/00 AFERRARA
2728 bonus_variation, -- 09/08/00 AFERRARA
2729 netbo_variation, -- 09/08/00 AFERRARA
2730 revaluation_total -- 09/08/00 AFERRARA
2731 --- Added for ECE Project
2732 ,depreciation_method
2733 ,life_in_months
2734 ,life_in_years
2735 ,prorate_convention
2736 ,tag_number
2737 ,serial_number
2738 ,asset_key_flexfield
2739 -- Added to fix bug 3240485
2740 ,ytd_deprn
2741 -- Added above to fix bug 3240485
2742 )
2743 VALUES(
2744 X_request_id,
2745 JG_RX_FAREG.var.organization_name,
2746 JG_RX_FAREG.var.functional_currency_code,
2747 X_last_update_date,
2748 X_last_updated_by,
2749 X_last_update_login,
2750 X_creation_date,
2751 X_created_by,
2752 JG_RX_FAREG.var.major_category,
2753 JG_RX_FAREG.var.minor_category,
2754 JG_RX_FAREG.var.deprn_rate,
2755 JG_RX_FAREG.var.starting_deprn_year,
2756 JG_RX_FAREG.var.date_placed_in_service,
2757 JG_RX_FAREG.var.asset_heading,
2758 JG_RX_FAREG.var.asset_number,
2759 JG_RX_FAREG.var.description,
2760 JG_RX_FAREG.var.parent_asset_id,
2761 JG_RX_FAREG.var.parent_asset_number,
2762 JG_RX_FAREG.var.asset_cost_orig,
2763 JG_RX_FAREG.var.bonus_rate,
2764 JG_RX_FAREG.var.invoice_number,
2765 JG_RX_FAREG.var.supplier_name,
2766 JG_RX_FAREG.var.cost_account,
2767 JG_RX_FAREG.var.expense_account,
2768 JG_RX_FAREG.var.reserve_account,
2769 JG_RX_FAREG.var.bonus_deprn_account,
2770 JG_RX_FAREG.var.bonus_reserve_account,
2771 JG_RX_FAREG.var.asset_cost_initial,
2772 JG_RX_FAREG.var.asset_cost_increase,
2773 JG_RX_FAREG.var.asset_cost_decrease,
2774 JG_RX_FAREG.var.asset_cost_final,
2775 JG_RX_FAREG.var.revaluation_initial,
2776 JG_RX_FAREG.var.revaluation_increase,
2780 JG_RX_FAREG.var.deprn_reserve_increase,
2777 JG_RX_FAREG.var.revaluation_decrease,
2778 JG_RX_FAREG.var.revaluation_final,
2779 JG_RX_FAREG.var.deprn_reserve_initial,
2781 JG_RX_FAREG.var.deprn_reserve_decrease,
2782 JG_RX_FAREG.var.deprn_reserve_final,
2783 JG_RX_FAREG.var.bonus_reserve_initial,
2784 JG_RX_FAREG.var.bonus_reserve_increase,
2785 JG_RX_FAREG.var.bonus_reserve_decrease,
2786 JG_RX_FAREG.var.bonus_reserve_final,
2787 JG_RX_FAREG.var.net_book_value_initial,
2788 JG_RX_FAREG.var.net_book_value_increase,
2789 JG_RX_FAREG.var.net_book_value_decrease,
2790 JG_RX_FAREG.var.net_book_value_final,
2791 p_transaction_date,
2792 p_transaction_number,
2793 p_transaction_code,
2794 p_transaction_amount,
2795 NULL,
2796 NULL,
2797 NULL,
2798 NULL,
2799 NULL,
2800 NULL,
2801 NULL,
2802 JG_RX_FAREG.var.initial_heading, -- 09/08/00 AFERRARA
2803 JG_RX_FAREG.var.variation_heading, -- 09/08/00 AFERRARA
2804 JG_RX_FAREG.var.final_heading, -- 09/08/00 AFERRARA
2805 JG_RX_FAREG.var.asset_variation, -- 09/08/00 AFERRARA
2806 JG_RX_FAREG.var.reval_variation, -- 09/08/00 AFERRARA
2807 JG_RX_FAREG.var.deprn_variation, -- 09/08/00 AFERRARA
2808 JG_RX_FAREG.var.bonus_variation, -- 09/08/00 AFERRARA
2809 JG_RX_FAREG.var.netbo_variation, -- 09/08/00 AFERRARA
2810 JG_RX_FAREG.var.revaluation_total -- 09/08/00 AFERRARA
2811 -- Added for ECE Project
2812 ,JG_RX_FAREG.var.depriciation_method
2813 ,JG_RX_FAREG.var.life_in_months
2814 ,JG_RX_FAREG.var.life_in_years
2815 ,JG_RX_FAREG.var.prorate_convention
2816 ,JG_RX_FAREG.var.tag_number
2817 ,JG_RX_FAREG.var.serial_number
2818 ,JG_RX_FAREG.var.asset_key_flexfield
2819 --Added to fix bug 3240485
2820 ,JG_RX_FAREG.var.ytd_deprn
2821 -- Added above to fix bug 3240485
2822 );
2823
2824
2825 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Insert_transaction()-');
2826
2827 END Insert_transaction;
2828
2829
2830 /*===================================================================+
2831 | Get_Net_Book_Value |
2832 +====================================================================*/
2833 PROCEDURE Get_Net_Book_Value
2834 IS
2835
2836 BEGIN
2837 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_Net_Book_Value()+');
2838
2839 JG_RX_FAREG.var.net_book_value_initial := NVL(JG_RX_FAREG.var.asset_cost_initial,0) -
2840 NVL(JG_RX_FAREG.var.deprn_reserve_initial,0) -
2841 NVL(JG_RX_FAREG.var.bonus_reserve_initial,0) ;
2842
2843 JG_RX_FAREG.var.net_book_value_increase := NVL(JG_RX_FAREG.var.asset_cost_increase,0) +
2844 NVL(JG_RX_FAREG.var.deprn_reserve_decrease,0) +
2845 NVL(JG_RX_FAREG.var.bonus_reserve_decrease,0) ;
2846
2847
2848
2849 JG_RX_FAREG.var.net_book_value_decrease := NVL(JG_RX_FAREG.var.asset_cost_decrease,0) +
2850 NVL(JG_RX_FAREG.var.deprn_reserve_increase,0) +
2851 NVL(JG_RX_FAREG.var.bonus_reserve_increase,0) ;
2852
2853 -- + 09/08/00 AFERRARA
2854 JG_RX_FAREG.var.netbo_variation := JG_RX_FAREG.var.net_book_value_increase -
2855 JG_RX_FAREG.var.net_book_value_decrease;
2856
2857 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.var.netbo_variation =' || JG_RX_FAREG.var.netbo_variation);
2858 -- - 09/08/00 AFERRARA
2859
2860 JG_RX_FAREG.var.net_book_value_final := NVL(JG_RX_FAREG.var.asset_cost_final,0) -
2861 NVL(JG_RX_FAREG.var.deprn_reserve_final,0) -
2862 NVL(JG_RX_FAREG.var.bonus_reserve_final,0) ;
2863
2864 FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Get_Net_Book_Value()-');
2865
2866 END Get_Net_Book_Value;
2867
2868
2869 END JG_RX_FAREG;