[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_AC_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_AC_EXPORT_PKG AS
2 --$Header: JACNACEB.pls 120.14 2011/03/09 09:23:02 jiachi noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNACEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export fixed asset card. |
13 --| |
14 --| PROCEDURE LIST |
15 --| Add_FA_Asset_Card Export asset Usage Information |
16 --| Add_FA_Asset_Card_Monthly Export asset Usage Information monthly |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 04/08/2010 Qingyi Wang created |
21 --| 04/27/2010 Qingyi Wang add the method Add_FA_Asset_Card_Monthly |
22 --| 09-Mar-2011 Jianchao Chi Updated for bug 11847991 |
23 --+======================================================================*/
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_AC_EXPORT';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Add_FA_Asset_Card Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is used to export asset card in Fixed Asset.
34 --
35 -- PARAMETERS:
36 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
37 -- pn_ledger_id Ledger ID
38 -- pv_accounting_year Accounting Year
39 -- pv_period_from Period From
40 -- pv_period_to Period To
41 -- pn_coa_id Identifier of chart of account
42 -- DESIGN REFERENCES:
43 -- CNAO_V2_FA_TD.doc
44 --
45 -- CHANGE HISTORY:
46 --| 04/08/2010 Qingyi Wang created
47 -- 09-Mar-2011 Jianchao Chi Updated for bug 11847991
48 --==========================================================================
49 PROCEDURE Add_FA_Asset_Card(pn_legal_entity_id IN NUMBER,
50 pn_ledger_id IN NUMBER,
51 pv_accounting_year IN VARCHAR2,
52 pv_period_from IN VARCHAR2,
53 pv_period_to IN VARCHAR2,
54 pn_coa_id NUMBER) IS
55 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
56 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
57 lv_procedure_name VARCHAR2(40) := 'Add_FA_Asset_Card';
58 ld_start_date DATE;
59 ld_end_date DATE;
60 ld_start_date_monthly DATE;
61 ld_end_date_monthly DATE;
62 lv_period_name VARCHAR2(15);
63 ln_row_count NUMBER := 0; --Add by Jianchao Chi for bug 11847991 on 2011-03-09
64
65 CURSOR fa_export_period_cur IS
66 SELECT DISTINCT fps.period_name, fps.start_date, fps.end_date
67 FROM gl_period_statuses fps
68 WHERE ledger_id = pn_ledger_id --set_of_books_id = l_set_of_books_id
69 AND application_id = 101
70 AND fps.start_date >= ld_start_date
71 AND fps.end_date <= ld_end_date
72 AND fps.adjustment_period_flag = 'N';
73
74 BEGIN
75 --logging for debug
76 IF (ln_proc_level >= ln_dbg_level) THEN
77 FND_LOG.STRING(ln_proc_level,
78 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
79 '.begin',
80 'Enter procedure');
81
82 -- logging the parameters
83 FND_LOG.STRING(ln_proc_level,
84 lv_procedure_name || '.parameters',
85 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
86 'pn_ledger_id=' || pn_ledger_id || ',' ||
87 'pv_accounting_year=' || pv_accounting_year || ',' ||
88 'pv_period_from=' || pv_period_from || ',' ||
89 'pv_period_to=' || pv_period_to || ',' ||
90 'pn_coa_id =' || pn_coa_id);
91 END IF; --l_proc_level>=l_dbg_level
92
93 FND_FILE.put_line(FND_FILE.log,
94 lv_procedure_name || '.parameters:' ||
95 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
96 'pn_ledger_id=' || pn_ledger_id || ',' ||
97 'pv_accounting_year=' || pv_accounting_year || ',' ||
98 'pv_period_from=' || pv_period_from || ',' ||
99 'pv_period_to=' || pv_period_to || ',' ||
100 'pn_coa_id =' || pn_coa_id);
101
102 --Fetch start data and end date
103 IF pv_period_from IS NOT NULL THEN
104 SELECT start_date
105 INTO ld_start_date
106 FROM gl_period_statuses
107 WHERE ledger_id = pn_ledger_id
108 AND application_id = 101
109 AND period_name = pv_period_from
110 AND to_char(period_year) = pv_accounting_year;
111 ELSE
112 ld_start_date := to_date(pv_accounting_year || '0101', 'YYYYMMDD');
113 END IF; -- pv_period_from IS NOT NULL
114
115 IF pv_period_to IS NOT NULL THEN
116 SELECT end_date
117 INTO ld_end_date
118 FROM gl_period_statuses
119 WHERE ledger_id = pn_ledger_id
120 AND application_id = 101
121 AND period_name = pv_period_to
122 AND to_char(period_year) = pv_accounting_year;
123 ELSE
124 ld_end_date := to_date(pv_accounting_year || '1231', 'YYYYMMDD');
125 END IF; -- pv_period_to IS NOT NULL
126
127 OPEN fa_export_period_cur;
128 LOOP
129 FETCH fa_export_period_cur
130 INTO lv_period_name, ld_start_date_monthly, ld_end_date_monthly;
131 EXIT WHEN fa_export_period_cur%NOTFOUND;
132 ln_row_count := Add_FA_Asset_Card_Monthly(pn_legal_entity_id => pn_legal_entity_id,
133 pn_ledger_id => pn_ledger_id,
134 pv_accounting_year => pv_accounting_year,
135 pv_period_from => lv_period_name,
136 pv_period_to => lv_period_name,
137 pn_coa_id => pn_coa_id);
138 END LOOP;
139 CLOSE fa_export_period_cur;
140
141 --Add the IF statement and the logic by Jiancao Chi for bug 11847991 on 2011-03-09
142 IF (ln_row_count = 0) THEN
143 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_CARD',
144 Ja_Cn_Utility.GV_MODULE_FA);
145
146 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD',
147 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START,
148 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
149 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER',
150 pv_text_node_value => NULL,
151 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
152 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
153 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
154 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_CATEGORY_CODE',
155 pv_text_node_value => NULL,
156 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
157 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
158 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
159 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_NUMBER',
160 pv_text_node_value => NULL,
161 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
162 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
163 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
164 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_NAME',
165 pv_text_node_value => NULL,
166 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
167 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
168 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
169 Ja_Cn_Utility.Add_Fixed_Child_Node(pv_child_tag_name => 'DATE_IN_SERVICE',
170 pv_text_node_value => NULL,
171 pn_fixed_length => 8,
172 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
173 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
174 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCOUNTING_PERIOD',
175 pv_text_node_value => NULL,
176 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
177 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
178 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
179 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_UNIT_OF_MEASURE',
180 pv_text_node_value => NULL,
181 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
182 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
183 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
184 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'UNIT',
185 pv_text_node_value => NULL,
186 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
187 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
188 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
189 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'MODIFICATION_METHOD_CODE',
190 pv_text_node_value => NULL,
191 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
192 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
193 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
194 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'DEPRECIATION_METHOD_CODE',
195 pv_text_node_value => NULL,
196 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
197 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
198 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
199 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'USAGE_STATUS_CODE',
200 pv_text_node_value => NULL,
201 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
202 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
203 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
204 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'LIFE_OF_MONTHS',
205 pv_text_node_value => NULL,
206 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
207 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
208 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
209 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'DEPRECIATED_MONTHS',
210 pv_text_node_value => NULL,
211 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
212 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
213 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
214 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FUNCTIONAL_CURRENCY',
215 pv_text_node_value => NULL,
216 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
217 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
218 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
219 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ORIGINAL_VALUE',
220 pv_text_node_value => NULL,
221 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
222 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
223 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
224 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUMULATED_DEPRECIATION',
225 pv_text_node_value => NULL,
226 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
227 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
228 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
229 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'NET_BOOK_VALUE',
230 pv_text_node_value => NULL,
231 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
232 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
233 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
234 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUMULATED_IMPAIRMENT',
235 pv_text_node_value => NULL,
236 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
237 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
238 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
239 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'SALVAGE_VALUE_PERCENT',
240 pv_text_node_value => NULL,
241 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
242 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
243 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
244 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'SALVAGE_VALUE',
245 pv_text_node_value => NULL,
246 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
247 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
248 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
249 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'MONTHLY_DEPRECIATION_RATE',
250 pv_text_node_value => NULL,
251 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
252 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
253 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
254 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'MONTHLY_DEPRECIATION_AMOUNT',
255 pv_text_node_value => NULL,
256 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
257 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
258 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
259 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'PRODUCTION_UOM',
260 pv_text_node_value => NULL,
261 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
262 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
263 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
264 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'PRODUCTION_CAPACITY',
265 pv_text_node_value => NULL,
266 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
267 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
268 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
269 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'LIFE_TO_DATE_PRODUCTION',
270 pv_text_node_value => NULL,
271 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
272 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
273 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
274 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_ACCOUNT',
275 pv_text_node_value => NULL,
276 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
277 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
278 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
279 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUM_IMPAIRMENT_ACCOUNT',
280 pv_text_node_value => NULL,
281 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
282 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
283 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
284 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUM_DEPRECIATION_ACCOUNT',
285 pv_text_node_value => NULL,
286 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
287 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
288 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
289 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD',
290 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END,
291 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
292 END IF;
293 --logging for debug
294 IF (ln_proc_level >= ln_dbg_level) THEN
295 FND_LOG.STRING(ln_proc_level,
296 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
297 'Exit procedure');
298 END IF; -- (ln_proc_level>=ln_dbg_level)
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
304 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
305 '.Other_Exception ',
306 SQLCODE || SQLERRM);
307 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
308 FND_FILE.put_line(FND_FILE.log,
309 lv_procedure_name || SQLCODE || SQLERRM);
310 END Add_FA_Asset_Card;
311 --==========================================================================
312 -- PROCEDURE NAME:
313 --
314 -- Add_FA_Asset_Card_Monthly Public
315 --
316 -- DESCRIPTION:
317 --
318 -- This procedure is used to export asset card in Fixed Asset monthly.
319 --
320 -- PARAMETERS:
321 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
322 -- pn_ledger_id Ledger ID
323 -- pv_accounting_year Accounting Year
324 -- pv_period_from Period From
325 -- pv_period_to Period To
326 -- pn_coa_id Identifier of chart of account
327 -- DESIGN REFERENCES:
328 -- CNAO_V2_FA_TD.doc
329 --
330 -- CHANGE HISTORY:
331 --| 04/27/2010 Qingyi Wang created
332 -- 09-Mar-2011 Jianchao Chi Updated for bug 11847991
333 --==========================================================================
334 --Changed from procdure to function, and add a return value by Jianchao Chi
335 FUNCTION Add_FA_Asset_Card_Monthly(pn_legal_entity_id IN NUMBER,
336 pn_ledger_id IN NUMBER,
337 pv_accounting_year IN VARCHAR2,
338 pv_period_from IN VARCHAR2,
339 pv_period_to IN VARCHAR2,
340 pn_coa_id NUMBER) RETURN NUMBER IS
341 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
342 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
343 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
344 ln_full_retirement_count NUMBER := 0;
345 ln_reinstate_count NUMBER := 0;
346 ln_depreciation_counter NUMBER := 0;
347 ln_real_deprn_counter NUMBER := 0;
348 ln_deprn_reserve NUMBER(20, 2) := 0;
349 ln_impairment_rsv NUMBER(20, 2) := 0;
350 ln_deprn_rsv_last NUMBER(20, 2) := 0; --the last month depreciate reserve
351 ln_impt_rsv_last NUMBER(20, 2) := 0; --the last month impairment reserve
352 ln_transaction_header_id NUMBER := 0;
353 ln_retire_tran_header_id NUMBER := 0;
354 ln_asset_unit NUMBER(20, 2) := 0;
355 ln_original_value NUMBER(20, 2) := 0;
356 ln_salvage_value NUMBER(20, 2) := 0;
357 ln_salvage_percent NUMBER(20, 2) := 0;
358 ln_production_capacity NUMBER(20, 2) := 0;
359 ln_recoverable_cost NUMBER(20, 2) := 0;
360 ln_life_production NUMBER(20, 2) := 0;
361 ln_life_of_month NUMBER := 0;
362 ln_dep_month NUMBER := 0;
363 ln_legacy_dep_month NUMBER := 0; --the existing depreciate when adding the asset.
364 ln_net_book_value NUMBER(20, 2) := 0;
365 ln_assigned_unit NUMBER(20, 2) := 0;
366 ln_asset_key_ccid NUMBER := 0;
367 ln_monthly_deprn_amount NUMBER(20, 2) := 0;
368 ln_mth_impairment_amount NUMBER(20, 2) := 0;
369 ln_monthly_deprn_rate NUMBER(20, 6) := 0;
370 ln_assigned_rate NUMBER(5, 3) := 0;
371 lb_retirement_flag BOOLEAN := false;
372 lv_procedure_name VARCHAR2(40) := 'Add_FA_Asset_Card_Monthly';
373 lv_asset_id VARCHAR2(15);
374 lv_asset_number VARCHAR2(15);
375 lv_currency VARCHAR2(15);
376 lv_category_code VARCHAR2(210);
377 lv_asset_name VARCHAR2(80);
378 lv_usage_segment VARCHAR2(30);
379 lv_measure_segment VARCHAR2(30);
380 lv_asset_measure VARCHAR2(30);
381 lv_asset_usage VARCHAR2(30);
382 lv_unit_of_measure VARCHAR2(25); --UOM
383 lv_accounting_period VARCHAR2(30);
384 lv_nature_segment VARCHAR2(30);
385 lv_book_class VARCHAR2(15);
386 lv_delimiter VARCHAR2(1);
387 lv_asset_cost_acct VARCHAR2(25);
388 lv_impair_reserve_acct VARCHAR2(25);
389 lv_deprn_reserve_acct VARCHAR2(25);
390 lv_depn_meth_code VARCHAR2(12);
391 lv_book_type_code VARCHAR2(15);
392 ld_start_date DATE;
393 ld_end_date DATE;
394 ld_reinstated_date DATE;
395 ld_retirement_date DATE;
396 ld_in_service_date DATE;
397 ld_addition_date DATE;
398 ln_row_count NUMBER; -- row count for asset card
399 NO_DATA EXCEPTION;
400
401 CURSOR asset_card_cur IS
402 SELECT distinct faav.asset_id,
403 faav.asset_number,
404 faav.attribute_category_code,
405 faav.description,
406 bk.date_placed_in_service,
407 bk.book_type_code,
408 faav.current_units,
409 bk.deprn_method_code,
410 bk.life_in_months,
411 fncv.name,
412 bk.cost,
413 bk.salvage_value,
414 bk.unit_of_measure --UOM
415 ,
416 bk.production_capacity,
417 fcb.asset_cost_acct,
418 fcb.impair_reserve_acct,
419 fcb.deprn_reserve_acct,
420 fbc.book_class,
421 faav.asset_key_ccid
422 FROM fa_additions_v faav
423 --, fa_asset_v fasv
424 ,
425 fa_books bk,
426 fa_book_controls_sec fbc,
427 fa_category_books fcb,
428 fa_methods mth,
429 fa_distribution_history fdh,
430 gl_ledgers gl,
431 fa_transaction_history_trx_v fthv,
432 fnd_currencies_vl fncv
433 -- Check ledger
434 WHERE bk.date_ineffective IS NULL
435 AND bk.transaction_header_id_out IS NULL
436 AND faav.asset_id = bk.asset_id
437 AND bk.book_type_code = fbc.book_type_code
438 AND fbc.book_class IN ('CORPORATE', 'TAX')
439 AND fbc.gl_posting_allowed_flag = 'YES'
440 AND fbc.set_of_books_id = pn_ledger_id
441 AND fdh.asset_id = faav.asset_id
442 -- Check legal entity
443 AND EXISTS
444 (SELECT fdp.period_close_date
445 FROM fa_deprn_periods fdp
446 WHERE fdp.book_type_code = bk.book_type_code
447 AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
448 ld_end_date
449 AND fdp.period_close_date IS NOT NULL)
450 AND fdh.date_effective <
451 nvl((SELECT MAX(fdp.period_close_date)
452 FROM fa_deprn_periods fdp
453 WHERE fdp.book_type_code = bk.book_type_code
454 AND fdp.calendar_period_close_date <= ld_end_date
455 AND fdp.period_close_date IS NOT NULL),
456 SYSDATE)
457 AND (fdh.date_ineffective >
458 nvl((SELECT MAX(fdp.period_close_date)
459 FROM fa_deprn_periods fdp
460 WHERE fdp.book_type_code = bk.book_type_code
461 AND fdp.calendar_period_close_date <= ld_end_date
462 AND fdp.period_close_date IS NOT NULL),
463 SYSDATE) OR fdh.date_ineffective IS NULL)
464 AND EXISTS
465 (SELECT jclllbg.bal_seg_value
466 FROM ja_cn_ledger_le_bsv_gt jclllbg
467 WHERE ja_cn_cfs_data_clt_pkg.get_balancing_segment(fdh.code_combination_id) =
468 jclllbg.bal_seg_value
469 AND jclllbg.Ledger_Id = pn_ledger_id
470 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
471 -- Check the asset type
472 AND faav.asset_type = 'CAPITALIZED'
473 --get category accounts
474 AND fcb.category_id = faav.asset_category_id
475 AND fcb.book_type_code = bk.book_type_code
476 -- get the book window elements.
477 AND bk.asset_id = faav.asset_id
478 AND bk.date_ineffective IS NULL
479 AND bk.book_type_code = fbc.book_type_code
480 AND bk.deprn_method_code = mth.method_code
481 AND (bk.life_in_months = mth.life_in_months OR
482 (bk.life_in_months IS NULL AND mth.life_in_months IS NULL))
483 --get the currency
484 AND gl.ledger_id = pn_ledger_id
485 AND gl.currency_code = fncv.currency_code
486 -- Check the asset added date
487 AND fthv.asset_id = faav.asset_id
488 AND fthv.transaction_type_code = 'ADDITION'
489 AND fthv.book_type_code = bk.book_type_code
490 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
491 least(SYSDATE,
492 dp.calendar_period_close_date)),
493 dp.calendar_period_open_date)
494 FROM fa_deprn_periods dp
495 WHERE fthv.book_type_code = dp.book_type_code
496 AND fthv.date_effective BETWEEN dp.period_open_date AND
497 nvl(dp.period_close_date, SYSDATE)) <= ld_end_date)
498 ORDER BY faav.asset_number;
499
500 -- to get the delimiter
501 CURSOR delimiter_cur IS
502 SELECT concatenated_segment_delimiter
503 FROM fnd_id_flex_structures
504 WHERE id_flex_code = 'CAT#';
505
506 BEGIN
507
508 --logging for debug
509 IF (ln_proc_level >= ln_dbg_level) THEN
510 FND_LOG.STRING(ln_proc_level,
511 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
512 '.begin',
513 'Enter procedure');
514 -- logging the parameters
515 FND_LOG.STRING(ln_proc_level,
516 lv_procedure_name || '.parameters',
517 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
518 'pn_ledger_id=' || pn_ledger_id || ',' ||
519 'pv_accounting_year=' || pv_accounting_year || ',' ||
520 'pv_period_from=' || pv_period_from || ',' ||
521 'pv_period_to=' || pv_period_to || ',' ||
522 'pn_coa_id =' || pn_coa_id);
523 END IF; --l_proc_level>=l_dbg_level
524
525 FND_FILE.put_line(FND_FILE.log,
526 lv_procedure_name || '.parameters:' ||
527 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
528 'pn_ledger_id=' || pn_ledger_id || ',' ||
529 'pv_accounting_year=' || pv_accounting_year || ',' ||
530 'pv_period_from=' || pv_period_from || ',' ||
531 'pv_period_to=' || pv_period_to || ',' ||
532 'pn_coa_id =' || pn_coa_id);
533
534 --Fetch start data and end date
535 IF pv_period_from IS NOT NULL THEN
536 SELECT start_date, period_num
537 INTO ld_start_date, lv_accounting_period
538 FROM gl_period_statuses
539 WHERE ledger_id = pn_ledger_id
540 AND application_id = 101
541 AND period_name = pv_period_from
542 AND to_char(period_year) = pv_accounting_year;
543 ELSE
544 ld_start_date := to_date(pv_accounting_year || '0101', 'YYYYMMDD');
545 END IF; -- pv_period_from IS NOT NULL
546
547 IF pv_period_to IS NOT NULL THEN
548 SELECT end_date
549 INTO ld_end_date
550 FROM gl_period_statuses
551 WHERE ledger_id = pn_ledger_id
552 AND application_id = 101
553 AND period_name = pv_period_to
554 AND to_char(period_year) = pv_accounting_year;
555 ELSE
556 ld_end_date := to_date(pv_accounting_year || '1231', 'YYYYMMDD');
557 END IF; -- pv_period_to IS NOT NULL
558
559 --log start data and end date
560 IF (ln_statement_level >= ln_dbg_level) THEN
561 FND_LOG.STRING(ln_statement_level,
562 lv_procedure_name,
563 'Fetched: start date=' ||
564 nvl(to_char(ld_start_date), 'null') || ' end date=' ||
565 nvl(to_char(ld_end_date), 'null'));
566 END IF; --(ln_statement_level >= ln_dbg_level)
567 FND_FILE.put_line(FND_FILE.log,
568 lv_procedure_name || '.variable:' || 'start date=' ||
569 nvl(to_char(ld_start_date), 'null') || ' end date=' ||
570 nvl(to_char(ld_end_date), 'null'));
571
572 --get the nature accouting segment name
573 SELECT application_column_name
574 INTO lv_nature_segment
575 FROM fnd_segment_attribute_values
576 WHERE application_id = 101
577 AND id_flex_num = pn_coa_id
578 AND id_flex_code = 'GL#'
579 AND attribute_value = 'Y'
580 AND segment_attribute_type = 'GL_ACCOUNT';
581
582 --get the usage segment.
583 SELECT dffa.attribute_column
584 into lv_usage_segment
585 FROM ja_cn_dff_assignments_v dffa
586 WHERE dffa.lookup_code = 'FAAU'
587 AND dffa.chart_of_accounts_id = pn_coa_id;
588
589 --get the measure segment.
590 SELECT dffa.attribute_column
591 into lv_measure_segment
592 FROM ja_cn_dff_assignments_v dffa
593 WHERE dffa.lookup_code = 'FAUM'
594 AND dffa.chart_of_accounts_id = pn_coa_id;
595
596 --get the system delimiter
597 OPEN delimiter_cur;
598 FETCH delimiter_cur
599 INTO lv_delimiter;
600 CLOSE delimiter_cur;
601
602 --get the export element
603 ln_row_count := 0;
604
605 OPEN asset_card_cur;
606 LOOP
607 FETCH asset_card_cur
608 INTO lv_asset_id,
609 lv_asset_number,
610 lv_category_code,
611 lv_asset_name,
612 ld_in_service_date,
613 lv_book_type_code,
614 ln_asset_unit,
615 lv_depn_meth_code,
616 ln_life_of_month,
617 lv_currency,
618 ln_original_value,
619 ln_salvage_value,
620 lv_unit_of_measure,
621 ln_production_capacity,
622 lv_asset_cost_acct,
623 lv_impair_reserve_acct,
624 lv_deprn_reserve_acct,
625 lv_book_class,
626 ln_asset_key_ccid;
627 EXIT WHEN asset_card_cur%NOTFOUND;
628 BEGIN
629 --- check if the asset is retired.
630 --- Check the retirement of the asset
631 lb_retirement_flag := FALSE; --reset the retirement flag. --TODO BOOK TYPE
632 ln_assigned_unit := 0; --reset the assigned unit
633 ln_depreciation_counter := 0;
634 ln_real_deprn_counter := 0;
635 lv_asset_measure := NULL;
636 lv_asset_usage := NULL;
637 ln_assigned_rate := 0;
638 ln_monthly_deprn_rate := 0;
639 ln_salvage_percent := 0;
640 ln_dep_month := 0;
641 ln_legacy_dep_month := 0;
642
643 SELECT COUNT(*)
644 INTO ln_full_retirement_count
645 FROM fa_transaction_history_trx_v fthv
646 WHERE fthv.asset_id = lv_asset_id
647 AND fthv.book_type_code = lv_book_type_code
648 AND fthv.transaction_type_code = 'FULL RETIREMENT'
649 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
650 least(SYSDATE,
651 dp.calendar_period_close_date)),
652 dp.calendar_period_open_date)
653 FROM fa_deprn_periods dp
654 WHERE fthv.book_type_code = dp.book_type_code
655 AND fthv.date_effective BETWEEN dp.period_open_date AND
656 nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
657 IF (ln_full_retirement_count <> 0) THEN
658 --Check the reinstatement of the asset.
659 SELECT COUNT(*)
660 INTO ln_reinstate_count
661 FROM fa_transaction_history_trx_v fthv
662 WHERE fthv.asset_id = lv_asset_id
663 AND fthv.book_type_code = lv_book_type_code
664 AND fthv.transaction_type_code = 'REINSTATEMENT'
665 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
666 least(SYSDATE,
667 dp.calendar_period_close_date)),
668 dp.calendar_period_open_date)
669 FROM fa_deprn_periods dp
670 WHERE fthv.book_type_code = dp.book_type_code
671 AND fthv.date_effective BETWEEN dp.period_open_date AND
672 nvl(dp.period_close_date, SYSDATE)) <=
673 ld_end_date);
674 IF (ln_reinstate_count = 0) THEN
675 lb_retirement_flag := TRUE;
676 ELSE
677 --Retireve the greatest effective date of retirement. ---TODO get the real accouting retirement date.
678 SELECT greatest(date_effective)
679 INTO ld_retirement_date
680 FROM fa_transaction_history_trx_v fthv
681 WHERE fthv.asset_id = lv_asset_id
682 AND fthv.book_type_code = lv_book_type_code
683 AND fthv.transaction_type_code = 'FULL RETIREMENT'
684 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
685 least(SYSDATE,
686 dp.calendar_period_close_date)),
687 dp.calendar_period_open_date)
688 FROM fa_deprn_periods dp
689 WHERE fthv.book_type_code = dp.book_type_code
690 AND fthv.date_effective BETWEEN dp.period_open_date AND
691 nvl(dp.period_close_date, SYSDATE)) <=
692 ld_end_date);
693 --Retireve the greatest effective date of reinstatement.
694 SELECT greatest(date_effective)
695 INTO ld_reinstated_date
696 FROM fa_transaction_history_trx_v fthv
697 WHERE fthv.asset_id = lv_asset_id
698 AND fthv.book_type_code = lv_book_type_code
699 AND fthv.transaction_type_code = 'REINSTATEMENT'
700 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
701 least(SYSDATE,
702 dp.calendar_period_close_date)),
703 dp.calendar_period_open_date)
704 FROM fa_deprn_periods dp
705 WHERE fthv.book_type_code = dp.book_type_code
706 AND fthv.date_effective BETWEEN dp.period_open_date AND
707 nvl(dp.period_close_date, SYSDATE)) <=
708 ld_end_date);
709 IF (ld_retirement_date > ld_reinstated_date) THEN
710 lb_retirement_flag := TRUE;
711 END IF; -- (ld_retirement_date > ld_reinstated_date)
712 END IF; -- (ln_reinstate_count = 0)
713 END IF; -- (ln_full_retirement_count <> 0)
714
715 --get the segment value and description
716 IF (lb_retirement_flag = FALSE) THEN
717 ln_row_count := ln_row_count + 1;
718 --get the period counter from depreciation period.
719 SELECT MAX(dp.period_counter)
720 INTO ln_depreciation_counter
721 FROM fa_deprn_periods dp
722 WHERE dp.book_type_code = lv_book_type_code
723 --AND dp.calendar_period_close_date >= ld_end_date
724 AND dp.calendar_period_open_date <= ld_end_date
725 AND dp.period_close_date IS NOT NULL;
726 --get the period counter from depreciation summary.
727 SELECT nvl(MAX(period_counter), 0)
728 INTO ln_real_deprn_counter
729 FROM fa_deprn_summary
730 WHERE asset_id = lv_asset_id
731 AND book_type_code = lv_book_type_code
732 AND deprn_source_code = 'DEPRN';
733
734 IF (ln_real_deprn_counter < ln_depreciation_counter) THEN
735 ln_depreciation_counter := ln_real_deprn_counter;
736 END IF; --(IF(ln_real_deprn_counter < ln_depreciation_counter))
737
738 --check if the asset is adjusted after the to_date.
739 BEGIN
740 SELECT MIN(thv.transaction_header_id)
741 INTO ln_transaction_header_id
742 FROM fa_deprn_periods dp,
743 fa_transaction_history_trx_v thv,
744 fa_transaction_headers fthr
745 WHERE thv.book_type_code = dp.book_type_code
746 AND thv.asset_id = lv_asset_id
747 AND dp.book_type_code = lv_book_type_code
748 AND thv.transaction_type_code IN
749 ('ADJUSTMENT',
750 'FULL RETIREMENT',
751 'PARTIAL RETIREMENT',
752 'REINSTATEMENT',
753 'REVALUATION')
754 AND thv.transaction_header_id = fthr.transaction_header_id
755 AND (fthr.transaction_key <> 'UA' OR
756 fthr.transaction_key IS NULL)
757 AND thv.date_effective BETWEEN dp.period_open_date AND
758 nvl(dp.period_close_date, SYSDATE)
759 AND greatest(greatest(dp.calendar_period_open_date,
760 least(SYSDATE,
761 dp.calendar_period_close_date)),
762 dp.calendar_period_open_date) > ld_end_date;
763
764 IF (ln_transaction_header_id IS NOT NULL AND
765 ln_transaction_header_id <> -1) THEN
766 --get the old element before adjustment.
767 SELECT cost,
768 salvage_value,
769 adjusted_recoverable_cost,
770 deprn_method_code,
771 production_capacity,
772 date_placed_in_service
773 INTO ln_original_value,
774 ln_salvage_value,
775 ln_recoverable_cost,
776 lv_depn_meth_code,
777 ln_production_capacity,
778 ld_in_service_date
779 FROM fa_books
780 WHERE transaction_header_id_out = ln_transaction_header_id;
781 END IF; --(ln_transaction_header_id IS NOT NULL)
782 EXCEPTION
783 WHEN OTHERS THEN
784 ln_transaction_header_id := -1;
785 END;
786
787 --check if the asset is unit adjusted or reclassed after the to_date.
788 --tax and coporate books share the same infomration, so we don't need identify the book type.
789 BEGIN
790 SELECT MIN(thv.transaction_header_id)
791 INTO ln_transaction_header_id
792 FROM fa_deprn_periods dp, fa_transaction_history_trx_v thv
793 WHERE asset_id = lv_asset_id
794 AND dp.book_type_code = lv_book_type_code
795 AND transaction_type_code IN ('UNIT ADJUSTMENT', 'RECLASS')
796 AND thv.date_effective BETWEEN dp.period_open_date AND
797 nvl(dp.period_close_date, SYSDATE)
798 AND greatest(greatest(dp.calendar_period_open_date,
799 least(SYSDATE,
800 dp.calendar_period_close_date)),
801 dp.calendar_period_open_date) > ld_end_date;
802
803 IF (ln_transaction_header_id IS NOT NULL AND
804 ln_transaction_header_id <> -1) THEN
805 SELECT fhv.units, ckfv.concatenated_segments
806 INTO ln_asset_unit, lv_category_code
807 FROM fa_asset_history_v fhv, fa_categories_b_kfv ckfv
808 WHERE fhv.key = ln_transaction_header_id
809 AND fhv.transaction_header_id_out =
810 ln_transaction_header_id
811 AND fhv.category_id = ckfv.category_id;
812 END IF; --(ln_transaction_header_id)
813 EXCEPTION
814 WHEN OTHERS THEN
815
816 ln_transaction_header_id := -1;
817 END;
818
819 --- monthly depreciation Rate from
820 SELECT deprn_reserve,
821 impairment_reserve,
822 ltd_production,
823 deprn_amount,
824 nvl(impairment_amount, 0)
825 INTO ln_deprn_reserve,
826 ln_impairment_rsv,
827 ln_life_production,
828 ln_monthly_deprn_amount,
829 ln_mth_impairment_amount
830 FROM fa_deprn_summary
831 WHERE asset_id = lv_asset_id
832 AND period_counter = ln_depreciation_counter
833 AND book_type_code = lv_book_type_code;
834
835 --get the assigned unit
836 SELECT SUM(fdh.units_assigned)
837 INTO ln_assigned_unit
838 FROM fa_distribution_history fdh
839 WHERE fdh.asset_id = lv_asset_id
840 AND fdh.date_effective <
841 nvl((SELECT MAX(fdp.period_close_date)
842 FROM fa_deprn_periods fdp
843 WHERE fdp.book_type_code = lv_book_type_code
844 AND fdp.calendar_period_close_date <= ld_end_date
845 AND fdp.period_close_date IS NOT NULL),
846 SYSDATE)
847 AND (fdh.date_ineffective >
848 nvl((SELECT MAX(fdp.period_close_date)
849 FROM fa_deprn_periods fdp
850 WHERE fdp.book_type_code = lv_book_type_code
851 AND fdp.calendar_period_close_date <= ld_end_date
852 AND fdp.period_close_date IS NOT NULL),
853 SYSDATE) OR fdh.date_ineffective IS NULL)
854 AND EXISTS
855 (SELECT jclllbg.bal_seg_value
856 FROM ja_cn_ledger_le_bsv_gt jclllbg
857 WHERE ja_cn_cfs_data_clt_pkg.get_balancing_segment(fdh.code_combination_id) =
858 jclllbg.bal_seg_value
859 AND jclllbg.Ledger_Id = pn_ledger_id
860 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
861
862 GROUP BY fdh.asset_id;
863
864 --verify if the asset was retired during the period.
865 SELECT MIN(fthv.transaction_header_id)
866 INTO ln_retire_tran_header_id
867 FROM fa_transaction_history_trx_v fthv, fa_retirements fr
868 WHERE fthv.book_type_code = lv_book_type_code
869 AND fthv.asset_id = lv_asset_id
870 AND fthv.transaction_type_code IN
871 ('FULL RETIREMENT', 'PARTIAL RETIREMENT')
872 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
873 least(SYSDATE,
874 dp.calendar_period_close_date)),
875 dp.calendar_period_open_date)
876 FROM fa_deprn_periods dp
877 WHERE fthv.book_type_code = dp.book_type_code
878 AND fthv.date_effective BETWEEN dp.period_open_date AND
879 nvl(dp.period_close_date, SYSDATE)) BETWEEN
880 ld_start_date --from date
881 AND ld_end_date --to date)
882 )
883 AND fr.transaction_header_id_in = fthv.transaction_header_id
884 AND fr.status <> 'PENDING'
885 AND fr.asset_id = fthv.asset_id
886 AND (TRANSACTION_HEADER_ID_OUT IS NULL OR NOT EXISTS
887 (SELECT thv.transaction_header_id
888 FROM fa_transaction_history_trx_v thv
889 WHERE thv.transaction_header_id =
890 fr.transaction_header_id_out
891 AND thv.book_type_code = lv_book_type_code
892 AND thv.asset_id = lv_asset_id
893 AND thv.transaction_type_code = 'REINSTATEMENT'
894 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
895 least(SYSDATE,
896 dp.calendar_period_close_date)),
897 dp.calendar_period_open_date)
898 FROM fa_deprn_periods dp
899 WHERE thv.book_type_code = dp.book_type_code
900 AND thv.date_effective BETWEEN
901 dp.period_open_date AND
902 nvl(dp.period_close_date, SYSDATE)) BETWEEN
903 ld_start_date --from date
904 AND ld_end_date --to date)
905 )));
906 --get the cost, salvage value and unit before the retirement.
907 IF (ln_retire_tran_header_id IS NOT NULL) THEN
908 SELECT fb.cost, fb.salvage_value
909 INTO ln_original_value, ln_salvage_value
910 FROM fa_books fb
911 WHERE fb.transaction_header_id_out = ln_retire_tran_header_id;
912
913 SELECT nvl(fds.deprn_reserve, 0),
914 nvl(fds.impairment_reserve, 0)
915 INTO ln_deprn_rsv_last, ln_impt_rsv_last
916 FROM fa_deprn_summary fds
917 WHERE fds.asset_id = lv_asset_id
918 AND fds.book_type_code = lv_book_type_code
919 AND fds.period_counter =
920 (SELECT MAX(period_counter)
921 FROM fa_deprn_summary fds2
922 WHERE fds2.asset_id = lv_asset_id
923 AND fds2.book_type_code = lv_book_type_code
924 AND fds2.period_counter < ln_depreciation_counter);
925
926 ln_deprn_reserve := ln_deprn_rsv_last +
927 ln_monthly_deprn_amount;
928 ln_impairment_rsv := ln_impt_rsv_last +
929 ln_mth_impairment_amount;
930
931 --get the total unit
932 SELECT SUM(fdh.units_assigned)
933 INTO ln_asset_unit
934 FROM fa_distribution_history fdh
935 WHERE fdh.asset_id = lv_asset_id
936 AND fdh.date_effective <
937 nvl((SELECT MAX(fdp.period_close_date)
938 FROM fa_deprn_periods fdp
939 WHERE fdp.book_type_code = lv_book_type_code
940 AND fdp.calendar_period_close_date <=
941 ld_start_date
942 AND fdp.period_close_date IS NOT NULL),
943 SYSDATE)
944 AND (fdh.date_ineffective >
945 nvl((SELECT MAX(fdp.period_close_date)
946 FROM fa_deprn_periods fdp
947 WHERE fdp.book_type_code = lv_book_type_code
948 AND fdp.calendar_period_close_date <=
949 ld_start_date
950 AND fdp.period_close_date IS NOT NULL),
951 SYSDATE) OR fdh.date_ineffective IS NULL);
952 --get the assigned unit
953 SELECT SUM(fdh.units_assigned)
954 INTO ln_assigned_unit
955 FROM fa_distribution_history fdh
956 WHERE fdh.asset_id = lv_asset_id
957 AND fdh.date_effective <
958 nvl((SELECT MAX(fdp.period_close_date)
959 FROM fa_deprn_periods fdp
960 WHERE fdp.book_type_code = lv_book_type_code
961 AND fdp.calendar_period_close_date <=
962 ld_start_date
963 AND fdp.period_close_date IS NOT NULL),
964 SYSDATE)
965 AND (fdh.date_ineffective >
966 nvl((SELECT MAX(fdp.period_close_date)
967 FROM fa_deprn_periods fdp
968 WHERE fdp.book_type_code = lv_book_type_code
969 AND fdp.calendar_period_close_date <=
970 ld_start_date
971 AND fdp.period_close_date IS NOT NULL),
972 SYSDATE) OR fdh.date_ineffective IS NULL)
973 AND EXISTS
974 (SELECT jclllbg.bal_seg_value
975 FROM ja_cn_ledger_le_bsv_gt jclllbg
976 WHERE ja_cn_cfs_data_clt_pkg.get_balancing_segment(fdh.code_combination_id) =
977 jclllbg.bal_seg_value
978 AND jclllbg.Ledger_Id = pn_ledger_id
979 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
980
981 GROUP BY fdh.asset_id;
982
983 END IF; --(ln_retire_tran_header_id IS NOT NULL)
984
985 -- calculate the assignment rate
986 IF (ln_asset_unit IS NOT NULL AND ln_asset_unit <> 0) THEN
987 ln_assigned_rate := ln_assigned_unit / ln_asset_unit;
988 END IF; --(IF(ln_asset_unit IS NOT NULL AND ln_asset_unit <> 0))
989
990 --calcluate the recoverable value
991 ln_recoverable_cost := ln_original_value - ln_salvage_value;
992
993 --calculate the salvage percent.
994 IF (ln_original_value IS NOT NULL AND ln_original_value <> 0) THEN
995 ln_salvage_percent := ln_salvage_value / ln_original_value;
996 END IF; --(IF(ln_original_value IS NOT NULL || ln_original_value <> 0))
997
998 --calculate the dpreciated months after addition.
999 SELECT COUNT(period_counter)
1000 INTO ln_dep_month
1001 FROM fa_deprn_summary
1002 WHERE asset_id = lv_asset_id
1003 AND period_counter <= ln_depreciation_counter
1004 AND book_type_code = lv_book_type_code
1005 AND deprn_source_code = 'DEPRN'
1006 AND deprn_amount <> 0;
1007 --calculate the depreicated months before addition.
1008 SELECT nvl(fdp.calendar_period_close_date, ld_in_service_date)
1009 INTO ld_addition_date
1010 FROM fa_deprn_periods fdp, fa_transaction_history_trx_v thv
1011 WHERE thv.asset_id = lv_asset_id
1012 AND fdp.book_type_code = lv_book_type_code
1013 AND thv.book_type_code = fdp.book_type_code
1014 AND thv.transaction_type_code = 'ADDITION'
1015 AND thv.period_entered = fdp.period_name;
1016
1017 ln_legacy_dep_month := TRUNC(MONTHS_BETWEEN(ld_addition_date,
1018 ld_in_service_date),
1019 0) - 1;
1020
1021 IF (ln_legacy_dep_month > 0) THEN
1022 ln_dep_month := ln_dep_month + ln_legacy_dep_month;
1023 END IF; --(IF(ln_legacy_dep_month > 0))
1024
1025 --calculate monthly depreciation rate
1026 IF (ln_original_value IS NOT NULL AND ln_original_value <> 0) THEN
1027 ln_monthly_deprn_rate := ln_monthly_deprn_amount /
1028 ln_original_value;
1029 END IF; --(ln_original_value IS NOT NULL AND ln_original_value <> 0)
1030
1031 --calculate the net book value
1032 ln_net_book_value := ln_original_value - ln_deprn_reserve -
1033 ln_impairment_rsv;
1034
1035 --remove the delimiter in asset category.
1036 IF (lv_category_code IS NOT NULL AND lv_delimiter IS NOT NULL) THEN
1037 lv_category_code := REPLACE(lv_category_code, lv_delimiter, '');
1038 END IF; --(lv_category_code IS NOT NULL)
1039
1040 --get the unit of measure and asset usage code
1041 IF (ln_asset_key_ccid IS NOT NULL) THEN
1042 SELECT decode(lv_measure_segment,
1043 'SEGMENT1',
1044 faak.segment1,
1045 'SEGMENT2',
1046 faak.segment2,
1047 'SEGMENT3',
1048 faak.segment3,
1049 'SEGMENT4',
1050 faak.segment4,
1051 'SEGMENT5',
1052 faak.segment5,
1053 'SEGMENT6',
1054 faak.segment6,
1055 'SEGMENT7',
1056 faak.segment7,
1057 'SEGMENT8',
1058 faak.segment8,
1059 'SEGMENT9',
1060 faak.segment9,
1061 'SEGMENT10',
1062 faak.segment10),
1063 decode(lv_usage_segment,
1064 'SEGMENT1',
1065 faak.segment1,
1066 'SEGMENT2',
1067 faak.segment2,
1068 'SEGMENT3',
1069 faak.segment3,
1070 'SEGMENT4',
1071 faak.segment4,
1072 'SEGMENT5',
1073 faak.segment5,
1074 'SEGMENT6',
1075 faak.segment6,
1076 'SEGMENT7',
1077 faak.segment7,
1078 'SEGMENT8',
1079 faak.segment8,
1080 'SEGMENT9',
1081 faak.segment9,
1082 'SEGMENT10',
1083 faak.segment10)
1084 INTO lv_asset_measure, lv_asset_usage
1085 FROM fa_asset_keywords faak
1086 WHERE faak.code_combination_id = ln_asset_key_ccid;
1087 END IF; --( IF(ln_asset_key_ccid IS NOT NULL))
1088
1089 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD',
1090 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START,
1091 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1092 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER',
1093 pv_text_node_value => lv_asset_number,
1094 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1095 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1096 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1097 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_CATEGORY_CODE',
1098 pv_text_node_value => lv_category_code,
1099 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1100 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1101 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1102 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_NUMBER',
1103 pv_text_node_value => lv_asset_number,
1104 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1105 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1106 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1107 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_NAME',
1108 pv_text_node_value => lv_asset_name,
1109 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1110 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1111 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1112 Ja_Cn_Utility.Add_Fixed_Child_Node(pv_child_tag_name => 'DATE_IN_SERVICE',
1113 pv_text_node_value => TO_CHAR(ld_in_service_date,
1114 'YYYYMMDD'),
1115 pn_fixed_length => 8,
1116 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1117 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1118 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCOUNTING_PERIOD',
1119 pv_text_node_value => lv_accounting_period,
1120 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1121 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1122 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1123 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_UNIT_OF_MEASURE',
1124 pv_text_node_value => lv_asset_measure,
1125 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1126 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1127 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1128 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'UNIT',
1129 pv_text_node_value => ln_assigned_unit,
1130 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1131 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1132 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1133 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'MODIFICATION_METHOD_CODE',
1134 pv_text_node_value => 'Addition',
1135 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1136 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1137 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1138 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'DEPRECIATION_METHOD_CODE',
1139 pv_text_node_value => lv_depn_meth_code,
1140 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1141 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1142 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1143 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'USAGE_STATUS_CODE',
1144 pv_text_node_value => lv_asset_usage,
1145 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1146 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1147 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1148 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'LIFE_OF_MONTHS',
1149 pv_text_node_value => ln_life_of_month,
1150 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1151 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1152 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1153 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'DEPRECIATED_MONTHS',
1154 pv_text_node_value => LEAST(ln_dep_month,
1155 ln_life_of_month),
1156 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1157 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1158 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1159 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FUNCTIONAL_CURRENCY',
1160 pv_text_node_value => lv_currency,
1161 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1162 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1163 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1164 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ORIGINAL_VALUE',
1165 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_original_value *
1166 ln_assigned_rate,
1167 pn_legal_entity_id => pn_legal_entity_id),
1168 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1169 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1170 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1171 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUMULATED_DEPRECIATION',
1172 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_deprn_reserve *
1173 ln_assigned_rate,
1174 pn_legal_entity_id => pn_legal_entity_id),
1175 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1176 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1177 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1178 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'NET_BOOK_VALUE',
1179 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_net_book_value *
1180 ln_assigned_rate,
1181 pn_legal_entity_id => pn_legal_entity_id),
1182 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1183 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1184 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1185 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUMULATED_IMPAIRMENT',
1186 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_impairment_rsv *
1187 ln_assigned_rate,
1188 pn_legal_entity_id => pn_legal_entity_id),
1189 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1190 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1191 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1192 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'SALVAGE_VALUE_PERCENT',
1193 pv_text_node_value => ln_salvage_percent,
1194 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1195 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1196 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1197 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'SALVAGE_VALUE',
1198 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_salvage_value *
1199 ln_assigned_rate,
1200 pn_legal_entity_id => pn_legal_entity_id),
1201 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1202 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1203 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1204 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'MONTHLY_DEPRECIATION_RATE',
1205 pv_text_node_value => ln_monthly_deprn_rate,
1206 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1207 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1208 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1209 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'MONTHLY_DEPRECIATION_AMOUNT',
1210 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_monthly_deprn_amount *
1211 ln_assigned_rate,
1212 pn_legal_entity_id => pn_legal_entity_id),
1213 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1214 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
1215 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1216 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'PRODUCTION_UOM',
1217 pv_text_node_value => lv_unit_of_measure,
1218 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1219 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
1220 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1221 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'PRODUCTION_CAPACITY',
1222 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_production_capacity *
1223 ln_assigned_rate,
1224 pn_legal_entity_id => pn_legal_entity_id),
1225 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1226 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
1227 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1228 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'LIFE_TO_DATE_PRODUCTION',
1229 pv_text_node_value => ja_cn_utility.get_rounding_value(pn_original_value => ln_life_production *
1230 ln_assigned_rate,
1231 pn_legal_entity_id => pn_legal_entity_id),
1232 pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER,
1233 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
1234 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1235 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_ACCOUNT',
1236 pv_text_node_value => lv_asset_cost_acct,
1237 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1238 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
1239 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1240 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUM_IMPAIRMENT_ACCOUNT',
1241 pv_text_node_value => lv_impair_reserve_acct,
1242 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1243 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
1244 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1245 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCUM_DEPRECIATION_ACCOUNT',
1246 pv_text_node_value => lv_deprn_reserve_acct,
1247 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
1248 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
1249 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1250 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD',
1251 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END,
1252 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
1253
1254 END IF; -- (lb_retirement_flag = FALSE)
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1258 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1259 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1260 '.Other_Exception ',
1261 SQLCODE || SQLERRM);
1262 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1263 FND_FILE.put_line(FND_FILE.log,
1264 lv_procedure_name || SQLCODE || SQLERRM);
1265 END;
1266 END LOOP;
1267 CLOSE asset_card_cur;
1268 /* IF (ln_row_count = 0) --Commented by Jianchao Chi for bug 11847991
1269 THEN
1270
1271 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_CARD',
1272 Ja_Cn_Utility.GV_MODULE_FA);
1273
1274 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'FIXED_ASSET_CARD'
1275 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
1276 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1277 );
1278 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER'
1279 , pv_text_node_value => NULL
1280 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1281 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1282 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1283 );
1284 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_CATEGORY_CODE'
1285 , pv_text_node_value => NULL
1286 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1287 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1288 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1289 );
1290 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_NUMBER'
1291 , pv_text_node_value => NULL
1292 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1293 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1294 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1295 );
1296 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_NAME'
1297 , pv_text_node_value => NULL
1298 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1299 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1300 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1301 );
1302 Ja_Cn_Utility.Add_Fixed_Child_Node( pv_child_tag_name => 'DATE_IN_SERVICE'
1303 , pv_text_node_value => NULL
1304 , pn_fixed_length => 8
1305 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1306 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1307 );
1308 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ACCOUNTING_PERIOD'
1309 , pv_text_node_value => NULL
1310 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1311 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1312 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1313 );
1314 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_UNIT_OF_MEASURE'
1315 , pv_text_node_value => NULL
1316 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1317 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1318 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1319 );
1320 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'UNIT'
1321 , pv_text_node_value => NULL
1322 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1323 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1324 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1325 );
1326 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MODIFICATION_METHOD_CODE'
1327 , pv_text_node_value => NULL
1328 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1329 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1330 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1331 );
1332 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'DEPRECIATION_METHOD_CODE'
1333 , pv_text_node_value => NULL
1334 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1335 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1336 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1337 );
1338 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'USAGE_STATUS_CODE'
1339 , pv_text_node_value => NULL
1340 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1341 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1342 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1343 );
1344 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'LIFE_OF_MONTHS'
1345 , pv_text_node_value => NULL
1346 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1347 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1348 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1349 );
1350 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'DEPRECIATED_MONTHS'
1351 , pv_text_node_value => NULL
1352 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1353 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1354 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1355 );
1356 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FUNCTIONAL_CURRENCY'
1357 , pv_text_node_value => NULL
1358 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1359 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1360 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1361 );
1362 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ORIGINAL_VALUE'
1363 , pv_text_node_value => NULL
1364 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1365 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1366 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1367 );
1368 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ACCUMULATED_DEPRECIATION'
1369 , pv_text_node_value => NULL
1370 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1371 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1372 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1373 );
1374 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'NET_BOOK_VALUE'
1375 , pv_text_node_value => NULL
1376 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1377 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1378 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1379 );
1380 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ACCUMULATED_IMPAIRMENT'
1381 , pv_text_node_value => NULL
1382 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1383 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1384 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1385 );
1386 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'SALVAGE_VALUE_PERCENT'
1387 , pv_text_node_value => NULL
1388 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1389 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1390 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1391 );
1392 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'SALVAGE_VALUE'
1393 , pv_text_node_value => NULL
1394 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1395 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1396 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1397 );
1398 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MONTHLY_DEPRECIATION_RATE'
1399 , pv_text_node_value => NULL
1400 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1401 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1402 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1403 );
1404 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MONTHLY_DEPRECIATION_AMOUNT'
1405 , pv_text_node_value => NULL
1406 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1407 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1408 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1409 );
1410 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'PRODUCTION_UOM'
1411 , pv_text_node_value => NULL
1412 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1413 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
1414 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1415 );
1416 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'PRODUCTION_CAPACITY'
1417 , pv_text_node_value => NULL
1418 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1419 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
1420 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1421 );
1422 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'LIFE_TO_DATE_PRODUCTION'
1423 , pv_text_node_value => NULL
1424 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1425 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
1426 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1427 );
1428 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_ACCOUNT'
1429 , pv_text_node_value => NULL
1430 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1431 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
1432 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1433 );
1434 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ACCUM_IMPAIRMENT_ACCOUNT'
1435 , pv_text_node_value => NULL
1436 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1437 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
1438 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1439 );
1440 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ACCUM_DEPRECIATION_ACCOUNT'
1441 , pv_text_node_value => NULL
1442 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1443 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
1444 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1445 );
1446 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'FIXED_ASSET_CARD'
1447 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
1448 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1449 );
1450
1451 END IF; --(ln_row_count = 0)*/
1452 --logging for debug
1453 IF (ln_proc_level >= ln_dbg_level) THEN
1454 FND_LOG.STRING(ln_proc_level,
1455 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1456 'Exit procedure');
1457 END IF; -- (ln_proc_level>=ln_dbg_level)
1458
1459 IF (ln_row_count = 0) THEN--Added by Jianchao Chi for bug 11847991
1460 RETURN 0;
1461 ELSE
1462 RETURN 1;
1463 END IF; --(ln_row_count = 0)
1464
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1468 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1469 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1470 '.Other_Exception ',
1471 SQLCODE || SQLERRM);
1472 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1473 FND_FILE.put_line(FND_FILE.log,
1474 lv_procedure_name || SQLCODE || SQLERRM);
1475
1476 END Add_FA_Asset_Card_Monthly;
1477 END JA_CN_FA_AC_EXPORT_PKG;
1478