DBA Data[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