[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_ACS_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_ACS_EXPORT_PKG AS
2 --$Header: JACNACSB.pls 120.3 2011/06/20 03:54:32 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNACSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export asset category. |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Asset_Category |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 04/08/2010 Jason Liu Created |
20 --| 20-Jun-2011 Choli updated, fix bug 12634759 |
21 --+======================================================================*/
22 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_BI_EXPORT_PKG';
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 --
27 -- Add_Asset_Category Public
28 --
29 -- DESCRIPTION:
30 --
31 -- This procedure is to export fixed asset category
32 --
33 -- PARAMETERS:
34 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
35 -- pn_ledger_id Ledger ID
36 -- pv_accounting_year Accounting Year
37 -- pv_period_from Period From
38 -- pv_period_to Period To
39 --
40 -- DESIGN REFERENCES:
41 -- TDD_1213_JA_CNAOV2_Fixed_Asset_v1.doc
42 --
43 -- CHANGE HISTORY:
44 --
45 -- 08-ARP-2010 Jason Liu created
46 -- 20-Jun-2011 Choli updated, fix bug 12634759
47 PROCEDURE Add_Asset_Category
48 (pn_legal_entity_id IN NUMBER
49 ,pn_ledger_id IN NUMBER
50 ,pv_accounting_year IN VARCHAR2
51 ,pv_period_from IN VARCHAR2
52 ,pv_period_to IN VARCHAR2
53 )
54 IS
55 lv_procedure_name VARCHAR2(40) := 'Add_Asset_Category';
56 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
57 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
58 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
59 ld_start_date DATE;
60 ld_end_date DATE;
61 ln_row_count NUMBER;
62 lv_code_convention VARCHAR2(100);
63 lv_delimiter VARCHAR2(1);
64 lv_concatenated_category_name VARCHAR2(1000);
65 lv_category VARCHAR2(300);
66 lv_category_value VARCHAR2(300);
67 lv_category_name VARCHAR2(300);
68 lv_combined_category_code VARCHAR2(1000);
69 ln_segment_count NUMBER;
70 TYPE value_set_id_tbl_type IS TABLE OF NUMBER
71 INDEX BY BINARY_INTEGER;
72 TYPE category_tbl_type IS TABLE OF VARCHAR2(200)
73 INDEX BY BINARY_INTEGER;
74 value_set_id_tbl value_set_id_tbl_type;
75 category_tbl category_tbl_type;
76
77 -- to get the code convention
78 CURSOR code_convention_cur
79 IS
80 SELECT ffvs.maximum_size
81 ,fifs.flex_value_set_id
82 FROM fnd_id_flex_segments_vl fifs
83 ,fnd_flex_value_sets ffvs
84 WHERE id_flex_code = 'CAT#'
85 AND fifs.flex_value_set_id = ffvs.flex_value_set_id
86 ORDER BY fifs.segment_num;
87
88 -- to get the delimiter
89 CURSOR delimiter_cur
90 IS
91 SELECT concatenated_segment_delimiter
92 FROM fnd_id_flex_structures
93 WHERE id_flex_code = 'CAT#';
94
95 --to get the category
96 CURSOR category_cur
97 IS
98 SELECT DISTINCT fcbk.concatenated_segments category_name
99 FROM fa_book_controls_sec fbcs
100 ,fa_category_books fcb
101 ,fa_additions_v fav
102 ,fa_categories_b_kfv fcbk
103 WHERE fbcs.book_type_code = fcb.book_type_code
104 AND fbcs.set_of_books_id = pn_ledger_id
105 AND fbcs.book_class IN ('CORPORATE', 'TAX')
106 AND fav.asset_category_id = fcb.category_id
107 AND fav.asset_type = 'CAPITALIZED'
108 -- Choli updated, fix bug 12634759
109 AND (asset_id in
110 (SELECT asset_id
111 FROM FA_BOOKS fb, FA_BOOK_CONTROLS_SEC sec
112 WHERE fb.BOOK_TYPE_CODE = fcb.book_type_code
113 AND NVL(DISABLED_FLAG, 'N') = 'N'
114 AND transaction_header_id_out IS NULL
115 AND fb.BOOK_TYPE_CODE = sec.BOOK_TYPE_CODE))
116 ----------------------------------------------------------
117 AND fav.asset_id IN
118 (SELECT DISTINCT fdh.asset_id
119 FROM fa_distribution_history fdh
120 ,fa_transaction_headers fth
121 WHERE fdh.asset_id = fth.asset_id
122 AND fth.transaction_type_code NOT IN ('FULL RETIREMENT')
123 AND EXISTS
124 (SELECT fdp.period_close_date
125 FROM FA_DEPRN_PERIODS fdp
126 WHERE fdp.book_type_code = fbcs.book_type_code
127 AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
128 ld_end_date
129 AND fdp.period_close_date IS NOT NULL)
130 AND fdh.date_effective <
131 nvl((SELECT MAX(fdp.period_close_date)
132 FROM FA_DEPRN_PERIODS fdp
133 WHERE fdp.book_type_code = fbcs.book_type_code
134 AND fdp.calendar_period_close_date <= ld_end_date
135 AND fdp.period_close_date IS NOT NULL),
136 SYSDATE)
137 AND (fdh.date_ineffective >
138 nvl((SELECT MAX(fdp.period_close_date)
139 FROM FA_DEPRN_PERIODS fdp
140 WHERE fdp.book_type_code = fbcs.book_type_code
141 AND fdp.calendar_period_close_date <= ld_end_date
142 AND fdp.period_close_date IS NOT NULL),
143 SYSDATE) OR fdh.date_ineffective IS NULL)
144 AND EXISTS
145 (SELECT jclllbg.bal_seg_value
146 FROM ja_cn_ledger_le_bsv_gt jclllbg
147 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
148 jclllbg.bal_seg_value
149 AND jclllbg.ledger_id = pn_ledger_id
150 AND jclllbg.legal_entity_id = pn_legal_entity_id))
151 AND fcbk.category_id = fcb.category_id;
152
153 CURSOR category_code_name_cur
154 (pv_child_category VARCHAR2
155 ,pv_parent_category VARCHAR2
156 ,pn_value_set_id NUMBER
157 )
158 IS
159 SELECT ffvv.flex_value category_value
160 ,ffvv.description category_name
161 FROM fnd_id_flex_segments_vl fifs
162 ,fnd_flex_value_sets ffvs
163 ,fnd_flex_values_vl ffvv
164 WHERE fifs.id_flex_code = 'CAT#'
165 AND fifs.flex_value_set_id = ffvs.flex_value_set_id
166 AND ffvv.flex_value_set_id = ffvs.flex_value_set_id
167 AND ffvv.flex_value = pv_child_category
168 AND ((ffvv.parent_flex_value_low =
169 nvl(pv_parent_category, ffvv.parent_flex_value_low) AND
170 parent_flex_value_low IS NOT NULL) OR
171 (ffvv.parent_flex_value_low IS NULL))
172 AND ffvv.flex_value_set_id = pn_value_set_id;
173
174 -- to get fileterd category
175 CURSOR filtered_category_cur IS
176 SELECT DISTINCT
177 jcfc.category_code,
178 jcfc.category_name
179 FROM ja_cn_fa_category_gt jcfc
180 ORDER BY jcfc.category_code;
181
182 BEGIN
183 --logging for debug
184 IF (ln_proc_level >= ln_dbg_level)
185 THEN
186 FND_LOG.STRING(ln_proc_level,
187 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
188 '.begin',
189 'Enter procedure');
190 -- logging the parameters
191 FND_LOG.STRING(ln_proc_level,
192 lv_procedure_name ||
193 '.parameters',
194 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
195 'pn_ledger_id=' || pn_ledger_id || ',' ||
196 'pv_accounting_year=' || pv_accounting_year || ',' ||
197 'pv_period_from=' || pv_period_from || ',' ||
198 'pv_period_to=' || pv_period_to);
199 END IF; --l_proc_level>=l_dbg_level
200
201 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
202 '.parameters:' ||
203 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
204 'pn_ledger_id=' || pn_ledger_id || ',' ||
205 'pv_accounting_year=' || pv_accounting_year || ',' ||
206 'pv_period_from=' || pv_period_from || ',' ||
207 'pv_period_to=' || pv_period_to);
208
209 --Fetch start date and end date
210 IF pv_period_from IS NOT NULL
211 THEN
212 SELECT start_date
213 INTO ld_start_date
214 FROM gl_period_statuses
215 WHERE ledger_id = pn_ledger_id
216 AND application_id = 101
217 AND period_name = pv_period_from
218 AND to_char(period_year) = pv_accounting_year;
219 ELSE
220 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
221 END IF; --pv_period_from IS NOT NULL
222
223 IF pv_period_to IS NOT NULL
224 THEN
225 SELECT end_date
226 INTO ld_end_date
227 FROM gl_period_statuses
228 WHERE ledger_id = pn_ledger_id
229 AND application_id = 101
230 AND period_name = pv_period_to
231 AND to_char(period_year) = pv_accounting_year;
232 ELSE
233 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
234 END IF; --pv_period_to IS NOT NULL
235
236 --logging the variables
237 IF (ln_statement_level >= ln_dbg_level)
238 THEN
239 FND_LOG.STRING(ln_statement_level,
240 lv_procedure_name,
241 'ld_start_date' || ld_start_date || ',' ||
242 'ld_end_date=' || ld_end_date);
243 END IF; --(ln_statement_level >= ln_dbg_level)
244
245 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
246 '.variables:' ||
247 'ld_start_date=' || ld_start_date || ',' ||
248 'ld_end_date=' || ld_end_date);
249
250 --OPEN code_convention_cur;
251 lv_code_convention := '';
252 ln_segment_count := 0;
253 FOR code_convention_row IN code_convention_cur
254 LOOP
255 ln_segment_count := ln_segment_count + 1;
256 value_set_id_tbl(ln_segment_count) := code_convention_row.flex_value_set_id;
257 lv_code_convention := lv_code_convention || code_convention_row.maximum_size || '-';
258 END LOOP;
259 -- remove the last '-'
260 lv_code_convention := substr(lv_code_convention, 1,length(lv_code_convention)-1);
261
262 OPEN delimiter_cur;
263 FETCH delimiter_cur INTO lv_delimiter;
264 CLOSE delimiter_cur;
265
266 lv_combined_category_code := '';
267 FOR category_row IN category_cur
268 LOOP
269 lv_concatenated_category_name := category_row.category_name;
270 --lv_parent_category := '';
271 lv_combined_category_code := '';
272
273 -- check if ends with the delimiter
274 IF(substr(lv_concatenated_category_name,-1) = lv_delimiter)
275 THEN
276 lv_concatenated_category_name := lv_concatenated_category_name;
277 ELSE
278 lv_concatenated_category_name := lv_concatenated_category_name || lv_delimiter;
279 END IF; --(substr(lv_concatenated_category_name,-1) = lv_delimiter)
280
281 ln_segment_count := 0;
282 WHILE lv_concatenated_category_name IS NOT NULL
283 LOOP
284 ln_segment_count := ln_segment_count + 1;
285 lv_category := trim(substr(lv_concatenated_category_name, 1,
286 instr(lv_concatenated_category_name, lv_delimiter, 1)-1
287 ));
288
289 lv_concatenated_category_name := substr(lv_concatenated_category_name,
290 instr(lv_concatenated_category_name, lv_delimiter, 1)+1,
291 length(lv_concatenated_category_name)
292 );
293 category_tbl(ln_segment_count):= lv_category;
294
295 IF(ln_segment_count = 1)
296 THEN
297 OPEN category_code_name_cur(lv_category, null, value_set_id_tbl(ln_segment_count));
298 FETCH category_code_name_cur INTO lv_category_value, lv_category_name;
299 CLOSE category_code_name_cur;
300 lv_combined_category_code := lv_combined_category_code || lv_category_value;
301
302 -- insert into the temporary table in order to filter the same
303 INSERT INTO ja_cn_fa_category_gt
304 (category_code
305 ,category_name
306 ,created_by
307 ,creation_date
308 ,last_updated_by
309 ,last_update_date
310 ,last_update_login)
311 VALUES
312 (lv_combined_category_code
313 ,lv_category_name
314 ,fnd_global.user_id
315 ,SYSDATE
316 ,fnd_global.user_id
317 ,SYSDATE
318 ,fnd_global.LOGIN_ID);
319 ELSE
320 FOR i IN 1..ln_segment_count-1
321 LOOP
322 OPEN category_code_name_cur(lv_category, category_tbl(i), value_set_id_tbl(ln_segment_count));
323 FETCH category_code_name_cur INTO lv_category_value, lv_category_name;
324
325 IF(category_code_name_cur%ROWCOUNT > 0)
326 THEN
327 lv_combined_category_code := lv_combined_category_code || lv_category_value;
328
329 -- insert into the temporary table in order to filter the same
330 INSERT INTO ja_cn_fa_category_gt
331 (category_code
332 ,category_name
333 ,created_by
334 ,creation_date
335 ,last_updated_by
336 ,last_update_date
337 ,last_update_login)
338 VALUES
339 (lv_combined_category_code
340 ,lv_category_name
341 ,fnd_global.user_id
342 ,SYSDATE
343 ,fnd_global.user_id
344 ,SYSDATE
345 ,fnd_global.LOGIN_ID);
346 END IF; --(category_code_name_cur%ROWCOUNT > 0)
347 CLOSE category_code_name_cur;
348 END LOOP; --FOR i IN 1..ln_segment_count-1
349 END IF; --(ln_segment_count = 1)
350 END LOOP; --lv_concatenated_category_name IS NOT NULL
351 END LOOP; --category_row IN category_cur
352
353 --add to XML
354 ln_row_count := 0;
355 FOR filtered_category_row IN filtered_category_cur
356 LOOP
357 ln_row_count := ln_row_count + 1;
358 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_CATEGORY_SETTING',
359 Ja_Cn_Utility.GV_TAG_TYPE_START,
360 Ja_Cn_Utility.GV_MODULE_FA);
361
362 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CATEGORY_CODE_RULE',
363 lv_code_convention,
364 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
365 Ja_Cn_Utility.GV_REQUIRED_YES,
366 Ja_Cn_Utility.GV_MODULE_FA
367 );
368 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CATEGORY_CODE',
369 filtered_category_row.category_code,
370 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
371 Ja_Cn_Utility.GV_REQUIRED_YES,
372 Ja_Cn_Utility.GV_MODULE_FA);
373 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CATEGORY_NAME',
374 filtered_category_row.category_name,
375 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
376 Ja_Cn_Utility.GV_REQUIRED_YES,
377 Ja_Cn_Utility.GV_MODULE_FA);
378
379 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_CATEGORY_SETTING',
380 Ja_Cn_Utility.GV_TAG_TYPE_END,
381 Ja_Cn_Utility.GV_MODULE_FA);
382 END LOOP; --filtered_category_row IN filtered_category_cur
383
384 IF(ln_row_count = 0)
385 THEN
386 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_CATEGORY_SETTING',
387 Ja_Cn_Utility.GV_MODULE_FA);
388
389 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_CATEGORY_SETTING',
390 Ja_Cn_Utility.GV_TAG_TYPE_START,
391 Ja_Cn_Utility.GV_MODULE_FA);
392
393 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CATEGORY_CODE_RULE',
394 NULL,
395 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
396 Ja_Cn_Utility.GV_REQUIRED_YES,
397 Ja_Cn_Utility.GV_MODULE_FA
398 );
399 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CATEGORY_CODE',
400 NULL,
401 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
402 Ja_Cn_Utility.GV_REQUIRED_YES,
403 Ja_Cn_Utility.GV_MODULE_FA);
404 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CATEGORY_NAME',
405 NULL,
406 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
407 Ja_Cn_Utility.GV_REQUIRED_YES,
408 Ja_Cn_Utility.GV_MODULE_FA);
409
410 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_CATEGORY_SETTING',
411 Ja_Cn_Utility.GV_TAG_TYPE_END,
412 Ja_Cn_Utility.GV_MODULE_FA);
413 END IF; --(ln_row_count = 0)
414
415 --logging for debug
416 IF (ln_proc_level >= ln_dbg_level)
417 THEN
418 FND_LOG.STRING(ln_proc_level,
419 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
420 'Exit procedure');
421 END IF; -- (ln_proc_level>=ln_dbg_level)
422 EXCEPTION
423 WHEN OTHERS THEN
424 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
425 THEN
426 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
427 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
428 '.Other_Exception ',
429 SQLCODE || SQLERRM);
430 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
431 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
432 RAISE;
433 END Add_Asset_Category;
434
435 END JA_CN_FA_ACS_EXPORT_PKG;
436