[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_BI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_BI_EXPORT_PKG AS
2 --$Header: JACNFBIB.pls 120.1 2010/05/25 09:34:02 jianliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNFBIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export basic information of fixed asset |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Basic_Information |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 04/08/2010 Jason Liu Created |
20 --+======================================================================*/
21 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_BI_EXPORT_PKG';
22
23 --==========================================================================
24 -- PROCEDURE NAME:
25 --
26 -- Add_Basic_Information Public
27 --
28 -- DESCRIPTION:
29 --
30 -- This procedure is to export basic information of fixed asset
31 --
32 -- PARAMETERS:
33 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
34 -- pn_ledger_id Ledger ID
35 -- pv_accounting_year Accounting Year
36 -- pv_period_from Period From
37 -- pv_period_to Period To
38 --
39 -- DESIGN REFERENCES:
40 -- TDD_1213_JA_CNAOV2_Fixed_Asset_v1.doc
41 --
42 -- CHANGE HISTORY:
43 --
44 -- 08-ARP-2010 Jason Liu created
45
46 PROCEDURE Add_Basic_Information
47 (pn_legal_entity_id IN NUMBER
48 ,pn_ledger_id IN NUMBER
49 ,pv_accounting_year IN VARCHAR2
50 ,pv_period_from IN VARCHAR2
51 ,pv_period_to IN VARCHAR2
52 )
53 IS
54 lv_procedure_name VARCHAR2(40) := 'Add_Basic_Information';
55 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
56 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
57 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
58 NO_DATA EXCEPTION;
59 ld_start_date DATE;
60 ld_end_date DATE;
61 ln_cost_row_count NUMBER; -- row count for fixed asset account
62 ln_impairment_row_count NUMBER; --row count for accumulated impairment account
63 ln_depreciation_row_count NUMBER; --row count for accumulated depreciation account
64
65 -- to get the fixed asset account
66 CURSOR fixed_asset_account_cur IS
67 SELECT DISTINCT fcb.asset_cost_acct
68 FROM fa_book_controls_sec fbcs
69 ,fa_category_books fcb
70 ,fa_additions_v fav
71 WHERE fbcs.book_type_code = fcb.book_type_code
72 AND fbcs.set_of_books_id = pn_ledger_id
73 AND fbcs.book_class IN ('CORPORATE', 'TAX')
74 AND fbcs.gl_posting_allowed_flag = 'YES'
75 AND fav.asset_category_id = fcb.category_id
76 AND fav.asset_type = 'CAPITALIZED'
77 AND fav.asset_id IN
78 (SELECT DISTINCT fdh.asset_id
79 FROM fa_distribution_history fdh
80 ,fa_transaction_headers fth
81 WHERE fdh.asset_id = fth.asset_id
82 AND fth.transaction_type_code NOT IN ('FULL RETIREMENT')
83 AND EXISTS
84 (SELECT fdp.period_close_date
85 FROM fa_deprn_periods fdp
86 WHERE fdp.book_type_code = fbcs.book_type_code
87 AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
88 ld_end_date
89 AND fdp.period_close_date IS NOT NULL)
90 AND fdh.date_effective <
91 nvl((SELECT MAX(fdp.period_close_date)
92 FROM fa_deprn_periods fdp
93 WHERE fdp.book_type_code = fbcs.book_type_code
94 AND fdp.calendar_period_close_date <= ld_end_date
95 AND fdp.period_close_date IS NOT NULL),
96 SYSDATE)
97 AND (fdh.date_ineffective >
98 nvl((SELECT MAX(fdp.period_close_date)
99 FROM fa_deprn_periods fdp
100 WHERE fdp.book_type_code = fbcs.book_type_code
101 AND fdp.calendar_period_close_date <= ld_end_date
102 AND fdp.period_close_date IS NOT NULL),
103 SYSDATE) OR fdh.date_ineffective IS NULL)
104 AND EXISTS
105 (SELECT jclllbg.bal_seg_value
106 FROM ja_cn_ledger_le_bsv_gt jclllbg
107 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
108 jclllbg.bal_seg_value
109 AND jclllbg.ledger_id = pn_ledger_id
110 AND jclllbg.legal_entity_id = pn_legal_entity_id))
111 ORDER BY fcb.asset_cost_acct;
112
113 -- to get the accumulated impairment account
114 CURSOR impairment_account_cur IS
115 SELECT DISTINCT fcb.impair_reserve_acct
116 FROM fa_book_controls_sec fbcs
117 ,fa_category_books fcb
118 ,fa_additions_v fav
119 WHERE fbcs.book_type_code = fcb.book_type_code
120 AND fbcs.set_of_books_id = pn_ledger_id
121 AND fbcs.book_class IN ('CORPORATE', 'TAX')
122 AND fbcs.gl_posting_allowed_flag = 'YES'
123 AND fav.asset_category_id = fcb.category_id
124 AND fav.asset_type = 'CAPITALIZED'
125 AND fav.asset_id IN
126 (SELECT DISTINCT fdh.asset_id
127 FROM fa_distribution_history fdh
128 ,fa_transaction_headers fth
129 WHERE fdh.asset_id = fth.asset_id
130 AND fth.transaction_type_code NOT IN ('FULL RETIREMENT')
131 AND EXISTS
132 (SELECT fdp.period_close_date
133 FROM fa_deprn_periods fdp
134 WHERE fdp.book_type_code = fbcs.book_type_code
135 AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
136 ld_end_date
137 AND fdp.period_close_date IS NOT NULL)
138 AND fdh.date_effective <
139 nvl((SELECT MAX(fdp.period_close_date)
140 FROM fa_deprn_periods fdp
141 WHERE fdp.book_type_code = fbcs.book_type_code
142 AND fdp.calendar_period_close_date <= ld_end_date
143 AND fdp.period_close_date IS NOT NULL),
144 SYSDATE)
145 AND (fdh.date_ineffective >
146 nvl((SELECT MAX(fdp.period_close_date)
147 FROM fa_deprn_periods fdp
148 WHERE fdp.book_type_code = fbcs.book_type_code
149 AND fdp.calendar_period_close_date <= ld_end_date
150 AND fdp.period_close_date IS NOT NULL),
151 SYSDATE) OR fdh.date_ineffective IS NULL)
152 AND EXISTS
153 (SELECT jclllbg.bal_seg_value
154 FROM ja_cn_ledger_le_bsv_gt jclllbg
155 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
156 jclllbg.bal_seg_value
157 AND jclllbg.ledger_id = pn_ledger_id
158 AND jclllbg.legal_entity_id = pn_legal_entity_id))
159 ORDER BY fcb.impair_reserve_acct;
160
161 -- to get the accumulated depreciation account
162 CURSOR depreciation_account_cur IS
163 SELECT DISTINCT fcb.deprn_reserve_acct
164 FROM fa_book_controls_sec fbcs
165 ,fa_category_books fcb
166 ,fa_additions_v fav
167 WHERE fbcs.book_type_code = fcb.book_type_code
168 AND fbcs.set_of_books_id = pn_ledger_id
169 AND fbcs.book_class IN ('CORPORATE', 'TAX')
170 AND fbcs.gl_posting_allowed_flag = 'YES'
171 AND fav.asset_category_id = fcb.category_id
172 AND fav.asset_type = 'CAPITALIZED'
173 AND fav.asset_id IN
174 (SELECT DISTINCT fdh.asset_id
175 FROM fa_distribution_history fdh
176 ,fa_transaction_headers fth
177 WHERE fdh.asset_id = fth.asset_id
178 AND fth.transaction_type_code NOT IN ('FULL RETIREMENT')
179 AND EXISTS
180 (SELECT fdp.period_close_date
181 FROM fa_deprn_periods fdp
182 WHERE fdp.book_type_code = fbcs.book_type_code
183 AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
184 ld_end_date
185 AND fdp.period_close_date IS NOT NULL)
186 AND fdh.date_effective <
187 nvl((SELECT MAX(fdp.period_close_date)
188 FROM fa_deprn_periods fdp
189 WHERE fdp.book_type_code = fbcs.book_type_code
190 AND fdp.calendar_period_close_date <= ld_end_date
191 AND fdp.period_close_date IS NOT NULL),
192 SYSDATE)
193 AND (fdh.date_ineffective >
194 nvl((SELECT MAX(fdp.period_close_date)
195 FROM fa_deprn_periods fdp
196 WHERE fdp.book_type_code = fbcs.book_type_code
197 AND fdp.calendar_period_close_date <= ld_end_date
198 AND fdp.period_close_date IS NOT NULL),
199 SYSDATE) OR fdh.date_ineffective IS NULL)
200 AND EXISTS
201 (SELECT jclllbg.bal_seg_value
202 FROM ja_cn_ledger_le_bsv_gt jclllbg
203 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
204 jclllbg.bal_seg_value
205 AND jclllbg.ledger_id = pn_ledger_id
206 AND jclllbg.legal_entity_id = pn_legal_entity_id))
207 ORDER BY fcb.deprn_reserve_acct;
208
209 BEGIN
210 --logging for debug
211 IF (ln_proc_level >= ln_dbg_level)
212 THEN
213 FND_LOG.STRING(ln_proc_level,
214 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
215 '.begin',
216 'Enter procedure');
217 -- logging the parameters
218 FND_LOG.STRING(ln_proc_level,
219 lv_procedure_name ||
220 '.parameters',
221 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
222 'pn_ledger_id=' || pn_ledger_id || ',' ||
223 'pv_accounting_year=' || pv_accounting_year || ',' ||
224 'pv_period_from=' || pv_period_from || ',' ||
225 'pv_period_to=' || pv_period_to);
226 END IF; --l_proc_level>=l_dbg_level
227
228 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
229 '.parameters:' ||
230 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
231 'pn_ledger_id=' || pn_ledger_id || ',' ||
232 'pv_accounting_year=' || pv_accounting_year || ',' ||
233 'pv_period_from=' || pv_period_from || ',' ||
234 'pv_period_to=' || pv_period_to);
235
236 --Fetch start date and end date
237 IF pv_period_from IS NOT NULL
238 THEN
239 SELECT start_date
240 INTO ld_start_date
241 FROM gl_period_statuses
242 WHERE ledger_id = pn_ledger_id
243 AND application_id = 101
244 AND period_name = pv_period_from
245 AND to_char(period_year) = pv_accounting_year;
246 ELSE
247 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
248 END IF; --pv_period_from IS NOT NULL
249
250 IF pv_period_to IS NOT NULL
251 THEN
252 SELECT end_date
253 INTO ld_end_date
254 FROM gl_period_statuses
255 WHERE ledger_id = pn_ledger_id
256 AND application_id = 101
257 AND period_name = pv_period_to
258 AND to_char(period_year) = pv_accounting_year;
259 ELSE
260 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
261 END IF; --pv_period_to IS NOT NULL
262
263 --logging the variables
264 IF (ln_statement_level >= ln_dbg_level)
265 THEN
266 FND_LOG.STRING(ln_statement_level,
267 lv_procedure_name,
268 'ld_start_date' || ld_start_date || ',' ||
269 'ld_end_date=' || ld_end_date);
270 END IF; --(ln_statement_level >= ln_dbg_level)
271
272 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
273 '.variables:' ||
274 'ld_start_date=' || ld_start_date || ',' ||
275 'ld_end_date=' || ld_end_date);
276
277
278 --add to XML
279 Ja_Cn_Utility.Add_Sub_Root_Node('BASIC_INFO_OF_FIXED_ASSET',
280 Ja_Cn_Utility.GV_TAG_TYPE_START,
281 Ja_Cn_Utility.GV_MODULE_FA);
282 ln_cost_row_count :=0;
283 FOR fixed_asset_account_row IN fixed_asset_account_cur
284 LOOP
285 ln_cost_row_count := ln_cost_row_count + 1;
286 IF(fixed_asset_account_row.asset_cost_acct IS NOT NULL)
287 THEN
288 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_ACCOUNT',
289 fixed_asset_account_row.asset_cost_acct,
290 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
291 Ja_Cn_Utility.GV_REQUIRED_YES,
292 Ja_Cn_Utility.GV_MODULE_FA
293 );
294 END IF; --(fixed_asset_account_row.asset_cost_acct IS NOT NULL)
295 END LOOP; --fixed_asset_account_row IN fixed_asset_account_cur
296
297 IF(ln_cost_row_count = 0)
298 THEN
299 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_ACCOUNT',
300 NULL,
301 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
302 Ja_Cn_Utility.GV_REQUIRED_YES,
303 Ja_Cn_Utility.GV_MODULE_FA
304 );
305 END IF; --(ln_cost_row_count = 0)
306
307 ln_impairment_row_count := 0;
308 FOR impairment_account_row IN impairment_account_cur
309 LOOP
310 ln_impairment_row_count := ln_impairment_row_count + 1;
311 IF(impairment_account_row.impair_reserve_acct IS NOT NULL)
312 THEN
313 Ja_Cn_Utility.Add_Child_Node('ACCUM_IMPAIRMENT_ACCOUNT',
314 impairment_account_row.impair_reserve_acct,
315 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
316 Ja_Cn_Utility.GV_REQUIRED_YES,
317 Ja_Cn_Utility.GV_MODULE_FA
318 );
319 END IF; --(impairment_account_row.deprn_reserve_acct IS NOT NULL)
320 END LOOP; --impairment_account_row IN impairment_account_cur
321
322 IF(ln_impairment_row_count = 0)
323 THEN
324 Ja_Cn_Utility.Add_Child_Node('ACCUM_IMPAIRMENT_ACCOUNT',
325 NULL,
326 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
327 Ja_Cn_Utility.GV_REQUIRED_YES,
328 Ja_Cn_Utility.GV_MODULE_FA
329 );
330 END IF; --(ln_impairment_row_count = 0)
331
332 ln_depreciation_row_count := 0;
333 FOR depreciation_account_row IN depreciation_account_cur
334 LOOP
335 ln_depreciation_row_count := ln_depreciation_row_count + 1;
336 IF(depreciation_account_row.deprn_reserve_acct IS NOT NULL)
337 THEN
338 Ja_Cn_Utility.Add_Child_Node('ACCUM_DEPRECIATION_ACCOUNT',
339 depreciation_account_row.deprn_reserve_acct,
340 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
341 Ja_Cn_Utility.GV_REQUIRED_YES,
342 Ja_Cn_Utility.GV_MODULE_FA
343 );
344 END IF; --(depreciation_account_row.impair_reserve_acct IS NOT NULL)
345 END LOOP; --depreciation_account_row IN depreciation_account_cur
346
347 IF(ln_depreciation_row_count = 0)
348 THEN
349 Ja_Cn_Utility.Add_Child_Node('ACCUM_DEPRECIATION_ACCOUNT',
350 NULL,
351 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
352 Ja_Cn_Utility.GV_REQUIRED_YES,
353 Ja_Cn_Utility.GV_MODULE_FA
354 );
355 END IF; --(ln_impairment_row_count = 0)
356
357 Ja_Cn_Utility.Add_Sub_Root_Node('BASIC_INFO_OF_FIXED_ASSET',
358 Ja_Cn_Utility.GV_TAG_TYPE_END,
359 Ja_Cn_Utility.GV_MODULE_FA);
360
361
362 --logging for debug
363 IF (ln_proc_level >= ln_dbg_level)
364 THEN
365 FND_LOG.STRING(ln_proc_level,
366 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
367 'Exit procedure');
368 END IF; -- (ln_proc_level>=ln_dbg_level)
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
372 THEN
373 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
374 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
375 '.Other_Exception ',
376 SQLCODE || SQLERRM);
377 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
378 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
379 RAISE;
380 END Add_Basic_Information;
381
382 END JA_CN_FA_BI_EXPORT_PKG;
383